INV Physical inventory trend and summary analysis
Description
- Categories
- BI Publisher
- Application
- Inventory
- Source
- Physical inventory trend and summary analysis (XML)
- Short Name
- INVARPTS_XML
- DB package
- INV_INVARPTS_XMLP_PKG
Imported from BI Publisher
Physical inventory trend and summary analysis
Physical inventory trend and summary analysis
SQL 26 lines
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 |
Parameters 2
| Parameter | Type | List of values SQL |
|---|---|---|
| Report Option | LOV Oracle | |
| Physical Inventory | LOV Oracle |