CAC Receiving Value (Period-End)

Description
Categories: Enginatics
Repository: Github
Report to show receiving value for all locations, as of the end of an accounting period. You may run this report for open or closed accounting periods. With parameters to display or not display WIP outside processing information (WIP job, status, close date and OSP resource code). If run for a prior period the report automatically rolls back the quantities and values to the prior period's perio ...  Report to show receiving value for all locations, as of the end of an accounting period. You may run this report for open or closed accounting periods. With parameters to display or not display WIP outside processing information (WIP job, status, close date and OSP resource code). If run for a prior period the report automatically rolls back the quantities and values to the prior period's period end date. In addition, for any receipt with an item number, this report displays Inventory, Shop Floor (Outside Processing) and Expense destination types.

Reconciliation Notes to Oracle Reports:
The Oracle All Inventory Values Report does not display Shop Floor (Outside Processing) or Expense destination types.
If the PO line is closed, the Oracle Receiving Value Report does not display Expense destination types, even if the receipt is still in receiving and has not been delivered.

Parameters:
Period Name: the accounting period you wish to report (mandatory).
Show WIP Outside Processing: display WIP job details for outside processing, enter Yes or No (mandatory).
Category Set 1: any item category you wish (optional).
Category Set 2: any item category you wish (optional).
Item Number: specific item you wish to report (optional)
Organization Code: specific inventory organization to report (optional)
Operating Unit: specific operating unit (optional)
Ledger: specific ledger (optional)
Product Line Accounting: use this parameter to tell the report if you have set up product line accounting information in your item master accounts. Enter Yes or No.
Product Line Segment: use this parameter to tell the report which segment to use to find the product line information. If the Product Line Accounting parameter is set to No this parameter is grayed out and not required.
Item Master Account Type: use this parameter to determine if you store your product line information in your item master Expense, Cost of Sales or Sales Accounts. If the Product Line Accounting parameter is set to No this parameter is grayed out and not required.

/* +=============================================================================+
-- | Copyright 2009 - 2022 Douglas Volz Consulting, Inc.
-- | All rights reserved.
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 25 Nov 2009 Douglas Volz Created initial Report for client
-- | based on xxx_inv_value.sql
-- | 1.32 15 Jan 2022 Douglas Volz Modify joins to rcv_accounting_events, was
-- | cross-joining giving poor results. Added parameter
-- | conditions for p_org_code.
-- | 1.33 24 Jul 2022 Douglas Volz Multi-language changes for column headings, item
-- | status and uom code. Remove organization name.
-- | 1.34 26 Jul 2022 Douglas Volz Post Close Section, add Clearing accounting line type
-- | to queries and fix cross joining to po distributions.
-- | 1.35 02 Aug 2022 Douglas Volz Replace hard-code tree-walk with connect by statements and
-- | add dynamic product line accounts from the item master.
-- | 1.36 10 Aug 2022 Douglas Volz Performance improvements for main query. Allow expense
-- | receipts with items, add PO line status column.
-- | 1.37 12 Aug 2022 Douglas Volz Add parameter to Show WIP OSP columns and remove extra
-- | join to the item master table.
-- | 1.38 16 Aug 2022 Douglas Volz Fixes for quantities for Correction transactions and
-- | for when the PO unit price is zero.
-- +=============================================================================+*/
   more
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 *