Product: | IGW - Grants Proposal (Obsolete) |
---|---|
Description: | View displays core proposal information |
Implementation/DBA Data: | Not implemented in this database |
SELECT 'EDI'
, ETPD.TEST_FLAG
, 'PRPO'
, ETPD.DOCUMENT_TYPE
, 'PRPO'
, ETPD.TRANSLATOR_CODE
, RAA.ECE_TP_LOCATION_CODE
, ETPH.TP_DESCRIPTION
, ETPH.TP_REFERENCE_EXT1
, ETPH.TP_REFERENCE_EXT2
, SYSDATE TRANSACTION_DATE
, ETPH.ATTRIBUTE_CATEGORY
, ETPH.ATTRIBUTE1
, ETPH.ATTRIBUTE2
, ETPH.ATTRIBUTE3
, ETPH.ATTRIBUTE4
, ETPH.ATTRIBUTE5
, ETPH.ATTRIBUTE6
, ETPH.ATTRIBUTE7
, ETPH.ATTRIBUTE8
, ETPH.ATTRIBUTE9
, ETPH.ATTRIBUTE10
, ETPH.ATTRIBUTE11
, ETPH.ATTRIBUTE12
, ETPH.ATTRIBUTE13
, ETPH.ATTRIBUTE14
, ETPH.ATTRIBUTE15
, ETPD.ATTRIBUTE_CATEGORY
, ETPD.ATTRIBUTE1
, ETPD.ATTRIBUTE2
, ETPD.ATTRIBUTE3
, ETPD.ATTRIBUTE4
, ETPD.ATTRIBUTE5
, ETPD.DOCUMENT_STANDARD
, PROP.PROPOSAL_ID
, PROP.PROPOSAL_NUMBER
, TO_CHAR(RUN.STATUS_DATE
, 'YYYYMMDD')
, TO_CHAR(RUN.STATUS_DATE
, 'HHMISS')
, PROP.PROPOSAL_TITLE
, PROP.PROGRAM_NUMBER PROGRAM_ID_NUMBER
, PROP.PROGRAM_TITLE
, PROP.NOTICE_OF_OPPORTUNITY_CODE
, LKUP.MEANING
, PROP.ORIGINAL_PROPOSAL_NUMBER
, PROP.NARRATIVE_SUBMISSION_CODE
, PROP.NARRATIVE_TYPE_CODE
, PROP.AWARD_NUMBER
, SO.LAST_NAME
, SO.FIRST_NAME
, SO.MIDDLE_NAME
, SO.PREFIX
, SO.SUFFIX
, IGW_EXTENSION_PKG.PERSON_GOVT_ID(SO.PERSON_ID)
, SO.ADDRESS_LINE1
, SO.ADDRESS_LINE2
, SO.TOWN_OR_CITY
, SO.REGION_2
, SO.POSTAL_CODE
, SO.COUNTRY
, SO.REGION_1
, SO.TITLE SO_TITLE
, SO.WORK_PHONE
, SO.WORK_FAX
, SO.EMAIL_ADDRESS
, AO.LAST_NAME
, AO.FIRST_NAME
, AO.MIDDLE_NAME
, AO.PREFIX
, AO.SUFFIX
, IGW_EXTENSION_PKG.PERSON_GOVT_ID(AO.PERSON_ID)
, AO.ADDRESS_LINE1
, AO.ADDRESS_LINE2
, AO.TOWN_OR_CITY
, AO.REGION_2
, AO.POSTAL_CODE
, AO.COUNTRY
, AO.REGION_1
, AO.TITLE
, AO.WORK_PHONE
, AO.WORK_FAX
, AO.EMAIL_ADDRESS
, APORG.ORGANIZATION_NAME
, APORG.ADDRESS_LINE_1
, APORG.ADDRESS_LINE_2
, APORG.ADDRESS_LINE_3
, APORG.TOWN_OR_CITY
, APORG.REGION_2
, APORG.POSTAL_CODE
, APORG.COUNTRY
, APORG.REGION_1
, APORG.CONGRESS_DISTRICT
, APORG.DUNS_NUMBER
, APORG.DUNS4_NUMBER
, APORG.DODAAC_NUMBER
, APORG.CAGE_NUMBER
, APORG.TIN
, APORG.EIN
, APORG.ORG_IPF_CODE
, RAC.CUSTOMER_NAME
, RAA.ADDRESS1
, RAA.ADDRESS2
, RAA.CITY
, RAA.STATE
, RAA.POSTAL_CODE
, RAA.COUNTRY
, RAA.COUNTY
, PROP.FUNDING_SPONSOR_UNIT
, RAC.DUNS_NUMBER_C
, PROP.PROPOSAL_START_DATE
, PROP.PROPOSAL_END_DATE
, ROUND(MONTHS_BETWEEN(PROP.PROPOSAL_END_DATE
, PROP.PROPOSAL_START_DATE)) PROPOSAL_DURATION
, BUD.TOTAL_COST
, BUD.TOTAL_DIRECT_COST
, BUD.TOTAL_INDIRECT_COST
, RATE_APPLIED INDIRECT_COST_RATE
, BASE_AMOUNT BASE_COST
, DECODE(ORGDT.AGREEMENT_DATE
, NULL
, 'N'
, 'Y')
, ORGDT.AGREEMENT_DATE
, ORGDT.NEGOTIATING_AGREEMENT_WITH
, ORGDT.RATE_ESTABLISHED_WITH
, ORGDT.RATE_ESTABLISHED_DATE
, ORGDT.HUMAN_EXEMPTION_NUMBER
, ORGDT.HUMAN_ASSURANCE_NUMBER
, ORGDT.ANIMAL_ASSURANCE_NUMBER
, BUD.START_DATE
, BUD.END_DATE
, ROUND(MONTHS_BETWEEN(BUD.END_DATE
, BUD.START_DATE))
, 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
, ORGDT.ATTRIBUTE_CATEGORY
, ORGDT.ATTRIBUTE1
, ORGDT.ATTRIBUTE2
, ORGDT.ATTRIBUTE3
, ORGDT.ATTRIBUTE4
, ORGDT.ATTRIBUTE5
, ORGDT.ATTRIBUTE6
, ORGDT.ATTRIBUTE7
, ORGDT.ATTRIBUTE8
, ORGDT.ATTRIBUTE9
, ORGDT.ATTRIBUTE10
, ORGDT.ATTRIBUTE11
, ORGDT.ATTRIBUTE12
, ORGDT.ATTRIBUTE13
, ORGDT.ATTRIBUTE14
, ORGDT.ATTRIBUTE15
FROM IGW_PROPOSALS PROP
, IGW_SIGNING_OFFICIAL_V SO
, IGW_ADMIN_OFFICIAL_V AO
, IGW_APPLICANT_ORG_V APORG
, RA_CUSTOMERS RAC
, RA_ADDRESSES RAA
, ECE_TP_HEADERS ETPH
, ECE_TP_DETAILS ETPD
, IGW_ORG_DETAILS ORGDT
, IGW_BUDGETS BUD
, IGW_REPORT_BUDGET_BASE_RATE RBUD
, IGW_LOOKUPS_V LKUP
, IGW_PROP_APPROVAL_RUNS RUN
WHERE PROP.PROPOSAL_ID = SO.PROPOSAL_ID(+)
AND PROP.PROPOSAL_ID = AO.PROPOSAL_ID(+)
AND PROP.PROPOSAL_ID = APORG.PROPOSAL_ID(+)
AND PROP.SPONSOR_ID = RAC.CUSTOMER_ID
AND PROP.PROPOSAL_ID = BUD.PROPOSAL_ID(+)
AND PROP.SUBMITTING_ORGANIZATION_ID = ORGDT.ORGANIZATION_ID(+)
AND BUD.FINAL_VERSION_FLAG(+) = 'Y'
AND RAC.CUSTOMER_ID = RAA.CUSTOMER_ID
AND RAA.TP_HEADER_ID = ETPH.TP_HEADER_ID
AND ETPD.TP_HEADER_ID = ETPH.TP_HEADER_ID
AND ETPD.DOCUMENT_ID = 'PRPO'
AND ETPD.EDI_FLAG = 'Y'
AND PROP.PROPOSAL_ID = RBUD.PROPOSAL_ID(+)
AND RBUD.BUDGET_PERIOD_ID(+) = 0
AND LKUP.LOOKUP_TYPE = 'IGW_PROPOSAL_TYPES'
AND LKUP.LOOKUP_CODE = PROP.PROPOSAL_TYPE_CODE
AND PROP.PROPOSAL_ID = RUN.PROPOSAL_ID(+)
AND RUN.STATUS_CODE(+) = 'A'