View: PA_PROJ_BILLING_DETAIL_VIEW

Product:	PA - Projects
Description:	Base view used to report billing status detail for projects
Implementation/DBA Data:	APPS.PA_PROJ_BILLING_DETAIL_VIEW

View Text

SELECT DECODE(P.INVOICE_METHOD
, 'EVENT'
, 'EVENT'
, 'WORK')
, P.PROJECT_ID
, P.SEGMENT1
, P.NAME
, NVL(P.RETN_ACCOUNTING_FLAG
, 'N')
, TO_CHAR(PDI.DRAFT_INVOICE_NUM)
, TO_CHAR(PDI.INVOICE_DATE)
, TO_CHAR(PDI.BILL_THROUGH_DATE)
, ROUND(TO_NUMBER(TRUNC(SYSDATE) - NVL(PDI.INVOICE_DATE
, NVL(P.START_DATE
, P.CREATION_DATE))))
, TO_CHAR(PA_BILLING_CYCLES_PKG.GET_NEXT_BILLING_DATE( P.PROJECT_ID
, NVL(P.START_DATE
, P.CREATION_DATE)
, P.BILLING_CYCLE_ID
, P.BILLING_OFFSET
, NULL
, NVL(PDI.BILL_THROUGH_DATE
, PDI.CREATION_DATE)))
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, P.ENABLE_TOP_TASK_INV_MTH_FLAG
, 0
FROM PA_PROJECT_TYPES_ALL PT
, PA_DRAFT_INVOICES PDI
, PA_PROJECTS_ALL P
WHERE PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED (P.PROJECT_STATUS_CODE
, 'GENERATE_INV') = 'Y'
AND P.PROJECT_TYPE = PT.PROJECT_TYPE
AND PT.DIRECT_FLAG = 'Y'
AND PDI.PROJECT_ID = P.PROJECT_ID
AND PDI.DRAFT_INVOICE_NUM = PA_BILLING_CYCLES_PKG.GET_LAST_RELEASED_INVOICE_NUM( P.PROJECT_ID )
AND PA_BILLING_CYCLES_PKG.GET_LAST_RELEASED_INVOICE_NUM(P.PROJECT_ID) IS NOT NULL
AND (P.ORG_ID IS NULL OR P.ORG_ID = PT.ORG_ID) UNION ALL SELECT DECODE(P.INVOICE_METHOD
, 'EVENT'
, 'EVENT'
, 'WORK')
, P.PROJECT_ID
, P.SEGMENT1
, P.NAME
, NVL(P.RETN_ACCOUNTING_FLAG
, 'N')
, NULL
, NULL
, NULL
, ROUND(TO_NUMBER(TRUNC(SYSDATE)- NVL(P.START_DATE
, P.CREATION_DATE)))
, TO_CHAR(PA_BILLING_CYCLES_PKG.GET_NEXT_BILLING_DATE( P.PROJECT_ID
, NVL(P.START_DATE
, P.CREATION_DATE)
, P.BILLING_CYCLE_ID
, P.BILLING_OFFSET
, NULL
, NULL ))
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, P.ENABLE_TOP_TASK_INV_MTH_FLAG
, 0
FROM PA_PROJECTS_ALL P
, PA_PROJECT_TYPES_ALL PT
WHERE PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED (P.PROJECT_STATUS_CODE
, 'GENERATE_INV') = 'Y'
AND P.PROJECT_TYPE = PT.PROJECT_TYPE
AND PT.DIRECT_FLAG = 'Y'
AND PA_BILLING_CYCLES_PKG.GET_LAST_RELEASED_INVOICE_NUM(P.PROJECT_ID) IS NULL
AND (P.ORG_ID IS NULL OR P.ORG_ID = PT.ORG_ID) UNION ALL SELECT DECODE(P.INVOICE_METHOD
, 'EVENT'
, 'EVENT'
, 'WORK')
, P.PROJECT_ID
, P.SEGMENT1
, P.NAME
, NVL(P.RETN_ACCOUNTING_FLAG
, 'N')
, NULL
, NULL
, NULL
, 0
, NULL
, MIN(DECODE(SUBSTR(P.DISTRIBUTION_RULE
, INSTR(P.DISTRIBUTION_RULE
, '/')+1
, LENGTH(P.DISTRIBUTION_RULE))
, 'EVENT'
, E.BILL_AMOUNT
, 0))
, 0
, 0
, 0
, MIN(DECODE(SUBSTR(P.DISTRIBUTION_RULE
, INSTR(P.DISTRIBUTION_RULE
, '/')+1
, LENGTH(P.DISTRIBUTION_RULE))
, 'EVENT'
, E.BILL_TRANS_BILL_AMOUNT
, 0))
, 0
, 0
, 0
, 0
, 0
, P.ENABLE_TOP_TASK_INV_MTH_FLAG
, MIN(E.EVENT_ID) EVENT_ID
FROM PA_PROJECTS P
, PA_EVENTS E
WHERE P.PROJECT_ID = E.PROJECT_ID
AND E.EVENT_ID = ( SELECT MIN(EVENT_ID) FROM PA_EVENTS A WHERE A.BILLED_FLAG ='N' AND A.COMPLETION_DATE IS NOT NULL AND P.PROJECT_ID = A.PROJECT_ID AND A.COMPLETION_DATE = ( SELECT MIN(E2.COMPLETION_DATE) FROM PA_EVENTS E2 WHERE E2.BILLED_FLAG ='N' AND A.PROJECT_ID = E2.PROJECT_ID AND E2.COMPLETION_DATE IS NOT NULL ) ) GROUP BY DECODE(P.INVOICE_METHOD
, 'EVENT'
, 'EVENT'
, 'WORK')
, P.PROJECT_ID
, P.SEGMENT1
, P.NAME
, NVL(P.RETN_ACCOUNTING_FLAG
, 'N')
, P.ENABLE_TOP_TASK_INV_MTH_FLAG UNION ALL SELECT NULL
, PDI.PROJECT_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 0
, NULL
, 0
, DECODE(PDI.RELEASED_DATE
, ''
, SUM(PDII.AMOUNT)
, 0)
, 0
, DECODE(PDI.RELEASED_DATE
, ''
, 0
, SUM(PDII.AMOUNT))
, 0
, DECODE(PDI.RELEASED_DATE
, ''
, SUM(PDII.PROJFUNC_BILL_AMOUNT)
, 0)
, 0
, DECODE(PDI.RELEASED_DATE
, ''
, 0
, SUM(PDII.PROJFUNC_BILL_AMOUNT))
, DECODE(PDI.RELEASED_DATE
, ''
, 0
, SUM(DECODE(PDII.INVOICE_LINE_TYPE
, 'RETENTION'
, 0
, DECODE(NVL(PDI.WRITE_OFF_FLAG
, 'N')
, 'Y'
, 0
, PDII.PROJFUNC_BILL_AMOUNT))))
, DECODE(PDI.RELEASED_DATE
, ''
, 0
, SUM(DECODE(PDII.INVOICE_LINE_TYPE
, 'RETENTION'
, DECODE(NVL(PDI.WRITE_OFF_FLAG
, 'N')
, 'Y'
, 0
, PDII.PROJFUNC_BILL_AMOUNT)
, 0)))
, NULL
, 0
FROM PA_DRAFT_INVOICES PDI
, PA_DRAFT_INVOICE_ITEMS PDII
WHERE PDI.PROJECT_ID = PDII.PROJECT_ID
AND PDI.DRAFT_INVOICE_NUM = PDII.DRAFT_INVOICE_NUM GROUP BY PDI.PROJECT_ID
, PDI.RELEASED_DATE UNION ALL SELECT NULL
, PDR.PROJECT_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 0
, NULL
, 0
, 0
, SUM(PDRI.AMOUNT) REVENUE
, 0
, 0
, 0
, SUM(PDRI.PROJFUNC_REVENUE_AMOUNT)
, 0
, 0
, 0
, NULL
, 0
FROM PA_DRAFT_REVENUES PDR
, PA_DRAFT_REVENUE_ITEMS PDRI
WHERE PDR.PROJECT_ID = PDRI.PROJECT_ID
AND PDR.DRAFT_REVENUE_NUM = PDRI.DRAFT_REVENUE_NUM GROUP BY PDR.PROJECT_ID

Columns