SELECT pv.party_id,
pv.vendor_id,pvs.vendor_site_id,
pvs.party_site_id,
pvs.org_id,
hou.name operating_unit,
pv.vendor_name,
pv.segment1 vendor_code,
pv.creation_date,
pv.end_date_active vendor_inactive_date,
pvs.inactive_date vendor_site_inactive_date,
pvs.vendor_site_code,
( Regexp_replace(pvs.address_line1, '[^-/&,;# ,.[:alnum:]]', '')
|| Regexp_replace(pvs.address_line2, '[^-/&,;# ,.[:alnum:]]', '')
|| Regexp_replace(pvs.address_line3, '[^-/&,;# ,.[:alnum:]]', '')
|| Regexp_replace(pvs.address_line4, '[^-/&,;# ,.[:alnum:]]', '') )
Vendor_address,
pvs.city,
pvs.zip,
(SELECT ftt.territory_short_name
FROM apps.fnd_territories_tl ftt
WHERE pvs.country = ftt.territory_code
AND ROWNUM = 1) country_name,
(SELECT hl_ship.location_code
FROM apps.hr_locations hl_ship
WHERE PVS.ship_to_location_id = hl_ship.location_id
AND ROWNUM = 1) ship_loc,
(SELECT hl_bill.location_code
FROM apps.hr_locations hl_bill
WHERE PVS.bill_to_location_id = hl_bill.location_id
AND ROWNUM = 1) bill_loc,
(SELECT apt.name
FROM apps.ap_terms apt
WHERE pvs.terms_id = apt.term_id
AND ROWNUM = 1) term_name,
(SELECT gcc_lia.concatenated_segments
FROM apps.gl_code_combinations_kfv gcc_lia
WHERE pvs.accts_pay_code_combination_id = gcc_lia.code_combination_id
AND ROWNUM = 1) liab_account,
(SELECT gcc_pre.concatenated_segments
FROM apps.gl_code_combinations_kfv gcc_pre
WHERE pvs.prepay_code_combination_id = gcc_pre.code_combination_id
AND ROWNUM = 1) prepay_account,
(SELECT gcc_fut.concatenated_segments
FROM apps.gl_code_combinations_kfv gcc_fut
WHERE pvs.future_dated_payment_ccid = gcc_fut.code_combination_id
AND ROWNUM = 1) future_account,
(SELECT ieppm.payment_method_code
FROM apps.iby_external_payees_all iepa,
apps.iby_ext_party_pmt_mthds ieppm
WHERE iepa.ext_payee_id = ieppm.ext_pmt_party_id
AND ieppm.primary_flag = 'Y'
AND iepa.supplier_site_id = pvs.vendor_site_id
AND ( ( ieppm.inactive_date IS NULL )
OR ( ieppm.inactive_date > SYSDATE ) )
AND iepa.party_site_id = pvs.party_site_id
AND iePA.payee_party_id = pv.party_id
AND ROWNUM = 1) payment_method,
pvs.pay_on_code,
(SELECT person.person_first_name
FROM apps.ap_supplier_contacts apsc,
apps.hz_parties person,
apps.hz_parties pty_rel
WHERE apsc.per_party_id = person.party_id
AND apsc.rel_party_id = pty_rel.party_id
AND apsc.org_party_site_id = pvs.party_site_id
AND ROWNUM = 1) person_first_name,
(SELECT person.person_last_name
FROM apps.ap_supplier_contacts apsc,
apps.hz_parties person,
apps.hz_parties pty_rel
WHERE apps.apsc.per_party_id = person.party_id
AND apsc.rel_party_id = pty_rel.party_id
AND apsc.org_party_site_id = pvs.party_site_id
AND ROWNUM = 1) person_last_name,
(SELECT pty_rel.primary_phone_number
FROM apps.ap_supplier_contacts apsc,
apps.hz_parties person,
apps.hz_parties pty_rel
WHERE apsc.per_party_id = person.party_id
AND apsc.rel_party_id = pty_rel.party_id
AND apsc.org_party_site_id = pvs.party_site_id
AND ROWNUM = 1) primary_phone_number,
(SELECT pty_rel.email_address
FROM apps.ap_supplier_contacts apsc,
apps.hz_parties person,
apps.hz_parties pty_rel
WHERE apsc.per_party_id = person.party_id
AND apsc.rel_party_id = pty_rel.party_id
AND apsc.org_party_site_id = pvs.party_site_id
AND ROWNUM = 1) email_address,
/*(select LISTAGG(regexp_replace(pvc.prefix||' '||pvc.first_name||' '||pvc.middle_name||' '||pvc.last_name,'[^- ,.[:alnum:]]', ''), ', ') WITHIN GROUP (ORDER BY pv.vendor_id)
OVER (PARTITION BY pv.vendor_id,pvs.vendor_site_id,pvs.org_id)
from ap_supplier_contacts pvc
where pvs.vendor_site_id = pvc.vendor_site_id
and rownum=1)contact_person,
(select LISTAGG(regexp_replace(pvc.phone,'[^-/()+&,;# ,.[:alnum:]]', ''), ', ') WITHIN GROUP (ORDER BY pv.vendor_id)
OVER (PARTITION BY pv.vendor_id,pvs.vendor_site_id,pvs.org_id)
from ap_supplier_contacts pvc
where pvs.vendor_site_id = pvc.vendor_site_id
and rownum=1)phone_number,
(select LISTAGG(regexp_replace(pvc.email_address,'[^-@_ ,.[:alnum:]]', ''), ', ') WITHIN GROUP (ORDER BY pv.vendor_id)
OVER (PARTITION BY pv.vendor_id,pvs.vendor_site_id)
from ap_supplier_contacts pvc
where pvs.vendor_site_id = pvc.vendor_site_id
and rownum=1)email,*/
(SELECT Regexp_replace(To_char (jpvs.vat_reg_no), '[^- ,.[:alnum:]]', '')
FROM apps.jai_cmn_vendor_sites jpvs
WHERE pvs.vendor_site_id = jpvs.vendor_site_id
AND ROWNUM = 1) tin_number,
(SELECT To_char (jpvs.service_tax_regno)
FROM apps.jai_cmn_vendor_sites jpvs
WHERE pvs.vendor_site_id = jpvs.vendor_site_id
AND ROWNUM = 1) service_tax_regno,
(SELECT jpvs.ec_code
FROM apps.jai_cmn_vendor_sites jpvs
WHERE pvs.vendor_site_id = jpvs.vendor_site_id
AND ROWNUM = 1) ecc_code,
(SELECT Regexp_replace(jpvs.cst_reg_no, '[^- ,.[:alnum:]]', '')
FROM apps.jai_cmn_vendor_sites jpvs
WHERE pvs.vendor_site_id = jpvs.vendor_site_id
AND ROWNUM = 1) cst_reg_no,
(SELECT jvt.tds_vendor_type_lookup_code
FROM apps.jai_ap_tds_vendor_hdrs jvt
WHERE pvs.vendor_site_id = jvt.vendor_site_id
AND ROWNUM = 1) tds_vendor_type_code,
pv.vendor_type_lookup_code,
(SELECT flv.meaning
FROM apps.fnd_lookup_values flv
WHERE flv.lookup_code( + ) = pv.vendor_type_lookup_code
AND flv.lookup_type( + ) = 'VENDOR TYPE'
AND flv.LANGUAGE( + ) = Userenv('lang')
AND ROWNUM = 1) vendor_type,
(SELECT jvt.tan_no
FROM apps.jai_ap_tds_vendor_hdrs jvt
WHERE pvs.vendor_site_id = jvt.vendor_site_id
AND ROWNUM = 1) tan_no,
CASE
WHEN pvs.create_debit_memo_flag = 'Y' THEN 'Active'
ELSE NULL
END create_debit_memo_flag,
CASE
WHEN pv.end_date_active IS NULL THEN 'Active'
ELSE 'Inactive'
END Vendor_Status,
CASE
WHEN pvs.inactive_date IS NULL THEN 'Active'
ELSE 'Inactive'
END Site_Status,
(SELECT ieba.bank_account_num
FROM apps.iby_ext_bank_accounts ieba,
apps.iby_external_payees_all iepa,
apps.iby_pmt_instr_uses_all ipiua
WHERE ipiua.instrument_id = ieba.ext_bank_account_id
AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND iePA.payee_party_id = pv.party_id
AND iePA.supplier_site_id = pvs.vendor_site_id
AND iePA.party_site_id = pvs.party_site_id
AND ipiua.end_date IS NULL
AND ROWNUM = 1) bank_account_num,
(SELECT ieba.bank_account_name
FROM apps.iby_ext_bank_accounts ieba,
apps.iby_external_payees_all iepa,
apps.iby_pmt_instr_uses_all ipiua
WHERE ipiua.instrument_id = ieba.ext_bank_account_id
AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND iePA.payee_party_id = pv.party_id
AND iePA.supplier_site_id = pvs.vendor_site_id
AND iePA.party_site_id = pvs.party_site_id
AND ipiua.end_date IS NULL
AND ROWNUM = 1) bank_account_name,
(SELECT ieba.attribute1
FROM apps.iby_ext_bank_accounts ieba,
apps.iby_external_payees_all iepa,
apps.iby_pmt_instr_uses_all ipiua
WHERE ipiua.instrument_id = ieba.ext_bank_account_id
AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND iePA.payee_party_id = pv.party_id
AND iePA.supplier_site_id = pvs.vendor_site_id
AND iePA.party_site_id = pvs.party_site_id
AND ipiua.end_date IS NULL
AND ROWNUM = 1) IFSC_CODE,
(SELECT ipiua.attribute1
FROM apps.iby_ext_bank_accounts ieba,
apps.iby_external_payees_all iepa,
apps.iby_pmt_instr_uses_all ipiua
WHERE ipiua.instrument_id = ieba.ext_bank_account_id
AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND iePA.payee_party_id = pv.party_id
AND iePA.supplier_site_id = pvs.vendor_site_id
AND iePA.party_site_id = pvs.party_site_id
AND ipiua.end_date IS NULL
AND ROWNUM = 1) attribute1,
(SELECT jrav.reporting_code
FROM apps.jai_party_regs jprv,
apps.jai_party_reg_lines jprlv,
apps.jai_reporting_associations jrav
WHERE jprlv.party_reg_id = jprv.party_reg_id
AND jprv.party_reg_id = jrav.entity_id
AND jprlv.regime_id = jrav.regime_id
AND jprlv.regime_id = 10001--- 'IND GST'
AND jrav.reporting_type_id = 10034---'JAI_TP_IN_STATE'
AND Trunc (SYSDATE) BETWEEN jprlv.effective_from AND Trunc (
Nvl (jprlv.effective_to, SYSDATE))
AND Trunc (SYSDATE) BETWEEN jrav.effective_from AND Trunc (
Nvl (jrav.effective_to, SYSDATE))
AND jprv.party_id = pv.vendor_id
AND jprv.party_site_id = pvs.vendor_site_id
AND jprv.org_id = pvs.org_id
AND ROWNUM = 1) state_code,
(SELECT jrav.reporting_code_description
FROM apps.jai_party_regs jprv,
apps.jai_party_reg_lines jprlv,
apps.jai_reporting_associations jrav
WHERE jprlv.party_reg_id = jprv.party_reg_id
AND jprv.party_reg_id = jrav.entity_id
AND jprlv.regime_id = jrav.regime_id
AND jprlv.regime_id = 10001--- 'IND GST'
AND jrav.reporting_type_id = 10034---'JAI_TP_IN_STATE'
AND Trunc (SYSDATE) BETWEEN jprlv.effective_from AND Trunc (
Nvl (jprlv.effective_to, SYSDATE))
AND Trunc (SYSDATE) BETWEEN jrav.effective_from AND Trunc (
Nvl (jrav.effective_to, SYSDATE))
AND jprv.party_id = pv.vendor_id
AND jprv.party_site_id = pvs.vendor_site_id
AND jprv.org_id = pvs.org_id
AND ROWNUM = 1) state_name,
(SELECT jprlv.registration_number
FROM apps.jai_party_regs jprv,
apps.jai_party_reg_lines jprlv,
apps.jai_reporting_associations jrav
WHERE jprlv.party_reg_id = jprv.party_reg_id
AND jprv.party_reg_id = jrav.entity_id
AND jprlv.regime_id = jrav.regime_id
AND jprlv.regime_id = 10001--- 'IND GST'
AND jrav.reporting_type_id = 10034---'JAI_TP_IN_STATE'
AND Trunc (SYSDATE) BETWEEN jprlv.effective_from AND Trunc (
Nvl (jprlv.effective_to, SYSDATE))
AND Trunc (SYSDATE) BETWEEN jrav.effective_from AND Trunc (
Nvl (jrav.effective_to, SYSDATE))
AND jprv.party_id = pv.vendor_id
AND jprv.party_site_id = pvs.vendor_site_id
AND jprv.org_id = pvs.org_id
AND ROWNUM = 1) gst_register_number,
(SELECT jprlv.secondary_registration_number
FROM apps.jai_party_regs jprv,
apps.jai_party_reg_lines jprlv,
apps.jai_reporting_associations jrav
WHERE jprlv.party_reg_id = jprv.party_reg_id
AND jprv.party_reg_id = jrav.entity_id
AND jprlv.regime_id = jrav.regime_id
AND jprlv.regime_id = 10001--- 'IND GST'
AND jrav.reporting_type_id = 10034---'JAI_TP_IN_STATE'
AND Trunc (SYSDATE) BETWEEN jprlv.effective_from AND Trunc (
Nvl (jprlv.effective_to, SYSDATE))
AND Trunc (SYSDATE) BETWEEN jrav.effective_from AND Trunc (
Nvl (jrav.effective_to, SYSDATE))
AND jprv.party_id = pv.vendor_id
AND jprv.party_site_id = pvs.vendor_site_id
AND jprv.org_id = pvs.org_id
AND ROWNUM = 1) PAN_NO,
(SELECT jprlv.registration_type_code
FROM apps.jai_party_regs jprv,
apps.jai_party_reg_lines jprlv,
apps.jai_reporting_associations jrav
WHERE jprlv.party_reg_id = jprv.party_reg_id
AND jprv.party_reg_id = jrav.entity_id
AND jprlv.regime_id = jrav.regime_id
AND jprlv.regime_id = 10001--- 'IND GST'
AND jrav.reporting_type_id = 10034---'JAI_TP_IN_STATE'
AND Trunc (SYSDATE) BETWEEN jprlv.effective_from AND Trunc (
Nvl (jprlv.effective_to, SYSDATE))
AND Trunc (SYSDATE) BETWEEN jrav.effective_from AND Trunc (
Nvl (jrav.effective_to, SYSDATE))
AND jprv.party_id = pv.vendor_id
AND jprv.party_site_id = pvs.vendor_site_id
AND jprv.org_id = pvs.org_id
AND ROWNUM = 1) REGISTRATION_TYPE_CODE,
(SELECT sec_registration_type_code
FROM apps.jai_party_regs jprv,
apps.jai_party_reg_lines jprlv,
apps.jai_reporting_associations jrav
WHERE jprlv.party_reg_id = jprv.party_reg_id
AND jprv.party_reg_id = jrav.entity_id
AND jprlv.regime_id = jrav.regime_id
AND jprlv.regime_id = 10001--- 'IND GST'
AND jrav.reporting_type_id = 10034---'JAI_TP_IN_STATE'
AND Trunc (SYSDATE) BETWEEN jprlv.effective_from AND Trunc (
Nvl (jprlv.effective_to, SYSDATE))
AND Trunc (SYSDATE) BETWEEN jrav.effective_from AND Trunc (
Nvl (jrav.effective_to, SYSDATE))
AND jprv.party_id = pv.vendor_id
AND jprv.party_site_id = pvs.vendor_site_id
AND jprv.org_id = pvs.org_id
AND ROWNUM = 1) SEC_REGISTRATION_TYPE_CODE,
(SELECT lookup_code
FROM apps.pos_bus_class_reqs a,
apps.pos_supplier_mappings b
WHERE a.mapping_id = b.mapping_id
AND B.vendor_id = pv.vendor_id
AND ROWNUM = 1) MSME_FLAG
FROM apps.ap_suppliers pv,
apps.ap_supplier_sites_all pvs,
apps.hr_operating_units hou
WHERE pv.vendor_id = pvs.vendor_id
AND pvs.org_id = hou.organization_id;
No comments:
Post a Comment