[Home] [Help]
PACKAGE BODY: APPS.OE_UPG_INSTALL_DETAILS
Source
1 PACKAGE BODY oe_upg_install_details as
2 /* $Header: OEXIUIDB.pls 120.0 2005/06/01 01:55:23 appldev noship $ */
3
4 Procedure Upgrade_Insert_Errors
5 (
6 L_header_id IN Varchar2,
7 L_comments IN varchar2
8 )
9 is
10
11 --
12 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
13 --
14 Begin
15 insert into oe_upgrade_errors
16 (
17 header_id,
18 comments,
19 creation_date
20 )
21 values
22 (
23 l_header_id,
24 substr(l_comments,1,240),
25 sysdate
26 );
27
28 End Upgrade_Insert_Errors;
29
30
31
32 PROCEDURE upgrade_install_details
33 (
34 p_slab IN NUMBER DEFAULT NULL
35 ) IS
36
37 TYPE install_lines_upg_cursor IS ref CURSOR;
38
39 install_lines install_lines_upg_cursor;
40
41 -- This dynamic statement is included because so that this script
42 -- compatible with both 11.5.1.A patch driver (which does not
43 -- pass the p_slab argument) and 11.5.2 patch driver
44 -- which uses parallel workers to execute this script.
45 -- The between clause will be appended only for 11.5.2 when the
46 -- slab parameter is being passed.
47
48 l_install_lines_stmt VARCHAR2(2000) :=
49 'SELECT sld.line_service_detail_id, sld.line_id' ||
50 ' FROM so_line_service_details sld,' ||
51 ' oe_order_lines_all ol' ||
52 ' WHERE ol.line_id = sld.line_id' ||
53 ' AND NOT exists' ||
54 ' (SELECT 1' ||
55 ' FROM cs_line_inst_details csd' ||
56 ' WHERE csd.line_inst_detail_id = sld.line_service_detail_id)';
57
58 l_between_clause VARCHAR2(240) :=
59 ' AND sld.line_service_detail_id BETWEEN :b_start_id AND :b_end_id';
60
61 l_start_id NUMBER := NULL;
62 l_end_id NUMBER := NULL;
63
64 l_parent_line_id NUMBER;
65 l_header_id NUMBER := NULL;
66 l_install_detail_line_id NUMBER;
67 l_new_line_inst_detail_id NUMBER;
68 l_return_status VARCHAR2(1);
69 l_msg_count NUMBER := 0;
70 l_msg_data VARCHAR2(2000);
71 l_msg_index NUMBER;
72 l_object_version_number NUMBER;
73 l_count NUMBER := 0;
74
75 l_line_inst_dtl_rec CS_InstalledBase_PUB.Line_Inst_Dtl_Rec_Type;
76 l_old_line_inst_dtl_rec CS_Inst_Detail_PUB.Line_Inst_Dtl_Rec_Type;
77 l_install_details_rec CS_InstalledBase_PUB.Line_Inst_Dtl_Rec_Type;
78 l_old_line_inst_dtl_desc_flex CS_InstalledBase_PUB.DFF_Rec_Type;
79
80 --
81 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
82 --
83 BEGIN
84
85 IF p_slab IS NOT NULL THEN
86
87 l_install_lines_stmt := l_install_lines_stmt || l_between_clause;
88
89 BEGIN
90
91 SELECT start_header_id, end_header_id
92 INTO l_start_id, l_end_id
93 FROM oe_upgrade_distribution
94 WHERE slab = p_slab
95 AND line_type = 'I';
96
97 EXCEPTION
98 WHEN no_data_found THEN
99
100 oe_upg_install_details.upgrade_insert_errors
101 (
102 l_header_id => 0,
103 l_comments => 'FYI Only: Parallel process of '
104 || 'Installation Details. Marking not used for slab: '
105 || To_char(p_slab)
106 );
107 COMMIT;
108
109 -- Return if no data found in oe_upgrade_distribution
110
111 RETURN;
112 END;
113 END IF;
114
115
116 IF l_debug_level > 0 THEN
117 oe_debug_pub.add( 'ENTERING INSTALLATION DETAILS' ) ;
118 END IF;
119
120 -- Get the parent line which has installation details
121
122 IF p_slab IS NULL THEN
123 OPEN install_lines FOR l_install_lines_stmt;
124 ELSE
125 OPEN install_lines FOR l_install_lines_stmt using l_start_id, l_end_id;
126 END IF;
127
128 LOOP
129
130 FETCH INSTALL_LINES INTO
131 l_install_detail_line_id, l_parent_line_id;
132 EXIT WHEN INSTALL_LINES%NOTFOUND;
133
134 /* Check for the parent line if there are multiple installation details */
135
136 IF l_debug_level > 0 THEN
137 oe_debug_pub.add( 'PARENT LINE ID : ' || L_PARENT_LINE_ID ) ;
138 END IF;
139
140 /* Get the installation details record from SO_LINES_SERVICE_DETAILS */
141 Get_Line_Inst_Details
142 (
143 p_line_inst_details_id =>l_install_detail_line_id,
144 x_line_inst_dtl_rec => l_old_line_inst_dtl_rec,
145 x_line_inst_dtl_desc_flex => l_old_line_inst_dtl_desc_flex
146 );
147
148 l_count := l_count + 1;
149
150 BEGIN -- calling crm api
151
152 CS_Inst_Detail_PUB.create_installation_details
153 (
154 p_api_version => 1.0
155 ,p_init_msg_list => FND_API.G_TRUE
156 ,x_return_status => l_return_status
157 ,x_msg_count => l_msg_count
158 ,x_msg_data => l_msg_data
159 ,p_line_inst_dtl_rec => l_old_line_inst_dtl_rec
160 ,p_line_inst_dtl_desc_flex => l_old_line_inst_dtl_desc_flex
161 ,p_upgrade => FND_API.G_TRUE
162 ,x_object_version_number => l_object_version_number
163 ,x_line_inst_detail_id => l_new_line_inst_detail_id
164 );
165
166 EXCEPTION
167 WHEN fnd_api.g_exc_error THEN
168
169 --
170 -- this is to work around a bug 1349874 filed against CRM
171 --
172
173 l_return_status := FND_API.g_ret_sts_error;
174 l_msg_count := 1;
175 l_msg_data := 'NO_DATA_FOUND Raised from CS_INST_DETAIL_PUB';
176
177 WHEN OTHERS THEN
178 RAISE;
179 END; -- calling crm api
180
181 IF l_count > 500 THEN
182 commit;
183 l_count := 0;
184 END IF;
185
186 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
187 IF l_debug_level > 0 THEN
188 oe_debug_pub.add( 'CREATE INSTALLATION DETAILS - UNEXPECTED ERROR' ) ;
189 END IF;
190 IF l_debug_level > 0 THEN
191 oe_debug_pub.add( 'EXITING INSTALLATION DETAILS API' ) ;
192 END IF;
193 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
194 IF l_debug_level > 0 THEN
195 oe_debug_pub.add( 'INSTALLATION DETAILS - ERROR' ) ;
196 END IF;
197 END IF;
198
199 IF NOT (l_return_status = FND_API.G_RET_STS_SUCCESS) then
200
201 BEGIN
202 select header_id into l_header_id
203 from so_lines_all where line_id = l_parent_line_id;
204 EXCEPTION
205
206 -- This exception is coded to handle a data corruption issue
207 -- with Oracle IT where installation details existed but
208 -- line_id's did not.
209
210 WHEN NO_DATA_FOUND THEN
211
212 l_header_id := NULL;
213 oe_upg_install_details.upgrade_insert_errors
214 (
215 L_header_id => l_header_id,
216 L_comments =>
217 'Upgrade of Installation details failed for detail:'
218 || to_char(l_install_detail_line_id)||' with error: '
219 || 'Line ID: ' || l_parent_line_id || ' does not exist'
220 );
221 END;
222
223 l_msg_index := 1;
224 while l_msg_count > 0 loop
225 l_msg_data := fnd_msg_pub.get(l_msg_index, FND_API.G_FALSE);
226 oe_upg_install_details.upgrade_insert_errors
227 (
228 L_header_id => l_header_id,
229 L_comments =>
230 'Upgrade of Installation details failed for detail :'
231 ||to_char(l_install_detail_line_id)||' with error: '
232 || l_msg_data
233 );
234
235 l_msg_index := l_msg_index + 1;
236 l_msg_count := l_msg_count - 1;
237 end loop;
238
239 END IF;
240
241 END LOOP;
242 CLOSE install_lines;
243 Exception
244 when others THEN
245
246 -- dbms_output.put_line('Line_id is: ' || l_old_line_inst_dtl_rec.order_line_id
247 -- || ' line_service_detail_id is: ' || l_old_line_inst_dtl_rec.line_inst_detail_id);
248
249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
250
251 END Upgrade_install_details;
252
253
254 PROCEDURE Get_Line_Inst_Details
255 ( p_line_inst_details_id IN NUMBER,
256 x_line_inst_dtl_rec OUT NOCOPY CS_Inst_Detail_PUB.Line_Inst_Dtl_Rec_Type,
257
258 x_line_inst_dtl_desc_flex OUT NOCOPY CS_InstalledBase_PUB.DFF_Rec_Type
259
260 )
261 IS
262
263 l_line_inst_dtl_rec CS_Inst_Detail_PUB.Line_Inst_Dtl_Rec_Type;
264 l_line_inst_dtl_desc_flex CS_InstalledBase_PUB.DFF_Rec_Type;
265 l_party_site_id NUMBER := NULL;
266
267 --
268 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
269 --
270 BEGIN
271
272
273 SELECT
274 lsd.line_service_detail_id,
275 lsd.line_id,
276 lsd.source_line_service_detail_id,
277 lsd.transaction_type_id,
278 lsd.system_id,
279 --lsd.system_type_code,
280 lsd.customer_product_id,
281 lsd.customer_product_type_code,
282 lsd.customer_product_quantity,
283 lsd.installation_site_use_id,
284 lsd.installed_cp_return_by_date,
285 lsd.new_cp_return_by_date,
286 --lsd.technical_contact_id,
287 --lsd.service_admin_contact_id,
288 lsd.context,
289 lsd.attribute1,
290 lsd.attribute2,
291 lsd.attribute3,
292 lsd.attribute4,
293 lsd.attribute5,
294 lsd.attribute6,
295 lsd.attribute7,
296 lsd.attribute8,
297 lsd.attribute9,
298 lsd.attribute10,
299 lsd.attribute11,
300 lsd.attribute12,
301 lsd.attribute13,
302 lsd.attribute14,
303 lsd.attribute15,
304 cas.party_site_id
305 INTO
306 l_line_inst_dtl_rec.line_inst_detail_id,
307 l_line_inst_dtl_rec.order_line_id,
308 l_line_inst_dtl_rec.source_line_inst_detail_id,
309 l_line_inst_dtl_rec.transaction_type_id,
310 l_line_inst_dtl_rec.system_id,
311 --l_line_inst_dtl_rec.system_type_code,
312 l_line_inst_dtl_rec.customer_product_id,
313 l_line_inst_dtl_rec.type_code,
314 l_line_inst_dtl_rec.quantity,
315 l_line_inst_dtl_rec.installed_at_party_site_id,
316 l_line_inst_dtl_rec.installed_cp_return_by_date,
317 l_line_inst_dtl_rec.new_cp_return_by_date,
318 l_line_inst_dtl_desc_flex.context,
319 l_line_inst_dtl_desc_flex.attribute1,
320 l_line_inst_dtl_desc_flex.attribute2,
321 l_line_inst_dtl_desc_flex.attribute3,
322 l_line_inst_dtl_desc_flex.attribute4,
323 l_line_inst_dtl_desc_flex.attribute5,
324 l_line_inst_dtl_desc_flex.attribute6,
325 l_line_inst_dtl_desc_flex.attribute7,
326 l_line_inst_dtl_desc_flex.attribute8,
327 l_line_inst_dtl_desc_flex.attribute9,
328 l_line_inst_dtl_desc_flex.attribute10,
329 l_line_inst_dtl_desc_flex.attribute11,
330 l_line_inst_dtl_desc_flex.attribute12,
331 l_line_inst_dtl_desc_flex.attribute13,
332 l_line_inst_dtl_desc_flex.attribute14,
333 l_line_inst_dtl_desc_flex.attribute15,
334 l_party_site_id
335 FROM
336 HZ_CUST_ACCT_SITES_ALL CAS,
337 HZ_CUST_SITE_USES_ALL CSU,
338 SO_LINE_SERVICE_DETAILS LSD
339 WHERE
340 csu.cust_acct_site_id = cas.cust_acct_site_id (+)
341 AND lsd.installation_site_use_id = csu.site_use_id (+)
342 AND lsd.line_service_detail_id = p_line_inst_details_id;
343
344 x_line_inst_dtl_rec := l_line_inst_dtl_rec;
345
346 /* Assign the missing values */
347
348 x_line_inst_dtl_desc_flex := l_line_inst_dtl_desc_flex;
349
350 l_line_inst_dtl_rec.installed_at_party_site_id := l_party_site_id; -- 1949721, 2023975
351
352 EXCEPTION
353 WHEN OTHERS THEN
354 IF l_debug_level > 0 THEN
355 oe_debug_pub.add( 'ERROR IN GET_LINE_INST_DETAILS' ) ;
356 END IF;
357 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
358
359 End Get_Line_Inst_Details;
360
361 END oe_upg_install_details;