[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_err_num,
l_err_code,
l_err_msg);
/* Layer Cost Update is processed separately. There is no hook
available for this transaction. In contrast with average cost
update this function inserts distributions into MTA, and not
through the distribution processor
*/
l_stmt_num := 40;
CSTPLENG.layer_cost_update(
i_org_id,
i_txn_id,
i_layer_id,
i_cost_type,
i_item_id,
i_txn_qty,
i_txn_action_id,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
l_err_num,
l_err_code,
l_err_msg);
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
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;
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,
CILCD.cost_element_id,
CILCD.level_type,
CILCD.layer_cost,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
SYSDATE
FROM cst_inv_layer_cost_details CILCD
WHERE CILCD.inv_layer_id = (
SELECT MIN(inv_layer_id)
FROM cst_inv_layers
WHERE layer_id = l_layer_id
AND layer_quantity > 0
AND creation_date = (
SELECT MIN(creation_date)
FROM cst_inv_layers
WHERE layer_id = l_layer_id
AND layer_quantity > 0
)
);
so that MCACD and MCLACD can be updated, contrary to average
costing where the function is called only if the cost hook
does not exist */
/* Changes for VMI. Adding planning transfer transaction */
if (i_txn_action_id NOT IN (2,5,3,12,21,28,55)) then
l_stmt_num := 80;
/* Update the layer costs, CQL, CLCD and item costs for
processed transactions */
elsif (i_exp_item <> 1) then
/* when we process transfer org's txn(i.e. intransit txfr),
we need to use txfr_layer_id instead. */
if (i_org_id <> i_txn_org_id) then
l_layer_id := i_txfr_layer_id;
select primary_cost_method
into l_to_method
from mtl_parameters
where organization_id = i_txn_org_id;
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 decode(l_to_std_exp,1,1,decode(asset_inventory,1,0,1))
into l_to_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;
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;
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)
WHERE mmt.transaction_id = i_txn_id
AND EXISTS (
SELECT 'X'
FROM cst_quantity_layers cql
WHERE cql.layer_id = i_txfr_layer_id);
select decode(wac.class_type, 1, 0,
3, 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,
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;
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;
select primary_cost_method
into l_from_method
from mtl_parameters
where organization_id = l_from_org;
select primary_cost_method
into l_to_method
from mtl_parameters
where organization_id = l_to_org;
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 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 nvl(layer_id,0) into l_to_layer from cst_quantity_layers
where organization_id = l_to_org and inventory_item_id = i_item_id
and cost_group_id = l_to_cost_grp;
select nvl(layer_id,0)
into l_from_layer
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 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
FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into MCACD for std org using cost from CICD');
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;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into MCACD for std org using 0 cost');
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');
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;
/* 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 count(*) into l_mclacd_exists
from mtl_cst_layer_act_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = l_from_layer;
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 NVL(abs(sum(mclacd.actual_cost * mclacd.layer_quantity) / abs(o_txn_qty)),0)
into l_snd_txn_cost
from mtl_cst_layer_act_cost_details mclacd
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = l_from_layer;
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;
l_txn_update_id,l_from_org, l_to_org,
l_snd_qty,l_txfr_cost,l_trans_cost,l_conv_rate,l_um_rate,
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_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);
select nvl(layer_id,0) into l_to_layer from cst_quantity_layers
where organization_id = l_to_org and inventory_item_id = i_item_id
and cost_group_id = l_to_cost_grp;
fnd_file.put_line(fnd_file.log, 'Calling createlayers for the std org' || i_org_id || ': interorg rec : '|| o_interorg_rec || ':txn_update_id :'|| l_txn_update_id );
l_txn_update_id,
l_to_layer,
i_item_id,
abs(o_txn_qty),
i_cost_method,
i_txn_src_type,
i_txn_action_id,
0,
o_interorg_rec, --2280515 (anjgupta)
i_cost_type,
i_mat_ct_id,
i_avg_rates_id,
o_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);
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;
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;
* is expense in receiving org, we need to insert into MCACD from MCTCD and update MMT.
*/
elsif (i_txn_action_id = 3 and l_std_org = i_txfr_org_id) then
l_stmt_num := 230;
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;
FND_FILE.PUT_LINE(FND_FILE.LOG, to_char(l_stmt_num) || ' insert into MCACD from MCTCD with l_txfr_std_exp = 1');
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
FND_FILE.PUT_LINE(FND_FILE.LOG, to_char(l_stmt_num) || ' update MMT from MCACD with l_txfr_std_exp = 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 = l_txn_update_id
and cacd.organization_id = l_std_org
and cacd.layer_id = -1)
where mmt.transaction_id = l_txn_update_id;
* and item is expense in receiving org, insert into MCACD from MCTCD on both the
* sending and receiving transactions and update MMT on the receiving transaction.
*/
if (l_to_std_exp = 1) then
if (i_txn_org_id = l_std_org) then
select transfer_transaction_id
into l_txfr_txn_id
from mtl_material_transactions mmt
where mmt.transaction_id = i_txn_id;
FND_FILE.PUT_LINE(FND_FILE.LOG, to_char(l_stmt_num) || ' insert into MCACD from MCTCD with l_to_std_exp = 1');
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 mmt if this is the receiving transaction id
if (i_txn_org_id = l_std_org) then
FND_FILE.PUT_LINE(FND_FILE.LOG, to_char(l_stmt_num) || ' update MMT from MCACD with l_to_std_exp = 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 = 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 ledger_id
into l_snd_sob_id
/*from org_organization_definitions */
from cst_acct_info_v
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;
select ledger_id
into l_rcv_sob_id
/*from org_organization_definitions*/
from cst_acct_info_v
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;
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,
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;
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,
NVL((sum(mclacd.actual_cost * abs(mclacd.layer_quantity)) / abs(i_snd_qty)),0)*i_conv_rate/i_um_rate, -- modified for bug #3835412
0,
0,
0
from mtl_cst_layer_act_cost_details mclacd
where organization_id = i_org_id
and transaction_id = i_txn_id
group by cost_element_id,level_type;
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 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;
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));
l_no_update_qty NUMBER;
select transaction_type_id
into l_txn_type_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,
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,
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;
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;
select nvl(max(inv_layer_id), 0)
into l_inv_layer_id
from cst_inv_layers
where layer_id = l_from_layer;
if i_hook = 0 then -- insert MCACD only if there is no cost hook
l_stmt_num := 80;
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,
l_from_layer,
1,
1,
i_txn_action_id,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
i_item_id,
0,
0,
0,
'N',
0,
'N');
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,
cilcd.cost_element_id,
cilcd.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,
cilcd.layer_cost,
0,
NULL,
'N',
0,
'N'
from cst_inv_layer_cost_details cilcd
where layer_id = l_from_layer
and inv_layer_id = l_inv_layer_id;
end if; --- i checking layer cost and inserting MCACD
FND_FILE.PUT_LINE(FND_FILE.LOG,'MCACD inserted for trxn '
|| to_char(i_txn_id)
|| ':' || to_char(l_stmt_num));
CSTPAVCP.update_mmt(
i_org_id,
i_txn_id,
-1, -- i_txfr_txn_id
i_layer_id,
0, -- i_cost_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);
l_interorg_rec := 3; -- compute layer cost and insert mclacd only
SELECT cst_inv_layers_s.nextval
INTO l_inv_layer_id
FROM dual;
SELECT transaction_source_id
INTO l_src_id
FROM mtl_material_transactions
WHERE transaction_id = i_txn_id;
INSERT
INTO cst_inv_layers (
layer_id,
inv_layer_id,
organization_id,
inventory_item_id,
creation_quantity,
layer_quantity,
layer_cost,
create_transaction_id,
transaction_source_id,
transaction_action_id,
transaction_source_type_id,
transaction_source,
unburdened_cost,
burden_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES (l_from_layer,
l_inv_layer_id,
i_org_id,
i_item_id,
0,
0,
0,
i_txn_id,
l_src_id,
i_txn_action_id,
i_txn_src_type,
l_src_number,
0,
0,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate);
/* Delete cost details for the inventory layer from CILCD.
No rows should be present. Just a safety check
*/
DELETE
FROM cst_inv_layer_cost_details
WHERE inv_layer_id = l_inv_layer_id;
INSERT
INTO cst_inv_layer_cost_details (
layer_id,
inv_layer_id,
level_type,
cost_element_id,
layer_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES( l_from_layer,
l_inv_layer_id,
1,
1,
0,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate );
update mtl_cst_layer_act_cost_details mclacd
set actual_cost = layer_cost
where mclacd.transaction_id = i_txn_id
and mclacd.organization_id = i_org_id
and mclacd.layer_id = l_from_layer;
l_no_update_qty := 1;
l_no_update_qty := 0;
0, -- i_no_update_mmt
l_no_update_qty,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
l_err_num,
l_err_code,
l_err_msg);
1. Layer change and to expense sub: insert MCACD using 'from' layer's MCACD.
No layer consumption or layer cost impact.
2. Layer change and to asset sub: create MCTCD using 'from' layer's MCACD
then call API's to create new layer and to insert MCACD for 'to' layer
3. No layer change and to asset sub: create MCTCD using latest layer cost
of 'from' layer then call API's to create new layer and to insert
MCACD for 'to' layer.
*/
/*-------------------------------------------------------------
Scenario 1: Layer change and transfer to an expense sub
------------------------------------------------------------*/
/* Set l_exp_flag for the 'to' subinventory */
if l_exp_item = 1 or
l_to_exp = 1 then
l_exp_flag := 1;
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,
mcacd.cost_element_id,
mcacd.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,
mcacd.actual_cost,
mcacd.actual_cost,
mcacd.actual_cost,
'N',
0,
'N'
from mtl_cst_actual_cost_details mcacd
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = l_from_layer;
FND_FILE.PUT_LINE(FND_FILE.LOG,'MCTCD inserted for txn '
|| to_char(i_txn_id)
|| ',to layer '|| to_char(l_from_layer)
|| ',stmt ' || to_char(l_stmt_num));
select count(*) -- check for existing mctcd
into l_txn_cost_exist
from mtl_cst_txn_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_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)
select
i_txn_id,
i_org_id,
mcacd.cost_element_id,
mcacd.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
from mtl_cst_actual_cost_details mcacd
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = l_from_layer;
FND_FILE.PUT_LINE(FND_FILE.LOG,'MCTCD inserted for txn '
|| to_char(i_txn_id)
|| ',to layer '|| to_char(l_from_layer)
|| ',stmt ' || to_char(l_stmt_num));
0, -- i_no_update_mmt
0, -- i_no_update_qty
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
l_err_num,
l_err_code,
l_err_msg);
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,
1, -- material cost element
1, -- this level
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);
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)
select
i_txn_id,
i_org_id,
cilcd.cost_element_id,
cilcd.level_type,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
sysdate,
i_item_id,
cilcd.layer_cost
from cst_inv_layer_cost_details cilcd
where layer_id = l_from_layer
and inv_layer_id = l_inv_layer_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'MCTCD inserted for txn '
|| to_char(i_txn_id)
|| ',to layer '|| to_char(l_to_layer)
|| ',stmt ' || to_char(l_stmt_num));
0, -- i_no_update_mmt
0, -- i_no_update_qty
i_user_id,
i_login_id,
i_req_id,
i_prg_appl_id,
i_prg_id,
l_err_num,
l_err_code,
l_err_msg);
select transfer_transaction_id
into l_txf_txn_id
from mtl_material_transactions
where transaction_id = i_txn_id;
update mtl_material_transactions mmt1
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 mmt1.transaction_id = l_txf_txn_id
and mmt1.primary_quantity > 0;
select pbp.borrow_transaction_id,
pbp.payback_quantity
from pjm_borrow_paybacks pbp
where pbp.payback_transaction_id = c_cur_txn_id;
select mclacd.transaction_id,
mclacd.cost_element_id,
mclacd.level_type,
mclacd.inventory_item_id,
mclacd.actual_cost,
mclacd.layer_id,
mclacd.layer_quantity
from mtl_cst_layer_act_cost_details mclacd
where mclacd.transaction_id = c_transaction_id
and mclacd.layer_id = i_from_layer;
select loan_quantity
into l_loan_quantity
from pjm_borrow_transactions
where borrow_transaction_id = c_payback_rec.borrow_transaction_id;
select count(*)
into l_count
from mtl_cst_layer_act_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;
update mtl_cst_layer_act_cost_details mcacd
set mcacd.payback_variance_amount = mclacd_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;
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;
select primary_cost_method
into l_from_method
from mtl_parameters
where organization_id = l_from_org;
select primary_cost_method
into l_to_method
from mtl_parameters
where organization_id = l_to_org;
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;
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
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_rcv_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;
select nvl(layer_id,0)
into l_to_layer
from cst_quantity_layers
where organization_id = l_to_org
and inventory_item_id = i_item_id
and cost_group_id = l_to_cost_grp;