[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Call the API to insert headers and lines |
|============================================================================*/
PROCEDURE create_acct_lines (
i_legal_entity IN NUMBER,
i_cost_type_id IN NUMBER,
i_cost_group_id IN NUMBER,
i_period_id IN NUMBER,
i_transaction_id IN NUMBER,
i_event_type_id IN VARCHAR2,
i_txn_type_flag IN VARCHAR2, --Bug 4586534
o_err_num OUT NOCOPY NUMBER,
o_err_code OUT NOCOPY VARCHAR2,
o_err_msg OUT NOCOPY VARCHAR2
)IS
l_ae_txn_rec CSTPALTY.CST_AE_TXN_REC_TYPE;
CST_INSERT_ERROR EXCEPTION;
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
count(rt.po_line_id) -- change for the bug 4968702
INTO
l_po_line_count
FROM
po_lines_all pol,
rcv_transactions rt
WHERE
pol.po_line_id = rt.po_line_id AND
rt.transaction_id = i_transaction_id;
SELECT
i_event_type_id,
null,
null,
null,
null,
rt.transaction_type,
rt.transaction_id,
pol.item_id,
i_legal_entity,
i_cost_type_id,
i_cost_group_id,
-- J Changes -------------------------------------------------------------------
DECODE(POLL.MATCHING_BASIS, 'AMOUNT', rt.amount, -- Changed for Complex work procurement
'QUANTITY', rt.primary_quantity),
--------------------------------------------------------------------------------
rt.subinventory,
null,
null,
null,
null,
rt.currency_code,
rt.currency_conversion_type,
nvl(rt.currency_conversion_date,transaction_date),
nvl(rt.currency_conversion_rate,1),
l_ae_txn_rec.txn_type_flag,
i_period_id,
rt.transaction_date,
rt.organization_id,
null,
null,
null,
null,
null,
null,
1, ----- inventory_asset_flag - is not used for this package
null,
nvl(poll.lcm_flag,'N')
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.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.lcm_flag
FROM
rcv_transactions rt,
po_lines_all pol,
po_line_locations_all poll -- Added for Complex work procurement
WHERE
rt.po_line_id = pol.po_line_id
AND rt.transaction_id = i_transaction_id
AND poll.line_location_id = rt.po_line_location_id; -- Added for Complex work procurement
select
displayed_field
into
l_ae_txn_rec.description
from
po_lookup_codes
where lookup_code = l_ae_txn_rec.txn_type AND
lookup_type = 'RCV TRANSACTION TYPE';
SELECT
ACCOUNTING_EVENT_ID,
TRANSACTION_DATE,
UNIT_PRICE,
PRIOR_UNIT_PRICE,
PRIMARY_QUANTITY,
CURRENCY_CODE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE,
CREDIT_ACCOUNT_ID,
ORGANIZATION_ID,
PO_DISTRIBUTION_ID
INTO
l_ae_txn_rec.transaction_id,
l_ae_txn_rec.accounting_date,
l_ae_txn_rec.unit_price,
l_ae_txn_rec.prior_unit_price,
l_ae_txn_rec.primary_quantity,
l_ae_txn_rec.currency_code,
l_ae_txn_rec.currency_conv_date,
l_ae_txn_rec.currency_conv_rate,
l_ae_txn_rec.currency_conv_type,
l_ae_txn_rec.credit_account,
l_ae_txn_rec.organization_id,
l_ae_txn_rec.po_distribution_id
FROM
RCV_ACCOUNTING_EVENTS
WHERE
ACCOUNTING_EVENT_ID = i_transaction_id;
SELECT
count(*)
INTO
l_po_line_count
FROM
po_lines_all pol,
rcv_transactions rt,
rcv_accounting_events rae
WHERE
pol.po_line_id = rt.po_line_id AND
rt.transaction_id = rae.rcv_transaction_id AND
rae.accounting_event_id = i_transaction_id;
SELECT
i_event_type_id,
null,
null,
null,
null,
decode(rae.event_type_id, 9, 'Logical Receive', 10, 'Logical Return to Vendor'),
rae.accounting_event_id,
RAE.INVENTORY_ITEM_ID,
i_legal_entity,
i_cost_type_id,
i_cost_group_id,
-- Service Line Type Changes ------------------------------------------------
-- For Services, Transaction_Value = TRANSACTION_AMOUNT
-- To fit into existing formula, Transaction_Value = Primary_Qty * Unit_Price,
-- we use: Primary_Quantity = TRANSACTION_AMOUNT, UNIT_PRICE = 1 in this case
DECODE (POLL.MATCHING_BASIS, 'AMOUNT', RAE.TRANSACTION_AMOUNT, -- Changed for Complex work procurement
'QUANTITY', RAE.source_doc_quantity),
-----------------------------------------------------------------------------
NULL, --subinventory code Verify if reqd especially for drop ship scenarios!!
null,
null,
null,
null,
rae.currency_code,
rae.currency_conversion_type,
nvl(rae.currency_conversion_date, rae.transaction_date),
nvl(rae.currency_conversion_rate,1),
l_ae_txn_rec.txn_type_flag,
i_period_id,
rae.transaction_date,
rae.organization_id,
null,
null,
null,
null,
null,
null,
1, ----- inventory_asset_flag - is not used for this package
null
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.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
FROM
rcv_accounting_events rae,
po_lines_all pol,
po_line_locations_all poll -- Added for Complex work procurement
WHERE
pol.po_line_id = RAE.PO_LINE_ID AND
poll.po_line_id = pol.po_line_id AND
poll.line_location_id = rae.po_line_location_id AND-- Added for Complex work procurement
rae.accounting_event_id = i_transaction_id;
SELECT
ACCOUNTING_EVENT_ID,
TRANSACTION_DATE,
UNIT_PRICE,
PRIOR_UNIT_PRICE,
PRIMARY_QUANTITY,
CURRENCY_CODE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE,
CREDIT_ACCOUNT_ID,
DEBIT_ACCOUNT_ID,
ORGANIZATION_ID,
PO_DISTRIBUTION_ID
INTO
l_ae_txn_rec.transaction_id,
l_ae_txn_rec.accounting_date,
l_ae_txn_rec.unit_price,
l_ae_txn_rec.prior_unit_price,
l_ae_txn_rec.primary_quantity,
l_ae_txn_rec.currency_code,
l_ae_txn_rec.currency_conv_date,
l_ae_txn_rec.currency_conv_rate,
l_ae_txn_rec.currency_conv_type,
l_ae_txn_rec.credit_account,
l_ae_txn_rec.debit_account,
l_ae_txn_rec.organization_id,
l_ae_txn_rec.po_distribution_id
FROM
RCV_ACCOUNTING_EVENTS
WHERE
ACCOUNTING_EVENT_ID = i_transaction_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
period_name,
period_end_date
INTO
l_ae_txn_rec.accounting_period_name,
l_period_end_date
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
set_of_books_id
INTO
l_ae_txn_rec.set_of_books_id
FROM
cst_le_cost_types clct
WHERE
clct.legal_entity = l_ae_txn_rec.legal_entity_id AND
clct.cost_type_id = l_ae_txn_rec.cost_type_id;
CSTPALPC.insert_ae_lines(
l_ae_txn_rec,
l_ae_line_rec_tbl,
l_ae_err_rec);
RAISE CST_INSERT_ERROR;
WHEN CST_INSERT_ERROR THEN
o_err_num := 30004;
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 rae.debit_account_id, rae.credit_account_id,
rae.po_distribution_id,
DECODE(POLL.MATCHING_BASIS, 'AMOUNT', 1, -- Changed for Complex work procurement
'QUANTITY', rae.unit_price)
into l_debit_acct_id, l_credit_acct_id,
l_po_dist_id,
l_unit_price
from rcv_accounting_events rae,
PO_LINE_LOCATIONS_ALL POLL -- Changed for Complex work procurement
where rae.accounting_event_id = i_ae_txn_rec.transaction_id
and RAE.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID; -- Changed for Complex work procurement
CSTPAPPR.insert_account (i_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
CSTPAPPR.insert_account (i_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_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
rt6.transaction_id
INTO
l_par_rcv_txn
FROM
rcv_transactions rt6
WHERE
rt6.transaction_type in (l_receive,l_match)
START WITH
rt6.transaction_id = i_ae_txn_rec.transaction_id
CONNECT BY
rt6.transaction_id = prior rt6.parent_transaction_id;
SELECT
count(rcv_transaction_id) -- change for the bug 4968702
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(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
rt1.transaction_id,
rt1.transaction_type
INTO
l_par_txn,
l_par_txn_type
FROM
rcv_transactions rt1
WHERE
rt1.transaction_id = (select rt2.parent_transaction_id
FROM
rcv_transactions rt2
WHERE
rt2.transaction_id = i_ae_txn_rec.transaction_id);
SELECT
decode(rt.po_distribution_id, NULL, 'S', 'D'),
nvl(rt.po_distribution_id, rt.po_line_location_id)
INTO
l_doc_level,
l_doc_id
FROM
rcv_transactions rt
WHERE
rt.transaction_id = i_ae_txn_rec.transaction_id;
SELECT
count(*)
into
l_dist_count
FROM
po_distributions_all
WHERE
-- begin fix for perf bug 2581067
(
(l_doc_level = 'D' AND po_distribution_id = l_doc_id)
OR (l_doc_level = 'S' AND line_location_id = l_doc_id)
)
AND rownum <= 1;
SELECT
decode (poll.match_option,'R',i_ae_txn_rec.currency_conv_rate,nvl(pod.rate,decode(l_acq_cost_ent,0,1,l_acq_cost/l_acq_cost_ent))) "EXCHG_RATE",
decode (poll.match_option,'R',i_ae_txn_rec.currency_conv_date,pod.rate_date) "EXCHG_DATE",
-- J Changes -------------------------------------------------------------------------
DECODE(POLL.MATCHING_BASIS, 'AMOUNT', 1, -- Changed for Complex work procurement
'QUANTITY', 0 ) "SERVICE_FLAG",
--------------------------------------------------------------------------------------
POD.po_distribution_id "PO_DISTRIBUTION_ID",
POLL.line_location_id "PO_LINE_LOCATION_ID",
POD.code_combination_id "EXPENSE_ACCOUNT_ID",
POD.destination_type_code "DESTINATION_TYPE_CODE",
decode(l_dropship_type_code, 2, RP.clearing_account_id, RP.receiving_account_id) "RECEIVING_ACCOUNT_ID", -- FP Bug 5845861 fix: pickup the clearing account for DS with old accounting
POD.accrual_account_id "ACCRUAL_ACCOUNT_ID",
nvl(POD.budget_account_id,-1) "ENCUMBRANCE_ACCOUNT_ID",
decode(l_doc_level,'D', 1, DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work procurement
'AMOUNT', POD.AMOUNT_ORDERED/POLL.AMOUNT,
'QUANTITY',POD.QUANTITY_ORDERED/POLL.QUANTITY))
* i_ae_txn_rec.primary_quantity "DIST_QUANTITY",
-- J Changes ----------------------------------------------------------------------------
(po_tax_sv.get_tax('PO',pod.po_distribution_id) /
DECODE(POLL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_ORDERED, -- Changed for Complex work procurement
'QUANTITY',POD.QUANTITY_ORDERED) ) "TAX"
-----------------------------------------------------------------------------------------
FROM
po_distributions_all pod,
po_line_locations_all poll,
-- J Changes ----------------------------------------------------------------------------
PO_LINES_ALL POL,
-----------------------------------------------------------------------------------------
rcv_parameters rp
WHERE
-- begin fix for perf bug 2581067
(
(l_doc_level = 'D' AND pod.po_distribution_id = l_doc_id)
OR (l_doc_level = 'S' AND poll.line_location_id = l_doc_id)
)
-- end fix for perf bug 2581067
and pod.line_location_id = poll.line_location_id
-- J Changes ----------------------------------------------------------------------------
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
-----------------------------------------------------------------------------------------
and rp.organization_id = pod.destination_organization_id
and pod.destination_type_code in ('INVENTORY', 'SHOP FLOOR')
and nvl(POLL.accrue_on_receipt_flag,'N') = 'Y'
/* and nvl(POD.accrue_on_receipt_flag,'N') = 'Y' */;
SELECT nvl(max(LANDED_COST_ABS_ACCOUNT),-1)
INTO l_landed_cost_abs_account
FROM CST_ORG_COST_GROUP_ACCOUNTS coga
WHERE coga.legal_entity_id = i_ae_txn_rec.legal_entity_id
AND coga.cost_type_id = i_ae_txn_rec.cost_type_id
AND coga.cost_group_id = i_ae_txn_rec.cost_group_id;
SELECT
-- J Changes ----------------------------------------------------------------------------
DECODE(C_RECEIPTS_REC.SERVICE_FLAG, 1, 1,
(poll.price_override * rt.source_doc_quantity / rt.primary_quantity))
-----------------------------------------------------------------------------------------
INTO
l_po_price
FROM
rcv_transactions rt,
po_line_locations_all poll
WHERE
rt.transaction_id = i_ae_txn_rec.transaction_id
AND rt.po_line_location_id = poll.line_location_id;
SELECT decode(c2.minimum_accountable_unit,
NULL,round(l_ae_line_rec.transaction_value,c2.precision),
round(l_ae_line_rec.transaction_value/c2.minimum_accountable_unit)
* c2.minimum_accountable_unit )
INTO l_ae_line_rec.transaction_value
FROM fnd_currencies c2
WHERE c2.currency_code = decode(l_curr_rec.alt_currency, NULL,
l_curr_rec.pri_currency,
l_curr_rec.alt_currency);
CSTPAPPR.insert_account (i_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT nvl(max(craca.period_id),-1)
INTO l_lcm_adj_period
FROM cst_rcv_acq_costs_adj craca
WHERE craca.rcv_transaction_id = l_par_rcv_txn
AND craca.cost_type_id = i_ae_txn_rec.cost_type_id
AND craca.cost_group_id = i_ae_txn_rec.cost_group_id;
SELECT craca.acquisition_cost
INTO l_acq_cost
FROM cst_rcv_acq_costs_adj craca
WHERE craca.rcv_transaction_id = l_par_rcv_txn
AND craca.cost_type_id = i_ae_txn_rec.cost_type_id
AND craca.cost_group_id = i_ae_txn_rec.cost_group_id
AND craca.period_id = l_lcm_adj_period;
SELECT decode(c2.minimum_accountable_unit,
NULL,round(l_ae_line_rec.transaction_value,c2.precision),
round(l_ae_line_rec.transaction_value/c2.minimum_accountable_unit)
* c2.minimum_accountable_unit )
INTO l_ae_line_rec.transaction_value
FROM fnd_currencies c2
WHERE c2.currency_code = decode(l_curr_rec.alt_currency, NULL,
l_curr_rec.pri_currency,
l_curr_rec.alt_currency);
CSTPAPPR.insert_account (i_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT decode(c2.minimum_accountable_unit,
NULL,round(l_ae_line_rec.transaction_value,c2.precision),
round(l_ae_line_rec.transaction_value/c2.minimum_accountable_unit)
* c2.minimum_accountable_unit )
INTO l_ae_line_rec.transaction_value
FROM fnd_currencies c2
WHERE c2.currency_code = decode(l_curr_rec.alt_currency, NULL,
l_curr_rec.pri_currency,
l_curr_rec.alt_currency);
CSTPAPPR.insert_account (i_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
CSTPAPPR.insert_account (i_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
select credit_account_id, debit_account_id
into l_credit_Account, l_debit_account
from rcv_accounting_events
where rcv_transaction_id = i_ae_txn_rec.transaction_id
and organization_id = i_ae_txn_rec.organization_id
and event_type_id = 1
and rownum = 1;
select org_id
into l_po_ou_id /* Get the OU where PO is created */
from po_headers_all
where po_header_id = (select po_header_id
from rcv_transactions
where transaction_id = i_ae_txn_rec.transaction_id);
select to_number(org_information3)
into l_rcv_ou_id /* Get OU where Receiving is done */
from hr_organization_information
where org_information_context = 'Accounting Information'
and organization_id = i_ae_txn_rec.organization_id;
CSTPAPPR.insert_account (i_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT
cracd.amount/nvl(aia.exchange_rate,1) "AMOUNT",
nvl(aia.exchange_rate,1) "EXCHANGE_RATE",
aia.exchange_date "EXCHANGE_DATE",
aia.exchange_rate_type "EXCHANGE_RATE_TYPE",
aida.dist_code_combination_id "CODE_COMBINATION_ID"
FROM
cst_rcv_acq_cost_details cracd,
cst_rcv_acq_costs crac,
ap_invoice_distributions_all aida,
ap_invoices_all aia
WHERE
cracd.line_type NOT IN ('ITEM','ACCRUAL','IPV','ERV','NONREC_TAX') AND
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 AND
cracd.header_id = crac.header_id AND
cracd.source_type = 'INVOICE' AND
cracd.invoice_distribution_id = aida.invoice_distribution_id AND
aia.invoice_id = aida.invoice_id AND
aia.org_id = aida.org_id AND
NOT EXISTS (
SELECT '1' FROM ap_invoice_distributions_all aida2
WHERE aida2.related_id = aida.invoice_distribution_id
AND aida2.line_type_lookup_code = 'IPV'
);
CSTPAPPR.insert_account (i_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT
aida.base_amount/nvl(aia.exchange_rate,1) "INVOICE_PRICE_VARIANCE",
nvl(aia.exchange_rate,1) "EXCHANGE_RATE",
aia.exchange_date "EXCHANGE_DATE",
aia.exchange_rate_type "EXCHANGE_RATE_TYPE",
aida.dist_code_combination_id "CODE_COMBINATION_ID",
-- Retroactive Pricing Enhancements
-- Need Invoice Distribution to find correction invoices
aida.related_id "INVOICE_DISTRIBUTION_ID"
FROM
cst_rcv_acq_cost_details cracd,
cst_rcv_acq_costs crac,
ap_invoice_distributions_all aida,
ap_invoices_all aia
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 AND
cracd.header_id = crac.header_id AND
cracd.source_type = 'INVOICE' AND
cracd.invoice_distribution_id = aida.related_id AND
aia.invoice_id = aida.invoice_id AND
aia.org_id = aida.org_id AND
aida.line_type_lookup_code = 'IPV' AND
aida.amount <> 0 AND
aida.posted_flag = 'Y'; --Added for bug 4773085
SELECT
NVL(AIDA.BASE_AMOUNT/NVL(AP_INV.EXCHANGE_RATE,1), 0)
INTO
l_correct_ipv_amount
FROM
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICES_ALL AP_INV
WHERE
AIDA.CORRECTED_INVOICE_DIST_ID = c_ipv_rec.INVOICE_DISTRIBUTION_ID
AND AIDA.LINE_TYPE_LOOKUP_CODE = 'IPV'
AND AIDA.INVOICE_ID = AP_INV.INVOICE_ID
AND AP_INV.INVOICE_TYPE_LOOKUP_CODE = 'PO PRICE ADJUST';
CSTPAPPR.insert_account (i_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT
aida.amount "EXCHANGE_RATE_VARIANCE",
aida.dist_code_combination_id "CODE_COMBINATION_ID"
FROM
cst_rcv_acq_cost_details cracd,
cst_rcv_acq_costs crac,
ap_invoice_distributions_all aida
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 AND
cracd.header_id = crac.header_id AND
cracd.source_type = 'INVOICE' AND
cracd.invoice_distribution_id = aida.related_id AND
aida.line_type_lookup_code = 'ERV' AND
aida.amount <> 0 AND
aida.posted_flag = 'Y';-- Added for bug 4773085
CSTPAPPR.insert_account (i_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT
decode (poll.match_option,'R',i_ae_txn_rec.currency_conv_rate,nvl(pod.rate,1)) "EXCHG_RATE",
decode (poll.match_option,'R',i_ae_txn_rec.currency_conv_date,pod.rate_date) "EXCHG_DATE",
-- J Changes --------------------------------------------------------------------------------------
DECODE (POLL.MATCHING_BASIS, 'AMOUNT', 1, -- Changed for Complex work procurement
'QUANTITY', 0)"SERVICE_FLAG",
---------------------------------------------------------------------------------------------------
POD.po_distribution_id "PO_DISTRIBUTION_ID",
POLL.line_location_id "PO_LINE_LOCATION_ID",
POD.code_combination_id "EXPENSE_ACCOUNT_ID",
POD.destination_type_code "DESTINATION_TYPE_CODE",
RP.receiving_account_id "RECEIVING_ACCOUNT_ID",
POD.accrual_account_id "ACCRUAL_ACCOUNT_ID",
nvl(POD.budget_account_id,-1) "ENCUMBRANCE_ACCOUNT_ID",
-- J Changes --------------------------------------------------------------------------------------
decode(l_doc_level,'D', 1, DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work procurement
'AMOUNT', POD.AMOUNT_ORDERED/POLL.AMOUNT,
'QUANTITY',pod.quantity_ordered/poll.quantity))
* i_ae_txn_rec.primary_quantity "DIST_QUANTITY",
po_tax_sv.get_tax('PO',pod.po_distribution_id) /
DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work procurement
'AMOUNT', POD.AMOUNT_ORDERED,
'QUANTITY',POD.QUANTITY_ORDERED) "TAX"
---------------------------------------------------------------------------------------------------
FROM
po_distributions_all pod,
po_line_locations_all poll,
-- J Changes --------------------------------------------------------------------------------------
PO_LINES_ALL POL,
---------------------------------------------------------------------------------------------------
rcv_parameters rp
WHERE
-- begin fix for perf bug 2581067
(
(l_doc_level = 'D' AND pod.po_distribution_id = l_doc_id)
OR (l_doc_level = 'S' AND poll.line_location_id = l_doc_id)
)
-- end fix for perf bug 2581067.
and pod.line_location_id = poll.line_location_id
-- J Changes --------------------------------------------------------------------------------------
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
---------------------------------------------------------------------------------------------------
and rp.organization_id = pod.destination_organization_id
and pod.destination_type_code in ('EXPENSE')
and nvl(POLL.accrue_on_receipt_flag,'N') = 'Y'
/* and nvl(POD.accrue_on_receipt_flag,'N') = 'Y' */;
SELECT
DECODE(C_RECEIPTS_REC.SERVICE_FLAG, 1, 1,
poll.price_override * rt.source_doc_quantity / rt.primary_quantity)
INTO
l_po_price
FROM
rcv_transactions rt,
po_line_locations_all poll
WHERE
rt.transaction_id = i_ae_txn_rec.transaction_id
AND rt.po_line_location_id = poll.line_location_id;
CSTPAPPR.insert_account (i_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
CSTPAPPR.insert_account (i_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
CSTPAPPR.insert_account (i_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
CSTPAPPR.insert_account (i_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT
decode (l_net_receipt,
1,
least(
abs(rt.source_doc_quantity),
greatest(POD.quantity_ordered-l_quantity_delivered,0)
),
-1,
greatest(
(least(POD.quantity_ordered-l_quantity_delivered,0) + abs(rt.source_doc_quantity)),
0
),
0
) * rt.primary_quantity/rt.source_doc_quantity * l_po_price,
nvl(POD.rate, 1),
pod.rate_date
INTO
l_encum_amount,
l_po_rate,
l_po_rate_date
FROM
po_headers_all POH,
po_lines_all POL,
po_line_locations_all POLL,
po_distributions_all POD,
rcv_transactions RT
WHERE
POH.po_header_id = POD.po_header_id AND
POL.po_line_id = POD.po_line_id AND
POLL.line_location_id = POD.line_location_id AND
POD.po_distribution_id = c_receipts_rec.po_distribution_id AND
nvl(POLL.accrue_on_receipt_flag,'N') = 'Y' AND
/*nvl(POD.accrue_on_receipt_flag,'N') = 'Y' AND */
RT.transaction_id = i_ae_txn_rec.transaction_id AND
POD.destination_type_code = 'EXPENSE';
SELECT
decode (l_net_receipt,
1,
least(
abs(RT.AMOUNT),
greatest(POD.AMOUNT_ORDERED - L_QUANTITY_DELIVERED, 0)
),
-1,
greatest(
(least(POD.AMOUNT_ORDERED - L_QUANTITY_DELIVERED, 0) + abs(rt.AMOUNT)),
0
),
0
) * l_po_price,
nvl(POD.rate, 1),
pod.rate_date
INTO
l_encum_amount,
l_po_rate,
l_po_rate_date
FROM
po_headers_all POH,
po_lines_all POL,
po_line_locations_all POLL,
po_distributions_all POD,
rcv_transactions RT
WHERE
POH.po_header_id = POD.po_header_id
AND POL.po_line_id = POD.po_line_id
AND POLL.line_location_id = POD.line_location_id
AND POD.po_distribution_id = c_receipts_rec.po_distribution_id
AND nvl(POLL.accrue_on_receipt_flag,'N') = 'Y'
/*AND nvl(POD.accrue_on_receipt_flag,'N') = 'Y' */
AND RT.transaction_id = i_ae_txn_rec.transaction_id
AND POD.destination_type_code = 'EXPENSE';
CSTPAPPR.insert_account (i_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
select
currency_code
into
l_curr_rec.pri_currency
from
gl_sets_of_books
where
set_of_books_id = p_ae_txn_rec.set_of_books_id;
SELECT
nvl(RETRO_PRICE_ADJ_ACCOUNT, -1)
INTO
l_debit_account
FROM
CST_ORG_COST_GROUP_ACCOUNTS
WHERE
LEGAL_ENTITY_ID = P_AE_TXN_REC.LEGAL_ENTITY_ID
AND COST_TYPE_ID = P_AE_TXN_REC.COST_TYPE_ID
AND COST_GROUP_ID = P_AE_TXN_REC.COST_GROUP_ID;
select
decode(l_curr_rec.alt_currency,NULL, NULL,
l_curr_rec.pri_currency, NULL,
decode(c2.minimum_accountable_unit,
NULL,
round(l_current_transaction_value, c2.precision),
round(l_current_transaction_value /c2.minimum_accountable_unit)
* c2.minimum_accountable_unit )),
decode(c1.minimum_accountable_unit,
NULL, round(l_current_transaction_value * l_curr_rec.currency_conv_rate, c1.precision),
round(l_current_transaction_value * l_curr_rec.currency_conv_rate/c1.minimum_accountable_unit)
* c1.minimum_accountable_unit ),
decode(l_curr_rec.alt_currency,NULL, NULL,
l_curr_rec.pri_currency, NULL,
decode(c2.minimum_accountable_unit,
NULL,
round(l_prior_transaction_value, c2.precision),
round(l_prior_transaction_value /c2.minimum_accountable_unit)
* c2.minimum_accountable_unit )),
decode(c1.minimum_accountable_unit,
NULL, round(l_prior_transaction_value * l_curr_rec.currency_conv_rate, c1.precision),
round(l_prior_transaction_value * l_curr_rec.currency_conv_rate/c1.minimum_accountable_unit)
* c1.minimum_accountable_unit )
into
l_current_entered_value,
l_current_accounted_value,
l_prior_entered_value,
l_prior_accounted_value
from
fnd_currencies c1,
fnd_currencies c2
where
c1.currency_code = l_curr_rec.pri_currency
and c2.currency_code = decode(l_curr_rec.alt_currency, NULL,
l_curr_rec.pri_currency,
l_curr_rec.alt_currency);
CSTPAPPR.insert_account (p_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
CSTPAPPR.insert_account (p_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
select
currency_code
into
l_curr_rec.pri_currency
from
gl_sets_of_books
where
set_of_books_id = p_ae_txn_rec.set_of_books_id;
CSTPAPPR.insert_account (p_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
CSTPAPPR.insert_account (p_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_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
decode(rt.po_distribution_id, NULL, 'S', 'D'),
nvl(rt.po_distribution_id, rt.po_line_location_id)
INTO
l_doc_level,
l_doc_id
FROM
rcv_transactions rt
WHERE
rt.transaction_id = i_ae_txn_rec.transaction_id;
SELECT
count(*)
into
l_dist_count
FROM
po_distributions_all
WHERE
(
(l_doc_level = 'D' AND po_distribution_id = l_doc_id)
OR (l_doc_level = 'S' AND line_location_id = l_doc_id)
)
AND rownum <= 1;
SELECT
decode (poll.match_option,'R',i_ae_txn_rec.currency_conv_rate,nvl(pod.rate,1)) "EXCHG_RATE",
decode (poll.match_option,'R',i_ae_txn_rec.currency_conv_date,pod.rate_date) "EXCHG_DATE",
DECODE (poll.matching_basis, 'AMOUNT', 1, 0) "SERVICE_FLAG",
POD.po_distribution_id "PO_DISTRIBUTION_ID",
nvl(POD.rate,1) "PO_RATE",
pod.rate_date "PO_DATE",
POLL.line_location_id "PO_LINE_LOCATION_ID",
POD.code_combination_id "EXPENSE_ACCOUNT_ID",
POD.destination_type_code "DESTINATION_TYPE_CODE",
RP.receiving_account_id "RECEIVING_ACCOUNT_ID",
POD.accrual_account_id "ACCRUAL_ACCOUNT_ID",
nvl(POD.budget_account_id,-1) "ENCUMBRANCE_ACCOUNT_ID",
decode(poll.matching_basis,
'AMOUNT', poll.amount - NVL(poll.amount_cancelled,0),
poll.quantity - NVL(poll.quantity_cancelled,0)) "SHIPMENT_QUANTITY",
decode(poll.matching_basis,
'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
pod.quantity_ordered - NVL(pod.quantity_cancelled, 0)) "DIST_QUANTITY",
decode(poll.matching_basis,
'AMOUNT', 1,
NVL(poll.price_override, pol.unit_price)) +
(po_tax_sv.get_tax( 'PO', pod.po_distribution_id) /
decode(poll.matching_basis,
'AMOUNT', pod.amount_ordered,
pod.quantity_ordered)) "UNIT_PRICE",
NVL(poll.match_option,'P') "MATCH_OPTION"
FROM
po_distributions_all pod,
po_line_locations_all poll,
po_lines_all pol,
rcv_parameters rp
WHERE
(
(l_doc_level = 'D' AND pod.po_distribution_id = l_doc_id)
OR (l_doc_level = 'S' AND poll.line_location_id = l_doc_id)
)
and pod.line_location_id = poll.line_location_id
and poll.po_line_id = pol.po_line_id
and rp.organization_id = pod.destination_organization_id
and pod.destination_type_code = 'EXPENSE'
and nvl(POLL.accrue_on_receipt_flag,'N') = 'N'
and nvl(POD.accrue_on_receipt_flag,'N') = 'N';
CSTPAPPR.insert_account (i_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
CSTPAPPR.insert_account (i_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
CSTPAPPR.insert_account (i_ae_txn_rec,
l_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT
decode(status,'I',101,201)
INTO
l_application_id
FROM
fnd_product_installations
WHERE
application_id = 101;
SELECT
NVL(org_id,-1)
into
l_operating_unit
FROM
po_headers_all
WHERE
po_header_id = (select po_header_id from rcv_transactions
where transaction_id = i_transaction_id);
SELECT
SOB.currency_code,
nvl(FSP.purch_encumbrance_flag, 'N'),
nvl(FSP.purch_encumbrance_type_id, 0)
INTO
l_functional_currency_code,
o_purch_encumbrance_flag,
o_purch_encumbrance_type_id
FROM
GL_PERIOD_STATUSES ACR,
GL_PERIOD_TYPES GLPT,
FINANCIALS_SYSTEM_PARAMS_ALL FSP,
GL_SETS_OF_BOOKS SOB
WHERE
GLPT.period_type = ACR.period_type AND
ACR.application_id = l_application_id AND
ACR.set_of_books_id = i_set_of_books_id AND
ACR.period_name = i_period_name AND
FSP.set_of_books_id = SOB.set_of_books_id AND
NVL(FSP.org_id,-1) = l_operating_unit;
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_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)
IS
l_err_rec CSTPALTY.cst_ae_err_rec_type;
fnd_file.put_line(fnd_file.log,'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;
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,1,
decode(i_ae_curr_rec.currency_conv_rate,-1,1,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, c2.precision),
round(i_ae_line_rec.transaction_value /c2.minimum_accountable_unit)
* c2.minimum_accountable_unit )),
decode(c1.minimum_accountable_unit,
NULL, round(i_ae_line_rec.transaction_value * i_ae_curr_rec.currency_conv_rate, c1.precision),
round(i_ae_line_rec.transaction_value * i_ae_curr_rec.currency_conv_rate/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);
fnd_file.put_line(fnd_file.log,'Insert_Account >>> ');
o_ae_err_rec.l_err_msg := 'CSTPAPPR.insert_account' || to_char(l_stmt_num) ||
substr(SQLERRM,1,180);
end insert_account;
SELECT
rt4.transaction_id,
rt4.transaction_type,
-- J Changes -------------------------------------------------------------
-- Bug 3588765 --
-- DECODE(POL.ORDER_TYPE_LOOKUP_CODE,
-- 'RATE', rt4.AMOUNT,
-- 'FIXED PRICE', rt4.AMOUNT,
-- RT4.PRIMARY_QUANTITY) "PRIMARY_QUANTITY",
-- End of Bug 3588765
----------------------------------------------------------------------------
rt4.parent_transaction_id
FROM
rcv_transactions rt4
-- J Changes -------------------------------------------------------------
-- Bug 3588765 --
-- PO_LINES_ALL POL
-- End of Bug 3588765
--------------------------------------------------------------------------
WHERE
rt4.transaction_id < i_transaction_id
-- J Changes -------------------------------------------------------------
-- Bug 3588765 --
AND EXISTS (SELECT 1 FROM PO_LINES_ALL POL WHERE RT4.PO_LINE_ID= POL.PO_LINE_ID)
-- AND RT4.PO_LINE_ID = POL.PO_LINE_ID
-- End of Bug 3588765
--------------------------------------------------------------------------
START WITH
rt4.po_distribution_id = i_po_distribution_id
and transaction_type = 'DELIVER'
CONNECT BY
prior rt4.transaction_id = rt4.parent_transaction_id
AND rt4.po_line_location_id = PRIOR rt4.po_line_location_id; -- Change for the bug 4968702
SELECT DECODE(POLL1.MATCHING_BASIS, -- Changed for Complex work procurement
'AMOUNT', rt6.AMOUNT,
'QUANTITY', RT6.PRIMARY_QUANTITY) "PRIMARY_QUANTITY"
INTO l_primary_quantity
FROM rcv_transactions rt6,
PO_LINE_LOCATIONS_ALL POLL1 -- Changed for Complex work procurement
WHERE rt6.transaction_id=c_nqd_rec.transaction_id
AND RT6.PO_LINE_LOCATION_ID= POLL1.LINE_LOCATION_ID; -- Changed for Complex work procurement
SELECT
rt5.transaction_type
INTO
l_parent_type
FROM
rcv_transactions rt5
WHERE
rt5.transaction_id = c_nqd_rec.parent_transaction_id;