[Home] [Help]
PACKAGE BODY: APPS.AHL_PRD_PRINT_PVT
Source
1 PACKAGE BODY AHL_PRD_PRINT_PVT AS
2 /* $Header: AHLVPPRB.pls 120.15 2008/03/14 10:32:34 pdoki ship $ */
3
4 G_PKG_NAME VARCHAR2(30) :='AHL_PRD_PRINT_PVT';
5
6 -- Constants to identify locator and item number elements
7 G_ITEM_NUMBER_CHAR NUMBER := 10;
8 G_LOCATOR_CHAR NUMBER := 15;
9
10 procedure build_qa_query(
11 p_plan_id IN NUMBER,
12 p_collection_id IN NUMBER,
13 x_query_string OUT NOCOPY VARCHAR2
14 );
15
16 TYPE EMP_DETAILS_REC_TYPE IS RECORD(employee_id NUMBER, employee_name VARCHAR2(240));
17
18 -- Table to hold employee_id and employee name
19 TYPE EMP_TABLE_TYPE IS TABLE OF EMP_DETAILS_REC_TYPE INDEX BY BINARY_INTEGER;
20
21 ----------------------------------------------------------------------------
22 --Function which returns offset for current server timezone as per the profile
23 --setting in SERVER_TIMEZONE_ID
24 ----------------------------------------------------------------------------
25 FUNCTION get_tz_offset
26 RETURN VARCHAR2
27 IS
28
29 CURSOR c_get_tz_code
30 IS
31 SELECT
32 timezone_code
33 FROM
34 fnd_timezones_vl
35 WHERE
36 upgrade_tz_id = fnd_profile.VALUE('SERVER_TIMEZONE_ID');
37
38 CURSOR c_get_tz_offset(c_tz_code IN VARCHAR2)
39 IS
40 SELECT substr(tz_offset(c_tz_code),0,6)
41 FROM
42 dual;
43
44 l_tz_offset VARCHAR2(100);
45 l_tz_code VARCHAR2(50);
46
47 BEGIN
48 -- Code added for bug # 5199935
49 -- Ref https://metalink.oracle.com/metalink/plsql/f?p=130:14:2578122075547395620::::p14_database_id,p14_docid,
50 --p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,340512.1,1,1,1,helvetica#aref2
51 OPEN c_get_tz_code;
52 FETCH c_get_tz_code INTO l_tz_code;
53 CLOSE c_get_tz_code;
54
55 OPEN c_get_tz_offset(l_tz_code);
56 FETCH c_get_tz_offset INTO l_tz_offset;
57 CLOSE c_get_tz_offset;
58 -- Code added for bug # 5199935
59
60 RETURN l_tz_offset;
61 END get_tz_offset;
62
63 ------------------------------------------------------------------------------------------------
64 -- Procedure to generate XML data for workorder(s).
65 ------------------------------------------------------------------------------------------------
66 PROCEDURE Gen_Wo_Xml(
67 p_api_version IN NUMBER :=1.0,
68 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
69 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
70 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
71 p_default IN VARCHAR2 :=FND_API.G_FALSE,
72 p_module_type IN VARCHAR2 :=NULL,
73 x_return_status OUT NOCOPY VARCHAR2,
74 x_msg_count OUT NOCOPY NUMBER,
75 x_msg_data OUT NOCOPY VARCHAR2,
76 p_workorders_tbl IN WORKORDER_TBL_TYPE,
77 p_employee_id IN NUMBER,
78 p_user_role IN VARCHAR2,-- required for resource transactions
79 p_material_req_flag IN VARCHAR2 := 'N',--not required any more
80 x_xml_data OUT NOCOPY CLOB,
81 p_concurrent_flag IN VARCHAR2 := 'N'-- pass as N non concurrent programs
82 )
83 IS
84
85
86
87 --1. Gather workorder header details.
88 l_wo_details VARCHAR2(5000) := '
89 SELECT
90 wo.job_number,
91 wo.job_description,
92 wo.job_status_meaning,
93 wo.visit_number,
94 wo.organization_name,
95 wo.department_name,
96 decode(wo.scheduled_start_date, null, null, TO_CHAR(wo.scheduled_start_date,''YYYY-MM-DD'')||''T''||TO_CHAR(wo.scheduled_start_date, ''hh24:mi:ss'')||AHL_PRD_PRINT_PVT.get_tz_offset ) scheduled_start_date,
97 decode(wo.scheduled_end_date, null, null, TO_CHAR(wo.scheduled_end_date, ''YYYY-MM-DD'')||''T''||TO_CHAR(wo.scheduled_end_date, ''hh24:mi:ss'')||AHL_PRD_PRINT_PVT.get_tz_offset ) scheduled_end_date,
98 decode(wo.actual_start_date, null, null, TO_CHAR(wo.actual_start_date,''YYYY-MM-DD'')||''T''||TO_CHAR(wo.actual_start_date, ''hh24:mi:ss'')||AHL_PRD_PRINT_PVT.get_tz_offset )actual_start_date,
99 decode(wo.actual_end_date, null, null, TO_CHAR(wo.actual_end_date, ''YYYY-MM-DD'')||''T''||TO_CHAR(wo.actual_end_date, ''hh24:mi:ss'')||AHL_PRD_PRINT_PVT.get_tz_offset ) actual_end_date,
100 wo.unit_name,
101 wo.wo_part_number,
102 wo.serial_number,
103 wo.mr_title,
104 ro.ROUTE_NO route_title
105 FROM
106 AHL_SEARCH_WORKORDERS_V wo,
107 ahl_routes_app_v ro, ';
108
109
110 l_wo_details_where VARCHAR2(500) := ' WHERE
111 wo.route_id = ro.route_id(+) and
112 WODE.workorder_id = wo.workorder_id and
113 wo.workorder_id = ';
114
115 --2. Gather turnover notes for the workorder.
116 l_to_notes VARCHAR2(3000) := '
117 SELECT
118 decode(ENTERED_DATE, null, null, TO_CHAR(ENTERED_DATE, ''YYYY-MM-DD'')||''T''||TO_CHAR(ENTERED_DATE, ''hh24:mi:ss'')||AHL_PRD_PRINT_PVT.get_tz_offset ) ENTERED_DATE,
119 ENTERED_BY_NAME ,
120 notes
121 FROM
122 JTF_NOTES_VL
123 WHERE
124 source_object_code = ''AHL_WO_TURNOVER_NOTES'' and
125 source_object_id = ';
126
127 --3. Gather operation details.
128 l_op_details_1 VARCHAR2(500) := '
129 SELECT
130 OPERATION_SEQUENCE_NUM,
131 OPERATION_CODE,
132 DESCRIPTION,
133 STATUS,';
134
135 --AHL_PRD_UTIL_PKG.Get_Op_TotalHours_Assigned(WIP_ENTITY_ID,operation_sequence_num) "Total_Hours",
136 --AHL_PRD_UTIL_PKG.Get_Op_transacted_hours(WIP_ENTITY_ID,operation_sequence_num) "Hours_Worked",
137
138 l_op_details_2 VARCHAR2(1000) := '
139 decode(SCHEDULED_START_DATE, null, null, TO_CHAR(SCHEDULED_START_DATE, ''YYYY-MM-DD'')||''T''||TO_CHAR(SCHEDULED_START_DATE, ''hh24:mi:ss'')||AHL_PRD_PRINT_PVT.get_tz_offset )SCHEDULED_START_DATE,
140 decode(ACTUAL_START_DATE, null, null, TO_CHAR(ACTUAL_START_DATE, ''YYYY-MM-DD'')||''T''||TO_CHAR(ACTUAL_START_DATE, ''hh24:mi:ss'')||AHL_PRD_PRINT_PVT.get_tz_offset ) ACTUAL_START_DATE,
141 decode(ACTUAL_END_DATE, null, null, TO_CHAR(ACTUAL_END_DATE, ''YYYY-MM-DD'')||''T''||TO_CHAR(ACTUAL_END_DATE, ''hh24:mi:ss'')||AHL_PRD_PRINT_PVT.get_tz_offset ) ACTUAL_END_DATE
142 FROM
143 ahl_workorder_operations_v
144 WHERE
145 workorder_operation_id = ';
146
147
148 --4. Gather material requirements.
149 l_wo_materials VARCHAR2(3000) := '
150 SELECT
151 CONCATENATED_SEGMENTS,
152 OPERATION_SEQUENCE,
153 DESCRIPTION,
154 REQUESTED_QUANTITY required_quantity,
155 decode(REQUESTED_DATE, null, null, TO_CHAR(REQUESTED_DATE, ''YYYY-MM-DD'')||''T''||TO_CHAR(REQUESTED_DATE, ''hh24:mi:ss'')||AHL_PRD_PRINT_PVT.get_tz_offset ) required_date,
156 SCHEDULE_QUANTITY,
157 decode(SCHEDULE_DATE, null, null, TO_CHAR(SCHEDULE_DATE, ''YYYY-MM-DD'')||''T''||TO_CHAR(SCHEDULE_DATE, ''hh24:mi:ss'')||AHL_PRD_PRINT_PVT.get_tz_offset ) SCHEDULE_DATE,
158 ISSUED_QTY,
159 UOM
160 FROM
161 AHL_JOB_OPER_MATERIALS_V
162 WHERE
163 WORKORDER_ID = ';
164
165 --9. gather document requirements.
166 --1. docs associated to Routes
167 l_route_doc VARCHAR2(1000) := '
168 SELECT
169 DOC.DOCUMENT_NO,
170 DOC.DOCUMENT_TITLE,
171 DOC.ASO_OBJECT_TYPE_DESC,
172 DOC.REVISION_NO,
173 DOC.CHAPTER,
174 DOC.SECTION,
175 DOC.SUBJECT,
176 DOC.PAGE,
177 DOC.FIGURE,
178 DOC.NOTE
179 FROM
180 AHL_WORKORDERS WO,
181 AHL_DOCUMENT_ASSOS_V DOC
182 WHERE
183 WO.ROUTE_ID = DOC.ASO_OBJECT_ID
184 AND DOC.ASO_OBJECT_TYPE_CODE = ''ROUTE''
185 AND WO.WORKORDER_ID = ';
186
187 --2. docs associated to operations
188 l_op_doc VARCHAR2(2000) := '
189 UNION ALL
190 SELECT
191 DOC.DOCUMENT_NO,
192 DOC.DOCUMENT_TITLE,
193 DOC.ASO_OBJECT_TYPE_DESC,
194 DOC.REVISION_NO,
195 DOC.CHAPTER,
196 DOC.SECTION,
197 DOC.SUBJECT,
198 DOC.PAGE,
199 DOC.FIGURE,
200 DOC.NOTE
201 FROM
202 AHL_WORKORDER_OPERATIONS WOP,
203 AHL_DOCUMENT_ASSOS_V DOC
204 WHERE
205 WOP.OPERATION_ID = DOC.ASO_OBJECT_ID
206 AND DOC.ASO_OBJECT_TYPE_CODE = ''OPERATION''
207 AND WOP.WORKORDER_ID = ';
208
209 -- 3. docs associated to MRs
210 l_mr_doc VARCHAR2(2000) := '
211 UNION ALL
212 SELECT
213 DOC.DOCUMENT_NO,
214 DOC.DOCUMENT_TITLE,
215 DOC.ASO_OBJECT_TYPE_DESC,
216 DOC.REVISION_NO,
217 DOC.CHAPTER,
218 DOC.SECTION,
219 DOC.SUBJECT,
220 DOC.PAGE,
221 DOC.FIGURE,
222 DOC.NOTE
223 FROM
224 AHL_WORKORDERS WO,
225 AHL_VISIT_TASKS_B VST,
226 AHL_DOCUMENT_ASSOS_V DOC
227 WHERE
228 WO.VISIT_TASK_ID = VST.VISIT_TASK_ID
229 AND VST.MR_ID = DOC.ASO_OBJECT_ID
230 AND DOC.ASO_OBJECT_TYPE_CODE = ''MR''
231 AND WO.WORKORDER_ID = ';
232
233 --4. Docs associated to MCs
234 l_mc_doc VARCHAR2(2000) := '
235 UNION ALL
236 -- MC DOCUMENT ASSOCIATIONS
237 SELECT
238 DOC.DOCUMENT_NO,
239 DOC.DOCUMENT_TITLE,
240 DOC.ASO_OBJECT_TYPE_DESC,
241 DOC.REVISION_NO,
242 DOC.CHAPTER,
243 DOC.SECTION,
244 DOC.SUBJECT,
245 DOC.PAGE,
246 DOC.FIGURE,
247 DOC.NOTE
248 FROM
249 AHL_WORKORDERS WO,
250 CSI_II_RELATIONSHIPS CSI,
251 AHL_VISIT_TASKS_B VTS,
252 AHL_DOCUMENT_ASSOS_V DOC
253 WHERE
254 WO.VISIT_TASK_ID = VTS.VISIT_TASK_ID
255 AND VTS.INSTANCE_ID = CSI.SUBJECT_ID
256 AND CSI.RELATIONSHIP_TYPE_CODE = ''COMPONENT-OF''
257 AND (SYSDATE BETWEEN NVL(CSI.ACTIVE_START_DATE, SYSDATE) AND NVL(CSI.ACTIVE_END_DATE, SYSDATE))
258 AND CSI.POSITION_REFERENCE = TO_CHAR(DOC.ASO_OBJECT_ID)
259 AND DOC.ASO_OBJECT_TYPE_CODE = ''MC''
260 AND WO.WORKORDER_ID = ';
261
262 --5. Docs associated to PC node
263 l_pc_doc VARCHAR2(2000) := '
264 UNION ALL
265 SELECT
266 DOC.DOCUMENT_NO,
267 DOC.DOCUMENT_TITLE,
268 DOC.ASO_OBJECT_TYPE_DESC,
269 DOC.REVISION_NO,
270 DOC.CHAPTER,
271 DOC.SECTION,
272 DOC.SUBJECT,
273 DOC.PAGE,
274 DOC.FIGURE,
275 DOC.NOTE
276 FROM
277 AHL_WORKORDERS WO,
278 AHL_PC_ASSOCIATIONS PCA,
279 AHL_VISIT_TASKS_B VTS,
280 AHL_DOCUMENT_ASSOS_V DOC
281 WHERE
282 WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
283 AND AHL_UTIL_UC_PKG.GET_UC_HEADER_ID(VTS.INSTANCE_ID) = PCA.UNIT_ITEM_ID
284 AND PCA.PC_NODE_ID = DOC.ASO_OBJECT_ID
285 AND DOC.ASO_OBJECT_TYPE_CODE = ''PC''
286 AND WO.WORKORDER_ID = ';
287
288
289 CURSOR get_wo_employees(p_wo_id IN NUMBER, p_employee_id IN NUMBER) IS
290 SELECT DISTINCT
291 wass.employee_id,
292 pf.full_name employee_name
293 FROM
294 ahl_Work_Assignments wass,
295 ahl_Operation_Resources opr,
296 ahl_Workorder_Operations wop,
297 per_people_f pf,
298 per_Person_Types Pt
299 WHERE
300 wass.Operation_Resource_Id = opr.operation_resource_id and
301 opr.Workorder_Operation_Id = wop.workorder_operation_id and
302 wop.workorder_id = p_wo_id and
303 wass.employee_id = nvl(p_employee_id, wass.employee_id) and
304 wass.employee_id = pf.PERSON_ID and
305 pt.Person_Type_Id = Pf.Person_Type_Id And
306 pt.System_Person_Type ='EMP' And
307 ( Trunc(Sysdate) Between Pf.Effective_Start_Date And
308 Pf.Effective_End_Date);
309
310 -- cursor to get operations of a workorder
311 CURSOR c_wo_operations(p_workorder_id IN NUMBER) IS
312 SELECT
313 workorder_operation_id,
314 operation_sequence_num
315 FROM
316 ahl_workorder_operations
317 WHERE
318 workorder_id = p_workorder_id;
319
320 --Cursor for getting workorder plan_id and collection id
321 CURSOR get_wo_qa_ids_csr(p_workorder_id IN NUMBER) IS
322 SELECT
323 PLAN_ID,
324 COLLECTION_ID
325 FROM
326 AHL_WORKORDERS
327 WHERE
328 workorder_id = p_workorder_id;
329
330 --Cursor for getting workorder operation plan_id and collection id
331 CURSOR get_op_qa_ids_csr(p_wo_id IN NUMBER) IS
332 SELECT
333 OPERATION_SEQUENCE_NUM, --balaji added for Bug 6777371
334 PLAN_ID,
335 COLLECTION_ID
336 FROM
337 AHL_WORKORDER_OPERATIONS
338 WHERE
339 workorder_id = p_wo_id;
340
341 --declare all local variables here.
342 l_api_name CONSTANT VARCHAR2(30) := 'Gen_Wo_Xml';
343 l_api_version CONSTANT NUMBER := 1.0;
344 l_wo_details_lob CLOB;
345 l_merged_lob CLOB;
346 l_temp_lob CLOB;
347 l_ton_lob CLOB;
348 l_op_lob CLOB;
349 l_wo_mat_lob CLOB;
350 l_wo_doc_lob CLOB;
351 l_wo_qa_lob CLOB;
352 l_op_qa_lob CLOB;
353 l_offset NUMBER;
354 l_next_offset NUMBER;
355 wo_count NUMBER;
356 l_emp_tbl EMP_TABLE_TYPE;
357 l_employee_id NUMBER;
358 l_dummy_string VARCHAR2(1000);
359 i NUMBER;
360 j NUMBER;
361 l_plan_id NUMBER;
362 l_collection_id NUMBER;
363 l_op_seq_num NUMBER; --balaji added for Bug 6777371
364 l_emp_name VARCHAR2(100);
365 l_query_string VARCHAR2(30000); --pdoki changed for Bug 6777371
366 --l_op_details VARCHAR2(2000);
367 l_op_con_query VARCHAR2(3000);
368 l_wo_con_query VARCHAR2(3000);
369 l_count NUMBER;
370 l_user_role VARCHAR2(40);
371 l_fnd_offset NUMBER;
372 l_chunk_size NUMBER;
373 l_clob_size NUMBER;
374
375
376 context DBMS_XMLGEN.ctxHandle;
377 l_x_res_txn_tbl AHL_PRD_RESOURCE_TRANX_PVT.PRD_RESOURCE_TXNS_TBL;
378
379 BEGIN
380 --set the operating unit.
381 mo_global.init('AHL');
382
383 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
384
385 fnd_log.string
386 (
387 fnd_log.level_procedure,
388 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
389 'At the start of Gen_Wo_Xml'
390 );
391 END IF;
392
393 --SAVEPOINT Gen_Wo_Xml;
394
395
396 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
397 fnd_log.string
398 (
399 fnd_log.level_statement,
400 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
401 'API input Parameters '
402 );
403 fnd_log.string
404 (
405 fnd_log.level_statement,
406 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
407 '************************'
408 );
409 fnd_log.string
410 (
411 fnd_log.level_statement,
412 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
413 'p_employee_id -> '||p_employee_id
414 );
415 fnd_log.string
416 (
417 fnd_log.level_statement,
418 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
419 'p_user_role -> '||p_user_role
420 );
421 fnd_log.string
422 (
423 fnd_log.level_statement,
424 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
425 'p_concurrent_flag -> '||p_concurrent_flag
426 );
427 fnd_log.string
428 (
429 fnd_log.level_statement,
430 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
431 'p_workorders_tbl size -> '||p_workorders_tbl.count
432 );
433
434 fnd_log.string
435 (
436 fnd_log.level_statement,
437 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
438 'mo operating unit -> '||mo_global.get_current_org_id()
439 );
440 fnd_log.string
441 (
442 fnd_log.level_statement,
443 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
444 'AHL_PRD_PRINT_PVT.get_tz_offset -> '||AHL_PRD_PRINT_PVT.get_tz_offset
445 );
446 END IF;
447
448 -- Initialize return status to success initially
449 x_return_status:= FND_API.G_RET_STS_SUCCESS;
450
451 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
452 p_api_version,
453 l_api_name,G_PKG_NAME)
454 THEN
455 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
456 END IF;
457
458 -- Initialize message list if p_init_msg_list is set to TRUE.
459 IF FND_API.to_boolean(p_init_msg_list) THEN
460 FND_MSG_PUB.initialize;
461 END IF;
462
463 -- Open a temporary lob for merging the contents.
464 dbms_lob.createTemporary( l_merged_lob, true );
465 dbms_lob.open( l_merged_lob, dbms_lob.lob_readwrite );
466
467 --determine the user role based on employee parameter if it is not passed already in the input. Concurrent program is an example
468 l_user_role := p_user_role;
469
470 IF p_user_role IS NULL
471 THEN
472 l_user_role := AHL_PRD_WO_LOGIN_PVT.Get_User_Role;
473 END IF;
474
475 -- XML generated with dbms_xmlgen doesnt have encoding information. so we need to manually insert into the resultant CLOB.
476 dbms_lob.write(l_merged_lob,length('<?xml version="1.0" encoding="UTF-8"?>'),1,'<?xml version="1.0" encoding="UTF-8"?>');
477
478 --Put the root node to maintain the XML completeness.
479 dbms_lob.write(l_merged_lob, length('<G_WORKCARD_LIST>'),length(l_merged_lob)+1, '<G_WORKCARD_LIST>');
480
481 /***************************************************************************************
482 *Start Actual API processing here
483 ***************************************************************************************/
484 -- Process all workorders in the input.
485 IF p_workorders_tbl.COUNT > 0 THEN
486 FOR wo_count IN p_workorders_tbl.FIRST .. p_workorders_tbl.LAST
487 LOOP
488 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
489 fnd_log.string
490 (
491 fnd_log.level_statement,
492 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
493 'p_workorders_tbl('||wo_count||') --> '||p_workorders_tbl(wo_count)
494 );
495 END IF;
496
497 i := 1;
498 FOR emp_rec in get_wo_employees(p_workorders_tbl(wo_count), p_employee_id)
499 LOOP
500 l_emp_tbl(i).employee_id := emp_rec.employee_id;
501 l_emp_tbl(i).employee_name := emp_rec.employee_name;
502 i := i + 1;
503 END LOOP;
504
505 -- If no employees are assigned to work on the workcard
506 -- the report card has to be printed atleast once. hence
507 -- put some dummy value for the loop to get through.
508 IF l_emp_tbl.COUNT = 0 THEN--AND p_employee_id IS NULL THEN
509 l_emp_tbl(1).employee_id := -9999;
510 END IF;
511
512 IF l_emp_tbl.COUNT > 0 THEN
513 FOR l_count IN l_emp_tbl.FIRST..l_emp_tbl.LAST
514 LOOP
515
516 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
517 fnd_log.string
518 (
519 fnd_log.level_statement,
520 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
521 'l_emp_tbl('||l_count||') --> '||l_emp_tbl(l_count).employee_id
522 );
523 END IF;
524
525 --copy the workcard start tag
526 dbms_lob.write(l_merged_lob, length('<G_WORKCARD>'),length(l_merged_lob)+1, '<G_WORKCARD>');
527
528 /********************************************************************
529 * Create XML data related to employee
530 ********************************************************************/
531 -- add employee details to the XML output
532 IF l_emp_tbl(l_count).employee_name IS NOT NULL
533 THEN
534
535 dbms_lob.write(
536 l_merged_lob,
537 length('<G_EMP_NAME><EMP_NAME>'||l_emp_tbl(l_count).employee_name),
538 length(l_merged_lob)+1, '<G_EMP_NAME><EMP_NAME>'||dbms_xmlgen.convert(l_emp_tbl(l_count).employee_name)
539 );
540
541 dbms_lob.write(
542 l_merged_lob,
543 length('</EMP_NAME></G_EMP_NAME>'),
544 length(l_merged_lob)+1,
545 '</EMP_NAME></G_EMP_NAME>'
546 );
547 END IF;
548
549 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
550 fnd_log.string
551 (
552 fnd_log.level_statement,
553 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
554 'After Processing Employee info '
555 );
556 END IF;
557
558 /********************************************************************
559 * Create XML data related workorder header
560 ********************************************************************/
561
562 -- Query and processing workorder details
563 l_wo_con_query := l_wo_details || '(
564 SELECT
565 wop.workorder_id,
566 TO_CHAR(MIN(AHL_PRD_UTIL_PKG.Get_Op_Assigned_Start_Date('||
567 l_emp_tbl(l_count).employee_id ||
568 ',wop.workorder_id
569 ,wop.operation_sequence_num
570 ,''LINE''
571 )),
572 ''YYYY-MM-DD hh:mm:ss+HH:MM'') Assigned_Start_Date,
573 TO_CHAR(MAX(AHL_PRD_UTIL_PKG.Get_Op_Assigned_Start_Date('||
574 l_emp_tbl(l_count).employee_id ||
575 ',wop.workorder_id
576 ,wop.operation_sequence_num
577 ,''LINE'')),
578 ''YYYY-MM-DD hh:mm:ss+HH:MM'') Assigned_End_Date
579 FROM
580 AHL_WORKORDER_OPERATIONS WOP
581 GROUP BY workorder_id
582 ) WODE ';
583
584 l_wo_con_query := l_wo_con_query || l_wo_details_where;
585
586 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
587 fnd_log.string
588 (
589 fnd_log.level_statement,
590 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
591 'Workorder details query string --->'||l_wo_con_query
592 );
593 END IF;
594
595
596 context := dbms_xmlgen.newContext(l_wo_con_query || p_workorders_tbl(wo_count));
597 dbms_xmlgen.setRowsetTag(context,null);
598 dbms_xmlgen.setRowTag(context,'G_WORKORDER');
599 dbms_xmlgen.setConvertSpecialChars ( context, TRUE);
600 l_wo_details_lob := dbms_xmlgen.getXML(context,DBMS_XMLGEN.NONE);
601 dbms_xmlgen.closeContext(context);
602
603 -- free the temp variable for query after every iteration.
604 l_wo_con_query := null;
605
606 -- Write to lob only when some data exists.
607 IF dbms_lob.getlength(l_wo_details_lob) > 0
608 THEN
609 -- The generated XML itself puts the XML instruction tag which is already there
610 -- in final CLOB. so copy only rest of the details to the final output.
611 l_offset := dbms_lob.INSTR(l_wo_details_lob, '>');
612
613 -- copy workorder details into final lob
614 dbms_lob.copy(l_merged_lob, l_wo_details_lob, dbms_lob.getlength(l_wo_details_lob), dbms_lob.getlength(l_merged_lob)+1, l_offset+ 1);
615 END IF;
616
617 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
618 fnd_log.string
619 (
620 fnd_log.level_statement,
621 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
622 'After Processing Workorder Header Info '
623 );
624 END IF;
625
626 /********************************************************************
627 * Create XML data related to workorder quality
628 ********************************************************************/
629
630 -- Query and processing workorder details
631 --get the collection_id and plan_id for the workorder.
632 OPEN get_wo_qa_ids_csr(p_workorders_tbl(wo_count));
633 FETCH get_wo_qa_ids_csr INTO l_plan_id, l_collection_id;
634 CLOSE get_wo_qa_ids_csr;
635
636 IF l_plan_id IS NOT NULL AND l_collection_id IS NOT NULL
637 THEN
638
639 build_qa_query(
640 p_plan_id => l_plan_id,
641 p_collection_id => l_collection_id,
642 x_query_string => l_query_string
643 );
644
645 context := dbms_xmlgen.newContext(l_query_string);
646 dbms_xmlgen.setRowsetTag(context,'G_WO_QA_LIST');
647 dbms_xmlgen.setRowTag(context,'G_WO_QA');
648 dbms_xmlgen.setConvertSpecialChars ( context, TRUE);
649 l_wo_qa_lob := dbms_xmlgen.getXML(context,DBMS_XMLGEN.NONE);
650 dbms_xmlgen.closeContext(context);
651
652 -- Write to lob only when some data exists.
653 IF dbms_lob.getlength(l_wo_qa_lob) > 0
654 THEN
655 -- The generated XML itself puts the XML instruction tag which is already there
656 -- in final CLOB. so copy only rest of the details to the final output.
657 l_offset := dbms_lob.INSTR(l_wo_qa_lob, '>');
658
659 -- copy workorder details into final lob
660 dbms_lob.copy(l_merged_lob, l_wo_qa_lob, dbms_lob.getlength(l_wo_qa_lob), dbms_lob.getlength(l_merged_lob)+1, l_offset+ 1);
661 END IF;
662 END IF;
663
664 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
665 fnd_log.string
666 (
667 fnd_log.level_statement,
668 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
669 'After Processing Workorder Quality Info '
670 );
671 END IF;
672
673 /********************************************************************
674 * Create XML data related to workorder operation quality details
675 ********************************************************************/
676
677 -- Query and processing workorder details
678 --get the collection_id and plan_id for the workorder.
679 -- Balaji modified the code for Bug # 6777371.
680 -- Multiple Operations under the Work Order need to be accomodated.
681 -- Bug # 6777371 -- start
682 /*
683 OPEN get_op_qa_ids_csr(p_workorders_tbl(wo_count));
684 FETCH get_op_qa_ids_csr INTO l_plan_id, l_collection_id;
685 CLOSE get_op_qa_ids_csr;
686 */
687 dbms_lob.write(
688 l_merged_lob,
689 length('<G_WO_OP_QA_LIST>'),
690 length(l_merged_lob)+1,
691 '<G_WO_OP_QA_LIST>'
692 );
693
694 FOR get_op_qa_ids_rec IN get_op_qa_ids_csr(p_workorders_tbl(wo_count))
695 LOOP
696
697 l_plan_id := get_op_qa_ids_rec.plan_id;
698 l_collection_id := get_op_qa_ids_rec.collection_id;
699 l_op_seq_num := get_op_qa_ids_rec.operation_sequence_num;
700
701 IF l_plan_id IS NOT NULL AND l_collection_id IS NOT NULL
702 THEN
703
704 dbms_lob.write(
705 l_merged_lob,
706 length('<G_WO_OP_QA_HEADER><QA_WO_OP_SEQ_NO>'||l_op_seq_num||'</QA_WO_OP_SEQ_NO>'),
707 length(l_merged_lob)+1,
708 '<G_WO_OP_QA_HEADER><QA_WO_OP_SEQ_NO>'||l_op_seq_num||'</QA_WO_OP_SEQ_NO>'
709 );
710
711 build_qa_query(
712 p_plan_id => l_plan_id,
713 p_collection_id => l_collection_id,
714 x_query_string => l_query_string
715 );
716
717 context := dbms_xmlgen.newContext(l_query_string);
718 -- Top tag need to be present.
719 dbms_xmlgen.setRowsetTag(context,'G_DUMMY_TOP_OP_QA');
720 dbms_xmlgen.setRowTag(context,'G_WO_OP_QA');
721 dbms_xmlgen.setConvertSpecialChars ( context, TRUE);
722 l_op_qa_lob := dbms_xmlgen.getXML(context,DBMS_XMLGEN.NONE);
723 dbms_xmlgen.closeContext(context);
724
725 -- Write to lob only when some data exists.
726 IF dbms_lob.getlength(l_op_qa_lob) > 0
727 THEN
728 -- The generated XML itself puts the XML instruction tag which is already there
729 -- in final CLOB. so copy only rest of the details to the final output.
730 l_offset := dbms_lob.INSTR(l_op_qa_lob, '>');
731
732 -- copy workorder details into final lob
733 dbms_lob.copy(l_merged_lob, l_op_qa_lob, dbms_lob.getlength(l_op_qa_lob), dbms_lob.getlength(l_merged_lob)+1, l_offset+ 1);
734 END IF;
735
736 dbms_lob.write(
737 l_merged_lob,
738 length('</G_WO_OP_QA_HEADER>'),
739 length(l_merged_lob)+1,
740 '</G_WO_OP_QA_HEADER>'
741 );
742
743 END IF;
744 END LOOP;
745 -- Bug # 6777371 -- end
746
747 dbms_lob.write(
748 l_merged_lob,
749 length('</G_WO_OP_QA_LIST>'),
750 length(l_merged_lob)+1,
751 '</G_WO_OP_QA_LIST>'
752 );
753
754 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
755 fnd_log.string
756 (
757 fnd_log.level_statement,
758 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
759 'After Processing Workorder Operation Quality Info '
760 );
761 END IF;
762
763 /********************************************************************
764 * Create XML data related to turnover notes
765 ********************************************************************/
766
767 context := dbms_xmlgen.newContext(l_to_notes || p_workorders_tbl(wo_count));
768 dbms_xmlgen.setRowsetTag(context, 'G_TO_NOTES_LIST');-- turn this off if not required
769 dbms_xmlgen.setRowTag(context,'G_TO_NOTES');
770 dbms_xmlgen.setConvertSpecialChars ( context, TRUE);
771 l_ton_lob := dbms_xmlgen.getXML(context,DBMS_XMLGEN.NONE);
772 dbms_xmlgen.closeContext(context);
773
774 -- Write to lob only when some data exists.
775 IF dbms_lob.getlength(l_ton_lob) > 0
776 THEN
777 -- The generated XML itself puts the XML instruction tag which is already there
778 -- in final CLOB. so copy only rest of the details to the final output.
779 l_offset := dbms_lob.INSTR(l_ton_lob, '>');
780
781 -- copy workorder details into final lob
782 dbms_lob.copy(l_merged_lob, l_ton_lob, dbms_lob.getlength(l_ton_lob), dbms_lob.getlength(l_merged_lob)+1, l_offset+ 1);
783 END IF;
784
785 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
786 fnd_log.string
787 (
788 fnd_log.level_statement,
789 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
790 'After Processing turnover notes '
791 );
792 END IF;
793
794
795 /********************************************************************
796 * Create XML data for workorder operations
797 ********************************************************************/
798
799 --For each operation of the workorder gather operation details and add it to
800 --output
801 FOR l_op_rec IN c_wo_operations(p_workorders_tbl(wo_count))
802 LOOP
803
804 l_op_con_query := l_op_details_1;
805
806 --bind total hours and hours worked
807 l_op_con_query := l_op_con_query || 'NVL(AHL_PRD_UTIL_PKG.Get_Op_TotalHours_Assigned('
808 || l_emp_tbl(l_count).employee_id
809 ||', WORKORDER_ID '
810 ||', operation_sequence_num '
811 ||', '''||l_user_role||''' ),0) "TOTAL_HOURS", ';
812
813 l_op_con_query := l_op_con_query || 'NVL(AHL_PRD_UTIL_PKG.Get_Op_transacted_hours('
814 || l_emp_tbl(l_count).employee_id
815 ||', WIP_ENTITY_ID '
816 ||', operation_sequence_num '
817 ||', '''||l_user_role||''' ),0) "HOURS_WORKED", ';
818
819 l_op_con_query := l_op_con_query || l_op_details_2 || l_op_rec.workorder_operation_id;
820
821 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
822 fnd_log.string
823 (
824 fnd_log.level_statement,
825 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
826 'operation details query string --->'||l_op_con_query
827 );
828 END IF;
829
830 context := dbms_xmlgen.newContext(l_op_con_query);
831
832 dbms_xmlgen.setRowsetTag(context,null);-- turn this off if not required
833 dbms_xmlgen.setRowTag(context,'G_OP_REC');
834 dbms_xmlgen.setConvertSpecialChars ( context, TRUE);
835 l_op_lob := dbms_xmlgen.getXML(context,DBMS_XMLGEN.NONE);
836 dbms_xmlgen.closeContext(context);
837
838 -- Free temp variable once the query is executed for next iteration.
839 l_op_con_query := null;
840
841 -- Write to lob only when some data exists.
842 IF dbms_lob.getlength(l_op_lob) > 0
843 THEN
844 -- The generated XML itself puts the XML instruction tag which is already there
845 -- in final CLOB. so copy only rest of the details to the final output.
846 l_offset := dbms_lob.INSTR(l_op_lob, '>');
847
848 -- copy workorder details into final lob
849 dbms_lob.copy(l_merged_lob, l_op_lob, dbms_lob.getlength(l_op_lob), dbms_lob.getlength(l_merged_lob)+1, l_offset+ 1);
850 /********************************************************************
851 * Create XML data for resource transactions
852 ********************************************************************/
853 --Invoke AHL_PRD_RESOURCE_TRANX_PVT.Get_Resource_Txn_Defaults to get
854 --resource transaction details for the operation as follows.
855
856 AHL_PRD_RESOURCE_TRANX_PVT.Get_Resource_Txn_Defaults(
857 P_api_version => 1.0,
858 P_init_msg_list => FND_API.G_FALSE,
859 p_module_type => null,
860 x_return_status => x_return_status,
861 x_msg_count => x_msg_count,
862 x_msg_data => x_msg_data,
863 p_employee_id => l_emp_tbl(l_count).employee_id,
864 p_workorder_id => p_workorders_tbl(wo_count),
865 p_operation_seq_num => l_op_rec.operation_sequence_num,
866 p_function_name => l_user_role,
867 --p_user_role => p_user_role,
868 x_resource_txn_tbl => l_x_res_txn_tbl
869 );
870
871 --For every resource transaction data returned create and include xml data as below
872
873 IF l_x_res_txn_tbl.COUNT > 0
874 THEN
875 -- The end tag of operation has to come after its resource transaction records.
876 --hence overwrite the end tag
877 l_offset := dbms_lob.INSTR(l_merged_lob, '</G_OP_REC>', length(l_merged_lob)-12, 1);
878
879 --Insert the start tag for current resource transaction.
880 dbms_lob.write(l_merged_lob, length('<G_RES_TXN_LIST>'),l_offset, '<G_RES_TXN_LIST>');
881
882 FOR j IN l_x_res_txn_tbl.FIRST .. l_x_res_txn_tbl.LAST LOOP
883
884 l_dummy_string := '<G_OP_RES_REC>'||'<OPERATION_SEQUENCE>'||l_x_res_txn_tbl(j).operation_sequence_num||'</OPERATION_SEQUENCE>';
885 l_dummy_string := l_dummy_string ||'<RESOURCE_SEQUENCE>'||l_x_res_txn_tbl(j).resource_sequence_num||'</RESOURCE_SEQUENCE>';
886 l_dummy_string := l_dummy_string || '<RESOURCE_TYPE>'||dbms_xmlgen.convert(l_x_res_txn_tbl(j).resource_type_name)||'</RESOURCE_TYPE>';
887 l_dummy_string := l_dummy_string || '<RESOURCE_NAME>'||dbms_xmlgen.convert(l_x_res_txn_tbl(j).resource_name)||'</RESOURCE_NAME>';
888 l_dummy_string := l_dummy_string || '<EMPLOYEE_NAME>'||dbms_xmlgen.convert(l_x_res_txn_tbl(j).employee_name)||'</EMPLOYEE_NAME>'||'</G_OP_RES_REC>';
889 dbms_lob.write(l_merged_lob, length(l_dummy_string),length(l_merged_lob)+1, l_dummy_string);
890 END LOOP;
891
892 --Insert the end tag for current resource transaction.
893 dbms_lob.write(l_merged_lob, length('</G_RES_TXN_LIST>'),length(l_merged_lob)+1, '</G_RES_TXN_LIST>');
894 dbms_lob.write(l_merged_lob, length('</G_OP_REC>'),length(l_merged_lob)+1, '</G_OP_REC>');
895 END IF;
896 END IF;
897 END LOOP;
898
899 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
900 fnd_log.string
901 (
902 fnd_log.level_statement,
903 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
904 'After Processing Workorder Operations '
905 );
906 END IF;
907
908
909 /********************************************************************
910 * Create XML data for material requirements of a workorder
911 ********************************************************************/
912
913 -- Query and process workorder materials
914 context := dbms_xmlgen.newContext(l_wo_materials || p_workorders_tbl(wo_count));
915 dbms_xmlgen.setRowsetTag(context, 'G_WO_MAT_LIST');
916 dbms_xmlgen.setRowTag(context,'G_WO_MAT');
917 dbms_xmlgen.setConvertSpecialChars ( context, TRUE);
918 l_wo_mat_lob := dbms_xmlgen.getXML(context,DBMS_XMLGEN.NONE);
919 dbms_xmlgen.closeContext(context);
920
921 -- Write to lob only when some data exists.
922 IF dbms_lob.getlength(l_wo_mat_lob) > 0
923 THEN
924 -- The generated XML itself puts the XML instruction tag which is already there
925 -- in final CLOB. so copy only rest of the details to the final output.
926 l_offset := dbms_lob.INSTR(l_wo_mat_lob, '>');
927
928 -- copy workorder details into final lob
929 dbms_lob.copy(l_merged_lob, l_wo_mat_lob, dbms_lob.getlength(l_wo_mat_lob), dbms_lob.getlength(l_merged_lob)+1, l_offset+ 1);
930
931 END IF;
932
933 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
934 fnd_log.string
935 (
936 fnd_log.level_statement,
937 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
938 'After Processing Workorder Material requirements '
939 );
940 END IF;
941
942 /********************************************************************
943 * Create XML data for Document associations to a workorder
944 ********************************************************************/
945
946 -- Query and process workorder materials
947 context := dbms_xmlgen.newContext(
948 l_route_doc || p_workorders_tbl(wo_count) || ' '||
949 l_op_doc || p_workorders_tbl(wo_count) || ' '||
950 l_mr_doc || p_workorders_tbl(wo_count) || ' '||
951 l_mc_doc || p_workorders_tbl(wo_count) || ' '
952 --||l_pc_doc || p_workorders_tbl(wo_count)
953 );
954 dbms_xmlgen.setRowsetTag(context,'G_WO_DOC_LIST');
955 dbms_xmlgen.setRowTag(context,'G_WO_DOC');
956 dbms_xmlgen.setConvertSpecialChars ( context, TRUE);
957 l_wo_doc_lob := dbms_xmlgen.getXML(context,DBMS_XMLGEN.NONE);
958 dbms_xmlgen.closeContext(context);
959
960 -- Write to lob only when some data exists.
961 IF dbms_lob.getlength(l_wo_doc_lob) > 0
962 THEN
963 -- The generated XML itself puts the XML instruction tag which is already there
964 -- in final CLOB. so copy only rest of the details to the final output.
965 l_offset := dbms_lob.INSTR(l_wo_doc_lob, '>');
966
967 -- copy workorder details into final lob
968 dbms_lob.copy(l_merged_lob, l_wo_doc_lob, dbms_lob.getlength(l_wo_doc_lob), dbms_lob.getlength(l_merged_lob)+1, l_offset+ 1);
969
970 END IF;
971
972 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
973 fnd_log.string
974 (
975 fnd_log.level_statement,
976 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
977 'After Processing Document associations to workorders'
978 );
979 END IF;
980
981 --copy the workcard end tag
982 dbms_lob.write(l_merged_lob, length('</G_WORKCARD>'),length(l_merged_lob)+1, '</G_WORKCARD>');
983 END LOOP;
984
985 END IF;
986
987 END LOOP;
988 END IF;
989 /***************************************************************************************
990 *End Actual API processing here
991 ***************************************************************************************/
992
993 -- Insert ending root node to maintain the XML completeness.
994 dbms_lob.write(l_merged_lob, length('</G_WORKCARD_LIST>'),length(l_merged_lob)+1, '</G_WORKCARD_LIST>');
995
996 x_xml_data := l_merged_lob;
997
998 --Close and release the temporary lobs
999 dbms_lob.close( l_merged_lob );
1000 --dbms_lob.close(l_wo_details_lob);
1001 dbms_lob.freeTemporary( l_merged_lob );
1002
1003 /*
1004 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1005 l_fnd_offset := 1;
1006 l_chunk_size := 3000;
1007 l_clob_size := LENGTH(x_xml_data);
1008
1009 WHILE (l_clob_size > 0) LOOP
1010 fnd_log.string
1011 (
1012 fnd_log.level_statement,
1013 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
1014 'xml data ->'||substr(x_xml_data, l_chunk_size, l_fnd_offset)
1015 );
1016 l_clob_size := l_clob_size - l_chunk_size;
1017 l_fnd_offset := l_fnd_offset + l_chunk_size;
1018 END LOOP;
1019
1020 END IF;
1021 */
1022
1023 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1024 fnd_log.string
1025 (
1026 fnd_log.level_procedure,
1027 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.end',
1028 'At the end of AHL_PRD_PRINT_PVT'
1029 );
1030 END IF;
1031
1032 EXCEPTION
1033 WHEN OTHERS THEN
1034
1035 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
1036
1037 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1038 fnd_log.string
1039 (
1040 fnd_log.level_statement,
1041 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
1042 'xml data ->'||dbms_lob.substr(x_xml_data, dbms_lob.getlength(x_xml_data), 1)
1043 );
1044 END IF;
1045
1046 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
1047 THEN
1048 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
1049 END IF;
1050
1051 Fnd_Msg_Pub.count_and_get (
1052 p_encoded => Fnd_Api.g_false,
1053 p_count => x_msg_count,
1054 p_data => x_msg_data
1055 );
1056
1057
1058
1059 END Gen_Wo_Xml;
1060
1061
1062 -- procedure to collect all the QA data and return it as a string.
1063 procedure build_qa_query(
1064 p_plan_id IN NUMBER,
1065 p_collection_id IN NUMBER,
1066 x_query_string OUT NOCOPY VARCHAR2
1067 )
1068 IS
1069
1070 --cursor for getting QA Chars
1071 CURSOR get_qa_chars_csr(p_plan_id IN NUMBER) IS
1072 SELECT
1073 char_id,
1074 organization_id,
1075 prompt_sequence ,
1076 prompt,
1077 enabled_flag,
1078 default_value,
1079 default_value_id,
1080 result_column_name,
1081 values_exist_flag,
1082 displayed_flag,
1083 plan_name,
1084 plan_description,
1085 char_name,
1086 datatype,
1087 hardcoded_column,
1088 developer_name
1089 FROM
1090 QA_PLAN_CHARS_V QA
1091 WHERE
1092 plan_id = p_plan_id;
1093
1094 --local variable to hold the query string to retrieve qa results
1095 l_query_string VARCHAR2(30000) := null; --pdoki changed for Bug 6777371
1096 l_rec_count NUMBER;
1097 l_result_column_name VARCHAR2(100);
1098 l_item_found VARCHAR2(1);
1099 l_locator_found VARCHAR2(1);
1100 l_item_hardcoded_column VARCHAR2(100);
1101 l_locator_hardcoded_column VARCHAR(100);
1102 l_dummy_query VARCHAR2(3000);
1103 l_api_name CONSTANT VARCHAR2(30) := 'build_qa_query';
1104
1105 BEGIN
1106
1107 l_query_string := 'SELECT RESULTS.COLLECTION_ID, RESULTS.OCCURRENCE ';
1108
1109 l_dummy_query := ' UNION ALL SELECT null collection_id, null occurrence';
1110
1111 l_rec_count := 1;
1112
1113 -- retrieve all chars associated with the qa plan
1114 FOR qa_csr IN get_qa_chars_csr(p_plan_id) LOOP
1115
1116 IF qa_csr.char_id = G_ITEM_NUMBER_CHAR THEN
1117 -- its an item number
1118 l_query_string := l_query_string ||' ,XMLConcat(XMLELEMENT("LABEL", '''|| qa_csr.prompt ||''' )';
1119 l_query_string := l_query_string || ', XMLELEMENT("VALUE", ITEM.' || 'CONCATENATED_SEGMENTS' || ' ) ) COLL_ELEMENT';
1120
1121 l_dummy_query := l_dummy_query ||' ,XMLConcat(XMLELEMENT("LABEL", '''|| null ||''' )';
1122 l_dummy_query := l_dummy_query || ', XMLELEMENT("VALUE", '''|| null ||''' ) ) COLL_ELEMENT';
1123
1124 l_item_found := FND_API.G_TRUE;
1125 l_item_hardcoded_column := qa_csr.hardcoded_column;
1126 --l_column_name_tbl(l_rec_count) := result_column;
1127 ELSIF qa_csr.char_id = G_LOCATOR_CHAR THEN
1128
1129 l_query_string := l_query_string ||' ,XMLConcat(XMLELEMENT("LABEL", '''|| qa_csr.prompt ||''' )';
1130 l_query_string := l_query_string || ', XMLELEMENT("VALUE", LOCATOR.' || 'CONCATENATED_SEGMENTS' || ' ) ) COLL_ELEMENT';
1131
1132 l_dummy_query := l_dummy_query ||' ,XMLConcat(XMLELEMENT("LABEL", '''|| null ||''' )';
1133 l_dummy_query := l_dummy_query || ', XMLELEMENT("VALUE", '''|| null ||''' ) ) COLL_ELEMENT';
1134
1135 l_locator_found := FND_API.G_TRUE;
1136 l_locator_hardcoded_column := qa_csr.hardcoded_column;
1137 --l_column_name_tbl(l_rec_count) := result_column;
1138 ELSE
1139 IF qa_csr.hardcoded_column IS NOT NULL
1140 THEN
1141 l_result_column_name := qa_csr.developer_name;
1142 ELSE
1143 l_result_column_name := qa_csr.result_column_name;
1144 END IF;
1145 l_query_string := l_query_string ||' ,XMLConcat(XMLELEMENT("LABEL", '''|| qa_csr.prompt ||''' )';
1146 l_query_string := l_query_string || ', XMLELEMENT("VALUE", RESULTS.' || l_result_column_name || ' ) ) COLL_ELEMENT';
1147
1148 l_dummy_query := l_dummy_query ||' ,XMLConcat(XMLELEMENT("LABEL", '''|| null ||''' )';
1149 l_dummy_query := l_dummy_query || ', XMLELEMENT("VALUE", '''|| null ||''' ) ) COLL_ELEMENT';
1150 END IF;
1151
1152
1153 l_rec_count := l_rec_count + 1;
1154
1155 END LOOP;
1156
1157 --l_query_string := l_query_string || ' ,'''||TO_CHAR(l_rec_count-1)||''''||' AS FIELD_COUNT';
1158
1159 -- Results will all be included in the from clause
1160 l_query_string := l_query_string || ' FROM QA_RESULTS_V RESULTS ';
1161
1162 -- Append required FROM clauses for the API.
1163 IF l_item_found = FND_API.G_TRUE THEN
1164 l_query_string := l_query_string || ' , MTL_SYSTEM_ITEMS_KFV ITEM ';
1165 END IF;
1166
1167 IF l_locator_found = FND_API.G_TRUE THEN
1168 l_query_string := l_query_string || ' , MTL_ITEM_LOCATIONS_KFV LOCATOR ';
1169 END IF;
1170
1171 -- Append collection id to the where clause
1172 l_query_string := l_query_string || ' WHERE RESULTS.COLLECTION_ID = ' || p_collection_id;
1173
1174 -- Append required FROM clauses for the API.
1175 IF l_item_found = FND_API.G_TRUE THEN
1176 l_query_string := l_query_string || ' AND ITEM.inventory_item_id (+) = RESULTS.' || l_item_hardcoded_column;
1177 l_query_string := l_query_string || ' AND ITEM.organization_id (+) = RESULTS.organization_id ';
1178 END IF;
1179
1180 IF l_locator_found = FND_API.G_TRUE THEN
1181 l_query_string := l_query_string || ' AND LOCATOR.inventory_location_id (+) = RESULTS.' || l_locator_hardcoded_column;
1182 l_query_string := l_query_string || ' AND LOCATOR.organization_id (+) = RESULTS.organization_id ';
1183 END IF;
1184
1185 --l_query_string := l_query_string || ' ORDER BY RESULTS.OCCURRENCE ';
1186
1187 --add dummy query to the original query
1188 l_query_string := l_query_string || l_dummy_query || ' FROM DUAL CONNECT BY 1 = 1 and level <= 3';
1189 x_query_string := l_query_string;
1190
1191 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1192 fnd_log.string
1193 (
1194 fnd_log.level_statement,
1195 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
1196 'Quality Collection query string --->'||l_query_string
1197 );
1198 END IF;
1199
1200 END build_qa_query;
1201
1202 ------------------------------------------------------------------------------------------------
1203 -- Procedure to generate XML data for Workcard concurrent program
1204 ------------------------------------------------------------------------------------------------
1205 PROCEDURE Gen_Workcard_Xml(
1206 errbuf OUT NOCOPY VARCHAR2,
1207 retcode OUT NOCOPY NUMBER,
1208 p_api_version IN NUMBER,
1209 p_visit_id IN NUMBER,
1210 p_stage_id IN NUMBER,
1211 p_wo_no_from IN VARCHAR2,
1212 p_wo_no_to IN VARCHAR2,
1213 p_sch_start_from IN VARCHAR2,
1214 p_sch_start_to IN VARCHAR2,
1215 p_employee_id IN NUMBER
1216 )
1217 IS
1218
1219 --declare local variables here.
1220 l_return_status VARCHAR2(30);
1221 l_msg_count NUMBER;
1222 l_api_name VARCHAR2(30) := 'Gen_Workcard_Xml';
1223 l_api_version NUMBER := 1.0;
1224 l_workorder_tbl WORKORDER_TBL_TYPE;
1225 l_wo_count NUMBER;
1226 l_clob CLOB;
1227 l_bind_value_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
1228 l_vst_wocard_csr AHL_OSP_UTIL_PKG.ahl_search_csr;
1229 l_bind_index NUMBER;
1230 l_query_string VARCHAR2(2000);
1231 i NUMBER;
1232 l_workorder_id NUMBER;
1233 l_offset NUMBER;
1234 l_chunk_size NUMBER;
1235 l_clob_size NUMBER;
1236
1237 BEGIN
1238
1239 --set the operating unit.
1240 mo_global.init('AHL');
1241
1242 -- this step is not required. peforming since was not getting the output.
1243 -- dbms_application_info.set_client_info('600');
1244
1245 -- Initialize error message stack by default
1246 FND_MSG_PUB.Initialize;
1247
1248 fnd_file.put_line(fnd_file.log, '*************API input parameters**************');
1249 fnd_file.put_line(fnd_file.log, 'API inputs p_api_version -> '||p_api_version);
1250 fnd_file.put_line(fnd_file.log, 'API inputs p_visit_id -> '||p_visit_id);
1251 fnd_file.put_line(fnd_file.log, 'API inputs p_stage_id -> '||p_stage_id);
1252 fnd_file.put_line(fnd_file.log, 'API inputs p_wo_no_from -> '||p_wo_no_from);
1253 fnd_file.put_line(fnd_file.log, 'API inputs p_wo_no_to -> '||p_wo_no_to);
1254 fnd_file.put_line(fnd_file.log, 'API inputs p_sch_start_from -> '||p_sch_start_from);
1255 fnd_file.put_line(fnd_file.log, 'API inputs p_sch_start_to -> '||p_sch_start_to);
1256 fnd_file.put_line(fnd_file.log, 'API inputs p_employee_id -> '||p_employee_id);
1257 fnd_file.put_line(fnd_file.log, '*************API input parameters**************');
1258
1259 l_query_string := ' select
1260 wo.workorder_id
1261 from
1262 ahl_workorders wo,
1263 wip_discrete_jobs wipd,
1264 ahl_visits_b vst,
1265 ahl_visit_tasks_b vtsk
1266 where
1267 wo.visit_id = vst.visit_id and
1268 wo.wip_entity_id = wipd.wip_entity_id and
1269 wo.visit_task_id = vtsk.visit_task_id and
1270 vst.visit_id = vtsk.visit_id and
1271 wo.status_code in (3, 4, 5, 6, 7, 12, 19) and
1272 wo.master_workorder_flag <> ''Y'' and
1273 vst.status_code in (''RELEASED'',''PARTIALLY RELEASED'') ';
1274
1275 l_bind_index := 1;
1276
1277 IF p_visit_id IS NOT NULL THEN
1278 l_query_string := l_query_string || ' AND wo.visit_id = :'||l_bind_index;
1279 l_bind_value_tbl(l_bind_index) := p_visit_id;
1280 l_bind_index := l_bind_index + 1;
1281 END IF;
1282
1283 IF p_stage_id IS NOT NULL THEN
1284 l_query_string := l_query_string || ' AND vtsk.stage_id = :'||l_bind_index;
1285 l_bind_value_tbl(l_bind_index) := p_stage_id;
1286 l_bind_index := l_bind_index + 1;
1287 END IF;
1288
1289 IF p_wo_no_from IS NOT NULL AND p_wo_no_to IS NOT NULL THEN
1290 l_query_string := l_query_string || ' AND wo.workorder_name between :'||l_bind_index||' and :'||(l_bind_index+1);
1291 l_bind_value_tbl(l_bind_index) := p_wo_no_from;
1292 l_bind_value_tbl(l_bind_index+1) := p_wo_no_to;
1293 l_bind_index := l_bind_index + 2;
1294 END IF;
1295
1296 IF p_sch_start_from IS NOT NULL THEN
1297 l_query_string := l_query_string || ' AND wipd.scheduled_start_date >= :'||l_bind_index;
1298 l_bind_value_tbl(l_bind_index) := fnd_date.canonical_to_date(p_sch_start_from);
1299 l_bind_index := l_bind_index + 1;
1300 END IF;
1301
1302 IF p_sch_start_to IS NOT NULL THEN
1303 l_query_string := l_query_string || ' AND wipd.scheduled_completion_date <= :'||l_bind_index;
1304 l_bind_value_tbl(l_bind_index) := fnd_date.canonical_to_date(p_sch_start_to);
1305 l_bind_index := l_bind_index + 1;
1306 END IF;
1307
1308 fnd_file.put_line(fnd_file.log, l_query_string);
1309
1310 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR(l_vst_wocard_csr, l_bind_value_tbl, l_query_string);
1311
1312 i := 1;
1313 LOOP
1314 FETCH l_vst_wocard_csr INTO l_workorder_id;
1315 EXIT WHEN l_vst_wocard_csr%NOTFOUND;
1316 l_workorder_tbl(i) := l_workorder_id;
1317 i := i + 1;
1318 END LOOP;
1319
1320 CLOSE l_vst_wocard_csr;
1321
1322
1323 fnd_file.put_line(fnd_file.log, 'No of workorders ->'||l_workorder_tbl.COUNT);
1324
1325 IF l_workorder_tbl.COUNT > 0
1326 THEN
1327 Gen_Wo_Xml(
1328 p_api_version => 1.0,
1329 p_init_msg_list => FND_API.G_TRUE,
1330 p_commit => FND_API.G_TRUE,
1331 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1332 p_default => FND_API.G_TRUE,
1333 p_module_type => NULL,
1334 x_return_status => l_return_status,
1335 x_msg_count => l_msg_count,
1336 x_msg_data => errbuf,
1337 p_workorders_tbl => l_workorder_tbl,
1338 p_employee_id => p_employee_id,
1339 p_user_role => null,
1340 p_material_req_flag => 'N',
1341 x_xml_data => l_clob,
1342 p_concurrent_flag => 'Y'
1343 );
1344
1345 l_offset := 1;
1346 l_chunk_size := 3000;
1347 l_clob_size := dbms_lob.getlength(l_clob);
1348
1349 WHILE (l_clob_size > 0) LOOP
1350 fnd_file.put(fnd_file.log, dbms_lob.substr (l_clob, l_chunk_size, l_offset));
1351 l_clob_size := l_clob_size - l_chunk_size;
1352 l_offset := l_offset + l_chunk_size;
1353 END LOOP;
1354
1355 l_msg_count := FND_MSG_PUB.Count_Msg;
1356 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1357 THEN
1358 retcode := 2; -- error based only on return status
1359 ELSIF (l_msg_count > 0 AND l_return_status = FND_API.G_RET_STS_SUCCESS)
1360 THEN
1361 retcode := 1; -- warning based on return status + msg count
1362 ELSE
1363 --fnd_file.put_line(fnd_file.output, dbms_lob.substr(l_clob, dbms_lob.getlength(l_clob), 1));
1364
1365 l_offset := 1;
1366 l_chunk_size := 3000;
1367 l_clob_size := dbms_lob.getlength(l_clob);
1368
1369 WHILE (l_clob_size > 0) LOOP
1370 fnd_file.put(fnd_file.output, dbms_lob.substr (l_clob, l_chunk_size, l_offset));
1371 l_clob_size := l_clob_size - l_chunk_size;
1372 l_offset := l_offset + l_chunk_size;
1373 END LOOP;
1374
1375 retcode := 0; -- success, since nothing is wrong
1376 END IF;
1377 END IF;
1378 END Gen_Workcard_Xml;
1379
1380 END AHL_PRD_PRINT_PVT;