R12 Vendors listing – compiled by Gou-Chunyang!

--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(+);