Product: | XTR - Treasury |
---|---|
Description: | A public view for Treasury payments. |
Implementation/DBA Data: | APPS.XTR_SWIFT_EFT_V |
SELECT S.SETTLEMENT_NUMBER SETTLEMENT_NUMBER
, S.COMPANY COMPANY
, BC.PARTY_CODE CPARTY
, P.FULL_NAME COMP_NAME
, PC.FULL_NAME CP_NAME
, S.CURRENCY CURRENCY
, S.SETTLEMENT_AMOUNT SETTLEMENT_AMOUNT
, S.SETTLEMENT_DATE SETTLEMENT_DATE
, S.COMPANY_ACCT_NO COMPANY_ACCT_NO
, S.CPARTY_ACCT_NO CPARTY_ACCT_NO
, B.SWIFT_ID COMP_SWIFT_ID
, PB.SWIFT_ID CP_SWIFT_ID
, P.ADDRESS_1 COMP_ADDRESS1
, P.ADDRESS_2 COMP_ADDRESS2
, P.ADDRESS_3 COMP_ADDRESS3
, PC.P_ADDRESS_1 CP_ADDRESS1
, PC.P_ADDRESS_2 CP_ADDRESS2
, PC.P_ADDRESS_3 CP_ADDRESS3
, B.BANK_CODE COMP_BANK_CODE
, BC.BANK_CODE CP_BANK_CODE
, B.BANK_NAME COMP_BANK_NAME
, BC.BANK_NAME CP_BANK_NAME
, B.LOCATION COMP_BANK_LOCATION
, BC.LOCATION CP_BANK_LOCATION
, B.STREET COMP_BANK_STREET
, BC.STREET CP_BANK_STREET
, BC.SUB_ACCOUNT_AT CP_CORR_BANK_NAME
, BC.SUB_ACCOUNT_NUMBER CP_CORR_BANK_NO
, IC.IBAN CP_IBAN
, S.SETTLEMENT_SUMMARY_ID SETTLEMENT_SUMMARY_ID
FROM XTR_SETTLEMENT_SUMMARY S
, XTR_BANK_ACCOUNTS B
, XTR_BANK_ACCOUNTS BC
, XTR_BANK_ACCOUNTS IC
, XTR_PARTIES_V P
, XTR_PARTIES_V PC
, XTR_PARTIES_V PB
WHERE S.SETTLEMENT_AMOUNT< 0
AND BC.BANK_CODE = PB.PARTY_CODE
AND B.ACCOUNT_NUMBER = S.COMPANY_ACCT_NO
AND B.PARTY_CODE = S.COMPANY
AND BC.ACCOUNT_NUMBER = S.CPARTY_ACCT_NO
AND IC.ACCOUNT_NUMBER = S.CPARTY_ACCT_NO
AND P.PARTY_CODE = S.COMPANY
AND B.PARTY_TYPE = 'C'
AND S.STATUS= 'A'
AND S.CPARTY_CODE = PC.PARTY_CODE
AND PC.PARTY_CODE = BC.PARTY_CODE
AND NVL(S.EXTERNAL_SOURCE
, 'N')<> 'ZBA'