CAC Invoice Price Variance

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report to display the Invoice Price Variances (IPV), Exchange Rate Variances (ERV) and A/P Accrual Write-Off Variances for an entered date range. IPV is the difference between the PO unit price and the invoice unit cost times the quantity invoiced. ERV is the difference between the purchase order exchange rate and the exchange rate used by the A/P invoice. For a given invoice line, both the IPV ...  Report to display the Invoice Price Variances (IPV), Exchange Rate Variances (ERV) and A/P Accrual Write-Off Variances for an entered date range. IPV is the difference between the PO unit price and the invoice unit cost times the quantity invoiced. ERV is the difference between the purchase order exchange rate and the exchange rate used by the A/P invoice. For a given invoice line, both the IPV and ERV amounts will be shown on the same row, in separate columns. These entries have the Type "IPV-ERV". The A/P Accrual Write-Off Variances appear as separate rows with the Type "INV WO" for invoice write-off amounts. The A/P Accrual Write-Off Variances typically use the IPV account so for completeness, are also displayed on this report.

/* +=============================================================================+
-- | Copyright 2006-2021 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- +=============================================================================+
-- |
-- | Original Author: Douglas Volz ([email protected])
-- |
-- | Program Name: xxx_ipv_rept.sql
-- |
-- | Parameters:
-- | p_trx_date_from -- starting accounting date for the payables invoices
-- | p_trx_date_to -- ending accounting date for the payables invoices
-- | p_category_set1 -- The first item category set to report, typically the
-- | Cost or Product Line Category Set
-- | p_category_set2 -- The second item category set to report, typically the
-- | Inventory Category Set
-- | p_vendor_name -- Vendor you want to report (optional)
-- | p_operating_unit -- Operating Unit you wish to report, leave blank for all
-- | operating units (optional)
-- | p_ledger -- general ledger you wish to report, leave blank for all
-- | ledgers (optional)
-- |
-- | Description:
-- | Report to display the invoice price variances for an entered date range.
-- | IPV is the difference between the PO unit price and the invoice unit
-- | cost times the quantity invoiced.
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 01 Jun 2006 Douglas Volz Initial Coding based on XXX_IPV_REPT.sql
-- | 1.1 17 Apr 2010 Douglas Volz Modified for Release 12 for Client, the IPV
-- | columns are now null as a new line type
-- | exists for IPV in AID, and the amount columns
-- | only have the PO amounts, not the total invoice
-- | amount.
-- | 1.11 22 May 2017 Douglas Volz Added product type, business code, product family and
-- | and product line inventory categories
-- | 1.12 20 Jul 2019 Douglas Volz Removed all item categories except COSTING.
-- | 1.13 18 Feb 2021 Douglas Volz Changed to multi-org views for items, categories
-- | and HR organizations.
-- | 1.14 12 Apr 2021 Douglas Volz Removed redundant joins and tables to improve performance.
-- +=============================================================================+*/
   more
Run CAC Invoice Price Variance and other Oracle EBS reports with Blitz Report™ on our demo environment
select gl.name Ledger,
 haou2.name Operating_Unit,
 mp.organization_code Org_Code,
 ap_txns.period_name Period_Name,
&segment_columns
 -- Revision for version 1.8
 ap_txns.type Type,
 flv.meaning Accounting_Class_Code,
 -- End revision for version 1.8
 pov.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,
 -- Revision for version 1.11 and 1.12
