VIEW: APPS.MSD_LB_OPEN_PO_CS_V

Source

View Text - Preformatted

SELECT pln.compile_designator, 1, PRD.LEVEL_PK, 12, GEO.LEVEL_PK, 7, ORG.LEVEL_PK, 34, DCS.LEVEL_PK, 9, PO.NEW_ORDER_PLACEMENT_DATE, PO.NEW_ORDER_QUANTITY, po.creation_date, po.created_by, po.last_update_date, po.last_updated_by, po.last_update_login, 1, 1, 'I', to_number(null), to_number(null) FROM MSD_LEVEL_VALUES_LB PRD, MSD_LEVEL_VALUES_LB GEO, MSD_LEVEL_VALUES_LB ORG, MSD_LEVEL_VALUES_LB DCS, MSC_SUPPLIES PO, MSC_ITEM_SUPPLIERS mis, MSC_TRADING_PARTNERS MTP, MSC_PLANS PLN WHERE PO.PLAN_ID = mis.PLAN_ID AND PO.PLAN_ID = PLN.PLAN_ID AND PO.ORDER_TYPE = 1 AND PO.INVENTORY_ITEM_ID = mis.INVENTORY_ITEM_ID AND PO.ORGANIZATION_ID = mis.ORGANIZATION_ID AND PO.SUPPLIER_ID = mis.SUPPLIER_ID AND PO.PLAN_ID = PRD.PLAN_ID AND PO.PLAN_ID = GEO.PLAN_ID AND PO.PLAN_ID = ORG.PLAN_ID AND PO.PLAN_ID = DCS.PLAN_ID AND PRD.LEVEL_ID = 1 AND ORG.LEVEL_ID = 7 AND GEO.LEVEL_ID = 12 AND DCS.LEVEL_ID = 34 AND PRD.SR_LEVEL_PK = PO.INVENTORY_ITEM_ID AND org.sr_level_pk = mtp.sr_instance_id||':'||mtp.sr_tp_id AND MTP.SR_TP_ID = PO.ORGANIZATION_ID AND MTP.SR_INSTANCE_ID = PO.SR_INSTANCE_ID AND MTP.PARTNER_TYPE = 3 AND GEO.SR_LEVEL_PK = PO.SUPPLIER_ID AND DCS.SR_LEVEL_PK = '5' and pln.plan_id <> -1 and mis.INCLUDE_LIABILITY_AGREEMENT = 1 and mis.ASL_LIABILITY_AGREEMENT_BASIS is not null UNION ALL /* ODS */ SELECT mdp.liab_plan_name, 1, prd.level_pk, 12, geo.level_pk, 7, org.level_pk, 34, dcs.level_pk, 9, sup.key_date, sup.primary_quantity, sup.creation_date, sup.created_by, sup.last_update_date, sup.last_updated_by, sup.last_update_login, 1, 1, 'I', to_number(null), to_number(null) FROM msd_level_values_lb prd, msd_level_values_lb geo, msd_level_values_lb dcs, msd_level_values_lb org, msc_system_items itm, msc_sup_dem_entries sup, msc_company_relationships mcr_sup, msc_trading_partner_maps map_sup, msc_trading_partner_maps map_org, MSC_ITEM_SUPPLIERS mis, msc_trading_partners mtp, msd_demand_plans mdp WHERE mdp.liab_plan_id = -1 AND itm.plan_id = -1 /* Product Mapping */ AND prd.plan_id = -1 AND prd.level_id = 1 AND prd.sr_level_pk = itm.inventory_item_id AND itm.plan_id = sup.plan_id AND itm.inventory_item_id = sup.inventory_item_id AND itm.sr_instance_id = mtp.sr_instance_id AND itm.organization_id = mtp.sr_tp_id AND itm.purchasing_enabled_flag = 1 /* Supplier Mapping */ AND sup.plan_id = -1 AND sup.supplier_id = mcr_sup.object_id AND mcr_sup.subject_id = 1 AND mcr_sup.relationship_type = 2 AND mcr_sup.relationship_id = map_sup.company_key AND map_sup.map_type = 1 AND geo.plan_id = -1 AND geo.level_id = 12 AND geo.sr_level_pk = map_sup.tp_key /* Org Mapping */ AND sup.customer_site_id = map_org.company_key AND map_org.map_type = 2 AND mtp.partner_id = map_org.tp_key AND org.level_id = 7 AND org.sr_level_pk = mtp.sr_instance_id||':'||mtp.sr_tp_id AND org.plan_id = -1 /* Agreement Details Mapping */ AND mis.plan_id = -1 AND mis.inventory_item_id = sup.inventory_item_id AND mis.organization_id = mtp.sr_tp_id AND mis.sr_instance_id = mtp.sr_instance_id AND mis.supplier_id = map_sup.tp_key /* Order Forecast - Purchase Orders * OR * Supply Commit - Sales Orders */ AND (((mis.ASL_LIABILITY_AGREEMENT_BASIS =1) and (sup.publisher_order_type in (13))) OR ((mis.ASL_LIABILITY_AGREEMENT_BASIS = 2) and (sup.publisher_order_type in (14)))) /* Authorization Code Manpping */ AND dcs.plan_id = -1 AND dcs.level_id = 34 AND dcs.sr_level_pk = '5'

