1 PACKAGE BODY OE_ACCEPTANCE_UTIL AS
2 /* $Header: OEXUACCB.pls 120.16.12010000.3 2009/07/03 11:33:27 nitagarw ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_ACCEPTANCE_UTIL';
7
8 G_batch_source_id NUMBER := FND_API.G_MISS_NUM;
9 G_batch_source_name VARCHAR2(50) := FND_API.G_MISS_CHAR;
10 G_BINARY_LIMIT CONSTANT NUMBER := OE_GLOBALS.G_BINARY_LIMIT; -- Bug 8656395
11
12 PROCEDURE Register_Changed_Lines (
13 p_line_id IN NUMBER
14 , p_header_id IN NUMBER
18 , p_inventory_item_id IN NUMBER
15 , p_line_type_id IN NUMBER
16 , p_sold_to_org_id IN NUMBER
17 , p_invoice_to_org_id IN NUMBER
19 , p_shippable_flag IN VARCHAR2
20 , p_org_id IN NUMBER
21 , p_accounting_rule_id IN NUMBER
22 , p_operation IN VARCHAR2
23 , p_ship_to_org_id IN NUMBER DEFAULT NULL --For bug#8262992
24 ) IS
25
26 l_line_index NUMBER := 0;
27 l_mod_line_id NUMBER;
28 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
29
30 BEGIN
31 IF l_debug_level > 0 THEN
32 oe_debug_pub.add('Entering OE_ACCEPTANCE_UTIL.Register_Changed_Lines ' );
33 END IF;
34
35 IF l_debug_level > 0 THEN
36 oe_debug_pub.add('Operation value is ' || p_operation );
37 END IF;
38
39 l_mod_line_id := MOD(p_line_id, G_BINARY_LIMIT); -- Bug 8656395
40 IF p_operation In (OE_GLOBALS.G_OPR_CREATE, OE_GLOBALS.G_OPR_UPDATE) THEN
41 IF G_line_index_Tbl.exists(l_mod_line_id) THEN
42 l_line_index := G_line_index_tbl(l_mod_line_id).line_index; -- Replaced p_line_id with l_mod_line_id 8656395
43 ELSE
44 l_line_index := G_line_id_tbl.count + 1;
45 G_line_index_tbl(l_mod_line_id).line_index := l_line_index;
46 END IF;
47
48 IF l_debug_level > 0 THEN
49 oe_debug_pub.add('Line Index value is ' || l_line_index );
50 END IF;
51
52 G_Line_id_tbl(l_line_index) := nvl(p_line_id, -99);
53 G_header_id_tbl(l_line_index) := nvl(p_header_id, -99);
54 G_line_type_id_tbl(l_line_index) := nvl(p_line_type_id, -99);
55 G_sold_to_org_id_tbl(l_line_index) := nvl(p_sold_to_org_id, -99);
56 G_invoice_to_org_id_tbl(l_line_index) := nvl(p_invoice_to_org_id, -99);
57 G_inventory_item_id_tbl(l_line_index) := nvl(p_inventory_item_id, -99);
58 G_shippable_flag_tbl(l_line_index) := nvl(p_shippable_flag, -99);
59 G_org_id_tbl(l_line_index) := nvl(p_org_id, -99);
60 G_accounting_rule_id_tbl(l_line_index) := nvl(p_accounting_rule_id, -99);
61 --For Bug#8262992
62 G_ship_to_org_id_tbl(l_line_index) := nvl(p_ship_to_org_id, -99);
63
64 ELSIF p_operation = OE_GLOBALS.G_OPR_DELETE THEN
65
66 IF G_line_index_tbl.exists(l_mod_line_id) THEN
67 IF l_debug_level > 0 THEN
68 oe_debug_pub.add('Line Index value is ' || l_line_index ); -- Replaced p_line_id with l_mod_line_id 8656395
69 END IF;
70 G_Line_id_tbl(G_line_index_tbl(l_mod_line_id).line_index) := -99;
71 G_line_index_tbl.delete(l_mod_line_id);
72 END IF;
73
74 END IF;
75
76 IF l_debug_level > 0 THEN
77 oe_debug_pub.add('Exiting OE_ACCEPTANCE_UTIL.Register_Changed_Lines ' );
78 END IF;
79
80 END Register_Changed_Lines;
81
82 PROCEDURE Delete_Changed_Lines_Tbl IS
83 BEGIN
84 G_line_index_tbl.delete;
85 G_line_id_tbl.delete;
86 G_header_id_tbl.delete;
87 G_line_type_id_tbl.delete;
88 G_sold_to_org_id_tbl.delete;
89 G_invoice_to_org_id_tbl.delete;
90 G_inventory_item_id_tbl.delete;
91 G_org_id_tbl.delete;
92 G_accounting_rule_id_tbl.delete;
93 G_batch_source_id_tbl.delete;
94 G_cust_trx_type_id_tbl.delete;
95 G_invoice_to_customer_tbl.delete;
96 G_invoice_to_site_tbl.delete;
97 G_shippable_flag_tbl.delete;
98 G_accounting_rule_id_tbl.delete;
99 --For Bug#8262992
100 G_ship_to_customer_tbl.delete;
101 G_ship_to_org_id_tbl.delete;
102 G_ship_to_site_tbl.delete;
103 END Delete_Changed_Lines_Tbl;
104
105 FUNCTION Get_batch_source_ID
106 (p_batch_source_name VARCHAR2)
107 RETURN NUMBER IS
108
109 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
110
111 BEGIN
112 IF l_debug_level > 0 THEN
113 oe_debug_pub.add('Entering OE_ACCEPTANCE_UTIL.Get_batch_source_ID ' );
114 END IF;
115
116 IF l_debug_level > 0 THEN
117 oe_debug_pub.add('IN Batch source name is ' || p_batch_source_name );
118 END IF;
119
120 IF p_batch_source_name IS NOT NULL THEN
121 IF g_batch_source_name = FND_API.G_MISS_CHAR OR
122 G_batch_source_name <> p_batch_source_name THEN
123
124 SELECT batch_source_id,
125 name
126 INTO g_batch_source_id,
127 g_batch_source_name
128 FROM ra_batch_sources
129 WHERE name = p_batch_source_name;
130
131 END IF;
132
133 IF l_debug_level > 0 THEN
134 oe_debug_pub.add('OUT Batch source name: ' || g_batch_source_name||' :batch_source_id:'||g_batch_source_id );
135 END IF;
136
137 RETURN g_batch_source_id;
138 ELSE
139 RETURN -99;
140 END IF;
141
142 IF l_debug_level > 0 THEN
143 oe_debug_pub.add('Exiting OE_ACCEPTANCE_UTIL.Get_batch_source_ID ' );
144 END IF;
145
146 EXCEPTION
147 WHEN NO_DATA_FOUND THEN
148 RETURN -99;
149 END get_batch_source_ID;
150
151 PROCEDURE Default_Contingency_Attributes IS
152
153 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
154
155 l_return_status VARCHAR2(1);
156 l_msg_count NUMBER;
157 l_msg_data VARCHAR2(2000);
158
159 l_invoice_numbering_method VARCHAR2(1) := NVL(FND_PROFILE.VALUE('WSH_INVOICE_NUMBERING_METHOD'), 'A');
160 l_line_type_rec OE_Order_Cache.line_type_Rec_Type;
161 l_order_type_rec OE_Order_Cache.order_type_Rec_Type;
162
166
163 l_cust_trx_type_id NUMBER;
164 l_invoice_source_id NUMBER;
165 l_non_d_invoice_source_id NUMBER;
167 l_cust_trx_type_id2 NUMBER;
168 l_invoice_source_id2 NUMBER;
169 l_non_d_invoice_source_id2 NUMBER;
170
171 l_line_id_old NUMBER;
172 l_line_id_new NUMBER;
173 l_inserted_lines NUMBER;
174
175 CURSOR default_contingencies IS
176 SELECT id, contingency_id, revrec_event_code, expiration_days
177 FROM fun_rule_bulk_result_gt gt,
178 ar_deferral_reasons dr
179 WHERE gt.result_value = dr.contingency_id
180 AND revrec_event_code in ('INVOICING', 'CUSTOMER_ACCEPTANCE')
181 ORDER BY id, revrec_event_code DESC, expiration_days, creation_date DESC;
182
183 BEGIN
184 IF l_debug_level > 0 THEN
185 oe_debug_pub.add('Entering OE_ACCEPTANCE_UTIL.Default_Contingency_Attributes ' );
186 END IF;
187
188 FOR i in 1..g_Line_id_tbl.count LOOP
189
190 IF g_line_id_tbl(i) = -99 THEN
191 IF l_debug_level > 0 THEN
192 oe_debug_pub.add('Setting remaining attributes as -99');
193 END IF;
194 g_invoice_to_customer_tbl(i) := -99;
195 g_invoice_to_site_tbl(i) := -99;
196 g_cust_trx_type_id_tbl(i) := -99;
197 g_batch_source_id_tbl(i) := -99;
198 --For Bug#8262992
199 g_ship_to_customer_tbl(i) := -99;
200 g_ship_to_site_tbl(i) := -99;
201 ELSE
202 -- populate customer account and customer account site
203 BEGIN
204 IF g_invoice_to_org_id_tbl(i) IS NOT NULL and g_invoice_to_org_id_tbl(i) <> -99 THEN
205 SELECT acct_site.cust_account_id, site.cust_acct_site_id
206 INTO g_invoice_to_customer_tbl(i),
207 g_invoice_to_site_tbl(i)
208 FROM hz_cust_acct_sites_all acct_site,
209 hz_cust_site_uses_all site
210 WHERE SITE.SITE_USE_CODE = 'BILL_TO'
211 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
212 AND SITE.SITE_USE_ID = g_invoice_to_org_id_tbl(i);
213 ELSE
214 -- bug 4995169 when invoice to org id is NULL, error
215 g_invoice_to_customer_tbl(i) := -99;
216 g_invoice_to_site_tbl(i) := -99;
217 END IF;
218 --For Bug#8262992
219 IF g_ship_to_org_id_tbl(i) IS NOT NULL and g_ship_to_org_id_tbl(i) <> -99 THEN
220 SELECT acct_site.cust_account_id, site.cust_acct_site_id
221 INTO g_ship_to_customer_tbl(i),
222 g_ship_to_site_tbl(i)
223 FROM hz_cust_acct_sites_all acct_site,
224 hz_cust_site_uses_all site
225 WHERE SITE.SITE_USE_CODE = 'SHIP_TO'
226 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
227 AND SITE.SITE_USE_ID = g_ship_to_org_id_tbl(i);
228 ELSE
229 -- bug 4995169 when invoice to org id is NULL, error
230 g_ship_to_customer_tbl(i) := -99;
231 g_ship_to_site_tbl(i) := -99;
232 END IF;
233 --End of Bug#8262992
234 EXCEPTION
235 WHEN NO_DATA_FOUND THEN
236 g_invoice_to_customer_tbl(i) := -99;
237 g_invoice_to_site_tbl(i) := -99;
238 --For Bug#8262992
239 g_ship_to_customer_tbl(i) := -99;
240 g_ship_to_site_tbl(i) := -99;
241 END;
242
243 -- Populate cust_trx_type_id, invoice_source_id, non_delivery_inoice_source_id
244 l_line_type_rec := oe_order_cache.load_line_type(g_line_type_id_tbl(i));
245 l_cust_trx_type_id := l_line_type_rec.cust_trx_type_id;
246 l_invoice_source_id := l_line_type_rec.invoice_source_id;
247 l_non_d_invoice_source_id := l_line_type_rec.non_delivery_invoice_source_id;
248
249 IF l_debug_level > 0 THEN
250 oe_debug_pub.add('Line type details... ' );
251 oe_debug_pub.add('Customer Transaction type : ' || l_cust_trx_type_id );
252 oe_debug_pub.add('Invoice Source : ' || l_invoice_source_id );
253 oe_debug_pub.add('Non Delivery Invoice Source : ' || l_non_d_invoice_source_id );
254 END IF;
255
256 IF l_cust_trx_type_id IS NULL OR
257 (l_invoice_source_id IS NULL AND (g_shippable_flag_tbl(i) = 'Y' OR l_invoice_numbering_method = 'A')) OR
258 (l_non_d_invoice_source_id IS NULL AND l_invoice_numbering_method = 'D' AND g_shippable_flag_tbl(i) = 'N') THEN
259
260 l_order_type_rec := oe_order_cache.load_order_type(OE_Order_Cache.g_header_rec.order_type_id);
261 l_cust_trx_type_id2 := l_order_type_rec.cust_trx_type_id;
262 l_invoice_source_id2 := l_order_type_rec.invoice_source_id;
263 l_non_d_invoice_source_id2 := l_order_type_rec.non_delivery_invoice_source_id;
264 END IF;
265
266 IF l_debug_level > 0 THEN
267 oe_debug_pub.add('Order type details... ' );
268 oe_debug_pub.add('Customer Transaction type : ' || l_cust_trx_type_id2 );
269 oe_debug_pub.add('Invoice Source : ' || l_invoice_source_id2 );
270 oe_debug_pub.add('Non Delivery Invoice Source : ' || l_non_d_invoice_source_id2 );
271 END IF;
272
273 g_cust_trx_type_id_tbl(i) := NVL(l_cust_trx_type_id, NVL(l_cust_trx_type_id2, NVL(OE_SYS_PARAMETERS.VALUE('OE_INVOICE_TRANSACTION_TYPE_ID', g_org_id_tbl(i)), -99)));
274
275 IF g_shippable_flag_tbl(i) = 'Y' OR l_invoice_numbering_method ='A' THEN
276 g_batch_source_id_tbl(i) := NVL(l_invoice_source_id, NVL(l_invoice_source_id2, -99));
277
278 IF g_batch_source_id_tbl(i) = -99 AND OE_SYS_PARAMETERS.VALUE('OE_INVOICE_SOURCE', g_org_id_tbl(i)) IS NOT NULL THEN
282 g_batch_source_id_tbl(i) := NVL(l_non_d_invoice_source_id, NVL(l_non_d_invoice_source_id2, -99));
279 g_batch_source_id_tbl(i) := Get_batch_source_ID( OE_SYS_PARAMETERS.VALUE('OE_INVOICE_SOURCE', g_org_id_tbl(i)));
280 END IF;
281 ELSE
283
284 IF g_batch_source_id_tbl(i) = -99 AND OE_SYS_PARAMETERS.VALUE('OE_NON_DELIVERY_INVOICE_SOURCE', g_org_id_tbl(i)) IS NOT NULL THEN
285 g_batch_source_id_tbl(i) := Get_batch_source_ID( OE_SYS_PARAMETERS.VALUE('OE_NON_DELIVERY_INVOICE_SOURCE', g_org_id_tbl(i)));
286 END IF;
287 END IF;
288
289 IF l_debug_level > 0 THEN
290 oe_debug_pub.add('i:'||i);
291 oe_debug_pub.add('g_line_id_tbl(i):'||g_line_id_tbl(i));
292 oe_debug_pub.add('g_invoice_to_customer_tbl(i):'||g_invoice_to_customer_tbl(i));
293 oe_debug_pub.add('G_invoice_to_site_tbl(i):'||G_invoice_to_site_tbl(i));
294 oe_debug_pub.add('g_inventory_item_id_tbl(i):'||g_inventory_item_id_tbl(i));
295 oe_debug_pub.add('Customer Transaction type : ' || g_cust_trx_type_id_tbl(i) );
296 oe_debug_pub.add('Invoice Source : ' || g_batch_source_id_tbl(i) );
297 oe_debug_pub.add('org_id : ' || g_org_id_tbl(i) );
298 oe_debug_pub.add('accounting rule : ' || g_accounting_rule_id_tbl(i) );
299
300 END IF;
301
302 END IF;
303 END LOOP;
304
305 IF l_debug_level > 0 THEN
306 oe_debug_pub.add('Inserting records in AR_RDR_PARAMETERS_GT ' );
307 oe_debug_pub.add('g_Line_id_tbl.count: ' ||g_Line_id_tbl.count);
308 END IF;
309
310 --Populate global temporary table AR_RDR_PARAMETERS_GT
311 -- FORALL i in g_line_id_tbl.FIRST..g_line_id_tbl.LAST
312 FORALL i in 1..g_Line_id_tbl.count
313 INSERT INTO ar_rdr_parameters_gt
314 (source_line_id,
315 batch_source_id,
316 -- profile_class_id,
317 cust_account_id,
318 cust_acct_site_id,
319 cust_trx_type_id,
320 -- item_category_id,
321 inventory_item_id,
322 org_id,
323 accounting_rule_id,
324 -- memo_line_id
325 ship_to_cust_acct_id,
326 ship_to_site_use_id
327 )
328 SELECT g_line_id_tbl(i),
329 DECODE(g_batch_source_id_tbl(i),-99, NULL, g_batch_source_id_tbl(i)),
330 -- profile_class_id,
331 DECODE(g_invoice_to_customer_tbl(i), -99, NULL, g_invoice_to_customer_tbl(i)) ,
332 -- DECODE(G_invoice_to_site_tbl(i),-99, NULL, G_invoice_to_site_tbl(i)),
333 DECODE(g_invoice_to_org_id_tbl(i),-99,NULL,g_invoice_to_org_id_tbl(i)), -- for Bug#8262992
334 DECODE(g_cust_trx_type_id_tbl(i), -99, NULL, g_cust_trx_type_id_tbl(i)),
335 -- item_category_id,
336 DECODE(g_inventory_item_id_tbl(i), -99, NULL, g_inventory_item_id_tbl(i)),
337 DECODE(g_org_id_tbl(i), -99, NULL, g_org_id_tbl(i)),
338 DECODE(G_accounting_rule_id_tbl(i), -99, NULL, G_accounting_rule_id_tbl(i)),
339 -- memo_line_id
340 --For Bug#8262992
341 DECODE(g_ship_to_customer_tbl(i), -99, NULL, g_ship_to_customer_tbl(i)) ,
342 DECODE(g_ship_to_org_id_tbl(i),-99,NULL,g_ship_to_org_id_tbl(i))
343
344 FROM dual
345 WHERE g_line_id_tbl(i) <> -99;
346
347 IF l_debug_level > 0 THEN
348 oe_debug_pub.add('done inserting records' );
349 END IF;
350
351 l_inserted_lines := SQL%ROWCOUNT;
352 IF l_debug_level > 0 THEN
353 oe_debug_pub.add( 'INSERTED '||l_inserted_lines||' records' , 3 ) ;
354 END IF;
355
356 IF l_inserted_lines > 0 THEN
357 --Call AR API
358 IF l_debug_level > 0 THEN
359 oe_debug_pub.add('Entering AR_DEFERRAL_REASONS_GRP.default_reasons ' );
360 END IF;
361 ar_deferral_reasons_grp.default_reasons (
362 p_api_version => 1.0,
363 p_mode => 'OM',
364 x_return_status => l_return_status,
365 x_msg_count => l_msg_count,
366 x_msg_data => l_msg_data);
367
368 IF l_debug_level > 0 THEN
369 oe_debug_pub.add('Exiting AR_DEFERRAL_REASONS_GRP.default_reasons ' );
370 END IF;
371
372 --join fun_rule_bulk_result_gt with ar_deferral_reasons to get all the AR attributes
373 --pick one pre-billing or post-billing contingency
374
375 --1. Pre-billing deferral reason has precedence over post-billing one
376 --2. If there are two pre-billing deferral reasons(or two post-blling without prebilling), we pick the one that has less number of expiration days.
377 --3. If even the number of expiration days is the same, we pick the one that was created later.
378
379 l_line_id_old := 0;
380 l_line_id_new := 0;
381
382 IF l_debug_level > 0 THEN -- for debugging purpose
383 oe_debug_pub.add('Records returned by AR');
384 FOR default_contingencies_rec IN default_contingencies LOOP
385 oe_debug_pub.add('id:'||default_contingencies_rec.id);
386 oe_debug_pub.add('contingency_id:'||default_contingencies_rec.contingency_id);
387 oe_debug_pub.add('revrec_event_code:'||default_contingencies_rec.revrec_event_code);
388 oe_debug_pub.add('revrec_expiration_days:'||default_contingencies_rec.expiration_days);
389 END LOOP;
390 END IF;
391
392
393 FOR default_contingencies_rec IN default_contingencies LOOP
394 l_line_id_new := default_contingencies_rec.id;
395
396 IF l_line_id_new <> l_line_id_old THEN
397 IF l_debug_level > 0 THEN
398 oe_debug_pub.add('Updating records in OE_ORDER_LINES_ALL ' );
399 END IF;
400 UPDATE OE_ORDER_LINES_ALL
401 SET contingency_id = default_contingencies_rec.contingency_id,
402 revrec_event_code = default_contingencies_rec.revrec_event_code,
406 l_line_id_old := l_line_id_new;
403 revrec_expiration_days = default_contingencies_rec.expiration_days
404 WHERE line_id = default_contingencies_rec.id;
405
407 END IF;
408
409 END LOOP;
410 END IF;
411
412 IF l_debug_level > 0 THEN
413 oe_debug_pub.add('Deleting the changed lines table once the lines are processed ' );
414 END IF;
415
416 Delete_Changed_Lines_Tbl;
417
418 EXCEPTION
419 WHEN OTHERS THEN
420 OE_MSG_PUB.Add_Exc_Msg
421 ( G_PKG_NAME
422 , 'Default_Contingency_Attributes'
423 );
424 END Default_Contingency_Attributes;
425
426 PROCEDURE Default_Parent_Accept_Details
427 (
428 p_line_rec IN OUT NOCOPY OE_Order_PUB.Line_Rec_Type
429 )
430 IS
431 l_order_line_id NUMBER;
432 l_return_status VARCHAR2(1);
433 l_service_reference_line_id NUMBER;
434 --
435 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
436 --
437 l_accepted_quantity NUMBER;
438 l_revrec_signature VARCHAR2(240);
439 l_revrec_signature_date DATE;
440 l_revrec_reference_document VARCHAR2(240);
441 l_revrec_comments VARCHAR2(2000);
442 l_revrec_implicit_flag VARCHAR2(1);
443 l_accepted_by NUMBER;
444 l_top_model_line_id NUMBER;
445 l_item_type_code VARCHAR2(30);
446
447 BEGIN
448 IF l_debug_level > 0 THEN
449 oe_debug_pub.add( 'ENTERING OE_ACCEPTANCE_UTIL.Default_Parent_Accept_Details' ) ;
450 END IF;
451 IF p_line_rec.item_type_code = 'SERVICE' THEN
452 IF p_line_rec.service_reference_type_code = 'CUSTOMER_PRODUCT' AND
453 p_line_rec.service_reference_line_id IS NOT NULL THEN
454 IF l_debug_level > 0 THEN
455 oe_debug_pub.add( 'LINE IS A CUSTOMER PRODUCT' ) ;
456 END IF;
457 OE_SERVICE_UTIL.Get_Cust_Product_Line_Id
458 ( x_return_status => l_return_status
459 , p_reference_line_id => p_line_rec.service_reference_line_id
460 , p_customer_id => p_line_rec.sold_to_org_id
461 , x_cust_product_line_id => l_order_line_id
462 );
463 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
464 IF l_debug_level > 0 THEN
465 oe_debug_pub.add( 'SERVICE LINE ID IS ' || L_ORDER_LINE_ID ) ;
466 END IF;
467 l_service_reference_line_id := l_order_line_id;
468 ELSE
469 IF l_debug_level > 0 THEN
470 oe_debug_pub.add( 'NOT ABLE TO RETRIEVE CUST PRODUCT LINE ID' ) ;
471 END IF;
472 FND_MESSAGE.SET_NAME('ONT','ONT_NO_CUST_PROD_LINE');
473 OE_MSG_PUB.ADD;
474 --RAISE NO_DATA_FOUND;
475 END IF;
476 ELSE -- not a customer product
477 l_service_reference_line_id := p_line_rec.service_reference_line_id;
478 END IF;
479 END IF;
480
481 IF l_service_reference_line_id IS NOT NULL THEN
482
483 SELECT accepted_quantity
484 ,Revrec_signature
485 ,Revrec_signature_date
486 ,Revrec_reference_document
487 ,Revrec_comments
488 ,Revrec_implicit_flag
489 ,accepted_by
490 ,item_type_code
491 ,top_model_line_id
492 INTO l_accepted_quantity
493 , l_revrec_signature
494 , l_revrec_signature_date
495 , l_revrec_reference_document
496 , l_revrec_comments
497 , l_revrec_implicit_flag
498 , l_accepted_by
499 , l_item_type_code
500 , l_top_model_line_id
501 FROM oe_order_lines_all
502 WHERE line_id = l_service_reference_line_id;
503
504 IF l_item_type_code IN ('MODEL', 'STANDARD') or (l_item_type_code='KIT' AND
505 l_top_model_line_id = l_service_reference_line_id) AND
506 l_accepted_quantity is not null THEN -- parent is a top model and is accepted already
507 if nvl(l_accepted_quantity,0) = 0 then --if parent is rejected or not accepted
508 p_line_rec.accepted_quantity := l_accepted_quantity;
509 else
510 p_line_rec.accepted_quantity := nvl(p_line_rec.fulfilled_quantity,nvl(p_line_rec.shipped_quantity,nvl(p_line_rec.ordered_quantity,0)));
511 end if;
512 p_line_rec.Revrec_signature:=l_revrec_signature;
513 p_line_rec.Revrec_signature_date:=l_revrec_signature_date;
514 p_line_rec.revrec_reference_document:= l_revrec_reference_document;
515 p_line_rec.revrec_comments:= l_revrec_comments;
516 p_line_rec.revrec_implicit_flag:=l_revrec_implicit_flag;
517 p_line_rec.accepted_by:= l_accepted_by;
518 ELSIF l_top_model_line_id IS NOT NULL AND l_accepted_quantity is not null THEN -- parent is a child line and is accepted
519
520 SELECT Accepted_quantity
521 ,Revrec_signature
522 ,Revrec_signature_date
523 ,Revrec_reference_document
524 ,Revrec_comments
525 ,Revrec_implicit_flag
526 ,accepted_by
527 ,item_type_code
528 ,top_model_line_id
529 INTO
530 l_accepted_quantity
531 , l_revrec_signature
532 , l_revrec_signature_date
533 , l_revrec_reference_document
534 , l_revrec_comments
535 , l_revrec_implicit_flag
539 FROM oe_order_lines_all
536 ,l_accepted_by
537 , l_item_type_code
538 , l_top_model_line_id
540 WHERE line_id= l_top_model_line_id;
541
542 if nvl(l_accepted_quantity,0) = 0 then --if parent is rejected or not accepted
543 p_line_rec.accepted_quantity := l_accepted_quantity;
544 else
545 p_line_rec.accepted_quantity := nvl(p_line_rec.fulfilled_quantity,nvl(p_line_rec.shipped_quantity,nvl(p_line_rec.ordered_quantity,0)));
546 end if;
547 p_line_rec.Revrec_signature:=l_revrec_signature;
548 p_line_rec.Revrec_signature_date:=l_revrec_signature_date;
549 p_line_rec.revrec_reference_document:= l_revrec_reference_document;
550 p_line_rec.revrec_comments:= l_revrec_comments;
551 p_line_rec.revrec_implicit_flag:=l_revrec_implicit_flag;
552 p_line_rec.accepted_by:= l_accepted_by;
553
554
555
556 END IF;
557 END IF; -- service reference is not null
558
559 EXCEPTION
560 WHEN NO_DATA_FOUND THEN
561 NULL;
562 END Default_Parent_Accept_Details;
563
564 PROCEDURE Get_Contingency_Attributes
565 (p_line_rec IN OE_ORDER_PUB.Line_Rec_Type
566 ,x_contingency_id OUT NOCOPY NUMBER
567 ,x_revrec_event_code OUT NOCOPY VARCHAR2
568 ,x_revrec_expiration_days OUT NOCOPY NUMBER)
569 IS
570 l_service_reference_line_id NUMBER;
571 l_return_status VARCHAR2(1);
572 l_item_type_code VARCHAR2(30);
573 l_order_line_id NUMBER;
574 l_top_model_line_id NUMBER;
575 --
576 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
577 --
578 BEGIN
579 x_contingency_id := NULL;
580 x_revrec_event_code := NULL;
581 x_revrec_expiration_days := NULL;
582
583 IF p_line_rec.item_type_code = 'RETURN' THEN
584
585 IF l_debug_level > 0 THEN
586 oe_debug_pub.add( 'Return Line, Return ' );
587 END IF;
588 RETURN;
589
590 ELSIF p_line_rec.source_document_type_id = 10 THEN
591
592 IF l_debug_level > 0 THEN
593 oe_debug_pub.add( 'Internal Order Line, Return');
594 END IF;
595 RETURN;
596
597 ELSIF p_line_rec.order_source_id=27 AND p_line_rec.retrobill_request_id IS NOT NULL THEN
598
599 IF l_debug_level > 0 THEN
600 oe_debug_pub.add( 'Retrobill Line, Return ' );
601 END IF;
602 RETURN;
603
604 ELSIF p_line_rec.item_type_code IN ('CONFIG','CLASS','OPTION','INCLUDED')
605 OR (p_line_rec.item_type_code = 'KIT' and p_line_rec.top_model_line_id <> p_line_rec.line_id) THEN
606
607 IF l_debug_level > 0 THEN
608 oe_debug_pub.add('Item_type_code:'||p_line_rec.item_type_code||' Get from parent:'||p_line_rec.top_model_line_id );
609 END IF;
610 IF p_line_rec.top_model_line_id IS NOT NULL THEN
611 SELECT contingency_id, revrec_event_code, revrec_expiration_days
612 INTO x_contingency_id, x_revrec_event_code,x_revrec_expiration_days
613 FROM oe_order_lines_all
614 WHERE line_id = p_line_rec.top_model_line_id;
615 END IF;
616 ELSIF p_line_rec.item_type_code = 'SERVICE' THEN
617 IF l_debug_level > 0 THEN
618 oe_debug_pub.add( 'Item_type_code:'||p_line_rec.item_type_code||' - Get from service parent' );
619 END IF;
620 IF p_line_rec.service_reference_type_code = 'CUSTOMER_PRODUCT' AND
621 p_line_rec.service_reference_line_id IS NOT NULL THEN
622 IF l_debug_level > 0 THEN
623 oe_debug_pub.add( 'LINE IS A CUSTOMER PRODUCT' ) ;
624 END IF;
625 OE_SERVICE_UTIL.Get_Cust_Product_Line_Id
626 ( x_return_status => l_return_status
627 , p_reference_line_id => p_line_rec.service_reference_line_id
628 , p_customer_id => p_line_rec.sold_to_org_id
629 , x_cust_product_line_id => l_order_line_id
630 );
631 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
632 IF l_debug_level > 0 THEN
633 oe_debug_pub.add( 'SERVICE LINE ID IS ' || L_ORDER_LINE_ID ) ;
634 END IF;
635 l_service_reference_line_id := l_order_line_id;
636 ELSE
637 IF l_debug_level > 0 THEN
638 oe_debug_pub.add( 'NOT ABLE TO RETRIEVE CUST PRODUCT LINE ID' ) ;
639 END IF;
640 FND_MESSAGE.SET_NAME('ONT','ONT_NO_CUST_PROD_LINE');
641 OE_MSG_PUB.ADD;
642 RAISE NO_DATA_FOUND;
643 END IF;
644 ELSE
645 l_service_reference_line_id := p_line_rec.service_reference_line_id;
646 END IF;
647
648 IF l_service_reference_line_id IS NOT NULL THEN
649 SELECT contingency_id,revrec_event_code,revrec_expiration_days,item_type_code,top_model_line_id
650 INTO x_contingency_id,x_revrec_event_code,x_revrec_expiration_days,l_item_type_code,l_top_model_line_id
651 FROM oe_order_lines_all
652 WHERE line_id= l_service_reference_line_id;
653
654 IF l_item_type_code IN ('MODEL','STANDARD') OR
655 (l_item_type_code = 'KIT' AND l_top_model_line_id=l_service_reference_line_id) THEN
656 --service attached to a parent already assigned
657 NULL;
658 ELSIF l_top_model_line_id IS NOT NULL THEN -- service attached to a child line
659 SELECT contingency_id, revrec_event_code, revrec_expiration_days
660 INTO x_contingency_id, x_revrec_event_code, x_revrec_expiration_days
661 FROM oe_order_lines_all
662 WHERE line_id=l_top_model_line_id;
663 END IF;
667 oe_debug_pub.add( 'Item_type_code:'||p_line_rec.item_type_code||' top_model_line_id:'||p_line_rec.top_model_line_id );
664 END IF;
665 ELSE -- standard line or top model
666 IF l_debug_level > 0 THEN
668 END IF;
669 x_contingency_id := p_line_rec.contingency_id;
670 x_revrec_event_code := p_line_rec.revrec_event_code;
671 x_revrec_expiration_days:= p_line_rec.revrec_expiration_days;
672 END IF;
673 EXCEPTION
674 WHEN OTHERS THEN
675 x_contingency_id := NULL;
676 x_revrec_event_code := NULL;
677 x_revrec_expiration_days:= NULL;
678
679 OE_MSG_PUB.Add_Exc_Msg
680 ( G_PKG_NAME
681 , 'Get_Contingency_Attributes'
682 );
683
684 END Get_Contingency_Attributes;
685
686
687 FUNCTION Pre_billing_acceptance_on(p_line_rec IN OE_Order_PUB.Line_Rec_Type ) RETURN BOOLEAN
688 IS
689 l_service_reference_line_id NUMBER;
690 l_return_status VARCHAR2(1);
691 l_item_type_code VARCHAR2(30);
692 l_order_line_id NUMBER;
693 l_top_model_line_id NUMBER;
694 l_contingency_id NUMBER;
695 l_revrec_event_code VARCHAR2(30);
696 --
697 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
698 --
699 BEGIN
700
701 IF p_line_rec.line_category_code = 'RETURN' THEN
702
703 IF l_debug_level > 0 THEN
704 oe_debug_pub.add( 'Return Line, Return FALSE' );
705 END IF;
706 RETURN FALSE;
707
708 ELSIF p_line_rec.source_document_type_id = 10 THEN
709
710 IF l_debug_level > 0 THEN
711 oe_debug_pub.add( 'Internal Order Line, Return FALSE' );
712 END IF;
713 RETURN FALSE;
714
715 ELSIF p_line_rec.order_source_id = 27 AND p_line_rec.retrobill_request_id IS NOT NULL THEN
716
717 IF l_debug_level > 0 THEN
718 oe_debug_pub.add( 'Retrobill Line, Return FALSE' );
719 END IF;
720 RETURN FALSE;
721
722 ELSIF p_line_rec.item_type_code IN ('CONFIG', 'CLASS', 'OPTION', 'INCLUDED')
723 OR (p_line_rec.item_type_code = 'KIT' and p_line_rec.top_model_line_id <> p_line_rec.line_id) THEN
724
725 IF l_debug_level > 0 THEN
726 oe_debug_pub.add( 'Item_type_code:'||p_line_rec.item_type_code||' - Get from parent:'||p_line_rec.top_model_line_id );
727 END IF;
728 IF p_line_rec.top_model_line_id IS NOT NULL THEN
729 SELECT contingency_id, revrec_event_code
730 INTO l_contingency_id, l_revrec_event_code
731 FROM oe_order_lines_all
732 WHERE line_id = p_line_rec.top_model_line_id;
733 END IF;
734
735 IF l_contingency_id IS NOT NULL AND l_revrec_event_code='INVOICING' THEN
736 RETURN TRUE;
737 ELSE
738 RETURN FALSE;
739 END IF;
740 ELSIF p_line_rec.item_type_code = 'SERVICE' THEN
741 IF l_debug_level > 0 THEN
742 oe_debug_pub.add( 'Item_type_code:'||p_line_rec.item_type_code||' - Get from service parent' );
743 END IF;
744 IF p_line_rec.service_reference_type_code='CUSTOMER_PRODUCT' AND
745 p_line_rec.service_reference_line_id IS NOT NULL THEN
746 IF l_debug_level > 0 THEN
747 oe_debug_pub.add( 'LINE IS A CUSTOMER PRODUCT' ) ;
748 END IF;
749 OE_SERVICE_UTIL.Get_Cust_Product_Line_Id
750 ( x_return_status => l_return_status
751 , p_reference_line_id => p_line_rec.service_reference_line_id
752 , p_customer_id => p_line_rec.sold_to_org_id
753 , x_cust_product_line_id => l_order_line_id
754 );
755 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
756 IF l_debug_level > 0 THEN
757 oe_debug_pub.add( 'SERVICE LINE ID IS ' || L_ORDER_LINE_ID ) ;
758 END IF;
759 l_service_reference_line_id := l_order_line_id;
760 ELSE
761 IF l_debug_level > 0 THEN
762 oe_debug_pub.add( 'NOT ABLE TO RETRIEVE CUST PRODUCT LINE ID' ) ;
763 END IF;
764 FND_MESSAGE.SET_NAME('ONT','ONT_NO_CUST_PROD_LINE');
765 OE_MSG_PUB.ADD;
766 --RAISE NO_DATA_FOUND;
767 END IF;
768 ELSE
769 l_service_reference_line_id := p_line_rec.service_reference_line_id;
770 END IF;
771
772 IF l_service_reference_line_id IS NOT NULL THEN
773 SELECT contingency_id, revrec_event_code,item_type_code, top_model_line_id
774 INTO l_contingency_id, l_revrec_event_code,l_item_type_code, l_top_model_line_id
775 FROM oe_order_lines_all
776 WHERE line_id= l_service_reference_line_id;
777
778 IF l_item_type_code IN ('MODEL', 'STANDARD') OR
779 (l_item_type_code = 'KIT' AND l_top_model_line_id=l_service_reference_line_id) THEN --service attached to a parent
780 IF l_contingency_id IS NOT NULL AND l_revrec_event_code='INVOICING' THEN
781 RETURN TRUE;
782 ELSE
783 RETURN FALSE;
784 END IF;
785 ELSIF l_top_model_line_id IS NOT NULL THEN -- service attached to a child line
786 SELECT contingency_id, revrec_event_code
787 INTO l_contingency_id, l_revrec_event_code
788 FROM oe_order_lines_all
789 WHERE line_id=l_top_model_line_id;
790 END IF;
791 IF l_contingency_id IS NOT NULL AND l_revrec_event_code='INVOICING' THEN
792 RETURN TRUE;
793 ELSE
794 RETURN FALSE;
795 END IF;
796 ELSE -- if service_reference_line_id is null
800 IF l_debug_level > 0 THEN
797 RETURN FALSE;
798 END IF;
799 ELSE -- standard line or top model
801 oe_debug_pub.add( 'Item_type_code:'||p_line_rec.item_type_code||' top_model_line_id:'||p_line_rec.top_model_line_id );
802 END IF;
803 IF p_line_rec.contingency_id IS NOT NULL AND p_line_rec.revrec_event_code='INVOICING' THEN
804 RETURN TRUE;
805 ELSE
806 RETURN FALSE;
807 END IF;
808 END IF;
809
810 RETURN FALSE;
811
812 EXCEPTION
813 WHEN OTHERS THEN
814 OE_MSG_PUB.Add_Exc_Msg
815 ( G_PKG_NAME
816 , ' Pre_billing_acceptance_on'
817 );
818 RETURN FALSE;
819
820 END Pre_billing_acceptance_on;
821
822 --Overloaded to accept line_id as parameter
823 FUNCTION Pre_billing_acceptance_on (p_line_id IN NUMBER) RETURN BOOLEAN
824 IS
825 l_line_rec OE_ORDER_PUB.line_rec_type;
826 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
827 BEGIN
828
829 OE_Line_Util.Query_Row(p_line_id => p_line_id,x_line_rec => l_line_rec);
830 RETURN Pre_billing_acceptance_on (p_line_rec => l_line_rec);
831
832 EXCEPTION
833 WHEN OTHERS THEN
834 OE_MSG_PUB.Add_Exc_Msg
835 ( G_PKG_NAME
836 , ' Pre_billing_acceptance_on'
837 );
838 RETURN FALSE;
839 END Pre_billing_acceptance_on;
840
841 FUNCTION Post_billing_acceptance_on (p_line_rec IN OE_Order_PUB.Line_Rec_Type) RETURN BOOLEAN IS
842 l_service_reference_line_id NUMBER;
843 l_return_status VARCHAR2(1);
844 l_item_type_code VARCHAR2(30);
845 l_order_line_id NUMBER;
846 l_top_model_line_id NUMBER;
847 l_contingency_id NUMBER;
848 l_revrec_event_code VARCHAR2(30);
849 --
850 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
851 --
852 BEGIN
853
854 IF p_line_rec.line_category_code = 'RETURN' THEN
855
856 IF l_debug_level > 0 THEN
857 oe_debug_pub.add( 'Return Line, Return FALSE' );
858 END IF;
859 RETURN FALSE;
860
861 ELSIF p_line_rec.source_document_type_id = 10 THEN
862
863 IF l_debug_level > 0 THEN
864 oe_debug_pub.add( 'Internal Order Line, Return FALSE' );
865 END IF;
866 RETURN FALSE;
867
868 ELSIF p_line_rec.order_source_id = 27 AND p_line_rec.retrobill_request_id IS NOT NULL THEN
869
870 IF l_debug_level > 0 THEN
871 oe_debug_pub.add( 'Retrobill Line, Return FALSE' );
872 END IF;
873 RETURN FALSE;
874
875 ELSIF p_line_rec.item_type_code IN ('CONFIG', 'CLASS', 'OPTION', 'INCLUDED')
876 OR (p_line_rec.item_type_code = 'KIT' and p_line_rec.top_model_line_id <> p_line_rec.line_id) THEN
877
878 IF l_debug_level > 0 THEN
879 oe_debug_pub.add( 'Item_type_code:'||p_line_rec.item_type_code||' - Get from parent:'||p_line_rec.top_model_line_id );
880 END IF;
881 IF p_line_rec.top_model_line_id IS NOT NULL THEN
882 SELECT contingency_id, revrec_event_code
883 INTO l_contingency_id, l_revrec_event_code
884 FROM oe_order_lines_all
885 WHERE line_id = p_line_rec.top_model_line_id;
886 END IF;
887
888 IF l_contingency_id IS NOT NULL AND l_revrec_event_code='CUSTOMER_ACCEPTANCE' THEN
889 RETURN TRUE;
890 ELSE
891 RETURN FALSE;
892 END IF;
893 ELSIF p_line_rec.item_type_code = 'SERVICE' THEN
894 IF l_debug_level > 0 THEN
895 oe_debug_pub.add( 'Item_type_code:'||p_line_rec.item_type_code||' - Get from service parent' );
896 END IF;
897 IF p_line_rec.service_reference_type_code='CUSTOMER_PRODUCT' AND
898 p_line_rec.service_reference_line_id IS NOT NULL THEN
899 IF l_debug_level > 0 THEN
900 oe_debug_pub.add( 'LINE IS A CUSTOMER PRODUCT' ) ;
901 END IF;
902 OE_SERVICE_UTIL.Get_Cust_Product_Line_Id
903 ( x_return_status => l_return_status
904 , p_reference_line_id => p_line_rec.service_reference_line_id
905 , p_customer_id => p_line_rec.sold_to_org_id
906 , x_cust_product_line_id => l_order_line_id
907 );
908 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
909 IF l_debug_level > 0 THEN
910 oe_debug_pub.add( 'SERVICE LINE ID IS ' || L_ORDER_LINE_ID ) ;
911 END IF;
912 l_service_reference_line_id := l_order_line_id;
913 ELSE
914 IF l_debug_level > 0 THEN
915 oe_debug_pub.add( 'NOT ABLE TO RETRIEVE CUST PRODUCT LINE ID' ) ;
916 END IF;
917 FND_MESSAGE.SET_NAME('ONT','ONT_NO_CUST_PROD_LINE');
918 OE_MSG_PUB.ADD;
919 --RAISE NO_DATA_FOUND;
920 END IF;
921 ELSE
922 l_service_reference_line_id := p_line_rec.service_reference_line_id;
923 END IF;
924
925 IF l_service_reference_line_id IS NOT NULL THEN
926 SELECT contingency_id, revrec_event_code,item_type_code, top_model_line_id
927 INTO l_contingency_id, l_revrec_event_code,l_item_type_code, l_top_model_line_id
928 FROM oe_order_lines_all
929 WHERE line_id= l_service_reference_line_id;
930
931 IF l_item_type_code IN ('MODEL', 'STANDARD') OR
935 ELSE
932 (l_item_type_code = 'KIT' AND l_top_model_line_id=l_service_reference_line_id) THEN --service attached to a parent
933 IF l_contingency_id IS NOT NULL AND l_revrec_event_code='CUSTOMER_ACCEPTANCE' THEN
934 RETURN TRUE;
936 RETURN FALSE;
937 END IF;
938 ELSIF l_top_model_line_id IS NOT NULL THEN -- service attached to a child line
939 SELECT contingency_id, revrec_event_code
940 INTO l_contingency_id, l_revrec_event_code
941 FROM oe_order_lines_all
942 WHERE line_id=l_top_model_line_id;
943 END IF;
944 IF l_contingency_id IS NOT NULL AND l_revrec_event_code='CUSTOMER_ACCEPTANCE' THEN
945 RETURN TRUE;
946 ELSE
947 RETURN FALSE;
948 END IF;
949 ELSE -- service_refernce_line_id null
950 RETURN FALSE;
951 END IF;
952 ELSE -- standard line or top model
953 IF l_debug_level > 0 THEN
954 oe_debug_pub.add( 'Item_type_code:'||p_line_rec.item_type_code||' top_model_line_id:'||p_line_rec.top_model_line_id );
955 END IF;
956 IF p_line_rec.contingency_id IS NOT NULL AND p_line_rec.revrec_event_code='CUSTOMER_ACCEPTANCE' THEN
957 RETURN TRUE;
958 ELSE
959 RETURN FALSE;
960 END IF;
961 END IF;
962
963 RETURN FALSE;
964
965 EXCEPTION
966 WHEN OTHERS THEN
967 OE_MSG_PUB.Add_Exc_Msg
968 ( G_PKG_NAME
969 , ' Post_billing_acceptance_on'
970 );
971 RETURN FALSE;
972 END Post_billing_acceptance_on;
973
974 --Overloaded to accept line_id as parameter
975 FUNCTION Post_billing_acceptance_on (p_line_id IN NUMBER) RETURN BOOLEAN
976 IS
977 l_line_rec OE_ORDER_PUB.line_rec_type;
978 --
979 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
980 --
981 BEGIN
982 OE_Line_Util.Query_Row(p_line_id => p_line_id,
983 x_line_rec => l_line_rec);
984
985 RETURN Post_billing_acceptance_on (p_line_rec => l_line_rec);
986 EXCEPTION
987 WHEN OTHERS THEN
988 OE_MSG_PUB.Add_Exc_Msg
989 ( G_PKG_NAME
990 , ' Post_billing_acceptance_on'
991 );
992 RETURN FALSE;
993 END Post_billing_acceptance_on;
994
995 FUNCTION Customer_acceptance_Eligible (p_line_rec IN OE_Order_PUB.Line_Rec_Type) RETURN BOOLEAN IS
996 l_count NUMBER := 0;
997 --
998 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
999 --
1000 BEGIN
1001 IF p_line_rec.line_category_code = 'RETURN' THEN
1002
1003 IF l_debug_level > 0 THEN
1004 oe_debug_pub.add( 'Return Line, Return FALSE' );
1005 END IF;
1006 RETURN FALSE;
1007
1008 ELSIF p_line_rec.source_document_type_id = 10 THEN
1009
1010 IF l_debug_level > 0 THEN
1011 oe_debug_pub.add( 'Internal Order Line, Return FALSE' );
1012 END IF;
1013 RETURN FALSE;
1014
1015 ELSIF p_line_rec.order_source_id = 27 AND p_line_rec.retrobill_request_id IS NOT NULL THEN
1016
1017 IF l_debug_level > 0 THEN
1018 oe_debug_pub.add( 'Retrobill Line, Return FALSE' );
1019 END IF;
1020 RETURN FALSE;
1021 ELSIF p_line_rec.item_type_code IN ('CONFIG', 'CLASS', 'OPTION', 'INCLUDED')
1022 OR (p_line_rec.item_type_code = 'KIT' and p_line_rec.top_model_line_id <> p_line_rec.line_id) THEN --child line
1023
1024 IF l_debug_level > 0 THEN
1025 oe_debug_pub.add( 'Item_type_code:'||p_line_rec.item_type_code);
1026 END IF;
1027 RETURN FALSE;
1028 ELSIF p_line_rec.item_type_code = 'SERVICE' THEN
1029 IF l_debug_level > 0 THEN
1030 oe_debug_pub.add( 'Item_type_code:'||p_line_rec.item_type_code||' - Get from service parent' );
1031 END IF;
1032 IF p_line_rec.service_reference_type_code='ORDER' THEN
1033 IF l_debug_level > 0 THEN
1034 oe_debug_pub.add( 'LINE is a service with reference type ORDER' ) ;
1035 END IF;
1036 RETURN FALSE;
1037 -- Acceptance of customer product services should not be allowed through UI.
1038 -- But explicit acceptance should be allowed
1039 ELSIF p_line_rec.service_reference_type_code='CUSTOMER_PRODUCT' THEN
1040 IF p_line_rec.flow_status_code in ('PRE-BILLING_ACCEPTANCE', 'POST-BILLING_ACCEPTANCE') THEN
1041 RETURN TRUE;
1042 ELSE
1043 RETURN FALSE;
1044 END IF;
1045 END IF;
1046 ELSE -- Now it could be a model, kit or standard line
1047 IF l_debug_level > 0 THEN
1048 oe_debug_pub.add( 'Item_type_code:'||p_line_rec.item_type_code||' top_model_line_id'|| p_line_rec.top_model_line_id);
1049 END IF;
1050 IF p_line_rec.top_model_line_id IS NOT NULL AND p_line_rec.top_model_line_id = p_line_rec.line_id THEN
1051 IF p_line_rec.flow_status_code NOT IN ('PRE-BILLING_ACCEPTANCE', 'POST-BILLING_ACCEPTANCE') THEN
1052 RETURN FALSE;
1053 ELSE
1054 --top model line use exists
1055 SELECT count(*)
1056 INTO l_count
1057 FROM oe_order_lines_all
1058 WHERE header_id = p_line_rec.header_id
1059 AND top_model_line_id = p_line_rec.line_id
1060 AND flow_status_code NOT IN ('PRE-BILLING_ACCEPTANCE', 'POST-BILLING_ACCEPTANCE')
1061 AND nvl(open_flag, 'Y') = 'Y';
1062
1063 IF l_count = 0 THEN
1064 RETURN TRUE;
1065 ELSE
1066 RETURN FALSE;
1067 END IF;
1068 END IF;
1069 ELSE -- standard line
1070 IF p_line_rec.flow_status_code in ('PRE-BILLING_ACCEPTANCE', 'POST-BILLING_ACCEPTANCE') THEN
1071 RETURN TRUE;
1072 ELSE
1073 RETURN FALSE;
1074 END IF;
1075 END IF;
1076 END IF;
1077
1078 RETURN FALSE;
1079
1080 EXCEPTION
1081 WHEN OTHERS THEN
1082 OE_MSG_PUB.Add_Exc_Msg
1083 ( G_PKG_NAME
1084 , 'Customer_acceptance_Eligible'
1085 );
1086 RETURN FALSE;
1087 END Customer_acceptance_Eligible;
1088
1089 --Overloaded to accept line_id as parameter
1090 FUNCTION Customer_Acceptance_Eligible (p_line_id IN NUMBER) RETURN BOOLEAN IS
1091 l_line_rec OE_ORDER_PUB.line_rec_type;
1092 --
1093 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1094 --
1095 BEGIN
1096 OE_Line_Util.Query_Row(p_line_id => p_line_id,
1097 x_line_rec => l_line_rec);
1098
1099 RETURN Customer_Acceptance_Eligible(p_line_rec => l_line_rec);
1100
1101 EXCEPTION
1102 WHEN OTHERS THEN
1103 OE_MSG_PUB.Add_Exc_Msg
1104 ( G_PKG_NAME
1105 , 'Customer_acceptance_Eligible'
1106 );
1107 RETURN FALSE;
1108 END Customer_Acceptance_Eligible;
1109
1110 FUNCTION Acceptance_Status(p_line_rec IN OE_Order_PUB.Line_Rec_Type) RETURN VARCHAR2 IS
1111 --
1112 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1113 --
1114 BEGIN
1115
1116 IF p_line_rec.accepted_quantity IS NULL OR p_line_rec.accepted_quantity = FND_API.G_MISS_NUM THEN
1117 -- consider closed lines as accepted because parent line might have been closed
1118 -- without acceptance (ex: from progress order when system param turned off)
1119 IF nvl(p_line_rec.open_flag, 'Y') = 'N' THEN
1120 IF l_debug_level > 0 THEN
1121 oe_debug_pub.add('returning as accepted because line '||p_line_rec.line_id||' is already closed');
1122 END IF;
1123 RETURN 'ACCEPTED';
1124 ELSE
1125 RETURN 'NOT_ACCEPTED';
1126 END IF;
1127 ELSIF p_line_rec.accepted_quantity = 0 THEN
1128 RETURN 'REJECTED';
1129 ELSE
1130 RETURN 'ACCEPTED';
1131 END IF;
1132
1133 END Acceptance_Status;
1134
1135 --Overloaded to accept line_id as parameter
1136
1137 FUNCTION Acceptance_Status(p_line_id IN NUMBER) RETURN VARCHAR2 IS
1138 l_accepted_quantity NUMBER;
1139 l_open_flag VARCHAR2(1);
1140 --
1141 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1142 --
1143 BEGIN
1144 SELECT accepted_quantity, open_flag
1145 INTO l_accepted_quantity, l_open_flag
1146 FROM oe_order_lines_all
1147 WHERE line_id = p_line_id;
1148
1149 IF l_accepted_quantity is NULL OR l_accepted_quantity = FND_API.G_MISS_NUM THEN
1150 -- consider closed lines as accepted because parent line might have been closed
1151 -- without acceptance (ex: from progress order when system param turned off)
1152 IF nvl(l_open_flag, 'Y') = 'N' THEN
1153 IF l_debug_level > 0 THEN
1154 oe_debug_pub.add('returning as accepted because line '||p_line_id||' is already closed');
1155 END IF;
1156 RETURN 'ACCEPTED';
1157 ELSE
1158 RETURN 'NOT_ACCEPTED';
1159 END IF;
1160 ELSIF l_accepted_quantity = 0 THEN
1161 RETURN 'REJECTED';
1162 ELSE
1163 RETURN 'ACCEPTED';
1164 END IF;
1165 EXCEPTION
1166 WHEN OTHERS THEN
1167 OE_MSG_PUB.Add_Exc_Msg
1168 ( G_PKG_NAME
1169 , 'Acceptance_Status'
1170 );
1171 RETURN 'NOT_ACCEPTED';
1172
1173 END Acceptance_Status;
1174
1175 END OE_ACCEPTANCE_UTIL;
[Home] [Help]