[Home] [Help]
PACKAGE BODY: APPS.ECE_CDMO_UTIL
Source
1 package body ece_cdmo_util as
2 -- $Header: ECCDMOB.pls 120.5 2006/05/02 10:30:29 sbastida noship $
3 PROCEDURE Update_AR ( Document_Type IN VARCHAR2,
4 Transaction_ID IN NUMBER,
5 Installment_Number IN NUMBER,
6 Multiple_Installments_Flag IN VARCHAR2,
7 Maximum_Installment_Number IN NUMBER,
8 Update_Date IN DATE )
9 IS
10
11
12 l_Update_Value VARCHAR2(20);
13 l_EDI_Flag VARCHAR2(1);
14 l_Print_Flag VARCHAR2(1);
15 xprogress varchar2(100);
16
17 BEGIN
18
19 ec_debug.push('ECE_AR_TRANSACTION.UPDATE_AR');
20 ec_debug.pl ( 3, 'Document_Type: ', Document_Type );
21 ec_debug.pl ( 3, 'Transaction_ID: ', Transaction_ID );
22 ec_debug.pl ( 3, 'Installment_Number: ', Installment_Number );
23 ec_debug.pl ( 3, 'Multiple_Installments_Flag: ',Multiple_Installments_Flag );
24 ec_debug.pl ( 3, 'Maximum_Installment_Number: ',Maximum_Installment_Number );
25 ec_debug.pl ( 3, 'Update_Date: ',Update_Date );
26
27 xProgress := '2000-20';
28 BEGIN
29 /* Replaced as per bug:5081637
30 SELECT edi_flag,
31 print_flag
32 INTO l_EDI_flag,
33 l_Print_flag
34 FROM ece_cdmo_header_v eih,
35 ece_tp_details etd,
36 hz_cust_acct_sites cas
37 WHERE eih.bill_to_address_id = cas.cust_acct_site_id
38 AND cas.tp_header_id = etd.tp_header_id
39 AND etd.document_type = Update_AR.Document_Type
40 AND eih.transaction_id = Update_AR.Transaction_ID;
41 */
42
43 SELECT etd.edi_flag,etd.print_flag
44 INTO l_EDI_flag,
45 l_Print_flag
46 FROM
47 ra_customer_trx rct,
48 hz_cust_site_uses_all csu,
49 hz_cust_acct_sites_all cas,
50 ece_tp_headers eth,
51 ece_tp_details etd
52 WHERE
53 rct.bill_to_site_use_id = csu.site_use_id and
54 csu.cust_acct_site_id = cas.cust_acct_site_id and
55 cas.tp_header_id = eth.tp_header_id and
56 eth.tp_header_id = etd.tp_header_id and
57 rct.CUSTOMER_TRX_ID = update_ar.transaction_id and
58 etd.document_type = update_ar.document_type;
59
60 EXCEPTION
61 WHEN NO_DATA_FOUND THEN
62 ec_debug.pl ( 0,
63 'EC',
64 'ECE_NO_ROW_SELECTED',
65 'PROGRESS_LEVEL',
66 xProgress,
67 'INFO',
68 'EDI FLAG, PRINT FLAG',
69 'TABLE_NAME',
70 'ECE_CDMO_HEADER_V, ECE_TP_DETAILS, HZ_CUST_ACCT_SITES' );
71 -- bug 4718847
72 END;
73
74 IF l_EDI_Flag = 'Y' AND
75 l_Print_Flag = 'Y'
76 THEN
77 l_Update_Value := 'EP';
78 END IF;
79
80 IF l_EDI_Flag = 'Y' AND
81 l_Print_Flag <> 'Y'
82 THEN
83 l_Update_Value := 'ED';
84 END IF;
85
86 IF l_EDI_Flag <> 'Y' AND
87 l_Print_Flag = 'Y'
88 THEN
89 l_Update_Value := 'PR';
90 END IF;
91
92 ec_debug.pl ( 3, 'L_UPDATE_VALUE: ',l_Update_Value );
93
94 xProgress := '2010-20';
95 UPDATE ra_customer_trx
96 SET last_update_date = SYSDATE,
97 printing_pending = DECODE (Document_Type,
98 'CM', 'N',
99 'OACM', 'N',
100 DECODE (Maximum_Installment_Number,
101 Installment_Number, 'N',
102 NULL, 'N',
103 1, 'N',
104 'Y')),
105 printing_count = NVL(printing_count,0) + 1,
106 printing_last_printed = SYSDATE,
107 printing_original_date = DECODE (NVL(printing_count,0),
108 0, SYSDATE,
109 printing_original_date ),
110 last_printed_sequence_num = DECODE (Multiple_Installments_Flag,
111 'N',NULL,
112 GREATEST(NVL(last_printed_sequence_num,0),
113 Installment_Number)),
114 edi_processed_flag = 'Y',
115 edi_processed_status = l_Update_Value
116 WHERE customer_trx_id = Update_AR.Transaction_ID;
117
118 IF SQL%NOTFOUND
119 THEN
120 ec_debug.pl (0,
121 'EC',
122 'ECE_NO_ROW_UPDATED',
123 'PROGRESS_LEVEL',
124 xProgress,
125 'INFO',
126 'EDI PROCESSED',
127 'TABLE_NAME',
128 'RA_CUSTOMER_TRX' );
129 END IF;
130
131 /* The following lines were commented out was because of a request
132 from a beta site. Their business practice requires them to
133 print multiple installment invoices at the same time.
134
135 BE AWARE: by doing so, we are removing the data consistency test.
136 */
137 -- AND LAST_UPDATE_DATE = Update_AR.Update_Date;
138
139 -- The join on last_update_date is to ensure that the
140 -- record has not been updated by another user, between
141 -- the select above and the lock created by this update.
142
143 /* IF SQL%NOTFOUND THEN
144 RAISE_APPLICATION_ERROR(-20000,'Record changed by another user.');
145 END IF;
146 */
147
148 ec_debug.pop('ECE_AR_TRANSACTION.UPDATE_AR');
149 EXCEPTION
150 WHEN OTHERS THEN
151
152 ec_debug.pl ( 0,
153 'EC',
154 'ECE_PROGRAM_ERROR',
155 'PROGRESS_LEVEL',
156 xProgress );
157
158 ec_debug.pl ( 0,
159 'EC',
160 'ECE_ERROR_MESSAGE',
161 'ERROR_MESSAGE',
162 SQLERRM );
163
164 app_exception.raise_exception;
165
166 END Update_AR;
167
168 PROCEDURE GET_REMIT_ADDRESS (
169 customer_trx_id IN NUMBER,
170 remit_to_address1 OUT NOCOPY VARCHAR2,
171 remit_to_address2 OUT NOCOPY VARCHAR2,
172 remit_to_address3 OUT NOCOPY VARCHAR2,
173 remit_to_address4 OUT NOCOPY VARCHAR2,
174 remit_to_city OUT NOCOPY VARCHAR2,
175 remit_to_county OUT NOCOPY VARCHAR2,
176 remit_to_state OUT NOCOPY VARCHAR2,
177 remit_to_province OUT NOCOPY VARCHAR2,
178 remit_to_country OUT NOCOPY VARCHAR2,
179 remit_to_code_int OUT NOCOPY VARCHAR2,
180 remit_to_postal_code OUT NOCOPY VARCHAR2)
181 IS
182
183 xprogress varchar2(100);
184 L_REMIT_TO_ADDRESS_ID NUMBER;
185 DUMMY NUMBER;
186
187 BEGIN
188
189 ec_debug.pl(3, 'Inside Get Remit to Address');
190 xProgress := '2000-30';
191 begin
192 SELECT REMIT_TO_ADDRESS_ID INTO l_remit_to_address_id
193 FROM RA_CUSTOMER_TRX
194 WHERE CUSTOMER_TRX_ID = get_remit_address.customer_trx_id;
195 exception
196 when no_data_found
197 then
198 l_remit_to_address_id := null;
199 ec_debug.pl(3, 'Remit to not found. Setting to Null');
200 end;
201
202 IF l_remit_to_address_id IS NULL THEN
203
204 DECLARE
205 -- bug 4718847
206 CURSOR remit_cur IS
207 SELECT RT.ADDRESS_ID
208 FROM RA_CUSTOMER_TRX RCT,HZ_CUST_ACCT_SITES A,RA_REMIT_TOS RT,
209 HZ_PARTY_SITES HPS,HZ_LOCATIONS LOC
210 WHERE RCT.CUSTOMER_TRX_ID = get_remit_address.customer_trx_id
211 AND RCT.BILL_TO_ADDRESS_ID = A.CUST_ACCT_SITE_ID
212 AND A.PARTY_SITE_ID = HPS.PARTY_SITE_ID
213 AND HPS.LOCATION_ID = LOC.LOCATION_ID
214 AND RT.STATUS = 'A'
215 AND NVL(A.STATUS,'A') = 'A'
216 AND RT.COUNTRY = LOC.COUNTRY
217 AND ( LOC.STATE = NVL(RT.STATE, LOC.STATE )
218 OR ( LOC.STATE IS NULL
219 AND RT.STATE IS NULL
220 )
221 OR ( LOC.STATE IS NULL
222 AND LOC.POSTAL_CODE <= NVL(RT.POSTAL_CODE_HIGH, LOC.POSTAL_CODE)
223 AND LOC.POSTAL_CODE >= NVL(RT.POSTAL_CODE_LOW, LOC.POSTAL_CODE)
224 AND ( POSTAL_CODE_LOW IS NOT NULL
225 OR POSTAL_CODE_HIGH IS NOT NULL
226 )
227 )
228 )
229 AND ( ( LOC.POSTAL_CODE <= NVL(RT.POSTAL_CODE_HIGH, LOC.POSTAL_CODE)
230 AND LOC.POSTAL_CODE >= NVL(RT.POSTAL_CODE_LOW, LOC.POSTAL_CODE)
231 )
232 OR ( LOC.POSTAL_CODE IS NULL
233 AND RT.POSTAL_CODE_LOW IS NULL
234 AND RT.POSTAL_CODE_HIGH IS NULL
235 )
236 )
237 ORDER BY RT.STATE, RT.POSTAL_CODE_LOW, RT.POSTAL_CODE_HIGH;
238
239 BEGIN
240 -- We only want the first record from the select since the
241 -- order by puts the records in a special order
242 xProgress := '2010-30';
243 OPEN remit_cur;
244 FETCH remit_cur INTO l_remit_to_address_id;
245 IF remit_cur%NOTFOUND THEN
246 l_remit_to_address_id := NULL;
247 END IF;
248 CLOSE remit_cur;
249
250 END;
251
252 END IF;
253
254 IF l_remit_to_address_id IS NULL THEN
255 xProgress := '2020-30';
256
257 SELECT MIN(ADDRESS_ID) INTO l_remit_to_address_id
258 FROM RA_REMIT_TOS
259 WHERE STATUS='A'
260 AND STATE = 'DEFAULT'
261 AND COUNTRY = 'DEFAULT';
262
263 END IF;
264 -- bug 4718847
265 xProgress := '2030-30';
266 SELECT LOC.ADDRESS1, LOC.ADDRESS2, LOC.ADDRESS3, LOC.ADDRESS4,
267 LOC.CITY, LOC.COUNTY, LOC.STATE, LOC.PROVINCE, LOC.COUNTRY, LOC.POSTAL_CODE,
268 HCAS.ORIG_SYSTEM_REFERENCE
269 INTO remit_to_address1, remit_to_address2, remit_to_address3,
270 remit_to_address4, remit_to_city, remit_to_county, remit_to_state,
271 remit_to_province, remit_to_country, remit_to_postal_code,
272 remit_to_code_int
273 FROM HZ_CUST_ACCT_SITES HCAS,
274 HZ_LOCATIONS LOC,
275 HZ_PARTY_SITES HPS
276 WHERE HPS.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
277 AND HPS.LOCATION_ID = LOC.LOCATION_ID
278 AND HCAS.CUST_ACCT_SITE_ID = l_remit_to_address_id;
279
280 EXCEPTION
281 WHEN OTHERS THEN
282 fnd_message.set_name('EC','ECE_PROGRAM_ERROR');
283 fnd_message.set_token('PROGRESS_LEVEL',xProgress);
284 ec_debug.pl( 3,fnd_message.get);
285 ec_debug.pl( 3,SQLERRM);
286 /* app_exception.raise_exception; */
287
288 END GET_REMIT_ADDRESS;
289
290 PROCEDURE GET_PAYMENT (
291 customer_trx_id IN NUMBER,
292 installment_number IN NUMBER,
293 multiple_installments_flag OUT NOCOPY VARCHAR2,
294 maximum_installment_number OUT NOCOPY NUMBER,
295 amount_tax_due OUT NOCOPY NUMBER,
296 amount_charges_due OUT NOCOPY NUMBER,
297 amount_freight_due OUT NOCOPY NUMBER,
298 amount_line_items_due OUT NOCOPY NUMBER,
299 total_amount_due OUT NOCOPY NUMBER,
300 total_amount_remaining OUT NOCOPY NUMBER)
301
302 IS
303 xprogress varchar2(100);
304 l_term_id NUMBER;
305 l_payment_schedule_exists VARCHAR2(1);
306 l_term_base_amount NUMBER;
307 l_term_relative_amount NUMBER;
308 l_minimum_installment_number NUMBER;
309 l_amount_tax_due NUMBER;
310 l_amount_charges_due NUMBER;
311 l_amount_freight_due NUMBER;
312 l_amount_line_items_due NUMBER;
313 l_first_installment_code VARCHAR2(30);
314 l_type VARCHAR2(30);
315 l_currency_precision NUMBER;
316
317 -- This procedure gets the amount due/credited for a paricular installment
318 -- of an Invoice or Credit Memo (or any of the related documents)
319
320 BEGIN
321
322 -- This select statement is used to determine whether this transaction
323 -- has a payment_schedule. If it does we can get all of the information
324 -- we need directly from the payment_schedule, else we need to derive it
325 -- from the payment term.
326 xProgress := '2000-40';
327 SELECT RCT.TERM_ID, FC.PRECISION, RCTT.ACCOUNTING_AFFECT_FLAG,
328 RCTT.TYPE, RT.FIRST_INSTALLMENT_CODE,
329 DECODE(RCTT.TYPE,
330 'CM',
331 'N',
332 'OACM',
333 'N',
334 DECODE(COUNT(*),
335 0,
336 'N',
337 1,
338 'N',
339 'Y')),
340 MAX(RTL.SEQUENCE_NUM),
341 MIN(RTL.SEQUENCE_NUM)
342 INTO l_term_id, l_currency_precision, l_payment_schedule_exists, l_type,
343 l_first_installment_code, multiple_installments_flag,
344 maximum_installment_number, l_minimum_installment_number
345 FROM RA_CUSTOMER_TRX RCT, RA_CUST_TRX_TYPES RCTT, RA_TERMS_LINES RTL,
346 RA_TERMS RT, FND_CURRENCIES FC
347 WHERE RCT.CUSTOMER_TRX_ID = get_payment.customer_trx_id
348 AND RCT.INVOICE_CURRENCY_CODE = FC.CURRENCY_CODE
349 AND RCT.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID
350 AND RCT.TERM_ID = RT.TERM_ID (+)
351 AND RT.TERM_ID = RTL.TERM_ID (+)
352 GROUP BY RCT.TERM_ID, FC.PRECISION, RCTT.ACCOUNTING_AFFECT_FLAG,
353 RCTT.TYPE, RT.FIRST_INSTALLMENT_CODE;
354
355 xProgress := '2010-40';
356 SELECT NVL(MIN(RTL.RELATIVE_AMOUNT),1), NVL(MIN(RT.BASE_AMOUNT),1)
357 INTO l_term_relative_amount, l_term_base_amount
358 FROM RA_TERMS RT, RA_TERMS_LINES RTL
359 WHERE RT.TERM_ID = l_term_id
360 AND RT.TERM_ID = RTL.TERM_ID
361 AND RTL.SEQUENCE_NUM = get_payment.installment_number;
362
363 IF l_payment_schedule_exists = 'Y' THEN
364 xProgress := '2020-40';
365 SELECT NVL(TAX_ORIGINAL,0),
366 NVL(FREIGHT_ORIGINAL,0),
367 NVL(AMOUNT_LINE_ITEMS_ORIGINAL,0),
368 NVL(AMOUNT_DUE_ORIGINAL,0),
369 NVL(AMOUNT_DUE_REMAINING,0)
370 INTO amount_tax_due, amount_freight_due,
371 amount_line_items_due, total_amount_due,
372 total_amount_remaining
373 FROM AR_PAYMENT_SCHEDULES
374 WHERE CUSTOMER_TRX_ID = get_payment.customer_trx_id
375 AND DECODE(l_type,
376 'CM',get_payment.installment_number,
377 'OACM',get_payment.installment_number,
378 NVL(TERMS_SEQUENCE_NUMBER, get_payment.installment_number))
379 = get_payment.installment_number;
380
381 xProgress := '2030-40';
382 SELECT NVL(SUM((NVL(RCTL.QUANTITY_INVOICED, RCTL.QUANTITY_CREDITED) *
383 RCTL.UNIT_SELLING_PRICE)
384 * l_term_relative_amount / l_term_base_amount),0)
385 INTO amount_charges_due
386 FROM RA_CUSTOMER_TRX_LINES RCTL
387 WHERE RCTL.CUSTOMER_TRX_ID = get_payment.customer_trx_id
388 AND RCTL.LINE_TYPE = 'CHARGES';
389
390 ELSE
391
392 -- There isn't any payment_schedule, so we need to get the information by
393 -- summing up the tax, freight and lines and then applying the payment
394 -- term, currency precision and if tax/freight are prorated
395 xProgress := '2040-40';
396 SELECT ROUND(SUM(EXTENDED_AMOUNT * l_term_relative_amount /
397 l_term_base_amount),l_currency_precision)
398 INTO l_amount_line_items_due
399 FROM RA_CUSTOMER_TRX_LINES
400 WHERE CUSTOMER_TRX_ID = get_payment.customer_trx_id
401 AND LINE_TYPE NOT IN ('TAX','FREIGHT','CHARGES');
402
403 xProgress := '2050-40';
404 SELECT ROUND(SUM(EXTENDED_AMOUNT * l_term_relative_amount /
405 l_term_base_amount),l_currency_precision)
406 INTO l_amount_charges_due
407 FROM RA_CUSTOMER_TRX_LINES
408 WHERE CUSTOMER_TRX_ID = get_payment.customer_trx_id
409 AND LINE_TYPE = 'CHARGES';
410
411 -- Check to see if the tax/freight are prorated across installments
412 -- or if they are simply included on the first installment.
413
414 xProgress := '2060-40';
415 IF l_first_installment_code = 'INCLUDE' THEN
416 xProgress := '2070-40';
417 IF l_minimum_installment_number = get_payment.installment_number THEN
418
419 xProgress := '2080-40';
420 SELECT SUM(EXTENDED_AMOUNT)
421 INTO l_amount_tax_due
422 FROM RA_CUSTOMER_TRX_LINES
423 WHERE CUSTOMER_TRX_ID = get_payment.customer_trx_id
424 AND LINE_TYPE = 'TAX';
425
426 xProgress := '2090-40';
427 SELECT SUM(EXTENDED_AMOUNT)
428 INTO l_amount_freight_due
429 FROM RA_CUSTOMER_TRX_LINES
430 WHERE CUSTOMER_TRX_ID = get_payment.customer_trx_id
431 AND LINE_TYPE = 'FREIGHT';
432
433 ELSE
434 l_amount_tax_due := 0;
435 l_amount_freight_due := 0;
436 END IF;
437
438 ELSE
439
440 xProgress := '2100-40';
441 SELECT ROUND(SUM(EXTENDED_AMOUNT * l_term_relative_amount /
442 l_term_base_amount),l_currency_precision)
443 INTO l_amount_tax_due
444 FROM RA_CUSTOMER_TRX_LINES
445 WHERE CUSTOMER_TRX_ID = get_payment.customer_trx_id
446 AND LINE_TYPE = 'TAX';
447
448 xProgress := '2110-40';
449 SELECT ROUND(SUM(EXTENDED_AMOUNT * l_term_relative_amount /
450 l_term_base_amount),l_currency_precision)
451 INTO l_amount_freight_due
452 FROM RA_CUSTOMER_TRX_LINES
453 WHERE CUSTOMER_TRX_ID = get_payment.customer_trx_id
454 AND LINE_TYPE = 'FREIGHT';
455
456 END IF;
457
458 -- Total up the values and assign them to the out parameters.
459 xProgress := '2120-40';
460 total_amount_due := l_amount_tax_due + l_amount_freight_due
461 + l_amount_charges_due + l_amount_line_items_due;
462 amount_tax_due := NVL(l_amount_tax_due,0);
463 amount_charges_due := NVL(l_amount_charges_due,0);
464 amount_freight_due := NVL(l_amount_freight_due,0);
465 amount_line_items_due := NVL(l_amount_line_items_due,0);
466
467 END IF;
468
469
470 EXCEPTION
471 WHEN OTHERS THEN
472 fnd_message.set_name('EC','ECE_PROGRAM_ERROR');
473 fnd_message.set_token('PROGRESS_LEVEL',xProgress);
474 app_exception.raise_exception;
475 END GET_PAYMENT;
476
477
478 -- The following procedure gets the discount information
479 -- for the term being used. The discount info is a sub-table
480 -- off of terms, this procedure will get the first three
481 -- discounts, this is a denormalization, but is being used
482 -- to avoid the overhead of another level of data.
483 -- Also it is assumed that Credit Memo types (CM and OACM) do not have
484 -- payment terms information, even though they mat have a payment term
485
486 PROCEDURE GET_TERM_DISCOUNT (
487 document_type IN VARCHAR2,
488 term_id IN NUMBER,
489 term_sequence_number IN NUMBER,
490 discount_percent1 OUT NOCOPY NUMBER,
491 discount_days1 OUT NOCOPY NUMBER,
492 discount_date1 OUT NOCOPY DATE,
493 discount_day_of_month1 OUT NOCOPY NUMBER,
494 discount_months_forward1 OUT NOCOPY NUMBER,
495 discount_percent2 OUT NOCOPY NUMBER,
496 discount_days2 OUT NOCOPY NUMBER,
497 discount_date2 OUT NOCOPY DATE,
498 discount_day_of_month2 OUT NOCOPY NUMBER,
499 discount_months_forward2 OUT NOCOPY NUMBER,
500 discount_percent3 OUT NOCOPY NUMBER,
501 discount_days3 OUT NOCOPY NUMBER,
502 discount_date3 OUT NOCOPY DATE,
503 discount_day_of_month3 OUT NOCOPY NUMBER,
504 discount_months_forward3 OUT NOCOPY NUMBER)
505 IS
506 CURSOR discount IS SELECT DISCOUNT_PERCENT,
507 DISCOUNT_DAYS,
508 DISCOUNT_DATE,
509 DISCOUNT_DAY_OF_MONTH,
510 DISCOUNT_MONTHS_FORWARD
511 FROM RA_TERMS_LINES_DISCOUNTS
512 WHERE TERM_ID = get_term_discount.term_id
513 AND SEQUENCE_NUM =
514 get_term_discount.term_sequence_number;
515 l_counter NUMBER DEFAULT 1;
516 l_discount_percent NUMBER;
517 l_discount_days NUMBER;
518 l_discount_date DATE;
519 l_discount_day_of_month NUMBER;
520 l_discount_months_forward NUMBER;
521 xprogress varchar2(100);
522
523 BEGIN
524
525 xProgress := '2000-50';
526 IF get_term_discount.document_type IN ('CM','OACM') THEN
527
528 discount_percent1 := null;
529 discount_days1 := null;
530 discount_date1 := null;
531 discount_day_of_month1 := null;
532 discount_months_forward1 := null;
533 discount_percent2 := null;
534 discount_days2 := null;
535 discount_date2 := null;
536 discount_day_of_month2 := null;
537 discount_months_forward2 := null;
538 discount_percent3 := null;
539 discount_days3 := null;
540 discount_date3 := null;
541 discount_day_of_month3 := null;
542 discount_months_forward3 := null;
543
544 ELSE
545 xProgress := '2010-50';
546 OPEN DISCOUNT;
547
548 LOOP
549 xProgress := '2020-50';
550 FETCH discount into l_discount_percent,
551 l_discount_days,
552 l_discount_date,
553 l_discount_day_of_month,
554 l_discount_months_forward;
555 EXIT WHEN discount%NOTFOUND;
556
557 xProgress := '2030-50';
558 IF l_counter = 1 THEN
559 discount_percent1 := l_discount_percent;
560 discount_days1 := l_discount_days;
561 discount_date1 := l_discount_date;
562 discount_day_of_month1 := l_discount_day_of_month;
563 discount_months_forward1 := l_discount_months_forward;
564 END IF;
565
566 xProgress := '2040-50';
567 IF l_counter = 2 THEN
568 discount_percent2 := l_discount_percent;
569 discount_days2 := l_discount_days;
570 discount_date2 := l_discount_date;
571 discount_day_of_month2 := l_discount_day_of_month;
572 discount_months_forward2 := l_discount_months_forward;
573 END IF;
574
575 xProgress := '2050-50';
576 IF l_counter = 3 THEN
577 discount_percent3 := l_discount_percent;
578 discount_days3 := l_discount_days;
579 discount_date3 := l_discount_date;
580 discount_day_of_month3 := l_discount_day_of_month;
581 discount_months_forward3 := l_discount_months_forward;
582 END IF;
583
584 l_counter := l_counter + 1;
585
586 END LOOP;
587
588 END IF;
589 EXCEPTION
590 WHEN others THEN
591 fnd_message.set_name('EC','ECE_PROGRAM_ERROR');
592 fnd_message.set_token('PROGRESS_LEVEL',xProgress);
593 app_exception.raise_exception;
594 END GET_TERM_DISCOUNT;
595
596 --Bug 1940758
597 PROCEDURE UPDATE_HEADER_WITH_LINE (
598 p_customer_trx_id IN NUMBER) IS
599 nPos1 pls_integer;
600 nPos2 pls_integer;
601 nPos3 pls_integer;
602 nPos4 pls_integer;
603 nPos5 pls_integer;
604 nPos6 pls_integer;
605 nPos7 pls_integer;
606 nPos8 pls_integer;
607 l_gross_weight varchar2(30);
608 l_net_weight varchar2(30);
609 l_weight_uom_code varchar2(3);
610 l_volume varchar2(30);
611 l_volume_uom_code varchar2(3);
612 l_shipment_number varchar2(30);
613 l_booking_number varchar2(30);
614 l_bill_of_lading varchar2(30);
615 xprogress varchar2(100);
616 BEGIN
617 xProgress := '2070-10';
618 ec_utils.find_pos (
619 1,
620 'GROSS_WEIGHT',
621 nPos1);
622
623 xProgress := '2070-20';
624 ec_utils.find_pos (
625 1,
626 'NET_WEIGHT',
627 nPos2);
628
629 xProgress := '2070-30';
630 ec_utils.find_pos (
631 1,
632 'WEIGHT_UOM_CODE_INT',
633 nPos3);
634
635 xProgress := '2070-40';
636 ec_utils.find_pos (
637 1,
638 'VOLUME',
639 nPos4);
640
641 xProgress := '2070-50';
642 ec_utils.find_pos (
643 1,
644 'VOLUME_UOM_CODE_INT',
645 nPos5);
646
647 xProgress := '2070-60';
648 ec_utils.find_pos (
649 1,
650 'SHIPMENT_NUMBER',
651 nPos6);
652
653 xProgress := '2070-70';
654 ec_utils.find_pos (
655 1,
656 'BOOKING_NUMBER',
657 nPos7);
658
659 xProgress := '2070-80';
660 ec_utils.find_pos (
661 1,
662 'BILL_OF_LADING_NUMBER',
663 nPos8);
664
665 begin
666 xProgress := '2070-90';
667 select TO_CHAR(gross_weight),
668 TO_CHAR(net_weight),
669 weight_uom_code_int,
670 TO_CHAR(volume),
671 volume_uom_code_int,
672 TO_CHAR(shipment_number) ,
673 booking_number,
674 bill_of_lading_number
675 into l_gross_weight,
676 l_net_weight,
677 l_weight_uom_code,
678 l_volume,
679 l_volume_uom_code,
680 l_shipment_number,
681 l_booking_number,
682 l_bill_of_lading
683 from ece_cdmo_line_v
684 where transaction_id = p_customer_trx_id
685 and sales_order_number is not null
686 and rownum=1;
687
688 exception
689 when no_data_found then
690 ec_debug.pl(3,'No records found for the customer_trx_id',p_customer_trx_id);
691 end;
692
693 xProgress := '2070-100';
694 ec_utils.g_file_tbl(nPos1).value:= l_gross_weight;
695 ec_utils.g_file_tbl(nPos2).value:= l_net_weight;
696 ec_utils.g_file_tbl(nPos3).value:= l_weight_uom_code;
697 ec_utils.g_file_tbl(nPos4).value:= l_volume;
698 ec_utils.g_file_tbl(nPos5).value:= l_volume_uom_code;
699 ec_utils.g_file_tbl(nPos6).value:= l_shipment_number;
700 ec_utils.g_file_tbl(nPos7).value:= l_booking_number;
701 ec_utils.g_file_tbl(nPos8).value:= l_bill_of_lading;
702
703 EXCEPTION
704 WHEN others THEN
705 fnd_message.set_name('EC','ECE_PROGRAM_ERROR');
706 fnd_message.set_token('PROGRESS_LEVEL',xProgress);
707 app_exception.raise_exception;
708 END UPDATE_HEADER_WITH_LINE;
709
710 end ece_cdmo_util;