VIEW: APPS.CE_BU_PAY_EXTERNAL_ACCTS_V

Source

View Text - Preformatted

SELECT pay.external_account_id, decode(pay.territory_code, /* Account Name */ 'AU', pay.segment3, 'BE', pay.segment4, 'CA', pay.segment1, 'FR', pay.segment6, 'GB', pay.segment5, 'HK', pay.segment4, 'IT', pay.segment4, 'NZ', pay.segment2, 'SG', pay.segment2, 'US', pay.segment1, null), decode(pay.territory_code, /* Account Number */ 'AU', pay.segment2, 'BE', pay.segment3, 'CA', pay.segment3, 'FR', pay.segment5, 'GB', pay.segment4, 'HK', pay.segment3, 'IT', pay.segment3, 'NZ', pay.segment2, 'SG', pay.segment1, 'US', pay.segment3, null), decode(pay.territory_code, /* Bank Name */ 'AU', pay.segment1, 'BE', pay.segment1, 'CA', pay.segment5, 'FR', pay.segment1, 'GB', (select meaning from hr_lookups where lookup_code = pay.segment1 and lookup_type = 'GB_BANKS' and application_id between 800 and 899), 'HK', pay.segment1, 'IT', pay.segment1, 'NZ', pay.segment1, 'SG', pay.segment5, 'US', pay.segment5, null), decode(pay.territory_code, /* Branch Name */ 'AU', pay.segment1, 'BE', pay.segment1, 'CA', pay.segment6, 'FR', pay.segment4, 'GB', pay.segment2, 'HK', pay.segment2, 'IT', pay.segment1, 'NZ', pay.segment1, 'SG', pay.segment7, 'US', pay.segment6, null), decode(pay.territory_code, /* Location (city) */ 'BE', pay.segment2, 'GB', pay.segment8, 'IT', pay.segment2, null), decode(pay.territory_code, /* Currency Code */ 'BE', pay.segment5, 'IT', pay.segment5, null), decode(pay.territory_code, /* Bank Number */ 'AU', pay.segment1, 'CA', pay.segment7, 'FR', pay.segment2, 'HK', pay.segment1, 'SG', pay.segment4, null), decode(pay.territory_code, /* Branch Number */ 'AU', pay.segment1, 'CA', pay.segment4, 'FR', pay.segment3, 'GB', pay.segment3, 'HK', pay.segment2, 'NZ', pay.segment5, 'SG', pay.segment6, 'US', pay.segment4, null), decode(pay.territory_code, /* Bank Account Type */ 'CA', pay.segment2, 'FR', pay.segment9, 'GB', pay.segment6, 'SG', pay.segment3, 'US', pay.segment2, null), decode(pay.territory_code, /* Secondary account reference */ 'GB', pay.segment7, null), decode(pay.territory_code, /* Communication agreement id */ 'IT', pay.segment6, null), decode(pay.territory_code, /* Account Suffix */ 'NZ', pay.segment3, null), decode(pay.territory_code, /* Description */ 'NZ', pay.segment4, null), null, /* Description code1 */ null, /* Description code2 */ decode(pay.territory_code, /* EFT requester identifier */ 'FR', pay.segment8, null), null, /* Bank name kana */ pay.start_date_active, /* Bank start date */ pay.end_date_active, /* Bank end date */ null, /* Branch name kana */ pay.start_date_active, /* Branch start date */ pay.end_date_active, /* Branch end date */ pay.start_date_active, /* Account start date */ pay.end_date_active, /* Account end date */ fnd.territory_short_name, pay.territory_code, pay.creation_date, pay.created_by, pay.last_update_date, pay.last_updated_by, pay.last_update_login from fnd_territories_vl fnd, pay_external_accounts pay where pay.territory_code = fnd.territory_code and pay.territory_code <> 'JP' union all select pay.external_account_id, pay.segment9, pay.segment8, jpb.bank_name, jpbb.branch_name, null, null, pay.segment1, pay.segment4, pay.segment7, null, null, null, null, pay.segment10, pay.segment11, null, jpb.bank_name_kana, jpb.start_date_active, jpb.end_date_active, jpbb.branch_name_kana, jpbb.start_date_active, jpbb.end_date_active, pay.start_date_active, pay.end_date_active, fnd.territory_short_name, pay.territory_code, pay.creation_date, pay.created_by, pay.last_update_date, pay.last_updated_by, pay.last_update_login from pay_jp_bank_branches jpbb, pay_jp_banks jpb, fnd_territories_vl fnd, pay_external_accounts pay where pay.territory_code = fnd.territory_code and pay.territory_code = 'JP' and jpb.bank_code = pay.segment1 and jpbb.bank_code = pay.segment1 and jpbb.branch_code = pay.segment4

