VIEW: APPS.IBY_XML_FD_11IPMT_1_0_V

Source

View Text - Preformatted

SELECT XMLElement("OutboundPayment", XMLElement("PaymentAmount", XMLElement("Value", c.amount), XMLElement("Currency", XMLElement("Code", c.currency_code))) , XMLElement("PaymentDate", c.check_date) , xmlelement("PaymentNumber", xmlelement("CheckNumber", c.check_number)) , xmlelement("PaymentStatus", xmlelement("Code", 'VOID')) , xmlelement("Payee", xmlelement("Name", c.Vendor_Name)) , xmlelement("BankAccount", xmlelement("BranchNumber", ceb.branch_number), xmlelement("BankAccountNumber", ce.bank_Account_num))) payment, ce.bank_account_id INTERNAL_BANK_ACCOUNT_ID, c.check_date PAYMENT_DATE, DECODE(c.positive_pay_status_code,'SENT AS VOIDED','Y','N') positive_pay_file_created_flag, c.check_id CHECK_ID FROM ap_checks_all c , ap_payment_history_all ph , ap_payment_history_all ph2, ce_bank_accounts ce, ce_bank_branches_v ceb, ce_bank_acct_uses_all cebu WHERE c.check_id = ph.check_id AND c.check_id = ph2.check_id AND c.ce_bank_acct_use_id = cebu.bank_acct_use_id AND cebu.bank_account_id = ce.bank_account_id AND ce.bank_id = ceb.bank_party_id AND ce.bank_branch_id = ceb.branch_party_id AND ph.historical_flag = 'Y' AND ph.transaction_type = 'PAYMENT CREATED' AND NVL(ph2.historical_flag, 'N') != 'Y' AND ph2.transaction_type ='PAYMENT CANCELLED'

View Text - HTML Formatted

SELECT XMLELEMENT("OUTBOUNDPAYMENT"
, XMLELEMENT("PAYMENTAMOUNT"
, XMLELEMENT("VALUE"
, C.AMOUNT)
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, C.CURRENCY_CODE)))
, XMLELEMENT("PAYMENTDATE"
, C.CHECK_DATE)
, XMLELEMENT("PAYMENTNUMBER"
, XMLELEMENT("CHECKNUMBER"
, C.CHECK_NUMBER))
, XMLELEMENT("PAYMENTSTATUS"
, XMLELEMENT("CODE"
, 'VOID'))
, XMLELEMENT("PAYEE"
, XMLELEMENT("NAME"
, C.VENDOR_NAME))
, XMLELEMENT("BANKACCOUNT"
, XMLELEMENT("BRANCHNUMBER"
, CEB.BRANCH_NUMBER)
, XMLELEMENT("BANKACCOUNTNUMBER"
, CE.BANK_ACCOUNT_NUM))) PAYMENT
, CE.BANK_ACCOUNT_ID INTERNAL_BANK_ACCOUNT_ID
, C.CHECK_DATE PAYMENT_DATE
, DECODE(C.POSITIVE_PAY_STATUS_CODE
, 'SENT AS VOIDED'
, 'Y'
, 'N') POSITIVE_PAY_FILE_CREATED_FLAG
, C.CHECK_ID CHECK_ID
FROM AP_CHECKS_ALL C
, AP_PAYMENT_HISTORY_ALL PH
, AP_PAYMENT_HISTORY_ALL PH2
, CE_BANK_ACCOUNTS CE
, CE_BANK_BRANCHES_V CEB
, CE_BANK_ACCT_USES_ALL CEBU
WHERE C.CHECK_ID = PH.CHECK_ID
AND C.CHECK_ID = PH2.CHECK_ID
AND C.CE_BANK_ACCT_USE_ID = CEBU.BANK_ACCT_USE_ID
AND CEBU.BANK_ACCOUNT_ID = CE.BANK_ACCOUNT_ID
AND CE.BANK_ID = CEB.BANK_PARTY_ID
AND CE.BANK_BRANCH_ID = CEB.BRANCH_PARTY_ID
AND PH.HISTORICAL_FLAG = 'Y'
AND PH.TRANSACTION_TYPE = 'PAYMENT CREATED'
AND NVL(PH2.HISTORICAL_FLAG
, 'N') != 'Y'
AND PH2.TRANSACTION_TYPE ='PAYMENT CANCELLED'