VIEW: APPS.MTL_CYCLE_COUNT_ENTRIES_V

Source

View Text - Preformatted

SELECT CCE.ROWID ROW_ID , CCE.CYCLE_COUNT_ENTRY_ID , CCE.COUNT_LIST_SEQUENCE , CCE.COUNT_DATE_FIRST , CCE.COUNT_DATE_CURRENT , CCE.COUNT_DATE_PRIOR , CCE.COUNT_DATE_DUMMY , CCE.COUNTED_BY_EMPLOYEE_ID_FIRST , CCE.COUNTED_BY_EMPLOYEE_ID_CURRENT , CCE.COUNTED_BY_EMPLOYEE_ID_PRIOR , CCE.COUNTED_BY_EMPLOYEE_ID_DUMMY , CCE.APPROVER_EMPLOYEE_ID , CCE.COUNT_UOM_FIRST , CCE.COUNT_UOM_CURRENT , CCE.COUNT_UOM_PRIOR , CCE.COUNT_QUANTITY_FIRST , CCE.COUNT_QUANTITY_CURRENT , CCE.COUNT_QUANTITY_PRIOR , CCE.INVENTORY_ITEM_ID , IT.DESCRIPTION ITEM_DESCRIPTION , IT.LOT_CONTROL_CODE , IT.REVISION_QTY_CONTROL_CODE , IT.RESTRICT_SUBINVENTORIES_CODE , IT.RESTRICT_LOCATORS_CODE , IT.ALLOWED_UNITS_LOOKUP_CODE UOM_TYPE , IT.PRIMARY_UOM_CODE , IT.LOCATION_CONTROL_CODE , IT.POSITIVE_MEASUREMENT_ERROR , IT.NEGATIVE_MEASUREMENT_ERROR , IT.SERIAL_NUMBER_CONTROL_CODE , CCE.SUBINVENTORY , CCE.ENTRY_STATUS_CODE , CCE.COUNT_DUE_DATE , CCE.ORGANIZATION_ID , CCE.CYCLE_COUNT_HEADER_ID , CCE.NUMBER_OF_COUNTS , CCE.LOCATOR_ID , CCE.ADJUSTMENT_QUANTITY , CCE.ADJUSTMENT_DATE , CCE.ADJUSTMENT_AMOUNT , CCE.NEG_ADJUSTMENT_QUANTITY ,CCE.NEG_ADJUSTMENT_AMOUNT , CCE.ITEM_UNIT_COST , CCE.INVENTORY_ADJUSTMENT_ACCOUNT , CCE.APPROVAL_DATE , CCE.REVISION , CCE.LOT_NUMBER , CCE.LOT_CONTROL , CCE.SERIAL_DETAIL , CCE.APPROVAL_CONDITION , MFG2.MEANING APPROVAL_CONDITION_MEANING , CCE.SERIAL_NUMBER , CCE.SYSTEM_QUANTITY_FIRST , CCE.SYSTEM_QUANTITY_CURRENT , CCE.SYSTEM_QUANTITY_PRIOR , CCE.REFERENCE_FIRST , CCE.REFERENCE_CURRENT , CCE.REFERENCE_PRIOR , CCE.PRIMARY_UOM_QUANTITY_FIRST , CCE.PRIMARY_UOM_QUANTITY_CURRENT , CCE.PRIMARY_UOM_QUANTITY_PRIOR , CCE.COUNT_TYPE_CODE , CCE.TRANSACTION_REASON_ID , REA.REASON_NAME , REA.DESCRIPTION REASON_DESCRIPTION , CCE.APPROVAL_TYPE , CCE.LAST_UPDATE_DATE , CCE.LAST_UPDATED_BY , CCE.CREATION_DATE , CCE.CREATED_BY , CCE.LAST_UPDATE_LOGIN , CCE.REQUEST_ID , CCE.PROGRAM_APPLICATION_ID , CCE.PROGRAM_ID , CCE.PROGRAM_UPDATE_DATE , CCE.ATTRIBUTE_CATEGORY , CCE.ATTRIBUTE1 , CCE.ATTRIBUTE2 , CCE.ATTRIBUTE3 , CCE.ATTRIBUTE4 , CCE.ATTRIBUTE5 , CCE.ATTRIBUTE6 , CCE.ATTRIBUTE7 , CCE.ATTRIBUTE8 , CCE.ATTRIBUTE9 , CCE.ATTRIBUTE10 , CCE.ATTRIBUTE11 , CCE.ATTRIBUTE12 , CCE.ATTRIBUTE13 , CCE.ATTRIBUTE14 , CCE.ATTRIBUTE15 , CCI.ABC_CLASS_ID , CCI.APPROVAL_TOLERANCE_POSITIVE , CCI.APPROVAL_TOLERANCE_NEGATIVE , ABC.ABC_CLASS_NAME , CCC.APPROVAL_TOLERANCE_POSITIVE CLASS_APP_TOL_POS , CCC.APPROVAL_TOLERANCE_NEGATIVE CLASS_APP_TOL_NEG , CCC.COST_TOLERANCE_POSITIVE CLASS_COST_TOL_POS , CCC.COST_TOLERANCE_NEGATIVE CLASS_COST_TOL_NEG , DECODE(NVL(CCE.ENTRY_STATUS_CODE,1), 3, 3, 4, 2, 5, 1, 0) APPROVAL_STATUS , MFG.MEANING COUNT_STATUS , CCE.EXPORT_FLAG ,CCE.PARENT_LPN_ID ,CCE.OUTERMOST_LPN_ID ,CCE.STANDARD_OPERATION_ID ,CCE.TASK_PRIORITY ,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 ,CCE.COST_GROUP_ID ,CG.COST_GROUP COST_GROUP_NAME ,lpn1.cost_group_id container_cost_group_id ,cg2.cost_group container_cost_group_name , CCE.SECONDARY_ADJUSTMENT_QUANTITY , CCE.SECONDARY_SYSTEM_QTY_FIRST , CCE.SECONDARY_SYSTEM_QTY_CURRENT , CCE.SECONDARY_SYSTEM_QTY_PRIOR , CCE.SECONDARY_UOM_QUANTITY_FIRST , CCE.SECONDARY_UOM_QUANTITY_CURRENT , CCE.SECONDARY_UOM_QUANTITY_PRIOR , CCE.COUNT_SECONDARY_UOM_FIRST , CCE.COUNT_SECONDARY_UOM_CURRENT , CCE.COUNT_SECONDARY_UOM_PRIOR FROM MTL_SYSTEM_ITEMS IT, MTL_TRANSACTION_REASONS REA, MTL_CYCLE_COUNT_ITEMS CCI, MTL_CYCLE_COUNT_CLASSES CCC, MTL_ABC_CLASSES ABC, MFG_LOOKUPS MFG, MTL_CYCLE_COUNT_ENTRIES CCE, MFG_LOOKUPS MFG2, WMS_LICENSE_PLATE_NUMBERS LPN1, WMS_LICENSE_PLATE_NUMBERS LPN2, CST_COST_GROUPS CG ,cst_cost_groups cg2 WHERE IT.INVENTORY_ITEM_ID = CCE.INVENTORY_ITEM_ID AND IT.ORGANIZATION_ID = CCE.ORGANIZATION_ID AND CCI.CYCLE_COUNT_HEADER_ID = CCE.CYCLE_COUNT_HEADER_ID AND CCI.INVENTORY_ITEM_ID = CCE.INVENTORY_ITEM_ID AND CCC.CYCLE_COUNT_HEADER_ID = CCE.CYCLE_COUNT_HEADER_ID AND CCC.ABC_CLASS_ID = CCI.ABC_CLASS_ID AND CCC.ORGANIZATION_ID = CCE.ORGANIZATION_ID AND ABC.ABC_CLASS_ID = CCC.ABC_CLASS_ID AND ABC.ORGANIZATION_ID = CCC.ORGANIZATION_ID AND REA.REASON_ID(+) = CCE.TRANSACTION_REASON_ID AND MFG.LOOKUP_TYPE = 'MTL_CC_ENTRY_STATUSES' AND MFG.LOOKUP_CODE = CCE.ENTRY_STATUS_CODE AND MFG2.LOOKUP_TYPE (+) = 'MTL_CC_APPROVAL_CONDITION' AND MFG2.LOOKUP_CODE (+) = CCE.APPROVAL_CONDITION AND LPN1.LPN_ID (+) = CCE.PARENT_LPN_ID AND LPN2.LPN_ID (+) = CCE.OUTERMOST_LPN_ID AND CCE.COST_GROUP_ID = CG.COST_GROUP_ID(+) and lpn1.cost_group_id = cg2.cost_group_id (+)

