[Home] [Help]
PACKAGE BODY: APPS.MSD_COLLECT_TIME_DATA
Source
1 PACKAGE BODY MSD_COLLECT_TIME_DATA AS
2 /* $Header: msdctimb.pls 120.2 2008/03/07 11:34:59 lannapra ship $ */
3
4 /*
5 /* Public Procedures */
6
7 procedure collect_time_data(
8 errbuf OUT NOCOPY VARCHAR2,
9 retcode OUT NOCOPY VARCHAR2,
10 p_instance_id IN NUMBER,
11 p_calendar_type_id IN VARCHAR2,
12 p_calendar_code IN VARCHAR2,
13 p_from_date IN VARCHAR2,
14 p_to_date IN VARCHAR2) IS
15
16 x_instance_id varchar2(40);
17 x_dblink varchar2(128);
18 x_retcode number;
19 x_direct_load_profile boolean;
20 x_source_table VARCHAR2(50) ;
21 x_dest_table varchar2(50) ;
22 x_sql_stmt varchar2(4000);
23 x_from_date DATE;
24 x_to_date DATE;
25 l_calendar_count number := 0;
26
27 /* OPM Comment By Rajesh Patangya */
28 o_source_table VARCHAR2(50) ;
29 o_dblink varchar2(128);
30 o_icode varchar2(128);
31 o_retcode number;
32 o_instance_type number;
33 o_dgmt number;
34 o_apps_ver number;
35 calendar_code_and_type varchar2(10);
36
37 Begin
38
39 retcode :=0;
40
41 msd_common_utilities.get_db_link(p_instance_id, x_dblink, x_retcode);
42 if (x_retcode = -1) and (p_calendar_type_id <> MSD_COMMON_UTILITIES.GREGORIAN_CALENDAR) then
43 retcode :=-1;
44 errbuf := 'Error while getting db_link';
45 return;
46 end if;
47
48 /* OPM Comment By Rajesh Patangya */
49 msd_common_utilities.get_inst_info(p_instance_id, o_dblink, o_icode,
50 o_apps_ver, o_dgmt, o_instance_type, o_retcode) ;
51 if (o_retcode = -1) and (p_calendar_type_id <> MSD_COMMON_UTILITIES.GREGORIAN_CALENDAR) then
52 retcode :=-1;
53 errbuf := 'Error while getting instance_info';
54 return;
55 end if;
56
57 if (p_calendar_type_id = MSD_COMMON_UTILITIES.GREGORIAN_CALENDAR) then
58 x_source_table := null ;
59 calendar_code_and_type := 'DISCRETE' ;
60 elsif (p_calendar_type_id = MSD_COMMON_UTILITIES.MANUFACTURING_CALENDAR) then
61 x_source_table := MSD_COMMON_UTILITIES.MFG_TIME_SOURCE_TABLE || x_dblink ;
62
63 /* OPM Comment By Rajesh Patangya */
64 o_source_table := MSD_COMMON_UTILITIES.OPM_MFG_TIME_SOURCE_TABLE || x_dblink ;
65 elsif (p_calendar_type_id = MSD_COMMON_UTILITIES.FISCAL_CALENDAR) then
66 x_source_table := MSD_COMMON_UTILITIES.FISCAL_TIME_SOURCE_TABLE|| x_dblink ;
67 calendar_code_and_type := 'DISCRETE' ;
68 end if ;
69
70 --dbms_output.put_line(x_source_table) ;
71
72 x_direct_load_profile := (fnd_profile.value('MSD_ONE_STEP_COLLECTION')='Y');
73
74 if (x_direct_load_profile) then
75 x_dest_table := MSD_COMMON_UTILITIES.TIME_FACT_TABLE ;
76 else
77 x_dest_table := MSD_COMMON_UTILITIES.TIME_STAGING_TABLE ;
78 end if;
79
80 --dbms_output.put_line(x_dest_table) ;
81 --dbms_output.put_line(p_from_date) ;
82 --dbms_output.put_line(p_to_date) ;
83
84 x_from_date := FND_DATE.canonical_to_date(p_from_date);
85 x_to_date := FND_DATE.canonical_to_date(p_to_date);
86
87 if (p_calendar_type_id = MSD_COMMON_UTILITIES.GREGORIAN_CALENDAR) then
88 if (p_from_date is null) then
89 x_from_date := to_date('01/01/1980','DD/MM/RRRR');
90 end if;
91 if (p_to_date is null) then
92 x_to_date := to_date('31/12/2020','DD/MM/RRRR');
93 end if;
94 end if;
95
96 /*
97 x_from_date := to_date(p_from_date, 'DD-MON-RRRR') ;
98 x_to_date := to_date(p_to_date, 'DD-MON-RRRR') ;
99 */
100
101 --dbms_output.put_line(x_from_date) ;
102 --dbms_output.put_line(x_to_date) ;
103
104 if ( (p_calendar_type_id = MSD_COMMON_UTILITIES.MANUFACTURING_CALENDAR) or
105 (p_calendar_type_id = MSD_COMMON_UTILITIES.FISCAL_CALENDAR)) then
106
107 /* Bug# 4620927 */
108 -- if o_instance_type = 1 Then /* DIS instance */
109 if o_instance_type = 1 OR o_apps_ver >= 4 Then /* DIS 11i instance OR R12 instance of any type */
110
111 -- sudesh Bug # 3899742
112 begin
113 x_sql_stmt := 'select 1 from dual where exists( select null from '||x_source_table||' where calendar_code = nvl(:p_calendar_code, calendar_code)) ';
114 EXECUTE IMMEDIATE x_sql_stmt INTO l_calendar_count USING p_calendar_code;
115 exception
116 when no_data_found then
117 l_calendar_count := 0;
118 end;
119
120 -- sudesh Bug # 3899742 x_sql_stmt := 'select count(1) ' || 'from ' || x_source_table ||
121 -- sudesh Bug # 3899742 ' where calendar_code = nvl(:p_calendar_code, calendar_code) ' ;
122 -- sudesh Bug # 3899742 EXECUTE IMMEDIATE x_sql_stmt INTO l_calendar_count USING p_calendar_code;
123
124 calendar_code_and_type := 'DISCRETE' ;
125
126 elsif o_apps_ver = 3 AND o_instance_type = 2 then /* OPM instance */
127 if p_calendar_type_id = MSD_COMMON_UTILITIES.FISCAL_CALENDAR then
128 -- sudesh Bug # 3899742
129 begin
130
131 x_sql_stmt := 'select 1 from dual where exists( select null from '||x_source_table||' where calendar_code = nvl(:p_calendar_code, calendar_code)) ';
132 EXECUTE IMMEDIATE x_sql_stmt INTO l_calendar_count USING p_calendar_code;
133 exception
134 when no_data_found then
135 l_calendar_count := 0;
136 end;
137
138 -- sudesh Bug # 3899742 x_sql_stmt := 'select count(1) ' || 'from ' || x_source_table ||
139 -- sudesh Bug # 3899742 ' where calendar_code = nvl(:p_calendar_code, calendar_code) ' ;
140
141 calendar_code_and_type := 'DISCRETE' ;
142 else
143
144 -- sudesh Bug # 3899742
145 begin
146
147 x_sql_stmt := 'select 1 from dual where exists( select null from '||o_source_table||' where calendar_code = nvl(:p_calendar_code, calendar_code)) ';
148 EXECUTE IMMEDIATE x_sql_stmt INTO l_calendar_count USING p_calendar_code;
149 exception
150 when no_data_found then
151 l_calendar_count := 0;
152 end;
153
154 -- sudesh Bug # 3899742 x_sql_stmt := 'select count(1) ' || 'from ' || o_source_table ||
155 -- sudesh Bug # 3899742 ' where calendar_code = nvl(:p_calendar_code, calendar_code) ' ;
156
157 calendar_code_and_type := 'PROCESS' ;
158 end if ;
159 -- sudesh Bug # 3899742 EXECUTE IMMEDIATE x_sql_stmt INTO l_calendar_count USING p_calendar_code;
160
161 /* OPM-DIS instance */
162 elsif o_apps_ver = 3 AND o_instance_type = 4 Then
163
164 -- sudesh Bug # 3899742
165 begin
166
167 x_sql_stmt := 'select 1 from dual where exists( select null from '||x_source_table||' where calendar_code = nvl(:p_calendar_code, calendar_code)) ';
168 EXECUTE IMMEDIATE x_sql_stmt INTO l_calendar_count USING p_calendar_code;
169 exception
170 when no_data_found then
171 l_calendar_count := 0;
172 end;
173
174 -- sudesh Bug # 3899742 x_sql_stmt := 'select count(1) ' || 'from ' || x_source_table ||
175 -- sudesh Bug # 3899742 ' where calendar_code = nvl(:p_calendar_code, calendar_code)' ;
176 -- sudesh Bug # 3899742 EXECUTE IMMEDIATE x_sql_stmt INTO l_calendar_count USING p_calendar_code;
177
178 calendar_code_and_type := 'DISCRETE' ;
179
180
181 if ((l_calendar_count = 0) AND (p_calendar_type_id = MSD_COMMON_UTILITIES.MANUFACTURING_CALENDAR)) then
182 x_sql_stmt := NULL ;
183
184 -- sudesh Bug # 3899742
185 begin
186
187 x_sql_stmt := 'select 1 from dual where exists( select null from '||o_source_table||' where calendar_code = nvl(:p_calendar_code, calendar_code)) ';
188 EXECUTE IMMEDIATE x_sql_stmt INTO l_calendar_count USING p_calendar_code;
189 exception
190 when no_data_found then
191 l_calendar_count := 0;
192 end;
193
194 -- sudesh Bug # 3899742 x_sql_stmt := 'select count(1) ' ||
195 -- sudesh Bug # 3899742 'from ' || o_source_table ||
196 -- sudesh Bug # 3899742 ' where calendar_code = nvl(:p_calendar_code, calendar_code)' ;
197 -- sudesh Bug # 3899742 EXECUTE IMMEDIATE x_sql_stmt INTO l_calendar_count USING p_calendar_code;
198
199 calendar_code_and_type := 'PROCESS' ;
200 end if;
201
202 end if ;
203
204 if (l_calendar_count = 0) then
205 retcode :=-1;
206 errbuf := 'The specified calendar code is not defined in the source instance or no data was retrieved.';
207 return;
208 end if;
209
210 end if ;
211
212 /* OPM Comment By Rajesh Patangya */
213 /* If condition will take care of */
214 /* Grogerian calendar, as calendar_code is null, */
215 /* if calendar_code is null, in case of manufacturing or fiscal, */
216 /* if calendar_code is duplicate in both Discrete and process, */
217 /* only if condition will be fired */
218
219 if (calendar_code_and_type = 'DISCRETE') then
220 MSD_TRANSLATE_TIME_DATA.translate_time_data(
221 errbuf => errbuf,
222 retcode => retcode,
223 p_source_table => x_source_table,
224 p_dest_table => x_dest_table,
225 p_instance_id => p_instance_id,
226 p_calendar_type_id => to_number(p_calendar_type_id),
227 p_calendar_code => replace(p_calendar_code, '''', ''''''),
228 p_from_date => x_from_date,
229 p_to_date => x_to_date ) ;
230
231 if retcode <> 0 then
232 errbuf := ' In MSD Call for Manufacturing/Fiscal Time';
233 return;
234 end if ;
235
236
237 /* else part will take care of */
238 /* if calendar_code is a OPM calendar */
239
240 elsif calendar_code_and_type = 'PROCESS'then
241
242 MSD_TRANSLATE_TIME_DATA.translate_time_data(
243 errbuf => errbuf,
244 retcode => retcode,
245 p_source_table => o_source_table,
246 p_dest_table => x_dest_table,
247 p_instance_id => p_instance_id,
248 p_calendar_type_id => to_number(p_calendar_type_id),
249 p_calendar_code => replace(p_calendar_code, '''', ''''''),
250 p_from_date => x_from_date,
251 p_to_date => x_to_date ) ;
252
253 if retcode <> 0 then
254 errbuf := ' In OPM Call for Manufacturing Time';
255 return;
256 end if ;
257 end if ;
258
259 /* Added by esubrama */
260 MSD_ANALYZE_TABLES.analyze_table(x_dest_table,null);
261
262 exception
263
264 when others then
265
266 errbuf := substr(SQLERRM,1,150);
267 retcode := -1 ;
268
269
270 End collect_time_data ;
271
272 END MSD_COLLECT_TIME_DATA ;