[Home] [Help]
PACKAGE BODY: APPS.EAM_WOREP_PUB
Source
1 PACKAGE BODY EAM_WOREP_PUB AS
2 /* $Header: EAMPWORB.pls 120.9.12020000.2 2013/02/21 08:39:11 srkotika ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMPWORB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_WOREP_PUB
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 20-MARCH-2006 Smriti Sharma Initial Creation
21 ***************************************************************************/
22 G_PKG_NAME CONSTANT VARCHAR2(30):='EAM_WOREP_PUB';
23 PROCEDURE Work_Order_CP
24 ( errbuf OUT NOCOPY VARCHAR2
25 , retcode OUT NOCOPY VARCHAR2
26 , p_work_order_from IN VARCHAR2
27 , p_work_order_to IN VARCHAR2
28 , p_scheduled_start_date_from IN VARCHAR2
29 , p_scheduled_start_date_to IN VARCHAR2
30 , p_asset_area_from IN VARCHAR2
31 , p_asset_area_to IN VARCHAR2
32 , p_asset_number IN VARCHAR2
33 , p_status_type IN NUMBER
34 , p_assigned_department IN NUMBER
35 , p_organization_id IN NUMBER
36 , p_operation IN NUMBER
37 , p_resource IN NUMBER
38 , p_material IN NUMBER
39 , p_direct_item IN NUMBER
40 , p_work_request IN NUMBER
41 , p_meter IN NUMBER
42 , p_quality_plan IN NUMBER
43 , p_mandatory IN NUMBER
44 , p_attachment IN NUMBER
45 , p_asset_bom IN NUMBER
46 , p_permit IN NUMBER --added bug 9812863
47 , p_clearance IN NUMBER
48 )
49 IS
50 TYPE WipIdCurType IS REF CURSOR;
51
52 get_wip_entity_id_csr WipIdCurType;
53 --TYPE wip_entity_id_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
54 --wip_entity_id_tbl wip_entity_id_type;
55 wip_entity_id_tbl system.eam_wipid_tab_type;
56
57 l_api_version CONSTANT NUMBER:=1;
58 l_shortage_exists VARCHAR2(1);
59 l_return_status VARCHAR2(1);
60 l_msg_count NUMBER;
61 l_msg_data VARCHAR2(2000);
62 l_sql_stmt VARCHAR2(2000);
63 l_where_clause VARCHAR2(2000):=null;
64 l_where_clause1 VARCHAR2(2000):=null;
65 l_rows NUMBER := 5000;
66 l_quality_plan NUMBER :=0;
67 l_short_attachment NUMBER :=0;
68 l_long_attachment NUMBER :=0;
69 l_file_attachment NUMBER :=0;
70 l_operation NUMBER;
71 l_material NUMBER;
72 l_resource NUMBER;
73 l_direct_item NUMBER;
74 l_work_request NUMBER;
75 l_meter NUMBER;
76 l_asset_bom NUMBER;
77 l_safety_permit NUMBER ; -- for safety permit
78 l_safety_clearance NUMBER ;
79 l_xmldoc CLOB:=null ;
80 l_length NUMBER ;
81 l_encoding VARCHAR2(2000);
82 l_offset NUMBER;
83 l_char VARCHAR2(6);
84 BEGIN
85
86 l_sql_stmt := ' SELECT wdj.wip_entity_id
87 FROM wip_discrete_jobs wdj, eam_work_order_details ewod, csi_item_instances cii,wip_entities we,eam_org_maint_defaults eomd,
88 mtl_eam_locations mel
89 WHERE wdj.wip_entity_id = ewod.wip_entity_id
90 AND decode(wdj.maintenance_object_type,3, wdj.maintenance_object_id,NULL) = cii.instance_id(+)
91 AND wdj.wip_entity_id = we.wip_entity_id
92 AND wdj.organization_id=we.organization_id
93 AND wdj.organization_id = :p_org_id
94 AND eomd.object_id(+) = cii.instance_id
95 AND eomd.object_type(+)=50
96 AND eomd.organization_id(+)=:p_org_id
97 AND mel.location_id(+)=eomd.area_id';
98
99 IF p_work_order_from IS NOT NULL THEN
100 l_where_clause := l_where_clause || ' AND we.wip_entity_name >= '''|| p_work_order_from || '''';
101 IF p_work_order_to IS NOT NULL THEN
102 l_where_clause := l_where_clause || ' AND we.wip_entity_name <= '''|| p_work_order_to|| '''';
103 ELSE
104 l_where_clause := ' AND we.wip_entity_name = '''|| p_work_order_from || '''';
105 END IF;
106 END IF;
107
108
109 IF p_asset_number IS NOT NULL THEN
110 l_where_clause := l_where_clause || ' AND cii.instance_number = '|| '''' ||p_asset_number ||'''';
111 END IF;
112
113 IF p_scheduled_start_date_from IS NOT NULL THEN
114 l_where_clause := l_where_clause || ' AND wdj.scheduled_start_date >= fnd_date.canonical_to_date( '||' '' '||p_scheduled_start_date_from||' '' ) ';
115 END IF;
116
117 IF p_scheduled_start_date_to IS NOT NULL THEN
118 l_where_clause := l_where_clause || ' AND wdj.scheduled_start_date <= fnd_date.canonical_to_date('||' '' '||p_scheduled_start_date_to||' '' ) ';
119 END IF;
120
121
122
123 IF p_status_type IS NOT NULL THEN
124 l_where_clause := l_where_clause || ' AND ewod.user_defined_status_id = ' || p_status_type ;
125 END IF;
126
127
128 IF p_assigned_department IS NOT NULL THEN
129 l_where_clause := l_where_clause || ' AND EXISTS (SELECT 1
130 FROM wip_operations wo
131 WHERE wo.wip_entity_id = wdj.wip_entity_id
132 AND wo.organization_id=wdj.organization_id
133 AND wo.department_id = ' || p_assigned_department || ' ) ';
134 END IF;
135
136 if p_asset_area_from is not null then
137 l_where_clause1 := ' AND mel.location_id >= '|| p_asset_area_from ;
138 IF p_asset_area_to IS NOT NULL THEN
139 l_where_clause1 := l_where_clause1 || ' AND mel.location_id <='|| p_asset_area_to;
140 ELSE
141 l_where_clause1 := ' AND mel.location_id = '|| p_asset_area_from;
142 END IF;
143
144 end if;
145
146
147 if p_quality_plan =1 then
148 if p_mandatory=1 then
149 l_quality_plan:=1;
150 else
151 l_quality_plan:=2;
152 end if;
153 end if;
154
155 if p_attachment=1 then
156 l_short_attachment :=1;
157 l_long_attachment :=1;
158 l_file_attachment :=1;
159 end if;
160
161 if p_operation=1 then
162 l_operation:=1;
163 else
164 l_operation:=0;
165 end if;
166
167 if p_material=1 then
168 l_material:=1;
169 else
170 l_material:=0;
171 end if;
172
173 if p_resource=1 then
174 l_resource:=1;
175 else
176 l_resource:=0;
177 end if;
178
179 if p_direct_item=1 then
180 l_direct_item:=1;
181 else
182 l_direct_item:=0;
183 end if;
184
185 if p_work_request=1 then
186 l_work_request:=1;
187 else
188 l_work_request:=0;
189 end if;
190
191 if p_meter=1 then
192 l_meter:=1;
193 else
194 l_meter:=0;
195 end if;
196
197 if p_asset_bom=1 then
198 l_asset_bom := 1;
199 else
200 l_asset_bom :=0;
201 end if;
202
203 --added bug 9812863
204 if p_permit =1 then
205 l_safety_permit:=1;
206 else
207 l_safety_permit:=0;
208 end if;
209
210 if p_clearance =1 then
211 l_safety_clearance:=1;
212 else
213 l_safety_clearance:=0;
214 end if;
215
216
217 l_sql_stmt := l_sql_stmt || l_where_clause || l_where_clause1;
218
219 OPEN get_wip_entity_id_csr FOR l_sql_stmt USING p_organization_id,p_organization_id;
220 FETCH get_wip_entity_id_csr BULK COLLECT INTO wip_entity_id_tbl LIMIT l_rows;
221 CLOSE get_wip_entity_id_csr;
222 if wip_entity_id_tbl.count <> 0 then
223 l_xmldoc := EAM_WorkOrderRep_PVT.getWoReportXML(wip_entity_id_tbl,l_operation,l_material,l_resource,l_direct_item,l_short_attachment,l_long_attachment ,
224 l_file_attachment,l_work_request ,l_meter ,l_quality_plan,l_asset_bom,l_safety_permit,l_safety_clearance);
225 end if;
226 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
227 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<?xml version="1.0" encoding="'||l_encoding ||'"?>' );
228 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ROWSET>');
229 l_length := nvl(DBMS_LOB.getlength(l_xmldoc), 0);
230 l_offset := 1;
231
232 WHILE (l_offset <= l_length)
233 LOOP
234 l_char := dbms_lob.substr(l_xmldoc,1,l_offset);
235
236 IF (l_char = to_char(10))
237 THEN
238 fnd_file.new_line(fnd_file.output, 1);
239 ELSE
240 fnd_file.put(fnd_file.output, l_char);
241 END IF;
242
243 l_offset := l_offset + 1;
244 END LOOP;
245
246 fnd_file.new_line(fnd_file.output, 1);
247
248 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</ROWSET>');
249
250 retcode := 0;
251 EXCEPTION
252
253 WHEN OTHERS THEN
254 errbuf := SQLERRM;
255 retcode := 2;
256 END Work_Order_CP;
257
258 END EAM_WOREP_PUB;
259