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