INV Cycle count hit/miss analysis

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Cycle count hit/miss analysis (XML)
Short Name: INVARHMA_XML
DB package: INV_INVARHMA_XMLP_PKG
select  
      cce.cycle_count_entry_id,
      cce.subinventory                       Subinventory,
      abc.abc_class_name,
      1                                                   EntryFlag,
      cce.system_quantity_first        sys_quantity,
      nvl(cla.hit_miss_tolerance_positive, cch.hit_miss_tolerance_positive)    pos_tol,
      nvl(cla.hit_miss_tolerance_negative, cch.hit_miss_tolerance_negative)   neg_tol,
      (cce.count_quantity_first  -  
                             cce.system_quantity_first)
                                                           adj_quantity,
      serial_detail,
      serial_number, 
	INV_INVARHMA_XMLP_PKG.fs_accuracypercentformula(:SS_TotalEntries, :SS_TotalOutTolerance) FS_AccuracyPercent, 
	INV_INVARHMA_XMLP_PKG.f_accuracypercentformula(:F_TotalEntries, :C_TotalOutTolerance) F_AccuracyPercent, 
	INV_INVARHMA_XMLP_PKG.f_outtoleranceflagformula(serial_detail, serial_number, ( cce.count_quantity_first - cce.system_quantity_first ), cce.system_quantity_first, nvl ( cla.hit_miss_tolerance_negative , cch.hit_miss_tolerance_negative ), nvl ( cla.hit_miss_tolerance_positive , cch.hit_miss_tolerance_positive )) F_OutToleranceFlag
from 
      mtl_abc_classes                abc,
      mtl_cycle_count_classes     cla, 
      mtl_cycle_count_headers   cch,
       mtl_cycle_count_items       cci,
       mtl_cycle_count_entries     cce
where
          cch.organization_id  = :P_ORG_ID
 and   cce.organization_id  = :P_ORG_ID
 and   cce.inventory_item_id = cci.inventory_item_id
 and   cci.abc_class_id   = abc.abc_class_id
 and   cci.abc_class_id   = cla.abc_class_id
 and   cla.cycle_count_header_id = :P_HeaderId
 and   cla.organization_id  = :P_Org_Id
 and   abc.organization_id = :P_Org_id
 and   cci.cycle_count_header_id  = :P_HeaderID
 and   cch.cycle_count_header_id = :P_HeaderID
 and   cce.cycle_count_header_id = :P_HeaderID
 and   ( cce.entry_status_code =  5  or
            cce.entry_status_code =  2 or
            cce.entry_status_code = 3) 
 and    cce.count_type_code <> 4
/* and   TO_DATE(CCE.count_date_first,'DD-MON-RRRR') <=  :P_ToDate
 and   TO_DATE(CCE.count_date_first,'DD-MON-RRRR')>=  :P_FromDate*/
 and   TO_DATE(CCE.count_date_first,'DD-MON-RRRR') <=  trunc(:P_ToDate)
 and   TO_DATE(CCE.count_date_first,'DD-MON-RRRR')>=  trunc(:P_FromDate)
ORDER BY 
cce.subinventory, abc.abc_class_name
Parameter Name SQL text Validation
Organization
 
Number
End Date
 
Date
Start Date
 
Date
Cycle Count Name
 
LOV Oracle
Ask a question