[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
MAX(iteration_count)
FROM
CST_PAC_INTORG_ITMS_TEMP
WHERE pac_period_id = c_period_id
AND inventory_item_id = c_inventory_item_id
AND tolerance_flag = 'N';
SELECT
SUM(actual_cost)
FROM
mtl_pac_act_cst_dtl_temp
WHERE cost_group_id = c_cost_group_id
AND cost_type_id = c_cost_type_id
AND pac_period_id <= c_period_id
AND transaction_id = c_transaction_id;
SELECT
actual_cost
FROM
mtl_pac_act_cst_dtl_temp
WHERE cost_group_id = c_cost_group_id
AND pac_period_id <= c_period_id
AND cost_type_id = c_cost_type_id
AND transaction_id = c_transaction_id
AND cost_element_id = c_cost_element_id
AND level_type = c_level_type;
SELECT
actual_cost
, new_cost
FROM
mtl_pac_act_cst_dtl_temp
WHERE cost_group_id = c_cost_group_id
AND pac_period_id <= c_period_id
AND cost_type_id = c_cost_type_id
AND transaction_id = c_opp_transaction_id
AND cost_element_id = c_cost_element_id
AND level_type = c_level_type;
SELECT
actual_cost
FROM
mtl_pac_act_cst_dtl_temp
WHERE cost_group_id = c_cost_group_id
AND pac_period_id <= c_period_id
AND cost_type_id = c_cost_type_id
AND transaction_id = c_transaction_id
AND cost_element_id = c_cost_element_id
AND level_type = c_level_type;
SELECT
cost_layer_id
, cost_element_id
, level_type
, actual_cost
FROM
mtl_pac_act_cst_dtl_temp
WHERE pac_period_id = c_period_id
AND cost_group_id = c_cost_group_id
AND transaction_id = c_transaction_id
AND inventory_item_id = c_inventory_item_id
ORDER BY
cost_element_id
, level_type
, transaction_id
FOR UPDATE;
SELECT
ccit.transaction_id transaction_id
, ccit.transaction_action_id transaction_action_id
, ccit.organization_id organization_id
, nvl(ccit.transfer_organization_id,-1) transfer_organization_id
, ccit.transfer_transaction_id transfer_transaction_id
, ccit.cost_group_id cost_group_id
, ccit.txn_type txn_type
FROM
CST_PAC_INTERORG_TXNS_TMP ccit, cst_pac_intorg_itms_temp cpiit
WHERE ccit.inventory_item_id = c_inventory_item_id
AND ccit.pac_period_id = c_period_id
AND cpiit.inventory_item_id = ccit.inventory_item_id
AND cpiit.cost_group_id = ccit.cost_group_id
AND cpiit.pac_period_id = ccit.pac_period_id
AND cpiit.interorg_receipt_flag = 'Y'
AND cpiit.interorg_shipment_flag = 'Y'
ORDER BY ccit.cost_group_id, ccit.txn_type, ccit.transaction_id;
SELECT
cost_layer_id
FROM
CST_PAC_ITEM_COSTS
WHERE pac_period_id = c_period_id
AND cost_group_id = c_opp_cost_group_id
AND inventory_item_id = c_inventory_item_id;
SELECT
interorg_receipt_flag
, interorg_shipment_flag
FROM cst_pac_intorg_itms_temp
WHERE pac_period_id = c_period_id
AND cost_group_id = c_opp_cost_group_id
AND inventory_item_id = c_inventory_item_id;
update moh absorption cost in pac txn temp table
if the cost element <> 2 then retain as it is */
IF l_correspond_txn_flag = 'Y' AND pac_bal_txn_idx.cost_element_id = 2 THEN
BEGIN
SELECT SUM(nvl(actual_cost,0)) /* 11670136 */
INTO l_moh_absorption_cost
FROM MTL_PAC_COST_SUBELEMENTS
WHERE cost_group_id = l_pac_interorg_txns_tab(i).cost_group_id
AND transaction_id = l_pac_interorg_txns_tab(i).transaction_id
AND pac_period_id = p_period_id
AND cost_element_id = 2
AND level_type = pac_bal_txn_idx.level_type;
-- Update moh absorption cost in pac txn temp table
IF pac_bal_txn_idx.cost_element_id = 2 AND l_moh_absorption_cost <> 0 THEN
UPDATE MTL_PAC_ACT_CST_DTL_TEMP
SET moh_absorption_cost = l_moh_absorption_cost
WHERE CURRENT OF pac_bal_txn_cursor;
, 'MOH Absorption Cost Update:' || l_moh_absorption_cost
);
DO NOT delete this record as this record will be put back
into MPACD at the end of iteration process
No logic as the record will be retained as it is */
ELSIF l_pac_interorg_txns_tab(i).txn_type = 2 THEN
-- Cost derived (group 2) transactions include shipments
-- Corresponding cost owned (group 1) txn include receipt
-- If the corresponding transaction exists then retain as it is
-- If the corresponding transaction NOT exists, then insert the
-- corresponding transaction as it is required to be considered for
-- iteration process
-- Insert corresponding group 1 pac transaction if the current
-- group2 transaction exists and the transaction is direct interorg
-- DO NOT insert corresponding group 1 pac transaction if the
-- current group2 transaction is an intransit interorg transaction
-- and DO NOT use for comparison since the corresponding group 1 txn
-- may be across periods.
IF (l_correspond_txn_flag = 'N') AND (l_pac_interorg_txns_tab(i).transaction_action_id = 3 ) THEN
-- Get Corresponding Cost Group Id
l_correspond_cost_group_id := get_cost_group(l_pac_interorg_txns_tab(i).transfer_organization_id);
-- Insert into MTL_PAC_ACT_CST_DTL_TEMP
-- Cost owned transactions
INSERT INTO MTL_PAC_ACT_CST_DTL_TEMP
( COST_GROUP_ID
, TRANSACTION_ID
, PAC_PERIOD_ID
, COST_TYPE_ID
, COST_ELEMENT_ID
, LEVEL_TYPE
, INVENTORY_ITEM_ID
, COST_LAYER_ID
, PRIOR_COST
, ACTUAL_COST
, NEW_COST
, PRIOR_BUY_COST
, PRIOR_MAKE_COST
, NEW_BUY_COST
, NEW_MAKE_COST
, USER_ENTERED
, INSERTION_FLAG
, TRANSACTION_COSTED_DATE
, TRANSFER_TRANSACTION_ID
, TRANSFER_COST
, TRANSPORTATION_COST
, MOH_ABSORPTION_COST
) VALUES
( l_correspond_cost_group_id
, l_correspond_transaction_id
, p_period_id
, p_cost_type_id
, pac_bal_txn_idx.cost_element_id
, pac_bal_txn_idx.level_type
, p_inventory_item_id
, l_correspond_cost_layer_id
, 0
, pac_bal_txn_idx.actual_cost
, 0
, 0
, 0
, 0
, 0
,'Y'
,'N'
,NULL
,l_pac_interorg_txns_tab(i).transfer_transaction_id
,0
,0
,0
);
SELECT distinct cost_layer_id, mpacd.transaction_id
BULK COLLECT
INTO l_cost_layer_id_tbl, l_last_txn_id_tbl
FROM mtl_pac_actual_cost_details mpacd
WHERE mpacd.transaction_id = (SELECT max(mpacd1.transaction_id)
FROM mtl_pac_actual_cost_details mpacd1
WHERE mpacd1.txn_category = 8
AND mpacd1.inventory_item_id = mpacd.inventory_item_id
AND mpacd1.pac_period_id = p_pac_period_id
AND mpacd1.cost_group_id = p_cost_group_id)
AND mpacd.cost_group_id = p_cost_group_id
AND mpacd.pac_period_id = p_pac_period_id
AND mpacd.inventory_item_id = p_inventory_item_id
AND NOT EXISTS (SELECT 1
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.inventory_item_id = mpacd.inventory_item_id
AND cpllc.pac_period_id = p_pac_period_id
AND cpllc.cost_group_id = p_cost_group_id);
SELECT distinct cost_layer_id, mpacd.transaction_id
BULK COLLECT
INTO l_cost_layer_id_tbl, l_last_txn_id_tbl
FROM mtl_pac_actual_cost_details mpacd
WHERE mpacd.transaction_id = (SELECT max(mpacd1.transaction_id)
FROM mtl_pac_actual_cost_details mpacd1
WHERE mpacd1.txn_category = 8
AND mpacd1.inventory_item_id = mpacd.inventory_item_id
AND mpacd1.pac_period_id = p_pac_period_id
AND mpacd1.cost_group_id = p_cost_group_id)
AND mpacd.cost_group_id = p_cost_group_id
AND mpacd.pac_period_id = p_pac_period_id
AND mpacd.inventory_item_id = p_inventory_item_id
AND EXISTS (SELECT 1
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.inventory_item_id = mpacd.inventory_item_id
AND cpllc.low_level_code = p_low_level_code
AND cpllc.pac_period_id = p_pac_period_id
AND cpllc.cost_group_id = p_cost_group_id);
UPDATE mtl_pac_actual_cost_details mpacd
SET variance_amount = (SELECT decode (sign(l_period_quantity),
0, cpicd.item_balance,
(-1 * sign(cpicd.item_balance)), cpicd.item_balance,
0)
FROM cst_pac_item_costs cpic,
cst_pac_item_cost_details cpicd
WHERE cpic.cost_layer_id = cpicd.cost_layer_id
AND cpicd.cost_layer_id = l_cost_layer_id_tbl (l_index)
AND cpicd.cost_element_id = mpacd.cost_element_id
AND cpicd.level_type = mpacd.level_type),
last_update_date = sysdate,
last_updated_by = p_user_id,
last_update_login = p_login_id,
request_id = p_request_id,
program_application_id = p_prog_appl_id,
program_id = p_prog_id,
program_update_date = sysdate
WHERE transaction_id = l_last_txn_id_tbl (l_index)
AND mpacd.cost_group_id = p_cost_group_id
AND mpacd.pac_period_id = p_pac_period_id
AND mpacd.cost_layer_id = l_cost_layer_id_tbl(l_index)
AND (cost_element_id, level_type) = (SELECT cost_element_id, level_type
FROM cst_pac_item_cost_details cpicd
WHERE cpicd.cost_layer_id = l_cost_layer_id_tbl (l_index)
AND cpicd.cost_element_id = mpacd.cost_element_id
AND cpicd.level_type = mpacd.level_type);
UPDATE cst_pac_item_cost_details cpicd
SET (last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
item_cost,
item_buy_cost,
item_make_cost,
item_balance,
buy_balance,
make_balance) =
(SELECT sysdate,
p_user_id,
p_login_id,
p_request_id,
p_prog_appl_id,
p_prog_id,
sysdate,
decode (sign(l_period_quantity),
0, cpicd.item_cost,
(-1 * sign(cpicd.item_balance)), 0,
cpicd.item_balance / l_period_quantity),
decode (sign(l_period_quantity),
0, cpicd.item_buy_cost,
(-1 * sign(cpicd.item_balance)), 0,
decode (cpic.buy_quantity,
0, 0,
cpicd.buy_balance / cpic.buy_quantity)),
decode (sign(l_period_quantity),
0, cpicd.item_make_cost,
(-1 * sign(cpicd.item_balance)), 0,
decode (cpic.make_quantity,
0, 0,
cpicd.make_balance / cpic.make_quantity)),
decode (sign (l_period_quantity),
0, 0,
(-1 * sign(cpicd.item_balance)), 0,
(cpicd.item_balance / l_period_quantity) * cpic.total_layer_quantity),
/* cpicd.item_balance and l_period_quantity correspond to the balance and quantity after processing category 8
cpic.total_layer_quantity corresponds to the quantity after processing category 9 */
decode (sign (l_period_quantity),
0, 0,
(-1 * sign(cpicd.item_balance)), 0,
cpicd.buy_balance),
decode (sign (l_period_quantity),
0, 0,
(-1 * sign(cpicd.item_balance)), 0,
cpicd.make_balance)
FROM cst_pac_item_costs cpic
WHERE cpic.cost_layer_id = cpicd.cost_layer_id)
WHERE cpicd.cost_layer_id IN (select cost_layer_id
from cst_pac_item_costs
where inventory_item_id = p_inventory_item_id
and pac_period_id = p_pac_period_id
and cost_group_id = p_cost_group_id)
AND EXISTS (SELECT 1
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = p_pac_period_id
AND cppb.cost_group_id = p_cost_group_id
AND cppb.cost_layer_id = cpicd.cost_layer_id
AND cppb.cost_element_id = cpicd.cost_element_id
AND cppb.level_type = cpicd.level_type
AND cppb.inventory_item_id = p_inventory_item_id)
AND NOT EXISTS (SELECT 1
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.pac_period_id = p_pac_period_id
AND cpllc.cost_group_id = p_cost_group_id
AND cpllc.inventory_item_id = p_inventory_item_id);
UPDATE cst_pac_item_costs cpic
SET (last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
pl_material,
pl_material_overhead,
pl_resource,
pl_outside_processing,
pl_overhead,
tl_material,
tl_material_overhead,
tl_resource,
tl_outside_processing,
tl_overhead,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost,
pl_item_cost,
tl_item_cost,
item_cost,
item_buy_cost,
item_make_cost,
unburdened_cost,
burden_cost
) =
(SELECT p_user_id,
sysdate,
p_login_id,
p_request_id,
p_prog_appl_id,
p_prog_id,
sysdate,
SUM(DECODE(level_type,2,DECODE(cost_element_id,1,item_cost,0),0)) ,
SUM(DECODE(level_type,2,DECODE(cost_element_id,2,item_cost,0),0)) ,
SUM(DECODE(level_type,2,DECODE(cost_element_id,3,item_cost,0),0)) ,
SUM(DECODE(level_type,2,DECODE(cost_element_id,4,item_cost,0),0)) ,
SUM(DECODE(level_type,2,DECODE(cost_element_id,5,item_cost,0),0)) ,
SUM(DECODE(level_type,1,DECODE(cost_element_id,1,item_cost,0),0)) ,
SUM(DECODE(level_type,1,DECODE(cost_element_id,2,item_cost,0),0)) ,
SUM(DECODE(level_type,1,DECODE(cost_element_id,3,item_cost,0),0)) ,
SUM(DECODE(level_type,1,DECODE(cost_element_id,4,item_cost,0),0)) ,
SUM(DECODE(level_type,1,DECODE(cost_element_id,5,item_cost,0),0)) ,
SUM(DECODE(cost_element_id,1,item_cost,0)) ,
SUM(DECODE(cost_element_id,2,item_cost,0)) ,
SUM(DECODE(cost_element_id,3,item_cost,0)) ,
SUM(DECODE(cost_element_id,4,item_cost,0)) ,
SUM(DECODE(cost_element_id,5,item_cost,0)) ,
SUM(DECODE(level_type,2,item_cost,0)) ,
SUM(DECODE(level_type,1,item_cost,0)) ,
SUM(item_cost) ,
SUM(item_buy_cost) ,
SUM(item_make_cost),
SUM(DECODE(cost_element_id,2,DECODE(level_type,2,item_cost,0),item_cost)) ,
SUM(DECODE(cost_element_id,2,DECODE(level_type,1,item_cost,0),0))
FROM cst_pac_item_cost_details cpicd
WHERE cpicd.cost_layer_id = cpic.cost_layer_id)
WHERE cpic.pac_period_id = p_pac_period_id
AND cpic.cost_group_id = p_cost_group_id
AND cpic.inventory_item_id = p_inventory_item_id
AND EXISTS (SELECT 1
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = p_pac_period_id
AND cppb.cost_group_id = p_cost_group_id
AND cppb.cost_layer_id = cpic.cost_layer_id
AND cppb.inventory_item_id = p_inventory_item_id)
AND NOT EXISTS (SELECT 1
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.inventory_item_id = cpic.inventory_item_id
AND cpllc.pac_period_id = p_pac_period_id
AND cpllc.cost_group_id = p_cost_group_id)
AND EXISTS
(SELECT 'there is detail cost'
FROM cst_pac_item_cost_details cpicd
WHERE cpicd.cost_layer_id = cpic.cost_layer_id);
UPDATE cst_pac_item_cost_details cpicd
SET (last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
item_cost,
item_buy_cost,
item_make_cost,
item_balance,
buy_balance,
make_balance) =
(SELECT sysdate,
p_user_id,
p_login_id,
p_request_id,
p_prog_appl_id,
p_prog_id,
sysdate,
decode (sign(l_period_quantity),
0, cpicd.item_cost,
(-1 * sign(cpicd.item_balance)), 0,
cpicd.item_balance / l_period_quantity),
decode (sign(l_period_quantity),
0, cpicd.item_buy_cost,
(-1 * sign(cpicd.item_balance)), 0,
decode (cpic.buy_quantity,
0, 0,
cpicd.buy_balance / cpic.buy_quantity)),
decode (sign(l_period_quantity),
0, cpicd.item_make_cost,
(-1 * sign(cpicd.item_balance)), 0,
decode (cpic.make_quantity,
0, 0,
cpicd.make_balance / cpic.make_quantity)),
decode (sign (l_period_quantity),
0, 0,
(-1 * sign(cpicd.item_balance)), 0,
(cpicd.item_balance / l_period_quantity) * cpic.total_layer_quantity),
/* cpicd.item_balance and l_period_quantity correspond to the balance and quantity after processing category 8
cpic.total_layer_quantity corresponds to the quantity after processing category 9 */
decode (sign (l_period_quantity),
0, 0,
(-1 * sign(cpicd.item_balance)), 0,
cpicd.buy_balance),
decode (sign (l_period_quantity),
0, 0,
(-1 * sign(cpicd.item_balance)), 0,
cpicd.make_balance)
FROM cst_pac_item_costs cpic
WHERE cpic.cost_layer_id = cpicd.cost_layer_id)
WHERE cpicd.cost_layer_id IN (select cost_layer_id
from cst_pac_item_costs
where inventory_item_id = p_inventory_item_id
and pac_period_id = p_pac_period_id
and cost_group_id = p_cost_group_id)
AND EXISTS (SELECT 1
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = p_pac_period_id
AND cppb.cost_group_id = p_cost_group_id
AND cppb.cost_layer_id = cpicd.cost_layer_id
AND cppb.cost_element_id = cpicd.cost_element_id
AND cppb.level_type = cpicd.level_type
AND cppb.inventory_item_id = p_inventory_item_id)
AND EXISTS (SELECT 1
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.low_level_code = p_low_level_code
AND cpllc.pac_period_id = p_pac_period_id
AND cpllc.cost_group_id = p_cost_group_id
);
UPDATE cst_pac_item_costs cpic
SET (last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
pl_material,
pl_material_overhead,
pl_resource,
pl_outside_processing,
pl_overhead,
tl_material,
tl_material_overhead,
tl_resource,
tl_outside_processing,
tl_overhead,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost,
pl_item_cost,
tl_item_cost,
item_cost,
item_buy_cost,
item_make_cost,
unburdened_cost,
burden_cost
) =
(SELECT p_user_id,
sysdate,
p_login_id,
p_request_id,
p_prog_appl_id,
p_prog_id,
sysdate,
SUM(DECODE(level_type,2,DECODE(cost_element_id,1,item_cost,0),0)) ,
SUM(DECODE(level_type,2,DECODE(cost_element_id,2,item_cost,0),0)) ,
SUM(DECODE(level_type,2,DECODE(cost_element_id,3,item_cost,0),0)) ,
SUM(DECODE(level_type,2,DECODE(cost_element_id,4,item_cost,0),0)) ,
SUM(DECODE(level_type,2,DECODE(cost_element_id,5,item_cost,0),0)) ,
SUM(DECODE(level_type,1,DECODE(cost_element_id,1,item_cost,0),0)) ,
SUM(DECODE(level_type,1,DECODE(cost_element_id,2,item_cost,0),0)) ,
SUM(DECODE(level_type,1,DECODE(cost_element_id,3,item_cost,0),0)) ,
SUM(DECODE(level_type,1,DECODE(cost_element_id,4,item_cost,0),0)) ,
SUM(DECODE(level_type,1,DECODE(cost_element_id,5,item_cost,0),0)) ,
SUM(DECODE(cost_element_id,1,item_cost,0)) ,
SUM(DECODE(cost_element_id,2,item_cost,0)) ,
SUM(DECODE(cost_element_id,3,item_cost,0)) ,
SUM(DECODE(cost_element_id,4,item_cost,0)) ,
SUM(DECODE(cost_element_id,5,item_cost,0)) ,
SUM(DECODE(level_type,2,item_cost,0)) ,
SUM(DECODE(level_type,1,item_cost,0)) ,
SUM(item_cost) ,
SUM(item_buy_cost) ,
SUM(item_make_cost),
SUM(DECODE(cost_element_id,2,DECODE(level_type,2,item_cost,0),item_cost)) ,
SUM(DECODE(cost_element_id,2,DECODE(level_type,1,item_cost,0),0))
FROM cst_pac_item_cost_details cpicd
WHERE cpicd.cost_layer_id = cpic.cost_layer_id)
WHERE cpic.pac_period_id = p_pac_period_id
AND cpic.cost_group_id = p_cost_group_id
AND cpic.inventory_item_id = p_inventory_item_id
AND EXISTS (SELECT 1
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = p_pac_period_id
AND cppb.cost_group_id = p_cost_group_id
AND cppb.cost_layer_id = cpic.cost_layer_id
AND cppb.inventory_item_id = p_inventory_item_id)
AND EXISTS (SELECT 1
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.low_level_code = p_low_level_code
AND cpllc.inventory_item_id = cpic.inventory_item_id
AND cpllc.pac_period_id = p_pac_period_id
AND cpllc.cost_group_id = p_cost_group_id)
AND EXISTS
(SELECT 'there is detail cost'
FROM cst_pac_item_cost_details cpicd
WHERE cpicd.cost_layer_id = cpic.cost_layer_id);
PROCEDURE Calc_Pmac_Update_Cppb(p_pac_period_id IN NUMBER
,p_cost_type_id IN NUMBER
,p_cost_group_id IN NUMBER
,p_inventory_item_id IN NUMBER
,p_end_date IN DATE
,p_user_id IN NUMBER
,p_login_id IN NUMBER
,p_req_id IN NUMBER
,p_prg_id IN NUMBER
,p_prg_appid IN NUMBER
)
IS
l_routine CONSTANT VARCHAR2(30) := 'Calc_Pmac_Update_Cppb';
SELECT
low_level_code
FROM cst_pac_low_level_codes
WHERE pac_period_id = c_pac_period_id
AND cost_group_id = c_cost_group_id
AND inventory_item_id = c_inventory_item_id;
, '>> CST_PERIODIC_ABSORPTION_PROC.Calc_Pmac_Update_Cppb'
);
UPDATE CST_PAC_PERIOD_BALANCES cppb
SET (last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
txn_category_value,
period_quantity,
period_balance,
periodic_cost,
variance_amount
) =
(SELECT p_user_id,
sysdate,
p_login_id,
p_req_id,
p_prg_appid,
p_prg_id,
sysdate,
(SELECT sum (nvl (mpacd.actual_cost, 0) * nvl(mmt.periodic_primary_quantity,0))
FROM mtl_pac_actual_cost_details mpacd,
mtl_material_transactions mmt
WHERE mpacd.txn_category = 8
AND mpacd.inventory_item_id = p_inventory_item_id
AND mpacd.pac_period_id = p_pac_period_id
AND mpacd.cost_group_id = l_cost_group_id
AND mpacd.transaction_id = mmt.transaction_id
AND mpacd.inventory_item_id = mmt.inventory_item_id
AND mpacd.cost_layer_id = cppb.cost_layer_id
AND mpacd.cost_element_id = cppb.cost_element_id
AND mpacd.level_type = cppb.level_type),
l_period_quantity,
l_period_quantity * cpicd.item_cost,
cpicd.item_cost,
(SELECT sum (nvl (mpacd.variance_amount, 0))
FROM mtl_pac_actual_cost_details mpacd
WHERE mpacd.txn_category = 8
AND mpacd.inventory_item_id = p_inventory_item_id
AND mpacd.pac_period_id = p_pac_period_id
AND mpacd.cost_group_id = l_cost_group_id
AND mpacd.cost_layer_id = cppb.cost_layer_id
AND mpacd.cost_element_id = cppb.cost_element_id
AND mpacd.level_type = cppb.level_type)
FROM cst_pac_item_cost_details cpicd,
cst_pac_item_costs cpic
WHERE cpic.cost_layer_id = cpicd.cost_layer_id
AND cppb.cost_layer_id = cpicd.cost_layer_id
AND cppb.cost_element_id = cpicd.cost_element_id
AND cppb.level_type = cpicd.level_type)
WHERE cppb.pac_period_id = p_pac_period_id
AND cppb.cost_group_id = l_cost_group_id
AND cppb.inventory_item_id = p_inventory_item_id
AND cppb.txn_category = 8
AND EXISTS (SELECT 1
FROM CST_PAC_ITEM_COST_DETAILS cpicd1
WHERE cppb.cost_layer_id = cpicd1.cost_layer_id
AND cppb.cost_element_id = cpicd1.cost_element_id
AND cppb.level_type = cpicd1.level_type);
UPDATE CST_PAC_PERIOD_BALANCES cppb
SET txn_category_value =
(SELECT sum (nvl (mpacd.actual_cost, 0) * nvl(mmt.periodic_primary_quantity,0))
FROM mtl_pac_actual_cost_details mpacd,
mtl_material_transactions mmt
WHERE mpacd.txn_category = 9
AND mpacd.inventory_item_id = cppb.inventory_item_id
AND mpacd.pac_period_id = cppb.pac_period_id
AND mpacd.transaction_id = mmt.transaction_id
AND mpacd.inventory_item_id = mmt.inventory_item_id
AND mpacd.cost_group_id = cppb.cost_group_id
AND mpacd.cost_layer_id = cppb.cost_layer_id
AND mpacd.cost_element_id = cppb.cost_element_id
AND mpacd.level_type = cppb.level_type)
WHERE cppb.pac_period_id = p_pac_period_id
AND cppb.cost_group_id = l_cost_group_id
AND cppb.inventory_item_id = p_inventory_item_id
AND cppb.txn_category = 9
AND EXISTS (SELECT 1
FROM CST_PAC_ITEM_COST_DETAILS cpicd1
WHERE cppb.cost_layer_id = cpicd1.cost_layer_id
AND cppb.cost_element_id = cpicd1.cost_element_id
AND cppb.level_type = cpicd1.level_type);
, '<< CST_PERIODIC_ABSORPTION_PROC.Calc_Pmac_Update_Cppb'
);
END Calc_Pmac_Update_Cppb;
INSERT INTO MTL_PAC_ACTUAL_COST_DETAILS
( COST_GROUP_ID
, TRANSACTION_ID
, PAC_PERIOD_ID
, COST_TYPE_ID
, COST_ELEMENT_ID
, LEVEL_TYPE
, INVENTORY_ITEM_ID
, COST_LAYER_ID
, ACTUAL_COST
, VARIANCE_AMOUNT
, USER_ENTERED
, INSERTION_FLAG
, TRANSACTION_COSTED_DATE
, TXN_CATEGORY -- bug 7674673 fix
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, LAST_UPDATE_LOGIN
)
SELECT
mpacdt.cost_group_id
, mpacdt.transaction_id
, mpacdt.pac_period_id
, mpacdt.cost_type_id
, mpacdt.cost_element_id
, mpacdt.level_type
, mpacdt.inventory_item_id
, mpacdt.cost_layer_id
, mpacdt.actual_cost
, mpacdt.variance_amount
, mpacdt.user_entered
, mpacdt.insertion_flag
, mpacdt.transaction_costed_date
, DECODE(cpitt.txn_type,1,8,2,9) txn_category -- bug 7674673 fix
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, FND_GLOBAL.conc_request_id
, FND_GLOBAL.prog_appl_id
, FND_GLOBAL.conc_program_id
, SYSDATE
, FND_GLOBAL.login_id
FROM MTL_PAC_ACT_CST_DTL_TEMP mpacdt
,CST_PAC_INTERORG_TXNS_TMP cpitt
WHERE mpacdt.pac_period_id = p_pac_period_id
AND mpacdt.inventory_item_id = p_inventory_item_id
AND mpacdt.cost_group_id = nvl(p_cost_group_id, mpacdt.cost_group_id)
AND EXISTS (SELECT 'X'
FROM cst_pac_intorg_itms_temp cpiit
WHERE cpiit.pac_period_id = mpacdt.pac_period_id
AND cpiit.inventory_item_id = mpacdt.inventory_item_id
AND cpiit.cost_group_id = mpacdt.cost_group_id
AND cpiit.diverging_flag = 'N'
AND cpiit.interorg_receipt_flag = 'Y'
AND cpiit.interorg_shipment_flag = 'Y')
AND mpacdt.transaction_id = cpitt.transaction_id
AND mpacdt.pac_period_id = cpitt.pac_period_id
AND mpacdt.cost_group_id = cpitt.cost_group_id
AND mpacdt.inventory_item_id = cpitt.inventory_item_id;
PROCEDURE Update_Cpicd_With_New_Values(p_pac_period_id IN NUMBER
,p_inventory_item_id IN NUMBER
,p_cost_group_id IN NUMBER DEFAULT NULL
,p_cost_type_id IN NUMBER
,p_end_date IN DATE
)
IS
l_routine CONSTANT VARCHAR2(30) := 'update_cpicd_with_new_values';
SELECT
DISTINCT mpacd.cost_layer_id
, mpacd.cost_group_id
, mpacd.cost_element_id
, mpacd.level_type
FROM mtl_pac_actual_cost_details mpacd, cst_pac_intorg_itms_temp cpiit
WHERE cpiit.pac_period_id = c_pac_period_id
AND cpiit.inventory_item_id = c_inventory_item_id
AND cpiit.cost_group_id = nvl(c_cost_group_id, cpiit.cost_group_id)
AND cpiit.diverging_flag = 'N'
AND cpiit.interorg_receipt_flag = 'Y'
AND mpacd.pac_period_id = cpiit.pac_period_id
AND mpacd.inventory_item_id = cpiit.inventory_item_id
AND mpacd.cost_group_id = cpiit.cost_group_id
ORDER BY
mpacd.cost_layer_id
, mpacd.cost_element_id
, mpacd.level_type;
SELECT cost_group_id
FROM cst_pac_intorg_itms_temp
WHERE inventory_item_id = c_item_id
AND pac_period_id = c_pac_period_id
AND diverging_flag = 'N'
AND interorg_receipt_flag = 'Y';
UPDATE cst_pac_item_cost_details cpicd
SET last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
,request_id = FND_GLOBAL.conc_request_id
,program_application_id = FND_GLOBAL.prog_appl_id
,program_id = FND_GLOBAL.conc_program_id
,program_update_date = SYSDATE
,item_cost = l_item_cost
,item_balance = l_item_balance
WHERE cpicd.cost_layer_id = mpacd_idx.cost_layer_id
AND cpicd.cost_element_id = mpacd_idx.cost_element_id
AND cpicd.level_type = mpacd_idx.level_type;
, 'Update CPICD: ' || 'Item Cost:' || l_item_cost || ' Item Balance:' || l_item_balance
);
INSERT INTO CST_PAC_ITEM_COST_DETAILS cpicd
(cost_layer_id,
cost_element_id,
level_type,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
item_cost,
item_buy_cost,
item_make_cost,
item_balance,
make_balance,
buy_balance)
(SELECT mpacd_idx.cost_layer_id,
mpacd_idx.cost_element_id,
mpacd_idx.level_type,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id,
FND_GLOBAL.login_id,
FND_GLOBAL.conc_request_id,
FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_program_id,
sysdate,
l_item_cost,
0,
0,
l_item_balance,
0,
0
FROM dual
WHERE NOT EXISTS (SELECT 1
FROM cst_pac_item_cost_details cpicd1
WHERE cpicd1.cost_layer_id = mpacd_idx.cost_layer_id
AND cpicd1.cost_element_id = mpacd_idx.cost_element_id
AND cpicd1.level_type = mpacd_idx.level_type));
UPDATE cst_pac_item_costs cpic
SET (last_updated_by
,last_update_date
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,pl_material
,pl_material_overhead
,pl_resource
,pl_outside_processing
,pl_overhead
,tl_material
,tl_material_overhead
,tl_resource
,tl_outside_processing
,tl_overhead
,material_cost
,material_overhead_cost
,resource_cost
,outside_processing_cost
,overhead_cost
,pl_item_cost
,tl_item_cost
,item_cost
,item_buy_cost
,item_make_cost
,unburdened_cost
,burden_cost
) =
(SELECT
FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.login_id
, FND_GLOBAL.conc_request_id
, FND_GLOBAL.prog_appl_id
, FND_GLOBAL.conc_program_id
, SYSDATE
, SUM(DECODE(level_type,2,DECODE(cost_element_id,1,item_cost,0),0))
, SUM(DECODE(level_type,2,DECODE(cost_element_id,2,item_cost,0),0))
, SUM(DECODE(level_type,2,DECODE(cost_element_id,3,item_cost,0),0))
, SUM(DECODE(level_type,2,DECODE(cost_element_id,4,item_cost,0),0))
, SUM(DECODE(level_type,2,DECODE(cost_element_id,5,item_cost,0),0))
, SUM(DECODE(level_type,1,DECODE(cost_element_id,1,item_cost,0),0))
, SUM(DECODE(level_type,1,DECODE(cost_element_id,2,item_cost,0),0))
, SUM(DECODE(level_type,1,DECODE(cost_element_id,3,item_cost,0),0))
, SUM(DECODE(level_type,1,DECODE(cost_element_id,4,item_cost,0),0))
, SUM(DECODE(level_type,1,DECODE(cost_element_id,5,item_cost,0),0))
, SUM(DECODE(cost_element_id,1,item_cost,0))
, SUM(DECODE(cost_element_id,2,item_cost,0))
, SUM(DECODE(cost_element_id,3,item_cost,0))
, SUM(DECODE(cost_element_id,4,item_cost,0))
, SUM(DECODE(cost_element_id,5,item_cost,0))
, SUM(DECODE(level_type,2,item_cost,0))
, SUM(DECODE(level_type,1,item_cost,0))
, SUM(item_cost)
, SUM(item_buy_cost)
, SUM(item_make_cost)
, SUM(DECODE(cost_element_id,2,DECODE(level_type,2,item_cost,0),item_cost))
, SUM(DECODE(cost_element_id,2,DECODE(level_type,1,item_cost,0),0))
FROM cst_pac_item_cost_details cpicd
WHERE cpicd.cost_layer_id = mpacd_idx.cost_layer_id
GROUP BY cpicd.cost_layer_id)
WHERE cpic.cost_layer_id = mpacd_idx.cost_layer_id
AND EXISTS
(SELECT 'there is detail cost'
FROM cst_pac_item_cost_details cpicd
WHERE cpicd.cost_layer_id = mpacd_idx.cost_layer_id);
Calc_Pmac_Update_Cppb(p_pac_period_id => p_pac_period_id
,p_cost_type_id => p_cost_type_id
,p_cost_group_id => p_cost_group_id
,p_inventory_item_id => p_inventory_item_id
,p_end_date => p_end_date
,p_user_id => FND_GLOBAL.user_id
,p_login_id => FND_GLOBAL.login_id
,p_req_id => FND_GLOBAL.conc_request_id
,p_prg_id => FND_GLOBAL.conc_program_id
,p_prg_appid => FND_GLOBAL.prog_appl_id
);
Calc_Pmac_Update_Cppb(p_pac_period_id => p_pac_period_id
,p_cost_type_id => p_cost_type_id
,p_cost_group_id => cost_group_idx.cost_group_id
,p_inventory_item_id => p_inventory_item_id
,p_end_date => p_end_date
,p_user_id => FND_GLOBAL.user_id
,p_login_id => FND_GLOBAL.login_id
,p_req_id => FND_GLOBAL.conc_request_id
,p_prg_id => FND_GLOBAL.conc_program_id
,p_prg_appid => FND_GLOBAL.prog_appl_id
);
END; -- Update Cpicd With New Values
UPDATE CST_PAC_PROCESS_PHASES
SET process_status = p_phase_status
,process_date = SYSDATE
,process_upto_date = decode(p_phase_status,4,p_period_end_date,NULL)
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,request_id = FND_GLOBAL.conc_request_id
,program_application_id = FND_GLOBAL.prog_appl_id
,program_id = FND_GLOBAL.conc_program_id
,program_update_date = SYSDATE
,last_update_login = FND_GLOBAL.login_id
WHERE pac_period_id = p_period_id
AND cost_group_id = l_cost_group_id
AND process_phase = 5;
UPDATE CST_PAC_PROCESS_PHASES
SET process_status = p_phase_status
,process_date = SYSDATE
,process_upto_date = decode(p_phase_status,4,p_period_end_date,
5,p_period_end_date,
3,p_period_end_date,NULL)
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,request_id = FND_GLOBAL.conc_request_id
,program_application_id = FND_GLOBAL.prog_appl_id
,program_id = FND_GLOBAL.conc_program_id
,program_update_date = SYSDATE
,last_update_login = FND_GLOBAL.login_id
WHERE pac_period_id = p_period_id
AND cost_group_id = l_cost_group_id
AND process_phase = 7;
SELECT
cost_group_id
, prev_itr_item_cost
, item_cost
FROM cst_pac_intorg_itms_temp
WHERE inventory_item_id = c_item_id
AND pac_period_id = c_pac_period_id
AND interorg_receipt_flag = 'Y'
AND interorg_shipment_flag = 'Y'
AND DIVERGING_FLAG = 'N'
ORDER BY sequence_num;
SELECT
cost_group_id
, cost_group
, organization_id master_organization_id
FROM
cst_cost_groups ccg
WHERE legal_entity = c_legal_entity_id;
SELECT
cost_group_id
, organization_id
FROM
cst_cost_group_assignments ccga
WHERE check_cst_group(ccga.cost_group_id) = 'Y'
ORDER BY cost_group_id;
CST_PAC_ITERATION_PROCESS_PVT.G_CST_GROUP_TBL.delete;
CST_PAC_ITERATION_PROCESS_PVT.G_CST_GROUP_ORG_TBL.delete;
DELETE FROM CST_PAC_INTERORG_TXNS_TMP WHERE COST_GROUP_ID = p_cost_group_id AND PAC_PERIOD_ID = p_period_id;
INSERT INTO CST_PAC_INTERORG_TXNS_TMP
( transaction_id,
transaction_action_id,
transaction_source_type_id,
inventory_item_id,
primary_quantity,
periodic_primary_quantity,
organization_id,
transfer_organization_id,
subinventory_code,
transfer_price,
shipment_number,
transfer_transaction_id,
waybill_airbill,
transfer_cost,
transportation_cost,
transfer_percentage,
cost_group_id,
transfer_cost_group_id,
txn_type,
pac_period_id)
(SELECT
mmt.transaction_id transaction_id
, mmt.transaction_action_id transaction_action_id
, mmt.transaction_source_type_id transaction_source_type_id
, mmt.inventory_item_id inventory_item_id
, mmt.primary_quantity primary_quantity
, mmt.periodic_primary_quantity periodic_primary_quantity
, mmt.organization_id organization_id
, nvl(mmt.transfer_organization_id,-1) transfer_organization_id
, mmt.subinventory_code subinventory_code
, nvl(mmt.transfer_price,0) transfer_price
, mmt.shipment_number shipment_number
, mmt.transfer_transaction_id transfer_transaction_id
, mmt.waybill_airbill waybill_airbill
, nvl(mmt.transfer_cost,0) transfer_cost
, nvl(mmt.transportation_cost,0) transportation_cost
, nvl(mmt.transfer_percentage,0) transfer_percentage
, p_cost_group_id cost_group_id
, decode(c1.cost_group_id, p_cost_group_id, c2.cost_group_id, c1.cost_group_id) transfer_cost_group_id
, l_txn_type txn_type
, p_period_id pac_period_id
FROM
mtl_material_transactions mmt
, mtl_parameters mp
, cst_cost_group_assignments c1
, cst_cost_group_assignments c2
, cst_cost_groups ccg1
, cst_cost_groups ccg2
WHERE
mmt.transaction_date BETWEEN p_period_start_date AND p_period_end_date
AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
AND nvl(mmt.owning_tp_type,2) = 2
AND mmt.organization_id = mp.organization_id
AND nvl(mp.process_enabled_flag,'N') = 'N'
AND c1.organization_id = mmt.organization_id
AND c2.organization_id = mmt.transfer_organization_id
AND c1.cost_group_id <> c2.cost_group_id
AND c1.cost_group_id = ccg1.cost_group_id
AND c2.cost_group_id = ccg2.cost_group_id
AND ccg1.legal_entity = ccg2.legal_entity
AND (
(mmt.transaction_action_id = 3 AND mmt.transaction_source_type_id = 13
AND EXISTS ( SELECT 'X'
FROM cst_cost_group_assignments ccga1
WHERE ccga1.cost_group_id = p_cost_group_id
AND ccga1.organization_id = mmt.organization_id
AND mmt.primary_quantity > 0))
OR (mmt.transaction_action_id = 21 AND mmt.transaction_source_type_id = 13
AND EXISTS ( SELECT 'X'
FROM mtl_interorg_parameters mip,
cst_cost_group_assignments ccga2
WHERE mip.from_organization_id = mmt.organization_id
AND mip.to_organization_id = mmt.transfer_organization_id
AND nvl(mmt.fob_point,mip.fob_point) = 1
AND ccga2.organization_id = mip.to_organization_id
AND ccga2.cost_group_id = p_cost_group_id))
OR (mmt.transaction_action_id = 12 AND mmt.transaction_source_type_id = 13
AND EXISTS ( SELECT 'X'
FROM mtl_interorg_parameters mip,
cst_cost_group_assignments ccga3
WHERE mip.from_organization_id = mmt.transfer_organization_id
AND mip.to_organization_id = mmt.organization_id
AND nvl(mmt.fob_point,mip.fob_point) = 2
AND ccga3.organization_id = mip.to_organization_id
AND ccga3.cost_group_id = p_cost_group_id))
)
UNION
SELECT
mmt.transaction_id transaction_id
, mmt.transaction_action_id transaction_action_id
, mmt.transaction_source_type_id transaction_source_type_id
, mmt.inventory_item_id inventory_item_id
, mmt.primary_quantity primary_quantity
, mmt.periodic_primary_quantity periodic_primary_quantity
, mmt.organization_id organization_id
, nvl(mmt.transfer_organization_id,-1) transfer_organization_id
, mmt.subinventory_code subinventory_code
, nvl(mmt.transfer_price,0) transfer_price
, mmt.shipment_number shipment_number
, mmt.transfer_transaction_id transfer_transaction_id
, mmt.waybill_airbill waybill_airbill
, nvl(mmt.transfer_cost,0) transfer_cost
, nvl(mmt.transportation_cost,0) transportation_cost
, nvl(mmt.transfer_percentage,0) transfer_percentage
, p_cost_group_id cost_group_id
, decode(c1.cost_group_id, p_cost_group_id, c2.cost_group_id, c1.cost_group_id) transfer_cost_group_id
, l_txn_type txn_type
, p_period_id pac_period_id
FROM
mtl_material_transactions mmt
, mtl_parameters mp
, cst_cost_group_assignments c1
, cst_cost_group_assignments c2
, cst_cost_groups ccg1
, cst_cost_groups ccg2
WHERE
mmt.transaction_date BETWEEN p_period_start_date AND p_period_end_date
AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
AND nvl(mmt.owning_tp_type,2) = 2
AND mmt.organization_id = mp.organization_id
AND nvl(mp.process_enabled_flag,'N') = 'N'
AND c1.organization_id = mmt.organization_id
AND c2.organization_id = mmt.transfer_organization_id
AND c1.cost_group_id <> c2.cost_group_id
AND c1.cost_group_id = ccg1.cost_group_id
AND c2.cost_group_id = ccg2.cost_group_id
AND ccg1.legal_entity = ccg2.legal_entity
AND NOT EXISTS (SELECT 'X'
FROM mtl_intercompany_parameters mip
WHERE nvl(fnd_profile.value('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER'),0) = 1
AND mip.flow_type = 1
AND nvl(fnd_profile.value('CST_TRANSFER_PRICING_OPTION'),0) = 2
AND mip.ship_organization_id = (select to_number(hoi.org_information3)
from hr_organization_information hoi
where hoi.organization_id = decode(mmt.transaction_action_id,21,
mmt.organization_id,mmt.transfer_organization_id)
AND hoi.org_information_context = 'Accounting Information')
AND mip.sell_organization_id = (select to_number(hoi2.org_information3)
from hr_organization_information hoi2
where hoi2.organization_id = decode(mmt.transaction_action_id,21,
mmt.transfer_organization_id, mmt.organization_id)
AND hoi2.org_information_context = 'Accounting Information'))
AND (
(mmt.transaction_action_id = 3 AND transaction_source_type_id IN (7,8)
AND EXISTS ( SELECT 'X'
FROM cst_cost_group_assignments ccga1
WHERE ccga1.cost_group_id = p_cost_group_id
AND ccga1.organization_id = mmt.organization_id
AND mmt.primary_quantity > 0))
OR (mmt.transaction_action_id = 21 AND transaction_source_type_id IN (7,8)
AND EXISTS ( SELECT 'X'
FROM mtl_interorg_parameters mip,
cst_cost_group_assignments ccga2
WHERE mip.from_organization_id = mmt.organization_id
AND mip.to_organization_id = mmt.transfer_organization_id
AND nvl(mmt.fob_point,mip.fob_point) = 1
AND ccga2.organization_id = mip.to_organization_id
AND ccga2.cost_group_id = p_cost_group_id))
OR (mmt.transaction_action_id = 12 AND mmt.transaction_source_type_id IN (7,8)
AND EXISTS ( SELECT 'X'
FROM mtl_interorg_parameters mip,
cst_cost_group_assignments ccga3
WHERE mip.from_organization_id = mmt.transfer_organization_id
AND mip.to_organization_id = mmt.organization_id
AND nvl(mmt.fob_point,mip.fob_point) = 2
AND ccga3.organization_id = mip.to_organization_id
AND ccga3.cost_group_id = p_cost_group_id))
));
INSERT INTO CST_PAC_INTERORG_TXNS_TMP
( transaction_id,
transaction_action_id,
transaction_source_type_id,
inventory_item_id,
primary_quantity,
periodic_primary_quantity,
organization_id,
transfer_organization_id,
subinventory_code,
transfer_price,
shipment_number,
transfer_transaction_id,
waybill_airbill,
transfer_cost,
transportation_cost,
transfer_percentage,
cost_group_id,
transfer_cost_group_id,
txn_type,
pac_period_id)
(SELECT
mmt.transaction_id transaction_id
, mmt.transaction_action_id transaction_action_id
, mmt.transaction_source_type_id transaction_source_type_id
, mmt.inventory_item_id inventory_item_id
, mmt.primary_quantity primary_quantity
, mmt.periodic_primary_quantity periodic_primary_quantity
, mmt.organization_id organization_id
, nvl(mmt.transfer_organization_id,-1) transfer_organization_id
, mmt.subinventory_code subinventory_code
, nvl(mmt.transfer_price,0) transfer_price
, mmt.shipment_number shipment_number
, mmt.transfer_transaction_id transfer_transaction_id
, mmt.waybill_airbill waybill_airbill
, nvl(mmt.transfer_cost,0) transfer_cost
, nvl(mmt.transportation_cost,0) transportation_cost
, nvl(mmt.transfer_percentage,0) transfer_percentage
, p_cost_group_id cost_group_id
, NULL transfer_cost_group_id
, l_txn_type txn_type
, p_period_id pac_period_id
FROM
mtl_material_transactions mmt
, mtl_parameters mp
, mtl_parameters mptrans
WHERE
mmt.transaction_date BETWEEN p_period_start_date AND p_period_end_date
AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
AND nvl(mmt.owning_tp_type,2) = 2
AND mmt.organization_id = mp.organization_id
AND nvl(mp.process_enabled_flag, 'N') = 'N'
AND (transaction_action_id in (3,12,21) AND transaction_source_type_id = 13
AND EXISTS (SELECT 'EXISTS'
FROM cst_cost_group_assignments ccga
WHERE ccga.cost_group_id = p_cost_group_id
AND (ccga.organization_id = mmt.organization_id OR
ccga.organization_id = mmt.transfer_organization_id)))
AND mptrans.organization_id = mmt.transfer_organization_id
AND mptrans.process_enabled_flag = 'N'
AND (transaction_action_id IN (3,12,21)
AND NOT EXISTS (
SELECT 'X'
FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
WHERE c1.organization_id = mmt.organization_id
AND c2.organization_id = mmt.transfer_organization_id
AND c1.cost_group_id = c2.cost_group_id)
AND (
(mmt.transaction_action_id = 3
AND EXISTS (
SELECT 'X'
FROM cst_cost_group_assignments ccga1
WHERE ccga1.cost_group_id = p_cost_group_id
AND ccga1.organization_id = mmt.organization_id
AND mmt.primary_quantity < 0))
OR (mmt.transaction_action_id = 21
AND EXISTS (
SELECT 'X'
FROM cst_cost_group_assignments ccga2
WHERE ccga2.organization_id = mmt.organization_id
AND ccga2.cost_group_id = p_cost_group_id))
OR (mmt.transaction_action_id = 12
AND EXISTS (
SELECT 'X'
FROM mtl_interorg_parameters mip
WHERE mip.from_organization_id = mmt.transfer_organization_id
AND mip.to_organization_id = mmt.organization_id
AND (
(NVL(mmt.fob_point,mip.fob_point) = 1 AND EXISTS (
SELECT 'X'
FROM cst_cost_group_assignments ccga2
WHERE ccga2.organization_id = mip.to_organization_id
AND ccga2.cost_group_id = p_cost_group_id ))
OR (NVL(mmt.fob_point,mip.fob_point) = 2 AND EXISTS (
SELECT 'X'
FROM cst_cost_group_assignments ccga3
WHERE ccga3.organization_id = mip.from_organization_id
AND ccga3.cost_group_id = p_cost_group_id )))))
))
UNION
SELECT
mmt1.transaction_id transaction_id
, mmt1.transaction_action_id transaction_action_id
, mmt1.transaction_source_type_id transaction_source_type_id
, mmt1.inventory_item_id inventory_item_id
, mmt1.primary_quantity primary_quantity
, mmt1.periodic_primary_quantity periodic_primary_quantity
, mmt1.organization_id organization_id
, nvl(mmt1.transfer_organization_id,-1) transfer_organization_id
, mmt1.subinventory_code subinventory_code
, nvl(mmt1.transfer_price,0) transfer_price
, mmt1.shipment_number shipment_number
, mmt1.transfer_transaction_id transfer_transaction_id
, mmt1.waybill_airbill waybill_airbill
, nvl(mmt1.transfer_cost,0) transfer_cost
, nvl(mmt1.transportation_cost,0) transportation_cost
, nvl(mmt1.transfer_percentage,0) transfer_percentage
, p_cost_group_id cost_group_id
, NULL transfer_cost_group_id
, l_txn_type txn_type
, p_period_id pac_period_id
FROM
mtl_material_transactions mmt1
, mtl_parameters mp1
, mtl_parameters mptrans1
WHERE
mmt1.transaction_date BETWEEN p_period_start_date AND p_period_end_date
AND mmt1.organization_id = nvl(mmt1.owning_organization_id, mmt1.organization_id)
AND nvl(mmt1.owning_tp_type,2) = 2
AND mmt1.organization_id = mp1.organization_id
AND nvl(mp1.process_enabled_flag, 'N') = 'N'
AND (mmt1.transaction_action_id in (3,12,21) AND mmt1.transaction_source_type_id IN (7,8)
AND EXISTS (SELECT 'EXISTS'
FROM cst_cost_group_assignments ccga
WHERE ccga.cost_group_id = p_cost_group_id
AND (ccga.organization_id = mmt1.organization_id OR
ccga.organization_id = mmt1.transfer_organization_id)))
AND mptrans1.organization_id = mmt1.transfer_organization_id
AND mptrans1.process_enabled_flag = 'N'
AND NOT EXISTS (SELECT 'X'
FROM mtl_intercompany_parameters mip
WHERE nvl(fnd_profile.value('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER'),0) = 1
AND mip.flow_type = 1
AND nvl(fnd_profile.value('CST_TRANSFER_PRICING_OPTION'),0) = 2
AND mip.ship_organization_id = (select to_number(hoi.org_information3)
from hr_organization_information hoi
where hoi.organization_id = decode(mmt1.transaction_action_id,21,
mmt1.organization_id,mmt1.transfer_organization_id)
AND hoi.org_information_context = 'Accounting Information')
AND mip.sell_organization_id = (select to_number(hoi2.org_information3)
from hr_organization_information hoi2
where hoi2.organization_id = decode(mmt1.transaction_action_id,21,
mmt1.transfer_organization_id, mmt1.organization_id)
AND hoi2.org_information_context = 'Accounting Information'))
AND (mmt1.transaction_action_id IN (3,12,21) AND mmt1.transaction_source_type_id IN (7,8)
AND NOT EXISTS (
SELECT 'X'
FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
WHERE c1.organization_id = mmt1.organization_id
AND c2.organization_id = mmt1.transfer_organization_id
AND c1.cost_group_id = c2.cost_group_id)
AND (
(mmt1.transaction_action_id = 3
AND EXISTS (
SELECT 'X'
FROM cst_cost_group_assignments ccga1
WHERE ccga1.cost_group_id = p_cost_group_id
AND ccga1.organization_id = mmt1.organization_id
AND mmt1.primary_quantity < 0))
OR (mmt1.transaction_action_id = 21
AND EXISTS (
SELECT 'X'
FROM cst_cost_group_assignments ccga2
WHERE ccga2.organization_id = mmt1.organization_id
AND ccga2.cost_group_id = p_cost_group_id))
OR (mmt1.transaction_action_id = 12
AND EXISTS (
SELECT 'X'
FROM mtl_interorg_parameters mip
WHERE mip.from_organization_id = mmt1.transfer_organization_id
AND mip.to_organization_id = mmt1.organization_id
AND (
(NVL(mmt1.fob_point,mip.fob_point) = 1 AND EXISTS (
SELECT 'X'
FROM cst_cost_group_assignments ccga2
WHERE ccga2.organization_id = mip.to_organization_id
AND ccga2.cost_group_id = p_cost_group_id ))
OR (NVL(mmt1.fob_point,mip.fob_point) = 2 AND EXISTS (
SELECT 'X'
FROM cst_cost_group_assignments ccga3
WHERE ccga3.organization_id = mip.from_organization_id
AND ccga3.cost_group_id = p_cost_group_id )))))
)));
INSERT INTO CST_PAC_INTORG_ITMS_TEMP
( INVENTORY_ITEM_ID
, COST_GROUP_ID
, PAC_PERIOD_ID
, ITEM_COST
, PREV_ITR_ITEM_COST
, LOW_LEVEL_CODE
, TOLERANCE_FLAG
, ITERATION_COUNT
, DIFFERENCE
, DIVERGING_FLAG
, INTERORG_RECEIPT_FLAG
)
SELECT
distinct ccit.inventory_item_id
, p_cost_group_id
, p_period_id
, 0
, 0
, 1000
, 'N'
, 0
, 0
, 'N'
, 'Y'
FROM CST_PAC_INTERORG_TXNS_TMP ccit, mtl_parameters mp
WHERE ccit.cost_group_id = p_cost_group_id
AND ccit.pac_period_id = p_period_id
AND ccit.txn_type = 1
AND mp.organization_id = ccit.transfer_organization_id
AND mp.process_enabled_flag = 'N'
AND NOT EXISTS (
SELECT 'X'
FROM
cst_pac_low_level_codes cpllc
WHERE cpllc.inventory_item_id = ccit.inventory_item_id
AND cpllc.pac_period_id = p_period_id
AND cpllc.cost_group_id = p_cost_group_id);
INSERT INTO CST_PAC_INTORG_ITMS_TEMP
( INVENTORY_ITEM_ID
, COST_GROUP_ID
, PAC_PERIOD_ID
, ITEM_COST
, PREV_ITR_ITEM_COST
, LOW_LEVEL_CODE
, TOLERANCE_FLAG
, ITERATION_COUNT
, DIFFERENCE
, DIVERGING_FLAG
, INTERORG_RECEIPT_FLAG
, INTERORG_SHIPMENT_FLAG
, SEQUENCE_NUM
)
SELECT
distinct ccit.inventory_item_id
, p_cost_group_id
, p_period_id
, 0
, 0
, 1000
, 'N'
, 0
, 0
, 'N'
, 'N'
, 'Y'
, 1
FROM CST_PAC_INTERORG_TXNS_TMP ccit
WHERE ccit.cost_group_id = p_cost_group_id
AND ccit.pac_period_id = p_period_id
AND ccit.txn_type = 2
AND NOT EXISTS (
SELECT 'X'
FROM cst_pac_intorg_itms_temp cpiit
WHERE cpiit.cost_group_id = p_cost_group_id
AND cpiit.pac_period_id = p_period_id
AND cpiit.inventory_item_id = ccit.inventory_item_id)
AND NOT EXISTS (
SELECT 'X'
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.inventory_item_id = ccit.inventory_item_id
AND cpllc.pac_period_id = p_period_id
AND cpllc.cost_group_id = p_cost_group_id);
INSERT INTO CST_PAC_INTORG_ITMS_TEMP
( INVENTORY_ITEM_ID
, COST_GROUP_ID
, PAC_PERIOD_ID
, ITEM_COST
, PREV_ITR_ITEM_COST
, LOW_LEVEL_CODE
, TOLERANCE_FLAG
, ITERATION_COUNT
, DIFFERENCE
, DIVERGING_FLAG
, INTERORG_RECEIPT_FLAG
)
SELECT
distinct ccit.inventory_item_id
, p_cost_group_id
, p_period_id
, 0
, 0
, cpllc.low_level_code
, 'N'
, 0
, 0
, 'N'
, 'Y'
FROM CST_PAC_INTERORG_TXNS_TMP ccit, mtl_parameters mp, cst_pac_low_level_codes cpllc
WHERE ccit.cost_group_id = p_cost_group_id
AND ccit.pac_period_id = p_period_id
AND ccit.txn_type = 1
AND mp.organization_id = ccit.transfer_organization_id
AND mp.process_enabled_flag = 'N'
AND cpllc.inventory_item_id = ccit.inventory_item_id
AND cpllc.pac_period_id = p_period_id
AND cpllc.cost_group_id = p_cost_group_id;
INSERT INTO CST_PAC_INTORG_ITMS_TEMP
( INVENTORY_ITEM_ID
, COST_GROUP_ID
, PAC_PERIOD_ID
, ITEM_COST
, PREV_ITR_ITEM_COST
, LOW_LEVEL_CODE
, TOLERANCE_FLAG
, ITERATION_COUNT
, DIFFERENCE
, DIVERGING_FLAG
, INTERORG_RECEIPT_FLAG
, INTERORG_SHIPMENT_FLAG
, SEQUENCE_NUM
)
SELECT
distinct ccit.inventory_item_id
, p_cost_group_id
, p_period_id
, 0
, 0
, cpllc.low_level_code
, 'N'
, 0
, 0
, 'N'
, 'N'
, 'Y'
, 1
FROM CST_PAC_INTERORG_TXNS_TMP ccit, cst_pac_low_level_codes cpllc
WHERE ccit.cost_group_id = p_cost_group_id
AND ccit.pac_period_id = p_period_id
AND ccit.txn_type = 2
AND cpllc.inventory_item_id = ccit.inventory_item_id
AND cpllc.pac_period_id = p_period_id
AND cpllc.cost_group_id = p_cost_group_id
AND NOT EXISTS (
SELECT 'X'
FROM cst_pac_intorg_itms_temp cpiit
WHERE cpiit.cost_group_id = p_cost_group_id
AND cpiit.pac_period_id = p_period_id
AND cpiit.inventory_item_id = ccit.inventory_item_id);
UPDATE CST_PAC_INTORG_ITMS_TEMP cpiit
SET (INTERORG_SHIPMENT_FLAG,
SEQUENCE_NUM) = (select (case when exists(select 'X'
from CST_PAC_INTERORG_TXNS_TMP ccit
where ccit.pac_period_id = p_period_id
and ccit.cost_group_id = p_cost_group_id
and ccit.inventory_item_id = cpiit.inventory_item_id
and ccit.txn_type = 2)
then 'Y'
else 'N'
end) INTERORG_SHIPMENT_FLAG,
(case when exists(select 'X'
from CST_PAC_INTERORG_TXNS_TMP ccit
where ccit.pac_period_id = p_period_id
and ccit.cost_group_id = p_cost_group_id
and ccit.inventory_item_id = cpiit.inventory_item_id
and ccit.txn_type = 2)
then 2
else 3
end) SEQUENCE_NUM
from dual)
where cpiit.cost_group_id = p_cost_group_id
AND cpiit.pac_period_id = p_period_id
AND cpiit.INTERORG_RECEIPT_FLAG = 'Y';
update cst_pac_intorg_itms_temp cos1
set cos1.sequence_num =
(select sequence_num
from (select cos.inventory_item_id inventory_item_id,
cos.cost_group_id cost_group_id,
cos.pac_period_id pac_period_id,
row_number() over (partition by cos.inventory_item_id order by cos.sequence_num
,nvl(cpic.total_layer_quantity,0)
,cos.cost_group_id
) sequence_num
FROM cst_pac_item_costs cpic, cst_pac_intorg_itms_temp cos
WHERE cpic.inventory_item_id(+) = cos.inventory_item_id
AND cpic.cost_group_id(+) = cos.cost_group_id
AND cpic.pac_period_id(+) = cos.pac_period_id
AND cos.pac_period_id = p_period_id) Q
WHERE cos1.inventory_item_id = Q.inventory_item_id
and cos1.cost_group_id = Q.cost_group_id
and cos1.pac_period_id = Q.pac_period_id)
WHERE cos1.pac_period_id = p_period_id;
SELECT
nvl(cppb.period_balance,0)
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = c_pac_period_id
AND cppb.cost_group_id = c_cost_group_id
AND cppb.inventory_item_id = c_inventory_item_id
AND cppb.cost_element_id = c_cost_element_id
AND cppb.level_type = c_level_type
AND cppb.txn_category = (Select MAX(cppb1.txn_category)
FROM cst_pac_period_balances cppb1
WHERE cppb.pac_period_id = cppb1.pac_period_id
AND cppb.cost_group_id = cppb1.cost_group_id
AND cppb.inventory_item_id = cppb1.inventory_item_id
AND cppb.cost_element_id = cppb1.cost_element_id
AND cppb.level_type = cppb1.level_type
AND txn_category < 8);
SELECT
nvl(cppb.period_quantity,0)
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = c_pac_period_id
AND cppb.cost_group_id = c_cost_group_id
AND cppb.inventory_item_id = c_inventory_item_id
AND cppb.txn_category = (Select MAX(cppb1.txn_category)
FROM cst_pac_period_balances cppb1
WHERE cppb.pac_period_id = cppb1.pac_period_id
AND cppb.cost_group_id = cppb1.cost_group_id
AND cppb.inventory_item_id = cppb1.inventory_item_id
AND txn_category < 8)
AND rownum = 1;
SELECT
ccit.transaction_id transaction_id
, ccit.transaction_action_id transaction_action_id
, ccit.transaction_source_type_id transaction_source_type_id
, ccit.inventory_item_id inventory_item_id
, ccit.primary_quantity primary_quantity
, ccit.periodic_primary_quantity periodic_primary_quantity
, ccit.organization_id organization_id
, nvl(ccit.transfer_organization_id,-1) transfer_organization_id
, ccit.subinventory_code subinventory_code
, nvl(ccit.transfer_price,0) transfer_price
, ccit.shipment_number shipment_number
, ccit.transfer_transaction_id transfer_transaction_id
, ccit.waybill_airbill waybill_airbill
, nvl(ccit.transfer_cost,0) transfer_cost
, nvl(ccit.transportation_cost,0) transportation_cost
, nvl(ccit.transfer_percentage,0) transfer_percentage
, DECODE(msi.inventory_asset_flag,'Y',0,1) exp_item
, DECODE(msubinv.asset_inventory,1,0,1) exp_flag
FROM
CST_PAC_INTERORG_TXNS_TMP ccit
, mtl_system_items msi
, mtl_secondary_inventories msubinv
WHERE ccit.inventory_item_id = c_inventory_item_id
AND ccit.cost_group_id = c_cost_group_id
AND ccit.pac_period_id = c_period_id
AND ccit.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = ccit.organization_id
AND msubinv.organization_id(+) = ccit.organization_id
AND msubinv.secondary_inventory_name(+) = ccit.subinventory_code
AND ccit.txn_type = 1;
-- insert into cppb for 1000 inventory items
l_error_num := 0;
CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
,i_cost_group_id => p_cost_group_id
,i_txn_category => l_txn_category
,i_user_id => FND_GLOBAL.user_id
,i_login_id => FND_GLOBAL.login_id
,i_request_id => FND_GLOBAL.conc_request_id
,i_prog_id => FND_GLOBAL.conc_program_id
,i_prog_appl_id => FND_GLOBAL.prog_appl_id
,o_err_num => l_error_num
,o_err_code => l_error_code
,o_err_msg => l_error_msg
);
,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
);
-- Insert into MTL_PAC_ACT_CST_DTL_TEMP only if the iteration
-- process flag is enabled with more than 1 iteration
IF p_iteration_proc_flag = 'Y' AND p_end_iteration_num > 1 THEN
-- Cost owned transactions
INSERT INTO MTL_PAC_ACT_CST_DTL_TEMP
( COST_GROUP_ID
, TRANSACTION_ID
, PAC_PERIOD_ID
, COST_TYPE_ID
, COST_ELEMENT_ID
, LEVEL_TYPE
, INVENTORY_ITEM_ID
, COST_LAYER_ID
, ACTUAL_COST
, VARIANCE_AMOUNT
, USER_ENTERED
, INSERTION_FLAG
, TRANSACTION_COSTED_DATE
, SHIPMENT_NUMBER
, TRANSFER_TRANSACTION_ID
, TRANSPORTATION_COST
, MOH_ABSORPTION_COST
)
SELECT
cost_group_id
, transaction_id
, pac_period_id
, cost_type_id
, cost_element_id
, level_type
, inventory_item_id
, cost_layer_id
, actual_cost
, variance_amount
, user_entered
, insertion_flag
, transaction_costed_date
, l_group1_interorg_tab(i).shipment_number
, l_group1_interorg_tab(i).transfer_transaction_id
, decode(cost_element_id,2,
decode(level_type,1,l_group1_interorg_tab(i).transportation_cost,0),0)
, 0
FROM MTL_PAC_ACTUAL_COST_DETAILS
WHERE transaction_id = l_group1_interorg_tab(i).transaction_id
AND cost_group_id = p_cost_group_id
AND pac_period_id = p_period_id
AND cost_type_id = p_cost_type_id;
,'After inserting mtl_pac_act_cst_dtl_temp:'||
l_group1_interorg_tab(i).transaction_id
);
INSERT INTO MTL_PAC_ACT_CST_DTL_TEMP
( COST_GROUP_ID
, TRANSACTION_ID
, PAC_PERIOD_ID
, COST_TYPE_ID
, COST_ELEMENT_ID
, LEVEL_TYPE
, INVENTORY_ITEM_ID
, COST_LAYER_ID
, ACTUAL_COST
, VARIANCE_AMOUNT
, USER_ENTERED
, INSERTION_FLAG
, TRANSACTION_COSTED_DATE
, SHIPMENT_NUMBER
, TRANSFER_TRANSACTION_ID
, TRANSPORTATION_COST
, MOH_ABSORPTION_COST
)
SELECT
mpacd.cost_group_id
, mpacd.transaction_id
, mpacd.pac_period_id
, mpacd.cost_type_id
, cce.cost_element_id
, lt.level_type
, mpacd.inventory_item_id
, mpacd.cost_layer_id
, 0
, 0
, mpacd.user_entered
, mpacd.insertion_flag
, mpacd.transaction_costed_date
, l_group1_interorg_tab(i).shipment_number
, l_group1_interorg_tab(i).transfer_transaction_id
, 0
, 0
FROM (SELECT *
FROM MTL_PAC_ACTUAL_COST_DETAILS
WHERE transaction_id = l_group1_interorg_tab(i).transaction_id
AND cost_group_id = p_cost_group_id
AND pac_period_id = p_period_id
AND cost_type_id = p_cost_type_id
AND rownum = 1) mpacd,
CST_COST_ELEMENTS cce,
(SELECT 1 level_type FROM DUAL
UNION
SELECT 2 level_type FROM DUAL) lt
WHERE NOT EXISTS
(SELECT 1
FROM mtl_pac_act_cst_dtl_temp mpacdt
WHERE mpacdt.cost_group_id = p_cost_group_id
AND mpacdt.transaction_id = l_group1_interorg_tab(i).transaction_id
AND mpacdt.pac_period_id = p_period_id
AND mpacdt.cost_type_id = p_cost_type_id
AND mpacdt.cost_element_id = cce.cost_element_id
AND mpacdt.level_type = lt.level_type);
-- insert left over interorg receipts into cppb
-- Calculate Periodic Cost if interorg receipts exist
-- Update Variance Amount into MPACD_TEMP if interorg
-- receipts exist and consecutive iterations exist
-- update cppb if interorg receipts exist
-- ======================================================
l_error_num := 0;
CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
,i_cost_group_id => p_cost_group_id
,i_txn_category => l_txn_category
,i_user_id => FND_GLOBAL.user_id
,i_login_id => FND_GLOBAL.login_id
,i_request_id => FND_GLOBAL.conc_request_id
,i_prog_id => FND_GLOBAL.conc_program_id
,i_prog_appl_id => FND_GLOBAL.prog_appl_id
,o_err_num => l_error_num
,o_err_code => l_error_code
,o_err_msg => l_error_msg
);
,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
);
SELECT transaction_id, cost_element_id, level_type,variance_amount
BULK COLLECT
INTO l_txn_id_tbl, l_cost_element_id_tbl, l_level_type_tbl,l_variance_amt_tbl
FROM MTL_PAC_ACTUAL_COST_DETAILS
WHERE pac_period_id = p_period_id
AND cost_type_id = p_cost_type_id
AND cost_group_id = p_cost_group_id
AND inventory_item_id = p_inventory_item_id;
UPDATE MTL_PAC_ACT_CST_DTL_TEMP
SET variance_amount = l_variance_amt_tbl(l_mpacd_idx)
WHERE pac_period_id = p_period_id
AND cost_type_id = p_cost_type_id
AND cost_group_id = p_cost_group_id
AND inventory_item_id = p_inventory_item_id
AND cost_element_id = l_cost_element_id_tbl(l_mpacd_idx)
AND level_type = l_level_type_tbl(l_mpacd_idx)
AND transaction_id = l_txn_id_tbl(l_mpacd_idx);
FND_MESSAGE.set_token('MESSAGE', 'CSTPPWAC.update_item_cppb for CG '|| p_cost_group_id ||' txn category '
||l_txn_category||' t_low_level_code ' ||p_low_level_code
||' error('||l_error_code||') '||l_error_msg);
CSTPPWAC.update_item_cppb (i_pac_period_id => p_period_id,
i_cost_group_id => p_cost_group_id,
i_txn_category => l_txn_category,
i_item_id => p_inventory_item_id,
i_user_id => FND_GLOBAL.user_id,
i_login_id => FND_GLOBAL.login_id,
i_request_id => FND_GLOBAL.conc_request_id,
i_prog_id => FND_GLOBAL.conc_program_id,
i_prog_appl_id => FND_GLOBAL.prog_appl_id,
o_err_num => l_error_num,
o_err_code => l_error_code,
o_err_msg => l_error_msg );
UPDATE CST_PAC_INTORG_ITMS_TEMP
SET item_cost = (SELECT nvl(item_cost,0)
FROM cst_pac_item_costs cpic
WHERE cpic.pac_period_id = p_period_id
AND cpic.cost_group_id = p_cost_group_id
AND cpic.inventory_item_id = p_inventory_item_id)
WHERE pac_period_id = p_period_id
AND cost_group_id = p_cost_group_id
AND inventory_item_id = p_inventory_item_id;
SELECT
ccit.transaction_id transaction_id
, ccit.transaction_action_id transaction_action_id
, ccit.transaction_source_type_id transaction_source_type_id
, ccit.inventory_item_id inventory_item_id
, ccit.primary_quantity primary_quantity
, ccit.periodic_primary_quantity periodic_primary_quantity
, ccit.organization_id organization_id
, nvl(ccit.transfer_organization_id,-1) transfer_organization_id
, ccit.subinventory_code subinventory_code
, nvl(ccit.transfer_price,0) transfer_price
, ccit.shipment_number shipment_number
, ccit.transfer_transaction_id transfer_transaction_id
, nvl(ccit.transfer_cost,0) transfer_cost
, nvl(ccit.transportation_cost,0) transportation_cost
, nvl(ccit.transfer_percentage,0) transfer_percentage
, DECODE(msi.inventory_asset_flag,'Y',0,1) exp_item
, DECODE(msubinv.asset_inventory,1,0,1) exp_flag
FROM
CST_PAC_INTERORG_TXNS_TMP ccit
, mtl_system_items msi
, mtl_secondary_inventories msubinv
WHERE ccit.inventory_item_id = c_inventory_item_id
AND ccit.cost_group_id = c_cost_group_id
AND ccit.pac_period_id = c_period_id
AND ccit.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = ccit.organization_id
AND msubinv.organization_id(+) = ccit.organization_id
AND msubinv.secondary_inventory_name(+) = ccit.subinventory_code
AND ccit.txn_type = 2;
-- insert into cppb for 1000 inventory items
l_error_num := 0;
CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
,i_cost_group_id => p_cost_group_id
,i_txn_category => l_txn_category
,i_user_id => FND_GLOBAL.user_id
,i_login_id => FND_GLOBAL.login_id
,i_request_id => FND_GLOBAL.conc_request_id
,i_prog_id => FND_GLOBAL.conc_program_id
,i_prog_appl_id => FND_GLOBAL.prog_appl_id
,o_err_num => l_error_num
,o_err_code => l_error_code
,o_err_msg => l_error_msg
);
,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
);
-- Insert into MTL_PAC_ACT_CST_DTL_TEMP only if the iteration
-- process flag is enabled and consecutive iterations exist
IF p_iteration_proc_flag = 'Y' AND p_end_iteration_num > 1 THEN
-- Cost derived transactions
INSERT INTO MTL_PAC_ACT_CST_DTL_TEMP
( COST_GROUP_ID
, TRANSACTION_ID
, PAC_PERIOD_ID
, COST_TYPE_ID
, COST_ELEMENT_ID
, LEVEL_TYPE
, INVENTORY_ITEM_ID
, COST_LAYER_ID
, ACTUAL_COST
, USER_ENTERED
, INSERTION_FLAG
, TRANSACTION_COSTED_DATE
, SHIPMENT_NUMBER
, TRANSFER_TRANSACTION_ID
, TRANSPORTATION_COST
, MOH_ABSORPTION_COST
)
SELECT
cost_group_id
, transaction_id
, pac_period_id
, cost_type_id
, cost_element_id
, level_type
, inventory_item_id
, cost_layer_id
, actual_cost
, user_entered
, insertion_flag
, transaction_costed_date
, l_group2_interorg_tab(i).shipment_number
, l_group2_interorg_tab(i).transfer_transaction_id
, decode(cost_element_id,2,
decode(level_type,1,l_group2_interorg_tab(i).transportation_cost,0),0)
, 0
FROM MTL_PAC_ACTUAL_COST_DETAILS
WHERE transaction_id = l_group2_interorg_tab(i).transaction_id
AND cost_group_id = p_cost_group_id
AND pac_period_id = p_period_id
AND cost_type_id = p_cost_type_id;
,'After inserting mtl_pac_act_cst_dtl_temp:' ||
l_group2_interorg_tab(i).transaction_id
);
INSERT INTO MTL_PAC_ACT_CST_DTL_TEMP
( COST_GROUP_ID
, TRANSACTION_ID
, PAC_PERIOD_ID
, COST_TYPE_ID
, COST_ELEMENT_ID
, LEVEL_TYPE
, INVENTORY_ITEM_ID
, COST_LAYER_ID
, ACTUAL_COST
, USER_ENTERED
, INSERTION_FLAG
, TRANSACTION_COSTED_DATE
, SHIPMENT_NUMBER
, TRANSFER_TRANSACTION_ID
, TRANSPORTATION_COST
, MOH_ABSORPTION_COST
)
SELECT
mpacd.cost_group_id
, mpacd.transaction_id
, mpacd.pac_period_id
, mpacd.cost_type_id
, cce.cost_element_id
, lt.level_type
, mpacd.inventory_item_id
, mpacd.cost_layer_id
, 0
, mpacd.user_entered
, mpacd.insertion_flag
, mpacd.transaction_costed_date
, l_group2_interorg_tab(i).shipment_number
, l_group2_interorg_tab(i).transfer_transaction_id
, 0
, 0
FROM (SELECT *
FROM MTL_PAC_ACTUAL_COST_DETAILS
WHERE transaction_id = l_group2_interorg_tab(i).transaction_id
AND cost_group_id = p_cost_group_id
AND pac_period_id = p_period_id
AND cost_type_id = p_cost_type_id
AND rownum = 1) mpacd,
CST_COST_ELEMENTS cce,
(SELECT 1 level_type FROM DUAL
UNION
SELECT 2 level_type FROM DUAL) lt
WHERE NOT EXISTS
(SELECT 1
FROM mtl_pac_act_cst_dtl_temp mpacdt
WHERE mpacdt.cost_group_id = p_cost_group_id
AND mpacdt.transaction_id = l_group2_interorg_tab(i).transaction_id
AND mpacdt.pac_period_id = p_period_id
AND mpacdt.cost_type_id = p_cost_type_id
AND mpacdt.cost_element_id = cce.cost_element_id
AND mpacdt.level_type = lt.level_type);
CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
,i_cost_group_id => p_cost_group_id
,i_txn_category => l_txn_category
,i_user_id => FND_GLOBAL.user_id
,i_login_id => FND_GLOBAL.login_id
,i_request_id => FND_GLOBAL.conc_request_id
,i_prog_id => FND_GLOBAL.conc_program_id
,i_prog_appl_id => FND_GLOBAL.prog_appl_id
,o_err_num => l_error_num
,o_err_code => l_error_code
,o_err_msg => l_error_msg
);
,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
);
CSTPPWAC.update_item_cppb(i_pac_period_id => p_period_id
,i_cost_group_id => p_cost_group_id
,i_txn_category => l_txn_category
,i_item_id => p_inventory_item_id
,i_user_id => FND_GLOBAL.user_id
,i_login_id => FND_GLOBAL.login_id
,i_request_id => FND_GLOBAL.conc_request_id
,i_prog_id => FND_GLOBAL.conc_program_id
,i_prog_appl_id => FND_GLOBAL.prog_appl_id
,o_err_num => l_error_num
,o_err_code => l_error_code
,o_err_msg => l_error_msg
);
,'After calling update_item_cppb:'|| l_error_num || l_error_code || l_error_msg
);
, 'CSTPPWAC.update_item_cppb for CG '|| p_cost_group_id ||' txn category '
||l_txn_category||' t_low_level_code ' ||p_low_level_code
||' error('||l_error_code||') '||l_error_msg
);
FND_MESSAGE.set_token('MESSAGE', 'CSTPPWAC.update_item_cppb for CG '|| p_cost_group_id ||' txn category '
||l_txn_category||' t_low_level_code ' ||p_low_level_code
||' error('||l_error_code||') '||l_error_msg);
SELECT
ccit.transaction_id transaction_id
, ccit.transaction_action_id transaction_action_id
, ccit.transaction_source_type_id transaction_source_type_id
, ccit.inventory_item_id inventory_item_id
, ccit.primary_quantity primary_quantity
, ccit.periodic_primary_quantity periodic_primary_quantity
, ccit.organization_id organization_id
, nvl(ccit.transfer_organization_id,-1) transfer_organization_id
, ccit.subinventory_code subinventory_code
, nvl(ccit.transfer_price,0) transfer_price
, ccit.shipment_number shipment_number
, ccit.transfer_transaction_id transfer_transaction_id
, ccit.waybill_airbill waybill_airbill
, nvl(ccit.transfer_cost,0) transfer_cost
, nvl(ccit.transportation_cost,0) transportation_cost
, nvl(ccit.transfer_percentage,0) transfer_percentage
, DECODE(msi.inventory_asset_flag,'Y',0,1) exp_item
, DECODE(msubinv.asset_inventory,1,0,1) exp_flag
FROM
CST_PAC_INTERORG_TXNS_TMP ccit
, mtl_system_items msi
, mtl_secondary_inventories msubinv
WHERE ccit.inventory_item_id = c_inventory_item_id
AND ccit.cost_group_id = c_cost_group_id
AND ccit.pac_period_id = c_period_id
AND ccit.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = ccit.organization_id
AND msubinv.organization_id(+) = ccit.organization_id
AND msubinv.secondary_inventory_name(+) = ccit.subinventory_code
AND ccit.txn_type = 1;
SELECT
cost_layer_id
, prior_buy_cost
, prior_make_cost
, new_buy_cost
, new_make_cost
, cost_element_id
, level_type
, prior_cost
, actual_cost
, new_cost
, transfer_transaction_id
, transfer_cost
, transportation_cost
, moh_absorption_cost
, new_buy_quantity
FROM
mtl_pac_act_cst_dtl_temp
WHERE pac_period_id = c_period_id
AND cost_group_id = c_cost_group_id
AND transaction_id = c_transaction_id
AND inventory_item_id = c_inventory_item_id
ORDER BY
cost_element_id
, level_type
, transaction_id
FOR UPDATE;
SELECT
nvl(actual_cost,0) actual_cost
FROM
mtl_pac_act_cst_dtl_temp
WHERE pac_period_id = c_pac_period_id
AND cost_group_id = c_cost_group_id
AND inventory_item_id = c_inventory_item_id
AND transaction_id = c_transaction_id
AND cost_element_id = c_cost_element_id
AND level_type = c_level_type;
SELECT
nvl(item_cost,0),
nvl(difference,0)
FROM CST_PAC_INTORG_ITMS_TEMP
WHERE pac_period_id = c_pac_period_id
AND cost_group_id = c_cost_group_id
AND inventory_item_id = c_inventory_item_id;
-- Update Actual Cost with New Cost of corresponding transaction
-- For cost element 2 - MOH, actual_cost will be New Cost of corresponding
-- transaction cost + Unit Transfer Cost + Unit Transportation Cost
-- + moh_absorption_cost
-- For all other cost elements, actual_cost will be New Cost of
-- corresponding transaction
-- =======================================================================
UPDATE MTL_PAC_ACT_CST_DTL_TEMP
SET actual_cost = decode(cost_element_id, 2,
decode(level_type,1, (l_new_correspond_cost + l_unit_transfer_cost +
(transportation_cost/l_txn_quantity) + moh_absorption_cost), l_new_correspond_cost),l_new_correspond_cost)
, transfer_cost = DECODE(cost_element_id, 2, decode(level_type,1, l_unit_transfer_cost,0),0)
WHERE CURRENT OF pac_txn_cursor;
-- Calculate Variance Amount and update MPACD_TEMP
-- ===============================================
l_period_new_balance :=
G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).period_new_balance;
UPDATE mtl_pac_act_cst_dtl_temp
SET variance_amount = decode(sign(l_period_quantity),
0, l_period_new_balance,
(-1 * sign(l_period_new_balance)),
l_period_new_balance,0)
WHERE CURRENT OF pac_txn_cursor;
/* Update PAC tables for diverging cost group since further iterations will not take place
for the item in this cost group and if the item does not achieve tolerance in this run
and user chooses to Resume for Non Tolerance in the next run, the values in PL/SQL table
G_CG_PWAC_COST_TBL will be lost and update_cpicd_with_new_values will error out with no data found */
Create_Mpacd_With_New_Values(p_period_id
,p_inventory_item_id
,p_cost_group_id
);
Update_Cpicd_With_New_Values(p_pac_period_id => p_period_id
,p_inventory_item_id => p_inventory_item_id
,p_cost_group_id => p_cost_group_id
,p_cost_type_id => p_cost_type_id
,p_end_date => p_period_end_date
);
UPDATE CST_PAC_INTORG_ITMS_TEMP
SET DIVERGING_FLAG = 'Y',
TOLERANCE_FLAG = 'Y',
ITERATION_COUNT = p_iteration_num
WHERE pac_period_id = p_period_id
AND cost_group_id = p_cost_group_id
AND inventory_item_id = p_inventory_item_id;
G_PWAC_NEW_COST_TBL.delete;
UPDATE CST_PAC_INTORG_ITMS_TEMP
SET prev_itr_item_cost = l_prev_itr_item_cost
,item_cost = l_pwac_item_cost
,difference = l_new_difference
WHERE pac_period_id = p_period_id
AND cost_group_id = p_cost_group_id
AND inventory_item_id = p_inventory_item_id;
update mtl_pac_act_cst_dtl_temp mpacdt
set mpacdt.actual_cost = (select (CASE mpacdt.level_type
WHEN 1 THEN
(CASE mpacdt.cost_element_id
WHEN 1 THEN G_PWAC_NEW_COST_TBL(1)(1).final_new_cost
WHEN 2 THEN G_PWAC_NEW_COST_TBL(2)(1).final_new_cost
WHEN 3 THEN G_PWAC_NEW_COST_TBL(3)(1).final_new_cost
WHEN 4 THEN G_PWAC_NEW_COST_TBL(4)(1).final_new_cost
WHEN 5 THEN G_PWAC_NEW_COST_TBL(5)(1).final_new_cost
END)
WHEN 2 THEN
(CASE mpacdt.cost_element_id
WHEN 1 THEN G_PWAC_NEW_COST_TBL(1)(2).final_new_cost
WHEN 2 THEN G_PWAC_NEW_COST_TBL(2)(2).final_new_cost
WHEN 3 THEN G_PWAC_NEW_COST_TBL(3)(2).final_new_cost
WHEN 4 THEN G_PWAC_NEW_COST_TBL(4)(2).final_new_cost
WHEN 5 THEN G_PWAC_NEW_COST_TBL(5)(2).final_new_cost
END)
END) actual_cost
from dual)
where mpacdt.transaction_id in (select ccit.transaction_id
FROM CST_PAC_INTERORG_TXNS_TMP ccit
WHERE ccit.inventory_item_id = p_inventory_item_id
AND ccit.cost_group_id = p_cost_group_id
AND ccit.pac_period_id = p_period_id
AND ccit.txn_type = 2)
and mpacdt.pac_period_id = p_period_id
and mpacdt.cost_group_id = p_cost_group_id
and mpacdt.inventory_item_id = p_inventory_item_id;
G_PWAC_NEW_COST_TBL.delete;
SELECT
cost_group_id
, interorg_receipt_flag
, interorg_shipment_flag
, low_level_code
FROM cst_pac_intorg_itms_temp
WHERE inventory_item_id = c_item_id
AND pac_period_id = c_pac_period_id
AND diverging_flag = 'N'
ORDER BY sequence_num;
SELECT NVL(cppb1.period_balance,0) - NVL(cppb2.period_balance,0) pcu_value_balance
,cppb1.cost_group_id cost_group_id
,cppb1.cost_element_id cost_element_id
,cppb1.level_type level_type
FROM cst_pac_period_balances cppb1
,cst_pac_period_balances cppb2
where cppb1.pac_period_id = cppb2.pac_period_id
and cppb1.cost_group_id = cppb2.cost_group_id
and cppb1.inventory_item_id = cppb2.inventory_item_id
and cppb1.cost_element_id = cppb2.cost_element_id
and cppb1.level_type = cppb2.level_type
and cppb1.txn_category = 8.5
and cppb2.txn_category = 8
and cppb1.pac_period_id = c_pac_period_id
AND cppb1.cost_group_id = c_cost_group_id
AND cppb1.inventory_item_id = c_inventory_item_id;
SELECT count(*)
FROM CST_PAC_PERIOD_BALANCES
WHERE pac_period_id = c_pac_period_id
AND cost_group_id = c_cost_group_id
AND inventory_item_id = c_inventory_item_id
AND txn_category = 8.5;
SELECT low_level_code
FROM cst_pac_low_level_codes
WHERE pac_period_id = c_pac_period_id
AND cost_group_id = c_cost_group_id
AND inventory_item_id = c_inventory_item_id
AND rownum = 1;
-- Process Periodic Cost Update Value Change only for interorg item
-- both completion and no completion items are included
CST_PERIODIC_ABSORPTION_PROC.Periodic_Cost_Update_By_Level
(p_period_id => p_period_id
,p_legal_entity => p_legal_entity_id
,p_cost_type_id => p_cost_type_id
,p_cost_group_id => l_optimal_cg_idx.cost_group_id
,p_inventory_item_id => l_inventory_item_id
,p_cost_method => p_cost_method
,p_start_date => p_start_date
,p_end_date => p_end_date
,p_pac_rates_id => p_pac_rates_id
,p_master_org_id => l_master_org_id
,p_uom_control => p_uom_control
,p_low_level_code => t_low_level_code
,p_txn_category => 8.5
,p_user_id => p_user_id
,p_login_id => p_login_id
,p_req_id => p_req_id
,p_prg_id => p_prg_id
,p_prg_appid => p_prg_appid);
UPDATE CST_PAC_INTORG_ITMS_TEMP
SET item_cost = (SELECT nvl(item_cost,0)
FROM cst_pac_item_costs cpic
WHERE cpic.pac_period_id = p_period_id
AND cpic.cost_group_id = l_optimal_cg_idx.cost_group_id
AND cpic.inventory_item_id = l_inventory_item_id)
WHERE pac_period_id = p_period_id
AND cost_group_id = l_optimal_cg_idx.cost_group_id
AND inventory_item_id = l_inventory_item_id;
DELETE FROM mtl_pac_actual_cost_details mpacd
WHERE mpacd.pac_period_id = p_period_id
AND EXISTS (select 'X'
from cst_pac_intorg_itms_temp
where cost_group_id = mpacd.cost_group_id
and inventory_item_id = l_inventory_item_id
and pac_period_id = p_period_id
and interorg_receipt_flag = 'Y')
AND transaction_id IN (
SELECT transaction_id
FROM mtl_pac_act_cst_dtl_temp
WHERE pac_period_id = p_period_id
AND inventory_item_id = l_inventory_item_id);
UPDATE CST_PAC_INTORG_ITMS_TEMP
SET tolerance_flag = l_tolerance_flag
,iteration_count = l_iteration_num
WHERE inventory_item_id = l_inventory_item_id
AND pac_period_id = p_period_id
AND diverging_flag <> 'Y';
Update_Cpicd_With_New_Values(p_pac_period_id => p_period_id
,p_inventory_item_id => l_inventory_item_id
,p_cost_type_id => p_cost_type_id
,p_end_date => p_end_date
);
UPDATE CST_PAC_INTORG_ITMS_TEMP
SET tolerance_flag = 'F'
,iteration_count = l_end_iteration_num
WHERE pac_period_id = p_period_id
AND inventory_item_id = l_inventory_item_id
AND tolerance_flag = 'N';
Update_Cpicd_With_New_Values(p_pac_period_id => p_period_id
,p_inventory_item_id => l_inventory_item_id
,p_cost_type_id => p_cost_type_id
,p_end_date => p_end_date
);
UPDATE CST_PAC_INTORG_ITMS_TEMP
SET iteration_count = l_end_iteration_num
WHERE pac_period_id = p_period_id
AND tolerance_flag = 'N'
AND inventory_item_id = l_inventory_item_id
AND diverging_flag <> 'Y';
G_CG_PWAC_COST_TBL.delete;
G_PCU_VALUE_CHANGE_TBL.delete;