VIEW: APPS.MSC_SC_BILL_SF_V

Source

View Text - Preformatted

SELECT Distinct sources.ALLOCATION_PERCENT || '% ' || msc_get_name.lookup_meaning('MRP_SOURCE_TYPE', DECODE(PT2.organization_code,NULL,3,1)) || ' ' || nvl(PT2.organization_code, pT.PARTNER_NAME || '-' || PTS.TP_SITE_CODE) , SOURCES.ASSIGNMENT_SET_ID , SOURCES.ASSIGNMENT_TYPE , SOURCES.SOURCING_RULE_NAME , SOURCES.ORGANIZATION_ID , SOURCES.SR_INSTANCE_ID , PT1.ORGANIZATION_CODE , SOURCES.INVENTORY_ITEM_ID , ITEM.ITEM_NAME , NVL(SOURCES.SOURCE_ORGANIZATION_ID, 0) , SOURCES.SOURCE_ORG_INSTANCE_ID , PT2.ORGANIZATION_CODE , SOURCES.INVENTORY_ITEM_ID , ITEM.ITEM_NAME , CAL.CALENDAR_DATE , TO_NUMBER(NULL) , SOURCES.ALLOCATION_PERCENT , SOURCES.SHIP_METHOD , SOURCES.AVG_TRANSIT_LEAD_TIME , SOURCES.RANK , PT.PARTNER_NAME , PTS.TP_SITE_CODE , SOURCES.VENDOR_ID , SOURCES.VENDOR_SITE_ID , NULL , ITEM.DESCRIPTION ,pt2.modeled_supplier_id ,pt2.modeled_supplier_site_id ,sources.circular_src FROM MSC_TRADING_PARTNERS PT, MSC_TRADING_PARTNERS PT2, MSC_TRADING_PARTNERS PT1, MSC_TRADING_PARTNER_SITES PTS, MSC_ITEMS ITEM, MSC_CALENDAR_DATES CAL, MSC_ASSIGNMENT_SETS SETS, MSC_SOURCES_V SOURCES WHERE PT.PARTNER_ID(+) = SOURCES.VENDOR_ID AND PTS.PARTNER_SITE_ID(+) = SOURCES.VENDOR_SITE_ID AND PT2.SR_TP_ID (+) = SOURCES.SOURCE_ORGANIZATION_ID AND PT2.SR_INSTANCE_ID(+) = SOURCES.SOURCE_ORG_INSTANCE_ID AND PT2.PARTNER_TYPE(+)=3 AND ITEM.INVENTORY_ITEM_ID = SOURCES.INVENTORY_ITEM_ID AND CAL.CALENDAR_CODE = PT1.CALENDAR_CODE AND CAL.EXCEPTION_SET_ID = PT1.CALENDAR_EXCEPTION_SET_ID AND CAL.CALENDAR_DATE BETWEEN SOURCES.EFFECTIVE_DATE AND NVL(SOURCES.DISABLE_DATE, TO_DATE(2634525, 'J')) AND PT1.SR_TP_ID = SOURCES.ORGANIZATION_ID AND PT1.SR_INSTANCE_ID = SOURCES.SR_INSTANCE_ID AND PT1.PARTNER_TYPE = 3 AND SETS.ASSIGNMENT_SET_ID = NVL(SOURCES.ASSIGNMENT_SET_ID, SETS.ASSIGNMENT_SET_ID) AND ((SOURCES.ORGANIZATION_ID != NVL(SOURCES.SOURCE_ORGANIZATION_ID,1))) UNION ALL SELECT DISTINCT round( bic.USAGE_QUANTITY,6) || 'x ' || ITEM2.ITEM_NAME || '/' || pt.organization_code, SETS.ASSIGNMENT_SET_ID, TO_NUMBER(NULL), TO_CHAR(NULL), PT.SR_TP_ID, PT.SR_INSTANCE_ID, PT.ORGANIZATION_CODE, BBM.ASSEMBLY_ITEM_ID, ITEM1.ITEM_NAME, BBM.ORGANIZATION_ID, BBM.SR_INSTANCE_ID, PT.ORGANIZATION_CODE, BIC.INVENTORY_ITEM_ID, ITEM2.ITEM_NAME, CAL.CALENDAR_DATE, BIC.USAGE_QUANTITY, TO_NUMBER(NULL), TO_CHAR(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), BBM.ALTERNATE_BOM_DESIGNATOR, ITEM2.DESCRIPTION, pt.modeled_supplier_id, pt.modeled_supplier_site_id, 'N' FROM MSC_system_ITEMS ITEM2, MSC_ITEMS ITEM1, MSC_CALENDAR_DATES CAL, MSC_BOM_COMPONENTS BIC, MSC_BOMS BBM, MSC_TRADING_PARTNERS PT, MSC_ASSIGNMENT_SETS SETS WHERE BIC.ORGANIZATION_ID = PT.SR_TP_ID AND BIC.SR_INSTANCE_ID = PT.SR_INSTANCE_ID AND ITEM2.INVENTORY_ITEM_ID = BIC.INVENTORY_ITEM_ID AND ITEM2.SR_INSTANCE_ID = BIC.SR_INSTANCE_ID AND ITEM2.ORGANIZATION_ID = BIC.ORGANIZATION_ID AND ITEM2.PLAN_ID = BIC.PLAN_ID AND BBM.ORGANIZATION_ID = PT.SR_TP_ID AND BBM.SR_INSTANCE_ID = PT.SR_INSTANCE_ID AND PT.PARTNER_TYPE = 3 AND BBM.PLAN_ID = -1 AND BIC.PLAN_ID = BBM.PLAN_ID AND BIC.BILL_SEQUENCE_ID = BBM.BILL_SEQUENCE_ID AND BIC.SR_INSTANCE_ID = BBM.SR_INSTANCE_ID AND nvl(BIC.COMPONENT_TYPE, -1) <> 10 AND ITEM1.INVENTORY_ITEM_ID = BBM.ASSEMBLY_ITEM_ID AND CAL.CALENDAR_CODE = PT.CALENDAR_CODE AND CAL.EXCEPTION_SET_ID = PT.CALENDAR_EXCEPTION_SET_ID AND CAL.SR_INSTANCE_ID = PT.SR_INSTANCE_ID AND CAL.CALENDAR_DATE BETWEEN trunc(BIC.EFFECTIVITY_DATE) AND NVL(BIC.DISABLE_DATE, TO_DATE(2634525, 'J')) AND NOT EXISTS (SELECT NULL FROM MSC_ITEM_LOWEST_SR_LEVEL_V SOURCES WHERE SOURCES.ORGANIZATION_ID = PT.SR_TP_ID AND SOURCES.SR_INSTANCE_ID = PT.SR_INSTANCE_ID AND PT.PARTNER_TYPE = 3 AND SOURCES.ASSIGNMENT_SET_ID = SETS.ASSIGNMENT_SET_ID AND SOURCES.INVENTORY_ITEM_ID = BBM.ASSEMBLY_ITEM_ID AND CAL.CALENDAR_DATE BETWEEN SOURCES.EFFECTIVE_DATE AND NVL(SOURCES.DISABLE_DATE, CAL.CALENDAR_DATE))

