VIEW: APPS.MRP_SCATP_ITEM_SR_LEVELS_V

Source

View Text - Preformatted

SELECT s.inventory_item_id, s.location_id, s.assignment_type, s.assignment_set_id, msr.sourcing_rule_type, source_org.source_organization_id, org.location_id, source_org.vendor_id, source_org.vendor_site_id, source_org.allocation_percent, source_org.rank, decode(s.location_id, NULL, to_number(NULL), decode(org.location_id, NULL, to_number(NULL), mrp_scatp_pvt.get_default_intransit_time(org.location_id, s.location_id))), decode(s.location_id, NULL, to_char(NULL), decode(org.location_id, NULL, to_char(NULL), SUBSTR(mrp_scatp_pvt.get_default_ship_method(org.location_id, s.location_id), 1, 30))), receipt_org.effective_date, receipt_org.disable_date, s.category_id, Decode (sr_level, 1, decode(s.assignment_type, 6, 1, 4, 8), 2, 3, 3, decode(s.assignment_type, 1, decode(msr.sourcing_rule_type, 1, 11, 10), 3, decode(msr.sourcing_rule_type, 1, 5, 4)), decode(msr.sourcing_rule_type, 1, 7, 6) ), s.assignment_id, msr.sourcing_rule_id, msr.sourcing_rule_name, source_org.source_type, msr.description, s.customer_id, s.ship_to_site_id FROM hr_organization_units org, mrp_sr_source_org source_org, mrp_sr_receipt_org receipt_org, mrp_sourcing_rules msr, ( SELECT msra.sourcing_rule_id, item.inventory_item_id, item.organization_id, po.location_id, msra.assignment_type, msra.assignment_set_id, msra.category_id, msra.assignment_id, msra.customer_id customer_id, msra.ship_to_site_id ship_to_site_id, 1 sr_level FROM mtl_system_items item, po_location_associations_all po, mrp_sr_assignments msra WHERE msra.customer_id IS NOT NULL AND msra.ship_to_site_id IS NOT NULL AND po.customer_id(+) = msra.customer_id AND po.site_use_id(+) = msra.ship_to_site_id AND((msra.assignment_type = 6 /* ITEM-ORG*/ AND item.inventory_item_id = msra.inventory_item_id) OR(msra.assignment_type = 4)) UNION ALL SELECT /* LEVEL 3 , ASSIGNMENT_TYPE 5: ITEM CATEGORY ORG */ msra.sourcing_rule_id, cat.inventory_item_id, cat.organization_id, po.location_id, msra.assignment_type, msra.assignment_set_id, msra.category_id, msra.assignment_id, msra.customer_id customer_id, msra.ship_to_site_id ship_to_site_id, 2 sr_level FROM mtl_item_categories cat, po_location_associations_all po, mrp_sr_assignments msra WHERE msra.assignment_type = 5 /* ITEM CATEGORY-ORG */ AND msra.customer_id IS NOT NULL AND msra.ship_to_site_id IS NOT NULL AND po.customer_id(+) = msra.customer_id AND po.site_use_id(+) = msra.ship_to_site_id AND cat.category_id = msra.category_id AND cat.category_set_id = msra.category_set_id UNION ALL SELECT msra.sourcing_rule_id, item.inventory_item_id, item.organization_id, po.location_id, msra.assignment_type, msra.assignment_set_id, msra.category_id, msra.assignment_id, po.customer_id customer_id, po.site_use_id ship_to_site_id, 3 sr_level FROM mtl_system_items item, po_location_associations_all po, mrp_sr_assignments msra WHERE((msra.assignment_type = 1 /* GLOBAL */) OR(msra.assignment_type = 3 /* ITEM */ AND item.inventory_item_id = msra.inventory_item_id)) AND po.site_use_id > 0 AND po.customer_id IS NOT NULL UNION ALL SELECT /* LEVEL 6 , 7 */ msra.sourcing_rule_id, cat.inventory_item_id, cat.organization_id, po.location_id, msra.assignment_type, msra.assignment_set_id, msra.category_id, msra.assignment_id, po.customer_id customer_id, po.site_use_id ship_to_site_id, 4 sr_level FROM mtl_item_categories cat, po_location_associations_all po, mrp_sr_assignments msra WHERE msra.assignment_type = 2 /* ITEM CATEGORY */ AND cat.category_id = msra.category_id AND cat.category_set_id = msra.category_set_id AND po.site_use_id > 0 AND po.customer_id IS NOT NULL )s WHERE msr.sourcing_rule_id = s.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 org.organization_id = source_org.source_organization_id AND s.organization_id = source_org.source_organization_id

View Text - HTML Formatted

