MSC_SOURCING_LEVELS

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
ASSIGNMENT_TYPE ASSIGNMENT_SET_ID SOURCE_TYPE CUSTOMER_ID CUSTOMER_SITE_ID EFFECTIVE_DATE DISABLE_DATE INVENTORY_ITEM_ID ORGANIZATION_ID SOURCE_ORGANIZATION_ID SOURCE_PARTNER_ID SOURCE_PARTNER_SITE_ID ALLOCATION_PERCENT RANK CATEGORY_NAME SOURCING_LEVEL ASSIGNMENT_ID SOURCING_RULE_ID SOURCING_RULE_NAME SOURCING_RULE_TYPE COLLECTED_FLAG SR_RECEIPT_ID SR_SOURCE_ID PLANNING_ACTIVE SOURCE_PARTNER_SITE_CODE

Name

ASSIGNMENT_TYPE

ASSIGNMENT_SET_ID

SOURCE_TYPE

CUSTOMER_ID

CUSTOMER_SITE_ID

EFFECTIVE_DATE

DISABLE_DATE

INVENTORY_ITEM_ID

ORGANIZATION_ID

SOURCE_ORGANIZATION_ID

SOURCE_PARTNER_ID

SOURCE_PARTNER_SITE_ID

ALLOCATION_PERCENT

RANK

CATEGORY_NAME

SOURCING_LEVEL

ASSIGNMENT_ID

SOURCING_RULE_ID

SOURCING_RULE_NAME

SOURCING_RULE_TYPE

COLLECTED_FLAG

SR_RECEIPT_ID

SR_SOURCE_ID

PLANNING_ACTIVE

SOURCE_PARTNER_SITE_CODE

Query

SQL_Statement
Select MSA.ASSIGNMENT_TYPE, MSA.ASSIGNMENT_SET_ID, SOURCE_ORG.SOURCE_TYPE, MSA.PARTNER_ID Customer_Id, MSA.SHIP_TO_SITE_ID Customer_site_id, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, MSA.INVENTORY_ITEM_ID, MSA.ORGANIZATION_ID ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_PARTNER_ID , SOURCE_ORG.SOURCE_PARTNER_SITE_ID , SOURCE_ORG.ALLOCATION_PERCENT, SOURCE_ORG.RANK, MSA.CATEGORY_NAME, decode(MSA.ASSIGNMENT_TYPE, 6, 1, 5, 3, 3, decode(SOURCE_ORG.SOURCE_TYPE, 3, 4, 1, 5), 3, decode(MSR.SOURCING_RULE_TYPE, 1, 5, 4), 2, decode(SOURCE_ORG.SOURCE_TYPE, 3, 6, 1, 7), 2, DECODE(MSR.SOURCING_RULE_TYPE,1,7,6), 4, 8, 1, decode(SOURCE_ORG.SOURCE_TYPE, 3, 10, 1, 11), 1, DECODE(MSR.SOURCING_RULE_TYPE, 1,11,10) ) SOURCING_LEVEL, MSA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, MSR.SOURCING_RULE_TYPE, MSA.COLLECTED_FLAG, RECEIPT_ORG.SR_RECEIPT_ID, SOURCE_ORG.SR_SOURCE_ID, MSR.PLANNING_ACTIVE, SOURCE_ORG.SOURCE_PARTNER_SITE_CODE From MSC_SR_ASSIGNMENTS MSA, MSC_SOURCING_RULES MSR, MSC_SR_RECEIPT_ORG RECEIPT_ORG, MSC_SR_SOURCE_ORG SOURCE_ORG Where MSR.SOURCING_RULE_ID = MSA.SOURCING_RULE_ID AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID And MSR.STATUS = 1 AND MSA.ASSIGNMENT_TYPE not in (2,5,11,14) union Select MSA.ASSIGNMENT_TYPE, MSA.ASSIGNMENT_SET_ID, SOURCE_ORG.SOURCE_TYPE, MSA.PARTNER_ID Customer_Id, MSA.SHIP_TO_SITE_ID Customer_site_id, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, cat.INVENTORY_ITEM_ID, cat.ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_PARTNER_ID , SOURCE_ORG.SOURCE_PARTNER_SITE_ID , SOURCE_ORG.ALLOCATION_PERCENT, SOURCE_ORG.RANK, MSA.CATEGORY_NAME, decode(MSA.ASSIGNMENT_TYPE, 6, 1, 5, 3, 3, decode(SOURCE_ORG.SOURCE_TYPE, 3, 4, 1, 5), 3, decode(MSR.SOURCING_RULE_TYPE, 1, 5, 4), 2, decode(SOURCE_ORG.SOURCE_TYPE, 3, 6, 1, 7), 2, DECODE(MSR.SOURCING_RULE_TYPE,1,7,6), 4, 8, 1, decode(SOURCE_ORG.SOURCE_TYPE, 3, 10, 1, 11), 1, DECODE(MSR.SOURCING_RULE_TYPE, 1,11,10) ) SOURCING_LEVEL, MSA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, MSR.SOURCING_RULE_TYPE, MSA.COLLECTED_FLAG, RECEIPT_ORG.SR_RECEIPT_ID, SOURCE_ORG.SR_SOURCE_ID, MSR.PLANNING_ACTIVE, SOURCE_ORG.SOURCE_PARTNER_SITE_CODE From MSC_SR_ASSIGNMENTS MSA, msc_item_categories cat, MSC_SOURCING_RULES MSR, MSC_SR_RECEIPT_ORG RECEIPT_ORG, MSC_SR_SOURCE_ORG SOURCE_ORG Where MSR.SOURCING_RULE_ID = MSA.SOURCING_RULE_ID AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID and cat.category_id = msa.category_id and nvl(msa.organization_id, cat.organization_id) = cat.organization_id And MSR.STATUS = 1 AND MSA.ASSIGNMENT_TYPE in (2,5,11,14)

