Product: | IGW - Grants Proposal |
---|---|
Description: | 10SC Only |
Implementation/DBA Data: | APPS.IGW_PROPOSALS_V |
SELECT PROP.ROWID ROW_ID
, PROP.PROPOSAL_ID
, PROP.PROPOSAL_NUMBER
, PROP.ORG_ID
, PROP.PROPOSAL_STATUS
, LOOKUP1.MEANING PROPOSAL_STATUS_DESC
, PROP.LEAD_ORGANIZATION_ID
, ORG1.NAME LEAD_ORGANIZATION_NAME
, PROP.PROPOSAL_START_DATE
, PROP.PROPOSAL_END_DATE
, PROP.PROPOSAL_TITLE
, PROP.PROPOSAL_TYPE_CODE
, LOOKUP2.MEANING PROPOSAL_TYPE_DESC
, PROP.ORIGINAL_PROPOSAL_NUMBER
, PROP.ORIGINAL_AWARD_NUMBER
, PROP.ORIGINAL_PROPOSAL_START_DATE
, PROP.ORIGINAL_PROPOSAL_END_DATE
, PROP.ACTIVITY_TYPE_CODE
, LOOKUP3.MEANING ACTIVITY_TYPE_DESC
, PROP.SPONSOR_ID
, CUST.CUSTOMER_NAME SPONSOR_NAME
, PROP.FUNDING_SPONSOR_UNIT
, PROP.ORIGINAL_SPONSOR_ID
, CUST1.CUSTOMER_NAME ORIGINAL_SPONSOR_NAME
, PROP.SPONSOR_PROPOSAL_NUMBER
, PROP.DEADLINE_DATE
, PROP.DEADLINE_TYPE
, PROP.NOTICE_OF_OPPORTUNITY_CODE
, LOOKUP4.MEANING NOTICE_OF_OPPORTUNITY_DESC
, PROP.PROGRAM_NUMBER
, PROP.PROGRAM_TITLE
, PROP.PROGRAM_URL
, PROP.LETTER_OF_INTENT_DUE_DATE
, PROP.SUBMITTING_ORGANIZATION_ID
, PROP.SIGNING_OFFICIAL_ID
, SGN.FULL_NAME SIGNING_OFFICIAL_NAME
, PROP.ADMIN_OFFICIAL_ID
, ADM.FULL_NAME ADMIN_OFFICIAL_NAME
, PROP.SPONSOR_ACTION_CODE
, LOOKUP5.MEANING SPONSOR_ACTION_DESC
, PROP.SPONSOR_ACTION_DATE
, PROP.SPONSOR_ACTION_COMMENTS
, PROP.AWARD_NUMBER
, PROP.AWARD_AMOUNT
, PROP.EDI_GENERATED_DATE
, PROP.NARRATIVE_TYPE_CODE
, PROP.NARRATIVE_SUBMISSION_CODE
, PROP.LAST_UPDATE_DATE
, PROP.LAST_UPDATED_BY
, PROP.CREATION_DATE
, PROP.CREATED_BY
, PROP.LAST_UPDATE_LOGIN
, PROP.ATTRIBUTE_CATEGORY
, PROP.ATTRIBUTE1
, PROP.ATTRIBUTE2
, PROP.ATTRIBUTE3
, PROP.ATTRIBUTE4
, PROP.ATTRIBUTE5
, PROP.ATTRIBUTE6
, PROP.ATTRIBUTE7
, PROP.ATTRIBUTE8
, PROP.ATTRIBUTE9
, PROP.ATTRIBUTE10
, PROP.ATTRIBUTE11
, PROP.ATTRIBUTE12
, PROP.ATTRIBUTE13
, PROP.ATTRIBUTE14
, PROP.ATTRIBUTE15
, SUBSTR(IGW_PROP.GET_PI_FULL_NAME(PROP.PROPOSAL_ID)
, 1
, 240) PI_FULL_NAME
, SUBSTR(IGW_SECURITY.ALLOW_QUERY('PROPOSAL'
, PROP.PROPOSAL_ID
, FND_GLOBAL.USER_ID)
, 1
, 1) ALLOW_QUERY
, SUBSTR(IGW_SECURITY.ALLOW_MODIFY('PROPOSAL'
, PROP.PROPOSAL_ID
, FND_GLOBAL.USER_ID)
, 1
, 1) ALLOW_MODIFY
FROM IGW_PROPOSALS_ALL PROP
, RA_CUSTOMERS CUST
, RA_CUSTOMERS CUST1
, FND_LOOKUPS LOOKUP1
, FND_LOOKUPS LOOKUP2
, FND_LOOKUPS LOOKUP3
, FND_LOOKUPS LOOKUP4
, FND_LOOKUPS LOOKUP5
, HR_ORGANIZATION_UNITS ORG1
, PER_PEOPLE_X SGN
, PER_PEOPLE_X ADM
WHERE CUST.CUSTOMER_ID(+) = PROP.SPONSOR_ID
AND CUST1.CUSTOMER_ID(+) = PROP.ORIGINAL_SPONSOR_ID
AND LOOKUP1.LOOKUP_CODE = PROP.PROPOSAL_STATUS
AND LOOKUP1.LOOKUP_TYPE = 'IGW_PROPOSAL_STATUS'
AND LOOKUP2.LOOKUP_CODE = PROP.PROPOSAL_TYPE_CODE
AND LOOKUP2.LOOKUP_TYPE = 'IGW_PROPOSAL_TYPES'
AND LOOKUP3.LOOKUP_CODE = PROP.ACTIVITY_TYPE_CODE
AND LOOKUP3.LOOKUP_TYPE = 'IGW_ACTIVITY_TYPES'
AND LOOKUP4.LOOKUP_CODE(+) = PROP.NOTICE_OF_OPPORTUNITY_CODE
AND LOOKUP4.LOOKUP_TYPE(+) = 'IGW_OPPORTUNITY_NOTICE_TYPES'
AND LOOKUP5.LOOKUP_CODE(+) = PROP.SPONSOR_ACTION_CODE
AND LOOKUP5.LOOKUP_TYPE(+) = 'IGW_SPONSOR_ACTION_CODE'
AND ORG1.ORGANIZATION_ID = PROP.LEAD_ORGANIZATION_ID
AND SGN.PERSON_ID(+) = PROP.SIGNING_OFFICIAL_ID
AND ADM.PERSON_ID(+) = PROP.ADMIN_OFFICIAL_ID
AND NVL(ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)