select gl.name Ledger,
haou2.name Operating_Unit,
mp.organization_code Org_Code,
ap_txns.period_name Period_Name,
&segment_columns
ap_txns.type Type,
flv.meaning Accounting_Class_Code,
pv.vendor_name Supplier,
he.full_name Buyer,
msiv.concatenated_segments Item_Number,
msiv.description Item_Description,
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type,
&category_columns
pl.displayed_field Destination_Type,
pha.segment1 PO_Number,
to_char(pla.line_num) PO_Line,
pra.release_num PO_Release,
aia.invoice_num Invoice_Number,
-- Decode to enable a null value for the additional union all for A/P Accrual Write-Offs
decode(ap_txns.ipv_distribution_line_number, 0, null, ap_txns.ipv_distribution_line_number) IPV_Invoice_Line,
decode(ap_txns.erv_distribution_line_number, 0, null, ap_txns.erv_distribution_line_number) ERV_Invoice_Line,
aia.invoice_date Invoice_Date,
ap_txns.accounting_date Accounting_Date,
pla.unit_meas_lookup_code PO_UOM,
ap_txns.quantity_invoiced * ucr.conversion_rate Invoice_Quantity,
nvl(pha.currency_code, gl.currency_code) PO_Currency_Code,
plla.price_override PO_Unit_Price,
nvl(ap_txns.rate,1) PO_Exchange_Rate,
gl.currency_code GL_Currency_Code,
round(nvl(ap_txns.rate,1) * plla.price_override,6) Converted_PO_Unit_Price,
nvl(ap_txns.rate,1) * plla.price_override * (ap_txns.quantity_invoiced * ucr.conversion_rate) Total_PO_Amount,
nvl(aia.invoice_currency_code, gl.currency_code) Invoice_Currency_Code,
ap_txns.unit_price Payables_Unit_Cost,
nvl(aia.exchange_rate,1) Payables_Exchange_Rate,
gl.currency_code GL_Currency_Code,
round(nvl(ap_txns.unit_price,0)*nvl(aia.exchange_rate,1),6) Converted_Invoice_Unit_Cost,
round(nvl(ap_txns.unit_price,0)*nvl(aia.exchange_rate,1),6) - round(nvl(ap_txns.rate,1) * plla.price_override,6) Unit_Cost_Variance,
round(nvl(ap_txns.unit_price,0)*nvl(aia.exchange_rate,1) * ap_txns.quantity_invoiced * ucr.conversion_rate,2) Total_Invoice_Amount,
round(nvl(ap_txns.unit_price,0)*nvl(aia.exchange_rate,1) * ap_txns.quantity_invoiced * ucr.conversion_rate -
nvl(ap_txns.rate,1) * plla.price_override * ap_txns.quantity_invoiced * ucr.conversion_rate
,2) Total_Calculated_IPV,
ap_txns.ipv_amount General_Ledger_IPV_Amount,
ap_txns.erv_amount General_Ledger_ERV_Amount,
round((nvl(ap_txns.unit_price,0)*nvl(aia.exchange_rate,1) * ap_txns.quantity_invoiced * ucr.conversion_rate -
nvl(ap_txns.rate,1) * plla.price_override * ap_txns.quantity_invoiced * ucr.conversion_rate)
/ abs(decode(nvl(ap_txns.rate,1) * plla.price_override * (ap_txns.quantity_invoiced * ucr.conversion_rate),0,1,nvl(ap_txns.rate,1)
* plla.price_override * (ap_txns.quantity_invoiced * ucr.conversion_rate))) * 100,1) Percent
from
ap_invoices_all aia,
po_vendors pv,
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
po_releases_all pra,
mtl_parameters mp,
mtl_system_items_vl msiv,
mtl_uom_conversions_view ucr,
gl_code_combinations gcc1,
gl_code_combinations gcc2,
hr_employees he,
po_lookup_codes pl,
fnd_lookup_values flv,
hr_organization_information hoi,
hr_all_organization_units_vl haou, -- inv_organization_id
hr_all_organization_units_vl haou2, -- operating unit
gl_ledgers gl,
-- =================================================================================
-- Use inline tables to select information from both the IPV and ERV AID lines
-- AID lines of IPV and ERV don't have an invoice quantity but AID Accrual lines do
-- AID invoice_distribution_id lines for IPV and ERV will join to the correct
-- SLA accounting entries, so we need a combination of information from the ACCRUAL,
-- IPV and ERV AID lines to report the variances correctly
-- =================================================================================
-- =================================================================================
-- Join condensed rows with aid for ACCRUAL AP invoice distribution lines in order
-- to get the matched_uom_lookup_code, quantity_invoiced and unit_price information
-- =================================================================================
(select net_ipv_erv_txns.invoice_id invoice_id,
net_ipv_erv_txns.period_name period_name,
net_ipv_erv_txns.accounting_date accounting_date,
net_ipv_erv_txns.ledger_id,
net_ipv_erv_txns.ipv_code_combination_id ipv_code_combination_id,
net_ipv_erv_txns.erv_code_combination_id erv_code_combination_id,
net_ipv_erv_txns.type type,
net_ipv_erv_txns.accounting_class_code accounting_class_code,
net_ipv_erv_txns.ipv_distribution_line_number,
net_ipv_erv_txns.erv_distribution_line_number,
net_ipv_erv_txns.ipv_invoice_distribution_id,
net_ipv_erv_txns.erv_invoice_distribution_id,
net_ipv_erv_txns.invoice_line_number,
aida.matched_uom_lookup_code,
net_ipv_erv_txns.po_distribution_id,
sum(nvl(aida.quantity_invoiced,0)) quantity_invoiced, -- IPV and ERV lines have no quantity information
net_ipv_erv_txns.related_id,
sum(nvl(aida.unit_price,0)) unit_price, -- IPV and ERV lines have no unit price information
sum(nvl(aida.base_amount,0)) accrual_amount,
sum(net_ipv_erv_txns.ipv_amount) ipv_amount,
sum(net_ipv_erv_txns.erv_amount) erv_amount,
net_ipv_erv_txns.po_line_id,
net_ipv_erv_txns.line_location_id,
net_ipv_erv_txns.rate,
net_ipv_erv_txns.destination_type_code
from
ap_invoice_distributions_all aida,
-- =================================================================================
-- Condense into one row per invoice_id, invoice_line_number, related_id
-- =================================================================================
(select
ipv_erv_txns.invoice_id,
ipv_erv_txns.period_name,
ipv_erv_txns.accounting_date,
ipv_erv_txns.ledger_id,
max(ipv_erv_txns.ipv_code_combination_id) ipv_code_combination_id,
max(ipv_erv_txns.erv_code_combination_id) erv_code_combination_id,
max(ipv_erv_txns.type) type,
max(ipv_erv_txns.accounting_class_code) accounting_class_code,
max(ipv_erv_txns.ipv_distribution_line_number) ipv_distribution_line_number,
max(ipv_erv_txns.erv_distribution_line_number) erv_distribution_line_number,
max(ipv_erv_txns.ipv_invoice_distribution_id) ipv_invoice_distribution_id,
max(ipv_erv_txns.erv_invoice_distribution_id) erv_invoice_distribution_id,
ipv_erv_txns.invoice_line_number,
ipv_erv_txns.matched_uom_lookup_code,
ipv_erv_txns.po_distribution_id,
ipv_erv_txns.quantity_invoiced, -- IPV and ERV lines have no quantity information
ipv_erv_txns.related_id,
ipv_erv_txns.unit_price, -- IPV and ERV lines have no unit price information
sum(ipv_erv_txns.accrual_amount) accrual_amount,
sum(ipv_erv_txns.ipv_amount) ipv_amount,
sum(ipv_erv_txns.erv_amount) erv_amount,
ipv_erv_txns.po_line_id,
ipv_erv_txns.line_location_id,
ipv_erv_txns.rate,
ipv_erv_txns.destination_type_code
-- =================================================================================
-- Get the Invoice Price Variances and Exchange Rate Variances from aid
-- =================================================================================
from
-- =================================================================================
-- Get the IPV transactions from AID and join to the SLA tables to get the ccid
-- Join to PO distributions to get non-EXPENSE entries and foreign key references
-- =================================================================================
(select
aida.invoice_id,
aida.period_name,
xal.ledger_id,
aida.accounting_date,
xal.code_combination_id ipv_code_combination_id,
null erv_code_combination_id,
'IPV-ERV' type,
xal.accounting_class_code,
aida.invoice_line_number ipv_distribution_line_number,
null erv_distribution_line_number,
aida.invoice_distribution_id ipv_invoice_distribution_id,
null erv_invoice_distribution_id,
aida.invoice_line_number,
aida.line_type_lookup_code,
aida.matched_uom_lookup_code,
aida.po_distribution_id,
aida.quantity_invoiced, -- IPV and ERV lines have no quantity information
aida.related_id,
aida.unit_price, -- IPV and ERV lines have no unit price information
0 accrual_amount,
aida.base_amount ipv_amount,
0 erv_amount,
pda.po_line_id,
pda.line_location_id,
pda.rate,
pda.destination_type_code
from
ap_invoice_distributions_all aida,
po_distributions_all pda,
xla_distribution_links xdl,
xla_ae_lines xal
where aida.line_type_lookup_code = 'IPV'
and 2=2 -- p_trx_date_from, p_trx_date_to
and aida.po_distribution_id = pda.po_distribution_id
and pda.destination_type_code <> 'EXPENSE'
-- ========================================================
-- SLA table joins to get the exact account numbers
-- ========================================================
and xal.application_id = 200
and xal.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num
and xal.accounting_class_code = 'IPV'
and xdl.application_id = 200
and xdl.source_distribution_type = 'AP_INV_DIST'
and xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
union all
-- =================================================================================
-- Get the ERV transactions from AID and join to the SLA tables to get the ccid
-- Join to PO distributions to get non-expense entries and foreign key references
-- =================================================================================
select aida.invoice_id,
aida.period_name,
xal.ledger_id,
aida.accounting_date,
null ipv_code_combination_id,
xal.code_combination_id erv_code_combination_id,
'IPV-ERV' type,
xal.accounting_class_code,
null ipv_distribution_line_number,
aida.invoice_line_number erv_distribution_line_number,
null ipv_invoice_distribution_id,
aida.invoice_distribution_id erv_invoice_distribution_id,
aida.invoice_line_number,
aida.line_type_lookup_code,
aida.matched_uom_lookup_code,
aida.po_distribution_id,
aida.quantity_invoiced, -- IPV and ERV lines have no quantity information
aida.related_id,
aida.unit_price, -- IPV and ERV lines have no unit price information
0 accrual_amount,
0 ipv_amount,
aida.base_amount erv_amount,
pda.po_line_id,
pda.line_location_id,
pda.rate,
pda.destination_type_code
from
ap_invoice_distributions_all aida,
po_distributions_all pda,
xla_distribution_links xdl,
xla_ae_lines xal
where aida.line_type_lookup_code = 'ERV'
and 2=2 -- p_trx_date_from, p_trx_date_to
and aida.po_distribution_id = pda.po_distribution_id
and pda.destination_type_code <> 'EXPENSE'
-- ========================================================
-- SLA table joins to get the exact account numbers
-- ========================================================
and xal.application_id = 200
and xal.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num
and xal.accounting_class_code = 'EXCHANGE_RATE_VARIANCE'
and xdl.application_id = 200
and xdl.source_distribution_type = 'AP_INV_DIST'
and xdl.source_distribution_id_num_1 = aida.invoice_distribution_id) ipv_erv_txns
group by
ipv_erv_txns.invoice_id,
ipv_erv_txns.period_name,
ipv_erv_txns.ledger_id,
ipv_erv_txns.accounting_date,
ipv_erv_txns.invoice_line_number,
ipv_erv_txns.matched_uom_lookup_code,
ipv_erv_txns.po_distribution_id,
ipv_erv_txns.quantity_invoiced,
ipv_erv_txns.related_id,
ipv_erv_txns.unit_price,
ipv_erv_txns.po_line_id,
ipv_erv_txns.line_location_id,
ipv_erv_txns.rate,
ipv_erv_txns.destination_type_code) net_ipv_erv_txns
where aida.line_type_lookup_code = 'ACCRUAL'
and aida.related_id = net_ipv_erv_txns.related_id
group by
net_ipv_erv_txns.invoice_id,
net_ipv_erv_txns.period_name,
net_ipv_erv_txns.ledger_id,
net_ipv_erv_txns.accounting_date,
net_ipv_erv_txns.ipv_code_combination_id,
net_ipv_erv_txns.erv_code_combination_id,
net_ipv_erv_txns.type,
net_ipv_erv_txns.accounting_class_code,
net_ipv_erv_txns.ipv_distribution_line_number,
net_ipv_erv_txns.erv_distribution_line_number,
net_ipv_erv_txns.ipv_invoice_distribution_id,
net_ipv_erv_txns.erv_invoice_distribution_id,
net_ipv_erv_txns.invoice_line_number,
aida.matched_uom_lookup_code,
net_ipv_erv_txns.po_distribution_id,
net_ipv_erv_txns.related_id,
net_ipv_erv_txns.po_line_id,
net_ipv_erv_txns.line_location_id,
net_ipv_erv_txns.rate,
net_ipv_erv_txns.destination_type_code) ap_txns
-- ===================================================================
-- Join the condensed and summarized AP IPV and ERV transactions
-- with the rest of the information needed to report IPV and ERV
-- ===================================================================
where aia.invoice_id = ap_txns.invoice_id
and aia.org_id = to_number(hoi.org_information3)
and aia.org_id = pha.org_id
and pla.po_line_id = ap_txns.po_line_id
and pha.po_header_id = pla.po_header_id
and plla.line_location_id = ap_txns.line_location_id
and plla.po_line_id = pla.po_line_id
and plla.po_release_id = pra.po_release_id(+)
and pv.vendor_id = pha.vendor_id
and msiv.inventory_item_id = pla.item_id
and msiv.inventory_item_id = ucr.inventory_item_id
and msiv.organization_id = ucr.organization_id
and ucr.unit_of_measure = pla.unit_meas_lookup_code
and mp.organization_id = msiv.organization_id
and msiv.organization_id = plla.ship_to_organization_id
and gcc1.code_combination_id (+) = ap_txns.ipv_code_combination_id
and gcc2.code_combination_id (+) = ap_txns.erv_code_combination_id
and pha.agent_id = he.employee_id
and pl.lookup_type = 'DESTINATION TYPE'
and pl.lookup_code = ap_txns.destination_type_code
and flv.lookup_type = 'XLA_ACCOUNTING_CLASS'
and flv.lookup_code = ap_txns.accounting_class_code
and flv.language = userenv('lang')
-- ===================================================================
-- using the base tables to avoid the performance issues
-- with org_organization_definitions and hr_operating_units
-- ===================================================================
and hoi.org_information_context = 'Accounting Information'
and hoi.organization_id = mp.organization_id
and hoi.organization_id = haou.organization_id -- this gets the organization name
and haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id
and gl.ledger_id = ap_txns.ledger_id
and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
and haou2.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
and 1=1 -- p_operating_unit, p_ledger
and 3=3 -- p_vendor_name
-- ===================================================================
-- Revision for version 1.8
-- Include A/P Accrual Write-Offs on this report
-- ===================================================================
union all
select gl.name Ledger,
haou2.name Operating_Unit,
mp.organization_code Org_Code,
wo_txns.period_name Period_Name,
&segment_columns2
wo_txns.write_off_type Type,
flv.meaning Accounting_Class_Code,
wo_txns.vendor_name Supplier,
wo_txns.full_name Buyer,
wo_txns.item_number Item_Number,
wo_txns.item_description Item_Description,
wo_txns.user_item_type,
&category_columns2
pl.displayed_field Destination_Type,
wo_txns.po_num PO_Number,
wo_txns.po_line_num PO_Line,
wo_txns.release_num PO_Release,
wo_txns.invoice_num Invoice_Number,
decode(wo_txns.ipv_distribution_line_number, 0, null, wo_txns.ipv_distribution_line_number) IPV_Invoice_Line,
decode(wo_txns.erv_distribution_line_number, 0, null, wo_txns.erv_distribution_line_number) ERV_Invoice_Line,
wo_txns.invoice_date Invoice_Date,
wo_txns.accounting_date Accounting_Date,
wo_txns.unit_meas_lookup_code PO_UOM,
wo_txns.quantity_invoiced * wo_txns.conversion_rate Invoice_Quantity,
nvl(wo_txns.currency_code, gl.currency_code) PO_Currency_Code,
wo_txns.price_override PO_Unit_Price,
nvl(wo_txns.rate,1) PO_Exchange_Rate,
gl.currency_code GL_Currency_Code,
round(nvl(wo_txns.rate,1) * wo_txns.price_override,6) Converted_PO_Unit_Price,
nvl(wo_txns.rate,1) * wo_txns.price_override * (wo_txns.quantity_invoiced * wo_txns.conversion_rate) Total_PO_Amount,
nvl(wo_txns.invoice_currency_code, gl.currency_code) Invoice_Currency_Code,
wo_txns.unit_price Payables_Unit_Cost,
nvl(wo_txns.exchange_rate,1) Payables_Exchange_Rate,
gl.currency_code GL_Currency_Code,
round(nvl(wo_txns.unit_price,0)*nvl(wo_txns.exchange_rate,1),6) Converted_Invoice_Unit_Cost,
0 Unit_Cost_Variance,
round(nvl(wo_txns.unit_price,0)*nvl(wo_txns.exchange_rate,1)*wo_txns.quantity_invoiced*wo_txns.conversion_rate,2) Total_Invoice_Amount,
round(nvl(wo_txns.unit_price,0)*nvl(wo_txns.exchange_rate,1)*wo_txns.quantity_invoiced*wo_txns.conversion_rate -
nvl(wo_txns.rate,1) * wo_txns.price_override * wo_txns.quantity_invoiced * wo_txns.conversion_rate,2) Total_Calculated_IPV,
wo_txns.ipv_amount General_Ledger_IPV_Amount,
wo_txns.erv_amount General_Ledger_ERV_Amount,
round((
nvl(wo_txns.unit_price,0)*nvl(wo_txns.exchange_rate,1) * wo_txns.quantity_invoiced * wo_txns.conversion_rate -
(nvl(wo_txns.rate,1) * wo_txns.price_override * (wo_txns.quantity_invoiced * wo_txns.conversion_rate)
)
)
/ abs(decode(nvl(wo_txns.rate,1) * wo_txns.price_override * wo_txns.quantity_invoiced * wo_txns.conversion_rate,0,1,
nvl(wo_txns.rate,1) * wo_txns.price_override * wo_txns.quantity_invoiced * wo_txns.conversion_rate)) * 100,1) Percent
from
mtl_parameters mp,
hr_organization_information hoi,
hr_all_organization_units_vl haou, -- inv_organization_id
hr_all_organization_units_vl haou2, -- operating unit
gl_ledgers gl,
gl_code_combinations gcc1,
po_lookup_codes pl,
fnd_lookup_values flv,
-- =================================================================================
-- Use union all selects to get the following Accrual Write-Offs: rows that have
-- and inventory_transaction_id (from mtl_material_transactions), rows that have
-- a PO distribution ID (may be from AP, PO or Receiving), or rows which have a
-- invoice_distribution_id (from A/P which is not matched to a PO line).
-- =================================================================================
-- =================================================================================
-- 1.0 union all
-- Get the Accrual Write-Offs which join to an Inventory Transaction_Id (mmt and mta)
-- =================================================================================
(select
null vendor_name,
fu.user_name full_name,
xal.ledger_id,
msiv.organization_id,
xah.period_name,
msiv.concatenated_segments item_number,
msiv.inventory_item_id,
msiv.description item_description,
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type,
cwo.destination_type_code,
xal.code_combination_id,
cwo.offset_account_id,
cwo.accrual_account_id,
cwo.write_off_id,
'INV WO' write_off_type,
xal.accounting_class_code,
null po_num,
null po_line_num,
null release_num,
null invoice_num,
-- Set to value of 0 so the union all works with no errors
0 ipv_distribution_line_number,
0 erv_distribution_line_number,
ml.meaning accounting_line_type,
mmt.transaction_id,
null invoice_date,
cwo.transaction_date accounting_date,
msiv.primary_unit_of_measure unit_meas_lookup_code,
mta.primary_quantity quantity_invoiced,
nvl(cwo.currency_conversion_rate,1) conversion_rate,
cwo.currency_code,
mmt.actual_cost price_override,
nvl(mmt.currency_conversion_rate,1) rate,
null invoice_currency_code,
0 unit_price,
1 exchange_rate,
sum(nvl(xal.accounted_dr,0) - nvl(xal.accounted_cr,0)) ipv_amount,
sum(0) erv_amount
from
cst_write_offs cwo,
mtl_material_transactions mmt,
mtl_transaction_accounts mta,
mtl_system_items_vl msiv,
mtl_uom_conversions_view ucr,
mfg_lookups ml,
fnd_user fu,
xla_distribution_links xdl,
xla_ae_headers xah,
xla_ae_lines xal
where cwo.transaction_date >= :p_trx_from -- p_trx_date_from
and cwo.transaction_date < :p_trx_to -- p_trx_date_to
and cwo.inventory_transaction_id = mmt.transaction_id
and mmt.transaction_id = mta.transaction_id
and mta.reference_account = cwo.accrual_account_id
and mmt.created_by = fu.user_id
and msiv.inventory_item_id = mmt.inventory_item_id
and msiv.organization_id = mta.organization_id
and msiv.inventory_item_id = ucr.inventory_item_id
and msiv.organization_id = ucr.organization_id
and ml.lookup_type = 'CST_ACCOUNTING_LINE_TYPE'
and ml.lookup_code = mta.accounting_line_type
-- ========================================================
-- SLA table joins to get the exact account numbers
-- ========================================================
and xah.ae_header_id = xal.ae_header_id
and xah.application_id = xal.application_id
and xal.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num
and xal.application_id = xdl.application_id
and xdl.application_id = 707
and xdl.source_distribution_type = 'CST_WRITE_OFFS'
and xdl.source_distribution_id_num_1 = cwo.write_off_id
group by
null, -- vendor_name
fu.user_name, -- full_name
xal.ledger_id,
msiv.organization_id,
xah.period_name,
msiv.concatenated_segments, -- item_number
msiv.inventory_item_id,
msiv.description, -- item_description
msiv.item_type,
cwo.destination_type_code, -- destination_type_code
xal.code_combination_id,
cwo.offset_account_id,
cwo.accrual_account_id,
cwo.write_off_id,
'INV WO', -- Accrual Write-Off Type
xal.accounting_class_code,
null, -- po_num
null, -- po_line_num
null, -- release_num
null, -- invoice_num
-- Set to value of 0 so the union all works with no errors
0, -- ipv_distribution_line_number
0, -- erv_distribution_line_number
ml.meaning, -- accounting_line_type
mmt.transaction_id,
null, -- invoice_date
cwo.transaction_date, -- accounting_date,
msiv.primary_unit_of_measure, -- unit_meas_lookup_code
mta.primary_quantity, -- quantity_invoiced
nvl(cwo.currency_conversion_rate,1), -- conversion_rate
cwo.currency_code,
mmt.actual_cost, -- price_override
nvl(mmt.currency_conversion_rate,1), -- rate
null, -- invoice_currency_code
0, -- unit_price
1 -- exchange_rate
-- =================================================================================
-- 2.0 union all
-- Get the Accrual Write-Offs which join to a purchase order line and which join
-- to an item number (cannot have more than one outer-join per select). This
-- select will get both INVENTORY and EXPENSE destination type codes.
-- =================================================================================
union all
select pv.vendor_name vendor_name,
he.full_name full_name,
xal.ledger_id,
msiv.organization_id,
xah.period_name,
msiv.concatenated_segments item_number,
msiv.inventory_item_id,
msiv.description item_description,
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type,
cwo.destination_type_code destination_type_code,
xal.code_combination_id,
cwo.offset_account_id,
cwo.accrual_account_id,
cwo.write_off_id,
'AP-PO WO' write_off_type,
xal.accounting_class_code,
pha.segment1 po_num,
to_char(pla.line_num) po_line_num,
pra.release_num release_num,
null invoice_num,
-- Set to value of 0 so the union all works with no errors
0 ipv_distribution_line_number,
0 erv_distribution_line_number,
null accounting_line_type,
null transaction_id,
null invoice_date,
cwo.transaction_date accounting_date,
pla.unit_meas_lookup_code,
0 quantity_invoiced,
nvl(cwo.CURRENCY_conversion_rate,1) conversion_rate,
cwo.currency_code,
plla.price_override price_override,
nvl(pda.rate, pha.rate) rate,
null invoice_currency_code,
0 unit_price,
1 exchange_rate,
sum(nvl(xal.accounted_dr,0) - nvl(xal.accounted_cr,0)) ipv_amount,
sum(0) erv_amount
from
cst_write_offs cwo,
po_distributions_all pda,
po_vendors pv,
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
po_releases_all pra,
mtl_system_items_vl msiv,
mtl_uom_conversions_view ucr,
hr_employees he,
xla_distribution_links xdl,
xla_ae_headers xah,
xla_ae_lines xal
where cwo.transaction_date >= :p_trx_from -- p_trx_date_from
and cwo.transaction_date < :p_trx_to -- p_trx_date_to
and cwo.po_distribution_id = pda.po_distribution_id
and pda.line_location_id = plla.line_location_id
and pda.po_header_id = pha.po_header_id
and pda.po_line_id = pla.po_line_id
and plla.po_release_id = pra.po_release_id (+)
and pv.vendor_id = pha.vendor_id
and pha.agent_id = he.employee_id
and msiv.inventory_item_id = pla.item_id
and msiv.inventory_item_id = ucr.inventory_item_id
and msiv.organization_id = ucr.organization_id
and ucr.unit_of_measure = pla.unit_meas_lookup_code
and msiv.organization_id = plla.ship_to_organization_id
-- ========================================================
-- SLA table joins to get the exact account numbers
-- ========================================================
and xah.ae_header_id = xal.ae_header_id
and xah.application_id = xal.application_id
and xal.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num
and xal.application_id = xdl.application_id
and xdl.source_distribution_type = 'CST_WRITE_OFFS'
and xdl.application_id = 707
and xdl.source_distribution_id_num_1 = cwo.write_off_id
and 3=3 -- p_vendor_name
group by
pv.vendor_name, -- vendor_name
he.full_name, -- full_name
xal.ledger_id,
msiv.organization_id,
xah.period_name,
msiv.concatenated_segments, -- item_number
msiv.inventory_item_id,
msiv.description, -- item_description
msiv.item_type,
cwo.destination_type_code, -- destination_type_code
xal.code_combination_id,
cwo.offset_account_id,
cwo.accrual_account_id,
cwo.write_off_id,
'AP-PO WO', -- Accrual Write-Off Type
xal.accounting_class_code,
pha.segment1, -- po_num
to_char(pla.line_num), -- po_line_num
pra.release_num, -- release_num
null, -- invoice_num
-- Set to value of 0 so the union all works with no errors
0, -- ipv_distribution_line_number
0, -- erv_distribution_line_number
null, -- accounting_line_type
null, -- transaction_id,
null, -- invoice_date
cwo.transaction_date, -- accounting_date,
pla.unit_meas_lookup_code,
0, -- quantity_invoiced
nvl(cwo.currency_conversion_rate,1), -- conversion_rate
cwo.currency_code,
plla.price_override, -- price_override
nvl(pda.rate, pha.rate), -- rate
null, -- invoice_currency_code
0, -- unit_price
1 -- exchange_rate
-- =================================================================================
-- 3.0 union all
-- Get the Accrual Write-Offs which join to a purchase order line and which do not
-- join or reference an item number (cannot have more than one outer-join per select)
-- This select will only get EXPENSE destination type codes.
-- =================================================================================
union all
select pv.vendor_name vendor_name,
he.full_name full_name,
xal.ledger_id,
plla.ship_to_organization_id organization_id,
xah.period_name,
null item_number,
-- Revision for version 1.11
null inventory_item_id,
pla.item_description item_description,
null user_item_type,
cwo.destination_type_code destination_type_code,
xal.code_combination_id,
cwo.offset_account_id,
cwo.accrual_account_id,
cwo.write_off_id,
'AP-PO WO' write_off_type,
xal.accounting_class_code,
pha.segment1 po_num,
to_char(pla.line_num) po_line_num,
pra.release_num release_num,
null invoice_num,
-- Set to value of 0 so the union all works with no errors
0 ipv_distribution_line_number,
0 erv_distribution_line_number,
null accounting_line_type,
null transaction_id,
null invoice_date,
cwo.transaction_date accounting_date,
pla.unit_meas_lookup_code,
0 quantity_invoiced,
nvl(cwo.CURRENCY_conversion_rate,1) conversion_rate,
cwo.currency_code,
plla.price_override price_override,
nvl(pda.rate, pha.rate) rate,
null invoice_currency_code,
0 unit_price,
1 exchange_rate,
sum(nvl(xal.accounted_dr,0) - nvl(xal.accounted_cr,0)) ipv_amount,
sum(0) erv_amount
from
cst_write_offs cwo,
po_distributions_all pda,
po_vendors pv,
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
po_releases_all pra,
hr_employees he,
xla_distribution_links xdl,
xla_ae_headers xah,
xla_ae_lines xal
where cwo.transaction_date >= :p_trx_from -- p_trx_date_from
and cwo.transaction_date < :p_trx_to -- p_trx_date_to
-- Only EXPENSE destination type codes have no items
and cwo.destination_type_code = 'EXPENSE'
and cwo.po_distribution_id = pda.po_distribution_id
and pda.line_location_id = plla.line_location_id
and pda.po_header_id = pha.po_header_id
and pda.po_line_id = pla.po_line_id
-- EXPENSE destination type codes may or may not reference an item_id
and pla.item_id is null
and plla.po_release_id = pra.po_release_id (+)
and pv.vendor_id = pha.vendor_id
and pha.agent_id = he.employee_id
-- ========================================================
-- SLA table joins to get the exact account numbers
-- ========================================================
and xah.ae_header_id = xal.ae_header_id
and xah.application_id = xal.application_id
and xal.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num
and xal.application_id = xdl.application_id
-- and xal.accounting_class_code = 'WRITE_OFF_VARIANCE'
and xdl.application_id = 707
and xdl.source_distribution_type = 'CST_WRITE_OFFS'
and xdl.source_distribution_id_num_1 = cwo.write_off_id
and 3=3 -- p_vendor_name
group by
pv.vendor_name, -- vendor_name
he.full_name, -- full_name
xal.ledger_id,
plla.ship_to_organization_id, -- organization_id
xah.period_name,
null, -- item_number
null, -- inventory_item_id
pla.item_description, -- item_description
null, -- user_item_type
cwo.destination_type_code, -- destination_type_code
xal.code_combination_id,
cwo.offset_account_id,
cwo.accrual_account_id,
cwo.write_off_id,
'AP-PO WO', -- Accrual Write-Off Type
xal.accounting_class_code,
pha.segment1, -- po_num
to_char(pla.line_num), -- po_line_num
pra.release_num, -- release_num
null, -- invoice_num
-- Set to value of 0 so the union all works with no errors
0, -- ipv_distribution_line_number
0, -- erv_distribution_line_number
null, -- accounting_line_type
null, -- transaction_id,
null, -- invoice_date
cwo.transaction_date, -- accounting_date,
pla.unit_meas_lookup_code, -- unit_meas_lookup_code
0, -- quantity_invoiced
nvl(cwo.currency_conversion_rate,1), -- conversion_rate
cwo.currency_code,
plla.price_override, -- price_override
nvl(pda.rate, pha.rate), -- rate
null, -- invoice_currency_code
0, -- unit_price
1 -- exchange_rate
-- =================================================================================
-- 4.0 union all
-- Get the Accrual Write-Offs for unmatched Payables entries (AP No Match).
-- =================================================================================
union all
select pv.vendor_name vendor_name,
fu.user_name full_name,
xal.ledger_id,
osp.master_organization_id organization_id,
xah.period_name,
null item_number,
null inventory_item_id,
aida.description item_description,
null user_item_type,
cwo.destination_type_code destination_type_code,
xal.code_combination_id,
cwo.offset_account_id,
cwo.accrual_account_id,
cwo.write_off_id,
'AP No PO WO' write_off_type,
xal.accounting_class_code,
null po_num,
null po_line_num,
null release_num,
null invoice_num,
aida.distribution_line_number ipv_distribution_line_number,
-- Set to value of 0 so the union all works with no errors
0 erv_distribution_line_number,
null accounting_line_type,
null transaction_id,
aia.invoice_date invoice_date,
cwo.transaction_date accounting_date,
nvl(aida.matched_uom_lookup_code,aia.invoice_currency_code) unit_meas_lookup_code,
nvl(aida.quantity_invoiced,0) quantity_invoiced,
nvl(cwo.currency_conversion_rate,1) conversion_rate,
cwo.currency_code,
0 price_override, -- po unit price
1 rate, -- po exchange rate
aia.invoice_currency_code invoice_currency_code,
aida.unit_price unit_price,
nvl(aida.exchange_rate,1) exchange_rate,
sum(nvl(xal.accounted_dr,0) - nvl(xal.accounted_cr,0)) ipv_amount,
sum(0) erv_amount
from
cst_write_offs cwo,
ap_invoices_all aia,
ap_invoice_distributions_all aida,
po_vendors pv,
-- oe_system_parameters_all holds the validation inventory org
-- for order management, A/R and A/P
oe_system_parameters_all osp,
fnd_user fu,
xla_distribution_links xdl,
xla_ae_headers xah,
xla_ae_lines xal
where cwo.transaction_date >= :p_trx_from -- p_trx_date_from
and cwo.transaction_date < :p_trx_to -- p_trx_date_to
and cwo.invoice_distribution_id = aida.invoice_distribution_id
and cwo.invoice_distribution_id is not null
and aia.invoice_id = aida.invoice_id
and pv.vendor_id = aia.vendor_id
and aia.created_by = fu.user_id
and osp.org_id = aida.org_id
-- ========================================================
-- SLA table joins to get the exact account numbers
-- ========================================================
and xah.ae_header_id = xal.ae_header_id
and xah.application_id = xal.application_id
and xal.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num
and xah.application_id = xdl.application_id
and xdl.source_distribution_type = 'CST_WRITE_OFFS'
and xdl.application_id = 707
and xdl.source_distribution_id_num_1 = cwo.write_off_id
and 2=2 -- p_trx_date_from, p_trx_date_to
and 3=3 -- p_vendor_name
group by
pv.vendor_name,
fu.user_name,
xal.ledger_id,
osp.master_organization_id,
xah.period_name,
null, -- item_number
aida.description, -- item_description
null, -- user_item_type
cwo.destination_type_code,
xal.code_combination_id,
cwo.offset_account_id,
cwo.accrual_account_id,
cwo.write_off_id,
'AP No PO WO', -- write_off_type
xal.accounting_class_code,
null, -- po_num
null, -- po_line_num
null, -- release_num
null, -- invoice_num
aida.distribution_line_number, -- ipv_distribution_line_number
-- Set to value of 0 so the union all works with no errors
0, -- erv_distribution_line_number
null, -- accounting_line_type
null, -- transaction_id
aia.invoice_date,
cwo.transaction_date,
nvl(aida.matched_uom_lookup_code,aia.invoice_currency_code), -- unit_meas_lookup_code
nvl(aida.quantity_invoiced,0), -- quantity_invoiced
nvl(cwo.currency_conversion_rate,1), -- conversion_rate
cwo.currency_code,
0, -- po unit price or price_override
1, -- po exchange rate
aia.invoice_currency_code,
aida.unit_price, -- invoice unit_price
nvl(aida.exchange_rate,1) -- invoice exchange_rate
) wo_txns -- ipv_amount
where mp.organization_id = wo_txns.organization_id
and gcc1.code_combination_id (+) = wo_txns.code_combination_id
and pl.lookup_type = 'DESTINATION TYPE'
and pl.lookup_code = wo_txns.destination_type_code
and flv.lookup_type = 'XLA_ACCOUNTING_CLASS'
and flv.lookup_code = wo_txns.accounting_class_code
and flv.language = userenv('lang')
-- ===================================================================
-- using the base tables to avoid the performance issues
-- with org_organization_definitions and hr_operating_units
-- ===================================================================
and hoi.org_information_context = 'Accounting Information'
and hoi.organization_id = mp.organization_id
and hoi.organization_id = haou.organization_id -- this gets the organization name
and haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id
and gl.ledger_id = wo_txns.ledger_id
and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
and haou2.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
and 1=1 -- p_operating_unit, p_ledger
-- ===================================================================
order by 1,2,3,4,5,6,7,8,9,10,11,14,15,16,17,18,19,20,23,26,27,28,29,30 |
