CAC Open Internal ISO-IRO

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report to display the open internal sales orders and requisition numbers, with aging dates and other useful information.

/* +=============================================================================+
-- | Copyright 2009 - 2022 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Permissi ...  Report to display the open internal sales orders and requisition numbers, with aging dates and other useful information.

/* +=============================================================================+
-- | Copyright 2009 - 2022 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Permission to use this code is granted provided the original author is |
-- | acknowledged. No warranties, express or otherwise is included in this |
-- | permission. |
-- +=============================================================================+
-- | Original Author: Douglas Volz ([email protected])
-- |
-- | Program Name: xxx_open_iro.sql
-- |
-- | Parameters:
-- | p_org_code -- Specific inventory organization you wish 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)
-- | 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
-- |
-- | Description:
-- | Report to display the open internal sales orders and requisition numbers,
-- | with aging dates and other useful information.
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 12 Nov 2009 Douglas Volz Initial Coding for Client
-- | 1.1 01 Sep 2010 Douglas Volz Added Ledger and Operating Unit info
-- | Added condition to avoid old pre-conversion orgs
-- | 1.2 30 Mar 2011 Douglas Volz Added condition to avoid disabled orgs,
-- | added Ledger parameter
-- | 1.3 18 Nov 2012 Douglas Volz Removed client-specific org conditions
-- | 1.4 16 Jan 2020 Douglas Volz Added org code, operating unit parameters
-- | category set parameters, item type and status.
-- | 1.5 09 Apr 2020 Douglas Volz Replaced tables for multi-language capabilities:
-- | mtl_system_items with mtl_system_items_vl
-- | mtl_categories_b with mtl_categories_vl
-- | hr_all_organization_units with hr_all_organization_units_vl
-- | 1.6 07 Jul 2022 Douglas Volz Replace with multi-lang item status and UOM_Codes.
-- +=============================================================================+*/   more
Run CAC Open Internal ISO-IRO and other Oracle EBS reports with Blitz Report™ on our demo environment
select nvl(gl.short_name, gl.name) Ledger,
 haou2.name Operating_Unit,
 mp.organization_code Ship_From_Org,
 mp2.organization_code Ship_To_Org,
 msiv.concatenated_segments Item_Number,
 msiv.description Item_Description,
 -- Revision for version 1.4 and 1.7
 muomv.uom_code UOM_Code,
 fcl.meaning Item_Type,
 misv.inventory_item_status_code Item_Status,
 ml1.meaning Make_Buy_Code,
&category_columns
 hz.party_name Customer,
 hca.account_number Customer_Number,
 iso.order_number Sales_Order_Number,
 iso_line.line_number SO_Line,
 decode(iso.order_source_id, 10, iso.orig_sys_document_ref, '') Requisition_Number,
 fu_iso.user_name IRO_Created_By,
 fu_iso2.user_name IRO_Last_Updated_By,
 ottt.name Order_Type,
 iso_line.flow_status_code Status,
 hz.address1 Address_Line_1,
 hz.address2 Address_Line_2,
 hsu.location Location_Number,
 hl.city City,
 hl.state State,
 hl.county County,
 hl.country Country,
 trunc(iso_line.creation_date) Creation_Date,
 trunc(iso_line.request_date) Request_Date,
 trunc(iso_line.promise_date) Promise_Date,
 trunc((sysdate - iso.request_date)) Days_Outstanding,
 case 
  when (sysdate - iso.request_date) < 31  then '30 days'
  when (sysdate - iso.request_date) < 61  then '31 to 60 days'
  when (sysdate - iso.request_date) < 91  then '61 to 90 days'
  when (sysdate - iso.request_date) < 121 then '91 to 120 days'
  when (sysdate - iso.request_date) < 151 then '121 to 150 days'
  when (sysdate - iso.request_date) < 181 then '151 to 180 days'
   else 'Over 180 days'
 end Aging_Date,
 iso_line.ORDER_QUANTITY_UOM Order_UOM,
 iso_line.ordered_quantity Ordered_Quantity,
 gl.currency_code Currency_Code,
 cic.item_cost Unit_Cost,
 round(iso_line.ordered_quantity * cic.item_cost,2) COGS_Amount
from mtl_system_items_vl msiv,
 -- Revision for version 1.6
 mtl_item_status_vl misv,
 mtl_units_of_measure_vl muomv,
 -- End revision for version 1.6
 cst_item_costs cic,    
 oe_order_headers_all iso,  
 oe_order_lines_all iso_line,  
 oe_transaction_types_tl ottt,
 po_requisition_headers_all prh,   
 po_requisition_lines_all prl,
 mtl_parameters mp,    
 mtl_parameters mp2,
 -- Revision for version 1.4
 fnd_common_lookups fcl,
 -- Revision for version 1.6
 mfg_lookups ml1, -- planning make/buy code, MTL_PLANNING_MAKE_BUY
 fnd_user fu_iso,
 fnd_user fu_iso2,
 hz_cust_accounts_all hca,
 hz_cust_acct_sites_all hcs,
 hz_cust_site_uses_all hsu,
 hz_parties hz,
 hz_party_sites hps,
 hz_locations hl,
 -- fix for version 1.1
 hr_organization_information hoi,
 hr_all_organization_units_vl haou,
 hr_all_organization_units_vl haou2,
 gl_ledgers gl    
