[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| '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')
)
)
);
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'))
) pmt_all;