[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
inventory_item_id, organization_id,
nvl(cost_group_id,1), nvl(transfer_cost_group_id,1),
transaction_date,
primary_quantity, subinventory_code,
transfer_organization_id,
quantity_adjusted, nvl(transfer_cost,0), nvl(transportation_cost,0),
nvl(transportation_dist_account,-1),
transaction_action_id,
nvl(transaction_source_id,-1),
transfer_subinventory,
nvl(transfer_transaction_id,-1),
transaction_source_type_id, nvl(distribution_account_id,-1),
nvl(material_account, -1), nvl(material_overhead_account, -1),
nvl(resource_account, -1), nvl(outside_processing_account, -1),
nvl(overhead_account, -1),
nvl(encumbrance_account, -1), nvl(encumbrance_amount, 0),
currency_code,
nvl(currency_conversion_date,transaction_date),
nvl(currency_conversion_rate,-1) , currency_conversion_type,
logical_transaction,
transaction_type_id,
transfer_price, -- OPM INVCONV umoogala
trx_source_line_id, -- COGS OM Line ID
nvl(so_issue_account_type,1), -- 1=COGS, 2=Deferred COGS
cogs_recognition_percent,
rcv_transaction_id,
nvl(expense_account_id,-1)
into
l_item_id,
l_txn_org_id,
l_cost_grp_id,
l_txfr_cost_grp,
l_txn_date,
l_p_qty,
l_subinv,
l_txf_org_id,
l_qty_adj,
l_txf_cost,
l_trp_cost,
l_trp_acct,
l_txn_act_id,
l_txn_src_id,
l_txf_subinv,
l_txf_txn_id,
l_src_type_id,
l_dist_acct,
l_mat_acct,
l_mat_ovhd_acct,
l_res_acct,
l_osp_acct,
l_ovhd_acct,
l_enc_acct,
l_enc_amount,
l_alt_curr,
l_conv_date,
l_conv_rate,
l_conv_type,
l_logical_txn,
l_txn_type_id,
l_pd_txfr_price, -- OPM INVCONV umoogala
l_cogs_om_line_id,
l_so_issue_accounting,
l_cogs_percentage,
l_rcv_transaction_id,
l_onhand_var_acct
from mtl_material_transactions
where transaction_id = i_txn_id;
SELECT nvl(comms_nl_trackable_flag, 'N'), asset_creation_code
INTO l_nl_trackable, l_asset_creation_code
FROM mtl_system_items
WHERE inventory_item_id = l_item_id
AND organization_id = i_org_id;
select decode(encumbrance_reversal_flag,1,1,2,0,0),
organization_id,
primary_cost_method
into l_enc_rev,
l_enc_org_id,
l_enc_org_cost_method
from mtl_parameters
where ( organization_id = i_org_id
AND l_src_type_id = 1 )
OR (organization_id = i_org_id
AND l_txn_act_id = 29 and l_src_type_id = 7 )
OR ( organization_id = l_txn_org_id
AND l_txn_act_id = 3 and l_src_type_id in (7,8) and l_p_qty > 0)
OR ( organization_id = l_txf_org_id
AND l_txn_act_id = 3 and l_src_type_id in (7,8) and l_p_qty < 0)
OR (organization_id = l_txn_org_id
AND l_txn_act_id = 12 and l_src_type_id = 7)
OR (organization_id = l_txf_org_id
AND l_txn_act_id = 21 and l_src_type_id = 8);
select decode(encumbrance_reversal_flag,1,1,2,0,0),
organization_id,
primary_cost_method
into l_enc_rev,
l_enc_org_id,
l_enc_org_cost_method
from mtl_parameters
where organization_id = i_org_id;
select set_of_books_id,
operating_unit
into l_sob_id,
l_operating_unit
/*from org_organization_definitions */
from cst_organization_definitions
where organization_id = i_org_id;
select currency_code
into l_pri_curr
from gl_sets_of_books
where set_of_books_id = l_sob_id;
** 4) Average cost update **
** 5) Add if clause for drop ship/global procure **
** 6) rest of inventory transactions **
*******************************************************************/
if (i_comm_iss_flag = 1) THEN
-- call dedicated function for common issue to wip txn here.
CSTPACDP.comm_iss_to_wip(I_TXN_ID => i_txn_id,
I_COMM_ISS_FLAG => i_comm_iss_flag,
I_FLOW_SCHEDULE => i_flow_schedule,
I_ORG_ID => i_org_id,
I_ITEM_ID => l_item_id,
I_COST_GRP_ID => l_cost_grp_id,
I_TXFR_COST_GRP => l_txfr_cost_grp,
I_TXN_DATE => l_txn_date,
I_P_QTY => l_p_qty,
I_SUBINV => l_subinv,
I_SOB_ID => l_sob_id,
I_PRI_CURR => l_pri_curr,
I_ALT_CURR => l_alt_curr,
I_CONV_DATE => l_conv_date,
I_CONV_RATE => l_conv_rate,
I_CONV_TYPE => l_conv_type,
I_EXP_ITEM => i_exp_item,
I_TXF_SUBINV => l_txf_subinv,
I_TXN_ACT_ID => l_txn_act_id,
I_TXN_SRC_ID => l_txn_src_id,
I_SRC_TYPE_ID => l_src_type_id,
I_USER_ID => i_user_id,
I_LOGIN_ID => i_login_id,
I_REQ_ID => i_req_id,
I_PRG_APPL_ID => i_prg_appl_id,
I_PRG_ID => i_prg_id,
O_Error_Num => l_err_num,
O_Error_Code => l_err_code,
O_Error_Message => l_err_msg);
CSTPACDP.consigned_update_cost_txn(i_org_id, i_txn_id, l_cost_grp_id, l_item_id,
l_txn_date, l_p_qty, l_subinv, l_txn_act_id,
l_txn_src_id, l_src_type_id, l_dist_acct,
l_sob_id, l_pri_curr, l_alt_curr,
l_conv_date, l_conv_rate, l_conv_type,
i_exp_item, i_user_id, i_login_id,
i_req_id, i_prg_appl_id, i_prg_id, l_err_num,
l_err_code, l_err_msg);
SELECT nvl(req_encumbrance_flag,'N') /*nvl(purch_encumbrance_flag, 'N')Bug 6469694*/,
gsb.currency_code
INTO l_purch_encum_flag,
l_pri_curr
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP,
cst_organization_definitions cod,
gl_sets_of_books gsb
WHERE fsp.set_of_books_id = cod.set_of_books_id
and fsp.org_id= cod.operating_unit
and cod.organization_id = l_enc_org_id
and gsb.set_of_books_id = cod.set_of_books_id ;
SELECT po_distribution_id
INTO l_loc_po_distribution_id
FROM rcv_transactions
WHERE transaction_id =l_rcv_transaction_id;
select pod.quantity_ordered * (poll.price_override) + nvl(pod.nonrecoverable_tax,0)
into l_total_dist_amount
from po_line_locations_all poll,
po_distributions_all pod
where poll.po_header_id =pod.po_header_id
and poll.line_location_id =pod.line_location_id
and pod.po_distribution_id =l_loc_po_distribution_id;
Update mtl_cst_actual_cost_details
set transaction_costed_date = sysdate
where transaction_id = i_txn_id
and transaction_costed_date is NULL;
select decode(asset_inventory,1,0,1)
into l_exp_sub1
from mtl_secondary_inventories
where secondary_inventory_name = i_subinv
and organization_id = i_org_id;
select nvl(expense_account, -1)
into l_exp_acct1
from mtl_secondary_inventories
where secondary_inventory_name = i_subinv
and organization_id = i_org_id;
select nvl(expense_account,-1)
into l_exp_acct1
from mtl_parameters
where organization_id = i_org_id;
select nvl(expense_account, -1)
into l_exp_acct1
from cst_cost_group_accounts
where cost_group_id = i_cost_grp_id
and organization_id = i_org_id;
select decode(class_type,1,0,3,0,1), -1
into l_exp_sub2, l_exp_acct2
from wip_discrete_jobs wdj,
wip_accounting_classes wac
where
wdj.wip_entity_id = I_TXN_SRC_ID and
wdj.class_code = wac.class_code and
wdj.organization_id = wac.organization_id and
wdj.organization_id = i_org_id;
select decode(class_type,1,0,3,0,1), -1
into l_exp_sub2, l_exp_acct2
from wip_flow_schedules wdj,
wip_accounting_classes wac
where
wdj.wip_entity_id = I_TXN_SRC_ID and
wdj.class_code = wac.class_code and
wdj.organization_id = wac.organization_id and
wdj.organization_id = i_org_id;
select material_account, material_overhead_account, resource_account,
outside_processing_account, overhead_account, class_code
into l_mat_acct, l_mat_ovhd_acct, l_res_acct, l_osp_acct, l_ovhd_acct,
l_acct_class
from wip_discrete_jobs
where organization_id = i_org_id
and wip_entity_id = i_txn_src_id;
select material_account, material_overhead_account, resource_account,
outside_processing_account, overhead_account, class_code
into l_mat_acct, l_mat_ovhd_acct, l_res_acct, l_osp_acct, l_ovhd_acct,
l_acct_class
from wip_flow_schedules
where organization_id = i_org_id
and wip_entity_id = i_txn_src_id;
select decode(class_type, 4,1,0)
into l_exp_job
from wip_accounting_classes
where class_code = l_acct_class
and organization_id = i_org_id;
select material_account, material_overhead_account, resource_account,
outside_processing_account, overhead_account, class_code
into l_mat_acct, l_mat_ovhd_acct, l_res_acct, l_osp_acct, l_ovhd_acct,
l_acct_class
from wip_discrete_jobs
where organization_id = i_org_id
and wip_entity_id = i_txn_src_id;
SELECT material_account, material_overhead_account, resource_account,
outside_processing_account, overhead_account, class_code,
material_variance_account, resource_variance_account,
outside_proc_variance_account, overhead_variance_account
INTO l_mat_acct, l_mat_ovhd_acct, l_res_acct,
l_osp_acct, l_ovhd_acct, l_acct_class,
l_mat_var_acct, l_res_var_acct,
l_osp_var_acct, l_ovhd_var_acct
FROM wip_flow_schedules
WHERE organization_id = i_org_id
AND wip_entity_id = i_txn_src_id;
SELECT material_account, material_overhead_account, resource_account,
outside_processing_account, overhead_account, class_code
INTO l_mat_acct, l_mat_ovhd_acct, l_res_acct, l_osp_acct, l_ovhd_acct,
l_acct_class
FROM wip_flow_schedules
WHERE organization_id = i_org_id
AND wip_entity_id = i_txn_src_id;
select decode(class_type, 4,1,0)
into l_exp_job
from wip_accounting_classes
where class_code = l_acct_class
and organization_id = i_org_id;
select decode(asset_inventory,1,0,1)
into l_exp_sub
from mtl_secondary_inventories
where secondary_inventory_name = i_subinv
and organization_id = i_org_id;
select nvl(expense_account, -1)
into l_exp_acct
from mtl_secondary_inventories
where secondary_inventory_name = i_subinv
and organization_id = i_org_id;
select nvl(expense_account,-1)
into l_exp_acct
from mtl_parameters
where organization_id = i_org_id;
select nvl(expense_account, -1)
into l_exp_acct
from cst_cost_group_accounts
where cost_group_id = i_cost_grp_id
and organization_id = i_org_id;
select count(*)
into l_mat_ovhd_exists
from mtl_cst_actual_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id
and cost_element_id = 2
and level_type = 1;
select decode(asset_inventory,1,0,1)
into l_exp_sub1
from mtl_secondary_inventories
where secondary_inventory_name = i_subinv
and organization_id = i_org_id;
select decode(asset_inventory,1,0,1)
into l_exp_sub2
from mtl_secondary_inventories
where secondary_inventory_name = nvl(i_txf_subinv,i_subinv)
and organization_id = i_org_id;
select nvl(expense_account, -1)
into l_exp_acct1
from mtl_secondary_inventories
where secondary_inventory_name = i_subinv
and organization_id = i_org_id;
select nvl(expense_account, -1)
into l_exp_acct2
from mtl_secondary_inventories
where secondary_inventory_name = nvl(i_txf_subinv,i_subinv)
and organization_id = i_org_id;
select nvl(expense_account,-1)
into l_exp_acct1
from mtl_parameters
where organization_id = i_org_id;
select nvl(expense_account, -1)
into l_exp_acct1
from cst_cost_group_accounts
where cost_group_id = i_cost_grp_id
and organization_id = i_org_id;
select nvl(expense_account,-1)
into l_exp_acct2
from mtl_parameters
where organization_id = i_org_id;
select nvl(expense_account, -1)
into l_exp_acct2
from cst_cost_group_accounts
where cost_group_id = i_txfr_cost_grp
and organization_id = i_org_id;
select decode(asset_inventory,1,0,1)
into l_exp_sub
from mtl_secondary_inventories
where secondary_inventory_name = i_subinv
and organization_id = i_org_id;
select nvl(expense_account, -1)
into l_exp_acct
from mtl_secondary_inventories
where secondary_inventory_name = i_subinv AND
organization_id = i_org_id;
select nvl(expense_account,-1)
into l_exp_acct
from mtl_parameters
where organization_id = i_org_id;
select nvl(expense_account, -1)
into l_exp_acct
from cst_cost_group_accounts
where cost_group_id = i_cost_grp_id AND
organization_id = i_org_id;
SELECT decode(MOD(SUM(DECODE(MP.process_enabled_flag,'Y',1,2)), 2), 1, 'Y', 'N')
INTO l_pd_xfer_ind
FROM mtl_parameters mp, mtl_material_transactions mmt
WHERE mmt.transaction_id = i_txn_id
AND (mp.organization_id = mmt.organization_id
OR mp.organization_id = mmt.transfer_organization_id);
select nvl(expense_account, -1)
into l_exp_acct
from mtl_system_items
where inventory_item_id = i_item_id
and organization_id = i_org_id;
select ap_accrual_account
into l_acct
from mtl_parameters
where organization_id = i_org_id;
select rt.dropship_type_code
into l_drop_ship_type_code
from rcv_transactions rt, mtl_material_transactions mmt
where rt.transaction_id = mmt.rcv_transaction_id
and mmt.transaction_id = i_txn_id;
select nvl(msi.cost_of_sales_account, mp.cost_of_sales_account)
into l_acct
from mtl_system_items msi,
mtl_parameters mp
where msi.organization_id = i_org_id
and msi.inventory_item_id = i_item_id
and mp.organization_id = msi.organization_id;
SELECT deferred_cogs_account
INTO l_def_cogs_acct_id
FROM mtl_parameters
WHERE organization_id = i_org_id;
/* Only call Insert_OneSoIssue if the percentage is NULL. */
IF (i_cogs_percentage IS NULL) THEN
l_stmt_num := 45;
* inserting into the Revenue / COGS matching tables */
CST_RevenueCogsMatch_PVT.Insert_OneSoIssue(
p_api_version => 1,
p_user_id => i_user_id,
p_login_id => i_login_id,
p_request_id => i_req_id,
p_pgm_app_id => i_prg_appl_id,
p_pgm_id => i_prg_id,
x_return_status => l_return_status,
p_cogs_om_line_id => i_cogs_om_line_id,
p_cogs_acct_id => l_cogs_acct_id,
p_def_cogs_acct_id => l_def_cogs_acct_id,
p_mmt_txn_id => i_txn_id,
p_organization_id => i_org_id,
p_item_id => i_item_id,
p_transaction_date => i_txn_date,
p_cost_group_id => i_cost_grp_id,
p_quantity => (-1*i_p_qty)); -- track issue quantities as positive values
l_err_msg := 'Failure in procedure CST_RevenueCogsMatch_PVT.Insert_OneSoIssue()';
SELECT max(ool.reference_line_id)
INTO l_ref_om_line_id
FROM oe_order_lines_all ool,
cst_revenue_cogs_match_lines crcml
WHERE ool.line_id = i_cogs_om_line_id
AND ool.reference_line_id = crcml.cogs_om_line_id
AND crcml.pac_cost_type_id IS NULL;
select l_acct, l_acct, l_acct, l_acct, l_acct
into l_mat_acct, l_mat_ovhd_acct, l_res_acct, l_osp_acct, l_ovhd_acct
from dual;
select distribution_account
into l_acct
from mtl_generic_dispositions
where disposition_id = i_txn_src_id
and organization_id = i_org_id;
SELECT MMT.TRANSFER_ORGANIZATION_ID,
MMT.TRANSFER_TRANSACTION_ID,
MMT.TRANSFER_PRICE,
CASE
WHEN mpx.process_enabled_flag <> mp.process_enabled_flag
THEN
'Y'
ELSE
'N'
END
INTO l_txfr_org_id,
l_txfr_txn_id,
l_transfer_price,
l_pd_xfer_ind
FROM MTL_MATERIAL_TRANSACTIONS mmt,
MTL_PARAMETERS mp, MTL_PARAMETERS mpx
WHERE mmt.transaction_id = i_txn_id
AND mp.organization_id = mmt.organization_id
AND mpx.organization_id = mmt.transfer_organization_id;
SELECT interorg_receivables_account,
interorg_profit_account -- Bug 5349860: umoogala
INTO l_acct,
l_io_profit_acct -- Bug 5349860: umoogala
FROM MTL_INTERORG_PARAMETERS
WHERE from_organization_id = i_org_id
AND to_organization_id = l_txfr_org_id;
SELECT DISTRIBUTION_ACCOUNT_ID
INTO l_acct
FROM MTL_MATERIAL_TRANSACTIONS
WHERE transaction_id = l_txfr_txn_id;
insert_account(i_org_id, i_txn_id, i_item_id, -1 * i_p_qty * l_transfer_price,
-1 * i_p_qty, l_acct, i_sob_id, l_acct_line_type,
NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
i_pri_curr, i_alt_curr, i_conv_date, i_conv_rate, i_conv_type,
1, i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
l_err_num, l_err_code, l_err_msg);
SELECT SUM(NVL(base_transaction_value,0))
INTO l_value
FROM mtl_transaction_accounts
WHERE transaction_id = i_txn_id
AND organization_id = i_org_id
;
insert_account(i_org_id, i_txn_id, i_item_id, -1 * l_value,
sign(-1 * l_value) * abs(i_p_qty), l_acct, i_sob_id,
34, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
i_pri_curr, i_alt_curr, i_conv_date, i_conv_rate, i_conv_type,
1, i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
l_err_num, l_err_code, l_err_msg);
select count(*)
into l_mat_ovhd_exists
from mtl_cst_actual_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id
and cost_element_id = 2
and level_type = 1;
SELECT MOD(SUM(DECODE(MP.process_enabled_flag,'Y',1,2)), 2)
INTO l_pd_txfr_ind
FROM mtl_parameters mp
WHERE mp.organization_id = i_txn_org_id
OR mp.organization_id = i_txf_org_id;
SELECT nvl(ap_accrual_account, -1)
INTO l_inv_ap_accrual_acct
FROM mtl_parameters
WHERE organization_id = i_org_id;
SELECT to_number(org_information3)
INTO l_org_ou
FROM hr_organization_information
WHERE org_information_context = 'Accounting Information'
AND organization_id = i_txn_org_id;
SELECT to_number(org_information3)
INTO l_txf_org_ou
FROM hr_organization_information
WHERE org_information_context = 'Accounting Information'
AND organization_id = i_txf_org_id;
select nvl(expense_account, -1)
into l_exp_acct
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_exp_sub
from mtl_secondary_inventories
where secondary_inventory_name = l_subinv
and organization_id = i_org_id;
select nvl(expense_account, l_exp_acct)
into l_exp_acct
from mtl_secondary_inventories
where secondary_inventory_name = l_subinv
and organization_id = i_org_id;
select nvl(expense_account,l_exp_acct)
into l_exp_acct
from mtl_parameters
where organization_id = i_org_id;
select nvl(expense_account, l_exp_acct)
into l_exp_acct
from cst_cost_group_accounts
where cost_group_id = i_cost_grp_id
and organization_id = i_org_id;
select nvl(MMT.intransit_account, MIP.intransit_inv_account),
MIP.interorg_transfer_cr_account,
MIP.interorg_receivables_account,
MIP.interorg_payables_account,
MIP.interorg_price_var_account,
MIP.interorg_profit_account -- OPM INVCONV umoogala
into
l_io_inv_acct,
l_io_txfr_acct,
l_io_rec_acct,
l_io_pay_acct,
l_io_ppv_acct,
l_io_profit_acct -- OPM INVCONV umoogala
from mtl_interorg_parameters MIP, mtl_material_transactions MMT
where MIP.from_organization_id = l_from_org
and MIP.to_organization_id = l_to_org
and MMT.transaction_id = i_txn_id;
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 decode(inventory_asset_flag, 'Y', 0,1), nvl(expense_account, -1)
into l_std_exp_item, l_std_exp_acct
from mtl_system_items
where inventory_item_id = i_item_id
and organization_id = l_std_org;
SELECT decode(nvl(cost_group_accounting,0),0,0,
decode(nvl(project_reference_enabled,0),1,1,0))
INTO l_pjm_flg
FROM MTL_PARAMETERS
WHERE organization_id = l_std_org;
select decode(asset_inventory,1,0,1)
into l_std_exp_sub
from mtl_secondary_inventories msi
where msi.organization_id = l_std_org
and secondary_inventory_name = l_subinv;
select material_account, material_overhead_account, resource_account,
outside_processing_account, overhead_account,
nvl(expense_account, l_std_exp_acct)
into l_std_mat_acct, l_std_mat_ovhd_acct, l_std_res_acct,
l_std_osp_acct, l_std_ovhd_acct,
l_std_exp_acct
from mtl_secondary_inventories msi
where msi.organization_id = l_std_org
and secondary_inventory_name = l_subinv;
select decode(l_std_org,l_from_org,l_from_cost_grp,l_to_cost_grp)
into l_cg_id
from dual;
select material_account, material_overhead_account, resource_account,
outside_processing_account, overhead_account,
nvl(expense_account, l_std_exp_acct)
into l_std_mat_acct, l_std_mat_ovhd_acct, l_std_res_acct,
l_std_osp_acct, l_std_ovhd_acct,
l_std_exp_acct
from mtl_parameters
where organization_id = l_std_org;
select material_account, material_overhead_account, resource_account,
outside_processing_account, overhead_account,
nvl(expense_account, l_std_exp_acct)
into l_std_mat_acct, l_std_mat_ovhd_acct, l_std_res_acct,
l_std_osp_acct, l_std_ovhd_acct,
l_std_exp_acct
from cst_cost_group_accounts
where organization_id = l_std_org
and cost_group_id = l_cg_id;
insert_account(l_from_org, i_txn_id, i_item_id, -1*l_value,
l_snd_qty, l_acct, l_snd_sob_id, 12, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_snd_curr, NULL, NULL, NULL, NULL,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);
insert_account(l_from_org, i_txn_id, i_item_id, l_value,
-1 * l_snd_qty, l_acct, l_snd_sob_id, 10, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_snd_curr, NULL, NULL, NULL, NULL,1,
i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
l_err_num, l_err_code, l_err_msg);
SELECT SUM(NVL(base_transaction_value,0))
INTO l_value
FROM mtl_transaction_accounts
WHERE transaction_id = i_txn_id
AND organization_id = l_from_org
;
insert_account(l_from_org, i_txn_id, i_item_id, -1 * l_value,
l_snd_qty, l_acct, l_snd_sob_id,
G_INTERORG_PROFIT_ACCT, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_snd_curr, NULL, NULL, NULL, NULL,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);
insert_account(l_from_org, i_txn_id, i_item_id, -1 * l_value,
l_snd_qty, l_acct, l_snd_sob_id, 11, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_snd_curr, NULL, NULL, NULL, NULL,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);
insert_account(l_from_org, i_txn_id, i_item_id, -1 * l_value,
l_snd_qty, l_acct, l_snd_sob_id, 12, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_snd_curr, NULL, NULL, NULL, NULL,1,
i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
l_err_num, l_err_code, l_err_msg);
select nvl(sum(base_transaction_value),0)
into l_value
from mtl_transaction_accounts
where transaction_id = i_txn_id
and organization_id = l_from_org;
insert_account(l_from_org, i_txn_id, i_item_id, -1 * l_value,
-1 * l_snd_qty, l_acct, l_snd_sob_id, 10, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_snd_curr, NULL, NULL, NULL, NULL,1,
i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
l_err_num, l_err_code, l_err_msg);
SELECT SUM(NVL(base_transaction_value,0))
INTO l_value
FROM mtl_transaction_accounts
WHERE transaction_id = i_txn_id
AND organization_id = l_from_org
;
insert_account(l_from_org, i_txn_id, i_item_id, -1 * l_value,
-1 * l_snd_qty, l_acct, l_snd_sob_id,
G_INTERORG_PROFIT_ACCT, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_snd_curr, NULL, NULL, NULL, NULL,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);
insert_account(l_to_org, i_txn_id, i_item_id, -1 * l_value,
-1 * l_rcv_qty, l_acct, l_rcv_sob_id, 2, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_rcv_curr, l_snd_curr,
l_conv_date, l_conv_rate, l_curr_type,1,
i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
l_err_num, l_err_code, l_err_msg);
insert_account(l_to_org, i_txn_id, i_item_id, -1 * l_value,
-1 * l_rcv_qty, l_acct, l_rcv_sob_id, 9, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_rcv_curr, l_snd_curr,
l_conv_date, l_conv_rate, l_curr_type,1,
i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
l_err_num, l_err_code, l_err_msg);
insert_account(l_to_org, i_txn_id, i_item_id, -1 * l_value,
l_rcv_qty, l_acct, l_rcv_sob_id, 12, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_rcv_curr, l_snd_curr,
l_conv_date, l_conv_rate, l_curr_type,1,
i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
l_err_num, l_err_code, l_err_msg);
select nvl(sum(mctcd.transaction_cost),0)
into l_ppv
from mtl_cst_txn_cost_details mctcd
where mctcd.transaction_id = i_txn_id
and mctcd.organization_id = l_std_org
;
select l_ppv - nvl(sum(mcacd.actual_cost),0)
into l_ppv
from mtl_cst_actual_cost_details mcacd
where mcacd.transaction_id = i_txn_id
and mcacd.organization_id = l_std_org;
select l_ppv - nvl(sum(mcacd.actual_cost),0)
into l_ppv
from mtl_cst_actual_cost_details mcacd
where mcacd.transaction_id = i_txn_id
and mcacd.organization_id = l_std_org
and not (cost_element_id = 2
and level_type = 1);
update mtl_material_transactions
set variance_amount = l_ppv * abs(l_rcv_qty)
where transaction_id = i_txn_id
and organization_id = l_std_org;
insert_account(l_std_org, i_txn_id, i_item_id,
l_ppv * abs(l_rcv_qty),
l_rcv_qty, l_acct, l_rcv_sob_id, 6, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_rcv_curr, l_snd_curr, l_conv_date, l_conv_rate,
l_curr_type,1,
i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
l_err_num, l_err_code, l_err_msg);
select nvl(material_account,-1), nvl(material_overhead_account,-1)
into l_acct, l_moh_offset_acct
from mtl_parameters
where organization_id = i_org_id;
select nvl(material_account,-1), nvl(material_overhead_account,-1)
into l_acct, l_moh_offset_acct
from cst_cost_group_accounts
where cost_group_id = l_to_cost_grp
and organization_id = i_org_id;
select decode(i_exp_item, 0, nvl(l_exp_sub,0), i_exp_item)
into l_exp
from dual;
fnd_file.put_line(fnd_file.log,'calling insert_account for booking INV with value/qty: ' || l_value ||'/'||l_rcv_qty);
insert_account(l_to_org, i_txn_id, i_item_id, l_value,
l_rcv_qty, l_acct, l_rcv_sob_id, l_acct_line_type, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_rcv_curr, l_snd_curr,
l_conv_date, l_conv_rate, l_curr_type,1,
i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
l_err_num, l_err_code, l_err_msg);
select nvl(sum(mctcd.transaction_cost),0)
into l_ppv
from mtl_cst_txn_cost_details mctcd
where mctcd.transaction_id = i_txn_id
and mctcd.organization_id = l_std_org;
select l_ppv - nvl(sum(mcacd.actual_cost),0)
into l_ppv
from mtl_cst_actual_cost_details mcacd
where mcacd.transaction_id = i_txn_id
and mcacd.organization_id = l_std_org;
select l_ppv - nvl(sum(mcacd.actual_cost),0)
into l_ppv
from mtl_cst_actual_cost_details mcacd
where mcacd.transaction_id = i_txn_id
and mcacd.organization_id = l_std_org
and not (cost_element_id = 2
and level_type = 1);
update mtl_material_transactions
set variance_amount = l_ppv * abs(l_rcv_qty)
where transaction_id = i_txn_id
and organization_id = l_std_org;
insert_account(l_std_org, i_txn_id, i_item_id,
l_ppv * abs(l_rcv_qty),
l_rcv_qty, l_acct, l_rcv_sob_id, 6, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_rcv_curr, l_snd_curr, l_conv_date, l_conv_rate,
l_curr_type,1,
i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
l_err_num, l_err_code, l_err_msg);
select nvl(sum(base_transaction_value), 0)
into l_value
from mtl_transaction_accounts
where transaction_id = i_txn_id
and organization_id = l_to_org
and accounting_line_type = 3
and cost_element_id = 2;
insert_account(l_to_org, i_txn_id, i_item_id, -1 * l_value,
l_rcv_qty, l_moh_offset_acct, l_rcv_sob_id, 1, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_rcv_curr, l_snd_curr,
l_conv_date, l_conv_rate, l_curr_type,1,
i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
l_err_num, l_err_code, l_err_msg);
select sum(base_transaction_value)
into l_value
from mtl_transaction_accounts
where transaction_id = decode(i_txn_act_id, 3, i_txf_txn_id,i_txn_id)
and organization_id = l_from_org
and accounting_line_type = 10;
insert_account(l_to_org, i_txn_id, i_item_id, -1 * l_value,
-1 * l_rcv_qty, l_acct, l_rcv_sob_id, 2, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_rcv_curr, l_snd_curr,
l_conv_date, l_conv_rate, l_curr_type,1,
i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
l_err_num, l_err_code, l_err_msg);
insert_account(l_to_org, i_txn_id, i_item_id, -1 * l_value,
-1 * l_rcv_qty, l_acct, l_rcv_sob_id, 9, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_rcv_curr, l_snd_curr,
l_conv_date, l_conv_rate, l_curr_type,1,
i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
l_err_num, l_err_code, l_err_msg);
insert_account(l_to_org, i_txn_id, i_item_id, -1 * l_value,
l_rcv_qty, l_acct, l_rcv_sob_id, 12, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_rcv_curr, l_snd_curr,
l_conv_date, l_conv_rate, l_curr_type,1,
i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
l_err_num, l_err_code, l_err_msg);
select nvl(material_account,-1)
into l_acct
from mtl_parameters
where organization_id = i_org_id;
select nvl(material_account,-1)
into l_acct
from cst_cost_group_accounts
where organization_id = i_org_id
and cost_group_id = l_to_cost_grp;
insert_account(l_to_org, i_txn_id, i_item_id, -1 * l_value,
-1 * l_rcv_qty, l_acct, l_rcv_sob_id, 1, NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_rcv_curr, NULL,
NULL, NULL, NULL, 1,
i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
l_err_num, l_err_code, l_err_msg);
select nvl(material_account,-1)
into l_acct
from mtl_parameters
where organization_id = i_org_id;
select nvl(material_account,-1)
into l_acct
from cst_cost_group_accounts
where organization_id = i_org_id
and cost_group_id = l_to_cost_grp;
insert_account(l_to_org, i_txn_id, i_item_id, l_value,
l_rcv_qty, l_acct, l_rcv_sob_id, 14,
NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
l_rcv_curr, NULL, NULL, NULL, NULL,
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);
FND_MESSAGE.set_name('BOM', 'CST_UPDATE_ZERO_QTY');
select count(*)
into l_acct_exist
from mtl_parameters
where organization_id = i_org_id;
select count(*)
into l_acct_exist
from cst_cost_group_accounts
where organization_id = i_org_id
and cost_group_id = i_cost_grp_id;
select nvl(material_account,-1),
nvl(material_overhead_account,-1),
nvl(resource_account,-1),
nvl(outside_processing_account,-1),
nvl(overhead_account,-1)
into l_inv_mat_acct,
l_inv_mat_ovhd_acct,
l_inv_res_acct,
l_inv_osp_acct,
l_inv_ovhd_acct
from mtl_parameters
where organization_id = i_org_id;
select nvl(material_account,-1),
nvl(material_overhead_account,-1),
nvl(resource_account,-1),
nvl(outside_processing_account,-1),
nvl(overhead_account,-1)
into l_inv_mat_acct,
l_inv_mat_ovhd_acct,
l_inv_res_acct,
l_inv_osp_acct,
l_inv_ovhd_acct
from cst_cost_group_accounts
where organization_id = i_org_id
and cost_group_id = i_cost_grp_id;
select count(*)
into l_ele_exist
from mtl_cst_actual_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id;
select (sum(new_cost) - sum(prior_cost)),sum(variance_amount)
,sum(onhand_variance_amount)
into l_cost,l_var,l_onhand_var
from mtl_cst_actual_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id
and cost_element_id = cost_element;
select decode(cost_element, 1, l_inv_mat_acct,
2, l_inv_mat_ovhd_acct,
3, l_inv_res_acct,
4, l_inv_osp_acct,
5, l_inv_ovhd_acct)
into l_acct
from dual;
insert_account(i_org_id, i_txn_id, i_item_id, i_qty * l_cost,
sign(i_qty * l_cost) * abs(i_qty)/*modified for bug#4005770*//*i_qty*/, l_acct, i_sob_id, 1,
cost_element, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
i_pri_curr, i_alt_curr, i_conv_date, i_conv_rate, i_conv_type,
1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
l_err_num, l_err_code, l_err_msg);
/* Added the decode for BUG: 1107767. Avg cost update through the interface needs all the accounts
in MMT to be specified, even if only the material cost element is getting affected */
if (l_acct = -1) then
select decode(cost_element, 1, i_mat_acct,
2, decode(i_mat_ovhd_acct,-1, i_mat_acct, i_mat_ovhd_acct),
3, decode(i_res_acct,-1, i_mat_acct, i_res_acct),
4, decode(i_osp_acct,-1, i_mat_acct, i_osp_acct),
5, decode(i_ovhd_acct,-1, i_mat_acct, i_ovhd_acct))
into l_acct
from dual;
insert_account(i_org_id, i_txn_id, i_item_id, (i_qty * l_cost) - l_var-l_onhand_var,
i_qty, l_acct, i_sob_id, 2,
cost_element, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
i_pri_curr, i_alt_curr, i_conv_date, i_conv_rate, i_conv_type,
1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
l_err_num, l_err_code, l_err_msg);
insert_account(i_org_id, i_txn_id, i_item_id, l_onhand_var,
i_qty, l_acct, i_sob_id, 20,
cost_element, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
i_pri_curr, i_alt_curr, i_conv_date, i_conv_rate, i_conv_type,
1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
l_err_num, l_err_code, l_err_msg);
select nvl(sum(variance_amount),0)
into l_var
from mtl_cst_actual_cost_details cacd
where transaction_id = i_txn_id
and organization_id = i_org_id;
select nvl(average_cost_var_account,-1)
into l_acct
from mtl_parameters
where organization_id = i_org_id;
select nvl(average_cost_var_account,-1)
into l_acct
from cst_cost_group_accounts
where organization_id = i_org_id
and cost_group_id = i_cost_grp_id;
insert_account(i_org_id, i_txn_id, i_item_id, l_var,
i_qty, l_acct, i_sob_id, 13,
NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
i_pri_curr, i_alt_curr, i_conv_date, i_conv_rate, i_conv_type,
1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
l_err_num, l_err_code, l_err_msg);
select nvl(expense_account,-1)
into l_exp_acct
from mtl_parameters
where organization_id = i_org_id;
select nvl(expense_account, -1)
into l_exp_acct
from cst_cost_group_accounts
where cost_group_id = i_cost_grp_id AND
organization_id = i_org_id;
select primary_cost_method, -- Standard costing orgs will have to call the new inventory_accounts_std() procedure
deferred_cogs_account
into l_cost_method,
l_def_cogs_acct_id
from mtl_parameters
where organization_id = i_org_id;
SELECT max(ool.reference_line_id)
INTO l_ref_om_line_id
FROM oe_order_lines_all ool,
cst_revenue_cogs_match_lines crcml
WHERE ool.line_id = i_cogs_om_line_id
AND ool.reference_line_id = crcml.cogs_om_line_id
AND crcml.pac_cost_type_id IS NULL
AND i_txn_type_id = 16;
/* Only call Insert_OneSoIssue if the percentage is NULL. */
IF (i_cogs_percentage IS NULL) THEN
l_stmt_num := 60;
* inserting into the Revenue / COGS matching tables */
CST_RevenueCogsMatch_PVT.Insert_OneSoIssue(
p_api_version => 1,
p_user_id => i_user_id,
p_login_id => i_login_id,
p_request_id => i_req_id,
p_pgm_app_id => i_prg_appl_id,
p_pgm_id => i_prg_id,
x_return_status => l_return_status,
p_cogs_om_line_id => i_cogs_om_line_id,
p_cogs_acct_id => l_acct,
p_def_cogs_acct_id => l_def_cogs_acct_id,
p_mmt_txn_id => i_txn_id,
p_organization_id => i_org_id,
p_item_id => i_item_id,
p_transaction_date => i_txn_date,
p_cost_group_id => i_cost_grp_id,
p_quantity => (-1*i_p_qty)); /* track issue quantities as positive values */
l_err_msg := 'Failure in procedure CST_RevenueCogsMatch_PVT.Insert_OneSoIssue()';
procedure consigned_update_cost_txn(
I_ORG_ID IN NUMBER,
I_TXN_ID IN NUMBER,
I_COST_GRP_ID IN NUMBER,
I_ITEM_ID IN NUMBER,
I_TXN_DATE IN DATE,
I_P_QTY IN NUMBER,
I_SUBINV IN VARCHAR2,
I_TXN_ACT_ID IN NUMBER,
I_TXN_SRC_ID IN NUMBER,
I_SRC_TYPE_ID IN NUMBER,
I_DIST_ACCT IN NUMBER,
I_SOB_ID IN NUMBER,
I_PRI_CURR IN VARCHAR2,
I_ALT_CURR IN VARCHAR2,
I_CONV_DATE IN DATE,
I_CONV_RATE IN NUMBER,
I_CONV_TYPE IN VARCHAR2,
I_EXP_ITEM IN NUMBER,
I_USER_ID IN NUMBER,
I_LOGIN_ID IN NUMBER,
I_REQ_ID IN NUMBER,
I_PRG_APPL_ID IN NUMBER,
I_PRG_ID IN NUMBER,
O_Error_Num OUT NOCOPY NUMBER,
O_Error_Code OUT NOCOPY VARCHAR2,
O_Error_Message OUT NOCOPY VARCHAR2
)IS
l_stmt_num NUMBER;
select nvl(retroprice_adj_account_id,-1)
into l_retro_acct_id
from rcv_parameters
where organization_id = i_org_id;
l_err_msg := 'CSTPACDP.consigned_update_cost_txn (10): '||
'RCV_PARAMETERS.RETROOPRICE_ADJ_ACCOUNT_ID must not be NULL';
select quantity_adjusted, transaction_cost
into l_qty_adj, l_txn_cost
from mtl_material_transactions
where transaction_id = i_txn_id;
insert_account(i_org_id, i_txn_id, i_item_id, l_qty_adj * l_txn_cost,
l_qty_adj, l_retro_acct_id, i_sob_id, 32,
1 /*cost_element*/, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
i_pri_curr, i_alt_curr, i_conv_date, i_conv_rate, i_conv_type,
1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
l_err_num, l_err_code, l_err_msg);
O_error_message := 'CSTPACDP.consigned_update_cost_txn (' || to_char(l_stmt_num) ||
'): ' || substr(SQLERRM,1,180);
END consigned_update_cost_txn;
select decode(i_exp_item, 0, nvl(i_exp_subinv,0), i_exp_item)
into l_exp
from dual;
select count(*)
into l_ele_exist
from mtl_cst_actual_cost_details mcacd
where mcacd.transaction_id = i_txn_id
and mcacd.organization_id = i_org_id
and mcacd.layer_id = l_layer_id;
SELECT decode(nvl(cost_group_accounting,0),0,0,
decode(nvl(project_reference_enabled,0),1,1,0))
INTO l_pjm_flg
FROM MTL_PARAMETERS
WHERE organization_id = i_org_id;
SELECT
nvl(material_account, -1),
nvl(material_overhead_account, -1),
nvl(resource_account, -1),
nvl(outside_processing_account, -1),
nvl(overhead_account, -1)
INTO l_mat_acct, l_mat_ovhd_acct, l_res_acct, l_osp_acct, l_ovhd_acct
FROM mtl_secondary_inventories
WHERE organization_id = i_org_id
and secondary_inventory_name = i_subinv;
SELECT
nvl(material_account, -1),
nvl(material_overhead_account, -1),
nvl(resource_account, -1),
nvl(outside_processing_account, -1),
nvl(overhead_account, -1)
INTO l_mat_acct, l_mat_ovhd_acct, l_res_acct, l_osp_acct, l_ovhd_acct
FROM mtl_parameters
WHERE organization_id = i_org_id;
SELECT count(*)
INTO l_ccga_count
FROM cst_cost_group_accounts
WHERE cost_group_id = l_cost_grp_id
AND organization_id = i_org_id;
SELECT
nvl(material_account, -1),
nvl(material_overhead_account, -1),
nvl(resource_account, -1),
nvl(outside_processing_account, -1),
nvl(overhead_account, -1)
INTO l_mat_acct, l_mat_ovhd_acct, l_res_acct, l_osp_acct, l_ovhd_acct
FROM cst_cost_group_accounts
WHERE organization_id = i_org_id
AND cost_group_id = l_cost_grp_id;
select sum(actual_cost), nvl(sum(variance_amount),0)
into l_cost, l_var
from mtl_cst_actual_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = l_layer_id
and cost_element_id = cost_element;
select decode(l_exp, 1, i_exp_acct,
decode(cost_element, 1, l_mat_acct,
2, l_mat_ovhd_acct,
3, l_res_acct,
4, l_osp_acct,
5, l_ovhd_acct))
into l_acct
from dual;
insert_account(i_org_id, i_txn_id, i_item_id, i_p_qty * l_cost - l_var,
i_p_qty, l_acct, i_sob_id, l_acct_line_type,
cost_element, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
i_pri_curr, i_alt_curr, i_conv_date, i_conv_rate, i_conv_type,
1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
l_err_num, l_err_code, l_err_msg);
select decode(i_exp_item, 0, nvl(i_exp_subinv,0), i_exp_item)
into l_exp
from dual;
select count(*)
into l_acct_exist
from mtl_parameters
where organization_id = i_org_id;
select count(*)
into l_acct_exist
from cst_cost_group_accounts
where organization_id = i_org_id
and cost_group_id = i_cost_grp_id;
select nvl(material_account,-1),
nvl(material_overhead_account,-1),
nvl(resource_account,-1),
nvl(outside_processing_account,-1),
nvl(overhead_account,-1)
into l_mat_acct,
l_mat_ovhd_acct,
l_res_acct,
l_osp_acct,
l_ovhd_acct
from mtl_parameters
where organization_id = i_org_id;
select nvl(material_account,-1),
nvl(material_overhead_account,-1),
nvl(resource_account,-1),
nvl(outside_processing_account,-1),
nvl(overhead_account,-1)
into l_mat_acct,
l_mat_ovhd_acct,
l_res_acct,
l_osp_acct,
l_ovhd_acct
from cst_cost_group_accounts
where organization_id = i_org_id
and cost_group_id = l_cost_grp_id;
select layer_id
into l_layer_id
from cst_quantity_layers
where inventory_item_id = i_item_id
and organization_id = i_org_id
and cost_group_id = i_cost_grp_id;
select count(*)
into l_ele_exist
from mtl_cst_actual_cost_details mcacd
where mcacd.transaction_id = i_txn_id
and mcacd.organization_id = i_org_id
and mcacd.layer_id = l_layer_id
and nvl(mcacd.transaction_action_id,-99) =
nvl(decode(i_comm_iss_flag,1,
i_cost_txn_action_id,mcacd.transaction_action_id),-99);
select mmt.transaction_type_id, mmt.transaction_action_id
into l_txn_type, l_txn_action_id
from mtl_material_transactions mmt
where mmt.transaction_id = i_txn_id;
select sum(actual_cost), nvl(sum(variance_amount),0)
into l_cost, l_var
from mtl_cst_actual_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = l_layer_id
and cost_element_id = cost_element
and nvl(transaction_action_id,-99) =
nvl(decode(i_comm_iss_flag,1,
i_cost_txn_action_id,transaction_action_id),-99);
fnd_file.put_line(fnd_file.log, 'Calling Insert Account');
select decode(l_exp, 1, i_exp_acct,
decode(cost_element, 1, l_mat_acct,
2, l_mat_ovhd_acct,
3, l_res_acct,
4, l_osp_acct,
5, l_ovhd_acct))
into l_acct
from dual;
insert_account(i_org_id, i_txn_id, i_item_id, i_p_qty * l_cost - l_var,
i_p_qty, l_acct, i_sob_id, l_acct_line_type,
cost_element, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
i_pri_curr, i_alt_curr, i_conv_date, i_conv_rate, i_conv_type,
1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
l_err_num, l_err_code, l_err_msg);
select nvl(sum(variance_amount),0)
into l_var
from mtl_cst_actual_cost_details cacd
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = l_layer_id
and nvl(transaction_action_id,-99) =
nvl(decode(i_comm_iss_flag,1,
i_cost_txn_action_id,transaction_action_id),-99);
select nvl(average_cost_var_account,-1)
into l_acct
from mtl_parameters
where organization_id = i_org_id;
select nvl(average_cost_var_account,-1)
into l_acct
from cst_cost_group_accounts
where cost_group_id = i_cost_grp_id
and organization_id = i_org_id;
insert_account(i_org_id, i_txn_id, i_item_id, l_var,
i_p_qty, l_acct, i_sob_id, 13,
NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
i_pri_curr, i_alt_curr, i_conv_date, i_conv_rate, i_conv_type,
1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
l_err_num, l_err_code, l_err_msg);
select nvl(max(layer_id), l_layer_id)
into l_to_layer_id
from mtl_cst_actual_cost_details
where transaction_id = i_txn_id
and layer_id <> l_layer_id;
select nvl(sum(payback_variance_amount),0)
into l_payback_var
from mtl_cst_actual_cost_details cacd
where transaction_id = i_txn_id
and organization_id = i_org_id
and layer_id = l_layer_id
and cost_element_id = cost_element;
select nvl(payback_mat_var_account,-1)
into l_acct
from cst_cost_group_accounts
where organization_id = i_org_id
and cost_group_id = i_cost_grp_id;
select nvl(payback_moh_var_account,-1)
into l_acct
from cst_cost_group_accounts
where organization_id = i_org_id
and cost_group_id = i_cost_grp_id;
select nvl(payback_res_var_account,-1)
into l_acct
from cst_cost_group_accounts
where organization_id = i_org_id
and cost_group_id = i_cost_grp_id;
select nvl(payback_osp_var_account,-1)
into l_acct
from cst_cost_group_accounts
where organization_id = i_org_id
and cost_group_id = i_cost_grp_id;
select nvl(payback_ovh_var_account,-1)
into l_acct
from cst_cost_group_accounts
where organization_id = i_org_id
and cost_group_id = i_cost_grp_id;
end if; -- end selecting different variance account
insert_account(i_org_id, i_txn_id, i_item_id, -1 * i_p_qty * l_payback_var,
i_p_qty, l_acct, i_sob_id, 13,
cost_element, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
i_pri_curr, i_alt_curr, i_conv_date, i_conv_rate, i_conv_type,
1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
l_err_num, l_err_code, l_err_msg);
select primary_cost_method
into l_cost_method
from mtl_parameters
where organization_id = i_org_id;
select layer_id into l_layer_id
from cst_quantity_layers where
inventory_item_id = i_item_id and
organization_id = i_org_id and
cost_group_id = i_cost_grp_id;
select count(*)
into l_ele_exist
from mtl_cst_actual_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id
and decode(i_comm_iss_flag,1,l_layer_id,layer_id) = layer_id
and nvl(decode(i_comm_iss_flag,1,i_cost_txn_action_id,transaction_action_id),-99)=
nvl(transaction_action_id,-99);
select sum(actual_cost)
into l_elemental_cost(cost_element)
from mtl_cst_actual_cost_details cacd
where transaction_id = i_txn_id
and organization_id = i_org_id
and cost_element_id = cost_element
and decode(i_comm_iss_flag,1,l_layer_id,layer_id) = layer_id and
nvl(decode(i_comm_iss_flag,1,i_cost_txn_action_id,transaction_action_id),-99)=
nvl(transaction_action_id,-99)
and (cost_element_id <> 2
OR
(cost_element_id = 2
and level_type = decode(i_ovhd_absp,1,2,2,0,level_type)));
select decode(cost_element, 1, i_mat_acct,
2, i_mat_ovhd_acct,
3, i_res_acct,
4, i_osp_acct,
5, i_ovhd_acct)
into l_acct
from dual;
insert_account(i_org_id, i_txn_id, i_item_id, i_p_qty * l_elemental_cost(cost_element),
i_p_qty, l_acct, i_sob_id, i_acct_line_type,
cost_element, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
i_pri_curr, i_alt_curr, i_conv_date, i_conv_rate, i_conv_type,
1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
l_err_num, l_err_code, l_err_msg);
UPDATE cst_cogs_events
SET costed = NULL,
last_update_date = sysdate,
last_updated_by = i_user_id,
last_update_login = i_login_id,
request_id = i_req_id
WHERE cogs_om_line_id = i_cogs_om_line_id
AND mmt_transaction_id = i_txn_id;
select decode(c1.minimum_accountable_unit,
null,ROUND((nvl(sum(actual_cost),0)*abs(i_p_qty)),c1.precision)/abs(i_p_qty),
(ROUND((nvl(sum(actual_cost),0)*abs(i_p_qty))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit)/abs(i_p_qty))
into l_ce_round_cost
from mtl_cst_actual_cost_details cacd,
cst_organization_definitions cod,
fnd_currencies c1,
gl_sets_of_books gsb
where cacd.transaction_id = i_txn_id
and cacd.organization_id = i_org_id
and c1.currency_code = gsb.currency_code
and gsb.set_of_books_id = cod.set_of_books_id
and cod.organization_id = i_org_id
and cacd.cost_element_id = cost_element
and (cost_element_id <> 2
OR
(cost_element_id = 2
and level_type = decode(i_ovhd_absp,1,2,2,0,level_type)))
group by c1.minimum_accountable_unit,
c1.precision;
select nvl(sum(actual_cost),0)
into l_cost
from mtl_cst_actual_cost_details cacd
where transaction_id = i_txn_id
and organization_id = i_org_id
and (cost_element_id <> 2
OR
(cost_element_id = 2
and level_type = decode(i_ovhd_absp,1,2,2,0,level_type)));
fnd_file.put_line(fnd_file.log, 'Insert account()');
insert_account(i_org_id, i_txn_id, i_item_id, i_p_qty * l_cost,
i_p_qty, l_acct, i_sob_id, i_acct_line_type,
NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
i_pri_curr, i_alt_curr, i_conv_date, i_conv_rate, i_conv_type,
1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
l_err_num, l_err_code, l_err_msg);
select count(*)
into l_ele_exist
from mtl_cst_actual_cost_details
where transaction_id = i_txn_id
and organization_id = i_org_id;
select sum(actual_cost)
into l_cost
from mtl_cst_actual_cost_details cacd
where transaction_id = i_txn_id
and organization_id = i_org_id
and (level_type = 2
or (level_type = 1 and cost_element_id =1));
select sum(actual_cost)
into l_cost
from mtl_cst_actual_cost_details cacd
where transaction_id = i_txn_id
and organization_id = i_org_id
and level_type = 1
and cost_element_id = cost_element;
select decode(cost_element, 1, i_mat_acct,
2, i_mat_ovhd_acct,
3, i_res_acct,
4, i_osp_acct,
5, i_ovhd_acct)
into l_acct
from dual;
insert_account(i_org_id, i_txn_id, i_item_id, i_p_qty * l_cost,
i_p_qty, l_acct, i_sob_id, i_acct_line_type,
cost_element, NULL,
i_txn_date, i_txn_src_id, i_src_type_id,
i_pri_curr, i_alt_curr, i_conv_date, i_conv_rate, i_conv_type,
1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
l_err_num, l_err_code, l_err_msg);
select resource_id, actual_cost
from mtl_actual_cost_subelement
where transaction_id = i_txn_id
and organization_id = i_org_id
and cost_element_id = 2;
select nvl(absorption_account,-1)
into l_acct
from bom_resources
where resource_id = l_res_id
and organization_id = i_org_id;
insert_account(i_org_id, i_txn_id, i_item_id, i_p_qty * l_cost,
i_p_qty, l_acct, i_sob_id, 3,
2, l_res_id,
i_txn_date, i_txn_src_id, i_src_type_id,
i_pri_curr, i_alt_curr, i_conv_date, i_conv_rate, i_conv_type,
1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
l_err_num, l_err_code, l_err_msg);
l_act_flag NUMBER; -- Flag to indicate the insert_account if encumbrance
insert_account(i_org_id, i_txn_id, i_item_id, i_enc_amount,
i_p_qty, l_acct, i_sob_id, l_acct_line_type,
NULL, NULL,
i_txn_date, i_txn_src_id, i_src_type_id, i_pri_curr, i_alt_curr,
i_conv_date, i_conv_rate, i_conv_type,
l_act_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);
PROCEDURE insert_account(
I_ORG_ID IN NUMBER,
I_TXN_ID IN NUMBER,
I_ITEM_ID IN NUMBER,
I_VALUE IN NUMBER,
I_QTY IN NUMBER,
I_ACCT IN NUMBER,
I_SOB_ID IN NUMBER,
I_ACCT_LINE_TYPE IN NUMBER,
I_COST_ELEMENT_ID IN NUMBER,
I_RESOURCE_ID IN NUMBER,
I_TXN_DATE IN DATE,
I_TXN_SRC_ID IN NUMBER,
I_SRC_TYPE_ID IN NUMBER,
I_PRI_CURR IN VARCHAR2,
I_ALT_CURR IN VARCHAR2,
I_CONV_DATE IN DATE,
I_CONV_RATE IN NUMBER,
I_CONV_TYPE IN VARCHAR2,
I_ACT_FLAG IN NUMBER,
I_USER_ID IN NUMBER,
I_LOGIN_ID IN NUMBER,
I_REQ_ID IN NUMBER,
I_PRG_APPL_ID IN NUMBER,
I_PRG_ID IN NUMBER,
O_Error_Num OUT NOCOPY NUMBER,
O_Error_Code OUT NOCOPY VARCHAR2,
O_Error_Message OUT NOCOPY VARCHAR2
)IS
l_operating_unit NUMBER;
fnd_file.put_line(fnd_file.log, 'In insert accounts');
select decode(encumbrance_reversal_flag,1,1,2,0,0)
into l_enc_rev
from mtl_parameters
where organization_id = i_org_id;
SELECT transaction_type_id
INTO l_txn_type
FROM mtl_material_transactions
WHERE transaction_id = i_txn_id;
SELECT rsl.ussgl_transaction_code
INTO l_ussgl_tc
FROM mtl_material_transactions mmt,
rcv_transactions rt,
rcv_shipment_lines rsl
WHERE mmt.transaction_id = i_txn_id
AND mmt.rcv_transaction_id = rt.transaction_id
AND rt.shipment_line_id = rsl.shipment_line_id;
SELECT POD.ussgl_transaction_code, nvl(pod.rate,1), pod.rate_date, poh.rate_type
INTO l_ussgl_tc, l_conv_rate, l_conv_date, l_conv_type
FROM mtl_material_transactions mmt,
rcv_transactions rt,
po_distributions_all pod,
po_headers_all poh
WHERE mmt.transaction_id = i_txn_id
AND mmt.rcv_transaction_id = rt.transaction_id
AND pod.po_distribution_id = rt.po_distribution_id
AND pod.po_header_id = poh.po_header_id;
/* The following select statement will be made to refer to cst_organization_definitions as an impact of the HR-PROFILE option */
l_stmt_num := 5;
SELECT NVL(operating_unit,-1)
INTO l_operating_unit
/*FROM org_organization_definitions */
FROM cst_organization_definitions
WHERE organization_id = i_org_id;
SELECT g1.encumbrance_type_id, g2.encumbrance_type_id
INTO l_req_enc_id, l_po_enc_id
FROM gl_encumbrance_types g1, gl_encumbrance_types g2
WHERE g1.encumbrance_type_key = 'Commitment' AND
g2.encumbrance_type_key = 'Obligation';
insert into mtl_transaction_accounts (
inv_sub_ledger_id, /* R12 - SLA Distribution Link */
transaction_id,
reference_account,
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,
organization_id,
transaction_date,
transaction_value,
accounting_line_type,
base_transaction_value,
basis_type,
contra_set_id,
resource_id,
cost_element_id,
currency_code,
currency_conversion_date,
currency_conversion_rate,
currency_conversion_type,
primary_quantity,
rate_or_amount,
transaction_source_type_id,
transaction_source_id,
encumbrance_type_id,
ussgl_transaction_code)
select
/* Bug 9356654- Costing Single Event Approach for Inventory
For encumbrance line making inv_subledger_id value as -ive
to indicate single event encumbrance data
*/
decode(i_acct_line_type,
15,
-1*CST_INV_SUB_LEDGER_ID_S.NEXTVAL,
CST_INV_SUB_LEDGER_ID_S.NEXTVAL
),
i_txn_id,
i_acct,
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,
i_org_id,
i_txn_date,
decode(i_alt_curr,NULL, NULL,
i_pri_curr, NULL,
decode(c2.minimum_accountable_unit,
NULL, round(i_value/l_conv_rate, c2.precision),
round(i_value/l_conv_rate
/c2.minimum_accountable_unit)
* c2.minimum_accountable_unit )),
i_acct_line_type,
decode(c1.minimum_accountable_unit,
NULL, round(i_value, c1.precision),
round(i_value/c1.minimum_accountable_unit)
* c1.minimum_accountable_unit ),
1,
1,
i_resource_id,
i_cost_element_id,
decode(i_alt_curr, i_pri_curr, NULL, i_alt_curr),
decode(i_alt_curr, i_pri_curr, NULL, l_conv_date),
decode(i_alt_curr, i_pri_curr, NULL, l_conv_rate),
decode(i_alt_curr, i_pri_curr, NULL, l_conv_type),
decode(i_acct_line_type,
1, i_qty, --inventory
14, i_qty, --intransit inventory
3, i_qty, -- overhead absorption
abs(i_qty) * decode(sign(i_value), 0, sign(i_qty), sign(i_value))
), -- primary quantity
decode(i_qty, 0,0,
decode(i_acct_line_type,
1, i_value/i_qty,
14, i_value/i_qty,
3, i_value/i_qty,
abs(i_value/i_qty)
)
), -- rate_or_amount
i_src_type_id,
nvl(i_txn_src_id,-1),
decode(i_act_flag,1,NULL,0,decode(i_src_type_id,
1,l_po_enc_id,
7,l_req_enc_id,
8,l_req_enc_id,
NULL),NULL),
decode(l_enc_rev,1,
decode(l_txn_type,
18, decode(i_acct_line_type,
1, decode(i_cost_element_id,1, l_ussgl_tc,NULL),
15, l_ussgl_tc, NULL),
36, decode(i_acct_line_type,
1, decode(i_cost_element_id,1, l_ussgl_tc,NULL),
15, l_ussgl_tc, NULL),
71, decode(i_acct_line_type,
1, decode(i_cost_element_id,1, l_ussgl_tc,NULL),
15, l_ussgl_tc, NULL),
NULL),
NULL)
from
fnd_currencies c1,
fnd_currencies c2
where
c1.currency_code = i_pri_curr
and c2.currency_code = decode(i_alt_curr, NULL, i_pri_curr, i_alt_curr);
O_error_message := 'CSTPACDP.insert_account (' || to_char(l_stmt_num)
|| ') ' || substr(SQLERRM,1,180);
end insert_account;
select nvl(sum(base_transaction_value),0), nvl(sum(transaction_value),0)
into l_base_value, l_value
from mtl_transaction_accounts
where transaction_id = i_txn_id
and organization_id = i_org_id
and accounting_line_type <> 15;
SELECT MAX(rowid)
INTO l_rowid
FROM mtl_transaction_accounts
WHERE transaction_id = i_txn_id
AND organization_id = i_org_id
AND accounting_line_type NOT IN (9,10,15) /* this condition added for 4052277 */
AND sign(base_transaction_value - l_base_value) *
sign(nvl(decode(transaction_value, NULL,
decode(l_value, 0, NULL,-1*l_value),
transaction_value - l_value),0)) >= 0;
update mtl_transaction_accounts
set transaction_value = decode(transaction_value, NULL,
decode(l_value, 0, NULL,-1*l_value),
transaction_value - l_value),
base_transaction_value = base_transaction_value - l_base_value
where rowid = l_rowid;
SELECT MAX(rowid)
INTO l_rowid
FROM mtl_transaction_accounts
WHERE transaction_id = i_txn_id
AND organization_id = i_org_id
AND accounting_line_type NOT IN (9,10,15)
AND sign(base_transaction_value) *
sign(nvl(decode(transaction_value, NULL,
decode(l_value, 0, NULL,-1*l_value),
transaction_value - l_value),0)) >= 0;
update mtl_transaction_accounts
set transaction_value = decode(transaction_value, NULL,
decode(l_value, 0, NULL,-1*l_value),
transaction_value - l_value)
where rowid = l_rowid;
SELECT MAX(rowid)
INTO l_rowid
FROM mtl_transaction_accounts
WHERE transaction_id = i_txn_id
AND organization_id = i_org_id
AND accounting_line_type NOT IN (9,10,15)
AND sign(base_transaction_value-l_base_value) *
sign(nvl(transaction_value,0)) >= 0;
update mtl_transaction_accounts
set base_transaction_value = base_transaction_value - l_base_value
where rowid = l_rowid;
select
inventory_item_id, organization_id,
transaction_date,
primary_quantity, subinventory_code, nvl(cost_group_id,0),
transfer_organization_id, nvl(prior_cost,0), nvl(new_cost,0),
nvl(transaction_cost,0), nvl(actual_cost,0),
quantity_adjusted, nvl(transfer_cost,0), nvl(transportation_cost,0),
nvl(transportation_dist_account,-1),
nvl(flow_schedule, 'N'),
DECODE(completion_transaction_id,NULL,0,-1,0,completion_transaction_id),
cost_update_id, transaction_action_id,
decode( transaction_source_type_id, 16, -1, nvl(transaction_source_id,-1) ),
transfer_subinventory, nvl(transfer_cost_group_id,0),
nvl(transfer_transaction_id,0), trx_source_line_id,
transaction_source_type_id, nvl(distribution_account_id,-1),
acct_period_id, operation_seq_num,
currency_code,
nvl(currency_conversion_date,transaction_date),
nvl(currency_conversion_rate,-1) , currency_conversion_type,
nvl(encumbrance_account,-1), nvl(encumbrance_amount,0),
nvl(variance_amount,0), movement_id,
transaction_type_id, nvl(transfer_price, 0)
INTO
item_id, org_id, txn_date,
p_qty, subinv, cg_id,
txf_org_id, prior_cost, new_cost,
txn_cost, act_cost,
qty_adj, txf_cost, trp_cost,
trp_acct,
flow_sch,
comp_txn_id,
cstud_id, txn_act_id,
txn_src_id,
txf_subinv, txf_cg_id,
txf_txn_id, src_line_id,
src_type_id, dist_acct,
acct_period_id, op_seq_num,
alt_curr, conv_date,
conv_rate, conv_type,
enc_acct, enc_amt,
var_amt, mv_idmvi,
txn_type_id, interorg_xfr_price
from mtl_material_transactions
where transaction_id = trans_id;
SELECT to_number(org_information3)
INTO l_org_ou
FROM hr_organization_information
WHERE org_information_context = 'Accounting Information'
AND organization_id = org_id;
SELECT to_number(org_information3)
INTO l_txf_org_ou
FROM hr_organization_information
WHERE org_information_context = 'Accounting Information'
AND organization_id = txf_org_id;
SELECT decode(nvl(cost_group_accounting,0),0,0,
decode(nvl(project_reference_enabled,0),1,1,0))
INTO pjm_flg
FROM MTL_PARAMETERS
WHERE organization_id = org_id;
EXEC SQL select
primary_cost_method
INTO
:rec_p_cst_type
from mtl_parameters
where organization_id = :txf_org_id;
goto update_cost;
select
primary_cost_method,
material_account, ap_accrual_account, cost_of_sales_account,
purchase_price_var_account, average_cost_var_account,
decode(encumbrance_reversal_flag,1,1,2,0,0),
material_account, material_overhead_account, resource_account,
outside_processing_account, overhead_account,
expense_account
INTO
p_cst_type,
inv_acct, ap_acct, cog_acct,
ppv_acct, acv_acct,
enc_rev,
mat_acct, mat_ovhd_acct, res_acct,
osp_acct, ovhd_acct,
exp_acct
from mtl_parameters
where organization_id = org_id;
select cod.set_of_books_id, sob.currency_code
INTO sob_id, pri_curr
FROM cst_organization_definitions cod, gl_sets_of_books sob
WHERE cod.organization_id = org_id
AND sob.set_of_books_id = cod.set_of_books_id;
select decode(inventory_asset_flag,'Y',0,1), nvl(expense_account, -1)
INTO iexp_flg, exp_acct
FROM mtl_system_items_b
WHERE inventory_item_id = item_id
AND organization_id = org_id;
SELECT DECODE(txn_act_id, 3, DECODE(iexp_flg,1,1,DECODE(asset_inventory,1,0,1)),
iexp_flg),
DECODE(asset_inventory,1,0,1)
INTO exp_flg, exp_sub
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = subinv
AND organization_id = org_id;
select material_account, material_overhead_account, resource_account,
outside_processing_account, overhead_account,
nvl(expense_account, exp_acct)
into mat_acct, mat_ovhd_acct, res_acct,
osp_acct, ovhd_acct,
exp_acct
from mtl_secondary_inventories msi
where msi.organization_id = org_id
and secondary_inventory_name = subinv;
select material_account, material_overhead_account, resource_account,
outside_processing_account, overhead_account,
nvl(expense_account, exp_acct)
into mat_acct, mat_ovhd_acct, res_acct,
osp_acct, ovhd_acct,
exp_acct
from mtl_parameters
where organization_id = org_id;
select count(*)
INTO ccga_count
FROM cst_cost_group_accounts
WHERE cost_group_id = cg_id
AND organization_id = org_id;
select material_account, material_overhead_account, resource_account,
outside_processing_account, overhead_account,
nvl(expense_account, exp_acct)
into mat_acct, mat_ovhd_acct, res_acct,
osp_acct, ovhd_acct,
exp_acct
from cst_cost_group_accounts
where organization_id = org_id
and cost_group_id = cg_id;
|Insert standard cost into mtl_cst_actual_cost_details
+----------------------------------------------------------------------*/
fnd_file.put_line(fnd_file.log, 'inserting into mcacd');
goto update_cost;
select send.primary_uom_code, rec.primary_uom_code
INTO send_uom, rec_uom
FROM mtl_system_items_b send, mtl_system_items_b rec
WHERE send.inventory_item_id = item_id
AND send.organization_id = from_org
AND rec.inventory_item_id = item_id
AND rec.organization_id = to_org;
SELECT
case when txn_act_id <> 3 then nvl(mmt.fob_point, mip.fob_point) end case,
decode(txn_act_id,3,mp.material_account,
nvl(mmt.intransit_account, mip.intransit_inv_account)), /* used only for interorg shipment */
mip.interorg_transfer_cr_account,
mip.interorg_receivables_account,
mip.interorg_payables_account,
mip.interorg_price_var_account,
mp.average_cost_var_account,
mip.interorg_profit_account /* interorg profit account for process-discrete xfers only */
INTO
fob_pt,
io_inv_acct,
io_txfr_cr_acct,
io_rec_acct,
io_pay_acct,
io_ppv_acct,
acv_acct, /* overwrite sending acv_acct */
interorg_profit_acct
FROM mtl_interorg_parameters mip, mtl_parameters mp, mtl_material_transactions mmt
WHERE mip.from_organization_id = from_org
AND mip.to_organization_id = to_org
AND mp.organization_id = org_id
AND mmt.transaction_id = trans_id;
SELECT decode(act_cost, 0, nvl(item_cost,0), act_cost), nvl(item_cost, 0)
INTO act_cost, item_cost
FROM cst_item_costs_for_gl_view
WHERE inventory_item_id = item_id
AND organization_id = org_id;
SELECT nvl(purchase_price_var_account, io_ppv_acct)
INTO io_ppv_acct
FROM mtl_parameters
WHERE organization_id = org_id;
SELECT count(*)
INTO ccga_count
FROM cst_cost_group_accounts
WHERE cost_group_id = cg_id
AND organization_id = org_id;
SELECT nvl(purchase_price_var_account, io_ppv_acct)
INTO io_ppv_acct
FROM cst_cost_group_accounts
WHERE cost_group_id = cg_id
AND organization_id = org_id ;
SELECT count(*)
INTO ccga_count
FROM cst_cost_group_accounts
WHERE cost_group_id = cg_id
AND organization_id = org_id;
SELECT nvl(purchase_price_var_account, io_ppv_acct)
INTO io_ppv_acct
FROM cst_cost_group_accounts
WHERE cost_group_id = cg_id
AND organization_id = org_id ;
CSTPACDP.insert_account
(org_id, trans_id, item_id, -1 * trp_cost,
p_qty, trp_acct, sob_id, 12, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, NULL, NULL, NULL, NULL,1,
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
CSTPACDP.insert_account
(org_id, trans_id, item_id, -1 * txf_price * p_qty,
-1 * p_qty, io_rec_acct, sob_id, 10, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, NULL, NULL, NULL, NULL,1,
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
SELECT SUM(NVL(base_transaction_value,0))
INTO interorg_profit
FROM mtl_transaction_accounts
WHERE transaction_id = trans_id
AND organization_id = org_id
;
CSTPACDP.insert_account
(org_id, trans_id, item_id, -1 * interorg_profit,
p_qty, interorg_profit_acct, sob_id,
G_INTERORG_PROFIT_ACCT, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, NULL, NULL, NULL, NULL,1,
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
CSTPACDP.insert_account
(org_id, trans_id, item_id, 0,
p_qty, mat_acct, sob_id, 1, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, NULL, NULL, NULL, NULL,1,
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
CSTPACDP.insert_account
(org_id, trans_id, item_id, 0,
-1 * p_qty, io_inv_acct, sob_id, 14, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, NULL, NULL, NULL, NULL,1,
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
CSTPACDP.insert_account
(org_id, trans_id, item_id, -1 * payamt,
-1 * p_qty, io_pay_acct, sob_id, 9, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, l_snd_curr, l_conv_date, l_conv_rate, l_curr_type, 1, /* INVCONV ANTHIYAG Bug#5352186 09-Sep-2006 */
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
CSTPACDP.insert_account
(org_id, trans_id, item_id, interorg_xfr_price * p_qty,
p_qty, exp_acct, sob_id, 2, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, l_snd_curr, l_conv_date, l_conv_rate, l_curr_type, 1, /* umoogala Bug#5708387 13-dec-2006 */
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
goto update_cost;
/* Changes for PJM Standard Costing - Update MMT with PPV amount*/
UPDATE mtl_material_transactions
SET variance_amount = (SELECT -1 * nvl(sum(base_transaction_value),0)
FROM mtl_transaction_accounts mta
WHERE mta.transaction_id = trans_id
AND mta.organization_id = org_id
AND encumbrance_type_id is null)
WHERE transaction_id = trans_id
RETURNING variance_amount INTO ppv_amt;
CSTPACDP.insert_account
(org_id, trans_id, item_id, ppv_amt,
p_qty, io_ppv_acct, sob_id, 6, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, l_snd_curr, l_conv_date, l_conv_rate, l_curr_type, 1, /* umoogala Bug#5708387 13-dec-2006 */
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
CSTPACDP.insert_account
(org_id, trans_id, item_id, payamt,
-1 * p_qty, io_pay_acct, sob_id, 9, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, l_snd_curr,l_conv_date, l_conv_rate, l_curr_type, 1, /* INVCONV ANTHIYAG Bug#5352186 09-Sep-2006 End */
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
UPDATE mtl_material_transactions
SET variance_amount = (SELECT -1 * nvl(sum(base_transaction_value),0)
FROM mtl_transaction_accounts mta
WHERE mta.transaction_id = trans_id
AND mta.organization_id = org_id
AND encumbrance_type_id is null)
WHERE transaction_id = trans_id
RETURNING variance_amount INTO ppv_amt;
CSTPACDP.insert_account
(org_id, trans_id, item_id, ppv_amt,
p_qty, io_ppv_acct, sob_id, 6, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, l_snd_curr, l_conv_date, l_conv_rate, l_curr_type, 1, /* umoogala Bug#5708387 13-dec-2006 */
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
CSTPACDP.insert_account
(org_id, trans_id, item_id, -1 * payamt,
p_qty, exp_acct, sob_id, 2, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, l_snd_curr, l_conv_date, l_conv_rate, l_curr_type, 1, /* umoogala Bug#5708387 13-dec-2006 */
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
CSTPACDP.insert_account
(org_id, trans_id, item_id, -1 * trp_cost,
p_qty, trp_acct, sob_id, 12, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, NULL, NULL, NULL, NULL,1,
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
CSTPACDP.insert_account
(org_id, trans_id, item_id, interorg_xfr_price * p_qty,
p_qty, io_rec_acct, sob_id, 10, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, NULL, NULL, NULL, NULL,1,
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
SELECT SUM(NVL(base_transaction_value,0))
INTO interorg_profit
FROM mtl_transaction_accounts
WHERE transaction_id = trans_id
AND organization_id = org_id
;
CSTPACDP.insert_account
(org_id, trans_id, item_id, -1 * interorg_profit, /* ANTHIYAG Bug#5459157 11-Aug-2006 */
p_qty, interorg_profit_acct, sob_id,
G_INTERORG_PROFIT_ACCT, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, NULL, NULL, NULL, NULL,1,
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
CSTPACDP.insert_account
(org_id, trans_id, item_id, payamt,
-1 * p_qty, io_pay_acct, sob_id, 9, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, l_snd_curr,l_conv_date, l_conv_rate, l_curr_type, 1, /* INVCONV ANTHIYAG Bug#5352186 09-Sep-2006 End */
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
CSTPACDP.insert_account
(org_id, trans_id, item_id, -1 * trp_cost,
p_qty, trp_acct, sob_id, 12, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, l_snd_curr,l_conv_date, l_conv_rate, l_curr_type, 1, /* INVCONV ANTHIYAG Bug#5352186 09-Sep-2006 End */
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
CSTPACDP.insert_account
(org_id, trans_id, item_id, var_amt,
p_qty, acv_acct, sob_id, 13, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, l_snd_curr,l_conv_date, l_conv_rate, l_curr_type, 1, /* umoogala Bug#5708387 13-dec-2006 */
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
CSTPACDP.insert_account
(org_id, trans_id, item_id, trp_cost,
p_qty, exp_acct, sob_id, 2, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, l_snd_curr, l_conv_date, l_conv_rate, l_curr_type, 1, /* umoogala Bug#5708387 13-dec-2006 */
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
goto update_cost;
UPDATE mtl_material_transactions
SET variance_amount = (SELECT -1 * nvl(sum(base_transaction_value),0)
FROM mtl_transaction_accounts mta
WHERE mta.transaction_id = trans_id
AND mta.organization_id = org_id
AND encumbrance_type_id is null)
WHERE transaction_id = trans_id
RETURNING variance_amount INTO ppv_amt;
CSTPACDP.insert_account
(org_id, trans_id, item_id, ppv_amt,
p_qty, io_ppv_acct, sob_id, 6, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, l_snd_curr, l_conv_date, l_conv_rate, l_curr_type, 1, /* umoogala Bug#5708387 13-dec-2006 */
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
CSTPACDP.insert_account
(org_id, trans_id, item_id, -1*enc_amt,
p_qty, enc_acct, sob_id, 15, NULL, NULL,
txn_date, txn_src_id, src_type_id,
pri_curr, NULL, NULL, NULL, NULL,1,
user_id, login_id, req_id, prg_appid, prg_id,
O_Error_Num, O_Error_Code, O_Error_Message);
<>
/* Transfer Pricing */
/* Perform accounting adjustment */
/*
* - OPM INVCONV process/discrete Xfer Enh.
* Added 15 and 22 action ids. These gets created only for process/discrete
* transfers.
*/
if ( ((txn_act_id = 21 AND src_type_id = 8 AND fob_pt = 1) OR
(txn_act_id = 12 AND src_type_id = 7 AND fob_pt = 2) OR
(txn_act_id = 15 AND src_type_id = 7) OR
(txn_act_id = 22 AND src_type_id = 8))
AND tprice_option <> 0 )
then
-- {
fnd_file.put_line(FND_FILE.LOG, 'Start calling CST_TPRICE_PVT.Adjust_Acct');
/* For wip_scrap and periodic cost update, we don't update actual cost. */
if (txn_act_id <> 30 AND txn_act_id <> 50 AND txn_act_id <> 51
AND txn_act_id <> 52 AND txn_act_id <> 40 AND txn_act_id <> 41
AND txn_act_id <> 42 AND txn_act_id <> 43
AND NOT (txn_act_id = 24 AND src_type_id = 14))
then
-- {
l_stmt_num := 950;
UPDATE mtl_material_transactions
SET transaction_cost = (((txf_price*abs(p_qty))+nvl(trp_cost,0))/abs(p_qty))
WHERE transaction_id = trans_id;
UPDATE mtl_material_transactions
SET costed_flag = NULL,
transaction_group_id = NULL,
request_id = req_id,
program_application_id = prg_appid,
program_id = prg_id,
program_update_date = sysdate,
actual_cost = decode(zero_cost_flag, 1, 0, nvl(item_cost,0))
WHERE transaction_id = trans_id;
UPDATE mtl_material_transactions
SET costed_flag = 'E',
error_code = substrb(O_Error_Code,1,240),
error_explanation = substrb(O_Error_Message,1,240),
request_id = req_id,
program_application_id = prg_appid,
program_id = prg_id,
program_update_date = sysdate
WHERE transaction_id = trans_id;
UPDATE mtl_material_transactions
SET costed_flag = 'E',
error_code = substrb(O_Error_Code,1,240),
error_explanation = substrb(O_Error_Message,1,240),
request_id = req_id,
program_application_id = prg_appid,
program_id = prg_id,
program_update_date = sysdate
WHERE transaction_id = trans_id;
UPDATE mtl_material_transactions
SET costed_flag = 'E',
error_code = substrb(O_Error_Code,1,240),
error_explanation = substrb(O_Error_Message,1,240),
request_id = req_id,
program_application_id = prg_appid,
program_id = prg_id,
program_update_date = sysdate
WHERE transaction_id = trans_id;
SELECT nvl(sum(a.base_transaction_value),0)
--{
--BUG#9895493 ,nvl(sum(a.transaction_value),0)
,sum(a.transaction_value)
--}
,MAX(DECODE(a.accounting_line_type,1,
a.TRANSACTION_ID))
,MAX(NVL(b.cost_of_sales_account,-1)) --adj_acct MP COGS
,MAX(DECODE(a.accounting_line_type,1,
a.LAST_UPDATE_DATE))
,MAX(DECODE(a.accounting_line_type,1,
a.LAST_UPDATED_BY))
,MAX(DECODE(a.accounting_line_type,1,
a.CREATION_DATE))
,MAX(DECODE(a.accounting_line_type,1,
a.CREATED_BY))
,MAX(DECODE(a.accounting_line_type,1,
a.LAST_UPDATE_LOGIN))
,MAX(DECODE(a.accounting_line_type,1,
a.INVENTORY_ITEM_ID))
,MAX(DECODE(a.accounting_line_type,1,
a.ORGANIZATION_ID))
,MAX(DECODE(a.accounting_line_type,1,
a.TRANSACTION_DATE))
,MAX(DECODE(a.accounting_line_type,1,
a.TRANSACTION_SOURCE_ID))
,MAX(DECODE(a.accounting_line_type,1,
a.TRANSACTION_SOURCE_TYPE_ID))
,MAX(DECODE(a.accounting_line_type,1,
a.TRANSACTION_VALUE))
,MAX(DECODE(a.accounting_line_type,1,
a.PRIMARY_QUANTITY))
,MAX(DECODE(a.accounting_line_type,1,
a.GL_BATCH_ID))
,MAX(DECODE(a.accounting_line_type,1,
a.ACCOUNTING_LINE_TYPE))
,MAX(DECODE(a.accounting_line_type,1,
a.BASE_TRANSACTION_VALUE))
,MAX(DECODE(a.accounting_line_type,1,
a.CONTRA_SET_ID))
,MAX(DECODE(a.accounting_line_type,1,
a.RATE_OR_AMOUNT))
,MAX(DECODE(a.accounting_line_type,1,
a.BASIS_TYPE))
,MAX(DECODE(a.accounting_line_type,1,
a.RESOURCE_ID))
,MAX(DECODE(a.accounting_line_type,1,
a.COST_ELEMENT_ID))
,MAX(DECODE(a.accounting_line_type,1,
a.ACTIVITY_ID))
,MAX(DECODE(a.accounting_line_type,1,
a.CURRENCY_CODE))
,MAX(DECODE(a.accounting_line_type,1,
a.CURRENCY_CONVERSION_DATE))
,MAX(DECODE(a.accounting_line_type,1,
a.CURRENCY_CONVERSION_TYPE))
,MAX(DECODE(a.accounting_line_type,1,
a.CURRENCY_CONVERSION_RATE))
,MAX(DECODE(a.accounting_line_type,1,
a.REQUEST_ID))
,MAX(DECODE(a.accounting_line_type,1,
a.PROGRAM_APPLICATION_ID))
,MAX(DECODE(a.accounting_line_type,1,
a.PROGRAM_ID))
,MAX(DECODE(a.accounting_line_type,1,
a.PROGRAM_UPDATE_DATE))
,MAX(DECODE(a.accounting_line_type,1,
a.ENCUMBRANCE_TYPE_ID))
,MAX(DECODE(a.accounting_line_type,1,
a.REPETITIVE_SCHEDULE_ID))
,MAX(DECODE(a.accounting_line_type,1,
a.USSGL_TRANSACTION_CODE))
INTO l_base_value
,l_value
,l_mta_rec.TRANSACTION_ID
,l_mta_rec.REFERENCE_ACCOUNT
,l_mta_rec.LAST_UPDATE_DATE
,l_mta_rec.LAST_UPDATED_BY
,l_mta_rec.CREATION_DATE
,l_mta_rec.CREATED_BY
,l_mta_rec.LAST_UPDATE_LOGIN
,l_mta_rec.INVENTORY_ITEM_ID
,l_mta_rec.ORGANIZATION_ID
,l_mta_rec.TRANSACTION_DATE
,l_mta_rec.TRANSACTION_SOURCE_ID
,l_mta_rec.TRANSACTION_SOURCE_TYPE_ID
,l_mta_rec.TRANSACTION_VALUE
,l_mta_rec.PRIMARY_QUANTITY
,l_mta_rec.GL_BATCH_ID
,l_mta_rec.ACCOUNTING_LINE_TYPE
,l_mta_rec.BASE_TRANSACTION_VALUE
,l_mta_rec.CONTRA_SET_ID
,l_mta_rec.RATE_OR_AMOUNT
,l_mta_rec.BASIS_TYPE
,l_mta_rec.RESOURCE_ID
,l_mta_rec.COST_ELEMENT_ID
,l_mta_rec.ACTIVITY_ID
,l_mta_rec.CURRENCY_CODE
,l_mta_rec.CURRENCY_CONVERSION_DATE
,l_mta_rec.CURRENCY_CONVERSION_TYPE
,l_mta_rec.CURRENCY_CONVERSION_RATE
,l_mta_rec.REQUEST_ID
,l_mta_rec.PROGRAM_APPLICATION_ID
,l_mta_rec.PROGRAM_ID
,l_mta_rec.PROGRAM_UPDATE_DATE
,l_mta_rec.ENCUMBRANCE_TYPE_ID
,l_mta_rec.REPETITIVE_SCHEDULE_ID
,l_mta_rec.USSGL_TRANSACTION_CODE
FROM mtl_transaction_accounts a,
mtl_parameters b
WHERE a.transaction_id = i_txn_id
AND a.organization_id = i_org_id
AND b.organization_id = a.organization_id;
select decode(sign(l_mta_rec.base_transaction_value),-1,-1,1) into l_sign from dual;
INSERT INTO mtl_transaction_accounts
( TRANSACTION_ID
,REFERENCE_ACCOUNT
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
--
,CREATED_BY
,LAST_UPDATE_LOGIN
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TRANSACTION_DATE
--
,TRANSACTION_SOURCE_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_VALUE
,PRIMARY_QUANTITY
,GL_BATCH_ID
--
,ACCOUNTING_LINE_TYPE
,BASE_TRANSACTION_VALUE
,CONTRA_SET_ID
,RATE_OR_AMOUNT
,BASIS_TYPE
--
,RESOURCE_ID
,COST_ELEMENT_ID
,ACTIVITY_ID
,CURRENCY_CODE
,CURRENCY_CONVERSION_DATE
--
,CURRENCY_CONVERSION_TYPE
,CURRENCY_CONVERSION_RATE
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
--
,PROGRAM_UPDATE_DATE
,ENCUMBRANCE_TYPE_ID
,REPETITIVE_SCHEDULE_ID
,GL_SL_LINK_ID
,USSGL_TRANSACTION_CODE
--
,INV_SUB_LEDGER_ID )
SELECT
l_mta_rec.TRANSACTION_ID
,l_mta_rec.REFERENCE_ACCOUNT
,l_mta_rec.LAST_UPDATE_DATE
,l_mta_rec.LAST_UPDATED_BY
,l_mta_rec.CREATION_DATE
--
,l_mta_rec.CREATED_BY
,l_mta_rec.LAST_UPDATE_LOGIN
,l_mta_rec.INVENTORY_ITEM_ID
,l_mta_rec.ORGANIZATION_ID
,l_mta_rec.TRANSACTION_DATE
--
,l_mta_rec.TRANSACTION_SOURCE_ID
,l_mta_rec.TRANSACTION_SOURCE_TYPE_ID
,l_mta_rec.TRANSACTION_VALUE
,l_mta_rec.PRIMARY_QUANTITY
,l_mta_rec.GL_BATCH_ID
--
,l_mta_rec.ACCOUNTING_LINE_TYPE
,l_mta_rec.BASE_TRANSACTION_VALUE
,l_mta_rec.CONTRA_SET_ID
,l_mta_rec.RATE_OR_AMOUNT
,l_mta_rec.BASIS_TYPE
--
,l_mta_rec.RESOURCE_ID
,l_mta_rec.COST_ELEMENT_ID
,l_mta_rec.ACTIVITY_ID
,l_mta_rec.CURRENCY_CODE
,l_mta_rec.CURRENCY_CONVERSION_DATE
--
,l_mta_rec.CURRENCY_CONVERSION_TYPE
,l_mta_rec.CURRENCY_CONVERSION_RATE
,l_mta_rec.REQUEST_ID
,l_mta_rec.PROGRAM_APPLICATION_ID
,l_mta_rec.PROGRAM_ID
--
,l_mta_rec.PROGRAM_UPDATE_DATE
,l_mta_rec.ENCUMBRANCE_TYPE_ID
,l_mta_rec.REPETITIVE_SCHEDULE_ID
,l_mta_rec.GL_SL_LINK_ID
,l_mta_rec.USSGL_TRANSACTION_CODE
--
,CST_INV_SUB_LEDGER_ID_S.NEXTVAL
FROM DUAL;
select decode(sign(l_mta_rec.base_transaction_value),-1,-1,1) into l_sign from dual;
INSERT INTO mtl_transaction_accounts
( TRANSACTION_ID
,REFERENCE_ACCOUNT
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
--
,CREATED_BY
,LAST_UPDATE_LOGIN
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TRANSACTION_DATE
--
,TRANSACTION_SOURCE_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_VALUE
,PRIMARY_QUANTITY
,GL_BATCH_ID
--
,ACCOUNTING_LINE_TYPE
,BASE_TRANSACTION_VALUE
,CONTRA_SET_ID
,RATE_OR_AMOUNT
,BASIS_TYPE
--
,RESOURCE_ID
,COST_ELEMENT_ID
,ACTIVITY_ID
,CURRENCY_CODE
,CURRENCY_CONVERSION_DATE
--
,CURRENCY_CONVERSION_TYPE
,CURRENCY_CONVERSION_RATE
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
--
,PROGRAM_UPDATE_DATE
,ENCUMBRANCE_TYPE_ID
,REPETITIVE_SCHEDULE_ID
,GL_SL_LINK_ID
,USSGL_TRANSACTION_CODE
--
,INV_SUB_LEDGER_ID )
SELECT
l_mta_rec.TRANSACTION_ID
,l_mta_rec.REFERENCE_ACCOUNT
,l_mta_rec.LAST_UPDATE_DATE
,l_mta_rec.LAST_UPDATED_BY
,l_mta_rec.CREATION_DATE
--
,l_mta_rec.CREATED_BY
,l_mta_rec.LAST_UPDATE_LOGIN
,l_mta_rec.INVENTORY_ITEM_ID
,l_mta_rec.ORGANIZATION_ID
,l_mta_rec.TRANSACTION_DATE
--
,l_mta_rec.TRANSACTION_SOURCE_ID
,l_mta_rec.TRANSACTION_SOURCE_TYPE_ID
,l_mta_rec.TRANSACTION_VALUE
,l_mta_rec.PRIMARY_QUANTITY
,l_mta_rec.GL_BATCH_ID
--
,l_mta_rec.ACCOUNTING_LINE_TYPE
,l_mta_rec.BASE_TRANSACTION_VALUE
,l_mta_rec.CONTRA_SET_ID
,l_mta_rec.RATE_OR_AMOUNT
,l_mta_rec.BASIS_TYPE
--
,l_mta_rec.RESOURCE_ID
,l_mta_rec.COST_ELEMENT_ID
,l_mta_rec.ACTIVITY_ID
,l_mta_rec.CURRENCY_CODE
,l_mta_rec.CURRENCY_CONVERSION_DATE
--
,l_mta_rec.CURRENCY_CONVERSION_TYPE
,l_mta_rec.CURRENCY_CONVERSION_RATE
,l_mta_rec.REQUEST_ID
,l_mta_rec.PROGRAM_APPLICATION_ID
,l_mta_rec.PROGRAM_ID
--
,l_mta_rec.PROGRAM_UPDATE_DATE
,l_mta_rec.ENCUMBRANCE_TYPE_ID
,l_mta_rec.REPETITIVE_SCHEDULE_ID
,l_mta_rec.GL_SL_LINK_ID
,l_mta_rec.USSGL_TRANSACTION_CODE
--
,CST_INV_SUB_LEDGER_ID_S.NEXTVAL
FROM DUAL;
SELECT MAX(rowid)
INTO l_rowid
FROM mtl_transaction_accounts
WHERE transaction_id = i_txn_id
AND organization_id = i_org_id
AND accounting_line_type NOT IN (9,10,15)
AND sign(base_transaction_value) *
sign(nvl(decode(transaction_value, NULL,
decode(l_value, 0, NULL,-1*l_value),
transaction_value - l_value),0)) >= 0;
update mtl_transaction_accounts
set transaction_value = decode(transaction_value, NULL,
decode(l_value, 0, NULL,-1*l_value),
transaction_value - l_value)
where rowid = l_rowid;