VIEW: APPS.IGW_PRPO_HEADERS_V

Source

View Text - Preformatted

SELECT 'EDI', ETPD.test_flag, 'PRPO', ETPD.document_type, 'PRPO', ETPD.translator_code, ACCT_SITE.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, substrb(p.party_name,1,50) customer_name, LOC.address1 , LOC.address2 , LOC.city , LOC.state , LOC.postal_code, LOC.country , LOC.county , PROP.funding_sponsor_unit, p.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, hz_parties p, hz_cust_accounts c, HZ_PARTY_SITES PARTY_SITE, HZ_LOC_ASSIGNMENTS LOC_ASSIGN, HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE, 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 c.party_id = p.party_id and c.cust_account_id = PROP.sponsor_id and PROP.proposal_id = BUD.proposal_id(+) and PROP.submitting_organization_id = ORGDT.organization_id(+) and BUD.final_version_flag(+) = 'Y' and ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID and LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID and LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID and NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99) and c.cust_account_id = ACCT_SITE.cust_account_id and ACCT_SITE.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'

View Text - HTML Formatted

SELECT 'EDI'
, ETPD.TEST_FLAG
, 'PRPO'
, ETPD.DOCUMENT_TYPE
, 'PRPO'
, ETPD.TRANSLATOR_CODE
, ACCT_SITE.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
, SUBSTRB(P.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.CITY
, LOC.STATE
, LOC.POSTAL_CODE
, LOC.COUNTRY
, LOC.COUNTY
, PROP.FUNDING_SPONSOR_UNIT
, P.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
, HZ_PARTIES P
, HZ_CUST_ACCOUNTS C
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOC_ASSIGNMENTS LOC_ASSIGN
, HZ_LOCATIONS LOC
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, 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 C.PARTY_ID = P.PARTY_ID
AND C.CUST_ACCOUNT_ID = PROP.SPONSOR_ID
AND PROP.PROPOSAL_ID = BUD.PROPOSAL_ID(+)
AND PROP.SUBMITTING_ORGANIZATION_ID = ORGDT.ORGANIZATION_ID(+)
AND BUD.FINAL_VERSION_FLAG(+) = 'Y'
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
, -99) = NVL(LOC_ASSIGN.ORG_ID
, -99)
AND C.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
AND ACCT_SITE.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'