[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
P_LAST_UPDATE_DATE IN DATE,
P_LAST_UPDATED_BY IN NUMBER,
P_LAST_UPDATE_LOGIN IN NUMBER,
P_CREATED_BY IN NUMBER,
P_CREATION_DATE IN DATE,
P_PROGRAM_UPDATE_DATE IN DATE,
P_PROGRAM_APPLICATION_ID IN NUMBER,
P_PROGRAM_ID IN NUMBER,
P_REQUEST_ID IN NUMBER,
P_CALLING_SEQUENCE IN VARCHAR2,
P_LOGICAL_GROUP_REFERENCE IN VARCHAR2
) AS
l_trx_id NUMBER;
SELECT distinct check_id,
status_lookup_code,
amount,
200 app_id
FROM ap_checks checks, iby_payments_all pmts
WHERE checks.payment_instruction_id = P_PAYMENT_INSTRUCTION_ID
AND checks.payment_id = pmts.payment_id (+)
AND Nvl(pmts.logical_group_reference, 'N') = Nvl(P_LOGICAL_GROUP_REFERENCE, nvl(pmts.logical_group_reference,'N'))
/* Bug 8340931
and
exists (select 1 from iby_payments_all pay
where nvl(logical_group_reference,'N') = nvl(P_LOGICAL_GROUP_REFERENCE,nvl(logical_group_reference,'N'))
and checks.payment_instruction_id = pay.payment_instruction_id
and checks.payment_id = pay.payment_id)*/
AND checks.status_lookup_code not in
('VOIDED','SPOILED','OVERFLOW','SETUP','STOP INITIATED',
'UNCONFIRMED SET UP', 'RECONCILED', 'RECONCILED UNACCOUNTED',
'ISSUED')
UNION ALL
SELECT distinct CC.CASHFLOW_ID,
CC.CASHFLOW_STATUS_CODE,
CC.CASHFLOW_AMOUNT,
260 app_id
FROM CE_SECURITY_PROFILES_GT le,
iby_fd_docs_payable_v docs,
iby_fd_payments_v pay,
CE_CASHFLOWS CC,
CE_CASHFLOW_ACCT_H CCH
WHERE pay.payment_instruction_id = P_PAYMENT_INSTRUCTION_ID
AND CC.CASHFLOW_STATUS_CODE IN ('CREATED', 'CLEARED')
AND CC.CASHFLOW_ID = CCH.CASHFLOW_ID
AND CCH.CURRENT_RECORD_FLAG = 'Y'
AND CCH.EVENT_TYPE in
('CE_STMT_RECORDED', 'CE_BAT_CLEARED', 'CE_BAT_CREATED')
and cc.source_trxn_type ='BAT'
AND exists
(select null
from ce_payment_transactions pt
where cc.trxn_reference_number = pt.trxn_reference_number
and pt.trxn_reference_number = cc.trxn_reference_number
and pt.trxn_status_code = 'SETTLED')
and cc.trxn_reference_number = docs.calling_app_doc_ref_number
and pay.payment_id = docs.payment_id
and docs.CALLING_APP_ID = 260
AND CC.CASHFLOW_LEGAL_ENTITY_ID = LE.ORGANIZATION_ID
AND LE.ORGANIZATION_TYPE = 'LEGAL_ENTITY'
ORDER BY 3;
SELECT count(check_id), sum(amount) , max(amount)
FROM ap_checks checks, iby_payments_all pmts
WHERE checks.payment_instruction_id = P_PAYMENT_INSTRUCTION_ID
AND checks.payment_id = pmts.payment_id (+)
AND Nvl(pmts.logical_group_reference, 'N') = Nvl(P_LOGICAL_GROUP_REFERENCE, nvl(pmts.logical_group_reference,'N'))
/* Bug 8340931
AND exists (select 1 from iby_payments_all pay
where nvl(logical_group_reference,'N') = nvl(P_LOGICAL_GROUP_REFERENCE,nvl(logical_group_reference,'N'))
and checks.payment_instruction_id = pay.payment_instruction_id
and checks.payment_id = pay.payment_id) */
AND checks.status_lookup_code NOT IN
('VOIDED','SPOILED','OVERFLOW','SETUP','STOP INITIATED',
'UNCONFIRMED SET UP', 'RECONCILED', 'RECONCILED UNACCOUNTED',
'ISSUED')
UNION ALL
SELECT count(CC.CASHFLOW_ID), sum(CC.CASHFLOW_AMOUNT) ,
max(CC.CASHFLOW_AMOUNT)
FROM CE_SECURITY_PROFILES_GT le,
iby_fd_docs_payable_v docs,
iby_fd_payments_v pay,
CE_CASHFLOWS CC,
CE_CASHFLOW_ACCT_H CCH
WHERE pay.payment_instruction_id = P_PAYMENT_INSTRUCTION_ID
AND CC.CASHFLOW_STATUS_CODE IN ('CREATED', 'CLEARED')
AND CC.CASHFLOW_ID = CCH.CASHFLOW_ID
AND CCH.CURRENT_RECORD_FLAG = 'Y'
AND CCH.EVENT_TYPE in
('CE_STMT_RECORDED', 'CE_BAT_CLEARED', 'CE_BAT_CREATED')
and cc.source_trxn_type ='BAT'
AND exists
(select null
from ce_payment_transactions pt
where cc.trxn_reference_number = pt.trxn_reference_number
and pt.trxn_reference_number = cc.trxn_reference_number
and pt.trxn_status_code = 'SETTLED')
and cc.trxn_reference_number = docs.calling_app_doc_ref_number
and pay.payment_id = docs.payment_id
and docs.CALLING_APP_ID = 260
AND CC.CASHFLOW_LEGAL_ENTITY_ID = LE.ORGANIZATION_ID
AND LE.ORGANIZATION_TYPE = 'LEGAL_ENTITY';
SELECT base_currency_code
INTO l_functional_currency_code
FROM ap_system_parameters;
SELECT payment_currency_code
INTO l_pmt_currency_code
FROM iby_pay_instructions_all
WHERE payment_instruction_id = P_PAYMENT_INSTRUCTION_ID;
X_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
X_CREATED_BY => P_CREATED_BY,
X_CREATION_DATE => P_CREATION_DATE,
X_PROGRAM_UPDATE_DATE => P_PROGRAM_UPDATE_DATE,
X_PROGRAM_APPLICATION_ID=> P_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID => P_PROGRAM_ID,
X_REQUEST_ID => P_REQUEST_ID,
X_CALLING_SEQUENCE => current_calling_sequence
);
P_LAST_UPDATE_DATE IN DATE,
P_LAST_UPDATED_BY IN NUMBER,
P_LAST_UPDATE_LOGIN IN NUMBER,
P_CREATED_BY IN NUMBER,
P_CREATION_DATE IN DATE,
P_PROGRAM_UPDATE_DATE IN DATE,
P_PROGRAM_APPLICATION_ID IN NUMBER,
P_PROGRAM_ID IN NUMBER,
P_REQUEST_ID IN NUMBER,
P_CALLING_SEQUENCE IN VARCHAR2
) AS
-- conditions for AP are
-- copied from apreconb 115.44
CURSOR l_ins_pmt_unclr_cur IS
SELECT chk.check_id,
200 app_id
FROM iby_payments_all pmt,
iby_pay_service_requests ppr,
ap_checks_all chk,
ap_payment_history apha
WHERE pmt.payment_instruction_id = P_PAYMENT_INSTRUCTION_ID
AND pmt.payment_service_request_id = ppr.payment_service_request_id
AND ppr.calling_app_id = 200
AND apha.check_id = chk.check_id
AND apha.transaction_type = 'PAYMENT CLEARING'
AND chk.status_lookup_code IN
('CLEARED', 'CLEARED BUT UNACCOUNTED',
'RECONCILED', 'RECONCILED UNACCOUNTED')
AND NOT EXISTS
(SELECT aphb.payment_history_id
FROM ap_payment_history aphb
WHERE aphb.check_id = apha.check_id
AND aphb.rev_pmt_hist_id = apha.payment_history_id);
X_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
X_CREATED_BY => P_CREATED_BY,
X_CREATION_DATE => P_CREATION_DATE,
X_PROGRAM_UPDATE_DATE => P_PROGRAM_UPDATE_DATE,
X_PROGRAM_APPLICATION_ID=> P_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID => P_PROGRAM_ID,
X_REQUEST_ID => P_REQUEST_ID,
X_CALLING_SEQUENCE => current_calling_sequence
);