CAC Receiving Value (Period-End)

select nvl(gl.short_name, gl.name) Ledger,
 haou2.name Operating_Unit,
 mp.organization_code Org_Code,
 oap.period_name Period_Name,
 &column_segments
 -- Revision for version 1.29
 pov.vendor_name Supplier,
  hr.full_name Buyer,
 -- End revision for version 1.29
 rp_items.concatenated_segments Item_Number,
 rp_items.description Item_Description,
 fcl.meaning Item_Type,
 -- Revision for version 1.33
 misv.inventory_item_status_code_tl Item_Status,
 ml.meaning Make_Buy_Code,
 -- revision for version 1.27
 -- Not all items have category values
 -- mc.segment1 Prod_Line,
&category_columns
 pl1.displayed_field Destination_Type,
 -- Revision for version 1.13
 poh.segment1 PO_Number,
 -- Revision for version 1.29
 pol.line_num PO_Line,
 -- Revision for version 1.36
 nvl(pl4.displayed_field,nvl(pl3.displayed_field, pl2.displayed_field))  PO_Line_Status,
 pr.release_num PO_Release,
 pp.segment1 Project_Number,
 pp.name Project_Name,
 (select max(prh.segment1)
  from po_requisition_headers_all prh,
  po_requisition_lines_all prl
  where prh.requisition_header_id = prl.requisition_header_id
  and prl.line_location_id      = net_rcv.po_line_location_id) Requisition_Number,
 (select max(hr.full_name)
  from po_requisition_headers_all prh,
  po_requisition_lines_all prl,
  hr_employees hr
  where prh.requisition_header_id = prl.requisition_header_id
  and prl.line_location_id      = net_rcv.po_line_location_id
  and prh.preparer_id           = hr.employee_id) Requestor,
 (select max(hr.email_address)
  from po_requisition_headers_all prh,
  po_requisition_lines_all prl,
  hr_employees hr
  where prh.requisition_header_id = prl.requisition_header_id
  and prl.line_location_id      = net_rcv.po_line_location_id
  and prh.preparer_id           = hr.employee_id) Requestor_Email,
 -- End revision for version 1.29
 net_rcv.receipt_num Receipt_Number,
 -- Revision for version 1.13
 net_rcv.transaction_date Earliest_Receipt_Date,
 case
  when (sysdate - net_rcv.transaction_date) < 31  then '30 days'
  when (sysdate - net_rcv.transaction_date) < 61  then '60 days'
  when (sysdate - net_rcv.transaction_date) < 91  then '90 days'
  when (sysdate - net_rcv.transaction_date) < 121 then '120 days'
  when (sysdate - net_rcv.transaction_date) < 151 then '150 days'
  when (sysdate - net_rcv.transaction_date) < 181 then '180 days'
  else 'Over 180 days'
 end Aging_Date,
 -- Revision for version 1.13 and 1.36
 &p_show_wip_osp
  -- nvl((select we.wip_entity_name
  --      from wip_entities we
  --      where we.wip_entity_id = net_rcv.wip_entity_id),'') WIP_Job,
  -- -- Revision for version 1.30
  -- nvl((select ml.meaning
  --      from wip_discrete_jobs wdj,
  --   mfg_lookups ml
  --      where wdj.wip_entity_id = net_rcv.wip_entity_id
  --      and ml.lookup_type    = 'WIP_JOB_STATUS'
   --     and ml.lookup_code    = wdj.status_type),'') Job_Status,
  -- nvl((select wdj.date_closed
  --      from wip_discrete_jobs wdj
  --      where wdj.wip_entity_id = net_rcv.wip_entity_id),'') Job_Close_Date,
  -- -- End revision for version 1.30
  -- nvl((select br.resource_code
  --     from bom_resources br
   --     where br.resource_id    = net_rcv.bom_resource_id),'') OSP_Resource,
  -- -- End revision for version 1.13 and 1.36
 -- Revision for version 1.33
 muomv.uom_code UOM_Code,
 sum(net_rcv.quantity) Onhand_Quantity,
 gl.currency_code Currency_Code,
 sum(net_rcv.amount) Onhand_Value
