R12 Vendors listing

最新推荐文章于 2021-04-04 12:02:07 发布

davidjiangqian

最新推荐文章于 2021-04-04 12:02:07 发布

阅读量486

点赞数

分类专栏： Oracle EBS

本文链接：https://blog.csdn.net/davidjq2009/article/details/46120373

版权

Oracle EBS 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

--R12Vendors listing

--In Detail

SELECT ou.name OU,

aps.VENDOR_NAME,

aps.vendor_name_alt,

aps.segment1 "VendorNumber",

aps.vendor_type_lookup_code,

aps.creation_date VendorCreation,

papf1.full_name "Vendor CreatedBy",

aps.end_date_active "Vendor EndDate",

aps.last_update_date "Vendor LastUpdated Date",

--aps.last_updated_by "VendorLast Updated By",

papf2.full_name "Vendor LastUpdated By",

(SELECT DECODE (

UPPER(aps.vendor_type_lookup_code),

'EMPLOYEE', (SELECTpapf.national_identifier

FROM apps.per_people_x papf

WHEREpapf.person_id = aps.employee_id),

DECODE(aps.organization_type_lookup_code,

'INDIVIDUAL',aps.individual_1099,

'FOREIGN INDIVIDUAL',aps.individual_1099,

hp.jgzz_fiscal_code))

FROM apps.hz_parties hp

WHERE hp.party_id = aps.party_id)

AS "Tax Payer ID",

apsa.VENDOR_SITE_CODE,

apsa.creation_date "Site CreationDate",

papf3.full_name "Site CreatedBy",

apsa.INACTIVE_DATE "Site InactiveDate",

apsa.last_update_date "Site LastUpdated Date",

papf4.full_name "Site LastUpdated By",

aps.VENDOR_NAME,

aps.vendor_name_alt,

aps.segment1 "VendorNumber",

apsa.VENDOR_SITE_CODE,

apsa.country,

apsa.ADDRESS_LINE1,

apsa.ADDRESS_LINE2,

apsa.ADDRESS_LINE3,

apsa.ADDRESS_LINE4,

apsa.CITY AS "City",

apsa.STATE AS "State",

apsa.PROVINCE AS "Province",

apsa.ZIP,

--aps.segment1,

--apsa.VENDOR_SITE_CODE,

apsa.address_lines_alt,

apsa.email_address,

apsa.INACTIVE_DATE AS "SiteInactive date",

apsa.PURCHASING_SITE_FLAG AS"Purchasing",

apsa.PAY_SITE_FLAG AS "Pay",

apsa.PRIMARY_PAY_SITE_FLAG AS"Site Primary",

apsa.INACTIVE_DATE AS "SiteInactive date",

apsa.ATTRIBUTE1 AS "2 WayOverride",

apsa.SUPPLIER_NOTIF_METHODnotifMethod,

apsa.email_address email,

DECODE (apsa.MATCH_OPTION,

'P', 'Purchase Order',

'R', 'Receipt',

'')

MatchApprvlevel,

(SELECT pmthds.Payment_Method_Name

FROM apps.IBY_EXT_PARTY_PMT_MTHDSPmtMthdAssignmentsEO,

apps.IBY_PAYMENT_METHODS_VLpmthds,

apps.IBY_EXTERNAL_PAYEES_ALLiepa

WHERE pmthds.payment_method_code =

PmtMthdAssignmentsEO.payment_method_code

ANDPmtMthdAssignmentsEO.Payment_flow LIKE 'DISBURSEMENT%'

ANDPmtMthdAssignmentsEO.EXT_PMT_PARTY_ID = iepa.EXT_PAYEE_ID

ANDPmtMthdAssignmentsEO.primary_flag = 'Y'

AND iepa.payee_party_id =aps.party_id

AND iepa.org_id IS NULL)

AS "Pay Method-SupplierLevel",

(SELECT pmthds.Payment_Method_Name

FROM apps.IBY_EXT_PARTY_PMT_MTHDSPmtMthdAssignmentsEO,

apps.IBY_PAYMENT_METHODS_VLpmthds,

apps.IBY_EXTERNAL_PAYEES_ALLiepa

WHERE pmthds.payment_method_code =

PmtMthdAssignmentsEO.payment_method_code

ANDPmtMthdAssignmentsEO.Payment_flow LIKE 'DISBURSEMENT%'

ANDPmtMthdAssignmentsEO.EXT_PMT_PARTY_ID = iepa.EXT_PAYEE_ID

ANDPmtMthdAssignmentsEO.primary_flag = 'Y'

AND iepa.payee_party_id = aps.party_id

AND iepa.supplier_site_id =apsa.vendor_site_id

AND iepa.org_id = apsa.org_id)

AS "Pay Method-SiteLevel",

