OKC Small Business Coordination Record Data Template
Description
Categories: BI Publisher
Application: Contracts Core
Source:
Short Name: OKCCLMSBCRDT
DB package: OKC_SBCR_DT_PKG
Source:
Short Name: OKCCLMSBCRDT
DB package: OKC_SBCR_DT_PKG
Run
OKC Small Business Coordination Record Data Template and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT orc.contract_number, orc.contract_id , orc.contract_type, orc.contract_version_num contractVer, orc.contract_name, orc.contract_desc, orc.contract_status_code, orc.org_id, orc.amount, orc.sbcr_coordination_type, (SELECT DECODE(orcu.business_document_type, 'SOLICITATION', SubStr((SELECT pah.document_number FROM PON_AUCTION_HEADERS_ALL pah WHERE pah.auction_header_id = orcu.business_document_id),1,13), 'PO_STANDARD_MOD', SubStr((SELECT pd.modification_number FROM po_drafts pd WHERE pd.draft_id = orcu.business_document_id),1,13),NULL) FROM okc_rep_contract_usages orcu WHERE orcu.contract_id = orc.contract_id AND orcu.contract_type = orc.contract_type AND orcu.contract_version = orc.contract_version_num) sol_number, (SELECT DECODE(orcu.business_document_type, 'PA_BLANKET_MOD', SubStr((SELECT pd.modification_number FROM po_drafts pd WHERE pd.draft_id = orcu.business_document_id),1,13), 'PA_CONTRACT_MOD', SubStr((SELECT pd.modification_number FROM po_drafts pd WHERE pd.draft_id = orcu.business_document_id),1,13), NULL) FROM okc_rep_contract_usages orcu WHERE orcu.contract_id = orc.contract_id AND orcu.contract_type = orc.contract_type AND orcu.contract_version = orc.contract_version_num) bpa_number, (SELECT DECODE(orcu.business_document_type, 'SOLICITATION', SubStr((SELECT pah.document_number FROM PON_AUCTION_HEADERS_ALL pah WHERE pah.auction_header_id = orcu.business_document_id),15), 'PO_STANDARD_MOD', SubStr((SELECT pd.modification_number FROM po_drafts pd WHERE pd.draft_id = orcu.business_document_id),15), 'PA_BLANKET_MOD', SubStr((SELECT pd.modification_number FROM po_drafts pd WHERE pd.draft_id = orcu.business_document_id),15), 'PA_CONTRACT_MOD', SubStr((SELECT pd.modification_number FROM po_drafts pd WHERE pd.draft_id = orcu.business_document_id),15), NULL) FROM okc_rep_contract_usages orcu WHERE orcu.contract_id = orc.contract_id AND orcu.contract_type = orc.contract_type AND orcu.contract_version = orc.contract_version_num) mod_number, (SELECT DECODE(orcu.business_document_type, 'SOLICITATION', SubStr((SELECT pah.document_number FROM PON_AUCTION_HEADERS_ALL pah WHERE pah.auction_header_id = orcu.business_document_id),15), 'PO_STANDARD_MOD', SubStr((SELECT pd.modification_number FROM po_drafts pd WHERE pd.draft_id = orcu.business_document_id),15), 'PA_BLANKET_MOD', SubStr((SELECT pd.modification_number FROM po_drafts pd WHERE pd.draft_id = orcu.business_document_id),15), 'PA_CONTRACT_MOD', SubStr((SELECT pd.modification_number FROM po_drafts pd WHERE pd.draft_id = orcu.business_document_id),15), NULL) FROM okc_rep_contract_usages orcu WHERE orcu.contract_id = orc.contract_id AND orcu.contract_type = orc.contract_type AND orcu.contract_version = orc.contract_version_num) mod_number, OKC_SBCR_DT_PKG.get_uda_single_attr_xml(orc.contract_id).getClobVal() uda_attributes, OKC_SBCR_DT_PKG.get_setaside_recommendations(orc.contract_id).getClobVal() setaside_recommendations, OKC_SBCR_DT_PKG.get_prev_acqs_history(orc.contract_id).getClobVal() prev_acquisitions, OKC_SBCR_DT_PKG.get_sbss_employees_size(orc.contract_id) sbss_employees, OKC_SBCR_DT_PKG.get_sbss_dollars_size(orc.contract_id) sbss_dollars, OKC_SBCR_DT_PKG.is_sec8A_setaside_recommended(orc.contract_id) is_sec8A_setaside_recommended, OKC_SBCR_DT_PKG.is_hzone_setaside_recommended(orc.contract_id) is_hzone_setaside_recommended, OKC_SBCR_DT_PKG.is_sdvosb_setaside_recommended(orc.contract_id) is_sdvosb_setaside_recommended, OKC_SBCR_DT_PKG.is_mulawrd_setaside_recomended(orc.contract_id) is_mulawrd_setaside_recomended, OKC_SBCR_DT_PKG.is_sec8A_prevacq_recommended(orc.contract_id) is_sec8A_prevacq_recommended, OKC_SBCR_DT_PKG.is_hzone_prevacq_recommended(orc.contract_id) is_hzone_prevacq_recommended, OKC_SBCR_DT_PKG.is_sdvosb_prevacq_recommended(orc.contract_id) is_sdvosb_prevacq_recommended, OKC_SBCR_DT_PKG.is_mulawrd_prevacq_recomended(orc.contract_id) is_mulawrd_prevacq_recomended FROM okc_rep_contracts_all orc WHERE orc.contract_id = :contractId |