PO Generate XML for EDA

Description
Categories: BI Publisher
Columns: V Org Type, Usfederal, Usstate, Uslocal, Tribal, Foreign, Governmentorganization1, Governmentorganization2, Governmentorganization3, Governmentorganization4 ...
Application: Purchasing
Source:
Short Name: POEDAXML
DB package: PO_EDA_DATATEMPLATE_PKG
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