eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MSD_OPEN_PO_DS_V

Source

View Text - Preformatted


SELECT MDS.DEMAND_PLAN_ID, PLANS.PLAN_ID, PLANS.compile_designator, D_ORG.LEVEL_PK, 7, D_PRD.LEVEL_PK, 1, D_GEO.LEVEL_PK, 50, 9, PO.NEW_ORDER_QUANTITY, to_number(NULL), TRUNC(PO.NEW_ORDER_PLACEMENT_DATE), 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 MSC_PLANS PLANS, MSC_SUPPLIES PO, MSD_DP_SESSION MDS, MSC_ITEM_SUPPLIERS mis, MSC_SYSTEM_ITEMS MSI, MSC_TRADING_PARTNERS MTP, MSD_LEVEL_VALUES_DS D_PRD, MSD_LEVEL_VALUES_DS D_GEO, MSD_LEVEL_VALUES_DS D_ORG WHERE PO.PLAN_ID = mis.PLAN_ID AND PO.PLAN_ID=MSI.PLAN_ID AND PO.PLAN_ID = PLANS.PLAN_ID AND plans.plan_id <> -1 and PO.INVENTORY_ITEM_ID = mis.INVENTORY_ITEM_ID AND PO.ORGANIZATION_ID = mis.ORGANIZATION_ID AND PO.SR_INSTANCE_ID = mis.SR_INSTANCE_ID AND PO.SUPPLIER_ID=MIS.SUPPLIER_ID AND PO.SUPPLIER_SITE_ID=MIS.SUPPLIER_SITE_ID AND PO.ORDER_TYPE in (1,2) AND mis.INCLUDE_LIABILITY_AGREEMENT = 1 AND PO.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID AND PO.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND PO.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MTP.PARTNER_ID=MIS.SUPPLIER_ID AND MTP.PARTNER_TYPE=1 AND D_PRD.LEVEL_ID = 1 AND D_PRD.SR_LEVEL_PK = to_char(MSI.SR_INVENTORY_ITEM_ID) AND D_PRD.instance = to_char(MSI.SR_INSTANCE_ID) AND D_PRD.DEMAND_PLAN_ID=MDS.DEMAND_PLAN_ID AND D_ORG.LEVEL_ID = 7 AND D_org.sr_level_pk = to_char(PO.ORGANIZATION_ID) AND D_ORG.instance = to_char(PO.SR_INSTANCE_ID) AND D_ORG.DEMAND_PLAN_ID=MDS.DEMAND_PLAN_ID AND D_GEO.LEVEL_ID = 50 AND D_GEO.SR_LEVEL_PK = to_char('1.'||MTP.SR_TP_ID) AND D_GEO.instance = to_char(PO.SR_INSTANCE_ID) AND D_GEO.DEMAND_PLAN_ID=MDS.DEMAND_PLAN_ID UNION ALL SELECT MDS.DEMAND_PLAN_ID, TO_NUMBER(NULL) PLAN_ID, MCD.CS_NAME CS_NAME, MLV_ORG.LEVEL_PK ORG_LEVEL_VALUE_PK, 7 ORG_LEVEL_ID, MLV_PRD.LEVEL_PK PRD_LEVEL_VALUE_PK, 1 PRD_LEVEL_ID, MLV_GEO.LEVEL_PK GEO_LEVEL_VALUE_PK, 50 GEO_LEVEL_ID, 9 TIME_LEVEL_ID, TO_NUMBER(MCD.ATTRIBUTE_41) QUANTITY, TO_NUMBER(MCD.ATTRIBUTE_42) AMOUNT, TO_DATE(MCD.ATTRIBUTE_43,'YYYY/MM/DD'), MCD.CREATION_DATE CREATION_DATE, MCD.CREATED_BY CREATED_BY, MCD.LAST_UPDATE_DATE LAST_UPDATE_DATE, MCD.LAST_UPDATED_BY LAST_UPDATED_BY, MCD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, MCD.LAST_REFRESH_NUM LAST_REFRESH_NUM, MCD.CREATED_BY_REFRESH_NUM CREATED_BY_REFRESH_NUM, MCD.ACTION_CODE ACTION_CODE, TO_NUMBER(NULL) PRD_PARENT_LEVEL_ID, TO_NUMBER(NULL) PRD_PARENT_LEVEL_VALUE_PK FROM MSD_CS_DATA MCD, MSD_LEVEL_VALUES_DS MLV_ORG, MSD_LEVEL_VALUES_DS MLV_PRD, MSD_LEVEL_VALUES_DS MLV_GEO, MSD_DP_SESSION MDS WHERE MCD.CS_DEFINITION_ID = (SELECT CS_DEFINITION_ID FROM MSD_CS_DEFINITIONS WHERE NAME = 'MSD_OPEN_PURCHASE_ORDER') AND MCD.ATTRIBUTE_10 = 7 AND MLV_ORG.LEVEL_ID = 7 AND MCD.ATTRIBUTE_11 = MLV_ORG.SR_LEVEL_PK AND MCD.ATTRIBUTE_1 = MLV_ORG.INSTANCE AND MLV_ORG.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID AND MCD.ATTRIBUTE_2 = 1 AND MLV_PRD.LEVEL_ID = 1 AND MCD.ATTRIBUTE_3 = MLV_PRD.SR_LEVEL_PK AND MCD.ATTRIBUTE_1 = MLV_PRD.INSTANCE AND MLV_PRD.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID AND MCD.ATTRIBUTE_6 = 50 AND MLV_GEO.LEVEL_ID = 50 AND MCD.ATTRIBUTE_7 = MLV_GEO.SR_LEVEL_PK AND MCD.ATTRIBUTE_1 = MLV_GEO.INSTANCE AND MLV_GEO.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID

