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;
/
No comments:
Post a Comment