-- =============================================
-- Internal order, item, cost and org joins
-- =============================================
where iso_line.open_flag              = 'Y'
and msiv.inventory_item_id          = iso_line.inventory_item_id  
and msiv.organization_id            = iso_line.ship_from_org_id
-- Revision for version 1.6
and msiv.primary_uom_code           = muomv.uom_code
and msiv.inventory_item_status_code = misv.inventory_item_status_code
-- End revision for version 1.6
and msiv.inventory_item_id          = cic.inventory_item_id  
and msiv.organization_id            = cic.organization_id
-- Revision for version 1.6
and cic.cost_type_id                = mp.primary_cost_method     
and iso_line.line_category_code in ('ORDER')
-- use this condition to limit this sql for internal requisitions
and iso_line.order_source_id        = 10 -- internal requisitions
and iso.header_id                   = iso_line.header_id
and iso_line.line_type_id           = ottt.transaction_type_id  
and ottt.language                   = userenv('lang')
and mp.organization_id              = msiv.organization_id
-- Revision for version 1.3
-- Avoid selecting disabled inventory organizations
and sysdate                         <  nvl(haou.date_to, sysdate +1)
-- =============================================
-- Use these conditions to join to purchase reqs
-- =============================================
and prh.type_lookup_code            = 'INTERNAL'
and iso.source_document_id          = prh.requisition_header_id
and prl.requisition_header_id       = prh.requisition_header_id
and prl.requisition_line_id         = iso_line.source_document_line_id
and mp2.organization_id             = prl.DESTINATION_organization_id
and fu_iso.user_id                  = prh.created_by
and fu_iso2.user_id                 = prh.last_updated_by
-- =============================================
-- Lookup codes for item types
and fcl.lookup_code (+)             = msiv.item_type
and fcl.lookup_type (+)             = 'ITEM_TYPE'
-- Revision for version 1.6
and ml1.lookup_type                 = 'MTL_PLANNING_MAKE_BUY'
and ml1.lookup_code                 = msiv.planning_make_buy_code
-- =============================================
 -- added for customer ship-to-information
 -- and replaces use of ra_customers
 -- d.volz 6-Oct-08
and iso_line.sold_to_org_id         = hca.cust_account_id
and iso_line.Ship_to_org_id         = hsu.site_use_id
and hcs.cust_acct_site_id           = hsu.cust_acct_site_id
and hca.cust_account_id             = hcs.cust_account_id
and hca.party_id                    = hz.party_id
and hcs.party_site_id               = hps.party_site_id
and hps.location_id                 = hl.location_id
-- =============================================
-- Organization joins to the HR org model
-- Fix for version 1.1
-- =============================================
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) -- get the ledger_id
-- =============================================
-- Parameters, revision for version 1.1 and 1.4
-- =============================================
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 1=1                             -- p_item number, p_org_code, p_operating Unit, p_ledger
-- Order by Ledger, Operating_Unit, Ship From, Ship To, Item, customer, order number and order line
order by 
 nvl(gl.short_name, gl.name), -- Ledger
 haou2.name, --  Operating_Unit
 mp.organization_code, --  Ship_From_Org
 mp2.organization_code, --  Ship_To_Org
 msiv.concatenated_segments, --  Item_Number
 hz.party_name, --  Customer
 iso.order_number, --  SO Number
 iso_line.line_number --  SO Line
Parameter Name SQL text Validation
Category Set 1 
select x.* from (
select
'(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and msiv.organization_id=mic.organization_id and msiv.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substr(fifsv.form_left_prompt,1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv,
fnd_id_flex_segments_vl fifsv
where
mcsv.category_set_name=:category_set_name1 and
mcsv.structure_id=fifsv.id_flex_num and
fifsv.application_id=401 and
fifsv.id_flex_code='MCAT' and
fifsv.enabled_flag='Y'
order by
fifsv.id_flex_num,
fifsv.segment_num
) x
union all
select
'(select min(mct.description) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_tl mct where mic.category_set_id='||mcsv.category_set_id||' and msiv.organization_id=mic.organization_id and msiv.inventory_item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang'')) "'||substr(mcsv.category_set_name||' Description',1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_name=:category_set_name1
 LOV
Category Set 2 
select x.* from (
select
'(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and msiv.organization_id=mic.organization_id and msiv.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substr(fifsv.form_left_prompt,1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv,
fnd_id_flex_segments_vl fifsv
where
mcsv.category_set_name=:category_set_name2 and
mcsv.structure_id=fifsv.id_flex_num and
fifsv.application_id=401 and
fifsv.id_flex_code='MCAT' and
fifsv.enabled_flag='Y'
order by
fifsv.id_flex_num,
fifsv.segment_num
) x
union all
select
'(select min(mct.description) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_tl mct where mic.category_set_id='||mcsv.category_set_id||' and msiv.organization_id=mic.organization_id and msiv.inventory_item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang'')) "'||substr(mcsv.category_set_name||' Description',1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_name=:category_set_name2
 LOV
Item Number 
msiv.concatenated_segments = :p_item_number
 LOV
Organization Code 
mp.organization_code = :p_org_code
 LOV
Operating Unit 
haou2.name = :p_operating_unit
 LOV
Ledger 
gl.name = :p_ledger
 LOV
Download
 
Related Blitz Reports
PO Receiving Transactions Pipeline MRP Pegging CAC Internal Order Shipment Margin GL Account Analysis COPY OF: GL Account Analysis CAC Purchase Price Variance CAC Deferred COGS Out-of-Balance GL Account Analysis (Distributions) CAC ICP PII Material Account Detail
Blitz Report™

Blitz Report™ provides multiple benefits: