Group Team
Below code can be used to assign item from Master org to Child Org

declare
v_organization_id NUMBER := 0;
v_master_org 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_item_id NUMBER := 0;

Begin
--Getting the Organization id
BEGIN
SELECT Organization_id,master_organization_id
INTO v_organization_id,v_master_org
FROM mtl_parameters mp
WHERE mp.organization_code = 'C1'; --C1 is the Child Organization Code
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in getting the Organization id for Organization code V1 and error is '||SUBSTR(SQLERRM,1,200));
END;

--Getting the Inventory Item id of the Item which is available in Master Organization
SELECT inventory_item_id
INTO v_item_id
from mtl_system_items_b
where segment1 = 'Existing Item Name'
and organization_id = v_master_org;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in getting the inventory item id for Item and error is '||SUBSTR(SQLERRM,1,200));
END;


--Inserting into Item interface table
BEGIN
INSERT INTO mtl_system_items_interface
(inventory_item_id,
organization_id,
process_flag,
set_process_id,
transaction_type
)
values
( v_item_id,
v_organization_id,
1,
1,
'CREATE'
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in inserting record in interface table and error is '||SUBSTR(SQLERRM,1,200));
END;
END;


--Submit the item import program in Create Mode to Assign existing Item
BEGIN

dbms_output.put_line('--Submitting Item Import Program for Item--');
v_request_id := Fnd_Request.submit_request (
application => 'INV',
program => 'INCOIN',
description => NULL,
start_time => SYSDATE,
sub_request => FALSE,
argument1 => 1,
argument2 => 1,
argument3 => 1, --Group ID option (All)
argument4 => 1, -- Group ID Dummy
argument5 => 1, -- Delete processed Record
argument6 => 1, -- Set Process id
argument7 => 1 -- Create item
);
COMMIT;
dbms_output.put_line('Item Import Program submitted');

IF ( v_request_id = 0 ) THEN
dbms_output.put_line( 'Item Import Program Not Submitted');
END IF;

-- Wait for request to run the import Program to Finish
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 status
IF ( UPPER(v_request_status) = 'NORMAL') THEN
dbms_output.put_line( 'Item Import Program Completed Normally');
ELSE
dbms_output.put_line( 'Item Import Program completed with error. Check Mtl_interface_error table for the transaction_id');
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in Submitting Item Import Program and error is '||SUBSTR(SQLERRM,1,200));
END;
END;
3 Responses
  1. Sekhar Byna Says:

    Very intersting notes on ebusiness Suite
    Please do share your articles like this.THis link also useful to Find info On Oracle apps-jobs,interview tips,Resume preparation,functional issuesOracle eBusiness Suite


  2. Sekhar Byna Says:

    Very intersting notes on ebusiness Suite
    Please do share your articles like this.THis link also useful to Find info On Oracle apps-jobs,interview tips,Resume preparation,functional issuesOracle eBusiness Suite


  3. Unknown Says:

    Great blog post...Thanks for sharing.Its a valuable info.
    Sponsor a child in need
    Donate to children in need


Post a Comment