VIEW: APPS.PO_NOTIFICATION_CTRL_MERGE_V

Source

View Text - Preformatted

SELECT DFT.owner_user_id ,DFT.owner_role ,DFT.status ,DFT.draft_id ,PNCD.change_accepted_flag ,PNCD.delete_flag ,PNCD.NOTIFICATION_ID ,PNCD.PO_HEADER_ID ,PNCD.START_DATE_ACTIVE ,PNCD.END_DATE_ACTIVE ,PNCD.NOTIFICATION_AMOUNT ,PNCD.NOTIFICATION_CONDITION_CODE ,PNCD.NOTIFICATION_QTY_PERCENTAGE ,PNCD.LAST_UPDATE_DATE ,PNCD.LAST_UPDATE_LOGIN ,PNCD.LAST_UPDATED_BY ,PNCD.PROGRAM_ID ,PNCD.CREATED_BY ,PNCD.CREATION_DATE ,PNCD.ATTRIBUTE_CATEGORY ,PNCD.ATTRIBUTE1 ,PNCD.ATTRIBUTE2 ,PNCD.ATTRIBUTE3 ,PNCD.ATTRIBUTE4 ,PNCD.ATTRIBUTE5 ,PNCD.ATTRIBUTE6 ,PNCD.ATTRIBUTE7 ,PNCD.ATTRIBUTE8 ,PNCD.ATTRIBUTE9 ,PNCD.ATTRIBUTE10 ,PNCD.ATTRIBUTE11 ,PNCD.ATTRIBUTE12 ,PNCD.ATTRIBUTE13 ,PNCD.ATTRIBUTE14 ,PNCD.ATTRIBUTE15 ,PNCD.PROGRAM_APPLICATION_ID ,PNCD.PROGRAM_UPDATE_DATE ,PNCD.REQUEST_ID FROM po_notification_ctrl_draft PNCD, po_drafts DFT WHERE PNCD.draft_id = DFT.draft_id AND NVL(PNCD.delete_flag, 'N') = 'N' AND DFT.owner_role = PO_GLOBAL.role AND DFT.status <> 'COMPLETED' UNION ALL SELECT TO_NUMBER(NULL) ,NULL ,NULL ,TO_NUMBER(NULL) ,NULL ,NULL ,PNC.NOTIFICATION_ID ,PNC.PO_HEADER_ID ,PNC.START_DATE_ACTIVE ,PNC.END_DATE_ACTIVE ,PNC.NOTIFICATION_AMOUNT ,PNC.NOTIFICATION_CONDITION_CODE ,PNC.NOTIFICATION_QTY_PERCENTAGE ,PNC.LAST_UPDATE_DATE ,PNC.LAST_UPDATE_LOGIN ,PNC.LAST_UPDATED_BY ,PNC.PROGRAM_ID ,PNC.CREATED_BY ,PNC.CREATION_DATE ,PNC.ATTRIBUTE_CATEGORY ,PNC.ATTRIBUTE1 ,PNC.ATTRIBUTE2 ,PNC.ATTRIBUTE3 ,PNC.ATTRIBUTE4 ,PNC.ATTRIBUTE5 ,PNC.ATTRIBUTE6 ,PNC.ATTRIBUTE7 ,PNC.ATTRIBUTE8 ,PNC.ATTRIBUTE9 ,PNC.ATTRIBUTE10 ,PNC.ATTRIBUTE11 ,PNC.ATTRIBUTE12 ,PNC.ATTRIBUTE13 ,PNC.ATTRIBUTE14 ,PNC.ATTRIBUTE15 ,PNC.PROGRAM_APPLICATION_ID ,PNC.PROGRAM_UPDATE_DATE ,PNC.REQUEST_ID FROM PO_NOTIFICATION_CONTROLS PNC WHERE NOT EXISTS ( SELECT 1 FROM po_notification_ctrl_draft PNCD1, po_drafts DFT1 WHERE PNC.notification_id = PNCD1.notification_id AND PNCD1.draft_id = DFT1.draft_id AND DFT1.owner_role = PO_GLOBAL.role AND DFT1.status <> 'COMPLETED')

View Text - HTML Formatted

