VIEW: APPS.PA_CE_BILLING_EVENTS_V

Source

View Text - Preformatted

SELECT PA.PROJECT_ID , EV.BILL_AMOUNT , EV.BILL_AMOUNT , PA.PROJECT_CURRENCY_CODE , PA_CURRENCY.GET_CURRENCY_CODE , EV.COMPLETION_DATE , PA.ORG_ID , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_CHAR(NULL) , EV.EVENT_NUM , EV.TASK_ID , EV.BILL_AMOUNT , PA.PROJECT_CURRENCY_CODE FROM PA_EVENTS EV, PA_PROJECTS_ALL PA WHERE EV.PROJECT_ID = PA.PROJECT_ID AND PA.DISTRIBUTION_RULE IS NOT NULL AND EV.COMPLETION_DATE IS NOT NULL AND NOT EXISTS (SELECT 'x' FROM PA_DRAFT_INVOICE_ITEMS ITEM WHERE ITEM.PROJECT_ID = EV.PROJECT_ID AND NVL(ITEM.EVENT_TASK_ID,0) = NVL(EV.TASK_ID,0) AND ITEM.EVENT_NUM = EV.EVENT_NUM HAVING SUM(ITEM.INV_AMOUNT) > 0) UNION ALL SELECT ITEM1.PROJECT_ID , ITEM1.INV_AMOUNT , ITEM1.AMOUNT , INV1.INV_CURRENCY_CODE , PA_CURRENCY.GET_CURRENCY_CODE , EV1.COMPLETION_DATE , INV1.ORG_ID , ITEM1.DRAFT_INVOICE_NUM , ITEM1.LINE_NUM , ITEM1.INVOICE_LINE_TYPE , EV1.EVENT_NUM , EV1.TASK_ID , EV1.BILL_AMOUNT , PA_CURRENCY.GET_CURRENCY_CODE FROM PA_EVENTS EV1, PA_DRAFT_INVOICE_ITEMS ITEM1, PA_DRAFT_INVOICES_ALL INV1 WHERE ITEM1.PROJECT_ID = EV1.PROJECT_ID AND NVL(ITEM1.EVENT_TASK_ID,0) = NVL(EV1.TASK_ID,0) AND ITEM1.EVENT_NUM = EV1.EVENT_NUM AND ITEM1.DRAFT_INVOICE_NUM = INV1.DRAFT_INVOICE_NUM AND ITEM1.PROJECT_ID = INV1.PROJECT_ID AND INV1.RELEASED_DATE IS NULL

View Text - HTML Formatted

SELECT PA.PROJECT_ID
, EV.BILL_AMOUNT
, EV.BILL_AMOUNT
, PA.PROJECT_CURRENCY_CODE
, PA_CURRENCY.GET_CURRENCY_CODE
, EV.COMPLETION_DATE
, PA.ORG_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, EV.EVENT_NUM
, EV.TASK_ID
, EV.BILL_AMOUNT
, PA.PROJECT_CURRENCY_CODE
FROM PA_EVENTS EV
, PA_PROJECTS_ALL PA
WHERE EV.PROJECT_ID = PA.PROJECT_ID
AND PA.DISTRIBUTION_RULE IS NOT NULL
AND EV.COMPLETION_DATE IS NOT NULL
AND NOT EXISTS (SELECT 'X'
FROM PA_DRAFT_INVOICE_ITEMS ITEM
WHERE ITEM.PROJECT_ID = EV.PROJECT_ID
AND NVL(ITEM.EVENT_TASK_ID
, 0) = NVL(EV.TASK_ID
, 0)
AND ITEM.EVENT_NUM = EV.EVENT_NUM HAVING SUM(ITEM.INV_AMOUNT) > 0) UNION ALL SELECT ITEM1.PROJECT_ID
, ITEM1.INV_AMOUNT
, ITEM1.AMOUNT
, INV1.INV_CURRENCY_CODE
, PA_CURRENCY.GET_CURRENCY_CODE
, EV1.COMPLETION_DATE
, INV1.ORG_ID
, ITEM1.DRAFT_INVOICE_NUM
, ITEM1.LINE_NUM
, ITEM1.INVOICE_LINE_TYPE
, EV1.EVENT_NUM
, EV1.TASK_ID
, EV1.BILL_AMOUNT
, PA_CURRENCY.GET_CURRENCY_CODE
FROM PA_EVENTS EV1
, PA_DRAFT_INVOICE_ITEMS ITEM1
, PA_DRAFT_INVOICES_ALL INV1
WHERE ITEM1.PROJECT_ID = EV1.PROJECT_ID
AND NVL(ITEM1.EVENT_TASK_ID
, 0) = NVL(EV1.TASK_ID
, 0)
AND ITEM1.EVENT_NUM = EV1.EVENT_NUM
AND ITEM1.DRAFT_INVOICE_NUM = INV1.DRAFT_INVOICE_NUM
AND ITEM1.PROJECT_ID = INV1.PROJECT_ID
AND INV1.RELEASED_DATE IS NULL