[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
transaction_type_flag
INTO
l_ae_txn_rec.txn_type_flag
FROM
cst_accounting_event_types_v caet
WHERE
caet.event_type = i_event_type_id;
SELECT
set_of_books_id
INTO
l_ae_txn_rec.set_of_books_id
FROM
cst_le_cost_types clct
WHERE
clct.legal_entity = i_legal_entity AND
clct.cost_type_id = i_cost_type_id;
SELECT
i_event_type_id,
mmt.transaction_action_id,
mmt.transaction_source_type_id,
nvl(mmt.transaction_source_id,-1),
mmt.transaction_type_id,
null,
mmt.transaction_id,
mmt.inventory_item_id,
i_legal_entity,
i_cost_type_id,
i_cost_group_id,
nvl(mmt.periodic_primary_quantity,mmt.primary_quantity),
mmt.subinventory_code,
mmt.transfer_organization_id,
mmt.transfer_subinventory,
mmt.transfer_transaction_id,
nvl(mmt.distribution_account_id,-1),
mmt.currency_code,
mmt.currency_conversion_type,
nvl(mmt.currency_conversion_date,transaction_date),
nvl(mmt.currency_conversion_rate,-1),
'MTL',
i_period_id,
mmt.transaction_date,
mmt.organization_id,
nvl(mmt.material_account, -1),
nvl(mmt.material_overhead_account, -1),
nvl(mmt.resource_account, -1),
nvl(mmt.outside_processing_account, -1),
nvl(mmt.overhead_account, -1),
decode(mmt.flow_schedule,'Y',1,0),
decode(msi.inventory_asset_flag,'Y',0,1),
mmt.repetitive_line_id,
nvl(mmt.encumbrance_account, -1),
nvl(mmt.encumbrance_amount, 0),
-- Reveue / COGS Matching
nvl(mmt.so_issue_account_type,0),
mmt.trx_source_line_id,
mmt.cogs_recognition_percent,
nvl(mmt.expense_account_id,-1)
INTO
l_ae_txn_rec.event_type_id,
l_ae_txn_rec.txn_action_id,
l_ae_txn_rec.txn_src_type_id,
l_ae_txn_rec.txn_src_id,
l_ae_txn_rec.txn_type_id,
--l_ae_txn_rec.wip_txn_type,
l_ae_txn_rec.txn_type,
l_ae_txn_rec.transaction_id,
l_ae_txn_rec.inventory_item_id,
l_ae_txn_rec.legal_entity_id,
l_ae_txn_rec.cost_type_id,
l_ae_txn_rec.cost_group_id,
l_ae_txn_rec.primary_quantity,
l_ae_txn_rec.subinventory_code,
l_ae_txn_rec.xfer_organization_id,
l_ae_txn_rec.xfer_subinventory,
l_ae_txn_rec.xfer_transaction_id,
l_ae_txn_rec.dist_acct_id,
l_ae_txn_rec.currency_code,
l_ae_txn_rec.currency_conv_type,
l_ae_txn_rec.currency_conv_date,
l_ae_txn_rec.currency_conv_rate,
l_ae_txn_rec.ae_category,
l_ae_txn_rec.accounting_period_id,
l_ae_txn_rec.accounting_date,
l_ae_txn_rec.organization_id,
l_ae_txn_rec.mat_account,
l_ae_txn_rec.mat_ovhd_account,
l_ae_txn_rec.res_account,
l_ae_txn_rec.osp_account,
l_ae_txn_rec.ovhd_account,
l_ae_txn_rec.flow_schedule,
l_ae_txn_rec.exp_item ,
l_ae_txn_rec.line_id,
l_ae_txn_rec.encum_account,
l_ae_txn_rec.encum_amount,
-- Revenue / COGS Matching
l_ae_txn_rec.so_issue_acct_type,
l_ae_txn_rec.om_line_id,
l_ae_txn_rec.cogs_percentage,
l_ae_txn_rec.expense_account_id
FROM
mtl_material_transactions mmt,
mtl_system_items msi
WHERE
mmt.inventory_item_id = msi.inventory_item_id AND
mmt.organization_id = msi.organization_id AND
mmt.transaction_id = i_transaction_id;
select
transaction_type_name
into
l_ae_txn_rec.description
from
mtl_transaction_types
where transaction_type_id = l_ae_txn_rec.txn_type_id;
SELECT entity_type
INTO l_ae_txn_rec.wip_entity_type
FROM wip_entities we
WHERE we.wip_entity_id = l_ae_txn_rec.txn_src_id;
/* The following Select statement has already been commented out.
The Select statement will still however be made to refer to the
CST_ORGANIZATION_DEFINITIONS as an impact of the HR-PROFILE option */
/* this new SELECT query below has been comented out because the above query had
been commented out.The only change being this new query refers to
cst_organization_definitions.In the future if this above query needs to be
uncommented,please DO NOT uncomment the above query.Instead uncomment
this below query*/
l_stmt_num := 28;
SELECT NVL(operating_unit,-1)
INTO l_operating_unit
FROM cst_organization_definitions
WHERE organization_id = l_ae_txn_rec.organization_id;
SELECT NVL(cost_group_id, -1)
INTO l_txn_cost_group_id
FROM cst_cost_group_assignments
WHERE organization_id = l_ae_txn_rec.organization_id;
/* Select the operating unit of the org belonging to the processing cost group */
SELECT NVL(operating_unit,-1)
INTO l_operating_unit
FROM cst_organization_definitions
WHERE organization_id = decode(i_cost_group_id,
l_txn_cost_group_id, l_ae_txn_rec.organization_id,
l_ae_txn_rec.xfer_organization_id);
SELECT decode(l_ae_txn_rec.txn_src_type_id,
1,g1.ENCUMBRANCE_TYPE_ID,
g2.ENCUMBRANCE_TYPE_ID)
INTO l_ae_txn_rec.encum_type_id
FROM gl_encumbrance_types g1, gl_encumbrance_types g2
WHERE g1.encumbrance_type_key = 'Obligation' AND
g2.encumbrance_type_key = 'Commitment';
select
i_event_type_id,
null,
null,
wt.wip_entity_id,
--null, moved to after transaction_type to populate transaction_type
wt.transaction_type, -- assign to transaction_type_id
null,
wt.transaction_id,
wt.primary_item_id,
i_legal_entity,
i_cost_type_id,
i_cost_group_id,
null,
wt.primary_quantity,
null,
null,
null,
null,
null,
wt.currency_code,
wt.currency_conversion_type,
nvl(wt.currency_conversion_date,transaction_date),
nvl(wt.currency_conversion_rate,-1),
'WIP',
i_period_id,
wt.transaction_date,
wt.organization_id,
null,
null,
null,
null,
null,
0,
wt.line_id,
/* added decode on wt.primary_item_id for non-std jobs - fix 3179823 */
DECODE(transaction_type,6,0, decode(wt.primary_item_id,NULL,-1,1))
--{BUG#9356654
, ENCUMBRANCE_TYPE_ID
, ENCUMBRANCE_CCID
, ENCUMBRANCE_AMOUNT
--}
INTO
l_ae_txn_rec.event_type_id,
l_ae_txn_rec.txn_action_id,
l_ae_txn_rec.txn_src_type_id,
l_ae_txn_rec.txn_src_id,
l_ae_txn_rec.txn_type_id,
--l_ae_txn_rec.wip_txn_type,
l_ae_txn_rec.txn_type,
l_ae_txn_rec.transaction_id,
l_ae_txn_rec.inventory_item_id,
l_ae_txn_rec.legal_entity_id,
l_ae_txn_rec.cost_type_id,
l_ae_txn_rec.cost_group_id ,
l_ae_txn_rec.xfer_cost_group_id,
l_ae_txn_rec.primary_quantity,
l_ae_txn_rec.subinventory_code,
l_ae_txn_rec.xfer_organization_id,
l_ae_txn_rec.xfer_subinventory,
l_ae_txn_rec.xfer_transaction_id,
l_ae_txn_rec.dist_acct_id,
l_ae_txn_rec.currency_code,
l_ae_txn_rec.currency_conv_type,
l_ae_txn_rec.currency_conv_date,
l_ae_txn_rec.currency_conv_rate,
l_ae_txn_rec.ae_category,
l_ae_txn_rec.accounting_period_id,
l_ae_txn_rec.accounting_date,
l_ae_txn_rec.organization_id,
l_ae_txn_rec.mat_account,
l_ae_txn_rec.mat_ovhd_account,
l_ae_txn_rec.res_account,
l_ae_txn_rec.osp_account,
l_ae_txn_rec.ovhd_account,
l_ae_txn_rec.flow_schedule,
l_ae_txn_rec.line_id,
l_ae_txn_rec.exp_item
--{BUG#9356654
, l_ae_txn_rec.encum_type_id
, l_ae_txn_rec.encum_account
, l_ae_txn_rec.encum_amount
--}
FROM
wip_transactions wt
WHERE
wt.transaction_id = i_transaction_id;
SELECT entity_type
INTO l_ae_txn_rec.wip_entity_type
FROM wip_entities we
WHERE we.wip_entity_id = l_ae_txn_rec.txn_src_id;
select
decode(msi.inventory_asset_flag,'Y',0,1)
INTO
l_ae_txn_rec.exp_item
FROM
mtl_system_items msi,
wip_transactions wt
WHERE
msi.inventory_item_id = wt.primary_item_id AND
wt.organization_id = msi.organization_id AND
wt.transaction_id = i_transaction_id;
select
meaning
into
l_ae_txn_rec.description
from
mfg_lookups
where
lookup_type = 'WIP_TRANSACTION_TYPE'
and lookup_code = l_ae_txn_rec.txn_type_id;
SELECT
cost_type
INTO
l_cost_type_name
FROM
cst_cost_types
WHERE
cost_type_id = i_cost_type_id;
SELECT
cost_group
INTO
l_cost_group_name
FROM
cst_cost_groups
WHERE
cost_group_id = i_cost_group_id;
SELECT
period_name
INTO
l_period_name
FROM
cst_pac_periods
WHERE
pac_period_id = i_period_id;
SELECT
count(organization_id)
INTO
l_xfer_cg_exists
FROM
cst_cost_group_assignments
WHERE
organization_id = nvl(l_ae_txn_rec.xfer_organization_id,-1)
AND rownum < 2; /* bug 4586534 added rownum=1 for performance as its only existence check. */
SELECT
DECODE (l_ae_txn_rec.xfer_organization_id,NULL,NULL,cost_group_id)
INTO
l_ae_txn_rec.xfer_cost_group_id
FROM
cst_cost_group_assignments
WHERE
organization_id = nvl(l_ae_txn_rec.xfer_organization_id,organization_id);
SELECT
period_name
INTO
l_ae_txn_rec.accounting_period_name
FROM
cst_pac_periods
WHERE
pac_period_id = l_ae_txn_rec.accounting_period_id AND
cost_type_id = l_ae_txn_rec.cost_type_id AND
legal_entity = l_ae_txn_rec.legal_entity_id;
SELECT
category_id
INTO
l_ae_txn_rec.category_id
FROM
mtl_item_categories mic
WHERE
mic.inventory_item_id = l_ae_txn_rec.inventory_item_id
AND /* Bug 12794529: In case of interorg transactions, we could be processing the transfer organization */
mic.organization_id = Decode(i_cost_group_id, Nvl(l_ae_txn_rec.xfer_cost_group_id, -1), l_ae_txn_rec.xfer_organization_id, l_ae_txn_rec.organization_id)
AND
mic.category_set_id = (SELECT category_set_id
FROM mtl_default_category_sets
WHERE functional_area_id = 5);
CSTPALBR.insert_ae_lines(
l_ae_txn_rec,
l_ae_line_rec_tbl,
l_ae_err_rec);
select currency_code
into l_curr_rec.pri_currency
from gl_sets_of_books
where set_of_books_id = i_ae_txn_rec.set_of_books_id;
SELECT NVL(cost_group_id, -1)
INTO l_txn_cost_group_id
FROM cst_cost_group_assignments
WHERE organization_id = i_ae_txn_rec.organization_id;
SELECT nvl(mmt.fob_point,mip.fob_point)
INTO l_fob_point
FROM mtl_material_transactions mmt,mtl_interorg_parameters mip
WHERE mmt.transaction_id = i_ae_txn_rec.transaction_id
AND mip.from_organization_id = i_ae_txn_rec.xfer_organization_id
AND mip.to_organization_id = i_ae_txn_rec.organization_id;
SELECT currency_code
INTO l_curr_rec.alt_currency -- currency of the sending org
FROM cst_organization_definitions
WHERE organization_id = decode(i_ae_txn_rec.txn_action_id,
21, i_ae_txn_rec.organization_id,
i_ae_txn_rec.xfer_organization_id);
SELECT mmt.rcv_transaction_id into l_rcv_txn
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = i_ae_txn_rec.transaction_id;
SELECT
rt6.transaction_id
INTO
l_par_rcv_txn
FROM
rcv_transactions rt6
where rt6.transaction_type = 'RECEIVE'
START WITH
rt6.transaction_id = l_rcv_txn
CONNECT BY
rt6.transaction_id = prior rt6.parent_transaction_id;
SELECT
COUNT(rcv_transaction_id)
INTO
l_acq_exists
FROM
cst_rcv_acq_costs crac
WHERE
crac.rcv_transaction_id = l_par_rcv_txn AND
crac.cost_type_id = i_ae_txn_rec.cost_type_id AND
crac.cost_group_id = i_ae_txn_rec.cost_group_id;
SELECT
crac.net_quantity_received,
crac.costed_quantity,
crac.acquisition_cost
INTO
l_nqr,
l_costed_quantity,
l_acq_cost
from cst_rcv_acq_costs crac
where crac.rcv_transaction_id = l_par_rcv_txn AND
crac.cost_type_id = i_ae_txn_rec.cost_type_id AND
crac.cost_group_id = i_ae_txn_rec.cost_group_id;
SELECT
SUM(cracd.amount/NVL(aida.exchange_rate,1))
INTO
l_acq_cost_ent_inv
FROM
cst_rcv_acq_cost_details cracd,
ap_invoice_distributions_all aida
WHERE aida.invoice_distribution_id = cracd.invoice_distribution_id
AND cracd.header_id = (SELECT header_id
FROM cst_rcv_acq_costs crac
WHERE rcv_transaction_id = l_par_rcv_txn
AND crac.cost_type_id = i_ae_txn_rec.cost_type_id
AND crac.cost_group_id = i_ae_txn_rec.cost_group_id)
AND cracd.source_type = 'INVOICE';*/
select
sum(cracd.amount/NVL(aia.exchange_rate,1))
into
l_acq_cost_ent_inv
from
cst_rcv_acq_cost_details cracd,
ap_invoice_distributions_all aida,
ap_invoices_all aia
where
aida.invoice_distribution_id = cracd.invoice_distribution_id
and aia.invoice_id = aida.invoice_id
and aia.org_id = aida.org_id
and cracd.header_id = (select header_id
from cst_rcv_acq_costs crac
where rcv_transaction_id = l_par_rcv_txn
AND
crac.cost_type_id = i_ae_txn_rec.cost_type_id AND
crac.cost_group_id = i_ae_txn_rec.cost_group_id)
and cracd.source_type = 'INVOICE';
SELECT
SUM(cracd.amount/ decode(poll.match_option,
'P',CSTPPACQ.get_po_rate(rt.transaction_id),
'R',rt.currency_conversion_rate))
INTO
l_acq_cost_ent_po
FROM
cst_rcv_acq_cost_details cracd,
po_line_locations_all poll,
rcv_transactions rt
WHERE
poll.line_location_id = cracd.po_line_location_id
AND cracd.header_id = (SELECT header_id
FROM cst_rcv_acq_costs crac
WHERE rcv_transaction_id = rt.transaction_id
AND crac.cost_type_id = i_ae_txn_rec.cost_type_id
AND crac.cost_group_id = i_ae_txn_rec.cost_group_id)
AND rt.transaction_id = l_par_rcv_txn
AND rt.po_line_location_id = poll.line_location_id
AND cracd.source_type = 'PO';
select nvl(logical_transaction, 2)
into l_logical_transaction
from mtl_material_transactions
where transaction_id = i_ae_txn_rec.transaction_id;
** 4) Periodic cost update **
** 5) rest of inventory transactions **
** 6) Logical Accounting Events **
** 7) Consigned Price Update **
*******************************************************************/
if (i_ae_txn_rec.txn_src_type_id = 5 and i_ae_txn_rec.txn_action_id <> 2) then
-- WIP transaction
-- These always occur in base currency, so set alt currency as null.
-- -----------------------------------------------------------------
l_curr_rec.alt_currency := NULL;
CSTPAPBR.cost_consigned_update_txn(i_ae_txn_rec,
l_curr_rec,
l_ae_line_tbl,
l_err_rec);
select
encumbrance_reversal_flag
into
l_enc_rev
from
mtl_parameters
where organization_id = i_ae_txn_rec.organization_id;
SELECT MOD(SUM(DECODE(process_enabled_flag,'Y',1,2)), 2)
INTO l_pd_txfr_ind
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = i_ae_txn_rec.xfer_organization_id
OR MP.ORGANIZATION_ID = i_ae_txn_rec.organization_id;
SELECT decode(asset_inventory,1,0,1)
INTO l_exp_sub
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = i_ae_txn_rec.xfer_subinventory
AND organization_id = i_ae_txn_rec.xfer_organization_id;
SELECT MAX(wrs.repetitive_schedule_id)
INTO l_rep_sched_id
FROM wip_repetitive_schedules wrs
WHERE wrs.line_id = i_ae_txn_rec.line_id;
SELECT material_account,
material_overhead_account,
resource_account,
overhead_account,
outside_processing_account
INTO l_acct_rec.mat_account,
l_acct_rec.mat_ovhd_account,
l_acct_rec.res_account,
l_acct_rec.ovhd_account,
l_acct_rec.osp_account
FROM wip_repetitive_schedules wrs
WHERE wrs.repetitive_schedule_id = l_rep_sched_id;
SELECT
class_code
INTO
l_acct_class
FROM
wip_repetitive_items wri
WHERE
wri.wip_entity_id = i_ae_txn_rec.txn_src_id AND
wri.line_id = i_ae_txn_rec.line_id AND
wri.organization_id = i_ae_txn_rec.organization_id;
SELECT
material_account,
material_overhead_account,
resource_account,
outside_processing_account,
overhead_account,
class_code
INTO
l_acct_rec.mat_account,
l_acct_rec.mat_ovhd_account,
l_acct_rec.res_account,
l_acct_rec.osp_account,
l_acct_rec.ovhd_account,
l_acct_class
FROM wip_flow_schedules
WHERE organization_id = i_ae_txn_rec.organization_id
AND wip_entity_id = i_ae_txn_rec.txn_src_id;
select
material_account,
material_overhead_account,
resource_account,
outside_processing_account,
overhead_account,
class_code
into
l_acct_rec.mat_account,
l_acct_rec.mat_ovhd_account,
l_acct_rec.res_account,
l_acct_rec.osp_account,
l_acct_rec.ovhd_account,
l_acct_class
from wip_discrete_jobs
where organization_id = i_ae_txn_rec.organization_id
and wip_entity_id = i_ae_txn_rec.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_ae_txn_rec.organization_id;
select decode(asset_inventory,1,0,1)
into l_exp_sub
from mtl_secondary_inventories
where secondary_inventory_name = i_ae_txn_rec.subinventory_code
and organization_id = i_ae_txn_rec.organization_id;
select count(transaction_id) /* Bug No. 4586534 */
into l_mat_ovhd_exists
from mtl_pac_actual_cost_details
where transaction_id = i_ae_txn_rec.transaction_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and cost_element_id = 2
and level_type = 1;
select material_variance_account,
material_variance_account,
resource_variance_account,
outside_proc_variance_account,
overhead_variance_account
into l_acct_rec.mat_account,
l_acct_rec.mat_ovhd_account,
l_acct_rec.res_account,
l_acct_rec.osp_account,
l_acct_rec.ovhd_account
from wip_flow_schedules
where organization_id = i_ae_txn_rec.organization_id
and wip_entity_id = i_ae_txn_rec.txn_src_id;
select decode(asset_inventory,1,0,1)
into l_exp_sub1
from mtl_secondary_inventories
where secondary_inventory_name = i_ae_txn_rec.subinventory_code
and organization_id = i_ae_txn_rec.organization_id;
select decode(asset_inventory,1,0,1)
into l_exp_sub2
from mtl_secondary_inventories
where secondary_inventory_name = i_ae_txn_rec.xfer_subinventory
and organization_id = i_ae_txn_rec.organization_id;
SELECT decode(asset_inventory,1,0,1)
INTO l_exp_sub1
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = i_ae_txn_rec.subinventory_code
AND organization_id = i_ae_txn_rec.organization_id;
SELECT nvl(expense_account, -1)
INTO l_exp_account
FROM mtl_fiscal_cat_accounts
WHERE legal_entity_id = i_ae_txn_rec.legal_entity_id
AND cost_type_id = i_ae_txn_rec.cost_type_id
AND cost_group_id = i_ae_txn_rec.cost_group_id
AND category_id = i_ae_txn_rec.category_id;
SELECT nvl(mmt.fob_point,mip.fob_point)
INTO l_fob_point
FROM mtl_material_transactions mmt,mtl_interorg_parameters mip
WHERE mmt.transaction_id = i_ae_txn_rec.transaction_id
AND mip.from_organization_id = decode(i_ae_txn_rec.txn_action_id,
21,i_ae_txn_rec.organization_id,
22,i_ae_txn_rec.organization_id, -- INVCONV sikhanna
i_ae_txn_rec.xfer_organization_id)
AND mip.to_organization_id = decode(i_ae_txn_rec.txn_action_id,
21,i_ae_txn_rec.xfer_organization_id,
22,i_ae_txn_rec.xfer_organization_id, -- INVCONV sikhanna
i_ae_txn_rec.organization_id);
SELECT NVL(cost_group_id, -1)
INTO l_txn_cost_group_id
FROM cst_cost_group_assignments
WHERE organization_id = i_ae_txn_rec.organization_id;
SELECT decode(asset_inventory,1,0,1)
INTO l_exp_sub2
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = i_ae_txn_rec.xfer_subinventory
AND organization_id = i_ae_txn_rec.xfer_organization_id;
select decode(inventory_asset_flag,'Y',0,1)
into l_exp2
from mtl_system_items
where inventory_item_id = i_ae_txn_rec.inventory_item_id
and organization_id = i_ae_txn_rec.xfer_organization_id;
select decode(inventory_asset_flag,'Y',0,1)
into l_exp2
from mtl_system_items
where inventory_item_id = i_ae_txn_rec.inventory_item_id
and organization_id = i_ae_txn_rec.organization_id;
/* Select the expense item status of the item in the org belonging
to the cost group currently being processed. This may be different
than the cost group of the transaction org. */
IF (i_ae_txn_rec.cost_group_id <> l_txn_cost_group_id) THEN
SELECT decode(inventory_asset_flag, 'Y', 0, 1)
INTO l_cg_exp_item
FROM mtl_system_items
WHERE organization_id = i_ae_txn_rec.xfer_organization_id
AND inventory_item_id = i_ae_txn_rec.inventory_item_id;
SELECT transfer_transaction_id
INTO l_txfr_txn_id
FROM mtl_material_transactions
WHERE transaction_id = i_ae_txn_rec.transaction_id;
SELECT cost_group_id
INTO l_shipping_cg_id
FROM cst_cost_group_assignments
WHERE organization_id = (SELECT decode(i_ae_txn_rec.txn_action_id,
21, organization_id,
22, organization_id, -- Logical Shipping txn
12, transfer_organization_id,
15, transfer_organization_id, -- Logical Receipt txn
3, decode(sign(i_ae_txn_rec.primary_quantity),
1, transfer_organization_id,
organization_id),
NULL)
FROM mtl_material_transactions
WHERE transaction_id = i_ae_txn_rec.transaction_id);
SELECT nvl(fnd_profile.value('CST_TRANSFER_PRICING_OPTION'), 0)
INTO l_tprice_option
FROM mtl_intercompany_parameters MIP
WHERE fnd_profile.value('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER') = 1
AND (select count(1)
from hr_organization_information HOI
where HOI.organization_id = decode(i_ae_txn_rec.txn_action_id,
21, i_ae_txn_rec.organization_id,
i_ae_txn_rec.xfer_organization_id)
AND HOI.org_information_context = 'Accounting Information'
AND MIP.ship_organization_id = to_number(HOI.org_information3)
AND ROWNUM < 2) >0
AND ( select count(1)
from hr_organization_information HOI2
where HOI2.organization_id = decode(i_ae_txn_rec.txn_action_id,
21, i_ae_txn_rec.xfer_organization_id,
i_ae_txn_rec.organization_id)
AND HOI2.org_information_context = 'Accounting Information'
AND MIP.sell_organization_id = to_number(HOI2.org_information3)
AND ROWNUM < 2)>0
AND MIP.flow_type = 1;
SELECT MOD(SUM(DECODE(process_enabled_flag,'Y',1,2)), 2)
INTO l_pd_txfr_ind
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = i_ae_txn_rec.xfer_organization_id
OR MP.ORGANIZATION_ID = i_ae_txn_rec.organization_id;
select count(transaction_id)
into l_mat_ovhd_exists
from mtl_pac_actual_cost_details
where transaction_id = i_ae_txn_rec.transaction_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and cost_element_id = 2
and level_type = 1;
SELECT NVL(mmt.transfer_price,0)
INTO l_transfer_price
FROM mtl_material_transactions MMT
WHERE MMT.transaction_id = i_ae_txn_rec.transaction_id;
SELECT NVL(mmt.transfer_price,0) * l_conv_rate
INTO l_transfer_price
FROM mtl_material_transactions MMT
WHERE MMT.transaction_id = i_ae_txn_rec.transaction_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT sum(nvl(transaction_cost,0))
INTO l_mptcd_cost
FROM mtl_pac_txn_cost_details
WHERE transaction_id = i_ae_txn_rec.transaction_id
AND pac_period_id = i_ae_txn_rec.accounting_period_id
AND cost_group_id = i_ae_txn_rec.cost_group_id;
SELECT nvl(profit_in_inv_account, -1)
INTO l_profit_in_inv_account
FROM mtl_interorg_parameters
WHERE from_organization_id = i_ae_txn_rec.organization_id
AND to_organization_id = i_ae_txn_rec.xfer_organization_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT mip.interorg_receivables_account,
mmt.transportation_dist_account,
mip.interorg_transfer_cr_account,
nvl(mip.interorg_profit_account, -1)
INTO l_io_rcv_acct,
l_io_freight_acct,
l_io_txfr_cr_acct,
l_io_txfr_var_acct
FROM mtl_interorg_parameters mip,
mtl_material_transactions mmt
WHERE mip.from_organization_id = i_ae_txn_rec.organization_id
AND mip.to_organization_id = i_ae_txn_rec.xfer_organization_id
AND mmt.transaction_id = i_ae_txn_rec.transaction_id;
SELECT nvl(max(cpp.pac_period_id), -1)
INTO l_prev_period_id
FROM cst_pac_periods cpp
WHERE cpp.cost_type_id = i_ae_txn_rec.cost_type_id
AND cpp.legal_entity = i_ae_txn_rec.legal_entity_id
AND cpp.pac_period_id < i_ae_txn_rec.accounting_period_id;
/* The flag selected below indicates if PACP is used or not */
SELECT TRANSFER_COST_FLAG
INTO l_transfer_cost_flag
FROM CST_LE_COST_TYPES
WHERE LEGAL_ENTITY = i_ae_txn_rec.legal_entity_id
AND COST_TYPE_ID = i_ae_txn_rec.cost_type_id;
SELECT NVL(MAX(cost_group_id),-1)
INTO l_txfr_cost_group_id
FROM cst_cost_group_assignments
WHERE organization_id = i_ae_txn_rec.xfer_organization_id;
SELECT legal_entity
INTO l_txfr_legal_entity
FROM cst_cost_groups
WHERE cost_group_id = l_txfr_cost_group_id;
SELECT count(*)
INTO l_same_le_ct
FROM cst_le_cost_types
WHERE legal_entity = l_txfr_legal_entity
AND cost_type_id = i_ae_txn_rec.cost_type_id;
SELECT NVL(CPIC.item_cost,0)
INTO l_pacp_pwac_cost
FROM CST_PAC_ITEM_COSTS CPIC
WHERE CPIC.INVENTORY_ITEM_ID = i_ae_txn_rec.inventory_item_id
AND CPIC.COST_GROUP_ID = i_ae_txn_rec.cost_group_id
AND CPIC.PAC_PERIOD_ID = i_ae_txn_rec.accounting_period_id;
SELECT nvl(transfer_percentage,0),nvl(transfer_cost,0)
INTO l_txfr_percent,l_txfr_cost
FROM mtl_material_transactions
WHERE transaction_id = i_ae_txn_rec.transaction_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT nvl(CPIC.item_cost,0)
INTO l_prev_period_pwac_cost
FROM CST_PAC_ITEM_COSTS CPIC
WHERE CPIC.INVENTORY_ITEM_ID = i_ae_txn_rec.inventory_item_id
AND CPIC.COST_GROUP_ID = i_ae_txn_rec.cost_group_id
AND CPIC.PAC_PERIOD_ID = l_prev_period_id;
SELECT nvl(transfer_percentage,0),nvl(transfer_cost,0)
INTO l_txfr_percent,l_txfr_cost
FROM mtl_material_transactions
WHERE transaction_id = i_ae_txn_rec.transaction_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT sum(nvl(actual_cost,0))
INTO l_mpacd_cost
FROM mtl_pac_actual_cost_details
WHERE transaction_id = i_ae_txn_rec.transaction_id
AND pac_period_id = i_ae_txn_rec.accounting_period_id
AND cost_group_id = i_ae_txn_rec.cost_group_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT MOD(SUM(DECODE(process_enabled_flag,'Y',1,2)), 2)
INTO l_pd_txfr_ind
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = i_ae_txn_rec.xfer_organization_id
OR MP.ORGANIZATION_ID = i_ae_txn_rec.organization_id;
SELECT nvl(SUM(ABS(NVL(base_transaction_value, 0))),0)
INTO l_perp_ship_value
FROM mtl_transaction_accounts mta
WHERE mta.transaction_id = i_ae_txn_rec.transaction_id
and mta.organization_id = i_ae_txn_rec.organization_id
and mta.accounting_line_type IN (1,2,14)
and mta.base_transaction_value < 0;
SELECT nvl(transfer_percentage,0),nvl(transfer_cost,0)
INTO l_txfr_percent,l_txfr_cost
FROM mtl_material_transactions
WHERE transaction_id = i_ae_txn_rec.transaction_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT sum(nvl(actual_cost,0))
INTO l_mpacd_cost
FROM mtl_pac_actual_cost_details
WHERE transaction_id = i_ae_txn_rec.transaction_id
AND pac_period_id = i_ae_txn_rec.accounting_period_id
AND cost_group_id = i_ae_txn_rec.cost_group_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT mip.interorg_receivables_account,
mmt.transportation_dist_account,
nvl(mip.interorg_profit_account, -1),
nvl(mmt.transfer_price,0),
nvl(mmt.transportation_cost,0)
INTO l_io_rcv_acct,
l_io_freight_acct,
l_io_txfr_var_acct,
l_txfr_cost, -- transfer price
l_trp_cost -- transportation cost
FROM mtl_interorg_parameters mip,mtl_material_transactions mmt
WHERE mip.from_organization_id = i_ae_txn_rec.organization_id
AND mip.to_organization_id = i_ae_txn_rec.xfer_organization_id
AND mmt.transaction_id = i_ae_txn_rec.transaction_id;
SELECT NVL(CPIC.item_cost,0)
INTO l_pwac_cost
FROM CST_PAC_ITEM_COSTS CPIC
WHERE CPIC.INVENTORY_ITEM_ID = i_ae_txn_rec.inventory_item_id
AND CPIC.COST_GROUP_ID = i_ae_txn_rec.cost_group_id
AND CPIC.PAC_PERIOD_ID = i_ae_txn_rec.accounting_period_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT mip.interorg_payables_account,
mmt.transportation_dist_account
INTO l_io_pay_acct,
l_io_freight_acct
FROM mtl_interorg_parameters mip,
mtl_material_transactions mmt
WHERE mip.from_organization_id = i_ae_txn_rec.organization_id
AND mip.to_organization_id = i_ae_txn_rec.xfer_organization_id
AND mmt.transaction_id = i_ae_txn_rec.transaction_id;
SELECT nvl(transportation_cost,0) * l_conv_rate /* convert from shipping org currency to receiving org currency */
INTO l_trp_cost
FROM mtl_material_transactions
WHERE transaction_id = i_ae_txn_rec.transaction_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
select count(transaction_id)
into l_mat_ovhd_exists
from mtl_pac_actual_cost_details
where transaction_id = i_ae_txn_rec.transaction_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and cost_element_id = 2
and level_type = 1;
select sum(nvl(actual_cost,0))
into l_mat_ovhd_cost
from mtl_pac_cost_subelements
where transaction_id = i_ae_txn_rec.transaction_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_type_id = i_ae_txn_rec.cost_type_id
and cost_element_id = 2;
SELECT sum(nvl(transaction_cost,0))
INTO l_mptcd_cost
FROM mtl_pac_txn_cost_details
WHERE transaction_id = i_ae_txn_rec.transaction_id
AND pac_period_id = i_ae_txn_rec.accounting_period_id
AND cost_group_id = i_ae_txn_rec.cost_group_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT mip.interorg_receivables_account,
mmt.transportation_dist_account,
mip.interorg_transfer_cr_account,
nvl(mip.interorg_profit_account, -1)
INTO l_io_rcv_acct,
l_io_freight_acct,
l_io_txfr_cr_acct,
l_io_txfr_var_acct
FROM mtl_interorg_parameters mip,mtl_material_transactions mmt
WHERE mip.from_organization_id = i_ae_txn_rec.organization_id
AND mip.to_organization_id = i_ae_txn_rec.xfer_organization_id
AND mmt.transaction_id = i_ae_txn_rec.transaction_id;
SELECT TRANSFER_COST_FLAG
INTO l_transfer_cost_flag
FROM CST_LE_COST_TYPES
WHERE LEGAL_ENTITY = i_ae_txn_rec.legal_entity_id
AND COST_TYPE_ID = i_ae_txn_rec.cost_type_id;
SELECT nvl(max(cpp.pac_period_id), -1)
INTO l_prev_period_id
FROM cst_pac_periods cpp
WHERE cpp.cost_type_id = i_ae_txn_rec.cost_type_id
AND cpp.legal_entity = i_ae_txn_rec.legal_entity_id
AND cpp.pac_period_id < i_ae_txn_rec.accounting_period_id;
SELECT NVL(MAX(cost_group_id),-1)
INTO l_txfr_cost_group_id
FROM cst_cost_group_assignments
WHERE organization_id = i_ae_txn_rec.xfer_organization_id;
SELECT legal_entity
INTO l_txfr_legal_entity
FROM cst_cost_groups
WHERE cost_group_id = l_txfr_cost_group_id;
SELECT count(*)
INTO l_same_le_ct
FROM cst_le_cost_types
WHERE legal_entity = l_txfr_legal_entity
AND cost_type_id = i_ae_txn_rec.cost_type_id;
SELECT NVL(CPIC.item_cost,0)
INTO l_pacp_pwac_cost
FROM CST_PAC_ITEM_COSTS CPIC
WHERE CPIC.INVENTORY_ITEM_ID = i_ae_txn_rec.inventory_item_id
AND CPIC.COST_GROUP_ID = i_ae_txn_rec.cost_group_id
AND CPIC.PAC_PERIOD_ID = i_ae_txn_rec.accounting_period_id;
SELECT nvl(transfer_percentage,0),
nvl(transfer_cost,0),
nvl(transportation_cost,0)
INTO l_txfr_percent,
l_txfr_cost,
l_trp_cost
FROM mtl_material_transactions
WHERE transaction_id = i_ae_txn_rec.transaction_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT nvl(CPIC.item_cost,0)
INTO l_prev_period_pwac_cost
FROM CST_PAC_ITEM_COSTS CPIC
WHERE CPIC.INVENTORY_ITEM_ID = i_ae_txn_rec.inventory_item_id
AND CPIC.COST_GROUP_ID = i_ae_txn_rec.cost_group_id
AND CPIC.PAC_PERIOD_ID = l_prev_period_id;
SELECT nvl(transfer_percentage,0),
nvl(transfer_cost,0),
nvl(transportation_cost,0)
INTO l_txfr_percent,
l_txfr_cost,
l_trp_cost
FROM mtl_material_transactions
WHERE transaction_id = i_ae_txn_rec.transaction_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT sum(nvl(actual_cost,0))
INTO l_mpacd_cost
FROM mtl_pac_actual_cost_details
WHERE transaction_id = i_ae_txn_rec.transaction_id
AND pac_period_id = i_ae_txn_rec.accounting_period_id
AND cost_group_id = i_ae_txn_rec.cost_group_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT MOD(SUM(DECODE(process_enabled_flag,'Y',1,2)), 2)
INTO l_pd_txfr_ind
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = i_ae_txn_rec.xfer_organization_id
OR MP.ORGANIZATION_ID = i_ae_txn_rec.organization_id;
SELECT mip.interorg_receivables_account,
mmt.transportation_dist_account,
mip.interorg_transfer_cr_account,
nvl(mip.interorg_profit_account, -1)
INTO l_io_rcv_acct,
l_io_freight_acct,
l_io_txfr_cr_acct,
l_io_txfr_var_acct
FROM mtl_interorg_parameters mip,mtl_material_transactions mmt
WHERE mip.from_organization_id = i_ae_txn_rec.organization_id -- INVCONV condition is opposite
AND mip.to_organization_id = i_ae_txn_rec.xfer_organization_id
AND mmt.transaction_id = i_ae_txn_rec.transaction_id;
SELECT nvl(transfer_price,0),
nvl(transportation_cost,0)
INTO l_txfr_cost, -- transfer price
l_trp_cost -- transportation cost
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = i_ae_txn_rec.transaction_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT NVL(CPIC.item_cost,0)
INTO l_pwac_cost
FROM CST_PAC_ITEM_COSTS CPIC
WHERE CPIC.INVENTORY_ITEM_ID = i_ae_txn_rec.inventory_item_id
AND CPIC.COST_GROUP_ID = i_ae_txn_rec.cost_group_id
AND CPIC.PAC_PERIOD_ID = i_ae_txn_rec.accounting_period_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT nvl(SUM(ABS(NVL(base_transaction_value, 0))),0)
INTO l_perp_ship_value
FROM mtl_transaction_accounts mta
WHERE mta.transaction_id = i_ae_txn_rec.transaction_id
and mta.organization_id = i_ae_txn_rec.organization_id
and mta.accounting_line_type IN (1,2,14)
and mta.base_transaction_value < 0;
SELECT nvl(transfer_percentage,0),nvl(transfer_cost,0),nvl(transportation_cost,0)
INTO l_txfr_percent,l_txfr_cost,l_trp_cost
FROM mtl_material_transactions
WHERE transaction_id = i_ae_txn_rec.transaction_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT sum(nvl(actual_cost,0))
INTO l_mpacd_cost
FROM mtl_pac_actual_cost_details
WHERE transaction_id = i_ae_txn_rec.transaction_id
AND pac_period_id = i_ae_txn_rec.accounting_period_id
AND cost_group_id = i_ae_txn_rec.cost_group_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT nvl(fnd_profile.value('CST_TRANSFER_PRICING_OPTION'), 0)
INTO l_tprice_option
FROM mtl_intercompany_parameters MIP
WHERE fnd_profile.value('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER') = 1
AND ( select count(1)
FROM hr_organization_information HOI
where HOI.organization_id = decode(i_ae_txn_rec.txn_action_id, 21,
i_ae_txn_rec.organization_id,
i_ae_txn_rec.xfer_organization_id)
AND HOI.org_information_context = 'Accounting Information'
AND MIP.ship_organization_id = to_number(HOI.org_information3)
AND ROWNUM < 2) > 0
AND (select count(1)
FROM hr_organization_information HOI2
WHERE HOI2.organization_id = decode(i_ae_txn_rec.txn_action_id, 21,
i_ae_txn_rec.xfer_organization_id,
i_ae_txn_rec.organization_id)
AND HOI2.org_information_context = 'Accounting Information'
AND MIP.sell_organization_id = to_number(HOI2.org_information3)
AND ROWNUM < 2) > 0
AND MIP.flow_type = 1;
SELECT MOD(SUM(DECODE(process_enabled_flag,'Y',1,2)), 2)
INTO l_pd_txfr_ind
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = i_ae_txn_rec.xfer_organization_id
OR MP.ORGANIZATION_ID = i_ae_txn_rec.organization_id;
SELECT nvl(expense_account_id,-1)
INTO l_accrual_account
FROM mtl_material_transactions
WHERE transaction_id = i_ae_txn_rec.transaction_id;
select count(transaction_id)
into l_mat_ovhd_exists
from mtl_pac_actual_cost_details
where transaction_id = i_ae_txn_rec.transaction_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and cost_element_id = 2
and level_type = 1;
SELECT NVL(mmt.transfer_price,0)
INTO l_transfer_price
FROM mtl_material_transactions MMT
WHERE MMT.transaction_id = i_ae_txn_rec.transaction_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT sum(nvl(transaction_cost,0))
INTO l_mptcd_cost
FROM mtl_pac_txn_cost_details
WHERE transaction_id = i_ae_txn_rec.transaction_id
AND pac_period_id = i_ae_txn_rec.accounting_period_id
AND cost_group_id = i_ae_txn_rec.cost_group_id;
SELECT nvl(profit_in_inv_account, -1)
INTO l_profit_in_inv_account
FROM mtl_interorg_parameters
WHERE from_organization_id = i_ae_txn_rec.xfer_organization_id
AND to_organization_id = i_ae_txn_rec.organization_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT MOD(SUM(DECODE(process_enabled_flag,'Y',1,2)), 2)
INTO l_pd_txfr_ind
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = i_ae_txn_rec.xfer_organization_id
OR MP.ORGANIZATION_ID = i_ae_txn_rec.organization_id;
select count(transaction_id)
into l_mat_ovhd_exists
from mtl_pac_actual_cost_details
where transaction_id = i_ae_txn_rec.transaction_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and cost_element_id = 2
and level_type = 1;
SELECT mip.interorg_payables_account
INTO l_io_pay_acct
FROM mtl_interorg_parameters mip
WHERE mip.from_organization_id = i_ae_txn_rec.xfer_organization_id
AND mip.to_organization_id = i_ae_txn_rec.organization_id;
SELECT sum(nvl(transaction_cost,0))
INTO l_mptcd_cost
FROM mtl_pac_txn_cost_details
WHERE transaction_id = i_ae_txn_rec.transaction_id
AND pac_period_id = i_ae_txn_rec.accounting_period_id
AND cost_group_id = i_ae_txn_rec.cost_group_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT mip.interorg_receivables_account,
mmt.transportation_dist_account,
mip.interorg_transfer_cr_account,
nvl(mip.interorg_profit_account, -1)
INTO l_io_rcv_acct,
l_io_freight_acct,
l_io_txfr_cr_acct,
l_io_txfr_var_acct
FROM mtl_interorg_parameters mip,mtl_material_transactions mmt
WHERE mip.from_organization_id = i_ae_txn_rec.xfer_organization_id
AND mip.to_organization_id = i_ae_txn_rec.organization_id
AND mmt.transaction_id = i_ae_txn_rec.transaction_id;
SELECT TRANSFER_COST_FLAG
INTO l_transfer_cost_flag
FROM CST_LE_COST_TYPES
WHERE LEGAL_ENTITY = i_ae_txn_rec.legal_entity_id
AND COST_TYPE_ID = i_ae_txn_rec.cost_type_id;
SELECT nvl(max(cpp.pac_period_id), -1)
INTO l_prev_period_id
FROM cst_pac_periods cpp
WHERE cpp.cost_type_id = i_ae_txn_rec.cost_type_id
AND cpp.legal_entity = i_ae_txn_rec.legal_entity_id
AND cpp.pac_period_id < i_ae_txn_rec.accounting_period_id;
SELECT NVL(MAX(cost_group_id),-1)
INTO l_txfr_cost_group_id
FROM cst_cost_group_assignments
WHERE organization_id = i_ae_txn_rec.organization_id;
SELECT legal_entity
INTO l_txfr_legal_entity
FROM cst_cost_groups
WHERE cost_group_id = l_txfr_cost_group_id;
SELECT count(*)
INTO l_same_le_ct
FROM cst_le_cost_types
WHERE legal_entity = l_txfr_legal_entity
AND cost_type_id = i_ae_txn_rec.cost_type_id;
SELECT sum(NVL(CPIC.item_cost,0))
INTO l_pacp_pwac_cost
FROM CST_PAC_ITEM_COSTS CPIC
WHERE CPIC.INVENTORY_ITEM_ID = i_ae_txn_rec.inventory_item_id
AND CPIC.COST_GROUP_ID = i_ae_txn_rec.cost_group_id
AND CPIC.PAC_PERIOD_ID = i_ae_txn_rec.accounting_period_id;
SELECT nvl(transfer_percentage,0),nvl(transfer_cost,0),nvl(transportation_cost,0),primary_quantity
INTO l_txfr_percent,l_txfr_cost,l_trp_cost,l_txfr_txn_qty
FROM mtl_material_transactions
WHERE transaction_id = l_txfr_txn_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT nvl(CPIC.item_cost,0)
INTO l_prev_period_pwac_cost
FROM CST_PAC_ITEM_COSTS CPIC
WHERE CPIC.INVENTORY_ITEM_ID = i_ae_txn_rec.inventory_item_id
AND CPIC.COST_GROUP_ID = i_ae_txn_rec.cost_group_id
AND CPIC.PAC_PERIOD_ID = l_prev_period_id;
SELECT nvl(transfer_percentage,0),nvl(transfer_cost,0),nvl(transportation_cost,0),primary_quantity
INTO l_txfr_percent,l_txfr_cost,l_trp_cost,l_txfr_txn_qty
FROM mtl_material_transactions
WHERE transaction_id = l_txfr_txn_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT sum(nvl(actual_cost,0))
INTO l_mpacd_cost
FROM mtl_pac_actual_cost_details
WHERE transaction_id = i_ae_txn_rec.transaction_id
AND pac_period_id = i_ae_txn_rec.accounting_period_id
AND cost_group_id = i_ae_txn_rec.cost_group_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT nvl(SUM(ABS(NVL(base_transaction_value, 0))),0)
INTO l_perp_ship_value
FROM mtl_transaction_accounts mta
WHERE mta.transaction_id = l_txfr_txn_id
and mta.organization_id = i_ae_txn_rec.xfer_organization_id
and mta.accounting_line_type IN (1,2,14)
and mta.base_transaction_value < 0;
/* Select transfer cost and transportation cost from the transfer transaction (shipment txn).
We also need the shipment transaction quantity since this is the quantity we'll
need to divide by to get the unit transfer and transportation cost. */
SELECT nvl(transfer_percentage,0),nvl(transfer_cost,0),nvl(transportation_cost,0),primary_quantity
INTO l_txfr_percent,l_txfr_cost,l_trp_cost,l_txfr_txn_qty
FROM mtl_material_transactions
WHERE transaction_id = l_txfr_txn_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT sum(nvl(actual_cost,0))
INTO l_mpacd_cost
FROM mtl_pac_actual_cost_details
WHERE transaction_id = i_ae_txn_rec.transaction_id
AND pac_period_id = i_ae_txn_rec.accounting_period_id
AND cost_group_id = i_ae_txn_rec.cost_group_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT MOD(SUM(DECODE(process_enabled_flag,'Y',1,2)), 2)
INTO l_pd_txfr_ind
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = i_ae_txn_rec.xfer_organization_id
OR MP.ORGANIZATION_ID = i_ae_txn_rec.organization_id;
select count(transaction_id)
into l_mat_ovhd_exists
from mtl_pac_actual_cost_details
where transaction_id = i_ae_txn_rec.transaction_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and cost_element_id = 2
and level_type = 1;
SELECT mip.interorg_payables_account
INTO l_io_pay_acct
FROM mtl_interorg_parameters mip
WHERE mip.from_organization_id = i_ae_txn_rec.xfer_organization_id
AND mip.to_organization_id = i_ae_txn_rec.organization_id;
SELECT sum(nvl(transaction_cost,0))
INTO l_mptcd_cost
FROM mtl_pac_txn_cost_details
WHERE transaction_id = i_ae_txn_rec.transaction_id
AND pac_period_id = i_ae_txn_rec.accounting_period_id
AND cost_group_id = i_ae_txn_rec.cost_group_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT mip.interorg_payables_account,
mmt.transportation_dist_account
INTO l_io_pay_acct,
l_io_freight_acct
FROM mtl_interorg_parameters mip,
mtl_material_transactions mmt
WHERE mip.from_organization_id = i_ae_txn_rec.xfer_organization_id
AND mip.to_organization_id = i_ae_txn_rec.organization_id
AND mmt.transaction_id = i_ae_txn_rec.transaction_id;
SELECT nvl(transfer_price,0),
nvl(transportation_cost,0)
INTO l_txfr_cost, -- transfer price
l_trp_cost -- transportation cost
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = i_ae_txn_rec.transaction_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
select count(transaction_id)
into l_mat_ovhd_exists
from mtl_pac_actual_cost_details
where transaction_id = i_ae_txn_rec.transaction_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and cost_element_id = 2
and level_type = 1;
SELECT sum(nvl(actual_cost,0))
INTO l_txfr_cost
FROM mtl_pac_actual_cost_details
WHERE transaction_id = i_ae_txn_rec.transaction_id
AND pac_period_id = i_ae_txn_rec.accounting_period_id
AND cost_group_id = i_ae_txn_rec.cost_group_id;
select sum(nvl(actual_cost,0))
into l_mat_ovhd_cost
from mtl_pac_cost_subelements
where transaction_id = i_ae_txn_rec.transaction_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_type_id = i_ae_txn_rec.cost_type_id
and cost_element_id = 2;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT mip.interorg_receivables_account,
mmt.transportation_dist_account,
mip.interorg_transfer_cr_account,
nvl(mip.interorg_profit_account, -1)
INTO l_io_rcv_acct,
l_io_freight_acct,
l_io_txfr_cr_acct,
l_io_txfr_var_acct
FROM mtl_interorg_parameters mip,mtl_material_transactions mmt
WHERE mip.from_organization_id = i_ae_txn_rec.organization_id -- INVCONV condition is opposite
AND mip.to_organization_id = i_ae_txn_rec.xfer_organization_id
AND mmt.transaction_id = i_ae_txn_rec.transaction_id;
SELECT nvl(transfer_price,0),
nvl(transportation_cost,0)
INTO l_txfr_cost, -- transfer price
l_trp_cost -- transportation cost
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = i_ae_txn_rec.transaction_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT NVL(CPIC.item_cost,0)
INTO l_pwac_cost
FROM CST_PAC_ITEM_COSTS CPIC
WHERE CPIC.INVENTORY_ITEM_ID = i_ae_txn_rec.inventory_item_id
AND CPIC.COST_GROUP_ID = i_ae_txn_rec.cost_group_id
AND CPIC.PAC_PERIOD_ID = i_ae_txn_rec.accounting_period_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
select count(legal_entity_id) /* Bug 4586534 */
into l_acct_exist
from mtl_fiscal_cat_accounts
where legal_entity_id = i_ae_txn_rec.legal_entity_id
and cost_type_id = i_ae_txn_rec.cost_type_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and category_id = i_ae_txn_rec.category_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_acct_rec.mat_account,
l_acct_rec.mat_ovhd_account,
l_acct_rec.res_account,
l_acct_rec.osp_account,
l_acct_rec.ovhd_account
from mtl_fiscal_cat_accounts
where legal_entity_id = i_ae_txn_rec.legal_entity_id
and cost_type_id = i_ae_txn_rec.cost_type_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and category_id = i_ae_txn_rec.category_id;
SELECT nvl(max(primary_cost_method),-1)
INTO l_cost_method
FROM cst_le_cost_types clct
WHERE clct.legal_entity = i_ae_txn_rec.legal_entity_id
AND clct.cost_type_id = i_ae_txn_rec.cost_type_id;
select count(transaction_id) /* Bug 4586534 */
into l_ele_exist
from mtl_pac_actual_cost_details mpacd
where mpacd.transaction_id = i_ae_txn_rec.transaction_id
and mpacd.pac_period_id = i_ae_txn_rec.accounting_period_id
and mpacd.cost_group_id = i_ae_txn_rec.cost_group_id
and rownum < 2; /* 4586534 added rownum filter for perf as its only existence check */
select sum(nvl(variance_amount,0))
into l_var_total
from mtl_pac_actual_cost_details mpacd
where mpacd.transaction_id = i_ae_txn_rec.transaction_id
and mpacd.pac_period_id = i_ae_txn_rec.accounting_period_id
and mpacd.cost_group_id = i_ae_txn_rec.cost_group_id;
select count (1)
into l_value_change_flag
from mtl_pac_txn_cost_details mptcd
where mptcd.transaction_id = i_ae_txn_rec.transaction_id
and mptcd.pac_period_id = i_ae_txn_rec.accounting_period_id
and mptcd.cost_group_id = i_ae_txn_rec.cost_group_id
and mptcd.value_change is not null
and rownum < 2;
select decode (l_value_change_flag,
0, (sum(nvl(new_cost,0)) - sum(nvl(prior_cost,0))),
sum (nvl(actual_cost, 0))),
sum(nvl(variance_amount,0)),
sum(nvl(onhand_variance_amount,0))
into l_cost,
l_var,
l_onhand_var
from mtl_pac_actual_cost_details
where transaction_id = i_ae_txn_rec.transaction_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and cost_element_id = cost_element;
/* Bug# 4586534. Replaced select statement from dual to PL/SQL based logic.
select decode(cost_element, 1, l_acct_rec.mat_account,
2, l_acct_rec.mat_ovhd_account,
3, l_acct_rec.res_account,
4, l_acct_rec.osp_account,
5, l_acct_rec.ovhd_account)
into l_ae_line_rec.account
from dual;
CSTPAPBR.insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
/* Bug 4586534. Modified to PL/SQL logic based instead of using select from dual and
moved up (stmt 32) to include validation that no distribution for the cost element would be
done if cost is zero and no account is provided for the cost element.
select decode(cost_element, 1, i_ae_txn_rec.mat_account,
2, i_ae_txn_rec.mat_ovhd_account,
3, i_ae_txn_rec.res_account,
4, i_ae_txn_rec.osp_account,
5, i_ae_txn_rec.ovhd_account)
into l_ae_line_rec.account
from dual;
CSTPAPBR.insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
CSTPAPBR.insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
select count(legal_entity_id) /* Bug No. 4586534 */
into l_acct_exist
from cst_org_cost_group_accounts
where legal_entity_id = i_ae_txn_rec.legal_entity_id
and cost_type_id = i_ae_txn_rec.cost_type_id
and cost_group_id = i_ae_txn_rec.cost_group_id;
/*Check if there was anything inserted into the adjustment accounts at all.If not then drive everything to variance and debit the Material account */
If l_loop_count = 5 then
If l_var_total < 0 then
l_dr_flag := "TRUE";
/* bug 4586534. changed to PL/SQL logic based instead of select from dual
select i_ae_txn_rec.mat_account
into l_ae_line_rec.account
from dual;
CSTPAPBR.insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
select nvl(cost_variance_account,-1)
into l_ae_line_rec.account
from cst_org_cost_group_accounts
where legal_entity_id = i_ae_txn_rec.legal_entity_id
and cost_type_id = i_ae_txn_rec.cost_type_id
and cost_group_id = i_ae_txn_rec.cost_group_id;
CSTPAPBR.insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
select decode(asset_inventory,1,0,1)
into l_exp_sub
from mtl_secondary_inventories
where secondary_inventory_name = i_ae_txn_rec.subinventory_code
and organization_id = i_ae_txn_rec.organization_id;
select nvl(rt.dropship_type_code, 3)
into l_dropship_type_code
from rcv_transactions rt, mtl_material_transactions mmt
where mmt.rcv_transaction_id = rt.transaction_id
and mmt.transaction_id = i_ae_txn_rec.transaction_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_ae_txn_rec.om_line_id
AND ool.reference_line_id = crcml.cogs_om_line_id
AND crcml.pac_cost_type_id = i_ae_txn_rec.cost_type_id
AND i_ae_txn_rec.txn_src_type_id = 12;
SELECT count(*)
INTO l_count_so
FROM mtl_material_transactions MMT,
mtl_pac_actual_cost_details MPACD
WHERE (MMT.transaction_action_id = 1 OR MMT.transaction_action_id = 7)
AND MMT.transaction_source_type_id = 2
AND EXISTS (SELECT 1
FROM cst_cost_group_assignments CCGA
WHERE CCGA.cost_group_id = i_ae_txn_rec.cost_group_id
AND CCGA.organization_id = MMT.organization_id)
AND MMT.inventory_item_id = i_ae_txn_rec.inventory_item_id
AND MMT.trx_source_line_id = l_ref_om_line_id
AND MPACD.transaction_id = MMT.transaction_id
AND MPACD.cost_type_id = i_ae_txn_rec.cost_type_id;
SELECT deferred_cogs_acct_id
INTO l_acct_rec.account
FROM cst_revenue_cogs_match_lines
WHERE cogs_om_line_id = i_ae_txn_rec.om_line_id
AND pac_cost_type_id = i_ae_txn_rec.cost_type_id;
select nvl(msi.cost_of_sales_account, mp.cost_of_sales_account)
into l_acct_rec.account
from mtl_system_items msi,
mtl_parameters mp
where msi.organization_id = i_ae_txn_rec.organization_id
and msi.inventory_item_id = i_ae_txn_rec.inventory_item_id
and mp.organization_id = msi.organization_id;
select distribution_account
into l_acct_rec.account
from mtl_generic_dispositions
where disposition_id = i_ae_txn_rec.txn_src_id
and organization_id = i_ae_txn_rec.organization_id;
select count(transaction_id) /* Changed for the bug No . 4586534 */
into l_mat_ovhd_exists
from mtl_pac_actual_cost_details
where transaction_id = i_ae_txn_rec.transaction_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and cost_element_id = 2
and level_type = 1;
select decode(asset_inventory,1,0,1)
into l_exp_sub
from mtl_secondary_inventories
where secondary_inventory_name = i_ae_txn_rec.subinventory_code
and organization_id = i_ae_txn_rec.organization_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_ae_txn_rec.om_line_id
AND ool.reference_line_id = crcml.cogs_om_line_id
AND crcml.pac_cost_type_id = i_ae_txn_rec.cost_type_id
AND i_ae_txn_rec.txn_src_type_id = 12;
select NVL(b.cost_of_sales_account,-1)
into l_std_cost_adj_var_acct
from mtl_parameters b
where organization_id = i_ae_txn_rec.organization_id;
CSTPAPBR.insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec_std_var,
l_ae_line_tbl,
l_err_rec);
SELECT deferred_cogs_acct_id
INTO l_acct_rec.account
FROM cst_revenue_cogs_match_lines
WHERE cogs_om_line_id = i_ae_txn_rec.om_line_id
AND pac_cost_type_id = i_ae_txn_rec.cost_type_id;
procedure cost_consigned_update_txn(
i_ae_txn_rec IN CSTPALTY.cst_ae_txn_rec_type,
i_ae_curr_rec IN CSTPALTY.cst_ae_curr_rec_type,
l_ae_line_tbl IN OUT NOCOPY CSTPALTY.cst_ae_line_tbl_type,
o_ae_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type
) IS
l_exp_sub NUMBER;
select nvl(quantity_adjusted, 0)
into l_qty
from mtl_material_transactions
where transaction_id = i_ae_txn_rec.transaction_id;
select nvl(retroprice_adj_account_id,-1)
into l_acct_rec.account
from rcv_parameters
where organization_id = i_ae_txn_rec.organization_id;
select count(*)
into l_ele_exist
from mtl_pac_actual_cost_details mpacd
where mpacd.transaction_id = i_ae_txn_rec.transaction_id
and mpacd.pac_period_id = i_ae_txn_rec.accounting_period_id
and mpacd.cost_group_id = i_ae_txn_rec.cost_group_id;
select sum(nvl(actual_cost,0))
into l_cost
from mtl_pac_actual_cost_details
where transaction_id = i_ae_txn_rec.transaction_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_group_id = i_ae_txn_rec.cost_group_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
fnd_file.put_line(fnd_file.log,'Cost_consigned_update_txn >> ');
END cost_consigned_update_txn;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
select count(legal_entity_id) /* changed for bug no. 4586534 */
into l_acct_exist
from mtl_fiscal_cat_accounts
where legal_entity_id = i_ae_txn_rec.legal_entity_id
and cost_type_id = i_ae_txn_rec.cost_type_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and category_id = i_ae_txn_rec.category_id;
select nvl(material_account,-1),
nvl(material_overhead_account,-1),
nvl(resource_account,-1),
nvl(outside_processing_account,-1),
nvl(overhead_account,-1),
nvl(expense_account, -1)
into l_acct_rec.mat_account,
l_acct_rec.mat_ovhd_account,
l_acct_rec.res_account,
l_acct_rec.osp_account,
l_acct_rec.ovhd_account,
l_expense_account
from mtl_fiscal_cat_accounts
where legal_entity_id = i_ae_txn_rec.legal_entity_id
and cost_type_id = i_ae_txn_rec.cost_type_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and category_id = i_ae_txn_rec.category_id;
select count(transaction_id)
into l_ele_exist
from mtl_pac_actual_cost_details mpacd
where mpacd.transaction_id = i_ae_txn_rec.transaction_id
and mpacd.pac_period_id = i_ae_txn_rec.accounting_period_id
and mpacd.cost_group_id = i_ae_txn_rec.cost_group_id
and rownum < 2; /* 4586534 added rownum filter for perf as its only existence check */
select sum(nvl(actual_cost,0)),
sum(nvl(variance_amount,0)) /* Bug 3123936: Prop of 2896193 */
into l_cost, l_var
from mtl_pac_actual_cost_details
where transaction_id = i_ae_txn_rec.transaction_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and cost_element_id = cost_element;
fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ': Insert element ' || cost_element);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag, --i_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
select sum(nvl(variance_amount,0))
into l_var
from mtl_pac_actual_cost_details
where transaction_id = i_ae_txn_rec.transaction_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_group_id = i_ae_txn_rec.cost_group_id;
SELECT nvl(cost_variance_account,-1)
INTO l_ae_line_rec.account
FROM cst_org_cost_group_accounts
WHERE cost_group_id=i_ae_txn_rec.cost_group_id
AND legal_entity_id = i_ae_txn_rec.legal_entity_id
AND cost_type_id = i_ae_txn_rec.cost_type_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
select count(transaction_id)
into l_ele_exist
from mtl_pac_actual_cost_details
where transaction_id = i_ae_txn_rec.transaction_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and rownum < 2; /* 4586534 added rownum filter for perf as its only existence check */
select sum(nvl(actual_cost,0))
into l_cost
from mtl_pac_actual_cost_details
where transaction_id = i_ae_txn_rec.transaction_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and 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)));
select decode(cost_element, 1, i_ae_acct_rec.mat_account,
2, i_ae_acct_rec.mat_ovhd_account,
3, i_ae_acct_rec.res_account,
4, i_ae_acct_rec.osp_account,
5, i_ae_acct_rec.ovhd_account)
into l_ae_line_rec.account
from dual;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
i_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
select nvl(sum(nvl(actual_cost,0)),0)
into l_cost
from mtl_pac_actual_cost_details
where transaction_id = i_ae_txn_rec.transaction_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and (cost_element_id <> 2
OR
(cost_element_id = 2
and level_type = decode(i_ovhd_absp,1,2,2,0,level_type)));
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
i_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
select resource_id, actual_cost
from mtl_pac_cost_subelements
where transaction_id = i_ae_txn_rec.transaction_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_type_id = i_ae_txn_rec.cost_type_id
and cost_element_id = 2;
SELECT NVL(cost_group_id, -1)
INTO l_txn_cost_group_id
FROM cst_cost_group_assignments
WHERE organization_id = i_ae_txn_rec.organization_id;
select nvl(absorption_account,-1)
into l_ae_line_rec.account
from bom_resources
where resource_id = l_res_id
and organization_id = i_ae_txn_rec.xfer_organization_id;
select nvl(absorption_account,-1)
into l_ae_line_rec.account
from bom_resources
where resource_id = l_res_id
and organization_id = i_ae_txn_rec.organization_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
i_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
PROCEDURE insert_account(
i_ae_txn_rec IN CSTPALTY.cst_ae_txn_rec_type,
i_ae_curr_rec IN CSTPALTY.cst_ae_curr_rec_type,
--i_ae_txn_rec IN CSTPALBR.cst_ae_txn_rec_type,
--i_ae_curr_rec IN CSTPALBR.cst_ae_curr_rec_type,
i_dr_flag IN BOOLEAN,
i_ae_line_rec IN CSTPALTY.cst_ae_line_rec_type,
l_ae_line_tbl IN OUT NOCOPY CSTPALTY.cst_ae_line_tbl_type,
o_ae_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type)
--i_ae_line_rec IN CSTPALBR.cst_ae_line_rec_type,
--l_ae_line_tbl IN OUT CSTPALBR.cst_ae_line_tbl_type,
--o_ae_err_rec OUT CSTPALBR.cst_ae_err_rec_type)
IS
l_err_rec CSTPALTY.cst_ae_err_rec_type;
l_api_name CONSTANT VARCHAR2(30) := 'CSTPAPBR.insert_account';
select
meaning
into
l_ae_line_tbl(next_record_avail).description
from
mfg_lookups
where lookup_type = 'CST_ACCOUNTING_LINE_TYPE'
and lookup_code = l_ae_line_tbl(next_record_avail).ae_line_type;
/* Bug 4586534. changed to PL/SQL logic based instead of select from dual
select
nvl(i_ae_curr_rec.alt_currency,i_ae_curr_rec.pri_currency)
into l_ae_line_tbl(next_record_avail).currency_code
from dual;
/*select
decode(i_ae_curr_rec.alt_currency,
i_ae_curr_rec.pri_currency,NULL,
i_ae_curr_rec.currency_conv_date)
into l_ae_line_tbl(next_record_avail).currency_conv_date
from dual;
/*select
decode(i_ae_curr_rec.alt_currency,
i_ae_curr_rec.pri_currency,NULL,
decode(i_ae_curr_rec.currency_conv_rate,-1,NULL,i_ae_curr_rec.currency_conv_rate))
into l_ae_line_tbl(next_record_avail).currency_conv_rate
from dual;
/*select
decode(i_ae_curr_rec.alt_currency,
i_ae_curr_rec.pri_currency,NULL,
i_ae_curr_rec.currency_conv_type)
into l_ae_line_tbl(next_record_avail).currency_conv_type
from dual;
select decode(i_ae_curr_rec.alt_currency,NULL, NULL,
i_ae_curr_rec.pri_currency, NULL,
decode(c2.minimum_accountable_unit,
NULL,
round(i_ae_line_rec.transaction_value/nvl(i_ae_curr_rec.currency_conv_rate,1),
c2.precision),
round(i_ae_line_rec.transaction_value/nvl(i_ae_curr_rec.currency_conv_rate,1)
/c2.minimum_accountable_unit)
* c2.minimum_accountable_unit )),
decode(c1.minimum_accountable_unit,
NULL, round(i_ae_line_rec.transaction_value, c1.precision),
round(i_ae_line_rec.transaction_value/c1.minimum_accountable_unit)
* c1.minimum_accountable_unit )
into
l_entered_value,
l_accounted_value
from
fnd_currencies c1,
fnd_currencies c2
where
c1.currency_code = i_ae_curr_rec.pri_currency
and c2.currency_code = decode(i_ae_curr_rec.alt_currency, NULL,
i_ae_curr_rec.pri_currency,
i_ae_curr_rec.alt_currency);
select decode(i_ae_txn_rec.primary_quantity,0,0,
abs(i_ae_line_rec.transaction_value/i_ae_txn_rec.primary_quantity))
into l_ae_line_tbl(next_record_avail).rate_or_amount
from dual;
select decode(asset_inventory,1,0,1)
into l_exp_sub1
from mtl_secondary_inventories
where secondary_inventory_name = i_ae_txn_rec.subinventory_code
and organization_id = i_ae_txn_rec.organization_id;
o_ae_err_rec.l_err_msg := 'CSTPAPBR.insert_account' || to_char(l_stmt_num) ||
substr(SQLERRM,1,180);
end insert_account;
select distinct resource_id /* added distinct for bug 3116821 */
from wip_pac_actual_cost_details
where transaction_id = l_transaction_id
and pac_period_id = l_accounting_period_id
and cost_group_id = l_cost_group_id
and cost_element_id = l_cost_element;
select currency_code
into l_ae_curr_rec.pri_currency
from gl_sets_of_books
where set_of_books_id = i_ae_txn_rec.set_of_books_id;
select count(transaction_id)
into l_ele_exist
from wip_pac_actual_cost_details mpacd
where mpacd.transaction_id = i_ae_txn_rec.transaction_id
and mpacd.pac_period_id = i_ae_txn_rec.accounting_period_id
and mpacd.cost_group_id = i_ae_txn_rec.cost_group_id
and rownum < 2; /* 4586534 added rownum filter for perf as its only existence check */
select
we.wip_entity_id
into
l_ae_line_rec.wip_entity_id
from wip_transactions wt,
wip_entities we
where we.wip_entity_id = wt.wip_entity_id
and wt.transaction_id = i_ae_txn_rec.transaction_id;
SELECT nvl(actual_value ,0) ,
nvl(actual_cost ,0)
INTO l_actual_value,
l_actual_cost
FROM wip_pac_actual_cost_details
WHERE transaction_id = i_ae_txn_rec.transaction_id
AND pac_period_id = i_ae_txn_rec.accounting_period_id
AND cost_group_id = i_ae_txn_rec.cost_group_id;
SELECT decode(l_cost_element, 1, nvl(material_account,-1),
3, nvl(resource_account, -1),
4, nvl(outside_processing_account, -1), -1)
INTO l_ae_line_rec.account
FROM wip_discrete_jobs
WHERE wip_entity_id = l_ae_line_rec.wip_entity_id;
/* Set the other fields we will need in Insert_Account procedure.
-- --------------------------------------------------------------*/
l_stmt_num := 70;
/* Call Insert Account.
-- --------------------*/
l_stmt_num := 90;
CSTPAPBR.insert_account (i_ae_txn_rec,
l_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_ae_err_rec);
/* Set the fields we will need in next Insert_Account calling.
-- ----------------------------------------------------------- */
l_stmt_num := 110;
SELECT RCV.receiving_account_id
INTO l_ae_line_rec.account
FROM rcv_parameters RCV , wip_transactions WT
WHERE WT.transaction_id = i_ae_txn_rec.transaction_id
AND WT.organization_id = RCV.organization_id;
/* Call Insert Account.
-- -------------------- */
l_stmt_num := 120;
CSTPAPBR.insert_account (i_ae_txn_rec,
l_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_ae_err_rec);
select sum(nvl(actual_value,0)),
sum(nvl(actual_cost,0))
into l_actual_value,
l_actual_cost
from wip_pac_actual_cost_details
where transaction_id = i_ae_txn_rec.transaction_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and cost_element_id = cost_element
and nvl(resource_id,-999) = nvl(l_ae_line_rec.resource_id,-999);
select nvl(actual_value ,0) ,
nvl(actual_cost ,0)
into l_actual_value,
l_actual_cost
from wip_pac_actual_cost_details
where transaction_id = i_ae_txn_rec.transaction_id
and pac_period_id = i_ae_txn_rec.accounting_period_id
and cost_group_id = i_ae_txn_rec.cost_group_id
and cost_element_id = cost_element
and resource_id = l_ae_line_rec.resource_id ;
select decode(i_ae_txn_rec.txn_type_id, 6, abs(l_actual_cost), abs(l_actual_value))
into l_ae_line_rec.transaction_value
from dual;
CSTPAPBR.insert_account (i_ae_txn_rec,
l_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_ae_err_rec);
select decode(i_ae_txn_rec.txn_type_id, 1, 4, 2, 3, 3, 4, 6, 8)
into l_ae_line_rec.ae_line_type
from dual;
CSTPAPBR.insert_account (i_ae_txn_rec,
l_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_ae_err_rec);
select
encumbrance_reversal_flag
into
l_enc_rev
from
mtl_parameters
where organization_id = i_ae_txn_rec.organization_id;
SELECT currency_code
, currency_conversion_date
, currency_conversion_type
, currency_conversion_rate
INTO
l_ae_enc_curr_rec.alt_currency
,l_ae_enc_curr_rec.currency_conv_date
,l_ae_enc_curr_rec.currency_conv_type
,l_ae_enc_curr_rec.currency_conv_rate
FROM wip_transaction_accounts
WHERE transaction_id = i_ae_txn_rec.transaction_id
AND accounting_line_type = 15;
select count(resource_id)
into l_acct_exist
from bom_resources
where resource_id = i_ae_line_rec.resource_id
and organization_id = i_ae_txn_rec.organization_id
and rownum < 2; /* 4586534 added rownum filter for perf as its only existence check */
select nvl(absorption_account,-1)
into o_acct_id2
from bom_resources
where resource_id = i_ae_line_rec.resource_id
and organization_id = i_ae_txn_rec.organization_id;
select we.entity_type,
we.wip_entity_id,
wt.line_id
into l_entity_type,
l_wip_entity_id,
l_line_id
from wip_transactions wt,
wip_entities we
where we.wip_entity_id = wt.wip_entity_id
and wt.transaction_id = i_ae_txn_rec.transaction_id;
select count(wip_entity_id) /* Changed for Bug No. 4586534 */
into l_acct_exist
from wip_discrete_jobs
where wip_entity_id = l_wip_entity_id;
select decode(i_ae_line_rec.cost_element_id,
1, nvl(material_account,-1),
2, nvl(material_overhead_account,-1),
3, nvl(resource_account,-1),
4, nvl(outside_processing_account,-1),
5, nvl(overhead_account,-1)),
--decode(i_ae_txn_rec.wip_txn_type,
decode(i_ae_txn_rec.txn_type_id,
6,decode(i_ae_line_rec.cost_element_id,
1, nvl(material_variance_account,-1),
2, nvl(material_variance_account,-1),
3, nvl(resource_variance_account,-1),
4, nvl(outside_proc_variance_account,-1),
5, nvl(overhead_variance_account,-1)),
o_acct_id2)
into o_acct_id1,
o_acct_id2
from wip_discrete_jobs
where wip_entity_id = l_wip_entity_id;
select count(wip_entity_id) /* Changed for bug No 4586534 */
into l_acct_exist
from wip_repetitive_schedules
where wip_entity_id = l_wip_entity_id
and line_id = l_line_id
and rownum < 2; /* 4586534 added rownum check as its only existence check */
select decode(i_ae_line_rec.cost_element_id,
1, nvl(material_account,-1),
2, nvl(material_overhead_account,-1),
3, nvl(resource_account,-1),
4, nvl(outside_processing_account,-1),
5, nvl(overhead_account,-1))
into o_acct_id1
from wip_repetitive_schedules
where wip_entity_id = l_wip_entity_id
and line_id = l_line_id
and rownum = 1;
select count(wip_entity_id) /* Changed For bug No. 4586534 */
into l_acct_exist
from wip_flow_schedules
where wip_entity_id = l_wip_entity_id;
select decode(i_ae_line_rec.cost_element_id,
1, nvl(material_account,-1),
2, nvl(material_overhead_account,-1),
3, nvl(resource_account,-1),
4, nvl(outside_processing_account,-1),
5, nvl(overhead_account,-1)),
decode(i_ae_txn_rec.txn_type_id,
6,decode(i_ae_line_rec.cost_element_id,
1, nvl(material_variance_account,-1),
2, nvl(material_variance_account,-1),
3, nvl(resource_variance_account,-1),
4, nvl(outside_proc_variance_account,-1),
5, nvl(overhead_variance_account,-1)),
o_acct_id2)
into o_acct_id1,
o_acct_id2
from wip_flow_schedules
where wip_entity_id = l_wip_entity_id;
SELECT COUNT(*)
INTO l_ele_exist
FROM MTL_PAC_ACTUAL_COST_DETAILS
WHERE transaction_id = i_ae_txn_rec.transaction_id
AND pac_period_id = i_ae_txn_rec.accounting_period_id
AND cost_group_id = i_ae_txn_rec.cost_group_id;
SELECT SUM(nvl(actual_cost,0)) ,
SUM(nvl(wip_variance,0))
INTO l_cost,
l_wip_variance
FROM mtl_pac_actual_cost_details
WHERe transaction_id = i_ae_txn_rec.transaction_id
AND pac_period_id = i_ae_txn_rec.accounting_period_id
AND cost_group_id = i_ae_txn_rec.cost_group_id
AND 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)));
select decode(cost_element, 1, i_ae_acct_rec.mat_account,
2, i_ae_acct_rec.mat_ovhd_account,
3, i_ae_acct_rec.res_account,
4, i_ae_acct_rec.osp_account,
5, i_ae_acct_rec.ovhd_account)
into l_ae_line_rec.account
from dual;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
i_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT decode(cost_element, 1, material_variance_account,
2, material_variance_account, -- Using Material Variance acct for cost element 2 also
3, resource_variance_account,
4, outside_proc_variance_account,
5, overhead_variance_account)
INTO l_ae_line_rec.account
FROM WIP_DISCRETE_JOBS
WHERE organization_id = i_ae_txn_rec.organization_id
AND wip_entity_id = i_ae_txn_rec.txn_src_id;
SELECT decode(cost_element, 1, material_variance_account,
2, material_variance_account, -- Using Material Variance acct for cost element 2 also
3, resource_variance_account,
4, outside_proc_variance_account,
5, overhead_variance_account)
INTO l_ae_line_rec.account
FROM WIP_REPETITIVE_SCHEDULES
WHERE organization_id = i_ae_txn_rec.organization_id
AND wip_entity_id = i_ae_txn_rec.txn_src_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
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_ae_txn_rec.organization_id
OR mp.organization_id = i_ae_txn_rec.xfer_organization_id;
SELECT cost_group_id
INTO l_shipping_cg_id
FROM cst_cost_group_assignments
WHERE organization_id = (SELECT decode(i_ae_txn_rec.txn_action_id,
21, organization_id,
22, organization_id, -- Logical Shipping txn
12, transfer_organization_id,
15, transfer_organization_id, -- Logical Receipt txn
3, decode(sign(i_ae_txn_rec.primary_quantity),
1, transfer_organization_id,
organization_id),
NULL)
FROM mtl_material_transactions
WHERE transaction_id = i_ae_txn_rec.transaction_id);
SELECT chart_of_accounts_id
INTO l_rcv_coa_id
FROM gl_sets_of_books
WHERE set_of_books_id = l_rcv_sob_id;
SELECT to_number(org_information3)
INTO l_from_ou
FROM hr_organization_information
WHERE org_information_context = 'Accounting Information'
AND organization_id = l_from_org;
SELECT to_number(org_information3)
INTO l_to_ou
FROM hr_organization_information
WHERE org_information_context = 'Accounting Information'
AND organization_id = l_to_org;
SELECT MMT.trx_source_line_id, OEH.header_id, OEH.sold_to_org_id, OEH.order_type_id
INTO l_line_id, l_header_id, l_cust_id, l_order_type
FROM mtl_material_transactions MMT, oe_order_headers_all OEH, oe_order_lines_all OEL
WHERE MMT.transaction_id = i_ae_txn_rec.transaction_id
AND OEL.line_id = MMT.trx_source_line_id
AND OEL.header_id = OEH.header_id;
SELECT MMT.shipment_number, RT.requisition_line_id,
-- For action id 22, transfer_transaction_id points to receiving txn, not the shipping txn,
-- because it got created after goods are received, so goto else part...
decode(l_pd_txfr_ind, 1, 0, nvl(MMT.transfer_transaction_id, 0))
INTO l_ship_num, l_req_line, l_txf_txn_id
FROM mtl_material_transactions MMT, rcv_transactions RT
WHERE MMT.transaction_id = i_ae_txn_rec.transaction_id
AND RT.transaction_id = MMT.rcv_transaction_id;
SELECT MMT.trx_source_line_id, OEH.header_id, OEH.sold_to_org_id, OEH.order_type_id
INTO l_line_id, l_header_id, l_cust_id, l_order_type
FROM mtl_material_transactions MMT, oe_order_headers_all OEH, oe_order_lines_all OEL
WHERE MMT.transaction_id = i_ae_txn_rec.xfer_transaction_id
AND MMT.trx_source_line_id = OEL.line_id
AND OEL.header_id = OEH.header_id;
SELECT MMT.trx_source_line_id, OEH.header_id, OEH.sold_to_org_id, OEH.order_type_id
INTO l_line_id, l_header_id, l_cust_id, l_order_type
FROM mtl_material_transactions MMT, oe_order_headers_all OEH, oe_order_lines_all OEL
WHERE MMT.transaction_action_id = 21
AND MMT.transaction_source_type_id = 8
AND MMT.organization_id = l_from_org
AND MMT.inventory_item_id = i_ae_txn_rec.inventory_item_id
AND MMT.shipment_number = l_ship_num
AND MMT.trx_source_line_id = OEL.line_id
AND OEL.source_document_line_id = l_req_line
AND OEL.header_id = OEH.header_id
AND ROWNUM = 1;
SELECT nvl(MSI.cost_of_sales_account, MP.cost_of_sales_account)
INTO l_cogs_ccid
FROM mtl_system_items MSI, mtl_parameters MP
WHERE MSI.organization_id = l_from_org
AND MSI.inventory_item_id = i_ae_txn_rec.inventory_item_id
AND MP.organization_id = MSI.organization_id;
SELECT decode(asset_inventory,1,0,1)
INTO l_exp_sub
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = i_ae_txn_rec.xfer_subinventory
AND organization_id = i_ae_txn_rec.xfer_organization_id;
SELECT decode(asset_inventory,1,0,1)
INTO l_exp_sub
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = i_ae_txn_rec.subinventory_code
AND organization_id = i_ae_txn_rec.organization_id;
SELECT nvl(expense_account, -1)
INTO l_mfa_exp_account
FROM mtl_fiscal_cat_accounts
WHERE legal_entity_id = i_ae_txn_rec.legal_entity_id
AND cost_type_id = i_ae_txn_rec.cost_type_id
AND cost_group_id = i_ae_txn_rec.cost_group_id
AND category_id = i_ae_txn_rec.category_id;
SELECT NVL(cost_group_id, -1)
INTO l_txn_cost_group_id
FROM cst_cost_group_assignments
WHERE organization_id = i_ae_txn_rec.organization_id;
SELECT NVL(CPIC.item_cost,0)
INTO l_pwac_cost
FROM CST_PAC_ITEM_COSTS CPIC
WHERE CPIC.INVENTORY_ITEM_ID = i_ae_txn_rec.inventory_item_id
AND CPIC.COST_GROUP_ID = i_ae_txn_rec.cost_group_id
AND CPIC.PAC_PERIOD_ID = i_ae_txn_rec.accounting_period_id;
SELECT distribution_account_id
INTO l_mmt_exp_account
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = i_ae_txn_rec.xfer_transaction_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT MOD(SUM(DECODE(process_enabled_flag,'Y',1,2)), 2)
INTO l_pd_txfr_ind
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = i_ae_txn_rec.xfer_organization_id
OR MP.ORGANIZATION_ID = i_ae_txn_rec.organization_id;
SELECT mip.interorg_receivables_account,
mip.interorg_transfer_cr_account,
nvl(mip.interorg_profit_account, -1)
INTO l_io_rcv_acct,
l_io_txfr_cr_acct,
l_io_txfr_var_acct
FROM mtl_interorg_parameters mip,
mtl_material_transactions mmt
WHERE mip.from_organization_id = i_ae_txn_rec.organization_id
AND mip.to_organization_id = i_ae_txn_rec.xfer_organization_id
AND mmt.transaction_id = i_ae_txn_rec.transaction_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT sum(nvl(actual_cost,0))
INTO l_mpacd_cost
FROM mtl_pac_actual_cost_details
WHERE transaction_id = i_ae_txn_rec.transaction_id
AND pac_period_id = i_ae_txn_rec.accounting_period_id
AND cost_group_id = i_ae_txn_rec.cost_group_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT mip.interorg_payables_account,
distribution_account_id
INTO l_io_pay_acct,
l_mmt_exp_account
FROM mtl_interorg_parameters mip,
mtl_material_transactions mmt
WHERE mip.from_organization_id = i_ae_txn_rec.xfer_organization_id
AND mip.to_organization_id = i_ae_txn_rec.organization_id
AND mmt.transaction_id = i_ae_txn_rec.transaction_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT mip.interorg_receivables_account,
nvl(mip.interorg_profit_account, -1),
nvl(mmt.transfer_price,0)
INTO l_io_rcv_acct,
l_io_txfr_var_acct,
l_transfer_price -- transfer price
FROM mtl_interorg_parameters mip,mtl_material_transactions mmt
WHERE mip.from_organization_id = i_ae_txn_rec.organization_id
AND mip.to_organization_id = i_ae_txn_rec.xfer_organization_id
AND mmt.transaction_id = i_ae_txn_rec.transaction_id;
SELECT NVL(CPIC.item_cost,0)
INTO l_pwac_cost
FROM CST_PAC_ITEM_COSTS CPIC
WHERE CPIC.INVENTORY_ITEM_ID = i_ae_txn_rec.inventory_item_id
AND CPIC.COST_GROUP_ID = i_ae_txn_rec.cost_group_id
AND CPIC.PAC_PERIOD_ID = i_ae_txn_rec.accounting_period_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT mip.interorg_payables_account,
distribution_account_id,
nvl(transfer_price,0)
INTO l_io_pay_acct,
l_mmt_exp_account,
l_transfer_price -- transfer price
FROM mtl_interorg_parameters mip,
mtl_material_transactions mmt
WHERE mip.from_organization_id = i_ae_txn_rec.xfer_organization_id
AND mip.to_organization_id = i_ae_txn_rec.organization_id
AND mmt.transaction_id = i_ae_txn_rec.transaction_id;
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
insert_account (i_ae_txn_rec,
i_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT nvl(transfer_percentage,0),nvl(transfer_cost,0)
INTO l_txfr_percent,l_txfr_cost
FROM mtl_material_transactions
WHERE transaction_id = i_ae_txn_rec.transaction_id;
SELECT nvl(max(cpp.pac_period_id), -1)
INTO l_prev_period_id
FROM cst_pac_periods cpp
WHERE cpp.cost_type_id = i_ae_txn_rec.cost_type_id
AND cpp.legal_entity = i_ae_txn_rec.legal_entity_id
AND cpp.pac_period_id < i_ae_txn_rec.accounting_period_id;
/* The flag selected below indicates if PACP is used or not */
SELECT TRANSFER_COST_FLAG
INTO l_transfer_cost_flag
FROM CST_LE_COST_TYPES
WHERE LEGAL_ENTITY = i_ae_txn_rec.legal_entity_id
AND COST_TYPE_ID = i_ae_txn_rec.cost_type_id;
SELECT NVL(MAX(cost_group_id),-1)
INTO l_txfr_cost_group_id
FROM cst_cost_group_assignments
WHERE organization_id = i_ae_txn_rec.xfer_organization_id;
SELECT legal_entity
INTO l_txfr_legal_entity
FROM cst_cost_groups
WHERE cost_group_id = l_txfr_cost_group_id;
SELECT count(*)
INTO l_same_le_ct
FROM cst_le_cost_types
WHERE legal_entity = l_txfr_legal_entity
AND cost_type_id = i_ae_txn_rec.cost_type_id;
SELECT NVL(CPIC.item_cost,0)
INTO o_pacp_pwac_cost
FROM CST_PAC_ITEM_COSTS CPIC
WHERE CPIC.INVENTORY_ITEM_ID = i_ae_txn_rec.inventory_item_id
AND CPIC.COST_GROUP_ID = l_txfr_cost_group_id
AND CPIC.PAC_PERIOD_ID = i_ae_txn_rec.accounting_period_id;
SELECT NVL(CPIC.item_cost,0)
INTO o_pacp_pwac_cost
FROM CST_PAC_ITEM_COSTS CPIC
WHERE CPIC.INVENTORY_ITEM_ID = i_ae_txn_rec.inventory_item_id
AND CPIC.COST_GROUP_ID = i_ae_txn_rec.cost_group_id
AND CPIC.PAC_PERIOD_ID = i_ae_txn_rec.accounting_period_id;
SELECT nvl(CPIC.item_cost,0)
INTO o_prev_period_pwac_cost
FROM CST_PAC_ITEM_COSTS CPIC
WHERE CPIC.INVENTORY_ITEM_ID = i_ae_txn_rec.inventory_item_id
AND CPIC.COST_GROUP_ID = l_txfr_cost_group_id
AND CPIC.PAC_PERIOD_ID = l_prev_period_id;
SELECT nvl(CPIC.item_cost,0)
INTO o_prev_period_pwac_cost
FROM CST_PAC_ITEM_COSTS CPIC
WHERE CPIC.INVENTORY_ITEM_ID = i_ae_txn_rec.inventory_item_id
AND CPIC.COST_GROUP_ID = i_ae_txn_rec.cost_group_id
AND CPIC.PAC_PERIOD_ID = l_prev_period_id;
SELECT nvl(SUM(ABS(NVL(base_transaction_value, 0))),0)
INTO o_perp_ship_value
FROM mtl_transaction_accounts mta
WHERE mta.transaction_id = i_ae_txn_rec.transaction_id
and mta.organization_id = i_ae_txn_rec.organization_id
and mta.accounting_line_type IN (1,2,14)
and mta.base_transaction_value > 0;
SELECT nvl(SUM(ABS(NVL(base_transaction_value, 0))),0)
INTO o_perp_ship_value
FROM mtl_transaction_accounts mta
WHERE mta.transaction_id = i_ae_txn_rec.transaction_id
and mta.organization_id = i_ae_txn_rec.organization_id
and mta.accounting_line_type IN (1,2,14)
and mta.base_transaction_value < 0;
SELECT um.uom_code,
rl.quantity,
rl.unit_price,
rd.budget_account_id,
nvl(rd.nonrecoverable_tax,0) /* Bug 6033153 */
INTO l_doc_uom_code,
l_doc_line_qty,
l_unit_price,
x_encumbrance_account,
l_non_recoverable_tax /* Bug 6033153 */
FROM po_req_distributions_all rd,
po_requisition_lines_all rl,
mtl_units_of_measure um
WHERE rd.requisition_line_id = p_req_line_id
and rd.requisition_line_id = rl.requisition_line_id
and rl.UNIT_MEAS_LOOKUP_CODE = um.unit_of_measure;
SELECT primary_uom_code
INTO l_primary_uom_code
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id;
SELECT
mmt.trx_source_line_id,
mmt.primary_quantity,
mmt.organization_id,
mmt.inventory_item_id,
mmt.transaction_action_id,
mmt.transaction_source_type_id,
mmt.transaction_type_id,
mmt.rcv_transaction_id,
mmt.transaction_date
INTO
l_trx_source_line_id,
l_primary_qty,
l_organization_id,
l_item_id,
l_txn_action_id,
l_txn_src_type_id,
l_txn_type_id,
l_rcv_txn_id,
l_txn_date
FROM
MTL_MATERIAL_TRANSACTIONS mmt
WHERE
transaction_id = p_transaction_id;
SELECT sum(primary_quantity)
INTO l_total_primary_qty
from mtl_material_transactions
where transaction_action_id = l_txn_action_id
and transaction_source_type_id = l_txn_src_type_id
and transaction_type_id = l_txn_type_id
and trx_source_line_id = l_req_line_id
and organization_id = l_organization_id
and ( transaction_date < l_txn_date or (transaction_date = l_txn_date and transaction_id < p_transaction_id));
SELECT
oel.SOURCE_DOCUMENT_LINE_ID
INTO
l_req_line_id
FROM
OE_ORDER_LINES_ALL oel
WHERE
oel.LINE_ID = l_trx_source_line_id;
SELECT
REQUISITION_LINE_ID
INTO
l_req_line_id
FROM
RCV_TRANSACTIONS
WHERE
TRANSACTION_ID = l_rcv_txn_id;