--R12 Vendors listing – done by Gou-Chunyang!
--In Detail
SELECT--SUBSTR(HOU.ATTRIBUTE1, 3, 2),
--HOU.ATTRIBUTE1 组织代码,
HOU.NAME 组织名称,
SUP.SEGMENT1 供应商编号,
PLC.DISPLAYED_FIELD "供应商类型",
TRIM(SUP.VENDOR_NAME) 供应商名称,
SUP.VAT_REGISTRATION_NUM "纳税登记编号",
SUP.NUM_1099 "纳税人标识",
SUP.END_DATE_ACTIVE "供应商失效日期",
FTV.TERRITORY_SHORT_NAME "国家",
SITE.ADDRESS_LINE2 "地址行2",
NVL(SITE.PROVINCE, SITE.STATE) "省份",
SITE.CITY "城市",
SITE.ZIP "邮编",
SITE.ADDRESS_LINE1 "地址行1",
PS.PARTY_SITE_NAME 地址名称,
DECODE(SITE.ATTRIBUTE11, 'N', '否', 'Y', '是', '否') "是否关联交易方",
SITE.VENDOR_SITE_CODE 地点名称,
SITE.INACTIVE_DATE 地点失效日期,
GCC1.SEGMENT1 || '.' || GCC1.SEGMENT2 || '.'|| GCC1.SEGMENT3 || '.' ||
GCC1.SEGMENT4 || '.' || GCC1.SEGMENT5 || '.'|| GCC1.SEGMENT6 || '.' ||
GCC1.SEGMENT7 || '.' || GCC1.SEGMENT8 || '.'|| GCC1.SEGMENT9负债帐户,
GCC2.SEGMENT1 || '.' || GCC2.SEGMENT2 || '.'|| GCC2.SEGMENT3 || '.' ||
GCC2.SEGMENT4 || '.' || GCC2.SEGMENT5 || '.'|| GCC2.SEGMENT6 || '.' ||
GCC2.SEGMENT7 || '.' || GCC2.SEGMENT8 || '.'|| GCC2.SEGMENT9 "预付款帐户",
'' 银行所属省,
'' 银行所属城市,
BANK.BANK_NAME 银行名称,
BANK.BANKBRANCH 分行名称,
BANK.BANK_ACCOUNT_NAME 供应商银行帐户名称,
BANK.ACCOUNT_SUFFIX "银行中间码(账户后缀)",
BANK.ORDER_OF_PREFERENCE 账户优先级,
BANK.BANK_ACCOUNT_NUM 供应商银行帐号,
BANK.START_DATE 银行帐户开户日期,
BANK.END_DATE 银行帐户失效日期,
(SELECT PMTHDS.PAYMENT_METHOD_NAME
FROM IBY_PAYMENT_METHODS_VL PMTHDS,
IBY_EXT_PARTY_PMT_MTHDSPMTMTHDASSIGNMENTSEO
WHERE PMTHDS.PAYMENT_METHOD_CODE =
PMTMTHDASSIGNMENTSEO.PAYMENT_METHOD_CODE(+)
AND PMTMTHDASSIGNMENTSEO.PAYMENT_FLOW ='DISBURSEMENTS'
AND PMTMTHDASSIGNMENTSEO.EXT_PMT_PARTY_ID=
(SELECT IEPA.EXT_PAYEE_ID
FROM IBY.IBY_EXTERNAL_PAYEES_ALLIEPA
WHERE IEPA.SUPPLIER_SITE_ID =SITE.VENDOR_SITE_ID)
ANDPMTMTHDASSIGNMENTSEO.PAYMENT_METHOD_CODE =
(SELECTIEPA.DEFAULT_PAYMENT_METHOD_CODE
FROM IBY.IBY_EXTERNAL_PAYEES_ALLIEPA
WHERE IEPA.SUPPLIER_SITE_ID =SITE.VENDOR_SITE_ID)) "付款方法",
SITE.VENDOR_SITE_ID,
SITE.INVOICE_CURRENCY_CODE "发票币种",
SITE.PAYMENT_CURRENCY_CODE "付款币种",
SITE.PAY_GROUP_LOOKUP_CODE "支付组",
(SELECT ATT.NAME
FROM APPS.AP_TERMS_TL ATT
WHERE ATT.LANGUAGE = 'ZHS'
AND ATT.TERM_ID = SITE.TERMS_ID) "付款条件",
SITE.PAYMENT_PRIORITY "付款优先级",
DECODE(SITE.MATCH_OPTION, 'P', '采购订单', 'R', '接收', SITE.MATCH_OPTION) "发票匹配选项",
DECODE(SITE.PAY_ON_CODE,
'USE',
'使用',
'RECEIPT',
'接收',
'RECEIPT_AND_USE',
'接收与使用',
SITE.PAY_ON_CODE) "付款方式",
DECODE(SITE.PAY_ON_RECEIPT_SUMMARY_CODE,
'PAY_SITE',
'付款地点',
'CONSUMPTION_ADVICE',
'冲减通知',
SITE.PAY_ON_RECEIPT_SUMMARY_CODE)"发票汇总层"
FROM AP_SUPPLIERS SUP,
HZ_PARTIES HP,
HZ_PARTY_SITES PS,
HZ_LOCATIONS HL,
AP_SUPPLIER_SITES_ALL SITE,
HR_ORGANIZATION_UNITS HOU,
(SELECT P.ORG_ID,
P.SUPPLIER_SITE_ID,
BANK.BANK_NAME,
BANK.BANK_INSTITUTION_TYPE ASBANK_ACCOUNT_TYPE,
BANK.BANK_BRANCH_NAME ASBANKBRANCH,
BANK.BANK_BRANCH_NAME ASBANK_BRANCH,
ACCT.EXT_BANK_ACCOUNT_ID ASBANK_ACCOUNT_ID,
ACCT.BANK_ACCOUNT_NUM,
ACCT.AGENCY_LOCATION_CODE,
ACCT.ATTRIBUTE13,
ACCT.ACCOUNT_SUFFIX,
ACCT.BANK_ACCOUNT_NAME,
U.ORDER_OF_PREFERENCE,
U.START_DATE,
U.END_DATE,
CASE
WHEN SYSDATE BETWEENNVL(ACCT.START_DATE, SYSDATE) AND
NVL(ACCT.END_DATE,SYSDATE) THEN
'Y'
ELSE
'N'
END ACCT_VALID_FLAG,
CASE
WHEN SYSDATE BETWEENBANK.START_DATE AND
NVL(BANK.END_DATE,SYSDATE) THEN
'Y'
ELSE
'N'
END BRANCHE_VALID_FLAG,
CASE
WHEN SYSDATE BETWEENB.START_DATE AND
NVL(B.END_DATE, SYSDATE)THEN
'Y'
ELSE
'N'
END B_VALID_FLAG,
CASE
WHEN SYSDATE BETWEENU.START_DATE AND
NVL(U.END_DATE, SYSDATE)THEN
'Y'
ELSE
'N'
END U_VALID_FLAG,
GREATEST(ACCT.LAST_UPDATE_DATE,
P.LAST_UPDATE_DATE,
U.LAST_UPDATE_DATE) ASLAST_UPDATE_DATE
FROM APPS.IBY_PMT_INSTR_USES_ALL U,
APPS.IBY_EXTERNAL_PAYEES_ALL P,
APPS.IBY_EXT_BANK_ACCOUNTS ACCT,
APPS.CE_BANK_BRANCHES_V BANK,
APPS.CE_BANKS_V B
WHERE P.PAYMENT_FUNCTION ='PAYABLES_DISB'
AND P.ORG_TYPE = 'OPERATING_UNIT'
AND U.PAYMENT_FLOW = 'DISBURSEMENTS'
AND U.EXT_PMT_PARTY_ID =P.EXT_PAYEE_ID
AND U.INSTRUMENT_TYPE ='BANKACCOUNT'
AND U.INSTRUMENT_ID =ACCT.EXT_BANK_ACCOUNT_ID
AND ACCT.BRANCH_ID =BANK.BRANCH_PARTY_ID
AND ACCT.BANK_ID = B.BANK_PARTY_ID)BANK,
GL_CODE_COMBINATIONS GCC1,
GL_CODE_COMBINATIONS GCC2,
PO_LOOKUP_CODES PLC,
FND_TERRITORIES_VL FTV
WHERE SUP.PARTY_ID = HP.PARTY_ID
AND SITE.VENDOR_ID = SUP.VENDOR_ID
AND SITE.LOCATION_ID = HL.LOCATION_ID(+)
AND SITE.PARTY_SITE_ID = PS.PARTY_SITE_ID(+)
AND SITE.ORG_ID = HOU.ORGANIZATION_ID
AND SITE.ACCTS_PAY_CODE_COMBINATION_ID =GCC1.CODE_COMBINATION_ID(+)
AND SITE.VENDOR_SITE_ID =BANK.SUPPLIER_SITE_ID(+)
AND SITE.ORG_ID = BANK.ORG_ID(+)
AND PLC.LOOKUP_CODE(+) =SUP.VENDOR_TYPE_LOOKUP_CODE
--AND PLC.LOOKUP_TYPE(+) = 'VENDOR TYPE'
AND SITE.COUNTRY = FTV.TERRITORY_CODE(+)
--AND SUP.VENDOR_TYPE_LOOKUP_CODE ='COMPANY'
AND NVL(SUP.END_DATE_ACTIVE, SYSDATE) >=SYSDATE
AND SUP.VENDOR_NAME like 'ベンダーテスト京都55%'
AND HOU.NAME LIKE 'JP%'
AND SITE.PREPAY_CODE_COMBINATION_ID =GCC2.CODE_COMBINATION_ID(+);