-------------------------------------------------------------------------------------------------------------------
create table xyka_test_todrop_bkp as ( select * from xyka_test_todrop)
commit
drop table xyka_test_todrop
create table xyka_test_todrop
as
select ORGANIZATION_CODE,ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID,LOCATOR,INVENTORY_ITEM_ID,ITEM, sum(ON_HAND) ON_HAND
from mtl_onhand_total_v
where ORGANIZATION_ID=105
and SUBINVENTORY_CODE = 'BMD'
group by ORGANIZATION_CODE,ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID,LOCATOR,INVENTORY_ITEM_ID,ITEM
alter table xyka_test_todrop
add( qty_transact number,
qty_reserve number );
select * from xyka_test_todrop
--where item = 'BH-28'
select * from hr_operating_units
DECLARE
x_return_status VARCHAR2 (1);
x_msg_data VARCHAR2 (4000);
x_msg_count NUMBER;
x_qoh NUMBER;
x_rqoh NUMBER;
x_qr NUMBER;
x_qs NUMBER;
x_att NUMBER;
x_atr NUMBER;
x_sqoh NUMBER;
x_srqoh NUMBER;
x_sqr NUMBER;
x_sqs NUMBER;
x_satt NUMBER;
x_sqtr NUMBER;
a number := 0;
b number := 0;
BEGIN
inv_globals.set_org_id (85);
inv_quantity_tree_pub.clear_quantity_cache;
for x in (
select ORGANIZATION_CODE,ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID,LOCATOR,INVENTORY_ITEM_ID,ITEM,ON_HAND
from xyka_test_todrop
--where INVENTORY_ITEM_ID = 8367
--and LOCATOR_ID = 18471
--and ORGANIZATION_ID = 112
--and SUBINVENTORY_CODE = 'IBS'
)
LOOP
inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => x.ORGANIZATION_ID,
p_inventory_item_id => x.INVENTORY_ITEM_ID,
p_tree_mode => inv_quantity_tree_pvt.g_transaction_mode,
p_is_revision_control => FALSE,
p_is_lot_control => TRUE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => x.SUBINVENTORY_CODE,
p_locator_id => x.LOCATOR_ID,
x_qoh => x_qoh,
x_rqoh => x_rqoh,
x_qr => x_qr,
x_qs => x_qs,
x_att => x_att,
x_atr => x_atr);
update xyka_test_todrop
set QTY_TRANSACT = X_att,
qty_reserve = x_atr
where INVENTORY_ITEM_ID = x.INVENTORY_ITEM_ID
and LOCATOR_ID = x.LOCATOR_ID
and ORGANIZATION_ID = 105
and SUBINVENTORY_CODE = 'BMD';
a := sql%rowcount;
b := a + b;
end loop;
dbms_output.put_line(to_char(b));
END;
select * from xyka_test_todrop
commit
Monday, April 16, 2012
Thursday, April 12, 2012
XML Reports An invalid character was found in text content.
You need to update your profile in 11i / R12 .
change this to UTF8. We had this issue with our international documents .
Go To - System Administrator > Profiles > Find for below profile . change value to - UFT 8
FND: NATIVE CLIENT ENCODING
Before >>
After >>
change this to UTF8. We had this issue with our international documents .
Go To - System Administrator > Profiles > Find for below profile . change value to - UFT 8
FND: NATIVE CLIENT ENCODING
Before >>
Monday, April 9, 2012
WMS - Pick Pending Summery Report
/* Formatted on 4/9/2012 3:12:18 PM (QP5 v5.114.809.3010) */
SELECT ROW_NUMBER ()
OVER (
ORDER BY
oel.shipping_method_code, hp.party_name, oeh.order_number
)
AS sr_no,
mmtt.transaction_temp_id task_no,
wdt.task_id,
hp.party_name customer,
msi.DESCRIPTION,
flv.meaning task_status,
fu1.user_name USER_name,
mmtt.transaction_quantity QUANTITY,
oeh.order_number,
mmtt.SUBINVENTORY_CODE,
mmtt.TRANSACTION_SOURCE_NAME,
wt.user_task_type_code EQUIPMENT,
mmtt.LOCATOR_ID,
oel.shipping_method_code route,
ot.name order_type
FROM hz_parties hp,
hz_cust_accounts hca,
oe_order_headers_all oeh,
mtl_material_transactions_temp mmtt,
wms_dispatched_tasks wdt,
oe_order_lines_all oel,
fnd_lookup_values flv,
fnd_user fu1,
mtl_system_items_b msi,
oe_transaction_types_tl ot,
wms_tasks_v wt
WHERE msi.organization_id = mmtt.organization_id
AND msi.inventory_item_id = mmtt.inventory_item_id
AND oel.line_id = mmtt.trx_source_line_id
AND flv.lookup_type = 'WMS_TASK_STATUS'
AND flv.lookup_code =
NVL (wdt.status, NVL (mmtt.wms_task_status, 1))
AND flv.language = 'US'
AND wdt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND oeh.header_id = oel.header_id
AND fu1.employee_id(+) = wdt.person_id
AND oeh.sold_to_org_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND flv.MEANING IN ('Pending', 'Unreleased')
AND oeh.order_type_id = ot.transaction_type_id
AND wt.transaction_temp_id = mmtt.transaction_temp_id
AND hp.party_name = NVL (:P_CUST, hp.party_name)
AND wt.user_task_type_code =
NVL (:P_EQUIPMENT, wt.user_task_type_code)
AND oeh.order_number = NVL (:P_ORD_NO, oeh.order_number)
AND ot.name = NVL (:P_ORD_TYP, ot.name)
AND ot.language = 'US'
AND NVL (oel.shipping_method_code, -99) =
NVL (:P_ROUTE, NVL (oel.shipping_method_code, -99))
AND mmtt.SUBINVENTORY_CODE =
NVL (:P_SUB_INV, mmtt.SUBINVENTORY_CODE)
AND TRUNC (mmtt.creation_date) BETWEEN TRUNC(NVL (
:P_FRM_DT,
mmtt.creation_date
))
AND TRUNC(NVL (
:P_TO_DT,
mmtt.creation_date
))
AND mmtt.organization_id =
NVL (:P_ORGANIZATION_ID, mmtt.organization_id)
ORDER BY oel.shipping_method_code, hp.party_name, oeh.order_number
SELECT ROW_NUMBER ()
OVER (
ORDER BY
oel.shipping_method_code, hp.party_name, oeh.order_number
)
AS sr_no,
mmtt.transaction_temp_id task_no,
wdt.task_id,
hp.party_name customer,
msi.DESCRIPTION,
flv.meaning task_status,
fu1.user_name USER_name,
mmtt.transaction_quantity QUANTITY,
oeh.order_number,
mmtt.SUBINVENTORY_CODE,
mmtt.TRANSACTION_SOURCE_NAME,
wt.user_task_type_code EQUIPMENT,
mmtt.LOCATOR_ID,
oel.shipping_method_code route,
ot.name order_type
FROM hz_parties hp,
hz_cust_accounts hca,
oe_order_headers_all oeh,
mtl_material_transactions_temp mmtt,
wms_dispatched_tasks wdt,
oe_order_lines_all oel,
fnd_lookup_values flv,
fnd_user fu1,
mtl_system_items_b msi,
oe_transaction_types_tl ot,
wms_tasks_v wt
WHERE msi.organization_id = mmtt.organization_id
AND msi.inventory_item_id = mmtt.inventory_item_id
AND oel.line_id = mmtt.trx_source_line_id
AND flv.lookup_type = 'WMS_TASK_STATUS'
AND flv.lookup_code =
NVL (wdt.status, NVL (mmtt.wms_task_status, 1))
AND flv.language = 'US'
AND wdt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND oeh.header_id = oel.header_id
AND fu1.employee_id(+) = wdt.person_id
AND oeh.sold_to_org_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND flv.MEANING IN ('Pending', 'Unreleased')
AND oeh.order_type_id = ot.transaction_type_id
AND wt.transaction_temp_id = mmtt.transaction_temp_id
AND hp.party_name = NVL (:P_CUST, hp.party_name)
AND wt.user_task_type_code =
NVL (:P_EQUIPMENT, wt.user_task_type_code)
AND oeh.order_number = NVL (:P_ORD_NO, oeh.order_number)
AND ot.name = NVL (:P_ORD_TYP, ot.name)
AND ot.language = 'US'
AND NVL (oel.shipping_method_code, -99) =
NVL (:P_ROUTE, NVL (oel.shipping_method_code, -99))
AND mmtt.SUBINVENTORY_CODE =
NVL (:P_SUB_INV, mmtt.SUBINVENTORY_CODE)
AND TRUNC (mmtt.creation_date) BETWEEN TRUNC(NVL (
:P_FRM_DT,
mmtt.creation_date
))
AND TRUNC(NVL (
:P_TO_DT,
mmtt.creation_date
))
AND mmtt.organization_id =
NVL (:P_ORGANIZATION_ID, mmtt.organization_id)
ORDER BY oel.shipping_method_code, hp.party_name, oeh.order_number
WMS - LPN In Pre Stagging Report
/* Formatted on 4/9/2012 2:44:15 PM (QP5 v5.114.809.3010) */
SELECT ROW_NUMBER () OVER (ORDER BY wdd.ship_method_code) AS sr_no,
COUNT (wlc.quantity) no_of_lines,
SUM (wlc.quantity) transaction_quantity,
mil.concatenated_segments concatenated_segments,
wlpn.license_plate_number,
wsc.parent_lpn,
wlpn.lpn_id,
wdd.ship_method_code,
wdd.source_line_id,
wdd.subinventory
FROM wms_license_plate_numbers wlpn,
wms_lpn_contents wlc,
mtl_item_locations_kfv mil,
wsh_deliverables_v wdd,
wsh_dsno_containers_v wsc
WHERE 1 = 1
AND wlpn.lpn_id = wlc.parent_lpn_id
AND wdd.container_instance_id = wsc.container_instance_id
AND wdd.lpn_id = wlpn.lpn_id
AND mil.inventory_location_id = wdd.locator_id
AND wdd.ship_method_code = NVL (:p_route, wdd.ship_method_code)
AND mil.concatenated_segments =
NVL (:p_loc_segs, mil.concatenated_segments)
AND wdd.organization_id =
NVL (:p_organization_id, wdd.organization_id) --122
AND mil.subinventory_code = 'STGN'
AND UPPER (wdd.released_status_name) = 'PICKED'
AND NOT EXISTS (SELECT *
FROM wsh_dsno_containers_v
WHERE wlpn.license_plate_number = parent_lpn)
GROUP BY mil.concatenated_segments,
wlpn.license_plate_number,
wsc.parent_lpn,
wlpn.lpn_id,
wdd.ship_method_code,
wdd.source_line_id,
wdd.subinventory;
SELECT ROW_NUMBER () OVER (ORDER BY wdd.ship_method_code) AS sr_no,
COUNT (wlc.quantity) no_of_lines,
SUM (wlc.quantity) transaction_quantity,
mil.concatenated_segments concatenated_segments,
wlpn.license_plate_number,
wsc.parent_lpn,
wlpn.lpn_id,
wdd.ship_method_code,
wdd.source_line_id,
wdd.subinventory
FROM wms_license_plate_numbers wlpn,
wms_lpn_contents wlc,
mtl_item_locations_kfv mil,
wsh_deliverables_v wdd,
wsh_dsno_containers_v wsc
WHERE 1 = 1
AND wlpn.lpn_id = wlc.parent_lpn_id
AND wdd.container_instance_id = wsc.container_instance_id
AND wdd.lpn_id = wlpn.lpn_id
AND mil.inventory_location_id = wdd.locator_id
AND wdd.ship_method_code = NVL (:p_route, wdd.ship_method_code)
AND mil.concatenated_segments =
NVL (:p_loc_segs, mil.concatenated_segments)
AND wdd.organization_id =
NVL (:p_organization_id, wdd.organization_id) --122
AND mil.subinventory_code = 'STGN'
AND UPPER (wdd.released_status_name) = 'PICKED'
AND NOT EXISTS (SELECT *
FROM wsh_dsno_containers_v
WHERE wlpn.license_plate_number = parent_lpn)
GROUP BY mil.concatenated_segments,
wlpn.license_plate_number,
wsc.parent_lpn,
wlpn.lpn_id,
wdd.ship_method_code,
wdd.source_line_id,
wdd.subinventory;
WMS - Pending Task Report
SELECT wt.transaction_temp_id task_no,
hp.party_name customer,
wt.item item_number,
wt.ITEM_DESCRIPTION,
wt.status_code,
wt.status,
wt.TRANSACTION_QUANTITY,
oel.SHIPPING_METHOD_CODE,
oeh.order_number,
fu1.user_name,
wt.user_task_type_code,
wt.subinventory_code
FROM wms_tasks_v WT,
apps.oe_order_lines_all oel,
apps.oe_order_headers_all oeh,
apps.fnd_user fu1,
hz_parties hp,
hz_cust_accounts hca
WHERE 1 = 1 --TRANSACTION_SOURCE_TYPE in ('RMA')
AND TASK_TYPE_DESCRIPTION = 'Pick'
AND STATUS_CODE <> 'Completed'
AND oel.line_id = WT.TRANSACTION_SOURCE_LINE_ID
AND oeh.header_id = oel.header_id
AND wt.person_id = fu1.employee_id(+)
AND oeh.sold_to_org_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND wt.status = NVL (:p_status, wt.status)
AND wt.creation_date BETWEEN NVL (:p_from_date, wt.creation_date)
AND NVL (:p_to_date, wt.creation_date)
hp.party_name customer,
wt.item item_number,
wt.ITEM_DESCRIPTION,
wt.status_code,
wt.status,
wt.TRANSACTION_QUANTITY,
oel.SHIPPING_METHOD_CODE,
oeh.order_number,
fu1.user_name,
wt.user_task_type_code,
wt.subinventory_code
FROM wms_tasks_v WT,
apps.oe_order_lines_all oel,
apps.oe_order_headers_all oeh,
apps.fnd_user fu1,
hz_parties hp,
hz_cust_accounts hca
WHERE 1 = 1 --TRANSACTION_SOURCE_TYPE in ('RMA')
AND TASK_TYPE_DESCRIPTION = 'Pick'
AND STATUS_CODE <> 'Completed'
AND oel.line_id = WT.TRANSACTION_SOURCE_LINE_ID
AND oeh.header_id = oel.header_id
AND wt.person_id = fu1.employee_id(+)
AND oeh.sold_to_org_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND wt.status = NVL (:p_status, wt.status)
AND wt.creation_date BETWEEN NVL (:p_from_date, wt.creation_date)
AND NVL (:p_to_date, wt.creation_date)
Monday, March 26, 2012
Inventory Sub Inventory Transfer Conversion
XYKA_SUBINV_TRANSFER_PROC
CREATE TABLE APPS.XYKA_SUBINV_TRANS_STG
(
X_ITEM VARCHAR2(30 BYTE),
FROM_SUB VARCHAR2(50 BYTE),
TO_SUB_INV VARCHAR2(50 BYTE),
QUANTITY NUMBER,
ORG_CODE VARCHAR2(5 BYTE),
FROM_LOCATOR VARCHAR2(50 BYTE),
TO_LOCATOR VARCHAR2(50 BYTE),
INVENTORY_ITEM_ID NUMBER
)
CREATE TABLE APPS.XYKA_SUBINV_TRANS_STG_ERROR
(
X_ITEM VARCHAR2(50 BYTE),
FROM_SUB VARCHAR2(50 BYTE),
TO_SUB_INV VARCHAR2(50 BYTE),
QUANTITY NUMBER,
ORG_CODE VARCHAR2(5 BYTE),
FROM_LOCATOR VARCHAR2(50 BYTE),
TO_LOCATOR VARCHAR2(50 BYTE),
INVENTORY_ITEM_ID NUMBER,
ITEM_NUMBER VARCHAR2(30 BYTE),
LOCATOR_ID NUMBER,
LOCK_FLAG NUMBER(1),
MATERIAL_ACCOUNT NUMBER,
ORGANIZATION_ID NUMBER,
PROCESS_FLAG NUMBER(1),
SCHEDULED_FLAG NUMBER,
SOURCE_CODE VARCHAR2(30 BYTE),
SOURCE_HEADER_ID NUMBER,
SOURCE_LINE_ID NUMBER,
SUBINVENTORY_CODE VARCHAR2(10 BYTE),
TRANSACTION_DATE DATE,
TRANSACTION_HEADER_ID NUMBER,
TRANSACTION_INTERFACE_ID NUMBER,
TRANSACTION_MODE NUMBER,
TRANSACTION_QUANTITY NUMBER,
TRANSACTION_TYPE_ID NUMBER,
TRANSACTION_UOM VARCHAR2(3 BYTE),
TRANSFER_ORG_ID NUMBER,
TRANSFER_LOCATOR NUMBER,
TRANSFER_SUBINVENTORY VARCHAR2(10 BYTE),
TRANSACTION_COST NUMBER,
TRANSACTION_REFERENCE VARCHAR2(240 BYTE),
REASON_ID NUMBER,
DISTRIBUTION_ACCOUNT_ID NUMBER,
STATUS VARCHAR2(30 BYTE),
ERROR_MASSAGE VARCHAR2(240 BYTE)
)
CREATE OR REPLACE PROCEDURE APPS.xyka_Subinv_transfer_proc
(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER
)
IS
cursor onhand_cur
is
select * from XYKA_SUBINV_TRANS_STG;
v_transaction_header_id NUMBER;
v_transaction_interface_id NUMBER;
v_inventory_item_id NUMBER;
v_segment1 VARCHAR2 (30);
v_sysdate VARCHAR2 (30);
v_org_id NUMBER;
v_sub_inv VARCHAR2 (30);
v_uom VARCHAR2 (30);
v_material_account NUMBER;
v_from_locator_id NUMBER;
v_from_locator_code VARCHAR2 (30);
v_to_locator_id NUMBER;
v_to_locator_code VARCHAR2 (30);
v_retval VARCHAR2 (3);
l_return_status VARCHAR2 (50);
l_msg_cnt NUMBER;
l_msg_data VARCHAR2 (3000);
l_trans_count NUMBER;
v_row_count NUMBER := 0;
v_resp_id NUMBER := FND_PROFILE.VALUE ('RESP_ID');
v_appl_id NUMBER := FND_PROFILE.VALUE ('RESP_APPL_ID');
v_user_id NUMBER := fnd_profile.VALUE ('USER_ID');
v_login_id NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
Begin
Begin
/*
* APPS_INITIALIZE Required because indirectly use profile options
-- FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id,l_resp_appl_id);
*/
FND_GLOBAL.APPS_INITIALIZE(1295,50683,401);
End;
FOR yk_onhand_cur IN onhand_cur
LOOP
SELECT mtl_material_transactions_s.NEXTVAL
INTO v_transaction_interface_id
FROM DUAL;
Begin
SELECT organization_id
INTO v_org_id
FROM org_organization_definitions
WHERE organization_code = yk_onhand_cur.ORG_CODE;
Exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid Org Code ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid Org Code ' || SQLERRM);
End;
Begin
SELECT msib.inventory_item_id
INTO v_inventory_item_id
FROM mtl_system_items_b msib
WHERE msib.segment1 = yk_onhand_cur.X_ITEM
AND msib.organization_id = v_org_id
AND ROWNUM = 1;
exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid Item Code ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid Item Code ' || SQLERRM);
End ;
Begin
SELECT msib.PRIMARY_UOM_CODE
INTO v_uom
FROM mtl_system_items_b msib
WHERE msib.segment1 = yk_onhand_cur.X_ITEM
AND msib.organization_id = v_org_id
AND ROWNUM = 1;
exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid PRIMARY_UOM_CODE ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid PRIMARY_UOM_CODE ' || SQLERRM);
End ;
Begin
SELECT msi.material_account
INTO v_material_account
FROM mtl_secondary_inventories msi
WHERE msi.organization_id = v_org_id
AND msi.SECONDARY_INVENTORY_NAME = yk_onhand_cur.TO_SUB_INV;
Exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid material_account ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid material_account ' || SQLERRM);
End;
Begin
SELECT inventory_location_id
INTO v_from_locator_id
FROM mtl_item_locations
WHERE --SEGMENT1 ||'-'||SEGMENT2||'-'||SEGMENT3||'-'||SEGMENT4||'-'||SEGMENT5 = yk_onhand_cur.FROM_LOCATOR -- On Prod
SEGMENT1 ||'|'||SEGMENT2||'|'||SEGMENT3||'|'||SEGMENT4||'|'||SEGMENT5 = yk_onhand_cur.FROM_LOCATOR -- On Dev
AND subinventory_code = NVL(yk_onhand_cur.FROM_SUB,subinventory_code)
AND organization_id = v_org_id ;
SELECT segment1
||'|'||segment2
||'|'||segment3
||'|'||segment4
INTO v_from_locator_code
FROM mtl_item_locations
WHERE inventory_location_id = v_from_locator_id
AND subinventory_code = NVL(yk_onhand_cur.FROM_SUB,subinventory_code)
AND organization_id = v_org_id ;
Begin
SELECT inventory_location_id
INTO v_to_locator_id
FROM mtl_item_locations
WHERE segment1||'|'||segment2||'|'||segment3||'|'||segment4 = v_from_locator_code
AND subinventory_code = NVL(yk_onhand_cur.TO_SUB_inv,subinventory_code)
AND organization_id = v_org_id ;
Exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid To Locator ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid To Locator ' || SQLERRM);
End ;
Exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid From Locator ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid From Locator ' || SQLERRM);
End;
Begin
INSERT INTO MTL_TRANSACTIONS_INTERFACE (created_by,
last_updated_by,
last_update_date,
last_update_login,
creation_date,
inventory_item_id,
locator_id,
lock_flag,
material_account,
organization_id,
process_flag,
program_id,
program_update_date,
scheduled_flag,
source_code,
source_header_id,
source_line_id,
subinventory_code,
transaction_date,
transaction_header_id,
transaction_interface_id,
transaction_mode,
transaction_quantity,
transaction_type_id,
transaction_uom,
transfer_locator,
transfer_subinventory,
transaction_cost,
transaction_reference,
reason_id,
distribution_account_id)
Values (
-1 --created_by
,-1 --last_updated_by
,SYSDATE --last_update_date
,-1 --last_update_login
,SYSDATE --creation_date
,v_inventory_item_id --inventory_item_id
,v_from_locator_id --locator_id
,2 --lock_flag
,v_material_account --material_account
,v_org_id
,1 --process_flag
,NULL --program_id
,NULL --program_update_date
,NULL --scheduled_flag
,'Interface' --source_code
,oe_order_headers_s.nextval -- source_header_id
,oe_order_lines_s.nextval -- source_line_id
,yk_onhand_cur.from_sub --source subinventory_code
,SYSDATE --transaction_date
,v_transaction_header_id --transaction_header_id
,v_transaction_interface_id --transaction_interface_id
,3 --transaction_mode
,yk_onhand_cur.quantity
,2 --transaction_type_id
,v_uom --transaction_uom
,v_to_locator_id --transfer_locator
,yk_onhand_cur.TO_SUB_INV --transfer_subinventory
,NULL --transaction_cost
,NULL
,NULL
,NULL --distribution_account_id validation_required
);
BEGIN
INSERT INTO XYKA_SUBINV_TRANS_STG_ERROR (
X_ITEM ,
FROM_SUB ,
TO_SUB_INV ,
QUANTITY ,
ORG_CODE ,
FROM_LOCATOR ,
TO_LOCATOR ,
inventory_item_id ,
item_number ,
locator_id ,
lock_flag ,
material_account ,
organization_id ,
process_flag ,
scheduled_flag ,
source_code ,
-- source_line_id ,
-- source_header_id ,
subinventory_code ,
transaction_date ,
transaction_header_id ,
transaction_interface_id ,
transaction_mode ,
transaction_quantity ,
transaction_type_id ,
transaction_uom ,
transfer_locator ,
transfer_subinventory ,
transaction_cost ,
transaction_reference ,
reason_id ,
distribution_account_id ,
status ,
error_massage )
Values(
yk_onhand_cur.X_item
,yk_onhand_cur.from_sub
,yk_onhand_cur.to_sub_inv
,yk_onhand_cur.quantity
,yk_onhand_cur.org_code
,yk_onhand_cur.from_locator
,yk_onhand_cur.to_locator
,v_inventory_item_id
,yk_onhand_cur.X_item -- item_number
,v_from_locator_id --locator_id
,2 -- lock_flag
,v_material_account --material_account
,v_org_id
,1 --process_flag
,NULL --scheduled_flag ,
,'Interface' --source_code ,
,yk_onhand_cur.from_sub --subinventory_code ,
,SYSDATE -- transaction_date ,
,v_transaction_header_id --transaction_header_id ,
,v_transaction_interface_id --transaction_interface_id ,
,3 -- transaction_mode ,
,yk_onhand_cur.quantity -- transaction_quantity ,
,2 --transaction_type_idtransaction_type_id ,
,v_uom --transaction_uom ,
,v_to_locator_id --transfer_locatortransfer_locator ,
,yk_onhand_cur.to_sub_inv -- transfer_subinventory ,
,yk_onhand_cur.quantity -- transaction_cost ,
,NULL --transaction_reference ,
,NULL --reason_id ,
,NULL --distribution_account_id ,
,'Success' --status ,
,'NO ERROR' --error_massage
);
END;
Exception
WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.PUT_LINE ('Too many rows found while insert into MTL_TRANSACTIONS_INTERFACE ' || SQLERRM);
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Unexpected error while inserting data in MTL_TRANSACTIONS_INTERFACE '||'-' || SQLERRM );
End;
v_row_count := onhand_cur%ROWCOUNT;
--DBMS_OUTPUT.PUT_LINE ('Number Of rows in MTL_TRANSACTIONS_INTERFACE '||'= ' || v_row_count );
END LOOP;
Commit;
IF v_row_count > 0
THEN
v_retval :=
INV_TXN_MANAGER_PUB.process_transactions (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
x_trans_count => l_trans_count,
p_table => 1,
p_header_id => v_transaction_header_id
);
COMMIT;
IF l_return_status = 'S'
THEN
l_return_status := 'Transactions processed successfully';
DBMS_OUTPUT.PUT_LINE ('Success '||'= ' || l_return_status );
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_msg_data );
ELSIF l_return_status = 'E'
THEN
l_return_status := 'Transactions Processing Error';
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_return_status );
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_msg_data );
ELSIF l_return_status IS NULL
THEN
l_return_status := 'Transactions Processing Error';
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_return_status );
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_msg_data );
END IF;
IF l_return_status = 'E' THEN
UPDATE XYKA_SUBINV_TRANS_STG_ERROR yst
SET (yst.status , yst.error_massage) = ( SELECT 'FAILD', mti.error_explanation FROM
mtl_transactions_interface mti
WHERE mti.source_header_id = yst.source_header_id
AND mti.source_line_id = yst.source_line_id
AND mti.transaction_header_id = v_transaction_header_id)
WHERE yst.transaction_header_id = v_transaction_header_id
AND EXISTS (SELECT * FROM mtl_transactions_interface mtic
WHERE mtic.source_header_id = yst.source_header_id
AND mtic.source_line_id = yst.source_line_id);
COMMIT;
END IF;
END IF;
End;
/
XYKA_SUBINV_TRANSFER_PROC
CREATE TABLE APPS.XYKA_SUBINV_TRANS_STG
(
X_ITEM VARCHAR2(30 BYTE),
FROM_SUB VARCHAR2(50 BYTE),
TO_SUB_INV VARCHAR2(50 BYTE),
QUANTITY NUMBER,
ORG_CODE VARCHAR2(5 BYTE),
FROM_LOCATOR VARCHAR2(50 BYTE),
TO_LOCATOR VARCHAR2(50 BYTE),
INVENTORY_ITEM_ID NUMBER
)
CREATE TABLE APPS.XYKA_SUBINV_TRANS_STG_ERROR
(
X_ITEM VARCHAR2(50 BYTE),
FROM_SUB VARCHAR2(50 BYTE),
TO_SUB_INV VARCHAR2(50 BYTE),
QUANTITY NUMBER,
ORG_CODE VARCHAR2(5 BYTE),
FROM_LOCATOR VARCHAR2(50 BYTE),
TO_LOCATOR VARCHAR2(50 BYTE),
INVENTORY_ITEM_ID NUMBER,
ITEM_NUMBER VARCHAR2(30 BYTE),
LOCATOR_ID NUMBER,
LOCK_FLAG NUMBER(1),
MATERIAL_ACCOUNT NUMBER,
ORGANIZATION_ID NUMBER,
PROCESS_FLAG NUMBER(1),
SCHEDULED_FLAG NUMBER,
SOURCE_CODE VARCHAR2(30 BYTE),
SOURCE_HEADER_ID NUMBER,
SOURCE_LINE_ID NUMBER,
SUBINVENTORY_CODE VARCHAR2(10 BYTE),
TRANSACTION_DATE DATE,
TRANSACTION_HEADER_ID NUMBER,
TRANSACTION_INTERFACE_ID NUMBER,
TRANSACTION_MODE NUMBER,
TRANSACTION_QUANTITY NUMBER,
TRANSACTION_TYPE_ID NUMBER,
TRANSACTION_UOM VARCHAR2(3 BYTE),
TRANSFER_ORG_ID NUMBER,
TRANSFER_LOCATOR NUMBER,
TRANSFER_SUBINVENTORY VARCHAR2(10 BYTE),
TRANSACTION_COST NUMBER,
TRANSACTION_REFERENCE VARCHAR2(240 BYTE),
REASON_ID NUMBER,
DISTRIBUTION_ACCOUNT_ID NUMBER,
STATUS VARCHAR2(30 BYTE),
ERROR_MASSAGE VARCHAR2(240 BYTE)
)
CREATE OR REPLACE PROCEDURE APPS.xyka_Subinv_transfer_proc
(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER
)
IS
cursor onhand_cur
is
select * from XYKA_SUBINV_TRANS_STG;
v_transaction_header_id NUMBER;
v_transaction_interface_id NUMBER;
v_inventory_item_id NUMBER;
v_segment1 VARCHAR2 (30);
v_sysdate VARCHAR2 (30);
v_org_id NUMBER;
v_sub_inv VARCHAR2 (30);
v_uom VARCHAR2 (30);
v_material_account NUMBER;
v_from_locator_id NUMBER;
v_from_locator_code VARCHAR2 (30);
v_to_locator_id NUMBER;
v_to_locator_code VARCHAR2 (30);
v_retval VARCHAR2 (3);
l_return_status VARCHAR2 (50);
l_msg_cnt NUMBER;
l_msg_data VARCHAR2 (3000);
l_trans_count NUMBER;
v_row_count NUMBER := 0;
v_resp_id NUMBER := FND_PROFILE.VALUE ('RESP_ID');
v_appl_id NUMBER := FND_PROFILE.VALUE ('RESP_APPL_ID');
v_user_id NUMBER := fnd_profile.VALUE ('USER_ID');
v_login_id NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
Begin
Begin
/*
* APPS_INITIALIZE Required because indirectly use profile options
-- FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id,l_resp_appl_id);
*/
FND_GLOBAL.APPS_INITIALIZE(1295,50683,401);
End;
FOR yk_onhand_cur IN onhand_cur
LOOP
SELECT mtl_material_transactions_s.NEXTVAL
INTO v_transaction_interface_id
FROM DUAL;
Begin
SELECT organization_id
INTO v_org_id
FROM org_organization_definitions
WHERE organization_code = yk_onhand_cur.ORG_CODE;
Exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid Org Code ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid Org Code ' || SQLERRM);
End;
Begin
SELECT msib.inventory_item_id
INTO v_inventory_item_id
FROM mtl_system_items_b msib
WHERE msib.segment1 = yk_onhand_cur.X_ITEM
AND msib.organization_id = v_org_id
AND ROWNUM = 1;
exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid Item Code ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid Item Code ' || SQLERRM);
End ;
Begin
SELECT msib.PRIMARY_UOM_CODE
INTO v_uom
FROM mtl_system_items_b msib
WHERE msib.segment1 = yk_onhand_cur.X_ITEM
AND msib.organization_id = v_org_id
AND ROWNUM = 1;
exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid PRIMARY_UOM_CODE ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid PRIMARY_UOM_CODE ' || SQLERRM);
End ;
Begin
SELECT msi.material_account
INTO v_material_account
FROM mtl_secondary_inventories msi
WHERE msi.organization_id = v_org_id
AND msi.SECONDARY_INVENTORY_NAME = yk_onhand_cur.TO_SUB_INV;
Exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid material_account ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid material_account ' || SQLERRM);
End;
Begin
SELECT inventory_location_id
INTO v_from_locator_id
FROM mtl_item_locations
WHERE --SEGMENT1 ||'-'||SEGMENT2||'-'||SEGMENT3||'-'||SEGMENT4||'-'||SEGMENT5 = yk_onhand_cur.FROM_LOCATOR -- On Prod
SEGMENT1 ||'|'||SEGMENT2||'|'||SEGMENT3||'|'||SEGMENT4||'|'||SEGMENT5 = yk_onhand_cur.FROM_LOCATOR -- On Dev
AND subinventory_code = NVL(yk_onhand_cur.FROM_SUB,subinventory_code)
AND organization_id = v_org_id ;
SELECT segment1
||'|'||segment2
||'|'||segment3
||'|'||segment4
INTO v_from_locator_code
FROM mtl_item_locations
WHERE inventory_location_id = v_from_locator_id
AND subinventory_code = NVL(yk_onhand_cur.FROM_SUB,subinventory_code)
AND organization_id = v_org_id ;
Begin
SELECT inventory_location_id
INTO v_to_locator_id
FROM mtl_item_locations
WHERE segment1||'|'||segment2||'|'||segment3||'|'||segment4 = v_from_locator_code
AND subinventory_code = NVL(yk_onhand_cur.TO_SUB_inv,subinventory_code)
AND organization_id = v_org_id ;
Exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid To Locator ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid To Locator ' || SQLERRM);
End ;
Exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid From Locator ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid From Locator ' || SQLERRM);
End;
Begin
INSERT INTO MTL_TRANSACTIONS_INTERFACE (created_by,
last_updated_by,
last_update_date,
last_update_login,
creation_date,
inventory_item_id,
locator_id,
lock_flag,
material_account,
organization_id,
process_flag,
program_id,
program_update_date,
scheduled_flag,
source_code,
source_header_id,
source_line_id,
subinventory_code,
transaction_date,
transaction_header_id,
transaction_interface_id,
transaction_mode,
transaction_quantity,
transaction_type_id,
transaction_uom,
transfer_locator,
transfer_subinventory,
transaction_cost,
transaction_reference,
reason_id,
distribution_account_id)
Values (
-1 --created_by
,-1 --last_updated_by
,SYSDATE --last_update_date
,-1 --last_update_login
,SYSDATE --creation_date
,v_inventory_item_id --inventory_item_id
,v_from_locator_id --locator_id
,2 --lock_flag
,v_material_account --material_account
,v_org_id
,1 --process_flag
,NULL --program_id
,NULL --program_update_date
,NULL --scheduled_flag
,'Interface' --source_code
,oe_order_headers_s.nextval -- source_header_id
,oe_order_lines_s.nextval -- source_line_id
,yk_onhand_cur.from_sub --source subinventory_code
,SYSDATE --transaction_date
,v_transaction_header_id --transaction_header_id
,v_transaction_interface_id --transaction_interface_id
,3 --transaction_mode
,yk_onhand_cur.quantity
,2 --transaction_type_id
,v_uom --transaction_uom
,v_to_locator_id --transfer_locator
,yk_onhand_cur.TO_SUB_INV --transfer_subinventory
,NULL --transaction_cost
,NULL
,NULL
,NULL --distribution_account_id validation_required
);
BEGIN
INSERT INTO XYKA_SUBINV_TRANS_STG_ERROR (
X_ITEM ,
FROM_SUB ,
TO_SUB_INV ,
QUANTITY ,
ORG_CODE ,
FROM_LOCATOR ,
TO_LOCATOR ,
inventory_item_id ,
item_number ,
locator_id ,
lock_flag ,
material_account ,
organization_id ,
process_flag ,
scheduled_flag ,
source_code ,
-- source_line_id ,
-- source_header_id ,
subinventory_code ,
transaction_date ,
transaction_header_id ,
transaction_interface_id ,
transaction_mode ,
transaction_quantity ,
transaction_type_id ,
transaction_uom ,
transfer_locator ,
transfer_subinventory ,
transaction_cost ,
transaction_reference ,
reason_id ,
distribution_account_id ,
status ,
error_massage )
Values(
yk_onhand_cur.X_item
,yk_onhand_cur.from_sub
,yk_onhand_cur.to_sub_inv
,yk_onhand_cur.quantity
,yk_onhand_cur.org_code
,yk_onhand_cur.from_locator
,yk_onhand_cur.to_locator
,v_inventory_item_id
,yk_onhand_cur.X_item -- item_number
,v_from_locator_id --locator_id
,2 -- lock_flag
,v_material_account --material_account
,v_org_id
,1 --process_flag
,NULL --scheduled_flag ,
,'Interface' --source_code ,
,yk_onhand_cur.from_sub --subinventory_code ,
,SYSDATE -- transaction_date ,
,v_transaction_header_id --transaction_header_id ,
,v_transaction_interface_id --transaction_interface_id ,
,3 -- transaction_mode ,
,yk_onhand_cur.quantity -- transaction_quantity ,
,2 --transaction_type_idtransaction_type_id ,
,v_uom --transaction_uom ,
,v_to_locator_id --transfer_locatortransfer_locator ,
,yk_onhand_cur.to_sub_inv -- transfer_subinventory ,
,yk_onhand_cur.quantity -- transaction_cost ,
,NULL --transaction_reference ,
,NULL --reason_id ,
,NULL --distribution_account_id ,
,'Success' --status ,
,'NO ERROR' --error_massage
);
END;
Exception
WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.PUT_LINE ('Too many rows found while insert into MTL_TRANSACTIONS_INTERFACE ' || SQLERRM);
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Unexpected error while inserting data in MTL_TRANSACTIONS_INTERFACE '||'-' || SQLERRM );
End;
v_row_count := onhand_cur%ROWCOUNT;
--DBMS_OUTPUT.PUT_LINE ('Number Of rows in MTL_TRANSACTIONS_INTERFACE '||'= ' || v_row_count );
END LOOP;
Commit;
IF v_row_count > 0
THEN
v_retval :=
INV_TXN_MANAGER_PUB.process_transactions (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
x_trans_count => l_trans_count,
p_table => 1,
p_header_id => v_transaction_header_id
);
COMMIT;
IF l_return_status = 'S'
THEN
l_return_status := 'Transactions processed successfully';
DBMS_OUTPUT.PUT_LINE ('Success '||'= ' || l_return_status );
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_msg_data );
ELSIF l_return_status = 'E'
THEN
l_return_status := 'Transactions Processing Error';
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_return_status );
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_msg_data );
ELSIF l_return_status IS NULL
THEN
l_return_status := 'Transactions Processing Error';
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_return_status );
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_msg_data );
END IF;
IF l_return_status = 'E' THEN
UPDATE XYKA_SUBINV_TRANS_STG_ERROR yst
SET (yst.status , yst.error_massage) = ( SELECT 'FAILD', mti.error_explanation FROM
mtl_transactions_interface mti
WHERE mti.source_header_id = yst.source_header_id
AND mti.source_line_id = yst.source_line_id
AND mti.transaction_header_id = v_transaction_header_id)
WHERE yst.transaction_header_id = v_transaction_header_id
AND EXISTS (SELECT * FROM mtl_transactions_interface mtic
WHERE mtic.source_header_id = yst.source_header_id
AND mtic.source_line_id = yst.source_line_id);
COMMIT;
END IF;
END IF;
End;
/
Inventory Locator Update Conversion
/* Formatted on 3/26/2012 4:07:32 PM (QP5 v5.114.809.3010) */
CREATE OR REPLACE PROCEDURE APPS.XX_UPDATE_STOCK_LOCATORS_EHA
IS
/*
** ---------------------------------------------------------------------------
** procedure : update_locator
** description : this procedure updates an existing locator
**
** i/p :
** NOTE:
** if the default value of the input parameter is used, then
** that column retains its original value and is not changed
** during update.
** this can be achieved by not passing this parameter during the
** API call.
**
** p_organization_id
** identifier of organization in which locator is to
** be updated.
** p_organization_code
** organization code of organziation in which locator
** is to be updated. Either p_organization_id or
** p_organziation_code MUST be passed
** p_inventory_location_id
** identifier of locator to be updated
** p_concatenated_segments
** concatenated segment string with separator
** of the locator to be updated. Eg:A.1.1
** either p_inventory_location_id or p_concatenated_segments
** MUST be passed.
** p_description
** locator description
** p_inventory_location_type
** type of locator.
** dock door(1) or staging lane(2) or storage locator(3)
** p_picking_order
** number that identifies physical position of locator
** for travel optimization during picking and task dispatching.
** It has a a higher precedence over x,y,z coordinates.
** p_location_maximum_units
** Maxmimum units the locator can hold
** p_subinventory_code
** Subinventory to which locator belongs
** p_location_weight_uom_code
** UOM of locator's max weight capacity
** p_max_weight
** Max weight locator can hold
** p_volume_uom_code
** UOM of locator's max volume capacity
** p_max_cubic_area
** Max volume capacity of the locator
** p_x_coordinate
** X-position of the locator in space. Used
** for travel optimization during picking and task dispatching.
** p_y_coordinate
** Y-position of the locator in space. Used
** for travel optimization during picking and task dispatching.
** p_z_coordinate
** Z-position of the locator in space. Used
** for travel optimization during picking and task dispatching.
** p_physical_location_id
** locators that are the same physically have the same
** inventory_location_id in this column
** p_pick_uom_code
** UOM in which material is picked from locator
** p_dimension_uom_code
** UOM in which locator dimensions are expressed
** p_length
** Length of the locator
** p_width
** Width of the locator
** p_height
** Height of the locator
** p_status_id
** Material Status that needs to be associated to locator
** p_dropping_order
** For ordering drop-off locators and also to order by putaway
** drop-off operations (bug 2681871)
** For the DFF attributes mentioned below, to update correctly use the following strategy
** To retain the value in the table, do not pass any value OR pass NULL as i/p
** To update the attribute with NULL, pass fnd_api.g_miss_char
** To update with any other value, pass the appropriate value
** p_attribute_category Holds the Context of the Descriptive FlexField for the Locator
** p_attribute1 Holds the Descriptive FlexField attribute for the Locator
** p_attribute2 Holds the Descriptive FlexField attribute for the Locator
** p_attribute3 Holds the Descriptive FlexField attribute for the Locator
** p_attribute4 Holds the Descriptive FlexField attribute for the Locator
** p_attribute5 Holds the Descriptive FlexField attribute for the Locator
** p_attribute6 Holds the Descriptive FlexField attribute for the Locator
** p_attribute7 Holds the Descriptive FlexField attribute for the Locator
** p_attribute8 Holds the Descriptive FlexField attribute for the Locator
** p_attribute9 Holds the Descriptive FlexField attribute for the Locator
** p_attribute10 Holds the Descriptive FlexField attribute for the Locator
** p_attribute11 Holds the Descriptive FlexField attribute for the Locator
** p_attribute12 Holds the Descriptive FlexField attribute for the Locator
** p_attribute13 Holds the Descriptive FlexField attribute for the Locator
** p_attribute14 Holds the Descriptive FlexField attribute for the Locator
** p_attribute15 Holds the Descriptive FlexField attribute for the Locator
**
** o/p:
** x_return_status
** return status indicating success, error, unexpected error
** x_msg_count
** number of messages in message list
** x_msg_data
** if the number of messages in message list is 1, contains
** message text
**
** ---------------------------------------------------------------------------
*/
L_MSG_DATA VARCHAR2 (100);
L_MSG_COUNT NUMBER;
L_RETURN_STATUS VARCHAR2 (1);
L_LOCATOR_ID NUMBER;
L_LOCATOR_EXISTS VARCHAR2 (1);
L_ORG_ID NUMBER := 107; /*ORGANIZATION_ID */
L_ORGANIZATION_CODE VARCHAR2 (10); /*ORGANIZATION_CODE */
L_SUB_CODE VARCHAR2 (10); /*VARIABLE FOR SUBINVENTORY*/
L_CONCATENATED_SEGMENTS VARCHAR2 (100); /*VARIABLE FOR LOCATOR SEGMENT*/
L_PICKING_ORDER NUMBER;
l_location_maximum_units NUMBER;
l_inventory_location_id NUMBER;
l_supplier varchar2 (2000);
l_vendor_code varchar2 (10);
/*PUTAWAY SEQUENCE NO */
--L_USER_ID NUMBER := 1262; /* USER ID FROM FND_USERS TABLE */
--L_RESP_ID NUMBER := 20634; /*RESPONSIBILITY ID*/
--L_RESP_APPL_ID NUMBER := 401; /* RESPONSIBILITY APPLICATION ID */
BEGIN
/*
* APPS_INITIALIZE REQUIRED BECAUSE INDIRECTLY USE PROFILE OPTIONS
-- FND_GLOBAL.APPS_INITIALIZE(L_USER_ID, L_RESP_ID,L_RESP_APPL_ID);
*/
--FND_GLOBAL.APPS_INITIALIZE(USER_ID IN NUMBER,RESP_ID IN NUMBER, RESP_APPL_ID IN NUMBER SECURITY_GROUP_ID IN NUMBER);
FND_GLOBAL.APPS_INITIALIZE (1295, 50683, 401);
FND_MSG_PUB.INITIALIZE;
FOR X
IN (SELECT DISTINCT SUBINVENTORY SUBINVENTORY
FROM XX_STOCK_LOCATOR_STAGING)
/*
CREATE TABLE APPS.XX_STOCK_LOCATOR_STAGING
(
SUBINVENTORY VARCHAR2 (12 BYTE),
LOCATOR_CONCAT_SEGMENTS VARCHAR2 (2000 BYTE),
PICKING_PRIORITY NUMBER,
SUPPLIER VARCHAR2 (240 BYTE),
CAPACITY NUMBER,
SRL NUMBER,
STATUS VARCHAR2 (1 BYTE),
ORGANIZATION_ID NUMBER
)*/
LOOP
FOR I
IN (SELECT SUBINVENTORY SUBINVENTORY_CODE,
ORGANIZATION_ID,
SRL INVENTORY_LOCATION_ID,
PICKING_PRIORITY,
CAPACITY,
xyka_custom_san.get_organization_code (organization_id) -- L_ORG_ID
ORGANIZATION_CODE,
SUPPLIER ATTRIBUTE4,
LOCATOR_CONCAT_SEGMENTS
FROM XX_STOCK_LOCATOR_STAGING
WHERE SUBINVENTORY = X.SUBINVENTORY)
LOOP
L_CONCATENATED_SEGMENTS := I.LOCATOR_CONCAT_SEGMENTS;
L_PICKING_ORDER := I.PICKING_PRIORITY;
l_location_maximum_units := I.CAPACITY;
L_SUB_CODE := I.SUBINVENTORY_CODE;
L_ORG_ID := I.ORGANIZATION_ID;
l_inventory_location_id := I.INVENTORY_LOCATION_ID;
L_ORGANIZATION_CODE := I.ORGANIZATION_CODE;
--l_vendor_code := I.ATTRIBUTE4;
/*
BEGIN
SELECT inventory_location_id
INTO l_inventory_location_id
FROM MTL_ITEM_LOCATIONS_KFV
WHERE concatenated_segments = I.LOCATOR_CONCAT_SEGMENTS
and organization_id =L_ORG_ID;
END;
*/
IF l_inventory_location_id IS NOT NULL
THEN
INV_LOC_WMS_PUB.UPDATE_LOCATOR (
x_return_status => L_RETURN_STATUS,
x_msg_count => L_MSG_COUNT,
x_msg_data => L_MSG_DATA,
p_organization_id => L_ORG_ID,
p_organization_code => L_ORGANIZATION_CODE,
p_inventory_location_id => l_inventory_location_id,
p_concatenated_segments => NULL,
p_description => L_CONCATENATED_SEGMENTS, --NULL,
p_disabled_date => NULL,
p_inventory_location_type => 3,
p_picking_order => L_PICKING_ORDER,
p_location_maximum_units => l_location_maximum_units,
p_location_Weight_uom_code => NULL,
p_max_weight => NULL,
p_volume_uom_code => NULL,
p_max_cubic_area => NULL,
p_x_coordinate => NULL,
p_y_coordinate => NULL,
p_z_coordinate => NULL,
p_physical_location_id => NULL,
p_pick_uom_code => NULL,
p_dimension_uom_code => NULL,
p_length => NULL,
p_width => NULL,
p_height => NULL,
p_status_id => 1, -- DEFAULT STATUS 'ACTIVE'
p_dropping_order => NULL,
p_attribute_category => NULL,
p_attribute1 => NULL, --attribute1 - Bintype
p_attribute2 => NULL, --attribute2 - max quantity
p_attribute3 => NULL, --attribute4 - supplier
p_attribute4 => NULL, --l_vendor_code , --attributr3 - category
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_alias => NULL
);
ELSE
NULL;
END IF;
DBMS_OUTPUT.PUT_LINE( 'Return Status '
|| L_CONCATENATED_SEGMENTS
|| ' - '
|| l_supplier
|| ' - '
|| L_RETURN_STATUS
|| ' - '
|| L_MSG_DATA);
IF L_RETURN_STATUS IN ('E', 'U')
THEN
-- DBMS_OUTPUT.PUT_LINE ('# of Errors ' || L_MSG_COUNT);
-- DBMS_OUTPUT.PUT_LINE ('Error ' || L_MSG_DATA);
BEGIN
--UPDATE XX_STOCK_LOCATOR_STAGING_CHECK
-- SET STATUS = 'N'
-- WHERE LOCATOR_CONCAT_SEGMENTS = L_CONCATENATED_SEGMENTS;
NULL;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
DBMS_OUTPUT.PUT_LINE ('N');
ELSE
-- UPDATE MTL_ITEM_LOCATIONS
-- SET SEGMENT5 = L_SUB_CODE
-- WHERE INVENTORY_LOCATION_ID = L_LOCATOR_ID;
BEGIN
--UPDATE XX_STOCK_LOCATOR_STAGING_CHECK
--SET STATUS = 'Y'
--WHERE LOCATOR_CONCAT_SEGMENTS = L_CONCATENATED_SEGMENTS;
--NULL;
UPDATE XX_STOCK_LOCATOR_STAGING
SET status = 'Y'
WHERE srl = l_inventory_location_id
AND organization_id = L_ORG_ID;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
COMMIT;
-- DBMS_OUTPUT.PUT_LINE ('Y');
END IF;
-- COMMIT;
END LOOP;
-- COMMIT;
END LOOP;
END;
/
/* Formatted on 3/26/2012 4:07:32 PM (QP5 v5.114.809.3010) */
CREATE OR REPLACE PROCEDURE APPS.XX_UPDATE_STOCK_LOCATORS_EHA
IS
/*
** ---------------------------------------------------------------------------
** procedure : update_locator
** description : this procedure updates an existing locator
**
** i/p :
** NOTE:
** if the default value of the input parameter is used, then
** that column retains its original value and is not changed
** during update.
** this can be achieved by not passing this parameter during the
** API call.
**
** p_organization_id
** identifier of organization in which locator is to
** be updated.
** p_organization_code
** organization code of organziation in which locator
** is to be updated. Either p_organization_id or
** p_organziation_code MUST be passed
** p_inventory_location_id
** identifier of locator to be updated
** p_concatenated_segments
** concatenated segment string with separator
** of the locator to be updated. Eg:A.1.1
** either p_inventory_location_id or p_concatenated_segments
** MUST be passed.
** p_description
** locator description
** p_inventory_location_type
** type of locator.
** dock door(1) or staging lane(2) or storage locator(3)
** p_picking_order
** number that identifies physical position of locator
** for travel optimization during picking and task dispatching.
** It has a a higher precedence over x,y,z coordinates.
** p_location_maximum_units
** Maxmimum units the locator can hold
** p_subinventory_code
** Subinventory to which locator belongs
** p_location_weight_uom_code
** UOM of locator's max weight capacity
** p_max_weight
** Max weight locator can hold
** p_volume_uom_code
** UOM of locator's max volume capacity
** p_max_cubic_area
** Max volume capacity of the locator
** p_x_coordinate
** X-position of the locator in space. Used
** for travel optimization during picking and task dispatching.
** p_y_coordinate
** Y-position of the locator in space. Used
** for travel optimization during picking and task dispatching.
** p_z_coordinate
** Z-position of the locator in space. Used
** for travel optimization during picking and task dispatching.
** p_physical_location_id
** locators that are the same physically have the same
** inventory_location_id in this column
** p_pick_uom_code
** UOM in which material is picked from locator
** p_dimension_uom_code
** UOM in which locator dimensions are expressed
** p_length
** Length of the locator
** p_width
** Width of the locator
** p_height
** Height of the locator
** p_status_id
** Material Status that needs to be associated to locator
** p_dropping_order
** For ordering drop-off locators and also to order by putaway
** drop-off operations (bug 2681871)
** For the DFF attributes mentioned below, to update correctly use the following strategy
** To retain the value in the table, do not pass any value OR pass NULL as i/p
** To update the attribute with NULL, pass fnd_api.g_miss_char
** To update with any other value, pass the appropriate value
** p_attribute_category Holds the Context of the Descriptive FlexField for the Locator
** p_attribute1 Holds the Descriptive FlexField attribute for the Locator
** p_attribute2 Holds the Descriptive FlexField attribute for the Locator
** p_attribute3 Holds the Descriptive FlexField attribute for the Locator
** p_attribute4 Holds the Descriptive FlexField attribute for the Locator
** p_attribute5 Holds the Descriptive FlexField attribute for the Locator
** p_attribute6 Holds the Descriptive FlexField attribute for the Locator
** p_attribute7 Holds the Descriptive FlexField attribute for the Locator
** p_attribute8 Holds the Descriptive FlexField attribute for the Locator
** p_attribute9 Holds the Descriptive FlexField attribute for the Locator
** p_attribute10 Holds the Descriptive FlexField attribute for the Locator
** p_attribute11 Holds the Descriptive FlexField attribute for the Locator
** p_attribute12 Holds the Descriptive FlexField attribute for the Locator
** p_attribute13 Holds the Descriptive FlexField attribute for the Locator
** p_attribute14 Holds the Descriptive FlexField attribute for the Locator
** p_attribute15 Holds the Descriptive FlexField attribute for the Locator
**
** o/p:
** x_return_status
** return status indicating success, error, unexpected error
** x_msg_count
** number of messages in message list
** x_msg_data
** if the number of messages in message list is 1, contains
** message text
**
** ---------------------------------------------------------------------------
*/
L_MSG_DATA VARCHAR2 (100);
L_MSG_COUNT NUMBER;
L_RETURN_STATUS VARCHAR2 (1);
L_LOCATOR_ID NUMBER;
L_LOCATOR_EXISTS VARCHAR2 (1);
L_ORG_ID NUMBER := 107; /*ORGANIZATION_ID */
L_ORGANIZATION_CODE VARCHAR2 (10); /*ORGANIZATION_CODE */
L_SUB_CODE VARCHAR2 (10); /*VARIABLE FOR SUBINVENTORY*/
L_CONCATENATED_SEGMENTS VARCHAR2 (100); /*VARIABLE FOR LOCATOR SEGMENT*/
L_PICKING_ORDER NUMBER;
l_location_maximum_units NUMBER;
l_inventory_location_id NUMBER;
l_supplier varchar2 (2000);
l_vendor_code varchar2 (10);
/*PUTAWAY SEQUENCE NO */
--L_USER_ID NUMBER := 1262; /* USER ID FROM FND_USERS TABLE */
--L_RESP_ID NUMBER := 20634; /*RESPONSIBILITY ID*/
--L_RESP_APPL_ID NUMBER := 401; /* RESPONSIBILITY APPLICATION ID */
BEGIN
/*
* APPS_INITIALIZE REQUIRED BECAUSE INDIRECTLY USE PROFILE OPTIONS
-- FND_GLOBAL.APPS_INITIALIZE(L_USER_ID, L_RESP_ID,L_RESP_APPL_ID);
*/
--FND_GLOBAL.APPS_INITIALIZE(USER_ID IN NUMBER,RESP_ID IN NUMBER, RESP_APPL_ID IN NUMBER SECURITY_GROUP_ID IN NUMBER);
FND_GLOBAL.APPS_INITIALIZE (1295, 50683, 401);
FND_MSG_PUB.INITIALIZE;
FOR X
IN (SELECT DISTINCT SUBINVENTORY SUBINVENTORY
FROM XX_STOCK_LOCATOR_STAGING)
/*
CREATE TABLE APPS.XX_STOCK_LOCATOR_STAGING
(
SUBINVENTORY VARCHAR2 (12 BYTE),
LOCATOR_CONCAT_SEGMENTS VARCHAR2 (2000 BYTE),
PICKING_PRIORITY NUMBER,
SUPPLIER VARCHAR2 (240 BYTE),
CAPACITY NUMBER,
SRL NUMBER,
STATUS VARCHAR2 (1 BYTE),
ORGANIZATION_ID NUMBER
)*/
LOOP
FOR I
IN (SELECT SUBINVENTORY SUBINVENTORY_CODE,
ORGANIZATION_ID,
SRL INVENTORY_LOCATION_ID,
PICKING_PRIORITY,
CAPACITY,
xyka_custom_san.get_organization_code (organization_id) -- L_ORG_ID
ORGANIZATION_CODE,
SUPPLIER ATTRIBUTE4,
LOCATOR_CONCAT_SEGMENTS
FROM XX_STOCK_LOCATOR_STAGING
WHERE SUBINVENTORY = X.SUBINVENTORY)
LOOP
L_CONCATENATED_SEGMENTS := I.LOCATOR_CONCAT_SEGMENTS;
L_PICKING_ORDER := I.PICKING_PRIORITY;
l_location_maximum_units := I.CAPACITY;
L_SUB_CODE := I.SUBINVENTORY_CODE;
L_ORG_ID := I.ORGANIZATION_ID;
l_inventory_location_id := I.INVENTORY_LOCATION_ID;
L_ORGANIZATION_CODE := I.ORGANIZATION_CODE;
--l_vendor_code := I.ATTRIBUTE4;
/*
BEGIN
SELECT inventory_location_id
INTO l_inventory_location_id
FROM MTL_ITEM_LOCATIONS_KFV
WHERE concatenated_segments = I.LOCATOR_CONCAT_SEGMENTS
and organization_id =L_ORG_ID;
END;
*/
IF l_inventory_location_id IS NOT NULL
THEN
INV_LOC_WMS_PUB.UPDATE_LOCATOR (
x_return_status => L_RETURN_STATUS,
x_msg_count => L_MSG_COUNT,
x_msg_data => L_MSG_DATA,
p_organization_id => L_ORG_ID,
p_organization_code => L_ORGANIZATION_CODE,
p_inventory_location_id => l_inventory_location_id,
p_concatenated_segments => NULL,
p_description => L_CONCATENATED_SEGMENTS, --NULL,
p_disabled_date => NULL,
p_inventory_location_type => 3,
p_picking_order => L_PICKING_ORDER,
p_location_maximum_units => l_location_maximum_units,
p_location_Weight_uom_code => NULL,
p_max_weight => NULL,
p_volume_uom_code => NULL,
p_max_cubic_area => NULL,
p_x_coordinate => NULL,
p_y_coordinate => NULL,
p_z_coordinate => NULL,
p_physical_location_id => NULL,
p_pick_uom_code => NULL,
p_dimension_uom_code => NULL,
p_length => NULL,
p_width => NULL,
p_height => NULL,
p_status_id => 1, -- DEFAULT STATUS 'ACTIVE'
p_dropping_order => NULL,
p_attribute_category => NULL,
p_attribute1 => NULL, --attribute1 - Bintype
p_attribute2 => NULL, --attribute2 - max quantity
p_attribute3 => NULL, --attribute4 - supplier
p_attribute4 => NULL, --l_vendor_code , --attributr3 - category
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_alias => NULL
);
ELSE
NULL;
END IF;
DBMS_OUTPUT.PUT_LINE( 'Return Status '
|| L_CONCATENATED_SEGMENTS
|| ' - '
|| l_supplier
|| ' - '
|| L_RETURN_STATUS
|| ' - '
|| L_MSG_DATA);
IF L_RETURN_STATUS IN ('E', 'U')
THEN
-- DBMS_OUTPUT.PUT_LINE ('# of Errors ' || L_MSG_COUNT);
-- DBMS_OUTPUT.PUT_LINE ('Error ' || L_MSG_DATA);
BEGIN
--UPDATE XX_STOCK_LOCATOR_STAGING_CHECK
-- SET STATUS = 'N'
-- WHERE LOCATOR_CONCAT_SEGMENTS = L_CONCATENATED_SEGMENTS;
NULL;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
DBMS_OUTPUT.PUT_LINE ('N');
ELSE
-- UPDATE MTL_ITEM_LOCATIONS
-- SET SEGMENT5 = L_SUB_CODE
-- WHERE INVENTORY_LOCATION_ID = L_LOCATOR_ID;
BEGIN
--UPDATE XX_STOCK_LOCATOR_STAGING_CHECK
--SET STATUS = 'Y'
--WHERE LOCATOR_CONCAT_SEGMENTS = L_CONCATENATED_SEGMENTS;
--NULL;
UPDATE XX_STOCK_LOCATOR_STAGING
SET status = 'Y'
WHERE srl = l_inventory_location_id
AND organization_id = L_ORG_ID;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
COMMIT;
-- DBMS_OUTPUT.PUT_LINE ('Y');
END IF;
-- COMMIT;
END LOOP;
-- COMMIT;
END LOOP;
END;
/
Subscribe to:
Posts (Atom)