-- ==========================================================
-- Select the receiving quantities and values from Part 4,
-- condensing to one row per org, item, po, po line and req.
-- ==========================================================
from -- Revision for version 1.37
 -- mtl_system_items_vl msiv,
 -- Revision for version 1.33
 mtl_item_status_vl misv,
 mtl_units_of_measure_vl muomv,
 -- End revision for version 1.33
 mtl_parameters mp,
 -- Revision for version 1.35
 -- rcv_parameters rp,
 -- Get the receiving valuation accounts by organization and item master account
 (-- No Product Line Accounting
  select 'No Product Line Accounting' valuation_type,
  rp.organization_id,
  msiv.inventory_item_id,
  rp.receiving_account_id,
  -- Revision for version 1.37
  msiv.concatenated_segments,
  msiv.description,
  msiv.item_type,
  msiv.inventory_item_status_code,
  msiv.planning_make_buy_code,
  msiv.primary_uom_code
  -- End revision for version 1.37
  from rcv_parameters rp,
  mtl_system_items_vl msiv
  where msiv.organization_id            = rp.organization_id
  -- Avoid organizations with category accounts
  and :p_product_line_accounting      = 'N'
  and rp.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 4=4                             -- p_org_code
  union all
  -- Category Accounting
  -- Revision for version 1.19
  select 'Product Line Accounting' valuation_type,
  item.organization_id,
  item.inventory_item_id,
  nvl(item.code_combination_id,rp.receiving_account_id) receiving_account_id,
  -- Revision for version 1.37
  item.concatenated_segments,
  item.description,
  item.item_type,
  item.inventory_item_status_code,
  item.planning_make_buy_code,
  item.primary_uom_code
  -- End revision for version 1.37
  from rcv_parameters rp,
  (select rp.organization_id,
   msiv.inventory_item_id,
   (select gcc2.code_combination_id
    from gl_code_combinations gcc2
    where &gcc_product_line_where_clause 
    gcc.chart_of_accounts_id = gcc2.chart_of_accounts_id) code_combination_id,
   -- Revision for version 1.37
   msiv.concatenated_segments,
   msiv.description,
   msiv.item_type,
   msiv.inventory_item_status_code,
   msiv.planning_make_buy_code,
   msiv.primary_uom_code
   -- End revision for version 1.37
   from gl_code_combinations gcc_item,
   gl_code_combinations gcc,
   mtl_system_items_vl msiv,
   rcv_parameters rp,
   fnd_lookups fl
   where gcc_item.code_combination_id =
    decode( fl.lookup_code,
     'SALES_ACCOUNT', msiv.sales_account,
     'COST_OF_SALES_ACCOUNT', msiv.cost_of_sales_account,
     'EXPENSE_ACCOUNT', msiv.expense_account)
   and gcc.code_combination_id (+)  = rp.receiving_account_id
   and msiv.organization_id         = rp.organization_id
   and fl.lookup_type               = 'INV_ITEM_ATTRIBUTES'
   and fl.meaning                   = :p_item_master_account_type
   and rp.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 4=4                          -- p_org_code
  ) item
  where item.organization_id(+)            = rp.organization_id
  and :p_product_line_accounting         = 'Y'
 ) rp_items,
 -- End revision for version 1.35
 po_headers_all poh,
 org_acct_periods oap,
 mfg_lookups ml, -- Planning Make Buy Code
 fnd_common_lookups fcl, -- Item Type
 po_lookup_codes pl1, -- Destination Type Code
 -- Revision for version 1.36
 po_lookup_codes pl2,  -- Header
 po_lookup_codes pl3,  -- Line
 po_lookup_codes pl4,  -- Line location
 -- End revision for version 1.36
 -- Revision for version 1.29
 po_lines_all pol,
 -- Revision for version 1.36
 po_line_locations_all pll,
 po_releases_all pr,
 po_vendors pov,
 hr_employees hr,
 pa_projects_all pp,
 -- End revision for version 1.29
 hr_organization_information hoi,
 hr_all_organization_units haou,  -- inv_organization_id
 hr_all_organization_units haou2, -- operating unit
 gl_code_combinations gcc,
 gl_ledgers gl,
 -- ==================================
 -- Get receiving quantities and value
 -- ==================================
 -- ================================================
 -- Part 4
 -- Condense the Union down to individual Org/Items
 -- ================================================
 (select all_rcv.organization_id         organization_id,
  all_rcv.inventory_item_id       inventory_item_id,
  all_rcv.destination_type_code   destination_type_code,
  all_rcv.po_header_id            po_header_id,
  all_rcv.po_line_id              po_line_id,
  all_rcv.po_line_location_id     po_line_location_id,
  -- Revision for version 1.29
  all_rcv.po_release_id  po_release_id,
  all_rcv.project_id project_id,
  -- End revision for version 1.29
  all_rcv.receipt_num             receipt_num,
  -- ==========================================================
  -- Revision for version 1.36
  -- Move the earliest transaction logic to the upper query.
  -- For consistent reporting so the qtys and amounts sum together,
  -- use the earliest receipt date for the transaction date
  -- ==========================================================
  (select min(rt2.transaction_date)
   from rcv_transactions rt2,
   rcv_shipment_headers rsh2
   where rt2.organization_id     = all_rcv.organization_id
   and rt2.shipment_header_id  = all_rcv.shipment_header_id
   and rsh2.shipment_header_id = rt2.shipment_header_id
   and rsh2.receipt_num        = all_rcv.receipt_num
  ) transaction_date,
  -- End revision for version 1.36
  all_rcv.wip_entity_id           wip_entity_id,
  all_rcv.bom_resource_id         bom_resource_id,
  -- Revision for version 1.36, comment this out
  -- get the earliest date so that the Aging Date is correct,
  -- even if you run this report for a prior period
  -- Fix for version 1.16, changed to MIN instead of MAX
  -- min(trunc(all_rcv.transaction_date)) transaction_date,
  -- End revision for version 1.36
  sum(nvl(all_rcv.quantity,0))    quantity,
  sum(nvl(all_rcv.amount,0))      amount
  from (
    -- =============================================================
   -- Part 3
   -- Get the onhand receiving quantities
   -- =============================================================
   -- Revision for version 1.28, rewrite this section to not use
   -- rcv_receiving_value_view, unit prices in Release 12 from
   -- purchase orders or from transfer prices based on advanced pricing.
    -- =============================================================
   select 'rcv_onhand' section,
    rs.to_organization_id organization_id,
   rs.item_id  inventory_item_id,
   rs.destination_type_code,
   rs.po_header_id,
   rs.po_line_id,
   rs.po_line_location_id,
   -- Revision for version 1.29
   rs.po_release_id,
   -- Revision for version 1.36
   -- Move Txn Date Logic to all_rcv Query
   rs.shipment_header_id,
   rs.shipment_line_id,
   -- End revision for version 1.36
   pod.project_id,
   -- End revision for version 1.29
   rsh.receipt_num,
   pod.wip_entity_id,
   pod.bom_resource_id,
   round(rs.to_org_primary_quantity,3) quantity,
   -- Revision for version 1.32
   -- round(decode(rt.currency_conversion_rate,
   --    null, nvl(rt.po_unit_price,0),
   --    nvl(rt.po_unit_price,0) * rt.currency_conversion_rate
   --      ) * (rt.source_doc_quantity/rt.primary_quantity)
   --        * round(rs.to_org_primary_quantity,3),2) amount
   -- End revision for version 1.32
   rs.amount
   from (select ms.to_organization_id,
    ms.item_id,
    ms.destination_type_code,
    ms.po_header_id,
    ms.po_line_id,
    ms.po_line_location_id,
    ms.po_distribution_id,
    ms.po_release_id,
    ms.req_header_id,
    ms.shipment_header_id,
    ms.shipment_line_id,
    ms.rcv_transaction_id,
    ms.to_org_primary_quantity,
    -- Revision for version 1.32
    -- Need a consistent price based on rae qtys as the transaction quantity in rcv_accounting_events may be
    -- different from the ms.to_org_primary_quantity in mtl_supply, due to returns to vendor transactions.
    -- Average Unit Price
    round(sum(decode(rae.currency_conversion_rate,
       null, nvl(rae.unit_price,0),
       nvl(rae.unit_price,0) * rae.currency_conversion_rate
      )
       * (rae.source_doc_quantity/rae.primary_quantity) * rae.primary_quantity
      )
    -- Divided by the Quantity
      / sum(rae.primary_quantity)
       ,8) avg_unit_price,
    -- Average Unit Price X Quantity = Amount
    round(ms.to_org_primary_quantity *
     -- Price X Quantity
     round(sum(decode(rae.currency_conversion_rate,
        null, nvl(rae.unit_price,0),
        nvl(rae.unit_price,0) * rae.currency_conversion_rate
       ) * (rae.source_doc_quantity/rae.primary_quantity) * rae.primary_quantity
           ) / sum(rae.primary_quantity)
        ,8)
        ,2) amount
    from mtl_supply ms,
    -- Revision for version 1.35
    (select x.*
     from (select rt.transaction_id parent_transaction_id,
      rt.organization_id,
      connect_by_root rt.transaction_id child_transaction_id,
      connect_by_isleaf
      from rcv_transactions rt
      connect by prior rt.parent_transaction_id=rt.transaction_id
      start with rt.transaction_id in
      (select ms.rcv_transaction_id
       from mtl_supply ms
       where ms.supply_type_code       ='RECEIVING'
       -- Revision for version 1.36, client has expense receipts with items
       -- and ms.destination_type_code <>'EXPENSE'
       and ms.to_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 5=5                       -- p_org_code
       -- End revision for version 1.36
      )
      -- Transfer of ownership consigned entries do not hit receiving accounts
      and nvl(rt.consigned_flag,'N')        = 'N'
      and rt.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 2=2                               -- p_org_code
     ) x
     where x.connect_by_isleaf=1
    ) rt,
    -- End revision for version 1.35
    rcv_accounting_events rae
    where ms.rcv_transaction_id         = rt.child_transaction_id
    and rt.parent_transaction_id      = rae.rcv_transaction_id
    -- Revision for version 1.35
    -- and rae.transaction_date         >= ms.receipt_date
    and rae.organization_id           = rt.organization_id
    -- Revision for version 1.36
    and ms.to_organization_id         = rt.organization_id
    group by
    ms.to_organization_id,
    ms.item_id,
    ms.destination_type_code,
    ms.po_header_id,
    ms.po_line_id,
    ms.po_line_location_id,
    ms.po_distribution_id,
    ms.po_release_id,
    ms.req_header_id,
    ms.shipment_header_id,
    ms.shipment_line_id,
    ms.rcv_transaction_id,
    ms.to_org_primary_quantity
   ) rs,
   -- End revision for version 1.35
   rcv_shipment_headers rsh,
   rcv_shipment_lines rsl,
   po_headers_all ph,
   po_line_locations_all pll,
   po_distributions_all pod,
   po_requisition_headers_all prh
   -- Revision for version 1.29
   -- po_lines_all pl,
   -- po_requisition_lines_all prl,
   -- rcv_transactions rt
  where rsh.shipment_header_id        = rs.shipment_header_id
  and rsl.shipment_line_id          = rs.shipment_line_id
  -- Revision for version 1.36
  and rsl.shipment_header_id        = rs.shipment_header_id
  and ph.po_header_id (+)           = rs.po_header_id
  -- Revision for version 1.29
  -- and pl.po_line_id (+)             = rs.po_line_id
  and pll.line_location_id (+)      = rs.po_line_location_id
  and pod.po_distribution_id (+)    = rs.po_distribution_id
  and prh.requisition_header_id (+) = rs.req_header_id
  -- Revision for version 1.29
  -- and prl.requisition_line_id (+)   = rs.req_line_id
  -- and rs.rcv_transaction_id         = rt.transaction_id
  -- and rs.supply_type_code           = 'RECEIVING'
  -- use the Expense Receiving Value Report instead
  -- Revision for version 1.36, client has expense receipts with items
  -- and rs.destination_type_code <> 'EXPENSE'
  -- Internal requisitions are not part of receiving inventory value
  and rsl.source_document_code <> 'REQ'
  union all
        -- =============================================================
       -- Part 2
       -- Sum up all the post close rcv'g transactions by item and org
       -- The SIGN of the quantities and amounts have been reversed
       -- =============================================================
   select post_close_rcv_txns.section               section,
   post_close_rcv_txns.organization_id       organization_id,
   post_close_rcv_txns.inventory_item_id     inventory_item_id,
   post_close_rcv_txns.destination_type_code destination_type_code,
   post_close_rcv_txns.po_header_id          po_header_id,
   post_close_rcv_txns.po_line_id            po_line_id,
   post_close_rcv_txns.po_line_location_id   po_line_location_id,
   -- Revision for version 1.29
   post_close_rcv_txns.po_release_id         po_release_id,
   -- Revision for version 1.36
   -- Move Txn Date Logic to all_rcv Query
   post_close_rcv_txns.shipment_header_id,
   post_close_rcv_txns.shipment_line_id,
   -- End revision for version 1.36
   post_close_rcv_txns.project_id            project_id,
   -- End revision for version 1.29
   post_close_rcv_txns.receipt_num           receipt_num,
   post_close_rcv_txns.wip_entity_id         wip_entity_id,
   post_close_rcv_txns.bom_resource_id       bom_resource_id,
   -- Revision for version 1.36
   -- post_close_rcv_txns.transaction_date      transaction_date,
   sum(nvl(post_close_rcv_txns.quantity,0))  quantity,
   sum(nvl(post_close_rcv_txns.amount,0))    amount
   from (
       -- ==========================================================
       -- Part 1
       -- Get the post close transactions for all receiving activity
       -- ==========================================================
    -- ==========================================================
    -- 1.1 Get the PO receipts into receiving inspection
    -- ==========================================================
    select 'Section 1.1' section,
    -- Revision for version 1.28, added to avoid missing
    -- transactions having same organization_id, item_id, etc.
    rae.rcv_transaction_id,
    rae.organization_id,
    rae.inventory_item_id inventory_item_id,
    pod.destination_type_code destination_type_code,
    rae.po_header_id po_header_id,
    rae.po_line_id po_line_id,
    rae.po_line_location_id po_line_location_id,
    -- Revision for version 1.29
    rsl.po_release_id po_release_id,
    -- Revision for version 1.36
    -- Move Txn Date Logic to all_rcv Query
    rsh.shipment_header_id,
    rsl.shipment_line_id,
    -- End revision for version 1.36
    pod.project_id project_id,
    -- End revision for version 1.29
    rsh.receipt_num receipt_num,
    pod.wip_entity_id wip_entity_id,
    pod.bom_resource_id bom_resource_id,
    -- Amount = Quantity X Price
    -- Rewrite quantity logic for version 1.38, decode on received accounted amounts does not work if it is zero
    round(decode(rt.transaction_type,
      'RECEIVE', -1 * rt.primary_quantity,
      'RETURN TO VENDOR', 1 * rt.primary_quantity,
      'MATCH', -1 * rt.primary_quantity,
      'CORRECT',
       decode(parent_rt.transaction_type,
        'UNORDERED', 0,
        'RECEIVE', -1 * rt.primary_quantity,
        'RETURN TO VENDOR', 1 * rt.primary_quantity,
        0
             ),
      0
         )
       ,3) quantity,
    -- =====================================================================
    -- Revision for version 1.28
    -- 1)  Round amounts to 2 decimals
    -- 2)  No longer use rrsl, inconsistent amounts with mmt when try to
    --     subtract away non-recoverable tax and recoverable tax amounts
    -- 3)  Invert the SIGN as we will subtract away these amounts
    -- 4)  Convert the price into the primary UOM -- (rae.source_doc_quantity/rae.primary_quantity)
    -- 5)  Use rcv_accounting_events to get the quantity received by PO Distribution
    -- 6)  Don't sum up the quantities or amounts as there are multiple po
    --     distributions per PO Header, Line and Line Location, which creates
    --     split PO receipts by a percentage of the PO Distributions.
    -- =====================================================================
    -- Comment out the below code
    -- -1 * round(sum((nvl(rrsl.accounted_dr,0) - nvl(rrsl.accounted_cr,0)
    -- - nvl(rrsl.accounted_rec_tax,0) - nvl(rrsl.accounted_nr_tax0))),2) amount
    -- =====================================================================
    round(
     -- Quantity
     round(decode(sign(nvl(rrsl.accounted_dr,0) - nvl(rrsl.accounted_cr,0)),
         1,  -1 * abs(rae.primary_quantity),
        -1, +1 * abs(rae.primary_quantity),
        rae.primary_quantity
           )
        ,3) *
            -- Price
     decode(rt.currency_conversion_rate,
      null, nvl(rt.po_unit_price,0),
      nvl(rt.po_unit_price,0) * rt.currency_conversion_rate
           ) *
     -- Convert into the primary UOM
     (rt.source_doc_quantity/rt.primary_quantity)
       ,2) amount
    -- End fix for version 1.28
    from -- =====================================================================
    -- Revision for version 1.28
    -- Client has multiple WIP Entity Ids and multiple PO distributions per
    -- Receipt Number for the same PO Header, Line, Line Location and item number.
    -- Need to use rcv_accounting_events to get the split quantities
    -- =====================================================================
    rcv_transactions rt,
    -- Revision for version 1.38
    rcv_transactions parent_rt,
    rcv_shipment_headers rsh,
    rcv_shipment_lines rsl,
    rcv_accounting_events rae,
    rcv_receiving_sub_ledger rrsl,
    po_distributions_all pod,
    org_acct_periods oap
    where rt.transaction_id            = rae.rcv_transaction_id
    -- Revision for version 1.38
    and rt.parent_transaction_id     = parent_rt.transaction_id (+)
    and rt.organization_id           = parent_rt.organization_id (+)
    and rt.transaction_id            = rrsl.rcv_transaction_id
    -- Oracle difference =>  the RRSL table is using the meaning as the value for the CST_ACCOUNTING_LINE_TYPE
    --                       lookup code, as opposed to the lookup code values 1 - 99
    -- Revision for version 1.34, receiving transactions also use 'Clearing'
    -- and rrsl.accounting_line_type    = 'Receiving Inspection'
    and rrsl.accounting_line_type in ('Clearing', 'Receiving Inspection')
    -- End revision for version 1.34
    -- Revision for version 1.32
    -- Fix for version 1.16
    -- and trunc(rae.transaction_date) > oap.schedule_close_date
    and rt.transaction_date         >= oap.schedule_close_date + 1
    -- Revision for version 1.36
    and rrsl.transaction_date       >= oap.schedule_close_date + 1
    and pod.po_distribution_id       = rrsl.reference3
    -- and rae.organization_id          = rt.organization_id
    -- and oap.organization_id          = rae.organization_id
    -- and rae.transaction_date        >= oap.schedule_close_date + 1
    -- and pod.po_distribution_id       = rae.po_distribution_id
    -- End revision for version 1.36
    and rt.transaction_id            = rrsl.rcv_transaction_id
    and oap.organization_id          = rt.organization_id
    and rae.accounting_event_id      = rrsl.accounting_event_id
    -- End revision for version 1.32
    -- Revision for version 1.32
    and rt.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 2=2                          -- p_org_code
    and 3=3                          -- p_period_name
    -- Revision for version 1.36
    -- and pod.destination_type_code   <> 'EXPENSE'
    and rt.shipment_header_id        = rsh.shipment_header_id
    and rt.shipment_line_id          = rsl.shipment_line_id
    -- Fix for version 1.19
    and rt.transaction_type         <> 'DELIVER'  -- only want receipts, return to vendor and corrections
    -- End revision for version 1.32
    group by
    'Section 1.1', -- section
    -- Revision for version 1.28
    rae.rcv_transaction_id,
    rae.organization_id,
    rae.inventory_item_id,
    pod.destination_type_code,
    rae.po_header_id,
    rae.po_line_id,
    rae.po_line_location_id,
    -- Revision for version 1.29
    rsl.po_release_id,
    -- Revision for version 1.36
    -- Move Txn Date Logic to all_rcv Query
    rsh.shipment_header_id,
    rsl.shipment_line_id,
    -- End revision for version 1.36
    pod.project_id,
    -- End revision for version 1.29
    rsh.receipt_num,
    pod.wip_entity_id,
    pod.bom_resource_id,
    -- Revision for version 1.38, decode on accounted amounts does not work if it is zero
    round(decode(rt.transaction_type,
      'RECEIVE', -1 * rt.primary_quantity,
      'RETURN TO VENDOR', 1 * rt.primary_quantity,
      'MATCH', -1 * rt.primary_quantity,
      'CORRECT',
       decode(parent_rt.transaction_type,
        'UNORDERED', 0,
        'RECEIVE', -1 * rt.primary_quantity,
        'RETURN TO VENDOR', 1 * rt.primary_quantity,
        0
             ),
      0
         )
       ,3), --quantity
    -- Amount = Quantity X Price
    round(
     -- Quantity
     round(decode(sign(<