平凡的幸福(備站）

DECLARE
p_organization_id mtl_system_items_interface.organization_id%TYPE := 168;
p_user_id fnd_user.user_id%TYPE;
p_set_process_id mtl_system_items_interface.set_process_id%TYPE := 5000;

CONC_ID NUMBER;
rc number;
rt boolean;
outcome varchar2(200) := NULL;
message varchar2(200) := NULL;
timeout number:= 3000;
LB_RETURN BOOLEAN;
LS_PHASE VARCHAR2(100);
LS_STATUS VARCHAR2(100);
LS_DUMMY1 VARCHAR2(100);
LS_DUMMY2 VARCHAR2(100);
LS_DUMMY3 VARCHAR2(100);
V_DUMMY NUMBER := 0;

v_request_id number;
errbuf varchar2(500);

l_num_user_id number;
l_num_resp_id number;
l_num_resp_appl_id number;


CURSOR cur IS
SELECT distinct d.inventory_Item_id,d.segment1
FROM MTL_SYSTEM_ITEMS D
WHERE d.organization_id=168
and d.segment1='XX-1234';

BEGIN

FOR rec IN cur LOOP
--將欲修改的資料寫入INTERFACE
INSERT INTO mtl_system_items_interface
(
process_flag , set_process_id
, transaction_type, inventory_item_id
, organization_id
, last_update_date
, last_updated_by , last_update_login
,FIXED_DAYS_SUPPLY
)
VALUES (
1 , p_set_process_id
,'UPDATE' , rec.inventory_item_id
, p_organization_id
, SYSDATE
,-1,-1
,1
);
END LOOP;

COMMIT;

--找初始化環境所需的資料
SELECT user_id
INTO l_num_user_id
FROM applsys.fnd_user
WHERE user_name ='TEST_USER';

SELECT responsibility_id
INTO l_num_resp_id
FROM apps.fnd_responsibility_vl
WHERE responsibility_name ='INVENTORY';

SELECT application_id
INTO l_num_resp_appl_id
FROM applsys.fnd_application
WHERE application_short_name = 'INV';

--初始化必要的環境變數
fnd_global.apps_initialize (
user_id => l_num_user_id,
resp_id => l_num_resp_id,
resp_appl_id => l_num_resp_appl_id
);

--如果成功會回傳Request ID，失敗傳回0
v_request_id:=Fnd_Request.submit_request (
application => 'INV',
Program => 'INCOIN',
description => '',
start_time => '',
sub_request => FALSE,
argument1 => '168',
argument2 => '1',
argument3 => '1', --Group ID option (All)
argument4 => '1', -- Group ID Dummy
argument5 => '1', -- Delete processed Record
argument6 => p_set_process_id, -- Set Process id
argument7 =>'2' -- Update item
);
COMMIT ;

dbms_output.put_line( v_request_id) ;

IF p_set_process_id = 0 THEN
errbuf := 'Call INCOIN Error';
rollback;
return;
END IF;

lb_return := fnd_concurrent.wait_for_request(
p_set_process_id
,3 -- interval
,7200 -- max_wait
,ls_phase
,ls_status
,ls_dummy1
,ls_dummy2
,ls_dummy3
);
IF TRIM(ls_phase) = 'Completed' AND TRIM(ls_status) NOT IN ('Normal','Warning') THEN
errbuf :='Batch Submit item error';
return;
END IF;

END;