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;
/
Creating a blog is not just a hobby it’s a partnership to grow together.
Monday, March 26, 2012
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)