[Home] [Help]
PACKAGE BODY: APPS.AMW_LOAD_KEY_ACC_DATA
Source
1 PACKAGE BODY AMW_LOAD_KEY_ACC_DATA AS
2 /* $Header: amwkaccb.pls 120.0.12000000.3 2007/04/13 00:00:17 npanandi ship $ */
3 /*****************************************************************************/
4 /*****************************************************************************/
5 /*****************************************************************************/
6 /* Major Functionality of the followIng procedure includes: */
7 /* Reads the amw_risk-ctrl_interface table */
8 /* following tables: */
9 /* INSERTS OR UPDATES ARE DONE AGAINIST THE FOLLOWING TABLES */
10 /* Insert into AMW_RISKS_B and AMW_RISKS_TL */
11 /* Insert into AMW_CONTROLS_B and AMW_CONTROLS_TL */
12 /* Insert into AMW_CONTROL_ASSOCIATIONS */
13 /* Insert into AMW_RISK_ASSOCIATIONS */
14 /* Insert into AMW_CONTROL_OBJECTIVES */
15 /* Insert into AMW_CONTROL_ASSERTIONS */
16 /* Updates amw_risk-ctrl_interface, with error messages */
17 /* Deleting successful production inserts, based on profile */
18 /* */
19 /*****************************************************************************/
20 --
21 -- Used for exception processing
22 --
23
24 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
25 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
26 v_error_found BOOLEAN DEFAULT FALSE;
27 v_user_id NUMBER;
28 v_interface_id NUMBER;
29 vx_control_rev_id NUMBER;
30 v_err_msg VARCHAR2 (2000);
31 v_table_name VARCHAR2 (240);
32
33 ---02.03.2005 npanandi: increased Varchar2 length of v_import_func
34 ---from 30 to 480 per FND mandate
35 v_import_func CONSTANT VARCHAR2(480) := 'AMW_DATA_IMPORT';
36
37 v_control_db_approval_status VARCHAR2(30);
38
39 v_invalid_requestor_msg VARCHAR2(2000);
40 v_no_import_privilege_msg VARCHAR2(2000);
41 v_control_pending_msg VARCHAR2(2000);
42 --
43 -- function to check the user access privilege
44 --
45 FUNCTION Has_Import_Privilege RETURN Boolean
46 IS
47 CURSOR c_func_exists IS
48 SELECT 'Y'
49 FROM fnd_responsibility r, fnd_compiled_menu_functions m, fnd_form_functions f
50 WHERE r.responsibility_id = fnd_global.resp_id
51 AND r.application_id=fnd_global.resp_appl_id
52 AND r.menu_id = m.menu_id
53 AND m.function_id = f.function_id
54 AND f.function_name = v_import_func;
55 CURSOR c_func_excluded IS
56 SELECT 'Y'
57 FROM fnd_resp_functions rf, fnd_form_functions f
58 WHERE rf.application_id = fnd_global.resp_appl_id
59 AND rf.responsibility_id = fnd_global.resp_id
60 AND rf.rule_type = 'F'
61 AND rf.action_id = f.function_id
62 AND f.function_name = v_import_func;
63
64 l_func_exists VARCHAR2(1);
65 l_func_excluded VARCHAR2(1);
66 BEGIN
67 OPEN c_func_exists;
68 FETCH c_func_exists INTO l_func_exists;
69 IF c_func_exists%NOTFOUND THEN
70 CLOSE c_func_exists;
71 return FALSE;
72 END IF;
73 CLOSE c_func_exists;
74
75 OPEN c_func_excluded;
76 FETCH c_func_excluded INTO l_func_excluded;
77 CLOSE c_func_excluded;
78
79 IF l_func_excluded is not null THEN
80 return FALSE;
81 END IF;
82
83 return TRUE;
84 END Has_Import_Privilege;
85
86
87 /*****************************************************************************/
88 /*****************************************************************************/
89 PROCEDURE create_key_acc_assoc (
90 errbuf OUT NOCOPY VARCHAR2
91 ,retcode OUT NOCOPY VARCHAR2
92 ,p_batch_id IN NUMBER
93 ,p_user_id IN NUMBER
94 )
95 IS
96 /****************************************************/
97 CURSOR key_acc_cur
98 IS
99 SELECT key_acc_interface_id
100 ,process_id
101 ,financial_statement_id
102 ,financial_item_id
103 ,to_number(natural_account_id) as natural_account_id
104 ,natural_account_acc_id
105 ,processed_flag
106 ,error_flag
107 ,interface_status
108 FROM amw_key_acc_interface
109 WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
110 AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
111 AND processed_flag IS NULL
112 AND error_flag IS NULL;
113
114 CURSOR c_requestor_id IS
115 SELECT party_id
116 FROM amw_employees_current_v
117 WHERE employee_id = (select employee_id
118 from fnd_user
119 where user_id = p_user_id)
120 AND rownum = 1;
121
122 cursor c_key_acc_exists(p_process_id in number, p_natural_account_id in number) is
123 select acct_assoc_id
124 from amw_acct_associations
125 where object_type = 'PROCESS'
126 and pk1 = p_process_id
127 and natural_account_id = p_natural_account_id;
128
129 l_process_id NUMBER;
130 l_natural_account_id NUMBER;
131 l_natural_account_acc_id varchar2(100);
132 l_natural_account_value varchar2(100);
133 l_api_version_number CONSTANT NUMBER := 1.0;
134 l_requestor_id NUMBER;
135 l_amw_delt_key_acc_intf VARCHAR2 (2);
136
137 lx_return_status VARCHAR2(30);
138 lx_msg_count NUMBER;
139 lx_msg_data VARCHAR2(2000);
140 lx_acct_assoc_id NUMBER;
141 l_object_type_count NUMBER;
142 l_process_flag VARCHAR2(1);
143 e_no_import_access EXCEPTION;
144 e_invalid_requestor_id EXCEPTION;
145
146 L_ACCT_ASSOCIATION_ID number;
147 L_APPROVAL_DATE date;
148
149 l_startpos number;
150 l_length number;
151 l_valid_acc number;
152 l_body varchar2(4000);
153 l_prb_counts number;
154
155 l_has_proc_acct_assoc_access varchar2(15) := 'T'; --defaulting to 'T', which means 'has access'
156 BEGIN
157 fnd_file.put_line (fnd_file.LOG, 'resp id: '||fnd_global.RESP_ID);
158 fnd_file.put_line (fnd_file.LOG, 'resp appl id: '||fnd_global.RESP_APPL_ID);
159
160 --
161 -- check access privilege
162 --
163
164 IF not Has_Import_Privilege THEN
165 RAISE e_no_import_access;
166 END IF;
167
168 --
169 -- get user requestor_id
170 --
171 v_user_id := p_user_id;
172
173 OPEN c_requestor_id;
174 FETCH c_requestor_id INTO l_requestor_id;
175 IF (c_requestor_id%NOTFOUND) THEN
176 CLOSE c_requestor_id;
177 RAISE e_invalid_requestor_id;
178 END IF;
179 CLOSE c_requestor_id;
180
181 --
182 -- get profile info for deleting records from interface table
183 --
184 l_amw_delt_key_acc_intf := nvl(fnd_profile.VALUE ('AMW_DELT_KEY_ACC_INTF'), 'N');
185
186 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_PENDING_CHANGE_ERROR',
187 p_token_name => 'OBJ_TYPE',
188 p_token_value => AMW_UTILITY_PVT.GET_LOOKUP_MEANING('AMW_OBJECT_TYPE','CTRL'));
189 v_control_pending_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_LAST,p_encoded => fnd_api.g_false);
190
191 --
192 -- loop processing each record
193 --
194 FOR kacc_rec IN key_acc_cur LOOP
195 v_interface_id := kacc_rec.key_acc_interface_id;
196 l_process_id := kacc_rec.process_id;
197 l_natural_account_id := kacc_rec.natural_account_id;
198 lx_acct_assoc_id := null;
199
200 /**03.01.2007 npanandi: bug 5457611 fix -- parse the l_natural_account_acc_id
201 to get natural_account_id**/
202 /*04.12.2007 npanandi: this is the scenario for existing
203 problematic spreadsheets; multiple records with same
204 NaturalAccountName exist, and the NaturalAccountAccId column
205 is NULL*/
206 if(kacc_rec.natural_account_acc_id is null) then
207 l_prb_counts := 0;
208 select count(distinct natural_account_value)
209 into l_prb_counts
210 from amw_fin_key_accounts_vl
211 where end_date is null
212 and account_name in (select distinct afkav.account_name
213 from amw_fin_key_accounts_vl afkav
214 where afkav.natural_account_id=kacc_rec.natural_account_id
215 and afkav.end_date is null);
216 if(l_prb_counts > 1) then
217 fnd_message.set_name('AMW', 'AMW_WEBADI_VALID_ERROR');
218 fnd_message.set_token('ITEM', 'NATURAL_ACCOUNT_ID');
219 V_ERR_MSG := fnd_message.get;
220 UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
221 end if;
222 end if;
223 /*04.12.2007 npanandi: ends fix for NULL columns*/
224
225 if(kacc_rec.natural_account_acc_id is not null) then
226 ---parse the new column, only if it is not blank!!
227 l_natural_account_acc_id := kacc_rec.natural_account_acc_id;
228
229 /*compute NaturalAccountValue*/
230 l_natural_account_value := substr(l_natural_account_acc_id,1,(instr(l_natural_account_acc_id,'-',1,1)-1));
231 /*computed NaturalAccountValue*/
232
233 /*compute NaturalAccountId*/
234 l_startpos := instr(l_natural_account_acc_id,'-',1,2)+1; ---starting from the 2nd '-'
235 if(instr(l_natural_account_acc_id,'-',1,3) = 0)then --no parentNaturalAccountId
236 l_length := length(l_natural_account_acc_id) - (l_startpos-1);
237 else --parentNaturalAccountId exists
238 l_length := (instr(l_natural_account_acc_id,'-',1,3)) - l_startpos;
239 end if;
240 l_natural_account_id := substr(l_natural_account_acc_id,l_startpos,l_length);
241 /*computed NaturalAccountId*/
242
243 begin
244 select 1 into l_valid_acc from dual
245 where exists (select * from amw_fin_key_accounts_vl
246 where natural_account_id=l_natural_account_id
247 and natural_account_value=l_natural_account_value
248 and end_date is null);
249 exception
250 when others then
251 fnd_message.set_name('AMW', 'AMW_WEBADI_VALID_ERROR');
252 fnd_message.set_token('ITEM', 'NATURAL_ACCOUNT_ID');
253 V_ERR_MSG := fnd_message.get;
254 UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
255 end;
256 end if;
257 /**03.01.2007 npanandi: bug 5457611 fix ends**/
258
259
260 --NPANANDI 12.08.2004: CALLING THIS PROCEDURE TO REVISE PROCESS, PER CHANGED
261 --PROCESS REVISION DATAMODEL
262 AMW_RL_HIERARCHY_PKG.revise_process_if_necessary(
263 P_PROCESS_ID => L_PROCESS_ID
264 );
265
266 --SET THESE VALUES TO NULL FOR THIS LOOP
267 L_ACCT_ASSOCIATION_ID := NULL;
268 L_APPROVAL_DATE := NULL;
269 BEGIN
270 SELECT ACCT_ASSOC_ID, APPROVAL_DATE
271 INTO L_ACCT_ASSOCIATION_ID,L_APPROVAL_DATE
272 FROM AMW_ACCT_ASSOCIATIONS
273 WHERE OBJECT_TYPE='PROCESS'
274 AND PK1=l_process_id
275 AND NATURAL_ACCOUNT_ID=l_natural_account_id
276 AND DELETION_DATE IS NULL;
277 EXCEPTION
278 WHEN NO_DATA_FOUND THEN
279 NULL;
280 END;
281
282 fnd_file.put_line (fnd_file.LOG, 'l_process_id '||l_process_id);
283 fnd_file.put_line (fnd_file.LOG, 'l_natural_account_id '||l_natural_account_id);
284 fnd_file.put_line (fnd_file.LOG, 'lx_acct_assoc_id '||lx_acct_assoc_id);
285
286 ---03.02.2005 npanandi: check here to see if this Process has AMW_UPDATE_AP_DETAILS
287 ---privilege to associate this Control to AP
288 l_has_proc_acct_assoc_access := 'T';
289 l_has_proc_acct_assoc_access := check_function(
290 p_function => 'AMW_UPD_RL_PROC_ACCT_ASSOC'
291 ,p_object_name => 'AMW_PROCESS_APPR_ETTY'
292 ,p_instance_pk1_value => l_PROCESS_ID
293 ,p_user_id => fnd_global.user_id);
294
295 fnd_file.put_line(fnd_file.log,'%%%%%%%%%%%%%%% l_has_proc_acct_assoc_access: '||l_has_proc_acct_assoc_access||' %%%%%%%%%%%%%%%');
296
297 if(l_has_proc_acct_assoc_access = 'T')then
298 IF (L_ACCT_ASSOCIATION_ID is null) THEN
299 --NO ROW RETRIEVED, SO ASSOCIATION DOESN'T EXIST YET
300 --CREATE AN ASSOCIATION, SET ASSOCIATION_CREATION_DATE=SYSDATE
301 CREATE_AMW_KEY_ACC_ASSOC(
302 L_natural_account_id
303 ,L_process_id
304 );
305 ELSE
306 --THIS MEANS THAT ASSOCIATION EXISTS, SO CHECK APPROVAL_DATE
307
308 --IF APPROVAL_DATE IS NULL FOR OBJECTIVE_ASSOCIATIONS,
309 --THIS MEANS THAT THIS ASSOCIATION
310 --IS LATEST_REVISION, SO SIMPLY UPDATE ASSOC ATTRIBUTES
311 IF(L_APPROVAL_DATE IS NOT NULL) THEN
312 --THIS MEANS THAT THIS IS LATEST REVISION FOR THIS ASSOCIATION
313 --AND IS APPROVED, SO
314 BEGIN
315 UPDATE AMW_ACCT_ASSOCIATIONS
316 SET DELETION_DATE=SYSDATE
317 ,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
318 ,LAST_UPDATE_DATE=SYSDATE
319 ,LAST_UPDATED_BY=G_USER_ID
320 ,LAST_UPDATE_LOGIN=G_LOGIN_ID
321 WHERE ACCT_ASSOC_ID=L_ACCT_ASSOCIATION_ID;
322
323 -- ...AND CREATE A NEW ROW FOR THE LATEST ASSOCIATION
324 CREATE_AMW_KEY_ACC_ASSOC(
325 L_natural_account_id
326 ,L_process_id
327 );
328 EXCEPTION
329 WHEN OTHERS THEN
330 V_ERR_MSG := 'INTERFACE_ID := '||v_interface_id||' '||SUBSTR (SQLERRM, 1, 200);
331 UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
332 END;
333 END IF;
334 END IF; -- end of IF lx_acct_assoc_id is null OR...
335 end if; ---end of if for l_has_proc_acct_assoc_access check
336 END LOOP;
337
338 --
339 -- check profile option for (deletion of interface record, when the value is 'N', otherwise
340 -- set processed flag to 'Y', and update record
341 --
342 IF v_error_found THEN
343 ROLLBACK;
344 l_process_flag := NULL;
345 ELSE
346 l_process_flag := 'Y';
347 END IF;
348
349 IF UPPER (l_amw_delt_key_acc_intf) <> 'Y' THEN
350 BEGIN
351 UPDATE amw_key_acc_interface
352 SET processed_flag = l_process_flag
353 ,last_update_date = SYSDATE
354 ,last_updated_by = v_user_id
355 WHERE batch_id = p_batch_id;
356 EXCEPTION
357 WHEN OTHERS THEN
358 fnd_file.put_line (fnd_file.LOG,'err in update process flag: '||SUBSTR (SQLERRM, 1, 200));
359 END;
360 ELSE
361 IF NOT v_error_found THEN
362 BEGIN
363 DELETE FROM amw_key_acc_interface WHERE batch_id = p_batch_id;
364 EXCEPTION
365 WHEN OTHERS THEN
369 END IF;
366 fnd_file.put_line (fnd_file.LOG,'err in delete interface records: '||SUBSTR (SQLERRM, 1, 200));
367 END;
368 END IF;
370 EXCEPTION
371
372 WHEN e_invalid_requestor_id THEN
373 fnd_file.put_line (fnd_file.LOG
374 , 'Invalid requestor id.');
375
376 BEGIN
377 IF v_invalid_requestor_msg is null THEN
378 v_invalid_requestor_msg := FND_MESSAGE.GET_STRING('AMW', 'AMW_UNKNOWN_EMPLOYEE');
379 END IF;
380 UPDATE amw_key_acc_interface
381 SET error_flag = 'Y'
382 ,interface_status = v_invalid_requestor_msg
383 WHERE batch_id = p_batch_id;
384 EXCEPTION
385 WHEN OTHERS THEN
386 fnd_file.put_line (fnd_file.LOG
387 , 'unexpected exception in handling e_invalid_requestor_id: '||sqlerrm);
388 END;
389 WHEN e_no_import_access THEN
390 fnd_file.put_line (fnd_file.LOG
391 , 'no import privilege');
392
393 BEGIN
394 IF v_no_import_privilege_msg is null THEN
395 v_no_import_privilege_msg := FND_MESSAGE.GET_STRING('AMW', 'AMW_NO_IMPORT_ACCESS');
396 END IF;
397 UPDATE amw_key_acc_interface
398 SET error_flag = 'Y'
399 ,interface_status = v_no_import_privilege_msg
400 WHERE batch_id = p_batch_id;
401 EXCEPTION
402 WHEN OTHERS THEN
403 fnd_file.put_line (fnd_file.LOG
404 , 'unexpected exception in handling e_no_import_access: '||sqlerrm);
405 END;
406 WHEN others THEN
407 rollback;
408 fnd_file.put_line (fnd_file.LOG, 'unexpected exception in create_controls: '||sqlerrm);
409 END create_key_acc_assoc;
410
411 PROCEDURE CREATE_AMW_KEY_ACC_ASSOC(
412 P_natural_account_id IN NUMBER
413 ,P_process_id IN NUMBER
414 )
415 IS
416
417 BEGIN
418 INSERT INTO amw_acct_associations(
419 acct_assoc_id
420 ,last_update_date
421 ,last_updated_by
422 ,creation_date
423 ,created_by
424 ,last_update_login
425 ,ASSOCIATION_CREATION_DATE
426 ,natural_account_id
427 ,pk1
428 ,object_type
429 ,object_version_number
430 )VALUES (
431 amw_acct_associations_s.NEXTVAL
432 ,SYSDATE
433 ,v_user_id
434 ,SYSDATE
435 ,v_user_id
436 ,G_LOGIN_ID
437 ,SYSDATE
438 ,P_natural_account_id
439 ,P_process_id
440 ,'PROCESS'
441 ,1
442 );
443 END CREATE_AMW_KEY_ACC_ASSOC;
444
445 ---
446 ---03.02.2005 npanandi: function to check access privilege for this Risk/Ctrl
447 ---
448 function check_function(
449 p_function in varchar2
450 ,p_object_name in varchar2
451 ,p_instance_pk1_value in number
452 ,p_instance_pk2_value in number
453 ,p_instance_pk3_value in number
454 ,p_instance_pk4_value in number
455 ,p_instance_pk5_value in number
456 ,p_user_id in number
457 ) return varchar2
458 is
459 cursor c_get_user_name is
460 select user_name from fnd_user where user_id=p_user_id;
461
462 l_has_access varchar2(15) := 'T';
463 l_user_name varchar2(100); ---fnd_user.user_name colLength = 100
464 l_security_switch VARCHAR2 (2);
465 begin
466 open c_get_user_name;
467 fetch c_get_user_name into l_user_name;
468 close c_get_user_name;
469
470 l_security_switch := NVL(fnd_profile.VALUE ('AMW_DATA_SECURITY_SWITCH'), 'N');
471
472 fnd_file.put_line (fnd_file.LOG, 'l_security_switch: '||l_security_switch);
473 if(l_security_switch = 'Y') then ---check for Upd prvlg only if Security mode is set on
474 l_has_access := fnd_data_security.check_function(
475 p_api_version => 1
476 ,p_function => p_function
477 ,p_object_name => p_object_name
478 ,p_instance_pk1_value => p_instance_pk1_value
479 ,p_user_name => l_user_name);
480 end if;
481 return l_has_access;
482 end;
483 ---03.02.2005 npanandi: end function to check access privilege
484
485
486 --
487 -- procedure update_interface_with_error
488 --
489 --
490 PROCEDURE update_interface_with_error (
491 p_err_msg IN VARCHAR2
492 ,p_table_name IN VARCHAR2
493 ,p_interface_id IN NUMBER
494 )
495 IS
496 l_interface_status amw_key_acc_interface.interface_status%TYPE;
497 BEGIN
498 ROLLBACK; -- rollback any inserts done during the current loop process
499 v_error_found := TRUE;
500
501 BEGIN
502 SELECT interface_status
503 INTO l_interface_status
504 FROM amw_key_acc_interface
505 WHERE key_acc_interface_id = p_interface_id;
506 EXCEPTION
507 WHEN OTHERS
508 THEN
509 v_err_msg :=
510 'interface_id: = '
511 || p_interface_id
512 || ' '
513 || SUBSTR (SQLERRM, 1, 100);
514 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
515 END;
516
517 BEGIN
518 UPDATE amw_key_acc_interface
519 SET interface_status =
520 l_interface_status
521 -- || 'Error Msg: '
525 || '**'
522 || p_err_msg
523 -- || ' Table Name: '
524 -- || p_table_name
526 ,error_flag = 'Y'
527 WHERE key_acc_interface_id = p_interface_id;
528
529 fnd_file.put_line (fnd_file.LOG, SUBSTR (l_interface_status, 1, 200));
530 COMMIT;
531 EXCEPTION
532 WHEN OTHERS
533 THEN
534 v_err_msg :=
535 'Error during package processing '
536 || ' interface_id: = '
537 || p_interface_id
538 || SUBSTR (SQLERRM, 1, 100);
539 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
540 END;
541
542 COMMIT;
543 END update_interface_with_error;
544
545 END amw_load_key_acc_data;