1 Package Body hr_gl_cost_centers AS
2 /* $Header: hrglcsyn.pkb 120.0 2005/05/31 00:37:09 appldev noship $ */
3
4 g_package varchar2(20) := 'hr_gl_cost_centers';
5 g_max_number_of_retries NUMBER := 10;
6 g_debug_level varchar2(80) := null;
7 g_no_cc_proc_exc exception;
8 g_org_name_length number;
9
10 /*
11 cid number;
12 g_newline varchar2(10) := fnd_global.newline;
13 g_tab varchar2(30) := fnd_global.tab;
14
15
16 g_log_file utl_file.file_type;
17 g_application_short_name VARCHAR2(50) := 'PER';
18 g_bgid NUMBER := 0;
19 g_retcode NUMBER := 0;
20 g_ccid NUMBER := 0;
21 g_mode VARCHAR2(30) := null;
22
23 g_last_update_login number := 0;
24
25 g_start_date DATE;
26
27 g_edw_cc_reporting_temp VARCHAR2(40) := 'HR_CC_REPORTING_TEMP';
28 g_edw_cc_reporting_comp VARCHAR2(40) := 'HR_CC_REPORTING_COMP';
29 g_edw_sync_temp VARCHAR2(40) := 'HR_SYNC_TEMP';
30 g_edw_sync_ccid VARCHAR2(40) := 'HR_SYNC_GL_CCID';
31 g_schema VARCHAR2(10) := null;
32
33 */
34 Function getDebugState return varchar2 is
35
36 cursor c_get_debug is
37 select parameter_value
38 from pay_action_parameters
39 where parameter_name = 'HR_GL_SYNC_DEBUG';
40 l_debug_level varchar2(80);
41
42 BEGIN
43 l_debug_level := g_debug_level;
44 if l_debug_level is null then
45 open c_get_debug;
46 fetch c_get_debug into l_debug_level;
47 if c_get_debug%NOTFOUND then
48 g_debug_level := 'NORMAL';
49 close c_get_debug;
50 elsif c_get_debug%FOUND then
51 g_debug_level := l_debug_level;
52 close c_get_debug;
53 end if;
54 end if;
55 return l_debug_level;
56 END;
57
58
59 Procedure writelog(p_text in VARCHAR2
60 ,p_error_or_debug in VARCHAR2 default null) IS
61
62 l_debug_level varchar2(80);
63
64 BEGIN
65
66 l_debug_level := getDebugState();
67
68 if p_error_or_debug = 'D'
69 then
70 if l_debug_level = 'DEBUG' then
71 fnd_file.put_line(FND_FILE.log, p_text);
72 end if;
73 elsif p_error_or_debug = 'E' then
74 fnd_file.put_line(FND_FILE.log, p_text);
75 hr_utility.raise_error;
76 else
77 fnd_file.put_line(FND_FILE.log, p_text);
78 end if;
79
80 hr_utility.set_location(p_text,10);
81 exception
82 when others then
83 if SQLCODE = -20100 then
84 hr_utility.set_location(substr(p_text,1,100),20);
85 else
86 hr_utility.set_location('writelog encountered unexpected exception',30);
87 raise;
88 end if;
89 END;
90
91 Procedure dumpccidtable(p_tablename IN varchar2) is
92 l_stmt varchar2(2000);
93 type curType is ref cursor;
94 c_list curType;
95 l_ccid number;
96 l_org_id number;
97 l_debug_level varchar2(10);
98 begin
99
100 l_debug_level := getDebugState;
101 if l_debug_level <> 'DEBUG' then
102 return;
103 end if;
104
105 writelog('Dumping table '||p_tablename,'D');
106 l_stmt := 'select ccid, org_id from '||p_tablename;
107 open c_list for l_stmt;
108 loop
109 fetch c_list into l_ccid, l_org_id;
110 exit when c_list%NOTFOUND;
111 writelog('ccid '||to_char(l_ccid),'D');
112 writelog('org_id '||to_char(l_org_id),'D');
113 end loop;
114 close c_list;
115 end;
116
117 Procedure dumptemptable(p_tablename IN varchar2) is
118 l_stmt varchar2(2000);
119 type curType is ref cursor;
120 c_list curType;
121 l_ccid number;
122 l_org_id number;
123 l_chart_of_accounts_id number;
124 l_company varchar2(240);
125 l_company_vs number;
126 l_cc_vs number;
127 l_cost_center varchar2(240);
128 l_business_group_id number;
129 l_debug_level varchar2(10);
130 begin
131
132 l_debug_level := getDebugState;
133 if l_debug_level <> 'DEBUG' then
134 return;
135 end if;
136
137 writelog('Dumping table '||p_tablename,'D');
138 l_stmt := 'select ccid, org_id, chart_of_accounts_id,
139 company, company_vs, cost_center, cc_vs,
140 business_group_id from '||p_tablename;
141 open c_list for l_stmt;
142 loop
143 fetch c_list into l_ccid, l_org_id, l_chart_of_accounts_id,
144 l_company, l_company_vs, l_cost_center,
145 l_cc_vs, l_business_group_id ;
146 exit when c_list%NOTFOUND;
147 writelog('ccid '||to_char(l_ccid),'D');
148 writelog('org_id '||to_char(l_org_id),'D');
149 writelog('coa_id '||to_char(l_chart_of_accounts_id),'D');
150 writelog('company '||l_company,'D');
151 writelog('company vs '||to_char(l_company_vs),'D');
152 writelog('cost_center '||l_cost_center,'D');
153 writelog('cost_center vs '||to_char(l_cc_vs),'D');
154 writelog('bg id '||to_char(l_business_group_id),'D');
155 end loop;
156 close c_list;
157 end;
158
159 Procedure droptable(p_tablename IN VARCHAR2,
160 p_force IN BOOLEAN default TRUE) IS
161 l_proc varchar2(50) := g_package||'.droptable';
162 l_debug_level varchar2(80);
163 BEGIN
164 hr_utility.set_location('Entering : '||l_proc,10);
165 l_debug_level := getDebugState();
166
167 if p_force
168 OR
169 (not p_force and
170 l_debug_level <> 'DEBUG') then
171 hr_utility.set_location(l_proc,15);
172 execute immediate 'drop table '|| p_tablename;
173 end if;
174 hr_utility.set_location('Leaving : '||l_proc,20);
175
176 Exception when others then
177 writelog(sqlerrm,'D');
178 IF (sqlcode = -942) THEN
179 /*
180 ** table does not exist at time of dropping which
181 ** we can ignore.
182 */
183 hr_utility.set_location('Leaving : '||l_proc,30);
184 null;
185 ELSE
186 hr_utility.set_location('Leaving : '||l_proc,40);
187 raise;
188 END IF;
189
190 END;
191
192 Function reportMissingFlex(p_chart_of_accounts_id NUMBER,
193 p_cc_segment VARCHAR2,
194 p_company_segment VARCHAR2,
195 p_cc_vs NUMBER,
196 p_company_vs NUMBER) return number is
197
198 l_retcode number;
199 l_proc varchar2(150) := g_package||'.reportMissingFlex';
200 l_message varchar2(150);
201 begin
202
203 hr_utility.set_location('Entering : '||l_proc,10);
204
205 hr_utility.set_message(800,'PER_289604_WRN_CHRT_ACC_ID');
206 fnd_message.set_token('ID',p_chart_of_accounts_id);
207 l_message := fnd_message.get();
208
209 writelog(l_message,'W');
210 if p_company_segment is NULL then
211 writelog(fnd_message.get_string(800,'PER_289605_WRN_COMP_SEG'),'W');
212 end if;
213 if p_cc_segment is NULL then
214 writelog(fnd_message.get_string(800,'PER_289606_WRN_CC_SEG'),'W');
215 end if;
216 if p_company_vs = -1 then
217 writelog(fnd_message.get_string(800,'PER_289607_WRN_COMP_VS'),'W');
218 end if;
219 if p_cc_vs = - 1 then
220 writelog(fnd_message.get_string(800,'PER_289608_WRN_CC_VS'),'W');
221 end if;
222 l_retcode := 1;
223 hr_utility.set_location('Leaving : '||l_proc,20);
224 return l_retcode;
225
226 end;
227
228 Function getProductSchema(p_product IN VARCHAR2) RETURN VARCHAR2 IS
229 l_dummy1 varchar2(2000);
230 l_dummy2 varchar2(2000);
231 l_schema varchar2(400);
232 l_proc varchar2(50) := g_package||'.getProductSchema';
233 BEGIN
234 hr_utility.set_location('Entering : '||l_proc,10);
235 if FND_INSTALLATION.GET_APP_INFO(p_product
236 ,l_dummy1
237 ,l_dummy2
238 ,l_schema) = false then
239 hr_utility.set_location('Leaving : '||l_proc,15);
240 return null;
241 end if;
242 hr_utility.set_location('Leaving : '||l_proc,20);
243 return l_schema;
244 END;
245
246 Procedure writeHeaderFileHeader(p_file in utl_file.file_type
247 ,p_bgid in NUMBER
248 ,p_datfile in VARCHAR2) IS
249 l_bg_name per_business_groups.name%type := null;
250 l_proc varchar2(50) := g_package||'.writeHeaderFileHeader';
251 l_tab varchar2(5) := fnd_global.tab;
252 BEGIN
253 hr_utility.set_location('Entering : '||l_proc,10);
254 BEGIN
255 SELECT name INTO l_bg_name
256 from PER_BUSINESS_GROUPS
257 WHERE business_group_id = p_bgid;
258
259 utl_file.put_line(P_FILE, 'Header'||l_tab||'Start');
260 utl_file.put_line(P_FILE, 'Batch Name'||l_tab||
261 substr(l_bg_name||'-'||to_char(sysdate, 'YYYY/MM/DD'), 1, 70));
262 utl_file.put_line(P_FILE, 'Date'||l_tab||to_char(sysdate, 'YYYY/MM/DD'));
263 utl_file.put_line(P_FILE, 'Version'||l_tab||'1.0');
264 utl_file.put_line(P_FILE, 'Date Format'||l_tab||'YYYY/MM/DD');
265 utl_file.put_line(P_FILE, 'Number Format'||l_tab||'999999999999999');
266 utl_file.put_line(P_FILE, 'Header'||l_tab||'End');
267 utl_file.put_line(P_FILE, 'Files'||l_tab||'Start');
268 utl_file.put_line(P_FILE, 'create_company_cost_center'||l_tab||p_datfile);
269 utl_file.put_line(P_FILE, 'Files'||l_tab||'End');
270
271 Exception
272 When no_data_found then
273 writelog('Error in writeHeaderFile for business group id : '||p_bgId
274 ,'E');
275 END;
276 hr_utility.set_location('Leaving : '||l_proc,20);
277 END;
278
279 Procedure writeDataFileHeader(p_file IN utl_file.file_type) IS
280 l_proc varchar2(50) := g_package||'.writeDataFileHeader';
281 l_tab varchar2(5) := fnd_global.tab;
282 BEGIN
283 hr_utility.set_location('Entering : '||l_proc,10);
284
285 utl_file.put_line(P_FILE, 'Descriptor'||l_tab||'Start');
286 utl_file.put_line(P_FILE, 'API'||l_tab||'create_company_cost_center');
287 utl_file.put_line(P_FILE, 'Title'||l_tab||'create company cost center');
288 utl_file.put_line(P_FILE, 'Process Order'||l_tab||'10');
289 utl_file.put_line(P_FILE, 'Descriptor'||l_tab||'End');
290 utl_file.put_line(P_FILE, 'Data'||l_tab||'Start');
291
292 utl_file.put_line(P_FILE, 'ID'||l_tab||'organization_name'||l_tab||
293 'costcenter_id'||l_tab||'costcenter_name'||l_tab||
294 'company_id'||l_tab||'company_name'||l_tab||
295 'costcenter_valueset_name'||l_tab||'company_valueset_name'||
296 l_tab||'start_date'||l_tab||'language_code');
297 hr_utility.set_location('Leaving : '||l_proc,20);
298 END;
299
300 procedure spoolToFile(p_hr_cc_reporting_temp in varchar2) is
301
302 l_proc varchar2(50) := g_package||'.spoolToFile';
303
304 type curType is ref cursor;
305 c_list curType;
306 c_bg_list curType;
307
308 l_company_vs NUMBER := 0;
309 l_cc_vs NUMBER := 0;
310
311 l_counter NUMBER := 1;
312 l_bg_id NUMBER := -1;
313 l_data_line_counter NUMBER := 1;
314
315 l_company_vsname VARCHAR2(80) := null;
316 l_cc_vsname VARCHAR2(80) := null;
317 l_company_id VARCHAR2(240) := null;
318 l_cost_center_id VARCHAR2(240) := null;
319 l_company_name VARCHAR2(240) := null;
320 l_cost_center_name VARCHAR2(240) := null;
321 l_org_name hr_all_organization_units_tl.name%type := null;
322 l_bg_name hr_all_organization_units_tl.name%type := null;
323 l_dir VARCHAR2(300) := null;
324 l_org_start_date date ; -- Added by FS
325
326 l_hdr_file utl_file.file_type;
327 l_dat_file utl_file.file_type;
328 l_header_file varchar2(80);
329 l_data_file varchar2(80);
330 l_stmt VARCHAR2(3000) := null;
331 l_retcode NUMBER;
332 l_tab VARCHAR2(5) := fnd_global.tab;
333 BEGIN
334 hr_utility.set_location('Entering : '||l_proc,10);
335 l_dir := fnd_profile.value('PER_DATA_EXCHANGE_DIR');
336
337 writelog('HR profile PER_DATA_EXCHANGE_DIR is set to : '||l_dir,'D');
338
339 IF (l_dir is null) THEN
340 hr_utility.set_message(801,'HR_289427_NO_EXC_DIR');
341 writelog(fnd_message.get_string('PER','HR_289427_NO_EXC_DIR'),'E');
342 END IF;
343 hr_utility.set_location(l_proc,20);
344
345 open c_bg_list for 'select distinct rep.business_group_id,
346 per.name
347 from '||p_hr_cc_reporting_temp ||' rep,
348 per_business_groups per
349 where rep.business_group_id=per.business_group_id';
350 loop
351 hr_utility.set_location(l_proc,30);
352 fetch c_bg_list into l_bg_id, l_bg_name;
353 exit when c_bg_list%NOTFOUND or l_bg_id is null;
354
355 l_header_file := 'GLCC_'||replace(substr(l_bg_name,1,30),' ','_')||
356 to_char(l_bg_id)||'.hdr';
357 l_data_file := 'GLCC_'||replace(substr(l_bg_name,1,30),' ','_')||
358 to_char(l_bg_id)||'.txt';
359 writelog('Header file name '||l_header_file,'D');
360 writelog('Data file name '||l_data_file,'D');
361
362 begin
363 hr_utility.set_location(l_proc,50);
364 l_hdr_file := utl_file.fopen(l_dir,l_header_file,'w');
365 Exception
366 when others then
367 hr_utility.set_message(801,'HR_289426_INV_EXC_DIR');
368 writelog(fnd_message.get_string('PER','HR_289426_INV_EXC_DIR'),'E');
369 end;
370
371 hr_utility.set_location(l_proc,60);
372 writeHeaderFileHeader(l_hdr_file
373 ,l_bg_id
374 ,l_data_file);
375 utl_file.fclose(l_hdr_file);
376 hr_utility.set_location(l_proc,70);
377
378 l_dat_file := utl_file.fopen(l_dir, l_data_file,'w');
379
380 writeDataFileHeader(l_dat_file);
381
382 l_stmt :=
383 'select distinct reptemp.company_value_set,
384 comp.flex_value_set_name,
385 reptemp.cc_value_set,
386 cc.flex_value_set_name,
387 reptemp.company,
388 reptemp.cost_center,
389 reptemp.org_name,
390 compname.description,
391 ccname.description,
392 reptemp.org_start_date
393 from fnd_flex_value_sets comp,
394 fnd_flex_value_sets cc,
395 fnd_flex_values_vl compname,
396 fnd_flex_values_vl ccname,
397 (select distinct company_value_set,
398 cc_value_set,
399 company,
400 cost_center,
401 org_name ,
402 org_start_date
403 from '||p_hr_cc_reporting_temp|| ') reptemp
404 where reptemp.company_value_set = comp.flex_value_set_id
405 and reptemp.cc_value_set = cc.flex_value_set_id
406 and comp.flex_value_set_id = compname.flex_value_set_id
407 and cc.flex_value_set_id = ccname.flex_value_set_id
408 and reptemp.company = compname.flex_value
409 and reptemp.cost_center = ccname.flex_value ';
410
411 open c_list for l_stmt;
412 hr_utility.set_location(l_proc,80);
413
414 loop
415 fetch c_list
416 into l_company_vs,
417 l_company_vsname,
418 l_cc_vs,
419 l_cc_vsname,
420 l_company_id,
421 l_cost_center_id,
422 l_org_name,
423 l_company_name,
424 l_cost_center_name,
425 l_org_start_date ;
426
427 exit when c_list%notfound;
428
429 -- Now Print it to the output file
430 utl_file.put_line(l_dat_file,
431 l_data_line_counter||l_tab|| -- ID Column
432 l_org_name||l_tab|| -- Org Name
433 l_cost_center_id||l_tab|| -- Cost Center ID
434 l_cost_center_name||l_tab|| -- Cost Center Name
435 l_company_id||l_tab|| -- Company ID
436 l_company_name||l_tab|| -- Company Name
437 l_cc_vsname ||l_tab|| -- CC VS Name
438 l_company_vsname||l_tab|| -- Company VS Name
439 l_org_start_date||l_tab||
440 -- Effective_date
441 'US' ); -- Language_code
442
443 l_data_line_counter := l_data_line_counter + 1;
444 end loop;
445 hr_utility.set_location(l_proc,90);
446 utl_file.put_line(l_dat_file, 'Data'||l_tab||'End');
447 utl_file.fclose(l_dat_file);
448 writelog('Spooled '||l_data_line_counter||' lines in the data file','D');
449 l_data_line_counter := 1;
450 l_counter := l_counter +1;
451 close c_list;
452 hr_utility.set_location(l_proc,100);
453 END LOOP;
454 hr_utility.set_location(l_proc,110);
455 close c_bg_list;
456
457 writelog('Finished Spooling to file.','D');
458 end;
459
460
461
462 Function getSegmentForQualifier(p_chart_of_accounts_id in NUMBER
463 ,p_qualifier in VARCHAR2
464 ,p_column_name in out nocopy VARCHAR2)
465 return VARCHAR2 is
466 l_proc varchar2(50) := g_package||'.getSegmentForQualifier';
467
468 begin
469 hr_utility.set_location('Entering : '||l_proc,10);
470 if fnd_flex_apis.get_segment_column(101
471 ,'GL#'
472 ,p_chart_of_accounts_id
473 ,p_qualifier
474 ,p_column_name) THEN
475 hr_utility.set_location(p_qualifier||' segment : '||p_column_name,20);
476 hr_utility.set_location('Leaving : '||l_proc,30);
477 return 0;
478 end if;
479 hr_utility.set_location('Leaving : '||l_proc,40);
480 return -2;
481 END;
482
483
484
485 Function getValueSetForSegment(p_chart_of_accounts_id in NUMBER
486 ,p_segment in VARCHAR2
487 ,p_vs_id in out nocopy VARCHAR2)
488 return NUMBER IS
489 l_proc varchar2(50) := g_package||'.getValueSetForSegment';
490
491 type curType is ref cursor;
492 c_list curType;
493
494 l_vs_name fnd_flex_value_sets.flex_value_set_name%TYPE;
495
496 BEGIN
497 p_vs_id := -1;
498 hr_utility.set_location('Entering : '||l_proc,10);
499 hr_utility.set_location('coa ID '||p_chart_of_Accounts_id,10);
500 hr_utility.set_location('segment '||p_segment,10);
501
502 open c_list for 'select vs.flex_value_set_id,
503 vs.flex_value_set_name
504 from fnd_id_flex_segments_vl seg
505 , fnd_flex_value_sets vs
506 where upper(seg.id_flex_code) = ''GL#''
507 and seg.application_id = 101
508 and seg.flex_value_set_id = vs.flex_value_set_id
509 and seg.enabled_flag = ''Y''
510 and id_flex_num = :1
511 and application_column_name = :2'
512 using p_chart_of_accounts_id, p_segment;
513 FETCH c_list into p_vs_id, l_vs_name;
514 close c_list;
515 hr_utility.set_location(p_segment||' valueset is '||
516 l_vs_name||'('||p_vs_id||')',20);
517 hr_utility.set_location('Leaving : '||l_proc,30);
518 return 0;
519 END;
520
521
522 /* ------------------------------------------------------------------------------------------
523 ** -- --
524 ** -- create_org_and_classification --
525 ** -- --
526 ** ------------------------------------------------------------------------------------------ */
527 Function create_org_and_classification (p_mode in varchar2
528 ,p_bg_id in number
529 ,p_company in VARCHAR2
530 ,p_cost_center in VARCHAR2
531 ,p_company_vs in number
532 ,p_cc_vs in number
533 ,p_ccid in number
534 ,p_org_id out nocopy number)
535 return NUMBER IS
536
537 l_proc varchar2(50) := g_package||'.create_org_and_classification';
538
539 l_organization_id number(15) := -1;
540 l_organization_name HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
541 l_ori_ovn number := -1;
542 l_org_ovn number := -1;
543 l_ori_inf_id number := -1;
544 l_org_inf_id number := -1;
545 l_class_id number;
546 l_class_ovn number;
547 l_enabled_flag varchar2(10);
548 l_new_org_created boolean := FALSE;
549
550 l_number_of_retries number;
551
552 l_org_profile varchar2(10) := 'CC';
553 l_class_profile varchar2(10);
554
555 l_company_desc varchar2(150) := null;
556 l_cc_desc varchar2(150) := null;
557 l_summary_flag VARCHAR2(10) := null;
558 type curType is ref cursor;
559 c_list curType;
560 l_dummy varchar2(100);
561
562 l_org_name HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE := null;
563 l_orig_org_name HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE := null;
564 l_org_name_format varchar2(60) := null;
565 l_org_name_suffix varchar2(5) := null;
566 l_message varchar2(2000);
567
568 l_company varchar2(150);
569 l_company_vs varchar2(150);
570 l_cost_center varchar2(150);
571 l_cost_center_vs varchar2(150);
572 l_comp_start_dt date; -- Bug# 3204851
573 l_cc_start_dt date; -- Bug# 3204851
574 l_org_start_dt date; -- BUg# 3208451
575
576
577 cursor csr_chk_org_name is
578 select organization_id
579 from hr_all_organization_units
580 where name = l_org_name
581 and business_group_id = p_bg_id;
582
583 cursor csr_chk_org_class(p_organization_id number,
584 p_classification varchar2) is
585 select org_information2, org_information_id, object_version_number
586 from hr_organization_information ori
587 where organization_id = p_organization_id
588 and org_information_context = 'CLASS'
589 and org_information1 = p_classification;
590
591 cursor csr_chk_info_type(p_organization_id number,
592 p_info_type varchar2) is
593 select org_information2,
594 org_information3,
595 org_information4,
596 org_information5
597 from hr_organization_information ori
598 where organization_id = p_organization_id
599 and org_information_context = p_info_type;
600
601 BEGIN
602 hr_utility.set_location('Entering : '||l_proc,10);
603
604 writelog('p_bg_id = '||p_bg_id||' , p_company = '||
605 p_company||', p_cost_center = '||p_Cost_center||
606 ', p_company_vs = ' ||p_company_vs||', p_cc_vs = '||p_cc_vs,'D');
607
608 l_org_profile := fnd_profile.value('HR_GENERATE_GL_OPTIONS');
609 writelog('HR_GENERATE_GL_OPTIONS : '||l_org_profile,'D');
610 if (l_org_profile = 'S') then
611 hr_utility.set_location(l_proc,20);
612 writelog(fnd_message.get_string('PER','HR_289509_NOT_GEN_GL_ORGS'),'W');
613 return -1;
614 end if;
615 l_class_profile := fnd_profile.value('HR_GENERATE_GL_ORGS');
616 writelog('HR_GENERATE_GL_ORGS : '||l_class_profile,'D');
617 if (l_org_profile = 'SC' or l_org_profile = 'SCO')
618 and l_class_profile = 'N' then
619 writelog(fnd_message.get_string('PER','HR_289478_PROF_OPTS_WARNING'),'E');
620 end if;
621
622 hr_utility.set_location(l_proc,30);
623
624 if (p_mode = 'GL') then
625 hr_utility.set_location(l_proc,40);
626 begin
627 select summary_flag
628 into l_summary_flag
629 from gl_code_combinations
630 where code_combination_id = p_ccid;
631
632 if (upper(l_summary_flag) = 'Y') then
633 hr_utility.set_location(l_proc,50);
634 writelog('PER_289624_SUMM_FLG_CCID');
635 return -1;
636 end if;
637
638 exception
639 when no_data_found then
640 hr_utility.set_location(l_proc,60);
641 /*
642 ** This message has got tokens so stick all the stuff
643 ** on the stack and then pull it off to get the fully
644 ** translated and token substituted string...
645 */
646 hr_utility.set_message(800,'HR_289488_INV_CCID');
647 fnd_message.set_token('CCID',p_ccid);
648 l_message := fnd_message.get();
649 /*
650 ** ... having got the translated and token substituted message
651 ** put it all back on the stack so it is found later when the error
652 ** is raised...
653 */
654 hr_utility.set_message(800,'HR_289488_INV_CCID');
655 fnd_message.set_token('CCID',p_ccid);
656 /*
657 ** ...now pass the string to writelog and get the error raised.
658 */
659 writelog(l_message,'E');
660 hr_utility.raise_error;
661 end;
662 end if; /* p_mode = 'GL' */
663 hr_utility.set_location(l_proc,70);
664
665 /* Check if Org already exists using company and cost center valueset/value
666 ** information
667 */
668
669 l_organization_id := -1;
670 open c_list for 'select units.organization_id, units.name
671 from hr_all_organization_units_tl units,
672 hr_organization_information class,
673 hr_organization_information cc
674 where units.organization_id = class.organization_id
675 and class.org_information_context=''CLASS''
676 and class.org_information1=''CC''
677 and class.organization_id = cc.organization_id
678 and cc.org_information_context = ''Company Cost Center''
679 and cc.org_information2 = :1
680 and cc.org_information4 = :2
681 and cc.org_information3 = :3
682 and cc.org_information5 = :4 '
683 using to_char(p_company_vs), to_char(p_cc_vs), p_company,
684 p_cost_center;
685
686 fetch c_list into l_organization_id,l_organization_name;
687 close c_list;
688 hr_utility.set_location(l_proc,80);
689
690 if (l_organization_id <> -1) then
691 hr_utility.set_location(l_proc,83);
692 /*
693 ** This message has got tokens so stick all the stuff
694 ** on the stack and then pull it off to get the fully
695 ** translated and token substituted string...
696 */
697 hr_utility.set_message(800,'HR_289600_ORG_ALRDY');
698 fnd_message.set_token('NAME',l_organization_name);
699 fnd_message.set_token('COMPANY',p_company);
700 fnd_message.set_token('COST',p_cost_center);
701 l_message := fnd_message.get();
702 /*
703 ** ...now pass the string to writelog.
704 */
705 writelog(l_message,'I');
706 p_org_id := l_organization_id;
707 return 0;
708 end if; /* l_organization_id <> -1 */
709
710 /*
711 ** To get this far we failed to find an organization which has the the company and cost center
712 ** information defined for it. This means we are going to try and find it by name. If we don't
713 ** find it then we'll create a new org with the appropriate classification and info type. If
714 ** we don find a match by name we'll check for the CC classification and orig info and do the
715 ** appropriate thing (see below what the "appropriate thing" is).
716 */
717
718 /* First derive the organization name....
719 */
720 open c_list for 'select description ,start_date_active
721 from fnd_flex_values_vl
722 where flex_value_set_id = :1
723 and flex_value = :2 '
724 using p_company_vs, p_company;
725 fetch c_list into l_company_desc,l_comp_start_dt;
726 close c_list;
727 hr_utility.set_location(l_proc,86);
728
729 if (l_company_desc is null) then
730 l_company_desc := p_company;
731 end if;
732
733 open c_list for 'select description ,start_date_active
734 from fnd_flex_values_vl
735 where flex_value_set_id = :1
736 and flex_value = :2'
737 using p_cc_vs, p_cost_center;
738 fetch c_list into l_cc_desc,l_cc_start_dt;
739 close c_list;
740 hr_utility.set_location(l_proc,90);
741
742 if (l_cc_desc is null) then
743 l_cc_desc := p_cost_center;
744 end if;
745
746 /* Bug 3208451
747 ** Set the Org start date to the latest of company or cost center start date
748 ** and Set it to default date 01/01/1990' if the value fetched from abse table is null
749 */
750
751 l_org_start_dt := greatest(nvl(l_cc_start_dt,to_date('01/01/1990','DD/MM/RRRR')),nvl(l_comp_start_dt,to_date('01/01/1990','DD/MM/RRRR')));
752 if to_char(l_org_start_dt ,'DD/MM/RRRR')= '01/01/1900' then
753 l_org_start_dt := to_date('01/01/1990','DD/MM/RRRR');
754 end if ;
755
756
757 /* Read the Org Name Format */
758 l_org_name_format := fnd_profile.value('HR_GL_ORG_NAME_FORMAT');
759 if (l_org_name_format IS NULL) then
760 hr_utility.set_location(l_proc,100);
761 hr_utility.set_message(801,'HR_289489_NO_NAME_FORMAT');
762 writelog(fnd_message.get_string('PER','HR_289489_NO_NAME_FORMAT'),'E');
763 end if;
764
765 hr_utility.set_location(l_proc,110);
766 writelog('Org Format is : '||l_org_name_format,'D');
767 /*
768 ** Build the organization name by Substituting the available componenents
769 ** using the organization name format string defined.
770 */
771 select substrb(
772 REPLACE(
773 REPLACE(
774 REPLACE(
775 REPLACE(l_org_name_format,'$COC', p_company),
776 '$CCC', p_cost_center),
777 '$CON', l_company_desc),
778 '$CCN', l_cc_desc), 1, g_org_name_length)
779 into l_org_name
780 from dual;
781
782 hr_utility.set_location(l_proc,120);
783 l_org_name := substrb(l_org_name, 1, g_org_name_length);
784 l_orig_org_name := l_org_name;
785 writelog('Org to be created is : '||l_org_name,'D');
786
787 /*
788 ** We've got the Org name so look to see if an organization with
789 ** this name already exists.
790 */
791 open csr_chk_org_name;
792 fetch csr_chk_org_name into l_organization_id;
793
794 if (csr_chk_org_name%NOTFOUND) then
795 hr_utility.set_location(l_proc,125);
796
797 if l_org_profile = 'SCO' then
798 /*
799 ** No organization exists with this name and we want to create missing
800 ** organizations so go ahead and create one with the required
801 ** classifications.
802 */
803 close csr_chk_org_name;
804
805 begin
806 hr_utility.set_location(l_proc||' org name '||l_org_name,130);
807 hr_organization_api.create_organization
808 (p_effective_date => sysdate
809 ,p_business_group_id => p_bg_id
810 ,p_date_from => l_org_start_dt
811 ,p_name => l_org_name
812 ,p_internal_external_flag => 'INT'
813 ,p_organization_id => l_organization_id
814 ,p_object_version_number => l_org_ovn);
815 writelog('Completed create_organization, org_id = '||l_organization_id,'D');
816 l_new_org_created := TRUE;
817 hr_utility.set_location(l_proc,140);
818 exception
819 when OTHERS then
820 /* We got an error in creating the org. Write this to the log file and
821 ** stop processing this company cost center.
822 */
823 writelog('Error while creating Organization, name = '|| l_org_name,'W');
824 writelog('Error : '|| SQLCODE ||' - '|| SQLERRM,'W');
825 return -1;
826 end;
827
828 if (l_class_profile LIKE 'CC%') then
829 /* The HR_GENERATE_GL_ORGS contains the token indicating we should create
830 ** a Company Cost Center(Class = 'CC') classification/info type.
831 */
832 begin
833 hr_utility.set_location(l_proc,150);
834 hr_organization_api.create_company_cost_center
835 (p_effective_date => sysdate
836 ,p_organization_id=>l_organization_id
837 ,p_company_valueset_id => p_company_vs
838 ,p_company => p_company
839 ,p_costcenter_valueset_id => p_cc_vs
840 ,p_costcenter => p_cost_center
841 ,p_ori_org_information_id => l_ori_inf_id
842 ,p_ori_object_version_number => l_ori_ovn
843 ,p_org_information_id => l_class_id
844 ,p_object_version_number=>l_class_ovn);
845
846 writelog('Completed creating CC classification, org_info_id = '||
847 l_ori_inf_id,'D');
848 exception
849 when OTHERS then
850 /* We got an error in creating the classificationand info type. Write this to
851 ** the log file and stop processing this company cost center.
852 */
853 writelog('Error while creating CC classification, org_name = '|| l_org_name,'W');
854 writelog('Error : '|| SQLCODE ||' - '|| SQLERRM, 'W');
855 return -1;
856 end;
857 end if;
858 hr_utility.set_location(l_proc,160);
859
860 if (l_class_profile = 'CCHR') then
861 /* The HR_GENERATE_GL_ORGS contains the token indicating we should create
862 ** an HR Organization(Class = 'HR_ORG') classification.
863 */
864 open csr_chk_org_class(l_organization_id, 'HR_ORG');
865 fetch csr_chk_org_class into l_enabled_flag, l_class_id, l_class_ovn;
866
867 if csr_chk_org_class%NOTFOUND then
868 hr_utility.set_location(l_proc,300);
869
870
871 begin
872 hr_utility.set_location(l_proc,170);
873 hr_organization_api.create_org_classification
874 (p_effective_date => sysdate
875 ,p_organization_id=>l_organization_id
876 ,p_org_classif_code =>'HR_ORG'
877 ,p_org_information_id => l_class_id
878 ,p_object_version_number => l_class_ovn);
879 exception
880 when OTHERS then
881 IF csr_chk_org_class%isopen then
882 close csr_chk_org_class;
883 end if;
884 /* We got an error in creating the classification. Write this to
885 ** the log file and stop processing this company cost center.
886 */
887 writelog('Error while creating Org classification, org_name = '|| l_org_name,'W');
888 writelog('Error : '|| SQLCODE ||' - '|| SQLERRM, 'W');
889 return -1;
890 end;
891 end if; /* csr_chk_org_class */
892
893 IF csr_chk_org_class%isopen then
894 close csr_chk_org_class;
895 end if;
896
897 end if;
898
899 end if; /* l_org_profile = 'SCO' */
900
901 else /* csr_chk_org_name%NOTFOUND */
902
903 /* We found an organization which matches by name so we'll do the following...
904 **
905 ** Check for the presence of a Company Cost Center org classification.
906 ** If we have one then (A)
907 ** check the classified org has the cost center info type.
908 ** if the org info type is not present then (B)
909 ** Add the cost center org info type
910 ** if the org info type is present with no data then (C)
911 ** Update the info type and link the org to the current cost center.
912 ** if the org info type is present with data then (D)
913 ** Derive an unique org name and create a new org with classification as required.
914 ** if we don't have an org classification then (E)
915 ** Create the Company Cost Center Org classification and info type.
916 ** Add the HR_ORG classification if the profile indicates it's required and the
917 ** organization does not have it already.
918 */
919 hr_utility.set_location(l_proc,180);
920 close csr_chk_org_name;
921
922 open csr_chk_org_class(l_organization_id, 'CC');
923 fetch csr_chk_org_class into l_enabled_flag, l_class_id, l_class_ovn;
924
925 if csr_chk_org_class%FOUND then --> A
926 hr_utility.set_location(l_proc,190);
927 close csr_chk_org_class;
928
929 open csr_chk_info_type(l_organization_id, 'Company Cost Center');
930 fetch csr_chk_info_type into l_company, l_company_vs, l_cost_center,
931 l_cost_center_vs;
932
933 if csr_chk_info_type%NOTFOUND then --> B
934 /*
935 ** The org exists with the CC class but without the info type therefore
936 ** create the Company Cost Center org information type.
937 */
938 hr_utility.set_location(l_proc,200);
939 close csr_chk_info_type;
940
941 begin
942 if l_enabled_flag = 'N' then
943 /* The class is disabled. Temporarily enable it.
944 */
945 hr_organization_api.enable_org_classification(
946 p_effective_date => sysdate,
947 p_org_information_id => l_class_id,
948 p_org_info_type_code => 'CC',
949 p_object_version_number => l_class_ovn);
950 end if;
951
952 hr_utility.set_location(l_proc,210);
953 hr_organization_api.create_org_information
954 (p_effective_date => sysdate
955 ,p_organization_id=>l_organization_id
956 ,p_org_info_type_code => 'Company Cost Center'
957 ,p_org_information2 => p_company_vs
958 ,p_org_information3 => p_company
959 ,p_org_information4 => p_cc_vs
960 ,p_org_information5 => p_cost_center
961 ,p_org_information_id => l_ori_inf_id
962 ,p_object_version_number=>l_ori_ovn);
963
964 writelog('Completed creating CC classification, org_info_id = '||
965 l_ori_inf_id,'D');
966
967 if l_enabled_flag = 'N' then
968 /* The class is disabled. Reset it....
969 */
970 hr_organization_api.disable_org_classification(
971 p_effective_date => sysdate,
972 p_org_information_id => l_class_id,
973 p_org_info_type_code => 'CC',
974 p_object_version_number => l_class_ovn);
975
976 end if;
977
978 exception
979 when OTHERS then
980 /* We got an error in creating the info type. Write this to
981 ** the log file and stop processing this company cost center.
982 */
983 writelog('Error while creating Company Cost Center info type,
984 org_name = '||l_org_name,'W');
985 writelog('Error : '|| SQLCODE ||' - '|| SQLERRM,'W');
986 return -1;
987 end;
988
989 if (l_class_profile = 'CCHR') then
990 /* The HR_GENERATE_GL_ORGS contains the token indicating we should create
991 ** an HR Organization(Class = 'HR_ORG') classification.
992 */
993 open csr_chk_org_class(l_organization_id, 'HR_ORG');
994 fetch csr_chk_org_class into l_enabled_flag, l_class_id, l_class_ovn;
995
996 if csr_chk_org_class%NOTFOUND then
997 hr_utility.set_location(l_proc,310);
998
999
1000 begin
1001 hr_utility.set_location(l_proc,220);
1002 hr_organization_api.create_org_classification
1003 (p_effective_date => sysdate
1004 ,p_organization_id=>l_organization_id
1005 ,p_org_classif_code =>'HR_ORG'
1006 ,p_org_information_id => l_class_id
1007 ,p_object_version_number => l_class_ovn);
1008 exception
1009 when OTHERS then
1010 IF csr_chk_org_class%isopen then
1011 close csr_chk_org_class;
1012 end if;
1013 /* We got an error in creating the classification. Write this to
1014 ** the log file and stop processing this company cost center.
1015 */
1016 writelog('Error while creating Org classification, org_name = '|| l_org_name,'W');
1017 writelog('Error : '|| SQLCODE ||' - '|| SQLERRM, 'W');
1018 return -1;
1019 end;
1020 end if; /* csr_chk_org_class */
1021 IF csr_chk_org_class%isopen then
1022 close csr_chk_org_class;
1023 end if;
1024
1025 end if; /* l_org_profile = 'CCHR' */
1026
1027 else /* csr_chk_info_type%NOTFOUND */
1028 /*
1029 ** Check the data on the info type and proceed as appropriate.
1030 */
1031 hr_utility.set_location(l_proc,220);
1032 close csr_chk_info_type;
1033
1034 if l_company is null
1035 and l_cost_center is null
1036 and l_company_vs is null
1037 and l_cost_center_vs is null then --> C
1038 /*
1039 ** The info type is present but empty. This should never happen since
1040 ** the Company Value Set segment is mandatory.
1041 */
1042 hr_utility.set_location(l_proc,230);
1043 null;
1044 else --> D
1045 /*
1046 ** The info type is present with data but to have got to this point
1047 ** the data for the organization must be different to what we have
1048 ** for the current cost center. We need to derive a new org name
1049 ** and create the org, the classification and the info type if we
1050 ** are creating new orgs.
1051 */
1052 hr_utility.set_location(l_proc,240);
1053
1054 /*
1055 ** Reset the organization ID since the org we found is of no use
1056 ** to us.
1057 */
1058 l_organization_id := -1;
1059
1060 if l_org_profile = 'SCO' then
1061 /*
1062 ** We are creating missing organizations so continue...
1063 */
1064
1065 l_number_of_retries := 1;
1066 /*
1067 ** We need to find an organization name which is not used and then create the org
1068 ** using that name. We will use a similar method to that used by FNDLOAD to resolve
1069 ** duplicate description/meaning values by suffixing "(n)" to the end of the derived
1070 ** org name increasing n until we find a unique name.
1071 **
1072 ** Since we already know an org exists with the name we derived modify the name
1073 ** we have to add the first possible suffix.
1074 */
1075 l_org_name_suffix := ' ('||to_char(l_number_of_retries)||')';
1076 l_org_name := substrb(l_org_name,1,
1077 g_org_name_length-length(l_org_name_suffix))||
1078 l_org_name_suffix;
1079 loop
1080 hr_utility.set_location(l_proc,250);
1081 l_organization_id := null;
1082 open csr_chk_org_name;
1083 fetch csr_chk_org_name into l_organization_id;
1084 close csr_chk_org_name;
1085 /*
1086 ** If the above did not find anything exit otherwise we'll carry on....
1087 ** but only so far, exit if we hit the max number of tries...
1088 */
1089 exit when l_organization_id is null;
1090 exit when l_number_of_retries = g_max_number_of_retries;
1091
1092 /*
1093 ** To get this far the org name is already in use, so let's add
1094 ** a suffix and try that...
1095 */
1096 l_org_name_suffix := ' ('||to_char(l_number_of_retries)||')';
1097 l_org_name := substrb(l_org_name,1,
1098 g_org_name_length-length(l_org_name_suffix))||
1099 l_org_name_suffix;
1100 l_number_of_retries := l_number_of_retries + 1;
1101
1102 end loop;
1103
1104 if ( l_organization_id IS NULL
1105 AND l_number_of_retries <> g_max_number_of_retries) then
1106 /*
1107 ** We got an unused org name so create the org...
1108 */
1109 hr_utility.set_location(l_proc,260);
1110
1111 begin
1112 hr_utility.set_location(l_proc||' org name '||l_org_name,130);
1113 hr_organization_api.create_organization
1114 (p_effective_date => sysdate
1115 ,p_business_group_id => p_bg_id
1116 ,p_date_from => l_org_start_dt
1117 ,p_name => l_org_name
1118 ,p_internal_external_flag => 'INT'
1119 ,p_organization_id => l_organization_id
1120 ,p_object_version_number => l_org_ovn);
1121 writelog('Completed create_organization, org_id = '||l_organization_id,'D');
1122 l_new_org_created := TRUE;
1123 hr_utility.set_location(l_proc,140);
1124 exception
1125 when OTHERS then
1126 /* We got an error in creating the org. Write this to the log file and
1127 ** stop processing this company cost center.
1128 */
1129 writelog('Error while creating Organization, name = '|| l_org_name,'W');
1130 writelog('Error : '|| SQLCODE ||' - '|| SQLERRM,'W');
1131 return -1;
1132 end;
1133
1134 if (l_class_profile LIKE 'CC%') then
1135 /* The HR_GENERATE_GL_ORGS contains the token indicating we should create
1136 ** a Company Cost Center(Class = 'CC') classification/info type.
1137 */
1138 begin
1139 hr_utility.set_location(l_proc,150);
1140 hr_organization_api.create_company_cost_center
1141 (p_effective_date => sysdate
1142 ,p_organization_id=>l_organization_id
1143 ,p_company_valueset_id => p_company_vs
1144 ,p_company => p_company
1145 ,p_costcenter_valueset_id => p_cc_vs
1146 ,p_costcenter => p_cost_center
1147 ,p_ori_org_information_id => l_ori_inf_id
1148 ,p_ori_object_version_number => l_ori_ovn
1149 ,p_org_information_id => l_class_id
1150 ,p_object_version_number=>l_class_ovn);
1151
1152 writelog('Completed creating CC classification, org_info_id = '||
1153 l_ori_inf_id,'D');
1154 exception
1155 when OTHERS then
1156 /* We got an error in creating the classificationand info type. Write this to
1157 ** the log file and stop processing this company cost center.
1158 */
1159 writelog('Error while creating CC classification, org_name = '|| l_org_name,'W');
1160 writelog('Error : '|| SQLCODE ||' - '|| SQLERRM, 'W');
1161 return -1;
1162 end;
1163 end if; /* l_org_profile LIKE 'CC%' */
1164 hr_utility.set_location(l_proc,160);
1165
1166 if (l_class_profile = 'CCHR') then
1167 /* The HR_GENERATE_GL_ORGS contains the token indicating we should create
1168 ** an HR Organization(Class = 'HR_ORG') classification.
1169 */
1170 open csr_chk_org_class(l_organization_id, 'HR_ORG');
1171 fetch csr_chk_org_class into l_enabled_flag, l_class_id, l_class_ovn;
1172
1173 if csr_chk_org_class%NOTFOUND then
1174 hr_utility.set_location(l_proc,315);
1175
1176 begin
1177 hr_utility.set_location(l_proc,170);
1178 hr_organization_api.create_org_classification
1179 (p_effective_date => sysdate
1180 ,p_organization_id=>l_organization_id
1181 ,p_org_classif_code =>'HR_ORG'
1182 ,p_org_information_id => l_class_id
1183 ,p_object_version_number => l_class_ovn);
1184 exception
1185 when OTHERS then
1186 IF csr_chk_org_class%isopen then
1187 close csr_chk_org_class;
1188 end if;
1189 /* We got an error in creating the classification. Write this to
1190 ** the log file and stop processing this company cost center.
1191 */
1192 writelog('Error while creating Org classification, org_name = '|| l_org_name,'W');
1193 writelog('Error : '|| SQLCODE ||' - '|| SQLERRM, 'W');
1194 return -1;
1195 end;
1196 end if ;/* csr_chk_org_class */
1197 IF csr_chk_org_class%isopen then
1198 close csr_chk_org_class;
1199 end if;
1200
1201 end if; /* l_org_profile = 'CCHR' */
1202
1203 else
1204 /*
1205 ** We exited the loop above without finding a unique org name
1206 ** so raise an error to this effect.
1207 ** Need to do the same messing about as earlier as this has tokens....
1208 */
1209 hr_utility.set_message(801,'HR_289490_NO_UNIQ_NAME');
1210 fnd_message.set_token('ORG_NAME',l_orig_org_name);
1211 l_message := fnd_message.get();
1212 hr_utility.set_message(801,'HR_289490_NO_UNIQ_NAME');
1213 fnd_message.set_token('ORG_NAME',l_orig_org_name);
1214 writelog(l_message,'W');
1215 return -1;
1216 end if; /* l_organization_id IS NULL */
1217
1218 end if; /* l_org_profile = SCO */
1219
1220 end if; /* l_company is null */
1221
1222 end if; /* csr_chk_info_type%NOTFOUND */
1223
1224 else /* --> E csr_chk_org_class%FOUND */
1225 /*
1226 ** We don't have a company cost center classification for this org so
1227 ** create it together with the info type data.
1228 */
1229 hr_utility.set_location(l_proc,250);
1230 IF csr_chk_org_class%isopen then
1231 close csr_chk_org_class;
1232 end if;
1233
1234 if (l_class_profile LIKE 'CC%') then
1235 /* The HR_GENERATE_GL_ORGS contains the token indicating we should create
1236 ** a Company Cost Center(Class = 'CC') classification/info type.
1237 */
1238 begin
1239 hr_utility.set_location(l_proc,150);
1240 hr_organization_api.create_company_cost_center
1241 (p_effective_date => sysdate
1242 ,p_organization_id=>l_organization_id
1243 ,p_company_valueset_id => p_company_vs
1244 ,p_company => p_company
1245 ,p_costcenter_valueset_id => p_cc_vs
1246 ,p_costcenter => p_cost_center
1247 ,p_ori_org_information_id => l_ori_inf_id
1248 ,p_ori_object_version_number => l_ori_ovn
1249 ,p_org_information_id => l_class_id
1250 ,p_object_version_number=>l_class_ovn);
1251
1252 writelog('Completed creating CC classification, org_info_id = '||
1253 l_ori_inf_id,'D');
1254 exception
1255 when OTHERS then
1256 /* We got an error in creating the classificationand info type. Write this to
1257 ** the log file and stop processing this company cost center.
1258 */
1259 writelog('Error while creating CC classification, org_name = '|| l_org_name,'W');
1260 writelog('Error : '|| SQLCODE ||' - '|| SQLERRM, 'W');
1261 return -1;
1262 end;
1263 end if; /* l_org_profile LIKE 'CC%' */
1264 hr_utility.set_location(l_proc,160);
1265
1266 if (l_class_profile = 'CCHR') then
1267 /* The HR_GENERATE_GL_ORGS contains the token indicating we should create
1268 ** an HR Organization(Class = 'HR_ORG') classification. Only do this if
1269 ** the org does not already have this classification.
1270 */
1271 open csr_chk_org_class(l_organization_id, 'HR_ORG');
1272 fetch csr_chk_org_class into l_enabled_flag, l_class_id, l_class_ovn;
1273
1274 if csr_chk_org_class%NOTFOUND then
1275 hr_utility.set_location(l_proc,320);
1276 begin
1277 hr_utility.set_location(l_proc,170);
1278 hr_organization_api.create_org_classification
1279 (p_effective_date => sysdate
1280 ,p_organization_id=>l_organization_id
1281 ,p_org_classif_code =>'HR_ORG'
1282 ,p_org_information_id => l_class_id
1283 ,p_object_version_number => l_class_ovn);
1284 exception
1285 when OTHERS then
1286 /* We got an error in creating the classification. Write this to
1287 ** the log file and stop processing this company cost center.
1288 */
1289 IF csr_chk_org_class%isopen then
1290 close csr_chk_org_class;
1291 end if;
1292 writelog('Error while creating Org classification, org_name = '||
1293 l_org_name,'W');
1294 writelog('Error : '|| SQLCODE ||' - '|| SQLERRM, 'W');
1295 return -1;
1296 end;
1297
1298 end if; /*csr_chk_org_class%NOTFOUND */
1299 IF csr_chk_org_class%isopen then
1300 close csr_chk_org_class;
1301 end if;
1302
1303 end if; /* l_org_profile = 'CCHR' */
1304
1305 end if; /* csr_chk_org_class%FOUND */
1306
1307 end if; /* csr_chk_org_name%NOTFOUND */
1308
1309 if l_organization_id > -1 then
1310 if l_new_org_created then
1311 hr_utility.set_message(800,'HR_289195_SYNCD_NEW_ORG');
1312 fnd_message.set_token('NAME',l_org_name);
1313 fnd_message.set_token('COMPANY',p_company);
1314 fnd_message.set_token('COST',p_cost_center);
1315 l_message := fnd_message.get();
1316 else
1317 hr_utility.set_message(800,'HR_289177_SYNCD_ORG');
1318 fnd_message.set_token('NAME',l_org_name);
1319 fnd_message.set_token('COMPANY',p_company);
1320 fnd_message.set_token('COST',p_cost_center);
1321 l_message := fnd_message.get();
1322 end if;
1323 /*
1324 ** ...now pass the string to writelog.
1325 */
1326 writelog(l_message,'I');
1327 end if;
1328 hr_utility.set_location('Leaving : '||l_proc,180);
1329 p_org_id := l_organization_id;
1330 return 0;
1331
1332 end create_org_and_classification;
1333
1334
1335 function getBGID(p_company IN VARCHAR2
1336 ,p_companyvs IN NUMBER)
1337 return NUMBER is
1338 l_proc varchar2(50) := g_package||'getBGID';
1339 l_bgid number := -1;
1340 l_count NUMBER := 0;
1341
1342 TYPE number_tab IS TABLE OF number;
1343 l_bg_tab number_tab;
1344
1345 cursor company_bg is
1346 select distinct units.business_group_id
1347 from hr_all_organization_units units,
1348 hr_organization_information class,
1349 hr_organization_information cc
1350 where units.organization_id = class.organization_id
1351 and class.org_information_context='CLASS'
1352 and class.org_information1='CC'
1353 and class.organization_id = cc.organization_id
1354 and cc.org_information_context = 'Company Cost Center'
1355 and cc.org_information3 = p_company
1356 and cc.org_information2 = to_char(p_companyvs)
1357 and cc.org_information4 is null
1358 and cc.org_information5 is null;
1359
1360 begin
1361 hr_utility.set_location('Entering : '||l_proc,10);
1362
1363 open company_bg;
1364 fetch company_bg bulk collect into l_bg_tab limit 10;
1365 close company_bg;
1366
1367 hr_utility.set_location(l_proc,20);
1368
1369 if (l_bg_tab.count > 1) then -- This company exists in more than 1 BG, so error
1370 hr_utility.set_location(l_proc,30);
1371 l_bgid:=-1;
1372 writelog(fnd_message.get_string('PER','HR_289491_CO_MULT_BG'),'E');
1373 elsif (l_bg_tab.count = 0) then -- This company does not exist in any BG so error
1374 hr_utility.set_location(l_proc,30);
1375 l_bgid:=-1;
1376 writelog('Company : '||p_company,'D');
1377 writelog('Company VS: '||to_char(p_companyvs),'D');
1378 writelog(fnd_message.get_string('PER','HR_289601_CO_NO_BG'),'E');
1379 elsif l_bg_tab.count = 1 then
1380 hr_utility.set_location(l_proc,40);
1381 l_bgid := l_bg_tab(1);
1382 writelog('bg id '||l_bgid,'D');
1383 hr_utility.set_location(l_proc||' BG id '||to_char(l_bgid),45);
1384 end if;
1385
1386 hr_utility.set_location('Leaving : '||l_proc,50);
1387 return l_bgid;
1388 END;
1389
1390
1391 /*---------------------------------------------------------------------------
1392 Find all the different Chart of Accounts where company_cost_center_org_id is
1393 null.
1394 For each COA, get the distinct company cost center combinations for which
1395 company_cost_center_org_id is null.
1396 For each of these combinations, find the business group for the company.
1397
1398 ---------------------------------------------------------------------------*/
1399
1400
1401 function reportingMode(p_application_short_name in varchar2,
1402 p_hr_cc_reporting_temp in varchar2,
1403 p_bgid in number,
1404 p_coa in number) -- Fix for Bug 2875915
1405 return NUMBER is
1406
1407 l_proc VARCHAR2(50) := g_package||'.reportingMode';
1408 l_no_flex_value exception;
1409
1410 l_company_segment VARCHAR2(40) := null;
1411 l_cc_segment VARCHAR2(40) := null;
1412 l_company VARCHAR2(240) :=NULL;
1413 l_cost_center VARCHAR2(240) := NULL;
1414 l_chart_of_accounts_id NUMBER := 0;
1415 l_company_vs NUMBER := -1;
1416 l_cc_vs NUMBER := -1;
1417
1418 l_schema varchar2(30) := null;
1419 l_stmt varchar2(1000) := null;
1420 l_stmt2 varchar2(1000) := null;
1421
1422 l_org_stmt VARCHAR2(1000) := null;
1423 l_temp_stmt varchar2(300) := null;
1424 l_org_name HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE := null;
1425 l_retcode number := 0;
1426 l_retcode1 number :=0;
1427 l_org_name_format varchar2(60) := null;
1428 l_stmt1 varchar2(2000) :=null;
1429 l_comp_start_date_active date;
1430 l_cc_start_date_active date;
1431 l_company_name varchar2(240) := null;
1432 l_cost_center_name varchar2(240) := null;
1433
1434 cursor c_coas is
1435 select distinct chart_of_accounts_id
1436 from gl_sets_of_books --Bug 3264485
1437 where chart_of_accounts_id = p_coa
1438 or p_coa is null ; -- Fix for Bug 2875915
1439
1440 type curType is ref cursor;
1441 c_list curType;
1442 c_list2 curType;
1443 l_rowcount number;
1444 -----
1445
1446 cursor c_value(p_vs number, p_segment varchar2) is
1447 select description, start_date_active
1448 from fnd_flex_values_vl
1449 where flex_value_set_id= p_vs
1450 and p_segment = flex_value;
1451
1452 ----
1453 begin
1454 hr_utility.set_location('Entering : '||l_proc,10);
1455
1456 begin
1457 droptable(p_hr_cc_reporting_temp,TRUE);
1458 end;
1459 hr_utility.set_location(l_proc,20);
1460
1461 execute immediate 'create table '||p_hr_cc_reporting_temp ||'
1462 (cc_value_set number,
1463 company_value_set number,
1464 chart_of_accounts_id number,
1465 company varchar2(240),
1466 cost_center varchar2(240),
1467 org_name varchar2(240),
1468 business_group_id number,
1469 business_group_name varchar2(240),
1470 company_name varchar2(240),
1471 cost_center_name varchar2(240),
1472 org_start_date date)'; -- Bug Fix 3208451
1473
1474 for l_coas in c_coas
1475 loop
1476 --
1477
1478 hr_utility.set_location(l_proc||' chart of acct ID '||
1479 to_char(l_coas.chart_of_accounts_id),30);
1480
1481 l_retcode := getSegmentForQualifier(l_coas.chart_of_accounts_id,
1482 'GL_BALANCING',
1483 l_company_segment);
1484 l_retcode := getSegmentForQualifier(l_coas.chart_of_accounts_id,
1485 'FA_COST_CTR',
1486 l_cc_segment);
1487 l_retcode := getValueSetForSegment(l_coas.chart_of_accounts_id,
1488 l_company_segment,
1489 l_company_vs);
1490 l_retcode := getValueSetForSegment(l_coas.chart_of_accounts_id,
1491 l_cc_segment,
1492 l_cc_vs);
1493
1494
1495 if (l_cc_segment is NOT NULL and
1496 l_company_segment IS NOT NULL and
1497 l_company_vs <> -1 and
1498 l_cc_vs <> -1) then
1499 hr_utility.set_location(l_proc,40);
1500
1501 open c_list2 for 'SELECT /*+ORDERED USE_NL(gcc)*/
1502 distinct gcc.'||l_company_segment||' company,
1503 gcc.'||l_cc_segment ||' cost_center
1504 FROM gl_code_combinations gcc
1505 WHERE gcc.company_cost_center_org_id is null
1506 AND gcc.summary_flag = ''N''
1507 AND gcc.chart_of_accounts_id = '||l_coas.chart_of_accounts_id||
1508 'AND gcc.'||l_company_segment||' is not null
1509 AND gcc.'||l_cc_segment ||' is not null';
1510
1511 --
1512 loop
1513 fetch c_list2 into l_company, l_cost_center;
1514
1515 exit when c_list2%notfound;
1516 begin
1517 --
1518 -- writelog('l_coas.chart_of_accounts_id'||l_coas.chart_of_accounts_id);
1519 -- writelog('l_company '||l_company);
1520 -- writelog('l_cc '||l_cost_center);
1521 -- writelog('l_company_vs '||l_company_vs);
1522 -- writelog('l_cc_vs '||l_cc_vs);
1523 --
1524 open c_value (l_company_vs, l_company);
1525 fetch c_value into l_company_name,l_comp_start_date_active;
1526 if c_value%notfound then
1527 close c_value;
1528 raise l_no_flex_value;
1529 end if;
1530 close c_value;
1531 --
1532 open c_value (l_cc_vs, l_cost_center);
1533 fetch c_value into l_cost_center_name,l_cc_start_date_active;
1534 if c_value%notfound then
1535 close c_value;
1536 raise l_no_flex_value;
1537 end if;
1538 close c_value;
1539 --
1540
1541 l_stmt := 'INSERT INTO '||p_hr_cc_reporting_temp ||'
1542 (cc_value_set,
1543 company_value_set,
1544 chart_of_accounts_id,
1545 company,
1546 cost_center,
1547 business_group_id,
1548 company_name,
1549 cost_center_name,
1550 org_start_date)
1551 VALUES (:1,
1552 :2,
1553 :3,
1554 :4,
1555 :5,
1556 :6,
1557 :7,
1558 :8,
1559 greatest(nvl(:9 ,to_date(''01/01/1900'',''DD/MM/RRRR'')),
1560 nvl(:10 ,to_date(''01/01/1900'',''DD/MM/RRRR''))))';
1561 -- writelog ('SUCCESS');
1562 execute immediate l_stmt using l_cc_vs,
1563 l_company_vs,
1564 l_coas.chart_of_accounts_id,
1565 l_company,
1566 l_cost_center,
1567 p_bgid,
1568 l_company_name,
1569 l_cost_center_name,
1570 l_cc_start_date_active,
1571 l_comp_start_date_active;
1572
1573 --
1574 exception
1575 when l_no_flex_value then
1576 writelog('Error while processing for Code Combination Id:'||l_coas.chart_of_accounts_id, 'W' );
1577 writelog('having Cost Center Code :'||l_cost_center, 'W');
1578 writelog('and Company Code :'||l_company, 'W');
1579 writelog ('Processing is terminated for this combination', 'W');
1580 l_retcode1 := 1;
1581 end;
1582
1583 end loop;
1584 close c_list2;
1585 --
1586 --
1587 hr_utility.set_location(l_proc,50);
1588 /*
1589 ** We have populated the temp table with company cost centers for the
1590 ** current COA. Now check the rows in the temp table. If we don't
1591 ** have any tell the user.
1592 */
1593 open c_list for 'select count(*) from '||p_hr_cc_reporting_temp;
1594 fetch c_list into l_rowcount;
1595 close c_list;
1596 if l_rowcount = 0 then
1597 writelog(fnd_message.get_string('PER','HR_289178_NO_CC_PROC'),'W');
1598 l_retcode := 1;
1599 return l_retcode;
1600 end if;
1601 else
1602 l_retcode := reportMissingFlex(
1603 p_chart_of_accounts_id => l_coas.chart_of_accounts_id,
1604 p_company_segment => l_company_segment,
1605 p_cc_segment => l_cc_segment,
1606 p_company_vs => l_company_vs,
1607 p_cc_vs => l_cc_vs);
1608 end if;
1609
1610
1611 end loop;
1612 hr_utility.set_location(l_proc,70);
1613
1614 /*
1615 ** Delete records from the working table which are for companies which do
1616 ** not exist in the business group. Because GL CC table spans multiple
1617 ** business groups you need to define the companies in the appropriate BG
1618 ** first(a manual setup step) and then run sync orgs. This enables you to
1619 ** run the sync orgs process for a business group and only pull in those
1620 ** company cost centers which relate to this business group (i.e those
1621 ** which are for companies already defined in this business group.
1622 **
1623 ** A possible enhancement here - instead of inserting all the data and then
1624 ** deleting some, merge this into a single insert which only inserts cost
1625 ** centers for companies in this business group. We probably should not
1626 ** implement this enhancement as it would prevent us from determining if the
1627 ** temporary table was empty because there are no GL cost centers to process
1628 ** or because the setup is incorrect and no companies have been defined in
1629 ** the current BG.
1630 */
1631 l_stmt := 'delete from '||p_hr_cc_reporting_temp||' rep
1632 where (company,company_value_set) not in
1633 ( select distinct cc.org_information3,
1634 cc.org_information2
1635 from hr_all_organization_units units,
1636 hr_all_organization_units_tl unitstl,
1637 hr_organization_information class,
1638 hr_organization_information cc
1639 where units.organization_id = class.organization_id
1640 and units.organization_id = unitstl.organization_id
1641 and class.org_information_context=''CLASS''
1642 and class.org_information1=''CC''
1643 and class.organization_id = cc.organization_id
1644 and cc.org_information_context = ''Company Cost Center''
1645 and units.business_group_id = :1
1646 and cc.org_information4 is null
1647 and cc.org_information5 is null)';
1648
1649 execute immediate l_stmt using p_bgid;
1650 hr_utility.set_location(l_proc,80);
1651
1652 /*
1653 ** We have populated the temp table and deleted records from it
1654 ** if no company organization has been defined in the business
1655 ** group. Now check the rows in the temp table. If we don't
1656 ** have any tell the user.
1657 */
1658 open c_list for 'select count(*) from '||p_hr_cc_reporting_temp;
1659 fetch c_list into l_rowcount;
1660 close c_list;
1661 if l_rowcount = 0 then
1662 writelog(fnd_message.get_string('PER','HR_289599_NO_CC_PROC'),'W');
1663 l_retcode := 1;
1664 else
1665
1666 /* Read the Org Name Format */
1667 l_org_name_format := fnd_profile.value('HR_GL_ORG_NAME_FORMAT');
1668 if (l_org_name_format IS NULL) then
1669 hr_utility.set_location(l_proc,90);
1670 hr_utility.set_message(801,'HR_289489_NO_NAME_FORMAT');
1671 writelog(fnd_message.get_string('PER','HR_289489_NO_NAME_FORMAT'),'E');
1672 end if;
1673
1674 writelog('Org Format is : '||l_org_name_format,'D');
1675
1676 l_stmt := 'UPDATE '||p_hr_cc_reporting_temp||' rep set (org_name) =
1677 (select substrb(REPLACE(
1678 REPLACE(
1679 REPLACE(
1680 REPLACE(:l_org_name_format,''$COC'',
1681 rep.company),
1682 ''$CCC'', rep.cost_center),
1683 ''$CON'', rep.company_name),
1684 ''$CCN'', rep.cost_center_name), 1,
1685 :g_org_name_length)
1686 from dual)';
1687
1688 execute immediate l_stmt using l_org_name_format, g_org_name_length;
1689 hr_utility.set_location(l_proc,80);
1690
1691 -- Now check if Org Names are duplicated for different value sets
1692
1693 l_stmt := 'update '||p_hr_cc_reporting_temp||' rep
1694 set org_name = substrb(org_name, 1, '||
1695 to_char(g_org_name_length)||'-length(''-''||
1696 company_value_set||
1697 ''-''||cc_value_set))
1698 ||''-''||company_value_set ||''-''||cc_value_set
1699 where rep.org_name in
1700 (select distinct a.org_name
1701 from '||p_hr_cc_reporting_temp||' a, '||p_hr_cc_reporting_temp||' b
1702 where a.org_name = b.org_name and
1703 (a.cc_value_set <> b.cc_value_set
1704 or a.company_value_set<>b.company_value_set))';
1705
1706 execute immediate l_stmt;
1707
1708 /* Bug 3208451
1709 ** If the start date is '01/01/1900' then update it to default
1710 ** date 01/01/1990
1711 */
1712 execute immediate 'UPDATE '||p_hr_cc_reporting_temp||' rep
1713 set (org_start_date) = to_date(''01/01/1990'',''DD/MM/RRRR'')
1714 Where to_char(org_start_date,''DD/MM/RRRR'') = (''01/01/1900'')';
1715
1716 writelog(' Done processing. Spooling to file now.','D');
1717 spoolToFile(p_hr_cc_reporting_temp);
1718 end if;
1719 hr_utility.set_location('Leaving : '||l_proc,100);
1720 -- commit;
1721 if l_retcode= 0 then
1722 return l_retcode1;
1723 end if;
1724 return l_retcode;
1725 end;
1726
1727
1728 function createSyncTempTable(p_hr_sync_temp in varchar2,
1729 p_hr_sync_ccid in varchar2,
1730 p_bgid in number,
1731 p_schema in varchar2,
1732 p_coa in number)
1733 return NUMBER is
1734
1735 l_proc VARCHAR2(50) := g_package||'.createSyncTempTable';
1736
1737 l_chart_of_accounts_id NUMBER :=0;
1738 l_dummy1 varchar2(2000);
1739 l_dummy2 varchar2(2000);
1740 l_schema varchar2(400);
1741 l_stmt VARCHAR2(3000);
1742
1743 l_company varchar2(240); /* The column name of the segment holding the
1744 company value */
1745 l_cost_center varchar2(240); /* The column name of the segment holding the
1746 cost center value */
1747 l_company_vs number(15) := -1; /* The ID of the company valueset. */
1748 l_cc_vs number(15) := -1; /* The ID of the cost center valueset. */
1749
1750 errbuf varchar2(240);
1751 l_retcode varchar2(10);
1752
1753 l_temp_table varchar2(30) := null;
1754
1755 cursor c_coas is
1756 select distinct chart_of_accounts_id
1757 from gl_sets_of_books --Bug 3264485
1758 where chart_of_accounts_id = p_coa
1759 or p_coa is null ; -- Fix for Bug 2875915
1760 --
1761
1762 type curType is ref cursor;
1763 c_list curType;
1764 l_rowcount number;
1765
1766 BEGIN
1767 hr_utility.set_location('Entering : '||l_proc,10);
1768
1769 droptable(p_hr_sync_temp, TRUE );
1770 droptable(p_hr_sync_ccid, TRUE );
1771
1772 /*
1773 ** This table is used to hold information about the company and cost center
1774 ** from gl_code_combinations table for all chart of accounts which do not
1775 ** have a value for company_cost_center_org_id.
1776 */
1777 execute immediate 'create table '||p_hr_sync_temp ||' (
1778 ccid number(15),
1779 chart_of_accounts_id number(15),
1780 company varchar2(240),
1781 company_vs number(15),
1782 cc_vs number(15),
1783 cost_center varchar2(240),
1784 org_id NUMBER(15),
1785 business_group_id NUMBER(15))';
1786
1787 execute immediate 'create unique index '||p_hr_sync_temp||'_u1 on '||
1788 p_hr_sync_temp||'(ccid)';
1789
1790 execute immediate 'create index '||p_hr_sync_temp||'_n1 on '||
1791 p_hr_sync_temp||' (chart_of_accounts_id)';
1792
1793 hr_utility.set_location(l_proc,20);
1794
1795 for l_coas in c_coas
1796 loop
1797 hr_utility.set_location(l_proc,30);
1798 l_retcode := getSegmentForQualifier(l_coas.chart_of_accounts_id,
1799 'GL_BALANCING',
1800 l_company);
1801 l_retcode := getSegmentForQualifier(l_coas.chart_of_accounts_id,
1802 'FA_COST_CTR',
1803 l_cost_center);
1804 l_retcode := getValueSetForSegment(l_coas.chart_of_accounts_id,
1805 l_company,
1806 l_company_vs);
1807 l_retcode := getValueSetForSegment(l_coas.chart_of_accounts_id,
1808 l_cost_center,
1809 l_cc_vs);
1810
1811 if (l_company is NULL OR
1812 l_cost_center is null OR
1813 l_company_vs = -1 OR
1814 l_cc_vs = -1) then
1815 l_retcode := reportMissingFlex(
1816 p_chart_of_accounts_id => l_coas.chart_of_accounts_id,
1817 p_company_segment => l_company,
1818 p_cc_segment => l_cost_center,
1819 p_company_vs => l_company_vs,
1820 p_cc_vs => l_cc_vs);
1821 else
1822 hr_utility.set_location(l_proc,50);
1823 /*
1824 ** In the following SQL - l_company and l_cost_center hold the
1825 ** name of the segment column holding the company and cost center info
1826 ** cause the data from the corresponding column in the code comb table
1827 ** to be inserted into the temp table. i.e. if l_company holds the string
1828 ** 'SEGMENT1' then the value from GL_CODE_COMBINATIONS.SEGMENT1 is
1829 ** inserted into the tmp table.
1830 */
1831 execute immediate 'insert into '||p_hr_sync_temp ||'
1832 (ccid,
1833 chart_of_accounts_id,
1834 company,
1835 company_vs,
1836 cost_center,
1837 cc_vs)
1838 select code_combination_id,
1839 chart_of_accounts_id,'||
1840 l_company ||', '||l_company_vs||', '||
1841 l_cost_center||', '||l_cc_vs||'
1842 from gl_code_combinations
1843 where company_cost_center_org_id is null
1844 and summary_flag = ''N''
1845 and chart_of_accounts_id = :1'
1846 using l_coas.chart_of_accounts_id;
1847
1848 writelog('Inserted '||sql%rowcount||' records for COA = '||
1849 l_coas.chart_of_accounts_id,'D');
1850 if sql%rowcount = 0 then
1851 writelog(fnd_message.get_string('PER','HR_289178_NO_CC_PROC'),'W');
1852 /*
1853 ** We've got no rows in the temp table so bail out of the processing
1854 ** now as doing any further work is just inefficient.
1855 */
1856 raise g_no_cc_proc_exc;
1857 end if;
1858
1859 end if;
1860
1861 end loop;
1862 hr_utility.set_location(l_proc,60);
1863 -- commit;
1864
1865 /*
1866 ** Remove those records for which no company organization can be found.
1867 */
1868 l_stmt := 'delete from '||p_hr_sync_temp||' rep
1869 where (company,company_vs) not in
1870 ( select distinct cc.org_information3,
1871 cc.org_information2
1872 from hr_all_organization_units units,
1873 hr_all_organization_units_tl unitstl,
1874 hr_organization_information class,
1875 hr_organization_information cc
1876 where units.organization_id = class.organization_id
1877 and units.organization_id = unitstl.organization_id
1878 and class.org_information_context=''CLASS''
1879 and class.org_information1=''CC''
1880 and class.organization_id = cc.organization_id
1881 and cc.org_information_context = ''Company Cost Center''
1882 and units.business_group_id = :1
1883 and cc.org_information4 is null
1884 and cc.org_information5 is null)';
1885 execute immediate l_stmt using p_bgid;
1886 hr_utility.set_location(l_proc,62);
1887
1888 /*
1889 ** We have populated the temp table and excluded records from it
1890 ** if no company organization has been defined in the business
1891 ** group. Now check the rows in the temp table. If we don't
1892 ** have any tell the user.
1893 */
1894 open c_list for 'select count(*) from '||p_hr_sync_temp;
1895 fetch c_list into l_rowcount;
1896 close c_list;
1897 if l_rowcount = 0 then
1898 hr_utility.set_location(l_proc,64);
1899 writelog(fnd_message.get_string('PER','HR_289599_NO_CC_PROC'),'W');
1900 /*
1901 ** We've got no rows in the temp table so bail out of the processing
1902 ** now as doing any further work is just inefficient.
1903 */
1904 raise g_no_cc_proc_exc;
1905 else
1906 /*
1907 ** This temp table cross-references CCID and org ID for each record in
1908 ** gl_code_combinations table for which an organization with matching
1909 ** company and cost center is already defined. i.e. regardless of org name
1910 ** if we find an organization which already has the Company Cost Center
1911 ** classification and the org information matches on company, cost center
1912 ** and valueset details then link the org to the GL code combinations
1913 ** record.
1914 **
1915 ** This SQL needs rewriting to use org_information1 in the search and not
1916 ** the individual segments.
1917 */
1918 hr_utility.set_location(l_proc,66);
1919
1920 l_stmt := 'create table '||p_hr_sync_ccid||'(ccid, org_id) as
1921 (select sync.ccid,
1922 units.organization_id
1923 from '||p_hr_sync_temp ||' sync ,
1924 hr_all_organization_units units,
1925 hr_organization_information class,
1926 hr_organization_information cc
1927 where units.organization_id = class.organization_id
1928 and units.business_group_id = '||p_bgid||'
1929 and class.org_information_context=''CLASS''
1930 and class.org_information1=''CC''
1931 and class.organization_id = cc.organization_id
1932 and cc.org_information_context = ''Company Cost Center''
1933 and cc.org_information2 = to_char(sync.company_vs)
1934 and cc.org_information3 = sync.company
1935 and cc.org_information4 = to_char(sync.cc_vs)
1936 and cc.org_information5 = sync.cost_center)';
1937
1938 execute immediate l_stmt;
1939 hr_utility.set_location(l_proc,70);
1940
1941 begin
1942 execute immediate 'create unique index '||p_hr_sync_ccid||'_u1 on '||
1943 p_hr_sync_ccid||'(ccid)';
1944 exception
1945 when others then
1946 if sqlcode = -1452 then
1947 /* cannot CREATE UNIQUE INDEX; duplicate keys found */
1948 writelog(fnd_message.get_string('PER','HR_289492_DUP_CCIDS'),'E');
1949 end if;
1950
1951 end;
1952 hr_utility.set_location(l_proc,80);
1953 -- commit;
1954
1955 -- Now analyze table for avoiding view sort
1956
1957 l_temp_table := substr(p_hr_sync_temp, instr(p_hr_sync_temp, '.') + 1,
1958 length(p_hr_sync_temp)) ;
1959
1960 fnd_stats.gather_table_stats (errbuf,l_retcode, p_schema, l_temp_table, 10, 1);
1961 hr_utility.set_location(l_proc,90);
1962
1963 l_temp_table := substr(p_hr_sync_ccid, instr(p_hr_sync_ccid, '.') + 1,
1964 length(p_hr_sync_ccid)) ;
1965 fnd_stats.gather_table_stats (errbuf,l_retcode, p_schema, l_temp_table,10, 1);
1966 end if;
1967
1968 hr_utility.set_location('Leaving : '||l_proc,100);
1969
1970 return l_retcode;
1971 end;
1972
1973
1974 /*---------------------------------------------------------------------
1975
1976 This operates only on org_ids that are null in the gl_code_combinations table
1977 For those that are null, first check if there is an Org with Company Cost Center
1978 classification with the same company and cost center attributes.
1979 If so, do nothing.
1980 If this org does not exist then check for an organization with the same name
1981 derived using the name format and the current values. If it exists and it is
1982 not already classified with the Company Cost Center classification then add
1983 the classification with the current information. If the classification does
1984 exist then report this to the log file. (it is unlikely that we will find an
1985 org which matches using the derived org name but which has been linked to a
1986 different company cost center via the org info.
1987 If we have no match on classification information or org name then create an
1988 Org, add the HR and CC classifications.
1989 For the last two cases update the GCC table with this org_id.
1990
1991 ---------------------------------------------------------------------*/
1992
1993 function synchronizeMode(p_mode in varchar2,
1994 p_hr_sync_temp in varchar2,
1995 p_hr_sync_ccid in varchar2,
1996 p_start_date in date,
1997 p_bgid in number,
1998 p_schema in varchar2,
1999 p_coa in number)
2000 return NUMBER is
2001
2002 l_proc VARCHAR2(50) := g_package||'.synchronizeMode';
2003
2004 type curType is ref cursor;
2005 c_list curType;
2006 c_temp curType;
2007
2008 l_schema varchar2(30) := null;
2009
2010 l_chart_of_accounts_id NUMBER(15) := 0;
2011 l_company varchar2(240);
2012 l_cost_center varchar2(240);
2013 l_company_vs number(15) := -1;
2014 l_cc_vs number(15) := -1;
2015
2016 l_bg_id NUMBER(15) := 0;
2017 l_org_id NUMBER(15) := 0;
2018 l_ver NUMBER(15) := 0;
2019 l_org_inf_id NUMBER(15) := 0;
2020 l_stmt VARCHAR2(3000) := null;
2021
2022 l_temp_comp varchar2(240) := null;
2023 l_temp_cc varchar2(240) := null;
2024 l_temp_compvs NUMBER(15) := 0;
2025 l_temp_ccvs NUMBER(15) := 0;
2026
2027 l_org_profile VARCHAR2(10) := null;
2028 l_retcode number := 0;
2029 l_rowcount number;
2030
2031 l_last_update_login number := 0;
2032 l_message varchar2(1000);
2033
2034 BEGIN
2035 hr_utility.set_location('Entering : '||l_proc,10);
2036 /*
2037 ** Create and populate temporary tables which
2038 ** contain the company cost centers to be processed and the GL CCID's
2039 ** which already have a matching org(classified as company cost center with
2040 ** the company and cost center data) already defined.
2041 */
2042 l_retcode := createSyncTempTable(
2043 p_hr_sync_temp => p_hr_sync_temp,
2044 p_hr_sync_ccid => p_hr_sync_ccid,
2045 p_bgid => p_bgid,
2046 p_schema => p_schema,
2047 p_coa => p_coa); -- fix for bug 2875915
2048
2049 dumpccidtable(p_hr_sync_ccid);
2050 dumptemptable(p_hr_sync_temp);
2051 writelog('Created '||p_hr_sync_temp||' table ','D');
2052 writelog('Created '||p_hr_sync_ccid||' table ','D');
2053
2054 /*
2055 ** Update the GL code combinations table and set the org ID FK
2056 ** to the corresponding company cost center org.
2057 */
2058 l_stmt := 'update /*+ORDERED USE_NL(gcc)*/ gl_code_combinations gcc
2059 set (company_cost_center_org_id,
2060 last_update_date,
2061 last_updated_by ) =
2062 (select sync.org_id,
2063 sysdate, '||
2064 l_last_update_login||
2065 ' from '||p_hr_sync_ccid ||' sync
2066 where gcc.code_combination_id = sync.ccid)
2067 where gcc.company_cost_center_org_id is null
2068 and gcc.code_combination_id in
2069 (select ccid from '||p_hr_sync_ccid ||')';
2070
2071 execute immediate l_stmt;
2072
2073 writelog('Updated '||sql%rowcount||' rows in GL_CODE_COMBINATIONS','D');
2074 hr_utility.set_message(800,'HR_289179_COMB_ORG_ALRDY');
2075 fnd_message.set_token('ROWCOUNT',sql%rowcount);
2076 l_message := fnd_message.get();
2077 /*
2078 ** ...now pass the string to writelog.
2079 */
2080 writelog(l_message,'I');
2081
2082 /*
2083 ** This marks the end of part 1 of the sync process. We have found all the
2084 ** GL code comb records which map to existing organizations based on
2085 ** cost center org information. We now start part 2 of the process which
2086 ** will deal with those code combinations which do not map to any existing
2087 ** organization.
2088 */
2089
2090 /*
2091 ** Work out if we are just syncing with existing orgs based on current
2092 ** classifications.
2093 */
2094 l_org_profile := fnd_profile.value('HR_GENERATE_GL_OPTIONS');
2095 hr_utility.set_location('generate GL options '||l_org_profile,20);
2096 if (l_org_profile = 'S') then
2097 hr_utility.set_location(l_proc,25);
2098 writelog(fnd_message.get_string('PER','HR_289509_NOT_GEN_GL_ORGS'),'W');
2099 return 1;
2100 end if;
2101
2102 /*
2103 ** Since we have just set the org ID on GL_CODE_COMBINATIONS for those
2104 ** records which have a matching cost center Org already defined
2105 ** we can remove the records from our other temp table for CCID's which
2106 ** now have a value for the org ID.
2107 */
2108 hr_utility.set_location(l_proc,30);
2109 execute immediate 'delete from '||p_hr_sync_temp ||'
2110 where ccid in
2111 (select code_combination_id
2112 from gl_code_combinations
2113 where company_cost_center_org_id is not null
2114 and last_update_date >= :1)'
2115 using p_start_date;
2116
2117 writelog('deleted '||sql%rowcount||' rows from '||p_hr_sync_temp||
2118 ' where org_id is not null','D');
2119 dumpccidtable(p_hr_sync_ccid);
2120 dumptemptable(p_hr_sync_temp);
2121
2122 open c_list for 'select count(*) from '||p_hr_sync_temp;
2123 fetch c_list into l_rowcount;
2124 close c_list;
2125 if l_rowcount > 0 then
2126 -- hr_utility.set_message(800,'HR_289180_COMB_TO_SYNC');
2127 -- fnd_message.set_token('ROWCOUNT',l_rowcount);
2128 -- l_message := fnd_message.get();
2129 /*
2130 ** ...now pass the string to writelog.
2131 */
2132 -- writelog(l_message,'I');
2133
2134 /*
2135 ** For the remaining records in the temp table create new Orgs, add the
2136 ** cost center classification and if required an HR Org classification.
2137 */
2138 open c_list for 'select distinct company,
2139 cost_center,
2140 company_vs,
2141 cc_vs
2142 from '||p_hr_sync_temp;
2143 loop
2144 fetch c_list into l_company,
2145 l_cost_center,
2146 l_temp_compvs,
2147 l_temp_ccvs;
2148 exit when c_list%NOTFOUND;
2149
2150
2151 l_retcode := create_org_and_classification(p_mode,
2152 p_bgid,
2153 l_company,
2154 l_cost_center,
2155 l_temp_compvs,
2156 l_temp_ccvs,
2157 null,
2158 l_org_id);
2159
2160 if (l_retcode = -1) then
2161 l_retcode := 1;
2162 writelog('Problems with Create Org and Classification.','D');
2163 else
2164
2165 /*
2166 ** Save this new Org ID in the temp table for later.
2167 */
2168 execute immediate 'update '||p_hr_sync_temp ||'
2169 set (org_id) = :1
2170 where company =:2 and cost_center=:3
2171 and company_vs = :4 and cc_vs = :5'
2172 using l_org_id, l_company, l_cost_center,
2173 l_temp_compvs, l_temp_ccvs;
2174
2175
2176 end if;
2177 end loop;
2178
2179 /*
2180 ** now update GL_CODE_COMBINATIONS and set the org ID FK.
2181 */
2182 l_stmt := 'update gl_code_combinations gcc
2183 set (company_cost_center_org_id,
2184 last_update_date,
2185 last_updated_by) =
2186 (select org_id,
2187 sysdate, '||
2188 fnd_global.user_id||
2189 ' from '||p_hr_sync_temp||' sync
2190 where sync.ccid = gcc.code_combination_id
2191 and sync.org_id <> -1
2192 and sync.org_id is not null)
2193 where gcc.code_combination_id in
2194 (select ccid
2195 from '||p_hr_sync_temp||'
2196 where org_id is not null
2197 and org_id <> -1)';
2198
2199 execute immediate l_stmt;
2200 writelog('Updated '||sql%rowcount||
2201 ' rows in GL Code combinations with newly created Org ids','D');
2202 if sql%rowcount = 0 then
2203 writelog(fnd_message.get_String('PER','HR_289180_SYNC_SUMMARY_ZERO'),'I');
2204 else
2205 hr_utility.set_message(800,'HR_289184_SYNC_SUMMARY');
2206 fnd_message.set_token('ROWCOUNT',sql%rowcount);
2207 l_message := fnd_message.get();
2208 writelog(l_message,'I');
2209 end if;
2210 end if;
2211
2212 return l_retcode;
2213 exception
2214 when g_no_cc_proc_exc then
2215 /* There are no rows in the temp table so catch the exception and return
2216 ** a warning status.
2217 */
2218 return 1;
2219 when others then
2220 raise;
2221 end;
2222
2223
2224 /* Called by the Incremental GL API */
2225
2226 Procedure create_org(p_ccid in NUMBER) IS
2227
2228 l_proc VARCHAR2(50) := g_package ||'.create_org';
2229
2230 l_request_id number := -1;
2231 l_application_short_name VARCHAR2(3) := 'PER';
2232 l_incremental_enabled VARCHAR2(10) := 'N';
2233 l_autoorgs_enabled varchar2(10) := 'N';
2234
2235 BEGIN
2236 hr_utility.set_location('Entering : '||l_proc,10);
2237 --
2238 -- >>> Code Change Start >>> (See change history for ver 115.46)
2239 --
2240 ----------------------------------------------------------------------
2241 -- This procedure is invoked as a code hook from GL when a new code
2242 -- combination is created.
2243 --
2244 -- Amended procedure to be a wrapper which invokes the new sync orgs
2245 -- code as follows -
2246 -- Package - HR_GL_SYNC_ORGS
2247 -- Procedure - SYNC_SINGLE_ORG()
2248 -- Source - hrglsync.pk[hb]
2249 --
2250 -- NOTE that this introduces a dependency on package HR_GL_SYNC_ORGS
2251 -- and hence files hrglsync.pkh and hrglsync.pkb
2252 ----------------------------------------------------------------------
2253 --
2254 -- l_incremental_enabled := fnd_profile.value('HR_SYNC_SINGLE_GL_ORG');
2255 -- l_autoorgs_enabled := fnd_profile.value('HR_GENERATE_GL_OPTIONS');
2256 --
2257 -- IF (l_autoorgs_enabled = 'S' or
2258 -- (l_autoorgs_enabled <> 'S' and
2259 -- l_incremental_enabled = 'N')) THEN
2260 -- return;
2261 -- END IF;
2262 --
2263 -- hr_utility.set_location(l_proc,20);
2264 --
2265 -- l_request_id := fnd_request.submit_request(
2266 -- l_application_short_name,
2267 -- 'HR_CCID_GL_COST_CENTERS',
2268 -- null,
2269 -- null,
2270 -- false,
2271 -- 'GL',
2272 -- -1,
2273 -- p_ccid,
2274 -- chr(0), NULL, NULL, NULL, NULL, NULL, NULL,
2275 -- NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ,
2276 -- NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ,
2277 -- NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ,
2278 -- NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ,
2279 -- NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ,
2280 -- NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ,
2281 -- NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ,
2282 -- NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ,
2283 -- NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
2284 --
2285 -- Invoke new sync orgs in single org mode.
2286 hr_gl_sync_orgs.sync_single_org(p_ccid);
2287 --
2288 -- <<< Code Change End <<<
2289 --
2290 hr_utility.set_location('Leaving : '||l_proc,10);
2291 end;
2292
2293 function incrementalOrgs(p_mode in varchar2,
2294 p_ccid in NUMBER)
2295 return NUMBER is
2296
2297 l_proc varchar2(50) := g_package||'.incrementalOrgs';
2298
2299 l_chart_of_accounts_id NUMBER(15) := 0;
2300 l_company_segment VARCHAR2(30);
2301 l_cc_segment VARCHAR2(30);
2302 l_company VARCHAR2(240);
2303 l_cost_center VARCHAR2(240);
2304 l_company_vs NUMBER := -1;
2305 l_cc_vs NUMBER := -1;
2306 l_retcode NUMBER := 0;
2307
2308 type curType is ref cursor;
2309 c_list curType;
2310
2311 l_org_id NUMBER := -1;
2312 l_bg_id NUMBER := -1;
2313
2314 BEGIN
2315 hr_utility.set_location('Entering : '||l_proc,10);
2316 writelog('Inside incrementalOrgs for p_ccid = '||p_ccid,'D');
2317
2318 select chart_of_accounts_id
2319 into l_chart_of_accounts_id
2320 from gl_code_combinations
2321 where code_combination_id = p_ccid;
2322
2323 l_retcode := getSegmentForQualifier(l_chart_of_accounts_id,
2324 'GL_BALANCING',
2325 l_company_segment);
2326 l_retcode := getSegmentForQualifier(l_chart_of_accounts_id,
2327 'FA_COST_CTR',
2328 l_cc_segment);
2329 l_retcode := getValueSetForSegment(l_chart_of_accounts_id,
2330 l_company_segment,
2331 l_company_vs);
2332 l_retcode := getValueSetForSegment(l_chart_of_accounts_id,
2333 l_cc_segment,
2334 l_cc_vs);
2335
2336 if (l_cc_vs = -1 OR
2337 l_company_vs = -1 OR
2338 l_cc_segment is NULL OR
2339 l_company_segment is NULL) then
2340 l_retcode := reportMissingFlex(
2341 p_chart_of_accounts_id => l_chart_of_accounts_id,
2342 p_company_segment => l_company_segment,
2343 p_cc_segment => l_cc_segment,
2344 p_company_vs => l_company_vs,
2345 p_cc_vs => l_cc_vs);
2346
2347 l_retcode := 2;
2348 return l_retcode;
2349 end if;
2350 /*
2351 ** Get the actual company and cost center segment values.
2352 */
2353 open c_list for 'select '||l_company_segment||', '||
2354 l_cc_segment ||'
2355 from gl_code_combinations
2356 where code_combination_id =:1'
2357 using p_ccid;
2358 fetch c_list into l_company, l_cost_center ;
2359 close c_list;
2360 hr_utility.set_location(l_proc,30);
2361
2362 -- Now Check if an Org already exists with a Cost Center Classification
2363 -- for this Company/Cost Center
2364
2365 writelog('Checking to see if an Org already exists for the combination' ,'D');
2366
2367 --
2368 -- This SQL needs to be tuned to use org_information1 rather than the
2369 -- individual segment values.
2370 --
2371 open c_list for 'select units.organization_id
2372 from hr_all_organization_units units,
2373 hr_organization_information class,
2374 hr_organization_information cc
2375 where units.organization_id = class.organization_id
2376 and class.org_information_context=''CLASS''
2377 and class.org_information1=''CC''
2378 and class.organization_id = cc.organization_id
2379 and cc.org_information_context = ''Company Cost Center''
2380 and cc.org_information2 = :1
2381 and cc.org_information4 = :2
2382 and cc.org_information3 = :2
2383 and cc.org_information5 = :4 '
2384 using to_char(l_company_vs), to_char(l_cc_vs),
2385 l_company, l_cost_center;
2386 hr_utility.set_location(l_proc,31);
2387 fetch c_list into l_org_id;
2388 hr_utility.set_location(l_proc,32);
2389 close c_list;
2390 hr_utility.set_location(l_proc,35);
2391 if (l_org_id = -1) then -- create new org
2392 hr_utility.set_location(l_proc,40);
2393 writelog('Org ID does not already exist ','D');
2394
2395 l_bg_id := getBGID(l_company, l_company_vs);
2396
2397 if (l_bg_id <> -1) then
2398 l_retcode := create_org_and_classification(p_mode,
2399 l_bg_id,
2400 l_company,
2401 l_cost_center,
2402 l_company_vs,
2403 l_cc_vs,
2404 p_ccid,
2405 l_org_id);
2406 else
2407 /*
2408 ** This error condition is identical to one inside getBGID
2409 ** which results in 289601 message being raised. 289493 message
2410 ** has beenremovd from seed115 but leaving this code in place
2411 ** just in case the 289601 error is not detected and we exit
2412 ** getBGID with l_bg_id = -1.
2413 */
2414 hr_utility.set_message(801,'HR_289493_NO_COMPANY_ORG');
2415 hr_utility.raise_error;
2416 end if;
2417 end if;
2418
2419 if l_org_id <> -1 then
2420 hr_utility.set_location(l_proc,50);
2421 execute immediate 'update gl_code_combinations
2422 set company_cost_center_org_id=:1,
2423 last_update_date = sysdate,
2424 last_updated_by = :2
2425 where code_combination_id =:3'
2426 using l_org_id, fnd_global.user_id, p_ccid;
2427
2428 writelog('Updated GL_CODE_COMBINATIONS. Set Company_cost_center_org_id = '||
2429 l_org_id ||' where code_Combination_id = '||p_ccid,'D');
2430 end if;
2431
2432 hr_utility.set_location('Leaving : '||l_proc,60);
2433 return l_retcode;
2434 end;
2435
2436 Procedure synch_orgs(errbuf in out nocopy VARCHAR2
2437 ,retcode in out nocopy NUMBER
2438 ,p_mode in VARCHAR2
2439 ,p_business_group_id in NUMBER default null
2440 ,p_ccid in NUMBER default null
2441 ,p_coa in NUMBER default null) is
2442
2443 l_dummy number;
2444 l_proc VARCHAR2(50) := 'synch_orgs';
2445 l_column_name VARCHAR2(40) := null;
2446 l_dir VARCHAR2(300) := null;
2447 l_schema VARCHAR2(10) := null;
2448 l_hr_cc_reporting_temp VARCHAR2(50) := 'hr_cc_reporting_temp';
2449 l_hr_cc_reporting_comp VARCHAR2(50) := 'hr_cc_reporting_comp';
2450 l_hr_sync_temp VARCHAR2(50) := 'hr_cc_temp';
2451 l_hr_sync_ccid VARCHAR2(50) := 'hr_sync_gl_ccid';
2452 l_retcode NUMBER := 0;
2453 l_application_short_name VARCHAR2(50);
2454 l_start_date DATE;
2455
2456 cursor c_derive_name_length is
2457 select 1
2458 from user_triggers
2459 where trigger_name='HR_ALL_ORGANIZATION_UNITS_UTF8';
2460
2461 BEGIN
2462 hr_utility.set_location('Entering : '||l_proc,10);
2463 /*
2464 ** Set the Global holding the max length of an org name based
2465 ** on the existence of the UTF8 trigger. If it's there make the
2466 ** length 60, if it's not make it 240.
2467 */
2468 open c_derive_name_length;
2469 fetch c_derive_name_length into l_dummy;
2470 if c_derive_name_length%FOUND then
2471 hr_utility.set_location(l_proc,14);
2472 g_org_name_length := 60;
2473 else
2474 hr_utility.set_location(l_proc,18);
2475 g_org_name_length := 240;
2476 end if;
2477 close c_derive_name_length;
2478
2479 l_start_date := sysdate;
2480 l_application_short_name := 'PER';
2481 l_schema := getProductSchema(l_application_short_name);
2482 l_hr_cc_reporting_temp := l_schema||'.'|| l_hr_cc_reporting_temp;
2483 l_hr_cc_reporting_comp := l_schema||'.'||l_hr_cc_reporting_comp;
2484 l_hr_sync_temp := l_schema||'.'|| l_hr_sync_temp;
2485 l_hr_sync_ccid := l_schema||'.'|| l_hr_sync_ccid;
2486
2487 if (p_mode = 'GL') then
2488 hr_utility.set_location(l_proc,20);
2489 if (p_ccid is null) then
2490 hr_utility.set_message(801,'HR_289494_NO_CCID');
2491 hr_utility.raise_error;
2492 retcode := 2;
2493 return;
2494 end if;
2495 fnd_file.put_names(p_mode||p_ccid||'.log'
2496 ,p_mode||p_ccid||'.out'
2497 ,l_dir);
2498 hr_utility.set_location(l_proc,30);
2499 else
2500 hr_utility.set_location(l_proc,40);
2501 if (p_business_group_id IS NULL) then
2502 writelog(fnd_message.get_string('PER','HR_289495_NO_BG'),'E');
2503 end if;
2504
2505 fnd_file.put_names(p_mode||p_business_group_id||'.log'
2506 ,p_mode||p_business_group_id||'.out'
2507 ,l_dir);
2508 hr_utility.set_location(l_proc,50);
2509
2510 l_hr_cc_reporting_temp := l_hr_cc_reporting_temp||'_'||p_business_group_id;
2511 l_hr_cc_reporting_comp := l_hr_cc_reporting_comp||'_'||p_business_group_id;
2512 l_hr_sync_temp := l_hr_sync_temp||'_'||p_business_group_id;
2513 l_hr_sync_ccid := l_hr_sync_ccid||'_'||p_business_group_id;
2514 end if;
2515
2516 writelog('Beginning of Synchronize GL Cost Center program in '||p_mode ||' mode.','D');
2517 writelog('Start time is : '|| to_char(sysdate, 'dd-mm-yy hh24:mi:ss'),'D');
2518 writelog('p_mode is : '||p_mode,'D');
2519 writelog('Business Group Id is : '||p_business_group_id,'D');
2520 writelog('CCID is : '|| p_ccid,'D');
2521
2522 begin
2523 if (p_mode = 'REPORT') then
2524 hr_utility.set_location(l_proc,60);
2525 l_retcode := reportingMode(
2526 p_application_short_name => l_application_short_name,
2527 p_hr_cc_reporting_temp => l_hr_cc_reporting_temp,
2528 p_bgid => p_business_group_id,
2529 p_coa => p_coa);
2530
2531 /* Drop tables. FALSE means only drop them if not in DEBUG mode.
2532 */
2533 droptable(l_hr_cc_reporting_temp, FALSE);
2534 elsif (p_mode = 'SYNCHRONIZE') then
2535 hr_utility.set_location(l_proc,70);
2536 l_retcode := synchronizeMode(
2537 p_mode => p_mode,
2538 p_hr_sync_temp => l_hr_sync_temp,
2539 p_hr_sync_ccid => l_hr_sync_ccid,
2540 p_start_date => l_start_date,
2541 p_bgid => p_business_group_id,
2542 p_schema => l_schema,
2543 p_coa => p_coa);
2544 hr_utility.set_location(l_proc, 72);
2545 /* Drop tables. FALSE means only drop them if not in DEBUG mode.
2546 */
2547 droptable(l_hr_sync_temp, FALSE);
2548 droptable(l_hr_sync_ccid, FALSE);
2549 hr_utility.set_location(l_proc, 78);
2550 else
2551 hr_utility.set_location(l_proc,80);
2552 l_retcode := incrementalOrgs(p_mode,
2553 p_ccid);
2554 end if;
2555 exception
2556 when others then
2557 /*
2558 ** I've hit an error and raised an exception so set retcode to
2559 ** indicate a failure and then continue to the exit code.
2560 */
2561 hr_utility.set_location(l_proc, 85);
2562 hr_utility.set_location(SQLERRM,86);
2563 l_retcode := 2;
2564 end;
2565
2566 retcode := l_retcode;
2567 hr_utility.set_location(l_proc||' retcode '||to_char(retcode),90);
2568 if (retcode = 0) then
2569 writelog('Completed all operations for '||p_mode||' mode.','D');
2570 elsif retcode =1 then
2571 writelog('Completed with warnings.','D');
2572 elsif retcode = 2 then
2573 writelog('Program terminated with errors.','D');
2574 else
2575 writelog('Terminating with unknown code '||to_char(retcode),'D');
2576 end if;
2577
2578 writelog('End time is : '||to_char(sysdate, 'dd-mm-yy hh24:mi:ss'),'D');
2579
2580 Exception
2581 when others then
2582 raise;
2583
2584 end;
2585
2586 END hr_gl_cost_centers;
[Home] [Help]