ebs 供应商地点信息_EBS标准的查看供应商地点

SELECTpvsa.address_style,

hzl.language,

pvsa.province,

pvsa.country,

pvsa.area_code,

pvsa.phone,

pvsa.customer_num,

pvsa.ship_to_location_id,

pvsa.bill_to_location_id,

pvsa.ship_via_lookup_code,

pvsa.freight_terms_lookup_code,

pvsa.fob_lookup_code,

pvsa.inactive_date,

pvsa.fax,

pvsa.fax_area_code,

pvsa.telex,

pvsa.terms_date_basis,

pvsa.distribution_set_id,

pvsa.accts_pay_code_combination_id,

pvsa.prepay_code_combination_id,

pvsa.pay_group_lookup_code,

pvsa.payment_priority,

pvsa.terms_id,

pvsa.invoice_amount_limit,

pvsa.pay_date_basis_lookup_code,

pvsa.always_take_disc_flag,

pvsa.invoice_currency_code,

pvsa.payment_currency_code,

pvsa.county,

pvsa.vendor_site_id,

pvsa.last_update_date,

pvsa.last_updated_by,

pvsa.vendor_id,

pvsa.vendor_site_code,

pvsa.vendor_site_code_alt,

pvsa.last_update_login,

pvsa.creation_date,

pvsa.created_by,

pvsa.purchasing_site_flag,

pvsa.rfq_only_site_flag,

pvsa.pay_site_flag,

pvsa.attention_ar_flag,

pvsa.address_line1,

pvsa.address_line2,

pvsa.address_line3,

pvsa.address_lines_alt,

pvsa.city,

pvsa.state,

pvsa.zip,

pvsa.hold_all_payments_flag,

pvsa.hold_future_payments_flag,

pvsa.hold_reason,

pvsa.hold_unmatched_invoices_flag,

pvsa.tax_reporting_site_flag,

pvsa.attribute_category,

pvsa.attribute1,

pvsa.attribute2,

pvsa.attribute3,

pvsa.attribute4,

pvsa.attribute5,

pvsa.attribute6,

pvsa.attribute7,

pvsa.attribute8,

pvsa.attribute9,

pvsa.attribute10,

pvsa.attribute11,

pvsa.attribute12,

pvsa.attribute13,

pvsa.attribute14,

pvsa.attribute15,

pvsa.request_id,

pvsa.program_application_id,

pvsa.program_id,

pvsa.program_update_date,

pvsa.validation_number,

pvsa.exclude_freight_from_discount,

pvsa.bank_charge_bearer,

pvsa.org_id,

pvsa.check_digits,

pvsa.address_line4,

pvsa.allow_awt_flag,

pvsa.awt_group_id,

pvsa.pay_awt_group_id,

pvsa.default_pay_site_id,

pvsa.pay_on_code,

pvsa.pay_on_receipt_summary_code,

pvsa.global_attribute_category,

pvsa.global_attribute1,

pvsa.global_attribute2,

pvsa.global_attribute3,

pvsa.global_attribute4,

pvsa.global_attribute5,

pvsa.global_attribute6,

pvsa.global_attribute7,

pvsa.global_attribute8,

pvsa.global_attribute9,

pvsa.global_attribute10,

pvsa.global_attribute11,

pvsa.global_attribute12,

pvsa.global_attribute13,

pvsa.global_attribute14,

pvsa.global_attribute15,

pvsa.global_attribute16,

pvsa.global_attribute17,

pvsa.global_attribute18,

pvsa.global_attribute19,

pvsa.global_attribute20,

pvsa.tp_header_id,

pvsa.edi_id_number,

pvsa.ece_tp_location_code,

pvsa.pcard_site_flag,

pvsa.match_option,

pvsa.country_of_origin_code,

pvsa.future_dated_payment_ccid,

pvsa.create_debit_memo_flag,

pvsa.supplier_notif_method,

pvsa.email_address,

nvl(pvsa.primary_pay_site_flag,‘N‘) ASprimary_pay_site_flag,

pvsa.shipping_control,

pvsa.selling_company_identifier,

pvsa.gapless_inv_num_flag,

pvsa.duns_number,

pvsa.location_id,

pvsa.party_site_id,

pvsa.tolerance_id,

pvsa.services_tolerance_id,NULL AS payment_method_lookup_code --obsoleted donot use

,

hrou.nameASorg_name,

pay_site.vendor_site_codeASdefault_pay_site_name,

