[Home] [Help]
PACKAGE BODY: APPS.AR_DEFERRAL_REASONS_GRP
Source
1 PACKAGE BODY ar_deferral_reasons_grp AS
2 /* $Header: ARXRDRB.pls 120.12 2011/11/23 22:26:22 mraymond ship $ */
3
4
5 /*=======================================================================+
6 | Global Constants
7 +=======================================================================*/
8
9 g_pkg_name CONSTANT VARCHAR2(30):= 'AR_DEFERREAL_REASONS_GRP';
10 g_om_context ra_interface_lines.interface_line_context%type :=
11 NVL(fnd_profile.value('ONT_SOURCE_CODE'),'###NOT_SET###');
12
13 pg_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'), 'N');
14
15 PROCEDURE default_reasons (
16 p_api_version IN NUMBER,
17 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
18 p_commit IN VARCHAR2 := fnd_api.g_false,
19 p_mode IN VARCHAR2 DEFAULT 'ALL',
20 x_return_status OUT NOCOPY VARCHAR2,
21 x_msg_count OUT NOCOPY NUMBER,
22 x_msg_data OUT NOCOPY VARCHAR2) IS
23
24 l_api_version CONSTANT NUMBER := 1.0;
25 l_api_name CONSTANT VARCHAR2(30) := 'default_reasons';
26
27 BEGIN
28
29 -- Standard Start of API savepoint
30 SAVEPOINT default_reasons_grp;
31
32 -- Standard call to check for call compatibility.
33 IF NOT FND_API.Compatible_API_Call (
34 p_current_version_number => l_api_version,
35 p_caller_version_number => p_api_version,
36 p_api_name => l_api_name,
37 p_pkg_name => g_pkg_name) THEN
38
39 RAISE fnd_api.g_exc_unexpected_error;
40
41 END IF;
42
43 -- Initialize message list if p_init_msg_list is set to TRUE.
44 IF FND_API.to_Boolean( p_init_msg_list ) THEN
45 fnd_msg_pub.initialize;
46 END IF;
47
48 /* 9968597 - Calling Hook Procedure to populate the attribute columns */
49 AR_CUSTOM_PARAMS_HOOK_PKG.populateContingencyAttributes(p_source => p_mode);
50
51 -- Initialize API return status to success
52 x_return_status := FND_API.G_RET_STS_SUCCESS;
53
54 fun_rule_pub.apply_rule_bulk (
55 p_application_short_name => 'AR',
56 p_rule_object_name => ar_revenue_management_pvt.c_rule_object_name,
57 p_param_view_name => 'AR_RDR_PARAMETERS_GT',
58 p_additional_where_clause => '1=1',
59 p_primary_key_column_name => 'SOURCE_LINE_ID'
60 );
61
62 EXCEPTION
63 WHEN fnd_api.g_exc_error THEN
64 ROLLBACK TO default_reasons_grp;
65 x_return_status := FND_API.G_RET_STS_ERROR ;
66 fnd_msg_pub.count_and_get (
67 p_encoded => fnd_api.g_false,
68 p_count => x_msg_count,
69 p_data => x_msg_data);
70
71 WHEN fnd_api.g_exc_unexpected_error THEN
72 ROLLBACK TO default_reasons_grp;
73 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
74 fnd_msg_pub.count_and_get (
75 p_encoded => fnd_api.g_false,
76 p_count => x_msg_count,
77 p_data => x_msg_data);
78
79 WHEN OTHERS THEN
80 ROLLBACK TO default_reasons_grp;
81 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
82 fnd_msg_pub.count_and_get (
83 p_encoded => fnd_api.g_false,
84 p_count => x_msg_count,
85 p_data => x_msg_data);
86
87 END default_reasons;
88
89 FUNCTION is_rule_deferred(p_rule_id IN NUMBER)
90 RETURN BOOLEAN IS
91 l_deferred_flag varchar2(1);
92 BEGIN
93 IF p_rule_id IS NULL
94 THEN
95 RETURN FALSE;
96 ELSE
97 SELECT nvl(deferred_revenue_flag, 'N')
98 INTO l_deferred_flag
99 FROM ra_rules
100 WHERE rule_id = p_rule_id;
101
102 IF l_deferred_flag = 'Y'
103 THEN
104 IF pg_debug IN ('Y','C')
105 THEN
106 arp_debug.debug('Rule is deferred, no contingency on AR side');
107 END IF;
108 RETURN TRUE;
109 ELSE
110 RETURN FALSE;
111 END IF;
112 END IF;
113 RETURN FALSE;
114 END is_rule_deferred;
115
116 PROCEDURE record_acceptance (
117 p_api_version IN NUMBER,
118 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
119 p_commit IN VARCHAR2 := fnd_api.g_false,
120 p_order_line IN line_flex_rec,
121 x_return_status OUT NOCOPY VARCHAR2,
122 x_msg_count OUT NOCOPY NUMBER,
123 x_msg_data OUT NOCOPY VARCHAR2) IS
124
125 l_api_version CONSTANT NUMBER := 1.0;
126 l_api_name CONSTANT VARCHAR2(30) := 'record_acceptance';
127
128 l_scenario NUMBER;
129 l_first_adj_num NUMBER;
130 l_last_adj_num NUMBER;
131 l_ram_desc_flexfield ar_revenue_management_pvt.desc_flexfield;
132 l_line_count NUMBER;
133 l_acctd_adjustable_amount NUMBER;
134 l_adjustable_amount NUMBER;
135 l_rows NUMBER;
136 l_target_in_ar BOOLEAN := FALSE;
137 l_first_row BOOLEAN := TRUE;
138 l_sum_dist NUMBER;
139
140 CURSOR parent_lines IS
141 SELECT pl.customer_trx_line_id, pl.customer_trx_id,
142 NVL(pl.autorule_complete_flag,'Y') autorule_complete_flag,
143 pl.accounting_rule_id
144 FROM ra_customer_trx_lines pl
145 WHERE interface_line_context = p_order_line.interface_line_context
146 AND interface_line_attribute1 = p_order_line.interface_line_attribute1
147 AND interface_line_attribute2 = p_order_line.interface_line_attribute2
148 AND interface_line_attribute3 = p_order_line.interface_line_attribute3
149 AND interface_line_attribute4 = p_order_line.interface_line_attribute4
150 AND interface_line_attribute5 = p_order_line.interface_line_attribute5
151 AND interface_line_attribute6 = p_order_line.interface_line_attribute6;
152
153 CURSOR child_lines(parent_trx_line_id NUMBER) IS
154 SELECT child.customer_trx_line_id,
155 child.customer_trx_id,
156 NVL(cline.autorule_complete_flag,'Y') autorule_complete_flag,
157 cline.accounting_rule_id
158 FROM ra_customer_trx_lines pline,
159 ar_deferred_lines child,
160 ra_customer_trx_lines cline
161 WHERE pline.customer_trx_line_id = parent_trx_line_id
162 AND pline.interface_line_context = g_om_context
163 AND child.parent_line_id = to_number(pline.interface_line_attribute6)
164 AND child.customer_trx_id = cline.customer_trx_id
165 AND child.customer_trx_line_id = cline.customer_trx_line_id;
166
167 BEGIN
168 IF pg_debug IN ('Y','C')
169 THEN
170 arp_debug.debug('ar_deferral_reasons_grp.record_acceptance()+');
171 arp_debug.debug(' acceptance_date = ' || p_order_line.acceptance_date);
172 END IF;
173
174 -- Standard Start of API savepoint
175 SAVEPOINT record_acceptance_grp;
176
177 -- Standard call to check for call compatibility.
178 IF NOT FND_API.Compatible_API_Call (
179 p_current_version_number => l_api_version,
180 p_caller_version_number => p_api_version,
181 p_api_name => l_api_name,
182 p_pkg_name => g_pkg_name) THEN
183
184 RAISE fnd_api.g_exc_unexpected_error;
185
186 END IF;
187
188 -- Initialize message list if p_init_msg_list is set to TRUE.
189 IF FND_API.to_Boolean( p_init_msg_list ) THEN
190 fnd_msg_pub.initialize;
191 END IF;
192
193 /* 5283886 - initialize ar_raapi_util */
194 ar_raapi_util.constant_system_values;
195
196 -- Initialize API return status to success
197 x_return_status := FND_API.G_RET_STS_SUCCESS;
198
199 /* Parent or target lines */
200 FOR p_line_rec IN parent_lines LOOP
201 IF pg_debug IN ('Y','C')
202 THEN
203 arp_debug.debug('accepting parent line : ' || p_line_rec.customer_trx_line_id);
204 arp_debug.debug(' so_line_id : ' ||
205 p_order_line.interface_line_attribute6);
206 END IF;
207
208 /* 13064007 - Skip this line if it is deferred */
209 IF is_rule_deferred(p_line_rec.accounting_rule_id)
210 THEN
211 CONTINUE;
212 END IF;
213
214 /* 8362201 - Check if rev rec has been run. If not,
215 run it for this transaction */
216 IF l_first_row
217 THEN
218 l_first_row := FALSE;
219
220 IF p_line_rec.autorule_complete_flag = 'N'
221 THEN
222 l_sum_dist := arp_auto_rule.create_distributions(
223 p_commit => 'N',
224 p_debug => 'N',
225 p_trx_id => p_line_rec.customer_trx_id);
226
227 IF pg_debug IN ('Y','C')
228 THEN
229 arp_debug.debug('Rev rec created ' || l_sum_dist ||
230 ' distributions for child trx_id ' ||
231 p_line_rec.customer_trx_id);
232 END IF;
233
234 END IF;
235 END IF;
236
237 /* 5501735 - found the target here, no need to sweep interface
238 table for it at end of this routine */
239 l_target_in_ar := TRUE;
240
241 /* Removed fix for 5283886, zero adjustments are fine now */
242
243 ar_revenue_management_pvt.process_event(
244 p_cust_trx_line_id => p_line_rec.customer_trx_line_id,
245 p_event_date => TRUNC(NVL(p_order_line.acceptance_date,
246 sysdate)),
247 p_event_code => 'CUSTOMER_ACCEPTANCE');
248
249 /* 5279702 - process child lines as well */
250
251 FOR c_line_rec IN child_lines(p_line_rec.customer_trx_line_id) LOOP
252 IF pg_debug IN ('Y','C')
253 THEN
254 arp_debug.debug('accepting child line : ' ||
255 c_line_rec.customer_trx_line_id);
256 END IF;
257
258 /* 13064007 - Skip this line if it is deferred */
259 IF is_rule_deferred(c_line_rec.accounting_rule_id)
260 THEN
261 CONTINUE;
262 END IF;
263
264 /* 8362201 - run autoaccounting if needed */
265 IF c_line_rec.autorule_complete_flag = 'N'
266 THEN
267 l_sum_dist := arp_auto_rule.create_distributions(
268 p_commit => 'N',
269 p_debug => 'N',
270 p_trx_id => c_line_rec.customer_trx_id);
271
272 IF pg_debug IN ('Y','C')
273 THEN
274 arp_debug.debug('Rev rec created ' || l_sum_dist ||
275 ' distributions for child trx_id ' ||
276 c_line_rec.customer_trx_id);
277 END IF;
278 END IF;
279
280 ar_revenue_management_pvt.process_event(
281 p_cust_trx_line_id => c_line_rec.customer_trx_line_id,
282 p_event_date => TRUNC(NVL(p_order_line.acceptance_date,
283 sysdate)),
284 p_event_code => 'CUSTOMER_ACCEPTANCE');
285
286 END LOOP;
287
288 END LOOP;
289
290 /* 5501735 - Lines might be in interface tables */
291 /* This updates parent or target lines */
292 IF l_target_in_ar = FALSE
293 THEN
294 UPDATE AR_INTERFACE_CONTS ic
295 SET COMPLETED_FLAG = 'Y',
296 EXPIRATION_DATE = TRUNC(NVL(p_order_line.acceptance_date,
297 sysdate))
298 WHERE ic.interface_line_context = g_om_context
299 AND interface_line_context = p_order_line.interface_line_context
300 AND interface_line_attribute1 = p_order_line.interface_line_attribute1
301 AND interface_line_attribute2 = p_order_line.interface_line_attribute2
302 AND interface_line_attribute3 = p_order_line.interface_line_attribute3
303 AND interface_line_attribute4 = p_order_line.interface_line_attribute4
304 AND interface_line_attribute5 = p_order_line.interface_line_attribute5
305 AND interface_line_attribute6 = p_order_line.interface_line_attribute6
306 AND EXISTS (SELECT 'acceptance contingency'
307 FROM ar_deferral_reasons dr
308 WHERE dr.contingency_id = ic.contingency_id
309 AND dr.revrec_event_code = 'CUSTOMER_ACCEPTANCE');
310
311 IF pg_debug IN ('Y','C')
312 THEN
313 l_rows := SQL%ROWCOUNT;
314 arp_debug.debug(' parent interface lines updated : ' || l_rows);
315 END IF;
316 END IF;
317
318 /* This updates any child lines in interface table */
319 UPDATE AR_INTERFACE_CONTS ic
320 SET COMPLETED_FLAG = 'Y',
321 EXPIRATION_DATE = NVL(p_order_line.acceptance_date,
322 TRUNC(sysdate))
323 WHERE interface_line_context = g_om_context
324 AND nvl(completed_flag, 'N') = 'N'
325 AND EXISTS (SELECT 'child exists'
326 FROM ra_interface_lines il
327 WHERE il.parent_line_id =
328 to_number(p_order_line.interface_line_attribute6)
329 AND il.interface_line_context = ic.interface_line_context
330 AND il.interface_line_attribute1 = ic.interface_line_attribute1
331 AND il.interface_line_attribute2 = ic.interface_line_attribute2
332 AND il.interface_line_attribute3 = ic.interface_line_attribute3
333 AND il.interface_line_attribute4 = ic.interface_line_attribute4
334 AND il.interface_line_attribute5 = ic.interface_line_attribute5
335 AND il.interface_line_attribute6 = ic.interface_line_attribute6)
336 AND EXISTS (SELECT 'acceptance contingency'
337 FROM ar_deferral_reasons dr
338 WHERE dr.contingency_id = ic.contingency_id
339 AND dr.revrec_event_code = 'CUSTOMER_ACCEPTANCE');
340
341 IF pg_debug IN ('Y','C')
342 THEN
343 l_rows := SQL%ROWCOUNT;
344 arp_debug.debug(' child interface lines updated : ' || l_rows);
345 arp_debug.debug('ar_deferral_reasons_grp.record_acceptance()-');
346 END IF;
347
348 EXCEPTION
349 WHEN fnd_api.g_exc_error THEN
350 arp_debug.debug('EXCEPTION: fnd_api.g_exc_error');
351 ROLLBACK TO record_acceptance_grp;
352 x_return_status := FND_API.G_RET_STS_ERROR ;
353 fnd_msg_pub.count_and_get (
354 p_encoded => fnd_api.g_false,
355 p_count => x_msg_count,
356 p_data => x_msg_data);
357
358 WHEN fnd_api.g_exc_unexpected_error THEN
359 arp_debug.debug('EXCEPTION: fnd_api.g_exc_unexpected_error');
360 ROLLBACK TO record_acceptance_grp;
361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
362 fnd_msg_pub.count_and_get (
363 p_encoded => fnd_api.g_false,
364 p_count => x_msg_count,
365 p_data => x_msg_data);
366
367 WHEN OTHERS THEN
368 arp_debug.debug('EXCEPTION: Others');
369 ROLLBACK TO record_acceptance_grp;
370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
371 fnd_msg_pub.count_and_get (
372 p_encoded => fnd_api.g_false,
373 p_count => x_msg_count,
374 p_data => x_msg_data);
375
376 END record_acceptance;
377
378
379 PROCEDURE record_proof_of_delivery (
380 p_api_version IN NUMBER,
381 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
382 p_commit IN VARCHAR2 := fnd_api.g_false,
383 p_delivery_id IN NUMBER,
384 p_pod_date IN DATE,
385 x_return_status OUT NOCOPY VARCHAR2,
386 x_msg_count OUT NOCOPY NUMBER,
387 x_msg_data OUT NOCOPY VARCHAR2) IS
388
389 l_api_version CONSTANT NUMBER := 1.0;
390 l_api_name CONSTANT VARCHAR2(30) := 'record_proof_of_delivery';
391
392 l_sales_order_line_id NUMBER;
393 l_delivery_id NUMBER;
394 l_pod_date DATE;
395 l_order_line line_flex_rec;
396
397 CURSOR so_line_id (p_delivery_id NUMBER) IS
398 SELECT wdd.source_line_id order_line_id
399 FROM wsh_delivery_details wdd,
400 wsh_delivery_assignments wda
401 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
402 AND wda.delivery_id = p_delivery_id;
403
404 CURSOR lines (p_order_line line_flex_rec) IS
405 SELECT customer_trx_line_id, customer_trx_id
406 FROM ra_customer_trx_lines
407 WHERE interface_line_context = p_order_line.interface_line_context
408 AND interface_line_attribute1 = p_order_line.interface_line_attribute1
409 AND interface_line_attribute1 = p_order_line.interface_line_attribute2
410 AND interface_line_attribute1 = p_order_line.interface_line_attribute3
411 AND interface_line_attribute1 = p_order_line.interface_line_attribute4
412 AND interface_line_attribute1 = p_order_line.interface_line_attribute5
413 AND interface_line_attribute1 = p_order_line.interface_line_attribute6;
414
415 BEGIN
416
417 -- Standard Start of API savepoint
418 SAVEPOINT record_proof_of_delivery_grp;
419
420 -- Standard call to check for call compatibility.
421 IF NOT FND_API.Compatible_API_Call (
422 p_current_version_number => l_api_version,
423 p_caller_version_number => p_api_version,
424 p_api_name => l_api_name,
425 p_pkg_name => g_pkg_name) THEN
426
427 RAISE fnd_api.g_exc_unexpected_error;
428
429 END IF;
430
431 -- Initialize message list if p_init_msg_list is set to TRUE.
432 IF FND_API.to_Boolean( p_init_msg_list ) THEN
433 fnd_msg_pub.initialize;
434 END IF;
435
436 -- Initialize API return status to success
437 x_return_status := FND_API.G_RET_STS_SUCCESS;
438
439 -- event name: oracle.apps.fte.delivery.pod.podreceived
440 -- l_delivery_id := p_event.GetValueForParameter('DELIVERY_ID');
441 -- l_pod_date := p_event.GetValueForParameter('POD_DATE');
442
443 FOR so_line_rec IN so_line_id(p_delivery_id) LOOP
444
445 -- call OM API to get the first 6 interface attributes
446 -- and loop through them
447
448 /* 5501735 - added this call as it was missing in
449 POD routines */
450 OE_AR_Acceptance_GRP.Get_interface_attributes
451 ( p_line_id => so_line_rec.order_line_id
452 , x_line_flex_rec => l_order_line
453 , x_return_status => x_return_status
454 , x_msg_count => x_msg_count
455 , x_msg_data => x_msg_data
456 );
457
458 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
459 THEN
460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
461 END IF;
462
463 -- for each invoice line call ar_revenue_managemet_id with
464 -- invoice line id andpod date
465
466 FOR line_rec IN lines(l_order_line) LOOP
467
468 ar_revenue_management_pvt.process_event(
469 p_cust_trx_line_id => line_rec.customer_trx_line_id,
470 p_event_date => p_pod_date,
471 p_event_code => 'PROOF_OF_DELIVERY');
472
473 END LOOP;
474
475 END LOOP;
476
477 EXCEPTION
478 WHEN fnd_api.g_exc_error THEN
479 ROLLBACK TO record_proof_of_delivery_grp;
480 x_return_status := FND_API.G_RET_STS_ERROR ;
481 fnd_msg_pub.count_and_get (
482 p_encoded => fnd_api.g_false,
483 p_count => x_msg_count,
484 p_data => x_msg_data);
485
486 WHEN fnd_api.g_exc_unexpected_error THEN
487 ROLLBACK TO record_proof_of_delivery_grp;
488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
489 fnd_msg_pub.count_and_get (
490 p_encoded => fnd_api.g_false,
491 p_count => x_msg_count,
492 p_data => x_msg_data);
493
494 WHEN OTHERS THEN
495 ROLLBACK TO record_proof_of_delivery_grp;
496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
497 fnd_msg_pub.count_and_get (
498 p_encoded => fnd_api.g_false,
499 p_count => x_msg_count,
500 p_data => x_msg_data);
501
502 END record_proof_of_delivery;
503
504 END ar_deferral_reasons_grp;