Product: | MSC - Advanced Supply Chain Planning |
---|---|
Description: | |
Implementation/DBA Data: | APPS.MSC_SUP_DEM_ENTRIES_UI_V |
SELECT SUPDEM.TRANSACTION_ID
, SUPDEM.PLAN_ID
, SUPDEM.SR_INSTANCE_ID
, SUPDEM.PUBLISHER_ID
, SUPDEM.PUBLISHER_SITE_ID
, SUPDEM.PUBLISHER_NAME
, SUPDEM.PUBLISHER_SITE_NAME
, SUPDEM.NEW_SCHEDULE_DATE
, SUPDEM.INVENTORY_ITEM_ID
, SUPDEM.QUANTITY
, SUPDEM.TP_QUANTITY
, SUPDEM.COMMENTS
, SUPDEM.PUBLISHER_ORDER_TYPE
, SUPDEM.SUPPLIER_ID
, SUPDEM.SUPPLIER_NAME
, SUPDEM.SUPPLIER_SITE_ID
, SUPDEM.SUPPLIER_SITE_NAME
, SUPDEM.CUSTOMER_ID
, SUPDEM.CUSTOMER_NAME
, SUPDEM.CUSTOMER_SITE_ID
, SUPDEM.CUSTOMER_SITE_NAME
, SUPDEM.LINE_CODE
, SUPDEM.BUCKET_TYPE
, SUPDEM.ORDER_NUMBER
, SUPDEM.END_ORDER_NUMBER
, SUPDEM.NEW_DOCK_DATE
, SUPDEM.POSTING_PARTY_ID
, SUPDEM.NEW_SHIP_DATE
, SUPDEM.NEW_ORDER_PLACEMENT_DATE
, SUPDEM.RELEASE_NUMBER
, SUPDEM.LINE_NUMBER
, SUPDEM.END_ORDER_REL_NUMBER
, SUPDEM.END_ORDER_LINE_NUMBER
, SUPDEM.PUBLISHER_ADDRESS_ID
, SUPDEM.CARRIER_CODE
, SUPDEM.VEHICLE_NUMBER
, SUPDEM.CONTAINER_TYPE
, SUPDEM.CONTAINER_QTY
, SUPDEM.TRACKING_NUMBER
, SUPDEM.VERSION
, SUPDEM.END_ORDER_TYPE
, SUPDEM.END_ORDER_PUBLISHER_ID
, SUPDEM.SHIP_TO_ADDRESS
, SUPDEM.SHIP_FROM_PARTY_ID
, SUPDEM.SHIP_TO_PARTY_ID
, SUPDEM.SHIP_TO_PARTY_SITE_ID
, SUPDEM.SHIP_TO_PARTY_ADDRESS_ID
, SUPDEM.SHIP_TO_PARTY_NAME
, SUPDEM.SHIP_TO_PARTY_SITE_NAME
, SUPDEM.SHIP_FROM_PARTY_ADDRESS_ID
, SUPDEM.SHIP_FROM_PARTY_SITE_ID
, SUPDEM.SHIP_FROM_PARTY_NAME
, SUPDEM.SHIP_FROM_PARTY_SITE_NAME
, SUPDEM.OWNING_SITE_ID
, SUPDEM.OWNING_SITE_NAME
, SUPDEM.END_ORDER_PUBLISHER_SITE_ID
, SUPDEM.END_ORDER_PUBLISHER_SITE_NAME
, SUPDEM.END_ORDER_PUBLISHER_NAME
, SUPDEM.ITEM_NAME
, SUPDEM.OWNER_ITEM_NAME
, SUPDEM.CUSTOMER_ITEM_NAME
, SUPDEM.SUPPLIER_ITEM_NAME
, SUPDEM.ITEM_DESCRIPTION
, SUPDEM.CUSTOMER_ITEM_DESCRIPTION
, SUPDEM.SUPPLIER_ITEM_DESCRIPTION
, SUPDEM.OWNER_ITEM_DESCRIPTION
, SUPDEM.PUBLISHER_ORDER_TYPE_DESC
, SUPDEM.DESIGNATOR
, SUPDEM.CONTEXT
, SUPDEM.UNIT_NUMBER
, SUPDEM.SHIP_METHOD
, SUPDEM.PROJECT_NUMBER
, SUPDEM.TASK_NUMBER
, SUPDEM.PLANNING_GROUP
, SUPDEM.SHIP_FROM_ADDRESS
, SUPDEM.PUBLISHER_ADDRESS
, SUPDEM.CUSTOMER_ADDRESS
, SUPDEM.SUPPLIER_ADDRESS
, SUPDEM.AVERAGE_ANNUAL_DEMAND
, SUPDEM.BILL_OF_LADING_NUMBER
, SUPDEM.BUCKET_TYPE_DESC
, SUPDEM.SERIAL_NUMBER
, SUPDEM.RELEASE_STATUS
, SUPDEM.INVENTORY_STATUS
, SUPDEM.WIP_END_DATE
, SUPDEM.WIP_START_DATE
, SUPDEM.PROMISE_SHIP_DATE
, SUPDEM.NEW_SCHEDULE_END_DATE
, SUPDEM.POSTING_PARTY_NAME
, SUPDEM.UOM_CODE
, SUPDEM.TP_UOM_CODE
, SUPDEM.PLANNER_CODE
, SUPDEM.LAST_REFRESH_NUMBER
, SUPDEM.ATTACHMENT_URL
, SUPDEM.ATTRIBUTE1
, SUPDEM.ATTRIBUTE2
, SUPDEM.ATTRIBUTE3
, SUPDEM.ATTRIBUTE4
, SUPDEM.ATTRIBUTE5
, SUPDEM.ATTRIBUTE6
, SUPDEM.ATTRIBUTE7
, SUPDEM.ATTRIBUTE8
, SUPDEM.ATTRIBUTE9
, SUPDEM.ATTRIBUTE10
, SUPDEM.ATTRIBUTE11
, SUPDEM.ATTRIBUTE12
, SUPDEM.ATTRIBUTE13
, SUPDEM.ATTRIBUTE14
, SUPDEM.ATTRIBUTE15
, SUPDEM.REQUEST_ID
, SUPDEM.PROGRAM_ID
, SUPDEM.PROGRAM_APPLICATION_ID
, SUPDEM.PROGRAM_UPDATE_DATE
, SUPDEM.CREATED_BY
, SUPDEM.CREATION_DATE
, SUPDEM.LAST_UPDATED_BY
, SUPDEM.LAST_UPDATE_DATE
, SUPDEM.LAST_UPDATE_LOGIN
, SUPDEM.KEY_DATE
, SUPDEM.SHIP_DATE
, SUPDEM.RECEIPT_DATE
, SUPDEM.ORIGINAL_PROMISED_DATE
, SUPDEM.REQUEST_DATE
, SUPDEM.PRIMARY_UOM
, SUPDEM.PRIMARY_QUANTITY
, SUPDEM.REF_HEADER_ID
, SUPDEM.REF_LINE_ID
, SUPDEM.CATEGORY_NAME
, SUPDEM.PUBLISHER_ORG_ID
, SUPDEM.SUBSCRIBER_ORG_ID
, SUPDEM.ASSEMBLY_ITEM_ID
, SUPDEM.CATEGORY_ID
, SUPDEM.START_DATE
, SUPDEM.END_DATE
, SUPDEM.PRODUCTION_SEQUENCE
, SUPDEM.SEQ_PLAN_COMPLETION_FLAG
, SUPDEM.INPUT_PLAN_ID
, SUPDEM.END_ASSEMBLY_ITEM_NAME
, SUPDEM.SUBSCRIBER_ADDRESS_ID
, SUPDEM.TP_ORDER_TYPE
, SUPDEM.PROCESSED_FLAG
, MIN(UPPER(RULE.PRIVILEGE)) PRIVILEGE
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, SUPDEM.CUSTOMER_ID
, SUPDEM.CUSTOMER_NAME
, SUPDEM.SUPPLIER_ID
, SUPDEM.SUPPLIER_NAME
, SUPDEM.PUBLISHER_NAME) VIEWER_COMPANY
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, SUPDEM.CUSTOMER_ID
, SUPDEM.CUSTOMER_SITE_NAME
, SUPDEM.SUPPLIER_ID
, SUPDEM.SUPPLIER_SITE_NAME
, SUPDEM.PUBLISHER_SITE_NAME) VIEWER_SITE
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, SUPDEM.CUSTOMER_ID
, SUPDEM.SUPPLIER_NAME
, SUPDEM.SUPPLIER_ID
, SUPDEM.CUSTOMER_NAME
, DECODE(SUPDEM.PUBLISHER_ID
, SUPDEM.CUSTOMER_ID
, SUPDEM.SUPPLIER_NAME
, SUPDEM.CUSTOMER_NAME)) TP_COMPANY
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, SUPDEM.SUPPLIER_ID
, SUPDEM.CUSTOMER_SITE_NAME
, SUPDEM.CUSTOMER_ID
, SUPDEM.SUPPLIER_SITE_NAME
, DECODE(SUPDEM.PUBLISHER_ID
, SUPDEM.CUSTOMER_ID
, SUPDEM.SUPPLIER_SITE_NAME
, SUPDEM.CUSTOMER_SITE_NAME)) TP_SITE
, DECODE(SYS_CONTEXT('MSC'
, 'COMPANY_ID')
, SUPDEM.SUPPLIER_ID
, 1
, SUPDEM.CUSTOMER_ID
, 1
, 0) THIRD_PARTY_FLAG
FROM MSC_SUP_DEM_ENTRIES SUPDEM
, MSC_X_SECURITY_RULES RULE
WHERE SYSDATE BETWEEN NVL(RULE.EFFECTIVE_FROM_DATE
, SYSDATE-1)
AND NVL(RULE.EFFECTIVE_TO_DATE
, SYSDATE +1)
AND NVL(RULE.COMPANY_ID
, SUPDEM.PUBLISHER_ID) = SUPDEM.PUBLISHER_ID
AND NVL(RULE.ORDER_TYPE
, SUPDEM.PUBLISHER_ORDER_TYPE) = SUPDEM.PUBLISHER_ORDER_TYPE
AND NVL(RULE.ITEM_ID
, SUPDEM.INVENTORY_ITEM_ID) = SUPDEM.INVENTORY_ITEM_ID
AND NVL(RULE.CUSTOMER_ID
, NVL(SUPDEM.CUSTOMER_ID
, -1)) = NVL(SUPDEM.CUSTOMER_ID
, -1)
AND NVL(RULE.SUPPLIER_ID
, NVL(SUPDEM.SUPPLIER_ID
, -1)) = NVL(SUPDEM.SUPPLIER_ID
, -1)
AND NVL(RULE.CUSTOMER_SITE_ID
, NVL(SUPDEM.CUSTOMER_SITE_ID
, -1)) = NVL(SUPDEM.CUSTOMER_SITE_ID
, -1)
AND NVL(RULE.SUPPLIER_SITE_ID
, NVL(SUPDEM.SUPPLIER_SITE_ID
, -1)) = NVL(SUPDEM.SUPPLIER_SITE_ID
, -1)
AND NVL(RULE.ORG_ID
, SUPDEM.PUBLISHER_SITE_ID) = SUPDEM.PUBLISHER_SITE_ID
AND NVL(RULE.ORDER_NUMBER
, NVL(SUPDEM.ORDER_NUMBER
, -1)) = NVL(SUPDEM.ORDER_NUMBER
, -1)
AND (RULE.GRANTEE_KEY = DECODE(UPPER(RULE.GRANTEE_TYPE)
, 'USER'
, FND_GLOBAL.USER_ID
, 'COMPANY'
, SYS_CONTEXT('MSC'
, 'COMPANY_ID')) OR UPPER(RULE.GRANTEE_TYPE) = 'DOCUMENT OWNER'
AND SUPDEM.PUBLISHER_ID = SYS_CONTEXT('MSC'
, 'COMPANY_ID') OR UPPER(RULE.GRANTEE_TYPE) = 'TRADING PARTNER'
AND SUPDEM.CUSTOMER_ID = SYS_CONTEXT('MSC'
, 'COMPANY_ID') OR UPPER(RULE.GRANTEE_TYPE) = 'TRADING PARTNER'
AND SUPDEM.SUPPLIER_ID = SYS_CONTEXT('MSC'
, 'COMPANY_ID') OR DECODE(UPPER(RULE.GRANTEE_TYPE)
, 'RESPONSIBILITY'
, RULE.GRANTEE_KEY) = FND_GLOBAL.RESP_ID OR (UPPER(RULE.GRANTEE_TYPE) = 'GLOBAL') OR (UPPER(RULE.GRANTEE_TYPE) = 'GROUP'
AND RULE.GRANTEE_KEY IN (SELECT GROUP_ID
FROM MSC_GROUP_COMPANIES
WHERE COMPANY_ID = SYS_CONTEXT('MSC'
, 'COMPANY_ID')
AND SYSDATE BETWEEN EFFECTIVE_DATE
AND NVL(DISABLE_DATE
, SYSDATE+1) ) ) ) GROUP BY TRANSACTION_ID
, SUPDEM.PLAN_ID
, SUPDEM.SR_INSTANCE_ID
, SUPDEM.PUBLISHER_ID
, SUPDEM.PUBLISHER_SITE_ID
, SUPDEM.PUBLISHER_NAME
, SUPDEM.PUBLISHER_SITE_NAME
, SUPDEM.NEW_SCHEDULE_DATE
, SUPDEM.INVENTORY_ITEM_ID
, SUPDEM.QUANTITY
, SUPDEM.TP_QUANTITY
, SUPDEM.COMMENTS
, SUPDEM.PUBLISHER_ORDER_TYPE
, SUPDEM.SUPPLIER_ID
, SUPDEM.SUPPLIER_NAME
, SUPDEM.SUPPLIER_SITE_ID
, SUPDEM.SUPPLIER_SITE_NAME
, SUPDEM.CUSTOMER_ID
, SUPDEM.CUSTOMER_NAME
, SUPDEM.CUSTOMER_SITE_ID
, SUPDEM.CUSTOMER_SITE_NAME
, SUPDEM.LINE_CODE
, SUPDEM.BUCKET_TYPE
, SUPDEM.ORDER_NUMBER
, SUPDEM.END_ORDER_NUMBER
, SUPDEM.NEW_DOCK_DATE
, SUPDEM.POSTING_PARTY_ID
, SUPDEM.NEW_SHIP_DATE
, SUPDEM.NEW_ORDER_PLACEMENT_DATE
, SUPDEM.RELEASE_NUMBER
, SUPDEM.LINE_NUMBER
, SUPDEM.END_ORDER_REL_NUMBER
, SUPDEM.END_ORDER_LINE_NUMBER
, SUPDEM.PUBLISHER_ADDRESS_ID
, SUPDEM.CARRIER_CODE
, SUPDEM.VEHICLE_NUMBER
, SUPDEM.CONTAINER_TYPE
, SUPDEM.CONTAINER_QTY
, SUPDEM.TRACKING_NUMBER
, SUPDEM.VERSION
, SUPDEM.END_ORDER_TYPE
, SUPDEM.END_ORDER_PUBLISHER_ID
, SUPDEM.SHIP_TO_ADDRESS
, SUPDEM.SHIP_FROM_PARTY_ID
, SUPDEM.SHIP_TO_PARTY_ID
, SUPDEM.SHIP_TO_PARTY_SITE_ID
, SUPDEM.SHIP_TO_PARTY_ADDRESS_ID
, SUPDEM.SHIP_TO_PARTY_NAME
, SUPDEM.SHIP_TO_PARTY_SITE_NAME
, SUPDEM.SHIP_FROM_PARTY_ADDRESS_ID
, SUPDEM.SHIP_FROM_PARTY_SITE_ID
, SUPDEM.SHIP_FROM_PARTY_NAME
, SUPDEM.SHIP_FROM_PARTY_SITE_NAME
, SUPDEM.OWNING_SITE_ID
, SUPDEM.OWNING_SITE_NAME
, SUPDEM.END_ORDER_PUBLISHER_SITE_ID
, SUPDEM.END_ORDER_PUBLISHER_SITE_NAME
, SUPDEM.END_ORDER_PUBLISHER_NAME
, SUPDEM.ITEM_NAME
, SUPDEM.OWNER_ITEM_NAME
, SUPDEM.CUSTOMER_ITEM_NAME
, SUPDEM.SUPPLIER_ITEM_NAME
, SUPDEM.ITEM_DESCRIPTION
, SUPDEM.CUSTOMER_ITEM_DESCRIPTION
, SUPDEM.SUPPLIER_ITEM_DESCRIPTION
, SUPDEM.OWNER_ITEM_DESCRIPTION
, SUPDEM.PUBLISHER_ORDER_TYPE_DESC
, SUPDEM.DESIGNATOR
, SUPDEM.CONTEXT
, SUPDEM.UNIT_NUMBER
, SUPDEM.SHIP_METHOD
, SUPDEM.PROJECT_NUMBER
, SUPDEM.TASK_NUMBER
, SUPDEM.PLANNING_GROUP
, SUPDEM.SHIP_FROM_ADDRESS
, SUPDEM.PUBLISHER_ADDRESS
, SUPDEM.CUSTOMER_ADDRESS
, SUPDEM.SUPPLIER_ADDRESS
, SUPDEM.AVERAGE_ANNUAL_DEMAND
, SUPDEM.BILL_OF_LADING_NUMBER
, SUPDEM.BUCKET_TYPE_DESC
, SUPDEM.SERIAL_NUMBER
, SUPDEM.RELEASE_STATUS
, SUPDEM.INVENTORY_STATUS
, SUPDEM.WIP_END_DATE
, SUPDEM.WIP_START_DATE
, SUPDEM.PROMISE_SHIP_DATE
, SUPDEM.NEW_SCHEDULE_END_DATE
, SUPDEM.POSTING_PARTY_NAME
, SUPDEM.UOM_CODE
, SUPDEM.TP_UOM_CODE
, SUPDEM.PLANNER_CODE
, SUPDEM.LAST_REFRESH_NUMBER
, SUPDEM.ATTACHMENT_URL
, SUPDEM.ATTRIBUTE1
, SUPDEM.ATTRIBUTE2
, SUPDEM.ATTRIBUTE3
, SUPDEM.ATTRIBUTE4
, SUPDEM.ATTRIBUTE5
, SUPDEM.ATTRIBUTE6
, SUPDEM.ATTRIBUTE7
, SUPDEM.ATTRIBUTE8
, SUPDEM.ATTRIBUTE9
, SUPDEM.ATTRIBUTE10
, SUPDEM.ATTRIBUTE11
, SUPDEM.ATTRIBUTE12
, SUPDEM.ATTRIBUTE13
, SUPDEM.ATTRIBUTE14
, SUPDEM.ATTRIBUTE15
, SUPDEM.REQUEST_ID
, SUPDEM.PROGRAM_ID
, SUPDEM.PROGRAM_APPLICATION_ID
, SUPDEM.PROGRAM_UPDATE_DATE
, SUPDEM.CREATED_BY
, SUPDEM.CREATION_DATE
, SUPDEM.LAST_UPDATED_BY
, SUPDEM.LAST_UPDATE_DATE
, SUPDEM.LAST_UPDATE_LOGIN
, SUPDEM.KEY_DATE
, SUPDEM.SHIP_DATE
, SUPDEM.RECEIPT_DATE
, SUPDEM.ORIGINAL_PROMISED_DATE
, SUPDEM.REQUEST_DATE
, SUPDEM.PRIMARY_UOM
, SUPDEM.PRIMARY_QUANTITY
, SUPDEM.REF_HEADER_ID
, SUPDEM.REF_LINE_ID
, SUPDEM.CATEGORY_NAME
, SUPDEM.PUBLISHER_ORG_ID
, SUPDEM.SUBSCRIBER_ORG_ID
, SUPDEM.ASSEMBLY_ITEM_ID
, SUPDEM.CATEGORY_ID
, SUPDEM.START_DATE
, SUPDEM.END_DATE
, SUPDEM.PRODUCTION_SEQUENCE
, SUPDEM.SEQ_PLAN_COMPLETION_FLAG
, SUPDEM.INPUT_PLAN_ID
, SUPDEM.END_ASSEMBLY_ITEM_NAME
, SUPDEM.SUBSCRIBER_ADDRESS_ID
, SUPDEM.TP_ORDER_TYPE
, SUPDEM.PROCESSED_FLAG