INV Cycle count open requests listing

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Cycle count open requests listing (XML)
Short Name: INVARORE_XML
DB package: INV_INVARORE_XMLP_PKG
select  
       sys.item_number,
       sys.description  Description,
       cce.subinventory                       Subinventory,
       cce.revision                               Revision,
       cce.lot_number                         LotNumber,
       cce.locator_id                           Locator ,
       cce.creation_date                     RequestDate,
       cce.count_due_date                  DueDate,
       cce.entry_status_code              StatusCode,
       mfg1.meaning                            Status,
       mfg2.meaning                            CountType,
       abc.abc_class_name                 ABCClass,   
       decode (cce.entry_status_code,
               3, 'N',
              decode(greatest (cce.count_due_date, :C_ReportDate ),
                           :C_ReportDate, 'Yes',
                           'No' )
              )        OverdueFlag,                                     
       null    C_LOCATOR_FLEXDAT,
       CYCLE_COUNT_ENTRY_ID   CYCLE_COUNT_ENTRY_ID,
       SERIAL_DETAIL   SERIAL_DETAIL,
       SERIAL_NUMBER    SERIAL_NUMBER,
       CCE.PARENT_LPN_ID   PARENT_LPN_ID,
       CCE.OUTERMOST_LPN_ID  OUTERMOST_LPN_ID,
       CCH.CONTAINER_ENABLED_FLAG,
       CCE.COST_GROUP_ID, 
	decode(cce.locator_id,null,' ',fnd_flex_xml_publisher_apis.process_kff_combination_1('f_locator_value', 'INV', 'MTLL', 101, LOC.ORGANIZATION_ID, LOC.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE')) F_LOCATOR_VALUE, 
	INV_INVARORE_XMLP_PKG.cf_parent_lpnformula(CCH.CONTAINER_ENABLED_FLAG, CCE.PARENT_LPN_ID) CF_PARENT_LPN, 
	INV_INVARORE_XMLP_PKG.cf_outermost_lpnformula(CCH.CONTAINER_ENABLED_FLAG, CCE.OUTERMOST_LPN_ID) CF_OUTERMOST_LPN, 
	INV_INVARORE_XMLP_PKG.cf_cost_groupformula(CCE.COST_GROUP_ID) CF_COST_GROUP
from 
       mfg_lookups     mfg1,       mfg_lookups     mfg2,
       MTL_ITEM_LOCATIONS LOC,
       mtl_item_flexfields       sys,
       mtl_cycle_count_headers   cch,
       mtl_cycle_count_items     cci,
       mtl_cycle_count_entries   cce,
       mtl_abc_classes           abc   
where
       sys.organization_id  = :P_ORG_ID
 and   cce.organization_id = :P_ORG_ID
 and   cch.organization_id  = :P_ORG_ID
 and   abc.organization_id  = :P_ORG_ID
 and   cce.locator_id =  loc.inventory_location_id(+)
 and   cce.organization_id    =  loc.organization_id(+)
 and   cch.cycle_count_header_id = :P_HeaderID
 and   cce.cycle_count_header_id = :P_HeaderID
 and   (cce.entry_status_code  = 1
           or (cce.entry_status_code = 3 and :P_OverdueOnly <> 1 ) )
and  (      ((cce.count_due_date < :C_ReportDate) 
               and (:P_OverdueOnly = 1))
          or  :P_OverdueOnly <> 1) 
 and   sys.item_id              = cce.inventory_item_id
 and   cci.abc_class_id      = abc.abc_class_id
 and   cce.cycle_count_header_id = cci.cycle_count_header_id(+)
 and   cce.inventory_item_id     = cci.inventory_item_id (+)
 and   mfg1.lookup_type = 'MTL_CC_ENTRY_STATUSES'
 and   mfg1.lookup_code = cce.entry_status_code
 and  mfg2.lookup_type = 'MTL_CC_COUNT_TYPES'
 and  mfg2.lookup_code = cce.count_type_code
ORDER BY 
CCE.SUBINVENTORY, SYS.ITEM_NUMBER,CCE.REVISION,
:P_LOCATOR_FLEXSQL, CCE.CREATION_DATE,SERIAL_NUMBER
Parameter Name SQL text Validation
Organization
 
Number
Display Serial Numbers
 
LOV Oracle
Overdue Requests Only
 
LOV Oracle
Cycle Count Name
 
LOV Oracle