[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.13 2011/06/02 07:03:17 mpmurali 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 /* bug#8367504 -- nallkuma
31 cursor get_schema_name is
32 select fnd_profile.value('MSD_DEM_SCHEMA')
33 from dual;
34 */
35
36 l_schema_name VARCHAR2(100);
37 l_entity_name VARCHAR2(100);
38 lv_sql_stmt VARCHAR2(4000);
39 lv_error_text VARCHAR2(1000);
40 l_dest_table VARCHAR2(100); -- syenamar Bug#6459492
41 l_dem_version number;
42
43 cursor c_get_instance_type is
44 select instance_type
45 from msc_apps_instances
46 where instance_id = p_instance_id;
47
48 l_instance_type number;
49
50 BEGIN
51
52 LOG_MESSAGE ('***************** Entered in the procedure - LAUNCH **********');
53
54 open c_get_instance_type;
55 fetch c_get_instance_type into l_instance_type;
56 close c_get_instance_type;
57
58 -- bug#8367471(fp for bug#6087699) nallkuma
59 if l_instance_type in (1,2,4) then
60 msd_dem_push_setup_parameters.push_setup_parameters(ERRBUF, RETCODE, p_instance_id, '-999');
61 if retcode = -1 then
62 msd_dem_common_utilities.log_message('Push Setup Parameters Failed');
63 msd_dem_common_utilities.log_debug('Push Setup Parameters Failed');
64 return;
65 end if;
66 else
67 /* Calling push_legacy_setup_parameters() procedure in case of pure legacy instance */
68 msd_dem_push_setup_parameters.push_legacy_setup_parameters(ERRBUF, RETCODE, p_instance_id);
69 if retcode = -1 then
70 msd_dem_common_utilities.log_message('Push Legacy Setup Parameters Failed');
71 msd_dem_common_utilities.log_debug('Push Legacy Setup Parameters Failed');
72 return;
73 end if;
74 end if;
75
76
77 /* bug#8367504 -- nallkuma
78 open get_schema_name;
79 fetch get_schema_name into l_schema_name;
80 close get_schema_name;
81
82 if l_schema_name is not null then
83 l_schema_name := l_schema_name;
84 else
85 l_schema_name := 'DMTRA_TEMPLATE';
86 end if;
87 */
88
89 -- bug#8367504 nallkuma
90 l_schema_name := substr(msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE')
91 , 1
92 , instr(msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE'), '.')-1) ;
93
94 LOG_MESSAGE('Fetched the schema name as : '||l_schema_name);
95
96 l_dem_version := fnd_profile.value('MSD_DEM_VERSION');
97
98 /*if l_dem_version is null then
99 LOG_MESSAGE('MSD_DEM_VERSION profile is null.');
100 return;
101 end if;*/
102
103
104 BEGIN
105
106 l_entity_name := 'ITEMS';
107
108 v_sql_stmt := 01;
109 lv_sql_stmt :=
110 ' UPDATE '||l_schema_name||'.'||' t_src_sales_tmpl t1 '
111 ||' SET t1.ebs_item_sr_pk = ( SELECT t2.sr_inventory_item_id '
112 ||' FROM msc_system_items t2 '
113 ||' WHERE t2.plan_id = -1 '
114 ||' AND t2.sr_instance_id = :p_instance_id '
115 ||' AND t2.item_name = t1.dm_item_code '
116 ||' AND t2.organization_id = ( SELECT t3.sr_tp_id '
117 ||' FROM msc_trading_partners t3 '
118 ||' WHERE t3.partner_type = 3 '
119 ||' AND t3.organization_code = t1.dm_org_code '
120 ||' AND t3.sr_instance_id = :p_instance_id '
121 ||' )'
122 ||' )'
123 ||' WHERE NVL(t1.ebs_item_sr_pk,'||''''||NULL_CHAR||''''||') '
124 ||' = '||''''||NULL_CHAR||'''';
125
126 IF v_debug THEN
127 LOG_MESSAGE(lv_sql_stmt);
128 END IF;
129
130 EXECUTE IMMEDIATE lv_sql_stmt
131 USING p_instance_id,
132 p_instance_id;
133
134
135 EXCEPTION
136 WHEN OTHERS THEN
137 LOG_MESSAGE ('An error occured while generating Source Keys for Entity - '||l_entity_name);
138
139 lv_error_text := substr('MSD_DEM_PROCESS_SALES_DATA.LAUNCH '||'('
140 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
141 LOG_MESSAGE(lv_error_text);
142
143 ERRBUF := lv_error_text;
144 RETCODE := G_WARNING;
145 END;
146
147
148 BEGIN
149 l_entity_name := 'ORGANIZATIONS';
150
151 v_sql_stmt := 02;
152 lv_sql_stmt :=
153 ' UPDATE '||l_schema_name||'.'||' t_src_sales_tmpl t1 '
154 ||' SET t1.ebs_org_sr_pk = ( SELECT t2.sr_tp_id '
155 ||' FROM msc_trading_partners t2 '
156 ||' WHERE t2.sr_instance_id = :p_instance_id '
157 ||' AND t2.organization_code = t1.dm_org_code '
158 ||' AND partner_type = 3 '
159 ||' AND rownum = 1 '
160 ||' )'
161 ||' WHERE NVL(t1.ebs_org_sr_pk,'||''''||NULL_CHAR||''''||') '
162 ||' = '||''''||NULL_CHAR||'''';
163
164 IF v_debug THEN
165 LOG_MESSAGE(lv_sql_stmt);
166 END IF;
167
168 EXECUTE IMMEDIATE lv_sql_stmt
169 USING p_instance_id;
170
171
172 EXCEPTION
173 WHEN OTHERS THEN
174 LOG_MESSAGE ('An error occured while generating Source Keys for Entity - '||l_entity_name);
175
176 lv_error_text := substr('MSD_DEM_PROCESS_SALES_DATA.LAUNCH '||'('
177 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
178 LOG_MESSAGE(lv_error_text);
179
180 ERRBUF := lv_error_text;
181 RETCODE := G_WARNING;
182 END;
183
184
185 BEGIN
186 l_entity_name := 'SITES';
187
188 v_sql_stmt := 03;
189 lv_sql_stmt :=
190 ' UPDATE '||l_schema_name||'.'||' t_src_sales_tmpl t1 '
191 ||' SET t1.ebs_site_sr_pk = ( SELECT t2.sr_tp_site_id '
192 ||' FROM msc_trading_partners t4, msc_tp_id_lid t5, msc_trading_partner_sites t3, msc_tp_site_id_lid t2 '
193 ||' WHERE t4.partner_type = 2 '
194 ||' AND t4.partner_name = substr(t1.dm_site_code,1,instr(t1.dm_site_code,'':'') - 1 ) '
195 ||' AND t5.partner_type = 2 '
196 ||' AND t5.sr_instance_id = ' || to_char(p_instance_id)
197 ||' AND t5.tp_id = t4.partner_id '
198 ||' AND nvl(t5.sr_cust_account_number, ''###'') = nvl(substr(t1.dm_site_code, instr(t1.dm_site_code, '':'') + 1, '
199 || ' instr(t1.dm_site_code, '':'', 1, 2) - instr(t1.dm_site_code, '':'', 1, 1) - 1), ''###'') '
200 ||' AND t3.partner_id = t4.partner_id '
201 ||' 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) '
202 ||' AND nvl(t3.operating_unit_name, ''###'') = nvl(substr(t1.dm_site_code , instr(t1.dm_site_code, '':'', 1, 3) + 1, '
203 ||' decode (instr(t1.dm_site_code , '':'', 1, 4), 0, length(t1.dm_site_code ) + 1, instr(t1.dm_site_code , '':'', 1, 4)) - instr(t1.dm_site_code , '':'', 1, 3) - 1), ''###'') ' ;
204 /* added this code for the bug#6871484 on 22-aug-2008 - nallkuma */
205 if l_instance_type in (1,2,4) then
206 lv_sql_stmt := lv_sql_stmt ||' AND t3.tp_site_code =''SHIP_TO'' ' ;
207 end if;
208
209 lv_sql_stmt := lv_sql_stmt ||'AND t2.tp_site_id = t3.partner_site_id '
210 ||' AND t2.partner_type = 2 '
211 ||' AND t2.sr_instance_id = :p_instance_id '
212 ||' AND nvl(t2.sr_company_id,-1) = -1 '
213 ||' AND nvl(t2.sr_cust_acct_id, -1) = nvl(t5.sr_tp_id, -1) '
214 ||' AND rownum = 1 '
215 ||' )'
216 ||' WHERE NVL(t1.ebs_site_sr_pk,'||''''||NULL_CHAR||''''||') '
217 ||' = '||''''||NULL_CHAR||'''';
218
219 IF v_debug THEN
220 LOG_MESSAGE(lv_sql_stmt);
221 END IF;
222
223 EXECUTE IMMEDIATE lv_sql_stmt
224 USING p_instance_id;
225
226 lv_sql_stmt :=
227 ' UPDATE '||l_schema_name||'.'||' t_src_sales_tmpl t1 '
228 ||' SET t1.ebs_site_sr_pk = msd_dem_sr_util.get_null_pk '
229 ||' WHERE dm_site_code = msd_dem_sr_util.get_null_code';
230
231 IF v_debug THEN
232 LOG_MESSAGE(lv_sql_stmt);
233 END IF;
234
235 EXECUTE IMMEDIATE lv_sql_stmt;
236
237 IF (msd_dem_common_utilities.is_use_new_site_format <> 0)
238 THEN
239
240 lv_sql_stmt :=
241 ' UPDATE '||l_schema_name||'.'||'t_src_sales_tmpl t1 '
242 ||' SET t1.dm_site_code = ''' || to_char(p_instance_id) || '::'' || t1.ebs_site_sr_pk '
243 ||' WHERE NVL(ebs_site_sr_pk,0) > 0 ';
244
245 IF v_debug THEN
246 LOG_MESSAGE(lv_sql_stmt);
247 END IF;
248
249 EXECUTE IMMEDIATE lv_sql_stmt;
250
251 END IF;
252
253
254 EXCEPTION
255 WHEN OTHERS THEN
256 LOG_MESSAGE ('An error occured while generating Source Keys for Entity - '||l_entity_name);
257
258 lv_error_text := substr('MSD_DEM_PROCESS_SALES_DATA.LAUNCH '||'('
259 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
260 LOG_MESSAGE(lv_error_text);
261
262 ERRBUF := lv_error_text;
263 RETCODE := G_WARNING;
264 END;
265
266
267
268 BEGIN
269 l_entity_name := 'SALES_CHANNELS';
270
271 v_sql_stmt := 04;
272
273 lv_sql_stmt :=
274 ' UPDATE '||l_schema_name||'.'||' t_src_sales_tmpl t1 '
275 ||' 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'
276 ||' WHERE t1.ebs_sales_channel_code is null';
277
278 IF v_debug THEN
279 LOG_MESSAGE(lv_sql_stmt);
280 END IF;
281
282 EXECUTE IMMEDIATE lv_sql_stmt;
283
284
285 lv_sql_stmt :=
286 ' UPDATE '||l_schema_name||'.'||' t_src_sales_tmpl t1 '
287 ||' SET t1.ebs_sales_channel_sr_pk = ( SELECT t2.sales_channel '
288 ||' FROM msc_sales_channel t2 '
289 ||' WHERE t2.meaning = t1.ebs_sales_channel_code '
290 ||' AND sr_instance_id = :p_instance_id '
291 ||' AND rownum = 1 '
292 ||' )'
293 ||' WHERE NVL(t1.ebs_sales_channel_sr_pk,'||''''||NULL_CHAR||''''||') '
294 ||' = '||''''||NULL_CHAR||'''';
295
296 IF v_debug THEN
297 LOG_MESSAGE(lv_sql_stmt);
298 END IF;
299
300 EXECUTE IMMEDIATE lv_sql_stmt
301 USING p_instance_id;
302
303
304
305 EXCEPTION
306 WHEN OTHERS THEN
307 LOG_MESSAGE ('An error occured while generating Source Keys for Entity - '||l_entity_name);
308
309 lv_error_text := substr('MSD_DEM_PROCESS_SALES_DATA.LAUNCH '||'('
310 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
311 LOG_MESSAGE(lv_error_text);
312
313 ERRBUF := lv_error_text;
314 RETCODE := G_WARNING;
315 END;
316
317 BEGIN
318 l_entity_name := 'DEMAND_CLASSES';
319
320 v_sql_stmt := 05;
321
322
323 lv_sql_stmt :=
324 ' UPDATE '||l_schema_name||'.'||' t_src_sales_tmpl t1 '
325 ||' 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'
326 ||' WHERE t1.ebs_demand_class_code is null';
327
328 IF v_debug THEN
329 LOG_MESSAGE(lv_sql_stmt);
330 END IF;
331
332 EXECUTE IMMEDIATE lv_sql_stmt;
333
334
335
336 lv_sql_stmt :=
337 ' UPDATE '||l_schema_name||'.'||' t_src_sales_tmpl t1 '
338 ||' SET t1.ebs_demand_class_sr_pk = ( SELECT t2.demand_class '
339 ||' FROM msc_demand_classes t2 '
340 ||' WHERE t2.meaning = t1.ebs_demand_class_code '
341 ||' AND sr_instance_id = :p_instance_id '
342 ||' AND rownum = 1 '
343 ||' )'
344 ||' WHERE NVL(t1.ebs_demand_class_sr_pk,'||''''||NULL_CHAR||''''||') '
345 ||' = '||''''||NULL_CHAR||'''';
346
347
348
349 IF v_debug THEN
350 LOG_MESSAGE(lv_sql_stmt);
351 END IF;
352
353 EXECUTE IMMEDIATE lv_sql_stmt
354 USING p_instance_id;
355
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 LOG_MESSAGE ('An error occured while generating Source Keys for Entity - '||l_entity_name);
360
361 lv_error_text := substr('MSD_DEM_PROCESS_SALES_DATA.LAUNCH '||'('
362 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
363 LOG_MESSAGE(lv_error_text);
364
365 ERRBUF := lv_error_text;
366 RETCODE := G_WARNING;
367 END;
368
369
370 BEGIN
371 l_entity_name := 'BASE MODEL';
372
373 v_sql_stmt := 06;
374 lv_sql_stmt :=
375 ' UPDATE '||l_schema_name||'.'||'t_src_sales_tmpl t1 '
376 ||' SET t1.ebs_base_model_sr_pk = ( SELECT t2.sr_inventory_item_id '
377 ||' FROM msc_system_items t2 '
378 ||' WHERE t2.plan_id = -1 '
379 ||' AND t2.sr_instance_id = :p_instance_id '
380 ||' AND t2.item_name = t1.ebs_base_model_code '
381 ||' AND t2.organization_id = ( SELECT t3.sr_tp_id '
382 ||' FROM msc_trading_partners t3 '
383 ||' WHERE t3.partner_type = 3 '
384 ||' AND t3.organization_code = t1.dm_org_code '
385 ||' AND t3.sr_instance_id = :p_instance_id '
386 ||' )'
387 ||' )'
388 ||' WHERE t1.ebs_base_model_code IS NOT NULL ';
389
390 IF v_debug THEN
391 LOG_MESSAGE(lv_sql_stmt);
392 END IF;
393
394 EXECUTE IMMEDIATE lv_sql_stmt
395 USING p_instance_id,
396 p_instance_id;
397
398
399 EXCEPTION
400 WHEN OTHERS THEN
401 LOG_MESSAGE ('An error occured while generating Source Keys for Entity - '||l_entity_name);
402
403 lv_error_text := substr('MSD_DEM_PROCESS_SALES_DATA.LAUNCH '||'('
404 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
405 LOG_MESSAGE(lv_error_text);
406
407 ERRBUF := lv_error_text;
408 RETCODE := G_WARNING;
409 END;
410
411
412 commit;
413
414 /* Insert dummy rows in the staging table for new items */
415 msd_dem_common_utilities.log_debug ('Begin Insert dummy rows for new items into the staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
416
417 msd_dem_collect_history_data.insert_dummy_rows (
418 errbuf,
419 retcode,
420 'T_SRC_SALES_TMPL',
421 p_instance_id);
422
423 IF (retcode = 1)
424 THEN
425 msd_dem_common_utilities.log_message ('Warning(1): msd_dem_process_sales_data.launch - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
426 msd_dem_common_utilities.log_message ('Error while inserting dummy rows into the sales staging table for new items. ');
427 END IF;
428
429
430 msd_dem_common_utilities.log_debug ('End Insert dummy rows for new items into the staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
431
432
433 --Final commit
434 commit;
435
436 /* Get the sales staging table name */ -- syenamar Bug#6459492
437 l_dest_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE');
438
439 IF (l_dest_table is NULL)
440 THEN
441 RETCODE := -1;
442 ERRBUF := 'Unable to find the sales staging tables.';
443 msd_dem_common_utilities.log_message ('MSD_DEM_PROCESS_SALES_DATA.LAUNCH - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
444 msd_dem_common_utilities.log_message (ERRBUF);
445 RETURN;
446 END IF;
447
448 IF (l_schema_name <> 'MSD' ) then -- BUG#8367504 nallkuma
449
450 msd_dem_common_utilities.log_debug ('Begin: Delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
451
452 /* Truncate the sales staging table */
453 msd_dem_common_utilities.log_debug ('Deleting data from ERR table - ' || l_dest_table ||'_err');
454 lv_sql_stmt := 'TRUNCATE TABLE ' || l_dest_table ||'_err';
455 EXECUTE IMMEDIATE lv_sql_stmt;
456
457 msd_dem_common_utilities.log_debug ('End: Delete from ERR table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS')); -- syenamar
458
459 End if;
460
461 LOG_MESSAGE ('***************** Exiting from the procedure - LAUNCH **********');
462
463 EXCEPTION
464 WHEN OTHERS THEN
465 LOG_MESSAGE ('Error generating Source Keys');
466
467 lv_error_text := substr('MSD_DEM_PROCESS_SALES_DATA.LAUNCH '||'('
468 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
469 LOG_MESSAGE(lv_error_text);
470
471 ERRBUF := lv_error_text;
472 RETCODE := G_ERROR;
473
474 END LAUNCH;
475
476 END MSD_DEM_PROCESS_SALES_DATA;