Product: | CE - Cash Management |
---|---|
Description: | Available AP/IBY payment Groups for reconciliation |
Implementation/DBA Data: | APPS.CE_200_GROUPS_V |
SELECT DISTINCT B.ROWID
, /* 1 */ 'N'
, /* 2 */ BA.BANK_ACCOUNT_ID
, /* 3 */ BA.BANK_ACCOUNT_NAME
, /* 4 */ B.PAYMENT_INSTRUCTION_ID
, /* 5 */ TO_CHAR(B.PAYMENT_INSTRUCTION_ID)
, /* 6 */ L1.LOOKUP_CODE
, /* 7 */ L1.MEANING
, /* 8 */ B.PAYMENT_DATE
, /* 9 */ B.PAYMENT_CURRENCY_CODE
, /* 10 */ TO_NUMBER(NULL)
, /* 11 */ TO_NUMBER(NULL)
, /* 12 */ TO_NUMBER(NULL)
, /* 13 */ TO_NUMBER(NULL)
, /* 14 */ TO_DATE(NULL)
, /* 15 */ TO_CHAR(NULL)
, /* 16 */ TO_CHAR(NULL)
, /* 17 */ TO_DATE(NULL)
, /* 18 */ TO_NUMBER(NULL)
, /* 19 */ TO_NUMBER(NULL)
, /* 20 */ TO_NUMBER(NULL)
, /* 21 */ B.CREATED_BY
, /* 22 */ B.CREATION_DATE
, /* 23 */ B.LAST_UPDATED_BY
, /* 24 */ B.LAST_UPDATE_DATE
, /* 25 */ TO_NUMBER(NULL)
, /* 26 */ TO_CHAR(B.PAYMENT_INSTRUCTION_ID)
, /* 27 */ 673
, /* 28 */ 0
, /* 29 */ BA.BANK_ACCOUNT_NUM
, /* 30 */ BANKPARTY.PARTY_NAME
, /* 31 */ BRANCHPARTY.PARTY_NAME
, /* 32 */ BA.CURRENCY_CODE
, /* 33 */ TO_CHAR(B.PAYMENT_INSTRUCTION_ID)
, /* 34 */ DECODE(B.PAYMENT_CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 35 */ 'FUNCTIONAL'
, BA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, TO_NUMBER(NULL)
, /* 36 */ DECODE(B.ORG_TYPE
, 'OPERATING_UNIT'
, B.ORG_ID
, NULL)
, /* 37 */ DECODE(B.ORG_TYPE
, 'LEGAL_ENTITY'
, B.ORG_ID
, NULL)
, /*38*/ B1.LOGICAL_GROUP_REFERENCE
FROM CE_LOOKUPS L1
, HZ_PARTIES BANKPARTY
, HZ_PARTIES BRANCHPARTY
, CE_BANK_ACCTS_GT_V BA
, IBY_PAY_INSTRUCTIONS_ALL B
, IBY_PAYMENTS_ALL B1
, GL_SETS_OF_BOOKS SOB
, CE_SYSTEM_PARAMETERS SP
WHERE L1.LOOKUP_TYPE = 'BATCH_TYPE'
AND L1.LOOKUP_CODE = 'PAYMENT'
AND B.ORG_ID IS NOT NULL
AND B.PAYMENT_INSTRUCTION_ID = B1.PAYMENT_INSTRUCTION_ID
AND BA.BANK_BRANCH_ID = BRANCHPARTY.PARTY_ID
AND BA.BANK_ID = BANKPARTY.PARTY_ID
AND B.INTERNAL_BANK_ACCOUNT_ID = BA.BANK_ACCOUNT_ID
AND SP.LEGAL_ENTITY_ID = BA.ACCOUNT_OWNER_ORG_ID
AND SOB.SET_OF_BOOKS_ID = SP.SET_OF_BOOKS_ID
AND B1.LOGICAL_GROUP_REFERENCE IS NOT NULL