cor.territory_short_nameAScountry_of_origin_name,

aag.nameASawt_group_name,

pay_aag.nameASpay_awt_group_name,

pos_vendor_util_pkg.get_le_name_by_liability_acct(pvsa.accts_pay_code_combination_id,

pvsa.org_id)ASentity_name,

pos_vendor_util_pkg.get_le_id_by_liability_acct(pvsa.accts_pay_code_combination_id,

pvsa.org_id)ASentity_id,

pvsa.retainage_rate,

ads.distribution_set_name,

poas.location_idASship_net_loc_id,

ap_param.allow_awt_flagASpayable_tax_flag,

pos_util_pkg.is_supp_ccr(1.0,‘‘,

pvsa.vendor_id)ASis_supp_ccr_str,

pos_util_pkg.is_site_ccr(1.0,‘‘,

pvsa.vendor_site_id)ASis_site_ccr_str,

pos_util_pkg.is_fv_enabled()ASis_fv_enabled_str,

decode(pos_util_pkg.is_site_ccr(1.0,‘‘,

pvsa.vendor_site_id),‘T‘,‘ViewCcrActive‘,‘ViewCcrInactive‘) ASviewccrsupplier,

decode(pos_util_pkg.is_site_ccr(1.0,‘‘,

pvsa.vendor_site_id),‘T‘,‘Y‘,‘N‘) ASsite_ccr_chk,‘hldUnvalInv‘ AShold_unval_invoices,‘HoldPay1‘ AShold_all_invoices,‘holdUnmtchInv1‘ AShold_unmatch_inv,

pos_util_pkg.is_ccr_site_active(1.0,‘‘,

pvsa.vendor_site_id) is_ccr_site_active_str,

ap_param.create_awt_dists_typeASawt_option_type,

hrl.location_codeASship_to_address,

decode(ap_vendor_pub_pkg.is_vendor_site_merged(pvsa.vendor_site_id),‘Y‘,‘EditDateDisabled‘,‘EditDateEnabled‘) ASis_date_edit_enabled,

decode(pv.vendor_type_lookup_code,‘EMPLOYEE‘,

decode(pvsa.vendor_site_code,‘HOME‘,

fnd_message.get_string(‘POS‘,‘POS_HT_SP_HOME‘),‘OFFICE‘,

fnd_message.get_string(‘POS‘,‘POS_HT_SP_OFFICE‘),‘PROVISIONAL‘,

fnd_message.get_string(‘POS‘,‘POS_HT_SP_PROVISIONAL‘),

pvsa.vendor_site_code),

pvsa.vendor_site_code) sitename,

terms.nameASterms_desc,

fnduser1.user_namelast_updated_user_name,

fnduser2.user_namecreated_user_nameFROMap_supplier_sites_all pvsa,

hr_operating_units hrou,

ap_supplier_sites_all pay_site,

fnd_territories_vl cor,

ap_awt_groups aag,

ap_awt_groups pay_aag,

hz_locations hzl,

ap_distribution_sets ads,

po_location_associations poas,

ap_system_parameters_all ap_param,

hr_locations hrl,

ap_terms_tl terms,

fnd_user fnduser1,

fnd_user fnduser2,

ap_suppliers pvWHERE pvsa.vendor_id =pv.Vendor_IdAND pv.vendor_id =pvsa.vendor_idAND poas.vendor_id(+) =pvsa.vendor_idAND poas.vendor_site_id(+) =pvsa.vendor_site_idAND hzl.location_id(+) =pvsa.location_idAND hrl.location_id(+) =pvsa.ship_to_location_idAND hrou.organization_id =pvsa.org_idAND pvsa.default_pay_site_id = pay_site.vendor_site_id(+)AND pvsa.country_of_origin_code = cor.territory_code(+)--在后台查询供应商地点时可将其先注释

--AND mo_global.check_access(pvsa.org_id) = ‘Y‘

AND pvsa.awt_group_id = aag.group_id(+)AND pvsa.pay_awt_group_id = pay_aag.group_id(+)AND pvsa.distribution_set_id = ads.distribution_set_id(+)AND pvsa.org_id =ap_param.org_idAND pvsa.terms_id = terms.term_id(+)AND terms.language(+) = userenv(‘LANG‘)AND terms.enabled_flag(+) = ‘Y‘

AND fnduser1.user_id =pvsa.last_updated_byAND fnduser2.user_id =pvsa.created_byAND PV.VENDOR_NAME=‘中粮屯河股份有限公司‘