[Home] [Help]
PACKAGE BODY: APPS.EAM_WOREP_PUB
Source
1 PACKAGE BODY EAM_WOREP_PUB AS
2 /* $Header: EAMPWORB.pls 120.4.12010000.2 2008/10/06 09:31:59 smrsharm 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 )
47 IS
48 TYPE WipIdCurType IS REF CURSOR;
49
50 get_wip_entity_id_csr WipIdCurType;
51 --TYPE wip_entity_id_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
52 --wip_entity_id_tbl wip_entity_id_type;
53 wip_entity_id_tbl system.eam_wipid_tab_type;
54
55 l_api_version CONSTANT NUMBER:=1;
56 l_shortage_exists VARCHAR2(1);
57 l_return_status VARCHAR2(1);
58 l_msg_count NUMBER;
59 l_msg_data VARCHAR2(2000);
60 l_sql_stmt VARCHAR2(2000);
61 l_where_clause VARCHAR2(2000):=null;
62 l_where_clause1 VARCHAR2(2000):=null;
63 l_rows NUMBER := 5000;
64 l_quality_plan NUMBER :=0;
65 l_short_attachment NUMBER :=0;
66 l_long_attachment NUMBER :=0;
67 l_file_attachment NUMBER :=0;
68 l_operation NUMBER;
69 l_material NUMBER;
70 l_resource NUMBER;
71 l_direct_item NUMBER;
72 l_work_request NUMBER;
73 l_meter NUMBER;
74 l_asset_bom NUMBER;
75 l_xmldoc CLOB:=null ;
76 l_length NUMBER ;
77 l_curr NUMBER:=1;
78 l_next NUMBER:=1;
79 l_encoding VARCHAR2(2000);
80 BEGIN
81
82 l_sql_stmt := ' SELECT wdj.wip_entity_id
83 FROM wip_discrete_jobs wdj, eam_work_order_details ewod, csi_item_instances cii,wip_entities we,eam_org_maint_defaults eomd,
84 mtl_eam_locations mel
85 WHERE wdj.wip_entity_id = ewod.wip_entity_id
86 AND decode(wdj.maintenance_object_type,3, wdj.maintenance_object_id,NULL) = cii.instance_id(+)
87 AND wdj.wip_entity_id = we.wip_entity_id
88 AND wdj.organization_id=we.organization_id
89 AND wdj.organization_id = :p_org_id
90 AND eomd.object_id(+) = cii.instance_id
91 AND eomd.object_type(+)=50
92 AND eomd.organization_id(+)=:p_org_id
93 AND mel.location_id(+)=eomd.area_id';
94
95 IF p_work_order_from IS NOT NULL THEN
96 l_where_clause := l_where_clause || ' AND we.wip_entity_name >= '''|| p_work_order_from || '''';
97 IF p_work_order_to IS NOT NULL THEN
98 l_where_clause := l_where_clause || ' AND we.wip_entity_name <= '''|| p_work_order_to|| '''';
99 ELSE
100 l_where_clause := ' AND we.wip_entity_name = '''|| p_work_order_from || '''';
101 END IF;
102 END IF;
103
104
105 IF p_asset_number IS NOT NULL THEN
106 l_where_clause := l_where_clause || ' AND cii.instance_number = '|| '''' ||p_asset_number ||'''';
107 END IF;
108
109 IF p_scheduled_start_date_from IS NOT NULL THEN
110 l_where_clause := l_where_clause || ' AND wdj.scheduled_start_date >= fnd_date.canonical_to_date( '||' '' '||p_scheduled_start_date_from||' '' ) ';
111 END IF;
112
113 IF p_scheduled_start_date_to 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_to||' '' ) ';
115 END IF;
116
117
118
119 IF p_status_type IS NOT NULL THEN
120 l_where_clause := l_where_clause || ' AND ewod.user_defined_status_id = ' || p_status_type ;
121 END IF;
122
123
124 IF p_assigned_department IS NOT NULL THEN
125 l_where_clause := l_where_clause || ' AND EXISTS (SELECT 1
126 FROM wip_operations wo
127 WHERE wo.wip_entity_id = wdj.wip_entity_id
128 AND wo.organization_id=wdj.organization_id
129 AND wo.department_id = ' || p_assigned_department || ' ) ';
130 END IF;
131
132 if p_asset_area_from is not null then
133 l_where_clause1 := ' AND mel.location_id >= '|| p_asset_area_from ;
134 IF p_asset_area_to IS NOT NULL THEN
135 l_where_clause1 := l_where_clause1 || ' AND mel.location_id <='|| p_asset_area_to;
136 ELSE
137 l_where_clause1 := ' AND mel.location_id = '|| p_asset_area_from;
138 END IF;
139
140 end if;
141
142
143 if p_quality_plan =1 then
144 if p_mandatory=1 then
145 l_quality_plan:=1;
146 else
147 l_quality_plan:=2;
148 end if;
149 end if;
150
151 if p_attachment=1 then
152 l_short_attachment :=1;
153 l_long_attachment :=1;
154 l_file_attachment :=1;
155 end if;
156
157 if p_operation=1 then
158 l_operation:=1;
159 else
160 l_operation:=0;
161 end if;
162
163 if p_material=1 then
164 l_material:=1;
165 else
166 l_material:=2;
167 end if;
168
169 if p_resource=1 then
170 l_resource:=1;
171 else
172 l_resource:=0;
173 end if;
174
175 if p_direct_item=1 then
176 l_direct_item:=1;
177 else
178 l_direct_item:=0;
179 end if;
180
181 if p_work_request=1 then
182 l_work_request:=1;
183 else
184 l_work_request:=0;
185 end if;
186
187 if p_meter=1 then
188 l_meter:=1;
189 else
190 l_meter:=2;
191 end if;
192
193 if p_asset_bom=1 then
194 l_asset_bom := 1;
195 else
196 l_asset_bom :=0;
197 end if;
198
199 l_sql_stmt := l_sql_stmt || l_where_clause || l_where_clause1;
200
201 OPEN get_wip_entity_id_csr FOR l_sql_stmt USING p_organization_id,p_organization_id;
202 FETCH get_wip_entity_id_csr BULK COLLECT INTO wip_entity_id_tbl LIMIT l_rows;
203 CLOSE get_wip_entity_id_csr;
204 if wip_entity_id_tbl.count <> 0 then
205 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 ,
206 l_file_attachment,l_work_request ,l_meter ,l_quality_plan,l_asset_bom);
207 end if;
208 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
209 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<?xml version="1.0" encoding="'||l_encoding ||'"?>' );
210 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ROWSET>');
211 l_length := nvl(DBMS_LOB.getlength(l_xmldoc), 0);
212
213 WHILE l_curr < l_length
214 LOOP
215 l_next := INSTR(l_xmldoc, '</', l_curr);
216 l_next := INSTR(l_xmldoc, '>', l_next);
217 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, SUBSTR(l_xmldoc, l_curr, l_next - l_curr + 1));
218 l_curr := l_next + 1;
219 END LOOP;
220
221 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</ROWSET>');
222
223
224 retcode := 0;
225 EXCEPTION
226
227 WHEN OTHERS THEN
228 errbuf := SQLERRM;
229 retcode := 2;
230 END Work_Order_CP;
231
232 END EAM_WOREP_PUB;
233