[Home] [Help]
PACKAGE BODY: APPS.AMW_IMPORT_STMNTS_ACCS_PKG
Source
1 PACKAGE BODY AMW_IMPORT_STMNTS_ACCS_PKG AS
2 /* $Header: amwacimb.pls 120.0.12000000.3 2007/03/29 23:15:59 rjohnson ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMW_IMPORT_STMNTS_ACCS_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15 --G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMW_IMPORT_STMNTS_ACCS_PKG';
16 --G_FILE_NAME CONSTANT VARCHAR2(12) := amwacimb.pls';
17 g_user_id NUMBER ;
18 g_login_id NUMBER ;
19 g_errbuf VARCHAR2(2000);
20 g_retcode VARCHAR2(2) ;
21 ------------------ *************************************** -----------------------
22 PROCEDURE import_accounts(errbuf OUT NOCOPY VARCHAR2,retcode OUT NOCOPY VARCHAR2)
23 is
24
25 begin
26 declare
27 m_fsg_or_not VARCHAR2(10);
28 m_message VARCHAR2(2000) := null;
29
30 begin
31 g_errbuf := null;
32 g_retcode := '0';
33 g_user_id := fnd_global.user_id;
34 g_login_id := fnd_global.conc_login_id;
35
36 select fnd_profile.value('AMW_FIN_IMPORT_FROM_FSG') into m_fsg_or_not from dual;
37
38 fnd_file.put_line(fnd_file.LOG,'AMW_FIN_IMPORT_FROM_FSG Profile Value' || m_fsg_or_not );
39
40 if (m_fsg_or_not is null or upper(trim(m_fsg_or_not)) = 'Y' or upper(trim(m_fsg_or_not)) = 'YES') then
41 IF check_account_value_set THEN
42 AMW_IMPORT_STMNTS_ACCS_PKG.get_acc_from_oracle_apps;
43 END IF;
44 ELSE
45 if AMW_IMPORT_STMNTS_ACCS_PKG.check_acc_profiles_has_value then
46 AMW_IMPORT_STMNTS_ACCS_PKG.get_acc_from_external_apps;
47
48 end if;
49 END IF;
50 end;
51
52 -- EXCEPTION WHEN OTHERS THEN
53 -- dbms_output.put_line(SQLERRM);
54 -- RAISE ;
55 -- RETURN;
56 errbuf := g_errbuf ;
57 retcode := g_retcode;
58 fnd_file.put_line(fnd_file.LOG,g_errbuf );
59
60
61 END import_accounts;
62 ------------------------ ************************************* --------------------------
63 PROCEDURE import_statements(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2, P_RUN_ID in NUMBER)
64 is
65
66 begin
67 declare
68 m_fsg_or_not VARCHAR2(10);
69 m_run_id number := P_RUN_ID;
70 begin
71 g_errbuf := null;
72 g_retcode := '0';
73 g_user_id := fnd_global.user_id;
74 g_login_id := fnd_global.conc_login_id;
75
76
77 select fnd_profile.value('AMW_FIN_IMPORT_FROM_FSG') into m_fsg_or_not from dual;
78 fnd_file.put_line(fnd_file.LOG,'AMW_FIN_IMPORT_FROM_FSG Profile Value' || m_fsg_or_not );
79
80 if (m_fsg_or_not is null or upper(trim(m_fsg_or_not)) = 'Y' or upper(trim(m_fsg_or_not)) = 'YES') then
81
82 if check_key_accounts_exists then
83 IF check_account_value_set THEN
84 AMW_IMPORT_STMNTS_ACCS_PKG.get_stmnts_from_oracle_apps(P_RUN_ID => m_run_id) ;
85 end if;
86 end if;
87
88 ELSE
89 if check_key_accounts_exists then
90 if check_stmnt_profiles_has_value then
91 AMW_IMPORT_STMNTS_ACCS_PKG.get_stmnts_from_external_apps(P_RUN_ID => m_run_id) ;
92 end if;
93
94 end if;
95 END IF;
96 end;
97 errbuf := g_errbuf ;
98 retcode := g_retcode;
99 fnd_file.put_line(fnd_file.LOG,g_errbuf );
100
101
102
103 END import_statements;
104 ------------------------ ************************************* ---------------------------
105 PROCEDURE get_stmnts_from_external_apps(P_RUN_ID in NUMBER) IS
106 begin
107 declare
108
109 m_run_id number := P_RUN_ID;
110
111 M_ACCOUNT_GROUP_ID NUMBER;
112 M_NATURAL_ACCOUNT_ID NUMBER;
113 M_PARENT_FINANCIAL_ITEM_ID NUMBER;
114 M_STATEMENT_GROUP_ID NUMBER;
115 M_FINANCIAL_STATEMENT_ID NUMBER;
116 M_END_DATE DATE;
117 M_LAST_UPDATE_DATE DATE;
118 M_LAST_UPDATED_BY NUMBER;
119 M_LAST_UPDATE_LOGIN NUMBER;
120 M_CREATION_DATE DATE;
121 M_CREATED_BY NUMBER;
122 M_ATTRIBUTE_CATEGORY VARCHAR2(30);
123 M_ATTRIBUTE1 VARCHAR2(150);
124 M_ATTRIBUTE2 VARCHAR2(150);
125 M_ATTRIBUTE3 VARCHAR2(150);
126 M_ATTRIBUTE4 VARCHAR2(150);
127 M_ATTRIBUTE5 VARCHAR2(150);
128 M_ATTRIBUTE6 VARCHAR2(150);
129 M_ATTRIBUTE7 VARCHAR2(150);
130 M_ATTRIBUTE8 VARCHAR2(150);
131 M_ATTRIBUTE9 VARCHAR2(150);
132 M_ATTRIBUTE10 VARCHAR2(150);
133 M_ATTRIBUTE11 VARCHAR2(150);
134 M_ATTRIBUTE12 VARCHAR2(150);
135 M_ATTRIBUTE13 VARCHAR2(150);
136 M_ATTRIBUTE14 VARCHAR2(150);
137 M_ATTRIBUTE15 VARCHAR2(150);
138 M_SECURITY_GROUP_ID NUMBER;
139 M_OBJECT_VERSION_NUMBER NUMBER;
140 M_FINANCIAL_ITEM_ID NUMBER;
141 M_SEQUENCE_NUMBER NUMBER;
142
143 M_NAME VARCHAR2(240);
144 M_LANGUAGE VARCHAR2(4);
145 M_SOURCE_LANGUAGE VARCHAR2(4);
146 M_OBJECT_TYPE VARCHAR2(10);
147 M_ORIG_SYSTEM_REFERENCE VARCHAR2(150);
148
149 --- Section of code containg declaration of dynamic cursor based on profile for importing statemnts from external applications
150 sql_for_stmnt varchar2(2000):=
151 'SELECT
152 FINANCIAL_STATEMENT_ID
153 from ' || fnd_profile.value('AMW_STMNT_SOURCE_VIEW')
154 || ' where FINANCIAL_STATEMENT_ID in (select FINANCIAL_STATEMENT_ID from AMW_FIN_STMNT_SELECTION where run_id =' || to_char(P_RUN_ID) || ')';
155
156 TYPE statements_b IS RECORD (
157 FINANCIAL_STATEMENT_ID NUMBER);
158 statements_b_record statements_b ;
159
160 TYPE get_stmnt_cursor IS ref CURSOR ;
161 Get_stmnt_from_external_apps get_stmnt_cursor ;
162
163 --- Section of code containg declaration of dynamic cursor based on profile for importing financial iterms from external applications
164
165 sql_for_stmnt_item varchar2(2000):=
166 'SELECT
167 FINANCIAL_STATEMENT_ID,
168 FINANCIAL_ITEM_ID,
169 PARENT_FINANCIAL_ITEM_ID,
170 SEQUENCE_NUMBER
171 from ' || fnd_profile.value('AMW_FINITEM_SOURCE_VIEW')
172 || ' where FINANCIAL_STATEMENT_ID in (select FINANCIAL_STATEMENT_ID from AMW_FIN_STMNT_SELECTION where run_id ='|| to_char(P_RUN_ID) || ')';
173
174
175 TYPE statement_item_b IS RECORD (
176 FINANCIAL_STATEMENT_ID NUMBER,
177 FINANCIAL_ITEM_ID NUMBER,
178 PARENT_FINANCIAL_ITEM_ID NUMBER,
179 SEQUENCE_NUMBER NUMBER);
180 statement_item_b_record statement_item_b;
181
182
183 TYPE get_stmnt_item_cursor IS ref CURSOR ;
184 Get_finitem_from_external_apps get_stmnt_item_cursor ;
185
186 --- Section of code containg declaration of dynamic cursor based on profile for importing financial iterms and account relation from external applications
187
188 sql_for_acc_item varchar2(2000):=
189 'SELECT
190 FINANCIAL_STATEMENT_ID,
191 FINANCIAL_ITEM_ID,
192 NATURAL_ACCOUNT_ID
193 from ' || fnd_profile.value('AMW_FIN_ITEM_ACC_RELATIONS_VIEW');
194
195 TYPE account_item_map_b IS RECORD (
196 FINANCIAL_STATEMENT_ID NUMBER,
197 FINANCIAL_ITEM_ID NUMBER,
198 NATURAL_ACCOUNT_ID NUMBER);
199 account_item_map_b_record account_item_map_b ;
200
201 TYPE get_acc_item_cursor IS ref CURSOR ;
202 Get_accitem_from_external_apps get_acc_item_cursor ;
203
204 --- Section of code containg declaration of dynamic cursor based on profile for importing statement names/desc. from external applications
205
206 sql_for_stmnt_name varchar2(2000):=
207 'SELECT
208 FINANCIAL_STATEMENT_ID,
209 NAME ,
210 LANGUAGE ,
211 SOURCE_LANGUAGE
212 from ' || fnd_profile.value('AMW_STMNT_SOURCE_TL_VIEW');
213
214 TYPE statement_tl IS RECORD (
215 FINANCIAL_STATEMENT_ID NUMBER,
216 NAME VARCHAR2(80),
217 LANGUAGE VARCHAR2(4),
218 SOURCE_LANGUAGE VARCHAR2(4) );
219
220 statement_tl_record statement_tl ;
221
222 TYPE get_stmntName_cursor IS ref CURSOR ;
223 Get_stmnt_names_external_apps get_stmntName_cursor ;
224
225 --- Section of code containg declaration of dynamic cursor based on profile for importing statement items names/desc. from external applications
226
227 sql_for_finitem_name varchar2(2000):=
228 'SELECT
229 FINANCIAL_STATEMENT_ID,
230 FINANCIAL_ITEM_ID,
231 NAME ,
232 LANGUAGE ,
233 SOURCE_LANGUAGE
234 from ' || fnd_profile.value('AMW_FINITEM_SOURCE_TL_VIEW');
235
236 TYPE finitem_tl IS RECORD (
237 FINANCIAL_STATEMENT_ID NUMBER,
238 FINANCIAL_ITEM_ID NUMBER,
239 NAME VARCHAR2(80),
240 LANGUAGE VARCHAR2(4),
241 SOURCE_LANGUAGE VARCHAR2(4) );
242
243 finitem_tl_record finitem_tl ;
244
245 TYPE get_FinItemName_cursor IS ref CURSOR ;
246 Get_finitem_names_ext_apps get_FinItemName_cursor ;
247
248 ------------------ get account group id -----------------
249 cursor Get_acc_values_from_icm (P_NATURAL_ACCOUNT_ID number)
250 is
251 SELECT
252 distinct keyacc.ACCOUNT_GROUP_ID
253 --, keyacc.NATURAL_ACCOUNT_ID
254 --, NATURAL_ACCOUNT_VALUE
255 from
256 AMW_FIN_KEY_ACCOUNTS_B keyacc
257 WHERE keyacc.End_Date is null
258 And keyacc.NATURAL_ACCOUNT_ID = P_NATURAL_ACCOUNT_ID
259 and END_DATE is null
260 and ACCOUNT_GROUP_ID =
261 (select max(keyacc2.ACCOUNT_GROUP_ID) from AMW_FIN_KEY_ACCOUNTS_B keyacc2 where keyacc2.End_Date is null) ;
262
263
264 begin
265 -- AMW_IMPORT_STMNTS_ACCS_PKG.end_date_stmnts_before_import(P_RUNID => m_run_id) ;
266 select AMW_FIN_STMNT_S.nextval into M_STATEMENT_GROUP_ID from dual;
267
268 open Get_stmnt_from_external_apps for sql_for_stmnt ;
269 loop
270 fetch Get_stmnt_from_external_apps into statements_b_record ;
271 exit when Get_stmnt_from_external_apps%notfound;
272
273 M_FINANCIAL_STATEMENT_ID := statements_b_record.FINANCIAL_STATEMENT_ID;
274
275
276 fnd_file.put_line(fnd_file.LOG, 'Processing STATEMENT_GROUP_ID =' || M_STATEMENT_GROUP_ID);
277 fnd_file.put_line(fnd_file.LOG, 'Processing FINANCIAL_STATEMENT_ID =' || M_FINANCIAL_STATEMENT_ID );
278
279 AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_STMNT_ROW (
280 X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
281 X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID ,
282 X_END_DATE => NULL,
283 X_LAST_UPDATE_DATE => SYSDATE,
284 X_LAST_UPDATED_BY => g_user_id ,
285 X_LAST_UPDATE_LOGIN => g_login_id,
286 X_CREATION_DATE => SYSDATE,
287 X_CREATED_BY => g_user_id ,
288 X_ATTRIBUTE_CATEGORY => NULL,
289 X_ATTRIBUTE1 => NULL,
290 X_ATTRIBUTE2 => NULL,
291 X_ATTRIBUTE3 => NULL,
292 X_ATTRIBUTE4 => NULL,
293 X_ATTRIBUTE5 => NULL,
294 X_ATTRIBUTE6 => NULL,
295 X_ATTRIBUTE7 => NULL,
296 X_ATTRIBUTE8 => NULL,
297 X_ATTRIBUTE9 => NULL,
298 X_ATTRIBUTE10 => NULL,
299 X_ATTRIBUTE11 => NULL,
300 X_ATTRIBUTE12 => NULL,
301 X_ATTRIBUTE13 => NULL,
302 X_ATTRIBUTE14 => NULL,
303 X_ATTRIBUTE15 => NULL,
304 X_SECURITY_GROUP_ID => NULL,
305 X_OBJECT_VERSION_NUMBER => NULL);
306
307 end loop;
308 open Get_stmnt_names_external_apps for sql_for_stmnt_name ;
309 loop
310 fetch Get_stmnt_names_external_apps into statement_tl_record ;
311 exit when Get_stmnt_names_external_apps%notfound;
312
313 M_FINANCIAL_STATEMENT_ID := statement_tl_record.FINANCIAL_STATEMENT_ID;
314 M_NAME := statement_tl_record.NAME;
315 M_LANGUAGE := statement_tl_record.LANGUAGE ;
316 M_SOURCE_LANGUAGE := statement_tl_record.SOURCE_LANGUAGE ;
317
318 fnd_file.put_line(fnd_file.LOG, 'Processing TL Definition for STATEMENT_GROUP_ID =' || M_STATEMENT_GROUP_ID);
319 fnd_file.put_line(fnd_file.LOG, 'Processing TL Definition for FINANCIAL_STATEMENT_ID =' || M_FINANCIAL_STATEMENT_ID );
320 fnd_file.put_line(fnd_file.LOG, 'Processing TL Definition for FINANCIAL_STATEMENT_NAME =' || M_NAME );
321 fnd_file.put_line(fnd_file.LOG, 'Processing TL Definition for LANGUAGE =' || M_LANGUAGE );
322
323
324 AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_STMNT_ROW_TL(
325 X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
326 X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID ,
327 X_NAME => M_NAME,
328 X_LANGUAGE => M_LANGUAGE,
329 X_SOURCE_LANGUAGE => M_SOURCE_LANGUAGE ,
330 -- X_OBJECT_TYPE ,
331 X_SECURITY_GROUP_ID => M_SECURITY_GROUP_ID ,
332 X_OBJECT_VERSION_NUMBER => Null,
333 X_ORIG_SYSTEM_REFERENCE => Null ,
334 X_LAST_UPDATE_DATE => SYSDATE,
335 X_LAST_UPDATED_BY => g_user_id ,
336 X_LAST_UPDATE_LOGIN => g_login_id,
337 X_CREATION_DATE => SYSDATE,
338 X_CREATED_BY => g_user_id);
339
340 end loop;
341
342 open Get_finitem_from_external_apps for sql_for_stmnt_item ;
343 loop
344 fetch Get_finitem_from_external_apps into statement_item_b_record ;
345 exit when Get_finitem_from_external_apps%notfound;
346 M_FINANCIAL_STATEMENT_ID := statement_item_b_record.FINANCIAL_STATEMENT_ID;
347 M_FINANCIAL_ITEM_ID := statement_item_b_record.FINANCIAL_ITEM_ID;
348 M_PARENT_FINANCIAL_ITEM_ID := statement_item_b_record.PARENT_FINANCIAL_ITEM_ID ;
349 M_SEQUENCE_NUMBER := statement_item_b_record.SEQUENCE_NUMBER;
350
351 fnd_file.put_line(fnd_file.LOG, 'Processing STATEMENT_GROUP_ID =' || M_STATEMENT_GROUP_ID);
352 fnd_file.put_line(fnd_file.LOG, 'Processing FINANCIAL_STATEMENT_ID =' || M_FINANCIAL_STATEMENT_ID );
353 fnd_file.put_line(fnd_file.LOG, 'Processing FINANCIAL_ITEM_ID =' || M_FINANCIAL_ITEM_ID );
354 fnd_file.put_line(fnd_file.LOG, 'Processing PARENT_FINANCIAL_ITEM_ID =' || M_PARENT_FINANCIAL_ITEM_ID );
355
356
357
358 AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_FINITEM_ROW(
359 X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
360 X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID ,
361 X_FINANCIAL_ITEM_ID => M_FINANCIAL_ITEM_ID ,
362 X_PARENT_FINANCIAL_ITEM_ID => M_PARENT_FINANCIAL_ITEM_ID ,
363 X_SEQUENCE_NUMBER => M_SEQUENCE_NUMBER ,
364 X_LAST_UPDATE_DATE => SYSDATE,
365 X_LAST_UPDATED_BY => g_user_id ,
366 X_LAST_UPDATE_LOGIN => g_login_id,
367 X_CREATION_DATE => SYSDATE,
368 X_CREATED_BY => g_user_id ,
369 X_ATTRIBUTE_CATEGORY => NULL,
370 X_ATTRIBUTE1 => NULL,
371 X_ATTRIBUTE2 => NULL,
372 X_ATTRIBUTE3 => NULL,
373 X_ATTRIBUTE4 => NULL,
374 X_ATTRIBUTE5 => NULL,
375 X_ATTRIBUTE6 => NULL,
376 X_ATTRIBUTE7 => NULL,
377 X_ATTRIBUTE8 => NULL,
378 X_ATTRIBUTE9 => NULL,
379 X_ATTRIBUTE10 => NULL,
380 X_ATTRIBUTE11 => NULL,
381 X_ATTRIBUTE12 => NULL,
382 X_ATTRIBUTE13 => NULL,
383 X_ATTRIBUTE14 => NULL,
384 X_ATTRIBUTE15 => NULL,
385 X_SECURITY_GROUP_ID => NULL,
386 X_OBJECT_VERSION_NUMBER => NULL);
387
388
389 end loop;
390 open Get_finitem_names_ext_apps for sql_for_finitem_name;
391 loop
392 fetch Get_finitem_names_ext_apps into finitem_tl_record ;
393 exit when Get_finitem_names_ext_apps%notfound;
394
395 M_FINANCIAL_STATEMENT_ID := finitem_tl_record.FINANCIAL_STATEMENT_ID;
396 M_FINANCIAL_ITEM_ID := finitem_tl_record.FINANCIAL_ITEM_ID;
397 M_NAME := finitem_tl_record.NAME;
398 M_LANGUAGE := finitem_tl_record.LANGUAGE ;
399 M_SOURCE_LANGUAGE := finitem_tl_record.SOURCE_LANGUAGE ;
400
401 fnd_file.put_line(fnd_file.LOG, 'Processing STATEMENT_GROUP_ID =' || M_STATEMENT_GROUP_ID);
402 fnd_file.put_line(fnd_file.LOG, 'Processing FINANCIAL_STATEMENT_ID =' || M_FINANCIAL_STATEMENT_ID );
403 fnd_file.put_line(fnd_file.LOG, 'Processing FINANCIAL_ITEM_ID =' || M_FINANCIAL_ITEM_ID );
404 fnd_file.put_line(fnd_file.LOG, 'Processing TL Definition for FINANCIAL_ITEM_NAME =' || M_NAME );
405 fnd_file.put_line(fnd_file.LOG, 'Processing TL Definition for LANGUAGE =' || M_LANGUAGE );
406
407
408 AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_FINITEM_ROW_TL(
409 X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
410 X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID ,
411 X_FINANCIAL_ITEM_ID => M_FINANCIAL_ITEM_ID ,
412 X_NAME => M_NAME,
413 X_LANGUAGE => M_LANGUAGE,
414 X_SOURCE_LANGUAGE => M_SOURCE_LANGUAGE ,
415 X_SECURITY_GROUP_ID => M_SECURITY_GROUP_ID ,
416 X_OBJECT_VERSION_NUMBER => Null,
417 X_ORIG_SYSTEM_REFERENCE => Null ,
418 X_LAST_UPDATE_DATE => SYSDATE,
419 X_LAST_UPDATED_BY => g_user_id ,
420 X_LAST_UPDATE_LOGIN => g_login_id,
421 X_CREATION_DATE => SYSDATE,
422 X_CREATED_BY => g_user_id);
423
424 end loop;
425
426 open Get_accitem_from_external_apps for sql_for_acc_item ;
427 loop
428 fetch Get_accitem_from_external_apps into account_item_map_b_record ;
429 exit when Get_accitem_from_external_apps%notfound;
430
431 -- M_ACCOUNT_GROUP_ID := get_acc_values.ACCOUNT_GROUP_ID;
432 M_NATURAL_ACCOUNT_ID := account_item_map_b_record.NATURAL_ACCOUNT_ID;
433 M_FINANCIAL_STATEMENT_ID := account_item_map_b_record.FINANCIAL_STATEMENT_ID ;
434 M_FINANCIAL_ITEM_ID := account_item_map_b_record.FINANCIAL_ITEM_ID ;
435
436
437 for get_account_id in Get_acc_values_from_icm(M_NATURAL_ACCOUNT_ID)
438 loop
439 exit when Get_acc_values_from_icm%notfound;
440 M_ACCOUNT_GROUP_ID := get_account_id.ACCOUNT_GROUP_ID;
441 end loop;
442
443 fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR STATEMENT_GROUP_ID =' || M_STATEMENT_GROUP_ID);
444 fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR FINANCIAL_STATEMENT_ID =' || M_FINANCIAL_STATEMENT_ID );
445 fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR FINANCIAL_ITEM_ID =' || M_FINANCIAL_ITEM_ID );
446 fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR ACCOUNT_GROUP_ID=' || M_ACCOUNT_GROUP_ID);
447 fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR NATURAL_ACCOUNT_ID=' || M_NATURAL_ACCOUNT_ID);
448
449
450 if (M_ACCOUNT_GROUP_ID is not null) then
451 AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_FINITEM_ACC_MAP(
452 X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
453 X_ACCOUNT_GROUP_ID => M_ACCOUNT_GROUP_ID ,
454 X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID ,
455 X_FINANCIAL_ITEM_ID => M_FINANCIAL_ITEM_ID ,
456 X_NATURAL_ACCOUNT_ID =>M_NATURAL_ACCOUNT_ID ,
457 X_LAST_UPDATE_DATE => SYSDATE,
458 X_LAST_UPDATED_BY => g_user_id ,
459 X_LAST_UPDATE_LOGIN => g_login_id,
460 X_CREATION_DATE => SYSDATE,
461 X_CREATED_BY => g_user_id ,
462 X_ATTRIBUTE_CATEGORY => NULL,
463 X_ATTRIBUTE1 => NULL,
464 X_ATTRIBUTE2 => NULL,
465 X_ATTRIBUTE3 => NULL,
466 X_ATTRIBUTE4 => NULL,
467 X_ATTRIBUTE5 => NULL,
468 X_ATTRIBUTE6 => NULL,
469 X_ATTRIBUTE7 => NULL,
470 X_ATTRIBUTE8 => NULL,
471 X_ATTRIBUTE9 => NULL,
472 X_ATTRIBUTE10 => NULL,
473 X_ATTRIBUTE11 => NULL,
474 X_ATTRIBUTE12 => NULL,
475 X_ATTRIBUTE13 => NULL,
476 X_ATTRIBUTE14 => NULL,
477 X_ATTRIBUTE15 => NULL,
478 X_SECURITY_GROUP_ID => NULL,
479 X_OBJECT_VERSION_NUMBER => NULL);
480 end if;
481
482
483 end loop;
484 AMW_IMPORT_STMNTS_ACCS_PKG.end_date_stmnts_after_import(P_RUNID => m_run_id,
485 P_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID) ;
486
487 -- Sanket.
488 amw_import_stmnts_accs_pkg.flatten_items ( x_group_id => m_statement_group_id );
489
490 commit;
491 end;
492 EXCEPTION WHEN OTHERS THEN
493 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
494 -- dbms_output.put_line(SQLERRM);
495 g_errbuf := SUBSTR (SQLERRM, 1, 2000) ;
496 g_retcode := '2';
497
498
499 RAISE ;
500 RETURN;
501
502
503 END get_stmnts_from_external_apps;
504
505 ------------------------ ************************************* ---------------------------
506 PROCEDURE get_stmnts_from_oracle_apps(P_RUN_ID in NUMBER) IS
507 begin
508 declare
509
510 m_run_id number := P_RUN_ID;
511
512 M_PARENT_FINANCIAL_ITEM_ID NUMBER;
513 M_STATEMENT_GROUP_ID NUMBER;
514 M_FINANCIAL_STATEMENT_ID NUMBER;
515 M_END_DATE DATE;
516 M_LAST_UPDATE_DATE DATE;
517 M_LAST_UPDATED_BY NUMBER;
518 M_LAST_UPDATE_LOGIN NUMBER;
519 M_CREATION_DATE DATE;
520 M_CREATED_BY NUMBER;
521 M_ATTRIBUTE_CATEGORY VARCHAR2(30);
522 M_ATTRIBUTE1 VARCHAR2(150);
523 M_ATTRIBUTE2 VARCHAR2(150);
524 M_ATTRIBUTE3 VARCHAR2(150);
525 M_ATTRIBUTE4 VARCHAR2(150);
526 M_ATTRIBUTE5 VARCHAR2(150);
527 M_ATTRIBUTE6 VARCHAR2(150);
528 M_ATTRIBUTE7 VARCHAR2(150);
529 M_ATTRIBUTE8 VARCHAR2(150);
530 M_ATTRIBUTE9 VARCHAR2(150);
531 M_ATTRIBUTE10 VARCHAR2(150);
532 M_ATTRIBUTE11 VARCHAR2(150);
533 M_ATTRIBUTE12 VARCHAR2(150);
534 M_ATTRIBUTE13 VARCHAR2(150);
535 M_ATTRIBUTE14 VARCHAR2(150);
536 M_ATTRIBUTE15 VARCHAR2(150);
537 M_SECURITY_GROUP_ID NUMBER;
538 M_OBJECT_VERSION_NUMBER NUMBER;
539 M_FINANCIAL_ITEM_ID NUMBER;
540
541 M_NAME VARCHAR2(240);
542 M_LANGUAGE VARCHAR2(4);
543 M_SOURCE_LANGUAGE VARCHAR2(4);
544 M_OBJECT_TYPE VARCHAR2(10);
545 M_ORIG_SYSTEM_REFERENCE VARCHAR2(150);
546
547 cursor Get_statements_from_ora_gl
548 is
549 select
550 FINANCIAL_STATEMENT_ID,
551 NAME
552 from
553 AMW_STATEMENTS_V
554 where FINANCIAL_STATEMENT_ID in (select FINANCIAL_STATEMENT_ID from AMW_FIN_STMNT_SELECTION where run_id = P_RUN_ID);
555
556
557 cursor Get_fin_items_from_ora_gl
558 is
559 select
560 FINANCIAL_STATEMENT_ID,
561 FINANCIAL_ITEM_ID,
562 NAME,
563 trim(DESCRIPTION) DESCRIPTION,
564 DISPLAY_FLAG,
565 PARENT_FINANCIAL_ITEM_ID
566 from
567 AMW_FINANCIAL_ITEMS_V
568 where FINANCIAL_STATEMENT_ID in (select FINANCIAL_STATEMENT_ID from AMW_FIN_STMNT_SELECTION where run_id = P_RUN_ID);
569
570 cursor Get_lang
571 is
572 select
573 LANGUAGE_CODE
574 from
575 FND_LANGUAGES
576 where INSTALLED_FLAG in ('I', 'B');
577
578 begin
579 -- AMW_IMPORT_STMNTS_ACCS_PKG.end_date_stmnts_before_import(P_RUNID => m_run_id) ;
580 select AMW_FIN_STMNT_S.nextval into M_STATEMENT_GROUP_ID from dual;
581
582 for statements in Get_statements_from_ora_gl
583 loop
584
585 exit when Get_statements_from_ora_gl%notfound;
586 M_FINANCIAL_STATEMENT_ID := statements.FINANCIAL_STATEMENT_ID;
587
588 fnd_file.put_line(fnd_file.LOG, 'Processing STATEMENT_GROUP_ID = ' || M_STATEMENT_GROUP_ID);
589 fnd_file.put_line(fnd_file.LOG, 'Processing FINANCIAL_STATEMENT_ID = ' || M_FINANCIAL_STATEMENT_ID);
590
591
592 AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_STMNT_ROW (
593 X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
594 X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID ,
595 X_END_DATE => NULL,
596 X_LAST_UPDATE_DATE => SYSDATE,
597 X_LAST_UPDATED_BY => g_user_id ,
598 X_LAST_UPDATE_LOGIN => g_login_id,
599 X_CREATION_DATE => SYSDATE,
600 X_CREATED_BY => g_user_id ,
601 X_ATTRIBUTE_CATEGORY => NULL,
602 X_ATTRIBUTE1 => NULL,
603 X_ATTRIBUTE2 => NULL,
604 X_ATTRIBUTE3 => NULL,
605 X_ATTRIBUTE4 => NULL,
606 X_ATTRIBUTE5 => NULL,
607 X_ATTRIBUTE6 => NULL,
608 X_ATTRIBUTE7 => NULL,
609 X_ATTRIBUTE8 => NULL,
610 X_ATTRIBUTE9 => NULL,
611 X_ATTRIBUTE10 => NULL,
612 X_ATTRIBUTE11 => NULL,
613 X_ATTRIBUTE12 => NULL,
614 X_ATTRIBUTE13 => NULL,
615 X_ATTRIBUTE14 => NULL,
616 X_ATTRIBUTE15 => NULL,
617 X_SECURITY_GROUP_ID => NULL,
618 X_OBJECT_VERSION_NUMBER => NULL);
619
620 M_NAME := statements.NAME;
621 M_SOURCE_LANGUAGE := userenv('LANG');
622
623 fnd_file.put_line(fnd_file.LOG, 'Processing TL Definition for STATEMENT_GROUP_ID = ' || M_STATEMENT_GROUP_ID);
624 fnd_file.put_line(fnd_file.LOG, 'Processing TL Definition for FINANCIAL_STATEMENT_ID = ' || M_FINANCIAL_STATEMENT_ID);
625 fnd_file.put_line(fnd_file.LOG, 'Processing TL Definition for FINANCIAL_STATEMENT_NAME = ' || M_NAME);
626
627 for lang in Get_lang
628 loop
629 exit when Get_lang%notfound;
630 M_LANGUAGE := lang.LANGUAGE_CODE;
631 fnd_file.put_line(fnd_file.LOG, 'Processing TL Definition for LANGUAGE = ' || M_LANGUAGE);
632
633 AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_STMNT_ROW_TL(
634 X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
635 X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID,
636 X_NAME => M_NAME,
637 X_LANGUAGE => M_LANGUAGE,
638 X_SOURCE_LANGUAGE => M_SOURCE_LANGUAGE,
639 -- X_OBJECT_TYPE ,
640 X_SECURITY_GROUP_ID => M_SECURITY_GROUP_ID,
641 X_OBJECT_VERSION_NUMBER => Null,
642 X_ORIG_SYSTEM_REFERENCE => Null,
643 X_LAST_UPDATE_DATE => SYSDATE,
644 X_LAST_UPDATED_BY => g_user_id ,
645 X_LAST_UPDATE_LOGIN => g_login_id,
646 X_CREATION_DATE => SYSDATE,
647 X_CREATED_BY => g_user_id);
648
649 end loop;
650 end loop;
651
652 for fin_items in Get_fin_items_from_ora_gl
653 loop
654
655 exit when Get_fin_items_from_ora_gl%notfound;
656
657 M_FINANCIAL_STATEMENT_ID := fin_items.FINANCIAL_STATEMENT_ID;
658 M_FINANCIAL_ITEM_ID := fin_items.FINANCIAL_ITEM_ID;
659 M_PARENT_FINANCIAL_ITEM_ID := fin_items.PARENT_FINANCIAL_ITEM_ID ;
660
661 fnd_file.put_line(fnd_file.LOG, 'Processing STATEMENT_GROUP_ID = ' || M_STATEMENT_GROUP_ID);
662 fnd_file.put_line(fnd_file.LOG, 'Processing FINANCIAL_STATEMENT_ID = ' || M_FINANCIAL_STATEMENT_ID);
663 fnd_file.put_line(fnd_file.LOG, 'Processing FINANCIAL_ITEM_ID = ' || M_FINANCIAL_ITEM_ID);
664 fnd_file.put_line(fnd_file.LOG, 'Processing PARENT_FINANCIAL_ITEM_ID = ' || M_PARENT_FINANCIAL_ITEM_ID);
665
666 AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_FINITEM_ROW(
667 X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
668 X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID ,
669 X_FINANCIAL_ITEM_ID => M_FINANCIAL_ITEM_ID ,
670 X_PARENT_FINANCIAL_ITEM_ID => M_PARENT_FINANCIAL_ITEM_ID,
671 X_SEQUENCE_NUMBER => M_FINANCIAL_ITEM_ID ,
672 X_LAST_UPDATE_DATE => SYSDATE,
673 X_LAST_UPDATED_BY => g_user_id ,
674 X_LAST_UPDATE_LOGIN => g_login_id,
675 X_CREATION_DATE => SYSDATE,
676 X_CREATED_BY => g_user_id ,
677 X_ATTRIBUTE_CATEGORY => NULL,
678 X_ATTRIBUTE1 => NULL,
679 X_ATTRIBUTE2 => NULL,
680 X_ATTRIBUTE3 => NULL,
681 X_ATTRIBUTE4 => NULL,
682 X_ATTRIBUTE5 => NULL,
683 X_ATTRIBUTE6 => NULL,
684 X_ATTRIBUTE7 => NULL,
685 X_ATTRIBUTE8 => NULL,
686 X_ATTRIBUTE9 => NULL,
687 X_ATTRIBUTE10 => NULL,
688 X_ATTRIBUTE11 => NULL,
689 X_ATTRIBUTE12 => NULL,
690 X_ATTRIBUTE13 => NULL,
691 X_ATTRIBUTE14 => NULL,
692 X_ATTRIBUTE15 => NULL,
693 X_SECURITY_GROUP_ID => NULL,
694 X_OBJECT_VERSION_NUMBER => NULL);
695
696 M_NAME := fin_items.DESCRIPTION;
697 M_SOURCE_LANGUAGE := userenv('LANG');
698
699 fnd_file.put_line(fnd_file.LOG, 'Processing STATEMENT_GROUP_ID = ' || M_STATEMENT_GROUP_ID);
700 fnd_file.put_line(fnd_file.LOG, 'Processing FINANCIAL_STATEMENT_ID = ' || M_FINANCIAL_STATEMENT_ID);
701 fnd_file.put_line(fnd_file.LOG, 'Processing FINANCIAL_ITEM_ID = ' || M_FINANCIAL_ITEM_ID);
702 fnd_file.put_line(fnd_file.LOG, 'Processing TL Definition for FINANCIAL_ITEM_NAME = ' || M_NAME);
703
704 for lang in Get_lang
705 loop
706 exit when Get_lang%notfound;
707 M_LANGUAGE := lang.LANGUAGE_CODE;
708 fnd_file.put_line(fnd_file.LOG, 'Processing TL Definition for LANGUAGE = ' || M_LANGUAGE);
709
710 AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_FINITEM_ROW_TL(
711 X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
712 X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID,
713 X_FINANCIAL_ITEM_ID => M_FINANCIAL_ITEM_ID,
714 X_NAME => M_NAME,
715 X_LANGUAGE => M_LANGUAGE,
716 X_SOURCE_LANGUAGE => M_SOURCE_LANGUAGE,
717 X_SECURITY_GROUP_ID => M_SECURITY_GROUP_ID,
718 X_OBJECT_VERSION_NUMBER => Null,
719 X_ORIG_SYSTEM_REFERENCE => Null,
720 X_LAST_UPDATE_DATE => SYSDATE,
721 X_LAST_UPDATED_BY => g_user_id,
722 X_LAST_UPDATE_LOGIN => g_login_id,
723 X_CREATION_DATE => SYSDATE,
724 X_CREATED_BY => g_user_id);
725
726 end loop;
727 end loop;
728
729 AMW_IMPORT_STMNTS_ACCS_PKG.get_stmnts_accs_oracle_apps(P_RUN_ID =>m_run_id ,
730 P_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID);
731
732 AMW_IMPORT_STMNTS_ACCS_PKG.end_date_stmnts_after_import(P_RUNID => m_run_id,
733 P_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID) ;
734
735 -- Sanket.
736 amw_import_stmnts_accs_pkg.flatten_items ( x_group_id => m_statement_group_id );
737
738 commit;
739 end;
740
741 EXCEPTION WHEN OTHERS THEN
742 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
743 -- dbms_output.put_line(SQLERRM);
744 g_errbuf := SUBSTR (SQLERRM, 1, 2000) ;
745 g_retcode := '2';
746 RAISE ;
747 RETURN;
748
749 END get_stmnts_from_oracle_apps;
750 ------------------------ ************************************* ---------------------------
751 PROCEDURE get_stmnts_accs_oracle_apps(P_RUN_ID in NUMBER, P_STATEMENT_GROUP_ID in NUMBER)
752 IS
753 begin
754 declare
755 m_run_id number := P_RUN_ID;
756 m_flex_value_set_id number;
757 default_value_set_id number;
758 M_CHART_OF_ACCOUNTS_ID NUMBER;
759 M_SET_OF_BOOKS_ID NUMBER;
760 M_Low_Value varchar2(60);
761 M_High_Value varchar2(60);
762
763 M_ACCOUNT_GROUP_ID NUMBER;
764 M_NATURAL_ACCOUNT_ID NUMBER;
765 M_STATEMENT_GROUP_ID NUMBER;
766 M_FINANCIAL_STATEMENT_ID NUMBER;
767 M_FINANCIAL_ITEM_ID NUMBER;
768 M_END_DATE DATE;
769 M_LAST_UPDATE_DATE DATE;
770 M_LAST_UPDATED_BY NUMBER;
771 M_LAST_UPDATE_LOGIN NUMBER;
772 M_CREATION_DATE DATE;
773 M_CREATED_BY NUMBER;
774 M_ATTRIBUTE_CATEGORY VARCHAR2(30);
775 M_ATTRIBUTE1 VARCHAR2(150);
776 M_ATTRIBUTE2 VARCHAR2(150);
777 M_ATTRIBUTE3 VARCHAR2(150);
778 M_ATTRIBUTE4 VARCHAR2(150);
779 M_ATTRIBUTE5 VARCHAR2(150);
780 M_ATTRIBUTE6 VARCHAR2(150);
781 M_ATTRIBUTE7 VARCHAR2(150);
782 M_ATTRIBUTE8 VARCHAR2(150);
783 M_ATTRIBUTE9 VARCHAR2(150);
784 M_ATTRIBUTE10 VARCHAR2(150);
785 M_ATTRIBUTE11 VARCHAR2(150);
786 M_ATTRIBUTE12 VARCHAR2(150);
787 M_ATTRIBUTE13 VARCHAR2(150);
788 M_ATTRIBUTE14 VARCHAR2(150);
789 M_ATTRIBUTE15 VARCHAR2(150);
790 M_SECURITY_GROUP_ID NUMBER;
791 M_OBJECT_VERSION_NUMBER NUMBER;
792 M_DEFAULT_SETOFBOOKS_ID NUMBER;
793
794
795 cursor Get_acc_range_from_ora_gl is
796 SELECT
797 FINANCIAL_STATEMENT_ID,
798 FINANCIAL_ITEM_ID ,
799 SET_OF_BOOKS_ID ,
800 SEGMENT1_LOW ,
801 SEGMENT1_HIGH ,
802 SEGMENT1_TYPE ,
803 SEGMENT2_LOW ,
804 SEGMENT2_HIGH ,
805 SEGMENT2_TYPE ,
806 SEGMENT3_LOW ,
807 SEGMENT3_HIGH ,
808 SEGMENT3_TYPE ,
809 SEGMENT4_LOW ,
810 SEGMENT4_HIGH ,
811 SEGMENT4_TYPE ,
812 SEGMENT5_LOW ,
813 SEGMENT5_HIGH ,
814 SEGMENT5_TYPE ,
815 SEGMENT6_LOW ,
816 SEGMENT6_HIGH ,
817 SEGMENT6_TYPE ,
818 SEGMENT7_LOW ,
819 SEGMENT7_HIGH ,
820 SEGMENT7_TYPE ,
821 SEGMENT8_LOW ,
822 SEGMENT8_HIGH ,
823 SEGMENT8_TYPE ,
824 SEGMENT9_LOW ,
825 SEGMENT9_HIGH ,
826 SEGMENT9_TYPE ,
827 SEGMENT10_LOW ,
828 SEGMENT10_HIGH ,
829 SEGMENT10_TYPE ,
830 SEGMENT11_LOW ,
831 SEGMENT11_HIGH ,
832 SEGMENT11_TYPE ,
833 SEGMENT12_LOW ,
834 SEGMENT12_HIGH ,
835 SEGMENT12_TYPE ,
836 SEGMENT13_LOW ,
837 SEGMENT13_HIGH ,
838 SEGMENT13_TYPE ,
839 SEGMENT14_LOW ,
840 SEGMENT14_HIGH ,
841 SEGMENT14_TYPE ,
842 SEGMENT15_LOW ,
843 SEGMENT15_HIGH ,
844 SEGMENT15_TYPE ,
845 SEGMENT16_LOW ,
846 SEGMENT16_HIGH ,
847 SEGMENT16_TYPE ,
848 SEGMENT17_LOW ,
849 SEGMENT17_HIGH ,
850 SEGMENT17_TYPE ,
851 SEGMENT18_LOW ,
852 SEGMENT18_HIGH ,
853 SEGMENT18_TYPE ,
854 SEGMENT19_LOW ,
855 SEGMENT19_HIGH ,
856 SEGMENT19_TYPE ,
857 SEGMENT20_LOW ,
858 SEGMENT20_HIGH ,
859 SEGMENT20_TYPE ,
860 SEGMENT21_LOW ,
861 SEGMENT21_HIGH ,
862 SEGMENT21_TYPE ,
863 SEGMENT22_LOW ,
864 SEGMENT22_HIGH ,
865 SEGMENT22_TYPE ,
866 SEGMENT23_LOW ,
867 SEGMENT23_HIGH ,
868 SEGMENT23_TYPE ,
869 SEGMENT24_LOW ,
870 SEGMENT24_HIGH ,
871 SEGMENT24_TYPE ,
872 SEGMENT25_LOW ,
873 SEGMENT25_HIGH ,
874 SEGMENT25_TYPE ,
875 SEGMENT26_LOW ,
876 SEGMENT26_HIGH ,
877 SEGMENT26_TYPE ,
878 SEGMENT27_LOW ,
879 SEGMENT27_HIGH ,
880 SEGMENT27_TYPE ,
881 SEGMENT28_LOW ,
882 SEGMENT28_HIGH ,
883 SEGMENT28_TYPE ,
884 SEGMENT29_LOW ,
885 SEGMENT29_HIGH ,
886 SEGMENT29_TYPE ,
887 SEGMENT30_LOW ,
888 SEGMENT30_HIGH ,
889 SEGMENT30_TYPE
890 FROM
891 AMW_FIN_ITEMS_ACCOUNT_RANGE_V
892 where FINANCIAL_STATEMENT_ID
893 in (select FINANCIAL_STATEMENT_ID from AMW_FIN_STMNT_SELECTION where run_id = P_RUN_ID);
894
895 cursor Get_acc_range_column_ora_gl(P_CHART_OF_ACCOUNTS_ID number) is
896 Select
897 distinct v.APPLICATION_COLUMN_NAME ,
898 vs.flex_value_set_name, vs.description, vs.flex_value_set_id
899 from
900 fnd_flex_value_sets vs, fnd_segment_attribute_Values v, fnd_id_flex_segments s
901 where
902 s.application_id=101 and s.id_flex_Code = 'GL#'
903 and s.enabled_flag='Y'
904 and v.application_id=s.application_id
905 and v.id_flex_code=s.id_flex_code
906 and v.id_flex_num=s.id_flex_num
907 and v.application_column_name=s.application_column_name
908 and v.segment_attribute_type='GL_ACCOUNT'
909 and v.attribute_value='Y'
910 and s.flex_value_set_id=vs.flex_value_set_id
911 and v.id_flex_num= P_CHART_OF_ACCOUNTS_ID;
912
913 cursor Get_set_of_books_ora_gl(P_SET_OF_BOOKS_ID number) is
914 Select
915 CHART_OF_ACCOUNTS_ID
916 from
917 GL_SETS_OF_BOOKS_V
918 Where
919 SET_OF_BOOKS_ID= P_SET_OF_BOOKS_ID ;
920
921
922 cursor Get_acc_values_from_ora_gl (p_Low_Value varchar2, p_High_Value varchar2 )
923 is
924 --(p_CHART_OF_ACCOUNTS_ID number)
925 SELECT
926 distinct ACCOUNT_GROUP_ID , NATURAL_ACCOUNT_ID, NATURAL_ACCOUNT_VALUE
927 from
928 AMW_FIN_KEY_ACCOUNTS_B
929 WHERE End_Date is null
930 And NATURAL_ACCOUNT_VALUE >= M_Low_Value
931 And NATURAL_ACCOUNT_VALUE <= M_High_Value
932 and END_DATE is null;
933
934
935
936 begin
937 select fnd_profile.value('AMW_NATRL_ACCT_VALUE_SET') into default_value_set_id from dual ;
938
939 fnd_file.put_line(fnd_file.LOG, 'Profile value AMW_NATRL_ACCT_VALUE_SET =' || default_value_set_id );
940
941 select fnd_profile.value('GL_SET_OF_BKS_ID') into M_DEFAULT_SETOFBOOKS_ID from dual ;
942 fnd_file.put_line(fnd_file.LOG, 'Profile value GL_SET_OF_BKS_ID (Default Set of Books)=' || M_DEFAULT_SETOFBOOKS_ID );
943
944
945
946 for acc_range in Get_acc_range_from_ora_gl
947 loop
948 exit when Get_acc_range_from_ora_gl%notfound;
949 M_STATEMENT_GROUP_ID := P_STATEMENT_GROUP_ID;
950 M_FINANCIAL_STATEMENT_ID := acc_range.FINANCIAL_STATEMENT_ID;
951 M_FINANCIAL_ITEM_ID := acc_range.FINANCIAL_ITEM_ID;
952
953 M_SET_OF_BOOKS_ID := acc_range.SET_OF_BOOKS_ID;
954
955 if trim(M_SET_OF_BOOKS_ID) is null then
956 M_SET_OF_BOOKS_ID := M_DEFAULT_SETOFBOOKS_ID;
957 fnd_file.put_line(fnd_file.LOG, 'This Ros import will be using Default Set of Books Profile value GL_SET_OF_BKS_ID as the set of books in account range - row set definition is blank');
958
959 end if;
960
961 fnd_file.put_line(fnd_file.LOG, '---------------------------------------------------------------------');
962 fnd_file.put_line(fnd_file.LOG, 'Processing STATEMENT_GROUP_ID =' || M_STATEMENT_GROUP_ID);
963 fnd_file.put_line(fnd_file.LOG, 'Processing FINANCIAL_STATEMENT_ID =' || M_FINANCIAL_STATEMENT_ID );
964 fnd_file.put_line(fnd_file.LOG, 'Processing FINANCIAL_ITEM_ID =' || M_FINANCIAL_ITEM_ID );
965 fnd_file.put_line(fnd_file.LOG, 'Processing SET_OF_BOOKS_ID =' || M_SET_OF_BOOKS_ID );
966
967 for chart_of_acc in Get_set_of_books_ora_gl(M_SET_OF_BOOKS_ID)
968 loop
969 exit when Get_set_of_books_ora_gl%notfound;
970
971 M_CHART_OF_ACCOUNTS_ID := chart_of_acc.CHART_OF_ACCOUNTS_ID;
972
973 fnd_file.put_line(fnd_file.LOG, 'Processing M_CHART_OF_ACCOUNTS_ID =' || M_CHART_OF_ACCOUNTS_ID);
974
975
976
977 for acc_segment in Get_acc_range_column_ora_gl(M_CHART_OF_ACCOUNTS_ID)
978 loop
979 exit when Get_acc_range_column_ora_gl%notfound;
980 --acc_segment := acc_segment.APPLICATION_COLUMN_NAME;
981 m_flex_value_set_id := acc_segment.flex_value_set_id;
982
983 if m_flex_value_set_id <> default_value_set_id then
984
985 FND_MESSAGE.SET_NAME ('AMW', 'AMW_STMNTS_NOT_IN_ACC_VAL_SET');
986 g_errbuf := FND_MESSAGE.GET_STRING('AMW', 'AMW_STMNTS_NOT_IN_ACC_VAL_SET');
987 --m_errMsg := FND_MESSAGE.GET_STRING('AMW', 'AMW_STMNTS_NOT_IN_ACC_VAL_SET');
988 g_retcode :='0';
989
990 fnd_file.put_line(fnd_file.LOG,'---------------------------!!!!!!!!!!!!---------------------------------');
991
992
993 fnd_file.put_line(fnd_file.LOG,g_errbuf );
994
995 fnd_file.put_line(fnd_file.LOG, 'WARNING: The Record with FINANCIAL_STATEMENT_ID (Row Set) =' || M_FINANCIAL_STATEMENT_ID );
996 fnd_file.put_line(fnd_file.LOG, ' FINANCIAL_ITEM_ID (ROW ID) =' || M_FINANCIAL_ITEM_ID );
997 fnd_file.put_line(fnd_file.LOG, ' SET_OF_BOOKS_ID =' || M_SET_OF_BOOKS_ID );
998 fnd_file.put_line(fnd_file.LOG, ' M_CHART_OF_ACCOUNTS_ID = ' || M_CHART_OF_ACCOUNTS_ID );
999 fnd_file.put_line(fnd_file.LOG, ' has a Natural Value Set Id of ' || m_flex_value_set_id || ' which is different from ' || 'default_value_set_id');
1000 fnd_file.put_line(fnd_file.LOG,'------------------------!!!!!!!!!!!!!!!!!!!!------------------------------------');
1001
1002
1003
1004 elsif m_flex_value_set_id = default_value_set_id then
1005
1006
1007 fnd_file.put_line(fnd_file.LOG, 'Natural Account Value Segment Column =' || acc_segment.APPLICATION_COLUMN_NAME );
1008
1009
1010 if acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT1' then
1011 M_Low_Value := acc_range.SEGMENT1_LOW ;
1012 M_High_Value := acc_range.SEGMENT1_HIGH ;
1013 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT2' then
1014 M_Low_Value := acc_range.SEGMENT2_LOW ;
1015 M_High_Value := acc_range.SEGMENT2_HIGH ;
1016 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT3' then
1017 M_Low_Value := acc_range.SEGMENT3_LOW ;
1018 M_High_Value := acc_range.SEGMENT3_HIGH ;
1019
1020 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT4' then
1021 M_Low_Value := acc_range.SEGMENT4_LOW ;
1022 M_High_Value := acc_range.SEGMENT4_HIGH ;
1023 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT5' then
1024 M_Low_Value := acc_range.SEGMENT5_LOW ;
1025 M_High_Value := acc_range.SEGMENT5_HIGH ;
1026 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT6' then
1027 M_Low_Value := acc_range.SEGMENT6_LOW ;
1028 M_High_Value := acc_range.SEGMENT6_HIGH ;
1029 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT7' then
1030 M_Low_Value := acc_range.SEGMENT7_LOW ;
1031 M_High_Value := acc_range.SEGMENT7_HIGH ;
1032 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT8' then
1033 M_Low_Value := acc_range.SEGMENT8_LOW ;
1034 M_High_Value := acc_range.SEGMENT8_HIGH ;
1035 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT9' then
1036 M_Low_Value := acc_range.SEGMENT9_LOW ;
1037 M_High_Value := acc_range.SEGMENT9_HIGH ;
1038 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT10' then
1039 M_Low_Value := acc_range.SEGMENT10_LOW ;
1040 M_High_Value := acc_range.SEGMENT10_HIGH ;
1041 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT11' then
1042 M_Low_Value := acc_range.SEGMENT11_LOW ;
1043 M_High_Value := acc_range.SEGMENT11_HIGH ;
1044 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT12' then
1045 M_Low_Value := acc_range.SEGMENT12_LOW ;
1046 M_High_Value := acc_range.SEGMENT12_HIGH ;
1047 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT13' then
1048 M_Low_Value := acc_range.SEGMENT13_LOW ;
1049 M_High_Value := acc_range.SEGMENT13_HIGH ;
1050 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT14' then
1051 M_Low_Value := acc_range.SEGMENT14_LOW ;
1052 M_High_Value := acc_range.SEGMENT14_HIGH ;
1053 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT15' then
1054 M_Low_Value := acc_range.SEGMENT15_LOW ;
1055 M_High_Value := acc_range.SEGMENT15_HIGH ;
1056 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT16' then
1057 M_Low_Value := acc_range.SEGMENT16_LOW ;
1058 M_High_Value := acc_range.SEGMENT16_HIGH ;
1059 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT17' then
1060 M_Low_Value := acc_range.SEGMENT17_LOW ;
1061 M_High_Value := acc_range.SEGMENT17_HIGH ;
1062 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT18' then
1063 M_Low_Value := acc_range.SEGMENT18_LOW ;
1064 M_High_Value := acc_range.SEGMENT18_HIGH ;
1065 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT19' then
1066 M_Low_Value := acc_range.SEGMENT19_LOW ;
1067 M_High_Value := acc_range.SEGMENT19_HIGH ;
1068 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT20' then
1069 M_Low_Value := acc_range.SEGMENT20_LOW ;
1070 M_High_Value := acc_range.SEGMENT20_HIGH ;
1071 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT21' then
1072 M_Low_Value := acc_range.SEGMENT21_LOW ;
1073 M_High_Value := acc_range.SEGMENT21_HIGH ;
1074 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT22' then
1075 M_Low_Value := acc_range.SEGMENT22_LOW ;
1076 M_High_Value := acc_range.SEGMENT22_HIGH ;
1077 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT23' then
1078 M_Low_Value := acc_range.SEGMENT23_LOW ;
1079 M_High_Value := acc_range.SEGMENT23_HIGH ;
1080 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT24' then
1081 M_Low_Value := acc_range.SEGMENT24_LOW ;
1082 M_High_Value := acc_range.SEGMENT24_HIGH ;
1083 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT25' then
1084 M_Low_Value := acc_range.SEGMENT25_LOW ;
1085 M_High_Value := acc_range.SEGMENT25_HIGH ;
1086 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT26' then
1087 M_Low_Value := acc_range.SEGMENT26_LOW ;
1088 M_High_Value := acc_range.SEGMENT26_HIGH ;
1089 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT27' then
1090 M_Low_Value := acc_range.SEGMENT27_LOW ;
1091 M_High_Value := acc_range.SEGMENT27_HIGH ;
1092 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT28' then
1093 M_Low_Value := acc_range.SEGMENT28_LOW ;
1094 M_High_Value := acc_range.SEGMENT28_HIGH ;
1095 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT29' then
1096 M_Low_Value := acc_range.SEGMENT29_LOW ;
1097 M_High_Value := acc_range.SEGMENT29_HIGH ;
1098 elsif acc_segment.APPLICATION_COLUMN_NAME = 'SEGMENT30' then
1099 M_Low_Value := acc_range.SEGMENT30_LOW ;
1100 M_High_Value := acc_range.SEGMENT30_HIGH ;
1101 end if;
1102
1103 -- M_Low_Value := acc_segment.APPLICATION_COLUMN_NAME || '_LOW';
1104 --M_High_Value := acc_segment.APPLICATION_COLUMN_NAME || '_HIGH';
1105
1106 fnd_file.put_line(fnd_file.LOG, 'Processing Account Range Low Val=' || M_Low_Value );
1107 fnd_file.put_line(fnd_file.LOG, 'Processing Account Range High Val=' || M_High_Value);
1108
1109
1110 for get_acc_values in Get_acc_values_from_ora_gl(M_Low_Value , M_High_Value)
1111 loop
1112 exit when Get_acc_values_from_ora_gl%notfound;
1113 M_ACCOUNT_GROUP_ID := get_acc_values.ACCOUNT_GROUP_ID;
1114 M_NATURAL_ACCOUNT_ID := get_acc_values.NATURAL_ACCOUNT_ID;
1115
1116 fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR STATEMENT_GROUP_ID =' || M_STATEMENT_GROUP_ID);
1117 fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR FINANCIAL_STATEMENT_ID =' || M_FINANCIAL_STATEMENT_ID );
1118 fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR FINANCIAL_ITEM_ID =' || M_FINANCIAL_ITEM_ID );
1119 fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR ACCOUNT_GROUP_ID=' || M_ACCOUNT_GROUP_ID);
1120 fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR NATURAL_ACCOUNT_ID=' || M_NATURAL_ACCOUNT_ID);
1121
1122
1123 AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_FINITEM_ACC_MAP(
1124 X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
1125 X_ACCOUNT_GROUP_ID => M_ACCOUNT_GROUP_ID ,
1126 X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID ,
1127 X_FINANCIAL_ITEM_ID => M_FINANCIAL_ITEM_ID ,
1128 X_NATURAL_ACCOUNT_ID =>M_NATURAL_ACCOUNT_ID ,
1129 X_LAST_UPDATE_DATE => SYSDATE,
1130 X_LAST_UPDATED_BY => g_user_id ,
1131 X_LAST_UPDATE_LOGIN => g_login_id,
1132 X_CREATION_DATE => SYSDATE,
1133 X_CREATED_BY => g_user_id ,
1134 X_ATTRIBUTE_CATEGORY => NULL,
1135 X_ATTRIBUTE1 => NULL,
1136 X_ATTRIBUTE2 => NULL,
1137 X_ATTRIBUTE3 => NULL,
1138 X_ATTRIBUTE4 => NULL,
1139 X_ATTRIBUTE5 => NULL,
1140 X_ATTRIBUTE6 => NULL,
1141 X_ATTRIBUTE7 => NULL,
1142 X_ATTRIBUTE8 => NULL,
1143 X_ATTRIBUTE9 => NULL,
1144 X_ATTRIBUTE10 => NULL,
1145 X_ATTRIBUTE11 => NULL,
1146 X_ATTRIBUTE12 => NULL,
1147 X_ATTRIBUTE13 => NULL,
1148 X_ATTRIBUTE14 => NULL,
1149 X_ATTRIBUTE15 => NULL,
1150 X_SECURITY_GROUP_ID => NULL,
1151 X_OBJECT_VERSION_NUMBER => NULL);
1152
1153
1154 end loop;
1155
1156 end if;
1157
1158 end loop;
1159
1160 end loop;
1161
1162
1163 end loop;
1164
1165 end;
1166
1167 EXCEPTION WHEN OTHERS THEN
1168 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
1169 -- dbms_output.put_line(SQLERRM);
1170 g_errbuf := SUBSTR (SQLERRM, 1, 2000) ;
1171 g_retcode := '2';
1172
1173 RAISE ;
1174 RETURN;
1175
1176 END get_stmnts_accs_oracle_apps;
1177
1178 ------------------------ ************************************* ---------------------------
1179 PROCEDURE end_date_stmnts_after_import(P_RUNID NUMBER, P_STATEMENT_GROUP_ID NUMBER)IS
1180 begin
1181 declare
1182 begin
1183 -- NOTE :- first update the end date if the statement structure is being re-imported and then if the end dated
1184 -- statements and associated records in other tables are not being used in any certifications delete them
1185
1186 fnd_file.put_line(fnd_file.LOG, 'End Dating and Deleting Statements Imported previously and is also part of this import after Importing For Run ID=' || P_RUNID);
1187
1188
1189 update AMW_FIN_STMNT_B set end_date = sysdate
1190 where STATEMENT_GROUP_ID <> P_STATEMENT_GROUP_ID and
1191 FINANCIAL_STATEMENT_ID in (select FINANCIAL_STATEMENT_ID from AMW_FIN_STMNT_SELECTION where run_id = P_RUNID);
1192
1193 delete AMW_FIN_STMNT_TL tl where exists(select base.STATEMENT_GROUP_ID, base.FINANCIAL_STATEMENT_ID
1194 from AMW_FIN_STMNT_B base where base.STATEMENT_GROUP_ID= tl.STATEMENT_GROUP_ID
1195 and base.FINANCIAL_STATEMENT_ID = tl.FINANCIAL_STATEMENT_ID and base.end_date is not null
1196 ) and not exists (select cert.STATEMENT_GROUP_ID, cert.FINANCIAL_STATEMENT_ID
1197 from amw_certification_b cert where cert.STATEMENT_GROUP_ID= tl.STATEMENT_GROUP_ID
1198 and cert.FINANCIAL_STATEMENT_ID = tl.FINANCIAL_STATEMENT_ID and cert.OBJECT_TYPE =
1199 'FIN_STMT');
1200
1201 delete AMW_FIN_STMNT_ITEMS_TL tl where exists(select base.STATEMENT_GROUP_ID, base.FINANCIAL_STATEMENT_ID
1202 from AMW_FIN_STMNT_B base where base.STATEMENT_GROUP_ID= tl.STATEMENT_GROUP_ID
1203 and base.FINANCIAL_STATEMENT_ID = tl.FINANCIAL_STATEMENT_ID and base.end_date is not null
1204 ) and not exists (select cert.STATEMENT_GROUP_ID, cert.FINANCIAL_STATEMENT_ID
1205 from amw_certification_b cert where cert.STATEMENT_GROUP_ID= tl.STATEMENT_GROUP_ID
1206 and cert.FINANCIAL_STATEMENT_ID = tl.FINANCIAL_STATEMENT_ID and cert.OBJECT_TYPE =
1207 'FIN_STMT');
1208
1209 delete AMW_FIN_STMNT_ITEMS_B tl where exists(select base.STATEMENT_GROUP_ID, base.FINANCIAL_STATEMENT_ID
1210 from AMW_FIN_STMNT_B base where base.STATEMENT_GROUP_ID= tl.STATEMENT_GROUP_ID
1211 and base.FINANCIAL_STATEMENT_ID = tl.FINANCIAL_STATEMENT_ID and base.end_date is not null
1212 ) and not exists (select cert.STATEMENT_GROUP_ID, cert.FINANCIAL_STATEMENT_ID
1213 from amw_certification_b cert where cert.STATEMENT_GROUP_ID= tl.STATEMENT_GROUP_ID
1214 and cert.FINANCIAL_STATEMENT_ID = tl.FINANCIAL_STATEMENT_ID and cert.OBJECT_TYPE =
1215 'FIN_STMT');
1216
1217 delete AMW_FIN_ITEMS_KEY_ACC tl where exists(select base.STATEMENT_GROUP_ID, base.FINANCIAL_STATEMENT_ID
1218 from AMW_FIN_STMNT_B base where base.STATEMENT_GROUP_ID= tl.STATEMENT_GROUP_ID
1219 and base.FINANCIAL_STATEMENT_ID = tl.FINANCIAL_STATEMENT_ID and base.end_date is not null
1220 ) and not exists (select cert.STATEMENT_GROUP_ID, cert.FINANCIAL_STATEMENT_ID
1221 from amw_certification_b cert where cert.STATEMENT_GROUP_ID= tl.STATEMENT_GROUP_ID
1222 and cert.FINANCIAL_STATEMENT_ID = tl.FINANCIAL_STATEMENT_ID and cert.OBJECT_TYPE =
1223 'FIN_STMT');
1224
1225 delete AMW_FIN_STMNT_B tl where tl.end_date is not null
1226 and not exists (select cert.STATEMENT_GROUP_ID, cert.FINANCIAL_STATEMENT_ID
1227 from amw_certification_b cert where cert.STATEMENT_GROUP_ID= tl.STATEMENT_GROUP_ID
1228 and cert.FINANCIAL_STATEMENT_ID = tl.FINANCIAL_STATEMENT_ID and cert.OBJECT_TYPE =
1229 'FIN_STMT');
1230
1231
1232
1233 end;
1234
1235 /* EXCEPTION WHEN OTHERS THEN
1236
1237 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
1238 -- dbms_output.put_line(SQLERRM);
1239 g_errbuf := SUBSTR (SQLERRM, 1, 2000) ;
1240 g_retcode := '2';
1241 RAISE ;
1242 RETURN;
1243 */
1244 END end_date_stmnts_after_import;
1245
1246 ------------------------ ************************************* ---------------------------
1247
1248 PROCEDURE get_acc_from_oracle_apps IS
1249 begin
1250 declare
1251
1252 m_acc_value_set_id number;
1253
1254 M_ACCOUNT_GROUP_ID NUMBER;
1255 M_NATURAL_ACCOUNT_ID NUMBER;
1256 M_NATURAL_ACCOUNT_VALUE VARCHAR2(150);
1257 M_END_DATE DATE;
1258 M_LAST_UPDATE_DATE DATE;
1259 M_LAST_UPDATED_BY NUMBER;
1260 M_LAST_UPDATE_LOGIN NUMBER;
1261 M_CREATION_DATE DATE;
1262 M_CREATED_BY NUMBER;
1263 M_ATTRIBUTE_CATEGORY VARCHAR2(30);
1264 M_ATTRIBUTE1 VARCHAR2(150);
1265 M_ATTRIBUTE2 VARCHAR2(150);
1266 M_ATTRIBUTE3 VARCHAR2(150);
1267 M_ATTRIBUTE4 VARCHAR2(150);
1268 M_ATTRIBUTE5 VARCHAR2(150);
1269 M_ATTRIBUTE6 VARCHAR2(150);
1270 M_ATTRIBUTE7 VARCHAR2(150);
1271 M_ATTRIBUTE8 VARCHAR2(150);
1272 M_ATTRIBUTE9 VARCHAR2(150);
1273 M_ATTRIBUTE10 VARCHAR2(150);
1274 M_ATTRIBUTE11 VARCHAR2(150);
1275 M_ATTRIBUTE12 VARCHAR2(150);
1276 M_ATTRIBUTE13 VARCHAR2(150);
1277 M_ATTRIBUTE14 VARCHAR2(150);
1278 M_ATTRIBUTE15 VARCHAR2(150);
1279 M_PARENT_NATURAL_ACCOUNT_ID NUMBER ;
1280
1281 C_NATURAL_ACCOUNT_ID NUMBER;
1282 C_NATURAL_ACCOUNT_VALUE VARCHAR2(150);
1283
1284 cursor Get_accounts_from_oraapps_gl
1285 (m_acc_value_set_id number)
1286 is
1287 SELECT
1288 FLEX_VALUE_ID, FLEX_VALUE, DESCRIPTION
1289 from
1290 fnd_flex_values_vl
1291 -- 4872820 18-Oct-2006 Start-1
1292 --WHERE flex_value_set_id= fnd_profile.value('AMW_NATRL_ACCT_VALUE_SET');
1293 WHERE FLEX_VALUE_SET_ID = m_acc_value_set_id AND ENABLED_FLAG = 'Y'
1294 -- 4872820 18-Oct-2006 End-1
1295 -- bug 5633695 modified by dliao on 12-18-2006
1296 AND (start_date_active IS NULL OR start_date_active <= SYSDATE)
1297 AND (end_date_active IS NULL OR end_date_active >= SYSDATE);
1298
1299 -- m_default_value_set_id;
1300
1301
1302 cursor Get_sub_acc_from_oraapps_gl
1303 (m_flex_value varchar2, m_acc_value_set_id number) is
1304 select
1305 FLEX_VALUE_SET_ID ,
1306 PARENT_FLEX_VALUE, FLEX_VALUE as Child_Flex_Value
1307 from
1308 FND_FLEX_VALUE_CHILDREN_V
1309 Where
1310 FLEX_VALUE_SET_ID = m_acc_value_set_id
1311 and
1312 PARENT_FLEX_VALUE =m_flex_value;
1313
1314
1315 cursor Get_sub_acc_id_oraapps_gl
1316 (m_child_flex_value varchar2, m_acc_value_set_id number) is
1317 SELECT
1318 FLEX_VALUE_ID, FLEX_VALUE, DESCRIPTION
1319 from
1320 fnd_flex_values_vl where
1321 FLEX_VALUE = m_child_flex_value
1322 -- 4872820 18-Oct-2006 Start-2
1323 -- and FLEX_VALUE_SET_ID = fnd_profile.value('AMW_NATRL_ACCT_VALUE_SET');
1324 AND FLEX_VALUE_SET_ID = m_acc_value_set_id AND ENABLED_FLAG = 'Y'
1325 -- 4872820 18-Oct-2006 End-2
1326 -- bug 5633695 modified by dliao on 12-18-2006
1327 AND (start_date_active IS NULL OR start_date_active <= SYSDATE)
1328 AND (end_date_active IS NULL OR end_date_active >= SYSDATE);
1329
1330
1331 begin
1332
1333 --update AMW_FIN_KEY_ACCOUNTS_B set end_date = sysdate;
1334
1335 --fix bug 5926333
1336 m_acc_value_set_id := to_number(fnd_profile.value('AMW_NATRL_ACCT_VALUE_SET'));
1337
1338 -- select fnd_profile.value('AMW_NATRL_ACCT_VALUE_SET') into m_acc_value_set_id from dual;
1339 select AMW_FIN_KEY_ACCOUNTS_S.nextval into M_ACCOUNT_GROUP_ID from dual;
1340
1341 for accounts in Get_accounts_from_oraapps_gl(m_acc_value_set_id)
1342 loop
1343
1344 exit when Get_accounts_from_oraapps_gl%notfound;
1345
1346 M_NATURAL_ACCOUNT_ID := accounts.FLEX_VALUE_ID;
1347 M_NATURAL_ACCOUNT_VALUE := accounts.FLEX_VALUE ;
1348 M_PARENT_NATURAL_ACCOUNT_ID := NULL;
1349
1350 fnd_file.put_line(fnd_file.LOG, '------------------get_acc_from_oracle_apps-------------');
1351 fnd_file.put_line(fnd_file.LOG, 'Processing ACCOUNT_GROUP_ID=' || M_ACCOUNT_GROUP_ID);
1352 fnd_file.put_line(fnd_file.LOG, 'Processing NATURAL_ACCOUNT_ID=' || M_NATURAL_ACCOUNT_ID);
1353
1354
1355
1356 AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_ROW(
1357 X_ACCOUNT_GROUP_ID => M_ACCOUNT_GROUP_ID,
1358 X_NATURAL_ACCOUNT_ID => M_NATURAL_ACCOUNT_ID,
1359 X_NATURAL_ACCOUNT_VALUE => M_NATURAL_ACCOUNT_VALUE,
1360 X_END_DATE => NULL,
1361 X_LAST_UPDATE_DATE => SYSDATE,
1362 X_LAST_UPDATED_BY => g_user_id ,
1363 X_LAST_UPDATE_LOGIN => g_login_id,
1364 X_CREATION_DATE => SYSDATE,
1365 X_CREATED_BY => g_user_id ,
1366 X_ATTRIBUTE_CATEGORY => NULL,
1367 X_ATTRIBUTE1 => NULL,
1368 X_ATTRIBUTE2 => NULL,
1369 X_ATTRIBUTE3 => NULL,
1370 X_ATTRIBUTE4 => NULL,
1371 X_ATTRIBUTE5 => NULL,
1372 X_ATTRIBUTE6 => NULL,
1373 X_ATTRIBUTE7 => NULL,
1374 X_ATTRIBUTE8 => NULL,
1375 X_ATTRIBUTE9 => NULL,
1376 X_ATTRIBUTE10 => NULL,
1377 X_ATTRIBUTE11 => NULL,
1378 X_ATTRIBUTE12 => NULL,
1379 X_ATTRIBUTE13 => NULL,
1380 X_ATTRIBUTE14 => NULL,
1381 X_ATTRIBUTE15 => NULL,
1382 X_PARENT_NATURAL_ACCOUNT_ID => NULL );
1383
1384 AMW_IMPORT_STMNTS_ACCS_PKG.get_acc_name_from_oracle_apps( p_group_id => M_ACCOUNT_GROUP_ID
1385 ,p_flex_value_id => M_NATURAL_ACCOUNT_ID);
1386
1387 M_PARENT_NATURAL_ACCOUNT_ID := M_NATURAL_ACCOUNT_ID;
1388 for sub_accounts in Get_sub_acc_from_oraapps_gl(M_NATURAL_ACCOUNT_VALUE, m_acc_value_set_id)
1389 loop
1390 exit when Get_sub_acc_from_oraapps_gl%notfound;
1391
1392 C_NATURAL_ACCOUNT_VALUE := sub_accounts.Child_Flex_Value;
1393
1394 for sub_accounts_id in Get_sub_acc_id_oraapps_gl(C_NATURAL_ACCOUNT_VALUE, m_acc_value_set_id)
1395 loop
1396 exit when Get_sub_acc_id_oraapps_gl%notfound;
1397
1398 C_NATURAL_ACCOUNT_ID := sub_accounts_id.FLEX_VALUE_ID;
1399 end loop;
1400
1401
1402 fnd_file.put_line(fnd_file.LOG, '------------------ Processing Account - Parent Child Relationship -------------');
1403 fnd_file.put_line(fnd_file.LOG, 'Processing ACCOUNT_GROUP_ID=' || M_ACCOUNT_GROUP_ID);
1404 fnd_file.put_line(fnd_file.LOG, 'Processing NATURAL_ACCOUNT_ID=' || M_NATURAL_ACCOUNT_ID);
1405 fnd_file.put_line(fnd_file.LOG, 'Processing PARENT_NATURAL_ACCOUNT_ID=' || M_PARENT_NATURAL_ACCOUNT_ID);
1406
1407
1408
1409 AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_ROW(
1410 X_ACCOUNT_GROUP_ID => M_ACCOUNT_GROUP_ID,
1411 X_NATURAL_ACCOUNT_ID => C_NATURAL_ACCOUNT_ID,
1412 X_NATURAL_ACCOUNT_VALUE => C_NATURAL_ACCOUNT_VALUE ,
1413 X_END_DATE => NULL,
1414 X_LAST_UPDATE_DATE => SYSDATE,
1415 X_LAST_UPDATED_BY => g_user_id ,
1416 X_LAST_UPDATE_LOGIN => g_login_id,
1417 X_CREATION_DATE => SYSDATE,
1418 X_CREATED_BY => g_user_id ,
1419 X_ATTRIBUTE_CATEGORY => NULL,
1420 X_ATTRIBUTE1 => NULL,
1421 X_ATTRIBUTE2 => NULL,
1422 X_ATTRIBUTE3 => NULL,
1423 X_ATTRIBUTE4 => NULL,
1424 X_ATTRIBUTE5 => NULL,
1425 X_ATTRIBUTE6 => NULL,
1426 X_ATTRIBUTE7 => NULL,
1427 X_ATTRIBUTE8 => NULL,
1428 X_ATTRIBUTE9 => NULL,
1429 X_ATTRIBUTE10 => NULL,
1430 X_ATTRIBUTE11 => NULL,
1431 X_ATTRIBUTE12 => NULL,
1432 X_ATTRIBUTE13 => NULL,
1433 X_ATTRIBUTE14 => NULL,
1434 X_ATTRIBUTE15 => NULL,
1435 X_PARENT_NATURAL_ACCOUNT_ID => M_PARENT_NATURAL_ACCOUNT_ID);
1436
1437 -- AMW_IMPORT_STMNTS_ACCS_PKG.get_acc_name_from_oracle_apps( p_group_id => M_ACCOUNT_GROUP_ID ,p_flex_value_id => C_NATURAL_ACCOUNT_ID);
1438
1439 end loop;
1440
1441 end loop;
1442 update AMW_FIN_KEY_ACCOUNTS_B set end_date = sysdate
1443 where ACCOUNT_GROUP_ID<>M_ACCOUNT_GROUP_ID;
1444
1445 -- Sanket.
1446 amw_import_stmnts_accs_pkg.flatten_accounts ( x_group_id => m_account_group_id );
1447
1448 commit;
1449 END;
1450 EXCEPTION WHEN OTHERS THEN
1451 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
1452 -- dbms_output.put_line(SQLERRM);
1453 g_errbuf := SUBSTR (SQLERRM, 1, 2000) ;
1454 g_retcode := '2';
1455
1456 RAISE ;
1457 RETURN;
1458
1459 END get_acc_from_oracle_apps;
1460 ----------------------***************************************** -------------------------
1461 PROCEDURE get_acc_name_from_oracle_apps(p_group_id in number, p_flex_value_id in number) IS
1462 begin
1463 declare
1464 M_NAME VARCHAR2(240);
1465 M_LANGUAGE VARCHAR2(4);
1466 M_SOURCE_LANGUAGE VARCHAR2(4);
1467 M_OBJECT_TYPE VARCHAR2(10);
1468 M_SECURITY_GROUP_ID NUMBER;
1469 M_OBJECT_VERSION_NUMBER NUMBER;
1470 M_ORIG_SYSTEM_REFERENCE VARCHAR2(150);
1471
1472 P_ACCOUNT_GROUP_ID NUMBER :=p_group_id;
1473 P_NATURAL_ACCOUNT_ID NUMBER := p_flex_value_id;
1474
1475 cursor Get_acc_names_from_oraapps_gl
1476 (m_flex_value_id NUMBER)
1477 is
1478 Select
1479 FLEX_VALUE_ID,
1480 LANGUAGE,
1481 LAST_UPDATE_DATE,
1482 LAST_UPDATED_BY,
1483 CREATION_DATE,
1484 CREATED_BY,
1485 LAST_UPDATE_LOGIN,
1486 DESCRIPTION,
1487 SOURCE_LANG,
1488 FLEX_VALUE_MEANING
1489 -- SECURITY_GROUP_ID commented as prd environments did not have this field
1490 from
1491 FND_FLEX_VALUES_TL
1492 where
1493 FLEX_VALUE_ID= m_flex_value_id;
1494
1495
1496 begin
1497 for accounts_tl in Get_acc_names_from_oraapps_gl(p_flex_value_id)
1498 loop
1499
1500 exit when Get_acc_names_from_oraapps_gl%notfound;
1501 -- accounts_tl.FLEX_VALUE_ID;
1502 M_LANGUAGE := accounts_tl.LANGUAGE;
1503 M_NAME := accounts_tl.DESCRIPTION;
1504 M_SOURCE_LANGUAGE := accounts_tl.SOURCE_LANG;
1505 M_SECURITY_GROUP_ID := null ; -- commented as prd environments did not have this field accounts_tl.SECURITY_GROUP_ID;
1506
1507 fnd_file.put_line(fnd_file.LOG, '------------------ Get_acc_names_from_oraapps_gl -------------');
1508 fnd_file.put_line(fnd_file.LOG, 'Processing ACCOUNT_GROUP_ID=' || P_ACCOUNT_GROUP_ID);
1509 fnd_file.put_line(fnd_file.LOG, 'Processing NATURAL_ACCOUNT_ID=' || P_NATURAL_ACCOUNT_ID);
1510 fnd_file.put_line(fnd_file.LOG, 'Processing LANGUAGE=' || M_LANGUAGE);
1511 fnd_file.put_line(fnd_file.LOG, 'Processing NAME=' || M_NAME);
1512
1513
1514 AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_ROW_TL(
1515 X_ACCOUNT_GROUP_ID => P_ACCOUNT_GROUP_ID,
1516 X_NATURAL_ACCOUNT_ID => P_NATURAL_ACCOUNT_ID,
1517 X_NAME => M_NAME,
1518 X_LANGUAGE => M_LANGUAGE,
1519 X_SOURCE_LANGUAGE => M_SOURCE_LANGUAGE ,
1520 -- X_OBJECT_TYPE ,
1521 X_SECURITY_GROUP_ID => M_SECURITY_GROUP_ID ,
1522 X_OBJECT_VERSION_NUMBER => Null,
1523 X_ORIG_SYSTEM_REFERENCE => Null ,
1524 X_LAST_UPDATE_DATE => SYSDATE,
1525 X_LAST_UPDATED_BY => g_user_id ,
1526 X_LAST_UPDATE_LOGIN => g_login_id,
1527 X_CREATION_DATE => SYSDATE,
1528 X_CREATED_BY => g_user_id);
1529 end loop;
1530
1531
1532 END;
1533 EXCEPTION WHEN OTHERS THEN
1534 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
1535 -- dbms_output.put_line(SQLERRM);
1536 g_errbuf := SUBSTR (SQLERRM, 1, 2000) ;
1537 g_retcode := '2';
1538
1539 RAISE ;
1540 RETURN;
1541
1542 END get_acc_name_from_oracle_apps ;
1543
1544 ------------------------ ************************************* --------------------------
1545 PROCEDURE get_acc_from_external_apps IS
1546 begin
1547 declare
1548
1549 m_acc_value_set_id number;
1550 m_external_view_name VARCHAR2(150);
1551
1552 M_ACCOUNT_GROUP_ID NUMBER;
1553 M_NATURAL_ACCOUNT_ID NUMBER;
1554 M_NATURAL_ACCOUNT_VALUE VARCHAR2(150);
1555 M_END_DATE DATE;
1556 M_LAST_UPDATE_DATE DATE;
1557 M_LAST_UPDATED_BY NUMBER;
1558 M_LAST_UPDATE_LOGIN NUMBER;
1559 M_CREATION_DATE DATE;
1560 M_CREATED_BY NUMBER;
1561 M_ATTRIBUTE_CATEGORY VARCHAR2(30);
1562 M_ATTRIBUTE1 VARCHAR2(150);
1563 M_ATTRIBUTE2 VARCHAR2(150);
1564 M_ATTRIBUTE3 VARCHAR2(150);
1565 M_ATTRIBUTE4 VARCHAR2(150);
1566 M_ATTRIBUTE5 VARCHAR2(150);
1567 M_ATTRIBUTE6 VARCHAR2(150);
1568 M_ATTRIBUTE7 VARCHAR2(150);
1569 M_ATTRIBUTE8 VARCHAR2(150);
1570 M_ATTRIBUTE9 VARCHAR2(150);
1571 M_ATTRIBUTE10 VARCHAR2(150);
1572 M_ATTRIBUTE11 VARCHAR2(150);
1573 M_ATTRIBUTE12 VARCHAR2(150);
1574 M_ATTRIBUTE13 VARCHAR2(150);
1575 M_ATTRIBUTE14 VARCHAR2(150);
1576 M_ATTRIBUTE15 VARCHAR2(150);
1577 M_PARENT_NATURAL_ACCOUNT_ID NUMBER ;
1578
1579 M_NAME VARCHAR2(240);
1580 M_LANGUAGE VARCHAR2(4);
1581 M_SOURCE_LANGUAGE VARCHAR2(4);
1582 M_OBJECT_TYPE VARCHAR2(10);
1583 M_SECURITY_GROUP_ID NUMBER;
1584 M_OBJECT_VERSION_NUMBER NUMBER;
1585 M_ORIG_SYSTEM_REFERENCE VARCHAR2(150);
1586
1587
1588 /* cursor Get_acc_from_external_apps
1589 (external_view_name VARCHAR2)
1590 is
1591 */
1592
1593 /* ---------- commented used for testing only can be removed
1594 sql_for_acc varchar2(2000):=
1595 'SELECT
1596 NATURAL_ACCOUNT_ID,
1597 NATURAL_ACCOUNT_VALUE,
1598 PARENT_NATURAL_ACCOUNT_ID,
1599 ATTRIBUTE_CATEGORY,
1600 ATTRIBUTE1 ,
1601 ATTRIBUTE2 ,
1602 ATTRIBUTE3 ,
1603 ATTRIBUTE4 ,
1604 ATTRIBUTE5 ,
1605 ATTRIBUTE6 ,
1606 ATTRIBUTE7 ,
1607 ATTRIBUTE8 ,
1608 ATTRIBUTE9 ,
1609 ATTRIBUTE10,
1610 ATTRIBUTE11,
1611 ATTRIBUTE12,
1612 ATTRIBUTE13,
1613 ATTRIBUTE14,
1614 ATTRIBUTE15,
1615 SECURITY_GROUP_ID,
1616 OBJECT_VERSION_NUMBER
1617 from ' || fnd_profile.value('AMW_ACCOUNT_SOURCE_VIEW');
1618 ------------------------------------------------------
1619 */
1620
1621 sql_for_acc varchar2(2000):=
1622 'SELECT
1623 NATURAL_ACCOUNT_ID,
1624 NATURAL_ACCOUNT_VALUE,
1625 PARENT_NATURAL_ACCOUNT_ID
1626 from ' || fnd_profile.value('AMW_ACCOUNT_SOURCE_VIEW') ;
1627
1628 TYPE accounts_b IS RECORD (
1629 NATURAL_ACCOUNT_ID NUMBER,
1630 NATURAL_ACCOUNT_VALUE VARCHAR2(150),
1631 PARENT_NATURAL_ACCOUNT_ID NUMBER );
1632
1633 --TYPE accounts_b_record IS TABLE OF
1634 --SITE_RECORD;
1635
1636 accounts_b_record accounts_b;
1637
1638 --accounts_b_record AMW_FIN_KEY_ACCOUNTS_B%rowtype;
1639 TYPE get_acc_cursor IS ref CURSOR ;
1640 Get_acc_from_external_apps get_acc_cursor;
1641
1642
1643 /* cursor Get_acc_names_external_apps(external_view_name VARCHAR2) is
1644
1645 SELECT
1646 *
1647 from
1648 DUAL;
1649
1650 */
1651
1652 sql_for_acc_name varchar2(2000):=
1653 'SELECT
1654 NATURAL_ACCOUNT_ID,
1655 NAME ,
1656 LANGUAGE ,
1657 SOURCE_LANGUAGE
1658 from ' || fnd_profile.value('AMW_ACCOUNT_NAMES_VIEW');
1659
1660 TYPE accounts_tl IS RECORD (
1661 NATURAL_ACCOUNT_ID NUMBER,
1662 NAME VARCHAR2(80),
1663 LANGUAGE VARCHAR2(4),
1664 SOURCE_LANGUAGE VARCHAR2(4) );
1665
1666 accounts_tl_record accounts_tl;
1667
1668 --accounts_tl_record AMW_FIN_KEY_ACCOUNTS_TL%rowtype;
1669 TYPE get_accdesc_cursor IS ref CURSOR ;
1670 Get_acc_names_external_apps get_accdesc_cursor;
1671
1672
1673 begin
1674
1675 --update AMW_FIN_KEY_ACCOUNTS_B set end_date = sysdate;
1676 select AMW_FIN_KEY_ACCOUNTS_S.nextval into M_ACCOUNT_GROUP_ID from dual;
1677
1678
1679 open Get_acc_from_external_apps for sql_for_acc;
1680 loop
1681 fetch Get_acc_from_external_apps into accounts_b_record;
1682 exit when Get_acc_from_external_apps%notfound;
1683
1684 M_NATURAL_ACCOUNT_ID:= accounts_b_record.NATURAL_ACCOUNT_ID;
1685 M_NATURAL_ACCOUNT_VALUE := accounts_b_record.NATURAL_ACCOUNT_VALUE;
1686 M_PARENT_NATURAL_ACCOUNT_ID := accounts_b_record.PARENT_NATURAL_ACCOUNT_ID;
1687
1688 fnd_file.put_line(fnd_file.LOG, '------------------ Get_acc_from_external_apps -------------');
1689 fnd_file.put_line(fnd_file.LOG, 'Processing ACCOUNT_GROUP_ID=' || M_ACCOUNT_GROUP_ID);
1690 fnd_file.put_line(fnd_file.LOG, 'Processing NATURAL_ACCOUNT_ID=' || M_NATURAL_ACCOUNT_ID);
1691 fnd_file.put_line(fnd_file.LOG, 'Processing PARENT_NATURAL_ACCOUNT_ID=' || M_PARENT_NATURAL_ACCOUNT_ID);
1692
1693
1694 AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_ROW(
1695 X_ACCOUNT_GROUP_ID => M_ACCOUNT_GROUP_ID,
1696 X_NATURAL_ACCOUNT_ID => M_NATURAL_ACCOUNT_ID,
1697 X_NATURAL_ACCOUNT_VALUE => M_NATURAL_ACCOUNT_VALUE,
1698 X_END_DATE => NULL,
1699 X_LAST_UPDATE_DATE => SYSDATE,
1700 X_LAST_UPDATED_BY => g_user_id ,
1701 X_LAST_UPDATE_LOGIN => g_login_id,
1702 X_CREATION_DATE => SYSDATE,
1703 X_CREATED_BY => g_user_id ,
1704 X_ATTRIBUTE_CATEGORY => NULL,
1705 X_ATTRIBUTE1 => NULL,
1706 X_ATTRIBUTE2 => NULL,
1707 X_ATTRIBUTE3 => NULL,
1708 X_ATTRIBUTE4 => NULL,
1709 X_ATTRIBUTE5 => NULL,
1710 X_ATTRIBUTE6 => NULL,
1711 X_ATTRIBUTE7 => NULL,
1712 X_ATTRIBUTE8 => NULL,
1713 X_ATTRIBUTE9 => NULL,
1714 X_ATTRIBUTE10 => NULL,
1715 X_ATTRIBUTE11 => NULL,
1716 X_ATTRIBUTE12 => NULL,
1717 X_ATTRIBUTE13 => NULL,
1718 X_ATTRIBUTE14 => NULL,
1719 X_ATTRIBUTE15 => NULL,
1720 X_PARENT_NATURAL_ACCOUNT_ID => M_PARENT_NATURAL_ACCOUNT_ID);
1721
1722 end loop;
1723
1724 open Get_acc_names_external_apps for sql_for_acc_name;
1725 loop
1726 fetch Get_acc_names_external_apps into accounts_tl_record;
1727 exit when Get_acc_names_external_apps%notfound;
1728
1729 M_NATURAL_ACCOUNT_ID:= accounts_tl_record.NATURAL_ACCOUNT_ID;
1730 M_NAME := accounts_tl_record.NAME;
1731 M_LANGUAGE := accounts_tl_record.LANGUAGE;
1732 M_SOURCE_LANGUAGE := accounts_tl_record.SOURCE_LANGUAGE;
1733
1734 fnd_file.put_line(fnd_file.LOG, '------------------ Get_acc_names_external_apps -------------');
1735 fnd_file.put_line(fnd_file.LOG, 'Processing ACCOUNT_GROUP_ID=' || M_ACCOUNT_GROUP_ID);
1736 fnd_file.put_line(fnd_file.LOG, 'Processing NATURAL_ACCOUNT_ID=' || M_NATURAL_ACCOUNT_ID);
1737 fnd_file.put_line(fnd_file.LOG, 'Processing LANGUAGE=' || M_LANGUAGE);
1738 fnd_file.put_line(fnd_file.LOG, 'Processing NAME=' || M_NAME);
1739
1740
1741
1742 AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_ROW_TL(
1743 X_ACCOUNT_GROUP_ID => M_ACCOUNT_GROUP_ID,
1744 X_NATURAL_ACCOUNT_ID => M_NATURAL_ACCOUNT_ID ,
1745 X_NAME => M_NAME,
1746 X_LANGUAGE => M_LANGUAGE,
1747 X_SOURCE_LANGUAGE => M_SOURCE_LANGUAGE ,
1748 -- X_OBJECT_TYPE ,
1749 X_SECURITY_GROUP_ID => M_SECURITY_GROUP_ID ,
1750 X_OBJECT_VERSION_NUMBER => Null,
1751 X_ORIG_SYSTEM_REFERENCE => Null ,
1752 X_LAST_UPDATE_DATE => SYSDATE,
1753 X_LAST_UPDATED_BY => g_user_id ,
1754 X_LAST_UPDATE_LOGIN => g_login_id,
1755 X_CREATION_DATE => SYSDATE,
1756 X_CREATED_BY => g_user_id);
1757
1758 end loop;
1759 update AMW_FIN_KEY_ACCOUNTS_B set end_date = sysdate
1760 where ACCOUNT_GROUP_ID<>M_ACCOUNT_GROUP_ID;
1761
1762
1763 -- Sanket.
1764 amw_import_stmnts_accs_pkg.flatten_accounts ( x_group_id => m_account_group_id );
1765
1766 commit;
1767 END;
1768
1769 EXCEPTION WHEN OTHERS THEN
1770 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
1771 -- dbms_output.put_line(SQLERRM);
1772 g_errbuf := SUBSTR (SQLERRM, 1, 2000) ;
1773 g_retcode := '2';
1774
1775 RAISE ;
1776 RETURN;
1777
1778 END get_acc_from_external_apps;
1779
1780 -------------------------------------------------------------------------------------
1781 procedure INSERT_ROW (
1782 X_ACCOUNT_GROUP_ID in out NOCOPY NUMBER,
1783 X_NATURAL_ACCOUNT_ID in NUMBER,
1784 X_NATURAL_ACCOUNT_VALUE in VARCHAR2,
1785 X_END_DATE in DATE,
1786 X_LAST_UPDATE_DATE in DATE,
1787 X_LAST_UPDATED_BY in NUMBER,
1788 X_LAST_UPDATE_LOGIN in NUMBER,
1789 X_CREATION_DATE IN DATE,
1790 X_CREATED_BY in NUMBER,
1791 X_ATTRIBUTE_CATEGORY in VARCHAR2,
1792 X_ATTRIBUTE1 in VARCHAR2,
1793 X_ATTRIBUTE2 in VARCHAR2,
1794 X_ATTRIBUTE3 in VARCHAR2,
1795 X_ATTRIBUTE4 in VARCHAR2,
1796 X_ATTRIBUTE5 in VARCHAR2,
1797 X_ATTRIBUTE6 in VARCHAR2,
1798 X_ATTRIBUTE7 in VARCHAR2,
1799 X_ATTRIBUTE8 in VARCHAR2,
1800 X_ATTRIBUTE9 in VARCHAR2,
1801 X_ATTRIBUTE10 in VARCHAR2,
1802 X_ATTRIBUTE11 in VARCHAR2,
1803 X_ATTRIBUTE12 in VARCHAR2,
1804 X_ATTRIBUTE13 in VARCHAR2,
1805 X_ATTRIBUTE14 in VARCHAR2,
1806 X_ATTRIBUTE15 in VARCHAR2,
1807 X_PARENT_NATURAL_ACCOUNT_ID in NUMBER) is
1808
1809 -- cursor C is select NATURAL_ACCOUNT_ID from AMW_FIN_KEY_ACCOUNTS_B
1810 -- where ACCOUNT_GROUP_ID = X_ACCOUNT_GROUP_ID;
1811
1812 var_NATURAL_ACCOUNT_ID number;
1813
1814 begin
1815 select
1816 NATURAL_ACCOUNT_ID into var_NATURAL_ACCOUNT_ID
1817 from AMW_FIN_KEY_ACCOUNTS_B
1818 where ACCOUNT_GROUP_ID = X_ACCOUNT_GROUP_ID AND
1819 NATURAL_ACCOUNT_ID = X_NATURAL_ACCOUNT_ID AND
1820 NVL(PARENT_NATURAL_ACCOUNT_ID,-1)= NVL(X_PARENT_NATURAL_ACCOUNT_ID,-1);
1821 EXCEPTION
1822 WHEN NO_DATA_FOUND THEN
1823
1824 insert into AMW_FIN_KEY_ACCOUNTS_B (
1825 ACCOUNT_GROUP_ID,
1826 NATURAL_ACCOUNT_ID,
1827 NATURAL_ACCOUNT_VALUE,
1828 END_DATE,
1829 LAST_UPDATE_DATE,
1830 LAST_UPDATED_BY,
1831 LAST_UPDATE_LOGIN,
1832 CREATION_DATE,
1833 CREATED_BY,
1834 ATTRIBUTE_CATEGORY,
1835 ATTRIBUTE1,
1836 ATTRIBUTE2,
1837 ATTRIBUTE3,
1838 ATTRIBUTE4,
1839 ATTRIBUTE5,
1840 ATTRIBUTE6,
1841 ATTRIBUTE7,
1842 ATTRIBUTE8,
1843 ATTRIBUTE9,
1844 ATTRIBUTE10,
1845 ATTRIBUTE11,
1846 ATTRIBUTE12,
1847 ATTRIBUTE13,
1848 ATTRIBUTE14,
1849 ATTRIBUTE15,
1850 PARENT_NATURAL_ACCOUNT_ID
1851 ) values (
1852 X_ACCOUNT_GROUP_ID ,
1853 X_NATURAL_ACCOUNT_ID ,
1854 X_NATURAL_ACCOUNT_VALUE,
1855 X_END_DATE ,
1856 X_LAST_UPDATE_DATE ,
1857 X_LAST_UPDATED_BY ,
1858 X_LAST_UPDATE_LOGIN ,
1859 X_CREATION_DATE ,
1860 X_CREATED_BY ,
1861 X_ATTRIBUTE_CATEGORY,
1862 X_ATTRIBUTE1,
1863 X_ATTRIBUTE2,
1864 X_ATTRIBUTE3,
1865 X_ATTRIBUTE4,
1866 X_ATTRIBUTE5,
1867 X_ATTRIBUTE6,
1868 X_ATTRIBUTE7,
1869 X_ATTRIBUTE8,
1870 X_ATTRIBUTE9,
1871 X_ATTRIBUTE10,
1872 X_ATTRIBUTE11,
1873 X_ATTRIBUTE12,
1874 X_ATTRIBUTE13,
1875 X_ATTRIBUTE14,
1876 X_ATTRIBUTE15,
1877 X_PARENT_NATURAL_ACCOUNT_ID
1878 );
1879
1880 /* open c;
1881 fetch c into X_ROWID;
1882 if (c%notfound) then
1883 close c;
1884 raise no_data_found;
1885 end if;
1886 close c;
1887 */
1888 WHEN OTHERS THEN
1889 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
1890 -- dbms_output.put_line(SQLERRM);
1891 fnd_file.put_line(fnd_file.LOG, 'ACCOUNT_GROUP_ID' || X_ACCOUNT_GROUP_ID );
1892 fnd_file.put_line(fnd_file.LOG, 'NATURAL_ACCOUNT_ID ' || X_NATURAL_ACCOUNT_ID );
1893 fnd_file.put_line(fnd_file.LOG, 'PARENT_NATURAL_ACCOUNT_ID' || X_PARENT_NATURAL_ACCOUNT_ID );
1894
1895 g_errbuf := SUBSTR (SQLERRM, 1, 2000) ;
1896 g_retcode := '2';
1897
1898 RAISE ;
1899 RETURN;
1900
1901 end INSERT_ROW;
1902 -----------------------------------------------------------------------------------------------------
1903 procedure INSERT_ROW_TL (
1904
1905 X_ACCOUNT_GROUP_ID in out NOCOPY NUMBER,
1906 X_NATURAL_ACCOUNT_ID in NUMBER,
1907 X_NAME in VARCHAR2,
1908 X_LANGUAGE in VARCHAR2,
1909 X_SOURCE_LANGUAGE in VARCHAR2,
1910 -- X_OBJECT_TYPE VARCHAR2,
1911 X_SECURITY_GROUP_ID in NUMBER,
1912 X_OBJECT_VERSION_NUMBER in NUMBER,
1913 X_ORIG_SYSTEM_REFERENCE in VARCHAR2,
1914 X_LAST_UPDATE_DATE in DATE,
1915 X_LAST_UPDATED_BY in NUMBER,
1916 X_LAST_UPDATE_LOGIN in NUMBER,
1917 X_CREATION_DATE DATE,
1918 X_CREATED_BY in NUMBER
1919 ) is
1920 -- cursor C is select NATURAL_ACCOUNT_ID from AMW_FIN_KEY_ACCOUNTS_TL
1921 -- where ACCOUNT_GROUP_ID = X_ACCOUNT_GROUP_ID ;
1922 var_NATURAL_ACCOUNT_ID number;
1923 begin
1924
1925 select
1926 NATURAL_ACCOUNT_ID into var_NATURAL_ACCOUNT_ID
1927 from AMW_FIN_KEY_ACCOUNTS_TL
1928 where ACCOUNT_GROUP_ID = X_ACCOUNT_GROUP_ID AND
1929 NATURAL_ACCOUNT_ID = X_NATURAL_ACCOUNT_ID AND
1930 LANGUAGE = X_LANGUAGE;
1931 EXCEPTION
1932 WHEN NO_DATA_FOUND THEN
1933
1934 insert into AMW_FIN_KEY_ACCOUNTS_TL (
1935 ACCOUNT_GROUP_ID,
1936 NATURAL_ACCOUNT_ID,
1937 NAME,
1938 LANGUAGE,
1939 SOURCE_LANGUAGE,
1940 --OBJECT_TYPE
1941 CREATED_BY,
1942 CREATION_DATE,
1943 LAST_UPDATED_BY,
1944 LAST_UPDATE_DATE,
1945 LAST_UPDATE_LOGIN,
1946 SECURITY_GROUP_ID,
1947 OBJECT_VERSION_NUMBER,
1948 SOURCE_LANG
1949 -- ORIG_SYSTEM_REFERENCE
1950 ) values (
1951 X_ACCOUNT_GROUP_ID,
1952 X_NATURAL_ACCOUNT_ID,
1953 X_NAME,
1954 X_LANGUAGE,
1955 X_SOURCE_LANGUAGE,
1956 --X_OBJECT_TYPE,
1957 X_CREATED_BY,
1958 X_CREATION_DATE,
1959 X_LAST_UPDATED_BY,
1960 X_LAST_UPDATE_DATE,
1961 X_LAST_UPDATE_LOGIN,
1962 X_SECURITY_GROUP_ID,
1963 X_OBJECT_VERSION_NUMBER,
1964 X_SOURCE_LANGUAGE
1965 --X_ORIG_SYSTEM_REFERENCE
1966 -- userenv('LANG')
1967 );
1968 /* open c;
1969 fetch c into X_ROWID;
1970 if (c%notfound) then
1971 close c;
1972 raise no_data_found;
1973 end if;
1974 close c;
1975 */
1976 WHEN OTHERS THEN
1977 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
1978 -- dbms_output.put_line(SQLERRM);
1979 g_errbuf := SUBSTR (SQLERRM, 1, 2000) ;
1980 g_retcode := '2';
1981 fnd_file.put_line(fnd_file.LOG, 'ACCOUNT_GROUP_ID=' || X_ACCOUNT_GROUP_ID );
1982 fnd_file.put_line(fnd_file.LOG, 'NATURAL_ACCOUNT_ID=' || X_NATURAL_ACCOUNT_ID );
1983 fnd_file.put_line(fnd_file.LOG, 'LANGUAGE=' || X_LANGUAGE);
1984
1985
1986 RAISE ;
1987 RETURN;
1988
1989 end INSERT_ROW_TL;
1990 ------------------------------------- ************************************ --------------------------------
1991 procedure INSERT_STMNT_ROW (
1992 X_STATEMENT_GROUP_ID in NUMBER,
1993 X_FINANCIAL_STATEMENT_ID in NUMBER,
1994 X_END_DATE in DATE,
1995 X_LAST_UPDATE_DATE in DATE,
1996 X_LAST_UPDATED_BY in NUMBER,
1997 X_LAST_UPDATE_LOGIN in NUMBER,
1998 X_CREATION_DATE in DATE,
1999 X_CREATED_BY in NUMBER,
2000 X_ATTRIBUTE_CATEGORY in VARCHAR2,
2001 X_ATTRIBUTE1 in VARCHAR2,
2002 X_ATTRIBUTE2 in VARCHAR2,
2003 X_ATTRIBUTE3 in VARCHAR2,
2004 X_ATTRIBUTE4 in VARCHAR2,
2005 X_ATTRIBUTE5 in VARCHAR2,
2006 X_ATTRIBUTE6 in VARCHAR2,
2007 X_ATTRIBUTE7 in VARCHAR2,
2008 X_ATTRIBUTE8 in VARCHAR2,
2009 X_ATTRIBUTE9 in VARCHAR2,
2010 X_ATTRIBUTE10 in VARCHAR2,
2011 X_ATTRIBUTE11 in VARCHAR2,
2012 X_ATTRIBUTE12 in VARCHAR2,
2013 X_ATTRIBUTE13 in VARCHAR2,
2014 X_ATTRIBUTE14 in VARCHAR2,
2015 X_ATTRIBUTE15 in VARCHAR2,
2016 X_SECURITY_GROUP_ID in NUMBER,
2017 X_OBJECT_VERSION_NUMBER in NUMBER)
2018 is
2019
2020 var_STATEMENT_ID number ;
2021 begin
2022
2023 select
2024 FINANCIAL_STATEMENT_ID into var_STATEMENT_ID
2025 from
2026 AMW_FIN_STMNT_B
2027 where
2028 STATEMENT_GROUP_ID = X_STATEMENT_GROUP_ID and
2029 FINANCIAL_STATEMENT_ID = X_FINANCIAL_STATEMENT_ID;
2030 EXCEPTION
2031 WHEN NO_DATA_FOUND THEN
2032 insert into AMW_FIN_STMNT_B(
2033 STATEMENT_GROUP_ID,
2034 FINANCIAL_STATEMENT_ID,
2035 END_DATE ,
2036 LAST_UPDATE_DATE ,
2037 LAST_UPDATED_BY ,
2038 LAST_UPDATE_LOGIN ,
2039 CREATION_DATE ,
2040 CREATED_BY ,
2041 ATTRIBUTE_CATEGORY ,
2042 ATTRIBUTE1 ,
2043 ATTRIBUTE2 ,
2044 ATTRIBUTE3 ,
2045 ATTRIBUTE4 ,
2046 ATTRIBUTE5 ,
2047 ATTRIBUTE6 ,
2048 ATTRIBUTE7 ,
2049 ATTRIBUTE8 ,
2050 ATTRIBUTE9 ,
2051 ATTRIBUTE10 ,
2052 ATTRIBUTE11 ,
2053 ATTRIBUTE12 ,
2054 ATTRIBUTE13 ,
2055 ATTRIBUTE14 ,
2056 ATTRIBUTE15 ,
2057 SECURITY_GROUP_ID ,
2058 OBJECT_VERSION_NUMBER)
2059 values
2060 (
2061 X_STATEMENT_GROUP_ID ,
2062 X_FINANCIAL_STATEMENT_ID ,
2063 X_END_DATE ,
2064 X_LAST_UPDATE_DATE ,
2065 X_LAST_UPDATED_BY ,
2066 X_LAST_UPDATE_LOGIN ,
2067 X_CREATION_DATE ,
2068 X_CREATED_BY ,
2069 X_ATTRIBUTE_CATEGORY ,
2070 X_ATTRIBUTE1 ,
2071 X_ATTRIBUTE2 ,
2072 X_ATTRIBUTE3 ,
2073 X_ATTRIBUTE4 ,
2074 X_ATTRIBUTE5 ,
2075 X_ATTRIBUTE6 ,
2076 X_ATTRIBUTE7 ,
2077 X_ATTRIBUTE8 ,
2078 X_ATTRIBUTE9 ,
2079 X_ATTRIBUTE10 ,
2080 X_ATTRIBUTE11 ,
2081 X_ATTRIBUTE12 ,
2082 X_ATTRIBUTE13 ,
2083 X_ATTRIBUTE14 ,
2084 X_ATTRIBUTE15 ,
2085 X_SECURITY_GROUP_ID ,
2086 X_OBJECT_VERSION_NUMBER );
2087
2088 WHEN OTHERS THEN
2089 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
2090 -- dbms_output.put_line(SQLERRM);
2091 g_errbuf := SUBSTR (SQLERRM, 1, 2000) ;
2092 g_retcode := '2';
2093 fnd_file.put_line(fnd_file.LOG, 'STATEMENT_GROUP_ID =' || X_STATEMENT_GROUP_ID );
2094 fnd_file.put_line(fnd_file.LOG, 'FINANCIAL_STATEMENT_ID =' || X_FINANCIAL_STATEMENT_ID);
2095
2096 RAISE ;
2097 RETURN;
2098
2099 END INSERT_STMNT_ROW;
2100 --------------------------------------- ********************************************* ----------------------------
2101 procedure INSERT_STMNT_ROW_TL (
2102 X_STATEMENT_GROUP_ID in NUMBER,
2103 X_FINANCIAL_STATEMENT_ID in NUMBER,
2104 X_NAME in VARCHAR2,
2105 X_LANGUAGE in VARCHAR2,
2106 X_SOURCE_LANGUAGE in VARCHAR2,
2107 X_SECURITY_GROUP_ID in NUMBER,
2108 X_OBJECT_VERSION_NUMBER in NUMBER,
2109 X_ORIG_SYSTEM_REFERENCE in VARCHAR2,
2110 X_LAST_UPDATE_DATE in DATE,
2111 X_LAST_UPDATED_BY in NUMBER,
2112 X_LAST_UPDATE_LOGIN in NUMBER,
2113 X_CREATION_DATE DATE,
2114 X_CREATED_BY in NUMBER
2115 ) is
2116 --cursor C is select NATURAL_ACCOUNT_ID from AMW_FIN_KEY_ACCOUNTS_TL
2117 -- where ACCOUNT_GROUP_ID = X_ACCOUNT_GROUP_ID;
2118
2119 var_STATEMENT_ID number ;
2120 begin
2121 select
2122 FINANCIAL_STATEMENT_ID into var_STATEMENT_ID
2123 from
2124 AMW_FIN_STMNT_tl where
2125 STATEMENT_GROUP_ID = X_STATEMENT_GROUP_ID and
2126 FINANCIAL_STATEMENT_ID = X_FINANCIAL_STATEMENT_ID and
2127 LANGUAGE =X_LANGUAGE ;
2128 EXCEPTION
2129 WHEN NO_DATA_FOUND THEN
2130
2131 insert into AMW_FIN_STMNT_tl(
2132 STATEMENT_GROUP_ID,
2133 FINANCIAL_STATEMENT_ID,
2134 NAME,
2135 LANGUAGE,
2136 SOURCE_LANGUAGE,
2137 CREATED_BY,
2138 CREATION_DATE,
2139 LAST_UPDATED_BY,
2140 LAST_UPDATE_DATE,
2141 LAST_UPDATE_LOGIN,
2142 SECURITY_GROUP_ID,
2143 OBJECT_VERSION_NUMBER,
2144 SOURCE_LANG
2145 ) values (
2146 X_STATEMENT_GROUP_ID ,
2147 X_FINANCIAL_STATEMENT_ID ,
2148 X_NAME,
2149 X_LANGUAGE,
2150 X_SOURCE_LANGUAGE,
2151 X_CREATED_BY,
2152 X_CREATION_DATE,
2153 X_LAST_UPDATED_BY,
2154 X_LAST_UPDATE_DATE,
2155 X_LAST_UPDATE_LOGIN,
2156 X_SECURITY_GROUP_ID,
2157 X_OBJECT_VERSION_NUMBER,
2158 X_SOURCE_LANGUAGE
2159 --X_ORIG_SYSTEM_REFERENCE
2160 -- userenv('LANG')
2161 );
2162 /* open c;
2163 fetch c into X_ROWID;
2164 if (c%notfound) then
2165 close c;
2166 raise no_data_found;
2167 end if;
2168 close c;
2169 */
2170 WHEN OTHERS THEN
2171 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
2172 -- dbms_output.put_line(SQLERRM);
2173 g_errbuf := SUBSTR (SQLERRM, 1, 2000) ;
2174 g_retcode := '2';
2175 fnd_file.put_line(fnd_file.LOG, 'STATEMENT_GROUP_ID =' || X_STATEMENT_GROUP_ID );
2176 fnd_file.put_line(fnd_file.LOG, 'FINANCIAL_STATEMENT_ID =' || X_FINANCIAL_STATEMENT_ID);
2177 fnd_file.put_line(fnd_file.LOG, 'LANGUAGE=' || X_LANGUAGE);
2178
2179
2180 RAISE ;
2181 RETURN;
2182
2183 end INSERT_STMNT_ROW_TL;
2184 ------------------------------------- ************************************ --------------------------------
2185 procedure INSERT_FINITEM_ROW (
2186 X_STATEMENT_GROUP_ID in NUMBER,
2187 X_FINANCIAL_STATEMENT_ID in NUMBER,
2188 X_FINANCIAL_ITEM_ID IN NUMBER,
2189 X_PARENT_FINANCIAL_ITEM_ID IN NUMBER,
2190 X_SEQUENCE_NUMBER in NUMBER,
2191 X_LAST_UPDATE_DATE in DATE,
2192 X_LAST_UPDATED_BY in NUMBER,
2193 X_LAST_UPDATE_LOGIN in NUMBER,
2194 X_CREATION_DATE in DATE,
2195 X_CREATED_BY in NUMBER,
2196 X_ATTRIBUTE_CATEGORY in VARCHAR2,
2197 X_ATTRIBUTE1 in VARCHAR2,
2198 X_ATTRIBUTE2 in VARCHAR2,
2199 X_ATTRIBUTE3 in VARCHAR2,
2200 X_ATTRIBUTE4 in VARCHAR2,
2201 X_ATTRIBUTE5 in VARCHAR2,
2202 X_ATTRIBUTE6 in VARCHAR2,
2203 X_ATTRIBUTE7 in VARCHAR2,
2204 X_ATTRIBUTE8 in VARCHAR2,
2205 X_ATTRIBUTE9 in VARCHAR2,
2206 X_ATTRIBUTE10 in VARCHAR2,
2207 X_ATTRIBUTE11 in VARCHAR2,
2208 X_ATTRIBUTE12 in VARCHAR2,
2209 X_ATTRIBUTE13 in VARCHAR2,
2210 X_ATTRIBUTE14 in VARCHAR2,
2211 X_ATTRIBUTE15 in VARCHAR2,
2212 X_SECURITY_GROUP_ID in NUMBER,
2213 X_OBJECT_VERSION_NUMBER in NUMBER)
2214 is
2215 var_ITEM_ID number;
2216 begin
2217 select
2218 FINANCIAL_ITEM_ID INTO var_ITEM_ID
2219 from
2220 AMW_FIN_STMNT_ITEMS_B
2221 where
2222 STATEMENT_GROUP_ID = X_STATEMENT_GROUP_ID and
2223 FINANCIAL_STATEMENT_ID = X_FINANCIAL_STATEMENT_ID and
2224 FINANCIAL_ITEM_ID = X_FINANCIAL_ITEM_ID and
2225 nvl(PARENT_FINANCIAL_ITEM_ID,-1) = nvl(X_PARENT_FINANCIAL_ITEM_ID,-1);
2226
2227 EXCEPTION
2228 WHEN NO_DATA_FOUND THEN
2229
2230 insert into AMW_FIN_STMNT_ITEMS_B(
2231 STATEMENT_GROUP_ID,
2232 FINANCIAL_STATEMENT_ID,
2233 FINANCIAL_ITEM_ID,
2234 PARENT_FINANCIAL_ITEM_ID,
2235 SEQUENCE_NUMBER ,
2236 LAST_UPDATE_DATE ,
2237 LAST_UPDATED_BY ,
2238 LAST_UPDATE_LOGIN ,
2239 CREATION_DATE ,
2240 CREATED_BY ,
2241 ATTRIBUTE_CATEGORY ,
2242 ATTRIBUTE1 ,
2243 ATTRIBUTE2 ,
2244 ATTRIBUTE3 ,
2245 ATTRIBUTE4 ,
2246 ATTRIBUTE5 ,
2247 ATTRIBUTE6 ,
2248 ATTRIBUTE7 ,
2249 ATTRIBUTE8 ,
2250 ATTRIBUTE9 ,
2251 ATTRIBUTE10 ,
2252 ATTRIBUTE11 ,
2253 ATTRIBUTE12 ,
2254 ATTRIBUTE13 ,
2255 ATTRIBUTE14 ,
2256 ATTRIBUTE15 ,
2257 SECURITY_GROUP_ID ,
2258 OBJECT_VERSION_NUMBER)
2259 values
2260 (
2261 X_STATEMENT_GROUP_ID ,
2262 X_FINANCIAL_STATEMENT_ID ,
2263 X_FINANCIAL_ITEM_ID ,
2264 X_PARENT_FINANCIAL_ITEM_ID ,
2265 X_SEQUENCE_NUMBER ,
2266 X_LAST_UPDATE_DATE ,
2267 X_LAST_UPDATED_BY ,
2268 X_LAST_UPDATE_LOGIN ,
2269 X_CREATION_DATE ,
2270 X_CREATED_BY ,
2271 X_ATTRIBUTE_CATEGORY ,
2272 X_ATTRIBUTE1 ,
2273 X_ATTRIBUTE2 ,
2274 X_ATTRIBUTE3 ,
2275 X_ATTRIBUTE4 ,
2276 X_ATTRIBUTE5 ,
2277 X_ATTRIBUTE6 ,
2278 X_ATTRIBUTE7 ,
2279 X_ATTRIBUTE8 ,
2280 X_ATTRIBUTE9 ,
2281 X_ATTRIBUTE10 ,
2282 X_ATTRIBUTE11 ,
2283 X_ATTRIBUTE12 ,
2284 X_ATTRIBUTE13 ,
2285 X_ATTRIBUTE14 ,
2286 X_ATTRIBUTE15 ,
2287 X_SECURITY_GROUP_ID ,
2288 X_OBJECT_VERSION_NUMBER );
2289
2290 WHEN OTHERS THEN
2291 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
2292 -- dbms_output.put_line(SQLERRM);
2293 g_errbuf := SUBSTR (SQLERRM, 1, 2000) ;
2294 g_retcode := '2';
2295 fnd_file.put_line(fnd_file.LOG, 'STATEMENT_GROUP_ID =' || X_STATEMENT_GROUP_ID );
2296 fnd_file.put_line(fnd_file.LOG, 'FINANCIAL_STATEMENT_ID =' || X_FINANCIAL_STATEMENT_ID);
2297 fnd_file.put_line(fnd_file.LOG, 'FINANCIAL_ITEM_ID=' || X_FINANCIAL_ITEM_ID);
2298 fnd_file.put_line(fnd_file.LOG, 'PARENT_FINANCIAL_ITEM_ID =' || X_PARENT_FINANCIAL_ITEM_ID );
2299
2300
2301 RAISE ;
2302 RETURN;
2303
2304 END INSERT_FINITEM_ROW ;
2305 --------------------------------------- ********************************************* ----------------------------
2306 procedure INSERT_FINITEM_ROW_TL (
2307 X_STATEMENT_GROUP_ID in NUMBER,
2308 X_FINANCIAL_STATEMENT_ID in NUMBER,
2309 X_FINANCIAL_ITEM_ID IN NUMBER,
2310 X_NAME in VARCHAR2,
2311 X_LANGUAGE in VARCHAR2,
2312 X_SOURCE_LANGUAGE in VARCHAR2,
2313 X_SECURITY_GROUP_ID in NUMBER,
2314 X_OBJECT_VERSION_NUMBER in NUMBER,
2315 X_ORIG_SYSTEM_REFERENCE in VARCHAR2,
2316 X_LAST_UPDATE_DATE in DATE,
2317 X_LAST_UPDATED_BY in NUMBER,
2318 X_LAST_UPDATE_LOGIN in NUMBER,
2319 X_CREATION_DATE DATE,
2320 X_CREATED_BY in NUMBER
2321 ) is
2322 --cursor C is select NATURAL_ACCOUNT_ID from AMW_FIN_KEY_ACCOUNTS_TL
2323 -- where ACCOUNT_GROUP_ID = X_ACCOUNT_GROUP_ID;
2324
2325 var_ITEM_ID NUMBER;
2326 begin
2327
2328 select FINANCIAL_ITEM_ID INTO var_ITEM_ID
2329 from
2330 AMW_FIN_STMNT_ITEMS_tl
2331 where
2332 STATEMENT_GROUP_ID = X_STATEMENT_GROUP_ID and
2333 FINANCIAL_STATEMENT_ID = X_FINANCIAL_STATEMENT_ID and
2334 FINANCIAL_ITEM_ID = X_FINANCIAL_ITEM_ID and
2335 LANGUAGE=X_LANGUAGE ;
2336 EXCEPTION
2337 WHEN NO_DATA_FOUND THEN
2338
2339 insert into AMW_FIN_STMNT_ITEMS_TL(
2340 STATEMENT_GROUP_ID,
2341 FINANCIAL_STATEMENT_ID,
2342 FINANCIAL_ITEM_ID,
2343 NAME,
2344 LANGUAGE,
2345 SOURCE_LANGUAGE,
2346 CREATED_BY,
2347 CREATION_DATE,
2348 LAST_UPDATED_BY,
2349 LAST_UPDATE_DATE,
2350 LAST_UPDATE_LOGIN,
2351 SECURITY_GROUP_ID,
2352 OBJECT_VERSION_NUMBER,
2353 SOURCE_LANG
2354 ) values (
2355 X_STATEMENT_GROUP_ID ,
2356 X_FINANCIAL_STATEMENT_ID ,
2357 X_FINANCIAL_ITEM_ID ,
2358 X_NAME,
2359 X_LANGUAGE,
2360 X_SOURCE_LANGUAGE,
2361 X_CREATED_BY,
2362 X_CREATION_DATE,
2363 X_LAST_UPDATED_BY,
2364 X_LAST_UPDATE_DATE,
2365 X_LAST_UPDATE_LOGIN,
2366 X_SECURITY_GROUP_ID,
2367 X_OBJECT_VERSION_NUMBER,
2368 X_SOURCE_LANGUAGE
2369 --X_ORIG_SYSTEM_REFERENCE
2370 -- userenv('LANG')
2371 );
2372 /* open c;
2373 fetch c into X_ROWID;
2374 if (c%notfound) then
2375 close c;
2376 raise no_data_found;
2377 end if;
2378 close c;
2379 */
2380 WHEN OTHERS THEN
2381 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
2382 -- dbms_output.put_line(SQLERRM);
2383 g_errbuf := SUBSTR (SQLERRM, 1, 2000) ;
2384 g_retcode := '2';
2385 fnd_file.put_line(fnd_file.LOG, 'STATEMENT_GROUP_ID =' || X_STATEMENT_GROUP_ID );
2386 fnd_file.put_line(fnd_file.LOG, 'FINANCIAL_STATEMENT_ID =' || X_FINANCIAL_STATEMENT_ID);
2387 fnd_file.put_line(fnd_file.LOG, 'FINANCIAL_ITEM_ID=' || X_FINANCIAL_ITEM_ID);
2388 fnd_file.put_line(fnd_file.LOG, 'LANGUAGE=' || X_LANGUAGE);
2389
2390
2391 RAISE ;
2392 RETURN;
2393
2394 end INSERT_FINITEM_ROW_TL ;
2395
2396 ---------------------------------------***************************************------------------------------
2397 procedure INSERT_FINITEM_ACC_MAP (
2398 X_STATEMENT_GROUP_ID in NUMBER,
2399 X_ACCOUNT_GROUP_ID in NUMBER,
2400 X_FINANCIAL_STATEMENT_ID in NUMBER,
2401 X_FINANCIAL_ITEM_ID IN NUMBER,
2402 X_NATURAL_ACCOUNT_ID in NUMBER,
2403 X_LAST_UPDATE_DATE in DATE,
2404 X_LAST_UPDATED_BY in NUMBER,
2405 X_LAST_UPDATE_LOGIN in NUMBER,
2406 X_CREATION_DATE in DATE,
2407 X_CREATED_BY in NUMBER,
2408 X_ATTRIBUTE_CATEGORY in VARCHAR2,
2409 X_ATTRIBUTE1 in VARCHAR2,
2410 X_ATTRIBUTE2 in VARCHAR2,
2411 X_ATTRIBUTE3 in VARCHAR2,
2412 X_ATTRIBUTE4 in VARCHAR2,
2413 X_ATTRIBUTE5 in VARCHAR2,
2414 X_ATTRIBUTE6 in VARCHAR2,
2415 X_ATTRIBUTE7 in VARCHAR2,
2416 X_ATTRIBUTE8 in VARCHAR2,
2417 X_ATTRIBUTE9 in VARCHAR2,
2418 X_ATTRIBUTE10 in VARCHAR2,
2419 X_ATTRIBUTE11 in VARCHAR2,
2420 X_ATTRIBUTE12 in VARCHAR2,
2421 X_ATTRIBUTE13 in VARCHAR2,
2422 X_ATTRIBUTE14 in VARCHAR2,
2423 X_ATTRIBUTE15 in VARCHAR2,
2424 X_SECURITY_GROUP_ID in NUMBER,
2425 X_OBJECT_VERSION_NUMBER in NUMBER)
2426 is
2427 begin
2428 DECLARE
2429 itmacc_count NUMBER :=0;
2430
2431 BEGIN
2432 select count(1) into itmacc_count
2433 from
2434 AMW_FIN_ITEMS_KEY_ACC
2435 where
2436 STATEMENT_GROUP_ID =X_STATEMENT_GROUP_ID and
2437 ACCOUNT_GROUP_ID = X_ACCOUNT_GROUP_ID and
2438 FINANCIAL_STATEMENT_ID = X_FINANCIAL_STATEMENT_ID and
2439 FINANCIAL_ITEM_ID = X_FINANCIAL_ITEM_ID and
2440 NATURAL_ACCOUNT_ID = X_NATURAL_ACCOUNT_ID ;
2441
2442 if itmacc_count <> 0 then
2443
2444 fnd_file.put_line(fnd_file.LOG, 'Warning :- Duplicate Account '|| X_ACCOUNT_GROUP_ID ||' found for Financial Item ' || X_FINANCIAL_ITEM_ID || 'for Statement ' || X_STATEMENT_GROUP_ID );
2445
2446 else -- if no recrods exists for the unique key
2447 insert into AMW_FIN_ITEMS_KEY_ACC(
2448 STATEMENT_GROUP_ID,
2449 ACCOUNT_GROUP_ID ,
2450 FINANCIAL_STATEMENT_ID,
2451 FINANCIAL_ITEM_ID,
2452 NATURAL_ACCOUNT_ID ,
2453 LAST_UPDATE_DATE ,
2454 LAST_UPDATED_BY ,
2455 LAST_UPDATE_LOGIN ,
2456 CREATION_DATE ,
2457 CREATED_BY ,
2458 ATTRIBUTE_CATEGORY ,
2459 ATTRIBUTE1 ,
2460 ATTRIBUTE2 ,
2461 ATTRIBUTE3 ,
2462 ATTRIBUTE4 ,
2463 ATTRIBUTE5 ,
2464 ATTRIBUTE6 ,
2465 ATTRIBUTE7 ,
2466 ATTRIBUTE8 ,
2467 ATTRIBUTE9 ,
2468 ATTRIBUTE10 ,
2469 ATTRIBUTE11 ,
2470 ATTRIBUTE12 ,
2471 ATTRIBUTE13 ,
2472 ATTRIBUTE14 ,
2473 ATTRIBUTE15 ,
2474 SECURITY_GROUP_ID ,
2475 OBJECT_VERSION_NUMBER)
2476 values
2477 (
2478 X_STATEMENT_GROUP_ID ,
2479 X_ACCOUNT_GROUP_ID ,
2480 X_FINANCIAL_STATEMENT_ID ,
2481 X_FINANCIAL_ITEM_ID ,
2482 X_NATURAL_ACCOUNT_ID ,
2483 X_LAST_UPDATE_DATE ,
2484 X_LAST_UPDATED_BY ,
2485 X_LAST_UPDATE_LOGIN ,
2486 X_CREATION_DATE ,
2487 X_CREATED_BY ,
2488 X_ATTRIBUTE_CATEGORY ,
2489 X_ATTRIBUTE1 ,
2490 X_ATTRIBUTE2 ,
2491 X_ATTRIBUTE3 ,
2492 X_ATTRIBUTE4 ,
2493 X_ATTRIBUTE5 ,
2494 X_ATTRIBUTE6 ,
2495 X_ATTRIBUTE7 ,
2496 X_ATTRIBUTE8 ,
2497 X_ATTRIBUTE9 ,
2498 X_ATTRIBUTE10 ,
2499 X_ATTRIBUTE11 ,
2500 X_ATTRIBUTE12 ,
2501 X_ATTRIBUTE13 ,
2502 X_ATTRIBUTE14 ,
2503 X_ATTRIBUTE15 ,
2504 X_SECURITY_GROUP_ID ,
2505 X_OBJECT_VERSION_NUMBER );
2506 END IF;
2507 end;
2508 EXCEPTION WHEN OTHERS THEN
2509 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
2510 -- dbms_output.put_line(SQLERRM);
2511 g_errbuf := SUBSTR (SQLERRM, 1, 2000) ;
2512 g_retcode := '2';
2513
2514 RAISE ;
2515 RETURN;
2516
2517 END INSERT_FINITEM_ACC_MAP;
2518 --------------------------------------- ********************************************* ----------------------------
2519 Function check_acc_profiles_has_value
2520 return boolean is
2521 begin
2522 declare
2523 M_AMW_ACCOUNT_SOURCE_VIEW varchar2(2000) := null;
2524 M_AMW_ACCOUNT_NAMES_VIEW varchar2(2000) := null;
2525 m_errMsg varchar2(2000);
2526
2527 begin
2528 select fnd_profile.value('AMW_ACCOUNT_SOURCE_VIEW') into M_AMW_ACCOUNT_SOURCE_VIEW from dual;
2529 select fnd_profile.value('AMW_ACCOUNT_NAMES_VIEW' ) into M_AMW_ACCOUNT_NAMES_VIEW from dual;
2530 if (trim(M_AMW_ACCOUNT_SOURCE_VIEW) is null) then
2531
2532 -- FND_MESSAGE.SET_NAME ('AMW', 'AMW_FINIMPORT_PROFILE_NO_VALUE');
2533
2534 FND_MESSAGE.SET_NAME ('AMW', 'AMW_ACCIMPORT_PROFILE_NO_VALUE');
2535 g_errbuf := FND_MESSAGE.GET_STRING('AMW', 'AMW_FINIMPORT_PROFILE_NO_VALUE');
2536 m_errMsg := FND_MESSAGE.GET_STRING('AMW', 'AMW_FINIMPORT_PROFILE_NO_VALUE');
2537 fnd_file.put_line(fnd_file.LOG,g_errbuf );
2538
2539 -- fnd_file.put_line(fnd_file.LOG, SUBSTR ('Run Aborted. No value for Profile AMW_ACCOUNT_SOURCE_VIEW. Make sure that value exists for AMW_ACCOUNT_SOURCE_VIEW and AMW_ACCOUNT_NAMES_VIEW', 1, 200));
2540 -- g_errbuf := 'Run Aborted. No value for Profile AMW_ACCOUNT_SOURCE_VIEW. Make sure that value exists for AMW_ACCOUNT_SOURCE_VIEW and AMW_ACCOUNT_NAMES_VIEW';
2541
2542 g_retcode := '2';
2543
2544 return False;
2545 end if;
2546 if (trim(M_AMW_ACCOUNT_SOURCE_VIEW) is null) then
2547
2548 FND_MESSAGE.SET_NAME ('AMW', 'AMW_ACCIMPORT_PROFILE_NO_VALUE');
2549 g_errbuf := FND_MESSAGE.GET_STRING('AMW', 'AMW_FINIMPORT_PROFILE_NO_VALUE');
2550 m_errMsg := FND_MESSAGE.GET_STRING('AMW', 'AMW_FINIMPORT_PROFILE_NO_VALUE');
2551 fnd_file.put_line(fnd_file.LOG,g_errbuf );
2552
2553
2554 -- fnd_file.put_line(fnd_file.LOG, SUBSTR ('Run Aborted. No value for Profile AMW_ACCOUNT_NAMES_VIEW Make sure that value exists for AMW_ACCOUNT_SOURCE_VIEW and AMW_ACCOUNT_NAMES_VIEW', 1, 200));
2555 -- g_errbuf := 'Run Aborted. No value for Profile AMW_ACCOUNT_NAMES_VIEW Make sure that value exists for AMW_ACCOUNT_SOURCE_VIEW and AMW_ACCOUNT_NAMES_VIEW';
2556 g_retcode := '2';
2557
2558
2559 return False;
2560 end if;
2561 return True;
2562 end;
2563
2564 END check_acc_profiles_has_value;
2565 --------------------------------------- ********************************************* ----------------------------
2566 Function check_stmnt_profiles_has_value
2567 return boolean is
2568 begin
2569 declare
2570
2571 M_AMW_STMNT_SOURCE_VIEW varchar2(2000) := null;
2572 M_AMW_FINITEM_SOURCE_VIEW varchar2(2000) := null;
2573 M_AMW_FIN_ITEM_ACC_MAP_VIEW varchar2(2000) := null;
2574 M_AMW_STMNT_SOURCE_TL_VIEW varchar2(2000) := null;
2575 M_AMW_FINITEM_SOURCE_TL_VIEW varchar2(2000) := null;
2576
2577 begin
2578 select fnd_profile.value('AMW_STMNT_SOURCE_VIEW') into M_AMW_STMNT_SOURCE_VIEW from dual;
2579 select fnd_profile.value('AMW_FINITEM_SOURCE_VIEW' ) into M_AMW_FINITEM_SOURCE_VIEW from dual;
2580 select fnd_profile.value('AMW_FIN_ITEM_ACC_RELATIONS_VIEW' ) into M_AMW_FIN_ITEM_ACC_MAP_VIEW from dual;
2581 select fnd_profile.value('AMW_STMNT_SOURCE_TL_VIEW' ) into M_AMW_STMNT_SOURCE_TL_VIEW from dual;
2582 select fnd_profile.value('AMW_FINITEM_SOURCE_TL_VIEW' ) into M_AMW_FINITEM_SOURCE_TL_VIEW from dual;
2583
2584
2585 if (trim(M_AMW_STMNT_SOURCE_VIEW) is null) then
2586
2587 FND_MESSAGE.SET_NAME ('AMW', 'AMW_FINIMPORT_PROFILE_NO_VALUE');
2588 g_errbuf := FND_MESSAGE.GET_STRING('AMW', 'AMW_FINIMPORT_PROFILE_NO_VALUE');
2589 fnd_file.put_line(fnd_file.LOG,g_errbuf );
2590 g_retcode := '2';
2591
2592 /* fnd_file.put_line(fnd_file.LOG, SUBSTR ('Run Aborted. No value for Profile AMW_STMNT_SOURCE_VIEW . Make sure that value exists for AMW_STMNT_SOURCE_VIEW', 1, 200));
2593
2594 g_errbuf := 'Run Aborted. No value for Profile AMW_STMNT_SOURCE_VIEW . Make sure that value exists for AMW_STMNT_SOURCE_VIEW';
2595 g_retcode := '2';
2596 */
2597 return False;
2598 end if;
2599 if (trim(M_AMW_FINITEM_SOURCE_VIEW) is null) then
2600
2601 FND_MESSAGE.SET_NAME ('AMW', 'AMW_FINIMPORT_PROFILE_NO_VALUE');
2602 g_errbuf := FND_MESSAGE.GET_STRING('AMW', 'AMW_FINIMPORT_PROFILE_NO_VALUE');
2603 fnd_file.put_line(fnd_file.LOG,g_errbuf );
2604 g_retcode := '2';
2605
2606
2607 /* fnd_file.put_line(fnd_file.LOG, SUBSTR ('Run Aborted. No value for Profile AMW_FINITEM_SOURCE_VIEW. Make sure that value exists for AMW_FINITEM_SOURCE_VIEW ', 1, 200));
2608 g_errbuf := 'Run Aborted. No value for Profile AMW_FINITEM_SOURCE_VIEW. Make sure that value exists for AMW_FINITEM_SOURCE_VIEW ';
2609 g_retcode := '2';
2610 */
2611 return False;
2612 end if;
2613
2614 if (trim(M_AMW_FIN_ITEM_ACC_MAP_VIEW) is null) then
2615
2616 FND_MESSAGE.SET_NAME ('AMW', 'AMW_FINIMPORT_PROFILE_NO_VALUE');
2617 g_errbuf := FND_MESSAGE.GET_STRING('AMW', 'AMW_FINIMPORT_PROFILE_NO_VALUE');
2618 fnd_file.put_line(fnd_file.LOG,g_errbuf );
2619 g_retcode := '2';
2620
2621 /*
2622 fnd_file.put_line(fnd_file.LOG, SUBSTR ('Run Aborted. No value for Profile AMW_FIN_ITEM_ACC_RELATIONS_VIEW. Make sure that value exists for AMW_FIN_ITEM_ACC_RELATIONS_VIEW ', 1, 200));
2623 g_errbuf := 'Run Aborted. No value for Profile AMW_FIN_ITEM_ACC_RELATIONS_VIEW. Make sure that value exists for AMW_FIN_ITEM_ACC_RELATIONS_VIEW ';
2624 g_retcode := '2';
2625 */
2626 return False;
2627 end if;
2628 if (trim(M_AMW_STMNT_SOURCE_TL_VIEW) is null) then
2629
2630 FND_MESSAGE.SET_NAME ('AMW', 'AMW_FINIMPORT_PROFILE_NO_VALUE');
2631 g_errbuf := FND_MESSAGE.GET_STRING('AMW', 'AMW_FINIMPORT_PROFILE_NO_VALUE');
2632 fnd_file.put_line(fnd_file.LOG,g_errbuf );
2633 g_retcode := '2';
2634
2635 /*
2636 fnd_file.put_line(fnd_file.LOG, SUBSTR ('Run Aborted. No value for Profile AMW_STMNT_SOURCE_TL_VIEW. Make sure that value exists for AMW_STMNT_SOURCE_TL_VIEW', 1, 200));
2637 g_errbuf := 'Run Aborted. No value for Profile AMW_STMNT_SOURCE_TL_VIEW. Make sure that value exists for AMW_STMNT_SOURCE_TL_VIEW';
2638 g_retcode := '2';
2639 */
2640
2641 return False;
2642 end if;
2643
2644 if (trim(M_AMW_FINITEM_SOURCE_TL_VIEW) is null) then
2645
2646 FND_MESSAGE.SET_NAME ('AMW', 'AMW_FINIMPORT_PROFILE_NO_VALUE');
2647 g_errbuf := FND_MESSAGE.GET_STRING('AMW', 'AMW_FINIMPORT_PROFILE_NO_VALUE');
2648 fnd_file.put_line(fnd_file.LOG,g_errbuf );
2649 g_retcode := '2';
2650
2651 /*
2652 fnd_file.put_line(fnd_file.LOG, SUBSTR ('Run Aborted. No value for Profile AMW_FINITEM_SOURCE_TL_VIEW. Make sure that value exists for AMW_FINITEM_SOURCE_TL_VIEW', 1, 200));
2653 g_errbuf := 'Run Aborted. No value for Profile AMW_FINITEM_SOURCE_TL_VIEW. Make sure that value exists for AMW_FINITEM_SOURCE_TL_VIEW';
2654 g_retcode := '2';
2655 */
2656
2657 return False;
2658 end if;
2659
2660 return True;
2661 end;
2662
2663 END check_stmnt_profiles_has_value;
2664 --------------------------------------- ********************************************* ----------------------------
2665
2666 Function check_key_accounts_exists
2667 return boolean is
2668 begin
2669 declare
2670
2671 m_errMsg varchar2(2000);
2672 M_acct_count number := 0;
2673 begin
2674 select count(account_group_id) into M_acct_count from amw_fin_key_Accounts_b where END_DATE is null;
2675
2676 if M_acct_count = 0 then
2677 FND_MESSAGE.SET_NAME ('AMW', 'AMW_KEY_ACCOUNTS_NOT_IMPORTED');
2678 g_errbuf := FND_MESSAGE.GET_STRING('AMW', 'AMW_KEY_ACCOUNTS_NOT_IMPORTED');
2679 --m_errMsg := FND_MESSAGE.GET_STRING('AMW', 'AMW_KEY_ACCOUNTS_NOT_IMPORTED');
2680 g_retcode :=2;
2681 fnd_file.put_line(fnd_file.LOG,g_errbuf );
2682 return False;
2683 end if;
2684 return True;
2685 end;
2686
2687 END check_key_accounts_exists;
2688 -------------------------------------------------------------------------------------------------------------------
2689 Function check_account_value_set
2690 return boolean is
2691 begin
2692 declare
2693
2694 acc_value_set_id number := NULL;
2695 begin
2696 select fnd_profile.value('AMW_NATRL_ACCT_VALUE_SET') into acc_value_set_id from dual ;
2697
2698 if acc_value_set_id IS NULL then
2699 FND_MESSAGE.SET_NAME ('AMW', 'AMW_ACCT_VALUE_SET_NOT_DEFINED');
2700 g_errbuf := FND_MESSAGE.GET_STRING('AMW', 'AMW_ACCT_VALUE_SET_NOT_DEFINED');
2701 --m_errMsg := FND_MESSAGE.GET_STRING('AMW', 'AMW_ACCT_VALUE_SET_NOT_DEFINED');
2702 g_retcode :=2;
2703 fnd_file.put_line(fnd_file.LOG,g_errbuf );
2704 return False;
2705 end if;
2706 return True;
2707 end;
2708
2709 END check_account_value_set;
2710
2711 -------------------------------------------------------------------------
2712 -- Procedure that flattens the accounts table (Sanket).
2713 --
2714 -- For every parent - child relationship in amw_fin_key_accounts_b table,
2715 -- we insert one record in the flat table. This implies that given a
2716 -- particular account group id g, for a root
2717 -- account id x, we will not have a row in the flat table with child =
2718 -- x. Similarly, for a leaf acct y, we will not have a row with parent =
2719 -- y. A standalone acct id z (a node with no parent or children) will not
2720 -- appear in the flat table.
2721 -------------------------------------------------------------------------
2722
2723 procedure flatten_accounts ( x_group_id in number ) is
2724
2725 cursor acct_cursor is
2726 select distinct natural_account_id acct_id, account_group_id group_id
2727 from amw_fin_key_accounts_b
2728 where account_group_id = x_group_id ;
2729
2730 cursor nested_cursor ( p_acct_id number, p_group_id number ) is
2731
2732 select p_acct_id parent_id,
2733 acct.natural_account_id child_id,
2734 acct.account_group_id group_id
2735 from AMW_FIN_KEY_ACCOUNTS_B acct
2736 start with account_group_id = p_group_id
2737 and parent_natural_account_id = p_acct_id
2738 connect by prior natural_account_id = parent_natural_account_id
2739 and account_group_id = p_group_id;
2740
2741 begin
2742
2743 g_user_id := fnd_global.user_id;
2744 g_login_id := fnd_global.conc_login_id;
2745
2746 for acct_rec in acct_cursor loop
2747
2748 for nested_rec in nested_cursor ( acct_rec.acct_id, acct_rec.group_id ) loop
2749 insert into amw_fin_key_acct_flat
2750 ( parent_natural_account_id,
2751 child_natural_account_id,
2752 account_group_id,
2753 CREATED_BY,
2754 CREATION_DATE,
2755 LAST_UPDATED_BY,
2756 LAST_UPDATE_DATE,
2757 LAST_UPDATE_LOGIN,
2758 SECURITY_GROUP_ID,
2759 OBJECT_VERSION_NUMBER
2760 )
2761 values
2762 ( nested_rec.parent_id,
2763 nested_rec.child_id,
2764 nested_rec.group_id,
2765 g_user_id,
2766 sysdate,
2767 g_user_id,
2768 sysdate,
2769 g_login_id,
2770 null,
2771 null
2772 );
2773 end loop;
2774
2775 end loop;
2776
2777 end flatten_accounts;
2778
2779 -------------------------------------------------------------------------
2780 -- Procedure that flattens the items table (Sanket).
2781 --
2782 -- This is similar to the above procedure. Please refer above for
2783 -- details.
2784 -------------------------------------------------------------------------
2785
2786 procedure flatten_items ( x_group_id in number ) is
2787
2788 cursor item_cursor is
2789 select distinct items.financial_item_id item_id,
2790 items.statement_group_id group_id,
2791 items.financial_statement_id stmt_id
2792 from amw_fin_stmnt_items_b items
2793 where items.statement_group_id = x_group_id;
2794
2795 cursor nested_cursor ( p_stmt_id number, p_group_id number, p_item_id number ) is
2796
2797 select p_item_id parent_id,
2798 items.financial_item_id child_id,
2799 items.statement_group_id group_id,
2800 items.financial_statement_id stmt_id
2801 from amw_fin_stmnt_items_b items
2802 start with items.statement_group_id = p_group_id
2803 and items.parent_financial_item_id = p_item_id
2804 and items.financial_statement_id = p_stmt_id
2805 connect by prior items.financial_item_id = items.parent_financial_item_id
2806 and items.statement_group_id = p_group_id
2807 and items.financial_statement_id = p_stmt_id;
2808
2809 begin
2810
2811 g_user_id := fnd_global.user_id;
2812 g_login_id := fnd_global.conc_login_id;
2813
2814 for item_rec in item_cursor loop
2815
2816 for nested_rec in nested_cursor ( item_rec.stmt_id, item_rec.group_id, item_rec.item_id ) loop
2817 insert into amw_fin_item_flat
2818 ( parent_financial_item_id,
2819 child_financial_item_id,
2820 statement_group_id,
2821 financial_statement_id,
2822 CREATED_BY,
2823 CREATION_DATE,
2824 LAST_UPDATED_BY,
2825 LAST_UPDATE_DATE,
2826 LAST_UPDATE_LOGIN,
2827 SECURITY_GROUP_ID,
2828 OBJECT_VERSION_NUMBER
2829 )
2830 values
2831 ( nested_rec.parent_id,
2832 nested_rec.child_id,
2833 nested_rec.group_id,
2834 nested_rec.stmt_id,
2835 g_user_id, sysdate, g_user_id, sysdate, g_login_id, null, null
2836 );
2837 end loop;
2838
2839 end loop;
2840
2841 end flatten_items;
2842 -------------------------------------------------------------------------------------------------------------------
2843
2844
2845 END AMW_IMPORT_STMNTS_ACCS_PKG;