View: MSC_SCATP_ITEM_SR_LEVELS_V

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

View Text

SELECT /* LEVEL 1
, ASSIGNMENT TYPE 6: ITEM-ORG */ ITEM.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSRA.ASSIGNMENT_TYPE ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID VENDOR_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT ALLOCATION_PERCENT
, SOURCE_ORG.RANK RANK
, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE
, MSRA.CATEGORY_SET_ID CATEGORY_ID
, 1000 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE SOURCE_TYPE
, MSR.DESCRIPTION DESCRIPTION
, MSRA.PARTNER_ID CUSTOMER_ID
, MTSIL.SR_TP_SITE_ID SHIP_TO_SITE_ID
, MSRA.REGION_ID REGION_ID
, MSC_SATP_FUNC.GET_SESSION_ID SESSION_ID
FROM MSC_SYSTEM_ITEMS ITEM
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SOURCING_RULES MSR
, MSC_SR_ASSIGNMENTS MSRA
, MSC_TP_SITE_ID_LID MTSIL
WHERE MSRA.ASSIGNMENT_TYPE = 6 /* ITEM-ORG */
AND MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE
, TRUNC(SYSDATE)+1)) >TRUNC(SYSDATE)
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= TRUNC(SYSDATE)
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND ITEM.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEM.PLAN_ID = -1
AND ITEM.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEM.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND MTSIL.TP_SITE_ID = MSRA.SHIP_TO_SITE_ID
AND MTSIL.SR_INSTANCE_ID = ITEM.SR_INSTANCE_ID UNION ALL SELECT /* LEVEL 2
, ASSIGNMENT TYPE 9: ITEM-REGION */ ITEM.INVENTORY_ITEM_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_SET_ID
, 2000 + ((10 - NVL(MRT.REGION_TYPE
, 0)) * 10) + DECODE(MRT.ZONE_FLAG
, 'Y'
, 1
, 0) SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION DESCRIPTION
, TO_NUMBER(NULL) PARTNER_ID
, MRT.PARTNER_SITE_ID
, MSRA.REGION_ID
, MRT.SESSION_ID SESSION_ID
FROM MSC_SYSTEM_ITEMS ITEM
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SOURCING_RULES MSR
, MSC_SR_ASSIGNMENTS MSRA
, MSC_REGIONS_TEMP MRT
WHERE MSRA.ASSIGNMENT_TYPE = 9 /* ITEM-REGION */
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE
, TRUNC(SYSDATE)+1)) >TRUNC(SYSDATE)
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= TRUNC(SYSDATE)
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND ITEM.PLAN_ID = -1
AND ITEM.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEM.SR_INSTANCE_ID = SOURCE_ORG.SR_INSTANCE_ID
AND ITEM.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND MSRA.REGION_ID = MRT.REGION_ID
AND MRT.PARTNER_SITE_ID IS NOT NULL UNION ALL SELECT /* LEVEL3
, ASSIGNMENT_TYPE 5: CATEGORY-ORG */ CAT.INVENTORY_ITEM_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_SET_ID
, 3000 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION DESCRIPTION
, MSRA.PARTNER_ID
, MTSIL.SR_TP_SITE_ID SHIP_TO_SITE_ID
, MSRA.REGION_ID
, MSC_SATP_FUNC.GET_SESSION_ID
FROM MSC_ITEM_CATEGORIES CAT
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SOURCING_RULES MSR
, MSC_SR_ASSIGNMENTS MSRA
, MSC_TP_SITE_ID_LID MTSIL
WHERE MSRA.ASSIGNMENT_TYPE = 5 /* CATEGORY-ORG */
AND MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE
, TRUNC(SYSDATE)+1 )) >TRUNC(SYSDATE)
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= TRUNC(SYSDATE)
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
AND CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND MTSIL.TP_SITE_ID = MSRA.SHIP_TO_SITE_ID
AND MTSIL.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID UNION ALL SELECT /* LEVEL 4
, ASSIGNMENT TYPE3: ITEM */ ITEM.INVENTORY_ITEM_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_SET_ID
, 4000 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION DESCRIPTION
, TP.PARTNER_ID
, MTSIL.SR_TP_SITE_ID SHIP_TO_SITE_ID
, MSRA.REGION_ID
, MSC_SATP_FUNC.GET_SESSION_ID
FROM MSC_SYSTEM_ITEMS ITEM
, MSC_TRADING_PARTNER_SITES TP
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SOURCING_RULES MSR
, MSC_SR_ASSIGNMENTS MSRA
, MSC_TP_SITE_ID_LID MTSIL
WHERE MSRA .ASSIGNMENT_TYPE = 3 /* ITEM */
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE
, TRUNC(SYSDATE)+1)) >TRUNC(SYSDATE)
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) < = TRUNC(SYSDATE)
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND ITEM.PLAN_ID = -1
AND ITEM.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEM.SR_INSTANCE_ID = SOURCE_ORG.SR_INSTANCE_ID
AND ITEM.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND TP.PARTNER_TYPE = 2
AND MTSIL.TP_SITE_ID = TP.PARTNER_SITE_ID
AND MTSIL.SR_INSTANCE_ID = ITEM.SR_INSTANCE_ID UNION ALL SELECT /* LEVEL 5
, ASSIGNMENT TYPE 8: CATEGORY-REGION */ CAT.INVENTORY_ITEM_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_SET_ID
, 5000 + ((10 - NVL(MRT.REGION_TYPE
, 0)) * 10) + DECODE(MRT.ZONE_FLAG
, 'Y'
, 1
, 0) SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION DESCRIPTION
, TO_NUMBER(NULL) PARTNER_ID
, MRT.PARTNER_SITE_ID
, MSRA.REGION_ID
, MRT.SESSION_ID SESSION_ID
FROM MSC_ITEM_CATEGORIES CAT
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SOURCING_RULES MSR
, MSC_SR_ASSIGNMENTS MSRA
, MSC_REGIONS_TEMP MRT
WHERE MSRA.ASSIGNMENT_TYPE = 8 /* CATEGORY-REGION */
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE
, TRUNC(SYSDATE)+ 1)) >TRUNC(SYSDATE)
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= TRUNC(SYSDATE)
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SR_INSTANCE_ID
AND MSRA.REGION_ID = MRT.REGION_ID
AND MRT.PARTNER_SITE_ID IS NOT NULL UNION ALL SELECT /* LEVEL 6
, ASSIGNMENT TYPE 2: CATEGORY */ CAT.INVENTORY_ITEM_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_SET_ID
, 6000 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION DESCRIPTION
, TP.PARTNER_ID
, MTSIL.SR_TP_SITE_ID
, MSRA.REGION_ID
, MSC_SATP_FUNC.GET_SESSION_ID
FROM MSC_ITEM_CATEGORIES CAT
, MSC_TRADING_PARTNER_SITES TP
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SOURCING_RULES MSR
, MSC_SR_ASSIGNMENTS MSRA
, MSC_TP_SITE_ID_LID MTSIL
WHERE MSRA.ASSIGNMENT_TYPE = 2 /* CATEGORY */
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE
, TRUNC(SYSDATE)+1)) >TRUNC(SYSDATE)
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= TRUNC(SYSDATE)
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SR_INSTANCE_ID
AND TP.PARTNER_TYPE = 2
AND MTSIL.TP_SITE_ID = TP.PARTNER_SITE_ID
AND MTSIL.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID UNION ALL SELECT /* LEVEL 7
, ASSIGNMENT_TYPE 4: ORG */ ITEM.INVENTORY_ITEM_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_SET_ID
, 7000 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION DESCRIPTION
, MSRA.PARTNER_ID
, MTSIL.SR_TP_SITE_ID
, MSRA.REGION_ID
, MSC_SATP_FUNC.GET_SESSION_ID
FROM MSC_SYSTEM_ITEMS ITEM
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SOURCING_RULES MSR
, MSC_SR_ASSIGNMENTS MSRA
, MSC_TP_SITE_ID_LID MTSIL
WHERE MSRA.ASSIGNMENT_TYPE = 4 /* ORG */
AND MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE
, TRUNC(SYSDATE) +1)) >TRUNC(SYSDATE)
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= TRUNC(SYSDATE )
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND ITEM.PLAN_ID = -1
AND ITEM.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEM.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND MTSIL.TP_SITE_ID = MSRA.SHIP_TO_SITE_ID
AND MTSIL.SR_INSTANCE_ID = ITEM.SR_INSTANCE_ID UNION ALL SELECT /* LEVEL 8
, ASSIGNMENT_TYPE 7: REGION */ ITEM.INVENTORY_ITEM_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_SET_ID
, 8000 + ((10 - NVL(MRT.REGION_TYPE
, 0)) * 100) + DECODE(MRT .ZONE_FLAG
, 'Y'
, 1
, 0) SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION DESCRIPTION
, TO_NUMBER(NULL) PARTNER_ID
, MRT.PARTNER_SITE_ID
, MSRA.REGION_ID
, MRT.SESSION_ID SESSION_ID
FROM MSC_SYSTEM_ITEMS ITEM
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SOURCING_RULES MSR
, MSC_SR_ASSIGNMENTS MSRA
, MSC_REGIONS_TEMP MRT
WHERE MSRA.ASSIGNMENT_TYPE = 7 /* REGION */
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE
, TRUNC(SYSDATE)+1)) >TRUNC(SYSDATE)
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= TRUNC(SYSDATE)
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND ITEM.PLAN_ID = -1
AND ITEM.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEM.SR_INSTANCE_ID = SOURCE_ORG.SR_INSTANCE_ID
AND MSRA.REGION_ID = MRT.REGION_ID
AND MRT.PARTNER_SITE_ID IS NOT NULL UNION ALL SELECT /* LEVEL 9
, ASSIGNMENT_TYPE 1: GLOBAL */ ITEM.INVENTORY_ITEM_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_SET_ID
, 9000 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION DESCRIPTION
, TP.PARTNER_ID
, MTSIL.SR_TP_SITE_ID
, MSRA.REGION_ID
, MSC_SATP_FUNC.GET_SESSION_ID
FROM MSC_SYSTEM_ITEMS ITEM
, MSC_TRADING_PARTNER_SITES TP
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SOURCING_RULES MSR
, MSC_SR_ASSIGNMENTS MSRA
, MSC_TP_SITE_ID_LID MTSIL
WHERE MSRA.ASSIGNMENT_TYPE = 1 /* GLOBAL */
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE
, TRUNC(SYSDATE)+1)) >TRUNC(SYSDATE)
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= TRUNC(SYSDATE)
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND ITEM.PLAN_ID = -1
AND ITEM.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEM.SR_INSTANCE_ID = SOURCE_ORG.SR_INSTANCE_ID
AND TP.PARTNER_TYPE = 2
AND MTSIL.TP_SITE_ID = TP.PARTNER_SITE_ID
AND MTSIL.SR_INSTANCE_ID = ITEM.SR_INSTANCE_ID

Columns