View Text - HTML Formatted

SELECT CCE.ROWID ROW_ID
, CCE.CYCLE_COUNT_ENTRY_ID
, CCE.COUNT_LIST_SEQUENCE
, CCE.COUNT_DATE_FIRST
, CCE.COUNT_DATE_CURRENT
, CCE.COUNT_DATE_PRIOR
, CCE.COUNT_DATE_DUMMY
, CCE.COUNTED_BY_EMPLOYEE_ID_FIRST
, CCE.COUNTED_BY_EMPLOYEE_ID_CURRENT
, CCE.COUNTED_BY_EMPLOYEE_ID_PRIOR
, CCE.COUNTED_BY_EMPLOYEE_ID_DUMMY
, CCE.APPROVER_EMPLOYEE_ID
, CCE.COUNT_UOM_FIRST
, CCE.COUNT_UOM_CURRENT
, CCE.COUNT_UOM_PRIOR
, CCE.COUNT_QUANTITY_FIRST
, CCE.COUNT_QUANTITY_CURRENT
, CCE.COUNT_QUANTITY_PRIOR
, CCE.INVENTORY_ITEM_ID
, IT.DESCRIPTION ITEM_DESCRIPTION
, IT.LOT_CONTROL_CODE
, IT.REVISION_QTY_CONTROL_CODE
, IT.RESTRICT_SUBINVENTORIES_CODE
, IT.RESTRICT_LOCATORS_CODE
, IT.ALLOWED_UNITS_LOOKUP_CODE UOM_TYPE
, IT.PRIMARY_UOM_CODE
, IT.LOCATION_CONTROL_CODE
, IT.POSITIVE_MEASUREMENT_ERROR
, IT.NEGATIVE_MEASUREMENT_ERROR
, IT.SERIAL_NUMBER_CONTROL_CODE
, CCE.SUBINVENTORY
, CCE.ENTRY_STATUS_CODE
, CCE.COUNT_DUE_DATE
, CCE.ORGANIZATION_ID
, CCE.CYCLE_COUNT_HEADER_ID
, CCE.NUMBER_OF_COUNTS
, CCE.LOCATOR_ID
, CCE.ADJUSTMENT_QUANTITY
, CCE.ADJUSTMENT_DATE
, CCE.ADJUSTMENT_AMOUNT
, CCE.NEG_ADJUSTMENT_QUANTITY
, CCE.NEG_ADJUSTMENT_AMOUNT
, CCE.ITEM_UNIT_COST
, CCE.INVENTORY_ADJUSTMENT_ACCOUNT
, CCE.APPROVAL_DATE
, CCE.REVISION
, CCE.LOT_NUMBER
, CCE.LOT_CONTROL
, CCE.SERIAL_DETAIL
, CCE.APPROVAL_CONDITION
, MFG2.MEANING APPROVAL_CONDITION_MEANING
, CCE.SERIAL_NUMBER
, CCE.SYSTEM_QUANTITY_FIRST
, CCE.SYSTEM_QUANTITY_CURRENT
, CCE.SYSTEM_QUANTITY_PRIOR
, CCE.REFERENCE_FIRST
, CCE.REFERENCE_CURRENT
, CCE.REFERENCE_PRIOR
, CCE.PRIMARY_UOM_QUANTITY_FIRST
, CCE.PRIMARY_UOM_QUANTITY_CURRENT
, CCE.PRIMARY_UOM_QUANTITY_PRIOR
, CCE.COUNT_TYPE_CODE
, CCE.TRANSACTION_REASON_ID
, REA.REASON_NAME
, REA.DESCRIPTION REASON_DESCRIPTION
, CCE.APPROVAL_TYPE
, CCE.LAST_UPDATE_DATE
, CCE.LAST_UPDATED_BY
, CCE.CREATION_DATE
, CCE.CREATED_BY
, CCE.LAST_UPDATE_LOGIN
, CCE.REQUEST_ID
, CCE.PROGRAM_APPLICATION_ID
, CCE.PROGRAM_ID
, CCE.PROGRAM_UPDATE_DATE
, CCE.ATTRIBUTE_CATEGORY
, CCE.ATTRIBUTE1
, CCE.ATTRIBUTE2
, CCE.ATTRIBUTE3
, CCE.ATTRIBUTE4
, CCE.ATTRIBUTE5
, CCE.ATTRIBUTE6
, CCE.ATTRIBUTE7
, CCE.ATTRIBUTE8
, CCE.ATTRIBUTE9
, CCE.ATTRIBUTE10
, CCE.ATTRIBUTE11
, CCE.ATTRIBUTE12
, CCE.ATTRIBUTE13
, CCE.ATTRIBUTE14
, CCE.ATTRIBUTE15
, CCI.ABC_CLASS_ID
, CCI.APPROVAL_TOLERANCE_POSITIVE
, CCI.APPROVAL_TOLERANCE_NEGATIVE
, ABC.ABC_CLASS_NAME
, CCC.APPROVAL_TOLERANCE_POSITIVE CLASS_APP_TOL_POS
, CCC.APPROVAL_TOLERANCE_NEGATIVE CLASS_APP_TOL_NEG
, CCC.COST_TOLERANCE_POSITIVE CLASS_COST_TOL_POS
, CCC.COST_TOLERANCE_NEGATIVE CLASS_COST_TOL_NEG
, DECODE(NVL(CCE.ENTRY_STATUS_CODE
, 1)
, 3
, 3
, 4
, 2
, 5
, 1
, 0) APPROVAL_STATUS
, MFG.MEANING COUNT_STATUS
, CCE.EXPORT_FLAG
, CCE.PARENT_LPN_ID
, CCE.OUTERMOST_LPN_ID
, CCE.STANDARD_OPERATION_ID
, CCE.TASK_PRIORITY
, 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
, CCE.COST_GROUP_ID
, CG.COST_GROUP COST_GROUP_NAME
, LPN1.COST_GROUP_ID CONTAINER_COST_GROUP_ID
, CG2.COST_GROUP CONTAINER_COST_GROUP_NAME
, CCE.SECONDARY_ADJUSTMENT_QUANTITY
, CCE.SECONDARY_SYSTEM_QTY_FIRST
, CCE.SECONDARY_SYSTEM_QTY_CURRENT
, CCE.SECONDARY_SYSTEM_QTY_PRIOR
, CCE.SECONDARY_UOM_QUANTITY_FIRST
, CCE.SECONDARY_UOM_QUANTITY_CURRENT
, CCE.SECONDARY_UOM_QUANTITY_PRIOR
, CCE.COUNT_SECONDARY_UOM_FIRST
, CCE.COUNT_SECONDARY_UOM_CURRENT
, CCE.COUNT_SECONDARY_UOM_PRIOR
FROM MTL_SYSTEM_ITEMS IT
, MTL_TRANSACTION_REASONS REA
, MTL_CYCLE_COUNT_ITEMS CCI
, MTL_CYCLE_COUNT_CLASSES CCC
, MTL_ABC_CLASSES ABC
, MFG_LOOKUPS MFG
, MTL_CYCLE_COUNT_ENTRIES CCE
, MFG_LOOKUPS MFG2
, WMS_LICENSE_PLATE_NUMBERS LPN1
, WMS_LICENSE_PLATE_NUMBERS LPN2
, CST_COST_GROUPS CG
, CST_COST_GROUPS CG2
WHERE IT.INVENTORY_ITEM_ID = CCE.INVENTORY_ITEM_ID
AND IT.ORGANIZATION_ID = CCE.ORGANIZATION_ID
AND CCI.CYCLE_COUNT_HEADER_ID = CCE.CYCLE_COUNT_HEADER_ID
AND CCI.INVENTORY_ITEM_ID = CCE.INVENTORY_ITEM_ID
AND CCC.CYCLE_COUNT_HEADER_ID = CCE.CYCLE_COUNT_HEADER_ID
AND CCC.ABC_CLASS_ID = CCI.ABC_CLASS_ID
AND CCC.ORGANIZATION_ID = CCE.ORGANIZATION_ID
AND ABC.ABC_CLASS_ID = CCC.ABC_CLASS_ID
AND ABC.ORGANIZATION_ID = CCC.ORGANIZATION_ID
AND REA.REASON_ID(+) = CCE.TRANSACTION_REASON_ID
AND MFG.LOOKUP_TYPE = 'MTL_CC_ENTRY_STATUSES'
AND MFG.LOOKUP_CODE = CCE.ENTRY_STATUS_CODE
AND MFG2.LOOKUP_TYPE (+) = 'MTL_CC_APPROVAL_CONDITION'
AND MFG2.LOOKUP_CODE (+) = CCE.APPROVAL_CONDITION
AND LPN1.LPN_ID (+) = CCE.PARENT_LPN_ID
AND LPN2.LPN_ID (+) = CCE.OUTERMOST_LPN_ID
AND CCE.COST_GROUP_ID = CG.COST_GROUP_ID(+)
AND LPN1.COST_GROUP_ID = CG2.COST_GROUP_ID (+)