DECLARE
CURSOR C1 IS
SELECT (upper(A.item_code)) ITEM_CODE, A.ORGANIZATION_ID ORG_ID,
A.sales_account
FROM hs_item_sales_account_conv A
WHERE A.ORGANIZATION_ID=111
AND A.ITEM_CODE IN()
AND EXISTS (SELECT 1
FROM MTL_SYSTEM_ITEMS_B MSI
WHERE MSI.ORGANIZATION_ID=111
AND MSI.SEGMENT1=(upper(A.item_code)));
BEGIN
FOR I IN C1
LOOP
INSERT INTO mtl_system_items_interface (
process_flag,
transaction_type,
set_process_id,
organization_id,
item_number,
last_update_date,
last_updated_by,
sales_account)
VALUES (
1,
‘UPDATE’,
1,
I.ORGANIZATION_ID,
I.ITEM_CODE,
sysdate,
53221,
I.sales_account);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Unexpected Error-‘||SUBSTR(SQLERRM,1,200)) ;
END ;
/
–ERROR TABLE SCRIPT
SELECT * FROM MTL_INTERFACE_ERRORS a
where exists (select * from mtl_system_items_interface
b where a.transaction_id=b.transaction_id and trunc(creation_date)=trunc(sysdate));
–Back end script for Import Items
DECLARE
v_sub_status BOOLEAN;
v_organization_id NUMBER := 0;
v_request_id NUMBER := 0;
v_phase VARCHAR2(240);
v_status VARCHAR2(240);
v_request_phase VARCHAR2(240);
v_request_status VARCHAR2(240);
v_finished BOOLEAN;
v_message VARCHAR2(240);
v_sub_status BOOLEAN := FALSE;
BEGIN
fnd_global.apps_initialize(user_id => 53221,resp_id => 50294,resp_appl_id => 401);
MO_GLOBAL.SET_POLICY_CONTEXT(‘S’,’106′);
–FND_GLOBAL.SET_NLS_CONTEXT(‘AMERICAN’); –This is for Language specific view
— MO_GLOBAL.INIT(‘INV’);
v_request_id := Fnd_Request.submit_request
(
application => ‘INV’,
program => ‘INCOIN’,
description => NULL,
start_time => SYSDATE,
sub_request => FALSE,
argument1 => 111, — Organization id
argument2 => 2, — All organizations
argument3 => 1, — Validate Items
argument4 => 1, — Process Items
argument5 => 1, — Delete Processed Rows
argument6 => NULL, — Process Set (Null for All)
argument7 => 2, — Create or Update Items
argument8 => 1 — Gather Statistics
);
COMMIT;
IF ( v_request_id = 0 ) THEN
dbms_output.put_line( ‘Item Import Program Not Submitted’);
ELSE
v_finished := fnd_concurrent.wait_for_request
(
request_id => v_request_id,
interval => 0,
max_wait => 0,
phase => v_phase,
status => v_status,
dev_phase => v_request_phase,
dev_status => v_request_status,
message => v_message
);
DBMS_OUTPUT.PUT_LINE(‘Request Phase : ‘|| v_request_phase );
DBMS_OUTPUT.PUT_LINE(‘Request Status : ‘|| v_request_status );
DBMS_OUTPUT.PUT_LINE(‘Request id : ‘|| v_request_id );
–Testing end statusv_request_id
IF ( UPPER(v_request_status) = ‘NORMAL’) THEN
DBMS_OUTPUT.PUT_LINE(‘Request id : ‘|| v_request_id );
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error in Submitting Item Import Program and error is ‘||SUBSTR(SQLERRM,1,200));
END;
