1 PACKAGE BODY DDR_ODI AS
2 /* $Header: ddrodib.pls 120.9.12020000.3 2012/08/04 02:32:16 gglover noship $ */
3
4 FUNCTION get_user RETURN VARCHAR2
5 IS
6 v_user VARCHAR2(100);
7 v_sql_user VARCHAR2(1000);
8
9 BEGIN
10 v_sql_user := 'select user from dual';
11 EXECUTE IMMEDIATE v_sql_user
12 INTO v_user;
13
14
15 IF v_user = 'APPS' THEN --{
16 /*If APPS user then EBS install and tables are in DDR schema */
17 v_user := 'DDR';
18 END IF; --}
19
20
21 RETURN v_user;
22
23 END get_user;
24
25
26 FUNCTION SPLIT (p_in_string VARCHAR2, p_delim VARCHAR2) RETURN code_type
27 IS
28
29 i NUMBER :=0;
30 v_pos NUMBER :=0;
31 v_str VARCHAR2(1000) := p_in_string;
32 v_strings code_type;
33
34 BEGIN
35 -- determine first chuck of string
36 v_pos := instr(v_str,p_delim,1,1);
37 -- if there is no delimiter in string
38 IF v_pos = 0 THEN--{
39 v_strings(0) := v_str;
40 ELSE
41 -- while there are chunks left, loop
42 WHILE ( v_pos <> 0) LOOP--{
43 -- create array element for chuck of string
44 v_strings(i) := substr(v_str,1,v_pos-1);
45 -- remove found chunk from original string
46 v_str := substr(v_str,v_pos+1,length(v_str));
47 -- determine next chunk
48 v_pos := instr(v_str,p_delim,1,1);
49 -- no last chunk, add to array
50 IF v_pos = 0 THEN--{
51 v_strings(i+1) := v_str;
52 END IF;--}
53 -- increment counter
54 i := i + 1;
55 END LOOP;--}
56 END IF;--}
57
58 -- return array
59 RETURN v_strings;
60
61 END SPLIT;
62
63
64 FUNCTION REMOVE_QUOTED_STRING (p_in_string VARCHAR2) RETURN VARCHAR2 IS
65
66 v_pos1 NUMBER := 0;
67 v_pos2 NUMBER := 0;
68 v_str VARCHAR2(1000) := p_in_string;
69 v_final_string VARCHAR2(1000);
70
71 BEGIN
72 -- determine first chunk of string
73 v_pos1 := instr(v_str,'''',1,1);
74 -- if there are no quotes, return original string
75 IF v_pos1 = 0 THEN--{
76 v_final_string := v_str;
77 -- if quote is at begining of the string find the 2nd
78 ELSIF v_pos1 = 1 THEN
79 -- find the 2nd quote position
80 v_pos2 := instr(v_str,'''',1,2);
81 -- remove the quoted chunk at the start
82 v_final_string := substr(v_str,v_pos2+2);
83 ELSE
84 -- remove the quoted chunk at the end
85 v_final_string := substr(v_str,1,v_pos1-2);
86 END iF;--}
87
88 -- return string
89 RETURN v_final_string;
90
91 END REMOVE_QUOTED_STRING;
92
93
94 PROCEDURE DUP_CHECK(p_src_tbl_name IN VARCHAR2, p_err_tbl_name IN VARCHAR2, p_key_cols IN VARCHAR2, p_src_typ IN VARCHAR2, p_load_id IN NUMBER) IS
95
96 v_user VARCHAR2(100);
97
98 CURSOR src_list IS
99 SELECT column_name
100 FROM all_tab_columns
101 WHERE owner = v_user
102 AND table_name = p_src_tbl_name
103 ORDER BY column_id;
104
105 v_src_col_list code_type;
106 v_count NUMBER:=0;
107 v_err_select VARCHAR2(10000);
108 v_src_select VARCHAR2(10000);
109
110 BEGIN
111 v_user := get_user;
112
113 FOR col IN src_list LOOP
114 IF p_src_tbl_name = 'DDR_I_RTL_SL_RTN_ITEM' OR p_src_tbl_name = 'DDR_I_SLS_FRCST_ITEM' THEN --{
115 IF col.column_name NOT LIKE '%_AMOUNT' AND col.column_name <> 'ALLOC_PARENT_IDNT' THEN --{
116 v_src_col_list(v_count):=col.column_name;
117 v_src_select := v_src_select || ',' || v_src_col_list(v_count);
118 v_count := v_count+1;
119 END IF;--}
120 ELSE
121 v_src_col_list(v_count):=col.column_name;
122 v_src_select := v_src_select || ',' || v_src_col_list(v_count);
123 v_count := v_count+1;
124 END IF;--}
125 END LOOP;
126 v_src_select := LTRIM(v_src_select,',');
127
128 IF p_src_typ = 'R' THEN
129 v_err_select:=v_src_select || ',LOAD_ID,ERR_REASON,ACTION_FLAG';
130 v_src_select:=v_src_select || ',' || p_load_id || ',''Duplicate Record;'',''N''';
131 ELSIF p_src_typ = 'F' THEN
132 v_err_select:=v_src_select || ',LOAD_ID,ERR_REASON,ACTION_FLAG,SRC_IDNT_FLAG';
133 v_src_select:=v_src_select || ',' || p_load_id || ',''Duplicate Record;'',''N'',''I''';
134 END IF;
135
136
137 /* Creating the INSERT SQL statement */
138 EXECUTE IMMEDIATE 'INSERT INTO ' || p_err_tbl_name || ' ( ' || v_err_select || ') SELECT ' || v_src_select || ' FROM ' || p_src_tbl_name || ' WHERE ( ' ||
139 p_key_cols || ' ) IN ( SELECT ' || p_key_cols || ' FROM ' || p_src_tbl_name || ' GROUP BY ' || p_key_cols || ' HAVING COUNT(*) > 1 )';
140 COMMIT;
141
142 /* Creating the DELETE SQL Statement */
143 EXECUTE IMMEDIATE 'DELETE FROM '||p_src_tbl_name||' WHERE ('||p_key_cols||') IN (SELECT '||p_key_cols||' FROM '||p_src_tbl_name||' GROUP BY '||p_key_cols||
144 ' HAVING COUNT(*) > 1 )';
145 COMMIT;
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 RAISE;
150 END DUP_CHECK;
151
152
153 PROCEDURE MOVE_ERRORS(p_src_tbl_name IN VARCHAR2, p_err_tbl_name IN VARCHAR2, p_trgt_tbl_name IN VARCHAR2, p_src_typ IN VARCHAR2, p_load_id IN NUMBER) IS
154 BEGIN
155 MOVE_ERRORS(p_src_tbl_name=>p_src_tbl_name,p_err_tbl_name=>p_err_tbl_name,p_trgt_tbl_name=>p_trgt_tbl_name,p_src_typ=>p_src_typ,p_filter=>null,p_load_id=>p_load_id);
156
157 END MOVE_ERRORS;
158
159
160 FUNCTION GET_ERROR(p_error_cd VARCHAR2) RETURN VARCHAR2 IS
161
162 v_err_msg VARCHAR2(240);
163
164 BEGIN
165 EXECUTE IMMEDIATE 'SELECT lkup_name FROM DDR_R_LKUP_MST WHERE lkup_typ_cd=''ERR'' AND lkup_cd=:c1' INTO v_err_msg USING p_error_cd;
166
167 RETURN v_err_msg;
168 EXCEPTION
169 WHEN OTHERS THEN
170 RAISE;
171 END GET_ERROR;
172
173
174 PROCEDURE IS_VALID(p_src_tab VARCHAR2
175 ,p_src_clm VARCHAR2
176 ,p_tgt_tab VARCHAR2
177 ,p_tgt_clm VARCHAR2
178 ,p_null_chk VARCHAR2 DEFAULT 'N'
179 ,p_where VARCHAR2 DEFAULT NULL
180 ,p_error_cols VARCHAR2 DEFAULT NULL
181 ,p_error_cd VARCHAR2
182 ,p_load_id NUMBER) IS
183
184
185 v_update_stmt VARCHAR2(4000);
186 v_where_stmt VARCHAR2(4000);
187 v_null_stmt VARCHAR2(1000);
188 v_sql VARCHAR2(4000);
189 v_src_clm_split code_type;
190 v_src_clm_count NUMBER;
191 v_pos NUMBER;
192 v_err_reason_string VARCHAR2(100);
193
194 BEGIN
195 v_src_clm_split := split(p_src_clm,',');
196 v_src_clm_count := v_src_clm_split.COUNT;
197
198 IF p_error_cols IS NULL THEN --{
199 v_err_reason_string := remove_quoted_string(p_src_clm);
200 ELSE
201 v_err_reason_string := p_error_cols;
202 END IF;--}
203
204 EXECUTE IMMEDIATE 'UPDATE '||p_src_tab||' SET err_reason = '' '' WHERE err_reason=''Generic Error'' AND load_id ='||p_load_id;
205
206 v_update_stmt := 'UPDATE '||p_src_tab||' SET err_reason = err_reason ||'''||v_err_reason_string||':''||ddr_odi.get_error('''||p_error_cd||
207 ''')||'';'' WHERE load_id = '||p_load_id||' AND (('|| p_src_clm||') NOT IN (SELECT ' ||p_tgt_clm||' FROM '||p_tgt_tab||' WHERE (1=1) ';
208
209 IF p_where IS NOT NULL THEN --{
210 v_where_stmt := 'AND ('||p_where||'))';
211 ELSE
212 v_where_stmt := ')';
213 END IF; --}
214
215 IF p_null_chk = 'Y' THEN --{
216 v_null_stmt := ' OR '|| v_src_clm_split(0)||' IS NULL';
217 WHILE (v_src_clm_count > 1) LOOP --{
218 v_pos := instr(v_src_clm_split(v_src_clm_count-1),'''',1,1);
219 IF v_pos = 0 THEN
220 v_null_stmt := v_null_stmt || ' OR ' || v_src_clm_split(v_src_clm_count-1)||' IS NULL ';
221 END IF;
222 v_src_clm_count:=v_src_clm_count-1;
223 END LOOP;--}
224 v_null_stmt := v_null_stmt || ')';
225 ELSE
226 v_null_stmt := v_null_stmt || ')';
227 END IF;--}
228
229 v_sql := v_update_stmt || v_where_stmt || v_null_stmt;
230
231 EXECUTE IMMEDIATE v_sql;
232 COMMIT;
233
234 EXCEPTION
235 WHEN OTHERS THEN
236 RAISE;
237 END IS_VALID;
238
239
240 PROCEDURE NULL_CHECK(p_src_tab VARCHAR2, p_src_clm VARCHAR2, p_error_cd VARCHAR2,p_load_id NUMBER) IS
241
242 v_sql VARCHAR2(4000);
243
244 BEGIN
245 EXECUTE IMMEDIATE 'UPDATE '||p_src_tab||' SET err_reason = '' '' WHERE err_reason=''Generic Error'' AND load_id='||p_load_id;
246
247 v_sql := 'UPDATE '||p_src_tab||' SET err_reason = err_reason ||'''||p_src_clm||':''||ddr_odi.get_error('''||p_error_cd||''')||'';'' WHERE '||p_src_clm||' IS NULL AND load_id='||p_load_id;
248
249 EXECUTE IMMEDIATE v_sql;
250 COMMIT;
251
252 EXCEPTION
253 WHEN OTHERS THEN
254 RAISE;
255 END NULL_CHECK;
256
257
258 PROCEDURE ALLOC_MOVE_ERR(p_src_tbl_name VARCHAR2,
259 p_err_tbl_name VARCHAR2,
260 p_trgt_tbl_name VARCHAR2,
261 p_run_typ VARCHAR2,
262 p_load_id NUMBER,
263 p_generic_err_msg VARCHAR2) IS
264
265 v_user VARCHAR2(100);
266
267 CURSOR src_list IS
268 SELECT column_name
269 FROM all_tab_columns
270 WHERE owner = v_user
271 AND table_name = UPPER(p_src_tbl_name)
272 ORDER BY column_id;
273
274 v_stg_tbl_name VARCHAR2(100);
275 v_src_col_list code_type;
276 v_err_select VARCHAR2(10000);
277 v_src_select VARCHAR2(10000);
278 v_src_select_after VARCHAR2(10000);
279 v_count NUMBER := 0;
280 v_insert_sql VARCHAR2(10000);
281 v_insert_stmt VARCHAR2(10000);
282 v_insert_select_stmt VARCHAR2(10000);
283 v_delete_sql VARCHAR2(10000);
284 v_err_msg VARCHAR2(300);
285
286 BEGIN
287 v_user := get_user;
288
289 FOR col IN src_list LOOP
290 v_src_col_list(v_count):=col.column_name;
291 v_src_select := v_src_select || ', ' || v_src_col_list(v_count);
292 v_count := v_count+1;
293 END LOOP;
294 v_src_select := LTRIM(v_src_select,',');
295 v_src_select := REPLACE(v_src_select,', ALLOC_PARENT_IDNT','');
296 v_src_select := REPLACE(v_src_select,', SLS_AMOUNT','');
297 v_src_select := REPLACE(v_src_select,', RTRN_AMOUNT','');
298 v_src_select := REPLACE(v_src_select,', SLS_COST_AMOUNT','');
299 v_src_select := REPLACE(v_src_select,', RTRN_COST_AMOUNT','');
300 /* For forecast flow, taking out unused column */
301 IF p_src_tbl_name = 'DDR_I_SLS_FRCST_ITEM' THEN
302 v_src_select := REPLACE(v_src_select,', RTL_BSNS_UNIT_CD','');
303 END IF;
304
305 v_err_select:=v_src_select || ', LOAD_ID, ERR_REASON, ACTION_FLAG, SRC_IDNT_FLAG';
306
307 IF p_run_typ = 'A' THEN --{
308 /* Moving error records from allocation flow */
309 /* Select for the invalid ORG Alloc records */
310 /* Checking if it's generic error messages */
311 IF p_generic_err_msg = 'Y' THEN
312 v_err_msg:='Generic Error';
313 ELSE
314 v_err_msg:=ddr_odi.get_error('DSR-1009');
315 END IF;
316 v_src_select_after:=v_src_select||', '||p_load_id||', '''||v_err_msg||''', ''N'', ''I''';
317
318 v_insert_stmt:='INSERT INTO '||p_err_tbl_name||' ( '||v_err_select||') SELECT '||v_src_select_after||' FROM '||p_src_tbl_name||' WHERE rowid IN';
319
320 v_insert_select_stmt:=' ((SELECT rowid FROM '||p_src_tbl_name||' WHERE LOC_IDNT_FLAG=''O'' AND PERIOD_TYP_FLAG=''D'' AND rowid NOT IN'||
321 ' (SELECT ALLOC_PARENT_IDNT FROM '||p_src_tbl_name||' WHERE ALLOC_PARENT_IDNT IS NOT NULL ))'||
322 ' UNION'||
323 ' (SELECT rowid FROM '||p_src_tbl_name||' WHERE LOC_IDNT_FLAG NOT IN(''O'',''B'') AND PERIOD_TYP_FLAG IN(''D'',''W'') AND rowid NOT IN'||
324 ' (SELECT ALLOC_PARENT_IDNT FROM '||p_src_tbl_name||' WHERE ALLOC_PARENT_IDNT IS NOT NULL)))';
325 v_insert_sql:=v_insert_stmt||v_insert_select_stmt;
326
327 EXECUTE IMMEDIATE v_insert_sql;
328
329 /* Union Select for Invalid Time Alloc records */
330 /* Checking if it's generic error messages */
331 IF p_generic_err_msg = 'Y' THEN
332 v_err_msg:='Generic Error';
333 ELSE
334 v_err_msg:=ddr_odi.get_error('DSR-1008');
335 END IF;
336 v_src_select_after:=v_src_select||', '||p_load_id||', '''||v_err_msg||''', ''N'', ''I''';
337
338 v_insert_stmt:='INSERT INTO '||p_err_tbl_name||' ( '||v_err_select||') SELECT '||v_src_select_after||' FROM '||p_src_tbl_name||' WHERE rowid IN';
339
340 v_insert_select_stmt:=' ((SELECT rowid FROM '||p_src_tbl_name||' WHERE PERIOD_TYP_FLAG=''W'' AND LOC_IDNT_FLAG=''B'''||
341 ' AND ALLOC_PARENT_IDNT IS NULL AND rowid NOT IN'||
342 ' (SELECT ALLOC_PARENT_IDNT FROM '||p_src_tbl_name||' WHERE ALLOC_PARENT_IDNT IS NOT NULL))'||
343 ' UNION'||
344 ' (SELECT rowid FROM '||p_src_tbl_name||' WHERE PERIOD_TYP_FLAG NOT IN (''W'',''D'') AND LOC_IDNT_FLAG IN (''O'',''B'') AND'||
345 ' ALLOC_PARENT_IDNT IS NULL AND rowid NOT IN'||
346 ' (SELECT ALLOC_PARENT_IDNT FROM '||p_src_tbl_name||' WHERE ALLOC_PARENT_IDNT IS NOT NULL)))';
347 v_insert_sql:=v_insert_stmt||v_insert_select_stmt;
348
349 EXECUTE IMMEDIATE v_insert_sql;
350
351 /* Union Select for Parent of Invalid middle parent (has both org and time alloc) */
352 /* Checking if it's generic error messages */
353 IF p_generic_err_msg = 'Y' THEN
354 v_err_msg:='Generic Error';
355 ELSE
356 v_err_msg:=ddr_odi.get_error('DSR-1008');
357 END IF;
358 v_src_select_after:=v_src_select||', '||p_load_id||', '''||v_err_msg||''', ''N'', ''I''';
362 ' (SELECT ALLOC_PARENT_IDNT FROM '||p_src_tbl_name||' WHERE ALLOC_PARENT_IDNT IS NOT NULL)))';
359 v_insert_stmt:='INSERT INTO '||p_err_tbl_name||' ( '||v_err_select||') SELECT '||v_src_select_after||' FROM '||p_src_tbl_name||' WHERE rowid IN';
360 v_insert_select_stmt:=' (SELECT rowid FROM '||p_src_tbl_name||' WHERE rowid IN'||
361 ' (SELECT ALLOC_PARENT_IDNT FROM '||p_src_tbl_name||' WHERE PERIOD_TYP_FLAG=''W'' AND rowid NOT IN'||
363 v_insert_sql:=v_insert_stmt||v_insert_select_stmt;
364
365 EXECUTE IMMEDIATE v_insert_sql;
366 /*SWATANTRA Added below code to handle Period flag and Loc Flag NULL VALUES*/
367 V_INSERT_STMT:='INSERT INTO '||P_ERR_TBL_NAME||' ( '||V_ERR_SELECT||') SELECT '||V_SRC_SELECT_AFTER||' FROM '||P_SRC_TBL_NAME||' WHERE rowid IN';
368 v_insert_select_stmt:=' (SELECT rowid FROM '||p_src_tbl_name||' WHERE LOC_IDNT_FLAG IS NULL OR PERIOD_TYP_FLAG IS NULL)';
369 V_INSERT_SQL:=V_INSERT_STMT||V_INSERT_SELECT_STMT;
370 /*SWATANTRA Added below code to handle Period flag and Loc Flag NULL VALUES*/
371 EXECUTE IMMEDIATE V_INSERT_SQL;
372
373 /* SWATANTRA Added below code to handle Period flag and Loc Flag INVALID VALUES*/
374 V_INSERT_STMT:='INSERT INTO '||P_ERR_TBL_NAME||' ( '||V_ERR_SELECT||') SELECT '||V_SRC_SELECT_AFTER||' FROM '||P_SRC_TBL_NAME||' WHERE rowid IN';
375 v_insert_select_stmt:=' (SELECT rowid FROM '||p_src_tbl_name||' WHERE LOC_IDNT_FLAG NOT IN (''O'',''B'') OR PERIOD_TYP_FLAG NOT IN (''W'',''D''))';
376 V_INSERT_SQL:=V_INSERT_STMT||V_INSERT_SELECT_STMT;
377 /*SWATANTRA Added below code to handle Period flag and Loc Flag INVALID VALUES*/
378 EXECUTE IMMEDIATE V_INSERT_SQL;
379
380 /* SWATANTRA Added below code to INVALID_LOC_CD, ORG_LVL_CD,ORG_CD*/
381 V_INSERT_STMT:='INSERT INTO '||P_ERR_TBL_NAME||' ( '||V_ERR_SELECT||') SELECT '||V_SRC_SELECT_AFTER||' FROM '||P_SRC_TBL_NAME||' WHERE rowid NOT IN';
382 V_INSERT_SELECT_STMT:=' (SELECT '||P_SRC_TBL_NAME||'.'||'rowid FROM '||P_SRC_TBL_NAME||','||'DDR_R_ORG_ALLOC '||' WHERE '||
383 P_SRC_TBL_NAME||'.'||'RTL_ORG_CD '||'= DDR_R_ORG_ALLOC.ORG_CD '|| 'AND '||P_SRC_TBL_NAME||'.'||'LOC_IDNT_CD' ||' =DECODE('||
384 P_SRC_TBL_NAME||'.'||'ALLOC_PARENT_IDNT, NULL,DDR_R_ORG_ALLOC.LOC_IDNT_CD,DDR_R_ORG_ALLOC.BSNS_UNIT_CD) '||
385 'AND '||p_src_tbl_name||'.'||'ORG_LVL_CD' ||' =DDR_R_ORG_ALLOC.ORG_LVL_CD ) AND '||p_src_tbl_name||'.'||'PERIOD_TYP_FLAG =''W'''||
386 'AND '||p_src_tbl_name||'.'||'LOC_IDNT_FLAG =''O''';
387 V_INSERT_SQL:=V_INSERT_STMT||V_INSERT_SELECT_STMT;
388 /* SWATANTRA Added below code to handle Period flag and Loc Flag NULL VALUES*/
389 EXECUTE IMMEDIATE V_INSERT_SQL;
390 COMMIT;
391
392 /* Creating the ORG DELETE SQL statement */
393 v_delete_sql:='DELETE FROM '||p_src_tbl_name||' WHERE rowid IN'||
394 /* Select for the invalid ORG Alloc records */
395 ' ((SELECT rowid FROM '||p_src_tbl_name||' WHERE LOC_IDNT_FLAG=''O'' AND PERIOD_TYP_FLAG=''D'' AND rowid NOT IN'||
396 ' (SELECT ALLOC_PARENT_IDNT FROM '||p_src_tbl_name||' WHERE ALLOC_PARENT_IDNT IS NOT NULL ))'||
397 /* Union Select for Invalid Time Alloc records */
398 ' UNION'||
399 ' (SELECT rowid FROM '||p_src_tbl_name||' WHERE PERIOD_TYP_FLAG=''W'' AND LOC_IDNT_FLAG=''B'' AND'||
400 ' ALLOC_PARENT_IDNT IS NULL AND rowid NOT IN'||
401 ' (SELECT ALLOC_PARENT_IDNT FROM '||p_src_tbl_name||' WHERE ALLOC_PARENT_IDNT IS NOT NULL))'||
402 ' UNION'||
403 /* Union Select for Parent of Invalid middle parent (has both org and time alloc) */
404 ' (SELECT rowid FROM '||p_src_tbl_name||' WHERE rowid IN'||
405 ' (SELECT ALLOC_PARENT_IDNT FROM '||p_src_tbl_name||' WHERE PERIOD_TYP_FLAG=''W'' AND LOC_IDNT_FLAG=''B'' AND rowid NOT IN'||
406 ' (SELECT ALLOC_PARENT_IDNT FROM '||p_src_tbl_name||' WHERE ALLOC_PARENT_IDNT IS NOT NULL)))'||
407 /* Union Select for Invalid middle parent (has both org and time alloc) */
408 ' UNION'||
409 ' (SELECT rowid FROM '||p_src_tbl_name||' WHERE PERIOD_TYP_FLAG=''W'' AND LOC_IDNT_FLAG=''B'' AND rowid NOT IN'||
410 ' (SELECT ALLOC_PARENT_IDNT FROM '||p_src_tbl_name||' WHERE ALLOC_PARENT_IDNT IS NOT NULL))'||
411 /*Union Select for Invalid code for Org Allocation */
412 ' UNION'||
413 ' (SELECT rowid FROM '||p_src_tbl_name||' WHERE LOC_IDNT_FLAG NOT IN(''O'',''B'') AND PERIOD_TYP_FLAG IN(''D'',''W'') AND rowid NOT IN'||
414 ' (SELECT ALLOC_PARENT_IDNT FROM DDR_I_RTL_SL_RTN_ITEM WHERE ALLOC_PARENT_IDNT IS NOT NULL))'||
415 /*Union Select for Invalid code for time allocation*/
416 ' UNION'||
417 ' (SELECT rowid FROM '||p_src_tbl_name||' WHERE PERIOD_TYP_FLAG NOT IN (''W'',''D'') AND LOC_IDNT_FLAG IN (''O'',''B'')'||
418 ' AND ALLOC_PARENT_IDNT IS NULL AND rowid NOT IN'||
419 ' (SELECT ALLOC_PARENT_IDNT FROM '||p_src_tbl_name||' WHERE ALLOC_PARENT_IDNT IS NOT NULL)))';
420
421 EXECUTE IMMEDIATE v_delete_sql;
422 COMMIT;
423
424 ELSIF p_run_typ = 'F' THEN --{
425 /*Moving errors from the regular interface */
426 v_src_select_after:=v_src_select||', '||p_load_id||', ''Generic Error'', ''N'', ''I''';
427
428 v_stg_tbl_name:='I$_' || p_trgt_tbl_name;
429
430 /* Creating the INSERT SQL statement */
431 /* Moving error records for records that had no allocation */
432 v_insert_sql:='INSERT INTO '||p_err_tbl_name||' ( '||v_err_select||') SELECT '||v_src_select_after||' FROM '||p_src_tbl_name||
433 ' WHERE PERIOD_TYP_FLAG=''D'' AND LOC_IDNT_FLAG=''B'' AND ALLOC_PARENT_IDNT IS NULL AND NOT EXISTS'||
434 ' ( SELECT REC_ID FROM '||v_stg_tbl_name||' WHERE '||v_stg_tbl_name||'.ROWID_INT = '||p_src_tbl_name||'.rowid )';
435
436 EXECUTE IMMEDIATE v_insert_sql;
437
438 /* Moving error records that had Org allocation children fail*/
439 v_insert_sql:='INSERT INTO '||p_err_tbl_name||' ( '||v_err_select||') SELECT '||v_src_select_after||' FROM '||p_src_tbl_name||
440 ' WHERE rowid IN'||
444 ' (SELECT rowid_int from '||v_stg_tbl_name||' WHERE '||v_stg_tbl_name||'.rowid_int = '||p_src_tbl_name||'.rowid)))';
441 ' (SELECT rowid FROM '||p_src_tbl_name||' WHERE rowid IN'||
442 ' (SELECT ALLOC_PARENT_IDNT FROM '||p_src_tbl_name||' WHERE ALLOC_PARENT_IDNT IS NOT NULL AND LOC_IDNT_FLAG=''B'' AND'||
443 ' PERIOD_TYP_FLAG=''D'' AND rowid NOT IN '||
445 EXECUTE IMMEDIATE v_insert_sql;
446
447 /*Moving error records that had Time allocation children fail*/
448 v_insert_sql:='INSERT INTO '||p_err_tbl_name||' ( '||v_err_select||') SELECT '||v_src_select_after||' FROM '||p_src_tbl_name||
449 ' WHERE rowid IN'||
450 ' (SELECT rowid FROM '||p_src_tbl_name||' WHERE rowid IN'||
451 ' (SELECT ALLOC_PARENT_IDNT FROM '||p_src_tbl_name||' WHERE ALLOC_PARENT_IDNT IS NOT NULL AND LOC_IDNT_FLAG=''B'' AND'||
452 ' PERIOD_TYP_FLAG=''W'' AND rowid NOT IN '||
453 --' (SELECT rowid_int from '||v_stg_tbl_name||' WHERE '||v_stg_tbl_name||'.rowid_int = '||p_src_tbl_name||'.rowid)))';
454 ' (SELECT ALLOC_PARENT_IDNT from '||V_STG_TBL_NAME||','|| P_SRC_TBL_NAME||' WHERE '||V_STG_TBL_NAME||'.rowid_int = '||P_SRC_TBL_NAME||'.rowid)))';
455 EXECUTE IMMEDIATE v_insert_sql;
456
457 COMMIT;
458 END IF;--}
459
460 EXCEPTION
461 WHEN OTHERS THEN
462 RAISE;
463 END ALLOC_MOVE_ERR;
464
465
466 PROCEDURE parse_pad_accounts(p_max_level IN NUMBER DEFAULT 10) AS
467 v_elems account_array; -- array indexed by level
468 v_elems_ID account_array; -- array indexed by level
469 v_counter NUMBER := 0;
470 v_curr_level NUMBER := 1;
471 v_prev_level NUMBER := 0;
472
473 -- We want to denormailze the account hierarchy but because of SCD2, there could be records
474 -- with same account but different from/to dates. To remove this problem we need to associate
475 -- by account id and parent account id instead of acocunt code.
476 -- However, account id is generated by the dimension object. Currently the dimension object is
477 -- set as one-level and no hierarchy. Therefore there is no way to produce parent account id
478 -- there. As a result we have to use PL/SQL to add the parent account id after the SCD2 data is generated.
479 --
480 -- generate parent account id from SCD2 data, assuming new open records have higher
481 -- ID than older or closed ones. Couple of note points:
482 -- 1. Some duplicate records are present when looking up manager id. These will have mismatched
483 -- from/to dates and are filtered out by date order by emp_id in the first WHERE clause
484 -- 2. The top accounts are missed out in the first query. They are added in by the second query.
485 CURSOR account_cursor IS
486 SELECT ORG_HCHY_ID, HRCHY_CD, CHILD_ID BSNS_ENT_ID, LEVEL, CHILD_CD BSNS_ENT_CD,
487 SRC_SYS_DT, SRC_SYS_IDNT, EFF_FROM_DT, EFF_TO_DT
488 FROM
489 (
490 SELECT ORG_HCHY_ID, HRCHY_CD, PARENT_ID, PARENT_CD, CHILD_ID, CHILD_CD,
491 SRC_SYS_DT, SRC_SYS_IDNT, EFF_FROM_DT, EFF_TO_DT
492 FROM
493 (
494 SELECT t1.ORG_HCHY_ID ORG_HCHY_ID, t1.HRCHY_CD HRCHY_CD,
495 t1.ORG_BSNS_ENT_ID PARENT_ID, t1.BSNS_ENT_CD PARENT_CD,
496 t2.ORG_BSNS_ENT_ID CHILD_ID, t2.BSNS_ENT_CD CHILD_CD,
497 t2.SRC_SYS_DT SRC_SYS_DT, t2.SRC_SYS_IDNT SRC_SYS_IDNT,
498 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
499 FROM DDR_R_ORG_BSNS_ENT t1 INNER JOIN DDR_R_ORG_BSNS_ENT t2
500 ON (t1.BSNS_ENT_CD = t2.BSNS_ENT_PRNT_CD AND t1.ORG_HCHY_ID = t2.ORG_HCHY_ID)
501 )
502 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))
503 UNION
504 (
505 SELECT
506 ORG_HCHY_ID, HRCHY_CD,
507 CASE WHEN BSNS_ENT_PRNT_CD is null THEN null
508 ELSE ORG_BSNS_ENT_ID
509 END PARENT_ID,
510 BSNS_ENT_PRNT_CD PARENT_CD,
511 ORG_BSNS_ENT_ID CHILD_ID,
512 BSNS_ENT_CD CHILD_CD,
513 SRC_SYS_DT, SRC_SYS_IDNT,
514 EFF_FROM_DT, EFF_TO_DT
515 FROM DDR_R_ORG_BSNS_ENT
516 WHERE BSNS_ENT_PRNT_CD is null
517 )
518 )
519 START WITH PARENT_CD is null
520 CONNECT BY PARENT_ID = PRIOR CHILD_ID;
521
522
523 -- store previous record columns before level change
524 v_hchy_id NUMBER;
525 v_hchy_cd VARCHAR2(30);
526 v_acct_id NUMBER;
527 v_acct_cd VARCHAR2(30);
528 v_sys_id VARCHAR2(40);
529 v_sys_dt DATE;
530
531 v_sql_stmt VARCHAR2(2048) := '';
532 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,';
533 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,';
534 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(';
535
536 v_hchy_cols VARCHAR2(100) :='';
537 v_acct_cols VARCHAR2(100) := '';
538 v_prnt_cols VARCHAR2(1000) := '';
539 v_sys_cols VARCHAR2(100) := '';
540
541 v_commit_stmt VARCHAR2(10) := 'commit';
542
543 BEGIN
544 -- clean up the previous records
545 EXECUTE IMMEDIATE 'truncate table DDR.DDR_R_AOH_LVL_DTL drop storage';
546
547 -- The logic detects individual branch in the tree:
548 -- 1. Read a row, if it is second row or more, we initialize previous level
549 -- 2. store the account level and code from current record
553 FOR acct_rec IN account_cursor
550 -- 3. if level has decreased, we write all previously accumulated accounts into one record
551 -- 4. clean up and start from current record to start traversing a new branch.
552
554 LOOP
555 v_counter := v_counter + 1;
556
557 -- set previous level after one record is read
558 IF (v_counter > 1)
559 THEN
560 v_prev_level := v_curr_level;
561 END IF;
562
563 v_curr_level := acct_rec.LEVEL;
564 v_elems(v_counter) := acct_rec.BSNS_ENT_CD;
565 v_elems_id(v_counter) := acct_rec.BSNS_ENT_ID;
566
567 -- Adjust counter and account array pointer when level is not in order
568 IF ( v_prev_level > 0 AND v_curr_level <= v_prev_level )
569 THEN
570 -- write to table in Elems from 1..Counter
571 v_hchy_cols := v_hchy_id || ',' || '''' || v_hchy_cd || '''' || ',';
572 v_acct_cols := v_acct_id || ',' || '''' || v_acct_cd || '''' || ',' || v_prev_level || ',';
573 FOR p in 1..p_max_level-1
574 LOOP
575 IF (p < v_counter)
576 THEN
577 v_prnt_cols := v_prnt_cols || '''' || v_elems(p) || '''' || ',' || v_elems_id(p) || ',';
578 ELSE
579 v_prnt_Cols := v_prnt_Cols || '''' || v_elems(v_counter-1) || '''' || ',' || v_elems_id(v_counter-1) || ','; -- padding
580 END IF;
581 END LOOP;
582
583 v_sys_cols := '''' || v_sys_id || '''' || ',' || 'TO_DATE(' || '''' || v_sys_dt || '''' || '),' || 'SYSDATE' || ', -1,' || 'SYSDATE' || ',-1,' || '''' || USER || '''' || ',' || '''' || USER || '''' || ',-1';
584 v_sql_stmt := v_sql_hdr1 || v_sql_hdr2 || v_sql_hdr3 || v_hchy_cols || v_acct_cols || v_prnt_cols || v_sys_cols || ')';
585 EXECUTE IMMEDIATE v_sql_stmt;
586
587 -- clean up the statements for next time
588 v_hchy_cols := '';
589 v_acct_cols := '';
590 v_prnt_cols := '';
591 v_sys_cols := '';
592 END IF;
593
594 -- reset account array to previous level
595 FOR i IN v_curr_level..v_counter
596 LOOP
597 v_elems(i) := null;
598 v_elems_id(i) := null;
599 END LOOP;
600
601 -- retreat back to the levels corresponding to the record we read
602 v_counter := v_curr_level;
603 v_prev_level := v_curr_level - 1;
604 v_elems(v_counter) := acct_rec.BSNS_ENT_CD;
605 v_elems_id(v_counter) := acct_rec.BSNS_ENT_ID;
606
607 -- store account attrs used to write to table when level changes as at that time
608 -- cursor already points to the next record.
609 v_hchy_id := acct_rec.ORG_HCHY_ID;
610 v_hchy_cd := acct_rec.HRCHY_CD;
611 v_acct_cd := acct_rec.BSNS_ENT_CD;
612 v_acct_id := acct_rec.BSNS_ENT_ID;
613 v_sys_id := acct_rec.SRC_SYS_IDNT;
614 v_sys_dt := acct_rec.SRC_SYS_DT;
615 END LOOP;
616
617 -- Last record in cursor is also a lowest level account and ened to be exported
618 IF v_counter > 0
619 THEN
620 v_hchy_cols := v_hchy_id || ',' || '''' || v_hchy_cd || '''' || ',';
621 v_acct_cols := v_acct_id || ',' || '''' || v_acct_cd || '''' || ',' || v_curr_level || ',';
622 FOR p in 1..p_max_level-1
623 LOOP
624 IF (p <= v_counter) -- note the difference here on counter from with above
625 THEN
626 v_prnt_cols := v_prnt_cols || '''' || v_elems(p) || '''' || ',' || v_elems_id(p) || ',';
627 ELSE
628 v_prnt_cols := v_prnt_cols || '''' || v_acct_cd || '''' || ',' || v_acct_id || ','; -- padding
629 END IF;
630 END LOOP;
631
632 v_sys_cols := '''' || v_sys_id || '''' || ',' || 'TO_DATE(' || '''' || v_sys_dt || '''' || '),' || 'SYSDATE' || ', -1,' || 'SYSDATE' || ',-1,' || '''' || USER || '''' || ',' || '''' || USER || '''' || ',-1';
633 v_sql_stmt := v_sql_hdr1 || v_sql_hdr2 || v_sql_hdr3 || v_hchy_cols || v_acct_cols || v_prnt_cols || v_sys_cols || ')';
634 EXECUTE IMMEDIATE v_sql_stmt;
635 END IF;
636 EXECUTE IMMEDIATE v_commit_stmt;
637
638 EXCEPTION
639 WHEN others THEN
640 ROLLBACK;
641
642 END parse_pad_accounts;
643
644
645 PROCEDURE MOVE_ERRORS(p_src_tbl_name IN VARCHAR2,p_err_tbl_name IN VARCHAR2,p_trgt_tbl_name IN VARCHAR2,p_src_typ IN VARCHAR2,p_filter IN VARCHAR2,p_load_id IN NUMBER) IS
646
647 v_user VARCHAR2(100);
648
649 CURSOR src_list IS
650 SELECT column_name
651 FROM all_tab_columns
652 WHERE owner = v_user
653 AND table_name = p_src_tbl_name
654 ORDER BY column_id;
655
656 v_src_col_list code_type;
657 v_err_select VARCHAR2(10000);
658 v_src_select VARCHAR2(10000);
659 v_count NUMBER := 0;
660 v_stg_tbl_name VARCHAR2(30);
661 v_insert_sql VARCHAR2(10000);
662 v_delete_sql VARCHAR2(10000);
663 v_src_filter VARCHAR2(10000);
664
665 BEGIN
666 v_user := get_user;
667
668 FOR col IN src_list LOOP
669 v_src_col_list(v_count):=col.column_name;
670 v_src_select := v_src_select || ', ' || v_src_col_list(v_count);
671 v_count := v_count+1;
672 END LOOP;
673 v_src_select := LTRIM(v_src_select,',');
674
675 --p_src_typ's are 'R'=Reference Flows
676 --p_src_typ's are 'F'=Fact Flows
677 --p_src_typ's are 'S'=Staging Dangling Key Flows
678 --p_src_typ's are 'E'=Extraordinary flows including AOH
679 IF p_src_typ = 'R' THEN
680 v_err_select:=v_src_select || ', LOAD_ID, ERR_REASON, ACTION_FLAG';
681 v_src_select:=v_src_select || ', ' || p_load_id || ', ''Generic Error'', ''N''';
682 ELSIF p_src_typ IN ('F','E') THEN
683 v_err_select:=v_src_select || ', LOAD_ID, ERR_REASON, ACTION_FLAG, SRC_IDNT_FLAG';
684 v_src_select:=v_src_select || ', ' || p_load_id || ', ''Generic Error'', ''N'', ''I''';
685 ELSIF p_src_typ = 'S' THEN
686 v_err_select:=v_src_select || ', ERR_REASON, ACTION_FLAG, SRC_IDNT_FLAG';
687 v_src_select:='';
688 FOR i IN v_src_col_list.FIRST..v_src_col_list.LAST LOOP
689 IF v_src_col_list(i)='LOAD_ID' THEN
690 v_src_select:=v_src_select || ', ' || p_load_id;
691 ELSE
692 v_src_select := v_src_select || ', ' || v_src_col_list(i);
693 END IF;
694 END LOOP;
695 v_src_select := LTRIM(v_src_select,',');
696 v_src_select:=v_src_select || ', ''Generic Error'', ''N'', ''S''';
697 /* For forecast flow, taking out unused column */
698 IF p_src_tbl_name = 'DDR_S_SLS_FRCST_ITEM' THEN
699 v_src_select := REPLACE(v_src_select,', RTL_BSNS_UNIT_CD','');
700 v_err_select := REPLACE(v_err_select,', RTL_BSNS_UNIT_CD','');
701 END IF;
702 END IF;
703
704 v_stg_tbl_name:='I$_' || p_trgt_tbl_name;
705
706 IF p_filter IS NOT NULL THEN --{
707 v_src_filter:='('||p_filter||')';
708 ELSE
709 v_src_filter:='(1=1)';
710 END IF;--}
711
712 /* Creating the INSERT SQL statement */
713 v_insert_sql:='INSERT INTO '||p_err_tbl_name||' ( '||v_err_select||') SELECT '||v_src_select||' FROM '||p_src_tbl_name||' WHERE NOT EXISTS ( '||
714 ' SELECT ROWID_INT FROM '||v_stg_tbl_name||' WHERE '||v_stg_tbl_name||'.ROWID_INT = '||p_src_tbl_name||'.rowid ) AND '||v_src_filter;
715
716 EXECUTE IMMEDIATE v_insert_sql;
717 COMMIT;
718
719 IF p_src_typ IN ('R','E') THEN
720 /* Creating the DELETE SQL statement for reference flows only*/
721 v_delete_sql:='DELETE FROM ' || p_src_tbl_name || ' WHERE (REC_ID) IN (SELECT REC_ID FROM ' || p_src_tbl_name ||
722 ' WHERE NOT EXISTS ( SELECT REC_ID FROM ' || v_stg_tbl_name || ' WHERE ' || v_stg_tbl_name || '.REC_ID = ' || p_src_tbl_name || '.REC_ID ) AND '||v_src_filter||')';
723
724 EXECUTE IMMEDIATE v_delete_sql;
725 COMMIT;
726 END IF;
727
728 EXCEPTION
729 WHEN OTHERS THEN
730 RAISE;
731 END MOVE_ERRORS;
732
733
734 PROCEDURE TRUNC_TABLE ( p_table IN VARCHAR2 ) IS
735
736 v_user VARCHAR2(100);
737 v_trunc_sql VARCHAR2(1000);
738
739 BEGIN
740 v_user := get_user;
741
742 v_trunc_sql := 'truncate table '|| v_user||'.'||p_table || ' drop storage';
743 EXECUTE IMMEDIATE v_trunc_sql;
744
745 END TRUNC_TABLE;
746
747
748 END DDR_ODI;
[Home] [Help]