1 PACKAGE BODY EAM_WORKORDERREP_PVT AS
2 /* $Header: EAMVWRPB.pls 120.12.12010000.2 2008/10/17 21:14:21 rsinn 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 ***************************************************************************/
22
23
24 G_PKG_NAME CONSTANT VARCHAR2(30):='EAM_WorkOrderRep_PVT';
25 Function getWoReportXML
26 (
27 p_wip_entity_id in system.eam_wipid_tab_type,
28 p_operation_flag in int,
29 p_material_flag in int,
30 p_resource_flag in int,
31 p_direct_material_flag in int,
32 p_short_attachment_flag in int,
33 p_long_attachment_flag in int,
34 p_file_attachment_flag in int,
35 p_work_request_flag in int,
36 p_meter_flag in int,
37 p_quality_plan_flag in int,
38 p_asset_bom_flag in int
39
40
41 )return CLOB
42
43 IS
44
45 l_xmlType XMLType:=null;
46 l_xmlType1 XMLType:=null;
47 l_xmlTypeOperation XMLType:=null;
48 l_xmlTypeResource XMLType:=null;
49 l_xmlTypeEmployee XMLType:=null;
50 l_xmlTypeDirectMaterial XMLType:=null;
51 l_xmlTypeShortAttachment XMLType:=null;
52 l_xmlTypeOpShortAttachment XMLType:=null;
53 l_xmlTypeLongAttachment XMLType:=null;
54 l_xmlTypeOpLongAttachment XMLType:=null;
55 l_xmlTypefileattachment XMLType:=null;
56 l_xmlTypeWorkRequest XMLType:=null;
57 l_xmlTypeMeter XMLType:=null;
58 l_xmlTypeMaterial XMLType:=null;
59 l_xmlTypeQualityPlan XMLType:=null;
60 l_xmlTypeAssetBom XMLType :=null;
61 l_xmlTypeFailureData XMLType:=null;
62 l_xmlTemp XMLType:=null;
63 l_xmlTemp2 XMLType:=null;
64 l_xmlTemp3 XMLType:=null;
65 l_string varchar2(4000);
66 l_wip_entity_name varchar2(100);
67 l_organization_id number;
68 l_org_id number;
69 l_asset_group varchar2(100);
70 l_instance_number varchar2(100):=null;
71 l_serial_number varchar2(100):=null;
72 l_asset_activity varchar2(100):=null;
73 l_media_id number:=0;
74 l_plan_id number;
75 i number:=1;
76 l_temp clob:=null;
77
78 -- FP 7493388 for Base Bug 7005666
79 l_inventory_id number;
80 l_asset_route_flag varchar2(20);
81 l_xmlTypeAssetroutecomp XMLType:=null;
82 -- for Bug 7005666
83
84 cursor operation_cursor(p_wip_id number) is
85 SELECT XMLELEMENT("OPERATION", XMLFOREST(WO.OPERATION_SEQ_NUM as "OPSEQNUM",
86 fnd_date.date_to_displayDT(Convert_to_client_time(WO.FIRST_UNIT_START_DATE)) as "OPSSHEDULEDSTART",
87 fnd_date.date_to_displayDT(Convert_to_client_time(WO.LAST_UNIT_COMPLETION_DATE)) as "OPSCHEDULEDCOMPLETION",
88 to_char((WO.LAST_UNIT_COMPLETION_DATE-WO.FIRST_UNIT_START_DATE),'99.99') as "OPDURATION",
89 LU2.meaning as "OPSHUTDOWNTYPE",
90 LU1.meaning as "OPCOMPLETED",
91 BS.OPERATION_CODE as "OPCODE",
92 BD.DEPARTMENT_CODE as "OPDEPT",
93 WO.DESCRIPTION as "OPDESC",
94 WO.LONG_DESCRIPTION as "OPLONGDESC",
95 fnd_date.date_to_displayDT(Convert_to_client_time(eoctv.actual_start_date)) as "OPACTUALSTARTDATE",
96 fnd_date.date_to_displayDT(Convert_to_client_time(eoctv.actual_end_date)) as "OPACTUALENDDATE")) Operation
97 FROM
98 eam_op_completion_txns_v eoctv,
99 FND_COMMON_LOOKUPS LU1,
100 MFG_LOOKUPS LU2,
101 WIP_OPERATIONS WO,
102 BOM_STANDARD_OPERATIONS BS,
103 BOM_DEPARTMENTS BD
104 WHERE BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
105 AND NVL(BS.OPERATION_TYPE,1) = 1 and
106 eoctv.wip_entity_id(+)=wo.wip_entity_id and
107 eoctv.operation_seq_num(+)=wo.operation_seq_num
108 AND BS.LINE_ID IS NULL
109 AND WO.WIP_ENTITY_ID = p_wip_id
110 AND LU2.LOOKUP_CODE(+) = WO.SHUTDOWN_TYPE
111 AND LU2.LOOKUP_TYPE(+) = 'BOM_EAM_SHUTDOWN_TYPE'
112 AND LU1.LOOKUP_CODE(+) = WO.OPERATION_COMPLETED
113 AND LU1.LOOKUP_TYPE(+) = 'EAM_YES_NO'
114 AND BS.STANDARD_OPERATION_ID (+) = WO.STANDARD_OPERATION_ID
115 ORDER BY WO.OPERATION_SEQ_NUM;
116
117 --Cursor for Materials
118
119 cursor material_cursor(p_wip_id number) is
120 SELECT XMLELEMENT("MATERIAL",XMLFOREST(WRO.OPERATION_SEQ_NUM as "OPERATIONSEQNUM",
121 milk.concatenated_segments as "REQLOCATORNAME",
122 --M.DESCRIPTION as "REQCOMPDESC",
123 LU.MEANING as "REQTYPE",
124 WRO.SUPPLY_SUBINVENTORY as "REQSUBINVENTORY",
125 fnd_date.date_to_displayDT(Convert_to_client_time(WRO.DATE_REQUIRED)) as "REQDATEREQUIRED",
126 M.PRIMARY_UOM_CODE as "REQUOM",
127 WRO.REQUIRED_QUANTITY as "REQREQUIREDQUANTITY",
128 msikfv.concatenated_segments as "REQITEMNAME",
129 msikfv.description as "REQITEMDESCRIPTION",
130 wro.quantity_issued as "REQISSUEDQUANTITY",
131 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" ,
132 eam_material_allocqty_pkg.allocated_quantity(WRO.WIP_ENTITY_ID,WRO.OPERATION_SEQ_NUM,WRO.ORGANIZATION_ID,WRO.INVENTORY_ITEM_ID) as "REQALLOCATEDQUANTITY")) Material
133 FROM
134 mtl_system_items_b_kfv msikfv,
135 MTL_SYSTEM_ITEMS M,
136 MTL_ITEM_LOCATIONS L,
137 MFG_LOOKUPS LU,
138 WIP_REQUIREMENT_OPERATIONS WRO,
139 MTL_ITEM_LOCATIONS_KFV milk
140 WHERE
141 msikfv.organization_id = wro.organization_id
142 AND msikfv.inventory_item_id = wro.inventory_item_id
143 AND M.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
144 AND WRO.WIP_ENTITY_ID= p_wip_id
145 AND L.INVENTORY_LOCATION_ID (+) = NVL(WRO.SUPPLY_LOCATOR_ID,'-1')
146 AND milk.INVENTORY_LOCATION_ID (+) = NVL(WRO.SUPPLY_LOCATOR_ID,'-1')
147 AND M.ORGANIZATION_ID = WRO.ORGANIZATION_ID
148 AND L.ORGANIZATION_ID (+) = WRO.ORGANIZATION_ID
149 AND milk.organization_id (+) = wro.organization_id
150 AND LU.LOOKUP_TYPE = 'WIP_SUPPLY_SHORT'
151 AND LU.LOOKUP_CODE = WRO.WIP_SUPPLY_TYPE
152 AND wro.inventory_item_id in (SELECT inventory_item_id
153 FROM mtl_system_items
154 WHERE stock_enabled_flag = 'Y')
155 ORDER BY msikfv.concatenated_segments;
156
157 -- Cursor for Resources
158
159 cursor resource_cursor(p_wip_id number) is
160 SELECT XMLELEMENT("RESOURCE",XMLFOREST(WOR.OPERATION_SEQ_NUM as "RESOPSEQNUM",
161 WOR.RESOURCE_SEQ_NUM as "RESSEQ",
162 WOR.SCHEDULE_SEQ_NUM as "RESSCHEDSEQ",
163 BR.RESOURCE_CODE as "RESCODE",
164 --BR.RESOURCE_ID as "RESRESOURCEID",
165 WOR.USAGE_RATE_OR_AMOUNT as "RESUSAGERATE",
166 WOR.UOM_CODE as "RESUOM",
167 LU.MEANING as "RESBASIS",
168 WOR.APPLIED_RESOURCE_UNITS as "ACTUALHRCHARGED",
169 WOR.ASSIGNED_UNITS as "RESCAPACITY",
170 CA.ACTIVITY as "RESACTIVITY",
171 fnd_date.date_to_displayDT(Convert_to_client_time(WOR.START_DATE)) as "RESSTARTDATE",
172 fnd_date.date_to_displayDT(Convert_to_client_time(WOR.COMPLETION_DATE)) as "RESCOMPLETIONDATE")) Resources
173 FROM
174 BOM_RESOURCES BR,
175 CST_ACTIVITIES CA,
176 MFG_LOOKUPS LU,
177 WIP_OPERATION_RESOURCES WOR
178 WHERE BR.ORGANIZATION_ID = WOR.ORGANIZATION_ID
179 AND WOR.WIP_ENTITY_ID = p_wip_id
180 AND BR.RESOURCE_ID = WOR.RESOURCE_ID
181 AND CA.ACTIVITY_ID(+) = WOR.ACTIVITY_ID
182 AND LU.LOOKUP_CODE = WOR.BASIS_TYPE
183 AND LU.LOOKUP_TYPE = 'CST_BASIS'
184 ORDER BY WOR.RESOURCE_SEQ_NUM;
185
186 --Cursor for Employees
187
188 cursor employee_cursor(p_wip_id number) is
189 SELECT XMLELEMENT("EMPLOYEE",XMLFOREST(
190 wori.operation_seq_num as "EMPOPSEQNO",
191 wori.resource_seq_num as "EMPRESSEQNO",
192 br.resource_code as "EMPRESCODE",
193 br.resource_type as "EMPRESTYPE",
194 ppf.employee_number as "EMPNO",
195 ppf.full_name as "EMPFULLNAME",
196 fnd_date.date_to_displayDT(Convert_to_client_time(bre.effective_start_date)) as "EMPSTARTDATE",
197 fnd_date.date_to_displayDT(Convert_to_client_time(bre.effective_end_date)) as "EMPENDDATE",
198 bd.department_code as "EMPDEPTCODE")) Employees
199 FROM wip_op_resource_instances wori,
200 wip_operation_resources wor,
201 bom_resources br,
202 bom_resource_employees bre,
203 per_people_f ppf,
204 bom_departments bd
205 WHERE wor.wip_entity_id = wori.wip_entity_id and
206 wor.organization_id = wori.organization_id and
207 wor.operation_seq_num = wori.operation_seq_num and
208 wor.resource_seq_num = wori.resource_seq_num and
209 br.resource_id = wor.resource_id and
210 br.resource_type = 2 and
211 bre.instance_id = wori.instance_id and
212 trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date and
213 ppf.person_id = bre.person_id and
214 wor.department_id=bd.department_id and
215 wor.organization_id=bd.organization_id and
216 wor.wip_entity_id=p_wip_id;
217
218 --Cursor for Direct Materials
219
220 cursor directmaterial_cursor(p_wip_id number) is
221 SELECT XMLELEMENT("DIRECTMATERIAL", XMLFOREST(edrv.task_number as "OPERATIONSEQNO",
222 edrv.service_line_type as "SERVICELINETYPE",
223 edrv.item_description as "ITEMNAME",
224 edrv.description as "ITEMDESC",
225 DECODE(edrv.order_type_lookup_code,'FIXED PRICE',NVL(edrv.amount,0),NVL(edrv.required_quantity, 0)) as "QUANTITYREQUIRED",
226 DECODE(edrv.order_type_lookup_code,'FIXED PRICE',NVL(edrv.rql_amount_ordered,0),NVL(edrv.rql_quantity_ordered,0)) as "REQQUANTITYORDERED",
227 DECODE(edrv.order_type_lookup_code,'FIXED PRICE',NVL(edrv.po_amount_ordered,0),NVL(edrv.po_quantity_ordered,0)) as "POQUANTITYORDERED",
228 DECODE(edrv.order_type_lookup_code,'FIXED PRICE',NVL(edrv.amount_delivered,0),NVL(edrv.quantity_received, 0)) as "QUANTITYRECEIVED",
229 edrv.uom_code as "UOM",
230 fnd_date.date_to_displayDT(Convert_to_client_time(edrv.date_required)) as "DATEREQUIRED")) DirectMaterial
231 FROM EAM_DIRECT_ITEM_RECS_V edrv
232 WHERE edrv.wip_entity_id = p_wip_id;
233
234
235 -- Cursor for Work Order Short Attachments
236
237 cursor shortattachment_cursor(p_wip_id number,p_org_id number) is
238 select XMLELEMENT("SHORTATTACHMENT",XMLFOREST(
239 --fdst.media_id as "AMEDIAID",
240 fdst.short_text as "ASHORTTEXT",
241 fdv.file_name as "AWORKNAME",
242 fdv.description as "AWORKDESC",
243 --fdv.datatype_name as "AWORKTYPE",
244 -- fdv.datatype_id as "AWORKDATATYPEID",
245 --fdv.category_id as "ACATEGORYID",
246 fdv.category_description as "AWORKCATEGORY",
247 fad.seq_num as "ASEQNUM"
248 --fad.entity_name as "AENTITYNAME",
249 /*fad.attached_document_id as "ATTACHWORKDOCID"*/)) ShortAttachment
250 from
251 FND_DOCUMENTS_SHORT_TEXT fdst,
252 fnd_documents_vl fdv,
253 fnd_attached_documents fad
254 where
255 fdst.media_id = fdv.media_id and
256 fad.document_id = fdv.document_id and
257 fad.entity_name = 'EAM_WORK_ORDERS' and
258 fad.pk1_value = p_org_id and
259 fad.pk2_value =p_wip_id
260 order by fdv.file_name;
261
262 -- Cursor for Work Order Long Attachments
263
264 cursor longattachment_cursor(p_wip_id number,p_org_id number) is
265 select XMLFOREST(
266 fdlt.media_id as "ALONGMEDIAID",
267 --fdlt.long_text as "ALONGTEXT",
268 fdv.file_name as "ALONGWORKNAME",
269 fdv.description as "ALONGWORKDESC",
270 --fdv.datatype_name as "AWORKTYPE",
271 --fdv.datatype_id as "AWORKDATATYPEID",
272 --fdv.category_id as "ACATEGORYID",
273 fdv.category_description as "ALONGWORKCATEGORY",
274 fad.seq_num as "ALONGSEQNUM"
275 --fad.entity_name as "AENTITYNAME",
276 /*fad.attached_document_id as "ATTACHWORKDOCID"*/) LongAttachment
277 from
278 FND_DOCUMENTS_LONG_TEXT fdlt,
279 fnd_documents_vl fdv,
280 fnd_attached_documents fad
281 where
282 fdlt.media_id = fdv.media_id and
283 fad.document_id = fdv.document_id and
284 fad.entity_name = 'EAM_WORK_ORDERS' and
285 fad.pk1_value = p_org_id and
286 fad.pk2_value =p_wip_id
287 order by fdv.file_name;
288
289 --Cursor for Operation Short Attachments
290
291 cursor opshortattachment(p_wip_id number,p_org_id number) is
292 select XMLELEMENT("OPSHORTATTACHMENT",XMLFOREST(fdst.short_text as "OPASHORTTEXT",
293 fdv.file_name as "OPASHORTNAME",
294 fdv.description as "OPASHORTDESC",
295 fdv.category_description as "OPASHORTCATEGORY",
296 fad.pk2_value as "OPASEQNO")) OpShortAttachment
297 from
298 FND_DOCUMENTS_SHORT_TEXT fdst,
299 fnd_documents_vl fdv,
300 fnd_attached_documents fad
301 where fdst.media_id = fdv.media_id
302 and fad.document_id = fdv.document_id
303 and fad.entity_name = 'EAM_DISCRETE_OPERATIONS'
304 and fad.pk1_value=p_wip_id
305 and fad.pk3_value=p_org_id
306 order by fdv.file_name;
307
308 -- Cursor for Operation Long Attachments
309 cursor oplongattachment_cursor(p_wip_id number,p_org_id number) is
310 select XMLFOREST(
311 fdlt.media_id as "OPALONGMEDIAID",
312 fdv.file_name as "OPALONGWORKNAME",
313 fdv.description as "OPALONGWORKDESC",
314 fdv.category_description as "OPALONGWORKCATEGORY",
315 fad.pk2_value as "OPALONGSEQNUM") OpLongAttachment
316 from
317 FND_DOCUMENTS_LONG_TEXT fdlt,
318 fnd_documents_vl fdv,
319 fnd_attached_documents fad
320 where
321 fdlt.media_id = fdv.media_id and
322 fad.document_id = fdv.document_id and
323 fad.entity_name = 'EAM_DISCRETE_OPERATIONS' and
324 fad.pk1_value = p_wip_id and
325 fad.pk3_value =p_org_id
326 order by fdv.file_name;
327
328 --Cursor for File Attachments
329 cursor fileattachment_cursor(p_wip_id number,p_org_id number) is
330 select XMLELEMENT("FILEATTACHMENT",XMLFOREST(
331 fdv.file_name as "AFILEWORKNAME",
332 fdv.description as "AFILEWORKDESC",
333 fdv.category_description as "AFILEWORKCATEGORY")) FileAttachment
334 from
335 fnd_documents_vl fdv,
336 fnd_attached_documents fad
337 where
338 fad.document_id = fdv.document_id and
339 fad.entity_name = 'EAM_WORK_ORDERS' and
340 fad.pk1_value=p_org_id and
341 fad.pk2_value= p_wip_id and
342 fdv.file_name is not null;
343
344
345 -- Cursor for Work Requests
346
347 cursor workrequest_cursor(p_wip_id number,p_org_id number) is
348 select XMLELEMENT("WORKREQUEST",XMLFOREST(wewr.work_request_number as "WRNUMBER",
349 wewr.description as "WRDESCRIPTION",
350 ml1.meaning as "WRSTATUS" ,
351 ml2.meaning as "WRPRIORITY",
352 bd.department_code as "WROWNINGDEPT",
353 fnd_date.date_to_displayDT(Convert_to_client_time(wewr.expected_resolution_date)) as "WREXPECTEDRESOLUTIONDATE",
354 fu.user_name as "WRORIGINATOR")) WorkRequest
355 from
356 wip_eam_work_requests wewr,
357 fnd_user fu,
358 bom_departments bd,
359 mfg_lookups ml1,
360 mfg_lookups ml2
361 where
362 fu.user_id = wewr.created_by
363 and bd.department_id = wewr.work_request_owning_dept
364 and ml1.lookup_type (+) = 'WIP_EAM_WORK_REQ_STATUS'
365 and ml1.lookup_code (+) = wewr.work_request_status_id
366 and ml2.lookup_type (+) = 'WIP_EAM_ACTIVITY_PRIORITY'
367 and ml2.lookup_code (+) = wewr.work_request_priority_id
368 and wewr.wip_entity_id =p_wip_id
369 and wewr.organization_id = p_org_id
370 order by wewr.work_request_number ;
371
372 -- Cursor for Meters
373
374 cursor meter_cursor(p_wip_id number) is
375 SELECT XMLELEMENT("METER",XMLFOREST(
376 CTL.NAME as "COUNTERNAME",
377 NVL(CTL2.NAME,CTL.NAME) as "SOURCECOUNTERNAME",
378 NVL(CBS.COUNTER_TYPE,CB.COUNTER_TYPE) as "COUNTERTYPE",
379 NVL(CCR2.COUNTER_READING,CCR1.COUNTER_READING) as "LASTREADING",
380 fnd_date.date_to_displayDT(Convert_to_client_time(NVL(CCR2.VALUE_TIMESTAMP,CCR1.VALUE_TIMESTAMP))) as "LASTVALUETIMESTAMP",
381 CB.uom_code as "METERUOM",
382 TO_NUMBER(NULL) as "NEWREADING",
383 EAM_METERS_UTIL.IS_METER_READING_MANDATORY_V(p_wip_id, nvl(CBS.COUNTER_ID,CB.COUNTER_ID)) as "MANDATORY" )) Meter
384 FROM csi_counters_b CB,
385 CSI_COUNTERS_TL CTL,
386 CSI_COUNTERS_TL CTL2,
387 CSI_COUNTERS_B CBS,
388 CSI_COUNTER_READINGS CCR1,
389 CSI_COUNTER_READINGS CCR2,
390 csi_item_instances cii,
391 wip_discrete_jobs wdj,
392 (
393 SELECT
394 *
395 FROM csi_counter_associations
396 WHERE sysdate BETWEEN nvl(start_date_Active,sysdate-1) AND nvl(end_date_active,sysdate+1)
397 )
398 CCA,
399 (
400 SELECT
401 *
402 FROM csi_counter_relationships
403 WHERE sysdate BETWEEN nvl(Active_end_date,sysdate-1) AND nvl(active_end_date,sysdate+1)
404 )
405 CCR
406 WHERE
407 CB.COUNTER_ID=CCA.COUNTER_ID(+)
408 AND CB.COUNTER_ID=CTL.COUNTER_ID
409 AND CCR.OBJECT_COUNTER_ID(+)=CB.COUNTER_ID
410 AND CCR.SOURCE_COUNTER_ID=CBS.COUNTER_ID(+)
411 AND CBS.COUNTER_ID=CTL2.COUNTER_ID(+)
412 AND CTL.LANGUAGE=USERENV('LANG')
413 AND CTL2.LANGUAGE(+)=USERENV('LANG')
414 AND CB.COUNTER_TYPE='REGULAR'
415 AND CBS.COUNTER_TYPE(+)='REGULAR'
416 AND CB.COUNTER_ID=CCR1.COUNTER_ID(+)
417 AND CCR1.DISABLED_FLAG(+)='N'
418 AND wdj.maintenance_object_id=cii.instance_id
419 AND wdj.maintenance_object_type=3
420 AND cca.source_object_id = cii.instance_id
421 AND wdj.wip_entity_id=p_wip_id
422 AND
423 (
424 CCR1.VALUE_TIMESTAMP =
425 (
426 SELECT
427 MAX(VALUE_TIMESTAMP)
428 FROM CSI_COUNTER_READINGS B
429 WHERE CCR1.COUNTER_ID=B.COUNTER_ID
430 AND B.DISABLED_FLAG='N'
431 GROUP BY COUNTER_ID
432 )
433 OR NOT EXISTS
434 (
435 SELECT
436 COUNTER_ID
437 FROM CSI_COUNTER_READINGS B
438 WHERE CB.COUNTER_ID=B.COUNTER_ID
439 AND B.DISABLED_FLAG='N'
440 )
441 )
442 AND CBS.COUNTER_ID=CCR2.COUNTER_ID(+)
443 AND
444 (
445 CCR2.VALUE_TIMESTAMP =
446 (
447 SELECT
448 MAX(VALUE_TIMESTAMP)
449 FROM CSI_COUNTER_READINGS B
450 WHERE CCR2.COUNTER_ID=B.COUNTER_ID
451 AND B.DISABLED_FLAG='N'
452 GROUP BY COUNTER_ID
453 )
454 OR NOT EXISTS
455 (
456 SELECT
457 COUNTER_ID
458 FROM CSI_COUNTER_READINGS B
459 WHERE CBS.COUNTER_ID=B.COUNTER_ID
460 AND B.DISABLED_FLAG='N'
461 )
462 )
463 AND CCR2.DISABLED_FLAG(+)='N';
464
465
466 --Cursor for Quality Plan
467 cursor qualityplan_cursor(p_quality_flag int, p_organization_id number,p_wip_entity_name varchar2 , p_asset_group varchar2,
468 p_instance_number varchar2,p_serial_number varchar2,p_asset_activity varchar2) is
469 Select XMLFOREST(
470 qpv.plan_id as "PLANID",
471 ml.meaning as "PLANMANDATORY",
472 qpv.DESCRIPTION as "PLANDESCRIPTION",
473 qpv.NAME as "PLANNAME",
474 qpv.plan_type_meaning as "PLANTYPE") QualityPlan
475 FROM
476 QA_PLANS_VAL_V qpv,
477 qa_plan_transactions qpt,
478 mfg_lookups ml
479 Where qpt.plan_id=qpv.plan_id
480 and ml.lookup_code=qpt.mandatory_collection_flag
481 and ml.lookup_type='SYS_YES_NO'
482 and nvl(p_quality_flag,2)=decode(p_quality_flag,1,qpt.mandatory_collection_flag,2,p_quality_flag,2)
483 and decode(qpt.transaction_number,31,qa_web_txn_api.plan_applies(qpv.plan_id,qpt.transaction_number,to_char(p_organization_id),
484 p_asset_group,p_serial_number,p_asset_activity,p_wip_entity_name,'',p_instance_number,'','','','','EAM'),'N')='Y';
485
486 --Cursor for Quality Plan Elements
487 cursor qplanelement_cursor(p_plan_id number) is
488 select XMLELEMENT("ELEMENTNAME",q.prompt ) QPlanElement
489 from QA_PLAN_CHARS q
490 where q.plan_id=p_plan_id;
491
492 --Cursor for Asset BOM
493 cursor assetbom_cursor(p_wip_id in number) is
494 select XMLELEMENT("ITEM",XMLFOREST(msik.concatenated_segments as "COMPONENTITEM",
495 msik.description as "DESCRIPTION",
496 bic.component_quantity as "QUANTITY",
497 msik.primary_uom_code as "UOM",
498 lu.meaning as "SUPPLYTYPE")) AssetBom
499 from bom_inventory_components bic,
500 mtl_system_items_kfv msik,
501 wip_discrete_jobs wdj,
502 csi_item_instances cii,
503 bom_bill_of_materials bbom,
504 mfg_lookups lu
505 where bic.effectivity_date <= sysdate
506 and (bic.disable_date >= sysdate or
507 bic.disable_date is null)
508 and wdj.maintenance_object_id = cii.instance_id
509 and wdj.maintenance_object_type=3
510 and wdj.wip_entity_id =p_wip_id
511 and cii.inventory_item_id = bbom.assembly_item_id
512 and wdj.organization_id = bbom.organization_id
513 and bic.bill_sequence_id=bbom.common_bill_sequence_id
514 and bic.component_item_id=msik.inventory_item_id
515 and msik.organization_id=wdj.organization_id
516 and lu.lookup_type(+) = 'EAM_CONSTANTS.G_SUPPLY_TYPE'
517 and lu.lookup_code(+) = bic.wip_supply_type
518 and (wdj.rebuild_item_id is not null OR
519 ((NVL(bic.from_end_item_unit_number,'0') = '0') OR bic.from_end_item_unit_number <= cii.serial_number)
520 and ( bic.to_end_item_unit_number >=cii.serial_number or NVL(bic.to_end_item_unit_number,'0')='0')
521 )
522
523 union all
524
525 select XMLELEMENT("ITEM",XMLFOREST(msik.concatenated_segments as "COMPONENTITEM",
526 msik.description as "DESCRIPTION",
527 bic.component_quantity as "QUANTITY",
528 msik.primary_uom_code as "UOM",
529 lu.meaning as "SUPPLYTYPE")) AssetBom
530 from bom_inventory_components bic,
531 mtl_system_items_kfv msik,
532 wip_discrete_jobs wdj,
533 bom_bill_of_materials bbom,
534 mfg_lookups lu
535 where bic.effectivity_date <= sysdate
536 and (bic.disable_date >= sysdate or
537 bic.disable_date is null)
538 and wdj.wip_entity_id =p_wip_id
539 and wdj.maintenance_object_type=2
540 and wdj.maintenance_object_id = bbom.assembly_item_id
541 and wdj.organization_id = bbom.organization_id
542 and bic.bill_sequence_id = bbom.common_bill_sequence_id
543 and bic.component_item_id=msik.inventory_item_id
544 and wdj.organization_id = msik.organization_id
545 and lu.lookup_type(+) = 'EAM_CONSTANTS.G_SUPPLY_TYPE'
546 and lu.lookup_code(+) = bic.wip_supply_type;
547
548 --begin changes for FP 7493388 (base bug 7005666)
549 --cursor for Asset Route
550
551 cursor assetroutecomp_cursor(l_serial_number varchar2,l_inventory_id number) is
552 SELECT XMLELEMENT("ASSETROUTECOMP", XMLFOREST(
553 R_components_name as "ASSET_NUMBER",
554 R_component_group as "ASSET_GROUP",
555 R_component_description as "DESCRIPTION",
556 area as "AREA")) AssetRoutecomp
557 from (select mena.network_serial_number R_asset_number,
558 msn1.concatenated_segments R_asset_group,
559 mena.serial_number R_components_name,
560 msn2.concatenated_segments R_component_group,
561 msn2.descriptive_text R_component_description,
562 msn2.area area,
563 mena.network_item_id R_asset_group_id,
564 mena.organization_id R_asset_org_id
565 from
566 mtl_eam_network_assets_v mena
567 , mtl_eam_asset_numbers_v msn1
568 , mtl_eam_asset_numbers_v msn2
569 ,csi_item_instances cii
570 where
571 mena.organization_id=msn1.current_organization_id
572 and mena.network_item_id=msn1.inventory_item_id
573 and mena.network_serial_number=msn1.serial_number
574 and mena.organization_id=msn2.current_organization_id
575 and mena.inventory_item_id=msn2.inventory_item_id
576 and mena.serial_number=msn2.serial_number
577 AND mena.network_object_id = cii.instance_id
578 AND cii.instance_number=l_instance_number
579 order by mena.serial_number) ;
580
581 --end changes for FP 7493388 (base bug 7005666)
582
583 BEGIN
584
585 for i in p_wip_entity_id.FIRST..p_wip_entity_id.LAST
586 loop
587
588 select wdj.organization_id into l_org_id
589 from wip_discrete_jobs wdj
590 where wdj.wip_entity_id=p_wip_entity_id(i);
591
592
593 --begin changes for FP 7493388 (base bug 7005666)
594
595 select wdj.ASSET_NUMBER, wdj.ASSET_GROUP_ID into l_serial_number ,l_inventory_id
596 from wip_discrete_jobs wdj
597 where wdj.wip_entity_id=p_wip_entity_id(i);
598
599
600 SELECT nvl(instance_number,'hghg') into l_instance_number
601 FROM eam_work_orders_v
602 WHERE wip_entity_id=p_wip_entity_id(i);
603
604 SELECT NETWORK_ASSET_FLAG into l_asset_route_flag
605 FROM csi_item_instances
606 WHERE INSTANCE_NUMBER = l_instance_number
607 AND INVENTORY_ITEM_ID = l_inventory_id
608 AND SERIAL_NUMBER = l_serial_number;
609
610 --end changes for FP 7493388 (base bug 7005666)
611
612
613 --Adding Operations
614
615 if p_operation_flag = 1 then
616 begin
617 for operation_record in operation_cursor(p_wip_entity_id(i)) loop
618 select XMLConcat(l_xmlTypeOperation,operation_record.Operation) into l_xmlTypeOperation from dual;
619 end loop;
620 select XMLELEMENT("OPERATION_LIST",l_xmlTypeOperation) into l_xmlTypeOperation from dual;
621 exception
622 when NO_DATA_FOUND then
623 null;
624 end;
625 end if;
626
627 --Adding Materials
628 if p_material_flag = 1 then
629 begin
630 for material_record in material_cursor(p_wip_entity_id(i)) loop
631 select XMLConcat(l_xmlTypeMaterial,material_record.Material) into l_xmlTypeMaterial from dual;
632 end loop;
633 select XMLELEMENT("MATERIAL_LIST",l_xmlTypeMaterial) into l_xmlTypeMaterial from dual;
634 exception
635 when NO_DATA_FOUND then
636 null;
637 end;
638 end if;
639
640 --Adding Resources
641 if p_resource_flag = 1 then
642 begin
643 for resource_record in resource_cursor(p_wip_entity_id(i)) loop
644 select XMLConcat(l_xmlTypeResource,resource_record.Resources) into l_xmlTypeResource from dual;
645 end loop;
646 select XMLELEMENT("RESOURCE_LIST",l_xmlTypeResource) into l_xmlTypeResource from dual;
647 --Adding Employees
648 for employee_record in employee_cursor(p_wip_entity_id(i)) loop
649 select XMLConcat(l_xmlTypeEmployee,employee_record.Employees) into l_xmlTypeEmployee from dual;
650 end loop;
651 select XMLELEMENT("EMPLOYEE_LIST",l_xmlTypeEmployee) into l_xmlTypeEmployee from dual;
652 select XMLConcat(l_xmlTypeResource,l_xmlTypeEmployee) into l_xmlTypeResource from dual;
653 exception
654 when NO_DATA_FOUND then
655 null;
656 end;
657 end if;
658
659 --Adding Direct Materials
660 if p_direct_material_flag = 1 then
661 begin
662 for directmaterial_record in directmaterial_cursor(p_wip_entity_id(i)) loop
663 select XMLConcat(l_xmlTypeDirectMaterial,directmaterial_record.DirectMaterial) into l_xmlTypeDirectMaterial from dual;
664 end loop;
665 select XMLELEMENT("DIRECTMATERIAL_LIST",l_xmlTypeDirectMaterial) into l_xmlTypeDirectMaterial from dual;
666 exception
667 when NO_DATA_FOUND then
668 null;
669 end;
670 end if;
671
672 --Adding Work Order Short Text Attachments
673 if p_short_attachment_flag =1 then
674 begin
675 for attachment_record in shortattachment_cursor(p_wip_entity_id(i),l_org_id) loop
676 select XMLConcat(l_xmlTypeShortAttachment,attachment_record.ShortAttachment) into l_xmlTypeShortAttachment from dual;
677 end loop;
678 select XMLELEMENT("WOSHORTATTACHMENT_LIST",l_xmlTypeShortAttachment) into l_xmlTypeShortAttachment from dual;
679 exception
680 when NO_DATA_FOUND then
681 null;
682 end;
683
684 --Adding Operation Short Text Attachments
685 begin
686 for opattachment_record in opshortattachment(p_wip_entity_id(i),l_org_id) loop
687 select XMLConcat(l_xmlTypeOpShortAttachment,opattachment_record.OpShortAttachment) into l_xmlTypeOpShortAttachment from dual;
688 end loop;
689 select XMLELEMENT("OPSHORTATTACHMENT_LIST",l_xmlTypeOpShortAttachment) into l_xmlTypeOpShortAttachment from dual;
690 select XMLConcat(l_xmlTypeShortAttachment,l_xmlTypeOpShortAttachment) into l_xmlTypeShortAttachment from dual;
691 exception
692 when NO_DATA_FOUND then
693 null;
694 end;
695 end if;
696
697 --Adding Work Order Long Text Attachments
698 if p_long_attachment_flag =1 then
699 begin
700 for longattachment_record in longattachment_cursor(p_wip_entity_id(i),l_org_id) loop
701 select longattachment_record.LongAttachment into l_xmlTemp from dual;
702 select extractValue(l_xmlTemp,'/ALONGMEDIAID') into l_media_id from dual;
703 l_temp:=getLong(p_wip_entity_id(i),l_org_id,l_media_id,1);
704 select XMLConcat(l_xmlTemp,xmlType('<ALONGTEXT>'||l_temp||'</ALONGTEXT>')) into l_xmlTemp from dual;
705 select XMLELEMENT("LONGATTACHMENT",l_xmlTemp) into l_xmlTemp from dual;
706 select XMLConcat(l_xmlTemp,l_xmlTypeLongAttachment) into l_xmlTypeLongAttachment from dual;
707 l_xmlTemp:=null;
708 end loop;
709 select XMLELEMENT("WOLONGATTACHMENT_LIST",l_xmlTypeLongAttachment) into l_xmlTypeLongAttachment from dual;
710 exception
711 when NO_DATA_FOUND then
712 null;
713 end;
714
715 --Adding Operation Long Text Attachments
716 begin
717 for oplongattachment_record in oplongattachment_cursor(p_wip_entity_id(i),l_org_id) loop
718 select oplongattachment_record.OpLongAttachment into l_xmlTemp from dual;
719 select extractValue(l_xmlTemp,'/OPALONGMEDIAID') into l_media_id from dual;
720 l_temp:=getLong(p_wip_entity_id(i),l_org_id,l_media_id,2);
721 select XMLConcat(l_xmlTemp,xmlType('<OPALONGTEXT>'||l_temp||'</OPALONGTEXT>')) into l_xmlTemp from dual;
722 select XMLELEMENT("OPLONGATTACHMENT",l_xmlTemp) into l_xmlTemp from dual;
723 select XMLConcat(l_xmlTemp,l_xmlTypeOpLongAttachment) into l_xmlTypeOpLongAttachment from dual;
724 l_xmlTemp:=null;
725 end loop;
726 select XMLELEMENT("OPLONGATTACHMENT_LIST",l_xmlTypeOpLongAttachment) into l_xmlTypeOpLongAttachment from dual;
727 select XMLConcat(l_xmlTypeLongAttachment,l_xmlTypeOpLongAttachment) into l_xmlTypeLongAttachment from dual;
728 exception
729 when NO_DATA_FOUND then
730 null;
731 end;
732 end if;
733
734 --Adding File Attachments
735 if p_file_attachment_flag = 1 then
736 begin
737 for fileattachment_record in fileattachment_cursor(p_wip_entity_id(i),l_org_id) loop
738 select XMLConcat(l_xmlTypefileattachment,fileattachment_record.FileAttachment) into l_xmlTypefileattachment from dual;
739 end loop;
740 select XMLELEMENT("FILEATTACHMENT_LIST",l_xmlTypefileattachment) into l_xmlTypefileattachment from dual;
741 exception
742 when NO_DATA_FOUND then
743 null;
744 end;
745 end if;
746
747 --Adding Work Request
748 if p_work_request_flag =1 then
749 begin
750 for workrequest_record in workrequest_cursor(p_wip_entity_id(i),l_org_id) loop
751 select XMLConcat(l_xmlTypeWorkRequest,workrequest_record.WorkRequest) into l_xmlTypeWorkRequest from dual;
752 end loop;
753 select XMLELEMENT("WORKREQUEST_LIST",l_xmlTypeWorkRequest) into l_xmlTypeWorkRequest from dual;
754 exception
755 when NO_DATA_FOUND then
756 null;
757 end;
758 end if;
759
760
761 --Adding Meters
762 if p_meter_flag =1 then
763 begin
764 for meter_record in meter_cursor(p_wip_entity_id(i)) loop
765 select XMLConcat(l_xmlTypeMeter,meter_record.Meter) into l_xmlTypeMeter from dual;
766 end loop;
767 select XMLELEMENT("METER_LIST",l_xmlTypeMeter) into l_xmlTypeMeter from dual;
768 exception
769 when NO_DATA_FOUND then
770 null;
771 end;
772 end if;
773
774 --Adding Quality Plan
775 if p_quality_plan_flag =1 or p_quality_plan_flag=2 then
776 begin
777 select we.wip_entity_name,
778 wdj.organization_id,
779 msi.concatenated_segments,
780 cii.instance_number,cii.serial_number into l_wip_entity_name,l_organization_id,l_asset_group,l_instance_number,l_serial_number
781 from wip_discrete_jobs wdj,
782 csi_item_instances cii,
783 mtl_system_items_b_kfv msi,
784 wip_entities we
785 where wdj.wip_entity_id=p_wip_entity_id(i)
786 and wdj.wip_entity_id=we.wip_entity_id
787 and cii.instance_id(+) = DECODE(wdj.maintenance_object_type,3,wdj.maintenance_object_id,NULL)
788 and nvl(wdj.asset_group_id,wdj.rebuild_item_id) = msi.inventory_item_id
789 and wdj.organization_id=msi.organization_id;
790
791 begin
792 select msi.concatenated_segments into l_asset_activity
793 from mtl_system_items_b_kfv msi,
794 wip_discrete_jobs wdj
795 where wdj.primary_item_id=msi.inventory_item_id and
796 wdj.organization_id=msi.organization_id and
797 wdj.wip_entity_id=p_wip_entity_id(i) ;
798 exception
799 when no_data_found then
800 null;
801 end;
802
803 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
804 select XMLConcat(l_xmlTypeQualityPlan,qualityplan_record.QualityPlan) into l_xmlTypeQualityPlan from dual;
805 select extractValue(l_xmlTypeQualityPlan,'/PLANID') into l_plan_id from dual;
806 l_xmlTemp2 := null;
807 for qplanelement_record in qplanelement_cursor(l_plan_id) loop
808 select qplanelement_record.QPlanElement into l_xmlTemp from dual;
809 select XMLConcat(l_xmlTemp,xmlType('<VALUE> </VALUE>')) into l_xmlTemp from dual;
810 select XMLELEMENT("ELEMENT",l_xmlTemp) into l_xmlTemp from dual;
811 select XMLConcat(l_xmlTemp2,l_xmlTemp) into l_xmlTemp2 from dual;
812 end loop;
813 select XMLELEMENT("ELEMENT_LIST",l_xmlTemp2) into l_xmlTemp2 from dual;
814 select XMLConcat(l_xmlTypeQualityPlan,l_xmlTemp2) into l_xmlTypeQualityPlan from dual;
815 select XMLELEMENT("QUALITYPLAN",l_xmlTypeQualityPlan) into l_xmlTypeQualityPlan from dual;
816 end loop;
817 select XMLELEMENT("QUALITYPLAN_LIST",l_xmlTypeQualityPlan) into l_xmlTypeQualityPlan from dual;
818
819 exception
820 when NO_DATA_FOUND then
821 null;
822 end;
823 end if;
824
825 --Adding Asset BOM
826 if p_asset_bom_flag =1 then
827 begin
828 for assetbom_record in assetbom_cursor(p_wip_entity_id(i)) loop
829 select XMLConcat(l_xmlTypeAssetBom,assetbom_record.AssetBom) into l_xmlTypeAssetBom from dual;
830 end loop;
831 select XMLELEMENT("ASSETBOM_LIST",l_xmlTypeAssetBom) into l_xmlTypeAssetBom from dual;
832 exception
833 when NO_DATA_FOUND then
834 null;
835 end;
836 end if;
837
838 --begin changes for FP 7493388 (base bug 7005666)
839 --Adding Asset route components
840 if l_asset_route_flag = 'Y' then
841 begin
842
843 for asset_route_record in assetroutecomp_cursor(l_serial_number,l_inventory_id) loop
844 select XMLConcat(l_xmlTypeAssetroutecomp,asset_route_record.AssetRoutecomp) into l_xmlTypeAssetroutecomp from dual;
845 end loop;
846 select XMLELEMENT("ASSETROUTECOMP_LIST",l_xmlTypeAssetroutecomp) into l_xmlTypeAssetroutecomp from dual;
847 exception
848 when NO_DATA_FOUND then
849 null;
850 end;
851 end if;
852 --end changes for FP 7493388 (base bug 7005666)
853
854 --Adding Failure Data
855 begin
856 select XMLELEMENT("FAILUREDATA",XMLFOREST(ewod.failure_code_required as "CODEREQUIRED",
857 fnd_date.date_to_displayDT(Convert_to_client_time(eaf.failure_date)) as "FAILUREDATE",
858 eafc.failure_code as "FAILURECODE",
859 eafc.cause_code as "CAUSECODE",
860 eafc.resolution_code as "RESOLUTIONCODE",
861 efs.set_name as "SETNAME",
862 eafc.comments as "COMMENTS")) into l_xmlTypeFailureData
863 from eam_asset_failures eaf,
864 eam_asset_failure_codes eafc,
865 eam_work_order_details ewod,
866 wip_discrete_jobs wdj,
867 eam_failure_set_associations easa,
868 eam_failure_sets efs
869 where wdj.wip_entity_id =eaf.source_id and
870 eaf.source_type=1 and
871 eaf.failure_id =eafc.failure_id and
872 wdj.wip_entity_id= ewod.wip_entity_id and
873 easa.inventory_item_id(+)=nvl(wdj.asset_group_id,wdj.rebuild_item_id) and
874 efs.set_id(+)=easa.set_id and
875 wdj.wip_entity_id=p_wip_entity_id(i);
876 exception
877 WHEN OTHERS THEN
878 null;
879 end;
880
881 --Main Work Order Header
882
883 --Adding PM Base Meter Name
884 begin
885 select XMLFOREST(ewod.warranty_active as "WARRANTYACTIVE",cct.name as "PMBASEMETER") into l_xmlTemp3
886 from eam_work_order_details ewod,csi_counters_tl cct
887 where ewod.wip_entity_id=p_wip_entity_id(i)
888 and ewod.pm_base_meter=cct.counter_id
889 and cct.language=userenv('Lang');
890 exception
891 when NO_DATA_FOUND then
892 null;
893 end;
894
895 --Adding Warranty Expiration Date
896 begin
897 select XMLConcat(XMLELEMENT("WARRANTYEXPDATE",fnd_date.date_to_displayDT(Convert_to_client_time(csi.SUPPLIER_WARRANTY_EXP_DATE))),l_xmlTemp3) into l_xmlTemp3
898 from csi_item_instances csi, wip_discrete_jobs wdj
899 where wdj.maintenance_object_id=csi.instance_id
900 and wdj.maintenance_object_type=3
901 and wdj.wip_entity_id=p_wip_entity_id(i);
902 exception
903 when NO_DATA_FOUND then
904 null;
905 end;
906
907 --Adding Actual Start Date and Actual End Date
908 begin
909 SELECT XMLConcat(XMLFOREST(fnd_date.date_to_displayDT(Convert_to_client_time(ACTUAL_START_DATE)) as "ACTUALSTARTDATE", fnd_date.date_to_displayDT(Convert_to_client_time(ACTUAL_END_DATE)) as "ACTUALENDDATE",
910 round((ACTUAL_END_DATE-ACTUAL_START_DATE)*24,2) as "ACTUALDURATION"),l_xmlTemp3) into l_xmlTemp3
911 FROM EAM_JOB_COMPLETION_TXNS
912 WHERE TRANSACTION_TYPE=1
913 AND TRANSACTION_ID=
914 (
915 SELECT MAX(TRANSACTION_ID)
916 FROM EAM_JOB_COMPLETION_TXNS EJT,WIP_DISCRETE_JOBS WDJ
917 WHERE EJT.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
918 AND EJT.ORGANIZATION_ID=WDJ.ORGANIZATION_ID
919 AND WDJ.WIP_ENTITY_ID=p_wip_entity_id(i)
920 );
921 exception
922 when NO_DATA_FOUND then
923 null;
924 end;
925
926
927 --Adding Asset Group Description
928 begin
929 select XMLConcat(XMLELEMENT("ASSETGRPDESC",msi.description),l_xmlTemp3) into l_xmlTemp3
930 from mtl_system_items_kfv msi, wip_discrete_jobs wdj
931 where nvl(wdj.asset_group_id,wdj.rebuild_item_id)=msi.inventory_item_id
932 and wdj.organization_id=msi.organization_id
933 and wdj.wip_entity_id=p_wip_entity_id(i);
934 exception
935 when NO_DATA_FOUND then
936 null;
937 end;
938
939 --Adding Area Info
940 begin
941 select XMLConcat(XMLFOREST(mel.location_codes as "AREA" ,mel.description as "AREADESC"),l_xmlTemp3) into l_xmlTemp3
942 from eam_org_maint_defaults eomd, mtl_eam_locations mel, wip_discrete_jobs wdj
943 where eomd.object_id = wdj.maintenance_object_id
944 and eomd.object_type = 50
945 and eomd.organization_id =wdj.organization_id
946 and wdj.wip_entity_id=p_wip_entity_id(i)
947 and eomd.area_id=mel.location_id;
948 exception
949 when NO_DATA_FOUND then
950 null;
951 end;
952
953 select XMLConcat(l_xmlType,
954 XMLELEMENT("WORKORDER",XMLATTRIBUTES(wewdv.wip_entity_id as "WIPENTITYID"),
955 XMLFOREST(wewdv.wip_entity_name as "NAME",
956 wewdv.description as "DESCRIPTION",
957 wewdv.work_order_status as "STATUS",
958 wewdv.asset_description as "ASSETDESC",
959 wewdv.priority_disp as "PRIORITY",
960 wewdv.class_code as "CLASSCODE",
961 wewdv.instance_number as "ASSETNUMBER",
962 flm1.meaning as "PENDING",
963 wewdv.shutdown_type_disp as "SHUTDOWNTYPE",
964 wewdv.asset_rebuild_group as "ASSETGROUP",
965 wewdv.rebuild_serial_number as "REBSERIALNO",
966 msi.concatenated_segments as "ACTIVITY",
967 msi.description as "ACTIVITYDESC",
968 wewdv.activity_type_disp as "ACTIVITYTYPE",
969 wewdv.activity_cause_disp as "ACTIVITYCAUSE",
970 wewdv.activity_source_meaning as "ACTIVITYSOURCE",
971 wewdv.warranty_claim_status as "WARRANTY",
972 wewdv.parent_wip_entity_name as "PARENTNAME",
973 flm4.meaning as "NOTIFICATION",
974 flm3.meaning as "TAGOUT",
975 flm5.meaning as "PLANNED",
976 wewdv.project_name as "PROJECTNAME",
977 wewdv.task_name as "TASKNAME",
978 fnd_date.date_to_displayDT(Convert_to_client_time(wewdv.pm_suggested_start_date)) as "PMSTARTDATE",
979 fnd_date.date_to_displayDT(Convert_to_client_time(wewdv.pm_suggested_end_date)) as "PMENDDATE",
980 flm2.meaning as "MATISSUEREQUEST",
981 lu1.meaning as "FIRM",
982 lu2.meaning as "MATSHORTAGE",
983 fnd_date.date_to_displayDT(Convert_to_client_time(wewdv.material_shortage_check_date)) as "ASOFDATE",
984 wewdv.owning_department_code as "DEPARTMENT",
985 wewdv.work_order_type_disp as "WOTYPE",
986 fnd_date.date_to_displayDT(Convert_to_client_time(wewdv.scheduled_start_date)) as "STARTDATE",
987 fnd_date.date_to_displayDT(Convert_to_client_time(wewdv.scheduled_completion_date)) as "ENDDATE",
988 round((wewdv.scheduled_completion_date-wewdv.scheduled_start_date)*24,2) as "SCHEDULEDDURATION",
989 eps.name as "PMNAME",
990 fnd_date.date_to_displayDT(Convert_to_client_time(eps.base_date)) as "BASEDATE",
991 bd.description as "DEPTDESCRIPTION"),
992 XMLConcat(l_xmlTemp3,l_xmlTypeFailureData,l_xmlType1,l_xmlTypeOperation,l_xmlTypeMaterial,l_xmlTypeResource,l_xmlTypeDirectMaterial,l_xmlTypeShortAttachment,
993 l_xmlTypeLongAttachment,l_xmlTypefileattachment,l_xmlTypeWorkRequest,l_xmlTypeMeter,l_xmlTypeQualityPlan,l_xmlTypeAssetBom,l_xmlTypeAssetroutecomp))) AS "RESULT" into l_xmlType
994 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
995 ,fnd_common_lookups flm1,fnd_common_lookups flm2,fnd_common_lookups flm3,fnd_common_lookups flm4 ,fnd_common_lookups flm5
996 where wewdv.wip_entity_id=p_wip_entity_id(i)
997 and msi.inventory_item_id(+)= wewdv.primary_item_id
998 and msi.organization_id(+)=wewdv.organization_id
999 and lu1.lookup_code(+)=wewdv.firm_planned_flag
1000 and lu1.lookup_type(+)='SYS_YES_NO'
1001 and lu2.lookup_code(+)=wewdv.material_shortage_flag
1002 and lu2.lookup_type(+)='SYS_YES_NO'
1003 and eps.pm_schedule_id(+)=wewdv.pm_schedule_id
1004 and bd.department_id(+)=wewdv.owning_department
1005 and flm1.lookup_type(+) = 'EAM_YES_NO'
1006 and flm1.lookup_code(+)=wewdv.pending_flag
1007 and flm2.lookup_type(+) = 'EAM_YES_NO'
1008 and flm2.lookup_code(+)=wewdv.material_issue_by_mo
1009 and flm3.lookup_type(+) = 'EAM_YES_NO'
1010 and flm3.lookup_code(+)=wewdv.tagout_required
1011 and flm4.lookup_type(+) = 'EAM_YES_NO'
1012 and flm4.lookup_code(+)=wewdv.notification_required
1013 and flm5.lookup_type(+) = 'EAM_YES_NO'
1014 and flm5.lookup_code(+)=wewdv.plan_maintenance;
1015
1016 l_xmlTypeOperation:=null;
1017 l_xmlTypeMaterial:=null;
1018 l_xmlTypeResource:=null;
1019 l_xmlTypeEmployee:=null;
1020 l_xmlTypeDirectMaterial:=null;
1021 l_xmlTypeShortAttachment:=null;
1022 l_xmlTypeOpShortAttachment:=null;
1023 l_xmlTypeOpLongAttachment:=null;
1024 l_xmlTypeLongAttachment:=null;
1025 l_xmlTypefileattachment:=null;
1026 l_xmlTypeWorkRequest:=null;
1027 l_xmlTypeMeter:=null;
1028 l_xmlTypeQualityPlan:=null;
1029 l_xmlTypeAssetBom:=null;
1030 l_xmlTypeFailureData:=null;
1031 l_xmlTemp3:=null;
1032 l_xmlTypeAssetroutecomp:=null;
1033 end loop;
1034
1035 select XMLELEMENT("WORKORDER_LIST", l_xmlType) into l_xmlType from dual;
1036
1037 return l_xmlType.getClobVal();
1038 -- End of API body.
1039
1040
1041 END getWoReportXML;
1042
1043 --Function to convert Long data into Clob
1044
1045 Function getLong
1046 (
1047 p_wip_id in number,
1048 p_org_id in number,
1049 p_media_id in number,
1050 p_select in number
1051
1052
1053 ) return CLOB
1054 IS
1055
1056 l_longVal long:=null;
1057 l_lobVal clob;
1058 l_attachmenttype varchar2(50);
1059 BEGIN
1060
1061 -- API body
1062 if p_select=1 then
1063 execute immediate 'create table temp_val(long_text clob)';
1064 execute immediate 'insert into temp_val (select to_lob(fdlt.long_text)
1065 from
1066 FND_DOCUMENTS_LONG_TEXT fdlt,
1067 fnd_documents_vl fdv,
1068 fnd_attached_documents fad
1069 where
1070 fdlt.media_id = fdv.media_id and
1071 fad.document_id = fdv.document_id and
1072 fad.entity_name =' || ' ''EAM_WORK_ORDERS'' ' || 'and
1073 fad.pk2_value ='|| p_wip_id ||'and
1074 fdlt.media_id ='||p_media_id||'and
1075 fad.pk1_value ='|| p_org_id||')';
1076
1077 execute immediate 'select long_text from temp_val' into l_lobVal;
1078 execute immediate 'drop table temp_val';
1079 else
1080 execute immediate 'create table temp_val(long_text clob)';
1081 execute immediate 'insert into temp_val (select to_lob(fdlt.long_text)
1082 from
1083 FND_DOCUMENTS_LONG_TEXT fdlt,
1084 fnd_documents_vl fdv,
1085 fnd_attached_documents fad
1086 where
1087 fdlt.media_id = fdv.media_id and
1088 fad.document_id = fdv.document_id and
1089 fad.entity_name =' || ' ''EAM_DISCRETE_OPERATIONS'' ' || 'and
1090 fad.pk1_value ='|| p_wip_id ||'and
1091 fdlt.media_id ='||p_media_id||'and
1092 fad.pk3_value ='|| p_org_id||')';
1093 execute immediate 'select long_text from temp_val' into l_lobVal;
1094 execute immediate 'drop table temp_val';
1095 end if;
1096 return l_lobVal;
1097 -- End of API body.
1098 END getLong;
1099
1100
1101 --Function to covert date from Server Time zone to Client Time Zone
1102
1103 Function Convert_to_client_time (
1104 p_server_time in date
1105 ) return date
1106 IS
1107 l_client_tz_id number;
1108 l_server_tz_id number;
1109 l_msg_count number;
1110 l_msg_data varchar2(2000);
1111 l_client_time date;
1112 l_status varchar2(100);
1113 BEGIN
1114 -- API body
1115 l_client_tz_id := to_number ( fnd_profile.value_specific('CLIENT_TIMEZONE_ID'));
1116 l_server_tz_id := to_number( fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
1117
1118 HZ_TIMEZONE_PUB.Get_Time(1.0, 'F', l_server_tz_id, l_client_tz_id, p_server_time,
1119 l_client_time, l_status, l_msg_count, l_msg_data);
1120 return l_client_time;
1121 -- API body
1122
1123 END;
1124
1125 END EAM_WorkOrderRep_PVT;
1126
[Home] [Help]