Custom Price Transfer accross the OU

CREATE OR REPLACE PROCEDURE APPS.PRG_COSTING_PERIOD_test(PERIODID IN VARCHAR2) IS
BEGIN


Insert into XX_UPDATE_TRANSFER_PRICE
(TRANSACTION_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID, TRANSFER_PRICE, NEW_TRANSFER_PRICE,
TRANSFER_ORGANIZATION_ID, XX_TRANSFER_PRICE, XX_PROCESS_FLAG, XX_PERIOD_ID,PROCESS_STATUS)
Values (148, 148, 148, 0, 0, 148, 0, 100, 148,'RUNNING');
COMMIT;

INSERT INTO XX_UPDATE_TRANSFER_PRICE

SELECT mtl.TRANSACTION_ID,mtl.INVENTORY_ITEM_ID,mtl.ORGANIZATION_ID ToOrg,mtl.TRANSFER_PRICE,0 NEW_TRANSFER_PRICE,
mtl.TRANSFER_ORGANIZATION_ID FromOrg, mtl.TRANSFER_PRICE,0 XX_PROCESS_FLAG, 148 XX_PERIOD_ID,NULL
FROM mtl_material_transactions mtl
,org_organization_definitions orgFrm1 -- From org
,org_organization_definitions orgTo -- To org
, cm_cmpt_dtl cst
WHERE mtl.source_code = 'RCV'
and mtl.TRANSACTION_DATE between (select trunc(start_date) from GMF_PERIOD_STATUSES where period_id =148-- PERIODID-- v_period_id
)
and (select trunc(end_date) from GMF_PERIOD_STATUSES where period_id =148-- PERIODID
)
and mtl.transaction_action_id = 12
and mtl.TRANSFER_ORGANIZATION_ID=orgFrm1.ORGANIZATION_ID
and mtl.ORGANIZATION_ID=orgTo.ORGANIZATION_ID
and orgFrm1.OPERATING_UNIT<>orgTo.OPERATING_UNIT
and mtl.TRANSFER_ORGANIZATION_ID=cst.ORGANIZATION_ID(+) and mtl.INVENTORY_ITEM_ID=cst.INVENTORY_ITEM_ID(+) and cst.PERIOD_ID(+)=148
and( mtl.TRANSFER_PRICE<>cst.CMPNT_COST or cst.CMPNT_COST is null)


;
COMMIT;


--FOR i IN 1 .. 1 LOOP

declare
cursor c_mmt is

SELECT * FROM XX_UPDATE_TRANSFER_PRICE
WHERE TRANSACTION_ID = 148 AND XX_PROCESS_FLAG=100 AND PROCESS_STATUS='RUNNING'
ORDER BY TRANSACTION_ID;

h_mmt c_mmt%ROWTYPE;
BEGIN
OPEN c_mmt;
LOOP
FETCH c_mmt INTO h_mmt;
EXIT WHEN c_mmt%NOTFOUND;

-------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE---------------------------

declare
cursor c_mmtN is

select * from XX_UPDATE_TRANSFER_PRICE WHERE PROCESS_STATUS IS NULL
order by TRANSACTION_ID;


h_mmtN c_mmtN%ROWTYPE;
BEGIN
OPEN c_mmtN;
LOOP
FETCH c_mmtN INTO h_mmtN;
EXIT WHEN c_mmtN%NOTFOUND;
update XX_UPDATE_TRANSFER_PRICE
set NEW_TRANSFER_PRICE =(

select acctg_cost
from gl_item_cst gic,mtl_material_transactions mmt
where mmt.transaction_id = h_mmtN.transaction_id
and mmt.inventory_item_id=gic.INVENTORY_ITEM_ID
and gic.period_ID = PERIODID --v_period_ID
and final_flag = 0
and mmt.TRANSFER_ORGANIZATION_ID = gic.ORGANIZATION_ID

)
where XX_UPDATE_TRANSFER_PRICE.transaction_id = h_mmtN.transaction_id;
commit;
END LOOP;
exception
when others then
null;
CLOSE c_mmtN;
END;





---------------------------------------------
/*
--update mtl_material_transactions
-- set TRANSFER_PRICE = h_mmt.NEW_TRANSFER_PRICE
-- where mtl_material_transactions.TRANSACTION_ID = h_mmt.transaction_id;

IF i=1 THEN
update XX_UPDATE_TRANSFER_PRICE set XX_PROCESS_FLAG=990, PROCESS_STATUS='COMPLETED' where transaction_id=148 and inventory_item_id=148 and xx_period_id=148 and ORGANIZATION_ID=148 and TRANSFER_ORGANIZATION_ID =148 and XX_PROCESS_FLAG=100;
ELSIF I=2 THEN
update XX_UPDATE_TRANSFER_PRICE set XX_PROCESS_FLAG=991, PROCESS_STATUS='COMPLETED' where transaction_id=148 and inventory_item_id=148 and xx_period_id=148 and ORGANIZATION_ID=148 and TRANSFER_ORGANIZATION_ID =148 and XX_PROCESS_FLAG=100;

ELSIF I=3 THEN
update XX_UPDATE_TRANSFER_PRICE set XX_PROCESS_FLAG=992, PROCESS_STATUS='COMPLETED' where transaction_id=148 and inventory_item_id=148 and xx_period_id=148 and ORGANIZATION_ID=148 and TRANSFER_ORGANIZATION_ID =148 and XX_PROCESS_FLAG=100;

END IF;







Insert into XX_UPDATE_TRANSFER_PRICE
(TRANSACTION_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID, TRANSFER_PRICE, NEW_TRANSFER_PRICE,
TRANSFER_ORGANIZATION_ID, XX_TRANSFER_PRICE, XX_PROCESS_FLAG, XX_PERIOD_ID,PROCESS_STATUS)
Values (148, 148, 148, 0, 0, 148, 0, 101, 148,'RUNNING'||i);
COMMIT;


*/


--- mmt transfer price update-----

declare
cursor c_mmtZ is
select * from XX_UPDATE_TRANSFER_PRICE WHERE PROCESS_STATUS IS NULL
--where TRANSACTION_ID = 1227428
order by TRANSACTION_ID;
h_mmtZ c_mmtZ%ROWTYPE;
BEGIN
OPEN c_mmtZ;
LOOP
FETCH c_mmtZ INTO h_mmtZ;
EXIT WHEN c_mmtZ%NOTFOUND;
update mtl_material_transactions
set TRANSFER_PRICE = h_mmtZ.NEW_TRANSFER_PRICE
where mtl_material_transactions.TRANSACTION_ID = h_mmtZ.transaction_id;
commit;
END LOOP;
exception
when others then
null;
CLOSE c_mmtZ;
END;
----------------------------------

update XX_UPDATE_TRANSFER_PRICE set XX_PROCESS_FLAG=990, PROCESS_STATUS='COMPLETED'; -- where transaction_id=148 and inventory_item_id=148 and xx_period_id=148 and ORGANIZATION_ID=148 and TRANSFER_ORGANIZATION_ID =148 and XX_PROCESS_FLAG=100 AND PROCESS_STATUS='RUNNING';
commit;
END LOOP;

exception
when others then
null;
CLOSE c_mmt;
END;



------------------------------------

--END LOOP;
COMMIT;

EXCEPTION
WHEN OTHERS THEN
-- DBMS_OUTPUT.PUT_LINE('Error -'||SQLERRM);
FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error - '||SQLERRM);
END;
/