[Home] [Help]
PACKAGE BODY: APPS.EDW_INSTANCE_COLLECT
Source
1 package body EDW_INSTANCE_COLLECT AS
2 /*$Header: EDWCINSB.pls 115.10 2003/11/06 00:55:38 vsurendr ship $*/
3
4 PROCEDURE COLLECT_DIMENSION(errbuf out NOCOPY varchar2, retcode out NOCOPY varchar2, p_dim_name varchar2) is
5 Begin
6 g_collection_start_date:=sysdate;
7 g_dim_name:=p_dim_name;
8 retcode:=0;
9 errbuf:=' ';
10 if p_dim_name is null then
11 errbuf:='No dimension name specified';
12 retcode:=2;
13 return;
14 end if;
15 EDW_OWB_COLLECTION_UTIL.init_all(p_dim_name,null,'bis.edw.collect');
16 init_all;
17 write_to_log_file_n('EDW_INSTANCE_COLLECT.Collect for '||p_dim_name||get_time);
18 make_insert_stmt;
19 if g_status =false then
20 errbuf:=g_status_message;
21 retcode:=2;
22 return_with_error;
23 return;
24 else
25 write_to_log_file_n('Made insert stmt for dimension '||p_dim_name||get_time);
26 end if;
27 execute_insert_stmt;
28 if g_status =false then
29 errbuf:=g_status_message;
30 retcode:=2;
31 return_with_error;
32 return;
33 else
34 write_to_log_file_n('Executed insert stmt for dimension '||p_dim_name||get_time);
35 write_to_log_file_n('Finished moving data from edw_source_instances to edw_instance_lstg,
36 rows inserted '||g_number_rows_processed);
37 end if;
38 --call the main procedure
39 call_main_collection(errbuf,retcode);
40 if g_status =false then
41 g_status_message:='Error in calling main collection for dimension '||p_dim_name||get_time;
42 write_to_log_file_n(g_status_message);
43 errbuf:=g_status_message;
44 retcode:=2;
45 return_with_error;
46 return;
47 else
48 write_to_log_file_n('Finished calling main collection for dimension '||p_dim_name||get_time);
49 end if;
50 return_with_success;
51 Exception when others then
52 g_status_message:='Error in COLLECT Instance Dimension '||g_dim_name||' '||sqlerrm||get_time;
53 write_to_log_file_n(g_status_message);
54 errbuf:=g_status_message;
55 retcode:=2;
56 g_status:=false;
57 return_with_error;
58 End;
59
60 procedure Init_all is
61 Begin
62 g_status:=true;
63 g_insert_stmt :=null;
64 g_conc_program_id:=FND_GLOBAL.Conc_request_id;--my conc id
65 g_number_rows_processed :=0;
66 G_CONC_PROGRAM_NAME:=g_dim_name||'_T';
67 g_object_type:='DIMENSION';
68 g_status_message:=' ';
69 if fnd_profile.value('EDW_DEBUG')='Y' then
70 g_debug:=true;--look at the profile value for this
71 else
72 g_debug:=false;
73 end if;
74 End;
75
76 procedure make_insert_stmt is
77 begin
78 if g_debug then
79 write_to_log_file_n('In make_insert_stmt');
80 end if;
81 g_insert_stmt:='insert into EDW_INSTANCE_LSTG (
82 INSTANCE_CODE,
83 INSTANCE_PK,
84 INSTANCE_DP,
85 NAME,
86 ALL_FK,
87 DESCRIPTION,
88 COLLECTION_STATUS,
89 WAREHOUSE_TO_INSTANCE_LINK,
90 CREATION_DATE,
91 LAST_UPDATE_DATE)
92 select
93 INSTANCE_CODE,
94 INSTANCE_CODE,
95 INSTANCE_CODE,
96 NAME,
97 ''ALL'',
98 DESCRIPTION,
99 ''READY'',
100 WAREHOUSE_TO_INSTANCE_LINK,
101 CREATION_DATE,
102 LAST_UPDATE_DATE
103 from
104 EDW_SOURCE_INSTANCES_VL ';
105
106 Exception when others then
107 g_status_message:='Error in make_insert_stmt for Instance Dimension '||g_dim_name||' '||sqlerrm||get_time;
108 write_to_log_file_n(g_status_message);
109 g_status:=false;
110 End;
111
112 procedure execute_insert_stmt is
113 Begin
114 if g_debug then
115 write_to_log_file_n('In execute_insert_stmt');
116 end if;
117
118 delete EDW_INSTANCE_LSTG;
119 if g_debug then
120 write_to_log_file_n('Going to execute ');
121 write_to_log_file(g_insert_stmt);
122 end if;
123 execute immediate g_insert_stmt;
124 g_number_rows_processed:=sql%rowcount;
125 write_to_log_file_n('Inserted '||g_number_rows_processed||' rows into EDW_INSTANCE_LSTG');
126 Exception when others then
127 g_status_message:='Error in execute_insert_stmt for Instance Dimension '||sqlerrm||get_time;
128 write_to_log_file_n(g_status_message);
129 g_status:=false;
130 End;
131
132 procedure return_with_success is
133 begin
134 --write_to_push_log(true);
135 commit;
136 End;
137
138 procedure return_with_error is
139 begin
140 rollback;
141 --write_to_push_log(false);
142 End;
143
144 procedure call_main_collection(errbuf out NOCOPY varchar2, retcode out NOCOPY varchar2) is
145 Begin
146 if g_debug then
147 write_to_log_file_n('In call_main_collection, dim name is '||g_dim_name);
148 end if;
149 EDW_ALL_COLLECT.Collect_Dimension(errbuf,retcode,g_dim_name);
150 Exception when others then
151 g_status_message:='Error in call_main_collection for Instance Dimension '||sqlerrm||get_time;
152 write_to_log_file_n(g_status_message);
153 g_status:=false;
154 End;
155
156 procedure write_to_push_log(p_flag boolean) is
157 l_stmt varchar2(10000);
158 begin
159 if g_debug then
160 if p_flag then
161 write_to_log_file_n('In write_to_push_log, TRUE');
162 else
163 write_to_log_file_n('In write_to_push_log, FALSE');
164 end if;
165 end if;
166 End;--procedure write_to_publish_log
167
168
169 procedure write_to_log_file(p_message varchar2) is
170 begin
171 EDW_OWB_COLLECTION_UTIL.write_to_log_file(p_message);
172 Exception when others then
173 null;
174 End;
175
176 procedure write_to_log_file_n(p_message varchar2) is
177 begin
178 write_to_log_file(' ');
179 write_to_log_file(p_message);
180 Exception when others then
181 null;
182 End;
183
184 function get_time return varchar2 is
185 begin
186 return ' oo'||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');
187 Exception when others then
188 write_to_log_file_n('Exception in get_time '||sqlerrm);
189 return null;
190 End;
191
192
193 END EDW_INSTANCE_COLLECT;