INV Physical inventory trend and summary analysis

Description

Imported from BI Publisher
Description: Physical inventory trend and summary analysis
Application: Inventory
Source: Physical inventory trend and summary analysis (XML)
Short Name: INVARPTS_XML
DB package: INV_INVARPTS_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

SELECT   decode(:P_report_option,1,MPI.PHYSICAL_INVENTORY_DATE ,sysdate)    PI_DATE,
         MPA.SUBINVENTORY_NAME           SUBINV,
         ROUND(SUM(MPA.SYSTEM_QUANTITY * nvl(MPA.ACTUAL_COST,0)),:C_std_prec)        SYSTEM_VALUE,
         ROUND(SUM(NVL(MPA.COUNT_QUANTITY,0) *  nvl(MPA.ACTUAL_COST,0)),:C_std_prec) COUNT_VALUE,
         ROUND(SUM((NVL(MPA.COUNT_QUANTITY,0)-MPA.SYSTEM_QUANTITY) 
             * nvl(MPA.ACTUAL_COST,0)),:C_std_prec)                           ADJMNT_VALUE,
         ROUND(SUM(ABS(NVL(MPA.COUNT_QUANTITY,0)-SYSTEM_QUANTITY)
             * nvl(MPA.ACTUAL_COST,0)),:C_std_prec)                            ABSOLUTE_VARIANCE,
         0                                           NUM_TAGS,
         COUNT  ( DECODE(MPA.ADJUSTMENT_QUANTITY,0,NULL,MPA.ADJUSTMENT_QUANTITY))     NUM_TXNS, 
	INV_INVARPTS_XMLP_PKG.c_adj_pctformula(ROUND ( SUM ( ( NVL ( MPA.COUNT_QUANTITY , 0 ) - MPA.SYSTEM_QUANTITY ) * nvl ( MPA.ACTUAL_COST , 0 ) ) , :C_std_prec ), ROUND ( SUM ( NVL ( MPA.COUNT_QUANTITY , 0 ) * nvl ( MPA.ACTUAL_COST , 0 ) ) , :C_std_prec )) C_adj_pct, 
	INV_INVARPTS_XMLP_PKG.c_var_pctformula(ROUND ( SUM ( ABS ( NVL ( MPA.COUNT_QUANTITY , 0 ) - SYSTEM_QUANTITY ) * nvl ( MPA.ACTUAL_COST , 0 ) ) , :C_std_prec ), ROUND ( SUM ( NVL ( MPA.COUNT_QUANTITY , 0 ) * nvl ( MPA.ACTUAL_COST , 0 ) ) , :C_std_prec )) C_var_pct, 
	INV_INVARPTS_XMLP_PKG.c_num_tagsformula(MPA.SUBINVENTORY_NAME) C_num_tags
FROM     MTL_PHYSICAL_INVENTORIES     MPI,
         MTL_PHYSICAL_ADJUSTMENTS     MPA
WHERE    MPA.ORGANIZATION_ID + 0 = :P_ORG_ID
  AND    MPI.ORGANIZATION_ID  = :P_ORG_ID
  AND    MPA.PHYSICAL_INVENTORY_ID = MPI.PHYSICAL_INVENTORY_ID
  AND    MPI.PHYSICAL_INVENTORY_ID = nvl(:P_PHYS_INV_ID, MPI.PHYSICAL_INVENTORY_ID)
&C_where_option
GROUP BY decode(:P_report_option,1,MPI.PHYSICAL_INVENTORY_DATE,sysdate),
         MPA.SUBINVENTORY_NAME
ORDER BY
decode(:P_report_option,1,MPI.PHYSICAL_INVENTORY_DATE,sysdate),
         MPA.SUBINVENTORY_NAME