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;
/
Create Inventory Locator Conversion
/* Formatted on 3/26/2012 3:59:43 PM (QP5 v5.114.809.3010) */
CREATE OR REPLACE PROCEDURE APPS.XX_CREATE_STOCK_LOCATORS
IS
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) := '221';
/*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;
/*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 */
CURSOR C1
IS
SELECT SUBINVENTORY,
LOCATOR_CONCAT_SEGMENTS,
PICKING_PRIORITY,
SUPPLIER,
CAPACITY
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
)
*/
WHERE SUBINVENTORY = 'K4FUR';
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 I IN C1
LOOP
L_CONCATENATED_SEGMENTS := I.LOCATOR_CONCAT_SEGMENTS;
L_PICKING_ORDER := I.PICKING_PRIORITY;
l_location_maximum_units := I.CAPACITY;
L_SUB_CODE := 'K4FUR';
L_ORG_ID := 107;
INV_LOC_WMS_PUB.CREATE_LOCATOR (
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => L_MSG_COUNT,
X_MSG_DATA => L_MSG_DATA,
X_INVENTORY_LOCATION_ID => L_LOCATOR_ID,
X_LOCATOR_EXISTS => L_LOCATOR_EXISTS,
P_ORGANIZATION_ID => L_ORG_ID,
P_ORGANIZATION_CODE => L_ORGANIZATION_CODE,
P_CONCATENATED_SEGMENTS => L_CONCATENATED_SEGMENTS,
P_DESCRIPTION => 'WMS-Locator Created By API', /*YOU CAN ALSO USE HERE DESCRIPTION OF YOUR LOCATOR COMBINATION*/
P_INVENTORY_LOCATION_TYPE => NULL,
P_PICKING_ORDER => L_PICKING_ORDER,
P_LOCATION_MAXIMUM_UNITS => NULL, --l_location_maximum_units, -- capacity
P_SUBINVENTORY_CODE => L_SUB_CODE, /*SUBINVENTORY CODE */
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
);
DBMS_OUTPUT.PUT_LINE( 'Return Status '
|| L_CONCATENATED_SEGMENTS
|| ' - '
|| 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);
UPDATE XX_STOCK_LOCATOR_STAGING_CHECK
SET STATUS = 'N'
WHERE LOCATOR_CONCAT_SEGMENTS LIKE L_CONCATENATED_SEGMENTS;
/* IF L_MSG_COUNT = 1
THEN
DBMS_OUTPUT.PUT_LINE ('Error ' || L_MSG_DATA);
ELSE
FOR I IN 1 .. L_MSG_COUNT
LOOP
DBMS_OUTPUT.PUT_LINE ('Error ' || FND_MSG_PUB.GET (I, 'F'));
END LOOP;
END IF;
*/
ELSE
UPDATE XX_STOCK_LOCATOR_STAGING
SET STATUS = 'Y'
WHERE LOCATOR_CONCAT_SEGMENTS = L_CONCATENATED_SEGMENTS;
COMMIT;
-- DBMS_OUTPUT.PUT_LINE ('Locator Id is ' || L_LOCATOR_ID);
END IF;
-- COMMIT;
END LOOP;
-- COMMIT;
END;
/
/* Formatted on 3/26/2012 3:59:43 PM (QP5 v5.114.809.3010) */
CREATE OR REPLACE PROCEDURE APPS.XX_CREATE_STOCK_LOCATORS
IS
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) := '221';
/*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;
/*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 */
CURSOR C1
IS
SELECT SUBINVENTORY,
LOCATOR_CONCAT_SEGMENTS,
PICKING_PRIORITY,
SUPPLIER,
CAPACITY
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
)
*/
WHERE SUBINVENTORY = 'K4FUR';
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 I IN C1
LOOP
L_CONCATENATED_SEGMENTS := I.LOCATOR_CONCAT_SEGMENTS;
L_PICKING_ORDER := I.PICKING_PRIORITY;
l_location_maximum_units := I.CAPACITY;
L_SUB_CODE := 'K4FUR';
L_ORG_ID := 107;
INV_LOC_WMS_PUB.CREATE_LOCATOR (
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => L_MSG_COUNT,
X_MSG_DATA => L_MSG_DATA,
X_INVENTORY_LOCATION_ID => L_LOCATOR_ID,
X_LOCATOR_EXISTS => L_LOCATOR_EXISTS,
P_ORGANIZATION_ID => L_ORG_ID,
P_ORGANIZATION_CODE => L_ORGANIZATION_CODE,
P_CONCATENATED_SEGMENTS => L_CONCATENATED_SEGMENTS,
P_DESCRIPTION => 'WMS-Locator Created By API', /*YOU CAN ALSO USE HERE DESCRIPTION OF YOUR LOCATOR COMBINATION*/
P_INVENTORY_LOCATION_TYPE => NULL,
P_PICKING_ORDER => L_PICKING_ORDER,
P_LOCATION_MAXIMUM_UNITS => NULL, --l_location_maximum_units, -- capacity
P_SUBINVENTORY_CODE => L_SUB_CODE, /*SUBINVENTORY CODE */
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
);
DBMS_OUTPUT.PUT_LINE( 'Return Status '
|| L_CONCATENATED_SEGMENTS
|| ' - '
|| 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);
UPDATE XX_STOCK_LOCATOR_STAGING_CHECK
SET STATUS = 'N'
WHERE LOCATOR_CONCAT_SEGMENTS LIKE L_CONCATENATED_SEGMENTS;
/* IF L_MSG_COUNT = 1
THEN
DBMS_OUTPUT.PUT_LINE ('Error ' || L_MSG_DATA);
ELSE
FOR I IN 1 .. L_MSG_COUNT
LOOP
DBMS_OUTPUT.PUT_LINE ('Error ' || FND_MSG_PUB.GET (I, 'F'));
END LOOP;
END IF;
*/
ELSE
UPDATE XX_STOCK_LOCATOR_STAGING
SET STATUS = 'Y'
WHERE LOCATOR_CONCAT_SEGMENTS = L_CONCATENATED_SEGMENTS;
COMMIT;
-- DBMS_OUTPUT.PUT_LINE ('Locator Id is ' || L_LOCATOR_ID);
END IF;
-- COMMIT;
END LOOP;
-- COMMIT;
END;
/
Monday, March 5, 2012
Standalone concurrent program in Oracle Applications
Creating a PL/SQL Concurrent Program in Oracle Applications
===========================================================
This note describes the basic steps to set-up PL/SQL programs as Standalone concurrent program in Oracle Applications
NOTE - Although this article has been prepared on a Release 11, the steps for 10.7 , R12 will be similar.
Introduction
============
These steps will create a package with four procedures.
The first procedure is a simple standalone procedure that just writes some messages to the log file.
Secondly create a procedure that calls another procedure as a child process.
Set-up a procedure as a standalone process that accepts a parameter and outputs the entered parameter to the log file.
1) Create custom application
============================
Customizations should be created in a separate area in a custom application.
This process relies on a custom table created as described below:
create table mzRunTable (source_name varchar2(20), status varchar2(20), date_created date);
2) Create and load PL/SQL program
=================================
Create the following two files:
a) mzRunS.pls
+++ START OF SCRIPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
create or replace package mzConcTest is
/** Header - Test package from Oracle Support note 73492.1 - Version 1.0.0**/
procedure mzFirst(errbuf out varchar2, retcode out varchar2);
procedure mzCallMe(errbuf out varchar2, retcode out varchar2);
procedure mzMain(errbuf out varchar2, retcode out varchar2);
procedure mzParameter(errbuf out varchar2, retcode out varchar2, mzVar in varchar2 );
end mzConcTest ;
/
show errors
commit;
+++ END OF SCRIPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
b) mzRunB.pls
+++ START OF SCRIPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
create or replace package body mzConcTest as
procedure mzFirst(errbuf out varchar2, retcode out varchar2) as
mzVar varchar2(20);
begin
mzVar := 'OK';
retcode := 0;
fnd_file.put_line(FND_FILE.LOG,'mzFirst procedure '|| mzVAR);
fnd_file.put_line(FND_FILE.LOG,'Retcode = '|| retcode);
end mzFirst;
procedure mzCallMe(errbuf out varchar2, retcode out varchar2) as
begin
fnd_file.put_line(FND_FILE.LOG,'mzCallMe procedure');
retcode := 0;
insert into mzRunTable
(source_name
,status
,date_created)
values
('MZCALLME'
,'WORKING OK'
,sysdate);
commit;
fnd_file.put_line(FND_FILE.LOG,'Retcode = '|| retcode);
exception
when others then
retcode := 4;
fnd_file.put_line(FND_FILE.LOG, 'ABORTED RUN. Retcode = '|| retcode);
end mzCallMe;
procedure mzMain(errbuf out varchar2, retcode out varchar2) as
l_errbuf varchar2(2000) := '' ;
l_retcode number(1) := 0;
l_phase varchar2(2000) := '' ;
l_status varchar2(2000) := '' ;
l_dev_phase varchar2(2000) := '' ;
l_dev_status varchar2(2000) := '' ;
l_message varchar2(2000) := '' ;
l_request_id number := 0;
l_get_request_status boolean ;
begin
retcode := 0;
fnd_file.put_line(FND_FILE.LOG,'mzMain procedure');
l_request_id := fnd_request.submit_request('FND', 'MZCALLME', 'Mikes MZCALLME routine');
commit;
if l_request_id = 0 then
l_errbuf := 'Request id is zero' ;
FND_FILE.PUT_LINE(FND_FILE.log,l_errbuf);
retcode := 1;
else
l_errbuf := 'Request id '||to_char(l_request_id) ;
FND_FILE.PUT_LINE(fnd_file.log, l_errbuf);
l_errbuf := 'Monitoring request '||to_char(l_request_id) ;
FND_FILE.PUT_LINE(FND_FILE.log,l_errbuf);
l_get_request_status := fnd_concurrent.wait_for_request(l_request_id, 60, 0, l_phase, l_status, l_dev_phase, l_dev_status, l_message);
if l_dev_status = 'ERROR' then
l_errbuf := 'Import failed with message '||l_message ;
FND_FILE.PUT_LINE(FND_FILE.log,l_errbuf);
retcode := 2;
else
l_errbuf := 'Request finished with status '||l_status ;
FND_FILE.PUT_LINE(FND_FILE.log,l_errbuf);
end if ;
end if;
fnd_file.put_line(FND_FILE.LOG, 'COMPLETED RUN. Retcode = '|| retcode);
exception
when others then
retcode := 3;
fnd_file.put_line(FND_FILE.LOG, 'ABORTED RUN. Retcode = '|| retcode);
end mzMain ;
procedure mzParameter(errbuf out varchar2, retcode out varchar2, mzVar in varchar2) as
begin
retcode := 0;
fnd_file.put_line(FND_FILE.LOG,'mzParameter procedure');
fnd_file.put_line(FND_FILE.LOG,'Parameter received was: '|| mzVar);
fnd_file.put_line(FND_FILE.LOG,'Retcode = '|| retcode);
end mzParameter;
end mzConcTest ;
/
show errors
commit;
+++ END OF SCRIPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Now run mzRunS.pls then mzRunB.pls against the database. It was loadied against the APPS schema,
but one should load them into the Custom Schema and create appropriate grants and synonyms if required.
The files above compile successfully against an Oracle 8.0.4 database. If any errors are received,
check the source files for incorrect spacing, spelling or extra carriage returns.
3) Set-up concurrent program and test results
=============================================
For this section it is assumed that a custom application called 'PLSQL Test' was created.
Enter whatever the custom application is called where 'PLSQL Test' is mentioned in the following set-up steps.
Set-up three separate tests one at a time.
Test One - simple standalone procedure
--------------------------------------
As System Administrator do the following:-
a) Setup the Executable. Concurrent-->Program-->Executable
Executable = mzFirst
Short Name = mzFirst
Application = PLSQL Test
Description = First Test
Execution Method = PL/SQL Stored Procedure
Execution File Name = mzConcTest.mzFirst
b) Define the Concurrent Program. Concurrent-->Program-->Define
Program = mzFirst
Short name = mzFirst
Application = PLSQL Test
Description = First Test
Executable Name = mzFirst
(Leave all other settings as the default)
c) Add Concurrent Program to Concurrent Request Group. Security-->Responsibility-->Request
Group = PLSQL Test
Application = PLSQL Test
Code =
Description = First Test
Requests Type = Program
Requests Name = mzFirst
Requests Application = PLSQL Test
d) Now log in to the user/responsibility that has access to the 'PLSQL Test'
request group and run the 'mzFirst' request. Monitor the request which should complete with 'Normal' status.
There will not be an output file, so just look at the Log file that has been generated.
It should look similar to that listed below:
+---------------------------------------+
MZFIRSTPLSQL module: mzFirst
Start of log messages from Plsql program
+---------------------------------------+
mzFirst procedure OK
Retcode = 0
+---------------------------------------+
End of log messages from PlSql program
+---------------------------------------+
Test Two - Procedure that spawns another procedure as a child concurrent process
--------------------------------------------------------------------------------
As System Administrator do the following:-
a) Setup the Executable. Concurrent-->Program-->Executable
Executable = mzMain
Short Name = mzMain
Application = PLSQL Test
Description = Calling PLSQL procedure
Execution Method = PL/SQL Stored Procedure
Execution File Name = mzConcTest.mzMain
b) Setup the Executable. Concurrent-->Program-->Executable
Executable = mzCallMe
Short Name = mzCallme
Application = PLSQL Test
Description = Called PLSQL procedure
Execution Method = PL/SQL Stored Procedure
Execution File Name = mzConcTest.mzCallme
c) Define the Concurrent Program. Concurrent-->Program-->Define
Program = mzMain
Short name = mzMain
Application = PLSQL Test
Description = Calling PLSQL procedure
Executable Name = mzMain
(Leave all other settings as the default)
d) Define the Concurrent Program. Concurrent-->Program-->Define
Program = mzCallMe
Short name = mzCallMe
Application = PLSQL Test
Description = Calling PLSQL procedure
Executable Name = mzCallMe
(Leave all other settings as the default)
e) Add Concurrent Program to Concurrent Request Group. Security-->Responsibility-->Request
Group = PLSQL Test
Application = PLSQL Test
Code =
Description = Calling PLSQL procedure
Requests Type = Program
Requests Name = mzMain
Requests Application = PLSQL Test
d) Now log in to the user/responsibility that has access to the 'PLSQL Test' request group and run the 'mzMain' request.
Monitor the request which should first spawn the 'mzCallme' request,
then once this child process has completed 'Normal' the 'mzMain' process will also complete with 'Normal' status.
There will not be an output file, so just look at the Log file for the two concurrent processes.
They should look similar to that listed below:-
+---------------------------------------+
MZMAIN module: mzMain
+---------------------------------------+
Start of log messages from Plsql program
+---------------------------------------+
mzMain procedure
Request id 6428
Monitoring request 6428
Request finished with status Normal
COMPLETED RUN. Retcode = 0
+---------------------------------------+
End of log messages from PlSql program
+---------------------------------------+
+---------------------------------------+
MZCALLME module: mzCallMe
+---------------------------------------+
Start of log messages from Plsql program
+---------------------------------------+
mzCallMe procedure
Retcode = 0
+---------------------------------------+
End of log messages from PlSql program
+---------------------------------------+
Test Three - standalone procedure with passes parameter
-------------------------------------------------------
As System Administrator do the following:
a) Setup the Executable. Concurrent-->Program-->Executable
Executable = mzParameter
Short Name = mzParameter
Application = PLSQL Test
Description = Procedure with parameter
Execution Method = PL/SQL Stored Procedure
Execution File Name = mzConcTest.mzParameter
b) Define the Concurrent Program. Concurrent-->Program-->Define
Program = mzParameter
Short name = mzParameter
Application = PLSQL Test
Description = Procedure with parameter
Executable Name = mzParameter
(Leave all other settings as the default)
Click on 'Parameters' button and enter the following:-
Seq = 1
Parameter = mzVar
Description = Text sent to log file
Value Set = 15 Characters
(Leave all other settings as the default)
c) Add Concurrent Program to Concurrent Request Group. Security-->Responsibility-->Request
Group = PLSQL Test
Application = PLSQL Test
Code =
Description = First Test
Requests Type = Program
Requests Name = mzParameter
Requests Application = PLSQL Test
d) Now login to the user/responsibility that has access to the 'PLSQL Test'
request group and run the 'mzParameter' request. When selected it should pop up a window to ask to enter
the 'mzVar' parameter value. Enter 'This is a test' as the text. Monitor the request which should complete with 'Normal' status.
The parameter entered will be seen on the 'View Requests' window in the 'Parameters' column.
There will not be an output file, so just look at the Log file that has been generated.
It should look similar to that listed below:
+---------------------------------------+
MZPARAMETER module: mzParameter
+---------------------------------------+
Start of log messages from Plsql program
+---------------------------------------+
mzParameter procedure
Parameter received was: This is a test
Retcode = 0
+---------------------------------------+
End of log messages from PlSql program
+---------------------------------------+
Sunday, March 4, 2012
Inventory Managers
Launch "Material Transaction Manager" from Inventory setup module. This will schedule a concurrent program called Process transactions interface (INCTCM) every 5 minutes. This manager (concurrent program) reads records from MTL_TRANSACTIONS_INTERFACE validates them and moves the successful transactions onto MTL_MATERIAL_TRANSACTIONS_TEMP, and submits Inventory Transaction Worker (INCTCW) which then processes these records through inventory.
Launch "Cost Manager" from Inventory setup module. This schedules a concurrent program called Cost Manager (CMCTCM) every 5 minutes. Cost transaction manager costs material transactions in Oracle Inventory and Oracle Work in Process in the background.
Launch "Move transaction Manager" from Inventory setup module. This will schedule a concurrent program called WIP Move Transactions Manager (WICTMS) every 5 minutes.
i think these steps may help resolve your problem
Launch "Cost Manager" from Inventory setup module. This schedules a concurrent program called Cost Manager (CMCTCM) every 5 minutes. Cost transaction manager costs material transactions in Oracle Inventory and Oracle Work in Process in the background.
Launch "Move transaction Manager" from Inventory setup module. This will schedule a concurrent program called WIP Move Transactions Manager (WICTMS) every 5 minutes.
i think these steps may help resolve your problem
Subscribe to:
Posts (Atom)