[Home] [Help]
View: IBY_CLEARED_PMT_XML_V
View Text
SELECT XMLCONCAT( XMLELEMENT("PAYMENTGROUP"
, XMLELEMENT("FNDAPPLICATIONID"
, REQ.CALLING_APP_ID)
, XMLAGG( XMLELEMENT("CLEAREDPAYMENT"
, XMLFOREST( PMT.PAYMENT_ID AS "PAYMENTID"
, 'DUMMY' AS "CLEAREDDATE"
, 'DUMMY' AS "CLEAREDAMOUNT"
, 'DUMMY' AS "BANKERRORAMOUNT"
, 'DUMMY' AS "BANKCHARGEAMOUNT"
, 'DUMMY' AS "BANKACCOUNTCURRENCYCODE"
, 'DUMMY' AS "EXCHANGERATE"
, 'DUMMY' AS "EXCHANGERATETYPE"
, 'DUMMY' AS "EXCHANGERATEDATE"
, 'DUMMY' AS "MATCHEDBANKSTATEMENTFLAG"
, 'DUMMY' AS "ACTUALVALUEDATE"
, 'DUMMY' AS "CLEARINGEVENTACCOUNTINGDATE" ) ) ) ) )
, REQ.PAYMENT_SERVICE_REQUEST_ID
, REQ.PAYMENT_SERVICE_REQUEST_STATUS
, PMT.PAYMENT_ID
, PMT.PAYMENT_STATUS
, INSTR.PAYMENT_INSTRUCTION_ID
, INSTR.PAYMENT_INSTRUCTION_STATUS
FROM IBY_PAYMENTS_ALL PMT
, IBY_PAY_SERVICE_REQUESTS REQ
, IBY_PAY_INSTRUCTIONS_ALL INSTR
WHERE REQ.PAYMENT_SERVICE_REQUEST_ID = PMT.PAYMENT_SERVICE_REQUEST_ID
AND INSTR.PAYMENT_INSTRUCTION_ID = PMT.PAYMENT_INSTRUCTION_ID GROUP BY REQ.CALLING_APP_ID
, REQ.PAYMENT_SERVICE_REQUEST_ID
, REQ.PAYMENT_SERVICE_REQUEST_STATUS
, PMT.PAYMENT_ID
, PMT.PAYMENT_STATUS
, INSTR.PAYMENT_INSTRUCTION_ID
, INSTR.PAYMENT_INSTRUCTION_STATUS
Columns
Name |
CLEARED_CONTENT |
REQUEST_ID |
REQUEST_STATUS |
PAYMENT_ID |
PAYMENT_STATUS |
INSTRUCTION_ID |
INSTRUCTION_STATUS |