[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_no_update_mmt NUMBER;
l_no_update_mmt := 0;
SELECT MOD(SUM(DECODE(mp.process_enabled_flag, 'Y', 1, 2)), 2)
INTO l_pd_txfr_ind
FROM mtl_parameters mp, mtl_material_transactions mmt
WHERE mmt.transaction_id = i_txn_id
AND (mmt.organization_id = mp.organization_id
OR mmt.transfer_organization_id = mp.organization_id);
l_no_update_mmt,
l_exp_flag,
l_err_num,
l_err_code,
l_err_msg);
l_no_update_mmt,
l_exp_flag,
l_hook,
l_err_num,
l_err_code,
l_err_msg);
fnd_file.put_line(fnd_file.log,'>>Average Cost update');
average_cost_update(i_org_id,
i_txn_id,
i_layer_id,
i_cost_type,
i_item_id,
i_txn_action_id,
i_txn_qty,/*LCM*/
l_exp_flag,
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 transaction_type_id
into l_txn_type_id
from mtl_material_transactions
where transaction_id = i_txn_id;
SELECT MIN(OOLA.reference_line_id)
INTO l_so_line_id
FROM mtl_material_transactions MMT,
oe_order_lines_all OOLA
WHERE MMT.transaction_id = i_txn_id
AND OOLA.line_id = MMT.trx_source_line_id;
INSERT INTO mtl_cst_txn_cost_details (
transaction_id,
organization_id,
inventory_item_id,
cost_element_id,
level_type,
transaction_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_txn_id,
i_org_id,
i_item_id,
MCACD.cost_element_id,
MCACD.level_type,
SUM(MMT.primary_quantity*MCACD.actual_cost)/SUM(MMT.primary_quantity),
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
SYSDATE
FROM oe_order_lines_all OOLA, /*BUG 5768680 Changes introduced to improve performance*/
oe_order_headers_all OOHA, /* of the layer cost worker*/
mtl_sales_orders MSO,
mtl_material_transactions MMT,
mtl_cst_actual_cost_details MCACD,
cst_cogs_events cce
WHERE OOLA.line_id = l_so_line_id
AND OOHA.header_id = OOLA.header_id
AND MSO.segment1 = TO_CHAR(OOHA.order_number) -- extraneous MSOs are possible
AND MMT.trx_source_line_id = l_so_line_id -- filter MMTs corresponding to extraneous MSOs
AND OOLA.line_id = cce.cogs_om_line_id
AND cce.event_type = 1
AND mmt.transaction_id = cce.mmt_transaction_id
AND MMT.transaction_source_id = MSO.sales_order_id
AND MMT.transaction_action_id IN (1,7)
AND MMT.transaction_source_type_id = 2
AND MMT.inventory_item_id = i_item_id
AND MCACD.transaction_id = MMT.transaction_id
AND EXISTS (SELECT NULL
FROM cst_acct_info_v v1,cst_acct_info_v v2
WHERE v1.organization_id = MMT.organization_id
AND v2.organization_id = i_org_id
AND v1.ledger_id = v2.ledger_id)
GROUP
BY MCACD.cost_element_id,
MCACD.level_type;
INSERT
INTO mtl_cst_txn_cost_details (
transaction_id,
organization_id,
inventory_item_id,
cost_element_id,
level_type,
transaction_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_txn_id,
i_org_id,
i_item_id,
MCACD.cost_element_id,
MCACD.level_type,
SUM(MMT.primary_quantity*MCACD.actual_cost)/SUM(MMT.primary_quantity),
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
SYSDATE
FROM oe_order_lines_all OOLA, /*BUG 5768680 Changes introduced to improve performance*/
oe_order_headers_all OOHA, /* of the layer cost worker*/
mtl_sales_orders MSO,
mtl_material_transactions MMT,
mtl_cst_actual_cost_details MCACD
WHERE OOLA.line_id = l_so_line_id
AND OOHA.header_id = OOLA.header_id
AND MSO.segment1 = TO_CHAR(OOHA.order_number) -- extraneous MSOs are possible
AND MMT.transaction_source_id = MSO.sales_order_id
AND MMT.trx_source_line_id = l_so_line_id -- filter MMTs corresponding to extraneous MSOs
AND MMT.transaction_action_id in (1,7)
AND MMT.transaction_source_type_id = 2
AND MMT.organization_id = i_org_id
AND MMT.inventory_item_id = i_item_id
AND MCACD.transaction_id = MMT.transaction_id
GROUP
BY MCACD.cost_element_id,
MCACD.level_type;
l_no_update_mmt,
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_mmt,
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);
select transaction_type_id
into l_txn_type_id
from mtl_material_transactions
where transaction_id = i_txn_id;
procedure average_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_ACTION_ID IN NUMBER,
I_TXN_QTY IN NUMBER,/*LCM*/
I_EXP_FLG 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
neg_cost_error EXCEPTION;
l_mandatory_update NUMBER;
fnd_file.put_line(fnd_file.log,'Average Cost Update <<<');
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,
onhand_variance_amount)
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,
decode(ctcd.new_average_cost,NULL,
decode(ctcd.percentage_change,NULL,
/* value change formula */
decode(sign(cql.layer_quantity),1,
decode(sign(i_txn_qty),1,
decode(sign(cql.layer_quantity-i_txn_qty),-1,
decode(sign(nvl(clcd.item_cost,0) * cql.layer_quantity +
(ctcd.value_change/i_txn_qty*cql.layer_quantity)),-1,
0,
(nvl(clcd.item_cost,0)*nvl(cql.layer_quantity,0) +
(ctcd.value_change/i_txn_qty*cql.layer_quantity))/nvl(cql.layer_quantity,-1)),
decode(sign(nvl(clcd.item_cost,0) * cql.layer_quantity + ctcd.value_change),-1,
0,
(nvl(clcd.item_cost,0)*nvl(cql.layer_quantity,0) +
ctcd.value_change)/nvl(cql.layer_quantity,-1))),
decode(sign(nvl(clcd.item_cost,0) * cql.layer_quantity + ctcd.value_change),-1,
0,
(nvl(clcd.item_cost,0)*nvl(cql.layer_quantity,0) +
ctcd.value_change)/nvl(cql.layer_quantity,-1))),
nvl(clcd.item_cost,0)),
/* percentage change formula */
nvl(clcd.item_cost,0)*(1+ctcd.percentage_change/100)),
/* new average cost formula */
ctcd.new_average_cost),
nvl(clcd.item_cost,0),
decode(ctcd.new_average_cost,NULL,
decode(ctcd.percentage_change,NULL,
/* value change formula */
decode(sign(cql.layer_quantity),1,
decode(sign(i_txn_qty),1,
decode(sign(cql.layer_quantity-i_txn_qty),-1,
decode(sign(nvl(clcd.item_cost,0) * cql.layer_quantity +
(ctcd.value_change/i_txn_qty*cql.layer_quantity)),-1,
0,
(nvl(clcd.item_cost,0)*nvl(cql.layer_quantity,0) +
(ctcd.value_change/i_txn_qty*cql.layer_quantity))/nvl(cql.layer_quantity,-1)),
decode(sign(nvl(clcd.item_cost,0) * cql.layer_quantity + ctcd.value_change),-1,
0,
(nvl(clcd.item_cost,0)*nvl(cql.layer_quantity,0) +
ctcd.value_change)/nvl(cql.layer_quantity,-1))),
decode(sign(nvl(clcd.item_cost,0) * cql.layer_quantity + ctcd.value_change),-1,
0,
(nvl(clcd.item_cost,0)*nvl(cql.layer_quantity,0) +
ctcd.value_change)/nvl(cql.layer_quantity,-1))),
nvl(clcd.item_cost,0)),
/* percentage change formula */
nvl(clcd.item_cost,0)*(1+ctcd.percentage_change/100)),
/* new average cost formula */
ctcd.new_average_cost),
'Y',
decode(ctcd.value_change,NULL,
0,
decode(sign(cql.layer_quantity),1,
decode(sign(i_txn_qty),1,
decode(sign(cql.layer_quantity-i_txn_qty),-1,
decode(sign(nvl(clcd.item_cost,0) * cql.layer_quantity + (ctcd.value_change/i_txn_qty*cql.layer_quantity)),-1,
(ctcd.value_change/i_txn_qty*cql.layer_quantity) + nvl(clcd.item_cost,0) * cql.layer_quantity,
0),
decode(sign(nvl(clcd.item_cost,0) * cql.layer_quantity + ctcd.value_change),-1,
ctcd.value_change + nvl(clcd.item_cost,0) * cql.layer_quantity,
0)),
decode(sign(nvl(clcd.item_cost,0) * cql.layer_quantity + ctcd.value_change),-1,
ctcd.value_change + nvl(clcd.item_cost,0) * cql.layer_quantity,
0)),
ctcd.value_change)),
'N',
/*LCM*/
decode(ctcd.value_change,NULL,
0,
decode(sign(i_txn_qty),1,
decode(sign(cql.layer_quantity),1,
decode(sign(cql.layer_quantity-i_txn_qty),-1,
ctcd.value_change*(1-cql.layer_quantity/i_txn_qty),
0
),
0
),
0
)
)
FROM mtl_cst_txn_cost_details ctcd,
cst_quantity_layers cql,
cst_layer_cost_details clcd
WHERE ctcd.transaction_id = i_txn_id
AND ctcd.organization_id = i_org_id
AND cql.layer_id = i_layer_id
AND cql.inventory_item_id = ctcd.inventory_item_id
AND cql.organization_id = ctcd.organization_id
AND clcd.layer_id (+) = i_layer_id
AND clcd.cost_element_id (+) = ctcd.cost_element_id
AND clcd.level_type (+) = ctcd.level_type;
/* select count(*)
into l_neg_cost
from mtl_cst_actual_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = i_layer_id
and new_cost < 0;
and if immediately average cost update performs then average cost update transaction gets
populated before costing of PO txn as a result it does not contain cost_element 2 cost,which has been
introduced by PO.These changes are similarto change done for India localization case
Fix :
1) Following insert statement would ensure that existing costs against other CE/Level
should not lost
2) We have already changed the Average Cost update form (through bug6407296 ) to make
fields "LEVEL" and "COST ELEMENT" as a non updatable fields
Above two changes will make behaviour consitent.
***************************************************/
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,
'Y',
0,
'N'
from cst_layer_cost_details clcd
where layer_id = i_layer_id
and not exists
(select 'this detail is not in cacd already'
from mtl_cst_actual_cost_details cacd
where cacd.transaction_id = i_txn_id
and cacd.organization_id = i_org_id
and cacd.layer_id = i_layer_id
and cacd.cost_element_id = clcd.cost_element_id
and cacd.level_type = clcd.level_type);
** Delete from cst_layer_cost_details and insert the new rows **
** from mtl_cst_actual_cost_details. **
********************************************************************/
l_stmt_num := 30;
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 i_layer_id,
cacd.cost_element_id,
cacd.level_type,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
cacd.new_cost
from mtl_cst_actual_cost_details cacd
where cacd.transaction_id = i_txn_id
and cacd.organization_id = i_org_id
and cacd.layer_id = i_layer_id
and cacd.insertion_flag = 'Y'
and cacd.new_cost <> 0;
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 i_layer_id,
cacd.cost_element_id,
cacd.level_type,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
cacd.new_cost
from mtl_cst_actual_cost_details cacd
where cacd.transaction_id = i_txn_id
and cacd.organization_id = i_org_id
and cacd.layer_id = i_layer_id
and cacd.insertion_flag = 'Y'
and cacd.cost_element_id = (select min(cost_element_id)
from mtl_cst_actual_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = i_layer_id
and insertion_flag = 'Y');
** Update cst_quanity_layers **
********************************************************************/
l_stmt_num := 50;
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,
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,
i_txn_id,
SUM(DECODE(LEVEL_TYPE, 2, DECODE(COST_ELEMENT_ID, 1, ITEM_COST, 0), 0)),
SUM(DECODE(LEVEL_TYPE, 2, DECODE(COST_ELEMENT_ID, 2, ITEM_COST, 0), 0)),
SUM(DECODE(LEVEL_TYPE, 2, DECODE(COST_ELEMENT_ID, 3, ITEM_COST, 0), 0)),
SUM(DECODE(LEVEL_TYPE, 2, DECODE(COST_ELEMENT_ID, 4, ITEM_COST, 0), 0)),
SUM(DECODE(LEVEL_TYPE, 2, DECODE(COST_ELEMENT_ID, 5, ITEM_COST, 0), 0)),
SUM(DECODE(LEVEL_TYPE, 1, DECODE(COST_ELEMENT_ID, 1, ITEM_COST, 0), 0)),
SUM(DECODE(LEVEL_TYPE, 1,DECODE(COST_ELEMENT_ID, 2, ITEM_COST, 0), 0)),
SUM(DECODE(LEVEL_TYPE, 1, DECODE(COST_ELEMENT_ID, 3, ITEM_COST, 0), 0)),
SUM(DECODE(LEVEL_TYPE ,1, DECODE(COST_ELEMENT_ID ,4, ITEM_COST, 0), 0)),
SUM(DECODE(LEVEL_TYPE, 1, DECODE(COST_ELEMENT_ID, 5, ITEM_COST, 0), 0)),
SUM(DECODE(COST_ELEMENT_ID, 1, ITEM_COST, 0)),
SUM(DECODE(COST_ELEMENT_ID, 2, ITEM_COST, 0)),
SUM(DECODE(COST_ELEMENT_ID, 3, ITEM_COST, 0)),
SUM(DECODE(COST_ELEMENT_ID, 4, ITEM_COST, 0)),
SUM(DECODE(COST_ELEMENT_ID, 5, ITEM_COST, 0)),
SUM(DECODE(LEVEL_TYPE, 2, ITEM_COST, 0)),
SUM(DECODE(LEVEL_TYPE, 1, ITEM_COST, 0)),
SUM(ITEM_COST),
SUM(DECODE(COST_ELEMENT_ID, 2, DECODE(LEVEL_TYPE, 2, ITEM_COST, 0), ITEM_COST)),
SUM(DECODE(COST_ELEMENT_ID, 2, DECODE(LEVEL_TYPE, 1, ITEM_COST, 0), 0))
from CST_LAYER_COST_DETAILS clcd
where clcd.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 Mtl_Material_Transactions **
********************************************************************/
CSTPAVCP.update_mmt(
i_org_id,
i_txn_id,
-1, -- txfr_txn_id is not applicable
i_layer_id,
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);
it is okay to update CIC with the cost when quantity is 0.
The default behavior is not to require mandatory update unless the
above property (one cost group, zero quantity) is found to be true.
When total layer quantity is positive, CIC is updated regardless of the
value of l_mandatory_update.
*/
l_mandatory_update := 0;
SELECT count(*)
INTO l_num_cost_groups
FROM cst_quantity_layers
WHERE inventory_item_id = i_item_id
AND organization_id = i_org_id;
SELECT layer_quantity
INTO l_layer_quantity
FROM cst_quantity_layers
WHERE inventory_item_id = i_item_id
AND organization_id = i_org_id;
l_mandatory_update := 1;
** Update Item Cost and Item Cost Details **
********************************************************************/
CSTPAVCP.update_item_cost(
i_org_id,
i_txn_id,
i_layer_id,
i_cost_type,
i_item_id,
0,/*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);
fnd_file.put_line(fnd_file.log,'Average Cost Update >>>');
o_err_msg := 'CSTPAVCP.average_cost_update (' || to_char(l_stmt_num) ||
'): '
|| substr(SQLERRM, 1,200);
END average_cost_update;
l_no_update_qty NUMBER;
select transaction_type_id, transaction_action_id
into l_txn_type_id, l_txn_action_id
from mtl_material_transactions
where transaction_id = i_txn_id;
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;
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(wac.class_type, 1, 0,
3, 0,
6, 0,
4, decode(l_exp1, 1, 1, 0))
into l_exp2
from mtl_material_transactions mmt,
wip_flow_schedules wfs,
wip_accounting_classes wac
where mmt.transaction_id = i_txn_id
and mmt.organization_id = i_org_id
and wfs.organization_id = i_org_id
and wfs.wip_entity_id = mmt.transaction_source_id
and wac.organization_id = i_org_id
and wac.class_code = wfs.class_code;
select decode(wac.class_type, 1, 0,
3, 0,
6, 0,
4, decode(l_exp1, 1, 1, 0))
into l_exp2
from mtl_material_transactions mmt,
wip_discrete_jobs wdj,
wip_accounting_classes wac
where mmt.transaction_id = i_txn_id
and mmt.organization_id = i_org_id
and wdj.organization_id = i_org_id
and wdj.wip_entity_id = mmt.transaction_source_id
and wac.organization_id = i_org_id
and wac.class_code = wdj.class_code;
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 = i_org_id
and mmt.transaction_id = i_txn_id
and mmt.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,
l_to_layer,
cacd.cost_element_id,
cacd.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,
cacd.actual_cost,
cacd.actual_cost,
cacd.actual_cost,
'Y',
0,
'N'
from mtl_cst_actual_cost_details cacd
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = l_from_layer;
if i_citw_flag = 1 then /* Bug 9758017: For CITW, MMT should not be updated for the tfr CG */
l_no_upd_mmt := 1;
0, -- exp to asset, thus 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);
delete from mtl_cst_actual_cost_details
where transaction_id = i_txn_id
and layer_id = l_from_layer;
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,
l_from_layer,
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 = l_from_layer;
l_no_update_qty := 0;
l_no_update_qty := 1;
fnd_file.put_line(fnd_file.log,'Update mmt for receiving side');
SELECT transfer_transaction_id
INTO l_txf_txn_id
FROM mtl_material_transactions
WHERE transaction_id = i_txn_id;
UPDATE mtl_material_transactions MMT
SET (
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
actual_cost,
prior_cost,
new_cost,
variance_amount
)
= (
SELECT SYSDATE,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
SYSDATE,
SUM(NVL(MCACD.actual_cost,0)),
SUM(NVL(MCACD.prior_cost,0)),
SUM(NVL(MCACD.new_cost,0)),
SUM(NVL(MCACD.variance_amount,0))
FROM mtl_cst_actual_cost_details MCACD
WHERE MCACD.transaction_id = i_txn_id
AND MCACD.layer_id = l_to_layer
)
WHERE MMT.transaction_id = l_txf_txn_id
AND MMT.primary_quantity > 0;
select transaction_action_id, transaction_source_type_id
into l_txn_action_id,l_src_type
from mtl_material_transactions
where transaction_id = i_txn_id;
UPDATE mtl_material_transactions mmt
SET TRANSFER_PRIOR_COSTED_QUANTITY =
(SELECT
layer_quantity
FROM cst_quantity_layers cql
WHERE cql.layer_id = i_txfr_layer_id) /* Modified for bug 6635134 */
WHERE mmt.transaction_id = i_txn_id
AND EXISTS (
SELECT 'X'
FROM cst_quantity_layers cql
WHERE cql.layer_id = i_txfr_layer_id); /* Modified for bug 6635134 */
/* Modified for bug 6635134 - Moved the insert into MCACD outside the if-else clause */
if l_txn_action_id = 1 then
CSTPAVCP.sub_transfer(
i_org_id,
i_txn_id,
i_layer_id,
i_cost_type,
i_item_id,
l_txn_qty,
i_txn_action_id,
l_new_cost,
NULL,
i_txfr_layer_id,
i_citw_flag,
i_flow_schedule,
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);
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_txfr_layer_id,
cacd.cost_element_id,
cacd.level_type,
27,
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,
cacd.new_cost,
cacd.new_cost,
cacd.new_cost,
'N', -- check
0,
'N'
from mtl_cst_actual_cost_details cacd
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = i_txfr_layer_id
and 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)
select
i_txn_id,
i_org_id,
i_txfr_layer_id,
cacd.cost_element_id,
cacd.level_type,
l_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,
cacd.new_cost,
cacd.new_cost,
cacd.new_cost,
'N', -- check
0,
'N'
from mtl_cst_actual_cost_details cacd
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = i_txfr_layer_id
and transaction_action_id = i_txn_action_id;
select decode(wac.class_type, 1, 0,
3, 0,
6, 0,
4, decode(i_exp_flag, 1, 1, 0))
into l_exp_flag
from mtl_material_transactions mmt,
wip_flow_schedules wfs,
wip_accounting_classes wac
where mmt.transaction_id = i_txn_id
and mmt.organization_id = i_org_id
and wfs.organization_id = i_org_id
and wfs.wip_entity_id = mmt.transaction_source_id
and wac.organization_id = i_org_id
and wac.class_code = wfs.class_code;
select decode(wac.class_type, 1, 0,
3, 0,
6, 0,
4, decode(i_exp_flag, 1, 1, 0))
into l_exp_flag
from mtl_material_transactions mmt,
wip_discrete_jobs wdj,
wip_accounting_classes wac
where mmt.transaction_id = i_txn_id
and mmt.organization_id = i_org_id
and wdj.organization_id = i_org_id
and wdj.wip_entity_id = mmt.transaction_source_id
and wac.organization_id = i_org_id
and wac.class_code = wdj.class_code;
/* Bug 9758017: For CITW, MMT should not be updated for the tfr CG */
1,
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);
Select count(*)
into l_txn_cost_exist
from mtl_cst_txn_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id
/* and transaction_cost >= 0 */; -- modified for bug#3835412
fnd_file.put_line(fnd_file.log,'>>>Insert into MCACD using MCTCD values');
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,
NULL,
'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 */; -- modified for bug#3835412
select count(*) into l_count
from mtl_cst_actual_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id;
** we will insert a TL material 0 cost layer. **
********************************************************************/
if (g_debug = 'Y') then
fnd_file.put_line(fnd_file.log,'>>>No Txn details in MCTCD');
select count(*)
into l_cost_details
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,
decode(l_zero_cost_flag, 1, 0, clcd.item_cost),
clcd.item_cost,
clcd.item_cost,
'N',
0,
'N'
from cst_layer_cost_details clcd
where layer_id = i_layer_id;
fnd_file.put_line(fnd_file.log,'>>>No cost values, Inserting zero cost in 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)
values(
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,
NULL,
0,
'N',
0,
'N');
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_actual_cost_details cacd
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 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 nvl(sum(actual_cost),0)
into l_item_cost
from mtl_cst_actual_cost_details cacd
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = i_layer_id;
The check ensures that the resource ID is not null in CICD before inserting into the
MACS. If it is null it replaces the value of resource_id for MOH with the default
value for the same defined in the organization. */
/* Bug 3959770*/
l_stmt_num := 25;
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 resource_id IS NULL
and level_type = decode(i_level,1,1,level_type);
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;
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;
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 resource_id IS NULL
and level_type = 1
and rownum =1;
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;
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_mcacd_ovhd
from mtl_cst_actual_cost_details cacd
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_mcacd_ovhd > 0) then --update mcacd
l_stmt_num := 85;
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;
update mtl_cst_actual_cost_details mcacd
set mcacd.actual_cost = mcacd.actual_cost + l_ovhd_cost
where mcacd.transaction_id = i_txn_id
and mcacd.organization_id = i_org_id
and mcacd.layer_id = i_layer_id
and mcacd.inventory_item_id = i_item_id
and mcacd.level_type = 1
and mcacd.cost_element_id = 2;
else -- insert into MCACD.
l_stmt_num := 89;
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,
2,
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,
sum(actual_cost),
0,
NULL,
'Y',
0,
'N'
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;
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,
2,
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,
sum(actual_cost),
0,
NULL,
'Y',
0,
'N'
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;
I_NO_UPDATE_MMT 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;
** Update mtl_cst_actual_cost_details and update the prior cost **
** to the current average for the elements that exists and insert **
** in to mtl_cst_actual_cost_details the current average cost for **
** the elements that do not exist. **
********************************************************************/
l_stmt_num := 5;
Update mtl_cst_actual_cost_details cacd
Set prior_cost = 0,
new_cost = NULL
Where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = i_layer_id
and transaction_action_id = i_txn_action_id;
Update mtl_cst_actual_cost_details cacd
Set (prior_cost, insertion_flag) =
(Select clcd.item_cost,
'N'
From cst_layer_cost_details clcd
Where clcd.layer_id = i_layer_id
and clcd.cost_element_id = cacd.cost_element_id
and clcd.level_type = cacd.level_type)
Where cacd.transaction_id = i_txn_id
and cacd.organization_id = i_org_id
and cacd.layer_id = i_layer_id
and cacd.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 = cacd.cost_element_id
and clcd.level_type = cacd.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 cacd already'
from mtl_cst_actual_cost_details cacd
where cacd.transaction_id = i_txn_id
and cacd.organization_id = i_org_id
and cacd.layer_id = i_layer_id
and cacd.cost_element_id = clcd.cost_element_id
and cacd.level_type = clcd.level_type);
select layer_quantity
into l_cur_onhand
from cst_quantity_layers cql
where cql.layer_id = i_layer_id;
Update mtl_cst_actual_cost_details cacd
Set new_cost =
decode(sign(l_cur_onhand),-1,
decode(sign(i_txn_qty), -1,
(cacd.prior_cost*l_cur_onhand + cacd.actual_cost*i_txn_qty)/l_new_onhand,
decode(sign(l_new_onhand),-1, cacd.prior_cost,
cacd.actual_cost)),
decode(sign(i_txn_qty), -1,
decode(sign(l_new_onhand), 1,
decode(sign((abs(cacd.prior_cost)*l_cur_onhand + abs(cacd.actual_cost)*i_txn_qty)/l_new_onhand),1,
(cacd.prior_cost*l_cur_onhand + cacd.actual_cost*i_txn_qty)/l_new_onhand,
0)
,cacd.actual_cost),
(cacd.prior_cost*l_cur_onhand + cacd.actual_cost*i_txn_qty)/l_new_onhand)),
-- variance amount
variance_amount =
decode(sign(l_cur_onhand),
-1, decode(sign(i_txn_qty),
-1, 0,
decode(sign(l_new_onhand),
-1, (cacd.actual_cost * i_txn_qty) - (cacd.prior_cost * i_txn_qty),
(cacd.actual_cost * abs(i_txn_qty)) - ((cacd.prior_cost * abs(l_cur_onhand)) + cacd.actual_cost *l_new_onhand)
)
),
decode(sign(i_txn_qty),
-1, decode(sign(l_new_onhand),
1, decode(sign(abs(cacd.actual_cost * i_txn_qty) - abs(cacd.prior_cost * l_cur_onhand)),
1, (cacd.prior_cost * abs(l_cur_onhand)) - (cacd.actual_cost * abs(i_txn_qty)),
0
),
(cacd.prior_cost * l_cur_onhand) + (cacd.actual_cost * abs(l_new_onhand)) - (cacd.actual_cost * abs(i_txn_qty))
),
0
)
)
where cacd.transaction_id = i_txn_id
and cacd.organization_id = i_org_id
and cacd.layer_id = i_layer_id
and cacd.transaction_action_id = i_txn_action_id;
Update cst_layer_cost_details clcd
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,
item_cost =
(select new_cost
from mtl_cst_actual_cost_details cacd
where cacd.transaction_id = i_txn_id
and cacd.organization_id = i_org_id
and cacd.layer_id = i_layer_id
and cacd.cost_element_id = clcd.cost_element_id
and cacd.level_type = clcd.level_type)
where clcd.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 i_layer_id,
cacd.cost_element_id,
cacd.level_type,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
cacd.new_cost
from mtl_cst_actual_cost_details cacd
where cacd.transaction_id = i_txn_id
and cacd.organization_id = i_org_id
and cacd.layer_id = i_layer_id
and cacd.insertion_flag = 'Y';
** Update Mtl_Material_Transactions **
** Need to update prior_costed_quantity now. **
********************************************************************/
if (i_no_update_mmt = 0) then
-- subinventory or staging transfer for receipt side, we need to pass
-- txfr_txn_id to update proper transaction in MMT.
/* Changes for VMI. Adding Planning Transfer Transaction */
if (i_txn_action_id IN (2,5,28,55) 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;
fnd_file.put_line(fnd_file.log, '>>>Calling CSTPAVCP.update_mmt');
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);
fnd_file.put_line(fnd_file.log, '<<
** Update layer quantity and layer costs information **
********************************************************************/
l_stmt_num := 60;
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_cur_onhand + i_txn_qty,
i_txn_id,
SUM(DECODE(LEVEL_TYPE, 2, DECODE(COST_ELEMENT_ID, 1, ITEM_COST, 0), 0)),
SUM(DECODE(LEVEL_TYPE, 2, DECODE(COST_ELEMENT_ID, 2, ITEM_COST, 0), 0)),
SUM(DECODE(LEVEL_TYPE, 2, DECODE(COST_ELEMENT_ID, 3, ITEM_COST, 0), 0)),
SUM(DECODE(LEVEL_TYPE, 2, DECODE(COST_ELEMENT_ID, 4, ITEM_COST, 0), 0)),
SUM(DECODE(LEVEL_TYPE, 2, DECODE(COST_ELEMENT_ID, 5, ITEM_COST, 0), 0)),
SUM(DECODE(LEVEL_TYPE, 1, DECODE(COST_ELEMENT_ID, 1, ITEM_COST, 0), 0)),
SUM(DECODE(LEVEL_TYPE, 1,DECODE(COST_ELEMENT_ID, 2, ITEM_COST, 0), 0)),
SUM(DECODE(LEVEL_TYPE, 1, DECODE(COST_ELEMENT_ID, 3, ITEM_COST, 0), 0)),
SUM(DECODE(LEVEL_TYPE ,1, DECODE(COST_ELEMENT_ID ,4, ITEM_COST, 0), 0)),
SUM(DECODE(LEVEL_TYPE, 1, DECODE(COST_ELEMENT_ID, 5, ITEM_COST, 0), 0)),
SUM(DECODE(COST_ELEMENT_ID, 1, ITEM_COST, 0)),
SUM(DECODE(COST_ELEMENT_ID, 2, ITEM_COST, 0)),
SUM(DECODE(COST_ELEMENT_ID, 3, ITEM_COST, 0)),
SUM(DECODE(COST_ELEMENT_ID, 4, ITEM_COST, 0)),
SUM(DECODE(COST_ELEMENT_ID, 5, ITEM_COST, 0)),
SUM(DECODE(LEVEL_TYPE, 2, ITEM_COST, 0)),
SUM(DECODE(LEVEL_TYPE, 1, ITEM_COST, 0)),
SUM(ITEM_COST),
SUM(DECODE(COST_ELEMENT_ID, 2, DECODE(LEVEL_TYPE, 2, ITEM_COST, 0), ITEM_COST)),
SUM(DECODE(COST_ELEMENT_ID, 2, DECODE(LEVEL_TYPE, 1, ITEM_COST, 0), 0))
from CST_LAYER_COST_DETAILS clcd
where clcd.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 **
********************************************************************/
IF g_debug = 'Y' THEN
fnd_file.put_line(fnd_file.log, '>>>Calling CSTPAVCP.update_item_cost');
CSTPAVCP.update_item_cost(
i_org_id,
i_txn_id,
i_layer_id,
i_cost_type,
i_item_id,
0, -- mandatory_update flag is not set
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);
fnd_file.put_line(fnd_file.log, '<<
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
layer_qty NUMBER;
** Update Mtl_Material_Transactions to set actual cost, prior **
** cost, new cost and prior costed quantity. **
********************************************************************/
if (i_no_update_mmt = 0) then
CSTPAVCP.update_mmt(
i_org_id,
i_txn_id,
-1, -- txfr_txn_id is not applicable
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 information in cst_quantity_layers. **
** There is no need to update the layer quantity for the **
** following transactions: **
** 1) wip scrap transactions **
** 2) Expense flag = 1 **
********************************************************************/
if ((i_txn_action_id = 30) or (i_no_update_qty = 1) or (i_exp_flag = 1)
) then
return;
Update cst_quantity_layers cql
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,
layer_quantity = (cql.layer_quantity + decode(i_txn_action_id, 22, -1*abs(i_txn_qty), i_txn_qty)),
update_transaction_id = i_txn_id
where layer_id = i_layer_id;
CSTPAVCP.update_item_cost(
i_org_id,
i_txn_id,
i_layer_id,
i_cost_type,
i_item_id,
0, -- mandatory_update flag is not set
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);
procedure update_mmt(
I_ORG_ID IN NUMBER,
I_TXN_ID IN NUMBER,
I_TXFR_TXN_ID IN NUMBER,
I_LAYER_ID IN NUMBER,
I_COST_UPDATE 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
layer_qty NUMBER;
fnd_file.put_line(fnd_file.log, '>>>>Inside Update_MMT');
Select transaction_action_id
into l_transaction_action_id
from mtl_material_transactions
where transaction_id = i_txn_id;
Select layer_quantity
into layer_qty
from cst_quantity_layers cql
where cql.layer_id = i_layer_id;
select count(*)
into l_cost_exists
from mtl_cst_actual_cost_details cacd
where cacd.transaction_id = i_txn_id
and cacd.organization_id = i_org_id
and cacd.layer_id = i_layer_id;
select decode(transaction_action_id, 1,
decode(transaction_source_type_id, 5,
decode(cost_group_id, NVL(transfer_cost_group_id, cost_group_id), 0, 1)
,0),
27,decode(transaction_source_type_id, 5,
decode(cost_group_id, NVL(transfer_cost_group_id, cost_group_id), 0, 1),
0)
,0)
into l_citw_flag
from mtl_material_transactions
where transaction_id = i_txn_id;
SELECT inventory_asset_flag
INTO l_asset_item_flag
FROM mtl_system_items
WHERE organization_id = i_org_id
AND inventory_item_id = (SELECT inventory_item_id
FROM mtl_material_transactions
WHERE transaction_id = i_txn_id);
Update mtl_material_transactions mmt
set (last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
actual_cost,
prior_cost,
new_cost,
variance_amount,
prior_costed_quantity,
quantity_adjusted) =
(select sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
SUM(DECODE(l_asset_item_flag, 'N', 0, NVL(actual_cost,0))), -- Bug5137993
sum(nvl(prior_cost,0)),
sum(nvl(new_cost,0)),
sum(nvl(variance_amount,0)),
layer_qty,
decode(i_cost_update,1,layer_qty,NULL)
from mtl_cst_actual_cost_details cacd
where cacd.transaction_id = i_txn_id
and cacd.organization_id = i_org_id
and cacd.layer_id = i_layer_id
and cacd.transaction_action_id =
decode(l_citw_flag, 1, 2, -- for citw, just select
cacd.transaction_action_id)) -- sub_txfr rows
where mmt.transaction_id = l_txn_id;
Update mtl_material_transactions mmt
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,
actual_cost = 0,
prior_cost = 0,
new_cost = 0,
variance_amount=0,
prior_costed_quantity = layer_qty
where mmt.transaction_id = l_txn_id;
SELECT
mmt.inventory_item_id
INTO
l_item_id
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = i_txn_id;
UPDATE mtl_material_transactions mmt
SET prior_costed_quantity = (
SELECT cql.layer_quantity - mmt.primary_quantity
FROM cst_quantity_layers cql,
mtl_parameters mp
WHERE cql.organization_id = i_org_id
AND cql.inventory_item_id = l_item_id
AND cql.cost_group_id = mp.default_cost_group_id
AND mp.organization_id = i_org_id
)
WHERE mmt.transaction_id = i_txn_id
AND EXISTS (
SELECT 'X'
FROM cst_quantity_layers cql,
mtl_parameters mp
WHERE cql.organization_id = i_org_id
AND cql.inventory_item_id = l_item_id
AND cql.cost_group_id = mp.default_cost_group_id
AND mp.organization_id = i_org_id
);
SELECT nvl(msi.asset_inventory,-9),
mmt.transfer_transaction_id,
mmt.inventory_item_id,
mmt.transfer_cost_group_id
INTO l_from_inv,
l_transfer_txn_id,
l_item_id,
l_transfer_cost_grp_id
FROM mtl_material_transactions mmt,
mtl_secondary_inventories msi
WHERE mmt.transaction_id = i_txn_id
AND mmt.subinventory_code = msi.secondary_inventory_name
AND mmt.organization_id = msi.organization_id;
SELECT nvl(msi.asset_inventory,-9)
INTO l_to_inv
FROM mtl_material_transactions mmt,
mtl_secondary_inventories msi
WHERE mmt.transaction_id = i_txn_id
AND nvl(mmt.transfer_subinventory,mmt.subinventory_code) = msi.secondary_inventory_name
AND mmt.organization_id = msi.organization_id;
UPDATE mtl_material_transactions mmt
SET prior_costed_quantity =
(SELECT
layer_quantity
FROM cst_quantity_layers cql
WHERE cql.organization_id = i_org_id
AND cql.inventory_item_id = l_item_id
AND cql.cost_group_id = l_transfer_cost_grp_id)
WHERE mmt.transaction_id = l_transfer_txn_id
AND EXISTS (
SELECT 'X'
FROM cst_quantity_layers cql
WHERE cql.organization_id = i_org_id
AND cql.inventory_item_id = l_item_id
AND cql.cost_group_id = l_transfer_cost_grp_id);
update mtl_material_transactions mmt
set prior_costed_quantity = 0
where mmt.transaction_id = l_transfer_txn_id;
fnd_file.put_line(fnd_file.log, 'Update_MMT >>>');
o_err_msg := 'CSTPAVCP.update_mmt (' || to_char(l_stmt_num) ||
'): '
|| substr(SQLERRM,1,200);
END update_mmt;
PROCEDURE update_item_cost(
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_MANDATORY_UPDATE 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
total_value NUMBER; -- Added for bug 4905189
select DEFAULT_MATL_OVHD_COST_ID
into l_default_MOH_subelement
from mtl_parameters
where organization_id= I_ORG_ID;
fnd_file.put_line(fnd_file.log, '>>>>Inside Update_Item_Cost');
select nvl(sum(layer_quantity),0), nvl(sum(layer_quantity*nvl(item_cost,0)),0),count(1)
into total_qty, total_value,l_num_cost_groups
from cst_quantity_layers cql
where cql.inventory_item_id = i_item_id
and cql.organization_id = i_org_id;
if ( (total_qty <= 0) and (i_mandatory_update = 0) and (l_num_cost_groups > 1) ) then
return;
Delete from cst_item_cost_details
where inventory_item_id = i_item_id
and organization_id = i_org_id
and cost_type_id = i_cost_type;
Insert into cst_item_cost_details (
inventory_item_id,
organization_id,
cost_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
level_type,
usage_rate_or_amount,
basis_type,
basis_factor,
net_yield_or_shrinkage_factor,
item_cost,
cost_element_id,
rollup_source_type,
request_id,
program_application_id,
program_id,
program_update_date,
resource_id) -----------------Bug 3959770
select
i_item_id,
i_org_id,
i_cost_type,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
clcd.level_type,
(sum(clcd.item_cost*decode(sign(total_qty),1,cql.layer_quantity,1)))
/decode(sign(total_qty),1,total_qty,1), -- modified for bug#3835412
1,
1,
1,
(sum(clcd.item_cost*decode(sign(total_qty),1,cql.layer_quantity,1)))
/decode(sign(total_qty),1,total_qty,1), -- modified for bug#3835412
clcd.cost_element_id,
1,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
decode(clcd.cost_element_id,2,decode(clcd.level_type,1,l_default_MOH_subelement,
NULL),
NULL)
from cst_layer_cost_details clcd,
cst_quantity_layers cql
where cql.organization_id = i_org_id
and cql.inventory_item_id = i_item_id
and cql.layer_id = clcd.layer_id
group by cost_element_id, level_type;
Update cst_item_costs cic
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,
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)),
SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,2,ITEM_COST,0),0)),
SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,3,ITEM_COST,0),0)),
SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,4,ITEM_COST,0),0)),
SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,5,ITEM_COST,0),0)),
SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,1,ITEM_COST,0),0)),
SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,2,ITEM_COST,0),0)),
SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,3,ITEM_COST,0),0)),
SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,4,ITEM_COST,0),0)),
SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,5,ITEM_COST,0),0)),
SUM(DECODE(COST_ELEMENT_ID,1,ITEM_COST)),
SUM(DECODE(COST_ELEMENT_ID,2,ITEM_COST)),
SUM(DECODE(COST_ELEMENT_ID,3,ITEM_COST)),
SUM(DECODE(COST_ELEMENT_ID,4,ITEM_COST)),
SUM(DECODE(COST_ELEMENT_ID,5,ITEM_COST)),
SUM(DECODE(LEVEL_TYPE,2,ITEM_COST,0)),
SUM(DECODE(LEVEL_TYPE,1,ITEM_COST,0)),
SUM(ITEM_COST),
SUM(DECODE(COST_ELEMENT_ID, 2,DECODE(LEVEL_TYPE,2,ITEM_COST,0), ITEM_COST)),
SUM(DECODE(COST_ELEMENT_ID, 2,DECODE(LEVEL_TYPE,1,ITEM_COST,0),0))
from CST_ITEM_COST_DETAILS cicd
where cicd.inventory_item_id = i_item_id
and cicd.organization_id = i_org_id
and cicd.cost_type_id = i_cost_type)
where cic.inventory_item_id = i_item_id
and cic.organization_id = i_org_id
and cic.cost_type_id = i_cost_type
and exists
(select 'there is detail cost'
from cst_item_cost_details cicd
where cicd.inventory_item_id = i_item_id
and cicd.organization_id = i_org_id
and cicd.cost_type_id = i_cost_type);
fnd_file.put_line(fnd_file.log, 'Update_Item_Cost >>>');
o_err_msg := 'CSTPAVCP.update_item_cost (' || to_char(l_stmt_num) ||
'): '
|| substr(SQLERRM, 1,200);
END update_item_cost;
O_NO_UPDATE_MMT IN OUT NOCOPY NUMBER,
O_EXP_FLAG IN OUT NOCOPY NUMBER,
O_HOOK_USED OUT NOCOPY NUMBER,
O_Err_Num OUT NOCOPY NUMBER,
O_Err_Code OUT NOCOPY VARCHAR2,
O_Err_Msg OUT NOCOPY VARCHAR2
) IS
l_err_num NUMBER;
l_txn_update_id NUMBER;
l_update_std NUMBER;
l_update_std := 0;
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 decode(primary_cost_method, 2,
(select decode(primary_cost_method, 2, 1, 0)
from mtl_parameters
where organization_id = l_from_org),
0)
into l_ave_to_ave
from mtl_parameters
where organization_id = l_to_org;
select decode(l_std_from_org, 1, l_from_org,
decode(l_std_to_org,1,l_to_org,-1))
into l_std_org
from dual;
select cost_organization_id
into l_std_cost_org
from mtl_parameters
where organization_id = l_std_org;
select decode(inventory_asset_flag, 'Y', 0, 1)
into l_std_exp
from mtl_system_items
where inventory_item_id = i_item_id
and organization_id = l_std_org;
select decode(l_std_exp,1,1,decode(asset_inventory,1,0,1))
into l_std_exp
from mtl_secondary_inventories msi
,mtl_material_transactions mmt
where mmt.transaction_id = i_txn_id
and mmt.organization_id = l_std_org
and msi.organization_id = l_std_org
and msi.secondary_inventory_name = mmt.subinventory_code;
o_no_update_mmt := 1;
select decode(inventory_asset_flag, 'Y',0,1)
into o_exp_flag
from mtl_system_items
where inventory_item_id = i_item_id
and organization_id = i_org_id;
UPDATE mtl_material_transactions mmt
SET TRANSFER_PRIOR_COSTED_QUANTITY =
(SELECT
layer_quantity
FROM cst_quantity_layers cql
WHERE cql.organization_id = l_which_org
AND cql.inventory_item_id = i_item_id
AND cql.cost_group_id = l_which_cst_grp)
WHERE mmt.transaction_id = i_txn_id
AND EXISTS (
SELECT 'X'
FROM cst_quantity_layers cql
WHERE cql.organization_id = l_which_org
AND cql.inventory_item_id = i_item_id
AND cql.cost_group_id = l_which_cst_grp);
update mtl_material_transactions mmt
set TRANSFER_PRIOR_COSTED_QUANTITY = 0
where mmt.transaction_id = i_txn_id;
UPDATE mtl_material_transactions mmt
SET mmt.transaction_cost = (select (mmt1.transaction_cost * mmt1.currency_conversion_rate)
from mtl_material_transactions mmt1
where mmt1.transaction_id = mmt.transfer_transaction_id
and mmt1.costed_flag is null)
WHERE mmt.transaction_id = i_txn_id
AND nvl(mmt.transaction_cost,0) = 0;
select decode(inventory_asset_flag, 'Y', 0, 1)
into l_to_std_exp
from mtl_system_items
where inventory_item_id = i_item_id
and organization_id = l_std_org;
select count(*)
into l_count
from cst_item_cost_details
where /* organization_id = l_std_org : bugfix 3048258 */
organization_id = l_std_cost_org
and cost_type_id = 1
and inventory_item_id = i_item_id;
/* If no rows exist in cicd (item hasn't been costed), insert into */
/* mcacd using 0 value of this level material */
if (l_count > 0) then
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,
l_std_org,
-1,
cicd.cost_element_id,
cicd.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,
nvl(sum(cicd.item_cost),0),
NULL,
NULL,
'N',
0,
'N'
from cst_item_cost_details cicd
where /* organization_id = l_std_org : bugfix 3048258 */
organization_id = l_std_cost_org
and cost_type_id = 1
and inventory_item_id = i_item_id
group by cost_element_id, 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)
values ( i_txn_id,
l_std_org,
-1,
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,
NULL,
NULL,
'N',
0,
'N');
FND_FILE.PUT_LINE(fnd_file.log, to_char(l_stmt_num) || 'Insert into MACS from CICD');
Insert into mtl_actual_cost_subelement(
layer_id,
transaction_id,
organization_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
-1,
i_txn_id,
l_std_org,
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,
cicd.item_cost,
'N'
from cst_item_cost_details cicd
where inventory_item_id = i_item_id
and /* organization_id = l_std_org : bugfix 3048258 */
organization_id = l_std_cost_org
and cost_type_id = 1
and cost_element_id = 2
and level_type = 1;
update mtl_material_transactions mmt
set (last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
actual_cost) =
(select sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
nvl(sum(actual_cost),0)
from mtl_cst_actual_cost_details cacd
where cacd.transaction_id = i_txn_id
and cacd.organization_id = l_std_org
and cacd.layer_id = -1)
where mmt.transaction_id = i_txn_id;
l_update_std := 1;
select transfer_transaction_id
into l_txn_update_id
from mtl_material_transactions
where transaction_id = i_txn_id;
l_txn_update_id := i_txn_id;
fnd_file.put_line(fnd_file.log, '>>>Transaction update id'||to_char(l_txn_update_id));
/* Exception block inserted for bug 1399079, (non costed items) */
BEGIN
select item_cost, -1
into l_snd_txn_cost, l_from_layer_id
from cst_item_costs
where cost_type_id = l_cost_type_id
and inventory_item_id = i_item_id
/* and organization_id = l_from_org; : bugfix 3048258 */
select sum(actual_cost), layer_id
into l_snd_txn_cost, l_from_layer_id
from mtl_cst_actual_cost_details
where transaction_id= i_txn_id
and organization_id= i_org_id
and layer_id= i_layer_id
group by layer_id;
select item_cost, layer_id
into l_snd_txn_cost, l_from_layer_id
from cst_quantity_layers
where organization_id = l_from_org
and inventory_item_id = i_item_id
and cost_group_id = l_from_cost_grp;
select item_cost, layer_id
into l_snd_txn_cost, l_from_layer_id
from cst_quantity_layers
where organization_id = l_from_org
and inventory_item_id = i_item_id
and cost_group_id = l_from_cost_grp;
Update mtl_material_transactions
Set transfer_cost =
(select decode(nvl(transfer_percentage, -999),-999, transfer_cost,
(transfer_percentage * l_snd_txn_cost *
decode(i_txn_action_id, 12, abs(primary_quantity)/l_um_rate,
abs(primary_quantity)))*l_xfer_conv_rate/100) -- bug 2827548-added l_xfer_conv_rate
from mtl_material_transactions
where transaction_id = i_txn_id)
where transaction_id = i_txn_id
or (transaction_id = decode(i_txn_action_id,3,l_txn_update_id,-1));
select nvl(transfer_cost,0), nvl(transportation_cost,0),
decode(i_txn_action_id,12,(primary_quantity / l_um_rate),primary_quantity)
into l_txfr_cost, l_trans_cost, l_snd_qty
from mtl_material_transactions
where transaction_id = i_txn_id;
insert price into MCTCD */
if (i_tprice_option = 2) then
l_rcv_txn_cost := i_txf_price;
i_txn_update_id => l_txn_update_id,
i_from_org => l_from_org,
i_to_org => l_to_org,
i_snd_qty => l_snd_qty,
i_txfr_cost => l_txfr_cost,
i_trans_cost => l_trans_cost,
i_conv_rate => l_conv_rate,
i_um_rate => l_um_rate,
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_hook_used => l_cost_hook_io,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg);
insert into mtl_cst_txn_cost_details (
transaction_id,
organization_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,
transaction_cost,
new_average_cost,
percentage_change,
value_change)
values (l_txn_update_id,
l_to_org,
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,
l_rcv_txn_cost,
0,
0,
0);
Update mtl_material_transactions
Set transaction_cost = l_new_txn_cost
where transaction_id = i_txn_id;
Update mtl_material_transactions
Set transaction_cost = l_rcv_txn_cost
where transaction_id = l_txn_update_id;
update mtl_material_transactions mmt
set mmt.transaction_cost = l_rcv_txn_cost
where mmt.transfer_transaction_id = i_txn_id
and mmt.transaction_action_id = 12;
update mtl_material_transactions mmt
set mmt.transaction_cost = l_snd_txn_cost
where mmt.transaction_id =
(select mmt1.transfer_transaction_id
from mtl_material_transactions mmt1
where mmt1.transaction_id = i_txn_id)
and mmt.transaction_action_id = 21
and nvl(mmt.transaction_cost,0) = 0;
select decode(inventory_asset_flag, 'Y', 0, 1)
into l_txfr_std_exp
from mtl_system_items
where inventory_item_id = i_item_id
and organization_id = l_std_org;
select transfer_transaction_id
into l_txfr_txn_id
from mtl_material_transactions mmt
where mmt.transaction_id = i_txn_id;
select decode(l_txfr_std_exp,1,1,decode(asset_inventory,1,0,1))
into l_txfr_std_exp
from mtl_secondary_inventories msi
,mtl_material_transactions mmt
where mmt.transaction_id = l_txfr_txn_id
and mmt.organization_id = l_std_org
and msi.organization_id = l_std_org
and msi.secondary_inventory_name = mmt.subinventory_code;
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
l_txfr_txn_id,
l_std_org,
-1,
decode(l_elemental_visible,'Y',ctcd.cost_element_id,1),
decode(l_elemental_visible,'Y',ctcd.level_type,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,
ctcd.inventory_item_id,
decode(l_elemental_visible,'Y',ctcd.transaction_cost,l_rcv_txn_cost),
NULL,
NULL,
'N',
0,
'N'
FROM mtl_cst_txn_cost_details ctcd
WHERE ctcd.transaction_id = l_txn_update_id
AND ctcd.organization_id = l_std_org
/* AND ctcd.transaction_cost >= 0 */; -- modified for bug#3835412
update mtl_material_transactions mmt
set (last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
actual_cost) =
(select sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
nvl(sum(actual_cost),0)
from mtl_cst_actual_cost_details cacd
where cacd.transaction_id = l_txn_update_id
and cacd.organization_id = l_std_org
and cacd.layer_id = -1)
where mmt.transaction_id = l_txn_update_id;
if (l_update_std = 1) then
/* the receiving org is standard exp. */
l_stmt_num := 210;
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,
l_std_org,
-1,
decode(l_elemental_visible,'Y',ctcd.cost_element_id,1),
decode(l_elemental_visible,'Y',ctcd.level_type,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,
ctcd.inventory_item_id,
decode(l_elemental_visible,'Y',ctcd.transaction_cost,l_rcv_txn_cost),
NULL,
NULL,
'N',
0,
'N'
FROM mtl_cst_txn_cost_details ctcd
WHERE ctcd.transaction_id = l_txn_update_id
AND ctcd.organization_id = l_std_org
/* AND ctcd.transaction_cost >= 0 */; -- modified for bug#3835412
update mtl_material_transactions mmt
set (last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
actual_cost) =
(select sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
nvl(sum(actual_cost),0)
from mtl_cst_actual_cost_details cacd
where cacd.transaction_id = l_txn_update_id
and cacd.organization_id = l_std_org
and cacd.layer_id = -1)
where mmt.transaction_id = l_txn_update_id;
select transfer_transaction_id
into l_txfr_txn_id
from mtl_material_transactions mmt
where mmt.transaction_id = i_txn_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,
l_std_org,
-1,
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,
NULL,
NULL,
'N',
0,
'N'
FROM mtl_cst_txn_cost_details ctcd
WHERE ctcd.transaction_id = decode(i_txn_org_id, l_std_org, l_txfr_txn_id, l_txn_update_id) -- sending txn id
AND ctcd.organization_id = l_std_org
/* AND ctcd.transaction_cost >= 0 */; -- modified for bug#3835412
update mtl_material_transactions mmt
set (last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
actual_cost) =
(select sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
nvl(sum(actual_cost),0)
from mtl_cst_actual_cost_details cacd
where cacd.transaction_id = l_txn_update_id
and cacd.organization_id = l_std_org
and cacd.layer_id = -1)
where mmt.transaction_id = l_txn_update_id;
/* The following FROM clause in the select statement has been commented out
because we now have to refer CST_ORGANIZATION_DEFINITIONS as a result of the
impact of the HR-PROFILE option */
select set_of_books_id
into l_snd_sob_id
/*from org_organization_definitions */
from cst_organization_definitions
where organization_id = i_from_org;
select currency_code
into l_snd_curr
from gl_sets_of_books
where set_of_books_id = l_snd_sob_id;
/* The following line in the FROM clause of the select statement has been
commented out because it will now be refering to cst_organization_definitions as an impact of the HR-PROFILE option */
select set_of_books_id
into l_rcv_sob_id
/*from org_organization_definitions */
from cst_organization_definitions
where organization_id = i_to_org;
select currency_code
into l_rcv_curr
from gl_sets_of_books
where set_of_books_id = l_rcv_sob_id;
select currency_conversion_type, TRUNC(transaction_date)
into l_curr_type, l_txn_date
from mtl_material_transactions
where transaction_id = i_txn_id;
select conversion_rate, conversion_date
into l_conv_rate, l_conv_date
from gl_daily_conversion_rates
where set_of_books_id = l_rcv_sob_id
and from_currency_code = l_snd_curr
and conversion_type = l_curr_type
and conversion_date =
(select max(conversion_date)
from gl_daily_conversion_rates
where set_of_books_id = l_rcv_sob_id
and from_currency_code = l_snd_curr
and conversion_type = l_curr_type
and conversion_date <= l_txn_date);
select primary_uom_code
into o_snd_uom
from mtl_system_items
where organization_id = i_from_org
and inventory_item_id = i_item_id;
select primary_uom_code
into o_rcv_uom
from mtl_system_items
where organization_id = i_to_org
and inventory_item_id = i_item_id;
select decode(primary_cost_method,1,1,0)
into l_ret_val
from mtl_parameters
where organization_id = i_org_id;
select pbp.borrow_transaction_id,
pbp.payback_quantity
from pjm_borrow_paybacks pbp
where pbp.payback_transaction_id = c_cur_txn_id;
select mcacd.transaction_id,
mcacd.cost_element_id,
mcacd.level_type,
mcacd.inventory_item_id,
mcacd.actual_cost,
mcacd.prior_cost,
mcacd.new_cost,
mcacd.layer_id
from mtl_cst_actual_cost_details mcacd
where mcacd.transaction_id = c_transaction_id
and mcacd.layer_id = i_to_layer;
if l_temp_element_cost(l_index_counter) <> 0 then -- if element cost is not 0 then insert into MCTCD
l_stmt_num := 20;
insert into mtl_cst_txn_cost_details(
TRANSACTION_ID,
ORGANIZATION_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,
TRANSACTION_COST)
values(
i_txn_id,
i_org_id,
decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5)),
l_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,
l_temp_element_cost(l_index_counter));
Select count(*) into l_count
from mtl_cst_txn_cost_details
where transaction_id = i_txn_id;
/* Insert int MCTCD only if cost element exists in MCACD with zero transaction cost */
insert into mtl_cst_txn_cost_details(
TRANSACTION_ID,
ORGANIZATION_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,
TRANSACTION_COST)
values(
i_txn_id,
i_org_id,
l_cost_element(i),
l_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);
if l_temp_element_cost(l_index_counter) <> 0 then -- if element cost <>0 then insert update MCACD
update mtl_cst_actual_cost_details mcacd
set mcacd.actual_cost = l_temp_element_cost(l_index_counter)
where mcacd.transaction_id = i_txn_id
and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
and mcacd.level_type = l_level_type
and mcacd.layer_id = i_to_layer;
else -- if the element cost == 0 then we need to delete MCACD.
delete from mtl_cst_actual_cost_details mcacd
where mcacd.transaction_id = i_txn_id
and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
and mcacd.level_type = l_level_type
and mcacd.layer_id = i_to_layer;
select max(mcacd.actual_cost)
into l_cur_cost
from mtl_cst_actual_cost_details mcacd
where mcacd.level_type = l_level_type
and mcacd.cost_element_id = l_ce
and mcacd.transaction_id = i_txn_id
and mcacd.layer_id = i_from_layer_id;
select max(mcacd.actual_cost)
into l_borrowed_cost
from mtl_cst_actual_cost_details mcacd
where mcacd.level_type = l_level_type
and mcacd.cost_element_id = l_ce
and mcacd.transaction_id = i_txn_id
and mcacd.layer_id = i_to_layer_id;
update mtl_cst_actual_cost_details mcacd
set mcacd.payback_variance_amount = l_variance
where mcacd.transaction_id = i_txn_id
and mcacd.cost_element_id = l_ce
and mcacd.level_type = l_level_type
and mcacd.layer_id = i_from_layer_id;
i_txn_update_id IN NUMBER,
i_from_org IN NUMBER,
i_to_org IN NUMBER,
i_snd_qty IN NUMBER,
i_txfr_cost IN NUMBER,
i_trans_cost IN NUMBER,
i_conv_rate IN NUMBER,
i_um_rate 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_hook_used IN NUMBER := 0,
o_err_num OUT NOCOPY NUMBER,
o_err_code OUT NOCOPY VARCHAR2,
o_err_msg OUT NOCOPY VARCHAR2)
IS
l_err_num NUMBER;
select cost_organization_id
into l_from_cost_org
from mtl_parameters
where organization_id = i_from_org;
insert into mtl_cst_txn_cost_details (
transaction_id,
organization_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,
transaction_cost,
new_average_cost,
percentage_change,
value_change)
select
i_txn_update_id,
i_to_org,
cost_element_id,
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,
sum(item_cost)*i_conv_rate/i_um_rate,
0,
0,
0
from cst_item_cost_details cicd
where cicd.cost_type_id = i_cost_type_id
and cicd.inventory_item_id = i_item_id
/* and cicd.organization_id = i_from_org : bugfix 3048258 */
and cicd.organization_id = l_from_cost_org
group by cicd.cost_element_id,cicd.level_type;
insert into mtl_cst_txn_cost_details (
transaction_id,
organization_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,
transaction_cost,
new_average_cost,
percentage_change,
value_change)
select
i_txn_update_id,
i_to_org,
cost_element_id,
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,
clcd.item_cost*i_conv_rate/i_um_rate,
0,
0,
0
from cst_layer_cost_details clcd
where clcd.layer_id = i_from_layer_id;
fnd_file.put_line(fnd_file.log, '>>>>Hook has been used, inserting in MCTCD from MCACD');
insert into mtl_cst_txn_cost_details (
transaction_id,
organization_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,
transaction_cost,
new_average_cost,
percentage_change,
value_change)
select
i_txn_update_id,
i_to_org,
cost_element_id,
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,
mcacd.actual_cost*i_conv_rate/i_um_rate,
0,
0,
0
from mtl_cst_actual_cost_details mcacd
where mcacd.layer_id = i_from_layer_id
and mcacd.transaction_id = i_txn_id;
insert into mtl_cst_txn_cost_details (
transaction_id,
organization_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,
transaction_cost,
new_average_cost,
percentage_change,
value_change)
values (
i_txn_update_id,
i_to_org,
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,
0,
0,
0);
select count(*)
into l_movh_cnt
from mtl_cst_txn_cost_details mctcd
where mctcd.transaction_id = i_txn_update_id
and mctcd.organization_id = i_to_org
and mctcd.inventory_item_id = i_item_id
and mctcd.level_type = 1
and mctcd.cost_element_id = 2;
select NVL(mctcd.transaction_cost,0)
into l_rcv_movh
from mtl_cst_txn_cost_details mctcd
where mctcd.transaction_id = i_txn_update_id
and mctcd.organization_id = i_to_org
and mctcd.inventory_item_id = i_item_id
and mctcd.level_type = 1
and mctcd.cost_element_id = 2;
update mtl_cst_txn_cost_details mctcd
set mctcd.transaction_cost = l_rcv_movh
where mctcd.transaction_id = i_txn_update_id
and mctcd.organization_id = i_to_org
and mctcd.inventory_item_id = i_item_id
and mctcd.level_type = 1
and mctcd.cost_element_id = 2;
insert into mtl_cst_txn_cost_details (
transaction_id,
organization_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,
transaction_cost,
new_average_cost,
percentage_change,
value_change)
values (
i_txn_update_id,
i_to_org,
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,
l_rcv_movh,
0,
0,
0);
SELECT mmt.TRANSACTION_ID "TRANSACTION_ID",
mmt.PRIMARY_QUANTITY "PRIMARY_QUANTITY",
mmt.TRANSACTION_TYPE_ID "TRANSACTION_TYPE_ID",
mmt.TRANSACTION_ACTION_ID "TRANSACTION_ACTION_ID",
mmt.TRANSACTION_SOURCE_TYPE_ID "TRANSACTION_SOURCE_TYPE_ID",
mmt.ORGANIZATION_ID "ORGANIZATION_ID",
mmt.TRANSFER_ORGANIZATION_ID "TRANSFER_ORGANIZATION_ID",
mmt.TRANSACTION_DATE "TRANSACTION_DATE",
mmt.INVENTORY_ITEM_ID "INVENTORY_ITEM_ID",
mmt.SUBINVENTORY_CODE "SUBINVENTORY_CODE",
NVL(mmt.TRANSFER_COST_GROUP_ID,-1) "TRANSFER_COST_GROUP_ID",
NVL(mmt.COST_GROUP_ID,mp.DEFAULT_COST_GROUP_ID) "COST_GROUP_ID",
mmt.COSTED_FLAG "COSTED_FLAG",
mmt.ACCT_PERIOD_ID "ACCT_PERIOD_ID",
NVL(mmt.PARENT_TRANSACTION_ID, mmt.transaction_id) "PARENT_ID",
mmt.transaction_quantity "TRANSACTION_QUANTITY",
NVL(mmt.LOGICAL_TRX_TYPE_CODE, -1) "DROP_SHIP_FLAG",
NVL(mmt.logical_transaction, 3) "LOGICAL_TRANSACTION",
mp.primary_cost_method "PRIMARY_COST_METHOD",
mp.cost_organization_id "COST_ORGANIZATION_ID",
NVL(mmt.DISTRIBUTION_ACCOUNT_ID, -1) "DISTRIBUTION_ACCOUNT_ID",
mp.primary_cost_method "COST_TYPE_ID", /* For use as cost_type_id */
NVL(mp.AVG_RATES_COST_TYPE_ID, -1) "AVG_RATES_COST_TYPE_ID",
decode(msi.INVENTORY_ASSET_FLAG,'Y',0,1) "EXP_ITEM"
FROM mtl_material_transactions mmt,
mtl_parameters mp,
mtl_system_items_b msi
WHERE mmt.organization_id = mp.organization_id
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND (( p_parent_id is not null and
mmt.parent_transaction_id = p_parent_id)
or (p_parent_id is null and p_txn_id is not null and
mmt.transaction_id = p_txn_id))
ORDER BY mmt.transaction_id;
SELECT decode(INVENTORY_ASSET_FLAG,'Y',0,1)
INTO l_exp_item
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = c_mmt_txn_rec.inventory_item_id
AND ORGANIZATION_ID = c_mmt_txn_rec.organization_id;
SELECT decode(l_exp_item,1,1,decode(ASSET_INVENTORY,1,0,1))
INTO l_exp_flag
FROM MTL_SECONDARY_INVENTORIES
WHERE SECONDARY_INVENTORY_NAME = c_mmt_txn_rec.subinventory_code
AND ORGANIZATION_ID = c_mmt_txn_rec.organization_id;
select organization_id, decode(nvl(logical_transaction, 2), 1, 0, 1)
into l_parent_organization_id, l_parent_transaction_type
from mtl_material_transactions
where transaction_id = p_parent_id;
select count(*)
into l_mctcd_count
from mtl_cst_txn_cost_details
where transaction_id = c_mmt_txn_rec.transaction_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
c_mmt_txn_rec.transaction_id,
c_mmt_txn_rec.organization_id,
l_layer_id,
1,
1,
c_mmt_txn_rec.transaction_action_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
sysdate,
c_mmt_txn_rec.inventory_item_id,
decode(c_mmt_txn_rec.transaction_type_id, 20, ctcd.value_change,ctcd.transaction_cost),
NULL,
NULL,
'N',
0,
'N'
FROM mtl_cst_txn_cost_details ctcd
WHERE transaction_id = c_mmt_txn_rec.transaction_id;
fnd_file.put_line (fnd_file.log, 'Inserted in mcacd:' || sql%rowcount);
/* Update MMT */
l_stmt_num := 42;
Update mtl_material_transactions mmt
set (last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
actual_cost,
variance_amount) =
( select sysdate,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
sysdate,
sum(nvl(actual_cost,0)),
sum(nvl(variance_amount,0))
from mtl_cst_actual_cost_details cacd
where cacd.transaction_id = c_mmt_txn_rec.transaction_id)
where mmt.transaction_id = c_mmt_txn_rec.transaction_id;
/* Update Costed Flag */
l_stmt_num := 70;
update mtl_material_transactions
set costed_flag = NULL
WHERE parent_transaction_id = p_parent_id;
update mtl_material_transactions
set costed_flag = NULL
WHERE transaction_id = p_txn_id ;
/* Modified update statement for performance reasons. See bug#3585779*/
if (p_parent_id is null) then
update mtl_material_transactions
set costed_flag = 'E',
error_code = x_err_code,
error_explanation = x_err_msg
where (p_txn_id is not null
and transaction_id = p_txn_id);
update mtl_material_transactions
set costed_flag = decode(transaction_id, p_parent_id, 'E', 'N'),
error_code = x_err_code,
error_explanation = x_err_msg
where parent_transaction_id = p_parent_id or
(transaction_id = p_parent_id and parent_transaction_id is null) or
(transaction_id = p_txn_id);
update mtl_material_transactions
set costed_flag = decode(transaction_id, p_parent_id, 'E', 'N'),
error_code = x_err_code,
error_explanation = x_err_msg
where parent_transaction_id = p_parent_id or
(transaction_id = p_parent_id and parent_transaction_id is null);
/* Modified update statement for performance reasons. See bug#3585779*/
if (p_parent_id is null) then
update mtl_material_transactions
set costed_flag = 'E',
error_code = x_err_code,
error_explanation = x_err_msg,
request_id = p_request_id
where (p_txn_id is not null
and transaction_id = p_txn_id);
update mtl_material_transactions
set costed_flag = decode(transaction_id, p_parent_id, 'E', 'N'),
error_code = x_err_code,
error_explanation = x_err_msg,
request_id = p_request_id
where parent_transaction_id = p_parent_id or
(transaction_id = p_parent_id and parent_transaction_id is null) or
(transaction_id = p_txn_id);
update mtl_material_transactions
set costed_flag = decode(transaction_id, p_parent_id, 'E', 'N'),
error_code = x_err_code,
error_explanation = x_err_msg,
request_id = p_request_id
where parent_transaction_id = p_parent_id or
(transaction_id = p_parent_id and parent_transaction_id is null);
select count(*)
into l_count
from cst_item_cost_details
where inventory_item_id = p_item_id
and organization_id = p_org_id
and cost_type_id = 1;
/* Insert into MCACD using 0 cost for This Level Material */
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,
insertion_flag,
variance_amount,
user_entered)
values ( p_txn_id,
p_org_id,
-1,
1,
1,
p_txn_action_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
sysdate,
p_item_id,
0,
'N',
0,
'N');
/* Insert into MCACD cost details from CICD */
l_stmt_num := 30;
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,
insertion_flag,
variance_amount,
user_entered)
select p_txn_id,
p_org_id,
-1,
cicd.cost_element_id,
cicd.level_type,
p_txn_action_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
sysdate,
p_item_id,
nvl(sum(cicd.item_cost),0),
'N',
0,
'N'
from cst_item_cost_details cicd
where organization_id = p_org_id
and inventory_item_id = p_item_id
and cost_type_id = 1
group by cost_element_id, level_type;
select count(*)
into l_count
from cst_item_cost_details
where inventory_item_id = p_item_id
and organization_id = p_cost_org_id
and cost_type_id = 1;
/* Insert into MCACD using 0 cost for This Level Material */
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,
insertion_flag,
variance_amount,
user_entered)
values ( p_txn_id,
p_org_id,
-1,
1,
1,
p_txn_action_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
sysdate,
p_item_id,
0,
'N',
0,
'N');
/* Insert into MCACD cost details from CICD */
l_stmt_num := 38;
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,
insertion_flag,
variance_amount,
user_entered)
select p_txn_id,
p_org_id,
-1,
cicd.cost_element_id,
cicd.level_type,
p_txn_action_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
sysdate,
p_item_id,
nvl(sum(cicd.item_cost),0),
'N',
0,
'N'
from cst_item_cost_details cicd
where organization_id = p_cost_org_id
and inventory_item_id = p_item_id
and cost_type_id = 1
group by cost_element_id, level_type;
0,--update mmt flag
0,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
l_error_num,
l_err_code,
l_err_msg);
Update mtl_material_transactions mmt
set (last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
actual_cost,
prior_cost,
new_cost,
variance_amount,
prior_costed_quantity,
quantity_adjusted) =
(select sysdate,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
sysdate,
mmt2.actual_cost,
mmt2.prior_cost,
mmt2.new_cost,
mmt2.variance_amount,
mmt2.prior_costed_quantity,
mmt2.quantity_adjusted
from mtl_material_transactions mmt2
where mmt2.transaction_id = p_txn_id
and mmt2.organization_id = p_org_id)
where mmt.transaction_id = p_parent_txn_id;
select pbp.borrow_transaction_id,
pbp.payback_quantity
from pjm_borrow_paybacks pbp
where pbp.payback_transaction_id = c_cur_txn_id;
select mcacd.transaction_id,
mcacd.cost_element_id,
mcacd.level_type,
mcacd.inventory_item_id,
mcacd.actual_cost,
mcacd.prior_cost,
mcacd.new_cost,
mcacd.layer_id
from mtl_cst_actual_cost_details mcacd
where mcacd.transaction_id = c_transaction_id
and mcacd.layer_id = i_from_layer;
select max(mcacd.actual_cost)
into l_cur_cost
from mtl_cst_actual_cost_details mcacd
where mcacd.level_type = l_level_type
and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
and mcacd.transaction_id = i_txn_id
and mcacd.layer_id = i_from_layer;
select l_temp_element_cost(l_index_counter)
into l_borrowed_cost
from dual;
update mtl_cst_actual_cost_details mcacd
set mcacd.payback_variance_amount = l_variance
where mcacd.transaction_id = i_txn_id
and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
and mcacd.level_type = l_level_type
and mcacd.layer_id = i_from_layer;
INSERT_ACCT_ERROR EXCEPTION;
INSERT_MCACD_ERROR EXCEPTION;
SELECT
DECODE(asset_inventory,1,0,1)
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = c_subinventory_code
AND organization_id = c_organization_id;
SELECT
mmt.inventory_item_id,
mmt.subinventory_code,
mmt.transfer_transaction_id,
mmt.organization_id,
mp.process_enabled_flag,
mmt.transfer_organization_id,
mpx.process_enabled_flag
INTO
l_item_id,
l_subinventory_code,
l_logical_txn_id,
l_parent_org_id,
l_parent_org_process_flag,
l_logical_org_id,
l_logical_org_process_flag
FROM
mtl_material_transactions mmt, mtl_parameters mpx, mtl_parameters mp
WHERE
mmt.transaction_id = p_parent_txn_id
AND mpx.organization_id = mmt.transfer_organization_id
AND mp.organization_id = mmt.organization_id
;
SELECT INVENTORY_ASSET_FLAG
INTO l_exp_item
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = l_item_id
AND ORGANIZATION_ID = l_parent_org_id;
SELECT decode(ASSET_INVENTORY, 1, 'N', 'Y')
INTO l_exp_flag
FROM MTL_SECONDARY_INVENTORIES
WHERE SECONDARY_INVENTORY_NAME = l_subinventory_code
AND ORGANIZATION_ID = l_parent_org_id;
UPDATE
MTL_MATERIAL_TRANSACTIONS
SET
COSTED_FLAG = NULL,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = p_user_id,
LAST_UPDATE_LOGIN = p_login_id,
REQUEST_ID = p_request_id,
PROGRAM_APPLICATION_ID = p_prog_app_id,
PROGRAM_ID = p_prog_id
WHERE
TRANSACTION_ID = l_logical_txn_id;
SELECT
PRIMARY_QUANTITY,
TRANSACTION_DATE,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
CURRENCY_CODE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_TYPE,
DISTRIBUTION_ACCOUNT_ID,
COST_GROUP_ID,
TRANSFER_PRICE -- Bug 5349860: umoogala
INTO
l_primary_quantity,
l_txn_date,
l_txn_src_id,
l_txn_src_typ_id,
l_txn_typ_id,
l_txn_act_id,
l_alt_curr,
l_curr_conv_rate,
l_curr_conv_date,
l_curr_conv_type,
l_debit_account,
l_logical_cost_group_id,
l_transfer_price -- Bug 5349860: umoogala
FROM
MTL_MATERIAL_TRANSACTIONS
WHERE
TRANSACTION_ID = l_logical_txn_id;
SELECT
PRIMARY_COST_METHOD
INTO
l_cost_method
FROM
MTL_PARAMETERS
WHERE
organization_id = l_logical_org_id;
SELECT SUM(ACTUAL_COST)
INTO l_actual_cost
FROM MTL_CST_ACTUAL_COST_DETAILS
WHERE transaction_id = p_parent_txn_id;
SELECT CURRENCY_CODE,
SET_OF_BOOKS_ID,
OPERATING_UNIT
INTO l_pri_curr,
l_set_of_books_id,
l_ou_id
FROM CST_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = l_logical_org_id;
SELECT CURRENCY_CODE
INTO l_sending_curr
FROM CST_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = l_parent_org_id;
SELECT
INTERORG_PAYABLES_ACCOUNT
INTO
l_credit_account
FROM
MTL_INTERORG_PARAMETERS
WHERE
FROM_ORGANIZATION_ID = l_parent_org_id
AND TO_ORGANIZATION_ID = l_logical_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,
insertion_flag,
variance_amount,
user_entered )
VALUES (
l_logical_txn_id,
l_logical_org_id,
l_layer_id,
1, -- All Costs into MTL
1, -- Level: TL
decode(l_pd_xfer_ind, 'Y', 17, 1), -- Bug 5349860: umoogala
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
sysdate,
l_item_id,
l_actual_cost,
'N',
0,
'N');
RAISE INSERT_MCACD_ERROR;
CSTPACDP.INSERT_ACCOUNT(
l_logical_org_id,
l_logical_txn_id,
l_item_id,
l_actual_cost * l_primary_quantity,
l_primary_quantity,
l_debit_account,
l_set_of_books_id,
l_dr_acct_line_type, -- Accounting_Line_Type
1, -- Cost_Element
NULL, -- Resource_Id
l_txn_date,
l_txn_src_id,
l_txn_src_typ_id,
l_pri_curr,
l_alt_curr,
l_curr_conv_date,
l_curr_conv_rate,
l_curr_conv_type,
1,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
l_err_num,
l_err_code,
l_err_msg );
RAISE INSERT_ACCT_ERROR;
CSTPACDP.INSERT_ACCOUNT(
l_logical_org_id,
l_logical_txn_id,
l_item_id,
-1 * l_actual_cost * l_primary_quantity,
-1 * l_primary_quantity,
l_credit_account,
l_set_of_books_id,
l_cr_acct_line_type, -- Accounting_Line_Type
1, -- Cost_Element
NULL, -- Resource_Id
l_txn_date,
l_txn_src_id,
l_txn_src_typ_id,
l_pri_curr,
l_alt_curr,
l_curr_conv_date,
l_curr_conv_rate,
l_curr_conv_type,
1, -- Actual_Flag
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
l_err_num,
l_err_code,
l_err_msg );
RAISE INSERT_ACCT_ERROR;
SELECT nvl(req_encumbrance_flag,'N') /*nvl(purch_encumbrance_flag, 'N')Bug 6469694*/
INTO l_encumbrance_flag
FROM FINANCIALS_SYSTEM_PARAMS_ALL
WHERE set_of_books_id = l_set_of_books_id
AND ( ( l_ou_id is not NULL AND org_id = l_ou_id ) OR
( l_ou_id is null ) );
RAISE INSERT_ACCT_ERROR;
UPDATE
MTL_MATERIAL_TRANSACTIONS
SET
COSTED_FLAG = NULL,
transaction_group_id = NULL,
ENCUMBRANCE_AMOUNT = l_enc_amount,
ENCUMBRANCE_ACCOUNT = l_enc_account,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = p_user_id,
LAST_UPDATE_LOGIN = p_login_id,
REQUEST_ID = p_request_id,
PROGRAM_APPLICATION_ID = p_prog_app_id,
PROGRAM_ID = p_prog_id
WHERE
TRANSACTION_ID = l_logical_txn_id;
WHEN INSERT_MCACD_ERROR THEN
x_err_num := -1;
x_err_code := 'CSTPAVCP.Cost_LogicalSOReceipt( '||to_char(l_stmt_num)||' ): '||'Error Inserting into MCACD: '||SQLERRM;
WHEN INSERT_ACCT_ERROR THEN
x_err_num := -1;
x_err_code := 'CSTPAVCP.Cost_LogicalSOReceipt( '||to_char(l_stmt_num)||' ): '||'Error in Insert_Account: ';
UPDATE mtl_material_transactions
SET costed_flag = 'E',
error_code = substrb(x_err_code,1,240),
error_explanation = substrb(x_err_msg,1,240),
request_id = p_request_id,
program_application_id = p_prog_app_id,
program_id = p_prog_id,
program_update_date = sysdate
WHERE transaction_id = l_trx_info.TRANSACTION_ID;
O_NO_UPDATE_MMT IN OUT NOCOPY NUMBER,
O_EXP_FLAG IN OUT NOCOPY NUMBER,
O_Err_Num OUT NOCOPY NUMBER,
O_Err_Code OUT NOCOPY VARCHAR2,
O_Err_Msg OUT NOCOPY VARCHAR2
) IS
l_err_num NUMBER;
l_txn_update_id NUMBER;
l_update_std NUMBER;
l_update_std := 0;
UPDATE mtl_material_transactions mmt
SET TRANSFER_PRIOR_COSTED_QUANTITY =
(SELECT
layer_quantity
FROM cst_quantity_layers cql
WHERE cql.organization_id = l_which_org
AND cql.inventory_item_id = i_item_id
AND cql.cost_group_id = l_which_cst_grp)
WHERE mmt.transaction_id = i_txn_id
AND EXISTS (
SELECT 'X'
FROM cst_quantity_layers cql
WHERE cql.organization_id = l_which_org
AND cql.inventory_item_id = i_item_id
AND cql.cost_group_id = l_which_cst_grp);
update mtl_material_transactions mmt
set TRANSFER_PRIOR_COSTED_QUANTITY = 0
where mmt.transaction_id = i_txn_id;
SELECT nvl(transportation_cost,0)
INTO l_trans_cost
FROM mtl_material_transactions
WHERE transaction_id = i_txn_id;
select item_cost, layer_id
into l_snd_txn_cost, l_from_layer_id
from cst_quantity_layers
where organization_id = l_from_org
and inventory_item_id = i_item_id
and cost_group_id = l_from_cost_grp;
Update mtl_material_transactions
Set transaction_cost = l_new_txn_cost
where transaction_id = i_txn_id;
Update mtl_material_transactions
Set transaction_cost = l_rcv_txn_cost
where transaction_id = i_txn_id;
fnd_file.put_line(fnd_file.log, 'inserting to MCTCD for txn: ' || i_txn_id || '. trxCost: ' || l_rcv_txn_cost);
insert into mtl_cst_txn_cost_details (
transaction_id,
organization_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,
transaction_cost,
new_average_cost,
percentage_change,
value_change)
values (i_txn_id,
l_to_org,
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,
l_rcv_txn_cost,
0,
0,
0);
Update mtl_material_transactions
Set transaction_cost = l_rcv_txn_cost
where transaction_id = i_txn_id;
* Bug 5631478: unique constraint violation because of this insert.
* Same insert is being done in compute_actual_cost procedure
*
IF g_debug = 'Y' THEN
fnd_file.put_line(fnd_file.log, 'inserting to MCACD for IO Issue to exp txn');
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;
SELECT um.uom_code,
rl.quantity,
rl.unit_price,
rd.budget_account_id,
nvl(rd.nonrecoverable_tax,0) /* Bug 6405593 */
INTO l_doc_uom_code,
l_doc_line_qty,
l_unit_price,
x_encumbrance_account,
l_non_recoverable_tax /* Bug 6405593 */
FROM po_req_distributions_all rd,
po_requisition_lines_all rl,
mtl_units_of_measure um
WHERE rd.requisition_line_id = p_req_line_id
and rd.requisition_line_id = rl.requisition_line_id
and rl.UNIT_MEAS_LOOKUP_CODE = um.unit_of_measure;
SELECT primary_uom_code
INTO l_primary_uom_code
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id;
SELECT
mmt.trx_source_line_id,
mmt.primary_quantity,
mmt.organization_id,
mmt.inventory_item_id,
mmt.transaction_action_id,
mmt.transaction_source_type_id,
mmt.transaction_type_id,
mmt.rcv_transaction_id
INTO
l_trx_source_line_id,
l_primary_qty,
l_organization_id,
l_item_id,
l_txn_action_id,
l_txn_src_type_id,
l_txn_type_id,
l_rcv_txn_id
FROM
MTL_MATERIAL_TRANSACTIONS mmt
WHERE
transaction_id = p_transaction_id;
SELECT sum(primary_quantity)
INTO l_total_primary_qty
from mtl_material_transactions
where transaction_action_id = l_txn_action_id
and transaction_source_type_id = l_txn_src_type_id
and transaction_type_id = l_txn_type_id
and trx_source_line_id = l_trx_source_line_id
and costed_flag IS NULL ;
SELECT
oel.SOURCE_DOCUMENT_LINE_ID
INTO
l_req_line_id
FROM
OE_ORDER_LINES_ALL oel,
cst_acct_info_v caiv
WHERE
oel.LINE_ID = l_trx_source_line_id
and oel.org_id = caiv.operating_unit
and caiv.organization_id = l_organization_id;
SELECT
REQUISITION_LINE_ID
INTO
l_req_line_id
FROM
RCV_TRANSACTIONS
WHERE
TRANSACTION_ID = l_rcv_txn_id;
| raise exception no_mcacd_for_hook. If the data inserted in |
| MCACD has the insertion flag as 'Y' and there are details |
| in CLCD we will raise exception insertion_flag_in_mcacd |
| |
| Parameters: i_txn_id: Transaction id |
| i_org_id: Organization id |
| i_layer_id: Layer id |
| |
| |
|===========================================================================*/
PROCEDURE validate_actual_cost_hook(
i_txn_id IN NUMBER,
i_org_id IN NUMBER,
i_layer_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_err_num NUMBER;
insertion_flag_in_mcacd EXCEPTION;
SELECT COUNT(*)
INTO l_test_mcacd
FROM MTL_CST_ACTUAL_COST_DETAILS MCACD
WHERE TRANSACTION_ID = i_txn_id
AND LAYER_ID = i_layer_id
AND ORGANIZATION_ID = i_org_id;
/* There shouldn't be details in CLCD if the insertion flag in MCACD is set as Y
for that cost element, it will suffice that one of the cost element violates
this condition to error out */
SELECT Count(*)
INTO l_test_clcd
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 Nvl(MCACD.insertion_flag,'N')='Y'
AND EXISTS (SELECT 'X'
FROM CST_LAYER_COST_DETAILS CLCD
WHERE MCACD.layer_id = CLCD.layer_id
AND MCACD.cost_element_id = CLCD.cost_element_id
AND MCACD.level_type = CLCD.level_type);
fnd_file.put_line(fnd_file.log, 'There should not be details in CLCD if the insertion flag in MCACD is set to Y in the hook');
raise insertion_flag_in_mcacd;
UPDATE mtl_material_transactions
SET costed_flag = 'E',
error_code = substrb(o_err_code,1,240),
error_explanation = substrb(o_err_msg,1,240),
request_id = i_req_id,
program_application_id = i_prg_appl_id,
program_id = i_prg_id,
program_update_date = sysdate
WHERE transaction_id = i_txn_id;
WHEN insertion_flag_in_mcacd THEN
rollback;
UPDATE mtl_material_transactions
SET costed_flag = 'E',
error_code = substrb(o_err_code,1,240),
error_explanation = substrb(o_err_msg,1,240),
request_id = i_req_id,
program_application_id = i_prg_appl_id,
program_id = i_prg_id,
program_update_date = sysdate
WHERE transaction_id = i_txn_id;
UPDATE mtl_material_transactions
SET costed_flag = 'E',
error_code = substrb(o_err_code,1,240),
error_explanation = substrb(o_err_msg,1,240),
request_id = i_req_id,
program_application_id = i_prg_appl_id,
program_id = i_prg_id,
program_update_date = sysdate
WHERE transaction_id = i_txn_id;