eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_PROJ_SUMMARY_AMOUNTS_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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