eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.AR_CAO_ASSIGN_PKG

Source


1 PACKAGE BODY ar_cao_assign_pkg AS
5 
2 /* $Header: ARCAOAB.pls 120.6 2010/03/02 06:51:40 rviriyal noship $*/
3 
4 PROCEDURE write_debug_and_log(p_message IN VARCHAR2) IS
6 BEGIN
7 
8   IF FND_GLOBAL.CONC_REQUEST_ID is not null THEN
9 
10     fnd_file.put_line(FND_FILE.LOG,p_message);
11 
12   END IF;
13 
14   arp_standard.debug(p_message);
15 
16 EXCEPTION
17 WHEN others THEN
18     NULL;
19 END;
20 
21 PROCEDURE spawn_child_requests(p_operating_unit              IN NUMBER,
22                          p_receipt_date_from   IN VARCHAR2,
23                          p_receipt_date_to     IN VARCHAR2,
24                          p_cust_prof_class     IN NUMBER,
25                          p_max_num_workers     IN NUMBER) IS
26 
27 l_reqid          NUMBER;
28 
29 BEGIN
30 
31 FOR i in 1..(p_max_num_workers-1) LOOP
32 
33     l_reqid := FND_REQUEST.SUBMIT_REQUEST('AR',
34                                          'ARCAOAB',
35                                          'Assign Cash Application Work Items',
36                                          sysdate,
37                                          FALSE,
38                                          p_operating_unit,
39                                          p_receipt_date_from,
40                                          p_receipt_date_to,
41                                          p_cust_prof_class,
42                                          p_max_num_workers,
43                                          i);
44    write_debug_and_log('Request ID' || l_reqid);
45 
46 END LOOP;
47 
48 END spawn_child_requests;
49 
50 /*
51 This function is used to check if a given user has access to the Cash Application Worker Queue Page.
52 user_id  User id to which the check is done.
53 valid_flag  Can have values Y or N.
54             Y - Check if the user currently has access to the function AR_CASH_APPLN_WORK_QUEUE
55                 This validity check is done only if the Grant flag of the function directly is unchecked or
56                 the function directly is present in the exclusion list of the user responsibilities.
57             N - Check if the user is associate to that function
58 */
59 
60 FUNCTION check_access(user_id IN NUMBER,   valid_flag IN VARCHAR2) RETURN NUMBER IS
61 
62  CURSOR valid_resp(user_id_bind NUMBER, curr_date DATE) IS
63 SELECT frv.responsibility_id
64 FROM fnd_responsibility frv,
65   fnd_compiled_menu_functions fcmf,
66   fnd_form_functions fff,
67   fnd_user_resp_groups_direct urg
68 WHERE fff.function_name = 'AR_CASH_APPLN_WORK_QUEUE'
69  AND fcmf.function_id = fff.function_id
70  AND frv.menu_id = fcmf.menu_id
71  AND fcmf.grant_flag = 'Y'
72  AND fff.function_id NOT IN
73   (SELECT frf.action_id
74    FROM fnd_resp_functions frf
75    WHERE frf.action_id = fff.function_id
76    AND frf.rule_type = 'F'
77    AND frf.application_id = 222
78    AND frf.responsibility_id = frv.responsibility_id)
79 AND curr_date BETWEEN nvl(urg.start_date,   curr_date)
80  AND nvl(urg.end_date,   curr_date)
81  AND urg.user_id = user_id_bind
82  AND urg.responsibility_id = frv.responsibility_id
83  AND frv.application_id = 222;
84 
85 CURSOR all_resp(user_id_bind NUMBER) IS
86 SELECT frv.responsibility_id
87 FROM fnd_responsibility frv,
88   fnd_compiled_menu_functions fcmf,
89   fnd_form_functions fff,
90   fnd_user_resp_groups_direct urg
91 WHERE fff.function_name = 'AR_CASH_APPLN_WORK_QUEUE'
92  AND fcmf.function_id = fff.function_id
93  AND frv.menu_id = fcmf.menu_id
94  AND urg.user_id = user_id_bind
95  AND urg.responsibility_id = frv.responsibility_id
96  AND frv.application_id = 222;
97 
98 
99 curr_date date;
100 
101 valid_resp_list valid_resp % rowtype;
102 all_resp_list all_resp % rowtype;
103 
104 
105 
106 BEGIN
107 
108   IF valid_flag = 'Y' THEN
109 
110     curr_date := TRUNC(sysdate);
111 
112     OPEN valid_resp(user_id, curr_date);
113     FETCH valid_resp
114     INTO valid_resp_list;
115 
116     IF valid_resp % FOUND THEN
117       CLOSE valid_resp;
118       RETURN 1;
119     ELSE
120       CLOSE valid_resp;
121       RETURN 0;
122     END IF;
123 
124   ELSE
125 
126     OPEN all_resp(user_id);
127     FETCH all_resp
128     INTO all_resp_list;
129 
130     IF all_resp % FOUND THEN
131       CLOSE all_resp;
132       RETURN 1;
133     ELSE
134       CLOSE all_resp;
135       RETURN 0;
136     END IF;
137 
138   END IF;
139 
140   EXCEPTION
141 
142     WHEN OTHERS THEN
143       RETURN 0;
144 
145 END;
146 
147 
148 PROCEDURE assign_work_items( errbuf                OUT NOCOPY VARCHAR2,
149                              retcode               OUT NOCOPY NUMBER,
150                              p_operating_unit              IN NUMBER,
151                              p_receipt_date_from   IN VARCHAR2,
152                              p_receipt_date_to     IN VARCHAR2,
153                              p_cust_prof_class     IN NUMBER,
154                              p_max_num_workers     IN NUMBER,
155                              p_worker_no           IN NUMBER) IS
156 
157 TYPE c_receipts  IS REF CURSOR ;
158 C_receipts_cur c_receipts;
159 C_receipt_stmt VARCHAR2(5000);
160 
161 
162 l_max_num_workers NUMBER;
163 l_worker_no NUMBER;
164 
165 TYPE L_receipts_type IS RECORD
166     (cash_receipt_id    NUMBER(15),
167       cust_account_id        NUMBER(15),
168       site_use_id         NUMBER(15),
169       profile_class_id  NUMBER(15),
170       country     hz_locations.country%TYPE,
171       org_id          NUMBER(15),
172       currency_code  ar_cash_receipts_all.currency_code%TYPE,
173       unidentified_amount NUMBER,
174       unapplied_amount  NUMBER,
175       ATTRIBUTE1 VARCHAR2(50),
176       ATTRIBUTE2 VARCHAR2(50),
177       ATTRIBUTE3 VARCHAR2(50),
178       ATTRIBUTE4 VARCHAR2(50),
179       ATTRIBUTE5 VARCHAR2(50),
180       ATTRIBUTE6 VARCHAR2(50),
181       ATTRIBUTE7 VARCHAR2(50),
182       ATTRIBUTE8 VARCHAR2(50),
183       ATTRIBUTE9 VARCHAR2(50),
184       ATTRIBUTE10 VARCHAR2(50));
185 
186 TYPE ReceiptsTabTyp IS TABLE OF L_receipts_type
187 INDEX BY BINARY_INTEGER;
188 
189 receipts_tab ReceiptsTabTyp;
190 
191 TYPE L_results_type IS RECORD
192       (cash_receipt_id     DBMS_SQL.NUMBER_TABLE,
193       cash_appln_owner_id  DBMS_SQL.NUMBER_TABLE);
194 
195 results_tab L_results_type;
196 
197 BEGIN
198 
199     write_debug_and_log('ar_cao_assign_pkg.assign_work_items (+)');
200 
201     mo_global.init('AR');
202     write_debug_and_log('Operating Unit : '|| p_operating_unit);
203     write_debug_and_log('Receipt Date From : '|| p_receipt_date_from);
204     write_debug_and_log('Receipt Date To : '|| p_receipt_date_to);
205     write_debug_and_log('Customer Profile Class : '|| p_cust_prof_class);
206     write_debug_and_log('Max Workers : '|| p_max_num_workers);
207     write_debug_and_log('Worker Number : '|| p_worker_no);
208 
209 
210     IF p_max_num_workers is null THEN
211       l_max_num_workers := 1;
212     ELSE
213       l_max_num_workers := p_max_num_workers;
214     END IF;
215 
216     IF p_worker_no is null THEN
217       l_worker_no := 0;
218     ELSE
219       l_worker_no := p_worker_no;
220     END IF;
221 
222     IF l_worker_no = 0 THEN
223       spawn_child_requests(p_operating_unit,
224                            p_receipt_date_from,
225                            p_receipt_date_to,
226                            p_cust_prof_class,
227                            l_max_num_workers);
228     END IF;
229 
230 
231     C_receipt_stmt := 'SELECT acr.cash_receipt_id, ' ||
232                           'hca.cust_account_id, ' ||
233                           'hcsu.site_use_id, '||
234                           'nvl(hcp1.profile_class_id,   hcp.profile_class_id) profile_class_id, '||
235                           'hl.country, '||
236                           'acr.org_id, '||
237                           'acr.currency_code, '||
238                           'decode(acr.status,   ''UNID'',   ABS(aps.amount_due_remaining),   0) unidentified_amount, '||
239                           'decode(acr.status,   ''UNAPP'',   ABS(aps.amount_due_remaining),   0) unapplied_amount, '||
240                           'null,null,null,null,null,null,null,null,null,null ' ||
241                           'FROM ar_cash_receipts acr, '||
242                           'hz_cust_accounts hca, '||
243                           'hz_cust_site_uses hcsu, '||
244                           'hz_locations hl, '||
245                           'hz_party_sites hps, '||
246                           'hz_cust_acct_sites hcas, '||
247                           'ar_payment_schedules aps, '||
248                           'hz_customer_profiles hcp, '||
249                           'hz_customer_profiles hcp1 '||
250                           'WHERE acr.pay_from_customer = hca.cust_account_id(+) '||
251                           'AND acr.customer_site_use_id = hcsu.site_use_id(+) '||
252                           'AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id(+) '||
253                           'AND hcas.party_site_id = hps.party_site_id(+) '||
254                           'AND hps.location_id = hl.location_id(+) '||
255                           'AND acr.cash_receipt_id = aps.cash_receipt_id '||
256                           'AND acr.type = ''CASH'' '||
257                           'AND acr.cash_appln_owner_id IS NULL '||
258                           'AND acr.status IN(''UNAPP'',   ''UNID'') '||
259                           'AND hca.cust_account_id = hcp.cust_account_id(+) '||
260                           'AND hcp.site_use_id IS NULL '||
261                           'AND hcsu.site_use_id = hcp1.site_use_id(+) '||
262                           'and mod(acr.cash_receipt_id,'|| l_max_num_workers ||
263                           ') = decode( ' ||l_max_num_workers ||
264                           ', 0, acr.cash_receipt_id, mod(' || l_worker_no|| ','|| l_max_num_workers || '))';
265 
266       IF p_operating_unit is not null THEN
267           C_receipt_stmt := C_receipt_stmt || ' AND acr.org_id = ' || p_operating_unit;
268       END IF;
269       IF p_receipt_date_from is not null THEN
270            C_receipt_stmt := C_receipt_stmt || ' AND acr.receipt_date >= trunc(to_date(''' || p_receipt_date_from || ''', ''YYYY/MM/DD HH24:MI:SS''))';
271       END IF;
272       IF p_receipt_date_to is not null THEN
273            C_receipt_stmt := C_receipt_stmt || ' AND acr.receipt_date <= trunc(to_date(''' || p_receipt_date_to || ''', ''YYYY/MM/DD HH24:MI:SS''))';
274       END IF;
275       IF p_cust_prof_class is not null THEN
276            C_receipt_stmt := C_receipt_stmt || ' AND (hcp1.profile_class_id = ' || p_cust_prof_class || ' OR hcp.profile_class_id =' || p_cust_prof_class || ')';
277       END IF;
278 
279       write_debug_and_log(' Query :' || C_receipt_stmt);
280 
281       OPEN C_receipts_cur for C_receipt_stmt;
282 
283       FETCH C_receipts_cur BULK COLLECT INTO receipts_tab;
284 
285       CLOSE C_receipts_cur;
286 
287       write_debug_and_log('Number of records: '|| receipts_tab.count);
288 
289       delete from AR_CASH_RECPT_RULE_PARAM_GT;
290 
291       FORALL i in receipts_tab.first..receipts_tab.last
292         INSERT INTO AR_CASH_RECPT_RULE_PARAM_GT values receipts_tab(i);
293 
294       AR_CUSTOM_PARAMS_HOOK_PKG.populateCAOwnerAttributes();
295       write_debug_and_log('Invoking Rule Engine');
296 
297 
298       /* Invoke Rule Engine */
299       FUN_RULE_PUB.apply_rule_bulk('AR',
300                                     'CASH_APPLICATION_OWNER_ASSIGN',
301                                     'AR_CASH_RECPT_RULE_PARAM_GT',
302                                     null,
303                                     'cash_receipt_id');
304 
305       write_debug_and_log('Returned from Rule Engine');
306 
307       SELECT ID, RESULT_VALUE BULK COLLECT INTO results_tab from FUN_RULE_BULK_RESULT_GT;
308 
309       /* Update the AR_Cash_Receipts table with the result values */
310 
311       FORALL i in results_tab.cash_receipt_id.first..results_tab.cash_receipt_id.last
312         UPDATE ar_cash_receipts_all
313         set CASH_APPLN_OWNER_ID = results_tab.cash_appln_owner_id(i),
314         WORK_ITEM_ASSIGNMENT_DATE = sysdate,
315         WORK_ITEM_STATUS_CODE = 'NEW'
316         where CASH_RECEIPT_ID = results_tab.cash_receipt_id(i)
317 	and results_tab.cash_appln_owner_id(i) is not null;
318 
319       write_debug_and_log('Receipts which are not assigned to any Cash Application Owner:');
320 
321       FOR i in 1..results_tab.cash_receipt_id.count LOOP
322       	IF  results_tab.cash_appln_owner_id(i) is null THEN
323 		 write_debug_and_log(results_tab.cash_receipt_id(i));
324 	END IF;
325       END LOOP;
326 
327       delete from FUN_RULE_BULK_RESULT_GT;
328 
329       write_debug_and_log('ar_cao_assign_pkg.assign_work_items (-)');
330 
331   EXCEPTION
332     WHEN OTHERS THEN
333       write_debug_and_log(sqlerrm);
334 
335 END;
336 
337 END ar_cao_assign_pkg;