CREATE OR REPLACE PROCEDURE APPS.PRG_COSTING_PERIOD_ALL_NEW_MK
(
errbuff OUT VARCHAR2,
retcode OUT NUMBER,
PERIODID IN NUMBER
--mmm VARCHAR2
)
is
--// ******************* created for Inv Periods Open as per OU ***********************************************
--// Created By : Mahfuz
--// Dated : 14 Sep 2014
--// Updated : 01 Sep 2020 , include STL
--// ICS/PML, DAIRY, SPL, STL, SAL, PABL and SBCL
--// Updated on : 22 Nov 2018 by Mahfuz for new org cancellation and Except eAM org
--// ******************* created by Mahfuz for Inv Periods Open ***********************************************
ddt number;
Tddt date;
cursor c1 is
select o.ORGANIZATION_ID P_OrgID from org_organization_definitions o, mtl_parameters p where o.ORGANIZATION_ID not in (370,1550,1650,1651)
and o.ORGANIZATION_ID=p.ORGANIZATION_ID and p.PROCESS_ENABLED_FLAG='Y' and DISABLE_DATE is null;
c1_hd c1%rowtype;
begin
DELETE xx_update_transfer_price_m;
commit;
INSERT INTO xx_update_transfer_price_m
(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, type_ind
)
VALUES (periodid, periodid, periodid,
0, 0,
periodid, 0,
100, periodid, 'RUNNING', 'START'
);
COMMIT;
-- Start ME -------------------
INSERT INTO xx_update_transfer_price_m
SELECT DISTINCT mtl.transaction_id, mtl.inventory_item_id,
mtl.organization_id, orgto.organization_code toorgcode,
orgto.organization_name toorgname, mtl.transfer_price,
0 new_transfer_price, mtl.transfer_organization_id,
orgfrm1.organization_code fromorgcode,
orgfrm1.organization_name fromorgname,
mtl.transfer_price tp, 0 xx_process_flag,
periodid xx_period_id, NULL, cmpnt_cost, 'NA' item_code,
'ME2OPM' type_ind
FROM mtl_material_transactions mtl,
org_organization_definitions orgfrm1 -- From org
,
org_organization_definitions orgto -- To org
,
(SELECT inventory_item_id, item_cost cmpnt_cost,
organization_id, periodid period_id
FROM cst_item_costs
WHERE item_cost <> 0) cst,
mtl_parameters op
WHERE mtl.source_code = 'RCV'
AND TRUNC (mtl.transaction_date)
BETWEEN (SELECT TRUNC (start_date)
FROM gmf_period_statuses
WHERE period_id = periodid
-- v_period_id
)
AND (SELECT TRUNC (end_date)
FROM gmf_period_statuses
WHERE period_id = periodid)
AND mtl.transaction_action_id = 12
AND mtl.transfer_organization_id = orgfrm1.organization_id
AND mtl.organization_id = orgto.organization_id
AND orgto.organization_id = op.organization_id
AND op.process_enabled_flag = 'Y'
--and orgTo.ORGANIZATION_NAME not like '%-ME-%'
--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 ( mtl.transfer_price <> cst.cmpnt_cost
OR cst.cmpnt_cost IS NULL
);
COMMIT;
DECLARE
CURSOR c_mmt
IS
SELECT *
FROM xx_update_transfer_price_m
WHERE transaction_id = periodid
AND xx_process_flag = 100
AND process_status = 'RUNNING';
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_mmtz
IS
SELECT *
FROM xx_update_transfer_price_m
WHERE process_status IS NULL
AND cmpnt_cost <> 0
AND type_ind = 'ME2OPM';
--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.cmpnt_cost,
actual_cost = NULL,
transaction_cost = 0
WHERE mtl_material_transactions.transaction_id =
h_mmtz.transaction_id;
UPDATE xx_update_transfer_price_m
SET xx_process_flag = 990, xx_transfer_price = h_mmtz.cmpnt_cost,
process_status = 'COMPLETED'
WHERE transaction_id = h_mmtz.transaction_id;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
CLOSE c_mmtz;
END;
----------------------------------
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
CLOSE c_mmt;
END; -------------------------- END ME -------------------
--------Start SAL ----------------------------
INSERT INTO xx_update_transfer_price_m
SELECT mtl.transaction_id, i.inventory_item_id, i.organization_id,
o.organization_code, o.organization_name, mtl.transfer_price,
0 new_transfer_price, mtl.transfer_organization_id,
orgto.organization_code toorgcode,
orgto.organization_name toorgname, mtl.transfer_price tp,
0 xx_process_flag, periodid xx_period_id, NULL, -- CMPNT_COST
c.sales_price cmpnt_cost, i.segment1, 'SAL' type_ind
--,o.OPERATING_UNIT fromou, orgto.OPERATING_UNIT toou, orgto.ORGANIZATION_CODE, orgto.ORGANIZATION_NAME
FROM mpfl_sales_price_ics c,
org_organization_definitions o,
mtl_system_items_b i,
mtl_material_transactions mtl,
org_organization_definitions orgto
WHERE c.org_code = o.operating_unit
AND c.item_code = i.segment1
AND i.organization_id = o.organization_id
AND c.proc_type = 'SAL'
AND mtl.source_code = 'RCV'
--and orgTo.ORGANIZATION_CODE in ('415','416','417')
AND TRUNC (mtl.transaction_date) BETWEEN (SELECT TRUNC (start_date)
FROM gmf_period_statuses
WHERE period_id = periodid
-- v_period_id
)
AND (SELECT TRUNC (end_date)
FROM gmf_period_statuses
WHERE period_id = periodid)
AND mtl.transaction_action_id = 12
AND mtl.transfer_organization_id = o.organization_id
AND mtl.inventory_item_id = i.inventory_item_id
AND mtl.organization_id = orgto.organization_id
AND o.operating_unit <> orgto.operating_unit
AND orgto.operating_unit not in (148);
COMMIT;
--FOR i IN 1 .. 1 LOOP
-------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE--------- --- mmt transfer price update-----
DECLARE
CURSOR c_mmtz_sal
IS
SELECT *
FROM xx_update_transfer_price_m
WHERE process_status IS NULL AND cmpnt_cost <> 0
AND type_ind = 'SAL';
-- AND item_code = '0273015700';
--where TRANSACTION_ID = 1227428
-- order by TRANSACTION_ID;
h_mmtz_sal c_mmtz_sal%ROWTYPE;
BEGIN
OPEN c_mmtz_sal;
LOOP
FETCH c_mmtz_sal
INTO h_mmtz_sal;
EXIT WHEN c_mmtz_sal%NOTFOUND;
UPDATE mtl_material_transactions
SET transfer_price = h_mmtz_sal.cmpnt_cost,
actual_cost = NULL,
transaction_cost = 0
WHERE mtl_material_transactions.transaction_id =
h_mmtz_sal.transaction_id;
UPDATE xx_update_transfer_price_m
SET xx_process_flag = 990,
process_status = 'COMPLETED',
xx_transfer_price = h_mmtz_sal.cmpnt_cost
WHERE transaction_id = h_mmtz_sal.transaction_id;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
CLOSE c_mmtz_sal;
END; ----------END SAL --------------------------
--------Start SPL ----------------------------
INSERT INTO xx_update_transfer_price_m
SELECT mtl.transaction_id, i.inventory_item_id, i.organization_id,
o.organization_code, o.organization_name, mtl.transfer_price,
0 new_transfer_price, mtl.transfer_organization_id,
orgto.organization_code toorgcode,
orgto.organization_name toorgname, mtl.transfer_price tp,
0 xx_process_flag, periodid xx_period_id, NULL, -- CMPNT_COST
c.sales_price cmpnt_cost, i.segment1, 'SPL' type_ind
--,o.OPERATING_UNIT fromou, orgto.OPERATING_UNIT toou, orgto.ORGANIZATION_CODE, orgto.ORGANIZATION_NAME
FROM mpfl_sales_price_ics c,
org_organization_definitions o,
mtl_system_items_b i,
mtl_material_transactions mtl,
org_organization_definitions orgto
WHERE c.org_code = o.operating_unit
AND c.item_code = i.segment1
AND i.organization_id = o.organization_id
AND c.proc_type = 'SPL'
AND mtl.source_code = 'RCV'
--and orgTo.ORGANIZATION_CODE in ('415','416','417')
AND TRUNC (mtl.transaction_date) BETWEEN (SELECT TRUNC (start_date)
FROM gmf_period_statuses
WHERE period_id = periodid
-- v_period_id
)
AND (SELECT TRUNC (end_date)
FROM gmf_period_statuses
WHERE period_id = periodid)
AND mtl.transaction_action_id = 12
AND mtl.transfer_organization_id = o.organization_id
AND mtl.inventory_item_id = i.inventory_item_id
AND mtl.organization_id = orgto.organization_id
AND o.operating_unit <> orgto.operating_unit
AND orgto.operating_unit not in (148);
COMMIT;
--FOR i IN 1 .. 1 LOOP
-------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE--------- --- mmt transfer price update-----
DECLARE
CURSOR c_mmtz_SPL
IS
SELECT *
FROM xx_update_transfer_price_m
WHERE process_status IS NULL AND cmpnt_cost <> 0
AND type_ind = 'SPL';
-- AND item_code = '0273015700';
--where TRANSACTION_ID = 1227428
-- order by TRANSACTION_ID;
h_mmtz_SPL c_mmtz_SPL%ROWTYPE;
BEGIN
OPEN c_mmtz_SPL;
LOOP
FETCH c_mmtz_SPL
INTO h_mmtz_SPL;
EXIT WHEN c_mmtz_SPL%NOTFOUND;
UPDATE mtl_material_transactions
SET transfer_price = h_mmtz_SPL.cmpnt_cost,
actual_cost = NULL,
transaction_cost = 0
WHERE mtl_material_transactions.transaction_id =
h_mmtz_SPL.transaction_id;
UPDATE xx_update_transfer_price_m
SET xx_process_flag = 990,
process_status = 'COMPLETED',
xx_transfer_price = h_mmtz_SPL.cmpnt_cost
WHERE transaction_id = h_mmtz_SPL.transaction_id;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
CLOSE c_mmtz_SPL;
END; ----------END SPL --------------------------
--------Start STL ----------------------------
INSERT INTO xx_update_transfer_price_m
SELECT mtl.transaction_id, i.inventory_item_id, i.organization_id,
o.organization_code, o.organization_name, mtl.transfer_price,
0 new_transfer_price, mtl.transfer_organization_id,
orgto.organization_code toorgcode,
orgto.organization_name toorgname, mtl.transfer_price tp,
0 xx_process_flag, periodid xx_period_id, NULL, -- CMPNT_COST
c.sales_price cmpnt_cost, i.segment1, 'STL' type_ind
--,o.OPERATING_UNIT fromou, orgto.OPERATING_UNIT toou, orgto.ORGANIZATION_CODE, orgto.ORGANIZATION_NAME
FROM mpfl_sales_price_ics c,
org_organization_definitions o,
mtl_system_items_b i,
mtl_material_transactions mtl,
org_organization_definitions orgto
WHERE c.org_code = o.operating_unit
AND c.item_code = i.segment1
AND i.organization_id = o.organization_id
AND c.proc_type = 'STL'
AND mtl.source_code = 'RCV'
--and orgTo.ORGANIZATION_CODE in ('415','416','417')
AND TRUNC (mtl.transaction_date) BETWEEN (SELECT TRUNC (start_date)
FROM gmf_period_statuses
WHERE period_id = periodid
-- v_period_id
)
AND (SELECT TRUNC (end_date)
FROM gmf_period_statuses
WHERE period_id = periodid)
AND mtl.transaction_action_id = 12
AND mtl.transfer_organization_id = o.organization_id
AND mtl.inventory_item_id = i.inventory_item_id
AND mtl.organization_id = orgto.organization_id
AND o.operating_unit <> orgto.operating_unit
AND orgto.operating_unit not in (148);
COMMIT;
--FOR i IN 1 .. 1 LOOP
-------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE--------- --- mmt transfer price update-----
DECLARE
CURSOR c_mmtz_STL
IS
SELECT *
FROM xx_update_transfer_price_m
WHERE process_status IS NULL AND cmpnt_cost <> 0
AND type_ind = 'STL';
-- AND item_code = '0273015700';
--where TRANSACTION_ID = 1227428
-- order by TRANSACTION_ID;
h_mmtz_STL c_mmtz_STL%ROWTYPE;
BEGIN
OPEN c_mmtz_STL;
LOOP
FETCH c_mmtz_STL
INTO h_mmtz_STL;
EXIT WHEN c_mmtz_STL%NOTFOUND;
UPDATE mtl_material_transactions
SET transfer_price = h_mmtz_STL.cmpnt_cost,
actual_cost = NULL,
transaction_cost = 0
WHERE mtl_material_transactions.transaction_id =
h_mmtz_STL.transaction_id;
UPDATE xx_update_transfer_price_m
SET xx_process_flag = 990,
process_status = 'COMPLETED',
xx_transfer_price = h_mmtz_STL.cmpnt_cost
WHERE transaction_id = h_mmtz_STL.transaction_id;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
CLOSE c_mmtz_STL;
END; ----------END STL --------------------------
--------Start SBCL ----------------------------
INSERT INTO xx_update_transfer_price_m
SELECT mtl.transaction_id, i.inventory_item_id, i.organization_id,
o.organization_code, o.organization_name, mtl.transfer_price,
0 new_transfer_price, mtl.transfer_organization_id,
orgto.organization_code toorgcode,
orgto.organization_name toorgname, mtl.transfer_price tp,
0 xx_process_flag, periodid xx_period_id, NULL, -- CMPNT_COST
c.sales_price cmpnt_cost, i.segment1, 'SBCL' type_ind
--,o.OPERATING_UNIT fromou, orgto.OPERATING_UNIT toou, orgto.ORGANIZATION_CODE, orgto.ORGANIZATION_NAME
FROM mpfl_sales_price_ics c,
org_organization_definitions o,
mtl_system_items_b i,
mtl_material_transactions mtl,
org_organization_definitions orgto
WHERE c.org_code = o.operating_unit
AND c.item_code = i.segment1
AND i.organization_id = o.organization_id
AND c.proc_type = 'SBCL'
AND mtl.source_code = 'RCV'
--and orgTo.ORGANIZATION_CODE in ('415','416','417')
AND TRUNC (mtl.transaction_date) BETWEEN (SELECT TRUNC (start_date)
FROM gmf_period_statuses
WHERE period_id = periodid
-- v_period_id
)
AND (SELECT TRUNC (end_date)
FROM gmf_period_statuses
WHERE period_id = periodid)
AND mtl.transaction_action_id = 12
AND mtl.transfer_organization_id = o.organization_id
AND mtl.inventory_item_id = i.inventory_item_id
AND mtl.organization_id = orgto.organization_id
AND o.operating_unit <> orgto.operating_unit
AND orgto.operating_unit not in (148);
COMMIT;
--FOR i IN 1 .. 1 LOOP
-------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE--------- --- mmt transfer price update-----
DECLARE
CURSOR c_mmtz_SBCL
IS
SELECT *
FROM xx_update_transfer_price_m
WHERE process_status IS NULL AND cmpnt_cost <> 0
AND type_ind = 'SBCL';
-- AND item_code = '0273015700';
--where TRANSACTION_ID = 1227428
-- order by TRANSACTION_ID;
h_mmtz_SBCL c_mmtz_SBCL%ROWTYPE;
BEGIN
OPEN c_mmtz_SBCL;
LOOP
FETCH c_mmtz_SBCL
INTO h_mmtz_SBCL;
EXIT WHEN c_mmtz_SBCL%NOTFOUND;
UPDATE mtl_material_transactions
SET transfer_price = h_mmtz_SBCL.cmpnt_cost,
actual_cost = NULL,
transaction_cost = 0
WHERE mtl_material_transactions.transaction_id =
h_mmtz_SBCL.transaction_id;
UPDATE xx_update_transfer_price_m
SET xx_process_flag = 990,
process_status = 'COMPLETED',
xx_transfer_price = h_mmtz_SBCL.cmpnt_cost
WHERE transaction_id = h_mmtz_SBCL.transaction_id;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
CLOSE c_mmtz_SBCL;
END; ----------END SBCL --------------------------
--------Start PABL ----------------------------
INSERT INTO xx_update_transfer_price_m
SELECT mtl.transaction_id, i.inventory_item_id, i.organization_id,
o.organization_code, o.organization_name, mtl.transfer_price,
0 new_transfer_price, mtl.transfer_organization_id,
orgto.organization_code toorgcode,
orgto.organization_name toorgname, mtl.transfer_price tp,
0 xx_process_flag, periodid xx_period_id, NULL, -- CMPNT_COST
c.sales_price cmpnt_cost, i.segment1, 'PABL' type_ind
--,o.OPERATING_UNIT fromou, orgto.OPERATING_UNIT toou, orgto.ORGANIZATION_CODE, orgto.ORGANIZATION_NAME
FROM mpfl_sales_price_ics c,
org_organization_definitions o,
mtl_system_items_b i,
mtl_material_transactions mtl,
org_organization_definitions orgto
WHERE c.org_code = o.operating_unit
AND c.item_code = i.segment1
AND i.organization_id = o.organization_id
AND c.proc_type = 'PABL'
AND mtl.source_code = 'RCV'
--and orgTo.ORGANIZATION_CODE in ('415','416','417')
AND TRUNC (mtl.transaction_date) BETWEEN (SELECT TRUNC (start_date)
FROM gmf_period_statuses
WHERE period_id = periodid
-- v_period_id
)
AND (SELECT TRUNC (end_date)
FROM gmf_period_statuses
WHERE period_id = periodid)
AND mtl.transaction_action_id = 12
AND mtl.transfer_organization_id = o.organization_id
AND mtl.inventory_item_id = i.inventory_item_id
AND mtl.organization_id = orgto.organization_id
AND o.operating_unit <> orgto.operating_unit
AND orgto.operating_unit not in (148);
COMMIT;
--FOR i IN 1 .. 1 LOOP
-------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE--------- --- mmt transfer price update-----
DECLARE
CURSOR c_mmtz_pabl
IS
SELECT *
FROM xx_update_transfer_price_m
WHERE process_status IS NULL AND cmpnt_cost <> 0
AND type_ind = 'PABL';
-- AND item_code = '0273015700';
--where TRANSACTION_ID = 1227428
-- order by TRANSACTION_ID;
h_mmtz_pabl c_mmtz_pabl%ROWTYPE;
BEGIN
OPEN c_mmtz_pabl;
LOOP
FETCH c_mmtz_pabl
INTO h_mmtz_pabl;
EXIT WHEN c_mmtz_pabl%NOTFOUND;
UPDATE mtl_material_transactions
SET transfer_price = h_mmtz_pabl.cmpnt_cost,
actual_cost = NULL,
transaction_cost = 0
WHERE mtl_material_transactions.transaction_id =
h_mmtz_pabl.transaction_id;
UPDATE xx_update_transfer_price_m
SET xx_process_flag = 990,
process_status = 'COMPLETED',
xx_transfer_price = h_mmtz_pabl.cmpnt_cost
WHERE transaction_id = h_mmtz_pabl.transaction_id;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
CLOSE c_mmtz_pabl;
END; ----------END PABL --------------------------
--------Start ICS -----------------------------------
INSERT INTO xx_update_transfer_price_m
SELECT DISTINCT mtl.transaction_id, mtl.inventory_item_id,
mtl.organization_id, orgto.organization_code toorgcode,
orgto.organization_name toorgname, mtl.transfer_price,
0 new_transfer_price, mtl.transfer_organization_id,
orgfrm1.organization_code fromorgcode,
orgfrm1.organization_name fromorgname,
mtl.transfer_price tp, 0 xx_process_flag,
periodid xx_period_id, NULL, cmpnt_cost, item_code,
'ICS' type_ind
FROM mtl_material_transactions mtl,
org_organization_definitions orgfrm1 -- From org
,
org_organization_definitions orgto -- To org
,
(SELECT i.segment1 item_code, i.inventory_item_id,
sales_price cmpnt_cost, i.segment1,
i.organization_id
FROM mpfl_sales_price_ics c,
org_organization_definitions o,
mtl_system_items_b i
WHERE c.org_code = o.organization_code
AND c.item_code = i.segment1
AND i.organization_id = o.organization_id
AND proc_type = 'ICS') cst
WHERE mtl.source_code = 'RCV'
AND orgto.organization_code IN ('415', '416', '417')
AND TRUNC (mtl.transaction_date)
BETWEEN (SELECT TRUNC (start_date)
FROM gmf_period_statuses
WHERE period_id = periodid
-- v_period_id
)
AND (SELECT TRUNC (end_date)
FROM gmf_period_statuses
WHERE period_id = 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 orgto.operating_unit not in (148)
AND mtl.organization_id = cst.organization_id
AND mtl.inventory_item_id = cst.inventory_item_id;
COMMIT;
-----------------------------------------------
-------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE--------- --- mmt transfer price update-----
DECLARE
CURSOR c_mmtz_ics
IS
SELECT *
FROM xx_update_transfer_price_m
WHERE process_status IS NULL AND type_ind = 'ICS';
h_mmtz_ics c_mmtz_ics%ROWTYPE;
BEGIN
OPEN c_mmtz_ics;
LOOP
FETCH c_mmtz_ics
INTO h_mmtz_ics;
EXIT WHEN c_mmtz_ics%NOTFOUND;
UPDATE mtl_material_transactions
SET transfer_price = h_mmtz_ics.cmpnt_cost,
actual_cost = NULL,
transaction_cost = 0
WHERE mtl_material_transactions.transaction_id =
h_mmtz_ics.transaction_id;
UPDATE xx_update_transfer_price_m
SET xx_process_flag = 990,
process_status = 'COMPLETED',
xx_transfer_price = h_mmtz_ics.cmpnt_cost
WHERE transaction_id = h_mmtz_ics.transaction_id;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
CLOSE c_mmtz_ics;
END;
------------------------------------
--END LOOP;
COMMIT;
INSERT INTO xx_update_transfer_price_m
SELECT mtl.transaction_id, i.inventory_item_id, i.organization_id,
o.organization_code, o.organization_name, mtl.transfer_price,
0 new_transfer_price, mtl.transfer_organization_id,
orgto.organization_code toorgcode,
orgto.organization_name toorgname, mtl.transfer_price tp,
0 xx_process_flag, periodid xx_period_id, NULL, -- CMPNT_COST
c.sales_price cmpnt_cost, i.segment1, 'ICSNEW' type_ind
--,o.OPERATING_UNIT fromou, orgto.OPERATING_UNIT toou, orgto.ORGANIZATION_CODE, orgto.ORGANIZATION_NAME
FROM mpfl_sales_price_ics c,
org_organization_definitions o,
mtl_system_items_b i,
mtl_material_transactions mtl,
org_organization_definitions orgto
WHERE --c.org_code
o.operating_unit IN (133,135,141,432,433,1087)
--= o.operating_unit
AND c.item_code = i.segment1
AND i.organization_id = o.organization_id
AND c.proc_type = 'ICS'
AND mtl.source_code = 'RCV'
--and orgTo.ORGANIZATION_CODE in ('415','416','417')
AND TRUNC (mtl.transaction_date) BETWEEN (SELECT TRUNC (start_date)
FROM gmf_period_statuses
WHERE period_id = periodid
-- v_period_id
)
AND (SELECT TRUNC (end_date)
FROM gmf_period_statuses
WHERE period_id = periodid)
AND mtl.transaction_action_id = 12
AND mtl.transfer_organization_id = o.organization_id
AND mtl.inventory_item_id = i.inventory_item_id
AND mtl.organization_id = orgto.organization_id
AND o.operating_unit <> orgto.operating_unit
AND orgto.operating_unit not in (148);
COMMIT;
-------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE--------- --- mmt transfer price update-----
DECLARE
CURSOR c_mmtz_icsnew
IS
SELECT *
FROM xx_update_transfer_price_m
WHERE process_status IS NULL AND type_ind = 'ICSNEW';
h_mmtz_icsnew c_mmtz_icsnew%ROWTYPE;
BEGIN
OPEN c_mmtz_icsnew;
LOOP
FETCH c_mmtz_icsnew
INTO h_mmtz_icsnew;
EXIT WHEN c_mmtz_icsnew%NOTFOUND;
UPDATE mtl_material_transactions
SET transfer_price = h_mmtz_icsnew.cmpnt_cost,
actual_cost = NULL,
transaction_cost = 0
WHERE mtl_material_transactions.transaction_id =
h_mmtz_icsnew.transaction_id;
UPDATE xx_update_transfer_price_m
SET xx_process_flag = 990,
process_status = 'COMPLETED',
xx_transfer_price = h_mmtz_icsnew.cmpnt_cost
WHERE transaction_id = h_mmtz_icsnew.transaction_id;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
CLOSE c_mmtz_icsnew;
END;
--------Start DAIRY ----------------------------
INSERT INTO xx_update_transfer_price_m
SELECT mtl.transaction_id, i.inventory_item_id, i.organization_id,
o.organization_code, o.organization_name, mtl.transfer_price,
0 new_transfer_price, mtl.transfer_organization_id,
orgto.organization_code toorgcode,
orgto.organization_name toorgname, mtl.transfer_price tp,
0 xx_process_flag, periodid xx_period_id, NULL, -- CMPNT_COST
c.sales_price cmpnt_cost, i.segment1, 'DAIRY' type_ind
--,o.OPERATING_UNIT fromou, orgto.OPERATING_UNIT toou, orgto.ORGANIZATION_CODE, orgto.ORGANIZATION_NAME
FROM mpfl_sales_price_ics c,
org_organization_definitions o,
mtl_system_items_b i,
mtl_material_transactions mtl,
org_organization_definitions orgto
WHERE c.org_code = o.operating_unit
AND c.item_code = i.segment1
AND i.organization_id = o.organization_id
AND c.proc_type = 'DAIRY'
AND mtl.source_code = 'RCV'
--and orgTo.ORGANIZATION_CODE in ('415','416','417')
AND TRUNC (mtl.transaction_date) BETWEEN (SELECT TRUNC (start_date)
FROM gmf_period_statuses
WHERE period_id = periodid
-- v_period_id
)
AND (SELECT TRUNC (end_date)
FROM gmf_period_statuses
WHERE period_id = periodid)
AND mtl.transaction_action_id = 12
AND mtl.transfer_organization_id = o.organization_id
AND mtl.inventory_item_id = i.inventory_item_id
AND mtl.organization_id = orgto.organization_id
AND o.operating_unit <> orgto.operating_unit
AND orgto.operating_unit not in (148)
;
COMMIT;
--FOR i IN 1 .. 1 LOOP
-------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE--------- --- mmt transfer price update-----
DECLARE
CURSOR c_mmtz_DAIRY
IS
SELECT *
FROM xx_update_transfer_price_m
WHERE process_status IS NULL AND cmpnt_cost <> 0
AND type_ind = 'DAIRY';
-- AND item_code = '0273015700';
--where TRANSACTION_ID = 1227428
-- order by TRANSACTION_ID;
h_mmtz_DAIRY c_mmtz_DAIRY%ROWTYPE;
BEGIN
OPEN c_mmtz_DAIRY;
LOOP
FETCH c_mmtz_DAIRY
INTO h_mmtz_DAIRY;
EXIT WHEN c_mmtz_DAIRY%NOTFOUND;
UPDATE mtl_material_transactions
SET transfer_price = h_mmtz_DAIRY.cmpnt_cost,
actual_cost = NULL,
transaction_cost = 0
WHERE mtl_material_transactions.transaction_id =
h_mmtz_DAIRY.transaction_id;
UPDATE xx_update_transfer_price_m
SET xx_process_flag = 990,
process_status = 'COMPLETED',
xx_transfer_price = h_mmtz_DAIRY.cmpnt_cost
WHERE transaction_id = h_mmtz_DAIRY.transaction_id;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
CLOSE c_mmtz_DAIRY;
END; ----------END DAIRY --------------------------
--------------------ICS Inhouse -------------------------
INSERT INTO xx_update_transfer_price_m
SELECT mtl.transaction_id, i.inventory_item_id, i.organization_id,
o.organization_code, o.organization_name, mtl.transfer_price,
0 new_transfer_price, mtl.transfer_organization_id,
orgto.organization_code toorgcode,
orgto.organization_name toorgname, mtl.transfer_price tp,
0 xx_process_flag, periodid xx_period_id, NULL, -- CMPNT_COST
c.sales_price cmpnt_cost, i.segment1, 'ICSINH' type_ind
FROM mpfl_sales_price_ics c,
org_organization_definitions o,
mtl_system_items_b i,
mtl_material_transactions mtl,
org_organization_definitions orgto
WHERE o.operating_unit IN (141)
AND c.item_code = i.segment1
AND i.organization_id = o.organization_id
AND c.proc_type = 'ICS'
AND mtl.source_code = 'RCV'
AND TRUNC (mtl.transaction_date) BETWEEN (SELECT TRUNC (start_date)
FROM gmf_period_statuses
WHERE period_id = periodid
-- v_period_id
)
AND (SELECT TRUNC (end_date)
FROM gmf_period_statuses
WHERE period_id = periodid)
AND mtl.transaction_action_id = 12
AND mtl.transfer_organization_id = o.organization_id
AND mtl.inventory_item_id = i.inventory_item_id
AND mtl.organization_id = orgto.organization_id
AND orgto.operating_unit in (135,433);
COMMIT;
-------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE--------- --- mmt transfer price update-----
DECLARE
CURSOR c_mmtz_icsinh
IS
SELECT *
FROM xx_update_transfer_price_m
WHERE process_status IS NULL AND type_ind = 'ICSINH';
h_mmtz_icsinh c_mmtz_icsinh%ROWTYPE;
BEGIN
OPEN c_mmtz_icsinh;
LOOP
FETCH c_mmtz_icsinh
INTO h_mmtz_icsinh;
EXIT WHEN c_mmtz_icsinh%NOTFOUND;
UPDATE mtl_material_transactions
SET transfer_price = h_mmtz_icsinh.cmpnt_cost,
actual_cost = NULL,
transaction_cost = 0
WHERE mtl_material_transactions.transaction_id =
h_mmtz_icsinh.transaction_id;
UPDATE xx_update_transfer_price_m
SET xx_process_flag = 990,
process_status = 'COMPLETED',
xx_transfer_price = h_mmtz_icsinh.cmpnt_cost
WHERE transaction_id = h_mmtz_icsinh.transaction_id;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
CLOSE c_mmtz_icsinh;
END;
------------------------------------
--END LOOP;
COMMIT;
--------END ICS ---------------------------------------------
--END LOOP;
COMMIT;
APPS.PRG_COSTING_PERIOD_ACROSS_MK(periodid);
COMMIT;
select to_number(to_char(to_date(sysdate,'dd-mm-yy'),'dd')) into ddt from dual;
select to_date('01/'||TO_CHAR(ADD_MONTHS(to_date(sysdate,'dd-mm-yy'),2),'MM') || '/' || TO_CHAR(ADD_MONTHS(to_date(sysdate,'dd-mm-yy'),2),'YY'),'dd-mm-yy') into Tddt from dual
;
if ddt>=26 then
open c1;
loop
Fetch c1 into c1_hd;
EXIT WHEN c1%NOTFOUND;
-- APPS.XX_ORG_ACCT_PERIODS(c1_hd.P_OrgID,Tddt);
commit;
end loop;
commit;
-- APPS.mpfl_OldPeriod_cancelation();
close c1;
end if;
EXCEPTION
WHEN OTHERS
THEN
-- DBMS_OUTPUT.PUT_LINE('Error -'||SQLERRM);
fnd_file.put_line (fnd_file.LOG, 'Error - ' || SQLERRM);
END;
/
Monday 31 August 2020
Transfer Price Update R12
Subscribe to:
Posts (Atom)
Internal location is Already assigned to another Ship-to Address
SELECT * FROM hz_cust_accounts WHERE ACCOUNT_NUMBER = 'R08'; -- CUST_ACCOUNT_ID =51401 , PARTY_ID= 235048 SELECT * FRO...
-
FND API to Delete, Add and Update value set values in Oracle Applications 01. Need to create a table like : CREATE TABLE...
-
OPM Financials Tables: TABLE NAME DESCRIPTION CM_ACER_MSG The Actual Cost Process Error Message Table will provide the user a...
-
/* opm_undo_create_accounting_by_date_range_and_LE_R12.sql +=======================================================================+ * ...