[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO iby_view_parameters_gt
(name,value,created_by,creation_date,last_updated_by,last_update_date,
last_update_login,object_version_number)
VALUES
(p_name,p_val,fnd_global.user_id,sysdate,fnd_global.user_id,
sysdate,fnd_global.login_id,1);
SELECT value
FROM iby_view_parameters_gt
WHERE (name=ci_name);
SELECT count(lookup_code)
INTO l_count
FROM fnd_lookups
WHERE (lookup_type = p_lookup)
AND (lookup_code = p_val)
AND (enabled_flag = 'Y')
AND (NVL(end_date_active,SYSDATE-10) < SYSDATE);
SELECT COUNT(party_id)
INTO l_count
FROM hz_parties
WHERE party_id = ci_party_id;
SELECT count(*)
FROM fnd_application
WHERE (application_id = ci_app_id);
SELECT territory_code
FROM fnd_territories
WHERE (territory_code = ci_code)
AND (NVL(obsolete_flag,'N') = 'N');
SELECT organization_id
INTO l_org_id
FROM hr_operating_units
WHERE organization_id = p_org_id;
SELECT payment_channel_code
INTO l_payment_channel_code
FROM iby_fndcpt_pmt_chnnls_b
WHERE instrument_type = p_instrument_type
AND (payment_channel_code = p_payment_channel_code
OR p_payment_channel_code is null);
SELECT
count(*)
INTO l_count1
FROM iby_pay_service_requests iby
WHERE iby.calling_app_id = 200
AND iby.process_type = 'STANDARD'
AND
( iby.payment_service_request_status IN (
'INFORMATION_REQUIRED',
'PENDING_REVIEW_DOC_VAL_ERRORS',
'PENDING_REVIEW_PMT_VAL_ERRORS',
'PENDING_REVIEW')
OR
(
iby.payment_service_request_status = 'PAYMENTS_CREATED'
AND EXISTS
(
select 'NEED_ACTION_BY_ME'
from iby_payments_all pmt, iby_pay_instructions_all instr
where iby.payment_service_request_id = pmt.payment_service_request_id
and instr.payment_instruction_id = pmt.payment_instruction_id
and instr.payment_instruction_status IN ('CREATION_ERROR',
'FORMATTED_READY_TO_TRANSMIT',
'TRANSMISSION_FAILED',
'FORMATTED_READY_FOR_PRINTING',
'SUBMITTED_FOR_PRINTING',
'CREATED_READY_FOR_PRINTING',
'CREATED_READY_FOR_FORMATTING',
'FORMATTED',
'CREATED')
and check_user_access(instr.payment_instruction_id) = 'Y'
)
)
);
SELECT count(*)
INTO l_count2
FROM ap_inv_selection_criteria_all ap
WHERE ap.status IN ('REVIEW', 'MISSING RATES' )
AND NOT EXISTS ( SELECT 'NEED_ACTION_BY_ME'
FROM iby_pay_service_requests iby
WHERE iby.calling_app_id = 200
AND iby.call_app_pay_service_req_code =
ap.checkrun_name);
SELECT
count(*)
INTO l_count1
FROM iby_pay_service_requests iby
WHERE iby.calling_app_id = 200
AND iby.process_type = 'STANDARD'
AND
(
iby.payment_service_request_status IN ('INSERTED', 'SUBMITTED',
'ASSIGNMENT_COMPLETE',
'DOCUMENTS_VALIDATED',
'RETRY_DOCUMENT_VALIDATION',
'RETRY_PAYMENT_CREATION')
OR
(
iby.payment_service_request_status IN ('PAYMENTS_CREATED')
AND EXISTS
(SELECT 'PROCESSING'
FROM iby_payments_all pmt
WHERE
pmt.payment_service_request_id = iby.payment_service_request_id
AND pmt.payment_status NOT IN('REMOVED', 'VOID', 'VOID_BY_SETUP', 'VOID_BY_OVERFLOW', 'REMOVED_PAYMENT_STOPPED',
'REMOVED_DOCUMENT_SPOILED', 'REMOVED_INSTRUCTION_TERMINATED', 'REMOVED_REQUEST_TERMINATED', 'ISSUED', 'TRANSMITTED', 'REJECTED')
AND pmt.payments_complete_flag <> 'Y'
AND NOT EXISTS
(SELECT 'NEED_ACTION'
FROM iby_pay_instructions_all inst
WHERE pmt.payment_instruction_id = inst.payment_instruction_id
AND inst.payment_instruction_status IN('CREATION_ERROR',
'FORMATTED_READY_TO_TRANSMIT',
'TRANSMISSION_FAILED',
'FORMATTED_READY_FOR_PRINTING',
'SUBMITTED_FOR_PRINTING',
'CREATED_READY_FOR_PRINTING',
'CREATED_READY_FOR_FORMATTING',
'FORMATTED',
'CREATED',
'FORMATTED_ELECTRONIC'))
)
)
);
SELECT count(*)
INTO l_count2
FROM ap_inv_selection_criteria_all ap
WHERE ap.status IN ('UNSTARTED', 'SELECTING', 'CANCELING',
'CALCULATING', 'SELECTED')
AND NOT EXISTS ( SELECT 'PROCESSING'
FROM iby_pay_service_requests iby
WHERE iby.calling_app_id = 200
AND iby.call_app_pay_service_req_code =
ap.checkrun_name);
SELECT count(*)
INTO l_count1
FROM ap_inv_selection_criteria_all ap
WHERE EXISTS ( SELECT 'IBY USER_TERMINATED'
FROM iby_pay_service_requests iby
WHERE iby.calling_app_id = 200
AND iby.call_app_pay_service_req_code =
ap.checkrun_name
AND iby.payment_service_request_status IN
('TERMINATED'))
AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
SELECT count(*)
INTO l_count2
FROM ap_inv_selection_criteria_all ap
WHERE ap.status IN ('CANCELED', 'CANCELLED NO PAYMENTS')
AND TRUNC(ap.creation_date) =TRUNC(sysdate)
AND NOT EXISTS ( SELECT 'AP USER_TERMINATED'
FROM iby_pay_service_requests iby
WHERE iby.calling_app_id = 200
AND iby.call_app_pay_service_req_code =
ap.checkrun_name)
AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
SELECT count(*)
INTO l_count1
FROM ap_inv_selection_criteria_all ap
WHERE EXISTS ( SELECT 'PROGRAM ERRORS'
FROM iby_pay_service_requests iby
WHERE iby.calling_app_id = 200
AND iby.call_app_pay_service_req_code =
ap.checkrun_name
AND iby.payment_service_request_status IN
('PENDING_REVIEW_DOC_VAL_ERRORS',
'PENDING_REVIEW_PMT_VAL_ERRORS'))
AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
SELECT count(*)
INTO l_count1
FROM ap_inv_selection_criteria_all ap
WHERE EXISTS ( SELECT 'COMPLETED'
FROM iby_pay_service_requests iby
WHERE iby.calling_app_id = 200
AND iby.call_app_pay_service_req_code =
ap.checkrun_name
AND iby.payment_service_request_status IN
('PAYMENTS_CREATED')
AND AP_PAYMENT_UTIL_PKG.get_payment_status_flag(iby.payment_service_request_id) = 'Y')
AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
select count (*) total_pmt_count,
count(case when PAYMENTS_COMPLETE_FLAG = 'Y' then 1 else null
end) pmt_complete_count
into l_total_pmt_count, l_pmt_complete_count /*Bug 7248943*/
FROM iby_payments_all
WHERE payment_service_request_id = p_psr_id
AND payment_status NOT IN ('REMOVED', 'VOID_BY_SETUP',
'VOID_BY_OVERFLOW', 'REMOVED_PAYMENT_STOPPED',
'REMOVED_DOCUMENT_SPOILED',
'REMOVED_INSTRUCTION_TERMINATED',
'REMOVED_REQUEST_TERMINATED',
'REJECTED', -- Bug 6897223- new statuses added
'FAILED_BY_CALLING_APP',
'FAILED_BY_REJECTION_LEVEL',
'FAILED_VALIDATION',
'INSTRUCTION_FAILED_VALIDATION'); --Bug 6686639
IF p_psr_status IN ('INSERTED', 'SUBMITTED',
'ASSIGNMENT_COMPLETE',
'DOCUMENTS_VALIDATED',
'RETRY_DOCUMENT_VALIDATION',
'RETRY_PAYMENT_CREATION') THEN
l_psr_status := 'BUILDING';
select count (*) total_pmt_count,
count(case when payment_instruction_id IS NOT NULL then 1
else null end) instr_count,
count(case when PAYMENT_STATUS IN ('REMOVED_INSTRUCTION_TERMINATED',
'REMOVED_REQUEST_TERMINATED',
'VOID',
'REMOVED',
'REMOVED_PAYMENT_STOPPED',
'VOID_BY_SETUP',
'VOID_BY_OVERFLOW',
'REMOVED_DOCUMENT_SPOILED',
'REJECTED',
'FAILED_BY_CALLING_APP',
'FAILED_BY_REJECTION_LEVEL',
'FAILED_VALIDATION',
'INSTRUCTION_FAILED_VALIDATION') then 1
else null end) pmt_terminate_count,
count(case when PAYMENT_STATUS IN ('REMOVED_DOCUMENT_SPOILED',
'REMOVED_DOCUMENT_SKIPPED') then 1
else null end) pmt_spoil_skip
into l_total_pmt_count, l_instr_count, l_pmt_terminate_count, l_pmt_spoil_skip
from iby_payments_all
WHERE payment_service_request_id = p_psr_id ;
g_psr_table.DELETE;
g_psr_snapshot_table.DELETE;
g_instr_access_table.DELETE;
select 'N' into l_access from dual where exists ( select 'Inaccessible org' from iby_payments_all where
payment_instruction_id = p_pay_instruction_id and MO_GLOBAL.CHECK_ACCESS(org_id) = 'N');