4 FUNCTION GET_ERR_MSG (p_err_no VARCHAR2 DEFAULT NULL)
1 PACKAGE BODY ddr_ref_util_pkg AS
2 /* $Header: ddrurefb.pls 120.2.12010000.6 2010/04/27 00:39:31 gglover ship $ */
3
5 RETURN varchar2 as
6 l_err_msg VARCHAR2(40);
7 begin
8 if p_err_no is not null then
9 select err_name into l_err_msg from ddr_l_err where err_cd = p_err_no;
10 return (l_err_msg);
11 else
12 return(null);
13 end if;
14 end GET_ERR_MSG;
15
16 PROCEDURE TRUNC_INTERFACE (p_table_parameters IN VARCHAR2 DEFAULT NULL)
17 IS
18
19 n number;
20 text varchar2(500);
21 cut_text varchar2(100);
22 p_loading_mode varchar2(40);
23 Begin
24 select param_val into p_loading_mode from ddr_l_sys_param where param_cd = 'RUN_TYP_REF';
25 text:=p_table_parameters;
26 If
27 text is not null
28 Then
29 Loop
30 n:=0;
31 n:= Instr(text,',');
32 If
33 n<>0
34 Then
35 cut_text:= Substr(text,1,n-1);
36 Else
37 cut_text:= text;
38 End If;
39
40 If p_loading_mode = 'P' THEN
41 EXECUTE IMMEDIATE 'TRUNCATE TABLE '|| 'DDR.' || CUT_TEXT;
42 END IF;
43 Exit when n=0;
44 text:= Substr(text,n+1);
45 End Loop;
46 End If;
47 End TRUNC_INTERFACE;
48
49 FUNCTION CHECK_MFG_ITEM_HCHY (p_hchy_cd VARCHAR2, p_hchy_level VARCHAR2)
50 RETURN VARCHAR2
51 as
52 l_mfg_item_lvl NUMBER;
53 l_return VARCHAR2(30);
54
55 CURSOR cur_cmpny IS
56 SELECT 1
57 FROM DDR_R_MFG_ITEM_CMPNY
58 WHERE MFG_CMPNY_CD = p_hchy_cd;
59
60 CURSOR cur_div IS
61 SELECT 1
62 FROM DDR_R_MFG_ITEM_DIV
63 WHERE MFG_DIV_CD = p_hchy_cd
64 AND EFF_TO_DT IS NULL;
65
66 CURSOR cur_grp IS
67 SELECT 1
68 FROM DDR_R_MFG_ITEM_GRP
69 WHERE MFG_GRP_CD = p_hchy_cd
70 AND EFF_TO_DT IS NULL;
71
72 CURSOR cur_class IS
73 SELECT 1
74 FROM DDR_R_MFG_ITEM_CLASS
75 WHERE MFG_CLASS_CD = p_hchy_cd
76 AND EFF_TO_DT IS NULL;
77
78 CURSOR cur_sbc IS
79 SELECT 1
80 FROM DDR_R_MFG_ITEM_SBC
81 WHERE MFG_SBC_CD = p_hchy_cd
82 AND EFF_TO_DT IS NULL;
83
84 CURSOR cur_item IS
85 SELECT 1
86 FROM DDR_R_MFG_ITEM
87 WHERE MFG_ITEM_NBR = p_hchy_cd
88 AND EFF_TO_DT IS NULL;
89
90 CURSOR cur_sku IS
91 SELECT 1
92 FROM DDR_R_MFG_SKU_ITEM
93 WHERE MFG_SKU_ITEM_NBR = p_hchy_cd
94 AND EFF_TO_DT IS NULL;
95
96 Begin
97 IF p_hchy_level IS NULL OR p_hchy_cd IS NULL
98 THEN
99 l_return := 'DSR-1001';
100 RETURN (l_return);
101 END IF;
102
103 l_mfg_item_lvl := -1;
104 IF UPPER(p_hchy_level) IN ('COMPANY','DIVISION','GROUP','CLASS','SUBCLASS','ITEM','SKU','NA') THEN
105
106 IF p_hchy_level='COMPANY'
107 THEN
108 OPEN cur_cmpny;
109 FETCH cur_cmpny INTO l_mfg_item_lvl;
110 CLOSE cur_cmpny;
111 ELSIF p_hchy_level='DIVISION'
112 THEN
113 OPEN cur_div;
114 FETCH cur_div INTO l_mfg_item_lvl;
115 CLOSE cur_div;
116 ELSIF p_hchy_level='GROUP'
117 THEN
118 OPEN cur_grp;
119 FETCH cur_grp INTO l_mfg_item_lvl;
120 CLOSE cur_grp;
121 ELSIF p_hchy_level='CLASS'
122 THEN
123 OPEN cur_class;
124 FETCH cur_class INTO l_mfg_item_lvl;
125 CLOSE cur_class;
126 ELSIF p_hchy_level='SUBCLASS'
127 THEN
128 OPEN cur_sbc;
129 FETCH cur_sbc INTO l_mfg_item_lvl;
130 CLOSE cur_sbc;
131 ELSIF p_hchy_level='ITEM'
132 THEN
133 OPEN cur_item;
134 FETCH cur_item INTO l_mfg_item_lvl;
135 CLOSE cur_item;
136 ELSIF p_hchy_level='SKU'
137 THEN
138 OPEN cur_sku;
139 FETCH cur_sku INTO l_mfg_item_lvl;
140 CLOSE cur_sku;
141 ELSIF p_hchy_level='NA'
142 THEN
143 IF p_hchy_cd ='-1'
144 THEN
145 l_mfg_item_lvl := 1;
146 ELSE
147 l_mfg_item_lvl := -1;
148 END IF;
149 END IF;
150
151 IF l_mfg_item_lvl='-1'
152 THEN
153 l_return := 'DSR-1002';
154 END IF;
155
156 ELSE l_return := 'DSR-1002';
157 END IF;
158
159 RETURN(l_return);
160 End CHECK_MFG_ITEM_HCHY;
161
162 FUNCTION CHECK_ORG_HCHY (p_hchy_cd VARCHAR2, p_hchy_level VARCHAR2, p_org_cd
163 VARCHAR2)
164 RETURN VARCHAR2
165 as
166 l_org_lvl NUMBER;
167 l_return VARCHAR2(30);
168 l_rtl_org_cd NUMBER;
172 SELECT 1
169 l_valid_org_bu NUMBER;
170
171 CURSOR cur_org IS
173 FROM DDR_R_ORG
174 WHERE ORG_CD = p_org_cd;
175
176 CURSOR cur_chain IS
177 SELECT 1
178 FROM DDR_R_ORG_CHAIN
179 WHERE CHAIN_CD = p_hchy_cd
180 AND ORG_CD = p_org_cd
181 AND EFF_TO_DT IS NULL;
182
183 CURSOR cur_area IS
184 SELECT 1
185 FROM DDR_R_ORG_AREA
186 WHERE AREA_CD = p_hchy_cd
187 AND ORG_CD = p_org_cd
188 AND EFF_TO_DT IS NULL;
189
190 CURSOR cur_rgn IS
191 SELECT 1
192 FROM DDR_R_ORG_RGN
193 WHERE RGN_CD = p_hchy_cd
194 AND ORG_CD = p_org_cd
195 AND EFF_TO_DT IS NULL;
196
197 CURSOR cur_dstrct IS
198 SELECT 1
199 FROM DDR_R_ORG_DSTRCT
200 WHERE DSTRCT_CD = p_hchy_cd
201 AND ORG_CD = p_org_cd
202 AND EFF_TO_DT IS NULL;
203
204 CURSOR cur_bsns_unit IS
205 SELECT 1
206 FROM DDR_R_ORG_BSNS_UNIT
207 WHERE BSNS_UNIT_CD = p_hchy_cd
208 AND ORG_CD = p_org_cd
209 AND EFF_TO_DT IS NULL;
210 Begin
211 IF p_hchy_level IS NULL OR p_hchy_cd IS NULL OR p_org_cd IS NULL
212 THEN
213 l_return := 'DSR-1001';
214 RETURN l_return;
215 END IF;
216
217 l_org_lvl := -1;
218 IF UPPER(p_hchy_level) IN ('ORGANIZATION','CHAIN','AREA','REGION','DISTRICT','BU')
219 THEN
220
221 IF p_hchy_level='ORGANIZATION'
222 THEN
223 IF p_hchy_cd = p_org_cd
224 THEN
225 OPEN cur_org;
226 FETCH cur_org INTO l_org_lvl;
227 CLOSE cur_org;
228 ELSE
229 l_org_lvl := -1;
230 END IF;
231 ELSIF p_hchy_level='CHAIN'
232 THEN
233 OPEN cur_chain;
234 FETCH cur_chain INTO l_org_lvl;
235 CLOSE cur_chain;
236 ELSIF p_hchy_level='AREA'
237 THEN
238 OPEN cur_area;
239 FETCH cur_area INTO l_org_lvl;
240 CLOSE cur_area;
241 ELSIF p_hchy_level='REGION'
242 THEN
243 OPEN cur_rgn;
244 FETCH cur_rgn INTO l_org_lvl;
245 CLOSE cur_rgn;
246 ELSIF p_hchy_level='DISTRICT'
247 THEN
248 OPEN cur_dstrct;
249 FETCH cur_dstrct INTO l_org_lvl;
250 CLOSE cur_dstrct;
251 ELSIF p_hchy_level='BU'
252 THEN
253 OPEN cur_bsns_unit;
254 FETCH cur_bsns_unit INTO l_org_lvl;
255 CLOSE cur_bsns_unit;
256 END IF;
257
258 ELSIF p_hchy_level='NA'
259 THEN
260 IF p_hchy_cd ='-1' AND p_org_cd = '-1'
261 THEN
262 l_org_lvl := 1;
263 ELSE
264 l_org_lvl := -1;
265 END IF;
266
267 ELSE l_return := 'DSR-1002';
268 END IF;
269
270 IF l_org_lvl ='-1'
271 THEN
272 l_return := 'DSR-1006';
273 END IF;
274
275 RETURN(l_return);
276 End CHECK_ORG_HCHY;
277
278 FUNCTION CHECK_RTL_ITEM_HCHY (p_hchy_cd VARCHAR2, p_hchy_level VARCHAR2)
279 RETURN VARCHAR2
280 as
281 l_rtl_item_lvl NUMBER;
282 l_return VARCHAR2(30);
283
284 CURSOR cur_cmpny IS
285 SELECT 1
286 FROM DDR_R_RTL_ITEM_CMPNY
287 WHERE RTL_CMPNY_CD = p_hchy_cd;
288
289 CURSOR cur_div IS
290 SELECT 1
291 FROM DDR_R_RTL_ITEM_DIV
292 WHERE RTL_DIV_CD = p_hchy_cd
293 AND EFF_TO_DT IS NULL;
294
295 CURSOR cur_grp IS
296 SELECT 1
297 FROM DDR_R_RTL_ITEM_GRP
298 WHERE RTL_GRP_CD = p_hchy_cd
299 AND EFF_TO_DT IS NULL;
300
301 CURSOR cur_dept IS
302 SELECT 1
303 FROM DDR_R_RTL_ITEM_DEPT
304 WHERE RTL_DEPT_CD = p_hchy_cd
305 AND EFF_TO_DT IS NULL;
306
307 CURSOR cur_class IS
308 SELECT 1
309 FROM DDR_R_RTL_ITEM_CLASS
310 WHERE RTL_CLASS_CD = p_hchy_cd
311 AND EFF_TO_DT IS NULL;
312
313 CURSOR cur_sbc IS
314 SELECT 1
315 FROM DDR_R_RTL_ITEM_SBC
316 WHERE RTL_SBC_CD = p_hchy_cd
317 AND EFF_TO_DT IS NULL;
318
319 CURSOR cur_item IS
320 SELECT 1
321 FROM DDR_R_RTL_ITEM
322 WHERE RTL_ITEM_NBR = p_hchy_cd
323 AND EFF_TO_DT IS NULL;
324
325 CURSOR cur_sku IS
326 SELECT 1
327 FROM DDR_R_RTL_SKU_ITEM
328 WHERE RTL_SKU_ITEM_NBR = p_hchy_cd
329 AND EFF_TO_DT IS NULL;
330
331 Begin
332 IF p_hchy_level IS NULL OR p_hchy_cd IS NULL
333 THEN
334 l_return := 'DSR-1001';
335 RETURN (l_return);
336 END IF;
337
338 l_rtl_item_lvl := -1;
339 IF UPPER(p_hchy_level) IN ('COMPANY','DIVISION','GROUP','DEPARTMENT','CLASS','SUBCLASS','ITEM','SKU','NA') THEN
340
341 IF p_hchy_level='COMPANY'
342 THEN
343 OPEN cur_cmpny;
344 FETCH cur_cmpny INTO l_rtl_item_lvl;
345 CLOSE cur_cmpny;
346 ELSIF p_hchy_level='DIVISION'
347 THEN
348 OPEN cur_div;
349 FETCH cur_div INTO l_rtl_item_lvl;
350 CLOSE cur_div;
351 ELSIF p_hchy_level='GROUP'
352 THEN
353 OPEN cur_grp;
354 FETCH cur_grp INTO l_rtl_item_lvl;
355 CLOSE cur_grp;
359 FETCH cur_dept INTO l_rtl_item_lvl;
356 ELSIF p_hchy_level='DEPARTMENT'
357 THEN
358 OPEN cur_dept;
360 CLOSE cur_dept;
361 ELSIF p_hchy_level='CLASS'
362 THEN
363 OPEN cur_class;
364 FETCH cur_class INTO l_rtl_item_lvl;
365 CLOSE cur_class;
366 ELSIF p_hchy_level='SUBCLASS'
367 THEN
368 OPEN cur_sbc;
369 FETCH cur_sbc INTO l_rtl_item_lvl;
370 CLOSE cur_sbc;
371 ELSIF p_hchy_level='ITEM'
372 THEN
373 OPEN cur_item;
374 FETCH cur_item INTO l_rtl_item_lvl;
375 CLOSE cur_item;
376 ELSIF p_hchy_level='SKU'
377 THEN
378 OPEN cur_sku;
379 FETCH cur_sku INTO l_rtl_item_lvl;
380 CLOSE cur_sku;
381 ELSIF p_hchy_level='NA'
382 THEN
383 IF p_hchy_cd ='-1'
384 THEN
385 l_rtl_item_lvl := 1;
386 ELSE
387 l_rtl_item_lvl := -1;
388 END IF;
389 END IF;
390
391 IF l_rtl_item_lvl='-1'
392 THEN
393 l_return := 'DSR-1002';
394 END IF;
395
396 ELSE l_return := 'DSR-1002';
397 END IF;
398
399 RETURN(l_return);
400 End CHECK_RTL_ITEM_HCHY;
401
402 FUNCTION CHECK_TIME_HCHY (p_hchy_cd VARCHAR2, p_hchy_level VARCHAR2)
403 RETURN VARCHAR2
404 as
405 l_time_lvl NUMBER;
406 l_return VARCHAR2(30);
407
408 CURSOR cur_yr IS
409 SELECT 1
410 FROM DDR_R_BSNS_YR
411 WHERE YR_CD = p_hchy_cd;
412
413 CURSOR cur_qtr IS
414 SELECT 1
415 FROM DDR_R_BSNS_QTR
416 WHERE QTR_CD = p_hchy_cd;
417
418 CURSOR cur_mnth IS
419 SELECT 1
420 FROM DDR_R_BSNS_MNTH
421 WHERE MNTH_CD = p_hchy_cd;
422
423 CURSOR cur_wk IS
424 SELECT 1
425 FROM DDR_R_BSNS_WK
426 WHERE WK_CD = p_hchy_cd;
427
428 CURSOR cur_day IS
429 SELECT 1
430 FROM DDR_R_DAY
431 WHERE DAY_CD = p_hchy_cd;
432
433 Begin
434 IF p_hchy_level IS NULL OR p_hchy_cd IS NULL
435 THEN
436 l_return := 'DSR-1001';
437 RETURN (l_return);
438 END IF;
439
440 l_time_lvl := -1;
441 IF UPPER(p_hchy_level) IN ('YEAR','QUARTER','MONTH','WEEK','DAY','NA') THEN
442
443 IF p_hchy_level='YEAR'
444 THEN
445 OPEN cur_yr;
446 FETCH cur_yr INTO l_time_lvl;
447 CLOSE cur_yr;
448 ELSIF p_hchy_level='QUARTER'
449 THEN
450 OPEN cur_qtr;
451 FETCH cur_qtr INTO l_time_lvl;
452 CLOSE cur_qtr;
453 ELSIF p_hchy_level='MONTH'
454 THEN
455 OPEN cur_mnth;
456 FETCH cur_mnth INTO l_time_lvl;
457 CLOSE cur_mnth;
458 ELSIF p_hchy_level='WEEK'
459 THEN
460 OPEN cur_wk;
461 FETCH cur_wk INTO l_time_lvl;
462 CLOSE cur_wk;
463 ELSIF p_hchy_level='DAY'
464 THEN
465 OPEN cur_day;
466 FETCH cur_day INTO l_time_lvl;
467 CLOSE cur_day;
468 ELSIF p_hchy_level='NA'
469 THEN
470 IF p_hchy_cd ='-1'
471 THEN
472 l_time_lvl := 1;
473 ELSE
474 l_time_lvl := -1;
475 END IF;
476 END IF;
477
478 IF l_time_lvl ='-1'
479 THEN
480 l_return := 'DSR-1002';
481 END IF;
482
483 ELSE l_return := 'DSR-1002';
484 END IF;
485
486 RETURN(l_return);
487 End CHECK_TIME_HCHY;
488
489 FUNCTION GET_REF_MAP_RUN_ID(P_AUDIT_ID VARCHAR2) RETURN NUMBER IS
490
491 BEGIN
492 IF p_audit_id = c_audit_id THEN --{
493 RETURN c_map_id;
494 ELSE --}{
495 c_audit_id := p_audit_id;
496
497 select DDR_LOAD_SEQ.NEXTVAL
498 into c_map_id
499 from dual;
500
501 RETURN c_map_id;
502 END IF; --}
503
504 END GET_REF_MAP_RUN_ID;
505
506 PROCEDURE parse_pad_accounts(p_max_level IN NUMBER DEFAULT 10) AS
507 v_elems account_array; -- array indexed by level
508 v_elems_ID account_array; -- array indexed by level
509 v_counter NUMBER := 0;
510 v_curr_level NUMBER := 1;
511 v_prev_level NUMBER := 0;
512
513 -- We want to denormailze the account hierarchy but because of SCD2, there could be records
514 -- with same account but different from/to dates. To remove this problem we need to associate
515 -- by account id and parent account id instead of acocunt code.
516 -- However, account id is generated by the dimension object. Currently the dimension object is
517 -- set as one-level and no hierarchy. Therefore there is no way to produce parent account id
518 -- there. As a result we have to use PL/SQL to add the parent account id after the SCD2 data is generated.
519 --
520 -- generate parent account id from SCD2 data, assuming new open records have higher
521 -- ID than older or closed ones. Couple of note points:
522 -- 1. Some duplicate records are present when looking up manager id. These will have mismatched
523 -- from/to dates and are filtered out by date order by emp_id in the first WHERE clause
524 -- 2. The top accounts are missed out in the first query. They are added in by the second query.
525 CURSOR account_cursor IS
526 SELECT ORG_HCHY_ID, HRCHY_CD, CHILD_ID BSNS_ENT_ID, LEVEL, CHILD_CD BSNS_ENT_CD,
527 SRC_SYS_DT, SRC_SYS_IDNT, EFF_FROM_DT, EFF_TO_DT
528 FROM
529 (
530 SELECT ORG_HCHY_ID, HRCHY_CD, PARENT_ID, PARENT_CD, CHILD_ID, CHILD_CD,
531 SRC_SYS_DT, SRC_SYS_IDNT, EFF_FROM_DT, EFF_TO_DT
532 FROM
533 (
534 SELECT t1.ORG_HCHY_ID ORG_HCHY_ID, t1.HRCHY_CD HRCHY_CD,
535 t1.ORG_BSNS_ENT_ID PARENT_ID, t1.BSNS_ENT_CD PARENT_CD,
539 FROM DDR_R_ORG_BSNS_ENT t1 INNER JOIN DDR_R_ORG_BSNS_ENT t2
536 t2.ORG_BSNS_ENT_ID CHILD_ID, t2.BSNS_ENT_CD CHILD_CD,
537 t2.SRC_SYS_DT SRC_SYS_DT, t2.SRC_SYS_IDNT SRC_SYS_IDNT,
538 t1.EFF_FROM_DT EFF_FROM_DT, t1.EFF_TO_DT, t2.EFF_FROM_DT FROM_DT_2, t2.EFF_TO_DT TO_DT_2
540 ON (t1.BSNS_ENT_CD = t2.BSNS_ENT_PRNT_CD AND t1.ORG_HCHY_ID =
541 t2.ORG_HCHY_ID)
542 )
543 WHERE NOT ((TO_DT_2 is not null AND TO_DT_2 < EFF_FROM_DT) OR (EFF_TO_DT is not null AND FROM_DT_2 > EFF_TO_DT))
544 UNION
545 (
546 SELECT
547 ORG_HCHY_ID, HRCHY_CD,
548 CASE WHEN BSNS_ENT_PRNT_CD is null THEN null
549 ELSE ORG_BSNS_ENT_ID
550 END PARENT_ID,
551 BSNS_ENT_PRNT_CD PARENT_CD,
552 ORG_BSNS_ENT_ID CHILD_ID,
553 BSNS_ENT_CD CHILD_CD,
554 SRC_SYS_DT, SRC_SYS_IDNT,
555 EFF_FROM_DT, EFF_TO_DT
556 FROM DDR_R_ORG_BSNS_ENT
557 WHERE BSNS_ENT_PRNT_CD is null
558 )
559 )
560 START WITH PARENT_CD is null
561 CONNECT BY PARENT_ID = PRIOR CHILD_ID;
562
563
564 -- store previous record columns before level change
565 v_hchy_id NUMBER;
566 v_hchy_cd VARCHAR2(30);
567 v_acct_id NUMBER;
568 v_acct_cd VARCHAR2(30);
569 v_sys_id VARCHAR2(40);
570 v_sys_dt DATE;
571
572 v_sql_stmt VARCHAR2(2048) := '';
573 v_sql_hdr1 VARCHAR2(250) := 'INSERT INTO DDR_R_AOH_LVL_DTL (ORG_HCHY_ID,HRCHY_CD, BSNS_ENT_ID,BSNS_ENT_CD,BSNS_ENT_LEVEL,PRNT1_BSNS_ENT_CD,PRNT1_BSNS_ENT_ID,PRNT2_BSNS_ENT_CD, PRNT2_BSNS_ENT_ID,PRNT3_BSNS_ENT_CD,PRNT3_BSNS_ENT_ID,';
574 v_sql_hdr2 VARCHAR2(250) := 'PRNT4_BSNS_ENT_CD,PRNT4_BSNS_ENT_ID, PRNT5_BSNS_ENT_CD,PRNT5_BSNS_ENT_ID,PRNT6_BSNS_ENT_CD,PRNT6_BSNS_ENT_ID,PRNT7_BSNS_ENT_CD, PRNT7_BSNS_ENT_ID,PRNT8_BSNS_ENT_CD,PRNT8_BSNS_ENT_ID,PRNT9_BSNS_ENT_CD,PRNT9_BSNS_ENT_ID,';
575 v_sql_hdr3 VARCHAR2(250) := 'SRC_SYS_IDNT,SRC_SYS_DT,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,CRTD_BY_DSR, LAST_UPDT_BY_DSR,LAST_UPDATE_LOGIN) VALUES(';
576
577 v_hchy_cols VARCHAR2(100) :='';
578 v_acct_cols VARCHAR2(100) := '';
579 v_prnt_cols VARCHAR2(1000) := '';
580 v_sys_cols VARCHAR2(100) := '';
581
582 v_commit_stmt VARCHAR2(10) := 'commit';
583
584 BEGIN
585 -- clean up the previous records
586 EXECUTE IMMEDIATE 'truncate table DDR.DDR_R_AOH_LVL_DTL drop storage';
587
588 -- The logic detects individual branch in the tree:
589 -- 1. Read a row, if it is second row or more, we initialize previous level
590 -- 2. store the account level and code from current record
591 -- 3. if level has decreased, we write all previously accumulated accounts into one record
592 -- 4. clean up and start from current record to start traversing a new branch.
593
594 FOR acct_rec IN account_cursor
595 LOOP
596 v_counter := v_counter + 1;
597
598 -- set previous level after one record is read
599 IF (v_counter > 1)
600 THEN
601 v_prev_level := v_curr_level;
602 END IF;
603
604 v_curr_level := acct_rec.LEVEL;
605 v_elems(v_counter) := acct_rec.BSNS_ENT_CD;
606 v_elems_id(v_counter) := acct_rec.BSNS_ENT_ID;
607
608 -- Adjust counter and account array pointer when level is not in order
609 IF ( v_prev_level > 0 AND v_curr_level <= v_prev_level )
610 THEN
611 -- write to table in Elems from 1..Counter
612 v_hchy_cols := v_hchy_id || ',' || '''' || v_hchy_cd || '''' || ',';
613 v_acct_cols := v_acct_id || ',' || '''' || v_acct_cd || '''' || ',' || v_prev_level || ',';
614 FOR p in 1..p_max_level-1
615 LOOP
616 IF (p < v_counter)
617 THEN
618 v_prnt_cols := v_prnt_cols || '''' || v_elems(p) || '''' || ',' || v_elems_id(p) || ',';
619 ELSE
620 v_prnt_Cols := v_prnt_Cols || '''' || v_elems(v_counter-1) || '''' || ',' || v_elems_id(v_counter-1) || ','; -- padding
621 END IF;
622 END LOOP;
623
624 v_sys_cols := '''' || v_sys_id || '''' || ',' || 'TO_DATE(' || '''' || v_sys_dt || '''' || '),' || 'SYSDATE' || ', -1,' || 'SYSDATE' || ',-1,' || '''' || USER || '''' || ',' || '''' || USER || '''' || ',-1';
625 v_sql_stmt := v_sql_hdr1 || v_sql_hdr2 || v_sql_hdr3 || v_hchy_cols || v_acct_cols || v_prnt_cols || v_sys_cols || ')';
626 --dbms_output.put_line(v_sql_stmt);
627 EXECUTE IMMEDIATE v_sql_stmt;
628
629 -- clean up the statements for next time
630 v_hchy_cols := '';
631 v_acct_cols := '';
632 v_prnt_cols := '';
633 v_sys_cols := '';
634 END IF;
635
636 -- reset account array to previous level
637 FOR i IN v_curr_level..v_counter
638 LOOP
639 v_elems(i) := null;
640 v_elems_id(i) := null;
641 END LOOP;
642
643 -- retreat back to the levels corresponding to the record we read
644 v_counter := v_curr_level;
645 v_prev_level := v_curr_level - 1;
646 v_elems(v_counter) := acct_rec.BSNS_ENT_CD;
647 v_elems_id(v_counter) := acct_rec.BSNS_ENT_ID;
648
649 -- store account attrs used to write to table when level changes as at that time
650 -- cursor already points to the next record.
651 v_hchy_id := acct_rec.ORG_HCHY_ID;
652 v_hchy_cd := acct_rec.HRCHY_CD;
653 v_acct_cd := acct_rec.BSNS_ENT_CD;
654 v_acct_id := acct_rec.BSNS_ENT_ID;
655 v_sys_id := acct_rec.SRC_SYS_IDNT;
656 v_sys_dt := acct_rec.SRC_SYS_DT;
657 END LOOP;
658
662 v_hchy_cols := v_hchy_id || ',' || '''' || v_hchy_cd || '''' || ',';
659 -- Last record in cursor is also a lowest level account and ened to be exported
660 IF v_counter > 0
661 THEN
663 v_acct_cols := v_acct_id || ',' || '''' || v_acct_cd || '''' || ',' || v_curr_level || ',';
664 FOR p in 1..p_max_level-1
665 LOOP
666 IF (p <= v_counter) -- note the difference here on counter from with above
667 THEN
668 v_prnt_cols := v_prnt_cols || '''' || v_elems(p) || '''' || ',' || v_elems_id(p) || ',';
669 ELSE
670 v_prnt_cols := v_prnt_cols || '''' || v_acct_cd || '''' || ',' || v_acct_id || ','; -- padding
671 END IF;
672 END LOOP;
673
674 v_sys_cols := '''' || v_sys_id || '''' || ',' || 'TO_DATE(' || '''' || v_sys_dt || '''' || '),' || 'SYSDATE' || ', -1,' || 'SYSDATE' || ',-1,' || '''' || USER || '''' || ',' || '''' || USER || '''' || ',-1';
675 v_sql_stmt := v_sql_hdr1 || v_sql_hdr2 || v_sql_hdr3 || v_hchy_cols || v_acct_cols || v_prnt_cols || v_sys_cols || ')';
676 --dbms_output.put_line(v_sql_stmt);
677 EXECUTE IMMEDIATE v_sql_stmt;
678 END IF;
679 EXECUTE IMMEDIATE v_commit_stmt;
680
681 EXCEPTION
682 WHEN others THEN
683 --dbms_output.put_line('Error detected in parse_pad_accounts: ' || SQLERRM);
684 ROLLBACK;
685
686 END parse_pad_accounts;
687
688 END ddr_ref_util_pkg;
[Home] [Help]