Physical Inventory Tag interface Program
Business Requirement:
User want to update the bulk of lines in physical tag counts and physical adjustment as well. The below interface is used to satisfy the both conditions with validation.
step :1
--------
-- Create staging table for upload the csv file
CREATE TABLE APPS.XXHMI_PHYSICAL_COUNT_DATA
(
PHYSICAL_INVENTORY_NAME VARCHAR2(30 BYTE),
TAG_NUMBER VARCHAR2(40 BYTE),
OPERATING_UNIT VARCHAR2(40 BYTE),
ITEM_NAME VARCHAR2(150 BYTE),
SUBINVENTORY_NAME VARCHAR2(10 BYTE),
LOCATOR_NAME VARCHAR2(122 BYTE),
PARENT_LPN VARCHAR2(30 BYTE),
TAG_QUANTITY NUMBER,
TAG_UOM VARCHAR2(3 BYTE),
EMPLOYEE_NUMBER VARCHAR2(30 BYTE),
PROCESS_FLAG VARCHAR2(1 BYTE),
REMARKS VARCHAR2(100 BYTE)
)
Step 2:
Register the loader concurrent with the following shortname XXHMI_PHY_CNT_TAG in to oracle apps.
Parameter : File name
CTL file:
-------------
options (skip=1)
load data
TRUNCATE
INTO TABLE XXHMI_PHYSICAL_COUNT_DATA
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS(
Physical_Inventory_Name,
TAG_NUMBER,
Operating_unit,
item_name,
subinventory_name,
locator_name,
PARENT_LPN,
TAG_quantity,
TAG_UOM,
employee_number,
process_flag CONSTANT 'N',
remarks)
Step 3: Register the below package in oracle apps
Executable Name : XXHMI_PHYSICAL_COUNT_TAGS.main
Parameter : filename
CREATE OR REPLACE PACKAGE BODY APPS.XXHMI_PHYSICAL_COUNT_TAGS
IS
/*******************************************************************************
* *
* Project : HLIFE *
* Module : INV *
* Table Name : XXHMI_PHYSICAL_COUNT_DATA *
* Description : Upload Physical Tag Data *
* *
* *
* *
* *
********************************************************************************
* Program History *
* *
* Date In Charge Description *
* ---------- --------- ----------------------------------------------- *
* 15-dec-2011 Vinoth kumar For Physical Tag data upload *
*******************************************************************************/
PROCEDURE main (errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_file_name IN VARCHAR2)
IS
l_retcode NUMBER;
l_errbuff VARCHAR2 (4000);
BEGIN
fnd_global.apps_initialize (fnd_profile.VALUE ('USER_ID'),
fnd_profile.VALUE ('RESP_ID'),
fnd_profile.VALUE ('RESP_APPL_ID'),
NULL,
NULL);
load_file_data (errbuf => l_errbuff,
retcode => l_retcode,
p_file_name => p_file_name);
IF l_retcode = 0
THEN
BEGIN
update_data_interface (errbuf => l_errbuff, retcode => l_retcode);
END;
END IF;
EXCEPTION
WHEN OTHERS
THEN
errbuf := 'ERROR IN MAIN PROCEDURE' || SQLERRM || SQLCODE;
retcode := 2;
fnd_file.put_line (fnd_file.output, 'Check outfile');
COMMIT;
END;
PROCEDURE load_file_data (errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_file_name IN VARCHAR2)
IS
l_request_id fnd_concurrent_requests.request_id%TYPE;
l_status_code fnd_concurrent_requests.status_code%TYPE := '~';
lb_wait BOOLEAN;
lc_phase VARCHAR2 (100);
lc_status VARCHAR2 (30);
lc_devpha VARCHAR2 (100);
lc_devsta VARCHAR2 (100);
lc_mesg VARCHAR2 (50);
ln_data_discarded NUMBER;
l_in_file_path VARCHAR2 (2000);
BEGIN
/*BEGIN
DELETE FROM xxhmi_subinventory_xl xxhmi;
COMMIT;
END;*/
BEGIN
retcode := 0;
SELECT '/usr/tmp' --DESCRIPTION -- UTL directory for reading the CSV file
INTO l_in_file_path
FROM DUAL;
IF l_in_file_path IS NULL
THEN
fnd_file.put_line (
fnd_file.LOG,
'Path is not defined for reading in Lookup type HMI_INBOUND_IFACE_FILE_PATH'
);
retcode := 2;
RETURN;
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.LOG,
'Issue is uploading the excel data' || SQLCODE || SQLERRM
);
retcode := 2;
RETURN;
END;
fnd_global.apps_initialize (fnd_profile.VALUE ('USER_ID'),
fnd_profile.VALUE ('RESP_ID'),
fnd_profile.VALUE ('RESP_APPL_ID'),
NULL,
NULL);
l_request_id :=
fnd_request.submit_request ('XXHMI', --'XXHN', -- APPLICATION.
'XXHMI_PHY_CNT_TAG', -- Loader short name
-- CONCURRENT PROGRAM.
NULL, -- DESCRIPTION.
NULL, -- START_TIME.
FALSE, -- SUB_REQUEST.
l_in_file_path || '/' || p_file_name,
CHR (0));
COMMIT;
IF (l_request_id = 0)
THEN
retcode := 2;
fnd_file.put_line (fnd_file.LOG,
'Failed to submit the loader program');
ELSE
lb_wait :=
fnd_concurrent.wait_for_request (request_id => l_request_id,
INTERVAL => 2,
phase => lc_phase,
status => lc_status,
dev_phase => lc_devpha,
dev_status => lc_devsta,
MESSAGE => lc_mesg);
IF lc_status IN ('Error')
THEN
retcode := 2;
fnd_file.put_line (
fnd_file.LOG,
'Error while reading data from file check request id '
|| l_request_id
);
ELSIF lc_status IN ('Warning')
THEN
SELECT fcr.ofile_size
INTO ln_data_discarded
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = l_request_id;
IF ln_data_discarded IS NOT NULL
THEN
retcode := 2;
END IF;
ELSE
retcode := 0;
fnd_file.put_line (
fnd_file.LOG,
'File read sucessfully check request id ' || l_request_id
);
END IF;
END IF;
/* IF retcode = 0
THEN
BEGIN
trim_data;
END;
END IF;*/
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error From Main' || SQLCODE || SQLERRM);
retcode := 2;
END load_file_data;
PROCEDURE update_data_interface (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
CURSOR c_validate
IS
SELECT * FROM XXHMI_PHYSICAL_COUNT_DATA;
CURSOR c_err
IS
SELECT *
FROM xxhmi_physical_count_data
WHERE PROCESS_FLAG <> 'Y';
--cursor c_process is select * from XXHMI_PHYSICAL_COUNT_DATA where process_flag='N'
v_phy_inv_id NUMBER;
v_operating_unit NUMBER;
v_item_id NUMBER;
v_locator_id NUMBER;
v_emp_id NUMBER;
v_adjust_id NUMBER;
v_count NUMBER;
v_count1 NUMBER;
v_lpn_id NUMBER;
v_remarks VARCHAR2 (250);
v_item_cost NUMBER;
v_cost_id NUMBER;
BEGIN
-- retcode := 0;
FOR i IN c_validate
LOOP
v_remarks := NULL;
v_lpn_id := NULL;
BEGIN
SELECT PHYSICAL_INVENTORY_ID
INTO v_phy_inv_id
FROM mtl_physical_inventories
WHERE PHYSICAL_INVENTORY_NAME = i.PHYSICAL_INVENTORY_NAME
AND organization_id IN
(SELECT organization_id
FROM hr_operating_units
WHERE name = i.operating_unit);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_remarks := v_remarks || 'Invalid Physical Inventory';
END;
BEGIN
SELECT COUNT ( * )
INTO v_count1
FROM mtl_physical_inventory_tags
WHERE TAG_NUMBER = i.TAG_NUMBER
AND PHYSICAL_INVENTORY_ID IN
(SELECT PHYSICAL_INVENTORY_ID
FROM mtl_physical_inventories
WHERE PHYSICAL_INVENTORY_NAME =
i.PHYSICAL_INVENTORY_NAME
AND organization_id IN
(SELECT organization_id
FROM hr_operating_units
WHERE name =
i.operating_unit));
IF v_count1 = 0
THEN
v_remarks := v_remarks || 'Invalid Tag Number';
END IF;
END;
BEGIN
SELECT organization_id
INTO v_operating_unit
FROM hr_operating_units
WHERE NAME = i.operating_unit;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_remarks := v_remarks || 'Invalid operating unit';
END;
BEGIN
SELECT COUNT ( * )
INTO v_count
FROM mtl_item_locations_kfv
WHERE SUBINVENTORY_CODE = i.SUBINVENTORY_NAME;
IF v_count = 0
THEN
v_remarks := v_remarks || 'Invalid subinventory';
END IF;
END;
BEGIN
SELECT DISTINCT INVENTORY_ITEM_ID
INTO v_item_id
FROM mtl_system_items_b
WHERE segment1 = i.ITEM_NAME;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_remarks := v_remarks || 'Invalid item_name';
END;
DBMS_OUTPUT.put_line ('hi v1');
BEGIN
SELECT ITEM_COST
INTO v_item_cost
FROM cst_item_costs
WHERE inventory_item_id = v_item_id
AND organization_id = v_operating_unit;
DBMS_OUTPUT.put_line ('hi v2');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_remarks := v_remarks || 'Invalid item cost';
END;
-- BEGIN
-- DBMS_OUTPUT.put_line ('hi vcost');
-- SELECT DISTINCT NVL (cost_group_id, NULL)
-- INTO v_cost_id
-- FROM MTL_ONHAND_QUANTITIES_DETAIL
-- WHERE 1 = 1
-- AND inventory_item_id = v_item_id --12200
-- AND organization_id = v_operating_unit --81
-- AND SUBINVENTORY_CODE = i.subinventory_name;
-- DBMS_OUTPUT.put_line ('hi v3');
-- EXCEPTION
-- WHEN NO_DATA_FOUND
-- THEN
-- v_remarks :=
-- v_remarks
-- || 'Cost group not available'
-- || SQLERRM
-- || v_cost_id;
-- END;
BEGIN
SELECT INVENTORY_LOCATION_ID
INTO v_locator_id
FROM MTL_ITEM_LOCATIONS_kfv
WHERE CONCATENATED_SEGMENTS = LTRIM (RTRIM (i.LOCATOR_NAME))
AND organization_id IN
(SELECT organization_id
FROM hr_operating_units
WHERE name = i.operating_unit);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_remarks := v_remarks || 'Invalid Locator name';
END;
DBMS_OUTPUT.put_line ('hi v4');
BEGIN
SELECT user_id
INTO v_emp_id
FROM fnd_user
WHERE user_name = i.employee_number;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_remarks := v_remarks || 'Invalid employee number';
END;
IF i.PARENT_LPN IS NOT NULL
THEN
BEGIN
SELECT LPN_ID
INTO v_lpn_id
FROM wms_license_plate_numbers
WHERE license_plate_number = i.PARENT_LPN;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_remarks := v_remarks || 'Invalid LPN';
END;
END IF;
IF v_remarks IS NULL AND i.PROCESS_FLAG <> 'Y'
--or PROCESS_FLAG ='N')
THEN
SELECT MTL_PHYSICAL_ADJUSTMENTS_S.NEXTVAL
INTO v_adjust_id
FROM DUAL;
v_remarks := 'Success';
UPDATE mtl_physical_inventory_tags
SET LAST_UPDATE_DATE = SYSDATE,
ADJUSTMENT_ID = v_adjust_id,
INVENTORY_ITEM_ID = v_item_id,
TAG_QUANTITY = i.TAG_QUANTITY,
TAG_UOM = i.TAG_UOM,
TAG_QUANTITY_AT_STANDARD_UOM = i.TAG_QUANTITY,
STANDARD_UOM = i.TAG_UOM,
SUBINVENTORY = i.SUBINVENTORY_NAME,
LOCATOR_ID = v_locator_id,
COUNTED_BY_EMPLOYEE_ID = v_emp_id,
PARENT_LPN_ID = v_lpn_id,
OUTERMOST_LPN_ID = v_lpn_id,
LAST_UPDATED_BY = v_emp_id,
LAST_UPDATE_LOGIN = v_emp_id,
CREATED_BY = v_emp_id
--,
-- COST_GROUP_ID = NVL (v_cost_id, NULL)
WHERE PHYSICAL_INVENTORY_ID = v_phy_inv_id
AND ORGANIZATION_ID = v_operating_unit
AND TAG_NUMBER = i.TAG_NUMBER;
-- IF process_flag = 'N'
-- THEN
INSERT INTO MTL_PHYSICAL_ADJUSTMENTS (ADJUSTMENT_ID,
ORGANIZATION_ID,
PHYSICAL_INVENTORY_ID,
INVENTORY_ITEM_ID,
SUBINVENTORY_NAME,
SYSTEM_QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
COUNT_QUANTITY,
ADJUSTMENT_QUANTITY,
REVISION,
LOCATOR_ID,
PARENT_LPN_ID,
OUTERMOST_LPN_ID,
-- COST_GROUP_ID,
LOT_NUMBER,
LOT_EXPIRATION_DATE,
SERIAL_NUMBER,
ACTUAL_COST,
SECONDARY_SYSTEM_QTY,
SECONDARY_COUNT_QTY,
SECONDARY_ADJUSTMENT_QTY)
VALUES (v_adjust_id,
v_operating_unit,
v_phy_inv_id,
v_item_id,
i.SUBINVENTORY_NAME,
0,
SYSDATE,
v_emp_id,
SYSDATE,
v_emp_id,
v_emp_id,
i.TAG_QUANTITY,
i.tag_quantity,
NULL,
v_locator_id,
v_lpn_id,
v_lpn_id,
-- NVL (v_cost_id, NULL),
NULL,
NULL,
NULL,
v_item_cost,
NULL,
NULL,
NULL);
-- END IF;
UPDATE XXHMI_PHYSICAL_COUNT_DATA
SET Process_flag = 'Y', remarks = 'Success'
WHERE PHYSICAL_INVENTORY_NAME = i.PHYSICAL_INVENTORY_NAME
AND TAG_NUMBER = i.TAG_NUMBER
AND OPERATING_UNIT = i.OPERATING_UNIT;
COMMIT;
ELSE
UPDATE XXHMI_PHYSICAL_COUNT_DATA
SET Process_flag = 'E', remarks = v_remarks
WHERE PHYSICAL_INVENTORY_NAME = i.PHYSICAL_INVENTORY_NAME
AND TAG_NUMBER = i.TAG_NUMBER
AND OPERATING_UNIT = i.OPERATING_UNIT;
COMMIT;
END IF;
fnd_file.put_line (
fnd_file.LOG,
i.PHYSICAL_INVENTORY_NAME
|| '-'
|| i.TAG_NUMBER
|| '-'
|| v_remarks
);
END LOOP;
BEGIN
fnd_file.put_line (
fnd_file.output,
'PHYSICAL_INVENTORY_NAME'
|| '|'
|| 'TAG_NUMBER'
|| '|'
|| 'OPERATING_UNIT'
|| '|'
|| 'ITEM_NAME'
|| '|'
|| 'SUBINVENTORY_NAME'
|| '|'
|| 'LOCATOR_NAME'
|| '|'
|| 'PARENT_LPN'
|| '|'
|| 'TAG_QUANTITY'
|| '|'
|| 'TAG_UOM'
|| '|'
|| 'EMPLOYEE_NUMBER'
|| '|'
|| 'PROCESS_FLAG'
|| '|'
|| 'REMARKS'
);
FOR j IN c_err
LOOP
fnd_file.put_line (
fnd_file.output,
J.PHYSICAL_INVENTORY_NAME
|| '|'
|| J.TAG_NUMBER
|| '|'
|| J.OPERATING_UNIT
|| '|'
|| J.ITEM_NAME
|| '|'
|| J.SUBINVENTORY_NAME
|| '|'
|| J.LOCATOR_NAME
|| '|'
|| J.PARENT_LPN
|| '|'
|| J.TAG_QUANTITY
|| '|'
|| J.TAG_UOM
|| '|'
|| J.EMPLOYEE_NUMBER
|| '|'
|| J.PROCESS_FLAG
|| '|'
|| J.REMARKS
);
END LOOP;
END;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'update_data_interface' || SQLCODE || SQLERRM);
DBMS_OUTPUT.put_line ('update_data_interface' || SQLCODE || SQLERRM);
-- retcode := 2;
RETURN;
END update_data_interface;
END XXHMI_PHYSICAL_COUNT_TAGS;
/
Step :4
Once register the concurrent then prepare the csv file and move to the utl dir (/USR/TMP/)
Step :5
Run the main program with the parameter of your file name with extension
Eg: Sample.csv
** note case senstive
Step :6 check the data in the front end
***************************END***********************
