OKC Small Business Coordination Record Data Template

Description
Categories: BI Publisher
Columns: Contract Number, Contract Id, Contract Type, Contractver, Contract Name, Contract Desc, Contract Status Code, Org Id, Amount, Sbcr Coordination Type ...
Application: Contracts Core
Source:
Short Name: OKCCLMSBCRDT
DB package: OKC_SBCR_DT_PKG
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