SELECT arrf_v.receipt_currency C_RECEIPT_CURRENCY,
arrf_v.remit_bank_name C_REMIT_BANK_NAME,
arrf_v.remit_bank_branch_name C_REMIT_BANK_BRANCH_NAME,
arrf_v.remit_bank_branch_number C_REMIT_BANK_BRANCH_NUM,
arrf_v.remit_bank_account_name C_REMIT_BANK_ACCOUNT_NAME,
arrf_v.remit_bank_account_number C_REMIT_BANK_ACCOUNT_NUMBER,
arrf_v.customer_number C_CUSTOMER_NUMBER,
arrf_v.customer_bank_account_name C_CUST_BANK_ACCOUNT_NAME,
arrf_v.customer_bank_branch_number||' '||
decode(nvl(:P_mask_bnk_acct,'F'),'F',
rpad(substr(arrf_v.customer_bank_account_number,1,4), length(arrf_v.customer_bank_account_number),'*'),
'L',
lpad(substr(arrf_v.customer_bank_account_number,-4), length(arrf_v.customer_bank_account_number),'*'),
'N',
arrf_v.customer_bank_account_number ) C_CUST_MICR_NUMBER,
arrf_v.payment_method C_PAYMENT_METHOD,
arrf_v.maturity_date C_DUE_DATE,
arrf_v.receipt_number C_RECEIPT_NUMBER,
arrf_v.customer_name C_CUSTOMER_NAME,
arrf_v.receipt_amount C_RECEIPT_AMOUNT
FROM ar_remitted_receipts_format_v arrf_v
WHERE arrf_v.remit_batch_id = :p_batch_id
and NOT EXISTS
(SELECT 'Receipt is Reversed'
FROM ar_cash_receipts acr
WHERE acr.cash_receipt_id = arrf_v.cash_receipt_id
AND acr.reversal_date is not null) |