View: WMS_OP_PLAN_DETAILS_V

Product:	WMS - Warehouse Management
Description:	Detail level Operation Information
Implementation/DBA Data:	APPS.WMS_OP_PLAN_DETAILS_V

View Text

SELECT D.OPERATION_PLAN_ID
, D.OPERATION_PLAN_DETAIL_ID
, D.OPERATION_TYPE
, D.OPERATION_SEQUENCE
, D.BULK_PICK_TYPE
, D.DROP_LPN_OPTION
, D.WAIT_FOR_GROUP_COMPLETION
, D.SYSTEM_DISPATCHED
, D.OP_SEGMENT_COMPLETED
, D.ZONE_SELECTION_CRITERIA
, D.PRE_SPECIFIED_ZONE_ID
, D.ZONE_SELECTION_API_ID
, D.SUB_SELECTION_CRITERIA
, D.PRE_SPECIFIED_SUB_CODE
, D.SUB_SELECTION_API_ID
, D.LOC_SELECTION_CRITERIA
, D.PRE_SPECIFIED_LOC_ID
, D.LOC_SELECTION_API_ID
, D.ACTIVITY_SEGMENT
, D.CREATED_BY
, D.CREATION_DATE
, D.LAST_UPDATED_BY
, D.LAST_UPDATE_DATE
, D.LAST_UPDATE_LOGIN
, D.ATTRIBUTE_CATEGORY
, D.ATTRIBUTE1
, D.ATTRIBUTE2
, D.ATTRIBUTE3
, D.ATTRIBUTE4
, D.ATTRIBUTE5
, D.ATTRIBUTE6
, D.ATTRIBUTE7
, D.ATTRIBUTE8
, D.ATTRIBUTE9
, D.ATTRIBUTE10
, D.ATTRIBUTE11
, D.ATTRIBUTE12
, D.ATTRIBUTE13
, D.ATTRIBUTE14
, D.ATTRIBUTE15
, D.LPN_SELECTION_CRITERIA
, D.LPN_SELECTION_API_ID
, D.LOC_MTRL_GRP_RULE_ID
, D.LPN_MTRL_GRP_RULE_ID
, D.IS_IN_INVENTORY
, ML1.MEANING DEST_ZONE_SUG_METHOD
, ML2.MEANING DEST_SUB_SUG_METHOD
, ML3.MEANING DEST_LOC_SUG_METHOD
, ML4.MEANING DEST_LPN_SUG_METHOD
, ML5.MEANING DROP_LPN_OPTION_NAME
, ML6.MEANING LOC_MTRL_GRP_RULE
, ML7.MEANING LPN_MTRL_GRP_RULE
, ML9.MEANING OPERATION_NAME
, DECODE(WOPB.ACTIVITY_TYPE_ID
, 1
, WAHC_LOC.LOC_DEST_SUG_API
, NULL) LOC_DEST_SUG_API
, WAHC_LPN.LPN_DEST_SUG_API LPN_DEST_SUG_API
, ZONE.ZONE_NAME ZONE_NAME
, NVL(MILK.CONCATENATED_SEGMENTS
, '') LOC_CONCATENATED_SEGMENTS
, SUB_PLAN.OPERATION_PLAN_NAME SUBSEQUENT_OP_PLAN_NAME
, SUB_PLAN.OPERATION_PLAN_ID SUBSEQUENT_OP_PLAN_ID
, ML10.MEANING CONSOL_METHOD_NAME
, D.CONSOLIDATION_METHOD_ID CONSOLIDATION_METHOD_ID
FROM WMS_OP_PLAN_DETAILS D
, WMS_OP_PLANS_B WOPB
, (SELECT LOOKUP_CODE
, MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'WMS_OP_DEST_SELECTION_MODE') ML1
, /* ZONE METHOD*/ (SELECT LOOKUP_CODE
, MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'WMS_OP_DEST_SELECTION_MODE') ML2
, /* SUB METHOD */ (SELECT LOOKUP_CODE
, MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'WMS_OP_DEST_SELECTION_MODE') ML3
, /* LOC METHOD */ (SELECT LOOKUP_CODE
, MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'WMS_OP_DEST_SELECTION_MODE') ML4
, /* LPN METHOD */ (SELECT LOOKUP_CODE
, MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'WMS_DROP_LPN_OPTION') ML5
, /* DROP LPN OPTION */ (SELECT LOOKUP_CODE
, MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'WMS_MTRL_GRP_RULES') ML6
, /* LOC MMG */ (SELECT LOOKUP_CODE
, MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'WMS_MTRL_GRP_RULES') ML7
, /* LPN MMG */ MFG_LOOKUPS ML9
, /* OPERATION NAME */ (SELECT ZONE_ID
, ZONE_NAME
FROM WMS_ZONES_TL
WHERE LANGUAGE = USERENV('LANG') ) ZONE
, MTL_ITEM_LOCATIONS_KFV MILK
, (SELECT ML.MEANING LOC_DEST_SUG_API
, WAHC1.HOOK_CALL_ID
FROM WMS_API_HOOK_CALLS WAHC1
, MFG_LOOKUPS ML
WHERE ML.LOOKUP_TYPE ='WMS_CHILD_SHORT_NAMES'
AND ML.LOOKUP_CODE = WAHC1.DESCRIPTION_ID UNION SELECT WAHC.DESCRIPTION
, WAHC.HOOK_CALL_ID
FROM WMS_API_HOOK_CALLS WAHC
WHERE SEED_FLAG = 'N'
AND MODULE_HOOK_ID = 100) WAHC_LOC
, (SELECT ML.MEANING LPN_DEST_SUG_API
, WAHC2.HOOK_CALL_ID
FROM WMS_API_HOOK_CALLS WAHC2
, MFG_LOOKUPS ML
WHERE ML.LOOKUP_TYPE ='WMS_CHILD_SHORT_NAMES'
AND ML.LOOKUP_CODE = WAHC2.DESCRIPTION_ID UNION SELECT WAHC.DESCRIPTION
, WAHC.HOOK_CALL_ID
FROM WMS_API_HOOK_CALLS WAHC
WHERE SEED_FLAG = 'N'
AND MODULE_HOOK_ID = 200) WAHC_LPN
, (SELECT OPERATION_PLAN_NAME
, OPERATION_PLAN_ID
FROM WMS_OP_PLANS_VL
WHERE USER_DEFINED = 'N'
AND ACTIVITY_TYPE_ID = 2) SUB_PLAN /* SUBSEQUENT SEEDED OUTBOUND PLAN*/
, (SELECT MEANING
, LOOKUP_CODE
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'WMS_CONSOL_METHODS') ML10 /* CONSOLIDATION METHOD */
WHERE D.ZONE_SELECTION_CRITERIA = ML1.LOOKUP_CODE (+)
AND D.SUB_SELECTION_CRITERIA = ML2.LOOKUP_CODE (+)
AND D.LOC_SELECTION_CRITERIA = ML3.LOOKUP_CODE (+)
AND D.LPN_SELECTION_CRITERIA = ML4.LOOKUP_CODE (+)
AND D.DROP_LPN_OPTION = ML5.LOOKUP_CODE (+)
AND D.LOC_MTRL_GRP_RULE_ID = ML6.LOOKUP_CODE (+)
AND D.LPN_MTRL_GRP_RULE_ID = ML7.LOOKUP_CODE (+)
AND ML9.LOOKUP_TYPE = 'WMS_OPERATION_TYPE'
AND D.OPERATION_TYPE = ML9.LOOKUP_CODE
AND D.PRE_SPECIFIED_ZONE_ID = ZONE.ZONE_ID (+)
AND D.PRE_SPECIFIED_LOC_ID = MILK.INVENTORY_LOCATION_ID (+)
AND D.ORGANIZATION_ID = MILK.ORGANIZATION_ID (+)
AND D.LOC_SELECTION_API_ID = WAHC_LOC.HOOK_CALL_ID (+)
AND D.LPN_SELECTION_API_ID = WAHC_LPN.HOOK_CALL_ID (+)
AND D.OPERATION_PLAN_ID = WOPB.OPERATION_PLAN_ID
AND D.SUBSEQUENT_OP_PLAN_ID = SUB_PLAN.OPERATION_PLAN_ID (+)
AND D.CONSOLIDATION_METHOD_ID = ML10.LOOKUP_CODE (+)

Columns