NVL(bau.REMIT_ADVICE_DELIVERY_METHOD,

(select p.REMIT_ADVICE_DELIVERY_METHOD

Fromapps.IBY_EXTERNAL_PAYEES_ALL p

Where p.supplier_site_id= apsa.vendor_site_id)

) AS "Site Remit Advc Dlvry Method",

NVL(bau.REMIT_ADVICE_EMAIL ,

(select p. REMIT_ADVICE_EMAIL

From apps.IBY_EXTERNAL_PAYEES_ALLp

Where p.supplier_site_id= apsa.vendor_site_id))

AS "Site Remit Advcemail",

aps.INVOICE_CURRENCY_CODE AS"Suplier Level-Inv Curr",

aps.INVOICE_AMOUNT_LIMIT AS"Suplier Level-Inv_Amt_Limit",

DECODE (aps.MATCH_OPTION,

'P', 'Purchase Order',

'R', 'Receipt',

'')

AS "SuplierLevel-Inv_Match_Option",

aps.PAYMENT_CURRENCY_CODE AS"Supplier Level-Pay_Curr",

(SELECT name

FROM apps.ap_terms

WHERE term_id = aps.TERMS_ID)

AS "SupplierLevel-Terms",

aps.terms_date_basis AS "SupplierLevel-TermsDateBasis",

aps.PAY_DATE_BASIS_LOOKUP_CODE AS"Supplier Level-Pay date Basis",

aps.pay_group_lookup_code AS"Supplier Level-Pay Grp",

aps.ALWAYS_TAKE_DISC_FLAG AS"Supplier Level-AlwaysTakDisc",

aps.EXCLUDE_FREIGHT_FROM_DISCOUNT AS"Supplier Level-ExclFrgtFrDisc",

apsa.INVOICE_CURRENCY_CODE AS"Site Level-Inv Curr",

apsa.INVOICE_AMOUNT_LIMIT AS"Site Level-Inv_Amt_Limit",

DECODE (apsa.MATCH_OPTION,

'P', 'Purchase Order',

'R', 'Receipt',

'')

AS "SiteLevel-Inv_Match_Option",

apsa.PAYMENT_CURRENCY_CODE AS"Site Level-Pay_Curr",

(SELECT name

FROM apps.ap_terms

WHERE term_id = apsa.TERMS_ID)

AS "Site Level-Terms",

apsa.terms_date_basis AS "SiteLevel-TermsDateBasis",

apsa.PAY_DATE_BASIS_LOOKUP_CODE AS"Site Level-Pay date Basis",

apsa.pay_group_lookup_code AS"Site Level-Pay Grp",

apsa.ALWAYS_TAKE_DISC_FLAG AS"Site Level-AlwaysTakDisc",

apsa.EXCLUDE_FREIGHT_FROM_DISCOUNT AS"Site Level-ExclFrgtFrDisc",

(SELECT TOLERANCE_NAME

FROM AP_TOLERANCE_TEMPLATES att

WHERE att.tolerance_id =apsa.TOLERANCE_ID)

AS "Site Level-InvTolerance",

(SELECT TOLERANCE_NAME

FROM AP_TOLERANCE_TEMPLATES att

WHERE att.tolerance_id =apsa.SERVICES_TOLERANCE_ID)

AS "Site Level-ServiceTolerance",

apsa.HOLD_ALL_PAYMENTS_FLAG AS"Hold from Payment-All Invoices",

apsa.HOLD_UNMATCHED_INVOICES_FLAG AS"Hold from Payment-Unmatched",

apsa.HOLD_FUTURE_PAYMENTS_FLAG AS"Hold from Payment-Unvalidated",

apsa.HOLD_REASON AS "SiteLevel-Hold Reason",

aps.VENDOR_NAME||'|'||apsa.VENDOR_SITE_CODE "UNIQ",

bau.bank_name AS "BankName",

bau.BANK_NAME_ALT,

bau.BANK_BRANCH_NAME AS "BranchName",

bau.BANK_BRANCH_NAME_ALT,

bau.BRANCH_NUMBER AS "BranchNumber",

bau.eft_swift_code AS "BIC/SWIFTCode",

bau.bank_account_number AS "BankAccount Number",

bau.bank_account_name AS "BankAccount Name",

bau.alternate_account_name,

bau.BANK_ACCOUNT_TYPE,

bau.FOREIGN_PAYMENT_USE_FLAG AS"Allow Intl Pymts",

bau.MULTI_CURRENCY_ALLOWED_FLAG,

NVL (bau.a_start_date, bau.start_date)AS "Bnk Acct Start date",

NVL (bau.a_end_date, bau.end_date) AS"Bnk Acct End date",

aps.ALLOW_AWT_FLAG AS "SupplierLevel-AllowWTax",

apsa.ALLOW_AWT_FLAG AS "SupplierLevel-AllowWTax"