View Text - HTML Formatted

SELECT PLN.COMPILE_DESIGNATOR
, 1
, PRD.LEVEL_PK
, 12
, GEO.LEVEL_PK
, 7
, ORG.LEVEL_PK
, 34
, DCS.LEVEL_PK
, 9
, PO.NEW_ORDER_PLACEMENT_DATE
, PO.NEW_ORDER_QUANTITY
, PO.CREATION_DATE
, PO.CREATED_BY
, PO.LAST_UPDATE_DATE
, PO.LAST_UPDATED_BY
, PO.LAST_UPDATE_LOGIN
, 1
, 1
, 'I'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSD_LEVEL_VALUES_LB PRD
, MSD_LEVEL_VALUES_LB GEO
, MSD_LEVEL_VALUES_LB ORG
, MSD_LEVEL_VALUES_LB DCS
, MSC_SUPPLIES PO
, MSC_ITEM_SUPPLIERS MIS
, MSC_TRADING_PARTNERS MTP
, MSC_PLANS PLN
WHERE PO.PLAN_ID = MIS.PLAN_ID
AND PO.PLAN_ID = PLN.PLAN_ID
AND PO.ORDER_TYPE = 1
AND PO.INVENTORY_ITEM_ID = MIS.INVENTORY_ITEM_ID
AND PO.ORGANIZATION_ID = MIS.ORGANIZATION_ID
AND PO.SUPPLIER_ID = MIS.SUPPLIER_ID
AND PO.PLAN_ID = PRD.PLAN_ID
AND PO.PLAN_ID = GEO.PLAN_ID
AND PO.PLAN_ID = ORG.PLAN_ID
AND PO.PLAN_ID = DCS.PLAN_ID
AND PRD.LEVEL_ID = 1
AND ORG.LEVEL_ID = 7
AND GEO.LEVEL_ID = 12
AND DCS.LEVEL_ID = 34
AND PRD.SR_LEVEL_PK = PO.INVENTORY_ITEM_ID
AND ORG.SR_LEVEL_PK = MTP.SR_INSTANCE_ID||':'||MTP.SR_TP_ID
AND MTP.SR_TP_ID = PO.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID = PO.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE = 3
AND GEO.SR_LEVEL_PK = PO.SUPPLIER_ID
AND DCS.SR_LEVEL_PK = '5'
AND PLN.PLAN_ID <> -1
AND MIS.INCLUDE_LIABILITY_AGREEMENT = 1
AND MIS.ASL_LIABILITY_AGREEMENT_BASIS IS NOT NULL UNION ALL /* ODS */ SELECT MDP.LIAB_PLAN_NAME
, 1
, PRD.LEVEL_PK
, 12
, GEO.LEVEL_PK
, 7
, ORG.LEVEL_PK
, 34
, DCS.LEVEL_PK
, 9
, SUP.KEY_DATE
, SUP.PRIMARY_QUANTITY
, SUP.CREATION_DATE
, SUP.CREATED_BY
, SUP.LAST_UPDATE_DATE
, SUP.LAST_UPDATED_BY
, SUP.LAST_UPDATE_LOGIN
, 1
, 1
, 'I'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSD_LEVEL_VALUES_LB PRD
, MSD_LEVEL_VALUES_LB GEO
, MSD_LEVEL_VALUES_LB DCS
, MSD_LEVEL_VALUES_LB ORG
, MSC_SYSTEM_ITEMS ITM
, MSC_SUP_DEM_ENTRIES SUP
, MSC_COMPANY_RELATIONSHIPS MCR_SUP
, MSC_TRADING_PARTNER_MAPS MAP_SUP
, MSC_TRADING_PARTNER_MAPS MAP_ORG
, MSC_ITEM_SUPPLIERS MIS
, MSC_TRADING_PARTNERS MTP
, MSD_DEMAND_PLANS MDP
WHERE MDP.LIAB_PLAN_ID = -1
AND ITM.PLAN_ID = -1 /* PRODUCT MAPPING */
AND PRD.PLAN_ID = -1
AND PRD.LEVEL_ID = 1
AND PRD.SR_LEVEL_PK = ITM.INVENTORY_ITEM_ID
AND ITM.PLAN_ID = SUP.PLAN_ID
AND ITM.INVENTORY_ITEM_ID = SUP.INVENTORY_ITEM_ID
AND ITM.SR_INSTANCE_ID = MTP.SR_INSTANCE_ID
AND ITM.ORGANIZATION_ID = MTP.SR_TP_ID
AND ITM.PURCHASING_ENABLED_FLAG = 1 /* SUPPLIER MAPPING */
AND SUP.PLAN_ID = -1
AND SUP.SUPPLIER_ID = MCR_SUP.OBJECT_ID
AND MCR_SUP.SUBJECT_ID = 1
AND MCR_SUP.RELATIONSHIP_TYPE = 2
AND MCR_SUP.RELATIONSHIP_ID = MAP_SUP.COMPANY_KEY
AND MAP_SUP.MAP_TYPE = 1
AND GEO.PLAN_ID = -1
AND GEO.LEVEL_ID = 12
AND GEO.SR_LEVEL_PK = MAP_SUP.TP_KEY /* ORG MAPPING */
AND SUP.CUSTOMER_SITE_ID = MAP_ORG.COMPANY_KEY
AND MAP_ORG.MAP_TYPE = 2
AND MTP.PARTNER_ID = MAP_ORG.TP_KEY
AND ORG.LEVEL_ID = 7
AND ORG.SR_LEVEL_PK = MTP.SR_INSTANCE_ID||':'||MTP.SR_TP_ID
AND ORG.PLAN_ID = -1 /* AGREEMENT DETAILS MAPPING */
AND MIS.PLAN_ID = -1
AND MIS.INVENTORY_ITEM_ID = SUP.INVENTORY_ITEM_ID
AND MIS.ORGANIZATION_ID = MTP.SR_TP_ID
AND MIS.SR_INSTANCE_ID = MTP.SR_INSTANCE_ID
AND MIS.SUPPLIER_ID = MAP_SUP.TP_KEY /* ORDER FORECAST - PURCHASE ORDERS * OR * SUPPLY COMMIT - SALES ORDERS */
AND (((MIS.ASL_LIABILITY_AGREEMENT_BASIS =1)
AND (SUP.PUBLISHER_ORDER_TYPE IN (13))) OR ((MIS.ASL_LIABILITY_AGREEMENT_BASIS = 2)
AND (SUP.PUBLISHER_ORDER_TYPE IN (14)))) /* AUTHORIZATION CODE MANPPING */
AND DCS.PLAN_ID = -1
AND DCS.LEVEL_ID = 34
AND DCS.SR_LEVEL_PK = '5'