[Home] [Help]
| Product: | GMS - Grants Accounting |
|---|---|
| Description: | |
| Implementation/DBA Data: | Not implemented in this database |
SELECT GA.AWARD_ORGANIZATION_ID
, HR.NAME
, GA.FUNDING_SOURCE_ID
, RC.CUSTOMER_NAME
, GI.INSTALLMENT_ID
, GA.TYPE
, GP.PERSON_ID
, PPX.FULL_NAME
, GA.AWARD_ID
, GA.AWARD_NUMBER
, GA.AWARD_PURPOSE_CODE
, TRUNC(GI.START_DATE_ACTIVE)
, TRUNC(GI.END_DATE_ACTIVE)
, TRUNC(GA.START_DATE_ACTIVE)
, TRUNC(GA.END_DATE_ACTIVE)
, SUM(NVL(GI.DIRECT_COST
, 0))
, SUM(NVL(GI.INDIRECT_COST
, 0))
, SUM(NVL(GI.DIRECT_COST
, 0)+NVL(GI.INDIRECT_COST
, 0))
FROM GMS_INSTALLMENTS GI
, GMS_AWARDS_ALL GA
, GMS_PERSONNEL GP
, PER_PEOPLE_X PPX
, RA_CUSTOMERS RC
, HR_ALL_ORGANIZATION_UNITS HR
WHERE GI.AWARD_ID = GA.AWARD_ID
AND GA.AWARD_ID = GP.AWARD_ID
AND GP.PERSON_ID = PPX.PERSON_ID
AND RC.CUSTOMER_ID = GA.FUNDING_SOURCE_ID
AND RC.STATUS = 'A'
AND GA.STATUS = 'ACTIVE'
AND GI.ACTIVE_FLAG = 'Y'
AND GP.PERSONNEL_ID = (SELECT MIN(PERSONNEL_ID)
FROM GMS_PERSONNEL
WHERE AWARD_ID = GA.AWARD_ID
AND AWARD_ROLE = FND_PROFILE.VALUE('GMS_BIS_AWARD_ROLE')
AND TRUNC(SYSDATE) BETWEEN TRUNC(START_DATE_ACTIVE)
AND TRUNC(NVL(END_DATE_ACTIVE
, SYSDATE)))
AND HR.ORGANIZATION_ID = GA.AWARD_ORGANIZATION_ID GROUP BY GA.AWARD_ORGANIZATION_ID
, HR.NAME
, GA.FUNDING_SOURCE_ID
, RC.CUSTOMER_NAME
, GI.INSTALLMENT_ID
, GA.TYPE
, GP.PERSON_ID
, PPX.FULL_NAME
, GA.AWARD_ID
, GA.AWARD_NUMBER
, GA.AWARD_PURPOSE_CODE
, GI.START_DATE_ACTIVE
, GI.END_DATE_ACTIVE
, GA.START_DATE_ACTIVE
, GA.END_DATE_ACTIVE