View: MSC_SR_ASSIGNMENTS_V

Product:	MSC - Advanced Supply Chain Planning
Description:
Implementation/DBA Data:	APPS.MSC_SR_ASSIGNMENTS_V

View Text

SELECT MSRA.ROWID ROW_ID
, MSRA.ASSIGNMENT_ID
, MSRA.ASSIGNMENT_SET_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.SR_INSTANCE_ID
, MSRA.SR_ASSIGNMENT_ID
, MSRA.SR_ASSIGNMENT_INSTANCE_ID
, MSRA.ORGANIZATION_ID
, DECODE(MSRA.ASSIGNMENT_TYPE
, 3
, NULL
, NVL(MSC_GET_NAME.ORG_CODE(MSRA.ORGANIZATION_ID
, MSRA.SR_INSTANCE_ID)
, MSC_GET_NAME.INSTANCE_CODE(MSRA.SR_INSTANCE_ID))) ORGANIZATION_CODE
, MSRA.PARTNER_ID CUSTOMER_ID
, MSC_GET_NAME.CUSTOMER(MSRA.PARTNER_ID) CUSTOMER_NAME
, MSRA.SHIP_TO_SITE_ID
, MST.PARTNER_ADDRESS SHIP_TO_ADDRESS
, DECODE(MSRA.ASSIGNMENT_TYPE
, 1
, NULL
, 2
, MSRA.CATEGORY_NAME
, 3
, MSC_GET_NAME.ITEM_NAME(MSRA.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, 4
, NULL
, 5
, MSRA.CATEGORY_NAME
, 6
, MSC_GET_NAME.ITEM_NAME(MSRA.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, NULL) ENTITY_NAME
, DECODE(MSRA.ASSIGNMENT_TYPE
, 1
, NULL
, 2
, MSC_GET_NAME.CATEGORY_DESC(MSRA.CATEGORY_NAME
, MSRA.CATEGORY_SET_ID
, MSRA.ORGANIZATION_ID
, MSRA.SR_INSTANCE_ID)
, 3
, MSC_GET_NAME.ITEM_DESC(MSRA.INVENTORY_ITEM_ID
, MSRA.ORGANIZATION_ID
, -1
, MSRA.SR_INSTANCE_ID)
, 4
, NULL
, 5
, MSC_GET_NAME.CATEGORY_DESC(MSRA.CATEGORY_NAME
, MSRA.CATEGORY_SET_ID
, MSRA.ORGANIZATION_ID
, MSRA.SR_INSTANCE_ID)
, 6
, MSC_GET_NAME.ITEM_DESC(MSRA.INVENTORY_ITEM_ID
, MSRA.ORGANIZATION_ID
, -1
, MSRA.SR_INSTANCE_ID)
, NULL) DESCRIPTION
, MSRA.SOURCING_RULE_TYPE
, ML.MEANING SOURCING_RULE_TYPE_TEXT
, MSRA.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, MSRA.CATEGORY_NAME
, MSRA.CATEGORY_SET_ID
, MSRA.INVENTORY_ITEM_ID
, MSRA.LAST_UPDATE_DATE
, MSRA.LAST_UPDATED_BY
, MSRA.CREATION_DATE
, MSRA.CREATED_BY
, MSRA.LAST_UPDATE_LOGIN
, MSRA.REQUEST_ID
, MSRA.PROGRAM_APPLICATION_ID
, MSRA.PROGRAM_ID
, MSRA.PROGRAM_UPDATE_DATE
, MSRA.ATTRIBUTE_CATEGORY
, MSRA.ATTRIBUTE1
, MSRA.ATTRIBUTE2
, MSRA.ATTRIBUTE3
, MSRA.ATTRIBUTE4
, MSRA.ATTRIBUTE5
, MSRA.ATTRIBUTE6
, MSRA.ATTRIBUTE7
, MSRA.ATTRIBUTE8
, MSRA.ATTRIBUTE9
, MSRA.ATTRIBUTE10
, MSRA.ATTRIBUTE11
, MSRA.ATTRIBUTE12
, MSRA.ATTRIBUTE13
, MSRA.ATTRIBUTE14
, MSRA.ATTRIBUTE15
, MSRA.REGION_ID
, TO_CHAR(NULL) REGION_CODE
, MSRA.COLLECTED_FLAG
FROM MFG_LOOKUPS ML
, MSC_TRADING_PARTNER_SITES MST
, MSC_SOURCING_RULES MSR
, MSC_SR_ASSIGNMENTS MSRA
WHERE MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND MST.PARTNER_SITE_ID (+) = MSRA.SHIP_TO_SITE_ID AND ML.LOOKUP_TYPE = 'MRP_SOURCING_RULE_TYPE' AND ML.LOOKUP_CODE = MSRA.SOURCING_RULE_TYPE AND MSRA.ASSIGNMENT_TYPE <= 6 UNION ALL SELECT MSRA.ROWID ROW_ID
, MSRA.ASSIGNMENT_ID
, MSRA.ASSIGNMENT_SET_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.SR_INSTANCE_ID
, MSRA.SR_ASSIGNMENT_ID
, MSRA.SR_ASSIGNMENT_INSTANCE_ID
, MSRA.ORGANIZATION_ID
, DECODE(MSRA.ASSIGNMENT_TYPE
, 3
, NULL
, NVL(MSC_GET_NAME.ORG_CODE(MSRA.ORGANIZATION_ID
, MSRA.SR_INSTANCE_ID)
, MSC_GET_NAME.INSTANCE_CODE(MSRA.SR_INSTANCE_ID))) ORGANIZATION_CODE
, MSRA.PARTNER_ID CUSTOMER_ID
, MSC_GET_NAME.CUSTOMER(MSRA.PARTNER_ID) CUSTOMER_NAME
, MSRA.SHIP_TO_SITE_ID
, TO_CHAR(NULL) SHIP_TO_ADDRESS
, DECODE(MSRA.ASSIGNMENT_TYPE
, 7
, NULL
, 8
, MSRA.CATEGORY_NAME
, 9
, MSC_GET_NAME.ITEM_NAME(MSRA.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, NULL) ENTITY_NAME
, DECODE(MSRA.ASSIGNMENT_TYPE
, 7
, NULL
, 8
, MSC_GET_NAME.CATEGORY_DESC(MSRA.CATEGORY_NAME
, MSRA.CATEGORY_SET_ID
, MSRA.ORGANIZATION_ID
, MSRA.SR_INSTANCE_ID)
, 9
, MSC_GET_NAME.ITEM_DESC(MSRA.INVENTORY_ITEM_ID
, MSRA.ORGANIZATION_ID
, -1
, MSRA.SR_INSTANCE_ID)
, NULL) DESCRIPTION
, MSRA.SOURCING_RULE_TYPE
, ML.MEANING SOURCING_RULE_TYPE_TEXT
, MSRA.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, MSRA.CATEGORY_NAME
, MSRA.CATEGORY_SET_ID
, MSRA.INVENTORY_ITEM_ID
, MSRA.LAST_UPDATE_DATE
, MSRA.LAST_UPDATED_BY
, MSRA.CREATION_DATE
, MSRA.CREATED_BY
, MSRA.LAST_UPDATE_LOGIN
, MSRA.REQUEST_ID
, MSRA.PROGRAM_APPLICATION_ID
, MSRA.PROGRAM_ID
, MSRA.PROGRAM_UPDATE_DATE
, MSRA.ATTRIBUTE_CATEGORY
, MSRA.ATTRIBUTE1
, MSRA.ATTRIBUTE2
, MSRA.ATTRIBUTE3
, MSRA.ATTRIBUTE4
, MSRA.ATTRIBUTE5
, MSRA.ATTRIBUTE6
, MSRA.ATTRIBUTE7
, MSRA.ATTRIBUTE8
, MSRA.ATTRIBUTE9
, MSRA.ATTRIBUTE10
, MSRA.ATTRIBUTE11
, MSRA.ATTRIBUTE12
, MSRA.ATTRIBUTE13
, MSRA.ATTRIBUTE14
, MSRA.ATTRIBUTE15
, MSRA.REGION_ID
, DECODE(MR.ZONE
, NULL
, (MR.COUNTRY || DECODE(MR.STATE
, NULL
, ''
, '
, '||MR.STATE) || DECODE(MR.CITY
, NULL
, ''
, '
, '||MR.CITY) || DECODE(MR.POSTAL_CODE_FROM
, NULL
, ''
, '
, '||MR.POSTAL_CODE_FROM) || DECODE(MR.POSTAL_CODE_TO
, NULL
, ''
, '-'||MR.POSTAL_CODE_TO))
, ZONE) REGION_CODE
, MSRA.COLLECTED_FLAG
FROM MFG_LOOKUPS ML
, MSC_REGIONS MR
, MSC_SOURCING_RULES MSR
, MSC_SR_ASSIGNMENTS MSRA WHERE MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND MR.REGION_ID = MSRA.REGION_ID AND MR.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID AND ML.LOOKUP_TYPE = 'MRP_SOURCING_RULE_TYPE' AND ML.LOOKUP_CODE = MSRA.SOURCING_RULE_TYPE AND MSRA.ASSIGNMENT_TYPE > 6

Columns