[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_PROCESS_SALES_DATA
Source
1 PACKAGE BODY MSD_DEM_PROCESS_SALES_DATA AS -- body
2 /* $Header: MSDDEMSDB.pls 120.3.12010000.2 2008/09/05 09:30:11 nallkuma ship $ */
3
4 NULL_CHAR CONSTANT VARCHAR2(6) := '-23453';
5
6 G_ERROR CONSTANT NUMBER := 2;
7 G_WARNING CONSTANT NUMBER := 1;
8
9 v_sql_stmt PLS_INTEGER;
10 v_debug BOOLEAN := nvl(FND_PROFILE.VALUE('MRP_DEBUG'),'N') = 'Y';
11
12 PROCEDURE LOG_MESSAGE(p_error_text IN VARCHAR2)
13 IS
14 BEGIN
15
16 IF fnd_global.conc_request_id > 0 THEN
17 FND_FILE.PUT_LINE( FND_FILE.LOG, p_error_text);
18 END IF;
19
20 EXCEPTION
21 WHEN OTHERS THEN
22 RETURN;
23 END LOG_MESSAGE;
24
25 PROCEDURE LAUNCH( ERRBUF OUT NOCOPY VARCHAR2,
26 RETCODE OUT NOCOPY NUMBER,
27 p_instance_id IN NUMBER )
28 IS
29
30 cursor get_schema_name is
31 select fnd_profile.value('MSD_DEM_SCHEMA')
32 from dual;
33
34 l_schema_name VARCHAR2(100);
35 l_entity_name VARCHAR2(100);
36 lv_sql_stmt VARCHAR2(4000);
37 lv_error_text VARCHAR2(1000);
38 l_dest_table VARCHAR2(100); -- syenamar Bug#6459492
39
40 cursor c_get_instance_type is
41 select instance_type
42 from msc_apps_instances
43 where instance_id = p_instance_id;
44
45 l_instance_type number;
46
47 BEGIN
48
49 LOG_MESSAGE ('***************** Entered in the procedure - LAUNCH **********');
50
51 open c_get_instance_type;
52 fetch c_get_instance_type into l_instance_type;
53 close c_get_instance_type;
54
55 if l_instance_type in (1,2,4) then
56 msd_dem_push_setup_parameters.push_setup_parameters(ERRBUF, RETCODE, p_instance_id, '-999');
57 if retcode = -1 then
58 msd_dem_common_utilities.log_message('Push Setup Parameters Failed');
59 msd_dem_common_utilities.log_debug('Push Setup Parameters Failed');
60 return;
61 end if;
62 else
63 LOG_MESSAGE('Push Setup Parameters will not be called since this is a pure legacy instance');
64 end if;
65
66
67
68 open get_schema_name;
69 fetch get_schema_name into l_schema_name;
70 close get_schema_name;
71
72 if l_schema_name is not null then
73 l_schema_name := l_schema_name;
74 else
75 l_schema_name := 'DMTRA_TEMPLATE';
76 end if;
77
78 LOG_MESSAGE('Fetched the schema name from profile MSD_DEM_SCHEMA as '||l_schema_name);
79
80 BEGIN
81 l_entity_name := 'ITEMS';
82
83 v_sql_stmt := 01;
84 lv_sql_stmt :=
85 ' UPDATE '||l_schema_name||'.'||' t_src_sales_tmpl t1 '
86 ||' SET t1.ebs_item_sr_pk = ( SELECT t2.sr_inventory_item_id '
87 ||' FROM msc_system_items t2 '
88 ||' WHERE t2.plan_id = -1 '
89 ||' AND t2.sr_instance_id = :p_instance_id '
90 ||' AND t2.item_name = t1.dm_item_code '
91 ||' AND t2.organization_id = ( SELECT t3.sr_tp_id '
92 ||' FROM msc_trading_partners t3 '
93 ||' WHERE t3.partner_type = 3 '
94 ||' AND t3.organization_code = t1.dm_org_code '
95 ||' AND t3.sr_instance_id = :p_instance_id '
96 ||' )'
97 ||' )'
98 ||' WHERE NVL(t1.ebs_item_sr_pk,'||''''||NULL_CHAR||''''||') '
99 ||' = '||''''||NULL_CHAR||'''';
100
101 IF v_debug THEN
102 LOG_MESSAGE(lv_sql_stmt);
103 END IF;
104
105 EXECUTE IMMEDIATE lv_sql_stmt
106 USING p_instance_id,
107 p_instance_id;
108
109
110 EXCEPTION
111 WHEN OTHERS THEN
112 LOG_MESSAGE ('An error occured while generating Source Keys for Entity - '||l_entity_name);
113
114 lv_error_text := substr('MSD_DEM_PROCESS_SALES_DATA.LAUNCH '||'('
115 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
116 LOG_MESSAGE(lv_error_text);
117
118 ERRBUF := lv_error_text;
119 RETCODE := G_WARNING;
120 END;
121
122
123 BEGIN
124 l_entity_name := 'ORGANIZATIONS';
125
126 v_sql_stmt := 02;
127 lv_sql_stmt :=
128 ' UPDATE '||l_schema_name||'.'||' t_src_sales_tmpl t1 '
129 ||' SET t1.ebs_org_sr_pk = ( SELECT t2.sr_tp_id '
130 ||' FROM msc_trading_partners t2 '
131 ||' WHERE t2.sr_instance_id = :p_instance_id '
132 ||' AND t2.organization_code = t1.dm_org_code '
133 ||' AND partner_type = 3 '
134 ||' AND rownum = 1 '
135 ||' )'
136 ||' WHERE NVL(t1.ebs_org_sr_pk,'||''''||NULL_CHAR||''''||') '
137 ||' = '||''''||NULL_CHAR||'''';
138
139 IF v_debug THEN
140 LOG_MESSAGE(lv_sql_stmt);
141 END IF;
142
143 EXECUTE IMMEDIATE lv_sql_stmt
144 USING p_instance_id;
145
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 LOG_MESSAGE ('An error occured while generating Source Keys for Entity - '||l_entity_name);
150
151 lv_error_text := substr('MSD_DEM_PROCESS_SALES_DATA.LAUNCH '||'('
152 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
153 LOG_MESSAGE(lv_error_text);
154
155 ERRBUF := lv_error_text;
156 RETCODE := G_WARNING;
157 END;
158
159
160 BEGIN
161 l_entity_name := 'SITES';
162
163 v_sql_stmt := 03;
164 lv_sql_stmt :=
165 ' UPDATE '||l_schema_name||'.'||' t_src_sales_tmpl t1 '
166 ||' SET t1.ebs_site_sr_pk = ( SELECT t2.sr_tp_site_id '
167 ||' FROM msc_tp_site_id_lid t2,msc_trading_partner_sites t3,msc_trading_partners t4 '
168 ||' WHERE t4.partner_type = 2 '
169 ||' AND t4.partner_name = substr(t1.dm_site_code,1,instr(t1.dm_site_code,'':'') - 1 ) '
170 ||' AND t3.partner_id = t4.partner_id '
171 ||' AND t3.location = substr(t1.dm_site_code, instr(t1.dm_site_code,'':'',1,2)+1, instr(t1.dm_site_code,'':'',1,3)-instr(t1.dm_site_code,'':'',1,2)-1) ' ;
172
173 /* added this code for the FP bug#7357426 on 05-sep-2008 - nallkuma */
174 if l_instance_type in (1,2,4) then
175 lv_sql_stmt := lv_sql_stmt ||' AND t3.tp_site_code =''SHIP_TO'' ' ;
176 end if;
177
178 lv_sql_stmt := lv_sql_stmt ||'AND t2.tp_site_id = t3.partner_site_id '
179 ||' AND t2.partner_type = 2 '
180 ||' AND t2.sr_instance_id = :p_instance_id '
181 ||' AND nvl(t2.sr_company_id,-1) = -1 '
182 ||' AND rownum = 1 '
183 ||' )'
184 ||' WHERE NVL(t1.ebs_site_sr_pk,'||''''||NULL_CHAR||''''||') '
185 ||' = '||''''||NULL_CHAR||'''';
186
187 IF v_debug THEN
188 LOG_MESSAGE(lv_sql_stmt);
189 END IF;
190
191 EXECUTE IMMEDIATE lv_sql_stmt
192 USING p_instance_id;
193
194 lv_sql_stmt :=
195 ' UPDATE '||l_schema_name||'.'||' t_src_sales_tmpl t1 '
196 ||' SET t1.ebs_site_sr_pk = msd_dem_sr_util.get_null_pk '
197 ||' WHERE dm_site_code = msd_dem_sr_util.get_null_code';
198
199 IF v_debug THEN
200 LOG_MESSAGE(lv_sql_stmt);
201 END IF;
202
203 EXECUTE IMMEDIATE lv_sql_stmt;
204
205
206 EXCEPTION
207 WHEN OTHERS THEN
208 LOG_MESSAGE ('An error occured while generating Source Keys for Entity - '||l_entity_name);
209
210 lv_error_text := substr('MSD_DEM_PROCESS_SALES_DATA.LAUNCH '||'('
211 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
212 LOG_MESSAGE(lv_error_text);
213
214 ERRBUF := lv_error_text;
215 RETCODE := G_WARNING;
216 END;
217
218
219 BEGIN
220 l_entity_name := 'SALES_CHANNELS';
221
222 v_sql_stmt := 04;
223
224 lv_sql_stmt :=
225 ' UPDATE '||l_schema_name||'.'||' t_src_sales_tmpl t1 '
226 ||' SET t1.ebs_sales_channel_sr_pk = to_char(msd_dem_sr_util.get_null_pk), t1.ebs_sales_channel_code = msd_dem_sr_util.get_null_code'
227 ||' WHERE t1.ebs_sales_channel_code is null';
228
229 IF v_debug THEN
230 LOG_MESSAGE(lv_sql_stmt);
231 END IF;
232
233 EXECUTE IMMEDIATE lv_sql_stmt;
234
235
236 lv_sql_stmt :=
237 ' UPDATE '||l_schema_name||'.'||' t_src_sales_tmpl t1 '
238 ||' SET t1.ebs_sales_channel_sr_pk = ( SELECT t2.sales_channel '
239 ||' FROM msc_sales_channel t2 '
240 ||' WHERE t2.meaning = t1.ebs_sales_channel_code '
241 ||' AND sr_instance_id = :p_instance_id '
242 ||' AND rownum = 1 '
243 ||' )'
244 ||' WHERE NVL(t1.ebs_sales_channel_sr_pk,'||''''||NULL_CHAR||''''||') '
245 ||' = '||''''||NULL_CHAR||'''';
246
247 IF v_debug THEN
248 LOG_MESSAGE(lv_sql_stmt);
249 END IF;
250
251 EXECUTE IMMEDIATE lv_sql_stmt
252 USING p_instance_id;
253
254
255
256 EXCEPTION
257 WHEN OTHERS THEN
258 LOG_MESSAGE ('An error occured while generating Source Keys for Entity - '||l_entity_name);
259
260 lv_error_text := substr('MSD_DEM_PROCESS_SALES_DATA.LAUNCH '||'('
261 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
262 LOG_MESSAGE(lv_error_text);
263
264 ERRBUF := lv_error_text;
265 RETCODE := G_WARNING;
266 END;
267
268 BEGIN
269 l_entity_name := 'DEMAND_CLASSES';
270
271 v_sql_stmt := 05;
272
273
274 lv_sql_stmt :=
275 ' UPDATE '||l_schema_name||'.'||' t_src_sales_tmpl t1 '
276 ||' SET t1.ebs_demand_class_sr_pk = to_char(msd_dem_sr_util.get_null_pk), t1.ebs_demand_class_code = msd_dem_sr_util.get_null_code'
277 ||' WHERE t1.ebs_demand_class_code is null';
278
279 IF v_debug THEN
280 LOG_MESSAGE(lv_sql_stmt);
281 END IF;
282
283 EXECUTE IMMEDIATE lv_sql_stmt;
284
285
286
287 lv_sql_stmt :=
288 ' UPDATE '||l_schema_name||'.'||' t_src_sales_tmpl t1 '
289 ||' SET t1.ebs_demand_class_sr_pk = ( SELECT t2.demand_class '
290 ||' FROM msc_demand_classes t2 '
291 ||' WHERE t2.meaning = t1.ebs_demand_class_code '
292 ||' AND sr_instance_id = :p_instance_id '
293 ||' AND rownum = 1 '
294 ||' )'
295 ||' WHERE NVL(t1.ebs_demand_class_sr_pk,'||''''||NULL_CHAR||''''||') '
296 ||' = '||''''||NULL_CHAR||'''';
297
298
299
300 IF v_debug THEN
301 LOG_MESSAGE(lv_sql_stmt);
302 END IF;
303
304 EXECUTE IMMEDIATE lv_sql_stmt
305 USING p_instance_id;
306
307
308 EXCEPTION
309 WHEN OTHERS THEN
310 LOG_MESSAGE ('An error occured while generating Source Keys for Entity - '||l_entity_name);
311
312 lv_error_text := substr('MSD_DEM_PROCESS_SALES_DATA.LAUNCH '||'('
313 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
314 LOG_MESSAGE(lv_error_text);
315
316 ERRBUF := lv_error_text;
317 RETCODE := G_WARNING;
318 END;
319
320 --Final commit
321 commit;
322
323 /* Get the sales staging table name */ -- syenamar Bug#6459492
324 l_dest_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE');
325
326 IF (l_dest_table is NULL)
327 THEN
328 RETCODE := -1;
329 ERRBUF := 'Unable to find the sales staging tables.';
330 msd_dem_common_utilities.log_message ('MSD_DEM_PROCESS_SALES_DATA.LAUNCH - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
331 msd_dem_common_utilities.log_message (ERRBUF);
332 RETURN;
333 END IF;
334
335 msd_dem_common_utilities.log_debug ('Begin: Delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
336
337 /* Truncate the sales staging table */
338 msd_dem_common_utilities.log_debug ('Deleting data from ERR table - ' || l_dest_table ||'_err');
339 lv_sql_stmt := 'TRUNCATE TABLE ' || l_dest_table ||'_err';
340 EXECUTE IMMEDIATE lv_sql_stmt;
341
342 msd_dem_common_utilities.log_debug ('End: Delete from ERR table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS')); -- syenamar
343
344 LOG_MESSAGE ('***************** Exiting from the procedure - LAUNCH **********');
345
346 EXCEPTION
347 WHEN OTHERS THEN
348 LOG_MESSAGE ('Error generating Source Keys');
349
350 lv_error_text := substr('MSD_DEM_PROCESS_SALES_DATA.LAUNCH '||'('
351 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
352 LOG_MESSAGE(lv_error_text);
353
354 ERRBUF := lv_error_text;
355 RETCODE := G_ERROR;
356
357 END LAUNCH;
358
359
360
361
362 END MSD_DEM_PROCESS_SALES_DATA;