[Home] [Help]
PACKAGE BODY: APPS.OE_ACCEPTANCE_PVT
Source
1 PACKAGE BODY OE_ACCEPTANCE_PVT AS
2 /* $Header: OEXVACCB.pls 120.7.12020000.3 2013/01/15 12:59:53 aparava ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_ACCEPTANCE_PVT';
7
8 G_LINE_ID_TBL NUMBER_TYPE;
9 G_HEADER_ID_TBL NUMBER_TYPE;
10 G_REVREC_COMMENTS_TBL VARCHAR_2000_TYPE;
11 G_REVREC_REF_DOC_TBL VARCHAR_240_TYPE;
12 G_REVREC_SIGNATURE_TBL VARCHAR_240_TYPE;
13 G_REVREC_IMPLICIT_FLAG_TBL FLAG_TYPE;
14 G_REVREC_SIGNATURE_DATE_TBL DATE_TYPE;
15 G_ACCEPTED_BY_TBL NUMBER_TYPE;
16 G_ACCEPTED_QUANTITY_TBL NUMBER_TYPE;
17 G_FLOW_STATUS_TBL VARCHAR_30_TYPE;
18
19 PROCEDURE Reset_global_tbls IS
20 BEGIN
21 G_LINE_ID_TBL.delete;
22 G_HEADER_ID_TBL.delete;
23 G_REVREC_COMMENTS_TBL.delete;
24 G_REVREC_REF_DOC_TBL.delete;
25 G_REVREC_SIGNATURE_TBL.delete;
26 G_REVREC_IMPLICIT_FLAG_TBL.delete;
27 G_REVREC_SIGNATURE_DATE_TBL.delete;
28 G_ACCEPTED_BY_TBL.delete;
29 G_ACCEPTED_QUANTITY_TBL.delete;
30 G_FLOW_STATUS_TBL.delete;
31 END Reset_global_tbls;
32
33 PROCEDURE Process_Acceptance
34 ( p_request_tbl IN OUT NOCOPY OE_ORDER_PUB.request_tbl_type
35 ,p_index IN NUMBER DEFAULT 1
36 ,x_return_status OUT NOCOPY /* file.sql.39 change */ varchar2)
37 IS
38 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
39 --
40 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
41 --Start of bug 16085063
42 l_nameVal_tbl OE_ORDER_UTIL.nameVal_Tbl_Type;
43 l_count NUMBER;
44 --End of bug 16085063
45 --
46 BEGIN
47 IF l_debug_level > 0 THEN
48 oe_debug_pub.add('ENTERING OE_ACCEPTANCE_UTIL.Process_Acceptance' , 1);
49 END IF;
50
51 FOR i IN p_index..p_request_tbl.COUNT
52 LOOP
53 IF (p_request_tbl(i).processed = 'Y') OR
54 p_request_tbl(i).request_type NOT IN (OE_GLOBALS.G_ACCEPT_FULFILLMENT,OE_GLOBALS.G_REJECT_FULFILLMENT) THEN
55 GOTO END_LOOP;
56 END IF;
57 -- Initialize each request's return_status as 'S'. It will be set to 'E' when required.
58 p_request_tbl(i).return_status := FND_API.G_RET_STS_SUCCESS;
59 IF p_request_tbl(i).entity_code = 'HEADER' THEN
60
61 Build_Header_Acceptance_table(p_request_rec =>p_request_tbl(i)
62 ,x_return_status => l_return_status);
63
64 IF l_debug_level > 0 THEN
65 oe_debug_pub.add('sasi: p_request_rec return_status after Build_Header_Acceptance_table:'||p_request_tbl(i).return_status , 3);
66 END IF;
67
68 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
71 RAISE FND_API.G_EXC_ERROR;
72 END IF;
73 p_request_tbl(i).processed := 'Y';
74 ELSIF p_request_tbl(i).entity_code = 'LINE' THEN
75
76 Build_Line_Acceptance_table(p_request_rec => p_request_tbl(i)
77 , x_return_status => l_return_status);
78
79 IF l_debug_level > 0 THEN
80 oe_debug_pub.add('sasi: p_request_rec return_status after Build_Line_Acceptance_table:'||p_request_tbl(i).return_status , 3);
81 END IF;
82
83 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
84 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
85 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
86 RAISE FND_API.G_EXC_ERROR;
87 END IF;
88 p_request_tbl(i).processed := 'Y';
89 END IF;
90 << END_LOOP >> -- Label for requests that do not need to be processed
91 NULL;
92 END LOOP;
93
94 IF G_LINE_ID_TBL.count = 0 THEN
95
96 IF l_debug_level > 0 THEN
97 oe_debug_pub.add('NO ROWS to process' , 2 );
98 oe_debug_pub.add('EXITING OE_ACCEPTANCE_UTIL.Process_Acceptance' , 1);
99 END IF;
100 -- No lines are eligible for acceptance
101 FND_MESSAGE.set_name('ONT', 'ONT_ACCEPTANCE_NA');
102 RETURN;
103 END IF;
104
105 IF l_debug_level > 0 THEN
106 oe_debug_pub.add('IN OE_ACCEPTANCE_UTIL.Process_Acceptance - before bulk update' , 1);
107 END IF;
108
109 FORALL i in g_line_id_tbl.FIRST .. g_line_id_tbl.LAST
110 UPDATE oe_order_lines_all
111 SET revrec_comments = g_revrec_comments_tbl(i),
112 Revrec_reference_document = g_revrec_ref_doc_tbl(i),
113 Revrec_signature = g_revrec_signature_tbl(i),
114 Revrec_implicit_flag = g_revrec_implicit_flag_tbl(i),
115 Revrec_signature_date = g_revrec_signature_date_tbl(i),
116 Accepted_by = g_accepted_by_tbl(i),
117 Accepted_quantity = g_accepted_quantity_tbl(i)
118 WHERE line_id = g_line_id_tbl(i);
119
120 IF l_debug_level > 0 THEN
121 oe_debug_pub.add('IN OE_ACCEPTANCE_UTIL.Process_Acceptance - after bulk update' , 1);
122 END IF;
123
124 -- Progress workflow for each processed lines in a loop.
125 OE_ACCEPTANCE_PVT.Progress_Accepted_lines(l_return_status);
126
127 IF l_debug_level > 0 THEN
128 oe_debug_pub.add('IN OE_ACCEPTANCE_UTIL.Process_Acceptance-progress_accepted_lines return status:'|| l_return_status , 1);
129 END IF;
130 --Start of bug 16085063
131 IF l_return_status = FND_API.G_RET_STS_SUCCESS AND NVL (Fnd_Profile.Value('ONT_RAISE_STATUS_CHANGE_BUSINESS_EVENT'), 'N') = 'Y' THEN
132 IF l_debug_level > 0 then
133 oe_debug_pub.add( 'OE_ACCEPTANCE_PVT - Raise Integration event for Customer Acceptance');
134 oe_debug_pub.add( 'g_line_id_tbl count: '|| g_line_id_tbl.count);
135 END IF;
136
137 FOR i IN 1..g_line_id_tbl.count LOOP
138
139 IF l_debug_level > 0 then
140 oe_debug_pub.add( 'g_accepted_quantity: '|| g_accepted_quantity_tbl(i));
141 END IF;
142
143 IF g_accepted_quantity_tbl(i) <> 0 THEN
144 IF l_debug_level > 0 THEN
145 oe_debug_pub.add( 'OE_ACCEPTANCE_PVT - Preparing paramters to pass to Integration event for Customer Acceptance');
146 END IF;
147 l_count := 1;
148 l_nameVal_tbl(l_count).name := 'ACCEPTANCE_STATUS';
149 l_nameVal_tbl(l_count).value := 'ACCEPTED';
150 l_nameVal_tbl(l_count+1).name := 'ACCEPTED_BY';
151 l_nameVal_tbl(l_count+1).value := g_accepted_by_tbl(i);
152
153 IF l_debug_level > 0 then
154 oe_debug_pub.add('....line_id = ' || g_line_id_tbl(i));
155 END IF;
156
157 ELSE --For Rejection
158 IF l_debug_level > 0 THEN
159 oe_debug_pub.add( 'OE_ACCEPTANCE_PVT - Preparing paramters to pass to Integration event for Customer Rejection');
160 END IF;
161 l_count := 1;
162 l_nameVal_tbl(l_count).name := 'ACCEPTANCE_STATUS';
163 l_nameVal_tbl(l_count).value := 'REJECTED';
164 l_nameVal_tbl(l_count+1).name := 'REJECTED BY';
165 l_nameVal_tbl(l_count+1).value := g_accepted_by_tbl(i);
166 l_nameVal_tbl(l_count+2).name := 'REJECTION_REASON';
167 l_nameVal_tbl(l_count+2).value := g_revrec_comments_tbl(i);
168
169 IF l_debug_level > 0 then
170 oe_debug_pub.add('....line_id = ' || g_line_id_tbl(i));
171 END IF;
172 END IF;
173 IF l_debug_level > 0 then
174 oe_debug_pub.add('Calling oe_order_util.raise_business_event to raise event for Customer Acceptance/Rejection');
175 END IF;
176 oe_order_util.raise_business_event( p_line_id =>g_line_id_tbl(i),
177 p_event_name => 'oracle.apps.ont.oip.statuschange.update',
178 p_nameVal_tbl => l_nameVal_tbl);
179
180 END LOOP;
181 END IF;
182 --End of bug 16085063
183 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
184 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
185 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
186 RAISE FND_API.G_EXC_ERROR;
187 END IF;
188
189 Reset_global_tbls;
190
191 EXCEPTION
192 WHEN FND_API.G_EXC_ERROR THEN
193 RAISE FND_API.G_EXC_ERROR;
194 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
196 WHEN OTHERS THEN
197 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
198 THEN
199 OE_MSG_PUB.Add_Exc_Msg
200 ( G_PKG_NAME
201 , 'Process_Acceptance'
202 );
203 END IF;
204 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
205 END Process_Acceptance;
206
207 PROCEDURE Build_Header_Acceptance_table(p_request_rec IN OUT NOCOPY OE_ORDER_PUB.request_rec_type,
208 x_return_status OUT NOCOPY VARCHAR2)
209 IS
210 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
211 l_header_id NUMBER := p_request_rec.entity_id;
212 l_line_id NUMBER;
213 CURSOR order_lines is
214 SELECT line_id
215 FROM oe_order_lines_all
216 WHERE header_id = l_header_id
217 AND open_flag = 'Y'
218 AND flow_status_code in ('PRE-BILLING_ACCEPTANCE', 'POST-BILLING_ACCEPTANCE');
219 --
220 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
221 --
222 BEGIN
223
224 OPEN order_lines ;
225 LOOP
226 FETCH order_lines into l_line_id;
227 EXIT WHEN order_lines%NOTFOUND;
228
229 Build_Line_Acceptance_table(p_request_rec => p_request_rec
230 ,p_line_id => l_line_id
231 ,x_return_status => l_return_status);
232 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
233 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
234 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
235 RAISE FND_API.G_EXC_ERROR;
236 END IF;
237
238 END LOOP;
239
240 EXCEPTION
241 WHEN OTHERS THEN
242 IF l_debug_level > 0 THEN
243 oe_debug_pub.add(' Build_Header_Acceptance_table '|| SQLERRM , 1);
244 END IF;
245 x_return_status := l_return_status;
246
247 END Build_Header_Acceptance_table;
248
249
250 PROCEDURE Build_Line_Acceptance_table(p_request_rec IN OUT NOCOPY OE_ORDER_PUB.request_rec_type
251 ,p_line_id IN NUMBER DEFAULT NULL
252 ,x_return_status OUT NOCOPY VARCHAR2)
253 IS
254 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
255 l_line_id NUMBER := nvl(p_line_id, p_request_rec.entity_id);
256 l_header_id NUMBER;
257 l_item_type_code VARCHAR2(30);
258 l_top_model_line_id NUMBER;
259 l_flow_status_code VARCHAR2(30);
260 l_line_count NUMBER := 0;
261 l_notify_index NUMBER := NULL;
262 l_notify_status VARCHAR2(1);
263 l_fulfilled_quantity NUMBER;
264 l_ordered_quantity NUMBER;
265 l_shipped_quantity NUMBER;
266
267 CURSOR model_children is
268 SELECT line_id, fulfilled_quantity,shipped_quantity, ordered_quantity,header_id, flow_status_code
269 FROM oe_order_lines_all
270 WHERE header_id = l_header_id
271 AND open_flag = 'Y'
272 AND top_model_line_id = l_line_id;
273
274 CURSOR c_service_lines is
275 SELECT line_id, fulfilled_quantity, shipped_quantity, ordered_quantity, header_id, flow_status_code
276 FROM oe_order_lines_all
277 WHERE open_flag='Y'
278 AND service_reference_line_id = l_line_id;
279
280
281 --
282 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
283 --
284
285 BEGIN
286 IF l_line_id IS NOT NULL THEN
287
288 l_line_count := G_LINE_ID_TBL.COUNT;
289 -- changes for bug# 5232503
290 -- accept all lines sent by implicit program without any check for eligibility if system param is turned off
291 IF NVL(OE_SYS_PARAMETERS.VALUE('ENABLE_FULFILLMENT_ACCEPTANCE'), 'N') = 'N' THEN
292 SELECT header_id, item_type_code, top_model_line_id, fulfilled_quantity,
293 shipped_quantity, ordered_quantity, flow_status_code
294 INTO l_header_id, l_item_type_code, l_top_model_line_id, l_fulfilled_quantity,
295 l_shipped_quantity, l_ordered_quantity, l_flow_status_code
296 FROM oe_order_lines_all
297 WHERE line_id=l_line_id;
298
299 l_line_count := l_line_count + 1;
300 G_LINE_ID_TBL(l_line_count) := l_line_id;
301 IF p_request_rec.request_type = OE_GLOBALS.G_ACCEPT_FULFILLMENT THEN
302 G_ACCEPTED_QUANTITY_TBL(l_line_count) := nvl(l_fulfilled_quantity,nvl(l_shipped_quantity,nvl(l_ordered_quantity,0)));
303 ELSE
304 G_ACCEPTED_QUANTITY_TBL(l_line_count) := 0;
305 END IF;
306 G_REVREC_COMMENTS_TBL(l_line_count) := p_request_rec.param1;
307 G_REVREC_SIGNATURE_TBL(l_line_count) := p_request_rec.param2;
308 G_REVREC_REF_DOC_TBL(l_line_count) := p_request_rec.param3;
309 G_REVREC_IMPLICIT_FLAG_TBL(l_line_count) := p_request_rec.param4;
310 G_HEADER_ID_TBL(l_line_count) := l_header_id;
311 G_REVREC_SIGNATURE_DATE_TBL(l_line_count) := nvl(p_request_rec.date_param1,sysdate);
312 G_ACCEPTED_BY_TBL(l_line_count) := FND_GLOBAL.USER_ID;
313 G_FLOW_STATUS_TBL(l_line_count) := l_flow_status_code;
314
315 IF OE_GLOBALS.G_UPDATE_GLOBAL_PICTURE = 'Y' THEN
316 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
317 p_line_id => l_line_id,
318 x_index => l_notify_index,
319 x_return_status => l_notify_status);
320
321 IF (l_notify_status <> FND_API.G_RET_STS_SUCCESS) THEN
322 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
323 END IF;
324
325 IF l_notify_index IS NOT NULL THEN
326 OE_ORDER_UTIL.g_line_tbl(l_notify_index) := OE_ORDER_UTIL.g_old_line_tbl(l_notify_index);
327 IF p_request_rec.request_type = OE_GLOBALS.G_ACCEPT_FULFILLMENT THEN
331 OE_ORDER_UTIL.g_line_tbl(l_notify_index).accepted_quantity :=0;
328 OE_ORDER_UTIL.g_line_tbl(l_notify_index).accepted_quantity :=
329 nvl(l_fulfilled_quantity,nvl(l_shipped_quantity, nvl(l_ordered_quantity,0)));
330 ELSE
332 END IF;
333 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_comments := p_request_rec.param1;
334 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_signature := p_request_rec.param2;
335 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_reference_document := p_request_rec.param3;
336 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_implicit_flag := p_request_rec.param4;
337 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_signature_date := nvl(p_request_rec.date_param1,sysdate);
338 OE_ORDER_UTIL.g_line_tbl(l_notify_index).ACCEPTED_BY := FND_GLOBAL.USER_ID;
339 END IF;
340
341 END IF;
342 ELSE -- system param on
343
344 IF OE_ACCEPTANCE_UTIL.Customer_Acceptance_Eligible(l_line_id) THEN
345
346 SELECT header_id, item_type_code, top_model_line_id, fulfilled_quantity,
347 shipped_quantity, ordered_quantity, flow_status_code
348 INTO l_header_id, l_item_type_code, l_top_model_line_id, l_fulfilled_quantity,
349 l_shipped_quantity, l_ordered_quantity, l_flow_status_code
350 FROM oe_order_lines_all
351 WHERE line_id=l_line_id;
352
353
354 --Entering children lines in to global tbls
355 IF (l_item_type_code = 'MODEL' OR l_item_type_code='KIT') AND
356 ( l_top_model_line_id IS NOT NULL AND l_top_model_line_id = l_line_id) THEN
357
358 OPEN model_children ;
359 LOOP
360 FETCH model_children into l_line_id, l_fulfilled_quantity, l_shipped_quantity,
361 l_ordered_quantity, l_header_id, l_flow_status_code;
362 EXIT when model_children%NOTFOUND;
363 l_line_count := l_line_count+1;
364 G_LINE_ID_TBL(l_line_count) := l_line_id;
365 IF p_request_rec.request_type = OE_GLOBALS.G_ACCEPT_FULFILLMENT THEN
366 G_ACCEPTED_QUANTITY_TBL(l_line_count) := nvl(l_fulfilled_quantity,nvl(l_shipped_quantity,
367 nvl(l_ordered_quantity,0)));
368 ELSE
369 G_ACCEPTED_QUANTITY_TBL(l_line_count) := 0;
370 END IF;
371 G_REVREC_COMMENTS_TBL(l_line_count) := p_request_rec.param1;
372 G_REVREC_SIGNATURE_TBL(l_line_count) := p_request_rec.param2;
373 G_REVREC_REF_DOC_TBL(l_line_count) := p_request_rec.param3;
374 G_REVREC_IMPLICIT_FLAG_TBL(l_line_count) := p_request_rec.param4;
375 G_HEADER_ID_TBL(l_line_count) := l_header_id;
376 G_REVREC_SIGNATURE_DATE_TBL(l_line_count) := nvl(p_request_rec.date_param1,sysdate);
377 G_ACCEPTED_BY_TBL(l_line_count) := FND_GLOBAL.USER_ID;
378 G_FLOW_STATUS_TBL(l_line_count) := l_flow_status_code;
379
380 IF OE_GLOBALS.G_UPDATE_GLOBAL_PICTURE = 'Y' THEN
381 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
382 p_line_id => l_line_id,
383 x_index => l_notify_index,
384 x_return_status => l_notify_status);
385
386 IF (l_notify_status <> FND_API.G_RET_STS_SUCCESS) THEN
387 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
388 END IF;
389
390 IF l_notify_index IS NOT NULL THEN
391 OE_ORDER_UTIL.g_line_tbl(l_notify_index) := OE_ORDER_UTIL.g_old_line_tbl(l_notify_index);
392 IF p_request_rec.request_type = OE_GLOBALS.G_ACCEPT_FULFILLMENT THEN
393 OE_ORDER_UTIL.g_line_tbl(l_notify_index).accepted_quantity :=
394 nvl(l_fulfilled_quantity,nvl(l_shipped_quantity,nvl(l_ordered_quantity,0)));
395 ELSE
396 OE_ORDER_UTIL.g_line_tbl(l_notify_index).accepted_quantity :=0;
397 END IF;
398 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_comments := p_request_rec.param1;
399 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_signature := p_request_rec.param2;
400 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_reference_document := p_request_rec.param3;
401 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_implicit_flag := p_request_rec.param4;
402 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_signature_date := nvl(p_request_rec.date_param1,sysdate);
403 OE_ORDER_UTIL.g_line_tbl(l_notify_index).ACCEPTED_BY := FND_GLOBAL.USER_ID;
404 END IF;
405 END IF;
406
407 OPEN c_service_lines ;
408 LOOP
409 FETCH c_service_lines into l_line_id, l_fulfilled_quantity, l_shipped_quantity,
410 l_ordered_quantity, l_header_id, l_flow_status_code;
411 EXIT when c_service_lines%NOTFOUND;
412
413 l_line_count := l_line_count + 1;
414 G_LINE_ID_TBL(l_line_count) := l_line_id;
415 IF p_request_rec.request_type = OE_GLOBALS.G_ACCEPT_FULFILLMENT THEN
416 G_ACCEPTED_QUANTITY_TBL(l_line_count) := nvl(l_fulfilled_quantity,nvl(l_shipped_quantity,nvl(l_ordered_quantity,0)));
417 ELSE
418 G_ACCEPTED_QUANTITY_TBL(l_line_count) := 0;
419 END IF;
420 G_REVREC_COMMENTS_TBL(l_line_count) := p_request_rec.param1;
421 G_REVREC_SIGNATURE_TBL(l_line_count) := p_request_rec.param2;
422 G_REVREC_REF_DOC_TBL(l_line_count) := p_request_rec.param3;
423 G_REVREC_IMPLICIT_FLAG_TBL(l_line_count) := p_request_rec.param4;
424 G_HEADER_ID_TBL(l_line_count) := l_header_id;
428
425 G_REVREC_SIGNATURE_DATE_TBL(l_line_count) := nvl(p_request_rec.date_param1,sysdate);
426 G_ACCEPTED_BY_TBL(l_line_count) := FND_GLOBAL.USER_ID;
427 G_FLOW_STATUS_TBL(l_line_count) := l_flow_status_code;
429 IF OE_GLOBALS.G_UPDATE_GLOBAL_PICTURE = 'Y' THEN
430 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
431 p_line_id => l_line_id,
432 x_index => l_notify_index,
433 x_return_status => l_notify_status);
434
435 IF (l_notify_status <> FND_API.G_RET_STS_SUCCESS) THEN
436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
437 END IF;
438
439 IF l_notify_index IS NOT NULL THEN
440 OE_ORDER_UTIL.g_line_tbl(l_notify_index) := OE_ORDER_UTIL.g_old_line_tbl(l_notify_index);
441 IF p_request_rec.request_type = OE_GLOBALS.G_ACCEPT_FULFILLMENT THEN
442 OE_ORDER_UTIL.g_line_tbl(l_notify_index).accepted_quantity :=
443 nvl(l_fulfilled_quantity,nvl(l_shipped_quantity,nvl(l_ordered_quantity,0)));
444 ELSE
445 OE_ORDER_UTIL.g_line_tbl(l_notify_index).accepted_quantity :=0;
446 END IF;
447 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_comments := p_request_rec.param1;
448 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_signature := p_request_rec.param2;
449 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_reference_document := p_request_rec.param3;
450 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_implicit_flag := p_request_rec.param4;
451 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_signature_date := nvl(p_request_rec.date_param1,sysdate);
452 OE_ORDER_UTIL.g_line_tbl(l_notify_index).ACCEPTED_BY := FND_GLOBAL.USER_ID;
453 END IF;
454 END IF;
455 END LOOP;
456 close c_service_lines;
457 END LOOP;
458 ELSE --standard lines
459 l_line_count := l_line_count + 1;
460 G_LINE_ID_TBL(l_line_count) := l_line_id;
461 IF p_request_rec.request_type = OE_GLOBALS.G_ACCEPT_FULFILLMENT THEN
462 G_ACCEPTED_QUANTITY_TBL(l_line_count) := nvl(l_fulfilled_quantity,nvl(l_shipped_quantity,
463 nvl(l_ordered_quantity,0)));
464 ELSE
465 G_ACCEPTED_QUANTITY_TBL(l_line_count) := 0;
466 END IF;
467 G_REVREC_COMMENTS_TBL(l_line_count) := p_request_rec.param1;
468 G_REVREC_SIGNATURE_TBL(l_line_count) := p_request_rec.param2;
469 G_REVREC_REF_DOC_TBL(l_line_count) := p_request_rec.param3;
470 G_REVREC_IMPLICIT_FLAG_TBL(l_line_count) := p_request_rec.param4;
471 G_HEADER_ID_TBL(l_line_count) := l_header_id;
472 G_REVREC_SIGNATURE_DATE_TBL(l_line_count) := nvl(p_request_rec.date_param1,sysdate);
473 G_ACCEPTED_BY_TBL(l_line_count) := FND_GLOBAL.USER_ID;
474 G_FLOW_STATUS_TBL(l_line_count) := l_flow_status_code;
475
476 IF OE_GLOBALS.G_UPDATE_GLOBAL_PICTURE = 'Y' THEN
477 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
478 p_line_id => l_line_id,
479 x_index => l_notify_index,
480 x_return_status => l_notify_status);
481
482 IF (l_notify_status <> FND_API.G_RET_STS_SUCCESS) THEN
483 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
484 END IF;
485
486 IF l_notify_index IS NOT NULL THEN
487 OE_ORDER_UTIL.g_line_tbl(l_notify_index) := OE_ORDER_UTIL.g_old_line_tbl(l_notify_index);
488 IF p_request_rec.request_type = OE_GLOBALS.G_ACCEPT_FULFILLMENT THEN
489 OE_ORDER_UTIL.g_line_tbl(l_notify_index).accepted_quantity :=
490 nvl(l_fulfilled_quantity,nvl(l_shipped_quantity,
491 nvl(l_ordered_quantity,0)));
492 ELSE
493 OE_ORDER_UTIL.g_line_tbl(l_notify_index).accepted_quantity :=0;
494 END IF;
495 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_comments := p_request_rec.param1;
496 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_signature := p_request_rec.param2;
497 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_reference_document := p_request_rec.param3;
498 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_implicit_flag := p_request_rec.param4;
499 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_signature_date := nvl(p_request_rec.date_param1,sysdate);
500 OE_ORDER_UTIL.g_line_tbl(l_notify_index).ACCEPTED_BY := FND_GLOBAL.USER_ID;
501 END IF;
502
503 END IF;
504 --Entering service lines for parent line
505 OPEN c_service_lines ;
506 LOOP
507 FETCH c_service_lines into l_line_id, l_fulfilled_quantity, l_shipped_quantity, l_ordered_quantity,
508 l_header_id, l_flow_status_code;
509 EXIT when c_service_lines%NOTFOUND;
510 l_line_count := l_line_count + 1;
511 G_LINE_ID_TBL(l_line_count) := l_line_id;
512 IF p_request_rec.request_type = OE_GLOBALS.G_ACCEPT_FULFILLMENT THEN
513 G_ACCEPTED_QUANTITY_TBL(l_line_count):= nvl(l_fulfilled_quantity,nvl(l_shipped_quantity,nvl(l_ordered_quantity,0)));
514 ELSE
515 G_ACCEPTED_QUANTITY_TBL(l_line_count) := 0;
516 END IF;
517 G_REVREC_COMMENTS_TBL(l_line_count) := p_request_rec.param1;
518 G_REVREC_SIGNATURE_TBL(l_line_count) := p_request_rec.param2;
519 G_REVREC_REF_DOC_TBL(l_line_count) := p_request_rec.param3;
520 G_REVREC_IMPLICIT_FLAG_TBL(l_line_count) := p_request_rec.param4;
521 G_HEADER_ID_TBL(l_line_count) := l_header_id;
525
522 G_REVREC_SIGNATURE_DATE_TBL(l_line_count) := nvl(p_request_rec.date_param1,sysdate);
523 G_ACCEPTED_BY_TBL(l_line_count) := FND_GLOBAL.USER_ID;
524 G_FLOW_STATUS_TBL(l_line_count) := l_flow_status_code;
526 IF OE_GLOBALS.G_UPDATE_GLOBAL_PICTURE = 'Y' THEN
527 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
528 p_line_id => l_line_id,
529 x_index => l_notify_index,
530 x_return_status => l_notify_status);
531
532 IF (l_notify_status <> FND_API.G_RET_STS_SUCCESS) THEN
533 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
534 END IF;
535
536 IF l_notify_index IS NOT NULL THEN
537 OE_ORDER_UTIL.g_line_tbl(l_notify_index) := OE_ORDER_UTIL.g_old_line_tbl(l_notify_index);
538 IF p_request_rec.request_type = OE_GLOBALS.G_ACCEPT_FULFILLMENT THEN
539 OE_ORDER_UTIL.g_line_tbl(l_notify_index).accepted_quantity :=
540 nvl(l_fulfilled_quantity,nvl(l_shipped_quantity,nvl(l_ordered_quantity,0)));
541 ELSE
542 OE_ORDER_UTIL.g_line_tbl(l_notify_index).accepted_quantity :=0;
543 END IF;
544 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_comments := p_request_rec.param1;
545 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_signature := p_request_rec.param2;
546 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_reference_document := p_request_rec.param3;
547 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_implicit_flag := p_request_rec.param4;
548 OE_ORDER_UTIL.g_line_tbl(l_notify_index).revrec_signature_date := nvl(p_request_rec.date_param1,sysdate);
549 OE_ORDER_UTIL.g_line_tbl(l_notify_index).ACCEPTED_BY := FND_GLOBAL.USER_ID;
550
551 END IF;
552 END IF;
553 END LOOP;
554 CLOSE c_service_lines;
555 END IF;
556 ELSE
557 IF l_debug_level > 0 THEN
558 OE_DEBUG_PUB.Add('Acceptance not allowed for this line. setting retun status of request_rec to E:'||l_line_id);
559 END IF;
560 p_request_rec.return_status := FND_API.G_RET_STS_ERROR;
561 END IF;
562 END IF; -- sys param check
563 END IF;
564 EXCEPTION
565 WHEN OTHERS THEN
566 IF l_debug_level > 0 THEN
567 oe_debug_pub.add('Build_Line_Acceptance_table '|| SQLERRM , 1);
568 END IF;
569 x_return_status := l_return_status;
570
571 END Build_Line_Acceptance_table;
572
573 PROCEDURE Progress_Accepted_lines
574 (x_return_status OUT NOCOPY Varchar2)
575 IS
576 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
577 l_msg_count NUMBER;
578 l_msg_data VARCHAR2(2000);
579 l_line_flex_rec ar_deferral_reasons_grp.line_flex_rec;
580 --
581 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
582 --
583 l_org_id oe_order_lines_all.org_id%TYPE; -- Bug 8859412
584 l_inventory_item_id oe_order_lines_all.inventory_item_id%TYPE;
585 BEGIN
586
587 IF l_debug_level > 0 THEN
588 oe_debug_pub.add('ENTERING Progress_ Accepted_Lines' , 1);
589 END IF;
590
591 FOR i IN 1..g_line_id_tbl.count LOOP
592 IF l_debug_level > 0 THEN
593 oe_debug_pub.add('Processing line_id:'||g_line_id_tbl(i));
594 END IF;
595 IF g_flow_status_tbl(i) = 'PRE-BILLING_ACCEPTANCE' THEN
596 BEGIN
597 WF_ENGINE.CompleteActivityInternalName(OE_GLOBALS.G_WFI_LIN, g_line_id_tbl(i), 'INVOICE_PENDING_ACCEPTANCE' , 'COMPLETE');
598 EXCEPTION
599 WHEN OTHERS THEN
600 IF l_debug_level > 0 THEN
601 oe_debug_pub.add('error progressing INVOICE_PENDING_ACCEPTANCE for line_id='||g_line_id_tbl(i)||sqlerrm, 1);
602 END IF;
603 null;
604 END;
605 ELSIF g_flow_status_tbl(i) = 'POST-BILLING_ACCEPTANCE' THEN -- pending at close line'
606 BEGIN
607 WF_ENGINE.CompleteActivityInternalName(OE_GLOBALS.G_WFI_LIN, g_line_id_tbl(i), 'CLOSE_PENDING_ACCEPTANCE', 'COMPLETE');
608 EXCEPTION
609 WHEN OTHERS THEN
610 IF l_debug_level > 0 THEN
611 oe_debug_pub.add('error progressing CLOSE_PENDING_ACCEPTANCE for line_id='||g_line_id_tbl(i)||sqlerrm, 1);
612 END IF;
613 null;
614 END;
615 END IF;
616 -- Start bug 8859412
617
618
619 IF g_accepted_quantity_tbl(i) = 0 THEN -- For rejection, Accepted Quantity = 0
620
621 oe_debug_pub.add('Customer is Rejected');
622
623 SELECT inventory_item_id, org_id
624 INTO l_inventory_item_id, l_org_id
625 FROM oe_order_lines_all
626 WHERE line_id = g_line_id_tbl(i);
627
628
629
630 cst_revenuecogsmatch_grp.receive_closelineevent (
631 p_api_version => 1.0,
632 p_init_msg_list => FND_API.G_FALSE,
633 p_commit => FND_API.G_FALSE,
634 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
635 x_return_status => l_return_status,
636 x_msg_count => l_msg_count,
637 x_msg_data => l_msg_data,
638 p_revenue_event_line_id => g_line_id_tbl(i),
639 p_event_date => SYSDATE,
640 p_ou_id => l_org_id,
641 p_inventory_item_id => l_inventory_item_id);
642
643 -- Start of bug 16085063
644 x_return_status := l_return_status;
645 IF l_debug_level > 0 THEN
646 oe_debug_pub.add('return status from cst_revenuecogsmatch_grp.receive_closelineevent : '||l_return_status);
647 END IF;
648 -- End of bug 16085063
649
650
651 END IF;
652
653
657 SET flow_status_code='NOTIFY_COSTING_ERROR'
654 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
655
656 UPDATE oe_order_lines_all
658 WHERE line_id = g_line_id_tbl(i);
659 x_return_status := l_return_status;
660 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
661 return;
662 END IF;
663 -- End Bug 8859412
664
665 --AR should be notified only for post-billing acceptance and not for rejection.(i.e. if accepted_quantity=0)
666 IF g_accepted_quantity_tbl(i) is not null and g_accepted_quantity_tbl(i) <>0 THEN
667 OE_AR_Acceptance_GRP.Get_interface_attributes
668 ( p_line_id => g_line_id_tbl(i)
669 , x_line_flex_rec => l_line_flex_rec
670 , x_return_status => l_return_status
671 , x_msg_count => l_msg_count
672 , x_msg_data => l_msg_data
673 );
674
675 IF l_debug_level > 0 THEN
676 oe_debug_pub.add('return status from OE_AR_Acceptance_GRP.Get_interface_attributes : '||l_return_status); --added for bug 16085063
677 END IF;
678
679
680 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
681 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
682 -- no need to raise if the info cannot be derived. just do not call AR.
683 --ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
684 -- RAISE FND_API.G_EXC_ERROR;
685 END IF;
686
687 IF l_line_flex_rec.interface_line_attribute6 IS NOT NULL THEN
688 IF l_debug_level > 0 THEN
689 oe_debug_pub.add('Calling ar_deferral_reasons_grp.record_acceptance');
690 END IF;
691 ar_deferral_reasons_grp.record_acceptance (
692 p_api_version => 1.0,
693 p_order_line => l_line_flex_rec,
694 x_return_status => l_return_status ,
695 x_msg_count =>l_msg_count,
696 x_msg_data => l_msg_data );
697
698 x_return_status := l_return_status; --Added for bug 16085063
699 IF l_debug_level > 0 THEN
700 oe_debug_pub.add('return status from AR api '||l_return_status);
701 END IF;
702
703 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
704 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
705 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
706 RAISE FND_API.G_EXC_ERROR;
707 END IF;
708 END IF;
709
710 END IF;
711 END LOOP;
712 EXCEPTION
713 WHEN OTHERS THEN
714 IF l_debug_level > 0 THEN
715 oe_debug_pub.add('Progress_ Accepted_Lines '|| SQLERRM , 1);
716 END IF;
717 x_return_status := l_return_status;
718 END Progress_Accepted_lines;
719
720 END OE_ACCEPTANCE_PVT;