Reports2017-11-18T12:27:27+00:00

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
By continuing to use the site, you agree to the use of cookies. Accept