VIEW: APPS.MTL_PHYSICAL_ADJUSTMENTS_V

Source

View Text - Preformatted

SELECT M.ROWID ROW_ID , M.ORGANIZATION_ID , M.PHYSICAL_INVENTORY_ID , M.ADJUSTMENT_ID , DECODE(M.APPROVAL_STATUS,NULL,0,3,0,M.APPROVAL_STATUS) APPROVAL_STATUS , M.INVENTORY_ITEM_ID , MSI.PRIMARY_UOM_CODE UOM , M.ADJUSTMENT_QUANTITY , ROUND((M.ACTUAL_COST * M.ADJUSTMENT_QUANTITY),5) VALUE , ROUND((M.ADJUSTMENT_QUANTITY * 100 / DECODE(M.SYSTEM_QUANTITY, 0, NULL, M.SYSTEM_QUANTITY)),5) PCT , M.SYSTEM_QUANTITY , M.COUNT_QUANTITY , M.REVISION , M.SUBINVENTORY_NAME , M.LOCATOR_ID , M.LOT_NUMBER , M.SERIAL_NUMBER , M.APPROVED_BY_EMPLOYEE_ID EMPLOYEE_ID , MSI.DESCRIPTION ITEM_DESCRIPTION , M.CREATION_DATE , M.CREATED_BY , M.LAST_UPDATE_DATE , M.LAST_UPDATED_BY , M.LAST_UPDATE_LOGIN , M.ACTUAL_COST , M.PARENT_LPN_ID , M.OUTERMOST_LPN_ID ,LPN1.LICENSE_PLATE_NUMBER PARENT_LPN ,LPN2.LICENSE_PLATE_NUMBER OUTERMOST_LPN ,LPN1.INVENTORY_ITEM_ID CONTAINER_ITEM_ID ,LPN1.REVISION CONTAINER_REVISION ,LPN1.LOT_NUMBER CONTAINER_LOT_NUMBER ,LPN1.SERIAL_NUMBER CONTAINER_SERIAL_NUMBER ,M.COST_GROUP_ID ,CG.COST_GROUP COST_GROUP_NAME ,lpn1.cost_group_id container_cost_group_id ,cg2.cost_group container_cost_group_name FROM MTL_SYSTEM_ITEMS MSI, MTL_PHYSICAL_ADJUSTMENTS M, WMS_LICENSE_PLATE_NUMBERS LPN1, WMS_LICENSE_PLATE_NUMBERS LPN2 ,CST_COST_GROUPS CG ,cst_cost_groups cg2 WHERE MSI.ORGANIZATION_ID = M.ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = M.INVENTORY_ITEM_ID AND M.PARENT_LPN_ID = LPN1.LPN_ID(+) AND M.OUTERMOST_LPN_ID = LPN2.LPN_ID(+) AND M.COST_GROUP_ID = CG.COST_GROUP_ID(+) and lpn1.cost_group_id = cg2.cost_group_id(+)

View Text - HTML Formatted

SELECT M.ROWID ROW_ID
, M.ORGANIZATION_ID
, M.PHYSICAL_INVENTORY_ID
, M.ADJUSTMENT_ID
, DECODE(M.APPROVAL_STATUS
, NULL
, 0
, 3
, 0
, M.APPROVAL_STATUS) APPROVAL_STATUS
, M.INVENTORY_ITEM_ID
, MSI.PRIMARY_UOM_CODE UOM
, M.ADJUSTMENT_QUANTITY
, ROUND((M.ACTUAL_COST * M.ADJUSTMENT_QUANTITY)
, 5) VALUE
, ROUND((M.ADJUSTMENT_QUANTITY * 100 / DECODE(M.SYSTEM_QUANTITY
, 0
, NULL
, M.SYSTEM_QUANTITY))
, 5) PCT
, M.SYSTEM_QUANTITY
, M.COUNT_QUANTITY
, M.REVISION
, M.SUBINVENTORY_NAME
, M.LOCATOR_ID
, M.LOT_NUMBER
, M.SERIAL_NUMBER
, M.APPROVED_BY_EMPLOYEE_ID EMPLOYEE_ID
, MSI.DESCRIPTION ITEM_DESCRIPTION
, M.CREATION_DATE
, M.CREATED_BY
, M.LAST_UPDATE_DATE
, M.LAST_UPDATED_BY
, M.LAST_UPDATE_LOGIN
, M.ACTUAL_COST
, M.PARENT_LPN_ID
, M.OUTERMOST_LPN_ID
, LPN1.LICENSE_PLATE_NUMBER PARENT_LPN
, LPN2.LICENSE_PLATE_NUMBER OUTERMOST_LPN
, LPN1.INVENTORY_ITEM_ID CONTAINER_ITEM_ID
, LPN1.REVISION CONTAINER_REVISION
, LPN1.LOT_NUMBER CONTAINER_LOT_NUMBER
, LPN1.SERIAL_NUMBER CONTAINER_SERIAL_NUMBER
, M.COST_GROUP_ID
, CG.COST_GROUP COST_GROUP_NAME
, LPN1.COST_GROUP_ID CONTAINER_COST_GROUP_ID
, CG2.COST_GROUP CONTAINER_COST_GROUP_NAME
FROM MTL_SYSTEM_ITEMS MSI
, MTL_PHYSICAL_ADJUSTMENTS M
, WMS_LICENSE_PLATE_NUMBERS LPN1
, WMS_LICENSE_PLATE_NUMBERS LPN2
, CST_COST_GROUPS CG
, CST_COST_GROUPS CG2
WHERE MSI.ORGANIZATION_ID = M.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = M.INVENTORY_ITEM_ID
AND M.PARENT_LPN_ID = LPN1.LPN_ID(+)
AND M.OUTERMOST_LPN_ID = LPN2.LPN_ID(+)
AND M.COST_GROUP_ID = CG.COST_GROUP_ID(+)
AND LPN1.COST_GROUP_ID = CG2.COST_GROUP_ID(+)