[Home] [Help]
PACKAGE BODY: APPS.EDW_FLEX_PUSH_C
Source
1 Package Body EDW_FLEX_PUSH_C AS
2 /* $Header: EDWFLXGB.pls 115.7 2002/12/05 23:05:17 arsantha ship $ */
3 G_DIMENSION VARCHAR2(100);
4 G_INDEX NUMBER := 0;
5 G_PUSH_DATE_RANGE1 Date:=Null;
6 G_PUSH_DATE_RANGE2 Date:=Null;
7 g_row_count Number:=0;
8 g_exception_msg varchar2(2000):=Null;
9 newline varchar2(10):='
10 ';
11
12 Procedure Push(Errbuf in out NOCOPY Varchar2,
13 Retcode in out NOCOPY Varchar2,
14 p_dimension IN VARCHAR2,
15 p_from_date IN VARCHAR2,
16 p_to_date IN VARCHAR2) IS
17 l_temp_date Date:=Null;
18 l_rows_inserted Number:=0;
19 l_duration Number:=0;
20 l_exception_msg Varchar2(2000):=Null;
21 cid number;
22 stmt varchar2(4000);
23 l_dummy NUMBER;
24 l_source_link VARCHAR2(128);
25 l_target_link VARCHAR2(128);
26
27 -- -------------------------------------------
28 -- Put any additional developer variables here
29 -- -------------------------------------------
30 Begin
31 Errbuf :=NULL;
32 Retcode:=0;
33 g_dimension := p_dimension;
34
35 /* should ideally change the value set to pass short names */
36 -- get databaselink
37 EDW_COLLECTION_UTIL.get_dblink_names(l_source_link, l_target_link);
38
39 stmt:= 'SELECT dim_name from edw_dimensions_md_v@'|| l_target_link ||
40 ' where dim_long_name = :longname';
41
42 cid := DBMS_SQL.OPEN_CURSOR;
43 DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
44 DBMS_SQL.BIND_VARIABLE(cid, ':longname', p_dimension);
45 DBMS_SQL.DEFINE_COLUMN(cid, 1, g_dimension, 100);
46 l_dummy := DBMS_SQL.EXECUTE_AND_FETCH(cid);
47 DBMS_SQL.COLUMN_VALUE(cid, 1, g_dimension);
48 DBMS_SQL.close_cursor(cid);
49
50
51 g_index := substr(g_dimension, 13 , instr(g_dimension, '_M') - 13 );
52
53 IF (Not EDW_COLLECTION_UTIL.setup(g_dimension)) THEN
54 errbuf := fnd_message.get;
55 Return;
56 END IF;
57
58 IF (p_from_date IS NULL) THEN
59 g_push_date_range1 := EDW_COLLECTION_UTIL.G_local_last_push_start_date -
60 EDW_COLLECTION_UTIL.g_offset;
61 ELSE
62 g_push_date_range1 := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
63 END IF;
64
65 IF (p_to_date IS NULL) THEN
66 g_push_date_range2 := EDW_COLLECTION_UTIL.G_local_curr_push_start_date;
67 ELSE
68 g_push_date_range2 := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
69 END IF;
70
71 edw_log.put_line( 'Push for User Defined dimension : '||p_dimension);
72 edw_log.put_line( 'The push range is from '||
73 to_char(EDW_FLEX_PUSH_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
74 to_char(EDW_FLEX_PUSH_C.g_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
75 edw_log.put_line(' ');
76
77 -- -----------------------------------------------------------------------------
78 -- Start of Collection , Developer Customizable Section
79 -- -----------------------------------------------------------------------------
80
81 edw_log.put_line(' ');
82 l_temp_date := sysdate;
83
84 -- -----------------------------------------------------------------------------
85 -- Call Push_Levels to push the two levels.
86 -- -----------------------------------------------------------------------------
87
88 Push_Levels(g_push_date_range1, g_push_date_range2);
89
90 l_duration := sysdate - l_temp_date;
91
92 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, null, g_push_date_range1, g_push_date_range2);
93
94 edw_log.put_line('Total rows inserted for '||g_dimension||' : '||g_row_count);
95 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
96 edw_log.put_line(' ');
97
98 commit;
99
100 Exception When others then
101 Errbuf:=sqlerrm;
102 Retcode:=sqlcode;
103 l_exception_msg := Retcode || ':' || Errbuf;
104 EDW_FLEX_PUSH_C.g_exception_msg := l_exception_msg;
105 rollback;
106 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_FLEX_PUSH_C.g_exception_msg,
107 g_push_date_range1, g_push_date_range2);
108
109 commit;
110 End;
111
112 -- -----------------------------------------------------------------------------
113 -- Push One Level will Insert into the staging table for Level with index
114 -- p_level. This procedure should check for existence of LAST_UPDATE_DATE,
115 -- CREATION_DATE, and DESCRIPTION in the view before inserting
116 -- -----------------------------------------------------------------------------
117
118 Procedure Push_One_Level(p_from_date IN date, p_to_date IN DATE, p_level IN NUMBER) IS
119 insertStmt varchar2(3000) := null;
120 cid NUMBER := 0;
121 l_dummy NUMBER := 0;
122
123 stmt Varchar2(4000);
124 l_count number;
125 l_column varchar2(40);
126 l_source_link VARCHAR2(128);
127 l_target_link VARCHAR2(128);
128
129 BEGIN
130
131 edw_log.put_line('Starting Push For EDW_FLEX_DIM'||g_index||'_L'||p_level||'_LSTG');
132
133 -- get databaselink
134 EDW_COLLECTION_UTIL.get_dblink_names(l_source_link, l_target_link);
135
136 stmt := 'Insert Into EDW_FLEX_DIM'||
137 g_index||'_L'||p_level||'_LSTG@' || l_target_link || '(
138 NAME, INSTANCE, '||
139 ' L'||p_level||'_FK, L'||p_level||'_PK, '||
140 ' LAST_UPDATE_DATE, CREATION_DATE, DESCRIPTION, OPERATION_CODE, COLLECTION_STATUS)
141 select ACTUAL_VALUE, INSTANCE,
142 NVL(L'||p_level||'_FK, ''NA_EDW'') L'||p_level||'_fk, L'||p_level||'_PK, ';
143 stmt := stmt || ' last_update_date, ';
144 stmt := stmt || ' creation_date, ';
145 stmt := stmt || ' description, ';
146 stmt := stmt||' NULL, ''READY'' from EDW_FLEX_DIM' ||g_index||'_L'||p_level||'_LCV@' || l_source_link;
147 /* if null then set to from_date + 0.1 seconds */
148 stmt := stmt ||' where NVL(last_update_date, to_date('''||
149 to_char(p_from_date,'MM/DD/YYYY HH24:MI:SS')||''','''|| 'MM/DD/YYYY HH24:MI:SS'||''''||')+1/864000) between :p_from_date and :p_to_date';
150
151 edw_log.put_line('Insert statement is : '|| stmt);
152
153 cid := DBMS_SQL.open_cursor;
154 DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
155 edw_log.put_line('Parsed stmt');
156
157
158 DBMS_SQL.BIND_VARIABLE(cid, ':p_from_date', p_from_date);
159 DBMS_SQL.BIND_VARIABLE(cid, ':p_to_date', p_to_date);
160 l_dummy := DBMS_SQL.EXECUTE(cid);
161 DBMS_SQL.close_cursor(cid);
162 commit;
163 g_row_count := g_row_count + l_dummy;
164 edw_log.put_line('Inserted '||l_dummy|| ' rows into the staging table');
165 edw_log.put_line('Commiting records for EDW_FLEX_DIM'||g_index||'_L'||p_level||'_LSTG');
166
167 commit;
168
169 edw_log.put_line('Completed Push_EDW_FLEX_DIM'||g_index||'_L'||p_level||'_LSTG'||newline||newline);
170
171 Exception When others then
172 raise;
173 commit;
174 END;
175
176
177 Procedure Push_Levels(p_from_date IN date, p_to_date IN DATE) IS
178 BEGIN
179 Push_One_Level(p_from_date, p_to_date, 2); /* Lower level push */
180 Push_One_Level(p_from_date, p_to_date, 1); /* Higher level push */
181 END;
182
183
184 End EDW_FLEX_PUSH_C;