SELECT DFT.OWNER_USER_ID
, DFT.OWNER_ROLE
, DFT.STATUS
, DFT.DRAFT_ID
, PNCD.CHANGE_ACCEPTED_FLAG
, PNCD.DELETE_FLAG
, PNCD.NOTIFICATION_ID
, PNCD.PO_HEADER_ID
, PNCD.START_DATE_ACTIVE
, PNCD.END_DATE_ACTIVE
, PNCD.NOTIFICATION_AMOUNT
, PNCD.NOTIFICATION_CONDITION_CODE
, PNCD.NOTIFICATION_QTY_PERCENTAGE
, PNCD.LAST_UPDATE_DATE
, PNCD.LAST_UPDATE_LOGIN
, PNCD.LAST_UPDATED_BY
, PNCD.PROGRAM_ID
, PNCD.CREATED_BY
, PNCD.CREATION_DATE
, PNCD.ATTRIBUTE_CATEGORY
, PNCD.ATTRIBUTE1
, PNCD.ATTRIBUTE2
, PNCD.ATTRIBUTE3
, PNCD.ATTRIBUTE4
, PNCD.ATTRIBUTE5
, PNCD.ATTRIBUTE6
, PNCD.ATTRIBUTE7
, PNCD.ATTRIBUTE8
, PNCD.ATTRIBUTE9
, PNCD.ATTRIBUTE10
, PNCD.ATTRIBUTE11
, PNCD.ATTRIBUTE12
, PNCD.ATTRIBUTE13
, PNCD.ATTRIBUTE14
, PNCD.ATTRIBUTE15
, PNCD.PROGRAM_APPLICATION_ID
, PNCD.PROGRAM_UPDATE_DATE
, PNCD.REQUEST_ID
FROM PO_NOTIFICATION_CTRL_DRAFT PNCD
, PO_DRAFTS DFT
WHERE PNCD.DRAFT_ID = DFT.DRAFT_ID
AND NVL(PNCD.DELETE_FLAG
, 'N') = 'N'
AND DFT.OWNER_ROLE = PO_GLOBAL.ROLE
AND DFT.STATUS <> 'COMPLETED' UNION ALL SELECT TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, PNC.NOTIFICATION_ID
, PNC.PO_HEADER_ID
, PNC.START_DATE_ACTIVE
, PNC.END_DATE_ACTIVE
, PNC.NOTIFICATION_AMOUNT
, PNC.NOTIFICATION_CONDITION_CODE
, PNC.NOTIFICATION_QTY_PERCENTAGE
, PNC.LAST_UPDATE_DATE
, PNC.LAST_UPDATE_LOGIN
, PNC.LAST_UPDATED_BY
, PNC.PROGRAM_ID
, PNC.CREATED_BY
, PNC.CREATION_DATE
, PNC.ATTRIBUTE_CATEGORY
, PNC.ATTRIBUTE1
, PNC.ATTRIBUTE2
, PNC.ATTRIBUTE3
, PNC.ATTRIBUTE4
, PNC.ATTRIBUTE5
, PNC.ATTRIBUTE6
, PNC.ATTRIBUTE7
, PNC.ATTRIBUTE8
, PNC.ATTRIBUTE9
, PNC.ATTRIBUTE10
, PNC.ATTRIBUTE11
, PNC.ATTRIBUTE12
, PNC.ATTRIBUTE13
, PNC.ATTRIBUTE14
, PNC.ATTRIBUTE15
, PNC.PROGRAM_APPLICATION_ID
, PNC.PROGRAM_UPDATE_DATE
, PNC.REQUEST_ID
FROM PO_NOTIFICATION_CONTROLS PNC
WHERE NOT EXISTS ( SELECT 1
FROM PO_NOTIFICATION_CTRL_DRAFT PNCD1
, PO_DRAFTS DFT1
WHERE PNC.NOTIFICATION_ID = PNCD1.NOTIFICATION_ID
AND PNCD1.DRAFT_ID = DFT1.DRAFT_ID
AND DFT1.OWNER_ROLE = PO_GLOBAL.ROLE
AND DFT1.STATUS <> 'COMPLETED')