1 Package Body IBY_PAYMENT_MANAGER_PKG AS
2 /* $Header: ibypaymb.pls 120.1 2011/01/28 22:37:54 svinjamu noship $ */
3
4 TYPE t_psr_record_type IS RECORD(
5 psr_status VARCHAR2(30),
6 payment_status_flag VARCHAR2(1)
7 );
8
9 TYPE psr_table_type IS TABLE OF t_psr_record_type INDEX BY BINARY_INTEGER;
10
11 g_psr_table psr_table_type;
12
13 TYPE t_psr_snapshot_record_type IS RECORD(
14 psr_snapshot_count NUMBER
15 );
16
17 TYPE psr_snapshot_table_type IS TABLE OF t_psr_snapshot_record_type INDEX BY varchar2(30);
18
19 g_psr_snapshot_table psr_snapshot_table_type;
20
21 TYPE t_instr_access_record_type IS RECORD(
22 instruction_id Number,
23 access_flag VARCHAR2(1)
24 );
25
26 TYPE instr_access_table_type IS TABLE OF t_instr_access_record_type INDEX BY BINARY_INTEGER;
27
28 g_instr_access_table instr_access_table_type;
29
30
31 /*-----------------------------------------------------------------------------------------
32 | FUNCTION - get_psr_snapshot_count PIPELINED.
33 |
34 |
35 | DESCRIPTION - This function is designed for the Payables Payment Manager
36 | Home Page . The function returns the total count of Payment
37 | Process Requests with a particular Status or a combination
38 | of Payment Process Request Statuses that map to a particular
39 | snapshot code
40 |
41 | SNAPSHOT CODE STATUS
42 | ------------- ------------------------------------------------
43 | NEED_ACTION_BY_ME AP:
44 | 'REVIEW', 'MISSING RATES'
45 | IBY:
46 | 'INFORMATION_REQUIRED'
47 | 'PENDING_REVIEW_DOC_VAL_ERRORS',
48 | 'PENDING_REVIEW_PMT_VAL_ERRORS',
49 | 'PENDING_REVIEW'
50 |
51 | PROCESSING AP:
52 | 'UNSTARTED', 'SELECTING', 'CANCELING',
53 | 'CALCULATING', 'SELECTED'
54 | IBY:
55 | 'INSERTED', 'SUBMITTED',
56 | 'ASSIGNMENT_COMPLETE','DOCUMENTS_VALIDATED',
57 | 'RETRY_DOCUMENT_VALIDATION',
58 | 'RETRY_PAYMENT_CREATION'
59 |
60 | USER_TERMINATED AP:
61 | 'CANCELED' , 'CANCELLED NO PAYMENTS'
62 | IBY:
63 | 'TERMINATED'
64 |
65 | PROGRAM_ERRORS IBY:
66 | 'PENDING_REVIEW_DOC_VAL_ERRORS'
67 | 'PENDING_REVIEW_PMT_VAL_ERRORS'
68 |
69 | COMPLETED IBY:
70 | 'PAYMENTS_CREATED'
71 |
72 | TOTAL COUNT(*) IN AP
73 |
74 |===========================================================================================
75 |Understanding PIPELINED FUNCTION:
76 |-----------------------------------
77 |PIPELINED functions are piece of code that can be used for querying SQL.
78 |Basically, when you would like a PLSQL routine to be the source
79 |of data -- instead of a table -- you would use a pipelined function.
80 |PIPELINED functions will operate like a table.
81 |Using PL/SQL table functions can significantly lower the over-head of
82 |doing such transformations. PL/SQL table functions accept and return
83 |multiple rows, delivering them as they are ready rather than all at once,
84 |and can be made to execute as parallel operations.
85 |
86 -----------------------------------------------------------------------------------------
87 */
88
89 FUNCTION get_psr_snapshot_pipe RETURN snapshot_count_t PIPELINED
90 IS
91
92 p_snapshot_code VARCHAR2(100) := 'Test';
93 l_ret_val snapshot_count_type;
94 l_status_code FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE;
95 --l_ret_val NUMBER;
96 l_count1 NUMBER;
97 l_count2 NUMBER;
98 l_count3 NUMBER;
99 l_count4 NUMBER;
100 -- Variables for count
101 l_need_action NUMBER;
102 l_processing NUMBER;
103 l_terminated NUMBER;
104 l_errors NUMBER;
105 l_completed NUMBER;
106 l_process_count NUMBER;
107 l_access VARCHAR2(1);
108 --Loop For every ppr
109 CURSOR c_psr_snapshot
110 IS
111 SELECT ipsr.payment_service_request_id ,
112 ipsr.payment_service_request_status ,
113 aisc.status ,
114 aisc.checkrun_id ,
115 aisc.checkrun_name ,
116 aisc.creation_date
117 FROM iby_pay_service_requests ipsr ,
118 ap_inv_selection_criteria_all aisc
119 WHERE ipsr.call_app_pay_service_req_code(+) = aisc.checkrun_name;
120 --AND process_type = 'STANDARD' ;
121 BEGIN
122 IF(g_psr_snapshot_table.EXISTS(p_snapshot_code) AND g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count IS NOT NULL) THEN
123
124 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
125 iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_psr_snapshot_count :Getting from the cache', debug_level => FND_LOG.LEVEL_STATEMENT, module => 'IBY_UTILITY_PVT.get_psr_snapshot_count');
126 END IF ;
127 ELSE
128 -- Initialize the variables.
129 l_need_action :=0;
130 l_processing :=0;
131 l_terminated :=0;
132 l_errors :=0;
133 l_completed :=0;
134 FOR c_rec_snap IN c_psr_snapshot
135 LOOP
136
137 /* IBY NEED ACTION */
138 --Handling everything with iby_pay_service_requests first.
139 --For IBY_PAY_SERVICE_REQUESTS.
140 IF c_rec_snap.payment_service_request_status IN ('INFORMATION_REQUIRED' , 'PENDING_REVIEW_DOC_VAL_ERRORS','PENDING_REVIEW_PMT_VAL_ERRORS', 'PENDING_REVIEW') THEN
141 l_need_action := l_need_action + 1;
142 --For records in ap_inv_selection_criteria_all but not in iby_pay_service_request. For PPR which are still before build.
143 ELSIF c_rec_snap.payment_service_request_status IS NULL AND c_rec_snap.status IN ('REVIEW', 'MISSING RATES' ) THEN
144 l_need_action := l_need_action + 1;
145 /* IBY NEED ACTION */
146 /* IBY IN PROCESS. */
147 ELSIF c_rec_snap.payment_service_request_status IN ('INSERTED', 'SUBMITTED','ASSIGNMENT_COMPLETE','DOCUMENTS_VALIDATED','RETRY_DOCUMENT_VALIDATION','RETRY_PAYMENT_CREATION') THEN
148 l_processing := l_processing + 1;
149 ELSIF c_rec_snap.payment_service_request_status IS NULL AND c_rec_snap.status IN ('UNSTARTED', 'SELECTING', 'CANCELING','CALCULATING', 'SELECTED') THEN
150 l_processing := l_processing + 1;
151 /* IBY IN PROCESS. */
152 /* PAYMENTS_CREATED and it can be in either processing or need action group */
153 --Special case Payment Created
154 ELSIF c_rec_snap.payment_service_request_status IN ('PAYMENTS_CREATED') THEN
155 -- Need to check the payment instruction and individual payments.
156 l_access := 'N';
157 --Check org access also verifies the Payment Instruction statuses that are valid.
158 l_access := check_org_access(c_rec_snap.payment_service_request_id);
159 IF l_access = 'Y'
160 THEN
161 l_need_action := l_need_action + 1;
162 ELSE
163 -- For Processing Action.
164 BEGIN
165 SELECT 'Y'
166 INTO l_process_count
167 FROM dual
168 WHERE EXISTS
169 (SELECT 'PROCESSING'
170 FROM iby_payments_all pmt
171 WHERE pmt.payment_service_request_id =c_rec_snap.payment_service_request_id
172 AND pmt.payment_status NOT IN('REMOVED', 'VOID', 'VOID_BY_SETUP', 'VOID_BY_OVERFLOW', 'REMOVED_PAYMENT_STOPPED', 'REMOVED_DOCUMENT_SPOILED', 'REMOVED_INSTRUCTION_TERMINATED',
173 'REMOVED_REQUEST_TERMINATED', 'ISSUED', 'TRANSMITTED', 'REJECTED')
174 AND pmt.payments_complete_flag <> 'Y'
175 AND NOT EXISTS
176 (SELECT 'NEED_ACTION'
177 FROM iby_pay_instructions_all inst
178 WHERE pmt.payment_instruction_id = inst.payment_instruction_id
179 AND (inst.payment_instruction_status IN('CREATION_ERROR', 'FORMATTED_READY_TO_TRANSMIT', 'TRANSMISSION_FAILED', 'FORMATTED_READY_FOR_PRINTING', 'SUBMITTED_FOR_PRINTING',
180 'CREATED_READY_FOR_PRINTING', 'CREATED_READY_FOR_FORMATTING', 'FORMATTED', 'CREATED', 'FORMATTED_ELECTRONIC')
181 )
182 )
183 );
184 -- If it comes here then it is in processing status
185 l_processing := l_processing + 1;
186 EXCEPTION
187 WHEN NO_DATA_FOUND THEN
188 NULL; -- Do Nothing.
189 WHEN OTHERS THEN
190 iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_psr_snapshot_count :Error in processing count', debug_level => FND_LOG.LEVEL_STATEMENT, module => 'IBY_UTILITY_PVT.get_psr_snapshot_count');
191 END;
192 END IF;
193 /* PAYMENTS_CREATED and it can be in either processing or need action group */
194 /* IBY Terminated */
195 ELSIF c_rec_snap.payment_service_request_status IN ('TERMINATED') AND (c_rec_snap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999))THEN
196 l_terminated := l_terminated + 1;
197 ELSIF c_rec_snap.payment_service_request_status IS NULL AND (c_rec_snap.status IN('CANCELED', 'CANCELLED NO PAYMENTS'))
198 AND (c_rec_snap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999)) THEN
199 l_terminated := l_terminated + 1;
200 /* IBY Terminated */
201 /* IBY Errors */
202 ELSIF c_rec_snap.payment_service_request_status IN ('PENDING_REVIEW_DOC_VAL_ERRORS', 'PENDING_REVIEW_PMT_VAL_ERRORS') AND
203 (c_rec_snap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999)) THEN
204 l_errors := l_errors + 1;
205 /* IBY Errors */
206 /* IBY Completed */
207 ELSIF c_rec_snap.payment_service_request_status IN ('PAYMENTS_CREATED','COMPLETED') AND
208 (c_rec_snap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999)) AND
209 (AP_PAYMENT_UTIL_PKG.get_payment_status_flag(c_rec_snap.payment_service_request_id) = 'Y') THEN
210 l_completed := l_completed + 1;
211 /* IBY Completed */
212 END IF;
213 END LOOP;
214
215 END IF;
216 -- dbms_output.put_line('Outputs:: Action::'||l_need_action ||' Processing::'||l_processing||' Terminated::'||l_terminated||' Errors::'||l_errors||' Completed::'|| l_completed);
217
218 SELECT l_need_action,l_processing,l_terminated,l_errors,l_completed
219 INTO l_ret_val
220 FROM DUAL;
221 --INSERTING INTO PIPE SO IT CAN BE QUERIED.
222 PIPE ROW( l_ret_val);
223 RETURN ;
224 END get_psr_snapshot_pipe;
225
226 FUNCTION check_org_access(
227 p_payment_service_request_id IN NUMBER)
228 RETURN VARCHAR2
229 IS
230 l_instr_id NUMBER;
231 l_access VARCHAR2(1);
232 CURSOR c_org_access
233 IS
234 SELECT pmt_all.INSTR_ID,
235 pmt_all.ORG_ID
236 FROM
237 (SELECT DISTINCT pmt.payment_instruction_id INSTR_ID,
238 pmt.org_id ORG_ID
239 FROM iby_payments_all pmt,
240 iby_pay_instructions_all instr
241 WHERE p_payment_service_request_id = pmt.payment_service_request_id
242 AND instr.payment_instruction_id = pmt.payment_instruction_id
243 AND (instr.payment_instruction_status IN ('CREATION_ERROR', 'FORMATTED_READY_TO_TRANSMIT', 'TRANSMISSION_FAILED', 'FORMATTED_READY_FOR_PRINTING',
244 'SUBMITTED_FOR_PRINTING', 'CREATED_READY_FOR_PRINTING', 'CREATED_READY_FOR_FORMATTING', 'FORMATTED', 'CREATED'))
245 ) pmt_all;
246 BEGIN
247 -- Initialize all variables.
248 l_instr_id := NULL;
249 l_access := 'N';
250 FOR i_org_access IN c_org_access
251 LOOP
252 IF l_instr_id IS NOT NULL AND l_access = 'N' THEN
253 RETURN 'N';
254 END IF;
255 l_access := 'Y'; -- Initialize for new instruction
256 l_instr_id := i_org_access.INSTR_ID;
257 IF MO_GLOBAL.check_access(i_org_access.ORG_ID) = 'N' THEN
258 l_access := 'N';
259 END IF;
260 END LOOP;
261
262 -- Take care of the last instruction here.
263 IF l_access = 'Y' THEN -- User has access to this last instruction, hence PPR
264 RETURN 'Y';
265 ELSE
266 RETURN 'N';
267 END IF;
268 END check_org_access;
269
270 END IBY_PAYMENT_MANAGER_PKG;
[Home] [Help]