[Home] [Help]
PACKAGE BODY: APPS.ECE_AR_TRANSACTION
Source
1 PACKAGE BODY ece_ar_transaction AS
2 -- $Header: ECEINOB.pls 120.7.12000000.2 2007/03/20 18:14:02 cpeixoto ship $
3
4 l_Organization_ID NUMBER;
5 l_Automotive_Installed BOOLEAN;
6 l_Industry VARCHAR2(240);
7 l_Schema VARCHAR2(240);
8 l_Status VARCHAR2(240);
9 l_Automotive_Status VARCHAR2(240);
10 l_Remit_To_Address_ID NUMBER;
11 base_currency_code varchar2(3);
12 rc BOOLEAN;
13
14 xProgress VARCHAR2(30);
15
16 /*===========================================================================
17
18 PROCEDURE NAME: Extract_INO_Outbound
19
20 PURPOSE: This procedure initiates the concurrent process to
21 extract the invoices.
22
23 ===========================================================================*/
24
25 PROCEDURE extract_ino_outbound(
26 errbuf OUT NOCOPY VARCHAR2,
27 retcode OUT NOCOPY VARCHAR2,
28 cOutputPath IN VARCHAR2,
29 cOutput_Filename IN VARCHAR2,
30 cCDate_From IN VARCHAR2,
31 cCDate_To IN VARCHAR2,
32 cCustomer_Name IN VARCHAR2,
33 cSite_Use_Code IN VARCHAR2,
34 cDocument_Type IN VARCHAR2,
35 cTransaction_Number IN VARCHAR2,
36 cdebug_mode IN NUMBER DEFAULT 0) IS
37
38 xHeaderCount NUMBER;
39 iRun_id NUMBER := 0;
40 iRequestID NUMBER := 0;
41 iOutput_width INTEGER := 4000;
42 cTransaction_Type VARCHAR2(120) := 'INO';
43 cFilename VARCHAR2(30) := NULL;
44 dTransaction_date DATE;
45 cCommunication_Method VARCHAR2(120) := 'EDI';
46 cHeader_Interface VARCHAR2(120) := 'ECE_AR_TRX_HEADERS';
47 cHeader_1_Interface VARCHAR2(120) := 'ECE_AR_TRX_HEADER_1';
48 cAlw_chg_Interface VARCHAR2(120) := 'ECE_AR_TRX_ALLOWANCE_CHARGES';
49 cLine_Interface VARCHAR2(120) := 'ECE_AR_TRX_LINES';
50 cLine_t_Interface VARCHAR2(120) := 'ECE_AR_TRX_LINE_TAX';
51 l_line_text VARCHAR2(2000);
52 uFile_type utl_file.file_type;
53 cCreate_Date_From DATE := TO_DATE(cCDate_From,'YYYY/MM/DD HH24:MI:SS');
54 cCreate_Date_To DATE := TO_DATE(cCDate_To,'YYYY/MM/DD HH24:MI:SS') + 1;
55 cExport_Type VARCHAR2(30) := 'INVOICE';
56 cEnabled VARCHAR2(1) := 'Y';
57 ece_transaction_disabled EXCEPTION;
58
59 CURSOR c_output IS
60 SELECT text
61 FROM ece_output
62 WHERE run_id = iRun_id
63 ORDER BY line_id;
64
65 BEGIN
66 ec_debug.enable_debug(cdebug_mode);
67 ec_debug.push('ECE_AR_TRANSACTION.EXTRACT_INO_OUTBOUND');
68 ec_debug.pl(3,'cOutputPath: ' ,cOutputPath);
69 ec_debug.pl(3,'cOutput_Filename: ' ,cOutput_Filename);
70 ec_debug.pl(3,'cCDate_From: ' ,cCDate_From);
71 ec_debug.pl(3,'cCDate_To: ' ,cCDate_To);
72 ec_debug.pl(3,'cCustomer_Name: ' ,cCustomer_Name);
73 ec_debug.pl(3,'cSite_Use_Code: ' ,cSite_Use_Code);
74 ec_debug.pl(3,'cDocument_Type: ' ,cDocument_Type);
75 ec_debug.pl(3,'cTransaction_Number: ',cTransaction_Number);
76 ec_debug.pl(3,'cdebug_mode: ' ,cdebug_mode);
77
78 /* Check to see if the transaction is enabled. If not, abort */
79 xProgress := 'INO-10-1000';
80 fnd_profile.get('ECE_' || cTransaction_Type || '_ENABLED',cEnabled);
81
82 xProgress := 'INO-10-1005';
83 IF cEnabled = 'N' THEN
84 xProgress := 'INO-10-1010';
85 RAISE ece_transaction_disabled;
86 END IF;
87
88 xProgress := 'INO-10-1015';
89 BEGIN
90 SELECT ece_output_runs_s.NEXTVAL INTO iRun_id
91 FROM DUAL;
92
93 EXCEPTION
94 WHEN NO_DATA_FOUND THEN
95 ec_debug.pl(1,
96 'EC',
97 'ECE_GET_NEXT_SEQ_FAILED',
98 'PROGRESS_LEVEL',
99 xProgress,
100 'SEQ',
101 'ECE_OUTPUT_RUNS_S');
102
103 END;
104 ec_debug.pl(3,'iRun_id: ',iRun_id);
105
106 xProgress := 'INO-10-1020';
107 ec_debug.pl(0,'EC','ECE_RUN_ID','RUN_ID',iRun_id);
108
109 xProgress := 'INO-10-1030';
110 BEGIN
111 SELECT SYSDATE INTO dTransaction_date
112 FROM DUAL;
113
114 EXCEPTION
115 WHEN NO_DATA_FOUND THEN
116 ec_debug.pl(1,
117 'EC',
118 'ECE_GET_SYSDATE_FAILED',
119 'PROGRESS_LEVEL',
120 xProgress,
121 'TABLE_NAME',
122 'DUAL');
123
124 END;
125 ec_debug.pl(3,'dTransaction_date: ',dTransaction_date);
126
127 xProgress := 'INO-10-1040';
128 ece_ar_transaction.populate_ar_trx(
129 cCommunication_Method,
130 cTransaction_Type,
131 iOutput_width,
132 dTransaction_date,
133 iRun_id,
134 cHeader_Interface,
135 cHeader_1_Interface,
136 cAlw_chg_Interface,
137 cLine_Interface,
138 cLine_t_Interface,
139 cCreate_Date_From,
140 cCreate_Date_To,
141 cCustomer_Name,
142 cSite_Use_Code,
143 cDocument_Type,
144 cTransaction_Number);
145
146 xProgress := 'INO-10-1043';
147 BEGIN
148 SELECT COUNT(*) INTO xHeaderCount
149 FROM ece_ar_trx_headers
150 WHERE run_id = iRun_id;
151
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 ec_debug.pl(1,
155 'EC',
156 'ECE_GET_COUNT_FAILED',
157 'PROGRESS_LEVEL',
158 xProgress,
159 'TABLE_NAME',
160 'ECE_OUTPUT');
161
162 END;
163 ec_debug.pl(3,'xHeaderCount: ',xHeaderCount);
164
165 xProgress := 'INO-10-1045';
166 ec_debug.pl(0,'EC','ECE_TRANSACTIONS_PROCESSED','NUMBER_OF_TRX',xHeaderCount);
167
168 xProgress := 'INO-10-1050';
169 ece_ar_transaction.put_data_to_output_table(
170 cCommunication_Method,
171 cTransaction_Type,
172 iOutput_width,
173 iRun_id,
174 cHeader_Interface,
175 cHeader_1_Interface,
176 cAlw_chg_Interface,
177 cLine_Interface,
178 cLine_t_Interface);
179
180 -- Allow users to enter a null value for filename. If it is null,
181 -- generate a unique filename. This is to handle the ability to setup
182 -- transaction to run automatically on a periodic basis (i.e. daily)
183 -- in SRS.
184 IF cOutput_Filename IS NULL THEN
185 cFilename := 'INO' || iRun_id || '.dat';
186 ELSE
187 cFilename := cOutput_Filename;
188 END IF;
189
190 -- Open the file for write.
191 xProgress := 'INO-10-1060';
192
193 -- Open the cursor to select the actual file output from ece_output.
194 xProgress := 'INO-10-1070';
195 OPEN c_output;
196 LOOP
197 FETCH c_output INTO l_line_text;
198 ec_debug.pl(3,'l_line_text: ',l_line_text);
199 if (c_output%ROWCOUNT > 0) then
200 if (NOT utl_file.is_open(uFile_type)) then
201 uFile_type := utl_file.fopen(cOutputPath,
202 cFilename,
203 'W');
204 end if;
205 end if;
206 EXIT WHEN c_output%NOTFOUND;
207
208 -- Write the data from ece_output to the output file.
209 xProgress := 'INO-10-1080';
210 utl_file.put_line(uFile_type,
211 l_line_text);
212
213 END LOOP;
214
215 CLOSE c_output;
216
217 -- Close the output file.
218 xProgress := 'INO-10-1090';
219 if (utl_file.is_open(uFile_type)) then
220 utl_file.fclose(uFile_type);
221 end if;
222
223 -- Assume everything went ok so delete the records from ece_output.
224 xProgress := 'INO-10-1100';
225 DELETE FROM ece_output
226 WHERE run_id = iRun_id;
227
228 IF SQL%NOTFOUND THEN
229 ec_debug.pl(1,
230 'EC',
231 'ECE_NO_ROW_DELETED',
232 'PROGRESS_LEVEL',
233 xProgress,
234 'TABLE_NAME',
235 'ECE_OUTPUT' );
236 END IF;
237
238 -- Check if the Automotive Module is installed and if it is installed,
239 -- export the file created to Radley Caras
240 xProgress := 'INO-10-1110';
241 l_automotive_installed := fnd_installation.get_app_info('VEH',
242 l_status,
243 l_industry,
244 l_schema);
245 l_automotive_status := l_status;
246 ec_debug.pl(3,'l_automotive_status: ',l_automotive_status);
247
248 IF l_automotive_status = 'I' THEN
249 xProgress := 'INO-10-1120';
250 iRequestID := fnd_request.submit_request(Application => 'VEH',
251 Program => 'VEH_DSN_IMPORT',
252 Description => 'Start Radley CARaS script to Import DSN',
253 Start_Time => NULL,
254 Sub_Request => FALSE,
255 Argument1 => cExport_Type,
256 Argument2 => cOutputPath,
257 Argument3 => cFilename );
258 END IF;
259
260 IF ec_mapping_utils.ec_get_trans_upgrade_status(cTransaction_Type) = 'U' THEN
261 ec_debug.pl(0,'EC','ECE_REC_TRANS_PENDING',NULL);
262 retcode := 1;
263 END IF;
264
265 ec_debug.pop('ECE_AR_TRANSACTION.EXTRACT_INO_OUTBOUND');
266 ec_debug.disable_debug;
267
268 COMMIT;
269
270 EXCEPTION
271 WHEN ece_transaction_disabled THEN
272 ec_debug.pl(0,'EC','ECE_TRANSACTION_DISABLED','TRANSACTION',cTransaction_type);
273 retcode := '1';
274 ec_debug.disable_debug;
275 ROLLBACK;
276
277 WHEN utl_file.write_error THEN
278 ec_debug.pl(0,'EC','ECE_UTL_WRITE_ERROR',NULL);
279 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
280
281 retcode := 2;
282 ec_debug.disable_debug;
283 ROLLBACK;
284
285 WHEN utl_file.invalid_path THEN
286 ec_debug.pl(0,'EC','ECE_UTIL_INVALID_PATH',NULL);
287 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
288
289 retcode := 2;
290 ec_debug.disable_debug;
291 ROLLBACK;
292
293 WHEN utl_file.invalid_operation THEN
294 ec_debug.pl(0,'EC','ECE_UTIL_INVALID_OPERATION',NULL);
295 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
296
297 retcode := 2;
298 ec_debug.disable_debug;
299 ROLLBACK;
300
301 WHEN OTHERS THEN
302 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
303 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
304
305 retcode := 2;
306 ec_debug.disable_debug;
307 ROLLBACK;
308
309 END extract_ino_outbound;
310
311 PROCEDURE Define_Interface_Column ( c IN INTEGER,
312 col IN VARCHAR,
313 col_size IN INTEGER,
314 tbl IN ece_flatfile_pvt.Interface_tbl_type )
315 IS
316
317 i INTEGER := 0;
318
319 BEGIN
320
321 ec_debug.push ( 'ECE_AR_TRANSACTION.DEFINE_INTERFACE_COLUMN' );
322 ec_debug.pl ( 3, 'c: ', c );
323 ec_debug.pl ( 3, 'col: ',col );
324 ec_debug.pl ( 3, 'col_size: ',col_size );
325
326 xProgress := '2000-10';
327 FOR k IN 1..tbl.count
328 LOOP
329 dbms_sql.define_column ( c,
330 k,
331 col,
332 col_size );
333 END LOOP;
334
335 ec_debug.pop('ECE_AR_TRANSACTION.DEFINE_INTERFACE_COLUMN');
336
337 EXCEPTION
338 WHEN OTHERS THEN
339
340 ec_debug.pl ( 0,
341 'EC',
342 'ECE_PROGRAM_ERROR',
343 'PROGRESS_LEVEL',
344 xProgress );
345
346 ec_debug.pl ( 0,
347 'EC',
348 'ECE_ERROR_MESSAGE',
349 'ERROR_MESSAGE',
350 SQLERRM );
351
352 app_exception.raise_exception;
353
354 END Define_Interface_Column;
355
356 PROCEDURE Update_AR ( Document_Type IN VARCHAR2,
357 Transaction_ID IN NUMBER,
358 Installment_Number IN NUMBER,
359 Multiple_Installments_Flag IN VARCHAR2,
360 Maximum_Installment_Number IN NUMBER,
361 Update_Date IN DATE )
362 IS
363
364
365 l_Update_Value VARCHAR2(20);
366 l_EDI_Flag VARCHAR2(1);
367 l_Print_Flag VARCHAR2(1);
368
369 BEGIN
370
371 ec_debug.push('ECE_AR_TRANSACTION.UPDATE_AR');
372 ec_debug.pl ( 3, 'Document_Type: ', Document_Type );
373 ec_debug.pl ( 3, 'Transaction_ID: ', Transaction_ID );
374 ec_debug.pl ( 3, 'Installment_Number: ', Installment_Number );
375 ec_debug.pl ( 3, 'Multiple_Installments_Flag: ',Multiple_Installments_Flag );
376 ec_debug.pl ( 3, 'Maximum_Installment_Number: ',Maximum_Installment_Number );
377 ec_debug.pl ( 3, 'Update_Date: ',Update_Date );
378
379 xProgress := '2000-20';
380 BEGIN /*2945057*/
381 SELECT edi_flag,
382 print_flag
383 INTO l_EDI_flag,
384 l_Print_flag
385 FROM ra_customer_trx rct,
386 ece_tp_details etd,
387 hz_cust_acct_sites rad,
388 hz_cust_site_uses rsu
389 WHERE rct.bill_to_site_use_id = rsu.site_use_id
390 AND rsu.cust_acct_site_id = rad.cust_acct_site_id
391 AND rad.tp_header_id = etd.tp_header_id
392 AND etd.document_type = Update_AR.Document_Type
393 AND rct.customer_trx_id = Update_AR.Transaction_ID;
394 EXCEPTION
395 WHEN NO_DATA_FOUND THEN
396 ec_debug.pl ( 1,
397 'EC',
398 'ECE_NO_ROW_SELECTED',
399 'PROGRESS_LEVEL',
400 xProgress,
401 'INFO',
402 'EDI FLAG, PRINT FLAG',
403 'TABLE_NAME',
404 'RA_CUSTOMER_TRX, ECE_TP_DETAILS, HZ_CUST_ACCT_SITES,HZ_CUST_SITE_USES' );
405 END;
406
407 IF l_EDI_Flag = 'Y' AND
408 l_Print_Flag = 'Y'
409 THEN
410 l_Update_Value := 'EP';
411 END IF;
412
413 IF l_EDI_Flag = 'Y' AND
414 l_Print_Flag <> 'Y'
415 THEN
416 l_Update_Value := 'ED';
417 END IF;
418
419 IF l_EDI_Flag <> 'Y' AND
420 l_Print_Flag = 'Y'
421 THEN
422 l_Update_Value := 'PR';
423 END IF;
424
425 ec_debug.pl ( 3, 'L_UPDATE_VALUE: ',l_Update_Value );
426
427 xProgress := '2010-20';
428 UPDATE ra_customer_trx
429 SET last_update_date = SYSDATE,
430 printing_pending = DECODE (Document_Type,
431 'CM', 'N',
432 'OACM', 'N',
433 DECODE (Maximum_Installment_Number,
434 Installment_Number, 'N',
435 NULL, 'N',
436 1, 'N',
437 'Y')),
438 printing_count = NVL(printing_count,0) + 1,
439 printing_last_printed = SYSDATE,
440 printing_original_date = DECODE (NVL(printing_count,0),
441 0, SYSDATE,
442 printing_original_date ),
443 last_printed_sequence_num = DECODE (Multiple_Installments_Flag,
444 'N',NULL,
445 GREATEST(NVL(last_printed_sequence_num,0),
446 Installment_Number)),
447 edi_processed_flag = 'Y',
448 edi_processed_status = l_Update_Value
449 WHERE customer_trx_id = Update_AR.Transaction_ID;
450
451 IF SQL%NOTFOUND
452 THEN
453 ec_debug.pl (0,
454 'EC',
455 'ECE_NO_ROW_UPDATED',
456 'PROGRESS_LEVEL',
457 xProgress,
458 'INFO',
459 'EDI PROCESSED',
460 'TABLE_NAME',
461 'RA_CUSTOMER_TRX' );
462 END IF;
463
464 /* The following lines were commented out was because of a request
465 from a beta site. Their business practice requires them to
466 print multiple installment invoices at the same time.
467
468 BE AWARE: by doing so, we are removing the data consistency test.
469 */
470 -- AND LAST_UPDATE_DATE = Update_AR.Update_Date;
471
472 -- The join on last_update_date is to ensure that the
473 -- record has not been updated by another user, between
474 -- the select above and the lock created by this update.
475
476 /* IF SQL%NOTFOUND THEN
477 RAISE_APPLICATION_ERROR(-20000,'Record changed by another user.');
478 END IF;
479 */
480
481 ec_debug.pop('ECE_AR_TRANSACTION.UPDATE_AR');
482 EXCEPTION
483 WHEN OTHERS THEN
484
485 ec_debug.pl ( 0,
486 'EC',
487 'ECE_PROGRAM_ERROR',
488 'PROGRESS_LEVEL',
489 xProgress );
490
491 ec_debug.pl ( 0,
492 'EC',
493 'ECE_ERROR_MESSAGE',
494 'ERROR_MESSAGE',
495 SQLERRM );
496
497 app_exception.raise_exception;
498
499 END Update_AR;
500
501 PROCEDURE Get_Remit_Address ( Customer_Trx_ID IN NUMBER,
502 Remit_To_Address1 OUT NOCOPY VARCHAR2,
503 Remit_To_Address2 OUT NOCOPY VARCHAR2,
504 Remit_To_Address3 OUT NOCOPY VARCHAR2,
505 Remit_To_Address4 OUT NOCOPY VARCHAR2,
506 Remit_To_City OUT NOCOPY VARCHAR2,
507 Remit_To_County OUT NOCOPY VARCHAR2,
508 Remit_To_State OUT NOCOPY VARCHAR2,
509 Remit_To_Province OUT NOCOPY VARCHAR2,
510 Remit_To_Country OUT NOCOPY VARCHAR2,
511 Remit_To_Code_Int OUT NOCOPY VARCHAR2,
512 Remit_To_Postal_Code OUT NOCOPY VARCHAR2,
513 Remit_To_Customer_Name OUT NOCOPY VARCHAR2, --2291130
514 Remit_To_Edi_Location_Code OUT NOCOPY VARCHAR2)
515 IS
516
517 dummy NUMBER;
518
519 BEGIN
520
521 ec_debug.push('ECE_AR_TRANSACTION.GET_REMIT_ADDRESS');
522 ec_debug.pl ( 3, 'customer_trx_id: ', Customer_Trx_ID );
523
524 xProgress := '2000-30';
525 BEGIN
526 SELECT remit_to_address_id INTO l_Remit_To_Address_ID
527 FROM RA_CUSTOMER_TRX
528 WHERE CUSTOMER_TRX_ID = Get_Remit_Address.Customer_Trx_ID;
529 EXCEPTION
530 WHEN NO_DATA_FOUND THEN
531 ec_debug.pl ( 1,
532 'EC',
533 'ECE_NO_ROW_SELECTED',
534 'PROGRESS_LEVEL',
535 xProgress,
536 'INFO',
537 'REMIT TO ADDRESS ID',
538 'TABLE_NAME',
539 'RA_CUSTOMER_TRX' );
540 END;
541
542 ec_debug.pl ( 3, 'l_Remit_To_Address_ID: ', l_Remit_To_Address_ID );
543
544 IF l_Remit_To_Address_ID IS NULL
545 THEN
546
547 DECLARE
548
549 CURSOR remit_cur IS
550 SELECT rt.address_id
551 FROM ra_customer_trx rct,
552 hz_cust_acct_sites a,
553 hz_party_sites hps,
554 hz_locations loc,
555 ra_remit_tos rt
556 WHERE rct.customer_trx_id = Get_Remit_Address.Customer_Trx_ID
557 AND rct.bill_to_address_id = a.cust_acct_site_id AND
558 a.party_site_id = hps.party_site_id AND
559 hps.location_id = loc.location_id
560 AND rt.status = 'A'
561 AND NVL(a.status,'A') = 'A'
562 AND rt.country = loc.COUNTRY
563 AND ( loc.state = NVL(rt.state, loc.state)
564 OR ( loc.state IS NULL
565 AND rt.state IS NULL
566 )
567 OR ( loc.state IS NULL
568 AND loc.postal_code <= NVL(rt.postal_code_high, loc.postal_code)
569 AND loc.postal_code >= NVL(rt.postal_code_low, loc.postal_code)
570 AND ( postal_code_low IS NOT NULL
571 OR postal_code_high IS NOT NULL
572 )
573 )
574 )
575 AND ( ( loc.postal_code <= NVL(rt.postal_code_high, loc.postal_code)
576 AND loc.postal_code >= NVL(rt.postal_code_low, loc.postal_code)
577 )
578 OR ( loc.postal_code IS NULL
579 AND rt.postal_code_low IS NULL
580 AND rt.postal_code_high IS NULL
581 )
582 )
583 ORDER BY rt.state,
584 rt.postal_code_low,
585 rt.postal_code_high;
586
587 BEGIN
588
589 -- We only want the first record from the select since the
590 -- order by puts the records in a special order
591
592 xProgress := '2010-30';
593 OPEN remit_cur;
594
595 FETCH remit_cur INTO l_Remit_To_Address_ID;
596 ec_debug.pl (3, 'l_Remit_To_Address_ID: ', l_Remit_To_Address_ID);
597
598 IF remit_cur%NOTFOUND THEN
599 l_Remit_To_Address_ID := NULL;
600 END IF;
601
602 CLOSE remit_cur;
603
604 END;
605
606 END IF;
607
608 IF l_Remit_To_Address_ID IS NULL
609 THEN
610 xProgress := '2020-30';
611 BEGIN
612 SELECT MIN(address_id)
613 INTO l_Remit_To_Address_ID
614 FROM ra_remit_tos
615 WHERE status = 'A'
616 AND state = 'DEFAULT'
617 AND country = 'DEFAULT';
618 EXCEPTION
619 WHEN NO_DATA_FOUND THEN
620 ec_debug.pl ( 1,
621 'EC',
622 'ECE_NO_ROW_SELECTED',
623 'PROGRESS_LEVEL',
624 xProgress,
625 'INFO',
626 'MINIMUM ADDRESS ID',
627 'TABLE_NAME',
628 'RA_REMIT_TOS' );
629 END;
630
631 ec_debug.pl ( 3, 'l_Remit_To_Address_ID: ', l_Remit_To_Address_ID );
632
633 END IF;
634
635 xProgress := '2030-30';
636 BEGIN
637 SELECT loc.address1,
638 loc.address2,
639 loc.address3,
640 loc.address4,
641 loc.city,
642 loc.county,
643 loc.state,
644 loc.province,
645 loc.country,
646 loc.postal_code,
647 hcas.orig_system_reference,
648 substr(loc.address_lines_phonetic,1,50), --2291130
649 hcas.ece_tp_location_code --2386848
650 INTO Remit_To_Address1,
651 Remit_To_Address2,
652 Remit_To_Address3,
653 Remit_To_Address4,
654 Remit_To_City,
655 Remit_To_County,
656 Remit_to_state,
657 Remit_To_Province,
658 Remit_To_Country,
659 Remit_To_Postal_Code,
660 Remit_To_Code_Int,
661 Remit_to_customer_name, --2291130
662 Remit_to_edi_location_code
663 FROM hz_cust_acct_sites hcas,
664 hz_party_sites hps,
665 hz_locations loc
666 WHERE hps.party_site_id = hcas.party_site_id
667 AND hps.location_id = loc.location_id
668 AND hcas.cust_acct_site_id = l_Remit_To_Address_ID;
669 -- bug 4718847
670 EXCEPTION
671 WHEN NO_DATA_FOUND THEN
672 ec_debug.pl ( 1,
673 'EC',
674 'ECE_NO_ROW_SELECTED',
675 'PROGRESS_LEVEL',
676 xProgress,
677 'INFO',
678 'RA ADDRESS',
679 'TABLE_NAME',
680 'HZ_CUST_ACCT_SITES' );
681 END;
682
683 ec_debug.pop('ECE_AR_TRANSACTION.GET_REMIT_ADDRESS');
684
685 EXCEPTION
686 WHEN OTHERS THEN
687
688 ec_debug.pl ( 0,
689 'EC',
690 'ECE_PROGRAM_ERROR',
691 'PROGRESS_LEVEL',
692 xProgress );
693
694 ec_debug.pl ( 0,
695 'EC',
696 'ECE_ERROR_MESSAGE',
697 'ERROR_MESSAGE',
698 SQLERRM );
699
700 app_exception.raise_exception;
701
702 END Get_Remit_Address;
703
704 PROCEDURE Get_Payment ( Customer_Trx_ID IN NUMBER,
705 Installment_Number IN NUMBER,
706 Multiple_Installments_Flag OUT NOCOPY VARCHAR2,
707 Maximum_Installment_Number OUT NOCOPY NUMBER,
708 Amount_Tax_Due OUT NOCOPY NUMBER,
709 Amount_Charges_Due OUT NOCOPY NUMBER,
710 Amount_Freight_Due OUT NOCOPY NUMBER,
711 Amount_Line_Items_Due OUT NOCOPY NUMBER,
712 Total_Amount_Due OUT NOCOPY NUMBER )
713
714 IS
715
716 l_Term_ID NUMBER;
717 l_Payment_Schedule_Exists VARCHAR2(1);
718 l_Term_Base_Amount NUMBER;
719 l_Term_Relative_Amount NUMBER;
720 l_Minimum_Installment_Number NUMBER;
721 l_Amount_Tax_Due NUMBER;
722 l_Amount_Charges_Due NUMBER;
723 l_Amount_Freight_Due NUMBER;
724 l_Amount_Line_Items_Due NUMBER;
725 l_First_Installment_Code VARCHAR2(30);
726 l_Type VARCHAR2(30);
727 l_Currency_Precision NUMBER;
728
729 -- This procedure gets the amount due/credited for a paricular installment
730 -- of an Invoice or Credit Memo (or any of the related documents)
731
732 BEGIN
733
734 ec_debug.push ( 'ece_ar_transaction.Get_Payment' );
735 ec_debug.pl ( 3, 'Customer_Trx_ID: ', Customer_Trx_ID );
736
737 -- This select statement is used to determine whether this transaction
738 -- has a payment_schedule. If it does we can get all of the information
739 -- we need directly from the payment_schedule, else we need to derive it
740 -- from the payment term.
741
742 xProgress := '2000-40';
743 BEGIN
744 SELECT rct.term_id,
745 fc.precision,
746 rctt.accounting_affect_flag,
747 rctt.type,
748 rt.first_installment_code,
749 DECODE(rctt.type,
750 'CM', 'N',
751 'OACM', 'N',
752 DECODE(COUNT(*),
753 0, 'N',
754 1, 'N',
755 'Y')),
756 MAX(rtl.sequence_num),
757 MIN(rtl.sequence_num)
758 INTO l_Term_ID,
759 l_Currency_Precision,
760 l_Payment_Schedule_Exists,
761 l_Type,
762 l_First_Installment_Code,
763 Multiple_Installments_Flag,
764 Maximum_Installment_Number,
765 l_Minimum_Installment_Number
766 FROM ra_customer_trx rct,
767 ra_cust_trx_types rctt,
768 ra_terms_lines rtl,
769 ra_terms rt,
770 fnd_currencies fc
771 WHERE rct.customer_trx_id = Get_Payment.Customer_Trx_ID
772 AND rct.invoice_currency_code = fc.currency_code
773 AND rct.cust_trx_type_id = rctt.cust_trx_type_id
774 AND rct.term_id = rt.term_id (+)
775 AND rt.term_id = rtl.term_id (+)
776 GROUP BY rct.term_id,
777 fc.precision,
778 rctt.accounting_affect_flag,
779 rctt.type,
780 rt.first_installment_code;
781 EXCEPTION
782 WHEN NO_DATA_FOUND THEN
783 ec_debug.pl ( 1,
784 'EC',
785 'ECE_NO_ROW_SELECTED',
786 'PROGRESS_LEVEL',
787 xProgress,
788 'INFO',
789 'PAYMENT SCHEDULE',
790 'TABLE_NAME',
791 'RA_CUSTOMER_TRX, RA_CUST_TRX_TYPES, RA_TERMS_LINES, RA_TERMS, FND_CURRENCIES' );
792 END;
793
794 ec_debug.pl ( 3, 'l_Term_ID: ', l_Term_ID );
795 ec_debug.pl ( 3, 'l_Currency_Precision: ', l_Currency_Precision );
796 ec_debug.pl ( 3, 'l_Payment_Schedule_Exists: ', l_Payment_Schedule_Exists );
797 ec_debug.pl ( 3, 'l_Type: ', l_Type );
798 ec_debug.pl ( 3, 'l_First_Installment_Code: ', l_First_Installment_Code );
799 ec_debug.pl ( 3, 'Multiple_Installments_Flag: ', Multiple_Installments_Flag );
800 ec_debug.pl ( 3, 'Maximum_Installment_Number: ', Maximum_Installment_Number );
801 ec_debug.pl ( 3, 'l_Minimum_Installment_Number: ', l_Minimum_Installment_Number );
802
803 xProgress := '2010-40';
804 BEGIN
805 SELECT NVL(MIN(rtl.relative_amount),1),
806 NVL(MIN(rt.base_amount),1)
807 INTO l_Term_Relative_Amount,
808 l_Term_Base_Amount
809 FROM ra_terms rt,
810 ra_terms_lines rtl
811 WHERE rt.term_id = l_Term_ID
812 AND rt.term_id = rtl.term_id
813 AND rtl.sequence_num = Get_Payment.Installment_Number;
814 EXCEPTION
815 WHEN NO_DATA_FOUND THEN
816 ec_debug.pl ( 1,
817 'EC',
818 'ECE_NO_ROW_SELECTED',
819 'PROGRESS_LEVEL',
820 xProgress,
821 'INFO',
822 'AMOUNT',
823 'TABLE_NAME',
824 'RA_TERMS, RA_TERMS_LINES' );
825 END;
826
827 ec_debug.pl ( 3, 'l_Term_Relative_Amount: ', l_Term_Relative_Amount );
828 ec_debug.pl ( 3, 'l_Term_Base_Amount: ', l_Term_Base_Amount );
829
830 IF l_Payment_Schedule_Exists = 'Y'
831 THEN
832 xProgress := '2020-40';
833 BEGIN
834 SELECT NVL(tax_original,0),
835 NVL(freight_original,0),
836 NVL(amount_line_items_original,0),
837 NVL(amount_due_original,0)
838 INTO Amount_Tax_Due,
839 Amount_Freight_Due,
840 Amount_Line_Items_Due,
841 Total_Amount_Due
842 FROM ar_payment_schedules
843 WHERE customer_trx_id = Get_Payment.Customer_Trx_ID
844 AND DECODE(l_Type,
845 'CM', Get_Payment.Installment_Number,
846 'OACM', Get_Payment.Installment_Number,
847 NVL(terms_sequence_number,
848 Get_Payment.Installment_Number)) = Get_Payment.Installment_Number;
849 EXCEPTION
850 WHEN NO_DATA_FOUND THEN
851 ec_debug.pl ( 1,
852 'EC',
853 'ECE_NO_ROW_SELECTED',
854 'PROGRESS_LEVEL',
855 xProgress,
856 'INFO',
857 'PAYMENT SCHEDULE',
858 'TABLE_NAME',
859 'AR_PAYMENT_SCHEDULES' );
860 END;
861
862 ec_debug.pl ( 3, 'Amount_Tax_Due: ', Amount_Tax_Due );
863 ec_debug.pl ( 3, 'Amount_Freight_Due: ', Amount_Freight_Due );
864 ec_debug.pl ( 3, 'Amount_Line_Items_Due: ', Amount_Line_Items_Due );
865 ec_debug.pl ( 3, 'Total_Amount_Due: ', Total_Amount_Due );
866
867 xProgress := '2030-40';
868 BEGIN
869 SELECT NVL(SUM((NVL(rctl.quantity_invoiced,
870 rctl.quantity_credited) *
871 rctl.unit_selling_price) *
872 l_Term_Relative_Amount /
873 l_Term_Base_Amount),
874 0)
875 INTO Amount_Charges_Due
876 FROM ra_customer_trx_lines rctl
877 WHERE rctl.customer_trx_id = Get_Payment.Customer_Trx_ID
878 AND rctl.line_type = 'CHARGES';
879 EXCEPTION
880 WHEN NO_DATA_FOUND THEN
881 ec_debug.pl ( 1,
882 'EC',
883 'ECE_NO_ROW_SELECTED',
884 'PROGRESS_LEVEL',
885 xProgress,
886 'INFO',
887 'CHARGE AMOUNT DUE',
888 'TABLE_NAME',
889 'RA_CUSTOMER_TRX_LINES' );
890 END;
891
892 ec_debug.pl ( 3, 'Amount_Charges_Due: ', Amount_Charges_Due );
893
894 ELSE
895
896 -- There isn't any payment_schedule, so we need to get the information by
897 -- summing up the tax, freight and lines and then applying the payment
898 -- term, currency precision and if tax/freight are prorated
899
900 xProgress := '2040-40';
901 BEGIN
902 SELECT ROUND(SUM(extended_amount *
903 l_Term_Relative_Amount /
904 l_Term_Base_Amount),
905 l_Currency_Precision)
906 INTO l_Amount_Line_Items_Due
907 FROM ra_customer_trx_lines
908 WHERE customer_trx_id = Get_Payment.Customer_Trx_ID
909 AND line_type NOT IN ('TAX','FREIGHT','CHARGES');
910 EXCEPTION
911 WHEN NO_DATA_FOUND THEN
912 ec_debug.pl ( 1,
913 'EC',
914 'ECE_NO_ROW_SELECTED',
915 'PROGRESS_LEVEL',
916 xProgress,
917 'INFO',
918 'LINE ITEM AMOUNT DUE',
919 'TABLE_NAME',
920 'RA_CUSTOMER_TRX_LINES' );
921 END;
922
923 ec_debug.pl ( 3, 'l_Amount_Line_Items_Due: ', l_Amount_Line_Items_Due );
924
925 xProgress := '2050-40';
926 BEGIN
927 SELECT ROUND(SUM(extended_amount *
928 l_Term_Relative_Amount /
929 l_Term_Base_Amount),
930 l_Currency_Precision)
931 INTO l_Amount_Charges_Due
932 FROM ra_customer_trx_lines
933 WHERE customer_trx_id = Get_Payment.Customer_Trx_ID
934 AND line_type = 'CHARGES';
935 EXCEPTION
936 WHEN NO_DATA_FOUND THEN
937 ec_debug.pl ( 1,
938 'EC',
939 'ECE_NO_ROW_SELECTED',
940 'PROGRESS_LEVEL',
941 xProgress,
942 'INFO',
943 'CHARGE AMOUNT DUE',
944 'TABLE_NAME',
945 'RA_CUSTOMER_TRX_LINES' );
946 END;
947
948 ec_debug.pl ( 3, 'l_Amount_Charges_Due: ', l_Amount_Charges_Due );
949
950 -- Check to see if the tax/freight are prorated across installments
951 -- or if they are simply included on the first installment.
952
953 xProgress := '2060-40';
954 IF l_First_Installment_Code = 'INCLUDE'
955 THEN
956 xProgress := '2070-40';
957 IF l_Minimum_Installment_Number = Get_Payment.Installment_Number
958 THEN
959
960 xProgress := '2080-40';
961 BEGIN
962 SELECT SUM(extended_amount)
963 INTO l_Amount_Tax_Due
964 FROM ra_customer_trx_lines
965 WHERE customer_trx_id = Get_Payment.Customer_Trx_ID
966 AND line_type = 'TAX';
967 EXCEPTION
968 WHEN NO_DATA_FOUND THEN
969 ec_debug.pl ( 1,
970 'EC',
971 'ECE_NO_ROW_SELECTED',
972 'PROGRESS_LEVEL',
973 xProgress,
974 'INFO',
975 'TAX AMOUNT DUE',
976 'TABLE_NAME',
977 'RA_CUSTOMER_TRX_LINES' );
978 END;
979
980 ec_debug.pl (3, 'l_Amount_Tax_Due: ', l_Amount_Tax_Due);
981
982 xProgress := '2090-40';
983 BEGIN
984 SELECT SUM(extended_amount)
985 INTO l_Amount_Freight_Due
986 FROM ra_customer_trx_lines
987 WHERE customer_trx_id = Get_Payment.Customer_Trx_ID
988 AND line_type = 'FREIGHT';
989 EXCEPTION
990 WHEN NO_DATA_FOUND THEN
991 ec_debug.pl ( 1,
992 'EC',
993 'ECE_NO_ROW_SELECTED',
994 'PROGRESS_LEVEL',
995 xProgress,
996 'INFO',
997 'FREIGHT AMOUNT DUE',
998 'TABLE_NAME',
999 'RA_CUSTOMER_TRX_LINES' );
1000 END;
1001
1002 ec_debug.pl (3, 'l_Amount_Freight_Due: ', l_Amount_Freight_Due);
1003
1004 ELSE
1005
1006 l_Amount_Tax_Due := 0;
1007 l_Amount_Freight_Due := 0;
1008
1009 END IF;
1010
1011 ELSE
1012
1013 xProgress := '2100-40';
1014 BEGIN
1015 SELECT ROUND(SUM(extended_amount *
1016 l_Term_Relative_Amount /
1017 l_Term_Base_Amount),
1018 l_Currency_Precision)
1019 INTO l_Amount_Tax_Due
1020 FROM ra_customer_trx_lines
1021 WHERE customer_trx_id = Get_Payment.Customer_Trx_ID
1022 AND line_type = 'TAX';
1023 EXCEPTION
1024 WHEN NO_DATA_FOUND THEN
1025 ec_debug.pl ( 1,
1026 'EC',
1027 'ECE_NO_ROW_SELECTED',
1028 'PROGRESS_LEVEL',
1029 xProgress,
1030 'INFO',
1031 'TAX AMOUNT DUE',
1032 'TABLE_NAME',
1033 'RA_CUSTOMER_TRX_LINES' );
1034 END;
1035
1036 ec_debug.pl ( 3, 'l_Amount_Tax_Due: ', l_Amount_Tax_Due );
1037
1038 xProgress := '2110-40';
1039 BEGIN
1040 SELECT ROUND(SUM(extended_amount *
1041 l_Term_Relative_Amount /
1042 l_Term_Base_Amount),
1043 l_Currency_Precision)
1044 INTO l_Amount_Freight_Due
1045 FROM ra_customer_trx_lines
1046 WHERE customer_trx_id = Get_Payment.Customer_Trx_ID
1047 AND line_type = 'FREIGHT';
1048 EXCEPTION
1049 WHEN NO_DATA_FOUND THEN
1050 ec_debug.pl ( 1,
1051 'EC',
1052 'ECE_NO_ROW_SELECTED',
1053 'PROGRESS_LEVEL',
1054 xProgress,
1055 'INFO',
1056 'FREIGHT AMOUNT DUE',
1057 'TABLE_NAME',
1058 'RA_CUSTOMER_TRX_LINES' );
1059 END;
1060
1061 ec_debug.pl ( 3, 'l_Amount_Freight_Due: ', l_Amount_Freight_Due );
1062
1063 END IF;
1064
1065 -- Total up the values and assign them to the out parameters.
1066
1067 xProgress := '2120-40';
1068 Total_Amount_Due := l_Amount_Tax_Due +
1069 l_Amount_Freight_Due +
1070 l_Amount_Charges_Due +
1071 l_Amount_Line_items_Due;
1072 Amount_Tax_Due := NVL(l_Amount_Tax_Due,0);
1073 Amount_Charges_Due := NVL(l_Amount_Charges_Due,0);
1074 Amount_Freight_Due := NVL(l_Amount_Freight_Due,0);
1075 Amount_Line_Items_Due := NVL(l_Amount_Line_Items_Due,0);
1076
1077 ec_debug.pl ( 3, 'Total_Amount_Due: ', Total_Amount_Due );
1078 ec_debug.pl ( 3, 'Amount_Tax_Due: ', Amount_Tax_Due );
1079 ec_debug.pl ( 3, 'Amount_Charges_Due: ', Amount_Charges_Due );
1080 ec_debug.pl ( 3, 'Amount_Freight_Due: ', Amount_Freight_Due );
1081 ec_debug.pl ( 3, 'Amount_Line_Items_Due: ', Amount_Line_Items_Due );
1082
1083 END IF;
1084
1085
1086 ec_debug.pop ( 'ece_ar_transaction.Get_Payment' );
1087
1088 EXCEPTION
1089 WHEN OTHERS THEN
1090
1091 ec_debug.pl ( 0,
1092 'EC',
1093 'ECE_PROGRAM_ERROR',
1094 'PROGRESS_LEVEL',
1095 xProgress );
1096
1097 ec_debug.pl ( 0,
1098 'EC',
1099 'ECE_ERROR_MESSAGE',
1100 'ERROR_MESSAGE',
1101 SQLERRM );
1102
1103 app_exception.raise_exception;
1104
1105 END Get_Payment;
1106
1107
1108 -- The following procedure gets the discount information
1109 -- for the term being used. The discount info is a sub-table
1110 -- off of terms, this procedure will get the first three
1111 -- discounts, this is a denormalization, but is being used
1112 -- to avoid the overhead of another level of data.
1113 -- Also it is assumed that Credit Memo types (CM and OACM) do not have
1114 -- payment terms information, even though they mat have a payment term
1115
1116 --Bug 2389231 Added a new column Invoice_date.
1117
1118 PROCEDURE Get_Term_Discount ( Document_Type IN VARCHAR2,
1119 Term_ID IN NUMBER,
1120 Term_Sequence_Number IN NUMBER,
1121 Invoice_date IN DATE,
1122 Discount_Percent1 OUT NOCOPY NUMBER,
1123 Discount_Days1 OUT NOCOPY NUMBER,
1124 Discount_Date1 OUT NOCOPY DATE,
1125 Discount_Day_Of_Month1 OUT NOCOPY NUMBER,
1126 Discount_Months_Forward1 OUT NOCOPY NUMBER,
1127 Discount_Percent2 OUT NOCOPY NUMBER,
1128 Discount_Days2 OUT NOCOPY NUMBER,
1129 Discount_Date2 OUT NOCOPY DATE,
1130 Discount_Day_Of_Month2 OUT NOCOPY NUMBER,
1131 Discount_Months_Forward2 OUT NOCOPY NUMBER,
1132 Discount_Percent3 OUT NOCOPY NUMBER,
1133 Discount_Days3 OUT NOCOPY NUMBER,
1134 Discount_Date3 OUT NOCOPY DATE,
1135 Discount_Day_Of_Month3 OUT NOCOPY NUMBER,
1136 Discount_Months_Forward3 OUT NOCOPY NUMBER )
1137 IS
1138
1139 CURSOR discount IS
1140 SELECT discount_percent,
1141 discount_days,
1142 nvl(discount_date,Get_Term_Discount.Invoice_date + discount_days), --Bug 2389231
1143 discount_day_of_month,
1144 discount_months_forward
1145 FROM ra_terms_lines_discounts
1146 WHERE term_id = Get_Term_Discount.Term_ID
1147 AND sequence_num = Get_Term_Discount.Term_Sequence_Number;
1148
1149 l_Counter NUMBER DEFAULT 1;
1150 l_Discount_Percent NUMBER;
1151 l_Discount_Days NUMBER;
1152 l_Discount_Date DATE;
1153 l_Discount_Day_Of_Month NUMBER;
1154 l_Discount_Months_Forward NUMBER;
1155
1156
1157 BEGIN
1158
1159 ec_debug.push ( 'ece_ar_transaction.Get_Term_Discount' );
1160 ec_debug.pl ( 3, 'Document_Type: ', Document_Type );
1161 ec_debug.pl ( 3, 'Term_ID: ',Term_ID );
1162 ec_debug.pl ( 3, 'Term_Sequence_Number: ',Term_Sequence_Number );
1163
1164 xProgress := '2000-50';
1165 IF get_term_discount.Document_Type IN ('CM','OACM')
1166 THEN
1167
1168 Discount_Percent1 := NULL;
1169 Discount_Days1 := NULL;
1170 Discount_Date1 := NULL;
1171 Discount_Day_Of_Month1 := NULL;
1172 Discount_Months_Forward1 := NULL;
1173 Discount_Percent2 := NULL;
1174 Discount_Days2 := NULL;
1175 Discount_Date2 := NULL;
1176 Discount_Day_Of_Month2 := NULL;
1177 Discount_Months_Forward2 := NULL;
1178 Discount_Percent3 := NULL;
1179 Discount_Days3 := NULL;
1180 Discount_Date3 := NULL;
1181 Discount_Day_Of_Month3 := NULL;
1182 Discount_Months_Forward3 := NULL;
1183
1184 ELSE
1185 xProgress := '2010-50';
1186 OPEN discount;
1187
1188 LOOP
1189 xProgress := '2020-50';
1190 FETCH discount
1191 INTO l_Discount_Percent,
1192 l_Discount_Days,
1193 l_Discount_Date,
1194 l_Discount_Day_Of_Month,
1195 l_Discount_Months_Forward;
1196
1197 EXIT WHEN discount%NOTFOUND;
1198
1199 ec_debug.pl ( 3, 'l_Discount_Percent: ',l_Discount_Percent );
1200 ec_debug.pl ( 3, 'l_Discount_Days: ',l_Discount_Days );
1201 ec_debug.pl ( 3, 'l_Discount_Date: ',l_Discount_Date );
1202 ec_debug.pl ( 3, 'l_Discount_Day_Of_Month: ',l_Discount_Day_Of_Month );
1203 ec_debug.pl ( 3, 'l_Discount_Months_Forward: ',l_Discount_Months_Forward );
1204
1205 xProgress := '2030-50';
1206 IF l_counter = 1 THEN
1207 Discount_Percent1 := l_Discount_Percent;
1208 Discount_Days1 := l_Discount_Days;
1209 Discount_Date1 := l_Discount_Date;
1210 Discount_Day_Of_Month1 := l_Discount_Day_Of_Month;
1211 Discount_Months_Forward1 := l_Discount_Months_Forward;
1212
1213 ec_debug.pl (3, 'Discount_Percent1: ',Discount_Percent1 );
1214 ec_debug.pl (3, 'Discount_Days1: ',Discount_Days1 );
1215 ec_debug.pl (3, 'Discount_Date1: ',Discount_Date1 );
1216 ec_debug.pl (3, 'Discount_Day_Of_Month1: ',Discount_Day_Of_Month1 );
1217 ec_debug.pl (3, 'Discount_Months_Forward1: ',Discount_Months_Forward1 );
1218 END IF;
1219
1220 xProgress := '2040-50';
1221 IF l_counter = 2 THEN
1222 Discount_Percent2 := l_Discount_Percent;
1223 Discount_Days2 := l_Discount_Days;
1224 Discount_Date2 := l_Discount_Date;
1225 Discount_Day_Of_Month2 := l_Discount_Day_Of_Month;
1226 Discount_Months_Forward2 := l_Discount_Months_Forward;
1227
1228 ec_debug.pl (3, 'Discount_Percent2: ',Discount_Percent2 );
1229 ec_debug.pl (3, 'Discount_Days2: ',Discount_Days2 );
1230 ec_debug.pl (3, 'Discount_Date2: ',Discount_Date2 );
1231 ec_debug.pl (3, 'Discount_Day_Of_Month2: ',Discount_Day_Of_Month2 );
1232 ec_debug.pl (3, 'Discount_Months_Forward2: ',Discount_Months_Forward2 );
1233 END IF;
1234
1235 xProgress := '2050-50';
1236 IF l_counter = 3 THEN
1237 Discount_Percent3 := l_Discount_Percent;
1238 Discount_Days3 := l_Discount_Days;
1239 Discount_Date3 := l_Discount_Date;
1240 Discount_Day_Of_Month3 := l_Discount_Day_Of_Month;
1241 Discount_Months_Forward3 := l_Discount_Months_Forward;
1242
1243 ec_debug.pl (3, 'Discount_Percent3: ',Discount_Percent3 );
1244 ec_debug.pl (3, 'Discount_Days3: ',Discount_Days3 );
1245 ec_debug.pl (3, 'Discount_Date3: ',Discount_Date3 );
1246 ec_debug.pl (3, 'Discount_Day_Of_Month3: ',Discount_Day_Of_Month3 );
1247 ec_debug.pl (3, 'Discount_Months_Forward3: ',Discount_Months_Forward3 );
1248 END IF;
1249
1250 l_counter := l_counter + 1;
1251
1252 END LOOP;
1253
1254 xProgress := '2060-50';
1255 IF ( dbms_sql.last_row_count = 0 )
1256 THEN
1257 ec_debug.pl (1,
1258 'EC',
1259 'ECE_NO_ROW_SELECTED',
1260 'PROGRESS_LEVEL',
1261 xProgress,
1262 'INFO',
1263 'DISCOUNT',
1264 'TABLE_NAME',
1265 'RA_TERMS_LINES_DISCOUNTS' );
1266 END IF;
1267
1268 END IF;
1269
1270 ec_debug.pop ( 'ece_ar_transaction.Get_Term_Discount' );
1271
1272 EXCEPTION
1273 WHEN OTHERS THEN
1274
1275 ec_debug.pl ( 0,
1276 'EC',
1277 'ECE_PROGRAM_ERROR',
1278 'PROGRESS_LEVEL',
1279 xProgress );
1280
1281 ec_debug.pl ( 0,
1282 'EC',
1283 'ECE_ERROR_MESSAGE',
1284 'ERROR_MESSAGE',
1285 SQLERRM );
1286
1287 app_exception.raise_exception;
1288
1289 END Get_Term_Discount;
1290
1291 -- The following function gets the currency code
1292
1293 function get_currency_code
1294 return varchar2
1295 IS
1296 Begin
1297 ec_debug.push('ECE_AR_TRANSACTION.GET_CURRENCY_CODE');
1298 If base_currency_code is null Then
1299 select GLB.CURRENCY_CODE Base_Currency_Code
1300 into base_currency_code
1301 from AR_SYSTEM_PARAMETERS ASP, GL_SETS_OF_BOOKS GLB
1302 where ASP.SET_OF_BOOKS_ID = GLB.SET_OF_BOOKS_ID ;
1303 End If;
1304 ec_debug.pop('ECE_AR_TRANSACTION.GET_CURRENCY_CODE');
1305 return base_currency_code;
1306 EXCEPTION
1307 WHEN EC_UTILS.PROGRAM_EXIT then
1308 raise;
1309 WHEN OTHERS THEN
1310 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',
1311 'ECE_AR_TRANSACTION.GET_CURRENCY_CODE');
1312 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1313 ec_debug.pop('ECE_AR_TRANSACTION.GET_CURRENCY_CODE');
1314 return base_currency_code;
1315 End;
1316
1317 -- PROCEDURE Put_Data_To_Output_Table
1318 -- This procedure has the following functionalities:
1319 -- 1. Build SQL statement dynamically to extract data from
1320 -- Interface Tables.
1321 -- 2. Execute the dynamic SQL statement.
1322 -- 3. Populate the ECE_OUTPUT table with the extracted data.
1323 -- 4. Delete data from Interface Tables.
1324 -- --------------------------------------------------------------------------
1325
1326 PROCEDURE Put_Data_To_Output_Table ( cCommunication_Method IN VARCHAR2,
1327 cTransaction_Type IN VARCHAR2,
1328 iOutput_width IN INTEGER,
1329 iRun_id IN INTEGER,
1330 cHeader_Interface IN VARCHAR2,
1331 cHeader_1_Interface IN VARCHAR2,
1332 cAlw_chg_Interface IN VARCHAR2,
1333 cLine_Interface IN VARCHAR2,
1334 cLine_t_Interface IN VARCHAR2 )
1335 IS
1336
1337 /**
1338 This should be a parameter in the next version to distinguish between MAPS.
1339 For now, it will be hardcoded to NULL so the default will be the seeded FF transaction
1340 **/
1341 cMap_id NUMBER := NULL;
1342
1343 l_Header_tbl ece_flatfile_pvt.Interface_tbl_type;
1344 l_Header_1_tbl ece_flatfile_pvt.Interface_tbl_type;
1345 l_alw_chg_h_tbl ece_flatfile_pvt.Interface_tbl_type;
1346 l_Line_tbl ece_flatfile_pvt.Interface_tbl_type;
1347 l_Line_t_tbl ece_flatfile_pvt.Interface_tbl_type;
1348 l_alw_chg_l_tbl ece_flatfile_pvt.Interface_tbl_type;
1349
1350 c_Header_common_key_name VARCHAR2(40);
1351 c_Header_1_common_key_name VARCHAR2(40);
1352 c_Alw_chg_common_key_name VARCHAR2(40);
1353 c_Line_common_key_name VARCHAR2(40);
1354 c_Line_t_common_key_name VARCHAR2(40);
1355 c_file_common_key VARCHAR2(255);
1356
1357 nHeader_key_pos NUMBER;
1358 nHeader_1_key_pos NUMBER;
1359 nAlw_chg_key_pos NUMBER;
1360 nLine_key_pos NUMBER;
1361 nLine_t_key_pos NUMBER;
1362 nTrans_code_pos NUMBER;
1363
1364 Header_sel_c INTEGER;
1365 Header_1_sel_c INTEGER;
1366 Alw_chg_h_sel_c INTEGER;
1367 Alw_chg_l_sel_c INTEGER;
1368 Line_sel_c INTEGER;
1369 Line_t_sel_c INTEGER;
1370
1371 Header_del_c1 INTEGER;
1372 Header_1_del_c1 INTEGER;
1373 Alw_chg_h_del_c1 INTEGER;
1374 Alw_chg_l_del_c1 INTEGER;
1375 Line_del_c1 INTEGER;
1376 Line_t_del_c1 INTEGER;
1377
1378 Header_del_c2 INTEGER;
1379 Header_1_del_c2 INTEGER;
1380 Alw_chg_h_del_c2 INTEGER;
1381 Alw_chg_l_del_c2 INTEGER;
1382 Line_del_c2 INTEGER;
1383 Line_t_del_c2 INTEGER;
1384
1385 cHeader_select VARCHAR2(32000);
1386 cHeader_1_select VARCHAR2(32000);
1387 cAlw_chg_h_select VARCHAR2(32000);
1388 cAlw_chg_l_select VARCHAR2(32000);
1389 cLine_select VARCHAR2(32000);
1390 cLine_t_select VARCHAR2(32000);
1391
1392 cHeader_from VARCHAR2(32000);
1393 cHeader_1_from VARCHAR2(32000);
1394 cAlw_chg_h_from VARCHAR2(32000);
1395 cAlw_chg_l_from VARCHAR2(32000);
1396 cLine_from VARCHAR2(32000);
1397 cLine_t_from VARCHAR2(32000);
1398
1399 cHeader_where VARCHAR2(32000);
1400 cHeader_1_where VARCHAR2(32000);
1401 cAlw_chg_h_where VARCHAR2(32000);
1402 cAlw_chg_l_where VARCHAR2(32000);
1403 cLine_where VARCHAR2(32000);
1404 cLine_t_where VARCHAR2(32000);
1405
1406 cAlw_chg_h_output_level VARCHAR2(30);
1407 cAlw_chg_l_output_level VARCHAR2(30);
1408
1409 cHeader_delete1 VARCHAR2(32000);
1410 cHeader_1_delete1 VARCHAR2(32000);
1411 cAlw_chg_h_delete1 VARCHAR2(32000);
1412 cAlw_chg_l_delete1 VARCHAR2(32000);
1413 cLine_delete1 VARCHAR2(32000);
1414 cLine_t_delete1 VARCHAR2(32000);
1415
1416 cHeader_delete2 VARCHAR2(32000);
1417 cHeader_1_delete2 VARCHAR2(32000);
1418 cAlw_chg_h_delete2 VARCHAR2(32000);
1419 cAlw_chg_l_delete2 VARCHAR2(32000);
1420 cLine_delete2 VARCHAR2(32000);
1421 cLine_t_delete2 VARCHAR2(32000);
1422
1423 iHeader_count NUMBER;
1424 iHeader_1_count NUMBER;
1425 iAlw_chg_h_count INTEGER;
1426 iAlw_chg_l_count INTEGER;
1427 iLine_count NUMBER;
1428 iLine_t_count NUMBER;
1429
1430 rHeader_rowid ROWID;
1431 rHeader_1_rowid ROWID;
1432 rAlw_chg_h_rowid ROWID;
1433 rAlw_chg_l_rowid ROWID;
1434 rLine_rowid ROWID;
1435 rLine_t_rowid ROWID;
1436
1437 cHeader_X_Interface VARCHAR2(50);
1438 cHeader_1_X_Interface VARCHAR2(50);
1439 cAlw_chg_X_Interface VARCHAR2(50);
1440 cLine_X_Interface VARCHAR2(50);
1441 cLine_t_X_Interface VARCHAR2(50);
1442
1443 rHeader_X_rowid ROWID;
1444 rHeader_1_X_rowid ROWID;
1445 rAlw_chg_X_rowid ROWID;
1446 rLine_X_rowid ROWID;
1447 rLine_t_X_rowid ROWID;
1448
1449 dummy INTEGER;
1450
1451 nPos1 NUMBER;
1452 nTrans_id NUMBER;
1453
1454 v_LevelProcessed VARCHAR2(40);
1455
1456 BEGIN
1457
1458 ec_debug.push ( 'ece_ar_transaction.Put_Data_To_Output_Table' );
1459 ec_debug.pl ( 3, 'cCommunication_Method: ', cCommunication_Method );
1460 ec_debug.pl ( 3, 'cTransaction_Type: ',cTransaction_Type );
1461 ec_debug.pl ( 3, 'iOutput_width: ',iOutput_width );
1462 ec_debug.pl ( 3, 'iRun_id: ',iRun_id );
1463 ec_debug.pl ( 3, 'cHeader_1_Interface: ',cHeader_1_Interface );
1464 ec_debug.pl ( 3, 'cAlw_chg_Interface: ',cAlw_chg_Interface );
1465 ec_debug.pl ( 3, 'cLine_Interface: ',cLine_Interface );
1466 ec_debug.pl ( 3, 'cLine_t_Interface: ',cLine_t_Interface );
1467
1468 -- **************************************************************************
1469 -- Here, I am building the SELECT, FROM, and WHERE clauses for the dynamic
1470 -- SQL call
1471 -- The ece_flatfile.select_clause uses the db data dictionary for the build.
1472 -- (The db data dictionary store contains all types of info about Interface
1473 -- tables and Extension tables.)
1474
1475 -- The DELETE clauses will be used to clean up both the interface and extension
1476 -- tables. I am using ROWID to tell me which row in the interface table is
1477 -- being written to the output table, thus, can be deleted.
1478 -- **************************************************************************
1479 -- Here we have to find the output level of the interface tables for allowances
1480 -- and Charges. The output level has to be passed on as an additional parameter
1481 -- here because the ECE_AR_TRX_ALLOWANCE_CHARGES is extracted twice into output
1482 -- table
1483 -- **************************************************************************
1484
1485 xProgress := '2000-60';
1486 BEGIN
1487 SELECT MIN(eel.external_level)
1488 INTO cAlw_chg_h_output_level
1489 FROM ece_interface_tables eit,
1490 ece_level_matrices elm,
1491 ece_external_levels eel
1492 WHERE eit.interface_table_name = 'ECE_AR_TRX_ALLOWANCE_CHARGES'
1493 AND eit.transaction_type = cTransaction_type
1494 AND eit.interface_table_id = elm.interface_table_id
1495 AND elm.external_level_id = eel.external_level_id
1496 AND eel.map_id = (SELECT NVL(cMap_id, MAX(em1.map_id))
1497 FROM ece_mappings em1
1498 WHERE em1.map_code like 'EC_'||RTRIM(LTRIM(NVL(cTransaction_type,'%')))||'_FF');
1499 EXCEPTION
1500 WHEN NO_DATA_FOUND THEN
1501 ec_debug.pl ( 1,
1502 'EC',
1503 'ECE_NO_ROW_SELECTED',
1504 'PROGRESS_LEVEL',
1505 xProgress,
1506 'INFO',
1507 'MINIMUM OUTPUT LEVEL',
1508 'TABLE_NAME',
1509 'ECE_INTERFACE_TABLES' );
1510 END;
1511
1512 ec_debug.pl ( 3, 'cAlw_chg_h_output_level: ',cAlw_chg_h_output_level );
1513
1514 xProgress := '2010-60';
1515 BEGIN
1516 SELECT MAX(eel.external_level)
1517 INTO cAlw_chg_l_output_level
1518 FROM ece_interface_tables eit,
1519 ece_level_matrices elm,
1520 ece_external_levels eel
1521 WHERE eit.interface_table_name = 'ECE_AR_TRX_ALLOWANCE_CHARGES'
1522 AND eit.transaction_type = cTransaction_type
1523 AND eit.interface_table_id = elm.interface_table_id
1524 AND elm.external_level_id = eel.external_level_id
1525 AND eel.map_id = (SELECT NVL(cMap_id, MAX(em1.map_id))
1526 FROM ece_mappings em1
1527 WHERE em1.map_code like 'EC_'||RTRIM(LTRIM(NVL(cTransaction_type,'%')))||'_FF');
1528 EXCEPTION
1529 WHEN NO_DATA_FOUND THEN
1530 ec_debug.pl ( 1,
1531 'EC',
1532 'ECE_NO_ROW_SELECTED',
1533 'PROGRESS_LEVEL',
1534 xProgress,
1535 'INFO',
1536 'MAXIMUM OUTPUT LEVEL',
1537 'TABLE_NAME',
1538 'ECE_INTERFACE_TABLES' );
1539 END;
1540
1541 ec_debug.pl ( 3, 'cAlw_chg_l_output_level: ',cAlw_chg_l_output_level );
1542
1543 xProgress := '2020-60';
1544 ece_flatfile_pvt.select_clause ( cTransaction_Type,
1545 cCommunication_Method,
1546 cHeader_Interface,
1547 cHeader_X_Interface,
1548 l_Header_tbl,
1549 c_Header_common_key_name,
1550 cHeader_select,
1551 cHeader_from,
1552 cHeader_where );
1553
1554 xProgress := '2030-60';
1555 ece_flatfile_pvt.select_clause ( cTransaction_Type,
1556 cCommunication_Method,
1557 cHeader_1_Interface,
1558 cHeader_1_X_Interface,
1559 l_Header_1_tbl,
1560 c_Header_1_common_key_name,
1561 cHeader_1_select,
1562 cHeader_1_from,
1563 cHeader_1_where );
1564
1565 xProgress := '2040-60';
1566 ece_flatfile_pvt.select_clause ( cTransaction_Type,
1567 cCommunication_Method,
1568 cAlw_chg_Interface,
1569 cAlw_chg_X_Interface,
1570 l_alw_chg_h_tbl,
1571 c_Alw_chg_common_key_name,
1572 cAlw_chg_h_select,
1573 cAlw_chg_h_from,
1574 cAlw_chg_h_where,
1575 cAlw_chg_h_output_level );
1576
1577 xProgress := '2050-60';
1578 ece_flatfile_pvt.select_clause ( cTransaction_Type,
1579 cCommunication_Method,
1580 cLine_Interface,
1581 cLine_X_Interface,
1582 l_Line_tbl,
1583 c_Line_common_key_name,
1584 cLine_select,
1585 cLine_from, cLine_where );
1586
1587 xProgress := '2060-60';
1588 ece_flatfile_pvt.select_clause ( cTransaction_Type,
1589 cCommunication_Method,
1590 cLine_t_Interface,
1591 cLine_t_X_Interface,
1592 l_Line_t_tbl,
1593 c_Line_t_common_key_name,
1594 cLine_t_select,
1595 cLine_t_from,
1596 cLine_t_where );
1597
1598 xProgress := '2070-60';
1599 ece_flatfile_pvt.select_clause ( cTransaction_Type,
1600 cCommunication_Method,
1601 cAlw_chg_Interface,
1602 cAlw_chg_X_Interface,
1603 l_alw_chg_l_tbl,
1604 c_Alw_chg_common_key_name,
1605 cAlw_chg_l_select,
1606 cAlw_chg_l_from,
1607 cAlw_chg_l_where,
1608 cAlw_chg_l_output_level );
1609
1610 -- **************************************************************************
1611 -- Here, I am customizing the WHERE clause to join the Interface tables together.
1612 -- i.e. Headers -- Lines -- Line Details
1613 --
1614 -- Select Data1, Data2, Data3...........
1615 -- From Header_Interface A, Line_Interface B, Line_details_Interface C,
1616 -- Header_Interface_X D, Line_Interface_X E, Line_details_Interface_X F
1617 -- Where A.Transaction_Record_ID = D.Transaction_Record_ID (+)
1618 -- and B.Transaction_Record_ID = E.Transaction_Record_ID (+)
1619 -- and C.Transaction_Record_ID = F.Transaction_Record_ID (+)
1620 -- $$$$$ (Customization should be added here) $$$$$$
1621 -- and A.Communication_Method = 'EDI'
1622 -- and A.xxx = B.xxx ........
1623 -- and B.yyy = C.yyy .......
1624 -- **************************************************************************
1625
1626
1627 /* --------------------------------------------------------------------------
1628 :transaction_id is a place holder for foreign key value.
1629 A PL/SQL table (list of values) will be used to store data.
1630 Procedure ece_flatfile.Find_pos will be used to locate the specific
1631 data value in the PL/SQL table.
1632 dbms_sql (Native Oracle db functions that come with every Oracle Apps)
1633 dbms_sql.bind_variable will be used to assign data value to :transaction_id.
1634
1635 Let's use the above example:
1636
1637 1. Execute dynamic SQL 1 for headers (A) data
1638 Get value of A.xxx (foreign key to B)
1639
1640 2. bind value A.xxx to variable B.xxx
1641
1642 3. Execute dynamic SQL 2 for lines (B) data
1643 Get value of B.yyy (foreigh key to C)
1644
1645 4. bind value B.yyy to variable C.yyy
1646
1647 5. Execute dynamic SQL 3 for line_details (C) data
1648 ----------------------------------------------------------------------------
1649 */
1650
1651 xProgress := '2080-60';
1652 cHeader_where := cHeader_where ||
1653 ' AND ' ||
1654 cHeader_Interface ||
1655 '.RUN_ID =' ||
1656 ':b1' ;
1657 ec_debug.pl ( 3, 'cHeader_where: ',cHeader_where );
1658
1659 xProgress := '2090-60';
1660 cHeader_1_where := cHeader_1_where ||
1661 ' AND ' ||
1662 cHeader_1_Interface ||
1663 '.RUN_ID =' ||
1664 ':b2' ||
1665 ' AND ' ||
1666 cHeader_1_Interface ||
1667 '.TRANSACTION_ID = :transaction_id';
1668 ec_debug.pl ( 3, 'cHeader_1_where: ',cHeader_1_where );
1669
1670 xProgress := '2100-60';
1671 cAlw_chg_h_where := cAlw_chg_h_where ||
1672 ' AND ' ||
1673 cAlw_chg_Interface ||
1674 '.RUN_ID =' ||
1675 ':b3' ||
1676 ' AND ' ||
1677 cAlw_chg_Interface ||
1678 '.TRANSACTION_ID = :transaction_id' ||
1679 ' AND ' ||
1680 cAlw_chg_Interface ||
1681 '.HEADER_DETAIL_INDICATOR = ''H''';
1682 ec_debug.pl ( 3, 'cAlw_chg_h_where: ',cAlw_chg_h_where );
1683
1684 xProgress := '2110-60';
1685 cLine_where := cLine_where ||
1686 ' AND ' ||
1687 cLine_Interface ||
1688 '.RUN_ID =' ||
1689 ':b4' ||
1690 ' AND ' ||
1691 cLine_Interface ||
1692 '.TRANSACTION_ID = :transaction_id' ||
1693 ' ORDER BY ' ||
1694 cLine_Interface ||
1695 '.LINE_NUMBER';
1696 ec_debug.pl ( 3, 'cLine_where: ',cLine_where );
1697
1698 xProgress := '2120-60';
1699 cLine_t_where := cLine_t_where ||
1700 ' AND ' ||
1701 cLine_t_Interface ||
1702 '.RUN_ID =' ||
1703 ':b5' ||
1704 ' AND ' ||
1705 cLine_t_Interface ||
1706 '.TRANSACTION_ID = :transaction_id' ||
1707 ' AND ' ||
1708 cLine_t_Interface ||
1709 '.LINE_NUMBER = :line_number';
1710 ec_debug.pl ( 3, 'cLine_t_where: ',cLine_t_where );
1711
1712 xProgress := '2130-60';
1713 cAlw_chg_l_where := cAlw_chg_l_where ||
1714 ' AND ' ||
1715 cAlw_chg_Interface ||
1716 '.RUN_ID =' ||
1717 ':b6' ||
1718 ' AND ' ||
1719 cAlw_chg_Interface ||
1720 '.TRANSACTION_ID = :transaction_id' ||
1721 ' AND ' ||
1722 cAlw_chg_Interface ||
1723 '.HEADER_DETAIL_INDICATOR = ''D''' ||
1724 ' AND ' ||
1725 cAlw_chg_Interface ||
1726 '.LINE_NUMBER = :line_number';
1727 ec_debug.pl ( 3, 'cAlw_chg_l_where: ',cAlw_chg_l_where );
1728
1729 xProgress := '2140-60';
1730 cHeader_select := cHeader_select ||
1731 ',' ||
1732 cHeader_Interface ||
1733 '.ROWID, ' ||
1734 cHeader_X_Interface ||
1735 '.ROWID, ' ||
1736 cHeader_Interface ||
1737 '.TRANSACTION_ID';
1738 ec_debug.pl ( 3, 'cHeader_select: ',cHeader_select );
1739
1740 xProgress := '2150-60';
1741 cHeader_1_select := cHeader_1_select ||
1742 ',' ||
1743 cHeader_1_Interface ||
1744 '.ROWID, ' ||
1745 cHeader_1_X_Interface ||
1746 '.ROWID, ' ||
1747 cHeader_1_Interface ||
1748 '.TRANSACTION_ID';
1749 ec_debug.pl ( 3, 'cHeader_1_select: ',cHeader_1_select );
1750
1751 xProgress := '2160-60';
1752 cAlw_chg_h_select := cAlw_chg_h_select ||
1753 ',' ||
1754 cAlw_chg_Interface ||
1755 '.ROWID, ' ||
1756 cAlw_chg_X_Interface ||
1757 '.ROWID';
1758 ec_debug.pl ( 3, 'cAlw_chg_h_select: ',cAlw_chg_h_select );
1759
1760 xProgress := '2170-60';
1761 cLine_select := cLine_select ||
1762 ',' ||
1763 cLine_Interface ||
1764 '.ROWID,' ||
1765 cLine_X_Interface ||
1766 '.ROWID';
1767 ec_debug.pl ( 3, 'cLine_select: ',cLine_select );
1768
1769 xProgress := '2170-60';
1770 cLine_t_select := cLine_t_select ||
1771 ',' ||
1772 cLine_t_Interface ||
1773 '.ROWID,' ||
1774 cLine_t_X_Interface ||
1775 '.ROWID';
1776 ec_debug.pl ( 3, 'cLine_t_select: ',cLine_t_select );
1777
1778 xProgress := '2180-60';
1779 cAlw_chg_l_select := cAlw_chg_l_select ||
1780 ',' ||
1781 cAlw_chg_Interface ||
1782 '.ROWID, ' ||
1783 cAlw_chg_X_Interface ||
1784 '.ROWID';
1785 ec_debug.pl ( 3, 'cAlw_chg_l_select: ',cAlw_chg_l_select );
1786
1787
1788 xProgress := '2190-60';
1789 cHeader_select := cHeader_select ||
1790 cHeader_from ||
1791 cHeader_where ||
1792 ' ORDER BY ' || cHeader_Interface || '.BILL_TO_CUSTOMER_NAME,' || /*Bug 2464584*/
1793 cHeader_Interface || '.BILL_TO_CUSTOMER_LOCATION ' ||
1794 ' FOR UPDATE';
1795 ec_debug.pl ( 3, 'cHeader_select: ',cHeader_select );
1796
1797 cHeader_1_select := cHeader_1_select ||
1798 cHeader_1_from ||
1799 cHeader_1_where ||
1800 ' FOR UPDATE';
1801 ec_debug.pl ( 3, 'cHeader_1_select: ',cHeader_1_select );
1802
1803 cAlw_chg_h_select := cAlw_chg_h_select ||
1804 cAlw_chg_h_from ||
1805 cAlw_chg_h_where ||
1806 ' FOR UPDATE';
1807 ec_debug.pl ( 3, 'cAlw_chg_h_select: ',cAlw_chg_h_select );
1808
1809 cLine_select := cLine_select ||
1810 cLine_from ||
1811 cLine_where ||
1812 ' FOR UPDATE';
1813 ec_debug.pl ( 3, 'cLine_select: ',cLine_select );
1814
1815 cLine_t_select := cLine_t_select ||
1816 cLine_t_from ||
1817 cLine_t_where ||
1818 ' FOR UPDATE';
1819 ec_debug.pl ( 3, 'cLine_t_select: ',cLine_t_select );
1820
1821 cAlw_chg_l_select := cAlw_chg_l_select ||
1822 cAlw_chg_l_from ||
1823 cAlw_chg_l_where ||
1824 ' FOR UPDATE';
1825 ec_debug.pl ( 3, 'cAlw_chg_l_select: ',cAlw_chg_l_select );
1826
1827 xProgress := '2200-60';
1828 cHeader_delete1 := 'DELETE FROM ' ||
1829 cHeader_Interface ||
1830 ' WHERE ROWID = :col_rowid';
1831 ec_debug.pl ( 3, 'cHeader_delete1: ',cHeader_delete1 );
1832
1833 xProgress := '2201-60';
1834 cHeader_1_delete1 := 'DELETE FROM ' ||
1835 cHeader_1_Interface ||
1836 ' WHERE ROWID = :col_rowid';
1837 ec_debug.pl ( 3, 'cHeader_1_delete1: ',cHeader_1_delete1 );
1838
1839 xProgress := '2202-60';
1840 cAlw_chg_h_delete1 := 'DELETE FROM ' ||
1841 cAlw_chg_Interface ||
1842 ' WHERE ROWID = :col_rowid';
1843 ec_debug.pl ( 3, 'cAlw_chg_h_delete1: ',cAlw_chg_h_delete1 );
1844
1845 xProgress := '2203-60';
1846 cLine_delete1 := 'DELETE FROM ' ||
1847 cLine_Interface ||
1848 ' WHERE ROWID = :col_rowid';
1849 ec_debug.pl ( 3, 'cLine_delete1: ',cLine_delete1 );
1850
1851 xProgress := '2204-60';
1852 cLine_t_delete1 := 'DELETE FROM ' ||
1853 cLine_t_Interface ||
1854 ' WHERE ROWID = :col_rowid';
1855 ec_debug.pl ( 3, 'cLine_t_delete1: ',cLine_t_delete1 );
1856
1857 xProgress := '2205-60';
1858 cAlw_chg_l_delete1 := 'DELETE FROM ' ||
1859 cAlw_chg_Interface ||
1860 ' WHERE ROWID = :col_rowid';
1861 ec_debug.pl ( 3, 'cAlw_chg_l_delete1: ',cAlw_chg_l_delete1 );
1862
1863 xProgress := '2206-60';
1864 cHeader_delete2 := 'DELETE FROM ' ||
1865 cHeader_X_Interface ||
1866 ' WHERE ROWID = :col_rowid';
1867 ec_debug.pl ( 3, 'cHeader_delete2: ',cHeader_delete2 );
1868
1869 xProgress := '2207-60';
1870 cHeader_1_delete2 := 'DELETE FROM ' ||
1871 cHeader_1_X_Interface ||
1872 ' WHERE ROWID = :col_rowid';
1873 ec_debug.pl ( 3, 'cHeader_1_delete2: ',cHeader_1_delete2 );
1874
1875 xProgress := '2208-60';
1876 cAlw_chg_h_delete2 := 'DELETE FROM ' ||
1877 cAlw_chg_X_Interface ||
1878 ' WHERE ROWID = :col_rowid';
1879 ec_debug.pl ( 3, 'cAlw_chg_h_delete2: ',cAlw_chg_h_delete2 );
1880
1881 xProgress := '2209-60';
1882 cLine_delete2 := 'DELETE FROM ' ||
1883 cLine_X_Interface ||
1884 ' WHERE ROWID = :col_rowid';
1885 ec_debug.pl ( 3, 'cLine_delete2: ',cLine_delete2 );
1886
1887 xProgress := '2210-60';
1888 cLine_t_delete2 := 'DELETE FROM ' ||
1889 cLine_t_X_Interface ||
1890 ' WHERE ROWID = :col_rowid';
1891 ec_debug.pl ( 3, 'cLine_t_delete2: ',cLine_t_delete2 );
1892
1893 xProgress := '2211-60';
1894 cAlw_chg_l_delete2 := 'DELETE FROM ' ||
1895 cAlw_chg_X_Interface ||
1896 ' WHERE ROWID = :col_rowid';
1897 ec_debug.pl ( 3, 'cAlw_chg_l_delete2: ',cAlw_chg_l_delete2 );
1898
1899 /***
1900 *** Get data setup for the dynamic SQL call.
1901 ***
1902 *** Open a cursor for each of the SELECT call
1903 ***/
1904
1905 xProgress := '2212-60';
1906 Header_sel_c := dbms_sql.open_cursor;
1907
1908 xProgress := '2213-60';
1909 Header_1_sel_c := dbms_sql.open_cursor;
1910
1911 xProgress := '2214-60';
1912 Alw_chg_h_sel_c := dbms_sql.open_cursor;
1913
1914 xProgress := '2215-60';
1915 Line_sel_c := dbms_sql.open_cursor;
1916
1917 xProgress := '2216-60';
1918 Line_t_sel_c := dbms_sql.open_cursor;
1919
1920 xProgress := '2217-60';
1921 Alw_chg_l_sel_c := dbms_sql.open_cursor;
1922
1923 xProgress := '2218-60';
1924 Header_del_c1 := dbms_sql.open_cursor;
1925
1926 xProgress := '2219-60';
1927 Header_1_del_c1 := dbms_sql.open_cursor;
1928
1929 xProgress := '2220-60';
1930 Alw_chg_h_del_c1 := dbms_sql.open_cursor;
1931
1932 xProgress := '2221-60';
1933 Line_del_c1 := dbms_sql.open_cursor;
1934
1935 xProgress := '2222-60';
1936 Line_t_del_c1 := dbms_sql.open_cursor;
1937
1938 xProgress := '2223-60';
1939 Alw_chg_l_del_c1 := dbms_sql.open_cursor;
1940
1941 xProgress := '2224-60';
1942 Header_del_c2 := dbms_sql.open_cursor;
1943
1944 xProgress := '2225-60';
1945 Header_1_del_c2 := dbms_sql.open_cursor;
1946
1947 xProgress := '2226-60';
1948 Alw_chg_h_del_c2 := dbms_sql.open_cursor;
1949
1950 xProgress := '2227-60';
1951 Line_del_c2 := dbms_sql.open_cursor;
1952
1953 xProgress := '2228-60';
1954 Line_t_del_c2 := dbms_sql.open_cursor;
1955
1956 xProgress := '2229-60';
1957 Alw_chg_l_del_c2 := dbms_sql.open_cursor;
1958
1959 /***
1960 *** Parse each of the SELECT and DELETE statement
1961 ***/
1962
1963 xProgress := '2230-60';
1964 BEGIN
1965 dbms_sql.parse ( Header_sel_c,
1966 cHeader_select,
1967 dbms_sql.native );
1968 EXCEPTION
1969 WHEN OTHERS THEN
1970 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
1971 cHeader_select);
1972 app_exception.raise_exception;
1973 END;
1974
1975 xProgress := '2231-60';
1976 BEGIN
1977 dbms_sql.parse ( Header_1_sel_c,
1978 cHeader_1_select,
1979 dbms_sql.native );
1980 EXCEPTION
1981 WHEN OTHERS THEN
1982 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
1983 cHeader_1_select);
1984 app_exception.raise_exception;
1985 END;
1986
1987 xProgress := '2232-60';
1988 BEGIN
1989 dbms_sql.parse ( Alw_chg_h_sel_c,
1990 cAlw_chg_h_select,
1991 dbms_sql.native );
1992 EXCEPTION
1993 WHEN OTHERS THEN
1994 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
1995 cAlw_chg_h_select );
1996 app_exception.raise_exception;
1997 END;
1998
1999 xProgress := '2233-60';
2000 BEGIN
2001 dbms_sql.parse ( Line_sel_c,
2002 cLine_select,
2003 dbms_sql.native );
2004 EXCEPTION
2005 WHEN OTHERS THEN
2006 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2007 cLine_select );
2008 app_exception.raise_exception;
2009 END;
2010
2011 xProgress := '2234-60';
2012 BEGIN
2013 dbms_sql.parse ( Line_t_sel_c,
2014 cLine_t_select,
2015 dbms_sql.native );
2016 EXCEPTION
2017 WHEN OTHERS THEN
2018 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2019 cLine_t_select );
2020 app_exception.raise_exception;
2021 END;
2022
2023 xProgress := '2235-60';
2024 BEGIN
2025 dbms_sql.parse ( Alw_chg_l_sel_c,
2026 cAlw_chg_l_select,
2027 dbms_sql.native );
2028 EXCEPTION
2029 WHEN OTHERS THEN
2030 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2031 cAlw_chg_l_select );
2032 app_exception.raise_exception;
2033 END;
2034
2035 xProgress := '2236-60';
2036 BEGIN
2037 dbms_sql.parse ( Header_del_c1,
2038 cHeader_delete1,
2039 dbms_sql.native );
2040 EXCEPTION
2041 WHEN OTHERS THEN
2042 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2043 cHeader_delete1 );
2044 app_exception.raise_exception;
2045 END;
2046
2047 xProgress := '2237-60';
2048 BEGIN
2049 dbms_sql.parse ( Header_1_del_c1,
2050 cHeader_1_delete1,
2051 dbms_sql.native );
2052 EXCEPTION
2053 WHEN OTHERS THEN
2054 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2055 cHeader_1_delete1 );
2056 app_exception.raise_exception;
2057 END;
2058
2059 xProgress := '2238-60';
2060 BEGIN
2061 dbms_sql.parse ( Alw_chg_h_del_c1,
2062 cAlw_chg_h_delete1,
2063 dbms_sql.native );
2064 EXCEPTION
2065 WHEN OTHERS THEN
2066 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2067 cAlw_chg_h_delete1 );
2068 app_exception.raise_exception;
2069 END;
2070
2071 xProgress := '2239-60';
2072 BEGIN
2073 dbms_sql.parse ( Line_del_c1,
2074 cLine_delete1,
2075 dbms_sql.native );
2076 EXCEPTION
2077 WHEN OTHERS THEN
2078 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2079 cLine_delete1 );
2080 app_exception.raise_exception;
2081 END;
2082
2083 xProgress := '2240-60';
2084 BEGIN
2085 dbms_sql.parse ( Line_t_del_c1,
2086 cLine_t_delete1,
2087 dbms_sql.native );
2088 EXCEPTION
2089 WHEN OTHERS THEN
2090 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2091 cLine_t_delete1 );
2092 app_exception.raise_exception;
2093 END;
2094
2095 xProgress := '2241-60';
2096 BEGIN
2097 dbms_sql.parse ( Alw_chg_l_del_c1,
2098 cAlw_chg_l_delete1,
2099 dbms_sql.native );
2100 EXCEPTION
2101 WHEN OTHERS THEN
2102 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2103 cAlw_chg_l_delete1 );
2104 app_exception.raise_exception;
2105 END;
2106
2107 xProgress := '2242-60';
2108 BEGIN
2109 dbms_sql.parse ( Header_del_c2,
2110 cHeader_delete2,
2111 dbms_sql.native );
2112 EXCEPTION
2113 WHEN OTHERS THEN
2114 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2115 cHeader_delete2 );
2116 app_exception.raise_exception;
2117 END;
2118
2119 xProgress := '2243-60';
2120 BEGIN
2121 dbms_sql.parse ( Header_1_del_c2,
2122 cHeader_1_delete2,
2123 dbms_sql.native );
2124 EXCEPTION
2125 WHEN OTHERS THEN
2126 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2127 cHeader_1_delete2 );
2128 app_exception.raise_exception;
2129 END;
2130
2131 xProgress := '2244-60';
2132 BEGIN
2133 dbms_sql.parse ( Alw_chg_h_del_c2,
2134 cAlw_chg_h_delete2,
2135 dbms_sql.native );
2136 EXCEPTION
2137 WHEN OTHERS THEN
2138 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2139 cAlw_chg_h_delete2 );
2140 app_exception.raise_exception;
2141 END;
2142
2143 xProgress := '2245-60';
2144 BEGIN
2145 dbms_sql.parse ( Line_del_c2,
2146 cLine_delete2,
2147 dbms_sql.native );
2148 EXCEPTION
2149 WHEN OTHERS THEN
2150 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2151 cLine_delete2 );
2152 app_exception.raise_exception;
2153 END;
2154
2155 xProgress := '2246-60';
2156 BEGIN
2157 dbms_sql.parse ( Line_t_del_c2,
2158 cLine_t_delete2,
2159 dbms_sql.native );
2160 EXCEPTION
2161 WHEN OTHERS THEN
2162 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2163 cLine_t_delete2 );
2164 app_exception.raise_exception;
2165 END;
2166
2167 xProgress := '2247-60';
2168 BEGIN
2169 dbms_sql.parse ( Alw_chg_l_del_c2,
2170 cAlw_chg_l_delete2,
2171 dbms_sql.native );
2172 EXCEPTION
2173 WHEN OTHERS THEN
2174 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2175 cAlw_chg_l_delete2 );
2176 app_exception.raise_exception;
2177 END;
2178
2179 -- *************************************************
2180 -- set counter
2181 -- *************************************************
2182
2183
2184 xProgress := '2250-60';
2185 iHeader_count := l_Header_tbl.count;
2186 ec_debug.pl ( 3, 'iHeader_count: ',iHeader_count );
2187
2188 xProgress := '2252-60';
2189 iHeader_1_count := l_Header_1_tbl.count;
2190 ec_debug.pl ( 3, 'iHeader_1_count: ',iHeader_1_count );
2191
2192 xProgress := '2254-60';
2193 iAlw_chg_h_count := l_alw_chg_h_tbl.count;
2194 ec_debug.pl ( 3, 'iAlw_chg_h_count: ',iAlw_chg_h_count );
2195
2196 xProgress := '2256-60';
2197 iLine_count := l_Line_tbl.count;
2198 ec_debug.pl ( 3, 'iLine_count: ',iLine_count );
2199
2200 xProgress := '2258-60';
2201 iLine_t_count := l_Line_t_tbl.count;
2202 ec_debug.pl ( 3, 'iLine_t_count: ',iLine_t_count );
2203
2204 xProgress := '2260-60';
2205 iAlw_chg_l_count := l_alw_chg_l_tbl.count;
2206 ec_debug.pl ( 3, 'iAlw_chg_l_count: ',iAlw_chg_l_count );
2207
2208 /***
2209 *** Define TYPE for every columns in the SELECT statement
2210 ***/
2211
2212
2213 xProgress := '2270-60';
2214 FOR k IN 1..iHeader_count
2215 LOOP
2216 dbms_sql.define_column ( Header_sel_c,
2217 k,
2218 cHeader_select,
2219 ece_flatfile_pvt.G_MaxColWidth );
2220 END LOOP;
2221
2222 /***
2223 *** Need rowid for delete (Header Level)
2224 ***/
2225
2226 xProgress := '2280-60';
2227 dbms_sql.define_column_rowid ( Header_sel_c,
2228 iHeader_count + 1,
2229 rHeader_rowid );
2230
2231 xProgress := '2282-60';
2232 dbms_sql.define_column_rowid ( Header_sel_c,
2233 iHeader_count + 2,
2234 rHeader_X_rowid );
2235
2236 xProgress := '2284-60';
2237 dbms_sql.define_column ( Header_sel_c,
2238 iHeader_count + 3,
2239 nTrans_id );
2240
2241
2242 xProgress := '2290-60';
2243 FOR k IN 1..iHeader_1_count
2244 LOOP
2245 dbms_sql.define_column ( Header_1_sel_c,
2246 k,
2247 cHeader_1_select,
2248 ece_flatfile_pvt.G_MaxColWidth );
2249 END LOOP;
2250
2251 /***
2252 *** Need rowid for delete (Header 1 Level)
2253 ***/
2254
2255 xProgress := '2292-60';
2256 dbms_sql.define_column_rowid ( Header_1_sel_c,
2257 iHeader_1_count + 1,
2258 rHeader_1_rowid );
2259
2260 xProgress := '2294-60';
2261 dbms_sql.define_column_rowid ( Header_1_sel_c,
2262 iHeader_1_count + 2,
2263 rHeader_1_X_rowid );
2264
2265 xProgress := '2300-60';
2266 FOR k IN 1..iAlw_chg_h_count
2267 LOOP
2268 dbms_sql.define_column ( Alw_chg_h_sel_c,
2269 k,
2270 cAlw_chg_h_select,
2271 ece_flatfile_pvt.G_MaxColWidth );
2272 END LOOP;
2273
2274 /***
2275 *** Need rowid for delete (Allowance Charges Header Level)
2276 ***/
2277
2278 xProgress := '2310-60';
2279 dbms_sql.define_column_rowid ( Alw_chg_h_sel_c,
2280 iAlw_chg_h_count + 1,
2281 rAlw_chg_h_rowid );
2282
2283 xProgress := '2312-60';
2284 dbms_sql.define_column_rowid ( Alw_chg_h_sel_c,
2285 iAlw_chg_h_count + 2,
2286 rAlw_chg_X_rowid );
2287
2288 xProgress := '2320-60';
2289 FOR k IN 1..iLine_count
2290 LOOP
2291 dbms_sql.define_column ( Line_sel_c,
2292 k,
2293 cLine_select,
2294 ece_flatfile_pvt.G_MaxColWidth );
2295 END LOOP;
2296
2297 /***
2298 *** Need rowid for delete (Line Level)
2299 ***/
2300
2301 xProgress := '2330-60';
2302 dbms_sql.define_column_rowid ( Line_sel_c,
2303 iLine_count + 1,
2304 rLine_rowid );
2305
2306 xProgress := '2332-60';
2307 dbms_sql.define_column_rowid ( Line_sel_c,
2308 iLine_count + 2,
2309 rLine_X_rowid );
2310
2311 xProgress := '2340-60';
2312 FOR k IN 1..iLine_t_count
2313 LOOP
2314 dbms_sql.define_column ( Line_t_sel_c,
2315 k,
2316 cLine_t_select,
2317 ece_flatfile_pvt.G_MaxColWidth );
2318 END LOOP;
2319
2320 /***
2321 *** Need rowid for delete (Line Level)
2322 ***/
2323
2324 xProgress := '2350-60';
2325 dbms_sql.define_column_rowid ( Line_t_sel_c,
2326 iLine_t_count + 1,
2327 rLine_t_rowid );
2328
2329 xProgress := '2352-60';
2330 dbms_sql.define_column_rowid ( Line_t_sel_c,
2331 iLine_t_count + 2,
2332 rLine_t_X_rowid );
2333
2334 xProgress := '2360-60';
2335 FOR k IN 1..iAlw_chg_l_count
2336 LOOP
2337 dbms_sql.define_column ( Alw_chg_l_sel_c,
2338 k,
2339 cAlw_chg_l_select,
2340 ece_flatfile_pvt.G_MaxColWidth );
2341 END LOOP;
2342
2343 /***
2344 *** Need rowid for delete (Allowance Charges Detail Level)
2345 ***/
2346
2347 xProgress := '2370-60';
2348 dbms_sql.define_column_rowid ( Alw_chg_l_sel_c,
2349 iAlw_chg_l_count + 1,
2350 rAlw_chg_l_rowid );
2351
2352 xProgress := '2372-60';
2353 dbms_sql.define_column_rowid ( Alw_chg_l_sel_c,
2354 iAlw_chg_l_count + 2,
2355 rAlw_chg_X_rowid );
2356
2357 /**************************************************************
2358 *** The following is custom tailored for this transaction
2359 *** It find the values and use them in the WHERE clause to
2360 *** join tables together.
2361 **************************************************************/
2362
2363 /*** To complete the SELECT statement,
2364 *** we will need values for the join condition.
2365 ***/
2366
2367 xProgress := '2380-60';
2368 ece_flatfile_pvt.Find_pos ( l_Line_tbl,
2369 'LINE_NUMBER',
2370 nPos1 );
2371 ec_debug.pl ( 3, 'nPos1: ',nPos1 );
2372
2373 -- EXECUTE the SELECT statement
2374
2375 dbms_sql.bind_variable ( Header_sel_c, 'b1', iRun_id );
2376
2377 xProgress := '2390-60';
2378 dummy := dbms_sql.execute ( Header_sel_c );
2379
2380 /*** --------------------------------------------------------------
2381 *** With data for each HEADER line, populate the ECE_OUTPUT table
2382 *** then populate ECE_OUTPUT with data from all HEADER 1 that belongs
2383 *** to the HEADER and Allowances and Charges that belong to the header
2384 *** Then populate ECE_OUTPUT with data from all
2385 *** LINES that belongs to the HEADER and then populate ECE_OUTPUT with
2386 *** data from all LINE TAX, Allowances and Charges that belong to the line
2387 *** ------------------------------------------------------------***/
2388
2389 -- HEADER - HEADER 1 - HEADER ALLOWANCES and CHARGES - LINE - LINE ALLOWANCES and CHARGES
2390 -- - LINE TAX ...
2391
2392 xProgress := '2400-60';
2393 WHILE dbms_sql.fetch_rows ( Header_sel_c ) > 0
2394 LOOP -- Header
2395
2396 /***
2397 *** store values in pl/sql table
2398 ***/
2399
2400 xProgress := '2410-60';
2401 FOR i IN 1..iHeader_count
2402 LOOP
2403 dbms_sql.column_value ( Header_sel_c,
2404 i,
2405 l_Header_tbl(i).value );
2406 END LOOP;
2407
2408 xProgress := '2420-60';
2409 dbms_sql.column_value ( Header_sel_c,
2410 iHeader_count + 1,
2411 rHeader_rowid );
2412
2413 xProgress := '2422-60';
2414 dbms_sql.column_value ( Header_sel_c,
2415 iHeader_count + 2,
2416 rHeader_X_rowid );
2417
2418 xProgress := '2424-60';
2419 dbms_sql.column_value (Header_sel_c,
2420 iHeader_count + 3,
2421 nTrans_id );
2422
2423 xProgress := '2430-60';
2424 ece_flatfile_pvt.Find_pos ( l_Header_tbl,
2425 ece_flatfile_pvt.G_Translator_Code,
2426 nTrans_code_pos );
2427 ec_debug.pl ( 3, 'nTrans_code_pos: ',nTrans_code_pos );
2428
2429 xProgress := '2432-60';
2430 ece_flatfile_pvt.Find_pos ( l_Header_tbl,
2431 c_header_common_key_name,
2432 nHeader_key_pos );
2433 ec_debug.pl ( 3, 'nHeader_key_pos: ',nHeader_key_pos );
2434
2435 xProgress := '2440-60';
2436 c_file_common_key := RPAD(SUBSTRB(NVL(l_Header_tbl(nTrans_code_pos).value,' '),
2437 1, 25),
2438 25);
2439 ec_debug.pl ( 3, 'c_file_common_key: ',c_file_common_key );
2440
2441 xProgress := '2442-60';
2442 c_file_common_key := c_file_common_key ||
2443 RPAD(SUBSTRB(NVL(l_Header_tbl(nHeader_key_pos).value,' '),
2444 1, 22),
2445 22) ||
2446 RPAD(' ',22) ||
2447 RPAD(' ',22);
2448 ec_debug.pl ( 3, 'c_file_common_key: ',c_file_common_key );
2449
2450
2451 xProgress := '2450-60';
2452 ece_flatfile_pvt.write_to_ece_output ( cTransaction_Type,
2453 cCommunication_Method,
2454 cHeader_Interface,
2455 l_Header_tbl,
2456 iOutput_width,
2457 iRun_id,
2458 c_file_common_key );
2459
2460 /*** --------------------------------------------------------------
2461 *** With Header data at hand, we can assign values to
2462 *** place holders (foreign keys) in Header_detail_Select,
2463 *** Line_select and Line_detail_Select
2464 *** ------------------------------------------------------------***/
2465
2466 /*** -- set values into binding variables
2467 ***/
2468 xProgress := '2452-60';
2469 dbms_sql.bind_variable ( Header_1_sel_c, 'transaction_id', nTrans_id );
2470
2471 dbms_sql.bind_variable ( Header_1_sel_c, 'b2', iRun_id );
2472
2473 xProgress := '2454-60';
2474 dbms_sql.bind_variable ( Alw_chg_h_sel_c, 'transaction_id', nTrans_id );
2475
2476 dbms_sql.bind_variable ( Alw_chg_h_sel_c, 'b3', iRun_id );
2477
2478 xProgress := '2456-60';
2479 dbms_sql.bind_variable ( Line_sel_c, 'transaction_id', nTrans_id );
2480
2481 dbms_sql.bind_variable ( Line_sel_c, 'b4', iRun_id );
2482
2483 xProgress := '2458-60';
2484 dbms_sql.bind_variable ( Line_t_sel_c, 'transaction_id', nTrans_id );
2485
2486 dbms_sql.bind_variable ( Line_t_sel_c, 'b5', iRun_id );
2487
2488 xProgress := '2460-60';
2489 dbms_sql.bind_variable ( Alw_chg_l_sel_c, 'transaction_id', nTrans_id );
2490
2491 dbms_sql.bind_variable ( Alw_chg_l_sel_c, 'b6', iRun_id );
2492
2493 xProgress := '2470-60';
2494 dummy := dbms_sql.execute ( Header_1_sel_c );
2495
2496 /*****
2497 ***** -- header 1 loop starts here
2498 *****/
2499
2500 xProgress := '2480-60';
2501 WHILE dbms_sql.fetch_rows ( Header_1_sel_c ) > 0
2502 LOOP --- Header 1
2503
2504 /*****
2505 ***** store values in pl/sql table
2506 *****/
2507
2508 xProgress := '2490-60';
2509 FOR l IN 1..iHeader_1_count
2510 LOOP
2511 dbms_sql.column_value ( Header_1_sel_c,
2512 l,
2513 l_Header_1_tbl(l).value );
2514 END LOOP;
2515
2516 xProgress := '2500-60';
2517 dbms_sql.column_value ( Header_1_sel_c,
2518 iHeader_1_count + 1,
2519 rHeader_1_rowid );
2520
2521 xProgress := '2510-60';
2522 dbms_sql.column_value ( Header_1_sel_c,
2523 iHeader_1_count + 2,
2524 rHeader_1_X_rowid );
2525
2526 xProgress := '2520-60';
2527 ece_flatfile_pvt.write_to_ece_output( cTransaction_Type,
2528 cCommunication_Method,
2529 cHeader_1_Interface,
2530 l_Header_1_tbl,
2531 iOutput_width,
2532 iRun_id,
2533 c_file_common_key );
2534 xProgress := '2530-60';
2535 dbms_sql.bind_variable ( Header_1_del_c1,
2536 'col_rowid',
2537 rHeader_1_rowid );
2538
2539 -- xProgress := '2532-60';
2540 dbms_sql.bind_variable ( Header_1_del_c2,
2541 'col_rowid',
2542 rHeader_1_X_rowid );
2543
2544 xProgress := '2532-60';
2545 dummy := dbms_sql.execute ( Header_1_del_c1 );
2546
2547 xProgress := '2534-60';
2548 dummy := dbms_sql.execute ( Header_1_del_c2 );
2549
2550 /* Bug 1703536 - closed the end loop to end the header 1 loop */
2551
2552 END LOOP;
2553
2554 xProgress := '2536-60';
2555 IF ( dbms_sql.last_row_count = 0 ) THEN
2556 v_LevelProcessed := 'HEADER 1';
2557 ec_debug.pl ( 1,
2558 'EC',
2559 'ECE_NO_DB_ROW_PROCESSED',
2560 'PROGRESS_LEVEL',
2561 xProgress,
2562 'LEVEL_PROCESSED',
2563 v_LevelProcessed,
2564 'TRANSACTION_TYPE',
2565 cTransaction_Type );
2566 END IF;
2567
2568
2569 xProgress := '2538-60';
2570 dummy := dbms_sql.execute ( Alw_chg_h_sel_c );
2571
2572 /*****
2573 ***** -- Allowances and Charges Header loop starts here
2574 *****/
2575
2576 xProgress := '2540-60';
2577 WHILE dbms_sql.fetch_rows ( Alw_chg_h_sel_c ) > 0
2578 LOOP --- Allowances and Charges Header
2579
2580 /*****
2581 ***** store values in pl/sql table
2582 *****/
2583
2584 xProgress := '2550-60';
2585 FOR m IN 1..iAlw_chg_h_count
2586 LOOP
2587 dbms_sql.column_value ( Alw_chg_h_sel_c,
2588 m,
2589 l_alw_chg_h_tbl(m).value );
2590 END LOOP;
2591
2592 xProgress := '2560-60';
2593 dbms_sql.column_value ( Alw_chg_h_sel_c,
2594 iAlw_chg_h_count + 1,
2595 rAlw_chg_h_rowid );
2596
2597 xProgress := '2562-60';
2598 dbms_sql.column_value ( Alw_chg_h_sel_c,
2599 iAlw_chg_h_count + 2,
2600 rAlw_chg_X_rowid );
2601
2602 xProgress := '2570-60';
2603 ece_flatfile_pvt.write_to_ece_output ( cTransaction_Type,
2604 cCommunication_Method,
2605 cAlw_chg_Interface,
2606 l_alw_chg_h_tbl,
2607 iOutput_width,
2608 iRun_id,
2609 c_file_common_key );
2610
2611 /*****
2612 ***** -- allowances and charges header loop ends here
2613 *****/
2614
2615 /****
2616 **** -- Use rowid for delete
2617 ****/
2618
2619 xProgress := '2580-60';
2620 dbms_sql.bind_variable ( Alw_chg_h_del_c1,
2621 'col_rowid',
2622 rAlw_chg_h_rowid );
2623
2624 xProgress := '2582-60';
2625 dbms_sql.bind_variable ( Alw_chg_h_del_c2,
2626 'col_rowid',
2627 rAlw_chg_X_rowid );
2628
2629 xProgress := '2590-60';
2630 dummy := dbms_sql.execute ( Alw_chg_h_del_c1 );
2631
2632 xProgress := '2592-60';
2633 dummy := dbms_sql.execute ( Alw_chg_h_del_c2 );
2634
2635 END LOOP;
2636
2637 xProgress := '2594-60';
2638 IF ( dbms_sql.last_row_count = 0 )
2639 THEN
2640 v_LevelProcessed := 'ALLOWANCE CHARGES HEADER';
2641 ec_debug.pl ( 1,
2642 'EC',
2643 'ECE_NO_DB_ROW_PROCESSED',
2644 'PROGRESS_LEVEL',
2645 xProgress,
2646 'LEVEL_PROCESSED',
2647 v_LevelProcessed,
2648 'TRANSACTION_TYPE',
2649 cTransaction_Type );
2650 END IF;
2651
2652 xProgress := '2600-60';
2653 dummy := dbms_sql.execute ( Line_sel_c );
2654
2655 /*****
2656 ***** -- line loop starts here
2657 *****/
2658
2659 xProgress := '2610-60';
2660 WHILE dbms_sql.fetch_rows ( Line_sel_c ) > 0
2661 LOOP --- Line
2662
2663 /*****
2664 ***** store values in pl/sql table
2665 *****/
2666
2667 xProgress := '2620-60';
2668 FOR j IN 1..iLine_count
2669 LOOP
2670 dbms_sql.column_value ( Line_sel_c,
2671 j,
2672 l_Line_tbl(j).value );
2673 END LOOP;
2674
2675 xProgress := '2630-60';
2676 dbms_sql.column_value ( Line_sel_c,
2677 iLine_count + 1,
2678 rLine_rowid );
2679
2680 xProgress := '2632-60';
2681 dbms_sql.column_value ( Line_sel_c,
2682 iLine_count + 2,
2683 rLine_X_rowid );
2684
2685 xProgress := '2640-60';
2686 ece_flatfile_pvt.Find_pos ( l_Line_tbl,
2687 c_line_common_key_name,
2688 nLine_key_pos );
2689 ec_debug.pl ( 3, 'nLine_key_pos: ',nLine_key_pos );
2690
2691 xProgress := '2650-60';
2692 c_file_common_key := RPAD(SUBSTRB(NVL(l_Header_tbl(nTrans_code_pos).value,' '),
2693 1, 25),
2694 25) ||
2695 RPAD(SUBSTRB(NVL(l_Header_tbl(nHeader_key_pos).value,' '),
2696 1, 22),
2697 22) ||
2698 RPAD(SUBSTRB(NVL(l_Line_tbl(nLine_key_pos).value,' '),
2699 1, 22),
2700 22) ||
2701 RPAD(' ',22);
2702 ec_debug.pl ( 3, 'c_file_common_key: ',c_file_common_key );
2703
2704 xProgress := '2660-60';
2705 ece_flatfile_pvt.write_to_ece_output ( cTransaction_Type,
2706 cCommunication_Method,
2707 cLine_Interface,
2708 l_Line_tbl,
2709 iOutput_width,
2710 iRun_id,
2711 c_file_common_key );
2712
2713
2714
2715 /*****
2716 ***** set LINE_NUMBER values
2717 *****/
2718
2719 xProgress := '2670-60';
2720 dbms_sql.bind_variable ( Line_t_sel_c,
2721 'line_number',
2722 l_Line_tbl(nPos1).value );
2723
2724 xProgress := '2672-60';
2725 dbms_sql.bind_variable ( Alw_chg_l_sel_c,
2726 'line_number',
2727 l_Line_tbl(nPos1).value );
2728
2729
2730 xProgress := '2680-60';
2731 dummy := dbms_sql.execute ( Line_t_sel_c );
2732
2733 /*****
2734 ***** -- line tax loop starts here
2735 *****/
2736
2737 xProgress := '2690-60';
2738 WHILE dbms_sql.fetch_rows ( Line_t_sel_c ) > 0
2739 LOOP --- Line Tax
2740
2741 /*****
2742 ***** store values in pl/sql table
2743 *****/
2744
2745 xProgress := '2700-60';
2746 FOR k IN 1..iLine_t_count LOOP
2747 dbms_sql.column_value ( Line_t_sel_c, k, l_Line_t_tbl(k).value );
2748 END LOOP;
2749
2750 xProgress := '2710-60';
2751 dbms_sql.column_value ( Line_t_sel_c,
2752 iLine_t_count + 1,
2753 rLine_t_rowid );
2754
2755 xProgress := '2712-60';
2756 dbms_sql.column_value ( Line_t_sel_c,
2757 iLine_t_count + 2,
2758 rLine_t_X_rowid );
2759
2760 xProgress := '2720-60';
2761 ece_flatfile_pvt.Find_pos (l_Line_t_tbl,
2762 c_Line_t_common_key_name,
2763 nLine_t_key_pos );
2764 ec_debug.pl ( 3, 'nLine_t_key_pos: ',nLine_t_key_pos );
2765
2766 xProgress := '2730-60';
2767 c_file_common_key := RPAD(SUBSTRB(NVL(l_Header_tbl(nTrans_code_pos).value,' '),
2768 1, 25),
2769 25) ||
2770 RPAD(SUBSTRB(NVL(l_Header_tbl(nHeader_key_pos).value,' '),
2771 1, 22),
2772 22) ||
2773 RPAD(SUBSTRB(NVL(l_Line_tbl(nLine_key_pos).value,' '),
2774 1, 22),
2775 22) ||
2776 RPAD(SUBSTRB(NVL(l_Line_t_tbl(nLine_t_key_pos).value,' '),
2777 1, 22),
2778 22);
2779 ec_debug.pl ( 3, 'c_file_common_key: ',c_file_common_key );
2780
2781 xProgress := '2740-60';
2782 ece_flatfile_pvt.write_to_ece_output ( cTransaction_Type,
2783 cCommunication_Method,
2784 cLine_t_Interface,
2785 l_Line_t_tbl,
2786 iOutput_width,
2787 iRun_id,
2788 c_file_common_key );
2789
2790
2791
2792 xProgress := '2750-60';
2793 dbms_sql.bind_variable ( Line_t_del_c1,
2794 'col_rowid',
2795 rLine_t_rowid );
2796
2797 xProgress := '2752-60';
2798 dbms_sql.bind_variable ( Line_t_del_c2,
2799 'col_rowid',
2800 rLine_t_X_rowid );
2801
2802 xProgress := '2760-60';
2803 dummy := dbms_sql.execute ( Line_t_del_c1 );
2804
2805 xProgress := '2762-60';
2806 dummy := dbms_sql.execute ( Line_t_del_c2 );
2807
2808 END LOOP;
2809
2810 xProgress := '2764-60';
2811 IF ( dbms_sql.last_row_count = 0 )
2812 THEN
2813 v_LevelProcessed := 'LINE TAX';
2814 ec_debug.pl ( 1,
2815 'EC',
2816 'ECE_NO_DB_ROW_PROCESSED',
2817 'PROGRESS_LEVEL',
2818 xProgress,
2819 'LEVEL_PROCESSED',
2820 v_LevelProcessed,
2821 'TRANSACTION_TYPE',
2822 cTransaction_Type );
2823 END IF;
2824
2825 /****
2826 **** -- line tax loop ends here
2827 ****/
2828
2829 xProgress := '2770-60';
2830 dummy := dbms_sql.execute ( Alw_chg_l_sel_c );
2831
2832 /*****
2833 ***** -- Allowances and Charges Line loop starts here
2834 *****/
2835
2836 xProgress := '2780-60';
2837 WHILE dbms_sql.fetch_rows ( Alw_chg_l_sel_c ) > 0
2838 LOOP --- Allowances and Charges Line
2839
2840 /*****
2841 ***** store values in pl/sql table
2842 *****/
2843
2844 xProgress := '2790-60';
2845 FOR n IN 1..iAlw_chg_l_count
2846 LOOP
2847 dbms_sql.column_value ( Alw_chg_l_sel_c,
2848 n,
2849 l_alw_chg_l_tbl(n).value );
2850 END LOOP;
2851
2852 xProgress := '2800-60';
2853 dbms_sql.column_value ( Alw_chg_l_sel_c,
2854 iAlw_chg_l_count + 1,
2855 rAlw_chg_l_rowid );
2856
2857 xProgress := '2810-60';
2858 dbms_sql.column_value ( Alw_chg_l_sel_c,
2859 iAlw_chg_l_count + 2,
2860 rAlw_chg_X_rowid );
2861
2862 xProgress := '2820-60';
2863 ece_flatfile_pvt.write_to_ece_output ( cTransaction_Type,
2864 cCommunication_Method,
2865 cAlw_chg_Interface,
2866 l_alw_chg_l_tbl,
2867 iOutput_width,
2868 iRun_id,
2869 c_file_common_key );
2870
2871 /*****
2872 ***** -- allowances and charges line loop ends here
2873 *****/
2874
2875 /****
2876 **** -- Use rowid for delete
2877 ****/
2878
2879 xProgress := '2830-60';
2880 dbms_sql.bind_variable ( Alw_chg_l_del_c1,
2881 'col_rowid',
2882 rAlw_chg_l_rowid );
2883
2884 xProgress := '2832-60';
2885 dbms_sql.bind_variable ( Alw_chg_l_del_c2,
2886 'col_rowid',
2887 rAlw_chg_X_rowid );
2888
2889 xProgress := '2834-60';
2890 dummy := dbms_sql.execute ( Alw_chg_l_del_c1 );
2891
2892 xProgress := '2836-60';
2893 dummy := dbms_sql.execute ( Alw_chg_l_del_c2 );
2894
2895 END LOOP;
2896
2897 xProgress := '2838-60';
2898 IF ( dbms_sql.last_row_count = 0 ) THEN
2899 v_LevelProcessed := 'ALLOWANCE CHARAGES LINE';
2900 ec_debug.pl ( 1,
2901 'EC',
2902 'ECE_NO_DB_ROW_PROCESSED',
2903 'PROGRESS_LEVEL',
2904 xProgress,
2905 'LEVEL_PROCESSED',
2906 v_LevelProcessed,
2907 'TRANSACTION_TYPE',
2908 cTransaction_Type );
2909 END IF;
2910
2911 /****
2912 **** -- Use rowid for delete
2913 ****/
2914
2915 xProgress := '2840-60';
2916 dbms_sql.bind_variable ( Line_del_c1,
2917 'col_rowid',
2918 rLine_rowid );
2919
2920 xProgress := '2842-60';
2921 dbms_sql.bind_variable ( Line_del_c2,
2922 'col_rowid',
2923 rLine_X_rowid );
2924
2925 xProgress := '2850-60';
2926 dummy := dbms_sql.execute ( Line_del_c1 );
2927
2928 xProgress := '2852-60';
2929 dummy := dbms_sql.execute ( Line_del_c2 );
2930
2931 END LOOP;
2932
2933 xProgress := '2854-60';
2934 IF ( dbms_sql.last_row_count = 0 )
2935 THEN
2936 v_LevelProcessed := 'LINE';
2937 ec_debug.pl ( 1,
2938 'EC',
2939 'ECE_NO_DB_ROW_PROCESSED',
2940 'PROGRESS_LEVEL',
2941 xProgress,
2942 'LEVEL_PROCESSED',
2943 v_LevelProcessed,
2944 'TRANSACTION_TYPE',
2945 cTransaction_Type );
2946 END IF;
2947
2948 /***
2949 *** -- line loop ends here
2950 ***/
2951 /* Bug 1703536 -
2952 ** Commented the following code and moved it to the end of the
2953 ** header 1 loop.
2954 */
2955
2956 /****
2957 xProgress := '2860-60';
2958 dbms_sql.bind_variable ( Header_1_del_c1,
2959 'col_rowid',
2960 rHeader_1_rowid );
2961
2962 xProgress := '2862-60';
2963 dbms_sql.bind_variable ( Header_1_del_c2,
2964 'col_rowid',
2965 rHeader_1_X_rowid );
2966
2967 xProgress := '2870-60';
2968 dummy := dbms_sql.execute ( Header_1_del_c1 );
2969
2970 xProgress := '2872-60';
2971 dummy := dbms_sql.execute ( Header_1_del_c2 );
2972
2973 END LOOP;
2974
2975 xProgress := '2874-60';
2976 IF ( dbms_sql.last_row_count = 0 ) THEN
2977 v_LevelProcessed := 'HEADER 1';
2978 ec_debug.pl ( 1,
2979 'EC',
2980 'ECE_NO_DB_ROW_PROCESSED',
2981 'PROGRESS_LEVEL',
2982 xProgress,
2983 'LEVEL_PROCESSED',
2984 v_LevelProcessed,
2985 'TRANSACTION_TYPE',
2986 cTransaction_Type );
2987 END IF;
2988
2989 ****/
2990 /***
2991 *** -- header 1 loop ends here
2992 ***/
2993
2994 xProgress := '2880-60';
2995 dbms_sql.bind_variable ( Header_del_c1,
2996 'col_rowid',
2997 rHeader_rowid );
2998
2999 xProgress := '2882-60';
3000 dbms_sql.bind_variable ( Header_del_c2,
3001 'col_rowid',
3002 rHeader_X_rowid );
3003
3004 xProgress := '2890-60';
3005 dummy := dbms_sql.execute ( Header_del_c1 );
3006
3007 xProgress := '2892-60';
3008 dummy := dbms_sql.execute ( Header_del_c2 );
3009
3010 END LOOP;
3011
3012 xProgress := '2894-60';
3013 IF ( dbms_sql.last_row_count = 0 ) THEN
3014 v_LevelProcessed := 'HEADER';
3015 ec_debug.pl ( 1,
3016 'EC',
3017 'ECE_NO_DB_ROW_PROCESSED',
3018 'PROGRESS_LEVEL',
3019 xProgress,
3020 'LEVEL_PROCESSED',
3021 v_LevelProcessed,
3022 'TRANSACTION_TYPE',
3023 cTransaction_Type );
3024 END IF;
3025
3026 /***
3027 *** -- header loop ends here
3028 ***/
3029
3030 /*** -- this commit is to make sure all data is deleted from interface tables
3031 ***/
3032
3033 xProgress := '2900-60';
3034 -- COMMIT;
3035
3036 /***
3037 *** -- close all open cursors here
3038 ***/
3039 xProgress := '2910-60';
3040 dbms_sql.close_cursor ( Header_sel_c );
3041
3042 xProgress := '2911-60';
3043 dbms_sql.close_cursor ( Header_1_sel_c );
3044
3045 xProgress := '2912-60';
3046 dbms_sql.close_cursor ( Alw_chg_h_sel_c );
3047
3048 xProgress := '2913-60';
3049 dbms_sql.close_cursor ( Line_sel_c );
3050
3051 xProgress := '2914-60';
3052 dbms_sql.close_cursor ( Line_t_sel_c );
3053
3054 xProgress := '2915-60';
3055 dbms_sql.close_cursor ( Alw_chg_l_sel_c );
3056
3057 xProgress := '2916-60';
3058 dbms_sql.close_cursor ( Header_del_c1 );
3059
3060 xProgress := '2917-60';
3061 dbms_sql.close_cursor ( Header_1_del_c1 );
3062
3063 xProgress := '2918-60';
3064 dbms_sql.close_cursor ( Alw_chg_h_del_c1 );
3065
3066 xProgress := '2919-60';
3067 dbms_sql.close_cursor ( Line_del_c1 );
3068
3069 xProgress := '2920-60';
3070 dbms_sql.close_cursor ( Line_t_del_c1 );
3071
3072 xProgress := '2921-60';
3073 dbms_sql.close_cursor ( Alw_chg_l_del_c1 );
3074
3075 ec_debug.pop('ece_ar_transaction.Put_Data_To_Output_Table');
3076
3077 EXCEPTION
3078 WHEN OTHERS THEN
3079
3080 ec_debug.pl ( 0,
3081 'EC',
3082 'ECE_PROGRAM_ERROR',
3083 'PROGRESS_LEVEL',
3084 xProgress );
3085
3086 ec_debug.pl ( 0,
3087 'EC',
3088 'ECE_ERROR_MESSAGE',
3089 'ERROR_MESSAGE',
3090 SQLERRM );
3091
3092 app_exception.raise_exception;
3093
3094 END Put_Data_To_Output_Table;
3095
3096 /* --------------------------------------------------------------------------*/
3097
3098 -- PROCEDURE Populate_AR_Trx
3099 -- This procedure has the following functionalities:
3100 -- 1. Build SQL statement dynamically to extract data from
3101 -- Base Application Tables.
3102 -- 2. Execute the dynamic SQL statement.
3103 -- 3. Assign data into 2-dim PL/SQL table
3104 -- 4. Pass data to the code conversion mechanism
3105 -- 5. Populate the Interface tables with the extracted data.
3106 -- --------------------------------------------------------------------------
3107
3108 PROCEDURE Populate_AR_Trx ( cCommunication_Method IN VARCHAR2,
3109 cTransaction_Type IN VARCHAR2,
3110 iOutput_width IN INTEGER,
3111 dTransaction_date IN DATE,
3112 iRun_id IN INTEGER,
3113 cHeader_Interface IN VARCHAR2,
3114 cHeader_1_Interface IN VARCHAR2,
3115 cAlw_Chg_Interface IN VARCHAR2,
3116 cLine_Interface IN VARCHAR2,
3117 cLine_t_Interface IN VARCHAR2,
3118 cCreate_Date_From IN DATE,
3119 cCreate_Date_To IN DATE,
3120 cCustomer_Name IN VARCHAR2,
3121 cSite_Use_Code IN VARCHAR2,
3122 cDocument_Type IN VARCHAR2,
3123 cTransaction_Number IN VARCHAR2 )
3124
3125 IS
3126
3127 /**
3128 This should be a parameter in the next version to distinguish between MAPS.
3129 For now, it will be hardcoded to NULL so the default will be the seeded FF transaction
3130 **/
3131 cMap_id NUMBER := NULL;
3132
3133 l_header_tbl ece_flatfile_pvt.Interface_tbl_type;
3134 l_header_1_tbl ece_flatfile_pvt.Interface_tbl_type;
3135 l_alw_chg_tbl ece_flatfile_pvt.Interface_tbl_type;
3136 l_line_tbl ece_flatfile_pvt.Interface_tbl_type;
3137 l_line_t_tbl ece_flatfile_pvt.Interface_tbl_type;
3138 l_key_tbl ece_flatfile_pvt.Interface_tbl_type;
3139 -- l_veh_alw_chg_tbl veh_allowance_charge_sv.tab_for_allowance_charge;
3140
3141 Header_sel_c INTEGER;
3142 Header_1_sel_c INTEGER;
3143 Alw_chg_sel_c INTEGER;
3144 Line_sel_c INTEGER;
3145 Line_t_sel_c INTEGER;
3146
3147 cHeader_select VARCHAR2( 32000);
3148 cHeader_1_select VARCHAR2( 32000);
3149 cAlw_chg_select VARCHAR2( 32000);
3150 cLine_select VARCHAR2( 32000);
3151 cLine_t_select VARCHAR2( 32000);
3152
3153 cHeader_from VARCHAR2( 32000);
3154 cHeader_1_from VARCHAR2( 32000);
3155 cAlw_chg_from VARCHAR2( 32000);
3156 cLine_from VARCHAR2( 32000);
3157 cLine_t_from VARCHAR2( 32000);
3158
3159 cHeader_where VARCHAR2( 32000);
3160 cHeader_1_where VARCHAR2( 32000);
3161 cAlw_chg_where VARCHAR2( 32000);
3162 cLine_where VARCHAR2( 32000);
3163 cLine_t_where VARCHAR2( 32000);
3164
3165 iHeader_count NUMBER := 0;
3166 iHeader_1_count NUMBER := 0;
3167 iAlw_chg_count NUMBER := 0;
3168 iLine_count NUMBER := 0;
3169 iLine_t_count NUMBER := 0;
3170 iKey_count NUMBER := 0;
3171
3172 l_header_fkey NUMBER;
3173 l_header_1_fkey NUMBER;
3174 l_alw_chg_fkey NUMBER;
3175 l_line_fkey NUMBER;
3176 l_line_t_fkey NUMBER;
3177
3178 n_trx_date_pos NUMBER;
3179 n_runid_pos NUMBER;
3180
3181 dummy INTEGER;
3182 nPos1 NUMBER;
3183 nPos2 NUMBER;
3184 nPos3 NUMBER;
3185 nPos4 NUMBER;
3186 nPos5 NUMBER;
3187 nPos6 NUMBER;
3188 nPos7 NUMBER;
3189 nPos8 NUMBER;
3190 nPos9 NUMBER;
3191 nPos10 NUMBER;
3192 nPos11 NUMBER;
3193 nPos12 NUMBER;
3194 nPos13 NUMBER;
3195 nPos14 NUMBER;
3196 nPos15 NUMBER;
3197 nPos16 NUMBER;
3198 nPos17 NUMBER;
3199 nPos18 NUMBER;
3200 nPos19 NUMBER;
3201 nPos20 NUMBER;
3202 nPos21 NUMBER;
3203 nPos23 NUMBER;
3204 nPos24 NUMBER;
3205 nPos25 NUMBER;
3206 nPos26 NUMBER;
3207 nPos27 NUMBER;
3208 nPos28 NUMBER; -- Bug 2389231
3209 pos_1 NUMBER;
3210 nTrans_id NUMBER;
3211
3212 l_net_weight NUMBER:=0;
3213 l_gross_weight NUMBER:=0;
3214 l_volume NUMBER:=0;
3215 l_weight_uom_code VARCHAR2(3);
3216 l_volume_uom_code VARCHAR2(3);
3217 l_shipment_number NUMBER;
3218 l_booking_number VARCHAR2(30);
3219
3220 l_weight_uom_code_ext1 VARCHAR2(3); -- bug 1979725 begin
3221 l_weight_uom_code_ext2 VARCHAR2(3);
3222 l_weight_uom_code_ext3 VARCHAR2(3);
3223 l_weight_uom_code_ext4 VARCHAR2(3);
3224 l_weight_uom_code_ext5 VARCHAR2(3);
3225
3226 l_volume_uom_code_ext1 VARCHAR2(3);
3227 l_volume_uom_code_ext2 VARCHAR2(3);
3228 l_volume_uom_code_ext3 VARCHAR2(3);
3229 l_volume_uom_code_ext4 VARCHAR2(3);
3230 l_volume_uom_code_ext5 VARCHAR2(3); -- bug 1979725 end
3231
3232 l_remit_to_address1 VARCHAR2(240);
3233 l_remit_to_address2 VARCHAR2(240);
3234 l_remit_to_address3 VARCHAR2(240);
3235 l_remit_to_address4 VARCHAR2(240);
3236 l_remit_to_city VARCHAR2(60);
3237 l_remit_to_county VARCHAR2(60);
3238 l_remit_to_state VARCHAR2(60);
3239 l_remit_to_province VARCHAR2(60);
3240 l_remit_to_country VARCHAR2(60);
3241 l_remit_to_postal_code VARCHAR2(60);
3242 l_remit_to_customer_name VARCHAR2(50); --2291130
3243 l_remit_to_edi_location_code VARCHAR2(40); --2386848
3244 l_Multiple_Installments_Flag VARCHAR2(1);
3245 l_Maximum_Installment_Number NUMBER;
3246 l_Amount_Tax_Due NUMBER;
3247 l_Amount_Charges_Due NUMBER;
3248 l_Amount_Freight_Due NUMBER;
3249 l_Amount_Line_Items_Due NUMBER;
3250 l_total_amount_due NUMBER;
3251 l_Discount_Percent1 NUMBER;
3252 l_Discount_Days1 NUMBER;
3253 l_Discount_Date1 DATE;
3254 l_Discount_Day_Of_Month1 NUMBER;
3255 l_Discount_Months_Forward1 NUMBER;
3256 l_Discount_Percent2 NUMBER;
3257 l_Discount_Days2 NUMBER;
3258 l_Discount_Date2 DATE;
3259 l_Discount_Day_Of_Month2 NUMBER;
3260 l_Discount_Months_Forward2 NUMBER;
3261 l_Discount_Percent3 NUMBER;
3262 l_Discount_Days3 NUMBER;
3263 l_Discount_Date3 DATE;
3264 l_Discount_Day_Of_Month3 NUMBER;
3265 l_Discount_Months_Forward3 NUMBER;
3266 l_line_item_number VARCHAR2(100);
3267 l_line_item_attrib_category VARCHAR2( 30);
3268 -- BUG:4451874 Length changed to 240 characters
3269 l_line_item_attribute1 VARCHAR2(240);
3270 l_line_item_attribute2 VARCHAR2(240);
3271 l_line_item_attribute3 VARCHAR2(240);
3272 l_line_item_attribute4 VARCHAR2(240);
3273 l_line_item_attribute5 VARCHAR2(240);
3274 l_line_item_attribute6 VARCHAR2(240);
3275 l_line_item_attribute7 VARCHAR2(240);
3276 l_line_item_attribute8 VARCHAR2(240);
3277 l_line_item_attribute9 VARCHAR2(240);
3278 l_line_item_attribute10 VARCHAR2(240);
3279 l_line_item_attribute11 VARCHAR2(240);
3280 l_line_item_attribute12 VARCHAR2(240);
3281 l_line_item_attribute13 VARCHAR2(240);
3282 l_line_item_attribute14 VARCHAR2(240);
3283 l_line_item_attribute15 VARCHAR2(240);
3284 l_allowance_charge_indicator VARCHAR2(1);
3285 l_charge_code VARCHAR2(50);
3286 l_special_charges_code VARCHAR2(50);
3287 l_special_services_code VARCHAR2(50);
3288 l_method_handling_code VARCHAR2(50);
3289 init_msg_list VARCHAR2(20);
3290 simulate VARCHAR2(20);
3291 validation_level VARCHAR2(20);
3292 commt VARCHAR2(20);
3293 return_status VARCHAR2(20);
3294 msg_count VARCHAR2(20);
3295 msg_data VARCHAR2(2000);
3296 l_remit_to_code_ext VARCHAR2( 35);
3297 l_remit_to_code_int VARCHAR2(240);
3298 l_sold_to_customer_code_ext VARCHAR2( 35);
3299 l_ship_to_customer_code_ext VARCHAR2( 35);
3300 l_bill_to_code_ext VARCHAR2( 35);
3301 l_bill_to_tp_reference_ext1 VARCHAR2(240);
3302 l_bill_to_tp_reference_ext2 VARCHAR2(240);
3303 l_ship_to_tp_reference_ext1 VARCHAR2(240);
3304 l_ship_to_tp_reference_ext2 VARCHAR2(240);
3305 l_sold_to_tp_reference_ext1 VARCHAR2(240);
3306 l_sold_to_tp_reference_ext2 VARCHAR2(240);
3307 l_remit_to_tp_reference_ext1 VARCHAR2(240);
3308 l_remit_to_tp_reference_ext2 VARCHAR2(240);
3309 l_reference_ext1 VARCHAR2(240);
3310 l_reference_ext2 VARCHAR2(240);
3311 l_last_update_date DATE;
3312 l_line_type VARCHAR2(20);
3313 l_cust_trx_line_id NUMBER;
3314 l_header_detail_ind VARCHAR2(1);
3315 l_data_found BOOLEAN := FALSE;
3316 l_delivery_id NUMBER;
3317 l_delivery_name VARCHAR2(30);
3318 l_output_level VARCHAR2(1) := NULL;
3319 l_alw_chg_output_level VARCHAR2(1);
3320 l_bill_to_contact_id NUMBER; /*2945057*/
3321 l_ship_to_contact_id NUMBER;
3322 l_sold_to_contact_id NUMBER;
3323 l_bill_to_contact_first_name VARCHAR2(240);
3324 l_bill_to_contact_last_name VARCHAR2(240);
3325 l_bill_to_contact_job_title VARCHAR2(240);
3326 l_ship_to_contact_first_name VARCHAR2(240);
3327 l_ship_to_contact_last_name VARCHAR2(240);
3328 l_ship_to_contact_job_title VARCHAR2(240);
3329 l_sold_to_contact_first_name VARCHAR2(240);
3330 l_sold_to_contact_last_name VARCHAR2(240);
3331 l_sold_to_contact_job_title VARCHAR2(240); /*2945057*/
3332
3333 v_LevelProcessed VARCHAR2(40);
3334
3335 d_dummy_date DATE;
3336 CURSOR c_header_1
3337 (tx_id IN NUMBER) IS
3338 SELECT
3339 RTRIM(WTP.VEHICLE_NUM_PREFIX, '0123456789') EQUIPMENT_PREFIX ,
3340 SUBSTR(WTP.VEHICLE_NUMBER, NVL(LENGTH(RTRIM(WTP.VEHICLE_NUMBER, '0123456789')), 0)+1) EQUIPMENT_NUMBER,
3341 SUBSTR(WTP.ROUTING_INSTRUCTIONS, 1, 150) ROUTING_INSTRUCTIONS,
3342 WDI.SEQUENCE_NUMBER PACKING_SLIP_NUMBER
3343 FROM
3344 WSH_DELIVERY_DETAILS
3345 WDD ,
3346 WSH_DELIVERY_LEGS
3347 WDL,
3348 WSH_TRIP_STOPS
3349 WTS,
3350 WSH_TRIPS
3351 WTP,
3352 WSH_DOCUMENT_INSTANCES
3353 WDI,
3354 RA_CUSTOMER_TRX_LINES
3355 RCTL
3356 WHERE
3357 TO_NUMBER(RCTL.INTERFACE_LINE_ATTRIBUTE6) =
3358 WDD.SOURCE_LINE_ID AND
3359 TO_NUMBER(RCTL.INTERFACE_LINE_ATTRIBUTE3) =
3360 WDL.DELIVERY_ID AND
3361 WDL.PICK_UP_STOP_ID = WTS.STOP_ID AND
3362 WTS.TRIP_ID = WTP.TRIP_ID AND
3363 NVL(TO_NUMBER(RCTL.INTERFACE_LINE_ATTRIBUTE3),0) =
3364 WDI.ENTITY_ID AND
3365 RCTL.customer_trx_id = tx_id
3366 AND RCTL.INTERFACE_LINE_CONTEXT =
3367 fnd_profile.value('ONT_SOURCE_CODE')
3368 AND ROWNUM = 1;
3369
3370 BEGIN
3371
3372 ec_debug.push ( 'ece_ar_transaction.Populate_AR_Trx' );
3373 ec_debug.pl ( 3, 'cCommunication_Method: ', cCommunication_Method );
3374 ec_debug.pl ( 3, 'cTransaction_Type: ',cTransaction_Type );
3375 ec_debug.pl ( 3, 'iOutput_width: ',iOutput_width );
3376 ec_debug.pl ( 3, 'dTransaction_date: ',dTransaction_date );
3377 ec_debug.pl ( 3, 'iRun_id: ',iRun_id );
3378 ec_debug.pl ( 3, 'cHeader_Interface: ',cHeader_Interface );
3379 ec_debug.pl ( 3, 'cHeader_1_Interface: ',cHeader_1_Interface );
3380 ec_debug.pl ( 3, 'cAlw_Chg_Interface: ',cAlw_Chg_Interface );
3381 ec_debug.pl ( 3, 'cLine_Interface: ',cLine_Interface );
3382 ec_debug.pl ( 3, 'cLine_t_Interface: ',cLine_t_Interface );
3383 ec_debug.pl ( 3, 'cCreate_Date_From: ',cCreate_Date_From );
3384 ec_debug.pl ( 3, 'cCreate_Date_To: ',cCreate_Date_To );
3385 ec_debug.pl ( 3, 'cCustomer_Name: ',cCustomer_Name );
3386 ec_debug.pl ( 3, 'cSite_Use_Code: ',cSite_Use_Code );
3387 ec_debug.pl ( 3, 'cDocument_Type: ',cDocument_Type );
3388 ec_debug.pl ( 3, 'cTransaction_Number: ',cTransaction_Number );
3389
3390 xProgress := '2000-70';
3391 ece_flatfile_pvt.init_table(cTransaction_Type,cHeader_Interface,NULL,FALSE,l_header_tbl,l_key_tbl);
3392
3393 xProgress := '2020-70';
3394 l_key_tbl := l_header_tbl;
3395
3396 xProgress := '2022-70';
3397 iKey_count := l_header_tbl.count;
3398
3399 xProgress := '2030-70';
3400 ece_flatfile_pvt.init_table(cTransaction_Type,cHeader_1_Interface,NULL,TRUE,l_header_1_tbl,l_key_tbl);
3401
3402
3403 /*
3404 * The output level is passed on for Allowance Charges table because the Interface
3405 * Table ECE_AR_TRX_ALLOWANCE_CHARGES is referenced more than one in the
3406 * ECE_INTERFACE_COLUMNS
3407 */
3408
3409 xProgress := '2060-70';
3410 BEGIN
3411 SELECT MIN(eel.external_level)
3412 INTO l_alw_chg_output_level
3413 FROM ece_interface_tables eit,
3414 ece_level_matrices elm,
3415 ece_external_levels eel
3416 WHERE eit.interface_table_name = 'ECE_AR_TRX_ALLOWANCE_CHARGES'
3417 AND eit.transaction_type = cTransaction_type
3418 AND eit.interface_table_id = elm.interface_table_id
3419 AND elm.external_level_id = eel.external_level_id
3420 AND eel.map_id = (SELECT NVL(cMap_id, MAX(em1.map_id))
3421 FROM ece_mappings em1
3422 WHERE em1.map_code like 'EC_'||RTRIM(LTRIM(NVL(cTransaction_type,'%')))||'_FF');
3423 EXCEPTION
3424 WHEN NO_DATA_FOUND THEN
3425 ec_debug.pl ( 1,
3426 'EC',
3427 'ECE_NO_ROW_SELECTED',
3428 'PROGRESS_LEVEL',
3429 xProgress,
3430 'INFO',
3431 'MINIMUM OUTPUT LEVEL',
3432 'TABLE_NAME',
3433 'ECE_INTERFACE_TABLES' );
3434 END;
3435 ec_debug.pl ( 3, 'l_alw_chg_output_level: ',l_alw_chg_output_level );
3436
3437
3438 xProgress := '2070-70';
3439 ece_flatfile_pvt.init_table(cTransaction_Type,cAlw_chg_Interface,l_alw_chg_output_level,TRUE,l_alw_chg_tbl,l_key_tbl);
3440
3441 xProgress := '2100-70';
3442 ece_flatfile_pvt.init_table(cTransaction_Type,cLine_Interface,NULL,TRUE,l_line_tbl,l_key_tbl);
3443
3444 xProgress := '2130-70';
3445 ece_flatfile_pvt.init_table(cTransaction_Type,cLine_t_Interface,NULL,TRUE,l_line_t_tbl,l_key_tbl);
3446
3447 -- ***************************************************************************
3448 --
3449 -- Here, I am building the SELECT, FROM, and WHERE clauses for the dynamic
3450 -- SQL call
3451 -- The ece_extract_utils_pub.select_clause uses the EDI data dictionary for the build.
3452 --
3453 -- **************************************************************************
3454
3455 xProgress := '2160-70';
3456 ece_extract_utils_pub.select_clause ( cTransaction_Type,
3457 cCommunication_Method,
3458 cHeader_Interface,
3459 l_header_tbl,
3460 cHeader_select,
3461 cHeader_from,
3462 cHeader_where );
3463
3464 xProgress := '2170-70';
3465 ece_extract_utils_pub.select_clause ( cTransaction_Type,
3466 cCommunication_Method,
3467 cHeader_1_Interface,
3468 l_header_1_tbl,
3469 cHeader_1_select,
3470 cHeader_1_from,
3471 cHeader_1_where );
3472
3473 xProgress := '2180-70';
3474 ece_extract_utils_pub.select_clause ( cTransaction_Type,
3475 cCommunication_Method,
3476 cAlw_chg_Interface,
3477 l_alw_chg_tbl,
3478 cAlw_chg_select,
3479 cAlw_chg_from,
3480 cAlw_chg_where );
3481
3482 xProgress := '2190-70';
3483 ece_extract_utils_pub.select_clause ( cTransaction_Type,
3484 cCommunication_Method,
3485 cLine_Interface,
3486 l_line_tbl,
3487 cLine_select,
3488 cLine_from ,
3489 cLine_where );
3490
3491 xProgress := '2200-70';
3492 ece_extract_utils_pub.select_clause ( cTransaction_Type,
3493 cCommunication_Method,
3494 cLine_t_Interface,
3495 l_line_t_tbl,
3496 cLine_t_select,
3497 cLine_t_from ,
3498 cLine_t_where );
3499
3500 -- **************************************************************************
3501 -- Here, I am customizing the WHERE clause to join the Interface tables together.
3502 -- i.e. Headers -- Lines -- Line Details
3503 --
3504 -- Select Data1, Data2, Data3...........
3505 -- From Header_View
3506 -- Where A.Transaction_Record_ID = D.Transaction_Record_ID (+)
3507 -- and B.Transaction_Record_ID = E.Transaction_Record_ID (+)
3508 -- and C.Transaction_Record_ID = F.Transaction_Record_ID (+)
3509 -- ******* (Customization should be added here) ********
3510 -- and A.Communication_Method = 'EDI'
3511 -- and A.xxx = B.xxx ........
3512 -- and B.yyy = C.yyy .......
3513 -- **************************************************************************
3514 -- **************************************************************************
3515 -- :transaction_id is a place holder for foreign key value.
3516 -- A PL/SQL table (list of values) will be used to store data.
3517 -- Procedure ece_flatfile.Find_pos will be used to locate the specific
3518 -- data value in the PL/SQL table.
3519 -- dbms_sql (Native Oracle db functions that come with every Oracle Apps)
3520 -- dbms_sql.bind_variable will be used to assign data value to :transaction_id.
3521 --
3522 -- Let's use the above example:
3523 --
3524 -- 1. Execute dynamic SQL 1 for headers (A) data
3525 -- Get value of A.xxx (foreign key to B)
3526 --
3527 -- 2. bind value A.xxx to variable B.xxx
3528 --
3529 -- 3. Execute dynamic SQL 2 for lines (B) data
3530 -- Get value of B.yyy (foreigh key to C)
3531 --
3532 -- 4. bind value B.yyy to variable C.yyy
3533 --
3534 -- 5. Execute dynamic SQL 3 for line_details (C) data
3535 -- **************************************************************************
3536 -- **************************************************************************
3537 -- Change the following few lines as needed
3538 -- **************************************************************************
3539
3540
3541 xProgress := '2210-70';
3542 cHeader_where := cHeader_where ||
3543 'ECE_INO_HEADER_V.COMMUNICATION_METHOD =' ||
3544 '''' ||
3545 cCommunication_Method ||
3546 '''';
3547
3548 xProgress := '2220-70';
3549 IF cCreate_Date_From IS NOT NULL
3550 THEN
3551 xProgress := '2222-70';
3552 cHeader_where := cHeader_where ||
3553 ' AND ' ||
3554 'ECE_INO_HEADER_V.CREATION_DATE >=' ||
3555 ':l_cCreate_Date_From';
3556 END IF;
3557
3558 xProgress := '2230-70';
3559 IF cCreate_Date_To IS NOT NULL
3560 THEN
3561 xProgress := '2232-70';
3562 cHeader_where := cHeader_where ||
3563 ' AND ' ||
3564 'ECE_INO_HEADER_V.CREATION_DATE <=' ||
3565 ':l_cCreate_Date_To';
3566 END IF;
3567
3568 xProgress := '2240-70';
3569 IF cCustomer_Name IS NOT NULL
3570 THEN
3571 xProgress := '2242-70';
3572 cHeader_where := cHeader_where ||
3573 ' AND ' ||
3574 'ECE_INO_HEADER_V.BILL_TO_CUSTOMER_NAME =' ||
3575 ':l_cCustomer_Name';
3576 END IF;
3577
3578 xProgress := '2250-70';
3579 IF cSite_Use_Code IS NOT NULL
3580 THEN
3581 xProgress := '2252-70';
3582 cHeader_where := cHeader_where ||
3583 ' AND ' ||
3584 'ECE_INO_HEADER_V.SITE_USE_CODE =' ||
3585 ':l_cSite_Use_Code';
3586 END IF;
3587
3588 xProgress := '2260-70';
3589 IF cDocument_Type IS NOT NULL
3590 THEN
3591 xProgress := '2262-70';
3592 cHeader_where := cHeader_where ||
3593 ' AND ' ||
3594 'ECE_INO_HEADER_V.DOCUMENT_TYPE =' ||
3595 ':l_cDocument_Type';
3596 END IF;
3597
3598 xProgress := '2270-70';
3599 IF cTransaction_Number IS NOT NULL
3600 THEN
3601 xProgress := '2272-70';
3602 cHeader_where := cHeader_where ||
3603 ' AND ' ||
3604 'ECE_INO_HEADER_V.TRANSACTION_NUMBER =' ||
3605 ':l_cTransaction_Number';
3606 END IF;
3607 ec_debug.pl ( 3, 'cHeader_where: ',cHeader_where );
3608
3609 xProgress := '2280-70';
3610 cHeader_1_where := cHeader_1_where ||
3611 'ECE_INO_HEADER_1_V.TRANSACTION_ID = :transaction_id AND ROWNUM = 1';
3612 ec_debug.pl ( 3, 'cHeader_1_where: ',cHeader_1_where );
3613
3614 xProgress := '2290-70';
3615 cAlw_chg_where := cAlw_chg_where ||
3616 'ECE_INO_ALLOWANCE_CHARGES_V.TRANSACTION_ID = :transaction_id';
3617 ec_debug.pl ( 3, 'cAlw_chg_where: ',cAlw_chg_where );
3618
3619 xProgress := '2300-70';
3620 cLine_where := cLine_where ||
3621 'ECE_INO_LINE_V.TRANSACTION_ID = :transaction_id';
3622 ec_debug.pl ( 3, 'cLine_where: ',cLine_where );
3623
3624 xProgress := '2310-70';
3625 cLine_t_where := cLine_t_where ||
3626 'ECE_INO_LINE_TAX_V.TRANSACTION_ID = :transaction_id' ||
3627 ' AND ' ||
3628 'ECE_INO_LINE_TAX_V.LINE_NUMBER = :line_number';
3629 ec_debug.pl ( 3, 'cLine_t_where: ',cLine_t_where );
3630
3631 -- **********************************************************************************
3632 -- If Allowance and Charges functionality becomes part of Standard EDI Gateway then
3633 -- remove check on automotive product being installed without modifying the where
3634 -- clause.
3635 -- **********************************************************************************
3636
3637 xProgress := '2320-70';
3638 IF l_Automotive_Installed THEN
3639 xProgress := '2330-70';
3640 cLine_t_where := cLine_t_where ||
3641 ' AND ' ||
3642 'ECE_INO_LINE_TAX_V.LINE_TYPE <>''FREIGHT''';
3643 ec_debug.pl ( 3, 'cLine_t_where: ',cLine_t_where );
3644 END IF;
3645
3646 xProgress := '2340-70';
3647 cHeader_select := cHeader_select ||
3648 cHeader_from ||
3649 cHeader_where ||
3650 ' ORDER BY BILL_TO_CUSTOMER_NAME,BILL_TO_CUSTOMER_LOCATION'; /* Bug 2464584 */
3651
3652 cHeader_1_select := cHeader_1_select ||
3653 cHeader_1_from ||
3654 cHeader_1_where;
3655
3656 cAlw_chg_select := cAlw_chg_select ||
3657 cAlw_chg_from ||
3658 cAlw_chg_where;
3659
3660 cLine_select := cLine_select ||
3661 cLine_from ||
3662 cLine_where;
3663
3664 cLine_t_select := cLine_t_select ||
3665 cLine_t_from ||
3666 cLine_t_where;
3667
3668 ec_debug.pl ( 3, 'cHeader_select: ',cHeader_select );
3669 ec_debug.pl ( 3, 'cHeader_1_select: ',cHeader_1_select );
3670 ec_debug.pl ( 3, 'cAlw_chg_select: ',cAlw_chg_select );
3671 ec_debug.pl ( 3, 'cLine_select: ',cLine_select );
3672 ec_debug.pl ( 3, 'cLine_t_select: ',cLine_t_select );
3673
3674
3675
3676 -- ***************************************************
3677 -- ***
3678 -- *** Get data setup for the dynamic SQL call.
3679 -- ***
3680 -- *** Open a cursor for each of the SELECT call
3681 -- *** This tells the database to reserve spaces
3682 -- *** for the data returned by the SQL statement
3683 -- ***
3684 -- ***************************************************
3685
3686 xProgress := '2350-70';
3687 Header_sel_c := dbms_sql.open_cursor;
3688
3689 xProgress := '2352-70';
3690 Header_1_sel_c := dbms_sql.open_cursor;
3691
3692 xProgress := '2354-70';
3693 Alw_chg_sel_c := dbms_sql.open_cursor;
3694
3695 xProgress := '2356-70';
3696 Line_sel_c := dbms_sql.open_cursor;
3697
3698 xProgress := '2358-70';
3699 Line_t_sel_c := dbms_sql.open_cursor;
3700
3701 -- ***************************************************
3702 --
3703 -- Parse each of the SELECT statement
3704 -- so the database understands the command
3705 --
3706 -- ***************************************************
3707
3708 xProgress := '2360-70';
3709 BEGIN
3710 dbms_sql.parse ( Header_sel_c,
3711 cHeader_select,
3712 dbms_sql.native );
3713 EXCEPTION
3714 WHEN OTHERS THEN
3715 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3716 cHeader_select );
3717 app_exception.raise_exception;
3718 END;
3719
3720 xProgress := '2362-70';
3721 BEGIN
3722 dbms_sql.parse ( Header_1_sel_c,
3723 cHeader_1_select,
3724 dbms_sql.native );
3725 EXCEPTION
3726 WHEN OTHERS THEN
3727 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3728 cHeader_1_select );
3729 app_exception.raise_exception;
3730 END;
3731
3732 xProgress := '2364-70';
3733 BEGIN
3734 dbms_sql.parse ( Alw_chg_sel_c,
3735 cAlw_chg_select,
3736 dbms_sql.native );
3737 EXCEPTION
3738 WHEN OTHERS THEN
3739 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3740 cAlw_chg_select );
3741 app_exception.raise_exception;
3742 END;
3743
3744 xProgress := '2366-70';
3745 BEGIN
3746 dbms_sql.parse ( Line_sel_c,
3747 cLine_select,
3748 dbms_sql.native );
3749 EXCEPTION
3750 WHEN OTHERS THEN
3751 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3752 cLine_select );
3753 app_exception.raise_exception;
3754 END;
3755
3756 xProgress := '2368-70';
3757 BEGIN
3758 dbms_sql.parse ( Line_t_sel_c,
3759 cLine_t_select,
3760 dbms_sql.native );
3761 EXCEPTION
3762 WHEN OTHERS THEN
3763 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3764 cLine_t_select );
3765 app_exception.raise_exception;
3766 END;
3767
3768 -- *************************************************
3769 -- set counter
3770 -- *************************************************
3771
3772 xProgress := '2370-70';
3773 iHeader_count := l_header_tbl.count;
3774 ec_debug.pl ( 3, 'iHeader_count: ',iHeader_count );
3775
3776 xProgress := '2372-70';
3777 iHeader_1_count := l_header_1_tbl.count;
3778 ec_debug.pl ( 3, 'iHeader_1_count: ',iHeader_1_count );
3779
3780 xProgress := '2374-70';
3781 iAlw_chg_count := l_alw_chg_tbl.count;
3782 ec_debug.pl ( 3, 'iAlw_chg_count: ',iAlw_chg_count );
3783
3784 xProgress := '2376-70';
3785 iLine_count := l_line_tbl.count;
3786 ec_debug.pl ( 3, 'iLine_count: ',iLine_count );
3787
3788 xProgress := '2378-70';
3789 iLine_t_count := l_line_t_tbl.count;
3790 ec_debug.pl ( 3, 'iLine_t_count: ',iLine_t_count );
3791
3792 -- ***************************************************
3793 --
3794 -- Define TYPE for every columns in the SELECT statement
3795 -- For each piece of the data returns, we need to tell
3796
3797 -- the database what type of information it will be.
3798 --
3799 -- e.g. ID is NUMBER, due_date is DATE
3800 -- However, for simplicity, we will convert
3801 -- everything to varchar2.
3802 --
3803 -- ***************************************************
3804
3805 xProgress := '2380-70';
3806 define_interface_column ( Header_sel_c,
3807 cHeader_select,
3808 ece_extract_utils_PUB.G_MaxColWidth,
3809 l_header_tbl );
3810
3811 xProgress := '2382-70';
3812 define_interface_column ( Header_1_sel_c,
3813 cHeader_1_select,
3814 ece_extract_utils_PUB.G_MaxColWidth,
3815 l_header_1_tbl );
3816
3817 xProgress := '2384-70';
3818 define_interface_column ( Alw_chg_sel_c,
3819 cAlw_chg_select,
3820 ece_extract_utils_PUB.G_MaxColWidth,
3821 l_alw_chg_tbl );
3822
3823 xProgress := '2386-70';
3824 define_interface_column ( Line_sel_c,
3825 cLine_select,
3826 ece_extract_utils_PUB.G_MaxColWidth,
3827 l_line_tbl );
3828 xProgress := '2388-70';
3829 define_interface_column ( Line_t_sel_c,
3830 cLine_t_select,
3831 ece_extract_utils_PUB.G_MaxColWidth,
3832 l_line_t_tbl );
3833 -- Bind Variables
3834 xProgress := '2388-70';
3835 IF cCreate_Date_From IS NOT NULL
3836 THEN
3837 dbms_sql.bind_variable ( Header_sel_c,
3838 'l_cCreate_Date_From',
3839 cCreate_Date_From);
3840 END IF;
3841
3842 xProgress := '2388-71';
3843 IF cCreate_Date_To IS NOT NULL
3844 THEN
3845 dbms_sql.bind_variable ( Header_sel_c,
3846 'l_cCreate_Date_To',
3847 cCreate_Date_To);
3848 END IF;
3849
3850 xProgress := '2388-72';
3851 IF cCustomer_Name IS NOT NULL
3852 THEN
3853 dbms_sql.bind_variable ( Header_sel_c,
3854 'l_cCustomer_Name',
3855 cCustomer_Name );
3856 END IF;
3857
3858 xProgress := '2388-73';
3859 IF cSite_Use_Code IS NOT NULL
3860 THEN
3861 dbms_sql.bind_variable ( Header_sel_c,
3862 'l_cSite_Use_Code',
3863 cSite_Use_Code );
3864 END IF;
3865
3866 xProgress := '2388-74';
3867 IF cDocument_Type IS NOT NULL
3868 THEN
3869 dbms_sql.bind_variable ( Header_sel_c,
3870 'l_cDocument_Type',
3871 cDocument_Type );
3872 END IF;
3873
3874 xProgress := '2388-75';
3875 IF cTransaction_Number IS NOT NULL
3876 THEN
3877 dbms_sql.bind_variable ( Header_sel_c,
3878 'l_cTransaction_Number',
3879 cTransaction_Number );
3880 END IF;
3881
3882 -- **************************************************************
3883 -- *** The following is custom tailored for this transaction
3884 -- *** It find the values and use them in the WHERE clause to
3885 -- *** join tables together.
3886 -- **************************************************************
3887
3888 -- ***************************************************
3889 -- To complete the Line SELECT statement,
3890 -- we will need values for the join condition.
3891 --
3892 -- ***************************************************
3893 -- EXECUTE the SELECT statement
3894
3895 xProgress := '2390-70';
3896 dummy := dbms_sql.execute(Header_sel_c);
3897
3898 -- ***************************************************
3899 --
3900 -- The model is:
3901 -- HEADER - HEADER 1 - ALLOWANCE CHARGES - LINE - LINE TAX ...
3902 --
3903 -- With data for each HEADER line, populate the header interface
3904 -- table then get all HEADER DETAILS and ALLOWANCE CHARGES that belong to the HEADER,
3905 -- then get LINES that belong to the HEADER. Then get all
3906 -- LINE TAX that belongs to the LINE.
3907 --
3908 -- ***************************************************
3909
3910 xProgress := '2400-70';
3911 WHILE dbms_sql.fetch_rows ( Header_sel_c ) > 0
3912 LOOP -- Header
3913
3914 -- ***************************************************
3915 --
3916 -- store internal values in pl/sql table
3917 --
3918 -- ***************************************************
3919
3920 xProgress := '2410-70';
3921 FOR i IN 1..iHeader_count
3922 LOOP
3923 dbms_sql.column_value ( Header_sel_c,
3924 i,
3925 l_header_tbl(i).value );
3926
3927 dbms_sql.column_value ( Header_sel_c,
3928 i,
3929 l_key_tbl(i).value );
3930 END LOOP;
3931
3932 xProgress := '2420-70';
3933 ece_extract_utils_pub.Find_pos ( l_header_tbl,
3934 'TRANSACTION_ID',
3935 nPos1 );
3936 ec_debug.pl ( 3, 'nPos1: ',nPos1 );
3937
3938 xProgress := '2422-70';
3939 ece_extract_utils_pub.Find_pos ( l_header_tbl,
3940 'INSTALLMENT_NUMBER',
3941 nPos2 );
3942 ec_debug.pl ( 3, 'nPos2: ',nPos2 );
3943
3944 xProgress := '2424-70';
3945 ece_extract_utils_pub.Find_pos ( l_header_tbl,
3946 'DOCUMENT_TYPE',
3947 nPos3 );
3948 ec_debug.pl ( 3, 'nPos3: ',nPos3 );
3949
3950 xProgress := '2426-70';
3951 ece_extract_utils_pub.Find_pos ( l_header_tbl ,
3952 'PAYMENT_TERM_ID',
3953 nPos4 );
3954 ec_debug.pl ( 3, 'nPos4: ',nPos4 );
3955
3956 xProgress := '2428-70';
3957 ece_extract_utils_pub.Find_pos ( l_header_tbl ,
3958 'SHIP_TO_ADDRESS_ID',
3959 nPos5 );
3960 ec_debug.pl ( 3, 'nPos5: ',nPos5 );
3961
3962 xProgress := '2430-70';
3963 ece_extract_utils_pub.Find_pos ( l_header_tbl ,
3964 'SOLD_TO_ADDRESS_ID',
3965 nPos6 );
3966 ec_debug.pl ( 3, 'nPos6: ',nPos6 );
3967
3968 xProgress := '2431-70';
3969 ece_extract_utils_pub.Find_pos ( l_header_tbl ,
3970 'BILL_TO_ADDRESS_ID',
3971 nPos8 );
3972 ec_debug.pl ( 3, 'nPos8: ',nPos8 );
3973
3974 xProgress := '2432-70';
3975 ece_extract_utils_pub.Find_pos ( l_header_tbl ,
3976 'TP_LOCATION_CODE_EXT',
3977 nPos7 );
3978 ec_debug.pl ( 3, 'nPos7: ',nPos7 );
3979
3980 xProgress := '2434-70';
3981 ece_extract_utils_pub.Find_pos ( l_header_tbl ,
3982 'REMIT_TO_ADDRESS1',
3983 nPos10 );
3984 ec_debug.pl ( 3, 'nPos10: ',nPos10 );
3985
3986 xProgress := '2436-70';
3987 ece_extract_utils_pub.Find_pos ( l_header_tbl ,
3988 'REMIT_TO_ADDRESS2',
3989 nPos11 );
3990 ec_debug.pl ( 3, 'nPos11: ',nPos11 );
3991
3992 xProgress := '2438-70';
3993 ece_extract_utils_pub.Find_pos ( l_header_tbl ,
3994 'REMIT_TO_ADDRESS3',
3995 nPos12 );
3996 ec_debug.pl ( 3, 'nPos12: ',nPos12 );
3997
3998 xProgress := '2440-70';
3999 ece_extract_utils_pub.Find_pos ( l_header_tbl ,
4000 'REMIT_TO_ADDRESS4',
4001 nPos13 );
4002 ec_debug.pl ( 3, 'nPos13: ',nPos13 );
4003
4004 xProgress := '2442-70';
4005 ece_extract_utils_pub.Find_pos ( l_header_tbl ,
4006 'REMIT_TO_CITY',
4007 nPos14 );
4008 ec_debug.pl ( 3, 'nPos14: ',nPos14 );
4009
4010 xProgress := '2444-70';
4011 ece_extract_utils_pub.Find_pos ( l_header_tbl ,
4012 'REMIT_TO_COUNTY',
4013 nPos15 );
4014 ec_debug.pl ( 3, 'nPos15: ',nPos15 );
4015
4016 xProgress := '2446-70';
4017 ece_extract_utils_pub.Find_pos ( l_header_tbl ,
4018 'REMIT_TO_STATE',
4019 nPos16 );
4020 ec_debug.pl ( 3, 'nPos16: ',nPos16 );
4021
4022 xProgress := '2448-70';
4023 ece_extract_utils_pub.Find_pos ( l_header_tbl ,
4024 'REMIT_TO_PROVINCE',
4025 nPos17 );
4026 ec_debug.pl ( 3, 'nPos17: ',nPos17 );
4027
4028 xProgress := '2450-70';
4029 ece_extract_utils_pub.Find_pos ( l_header_tbl ,
4030 'REMIT_TO_COUNTRY',
4031 nPos18 );
4032 ec_debug.pl ( 3, 'nPos18: ',nPos18 );
4033
4034 xProgress := '2452-70';
4035 ece_extract_utils_pub.Find_pos ( l_header_tbl ,
4036 'REMIT_TO_POSTAL_CODE',
4037 nPos19 );
4038 ec_debug.pl ( 3, 'nPos19: ',nPos19 );
4039
4040 xProgress := '2452-71'; --Bug 2389231
4041 ece_extract_utils_pub.Find_pos ( l_header_tbl ,
4042 'INV_TRANSACTION_DATE',
4043 nPos28 );
4044 ec_debug.pl ( 3, 'nPos28: ',nPos28 );
4045 ec_debug.pl(3, 'l_header_tbl(nPos28).value',l_header_tbl(nPos28).value);
4046
4047 -- ***************************************************
4048 --
4049 -- also need to populate transaction_date and run_id
4050 --
4051 -- ***************************************************
4052
4053 xProgress := '2460-70';
4054 ece_extract_utils_pub.Find_pos ( l_header_tbl,
4055 ece_flatfile_pvt.G_Transaction_date,
4056 n_trx_date_pos );
4057
4058 xProgress := '2470-70';
4059 l_header_tbl(n_trx_date_pos).value := TO_CHAR(dTransaction_date,'YYYYMMDD HH24MISS');
4060 ec_debug.pl ( 3, 'lheader_tbl(n_trx_date_pos).value: ',l_header_tbl(n_trx_date_pos).value );
4061
4062 xProgress := '2475-70';
4063
4064 /* 2945057*/
4065 select bill_to_contact_id,ship_to_contact_id,sold_to_contact_id
4066 into l_bill_to_contact_id,l_ship_to_contact_id,l_sold_to_contact_id
4067 from ra_customer_trx
4068 where customer_trx_id=l_header_tbl(nPos1).value;
4069
4070 If (l_bill_to_contact_id is not null) then
4071 begin
4072 xProgress := '2476-70';
4073 SELECT
4074 substrb(PARTY.PERSON_LAST_NAME,1,50),
4075 substrb(PARTY.PERSON_FIRST_NAME,1,40),
4076 ORG_CONT.JOB_TITLE
4077 into l_bill_to_contact_last_name,l_bill_to_contact_first_name,l_bill_to_contact_job_title
4078 FROM
4079 HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
4080 HZ_PARTIES PARTY,
4081 HZ_RELATIONSHIPS REL,
4082 HZ_ORG_CONTACTS ORG_CONT
4083 WHERE
4084 ACCT_ROLE.PARTY_ID = REL.PARTY_ID
4085 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
4086 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
4087 AND REL.DIRECTIONAL_FLAG = 'F'
4088 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4089 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4090 AND REL.SUBJECT_ID = PARTY.PARTY_ID
4091 AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = l_bill_to_contact_id;
4092 /* select last_name,first_name,job_title
4093 into l_bill_to_contact_last_name,l_bill_to_contact_first_name,l_bill_to_contact_job_title
4094 from ra_contacts
4095 where contact_id=l_bill_to_contact_id; */
4096 exception
4097 when others then
4098 ec_debug.pl ( 3, 'EC', 'ECE_PROGRAM_ERROR', 'PROGRESS_LEVEL', xProgress );
4099 end;
4100 End If;
4101
4102 If (l_ship_to_contact_id is not null) then
4103 begin
4104 xProgress :='2477-70';
4105 SELECT
4106 substrb(PARTY.PERSON_LAST_NAME,1,50),
4107 substrb(PARTY.PERSON_FIRST_NAME,1,40),
4108 ORG_CONT.JOB_TITLE
4109 into l_ship_to_contact_last_name,l_ship_to_contact_first_name,l_ship_to_contact_job_title
4110 FROM
4111 HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
4112 HZ_PARTIES PARTY,
4113 HZ_RELATIONSHIPS REL,
4114 HZ_ORG_CONTACTS ORG_CONT,
4115 HZ_PARTIES REL_PARTY
4116 WHERE
4117 ACCT_ROLE.PARTY_ID = REL.PARTY_ID
4118 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
4119 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
4120 AND REL.DIRECTIONAL_FLAG = 'F'
4121 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4122 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4123 AND REL.SUBJECT_ID = PARTY.PARTY_ID
4124 AND REL.PARTY_ID = REL_PARTY.PARTY_ID
4125 AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = l_ship_to_contact_id;
4126 /*
4127 select last_name,first_name,job_title
4128 into l_ship_to_contact_last_name,l_ship_to_contact_first_name,l_ship_to_contact_job_title
4129 from ra_contacts
4130 where contact_id=l_ship_to_contact_id; */
4131 exception
4132 when others then
4133 ec_debug.pl ( 3, 'EC', 'ECE_PROGRAM_ERROR', 'PROGRESS_LEVEL', xProgress );
4134 end;
4135 End If;
4136
4137 If (l_sold_to_contact_id is not null) then
4138 begin
4139 xProgress :='2477-70';
4140 SELECT
4141 substrb(PARTY.PERSON_LAST_NAME,1,50),
4142 substrb(PARTY.PERSON_FIRST_NAME,1,40),
4143 ORG_CONT.JOB_TITLE
4144 into l_sold_to_contact_last_name,l_sold_to_contact_first_name,l_sold_to_contact_job_title
4145 FROM
4146 HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
4147 HZ_PARTIES PARTY,
4148 HZ_RELATIONSHIPS REL,
4149 HZ_ORG_CONTACTS ORG_CONT,
4150 HZ_PARTIES REL_PARTY
4151 WHERE
4152 ACCT_ROLE.PARTY_ID = REL.PARTY_ID
4153 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
4154 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
4155 AND REL.DIRECTIONAL_FLAG = 'F'
4156 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4157 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4158 AND REL.SUBJECT_ID = PARTY.PARTY_ID
4159 AND REL.PARTY_ID = REL_PARTY.PARTY_ID
4160 AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = l_sold_to_contact_id;
4161 /* select last_name,first_name,job_title
4162 into l_sold_to_contact_last_name,l_sold_to_contact_first_name,l_sold_to_contact_job_title
4163 from ra_contacts
4164 where contact_id=l_sold_to_contact_id; */
4165 exception
4166 when others then
4167 ec_debug.pl ( 3, 'EC', 'ECE_PROGRAM_ERROR', 'PROGRESS_LEVEL', xProgress );
4168 end;
4169 End If;
4170 /*2945057*/
4171
4172 -- The following procedures get the payment and remit address
4173 -- information for this transaction
4174
4175 xProgress := '2480-70';
4176 ece_ar_transaction.Get_Payment ( l_header_tbl(nPos1).value,
4177 l_header_tbl(nPos2).value,
4178 l_Multiple_Installments_Flag,
4179 l_Maximum_Installment_Number,
4180 l_Amount_Tax_Due,
4181 l_Amount_Charges_Due,
4182 l_Amount_Freight_Due,
4183 l_Amount_Line_Items_Due,
4184 l_total_amount_due );
4185
4186 xProgress := '2490-70';
4187 ece_ar_transaction.Get_Remit_Address ( l_header_tbl(nPos1).value,
4188 l_remit_to_address1,
4189 l_remit_to_address2,
4190 l_remit_to_address3,
4191 l_remit_to_address4,
4192 l_remit_to_city,
4193 l_remit_to_county,
4194 l_remit_to_state,
4195 l_remit_to_province,
4196 l_remit_to_country,
4197 l_remit_to_code_int,
4198 l_remit_to_postal_code,
4199 l_remit_to_customer_name, --2291130
4200 l_remit_to_edi_location_code); --2386848
4201
4202 -- Now update the values in pl/sql table
4203 -- This is being done so that code conversion can be done on derived fields thru
4204 -- a procedure.
4205
4206 xProgress := '2500-70';
4207 l_header_tbl(nPos10).value := l_remit_to_address1;
4208 ec_debug.pl ( 3, 'lheader_tbl(nPos10).value: ',l_header_tbl(nPos10).value );
4209 xProgress := '2501-70';
4210 l_header_tbl(nPos11).value := l_remit_to_address2;
4211 ec_debug.pl ( 3, 'lheader_tbl(nPos11).value: ',l_header_tbl(nPos11).value );
4212 xProgress := '2502-70';
4213 l_header_tbl(nPos12).value := l_remit_to_address3;
4214 ec_debug.pl ( 3, 'lheader_tbl(nPos12).value: ',l_header_tbl(nPos12).value );
4215 xProgress := '2503-70';
4216 l_header_tbl(nPos13).value := l_remit_to_address4;
4217 ec_debug.pl ( 3, 'lheader_tbl(nPos13).value: ',l_header_tbl(nPos13).value );
4218 xProgress := '2504-70';
4219 l_header_tbl(nPos14).value := l_remit_to_city;
4220 ec_debug.pl ( 3, 'lheader_tbl(nPos14).value: ',l_header_tbl(nPos14).value );
4221 xProgress := '2505-70';
4222 l_header_tbl(nPos15).value := l_remit_to_county;
4223 ec_debug.pl ( 3, 'lheader_tbl(nPos15).value: ',l_header_tbl(nPos15).value );
4224 xProgress := '2506-70';
4225 l_header_tbl(nPos16).value := l_remit_to_state;
4226 ec_debug.pl ( 3, 'lheader_tbl(nPos16).value: ',l_header_tbl(nPos16).value );
4227 xProgress := '2507-70';
4228 l_header_tbl(nPos17).value := l_remit_to_province;
4229 ec_debug.pl ( 3, 'lheader_tbl(nPos17).value: ',l_header_tbl(nPos17).value );
4230 xProgress := '2508-70';
4231 l_header_tbl(nPos18).value := l_remit_to_country;
4232 ec_debug.pl ( 3, 'lheader_tbl(nPos18).value: ',l_header_tbl(nPos18).value );
4233 xProgress := '2509-70';
4234 l_header_tbl(nPos19).value := l_remit_to_postal_code;
4235 ec_debug.pl ( 3, 'lheader_tbl(nPos19).value: ',l_header_tbl(nPos19).value );
4236
4237 -- The following procedure gets the discount information
4238 -- for the term being used. The discount info is a sub-table
4239 -- off of terms, this procedure will get the first three
4240 -- discounts, this is a denormalization, but is being used
4241 -- to avoid the overhead of another level of data
4242
4243
4244 xProgress := '2510-70';
4245 -- Bug 2389231
4246 ece_ar_transaction.Get_Term_Discount ( l_header_tbl(nPos3).value,
4247 l_header_tbl(nPos4).value,
4248 l_header_tbl(nPos2).value,
4249 to_date(l_header_tbl(nPos28).value,'YYYYMMDD HH24MISS'),
4250 l_Discount_Percent1,
4251 l_Discount_Days1,
4252 l_Discount_Date1,
4253 l_Discount_Day_Of_Month1,
4254 l_Discount_Months_Forward1,
4255 l_Discount_Percent2,
4256 l_Discount_Days2,
4257 l_Discount_Date2,
4258 l_Discount_Day_Of_Month2,
4259 l_Discount_Months_Forward2,
4260 l_Discount_Percent3,
4261 l_Discount_Days3,
4262 l_Discount_Date3,
4263 l_Discount_Day_Of_Month3,
4264 l_Discount_Months_Forward3 );
4265
4266 -- The following procedures get the trading partner details for
4267 -- remit to, ship to and sold to addresses
4268
4269 xProgress := '2520-70';
4270 ec_trading_partner_pvt.Get_TP_Location_Code ( p_api_version_number => 1.0,
4271 p_init_msg_list => init_msg_list,
4272 p_simulate => simulate,
4273 p_commit => commt,
4274 p_validation_level => validation_level,
4275 p_return_status => return_status,
4276 p_msg_count => msg_count,
4277 p_msg_data => msg_data,
4278 p_entity_address_id => l_header_tbl(nPos8).value,
4279 p_info_type => ec_trading_partner_pvt.G_CUSTOMER,
4280 p_location_code_ext => l_bill_to_code_ext,
4281 p_reference_ext1 => l_bill_to_tp_reference_ext1,
4282 p_reference_ext2 => l_bill_to_tp_reference_ext2 );
4283
4284
4285 /* xProgress := '2520-70';
4286 ec_trading_partner_pvt.Get_TP_Location_Code ( p_api_version_number => 1.0,
4287 p_init_msg_list => init_msg_list,
4288 p_simulate => simulate,
4289 p_commit => commt,
4290 p_validation_level => validation_level,
4291 p_return_status => return_status,
4292 p_msg_count => msg_count,
4293 p_msg_data => msg_data,
4294 p_entity_address_id => l_Remit_To_Address_ID,
4295 p_info_type => ec_trading_partner_pvt.G_CUSTOMER,
4296 p_location_code_ext => l_remit_to_code_ext,
4297 p_reference_ext1 => l_remit_to_tp_reference_ext1,
4298 p_reference_ext2 => l_remit_to_tp_reference_ext2 );
4299
4300 xProgress := '2530-70';
4301 ec_trading_partner_pvt.Get_TP_Location_Code ( p_api_version_number => 1.0,
4302 p_init_msg_list => init_msg_list,
4303 p_simulate => simulate,
4304 p_commit => commt,
4305 p_validation_level => validation_level,
4306 p_return_status => return_status,
4307 p_msg_count => msg_count,
4308 p_msg_data => msg_data,
4309 p_entity_address_id => l_header_tbl(nPos5).value,
4310 p_info_type => ec_trading_partner_pvt.G_CUSTOMER,
4311 p_location_code_ext => l_ship_to_customer_code_ext,
4312 p_reference_ext1 => l_ship_to_tp_reference_ext1,
4313 p_reference_ext2 => l_ship_to_tp_reference_ext2 );
4314
4315 xProgress := '2540-70';
4316 ec_trading_partner_pvt.Get_TP_Location_Code ( p_api_version_number => 1.0,
4317 p_init_msg_list => init_msg_list,
4318 p_simulate => simulate,
4319 p_commit => commt,
4320 p_validation_level => validation_level,
4321 p_return_status => return_status,
4322 p_msg_count => msg_count,
4323 p_msg_data => msg_data,
4324 p_entity_address_id => l_header_tbl(nPos6).value,
4325 p_info_type => ec_trading_partner_pvt.G_CUSTOMER,
4326 p_location_code_ext => l_sold_to_customer_code_ext,
4327 p_reference_ext1 => l_sold_to_tp_reference_ext1,
4328 p_reference_ext2 => l_sold_to_tp_reference_ext2 );
4329 */
4330 -- The application specific feedback logic begins here.
4331 -- The procedure below contains all of the logic necessary
4332 -- to update the AR base tables.
4333
4334 xProgress := '2550-70';
4335
4336 ece_ar_transaction.Update_AR ( l_header_tbl(nPos3).value,
4337 l_header_tbl(nPos1).value,
4338 l_header_tbl(nPos2).value,
4339 l_Multiple_Installments_Flag,
4340 l_Maximum_Installment_Number,
4341 l_last_update_date );
4342
4343 -- ***********************************
4344 -- pass the pl/sql table in for xref
4345 -- ***********************************
4346
4347 xProgress := '2560-70';
4348 ec_code_Conversion_pvt.populate_plsql_tbl_with_extval ( p_api_version_number => 1.0,
4349 p_init_msg_list => init_msg_list,
4350 p_simulate => simulate,
4351 p_commit => commt,
4352 p_validation_level => validation_level,
4353 p_return_status => return_status,
4354 p_msg_count => msg_count,
4355 p_msg_data => msg_data,
4356 p_key_tbl => l_key_tbl,
4357 p_tbl => l_header_tbl );
4358
4359 -- ******************************************
4360 --
4361 -- insert into interface table
4362 --
4363 -- ******************************************
4364
4365 xProgress := '2570-70';
4366 BEGIN
4367 SELECT ece_ar_trx_headers_s.nextval
4368 INTO l_header_fkey
4369 FROM sys.dual;
4370 EXCEPTION
4371 WHEN NO_DATA_FOUND THEN
4372 ec_debug.pl ( 1,
4373 'EC',
4374 'ECE_GET_NEXT_SEQ_FAILED',
4375 'PROGRESS_LEVEL',
4376 xProgress,
4377 'SEQ',
4378 'ECE_AR_TRX_HEADERS_S' );
4379 END;
4380
4381 xProgress := '2580-70';
4382 ece_Extract_Utils_PUB.insert_into_interface_tbl ( iRun_id,
4383 cTransaction_Type,
4384 cCommunication_Method,
4385 cHeader_Interface,
4386 l_header_tbl,
4387 l_header_fkey );
4388
4389 -- Now update the columns values of which have been obtained thru the procedure
4390 -- calls.
4391
4392 xProgress := '2590-70';
4393 UPDATE ece_ar_trx_headers
4394 SET maximum_installment_number = l_Maximum_Installment_Number,
4395 amount_tax_due = l_Amount_Tax_Due,
4396 amount_charges_due = l_Amount_Charges_Due,
4397 amount_freight_due = l_Amount_Freight_Due,
4398 amount_line_items_due = l_Amount_Line_Items_Due,
4399 total_amount_due = l_total_amount_due,
4400 Discount_Percent1 = l_Discount_Percent1,
4401 Discount_Days1 = l_Discount_Days1,
4402 Discount_Date1 = l_Discount_Date1,
4403 Discount_Day_Of_Month1 = l_Discount_Day_Of_Month1,
4404 Discount_Months_Forward1 = l_Discount_Months_Forward1,
4405 Discount_Percent2 = l_Discount_Percent2,
4406 Discount_Days2 = l_Discount_Days2,
4407 Discount_Date2 = l_Discount_Date2,
4408 Discount_Day_Of_Month2 = l_Discount_Day_Of_Month2,
4409 Discount_Months_Forward2 = l_Discount_Months_Forward2,
4410 Discount_Percent3 = l_Discount_Percent3,
4411 Discount_Days3 = l_Discount_Days3,
4412 Discount_Date3 = l_Discount_Date3,
4413 Discount_Day_Of_Month3 = l_Discount_Day_Of_Month3,
4414 Discount_Months_Forward3 = l_Discount_Months_Forward3,
4415 remit_to_code_ext = l_remit_to_edi_location_code, --2386848
4416 remit_to_code_int = l_remit_to_code_int,
4417 /* ship_to_customer_code_ext = l_ship_to_customer_code_ext, --2386848
4418 sold_to_customer_code_ext = l_sold_to_customer_code_ext, */
4419 bill_to_customer_code_ext = l_header_tbl(nPos7).value,
4420 bill_to_tp_reference_ext1 = l_bill_to_tp_reference_ext1,
4421 bill_to_tp_reference_ext2 = l_bill_to_tp_reference_ext2,
4422 /* ship_to_tp_reference_ext1 = l_ship_to_tp_reference_ext1, --2386848
4423 ship_to_tp_reference_ext2 = l_ship_to_tp_reference_ext2,
4424 sold_to_tp_reference_ext1 = l_sold_to_tp_reference_ext1,
4425 sold_to_tp_reference_ext2 = l_sold_to_tp_reference_ext2,
4426 remit_to_tp_reference_ext1 = l_remit_to_tp_reference_ext1,
4427 remit_to_tp_reference_ext2 = l_remit_to_tp_reference_ext2, */
4428 tp_document_purpose_code = 'OR',
4429 remit_to_customer_name = l_remit_to_customer_name, --2291130
4430 bill_to_contact_last_name = l_bill_to_contact_last_name,
4431 bill_to_contact_first_name = l_bill_to_contact_first_name,
4432 bill_to_contact_job_title = l_bill_to_contact_job_title,
4433 ship_to_contact_last_name = l_ship_to_contact_last_name,
4434 ship_to_contact_first_name = l_ship_to_contact_first_name,
4435 ship_to_contact_job_title = l_ship_to_contact_job_title,
4436 sold_to_contact_last_name = l_sold_to_contact_last_name,
4437 sold_to_contact_first_name = l_sold_to_contact_first_name,
4438 sold_to_contact_job_title = l_sold_to_contact_job_title
4439 WHERE transaction_record_id = l_header_fkey;
4440
4441 IF SQL%NOTFOUND
4442 THEN
4443 ec_debug.pl ( 0,
4444 'EC',
4445 'ECE_NO_ROW_UPDATED',
4446 'PROGRESS_LEVEL',
4447 xProgress,
4448 'INFO',
4449 'AMOUNT, DISCOUNT AND LOCATIONS',
4450 'TABLE_NAME',
4451 'ECE_AR_TRX_HEADERS' );
4452 END IF;
4453
4454 -- ******************************************
4455 --
4456 -- Call custom program stub to populate the extension table
4457 --
4458 -- ******************************************
4459
4460 xProgress := '2600-70';
4461 ece_ino_x.populate_extension_header ( l_header_fkey, l_header_tbl );
4462
4463 -- the parameter of this procedure has not been finalized!!
4464 -- ALL of you has to create this package in a seperate file
4465 -- even if it is empty.
4466
4467 -- ***************************************************
4468 --
4469 -- From Header data, we can assign values to
4470 -- place holders (foreign keys) in Line_select and
4471 -- Line_detail_Select
4472 --
4473 -- ***************************************************
4474 -- -- set values into binding variables
4475 --
4476 -- ***************************************************
4477
4478 -- use the following bind_variable feature as you see fit.
4479
4480 xProgress := '2610-70';
4481 dbms_sql.bind_variable ( Header_1_sel_c,
4482 'transaction_id',
4483 l_header_tbl(nPos1).value );
4484
4485 xProgress := '2612-70';
4486 dbms_sql.bind_variable ( Alw_chg_sel_c,
4487 'transaction_id',
4488 l_header_tbl(nPos1).value );
4489
4490 xProgress := '2614-70';
4491 dbms_sql.bind_variable ( Line_sel_c,
4492 'transaction_id',
4493 l_header_tbl(nPos1).value );
4494
4495 xProgress := '2616-70';
4496 dbms_sql.bind_variable ( Line_t_sel_c,
4497 'transaction_id',
4498 l_header_tbl(nPos1).value );
4499
4500 xProgress := '2620-70';
4501 dummy := dbms_sql.execute( Header_1_sel_c );
4502
4503 -- ***************************************************
4504 --
4505 -- header detail loop starts here
4506 --
4507 -- ***************************************************
4508
4509 /* Header 1 loop begins here */
4510
4511 xProgress := '2630-70';
4512 WHILE dbms_sql.fetch_rows ( Header_1_sel_c ) > 0
4513 LOOP --- Header Detail
4514
4515 -- ***************************************************
4516 --
4517 -- store values in pl/sql table
4518 --
4519 -- ***************************************************
4520
4521 xProgress := '2640-70';
4522 FOR l IN 1..iHeader_1_count LOOP
4523 dbms_sql.column_value ( Header_1_sel_c,
4524 l,
4525 l_header_1_tbl(l).value );
4526
4527 dbms_sql.column_value ( Header_1_sel_c,
4528 l,
4529 l_key_tbl(l+iHeader_count).value );
4530 END LOOP;
4531
4532 -- ***************************************************
4533 -- pass the pl/sql table in for xref
4534 -- ***************************************************
4535
4536 xProgress := '2650-70';
4537 ece_extract_utils_pub.Find_pos ( l_header_1_tbl,
4538 'PACKING_SLIP_NUMBER',
4539 nPos4 );
4540 ec_debug.pl ( 3, 'nPos4: ',nPos4 );
4541 xProgress := '2660-70';
4542 ece_extract_utils_pub.Find_pos ( l_header_1_tbl,
4543 'SHIP_FROM_CODE_INT',
4544 nPos5 );
4545 ec_debug.pl ( 3, 'nPos5: ',nPos5 );
4546
4547 xProgress := '2664-70';
4548 ece_extract_utils_pub.Find_pos ( l_header_1_tbl,
4549 'INTERFACE_ATTRIBUTE3',
4550 pos_1 );
4551 ec_debug.pl ( 3, 'pos_1: ',pos_1 );
4552
4553 xProgress := '2665-70';
4554 ece_extract_utils_pub.Find_pos ( l_header_1_tbl,
4555 'EQUIPMENT_PREFIX',
4556 nPos8);
4557
4558 xProgress := '2666-70';
4559 ece_extract_utils_pub.Find_pos ( l_header_1_tbl,
4560 'EQUIPMENT_NUMBER',
4561 nPos9);
4562 xProgress := '2667-70';
4563 ece_extract_utils_pub.Find_pos ( l_header_1_tbl,
4564 'ROUTING_INSTRUCTIONS',
4565 nPos10);
4566
4567 xProgress := '2667-72';
4568 ece_extract_utils_pub.Find_pos ( l_header_1_tbl,
4569 'TRANSACTION_ID',
4570 nPos12);
4571
4572 BEGIN
4573
4574 FOR rec_header_1 IN c_header_1(l_header_1_tbl(nPos12).value)
4575 LOOP
4576 l_header_1_tbl(nPos8).value := rec_header_1.equipment_prefix;
4577 l_header_1_tbl(nPos9).value := rec_header_1.equipment_number;
4578 l_header_1_tbl(nPos10).value := rec_header_1.routing_instructions;
4579 l_header_1_tbl(nPos4).value := rec_header_1.packing_slip_number;
4580 END LOOP;
4581 EXCEPTION
4582 WHEN OTHERS THEN
4583 null;
4584 END;
4585
4586
4587 xProgress := '2668-70';
4588 l_delivery_name := l_header_1_tbl(pos_1).value;
4589 ec_debug.pl ( 3, 'l_delivery_name: ',l_delivery_name );
4590
4591 xProgress := '2670-70';
4592 IF ( l_delivery_name IS NOT NULL )
4593 THEN
4594
4595 BEGIN
4596 SELECT delivery_id
4597 INTO l_delivery_id
4598 FROM wsh_deliveries
4599 WHERE name = l_delivery_name;
4600 EXCEPTION
4601 WHEN NO_DATA_FOUND THEN
4602 l_delivery_id := NULL;
4603 END;
4604
4605 ELSE
4606
4607 l_delivery_id := NULL;
4608
4609 END IF;
4610
4611 ec_debug.pl ( 3, 'l_delivery_id: ',l_delivery_id );
4612
4613 xProgress := '2680-70';
4614 ec_code_Conversion_pvt.populate_plsql_tbl_with_extval ( p_api_version_number => 1.0,
4615 p_init_msg_list => init_msg_list,
4616 p_simulate => simulate,
4617 p_commit => commt,
4618 p_validation_level => validation_level,
4619 p_return_status => return_status,
4620 p_msg_count => msg_count,
4621 p_msg_data => msg_data,
4622 p_key_tbl => l_key_tbl,
4623 p_tbl => l_header_1_tbl );
4624
4625 xProgress := '2690-70';
4626 BEGIN
4627 SELECT ece_ar_trx_header_1_s.nextval
4628 INTO l_header_1_fkey
4629 FROM sys.dual;
4630 EXCEPTION
4631 WHEN NO_DATA_FOUND THEN
4632 ec_debug.pl ( 1,
4633 'EC',
4634 'ECE_GET_NEXT_SEQ_FAILED',
4635 'PROGRESS_LEVEL',
4636 xProgress,
4637 'SEQ',
4638 'ECE_AR_TRX_HEADERS_1_S' );
4639 END;
4640 ec_debug.pl ( 3, 'l_header_1_fkey: ',l_header_1_fkey );
4641
4642 xProgress := '2700-70';
4643 ece_Extract_Utils_PUB.insert_into_interface_tbl ( iRun_id,
4644 cTransaction_Type,
4645 cCommunication_Method,
4646 cHeader_1_Interface,
4647 l_header_1_tbl,
4648 l_header_1_fkey );
4649
4650 -- Now update Ship_From_Code_Int, Ship_From_Code_Ext columns on ECE_AR_TRX_HEADERS
4651 -- using the values obtained the Header 1 Select.
4652
4653 xProgress := '2710-70';
4654 ec_debug.pl ( 3, 'l_header_1_tbl(nPos5).value: ',l_header_1_tbl(nPos5).value );
4655 ec_debug.pl ( 3, 'l_header_1_tbl(nPos5).ext_val1: ',l_header_1_tbl(nPos5).ext_val1 );
4656
4657 -- Bug 1992730 : Modified the SHIP_FROM_CODE_EXT to SHIP_FROM_CODE_EXT1 in sql below.
4658 -- Bug 1979725. Also update ship_from_code_ext1..ext5
4659 UPDATE ECE_AR_TRX_HEADERS
4660 SET ship_from_code_int = l_header_1_tbl(nPos5).value,
4661 ship_from_code_ext1 = l_header_1_tbl(nPos5).ext_val1,
4662 ship_from_code_ext2 = l_header_1_tbl(nPos5).ext_val2,
4663 ship_from_code_ext3 = l_header_1_tbl(nPos5).ext_val3,
4664 ship_from_code_ext4 = l_header_1_tbl(nPos5).ext_val4,
4665 ship_from_code_ext5 = l_header_1_tbl(nPos5).ext_val5
4666 WHERE transaction_record_id = l_header_fkey;
4667
4668 IF SQL%NOTFOUND
4669 THEN
4670 ec_debug.pl ( 0,
4671 'EC',
4672 'ECE_NO_ROW_UPDATED',
4673 'PROGRESS_LEVEL',
4674 xProgress,
4675 'INFO',
4676 'SHIP FROM CODE',
4677 'TABLE_NAME',
4678 'ECE_AR_TRX_HEADERS' );
4679 END IF;
4680
4681 -- ******************************************
4682 --
4683 -- Call custom program stub to populate the extension table
4684 --
4685 -- ******************************************
4686 -- BUG 1706520: Modified the following call to populate_extension_header_1.
4687
4688 xProgress := '2720-70';
4689 ece_Ino_X.populate_extension_header_1 (l_header_1_fkey, l_header_1_tbl );
4690
4691 /* Bug 1703536 - closed the end loop to end the header 1 loop */
4692
4693 END LOOP;
4694 /* header 1 loop ends here */
4695
4696 xProgress := '2722-70';
4697 IF ( dbms_sql.last_row_count = 0 ) THEN
4698 v_LevelProcessed := 'HEADER 1';
4699 ec_debug.pl ( 1,
4700 'EC',
4701 'ECE_NO_DB_ROW_PROCESSED',
4702 'PROGRESS_LEVEL',
4703 xProgress,
4704 'LEVEL_PROCESSED',
4705 v_LevelProcessed,
4706 'TRANSACTION_TYPE',
4707 cTransaction_Type );
4708 END IF;
4709
4710 xProgress := '2730-70';
4711 dummy := dbms_sql.execute (Alw_chg_sel_c );
4712
4713 -- ***************************************************
4714 --
4715 -- allowance and charges loop starts here
4716 --
4717 -- ***************************************************
4718
4719 xProgress := '2740-70';
4720 WHILE dbms_sql.fetch_rows (Alw_chg_sel_c ) > 0
4721 LOOP --- Allowance and Charges
4722
4723 -- ***************************************************
4724 --
4725 -- store values in pl/sql table
4726 --
4727 -- ***************************************************
4728
4729 xProgress := '2750-70';
4730 FOR m IN 1..iAlw_chg_count
4731 LOOP
4732 dbms_sql.column_value ( Alw_chg_sel_c,
4733 m,
4734 l_alw_chg_tbl(m).value );
4735
4736 dbms_sql.column_value ( Alw_chg_sel_c,
4737 m,
4738 l_key_tbl(m+iHeader_count).value );
4739 END LOOP;
4740
4741 -- ***************************************************
4742 -- pass the pl/sql table in for xref
4743 -- ***************************************************
4744
4745 xProgress := '2760-70';
4746 ece_extract_utils_pub.Find_pos ( l_alw_chg_tbl,
4747 'TRANSACTION_ID',
4748 nPos1 );
4749 ec_debug.pl ( 3, 'nPos1: ',nPos1 );
4750
4751 xProgress := '2761-70';
4752 ece_extract_utils_pub.Find_pos ( l_alw_chg_tbl,
4753 'CUSTOMER_TRX_LINE_ID',
4754 nPos2 );
4755 ec_debug.pl ( 3, 'nPos2: ',nPos2 );
4756
4757 xProgress := '2762-70';
4758 ece_extract_utils_pub.Find_pos ( l_alw_chg_tbl,
4759 'LINK_TO_CUST_TRX_LINE_ID',
4760 nPos3 );
4761 ec_debug.pl ( 3, 'nPos3: ',nPos3 );
4762
4763 xProgress := '2763-70';
4764 ece_extract_utils_pub.Find_pos ( l_alw_chg_tbl,
4765 'LINE_NUMBER',
4766 nPos5 );
4767 ec_debug.pl ( 3, 'nPos5: ',nPos5 );
4768
4769 xProgress := '2764-70';
4770 ece_extract_utils_pub.Find_pos ( l_alw_chg_tbl,
4771 'ALLOWANCE_CHARGE_INDICATOR',
4772 nPos6 );
4773 ec_debug.pl ( 3, 'nPos6: ',nPos6 );
4774
4775 xProgress := '2765-70';
4776 ece_extract_utils_pub.Find_pos ( l_alw_chg_tbl,
4777 'ALLOWANCE_CHARGE_AMOUNT',
4778 nPos7 );
4779 ec_debug.pl ( 3, 'nPos7: ',nPos7 );
4780
4781 xProgress := '2766-70';
4782 ece_extract_utils_pub.Find_pos ( l_alw_chg_tbl,
4783 'SPECIAL_SERVICES_CODE',
4784 nPos8 );
4785 ec_debug.pl ( 3, 'nPos8: ',nPos8 );
4786
4787 xProgress := '2767-70';
4788 ece_extract_utils_pub.Find_pos ( l_alw_chg_tbl,
4789 'METHOD_HANDLING_CODE',
4790 nPos9 );
4791 ec_debug.pl ( 3, 'nPos9: ',nPos9 );
4792
4793 xProgress := '2768-70';
4794 ece_extract_utils_pub.Find_pos ( l_alw_chg_tbl,
4795 'SPECIAL_CHARGES_CODE',
4796 nPos10 );
4797 ec_debug.pl ( 3, 'nPos10: ',nPos10 );
4798
4799 xProgress := '2769-70';
4800 ece_extract_utils_pub.Find_pos ( l_alw_chg_tbl,
4801 'ALLOWANCE_CHARGE_DESC',
4802 nPos11 );
4803 ec_debug.pl ( 3, 'nPos11: ',nPos11 );
4804
4805 xProgress := '2770-70';
4806 ece_extract_utils_pub.Find_pos ( l_alw_chg_tbl,
4807 'AGENCY_QUALIFIER_CODE',
4808 nPos12 );
4809 ec_debug.pl ( 3, 'nPos12: ',nPos12 );
4810
4811 xProgress := '2771-70';
4812 ece_extract_utils_pub.Find_pos ( l_alw_chg_tbl,
4813 'ALLOWANCE_CHARGE_RATE',
4814 nPos13 );
4815 ec_debug.pl ( 3, 'nPos13: ',nPos13 );
4816
4817 xProgress := '2772-70';
4818 ece_extract_utils_pub.Find_pos ( l_alw_chg_tbl,
4819 'ALLOWANCE_CHARGE_PCT_QUALIFIER',
4820 nPos14 );
4821 ec_debug.pl ( 3, 'nPos14: ',nPos14 );
4822
4823 xProgress := '2773-70';
4824 ece_extract_utils_pub.Find_pos ( l_alw_chg_tbl,
4825 'ALLOWANCE_CHARGE_PCT',
4826 nPos15 );
4827 ec_debug.pl ( 3, 'nPos15: ',nPos15 );
4828
4829 xProgress := '2774-70';
4830 ece_extract_utils_pub.Find_pos ( l_alw_chg_tbl,
4831 'ALLOWANCE_CHARGE_UOM_CODE',
4832 nPos16 );
4833 ec_debug.pl ( 3, 'nPos16: ',nPos16 );
4834
4835 xProgress := '2775-70';
4836 ece_extract_utils_pub.Find_pos ( l_alw_chg_tbl,
4837 'ALLOWANCE_CHARGE_QUANTITY',
4838 nPos17 );
4839 ec_debug.pl ( 3, 'nPos17: ',nPos17 );
4840
4841 xProgress := '2776-70';
4842 ece_extract_utils_pub.Find_pos ( l_alw_chg_tbl,
4843 'HEADER_DETAIL_INDICATOR',
4844 nPos18 );
4845 ec_debug.pl ( 3, 'nPos18: ',nPos18 );
4846
4847 -- Check for automotive installation and call the procedure get_allownace_charge.
4848 -- Both the header and detail level charges are populated here
4849 -- If the allowances and charges are incorporated as part of the standard product
4850 -- in future, then modify the code appropriately to exclude check about automotive
4851 -- installation
4852
4853 l_data_found := FALSE;
4854
4855 xProgress := '2780-70';
4856 IF l_alw_chg_tbl(nPos3).value = 0
4857 THEN
4858 l_alw_chg_tbl(nPos18).value := 'H';
4859 l_alw_chg_tbl(nPos5).value := 0;
4860 ELSE
4861 l_alw_chg_tbl(nPos18).value := 'D';
4862 END IF;
4863 ec_debug.pl ( 3, 'l_alw_chg_tbl(nPos18).value: ',l_alw_chg_tbl(nPos18).value );
4864
4865 xProgress := '2790-70';
4866 IF l_Automotive_Installed
4867 THEN
4868 xProgress := '2800-70';
4869 /* l_data_found := ece_ino_stub.ece_auto_stub ( l_alw_chg_tbl(nPos2).value,
4870 l_delivery_id,
4871 l_alw_chg_tbl(nPos18).value,
4872 l_veh_alw_chg_tbl );*/
4873 null;
4874 END IF;
4875
4876 IF l_data_found
4877 THEN
4878
4879 xProgress := '2810-70';
4880 /* FOR i IN 0 .. l_veh_alw_chg_tbl.COUNT - 1
4881 LOOP
4882
4883 xProgress := '2811-70';
4884 l_alw_chg_tbl(nPos6).value := l_veh_alw_chg_tbl(i).charge_type;
4885 ec_debug.pl ( 3, 'l_alw_chg_tbl(nPos6).value: ',l_alw_chg_tbl(nPos6).value );
4886
4887 xProgress := '2812-70';
4888 l_alw_chg_tbl(nPos7).value := ABS(l_veh_alw_chg_tbl(i).amount);
4889 ec_debug.pl ( 3, 'l_alw_chg_tbl(nPos7).value: ',l_alw_chg_tbl(nPos7).value );
4890
4891 xProgress := '2813-70';
4892 l_alw_chg_tbl(nPos8).value := l_veh_alw_chg_tbl(i).special_services_code;
4893 ec_debug.pl ( 3, 'l_alw_chg_tbl(nPos8).value: ',l_alw_chg_tbl(nPos8).value );
4894
4895 xProgress := '2814-70';
4896 l_alw_chg_tbl(nPos9).value := l_veh_alw_chg_tbl(i).method_handling_code;
4897 ec_debug.pl ( 3, 'l_alw_chg_tbl(nPos9).value: ',l_alw_chg_tbl(nPos9).value );
4898
4899 xProgress := '2815-70';
4900 l_alw_chg_tbl(nPos10).value := l_veh_alw_chg_tbl(i).special_charge_code;
4901 ec_debug.pl ( 3, 'l_alw_chg_tbl(nPos10).value: ',l_alw_chg_tbl(nPos10).value );
4902
4903 xProgress := '2816-70';
4904 l_alw_chg_tbl(nPos11).value := l_veh_alw_chg_tbl(i).description;
4905 ec_debug.pl ( 3, 'l_alw_chg_tbl(nPos11).value: ',l_alw_chg_tbl(nPos11).value );
4906
4907 xProgress := '2817-70';
4908 l_alw_chg_tbl(nPos12).value := l_veh_alw_chg_tbl(i).agency_qualifier_code;
4909 ec_debug.pl ( 3, 'l_alw_chg_tbl(nPos12).value: ',l_alw_chg_tbl(nPos12).value );
4910
4911 xProgress := '2818-70';
4912 l_alw_chg_tbl(nPos13).value := NVL(l_veh_alw_chg_tbl(i).allowance_charge_rate,0);
4913 ec_debug.pl ( 3, 'l_alw_chg_tbl(nPos13).value: ',l_alw_chg_tbl(nPos13).value );
4914
4915 xProgress := '2819-70';
4916 l_alw_chg_tbl(nPos14).value := l_veh_alw_chg_tbl(i).allowance_charge_pct_qualifier;
4917 ec_debug.pl ( 3, 'l_alw_chg_tbl(nPos14).value: ',l_alw_chg_tbl(nPos14).value );
4918
4919 xProgress := '2820-70';
4920 l_alw_chg_tbl(nPos15).value := NVL(l_veh_alw_chg_tbl(i).allowance_charge_pct,0);
4921 ec_debug.pl ( 3, 'l_alw_chg_tbl(nPos15).value: ',l_alw_chg_tbl(nPos15).value );
4922
4923 xProgress := '2821-70';
4924 l_alw_chg_tbl(nPos16).value := l_veh_alw_chg_tbl(i).unit_of_measure_code;
4925 ec_debug.pl ( 3, 'l_alw_chg_tbl(nPos16).value: ',l_alw_chg_tbl(nPos16).value );
4926
4927 xProgress := '2822-70';
4928 l_alw_chg_tbl(nPos17).value := NVL(l_veh_alw_chg_tbl(i).quantity,0);
4929 ec_debug.pl ( 3, 'l_alw_chg_tbl(nPos17).value: ',l_alw_chg_tbl(nPos17).value );
4930
4931 xProgress := '2830-70';
4932 ec_code_Conversion_pvt.populate_plsql_tbl_with_extval ( p_api_version_number => 1.0,
4933 p_init_msg_list => init_msg_list,
4934 p_simulate => simulate,
4935 p_commit => commt,
4936 p_validation_level => validation_level,
4937 p_return_status => return_status,
4938 p_msg_count => msg_count,
4939 p_msg_data => msg_data,
4940 p_key_tbl => l_key_tbl,
4941 p_tbl => l_alw_chg_tbl );
4942
4943 xProgress := '2840-70';
4944 BEGIN
4945 SELECT ece_ar_trx_allowance_charges_s.nextval
4946 INTO l_alw_chg_fkey
4947 FROM sys.dual;
4948 EXCEPTION
4949 WHEN NO_DATA_FOUND THEN
4950 ec_debug.pl ( 1,
4951 'EC',
4952 'ECE_GET_NEXT_SEQ_FAILED',
4953 'PROGRESS_LEVEL',
4954 xProgress,
4955 'SEQ',
4956 'ECE_AR_TRX_ALLOWANCE_CHARGES_S' );
4957 END;
4958 ec_debug.pl ( 3, 'l_alw_chg_fkey: ',l_alw_chg_fkey );
4959
4960 xProgress := '2850-70';
4961 ece_Extract_Utils_PUB.insert_into_interface_tbl ( iRun_id,
4962 cTransaction_Type,
4963 cCommunication_Method,
4964 cAlw_chg_Interface,
4965 l_alw_chg_tbl,
4966 l_alw_chg_fkey );
4967
4968
4969 -- ******************************************
4970 --
4971 -- Call custom program stub to populate the extension table
4972 --
4973 -- ******************************************
4974 -- ece_Ino_X.populate_extension_line(l_alw_chg_fkey, l_alw_chg_tbl);
4975
4976 END LOOP; -- l_veh_alw_chg loop */
4977
4978 END IF;
4979
4980 END LOOP; -- allowence and charges
4981
4982 xProgress := '2854-70';
4983 IF ( dbms_sql.last_row_count = 0 ) THEN
4984 v_LevelProcessed := 'ALLOWANCE CHARGES';
4985 ec_debug.pl ( 1,
4986 'EC',
4987 'ECE_NO_DB_ROW_PROCESSED',
4988 'PROGRESS_LEVEL',
4989 xProgress,
4990 'LEVEL_PROCESSED',
4991 v_LevelProcessed,
4992 'TRANSACTION_TYPE',
4993 cTransaction_Type);
4994 END IF;
4995
4996 -- the parameter of this procedure has not been finalized!!
4997 -- ALL of you has to create this package in a seperate file
4998 -- even if it is empty.
4999
5000 xProgress := '2860-70';
5001 dummy := dbms_sql.execute ( Line_sel_c );
5002
5003 -- ***************************************************
5004 --
5005 -- line loop starts here
5006 --
5007 -- ***************************************************
5008
5009 xProgress := '2870-70';
5010 WHILE dbms_sql.fetch_rows ( Line_sel_c ) > 0
5011 LOOP --- Line
5012
5013 -- ***************************************************
5014 --
5015 -- store values in pl/sql table
5016 --
5017 -- ***************************************************
5018
5019 xProgress := '2880-70';
5020 FOR j IN 1..iLine_count
5021 LOOP
5022 dbms_sql.column_value ( Line_sel_c,
5023 j,
5024 l_line_tbl(j).value );
5025
5026 dbms_sql.column_value ( Line_sel_c,
5027 j,
5028 l_key_tbl(j+iHeader_count).value );
5029 END LOOP;
5030
5031 xProgress := '2890-70';
5032 ece_extract_utils_pub.Find_pos ( l_line_tbl,
5033 'ITEM_ID',
5034 nPos1 );
5035 ec_debug.pl ( 3, 'nPos1: ',nPos1 );
5036
5037 xProgress := '2891-70';
5038 ece_extract_utils_pub.Find_pos ( l_line_tbl,
5039 'LINE_NUMBER',
5040 nPos9 );
5041 ec_debug.pl ( 3, 'nPos9: ',nPos9 );
5042
5043 xProgress := '2900-70';
5044 ece_inventory.get_item_number ( l_line_tbl(nPos1).value,
5045 l_Organization_ID,
5046 l_line_item_number,
5047 l_line_item_attrib_category,
5048 l_line_item_attribute1,
5049 l_line_item_attribute2,
5050 l_line_item_attribute3,
5051 l_line_item_attribute4,
5052 l_line_item_attribute5,
5053 l_line_item_attribute6,
5054 l_line_item_attribute7,
5055 l_line_item_attribute8,
5056 l_line_item_attribute9,
5057 l_line_item_attribute10,
5058 l_line_item_attribute11,
5059 l_line_item_attribute12,
5060 l_line_item_attribute13,
5061 l_line_item_attribute14,
5062 l_line_item_attribute15 );
5063
5064 -- ***************************************************
5065 -- pass the pl/sql table in for xref
5066 -- ***************************************************
5067
5068 xProgress := '2910-70';
5069 ec_code_Conversion_pvt.populate_plsql_tbl_with_extval ( p_api_version_number => 1.0,
5070 p_init_msg_list => init_msg_list,
5071 p_simulate => simulate,
5072 p_commit => commt,
5073 p_validation_level => validation_level,
5074 p_return_status => return_status,
5075 p_msg_count => msg_count,
5076 p_msg_data => msg_data,
5077 p_key_tbl => l_key_tbl,
5078 p_tbl => l_line_tbl );
5079
5080 xProgress := '2920-70';
5081 BEGIN
5082 SELECT ece_ar_trx_lines_s.nextval
5083 INTO l_line_fkey
5084 FROM sys.dual;
5085 EXCEPTION
5086 WHEN NO_DATA_FOUND THEN
5087 ec_debug.pl ( 1,
5088 'EC',
5089 'ECE_GET_NEXT_SEQ_FAILED',
5090 'PROGRESS_LEVEL',
5091 xProgress,
5092 'SEQ',
5093 'ECE_AR_TRX_LINES_S' );
5094 END;
5095 ec_debug.pl ( 3, 'l_line_fkey: ',l_line_fkey );
5096
5097 xProgress := '2930-70';
5098 ece_Extract_Utils_PUB.insert_into_interface_tbl ( iRun_id,
5099 cTransaction_Type,
5100 cCommunication_Method,
5101 cLine_Interface,
5102 l_line_tbl,
5103 l_line_fkey );
5104
5105 -- Now update the columns values of which have been obtained thru the procedure
5106 -- calls.
5107
5108 xProgress := '2940-70';
5109 UPDATE ece_ar_trx_lines
5110 SET line_item_number = l_line_item_number,
5111 line_item_attrib_category = l_line_item_attrib_category,
5112 line_item_attribute1 = l_line_item_attribute1,
5113 line_item_attribute2 = l_line_item_attribute2,
5114 line_item_attribute3 = l_line_item_attribute3,
5115 line_item_attribute4 = l_line_item_attribute4,
5116 line_item_attribute5 = l_line_item_attribute5,
5117 line_item_attribute6 = l_line_item_attribute6,
5118 line_item_attribute7 = l_line_item_attribute7,
5119 line_item_attribute8 = l_line_item_attribute8,
5120 line_item_attribute9 = l_line_item_attribute9,
5121 line_item_attribute10 = l_line_item_attribute10,
5122 line_item_attribute11 = l_line_item_attribute11,
5123 line_item_attribute12 = l_line_item_attribute12,
5124 line_item_attribute13 = l_line_item_attribute13,
5125 line_item_attribute14 = l_line_item_attribute14,
5126 line_item_attribute15 = l_line_item_attribute15
5127 WHERE transaction_record_id = l_line_fkey;
5128
5129 IF SQL%NOTFOUND
5130 THEN
5131 ec_debug.pl ( 1,
5132 'EC',
5133 'ECE_NO_ROW_UPDATED',
5134 'PROGRESS_LEVEL',
5135 xProgress,
5136 'INFO',
5137 'LINE ITEM',
5138 'TABLE_NAME',
5139 'ECE_AR_TRX_LINES' );
5140 END IF;
5141
5142
5143 xProgress := '2941-70';
5144 ece_extract_utils_pub.Find_pos ( l_line_tbl,
5145 'NET_WEIGHT',
5146 nPos20 );
5147 ec_debug.pl ( 3, 'nPos20: ',nPos20);
5148
5149 xProgress := '2942-70';
5150 ece_extract_utils_pub.Find_pos ( l_line_tbl,
5151 'GROSS_WEIGHT',
5152 nPos21 );
5153 ec_debug.pl ( 3, 'nPos21: ',nPos21);
5154
5155 xProgress := '2943-70';
5156 ece_extract_utils_pub.Find_pos ( l_line_tbl,
5157 'VOLUME',
5158 nPos23 );
5159 ec_debug.pl ( 3, 'nPos23: ',nPos23);
5160
5161 xProgress := '2944-70';
5162 ece_extract_utils_pub.Find_pos ( l_line_tbl,
5163 'WEIGHT_UOM_CODE_INT',
5164 nPos24 );
5165 ec_debug.pl ( 3, 'nPos24: ',nPos24);
5166
5167 xProgress := '2945-70';
5168 ece_extract_utils_pub.Find_pos ( l_line_tbl,
5169 'VOLUME_UOM_CODE_INT',
5170 nPos25 );
5171 ec_debug.pl ( 3, 'nPos25: ',nPos25);
5172
5173 xProgress := '2946-70';
5174 ece_extract_utils_pub.Find_pos ( l_line_tbl,
5175 'SHIPMENT_NUMBER',
5176 nPos26 );
5177 ec_debug.pl ( 3, 'nPos26: ',nPos26);
5178
5179 xProgress := '2947-70';
5180 ece_extract_utils_pub.Find_pos ( l_line_tbl,
5181 'BOOKING_NUMBER',
5182 nPos27 );
5183 ec_debug.pl ( 3, 'nPos27: ',nPos27);
5184
5185 l_net_weight := nvl(l_line_tbl(nPos20).value,0) + l_net_weight;
5186 l_gross_weight := nvl(l_line_tbl(nPos21).value,0) + l_gross_weight;
5187 l_volume := nvl(l_line_tbl(nPos23).value,0) + l_volume;
5188 l_weight_uom_code := nvl(l_line_tbl(nPos24).value,null);
5189 l_volume_uom_code := nvl(l_line_tbl(nPos25).value,null);
5190 l_shipment_number := nvl(l_line_tbl(nPos26).value,0);
5191 l_booking_number := nvl(l_line_tbl(nPos27).value,null);
5192
5193 ec_debug.pl ( 3, 'l_line_tbl(nPos20).value: ',l_line_tbl(nPos20).value );
5194 ec_debug.pl ( 3, 'l_line_tbl(nPos21).value: ',l_line_tbl(nPos21).value );
5195 ec_debug.pl ( 3, 'l_line_tbl(nPos23).value: ',l_line_tbl(nPos23).value );
5196 ec_debug.pl ( 3, 'Net Weight:', l_net_weight);
5197 ec_debug.pl ( 3, 'Gross Weight:',l_gross_weight);
5198 ec_debug.pl ( 3, 'Weight UOM Code:',l_weight_uom_code);
5199 ec_debug.pl ( 3, 'Volume:',l_volume);
5200 ec_debug.pl ( 3, 'Volume UOM Code:',l_volume_uom_code);
5201 ec_debug.pl ( 3, 'Shipment Number:',l_shipment_number);
5202 ec_debug.pl ( 3, 'Booking Number:',l_booking_number);
5203
5204
5205 -- ******************************************
5206 --
5207 -- Call custom program stub to populate the extension table
5208 --
5209 -- ******************************************
5210
5211 xProgress := '2950-70';
5212 ece_Ino_X.populate_extension_line( l_line_fkey,
5213 l_line_tbl );
5214
5215 -- the parameter of this procedure has not been finalized!!
5216 -- ALL of you has to create this package in a seperate file
5217 -- even if it is empty.
5218
5219 -- ***************************************************
5220 --
5221 -- set LINE_NUMBER values
5222 --
5223 -- ***************************************************
5224
5225 xProgress := '2960-70';
5226 dbms_sql.bind_variable (Line_t_sel_c,
5227 'LINE_NUMBER',
5228 l_line_tbl(nPos9).value );
5229
5230 xProgress := '2970-70';
5231 dummy := dbms_sql.execute ( Line_t_sel_c );
5232
5233 -- ***************************************************
5234 --
5235 -- line tax loop starts here
5236 --
5237 -- ***************************************************
5238
5239 xProgress := '2980-70';
5240 WHILE dbms_sql.fetch_rows ( Line_t_sel_c ) > 0
5241 LOOP --- Line Tax
5242
5243 -- ***************************************************
5244 --
5245 -- store values in pl/sql table
5246 --
5247 -- ***************************************************
5248
5249 xProgress := '2990-70';
5250 FOR k IN 1..iLine_t_count
5251 LOOP
5252 dbms_sql.column_value ( Line_t_sel_c,
5253 k,
5254 l_line_t_tbl(k).value );
5255
5256 dbms_sql.column_value ( Line_t_sel_c,
5257 k,
5258 l_key_tbl(k+iHeader_count+iLine_count).value );
5259 END LOOP;
5260
5261
5262 xProgress := '3000-70';
5263 ece_extract_utils_pub.Find_pos ( l_line_t_tbl,
5264 'LINE_TYPE',
5265 nPos1 );
5266 ec_debug.pl ( 3, 'nPos1: ',nPos1 );
5267
5268 xProgress := '3002-70';
5269 ece_extract_utils_pub.Find_pos ( l_line_t_tbl,
5270 'LINK_TO_CUST_TRX_LINE_ID',
5271 nPos2 );
5272 ec_debug.pl ( 3, 'nPos2: ',nPos2 );
5273
5274 xProgress := '3004-70';
5275 ece_extract_utils_pub.Find_pos ( l_line_t_tbl,
5276 'CUSTOMER_TRX_LINE_ID',
5277 nPos3 );
5278 ec_debug.pl ( 3, 'nPos3: ',nPos3 );
5279
5280 xProgress := '3006-70';
5281 ece_extract_utils_pub.Find_pos ( l_line_t_tbl,
5282 'TAX_AMOUNT',
5283 nPos4 );
5284 ec_debug.pl ( 3, 'nPos4: ',nPos4 );
5285
5286
5287 xProgress := '3010-70';
5288 BEGIN
5289 SELECT ece_ar_trx_line_tax_s.nextval
5290 INTO l_line_t_fkey
5291 FROM sys.dual;
5292 EXCEPTION
5293 WHEN NO_DATA_FOUND THEN
5294 ec_debug.pl ( 1,
5295 'EC',
5296 'ECE_GET_NEXT_SEQ_FAILED',
5297 'PROGRESS_LEVEL',
5298 xProgress,
5299 'SEQ',
5300 'ECE_AR_TRX_LINE_TAX_S' );
5301 END;
5302
5303 ec_debug.pl ( 3, 'l_line_t_fkey: ',l_line_t_fkey );
5304
5305 -- ******************************************
5306 -- pass the pl/sql table in for xref
5307 -- ******************************************
5308
5309
5310 xProgress := '3020-70';
5311 ec_code_Conversion_pvt.populate_plsql_tbl_with_extval ( p_api_version_number => 1.0,
5312 p_init_msg_list => init_msg_list,
5313 p_simulate => simulate,
5314 p_commit => commt,
5315 p_validation_level => validation_level,
5316 p_return_status => return_status,
5317 p_msg_count => msg_count,
5318 p_msg_data => msg_data,
5319 p_key_tbl => l_key_tbl,
5320 p_tbl => l_line_t_tbl );
5321
5322 xProgress := '3030-70';
5323 ece_Extract_Utils_PUB.insert_into_interface_tbl ( iRun_id,
5324 cTransaction_Type,
5325 cCommunication_Method,
5326 cLine_t_Interface,
5327 l_line_t_tbl,
5328 l_line_t_fkey );
5329
5330 -- ******************************************
5331 --
5332 -- Call custom program stub to populate the extension table
5333 --
5334 -- ******************************************
5335
5336 xProgress := '3040-70';
5337 ece_Ino_X.populate_extension_line_tax ( l_line_t_fkey,
5338 l_line_t_tbl );
5339
5340 END LOOP;
5341
5342 xProgress := '3042-70';
5343 IF ( dbms_sql.last_row_count = 0 )
5344 THEN
5345 v_LevelProcessed := 'LINE TAX';
5346 ec_debug.pl ( 1,
5347 'EC',
5348 'ECE_NO_DB_ROW_PROCESSED',
5349 'PROGRESS_LEVEL',
5350 xProgress,
5351 'LEVEL_PROCESSED',
5352 v_LevelProcessed,
5353 'TRANSACTION_TYPE',
5354 cTransaction_Type );
5355 END IF;
5356
5357 END LOOP;
5358
5359 xProgress := '3044-70';
5360 IF ( dbms_sql.last_row_count = 0 )
5361 THEN
5362 v_LevelProcessed := 'LINE';
5363 ec_debug.pl ( 1,
5364 'EC',
5365 'ECE_NO_DB_ROW_PROCESSED',
5366 'PROGRESS_LEVEL',
5367 xProgress,
5368 'LEVEL_PROCESSED',
5369 v_LevelProcessed,
5370 'TRANSACTION_TYPE',
5371 cTransaction_Type );
5372 END IF;
5373
5374 xProgress := '3046-70';
5375 UPDATE ece_ar_trx_headers
5376 SET net_weight = l_net_weight,
5377 gross_weight = l_gross_weight,
5378 volume = l_volume,
5379 weight_uom_code_int = l_weight_uom_code,
5380 volume_uom_code_int = l_volume_uom_code,
5381 booking_number = l_booking_number
5382 WHERE transaction_record_id = l_header_fkey;
5383
5384
5385 /* Bug 1979725* begin*/
5386 /* Copy the value of ext1 to ext5 for
5387 weight uom code and volume uom code
5388 from lines to header
5389 */
5390
5391 xProgress := '3044-70.1';
5392 begin
5393 select weight_uom_code_ext1,
5394 weight_uom_code_ext2,
5395 weight_uom_code_ext3,
5396 weight_uom_code_ext4,
5397 weight_uom_code_ext5
5398 into
5399 l_weight_uom_code_ext1,
5400 l_weight_uom_code_ext2,
5401 l_weight_uom_code_ext3,
5402 l_weight_uom_code_ext4,
5403 l_weight_uom_code_ext5
5404 from
5405 ece_ar_trx_lines
5406 where weight_uom_code_int = l_weight_uom_code
5407 and rownum < 2;
5408
5409 exception
5410 when no_data_found then
5411 ec_debug.pl(1,'no data found',xprogress);
5412 when others then
5413 ec_debug.pl(1,xprogress);
5414 end;
5415
5416
5417
5418 xProgress := '3044-70.2';
5419 begin
5420 select volume_uom_code_ext1,
5421 volume_uom_code_ext2,
5422 volume_uom_code_ext3,
5423 volume_uom_code_ext4,
5424 volume_uom_code_ext5
5425 into
5426 l_volume_uom_code_ext1,
5427 l_volume_uom_code_ext2,
5428 l_volume_uom_code_ext3,
5429 l_volume_uom_code_ext4,
5430 l_volume_uom_code_ext5
5431 from
5432 ece_ar_trx_lines
5433 where volume_uom_code_int = l_volume_uom_code
5434 and rownum < 2;
5435
5436 exception
5437 when no_data_found then
5438 ec_debug.pl(1,'no data found',xprogress);
5439 when others then
5440 ec_debug.pl(1,xprogress);
5441 end;
5442
5443
5444 xProgress := '3044-70.3';
5445 begin
5446 update ece_ar_trx_headers
5447 set
5448 volume_uom_code_ext1 = l_volume_uom_code_ext1,
5449 volume_uom_code_ext2 = l_volume_uom_code_ext2,
5450 volume_uom_code_ext3 = l_volume_uom_code_ext3,
5451 volume_uom_code_ext4 = l_volume_uom_code_ext4,
5452 volume_uom_code_ext5 = l_volume_uom_code_ext5,
5453 weight_uom_code_ext1 = l_weight_uom_code_ext1,
5454 weight_uom_code_ext2 = l_weight_uom_code_ext2,
5455 weight_uom_code_ext3 = l_weight_uom_code_ext3,
5456 weight_uom_code_ext4 = l_weight_uom_code_ext4,
5457 weight_uom_code_ext5 = l_weight_uom_code_ext5
5458 where
5459 transaction_record_id = l_header_fkey;
5460
5461
5462 exception
5463 when no_data_found then
5464 ec_debug.pl(1,'no data found',xprogress);
5465 when others then
5466 ec_debug.pl(1,xprogress);
5467 end;
5468 /* Bug 1979925 end */
5469
5470
5471 /* Bug 1703536 -Commented the following and was moved to the
5472 ** end of the header 1 loop
5473 */
5474 /*****
5475 END LOOP;
5476
5477 xProgress := '3046-70';
5478 IF ( dbms_sql.last_row_count = 0 ) THEN
5479 v_LevelProcessed := 'HEADER 1';
5480 ec_debug.pl ( 1,
5481 'EC',
5482 'ECE_NO_DB_ROW_PROCESSED',
5483 'PROGRESS_LEVEL',
5484 xProgress,
5485 'LEVEL_PROCESSED',
5486 v_LevelProcessed,
5487 'TRANSACTION_TYPE',
5488 cTransaction_Type );
5489 END IF;
5490 *****/
5491 END LOOP;
5492
5493 xProgress := '3048-70';
5494 IF ( dbms_sql.last_row_count = 0 ) THEN
5495 v_LevelProcessed := 'HEADER';
5496 ec_debug.pl ( 1,
5497 'EC',
5498 'ECE_NO_DB_ROW_PROCESSED',
5499 'PROGRESS_LEVEL',
5500 xProgress,
5501 'LEVEL_PROCESSED',
5502 v_LevelProcessed,
5503 'TRANSACTION_TYPE',
5504 cTransaction_Type );
5505 END IF;
5506
5507 xProgress := '3050-70';
5508 -- COMMIT;
5509
5510 xProgress := '3060-70';
5511 dbms_sql.close_cursor ( Header_sel_c );
5512
5513 xProgress := '3062-70';
5514 dbms_sql.close_cursor ( Header_1_sel_c );
5515
5516 xProgress := '3064-70';
5517 dbms_sql.close_cursor ( Alw_chg_sel_c );
5518
5519 xProgress := '3066-70';
5520 dbms_sql.close_cursor ( Line_sel_c );
5521
5522 xProgress := '3068-70';
5523 dbms_sql.close_cursor ( Line_t_sel_c );
5524
5525 ec_debug.pop ( 'ece_ar_transaction.Populate_AR_Trx' );
5526
5527 EXCEPTION
5528 WHEN OTHERS THEN
5529
5530 ec_debug.pl ( 0,
5531 'EC',
5532 'ECE_PROGRAM_ERROR',
5533 'PROGRESS_LEVEL',
5534 xProgress );
5535
5536 ec_debug.pl ( 0,
5537 'EC',
5538 'ECE_ERROR_MESSAGE',
5539 'ERROR_MESSAGE',
5540 SQLERRM );
5541
5542 app_exception.raise_exception;
5543
5544 END Populate_AR_Trx;
5545
5546 BEGIN
5547
5548 xProgress := '2000-80';
5549 oe_profile.get('SO_ORGANIZATION_ID',l_Organization_ID);
5550
5551 xProgress := '2010-80';
5552 IF (l_Automotive_Status IS NULL) THEN
5553 xProgress := '2020-80';
5554 l_Automotive_Installed := fnd_installation.get_app_info('VEH',l_Status,l_Industry,l_Schema);
5555 l_Automotive_Status := l_Status;
5556 ec_debug.pl(3,'l_Automotive_Status: ',l_Automotive_Status);
5557 END IF;
5558
5559 xProgress := '2030-80';
5560 IF ( l_Automotive_Status = 'I' )
5561 THEN
5562 l_Automotive_Installed := TRUE;
5563 ELSE
5564 l_Automotive_Installed := FALSE;
5565 END IF;
5566
5567 EXCEPTION
5568 WHEN OTHERS THEN
5569
5570 ec_debug.pl ( 0,
5571 'EC',
5572 'ECE_PROGRAM_ERROR',
5573 'PROGRESS_LEVEL',
5574 xProgress );
5575
5576 ec_debug.pl ( 0,
5577 'EC',
5578 'ECE_ERROR_MESSAGE',
5579 'ERROR_MESSAGE',
5580 SQLERRM );
5581
5582 app_exception.raise_exception;
5583
5584 END ece_ar_transaction;