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
2 /* $Header: ARCAOAB.pls 120.0.12010000.4 2008/11/21 15:36:56 rmanikan noship $*/
3 
4 PROCEDURE write_debug_and_log(p_message IN VARCHAR2) IS
5 
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 
176 TYPE ReceiptsTabTyp IS TABLE OF L_receipts_type
177 INDEX BY BINARY_INTEGER;
178 
179 receipts_tab ReceiptsTabTyp;
180 
181 TYPE L_results_type IS RECORD
182       (cash_receipt_id     DBMS_SQL.NUMBER_TABLE,
183       cash_appln_owner_id  DBMS_SQL.NUMBER_TABLE);
184 
185 results_tab L_results_type;
186 
187 BEGIN
188 
189     write_debug_and_log('ar_cao_assign_pkg.assign_work_items (+)');
190 
191     mo_global.init('AR');
192     write_debug_and_log('Operating Unit : '|| p_operating_unit);
193     write_debug_and_log('Receipt Date From : '|| p_receipt_date_from);
194     write_debug_and_log('Receipt Date To : '|| p_receipt_date_to);
195     write_debug_and_log('Customer Profile Class : '|| p_cust_prof_class);
196     write_debug_and_log('Max Workers : '|| p_max_num_workers);
197     write_debug_and_log('Worker Number : '|| p_worker_no);
198 
199 
200     IF p_max_num_workers is null THEN
201       l_max_num_workers := 1;
202     ELSE
203       l_max_num_workers := p_max_num_workers;
204     END IF;
205 
206     IF p_worker_no is null THEN
207       l_worker_no := 0;
208     ELSE
209       l_worker_no := p_worker_no;
210     END IF;
211 
212     IF l_worker_no = 0 THEN
213       spawn_child_requests(p_operating_unit,
214                            p_receipt_date_from,
215                            p_receipt_date_to,
216                            p_cust_prof_class,
217                            l_max_num_workers);
218     END IF;
219 
220 
221     C_receipt_stmt := 'SELECT acr.cash_receipt_id, ' ||
222                           'hca.cust_account_id, ' ||
223                           'hcsu.site_use_id, '||
224                           'nvl(hcp1.profile_class_id,   hcp.profile_class_id) profile_class_id, '||
225                           'hl.country, '||
226                           'acr.org_id, '||
227                           'acr.currency_code, '||
228                           'decode(acr.status,   ''UNID'',   ABS(aps.amount_due_remaining),   0) unidentified_amount, '||
229                           'decode(acr.status,   ''UNAPP'',   ABS(aps.amount_due_remaining),   0) unapplied_amount '||
230                           'FROM ar_cash_receipts acr, '||
231                           'hz_cust_accounts hca, '||
232                           'hz_cust_site_uses hcsu, '||
233                           'hz_locations hl, '||
234                           'hz_party_sites hps, '||
235                           'hz_cust_acct_sites hcas, '||
236                           'ar_payment_schedules aps, '||
237                           'hz_customer_profiles hcp, '||
238                           'hz_customer_profiles hcp1 '||
239                           'WHERE acr.pay_from_customer = hca.cust_account_id(+) '||
240                           'AND acr.customer_site_use_id = hcsu.site_use_id(+) '||
241                           'AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id(+) '||
242                           'AND hcas.party_site_id = hps.party_site_id(+) '||
243                           'AND hps.location_id = hl.location_id(+) '||
244                           'AND acr.cash_receipt_id = aps.cash_receipt_id '||
245                           'AND acr.type = ''CASH'' '||
246                           'AND acr.cash_appln_owner_id IS NULL '||
247                           'AND acr.status IN(''UNAPP'',   ''UNID'') '||
248                           'AND hca.cust_account_id = hcp.cust_account_id(+) '||
249                           'AND hcp.site_use_id IS NULL '||
250                           'AND hcsu.site_use_id = hcp1.site_use_id(+) '||
251                           'and mod(acr.cash_receipt_id,'|| l_max_num_workers ||
252                           ') = decode( ' ||l_max_num_workers ||
253                           ', 0, acr.cash_receipt_id, mod(' || l_worker_no|| ','|| l_max_num_workers || '))';
254 
255       IF p_operating_unit is not null THEN
256           C_receipt_stmt := C_receipt_stmt || ' AND acr.org_id = ' || p_operating_unit;
257       END IF;
258       IF p_receipt_date_from is not null THEN
259            C_receipt_stmt := C_receipt_stmt || ' AND acr.receipt_date >= trunc(to_date(''' || p_receipt_date_from || ''', ''YYYY/MM/DD HH24:MI:SS''))';
260       END IF;
261       IF p_receipt_date_to is not null THEN
262            C_receipt_stmt := C_receipt_stmt || ' AND acr.receipt_date <= trunc(to_date(''' || p_receipt_date_to || ''', ''YYYY/MM/DD HH24:MI:SS''))';
263       END IF;
264       IF p_cust_prof_class is not null THEN
265            C_receipt_stmt := C_receipt_stmt || ' AND (hcp1.profile_class_id = ' || p_cust_prof_class || ' OR hcp.profile_class_id =' || p_cust_prof_class || ')';
266       END IF;
267 
268       write_debug_and_log(' Query :' || C_receipt_stmt);
269 
270       OPEN C_receipts_cur for C_receipt_stmt;
271 
272       FETCH C_receipts_cur BULK COLLECT INTO receipts_tab;
273 
274       CLOSE C_receipts_cur;
275 
276       write_debug_and_log('Number of records: '|| receipts_tab.count);
277 
278       delete from AR_CASH_RECPT_RULE_PARAM_GT;
279 
280       FORALL i in receipts_tab.first..receipts_tab.last
281         INSERT INTO AR_CASH_RECPT_RULE_PARAM_GT values receipts_tab(i);
282 
283       write_debug_and_log('Invoking Rule Engine');
284 
285       /* Invoke Rule Engine */
286       FUN_RULE_PUB.apply_rule_bulk('AR',
287                                     'CASH_APPLICATION_OWNER_ASSIGN',
288                                     'AR_CASH_RECPT_RULE_PARAM_GT',
289                                     null,
290                                     'cash_receipt_id');
291 
292       write_debug_and_log('Returned from Rule Engine');
293 
294       SELECT ID, RESULT_VALUE BULK COLLECT INTO results_tab from FUN_RULE_BULK_RESULT_GT;
295 
296       /* Update the AR_Cash_Receipts table with the result values */
297 
298       FORALL i in results_tab.cash_receipt_id.first..results_tab.cash_receipt_id.last
299         UPDATE ar_cash_receipts_all
300         set CASH_APPLN_OWNER_ID = results_tab.cash_appln_owner_id(i),
301         WORK_ITEM_ASSIGNMENT_DATE = sysdate,
302         WORK_ITEM_STATUS_CODE = 'NEW'
303         where CASH_RECEIPT_ID = results_tab.cash_receipt_id(i);
304 
305       delete from FUN_RULE_BULK_RESULT_GT;
306 
307       write_debug_and_log('ar_cao_assign_pkg.assign_work_items (-)');
308 
309   EXCEPTION
310     WHEN OTHERS THEN
311       write_debug_and_log(sqlerrm);
312 
313 END;
314 
315 END ar_cao_assign_pkg;