[Home] [Help]
PACKAGE BODY: APPS.AR_DEFERRAL_REASONS_GRP
Source
1 PACKAGE BODY ar_deferral_reasons_grp AS
2 /* $Header: ARXRDRB.pls 120.8 2006/09/18 17:27:33 mraymond noship $ */
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 -- Initialize API return status to success
49 x_return_status := FND_API.G_RET_STS_SUCCESS;
50
51 fun_rule_pub.apply_rule_bulk (
52 p_application_short_name => 'AR',
53 p_rule_object_name => ar_revenue_management_pvt.c_rule_object_name,
54 p_param_view_name => 'AR_RDR_PARAMETERS_GT',
55 p_additional_where_clause => '1=1',
56 p_primary_key_column_name => 'SOURCE_LINE_ID'
57 );
58
59 EXCEPTION
60 WHEN fnd_api.g_exc_error THEN
61 ROLLBACK TO default_reasons_grp;
62 x_return_status := FND_API.G_RET_STS_ERROR ;
63 fnd_msg_pub.count_and_get (
64 p_encoded => fnd_api.g_false,
65 p_count => x_msg_count,
66 p_data => x_msg_data);
67
68 WHEN fnd_api.g_exc_unexpected_error THEN
69 ROLLBACK TO default_reasons_grp;
70 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
71 fnd_msg_pub.count_and_get (
72 p_encoded => fnd_api.g_false,
73 p_count => x_msg_count,
74 p_data => x_msg_data);
75
76 WHEN OTHERS THEN
77 ROLLBACK TO default_reasons_grp;
78 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
79 fnd_msg_pub.count_and_get (
80 p_encoded => fnd_api.g_false,
81 p_count => x_msg_count,
82 p_data => x_msg_data);
83
84 END default_reasons;
85
86
87 PROCEDURE record_acceptance (
88 p_api_version IN NUMBER,
89 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
90 p_commit IN VARCHAR2 := fnd_api.g_false,
91 p_order_line IN line_flex_rec,
92 x_return_status OUT NOCOPY VARCHAR2,
93 x_msg_count OUT NOCOPY NUMBER,
94 x_msg_data OUT NOCOPY VARCHAR2) IS
95
96 l_api_version CONSTANT NUMBER := 1.0;
97 l_api_name CONSTANT VARCHAR2(30) := 'record_acceptance';
98
99 l_scenario NUMBER;
100 l_first_adj_num NUMBER;
101 l_last_adj_num NUMBER;
102 l_ram_desc_flexfield ar_revenue_management_pvt.desc_flexfield;
103 l_line_count NUMBER;
104 l_acctd_adjustable_amount NUMBER;
105 l_adjustable_amount NUMBER;
106 l_rows NUMBER;
107 l_target_in_ar BOOLEAN := FALSE;
108 CURSOR parent_lines IS
109 SELECT customer_trx_line_id, customer_trx_id
110 FROM ra_customer_trx_lines
111 WHERE interface_line_context = p_order_line.interface_line_context
112 AND interface_line_attribute1 = p_order_line.interface_line_attribute1
113 AND interface_line_attribute2 = p_order_line.interface_line_attribute2
114 AND interface_line_attribute3 = p_order_line.interface_line_attribute3
115 AND interface_line_attribute4 = p_order_line.interface_line_attribute4
116 AND interface_line_attribute5 = p_order_line.interface_line_attribute5
117 AND interface_line_attribute6 = p_order_line.interface_line_attribute6;
118
119 CURSOR child_lines(parent_trx_line_id NUMBER) IS
120 SELECT child.customer_trx_line_id, child.customer_trx_id
121 FROM ra_customer_trx_lines pline,
122 ar_deferred_lines child
123 WHERE pline.customer_trx_line_id = parent_trx_line_id
124 AND pline.interface_line_context = g_om_context
125 AND child.parent_line_id = to_number(pline.interface_line_attribute6);
126
127 BEGIN
128 IF pg_debug IN ('Y','C')
129 THEN
130 arp_debug.debug('ar_deferral_reasons_grp.record_acceptance()+');
131 END IF;
132
133 -- Standard Start of API savepoint
134 SAVEPOINT record_acceptance_grp;
135
136 -- Standard call to check for call compatibility.
137 IF NOT FND_API.Compatible_API_Call (
138 p_current_version_number => l_api_version,
139 p_caller_version_number => p_api_version,
140 p_api_name => l_api_name,
141 p_pkg_name => g_pkg_name) THEN
142
143 RAISE fnd_api.g_exc_unexpected_error;
144
145 END IF;
146
147 -- Initialize message list if p_init_msg_list is set to TRUE.
148 IF FND_API.to_Boolean( p_init_msg_list ) THEN
149 fnd_msg_pub.initialize;
150 END IF;
151
152 /* 5283886 - initialize ar_raapi_util */
153 ar_raapi_util.constant_system_values;
154
155 -- Initialize API return status to success
156 x_return_status := FND_API.G_RET_STS_SUCCESS;
157
158 /* Parent or target lines */
159 FOR p_line_rec IN parent_lines LOOP
160 IF pg_debug IN ('Y','C')
161 THEN
162 arp_debug.debug('accepting parent line : ' || p_line_rec.customer_trx_line_id);
163 arp_debug.debug(' so_line_id : ' ||
164 p_order_line.interface_line_attribute6);
165 END IF;
166
167 /* 5501735 - found the target here, no need to sweep interface
168 table for it at end of this routine */
169 l_target_in_ar := TRUE;
170
171 /* 5283886 - Prevent calls into revenue adjustment API
172 when there is no revenue to earn */
173 l_adjustable_amount := ar_raapi_util.adjustable_revenue (
174 p_customer_trx_line_id => p_line_rec.customer_trx_line_id,
175 p_adjustment_type => 'EA',
176 p_customer_trx_id => p_line_rec.customer_trx_id,
177 p_salesrep_id => NULL,
178 p_sales_credit_type => NULL,
179 p_item_id => NULL,
180 p_category_id => NULL,
181 p_revenue_adjustment_id => NULL,
182 p_line_count_out => l_line_count,
183 p_acctd_amount_out => l_acctd_adjustable_amount);
184
185 IF l_adjustable_amount = 0
186 THEN
187 IF pg_debug IN ('Y','C')
188 THEN
189 arp_debug.debug(' skipping parent adj, amount is zero');
190 arp_debug.debug('ar_deferral_reasons_grp.record_acceptance()-');
191 END IF;
192 GOTO skip_parent_adj;
193 END IF;
194 /* End - 5283886 */
195
196 ar_revenue_management_pvt.process_event(
197 p_cust_trx_line_id => p_line_rec.customer_trx_line_id,
198 p_event_date => sysdate,
199 p_event_code => 'CUSTOMER_ACCEPTANCE');
200
201 <<skip_parent_adj>>
202 /* 5279702 - process child lines as well */
203 FOR c_line_rec IN child_lines(p_line_rec.customer_trx_line_id) LOOP
204 IF pg_debug IN ('Y','C')
205 THEN
206 arp_debug.debug('accepting child line : ' ||
207 c_line_rec.customer_trx_line_id);
208 END IF;
209
210 /* 5283886 - Prevent calls into revenue adjustment API
211 when there is no revenue to earn */
212 l_adjustable_amount := ar_raapi_util.adjustable_revenue (
213 p_customer_trx_line_id => c_line_rec.customer_trx_line_id,
214 p_adjustment_type => 'EA',
215 p_customer_trx_id => c_line_rec.customer_trx_id,
216 p_salesrep_id => NULL,
217 p_sales_credit_type => NULL,
218 p_item_id => NULL,
219 p_category_id => NULL,
220 p_revenue_adjustment_id => NULL,
221 p_line_count_out => l_line_count,
222 p_acctd_amount_out => l_acctd_adjustable_amount);
223
224 IF nvl(l_adjustable_amount,0) = 0
225 THEN
226 IF pg_debug IN ('Y','C')
227 THEN
228 arp_debug.debug(' skipping child adj, amount is zero');
229 arp_debug.debug('ar_deferral_reasons_grp.record_acceptance()-');
230 END IF;
231 ELSE
232 /* Do the adjustment as usual */
233 ar_revenue_management_pvt.process_event(
234 p_cust_trx_line_id => c_line_rec.customer_trx_line_id,
235 p_event_date => sysdate,
236 p_event_code => 'CUSTOMER_ACCEPTANCE');
237 END IF;
238 /* End - 5283886 */
239
240 END LOOP;
241
242 END LOOP;
243
244 /* 5501735 - Lines might be in interface tables */
245 /* This updates parent or target lines */
246 IF l_target_in_ar = FALSE
247 THEN
248 UPDATE AR_INTERFACE_CONTS ic
249 SET COMPLETED_FLAG = 'Y',
250 EXPIRATION_DATE = sysdate
251 WHERE ic.interface_line_context = g_om_context
252 AND interface_line_context = p_order_line.interface_line_context
253 AND interface_line_attribute1 = p_order_line.interface_line_attribute1
254 AND interface_line_attribute2 = p_order_line.interface_line_attribute2
255 AND interface_line_attribute3 = p_order_line.interface_line_attribute3
256 AND interface_line_attribute4 = p_order_line.interface_line_attribute4
257 AND interface_line_attribute5 = p_order_line.interface_line_attribute5
258 AND interface_line_attribute6 = p_order_line.interface_line_attribute6
259 AND EXISTS (SELECT 'acceptance contingency'
260 FROM ar_deferral_reasons dr
261 WHERE dr.contingency_id = ic.contingency_id
262 AND dr.revrec_event_code = 'CUSTOMER_ACCEPTANCE');
263
264 IF pg_debug IN ('Y','C')
265 THEN
266 l_rows := SQL%ROWCOUNT;
267 arp_debug.debug(' parent interface lines updated : ' || l_rows);
268 END IF;
269 END IF;
270
271 /* This updates any child lines in interface table */
272 UPDATE AR_INTERFACE_CONTS ic
273 SET COMPLETED_FLAG = 'Y',
274 EXPIRATION_DATE = sysdate
275 WHERE interface_line_context = g_om_context
276 AND nvl(completed_flag, 'N') = 'N'
277 AND EXISTS (SELECT 'child exists'
278 FROM ra_interface_lines il
279 WHERE il.parent_line_id =
280 to_number(p_order_line.interface_line_attribute6)
281 AND il.interface_line_context = ic.interface_line_context
282 AND il.interface_line_attribute1 = ic.interface_line_attribute1
283 AND il.interface_line_attribute2 = ic.interface_line_attribute2
284 AND il.interface_line_attribute3 = ic.interface_line_attribute3
285 AND il.interface_line_attribute4 = ic.interface_line_attribute4
286 AND il.interface_line_attribute5 = ic.interface_line_attribute5
287 AND il.interface_line_attribute6 = ic.interface_line_attribute6)
288 AND EXISTS (SELECT 'acceptance contingency'
289 FROM ar_deferral_reasons dr
290 WHERE dr.contingency_id = ic.contingency_id
291 AND dr.revrec_event_code = 'CUSTOMER_ACCEPTANCE');
292
293 IF pg_debug IN ('Y','C')
294 THEN
295 l_rows := SQL%ROWCOUNT;
296 arp_debug.debug(' child interface lines updated : ' || l_rows);
297 arp_debug.debug('ar_deferral_reasons_grp.record_acceptance()-');
298 END IF;
299
300 EXCEPTION
301 WHEN fnd_api.g_exc_error THEN
302 arp_debug.debug('EXCEPTION: fnd_api.g_exc_error');
303 ROLLBACK TO record_acceptance_grp;
304 x_return_status := FND_API.G_RET_STS_ERROR ;
305 fnd_msg_pub.count_and_get (
306 p_encoded => fnd_api.g_false,
307 p_count => x_msg_count,
308 p_data => x_msg_data);
309
313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
310 WHEN fnd_api.g_exc_unexpected_error THEN
311 arp_debug.debug('EXCEPTION: fnd_api.g_exc_unexpected_error');
312 ROLLBACK TO record_acceptance_grp;
314 fnd_msg_pub.count_and_get (
315 p_encoded => fnd_api.g_false,
316 p_count => x_msg_count,
317 p_data => x_msg_data);
318
319 WHEN OTHERS THEN
320 arp_debug.debug('EXCEPTION: Others');
321 ROLLBACK TO record_acceptance_grp;
322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
323 fnd_msg_pub.count_and_get (
324 p_encoded => fnd_api.g_false,
325 p_count => x_msg_count,
326 p_data => x_msg_data);
327
328 END record_acceptance;
329
330
331 PROCEDURE record_proof_of_delivery (
332 p_api_version IN NUMBER,
333 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
334 p_commit IN VARCHAR2 := fnd_api.g_false,
335 p_delivery_id IN NUMBER,
336 p_pod_date IN DATE,
337 x_return_status OUT NOCOPY VARCHAR2,
338 x_msg_count OUT NOCOPY NUMBER,
339 x_msg_data OUT NOCOPY VARCHAR2) IS
340
341 l_api_version CONSTANT NUMBER := 1.0;
342 l_api_name CONSTANT VARCHAR2(30) := 'record_proof_of_delivery';
343
344 l_sales_order_line_id NUMBER;
345 l_delivery_id NUMBER;
346 l_pod_date DATE;
347 l_order_line line_flex_rec;
348
349 CURSOR so_line_id (p_delivery_id NUMBER) IS
350 SELECT wdd.source_line_id order_line_id
351 FROM wsh_delivery_details wdd,
352 wsh_delivery_assignments wda
353 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
354 AND wda.delivery_id = p_delivery_id;
355
356 CURSOR lines (p_order_line line_flex_rec) IS
357 SELECT customer_trx_line_id, customer_trx_id
358 FROM ra_customer_trx_lines
359 WHERE interface_line_context = p_order_line.interface_line_context
360 AND interface_line_attribute1 = p_order_line.interface_line_attribute1
361 AND interface_line_attribute1 = p_order_line.interface_line_attribute2
362 AND interface_line_attribute1 = p_order_line.interface_line_attribute3
363 AND interface_line_attribute1 = p_order_line.interface_line_attribute4
364 AND interface_line_attribute1 = p_order_line.interface_line_attribute5
365 AND interface_line_attribute1 = p_order_line.interface_line_attribute6;
366
367 BEGIN
368
369 -- Standard Start of API savepoint
370 SAVEPOINT record_proof_of_delivery_grp;
371
372 -- Standard call to check for call compatibility.
373 IF NOT FND_API.Compatible_API_Call (
374 p_current_version_number => l_api_version,
375 p_caller_version_number => p_api_version,
376 p_api_name => l_api_name,
377 p_pkg_name => g_pkg_name) THEN
378
379 RAISE fnd_api.g_exc_unexpected_error;
380
381 END IF;
382
383 -- Initialize message list if p_init_msg_list is set to TRUE.
384 IF FND_API.to_Boolean( p_init_msg_list ) THEN
385 fnd_msg_pub.initialize;
386 END IF;
387
388 -- Initialize API return status to success
389 x_return_status := FND_API.G_RET_STS_SUCCESS;
390
391 -- event name: oracle.apps.fte.delivery.pod.podreceived
392 -- l_delivery_id := p_event.GetValueForParameter('DELIVERY_ID');
393 -- l_pod_date := p_event.GetValueForParameter('POD_DATE');
394
395 FOR so_line_rec IN so_line_id(p_delivery_id) LOOP
396
397 -- call OM API to get the first 6 interface attributes
398 -- and loop through them
399
400 /* 5501735 - added this call as it was missing in
401 POD routines */
402 OE_AR_Acceptance_GRP.Get_interface_attributes
403 ( p_line_id => so_line_rec.order_line_id
404 , x_line_flex_rec => l_order_line
405 , x_return_status => x_return_status
406 , x_msg_count => x_msg_count
407 , x_msg_data => x_msg_data
408 );
409
410 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
411 THEN
412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
413 END IF;
414
415 -- for each invoice line call ar_revenue_managemet_id with
416 -- invoice line id andpod date
417
418 FOR line_rec IN lines(l_order_line) LOOP
419
420 ar_revenue_management_pvt.process_event(
421 p_cust_trx_line_id => line_rec.customer_trx_line_id,
422 p_event_date => p_pod_date,
423 p_event_code => 'PROOF_OF_DELIVERY');
424
425 END LOOP;
426
427 END LOOP;
428
429 EXCEPTION
430 WHEN fnd_api.g_exc_error THEN
431 ROLLBACK TO record_proof_of_delivery_grp;
432 x_return_status := FND_API.G_RET_STS_ERROR ;
433 fnd_msg_pub.count_and_get (
434 p_encoded => fnd_api.g_false,
435 p_count => x_msg_count,
436 p_data => x_msg_data);
437
438 WHEN fnd_api.g_exc_unexpected_error THEN
439 ROLLBACK TO record_proof_of_delivery_grp;
440 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
441 fnd_msg_pub.count_and_get (
442 p_encoded => fnd_api.g_false,
443 p_count => x_msg_count,
444 p_data => x_msg_data);
445
446 WHEN OTHERS THEN
447 ROLLBACK TO record_proof_of_delivery_grp;
448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
449 fnd_msg_pub.count_and_get (
450 p_encoded => fnd_api.g_false,
451 p_count => x_msg_count,
452 p_data => x_msg_data);
453
454 END record_proof_of_delivery;
455
456 END ar_deferral_reasons_grp;