View: PA_PROJ_SUMMARY_AMOUNTS_V

Product:	PA - Projects
Description:	10SC Only
Implementation/DBA Data:	APPS.PA_PROJ_SUMMARY_AMOUNTS_V

View Text

SELECT P.ORGANIZATION_ID
, P.ORGANIZATION_NAME
, P.MEMBER_PERSON_ID
, P.MEMBER_NAME
, P.PROJECT_ROLE_TYPE
, P.PROJECT_ROLE
, P.PROJECT_ID
, P.PROJECT_NUMBER
, P.PROJECT_NAME
, P.DESCRIPTION
, SUM(NVL(PF.PROJFUNC_BASELINED_AMOUNT
, 0))
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
FROM PA_SUMMARY_PROJECT_FUNDINGS PF
, PA_PROJ_MEMBERS_VIEW P
WHERE PF.PROJECT_ID = P.PROJECT_ID GROUP BY P.ORGANIZATION_ID
, P.ORGANIZATION_NAME
, P.MEMBER_PERSON_ID
, P.MEMBER_NAME
, P.PROJECT_ROLE_TYPE
, P.PROJECT_ROLE
, P.PROJECT_ID
, P.PROJECT_NUMBER
, P.PROJECT_NAME
, P.DESCRIPTION UNION ALL SELECT P.ORGANIZATION_ID
, P.ORGANIZATION_NAME
, P.MEMBER_PERSON_ID
, P.MEMBER_NAME
, P.PROJECT_ROLE_TYPE
, P.PROJECT_ROLE
, P.PROJECT_ID
, P.PROJECT_NUMBER
, P.PROJECT_NAME
, P.DESCRIPTION
, 0
, SUM(NVL(DRI.AMOUNT
, 0))
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
FROM PA_DRAFT_REVENUE_ITEMS DRI
, PA_DRAFT_REVENUES DR
, PA_PROJ_MEMBERS_VIEW P
WHERE DR.GENERATION_ERROR_FLAG = 'N'
AND DR.PROJECT_ID = DRI.PROJECT_ID
AND DR.DRAFT_REVENUE_NUM = DRI.DRAFT_REVENUE_NUM
AND DR.PROJECT_ID = P.PROJECT_ID GROUP BY P.ORGANIZATION_ID
, P.ORGANIZATION_NAME
, P.MEMBER_PERSON_ID
, P.MEMBER_NAME
, P.PROJECT_ROLE_TYPE
, P.PROJECT_ROLE
, P.PROJECT_ID
, P.PROJECT_NUMBER
, P.PROJECT_NAME
, P.DESCRIPTION UNION ALL SELECT P.ORGANIZATION_ID
, P.ORGANIZATION_NAME
, P.MEMBER_PERSON_ID
, P.MEMBER_NAME
, P.PROJECT_ROLE_TYPE
, P.PROJECT_ROLE
, P.PROJECT_ID
, P.PROJECT_NUMBER
, P.PROJECT_NAME
, P.DESCRIPTION
, 0
, 0
, SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION
, 'WRITE OFF'
, NVL(E.REVENUE_AMOUNT
, 0)
, 0))
, SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION
, 'WRITE OFF'
, 0
, NVL(E.REVENUE_AMOUNT
, 0)))
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
FROM PA_EVENTS E
, PA_EVENT_TYPES ET
, PA_PROJ_MEMBERS_VIEW P
WHERE E.EVENT_TYPE = ET.EVENT_TYPE
AND E.REVENUE_AMOUNT <> 0
AND E.PROJECT_ID = P.PROJECT_ID
AND EXISTS (SELECT 'EVENT REVENUE'
FROM PA_CUST_EVENT_REV_DIST_LINES ERDL
WHERE NVL(ERDL.TASK_ID
, 0) = NVL(E.TASK_ID
, 0)
AND ERDL.PROJECT_ID = E.PROJECT_ID
AND ERDL.EVENT_NUM = E.EVENT_NUM) GROUP BY P.ORGANIZATION_ID
, P.ORGANIZATION_NAME
, P.MEMBER_PERSON_ID
, P.MEMBER_NAME
, P.PROJECT_ROLE_TYPE
, P.PROJECT_ROLE
, P.PROJECT_ID
, P.PROJECT_NUMBER
, P.PROJECT_NAME
, P.DESCRIPTION UNION ALL SELECT P.ORGANIZATION_ID
, P.ORGANIZATION_NAME
, P.MEMBER_PERSON_ID
, P.MEMBER_NAME
, P.PROJECT_ROLE_TYPE
, P.PROJECT_ROLE
, P.PROJECT_ID
, P.PROJECT_NUMBER
, P.PROJECT_NAME
, P.DESCRIPTION
, 0
, 0
, 0
, 0
, SUM(ITEM.AMOUNT)
, - SUM(DECODE(ITEM.INVOICE_LINE_TYPE
, 'RETENTION'
, ITEM.AMOUNT
, 0))
, SUM(DECODE(NVL(INV.RETENTION_INVOICE_FLAG
, 'N')
, 'Y'
, ITEM.AMOUNT
, 0))
, SUM(ITEM.AMOUNT - (DECODE(ITEM.INVOICE_LINE_TYPE
, 'RETENTION'
, ITEM.AMOUNT
, 0)) - (DECODE(NVL(INV.RETENTION_INVOICE_FLAG
, 'N')
, 'Y'
, ITEM.AMOUNT
, 0)))
, SUM(ITEM.PROJFUNC_BILL_AMOUNT)
, - SUM(DECODE(ITEM.INVOICE_LINE_TYPE
, 'RETENTION'
, DECODE(NVL(INV.RETENTION_INVOICE_FLAG
, 'N')
, 'N'
, DECODE(NVL(INV.WRITE_OFF_FLAG
, 'N')
, 'N'
, ITEM.PROJFUNC_BILL_AMOUNT
, 0)
, 0)
, 0))
, SUM(DECODE(NVL(INV.RETENTION_INVOICE_FLAG
, 'N')
, 'Y'
, DECODE(NVL(INV.WRITE_OFF_FLAG
, 'N')
, 'N'
, ITEM.PROJFUNC_BILL_AMOUNT
, 0)
, 0))
, SUM(DECODE(ITEM.INVOICE_LINE_TYPE
, 'RETENTION'
, 0
, DECODE(NVL(INV.WRITE_OFF_FLAG
, 'N')
, 'Y'
, 0
, ITEM.PROJFUNC_BILL_AMOUNT)))
, 0
, 0
, SUM(ITEM.AMOUNT - (DECODE(ITEM.INVOICE_LINE_TYPE
, 'RETENTION'
, ITEM.AMOUNT
, 0)) - (DECODE(NVL(INV.RETENTION_INVOICE_FLAG
, 'N')
, 'Y'
, ITEM.AMOUNT
, 0)) - (DECODE(NVL(INV.WRITE_OFF_FLAG
, 'N')
, 'Y'
, ITEM.AMOUNT
, 0)))
, -SUM(DECODE(NVL(INV.WRITE_OFF_FLAG
, 'N')
, 'Y'
, ITEM.PROJFUNC_BILL_AMOUNT
, 0))
FROM PA_DRAFT_INVOICE_ITEMS ITEM
, PA_DRAFT_INVOICES INV
, PA_PROJ_MEMBERS_VIEW P
WHERE INV.PROJECT_ID = ITEM.PROJECT_ID
AND INV.DRAFT_INVOICE_NUM = ITEM.DRAFT_INVOICE_NUM
AND INV.RELEASED_DATE IS NOT NULL
AND INV.PROJECT_ID = P.PROJECT_ID GROUP BY P.ORGANIZATION_ID
, P.ORGANIZATION_NAME
, P.MEMBER_PERSON_ID
, P.MEMBER_NAME
, P.PROJECT_ROLE_TYPE
, P.PROJECT_ROLE
, P.PROJECT_ID
, P.PROJECT_NUMBER
, P.PROJECT_NAME
, P.DESCRIPTION UNION ALL SELECT /*+INDEX(RDL PA_CUST_REV_DIST_LINES_N2) */ P.ORGANIZATION_ID
, P.ORGANIZATION_NAME
, P.MEMBER_PERSON_ID
, P.MEMBER_NAME
, P.PROJECT_ROLE_TYPE
, P.PROJECT_ROLE
, P.PROJECT_ID
, P.PROJECT_NUMBER
, P.PROJECT_NAME
, P.DESCRIPTION
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, SUM(DECODE(EI.BILL_HOLD_FLAG
, 'N'
, 0
, RDL.BILL_TRANS_BILL_AMOUNT))
, SUM(DECODE(EI.BILL_HOLD_FLAG
, 'N'
, 0
, 1))
, 0
, 0
FROM PA_EXPENDITURE_ITEMS_ALL EI
, PA_PROJ_MEMBERS_VIEW P
, PA_CUST_REV_DIST_LINES RDL
WHERE EI.BILLABLE_FLAG = 'Y'
AND EI.PROJECT_ID = P.PROJECT_ID
AND RDL.PROJECT_ID = P.PROJECT_ID
AND RDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND RDL.LINE_NUM = (SELECT MAX(LINE_NUM)
FROM PA_CUST_REV_DIST_LINES RDL1
WHERE RDL1.EXPENDITURE_ITEM_ID = RDL.EXPENDITURE_ITEM_ID)
AND RDL.DRAFT_INVOICE_NUM IS NULL GROUP BY P.ORGANIZATION_ID
, P.ORGANIZATION_NAME
, P.MEMBER_PERSON_ID
, P.MEMBER_NAME
, P.PROJECT_ROLE_TYPE
, P.PROJECT_ROLE
, P.PROJECT_ID
, P.PROJECT_NUMBER
, P.PROJECT_NAME
, P.DESCRIPTION

Columns