SELECT avbt.trx_type type,
avbt.trx_type break_type,
avbt.type_meaning type_dsp,
avbt.trx_date trx_date,
to_date(NULL) maturity_date,
to_number(NULL) bank_account_amount,
avbt.trx_number trx_number,
NULL payment_method,
avbt.currency_code currency_code,
to_number(NULL) amount,
to_number(NULL) account_amount,
10 transaction_order,
avbt.batch_id batch_id,
avbt.batch_number batch_number,
avbt.remittance_number remittance_number,
to_number(null),
sp.name ORG_NAME,
CE_CEXAVBCH_XMLP_PKG.c_account_amountformula(avbt.trx_type, avbt.batch_id) C_ACCOUNT_AMOUNT,
CE_CEXAVBCH_XMLP_PKG.c_amountformula(avbt.trx_type, avbt.batch_id) C_AMOUNT,
CE_CEXAVBCH_XMLP_PKG.c_bank_account_amountformula(avbt.trx_type, avbt.batch_id) C_BANK_ACCOUNT_AMOUNT,
CE_CEXAVBCH_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
CE_CEXAVBCH_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT
FROM CE_222_BATCHES_V avbt, ce_security_profiles_gt sp
WHERE avbt.bank_account_id =:P_BANK_ACCOUNT AND
avbt.trx_type = 'RECEIPT' AND
:P_QUERY_RECEIPTS = 'Y' AND
avbt.org_id = sp.organization_id and
sp.organization_type = 'OPERATING_UNIT' and
avbt.org_id = nvl(:P_ORG_ID, avbt.org_id ) and
EXISTS ( SELECT null
FROM ce_222_transactions_v avtr
WHERE avbt.batch_id = avtr.batch_id and avbt.bank_account_id = avtr.bank_account_id )
UNION ALL
SELECT avbt.trx_type type,
avbt.trx_type break_type,
avbt.type_meaning type_dsp,
avbt.trx_date trx_date,
to_date(NULL) maturity_date,
to_number(NULL) bank_account_amount,
avbt.trx_number trx_number,
NULL payment_method,
avbt.currency_code currency_code,
to_number(NULL) amount,
to_number(NULL) account_amount,
20 transaction_order,
avbt.batch_id batch_id,
avbt.batch_number batch_number,
avbt.remittance_number remittance_number,
to_number(null),
sp.name ORG_NAME,
CE_CEXAVBCH_XMLP_PKG.c_account_amountformula(avbt.trx_type, avbt.batch_id) C_ACCOUNT_AMOUNT,
CE_CEXAVBCH_XMLP_PKG.c_amountformula(avbt.trx_type, avbt.batch_id) C_AMOUNT,
CE_CEXAVBCH_XMLP_PKG.c_bank_account_amountformula(avbt.trx_type, avbt.batch_id) C_BANK_ACCOUNT_AMOUNT,
CE_CEXAVBCH_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
CE_CEXAVBCH_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT
FROM CE_200_BATCHES_V avbt, ce_security_profiles_gt sp
WHERE avbt.bank_account_id =:P_BANK_ACCOUNT AND
avbt.trx_type = 'PAYMENT' AND
:P_QUERY_PAYMENTS = 'Y' AND
avbt.org_id = sp.organization_id and
sp.organization_type = 'OPERATING_UNIT' and
avbt.org_id = nvl(:P_ORG_ID, avbt.org_id ) and
EXISTS( select null from ce_200_transactions_v avtr
where avbt.bank_account_id = avtr.bank_account_id and
avbt.batch_id = avtr.batch_id AND
avbt.batch_number = avtr.batch_name AND
NVL(avtr.status, 'NEGOTIABLE') != 'VOIDED')
&C_ORDER_BY |
