[Home] [Help]
PACKAGE BODY: APPS.EAM_SAFETY_REPORTS_PVT
Source
1 PACKAGE BODY EAM_SAFETY_REPORTS_PVT AS
2 /* $Header: EAMVSRPB.pls 120.6 2011/06/02 10:40:55 vboddapa noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2010 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVSRPS.pls
11 --
12 -- DESCRIPTION
13 --
14 -- BODY of package EAM_SAFETY_REPORTS_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 07-APRIL-2008 Madhuri Shah Initial Creation
21 ***************************************************************************/
22 g_pkg_name CONSTANT VARCHAR2(30):= 'EAM_SAFETY_REPORTS_PVT';
23
24
25 /********************************************************************
26 * Function : getWorkPermitReportXML
27 * Purpose : This procedure generate xml data for work permits
28 *********************************************************************/
29 FUNCTION getWorkPermitReportXML(
30 p_permit_ids in eam_permit_tab_type,
31 p_work_clearance_flag in NUMBER,
32 p_work_order_flag in NUMBER,
33 p_text_attachment_flag IN NUMBER,
34 p_url_attachment_flag IN NUMBER,
35 p_file_attachment_flag in NUMBER)
36 RETURN CLOB
37
38 IS
39
40 l_xmlType XMLType :=NULL;
41 l_xmlTypeParamList XMLType :=NULL;
42 l_xmlTypeTextAttachment XMLType :=NULL;
43 l_xmlTypeURLAttachment XMLType :=NULL;
44 l_xmlTypefileattachment XMLType :=NULL;
45 l_xmlTypeAssociatedWO XMLType :=NULL;
46 l_xmlTypeAssociatedClearance XMLType :=NULL;
47 l_xmlTypePermitHeader XMLType :=NULL;
48 l_xmlTypeApprover XMLType :=NULL;
49 l_organization_id NUMBER;
50
51
52 --Cursor for permit header
53 CURSOR PERMIT_HEADER_CURSOR(P_PERMIT_ID NUMBER, P_ORGANIZATION_ID NUMBER)
54 IS
55 SELECT XMLELEMENT("PERMIT_HEADER",
56 XMLFOREST(EWP.PERMIT_NAME AS PERMIT_NAME,
57 ml.meaning AS PERMIT_TYPE,
58 EWP.DESCRIPTION AS PERMIT_DESC,
59 EPSV.PERMIT_STATUS AS PERMIT_STATUS,
60 ppf.full_name AS PREPARED_BY,
61 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(EWP.CREATION_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS PREPARED_DATE,
62 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(EWP.VALID_FROM), calendar_aware => FND_DATE.calendar_aware_alt) AS PERMIT_VALID_FROM,
63 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(EWP.VALID_TO), calendar_aware => FND_DATE.calendar_aware_alt) AS PERMIT_VALID_TO
64 ))"PERMIT_HEADER"
65 FROM EAM_WORK_PERMITS EWP,
66 EAM_PERMIT_STATUSES_VL EPSV,
67 mfg_lookups ml,
71 AND EWP.ORGANIZATION_ID =P_ORGANIZATION_ID
68 fnd_user fu,
69 per_people_f ppf
70 WHERE EWP.PERMIT_ID =P_PERMIT_ID
72 AND EPSV.STATUS_ID =EWP.USER_DEFINED_STATUS_ID
73 and EWP.created_by = fu.user_id(+)
74 and fu.employee_id = ppf.person_id (+)
75 and ml.lookup_type (+) = 'EAM_WORK_PERMIT_TYPE'
76 AND ml.lookup_code=ewp.permit_type
77 AND Nvl(ppf.effective_end_date,SYSDATE)>=sysdate
78 ;
79
80 --Cursor for approvers
81 CURSOR APPROVER_CURSOR(P_PERMIT_ID NUMBER, P_ORGANIZATION_ID NUMBER)
82 IS
83 SELECT XMLELEMENT("APPROVER",
84 XMLFOREST(
85 ppf.full_name AS APPROVER_NAME)) "APPROVER"
86 FROM
87 EAM_WORK_PERMITS EWP,
88 per_people_f ppf ,
89 fnd_user fu
90 WHERE EWP.PERMIT_ID =P_PERMIT_ID
91 AND EWP.ORGANIZATION_ID =P_ORGANIZATION_ID
92 AND EWP.approved_by = fu.user_id(+)
93 and fu.employee_id = ppf.person_id (+)
94 AND Nvl(ppf.effective_end_date,SYSDATE)>=sysdate;
95
96
97 --Cursor for work clearance associations
98 CURSOR PERMIT_WORKCLEARANCE_CURSOR(P_PERMIT_ID NUMBER, P_ORGANIZATION_ID NUMBER)
99 IS
100 SELECT XMLELEMENT("WORK_CLEARANCE",
101 XMLFOREST(WORK_CLEARANCE_NAME,
102 WORK_CLEARANCE_DESCRIPTION,
103 WORK_CLEARANCE_STATUS,
104 ISOLATION_TYPE,
105 SCHEDULED_ESTAB_START_DATE,
106 SCHEDULED_REESTAB_END_DATE)) "WORK_CLEARANCE"
107 FROM
108 (
109 SELECT
110 EWC.WORK_CLEARANCE_NAME AS WORK_CLEARANCE_NAME,
111 EWC.DESCRIPTION AS WORK_CLEARANCE_DESCRIPTION,
112 EWCSV.WORK_CLEARANCE_STATUS AS WORK_CLEARANCE_STATUS,
113 ML.MEANING AS ISOLATION_TYPE,
114 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(EWC.SCHEDULED_ESTAB_START_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS SCHEDULED_ESTAB_START_DATE,
115 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(EWC.SCHEDULED_REESTAB_END_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS SCHEDULED_REESTAB_END_DATE
116 FROM
117 EAM_WORK_CLEARANCES EWC,
118 EAM_SAFETY_ASSOCIATIONS ESA,
119 EAM_WORK_CLEARANCE_STATUSES_VL EWCSV,
120 EAM_ISOLATIONS ISO,
121 MFG_LOOKUPS ml
122 WHERE
123 ESA.ASSOCIATION_TYPE= 2
124 AND ESA.SOURCE_ID =P_PERMIT_ID
125 AND ESA.ORGANIZATION_ID =P_ORGANIZATION_ID
126 AND ESA.TARGET_REF_ID = EWC.WORK_CLEARANCE_ID
127 AND EWC.LAST_ISOLATION_ID = ISO.ISOLATION_ID(+)
128 AND ISO.ISOLATION_TYPE = ML.LOOKUP_CODE (+)
129 AND ML.LOOKUP_TYPE(+) = 'EAM_ISOLATION_TYPE'
130 AND EWC.USER_DEFINED_STATUS_ID = EWCSV.STATUS_ID
131
132 UNION
133
134 SELECT
135 EWC.WORK_CLEARANCE_NAME AS WORK_CLEARANCE_NAME,
136 EWC.DESCRIPTION AS WORK_CLEARANCE_DESCRIPTION,
137 EWCSV.WORK_CLEARANCE_STATUS AS WORK_CLEARANCE_STATUS,
138 ML.MEANING AS ISOLATION_TYPE,
139 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(EWC.SCHEDULED_ESTAB_START_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS SCHEDULED_ESTAB_START_DATE,
140 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(EWC.SCHEDULED_REESTAB_END_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS SCHEDULED_REESTAB_END_DATE
141 FROM
142 EAM_WORK_CLEARANCES EWC,
143 EAM_WORK_PERMITS PERMIT,
144 EAM_SAFETY_ASSOCIATIONS ESA1,
145 EAM_SAFETY_ASSOCIATIONS ESA2,
146 MFG_LOOKUPS ML,
147 EAM_ISOLATIONS ISO,
148 WIP_DISCRETE_JOBS WDJ,
149 EAM_WORK_CLEARANCE_STATUSES_VL EWCSV
150
151 WHERE
152 ESA1.SOURCE_ID=PERMIT.PERMIT_ID AND
153 ESA1.TARGET_REF_ID=WDJ.WIP_ENTITY_ID AND
154 ESA1.ASSOCIATION_TYPE=3 AND
155 ESA2.SOURCE_ID=EWC.WORK_CLEARANCE_ID AND
156 ESA2.TARGET_REF_ID=WDJ.WIP_ENTITY_ID AND
157 ESA2.ASSOCIATION_TYPE=4 AND
158 EWC.LAST_ISOLATION_ID = ISO.ISOLATION_ID(+) AND
159 ML.LOOKUP_CODE(+) = ISO.ISOLATION_TYPE AND
160 ML.LOOKUP_TYPE (+) = 'EAM_ISOLATION_TYPE' AND
161 PERMIT.PERMIT_ID=P_PERMIT_ID AND
162 PERMIT.ORGANIZATION_ID = P_ORGANIZATION_ID
163 AND EWC.WORK_CLEARANCE_ID NOT IN(SELECT TARGET_REF_ID
164 FROM EAM_SAFETY_ASSOCIATIONS
165 WHERE SOURCE_ID = P_PERMIT_ID
166 AND ASSOCIATION_TYPE=2
167 AND ORGANIZATION_ID=P_ORGANIZATION_ID)
168 AND EWC.USER_DEFINED_STATUS_ID = EWCSV.STATUS_ID
169 )
170 ORDER BY WORK_CLEARANCE_NAME;
171
172
173 --Cursor for work order associations
174 CURSOR PERMIT_WORKORDER_CURSOR(P_PERMIT_ID NUMBER, P_ORGANIZATION_ID NUMBER)
175 IS
176 SELECT XMLELEMENT("WORK_ORDER",
177 XMLFOREST(WORK_ORDER_NAME,
178 WO_ASSET_NUMBER,
179 WO_ASSET_DESC,
180 WO_DESC,
181 WO_SCHEDULED_START_DATE,
182 WO_SCHEDULED_COMPL_DATE)) "WORK_ORDER"
183 FROM(
184 SELECT
185 WEDV.WIP_ENTITY_NAME AS WORK_ORDER_NAME,
186 WEDV.ASSET_NUMBER AS WO_ASSET_NUMBER,
187 ASSET_DESCRIPTION AS WO_ASSET_DESC,
188 WEDV.DESCRIPTION AS WO_DESC,
189 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(WEDV.SCHEDULED_START_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS WO_SCHEDULED_START_DATE,
190 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(WEDV.SCHEDULED_COMPLETION_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS WO_SCHEDULED_COMPL_DATE
191
192 FROM
193 EAM_WORK_ORDERS_V WEDV,
194 EAM_SAFETY_ASSOCIATIONS ESA
195 WHERE
196 ESA.ASSOCIATION_TYPE= 3
197 AND ESA.SOURCE_ID =P_PERMIT_ID
198 AND ESA.ORGANIZATION_ID =P_ORGANIZATION_ID
199 AND WEDV.WIP_ENTITY_ID =ESA.TARGET_REF_ID
200
201 UNION
202
203 SELECT
204 WEDV.WIP_ENTITY_NAME AS WORK_ORDER_NAME,
205 WEDV.ASSET_NUMBER AS WO_ASSET_NUMBER,
206 ASSET_DESCRIPTION AS WO_ASSET_DESC,
210
207 WEDV.DESCRIPTION AS WO_DESC,
208 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(WEDV.SCHEDULED_START_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS WO_SCHEDULED_START_DATE,
209 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(WEDV.SCHEDULED_COMPLETION_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS WO_SCHEDULED_COMPL_DATE
211 FROM
212 EAM_SAFETY_ASSOCIATIONS ESA1,
213 EAM_SAFETY_ASSOCIATIONS ESA2,
214 EAM_WORK_PERMITS PERMIT,
215 EAM_WORK_CLEARANCES EWC,
216 EAM_WORK_ORDERS_V WEDV
217
218 WHERE
219 ESA1.SOURCE_ID=PERMIT.PERMIT_ID AND
220 ESA1.TARGET_REF_ID=EWC.WORK_CLEARANCE_ID AND
221 ESA1.ASSOCIATION_TYPE=2 AND
222 ESA2.SOURCE_ID=EWC.WORK_CLEARANCE_ID AND
223 ESA2.TARGET_REF_ID=WEDV.WIP_ENTITY_ID AND
224 ESA2.ASSOCIATION_TYPE=4 AND
225 PERMIT.PERMIT_ID = P_PERMIT_ID AND
226 PERMIT.ORGANIZATION_ID = P_ORGANIZATION_ID
227 )
228 ORDER BY WORK_ORDER_NAME;
229
230
231 --Cursor for Text Attachments
232 CURSOR textAttachment_cursor(p_permit_id NUMBER,p_org_id NUMBER)
233 IS
234 SELECT XMLELEMENT("TEXT_ATTACHMENT",
235 XMLFOREST( fdst.short_text AS "TEXT_BODY",
236 fdv.description AS "TEXT_DESC",
237 fdv.category_description AS "TEXT_CATEGORY")) textAttachment
238 FROM fnd_documents_short_text fdst,
239 fnd_documents_vl fdv,
240 fnd_attached_documents fad
241 WHERE fdst.media_id = fdv.media_id
242 AND fad.document_id = fdv.document_id
243 AND fad.entity_name = 'EAM_WORK_PERMIT'
244 AND fad.pk1_value = p_org_id
245 AND fad.pk2_value = p_permit_id
246 AND fdv.datatype_id=1; --Text attachments
247
248 --Cursor for URL Attachments
249 CURSOR urlAttachment_cursor(p_permit_id NUMBER,p_org_id NUMBER)
250 IS
251 SELECT XMLELEMENT("URL_ATTACHMENT",
252 XMLFOREST( fdv.URL AS "URL_NAME",
253 fdv.description AS "URL_DESC",
254 fdv.category_description AS "URL_CATEGORY")) urlAttachment
255 FROM
256 fnd_documents_vl fdv,
257 fnd_attached_documents fad
258 WHERE
259 fad.document_id = fdv.document_id
260 AND fad.entity_name = 'EAM_WORK_PERMIT'
261 AND fad.pk1_value = p_org_id
262 AND fad.pk2_value = p_permit_id
263 AND fdv.datatype_id=5; --URL attachments
264
265 --Cursor for File Attachments
266 CURSOR fileattachment_cursor(p_permit_id NUMBER,p_org_id NUMBER)
267 IS
268 SELECT XMLELEMENT("FILE_ATTACHMENT",
269 XMLFOREST( fdv.file_name AS "FILE_NAME",
270 fdv.description AS "FILE_DESC",
271 fdv.category_description AS "FILE_CATEGORY")) fileAttachment
272 FROM fnd_documents_vl fdv,
273 fnd_attached_documents fad
274 WHERE fad.document_id = fdv.document_id
275 AND fad.entity_name = 'EAM_WORK_PERMIT'
276 AND fad.pk1_value = p_org_id
277 AND fad.pk2_value = p_permit_id
278 AND fdv.datatype_id=6
279 AND fdv.file_name IS NOT NULL
280 AND fdv.datatype_name NOT IN ('Long Text', 'Short Text');
281
282
283 BEGIN
284 FOR i IN p_permit_ids.FIRST..p_permit_ids.LAST
285 LOOP
286 -- Get org id
287
288 select ewp.organization_id into l_organization_id
289 from EAM_WORK_PERMITS ewp
290 where ewp.permit_id=p_permit_ids(i).permit_id;
291
292 l_xmlTypeParamList := xmltype('<PARAM_LIST>
293 <PERMIT_CLEARANCE_PARAM>' ||Nvl(p_work_clearance_flag,0)|| '</PERMIT_CLEARANCE_PARAM>
294 <PERMIT_WO_PARAM>' ||Nvl(p_work_order_flag,0)|| '</PERMIT_WO_PARAM>
295 <TEXTATTACH_PARAM>' ||Nvl(p_text_attachment_flag,0)|| '</TEXTATTACH_PARAM>
296 <URLATTACH_PARAM>' ||Nvl(p_url_attachment_flag,0)|| '</URLATTACH_PARAM>
297 <FILEATTACH_PARAM>' ||Nvl(p_file_attachment_flag,0)|| '</FILEATTACH_PARAM>
298 </PARAM_LIST>');
299
300 --Adding Permit Header
301 BEGIN
302 FOR permit_header_record IN PERMIT_HEADER_CURSOR(p_permit_ids(i).permit_id,l_organization_id)
303 LOOP
304 SELECT XMLConcat(l_xmlTypePermitHeader,permit_header_record.PERMIT_HEADER)
305 INTO l_xmlTypePermitHeader
306 FROM dual;
307 END LOOP;
308 EXCEPTION
309 WHEN NO_DATA_FOUND THEN
310 NULL;
311 END;
312
313 -- Adding approver
314 BEGIN
315 FOR approver_record IN APPROVER_CURSOR(p_permit_ids(i).permit_id,l_organization_id)
316 LOOP
317 SELECT XMLConcat(l_xmlTypeApprover,approver_record.APPROVER)
318 INTO l_xmlTypeApprover
319 FROM dual;
320 END LOOP;
321 SELECT XMLELEMENT("APPROVERS",l_xmlTypeApprover)
322 INTO l_xmlTypeApprover
323 FROM dual;
324 EXCEPTION
325 WHEN NO_DATA_FOUND THEN
326 NULL;
327 END;
328
329
330 --Adding Work Clearances
331 IF p_work_clearance_flag = 1 THEN
332 BEGIN
333 FOR clearance_record IN PERMIT_WORKCLEARANCE_CURSOR(p_permit_ids(i).permit_id,l_organization_id)
334 LOOP
335 SELECT XMLConcat(l_xmlTypeAssociatedClearance,clearance_record.WORK_CLEARANCE)
336 INTO l_xmlTypeAssociatedClearance
337 FROM dual;
338 END LOOP;
339 SELECT XMLELEMENT("WORK_CLEARANCES",l_xmlTypeAssociatedClearance)
340 INTO l_xmlTypeAssociatedClearance
341 FROM dual;
342 EXCEPTION
343 WHEN NO_DATA_FOUND THEN
344 NULL;
345 END;
346 END IF;
347
348 --Adding Work Orders
349 IF p_work_order_flag = 1 THEN
350 BEGIN
354 INTO l_xmlTypeAssociatedWO
351 FOR wo_record IN PERMIT_WORKORDER_CURSOR(p_permit_ids(i).permit_id,l_organization_id)
352 LOOP
353 SELECT XMLConcat(l_xmlTypeAssociatedWO,wo_record.WORK_ORDER)
355 FROM dual;
356 END LOOP;
357 SELECT XMLELEMENT("WORK_ORDERS",l_xmlTypeAssociatedWO)
358 INTO l_xmlTypeAssociatedWO
359 FROM dual;
360 EXCEPTION
361 WHEN NO_DATA_FOUND THEN
362 NULL;
363 END;
364 END IF;
365
366 --Adding Text Attachments
367 IF p_text_attachment_flag = 1 THEN
368 BEGIN
369 FOR textattachment_record IN textattachment_cursor(p_permit_ids(i).permit_id,l_organization_id)
370 LOOP
371 SELECT XMLConcat(l_xmlTypetextattachment,textattachment_record.textAttachment)
372 INTO l_xmlTypetextattachment
373 FROM dual;
374 END LOOP;
375 SELECT XMLELEMENT("TEXT_ATTACHMENTS",l_xmlTypeTextAttachment)
376 INTO l_xmlTypeTextAttachment
377 FROM dual;
378 EXCEPTION
379 WHEN NO_DATA_FOUND THEN
380 NULL;
381 END;
382 END IF;
383
384 --Adding URL Attachments
385 IF p_url_attachment_flag = 1 THEN
386 BEGIN
387 FOR urlAttachment_record IN urlAttachment_cursor(p_permit_ids(i).permit_id,l_organization_id)
388 LOOP
389 SELECT XMLConcat(l_xmlTypeURLAttachment,urlAttachment_record.URLAttachment)
390 INTO l_xmlTypeURLAttachment
391 FROM dual;
392 END LOOP;
393 SELECT XMLELEMENT("URL_ATTACHMENTS",l_xmlTypeURLAttachment)
394 INTO l_xmlTypeURLAttachment
395 FROM dual;
396 EXCEPTION
397 WHEN NO_DATA_FOUND THEN
398 NULL;
399 END;
400 END IF;
401
402 --Adding File Attachments
403 IF p_file_attachment_flag = 1 THEN
404 BEGIN
405 FOR fileattachment_record IN fileattachment_cursor(p_permit_ids(i).permit_id,l_organization_id)
406 LOOP
407 SELECT XMLConcat(l_xmlTypefileattachment,fileattachment_record.FileAttachment)
408 INTO l_xmlTypefileattachment
409 FROM dual;
410 END LOOP;
411 SELECT XMLELEMENT("FILE_ATTACHMENTS",l_xmlTypefileattachment)
412 INTO l_xmlTypefileattachment
413 FROM dual;
414 EXCEPTION
415 WHEN NO_DATA_FOUND THEN
416 NULL;
417 END;
418 END IF;
419
420 -- concatinate all xml string into one
421
422 /* SELECT XMLCONCAT(l_xmlType,
423 XMLELEMENT("PERMIT_HEADER"
424 XMLFOREST(EWP.PERMIT_NAME AS PERMIT_NAME,
425 EWP.PERMIT_TYPE AS PERMIT_TYPE,
426 EWP.DESCRIPTION AS PERMIT_DESC,
427 EPSV.PERMIT_STATUS AS PERMIT_STATUS,
428 fnd_date.date_to_displayDT(Convert_to_client_time(EWP.VALID_FROM)) AS PERMIT_VALID_FROM,
429 fnd_date.date_to_displayDT(Convert_to_client_time(EWP.VALID_TO)) AS PERMIT_VALID_TO,
430 EWP.APPROVED_BY AS approverName),
431 XMLConcat(l_xmlTypeAssociatedClearance,l_xmlTypeAssociatedWO,l_xmlTypefileattachment)))AS "RESULT"
432 INTO l_xmlType
433 FROM EAM_WORK_PERMITS EWP,
434 EAM_PERMIT_STATUSES_VL EPSV
435 WHERE EWP.PERMIT_ID =p_permit_ids(i).permit_id
436 AND EWP.ORGANIZATION_ID =l_organization_id
437 AND EPSV.STATUS_ID =EWP.USER_DEFINED_STATUS_ID;*/
438
439 SELECT XMLCONCAT(l_xmlType,
440 XMLELEMENT("PERMIT",
441 XMLConcat(l_xmlTypePermitHeader,l_xmlTypeApprover,l_xmlTypeAssociatedClearance,l_xmlTypeAssociatedWO,l_xmlTypeTextAttachment,l_xmlTypeURLAttachment,l_xmlTypefileattachment))) "PERMIT"
442 INTO l_xmlType
443 FROM dual;
444
445 l_xmlTypefileattachment:=NULL;
446 l_xmlTypeAssociatedWO:=NULL;
447 l_xmlTypeAssociatedClearance:=NULL;
448 l_xmlTypePermitHeader:=NULL;
449 l_xmlTypeApprover:=NULL;
450 l_xmlTypeTextAttachment := NULL;
451 l_xmlTypeURLAttachment:=NULL;
452
453 END LOOP;
454 SELECT
455 XMLELEMENT("PERMITS",
456 XMLConcat(l_xmlTypeParamList,l_xmlType)) "PERMITS"
457 INTO l_xmlType
458 FROM dual;
459 --l_xmlType will have all concatenated work permit records
460 RETURN l_xmlType.getClobVal();
461
462 END getWorkPermitReportXML;
463
464 /********************************************************************
465 * Procedure : Convert_to_client_time
466 * Purpose : This procedure coverts date from Server Time zone to Client Time Zone
467 *********************************************************************/
468 FUNCTION Convert_to_client_time(
469 p_server_time IN DATE )
470 RETURN DATE
471 IS
472 l_client_tz_id NUMBER;
473 l_server_tz_id NUMBER;
474 l_msg_count NUMBER;
475 l_msg_data VARCHAR2(2000);
476 l_client_time DATE;
477 l_status VARCHAR2(100);
478 BEGIN
479 -- API body
480 l_client_tz_id := to_number ( fnd_profile.value_specific('CLIENT_TIMEZONE_ID'));
481 l_server_tz_id := to_number( fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
482 HZ_TIMEZONE_PUB.Get_Time(1.0, 'F', l_server_tz_id, l_client_tz_id, p_server_time, l_client_time, l_status, l_msg_count, l_msg_data);
483 RETURN l_client_time;
484 -- API body
485 END;
486 /********************************************************************
487 * Procedure : getWorkClearanceReportXML
488 * Purpose : This procedure generate xml data for work clearances
489 *********************************************************************/
490 /*Function getWorkClearanceReportXML
491 ( p_work_clearance_id in system.eam_wipid_tab_type,
492 p_short_attachment_flag in int,
493 p_long_attachment_flag in int,
494 p_file_attachment_flag in int,
495 p_work_request_flag in int,
496 p_asset_bom_flag in int
497 )return CLOB
498 BEGIN
499 --Logic for handling xml data for Work Clearance will be similar to that of the Procedure getWorkPermitReportXML
500 END getWorkClearanceReportXML; */
501
502 END EAM_SAFETY_REPORTS_PVT;
503