[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 (UPPER(payment_channel_code) = UPPER(p_payment_channel_code)
OR p_payment_channel_code is null);
SELECT SUM(icount) tcount
INTO l_count1
FROM
(SELECT COUNT(*) icount
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')
UNION
SELECT COUNT(*) icount
FROM iby_pay_service_requests iby
WHERE iby.calling_app_id = 200
AND iby.process_type = 'STANDARD'
AND iby.payment_service_request_status = 'PAYMENTS_CREATED'
AND EXISTS
(SELECT 'NEED_ACTION_BY_ME'
FROM iby_payments_all pmt
WHERE iby.payment_service_request_id = pmt.payment_service_request_id
AND EXISTS
(SELECT 'NEED_ACTION_BY_ME'
FROM iby_pay_instructions_all instr
WHERE 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', 'TRANSMITTED')
AND instr.payments_complete_code = 'NO'
AND iby_utility_pvt.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',
'FAILED_VALIDATION', 'FAILED_BY_CALLING_APP', 'FAILED_BY_REJECTION_LEVEL')
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','COMPLETED')
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 1
into l_valid_completed_pmt
from dual
where exists(select 'VALID_PAYMENT'
from iby_payments_all
where payment_service_request_id = p_psr_id
and payments_complete_flag = 'Y'
and payment_status in ('INSTRUCTION_CREATED', 'ISSUED', 'FORMATTED', 'TRANSMITTED'));
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');
SELECT decode(decode(template_type_code,
'RTF','PDF',
'ETEXT','ETEXT',
'XSL-XML','XML',
'PDF','PDF'),'PDF', 'IBY_FD_PAYMENT_FORMAT','IBY_FD_PAYMENT_FORMAT_TEXT')
into
l_conc_prog
FROM iby_pay_instructions_all ins,
iby_payment_profiles pp,
iby_formats_b format,
XDO_TEMPLATES_B temp
WHERE ins.payment_instruction_id = p_pay_instruction_id
AND ins.payment_profile_id = pp.payment_profile_id
AND format.FORMAT_CODE = pp.PAYMENT_FORMAT_CODE
AND format.FORMAT_TEMPLATE_CODE = temp.template_code
AND nvl(temp.end_date, SYSDATE)>= SYSDATE;
| 'UNSTARTED', 'SELECTING', 'CANCELING',
| 'CALCULATING', 'SELECTED'
| IBY:
| 'INSERTED', 'SUBMITTED',
| 'ASSIGNMENT_COMPLETE','DOCUMENTS_VALIDATED',
| 'RETRY_DOCUMENT_VALIDATION',
| 'RETRY_PAYMENT_CREATION'
|
| USER_TERMINATED AP:
| 'CANCELED' , 'CANCELLED NO PAYMENTS'
| IBY:
| 'TERMINATED'
|
| PROGRAM_ERRORS IBY:
| 'PENDING_REVIEW_DOC_VAL_ERRORS'
| 'PENDING_REVIEW_PMT_VAL_ERRORS'
|
| COMPLETED IBY:
| 'PAYMENTS_CREATED'
|
| TOTAL COUNT(*) IN AP
|
|===========================================================================================
|Understanding PIPELINED FUNCTION:
|-----------------------------------
|PIPELINED functions are piece of code that can be used for querying SQL.
|Basically, when you would like a PLSQL routine to be the source
|of data -- instead of a table -- you would use a pipelined function.
|PIPELINED functions will operate like a table.
|Using PL/SQL table functions can significantly lower the over-head of
|doing such transformations. PL/SQL table functions accept and return
|multiple rows, delivering them as they are ready rather than all at once,
|and can be made to execute as parallel operations.
|
-----------------------------------------------------------------------------------------
*/
FUNCTION get_psr_snapshot_pipe RETURN snapshot_count_t PIPELINED
IS
p_snapshot_code VARCHAR2(100) := 'Test';
SELECT ipsr.payment_service_request_id ,
ipsr.payment_service_request_status ,
aisc.status ,
aisc.checkrun_id ,
aisc.checkrun_name ,
aisc.creation_date
FROM iby_pay_service_requests ipsr ,
ap_inv_selection_criteria_all aisc
WHERE ipsr.call_app_pay_service_req_code(+) = aisc.checkrun_name;
ELSIF c_rec_snap.payment_service_request_status IN ('INSERTED', 'SUBMITTED','ASSIGNMENT_COMPLETE','DOCUMENTS_VALIDATED','RETRY_DOCUMENT_VALIDATION','RETRY_PAYMENT_CREATION') THEN
l_processing := l_processing + 1;
ELSIF c_rec_snap.payment_service_request_status IS NULL AND c_rec_snap.status IN ('UNSTARTED', 'SELECTING', 'CANCELING','CALCULATING', 'SELECTED') THEN
l_processing := l_processing + 1;
SELECT 'Y'
INTO l_process_count
FROM dual
WHERE EXISTS
(SELECT 'PROCESSING'
FROM iby_payments_all pmt
WHERE pmt.payment_service_request_id =c_rec_snap.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')
OR (inst.payment_instruction_status = 'TRANSMITTED' AND IBY_FD_USER_API_PUB.Is_transmitted_Pmt_Inst_Compl(inst.PAYMENT_INSTRUCTION_ID) = 'N'))
)
);
SELECT l_need_action,l_processing,l_terminated,l_errors,l_completed
INTO l_ret_val
FROM DUAL;
SELECT pmt_all.INSTR_ID,
pmt_all.ORG_ID
FROM
(SELECT DISTINCT pmt.payment_instruction_id INSTR_ID,
pmt.org_id ORG_ID
FROM iby_payments_all pmt,
iby_pay_instructions_all instr
WHERE p_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')
OR (instr.payment_instruction_status = 'TRANSMITTED' AND IBY_FD_USER_API_PUB.Is_transmitted_Pmt_Inst_Compl(instr.PAYMENT_INSTRUCTION_ID) = 'N'))
) pmt_all;
DELETE FROM IBY_PAY_DASHBOARD_GT;
iby_debug_pub.add(debug_msg => 'Deleted all records from GT table',
debug_level => FND_LOG.LEVEL_STATEMENT,
module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
INSERT INTO IBY_PAY_DASHBOARD_GT(PAYMENT_SERVICE_REQUEST_ID,
CHECKRUN_ID,
CHECKRUN_NAME,
STATE)
SELECT iby.payment_service_request_id,
null,
iby.CALL_APP_PAY_SERVICE_REQ_CODE,
'NEED_ACTION_BY_ME'
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')
UNION
SELECT iby.payment_service_request_id,
null,
iby.CALL_APP_PAY_SERVICE_REQ_CODE,
'NEED_ACTION_BY_ME'
FROM iby_pay_service_requests iby
WHERE iby.calling_app_id = 200
AND iby.process_type = 'STANDARD'
AND iby.payment_service_request_status = 'PAYMENTS_CREATED'
AND EXISTS
(SELECT 'NEED_ACTION_BY_ME'
FROM iby_payments_all pmt
WHERE iby.payment_service_request_id = pmt.payment_service_request_id
AND EXISTS
(SELECT 'NEED_ACTION_BY_ME'
FROM iby_pay_instructions_all instr
WHERE 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', 'TRANSMITTED')
AND instr.payments_complete_code = 'NO'
AND iby_utility_pvt.check_user_access(instr.payment_instruction_id) = 'Y'
)
);
INSERT INTO IBY_PAY_DASHBOARD_GT(PAYMENT_SERVICE_REQUEST_ID,
CHECKRUN_ID,
CHECKRUN_NAME,
STATE)
SELECT null,
ap.checkrun_id,
ap.checkrun_name,
'NEED_ACTION_BY_ME'
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);
INSERT INTO IBY_PAY_DASHBOARD_GT(PAYMENT_SERVICE_REQUEST_ID,
CHECKRUN_ID,
CHECKRUN_NAME,
STATE)
SELECT iby.payment_service_request_id,
null,
iby.CALL_APP_PAY_SERVICE_REQ_CODE,
'PROCESSING'
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',
'FAILED_VALIDATION', 'FAILED_BY_CALLING_APP', 'FAILED_BY_REJECTION_LEVEL')
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'))
)
)
);
INSERT INTO IBY_PAY_DASHBOARD_GT(PAYMENT_SERVICE_REQUEST_ID,
CHECKRUN_ID,
CHECKRUN_NAME,
STATE)
SELECT null,
ap.checkrun_id,
ap.checkrun_name,
'PROCESSING'
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);
INSERT INTO IBY_PAY_DASHBOARD_GT(PAYMENT_SERVICE_REQUEST_ID,
CHECKRUN_ID,
CHECKRUN_NAME,
STATE)
SELECT null,
ap.checkrun_id,
ap.checkrun_name,
'USER_TERMINATED'
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);
INSERT INTO IBY_PAY_DASHBOARD_GT(PAYMENT_SERVICE_REQUEST_ID,
CHECKRUN_ID,
CHECKRUN_NAME,
STATE)
SELECT null,
ap.checkrun_id,
ap.checkrun_name,
'USER_TERMINATED'
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);
INSERT INTO IBY_PAY_DASHBOARD_GT(PAYMENT_SERVICE_REQUEST_ID,
CHECKRUN_ID,
CHECKRUN_NAME,
STATE)
SELECT null,
ap.checkrun_id,
ap.checkrun_name,
'PROGRAM_ERRORS'
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);
INSERT INTO IBY_PAY_DASHBOARD_GT(PAYMENT_SERVICE_REQUEST_ID,
CHECKRUN_ID,
CHECKRUN_NAME,
STATE)
SELECT null,
ap.checkrun_id,
ap.checkrun_name,
'COMPLETED'
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','COMPLETED')
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);