--apsa.HOLD_ALL_PAYMENTS_FLAG,apsa.HOLD_FUTURE_PAYMENTS_FLAG, apsa.HOLD_UNMATCHED_INVOICES_FLAG

FROM apps.ap_suppliers aps,

apps.ap_supplier_sites_all apsa,

apps.hr_operating_units ou,

fnd_user fu1, fnd_user fu2,

per_all_people_f papf1,per_all_people_f papf2,

fnd_user fu3, fnd_user fu4,

per_all_people_f papf3,per_all_people_f papf4,

(SELECT DISTINCT

CEBRANCH.BANK_NAME,

CEBRANCH.BANK_NAME_ALT,

CEBRANCH.BANK_NUMBER,

CEBRANCH.BANK_BRANCH_NAME,

CEBRANCH.BANK_BRANCH_NAME_ALT,

CEBRANCH.BRANCH_NUMBER,

CEBRANCH.EFT_SWIFT_CODE,

BANKACCT.bank_account_name,

BANKACCT.alternate_account_name,

BANKACCT.bank_account_number,

BANKACCT.currency_code,

BANKACCT.country_code,

INSTR_ASSIGN.START_DATE,

INSTR_ASSIGN.END_DATE,

DECODE(BANKACCT.PRIMARY_ACCT_OWNER_PARTY_ID,

Payee.payee_party_id,'Y',

'N')

AS "PrimaryFlag",

payee.REMIT_ADVICE_DELIVERY_METHOD,

payee.REMIT_ADVICE_EMAIL,

payee.EXT_PAYEE_ID,

payee.party_site_id,

payee.payee_party_id,

PAYEE.SUPPLIER_SITE_ID,

BANKACCT.start_datea_start_date,

BANKACCT.end_datea_end_date,BANKACCT.FOREIGN_PAYMENT_USE_FLAG,

BANKACCT.MULTI_CURRENCY_ALLOWED_FLAG,

BANKACCT.BANK_ACCOUNT_TYPE

FROM apps.IBY_PMT_INSTR_USES_ALLINSTR_ASSIGN,

apps.IBY_EXTERNAL_PAYEES_ALLPAYEE,

apps.IBY_EXT_BANK_ACCOUNTS_VBANKACCT,

apps.CE_BANK_BRANCHES_VCEBRANCH

WHERE INSTR_ASSIGN.INSTRUMENT_ID =BANKACCT.EXT_BANK_ACCOUNT_ID

ANDINSTR_ASSIGN.EXT_PMT_PARTY_ID = PAYEE.EXT_PAYEE_ID

ANDINSTR_ASSIGN.INSTRUMENT_TYPE = 'BANKACCOUNT'

AND INSTR_ASSIGN.PAYMENT_FLOW= 'DISBURSEMENTS'

AND BANKACCT.branch_party_id =cebranch.branch_party_id(+)) bau

WHERE aps.vendor_id = apsa.vendor_id

AND ou.organization_id = apsa.org_id

AND ou.name ='KR OU' ---in ('AUOU','NZ OU','PG OU','FJ OU')-- 'AU OU'

--AND aps.end_date_active IS NULL

AND aps.vendor_type_lookup_code<> 'INTERCOMPANY'

AND aps.vendor_type_lookup_code<> 'EMPLOYEE'

AND aps.created_by = fu1.user_id

AND fu1.employee_id =papf1.person_id(+)

AND aps.last_updated_by = fu2.user_id

AND fu2.employee_id =papf2.person_id(+)

AND apsa.created_by = fu3.user_id

AND fu3.employee_id =papf3.person_id(+)

AND apsa.last_updated_by = fu4.user_id

AND fu4.employee_id =papf4.person_id(+)

AND bau.a_end_date IS NULL--bau.a_end_date,bau.end_date

--AND (apsa.last_update_date betweenTO_DATE('2015-02-16 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') andTO_DATE('2015-02-17 00:00:00' , 'YYYY-MM-DD HH24:MI:SS'))

--AND aps.VENDOR_NAME like 'FEDEXCORPORATION%'

--AND vendor_name_alt in ('DQJ TEST ADI VENDOR6','DQJ TEST ADI VENDOR7')

--AND aps.last_updated_by

--AND aps.segment1 in(5016159,5016220)

--AND apsa.ATTRIBUTE1 IS NULL

--AND bau.bank_account_number like'060287%'

AND apsa.vendor_site_id =bau.SUPPLIER_SITE_ID(+)

--AND papf4.full_name = 'Jiang, DavidQ'

--AND apsa.last_update_date betweenTO_DATE('2015-05-24 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') andTO_DATE('2015-05-25 00:00:00' , 'YYYY-MM-DD HH24:MI:SS')

--AND NVL (apsa.INACTIVE_DATE, SYSDATE+ 1) > SYSDATE

ORDER BY 1, 2, 3;