VIEW: APPS.IBY_PMT_LVL_PMT_STS_CHG_XML_V

Source

View Text - Preformatted

SELECT XMLConcat( XMLElement("PaymentGroup", XMLElement("FNDApplicationID", req.calling_app_id), XMLElement("PaymentInstruction", (SELECT XMLElement("ChangedPayment", XMLAgg( XMLForest( p.payment_id AS "PaymentID", 'dummy' AS "NewStatus", 'dummy' AS "DirectionOfChange" ) ) ) FROM iby_payments_all p WHERE p.payment_instruction_id=instr.payment_instruction_id AND p.payment_service_request_id=req.payment_service_request_id ) ) ) ), 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, instr.payment_instruction_id, instr.payment_instruction_status

View Text - HTML Formatted

SELECT XMLCONCAT( XMLELEMENT("PAYMENTGROUP"
, XMLELEMENT("FNDAPPLICATIONID"
, REQ.CALLING_APP_ID)
, XMLELEMENT("PAYMENTINSTRUCTION"
, (SELECT XMLELEMENT("CHANGEDPAYMENT"
, XMLAGG( XMLFOREST( P.PAYMENT_ID AS "PAYMENTID"
, 'DUMMY' AS "NEWSTATUS"
, 'DUMMY' AS "DIRECTIONOFCHANGE" ) ) )
FROM IBY_PAYMENTS_ALL P
WHERE P.PAYMENT_INSTRUCTION_ID=INSTR.PAYMENT_INSTRUCTION_ID
AND P.PAYMENT_SERVICE_REQUEST_ID=REQ.PAYMENT_SERVICE_REQUEST_ID ) ) ) )
, 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
, INSTR.PAYMENT_INSTRUCTION_ID
, INSTR.PAYMENT_INSTRUCTION_STATUS