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 |
