VIEW: APPS.IBY_REJECTED_DOCS_XML_V

Source

View Text - Preformatted

SELECT XMLConcat( XMLElement("DocumentGroup", XMLElement("FNDApplicationID", req.calling_app_id), XMLAgg( XMLElement("RejectedDocuments", XMLForest( docs.pay_proc_trxn_type_code AS "PaymentProcessTrxnTypeCode" , req.calling_app_id AS "CallingAppID", docs.calling_app_doc_unique_ref1 AS "CallingAppDocUniqueRef1", docs.calling_app_doc_unique_ref2 AS "CallingAppDocUniqueRef2", docs.calling_app_doc_unique_ref3 AS "CallingAppDocUniqueRef3", docs.calling_app_doc_unique_ref4 AS "CallingAppDocUniqueRef4", docs.calling_app_doc_unique_ref5 AS "CallingAppDocUniqueRef5" ) ) ) ) ), req.payment_service_request_id, req.payment_service_request_status FROM iby_docs_payable_all docs, iby_pay_service_requests req WHERE req.payment_service_request_id = docs.payment_service_request_id AND docs.document_status != 'VALIDATED' AND docs.payment_id IS NULL GROUP BY req.calling_app_id, req.payment_service_request_id, req.payment_service_request_status

View Text - HTML Formatted

SELECT XMLCONCAT( XMLELEMENT("DOCUMENTGROUP"
, XMLELEMENT("FNDAPPLICATIONID"
, REQ.CALLING_APP_ID)
, XMLAGG( XMLELEMENT("REJECTEDDOCUMENTS"
, XMLFOREST( DOCS.PAY_PROC_TRXN_TYPE_CODE AS "PAYMENTPROCESSTRXNTYPECODE"
, REQ.CALLING_APP_ID AS "CALLINGAPPID"
, DOCS.CALLING_APP_DOC_UNIQUE_REF1 AS "CALLINGAPPDOCUNIQUEREF1"
, DOCS.CALLING_APP_DOC_UNIQUE_REF2 AS "CALLINGAPPDOCUNIQUEREF2"
, DOCS.CALLING_APP_DOC_UNIQUE_REF3 AS "CALLINGAPPDOCUNIQUEREF3"
, DOCS.CALLING_APP_DOC_UNIQUE_REF4 AS "CALLINGAPPDOCUNIQUEREF4"
, DOCS.CALLING_APP_DOC_UNIQUE_REF5 AS "CALLINGAPPDOCUNIQUEREF5" ) ) ) ) )
, REQ.PAYMENT_SERVICE_REQUEST_ID
, REQ.PAYMENT_SERVICE_REQUEST_STATUS
FROM IBY_DOCS_PAYABLE_ALL DOCS
, IBY_PAY_SERVICE_REQUESTS REQ
WHERE REQ.PAYMENT_SERVICE_REQUEST_ID = DOCS.PAYMENT_SERVICE_REQUEST_ID
AND DOCS.DOCUMENT_STATUS != 'VALIDATED'
AND DOCS.PAYMENT_ID IS NULL GROUP BY REQ.CALLING_APP_ID
, REQ.PAYMENT_SERVICE_REQUEST_ID
, REQ.PAYMENT_SERVICE_REQUEST_STATUS