SELECT
(SELECT Decode(attrval,'OTHER','Other than Small','SMALL','Small',NULL)
from po_eda_uda_attr_values WHERE attr = 'SUPPLIER_DTLS_SUPLR_SIZE' and pk1_value=:documentId AND PK2_VALUE = :draftId and lvl = 'AWARDHEADER') v_org_type,
(CASE
WHEN InStr(attrval,'Federal Agency') <> 0 THEN 'Federal Agency'
WHEN InStr(attrval,'Federally Funded Research and Development Corp') <> 0 THEN
'Federally Funded Research and Development Corporation'
ELSE NULL
END ) USFederal,
Decode(InStr(attrval,'State Government'),0,NULL,'true') USState,
(CASE
WHEN InStr(attrval,'City') <> 0 THEN 'City'
WHEN InStr(attrval,'County') <> 0 THEN 'County'
WHEN InStr(attrval,'Inter- municipal') <> 0 THEN 'Inter-Municipal'
WHEN InStr(attrval,'Local Government Owned') <> 0 THEN 'Local Government Owned'
WHEN InStr(attrval,'Municipality') <> 0 THEN 'Municipality'
WHEN InStr(attrval,'School District') <> 0 THEN 'School District'
WHEN InStr(attrval,'Township') <> 0 THEN 'Township'
ELSE NULL
END ) USLocal,
Decode(InStr(attrval,'Tribal Government'),0,NULL,'true') Tribal,
Decode(InStr(attrval,'Foreign Owned and Located'),0,NULL,'true') Foreign,
decode(InStr(attrval,'Airport Authority'),0,NULL,'Airport Authority') GovernmentOrganization1,
decode(InStr(attrval,'Council of Governments'),0,NULL,'Council of Government') GovernmentOrganization2,
decode(InStr(attrval,'Housing Authorities Public/Tribal'),0,NULL,'Housing Authorities Public/Tribal') GovernmentOrganization3,
decode(InStr(attrval,'Interstate Entity'),0,NULL,'Interstate Entity') GovernmentOrganization4,
decode(InStr(attrval,'Planning Commission'),0,NULL,'Planning Commission') GovernmentOrganization5,
decode(InStr(attrval,'Port Authority'),0,NULL,'Port Authority') GovernmentOrganization6,
decode(InStr(attrval,'Transit Authority'),0,NULL,'Transit Authority') GovernmentOrganization7,
Decode(InStr(attrval,'Foreign Owned and Located'),0,NULL,'true') ForeignOwnedAndLocated,
NVL((select eda from po_eda_mapping where GROUPING = 'Address' AND attribute = 'IRSDesignation' AND ebs = (
SELECT fv.ORGANIZATIONAL_TYPE FROM fv_ccr_vendors fv,ap_supplier_sites_all ap WHERE fv.vendor_id = ap.vendor_id
and fv.duns = substr(ap.duns_number, 1, 9) and nvl(fv.plus_four, '@@') = nvl( substr(ap.duns_number, 10, 4), '@@')
and fv.VENDOR_ID = (SELECT vendor_id FROM po_headers_merge_v WHERE po_header_id = :documentId AND draft_id = :draftId) and ap.vendor_site_id = (SELECT vendor_site_id FROM po_headers_merge_v WHERE po_header_id = :documentId AND draft_id = :draftId)
) ),(SELECT fv.ORGANIZATIONAL_TYPE FROM fv_ccr_vendors fv,ap_supplier_sites_all ap WHERE fv.vendor_id = ap.vendor_id
and fv.duns = substr(ap.duns_number, 1, 9) and nvl(fv.plus_four, '@@') = nvl( substr(ap.duns_number, 10, 4), '@@')
and fv.VENDOR_ID = (SELECT vendor_id FROM po_headers_merge_v WHERE po_header_id = :documentId AND draft_id = :draftId) and ap.vendor_site_id = (SELECT vendor_site_id FROM po_headers_merge_v WHERE po_header_id = :documentId AND draft_id = :draftId)
)) IRSDesignation,
(CASE
WHEN InStr(attrval,'Asian Pacific American Owned') <> 0 THEN 'Asian Pacific American Owned'
WHEN InStr(attrval,'Black American Owned') <> 0 THEN 'Black American Owned'
WHEN InStr(attrval,'Hispanic American Owned') <> 0 THEN 'Hispanic American Owned'
WHEN InStr(attrval,'Native American Owned') <> 0 THEN 'Native American Owned'
WHEN InStr(attrval,'Subcontinent Asian (Asian- Indian) American Owned') <> 0 THEN 'Subcontinent Asian (Asian Indian) American Owned'
WHEN InStr(attrval,'Minority Owned Business') <> 0 THEN 'Other'
ELSE NULL
END ) MinorityOwned,
(CASE
WHEN InStr(attrval,'For- Profit Organization') <> 0 THEN 'For-Profit Organization'
WHEN InStr(attrval,'Nonprofit Organization') <> 0 THEN 'Non-Profit Organization'
WHEN InStr(attrval,'Other Not for Profit Organization') <> 0 THEN 'Other - Not for Profit Organization'
ELSE NULL
END ) ProfitStructure,
Decode(InStr(attrval,'Small Agricultural Cooperative'),0,NULL,'true') SmallAgriculturalCooperative,
decode(InStr(attrval,'Alaskin Native Corporation Owned Firm'),0,NULL,'Alaskan Native Corporation Owned Firm') FedRecNativeAmericanEntity1,
decode(InStr(attrval,'American Indian Owned'),0,NULL,'American Indian Owned') FedRecNativeAmericanEntity2,
decode(InStr(attrval,'Indian Tribe (Federally Recognized'),0,NULL,'Federally Recognized Indian Tribe') FedRecNativeAmericanEntity3,
decode(InStr(attrval,'Native Hawaiian Organization Owned Firm'),0,NULL,'Native Hawaiian Organization Owned Firm') FedRecNativeAmericanEntity4,
decode(InStr(attrval,'Tribally Owned Firm'),0,NULL,'Tribally Owned Firm') FedRecNativeAmericanEntity5,
decode(InStr(attrval,'Community Developed Corporation Owned Firm'),0,NULL,'Community Development Corporation Owned Firm') SocioEconomicGroup1,
decode(InStr(attrval,'Labor Surplus Area Firm'),0,NULL,'Labor Surplus Area Firm') SocioEconomicGroup2,
decode(InStr(attrval,'Woman Owned Business'),0,NULL,'Women Owned Business') SocioEconomicGroup3,
decode(InStr(attrval,'SBA Certified Small Disadvantaged Business'),0,NULL,'SBA Certified Small Disadvantaged Business') SocioEconomicGroup4,
decode(InStr(attrval,'SBA Certified 8(a) Program Participant'),0,NULL,'SBA Certified 8(a) Program Participant') SocioEconomicGroup5,
decode(InStr(attrval,'SBA Certified Hub Zone Firm'),0,NULL,'SBA Certified Hubzone Contractor') SocioEconomicGroup6,
decode(InStr(attrval,'Self Certified Small Disadvantaged Business'),0,NULL,'Self Certified Small Disadvantaged Business') SocioEconomicGroup7,
Decode(InStr(attrval,'Service Disabled Veteran Owned'),0,NULL,'true') ServiceDisabledVeteranOwned,
(CASE
WHEN InStr(attrval,'Service Disabled Veteran Owned Business') = 0 THEN
CASE
WHEN InStr(attrval,'Veteran Owned Business') <> 0 THEN 'true'
ELSE NULL
END
ELSE NULL
END ) NonServiceDisabledVeteranOwned,
decode(InStr(attrval,'Community Development Corporation'),0,NULL,'Community Development Corporation') Miscellaneous1,
decode(InStr(attrval,'Domestic Shelter'),0,NULL,'Domestic Shelter') Miscellaneous2,
decode(InStr(attrval,'Foundation'),0,NULL,'Foundation') Miscellaneous3,
decode(InStr(attrval,'Veterinary Hospital'),0,NULL,'Veterinary Hospital') Miscellaneous4,
decode(InStr(attrval,'Hospital'),0,NULL,'Hospital') Miscellaneous5,
decode(InStr(attrval,'1862 Land Grant College'),0,NULL,'1862 Land Grant College') EducationalInstitution1,
decode(InStr(attrval,'1890 Land Grant College'),0,NULL,'1890 Land Grant College') EducationalInstitution2,
decode(InStr(attrval,'1994 Land Grant College'),0,NULL,'1994 Land Grant College') EducationalInstitution3,
decode(InStr(attrval,'Alaskan Native Servicing Institution (ANSI)'),0,NULL,'Alaskan Native Servicing Institution') EducationalInstitution4,
decode(InStr(attrval,'Hispanic Servicing Institution'),0,NULL,'Hispanic Servicing Institution') EducationalInstitution5,
decode(InStr(attrval,'Historically Black College or University'),0,NULL,'Historically Black College or University (HBCU)') EducationalInstitution6,
decode(InStr(attrval,'Minority Institutions'),0,NULL,'Minority Institutions') EducationalInstitution7,
decode(InStr(attrval,'Native Hawaiian Servicing Institution (NHSI)'),0,NULL,'Native Hawaiian Servicing Institution') EducationalInstitution8,
decode(InStr(attrval,'Educational Institution'),0,NULL,'Other Educational Institution') EducationalInstitution9,
decode(InStr(attrval,'Private University or College'),0,NULL,'Private University or College') EducationalInstitution10,
decode(InStr(attrval,'School of Forestry'),0,NULL,'School of Forestry') EducationalInstitution11,
decode(InStr(attrval,'State Controlled Institution of Higher Learning'),0,NULL,'State Controlled Institution of Higher Learning') EducationalInstitution12,
decode(InStr(attrval,'Tribal College'),0,NULL,'Tribal College') EducationalInstitution13,
decode(InStr(attrval,'Veterinary College'),0,NULL,'Veterinary College') EducationalInstitution14
from po_eda_uda_attr_values WHERE attr = 'SUPPLIER_DTLS_SOCIO_EC_INFO' and pk1_value=:documentId AND PK2_VALUE = :draftId and lvl = 'AWARDHEADER' |
