Oracle EBS ERP供应商信息查询视图

CREATE OR REPLACE VIEW XXX_STD_SUPPLIERINFO_V AS

SELECT avs.org_id org_id,

       haou.name ou_name,

       aps.vendor_name sup_name,

       aps.segment1 sup_num,

       avs.vendor_site_code VendorSite_Code,

       aps.vendor_id Vendor_id,

       aps.party_id VParty_ID,

       avs.vendor_site_id vendor_SiteID,

       avs.party_site_id VParty_SiteID,

       nvl(aps.vendor_type_lookup_code, 'StanDard') VendarLookUp_Code,

       iee.default_payment_method_code DPMTs_Method_Code,

       ipp.payment_method_code PMTs_Method_Code,

       nvl(aps.employee_id, -9999) Emploii_ID,

       aps.enabled_flag Enable_Flag,

       aps.allow_awt_flag AWT_Header_Flag,

       nvl(aps.awt_group_id,-9999) AWT_Header_groupID,

       avs.allow_awt_flag AWT_Site_Flag,

       nvl(avs.awt_group_id,-9999) awt_Site_groupID,

       atms.name tms_name,

       gccl.concatenated_segments gccl_account,

       gccp.concatenated_segments gccp_account,

       avs.invoice_currency_code inv_Code,

       avs.payment_currency_code PMT_Code

  FROM ap_suppliers                aps,

       ap_supplier_sites_all       avs,

       hr_all_organization_units   haou,

       hr_organization_information hoi,

       ap_terms_tl                 atms,

       gl_code_combinations_kfv    gccl,

       gl_code_combinations_kfv    gccp,

       hz_parties                  hp,

       iby_external_payees_all iee,

       iby_ext_party_pmt_mthds ipp

 WHERE aps.vendor_id = avs.vendor_id

   AND haou.organization_id = avs.org_id

   AND haou.organization_id = avs.org_id

   AND haou.organization_id = hoi.organization_id

   and haou.organization_id=iee.ORG_ID

   and iee.ORG_TYPE='OPERATING_UNIT'

   and iee.payee_party_id=aps.party_id

   and iee.supplier_site_id=avs.vendor_site_id

   and iee.ext_payee_id=ipp.ext_pmt_party_id

   AND hoi.org_information1 = 'OPERATING_UNIT'

   AND hp.party_id = aps.party_id

   AND avs.terms_id = atms.term_id(+)

   AND atms.language(+) = USERENV('LANG')

   AND gccl.code_combination_id = avs.accts_pay_code_combination_id

   AND gccp.code_combination_id = avs.prepay_code_combination_id

   ----AND nvl(aps.vendor_type_lookup_code, 9999) <> 'EMPLOYEE'

   AND nvl(aps.end_date_active, trunc(SYSDATE)) >= trunc(SYSDATE)

   AND nvl(avs.inactive_date, trunc(SYSDATE)) >= trunc(SYSDATE)

 ORDER BY haou.name;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31515388/viewspace-2637280/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31515388/viewspace-2637280/