[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_no_update_qty NUMBER;
l_no_update_qty := 0;
insert_txn_history(i_pac_period_id, i_cost_group_id, i_txn_id,
i_process_group, i_item_id, i_qty_layer_id,
i_txn_qty, i_user_id, i_login_id, i_req_id,
i_prg_appl_id, i_prg_id, i_txn_category,
l_err_num, l_err_code, l_err_msg);
CSTPPWAC.periodic_cost_update(
i_pac_period_id,
i_cost_group_id,
i_cost_type_id,
i_txn_id,
i_cost_layer_id,
i_qty_layer_id,
i_item_id,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
i_txn_category,
i_txn_qty,/*LCM*/
l_err_num,
l_err_code,
l_err_msg);
CSTPFCHK.periodic_cost_update_hook(
i_pac_period_id,
i_cost_group_id,
i_cost_type_id,
i_txn_id,
i_cost_layer_id,
i_qty_layer_id,
i_item_id,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
i_txn_category,
i_txn_qty,/*LCM*/
l_err_num,
l_err_code,
l_err_msg);
update_txn_history(i_pac_period_id, i_cost_group_id, i_txn_id,
i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
l_err_num, l_err_code, l_err_msg);
update_txn_history(i_pac_period_id, i_cost_group_id, i_txn_id,
i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
l_err_num, l_err_code, l_err_msg);
l_no_update_qty,
i_cost_method,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
i_txn_category,
l_err_num,
l_err_code,
l_err_msg);
UPDATE cst_pac_item_costs cpic
SET last_update_date = sysdate,
last_updated_by = i_user_id,
last_update_login = i_login_id,
request_id = i_req_id,
program_application_id = i_prg_appl_id,
program_id = i_prg_id,
program_update_date = sysdate,
issue_quantity = issue_quantity + i_txn_qty,
buy_quantity = buy_quantity + l_buy_qty,
make_quantity = make_quantity + l_make_qty
WHERE cpic.cost_layer_id = i_cost_layer_id;
l_no_update_qty,
i_cost_method,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
i_txn_category,
l_err_num,
l_err_code,
l_err_msg);
update_txn_history(i_pac_period_id, i_cost_group_id, i_txn_id,
i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
l_err_num, l_err_code, l_err_msg);
I_NO_UPDATE_QTY IN NUMBER,
I_COST_METHOD IN NUMBER,
I_USER_ID IN NUMBER,
I_LOGIN_ID IN NUMBER,
I_REQ_ID IN NUMBER,
I_PRG_APPL_ID IN NUMBER,
I_PRG_ID IN NUMBER,
I_TXN_CATEGORY IN NUMBER,
O_Err_Num OUT NOCOPY NUMBER,
O_Err_Code OUT NOCOPY VARCHAR2,
O_Err_Msg OUT NOCOPY VARCHAR2
) IS
l_err_num NUMBER;
'I_NO_UPDATE_QTY = '||I_NO_UPDATE_QTY||','||
'I_COST_METHOD = '||I_COST_METHOD||','||
'I_USER_ID = '||I_USER_ID||','||
'I_LOGIN_ID = '||I_LOGIN_ID||','||
'I_REQ_ID = '||I_REQ_ID||','||
'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
'I_PRG_ID = '||I_PRG_ID||','||
'I_TXN_CATEGORY = '||I_TXN_CATEGORY
);
i_no_update_qty,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
i_txn_category,
i_txn_id,
i_item_id,
l_err_num,
l_err_code,
l_err_msg);
i_no_update_qty,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
l_err_num,
l_err_code,
l_err_msg);
I_NO_UPDATE_QTY IN NUMBER,
I_COST_METHOD IN NUMBER,
I_USER_ID IN NUMBER,
I_LOGIN_ID IN NUMBER,
I_REQ_ID IN NUMBER,
I_PRG_APPL_ID IN NUMBER,
I_PRG_ID IN NUMBER,
I_TXN_CATEGORY IN NUMBER,
O_Err_Num OUT NOCOPY NUMBER,
O_Err_Code OUT NOCOPY VARCHAR2,
O_Err_Msg OUT NOCOPY VARCHAR2
) IS
l_exp1 NUMBER;
'I_NO_UPDATE_QTY = '||I_NO_UPDATE_QTY||','||
'I_COST_METHOD = '||I_COST_METHOD||','||
'I_USER_ID = '||I_USER_ID||','||
'I_LOGIN_ID = '||I_LOGIN_ID||','||
'I_REQ_ID = '||I_REQ_ID||','||
'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
'I_PRG_ID = '||I_PRG_ID||','||
'I_TXN_CATEGORY = '||I_TXN_CATEGORY
);
select decode(asset_inventory,1,0,1)
into l_exp1
from mtl_secondary_inventories msi,
mtl_material_transactions mmt
where msi.secondary_inventory_name = mmt.subinventory_code
and msi.organization_id = i_org_id
and mmt.transaction_id = i_txn_id
and mmt.organization_id = i_org_id;
select decode(asset_inventory,1,0,1)
into l_exp2
from mtl_secondary_inventories msi,
mtl_material_transactions mmt
where msi.secondary_inventory_name = mmt.transfer_subinventory
and msi.organization_id = mmt.transfer_organization_id
and mmt.transaction_id = i_txn_id
and mmt.organization_id = i_org_id;
i_no_update_qty,
i_cost_method,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
i_txn_category,
l_err_num,
l_err_code,
l_err_msg);
l_no_update_qty NUMBER;
l_no_update_qty := 0;
select decode(asset_inventory,1,0,1)
into l_exp1
from mtl_secondary_inventories msi,
mtl_material_transactions mmt
where msi.secondary_inventory_name = mmt.subinventory_code
and msi.organization_id = i_org_id
and mmt.transaction_id = i_txn_id
and mmt.organization_id = i_org_id;
l_no_update_qty,
i_cost_method,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
i_txn_category,
l_err_num,
l_err_code,
l_err_msg);
select count(*)
into l_txn_cost_exist
from mtl_pac_txn_cost_details
where transaction_id = i_txn_id
and cost_group_id = i_cost_group_id
and pac_period_id = i_pac_period_id;
INSERT INTO mtl_pac_actual_cost_details (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
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,
inventory_item_id,
actual_cost,
wip_variance, -- New Column added for BOM based WIP reqmnt
insertion_flag,
user_entered,
transaction_costed_date,
txn_category)
SELECT
i_txn_id,
i_pac_period_id,
i_cost_type_id,
i_cost_group_id,
i_cost_layer_id,
mptcd.cost_element_id,
mptcd.level_type,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
mptcd.inventory_item_id,
mptcd.transaction_cost,
mptcd.wip_variance,
'Y',
'N',
SYSDATE,
i_txn_category
FROM mtl_pac_txn_cost_details mptcd
WHERE transaction_id = i_txn_id
AND pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id;
** we will insert a TL material 0 cost layer. **
********************************************************************/
l_stmt_num := 30;
select count(*)
into l_cost_details
from cst_pac_item_cost_details
where cost_layer_id = i_cost_layer_id;
SELECT decode(WE.entity_type,6,1,7,1,0)
INTO l_eam_job
FROM mtl_material_transactions MMT, WIP_ENTITIES WE
WHERE MMT.transaction_id = i_txn_id
AND MMT.transaction_source_id = WE.wip_entity_id;
INSERT INTO mtl_pac_actual_cost_details (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
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,
inventory_item_id,
actual_cost,
insertion_flag,
user_entered,
transaction_costed_date,
txn_category)
SELECT
i_txn_id,
i_pac_period_id,
i_cost_type_id,
i_cost_group_id,
i_cost_layer_id,
cpicd.cost_element_id,
cpicd.level_type,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
i_item_id,
decode(l_zero_cost_flag, 1, 0, cpicd.item_cost), /* changed for eAM support in PAC. Added decode to handle rebuilds */
'N',
'N',
SYSDATE,
i_txn_category
FROM cst_pac_item_cost_details cpicd
WHERE cpicd.cost_layer_id = i_cost_layer_id;
INSERT INTO mtl_pac_actual_cost_details (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
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,
inventory_item_id,
actual_cost,
insertion_flag,
user_entered,
transaction_costed_date,
txn_category)
VALUES(
i_txn_id,
i_pac_period_id,
i_cost_type_id,
i_cost_group_id,
i_cost_layer_id,
1,
1,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
i_item_id,
0,
'N',
'N',
SYSDATE,
i_txn_category);
select nvl(mmt.fob_point, mip.fob_point), mmt.transfer_organization_id
into l_fob_point, l_txfr_org_id
from mtl_interorg_parameters mip, mtl_material_transactions mmt
where mip.from_organization_id = i_org_id
and mip.to_organization_id = mmt.transfer_organization_id
and mmt.transaction_id = i_txn_id;
select count(*)
into l_mpacd_mat_ovhds
from mtl_pac_actual_cost_details mpacd
where transaction_id = i_txn_id
and cost_layer_id = i_cost_layer_id
and cost_element_id = 2
and level_type = decode(i_level, 1,1,level_type);
select nvl(sum(actual_cost),0)
into l_item_cost
from mtl_pac_actual_cost_details mpacd
where transaction_id = i_txn_id
and cost_layer_id = i_cost_layer_id;
INSERT INTO mtl_pac_cost_subelements(
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
cost_element_id,
level_type,
resource_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
actual_cost,
user_entered)
SELECT i_txn_id,
i_pac_period_id,
i_cost_type_id,
i_cost_group_id,
cicd.cost_element_id,
cicd.level_type,
cicd.resource_id,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
decode(cicd.basis_type, 1, cicd.usage_rate_or_amount,
2, cicd.usage_rate_or_amount/abs(i_txn_qty),
5, cicd.usage_rate_or_amount * l_item_cost,
6, cicd.usage_rate_or_amount * cicd.basis_factor,0),
'N'
FROM cst_item_cost_details cicd
WHERE inventory_item_id = i_item_id
AND organization_id = i_org_id
AND cost_type_id = i_pac_rates_id
AND basis_type in (1,2,5,6)
AND cost_element_id = 2
AND level_type = decode(i_level, 1,1,level_type);
select count(*)
into l_mpcs_mat_ovhds
from mtl_pac_cost_subelements
where transaction_id = i_txn_id
and pac_period_id = i_pac_period_id
and cost_group_id = i_cost_group_id
and cost_element_id = 2
and level_type = decode(i_level, 1,1,level_type);
UPDATE mtl_pac_actual_cost_details mpacd
SET (last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
actual_cost,
transaction_costed_date) =
(SELECT sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
sum(mpcs.actual_cost) + mpacd.actual_cost,
sysdate
FROM mtl_pac_cost_subelements mpcs
WHERE mpcs.transaction_id = i_txn_id
AND mpcs.pac_period_id = i_pac_period_id
AND mpcs.cost_group_id = i_cost_group_id
AND mpcs.cost_element_id = 2)
WHERE mpacd.transaction_id = i_txn_id
AND mpacd.cost_group_id = i_cost_group_id
AND mpacd.cost_layer_id = i_cost_layer_id
AND mpacd.cost_element_id = 2
AND mpacd.level_type = 1;
INSERT INTO mtl_pac_actual_cost_details(
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
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,
inventory_item_id,
actual_cost,
insertion_flag,
user_entered,
transaction_costed_date,
txn_category)
SELECT
i_txn_id,
i_pac_period_id,
i_cost_type_id,
i_cost_group_id,
i_cost_layer_id,
2,
1,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
i_item_id,
sum(actual_cost),
'Y',
'N',
SYSDATE,
i_txn_category
FROM mtl_pac_cost_subelements
WHERE transaction_id = i_txn_id
AND pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id
AND cost_element_id = 2;
I_NO_UPDATE_QTY IN NUMBER,
I_USER_ID IN NUMBER,
I_LOGIN_ID IN NUMBER,
I_REQ_ID IN NUMBER,
I_PRG_APPL_ID IN NUMBER,
I_PRG_ID IN NUMBER,
I_TXN_CATEGORY IN NUMBER,
I_TXN_ID IN NUMBER,
I_ITEM_ID IN NUMBER,
O_Err_Num OUT NOCOPY NUMBER,
O_Err_Code OUT NOCOPY VARCHAR2,
O_Err_Msg OUT NOCOPY VARCHAR2
) IS
l_err_num NUMBER;
'I_NO_UPDATE_QTY = '||I_NO_UPDATE_QTY||','||
'I_USER_ID = '||I_USER_ID||','||
'I_LOGIN_ID = '||I_LOGIN_ID||','||
'I_REQ_ID = '||I_REQ_ID||','||
'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
'I_PRG_ID = '||I_PRG_ID||','||
'I_TXN_CATEGORY = '||I_TXN_CATEGORY||','||
'I_TXN_ID = '||I_TXN_ID||','||
'I_ITEM_ID = '||I_ITEM_ID
);
if ((i_txn_action_id = 30) or (i_no_update_qty = 1) or (i_exp_flag = 1)) then
GOTO out_arg_log;
(SELECT actual_cost, cost_element_id, level_type
FROM mtl_pac_actual_cost_details mpacd
WHERE mpacd.cost_layer_id = i_cost_layer_id
AND mpacd.transaction_id = i_txn_id)
LOOP
DECLARE
l_index NUMBER;
** Update mtl_pac_actual_cost_details and update the prior cost **
** to the current average for the elements that exists and insert **
** in to mtl_pac_actual_cost_details the current average cost for **
** the elements that do not exist. **
********************************************************************/
l_stmt_num := 10;
INSERT INTO mtl_pac_actual_cost_details (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
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,
inventory_item_id,
actual_cost,
insertion_flag,
user_entered,
transaction_costed_date,
txn_category)
SELECT i_txn_id,
i_pac_period_id,
i_cost_type_id,
i_cost_group_id,
i_cost_layer_id,
cpicd.cost_element_id,
cpicd.level_type,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
i_item_id,
0,
'N',
'N',
sysdate,
i_txn_category
FROM cst_pac_item_cost_details cpicd
WHERE cost_layer_id = i_cost_layer_id
AND NOT EXISTS
(SELECT 'this detail is not in mpacd already'
FROM mtl_pac_actual_cost_details mpacd
WHERE mpacd.transaction_id = i_txn_id
AND mpacd.cost_group_id = i_cost_group_id
AND mpacd.cost_layer_id = i_cost_layer_id
AND mpacd.cost_element_id = cpicd.cost_element_id
AND mpacd.level_type = cpicd.level_type);
(SELECT actual_cost, cost_element_id, level_type
FROM mtl_pac_actual_cost_details mpacd
WHERE mpacd.cost_layer_id = i_cost_layer_id
AND mpacd.transaction_id = i_txn_id)
LOOP
DECLARE
l_index NUMBER;
Item-cost_element-level_type combination not found: Insert into
PL/SQL table. Quantity is maintained for each item, whereas all
other tables are maintained for each item-cost_element-level_type
**********************************************************************/
l_index := CSTPPINV.l_item_id_tbl.COUNT + 1;
Update/Insert total quantity, make quantity, buy quantity for each item
**************************************************************************/
CSTPPINV.l_item_balance_tbl(l_index) := (x.actual_cost * i_txn_qty) + CSTPPINV.l_item_balance_tbl(l_index);
PROCEDURE periodic_cost_update (
I_PAC_PERIOD_ID IN NUMBER,
I_COST_GROUP_ID IN NUMBER,
I_COST_TYPE_ID IN NUMBER,
I_TXN_ID IN NUMBER,
I_COST_LAYER_ID IN NUMBER,
I_QTY_LAYER_ID IN NUMBER,
I_ITEM_ID IN NUMBER,
I_USER_ID IN NUMBER,
I_LOGIN_ID IN NUMBER,
I_REQ_ID IN NUMBER,
I_PRG_APPL_ID IN NUMBER,
I_PRG_ID IN NUMBER,
I_TXN_CATEGORY IN NUMBER,
I_TXN_QTY IN NUMBER,
O_Err_Num OUT NOCOPY NUMBER,
O_Err_Code OUT NOCOPY VARCHAR2,
O_Err_Msg OUT NOCOPY VARCHAR2)
IS
l_value_change_flag NUMBER;
l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.periodic_cost_update';
'Entering CSTPPWAC.periodic_cost_update with '||
'I_PAC_PERIOD_ID = '||I_PAC_PERIOD_ID||','||
'I_COST_GROUP_ID = '||I_COST_GROUP_ID||','||
'I_COST_TYPE_ID = '||I_COST_TYPE_ID||','||
'I_TXN_ID = '||I_TXN_ID||','||
'I_COST_LAYER_ID = '||I_COST_LAYER_ID||','||
'I_QTY_LAYER_ID = '||I_QTY_LAYER_ID||','||
'I_ITEM_ID = '||I_ITEM_ID||','||
'I_USER_ID = '||I_USER_ID||','||
'I_LOGIN_ID = '||I_LOGIN_ID||','||
'I_REQ_ID = '||I_REQ_ID||','||
'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
'I_PRG_ID = '||I_PRG_ID||','||
'I_TXN_CATEGORY = '||I_TXN_CATEGORY
);
** Insert into mpacd, all the elemental cost : **
** - exists in cpicd, but not exists in mptcd **
** It will use the current cost in cpicd as the new cost **
********************************************************************/
l_stmt_num := 10;
INSERT INTO mtl_pac_actual_cost_details (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
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,
inventory_item_id,
actual_cost,
prior_cost,
prior_buy_cost,
prior_make_cost,
new_cost,
new_buy_cost,
new_make_cost,
variance_amount,
insertion_flag,
user_entered,
transaction_costed_date,
txn_category)
SELECT
i_txn_id,
i_pac_period_id,
i_cost_type_id,
i_cost_group_id,
i_cost_layer_id,
cpicd.cost_element_id,
cpicd.level_type,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
i_item_id,
decode (i_txn_category, 5, 0, nvl(cpicd.item_cost,0)), -- insert 0 for PCU value change
nvl(cpicd.item_cost,0),
nvl(cpicd.item_buy_cost,0),
nvl(cpicd.item_make_cost,0),
nvl(cpicd.item_cost,0),
nvl(cpicd.item_buy_cost,0),
nvl(cpicd.item_make_cost,0),
0, -- variance
'Y',
'N',
sysdate,
i_txn_category
FROM cst_pac_item_cost_details cpicd
WHERE cpicd.cost_layer_id = i_cost_layer_id
AND not exists (
SELECT 'not exists in mptcd'
FROM mtl_pac_txn_cost_details mptcd
WHERE mptcd.transaction_id = i_txn_id
AND mptcd.pac_period_id = i_pac_period_id
AND mptcd.cost_group_id = i_cost_group_id
AND mptcd.cost_element_id = cpicd.cost_element_id
AND mptcd.level_type = cpicd.level_type);
** Insert into mpacd, all the elemental cost : **
** - exists in mptcd and cpicd **
** - exists in mptcd but not exists in cpicd **
** New cost will be calculated based on current cost (if exists) **
** and cost change in mptcd. **
********************************************************************/
l_stmt_num := 20;
INSERT INTO mtl_pac_actual_cost_details (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
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,
inventory_item_id,
actual_cost,
prior_cost,
prior_buy_cost,
prior_make_cost,
new_cost,
new_buy_cost,
new_make_cost,
variance_amount,
insertion_flag,
user_entered,
transaction_costed_date,
txn_category,
onhand_variance_amount)
SELECT
i_txn_id,
i_pac_period_id,
i_cost_type_id,
i_cost_group_id,
i_cost_layer_id,
mptcd.cost_element_id,
mptcd.level_type,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
mptcd.inventory_item_id,
decode(mptcd.new_periodic_cost,NULL,
decode(mptcd.percentage_change,NULL,
/* value change formula */
decode(sign(cpql.layer_quantity),1,
decode(sign(i_txn_qty),1,
decode(sign(cpql.layer_quantity-i_txn_qty),-1,
(mptcd.value_change/i_txn_qty*cpql.layer_quantity),
nvl(mptcd.value_change,0)
),
nvl(mptcd.value_change,0)),
nvl(mptcd.value_change,0)),
/* percentage change formula */
nvl(cpicd.item_cost,0)*(1+mptcd.percentage_change/100)),
/* new average cost formula */
mptcd.new_periodic_cost),
decode (mptcd.value_change, NULL, nvl(cpicd.item_cost,0), NULL),
decode (mptcd.value_change, NULL, nvl(cpicd.item_buy_cost,0), NULL),
decode (mptcd.value_change, NULL, nvl(cpicd.item_make_cost,0), NULL),
decode(mptcd.new_periodic_cost,NULL,
decode(mptcd.percentage_change,NULL,
/* value change formula */
NULL, /* do not populate new_cost for value_change */
/* percentage change formula */
nvl(cpicd.item_cost,0)*(1+mptcd.percentage_change/100)),
/* new average cost formula */
mptcd.new_periodic_cost),
decode (mptcd.value_change, NULL, nvl(cpicd.item_buy_cost,0), NULL),
decode (mptcd.value_change, NULL, nvl(cpicd.item_make_cost,0), NULL),
NULL, /* variance */
'Y',
'N',
sysdate,
i_txn_category,
decode(mptcd.value_change,NULL,
0,
decode(sign(i_txn_qty),1,
decode(sign(cpql.layer_quantity),1,
decode(sign(cpql.layer_quantity-i_txn_qty),-1,
mptcd.value_change*(1-cpql.layer_quantity/i_txn_qty),
0
),
0
),
0
)
)
FROM mtl_pac_txn_cost_details mptcd,
cst_pac_item_cost_details cpicd,
cst_pac_quantity_layers cpql
WHERE mptcd.transaction_id = i_txn_id
AND mptcd.pac_period_id = i_pac_period_id
AND mptcd.cost_group_id = i_cost_group_id
AND cpql.cost_layer_id = i_cost_layer_id
AND cpql.quantity_layer_id = i_qty_layer_id
AND cpicd.cost_layer_id (+) = i_cost_layer_id
AND cpicd.cost_element_id (+) = mptcd.cost_element_id
AND cpicd.level_type (+) = mptcd.level_type;
If the transaction is not a value change cost update, set the value_change
flag to 1, otherwise set it to 0
*****************************************************************************/
l_stmt_num := 30;
SELECT DECODE(MAX(value_change),NULL, 1, 0)
INTO l_value_change_flag
FROM mtl_pac_txn_cost_details mptcd
WHERE mptcd.transaction_id = i_txn_id
AND mptcd.pac_period_id = i_pac_period_id
AND mptcd.cost_group_id = i_cost_group_id;
SELECT nvl(total_layer_quantity,0),
nvl(make_quantity,0),
nvl(buy_quantity,0)
INTO l_onhand,
l_make_qty,
l_buy_qty
FROM cst_pac_item_costs
WHERE cost_layer_id = i_cost_layer_id;
DELETE FROM cst_pac_item_cost_details
WHERE cost_layer_id = i_cost_layer_id;
INSERT INTO cst_pac_item_cost_details(
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,
buy_balance,
make_balance)
SELECT i_cost_layer_id,
mpacd.cost_element_id,
mpacd.level_type,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
mpacd.new_cost,
mpacd.new_buy_cost,
mpacd.new_make_cost,
mpacd.new_cost * l_onhand,
mpacd.new_buy_cost * l_buy_qty,
mpacd.new_make_cost * l_make_qty
FROM mtl_pac_actual_cost_details mpacd
WHERE mpacd.transaction_id = i_txn_id
AND mpacd.cost_group_id = i_cost_group_id
AND mpacd.cost_layer_id = i_cost_layer_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,
begin_item_cost,
item_buy_cost,
item_make_cost,
unburdened_cost,
burden_cost) =
(SELECT
i_user_id,
sysdate,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,1,ITEM_COST,0),0)), -- PL_MATERIAL
SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,2,ITEM_COST,0),0)), -- PL_MATERIAL_OVERHEAD
SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,3,ITEM_COST,0),0)), -- PL_RESOURCE
SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,4,ITEM_COST,0),0)), -- PL_OUTSIDE_PROCESSING
SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,5,ITEM_COST,0),0)), -- PL_OVERHEAD
SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,1,ITEM_COST,0),0)), -- TL_MATERIAL
SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,2,ITEM_COST,0),0)), -- TL_MATERIAL_OVERHEAD
SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,3,ITEM_COST,0),0)), -- TL_RESOURCE
SUM(DECODE(LEVEL_TYPE ,1,DECODE(COST_ELEMENT_ID ,4,ITEM_COST,0),0)), -- TL_OUTSIDE_PROCESSING
SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,5,ITEM_COST,0),0)), -- TL_OVERHEAD
SUM(DECODE(COST_ELEMENT_ID,1,ITEM_COST,0)), -- MATERIAL_COST
SUM(DECODE(COST_ELEMENT_ID,2,ITEM_COST,0)), -- MATERIAL_OVERHEAD_COST
SUM(DECODE(COST_ELEMENT_ID,3,ITEM_COST,0)), -- RESOURCE_COST
SUM(DECODE(COST_ELEMENT_ID,4,ITEM_COST,0)), -- OUTSIDE_PROCESSING_COST
SUM(DECODE(COST_ELEMENT_ID,5,ITEM_COST,0)), -- OVERHEAD_COST
SUM(DECODE(LEVEL_TYPE,2,ITEM_COST,0)), -- PL_ITEM_COST
SUM(DECODE(LEVEL_TYPE,1,ITEM_COST,0)), -- TL_ITEM_COST
SUM(ITEM_COST), -- ITEM_COST
DECODE(l_value_change_flag, 1, SUM(ITEM_COST), cpic.begin_item_cost),
SUM(ITEM_BUY_COST), -- ITEM_BUY_COST
SUM(ITEM_MAKE_COST), -- ITEM_MAKE_COST
SUM(DECODE(COST_ELEMENT_ID,2,DECODE(LEVEL_TYPE,2,ITEM_COST,0),ITEM_COST)),-- UNBURDENED_COST
SUM(DECODE(COST_ELEMENT_ID,2,DECODE(LEVEL_TYPE,1,ITEM_COST,0),0)) -- BURDEN_COST
FROM CST_PAC_ITEM_COST_DETAILS
WHERE COST_LAYER_ID = i_cost_layer_id
GROUP BY COST_LAYER_ID)
WHERE cpic.cost_layer_id = i_cost_layer_id
AND EXISTS
(SELECT 'there is detail cost'
FROM cst_pac_item_cost_details cpicd
WHERE cpicd.cost_layer_id = i_cost_layer_id);
USING (SELECT i_pac_period_id pac_period_id,
i_cost_group_id cost_group_id,
i_item_id item_id,
i_cost_layer_id cost_layer_id,
i_qty_layer_id qty_layer_id,
mpacd.cost_element_id cost_element_id,
mpacd.level_type level_type,
2 txn_category, -- txn category = 2 for PCU new cost and % change
0 category_quantity, -- quantity = 0 for cost update transactions
(l_onhand * (mpacd.actual_cost - mpacd.prior_cost)) category_balance,
(l_onhand * mpacd.actual_cost) period_balance,
l_onhand period_quantity,
mpacd.actual_cost
FROM mtl_pac_actual_cost_details mpacd
WHERE mpacd.cost_layer_id = i_cost_layer_id
AND mpacd.pac_period_id = i_pac_period_id
AND mpacd.cost_group_id = i_cost_group_id
AND mpacd.transaction_id = i_txn_id) mpacd
ON ( cppb.pac_period_id = mpacd.pac_period_id
AND cppb.cost_group_id = mpacd.cost_group_id
AND cppb.cost_layer_id = mpacd.cost_layer_id
AND cppb.cost_element_id = mpacd.cost_element_id
AND cppb.level_type = mpacd.level_type
AND cppb.txn_category = mpacd.txn_category)
WHEN NOT MATCHED THEN
INSERT (PAC_PERIOD_ID,
COST_GROUP_ID,
INVENTORY_ITEM_ID,
COST_LAYER_ID,
QUANTITY_LAYER_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TXN_CATEGORY,
TXN_CATEGORY_QTY,
TXN_CATEGORY_VALUE,
PERIOD_BALANCE,
PERIOD_QUANTITY,
PERIODIC_COST,
VARIANCE_AMOUNT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
VALUES (mpacd.pac_period_id,
mpacd.cost_group_id,
mpacd.item_id,
mpacd.cost_layer_id,
mpacd.qty_layer_id,
mpacd.cost_element_id,
mpacd.level_type,
mpacd.txn_category,
mpacd.category_quantity,
mpacd.category_balance,
mpacd.period_balance,
mpacd.period_quantity,
mpacd.actual_cost,
0,
sysdate,
i_user_id,
i_login_id,
i_user_id,
sysdate,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate)
WHEN MATCHED THEN
UPDATE SET
txn_category_qty = mpacd.category_quantity,
txn_category_value = txn_category_value + mpacd.category_balance,
period_quantity = mpacd.period_quantity,
period_balance = mpacd.period_balance,
periodic_cost = mpacd.actual_cost,
last_update_date = sysdate,
last_updated_by = i_user_id,
last_update_login = i_login_id,
request_id = i_req_id,
program_application_id = i_prg_appl_id,
program_id = i_prg_id,
program_update_date = sysdate;
(select actual_cost, cost_element_id, level_type
from mtl_pac_actual_cost_details mpacd
where mpacd.cost_layer_id = i_cost_layer_id
and mpacd.cost_group_id = i_cost_group_id
and mpacd.transaction_id = i_txn_id)
LOOP
DECLARE
l_index NUMBER;
IF (l_index = -1) THEN /* item-cost_element-level_type combination not found: Insert*/
l_index := CSTPPINV.l_item_id_tbl.COUNT + 1;
ELSE /* item-cost_element-level_type combination found: Update*/
CSTPPINV.l_item_balance_tbl(l_index) := x.actual_cost + CSTPPINV.l_item_balance_tbl(l_index);
* For a value change periodic update cost transaction,
* update the primary_quantity in mmt to the layer quantity from cpql.
* Prior to this, the quantity at the beginning of the period was being
* used and this caused errors in the distributions.
* The layer qty can be obtained from cst_pac_quantity_layers
*/
l_stmt_num := 120;
UPDATE mtl_material_transactions mmt
SET --primary_quantity = l_onhand,
/* Bug 2288994. Update periodic_primary_quantity also */
periodic_primary_quantity = l_onhand
WHERE mmt.value_change IS NOT NULL
AND mmt.transaction_id = i_txn_id;
fnd_file.put_line(fnd_file.log,'Updated MMT with primary_quantity: ' || to_char(l_onhand));
'Exiting CSTPPWAC.periodic_cost_update with '||
'O_Err_Num = '||O_Err_Num||','||
'O_Err_Code = '||O_Err_Code||','||
'O_Err_Msg = '||O_Err_Msg
);
END periodic_cost_update;
PROCEDURE insert_txn_history (
I_PAC_PERIOD_ID IN NUMBER,
I_COST_GROUP_ID IN NUMBER,
I_TXN_ID IN NUMBER,
I_PROCESS_GROUP IN NUMBER,
I_ITEM_ID IN NUMBER,
I_QTY_LAYER_ID IN NUMBER,
I_TXN_QTY IN NUMBER,
I_USER_ID IN NUMBER,
I_LOGIN_ID IN NUMBER,
I_REQ_ID IN NUMBER,
I_PRG_APPL_ID IN NUMBER,
I_PRG_ID IN NUMBER,
I_TXN_CATEGORY IN NUMBER,
O_Err_Num OUT NOCOPY NUMBER,
O_Err_Code OUT NOCOPY VARCHAR2,
O_Err_Msg OUT NOCOPY VARCHAR2
) IS
l_stmt_num NUMBER;
l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.insert_txn_history';
'Entering CSTPPWAC.insert_txn_history with '||
'I_PAC_PERIOD_ID = '||I_PAC_PERIOD_ID||','||
'I_COST_GROUP_ID = '||I_COST_GROUP_ID||','||
'I_TXN_ID = '||I_TXN_ID||','||
'I_PROCESS_GROUP = '||I_PROCESS_GROUP||','||
'I_ITEM_ID = '||I_ITEM_ID||','||
'I_QTY_LAYER_ID = '||I_QTY_LAYER_ID||','||
'I_TXN_QTY = '||I_TXN_QTY||','||
'I_USER_ID = '||I_USER_ID||','||
'I_LOGIN_ID = '||I_LOGIN_ID||','||
'I_REQ_ID = '||I_REQ_ID||','||
'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
'I_PRG_ID = '||I_PRG_ID||','||
'I_TXN_CATEGORY = '||I_TXN_CATEGORY
);
INSERT INTO cst_pc_txn_history (
pac_period_id,
cost_group_id,
transaction_id,
process_seq,
process_group,
inventory_item_id,
txn_master_qty,
prior_costed_master_qty,
txn_category,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
(SELECT
i_pac_period_id,
i_cost_group_id,
i_txn_id,
cst_pc_txn_history_s.nextval,
i_process_group,
i_item_id,
i_txn_qty,
layer_quantity,
i_txn_category,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
SYSDATE,
i_login_id
FROM
cst_pac_quantity_layers
WHERE quantity_layer_id = i_qty_layer_id);
'Exiting CSTPPWAC.insert_txn_history with '||
'O_Err_Num = '||O_Err_Num||','||
'O_Err_Code = '||O_Err_Code||','||
'O_Err_Msg = '||O_Err_Msg
);
END insert_txn_history;
PROCEDURE update_txn_history (
I_PAC_PERIOD_ID IN NUMBER,
I_COST_GROUP_ID IN NUMBER,
I_TXN_ID IN NUMBER,
I_USER_ID IN NUMBER,
I_LOGIN_ID IN NUMBER,
I_REQ_ID IN NUMBER,
I_PRG_APPL_ID IN NUMBER,
I_PRG_ID IN NUMBER,
O_Err_Num OUT NOCOPY NUMBER,
O_Err_Code OUT NOCOPY VARCHAR2,
O_Err_Msg OUT NOCOPY VARCHAR2
) IS
l_stmt_num NUMBER;
l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.update_txn_history';
'Entering CSTPPWAC.update_txn_history with '||
'I_PAC_PERIOD_ID = '||I_PAC_PERIOD_ID||','||
'I_COST_GROUP_ID = '||I_COST_GROUP_ID||','||
'I_TXN_ID = '||I_TXN_ID||','||
'I_USER_ID = '||I_USER_ID||','||
'I_LOGIN_ID = '||I_LOGIN_ID||','||
'I_REQ_ID = '||I_REQ_ID||','||
'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
'I_PRG_ID = '||I_PRG_ID
);
UPDATE cst_pc_txn_history
SET( actual_cost,
new_cost,
prior_cost )=
(SELECT
sum(actual_cost),
sum(new_cost),
sum(prior_cost)
FROM
mtl_pac_actual_cost_details
WHERE pac_period_id = i_pac_period_id
and cost_group_id = i_cost_group_id
and transaction_id = i_txn_id)
WHERE pac_period_id = i_pac_period_id
and cost_group_id = i_cost_group_id
and transaction_id = i_txn_id;
'Exiting CSTPPWAC.update_txn_history with '||
'O_Err_Num = '||O_Err_Num||','||
'O_Err_Code = '||O_Err_Code||','||
'O_Err_Msg = '||O_Err_Msg
);
END update_txn_history;
PROCEDURE insert_into_cppb(i_pac_period_id IN NUMBER,
i_cost_group_id IN NUMBER,
i_txn_category IN NUMBER,
i_user_id IN NUMBER,
i_login_id IN NUMBER,
i_request_id IN NUMBER,
i_prog_id IN NUMBER,
i_prog_appl_id IN NUMBER,
o_err_num OUT NOCOPY NUMBER,
o_err_code OUT NOCOPY VARCHAR2,
o_err_msg OUT NOCOPY VARCHAR2)
IS
l_stmt_num NUMBER;
l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.insert_into_cppb';
'Entering CSTPPWAC.insert_into_cppb with '||
'i_cost_group_id = '||i_cost_group_id||','||
'i_txn_category = '||i_txn_category||','||
'i_user_id = '||i_user_id||','||
'i_login_id = '||i_login_id||','||
'i_request_id = '||i_request_id||','||
'i_prog_id = '||i_prog_id||','||
'i_prog_appl_id = '||i_prog_appl_id
);
SELECT cpic.total_layer_quantity,
cpic.buy_quantity,
cpic.make_quantity,
cpic.issue_quantity,
0 /* category_qty = 0 for cost updates */
INTO l_new_qty_tbl (l_index),
l_new_buy_qty_tbl (l_index),
l_new_make_qty_tbl (l_index),
l_new_issue_qty_tbl (l_index),
l_category_qty_tbl (l_index)
FROM cst_pac_item_costs cpic
WHERE cpic.cost_layer_id = CSTPPINV.l_cost_layer_id_tbl (l_index);
SELECT cpic.total_layer_quantity + CSTPPINV.l_item_quantity_tbl (CSTPPINV.l_item_id_tbl(l_index)),
cpic.buy_quantity + CSTPPINV.l_buy_quantity_tbl (CSTPPINV.l_item_id_tbl(l_index)),
cpic.make_quantity + CSTPPINV.l_make_quantity_tbl (CSTPPINV.l_item_id_tbl(l_index)),
cpic.issue_quantity + CSTPPINV.l_issue_quantity_tbl (CSTPPINV.l_item_id_tbl(l_index)),
CSTPPINV.l_item_quantity_tbl (CSTPPINV.l_item_id_tbl(l_index))
INTO l_new_qty_tbl (l_index),
l_new_buy_qty_tbl (l_index),
l_new_make_qty_tbl (l_index),
l_new_issue_qty_tbl (l_index),
l_category_qty_tbl (l_index)
FROM cst_pac_item_costs cpic
WHERE cpic.cost_layer_id = CSTPPINV.l_cost_layer_id_tbl (l_index);
USING (SELECT i_pac_period_id pac_period_id,
i_cost_group_id cost_group_id,
CSTPPINV.l_item_id_tbl(l_index) item_id,
CSTPPINV.l_cost_layer_id_tbl(l_index) cost_layer_id,
CSTPPINV.l_qty_layer_id_tbl(l_index) qty_layer_id,
CSTPPINV.l_cost_element_id_tbl(l_index) cost_element_id,
CSTPPINV.l_level_type_tbl(l_index) level_type,
CSTPPINV.l_txn_category_tbl(l_index) txn_category,
l_category_qty_tbl (l_index) category_quantity,
CSTPPINV.l_item_balance_tbl(l_index) category_balance
FROM dual) temp
ON ( cppb.pac_period_id = temp.pac_period_id
AND cppb.cost_layer_id = temp.cost_layer_id
AND cppb.cost_element_id = temp.cost_element_id
AND cppb.level_type = temp.level_type
AND cppb.txn_category = temp.txn_category)
WHEN NOT MATCHED THEN
INSERT (pac_period_id,
cost_group_id,
inventory_item_id,
cost_layer_id,
quantity_layer_id,
cost_element_id,
level_type,
txn_category,
txn_category_qty,
txn_category_value,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES (temp.pac_period_id,
temp.cost_group_id,
temp.item_id,
temp.cost_layer_id,
temp.qty_layer_id,
temp.cost_element_id,
temp.level_type,
temp.txn_category,
temp.category_quantity,
temp.category_balance,
sysdate,
i_user_id,
i_login_id,
i_user_id,
sysdate,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate)
WHEN MATCHED THEN
UPDATE SET
txn_category_qty = txn_category_qty + temp.category_quantity,
txn_category_value = txn_category_value + temp.category_balance,
last_update_date = sysdate,
last_updated_by = i_user_id,
last_update_login = i_login_id,
request_id = i_request_id,
program_application_id = i_prog_appl_id,
program_id = i_prog_id,
program_update_date = sysdate;
UPDATE CST_PAC_ITEM_COST_DETAILS cpicd
SET item_balance = nvl(item_balance, 0) + CSTPPINV.l_item_balance_tbl (l_index),
make_balance = nvl(make_balance, 0) + CSTPPINV.l_make_balance_tbl (l_index),
buy_balance = nvl(buy_balance, 0) + CSTPPINV.l_buy_balance_tbl (l_index),
last_update_date = sysdate,
last_updated_by = i_user_id,
last_update_login = i_login_id,
request_id = i_request_id,
program_application_id = i_prog_appl_id,
program_id = i_prog_id,
program_update_date = sysdate
WHERE cpicd.cost_layer_id = CSTPPINV.l_cost_layer_id_tbl (l_index)
AND cpicd.cost_element_id = CSTPPINV.l_cost_element_id_tbl (l_index)
AND cpicd.level_type = CSTPPINV.l_level_type_tbl (l_index);
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 CSTPPINV.l_cost_layer_id_tbl (l_index),
CSTPPINV.l_cost_element_id_tbl (l_index),
CSTPPINV.l_level_type_tbl (l_index),
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
0,
0,
0,
CSTPPINV.l_item_balance_tbl (l_index),
CSTPPINV.l_make_balance_tbl (l_index),
CSTPPINV.l_buy_balance_tbl (l_index)
FROM dual
WHERE NOT EXISTS (SELECT 1
FROM cst_pac_item_cost_details cpicd1
WHERE cpicd1.cost_layer_id = CSTPPINV.l_cost_layer_id_tbl (l_index)
AND cpicd1.cost_element_id = CSTPPINV.l_cost_element_id_tbl (l_index)
AND cpicd1.level_type = CSTPPINV.l_level_type_tbl (l_index)));
/* update quantities and balance in CPIC */
FORALL l_index IN CSTPPINV.l_item_id_tbl.FIRST..CSTPPINV.l_item_id_tbl.LAST
UPDATE cst_pac_item_costs cpic
SET total_layer_quantity = l_new_qty_tbl (l_index),
issue_quantity = l_new_issue_qty_tbl (l_index),
buy_quantity = l_new_buy_qty_tbl(l_index),
make_quantity = l_new_make_qty_tbl (l_index),
last_update_date = sysdate,
last_updated_by = i_user_id,
request_id = i_request_id,
program_application_id = i_prog_appl_id,
program_id = i_prog_id,
program_update_date = sysdate,
last_update_login = i_login_id
WHERE cpic.cost_layer_id = CSTPPINV.l_cost_layer_id_tbl (l_index);
/* Update CPQL quantity */
FORALL l_index IN CSTPPINV.l_item_id_tbl.FIRST..CSTPPINV.l_item_id_tbl.LAST
UPDATE CST_PAC_QUANTITY_LAYERS cpql
SET (last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
layer_quantity) =
(SELECT i_user_id,
sysdate,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
l_new_qty_tbl (l_index)
FROM sys.dual)
WHERE cpql.quantity_layer_id = CSTPPINV.l_qty_layer_id_tbl (l_index)
AND EXISTS
(SELECT 'there is a layer'
FROM cst_pac_quantity_layers cpql
WHERE cpql.quantity_layer_id = CSTPPINV.l_qty_layer_id_tbl (l_index));
CSTPPINV.l_item_id_tbl.DELETE;
CSTPPINV.l_cost_layer_id_tbl.DELETE;
CSTPPINV.l_qty_layer_id_tbl.DELETE;
CSTPPINV.l_cost_element_id_tbl.DELETE;
CSTPPINV.l_level_type_tbl.DELETE;
CSTPPINV.l_txn_category_tbl.DELETE;
CSTPPINV.l_item_balance_tbl.DELETE;
CSTPPINV.l_make_balance_tbl.DELETE;
CSTPPINV.l_buy_balance_tbl.DELETE;
CSTPPINV.l_item_quantity_tbl.DELETE;
CSTPPINV.l_make_quantity_tbl.DELETE;
CSTPPINV.l_buy_quantity_tbl.DELETE;
CSTPPINV.l_issue_quantity_tbl.DELETE;
CSTPPINV.l_item_start_index_tbl.DELETE;
CSTPPINV.l_item_end_index_tbl.DELETE;
'Exiting CSTPPWAC.insert_into_cppb with '||
'o_err_num = '||o_err_num||','||
'o_err_code = '||o_err_code||','||
'o_err_msg = '||o_err_msg
);
END insert_into_cppb;
SELECT distinct cost_layer_id, mpacd.transaction_id, mpacd.txn_category
BULK COLLECT
INTO l_cost_layer_id_tbl, l_last_txn_id_tbl, l_txn_category_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 = (SELECT max(txn_category)
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = i_pac_period_id
AND cppb.cost_group_id = i_cost_group_id
AND cppb.cost_layer_id = mpacd.cost_layer_id)
AND mpacd1.inventory_item_id = mpacd.inventory_item_id
AND mpacd1.pac_period_id = i_pac_period_id
AND mpacd1.cost_group_id = i_cost_group_id)
AND mpacd.cost_group_id = i_cost_group_id
AND mpacd.pac_period_id = i_pac_period_id
AND mpacd.inventory_item_id = i_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 = i_pac_period_id
AND cpllc.cost_group_id = i_cost_group_id);
SELECT max(txn_category)
INTO l_max_txn_category
FROM mtl_pac_actual_cost_details mpacd
WHERE mpacd.pac_period_id = i_pac_period_id
AND mpacd.cost_group_id = i_cost_group_id
AND mpacd.inventory_item_id = i_item_id
AND EXISTS (SELECT 1
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.cost_group_id = i_cost_group_id
AND cpllc.pac_period_id = i_pac_period_id
AND cpllc.inventory_item_id = mpacd.inventory_item_id
AND cpllc.low_level_code = i_low_level_code);
SELECT distinct cost_layer_id, mpacd.transaction_id, mpacd.txn_category
BULK COLLECT
INTO l_cost_layer_id_tbl, l_last_txn_id_tbl, l_txn_category_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 = (SELECT max(txn_category)
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = i_pac_period_id
AND cppb.cost_group_id = i_cost_group_id
AND cppb.cost_layer_id = mpacd.cost_layer_id)
AND mpacd1.txn_category = l_max_txn_category
AND mpacd1.inventory_item_id = mpacd.inventory_item_id
AND mpacd1.pac_period_id = i_pac_period_id
AND mpacd1.cost_group_id = i_cost_group_id)
AND mpacd.cost_group_id = i_cost_group_id
AND mpacd.pac_period_id = i_pac_period_id
AND mpacd.inventory_item_id = i_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 = i_low_level_code
AND cpllc.pac_period_id = i_pac_period_id
AND cpllc.cost_group_id = i_cost_group_id);
SELECT distinct cost_layer_id, mpacd.transaction_id, mpacd.txn_category
BULK COLLECT
INTO l_cost_layer_id_tbl, l_last_txn_id_tbl, l_txn_category_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 = (SELECT max(txn_category)
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = i_pac_period_id
AND cppb.cost_group_id = i_cost_group_id
AND cppb.cost_layer_id = mpacd.cost_layer_id)
AND mpacd1.inventory_item_id = mpacd.inventory_item_id
AND mpacd1.pac_period_id = i_pac_period_id
AND mpacd1.cost_group_id = i_cost_group_id)
AND mpacd.cost_group_id = i_cost_group_id
AND mpacd.pac_period_id = i_pac_period_id
AND mpacd.inventory_item_id = i_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 = i_low_level_code
AND cpllc.pac_period_id = i_pac_period_id
AND cpllc.cost_group_id = i_cost_group_id);
processed for that item. Insert rows into mpacd for missing cost elements
****************************************************************************/
l_stmt_num := 35;
UPDATE mtl_pac_actual_cost_details mpacd
SET variance_amount = (SELECT decode (sign(cpic.total_layer_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 = i_user_id,
last_update_login = i_login_id,
request_id = i_request_id,
program_application_id = i_prog_appl_id,
program_id = i_prog_id,
program_update_date = sysdate
WHERE transaction_id = l_last_txn_id_tbl (l_index)
AND mpacd.cost_group_id = i_cost_group_id
AND mpacd.pac_period_id = i_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);
INSERT INTO mtl_pac_actual_cost_details mpacd
(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,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_LOGIN,
VARIANCE_AMOUNT,
TXN_CATEGORY)
(SELECT i_cost_group_id,
l_last_txn_id_tbl (l_index),
i_pac_period_id,
i_cost_type_id,
cpicd.cost_element_id,
cpicd.level_type,
cpic.inventory_item_id,
cpic.cost_layer_id,
0,
'N',
'N',
sysdate,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id,
decode (sign(cpic.total_layer_quantity),
0, cpicd.item_balance,
(-1 * sign(cpicd.item_balance)), cpicd.item_balance,
0),
l_txn_category_tbl(l_index)
FROM cst_pac_item_cost_details cpicd,
cst_pac_item_costs cpic
WHERE cpicd.cost_layer_id = cpic.cost_layer_id
AND cpicd.cost_layer_id = l_cost_layer_id_tbl (l_index)
AND NOT EXISTS (SELECT 1
FROM mtl_pac_actual_cost_details mpacd1
WHERE mpacd1.transaction_id = l_last_txn_id_tbl (l_index)
AND mpacd1.cost_layer_id = cpicd.cost_layer_id
AND mpacd1.cost_element_id = cpicd.cost_element_id
AND mpacd1.level_type = cpicd.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,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
decode (sign(cpic.total_layer_quantity),
0, cpicd.item_cost,
(-1 * sign(cpicd.item_balance)), 0,
cpicd.item_balance / cpic.total_layer_quantity),
decode (sign(cpic.total_layer_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(cpic.total_layer_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 (cpic.total_layer_quantity),
0, 0,
(-1 * sign(cpicd.item_balance)), 0,
cpicd.item_balance),
decode (sign (cpic.total_layer_quantity),
0, 0,
(-1 * sign(cpicd.item_balance)), 0,
cpicd.buy_balance),
decode (sign (cpic.total_layer_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 = i_item_id
AND cost_group_id = i_cost_group_id
AND pac_period_id = i_pac_period_id)
AND EXISTS (SELECT 1
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = i_pac_period_id
AND cppb.cost_group_id = i_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 = i_item_id)
AND NOT EXISTS (SELECT 1
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.pac_period_id = i_pac_period_id
AND cpllc.cost_group_id = i_cost_group_id
AND cpllc.inventory_item_id = i_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 i_user_id,
sysdate,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
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
FROM cst_pac_item_costs_v v
WHERE v.cost_layer_id = cpic.cost_layer_id)
WHERE cpic.inventory_item_id = i_item_id
AND cpic.cost_group_id = i_cost_group_id
AND cpic.pac_period_id = i_pac_period_id
AND EXISTS (SELECT 1
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = i_pac_period_id
AND cppb.cost_group_id = i_cost_group_id
AND cppb.cost_layer_id = cpic.cost_layer_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 = i_pac_period_id
AND cpllc.cost_group_id = i_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,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
decode (sign(cpic.total_layer_quantity),
0, cpicd.item_cost,
(-1 * sign(cpicd.item_balance)), 0,
cpicd.item_balance / cpic.total_layer_quantity),
decode (sign(cpic.total_layer_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(cpic.total_layer_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 (cpic.total_layer_quantity),
0, 0,
(-1 * sign(cpicd.item_balance)), 0,
cpicd.item_balance),
decode (sign (cpic.total_layer_quantity),
0, 0,
(-1 * sign(cpicd.item_balance)), 0,
cpicd.buy_balance),
decode (sign (cpic.total_layer_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 = i_item_id
AND cost_group_id = i_cost_group_id
AND pac_period_id = i_pac_period_id)
AND EXISTS (SELECT 1
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = i_pac_period_id
AND cppb.cost_group_id = i_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 = i_item_id)
AND EXISTS (SELECT 1
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.low_level_code = i_low_level_code
AND cpllc.pac_period_id = i_pac_period_id
AND cpllc.cost_group_id = i_cost_group_id
AND cpllc.inventory_item_id = i_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 i_user_id,
sysdate,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
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
FROM cst_pac_item_costs_v v
WHERE v.cost_layer_id = cpic.cost_layer_id)
WHERE cpic.inventory_item_id = i_item_id
AND cpic.cost_group_id = i_cost_group_id
AND cpic.pac_period_id = i_pac_period_id
AND EXISTS (SELECT 1
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = i_pac_period_id
AND cppb.cost_group_id = i_cost_group_id
AND cppb.cost_layer_id = cpic.cost_layer_id)
AND EXISTS (SELECT 1
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.low_level_code = i_low_level_code
AND cpllc.inventory_item_id = cpic.inventory_item_id
AND cpllc.pac_period_id = i_pac_period_id
AND cpllc.cost_group_id = i_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);
SELECT distinct cost_layer_id, mpacd.transaction_id, mpacd.txn_category
BULK COLLECT
INTO l_cost_layer_id_tbl, l_last_txn_id_tbl, l_txn_category_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 = (SELECT max(txn_category)
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = i_pac_period_id
AND cppb.cost_group_id = i_cost_group_id
AND cppb.cost_layer_id = mpacd.cost_layer_id)
AND mpacd1.inventory_item_id = mpacd.inventory_item_id
AND mpacd1.pac_period_id = i_pac_period_id
AND mpacd1.cost_group_id = i_cost_group_id)
AND mpacd.cost_group_id = i_cost_group_id
AND mpacd.pac_period_id = i_pac_period_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 = i_pac_period_id
AND cpllc.cost_group_id = i_cost_group_id);
SELECT max(mpacd.txn_category)
INTO l_max_txn_category
FROM mtl_pac_actual_cost_details mpacd
WHERE mpacd.pac_period_id = i_pac_period_id
AND mpacd.cost_group_id = i_cost_group_id
AND EXISTS (SELECT 1
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.cost_group_id = i_cost_group_id
AND cpllc.pac_period_id = i_pac_period_id
AND cpllc.inventory_item_id = mpacd.inventory_item_id
AND cpllc.low_level_code = i_low_level_code);
SELECT distinct cost_layer_id, mpacd.transaction_id, mpacd.txn_category
BULK COLLECT
INTO l_cost_layer_id_tbl, l_last_txn_id_tbl, l_txn_category_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 = (SELECT max(txn_category)
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = i_pac_period_id
AND cppb.cost_group_id = i_cost_group_id
AND cppb.cost_layer_id = mpacd.cost_layer_id)
AND mpacd1.txn_category = l_max_txn_category
AND mpacd1.inventory_item_id = mpacd.inventory_item_id
AND mpacd1.pac_period_id = i_pac_period_id
AND mpacd1.cost_group_id = i_cost_group_id)
AND mpacd.cost_group_id = i_cost_group_id
AND mpacd.pac_period_id = i_pac_period_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 = i_low_level_code
AND cpllc.pac_period_id = i_pac_period_id
AND cpllc.cost_group_id = i_cost_group_id);
SELECT distinct cost_layer_id, mpacd.transaction_id, mpacd.txn_category
BULK COLLECT
INTO l_cost_layer_id_tbl, l_last_txn_id_tbl, l_txn_category_tbl
FROM mtl_pac_actual_cost_details mpacd
WHERE mpacd.transaction_id = (SELECT max(transaction_id)
FROM mtl_pac_actual_cost_details mpacd1
WHERE mpacd1.txn_category = (SELECT max(txn_category)
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = i_pac_period_id
AND cppb.cost_group_id = i_cost_group_id
AND cppb.cost_layer_id = mpacd.cost_layer_id)
AND mpacd1.inventory_item_id = mpacd.inventory_item_id
AND mpacd1.pac_period_id = i_pac_period_id
AND mpacd1.cost_group_id = i_cost_group_id)
AND mpacd.cost_group_id = i_cost_group_id
AND mpacd.pac_period_id = i_pac_period_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 = i_low_level_code
AND cpllc.pac_period_id = i_pac_period_id
AND cpllc.cost_group_id = i_cost_group_id);
processed for that item. Insert rows into mpacd for missing cost elements
****************************************************************************/
l_stmt_num := 35;
UPDATE mtl_pac_actual_cost_details mpacd
SET variance_amount = (SELECT decode (sign(cpic.total_layer_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 = i_user_id,
last_update_login = i_login_id,
request_id = i_request_id,
program_application_id = i_prog_appl_id,
program_id = i_prog_id,
program_update_date = sysdate
WHERE transaction_id = l_last_txn_id_tbl (l_index)
AND mpacd.cost_group_id = i_cost_group_id
AND mpacd.pac_period_id = i_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);
INSERT INTO mtl_pac_actual_cost_details mpacd
(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,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_LOGIN,
VARIANCE_AMOUNT,
TXN_CATEGORY)
(SELECT i_cost_group_id,
l_last_txn_id_tbl (l_index),
i_pac_period_id,
i_cost_type_id,
cpicd.cost_element_id,
cpicd.level_type,
cpic.inventory_item_id,
cpic.cost_layer_id,
0,
'N',
'N',
sysdate,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id,
decode (sign(cpic.total_layer_quantity),
0, cpicd.item_balance,
(-1 * sign(cpicd.item_balance)), cpicd.item_balance,
0),
l_txn_category_tbl(l_index)
FROM cst_pac_item_cost_details cpicd,
cst_pac_item_costs cpic
WHERE cpicd.cost_layer_id = cpic.cost_layer_id
AND cpicd.cost_layer_id = l_cost_layer_id_tbl (l_index)
AND NOT EXISTS (SELECT 1
FROM mtl_pac_actual_cost_details mpacd1
WHERE mpacd1.transaction_id = l_last_txn_id_tbl (l_index)
AND mpacd1.cost_layer_id = cpicd.cost_layer_id
AND mpacd1.cost_element_id = cpicd.cost_element_id
AND mpacd1.level_type = cpicd.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,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
decode (sign(cpic.total_layer_quantity),
0, cpicd.item_cost,
(-1 * sign(cpicd.item_balance)), 0,
cpicd.item_balance / cpic.total_layer_quantity),
decode (sign(cpic.total_layer_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(cpic.total_layer_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 (cpic.total_layer_quantity),
0, 0,
(-1 * sign(cpicd.item_balance)), 0,
cpicd.item_balance),
decode (sign (cpic.total_layer_quantity),
0, 0,
(-1 * sign(cpicd.item_balance)), 0,
cpicd.buy_balance),
decode (sign (cpic.total_layer_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 pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id)
AND EXISTS (SELECT 1
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = i_pac_period_id
AND cppb.cost_group_id = i_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 NOT EXISTS (SELECT 1
FROM cst_pac_low_level_codes cpllc,
cst_pac_item_costs cpic1
WHERE cpllc.inventory_item_id = cpic1.inventory_item_id
AND cpic1.cost_layer_id = cpicd.cost_layer_id
AND cpllc.pac_period_id = i_pac_period_id
AND cpllc.cost_group_id = i_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 i_user_id,
sysdate,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
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
FROM cst_pac_item_costs_v v
WHERE v.cost_layer_id = cpic.cost_layer_id)
WHERE cpic.cost_group_id = i_cost_group_id
AND cpic.pac_period_id = i_pac_period_id
AND EXISTS (SELECT 1
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = i_pac_period_id
AND cppb.cost_group_id = i_cost_group_id
AND cppb.cost_layer_id = cpic.cost_layer_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 = i_pac_period_id
AND cpllc.cost_group_id = i_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,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
decode (sign(cpic.total_layer_quantity),
0, cpicd.item_cost,
(-1 * sign(cpicd.item_balance)), 0,
cpicd.item_balance / cpic.total_layer_quantity),
decode (sign(cpic.total_layer_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(cpic.total_layer_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 (cpic.total_layer_quantity),
0, 0,
(-1 * sign(cpicd.item_balance)), 0,
cpicd.item_balance),
decode (sign (cpic.total_layer_quantity),
0, 0,
(-1 * sign(cpicd.item_balance)), 0,
cpicd.buy_balance),
decode (sign (cpic.total_layer_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 pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id)
AND EXISTS (SELECT 1
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = i_pac_period_id
AND cppb.cost_group_id = i_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 EXISTS (SELECT 1
FROM cst_pac_low_level_codes cpllc,
cst_pac_item_costs cpic1
WHERE cpllc.low_level_code = i_low_level_code
AND cpllc.pac_period_id = i_pac_period_id
AND cpllc.cost_group_id = i_cost_group_id
AND cpllc.inventory_item_id = cpic1.inventory_item_id
AND cpic1.cost_layer_id = cpicd.cost_layer_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 i_user_id,
sysdate,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
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
FROM cst_pac_item_costs_v v
WHERE v.cost_layer_id = cpic.cost_layer_id)
WHERE cpic.cost_group_id = i_cost_group_id
AND cpic.pac_period_id = i_pac_period_id
AND EXISTS (SELECT 1
FROM cst_pac_period_balances cppb
WHERE cppb.pac_period_id = i_pac_period_id
AND cppb.cost_group_id = i_cost_group_id
AND cppb.cost_layer_id = cpic.cost_layer_id)
AND EXISTS (SELECT 1
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.low_level_code = i_low_level_code
AND cpllc.inventory_item_id = cpic.inventory_item_id
AND cpllc.pac_period_id = i_pac_period_id
AND cpllc.cost_group_id = i_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 update_cppb (i_pac_period_id IN NUMBER,
i_cost_group_id IN NUMBER,
i_txn_category IN NUMBER,
i_low_level_code IN NUMBER,
i_user_id IN NUMBER,
i_login_id IN NUMBER,
i_request_id IN NUMBER,
i_prog_id IN NUMBER,
i_prog_appl_id IN NUMBER,
o_err_num OUT NOCOPY NUMBER,
o_err_code OUT NOCOPY VARCHAR2,
o_err_msg OUT NOCOPY VARCHAR2)
IS
l_stmt_num NUMBER;
l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.update_cppb';
'Entering CSTPPWAC.update_cppb with '||
'i_cost_group_id = '||i_cost_group_id||','||
'i_txn_category = '||i_txn_category||','||
'i_low_level_code = '||i_low_level_code||','||
'i_user_id = '||i_user_id||','||
'i_login_id = '||i_login_id||','||
'i_request_id = '||i_request_id||','||
'i_prog_id = '||i_prog_id||','||
'i_prog_appl_id = '||i_prog_appl_id
);
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,
period_balance,
period_quantity,
periodic_cost,
variance_amount) =
(SELECT i_user_id,
sysdate,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
cpicd.item_balance,
cpic.total_layer_quantity,
cpicd.item_cost,
(SELECT sum (nvl (mpacd.variance_amount, 0))
FROM mtl_pac_actual_cost_details mpacd
WHERE mpacd.txn_category = i_txn_category
AND mpacd.inventory_item_id = cppb.inventory_item_id
AND mpacd.pac_period_id = i_pac_period_id
AND mpacd.cost_group_id = i_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 = i_pac_period_id
AND cppb.cost_group_id = i_cost_group_id
AND cppb.txn_category = i_txn_category
AND i_txn_category = (SELECT max (txn_category)
FROM MTL_PAC_ACTUAL_COST_DETAILS
WHERE pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id
AND inventory_item_id = cppb.inventory_item_id)
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 (last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
period_balance,
period_quantity,
periodic_cost,
variance_amount) =
(SELECT i_user_id,
sysdate,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
cpicd.item_balance,
cpic.total_layer_quantity,
cpicd.item_cost,
(SELECT sum (nvl (mpacd.variance_amount, 0))
FROM mtl_pac_actual_cost_details mpacd
WHERE mpacd.txn_category = i_txn_category
AND mpacd.inventory_item_id = cppb.inventory_item_id
AND mpacd.pac_period_id = i_pac_period_id
AND mpacd.cost_group_id = i_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 = i_pac_period_id
AND cppb.cost_group_id = i_cost_group_id
AND cppb.txn_category = i_txn_category
AND i_txn_category = (SELECT max (txn_category)
FROM MTL_PAC_ACTUAL_COST_DETAILS
WHERE pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id
AND inventory_item_id = cppb.inventory_item_id)
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)
AND NOT EXISTS (SELECT 1
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.inventory_item_id = cppb.inventory_item_id
AND cpllc.pac_period_id = i_pac_period_id
AND cpllc.cost_group_id = i_cost_group_id);
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,
period_balance,
period_quantity,
periodic_cost,
variance_amount) =
(SELECT i_user_id,
sysdate,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
cpicd.item_balance,
cpic.total_layer_quantity,
cpicd.item_cost,
(select sum (nvl (mpacd.variance_amount, 0))
from mtl_pac_actual_cost_details mpacd
where mpacd.txn_category = i_txn_category
and mpacd.inventory_item_id = cppb.inventory_item_id
and mpacd.pac_period_id = i_pac_period_id
and mpacd.cost_group_id = i_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 = i_pac_period_id
AND cppb.cost_group_id = i_cost_group_id
AND cppb.txn_category = i_txn_category
AND i_txn_category = (SELECT max (txn_category)
FROM MTL_PAC_ACTUAL_COST_DETAILS
WHERE pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id
AND inventory_item_id = cppb.inventory_item_id)
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)
AND EXISTS (SELECT 1
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.inventory_item_id = cppb.inventory_item_id
AND cpllc.low_level_code = i_low_level_code
AND cpllc.pac_period_id = i_pac_period_id
AND cpllc.cost_group_id = i_cost_group_id);
'Exiting CSTPPWAC.update_cppb with '||
'o_err_num = '||o_err_num||','||
'o_err_code = '||o_err_code||','||
'o_err_msg = '||o_err_msg
);
END update_cppb;
PROCEDURE update_item_cppb (i_pac_period_id IN NUMBER,
i_cost_group_id IN NUMBER,
i_txn_category IN NUMBER,
i_item_id IN NUMBER,
i_user_id IN NUMBER,
i_login_id IN NUMBER,
i_request_id IN NUMBER,
i_prog_id IN NUMBER,
i_prog_appl_id IN NUMBER,
o_err_num OUT NOCOPY NUMBER,
o_err_code OUT NOCOPY VARCHAR2,
o_err_msg OUT NOCOPY VARCHAR2)
IS
l_stmt_num NUMBER;
l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.update_item_cppb';
'Entering CSTPPWAC.update_item_cppb with '||
'i_cost_group_id = '||i_cost_group_id||','||
'i_txn_category = '||i_txn_category||','||
'i_item_id = '||i_item_id||','||
'i_user_id = '||i_user_id||','||
'i_login_id = '||i_login_id||','||
'i_request_id = '||i_request_id||','||
'i_prog_id = '||i_prog_id||','||
'i_prog_appl_id = '||i_prog_appl_id
);
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,
period_balance,
period_quantity,
periodic_cost,
variance_amount) =
(SELECT i_user_id,
sysdate,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
cpicd.item_balance,
cpic.total_layer_quantity,
cpicd.item_cost,
(SELECT sum (nvl (mpacd.variance_amount, 0))
FROM mtl_pac_actual_cost_details mpacd
WHERE mpacd.txn_category = cppb.txn_category
AND mpacd.inventory_item_id = cppb.inventory_item_id
AND mpacd.pac_period_id = cppb.pac_period_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)
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 = i_pac_period_id
AND cppb.cost_group_id = i_cost_group_id
AND cppb.txn_category = i_txn_category
AND cppb.inventory_item_id = i_item_id
AND i_txn_category = (SELECT max (txn_category)
FROM MTL_PAC_ACTUAL_COST_DETAILS
WHERE pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id
AND inventory_item_id = cppb.inventory_item_id)
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);
'Exiting CSTPPWAC.update_item_cppb with '||
'o_err_num = '||o_err_num||','||
'o_err_code = '||o_err_code||','||
'o_err_msg = '||o_err_msg
);
END update_item_cppb;
PROCEDURE insert_ending_balance (i_pac_period_id IN NUMBER,
i_cost_group_id IN NUMBER,
i_user_id IN NUMBER,
i_login_id IN NUMBER,
i_request_id IN NUMBER,
i_prog_id IN NUMBER,
i_prog_appl_id IN NUMBER,
o_err_num OUT NOCOPY NUMBER,
o_err_code OUT NOCOPY VARCHAR2,
o_err_msg OUT NOCOPY VARCHAR2)
IS
l_stmt_num NUMBER;
l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.insert_ending_balance';
'Entering CSTPPWAC.insert_ending_balance with '||
'i_cost_group_id = '||i_cost_group_id||','||
'i_user_id = '||i_user_id||','||
'i_login_id = '||i_login_id||','||
'i_request_id = '||i_request_id||','||
'i_prog_id = '||i_prog_id||','||
'i_prog_appl_id = '||i_prog_appl_id
);
INSERT INTO cst_pac_period_balances (
pac_period_id,
cost_group_id,
inventory_item_id,
cost_layer_id,
quantity_layer_id,
cost_element_id,
level_type,
txn_category,
txn_category_qty,
txn_category_value,
period_quantity,
periodic_cost,
period_balance,
variance_amount,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
request_id,
program_application_id,
program_id,
program_update_date)
(SELECT i_pac_period_id,
i_cost_group_id,
cpic.inventory_item_id,
cpic.cost_layer_id,
cpql.quantity_layer_id,
cpicd.cost_element_id,
cpicd.level_type,
10, -- txn_category
0,
0,
cpic.total_layer_quantity,
cpicd.item_cost,
cpicd.item_balance,
0,
sysdate,
i_user_id,
i_login_id,
i_user_id,
sysdate,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate
FROM cst_pac_item_costs cpic,
cst_pac_item_cost_details cpicd,
cst_pac_quantity_layers cpql
WHERE cpic.pac_period_id = i_pac_period_id
AND cpic.cost_group_id = i_cost_group_id
AND cpicd.cost_layer_id = cpic.cost_layer_id
AND cpql.cost_layer_id = cpic.cost_layer_id
-- Insert ending balance records in CPPB only for asset items, i.e. only for items
-- which already have atleast one record in CPPB
AND exists (select 1
from cst_pac_period_balances cppb1
where cppb1.inventory_item_id = cpic.inventory_item_id
and cppb1.cost_group_id = cpic.cost_group_id
and cppb1.pac_period_id = cpic.pac_period_id));
'Exiting CSTPPWAC.insert_ending_balance with '||
'o_err_num = '||o_err_num||','||
'o_err_code = '||o_err_code||','||
'o_err_msg = '||o_err_msg
);
END insert_ending_balance;