[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
into l_txn_cost_exists
from mtl_cst_actual_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id
and actual_cost >= 0;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Scrap transaction inserts into MCACD');
insert into mtl_cst_actual_cost_details (
transaction_id,
organization_id,
layer_id,
cost_element_id,
level_type,
transaction_action_id,
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,
new_cost,
insertion_flag,
variance_amount,
user_entered)
select
i_txn_id,
i_org_id,
i_layer_id,
mctcd.cost_element_id,
mctcd.level_type,
i_txn_action_id,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
mctcd.inventory_item_id,
mctcd.transaction_cost,
0,
NULL,
'N',
0,
'N'
from mtl_cst_txn_cost_details mctcd
where mctcd.transaction_id = i_txn_id
and mctcd.organization_id = i_org_id
and mctcd.transaction_cost >= 0;
select po.segment1
into l_src_number
from mtl_material_transactions mmt, po_headers_all po
where mmt.transaction_id = i_txn_id
and mmt.transaction_source_id = po.po_header_id;
select segment1
into l_src_number
from mtl_sales_orders
where sales_order_id = i_src_id;
select concatenated_segments
into l_src_number
from gl_code_combinations_kfv
where code_combination_id = i_src_id;
select wip_entity_name
into l_src_number
from wip_entities
where wip_entity_id = i_src_id;
select concatenated_segments
into l_src_number
from gl_code_combinations_kfv
where code_combination_id = (select distribution_account
from mtl_generic_dispositions
where disposition_id = i_src_id);
select segment1
into l_src_number
from po_requisition_headers_all
where requisition_header_id = i_src_id;
select segment1
into l_src_number
from mtl_sales_orders
where sales_order_id = i_src_id;
select cycle_count_header_name
into l_src_number
from mtl_cycle_count_headers
where cycle_count_header_id = i_src_id;
select physical_inventory_name
into l_src_number
from mtl_physical_inventories
where physical_inventory_id = i_src_id;
select segment1
into l_src_number
from mtl_sales_orders
where sales_order_id = i_src_id;
select concatenated_segments
into l_src_number
from gl_code_combinations_kfv
where code_combination_id = (select
nvl(mmt.distribution_account_id,mmt.transaction_source_id)
from mtl_material_transactions mmt
where transaction_id = i_txn_id);
PROCEDURE insert_mclacd (
i_txn_id IN NUMBER,
i_org_id IN NUMBER,
i_item_id IN NUMBER,
i_layer_id IN NUMBER,
i_cur_layer_id IN NUMBER,
i_qty IN NUMBER,
i_txn_action_id IN NUMBER,
i_user_id IN NUMBER,
i_login_id IN NUMBER,
i_req_id IN NUMBER,
i_prg_id IN NUMBER,
i_prg_appl_id IN NUMBER,
i_actual_cost_table IN VARCHAR2,
i_layer_cost_table IN VARCHAR2,
i_actual_layer_id IN NUMBER,
i_mode IN VARCHAR2,
o_err_num OUT NOCOPY NUMBER,
o_err_code OUT NOCOPY VARCHAR2,
o_err_msg OUT NOCOPY VARCHAR2
)
/* i_cur_layer_id is the inv layer which is being used in MCLACD, to get the
layer costs
i_actual_layer_id is used to get the actual costs
*/
IS
l_stmt_num NUMBER;
select transaction_type_id
into l_txn_type_id
from mtl_material_transactions
where transaction_id = i_txn_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert Row in MCLACD...');
select count(*)
into l_mclacd_exists
from mtl_cst_layer_act_cost_details
where transaction_id = i_txn_id
and layer_id = i_layer_id
and inv_layer_id = i_cur_layer_id;
update mtl_cst_layer_act_cost_details
set layer_quantity = nvl(layer_quantity,0) + i_qty,
variance_amount = nvl(variance_amount,0) + (nvl(actual_cost,0)-nvl(layer_cost,0))*i_qty
where transaction_id = i_txn_id
and layer_id = i_layer_id
and inv_layer_id = i_actual_layer_id;
insert into mtl_cst_layer_act_cost_details (
transaction_id,
organization_id,
layer_id,
inv_layer_id,
cost_element_id,
level_type,
layer_quantity,
layer_cost,
actual_cost,
variance_amount,
inventory_item_id,
user_entered,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
select i_txn_id, -- txn id
i_org_id, -- org id
i_layer_id,
i_cur_layer_id,
mcacd.cost_element_id,
mcacd.level_type,
i_qty,
decode(l_zero_cost_flag, 1, 0,mcacd.actual_cost),
decode(l_zero_cost_flag, 1, 0,mcacd.actual_cost),
0,
i_item_id,
'N',
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate
from mtl_cst_actual_cost_details mcacd
where mcacd.organization_id = i_org_id
and mcacd.transaction_id = i_txn_id
and mcacd.user_entered = 'Y';
insert into mtl_cst_layer_act_cost_details (
transaction_id,
organization_id,
layer_id,
inv_layer_id,
cost_element_id,
level_type,
layer_quantity,
layer_cost,
actual_cost,
variance_amount,
inventory_item_id,
user_entered,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
select i_txn_id,
i_org_id,
i_layer_id,
i_cur_layer_id,
mctcd.cost_element_id,
mctcd.level_type,
i_qty,
decode(l_zero_cost_flag, 1, 0,mctcd.transaction_cost),
decode(l_zero_cost_flag, 1, 0,mctcd.transaction_cost),
0,
i_item_id,
'N',
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate
from mtl_cst_txn_cost_details mctcd
where mctcd.organization_id = i_org_id
and mctcd.transaction_id = i_txn_id ;
insert into mtl_cst_layer_act_cost_details (
transaction_id,
organization_id,
layer_id,
inv_layer_id,
cost_element_id,
level_type,
layer_quantity,
layer_cost,
actual_cost,
variance_amount,
inventory_item_id,
user_entered,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
select i_txn_id,
i_org_id,
i_layer_id,
i_cur_layer_id,
1, --CE
1, --LT
i_qty,
0, --layer cost
0, -- actual cost
0, -- var amount
i_item_id,
'N',
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate
from dual;
insert into mtl_cst_layer_act_cost_details (
transaction_id,
organization_id,
layer_id,
inv_layer_id,
cost_element_id,
level_type,
layer_quantity,
layer_cost,
actual_cost,
variance_amount,
payback_variance_amount,
inventory_item_id,
user_entered,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
select i_txn_id,
i_org_id,
i_layer_id,
i_cur_layer_id,
cilcd2.cost_element_id,
cilcd2.level_type,
i_qty,
nvl(cilcd2.layer_cost,0),
nvl(cilcd1.layer_cost,0),
decode(l_txn_type_id,68,0,(nvl(cilcd1.layer_cost,0)-nvl(cilcd2.layer_cost,0))*i_qty),
decode(l_txn_type_id,68,(nvl(cilcd1.layer_cost,0)-nvl(cilcd2.layer_cost,0))*i_qty,0),
i_item_id,
'N',
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate
FROM cst_inv_layer_cost_details cilcd1,
cst_inv_layer_cost_details cilcd2
where cilcd1.inv_layer_id (+) = i_actual_layer_id
and cilcd2.inv_layer_id = i_cur_layer_id
and cilcd1.cost_element_id(+) = cilcd2.cost_element_id
and cilcd1.level_type (+) = cilcd2.level_type
UNION
select i_txn_id,
i_org_id,
i_layer_id,
i_cur_layer_id,
cilcd1.cost_element_id,
cilcd1.level_type,
i_qty,
nvl(cilcd2.layer_cost,0),
nvl(cilcd1.layer_cost,0),
decode(l_txn_type_id,68,0,(nvl(cilcd1.layer_cost,0)-nvl(cilcd2.layer_cost,0))*i_qty),
decode(l_txn_type_id,68,(nvl(cilcd1.layer_cost,0)-nvl(cilcd2.layer_cost,0))*i_qty,0),
i_item_id,
'N',
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate
FROM cst_inv_layer_cost_details cilcd1,
cst_inv_layer_cost_details cilcd2
where cilcd1.inv_layer_id = i_actual_layer_id
and cilcd2.inv_layer_id(+) = i_cur_layer_id
and cilcd1.cost_element_id = cilcd2.cost_element_id (+)
and cilcd1.level_type = cilcd2.level_type(+);
insert into mtl_cst_layer_act_cost_details (
transaction_id,
organization_id,
layer_id,
inv_layer_id,
cost_element_id,
level_type,
layer_quantity,
layer_cost,
actual_cost,
variance_amount,
payback_variance_amount,
inventory_item_id,
user_entered,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
select i_txn_id,
i_org_id,
i_layer_id,
i_cur_layer_id,
cilcd.cost_element_id,
cilcd.level_type,
i_qty,
nvl(cilcd.layer_cost,0),
nvl(mcacd.actual_cost,0),
decode(l_txn_type_id,68,0,(nvl(mcacd.actual_cost,0)-nvl(cilcd.layer_cost,0))*i_qty),
decode(l_txn_type_id,68,(nvl(mcacd.actual_cost,0)-nvl(cilcd.layer_cost,0))*i_qty,0),
i_item_id,
'N',
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate
FROM cst_inv_layer_cost_details cilcd,mtl_cst_actual_cost_details mcacd
where mcacd.organization_id (+) = i_org_id
and mcacd.transaction_id(+) = i_txn_id
and cilcd.inv_layer_id = i_cur_layer_id
and cilcd.cost_element_id = mcacd.cost_element_id (+)
and cilcd.level_type = mcacd.level_type (+)
AND mcacd.user_entered(+) = 'Y'
UNION
select i_txn_id,
i_org_id,
i_layer_id,
i_cur_layer_id,
mcacd.cost_element_id,
mcacd.level_type,
i_qty,
nvl(cilcd.layer_cost,0),
nvl(mcacd.actual_cost,0),
decode(l_txn_type_id,68,0,(nvl(mcacd.actual_cost,0)-nvl(cilcd.layer_cost,0))*i_qty),
decode(l_txn_type_id,68,(nvl(mcacd.actual_cost,0)-nvl(cilcd.layer_cost,0))*i_qty,0),
i_item_id,
'N',
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate
FROM cst_inv_layer_cost_details cilcd,mtl_cst_actual_cost_details mcacd
where mcacd.organization_id = i_org_id
and mcacd.transaction_id = i_txn_id
and cilcd.inv_layer_id(+) = i_cur_layer_id
and cilcd.cost_element_id(+) = mcacd.cost_element_id
and cilcd.level_type (+) = mcacd.level_type
AND mcacd.user_entered = 'Y' ;
insert into mtl_cst_layer_act_cost_details (
transaction_id,
organization_id,
layer_id,
inv_layer_id,
cost_element_id,
level_type,
layer_quantity,
layer_cost,
actual_cost,
variance_amount,
payback_variance_amount,
inventory_item_id,
user_entered,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
select i_txn_id,
i_org_id,
i_layer_id,
i_cur_layer_id,
cilcd.cost_element_id,
cilcd.level_type,
i_qty,
nvl(cilcd.layer_cost,0),
nvl(mctcd.transaction_cost,0),
decode(l_txn_type_id,68,0,(nvl(mctcd.transaction_cost,0)-nvl(cilcd.layer_cost,0))*i_qty),
decode(l_txn_type_id,68,(nvl(mctcd.transaction_cost,0)-nvl(cilcd.layer_cost,0))*i_qty,0),
i_item_id,
'N',
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate
FROM cst_inv_layer_cost_details cilcd,mtl_cst_txn_cost_details mctcd
where mctcd.organization_id(+) = i_org_id
and mctcd.transaction_id (+) = i_txn_id
and cilcd.inv_layer_id = i_cur_layer_id
and cilcd.cost_element_id = mctcd.cost_element_id (+)
and cilcd.level_type = mctcd.level_type (+)
UNION
select i_txn_id,
i_org_id,
i_layer_id,
i_cur_layer_id,
mctcd.cost_element_id,
mctcd.level_type,
i_qty,
nvl(cilcd.layer_cost,0),
nvl(mctcd.transaction_cost,0),
decode(l_txn_type_id,68,0,(nvl(mctcd.transaction_cost,0)-nvl(cilcd.layer_cost,0))*i_qty),
decode(l_txn_type_id,68,(nvl(mctcd.transaction_cost,0)-nvl(cilcd.layer_cost,0))*i_qty,0),
i_item_id,
'N',
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate
FROM cst_inv_layer_cost_details cilcd,mtl_cst_txn_cost_details mctcd
where mctcd.organization_id = i_org_id
and mctcd.transaction_id = i_txn_id
and cilcd.inv_layer_id (+) = i_cur_layer_id
and cilcd.cost_element_id(+) = mctcd.cost_element_id
and cilcd.level_type(+) = mctcd.level_type ;
OR(i_mode = 'UPDATE' and i_actual_cost_table = 'CILCD' and i_layer_cost_table = 'CILCD')
OR(i_mode = 'CONSUME' and i_actual_cost_table = 'CILCD' and i_layer_cost_table = 'CILCD')
) then
/* EAM Acct Enh Project */
CST_Utility_PUB.get_zeroCostIssue_flag (
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_txn_id => i_txn_id,
x_zero_cost_flag => l_zero_cost_flag
);
insert into mtl_cst_layer_act_cost_details (
transaction_id,
organization_id,
layer_id,
inv_layer_id,
cost_element_id,
level_type,
layer_quantity,
layer_cost,
actual_cost,
variance_amount,
inventory_item_id,
user_entered,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
select i_txn_id,
i_org_id,
i_layer_id,
i_cur_layer_id,
cilcd.cost_element_id,
cilcd.level_type,
i_qty,
decode(l_zero_cost_flag, 1, 0,cilcd.layer_cost),
decode(l_zero_cost_flag, 1, 0,cilcd.layer_cost),
0,
i_item_id,
'N',
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate
from cst_inv_layers cil, cst_inv_layer_cost_details cilcd
where cil.inv_layer_id = cilcd.inv_layer_id
and cil.layer_id = cilcd.layer_id
and cil.inv_layer_id = i_actual_layer_id;
insert into mtl_cst_layer_act_cost_details (
transaction_id,
organization_id,
layer_id,
inv_layer_id,
cost_element_id,
level_type,
layer_quantity,
layer_cost,
actual_cost,
variance_amount,
inventory_item_id,
user_entered,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
select i_txn_id,
i_org_id,
i_layer_id,
i_cur_layer_id,
cilcd.cost_element_id,
cilcd.level_type,
i_qty,
decode(l_zero_cost_flag, 1, 0, cilcd.layer_cost),
decode(l_zero_cost_flag, 1, 0, cilcd.layer_cost),
0,
i_item_id,
'N',
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate
from cst_inv_layers cil, cst_inv_layer_cost_details cilcd
where cil.inv_layer_id = cilcd.inv_layer_id
and cil.layer_id = cilcd.layer_id
and cil.inv_layer_id = i_actual_layer_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,sql%rowcount || ' records inserted using stmt : ' || to_char(l_stmt_num));
o_err_msg := 'CSTPLENG.insert_mclacd (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
End insert_mclacd;
/* If expense item, then insert into MCACD using current costs. No inventory layer created */
IF (i_exp_flag = 1) THEN
IF (l_debug = 'Y') THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into MCACD for expense flag of 1...');
SELECT COUNT(*)
INTO l_count
FROM mtl_cst_txn_cost_details
WHERE transaction_id = i_txn_id
AND organization_id = i_org_id;
INSERT
INTO mtl_cst_actual_cost_details (
transaction_id,
organization_id,
layer_id,
cost_element_id,
level_type,
transaction_action_id,
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,
new_cost,
insertion_flag,
variance_amount,
user_entered)
SELECT i_txn_id,
i_org_id,
i_layer_id,
ctcd.cost_element_id,
ctcd.level_type,
i_txn_action_id,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
ctcd.inventory_item_id,
ctcd.transaction_cost,
0,
0,
'Y',
0,
'N'
FROM mtl_cst_txn_cost_details ctcd
WHERE ctcd.transaction_id = i_txn_id
AND ctcd.organization_id = i_org_id
/*AND ctcd.transaction_cost >= 0*/; -- commented for bug#3835412
SELECT count(*)
INTO l_count
FROM cst_layer_cost_details
WHERE layer_id = i_layer_id;
INSERT
INTO mtl_cst_actual_cost_details (
transaction_id,
organization_id,
layer_id,
cost_element_id,
level_type,
transaction_action_id,
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,
new_cost,
insertion_flag,
variance_amount,
user_entered)
SELECT i_txn_id,
i_org_id,
i_layer_id,
clcd.cost_element_id,
clcd.level_type,
i_txn_action_id,
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,
clcd.item_cost,
clcd.item_cost,
clcd.item_cost,
'N',
0,
'N'
FROM cst_layer_cost_details clcd
WHERE layer_id = i_layer_id;
INSERT
INTO mtl_cst_actual_cost_details (
transaction_id,
organization_id,
layer_id,
cost_element_id,
level_type,
transaction_action_id,
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,
new_cost,
insertion_flag,
variance_amount,
user_entered)
SELECT i_txn_id,
i_org_id,
i_layer_id,
1,
1,
i_txn_action_id,
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,
0,
0,
'Y',
0,
'N'
FROM dual;
SELECT nvl(MAX(inv_layer_id),-1)
INTO l_inv_layer_id
FROM cst_inv_layers
WHERE layer_id = i_layer_id;
/* Obtain cost table, whose costs need to be used to insert into MCLACD
If cost_hook is present, use MCACD, else use costs from MCTCD, or the latest
inventory layer with positive quantity, 0 cost otherwise */
IF (i_cost_hook = 1) THEN
l_actual_cost_table := 'MCACD';
SELECT count(*)
INTO l_count
FROM mtl_cst_txn_cost_details
WHERE transaction_id = i_txn_id
AND organization_id = i_org_id
/* AND transaction_cost >= 0 */; -- commented for bug#3835412
/* Insert into MCLACD */
l_stmt_num := 40;
insert_mclacd(
i_txn_id,
i_org_id,
i_item_id,
i_layer_id,
l_inv_layer_id,
i_txn_qty,
i_txn_action_id,
i_user_id,
i_login_id,
i_req_id,
i_prg_id,
i_prg_appl_id,
l_actual_cost_table,
l_actual_cost_table,
l_inv_layer_id,
'CREATE',
l_err_num,
l_err_code,
l_err_msg);
SELECT COUNT(*)
INTO l_count
FROM cst_inv_layers cil,
cst_quantity_layers cql
WHERE cql.layer_id = i_layer_id
AND cil.inv_layer_id = l_inv_layer_id
AND cil.layer_quantity < 0
AND cil.layer_quantity > cql.layer_quantity;
SELECT create_transaction_id
INTO l_last_txn_id
FROM cst_inv_layers
WHERE inv_layer_id = l_inv_layer_id;
SELECT mmt.transaction_type_id,
decode(rt2.parent_transaction_id,-1,rt2.transaction_id,rt2.parent_transaction_id)
INTO l_last_txn_type_id,
l_last_rcv_txn_id
FROM mtl_material_transactions mmt,
rcv_transactions rt1,
rcv_transactions rt2
WHERE mmt.transaction_id = l_last_txn_id
AND mmt.rcv_transaction_id = rt1.transaction_id (+)
AND rt1.parent_transaction_id = rt2.transaction_id (+);
SELECT mmt.transaction_type_id,
decode(rt2.parent_transaction_id,-1,rt2.transaction_id,rt2.parent_transaction_id)
INTO l_txn_type_id,
l_rcv_txn_id
FROM mtl_material_transactions mmt,
rcv_transactions rt1,
rcv_transactions rt2
WHERE mmt.transaction_id = i_txn_id
AND mmt.rcv_transaction_id = rt1.transaction_id (+)
AND rt1.parent_transaction_id = rt2.transaction_id (+);
SELECT nvl(SUM(actual_cost),0)
INTO l_last_moh
FROM mtl_cst_layer_act_cost_details
WHERE transaction_id = l_last_txn_id
AND organization_id = i_org_id
AND layer_id = i_layer_id
AND inv_layer_id = l_inv_layer_id
AND cost_element_id = 2
AND level_type = 1;
SELECT nvl(SUM(actual_cost),0)
INTO l_moh
FROM mtl_cst_layer_act_cost_details
WHERE transaction_id = i_txn_id
AND organization_id = i_org_id
AND layer_id = i_layer_id
AND inv_layer_id = l_inv_layer_id
AND cost_element_id = 2
AND level_type = 1;
layer cost update could happen before the current transaction */
l_stmt_num := 86;
SELECT nvl(SUM(layer_cost),0)
INTO l_last_layer_cost
FROM cst_inv_layers
WHERE organization_id = i_org_id
AND layer_id = i_layer_id
AND inv_layer_id = l_inv_layer_id;
SELECT nvl(SUM(layer_cost),0)
INTO l_layer_cost
FROM mtl_cst_layer_act_cost_details
WHERE transaction_id = i_txn_id
AND organization_id = i_org_id
AND layer_id = i_layer_id
AND inv_layer_id = l_inv_layer_id
AND level_type = 1;
SELECT transaction_source_id
INTO l_src_id
FROM mtl_material_transactions
WHERE transaction_id = i_txn_id;
/* Update last created inventory layer */
l_stmt_num := 90;
UPDATE cst_inv_layers
SET creation_quantity = creation_quantity + i_txn_qty,
layer_quantity = layer_quantity + i_txn_qty,
transaction_source_id = decode(transaction_source_id, l_src_id, l_src_id, null),
transaction_source = decode(transaction_source, l_src_number, l_src_number, null),
last_update_date = sysdate,
last_updated_by = i_user_id,
creation_date = sysdate,
created_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
WHERE inv_layer_id = l_inv_layer_id;
SELECT cst_inv_layers_s.nextval
INTO l_inv_layer_id
FROM dual;
/* Update MCLACD entries */
l_stmt_num := 100;
UPDATE mtl_cst_layer_act_cost_details
SET inv_layer_id = l_inv_layer_id
WHERE transaction_id = i_txn_id
AND organization_id = i_org_id
AND layer_id = i_layer_id;
FND_FILE.PUT_LINE(FND_FILE.LOG, sql%rowcount || ' records updated in mclacd for ' || l_inv_layer_id);
SELECT transaction_source_id
INTO l_src_id
FROM mtl_material_transactions
WHERE transaction_id = i_txn_id;
INSERT
INTO cst_inv_layers (
layer_id,
inv_layer_id,
organization_id,
inventory_item_id,
creation_quantity,
layer_quantity,
layer_cost,
create_transaction_id,
transaction_source_id,
transaction_action_id,
transaction_source_type_id,
transaction_source,
unburdened_cost,
burden_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES (i_layer_id,
l_inv_layer_id,
i_org_id,
i_item_id,
i_txn_qty,
i_txn_qty,
0,
i_txn_id,
l_src_id,
i_txn_action_id,
i_txn_src_type,
l_src_number,
0,
0,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate);
/* Delete cost details for the inventory layer from CILCD. No rows should
be present. Just a safety check */
l_stmt_num := 120;
DELETE
FROM cst_inv_layer_cost_details
WHERE inv_layer_id = l_inv_layer_id;
INSERT
INTO cst_inv_layer_cost_details (
layer_id,
inv_layer_id,
level_type,
cost_element_id,
layer_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT i_layer_id,
l_inv_layer_id,
mclacd.level_type,
mclacd.cost_element_id,
SUM(mclacd.actual_cost),
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate
FROM mtl_cst_layer_act_cost_details mclacd
WHERE transaction_id = i_txn_id
AND inv_layer_id = l_inv_layer_id
AND organization_id = i_org_id
GROUP
BY transaction_id,
inv_layer_id,
cost_element_id,
level_type;
/* Update layer cost in CIL */
l_stmt_num := 130;
UPDATE cst_inv_layers
SET layer_cost = (
SELECT SUM(layer_cost)
FROM cst_inv_layer_cost_details
WHERE inv_layer_id = l_inv_layer_id
GROUP
BY inv_layer_id),
(unburdened_cost,burden_cost) = (
SELECT SUM(decode(cost_element_id,
2,decode(level_type,2,layer_cost,0),
layer_cost)),
SUM(decode(cost_element_id,
2,decode(level_type,1,layer_cost,0),
0))
FROM cst_inv_layer_cost_details
WHERE inv_layer_id = l_inv_layer_id
GROUP
BY inv_layer_id)
WHERE layer_id = i_layer_id
AND inv_layer_id = l_inv_layer_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'CIL cost updated from CILCD');
sql_stmt := 'select inv_layer_id, layer_quantity from cst_inv_layers ' ||
'where layer_id = :i and layer_quantity < 0 order by creation_date';
insert rows into MCLACD for replenishment */
l_stmt_num := 140;
/* Insert into MCLACD for the negative layer, using actual cost from
positive layer and layer cost from the negative layer
Verify Insert_mclacd( ) code for layer costs and actual costs */
l_stmt_num := 150;
insert_mclacd (
i_txn_id,
i_org_id,
i_item_id,
i_layer_id,
l_neg_layer_id,
l_qty,
i_txn_action_id,
i_user_id,
i_login_id,
i_req_id,
i_prg_id,
i_prg_appl_id,
'CILCD',
'CILCD',
l_inv_layer_id,
'REPLENISH',
l_err_num,
l_err_code,
l_err_msg);
/* Update quantity for the negative layer and the quantity available
for replenishment */
IF (nvl(i_interorg_rec,-1) <> 3) THEN
l_stmt_num := 140;
UPDATE cst_inv_layers
SET layer_quantity = l_neg_layer_qty + l_qty
WHERE inv_layer_id = l_neg_layer_id;
/* Insert into MCLACD using negative quantity for current layer */
l_stmt_num := 155;
insert_mclacd(
i_txn_id,
i_org_id,
i_item_id,
i_layer_id,
l_inv_layer_id,
-1*l_qty,
i_txn_action_id,
i_user_id,
i_login_id,
i_req_id,
i_prg_id,
i_prg_appl_id,
'CILCD',
'CILCD',
l_inv_layer_id,
'UPDATE',
l_err_num,
l_err_code,
l_err_msg);
/* Update layer quantity for current layer in CIL */
l_stmt_num := 160;
UPDATE cst_inv_layers
SET layer_quantity=layer_quantity-l_qty
WHERE inv_layer_id = l_inv_layer_id;
UPDATE cst_inv_layers
SET layer_quantity = l_qty_available
WHERE inv_layer_id = l_inv_layer_id;
select count(*)
into l_layers_exist
from cst_inv_layers
where layer_id = i_layer_id;
/* If expense item, then insert into MCACD using current costs. No inventory
layer consumed or created */
l_stmt_num := 6;
select decode(inventory_asset_flag,'Y',0,1)
into l_exp_item
from mtl_system_items
where inventory_item_id = i_item_id
and organization_id = i_org_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into MCACD for expense item...');
select count(*) into l_count
from cst_layer_cost_details
where layer_id = i_layer_id;
insert into mtl_cst_actual_cost_details (
transaction_id,
organization_id,
layer_id,
cost_element_id,
level_type,
transaction_action_id,
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,
new_cost,
insertion_flag,
variance_amount,
user_entered)
select i_txn_id,
i_org_id,
i_layer_id,
clcd.cost_element_id,
clcd.level_type,
i_txn_action_id,
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,
clcd.item_cost,
clcd.item_cost,
clcd.item_cost,
'N',
0,
'N'
from cst_layer_cost_details clcd
where layer_id = i_layer_id;
insert into mtl_cst_actual_cost_details (
transaction_id,
organization_id,
layer_id,
cost_element_id,
level_type,
transaction_action_id,
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,
new_cost,
insertion_flag,
variance_amount,
user_entered)
select i_txn_id,
i_org_id,
i_layer_id,
1,
1,
i_txn_action_id,
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,
0,
0,
'Y',
0,
'N'
from dual;
select transaction_source_id
into l_src_id
from mtl_material_transactions
where transaction_id = i_txn_id;
select subinventory_code
into l_subinv
from mtl_material_transactions
where transaction_id = i_txn_id;
select decode(asset_inventory, 1, 0, 1)
into l_expsub
from mtl_secondary_inventories
where organization_id = i_org_id
and secondary_inventory_name = l_subinv;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into MCACD for the Item Coming from Exp Sub Inv ...');
SELECT COUNT(*)
INTO l_count
FROM mtl_cst_txn_cost_details
WHERE transaction_id = i_txn_id
AND organization_id = i_org_id;
INSERT
INTO mtl_cst_actual_cost_details (
transaction_id,
organization_id,
layer_id,
cost_element_id,
level_type,
transaction_action_id,
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,
new_cost,
insertion_flag,
variance_amount,
user_entered)
SELECT i_txn_id,
i_org_id,
i_layer_id,
ctcd.cost_element_id,
ctcd.level_type,
i_txn_action_id,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
ctcd.inventory_item_id,
ctcd.transaction_cost,
0,
0,
'Y',
0,
'N'
FROM mtl_cst_txn_cost_details ctcd
WHERE ctcd.transaction_id = i_txn_id
AND ctcd.organization_id = i_org_id ;
INSERT
INTO mtl_cst_actual_cost_details (
transaction_id,
organization_id,
layer_id,
cost_element_id,
level_type,
transaction_action_id,
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,
new_cost,
insertion_flag,
variance_amount,
user_entered)
SELECT i_txn_id,
i_org_id,
i_layer_id,
1,
1,
i_txn_action_id,
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,
0,
0,
'Y',
0,
'N'
FROM dual;
select subinventory_code
into l_subinv
from mtl_material_transactions
where transaction_id = i_txn_id;
select decode(asset_inventory, 1, 0, 1)
into l_expsub
from mtl_secondary_inventories
where organization_id = i_org_id
and secondary_inventory_name = l_subinv;
select count(*)
into l_txn_cost_exists
from mtl_cst_txn_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id;
is inserted with zero cost against this level Material element.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
select count(*)
into l_inv_layer_exists
from cst_inv_layer_cost_details
where layer_id=i_layer_id;
insert_mclacd(i_txn_id,
i_org_id,
i_item_id,
i_layer_id,
l_inv_layer_table(i).inv_layer_id,
-1*l_inv_layer_table(i).layer_quantity,
i_txn_action_id,
i_user_id,
i_login_id,
i_req_id,
i_prg_id,
i_prg_appl_id,
l_actual_cost_table,
'CILCD',
l_inv_layer_table(i).inv_layer_id,
'CONSUME',
l_err_num,
l_err_code,
l_err_msg);
/* If layer is driven negative, then apply_layer_material_ovhd should not be called again, insert_mclacd return value of 999 in such a case */
if (l_err_num <> 0) then
if (l_err_num = 999) then
l_err_num := 0;
update cst_inv_layers
set layer_quantity = nvl(layer_quantity,0)-l_inv_layer_table(i).layer_quantity
where inv_layer_id = l_inv_layer_table(i).inv_layer_id;
SELECT MIN(inv_layer_id)
INTO l_inv_layer_id
FROM cst_inv_layers
WHERE layer_id = i_layer_id
AND layer_quantity > 0;
SELECT MAX(inv_layer_id)
INTO l_inv_layer_id
FROM cst_inv_layers
WHERE layer_id = i_layer_id
AND layer_quantity > 0;
SELECT MAX(inv_layer_id)
INTO l_inv_layer_id
FROM cst_inv_layers
WHERE layer_id = i_layer_id;
insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
SELECT inv_layer_id, layer_quantity
INTO l_inv_layer_rec.inv_layer_id,l_inv_layer_rec.layer_quantity
FROM cst_inv_layers
WHERE inv_layer_id = l_custom_layer -- inventory layer id exists
AND layer_id = i_layer_id; -- correct organization, item, cost group
insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
SELECT count(*)
INTO l_pos_layer_exist
FROM cst_inv_layers
WHERE layer_id = i_layer_id
AND inv_layer_id <> l_custom_layer
AND layer_quantity > 0;
insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
SELECT inv_layer_id, l_custom_layers(i).layer_quantity
INTO l_inv_layer_rec.inv_layer_id, l_inv_layer_rec.layer_quantity
FROM cst_inv_layers
WHERE inv_layer_id = l_custom_layers(i).inv_layer_id -- valid inventory layer id
AND layer_id = i_layer_id -- valid org, item, cost group
AND layer_quantity >=
l_custom_layers(i).layer_quantity -- enough quantity
AND l_custom_layers(i).layer_quantity > 0; -- positive quanttiy
insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
SELECT COUNT(*)
INTO l_rtr
FROM mtl_material_transactions
WHERE transaction_id = i_txn_id
AND transaction_action_id in (1, 29)
AND transaction_source_type_id = 1;
SELECT mmt_del.transaction_id
INTO l_rtr_txn_id
FROM mtl_material_transactions mmt_del,
mtl_material_transactions mmt_rtr,
rcv_transactions rt_rtr
WHERE mmt_del.rcv_transaction_id = rt_rtr.parent_transaction_id
AND rt_rtr.transaction_id = mmt_rtr.rcv_transaction_id
AND mmt_rtr.transaction_id = i_txn_id;
sql_stmt := 'SELECT inv_layer_id, layer_quantity'
||' FROM cst_inv_layers'
||' WHERE create_transaction_id = :i'
||' AND layer_quantity > 0'
||' AND inv_layer_id <> :j';
sql_stmt := 'SELECT inv_layer_id,layer_quantity FROM cst_inv_layers'
||' WHERE layer_id = :i AND transaction_source_id = :j AND layer_quantity > 0 '
||' AND create_transaction_id <> :k AND inv_layer_id <> :l';
sql_stmt := 'SELECT inv_layer_id, layer_quantity FROM cst_inv_layers'
||' WHERE layer_id = :i AND inv_layer_id <> :j'
||' AND NVL(transaction_source_id,-2) <> :k'
||' AND layer_quantity > 0';
SELECT MAX(inv_layer_id)
INTO l_inv_layer_rec.inv_layer_id
FROM cst_inv_layers
WHERE layer_id = i_layer_id
AND transaction_source_id = l_source_id;
SELECT MIN(inv_layer_id)
INTO l_inv_layer_rec.inv_layer_id
FROM cst_inv_layers
WHERE layer_id = i_layer_id
AND transaction_source_id = l_source_id;
insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
sql_stmt := 'SELECT inv_layer_id,layer_quantity FROM cst_inv_layers WHERE layer_id = :i'
||' AND inv_layer_id <> :j AND NVL(transaction_source_id,-2) <> :k'
||' AND layer_quantity > 0';
sql_stmt := 'SELECT inv_layer_id,layer_quantity FROM cst_inv_layers WHERE layer_id = :i';
insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
PROCEDURE insert_record(l_inv_layer_rec IN cst_layer_rec_type,
l_inv_layer_table IN OUT NOCOPY cst_layer_tbl_type,
o_err_num OUT NOCOPY NUMBER,
o_err_code OUT NOCOPY VARCHAR2,
o_err_msg OUT NOCOPY VARCHAR2) IS
l_stmt_num NUMBER;
o_err_msg := 'CSTPLENG.insert_record (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
END insert_record;
select DEFAULT_MATL_OVHD_COST_ID
into l_default_MOH_subelement
from mtl_parameters
where organization_id= I_ORG_ID;
select count(*)
into l_mat_ovhds
from mtl_cst_layer_act_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = i_layer_id -- cost group layer
and inv_layer_id = i_inv_layer_id -- inventory layer
and cost_element_id = 2
and level_type = decode(i_level,1,1,level_type)
and actual_cost > 0;
select organization_id, transfer_organization_id, primary_quantity
into l_txn_org_id, l_txfr_org_id, l_txn_qty
from mtl_material_transactions
where transaction_id = i_txn_id;
select NVL(elemental_visibility_enabled,'N')
into l_elemental_visible
from mtl_interorg_parameters
where from_organization_id = l_from_org
and to_organization_id = l_to_org;
select count(*)
into l_macs_ovhd
from mtl_actual_cost_subelement
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = i_layer_id
and cost_element_id = 2
and level_type = decode (i_level, 1,1,level_type);
if l_macs_ovhd <= 0 then /* inserting MACS */
if (i_mat_ct_id <> i_cost_type) then -- this is the common scenario since the
-- seeded cost type for FIFO/LIFO should
-- not be the rate cost type
l_stmt_num := 30;
select nvl(sum(actual_cost),0)
into l_item_cost
from mtl_cst_layer_act_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = i_layer_id
and inv_layer_id = i_inv_layer_id;
select count(*)
into l_res_id
from cst_item_cost_details cicd
where inventory_item_id = i_item_id
and organization_id = i_org_id
and cost_type_id = i_mat_ct_Id
and basis_type in (1,2,5,6)
and cost_element_id = 2
and level_type = decode (i_level, 1,1,level_type)
and resource_id IS NULL;
if (l_res_id > 0) then /*Changed this if block and inserted the update statement
instead of raising the exception due to bugg 3959770*/
if (l_default_MOH_subelement is NOT NULL) then
update CST_ITEM_COST_DETAILS
set resource_id = l_default_MOH_subelement
where inventory_item_id = i_item_id
and organization_id = i_org_id
and cost_type_id = i_mat_ct_Id
and basis_type in (1,2,5,6)
and cost_element_id = 2
and level_type = decode (i_level, 1,1,level_type)
and resource_id IS NULL;
fnd_file.put_line(fnd_file.log, 'Inserting into MACS');
Insert into mtl_actual_cost_subelement(
transaction_id,
organization_id,
layer_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_org_id,
i_layer_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_mat_ct_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_mat_ovhds
from cst_layer_cost_details
where layer_id = i_layer_id
and cost_element_id = 2
and level_type = 1;
select count(*)
into l_res_id
from cst_item_cost_details
where cost_type_id = i_avg_rates_id
and inventory_item_id = i_item_id
and organization_id = i_org_id;
select resource_id
into l_res_id
from cst_item_cost_details
where cost_type_id = i_avg_rates_id
and inventory_item_id = i_item_id
and organization_id = i_org_id
and cost_element_id = 2
and level_type = 1
and rownum = 1;
/* Changed this check and included the elsif block which inserts the resource
id instead of throwing the exception Bug 3959770*/
if (l_res_id = 0) then
raise avg_rates_no_ovhd;
update cst_item_cost_details
set resource_id = l_default_MOH_subelement
where cost_type_id = i_avg_rates_id
and inventory_item_id = i_item_id
and organization_id = i_org_id
and cost_element_id = 2
and level_type = 1
and resource_id IS NULL
and rownum =1;
fnd_file.put_line(fnd_file.log, 'Inserting into MACS');
Insert into mtl_actual_cost_subelement(
transaction_id,
organization_id,
layer_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_org_id,
i_layer_id,
clcd.cost_element_id,
clcd.level_type,
l_res_id,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
clcd.item_cost,
'N'
from cst_layer_cost_details clcd
where layer_id = i_layer_id
and cost_element_id = 2
and level_type = 1;
end if; /* end of inserting MACS */
select count(*)
into l_mat_ovhds
from mtl_actual_cost_subelement
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = i_layer_id
and cost_element_id = 2
and level_type = decode(i_level, 1,1,level_type);
select count(*)
into l_mclacd_ovhd
from mtl_cst_layer_act_cost_details mclacd
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = i_layer_id
and inv_layer_id = i_inv_layer_id
and cost_element_id = 2
and level_type = decode(i_level,1,1,level_type);
select sum(actual_cost)
into l_ovhd_cost
from mtl_actual_cost_subelement
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = i_layer_id
and cost_element_id = 2;
select transaction_type_id
into l_txn_type_id
from mtl_material_transactions
where transaction_id = i_txn_id;
update mtl_cst_layer_act_cost_details mclacd
set mclacd.actual_cost = nvl(mclacd.actual_cost, 0) + l_ovhd_cost,
mclacd.layer_cost = nvl(mclacd.layer_cost,0) + l_ovhd_cost,
mclacd.variance_amount = 0,
mclacd.payback_variance_amount = 0
where mclacd.transaction_id = i_txn_id
and mclacd.organization_id = i_org_id
and mclacd.layer_id = i_layer_id
and mclacd.inv_layer_id = i_inv_layer_id
and mclacd.level_type = 1
and mclacd.cost_element_id = 2;
update mtl_cst_layer_act_cost_details mclacd
set mclacd.actual_cost = nvl(mclacd.actual_cost, 0) + l_ovhd_cost,
mclacd.variance_amount = decode(l_txn_type_id,68,0,
(nvl(mclacd.actual_cost,0) + l_ovhd_cost
- nvl(mclacd.layer_cost,0)) * layer_quantity ),
mclacd.payback_variance_amount = decode(l_txn_type_id,68,
((nvl(mclacd.actual_cost,0) + l_ovhd_cost
- nvl(mclacd.layer_cost,0)) * layer_quantity),0)
where mclacd.transaction_id = i_txn_id
and mclacd.organization_id = i_org_id
and mclacd.layer_id = i_layer_id
and mclacd.inv_layer_id = i_inv_layer_id
and mclacd.level_type = 1
and mclacd.cost_element_id = 2;
insert into mtl_cst_layer_act_cost_details(
transaction_id,
organization_id,
inventory_item_id,
cost_element_id,
level_type,
layer_id,
inv_layer_id,
layer_quantity,
layer_cost,
actual_cost,
variance_amount,
user_entered,
payback_variance_amount,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
values (
i_txn_id,
i_org_id,
i_item_id,
2,
1,
i_layer_id,
i_inv_layer_id,
decode(sign(i_txn_qty),-1,-1*i_layer_qty,i_layer_qty),
decode(sign(i_txn_qty),-1,0,l_ovhd_cost), /* layer_cost */
l_ovhd_cost, /* actual_cost */
decode(sign(i_txn_qty),-1,(-1*l_ovhd_cost*i_layer_qty),0), /* variance_amount */
'N', /* user_entered */
0, /* payback_variance_amount */
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'movh.insertign mclacd...l_ovhd_cost = '
|| to_char(l_ovhd_cost)
|| ',txn_lyr = '
|| to_char(i_layer_qty)
|| ' , stmt '
|| to_char(l_stmt_num));
** . insert into MCACD with MCLACD's summarized costs **
** . update CLCD with CILCD's summarized costs **
** . update CQL's costs from CLCD **
** . update CICD's costs from CLCD **
** . update CIC's costs from CICD **
** This procedure assumes that all MCLACD rows have already been inserted by **
** calling program. **
** Set I_NO_UPDATE_MMT = 1 if the calling program does not want mmt to be **
** update; otherwise, set it to 0 **
** Set I_NO_UPDATE_QTY = 1 if clcd, cql, cic and cicd should not be updated; **
I_NO_UPDATE_MMT IN NUMBER,
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,
O_Err_Num OUT NOCOPY NUMBER,
O_Err_Code OUT NOCOPY VARCHAR2,
O_Err_Msg OUT NOCOPY VARCHAR2
) IS
l_txfr_txn_id NUMBER;
l_mandatory_update NUMBER;
** For each cost element/level type, one row of MCACD is inserted, **
** aggregating inventory layer(s) cost. The actual cost populated **
** in MCACD is the weighted average cost of all inventory layers **
** associated to the transaction. The variance amount is the sum **
** of those layers' amounts. **
** **
** Note: Unlike the Average Costing processor which uses the **
** insertion flag to signal clcd insert, the layer cost processor **
** uses CILCD for CLCD insert and not MCACD. Therefore, insertion **
** flag will always be set to 'N'. **
** **
********************************************************************/
-- get transaction type. It will be needed to identify payback transaction
-- and calculate payback variance.
l_stmt_num := 5;
select transaction_type_id
into l_txn_type_id
from mtl_material_transactions
where transaction_id = i_txn_id;
select count(*)
into l_count
from mtl_cst_layer_act_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id;
/* Insert MCACD (by summing up MCLACD) only if it's not a scrap txn.
Beware: there will be time where MCACD exists, such as when cost hook is used.
In such case, update MCACD with variance amounts.
*/
if (i_txn_action_id <> 30) then
l_stmt_num := 10;
update mtl_cst_actual_cost_details mcacd
set (prior_cost,
new_cost,
variance_amount,
payback_variance_amount,
onhand_variance_amount) =
(select
0, -- prior cost
NULL, -- new cost
NVL(sum(mclacd.variance_amount),0),
NVL(sum(mclacd.payback_variance_amount)/abs(i_txn_qty),0), -- bugfix 1393484
NVL(sum(mclacd.onhand_variance_amount),0)
from mtl_cst_layer_act_cost_details mclacd
where mclacd.transaction_id = i_txn_id
and mclacd.organization_id = i_org_id
and mclacd.layer_id = i_layer_id
and mclacd.cost_element_id = mcacd.cost_element_id
and mclacd.level_type = mcacd.level_type
group by mclacd.cost_element_id, mclacd.level_type)
where mcacd.transaction_id = i_txn_id
and mcacd.organization_id = i_org_id
and mcacd.layer_id = i_layer_id
and mcacd.transaction_action_id = i_txn_action_id;
insert into mtl_cst_actual_cost_details (
transaction_id,
organization_id,
layer_id,
cost_element_id,
level_type,
transaction_action_id,
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,
new_cost,
insertion_flag,
variance_amount,
user_entered,
payback_variance_amount,
onhand_variance_amount)
select i_txn_id,
i_org_id,
i_layer_id,
mclacd.cost_element_id,
mclacd.level_type,
i_txn_action_id,
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_qty,
0,
NVL((sum(mclacd.actual_cost)),0), -- modified for bug#3835412 -- NVL(abs(sum(mclacd.actual_cost)),0),
NVL((sum(mclacd.actual_cost * abs(mclacd.layer_quantity)) / abs(i_txn_qty)),0)), -- modified for bug#3835412 -- NVL(abs(sum(mclacd.actual_cost * mclacd.layer_quantity) / i_txn_qty),0)),
0, -- prior cost
NULL, -- new cost
'N', -- insertion flag
NVL(sum(mclacd.variance_amount),0),
'N',
NVL(sum(mclacd.payback_variance_amount)/abs(i_txn_qty),0), -- bugfix 1393484
NVL(sum(mclacd.onhand_variance_amount),0)
from mtl_cst_layer_act_cost_details mclacd
where mclacd.transaction_id = i_txn_id
and mclacd.organization_id = i_org_id
and mclacd.layer_id = i_layer_id
and not exists
(select 'MCACD does not exist'
from mtl_cst_actual_cost_details mcacd
where mcacd.transaction_id = i_txn_id
and mcacd.organization_id = i_org_id
and mcacd.layer_id = i_layer_id
and mcacd.cost_element_id = mclacd.cost_element_id
and mcacd.level_type = mclacd.level_type)
group by mclacd.cost_element_id, mclacd.level_type;
update mtl_cst_actual_cost_details mcacd
set prior_cost =
(select clcd.item_cost
from cst_layer_cost_details clcd
where clcd.layer_id = i_layer_id
and clcd.cost_element_id = mcacd.cost_element_id
and clcd.level_type = mcacd.level_type)
where mcacd.transaction_id = i_txn_id
and mcacd.organization_id = i_org_id
and mcacd.layer_id = i_layer_id
and mcacd.transaction_action_id = i_txn_action_id
and exists
(select 'there is details in clcd'
from cst_layer_cost_details clcd
where clcd.layer_id = i_layer_id
and clcd.cost_element_id = mcacd.cost_element_id
and clcd.level_type = mcacd.level_type);
INSERT
INTO mtl_cst_actual_cost_details (
transaction_id,
organization_id,
layer_id,
cost_element_id,
level_type,
transaction_action_id,
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,
new_cost,
insertion_flag,
variance_amount,
user_entered
)
SELECT i_txn_id,
i_org_id,
i_layer_id,
CLCD.cost_element_id,
CLCD.level_type,
i_txn_action_id,
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,
CLCD.item_cost,
NULL,
'N',
0,
'N'
FROM cst_layer_cost_details CLCD
WHERE layer_id = i_layer_id
AND NOT EXISTS(
SELECT 'this detail is not in MCACD already'
FROM mtl_cst_actual_cost_details MCACD
WHERE MCACD.transaction_id = i_txn_id
AND MCACD.organization_id = i_org_id
AND MCACD.layer_id = i_layer_id
AND MCACD.cost_element_id = CLCD.cost_element_id
AND MCACD.level_type = CLCD.level_type);
** Update cst_layer_cost_details if i_no_update_qty is not set. **
** Since CQL quantity before this transaction is still needed by **
** CSTPAVCP.update_mmt, CQL quantity and cost information will be **
** updated later. **
********************************************************************/
-- get the total layer quantity from cil
select sum(cil.layer_quantity)
into l_total_layer_qty
from cst_inv_layers cil
where cil.layer_id = i_layer_id;
/* Update clcd only if i_no_update_qty flag is not set and the total layer quantity is not zero */
if (i_no_update_qty = 0) and
(l_total_layer_qty <> 0) then
l_stmt_num := 20;
select sum(cil.layer_quantity)
into l_total_layer_qty
from cst_inv_layers cil
where cil.layer_id = i_layer_id;*/
select sum(cql.layer_quantity)
into l_layer_qty
from cst_quantity_layers cql
where cql.layer_id = i_layer_id;
update cst_layer_cost_details clcd
set item_cost =
(select (clcd.item_cost * l_layer_qty
+
nvl(sum((decode(i_txn_action_id, 24,
(nvl(mcl.actual_cost,0) - nvl(mcl.layer_cost,0)),
mcl.actual_cost)
* mcl.layer_quantity)
- decode(i_txn_action_id, 24,
0,nvl(mcl.variance_amount,0))),0)
) / l_total_layer_qty
from mtl_cst_layer_act_cost_details mcl
where mcl.transaction_id = i_txn_id
and mcl.layer_id = clcd.layer_id
and mcl.level_type = clcd.level_type
and mcl.cost_element_id = clcd.cost_element_id)
where clcd.layer_id = i_layer_id
and exists (select 1
from mtl_cst_layer_act_cost_details mclacd2
where mclacd2.transaction_id = i_txn_id
and mclacd2.layer_id = clcd.layer_id
and mclacd2.level_type = clcd.level_type
and mclacd2.cost_element_id = clcd.cost_element_id);
insert into cst_layer_cost_details(
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)
select mclacd.layer_id,
mclacd.cost_element_id,
mclacd.level_type,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
nvl(sum((decode(i_txn_action_id, 24, (nvl(mclacd.actual_cost,0) - nvl(mclacd.layer_cost,0)), nvl(mclacd.actual_cost,0))
* mclacd.layer_quantity
- decode(i_txn_action_id, 24, 0,nvl(mclacd.variance_amount,0))) / l_total_layer_qty),0)
from mtl_cst_layer_act_cost_details mclacd
where mclacd.transaction_id = i_txn_id
and mclacd.layer_id = i_layer_id
and not exists (select 1
from cst_layer_cost_details clcd
where clcd.layer_id = mclacd.layer_id
and clcd.level_type = mclacd.level_type
and clcd.cost_element_id = mclacd.cost_element_id)
GROUP BY
mclacd.layer_id,
mclacd.cost_element_id,
mclacd.level_type;
delete from cst_layer_cost_details
where layer_id = i_layer_id;
insert into cst_layer_cost_details(
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)
select cilcd.layer_id,
cilcd.cost_element_id,
cilcd.level_type,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
(sum((cilcd.layer_cost*cil.layer_quantity)/l_total_layer_qty)) -- modified for bug#3835412
from cst_inv_layer_cost_details cilcd,
cst_inv_layers cil
where cil.layer_id = i_layer_id*/
/*commented for bug 15979260
and cil.organization_id = i_org_id
and cil.inventory_item_id = i_item_id*/
/*and cil.inv_layer_id = cilcd.inv_layer_id
group by cilcd.layer_id,cost_element_id, level_type; */
** Update MCACD with new cost **
********************************************************************/
l_stmt_num := 35;
update mtl_cst_actual_cost_details mcacd
set new_cost =
(select clcd.item_cost
from cst_layer_cost_details clcd
where clcd.layer_id = i_layer_id
and clcd.cost_element_id = mcacd.cost_element_id
and clcd.level_type = mcacd.level_type)
where mcacd.organization_id = i_org_id
and mcacd.transaction_id = i_txn_id
and mcacd.layer_id = i_layer_id
and mcacd.transaction_action_id = i_txn_action_id;
** Update Mtl_Material_Transactions **
** Need to update prior_costed_quantity now. **
********************************************************************/
l_stmt_num := 40;
if (i_no_update_mmt = 0) then
-- subinventory transfer for receipt side, we need to pass
-- txfr_txn_id to update proper transaction in MMT.
if (i_txn_action_id = 2 and i_txn_qty > 0) then
select transfer_transaction_id
into l_txfr_txn_id
from mtl_material_transactions
where transaction_id = i_txn_id;
CSTPAVCP.update_mmt(
i_org_id,
i_txn_id,
l_txfr_txn_id,
i_layer_id,
0,
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 layer quantity and layer costs information **
** (cst_quantity_layers) **
********************************************************************/
if i_no_update_qty = 0 then
l_stmt_num := 45;
update cst_quantity_layers cql
set (last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
layer_quantity,
update_transaction_id,
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,
unburdened_cost,
burden_cost) =
(select
i_user_id,
sysdate,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
l_total_layer_qty,
i_txn_id,
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,
unburdened_cost,
burden_cost
from cst_quantity_layers_v v
where v.layer_id = i_layer_id)
where cql.layer_id = i_layer_id
and exists
(select 'there is detail cost'
from cst_layer_cost_details clcd
where clcd.layer_id = i_layer_id);
** Update Item Cost and Item Cost Details **
********************************************************************/
-- Determine the value of mandatory_update_flag.
-- If project is not enabled, set the l_mandatory_update flag.
-- This flag is passed to update_item_cost() routine. In that
-- routine, if this flag is set to 1, the item_cost will be
-- copied from clcd to cicd evenif the quantity <= 0.
-- Otherwise, it will return immediately if the quantity <= 0.
-- For quantity > 0, this flag is ignored, and the weighted avg
-- of cost in clcd (accross different cost group) will be put
-- into cicd.
l_stmt_num := 50;
/* select nvl(project_reference_enabled,0)
into l_proj_enabled
from mtl_parameters
where organization_id = i_org_id;
l_mandatory_update := 1;
l_mandatory_update := 0;
l_mandatory_update := 0;
CSTPAVCP.update_item_cost(
i_org_id,
i_txn_id,
i_layer_id,
i_cost_type,
i_item_id,
l_mandatory_update,
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);
** layer_cost_update **
** **
** DESCRIPTION **
** This function is called to update inventory layer cost. **
** It will determine the new elemental costs of the layer based **
** on user-enter values and compute the adjustment amounts to **
** inventory valuation. **
** MTL_CST_LAYER_ACT_COST_DETAILS will be populated and the other **
** cost tables (CILCD, CIL, CLCD, CQL, CICD, CIC) will be updated **
** accordingly with the new cost information. **
** This function is duplicated from CSTPAVCP.average_cost_update. **
** **
** HISTORY **
** 12-MAY-2000 Dieu-Thuong Le Creation **
** **
************************************************************************/
procedure layer_cost_update(
I_ORG_ID IN NUMBER,
I_TXN_ID IN NUMBER,
I_LAYER_ID IN NUMBER,
I_COST_TYPE IN NUMBER,
I_ITEM_ID IN NUMBER,
I_TXN_QTY IN NUMBER,
I_TXN_ACT_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_neg_cost NUMBER;
l_mandatory_update NUMBER;
select transaction_source_id
into l_inv_layer_id
from mtl_material_transactions
where transaction_id = I_TXN_ID;
select count(*)
into l_mctcd_exist
from mtl_cst_txn_cost_details ctcd
where ctcd.transaction_id = i_txn_id;
** Insert records into mtl_cst_layer_act_cost_details. **
*********************************************************/
insert into mtl_cst_layer_act_cost_details (
transaction_id,
organization_id,
layer_id,
inv_layer_id,
layer_quantity,
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,
layer_cost,
variance_amount,
user_entered,
onhand_variance_amount)
select
i_txn_id,
i_org_id,
i_layer_id,
l_inv_layer_id,
cil.layer_quantity,
ctcd.cost_element_id,
ctcd.level_type,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
ctcd.inventory_item_id,
decode(ctcd.new_average_cost,NULL, -- actual cost
decode(ctcd.percentage_change,NULL,
/* value change formula */
decode(sign(cil.layer_quantity),1,
decode(sign(i_txn_qty),1,
decode( sign(cil.layer_quantity-i_txn_qty),-1,
decode(sign(nvl(cilcd.layer_cost,0) * cil.layer_quantity + (ctcd.value_change/i_txn_qty*cil.layer_quantity)),-1,
0,
(nvl(cilcd.layer_cost,0)*nvl(cil.layer_quantity,0) +
(ctcd.value_change/i_txn_qty*cil.layer_quantity))/nvl(cil.layer_quantity,-1)),
decode(sign(nvl(cilcd.layer_cost,0) * cil.layer_quantity + ctcd.value_change),-1,
0,
(nvl(cilcd.layer_cost,0)*nvl(cil.layer_quantity,0) +
ctcd.value_change)/nvl(cil.layer_quantity,-1))
),
decode(sign(nvl(cilcd.layer_cost,0) * cil.layer_quantity + ctcd.value_change),-1,
0,
(nvl(cilcd.layer_cost,0)*nvl(cil.layer_quantity,0) +
ctcd.value_change)/nvl(cil.layer_quantity,-1))),
nvl(cilcd.layer_cost,0)),
/* percentage change formula */
nvl(cilcd.layer_cost,0)*(1+ctcd.percentage_change/100)),
/* new average cost formula */
ctcd.new_average_cost),
nvl(cilcd.layer_cost,0), -- layer cost
decode(ctcd.value_change,NULL,
0,
decode(sign(cil.layer_quantity),1,
decode(sign(i_txn_qty),1,
decode(sign(cil.layer_quantity-i_txn_qty),-1,
decode(sign(nvl(cilcd.layer_cost,0) * cil.layer_quantity + (ctcd.value_change/i_txn_qty*cil.layer_quantity)),-1,
(ctcd.value_change/i_txn_qty*cil.layer_quantity) + nvl(cilcd.layer_cost,0) * cil.layer_quantity,
0),
decode(sign(nvl(cilcd.layer_cost,0) * cil.layer_quantity + ctcd.value_change),-1,
ctcd.value_change + nvl(cilcd.layer_cost,0) * cil.layer_quantity,
0)),
decode(sign(nvl(cilcd.layer_cost,0) * cil.layer_quantity + ctcd.value_change),-1,
ctcd.value_change + nvl(cilcd.layer_cost,0) * cil.layer_quantity,
0)),
ctcd.value_change)),
'N', -- user entered
/*LCM*/
decode(ctcd.value_change,NULL,
0,
decode(sign(i_txn_qty),1,
decode(sign(cil.layer_quantity),1,
decode(sign(cil.layer_quantity-i_txn_qty),-1,
ctcd.value_change*(1-cil.layer_quantity/i_txn_qty),
0
),
0
),
0
)
)
FROM mtl_cst_txn_cost_details ctcd,
cst_inv_layers cil,
cst_inv_layer_cost_details cilcd
WHERE ctcd.transaction_id = i_txn_id
AND ctcd.organization_id = i_org_id
AND cil.layer_id = i_layer_id
AND cil.inv_layer_id = l_inv_layer_id
AND cil.inventory_item_id = ctcd.inventory_item_id
AND cil.organization_id = ctcd.organization_id
AND cilcd.inv_layer_id (+) = l_inv_layer_id
AND cilcd.cost_element_id (+) = ctcd.cost_element_id
AND cilcd.level_type (+) = ctcd.level_type;
/* select count(*)
into l_neg_cost
from mtl_cst_layer_act_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = i_layer_id
and inv_layer_id = l_inv_layer_id
and actual_cost < 0;
** Delete from cst_inv_layer_cost_details and insert the new rows **
** from mtl_cst_actual_cost_details. **
***********************************************************************/
l_stmt_num := 30;
Delete from cst_inv_layer_cost_details
where layer_id = i_layer_id
and inv_layer_id = l_inv_layer_id;
Insert into cst_inv_layer_cost_details (
layer_id,
inv_layer_id,
cost_element_id,
level_type,
layer_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
select i_layer_id,
l_inv_layer_id,
mclacd.cost_element_id,
mclacd.level_type,
mclacd.actual_cost,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate
from mtl_cst_layer_act_cost_details mclacd
where mclacd.transaction_id = i_txn_id
and mclacd.organization_id = i_org_id
and mclacd.layer_id = i_layer_id
and mclacd.inv_layer_id = l_inv_layer_id;
** Update cst_inv_layers **
********************************************************************/
l_stmt_num := 50;
update cst_inv_layers cil
set (last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
layer_cost)=
(select
i_user_id,
sysdate,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
nvl(sum(layer_cost),0)
from cst_inv_layer_cost_details cilcd
where cilcd.layer_id = i_layer_id
and cilcd.inv_layer_id = l_inv_layer_id)
where cil.layer_id = i_layer_id
and cil.inv_layer_id = l_inv_layer_id
and exists
(select 'there is detail cost'
from cst_inv_layer_cost_details cilcd
where cilcd.layer_id = i_layer_id
and cilcd.inv_layer_id = l_inv_layer_id);
** Update mcacd, clcd, cql, cic, cicd and mmt **
*******************************************************/
l_stmt_num := 60;
select cil.layer_quantity
into l_layer_qty
from cst_inv_layers cil
where cil.layer_id = i_layer_id
and cil.inv_layer_id = l_inv_layer_id;
0, -- i_no_update_mmt
0, -- 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);
/* Update MMT.quantity_adjusted with update transaction quantity. */
update mtl_material_transactions mmt
set last_update_date = sysdate,
last_updated_by = i_user_id,
last_update_login = i_login_id,
program_application_id = i_prg_appl_id,
program_id = i_prg_id,
program_update_date = sysdate,
quantity_adjusted = l_layer_qty
where mmt.transaction_id = i_txn_id;
o_err_msg := 'CSTPLENG.layer_cost_update (' || to_char(l_stmt_num) ||
'): '
|| substr(SQLERRM, 1,200);
END layer_cost_update;
select primary_cost_method
into l_cost_method
from mtl_parameters
where organization_id = I_ORG_ID;
select nvl(min(inv_layer_id),0)
into l_inv_layer_id
from cst_inv_layers
where layer_id = i_layer_id
and layer_quantity > 0;
select nvl(max(inv_layer_id),0)
into l_inv_layer_id
from cst_inv_layers
where layer_id = i_layer_id;
select nvl(max(inv_layer_id), 0)
into l_inv_layer_id
from cst_inv_layers
where layer_id = i_layer_id
and layer_quantity > 0;
select nvl(min(inv_layer_id),0)
into l_inv_layer_id
from cst_inv_layers
where layer_id = i_layer_id;
select cst_inv_layers_s.nextval
into l_inv_layer_id
from dual;
insert into cst_inv_layers (
create_transaction_id,
layer_id,
inv_layer_id,
organization_id,
inventory_item_id,
creation_quantity,
layer_quantity,
layer_cost,
transaction_source_type_id,
transaction_source_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
select
i_txn_id,
i_layer_id,
l_inv_layer_id,
i_org_id,
i_item_id,
0,
0,
0,
i_txn_src_type_id,
i_txn_src_id,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate
from dual;
insert into cst_inv_layer_cost_details (
layer_id,
inv_layer_id,
cost_element_id,
level_type,
layer_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
select
i_layer_id,
l_inv_layer_id,
1,
1,
0,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate
from dual;
SELECT count(*)
INTO l_num_detail
FROM MTL_TXN_COST_DET_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id;
* In this case, call cstpacit.cost_det_new_insert.
*/
if (l_num_detail = 0) then
cstpleng.layer_cost_det_new_insert(i_txn_id, i_txn_action_id, i_org_id,
i_item_id, i_cost_group_id, i_inv_layer_id, i_txn_cost,
i_new_avg_cost, i_per_change, i_val_change,
i_mat_accnt, i_mat_ovhd_accnt, i_res_accnt,
i_osp_accnt, i_ovhd_accnt,
i_user_id, i_login_id, i_request_id,
i_prog_appl_id, i_prog_id,
l_err_num, l_err_code, l_err_msg);
INSERT INTO MTL_CST_TXN_COST_DETAILS (
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
VALUE_CHANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
SELECT
i_txn_id,
i_org_id,
i_item_id,
COST_ELEMENT_ID,
LEVEL_TYPE,
LAYER_COST,
LAYER_COST,
NULL,
NULL,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate
FROM CST_INV_LAYER_COST_DETAILS CILCD
WHERE CILCD.LAYER_ID = l_layer_id
AND CILCD.INV_LAYER_ID = i_inv_layer_id;
UPDATE MTL_CST_TXN_COST_DETAILS mctcd
set (VALUE_CHANGE,
PERCENTAGE_CHANGE,
NEW_AVERAGE_COST)
=
(select
mtcdi.VALUE_CHANGE,
mtcdi.PERCENTAGE_CHANGE,
mtcdi.NEW_AVERAGE_COST
from MTL_TXN_COST_DET_INTERFACE mtcdi
where mtcdi.TRANSACTION_INTERFACE_ID = i_txn_interface_id
and mctcd.transaction_id = i_txn_id
and mtcdi.level_type = mctcd.level_type
and mtcdi.cost_element_id = mctcd.cost_element_id
)
where
mctcd.transaction_id = i_txn_id
and exists (select 1
from MTL_TXN_COST_DET_INTERFACE mtcdi
where mtcdi.TRANSACTION_INTERFACE_ID = i_txn_interface_id
and mtcdi.level_type = mctcd.level_type
and mtcdi.cost_element_id = mctcd.cost_element_id);
INSERT INTO MTL_CST_TXN_COST_DETAILS (
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
VALUE_CHANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
values (
i_txn_id,
i_org_id,
i_item_id,
1, /* Hard coded to This level Material */
1,
i_txn_cost,
i_new_avg_cost,
i_per_change,
i_val_change,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate);
procedure layer_cost_det_new_insert (
i_txn_id in number,
i_txn_action_id in number,
i_org_id in number,
i_item_id in number,
i_cost_group_id in number,
i_inv_layer_id in number,
i_txn_cost in number,
i_new_avg_cost in number,
i_per_change in number,
i_val_change in number,
i_mat_accnt in number,
i_mat_ovhd_accnt in number,
i_res_accnt in number,
i_osp_accnt in number,
i_ovhd_accnt in number,
i_user_id in number,
i_login_id in number,
i_request_id in number,
i_prog_appl_id in number,
i_prog_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;
cost_det_new_insert_error EXCEPTION;
SELECT CILCD.COST_ELEMENT_ID
FROM CST_INV_LAYERS CIL,
CST_INV_LAYER_COST_DETAILS CILCD
WHERE CIL.LAYER_ID = l_layer_id
AND CIL.INV_LAYER_ID = i_inv_layer_id
AND CILCD.LAYER_ID = l_layer_id
AND CILCD.INV_LAYER_ID = i_inv_layer_id;
raise cost_det_new_insert_error;
* insert each elements into MTL_CST_TXN_COST_DETAILS.
*/
if (l_layer_id <> 0) then
if (i_txn_action_id = 24) then
-- checking the existence of accounts for layer cost update case
open cost_elmt_ids;
raise cost_det_new_insert_error;
SELECT LAYER_COST
INTO cil_layer_cost
FROM CST_INV_LAYERS
WHERE LAYER_ID = l_layer_id
AND INV_LAYER_ID = i_inv_layer_id;
SELECT count(COST_ELEMENT_ID)
INTO cost_element_count
FROM CST_INV_LAYER_COST_DETAILS
WHERE LAYER_ID = l_layer_id
AND INV_LAYER_ID = i_inv_layer_id;
INSERT INTO MTL_CST_TXN_COST_DETAILS (
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
VALUE_CHANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
SELECT
i_txn_id,
i_org_id,
i_item_id,
CILCD.COST_ELEMENT_ID,
CILCD.LEVEL_TYPE,
DECODE(CIL.LAYER_COST, 0, i_txn_cost / cost_element_count,
i_txn_cost * CILCD.LAYER_COST / CIL.LAYER_COST),
DECODE(CIL.LAYER_COST, 0, i_new_avg_cost / cost_element_count,
i_new_avg_cost * CILCD.LAYER_COST / CIL.LAYER_COST),
i_per_change,
DECODE(CIL.LAYER_COST, 0, i_val_change / cost_element_count,
i_val_change * CILCD.LAYER_COST / CIL.LAYER_COST),
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate
FROM CST_INV_LAYERS CIL, CST_INV_LAYER_COST_DETAILS CILCD
WHERE CIL.LAYER_ID = l_layer_id
AND CIL.INV_LAYER_ID = i_inv_layer_id
AND CILCD.LAYER_ID = l_layer_id
AND CILCD.INV_LAYER_ID = i_inv_layer_id;
/* If layer detail does not exist, then insert a new row
* as a this level material.
*/
else
if (i_txn_action_id = 24 and i_mat_accnt is null) then
-- Error occured only for layer cost update
FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_ACCOUNT');
raise cost_det_new_insert_error;
INSERT INTO MTL_CST_TXN_COST_DETAILS (
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
VALUE_CHANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
values (
i_txn_id,
i_org_id,
i_item_id,
1, /* Hard coded to This level Material */
1,
i_txn_cost,
i_new_avg_cost,
i_per_change,
i_val_change,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate);
when cost_det_new_insert_error then
o_err_num := l_err_num;
o_err_msg := 'CSTPLENG.LAYER_COST_DET_NEW_INSERT:' || l_err_msg;
o_err_msg := 'CSTPLENG.LAYER_COST_DET_NEW_INSERT:' || substr(SQLERRM,1,150);
end layer_cost_det_new_insert;
procedure layer_cost_update_dist(
I_ORG_ID IN NUMBER,
I_TXN_ID IN NUMBER,
I_LAYER_ID IN NUMBER,
I_EXP_ITEM 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_Error_Num OUT NOCOPY NUMBER,
O_Error_Code OUT NOCOPY VARCHAR2,
O_Error_Message OUT NOCOPY VARCHAR2
)IS
l_hook NUMBER;
select
inventory_item_id, organization_id,
nvl(cost_group_id,1),
transaction_date,
primary_quantity, subinventory_code,
quantity_adjusted,
nvl(transaction_source_id,-1),
nvl(distribution_account_id,-1),
nvl(material_account, -1), nvl(material_overhead_account, -1),
nvl(resource_account, -1), nvl(outside_processing_account, -1),
nvl(overhead_account, -1),
nvl(encumbrance_account, -1), nvl(encumbrance_amount, 0),
currency_code,
nvl(currency_conversion_date,transaction_date),
nvl(currency_conversion_rate,-1) , currency_conversion_type,
nvl(expense_account_id,-1)
into
l_item_id,
l_txn_org_id,
l_cost_grp_id,
l_txn_date,
l_p_qty,
l_subinv,
l_qty_adj,
l_txn_src_id,
l_dist_acct,
l_mat_acct,
l_mat_ovhd_acct,
l_res_acct,
l_osp_acct,
l_ovhd_acct,
l_enc_acct,
l_enc_amount,
l_alt_curr,
l_conv_date,
l_conv_rate,
l_conv_type,
l_onhand_var_acct
from mtl_material_transactions
where transaction_id = i_txn_id;
select decode(encumbrance_reversal_flag,1,1,2,0,0)
into l_enc_rev
from mtl_parameters
where organization_id = i_org_id;
select ledger_id
into l_sob_id
from cst_acct_info_v
where organization_id = i_org_id;
select currency_code
into l_pri_curr
from gl_sets_of_books
where set_of_books_id = l_sob_id;
SELECT
nvl(material_account,-1),
nvl(material_overhead_account,-1),
nvl(resource_account,-1),
nvl(outside_processing_account,-1),
nvl(overhead_account,-1),
nvl(average_cost_var_account,-1)
INTO
l_inv_mat_acct,
l_inv_mat_ovhd_acct,
l_inv_res_acct,
l_inv_osp_acct,
l_inv_ovhd_acct,
l_avg_cost_var_acct
FROM
CST_COST_GROUP_ACCOUNTS
WHERE
ORGANIZATION_ID = i_org_id
AND COST_GROUP_ID = l_cost_grp_id;
SELECT
nvl(MATERIAL_ACCOUNT, -1),
nvl(MATERIAL_OVERHEAD_ACCOUNT, -1),
nvl(RESOURCE_ACCOUNT, -1),
nvl(OVERHEAD_ACCOUNT, -1),
nvl(OUTSIDE_PROCESSING_ACCOUNT, -1),
nvl(AVERAGE_COST_VAR_ACCOUNT, -1)
INTO
l_inv_mat_acct,
l_inv_mat_ovhd_acct,
l_inv_res_acct,
l_inv_ovhd_acct,
l_inv_osp_acct,
l_avg_cost_var_acct
FROM
MTL_PARAMETERS
WHERE
ORGANIZATION_ID = i_org_id;
select count(*)
into l_ele_exist
from mtl_cst_actual_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id;
select (sum(actual_cost) - sum(layer_cost)),sum(variance_amount),
sum(onhand_variance_amount)
into l_cost,l_var,l_onhand_var
from mtl_cst_layer_act_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id
and cost_element_id = cost_element;
select decode(cost_element, 1, l_inv_mat_acct,
2, l_inv_mat_ovhd_acct,
3, l_inv_res_acct,
4, l_inv_osp_acct,
5, l_inv_ovhd_acct)
into l_acct
from dual;
CSTPACDP.insert_account(i_org_id, i_txn_id, l_item_id, l_qty_adj * l_cost,
sign(l_qty_adj * l_cost) * abs(l_qty_adj)/*modified for bug #4005770*/ /*l_qty_adj*/, l_acct, l_sob_id, 1,
cost_element, NULL,
l_txn_date, l_txn_src_id, 15,
l_pri_curr, l_alt_curr, l_conv_date, l_conv_rate, l_conv_type,
1,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);
/* Added the decode for BUG: 1107767. Avg cost update through the interface needs all the accounts
in MMT to be specified, even if only the material cost element is getting affected */
if (l_acct = -1) then
select decode(cost_element, 1, l_mat_acct,
2, decode(l_mat_ovhd_acct,-1, l_mat_acct, l_mat_ovhd_acct),
3, decode(l_res_acct,-1, l_mat_acct, l_res_acct),
4, decode(l_osp_acct,-1, l_mat_acct, l_osp_acct),
5, decode(l_ovhd_acct,-1, l_mat_acct, l_ovhd_acct))
into l_acct
from dual;
CSTPACDP.insert_account(i_org_id, i_txn_id, l_item_id, (l_qty_adj * l_cost) - l_var - l_onhand_var,
l_qty_adj, l_acct, l_sob_id, 2,
cost_element, NULL,
l_txn_date, l_txn_src_id, 15,
l_pri_curr, l_alt_curr, l_conv_date, l_conv_rate, l_conv_type,
1,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);
CSTPACDP.insert_account(i_org_id, i_txn_id, l_item_id, l_onhand_var,
l_qty_adj, l_acct, l_sob_id, 20,
cost_element, NULL,
l_txn_date, l_txn_src_id, 15,
l_pri_curr, l_alt_curr, l_conv_date, l_conv_rate, l_conv_type,
1,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);
select nvl(sum(variance_amount),0)
into l_var
from mtl_cst_actual_cost_details cacd
where transaction_id = i_txn_id
and organization_id = i_org_id;
CSTPACDP.insert_account(i_org_id, i_txn_id, l_item_id, l_var,
l_qty_adj, l_acct, l_sob_id, 13,
NULL, NULL,
l_txn_date, l_txn_src_id, 15,
l_pri_curr, l_alt_curr, l_conv_date, l_conv_rate, l_conv_type,
1,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 mtl_cst_actual_cost_details
SET transaction_costed_date = sysdate
WHERE transaction_id = i_txn_id
AND transaction_costed_date IS NULL;
O_error_message := 'CSTPLENG.layer_cost_update_dist' || to_char(l_stmt_num) ||
substr(SQLERRM,1,180);
END layer_cost_update_dist;
PROCEDURE update_inv_layer_cost (i_org_id IN NUMBER,
i_item_id IN NUMBER,
i_userid IN NUMBER,
i_login_id IN NUMBER)
IS
Begin
update cst_inv_layers
set last_updated_by = i_userid,
last_update_date = sysdate,
last_update_login = i_login_id,
layer_cost = 0,
burden_cost = 0,
unburdened_cost = 0
where organization_id = i_org_id
and inventory_item_id = i_item_id;
delete from cst_inv_layer_cost_details
where inv_layer_id IN (select inv_layer_id
from cst_inv_layers
where organization_id = i_org_id
and inventory_item_id = i_item_id);
End update_inv_layer_cost;