View Text - HTML Formatted

SELECT DISTINCT SOURCES.ALLOCATION_PERCENT || '% ' || MSC_GET_NAME.LOOKUP_MEANING('MRP_SOURCE_TYPE'
, DECODE(PT2.ORGANIZATION_CODE
, NULL
, 3
, 1)) || ' ' || NVL(PT2.ORGANIZATION_CODE
, PT.PARTNER_NAME || '-' || PTS.TP_SITE_CODE)
, SOURCES.ASSIGNMENT_SET_ID
, SOURCES.ASSIGNMENT_TYPE
, SOURCES.SOURCING_RULE_NAME
, SOURCES.ORGANIZATION_ID
, SOURCES.SR_INSTANCE_ID
, PT1.ORGANIZATION_CODE
, SOURCES.INVENTORY_ITEM_ID
, ITEM.ITEM_NAME
, NVL(SOURCES.SOURCE_ORGANIZATION_ID
, 0)
, SOURCES.SOURCE_ORG_INSTANCE_ID
, PT2.ORGANIZATION_CODE
, SOURCES.INVENTORY_ITEM_ID
, ITEM.ITEM_NAME
, CAL.CALENDAR_DATE
, TO_NUMBER(NULL)
, SOURCES.ALLOCATION_PERCENT
, SOURCES.SHIP_METHOD
, SOURCES.AVG_TRANSIT_LEAD_TIME
, SOURCES.RANK
, PT.PARTNER_NAME
, PTS.TP_SITE_CODE
, SOURCES.VENDOR_ID
, SOURCES.VENDOR_SITE_ID
, NULL
, ITEM.DESCRIPTION
, PT2.MODELED_SUPPLIER_ID
, PT2.MODELED_SUPPLIER_SITE_ID
, SOURCES.CIRCULAR_SRC
FROM MSC_TRADING_PARTNERS PT
, MSC_TRADING_PARTNERS PT2
, MSC_TRADING_PARTNERS PT1
, MSC_TRADING_PARTNER_SITES PTS
, MSC_ITEMS ITEM
, MSC_CALENDAR_DATES CAL
, MSC_ASSIGNMENT_SETS SETS
, MSC_SOURCES_V SOURCES
WHERE PT.PARTNER_ID(+) = SOURCES.VENDOR_ID
AND PTS.PARTNER_SITE_ID(+) = SOURCES.VENDOR_SITE_ID
AND PT2.SR_TP_ID (+) = SOURCES.SOURCE_ORGANIZATION_ID
AND PT2.SR_INSTANCE_ID(+) = SOURCES.SOURCE_ORG_INSTANCE_ID
AND PT2.PARTNER_TYPE(+)=3
AND ITEM.INVENTORY_ITEM_ID = SOURCES.INVENTORY_ITEM_ID
AND CAL.CALENDAR_CODE = PT1.CALENDAR_CODE
AND CAL.EXCEPTION_SET_ID = PT1.CALENDAR_EXCEPTION_SET_ID
AND CAL.CALENDAR_DATE BETWEEN SOURCES.EFFECTIVE_DATE
AND NVL(SOURCES.DISABLE_DATE
, TO_DATE(2634525
, 'J'))
AND PT1.SR_TP_ID = SOURCES.ORGANIZATION_ID
AND PT1.SR_INSTANCE_ID = SOURCES.SR_INSTANCE_ID
AND PT1.PARTNER_TYPE = 3
AND SETS.ASSIGNMENT_SET_ID = NVL(SOURCES.ASSIGNMENT_SET_ID
, SETS.ASSIGNMENT_SET_ID)
AND ((SOURCES.ORGANIZATION_ID != NVL(SOURCES.SOURCE_ORGANIZATION_ID
, 1))) UNION ALL SELECT DISTINCT ROUND( BIC.USAGE_QUANTITY
, 6) || 'X ' || ITEM2.ITEM_NAME || '/' || PT.ORGANIZATION_CODE
, SETS.ASSIGNMENT_SET_ID
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, PT.SR_TP_ID
, PT.SR_INSTANCE_ID
, PT.ORGANIZATION_CODE
, BBM.ASSEMBLY_ITEM_ID
, ITEM1.ITEM_NAME
, BBM.ORGANIZATION_ID
, BBM.SR_INSTANCE_ID
, PT.ORGANIZATION_CODE
, BIC.INVENTORY_ITEM_ID
, ITEM2.ITEM_NAME
, CAL.CALENDAR_DATE
, BIC.USAGE_QUANTITY
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, BBM.ALTERNATE_BOM_DESIGNATOR
, ITEM2.DESCRIPTION
, PT.MODELED_SUPPLIER_ID
, PT.MODELED_SUPPLIER_SITE_ID
, 'N'
FROM MSC_SYSTEM_ITEMS ITEM2
, MSC_ITEMS ITEM1
, MSC_CALENDAR_DATES CAL
, MSC_BOM_COMPONENTS BIC
, MSC_BOMS BBM
, MSC_TRADING_PARTNERS PT
, MSC_ASSIGNMENT_SETS SETS
WHERE BIC.ORGANIZATION_ID = PT.SR_TP_ID
AND BIC.SR_INSTANCE_ID = PT.SR_INSTANCE_ID
AND ITEM2.INVENTORY_ITEM_ID = BIC.INVENTORY_ITEM_ID
AND ITEM2.SR_INSTANCE_ID = BIC.SR_INSTANCE_ID
AND ITEM2.ORGANIZATION_ID = BIC.ORGANIZATION_ID
AND ITEM2.PLAN_ID = BIC.PLAN_ID
AND BBM.ORGANIZATION_ID = PT.SR_TP_ID
AND BBM.SR_INSTANCE_ID = PT.SR_INSTANCE_ID
AND PT.PARTNER_TYPE = 3
AND BBM.PLAN_ID = -1
AND BIC.PLAN_ID = BBM.PLAN_ID
AND BIC.BILL_SEQUENCE_ID = BBM.BILL_SEQUENCE_ID
AND BIC.SR_INSTANCE_ID = BBM.SR_INSTANCE_ID
AND NVL(BIC.COMPONENT_TYPE
, -1) <> 10
AND ITEM1.INVENTORY_ITEM_ID = BBM.ASSEMBLY_ITEM_ID
AND CAL.CALENDAR_CODE = PT.CALENDAR_CODE
AND CAL.EXCEPTION_SET_ID = PT.CALENDAR_EXCEPTION_SET_ID
AND CAL.SR_INSTANCE_ID = PT.SR_INSTANCE_ID
AND CAL.CALENDAR_DATE BETWEEN TRUNC(BIC.EFFECTIVITY_DATE)
AND NVL(BIC.DISABLE_DATE
, TO_DATE(2634525
, 'J'))
AND NOT EXISTS (SELECT NULL
FROM MSC_ITEM_LOWEST_SR_LEVEL_V SOURCES
WHERE SOURCES.ORGANIZATION_ID = PT.SR_TP_ID
AND SOURCES.SR_INSTANCE_ID = PT.SR_INSTANCE_ID
AND PT.PARTNER_TYPE = 3
AND SOURCES.ASSIGNMENT_SET_ID = SETS.ASSIGNMENT_SET_ID
AND SOURCES.INVENTORY_ITEM_ID = BBM.ASSEMBLY_ITEM_ID
AND CAL.CALENDAR_DATE BETWEEN SOURCES.EFFECTIVE_DATE
AND NVL(SOURCES.DISABLE_DATE
, CAL.CALENDAR_DATE))