SELECT hp.party_name supplier,
ps.party_id supplier_ID,
Initcap(ps.vendor_type_lookup_Code) supplier_type,
DECODE(hp.status,'A','Active','Inactive' ) status,
TO_CHAR(ps.end_date_active,'dd-mm-yyyy') Inactivation_Reason,
TO_CHAR(ps.creation_date,'dd-mm-yyyy') creation_date,
ps.created_by,
ps.segment1 supplier_number,
zxptp.rep_registration_number Tax_Registration_Num,
psp.income_tax_id Tax_Payer_ID,
zxptp.tax_classification_code Tax_Code,
NULL Related_Party,
ps.business_relationship Relationship_with,
NULL Relationship_Type,
ps.organization_type_lookup_code Tax_Organization_Type,
NULL Classification,
NULL Status2,
NULL Certifying_Agency,
NULL Certificate,
TO_CHAR(psam.effective_start_date,'dd-mm-yyyy') start_date,
TO_CHAR(psam.effective_end_date,'dd-mm-yyyy') expiration_date,
NULL Attachment_Title,
NULL Attachment,
TO_CHAR(psam.effective_end_date,'dd-mm-yyyy') Expiration_Date2,
hp.ATTRIBUTE2 DFF,
TO_CHAR(psam.effective_start_date,'dd-mm-yyyy') Start_Date2,
NULL Attachment2,
hop.DUNS_NUMBER_C License_CR_Number,
----Supplier--------
hps.party_site_name Address_Name,
hp.city ,
hp.county,
Fax,
Phone,
TO_CHAR(ps.end_date_active,'dd-mm-yyyy') Inactive_Date,
--ps.customer_num,
--ps.standard_industry_class sic,
--hop.party_number Registry_id,
--hop.year_established,
--hop.mission_statement,
---Supplier Site---
pssm.vendor_site_code site,
hps.party_site_name site_Address_Name,
(hp.address1||','|| hp.address2||','|| hp.city||','||hp.state||','||hp.county) Site_Addresses,
decode(hp.status,'A','Active','Inactive' ) site_status,
TO_CHAR(hps.end_date_active,'dd-mm-yyyy') Site_Inactive_Date,
DECODE(pssm.hold_flag,'N','No','Yes') Hold,
pssm.purchasing_hold_reason Hold_Reason,
(SELECT Segment1 ||'.'||Segment2 ||'.'||Segment3||'.'||Segment4||'.'||Segment5||'.'||Segment6||'.'||Segment7||'.'||Segment8||'.'||Segment9||'.'||Segment10 Pre_Dist
FROM gl_code_combinations
WHERE code_combination_id = psam.prepay_code_combination_id) PrepaymentDistribution,
(SELECT Segment1 ||'.'||Segment2 ||'.'||Segment3||'.'||Segment4||'.'||Segment5||'.'||Segment6||'.'||Segment7||'.'||Segment8||'.'||Segment9||'.'||Segment10 Liab_dist
FROM gl_code_combinations
WHERE code_combination_id = psam.accts_pay_code_combination_id ) Liability_Distribution,
NULL WithholdingTaxGroup,
---Supplier Site Contact---
hp_contact.person_first_name first_name,
hp_contact.person_last_name last_name,
hp_contact.primary_phone_number contact_phone,
hp_contact.email_address contact_email,
NULL Contact_Inactive_Date,
(hp_contact.address1||','||hp_contact.address2||hp_contact.city ||','|| hp_contact.state||','|| hp_contact.county) Contact_Addresses
FROM poz_suppliers ps,
hz_parties hp ,
hz_organization_profiles hop,
poz_suppliers_pii psp,
hz_party_sites hps,
hz_parties hp_contact,
fusion.zx_party_tax_profile zxptp,
poz_supplier_sites_all_m pssm,
poz_site_assignments_all_m psam
WHERE hp.party_id = ps.party_id
AND hop.party_id = ps.party_id
AND psp.vendor_id(+) = ps.vendor_id
AND hps.party_site_id(+) = hp.iden_addr_party_site_id
AND hp_contact.party_id(+) = hp.preferred_contact_person_id
AND zxptp.party_id(+) = ps.party_id
AND ps.vendor_id = pssm.vendor_id
AND pssm.vendor_site_id = psam.vendor_site_id
AND hp.party_name = '&Supplier_Name' -- Comment out for all Supplier