View Text - HTML Formatted

SELECT PAY.EXTERNAL_ACCOUNT_ID
, DECODE(PAY.TERRITORY_CODE
, /* ACCOUNT NAME */ 'AU'
, PAY.SEGMENT3
, 'BE'
, PAY.SEGMENT4
, 'CA'
, PAY.SEGMENT1
, 'FR'
, PAY.SEGMENT6
, 'GB'
, PAY.SEGMENT5
, 'HK'
, PAY.SEGMENT4
, 'IT'
, PAY.SEGMENT4
, 'NZ'
, PAY.SEGMENT2
, 'SG'
, PAY.SEGMENT2
, 'US'
, PAY.SEGMENT1
, NULL)
, DECODE(PAY.TERRITORY_CODE
, /* ACCOUNT NUMBER */ 'AU'
, PAY.SEGMENT2
, 'BE'
, PAY.SEGMENT3
, 'CA'
, PAY.SEGMENT3
, 'FR'
, PAY.SEGMENT5
, 'GB'
, PAY.SEGMENT4
, 'HK'
, PAY.SEGMENT3
, 'IT'
, PAY.SEGMENT3
, 'NZ'
, PAY.SEGMENT2
, 'SG'
, PAY.SEGMENT1
, 'US'
, PAY.SEGMENT3
, NULL)
, DECODE(PAY.TERRITORY_CODE
, /* BANK NAME */ 'AU'
, PAY.SEGMENT1
, 'BE'
, PAY.SEGMENT1
, 'CA'
, PAY.SEGMENT5
, 'FR'
, PAY.SEGMENT1
, 'GB'
, (SELECT MEANING
FROM HR_LOOKUPS
WHERE LOOKUP_CODE = PAY.SEGMENT1
AND LOOKUP_TYPE = 'GB_BANKS'
AND APPLICATION_ID BETWEEN 800
AND 899)
, 'HK'
, PAY.SEGMENT1
, 'IT'
, PAY.SEGMENT1
, 'NZ'
, PAY.SEGMENT1
, 'SG'
, PAY.SEGMENT5
, 'US'
, PAY.SEGMENT5
, NULL)
, DECODE(PAY.TERRITORY_CODE
, /* BRANCH NAME */ 'AU'
, PAY.SEGMENT1
, 'BE'
, PAY.SEGMENT1
, 'CA'
, PAY.SEGMENT6
, 'FR'
, PAY.SEGMENT4
, 'GB'
, PAY.SEGMENT2
, 'HK'
, PAY.SEGMENT2
, 'IT'
, PAY.SEGMENT1
, 'NZ'
, PAY.SEGMENT1
, 'SG'
, PAY.SEGMENT7
, 'US'
, PAY.SEGMENT6
, NULL)
, DECODE(PAY.TERRITORY_CODE
, /* LOCATION (CITY) */ 'BE'
, PAY.SEGMENT2
, 'GB'
, PAY.SEGMENT8
, 'IT'
, PAY.SEGMENT2
, NULL)
, DECODE(PAY.TERRITORY_CODE
, /* CURRENCY CODE */ 'BE'
, PAY.SEGMENT5
, 'IT'
, PAY.SEGMENT5
, NULL)
, DECODE(PAY.TERRITORY_CODE
, /* BANK NUMBER */ 'AU'
, PAY.SEGMENT1
, 'CA'
, PAY.SEGMENT7
, 'FR'
, PAY.SEGMENT2
, 'HK'
, PAY.SEGMENT1
, 'SG'
, PAY.SEGMENT4
, NULL)
, DECODE(PAY.TERRITORY_CODE
, /* BRANCH NUMBER */ 'AU'
, PAY.SEGMENT1
, 'CA'
, PAY.SEGMENT4
, 'FR'
, PAY.SEGMENT3
, 'GB'
, PAY.SEGMENT3
, 'HK'
, PAY.SEGMENT2
, 'NZ'
, PAY.SEGMENT5
, 'SG'
, PAY.SEGMENT6
, 'US'
, PAY.SEGMENT4
, NULL)
, DECODE(PAY.TERRITORY_CODE
, /* BANK ACCOUNT TYPE */ 'CA'
, PAY.SEGMENT2
, 'FR'
, PAY.SEGMENT9
, 'GB'
, PAY.SEGMENT6
, 'SG'
, PAY.SEGMENT3
, 'US'
, PAY.SEGMENT2
, NULL)
, DECODE(PAY.TERRITORY_CODE
, /* SECONDARY ACCOUNT REFERENCE */ 'GB'
, PAY.SEGMENT7
, NULL)
, DECODE(PAY.TERRITORY_CODE
, /* COMMUNICATION AGREEMENT ID */ 'IT'
, PAY.SEGMENT6
, NULL)
, DECODE(PAY.TERRITORY_CODE
, /* ACCOUNT SUFFIX */ 'NZ'
, PAY.SEGMENT3
, NULL)
, DECODE(PAY.TERRITORY_CODE
, /* DESCRIPTION */ 'NZ'
, PAY.SEGMENT4
, NULL)
, NULL
, /* DESCRIPTION CODE1 */ NULL
, /* DESCRIPTION CODE2 */ DECODE(PAY.TERRITORY_CODE
, /* EFT REQUESTER IDENTIFIER */ 'FR'
, PAY.SEGMENT8
, NULL)
, NULL
, /* BANK NAME KANA */ PAY.START_DATE_ACTIVE
, /* BANK START DATE */ PAY.END_DATE_ACTIVE
, /* BANK END DATE */ NULL
, /* BRANCH NAME KANA */ PAY.START_DATE_ACTIVE
, /* BRANCH START DATE */ PAY.END_DATE_ACTIVE
, /* BRANCH END DATE */ PAY.START_DATE_ACTIVE
, /* ACCOUNT START DATE */ PAY.END_DATE_ACTIVE
, /* ACCOUNT END DATE */ FND.TERRITORY_SHORT_NAME
, PAY.TERRITORY_CODE
, PAY.CREATION_DATE
, PAY.CREATED_BY
, PAY.LAST_UPDATE_DATE
, PAY.LAST_UPDATED_BY
, PAY.LAST_UPDATE_LOGIN
FROM FND_TERRITORIES_VL FND
, PAY_EXTERNAL_ACCOUNTS PAY
WHERE PAY.TERRITORY_CODE = FND.TERRITORY_CODE
AND PAY.TERRITORY_CODE <> 'JP' UNION ALL SELECT PAY.EXTERNAL_ACCOUNT_ID
, PAY.SEGMENT9
, PAY.SEGMENT8
, JPB.BANK_NAME
, JPBB.BRANCH_NAME
, NULL
, NULL
, PAY.SEGMENT1
, PAY.SEGMENT4
, PAY.SEGMENT7
, NULL
, NULL
, NULL
, NULL
, PAY.SEGMENT10
, PAY.SEGMENT11
, NULL
, JPB.BANK_NAME_KANA
, JPB.START_DATE_ACTIVE
, JPB.END_DATE_ACTIVE
, JPBB.BRANCH_NAME_KANA
, JPBB.START_DATE_ACTIVE
, JPBB.END_DATE_ACTIVE
, PAY.START_DATE_ACTIVE
, PAY.END_DATE_ACTIVE
, FND.TERRITORY_SHORT_NAME
, PAY.TERRITORY_CODE
, PAY.CREATION_DATE
, PAY.CREATED_BY
, PAY.LAST_UPDATE_DATE
, PAY.LAST_UPDATED_BY
, PAY.LAST_UPDATE_LOGIN
FROM PAY_JP_BANK_BRANCHES JPBB
, PAY_JP_BANKS JPB
, FND_TERRITORIES_VL FND
, PAY_EXTERNAL_ACCOUNTS PAY
WHERE PAY.TERRITORY_CODE = FND.TERRITORY_CODE
AND PAY.TERRITORY_CODE = 'JP'
AND JPB.BANK_CODE = PAY.SEGMENT1
AND JPBB.BANK_CODE = PAY.SEGMENT1
AND JPBB.BRANCH_CODE = PAY.SEGMENT4