[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_FREIGHT_UTIL
Source
1 PACKAGE BODY ARP_PROCESS_FREIGHT_UTIL AS
2 /* $Header: ARTEFR1B.pls 115.6 2003/10/10 14:28:17 mraymond ship $ */
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4
5 pg_number_dummy number;
6 pg_date_dummy date;
7
8 /*===========================================================================+
9 | PROCEDURE |
10 | default_freight_line |
11 | |
12 | DESCRIPTION |
13 | Procedure to default values for a freight line |
14 | |
15 | SCOPE - PRIVATE |
16 | |
17 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
18 | arp_util.debug |
19 | |
20 | ARGUMENTS : IN: |
21 | p_form_name |
22 | p_form_version |
23 | p_ct_id |
24 | p_line_ctl_id |
25 | p_prev_ct_id |
26 | p_cust_trx_type_id |
27 | p_primary_salesrep_id |
28 | p_inventory_item_id |
29 | p_memo_line_id |
30 | p_currency_code |
31 | OUT: |
32 | p_line_prev_ctl_id |
33 | p_prev_ctl_id |
34 | p_amount |
35 | p_inv_line_number |
36 | p_inv_frt_ccid |
37 | p_inv_frt_amount |
38 | p_inv_frt_uncr_amount |
39 | p_ccid |
40 | p_concat_segments |
41 | p_ussgl_code |
42 | p_ct_id |
43 | IN/ OUT: |
44 | |
45 | RETURNS : NONE |
46 | |
47 | NOTES |
48 | |
49 | MODIFICATION HISTORY |
50 | 12-JUL-95 Subash Chadalavada Created |
51 | |
52 +===========================================================================*/
53
54 PROCEDURE default_freight_line(
55 p_form_name IN varchar2,
56 p_form_version IN number,
57 p_ct_id IN ra_customer_trx.customer_trx_id%type,
58 p_line_ctl_id IN ra_customer_trx_lines.customer_trx_line_id%type,
59 p_prev_ct_id IN ra_customer_trx.customer_trx_id%type,
60 p_cust_trx_type_id IN ra_customer_trx.cust_trx_type_id%type,
61 p_primary_salesrep_id IN ra_customer_trx.cust_trx_type_id%type,
62 p_inventory_item_id IN ra_customer_trx_lines.inventory_item_id%type,
63 p_memo_line_id IN ra_customer_trx_lines.memo_line_id%type,
64 p_currency_code IN fnd_currencies.currency_code%type,
65 p_line_prev_ctl_id IN OUT NOCOPY ra_customer_trx_lines.customer_trx_line_id%type,
66 p_prev_ctl_id OUT NOCOPY ra_customer_trx_lines.customer_trx_line_id%type,
67 p_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
68 p_inv_line_number OUT NOCOPY ra_customer_trx_lines.line_number%type,
69 p_inv_frt_ccid OUT NOCOPY ra_customer_trx_lines.line_number%type,
70 p_inv_frt_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
71 p_inv_frt_uncr_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
72 p_ccid OUT NOCOPY ra_cust_trx_line_gl_dist.code_combination_id%type,
73 p_concat_segments OUT NOCOPY ra_cust_trx_line_gl_dist.concatenated_segments%type,
74 p_ussgl_code OUT NOCOPY ra_customer_trx.default_ussgl_transaction_code%type)
75 IS
76 l_inv_ctl_id ra_customer_trx_lines.customer_trx_line_id%type;
77 l_inv_frt_ctl_id ra_customer_trx_lines.customer_trx_line_id%type;
78 l_inv_frt_ccid ra_cust_trx_line_gl_dist.code_combination_id%type;
79 l_inv_prev_ctl_id ra_customer_trx_lines.customer_trx_line_id%type;
80 l_inv_line_number ra_customer_trx_lines.line_number%type;
81 l_amount ra_customer_trx_lines.extended_amount%type;
82 l_line_ussgl_code ra_customer_trx_lines.default_ussgl_transaction_code%type;
83 l_header_ussgl_code ra_customer_trx.default_ussgl_transaction_code%type;
84 l_inventory_item_id ra_customer_trx_lines.inventory_item_id%type := p_inventory_item_id;
85 l_memo_line_id ra_customer_trx_lines.memo_line_id%type := p_memo_line_id;
86 l_warehouse_id ra_customer_trx_lines.warehouse_id%type := '';
87
88 l_inv_line_extended_amount ra_customer_trx_lines.extended_amount%type;
89 l_line_extended_amount ra_customer_trx_lines.extended_amount%type;
90 l_inv_frt_amount ra_customer_trx_lines.extended_amount%type;
91 l_inv_frt_uncr_amount ra_customer_trx_lines.extended_amount%type;
92
93 l_ccid number;
94 l_num_failed_rows number;
95 l_result number;
96 l_concat_segments varchar2(2000);
97 l_errorbuf varchar2(200);
98
99 BEGIN
100
101 IF PG_DEBUG in ('Y', 'C') THEN
102 arp_util.debug('get_default_fob: ' || 'arp_process_freight.default_freight_line()+');
103 END IF;
104
105 --
106 -- get the line_id that the parent line is crediting, if one is not passed
107 --
108 IF (p_line_prev_ctl_id IS NULL
109 AND
110 p_line_ctl_id IS NOT NULL)
111 THEN
112
113 SELECT previous_customer_trx_line_id,
114 inventory_item_id,
115 memo_line_id,
116 extended_amount,
117 default_ussgl_transaction_code
118 INTO l_inv_prev_ctl_id,
119 l_inventory_item_id,
120 l_memo_line_id,
121 l_line_extended_amount,
122 l_line_ussgl_code
123 FROM ra_customer_trx_lines
124 WHERE customer_trx_id = p_ct_id
125 AND customer_trx_line_id = p_line_ctl_id;
126 ELSE
127 l_inv_prev_ctl_id := p_line_prev_ctl_id;
128 END IF;
129
130 IF (p_prev_ct_id IS NOT NULL)
131 THEN
132
133 --
134 -- get credited freight line information
135 --
136
137 SELECT ctl_inv.ctl_line_line_number,
138 ctl_inv.ctl_line_extended_amount,
139 ctl_inv.customer_trx_line_id,
140 ctl_inv.lgd_code_combination_id,
141 ctl_inv.extended_amount,
142 ctl_inv.ctl_frt_balance
143 INTO l_inv_line_number,
144 l_inv_line_extended_amount,
145 l_inv_frt_ctl_id,
146 l_inv_frt_ccid,
147 l_inv_frt_amount,
148 l_inv_frt_uncr_amount
149 FROM RA_CUSTOMER_TRX_LINES_FRT_V ctl_inv
150 WHERE ctl_inv.customer_trx_id = p_prev_ct_id
151 AND nvl(ctl_inv.link_to_cust_trx_line_id, -10)
152 = nvl(l_inv_prev_ctl_id, -10);
153
154 END IF;
155
156 IF (l_inv_prev_ctl_id IS NOT NULL) THEN
157 --points to the line credited so get the warehouse id
158 select ctl.warehouse_id
159 into l_warehouse_id
160 from ra_customer_trx_lines ctl
161 where ctl.customer_trx_line_id = l_inv_prev_ctl_id;
162 END IF;
163
164 IF (p_line_prev_ctl_id IS NULL)
165 THEN p_line_prev_ctl_id := l_inv_prev_ctl_id;
166 END IF;
167
168 p_prev_ctl_id := l_inv_frt_ctl_id;
169 p_inv_line_number := l_inv_line_number;
170 p_inv_frt_amount := l_inv_frt_amount;
171 p_inv_frt_uncr_amount := l_inv_frt_uncr_amount;
172 p_inv_frt_ccid := l_inv_frt_ccid;
173 p_ussgl_code := l_line_ussgl_code;
174
175 BEGIN
176 ARP_AUTO_ACCOUNTING.do_autoaccounting(
177 'G',
178 'FREIGHT',
179 p_ct_id,
180 null,
181 null,
182 null,
183 null,
184 null,
185 null,
186 null,
187 null,
188 p_cust_trx_type_id,
189 p_primary_salesrep_id,
190 l_inventory_item_id,
191 l_memo_line_id,
192 l_warehouse_id,
193 l_ccid,
194 l_concat_segments,
195 l_num_failed_rows);
196
197
198 p_ccid := l_ccid;
199 p_concat_segments := l_concat_segments;
200
201 EXCEPTION
202 WHEN arp_auto_accounting.no_ccid THEN
203 null;
204 WHEN NO_DATA_FOUND THEN
205 null;
206 WHEN OTHERS THEN
207 RAISE;
208 END;
209
210
211 IF ( l_memo_line_id IS NOT NULL)
212 THEN
213
214 SELECT unit_std_price
215 INTO l_amount
216 FROM ar_memo_lines
217 WHERE memo_line_id = l_memo_line_id;
218
219 ELSIF (p_prev_ct_id IS NOT NULL)
220 THEN
221
222 IF (nvl(l_inv_line_extended_amount, 0) <> 0)
223 THEN
224
225 l_amount := arpcurr.CurrRound(
226 (nvl(l_line_extended_amount, 0) /
227 l_inv_line_extended_amount) * l_inv_frt_amount,
228 p_currency_code);
229
230 END IF;
231 END IF;
232
233 p_amount := l_amount;
234
235 IF PG_DEBUG in ('Y', 'C') THEN
236 arp_util.debug('get_default_fob: ' || 'arp_process_freight.default_freight_line()-');
237 END IF;
238
239 EXCEPTION
240 WHEN OTHERS THEN
241
242 IF PG_DEBUG in ('Y', 'C') THEN
243 arp_util.debug('get_default_fob: ' || 'EXCEPTION : arp_process_freight.default_freight_line()');
244 arp_util.debug('get_default_fob: ' || 'p_form_name : '||p_form_name);
245 arp_util.debug('get_default_fob: ' || 'p_form_version : '||p_form_version);
246 arp_util.debug('get_default_fob: ' || 'p_ct_id : '||p_ct_id);
247 arp_util.debug('get_default_fob: ' || 'p_line_ctl_id : '||p_line_ctl_id);
248 arp_util.debug('get_default_fob: ' || 'p_prev_ct_id : '||p_prev_ct_id);
249 arp_util.debug('get_default_fob: ' || 'p_cust_trx_type_id : '||p_cust_trx_type_id);
250 arp_util.debug('get_default_fob: ' || 'p_primary_salesrep_id : '||p_primary_salesrep_id);
251 arp_util.debug('get_default_fob: ' || 'p_inventory_item_id : '||p_inventory_item_id);
252 arp_util.debug('get_default_fob: ' || 'p_memo_line_id : '||p_memo_line_id);
253 arp_util.debug('get_default_fob: ' || 'p_currency_code : '||p_currency_code);
254 END IF;
255 RAISE;
256
257 END;
258
259 /*===========================================================================+
260 | FUNCTION |
261 | get_freight_type |
262 | |
263 | DESCRIPTION |
264 | This function returns the level at which freight is defined for the |
265 | transaction. |
266 | |
267 | SCOPE - PUBLIC |
268 | |
269 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
270 | arp_util.debug |
271 | |
272 | ARGUMENTS : IN: |
273 | p_customer_trx_id |
274 | OUT: |
275 | None |
276 | |
277 | RETURNS : 'H' if header level freight |
278 | 'L' if line level freight |
279 | '' if freight is defined for the transaction |
280 | |
281 | NOTES |
282 | |
283 | MODIFICATION HISTORY |
284 | 24-OCT-95 Subash Chadalavada Created |
285 | |
286 +===========================================================================*/
287
288
289 FUNCTION get_freight_type(
290 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type) RETURN varchar2
291 IS
292 l_frt_type varchar2(1);
293 BEGIN
294
295 IF PG_DEBUG in ('Y', 'C') THEN
296 arp_util.debug('get_default_fob: ' || 'arp_process_freight.get_freight_type()+');
297 END IF;
298
299 SELECT decode(count(*),
300 0, null,
301 decode(max(link_to_cust_trx_line_id),
302 null, 'H',
303 'L'))
304 INTO l_frt_type
305 FROM ra_customer_trx_lines ctl
306 WHERE ctl.customer_trx_id = p_customer_trx_id
307 AND ctl.line_type = 'FREIGHT';
308
309 return(l_frt_type);
310
311 IF PG_DEBUG in ('Y', 'C') THEN
312 arp_util.debug('get_default_fob: ' || 'arp_process_freight.get_freight_type()-');
313 END IF;
314
315 EXCEPTION
316 WHEN OTHERS THEN
317 IF PG_DEBUG in ('Y', 'C') THEN
318 arp_util.debug('get_default_fob: ' || 'EXCEPTION : arp_process_freight.get_freight_type()');
319 arp_util.debug('get_default_fob: ' || 'p_customer_trx_id : '||p_customer_trx_id);
320 END IF;
321
322 RAISE;
323 END get_freight_type;
324
325 /*===========================================================================+
326 | PROCEDURE |
327 | delete_frt_lines |
328 | |
329 | DESCRIPTION |
330 | Procedure to delete all freight lines for a transaction |
331 | |
332 | SCOPE - PRIVATE |
333 | |
334 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
335 | arp_util.debug |
336 | |
337 | ARGUMENTS : IN: |
338 | p_form_name |
339 | p_form_version |
340 | p_trx_class |
341 | p_complete_flag |
342 | p_open_rec_flag |
343 | p_customer_trx_id |
344 | OUT: |
345 | None |
346 | IN/ OUT: |
347 | None |
348 | |
349 | RETURNS : NONE |
350 | |
351 | NOTES |
352 | |
353 | MODIFICATION HISTORY |
354 | 12-JUL-95 Subash Chadalavada Created |
355 | |
356 +===========================================================================*/
357 PROCEDURE delete_frt_lines(
358 p_form_name IN varchar2,
359 p_form_version IN number,
360 p_trx_class IN ra_cust_trx_types.type%type,
361 p_complete_flag IN varchar2,
362 p_open_rec_flag IN varchar2,
363 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type)
364 IS
365
366 CURSOR frt_lines(p_ct_id ra_customer_trx.customer_trx_id%type) IS
367 SELECT customer_trx_line_id
368 FROM ra_customer_trx_lines
369 WHERE customer_trx_id = p_ct_id
370 AND line_type = 'FREIGHT';
371
372 BEGIN
373
374 IF PG_DEBUG in ('Y', 'C') THEN
375 arp_util.debug('get_default_fob: ' || 'arp_process_freight_util.delete_frt_lines()+');
376 END IF;
377
378 /*--------------------------------------------------------+
379 | call the delete handler for each of the freight lines |
380 +--------------------------------------------------------*/
381 FOR frt_line_rec IN frt_lines(p_customer_trx_id) LOOP
382
383 arp_process_freight.delete_freight(
384 p_form_name,
385 p_form_version,
386 p_trx_class,
387 p_complete_flag,
388 p_open_rec_flag,
389 p_customer_trx_id,
390 frt_line_rec.customer_trx_line_id);
391
392 END LOOP;
393
394 IF PG_DEBUG in ('Y', 'C') THEN
395 arp_util.debug('get_default_fob: ' || 'arp_process_freight_util.delete_frt_lines()-');
396 END IF;
397
398 EXCEPTION
399 WHEN OTHERS THEN
400 IF PG_DEBUG in ('Y', 'C') THEN
401 arp_util.debug('get_default_fob: ' || 'EXCEPTION : arp_process_freight_util.delete_frt_lines');
402 arp_util.debug('get_default_fob: ' || 'p_form_name : '||p_form_name);
403 arp_util.debug('get_default_fob: ' || 'p_form_version : '||p_form_version);
404 arp_util.debug('get_default_fob: ' || 'p_trx_class : '||p_trx_class);
405 arp_util.debug('get_default_fob: ' || 'p_complete_flag : '||p_complete_flag);
406 arp_util.debug('get_default_fob: ' || 'p_open_rec_flag : '||p_open_rec_flag);
407 arp_util.debug('get_default_fob: ' || 'p_customer_trx_id : '||p_customer_trx_id);
408 END IF;
409 RAISE;
410 END delete_frt_lines;
411
412 /*===========================================================================+
413 | PROCEDURE |
414 | get_default_fob
415 | |
416 | DESCRIPTION |
417 | Procedure to select the default fob_point.
418 | |
419 | SCOPE - PRIVATE |
420 | |
421 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
422 | arp_util.debug |
423 | |
424 | ARGUMENTS : IN: |
425 | p_form_name |
426 | p_form_version |
427 | p_trx_class |
428 | p_complete_flag |
429 | p_open_rec_flag |
430 | p_customer_trx_id |
431 | OUT: |
432 | None |
433 | IN/ OUT: |
434 | None |
435 | |
436 | RETURNS : NONE |
437 | |
438 | NOTES |
439 | Use the following hierarchy to default fob:
440 | 1) From Ship to site use
441 | 2) From bill to site use
442 | 3) From ship to customer
443 | 4) From bill to customer
444 | |
445 | MODIFICATION HISTORY |
446 | 9/4/1996 Harri Kaukovuo Created
447 +===========================================================================*/
448 PROCEDURE get_default_fob(
449 pn_SHIP_TO_SITE_USE_ID IN NUMBER
450 , pn_BILL_TO_SITE_USE_ID IN NUMBER
451 , pn_SHIP_TO_CUSTOMER_ID IN NUMBER
452 , pn_BILL_TO_CUSTOMER_ID IN NUMBER
453 , pc_fob_point OUT NOCOPY VARCHAR2
454 , pc_fob_point_name OUT NOCOPY VARCHAR2) IS
455 lc_fob_point ar_lookups.lookup_code%TYPE;
456 lc_fob_point_name ar_lookups.meaning%TYPE;
457
458 BEGIN
459 IF (pn_SHIP_TO_SITE_USE_ID IS NOT NULL)
460 THEN
461 begin
462 SELECT l.LOOKUP_CODE
463 , l.MEANING
464 INTO
465 lc_fob_point
466 , lc_fob_point_name
467 FROM AR_LOOKUPS L
468 , HZ_CUST_SITE_USES site_uses
469 WHERE site_uses.SITE_USE_ID = pn_SHIP_TO_SITE_USE_ID
470 AND L.LOOKUP_TYPE = 'FOB'
471 AND L.LOOKUP_CODE = site_uses.FOB_POINT
472 AND L.ENABLED_FLAG = 'Y'
473 AND TRUNC(SYSDATE) BETWEEN L.START_DATE_ACTIVE
474 AND NVL(L.END_DATE_ACTIVE, TRUNC(SYSDATE));
475 exception
476 when no_data_found then
477 null;
478 when others then
479 raise;
480 end;
481
482 -- Return immediately if FOB point was found
483 IF (lc_fob_point IS NOT NULL)
484 THEN
485 pc_fob_point := lc_fob_point;
486 pc_fob_point_name := lc_fob_point_name;
487 RETURN;
488 END IF;
489
490 end if;
491
492 -- ---------------------------------------------------------
493 -- The one who seeks, will find the treasure ...
494 -- Search with bill-to customer site
495 -- ---------------------------------------------------------
496 IF (pn_BILL_TO_SITE_USE_ID IS NOT NULL)
497 THEN
498 begin
499 SELECT l.LOOKUP_CODE
500 , l.MEANING
501 INTO
502 lc_fob_point
503 , lc_fob_point_name
504 FROM AR_LOOKUPS L
505 , HZ_CUST_SITE_USES site_uses
506 WHERE site_uses.SITE_USE_ID = pn_BILL_TO_SITE_USE_ID
507 AND L.LOOKUP_TYPE = 'FOB'
508 AND L.LOOKUP_CODE = site_uses.FOB_POINT
509 AND L.ENABLED_FLAG = 'Y'
510 AND TRUNC(SYSDATE) BETWEEN L.START_DATE_ACTIVE
511 AND NVL(L.END_DATE_ACTIVE, TRUNC(SYSDATE));
512 exception
513 when no_data_found then
514 null;
515 when others then
516 raise;
517 end;
518
519 -- Return immediately if FOB point was found
520 IF (lc_fob_point IS NOT NULL)
521 THEN
522 pc_fob_point := lc_fob_point;
523 pc_fob_point_name := lc_fob_point_name;
524 RETURN;
525 END IF;
526
527 end if;
528
529
530 -- ---------------------------------------------------------
531 -- Search with ship to customer
532 -- ---------------------------------------------------------
533 IF (pn_SHIP_TO_CUSTOMER_ID IS NOT NULL)
534 THEN
535 begin
536 /* modified for tca uptake */
537 SELECT l.LOOKUP_CODE
538 , l.MEANING
539 INTO
540 lc_fob_point
541 , lc_fob_point_name
542 FROM AR_LOOKUPS L
543 , hz_cust_accounts cust_acct
544 WHERE cust_acct.cust_account_id = pn_SHIP_TO_CUSTOMER_ID
545 AND L.LOOKUP_TYPE = 'FOB'
546 AND L.LOOKUP_CODE = cust_acct.FOB_POINT
547 AND L.ENABLED_FLAG = 'Y'
548 AND TRUNC(SYSDATE) BETWEEN L.START_DATE_ACTIVE
549 AND NVL(L.END_DATE_ACTIVE, TRUNC(SYSDATE));
550 exception
551 when no_data_found then
552 null;
553 when others then
554 raise;
555 end;
556
557 -- Return immediately if FOB point was found
558 IF (lc_fob_point IS NOT NULL)
559 THEN
560 pc_fob_point := lc_fob_point;
561 pc_fob_point_name := lc_fob_point_name;
562 RETURN;
563 END IF;
564
565 end if;
566
567 IF (pn_BILL_TO_CUSTOMER_ID IS NOT NULL)
568 THEN
569 begin
570 SELECT l.LOOKUP_CODE
571 , l.MEANING
572 INTO
573 lc_fob_point
574 , lc_fob_point_name
575 FROM AR_LOOKUPS L
576 , hz_cust_accounts cust_acct
577 WHERE cust_acct.cust_account_id= pn_BILL_TO_CUSTOMER_ID
578 AND L.LOOKUP_TYPE = 'FOB'
579 AND L.LOOKUP_CODE = cust_acct.FOB_POINT
580 AND L.ENABLED_FLAG = 'Y'
581 AND TRUNC(SYSDATE) BETWEEN L.START_DATE_ACTIVE
582 AND NVL(L.END_DATE_ACTIVE, TRUNC(SYSDATE));
583 exception
584 when no_data_found then
585 null;
586 when others then
587 raise;
588 end;
589
590 -- Return immediately if FOB point was found
591 IF (lc_fob_point IS NOT NULL)
592 THEN
593 pc_fob_point := lc_fob_point;
594 pc_fob_point_name := lc_fob_point_name;
595 RETURN;
596 END IF;
597
598 end if;
599
600 -- We return empty handed ...
601 pc_fob_point := '';
602 pc_fob_point_name := '';
603
604 EXCEPTION
605 WHEN OTHERS THEN
606 IF PG_DEBUG in ('Y', 'C') THEN
607 arp_util.debug('EXCEPTION : arp_process_freight_util.get_default_fob');
608 arp_util.debug('get_default_fob: ' || 'pn_SHIP_TO_SITE_USE_ID :'||to_char(pn_SHIP_TO_SITE_USE_ID));
609 arp_util.debug('get_default_fob: ' || 'pn_BILL_TO_SITE_USE_ID :'||to_char(pn_BILL_TO_SITE_USE_ID));
610 arp_util.debug('get_default_fob: ' || 'pn_SHIP_TO_CUSTOMER_ID :'||to_char(pn_SHIP_TO_CUSTOMER_ID));
611 arp_util.debug('get_default_fob: ' || 'pn_BILL_TO_CUSTOMER_ID :'||to_char(pn_BILL_TO_CUSTOMER_ID));
612 END IF;
613
614 RAISE;
615
616 end get_default_fob;
617
618 END ARP_PROCESS_FREIGHT_UTIL;