Customer Item Cross Reference Conversion
A
Customer Items are Item Numbers used by customer for the items you sell to your
customers. Customer Item numbers are customer specific, so you can have same
item referenced differently by each customer. Before you can cross-reference,
you must define customers and customer items first.
Step1: Sample script to fetch the Customer
Item Cross Reference Conversion details
is given below.
PROCEDURE INSERT_CUST_ITEM_XREF( errbut OUT VARCHAR2,
retcode OUT NUMBER,
p_org IN NUMBER)
IS
l_inv_item_id NUMBER;
l_err_msg VARCHAR2 (1000);
l_org_id mtl_parameters.organization_id%TYPE;
l_mst_org_id mtl_parameters.master_organization_id%TYPE;
l_item_type VARCHAR2(60);
l_cust_id NUMBER;
l_item_count NUMBER;
ln_exists NUMBER;
ln_request_id NUMBER;
lb_complete BOOLEAN;
lc_phase VARCHAR2 (20);
lc_status VARCHAR2 (20);
lc_dev_phase VARCHAR2 (20);
lc_dev_status VARCHAR2 (20);
lc_message VARCHAR2 (100);
ln_interval NUMBER := 2;
ln_max_wait NUMBER := 9999;
lc_status_code VARCHAR2(50);
lc_phase_code VARCHAR2(50);
--ln_exists NUMBER;
CURSOR item_cur
IS
SELECT ROWID, a.*
FROM xx_cnv_cust_item_xref a
WHERE x_record_status ='CIP';
BEGIN
BEGIN
SELECT organization_id, master_organization_id
INTO l_org_id, l_mst_org_id
FROM mtl_parameters
WHERE organization_id = p_org;
EXCEPTION
WHEN OTHERS
THEN
LOG ('Unable to find the Org before
processing' || SQLERRM);
END;
IF l_mst_org_id IS NOT NULL
THEN
FOR item_rec IN item_cur
LOOP
l_err_msg := NULL;
BEGIN
SELECT cust_account_id
INTO l_cust_id
FROM HZ_CUST_ACCOUNTS
WHERE orig_system_reference = item_rec.legacy_customer_num;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := l_err_msg || 'Cound not get Customer ID ' || ITEM_REC.customer_item_number;
END;
BEGIN
SELECT item_type
INTO l_item_type
FROM mtl_system_items_b
WHERE organization_id = l_org_id
AND segment1 = TRIM(ITEM_REC.item_number);
EXCEPTION
WHEN OTHERS
THEN
l_err_msg
:= l_err_msg || 'Inventory Item does not
exists,' || item_rec.item_number;
END;
IF l_item_type IS NULL
THEN
l_err_msg := l_err_msg || 'Item Type does not
exists,' || item_rec.item_number;
END IF;
-- added by Doyensys on
24-Aug-2016
ln_exists := 0;
BEGIN
SELECT 1
INTO ln_exists
FROM mtl_customer_items
WHERE customer_item_number = trim(item_rec.customer_item_number);
EXCEPTION
WHEN OTHERS
THEN
l_err_msg
:= l_err_msg || 'Customer Item does not
exists' || ITEM_REC.customer_item_number;
END;
IF l_err_msg IS NULL
THEN
INSERT INTO mtl_ci_xrefs_interface
(customer_id,
customer_item_number,
preference_number,
inventory_item,
master_organization_id,
item_definition_level,
process_flag,
process_mode,
inactive_flag,
transaction_type,
lock_flag,
last_updated_by,
created_by,
last_update_date,
creation_date
)
VALUES (l_cust_id,
item_rec.customer_item_number,
1, --Preference Number
item_rec.item_number,
l_mst_org_id,
1, --item_definition_level
1, --process_flag
1, --process_mode
'2', -- inactive_mode
'CREATE',
'N', --lock_flag
-1,
-1,
SYSDATE,
SYSDATE
);
UPDATE XX_CNV_CUST_ITEM_XREF
SET X_RECORD_STATUS = 'CXV'
WHERE ROWID = item_rec.ROWID;
ELSE
UPDATE XX_CNV_CUST_ITEM_XREF
SET X_RECORD_STATUS = 'CXE',
X_RECORD_MSG = l_err_msg
WHERE ROWID = item_rec.ROWID;
END IF;
END LOOP;
COMMIT;
--*** Standard Program Call
***--
ln_exists := 0;
BEGIN
SELECT COUNT(1)
INTO ln_exists
FROM XX_CNV_CUST_ITEM_XREF
WHERE X_RECORD_STATUS = 'CXV';
END;
IF ln_exists > 0
THEN
BEGIN
FND_GLOBAL.APPS_INITIALIZE(gn_user_id, gn_resp_id, gn_resp_appl_id);
mo_global.set_policy_context('S',gn_org_id);
fnd_request.set_org_id(gn_org_id);
fnd_file.put_line(fnd_file.log,('*** Submitting Customer Item
Cross Reference Import Program ***'));
ln_request_id :=FND_REQUEST.SUBMIT_REQUEST (application => 'INV'
,program => 'INVCIINTX'
,description => NULL
,start_time => NULL
,sub_request => NULL
,argument1 => 'N' -- Abort On Error
,argument2 => 'Y' -- Delete Record
);
fnd_file.put_line(fnd_file.LOG,'Request Id- '||ln_request_id);
IF
ln_request_id > 0 THEN
COMMIT;
lb_complete :=
fnd_concurrent.wait_for_request (request_id => ln_request_id
,interval => ln_interval
,max_wait => ln_max_wait
,phase => lc_phase
,status => lc_status
,dev_phase => lc_dev_phase
,dev_status => lc_dev_status
,message => lc_message
);
COMMIT;
IF UPPER (lc_dev_phase) IN ('COMPLETE') THEN
fnd_file.put_line(fnd_file.log,('*** Import Customer Items Cross
Reference Program Completed Successfully ***'));
END IF;
END IF;
l_err_msg:= SQLERRM;
UPDATE XX_CNV_CUST_ITEM_XREF a
SET X_RECORD_STATUS = 'CXP'
WHERE EXISTS (SELECT 1
FROM mtl_customer_items b
WHERE a.customer_item_number = b.customer_item_number)
AND X_RECORD_STATUS = 'CXV';
UPDATE XX_CNV_CUST_ITEM_XREF a
SET X_RECORD_STATUS = 'CXE'
WHERE NOT EXISTS (SELECT 1
FROM mtl_customer_items b
WHERE a.customer_item_number = b.customer_item_number)
AND X_RECORD_STATUS = 'CXV';
END;
END IF;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line
('Outermost Exception:'||SQLERRM);
END INSERT_CUST_ITEM_XREF;
Step2: The sample table structure is given below
CREATE TABLE XX_CNV_CUST_ITEM_XREF
(
ITEM_NUMBER VARCHAR2(240 BYTE),
LEGACY_CUSTOMER_NUM VARCHAR2(25 BYTE),
CUSTOMER_ITEM_NUMBER VARCHAR2(240 BYTE),
X_RECORD_STATUS VARCHAR2(3 BYTE),
X_RECORD_MSG VARCHAR2(2000 BYTE)
)
By
Deepak J
No comments:
Post a Comment