1 PACKAGE BODY EAM_WORKORDERREP_PVT AS
2 /* $Header: EAMVWRPB.pls 120.21.12020000.5 2013/03/20 09:20:44 rsandepo ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVWRPB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_WorkOrderRep_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 02-MARCH-2006 Smriti Sharma Initial Creation
21 -- 20-APR-2009 ngoutam Bug 7758322
22 ***************************************************************************/
23
24
25 G_PKG_NAME CONSTANT VARCHAR2(30):='EAM_WorkOrderRep_PVT';
26 Function getWoReportXML
27 (
28 p_wip_entity_id in system.eam_wipid_tab_type,
29 p_operation_flag in int,
30 p_material_flag in int,
31 p_resource_flag in int,
32 p_direct_material_flag in int,
33 p_short_attachment_flag in int,
34 p_long_attachment_flag in int,
35 p_file_attachment_flag in int,
36 p_work_request_flag in int,
37 p_meter_flag in int,
38 p_quality_plan_flag in int,
39 p_asset_bom_flag in int,
40 p_safety_permit_flag in int, -- for permit param
41 p_safety_clearance_flag in int -- for clearance param
42 )return CLOB
43
44 IS
45
46 l_xmlType XMLType:=null;
47 l_xmlType1 XMLType:=null;
48 l_xmlTypeParamList XMLType:=null;
49 l_xmlTypeOperation XMLType:=null;
50 l_xmlTypeResource XMLType:=null;
51 l_xmlTypeEmployee XMLType:=null;
52 l_xmlTypeDirectMaterial XMLType:=null;
53 l_xmlTypeShortAttachment XMLType:=null;
54 l_xmlTypeOpShortAttachment XMLType:=null;
55 l_xmlTypeLongAttachment XMLType:=null;
56 l_xmlTypeOpLongAttachment XMLType:=null;
57 l_xmlTypefileattachment XMLType:=null;
58 l_xmlTypeWorkRequest XMLType:=null;
59 l_xmlTypeMeter XMLType:=null;
60 l_xmlTypeMaterial XMLType:=null;
61 l_xmlTypeQualityPlan XMLType:=null;
62 l_xmlTypeAssetBom XMLType :=null;
63 l_xmlTypeFailureData XMLType:=null;
64 l_xmlTemp XMLType:=null;
65 l_xmlTemp2 XMLType:=null;
66 l_xmlTemp3 XMLType:=null;
67 l_xmlTemp4 XMLType:=null;
68 l_string varchar2(4000);
69 l_wip_entity_name varchar2(100);
70 l_organization_id number;
71 l_org_id number;
72 l_asset_group varchar2(100);
73 l_instance_number varchar2(100):=null;
74 l_serial_number varchar2(100):=null;
75 l_asset_activity varchar2(100):=null;
76 l_media_id number:=0;
77 l_plan_id number;
78 i number:=1;
79 l_temp clob:=null;
80
81 -- FP 7493388 for Base Bug 7005666
82 l_inventory_id number;
83 l_asset_route_flag varchar2(20);
84 l_xmlTypeAssetroutecomp XMLType:=null;
85 -- for Bug 7005666
86
87 l_xmlTypePermits XMLType :=NULL; --permit safety report
88 l_xmlTypeClearances XMLType :=NULL; -- safety clearances
89
90
91 cursor operation_cursor(p_wip_id number) is
92 SELECT XMLELEMENT("OPERATION", XMLFOREST(WO.OPERATION_SEQ_NUM as "OPSEQNUM",
93 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(WO.FIRST_UNIT_START_DATE), calendar_aware => FND_DATE.calendar_aware_alt) as "OPSSHEDULEDSTART",
94 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(WO.LAST_UNIT_COMPLETION_DATE), calendar_aware => FND_DATE.calendar_aware_alt) as "OPSCHEDULEDCOMPLETION",
95 to_char((WO.LAST_UNIT_COMPLETION_DATE-WO.FIRST_UNIT_START_DATE),'99.99') as "OPDURATION",
96 LU2.meaning as "OPSHUTDOWNTYPE",
97 LU1.meaning as "OPCOMPLETED",
98 BS.OPERATION_CODE as "OPCODE",
99 BD.DEPARTMENT_CODE as "OPDEPT",
100 WO.DESCRIPTION as "OPDESC",
101 WO.LONG_DESCRIPTION as "OPLONGDESC",
102 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(eoctv.actual_start_date),calendar_aware => FND_DATE.calendar_aware_alt) as "OPACTUALSTARTDATE",
103 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(eoctv.actual_end_date),calendar_aware => FND_DATE.calendar_aware_alt) as "OPACTUALENDDATE")) Operation
104 FROM
105 eam_op_completion_txns_v eoctv,
106 FND_COMMON_LOOKUPS LU1,
107 MFG_LOOKUPS LU2,
108 WIP_OPERATIONS WO,
109 BOM_STANDARD_OPERATIONS BS,
110 BOM_DEPARTMENTS BD
111 WHERE BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
112 AND NVL(BS.OPERATION_TYPE,1) = 1 and
113 eoctv.wip_entity_id(+)=wo.wip_entity_id and
114 eoctv.operation_seq_num(+)=wo.operation_seq_num
115 AND BS.LINE_ID IS NULL
116 AND WO.WIP_ENTITY_ID = p_wip_id
117 AND LU2.LOOKUP_CODE(+) = WO.SHUTDOWN_TYPE
118 AND LU2.LOOKUP_TYPE(+) = 'BOM_EAM_SHUTDOWN_TYPE'
119 AND LU1.LOOKUP_CODE(+) = WO.OPERATION_COMPLETED
120 AND LU1.LOOKUP_TYPE(+) = 'EAM_YES_NO'
121 AND BS.STANDARD_OPERATION_ID (+) = WO.STANDARD_OPERATION_ID
122 ORDER BY WO.OPERATION_SEQ_NUM;
123
124 --Cursor for Materials
125
126 cursor material_cursor(p_wip_id number) is
127 SELECT XMLELEMENT("MATERIAL",XMLFOREST(WRO.OPERATION_SEQ_NUM as "OPERATIONSEQNUM",
128 milk.concatenated_segments as "REQLOCATORNAME",
129 --M.DESCRIPTION as "REQCOMPDESC",
130 LU.MEANING as "REQTYPE",
131 WRO.SUPPLY_SUBINVENTORY as "REQSUBINVENTORY",
132 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(WRO.DATE_REQUIRED), calendar_aware => FND_DATE.calendar_aware_alt) as "REQDATEREQUIRED",
133 M.PRIMARY_UOM_CODE as "REQUOM",
134 WRO.REQUIRED_QUANTITY as "REQREQUIREDQUANTITY",
135 msikfv.concatenated_segments as "REQITEMNAME",
136 msikfv.description as "REQITEMDESCRIPTION",
137 wro.quantity_issued as "REQISSUEDQUANTITY",
138 eam_material_allocqty_pkg.open_quantity(WRO.WIP_ENTITY_ID,WRO.OPERATION_SEQ_NUM,WRO.ORGANIZATION_ID,WRO.INVENTORY_ITEM_ID,WRO.REQUIRED_QUANTITY,WRO.QUANTITY_ISSUED) as "REQQUANTITYOPEN" ,
139 eam_material_allocqty_pkg.allocated_quantity(WRO.WIP_ENTITY_ID,WRO.OPERATION_SEQ_NUM,WRO.ORGANIZATION_ID,WRO.INVENTORY_ITEM_ID) as "REQALLOCATEDQUANTITY")) Material
140 FROM
141 mtl_system_items_b_kfv msikfv,
142 MTL_SYSTEM_ITEMS M,
143 MTL_ITEM_LOCATIONS L,
144 MFG_LOOKUPS LU,
145 WIP_REQUIREMENT_OPERATIONS WRO,
146 MTL_ITEM_LOCATIONS_KFV milk
147 WHERE
148 msikfv.organization_id = wro.organization_id
149 AND msikfv.inventory_item_id = wro.inventory_item_id
150 AND M.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
151 AND WRO.WIP_ENTITY_ID= p_wip_id
152 AND L.INVENTORY_LOCATION_ID (+) = NVL(WRO.SUPPLY_LOCATOR_ID,'-1')
153 AND milk.INVENTORY_LOCATION_ID (+) = NVL(WRO.SUPPLY_LOCATOR_ID,'-1')
154 AND M.ORGANIZATION_ID = WRO.ORGANIZATION_ID
155 AND L.ORGANIZATION_ID (+) = WRO.ORGANIZATION_ID
156 AND milk.organization_id (+) = wro.organization_id
157 AND LU.LOOKUP_TYPE = 'WIP_SUPPLY_SHORT'
158 AND LU.LOOKUP_CODE = WRO.WIP_SUPPLY_TYPE
159 AND wro.inventory_item_id in (SELECT inventory_item_id
160 FROM mtl_system_items
161 WHERE stock_enabled_flag = 'Y' AND organization_id = msikfv.organization_id) --bug 13685873
162 ORDER BY WRO.OPERATION_SEQ_NUM;
163
164 -- Above order by is changed to be in Synch with Self Service WO Details screen.Bug 9735166
165
166 -- Cursor for Resources
167
168 cursor resource_cursor(p_wip_id number) is
169 SELECT XMLELEMENT("RESOURCE",XMLFOREST(WOR.OPERATION_SEQ_NUM as "RESOPSEQNUM",
170 WOR.RESOURCE_SEQ_NUM as "RESSEQ",
171 WOR.SCHEDULE_SEQ_NUM as "RESSCHEDSEQ",
172 BR.RESOURCE_CODE as "RESCODE",
173 --BR.RESOURCE_ID as "RESRESOURCEID",
174 WOR.USAGE_RATE_OR_AMOUNT as "RESUSAGERATE",
175 WOR.UOM_CODE as "RESUOM",
176 LU.MEANING as "RESBASIS",
177 WOR.APPLIED_RESOURCE_UNITS as "ACTUALHRCHARGED",
178 WOR.ASSIGNED_UNITS as "RESCAPACITY",
179 CA.ACTIVITY as "RESACTIVITY",
180 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(WOR.START_DATE), calendar_aware => FND_DATE.calendar_aware_alt) as "RESSTARTDATE",
181 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(WOR.COMPLETION_DATE), calendar_aware => FND_DATE.calendar_aware_alt) as "RESCOMPLETIONDATE")) Resources
182 FROM
183 BOM_RESOURCES BR,
184 CST_ACTIVITIES CA,
185 MFG_LOOKUPS LU,
186 WIP_OPERATION_RESOURCES WOR
187 WHERE BR.ORGANIZATION_ID = WOR.ORGANIZATION_ID
188 AND WOR.WIP_ENTITY_ID = p_wip_id
189 AND BR.RESOURCE_ID = WOR.RESOURCE_ID
190 AND CA.ACTIVITY_ID(+) = WOR.ACTIVITY_ID
191 AND LU.LOOKUP_CODE = WOR.BASIS_TYPE
192 AND LU.LOOKUP_TYPE = 'CST_BASIS'
193 ORDER BY WOR.RESOURCE_SEQ_NUM;
194
195 --Cursor for Employees
196
197 cursor employee_cursor(p_wip_id number) is
198 SELECT XMLELEMENT("EMPLOYEE",XMLFOREST(
199 wori.operation_seq_num as "EMPOPSEQNO",
200 wori.resource_seq_num as "EMPRESSEQNO",
201 br.resource_code as "EMPRESCODE",
202 br.resource_type as "EMPRESTYPE",
203 ppf.employee_number as "EMPNO",
204 ppf.full_name as "EMPFULLNAME",
205 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(wori.start_date), calendar_aware => FND_DATE.calendar_aware_alt) as "EMPSTARTDATE",
206 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(wori.completion_date), calendar_aware => FND_DATE.calendar_aware_alt) as "EMPENDDATE",
207 bd.department_code as "EMPDEPTCODE")) Employees
208 FROM wip_op_resource_instances wori,
209 wip_operation_resources wor,
210 bom_resources br,
211 bom_resource_employees bre,
212 per_people_f ppf,
213 bom_departments bd
214 WHERE wor.wip_entity_id = wori.wip_entity_id and
215 wor.organization_id = wori.organization_id and
216 wor.operation_seq_num = wori.operation_seq_num and
217 wor.resource_seq_num = wori.resource_seq_num and
218 br.resource_id = wor.resource_id and
219 br.resource_type = 2 and
220 bre.instance_id = wori.instance_id and
221 trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date and
222 ppf.person_id = bre.person_id and
223 wor.department_id=bd.department_id and
224 wor.organization_id=bd.organization_id and
225 wor.wip_entity_id=p_wip_id;
226
227 --Cursor for Direct Materials
228
229 cursor directmaterial_cursor(p_wip_id number) is
230 SELECT XMLELEMENT("DIRECTMATERIAL", XMLFOREST(edrv.task_number as "OPERATIONSEQNO",
231 edrv.service_line_type as "SERVICELINETYPE",
232 edrv.item_description as "ITEMNAME",
233 edrv.description as "ITEMDESC",
234 DECODE(edrv.order_type_lookup_code,'FIXED PRICE',NVL(edrv.amount,0),NVL(edrv.required_quantity, 0)) as "QUANTITYREQUIRED",
235 DECODE(edrv.order_type_lookup_code,'FIXED PRICE',NVL(edrv.rql_amount_ordered,0),NVL(edrv.rql_quantity_ordered,0)) as "REQQUANTITYORDERED",
236 DECODE(edrv.order_type_lookup_code,'FIXED PRICE',NVL(edrv.po_amount_ordered,0),NVL(edrv.po_quantity_ordered,0)) as "POQUANTITYORDERED",
237 DECODE(edrv.order_type_lookup_code,'FIXED PRICE',NVL(edrv.amount_delivered,0),NVL(edrv.quantity_received, 0)) as "QUANTITYRECEIVED",
238 edrv.uom_code as "UOM",
239 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(edrv.date_required), calendar_aware => FND_DATE.calendar_aware_alt) as "DATEREQUIRED")) DirectMaterial
240 FROM EAM_DIRECT_ITEM_RECS_V edrv
241 WHERE edrv.wip_entity_id = p_wip_id;
242
243
244 -- Cursor for Work Order Short Attachments
245
246 cursor shortattachment_cursor(p_wip_id number,p_org_id number) is
247 select XMLELEMENT("SHORTATTACHMENT",XMLFOREST(
248 --fdst.media_id as "AMEDIAID",
249 fdst.short_text as "ASHORTTEXT",
250 fdv.file_name as "AWORKNAME",
251 fdv.description as "AWORKDESC",
252 --fdv.datatype_name as "AWORKTYPE",
253 -- fdv.datatype_id as "AWORKDATATYPEID",
254 --fdv.category_id as "ACATEGORYID",
255 fdv.category_description as "AWORKCATEGORY",
256 fad.seq_num as "ASEQNUM"
257 --fad.entity_name as "AENTITYNAME",
258 /*fad.attached_document_id as "ATTACHWORKDOCID"*/)) ShortAttachment
259 from
260 FND_DOCUMENTS_SHORT_TEXT fdst,
261 fnd_documents_vl fdv,
262 fnd_attached_documents fad
263 where
264 fdst.media_id = fdv.media_id and
265 --fdv.datatype_name = 'Short Text' and
266 -- Changed bug 9081077
267 fdv.datatype_id = 1 AND
268 -- Above is Data Type id for short text.Refer FND_DOCUMENT_DATATYPES table
269 -- fvd.datatype_name fetches user name for data type
270 fad.document_id = fdv.document_id and
271 fad.entity_name = 'EAM_WORK_ORDERS' and
272 fad.pk1_value = to_char(p_org_id) and
273 fad.pk2_value = to_char(p_wip_id)
274 order by fdv.file_name;
275
276 -- Cursor for Work Order Long Attachments
277
278 cursor longattachment_cursor(p_wip_id number,p_org_id number) is
279 select XMLFOREST(
280 fdlt.media_id as "ALONGMEDIAID",
281 --fdlt.long_text as "ALONGTEXT",
282 fdv.file_name as "ALONGWORKNAME",
283 fdv.description as "ALONGWORKDESC",
284 --fdv.datatype_name as "AWORKTYPE",
285 --fdv.datatype_id as "AWORKDATATYPEID",
286 --fdv.category_id as "ACATEGORYID",
287 fdv.category_description as "ALONGWORKCATEGORY",
288 fad.seq_num as "ALONGSEQNUM"
289 --fad.entity_name as "AENTITYNAME",
290 /*fad.attached_document_id as "ATTACHWORKDOCID"*/) LongAttachment
291 from
292 FND_DOCUMENTS_LONG_TEXT fdlt,
293 fnd_documents_vl fdv,
294 fnd_attached_documents fad
295 where
296 fdlt.media_id = fdv.media_id and
297 fdv.datatype_name = 'Long Text' and
298 fad.document_id = fdv.document_id and
299 fad.entity_name = 'EAM_WORK_ORDERS' and
300 fad.pk1_value = to_char(p_org_id) and
301 fad.pk2_value = to_char(p_wip_id)
302 order by fdlt.media_id desc;
303
304 --Cursor for Operation Short Attachments
305
306 cursor opshortattachment(p_wip_id number,p_org_id number) is
307 select XMLELEMENT("OPSHORTATTACHMENT",XMLFOREST(fdst.short_text as "OPASHORTTEXT",
308 fdv.file_name as "OPASHORTNAME",
309 fdv.description as "OPASHORTDESC",
310 fdv.category_description as "OPASHORTCATEGORY",
311 fad.pk2_value as "OPASEQNO")) OpShortAttachment
312 from
313 FND_DOCUMENTS_SHORT_TEXT fdst,
314 fnd_documents_vl fdv,
315 fnd_attached_documents fad
316 where fdst.media_id = fdv.media_id
317 and fad.document_id = fdv.document_id
318 and fad.entity_name = 'EAM_DISCRETE_OPERATIONS'
319 and fdv.datatype_name = 'Short Text'
320 and fad.pk1_value= to_char(p_wip_id)
321 and fad.pk3_value= to_char(p_org_id)
322 order by fdv.file_name;
323
324 -- Cursor for Operation Long Attachments
325 cursor oplongattachment_cursor(p_wip_id number,p_org_id number) is
326 select XMLFOREST(
327 fdlt.media_id as "OPALONGMEDIAID",
328 fdv.file_name as "OPALONGWORKNAME",
329 fdv.description as "OPALONGWORKDESC",
330 fdv.category_description as "OPALONGWORKCATEGORY",
331 fad.pk2_value as "OPALONGSEQNUM") OpLongAttachment
332 from
333 FND_DOCUMENTS_LONG_TEXT fdlt,
334 fnd_documents_vl fdv,
335 fnd_attached_documents fad
336 where
337 fdlt.media_id = fdv.media_id and
338 fad.document_id = fdv.document_id and
339 fdv.datatype_name = 'Long Text' and
340 fad.entity_name = 'EAM_DISCRETE_OPERATIONS' and
341 fad.pk1_value = to_char(p_wip_id) and
342 fad.pk3_value = to_char(p_org_id)
343 order by fdlt.media_id desc;
344
345 --Cursor for File Attachments
346 cursor fileattachment_cursor(p_wip_id number,p_org_id number) is
347 select XMLELEMENT("FILEATTACHMENT",XMLFOREST(
348 fdv.file_name as "AFILEWORKNAME",
349 fdv.description as "AFILEWORKDESC",
350 fdv.category_description as "AFILEWORKCATEGORY")) FileAttachment
351 from
352 fnd_documents_vl fdv,
353 fnd_attached_documents fad
354 where
355 fad.document_id = fdv.document_id and
356 fad.entity_name = 'EAM_WORK_ORDERS' and
357 fad.pk1_value= to_char(p_org_id) and
358 fad.pk2_value= to_char(p_wip_id) and
359 fdv.file_name is not null and
360 fdv.datatype_name NOT IN ('Long Text', 'Short Text');
361
362
363 -- Cursor for Work Requests
364
365 cursor workrequest_cursor(p_wip_id number,p_org_id number) is
366 select XMLELEMENT("WORKREQUEST",XMLFOREST(wewr.work_request_number as "WRNUMBER",
367 wewr.description as "WRDESCRIPTION",
368 ml1.meaning as "WRSTATUS" ,
369 ml2.meaning as "WRPRIORITY",
370 bd.department_code as "WROWNINGDEPT",
371 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(wewr.expected_resolution_date), calendar_aware => FND_DATE.calendar_aware_alt) as "WREXPECTEDRESOLUTIONDATE",
372 fu.user_name as "WRORIGINATOR")) WorkRequest
373 from
374 wip_eam_work_requests wewr,
375 fnd_user fu,
376 bom_departments bd,
377 mfg_lookups ml1,
378 mfg_lookups ml2
379 where
380 fu.user_id = wewr.created_by
381 and bd.department_id = wewr.work_request_owning_dept
382 and ml1.lookup_type (+) = 'WIP_EAM_WORK_REQ_STATUS'
383 and ml1.lookup_code (+) = wewr.work_request_status_id
384 and ml2.lookup_type (+) = 'WIP_EAM_ACTIVITY_PRIORITY'
385 and ml2.lookup_code (+) = wewr.work_request_priority_id
386 and wewr.wip_entity_id =p_wip_id
387 and wewr.organization_id = p_org_id
388 order by wewr.work_request_number ;
389
390 -- Cursor for Meters
391
392 cursor meter_cursor(p_wip_id number) is
393 SELECT XMLELEMENT("METER",XMLFOREST(
394 CTL.NAME as "COUNTERNAME",
395 NVL(CTL2.NAME,CTL.NAME) as "SOURCECOUNTERNAME",
396 NVL(CBS.COUNTER_TYPE,CB.COUNTER_TYPE) as "COUNTERTYPE",
397 NVL(CCR2.COUNTER_READING,CCR1.COUNTER_READING) as "LASTREADING",
398 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(NVL(CCR2.VALUE_TIMESTAMP,CCR1.VALUE_TIMESTAMP)), calendar_aware => FND_DATE.calendar_aware_alt) as "LASTVALUETIMESTAMP",
399 CB.uom_code as "METERUOM",
400 TO_NUMBER(NULL) as "NEWREADING",
401 EAM_METERS_UTIL.IS_METER_READING_MANDATORY_V(p_wip_id, nvl(CBS.COUNTER_ID,CB.COUNTER_ID)) as "MANDATORY" )) Meter
402 FROM csi_counters_b CB,
403 CSI_COUNTERS_TL CTL,
404 CSI_COUNTERS_TL CTL2,
405 CSI_COUNTERS_B CBS,
406 CSI_COUNTER_READINGS CCR1,
407 CSI_COUNTER_READINGS CCR2,
408 csi_item_instances cii,
409 wip_discrete_jobs wdj,
410 (
411 SELECT
412 *
413 FROM csi_counter_associations
414 WHERE sysdate BETWEEN nvl(start_date_Active,sysdate-1) AND nvl(end_date_active,sysdate+1)
415 )
416 CCA,
417 (
418 SELECT
419 *
420 FROM csi_counter_relationships
421 WHERE sysdate BETWEEN nvl(Active_end_date,sysdate-1) AND nvl(active_end_date,sysdate+1)
422 )
423 CCR
424 WHERE
425 CB.COUNTER_ID=CCA.COUNTER_ID(+)
426 AND CB.COUNTER_ID=CTL.COUNTER_ID
427 AND CCR.OBJECT_COUNTER_ID(+)=CB.COUNTER_ID
428 AND CCR.SOURCE_COUNTER_ID=CBS.COUNTER_ID(+)
429 AND CBS.COUNTER_ID=CTL2.COUNTER_ID(+)
430 AND CTL.LANGUAGE=USERENV('LANG')
431 AND CTL2.LANGUAGE(+)=USERENV('LANG')
432 AND CB.COUNTER_TYPE='REGULAR'
433 AND CBS.COUNTER_TYPE(+)='REGULAR'
434 AND CB.COUNTER_ID=CCR1.COUNTER_ID(+)
435 AND CCR1.DISABLED_FLAG(+)='N'
436 AND wdj.maintenance_object_id=cii.instance_id
437 AND wdj.maintenance_object_type=3
438 AND cca.source_object_id = cii.instance_id
439 AND wdj.wip_entity_id=p_wip_id
440 AND
441 (
442 CCR1.VALUE_TIMESTAMP =
443 (
444 SELECT
445 MAX(VALUE_TIMESTAMP)
446 FROM CSI_COUNTER_READINGS B
447 WHERE CCR1.COUNTER_ID=B.COUNTER_ID
448 AND B.DISABLED_FLAG='N'
449 GROUP BY COUNTER_ID
450 )
451 OR NOT EXISTS
452 (
453 SELECT
454 COUNTER_ID
455 FROM CSI_COUNTER_READINGS B
456 WHERE CB.COUNTER_ID=B.COUNTER_ID
457 AND B.DISABLED_FLAG='N'
458 )
459 )
460 AND CBS.COUNTER_ID=CCR2.COUNTER_ID(+)
461 AND
462 (
463 CCR2.VALUE_TIMESTAMP =
464 (
465 SELECT
466 MAX(VALUE_TIMESTAMP)
467 FROM CSI_COUNTER_READINGS B
468 WHERE CCR2.COUNTER_ID=B.COUNTER_ID
469 AND B.DISABLED_FLAG='N'
470 GROUP BY COUNTER_ID
471 )
472 OR NOT EXISTS
473 (
474 SELECT
475 COUNTER_ID
476 FROM CSI_COUNTER_READINGS B
477 WHERE CBS.COUNTER_ID=B.COUNTER_ID
478 AND B.DISABLED_FLAG='N'
479 )
480 )
481 AND CCR2.DISABLED_FLAG(+)='N';
482
483
484 --Cursor for Quality Plan
485 cursor qualityplan_cursor(p_quality_flag int, p_organization_id number,p_wip_entity_name varchar2 , p_asset_group varchar2,
486 p_instance_number varchar2,p_serial_number varchar2,p_asset_activity varchar2) is
487 Select XMLFOREST(
488 qpv.plan_id as "PLANID",
489 ml.meaning as "PLANMANDATORY",
490 qpv.DESCRIPTION as "PLANDESCRIPTION",
491 qpv.NAME as "PLANNAME",
492 qpv.plan_type_meaning as "PLANTYPE") QualityPlan
493 FROM
494 QA_PLANS_VAL_V qpv,
495 qa_plan_transactions qpt,
496 mfg_lookups ml
497 Where qpt.plan_id=qpv.plan_id
498 and ml.lookup_code=qpt.mandatory_collection_flag
499 and ml.lookup_type='SYS_YES_NO'
500 and nvl(p_quality_flag,2)=decode(p_quality_flag,1,qpt.mandatory_collection_flag,2,p_quality_flag,2)
501 and decode(qpt.transaction_number,31,qa_web_txn_api.plan_applies(qpv.plan_id,qpt.transaction_number,to_char(p_organization_id),
502 p_asset_group,p_serial_number,p_asset_activity,p_wip_entity_name,'',p_instance_number,'','','','','EAM'),'N')='Y';
503
504 --Cursor for Quality Plan Elements
505 cursor qplanelement_cursor(p_plan_id number) is
506 select XMLELEMENT("ELEMENTNAME",q.prompt ) QPlanElement
507 from QA_PLAN_CHARS q
508 where q.plan_id=p_plan_id;
509
510 --Cursor for Asset BOM
511 cursor assetbom_cursor(p_wip_id in number) is
512 select XMLELEMENT("ITEM",XMLFOREST(msik.concatenated_segments as "COMPONENTITEM",
513 msik.description as "DESCRIPTION",
514 bic.component_quantity as "QUANTITY",
515 msik.primary_uom_code as "UOM",
516 lu.meaning as "SUPPLYTYPE")) AssetBom
517 from bom_inventory_components bic,
518 mtl_system_items_kfv msik,
519 wip_discrete_jobs wdj,
520 csi_item_instances cii,
521 bom_bill_of_materials bbom,
522 mfg_lookups lu
523 where bic.effectivity_date <= sysdate
524 and (bic.disable_date >= sysdate or
525 bic.disable_date is null)
526 and wdj.maintenance_object_id = cii.instance_id
527 and wdj.maintenance_object_type=3
528 and wdj.wip_entity_id =p_wip_id
529 and cii.inventory_item_id = bbom.assembly_item_id
530 and wdj.organization_id = bbom.organization_id
531 and bic.bill_sequence_id=bbom.common_bill_sequence_id
532 and bic.component_item_id=msik.inventory_item_id
533 and msik.organization_id=wdj.organization_id
534 and lu.lookup_type(+) = 'EAM_CONSTANTS.G_SUPPLY_TYPE'
535 and lu.lookup_code(+) = bic.wip_supply_type
536 and (wdj.rebuild_item_id is not null OR
537 ((NVL(bic.from_end_item_unit_number,'0') = '0') OR bic.from_end_item_unit_number <= cii.serial_number)
538 and ( bic.to_end_item_unit_number >=cii.serial_number or NVL(bic.to_end_item_unit_number,'0')='0')
539 )
540
541 union all
542
543 select XMLELEMENT("ITEM",XMLFOREST(msik.concatenated_segments as "COMPONENTITEM",
544 msik.description as "DESCRIPTION",
545 bic.component_quantity as "QUANTITY",
546 msik.primary_uom_code as "UOM",
547 lu.meaning as "SUPPLYTYPE")) AssetBom
548 from bom_inventory_components bic,
549 mtl_system_items_kfv msik,
550 wip_discrete_jobs wdj,
551 bom_bill_of_materials bbom,
552 mfg_lookups lu
553 where bic.effectivity_date <= sysdate
554 and (bic.disable_date >= sysdate or
555 bic.disable_date is null)
556 and wdj.wip_entity_id =p_wip_id
557 and wdj.maintenance_object_type=2
558 and wdj.maintenance_object_id = bbom.assembly_item_id
559 and wdj.organization_id = bbom.organization_id
560 and bic.bill_sequence_id = bbom.common_bill_sequence_id
561 and bic.component_item_id=msik.inventory_item_id
562 and wdj.organization_id = msik.organization_id
563 and lu.lookup_type(+) = 'EAM_CONSTANTS.G_SUPPLY_TYPE'
564 and lu.lookup_code(+) = bic.wip_supply_type;
565
566 --begin changes for FP 7493388 (base bug 7005666)
567 --cursor for Asset Route
568
569 cursor assetroutecomp_cursor(l_serial_number varchar2,l_inventory_id number) is
570 SELECT XMLELEMENT("ASSETROUTECOMP", XMLFOREST(
571 R_components_name as "ASSET_NUMBER",
572 R_component_group as "ASSET_GROUP",
573 R_component_description as "DESCRIPTION",
574 area as "AREA")) AssetRoutecomp
575 from (select mena.network_serial_number R_asset_number,
576 msn1.concatenated_segments R_asset_group,
577 mena.asset_number R_components_name,
578 msn2.concatenated_segments R_component_group,
579 msn2.descriptive_text R_component_description,
580 msn2.area area,
581 mena.network_item_id R_asset_group_id,
582 mena.organization_id R_asset_org_id
583 from
584 mtl_eam_network_assets_v mena
585 , mtl_eam_asset_numbers_v msn1
586 , mtl_eam_asset_numbers_v msn2
587 ,csi_item_instances cii
588 where
589 mena.organization_id=msn1.current_organization_id
590 and mena.network_item_id=msn1.inventory_item_id
591 and mena.network_serial_number=msn1.serial_number
592 and mena.organization_id=msn2.current_organization_id
593 and mena.inventory_item_id=msn2.inventory_item_id
594 and mena.serial_number=msn2.serial_number
595 AND mena.network_object_id = cii.instance_id
596 AND cii.instance_number=l_instance_number
597 and nvl(mena.end_date_active, sysdate+1) > sysdate /*bug12836608*/
598 order by mena.serial_number) ;
599
600 --end changes for FP 7493388 (base bug 7005666)
601
602
603 -- Cursor for Safety Permit
604 CURSOR workpermit_cursor(p_wip_id NUMBER,p_org_id NUMBER)
605 IS
606 SELECT XMLELEMENT("WORKPERMIT",
607 XMLFOREST(QRSLT.PERMIT_NAME AS permitName,
608 QRSLT.permit_type AS permitType,
609 QRSLT.DESCRIPTION AS permitDesc,
610 QRSLT.PERMIT_STATUS AS permitStatus,
611 QRSLT.permit_valid_from AS permitValidFrom,
612 QRSLT.permit_valid_to AS permitValidTo
613 )) workpermit
614 FROM
615 (
616 Select
617 EWP.PERMIT_NAME,
618 ml.meaning AS permit_type,
619 EWP.DESCRIPTION,
620 EPSV.PERMIT_STATUS,
621 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(EWP.VALID_FROM), calendar_aware => FND_DATE.calendar_aware_alt)AS permit_valid_from,
622 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(EWP.VALID_TO), calendar_aware => FND_DATE.calendar_aware_alt) AS permit_valid_to
623 from
624 EAM_WORK_PERMITS EWP,
625 EAM_SAFETY_ASSOCIATIONS ESA,
626 EAM_PERMIT_STATUSES_VL EPSV,
627 mfg_lookups ml
628 WHERE EWP.PERMIT_ID = ESA.SOURCE_ID
629 AND ESA.TARGET_REF_ID =p_wip_id
630 AND EWP.ORGANIZATION_ID =p_org_id
631 AND EPSV.STATUS_ID =EWP.USER_DEFINED_STATUS_ID
632 AND ml.lookup_type (+) = 'EAM_WORK_PERMIT_TYPE'
633 AND ml.lookup_code=ewp.permit_type
634
635 union
636
637 SELECT DISTINCT EWP1.PERMIT_NAME ,
638 ml1.meaning ,
639 EWP1.DESCRIPTION ,
640 EPSV1.PERMIT_STATUS ,
641 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(EWP1.VALID_FROM), calendar_aware => FND_DATE.calendar_aware_alt) AS permitValidFrom,
642 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(EWP1.VALID_TO), calendar_aware => FND_DATE.calendar_aware_alt) AS permitValidTo
643
644 FROM EAM_WORK_PERMITS EWP1,
645 EAM_SAFETY_ASSOCIATIONS ESA1,
646 eam_safety_associations esa2,
647 EAM_PERMIT_STATUSES_VL EPSV1,
648 mfg_lookups ml1
649 WHERE EWP1.PERMIT_ID = ESA1.SOURCE_ID
650 AND ESA2.TARGET_REF_ID =p_wip_id
651 AND esa2.source_id =esa1.target_ref_id
652 AND esa2.association_type =4
653 AND esa1.association_type =2
654 AND esa1.source_id =ewp1.permit_id
655 AND EWP1.ORGANIZATION_ID =p_org_id
656 AND EPSV1.STATUS_ID =EWP1.USER_DEFINED_STATUS_ID
657 AND ml1.lookup_type (+) = 'EAM_WORK_PERMIT_TYPE'
658 AND ml1.lookup_code=ewp1.permit_type
659 and ewp1.PERMIT_ID not in (
660 SELECT permit.permit_id
661 FROM eam_work_permits permit,
662 eam_safety_associations esa ,
663 wip_discrete_jobs wdj ,
664 eam_permit_statuses_vl epsv
665 WHERE esa.target_ref_id=wdj.wip_entity_id
666 AND esa.source_id =permit.permit_id
667 AND esa.association_type =3
668 AND epsv.status_id =permit.user_defined_status_id
669 AND wdj.organization_id = esa.organization_id
670 AND wdj.wip_entity_id = p_wip_id
671 )
672 ORDER BY permit_name
673 ) QRSLT
674 ;
675
676 --Cursor for Safety Clearance
677 CURSOR workclearance_cursor(p_wip_id NUMBER)
678 IS
679 SELECT XMLELEMENT("WORKCLEARANCE",
680 XMLFOREST(
681 QRSLT.WORK_CLEARANCE_NAME as workClearanceName ,
682 QRSLT.DESCRIPTION as description ,
683 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(QRSLT.SCHEDULED_ESTAB_START_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS schedEstabStDate,
684 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(QRSLT.SCHEDULED_ESTAB_END_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS schedEstabEndDate,
685 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(QRSLT.SCHEDULED_REESTAB_START_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS schedReEstabStDate,
686 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(QRSLT.SCHEDULED_REESTAB_END_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS schedReEstabEndDate,
687 QRSLT.user_status AS userStatus,
688 QRSLT.isolation_type AS isolationType )) workclearance
689 FROM
690 (
691 SELECT
692 ewc.WORK_CLEARANCE_NAME,
693 ewc.DESCRIPTION,
694 ewc.SCHEDULED_ESTAB_START_DATE,
695 ewc.SCHEDULED_ESTAB_END_DATE,
696 ewc.SCHEDULED_REESTAB_START_DATE,
697 ewc.SCHEDULED_REESTAB_END_DATE,
698 DECODE(ewc.pending_flag,'Y',ewsv.work_clearance_status
699 ||'-'
700 ||fnd_message.get_string('EAM','EAM_PENDING_TEXT'), ewsv.work_clearance_status) AS user_status,
701 ml2.meaning AS isolation_type
702 FROM
703 eam_work_clearances ewc ,
704 eam_safety_associations esa ,
705 wip_discrete_jobs wdj ,
706 mfg_lookups ml1 ,
707 mfg_lookups ml2 ,
708 EAM_WORK_CLEARANCE_STATUSES_VL ewsv,
709 eam_isolations iso
710 WHERE esa.target_ref_id =wdj.wip_entity_id
711 AND esa.source_id =ewc.WORK_CLEARANCE_ID
712 AND esa.association_type =4
713 AND ml1.lookup_code(+) = ewc.status_type
714 AND ml2.lookup_code(+) = iso.isolation_type
715 AND ml1.lookup_type (+) = 'EAM_WORK_CLEARANCE_STATUS'
716 AND ml2.lookup_type (+) = 'EAM_ISOLATION_TYPE'
717 AND ewc.last_isolation_id = iso.isolation_id(+)
718 AND ewsv.status_id =ewc.user_defined_status_id
719 AND wdj.organization_id = esa.organization_id
720 AND wdj.wip_entity_id = p_wip_id
721
722 UNION
723
724 SELECT DISTINCT
725 ewc1.WORK_CLEARANCE_NAME ,
726 ewc1.DESCRIPTION ,
727 ewc1.SCHEDULED_ESTAB_START_DATE ,
728 ewc1.SCHEDULED_ESTAB_END_DATE ,
729 ewc1.SCHEDULED_REESTAB_START_DATE ,
730 ewc1.SCHEDULED_REESTAB_END_DATE ,
731 DECODE(ewc1.pending_flag,'Y',ewsv1.work_clearance_status
732 ||'-'
733 ||fnd_message.get_string('EAM','EAM_PENDING_TEXT'), ewsv1.work_clearance_status) AS user_status,
734 ml4.meaning AS isolation_type
735 FROM eam_work_clearances ewc1 ,
736 eam_work_permits permit ,
737 eam_safety_associations esa1 ,
738 eam_safety_associations esa2 ,
739 mfg_lookups ml3 ,
740 mfg_lookups ml4 ,
741 eam_work_clearance_statuses_vl ewsv1,
742 eam_isolations iso1 ,
743 wip_discrete_jobs wdj1
744 WHERE esa2.source_id =permit.permit_id
745 AND esa2.target_ref_id =ewc1.work_clearance_id
746 AND esa2.association_type =2
747 AND esa1.target_ref_id =wdj1.wip_entity_id
748 AND esa1.source_id =permit.permit_id
749 AND esa1.association_type =3
750 AND ml3.lookup_code(+) = ewc1.status_type
751 AND ml4.lookup_code(+) = iso1.isolation_type
752 AND ml3.lookup_type (+) = 'EAM_WORK_CLEARANCE_STATUS'
753 AND ml4.lookup_type (+) = 'EAM_ISOLATION_TYPE'
754 AND ewc1.last_isolation_id = iso1.isolation_id(+)
755 AND ewsv1.status_id =ewc1.user_defined_status_id
756 AND wdj1.wip_entity_id =p_wip_id
757 AND ewc1.work_clearance_id NOT IN
758 (SELECT ewc.WORK_CLEARANCE_ID
759 FROM eam_work_clearances ewc,
760 eam_safety_associations esa ,
761 wip_discrete_jobs wdj
762 WHERE esa.target_ref_id=wdj.wip_entity_id
763 AND esa.source_id =ewc.WORK_CLEARANCE_ID
764 AND esa.association_type =4
765 AND wdj.organization_id = esa.organization_id
766 AND wdj.wip_entity_id = p_wip_id)
767 ORDER BY WORK_CLEARANCE_NAME
768 )QRSLT;
769
770
771
772 BEGIN
773
774 for i in p_wip_entity_id.FIRST..p_wip_entity_id.LAST
775 loop
776
777 select wdj.organization_id into l_org_id
778 from wip_discrete_jobs wdj
779 where wdj.wip_entity_id=p_wip_entity_id(i);
780
781 --begin changes for FP 7493388 (base bug 7005666)
782
783 select Nvl(wdj.ASSET_NUMBER,wdj.REBUILD_SERIAL_NUMBER),Nvl(wdj.ASSET_GROUP_ID,REBUILD_ITEM_ID) into l_serial_number ,l_inventory_id
784 from wip_discrete_jobs wdj
785 where wdj.wip_entity_id=p_wip_entity_id(i);
786
787
788 SELECT nvl(instance_number,'hghg') into l_instance_number
789 FROM eam_work_orders_v
790 WHERE wip_entity_id=p_wip_entity_id(i);
791
792 --Exception block added for bug 7758322
793 BEGIN
794 SELECT NETWORK_ASSET_FLAG into l_asset_route_flag
795 FROM csi_item_instances
796 WHERE INSTANCE_NUMBER = l_instance_number
797 AND INVENTORY_ITEM_ID = l_inventory_id
798 AND SERIAL_NUMBER = l_serial_number;
799
800 EXCEPTION
801 WHEN No_Data_Found THEN
802 l_asset_route_flag:='N';
803 END;
804
805 --end changes for FP 7493388 (base bug 7005666)
806 -- changes for FP 7658452
807
808 l_xmlTypeParamList := xmltype('<PARAM_LIST>
809 <OP_PARAM>'|| p_operation_flag || '</OP_PARAM>
810 <INV_PARAM>' || p_material_flag|| '</INV_PARAM>
811 <DIRECT_PARAM>' ||p_direct_material_flag|| '</DIRECT_PARAM>
812 <RES_PARAM>' ||p_resource_flag|| '</RES_PARAM>
813 <WOREQ_PARAM>' ||p_work_request_flag|| '</WOREQ_PARAM>
814 <METER_PARAM>' ||p_meter_flag|| '</METER_PARAM>
815 <PLAN_PARAM>' ||p_quality_plan_flag|| '</PLAN_PARAM>
816 <SHORTATTACH_PARAM>' ||p_short_attachment_flag|| '</SHORTATTACH_PARAM>
817 <LONGATTACH_PARAM>' ||p_long_attachment_flag|| '</LONGATTACH_PARAM>
818 <FILEATTACH_PARAM>' ||p_file_attachment_flag|| '</FILEATTACH_PARAM>
819 <BOM_PARAM>' ||p_asset_bom_flag|| '</BOM_PARAM>
820 <PERMITS_PARAM>' ||p_safety_permit_flag|| '</PERMITS_PARAM>
821 <CLEARANCES_PARAM>' ||p_safety_clearance_flag|| '</CLEARANCES_PARAM>
822 </PARAM_LIST>');
823
824 -- end changes for FP 7658452
825
826 --Adding Operations
827
828 if p_operation_flag = 1 then
829 begin
830 for operation_record in operation_cursor(p_wip_entity_id(i)) loop
831 select XMLConcat(l_xmlTypeOperation,operation_record.Operation) into l_xmlTypeOperation from dual;
832 end loop;
833 select XMLELEMENT("OPERATION_LIST",l_xmlTypeOperation) into l_xmlTypeOperation from dual;
834 exception
835 when NO_DATA_FOUND then
836 null;
837 end;
838 end if;
839
840 --Adding Materials
841 if p_material_flag = 1 then
842 begin
843 for material_record in material_cursor(p_wip_entity_id(i)) loop
844 select XMLConcat(l_xmlTypeMaterial,material_record.Material) into l_xmlTypeMaterial from dual;
845 end loop;
846 select XMLELEMENT("MATERIAL_LIST",l_xmlTypeMaterial) into l_xmlTypeMaterial from dual;
847 exception
848 when NO_DATA_FOUND then
849 null;
850 end;
851 end if;
852
853 --Adding Resources
854 if p_resource_flag = 1 then
855 begin
856 for resource_record in resource_cursor(p_wip_entity_id(i)) loop
857 select XMLConcat(l_xmlTypeResource,resource_record.Resources) into l_xmlTypeResource from dual;
858 end loop;
859 select XMLELEMENT("RESOURCE_LIST",l_xmlTypeResource) into l_xmlTypeResource from dual;
860 --Adding Employees
861 for employee_record in employee_cursor(p_wip_entity_id(i)) loop
862 select XMLConcat(l_xmlTypeEmployee,employee_record.Employees) into l_xmlTypeEmployee from dual;
863 end loop;
864 select XMLELEMENT("EMPLOYEE_LIST",l_xmlTypeEmployee) into l_xmlTypeEmployee from dual;
865 select XMLConcat(l_xmlTypeResource,l_xmlTypeEmployee) into l_xmlTypeResource from dual;
866 exception
867 when NO_DATA_FOUND then
868 null;
869 end;
870 end if;
871
872 --Adding Direct Materials
873 if p_direct_material_flag = 1 then
874 begin
875 for directmaterial_record in directmaterial_cursor(p_wip_entity_id(i)) loop
876 select XMLConcat(l_xmlTypeDirectMaterial,directmaterial_record.DirectMaterial) into l_xmlTypeDirectMaterial from dual;
877 end loop;
878 select XMLELEMENT("DIRECTMATERIAL_LIST",l_xmlTypeDirectMaterial) into l_xmlTypeDirectMaterial from dual;
879 exception
880 when NO_DATA_FOUND then
881 null;
882 end;
883 end if;
884
885 --Adding Work Order Short Text Attachments
886 if p_short_attachment_flag =1 then
887 begin
888 for attachment_record in shortattachment_cursor(p_wip_entity_id(i),l_org_id) loop
889 select XMLConcat(l_xmlTypeShortAttachment,attachment_record.ShortAttachment) into l_xmlTypeShortAttachment from dual;
890 end loop;
891 select XMLELEMENT("WOSHORTATTACHMENT_LIST",l_xmlTypeShortAttachment) into l_xmlTypeShortAttachment from dual;
892 exception
893 when NO_DATA_FOUND then
894 null;
895 end;
896
897 --Adding Operation Short Text Attachments
898 begin
899 for opattachment_record in opshortattachment(p_wip_entity_id(i),l_org_id) loop
900 select XMLConcat(l_xmlTypeOpShortAttachment,opattachment_record.OpShortAttachment) into l_xmlTypeOpShortAttachment from dual;
901 end loop;
902 select XMLELEMENT("OPSHORTATTACHMENT_LIST",l_xmlTypeOpShortAttachment) into l_xmlTypeOpShortAttachment from dual;
903 select XMLConcat(l_xmlTypeShortAttachment,l_xmlTypeOpShortAttachment) into l_xmlTypeShortAttachment from dual;
904 exception
905 when NO_DATA_FOUND then
906 null;
907 end;
908 end if;
909
910 --Adding Work Order Long Text Attachments
911 if p_long_attachment_flag =1 then
912 begin
913 for longattachment_record in longattachment_cursor(p_wip_entity_id(i),l_org_id) loop
914 select longattachment_record.LongAttachment into l_xmlTemp from dual;
915 select extractValue(l_xmlTemp,'/ALONGMEDIAID') into l_media_id from dual;
916 l_temp:=getLong(p_wip_entity_id(i),l_org_id,l_media_id,1);
917 l_temp := replace(l_temp, '&', '&');
918 l_temp := replace(l_temp, '<', '<');
919 l_temp := replace(l_temp, '>', '>');
920 select XMLConcat(l_xmlTemp,xmlType('<ALONGTEXT>'||l_temp||'</ALONGTEXT>')) into l_xmlTemp from dual;
921 select XMLELEMENT("LONGATTACHMENT",l_xmlTemp) into l_xmlTemp from dual;
922 select XMLConcat(l_xmlTemp,l_xmlTypeLongAttachment) into l_xmlTypeLongAttachment from dual;
923 l_xmlTemp:=null;
924 end loop;
925 select XMLELEMENT("WOLONGATTACHMENT_LIST",l_xmlTypeLongAttachment) into l_xmlTypeLongAttachment from dual;
926 exception
927 when NO_DATA_FOUND then
928 null;
929 end;
930
931 --Adding Operation Long Text Attachments
932 begin
933 for oplongattachment_record in oplongattachment_cursor(p_wip_entity_id(i),l_org_id) loop
934 select oplongattachment_record.OpLongAttachment into l_xmlTemp from dual;
935 select extractValue(l_xmlTemp,'/OPALONGMEDIAID') into l_media_id from dual;
936 l_temp:=getLong(p_wip_entity_id(i),l_org_id,l_media_id,2);
937 l_temp := replace(l_temp, '&', '&');
938 l_temp := replace(l_temp, '<', '<');
939 l_temp := replace(l_temp, '>', '>');
940 select XMLConcat(l_xmlTemp,xmlType('<OPALONGTEXT>'||l_temp||'</OPALONGTEXT>')) into l_xmlTemp from dual;
941 select XMLELEMENT("OPLONGATTACHMENT",l_xmlTemp) into l_xmlTemp from dual;
942 select XMLConcat(l_xmlTemp,l_xmlTypeOpLongAttachment) into l_xmlTypeOpLongAttachment from dual;
943 l_xmlTemp:=null;
944 end loop;
945 select XMLELEMENT("OPLONGATTACHMENT_LIST",l_xmlTypeOpLongAttachment) into l_xmlTypeOpLongAttachment from dual;
946 select XMLConcat(l_xmlTypeLongAttachment,l_xmlTypeOpLongAttachment) into l_xmlTypeLongAttachment from dual;
947 exception
948 when NO_DATA_FOUND then
949 null;
950 end;
951 end if;
952
953 --Adding File Attachments
954 if p_file_attachment_flag = 1 then
955 begin
956 for fileattachment_record in fileattachment_cursor(p_wip_entity_id(i),l_org_id) loop
957 select XMLConcat(l_xmlTypefileattachment,fileattachment_record.FileAttachment) into l_xmlTypefileattachment from dual;
958 end loop;
959 select XMLELEMENT("FILEATTACHMENT_LIST",l_xmlTypefileattachment) into l_xmlTypefileattachment from dual;
960 exception
961 when NO_DATA_FOUND then
962 null;
963 end;
964 end if;
965
966 --Adding Work Request
967 if p_work_request_flag =1 then
968 begin
969 for workrequest_record in workrequest_cursor(p_wip_entity_id(i),l_org_id) loop
970 select XMLConcat(l_xmlTypeWorkRequest,workrequest_record.WorkRequest) into l_xmlTypeWorkRequest from dual;
971 end loop;
972 select XMLELEMENT("WORKREQUEST_LIST",l_xmlTypeWorkRequest) into l_xmlTypeWorkRequest from dual;
973 exception
974 when NO_DATA_FOUND then
975 null;
976 end;
977 end if;
978
979
980 --Adding Meters
981 if p_meter_flag =1 then
982 begin
983 for meter_record in meter_cursor(p_wip_entity_id(i)) loop
984 select XMLConcat(l_xmlTypeMeter,meter_record.Meter) into l_xmlTypeMeter from dual;
985 end loop;
986 select XMLELEMENT("METER_LIST",l_xmlTypeMeter) into l_xmlTypeMeter from dual;
987 exception
988 when NO_DATA_FOUND then
989 null;
990 end;
991 end if;
992
993 --Adding Quality Plan
994 if p_quality_plan_flag =1 or p_quality_plan_flag=2 then
995 begin
996 select we.wip_entity_name,
997 wdj.organization_id,
998 msi.concatenated_segments,
999 cii.instance_number,cii.serial_number into l_wip_entity_name,l_organization_id,l_asset_group,l_instance_number,l_serial_number
1000 from wip_discrete_jobs wdj,
1001 csi_item_instances cii,
1002 mtl_system_items_b_kfv msi,
1003 wip_entities we
1004 where wdj.wip_entity_id=p_wip_entity_id(i)
1005 and wdj.wip_entity_id=we.wip_entity_id
1006 and cii.instance_id(+) = DECODE(wdj.maintenance_object_type,3,wdj.maintenance_object_id,NULL)
1007 and nvl(wdj.asset_group_id,wdj.rebuild_item_id) = msi.inventory_item_id
1008 and wdj.organization_id=msi.organization_id;
1009
1010 begin
1011 select msi.concatenated_segments into l_asset_activity
1012 from mtl_system_items_b_kfv msi,
1013 wip_discrete_jobs wdj
1014 where wdj.primary_item_id=msi.inventory_item_id and
1015 wdj.organization_id=msi.organization_id and
1016 wdj.wip_entity_id=p_wip_entity_id(i) ;
1017 exception
1018 when no_data_found then
1019 null;
1020 end;
1021
1022 for qualityplan_record in qualityplan_cursor(p_quality_plan_flag,l_organization_id,l_wip_entity_name,l_asset_group,l_instance_number,l_serial_number,l_asset_activity) loop
1023 select XMLConcat(l_xmlTypeQualityPlan,qualityplan_record.QualityPlan) into l_xmlTypeQualityPlan from dual;
1024 select extractValue(l_xmlTypeQualityPlan,'/PLANID') into l_plan_id from dual;
1025 l_xmlTemp2 := null;
1026 for qplanelement_record in qplanelement_cursor(l_plan_id) loop
1027 select qplanelement_record.QPlanElement into l_xmlTemp from dual;
1028 select XMLConcat(l_xmlTemp,xmlType('<VALUE> </VALUE>')) into l_xmlTemp from dual;
1029 select XMLELEMENT("ELEMENT",l_xmlTemp) into l_xmlTemp from dual;
1030 select XMLConcat(l_xmlTemp2,l_xmlTemp) into l_xmlTemp2 from dual;
1031 end loop;
1032 select XMLELEMENT("ELEMENT_LIST",l_xmlTemp2) into l_xmlTemp2 from dual;
1033 select XMLConcat(l_xmlTypeQualityPlan,l_xmlTemp2) into l_xmlTypeQualityPlan from dual;
1034 select XMLELEMENT("QUALITYPLAN",l_xmlTypeQualityPlan) into l_xmlTypeQualityPlan from dual;
1035 --changed bug fix 8289633
1036 SELECT XMLConcat(l_xmlTemp4,l_xmlTypeQualityPlan)INTO l_xmlTemp4 FROM dual;
1037 l_xmlTypeQualityPlan:=NULL;
1038 end loop;
1039 select XMLELEMENT("QUALITYPLAN_LIST",l_xmlTemp4) into l_xmlTypeQualityPlan from dual;
1040 --end of change bug fix 8289633
1041
1042 exception
1043 when NO_DATA_FOUND then
1044 null;
1045 end;
1046 end if;
1047
1048 --Adding Asset BOM
1049 if p_asset_bom_flag =1 then
1050 begin
1051 for assetbom_record in assetbom_cursor(p_wip_entity_id(i)) loop
1052 select XMLConcat(l_xmlTypeAssetBom,assetbom_record.AssetBom) into l_xmlTypeAssetBom from dual;
1053 end loop;
1054 select XMLELEMENT("ASSETBOM_LIST",l_xmlTypeAssetBom) into l_xmlTypeAssetBom from dual;
1055 exception
1056 when NO_DATA_FOUND then
1057 null;
1058 end;
1059 end if;
1060
1061 --begin changes for FP 7493388 (base bug 7005666)
1062 --Adding Asset route components
1063 if l_asset_route_flag = 'Y' then
1064 begin
1065
1066 for asset_route_record in assetroutecomp_cursor(l_serial_number,l_inventory_id) loop
1067 select XMLConcat(l_xmlTypeAssetroutecomp,asset_route_record.AssetRoutecomp) into l_xmlTypeAssetroutecomp from dual;
1068 end loop;
1069 select XMLELEMENT("ASSETROUTECOMP_LIST",l_xmlTypeAssetroutecomp) into l_xmlTypeAssetroutecomp from dual;
1070 exception
1071 when NO_DATA_FOUND then
1072 null;
1073 end;
1074 end if;
1075 --end changes for FP 7493388 (base bug 7005666)
1076
1077 --begin adding safety permit
1078 if p_safety_permit_flag = 1 then
1079 begin
1080 for permits_record in workpermit_cursor(p_wip_entity_id(i),l_org_id) loop
1081 select XMLConcat(l_xmlTypePermits,permits_record.workpermit) into l_xmlTypePermits from dual;
1082 end loop;
1083 select XMLELEMENT("WORKPERMIT_LIST",l_xmlTypePermits) into l_xmlTypePermits from dual;
1084 exception
1085 when NO_DATA_FOUND then
1086 null;
1087 end;
1088 end if;
1089 --end adding safety permit
1090
1091 --begin adding safety clearance
1092 if p_safety_clearance_flag = 1 then
1093 begin
1094 for clearances_record in workclearance_cursor(p_wip_entity_id(i)) loop
1095 select XMLConcat(l_xmlTypeClearances,clearances_record.workclearance) into l_xmlTypeClearances from dual;
1096 end loop;
1097 select XMLELEMENT("WORKCLEARANCE_LIST",l_xmlTypeClearances) into l_xmlTypeClearances from dual;
1098 exception
1099 when NO_DATA_FOUND then
1100 null;
1101 end;
1102 end if;
1103 --end adding safety permit
1104
1105
1106 --Adding Failure Data
1107 begin
1108 select XMLELEMENT("FAILUREDATA",XMLFOREST(ewod.failure_code_required as "CODEREQUIRED",
1109 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(eaf.failure_date), calendar_aware => FND_DATE.calendar_aware_alt) as "FAILUREDATE",
1110 eafc.failure_code as "FAILURECODE",
1111 eafc.cause_code as "CAUSECODE",
1112 eafc.resolution_code as "RESOLUTIONCODE",
1113 efs.set_name as "SETNAME",
1114 eafc.comments as "COMMENTS")) into l_xmlTypeFailureData
1115 from eam_asset_failures eaf,
1116 eam_asset_failure_codes eafc,
1117 eam_work_order_details ewod,
1118 wip_discrete_jobs wdj,
1119 eam_failure_set_associations easa,
1120 eam_failure_sets efs
1121 where wdj.wip_entity_id =eaf.source_id and
1122 eaf.source_type=1 and
1123 eaf.failure_id =eafc.failure_id and
1124 wdj.wip_entity_id= ewod.wip_entity_id and
1125 easa.inventory_item_id(+)=nvl(wdj.asset_group_id,wdj.rebuild_item_id) and
1126 efs.set_id(+)=easa.set_id and
1127 wdj.wip_entity_id=p_wip_entity_id(i);
1128 exception
1129 WHEN OTHERS THEN
1130 null;
1131 end;
1132
1133 --Main Work Order Header
1134
1135 --Adding PM Base Meter Name
1136 begin
1137 select XMLFOREST(ewod.warranty_active as "WARRANTYACTIVE",cct.name as "PMBASEMETER") into l_xmlTemp3
1138 from eam_work_order_details ewod,csi_counters_tl cct
1139 where ewod.wip_entity_id=p_wip_entity_id(i)
1140 and ewod.pm_base_meter=cct.counter_id
1141 and cct.language=userenv('Lang');
1142 exception
1143 when NO_DATA_FOUND then
1144 null;
1145 end;
1146
1147 --Adding Warranty Expiration Date
1148 begin
1149 select XMLConcat(XMLELEMENT("WARRANTYEXPDATE",fnd_date.date_to_displayDT(dateval => Convert_to_client_time(csi.SUPPLIER_WARRANTY_EXP_DATE), calendar_aware => FND_DATE.calendar_aware_alt)),l_xmlTemp3) into l_xmlTemp3
1150 from csi_item_instances csi, wip_discrete_jobs wdj
1151 where wdj.maintenance_object_id=csi.instance_id
1152 and wdj.maintenance_object_type=3
1153 and wdj.wip_entity_id=p_wip_entity_id(i);
1154 exception
1155 when NO_DATA_FOUND then
1156 null;
1157 end;
1158
1159 --Adding Actual Start Date and Actual End Date
1160 begin
1161 SELECT XMLConcat(XMLFOREST(fnd_date.date_to_displayDT(dateval => Convert_to_client_time(ACTUAL_START_DATE), calendar_aware => FND_DATE.calendar_aware_alt) as "ACTUALSTARTDATE",
1162 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(ACTUAL_END_DATE), calendar_aware => FND_DATE.calendar_aware_alt) as "ACTUALENDDATE",
1163 round((ACTUAL_END_DATE-ACTUAL_START_DATE)*24,2) as "ACTUALDURATION"),l_xmlTemp3) into l_xmlTemp3
1164 FROM EAM_JOB_COMPLETION_TXNS
1165 WHERE TRANSACTION_TYPE=1
1166 AND TRANSACTION_ID=
1167 (
1168 SELECT MAX(TRANSACTION_ID)
1169 FROM EAM_JOB_COMPLETION_TXNS EJT,WIP_DISCRETE_JOBS WDJ
1170 WHERE EJT.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
1171 AND EJT.ORGANIZATION_ID=WDJ.ORGANIZATION_ID
1172 AND WDJ.WIP_ENTITY_ID=p_wip_entity_id(i)
1173 );
1174 exception
1175 when NO_DATA_FOUND then
1176 null;
1177 end;
1178
1179
1180 --Adding Asset Group Description
1181 begin
1182 select XMLConcat(XMLELEMENT("ASSETGRPDESC",msi.description),l_xmlTemp3) into l_xmlTemp3
1183 from mtl_system_items_kfv msi, wip_discrete_jobs wdj
1184 where nvl(wdj.asset_group_id,wdj.rebuild_item_id)=msi.inventory_item_id
1185 and wdj.organization_id=msi.organization_id
1186 and wdj.wip_entity_id=p_wip_entity_id(i);
1187 exception
1188 when NO_DATA_FOUND then
1189 null;
1190 end;
1191
1192 --Adding Area Info
1193 begin
1194 select XMLConcat(XMLFOREST(mel.location_codes as "AREA" ,mel.description as "AREADESC"),l_xmlTemp3) into l_xmlTemp3
1195 from eam_org_maint_defaults eomd, mtl_eam_locations mel, wip_discrete_jobs wdj
1196 where eomd.object_id = wdj.maintenance_object_id
1197 and eomd.object_type = 50
1198 and eomd.organization_id =wdj.organization_id
1199 and wdj.wip_entity_id=p_wip_entity_id(i)
1200 and eomd.area_id=mel.location_id;
1201 exception
1202 when NO_DATA_FOUND then
1203 null;
1204 end;
1205
1206 select XMLConcat(l_xmlType,
1207 XMLELEMENT("WORKORDER",XMLATTRIBUTES(wewdv.wip_entity_id as "WIPENTITYID"),
1208 XMLFOREST(wewdv.wip_entity_name as "NAME",
1209 wewdv.description as "DESCRIPTION",
1210 wewdv.work_order_status as "STATUS",
1211 wewdv.asset_description as "ASSETDESC",
1212 wewdv.priority_disp as "PRIORITY",
1213 wewdv.class_code as "CLASSCODE",
1214 wewdv.instance_number as "ASSETNUMBER",
1215 flm1.meaning as "PENDING",
1216 wewdv.shutdown_type_disp as "SHUTDOWNTYPE",
1217 wewdv.asset_rebuild_group as "ASSETGROUP",
1218 wewdv.rebuild_serial_number as "REBSERIALNO",
1219 msi.concatenated_segments as "ACTIVITY",
1220 msi.description as "ACTIVITYDESC",
1221 wewdv.activity_type_disp as "ACTIVITYTYPE",
1222 wewdv.activity_cause_disp as "ACTIVITYCAUSE",
1223 wewdv.activity_source_meaning as "ACTIVITYSOURCE",
1224 wewdv.warranty_claim_status as "WARRANTY",
1225 wewdv.parent_wip_entity_name as "PARENTNAME",
1226 flm4.meaning as "NOTIFICATION",
1227 flm3.meaning as "TAGOUT",
1228 flm5.meaning as "PLANNED",
1229 wewdv.planner_maintenance_meaning as "PLANNERTYPE",
1230 wewdv.project_name as "PROJECTNAME",
1231 wewdv.task_name as "TASKNAME",
1232 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(wewdv.pm_suggested_start_date), calendar_aware => FND_DATE.calendar_aware_alt) as "PMSTARTDATE",
1233 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(wewdv.pm_suggested_end_date), calendar_aware => FND_DATE.calendar_aware_alt) as "PMENDDATE",
1234 flm2.meaning as "MATISSUEREQUEST",
1235 lu1.meaning as "FIRM",
1236 lu2.meaning as "MATSHORTAGE",
1237 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(wewdv.material_shortage_check_date), calendar_aware => FND_DATE.calendar_aware_alt) as "ASOFDATE",
1238 wewdv.owning_department_code as "DEPARTMENT",
1239 wewdv.work_order_type_disp as "WOTYPE",
1240 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(wewdv.scheduled_start_date), calendar_aware => FND_DATE.calendar_aware_alt) as "STARTDATE",
1241 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(wewdv.scheduled_completion_date), calendar_aware => FND_DATE.calendar_aware_alt) as "ENDDATE",
1242 round((wewdv.scheduled_completion_date-wewdv.scheduled_start_date)*24,2) as "SCHEDULEDDURATION",
1243 eps.name as "PMNAME",
1244 fnd_date.date_to_displayDT(dateval => Convert_to_client_time(eps.base_date), calendar_aware => FND_DATE.calendar_aware_alt) as "BASEDATE",
1245 bd.description as "DEPTDESCRIPTION"),
1246 XMLConcat(l_xmlTemp3,l_xmlTypeFailureData,l_xmlType1,l_xmlTypeOperation,l_xmlTypeMaterial,l_xmlTypeResource,l_xmlTypeDirectMaterial,l_xmlTypeShortAttachment,
1247 l_xmlTypeLongAttachment,l_xmlTypefileattachment,l_xmlTypeWorkRequest,l_xmlTypeMeter,l_xmlTypeQualityPlan,l_xmlTypeAssetBom,l_xmlTypeAssetroutecomp,l_xmlTypePermits,l_xmlTypeClearances,l_xmlTypeParamList))) AS "RESULT" into l_xmlType
1248 from eam_work_orders_v wewdv ,mtl_system_items_b_kfv msi,eam_pm_schedulings eps,bom_departments bd,mfg_lookups lu1,mfg_lookups lu2
1249 ,fnd_common_lookups flm1,fnd_common_lookups flm2,fnd_common_lookups flm3,fnd_common_lookups flm4 ,fnd_common_lookups flm5
1250 where wewdv.wip_entity_id=p_wip_entity_id(i)
1251 and msi.inventory_item_id(+)= wewdv.primary_item_id
1252 and msi.organization_id(+)=wewdv.organization_id
1253 and lu1.lookup_code(+)=wewdv.firm_planned_flag
1254 and lu1.lookup_type(+)='SYS_YES_NO'
1255 and lu2.lookup_code(+)=wewdv.material_shortage_flag
1256 and lu2.lookup_type(+)='SYS_YES_NO'
1257 and eps.pm_schedule_id(+)=wewdv.pm_schedule_id
1258 and bd.department_id(+)=wewdv.owning_department
1259 and flm1.lookup_type(+) = 'EAM_YES_NO'
1260 and flm1.lookup_code(+)=wewdv.pending_flag
1261 and flm2.lookup_type(+) = 'EAM_YES_NO'
1262 and flm2.lookup_code(+)=wewdv.material_issue_by_mo
1263 and flm3.lookup_type(+) = 'EAM_YES_NO'
1264 and flm3.lookup_code(+)=wewdv.tagout_required
1265 and flm4.lookup_type(+) = 'EAM_YES_NO'
1266 and flm4.lookup_code(+)=wewdv.notification_required
1267 and flm5.lookup_type(+) = 'EAM_YES_NO'
1268 and flm5.lookup_code(+)=wewdv.plan_maintenance;
1269
1270 l_xmlTypeOperation:=null;
1271 l_xmlTypeMaterial:=null;
1272 l_xmlTypeResource:=null;
1273 l_xmlTypeEmployee:=null;
1274 l_xmlTypeDirectMaterial:=null;
1275 l_xmlTypeShortAttachment:=null;
1276 l_xmlTypeOpShortAttachment:=null;
1277 l_xmlTypeOpLongAttachment:=null;
1278 l_xmlTypeLongAttachment:=null;
1279 l_xmlTypefileattachment:=null;
1280 l_xmlTypeWorkRequest:=null;
1281 l_xmlTypeMeter:=null;
1282 l_xmlTypeQualityPlan:=null;
1283 l_xmlTypeAssetBom:=null;
1284 l_xmlTypeFailureData:=null;
1285 l_xmlTemp3:=null;
1286 l_xmlTypeAssetroutecomp:=null;
1287 l_xmlTypePermits :=NULL; --permit safety report
1288 l_xmlTypeClearances :=NULL; -- safety clearance
1289 end loop;
1290
1291 select XMLELEMENT("WORKORDER_LIST", l_xmlType) into l_xmlType from dual;
1292
1293 return l_xmlType.getClobVal();
1294 -- End of API body.
1295
1296
1297 END getWoReportXML;
1298
1299 --Function to convert Long data into Clob
1300
1301 Function getLong
1302 (
1303 p_wip_id in number,
1304 p_org_id in number,
1305 p_media_id in number,
1306 p_select in number
1307
1308
1309 ) return CLOB
1310 IS
1311
1312 l_longVal long:=null;
1313 l_lobVal clob;
1314 l_attachmenttype varchar2(50);
1315 BEGIN
1316 -- bug 13552899 (changed for fnd change in long_text column)
1317 -- API body
1318 if p_select=1 then
1319 insert into EAM_WOREP_LONG_ATTACH_TEMP
1320 (select
1321 fdlt.long_text
1322 from
1323 FND_DOCUMENTS_LONG_TEXT fdlt,
1324 fnd_documents_vl fdv,
1325 fnd_attached_documents fad
1326 where
1327 fdlt.media_id = fdv.media_id and
1328 fad.document_id = fdv.document_id and
1329 fad.entity_name ='EAM_WORK_ORDERS' and
1330 fad.pk2_value =to_char(p_wip_id) and
1331 fdlt.media_id =to_char(p_media_id) and
1332 fad.pk1_value = to_char(p_org_id));
1333
1334 select long_text into l_lobVal from EAM_WOREP_LONG_ATTACH_TEMP ;
1335 delete from EAM_WOREP_LONG_ATTACH_TEMP;
1336
1337 else
1338 -- bug 13552899 (changed for fnd change in long_text column)
1339 insert into EAM_WOREP_LONG_ATTACH_TEMP (select
1340 fdlt.long_text
1341 from
1342 FND_DOCUMENTS_LONG_TEXT fdlt,
1343 fnd_documents_vl fdv,
1344 fnd_attached_documents fad
1345 where
1346 fdlt.media_id = fdv.media_id and
1347 fad.document_id = fdv.document_id and
1348 fad.entity_name ='EAM_DISCRETE_OPERATIONS'and
1349 fad.pk1_value = to_char(p_wip_id) and
1350 fdlt.media_id =to_char(p_media_id) and
1351 fad.pk3_value =to_char(p_org_id));
1352
1353 select long_text into l_lobVal from EAM_WOREP_LONG_ATTACH_TEMP;
1354 delete from EAM_WOREP_LONG_ATTACH_TEMP;
1355
1356 end if;
1357 return l_lobVal;
1358 -- End of API body.
1359 END getLong;
1360
1361
1362 --Function to covert date from Server Time zone to Client Time Zone
1363
1364 Function Convert_to_client_time (
1365 p_server_time in date
1366 ) return date
1367 IS
1368 l_client_tz_id number;
1369 l_server_tz_id number;
1370 l_msg_count number;
1371 l_msg_data varchar2(2000);
1372 l_client_time date;
1373 l_status varchar2(100);
1374 BEGIN
1375 -- API body
1376 /*l_client_tz_id := to_number ( fnd_profile.value_specific('CLIENT_TIMEZONE_ID'));
1377 l_server_tz_id := to_number( fnd_profile.value_specific('SERVER_TIMEZONE_ID'));*/
1378 -- fix for 6415147
1379 l_client_tz_id := nvl(to_number ( fnd_profile.value_specific('CLIENT_TIMEZONE_ID')),nvl(to_number ( fnd_profile.value_specific('CLIENT_TIMEZONE_ID',fnd_global.user_id)),0));
1380 l_server_tz_id := nvl(to_number( fnd_profile.value_specific('SERVER_TIMEZONE_ID')),nvl(to_number( fnd_profile.value_specific('SERVER_TIMEZONE_ID',fnd_global.user_id)),0));
1381
1382 HZ_TIMEZONE_PUB.Get_Time(1.0, 'F', l_server_tz_id, l_client_tz_id, p_server_time,
1383 l_client_time, l_status, l_msg_count, l_msg_data);
1384 return l_client_time;
1385 -- API body
1386
1387 END;
1388
1389 END EAM_WorkOrderRep_PVT;
1390
[Home] [Help]