SELECT S.INVENTORY_ITEM_ID
, S.LOCATION_ID
, S.ASSIGNMENT_TYPE
, S.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ORG.LOCATION_ID
, SOURCE_ORG.VENDOR_ID
, SOURCE_ORG.VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, DECODE(S.LOCATION_ID
, NULL
, TO_NUMBER(NULL)
, DECODE(ORG.LOCATION_ID
, NULL
, TO_NUMBER(NULL)
, MRP_SCATP_PVT.GET_DEFAULT_INTRANSIT_TIME(ORG.LOCATION_ID
, S.LOCATION_ID)))
, DECODE(S.LOCATION_ID
, NULL
, TO_CHAR(NULL)
, DECODE(ORG.LOCATION_ID
, NULL
, TO_CHAR(NULL)
, SUBSTR(MRP_SCATP_PVT.GET_DEFAULT_SHIP_METHOD(ORG.LOCATION_ID
, S.LOCATION_ID)
, 1
, 30)))
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, S.CATEGORY_ID
, DECODE (SR_LEVEL
, 1
, DECODE(S.ASSIGNMENT_TYPE
, 6
, 1
, 4
, 8)
, 2
, 3
, 3
, DECODE(S.ASSIGNMENT_TYPE
, 1
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 11
, 10)
, 3
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 5
, 4))
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 7
, 6) )
, S.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION
, S.CUSTOMER_ID
, S.SHIP_TO_SITE_ID
FROM HR_ORGANIZATION_UNITS ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SOURCING_RULES MSR
, ( SELECT MSRA.SOURCING_RULE_ID
, ITEM.INVENTORY_ITEM_ID
, ITEM.ORGANIZATION_ID
, PO.LOCATION_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSRA.CATEGORY_ID
, MSRA.ASSIGNMENT_ID
, MSRA.CUSTOMER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID SHIP_TO_SITE_ID
, 1 SR_LEVEL
FROM MTL_SYSTEM_ITEMS ITEM
, PO_LOCATION_ASSOCIATIONS_ALL PO
, MRP_SR_ASSIGNMENTS MSRA
WHERE MSRA.CUSTOMER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
AND PO.CUSTOMER_ID(+) = MSRA.CUSTOMER_ID
AND PO.SITE_USE_ID(+) = MSRA.SHIP_TO_SITE_ID AND((MSRA.ASSIGNMENT_TYPE = 6 /* ITEM-ORG*/
AND ITEM.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID) OR(MSRA.ASSIGNMENT_TYPE = 4)) UNION ALL SELECT /* LEVEL 3
, ASSIGNMENT_TYPE 5: ITEM CATEGORY ORG */ MSRA.SOURCING_RULE_ID
, CAT.INVENTORY_ITEM_ID
, CAT.ORGANIZATION_ID
, PO.LOCATION_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSRA.CATEGORY_ID
, MSRA.ASSIGNMENT_ID
, MSRA.CUSTOMER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID SHIP_TO_SITE_ID
, 2 SR_LEVEL
FROM MTL_ITEM_CATEGORIES CAT
, PO_LOCATION_ASSOCIATIONS_ALL PO
, MRP_SR_ASSIGNMENTS MSRA
WHERE MSRA.ASSIGNMENT_TYPE = 5 /* ITEM CATEGORY-ORG */
AND MSRA.CUSTOMER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
AND PO.CUSTOMER_ID(+) = MSRA.CUSTOMER_ID
AND PO.SITE_USE_ID(+) = MSRA.SHIP_TO_SITE_ID
AND CAT.CATEGORY_ID = MSRA.CATEGORY_ID
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID UNION ALL SELECT MSRA.SOURCING_RULE_ID
, ITEM.INVENTORY_ITEM_ID
, ITEM.ORGANIZATION_ID
, PO.LOCATION_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSRA.CATEGORY_ID
, MSRA.ASSIGNMENT_ID
, PO.CUSTOMER_ID CUSTOMER_ID
, PO.SITE_USE_ID SHIP_TO_SITE_ID
, 3 SR_LEVEL
FROM MTL_SYSTEM_ITEMS ITEM
, PO_LOCATION_ASSOCIATIONS_ALL PO
, MRP_SR_ASSIGNMENTS MSRA WHERE((MSRA.ASSIGNMENT_TYPE = 1 /* GLOBAL */) OR(MSRA.ASSIGNMENT_TYPE = 3 /* ITEM */
AND ITEM.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID))
AND PO.SITE_USE_ID > 0
AND PO.CUSTOMER_ID IS NOT NULL UNION ALL SELECT /* LEVEL 6
, 7 */ MSRA.SOURCING_RULE_ID
, CAT.INVENTORY_ITEM_ID
, CAT.ORGANIZATION_ID
, PO.LOCATION_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSRA.CATEGORY_ID
, MSRA.ASSIGNMENT_ID
, PO.CUSTOMER_ID CUSTOMER_ID
, PO.SITE_USE_ID SHIP_TO_SITE_ID
, 4 SR_LEVEL
FROM MTL_ITEM_CATEGORIES CAT
, PO_LOCATION_ASSOCIATIONS_ALL PO
, MRP_SR_ASSIGNMENTS MSRA
WHERE MSRA.ASSIGNMENT_TYPE = 2 /* ITEM CATEGORY */
AND CAT.CATEGORY_ID = MSRA.CATEGORY_ID
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND PO.SITE_USE_ID > 0
AND PO.CUSTOMER_ID IS NOT NULL )S
WHERE MSR.SOURCING_RULE_ID = S.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 ORG.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND S.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID