[Home] [Help]
PACKAGE BODY: APPS.GMP_DESPATCH_LOAD_RPT_PKG
Source
1 PACKAGE BODY GMP_DESPATCH_LOAD_RPT_PKG as
2 /* $Header: GMPRDESB.pls 120.6 2008/01/17 06:04:11 vpedarla ship $ */
3
4 G_ret_code BOOLEAN;
5 G_forg VARCHAR2(240);
6 G_torg VARCHAR2(240);
7 G_fres VARCHAR2(240);
8 G_tores VARCHAR2(240);
9 G_fres_instance NUMBER;
10 G_tores_instance NUMBER;
11 G_start_date DATE;
12 G_to_date DATE;
13 G_log_text VARCHAR2(1000);
14 G_template VARCHAR2(100);
15 G_template_locale VARCHAR2(6);
16 G_inst VARCHAR2(3);
17 G_plan VARCHAR2(10);
18
19 f_org NUMBER;
20 t_org NUMBER;
21 inst_id NUMBER;
22 plan_id NUMBER;
23 f_res NUMBER;
24 t_res NUMBER;
25
26 resdisp_rpt_id NUMBER;
27
28 PROCEDURE LOG_MESSAGE(pBUFF IN VARCHAR2);
29
30 /*============================================================================+
31 | |
32 | PROCEDURE NAME print_res_desp |
33 | |
34 | DESCRIPTION Procedure to submit the request for dispatch report |
35 | |
36 | CREATED BY Sowmya - 28-Jun-2005 |
37 | |
38 +============================================================================*/
39 PROCEDURE print_res_desp
40 ( errbuf OUT NOCOPY VARCHAR2,
41 retcode OUT NOCOPY VARCHAR2,
42 V_forg IN NUMBER,
43 V_torg IN NUMBER,
44 V_fres IN VARCHAR2,
45 V_tores IN VARCHAR2,
46 V_fres_instance IN NUMBER,
47 V_tores_instance IN NUMBER,
48 V_start_date IN VARCHAR2,
49 V_to_date IN VARCHAR2,
50 V_template IN VARCHAR2,
51 V_template_locale IN VARCHAR2
52 ) IS
53
54 BEGIN
55
56 --Initialising outpout values
57 retcode := -1;
58
59 f_org := V_forg;
60 t_org := V_torg;
61
62 --copying the parameter values into the global variables
63 G_forg := gmp_despatch_load_rpt_pkg.get_orgn_code(V_forg);
64 G_torg := gmp_despatch_load_rpt_pkg.get_orgn_code(V_torg);
65 G_fres := V_fres;
66 G_tores := V_tores;
67 G_fres_instance := V_fres_instance;
68 G_tores_instance := V_tores_instance;
69 G_start_date := to_date(V_start_date,'yyyy/mm/dd hh24:mi:ss');
70 G_to_date := to_date(V_to_date,'yyyy/mm/dd hh24:mi:ss');
71 G_template := V_template;
72 G_template_locale := V_template_locale;
73
74
75 --generate the xml and insert into the gtmp table.
76 gme_res_generate_xml;
77
78 IF G_ret_code THEN
79 retcode := 0;
80 log_message('Successfully Completed!!');
81 END IF;
82
83 log_message('Return code = '|| retcode);
84
85 END print_res_desp;
86 /*============================================================================+
87 | |
88 | PROCEDURE NAME print_res_load |
89 | |
90 | DESCRIPTION Procedure to submit the request for load report |
91 | |
92 | CREATED BY Sowmya - 28-Jun-2005 |
93 | |
94 +============================================================================*/
95 PROCEDURE print_res_load
96 ( errbuf OUT NOCOPY VARCHAR2,
97 retcode OUT NOCOPY VARCHAR2,
98 V_inst_id IN NUMBER,
99 V_orgid IN NUMBER,
100 V_plan_id IN NUMBER,
101 V_forg IN NUMBER,
102 V_torg IN NUMBER,
103 V_fres IN NUMBER,
104 V_tores IN NUMBER,
105 V_fres_instance IN NUMBER,
106 V_tores_instance IN NUMBER,
107 V_start_date IN VARCHAR2,
108 V_to_date IN VARCHAR2,
109 V_template IN VARCHAR2,
110 V_template_locale IN VARCHAR2
111 )IS
112 BEGIN
113 --Initialising outpout values
114 retcode := -1;
115
116 f_org := V_forg;
117 t_org := V_torg;
118 inst_id := V_inst_id;
119 plan_id := V_plan_id;
120
121 log_message('Inside the procedure print_res_load');
122
123 --copying the parameter values into the global variables
124 G_forg := gmp_despatch_load_rpt_pkg.get_orgn_code(V_forg);
125 G_torg := gmp_despatch_load_rpt_pkg.get_orgn_code(V_torg);
126 G_inst := gmp_despatch_load_rpt_pkg.get_inst_code(V_inst_id);
127 G_plan := gmp_despatch_load_rpt_pkg.get_plan_code(V_plan_id);
128 IF V_fres IS NOT NULL THEN
129 G_fres := gmp_despatch_load_rpt_pkg.get_resource_desc(V_fres);
130 f_res := to_number(V_fres);
131 ELSE
132 G_fres := NULL;
133 f_res := NULL;
134 END IF;
135
136 IF V_tores IS NOT NULL THEN
137 G_tores := gmp_despatch_load_rpt_pkg.get_resource_desc(V_tores);
138 t_res := to_number(V_tores);
139 ELSE
140 G_tores := NULL;
141 t_res := NULL;
142 END IF;
143
144 G_fres_instance := V_fres_instance;
145 G_tores_instance := V_tores_instance;
146 G_start_date := to_date(V_start_date,'yyyy/mm/dd hh24:mi:ss');
147 G_to_date := to_date(V_to_date,'yyyy/mm/dd hh24:mi:ss');
148 G_template := V_template;
149 G_template_locale := V_template_locale;
150
151
152 --generate the xml and insert into the gtmp table.
153 aps_res_generate_xml;
154
155 IF G_ret_code THEN
156 retcode := 0;
157 log_message('Successfully Completed!!');
158 END IF;
159
160 log_message('Return code = '|| retcode);
161
162 END print_res_load;
163 /*============================================================================+
164 | |
165 | PROCEDURE NAME gme_res_generate_xml |
166 | |
167 | DESCRIPTION Procedure used to Generate XML for GME resource batch |
168 | report. |
169 | |
170 | CREATED BY Sowmya - 28-Jun-2005 |
171 | |
172 +============================================================================*/
173 PROCEDURE gme_res_generate_xml IS
174
175 qryCtx DBMS_XMLGEN.ctxHandle;
176 result1 CLOB;
177 x_stmt VARCHAR2(10000);
178 seq_stmt VARCHAR2(100);
179
180 BEGIN
181
182 seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
183
184 EXECUTE IMMEDIATE seq_stmt INTO resdisp_rpt_id ;
185
186 x_stmt := ' SELECT ' ||
187 ''''||G_forg||''''||' forg, ' ||
188 ''''||G_torg||''''||' torg, ' ||
189 ''''||G_fres||''''||' fres, ' ||
190 ''''||G_tores||''''||' tores, ' ||
191 ''''||G_fres_instance||''''||' fres_instance, ' ||
192 ''''||G_tores_instance||''''||' tores_instance, ' ||
193 ''''||G_start_date||''''||' fdate, ' ||
194 ''''||G_to_date||''''||' tdate, ' ||
195 ' CURSOR( ' ||
196 ' SELECT ' ||
197 ' gmp_despatch_load_rpt_pkg.get_orgn_code(mp.organization_id) organzation_code, ' ||
198 ' crd.resources resource_desc, ' ||
199 ' crd.usage_uom uom, '||
200 ' gi.instance_number instance_number, ' ||
201 ' CURSOR( ' ||
202 ' SELECT h.batch_no batch_no, ' ||
203 ' gs.batchstep_no operation, ' ||
204 ' gsa.activity activity, ' ||
205 ' to_char(t.start_date, '||''''||'dd-mon-yy hh24:mi:ss'||''''||') start_date, '||
206 ' to_char(t.end_date, '||''''||'dd-mon-yy hh24:mi:ss'||''''||') end_date, '||
207 ' msi.segment1 Item, ' ||
208 ' round(t.resource_usage,3) resource_usage ' ||
209 ' FROM ' ||
210 ' gme_batch_header h , '||
211 ' gme_batch_steps gs , '||
212 ' gme_material_details gmt, '||
213 ' gme_batch_step_activities gsa, '||
214 ' gme_batch_step_resources gsr, '||
215 ' gme_batch_step_items gsi, '||
216 ' gme_resource_txns t, '||
217 ' mtl_system_items msi, '||
218 ' gmp_resource_instances gri '||
219 ' WHERE h.organization_id = mp.organization_id '||
220 ' and h.batch_id = gs.batch_id '||
221 ' and h.batch_status in (1,2) '||
222 ' and gs.batch_id = gsa.batch_id '||
223 ' and gs.step_status in (1,2) '||
224 ' and gs.batchstep_id = gsa.batchstep_id '||
225 ' and gmt.batch_id = gs.batch_id '||
226 ' and gmt.batch_id = gsi.batch_id (+) '||
227 ' and gmt.material_detail_id = gsi.material_detail_id (+) '||
228 ' and gsi.batchstep_id = gs.batchstep_id (+) '||
229 ' and gsr.batch_id = gsa.batch_id '||
230 ' and gsr.batchstep_id = gsa.batchstep_id '||
231 ' and gsr.batchstep_activity_id = gsa.batchstep_activity_id '||
232 ' and gsr.resources = crd.resources '||
233 ' and gmt.organization_id = msi.organization_id '||
234 ' and gmt.inventory_item_id = msi.inventory_item_id '||
235 ' and gmt.batch_id = t.doc_id '||
236 ' and gsr.batchstep_resource_id = t.line_id '||
237 ' and t.completed_ind = 0 '||
238 ' and t.delete_mark = 0 '||
239 ' and t.start_date >= nvl( to_date('||''''||to_char(G_start_date, 'dd-mm-yy hh24:mi:ss' )||''''||','||''''||'dd-mm-yy hh24:mi:ss'||''''||'), t.start_date) '||
240 ' and t.end_date <= nvl( to_date('||''''||to_char(G_to_date, 'dd-mm-yy hh24:mi:ss')||''''||','||''''||'dd-mm-yy hh24:mi:ss'||''''||'), t.end_date) '||
241 ' and t.instance_id = gri.instance_id (+) '||
242 ' and nvl(gri.inactive_ind,0) = 0 '||
243 ' and gri.resource_id (+) = gi.resource_id '||
244 ' and gri.instance_id (+) = gi.instance_id '||
245 ' order by 1,2,4 '||
246 ' ) DETAIL ' ||
247 ' FROM ' ||
248 ' mtl_parameters mp, ' ||
249 ' hr_organization_units hr, ' ||
250 ' cr_rsrc_dtl crd, ' ||
251 ' gmp_resource_instances gi '||
252 ' WHERE mp.organization_id = hr.organization_id '||
253 ' and mp.organization_id between '||''''||f_org||''''||' AND '||''''||t_org||''''||
254 ' and crd.resources between nvl('||''''||G_fres||''''||', crd.resources) and nvl('||''''||G_tores||''''||', crd.resources)'||
255 ' and mp.process_enabled_flag = '||''''||'Y'||''''||
256 ' and nvl(hr.date_to,sysdate) >= sysdate '||
257 ' and crd.organization_id = mp.organization_id '||
258 ' and crd.resource_id = gi.resource_id (+) ';
259
260 IF ( G_fres_instance IS NOT NULL ) OR ( G_tores_instance IS NOT NULL ) THEN
261 x_stmt := x_stmt ||' and gi.instance_number between nvl('||''''||G_fres_instance||''''||', gi.instance_number) and nvl('||''''||G_tores_instance||''''||', gi.instance_number)';
262 END IF;
263
264 x_stmt := x_stmt ||' order by 1,2,4 '||
265 ' ) HEADER ' ||
266 ' FROM DUAL ';
267
268 qryctx := dbms_xmlgen.newcontext(x_stmt);
269 result1 := DBMS_XMLGEN.getXML(qryCtx);
270
271 INSERT INTO GMP_RESDISP_XML_TEMP (RESDISP_XML_RPT_ID,RESULT) VALUES (resdisp_rpt_id, result1);
272 DBMS_XMLGEN.closeContext(qryCtx);
273
274 COMMIT;
275
276 resdl_generate_output(resdisp_rpt_id);
277
278 G_ret_code := TRUE;
279
280 EXCEPTION
281 WHEN OTHERS THEN
282 LOG_MESSAGE ('Exception in procedure gme_res_generate_xml :'||SQLERRM);
283 G_ret_code := FALSE;
284
285 END gme_res_generate_xml;
286 /*============================================================================+
287 | |
288 | PROCEDURE NAME aps_res_generate_xml |
289 | |
290 | DESCRIPTION Procedure used to Generate XML for APS resource batch |
291 | report. |
292 | |
293 | CREATED BY Sowmya - 28-Jun-2005 |
294 | |
295 +============================================================================*/
296 PROCEDURE aps_res_generate_xml IS
297
298 qryCtx DBMS_XMLGEN.ctxHandle;
299 result1 CLOB;
300 x_stmt1 VARCHAR2(20000);
301 seq_stmt VARCHAR2(100);
302 BEGIN
303
304 seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
305
306 EXECUTE IMMEDIATE seq_stmt INTO resdisp_rpt_id ;
307
308 x_stmt1 := ' SELECT ' ||
309 ''''||G_inst||''''||' Inst, ' ||
310 ''''||G_plan||''''||' Plan, ' ||
311 ''''||G_forg||''''||' forg, ' ||
312 ''''||G_torg||''''||' torg, ' ||
313 ''''||G_fres||''''||' fres, ' ||
314 ''''||G_tores||''''||' tores, ' ||
315 ''''||G_fres_instance||''''||' fres_instance, ' ||
316 ''''||G_tores_instance||''''||' tores_instance, ' ||
317 ''''||G_start_date||''''||' fdate, ' ||
318 ''''||G_to_date||''''||' tdate, ' ||
319 ' CURSOR( ' ||
320 ' SELECT ' ||
321 ' gmp_despatch_load_rpt_pkg.get_orgn_code(dr.organization_id) organization_code, '||
322 ' substr(dr.resource_description,1,15) resource_desc,'||
323 ' dr.unit_of_measure UOM, '||
324 ' mri.serial_number Instance_number,'||
325 ' CURSOR( ' ||
326 ' SELECT ms.transaction_id Order_Id, '||
327 ' decode(rr.routing_sequence_id,null,null,rt.routing_comment) routing_desc, '||
328 ' decode(rr.operation_sequence_id,null,null,mr.operation_seq_num) oprseq_no, '||
329 ' to_char(rr.start_date, '||''''||'dd-mon-yy hh24:mi:ss'||''''||') St_date, '||
330 ' to_char(rr.end_date, '||''''||'dd-mon-yy hh24:mi:ss'||''''||') Edate, '||
331 ' msi.item_name Item, '||
332 ' round(rr.resource_hours,3) resource_usage '||
333 ' FROM ' ||
334 ' msc_resource_requirements rr, '||
335 ' msc_supplies ms, '||
336 ' msc_system_items msi, '||
337 ' msc_operation_resources mor, '||
338 ' msc_routing_operations mr, '||
339 ' msc_routings rt '||
340 ' WHERE rr.sr_instance_id = ms.sr_instance_id '||
341 ' AND rr.sr_instance_id = msi.sr_instance_id '||
342 ' AND rr.sr_instance_id = mor.sr_instance_id '||
343 ' AND rr.sr_instance_id = mr.sr_instance_id '||
344 ' AND rr.sr_instance_id = rt.sr_instance_id '||
345 ' AND rr.sr_instance_id = dr.sr_instance_id '||
346 ' AND rr.plan_id = ms.plan_id '||
347 ' AND rr.plan_id = msi.plan_id '||
348 ' AND rr.plan_id = mor.plan_id '||
349 ' AND rr.plan_id = mr.plan_id '||
350 ' AND rr.plan_id = rt.plan_id '||
351 ' AND rr.plan_id = dr.plan_id '||
352 ' AND rr.organization_id = ms.organization_id '||
353 ' AND rr.organization_id = msi.organization_id '||
354 ' AND rr.organization_id = mor.organization_id '||
355 -- ' AND rr.organization_id = mr.organization_id '||
356 ' AND rr.organization_id = rt.organization_id '||
357 ' AND rr.organization_id = dr.organization_id '||
358 ' AND rr.supply_id = ms.transaction_id '||
359 ' AND ms.inventory_item_id = msi.inventory_item_id '||
360 ' AND rr.resource_id = mor.resource_id '||
361 ' AND rr.resource_id = dr.resource_id '||
362 ' AND rr.start_date >= nvl( to_date('||''''||to_char(G_start_date, 'dd-mm-yy hh24:mi:ss' )||''''||','||''''||'dd-mm-yy hh24:mi:ss'||''''||'), rr.start_date) '||
363 ' AND rr.end_date <= nvl( to_date('||''''||to_char(G_to_date, 'dd-mm-yy hh24:mi:ss')||''''||','||''''||'dd-mm-yy hh24:mi:ss'||''''||'), rr.end_date) '||
364 ' AND rr.resource_hours > 0 '||
365 ' AND rr.parent_id <> 2 ' ||
366 ' AND mor.routing_sequence_id = mr.routing_sequence_id '||
367 ' AND mor.operation_sequence_id = mr.operation_sequence_id '||
368 ' AND mr.routing_sequence_id = rt.routing_sequence_id '||
369 ' AND ms.order_type IN ( 5,17 ) '||
370 ' UNION '||
371 ' SELECT md.disposition_id Order_Id, '||
372 ' decode(rr.routing_sequence_id,null,null,rt.routing_comment) routing_desc, '||
373 ' decode(rr.operation_sequence_id,null,null,mr.operation_seq_num) oprseq_no, '||
374 ' to_char(rr.start_date, '||''''||'dd-mon-yy hh24:mi:ss'||''''||') St_date, '||
375 ' to_char(rr.end_date, '||''''||'dd-mon-yy hh24:mi:ss'||''''||') Edate, '||
376 ' msi.item_name Item, '||
377 ' round(rr.resource_hours,3) resource_usage '||
378 ' FROM ' ||
379 ' msc_resource_requirements rr, '||
380 ' msc_demands md, '||
381 ' msc_system_items msi, '||
382 ' msc_operation_resources mor, '||
383 ' msc_routing_operations mr, '||
384 ' msc_routings rt '||
385 ' WHERE rr.sr_instance_id = md.sr_instance_id '||
386 ' AND rr.sr_instance_id = msi.sr_instance_id '||
387 ' AND rr.sr_instance_id = mor.sr_instance_id '||
388 ' AND rr.sr_instance_id = mr.sr_instance_id '||
389 ' AND rr.sr_instance_id = rt.sr_instance_id '||
390 ' AND rr.sr_instance_id = dr.sr_instance_id '||
391 ' AND rr.plan_id = md.plan_id '||
392 ' AND rr.plan_id = msi.plan_id '||
393 ' AND rr.plan_id = mor.plan_id '||
394 ' AND rr.plan_id = mr.plan_id '||
395 ' AND rr.plan_id = rt.plan_id '||
396 ' AND rr.plan_id = dr.plan_id '||
397 ' AND rr.organization_id = md.organization_id '||
398 ' AND rr.organization_id = msi.organization_id '||
399 ' AND rr.organization_id = mor.organization_id '||
400 -- ' AND rr.organization_id = mr.organization_id '||
401 ' AND rr.organization_id = rt.organization_id '||
402 ' AND rr.organization_id = dr.organization_id '||
403 ' AND rr.supply_id = md.disposition_id '||
404 ' AND md.inventory_item_id = msi.inventory_item_id '||
405 ' AND rr.resource_id = mor.resource_id '||
406 ' AND rr.resource_id = dr.resource_id '||
407 ' AND rr.start_date >= nvl( to_date('||''''||to_char(G_start_date, 'dd-mm-yy hh24:mi:ss' )||''''||','||''''||'dd-mm-yy hh24:mi:ss'||''''||'), rr.start_date) '||
408 ' AND rr.end_date <= nvl( to_date('||''''||to_char(G_to_date, 'dd-mm-yy hh24:mi:ss')||''''||','||''''||'dd-mm-yy hh24:mi:ss'||''''||'), rr.end_date) '||
409 ' AND rr.resource_hours > 0 '||
410 ' AND rr.parent_id <> 2 ' ||
411 ' AND mor.routing_sequence_id = mr.routing_sequence_id '||
412 ' AND mor.operation_sequence_id = mr.operation_sequence_id '||
413 ' AND mr.routing_sequence_id = rt.routing_sequence_id '||
417 ' FROM ' ||
414 ' AND md.origination_type = 1 '||
415 ' ORDER BY 1,4 '||
416 ' ) DETAIL ' ||
418 ' msc_dept_res_instances mri, '||
419 ' msc_department_resources dr '||
420 ' WHERE dr.sr_instance_id = '|| inst_id ||
421 ' AND dr.sr_instance_id = mri.sr_instance_id (+) '||
422 ' AND dr.plan_id = '|| plan_id ||
423 ' AND dr.plan_id = mri.plan_id (+)'||
424 ' AND dr.organization_id between '||''''||f_org||''''||' AND '||''''||t_org||''''||
425 ' AND dr.organization_id = mri.organization_id (+) '||
426 ' AND dr.resource_id BETWEEN nvl('||''''||f_res||''''||', dr.resource_id) and nvl('||''''||t_res||''''||', dr.resource_id)'||
427 ' AND dr.resource_id = mri.resource_id (+) ';
428
429 IF ( G_fres_instance IS NOT NULL ) OR ( G_tores_instance IS NOT NULL ) THEN
430 x_stmt1 := x_stmt1 ||' and mri.res_instance_id between nvl('||''''||G_fres_instance||''''||', mri.res_instance_id) and nvl('||''''||G_tores_instance||''''||', mri.res_instance_id)';
431 END IF;
432
433 x_stmt1 := x_stmt1 ||' ORDER BY 1,2,4'||
434 ' ) HEADER ' ||
435 ' FROM DUAL ';
436
437 qryctx := dbms_xmlgen.newcontext(x_stmt1);
438 result1 := DBMS_XMLGEN.getXML(qryCtx);
439
440 INSERT INTO GMP_RESDISP_XML_TEMP (RESDISP_XML_RPT_ID,RESULT) VALUES (resdisp_rpt_id, result1);
441 DBMS_XMLGEN.closeContext(qryCtx);
442
443
444 COMMIT;
445
446 resdl_generate_output(resdisp_rpt_id);
447
448 G_ret_code := TRUE;
449
450 EXCEPTION
451 WHEN OTHERS THEN
452 LOG_MESSAGE ('Exception in procedure aps_res_generate_xml :'||SQLERRM);
453 G_ret_code := FALSE;
454
455 END aps_res_generate_xml;
456 /*============================================================================+
457 | |
458 | FUNCTION NAME get_orgn_code |
459 | |
460 | DESCRIPTION Function to get the organization code |
461 | |
462 | CREATED BY Sowmya - 28-Jun-2005 |
463 | |
464 +============================================================================*/
465 FUNCTION get_orgn_code( p_orgn_id IN NUMBER) RETURN VARCHAR2 IS
466
467 TYPE ref_cursor_typ IS REF CURSOR;
468 cur_orgn_code ref_cursor_typ ;
469
470 l_orgn_code VARCHAR2(3);
471
472 BEGIN
473 OPEN cur_orgn_code FOR
474 SELECT organization_code FROM mtl_parameters
475 WHERE organization_id = p_orgn_id;
476
477 FETCH cur_orgn_code INTO l_orgn_code ;
478
479 CLOSE cur_orgn_code ;
480
481 RETURN l_orgn_code;
482
483 END get_orgn_code;
484
485 /*============================================================================+
486 | |
487 | FUNCTION NAME get_inst_code |
488 | |
489 | DESCRIPTION Function to get the Instance code |
490 | |
491 | CREATED BY Sowmya - 28-Jun-2005 |
492 | |
493 +============================================================================*/
494 FUNCTION get_inst_code( p_inst_id IN NUMBER) RETURN VARCHAR2 IS
495
496 TYPE ref_cursor_typ IS REF CURSOR;
497 cur_inst_code ref_cursor_typ ;
498
499 l_inst_code VARCHAR2(3);
500
501 BEGIN
502 OPEN cur_inst_code FOR
503 SELECT instance_code FROM msc_apps_instances
504 WHERE instance_id = p_inst_id;
505
506 FETCH cur_inst_code INTO l_inst_code ;
507
508 CLOSE cur_inst_code ;
509
510 RETURN l_inst_code;
511
512 END get_inst_code;
513
514 /*============================================================================+
515 | |
516 | FUNCTION NAME get_plan_code |
517 | |
518 | DESCRIPTION Function to get the Plan name |
519 | |
520 | CREATED BY Sowmya - 28-Jun-2005 |
521 | |
522 +============================================================================*/
523 FUNCTION get_plan_code( p_plan_id IN NUMBER) RETURN VARCHAR2 IS
524
525 TYPE ref_cursor_typ IS REF CURSOR;
526 cur_plan_code ref_cursor_typ ;
527
528 l_plan_name VARCHAR2(10);
529
530 BEGIN
531 OPEN cur_plan_code FOR
532 SELECT compile_designator FROM msc_plans
533 WHERE plan_id = p_plan_id;
534
535 FETCH cur_plan_code INTO l_plan_name ;
539 RETURN l_plan_name;
536
537 CLOSE cur_plan_code ;
538
540
541 END get_plan_code;
542
543 /*============================================================================+
544 | |
545 | FUNCTION NAME get_resource_desc |
546 | |
547 | DESCRIPTION Function to get the Resources |
548 | |
549 | CREATED BY Sowmya - 28-Jun-2005 |
550 | |
551 +============================================================================*/
552 FUNCTION get_resource_desc( p_resource_id IN NUMBER) RETURN VARCHAR2 IS
553
554 TYPE ref_cursor_typ IS REF CURSOR;
555 cur_res_desc ref_cursor_typ ;
556
557 l_resource VARCHAR2(16);
558
559 BEGIN
560 OPEN cur_res_desc FOR
561 SELECT distinct substr(resource_description,1,16) FROM msc_department_resources
562 WHERE plan_id = plan_id
563 AND resource_id = p_resource_id
564 AND sr_instance_id = inst_id;
565
566 FETCH cur_res_desc INTO l_resource ;
567
568 CLOSE cur_res_desc ;
569
570 RETURN l_resource;
571
572 END get_resource_desc;
573
574 /*============================================================================+
575 | |
576 | PROCEDURE NAME get_orgn_code |
577 | |
578 | DESCRIPTION Procedure for logging messages in log file |
579 | |
580 | CREATED BY Sowmya - 28-Jun-2005 |
581 | |
582 +============================================================================*/
583 PROCEDURE LOG_MESSAGE(pBUFF IN VARCHAR2) IS
584
585 BEGIN
586
587 IF fnd_global.conc_request_id > 0 THEN
588 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
589 ELSE
590 NULL;
591 END IF;
592
593 EXCEPTION
594 WHEN OTHERS THEN
595 RETURN;
596
597 END LOG_MESSAGE;
598
599 /* ***************************************************************
600 * NAME
601 * PROCEDURE - resdl_generate_output
602 *
603 * DESCRIPTION
604 * Procedure used generate the final output.
605 *
606 *************************************************************** */
607
608 PROCEDURE resdl_generate_output (
609 p_sequence_num IN NUMBER
610 )
611 IS
612
613 l_conc_id NUMBER;
614 l_req_id NUMBER;
615 l_phase VARCHAR2(20);
616 l_status_code VARCHAR2(20);
617 l_dev_phase VARCHAR2(20);
618 l_dev_status VARCHAR2(20);
619 l_message VARCHAR2(20);
620 l_status BOOLEAN;
621
622
623 BEGIN
624
625 l_conc_id := FND_REQUEST.SUBMIT_REQUEST('GMP','GMPRESDP','', '',FALSE,
626 p_sequence_num, chr(0),'','','','','','','','','','','',
627 '','','','','','','','','','','','','','','',
628 '','','','','','','','','','',
629 '','','','','','','','','','',
630 '','','','','','','','','','',
631 '','','','','','','','','','',
632 '','','','','','','','','','',
633 '','','','','','','','','','',
634 '','','','','','','','','','');
635
636 IF l_conc_id = 0 THEN
637 G_log_text := FND_MESSAGE.GET;
638 FND_FILE.PUT_LINE ( FND_FILE.LOG,G_log_text);
639 ELSE
640 COMMIT ;
641 END IF;
642
643 IF l_conc_id <> 0 THEN
644
645 l_status := fnd_concurrent.WAIT_FOR_REQUEST
646 (
647 REQUEST_ID => l_conc_id,
648 INTERVAL => 30,
649 MAX_WAIT => 900,
650 PHASE => l_phase,
651 STATUS => l_status_code,
652 DEV_PHASE => l_dev_phase,
653 DEV_STATUS => l_dev_status,
654 MESSAGE => l_message
655 );
656
657 DELETE FROM GMP_RESDISP_XML_TEMP WHERE RESDISP_XML_RPT_ID = p_sequence_num;
658
659 /* Bug: 6609251 Vpedarla added a NULL parameters for the submition of the FND request for XDOREPPB */
660 l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB','', '',FALSE,'',
661 l_conc_id,554,G_template,
662 G_template_locale,'N','','','','','','','','',
663 '','','','','','','','','','','','','','','',
664 '','','','','','','','','','',
665 '','','','','','','','','','',
666 '','','','','','','','','','',
667 '','','','','','','','','','',
668 '','','','','','','','','','',
669 '','','','','','','','','','',
670 '','','','','','','','','','');
671 END IF;
672
673 EXCEPTION
674 WHEN OTHERS THEN
675 log_message('Exception in procedure resdl_generate_output '||SQLERRM);
676 END resdl_generate_output;
677
678 /* ***************************************************************
679 * NAME
680 * PROCEDURE - rd_xml_transfer
681 *
682 * DESCRIPTION
683 * Procedure used provide the XML as output of the concurrent program.
684 *
685 *************************************************************** */
686
687 PROCEDURE rd_xml_transfer (
688 errbuf OUT NOCOPY VARCHAR2,
689 retcode OUT NOCOPY VARCHAR2,
690 p_sequence_num IN NUMBER
691 )IS
692
693 l_file CLOB;
694 file_varchar2 VARCHAR2(200);
695 l_len NUMBER;
696 l_limit NUMBER;
697
698 BEGIN
699
700 SELECT RESULT INTO l_file
701 FROM GMP_RESDISP_XML_TEMP
702 WHERE RESDISP_XML_RPT_ID = p_sequence_num;
703
704 l_limit:= 1;
705
706 l_len := DBMS_LOB.GETLENGTH (l_file);
707
708 log_message (' l_len - '||l_len);
709
710 LOOP
711 IF l_len > l_limit THEN
712 file_varchar2 := DBMS_LOB.SUBSTR (l_file,100,l_limit);
713 FND_FILE.PUT(FND_FILE.OUTPUT,file_varchar2);
714 FND_FILE.PUT(FND_FILE.LOG, file_varchar2);
715 file_varchar2 := NULL;
716 l_limit:= l_limit + 100;
717 ELSE
718 file_varchar2 := DBMS_LOB.SUBSTR (l_file,100,l_limit);
719 FND_FILE.PUT(FND_FILE.OUTPUT, file_varchar2);
720 FND_FILE.PUT(FND_FILE.LOG,file_varchar2);
721 file_varchar2 := NULL;
722 EXIT;
723 END IF;
724 END LOOP;
725 EXCEPTION
726 WHEN OTHERS THEN
727 log_message ('Exception in procedure rd_xml_transfer '||SQLERRM);
728 END rd_xml_transfer;
729
730 END GMP_DESPATCH_LOAD_RPT_PKG;