&category_columns
 -- End revision for version 1.11
 pl.displayed_field Destination_Type,
 poh.segment1 PO_Number,
 to_char(pol.line_num) PO_Line,
 pr.release_num PO_Release,
 api.invoice_num Invoice_Number,
 -- Revision for version 1.8
 -- Decode to enable a null value for the additional union all for A/P Accrual Write-Offs
 -- ap_txns.ipv_distribution_line_number IPV_Inv._Line,
 -- ap_txns.erv_distribution_line_number ERV_Inv._Line,
 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,
 -- End revision for version 1.8
 api.invoice_date Invoice_Date,
 ap_txns.accounting_date Accounting_Date,
 pol.unit_meas_lookup_code PO_UOM,
 ap_txns.quantity_invoiced * ucr.conversion_rate  Invoice_Quantity,
 nvl(poh.currency_code, gl.currency_code) PO_Currency_Code,
 pll.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) * pll.price_override,6) Converted_PO_Unit_Price,
 nvl(ap_txns.rate,1) * pll.price_override * (ap_txns.quantity_invoiced * ucr.conversion_rate) Total_PO_Amount,
 nvl(api.invoice_currency_code, gl.currency_code) Invoice_Currency_Code,
 ap_txns.unit_price Payables_Unit_Cost,
 nvl(api.exchange_rate,1) Payables_Exchange_Rate,
 gl.currency_code GL_Currency_Code,
 round(decode(api.exchange_rate,null,nvl(ap_txns.unit_price,0),
                nvl(ap_txns.unit_price,0)*api.exchange_rate),6) Converted_Invoice_Unit_Cost,
 round(decode(api.exchange_rate,null,nvl(ap_txns.unit_price,0),
                nvl(ap_txns.unit_price,0)*api.exchange_rate),6)  -
                  round(nvl(ap_txns.rate,1) * pll.price_override,6) Unit_Cost_Variance,
 round(decode(api.exchange_rate,null,nvl(ap_txns.unit_price,0),
                    nvl(ap_txns.unit_price,0)*api.exchange_rate) * ap_txns.quantity_invoiced * ucr.conversion_rate,2) Total_Invoice_Amount,
    round(  (decode(api.exchange_rate,null,nvl(ap_txns.unit_price,0),
                    nvl(ap_txns.unit_price,0)*api.exchange_rate) * ap_txns.quantity_invoiced * ucr.conversion_rate
                   ) -
     (nvl(ap_txns.rate,1) * pll.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((
  (decode(api.exchange_rate,null,nvl(ap_txns.unit_price,0),
                nvl(ap_txns.unit_price,0)*api.exchange_rate) * ap_txns.quantity_invoiced * ucr.conversion_rate
  ) -
  (nvl(ap_txns.rate,1) * pll.price_override * (ap_txns.quantity_invoiced * ucr.conversion_rate)
  )
 )
       /  abs(decode(nvl(ap_txns.rate,1) * pll.price_override * (ap_txns.quantity_invoiced * ucr.conversion_rate),0,1,nvl(ap_txns.rate,1)
         * pll.price_override * (ap_txns.quantity_invoiced * ucr.conversion_rate))) * 100,1) Percent
from ap_invoices_all                     api,
 ap_system_parameters_all            asp,
 po_vendors                          pov,
 po_headers_all                      poh,
 po_lines_all                        pol,
 po_line_locations_all               pll,
 po_releases_all                     pr,
 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,
 -- Revision for version 1.8
 fnd_lookup_values                   flv,
 -- End revision for version 1.8
 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.ipv_code_combination_id ipv_code_combination_id,
  net_ipv_erv_txns.erv_code_combination_id erv_code_combination_id,
  -- Revision for version 1.8
  net_ipv_erv_txns.type type,
  net_ipv_erv_txns.accounting_class_code accounting_class_code,
  -- End Revision for version 1.8
  net_ipv_erv_txns.ipv_distribution_line_number ipv_distribution_line_number,
  net_ipv_erv_txns.erv_distribution_line_number erv_distribution_line_number,
  net_ipv_erv_txns.ipv_invoice_distribution_id ipv_invoice_distribution_id,
  net_ipv_erv_txns.erv_invoice_distribution_id erv_invoice_distribution_id,
  net_ipv_erv_txns.invoice_line_number invoice_line_number,
  aid.matched_uom_lookup_code matched_uom_lookup_code,
  net_ipv_erv_txns.po_distribution_id po_distribution_id,
  sum(nvl(aid.quantity_invoiced,0)) quantity_invoiced, -- IPV and ERV lines have no quantity information
  net_ipv_erv_txns.related_id related_id,
  sum(nvl(aid.unit_price,0)) unit_price, -- IPV and ERV lines have no unit price information
  sum(nvl(aid.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 po_line_id,
  net_ipv_erv_txns.line_location_id line_location_id,
  net_ipv_erv_txns.rate rate,
  net_ipv_erv_txns.destination_type_code destination_type_code
 from ap.ap_invoice_distributions_all aid,
  -- =================================================================================
  -- Condense into one row per invoice_id, invoice_line_number, related_id
  -- =================================================================================
  (select ipv_erv_txns.invoice_id invoice_id,
   ipv_erv_txns.period_name period_name,
   ipv_erv_txns.accounting_date accounting_date,
   sum(ipv_erv_txns.ipv_code_combination_id) ipv_code_combination_id,
   sum(ipv_erv_txns.erv_code_combination_id) erv_code_combination_id,
   -- Revision for version 1.8
   max(ipv_erv_txns.type) type,
   max(ipv_erv_txns.accounting_class_code) accounting_class_code,
   -- End Revision for version 1.8
   sum(ipv_erv_txns.ipv_distribution_line_number) ipv_distribution_line_number,
   sum(ipv_erv_txns.erv_distribution_line_number) erv_distribution_line_number,
   sum(ipv_erv_txns.ipv_invoice_distribution_id) ipv_invoice_distribution_id,
   sum(ipv_erv_txns.erv_invoice_distribution_id) erv_invoice_distribution_id,
   ipv_erv_txns.invoice_line_number invoice_line_number,
   ipv_erv_txns.matched_uom_lookup_code matched_uom_lookup_code,
   ipv_erv_txns.po_distribution_id po_distribution_id,
   ipv_erv_txns.quantity_invoiced quantity_invoiced, -- IPV and ERV lines have no quantity information
   ipv_erv_txns.related_id related_id,
   ipv_erv_txns.unit_price 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 po_line_id,
   ipv_erv_txns.line_location_id line_location_id,
   ipv_erv_txns.rate rate,
   ipv_erv_txns.destination_type_code 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 aid.invoice_id invoice_id,
    aid.period_name period_name,
    aid.accounting_date accounting_date,
    al.code_combination_id ipv_code_combination_id,
    null erv_code_combination_id,
    -- Revision for version 1.8
    'IPV-ERV' type,
    al.accounting_class_code accounting_class_code,
    -- End revision for version 1.8
    -- Fix for version 1.9
    -- aid.distribution_line_number ipv_distribution_line_number,
    aid.invoice_line_number ipv_distribution_line_number,
    -- End fix for version 1.9
    null erv_distribution_line_number,
    aid.invoice_distribution_id ipv_invoice_distribution_id,
    null erv_invoice_distribution_id,
    aid.invoice_line_number invoice_line_number,
    aid.line_type_lookup_code line_type_lookup_code,
    aid.matched_uom_lookup_code matched_uom_lookup_code,
    aid.po_distribution_id po_distribution_id,
    aid.quantity_invoiced quantity_invoiced, -- IPV and ERV lines have no quantity information
    aid.related_id related_id,
    aid.unit_price unit_price, -- IPV and ERV lines have no unit price information
    0 accrual_amount,
    aid.base_amount ipv_amount,
    0 erv_amount,
    pod.po_line_id po_line_id,
    pod.line_location_id line_location_id,
    pod.rate rate,
    pod.destination_type_code destination_type_code
    from ap_invoice_distributions_all        aid,
    po_distributions_all                pod,
    -- Revision 1.14, remove tables to increase performance
    -- xla_transaction_entities            ent,
    -- xla_events                          xe,
    -- End revision for version 1.14
    xla_distribution_links              xdl,
    xla_ae_headers                      ah,
    xla_ae_lines                        al
    where aid.line_type_lookup_code    = 'IPV'
    and 2=2                          -- p_trx_date_from, p_trx_date_to
    and aid.po_distribution_id       = pod.po_distribution_id
    and pod.destination_type_code   <> 'EXPENSE'
    -- ========================================================
    -- SLA table joins to get the exact account numbers
    -- ========================================================
    -- Revision for version 1.14, performance improvements
    -- and ent.entity_code              = 'AP_INVOICES'
    -- and ent.application_id           = 200
    -- and xe.application_id            = ent.application_id
    -- and xe.event_id                  = xdl.event_id
    -- and ah.event_id                  = xe.event_id
    -- and ah.entity_id                 = ent.entity_id
    -- and ah.ledger_id                 = ent.ledger_id
    -- and al.application_id            = ent.application_id
    -- and xdl.application_id           = ent.application_id
    -- End revisions for version 1.14
    and ah.application_id            = al.application_id
    and ah.application_id            = 200
    and ah.ae_header_id              = al.ae_header_id
    and al.ledger_id                 = ah.ledger_id
    and al.ae_header_id              = xdl.ae_header_id
    and al.ae_line_num               = xdl.ae_line_num
    and al.accounting_class_code     = 'IPV'
    and xdl.application_id           = 200
    and xdl.source_distribution_type = 'AP_INV_DIST'
    and xdl.source_distribution_id_num_1 = aid.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 aid.invoice_id invoice_id,
    aid.period_name period_name,
    aid.accounting_date accounting_date,
    null ipv_code_combination_id,
    al.code_combination_id erv_code_combination_id,
    -- Revision for version 1.8
    'IPV-ERV' type,
    al.accounting_class_code accounting_class_code,
    -- End revision for version 1.8
    null ipv_distribution_line_number,
    -- Fix for version 1.9
    aid.invoice_line_number erv_distribution_line_number,
    -- aid.distribution_line_number erv_distribution_line_number,
    -- End fix for version 1.9
    null ipv_invoice_distribution_id,
    aid.invoice_distribution_id erv_invoice_distribution_id,
    aid.invoice_line_number invoice_line_number,
    aid.line_type_lookup_code line_type_lookup_code,
    aid.matched_uom_lookup_code matched_uom_lookup_code,
    aid.po_distribution_id po_distribution_id,
    aid.quantity_invoiced quantity_invoiced, -- IPV and ERV lines have no quantity information
    aid.related_id related_id,
    aid.unit_price unit_price, -- IPV and ERV lines have no unit price information
    0 accrual_amount,
    0 ipv_amount,
    aid.base_amount erv_amount,
    pod.po_line_id po_line_id,
    pod.line_location_id line_location_id,
    pod.rate rate,
    pod.destination_type_code destination_type_code
     from ap_invoice_distributions_all        aid,
    po_distributions_all                pod,
    -- Revision 1.14, remove tables to increase performance
    -- xla_transaction_entities            ent,
    -- xla_events                          xe,
    -- End revision for version 1.14
    xla_distribution_links              xdl,
    xla_ae_headers                      ah,
    xla_ae_lines                        al
     where aid.line_type_lookup_code    =  'ERV'
    and 2=2                          -- p_trx_date_from, p_trx_date_to
    and aid.po_distribution_id       = pod.po_distribution_id
    and pod.destination_type_code   <> 'EXPENSE'
    -- ========================================================
    -- SLA table joins to get the exact account numbers
    -- ========================================================
    -- Revision for version 1.14, performance improvements
    -- and ent.entity_code              = 'AP_INVOICES'
    -- and ent.application_id           = 200
    -- and xe.application_id            = ent.application_id
    -- and xe.event_id                  = xdl.event_id
    -- and ah.event_id                  = xe.event_id
    -- and ah.entity_id                 = ent.entity_id
    -- and ah.ledger_id                 = ent.ledger_id
    -- and al.application_id            = ent.application_id
    -- and xdl.application_id           = ent.application_id
    -- End revisions for version 1.14
    and ah.application_id            = al.application_id
    and ah.application_id            = 200
    and ah.ae_header_id              = al.ae_header_id
    and al.ledger_id                 = ah.ledger_id
    and al.ae_header_id              = xdl.ae_header_id
    and al.ae_line_num               = xdl.ae_line_num
    and al.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 = aid.invoice_distribution_id) ipv_erv_txns
  group by
   ipv_erv_txns.invoice_id,
   ipv_erv_txns.period_name,
   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 aid.line_type_lookup_code    =  'ACCRUAL'
 and aid.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.accounting_date,
  net_ipv_erv_txns.ipv_code_combination_id,
  net_ipv_erv_txns.erv_code_combination_id,
  -- Revision for version 1.8
  net_ipv_erv_txns.type,
  net_ipv_erv_txns.accounting_class_code,
  -- End Revision for version 1.8
  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,
  aid.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 api.invoice_id               = ap_txns.invoice_id
and asp.org_id                   = to_number(hoi.org_information3)
and asp.org_id                   = api.org_id
and asp.org_id                   = poh.org_id
and pol.po_line_id               = ap_txns.po_line_id
and poh.po_header_id             = pol.po_header_id
and pll.line_location_id         = ap_txns.line_location_id
and pll.po_line_id               = pol.po_line_id     
and pll.po_release_id            = pr.po_release_id (+)
and pov.vendor_id                = poh.vendor_id
and msiv.inventory_item_id       = pol.item_id
and msiv.inventory_item_id       = ucr.inventory_item_id
and msiv.organization_id         = ucr.organization_id
and ucr.unit_of_measure          = pol.unit_meas_lookup_code
and mp.organization_id           = msiv.organization_id
and msiv.organization_id         = pll.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 poh.agent_id                 = he.employee_id
and pl.lookup_type               = 'DESTINATION TYPE'
and pl.lookup_code               = ap_txns.destination_type_code
-- Added for version 1.8
and flv.lookup_type              = 'XLA_ACCOUNTING_CLASS'
and flv.lookup_code              = ap_txns.accounting_class_code
and flv.language                 = userenv('lang')
-- End revision for version 1.8
-- End revision for version 1.10
--and nvl((round(decode(api.exchange_rate, 
--    null, nvl(ap_txns.unit_price,0),
--           nvl(aid_txns.unit_price,0)*api.exchange_rate),4) * aid_txns.quantity_invoiced) -
--   (nvl(aid_txns.rate,1) * pll.price_override * aid_txns.quantity_invoiced),0) <> 0
-- ===================================================================
-- 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                 = to_number(hoi.org_information1) 
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 gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_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,
 -- Revision for version 1.11 and 1.12
&category_columns2
 -- End revision for version 1.11
 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,<