SQL_Statement

Select MSA.ASSIGNMENT_TYPE,

MSA.ASSIGNMENT_SET_ID,

SOURCE_ORG.SOURCE_TYPE,

MSA.PARTNER_ID Customer_Id,

MSA.SHIP_TO_SITE_ID Customer_site_id,

RECEIPT_ORG.EFFECTIVE_DATE,

RECEIPT_ORG.DISABLE_DATE,

MSA.INVENTORY_ITEM_ID,

MSA.ORGANIZATION_ID ORGANIZATION_ID,

SOURCE_ORG.SOURCE_ORGANIZATION_ID,

SOURCE_ORG.SOURCE_PARTNER_ID ,

SOURCE_ORG.SOURCE_PARTNER_SITE_ID ,

SOURCE_ORG.ALLOCATION_PERCENT,

SOURCE_ORG.RANK,

MSA.CATEGORY_NAME,

decode(MSA.ASSIGNMENT_TYPE,

6, 1,

5, 3,

3, decode(SOURCE_ORG.SOURCE_TYPE, 3, 4, 1, 5),

3, decode(MSR.SOURCING_RULE_TYPE, 1, 5, 4),

2, decode(SOURCE_ORG.SOURCE_TYPE, 3, 6, 1, 7),

2, DECODE(MSR.SOURCING_RULE_TYPE,1,7,6),

4, 8,

1, decode(SOURCE_ORG.SOURCE_TYPE, 3, 10, 1, 11),

1, DECODE(MSR.SOURCING_RULE_TYPE, 1,11,10)

) SOURCING_LEVEL,

MSA.ASSIGNMENT_ID,

MSR.SOURCING_RULE_ID,

MSR.SOURCING_RULE_NAME,

MSR.SOURCING_RULE_TYPE,

MSA.COLLECTED_FLAG,

RECEIPT_ORG.SR_RECEIPT_ID,

SOURCE_ORG.SR_SOURCE_ID,

MSR.PLANNING_ACTIVE,

SOURCE_ORG.SOURCE_PARTNER_SITE_CODE

From MSC_SR_ASSIGNMENTS MSA,

MSC_SOURCING_RULES MSR,

MSC_SR_RECEIPT_ORG RECEIPT_ORG,

MSC_SR_SOURCE_ORG SOURCE_ORG

Where MSR.SOURCING_RULE_ID = MSA.SOURCING_RULE_ID

AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID

AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID

And MSR.STATUS = 1

AND MSA.ASSIGNMENT_TYPE not in (2,5,11,14)

union