View Text - HTML Formatted

SELECT MDS.DEMAND_PLAN_ID
, PLANS.PLAN_ID
, PLANS.COMPILE_DESIGNATOR
, D_ORG.LEVEL_PK
, 7
, D_PRD.LEVEL_PK
, 1
, D_GEO.LEVEL_PK
, 50
, 9
, PO.NEW_ORDER_QUANTITY
, TO_NUMBER(NULL)
, TRUNC(PO.NEW_ORDER_PLACEMENT_DATE)
, 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 MSC_PLANS PLANS
, MSC_SUPPLIES PO
, MSD_DP_SESSION MDS
, MSC_ITEM_SUPPLIERS MIS
, MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNERS MTP
, MSD_LEVEL_VALUES_DS D_PRD
, MSD_LEVEL_VALUES_DS D_GEO
, MSD_LEVEL_VALUES_DS D_ORG
WHERE PO.PLAN_ID = MIS.PLAN_ID
AND PO.PLAN_ID=MSI.PLAN_ID
AND PO.PLAN_ID = PLANS.PLAN_ID
AND PLANS.PLAN_ID <> -1
AND PO.INVENTORY_ITEM_ID = MIS.INVENTORY_ITEM_ID
AND PO.ORGANIZATION_ID = MIS.ORGANIZATION_ID
AND PO.SR_INSTANCE_ID = MIS.SR_INSTANCE_ID
AND PO.SUPPLIER_ID=MIS.SUPPLIER_ID
AND PO.SUPPLIER_SITE_ID=MIS.SUPPLIER_SITE_ID
AND PO.ORDER_TYPE IN (1
, 2)
AND MIS.INCLUDE_LIABILITY_AGREEMENT = 1
AND PO.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND PO.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND PO.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MTP.PARTNER_ID=MIS.SUPPLIER_ID
AND MTP.PARTNER_TYPE=1
AND D_PRD.LEVEL_ID = 1
AND D_PRD.SR_LEVEL_PK = TO_CHAR(MSI.SR_INVENTORY_ITEM_ID)
AND D_PRD.INSTANCE = TO_CHAR(MSI.SR_INSTANCE_ID)
AND D_PRD.DEMAND_PLAN_ID=MDS.DEMAND_PLAN_ID
AND D_ORG.LEVEL_ID = 7
AND D_ORG.SR_LEVEL_PK = TO_CHAR(PO.ORGANIZATION_ID)
AND D_ORG.INSTANCE = TO_CHAR(PO.SR_INSTANCE_ID)
AND D_ORG.DEMAND_PLAN_ID=MDS.DEMAND_PLAN_ID
AND D_GEO.LEVEL_ID = 50
AND D_GEO.SR_LEVEL_PK = TO_CHAR('1.'||MTP.SR_TP_ID)
AND D_GEO.INSTANCE = TO_CHAR(PO.SR_INSTANCE_ID)
AND D_GEO.DEMAND_PLAN_ID=MDS.DEMAND_PLAN_ID UNION ALL SELECT MDS.DEMAND_PLAN_ID
, TO_NUMBER(NULL) PLAN_ID
, MCD.CS_NAME CS_NAME
, MLV_ORG.LEVEL_PK ORG_LEVEL_VALUE_PK
, 7 ORG_LEVEL_ID
, MLV_PRD.LEVEL_PK PRD_LEVEL_VALUE_PK
, 1 PRD_LEVEL_ID
, MLV_GEO.LEVEL_PK GEO_LEVEL_VALUE_PK
, 50 GEO_LEVEL_ID
, 9 TIME_LEVEL_ID
, TO_NUMBER(MCD.ATTRIBUTE_41) QUANTITY
, TO_NUMBER(MCD.ATTRIBUTE_42) AMOUNT
, TO_DATE(MCD.ATTRIBUTE_43
, 'YYYY/MM/DD')
, MCD.CREATION_DATE CREATION_DATE
, MCD.CREATED_BY CREATED_BY
, MCD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, MCD.LAST_UPDATED_BY LAST_UPDATED_BY
, MCD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, MCD.LAST_REFRESH_NUM LAST_REFRESH_NUM
, MCD.CREATED_BY_REFRESH_NUM CREATED_BY_REFRESH_NUM
, MCD.ACTION_CODE ACTION_CODE
, TO_NUMBER(NULL) PRD_PARENT_LEVEL_ID
, TO_NUMBER(NULL) PRD_PARENT_LEVEL_VALUE_PK
FROM MSD_CS_DATA MCD
, MSD_LEVEL_VALUES_DS MLV_ORG
, MSD_LEVEL_VALUES_DS MLV_PRD
, MSD_LEVEL_VALUES_DS MLV_GEO
, MSD_DP_SESSION MDS
WHERE MCD.CS_DEFINITION_ID = (SELECT CS_DEFINITION_ID
FROM MSD_CS_DEFINITIONS
WHERE NAME = 'MSD_OPEN_PURCHASE_ORDER')
AND MCD.ATTRIBUTE_10 = 7
AND MLV_ORG.LEVEL_ID = 7
AND MCD.ATTRIBUTE_11 = MLV_ORG.SR_LEVEL_PK
AND MCD.ATTRIBUTE_1 = MLV_ORG.INSTANCE
AND MLV_ORG.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND MCD.ATTRIBUTE_2 = 1
AND MLV_PRD.LEVEL_ID = 1
AND MCD.ATTRIBUTE_3 = MLV_PRD.SR_LEVEL_PK
AND MCD.ATTRIBUTE_1 = MLV_PRD.INSTANCE
AND MLV_PRD.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND MCD.ATTRIBUTE_6 = 50
AND MLV_GEO.LEVEL_ID = 50
AND MCD.ATTRIBUTE_7 = MLV_GEO.SR_LEVEL_PK
AND MCD.ATTRIBUTE_1 = MLV_GEO.INSTANCE
AND MLV_GEO.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID