INV Physical inventory trend and summary analysis
Description
Categories: BI Publisher
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
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
Run
INV Physical inventory trend and summary analysis and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Report Option |
|
LOV Oracle | |
Physical Inventory |
|
LOV Oracle |