[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* this select has to be executed only when i_source_flag=1,
periodic acquisition cost processor */
CURSOR c_receipts_source_flag_1 (l_start_date IN DATE,
l_end_date IN DATE,
i_receipt_no IN NUMBER,
i_invoice_no IN NUMBER) IS
(SELECT
distinct rt.transaction_id ,
nvl(poll.lcm_flag,'N') lcm_flag
FROM
rcv_transactions rt,
po_line_locations_all poll, -- Added for Complex work Procurement
cst_cost_group_assignments ccga1
WHERE rt.transaction_date BETWEEN i_start_date and i_end_date AND
-- Added for Complex work Procurement
rt.po_line_id = poll.po_line_id AND
rt.po_line_location_id = poll.line_location_id AND
poll.shipment_type <> 'PREPAYMENT' AND
ccga1.cost_group_id = i_cost_group_id AND
rt.organization_id = ccga1.organization_id AND
rt.source_document_code = 'PO' AND
NVL(rt.consigned_flag,'N') = 'N' AND
NVL(rt.dropship_type_code,3) <> 1 AND -- FP bug 5845861 fix
( ( rt.parent_transaction_id = -1 AND
rt.transaction_type = 'RECEIVE'
)
OR
( transaction_type = 'MATCH')
)
);
(Select /*+ OPTIMIZER_FEATURES_ENABLE('9.0.1') */
distinct aida.rcv_transaction_id transaction_id
from ap_invoice_distributions_all aida
WHERE aida.rcv_transaction_id is not null
and i_invoice_no IS NULL
and i_receipt_no is NULL
and exists (select 1 from rcv_transactions rt,
po_line_locations_all poll, -- Added for Complex work Procurement
cst_cost_group_assignments ccga
where rt.transaction_id = aida.rcv_transaction_id
-- Added for Complex work Procurement
and rt.po_line_id = poll.po_line_id
and rt.po_line_location_id = poll.line_location_id
and nvl(poll.lcm_flag,'N') = 'N'
and poll.shipment_type <> 'PREPAYMENT'
and rt.transaction_date < l_start_date
AND ccga.cost_group_id = i_cost_group_id
AND rt.organization_id = ccga.organization_id
AND rt.source_document_code = 'PO'
AND NVL(rt.consigned_flag,'N') = 'N'
AND NVL(rt.dropship_type_code,3) = 3 --dropshipement project
AND ( ( rt.parent_transaction_id = -1
AND rt.transaction_type = 'RECEIVE')
OR
( rt.transaction_type = 'MATCH'))
)
AND NOT EXISTS ( SELECT 1
FROM RCV_TRANSACTIONS RT,
PO_DISTRIBUTIONS_ALL POD
WHERE RT.TRANSACTION_ID = AIDA.RCV_TRANSACTION_ID
AND POD.LINE_LOCATION_ID = RT.PO_LINE_LOCATION_ID
AND POD.DESTINATION_TYPE_CODE = 'EXPENSE'
)
/*bug 5044215/5264793.Only pick up receipts that have delivers */
AND EXISTS (Select 1
from rcv_transactions rt2
where rt2.transaction_type in ('DELIVER')
START WITH rt2.transaction_id = aida.rcv_transaction_id
CONNECT BY
prior rt2.transaction_id = rt2.parent_transaction_id
)
/* Invoice Lines Project
Removing reference to ap_chrg_allocations_all
*/
and EXISTS(
SELECT 1 FROM ap_invoice_distributions_all aida2
WHERE aida.invoice_distribution_id = nvl(aida2.charge_applicable_to_dist_id, aida.invoice_distribution_id)
AND (aida2.accounting_date between l_start_date and l_end_date)
AND aida2.posted_flag = 'Y'
AND aida2.line_type_lookup_code <> 'REC_TAX'
)
and NOT EXISTS (SELECT 1 FROM rcv_transactions rt,rcv_accounting_events rae --add for dropshipment
WHERE rt.transaction_id = aida.rcv_transaction_id
AND rae.rcv_transaction_id = rt.transaction_id
AND rae.event_type_id = 1 -- RECEIVE
AND rae.trx_flow_header_id is not NULL)
UNION
select distinct rcv_transaction_id from ap_invoice_distributions_all aida
where ((aida.accounting_date between l_start_date and l_end_date))
and aida.posted_flag = 'Y'
/* Invoice Lines Project, TAX is now REC_TAX and NONREC_TAX */
and aida.line_type_lookup_code <> 'REC_TAX'
and aida.rcv_transaction_id is NOT NULL
and i_receipt_no IS NULL
and i_invoice_no IS NULL
and exists (select 1 from rcv_transactions rt,
po_line_locations_all poll, -- Added for Complex work Procurement
cst_cost_group_assignments ccga
where rt.transaction_id = aida.rcv_transaction_id
-- Added for Complex work Procurement
and rt.po_line_id = poll.line_location_id
and rt.po_line_location_id = poll.line_location_id
and nvl(poll.lcm_flag,'N') = 'N'
and poll.shipment_type <> 'PREPAYMENT'
and rt.transaction_date < l_start_date
AND ccga.cost_group_id = i_cost_group_id
AND rt.organization_id = ccga.organization_id
AND rt.source_document_code = 'PO'
AND NVL(rt.consigned_flag,'N') = 'N'
AND NVL(rt.dropship_type_code,3) = 3 --dropshipment project
AND ( ( rt.parent_transaction_id = -1
AND rt.transaction_type = 'RECEIVE')
OR
( rt.transaction_type = 'MATCH'))
)
AND NOT EXISTS ( SELECT 1
FROM RCV_TRANSACTIONS RT,
PO_DISTRIBUTIONS_ALL POD
WHERE RT.TRANSACTION_ID = AIDA.RCV_TRANSACTION_ID
AND POD.LINE_LOCATION_ID = RT.PO_LINE_LOCATION_ID
AND POD.DESTINATION_TYPE_CODE = 'EXPENSE' )
/*bug 5044215/5264793.Only pick up receipts that have delivers */
AND EXISTS (Select 1
from rcv_transactions rt2
where rt2.transaction_type in ('DELIVER')
START WITH rt2.transaction_id = aida.rcv_transaction_id
CONNECT BY
prior rt2.transaction_id = rt2.parent_transaction_id
)
and NOT EXISTS (SELECT 1 FROM rcv_transactions rt,rcv_accounting_events rae --add for dropshipment
WHERE rt.transaction_id = aida.rcv_transaction_id
AND rae.rcv_transaction_id = rt.transaction_id
AND rae.event_type_id = 1 -- RECEIVE
AND rae.trx_flow_header_id is not NULL)
)
UNION
( select i_receipt_no from dual
where i_receipt_no IS NOT NULL
/*bug 5044215/5264793.Only pick up receipts that have delivers */
AND EXISTS (Select 1
from rcv_transactions rt2
where rt2.transaction_type in ('DELIVER')
START WITH rt2.transaction_id = i_receipt_no
CONNECT BY
prior rt2.transaction_id = rt2.parent_transaction_id
)
AND EXISTS ( Select 'not lcm enabled'
FROM rcv_transactions rt,
po_line_locations_all poll
WHERE rt.transaction_id = i_receipt_no
AND rt.po_line_location_id = poll.line_location_id
AND nvl(poll.lcm_flag,'N') = 'N'
)
)
UNION
( select distinct rcv_transaction_id from ap_invoice_distributions_all aida
where aida.invoice_id = i_invoice_no
and i_invoice_no IS NOT NULL
and aida.rcv_transaction_id IS NOT NULL
and aida.line_type_lookup_code = 'ITEM'
and NOT EXISTS (SELECT 1 FROM rcv_transactions rt,rcv_accounting_events rae --add for dropshipment
WHERE rt.transaction_id = aida.rcv_transaction_id
AND rae.rcv_transaction_id = rt.transaction_id
AND rae.event_type_id = 1 -- RECEIVE
AND rae.trx_flow_header_id is not NULL)
AND NOT EXISTS ( SELECT 1
FROM rcv_transactions rt,
po_distributions_all pod,
po_line_locations_all poll
WHERE rt.transaction_id = aida.rcv_transaction_id
AND pod.line_location_id = rt.po_line_location_id
AND poll.line_location_id = rt.po_line_location_id
AND nvl(poll.lcm_flag,'N') = 'Y'
AND pod.destination_type_code = 'EXPENSE' )
/*bug 5044215/5264793.Only pick up receipts that have delivers */
AND EXISTS (Select 1
from rcv_transactions rt2
where rt2.transaction_type in ('DELIVER')
START WITH rt2.transaction_id = aida.rcv_transaction_id
CONNECT BY
prior rt2.transaction_id = rt2.parent_transaction_id
)
);
IS SELECT clat.rcv_transaction_id,
clat.inventory_item_id,
clat.organization_id,
rp.receiving_account_id,
nvl(msi.inventory_asset_flag,'N') inventory_asset_flag,
rt.po_header_id,
rt.po_line_location_id line_location_id,
rt.po_line_id,
rt.unit_landed_cost,
msi.primary_uom_code,
(nvl(poll.price_override,0) + get_rcv_tax(rt.transaction_id)) po_unit_price,
decode(nvl(poll.match_option,'P'),
'P',get_po_rate(rt.transaction_id),
'R',rt.currency_conversion_rate) rate,
poll.org_id,
rt.po_release_id,
nvl(rt.po_distribution_id,-1) po_distribution_id,
poll.quantity poll_quantity,
muom.unit_of_measure,
max(clat.transaction_id) transaction_id
FROM cst_lc_adj_transactions clat,
cst_cost_group_assignments ccga1,
mtl_parameters mp,
rcv_transactions rt,
rcv_parameters rp,
mtl_system_items msi,
po_line_locations_all poll,
mtl_units_of_measure muom
WHERE rt.transaction_date < l_start_date
AND clat.transaction_date BETWEEN l_start_date and l_end_date
AND clat.rcv_transaction_id = rt.transaction_id
AND ccga1.cost_group_id = i_cost_group_id
AND rt.organization_id = ccga1.organization_id
AND mp.organization_id = ccga1.organization_id
AND msi.organization_id = clat.organization_id
AND clat.organization_id = rt.organization_id
AND msi.inventory_item_id = clat.inventory_item_id
AND mp.lcm_enabled_flag = 'Y'
AND rp.organization_id = ccga1.organization_id
AND poll.line_location_id = rt.po_line_location_id
AND poll.lcm_flag = 'Y'
AND muom.uom_code = msi.primary_uom_code
GROUP BY clat.rcv_transaction_id,
clat.inventory_item_id,
clat.organization_id,
rp.receiving_account_id,
nvl(msi.inventory_asset_flag,'N'),
rt.po_header_id,
rt.po_line_location_id,
rt.po_line_id,
rt.unit_landed_cost,
msi.primary_uom_code,
(nvl(poll.price_override,0) + get_rcv_tax(rt.transaction_id)),
decode(nvl(poll.match_option,'P'),
'P',get_po_rate(rt.transaction_id),
'R',rt.currency_conversion_rate),
poll.org_id,
rt.po_release_id,
nvl(rt.po_distribution_id,-1),
poll.quantity,
muom.unit_of_measure;
SELECT mmt.subinventory_code,
nvl(mse.asset_inventory,2) asset_inventory,
rt.po_distribution_id,
sum(mmt.primary_quantity) primary_quantity
FROM ( SELECT po_distribution_id,
transaction_id,
organization_id
FROM rcv_transactions
WHERE transaction_type IN ('DELIVER','RETURN TO RECEIVING','CORRECT')
AND transaction_date < p_valuation_date
AND organization_id = p_organization_id
START WITH transaction_id = p_rcv_transaction_id
CONNECT BY parent_transaction_id = PRIOR transaction_id
) rt,
mtl_material_transactions mmt,
mtl_secondary_inventories mse
WHERE rt.transaction_id = mmt.rcv_transaction_id
AND mse.secondary_inventory_name = mmt.subinventory_code
AND mse.organization_id = mmt.organization_id
AND mmt.organization_id = rt.organization_id
GROUP BY mmt.subinventory_code,
nvl(mse.asset_inventory,2),
rt.po_distribution_id;
select legal_entity
into l_legal_entity
from cst_cost_groups
where cost_group_id = i_cost_group_id ;
select NVL(restrict_doc_flag, 2),
set_of_books_id
into l_res_invoices,
l_sob_id
from cst_le_cost_types
where legal_entity = l_legal_entity
and cost_type_id = i_cost_type_id;
select NVL(MAX(pac_period_id), -1)
into l_prev_period_id
from cst_pac_periods
where legal_entity = l_legal_entity
and open_flag = 'N'
and cost_type_id = i_cost_type_id ;
select period_close_date,
i_end_date
into l_start_date,
l_end_date
from cst_pac_periods
where pac_period_id = l_prev_period_id
and legal_entity = l_legal_entity
and cost_type_id = i_cost_type_id;
select period_start_date,
i_end_date
INTO l_start_date,
l_end_date
FROM cst_pac_periods cpp
where cpp.pac_period_id = i_period
and cpp.legal_entity = l_legal_entity
and cpp.cost_type_id = i_cost_type_id;
SELECT count(rcv_transaction_id)
INTO l_rec_ct
FROM cst_rcv_acq_costs crac
WHERE crac.rcv_transaction_id = c_rec.transaction_id
AND crac.period_id = i_period
AND crac.cost_type_id = i_cost_type_id
AND crac.cost_group_id = i_cost_group_id
AND ROWNUM < 2;
SELECT cst_rcv_acq_costs_s.nextval
INTO l_header
FROM dual;
Select rae.accounting_event_id,
DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work Procurement
'AMOUNT', RAE.TRANSACTION_AMOUNT,
'QUANTITY',rae.unit_price),
INTERCOMPANY_PRICING_OPTION
Into l_accounting_event_id,
l_rae_unit_price,
l_rae_trf_price_flag
From rcv_accounting_events rae,
po_lines_all POL,
po_line_locations_all POLL, -- Added for Complex work Procurement
po_distributions_all POD
Where rae.rcv_transaction_id = c_rec.transaction_id
And rae.event_type_id = 1 -- RECEIVE
And rae.trx_flow_header_id is not null
AND RAE.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
AND POD.PO_LINE_ID = POL.PO_LINE_ID
AND POLL.PO_LINE_ID = POL.PO_LINE_ID; -- Added for Complex work Procurement
SELECT nvl(poll.match_option,'P')
INTO l_match_option
FROM po_line_locations_all poll,
rcv_transactions rt7
WHERE
poll.line_location_id = rt7.po_line_location_id
AND rt7.transaction_id = c_rec.transaction_id;
SELECT count(rt2.transaction_id)
INTO l_po_count
FROM rcv_transactions rt2,
po_lines_all pol1,
po_line_locations_all poll1
WHERE rt2.transaction_id = c_rec.transaction_id
AND rt2.po_line_location_id = poll1.line_location_id
AND pol1.po_line_id = poll1.po_line_id
AND ROWNUM < 2;
SELECT
decode(l_rae_trf_price_flag, 2, l_rae_unit_price, (nvl(poll2.price_override,0) + l_nr_tax_rate)),
rt3.po_line_location_id,
nvl(rt3.CURRENCY_CONVERSION_RATE,1) ,
rsl.item_id,
nvl(poll2.unit_meas_lookup_code,rsl.unit_of_measure),
poll2.quantity,
rt3.organization_id,
nvl(poll2.matching_basis,'QUANTITY') /* Bug4762808 */
INTO
l_po_price,
l_po_line_loc,
l_rate,
l_item_id,
l_po_uom_code,
l_poll_quantity,
l_org_id,
l_order_type_lookup_code
FROM
rcv_transactions rt3,
rcv_shipment_lines rsl,
po_line_locations_all poll2
WHERE
rt3.transaction_id = c_rec.transaction_id
AND rt3.po_line_location_id = poll2.line_location_id
AND rsl.shipment_line_id = rt3.shipment_line_id;
SELECT
-- J Changes ---------------------------------------------------------------
DECODE(POLL3.MATCHING_BASIS,
'AMOUNT', 1 + l_nr_tax_rate,
'QUANTITY',decode(l_rae_trf_price_flag, 2, l_rae_unit_price, (nvl(poll3.price_override,0) + l_nr_tax_rate))),
----------------------------------------------------------------------------
rt33.po_line_location_id,
rt33.unit_of_measure ,
nvl(pol2.item_id,-1),
nvl(poll3.unit_meas_lookup_code,pol2.unit_meas_lookup_code),
poll3.quantity,
rt33.organization_id,
decode(nvl(poll3.match_option,'P'),
'P',get_po_rate(rt33.transaction_id),
'R',rt33.currency_conversion_rate),
nvl(poll3.matching_basis,'QUANTITY') /* Bug4762808 */
INTO
l_po_price,
l_po_line_loc,
l_rec_uom_code,
l_item_id,
l_po_uom_code,
l_poll_quantity,
l_org_id,
l_rate,
l_order_type_lookup_code
FROM
po_lines_all pol2,
po_line_locations_all poll3,
rcv_transactions rt33
WHERE
rt33.transaction_id = c_rec.transaction_id
AND rt33.po_line_location_id = poll3.line_location_id
AND pol2.po_line_id = poll3.po_line_id;
SELECT
mum1.uom_code
INTO
l_po_uom
FROM
mtl_units_of_measure mum1
WHERE
MUM1.UNIT_OF_measure = l_po_uom_code;
SELECT
mum1.uom_code
INTO
l_rec_uom
FROM
mtl_units_of_measure mum1
WHERE
mum1.unit_of_measure = l_rec_uom_code;
SELECT
msi.primary_uom_code
INTO
l_primary_uom
FROM
mtl_system_items msi
WHERE
msi.inventory_item_id = l_item_id AND
msi.organization_id = l_org_id;
Insert_into_acqhdr_tables(
l_header,
i_cost_group_id,
i_cost_type_id,
i_period,
c_rec.transaction_id,
l_nqr, -- in pri uom
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
l_po_line_loc,
l_po_price, -- in po currency based on pri uom
l_primary_uom,
l_rate, -- rate at time of receipt
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_req_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id,
i_source_flag,
l_err_num,
l_err_msg);
Select NVL(restrict_doc_flag,2) into l_res_flag
from CST_LE_COST_TYPES
where legal_entity = l_legal_entity
and cost_type_id = i_cost_type_id;
SELECT count(rcv_transaction_id)
INTO l_inv_count
FROM ap_invoice_distributions_all ad1
WHERE ad1.rcv_transaction_id = c_rec.transaction_id AND
((l_res_flag =1) AND (ad1.accounting_date between i_start_date and i_end_date)) OR (l_res_flag = 2)
AND ad1.posted_flag = 'Y' AND
/* Invoice Lines Project TAX is now REC_TAX and NONREC_TAX */
ad1.line_type_lookup_code <> 'REC_TAX' AND
ROWNUM < 2;
SELECT
ad2.invoice_distribution_id,
ad2.invoice_id,
-- J Changes ------------------------------------------------------------------
nvl(DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work Procurement
'AMOUNT', AD2.AMOUNT,
'QUANTITY',ad2.quantity_invoiced), 0 ) "QUANTITY_INVOICED", -- Invoice UOM
-------------------------------------------------------------------------------
ad2.distribution_line_number,
ad2.line_type_lookup_code,
-- J Changes ------------------------------------------------------------------
nvl(DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work Procurement
'AMOUNT', 1,
'QUANTITY', ad2.unit_price), 0 ) unit_price, -- Invoice Currency
--------------------------------------------------------------------------------
nvl(ad2.base_amount, nvl(ad2.amount, 0)) base_amount
FROM
ap_invoice_distributions_all ad2,
-- J Changes -----------------------------------------------------------
RCV_TRANSACTIONS RT,
PO_LINES_ALL POL,
PO_LINE_LOCATIONS_ALL POLL, -- Added for Complex work Procurement
ap_invoices_all aia /* bug 4352624 Added to ignore invoices of type prepayment */
------------------------------------------------------------------------
WHERE
ad2.rcv_transaction_id = c_rec.transaction_id
AND ad2.posted_flag = 'Y'
/* bug 4352624 Added to ignore invoices of type prepayment */
AND ad2.line_type_lookup_code <>'PREPAY'
AND aia.invoice_id = ad2.invoice_id
AND aia.invoice_type_lookup_code <>'PREPAYMENT'
-- J Changes -----------------------------------------------------------
AND RT.TRANSACTION_ID = AD2.RCV_TRANSACTION_ID
AND POL.PO_LINE_ID = RT.PO_LINE_ID
AND RT.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND POLL.PO_LINE_ID = POL.PO_LINE_ID ---- Added for Complex work Procurement
------------------------------------------------------------------------
AND ( ( l_res_flag =1 ) AND ( ad2.accounting_date between i_start_date and i_end_date) )
OR (l_res_flag = 2)
/* Invoice Lines Project TAX is now REC_TAX AND NONREC_TAX */
AND ad2.line_type_lookup_code <> 'REC_TAX'
-- J Changes -------------------------------------------------------------
-- Ensure that Price corrections are not picked --
/* Invoice Lines Project root_distribution_id ->
corrected_invoice_dist_id */
AND ad2.corrected_invoice_dist_id is null;
SELECT AIDA.INVOICE_ID, /*Bug3891984*/
AIDA.INVOICE_DISTRIBUTION_ID, -- Only for debugging
NVL(AIDA.BASE_AMOUNT, NVL(AIDA.AMOUNT, 0))
INTO l_corr_invoice_id,
l_corr_inv,
l_correction_amount
FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICES_ALL AP_INV
/* Invoice Lines Project
No root_distribution_id or xinv_parent_reversal_id
now it'll just be represented by corrected_invoice_dist_id
*/
WHERE AIDA.CORRECTED_INVOICE_DIST_ID = c_inv.INVOICE_DISTRIBUTION_ID
AND AIDA.INVOICE_ID = AP_INV.INVOICE_ID
AND AP_INV.INVOICE_TYPE_LOOKUP_CODE = 'PO PRICE ADJUST';
SELECT NVL(AIDA.BASE_AMOUNT, NVL(AIDA.AMOUNT, 0))
INTO l_correction_tax_amount
FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE AIDA.INVOICE_ID = l_corr_invoice_id
/* Invoice Lines Project non-recoverable tax now is just NONREC_TAX */
AND AIDA.LINE_TYPE_LOOKUP_CODE = 'NONREC_TAX';
So we should prevent insertion into the details table from the c_reciepts cursor as it will be
inserted into the details table later from the chrg_allocations cursor */
l_chrg_present := 0;
Select count(*) into l_chrg_present
from ap_invoice_distributions_all
where invoice_distribution_id = c_inv.invoice_distribution_id
and charge_applicable_to_dist_id is not null;
select cst_rcv_acq_cost_details_s.nextval into l_details_nextvalue
from dual;
select decode(l_pri_quantity_invoiced,0,0,(c_inv.unit_price * c_inv.quantity_invoiced / l_pri_quantity_invoiced)) into l_priuom_cost
from dual;
Insert_into_acqdtls_tables (
l_header,
l_details_nextvalue,
'INVOICE',
NULL,
c_inv.invoice_distribution_id,
1,
0,
c_inv.invoice_distribution_id,
NULL,
NULL,
NULL,
c_inv.base_amount + l_correction_amount, -- in func currency
l_pri_quantity_invoiced, -- in pri uom
l_priuom_cost, -- convert to price based on pri uom
c_inv.line_type_lookup_code,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_req_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id,
i_source_flag,
l_err_num,
l_err_msg);
SELECT count(rcv_transaction_id)
INTO l_rec_ct
FROM cst_rcv_acq_costs crac
WHERE crac.rcv_transaction_id = c_rec.transaction_id
AND crac.period_id = i_period
AND crac.cost_type_id = i_cost_type_id
AND crac.cost_group_id = i_cost_group_id
AND ROWNUM < 2;
SELECT unit_landed_cost
INTO l_rct_landed_cost
FROM rcv_transactions
WHERE transaction_id = c_rec.transaction_id;
SELECT nvl(Max(new_landed_cost),-1)
INTO l_rct_adj_landed_cost
FROM
(
SELECT new_landed_cost,transaction_id,
max(transaction_id) OVER ( PARTITION BY transaction_date)
max_transaction_id
FROM
(SELECT new_landed_cost,transaction_id,transaction_date,
max(transaction_date) OVER (PARTITION BY rcv_transaction_id)
max_transaction_date
FROM cst_lc_adj_transactions
WHERE rcv_transaction_id = c_rec.transaction_id
AND transaction_date BETWEEN l_start_date
AND l_end_date)
WHERE transaction_date = max_transaction_date
)
WHERE transaction_id = max_transaction_id;
SELECT cst_rcv_acq_costs_s.nextval
INTO l_header_id
FROM dual;
SELECT
(nvl(poll3.price_override,0) + l_nr_tax_rate),
rt33.po_line_location_id,
nvl(pol2.item_id,-1),
nvl(poll3.unit_meas_lookup_code,
pol2.unit_meas_lookup_code),
rt33.organization_id,
decode(nvl(poll3.match_option,'P'),
'P',get_po_rate(rt33.transaction_id),
'R',rt33.currency_conversion_rate)
INTO
l_po_price,
l_po_line_loc,
l_item_id,
l_po_uom_code,
l_org_id,
l_rate
FROM
po_lines_all pol2,
po_line_locations_all poll3,
rcv_transactions rt33
WHERE rt33.transaction_id = c_rec.transaction_id
AND rt33.po_line_location_id = poll3.line_location_id
AND pol2.po_line_id = poll3.po_line_id;
SELECT mum1.uom_code
INTO l_po_uom
FROM mtl_units_of_measure mum1
WHERE MUM1.UNIT_OF_measure = l_po_uom_code;
SELECT msi.primary_uom_code
INTO l_primary_uom
FROM mtl_system_items msi
WHERE msi.inventory_item_id = l_item_id
AND msi.organization_id = l_org_id;
Insert_into_acqhdr_tables(
i_header_id => l_header_id,
i_cost_group_id => i_cost_group_id,
i_cost_type_id => i_cost_type_id,
i_period_id => i_period,
i_rcv_transaction_id => c_rec.transaction_id,
i_net_quantity_received => l_net_qty_rec,
i_total_quantity_invoiced => NULL,
i_quantity_at_po_price => 0,
i_total_invoice_amount => NULL,
i_amount_at_po_price => 0,
i_total_amount => l_net_qty_rec*l_lcm_acq_cost,
i_costed_quantity => l_net_qty_rec,
i_acquisition_cost => l_lcm_acq_cost,
i_po_line_location_id => l_po_line_loc,
i_po_unit_price => l_po_price,
i_primary_uom => l_primary_uom,
i_rec_exchg_rate => l_rate,
i_last_update_date => SYSDATE,
i_last_updated_by => i_user_id,
i_creation_date => SYSDATE,
i_created_by => i_user_id,
i_request_id => i_req_id,
i_program_application_id => i_prog_appl_id,
i_program_id => i_prog_id,
i_program_update_date => SYSDATE,
i_last_update_login => i_login_id,
i_source_flag => i_source_flag,
o_err_num => l_err_num,
o_err_msg => l_err_msg );
INSERT INTO cst_rcv_acq_cost_details (
HEADER_ID,
DETAIL_ID,
SOURCE_TYPE,
PO_LINE_LOCATION_ID,
PARENT_DISTRIBUTION_ID,
DISTRIBUTION_NUM,
LEVEL_NUM,
INVOICE_DISTRIBUTION_ID,
PARENT_INVOICE_DIST_ID,
ALLOCATED_AMOUNT,
PARENT_AMOUNT,
AMOUNT,
QUANTITY,
PRICE,
LINE_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES (
l_header_id,
cst_rcv_acq_cost_details_s.nextval,
'LCM',
l_po_line_loc,
NULL,
-1,
0,
NULL,
NULL,
NULL,
NULL,
l_net_qty_rec*l_lcm_acq_cost,
l_net_qty_rec,
l_lcm_acq_cost,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_req_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id);
SELECT nvl(max(LANDED_COST_VAR_ACCOUNT),-1),
nvl(max(LANDED_COST_ABS_ACCOUNT),-1)
INTO l_lcm_var_acct_id,
l_lcm_abs_acct_id
FROM CST_ORG_COST_GROUP_ACCOUNTS
WHERE legal_entity_id = l_legal_entity
AND cost_type_id = i_cost_type_id
AND cost_group_id = i_cost_group_id;
SELECT mia.control_level,
ccg.organization_id
INTO l_uom_control,l_master_org_id
FROM mtl_item_attributes mia,
cst_cost_groups ccg
WHERE mia.attribute_name = 'MTL_SYSTEM_ITEMS.PRIMARY_UNIT_OF_MEASURE'
AND ccg.cost_group_id = i_cost_group_id;
Delete from mtl_pac_txn_cost_details mptcd
where mptcd.transaction_id IN ( SELECT mmt.transaction_id
FROM mtl_material_transactions mmt,
cst_rcv_acq_costs_adj craca
WHERE mmt.rcv_transaction_id
= c_rec.rcv_transaction_id
AND mmt.transaction_source_id
= craca.header_id
AND craca.rcv_transaction_id
= c_rec.rcv_transaction_id
AND craca.cost_group_id
= i_cost_group_id
AND craca.period_id = i_period
AND craca.cost_type_id
= i_cost_type_id
AND mmt.transaction_action_id = 24
AND mmt.transaction_type_id = 26
AND mmt.transaction_source_type_id = 14);
Delete from mtl_material_transactions mmt
where mmt.rcv_transaction_id = c_rec.rcv_transaction_id
AND mmt.transaction_action_id = 24
AND mmt.transaction_type_id = 26
AND mmt.transaction_source_type_id = 14
AND mmt.transaction_source_id IN ( select craca.header_id
from cst_rcv_acq_costs_adj craca
where craca.period_id = i_period
and craca.cost_group_id = i_cost_group_id
and craca.rcv_transaction_id = c_rec.rcv_transaction_id
and craca.cost_type_id = i_cost_type_id );
Delete from rcv_accounting_events rae
WHERE rae.event_type_id IN (18,19,20)
AND rae.rcv_transaction_id = c_rec.rcv_transaction_id
AND rae.event_source_id IN ( select header_id from cst_rcv_acq_costs_adj craca
where craca.rcv_transaction_id = c_rec.rcv_transaction_id
and cost_group_id = i_cost_group_id
and period_id = i_period
and cost_type_id = i_cost_type_id
);
Delete from cst_rcv_acq_cost_details_adj cracda
where cracda.header_id = (select header_id from cst_rcv_acq_costs_adj craca
where craca.rcv_transaction_id = c_rec.rcv_transaction_id
and cost_group_id = i_cost_group_id
and period_id = i_period
and cost_type_id = i_cost_type_id);
Delete from cst_rcv_acq_costs_adj crac
where crac.rcv_transaction_id = c_rec.rcv_transaction_id
and cost_group_id = i_cost_group_id
and period_id = i_period
and cost_type_id = i_cost_type_id;
/*SELECT new_landed_cost
INTO l_new_landed_cost
FROM cst_lc_adj_transactions
WHERE transaction_id = c_rec.transaction_id;*/
SELECT Max(new_landed_cost)
INTO l_new_landed_cost
FROM
(
SELECT new_landed_cost,transaction_id,
max(transaction_id) OVER ( PARTITION BY transaction_date)
max_transaction_id
FROM
(SELECT new_landed_cost,transaction_id,transaction_date,
max(transaction_date) OVER (PARTITION BY rcv_transaction_id)
max_transaction_date
FROM cst_lc_adj_transactions
WHERE rcv_transaction_id = c_rec.rcv_transaction_id
AND organization_id = c_rec.organization_id
AND transaction_date BETWEEN l_start_date
AND l_end_date)
WHERE transaction_date = max_transaction_date
)
WHERE transaction_id = max_transaction_id;
/*SELECT nvl(max(period_id),-1)
INTO l_prior_period
FROM cst_rcv_acq_costs_adj
WHERE rcv_transaction_id = c_rec.rcv_transaction_id
AND cost_group_id = i_cost_group_id
AND cost_type_id = i_cost_type_id;
SELECT craca.acquisition_cost
INTO l_prior_landed_cost
FROM cst_rcv_acq_costs_adj craca
WHERE craca.rcv_transaction_id = c_rec.rcv_transaction_id
AND craca.cost_type_id = i_cost_type_id
AND craca.cost_group_id = i_cost_group_id
AND craca.period_id = l_prior_period;
select nvl(max(acquisition_cost),-1)
into l_prior_landed_cost
from (
select craca.acquisition_cost,
craca.period_id,
max(craca.period_id) OVER (PARTITION BY craca.rcv_transaction_id)
max_period_id
from cst_rcv_acq_costs_adj craca
WHERE craca.rcv_transaction_id = c_rec.rcv_transaction_id
AND craca.cost_type_id = i_cost_type_id
AND craca.cost_group_id = i_cost_group_id)
where period_id = max_period_id;
SELECT nvl(max(crac3.acquisition_cost),-1)
INTO l_prior_landed_cost
FROM cst_rcv_acq_costs crac3
WHERE crac3.cost_type_id = i_cost_type_id
AND crac3.cost_group_id = i_cost_group_id
AND crac3.rcv_transaction_id = c_rec.rcv_transaction_id;
/* SELECT nvl(max(clat1.new_landed_cost),-1)
INTO l_prior_landed_cost
FROM cst_lc_adj_transactions clat1
WHERE clat1.rcv_transaction_id = c_rec.rcv_transaction_id
AND clat1.transaction_date < l_start_date
AND clat1.transaction_id = ( SELECT max(clat2.transaction_id)
FROM cst_lc_adj_transactions clat2
WHERE clat2.rcv_transaction_id =
c_rec.rcv_transaction_id
AND clat2.transaction_date <
l_start_date
);*/
SELECT nvl(Max(new_landed_cost),-1)
INTO l_prior_landed_cost
FROM
(
SELECT new_landed_cost,transaction_id,
max(transaction_id) OVER ( PARTITION BY transaction_date)
max_transaction_id
FROM
(SELECT new_landed_cost,transaction_id,transaction_date,
max(transaction_date) OVER (PARTITION BY rcv_transaction_id)
max_transaction_date
FROM cst_lc_adj_transactions
WHERE rcv_transaction_id = c_rec.rcv_transaction_id
AND transaction_date < l_start_date
)
WHERE transaction_date = max_transaction_date
)
WHERE transaction_id = max_transaction_id;
SELECT cst_rcv_acq_costs_s.nextval
INTO l_header_id
FROM dual;
Insert_into_acqhdr_tables(
i_header_id => l_header_id,
i_cost_group_id => i_cost_group_id,
i_cost_type_id => i_cost_type_id,
i_period_id => i_period,
i_rcv_transaction_id => c_rec.rcv_transaction_id,
i_net_quantity_received => l_tot_qty_received,
i_total_quantity_invoiced => NULL,
i_quantity_at_po_price => 0,
i_total_invoice_amount => NULL,
i_amount_at_po_price => 0,
i_total_amount => l_tot_qty_received*l_new_landed_cost,
i_costed_quantity => l_tot_qty_received,
i_acquisition_cost => l_new_landed_cost,
i_po_line_location_id => c_rec.line_location_id,
i_po_unit_price => c_rec.po_unit_price,
i_primary_uom => c_rec.primary_uom_code,
i_rec_exchg_rate => c_rec.rate,
i_last_update_date => SYSDATE,
i_last_updated_by => i_user_id,
i_creation_date => SYSDATE,
i_created_by => i_user_id,
i_request_id => i_req_id,
i_program_application_id => i_prog_appl_id,
i_program_id => i_prog_id,
i_program_update_date => SYSDATE,
i_last_update_login => i_login_id,
i_source_flag => 2,
o_err_num => l_err_num,
o_err_msg => l_err_msg );
INSERT INTO cst_rcv_acq_cost_details_adj (
HEADER_ID,
DETAIL_ID,
SOURCE_TYPE,
PO_LINE_LOCATION_ID,
PARENT_DISTRIBUTION_ID,
DISTRIBUTION_NUM,
LEVEL_NUM,
INVOICE_DISTRIBUTION_ID,
PARENT_INVOICE_DIST_ID,
ALLOCATED_AMOUNT,
PARENT_AMOUNT,
AMOUNT,
QUANTITY,
PRICE,
LINE_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES (
l_header_id,
cst_rcv_acq_cost_details_s.nextval,
'LCM',
c_rec.line_location_id,
NULL,
-1,
0,
NULL,
NULL,
NULL,
NULL,
l_tot_qty_received*l_new_landed_cost,
l_tot_qty_received,
l_new_landed_cost,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_req_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id);
/* Insert PAC LCM ADJUST RECEIVE INTO RAE */
IF (c_rec.po_distribution_id <> -1) THEN
INSERT into RCV_ACCOUNTING_EVENTS(
accounting_event_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_udpate_date,
rcv_transaction_id,
event_type_id,
event_source,
event_source_id,
set_of_books_id,
org_id,
organization_id,
debit_account_id,
credit_account_id,
transaction_date,
source_doc_quantity,
transaction_quantity,
primary_quantity,
source_doc_unit_of_measure,
transaction_unit_of_measure,
primary_unit_of_measure,
po_header_id,
po_release_id,
po_line_id,
po_line_location_id,
po_distribution_id,
inventory_item_id,
unit_price,
prior_unit_price,
currency_conversion_rate)
(SELECT
rcv_accounting_event_s.NEXTVAL,
sysdate,
i_user_id,
i_login_id,
sysdate,
i_user_id,
i_req_id,
i_prog_appl_id,
i_prog_id,
sysdate,
c_rec.rcv_transaction_id,
18,
'PAC_LCM_ADJ_REC' ,
l_header_id,
l_sob_id,
c_rec.org_id,
c_rec.organization_id,
decode(sign(l_tot_qty_received*
(l_new_landed_cost-l_prior_landed_cost)),-1,
l_lcm_abs_acct_id,
c_rec.receiving_account_id),
decode(sign(l_tot_qty_received*
(l_new_landed_cost-l_prior_landed_cost)),-1,
c_rec.receiving_account_id,
l_lcm_abs_acct_id),
l_avcu_txn_date,
l_tot_qty_received ,
l_tot_qty_received ,
l_tot_qty_received ,
c_rec.unit_of_measure,
c_rec.unit_of_measure,
c_rec.unit_of_measure,
c_rec.po_header_id,
c_rec.po_release_id,
c_rec.po_line_id,
c_rec.line_location_id,
c_rec.po_distribution_id,
c_rec.inventory_item_id,
l_new_landed_cost unit_price,
l_prior_landed_cost,
1
FROM DUAL);
INSERT into RCV_ACCOUNTING_EVENTS(
accounting_event_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_udpate_date,
rcv_transaction_id,
event_type_id,
event_source,
event_source_id,
set_of_books_id,
org_id,
organization_id,
debit_account_id,
credit_account_id,
transaction_date,
source_doc_quantity,
transaction_quantity,
primary_quantity,
source_doc_unit_of_measure,
transaction_unit_of_measure,
primary_unit_of_measure,
po_header_id,
po_release_id,
po_line_id,
po_line_location_id,
po_distribution_id,
inventory_item_id,
unit_price,
prior_unit_price,
currency_conversion_rate)
(SELECT
rcv_accounting_event_s.NEXTVAL,
sysdate,
i_user_id,
i_login_id,
sysdate,
i_user_id,
i_req_id,
i_prog_appl_id,
i_prog_id,
sysdate,
c_rec.rcv_transaction_id,
18,
'PAC_LCM_ADJ_REC' ,
l_header_id,
l_sob_id,
c_rec.org_id,
c_rec.organization_id,
decode(sign(l_tot_qty_received*
(l_new_landed_cost-l_prior_landed_cost)),-1,
l_lcm_abs_acct_id,
c_rec.receiving_account_id),
decode(sign(l_tot_qty_received*
(l_new_landed_cost-l_prior_landed_cost)),-1,
c_rec.receiving_account_id,
l_lcm_abs_acct_id),
l_avcu_txn_date,
l_tot_qty_received*POD.quantity_ordered/c_rec.poll_quantity source_doc_quantity,
l_tot_qty_received*POD.quantity_ordered/c_rec.poll_quantity transaction_quantity,
l_tot_qty_received*POD.quantity_ordered/c_rec.poll_quantity primary_quantity,
c_rec.unit_of_measure,
c_rec.unit_of_measure,
c_rec.unit_of_measure,
c_rec.po_header_id,
c_rec.po_release_id,
c_rec.po_line_id,
c_rec.line_location_id,
pod.po_distribution_id,
c_rec.inventory_item_id,
l_new_landed_cost unit_price,
l_prior_landed_cost,
1
FROM po_distributions_all pod
WHERE pod.line_location_id = c_rec.line_location_id);
/* NOW INSERT THE RAE FOR DELIVERY */
FOR C_REC2 IN c_lcm_del(c_rec.rcv_transaction_id,
l_start_date,
c_rec.organization_id ) LOOP
IF (C_REC2.asset_inventory = 1
AND C_REC.inventory_asset_flag ='Y' ) THEN
l_stmt_num := 1250;
INSERT into RCV_ACCOUNTING_EVENTS(
accounting_event_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_udpate_date,
rcv_transaction_id,
event_type_id,
event_source,
event_source_id,
set_of_books_id,
org_id,
organization_id,
debit_account_id,
credit_account_id,
transaction_date,
source_doc_quantity,
transaction_quantity,
primary_quantity,
source_doc_unit_of_measure,
transaction_unit_of_measure,
primary_unit_of_measure,
po_header_id,
po_release_id,
po_line_id,
po_line_location_id,
po_distribution_id,
inventory_item_id,
unit_price,
prior_unit_price,
currency_conversion_rate)
VALUES
(
rcv_accounting_event_s.NEXTVAL,
sysdate,
i_user_id,
i_login_id,
sysdate,
i_user_id,
i_req_id,
i_prog_appl_id,
i_prog_id,
sysdate,
c_rec.rcv_transaction_id,
19,
'PAC_LCM_ADJ_DEL_ASSET' ,
l_header_id,
l_sob_id,
c_rec.org_id,
c_rec.organization_id,
decode(sign(c_rec2.primary_quantity*
(l_new_landed_cost-l_prior_landed_cost)),-1,
c_rec.receiving_account_id,
l_lcm_abs_acct_id),
decode(sign(c_rec2.primary_quantity*
(l_new_landed_cost-l_prior_landed_cost)),-1,
l_lcm_abs_acct_id,
c_rec.receiving_account_id),
l_avcu_txn_date,
c_rec2.primary_quantity ,
c_rec2.primary_quantity ,
c_rec2.primary_quantity ,
c_rec.unit_of_measure,
c_rec.unit_of_measure,
c_rec.unit_of_measure,
c_rec.po_header_id,
c_rec.po_release_id,
c_rec.po_line_id,
c_rec.line_location_id,
c_rec2.po_distribution_id,
c_rec.inventory_item_id,
l_new_landed_cost ,
l_prior_landed_cost,
1
)
Returning accounting_event_id INTO l_rcv_accounting_event_id;
select MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
into l_transaction_id
from dual;
SELECT msi.primary_uom_code
INTO l_master_uom_code
FROM mtl_system_items msi
WHERE msi.organization_id = l_master_org_id
AND msi.inventory_item_id = c_rec.inventory_item_id;
INSERT INTO MTL_MATERIAL_TRANSACTIONS
(transaction_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
inventory_item_id,
organization_id,
transaction_type_id,
transaction_action_id,
transaction_source_type_id,
transaction_quantity,
transaction_uom,
primary_quantity,
transaction_date,
value_change,
material_account,
material_overhead_account,
resource_account,
outside_processing_account,
overhead_account,
costed_flag,
org_cost_group_id,
cost_type_id,
source_code,
source_line_id,
expense_account_id,
rcv_transaction_id,
transaction_source_id,
subinventory_code)
VALUES (
l_transaction_id,
sysdate,
i_user_id,
sysdate,
i_user_id,
c_rec.inventory_item_id,
l_master_org_id,
26,
24,
14,
c_rec2.primary_quantity* l_um_rate,
l_master_uom_code,
c_rec2.primary_quantity* l_um_rate,
l_avcu_txn_date,
(c_rec2.primary_quantity*
(l_new_landed_cost-l_prior_landed_cost)),
l_lcm_abs_acct_id,
l_lcm_abs_acct_id,
l_lcm_abs_acct_id,
l_lcm_abs_acct_id,
l_lcm_abs_acct_id,
NULL,
i_cost_group_id,
i_cost_type_id,
'PACLCMADJ',
l_rcv_accounting_event_id,
l_lcm_var_acct_id,
c_rec.rcv_transaction_id,
l_header_id,
c_rec2.subinventory_code
);
/* insert into MPTCD */
l_stmt_num := 1280;
Insert into mtl_pac_txn_cost_details
(cost_group_id,
transaction_id,
pac_period_id,
cost_type_id,
cost_element_id,
level_type,
inventory_item_id,
value_change,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by )
Values (i_cost_group_id,
l_transaction_id,
i_period,
i_cost_type_id,
1, -- cost element ID
1, -- THis level
c_rec.inventory_item_id,
(c_rec2.primary_quantity*
(l_new_landed_cost-l_prior_landed_cost)),
l_prior_landed_cost/l_um_rate,
sysdate,
i_user_id,
sysdate,
i_user_id);
SELECT nvl(expense_account, -1)
INTO l_exp_account_id
FROM mtl_fiscal_cat_accounts
WHERE legal_entity_id = l_legal_entity
AND cost_type_id = i_cost_type_id
AND cost_group_id = i_cost_group_id
AND category_id = (SELECT mic.category_id
FROM mtl_item_categories mic
WHERE mic.inventory_item_id =
c_rec.inventory_item_id
AND mic.organization_id =
c_rec.organization_id
AND mic.category_set_id =
(SELECT category_set_id
FROM mtl_default_category_sets
WHERE functional_area_id = 5)
);
INSERT into RCV_ACCOUNTING_EVENTS(
accounting_event_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_udpate_date,
rcv_transaction_id,
event_type_id,
event_source,
event_source_id,
set_of_books_id,
org_id,
organization_id,
debit_account_id,
credit_account_id,
transaction_date,
source_doc_quantity,
transaction_quantity,
primary_quantity,
source_doc_unit_of_measure,
transaction_unit_of_measure,
primary_unit_of_measure,
po_header_id,
po_release_id,
po_line_id,
po_line_location_id,
po_distribution_id,
inventory_item_id,
unit_price,
prior_unit_price,
currency_conversion_rate)
( SELECT
rcv_accounting_event_s.NEXTVAL,
sysdate,
i_user_id,
i_login_id,
sysdate,
i_user_id,
i_req_id,
i_prog_appl_id,
i_prog_id,
sysdate,
c_rec.rcv_transaction_id,
20,
'PAC_LCM_ADJ_DEL_EXP' ,
l_header_id,
l_sob_id,
c_rec.org_id,
c_rec.organization_id,
decode(sign(c_rec2.primary_quantity*
(l_new_landed_cost-l_prior_landed_cost)),-1,
c_rec.receiving_account_id,
l_exp_account_id),
decode(sign(c_rec2.primary_quantity*
(l_new_landed_cost-l_prior_landed_cost)),-1,
l_exp_account_id,
c_rec.receiving_account_id),
l_avcu_txn_date,
c_rec2.primary_quantity source_doc_quantity,
c_rec2.primary_quantity transaction_quantity,
c_rec2.primary_quantity primary_quantity,
c_rec.unit_of_measure,
c_rec.unit_of_measure,
c_rec.unit_of_measure,
c_rec.po_header_id,
c_rec.po_release_id,
c_rec.po_line_id,
c_rec.line_location_id,
c_rec2.po_distribution_id,
c_rec.inventory_item_id,
l_new_landed_cost unit_price,
l_prior_landed_cost,
1
FROM DUAL);
/* Update the primary_quantity of the MMT with total adjusted QTY */
l_stmt_num := 1330;
UPDATE mtl_material_transactions mmt
SET (primary_quantity,
transaction_quantity)
= ( SELECT sum(mmt2.primary_quantity),
sum(mmt2.transaction_quantity)
FROM mtl_material_transactions mmt2
WHERE mmt2.inventory_item_id =
mmt.inventory_item_id
AND mmt2.transaction_action_id = 24
AND mmt2.transaction_type_id = 26
AND mmt2.transaction_source_type_id = 14
AND mmt2.transaction_date = l_avcu_txn_date
AND mmt2.source_code = 'PACLCMADJ'
AND mmt2.org_cost_group_id = i_cost_group_id
AND mmt2.cost_type_id = i_cost_type_id
AND mmt2.organization_id = l_master_org_id
)
WHERE mmt.transaction_action_id = 24
AND mmt.transaction_type_id = 26
AND mmt.transaction_source_type_id = 14
AND mmt.transaction_date = l_avcu_txn_date
AND mmt.source_code = 'PACLCMADJ'
AND mmt.org_cost_group_id = i_cost_group_id
AND mmt.cost_type_id = i_cost_type_id
AND mmt.organization_id = l_master_org_id;
Delete from mtl_pac_txn_cost_details mptcd
where mptcd.transaction_id IN ( select craca.mmt_transaction_id
from cst_rcv_acq_costs_adj craca
where craca.mmt_transaction_id is not null
and craca.period_id = i_period
and craca.cost_group_id = i_cost_group_id
and craca.rcv_transaction_id = c_rec.transaction_id
and craca.cost_type_id = i_cost_type_id );
Delete from mtl_material_transactions mmt
where mmt.transaction_id IN ( select craca.mmt_transaction_id
from cst_rcv_acq_costs_adj craca
where craca.mmt_transaction_id is not null
and craca.period_id = i_period
and craca.cost_group_id = i_cost_group_id
and craca.rcv_transaction_id = c_rec.transaction_id
and craca.cost_type_id = i_cost_type_id );
Delete from cst_rcv_acq_cost_details_adj cracda
where cracda.header_id = (select header_id from cst_rcv_acq_costs_adj craca
where craca.rcv_transaction_id = c_rec.transaction_id
and cost_group_id = i_cost_group_id
and period_id = i_period
and cost_type_id = i_cost_type_id);
Delete from cst_rcv_acq_costs_adj crac
where crac.rcv_transaction_id = c_rec.transaction_id
and cost_group_id = i_cost_group_id
and period_id = i_period
and cost_type_id = i_cost_type_id;
SELECT cst_rcv_acq_costs_s.nextval
INTO l_header
FROM dual;
Select rae.accounting_event_id,
DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work Procurement
'AMOUNT', RAE.TRANSACTION_AMOUNT,
'QUANTITY',rae.unit_price),
INTERCOMPANY_PRICING_OPTION
Into l_accounting_event_id,
l_rae_unit_price,
l_rae_trf_price_flag
From rcv_accounting_events rae,
po_lines_all POL,
po_line_locations_all POLL, -- Added for Complex work Procurement
po_distributions_all POD
Where rae.rcv_transaction_id = c_rec.transaction_id
And rae.event_type_id = 1 -- RECEIVE
And rae.trx_flow_header_id is not null
AND RAE.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
AND POD.PO_LINE_ID = POL.PO_LINE_ID
AND POLL.PO_LINE_ID = POL.PO_LINE_ID; -- Added for Complex work Procurement
SELECT nvl(poll.match_option,'P')
INTO l_match_option
FROM po_line_locations_all poll,
rcv_transactions rt7
WHERE
poll.line_location_id = rt7.po_line_location_id
AND rt7.transaction_id = c_rec.transaction_id;
SELECT count(rt2.transaction_id)
INTO l_po_count
FROM rcv_transactions rt2,
po_lines_all pol1,
po_line_locations_all poll1
WHERE rt2.transaction_id = c_rec.transaction_id
AND rt2.po_line_location_id = poll1.line_location_id
AND pol1.po_line_id = poll1.po_line_id
AND ROWNUM < 2;
SELECT
decode(l_rae_trf_price_flag, 2, l_rae_unit_price, (nvl(poll2.price_override,0) + l_nr_tax_rate)),
rt3.po_line_location_id,
nvl(rt3.CURRENCY_CONVERSION_RATE,1) ,
rsl.item_id,
nvl(poll2.unit_meas_lookup_code,rsl.unit_of_measure),
poll2.quantity,
rt3.organization_id,
nvl(poll2.matching_basis,'QUANTITY') /* Bug4762808 */
INTO
l_po_price,
l_po_line_loc,
l_rate,
l_item_id,
l_po_uom_code,
l_poll_quantity,
l_org_id,
l_order_type_lookup_code
FROM
rcv_transactions rt3,
rcv_shipment_lines rsl,
po_line_locations_all poll2
WHERE
rt3.transaction_id = c_rec.transaction_id
AND rt3.po_line_location_id = poll2.line_location_id
AND rsl.shipment_line_id = rt3.shipment_line_id;
SELECT
-- J Changes ---------------------------------------------------------------
DECODE(POLL3.MATCHING_BASIS,
'AMOUNT', 1 + l_nr_tax_rate,
'QUANTITY',decode(l_rae_trf_price_flag, 2, l_rae_unit_price, (nvl(poll3.price_override,0) + l_nr_tax_rate))),
----------------------------------------------------------------------------
rt33.po_line_location_id,
rt33.unit_of_measure ,
nvl(pol2.item_id,-1),
nvl(poll3.unit_meas_lookup_code,pol2.unit_meas_lookup_code),
poll3.quantity,
rt33.organization_id,
decode(nvl(poll3.match_option,'P'),
'P',get_po_rate(rt33.transaction_id),
'R',rt33.currency_conversion_rate),
nvl(poll3.matching_basis,'QUANTITY') /* Bug4762808 */
INTO
l_po_price,
l_po_line_loc,
l_rec_uom_code,
l_item_id,
l_po_uom_code,
l_poll_quantity,
l_org_id,
l_rate,
l_order_type_lookup_code
FROM
po_lines_all pol2,
po_line_locations_all poll3,
rcv_transactions rt33
WHERE
rt33.transaction_id = c_rec.transaction_id
AND rt33.po_line_location_id = poll3.line_location_id
AND pol2.po_line_id = poll3.po_line_id;
SELECT
mum1.uom_code
INTO
l_po_uom
FROM
mtl_units_of_measure mum1
WHERE
MUM1.UNIT_OF_measure = l_po_uom_code;
SELECT
mum1.uom_code
INTO
l_rec_uom
FROM
mtl_units_of_measure mum1
WHERE
mum1.unit_of_measure = l_rec_uom_code;
SELECT
msi.primary_uom_code
INTO
l_primary_uom
FROM
mtl_system_items msi
WHERE
msi.inventory_item_id = l_item_id AND
msi.organization_id = l_org_id;
Insert_into_acqhdr_tables(
l_header,
i_cost_group_id,
i_cost_type_id,
i_period,
c_rec.transaction_id,
l_nqr, -- in pri uom
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
l_po_line_loc,
l_po_price, -- in po currency based on pri uom
l_primary_uom,
l_rate, -- rate at time of receipt
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_req_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id,
i_source_flag,
l_err_num,
l_err_msg);
Select NVL(restrict_doc_flag,2) into l_res_flag
from CST_LE_COST_TYPES
where legal_entity = l_legal_entity
and cost_type_id = i_cost_type_id;
SELECT count(rcv_transaction_id)
INTO l_inv_count
FROM ap_invoice_distributions_all ad1
WHERE ad1.rcv_transaction_id = c_rec.transaction_id
AND ad1.accounting_date <= l_end_date
AND ad1.posted_flag = 'Y' AND
/* Invoice Lines Project TAX is now REC_TAX and NONREC_TAX */
ad1.line_type_lookup_code <> 'REC_TAX' AND
ROWNUM < 2;
SELECT
ad2.invoice_distribution_id,
ad2.invoice_id,
-- J Changes ------------------------------------------------------------------
nvl(DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work Procurement
'AMOUNT', AD2.AMOUNT,
'QUANTITY',ad2.quantity_invoiced), 0 ) "QUANTITY_INVOICED", -- Invoice UOM
-------------------------------------------------------------------------------
ad2.distribution_line_number,
ad2.line_type_lookup_code,
-- J Changes ------------------------------------------------------------------
nvl(DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work Procurement
'AMOUNT', 1,
'QUANTITY', ad2.unit_price), 0 ) unit_price, -- Invoice Currency
--------------------------------------------------------------------------------
nvl(ad2.base_amount, nvl(ad2.amount, 0)) base_amount
FROM
ap_invoice_distributions_all ad2,
-- J Changes -----------------------------------------------------------
RCV_TRANSACTIONS RT,
PO_LINES_ALL POL,
PO_LINE_LOCATIONS_ALL POLL, -- Added for Complex work Procurement
ap_invoices_all aia /* bug 4352624 Added to ignore invoices of type prepayment */
------------------------------------------------------------------------
WHERE
ad2.rcv_transaction_id = c_rec.transaction_id
AND ad2.posted_flag = 'Y'
/* bug 4352624 Added to ignore invoices of type prepayment */
AND ad2.line_type_lookup_code <>'PREPAY'
AND aia.invoice_id = ad2.invoice_id
AND aia.invoice_type_lookup_code <>'PREPAYMENT'
-- J Changes -----------------------------------------------------------
AND RT.TRANSACTION_ID = AD2.RCV_TRANSACTION_ID
AND POL.PO_LINE_ID = RT.PO_LINE_ID
AND RT.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND POLL.PO_LINE_ID = POL.PO_LINE_ID ---- Added for Complex work Procurement
------------------------------------------------------------------------
AND ad2.accounting_date <= l_end_date
/* Invoice Lines Project TAX is now REC_TAX AND NONREC_TAX */
AND ad2.line_type_lookup_code <> 'REC_TAX'
-- J Changes -------------------------------------------------------------
-- Ensure that Price corrections are not picked --
/* Invoice Lines Project root_distribution_id ->
corrected_invoice_dist_id */
AND ad2.corrected_invoice_dist_id is null;
SELECT AIDA.INVOICE_ID, /*Bug3891984*/
AIDA.INVOICE_DISTRIBUTION_ID, -- Only for debugging
NVL(AIDA.BASE_AMOUNT, NVL(AIDA.AMOUNT, 0))
INTO l_corr_invoice_id,
l_corr_inv,
l_correction_amount
FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICES_ALL AP_INV
/* Invoice Lines Project
No root_distribution_id or xinv_parent_reversal_id
now it'll just be represented by corrected_invoice_dist_id
*/
WHERE AIDA.CORRECTED_INVOICE_DIST_ID = c_inv.INVOICE_DISTRIBUTION_ID
AND AIDA.INVOICE_ID = AP_INV.INVOICE_ID
AND AP_INV.INVOICE_TYPE_LOOKUP_CODE = 'PO PRICE ADJUST';
SELECT NVL(AIDA.BASE_AMOUNT, NVL(AIDA.AMOUNT, 0))
INTO l_correction_tax_amount
FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE AIDA.INVOICE_ID = l_corr_invoice_id
/* Invoice Lines Project non-recoverable tax now is just NONREC_TAX */
AND AIDA.LINE_TYPE_LOOKUP_CODE = 'NONREC_TAX';
So we should prevent insertion into the details table from the c_reciepts cursor as it will be
inserted into the details table later from the chrg_allocations cursor */
l_chrg_present := 0;
Select count(*) into l_chrg_present
from ap_invoice_distributions_all
where invoice_distribution_id = c_inv.invoice_distribution_id
and charge_applicable_to_dist_id is not null;
select cst_rcv_acq_cost_details_s.nextval into l_details_nextvalue
from dual;
select decode(l_pri_quantity_invoiced,0,0,(c_inv.unit_price * c_inv.quantity_invoiced / l_pri_quantity_invoiced)) into l_priuom_cost
from dual;
Insert_into_acqdtls_tables (
l_header,
l_details_nextvalue,
'INVOICE',
NULL,
c_inv.invoice_distribution_id,
1,
0,
c_inv.invoice_distribution_id,
NULL,
NULL,
NULL,
c_inv.base_amount + l_correction_amount, -- in func currency
l_pri_quantity_invoiced, -- in pri uom
l_priuom_cost, -- convert to price based on pri uom
c_inv.line_type_lookup_code,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_req_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id,
i_source_flag,
l_err_num,
l_err_msg);
SELECT RT.TRANSACTION_ID,
RT.TRANSACTION_TYPE,
RT.AMOUNT,
RT.PARENT_TRANSACTION_ID
FROM RCV_TRANSACTIONS RT
WHERE ( ( (i_source_flag = 1)
AND ( ( (i_res_flag =1)
AND (rt.transaction_date between i_start_date and i_end_date))
OR (i_res_flag = 2)))
OR ( ( i_source_flag = 2 ) AND (rt.transaction_date <= i_end_date ) ) )
START WITH
RT.transaction_id = i_transaction_id
CONNECT BY
PRIOR RT.transaction_id = RT.parent_transaction_id;
SELECT
rt4.transaction_id,
rt4.transaction_type,
rt4.primary_quantity,
rt4.quantity,/* ADDED FOR #BUG6697382*/
rt4.parent_transaction_id
FROM
rcv_transactions rt4
WHERE
(((i_source_flag = 1) AND (((i_res_flag =1) AND (rt4.transaction_date between i_start_date and i_end_date)) OR (i_res_flag = 2))) OR ((i_source_flag = 2 ) AND (rt4.transaction_date <= i_end_date)))
START WITH
rt4.transaction_id = i_transaction_id
CONNECT BY
prior rt4.transaction_id = rt4.parent_transaction_id;
SELECT NVL(POLL.MATCHING_BASIS, POL.MATCHING_BASIS) -- Changed for Complex work Procurement
INTO L_PO_LINE_TYPE_CODE
FROM PO_LINES_ALL POL,
PO_LINE_LOCATIONS_ALL POLL, -- Added for Complex work Procurement
RCV_TRANSACTIONS RT
WHERE POL.PO_LINE_ID = RT.PO_LINE_ID
AND POLL.LINE_LOCATION_ID = RT.PO_LINE_LOCATION_ID-- Added for Complex work Procurement
AND RT.TRANSACTION_ID = I_TRANSACTION_ID;
SELECT transaction_type
INTO l_parent_type
FROM rcv_transactions
WHERE transaction_id = c_amount_rec.parent_transaction_id;
SELECT
rt5.transaction_type
INTO
l_parent_type
FROM
rcv_transactions rt5
WHERE
rt5.transaction_id = c_nqr_rec.parent_transaction_id;
SELECT count(1)
INTO l_chg_count
FROM ap_invoice_distributions_all aida
WHERE aida.posted_flag = 'Y'
AND (((i_res_flag = 1)
AND (aida.accounting_date BETWEEN i_start_date AND i_end_Date))
OR (i_res_flag = 2))
AND aida.line_type_lookup_code <> 'REC_TAX'
AND EXISTS (
SELECT 'X'
FROM ap_invoice_distributions_all aida2
WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id
AND aida2.posted_flag = 'Y'
AND (((i_res_flag = 1)
AND (aida2.accounting_date BETWEEN i_start_date AND i_end_Date))
OR (i_res_flag = 2))
AND aida2.line_type_lookup_code <> 'REC_TAX'
)
START WITH
aida.charge_applicable_to_dist_id = i_item_dist
CONNECT BY
prior aida.invoice_distribution_id = aida.charge_applicable_to_dist_id;
INSERT INTO
cst_rcv_acq_cost_details ( -- cracd2
HEADER_ID,
DETAIL_ID,
SOURCE_TYPE,
PO_LINE_LOCATION_ID,
PARENT_DISTRIBUTION_ID,
DISTRIBUTION_NUM,
LEVEL_NUM,
INVOICE_DISTRIBUTION_ID,
PARENT_INVOICE_DIST_ID,
ALLOCATED_AMOUNT,
PARENT_AMOUNT,
AMOUNT,
QUANTITY,
PRICE,
LINE_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
SELECT
i_hdr,
cst_rcv_acq_cost_details_s.nextval,
'INVOICE',
NULL,
i_item_dist,
rownum + 1,
LEVEL,
aida.invoice_distribution_id,
aida.charge_applicable_to_dist_id,
nvl(aida.base_amount,nvl(aida.amount,0)) base_amount,
NULL,
NULL,
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_req_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id
FROM
ap_invoice_distributions_all aida
WHERE aida.posted_flag = 'Y'
AND (((i_res_flag = 1) AND (aida.accounting_date BETWEEN i_start_date AND i_end_Date))
OR (i_res_flag = 2))
AND aida.line_type_lookup_code <> 'REC_TAX'
AND EXISTS (
SELECT 'X'
FROM ap_invoice_distributions_all aida2
WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id
AND aida2.posted_flag = 'Y'
AND (((i_res_flag = 1) AND (aida2.accounting_date BETWEEN i_start_date AND i_end_Date))
OR (i_res_flag = 2))
AND aida2.line_type_lookup_code <> 'REC_TAX'
)
START WITH
aida.charge_applicable_to_dist_id = i_item_dist
CONNECT BY
prior aida.invoice_distribution_id = aida.charge_applicable_to_dist_id;
UPDATE
cst_rcv_acq_cost_details cracd3
SET
cracd3.parent_amount = (
SELECT
nvl(ad7.base_amount,nvl(ad7.amount,0))
FROM
ap_invoice_distributions_all ad7
WHERE
ad7.invoice_distribution_id = cracd3.parent_invoice_dist_id) ,
cracd3.line_type = (
SELECT
ad8.line_type_lookup_code
FROM
ap_invoice_distributions_all ad8
WHERE
ad8.invoice_distribution_id = cracd3.invoice_distribution_id)
WHERE
cracd3.parent_invoice_dist_id IS NOT NULL AND
cracd3.invoice_distribution_id IS NOT NULL AND
cracd3.parent_distribution_id = i_item_dist AND
cracd3.header_id = i_hdr;
UPDATE
cst_rcv_acq_cost_details cracd4
SET
cracd4.amount = cracd4.allocated_amount -- amount in func curr
WHERE
cracd4.header_id = i_hdr AND
cracd4.PARENT_INVOICE_DIST_ID = i_item_dist;
SELECT
count(1)
INTO
l_chg_count
FROM
ap_invoice_distributions_all aida
WHERE aida.posted_flag = 'Y'
AND aida.accounting_date <= l_end_date
AND aida.line_type_lookup_code <> 'REC_TAX'
AND EXISTS (
SELECT 'X'
FROM ap_invoice_distributions_all aida2
WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id
AND aida2.posted_flag = 'Y'
AND aida2.accounting_date <= l_end_date
AND aida2.line_type_lookup_code <> 'REC_TAX'
)
START WITH
aida.charge_applicable_to_dist_id = i_item_dist
CONNECT BY
prior aida.invoice_distribution_id = aida.charge_applicable_to_dist_id;
INSERT INTO
cst_rcv_acq_cost_details_adj ( -- cracd2
HEADER_ID,
DETAIL_ID,
SOURCE_TYPE,
PO_LINE_LOCATION_ID,
PARENT_DISTRIBUTION_ID,
DISTRIBUTION_NUM,
LEVEL_NUM,
INVOICE_DISTRIBUTION_ID,
PARENT_INVOICE_DIST_ID,
ALLOCATED_AMOUNT,
PARENT_AMOUNT,
AMOUNT,
QUANTITY,
PRICE,
LINE_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
SELECT
i_hdr,
cst_rcv_acq_cost_details_s.nextval,
'INVOICE',
NULL,
i_item_dist,
rownum + 1,
LEVEL,
aida.invoice_distribution_id,
aida.charge_applicable_to_dist_id,
nvl(aida.base_amount,nvl(aida.amount,0)) base_amount,
NULL,
NULL,
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_req_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id
FROM
ap_invoice_distributions_all aida
WHERE aida.posted_flag = 'Y'
AND aida.accounting_date <= l_end_date
AND aida.line_type_lookup_code <> 'REC_TAX'
AND EXISTS (
SELECT 'X'
FROM ap_invoice_distributions_all aida2
WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id
AND aida2.posted_flag = 'Y'
AND aida2.accounting_date <= l_end_date
AND aida2.line_type_lookup_code <> 'REC_TAX'
)
START WITH
aida.charge_applicable_to_dist_id = i_item_dist
CONNECT BY
prior aida.invoice_distribution_id = aida.charge_applicable_to_dist_id;
UPDATE
cst_rcv_acq_cost_details_adj cracd3
SET
cracd3.parent_amount = (
SELECT
nvl(ad7.base_amount,nvl(ad7.amount,0))
FROM
ap_invoice_distributions_all ad7
WHERE
ad7.invoice_distribution_id = cracd3.parent_invoice_dist_id) ,
cracd3.line_type = (
SELECT
ad8.line_type_lookup_code
FROM
ap_invoice_distributions_all ad8
WHERE
ad8.invoice_distribution_id = cracd3.invoice_distribution_id)
WHERE
cracd3.parent_invoice_dist_id IS NOT NULL AND
cracd3.invoice_distribution_id IS NOT NULL AND
cracd3.parent_distribution_id = i_item_dist AND
cracd3.header_id = i_hdr;
UPDATE
cst_rcv_acq_cost_details_adj cracd4
SET
cracd4.amount = cracd4.allocated_amount -- amount in func curr
WHERE
cracd4.header_id = i_hdr AND
cracd4.PARENT_INVOICE_DIST_ID = i_item_dist;
SELECT count(header_id)
INTO l_cracd_count
FROM cst_rcv_acq_cost_details cracd9
WHERE cracd9.header_id = i_header
AND ROWNUM < 2;
SELECT
SUM(cracd10.amount)
INTO
l_total_invoice_amount
FROM
cst_rcv_acq_cost_details cracd10
WHERE
cracd10.header_id = i_header;
SELECT
SUM(nvl(cracd11.quantity,0))
INTO
l_qty_invoiced
FROM
cst_rcv_acq_cost_details cracd11
WHERE
cracd11.header_id = i_header;
UPDATE
cst_rcv_acq_costs crac2
SET
crac2.total_invoice_amount = l_total_invoice_amount,
crac2.total_quantity_invoiced = l_qty_invoiced,
crac2.quantity_at_po_price = l_qty_at_po,
crac2.amount_at_po_price = l_amount_at_po,
crac2.total_amount = l_total_amount,
crac2.costed_quantity = l_costed_quantity,
crac2.acquisition_cost = l_acq_cost
WHERE
crac2.header_id = i_header;
INSERT INTO
cst_rcv_acq_cost_details ( --cracd12
HEADER_ID,
DETAIL_ID,
SOURCE_TYPE,
PO_LINE_LOCATION_ID,
PARENT_DISTRIBUTION_ID,
DISTRIBUTION_NUM,
LEVEL_NUM,
INVOICE_DISTRIBUTION_ID,
PARENT_INVOICE_DIST_ID,
ALLOCATED_AMOUNT,
PARENT_AMOUNT,
AMOUNT,
QUANTITY,
PRICE,
LINE_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES (
i_header,
cst_rcv_acq_cost_details_s.nextval,
'PO',
i_po_line_loc,
NULL,
-1,
0,
NULL,
NULL,
NULL,
NULL,
l_amount_at_po,
l_qty_at_po,
i_po_price,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_req_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id);
SELECT count(header_id)
INTO l_cracd_count
FROM cst_rcv_acq_cost_details_adj cracd9
WHERE cracd9.header_id = i_header
AND ROWNUM < 2;
SELECT
SUM(cracd10.amount)
INTO
l_total_invoice_amount
FROM
cst_rcv_acq_cost_details_adj cracd10
WHERE
cracd10.header_id = i_header;
SELECT
SUM(nvl(cracd11.quantity,0))
INTO
l_qty_invoiced
FROM
cst_rcv_acq_cost_details_adj cracd11
WHERE
cracd11.header_id = i_header;
UPDATE
cst_rcv_acq_costs_adj crac2
SET
crac2.total_invoice_amount = l_total_invoice_amount,
crac2.total_quantity_invoiced = l_qty_invoiced,
crac2.quantity_at_po_price = l_qty_at_po,
crac2.amount_at_po_price = l_amount_at_po,
crac2.total_amount = l_total_amount,
crac2.costed_quantity = l_costed_quantity,
crac2.acquisition_cost = l_acq_cost
WHERE
crac2.header_id = i_header;
INSERT INTO
cst_rcv_acq_cost_details_adj ( --cracd12
HEADER_ID,
DETAIL_ID,
SOURCE_TYPE,
PO_LINE_LOCATION_ID,
PARENT_DISTRIBUTION_ID,
DISTRIBUTION_NUM,
LEVEL_NUM,
INVOICE_DISTRIBUTION_ID,
PARENT_INVOICE_DIST_ID,
ALLOCATED_AMOUNT,
PARENT_AMOUNT,
AMOUNT,
QUANTITY,
PRICE,
LINE_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES (
i_header,
cst_rcv_acq_cost_details_s.nextval,
'PO',
i_po_line_loc,
NULL,
-1,
0,
NULL,
NULL,
NULL,
NULL,
l_amount_at_po,
l_qty_at_po,
i_po_price,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_req_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id);
select NVL((crac.net_quantity_received * crac.acquisition_cost),0),
crac.rcv_transaction_id, nvl(crac.acquisition_cost,0),
nvl(crac.net_quantity_received,0)
INTO l_ori_acq_amount, l_rcv_txn_id, l_original_acq_cost,
l_original_qty_received
FROM cst_rcv_acq_costs crac, cst_rcv_acq_costs_adj craca
WHERE craca.header_id = i_header
AND crac.rcv_transaction_id = craca.rcv_transaction_id
AND crac.cost_type_id = i_cost_type_id
AND crac.cost_group_id = i_cost_group_id;
select NVL(SUM(NVL(value_change,0)),0) into l_old_increments
from mtl_material_transactions mmt, cst_rcv_acq_costs_adj craca
where mmt.transaction_id = craca.mmt_transaction_id
and craca.mmt_transaction_id is NOT NULL
and craca.cost_group_id = i_cost_group_id
and craca.cost_type_id = i_cost_type_id
and craca.rcv_transaction_id = (select rcv_transaction_id
from cst_rcv_acq_costs_adj craca2
where craca2.header_id = i_header);
select nvl(net_quantity_received,0)
into l_net_qty_received
from cst_rcv_acq_costs_adj
where header_id = i_header;
/* now start geting the details that are required to insert into MMT */
/* first get the legal entity for the cost group */
l_stmt_num := 55;
select legal_entity into l_legal_entity
from cst_cost_groups
where cost_group_id = i_cost_group_id ;
select NVL(MAX(pac_period_id), -1) into l_prev_period_id
from cst_pac_periods
where legal_entity = l_legal_entity
and open_flag = 'N'
and cost_type_id = i_cost_type_id;
select wip_entity_id
into l_wip_entity_id
from
(
Select distinct wip_entity_id
from rcv_transactions rt2
where rt2.transaction_type in ('DELIVER')
START WITH
rt2.transaction_id = (select rcv_transaction_id
from cst_rcv_acq_costs_adj craca2
where craca2.header_id = i_header)
CONNECT BY
prior rt2.transaction_id = rt2.parent_transaction_id
)
where rownum = 1;
Select primary_item_id
into l_item
from wip_entities
where wip_entity_id = l_wip_entity_id ;
select NVL(total_layer_quantity,0),NVL(item_cost,0)
into l_prior_period_quantity,l_prior_period_cost
from cst_pac_item_costs
where pac_period_id = l_prev_period_id
and cost_group_id = i_cost_group_id
and inventory_item_id = l_item ;
select MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL into l_transaction_id
from dual;
select NVL(period_end_date,sysdate) into l_period_close_date
from CST_PAC_PERIODS
where pac_period_id = i_pac_period_id
and legal_entity = l_legal_entity
and cost_type_id = i_cost_type_id;
/* Now insert stuff into MMT */
select LEAST(l_period_close_date,sysdate) into l_least_date
from dual;
INSERT INTO MTL_MATERIAL_TRANSACTIONS
(transaction_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
inventory_item_id,
organization_id,
transaction_type_id,
transaction_action_id,
transaction_source_type_id,
transaction_quantity,
transaction_uom,
primary_quantity,
transaction_date,
value_change,
material_account,
material_overhead_account,
resource_account,
outside_processing_account,
overhead_account,
costed_flag,
org_cost_group_id,
cost_type_id,
source_code,
source_line_id)
VALUES (
l_transaction_id,
sysdate,
i_user_id,
sysdate,
i_user_id,
l_item,
i_org_id,
26,
24,
14,
--l_prior_period_quantity,
0,
i_primary_uom,
--l_prior_period_quantity,
0,
l_least_date, --- transaction_date is sysdate
l_acq_adjustment_amount,
i_adj_account,
i_adj_account,
i_adj_account,
i_adj_account,
i_adj_account,
NULL,
i_cost_group_id,
i_cost_type_id,
'ACQADJ',
l_rcv_txn_id
);
/* insert into MPTCD */
Insert into mtl_pac_txn_cost_details
(cost_group_id,
transaction_id,
pac_period_id,
cost_type_id,
cost_element_id,
level_type,
inventory_item_id,
value_change,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by )
Values (i_cost_group_id,
l_transaction_id,
i_pac_period_id,
i_cost_type_id,
1, -- cost element ID
1, -- THis level
l_item,
l_acq_adjustment_amount,
l_prior_period_cost,
sysdate,
i_user_id,
sysdate,
i_user_id);
/* Now update the entry in CRACA with the new transaction ID of MMT */
l_stmt_num := 100;
Update cst_rcv_acq_costs_adj set
mmt_transaction_id = l_transaction_id
where header_id = i_header;
SELECT
rcv_transaction_id
INTO
l_rcv_txn
FROM
mtl_material_transactions mmt
WHERE
mmt.transaction_id = i_txn_id AND
mmt.organization_id in (
SELECT
ccga2.organization_id
FROM
cst_cost_group_assignments ccga2
WHERE
ccga2.cost_group_id = i_cost_group_id);
SELECT
rcv_transaction_id
INTO
l_rcv_txn
FROM
wip_transactions wt
WHERE
wt.transaction_id = i_txn_id AND
wt.organization_id in (
SELECT
ccga3.organization_id
FROM
cst_cost_group_assignments ccga3
WHERE
ccga3.cost_group_id = i_cost_group_id);
SELECT
rt6.transaction_id,
nvl(poll.lcm_flag,'N')
INTO
l_par_txn,
l_lcm_flag
FROM
rcv_transactions rt6,
po_line_locations_all poll
WHERE
rt6.transaction_type in ('RECEIVE','MATCH')
AND poll.line_location_id = rt6.po_line_location_id
START WITH
rt6.transaction_id = l_rcv_txn
CONNECT BY
rt6.transaction_id = prior rt6.parent_transaction_id;
SELECT
nvl(crac3.acquisition_cost,-1)
INTO
l_rec_cost
FROM
cst_rcv_acq_costs crac3
WHERE
crac3.cost_type_id = i_cost_type_id AND
crac3.cost_group_id = i_cost_group_id AND
crac3.rcv_transaction_id = l_par_txn;
SELECT
nvl(max(crac3.acquisition_cost),-1)
INTO
l_rec_cost
FROM
cst_rcv_acq_costs crac3
WHERE
crac3.cost_type_id = i_cost_type_id AND
crac3.cost_group_id = i_cost_group_id AND
crac3.rcv_transaction_id = l_par_txn;
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_txn
AND craca.cost_type_id = i_cost_type_id
AND craca.cost_group_id = i_cost_group_id;
SELECT craca.acquisition_cost
INTO l_rec_cost
FROM cst_rcv_acq_costs_adj craca
WHERE craca.rcv_transaction_id = l_par_txn
AND craca.cost_type_id = i_cost_type_id
AND craca.cost_group_id = i_cost_group_id
AND craca.period_id = l_lcm_adj_period;
SELECT
nvl((SUM(NVL(nonrecoverable_tax,0))
/SUM(DECODE(PLL.MATCHING_BASIS,
'AMOUNT', POD.AMOUNT_ORDERED,
'QUANTITY', POD.quantity_ordered ) ) ), 0 )
INTO
l_tot_tax
FROM
po_distributions_all pod,
rcv_transactions rcv,
po_line_locations_all pll
WHERE RCV.TRANSACTION_ID = i_rcv_txn_id
AND POD.LINE_LOCATION_ID = RCV.PO_LINE_LOCATION_ID
AND PLL.LINE_LOCATION_ID = RCV.PO_LINE_LOCATION_ID
AND (
( RCV.PO_DISTRIBUTION_ID IS NOT NULL
AND RCV.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
)
OR
( RCV.PO_DISTRIBUTION_ID IS NULL
AND RCV.PO_LINE_LOCATION_ID = POD.LINE_LOCATION_ID
)
);
SELECT count(rcv_transaction_id)
INTO l_rsl_exists
FROM rcv_receiving_sub_ledger rsl
WHERE rsl.rcv_transaction_id = i_rcv_txn_id
AND rsl.accounted_cr IS NOT NULL
AND rsl.accounted_cr <> 0
AND ROWNUM < 2;
Select
TRX_FLOW_HEADER_ID,
organization_id
Into
l_trx_flow,
l_org_id
From rcv_accounting_events rae,
po_lines_all POL,
po_distributions_all POD
Where rae.rcv_transaction_id = i_rcv_txn_id
And rae.event_type_id = 1 -- RECEIVE
And rae.trx_flow_header_id is not null
AND RAE.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
AND POD.PO_LINE_ID = POL.PO_LINE_ID;
SELECT
SUM(rsl.accounted_cr) / SUM(rsl.entered_cr)
INTO
l_po_rate
FROM
rcv_receiving_sub_ledger rsl,
rcv_accounting_events rae
WHERE
rsl.rcv_transaction_id = i_rcv_txn_id AND
rsl.accounted_cr is not null AND
rsl.accounted_cr <> 0 and
rsl.accounting_event_id = rae.accounting_event_id and
rae.event_type_id <> 7
and rae.organization_id = l_org_id;
SELECT
SUM(rsl.accounted_cr) / SUM(rsl.entered_cr)
INTO
l_po_rate
FROM
rcv_receiving_sub_ledger rsl
WHERE
rsl.rcv_transaction_id = i_rcv_txn_id AND
rsl.accounted_cr is not null AND
rsl.accounted_cr <> 0;
SELECT
SUM(DECODE(POLL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_ORDERED, POD.QUANTITY_ORDERED)*nvl(pod.rate,1))
/SUM(DECODE(POLL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_ORDERED, POD.QUANTITY_ORDERED))
INTO
l_po_rate
FROM
PO_DISTRIBUTIONS_ALL POD,
RCV_TRANSACTIONS RT,
PO_LINE_LOCATIONS_ALL POLL
WHERE
RT.TRANSACTION_ID = i_rcv_txn_id
AND (
( RT.PO_DISTRIBUTION_ID IS NOT NULL
AND RT.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
)
OR
( RT.PO_DISTRIBUTION_ID IS NULL
AND RT.PO_LINE_LOCATION_ID = POD.LINE_LOCATION_ID
)
)
AND POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID;
SELECT
rt4.transaction_id,
rt4.transaction_type,
rt4.primary_quantity,
rt4.parent_transaction_id
FROM
rcv_transactions rt4
WHERE
rt4.transaction_date < i_end_date
START WITH
rt4.transaction_id = i_transaction_id
CONNECT BY
prior rt4.transaction_id = rt4.parent_transaction_id;
SELECT
rt5.transaction_type
INTO
l_parent_type
FROM
rcv_transactions rt5
WHERE
rt5.transaction_id = c_nqud_rec.parent_transaction_id;
Procedure Insert_into_acqhdr_tables(
i_header_id IN NUMBER,
i_cost_group_id IN NUMBER,
i_cost_type_id IN NUMBER,
i_period_id IN NUMBER,
i_rcv_transaction_id IN NUMBER,
i_net_quantity_received IN NUMBER,
i_total_quantity_invoiced IN NUMBER,
i_quantity_at_po_price IN NUMBER,
i_total_invoice_amount IN NUMBER,
i_amount_at_po_price IN NUMBER,
i_total_amount IN NUMBER,
i_costed_quantity IN NUMBER,
i_acquisition_cost IN NUMBER,
i_po_line_location_id IN NUMBER,
i_po_unit_price IN NUMBER,
i_primary_uom IN VARCHAR2,
i_rec_exchg_rate IN NUMBER,
i_last_update_date IN DATE,
i_last_updated_by IN NUMBER,
i_creation_date IN DATE,
i_created_by IN NUMBER,
i_request_id IN NUMBER,
i_program_application_id IN NUMBER,
i_program_id IN NUMBER,
i_program_update_date IN DATE,
i_last_update_login IN NUMBER,
i_source_flag IN NUMBER,
o_err_num OUT NOCOPY NUMBER,
o_err_msg OUT NOCOPY VARCHAR2 ) IS
l_stmt_no NUMBER := 10;
INSERT INTO cst_rcv_acq_costs (
HEADER_ID,
COST_GROUP_ID,
COST_TYPE_ID,
PERIOD_ID,
RCV_TRANSACTION_ID,
NET_QUANTITY_RECEIVED,
TOTAL_QUANTITY_INVOICED,
QUANTITY_AT_PO_PRICE,
TOTAL_INVOICE_AMOUNT,
AMOUNT_AT_PO_PRICE,
TOTAL_AMOUNT,
COSTED_QUANTITY,
ACQUISITION_COST,
PO_LINE_LOCATION_ID,
PO_UNIT_PRICE,
PRIMARY_UOM,
REC_EXCHG_RATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values(i_header_id,
i_cost_group_id,
i_cost_type_id,
i_period_id,
i_rcv_transaction_id,
i_net_quantity_received,
i_total_quantity_invoiced,
i_quantity_at_po_price,
i_total_invoice_amount,
i_amount_at_po_price,
i_total_amount,
i_costed_quantity,
i_acquisition_cost,
i_po_line_location_id,
i_po_unit_price,
i_primary_uom,
i_rec_exchg_rate,
i_last_update_date,
i_last_updated_by,
i_creation_date,
i_created_by,
i_request_id,
i_program_application_id,
i_program_id,
i_program_update_date,
i_last_update_login );
INSERT INTO cst_rcv_acq_costs_adj (
HEADER_ID,
COST_GROUP_ID,
COST_TYPE_ID,
PERIOD_ID,
RCV_TRANSACTION_ID,
NET_QUANTITY_RECEIVED,
TOTAL_QUANTITY_INVOICED,
QUANTITY_AT_PO_PRICE,
TOTAL_INVOICE_AMOUNT,
AMOUNT_AT_PO_PRICE,
TOTAL_AMOUNT,
COSTED_QUANTITY,
ACQUISITION_COST,
PO_LINE_LOCATION_ID,
PO_UNIT_PRICE,
PRIMARY_UOM,
REC_EXCHG_RATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values(i_header_id,
i_cost_group_id,
i_cost_type_id,
i_period_id,
i_rcv_transaction_id,
i_net_quantity_received,
i_total_quantity_invoiced,
i_quantity_at_po_price,
i_total_invoice_amount,
i_amount_at_po_price,
i_total_amount,
i_costed_quantity,
i_acquisition_cost,
i_po_line_location_id,
i_po_unit_price,
i_primary_uom,
i_rec_exchg_rate,
i_last_update_date,
i_last_updated_by,
i_creation_date,
i_created_by,
i_request_id,
i_program_application_id,
i_program_id,
i_program_update_date,
i_last_update_login );
o_err_msg := SUBSTR('CSTPPACQ.Insert_into_acqhdr_tables('
||to_char(l_stmt_no)
||'):'
||SQLERRM,1,240);
end Insert_into_acqhdr_tables;
Procedure Insert_into_acqdtls_tables (
i_header_id IN NUMBER,
i_detail_id IN NUMBER,
i_source_type IN VARCHAR2,
i_po_line_location_id IN NUMBER,
i_parent_distribution_id IN NUMBER,
i_distribution_num IN NUMBER,
i_level_num IN NUMBER,
i_invoice_distribution_id IN NUMBER,
i_parent_inv_distribution_id IN NUMBER,
i_allocated_amount IN NUMBER,
i_parent_amount IN NUMBER,
i_amount IN NUMBER,
i_quantity IN NUMBER,
i_price IN NUMBER,
i_line_type IN VARCHAR2,
i_last_update_date IN DATE,
i_last_updated_by IN NUMBER,
i_creation_date IN DATE,
i_created_by IN NUMBER,
i_request_id IN NUMBER,
i_program_application_id IN NUMBER,
i_program_id IN NUMBER,
i_program_update_date IN DATE,
i_last_update_login IN NUMBER,
i_source_flag IN NUMBER,
o_err_num OUT NOCOPY NUMBER,
o_err_msg OUT NOCOPY VARCHAR2) IS
l_stmt_no NUMBER := 10;
Insert into cst_rcv_acq_cost_details(
HEADER_ID,
DETAIL_ID,
SOURCE_TYPE,
PO_LINE_LOCATION_ID,
PARENT_DISTRIBUTION_ID,
DISTRIBUTION_NUM,
LEVEL_NUM,
INVOICE_DISTRIBUTION_ID,
PARENT_INVOICE_DIST_ID,
ALLOCATED_AMOUNT,
PARENT_AMOUNT,
AMOUNT,
QUANTITY,
PRICE,
LINE_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values(
i_header_id,
i_detail_id,
i_source_type,
i_po_line_location_id,
i_parent_distribution_id,
i_distribution_num,
i_level_num,
i_invoice_distribution_id,
i_parent_inv_distribution_id,
i_allocated_amount,
i_parent_amount,
i_amount,
i_quantity,
i_price,
i_line_type,
i_last_update_date,
i_last_updated_by,
i_creation_date,
i_created_by,
i_request_id,
i_program_application_id,
i_program_id,
i_program_update_date,
i_last_update_login
);
Insert into cst_rcv_acq_cost_details_adj(
HEADER_ID,
DETAIL_ID,
SOURCE_TYPE,
PO_LINE_LOCATION_ID,
PARENT_DISTRIBUTION_ID,
DISTRIBUTION_NUM,
LEVEL_NUM,
INVOICE_DISTRIBUTION_ID,
PARENT_INVOICE_DIST_ID,
ALLOCATED_AMOUNT,
PARENT_AMOUNT,
AMOUNT,
QUANTITY,
PRICE,
LINE_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values(
i_header_id,
i_detail_id,
i_source_type,
i_po_line_location_id,
i_parent_distribution_id,
i_distribution_num,
i_level_num,
i_invoice_distribution_id,
i_parent_inv_distribution_id,
i_allocated_amount,
i_parent_amount,
i_amount,
i_quantity,
i_price,
i_line_type,
i_last_update_date,
i_last_updated_by,
i_creation_date,
i_created_by,
i_request_id,
i_program_application_id,
i_program_id,
i_program_update_date,
i_last_update_login
);
o_err_msg := SUBSTR('CSTPPACQ.Insert_into_acqdtls_tables('
||to_char(l_stmt_no)
||'):'
||SQLERRM,1,240);
END Insert_into_acqdtls_tables;