[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.13 2012/03/29 08:35:53 maychen 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 v_dblink VARCHAR2(128);
26
27 resdisp_rpt_id NUMBER;
28
29 PROCEDURE LOG_MESSAGE(pBUFF IN VARCHAR2);
30
31 /*============================================================================+
32 | |
33 | PROCEDURE NAME print_res_desp |
34 | |
35 | DESCRIPTION Procedure to submit the request for dispatch report |
36 | |
37 | CREATED BY Sowmya - 28-Jun-2005 |
38 | |
39 +============================================================================*/
40 PROCEDURE print_res_desp
41 ( errbuf OUT NOCOPY VARCHAR2,
42 retcode OUT NOCOPY VARCHAR2,
43 V_forg IN NUMBER,
44 V_torg IN NUMBER,
45 V_fres IN VARCHAR2,
46 V_tores IN VARCHAR2,
47 V_fres_instance IN NUMBER,
48 V_tores_instance IN NUMBER,
49 V_start_date IN VARCHAR2,
50 V_to_date IN VARCHAR2,
51 V_template IN VARCHAR2,
52 V_template_locale IN VARCHAR2
53 ) IS
54
55 BEGIN
56
57 --Initialising outpout values
58 retcode := -1;
59 v_dblink := NULL;
60 f_org := V_forg;
61 t_org := V_torg;
62
63 --copying the parameter values into the global variables
64 G_forg := gmp_despatch_load_rpt_pkg.get_orgn_code(V_forg);
65 G_torg := gmp_despatch_load_rpt_pkg.get_orgn_code(V_torg);
66 G_fres := V_fres;
67 G_tores := V_tores;
68 --Bug # 12909142 start
69 G_fres_instance := gmp_despatch_load_rpt_pkg.get_resource_inst(V_fres_instance, V_fres );--V_fres_instance;
70 G_tores_instance := gmp_despatch_load_rpt_pkg.get_resource_inst(V_tores_instance, V_tores);--V_tores_instance;
71 --Bug # 12909142 End
72 G_start_date := to_date(V_start_date,'yyyy/mm/dd hh24:mi:ss');
73 G_to_date := to_date(V_to_date,'yyyy/mm/dd hh24:mi:ss');
74 G_template := V_template;
75 G_template_locale := V_template_locale;
76
77
78 LOG_MESSAGE( 'Calling GMP_DESPATCH_LOAD_RPT_PKG.print_res_desp with values ');
79 LOG_MESSAGE( ' G_forg = '||G_forg);
80 LOG_MESSAGE( ' G_torg = '||G_torg);
81 LOG_MESSAGE( ' G_fres = '||G_fres);
82 LOG_MESSAGE( ' G_tores = '||G_tores);
83 LOG_MESSAGE( ' G_fres_instance = '||to_char(G_fres_instance));
84 LOG_MESSAGE( ' G_tores_instance = '||to_char(G_tores_instance));
85 LOG_MESSAGE( ' G_start_date = '||TO_CHAR(G_start_date,'DD-MON-YYYY HH24:MI:SS'));
86 LOG_MESSAGE( ' G_to_date = '||TO_CHAR(G_to_date,'DD-MON-YYYY HH24:MI:SS'));
87 LOG_MESSAGE( ' G_template = '||G_template);
88 LOG_MESSAGE( ' G_template_locale '||G_template_locale);
89
90
91 --generate the xml and insert into the gtmp table.
92 gme_res_generate_xml;
93
94 IF G_ret_code THEN
95 retcode := 0;
96 log_message('Successfully Completed!!');
97 END IF;
98
99 log_message('Return code = '|| retcode);
100
101 END print_res_desp;
102 /*============================================================================+
103 | |
104 | PROCEDURE NAME print_res_load |
105 | |
106 | DESCRIPTION Procedure to submit the request for load report |
107 | |
108 | CREATED BY Sowmya - 28-Jun-2005 |
109 | |
110 +============================================================================*/
111 PROCEDURE print_res_load
112 ( errbuf OUT NOCOPY VARCHAR2,
113 retcode OUT NOCOPY VARCHAR2,
114 V_inst_id IN NUMBER,
115 V_orgid IN NUMBER,
116 V_plan_id IN NUMBER,
117 V_forg IN NUMBER,
118 V_torg IN NUMBER,
119 V_fres IN NUMBER,
120 V_tores IN NUMBER,
121 V_fres_instance IN NUMBER,
122 V_tores_instance IN NUMBER,
123 V_start_date IN VARCHAR2,
124 V_to_date IN VARCHAR2,
125 V_template IN VARCHAR2,
126 V_template_locale IN VARCHAR2
127 )IS
128 BEGIN
129 --Initialising outpout values
130 retcode := -1;
131 v_dblink := NULL;
132 f_org := V_forg;
133 t_org := V_torg;
134 inst_id := V_inst_id;
135 plan_id := NVL(V_plan_id,0);
136
137 SELECT DECODE( M2A_DBLINK,
138 NULL, ' ',
139 '@'||M2A_DBLINK) INTO v_dblink FROM MSC_APPS_INSTANCES
140 WHERE INSTANCE_ID = V_inst_id ;
141
142 log_message('Inside the procedure print_res_load');
143
144 --copying the parameter values into the global variables
145 G_forg := gmp_despatch_load_rpt_pkg.get_orgn_code(V_forg);
146 G_torg := gmp_despatch_load_rpt_pkg.get_orgn_code(V_torg);
147 G_inst := gmp_despatch_load_rpt_pkg.get_inst_code(V_inst_id);
148 G_plan := gmp_despatch_load_rpt_pkg.get_plan_code(V_plan_id);
149
150 IF V_fres IS NOT NULL THEN
151 G_fres := gmp_despatch_load_rpt_pkg.get_resource_desc(V_fres);
152 f_res := to_number(V_fres);
153 ELSE
154 G_fres := NULL;
155 f_res := NULL;
156 END IF;
157
158 IF V_tores IS NOT NULL THEN
159 G_tores := gmp_despatch_load_rpt_pkg.get_resource_desc(V_tores);
160 t_res := to_number(V_tores);
161 ELSE
162 G_tores := NULL;
163 t_res := NULL;
164 END IF;
165 --Bug # 12909142 start
166 --G_fres_instance := V_fres_instance;
167 --G_tores_instance := V_tores_instance;
168 G_fres_instance := gmp_despatch_load_rpt_pkg.get_resource_inst(V_fres_instance, V_fres );
169 G_tores_instance := gmp_despatch_load_rpt_pkg.get_resource_inst(V_tores_instance, V_tores);
170 --Bug # 12909142 End
171 G_start_date := to_date(V_start_date,'yyyy/mm/dd hh24:mi:ss');
172 G_to_date := to_date(V_to_date,'yyyy/mm/dd hh24:mi:ss');
173 G_template := V_template;
174 G_template_locale := V_template_locale;
175
176 LOG_MESSAGE( 'Calling GMP_DESPATCH_LOAD_RPT_PKG.print_res_load with values ');
177 LOG_MESSAGE( ' v_inst_id = '||to_char(v_inst_id));
178 LOG_MESSAGE( ' G_plan = '||G_plan);
179 IF V_plan_id IS NULL THEN
180 LOG_MESSAGE( ' No Plan Name, Please choose the plan name. Plan_id = '||to_char(plan_id));
181 ELSE
182 LOG_MESSAGE( ' V_plan_id = '||to_char(V_plan_id));
183 END IF;
184 LOG_MESSAGE( ' G_forg = '||G_forg);
185 LOG_MESSAGE( ' G_torg = '||G_torg);
186 LOG_MESSAGE( ' G_fres_instance = '||to_char(G_fres_instance));
187 LOG_MESSAGE( ' G_tores_instance = '||to_char(G_tores_instance));
188 LOG_MESSAGE( ' G_start_date = '||TO_CHAR(G_start_date,'DD-MON-YYYY HH24:MI:SS'));
189 LOG_MESSAGE( ' G_to_date = '||TO_CHAR(G_to_date,'DD-MON-YYYY HH24:MI:SS'));
190 LOG_MESSAGE( ' G_template = '||G_template);
191 LOG_MESSAGE( ' G_template_locale = '||G_template_locale);
192
193 --generate the xml and insert into the gtmp table.
194 aps_res_generate_xml;
195
196 IF G_ret_code THEN
197 retcode := 0;
198 log_message('Successfully Completed!!');
199 END IF;
200
201 log_message('Return code = '|| retcode);
202
203 END print_res_load;
204 /*============================================================================+
205 | |
206 | PROCEDURE NAME gme_res_generate_xml |
207 | |
208 | DESCRIPTION Procedure used to Generate XML for GME resource batch |
209 | report. |
210 | |
211 | CREATED BY Sowmya - 28-Jun-2005 |
212 | |
213 +============================================================================*/
214 PROCEDURE gme_res_generate_xml IS
215
216 qryCtx DBMS_XMLGEN.ctxHandle;
217 result1 CLOB;
218 x_stmt VARCHAR2(25000);
219 seq_stmt VARCHAR2(200);
220 l_encoding VARCHAR2(20); /* B7481907 */
221 l_xml_header VARCHAR2(100); /* B7481907 */
222 l_offset PLS_INTEGER; /* B7481907 */
223 temp_clob CLOB; /* B7481907 */
224 len PLS_INTEGER; /* B7481907 */
225
226 BEGIN
227
228 -- B7481907 Rajesh Patangya starts
229 -- The following line of code ensures that XML data
230 -- generated here uses the right encoding
231 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
232 l_xml_header := '<?xml version="1.0" encoding="'|| l_encoding ||'"?>';
233 LOG_MESSAGE ('l_xml_header - '||l_xml_header);
234 -- B7481907 Rajesh Patangya starts
235
236 x_stmt := ' SELECT ' ||
237 ''''||G_forg||''''||' forg, ' ||
238 ''''||G_torg||''''||' torg, ' ||
239 ''''||G_fres||''''||' fres, ' ||
240 ''''||G_tores||''''||' tores, ' ||
241 ''''||G_fres_instance||''''||' fres_instance, ' ||
242 ''''||G_tores_instance||''''||' tores_instance, ' ||
243 ''''||G_start_date||''''||' fdate, ' ||
244 ''''||G_to_date||''''||' tdate, ' ||
245 ' CURSOR( ' ||
246 ' SELECT ' ||
247 ' gmp_despatch_load_rpt_pkg.get_orgn_code(mp.organization_id) organzation_code, ' ||
248 ' crd.resources resource_desc, ' ||
249 ' crd.usage_uom uom, '||
250 ' gi.instance_number instance_number, ' ||
251 ' CURSOR( ' ||
252 ' SELECT h.batch_no batch_no, ' ||
253 ' gs.batchstep_no operation, ' ||
254 ' gsa.activity activity, ' ||
255 ' to_char(t.start_date, '||''''||'dd-mon-yy hh24:mi:ss'||''''||') start_date, '||
256 ' to_char(t.end_date, '||''''||'dd-mon-yy hh24:mi:ss'||''''||') end_date, '||
257 ' msi.segment1 Item, ' ||
258 ' round(t.resource_usage,3) resource_usage ' ||
259 ' FROM ' ||
260 ' gme_batch_header h , '||
261 ' gme_batch_steps gs , '||
262 ' gme_material_details gmt, '||
263 ' gme_batch_step_activities gsa, '||
264 ' gme_batch_step_resources gsr, '||
265 ' gme_batch_step_items gsi, '||
266 ' gme_resource_txns t, '||
267 ' mtl_system_items msi, '||
268 ' gmp_resource_instances gri '||
269 ' WHERE h.organization_id = mp.organization_id '||
270 ' and h.batch_id = gs.batch_id '||
271 ' and h.batch_status in (1,2) '||
272 ' and gs.batch_id = gsa.batch_id '||
273 ' and gs.step_status in (1,2) '||
274 ' and gs.batchstep_id = gsa.batchstep_id '||
275 ' and gmt.batch_id = gs.batch_id '||
276 ' and gmt.batch_id = gsi.batch_id (+) '||
277 ' and gmt.material_detail_id = gsi.material_detail_id (+) '||
278 ' and gsi.batchstep_id = gs.batchstep_id (+) '||
279 ' and gsr.batch_id = gsa.batch_id '||
280 ' and gsr.batchstep_id = gsa.batchstep_id '||
281 ' and gsr.batchstep_activity_id = gsa.batchstep_activity_id '||
282 ' and gsr.resources = crd.resources '||
283 ' and gmt.organization_id = msi.organization_id '||
284 ' and gmt.inventory_item_id = msi.inventory_item_id '||
285 ' and gmt.batch_id = t.doc_id '||
286 ' and gsr.batchstep_resource_id = t.line_id '||
287 ' and t.completed_ind = 0 '||
288 ' and t.delete_mark = 0 '||
289 ' 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) '||
290 ' 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) '||
291 ' and t.instance_id = gri.instance_id (+) '||
292 ' and nvl(gri.inactive_ind,0) = 0 '||
293 ' and gri.resource_id (+) = gi.resource_id '||
294 ' and gri.instance_id (+) = gi.instance_id '||
295 ' order by 1,2,4 '||
296 ' ) DETAIL ' ||
297 ' FROM ' ||
298 ' mtl_parameters mp, ' ||
299 ' hr_organization_units hr, ' ||
300 ' cr_rsrc_dtl crd, ' ||
301 ' gmp_resource_instances gi '||
302 ' WHERE mp.organization_id = hr.organization_id '||
303 ' and mp.organization_id between nvl('||''''||f_org||''''||', mp.organization_id) and nvl('||''''||t_org||''''||', mp.organization_id)'||
304 ' and crd.resources between nvl('||''''||G_fres||''''||', crd.resources) and nvl('||''''||G_tores||''''||', crd.resources)'||
305 ' and mp.process_enabled_flag = '||''''||'Y'||''''||
306 ' and nvl(hr.date_to,sysdate) >= sysdate '||
307 ' and crd.organization_id = mp.organization_id '||
308 ' and crd.resource_id = gi.resource_id (+) ';
309
310 IF ( G_fres_instance IS NOT NULL ) OR ( G_tores_instance IS NOT NULL ) THEN
311 x_stmt := x_stmt ||' and gi.instance_number between nvl('||''''||G_fres_instance||''''||', gi.instance_number) and nvl('||''''||G_tores_instance||''''||', gi.instance_number)';
312 END IF;
313
314 x_stmt := x_stmt ||' order by 1,2,4 '||
315 ' ) HEADER ' ||
316 ' FROM DUAL ';
317
318 -- LOG_MESSAGE(x_stmt);
319
320 -- B7481907 Rajesh Patangya starts
321 DBMS_LOB.createtemporary(temp_clob, TRUE);
322 DBMS_LOB.createtemporary(result1, TRUE);
323
324 qryctx := dbms_xmlgen.newcontext(x_stmt);
325
326 -- generate XML data
327 DBMS_XMLGEN.getXML (qryctx, temp_clob, DBMS_XMLGEN.none);
328 l_offset := DBMS_LOB.INSTR (lob_loc => temp_clob,
329 pattern => '>',
330 offset => 1,
331 nth => 1);
332 LOG_MESSAGE('l_offset - '||l_offset);
333
334 -- Remove the header
335 DBMS_LOB.erase (temp_clob, l_offset,1);
336
337 -- The following line of code ensures that XML data
338 -- generated here uses the right encoding
339 DBMS_LOB.writeappend (result1, length(l_xml_header), l_xml_header);
340
341 -- Append the rest to xml output
342 DBMS_LOB.append (result1, temp_clob);
343
344 -- close context and free memory
345 DBMS_XMLGEN.closeContext(qryctx);
346 DBMS_LOB.FREETEMPORARY (temp_clob);
347 -- B7481907 Rajesh Patangya Ends
348
349 seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
350 EXECUTE IMMEDIATE seq_stmt INTO resdisp_rpt_id ;
351 INSERT INTO GMP_RESDISP_XML_TEMP (RESDISP_XML_RPT_ID,RESULT) VALUES (resdisp_rpt_id, result1);
352 DBMS_XMLGEN.closeContext(qryCtx);
353
354 COMMIT;
355
356 resdl_generate_output(resdisp_rpt_id);
357
358 G_ret_code := TRUE;
359
360 EXCEPTION
361 WHEN OTHERS THEN
362 LOG_MESSAGE ('Exception in procedure gme_res_generate_xml :'||SQLERRM);
363 G_ret_code := FALSE;
364
365 END gme_res_generate_xml;
366 /*============================================================================+
367 | |
368 | PROCEDURE NAME aps_res_generate_xml |
369 | |
370 | DESCRIPTION Procedure used to Generate XML for APS resource batch |
371 | report. |
372 | |
373 | CREATED BY Sowmya - 28-Jun-2005 |
374 | |
375 +============================================================================*/
376 PROCEDURE aps_res_generate_xml IS
377
378 qryCtx DBMS_XMLGEN.ctxHandle;
379 result1 CLOB;
380 x_stmt1 VARCHAR2(25000);
381 seq_stmt VARCHAR2(200);
382 l_encoding VARCHAR2(20); /* B7481907 */
383 l_xml_header VARCHAR2(100); /* B7481907 */
384 l_offset PLS_INTEGER; /* B7481907 */
385 temp_clob CLOB; /* B7481907 */
386 len PLS_INTEGER; /* B7481907 */
387
388 BEGIN
389
390 -- B7481907 Rajesh Patangya starts
391 -- The following line of code ensures that XML data
392 -- generated here uses the right encoding
393 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
394 l_xml_header := '<?xml version="1.0" encoding="'|| l_encoding ||'"?>';
395 LOG_MESSAGE ('l_xml_header - '||l_xml_header);
396 -- B7481907 Rajesh Patangya starts
397
398 -- B7669553 Select 40 characters for resource_description Rajesh Patangya
399
400 x_stmt1 := ' SELECT ' ||
401 ''''||G_inst||''''||' Inst, ' ||
402 ''''||G_plan||''''||' Plan, ' ||
403 ''''||G_forg||''''||' forg, ' ||
404 ''''||G_torg||''''||' torg, ' ||
405 ''''||G_fres||''''||' fres, ' ||
406 ''''||G_tores||''''||' tores, ' ||
407 ''''||G_fres_instance||''''||' fres_instance, ' ||
408 ''''||G_tores_instance||''''||' tores_instance, ' ||
409 ''''||G_start_date||''''||' fdate, ' ||
410 ''''||G_to_date||''''||' tdate, ' ||
411 ' CURSOR( ' ||
412 ' SELECT ' ||
413 ' gmp_despatch_load_rpt_pkg.get_orgn_code(dr.organization_id) organization_code, '||
414 ' substr(dr.resource_description,1,40) resource_desc,'||
415 ' dr.unit_of_measure UOM, '||
416 ' mri.serial_number Instance_number,'||
417 ' CURSOR( ' ||
418 ' SELECT ms.transaction_id Order_Id, '||
419 ' decode(rr.routing_sequence_id,null,null,rt.routing_comment) routing_desc, '||
420 ' decode(rr.operation_sequence_id,null,null,mr.operation_seq_num) oprseq_no, '||
421 ' to_char(rr.start_date, '||''''||'dd-mon-yy hh24:mi:ss'||''''||') St_date, '||
422 ' to_char(rr.end_date, '||''''||'dd-mon-yy hh24:mi:ss'||''''||') Edate, '||
423 ' msi.item_name Item, '||
424 ' round(rr.resource_hours,3) resource_usage '||
425 ' FROM ' ||
426 ' msc_resource_requirements rr, '||
427 ' msc_supplies ms, '||
428 ' msc_system_items msi, '||
429 ' msc_operation_resources mor, '||
430 ' msc_routing_operations mr, '||
431 ' msc_routings rt '||
432 ' WHERE rr.sr_instance_id = ms.sr_instance_id '||
433 ' AND rr.sr_instance_id = msi.sr_instance_id '||
434 ' AND rr.sr_instance_id = mor.sr_instance_id '||
435 ' AND rr.sr_instance_id = mr.sr_instance_id '||
436 ' AND rr.sr_instance_id = rt.sr_instance_id '||
437 ' AND rr.sr_instance_id = dr.sr_instance_id '||
438 ' AND rr.plan_id = ms.plan_id '||
439 ' AND rr.plan_id = msi.plan_id '||
440 ' AND rr.plan_id = mor.plan_id '||
441 ' AND rr.plan_id = mr.plan_id '||
442 ' AND rr.plan_id = rt.plan_id '||
443 ' AND rr.plan_id = dr.plan_id '||
444 ' AND rr.organization_id = ms.organization_id '||
445 ' AND rr.organization_id = msi.organization_id '||
446 ' AND rr.organization_id = mor.organization_id '||
447 -- ' AND rr.organization_id = mr.organization_id '||
448 ' AND rr.organization_id = rt.organization_id '||
449 ' AND rr.organization_id = dr.organization_id '||
450 ' AND rr.supply_id = ms.transaction_id '||
451 ' AND ms.inventory_item_id = msi.inventory_item_id '||
452 ' AND rr.resource_id = mor.resource_id '||
453 ' AND rr.resource_id = dr.resource_id '||
454 ' 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) '||
455 ' 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) '||
456 ' AND rr.resource_hours > 0 '||
457 ' AND rr.parent_id <> 2 ' ||
458 ' AND mor.routing_sequence_id = mr.routing_sequence_id '||
459 ' AND mor.operation_sequence_id = mr.operation_sequence_id '||
460 ' AND mr.routing_sequence_id = rt.routing_sequence_id '||
461 ' AND ms.order_type IN ( 5,17 ) '||
462 ' UNION '||
463 ' SELECT md.disposition_id Order_Id, '||
464 ' decode(rr.routing_sequence_id,null,null,rt.routing_comment) routing_desc, '||
465 ' decode(rr.operation_sequence_id,null,null,mr.operation_seq_num) oprseq_no, '||
466 ' to_char(rr.start_date, '||''''||'dd-mon-yy hh24:mi:ss'||''''||') St_date, '||
467 ' to_char(rr.end_date, '||''''||'dd-mon-yy hh24:mi:ss'||''''||') Edate, '||
468 ' msi.item_name Item, '||
469 ' round(rr.resource_hours,3) resource_usage '||
470 ' FROM ' ||
471 ' msc_resource_requirements rr, '||
472 ' msc_demands md, '||
473 ' msc_system_items msi, '||
474 ' msc_operation_resources mor, '||
475 ' msc_routing_operations mr, '||
476 ' msc_routings rt '||
477 ' WHERE rr.sr_instance_id = md.sr_instance_id '||
478 ' AND rr.sr_instance_id = msi.sr_instance_id '||
479 ' AND rr.sr_instance_id = mor.sr_instance_id '||
480 ' AND rr.sr_instance_id = mr.sr_instance_id '||
481 ' AND rr.sr_instance_id = rt.sr_instance_id '||
482 ' AND rr.sr_instance_id = dr.sr_instance_id '||
483 ' AND rr.plan_id = md.plan_id '||
484 ' AND rr.plan_id = msi.plan_id '||
485 ' AND rr.plan_id = mor.plan_id '||
486 ' AND rr.plan_id = mr.plan_id '||
487 ' AND rr.plan_id = rt.plan_id '||
488 ' AND rr.plan_id = dr.plan_id '||
489 ' AND rr.organization_id = md.organization_id '||
490 ' AND rr.organization_id = msi.organization_id '||
491 ' AND rr.organization_id = mor.organization_id '||
492 -- ' AND rr.organization_id = mr.organization_id '||
493 ' AND rr.organization_id = rt.organization_id '||
494 ' AND rr.organization_id = dr.organization_id '||
495 ' AND rr.supply_id = md.disposition_id '||
496 ' AND md.inventory_item_id = msi.inventory_item_id '||
497 ' AND rr.resource_id = mor.resource_id '||
498 ' AND rr.resource_id = dr.resource_id '||
499 ' 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) '||
500 ' 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) '||
501 ' AND rr.resource_hours > 0 '||
502 ' AND rr.parent_id <> 2 ' ||
503 ' AND mor.routing_sequence_id = mr.routing_sequence_id '||
504 ' AND mor.operation_sequence_id = mr.operation_sequence_id '||
505 ' AND mr.routing_sequence_id = rt.routing_sequence_id '||
506 ' AND md.origination_type = 1 '||
507 ' ORDER BY 1,4 '||
508 ' ) DETAIL ' ||
509 ' FROM ' ||
510 ' msc_dept_res_instances mri, '||
511 ' msc_department_resources dr '||
512 ' WHERE dr.sr_instance_id = '|| inst_id ||
513 ' AND dr.sr_instance_id = mri.sr_instance_id (+) '||
514 ' AND dr.plan_id = '|| plan_id ||
515 ' AND dr.plan_id = mri.plan_id (+)'||
516 ' AND dr.organization_id between nvl('||''''||f_org||''''||', dr.organization_id) and nvl('||''''||t_org||''''||', dr.organization_id)'||
517 ' AND dr.organization_id = mri.organization_id (+) '||
518 ' AND dr.resource_id BETWEEN nvl('||''''||f_res||''''||', dr.resource_id) and nvl('||''''||t_res||''''||', dr.resource_id)'||
519 ' AND dr.resource_id = mri.resource_id (+) ';
520
521 IF ( G_fres_instance IS NOT NULL ) OR ( G_tores_instance IS NOT NULL ) THEN
522 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)';
523 END IF;
524
525 x_stmt1 := x_stmt1 ||' ORDER BY 1,2,4'||
526 ' ) HEADER ' ||
527 ' FROM DUAL ';
528
529 LOG_MESSAGE(x_stmt1);
530
531 -- B7481907 Rajesh Patangya starts
532 DBMS_LOB.createtemporary(temp_clob, TRUE);
533 DBMS_LOB.createtemporary(result1, TRUE);
534
535 qryctx := dbms_xmlgen.newcontext(x_stmt1);
536
537 -- generate XML data
538 DBMS_XMLGEN.getXML (qryctx, temp_clob, DBMS_XMLGEN.none);
539 l_offset := DBMS_LOB.INSTR (lob_loc => temp_clob,
540 pattern => '>',
541 offset => 1,
542 nth => 1);
543 LOG_MESSAGE('l_offset - '||l_offset);
544
545 -- Remove the header
546 DBMS_LOB.erase (temp_clob, l_offset,1);
547
548 -- The following line of code ensures that XML data
549 -- generated here uses the right encoding
550 DBMS_LOB.writeappend (result1, length(l_xml_header), l_xml_header);
551
552 -- Append the rest to xml output
553 DBMS_LOB.append (result1, temp_clob);
554
555 -- close context and free memory
556 DBMS_XMLGEN.closeContext(qryctx);
557 DBMS_LOB.FREETEMPORARY (temp_clob);
558 -- B7481907 Rajesh Patangya Ends
559
560 seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
561 EXECUTE IMMEDIATE seq_stmt INTO resdisp_rpt_id ;
562 INSERT INTO GMP_RESDISP_XML_TEMP (RESDISP_XML_RPT_ID,RESULT) VALUES (resdisp_rpt_id, result1);
563 DBMS_XMLGEN.closeContext(qryCtx);
564
565 COMMIT;
566
567 resdl_generate_output(resdisp_rpt_id);
568
569 G_ret_code := TRUE;
570
571 EXCEPTION
572 WHEN OTHERS THEN
573 LOG_MESSAGE ('Exception in procedure aps_res_generate_xml :'||SQLERRM);
574 G_ret_code := FALSE;
575
576 END aps_res_generate_xml;
577 /*============================================================================+
578 | |
579 | FUNCTION NAME get_orgn_code |
580 | |
581 | DESCRIPTION Function to get the organization code |
582 | |
583 | CREATED BY Sowmya - 28-Jun-2005 |
584 | |
585 +============================================================================*/
586 FUNCTION get_orgn_code( p_orgn_id IN NUMBER) RETURN VARCHAR2 IS
587
588 TYPE ref_cursor_typ IS REF CURSOR;
589 cur_orgn_code ref_cursor_typ ;
590 l_orgn_code VARCHAR2(4);
591 v_sql_stmt VARCHAR2(1000);
592
593 BEGIN
594 l_orgn_code := NULL;
595 /* OPEN cur_orgn_code FOR
596 SELECT organization_code FROM mtl_parameters
597 WHERE organization_id = p_orgn_id;
598 */
599
600 v_sql_stmt :=
601 'SELECT '
602 || ' mp.organization_code '
603 || 'FROM '
604 || ' mtl_parameters' ||v_dblink|| ' mp '
605 || 'WHERE '
606 || ' mp.organization_id =:p1' ;
607
608 OPEN cur_orgn_code FOR v_sql_stmt USING p_orgn_id;
609 FETCH cur_orgn_code INTO l_orgn_code ;
610 CLOSE cur_orgn_code ;
611
612 RETURN l_orgn_code;
613
614 END get_orgn_code;
615
616 /*============================================================================+
617 | |
618 | FUNCTION NAME get_inst_code |
619 | |
620 | DESCRIPTION Function to get the Instance code |
621 | |
622 | CREATED BY Sowmya - 28-Jun-2005 |
623 | |
624 +============================================================================*/
625 FUNCTION get_inst_code( p_inst_id IN NUMBER) RETURN VARCHAR2 IS
626
627 TYPE ref_cursor_typ IS REF CURSOR;
628 cur_inst_code ref_cursor_typ ;
629
630 l_inst_code VARCHAR2(4);
631
632 BEGIN
633 OPEN cur_inst_code FOR
634 SELECT instance_code FROM msc_apps_instances
635 WHERE instance_id = p_inst_id;
636
637 FETCH cur_inst_code INTO l_inst_code ;
638
639 CLOSE cur_inst_code ;
640
641 RETURN l_inst_code;
642
643 END get_inst_code;
644
645 /*============================================================================+
646 | |
647 | FUNCTION NAME get_plan_code |
648 | |
649 | DESCRIPTION Function to get the Plan name |
650 | |
651 | CREATED BY Sowmya - 28-Jun-2005 |
652 | |
653 +============================================================================*/
654 FUNCTION get_plan_code( p_plan_id IN NUMBER) RETURN VARCHAR2 IS
655
656 TYPE ref_cursor_typ IS REF CURSOR;
657 cur_plan_code ref_cursor_typ ;
658
659 l_plan_name VARCHAR2(10);
660
661 BEGIN
662 OPEN cur_plan_code FOR
663 SELECT compile_designator FROM msc_plans
664 WHERE plan_id = p_plan_id;
665
666 FETCH cur_plan_code INTO l_plan_name ;
667
668 CLOSE cur_plan_code ;
669
670 RETURN l_plan_name;
671
672 END get_plan_code;
673
674 /*============================================================================+
675 | |
676 | FUNCTION NAME get_resource_desc |
677 | |
678 | DESCRIPTION Function to get the Resources |
679 | |
680 | CREATED BY Sowmya - 28-Jun-2005 |
681 | |
682 +============================================================================*/
683 FUNCTION get_resource_desc( p_resource_id IN NUMBER) RETURN VARCHAR2 IS
684
685 TYPE ref_cursor_typ IS REF CURSOR;
686 cur_res_desc ref_cursor_typ ;
687
688 l_resource VARCHAR2(40);
689
690 BEGIN
691 -- B7669553 Select 40 characters for resource_description Rajesh Patangya
692 OPEN cur_res_desc FOR
693 SELECT distinct substr(resource_description,1,40) FROM msc_department_resources
694 WHERE plan_id = plan_id
695 AND resource_id = p_resource_id
696 AND sr_instance_id = inst_id;
697
698 FETCH cur_res_desc INTO l_resource ;
699
700 CLOSE cur_res_desc ;
701
702 RETURN l_resource;
703
704 END get_resource_desc;
705
706 /*============================================================================+
707 | |
708 | FUNCTION NAME get_resource_inst |
709 | |
710 | DESCRIPTION Function to get the Resource instance |
711 | |
712 | CREATED BY Vijay Induri - 21-Dec-2011 --Bug # 12909142 |
713 | |
714 +============================================================================*/
715 FUNCTION get_resource_inst( p_resource_inst IN NUMBER, p_resource_id IN NUMBER) RETURN VARCHAR2 IS
716
717 TYPE ref_cursor_typ IS REF CURSOR;
718 cur_resource_inst ref_cursor_typ ;
719
720 l_resource_inst VARCHAR2(40);
721
722 BEGIN
723 -- B7669553 Select 40 characters for resource_description Rajesh Patangya
724 OPEN cur_resource_inst FOR
725 SELECT serial_number FROM Msc_Dept_Res_Instances
726 WHERE resource_id = p_resource_id
727 AND plan_id = plan_id
728 AND res_instance_id = p_resource_inst
729 AND sr_instance_id = inst_id;
730
731 FETCH cur_resource_inst INTO l_resource_inst ;
732
733 CLOSE cur_resource_inst ;
734
735 RETURN l_resource_inst;
736
737 END get_resource_inst;
738
739 /*============================================================================+
740 | |
741 | PROCEDURE NAME get_orgn_code |
742 | |
743 | DESCRIPTION Procedure for logging messages in log file |
744 | |
745 | CREATED BY Sowmya - 28-Jun-2005 |
746 | |
747 +============================================================================*/
748 PROCEDURE LOG_MESSAGE(pBUFF IN VARCHAR2) IS
749
750 BEGIN
751
752 IF fnd_global.conc_request_id > 0 THEN
753 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
754 ELSE
755 NULL;
756 END IF;
757
758 EXCEPTION
759 WHEN OTHERS THEN
760 RETURN;
761
762 END LOG_MESSAGE;
763
764 /* ***************************************************************
765 * NAME
766 * PROCEDURE - resdl_generate_output
767 *
768 * DESCRIPTION
769 * Procedure used generate the final output.
770 *
771 *************************************************************** */
772
773 PROCEDURE resdl_generate_output (
774 p_sequence_num IN NUMBER
775 )
776 IS
777
778 l_conc_id NUMBER;
779 l_req_id NUMBER;
780 l_phase VARCHAR2(20);
781 l_status_code VARCHAR2(20);
782 l_dev_phase VARCHAR2(20);
783 l_dev_status VARCHAR2(20);
784 l_message VARCHAR2(20);
785 l_status BOOLEAN;
786 scale_report VARCHAR2(1) ; -- Bug: 9265463 vpedarla
787
788
789 BEGIN
790 scale_report := NVL(FND_PROFILE.VALUE('GMP_SCALE_PDR'),'N'); -- Bug: 9265463 Vpedarla
791
792
793 l_conc_id := FND_REQUEST.SUBMIT_REQUEST('GMP','GMPRESDP','', '',FALSE,
794 p_sequence_num, chr(0),'','','','','','','','','','','',
795 '','','','','','','','','','','','','','','',
796 '','','','','','','','','','',
797 '','','','','','','','','','',
798 '','','','','','','','','','',
799 '','','','','','','','','','',
800 '','','','','','','','','','',
801 '','','','','','','','','','',
802 '','','','','','','','','','');
803
804 IF l_conc_id = 0 THEN
805 G_log_text := FND_MESSAGE.GET;
806 FND_FILE.PUT_LINE ( FND_FILE.LOG,G_log_text);
807 ELSE
808 COMMIT ;
809 END IF;
810
811 IF l_conc_id <> 0 THEN
812
813 l_status := fnd_concurrent.WAIT_FOR_REQUEST
814 (
815 REQUEST_ID => l_conc_id,
816 INTERVAL => 30,
817 MAX_WAIT => 900,
818 PHASE => l_phase,
819 STATUS => l_status_code,
820 DEV_PHASE => l_dev_phase,
821 DEV_STATUS => l_dev_status,
822 MESSAGE => l_message
823 );
824
825 DELETE FROM GMP_RESDISP_XML_TEMP WHERE RESDISP_XML_RPT_ID = p_sequence_num;
826
827 /* Bug: 6609251 Vpedarla added a NULL parameters for the submition of the FND request for XDOREPPB */
828
829 l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB','', '',FALSE,'',
830 l_conc_id,554,G_template,
831 G_template_locale,'Y','RTF','',scale_report,'','','','','',
832 '','','','','','','','','','','','','','','',
833 '','','','','','','','','','',
834 '','','','','','','','','','',
835 '','','','','','','','','','',
836 '','','','','','','','','','',
837 '','','','','','','','','','',
838 '','','','','','','','','','',
839 '','','','','','','','','','');
840
841 /*
842 l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB','', '',FALSE,'',
843 l_conc_id,554,G_template,
844 G_template_locale,'N','','','','','','','','',
845 '','','','','','','','','','','','','','','',
846 '','','','','','','','','','',
847 '','','','','','','','','','',
848 '','','','','','','','','','',
849 '','','','','','','','','','',
850 '','','','','','','','','','',
851 '','','','','','','','','','',
852 '','','','','','','','','',''); */
853 END IF;
854
855 EXCEPTION
856 WHEN OTHERS THEN
857 log_message('Exception in procedure resdl_generate_output '||SQLERRM);
858 END resdl_generate_output;
859
860 /* ***************************************************************
861 * NAME
862 * PROCEDURE - rd_xml_transfer
863 *
864 * DESCRIPTION
865 * Procedure used provide the XML as output of the concurrent program.
866 *
867 *************************************************************** */
868
869 PROCEDURE rd_xml_transfer (
870 errbuf OUT NOCOPY VARCHAR2,
871 retcode OUT NOCOPY VARCHAR2,
872 p_sequence_num IN NUMBER
873 )IS
874
875 l_file CLOB;
876 file_varchar2 VARCHAR2(32767);
877 m_file CLOB;
878 l_len NUMBER;
879 l_limit NUMBER;
880
881 BEGIN
882
883 SELECT RESULT INTO l_file
884 FROM GMP_RESDISP_XML_TEMP
885 WHERE RESDISP_XML_RPT_ID = p_sequence_num;
886
887 l_limit:= 1;
888 l_len := DBMS_LOB.GETLENGTH (l_file);
889 -- log_message (' l_len - '||l_len);
890
891 LOOP
892 IF l_len > l_limit THEN
893 --BUG 6646373 DBMS_LOB.SUBSTR was failing for multi byte character as l_file being CLOB type variable.
894 --Introduced another clob variable m_file and after trimming it assigned to the varchar type variable.
895 -- file_varchar2 := DBMS_LOB.SUBSTR (l_file,100,l_limit);
896 M_FILE := DBMS_LOB.SUBSTR (l_file,100,l_limit);
897 file_varchar2:=trim(M_FILE);
898 FND_FILE.PUT(FND_FILE.OUTPUT, file_varchar2);
899 FND_FILE.PUT(FND_FILE.LOG,file_varchar2);
900 file_varchar2 := NULL;
901 m_file :=NULL;
902 l_limit:= l_limit + 100;
903 ELSE
904 -- file_varchar2 := DBMS_LOB.SUBSTR (l_file,100,l_limit);
905 M_FILE := DBMS_LOB.SUBSTR (l_file,100,l_limit);
906 file_varchar2:=trim(M_FILE);
907 FND_FILE.PUT(FND_FILE.OUTPUT, file_varchar2);
908 FND_FILE.PUT(FND_FILE.LOG,file_varchar2);
909 file_varchar2 := NULL;
910 m_file :=NULL;
911 EXIT;
912 END IF;
913 END LOOP;
914
915 EXCEPTION
916 WHEN OTHERS THEN
917 log_message ('Exception in procedure rd_xml_transfer '||SQLERRM);
918 END rd_xml_transfer;
919
920 END GMP_DESPATCH_LOAD_RPT_PKG;