OKC Small Business Coordination Record Data Template

➜ Back to Library
Description
Categories: BI Publisher
Application: Contracts Core
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
Download
Related Blitz Reports
PO IDV Modification PO IDV Data template PO Modification Data template OKC CDRL Data Template PON Solicitation data template ECC Procurement, Purchase Orders PO IDV Mod datatemplate for FPDS-NG ECC Procurement, Agreements ECC Sourcing, Negotiations, SQL1
Blitz Report™

Blitz Report™ provides multiple benefits: