[Home] [Help]
PACKAGE BODY: APPS.JMF_GTA_TXT_OPERATOR_PROC
Source
1 PACKAGE BODY JMF_GTA_TXT_OPERATOR_PROC AS
2 --$Header: JMFRIETB.pls 120.36.12010000.8 2009/01/09 05:57:31 yaozhan ship $
3 --+=======================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JMFRIETB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package consists of server procedures, which are used to |
13 --| export customers, items and invoice to flat files respectively, |
14 --| also there is a procedure to import data from GT through flat |
15 --| file |
16 --| |
17 --| PROCEDURE LIST |
18 --| PROCEDURE Read_GT_Line |
19 --| PROCEDURE Put_Line |
20 --| PROCEDURE Put_Log |
21 --| PROCEDURE Clear_Imp_Temp_Table |
22 --| PROCEDURE Export_Invoice |
23 --| PROCEDURE Export_Invoices |
24 --| PROCEDURE Check_Batch_Number |
25 --| PROCEDURE Export_Invoices_From_Conc |
26 --| PROCEDURE Export_Invoices_From_Workbench |
27 --| FUNCTION Check_Header |
28 --| FUNCTION Check_Line_Length |
29 --| PROCEDURE Export_Customers |
30 --| FUNCTION Check_Item_Length |
31 --| PROCEDURE Export_To_Flat_File |
32 --| PROCEDURE Export_Items |
33 --| |
34 --| |
35 --| HISTORY |
36 --| 05/12/2005 Jogen Hu Created |
37 --| 05/17/2005 Jim Zheng Add procedure Export_Customers |
38 --| 05/17/2005 Donghai Wang Add procedure Export_Items |
39 --| 07/28/005 Jim Zheng Update Customer Export |
40 --| 09/28/2005 Jogen Hu Change procedure Export_invoices|
41 --| Export_Invoices_From_Conc and import_invoices |
42 --| 09/29/2005 Jim.Zheng Update Customer Export, give up |
43 --| the tax_payer_id export |
44 --| 10/10/2005 Jim.Zheng Change log level of Customer_ex |
45 --| port |
46 --| 13/10/2005 Jogen Hu Change due to message change |
47 --| |
48 --| 14/10/2005 Jim.Zheng Add org_id condition in |
49 --| export_customer |
50 --| when select custoemr site id |
51 --| 18/10/2005 Donghai Wang Update procedure |
52 --| 'Export_To_Flat_File' in order |
53 --| not to export tax rate any |
54 --| longer due to ebtax enabled |
55 --| 20/10/2005 Jogen Hu Change invoice description check|
56 --| 21/10/2005 Jogen Hu Add debug log message |
57 --| 22/10/2005 Jogen Hu update batch number source for |
58 --| export invoices from workbench |
59 --| 08/11/2005 jim Zheng update export_cusomters. Add '~~'
60 --| because the tax_payer_id should |
61 --| be leave blank '~~~~' |
62 --| 09/11/2005 Jogen Hu Update GTA invoice to add GT info
63 --| in import_invoices |
64 --| 10/11/2005 Jim Zheng Update customer export cause by |
65 --| Bank account mask profile change|
66 --| 11/11/2005 Jim Zheng Update customer export because |
67 --| the bank account mask profile |
68 --| value change to NO MASK |
69 --| 14/11/2005 Jim Zheng Update the customer export, |
70 --| Delete the blank when the column|
71 --| is null. |
72 --| 16/11/2005 Jogen Hu Update invoices export because |
73 --| the bank account mask profile |
74 --| value change to NO MASK |
75 --| 16/11/2005 Jim Zheng Update Export_customers for |
76 --| add message output for bank |
77 --| account mask |
78 --| 16/11/2005 Jogen Hu Update invoices import to change|
79 --| the import identifier |
80 --| verification method |
81 --| 30/11/2005 Jogen Hu Add some protection due to XML |
82 --| function |
83 --| 1/12/2005 Jogen Hu Change check_header: add |
84 --| condition when check CM |
85 --| CM decription is NULL |
86 --| 2/12/2005 Jogen Hu Modify procedure Read_GT_Line |
87 --| Add code protection for file EOF|
88 --| replace JMF_NO_DATA_FOUND with |
89 --| JMF_GTA_NO_DATA_FOUND |
90 --| 9/12/2005 Jogen Hu Modify procedure import_invoices|
91 --| exchange the comment line and |
92 --| indentifier line due to different
93 --| golden Tax system version |
94 --| 21/12/2005 Jim Zheng Update code because bank account|
95 --| uptake. Procedure export_customer
96 --| 26/12/2005 Jim Zheng fix performance issue in percedure
97 --| export_customers. |
98 --| 13/01/2006 Jim Zheng fix a variable issue in procedure
99 --| export customer when get band |
100 --| account. |
101 --| 16/01/2006 Jim Zheng update code by message change and
102 --| some error format of output file|
103 --| 07/02/2006 Jim Zheng Procedure export_customers. Add |
104 --| variable init for bank account |
105 --| export. |
106 --| 06/03/2006 Donghai Wang update procedures |
107 --| 'Check_Item_Length' and |
108 --| 'Export_Items' for adding fnd |
109 --| log |
110 --| 15/03/2006 Jogen Hu erase the log by fnd_file.log |
111 --| 21/03/2006 Jogen Hu Change data range from trunc |
112 --| parameters to DB columns by |
113 --| bug 5107043 |
114 --| 06/04/2006 Donghai Wang Update procedures |
115 --| Check_Item_Lenth,Export_To_Flat_File |
116 --| and Export_Items to remove tax_rate|
117 --| from export to fix bug 5138356 |
118 --| 22/06/2006 Jogen Hu update export_invoices to increase the|
119 --| length of l_str to fix bug 5335265,|
120 --| add '-' for batch number to fix bug|
121 --| 5351578 and change import process |
122 --| 20/09/2006 Jogen Hu update Import_invoices to |
123 --| Format date to XSD Date formate |
124 --| for Bug 5521629 |
125 --| 02/01/2007 Subba Updated 'Export_Invoices_From_Conc' |
126 --| procedure to accept 'invoice_type' |
127 --| parameter and added logic to fetch the |
128 --| invoices based on invoice_type |
129 --| 09/12/2008 Lv Xiao Modified for bug#7626503 |
130 --| Add validation of description of Special |
131 --| and Recycle VAT invoice, fixed prefix |
132 --| Add validation of unique description of |
133 --| Special and Recycle VAT invoice |
134 --| 09/18/2008 Lv Xiao Modified for bug#7644803 |
135 --| Add two more parameters while description|
136 --| of invoices duplicates with each other |
137 --| and set them to the error message |
138 --| 12/25/2008 Lv Xiao Modified for bug#7644876 |
139 --| Enlarge the string buffer of parameters: |
140 --| lv_crmemo_prefix_msg VARCHAR2(240); |
141 --| lv_trx_crmemo_prefix_msg VARCHAR2(1000); |
142 --| lv_trx_crmemo_notification_num VARCHAR2(1000); |
143 --| 12/26/2008 Yao Zhang Fix bug 7670310 Add description check logic|
144 --| for common Credit Memo |
145 --| 09/01/2009 Yao Zhang Fix bug 7673309 THE GTA INVOICE EXPORT: The same error
146 --| message is duplicated.
147 --+======================================================================*/
148
149 --TYPE COLUMN_VALUES IS TABLE OF VARCHAR2(200);
150 TYPE t_invoice_export_output IS TABLE OF VARCHAR2(5000);
151 TYPE c_trx_header_id_type IS REF CURSOR RETURN jmf_gta_trx_headers%ROWTYPE;
152
153 --===================
154 -- CONSTANTS
155 --===================
156 G_MODULE_PREFIX CONSTANT VARCHAR2(60):='jmf.plsql.JMF_GTA_TXT_OPERATOR_PROC.';
157
158 G_EXPORT_SUCC CONSTANT PLS_INTEGER:=0;
159 G_EXPORT_EXCEED_ERROR CONSTANT PLS_INTEGER:=1;
160 G_EXPORT_TAXPAYERID_ERROR CONSTANT PLS_INTEGER:=2;
161 G_EXPORT_CRMEMO_MISSING_ERROR CONSTANT PLS_INTEGER:=3;
162 G_EXPORT_MISSING CONSTANT PLS_INTEGER:=4;
163
164 --add by Lv Xiao on 9-DEC-2008, begin
165 ------------------------------------------------------------
166 G_EXPORT_MISSING_PREFIX_ERROR CONSTANT PLS_INTEGER:=5;
167 G_EXPORT_CRMEMO_DUP_ERROR CONSTANT PLS_INTEGER:=6;
168 ------------------------------------------------------------
169 --add by Lv Xiao on 9-DEC-2008, end
170
171 G_DEBUG_LEVEL CONSTANT PLS_INTEGER:=fnd_log.LEVEL_EXCEPTION;
172
173 G_import_error_prefix CONSTANT VARCHAR2(240):=
174 '<?xml version="1.0"?>
175 <ImportReport>
176 <ReportFailed>Y</ReportFailed>
177 <ReportFailedMsg>';
178
179 G_import_error_suffix CONSTANT VARCHAR2(100):='</ReportFailedMsg>
180 </ImportReport>';
181
182 g_export_delimiter VARCHAR2(2):='~~';
183 g_comment_delimiter VARCHAR2(2):='//';
184
185 gv_check_dup_flag VARCHAR2(20):='FALSE';
186
187 gv_dup_title_flag VARCHAR2(20):='FALSE';
188
189 gv_desc_duplicat_flag VARCHAR2(20) := 'FALSE';
190
191 gv_output_dup_err_flag VARCHAR2(20):='FALSE';
192
193 gv_prefix_missing_flag VARCHAR(10):= 'FALSE';
194
195 --==========================================================================
196 -- PROCEDURE NAME:
197 --
198 -- Put_Line Public
199 --
200 -- DESCRIPTION:
201 --
202 -- This procedure write data to log file.
203 --
204 -- PARAMETERS:
205 -- In: p_str VARCHAR2
206 --
207 -- Out:
208 --
209 -- DESIGN REFERENCES:
210 -- GTA-Txt-Interface-TD.doc
211 --
212 -- CHANGE HISTORY:
213 -- 05/12/05 Jogen Hu Created
214 --===========================================================================
215 PROCEDURE Put_Line
216 ( p_str IN VARCHAR2
217 )
218 IS
219 BEGIN
220 FND_FILE.Put_Line(FND_FILE.Output,p_str);
221
222 END Put_Line;
223
224 --==========================================================================
225 -- PROCEDURE NAME:
226 --
227 -- Put_Log Public
228 --
229 -- DESCRIPTION:
230 --
231 -- This procedure write data to log file.
232 --
233 -- PARAMETERS:
234 -- In: p_str VARCHAR2
235 --
236 -- Out:
237 --
238 -- DESIGN REFERENCES:
239 -- GTA-Txt-Interface-TD.doc
240 --
241 -- CHANGE HISTORY:
242 -- 05/12/05 Jogen Hu Created
243 --===========================================================================
244 PROCEDURE Put_Log
245 ( p_str IN VARCHAR2
246 )
247 IS
248 BEGIN
249 --FND_FILE.Put_Line(FND_FILE.Log,p_str);
250 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
251 THEN
252 fnd_log.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT
253 ,MODULE => G_MODULE_PREFIX||'.debug'
254 ,MESSAGE => p_str);
255 END IF;
256 END Put_Log;
257
258 --==========================================================================
259 -- PROCEDURE NAME:
260 --
261 -- Read_GT_Line Private
262 --
263 -- DESCRIPTION:
264 --
265 -- This procedure read a single line data from flat file to
266 -- seperate columns
267 --
268 -- flat file format:
269 -- the first line is a identity line
270 -- the second line is description line which format is
271 -- number of invoices~~start date~~end date
272 -- for third line, there will be a invoice header and following
273 -- lines are invoices lines belong to the header.Then another
274 -- invoice header and lines..
275 -- header: Line:
276 -- -------------------------- --------------------------------------
277 -- col1 - cancel flag discount flag(0:none,1:discount line)
278 -- col2 - list flag item name
279 -- col3 - invoice type code item model
280 -- col4 - invoice class coce unit of measure
281 -- col5 - invoice number quantity
282 -- col6 - detail lines number amount without tax
283 -- col7 - invoice date(RRRRMMDD) tax rate
284 -- col8 - tax month tax amount
285 -- col9 - invoice doc number(GTA number) price
286 -- col10 - amount without tax price flag(0: without tax,1:with tax)
287 -- col11 - tax rate item tax denomination
288 -- col12 - tax amount
289 -- col13 - customer name
290 -- col14 - customer taxpayer id
291 -- col15 - customer address phone
292 -- col16 - customer bank name account
293 -- col17 - supplier name
294 -- col18 - supplier taxpayer id
295 -- col19 - supplier address phone
296 -- col20 - supplier bank name account
297 -- col21 - comments
298 -- col22 - issuer
299 -- col23 - reviewer
300 -- col24 - payee
301 -- col25 -
302 --
303 -- PARAMETERS:
304 -- In Out: p_line_seq Read line number
305 --
306 -- Out: x_values Which contain all columns value
307 -- x_eof Whether it's End of File
308 --
309 -- DESIGN REFERENCES:
310 -- GTA-Txt-Interface-TD.doc
311 --
312 -- CHANGE HISTORY:
313 -- 05/12/05 Jogen Hu Created
314 --===========================================================================
315 PROCEDURE Read_GT_Line
316 ( x_line_seq IN OUT NOCOPY NUMBER
317 , x_values OUT NOCOPY COLUMN_VALUES
318 , x_eof OUT NOCOPY BOOLEAN
319 )
320 IS
321 l_procedure_name VARCHAR2(30) :='read_GT_Line';
322 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
323 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
324
325 BEGIN
326 --log for debug
327 IF( l_proc_level >= l_dbg_level )
328 THEN
329 FND_LOG.STRING(l_proc_level
330 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
331 ,'Enter procedure');
332 END IF; --( l_proc_level >= l_dbg_level )
333
334 x_values:=COLUMN_VALUES();
335 IF x_line_seq IS NULL
336 THEN
337 x_line_seq:=1;
338 END IF;
339
340 --get exact line number
341 SELECT MIN(import_seq)
342 INTO x_line_seq
343 FROM JMF_GTA_TRXIMP_TMP
344 WHERE import_seq>=x_line_seq;
345
346 --get a valid line and insert into out parameter.
347 LOOP
348 BEGIN
349
350 --clear the out table
351 x_values.DELETE;
352 x_values.EXTEND(25);
353
354 SELECT col1
355 , col2
356 , col3
357 , col4
358 , col5
359 , col6
360 , col7
361 , col8
362 , col9
363 , col10
364 , col11
365 , col12
366 , col13
367 , col14
368 , col15
369 , col16
370 , col17
371 , col18
372 , col19
373 , col20
374 , col21
375 , col22
376 , col23
377 , col24
378 , col25
379 INTO x_values(1)
380 , x_values(2)
381 , x_values(3)
382 , x_values(4)
383 , x_values(5)
384 , x_values(6)
385 , x_values(7)
386 , x_values(8)
387 , x_values(9)
388 , x_values(10)
389 , x_values(11)
390 , x_values(12)
391 , x_values(13)
392 , x_values(14)
393 , x_values(15)
394 , x_values(16)
395 , x_values(17)
396 , x_values(18)
397 , x_values(19)
398 , x_values(20)
399 , x_values(21)
400 , x_values(22)
401 , x_values(23)
402 , x_values(24)
403 , x_values(25)
404 FROM JMF_GTA_TRXIMP_TMP
405 WHERE import_seq=x_line_seq;
406
407 --delete the line from temporary table
408 --DELETE JMF_GTA_TRXIMP_TMP WHERE import_seq=x_line_seq;
409
410 --set the line number read next time
411 x_line_seq:=x_line_seq+1;
412
413 --filter comments line if there is still comment line in the table
414 --then we will go to next line, otherwise we already get a valid line and can exit
415 IF x_values(1) IS NOT NULL
416 THEN
417 IF instr(x_values(1),g_comment_delimiter)<1
418 THEN
419 EXIT;
420 END IF;
421 END IF;
422
423 EXCEPTION
424 WHEN NO_DATA_FOUND THEN
425 X_EOF:=TRUE;
426 EXIT;
427
428 WHEN OTHERS THEN
429 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
430 THEN
431 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
432 , G_MODULE_PREFIX || l_procedure_name
433 || '.OTHER_EXCEPTION'
434 , SQLCODE||SQLERRM);
435 END IF;
436 RAISE;
437 END;
438 END LOOP;
439
440 IF trim(x_values(1)) IS NULL
441 THEN
442 X_EOF:=TRUE;
443 END IF;
444
445 --log for debug
446 IF( l_proc_level >= l_dbg_level )
447 THEN
448 FND_LOG.STRING(l_proc_level
449 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.End'
450 ,'Exit procedure');
451 END IF; --( l_proc_level >= l_dbg_level )
452 EXCEPTION
453 WHEN NO_DATA_FOUND THEN
454 X_EOF:=TRUE;
455
456 WHEN OTHERS THEN
457 IF(l_proc_level >= l_dbg_level)
458 THEN
459 FND_LOG.string( l_proc_level
460 , G_MODULE_PREFIX || l_procedure_name||'.OTHER_EXCEPTION'
461 , SQLCODE||SQLERRM);
462 END IF;
463 RAISE;
464
465 END Read_GT_Line;
466
467 --==========================================================================
468 -- PROCEDURE NAME:
469 --
470 -- Clear_Imp_Temp_Table Private
471 --
472 -- DESCRIPTION:
473 --
474 -- This procedure clear the data imported from flat file
475 -- in temporary table
476 --
477 -- PARAMETERS:
478 -- In: None
479 -- Out: None
480 --
481 -- DESIGN REFERENCES:
482 -- GTA-Txt-Interface-TD.doc
483 --
484 -- CHANGE HISTORY:
485 -- 05/12/05 Jogen Hu Created
486 --===========================================================================
487 PROCEDURE Clear_Imp_Temp_Table
488 IS
489 PRAGMA AUTONOMOUS_TRANSACTION;
490
491 BEGIN
492 DELETE JMF_GTA_TRXIMP_TMP;
493 COMMIT;
494
495 END Clear_Imp_Temp_Table;
496
497 --==========================================================================
498 -- PROCEDURE NAME:
499 --
500 -- Import_Invoices Public
501 --
502 -- DESCRIPTION:
503 --
504 -- This procedure import VAT invoices from flat file to GTA
505 -- Because SQL*Loader will import flat file to temporary table
506 -- JMF_GTA_TRXIMP_TMP and GTA_TRX_NUMBER is a unique column
507 -- in GTA, so no parameter is needed here
508 --
509 -- PARAMETERS:
510 -- In: None
511 -- Out: None
512 --
513 -- DESIGN REFERENCES:
514 -- GTA-Txt-Interface-TD.doc
515 --
516 -- CHANGE HISTORY:
517 -- 05/12/05 Jogen Hu Created
518 -- 09/28/05 Jogen Hu Add the part to copy fp_tax_reg_number,
519 -- tp_tax_reg_number, legal_entity_id to GT line
520 --===========================================================================
521 PROCEDURE Import_Invoices
522 IS
523 l_procedure_name VARCHAR2(30):='Import_invoices';
524 L_HEADER_ID Jmf_Gta_Trx_Headers_All.Gta_Trx_Header_Id%TYPE;
525 l_line_seq NUMBER;
526 l_values COLUMN_VALUES:=COLUMN_VALUES(NULL);
527 l_EOF BOOLEAN;
528 l_num_of_Invoices NUMBER;
529 l_num_of_lines NUMBER;
530 l_GTA_Invoice_num Jmf_Gta_Trx_Headers_All.Gta_Trx_Number%TYPE;
531 l_org_id NUMBER;
532 l_trx_rec JMF_GTA_TRX_UTIL.TRX_REC_TYPE;
533 l_trx_header_rec JMF_GTA_TRX_UTIL.TRX_header_rec_TYPE;
534 l_trx_line_rec JMF_GTA_TRX_UTIL.TRX_line_rec_TYPE;
535 l_trx_line_tbl JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE
536 :=JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
537 l_error_msg VARCHAR2(2000);
538 l_fix_field VARCHAR2(100);
539 l_str VARCHAR2(100);
540 l_failed BOOLEAN;
541 l_line_num NUMBER;
542 l_status Jmf_Gta_Trx_Headers_All.Status%TYPE;
543 l_new_status Jmf_Gta_Trx_Headers_All.Status%TYPE;
544 l_Customer_Name Jmf_Gta_Trx_Headers_All.Bill_To_Customer_Name%TYPE;
545 l_TP_TAX_REG_NUMBER Jmf_Gta_Trx_Headers_All.TP_TAX_REGISTRATION_NUMBER%TYPE;
546 l_Invoice_Num Jmf_Gta_Trx_Headers_All.Gt_Invoice_Number%TYPE;
547 l_Invoice_date Jmf_Gta_Trx_Headers_All.Gt_Invoice_Date%TYPE;
548 l_Amount Jmf_Gta_Trx_Headers_All.Gt_Invoice_Net_Amount%TYPE;
549 l_conc_succ BOOLEAN;
550 l_failed_XML XMLTYPE;
551 l_succ_XML XMLTYPE;
552 l_report_XML XMLTYPE;
553 l_date_format VARCHAR2(20);
554 l_dbg_level NUMBER
555 :=FND_LOG.G_Current_Runtime_Level;
556 l_proc_level NUMBER
557 :=FND_LOG.Level_Procedure;
558
559 BEGIN
560 --log for debug
561 IF( l_proc_level >= l_dbg_level )
562 THEN
563 FND_LOG.STRING(l_proc_level
564 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
565 ,'Enter procedure');
566 END IF; --( l_proc_level >= l_dbg_level )
567
568 l_date_format:=fnd_profile.VALUE('ICX_DATE_FORMAT_MASK');
569
570 l_line_seq:=1;
571
572 --read first line
573 read_GT_line( x_line_seq=>l_line_seq
574 , x_values =>l_values
575 , x_EOF =>l_EOF
576 );
577
578 IF l_EOF THEN
579 fnd_message.SET_NAME( APPLICATION => 'JMF'
580 , NAME => 'JMF_GTA_UNMATCHED_INV_DATA_FMT');
581 l_error_msg := G_import_error_prefix||fnd_message.GET
582 ||G_import_error_suffix;
583 put_line(l_error_msg);
584 l_conc_succ:=FND_CONCURRENT.SET_COMPLETION_STATUS( status => 'WARNING'
585 , message => NULL);
586 RETURN;
587 END IF;
588
589 --Get the fixed Chinese text used in export flat file which identify
590 --the flat file is the flat file exported from GT system
591 fnd_message.SET_NAME( APPLICATION => 'JMF'
592 , NAME => 'JMF_GTA_INVOICE_IMPORT');
593 fnd_message.SET_TOKEN( TOKEN => 'MIDFIX'
594 , VALUE => g_export_delimiter
595 );
596
597 l_fix_field:=fnd_message.GET;
598 l_str:=l_values(1)||g_export_delimiter||l_values(2);
599
600 --verify the file format.
601 --If <1: no fixed Chinese text, give flat file format error
602 --IF instr(l_str,l_fix_field)<1
603 IF instr(l_fix_field,l_values(1))<1 or instr(l_fix_field,l_values(2))<1
604 THEN
605 fnd_message.SET_NAME('JMF','JMF_GTA_UNMATCHED_INV_DATA_FMT');
606 l_error_msg := G_import_error_prefix||fnd_message.GET
607 ||G_import_error_suffix;
608 put_line(l_error_msg);
609 l_conc_succ:=FND_CONCURRENT.SET_COMPLETION_STATUS( status => 'WARNING'
610 , message => NULL);
611 RETURN;
612 END IF;
613
614 --After the first line, we now read the second one
615 --The line sequence num is alredy increased in read_gt_line
616 read_GT_line( x_line_seq=>l_line_seq
617 , x_values =>l_values
618 , x_EOF =>l_EOF
619 );
620
621 --it's an empty file
622 IF l_EOF
623 THEN
624 fnd_message.SET_NAME('JMF','JMF_GTA_UNMATCHED_INV_DATA_FMT');
625 l_error_msg := G_import_error_prefix||fnd_message.GET
626 ||G_import_error_suffix;
627 put_line(l_error_msg);
628 l_conc_succ:=FND_CONCURRENT.SET_COMPLETION_STATUS( status => 'WARNING'
629 , message => NULL);
630 RETURN;
631 END IF;
632
633 --get the number of invoices
634 l_num_of_invoices:=to_number(l_values(1));
635
636 --save invoices
637 FOR i IN 1..l_num_of_invoices
638 LOOP
639 --mark the point where begin to create one GT invoice
640 --if any line failed ,then roll back this invoice
641 SAVEPOINT save_header_trans;
642
643 --get and save one invoice
644 l_failed:=FALSE;
645 l_trx_header_rec:=NULL;
646
647 --we now read a invoice header
648 --The line sequence num is already increased in read_gt_line
649 Read_GT_line( x_line_seq=>l_line_seq
650 , x_values =>l_values
651 , x_EOF =>l_EOF);
652
653 IF l_EOF
654 THEN
655 EXIT;
656 END IF;
657
658 --fetch correspoding variables from flat file line
659 l_Customer_Name:= l_values(13);
660 l_TP_TAX_REG_NUMBER := l_values(14);
661 l_Invoice_Num := l_values(5);
662
663 BEGIN
664 l_Invoice_date:= to_date(l_values(7),'RRRRMMDD');
665
666 EXCEPTION
667 WHEN OTHERS THEN
668 l_Invoice_date := l_values(7);
669
670 END ;
671
672 l_Amount := l_values(10);
673 BEGIN
674 l_num_of_lines := l_values(6); --get lines number
675
676 EXCEPTION
677 WHEN OTHERS THEN
678 fnd_message.SET_NAME('JMF','JMF_GTA_UNMATCHED_INV_DATA_FMT');
679 l_error_msg := G_import_error_prefix||fnd_message.GET
680 ||G_import_error_suffix;
681 put_line(l_error_msg);
682 l_conc_succ:=FND_CONCURRENT.SET_COMPLETION_STATUS
683 ( status => 'WARNING'
684 , message => NULL);
685
686 IF(G_DEBUG_LEVEL >= l_dbg_level)
687 THEN
688 FND_LOG.string( G_DEBUG_LEVEL
689 , G_MODULE_PREFIX || l_procedure_name || '.OTHER_EXCEPTION '
690 , 'Number of lines is not an integer'||SQLCODE||':'||SQLERRM);
691 END IF;
692
693 RETURN;
694 END ;
695
696 --check data reasonability and report JMF_GTA_UNMATCHED_INV_DATA_FMT
697 --when data error means no corresponding invoice in GTA
698 l_GTA_Invoice_num:=trim(l_values(9));
699
700 --get GTA corresponding infomation: GTA_TRX_HEADER_ID, org_id and status
701 BEGIN
702
703 SELECT GTA_TRX_HEADER_ID
704 , org_id
705 , status
706 INTO l_header_id
707 , l_org_id
708 , l_status
709 FROM JMF_GTA_TRX_HEADERS_ALL
710 WHERE GTA_TRX_NUMBER=l_GTA_Invoice_num
711 AND SOURCE='AR'
712 AND latest_version_flag='Y';
713
714 --check the ORG access
715 IF MO_GLOBAL.Check_Access(l_org_id)='Y'
716 THEN
717 --Jogen Jun-12 2006, bug
718 /*IF (l_status='GENERATED' ) OR
719 ( l_status='COMPLETED' AND l_values(1)='1')*/
720
721 IF (l_status='GENERATED' AND l_values(1)='0')
722 --first import back with successful status
723 --Jogen Jun-12 2006, bug
724 THEN
725 /*IF (l_values(1)='0')-- this invoice was cancelled
726 THEN
727 --set GTA corresponding invoices status to completed;
728 l_new_status:='COMPLETED';
729 ELSE
730 --set GTA corresponding invoices status to canceled;
731 l_new_status:='CANCELLED';
732 END IF;*/
733 l_new_status:='COMPLETED';
734
735 --clear the record imported before
736 DELETE JMF_GTA_TRX_HEADERS
737 WHERE GTA_TRX_NUMBER=l_GTA_Invoice_num
738 AND SOURCE='GT';
739
740 DELETE JMF_GTA_TRX_LINES
741 WHERE GTA_TRX_HEADER_ID IN
742 ( SELECT GTA_TRX_HEADER_ID
743 FROM JMF_GTA_TRX_HEADERS
744 WHERE GTA_TRX_NUMBER=l_GTA_Invoice_num
745 AND SOURCE='GT');
746
747 --change the matched_flag to initiated status.
748 UPDATE JMF_GTA_TRX_LINES
749 SET matched_flag='N'
750 WHERE GTA_TRX_HEADER_ID=l_header_id;
751
752 --Requested by Donghai, for workbench judge whether upgrade version
753 UPDATE JMF_GTA_TRX_HEADERS
754 SET Status=l_new_status
755 , gt_invoice_date = l_Invoice_date
756 , gt_invoice_net_amount = l_values(10)
757 , gt_invoice_tax_amount = l_values(12)
758 , gt_tax_month = l_values(8)
759 , gt_invoice_number = l_values(5)
760 , gt_invoice_type = l_values(3)
761 , gt_invoice_class = l_values(4)
762 WHERE GTA_TRX_HEADER_ID=l_header_id;
763
764 --copy values from GTA corresponding invoice to GT invoice:
765 --l_trx_rec.trx_header
766
767 SELECT ra_gl_date
768 , ra_gl_period
769 , set_of_books_id
770 , bill_to_customer_id
771 , bill_to_customer_number
772 , org_id
773 , rule_header_id
774 , gta_trx_number
775 , group_number
776 , version
777 , transaction_date
778 , ra_trx_id
779 , ra_currency_code
780 , conversion_type
781 , conversion_date
782 , conversion_rate
783 , gta_batch_number
784 , generator_id
785 , ra_trx_number
786 , Fp_Tax_Registration_Number
787 , Tp_Tax_Registration_Number
788 , Legal_Entity_Id
789 INTO
790 l_trx_header_rec.ra_gl_date
791 , l_trx_header_rec.ra_gl_period
792 , l_trx_header_rec.set_of_books_id
793 , l_trx_header_rec.bill_to_customer_id
794 , l_trx_header_rec.bill_to_customer_number
795 , l_trx_header_rec.org_id
796 , l_trx_header_rec.rule_header_id
797 , l_trx_header_rec.gta_trx_number
798 , l_trx_header_rec.group_number
799 , l_trx_header_rec.version
800 , l_trx_header_rec.transaction_date
801 , l_trx_header_rec.ra_trx_id
802 , l_trx_header_rec.ra_currency_code
803 , l_trx_header_rec.conversion_type
804 , l_trx_header_rec.conversion_date
805 , l_trx_header_rec.conversion_rate
806 , l_trx_header_rec.gta_batch_number
807 , l_trx_header_rec.generator_id
808 , l_trx_header_rec.ra_trx_number
809 , l_trx_header_rec.Fp_Tax_Registration_Number
810 , l_trx_header_rec.Tp_Tax_Registration_Number
811 , l_trx_header_rec.Legal_Entity_Id
812 FROM JMF_GTA_TRX_HEADERS
813 WHERE GTA_TRX_HEADER_ID=l_header_id;
814
815 --fill the data from GT
816 BEGIN
817
818 l_trx_header_rec.gt_invoice_date := l_Invoice_date;
819 l_trx_header_rec.gt_invoice_net_amount := l_values(10);
820 l_trx_header_rec.gt_invoice_tax_amount := l_values(12);
821 l_trx_header_rec.tax_rate := l_values(11);
822 l_trx_header_rec.gt_tax_month := l_values(8);
823 l_trx_header_rec.bill_to_customer_name := l_values(13);
824 l_trx_header_rec.source := 'GT';
825 l_trx_header_rec.description := l_values(21);
826 l_trx_header_rec.customer_address_phone := l_values(15);
827 l_trx_header_rec.bank_account_name_number:= l_values(16);
828 l_trx_header_rec.gt_invoice_number := l_values(5);
829 l_trx_header_rec.tp_tax_registration_number := l_values(14);
830 l_trx_header_rec.status := l_new_status;
831 l_trx_header_rec.sales_list_flag := l_values(2);
832 l_trx_header_rec.cancel_flag := l_values(1);
833 l_trx_header_rec.gt_invoice_type := l_values(3);
834 l_trx_header_rec.gt_invoice_class := l_values(4);
835 l_trx_header_rec.issuer_name := l_values(22);
836 l_trx_header_rec.reviewer_name := l_values(23);
837 l_trx_header_rec.payee_name := l_values(24);
838 l_trx_header_rec.LATEST_VERSION_FLAG :='Y';
839 l_trx_header_rec.request_id := fnd_global.CONC_REQUEST_ID;
840 l_trx_header_rec.program_application_id
841 := fnd_global.RESP_APPL_ID;
842 l_trx_header_rec.program_id := fnd_global.CONC_PROGRAM_ID;
843 l_trx_header_rec.program_update_date:= SYSDATE;
844 l_trx_header_rec.creation_date := SYSDATE;
845 l_trx_header_rec.created_by := fnd_global.USER_ID;
846 l_trx_header_rec.last_update_date := SYSDATE;
847 l_trx_header_rec.last_updated_by := fnd_global.USER_ID;
848 l_trx_header_rec.last_update_login := fnd_global.LOGIN_ID;
849 l_trx_header_rec.invoice_type := l_values(3); --added by subba
850
851
852
853
854
855
856 SELECT jmf_gta_trx_headers_all_s.NEXTVAL
857 INTO l_trx_header_rec.gta_trx_header_id
858 FROM dual;
859
860 EXCEPTION
861 WHEN OTHERS THEN
862
863 l_failed:=TRUE;
864 fnd_message.SET_NAME('JMF','JMF_GTA_UNMATCHED_INV_DATA_FMT');
865 l_error_msg:=fnd_message.GET;
866
867 IF(G_DEBUG_LEVEL >= l_dbg_level)
868 THEN
869 FND_LOG.string( G_DEBUG_LEVEL
870 , G_MODULE_PREFIX || l_procedure_name || '.OTHER_EXCEPTION '
871 , 'Assign value from TXT to record:'||SQLCODE||':'||SQLERRM);
872 END IF;
873
874 ROLLBACK TO save_header_trans;
875 INSERT INTO JMF_GTA_IMPORT_REP_TEMP( SEQ
876 , SUCCEEDED
877 , Customer_Name
878 , Taxpayer_ID
879 , Invoice_Num
880 , Invoice_date
881 , Amount
882 , FailedReason
883 )
884 VALUES(JMF_GTA_IMPORT_REP_TEMP_s.NEXTVAL
885 , 'N'
886 , l_Customer_Name
887 , l_TP_TAX_REG_NUMBER
888 , l_Invoice_Num
889 , to_char(l_Invoice_date,l_date_format)
890 , l_Amount
891 , l_error_msg
892 );
893 END;
894
895 l_trx_line_tbl.DELETE;
896 l_line_num:=0;
897
898 --read lines from flat file and create line record
899 FOR j IN 1..l_num_of_lines
900 LOOP
901 Read_GT_line(x_line_seq=>l_line_seq
902 , x_values =>l_values
903 , x_EOF =>l_EOF);
904
905 --
906 IF NOT l_failed
907 THEN
908 BEGIN
909 --put_log('import log:10--');
910 l_trx_line_rec :=NULL;
911
912 --compare whether there's record matched in GTA
913 UPDATE jmf_gta_trx_lines_all
914 SET matched_flag='Y'
915 WHERE gta_trx_header_id=l_header_id
916 AND enabled_flag='Y'
917 AND item_description=l_values(2)
918 AND item_model=l_values(3)
919 AND item_tax_denomination=l_values(11)
920 AND tax_rate=l_values(7)
921 AND uom_name=l_values(4)
922 AND quantity=l_values(5)
923 AND unit_price=decode(l_values(10),
924 '0',l_values(9),
925 '1',to_number(l_values(9))/
926 (1+to_number(l_values(7))),
927 NULL)
928 AND amount=l_values(6)
929 AND matched_flag='N'
930 AND tax_amount=l_values(8)
931 AND ROWNUM<2;
932
933 IF SQL%ROWCOUNT>0 THEN
934 l_trx_line_rec.matched_flag:='Y';
935 ELSE
936 l_trx_line_rec.matched_flag:='N';
937 END IF;
938
939 --fill other data
940 l_trx_line_rec.amount :=l_values(6);
941 l_trx_line_rec.tax_amount :=l_values(8);
942 l_trx_line_rec.org_id :=l_org_id;
943 l_trx_line_rec.gta_trx_header_id
944 :=l_trx_header_rec.gta_trx_header_id;
945
946 SELECT jmf_gta_trx_lines_all_s.NEXTVAL
947 INTO l_trx_line_rec.gta_trx_line_id
948 FROM dual;
949
950 l_line_num:=l_line_num+1;
951 l_trx_line_rec.line_number :=l_line_num;
952 l_trx_line_rec.item_description :=l_values(2);
953 l_trx_line_rec.item_model :=l_values(3);
954 l_trx_line_rec.item_tax_denomination:=l_values(11);
955 l_trx_line_rec.tax_rate :=l_values(7);
956 l_trx_line_rec.uom_name :=l_values(4);
957 l_trx_line_rec.quantity :=l_values(5);
958 l_trx_line_rec.price_flag :=l_values(10);
959
960 SELECT decode(l_values(10),
961 '0',l_values(9),
962 NULL)
963 , decode(l_values(10),
964 '1',l_values(9),
965 NULL)
966 INTO l_trx_line_rec.unit_price
967 , l_trx_line_rec.unit_tax_price
968 FROM dual;
969
970 l_trx_line_rec.discount_flag:=l_values(1);
971 l_trx_line_rec.enabled_flag :='Y';
972 l_trx_line_rec.request_id := fnd_global.CONC_REQUEST_ID;
973 l_trx_line_rec.program_id := fnd_global.CONC_PROGRAM_ID;
974 l_trx_line_rec.creation_date:= SYSDATE;
975 l_trx_line_rec.created_by := fnd_global.USER_ID;
976 l_trx_line_rec.last_update_date := SYSDATE;
977 l_trx_line_rec.last_updated_by := fnd_global.USER_ID;
978 l_trx_line_rec.last_update_login:= fnd_global.LOGIN_ID;
979 l_trx_line_rec.program_applicaton_id
980 := fnd_global.RESP_APPL_ID;
981 l_trx_line_rec.program_update_date := SYSDATE;
982
983 l_trx_line_tbl.EXTEND;
984 l_trx_line_tbl(l_trx_line_tbl.LAST):=l_trx_line_rec;
985 EXCEPTION
986 WHEN OTHERS THEN
987 l_failed:=TRUE;
988
989 fnd_message.SET_NAME( 'JMF'
990 , 'JMF_GTA_UNMATCHED_INV_DATA_FMT');
991 l_error_msg:=fnd_message.GET;
992
993 IF(G_DEBUG_LEVEL >= l_dbg_level)
994 THEN
995 FND_LOG.string( G_DEBUG_LEVEL
996 , G_MODULE_PREFIX || l_procedure_name || '.OTHER_EXCEPTION '
997 , 'Update original record:'||SQLCODE||':'||SQLERRM);
998 END IF;
999
1000 ROLLBACK TO save_header_trans;
1001 INSERT INTO JMF_GTA_IMPORT_REP_TEMP( SEQ
1002 , SUCCEEDED
1003 , Customer_Name
1004 , Taxpayer_ID
1005 , Invoice_Num
1006 , Invoice_date
1007 , Amount
1008 , FailedReason
1009 )
1010 VALUES( JMF_GTA_IMPORT_REP_TEMP_s.NEXTVAL
1011 , 'N'
1012 , l_Customer_Name
1013 , l_TP_TAX_REG_NUMBER
1014 , l_Invoice_Num
1015 , to_char(l_Invoice_date,l_date_format)
1016 , l_Amount
1017 , l_error_msg
1018 );
1019 END;
1020 END IF; --IF NOT l_failed
1021
1022 END LOOP;--FOR j IN 1..l_num_of_lines
1023
1024 IF NOT l_failed
1025 THEN
1026 l_trx_rec.trx_header:=l_trx_header_rec;
1027 l_trx_rec.trx_lines:=l_trx_line_tbl;
1028
1029 --insert into GTA table
1030 BEGIN
1031 jmf_gta_trx_util.create_Trx(P_GTA_Trx => l_trx_rec);
1032
1033 INSERT INTO JMF_GTA_IMPORT_REP_TEMP( SEQ
1034 , SUCCEEDED
1035 , Customer_Name
1036 , Taxpayer_ID
1037 , Invoice_Num
1038 , Invoice_date
1039 , Amount
1040 , STATUS
1041 )
1042 VALUES( JMF_GTA_IMPORT_REP_TEMP_s.NEXTVAL
1043 , 'Y'
1044 , l_Customer_Name
1045 , l_TP_TAX_REG_NUMBER
1046 , l_Invoice_Num
1047 , to_char(l_Invoice_date,l_date_format)
1048 , l_Amount
1049 , l_new_status
1050 );
1051 EXCEPTION
1052 WHEN OTHERS THEN
1053 l_failed:=TRUE;
1054
1055 fnd_message.SET_NAME('JMF'
1056 ,'JMF_GTA_UNMATCHED_INV_DATA_FMT');
1057 l_error_msg:=fnd_message.GET;
1058
1059 IF(G_DEBUG_LEVEL >= l_dbg_level)
1060 THEN
1061 FND_LOG.string( G_DEBUG_LEVEL
1062 , G_MODULE_PREFIX || l_procedure_name || '.OTHER_EXCEPTION '
1063 , 'Insert into base table:'||SQLCODE||':'||SQLERRM);
1064 END IF;
1065
1066 ROLLBACK TO save_header_trans;
1067 INSERT INTO JMF_GTA_IMPORT_REP_TEMP( SEQ
1068 , SUCCEEDED
1069 , Customer_Name
1070 , Taxpayer_ID
1071 , Invoice_Num
1072 , Invoice_date
1073 , Amount
1074 , FailedReason
1075 )
1076 VALUES( JMF_GTA_IMPORT_REP_TEMP_s.NEXTVAL
1077 , 'N'
1078 , l_Customer_Name
1079 , l_TP_TAX_REG_NUMBER
1080 , l_Invoice_Num
1081 , to_char(l_Invoice_date,l_date_format)
1082 , l_Amount
1083 , l_error_msg
1084 );
1085 END ;
1086 END IF;
1087 --Jogen Jun-12 2006, bug
1088 ELSIF (l_status='COMPLETED')--has exists VAT invoice from GT
1089 THEN
1090 l_failed:=TRUE;
1091
1092 fnd_message.SET_NAME('JMF'
1093 ,'JMF_GTA_IMP_PRIEXIST_FAIL');
1094 fnd_message.SET_TOKEN( TOKEN => 'NUM'
1095 , VALUE => l_Invoice_Num
1096 );
1097
1098 l_error_msg:=fnd_message.GET;
1099
1100 INSERT INTO JMF_GTA_IMPORT_REP_TEMP( SEQ
1101 , SUCCEEDED
1102 , Customer_Name
1103 , Taxpayer_ID
1104 , Invoice_Num
1105 , Invoice_date
1106 , Amount
1107 , FailedReason
1108 )
1109 VALUES( JMF_GTA_IMPORT_REP_TEMP_s.NEXTVAL
1110 , 'N'
1111 , l_Customer_Name
1112 , l_TP_TAX_REG_NUMBER
1113 , l_Invoice_Num
1114 , to_char(l_Invoice_date,l_date_format)
1115 , l_Amount
1116 , l_error_msg
1117 );
1118
1119 FOR j IN 1..l_num_of_lines
1120 LOOP
1121 Read_GT_Line(x_line_seq=>l_line_seq
1122 , x_values =>l_values
1123 , x_EOF =>l_EOF);
1124 END LOOP;
1125
1126 ELSIF (l_status='CANCELLED')--the VAT invoice was cancelled in GTA
1127 THEN
1128 l_failed:=TRUE;
1129
1130 fnd_message.SET_NAME('JMF'
1131 ,'JMF_GTA_IMP_CANCEL');
1132 fnd_message.SET_TOKEN( TOKEN => 'NUM'
1133 , VALUE => l_Invoice_Num
1134 );
1135
1136 l_error_msg:=fnd_message.GET;
1137
1138 INSERT INTO JMF_GTA_IMPORT_REP_TEMP( SEQ
1139 , SUCCEEDED
1140 , Customer_Name
1141 , Taxpayer_ID
1142 , Invoice_Num
1143 , Invoice_date
1144 , Amount
1145 , FailedReason
1146 )
1147 VALUES( JMF_GTA_IMPORT_REP_TEMP_s.NEXTVAL
1148 , 'N'
1149 , l_Customer_Name
1150 , l_TP_TAX_REG_NUMBER
1151 , l_Invoice_Num
1152 , to_char(l_Invoice_date,l_date_format)
1153 , l_Amount
1154 , l_error_msg
1155 );
1156
1157 FOR j IN 1..l_num_of_lines
1158 LOOP
1159 Read_GT_Line(x_line_seq=>l_line_seq
1160 , x_values =>l_values
1161 , x_EOF =>l_EOF);
1162 END LOOP;
1163 --Jogen Jun-12 2006, bug
1164
1165 ELSE --status error
1166 fnd_message.SET_NAME('JMF','JMF_GTA_WRONG_INV_STATUS');
1167 l_error_msg:=fnd_message.GET;
1168
1169 INSERT INTO JMF_GTA_IMPORT_REP_TEMP( SEQ
1170 , SUCCEEDED
1171 , Customer_Name
1172 , Taxpayer_ID
1173 , Invoice_Num
1174 , Invoice_date
1175 , Amount
1176 , FailedReason
1177 )
1178 VALUES( JMF_GTA_IMPORT_REP_TEMP_s.NEXTVAL
1179 , 'N'
1180 , l_Customer_Name
1181 , l_TP_TAX_REG_NUMBER
1182 , l_Invoice_Num
1183 , to_char(l_Invoice_date,l_date_format)
1184 , l_Amount
1185 , l_error_msg
1186 );
1187
1188 FOR j IN 1..l_num_of_lines
1189 LOOP
1190 Read_GT_Line(x_line_seq=>l_line_seq
1191 , x_values =>l_values
1192 , x_EOF =>l_EOF);
1193 END LOOP;
1194 END IF;
1195 ELSE --MO_GLOBAL.Check_Access(l_org_id)<>'Y'
1196 --report JMF_GTA_MOAC_ERROR;
1197 /*fnd_message.SET_NAME('JMF','JMF_GTA_MOAC_FORBID');
1198 l_error_msg:=fnd_message.GET;
1199
1200 INSERT INTO JMF_GTA_IMPORT_REP_TEMP( SEQ
1201 , SUCCEEDED
1202 , Customer_Name
1203 , Taxpayer_ID
1204 , Invoice_Num
1205 , Invoice_date
1206 , Amount
1207 , FailedReason
1208 )
1209 VALUES( JMF_GTA_IMPORT_REP_TEMP_s.NEXTVAL
1210 , 'N'
1211 , l_Customer_Name
1212 , l_Taxpayer_ID
1213 , l_Invoice_Num
1214 , to_char(l_Invoice_date,l_date_format)
1215 , l_Amount
1216 , l_error_msg
1217 );
1218 */
1219 FOR j IN 1..l_num_of_lines
1220 LOOP
1221 Read_GT_line(x_line_seq=>l_line_seq
1222 , x_values =>l_values
1223 , x_EOF =>l_EOF);
1224 END LOOP;
1225 END IF; -- MO_GLOBAL.Check_Access(l_org_id)='Y'
1226 EXCEPTION
1227 WHEN NO_DATA_FOUND THEN
1228
1229 FOR j IN 1..l_num_of_lines
1230 LOOP
1231 Read_GT_line(x_line_seq=>l_line_seq
1232 , x_values =>l_values
1233 , x_EOF =>l_EOF);
1234 END LOOP;
1235 END;
1236
1237 END LOOP; --FOR i IN 1..l_num_of_invoices
1238
1239 --generate XML output
1240 BEGIN
1241 SELECT XMLElement("Details"
1242 , xmlagg(
1243 xmlelement(
1244 "Invoice"
1245 , xmlforest(Customer_Name AS "CustomerName"
1246 ,Taxpayer_ID AS "TaxpayerID"
1247 ,Invoice_Num AS "InvoiceNum"
1248 --Jogen 20-Sep-2006 bug5521629
1249 --Format date to XSD Date format
1250 ,JMF_GTA_TRX_UTIL.To_Xsd_Date_String(Invoice_date)AS "InvoiceDate"
1251 --,Invoice_date AS "InvoiceDate"
1252 --Jogen 20-Sep-2006 bug5521629
1253 ,Amount AS "Amount"
1254 ,Status AS "Status"
1255 )
1256 )
1257 )
1258 )
1259 INTO l_succ_XML
1260 FROM JMF_GTA_IMPORT_REP_TEMP
1261 WHERE SUCCEEDED='Y';
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 NULL;
1265 END;
1266
1267 BEGIN
1268 SELECT XMLElement("FailedInvoices"
1269 , xmlagg(
1270 xmlelement(
1271 "Invoice"
1272 ,xmlforest(Customer_Name AS "CustomerName"
1273 ,Taxpayer_ID AS "TaxpayerID"
1274 ,Invoice_Num AS "InvoiceNum"
1275 --Jogen 20-Sep-2006 bug5521629
1276 --Format date to XSD Date format
1277 ,JMF_GTA_TRX_UTIL.To_Xsd_Date_String(Invoice_date)AS "InvoiceDate"
1278 --,Invoice_date AS "InvoiceDate"
1279 --Jogen 20-Sep-2006 bug5521629
1280 ,Amount AS "Amount"
1281 ,FailedReason AS "Reason"
1282 )
1283 )
1284 )
1285 )
1286 INTO l_failed_XML
1287 FROM JMF_GTA_IMPORT_REP_TEMP
1288 WHERE SUCCEEDED='N';
1289 EXCEPTION
1290 WHEN OTHERS THEN
1291 NULL;
1292 END;
1293
1294 SELECT XMLElement("ImportReport"
1295 , XMLElement("RepDate",to_char( SYSDATE
1296 , l_date_format
1297 )
1298 )
1299 , XMLElement("ReportFailed",'N')
1300 , XMLElement("FailedWithParameters",'N')
1301 , l_succ_XML
1302 , l_failed_XML
1303 )
1304 INTO l_report_XML
1305 FROM dual;
1306
1307 JMF_GTA_TRX_UTIL.output_conc(l_report_XML.Getclobval);
1308
1309 clear_imp_temp_table;
1310
1311 --log for debug
1312 IF( l_proc_level >= l_dbg_level )
1313 THEN
1314 FND_LOG.STRING(l_proc_level
1315 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
1316 ,'Exit procedure');
1317 END IF; --( l_proc_level >= l_dbg_level )
1318 EXCEPTION
1319 WHEN OTHERS THEN
1320 IF(l_proc_level >= l_dbg_level)
1321 THEN
1322 FND_LOG.string( l_proc_level
1323 , G_MODULE_PREFIX || l_procedure_name||'.OTHER_EXCEPTION'
1324 , SQLCODE||SQLERRM);
1325 END IF;
1326 clear_imp_temp_table;
1327 RAISE;
1328
1329 END Import_Invoices;
1330
1331 --==========================================================================
1332 -- PROCEDURE NAME:
1333 --
1334 -- Export_Invoice Private
1335 --
1336 -- DESCRIPTION:
1337 --
1338 -- This procedure export one VAT invoice from GTA to flat file
1339 --
1340 -- PARAMETERS:
1341 -- In: p_invoice_header_id The transaction id which need export
1342 -- to flat file
1343 -- p_batch_number The export batch number
1344 --
1345 -- Out: x_output export lines which need print to flat file
1346 -- x_success succeed or failed when export this single
1347 -- invoice
1348 --
1349 -- DESIGN REFERENCES:
1350 -- GTA-Txt-Interface-TD.doc
1351 --
1352 -- CHANGE HISTORY:
1353 -- 05/12/05 Jogen Hu Created
1354 -- 10/12/08 Lv Xiao Modified for bug#7626503
1355 --===========================================================================
1356 PROCEDURE Export_Invoice
1357 ( p_invoice_header_id IN NUMBER
1358 , p_batch_number IN VARCHAR2
1359 , x_output OUT NOCOPY t_invoice_export_output
1360 , x_success OUT NOCOPY PLS_INTEGER
1361 , p_dup_record_tbl IN dup_record_tbl
1362 )
1363 IS
1364 l_procedure_name VARCHAR2(20):='Export_invoice';
1365 l_invoice JMF_GTA_TRX_UTIL.TRX_header_rec_TYPE;
1366 l_TRX_Line_REC JMF_GTA_TRX_UTIL.TRX_LINE_REC_TYPE;
1367 l_lines_num NUMBER:=0;
1368 l_header_output VARCHAR2(1000);
1369
1370 ln_ta_trx_id NUMBER;
1371 lv_desc VARCHAR2(100);
1372 ln_trx_org_id NUMBER;
1373 ln_gta_trx_number VARCHAR2(100);
1374
1375 i INTEGER;
1376 l_index INTEGER;
1377
1378 CURSOR c_trx_lines(p_header_id IN NUMBER) IS
1379 SELECT *
1380 FROM JMF_GTA_trx_lines
1381 WHERE GTA_trx_header_id = p_header_id
1382 AND Enabled_Flag='Y';
1383
1384 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1385 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1386 BEGIN
1387
1388 --log for debug
1389 IF( l_proc_level >= l_dbg_level )
1390 THEN
1391 FND_LOG.STRING(l_proc_level
1392 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
1393 ,'Enter procedure');
1394 END IF; --( l_proc_level >= l_dbg_level )
1395
1396 --reserve one line for header output
1397 x_output:=t_invoice_export_output(NULL,NULL);
1398
1399 --Get the comments of a single invoice
1400 fnd_message.SET_NAME( APPLICATION => 'JMF'
1401 , NAME => 'JMF_GTA_INVOICE_EXPORT_COMMT'
1402 );
1403 fnd_message.SET_TOKEN( TOKEN => 'PREFIX'
1404 , VALUE => g_comment_delimiter
1405 );
1406
1407 --put to the temporary output
1408 x_output(1):=fnd_message.get;
1409
1410 jmf_gta_trx_headers_all_pkg.query_row( p_header_id => p_invoice_header_id
1411 , x_trx_header_rec => l_invoice
1412 );
1413
1414 x_success:=check_header(l_invoice);
1415
1416
1417 --add by Lv Xiao for bug#7626503 on 12-Dec-2008, begin
1418 -------------------------------------------------------------------------
1419 /*
1420 check duplicated description.
1421 For credit memo transaction reference to Special VAT invoice and
1422 Recycle VAT invoice, description should be unique.
1423 */
1424 IF gv_prefix_missing_flag = 'TRUE'
1425 THEN
1426 x_success :=G_EXPORT_MISSING_PREFIX_ERROR;
1427 END IF;
1428
1429 IF gv_desc_duplicat_flag = 'TRUE'
1430 THEN
1431 x_success := G_EXPORT_CRMEMO_DUP_ERROR;
1432 END IF;
1433
1434 -------------------------------------------------------------------------
1435 --add by Lv Xiao for bug#7626503 on 12-Dec-2008, end
1436
1437 --put line record to flat file temp
1438 FOR l_Invoice_Line IN c_trx_lines(l_invoice.gta_Trx_header_id)
1439 LOOP
1440
1441 IF l_Invoice_Line.ENABLED_FLAG='Y'
1442 THEN
1443 l_lines_num:=l_lines_num+1;
1444
1445 l_TRX_Line_REC.item_description :=l_Invoice_Line.item_description ;
1446 l_TRX_Line_REC.uom_name :=l_Invoice_Line.uom_name ;
1447 l_TRX_Line_REC.item_model :=l_Invoice_Line.item_model ;
1448 l_TRX_Line_REC.quantity :=l_Invoice_Line.quantity ;
1449 l_TRX_Line_REC.amount :=l_Invoice_Line.amount ;
1450 l_TRX_Line_REC.tax_rate :=l_Invoice_Line.tax_rate ;
1451 l_TRX_Line_REC.item_tax_denomination
1452 :=l_Invoice_Line.item_tax_denomination;
1453
1454 IF NOT check_line_Length(l_TRX_Line_REC)
1455 THEN
1456 x_success:=G_EXPORT_EXCEED_ERROR;
1457 END IF;
1458
1459 --put line record to flat file temp
1460 x_output.EXTEND;
1461
1462 x_output(x_output.LAST):=
1463 substr( rpad(l_Invoice_Line.ITEM_DESCRIPTION
1464 ,length(l_Invoice_Line.ITEM_DESCRIPTION)+1)||g_export_delimiter
1465 ||l_Invoice_Line.UOM_NAME ||g_export_delimiter
1466 ||l_Invoice_Line.ITEM_MODEL ||g_export_delimiter
1467 ||l_Invoice_Line.QUANTITY ||g_export_delimiter
1468 ||l_Invoice_Line.AMOUNT ||g_export_delimiter
1469 ||l_Invoice_Line.Tax_rate ||g_export_delimiter
1470 ||l_Invoice_Line.ITEM_TAX_DENOMINATION
1471 ,1, 1000);
1472
1473 END IF;
1474
1475 END LOOP;
1476
1477 --put header record to flat file temp
1478 l_header_output:= substr(
1479 l_invoice.GTA_TRX_NUMBER ||g_export_delimiter
1480 ||l_lines_num ||g_export_delimiter
1481 ||l_invoice.BILL_TO_CUSTOMER_NAME ||g_export_delimiter
1482 ||l_invoice.tp_tax_registration_number||g_export_delimiter
1483 ||l_invoice.CUSTOMER_ADDRESS_PHONE ||g_export_delimiter
1484 ||l_invoice.BANK_ACCOUNT_NAME_NUMBER||g_export_delimiter
1485 ||l_invoice.DESCRIPTION ||g_export_delimiter
1486 ||l_invoice.REVIEWER_NAME ||g_export_delimiter
1487 ||l_invoice.PAYEE_NAME ||g_export_delimiter
1488 ||g_export_delimiter
1489 ||to_char(l_invoice.TRANSACTION_DATE,'RRRRMMDD')
1490 ,1,1000);
1491
1492 IF x_success=G_EXPORT_SUCC
1493 THEN
1494 --put invoice header to temporary record of flat file
1495 x_output(2):=l_header_output;
1496
1497 --change GTA status
1498 --fill request_id etc. to P_INVOICE;
1499 UPDATE jmf_gta_trx_headers
1500 SET status='GENERATED'
1501 , gta_batch_number =p_batch_number
1502 , export_request_id =fnd_global.CONC_REQUEST_ID
1503 , REQUEST_ID =fnd_global.CONC_REQUEST_ID
1504 , PROGRAM_APPLICATION_ID=fnd_global.RESP_APPL_ID
1505 , PROGRAM_ID =fnd_global.CONC_PROGRAM_ID
1506 , PROGRAM_UPDATE_DATE =SYSDATE
1507 , LAST_UPDATE_DATE =SYSDATE
1508 , LAST_UPDATED_BY =fnd_global.USER_ID
1509 , LAST_UPDATE_LOGIN =fnd_global.LOGIN_ID
1510 WHERE GTA_TRX_HEADER_ID=l_invoice.gta_Trx_header_id;
1511
1512 ELSE -- x_success <> G_EXPORT_SUCC
1513 x_output(2):=g_comment_delimiter||l_header_output;
1514
1515 i:=x_output.NEXT(2);
1516 IF i IS NOT NULL THEN
1517 i:=x_output.NEXT(i);
1518 --FOR i IN (2)..x_output.COUNT
1519 WHILE i IS NOT NULL
1520 LOOP
1521 x_output(i):=g_comment_delimiter||x_output(i);
1522 i:=x_output.NEXT(i);
1523 END LOOP;
1524 END IF ;
1525
1526 UPDATE jmf_gta_trx_headers
1527 SET export_request_id =fnd_global.CONC_REQUEST_ID
1528 , REQUEST_ID =fnd_global.CONC_REQUEST_ID
1529 , PROGRAM_APPLICATION_ID=fnd_global.RESP_APPL_ID
1530 , PROGRAM_ID =fnd_global.CONC_PROGRAM_ID
1531 , PROGRAM_UPDATE_DATE =SYSDATE
1532 , LAST_UPDATE_DATE =SYSDATE
1533 , LAST_UPDATED_BY =fnd_global.USER_ID
1534 , LAST_UPDATE_LOGIN =fnd_global.LOGIN_ID
1535 WHERE GTA_TRX_HEADER_ID=l_invoice.gta_Trx_header_id;
1536
1537 END IF;-- x_success = G_EXPORT_SUCC
1538
1539 UPDATE jmf_gta_trx_lines
1540 SET REQUEST_ID =fnd_global.CONC_REQUEST_ID
1541 , program_application_id =fnd_global.RESP_APPL_ID
1542 , PROGRAM_ID =fnd_global.CONC_PROGRAM_ID
1543 , PROGRAM_UPDATE_DATE =SYSDATE
1544 , LAST_UPDATE_DATE =SYSDATE
1545 , LAST_UPDATED_BY =fnd_global.USER_ID
1546 , LAST_UPDATE_LOGIN =fnd_global.LOGIN_ID
1547 WHERE GTA_TRX_HEADER_ID=l_invoice.gta_Trx_header_id
1548 AND ENABLED_FLAG='Y';
1549
1550 --log for debug
1551 IF( l_proc_level >= l_dbg_level )
1552 THEN
1553 FND_LOG.STRING(l_proc_level
1554 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
1555 ,'Exit procedure');
1556 END IF; --( l_proc_level >= l_dbg_level )
1557
1558 EXCEPTION
1559
1560 WHEN OTHERS THEN
1561 IF(l_proc_level >= l_dbg_level)
1562 THEN
1563 FND_LOG.string( l_proc_level
1564 , G_MODULE_PREFIX || l_procedure_name || '.OTHER_EXCEPTION'
1565 , SQLCODE||SQLERRM);
1566 END IF;
1567 RAISE;
1568
1569 END Export_Invoice;
1570
1571
1572 --==========================================================================
1573 -- PROCEDURE NAME:
1574 --
1575 -- Export_Invoices Private
1576 --
1577 -- DESCRIPTION:
1578 --
1579 -- This procedure export VAT invoices from GTA to flat file
1580 --
1581 -- PARAMETERS:
1582 -- In: p_gta_trx_line CURSOR which get the invoices header_id for export
1583 -- p_batch_number Export batch number
1584 --
1585 -- Out:
1586 --
1587 -- DESIGN REFERENCES:
1588 -- GTA-Txt-Interface-TD.doc
1589 --
1590 -- CHANGE HISTORY:
1591 -- 05/12/05 Jogen Hu Created
1592 -- 09/28/05 Jogen Hu add functionality to put out the first party
1593 -- tax registration number
1594 -- 11/16/05 Jogen Hu updated, the bank account mask profile change
1595 -- to CE_MASK_INTERNAL_BANK_ACCT_NUM, and the value
1596 -- change to NO MASK
1597 -- 09/12/08 Lv Xiao Modified for bug#7626503
1598 -- 12/25/08 Lv Xiao Modified for bug#7644876
1599 -- Enlarge the string buffer of parameters:
1600 -- lv_crmemo_prefix_msg VARCHAR2(240);
1601 -- lv_trx_crmemo_prefix_msg VARCHAR2(1000);
1602 -- lv_trx_crmemo_notification_num VARCHAR2(1000);
1603 --===========================================================================
1604 PROCEDURE Export_Invoices
1605 ( P_cursor IN c_trx_header_id_type
1606 , p_batch_number IN VARCHAR2
1607 , p_generator_id IN NUMBER
1608 , p_org_id IN NUMBER
1609 , p_draft_dup_cur IN crmemo_dup_cur_TYPE DEFAULT NULL
1610 )
1611 IS
1612 l_procedure_name VARCHAR2(30):='export_Invoices';
1613 l_header_rec_cur Jmf_Gta_Trx_Headers_all%ROWTYPE;
1614 l_succ_output t_invoice_export_output:=t_invoice_export_output();
1615 l_exceed_output t_invoice_export_output:=t_invoice_export_output();
1616 l_taxpayid_err_out t_invoice_export_output:=t_invoice_export_output();
1617 l_creddit_memo_err t_invoice_export_output:=t_invoice_export_output();
1618 l_current_output t_invoice_export_output:=t_invoice_export_output();
1619
1620 l_trx_export_success PLS_INTEGER;
1621 l_error_msg VARCHAR2(5000);
1622 l_conc_succ BOOLEAN;
1623 l_gta_trx_header_id jmf_gta_trx_headers_all.gta_trx_header_id%TYPE;
1624 i INTEGER;
1625 l_str VARCHAR2(1000); --Jun-22, 2006, jogen bug 5335265
1626 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1627 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1628 l_first_Inv BOOLEAN :=TRUE;
1629
1630 --add by Lv Xiao on 9-Dec-2008 for bug#7626503, begin
1631 --------------------------------------------------------------------------
1632 l_missing_prefix_output t_invoice_export_output:=t_invoice_export_output();
1633 l_crmemo_dup_err_output t_invoice_export_output:=t_invoice_export_output();
1634
1635 ln_ta_trx_id NUMBER;
1636 lv_desc VARCHAR2(200);
1637 ln_trx_org_id NUMBER;
1638 ln_gta_trx_number VARCHAR2(100);
1639 l_index NUMBER;
1640 ln_dup_org_name1 VARCHAR2(100);
1641 ln_dup_org_name2 VARCHAR2(100);
1642
1643 lv_trx_type VARCHAR2(20);
1644 ln_pos NUMBER:=0;
1645 --modified by Lv Xiao for bug#7644876 on 25-Dec-2008, begin
1646 --------------------------------------------------------------------------
1647 /*lv_crmemo_prefix_msg VARCHAR2(50);
1648 lv_trx_crmemo_prefix_msg VARCHAR2(50);
1649 lv_trx_crmemo_notification_num VARCHAR2(50);*/
1650 lv_crmemo_prefix_msg VARCHAR2(240);
1651 lv_trx_crmemo_prefix_msg VARCHAR2(1000);
1652 lv_trx_crmemo_notification_num VARCHAR2(1000);
1653 --------------------------------------------------------------------------
1654 --modified by Lv Xiao for bug#7644876 on 25-Dec-2008, end
1655 lb_num_flag BOOLEAN:=TRUE;
1656
1657 ln_notification_num NUMBER:=0;
1658
1659 p_GTA_trx_header JMF_GTA_TRX_UTIL.TRX_header_rec_TYPE;
1660
1661 lt_dup_record_tbl dup_record_tbl := dup_record_tbl();
1662 --------------------------------------------------------------------------
1663 --add by Lv Xiao on 9-Dec-2008 for bug#7626503, end
1664
1665
1666 BEGIN
1667
1668 --log for debug
1669 IF( l_proc_level >= l_dbg_level )
1670 THEN
1671 FND_LOG.STRING(l_proc_level
1672 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
1673 ,'Enter procedure');
1674 END IF; --( l_proc_level >= l_dbg_level )
1675
1676 put_log(fnd_profile.VALUE('CE_MASK_INTERNAL_BANK_ACCT_NUM'));
1677
1678 IF fnd_profile.VALUE('CE_MASK_INTERNAL_BANK_ACCT_NUM') IS NULL
1679 OR fnd_profile.VALUE('CE_MASK_INTERNAL_BANK_ACCT_NUM') <> 'NO MASK'
1680 THEN
1681 --report JMF_GTA_BANKACCOUNT_MASKING
1682 fnd_message.SET_NAME('JMF','JMF_GTA_BANKACCOUNT_MASKING');
1683 l_error_msg:=fnd_message.GET;
1684 put_line(l_error_msg);
1685 --set concurrent status to warning
1686 l_conc_succ:=FND_CONCURRENT.SET_COMPLETION_STATUS( status => 'WARNING'
1687 , message => NULL);
1688 RETURN;
1689 END IF;
1690
1691 --add by Lv Xiao for bug#7626503 on 12-Dec-2008, begin
1692 ----------------------------------------------------------
1693 /* fetch all duplicated descriptions from curso p_draft_dup_cur
1694 to table lt_dup_record_tbl.
1695 */
1696
1697 IF p_draft_dup_cur IS NOT NULL
1698 THEN
1699 LOOP
1700 FETCH p_draft_dup_cur
1701 INTO ln_ta_trx_id
1702 , lv_desc
1703 , ln_trx_org_id
1704 , ln_gta_trx_number;
1705 EXIT WHEN p_draft_dup_cur%NOTFOUND;
1706 lt_dup_record_tbl.EXTEND;
1707 lt_dup_record_tbl(lt_dup_record_tbl.LAST).ra_trx_id := ln_ta_trx_id;
1708 lt_dup_record_tbl(lt_dup_record_tbl.LAST).description := lv_desc;
1709 lt_dup_record_tbl(lt_dup_record_tbl.LAST).org_id := ln_trx_org_id;
1710 lt_dup_record_tbl(lt_dup_record_tbl.LAST).gta_trx_number := ln_gta_trx_number;
1711
1712 END LOOP;
1713 END IF ;
1714 ----------------------------------------------------------
1715 --add by Lv Xiao for bug#7626503 on 12-Dec-2008, end
1716
1717 LOOP
1718 FETCH P_cursor INTO l_header_rec_cur;
1719 EXIT WHEN P_cursor%NOTFOUND;
1720 /* IF l_first_Inv
1721 THEN
1722 l_first_Inv:=FALSE;
1723 fnd_message.SET_NAME('JMF','JMF_GTA_FP_TAX_REG_NUMBER');
1724 fnd_message.SET_TOKEN( TOKEN => 'NUMBER'
1725 , VALUE => l_header_rec_cur.fp_tax_registration_number
1726 );
1727 put_line(g_comment_delimiter||fnd_message.GET);
1728
1729 END IF;*/
1730
1731 l_gta_trx_header_id:=l_header_rec_cur.gta_trx_header_id;
1732
1733 --add by Lv Xiao for bug#7626503 on 11-Dec-2008, begin
1734 ----------------------------------------------------------------
1735
1736 /*
1737 * following code is to the format of cr memo description prefix, and the fixed
1738 * format is :
1739 * 'Notification Number for Issued VAT Credit memo+16-digit Notification number'
1740 * if check fails, message 'JMF_GTA_CRMEMO_MISSING_PREFIX' is wroten to output
1741 * file.
1742 */
1743 jmf_gta_trx_headers_all_pkg.query_row( p_header_id => l_gta_trx_header_id
1744 , x_trx_header_rec => p_GTA_trx_header
1745 );
1746
1747 SELECT DISTINCT RCTT.type
1748 INTO lv_trx_type
1749 FROM JMF_GTA_TRX_HEADERS_ALL JGTH
1750 , RA_CUST_TRX_TYPES_ALL RCTT
1751 , RA_CUSTOMER_TRX_ALL RCT
1752 WHERE RCTT.cust_trx_type_id = RCT.cust_trx_type_id
1753 AND JGTH.source = 'AR'
1754 AND RCTT.org_id = p_GTA_trx_header.org_id
1755 AND RCT.customer_trx_id = p_GTA_trx_header.ra_trx_id;
1756
1757 /* only Special and Recycle VAT invoices with transaction class
1758 * 'Credit Memo' will be check their Prefix format, and if there
1759 * duplicated descriptions exist.
1760 */
1761
1762 IF ( (p_GTA_trx_header.invoice_type <> '2') AND
1763 ( lv_trx_type = 'CM'))
1764 THEN
1765 fnd_message.SET_NAME('JMF','JMF_GTA_CRMEMO_PREFIX');
1766 lv_crmemo_prefix_msg := fnd_message.GET;
1767 ln_pos := length(lv_crmemo_prefix_msg);
1768
1769
1770 lv_trx_crmemo_prefix_msg := substr(p_GTA_trx_header.description
1771 , 0
1772 , ln_pos);
1773
1774 lv_trx_crmemo_notification_num := substr(p_GTA_trx_header.description
1775 , ln_pos+1
1776 , length(p_GTA_trx_header.description));
1777
1778
1779
1780 IF length(lv_trx_crmemo_notification_num) <> 16 THEN
1781 lb_num_flag := FALSE;
1782 END IF; --length(lv_trx_crmemo_notification_num) <> 16
1783 BEGIN
1784 SELECT to_number(lv_trx_crmemo_notification_num)
1785 INTO ln_notification_num
1786 FROM dual;
1787 EXCEPTION
1788 WHEN OTHERS THEN
1789 lb_num_flag := FALSE;
1790 END;
1791
1792 IF (lv_trx_crmemo_prefix_msg <> lv_crmemo_prefix_msg
1793 OR lb_num_flag = FALSE)
1794 THEN
1795 gv_prefix_missing_flag := 'TRUE';
1796 fnd_message.SET_NAME('JMF','JMF_GTA_CRMEMO_MISSING_PREFIX');
1797 l_missing_prefix_output.EXTEND;
1798 l_missing_prefix_output(l_missing_prefix_output.LAST):= fnd_message.GET;
1799 END IF; --lv_trx_crmemo_prefix_msg <> lv_crmemo_prefix_msg
1800 -- END IF;
1801
1802 /*
1803 * following code is to check duplicated description, and write
1804 * output message to l_crmemo_dup_err_output.
1805 */
1806
1807 IF lt_dup_record_tbl.COUNT > 0
1808 THEN
1809
1810 IF gv_dup_title_flag = 'FALSE'
1811 THEN
1812 l_crmemo_dup_err_output.EXTEND;
1813 fnd_message.SET_NAME('JMF','JMF_GTA_CRMEMO_DUP_TITLE');
1814 l_crmemo_dup_err_output(l_crmemo_dup_err_output.LAST):= fnd_message.GET;
1815
1816 gv_dup_title_flag := 'TRUE';
1817 END IF;
1818
1819 l_index := lt_dup_record_tbl.FIRST;
1820
1821 WHILE l_index IS NOT NULL
1822 LOOP
1823 ln_ta_trx_id:=lt_dup_record_tbl(l_index).ra_trx_id;
1824 lv_desc:=lt_dup_record_tbl(l_index).description;
1825 ln_trx_org_id:=lt_dup_record_tbl(l_index).org_id;
1826 ln_gta_trx_number := lt_dup_record_tbl(l_index).gta_trx_number;
1827
1828
1829 /* condition is true while
1830 * 1) description generated this time is duplicated with 'GENERATED' & 'COMPLETED'
1831 * transactions
1832 * 2) there are more than one duplicated descriptions generated this time
1833 *
1834 */
1835
1836 IF ( p_GTA_trx_header.gta_trx_number <> ln_gta_trx_number AND
1837 p_GTA_trx_header.description = lv_desc)
1838 THEN
1839
1840 IF ( gv_desc_duplicat_flag = 'FALSE' )
1841 THEN
1842 gv_desc_duplicat_flag := 'TRUE';
1843 END IF;
1844
1845 -- modified by Lv Xiao for bug#7644803 on 18-Dec-2008, begin
1846 -------------------------------------------------------------------
1847 /*
1848 * the message format is :
1849 * The description of credit memo &INVOICE_NUMBER1 in &ORG_NAME1
1850 * duplicates with credit memo &INVOICE_NUMBER2 in &ORG_NAME2
1851 */
1852
1853 SELECT hr.name
1854 INTO ln_dup_org_name1
1855 FROM hr_operating_units hr
1856 WHERE hr.organization_id = p_GTA_trx_header.org_id;
1857 --AND MO_GLOBAL.Check_Access(hr.organization_id) = 'Y'
1858
1859 fnd_message.SET_NAME('JMF','JMF_GTA_CRMEMO_DUP');
1860 fnd_message.SET_TOKEN( TOKEN => 'INVOICE_NUMBER1'
1861 , VALUE => p_GTA_trx_header.gta_trx_number
1862 );
1863 fnd_message.SET_TOKEN( TOKEN => 'ORG_NAME1'
1864 , VALUE => ln_dup_org_name1
1865 );
1866
1867 SELECT hr.name
1868 INTO ln_dup_org_name2
1869 FROM hr_operating_units hr
1870 WHERE hr.organization_id = ln_trx_org_id;
1871 --AND MO_GLOBAL.Check_Access(hr.organization_id) = 'Y'
1872
1873 --fnd_message.SET_NAME('JMF','JMF_GTA_CRMEMO_DUP');
1874 fnd_message.SET_TOKEN( TOKEN => 'INVOICE_NUMBER2'
1875 , VALUE => ln_gta_trx_number
1876 );
1877 fnd_message.SET_TOKEN( TOKEN => 'ORG_NAME2'
1878 , VALUE => ln_dup_org_name2
1879 );
1880
1881 l_crmemo_dup_err_output.EXTEND;
1882 l_crmemo_dup_err_output(l_crmemo_dup_err_output.LAST):= fnd_message.GET;
1883
1884 -------------------------------------------------------------------
1885 -- modified by Lv Xiao for bug#7644803 on 18-Dec-2008, end
1886
1887 END IF; --( p_GTA_trx_header.description = lv_desc)
1888
1889 l_index := lt_dup_record_tbl.NEXT(l_index);
1890
1891 END LOOP;
1892 END IF; --lt_dup_record_tbl.COUNT > 0
1893 END IF; --( (p_GTA_trx_header.invoice_type <> '2') AND
1894 --( lv_trx_type = 'CM'))
1895
1896
1897 ----------------------------------------------------------------
1898 --add by Lv Xiao for bug#7626503 on 11-Dec-2008, end
1899
1900 Export_invoice( p_invoice_header_id =>l_gta_trx_header_id
1901 , p_batch_number =>p_batch_number
1902 , x_output =>l_current_output
1903 , x_success =>l_trx_export_success
1904 , p_dup_record_tbl =>lt_dup_record_tbl);
1905
1906 --insert into proper sections
1907 CASE l_trx_export_success --succeeded section
1908 WHEN G_EXPORT_SUCC THEN
1909 i:=l_current_output.FIRST;
1910 WHILE i IS NOT NULL
1911 LOOP
1912 l_succ_output.EXTEND;
1913 l_succ_output(l_succ_output.LAST):=l_current_output(i);
1914 i:=l_current_output.NEXT(i);
1915 END LOOP;
1916
1917 WHEN G_EXPORT_EXCEED_ERROR THEN --exceeded error section
1918 i:=l_current_output.FIRST;
1919 WHILE i IS NOT NULL
1920 LOOP
1921 l_exceed_output.EXTEND;
1922 l_exceed_output(l_exceed_output.LAST):=l_current_output(i);
1923 i:=l_current_output.NEXT(i);
1924 END LOOP;
1925
1926 WHEN G_EXPORT_TAXPAYERID_ERROR THEN --TAXPAYERID exceeded error section
1927 i:=l_current_output.FIRST;
1928 WHILE i IS NOT NULL
1929 LOOP
1930 l_taxpayid_err_out.EXTEND;
1931 l_taxpayid_err_out(l_taxpayid_err_out.LAST)
1932 :=l_current_output(i);
1933 i:=l_current_output.NEXT(i);
1934 END LOOP;
1935
1936 WHEN G_EXPORT_CRMEMO_MISSING_ERROR THEN --CRMEMO_MISSING error section
1937 i:=l_current_output.FIRST;
1938 WHILE i IS NOT NULL
1939 LOOP
1940 l_creddit_memo_err.EXTEND;
1941 l_creddit_memo_err(l_creddit_memo_err.LAST)
1942 :=l_current_output(i);
1943 i:=l_current_output.NEXT(i);
1944 END LOOP;
1945
1946 --add by Lv Xiao for bug#7626503 on 9-DEC-2008, begin
1947 --TODO more logic in the future.
1948 --------------------------------------------------------------------------
1949 WHEN G_EXPORT_MISSING_PREFIX_ERROR THEN --MISSING_PREFIX_ERROR error section
1950 NULL;
1951 WHEN G_EXPORT_CRMEMO_DUP_ERROR THEN --CRMEMO_DUP_ERROR error section
1952 NULL;
1953 --------------------------------------------------------------------------
1954 --add by Lv Xiao for bug#7626503 on 9-DEC-2008, end
1955 END CASE;
1956 END LOOP;
1957
1958 --no data found exception
1959 IF l_succ_output.COUNT=0
1960 AND l_exceed_output.COUNT=0
1961 AND l_taxpayid_err_out.COUNT=0
1962 AND l_creddit_memo_err.COUNT=0
1963 --add by Lv Xiao for bug#7626503 on 9-DEC-2008, begin
1964 ------------------------------------------------------
1965 AND l_missing_prefix_output.COUNT=0
1966 AND l_crmemo_dup_err_output.COUNT=0
1967 ------------------------------------------------------
1968 --add by Lv Xiao for bug#7626503 on 9-DEC-2008, end
1969 THEN
1970 fnd_message.SET_NAME('JMF','JMF_GTA_NO_DATA_FOUND');
1971 l_error_msg:=g_comment_delimiter||fnd_message.GET;
1972 put_line(l_error_msg||'..pls check the invoice type you selected');
1973 RETURN;
1974 END IF;
1975 --write the output to flat file
1976
1977 -- Get the export identity of export flat file
1978 fnd_message.SET_NAME( APPLICATION => 'JMF'
1979 , NAME => 'JMF_GTA_INVOICE_EXPORT'
1980 );
1981 fnd_message.SET_TOKEN( TOKEN => 'MIDFIX'
1982 , VALUE => g_export_delimiter
1983 );
1984
1985 l_str:=fnd_message.get;
1986
1987 -- Put it out
1988 put_line(l_str);
1989
1990 l_first_Inv:=FALSE;
1991 fnd_message.SET_NAME('JMF','JMF_GTA_FP_TAX_REG_NUMBER');
1992 fnd_message.SET_TOKEN( TOKEN => 'NUMBER'
1993 , VALUE => l_header_rec_cur.fp_tax_registration_number
1994 );
1995
1996 put_line(g_comment_delimiter||fnd_message.GET);
1997
1998 IF l_succ_output.COUNT > 0
1999 THEN
2000 i:=l_succ_output.FIRST;
2001 WHILE i IS NOT NULL
2002 LOOP
2003 put_line(l_succ_output(i));
2004 i:=l_succ_output.NEXT(i);
2005 END LOOP;
2006 END IF;
2007
2008
2009 IF l_exceed_output.COUNT>0
2010 THEN
2011 fnd_message.SET_NAME('JMF','JMF_GTA_EXCEED_LENGTH');
2012 l_error_msg:=g_comment_delimiter||fnd_message.GET;
2013 put_line(l_error_msg);
2014
2015 i:=l_exceed_output.FIRST;
2016 WHILE i IS NOT NULL
2017 LOOP
2018 put_line(l_exceed_output(i));
2019 i:=l_exceed_output.NEXT(i);
2020 END LOOP;
2021 END IF;
2022
2023
2024 IF l_taxpayid_err_out.COUNT>0
2025 THEN
2026 fnd_message.SET_NAME('JMF','JMF_GTA_INVALID_LENGTH');
2027 l_error_msg:=g_comment_delimiter||fnd_message.GET;
2028 put_line(l_error_msg);
2029
2030
2031 i:=l_taxpayid_err_out.FIRST;
2032 WHILE i IS NOT NULL
2033 LOOP
2034 put_line(l_taxpayid_err_out(i));
2035 i:=l_taxpayid_err_out.NEXT(i);
2036 END LOOP;
2037 END IF;
2038
2039
2040 IF l_creddit_memo_err.COUNT>0
2041 THEN
2042 fnd_message.SET_NAME('JMF','JMF_GTA_CRMEMO_MISSING_GTINV');
2043 l_error_msg:=g_comment_delimiter||fnd_message.GET;
2044 put_line(l_error_msg);
2045
2046
2047 i:=l_creddit_memo_err.FIRST;
2048 WHILE i IS NOT NULL
2049 LOOP
2050
2051 put_line(l_creddit_memo_err(i));
2052 i:=l_creddit_memo_err.NEXT(i);
2053 END LOOP;
2054 END IF;
2055 --add by Lv Xiao on 9-Dec-2008 for bug#7626503, begin
2056 -------------------------------------------------------------------
2057 /*
2058 * output the miss_prefix & duplicated_description error message.
2059 */
2060 IF l_missing_prefix_output.COUNT>0
2061 THEN
2062 i:=l_missing_prefix_output.FIRST;
2063 WHILE i IS NOT NULL
2064 LOOP
2065 put_line(g_comment_delimiter||l_missing_prefix_output(i));
2066 i:=l_missing_prefix_output.NEXT(i);
2067 END LOOP;
2068 END IF;
2069
2070 IF l_crmemo_dup_err_output.COUNT>0
2071 THEN
2072 i:=l_crmemo_dup_err_output.FIRST;
2073 WHILE i IS NOT NULL
2074 LOOP
2075 put_line(g_comment_delimiter||l_crmemo_dup_err_output(i));
2076 i:=l_crmemo_dup_err_output.NEXT(i);
2077 END LOOP;
2078 END IF;
2079 -------------------------------------------------------------------
2080 --add by Lv Xiao on 9-Dec-2008 for bug#7626503, end
2081
2082 --log for debug
2083 IF( l_proc_level >= l_dbg_level )
2084 THEN
2085 FND_LOG.STRING(l_proc_level
2086 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
2087 ,'Exit procedure');
2088 END IF; --( l_proc_level >= l_dbg_level )
2089
2090 EXCEPTION
2091
2092 WHEN OTHERS THEN
2093 IF(l_proc_level >= l_dbg_level)
2094 THEN
2095 FND_LOG.string( l_proc_level
2096 , G_MODULE_PREFIX || l_procedure_name ||'.OTHER_EXCEPTION '
2097 , SQLCODE||SQLERRM);
2098 END IF;
2099 RAISE;
2100
2101 END Export_Invoices;
2102
2103 --==========================================================================
2104 -- PROCEDURE NAME:
2105 --
2106 -- Check_Batch_Number Private
2107 --
2108 -- DESCRIPTION:
2109 --
2110 -- This procedure check whether the batch_number is available
2111 --
2112 -- PARAMETERS:
2113 -- In: p_org_id Identifier of operating unit
2114 -- p_invoke_point indicate where invoice export
2115 -- program :workbench/concurrent
2116 --
2117 -- In Out: x_batch_number The batch_number which will
2118 -- write into GTA table
2119 -- Out: x_succ whether the procedure run normally
2120 --
2121 -- DESIGN REFERENCES:
2122 -- GTA-Txt-Interface-TD.doc
2123 --
2124 -- CHANGE HISTORY:
2125 -- 05/12/05 Jogen Hu Created
2126 --===========================================================================
2127 PROCEDURE Check_Batch_Number
2128 ( p_org_id IN NUMBER
2129 , p_invoke_point IN VARCHAR2
2130 , x_batch_number IN OUT NOCOPY VARCHAR2
2131 , x_succ OUT NOCOPY BOOLEAN
2132 )
2133 IS
2134 l_procedure_name VARCHAR2(30):='check_batch_number';
2135 l_batch_numbering_flag VARCHAR2(1);
2136 l_error_msg VARCHAR2(1000);
2137 l_rows_same_batch NUMBER;
2138 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
2139 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
2140
2141 BEGIN
2142 --log for debug
2143 IF( l_proc_level >= l_dbg_level )
2144 THEN
2145 FND_LOG.STRING(l_proc_level
2146 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
2147 ,'Enter procedure');
2148 END IF; --( l_proc_level >= l_dbg_level )
2149
2150 x_succ:=TRUE;
2151 BEGIN
2152 SELECT s.auto_batch_numbering_flag
2153 INTO l_batch_numbering_flag
2154 FROM jmf_gta_system_parameters_all s
2155 WHERE s.org_id=P_ORG_ID;
2156 EXCEPTION
2157 WHEN NO_DATA_FOUND THEN
2158 fnd_message.SET_NAME('JMF','JMF_GTA_SYS_CONFIG_MISSING');
2159 fnd_message.set_token(TOKEN => 'TAX_REGIS_NUMBER'
2160 ,VALUE => NULL
2161 );
2162
2163 l_error_msg:=g_comment_delimiter||fnd_message.GET;
2164 put_line(l_error_msg);
2165 x_succ:=FALSE;
2166 RETURN;
2167 END;
2168
2169 IF l_batch_numbering_flag='A' --automatically
2170 THEN
2171 --put_line('aalog1:2--');
2172 IF p_invoke_point='CONC' --concurrent
2173 THEN
2174 x_batch_number:=jmf_gta_batch_number_util.Next_Value
2175 ( p_org_id => P_ORG_ID);
2176 END IF;
2177
2178 ELSIF x_batch_number IS NULL --manually
2179 THEN
2180 fnd_message.SET_NAME('JMF','JMF_GTA_BATCH_NUM_MISSING');
2181 l_error_msg:=g_comment_delimiter||fnd_message.GET;
2182 put_line(l_error_msg);
2183 x_succ:=FALSE;
2184 RETURN;
2185
2186 ELSE --manually
2187 --Jogen Jun-12 2006, bug5351578
2188 --x_batch_number:=x_batch_number||to_char(SYSDATE,'YYMMDDhhMIss');
2189 x_batch_number:=x_batch_number||'-'||to_char(SYSDATE,'YYMMDDhhMIss');
2190 --Jogen Jun-12 2006, bug5351578
2191
2192 SELECT COUNT(*)
2193 INTO l_rows_same_batch
2194 FROM jmf_gta_trx_headers
2195 WHERE gta_batch_number=x_batch_number;
2196
2197 IF l_rows_same_batch > 0
2198 THEN
2199 fnd_message.SET_NAME('JMF','JMF_GTA_DUP_BATCHNUM');
2200 l_error_msg:=g_comment_delimiter||fnd_message.GET;
2201 put_line(l_error_msg);
2202 x_succ:=FALSE;
2203 RETURN;
2204 END IF;
2205
2206 END IF;--l_batch_numbering_flag='A'
2207
2208 --log for debug
2209 IF( l_proc_level >= l_dbg_level )
2210 THEN
2211 FND_LOG.STRING(l_proc_level
2212 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
2213 ,'Exit procedure');
2214 END IF; --( l_proc_level >= l_dbg_level )
2215
2216 END Check_Batch_Number;
2217
2218 --==========================================================================
2219 -- PROCEDURE NAME:
2220 --
2221 -- Export_Invoices_From_Conc Public
2222 --
2223 -- DESCRIPTION:
2224 --
2225 -- This procedure will export GTA invoices to the flat file
2226 -- Its output will be printed on concurrent output and will
2227 -- be save as flat file by users.
2228 --
2229 -- PARAMETERS:
2230 -- In: p_org_id Identifier of operation unit
2231 -- p_regeneration New batch('N') or regeneration('Y')
2232 -- p_transfer_rule_id GTA transfer rule header ID
2233 -- p_batch_number Export batch number
2234 -- p_customer_id_from_number AccountID against customer Number
2235 -- p_customer_id_from_name AccountID against customer Name
2236 -- p_cust_id_from_taxpayer AccountID against taxpayerid
2237 -- p_ar_trx_num_from AR transaction Number
2238 -- p_ar_trx_num_to AR transaction Number
2239 -- p_ar_trx_date_from AR transaction date
2240 -- p_ar_trx_date_to AR transaction date
2241 -- p_ar_trx_gl_date_from AR transaction GL date
2242 -- p_ar_trx_gl_date_to AR transaction GL date
2243 -- p_ar_trx_batch_from AR transaction batch name
2244 -- p_ar_trx_batch_to AR transaction batch name
2245 -- p_trx_class AR transaction class: INV, CM, DM
2246 -- P_Batch_ID GTA batch number
2247 -- p_invoice_type_id Invoice Type: A for All,0 for Special,2 for Common, 1 for Recycle VAT invoices --added by Subba for R12.1
2248 --
2249 -- Out:
2250 --
2251 -- DESIGN REFERENCES:
2252 -- GTA-Txt-Interface-TD.doc
2253 --
2254 -- CHANGE HISTORY:
2255 -- 05/12/05 Jogen Hu Created
2256 -- 09/28/05 Jogen Hu Add a parameter of
2257 -- fisrt party registration number
2258 -- 01/02/08 Subba Added new parameter and changed logic
2259 -- 09/12/08 Lv Xiao Modified for bug#7626503
2260 --===========================================================================
2261
2262 PROCEDURE Export_Invoices_From_Conc
2263 ( P_ORG_ID IN NUMBER
2264 , P_regeneration IN VARCHAR2
2265 , p_FP_Tax_reg_Number IN VARCHAR2
2266 , P_transfer_rule_id IN NUMBER
2267 , P_Batch_Number IN VARCHAR2
2268 , P_Customer_id_from_Number IN NUMBER
2269 , P_Customer_id_FROM_Name IN NUMBER
2270 , P_cust_id_from_Taxpayer IN NUMBER
2271 , P_AR_Trx_Num_From IN VARCHAR2
2272 , P_AR_Trx_Num_To IN VARCHAR2
2273 , P_AR_Trx_Date_From IN DATE
2274 , P_AR_Trx_Date_To IN DATE
2275 , P_AR_Trx_GL_Date_From IN DATE
2276 , P_AR_Trx_GL_Date_To IN DATE
2277 , P_AR_Trx_Batch_From IN VARCHAR2
2278 , P_AR_Trx_Batch_To IN VARCHAR2
2279 , P_Trx_Class IN VARCHAR2
2280 , P_Batch_ID IN VARCHAR2
2281 , P_Invoice_Type_ID IN VARCHAR2 --added by subba for R12.1
2282 )
2283 IS
2284 l_procedure_name VARCHAR2(30):='export_Invoices_from_Conc';
2285 l_Customer_id NUMBER;
2286 l_cur_header c_trx_header_id_type;
2287 l_AR_Trx_Num_From Ra_Customer_Trx_All.Trx_Number%TYPE
2288 :=nvl(P_AR_Trx_Num_From,' ');
2289 l_AR_Trx_Num_To Ra_Customer_Trx_All.Trx_Number%TYPE
2290 :=nvl(p_AR_Trx_Num_To,lpad('z',20,'z'));
2291
2292 l_AR_Trx_Date_From Ra_Customer_Trx_All.Trx_Date%TYPE
2293 :=nvl(P_AR_Trx_Date_From,to_date('1900/01/01','RRRR/MM/DD'));
2294 l_AR_Trx_Date_To Ra_Customer_Trx_All.Trx_Date%TYPE
2295 :=nvl(P_AR_Trx_Date_To,to_date('4000/12/12','RRRR/MM/DD'));
2296
2297 l_AR_Trx_GL_Date_From RA_CUST_TRX_LINE_GL_DIST_all.Gl_Date%TYPE
2298 :=nvl(p_AR_Trx_GL_Date_From,to_date('1900/01/01','RRRR/MM/DD'));
2299 l_AR_Trx_GL_Date_To RA_CUST_TRX_LINE_GL_DIST_all.Gl_Date%TYPE
2300 :=nvl(p_AR_Trx_GL_Date_To,to_date('4000/12/12','RRRR/MM/DD'));
2301
2302 l_AR_Trx_Batch_From ra_batches_all.name%TYPE :=nvl(p_AR_Trx_Batch_From,' ');
2303 l_AR_Trx_Batch_to ra_batches_all.name%TYPE
2304 :=nvl(p_AR_Trx_Batch_to,lpad('z',50,'z'));
2305 -------------------------------------------------------------------------------
2306 l_cur_draft_dup crmemo_dup_cur_TYPE; --get draft crmemo with duplicate description
2307 -------------------------------------------------------------------------------
2308
2309 l_error_msg VARCHAR2(1000);
2310
2311 l_batch_number Jmf_Gta_Trx_Headers_All.Gta_Batch_Number%TYPE:=p_batch_number;
2312 l_batch_number_ok BOOLEAN;
2313 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
2314 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
2315 l_invoice_type_check VARCHAR2(1); --added for invoice_type condition by Subba.
2316 BEGIN
2317
2318 --log for debug
2319 IF( l_proc_level >= l_dbg_level )
2320 THEN
2321 FND_LOG.STRING(l_proc_level
2322 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
2323 ,'Enter procedure');
2324 END IF; --( l_proc_level >= l_dbg_level )
2325
2326 IF P_regeneration='N'
2327 THEN
2328 check_batch_number( P_ORG_ID => P_ORG_ID
2329 , p_invoke_point =>'CONC'
2330 , x_batch_number=> l_batch_number
2331 , x_succ => l_batch_number_ok
2332 );
2333
2334 IF NOT l_batch_number_ok
2335 THEN
2336 RETURN;
2337 END IF;
2338
2339 --compare the customer id from 3 different source
2340 IF P_Customer_id_from_Number IS NOT NULL
2341 AND (P_Customer_id_from_Number <>
2342 nvl(P_Customer_id_FROM_Name,P_Customer_id_from_Number)
2343 OR P_Customer_id_from_Number <>
2344 nvl(P_cust_id_from_Taxpayer,P_Customer_id_from_Number)
2345 )
2346 THEN
2347 fnd_message.SET_NAME('JMF','JMF_GTA_NO_DATA_FOUND');
2348 l_error_msg:=g_comment_delimiter||fnd_message.GET;
2349 put_line(l_error_msg);
2350 RETURN;
2351
2352 ELSIF P_Customer_id_from_Number IS NOT NULL
2353 --P_Customer_id_from_Number=P_Customer_id_FROM_Name=P_cust_id_from_Taxpayer
2354 THEN
2355 l_Customer_id:=P_Customer_id_from_Number;
2356
2357 ELSIF P_Customer_id_FROM_Name IS NOT NULL
2358 AND P_Customer_id_FROM_Name <>
2359 nvl(P_cust_id_from_Taxpayer,P_Customer_id_FROM_Name)
2360 THEN
2361 fnd_message.SET_NAME('JMF','JMF_GTA_NO_DATA_FOUND');
2362 l_error_msg:=g_comment_delimiter||fnd_message.GET;
2363 put_line(l_error_msg);
2364 RETURN;
2365 ELSE-- P_Customer_id_FROM_Name is null
2366 --or P_Customer_id_FROM_Name=
2367 -- nvl(P_Customer_id_FROM_Name,P_Customer_id_FROM_Name
2368 l_Customer_id:=nvl(P_cust_id_from_Taxpayer,P_Customer_id_FROM_Name);
2369
2370 END IF; --P_Customer_id_from_Number IS NOT NULL
2371 --AND (P_Customer_id_from_Number<>
2372 -- nvl(P_Customer_id_FROM_Name,P_Customer_id_from_Number)
2373 --OR P_Customer_id_from_Number<>
2374 -- nvl(P_cust_id_from_Taxpayer,P_Customer_id_from_Number)
2375
2376
2377 IF P_Invoice_Type_ID <>'A' THEN --if user selects a particular invoice_type
2378 OPEN l_cur_header FOR
2379 SELECT
2380 h.*
2381 FROM JMF_GTA_TRX_HEADERS h
2382 , ra_customer_trx_all ar
2383 , Ra_Cust_Trx_Types_all ctt
2384 , RA_CUST_TRX_LINE_GL_DIST_all gd
2385 , ra_batches_all b
2386 WHERE h.org_id = p_ORG_ID
2387 AND ar.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID
2388 AND ctt.ORG_ID = p_org_id
2389 AND h.fp_tax_registration_number = p_FP_Tax_reg_Number
2390 AND h.RA_TRX_ID = ar.CUSTOMER_TRX_ID
2391 AND GD.CUSTOMER_TRX_ID = h.RA_TRX_ID
2392 AND GD.ACCOUNT_CLASS = 'REC'
2393 AND GD.LATEST_REC_FLAG = 'Y'
2394 AND gd.Org_Id = p_org_id
2395 AND ar.BATCH_ID = b.batch_id(+)
2396 AND ar.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,ar.BILL_TO_CUSTOMER_ID)
2397 AND h.rule_header_id = nvl(p_transfer_rule_id,h.rule_header_id)
2398 AND ar.trx_number BETWEEN l_AR_Trx_Num_From
2399 AND l_AR_Trx_Num_To
2400 AND trunc(ar.trx_date,'DDD') BETWEEN l_AR_Trx_Date_From --jogen Mar-22, 2006
2401 AND l_AR_Trx_Date_To -- bug 5107043
2402 AND trunc(gd.GL_DATE,'DDD') BETWEEN l_AR_Trx_GL_Date_From
2403 AND l_AR_Trx_GL_Date_To --jogen Mar-22, 2006
2404 AND nvl(b.name,' ') BETWEEN l_AR_Trx_Batch_From
2405 AND l_AR_Trx_Batch_To
2406 AND ctt.TYPE = nvl(p_Trx_Class,ctt.type)
2407 AND h.latest_version_flag = 'Y'
2408 AND h.SOURCE = 'AR'
2409 AND h.status = 'DRAFT'
2410 AND h.invoice_type = P_Invoice_Type_ID; --added by subba.
2411
2412 --add by Lv Xiao for bug#7626503 on 11-Dec-2008, begin
2413 -------------------------------------------------------------------------------
2414 /*
2415 * Following cursor will fetch data when the package is called from concurrent.
2416 * For credit memo transaction referenced to Special VAT invoice and Recycle
2417 * VAT invoice, the cursor fetches all the duplicated description among 'DRAFT'
2418 * GTA Invoice need to generate this time and 'GENERATED', 'COMPLETED' GTA
2419 * Invoices.
2420 */
2421 OPEN l_cur_draft_dup FOR
2422 SELECT JGTHA.ra_trx_id
2423 , JGTHA.description
2424 , JGTHA.org_id
2425 , JGTHA.gta_trx_number
2426 FROM JMF_GTA_TRX_HEADERS_ALL JGTHA
2427 WHERE Get_Trx_Class(JGTHA.org_id, JGTHA.ra_trx_id) = 'CM'
2428 AND JGTHA.status IN ('GENERATED', 'COMPLETED')
2429
2430 AND JGTHA.description IN /*( SELECT h.description
2431 FROM JMF_GTA_TRX_HEADERS h
2432 , ra_customer_trx_all ar
2433 , Ra_Cust_Trx_Types_all ctt
2434 , RA_CUST_TRX_LINE_GL_DIST_all gd
2435 , ra_batches_all b
2436 WHERE ( h.org_id = p_ORG_ID
2437 AND ar.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID
2438 AND ctt.ORG_ID = p_org_id
2439 AND h.fp_tax_registration_number = p_FP_Tax_reg_Number
2440 AND h.RA_TRX_ID = ar.CUSTOMER_TRX_ID
2441 AND GD.CUSTOMER_TRX_ID = h.RA_TRX_ID
2442 AND GD.ACCOUNT_CLASS = 'REC'
2443 AND GD.LATEST_REC_FLAG = 'Y'
2444 AND gd.Org_Id = p_org_id
2445 AND ar.BATCH_ID = b.batch_id
2446 AND h.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,h.BILL_TO_CUSTOMER_ID)
2447 AND h.rule_header_id = nvl(p_transfer_rule_id,h.rule_header_id)
2448 AND h.ra_trx_number BETWEEN l_AR_Trx_Num_From
2449 AND l_AR_Trx_Num_To
2450 AND trunc(h.transaction_date,'DDD') BETWEEN l_AR_Trx_Date_From
2451 AND l_AR_Trx_Date_To
2452 AND trunc(h.ra_gl_date,'DDD') BETWEEN l_AR_Trx_GL_Date_From
2453 AND l_AR_Trx_GL_Date_To
2454 AND nvl(b.name,' ') BETWEEN l_AR_Trx_Batch_From
2455 AND l_AR_Trx_Batch_To
2456 AND ctt.TYPE = nvl(p_Trx_Class,ctt.type)
2457 AND h.latest_version_flag = 'Y'
2458 AND h.SOURCE = 'AR'
2459 AND h.status = 'DRAFT'
2460 AND h.invoice_type = P_Invoice_Type_ID)
2461 OR ( h.status IN ('GENERATED', 'COMPLETED'))
2462
2463 GROUP BY h.description
2464 HAVING COUNT(h.description) > 1)*/
2465
2466 ( SELECT DISTINCT description
2467 FROM (SELECT description
2468 FROM JMF_GTA_TRX_HEADERS JGTH
2469 WHERE JGTH.status IN ('GENERATED', 'COMPLETED')
2470 INTERSECT
2471 SELECT h.description
2472 FROM JMF_GTA_TRX_HEADERS h
2473 , ra_customer_trx_all ar
2474 , Ra_Cust_Trx_Types_all ctt
2475 , RA_CUST_TRX_LINE_GL_DIST_all gd
2476 , ra_batches_all b
2477 WHERE h.org_id = p_ORG_ID
2478 AND ar.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID
2479 AND ctt.ORG_ID = p_org_id
2480
2481 AND h.RA_TRX_ID = ar.CUSTOMER_TRX_ID
2482 AND GD.CUSTOMER_TRX_ID = h.RA_TRX_ID
2483 AND GD.ACCOUNT_CLASS = 'REC'
2484 AND GD.LATEST_REC_FLAG = 'Y'
2485 AND gd.Org_Id = p_org_id
2486 AND ar.BATCH_ID = b.batch_id(+)
2487
2488 --AND ar.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,ar.BILL_TO_CUSTOMER_ID)
2489 AND h.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,h.BILL_TO_CUSTOMER_ID)
2490
2491 AND h.rule_header_id = nvl(p_transfer_rule_id,h.rule_header_id)
2492
2493 AND h.fp_tax_registration_number = p_FP_Tax_reg_Number
2494 --AND ar.trx_number
2495 AND h.ra_trx_number
2496 BETWEEN l_AR_Trx_Num_From
2497 AND l_AR_Trx_Num_To
2498
2499 --AND trunc(ar.trx_date,'DDD')
2500 --AND trunc(h.transaction_date,'DDD')
2501 AND h.transaction_date
2502 BETWEEN l_AR_Trx_Date_From
2503 AND l_AR_Trx_Date_To
2504 --AND trunc(gd.GL_DATE,'DDD')
2505 --AND trunc(h.RA_GL_DATE,'DDD')
2506 AND h.RA_GL_DATE
2507 BETWEEN l_AR_Trx_GL_Date_From
2508 AND l_AR_Trx_GL_Date_To
2509 AND nvl(b.name,' ') BETWEEN l_AR_Trx_Batch_From
2510 AND l_AR_Trx_Batch_To
2511 AND ctt.TYPE = nvl(p_Trx_Class,ctt.type)
2512 AND h.latest_version_flag = 'Y'
2513 AND h.SOURCE = 'AR'
2514 AND h.status = 'DRAFT'
2515 AND h.invoice_type = P_Invoice_Type_ID))
2516
2517
2518 UNION ALL
2519
2520 SELECT JGTHA.ra_trx_id
2521 , JGTHA.description
2522 , JGTHA.org_id
2523 , JGTHA.gta_trx_number
2524 FROM JMF_GTA_TRX_HEADERS_ALL JGTHA
2525 , ra_customer_trx_all ar
2526 , Ra_Cust_Trx_Types_all ctt
2527 , RA_CUST_TRX_LINE_GL_DIST_all gd
2528 , ra_batches_all b
2529 WHERE Get_Trx_Class(JGTHA.org_id, JGTHA.ra_trx_id) = 'CM'
2530 AND JGTHA.status = 'DRAFT'
2531
2532 AND JGTHA.org_id = p_ORG_ID
2533 AND ar.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID
2534 AND ctt.ORG_ID = p_org_id
2535
2536 AND JGTHA.RA_TRX_ID = ar.CUSTOMER_TRX_ID
2537 AND GD.CUSTOMER_TRX_ID = JGTHA.RA_TRX_ID
2538 AND GD.ACCOUNT_CLASS = 'REC'
2539 AND GD.LATEST_REC_FLAG = 'Y'
2540 AND gd.Org_Id = p_org_id
2541 AND ar.BATCH_ID = b.batch_id(+)
2542
2543 --AND ar.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,ar.BILL_TO_CUSTOMER_ID)
2544 AND JGTHA.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,JGTHA.BILL_TO_CUSTOMER_ID)
2545
2546 AND JGTHA.rule_header_id = nvl(p_transfer_rule_id,JGTHA.rule_header_id)
2547 AND JGTHA.fp_tax_registration_number = p_FP_Tax_reg_Number
2548
2549 --AND ar.trx_number
2550 AND JGTHA.ra_trx_number
2551 BETWEEN l_AR_Trx_Num_From
2552 AND l_AR_Trx_Num_To
2553 --AND trunc(ar.trx_date,'DDD')
2554 --AND trunc(JGTHA.transaction_date,'DDD')
2555 AND JGTHA.transaction_date
2556 BETWEEN l_AR_Trx_Date_From
2557 AND l_AR_Trx_Date_To
2558 --AND trunc(gd.GL_DATE,'DDD')
2559 --AND trunc(JGTHA.RA_GL_DATE,'DDD')
2560 AND JGTHA.RA_GL_DATE
2561 BETWEEN l_AR_Trx_GL_Date_From
2562 AND l_AR_Trx_GL_Date_To
2563 AND nvl(b.name,' ') BETWEEN l_AR_Trx_Batch_From
2564 AND l_AR_Trx_Batch_To
2565 AND ctt.TYPE = nvl(p_Trx_Class,ctt.type)
2566 AND JGTHA.latest_version_flag = 'Y'
2567 AND JGTHA.SOURCE = 'AR'
2568 AND JGTHA.invoice_type = P_Invoice_Type_ID
2569
2570 AND JGTHA.description IN /*( SELECT h.description
2571 FROM JMF_GTA_TRX_HEADERS h
2572 , ra_customer_trx_all ar
2573 , Ra_Cust_Trx_Types_all ctt
2574 , RA_CUST_TRX_LINE_GL_DIST_all gd
2575 , ra_batches_all b
2576 WHERE ( h.org_id = p_ORG_ID
2577 AND ar.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID
2578 AND ctt.ORG_ID = p_org_id
2579 AND h.fp_tax_registration_number = p_FP_Tax_reg_Number
2580 AND h.RA_TRX_ID = ar.CUSTOMER_TRX_ID
2581 AND GD.CUSTOMER_TRX_ID = h.RA_TRX_ID
2582 AND GD.ACCOUNT_CLASS = 'REC'
2583 AND GD.LATEST_REC_FLAG = 'Y'
2584 AND gd.Org_Id = p_org_id
2585 AND ar.BATCH_ID = b.batch_id
2586 AND h.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,h.BILL_TO_CUSTOMER_ID)
2587 AND h.rule_header_id = nvl(p_transfer_rule_id,h.rule_header_id)
2588 AND h.ra_trx_number BETWEEN l_AR_Trx_Num_From
2589 AND l_AR_Trx_Num_To
2590 AND trunc(h.transaction_date,'DDD') BETWEEN l_AR_Trx_Date_From
2591 AND l_AR_Trx_Date_To
2592 AND trunc(h.ra_gl_date,'DDD') BETWEEN l_AR_Trx_GL_Date_From
2593 AND l_AR_Trx_GL_Date_To
2594 AND nvl(b.name,' ') BETWEEN l_AR_Trx_Batch_From
2595 AND l_AR_Trx_Batch_To
2596 AND ctt.TYPE = nvl(p_Trx_Class,ctt.type)
2597 AND h.latest_version_flag = 'Y'
2598 AND h.SOURCE = 'AR'
2599 AND h.status = 'DRAFT'
2600 AND h.invoice_type = P_Invoice_Type_ID)
2601
2602 OR ( h.status IN ('GENERATED', 'COMPLETED') )
2603 GROUP BY h.description
2604 HAVING COUNT(h.description) > 1)*/
2605
2606 ( SELECT DISTINCT description
2607 FROM (SELECT description
2608 FROM JMF_GTA_TRX_HEADERS JGTH
2609 WHERE JGTH.status IN ('GENERATED', 'COMPLETED')
2610 INTERSECT
2611 SELECT h.description
2612 FROM JMF_GTA_TRX_HEADERS h
2613 , ra_customer_trx_all ar
2614 , Ra_Cust_Trx_Types_all ctt
2615 , RA_CUST_TRX_LINE_GL_DIST_all gd
2616 , ra_batches_all b
2617 WHERE h.org_id = p_ORG_ID
2618 AND ar.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID
2619 AND ctt.ORG_ID = p_org_id
2620
2621 AND h.RA_TRX_ID = ar.CUSTOMER_TRX_ID
2622 AND GD.CUSTOMER_TRX_ID = h.RA_TRX_ID
2623 AND GD.ACCOUNT_CLASS = 'REC'
2624 AND GD.LATEST_REC_FLAG = 'Y'
2625 AND gd.Org_Id = p_org_id
2626 AND ar.BATCH_ID = b.batch_id(+)
2627
2628 --AND ar.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,ar.BILL_TO_CUSTOMER_ID)
2629 AND h.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,h.BILL_TO_CUSTOMER_ID)
2630
2631 AND h.rule_header_id = nvl(p_transfer_rule_id,h.rule_header_id)
2632
2633 AND h.fp_tax_registration_number = p_FP_Tax_reg_Number
2634 --AND ar.trx_number
2635 AND h.ra_trx_number
2636 BETWEEN l_AR_Trx_Num_From
2637 AND l_AR_Trx_Num_To
2638
2639 --AND trunc(ar.trx_date,'DDD')
2640 --AND trunc(h.transaction_date,'DDD')
2641 AND h.transaction_date
2642 BETWEEN l_AR_Trx_Date_From
2643 AND l_AR_Trx_Date_To
2644 --AND trunc(gd.GL_DATE,'DDD')
2645 --AND trunc(h.RA_GL_DATE,'DDD')
2646 AND h.RA_GL_DATE
2647 BETWEEN l_AR_Trx_GL_Date_From
2648 AND l_AR_Trx_GL_Date_To
2649 AND nvl(b.name,' ') BETWEEN l_AR_Trx_Batch_From
2650 AND l_AR_Trx_Batch_To
2651 AND ctt.TYPE = nvl(p_Trx_Class,ctt.type)
2652 AND h.latest_version_flag = 'Y'
2653 AND h.SOURCE = 'AR'
2654 AND h.status = 'DRAFT'
2655 AND h.invoice_type = P_Invoice_Type_ID));
2656
2657 ELSE --user selects 'All Invoices'
2658 NULL;
2659
2660 /*
2661 * following code is commented since the concurrent parameter: invoice type
2662 * can't be selected as 'ALL' in this change.
2663 */
2664
2665 /* OPEN l_cur_header FOR
2666 SELECT
2667 h.*
2668 FROM JMF_GTA_TRX_HEADERS h
2669 , ra_customer_trx_all ar
2670 , Ra_Cust_Trx_Types_all ctt
2671 , RA_CUST_TRX_LINE_GL_DIST_all gd
2672 , ra_batches_all b
2673 WHERE h.org_id = p_ORG_ID
2674 AND ar.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID
2675 AND ctt.ORG_ID = p_org_id
2676 AND h.fp_tax_registration_number = p_FP_Tax_reg_Number
2677 AND h.RA_TRX_ID = ar.CUSTOMER_TRX_ID
2678 AND GD.CUSTOMER_TRX_ID = h.RA_TRX_ID
2679 AND GD.ACCOUNT_CLASS = 'REC'
2680 AND GD.LATEST_REC_FLAG = 'Y'
2681 AND gd.Org_Id = p_org_id
2682 AND ar.BATCH_ID = b.batch_id(+)
2683 AND ar.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,ar.BILL_TO_CUSTOMER_ID)
2684 AND h.rule_header_id = nvl(p_transfer_rule_id,h.rule_header_id)
2685 AND ar.trx_number BETWEEN l_AR_Trx_Num_From
2686 AND l_AR_Trx_Num_To
2687 AND trunc(ar.trx_date,'DDD') BETWEEN l_AR_Trx_Date_From --jogen Mar-22, 2006
2688 AND l_AR_Trx_Date_To -- bug 5107043
2689 AND trunc(gd.GL_DATE,'DDD') BETWEEN l_AR_Trx_GL_Date_From
2690 AND l_AR_Trx_GL_Date_To --jogen Mar-22, 2006
2691 AND nvl(b.name,' ') BETWEEN l_AR_Trx_Batch_From
2692 AND l_AR_Trx_Batch_To
2693 AND ctt.TYPE = nvl(p_Trx_Class,ctt.type)
2694 AND h.latest_version_flag = 'Y'
2695 AND h.SOURCE = 'AR'
2696 AND h.status = 'DRAFT'; */
2697 /* AND ar.trx_date BETWEEN l_AR_Trx_Date_From
2698 AND l_AR_Trx_Date_To
2699 AND gd.GL_DATE BETWEEN l_AR_Trx_GL_Date_From
2700 AND l_AR_Trx_GL_Date_To */
2701 END IF; /* P_Invoice_Type_ID <>'A'*/ --added by subba.
2702 -------------------------------------------------------------------------------
2703 --add by Lv Xiao for bug#7626503 on 11-Dec-2008, end
2704
2705 export_Invoices(l_cur_header,l_batch_number, NULL, NULL, l_cur_draft_dup);
2706 CLOSE l_cur_draft_dup;
2707
2708 ELSE --P_regeneration<>'N'
2709
2710
2711 OPEN l_cur_header FOR
2712 SELECT *
2713 FROM JMF_GTA_TRX_HEADERS
2714 WHERE Gta_Batch_Number=P_Batch_ID
2715 AND status='GENERATED';
2716
2717 export_Invoices(l_cur_header,P_Batch_ID, NULL, NULL);
2718
2719 END IF; --P_regeneration='N'
2720
2721
2722 CLOSE l_cur_header;
2723
2724 --log for debug
2725 IF( l_proc_level >= l_dbg_level )
2726 THEN
2727 FND_LOG.STRING(l_proc_level
2728 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
2729 ,'Exit procedure');
2730 END IF; --( l_proc_level >= l_dbg_level )
2731
2732 EXCEPTION
2733
2734 WHEN OTHERS THEN
2735 IF(l_proc_level >= l_dbg_level)
2736 THEN
2737 FND_LOG.string( l_proc_level
2738 , G_MODULE_PREFIX || l_procedure_name || '.OTHER_EXCEPTION'
2739 , SQLCODE||SQLERRM);
2740 END IF;
2741 RAISE;
2742 END Export_Invoices_From_Conc;
2743
2744 --==========================================================================
2745 -- PROCEDURE NAME:
2746 --
2747 -- Export_Invoices_From_Workbench Public
2748 --
2749 -- DESCRIPTION:
2750 --
2751 -- This procedure export VAT invoices from GTA to flat file
2752 -- and is invoked in workbench.
2753 --
2754 -- PARAMETERS:
2755 -- In: p_org_id Identifier of operating unit
2756 -- p_generator_id Indicate which need export(choose in workbench)
2757 -- P_Batch_ID export batch number
2758 --
2759 -- Out:
2760 --
2761 -- DESIGN REFERENCES:
2762 -- GTA-Txt-Interface-TD.doc
2763 --
2764 -- CHANGE HISTORY:
2765 --
2766 -- 05/12/05 Jogen Hu Created
2767 -- 09/12/08 Lv Xiao Modified for bug#7626503
2768 -- Validate format of VAT Invoice Number
2769 -- Check the
2770 -- 09/01/2009 Yao Zhang Modified for bug 7673309
2771 --===========================================================================
2772 PROCEDURE Export_Invoices_From_Workbench
2773 ( p_org_id IN NUMBER
2774 , p_generator_ID IN NUMBER
2775 , P_Batch_ID IN VARCHAR2
2776 )
2777 IS
2778 l_procedure_name VARCHAR2(40):='export_Invoices_from_Workbench';
2779 l_cur_header c_trx_header_id_type;
2780
2781 --add by Lv Xiao for bug#7626503 on 11-Dec-2008, begin
2782 -----------------------------------------------------------------------------
2783 l_cur_draft_dup crmemo_dup_cur_TYPE;--get draft crmemo with duplicate description
2784 -------------------------------------------------------------------------------
2785 --add by Lv Xiao for bug#7626503 on 11-Dec-2008, end
2786
2787 l_batch_number Jmf_Gta_Trx_Headers_All.Gta_Batch_Number%TYPE:=P_Batch_ID;
2788 l_batch_number_ok BOOLEAN;
2789 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
2790 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
2791 BEGIN
2792
2793 --log for debug
2794 IF( l_proc_level >= l_dbg_level )
2795 THEN
2796 FND_LOG.STRING(l_proc_level
2797 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
2798 ,'Enter procedure');
2799 END IF; --( l_proc_level >= l_dbg_level )
2800
2801 check_batch_number( P_ORG_ID => P_ORG_ID
2802 , p_invoke_point=> 'WBCH'
2803 , x_batch_number=> l_batch_number
2804 , x_succ => l_batch_number_ok
2805 );
2806
2807 IF NOT l_batch_number_ok
2808 THEN
2809 RETURN;
2810 END IF;
2811
2812
2813 OPEN l_cur_header FOR
2814 SELECT *
2815 FROM JMF_GTA_TRX_HEADERS
2816 WHERE Generator_Id=p_generator_ID
2817 AND status='DRAFT';
2818
2819 --add by Lv Xiao for bug#7626503 on 11-Dec-2008, begin
2820 -----------------------------------------------------------------------------
2821 /*
2822 * Following cursor will fetch data when the package is called from workbench.
2823 * For credit memo transaction referenced to Special VAT invoice and Recycle
2824 * VAT invoice, the cursor will fetch all the duplicated descriptions among
2825 * 'DRAFT' GTA Invoices need to generate from this concurrent call and
2826 * 'GENERATED' and 'COMPLETED' GTA Invoices.
2827 */
2828 OPEN l_cur_draft_dup FOR
2829 SELECT JGTHA.ra_trx_id
2830 , JGTHA.description
2831 , JGTHA.org_id
2832 , JGTHA.gta_trx_number
2833 FROM JMF_GTA_TRX_HEADERS_ALL JGTHA
2834 WHERE Get_Trx_Class(JGTHA.org_id, JGTHA.ra_trx_id) = 'CM'
2835 AND JGTHA.Invoice_Type<>'2'--yao zhang add for bug 7673309
2836 AND JGTHA.source='AR'--yao zhang add for bug7673309
2837 AND JGTHA.status IN ('GENERATED', 'COMPLETED')
2838 AND JGTHA.org_id = p_org_id
2839
2840 --modified by Lv Xiao for bug#7644803 on 16-Dec-08, begin
2841 -------------------------------------------------------------------
2842 /* AND JGTHA.description IN ( SELECT DISTINCT description
2843 FROM (SELECT description
2844 FROM JMF_GTA_TRX_HEADERS_ALL JGTH
2845 WHERE JGTH.status IN ('GENERATED', 'COMPLETED')
2846 INTERSECT
2847 SELECT description
2848 FROM JMF_GTA_TRX_HEADERS_ALL
2849 WHERE Generator_Id=p_generator_ID
2850 AND status='DRAFT') )*/
2851
2852
2853 AND JGTHA.description IN ( SELECT description
2854 FROM JMF_GTA_TRX_HEADERS JGTH
2855 WHERE (( status = 'DRAFT'
2856 AND generator_id = p_generator_ID )
2857 OR status IN ('GENERATED', 'COMPLETED'))
2858 AND Invoice_Type<>'2'--yao zhang add for bug 7673309
2859 AND source='AR'--yao zhang add for bug 7673309
2860 GROUP BY description
2861 HAVING COUNT(description) > 1)
2862
2863 UNION ALL
2864
2865 SELECT JGTHA.ra_trx_id
2866 , JGTHA.description
2867 , JGTHA.org_id
2868 , JGTHA.gta_trx_number
2869 FROM JMF_GTA_TRX_HEADERS_ALL JGTHA
2870 WHERE Get_Trx_Class(JGTHA.org_id, JGTHA.ra_trx_id) = 'CM'
2871 AND JGTHA.Invoice_Type<>'2'--Yao Zhang add for bug 7673309
2872 AND JGTHA.source='AR'--Yao Zhang add for bug 7673309
2873 AND JGTHA.status = 'DRAFT'
2874 AND JGTHA.generator_id = p_generator_ID
2875 AND JGTHA.org_id = p_org_id
2876 /* AND JGTHA.description IN ( SELECT DISTINCT description
2877 FROM (SELECT description
2878 FROM JMF_GTA_TRX_HEADERS_ALL JGTH
2879 WHERE JGTH.status IN ('GENERATED', 'COMPLETED')
2880 INTERSECT
2881 SELECT description
2882 FROM JMF_GTA_TRX_HEADERS_ALL
2883 WHERE Generator_Id=p_generator_ID
2884 AND status='DRAFT') ) ;*/
2885
2886
2887 AND JGTHA.description IN ( SELECT description
2888 FROM JMF_GTA_TRX_HEADERS JGTH
2889 WHERE (( status = 'DRAFT'
2890 AND generator_id = p_generator_ID )
2891 OR status IN ('GENERATED', 'COMPLETED'))
2892 AND Invoice_Type<>'2'--Yao Zhang add for bug7673309
2893 AND source='AR'--Yao Zhang add for bug7673309
2894 GROUP BY description
2895 HAVING COUNT(description) > 1);
2896
2897 -------------------------------------------------------------------
2898 --modified by Lv Xiao for bug#7644803 on 16-Dec-08, end
2899
2900 --export_Invoices(l_cur_header,l_batch_number);
2901 export_Invoices(l_cur_header
2902 , l_batch_number
2903 , p_generator_id
2904 , p_org_id
2905 , l_cur_draft_dup);
2906
2907 CLOSE l_cur_draft_dup;
2908 CLOSE l_cur_header;
2909 -----------------------------------------------------------------------------
2910 --add by Lv Xiao for bug#7626503 on 11-Dec-2008, end
2911
2912
2913 --log for debug
2914 IF( l_proc_level >= l_dbg_level )
2915 THEN
2916 FND_LOG.STRING(l_proc_level
2917 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
2918 ,'Exit procedure');
2919 END IF; --( l_proc_level >= l_dbg_level )
2920
2921 --FND_FILE.Put_Line(FND_FILE.Log, 'export_Invoices_from_Workbench. end');
2922 EXCEPTION
2923
2924 WHEN OTHERS THEN
2925 IF(l_proc_level >= l_dbg_level)
2926 THEN
2927 FND_LOG.string( l_proc_level
2928 , G_MODULE_PREFIX || l_procedure_name || '.OTHER_EXCEPTION '
2929 , SQLCODE||SQLERRM);
2930 END IF;
2931
2932 RAISE;
2933 END Export_Invoices_From_Workbench;
2934
2935 --==========================================================================
2936 -- FUNCTION NAME:
2937 --
2938 -- Get_Trx_Class Public
2939 --
2940 -- DESCRIPTION:
2941 --
2942 -- This procedure get transaction class
2943 --
2944 -- PARAMETERS:
2945 -- In: p_GTA_org_id GTA transaction org id
2946 -- In: p_GTA_trx_id GTA transaction id
2947 --
2948 -- Out:
2949 -- Return: VARCHAR2;
2950 --
2951 -- DESIGN REFERENCES:
2952 -- GTA-Txt-Interface-TD.doc
2953 --
2954 -- CHANGE HISTORY:
2955 -- 09/12/08 Lv Xiao Created
2956 --===========================================================================
2957 FUNCTION Get_Trx_Class
2958 ( p_GTA_org_id IN NUMBER
2959 , p_GTA_trx_id IN NUMBER
2960 )
2961 RETURN VARCHAR2
2962 IS
2963
2964 lv_class_type VARCHAR2(30);
2965
2966 l_procedure_name VARCHAR2(50) :='Get_Trx_Class';
2967 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
2968 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
2969
2970
2971 BEGIN
2972
2973 --log for debug
2974 IF( l_proc_level >= l_dbg_level )
2975 THEN
2976 FND_LOG.STRING(l_proc_level
2977 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
2978 ,'Enter procedure');
2979 END IF; --( l_proc_level >= l_dbg_level )
2980
2981 SELECT DISTINCT RCTT.type
2982 INTO lv_class_type
2983 FROM RA_CUST_TRX_TYPES_ALL RCTT
2984 , RA_CUSTOMER_TRX_ALL RCT
2985 , JMF_GTA_TRX_HEADERS_ALL JGTH
2986 WHERE RCTT.cust_trx_type_id = RCT.cust_trx_type_id
2987 AND JGTH.source = 'AR'
2988 AND RCTT.org_id = p_GTA_org_id
2989 AND RCT.customer_trx_id = p_GTA_trx_id
2990 AND JGTH.ra_trx_id = p_GTA_trx_id;
2991 --log for debug
2992 IF( l_proc_level >= l_dbg_level )
2993 THEN
2994 FND_LOG.STRING(l_proc_level
2995 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
2996 ,'Exit procedure');
2997 END IF; --( l_proc_level >= l_dbg_level )
2998
2999 RETURN lv_class_type;
3000
3001 END Get_Trx_Class;
3002
3003
3004 --==========================================================================
3005 -- FUNCTION NAME:
3006 --
3007 -- Check_Header Public
3008 --
3009 -- DESCRIPTION:
3010 --
3011 -- This procedure check whether the columns of export data
3012 -- exceeding Golden Tax required length.
3013 --
3014 -- PARAMETERS:
3015 -- In: p_gta_trx_header GTA transaction header
3016 -- Out:
3017 -- Return: PLS_INTEGER
3018 --
3019 -- DESIGN REFERENCES:
3020 -- GTA-Txt-Interface-TD.doc
3021 --
3022 -- CHANGE HISTORY:
3023 -- 05/12/05 Jogen Hu Created
3024 -- 24/11/2008 Brian Zhao Modified for bug 7590613
3025 -- 09/12/2008 Lv Xiao Modified for bug#7626503
3026 -- 26/12/2008 Yao Zhang Modified for bug#7670310
3027 --===========================================================================
3028 FUNCTION Check_Header
3029 ( p_gta_trx_header IN JMF_GTA_TRX_UTIL.TRX_HEADER_REC_TYPE
3030 )
3031 RETURN PLS_INTEGER
3032 IS
3033
3034 l_cm_delimiter1 VARCHAR2(100);
3035
3036 l_cm_delimiter2 VARCHAR2(100);
3037
3038 l_procedure_name VARCHAR2(50) :='Check_Header';
3039 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
3040 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
3041 l_trx_class VARCHAR2(20); --added by yao Zhang for bug 7670310
3042 BEGIN
3043
3044 --log for debug
3045 IF( l_proc_level >= l_dbg_level )
3046 THEN
3047 FND_LOG.STRING(l_proc_level
3048 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
3049 ,'Enter procedure');
3050 END IF; --( l_proc_level >= l_dbg_level )
3051
3052
3053
3054 IF length(p_GTA_trx_header.bill_to_customer_name)>100
3055 OR length(p_GTA_trx_header.customer_address_phone)>80
3056 OR length(p_GTA_trx_header.bank_account_name_number)>80
3057 OR length(p_GTA_trx_header.gta_trx_number)>20
3058 OR length(p_GTA_trx_header.description)>160
3059 THEN
3060 RETURN G_EXPORT_EXCEED_ERROR ;
3061 END IF;
3062 --modified by subba to add condition for common invoice type in R12.1
3063
3064 /* IF ( (p_GTA_trx_header.invoice_type <> '2') AND (length(p_GTA_trx_header.tp_tax_registration_number)<>15) )
3065 THEN
3066 RETURN G_EXPORT_TAXPAYERID_ERROR;
3067
3068 END IF;
3069 */
3070
3071 -- Modified by Brian for bug 7590613
3072
3073 IF ((p_GTA_trx_header.invoice_type <> '2') AND
3074 (length(p_GTA_trx_header.tp_tax_registration_number) <> 15)) OR
3075 (p_GTA_trx_header.invoice_type = '2' AND
3076 length(p_GTA_trx_header.tp_tax_registration_number) <> 15 AND
3077 (p_GTA_trx_header.tp_tax_registration_number IS NOT NULL))
3078 THEN
3079 RETURN G_EXPORT_TAXPAYERID_ERROR;
3080
3081 END IF;
3082 --The following code is recovered by Yao Zhang for bug 7670310
3083 --The following check logic is only for common Credit Memo
3084 --following code is commented by subba to relax the validation for CM in R12.1
3085 IF (p_GTA_trx_header.invoice_type = '2') then
3086 BEGIN
3087 SELECT t.TYPE
3088 INTO l_trx_class
3089 FROM ra_customer_trx_all ct
3090 , ra_cust_trx_types_all t
3091 WHERE ct.cust_trx_type_id = t.cust_trx_type_id
3092 AND ct.customer_trx_id=p_GTA_trx_header.ra_trx_id
3093 AND t.org_id=p_GTA_trx_header.org_id;
3094 EXCEPTION
3095 WHEN NO_DATA_FOUND THEN
3096 RETURN G_EXPORT_MISSING;
3097
3098 END;
3099
3100 fnd_message.SET_NAME( APPLICATION => 'JMF'
3101 , NAME => 'JMF_GTA_CREDMEMO_EXPORT_IV'
3102 );
3103 l_cm_delimiter1:=fnd_message.get;
3104
3105 fnd_message.SET_NAME( APPLICATION => 'JMF'
3106 , NAME => 'JMF_GTA_CREDMEMO_EXPORT_NR'
3107 );
3108 l_cm_delimiter2:=fnd_message.get;
3109
3110 IF l_trx_class='CM'
3111 AND ( p_GTA_trx_header.DESCRIPTION IS NULL
3112 OR instr(p_GTA_trx_header.description,l_cm_delimiter1)<1
3113 OR instr(p_GTA_trx_header.description,l_cm_delimiter2)<1
3114 )
3115 THEN
3116 RETURN G_EXPORT_CRMEMO_MISSING_ERROR;
3117
3118 END IF;
3119 END IF;
3120 --comments end for CM validation
3121 --The above code is recovered by Yao Zhang for bug 7670310
3122
3123 --log for debug
3124 IF( l_proc_level >= l_dbg_level )
3125 THEN
3126 FND_LOG.STRING(l_proc_level
3127 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
3128 ,'Exit procedure');
3129 END IF; --( l_proc_level >= l_dbg_level )
3130
3131 RETURN G_EXPORT_SUCC;
3132 END Check_Header;
3133
3134 --==========================================================================
3135 -- FUNCTION NAME:
3136 --
3137 -- Check_Line_Length Public
3138 --
3139 -- DESCRIPTION:
3140 --
3141 -- This procedure check whether the columns of export data
3142 -- exceeding Golden Tax required length
3143 --
3144 -- PARAMETERS:
3145 -- In: p_gta_trx_line GTA transaction line record
3146 --
3147 -- Out:
3148 -- Return: BOOLEAN;
3149 --
3150 -- DESIGN REFERENCES:
3151 -- GTA-Txt-Interface-TD.doc
3152 --
3153 -- CHANGE HISTORY:
3154 -- 05/12/05 Jogen Hu Created
3155 --===========================================================================
3156 FUNCTION Check_Line_Length
3157 ( p_GTA_trx_line IN JMF_GTA_TRX_UTIL.TRX_LINE_REC_TYPE
3158 )
3159 RETURN BOOLEAN
3160 IS
3161 l_len_before_dot_qty NUMBER :=0;
3162 l_len_after_dot_qty NUMBER :=0;
3163 l_len_before_dot_amt NUMBER :=0;
3164 l_len_after_dot_amt NUMBER :=0;
3165 l_len_before_dot_tax NUMBER :=0;
3166 l_len_after_dot_tax NUMBER :=0;
3167 l_pos NUMBER :=0;
3168 l_procedure_name VARCHAR2(50) :='Check_Line_Length';
3169 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
3170 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
3171
3172 BEGIN
3173
3174 --log for debug
3175 IF( l_proc_level >= l_dbg_level )
3176 THEN
3177 FND_LOG.STRING(l_proc_level
3178 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
3179 ,'Enter procedure');
3180 END IF; --( l_proc_level >= l_dbg_level )
3181
3182 --convert float number to string limitation
3183 l_pos:=instr(to_char(p_GTA_trx_line.quantity),'.');
3184 IF l_pos>0 THEN
3185 l_len_before_dot_qty:=l_pos - 1;
3186 l_len_after_dot_qty :=length(to_char(p_GTA_trx_line.quantity)) - l_pos;
3187 ELSE
3188 l_len_before_dot_qty:=l_pos;
3189 END IF;
3190
3191 l_pos:=instr(to_char(p_GTA_trx_line.amount),'.');
3192 IF l_pos>0 THEN
3193 l_len_before_dot_amt:=l_pos - 1;
3194 l_len_after_dot_amt :=length(to_char(p_GTA_trx_line.amount)) - l_pos;
3195 ELSE
3196 l_len_before_dot_amt:=l_pos;
3197 END IF;
3198
3199 l_pos:=instr(to_char(p_GTA_trx_line.tax_rate),'.');
3200 IF l_pos>0 THEN
3201 l_len_before_dot_tax:=l_pos - 1;
3202 l_len_after_dot_tax :=length(to_char(p_GTA_trx_line.tax_rate)) - l_pos;
3203 ELSE
3204 l_len_before_dot_tax:=l_pos;
3205 END IF;
3206
3207 IF length(p_GTA_trx_line.item_description)>60
3208 OR length(p_GTA_trx_line.uom_name) >16
3209 OR length(p_GTA_trx_line.item_model) >30
3210 OR length(p_GTA_trx_line.item_tax_denomination)>4
3211 OR l_len_before_dot_qty >16
3212 OR l_len_after_dot_qty >6
3213 OR l_len_before_dot_amt >14
3214 OR l_len_after_dot_amt >2
3215 OR l_len_before_dot_tax >4
3216 OR l_len_after_dot_tax >2
3217
3218 THEN
3219 RETURN FALSE;
3220 END IF;
3221
3222 --log for debug
3223 IF( l_proc_level >= l_dbg_level )
3224 THEN
3225 FND_LOG.STRING(l_proc_level
3226 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
3227 ,'Exit procedure');
3228 END IF; --( l_proc_level >= l_dbg_level )
3229
3230 RETURN TRUE;
3231 END Check_Line_Length;
3232
3233 --==========================================================================
3234 -- PROCEDURE NAME:
3235 --
3236 -- Export_Customers Public
3237 --
3238 -- DESCRIPTION:
3239 --
3240 -- This procedure export customers information from GTA to flat file
3241 --
3242 -- PARAMETERS:
3243 -- In: p_org_id Identifier of operating unit
3244 -- p_customer_num_from Customer number low range
3245 -- p_customer_num_to Customer number high range
3246 -- p_customer_name_from Customer name low range
3247 -- p_customer_name_to Customer name high range
3248 -- p_taxpayee_id Identifier of taxpayer
3249 -- p_creation_date_from Creation date low range
3250 -- p_creation_date_to Creation date high range
3251 --
3252 -- OUt:
3253 --
3254 -- DESIGN REFERENCES:
3255 -- GTA-Txt-Interface-TD.doc
3256 --
3257 -- CHANGE HISTORY:
3258 --
3259 -- 06/05/05 Jim Zheng Created
3260 -- 30/09/05 Jim Zheng updated delete the export of tax_payer_id
3261 -- 08/11/05 Jim Zheng updated add '~~' in output file,because the
3262 -- tax_payer_id should be leave blank
3263 -- 11/11/05 Jim Zheng updated, the bank account mask profile change
3264 -- to CE_MASK_INTERNAL_BANK_ACCT_NUM, and the value
3265 -- change to NO MASK
3266 --
3267 --===========================================================================
3268 PROCEDURE export_customers
3269 ( P_ORG_ID IN NUMBER
3270 , P_CUSTOMER_NUM_FROM IN VARCHAR2
3271 , P_CUSTOMER_NUM_TO IN VARCHAR2
3272 , P_CUSTOMER_NAME_FROM IN VARCHAR2
3273 , P_CUSTOMER_NAME_TO IN VARCHAR2
3274 , P_CREATION_DATE_FROM IN DATE
3275 , P_CREATION_DATE_TO IN DATE
3276 )
3277 IS
3278 l_procedure_name VARCHAR2(30) := 'export_customers';
3279
3280 l_CUSTOMER_NUM_FROM HZ_CUST_ACCOUNTS.Account_Number%TYPE;
3281 l_CUSTOMER_NUM_TO HZ_CUST_ACCOUNTS.Account_Number%TYPE;
3282 l_CUSTOMER_NAME_FROM HZ_PARTIES.Party_Name%TYPE;
3283 l_CUSTOMER_NAME_TO HZ_PARTIES.Party_Name%TYPE;
3284
3285 l_CREATION_DATE_FROM HZ_CUST_ACCOUNTS.Creation_Date%TYPE;
3286 l_CREATION_DATE_TO HZ_CUST_ACCOUNTS.Creation_Date%TYPE;
3287
3288 l_customer_id HZ_CUST_ACCOUNTS.Cust_Account_Id%TYPE;
3289 l_customer_number HZ_CUST_ACCOUNTS.Account_Number%TYPE;
3290 l_customer_name HZ_PARTIES.Party_Name%TYPE;
3291 --l_taxpayer_id HZ_PARTIES.JGZZ_FISCAL_CODE%TYPE;
3292 l_customer_name_phonetic HZ_PARTIES.ORGANIZATION_NAME_PHONETIC%TYPE;
3293 l_party_id HZ_PARTIES.PARTY_ID%TYPE;
3294 l_alternate_name HZ_PARTIES.ORGANIZATION_NAME_PHONETIC%TYPE;
3295
3296 l_phone_num Hz_Contact_Points.Phone_Number%TYPE;
3297 l_currency_code JMF_GTA_SYSTEM_PARAMETERS_ALL.Gt_Currency_Code%TYPE;
3298 l_bank_account_name IBY_EXT_BANK_ACCOUNTS.BANK_ACCOUNT_NAME%TYPE;
3299 l_bank_account_num IBY_EXT_BANK_ACCOUNTS.BANK_ACCOUNT_NUM%TYPE;
3300 l_address AR_ADDRESSES_V.concatenated_address%TYPE;
3301 l_address_phonenumber AR_ADDRESSES_V.concatenated_address%TYPE;
3302
3303 l_party_site_id hz_party_sites.party_site_id%TYPE;
3304 l_customer_site_id hz_cust_acct_sites.CUST_ACCT_SITE_ID%TYPE;
3305 l_customer_site_use_id hz_cust_site_uses.SITE_USE_ID%TYPE;
3306
3307 l_bank_account VARCHAR2(110);
3308
3309 l_comment VARCHAR2(2) := '//';
3310 l_bound VARCHAR2(2) := '~~';
3311 l_exceed_length VARCHAR2(1000) := 'The customers below are excluded for exceeding the maximum length of the fields:';
3312 l_error_bound VARCHAR2(1000):= '*****************************************';
3313 l_cust_number_length NUMBER := 16;
3314 l_cust_name_length NUMBER := 60;
3315 l_alternate_name_length NUMBER := 6;
3316 l_taxpayer_id_length NUMBER := 15;
3317 l_address_phonenumber_length NUMBER := 80;
3318 l_bank_account_name_length NUMBER := 80;
3319
3320 l_err_text VARCHAR2(2000);
3321 l_error_string VARCHAR2(2000);
3322
3323 l_jmf_gta_gta_not_enabled VARCHAR2(2000);
3324 l_count NUMBER;
3325 l_conc_succ BOOLEAN; -- the status of concurrent
3326
3327 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
3328 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
3329
3330 l_ext_payer_id IBY_EXTERNAL_PAYERS_ALL.ext_payer_id%TYPE;
3331
3332
3333 TYPE l_string_tbl IS TABLE OF VARCHAR2(1000);
3334
3335 l_taxpayer_tbl l_string_tbl := l_string_tbl();
3336 l_exceed_tbl l_string_tbl := l_string_tbl();
3337 l_index NUMBER ; -- loop flag
3338
3339 CURSOR
3340 c_customer(p_num_from VARCHAR2
3341 , p_num_to VARCHAR2
3342 , p_name_from VARCHAR2
3343 , p_name_to VARCHAR2
3344 , p_create_from DATE
3345 , p_create_to DATE
3346 )
3347 IS
3348 SELECT
3349 CUST.CUST_ACCOUNT_ID
3350 , CUST.ACCOUNT_NUMBER
3351 , CUST_PARTY.PARTY_NAME
3352 --, CUST_PARTY.JGZZ_FISCAL_CODE
3353 , CUST_PARTY.ORGANIZATION_NAME_PHONETIC
3354 , CUST_PARTY.PARTY_ID
3355 FROM
3356 HZ_CUST_ACCOUNTS CUST
3357 , HZ_PARTIES CUST_PARTY
3358 WHERE cust.party_id = cust_party.party_id
3359 AND cust.account_number BETWEEN p_num_from AND p_num_to
3360 AND cust_party.party_name BETWEEN p_name_from AND p_name_to
3361 AND cust.creation_date BETWEEN p_create_from AND p_create_to
3362 --AND (cust_party.jgzz_fiscal_code = p_taxpayer_id OR p_taxpayer_id IS NULL)
3363 AND cust.status = 'A'
3364 AND cust_party.party_type = 'ORGANIZATION';
3365
3366 BEGIN
3367 -- add primary site id in query sql commment
3368 -- category the wrong process
3369 -- add the chinese token
3370
3371 IF(l_proc_level >= l_dbg_level)
3372 THEN
3373 fnd_log.STRING(l_proc_level
3374 , G_MODULE_PREFIX||l_procedure_name
3375 , ' Procedure begin . ');
3376 END IF;
3377
3378 -- check the profile for bank account mask
3379 IF fnd_profile.VALUE('CE_MASK_INTERNAL_BANK_ACCT_NUM') <> 'NO MASK'
3380 THEN
3381 fnd_message.set_name('JMF', 'JMF_GTA_BANKACCOUNT_MASKING');
3382
3383 fnd_file.put_line(fnd_file.OUTPUT, fnd_message.get());
3384
3385 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3386 THEN
3387 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
3388 , G_MODULE_PREFIX||l_procedure_name
3389 , l_error_string);
3390 END IF;
3391 --set concurrent status to Warning
3392 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
3393 , message => l_error_string
3394 );
3395 RETURN;
3396 END IF;
3397
3398 --output the title. by message and token
3399 -- message line 1
3400 fnd_message.set_name('JMF', 'JMF_GTA_CUSTOMER_EXPORT_LNA');
3401 fnd_message.set_token('PREFIX', '{');
3402 fnd_message.set_token('MIDFIX', '}[');
3403 fnd_message.set_token('SUFFIX', ']"~~"');
3404 fnd_file.put_line(fnd_file.output, fnd_message.get());
3405
3406 -- message line 2
3407 fnd_message.set_name('JMF', 'JMF_GTA_CUSTOMER_EXPORT_LNB');
3408 fnd_message.set_token('PREFIX', '//');
3409 fnd_message.set_token('SUFFIX', ':');
3410 fnd_file.put_line(fnd_file.output, fnd_message.get());
3411
3412 -- message line 3
3413 fnd_message.set_name('JMF', 'JMF_GTA_CUSTOMER_EXPORT_LNC');
3414 fnd_message.set_token('PREFIX', '//');
3415 fnd_message.set_token('MIDFIX', '~~');
3416 fnd_file.put_line(fnd_file.output, fnd_message.get());
3417
3418 l_customer_num_from :=nvl(p_customer_num_from,' ');
3419 l_customer_num_to :=nvl(p_customer_num_to,rpad('z',30,'z'));
3420 l_customer_name_from :=nvl(p_customer_name_from,' ');
3421 l_customer_name_to :=nvl(p_customer_name_to,rpad('z',30,'z'));
3422 l_creation_date_from :=nvl(p_creation_date_from,to_date('1900-01-01','RRRR-MM-DD'));
3423 l_creation_date_to :=nvl(p_creation_date_to,to_date('3000-01-01','RRRR-MM-DD'));
3424 --l_taxpayer_id :=P_TAXPAYEE_ID;
3425
3426 -- no data found message .
3427 SELECT
3428 COUNT(cust.cust_account_id)
3429 INTO
3430 l_count
3431 FROM
3432 HZ_CUST_ACCOUNTS CUST
3433 , HZ_PARTIES CUST_PARTY
3434 WHERE cust.party_id = cust_party.party_id
3435 AND cust.account_number BETWEEN l_customer_num_from AND l_customer_num_to
3436 AND cust_party.party_name BETWEEN l_customer_name_from AND l_customer_name_to
3437 AND cust.creation_date BETWEEN l_creation_date_from AND l_creation_date_to
3438 --AND (cust_party.jgzz_fiscal_code = l_taxpayer_id OR l_taxpayer_id IS NULL)
3439 AND cust.status = 'A'
3440 AND cust_party.party_type = 'ORGANIZATION';
3441
3442 IF l_count = 0
3443 THEN
3444 fnd_message.set_name('JMF', 'JMF_GTA_NO_DATA_FOUND');
3445 l_error_string := fnd_message.get();
3446 fnd_file.put_line(fnd_file.OUTPUT, l_comment||l_error_string);
3447 END IF;
3448
3449 OPEN c_customer(l_customer_num_from
3450 ,l_customer_num_to
3451 ,l_customer_name_from
3452 ,l_customer_name_to
3453 ,l_creation_date_from
3454 ,l_creation_date_to
3455 --,l_taxpayer_id
3456 );
3457 LOOP
3458 --fetch c_customer to variables;
3459 -- WHILE c_customer%FOUND
3460 FETCH
3461 c_customer
3462 INTO
3463 l_customer_id
3464 , l_customer_number
3465 , l_customer_name
3466 --, l_taxpayer_id
3467 , l_alternate_name --l_customer_name_phonetic
3468 , l_party_id;
3469
3470 IF c_customer%NOTFOUND
3471 THEN
3472 EXIT;
3473 END IF ;
3474
3475 -- init the customer var.
3476
3477 l_address := NULL;
3478 l_customer_site_id := NULL;
3479 l_party_site_id := NULL;
3480 l_phone_num := NULL;
3481 l_customer_site_use_id := NULL;
3482 l_bank_account_name := NULL;
3483 l_bank_account_num := NULL;
3484 l_address_phonenumber := NULL;
3485 l_bank_account:= NULL;
3486 l_ext_payer_id := NULL;
3487
3488
3489 BEGIN
3490 SELECT
3491 arp_addr_pkg.format_address(loc.address_style
3492 , loc.address1
3493 , loc.address2
3494 , loc.address3
3495 , loc.address4
3496 , loc.city
3497 , loc.county
3498 , loc.state
3499 , loc.province
3500 , loc.postal_code
3501 , terr.territory_short_name )
3502 , addr.CUST_ACCT_SITE_ID
3503 , party_site.party_site_id
3504 INTO
3505 l_address
3506 , l_customer_site_id
3507 , l_party_site_id
3508 FROM
3509 hz_cust_site_uses_all hcsua
3510 , hz_cust_acct_sites_all addr
3511 , hz_party_sites party_site
3512 , hz_locations loc
3513 , fnd_territories_tl terr
3514 WHERE addr.party_site_id = party_site.party_site_id
3515 AND loc.location_id = party_site.location_id
3516 AND hcsua.cust_acct_site_id = addr.cust_acct_site_id
3517 AND hcsua.site_use_code = 'BILL_TO'
3518 AND hcsua.status = 'A'
3519 AND hcsua.primary_flag = 'Y'
3520 AND loc.country = terr.territory_code(+)
3521 AND terr.LANGUAGE = USERENV('LANG')
3522 AND addr.org_id = p_org_id
3523 AND addr.cust_account_id = l_customer_id;
3524
3525 EXCEPTION
3526 WHEN NO_DATA_FOUND THEN
3527 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3528 THEN
3529 fnd_log.STRING(FND_LOG.LEVEL_EXCEPTION
3530 , G_MODULE_PREFIX || l_procedure_name || '.NoDataFound'
3531 ,'Customer'|| l_customer_name || 'has no primary bill to address');
3532 END IF;
3533 END;
3534
3535 --get phone info
3536 BEGIN
3537 SELECT
3538 phone_number
3539 INTO
3540 l_phone_num
3541 FROM
3542 Hz_Contact_Points
3543 WHERE owner_table_name='HZ_PARTY_SITES'
3544 AND owner_table_id=l_party_site_id
3545 AND phone_line_type='GEN'
3546 AND primary_flag='Y'
3547 AND status = 'A'
3548 AND contact_point_type = 'PHONE';
3549
3550 EXCEPTION
3551 WHEN NO_DATA_FOUND THEN
3552 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3553 THEN
3554 fnd_log.STRING(FND_LOG.LEVEL_EXCEPTION
3555 , G_MODULE_PREFIX || l_procedure_name || '.NoDataFound'
3556 ,'Customer'|| l_customer_name || 'has no primary phone number'||SQLCODE||SQLERRM);
3557 END IF;
3558 END;
3559
3560 --get customer site use id
3561 BEGIN
3562 SELECT
3563 SITE.SITE_USE_ID
3564 INTO
3565 l_customer_site_use_id
3566 FROM
3567 hz_cust_site_uses site
3568 WHERE SITE.CUST_ACCT_SITE_ID = l_customer_site_id
3569 AND SITE.SITE_USE_CODE = 'BILL_TO'
3570 AND SITE.STATUS = 'A';
3571 EXCEPTION
3572 WHEN OTHERS THEN
3573 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3574 THEN
3575 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
3576 , G_MODULE_PREFIX || l_procedure_name || '.NoDataFound'
3577 , l_err_text||SQLCODE ||SQLERRM);
3578 END IF;
3579 END;
3580
3581
3582 -- get currency code from GTA
3583 BEGIN
3584 l_err_text:='Golden Tax Interface Currency code setup error!';
3585 SELECT
3586 GT_CURRENCY_CODE
3587 INTO
3588 l_currency_code
3589 FROM
3590 JMF_GTA_SYSTEM_PARAMETERS_ALL
3591 WHERE
3592 org_id = p_org_id;
3593
3594 EXCEPTION
3595 WHEN OTHERS THEN
3596 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3597 THEN
3598 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
3599 , G_MODULE_PREFIX || l_procedure_name || '.NoDataFound'
3600 , l_err_text||SQLCODE ||SQLERRM);
3601 END IF;
3602 END;
3603
3604 -- get ext pmt party id
3605 BEGIN
3606 SELECT
3607 ext_payer_id
3608 INTO
3609 l_ext_payer_id
3610 FROM
3611 IBY_EXTERNAL_PAYERS_ALL
3612 WHERE party_id = l_party_id
3613 AND CUST_ACCOUNT_ID = l_customer_id -- site account id
3614 AND ACCT_SITE_USE_ID = l_customer_site_use_id -- site use id
3615 AND ORG_ID = p_org_id -- org id
3616 AND org_type = 'OPERATING_UNIT' -- ou
3617 AND payment_function = 'CUSTOMER_PAYMENT'; -- function
3618
3619 EXCEPTION
3620 WHEN OTHERS THEN
3621 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3622 THEN
3623 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
3624 , G_MODULE_PREFIX || l_procedure_name || '.NoDataFound'
3625 , l_err_text||SQLCODE ||SQLERRM);
3626 END IF;
3627 END;
3628
3629
3630 --get back name and account number info(need org_id when MOAC)
3631 BEGIN
3632
3633 l_err_text:='Customer'|| l_customer_name || 'has no primary bank name and accout!';
3634 SELECT
3635 bank_account_name
3636 , bank_account_num
3637 INTO
3638 l_bank_account_name
3639 , l_bank_account_num
3640 FROM (SELECT ibybanks.bank_account_name
3641 , ibybanks.bank_account_num
3642 FROM IBY_PMT_INSTR_USES_ALL ExtPartyInstrumentsEO
3643 , IBY_EXT_BANK_ACCOUNTS ibybanks
3644 WHERE ibybanks.EXT_BANK_ACCOUNT_ID = ExtPartyInstrumentsEO.instrument_id
3645 AND ExtPartyInstrumentsEO.INSTRUMENT_TYPE = 'BANKACCOUNT'
3646 AND ExtPartyInstrumentsEO.EXT_PMT_PARTY_ID = l_ext_payer_id
3647 AND ExtPartyInstrumentsEO.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
3648 AND ibybanks.currency_code = l_currency_code
3649 AND SYSDATE BETWEEN nvl(ExtPartyInstrumentsEO.START_DATE, to_date('1900-01-01','RRRR-MM-DD'))
3650 AND nvl(ExtPartyInstrumentsEO.END_DATE, to_date('3000-01-01','RRRR-MM-DD'))
3651 ORDER BY ExtPartyInstrumentsEO.ORDER_OF_PREFERENCE)
3652 WHERE ROWNUM =1;
3653
3654 EXCEPTION
3655 WHEN OTHERS THEN
3656 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3657 THEN
3658 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
3659 , G_MODULE_PREFIX || l_procedure_name || '.NoDataFound'
3660 , l_err_text||SQLCODE ||SQLERRM);
3661 END IF;
3662 END;
3663
3664 IF l_address IS NULL AND l_phone_num IS NULL
3665 THEN
3666 l_address_phonenumber := NULL;
3667 ELSIF l_address IS NOT NULL AND l_phone_num IS NULL
3668 THEN
3669 l_address_phonenumber := l_address;
3670 ELSIF l_address IS NULL AND l_phone_num IS NOT NULL
3671 THEN
3672 l_address_phonenumber := l_phone_num;
3673 ELSE
3674 l_address_phonenumber := l_address||' '||l_phone_num;
3675 END IF; /*l_address IS NULL AND l_phone_num IS NULL*/
3676
3677 IF l_bank_account_name IS NULL AND l_bank_account_num IS NULL
3678 THEN
3679 l_bank_account := NULL;
3680 ELSIF l_bank_account_name IS NOT NULL AND l_bank_account_num IS NULL
3681 THEN
3682 l_bank_account := l_bank_account_name;
3683 ELSIF l_bank_account_name IS NULL AND l_bank_account_num IS NOT NULL
3684 THEN
3685 l_bank_account := l_bank_account_num;
3686 ELSE
3687 l_bank_account := l_bank_account_name||' '||l_bank_account_num;
3688 END IF; /*l_bank_account_name IS NULL AND l_bank_account_num IS NULL*/
3689
3690
3691 /*
3692 IF length(l_taxpayer_id) > l_taxpayer_id_length
3693 OR length(l_taxpayer_id) < l_taxpayer_id_length
3694 THEN
3695 -- insert the error line into nested table
3696 l_taxpayer_tbl.EXTEND;
3697 l_taxpayer_tbl(l_taxpayer_tbl.COUNT) := l_comment
3698 ||nvl(l_customer_number, ' ')
3699 ||l_bound
3700 ||nvl(l_customer_name, ' ')
3701 ||l_bound
3702 ||nvl(l_alternate_name, ' ')
3703 ||l_bound
3704 ||nvl(l_taxpayer_id, ' ' )
3705 ||l_bound
3706 ||nvl(l_address_phonenumber, ' ')
3707 ||l_bound
3708 ||nvl(l_bank_account, ' ');
3709 */
3710
3711 IF length(l_customer_number) > l_cust_number_length
3712 OR length(l_customer_name) > l_cust_name_length
3713 OR length(l_alternate_name) > l_alternate_name_length
3714 OR length(l_address_phonenumber)> l_address_phonenumber_length
3715 OR length(l_bank_account) > l_bank_account_name_length
3716 THEN
3717
3718 l_exceed_tbl.EXTEND;
3719 l_exceed_tbl(l_exceed_tbl.COUNT) := l_comment
3720 ||l_customer_number
3721 ||l_bound
3722 ||l_customer_name
3723 ||l_bound
3724 ||l_alternate_name
3725 ||l_bound
3726 --||' '--nvl(l_taxpayer_id, ' ' )
3727 ||l_bound
3728 ||l_address_phonenumber
3729 ||l_bound
3730 ||l_bank_account;
3731
3732 ELSE
3733 fnd_file.PUT_LINE(fnd_file.OUTPUT, l_customer_number
3734 ||l_bound
3735 ||l_customer_name
3736 ||l_bound
3737 ||l_alternate_name
3738 ||l_bound
3739 --||' '--nvl(l_taxpayer_id, ' ' )
3740 ||l_bound
3741 ||l_address_phonenumber
3742 ||l_bound
3743 ||l_bank_account
3744 );
3745 END IF;
3746 END LOOP;
3747
3748 CLOSE c_customer;
3749
3750
3751 /*
3752 IF l_taxpayer_tbl.COUNT > 0
3753 THEN
3754 -- output JMF_GTA_INVALID_LENGTH
3755 fnd_message.SET_NAME('JMF', 'JMF_GTA_INVALID_LENGTH');
3756 l_error_string := fnd_message.get;
3757
3758 fnd_file.put_line(fnd_file.output, l_comment||l_error_bound);
3759 fnd_file.PUT_LINE(fnd_file.output, l_comment||l_error_string);
3760 fnd_file.put_line(fnd_file.output, '');
3761
3762 l_index := l_taxpayer_tbl.FIRST;
3763 WHILE l_index IS NOT NULL
3764 LOOP
3765 fnd_file.put_line(fnd_file.output, l_taxpayer_tbl(l_index));
3766 l_index := l_taxpayer_tbl.NEXT(l_index);
3767 END LOOP;
3768
3769 fnd_file.PUT_LINE(fnd_file.OUTPUT, l_comment||l_error_bound);
3770
3771 END IF;
3772 */
3773 IF l_exceed_tbl.COUNT > 0
3774 THEN
3775 -- output JMF_GTA_EXCEEDL_LENGTH message
3776 fnd_message.set_name('JMF', 'JMF_GTA_EXCEED_LENGTH');
3777 l_error_string := fnd_message.GET;
3778 fnd_file.put_line(fnd_file.output, l_comment||l_error_bound);
3779 fnd_file.PUT_LINE(fnd_file.output, l_comment||l_error_string);
3780 fnd_file.put_line(fnd_file.output, '');
3781
3782 l_index := l_exceed_tbl.FIRST;
3783 WHILE l_index IS NOT NULL
3784 LOOP
3785 fnd_file.put_line(fnd_file.output, l_exceed_tbl(l_index));
3786 l_index := l_exceed_tbl.NEXT(l_index);
3787 END LOOP;
3788
3789 fnd_file.PUT_LINE(fnd_file.OUTPUT, l_comment||l_error_bound);
3790 END IF ;
3791
3792
3793 IF(l_proc_level >= l_dbg_level)
3794 THEN
3795 fnd_log.STRING(l_proc_level
3796 , G_MODULE_PREFIX||l_procedure_name
3797 , ' Procedure End . ');
3798 END IF;
3799
3800 EXCEPTION
3801 WHEN OTHERS THEN
3802 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3803 THEN
3804 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
3805 , G_MODULE_PREFIX || l_procedure_name || '.OTHER_EXCEPTION '
3806 , SQLCODE||':'||SQLERRM);
3807 END IF;
3808 RAISE;
3809
3810 END Export_customers;
3811
3812 --==========================================================================
3813 -- FUNCTION NAME:
3814 --
3815 -- Check_Item_Length Private
3816 --
3817 -- DESCRIPTION:
3818 --
3819 --
3820 -- The function is to judge if length of attributes of a item exeede limit,
3821 -- if no, then return TRUE, else return FALSE
3822 --
3823 -- PARAMETERS:
3824 -- In: p_item_number Item number
3825 -- p_item_name Item name
3826 -- p_tax_name Tax name
3827 -- p_item_model Model of item
3828 -- p_uom Unit of measure
3829 --
3830 -- Return: BOOLEAN (TRUE/FALSE)
3831 --
3832 -- DESIGN REFERENCES:
3833 -- GTA-Txt-Interface-TD.doc
3834 --
3835 -- CHANGE HISTORY:
3836 --
3837 -- 17-MAY-2005 Donghai Wang Creation
3838 -- 06-MAR-2006 Donghai Wang Add fnd log
3839 -- 06-APR-2006 Donghai Wang Remove the parameter p_tax_rate to
3840 -- fix bug 5138356 due to no longer
3841 -- export tax rate by items
3842 --===========================================================================
3843 FUNCTION Check_Item_Length
3844 (p_item_number IN VARCHAR2
3845 ,p_item_name IN VARCHAR2
3846 ,p_tax_name IN VARCHAR2
3847 ,p_item_model IN VARCHAR2
3848 ,p_uom IN VARCHAR2
3849 ) RETURN BOOLEAN
3850 IS
3851 l_item_number mtl_system_items_b_kfv.concatenated_segments%TYPE :=p_item_number;
3852 l_item_name mtl_system_items_b.description%TYPE :=p_item_name;
3853 l_tax_name mtl_system_items_b.attribute1%TYPE :=p_tax_name;
3854 l_item_model mtl_system_items_b.attribute1%TYPE :=p_item_model;
3855 l_uom mtl_system_items_b.primary_unit_of_measure%TYPE :=p_uom;
3856 l_item_number_max_length NUMBER :=16;
3857 l_item_name_max_length NUMBER :=60;
3858 l_tax_name_max_length NUMBER :=4;
3859 l_taxrate_maxlen_before_dot NUMBER :=4;
3860 l_taxrate_maxlen_after_dot NUMBER :=2;
3861 l_item_model_max_length NUMBER :=30;
3862 l_uom_max_length NUMBER :=16;
3863 l_tax_rate_dot_position NUMBER;
3864 l_item_number_length NUMBER;
3865 l_item_name_length NUMBER;
3866 l_tax_name_length NUMBER;
3867 l_tax_rate_length_before_dot NUMBER;
3868 l_tax_rate_length_after_dot NUMBER;
3869 l_item_model_length NUMBER;
3870 l_uom_length NUMBER;
3871 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
3872 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
3873 l_procedure_name VARCHAR2(100) :='Check_Item_Length';
3874 l_dbg_msg VARCHAR2(1000);
3875 BEGIN
3876
3877 --log for debug
3878 IF( l_proc_level >= l_dbg_level )
3879 THEN
3880 FND_LOG.STRING(l_proc_level
3881 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
3882 ,'Enter function');
3883
3884 FND_LOG.STRING(l_proc_level
3885 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.parameters'
3886 ,'p_item_number '||p_item_number);
3887
3888 FND_LOG.STRING(l_proc_level
3889 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.parameters'
3890 ,'p_item_name '||p_item_name);
3891
3892 FND_LOG.STRING(l_proc_level
3893 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.parameters'
3894 ,'p_tax_name '||p_tax_name);
3895
3896
3897 FND_LOG.STRING(l_proc_level
3898 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.parameters'
3899 ,'p_item_model '||p_item_model);
3900
3901 FND_LOG.STRING(l_proc_level
3902 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.parameters'
3903 ,'p_uom '||p_uom);
3904 END IF; --( l_proc_level >= l_dbg_level )
3905
3906
3907
3908 --Get length of attributes of the item
3909 l_item_number_length:=NVL(LENGTH(l_item_number),0);
3910 l_item_name_length:=NVL(LENGTH(l_item_name),0);
3911 l_tax_name_length:=NVL(LENGTH(l_tax_name),0);
3912 l_item_model_length:=NVL(LENGTH(l_item_model),0);
3913 l_uom_length:=NVL(LENGTH(l_uom),0);
3914
3915 --If any fields exceed max lenth allowed, then return false
3916 IF (l_item_number_length>l_item_number_max_length) OR
3917 (l_item_name_length>l_item_name_max_length) OR
3918 (l_tax_name_length>l_tax_name_max_length) OR
3919 (l_item_model_length>l_item_model_max_length) OR
3920 (l_uom_length>l_uom_max_length)
3921 THEN
3922 RETURN FALSE;
3923 ELSE
3924 RETURN TRUE;
3925 END IF; --(l_item_number_length>l_item_number_max_length) or (l_item_name_length>l_item_name_max_length) ....
3926
3927
3928 --log for debug
3929 IF( l_proc_level >= l_dbg_level )
3930 THEN
3931 FND_LOG.STRING(l_proc_level
3932 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
3933 ,'Exit function');
3934 END IF; --( l_proc_level >= l_dbg_level )
3935
3936 EXCEPTION
3937 WHEN OTHERS THEN
3938 IF(l_proc_level >= l_dbg_level)
3939 THEN
3940 FND_LOG.string( l_proc_level
3941 , G_MODULE_PREFIX || l_procedure_name || '.OTHER_EXCEPTION '
3942 , SQLCODE||':'||SQLERRM);
3943 END IF;--(l_proc_level >= l_dbg_level)
3944 END Check_Item_Length;
3945
3946
3947 --==========================================================================
3948 -- PROCEDURE NAME:
3949 --
3950 -- Export_To_Flat_File Private
3951 --
3952 -- DESCRIPTION:
3953 --
3954 --
3955 -- The procedure is called by export_items procedure, the purpose is to
3956 -- export selected items to flat file
3957 --
3958 -- PARAMETERS:
3959 -- In: p_noreference PL/SQL table to store items that can not
3960 -- be export due to no cross reference
3961 -- defined,although the parameter
3962 -- p_item_name_source has defined
3963 -- name of item should be got from item
3964 -- cross reference
3965 -- p_export_item PL/SQL table to store items that can be
3966 -- successfully exported
3967 -- p_item_length_exp PL/SQL table to store items that can not
3968 -- be exported due to over-long
3969 --
3970 -- Out:
3971 --
3972 --
3973 -- DESIGN REFERENCES:
3974 -- GTA-Txt-Interface-TD.doc
3975 --
3976 -- CHANGE HISTORY:
3977 --
3978 -- 17-MAY-2005 Donghai Wang Creation
3979 -- 18-Oct-2005 Donghai Wang update code not to export tax rate any more due to
3980 -- ebtax functionality
3981 -- 06-Apr-2006 Donghai Wang Remove tax rate from exception
3982 -- records export to fix bug 5138356
3983 --===========================================================================
3984 PROCEDURE Export_To_Flat_File
3985 (p_noreference IN JMF_GTA_TXT_OPERATOR_PROC.G_Noreference_Tbl
3986 ,p_export_item IN JMF_GTA_TXT_OPERATOR_PROC.G_Item_Tbl
3987 ,p_item_length_exp IN JMF_GTA_TXT_OPERATOR_PROC.G_Item_Tbl
3988 )
3989 IS
3990 l_noreference JMF_GTA_TXT_OPERATOR_PROC.G_Noreference_Tbl :=p_noreference;
3991 l_export_item JMF_GTA_TXT_OPERATOR_PROC.G_Item_Tbl :=p_export_item;
3992 l_item_length_exp JMF_GTA_TXT_OPERATOR_PROC.G_Item_Tbl :=p_item_length_exp;
3993 l_idx NUMBER;
3994 l_export_record VARCHAR2(4000);
3995 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
3996 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
3997 l_procedure_name VARCHAR2(100) :='Export_To_Flat_file';
3998 l_dbg_msg VARCHAR2(1000);
3999 BEGIN
4000
4001 --log for debug
4002 IF( l_proc_level >= l_dbg_level )
4003 THEN
4004 FND_LOG.STRING(l_proc_level
4005 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
4006 ,'Enter procedure');
4007 END IF; --( l_proc_level >= l_dbg_level )
4008
4009 -- export validated item
4010 IF l_export_item.COUNT>0
4011 THEN
4012
4013 --export required header section of export file
4014 --Line 1
4015 FND_MESSAGE.Set_Name(application => 'JMF'
4016 ,name => 'JMF_GTA_ITEM_EXPORT_LNA'
4017 );
4018 FND_MESSAGE.Set_Token(TOKEN =>'PREFIX'
4019 ,VALUE => '{'
4020 );
4021 FND_MESSAGE.Set_Token(TOKEN =>'MIDFIX'
4022 ,VALUE => '}['
4023 );
4024 FND_MESSAGE.Set_Token(TOKEN =>'SUFFIX'
4025 ,VALUE => ']"~~"'
4026 );
4027 FND_FILE.Put_Line(FND_FILE.Output,FND_MESSAGE.GET);
4028
4029 --Line2
4030 FND_MESSAGE.Set_Name(application => 'JMF'
4031 ,name => 'JMF_GTA_CUSTOMER_EXPORT_LNB'
4032 );
4033 FND_MESSAGE.Set_Token(TOKEN =>'PREFIX'
4034 ,VALUE => '//'
4035 );
4036 FND_MESSAGE.Set_Token(TOKEN =>'SUFFIX'
4037 ,VALUE => ':'
4038 );
4039 FND_FILE.Put_Line(FND_FILE.Output,FND_MESSAGE.GET);
4040
4041 --Line3
4042 FND_MESSAGE.Set_Name(application => 'JMF'
4043 ,name => 'JMF_GTA_ITEM_EXPORT_LNB'
4044 );
4045 FND_MESSAGE.Set_Token(TOKEN =>'PREFIX'
4046 ,VALUE => '//'
4047 );
4048 FND_MESSAGE.Set_Token(TOKEN =>'MIDFIX'
4049 ,VALUE => '~~'
4050 );
4051 FND_FILE.Put_Line(FND_FILE.Output,FND_MESSAGE.GET);
4052
4053 l_idx:='';
4054 l_idx:=l_export_item.FIRST;
4055 WHILE l_idx IS NOT NULL
4056 LOOP
4057
4058 --Comment following statement out to not export tax rate anymore
4059 /* l_export_record:=l_export_item(l_idx).item_number||'~~'||
4060 l_export_item(l_idx).item_name||'~~'
4061 ||'~~'
4062 ||l_export_item(l_idx).tax_name||'~~'
4063 ||l_export_item(l_idx).tax_rate||'~~'
4064 ||l_export_item(l_idx).item_model||'~~'
4065 ||l_export_item(l_idx).uom||'~~'
4066 ||'~~'
4067 ;*/
4068
4069 --Updated statement
4070 l_export_record:=l_export_item(l_idx).item_number||'~~'||
4071 l_export_item(l_idx).item_name||'~~'
4072 ||'~~'
4073 ||l_export_item(l_idx).tax_name||'~~'
4074 ||'~~'
4075 ||l_export_item(l_idx).item_model||'~~'
4076 ||l_export_item(l_idx).uom||'~~'
4077 ||'~~'
4078 ;
4079
4080 FND_FILE.Put_Line(FND_FILE.Output,l_export_record);
4081 l_idx:=l_export_item.NEXT(l_idx);
4082 END LOOP; -- l_idx IS NOT NULL
4083 END IF; --l_export_item.COUNT>0
4084
4085
4086 --export exception item records
4087 IF l_noreference.COUNT>0
4088 THEN --export items that don't define cross reference
4089 FND_FILE.Put_Line(FND_FILE.Output,'//******************************');
4090 FND_MESSAGE.Set_Name('JMF','JMF_GTA_ITEM_MISSING_CROSS_REF');
4091 FND_FILE.Put_Line(FND_FILE.Output,'//'||FND_MESSAGE.Get);
4092
4093 l_idx:='';
4094 l_idx:=l_noreference.FIRST;
4095 WHILE l_idx IS NOT NULL
4096 LOOP
4097 l_export_record:='//'||l_noreference(l_idx);
4098 FND_FILE.Put_Line(FND_FILE.Output,l_export_record);
4099 l_idx:=l_noreference.NEXT(l_idx);
4100 END LOOP; -- l_idx IS NOT NULL
4101
4102 FND_FILE.Put_Line(FND_FILE.Output,'//******************************');
4103 END IF; --l_noreference.COUNT>0
4104
4105 IF l_item_length_exp.COUNT>0
4106 THEN --export items that exceed length limitation
4107 FND_FILE.Put_Line(FND_FILE.Output,'//******************************');
4108 FND_MESSAGE.Set_Name('JMF','JMF_GTA_EXCEED_LENGTH');
4109 FND_FILE.Put_Line(FND_FILE.Output,'//'||FND_MESSAGE.Get);
4110
4111 l_idx:='';
4112 l_idx:=l_item_length_exp.FIRST;
4113 WHILE l_idx IS NOT NULL
4114 LOOP
4115 l_export_record:='//'
4116 ||l_item_length_exp(l_idx).item_number||'~~'
4117 ||l_item_length_exp(l_idx).item_name||'~~'
4118 ||'~~'
4119 ||l_item_length_exp(l_idx).tax_name||'~~'
4120 ||'~~'
4121 ||l_item_length_exp(l_idx).item_model||'~~'
4122 ||l_item_length_exp(l_idx).uom||'~~'
4123 ||'~~'
4124 ;
4125
4126 FND_FILE.Put_Line(FND_FILE.Output,l_export_record);
4127 l_idx:=l_item_length_exp.NEXT(l_idx);
4128 END LOOP; --l_idx IS NOT NULL
4129
4130 FND_FILE.Put_Line(FND_FILE.Output,'//******************************');
4131 END IF; --l_item_lengh_exp.COUNT>0
4132
4133 --log for debug
4134 IF( l_proc_level >= l_dbg_level )
4135 THEN
4136 FND_LOG.STRING(l_proc_level
4137 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
4138 ,'Exit procedure');
4139 END IF; --( l_proc_level >= l_dbg_level )
4140
4141 EXCEPTION
4142 WHEN OTHERS THEN
4143 IF(l_proc_level >= l_dbg_level)
4144 THEN
4145 FND_LOG.string( l_proc_level
4146 , G_MODULE_PREFIX || l_procedure_name || '.OTHER_EXCEPTION '
4147 , SQLCODE||':'||SQLERRM);
4148 END IF;--(l_proc_level >= l_dbg_level)
4149
4150 END Export_To_Flat_File;
4151
4152 --==========================================================================
4153 -- PROCEDURE NAME:
4154 --
4155 -- Export_Items Public
4156 --
4157 -- DESCRIPTION:
4158 --
4159 -- This procedure is to export item information from
4160 -- inventory to a flat file with special format, the flat
4161 -- will be used as import file to import items into GT system
4162 --
4163 -- PARAMETERS:
4164 -- In: p_org_id Identifier for Operating Unit
4165 -- p_master_org_id Identifier for Master Organization
4166 -- of inventory organization
4167 -- p_item_num_from High range of item number
4168 -- p_item_num_to Low range of item number
4169 -- p_category_set_id Identifier of item category set
4170 -- p_category_structure_id Identifier for structure of item
4171 -- category
4172 -- p_item_category_from High range of item category
4173 -- p_item_category_to Low range of item category
4174 -- p_item_name_source Iten name source, alternative
4175 -- value is 'MASTER_ITEM' or
4176 -- 'LATEST_ITEM_CROSS_REFERENCE',
4177 -- this parameter is to decide
4178 -- where item name is got from
4179 -- p_cross_reference_type Cross reference type of item
4180 -- p_item_status Item status
4181 -- p_creation_date_from High range of item creation date
4182 -- p_creation_date_to Low range of item creation date
4183 --
4184 -- Out:
4185 --
4186 --
4187 -- DESIGN REFERENCES:
4188 -- GTA-Txt-Interface-TD.doc
4189 --
4190 -- CHANGE HISTORY:
4191 --
4192 -- 17-MAY-2005 Donghai Wang Creation
4193 -- 06-MAR-2006 Donghai Wang Add fnd log
4194 -- 06-Apr-2006 Donghai Wang Remove tax rate from the procedure
4195 -- for fix bug 5138356
4196 --===========================================================================
4197 PROCEDURE Export_items
4198 ( p_org_id IN NUMBER
4199 , p_master_org_id IN NUMBER
4200 , p_item_num_from IN VARCHAR2
4201 , p_item_num_to IN VARCHAR2
4202 , p_category_set_id IN NUMBER
4203 , p_category_structure_id IN NUMBER
4204 , p_item_category_from IN VARCHAR2
4205 , p_item_category_to IN VARCHAR2
4206 , p_item_name_source IN VARCHAR2
4207 , p_cross_reference_type IN VARCHAR2
4208 , p_item_status IN VARCHAR2
4209 , p_creation_date_from IN VARCHAR2
4210 , p_creation_date_to IN VARCHAR2
4211 )
4212 IS
4213 l_org_id NUMBER :=p_org_id;
4214 l_master_org_id NUMBER :=p_master_org_id;
4215 l_item_num_from mtl_system_items_b_kfv.concatenated_segments%TYPE :=p_item_num_from;
4216 l_item_num_to mtl_system_items_b_kfv.concatenated_segments%TYPE :=p_item_num_to;
4217 l_category_set_id mtl_category_sets_b.category_set_id%TYPE :=p_category_set_id;
4218 l_structure_id mtl_category_sets_b.structure_id%TYPE :=p_category_structure_id;
4219 l_item_category_from mtl_categories_b_kfv.concatenated_segments%TYPE :=p_item_category_from;
4220 l_item_category_to mtl_categories_b_kfv.concatenated_segments%TYPE :=p_item_category_to;
4221 l_creation_date_from DATE;
4222 l_creation_date_to DATE;
4223 l_export_item_count NUMBER;
4224 l_item_name mtl_system_items_b.description%TYPE;
4225 l_item_name_source VARCHAR2(30) :=p_item_name_source;
4226 l_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
4227 l_cross_reference_type mtl_cross_references.cross_reference_type%TYPE :=p_cross_reference_type;
4228 l_item_number mtl_system_items_b_kfv.concatenated_segments%TYPE;
4229 l_item_attribute_category fnd_descr_flex_column_usages.descriptive_flex_context_code%TYPE;
4230 l_tax_name_column fnd_descr_flex_column_usages.application_column_name%TYPE;
4231 l_item_model_column fnd_descr_flex_column_usages.application_column_name%TYPE;
4232 l_tax_name mtl_system_items_b.attribute1%TYPE;
4233 l_item_model mtl_system_items_b.attribute1%TYPE;
4234 l_dbg_msg VARCHAR2(1000);
4235 l_item_status MTL_ITEM_STATUS.INVENTORY_ITEM_STATUS_CODE%TYPE :=p_item_status;
4236
4237 CURSOR c_item IS
4238 SELECT
4239 DISTINCT
4240 items.inventory_item_id
4241 ,items.concatenated_segments item_number
4242 ,items.DESCRIPTION item_name
4243 ,items.primary_unit_of_measure uom
4244 FROM
4245 mtl_system_items_b_kfv items
4246 ,mtl_item_categories mic
4247 ,mtl_category_sets_b mcs
4248 ,mtl_categories_b_kfv mc
4249 WHERE items.organization_id=l_master_org_id
4250 AND items.inventory_item_status_code=nvl(l_item_status,items.inventory_item_status_code)
4251 AND items.concatenated_segments>=nvl(l_item_num_from,items.concatenated_segments)
4252 AND items.concatenated_segments<=nvl(l_item_num_to,items.concatenated_segments)
4253 AND items.creation_date BETWEEN NVL(l_creation_date_from,items.creation_date)
4254 AND NVL(l_creation_date_to,items.creation_date)
4255 AND mic.organization_id(+)=l_master_org_id
4256 AND mic.inventory_item_id(+)=items.inventory_item_id
4257 AND ((mic.category_set_id=l_category_set_id) OR (l_category_set_id IS NULL))
4258 AND mic.category_set_id=mcs.category_set_id(+)
4259 AND mic.category_id=mc.category_id(+)
4260 AND ((mcs.structure_id=l_structure_id) OR (l_structure_id IS NULL))
4261 AND ((mc.concatenated_segments>=l_item_category_from) OR (l_item_category_from IS NULL))
4262 AND ((mc.concatenated_segments<=l_item_category_to) OR (l_item_category_to IS NULL));
4263
4264 l_item c_item%ROWTYPE;
4265
4266 CURSOR c_reference_desc IS
4267 SELECT
4268
4269 reference1.cross_reference
4270 FROM
4271 mtl_cross_references reference1
4272 WHERE reference1.inventory_item_id=l_inventory_item_id
4273 AND (reference1.organization_id=l_master_org_id OR reference1.organization_id IS NULL)
4274 AND reference1.cross_reference_type=l_cross_reference_type
4275 AND reference1.creation_date=(SELECT
4276 MAX(creation_date)
4277 FROM
4278 mtl_cross_references reference2
4279 WHERE reference2.inventory_item_id=l_inventory_item_id
4280 AND (reference2.organization_id=l_master_org_id OR reference2.organization_id IS NULL)
4281 AND reference2.cross_reference_type=l_cross_reference_type
4282 );
4283
4284 CURSOR c_get_dff IS
4285 SELECT
4286 inv_item_context_code
4287 ,inv_tax_attribute_column
4288 ,inv_model_attribute_column
4289 FROM
4290 jmf_gta_system_parameters
4291 WHERE
4292 org_id=l_org_id;
4293
4294
4295 l_noreference g_noreference_tbl;
4296
4297 l_noreference_idx NUMBER;
4298
4299 CURSOR c_tax_name IS
4300 SELECT
4301 decode(l_tax_name_column
4302 ,'ATTRIBUTE1'
4303 ,ATTRIBUTE1
4304 ,'ATTRIBUTE2'
4305 ,ATTRIBUTE2
4306 ,'ATTRIBUTE3'
4307 ,ATTRIBUTE3
4308 ,'ATTRIBUTE4'
4309 ,ATTRIBUTE4
4310 ,'ATTRIBUTE5'
4311 ,ATTRIBUTE5
4312 ,'ATTRIBUTE6'
4313 ,ATTRIBUTE6
4314 ,'ATTRIBUTE7'
4315 ,ATTRIBUTE7
4316 ,'ATTRIBUTE8'
4317 ,ATTRIBUTE8
4318 ,'ATTRIBUTE9'
4319 ,ATTRIBUTE9
4320 ,'ATTRIBUTE10'
4321 ,ATTRIBUTE10
4322 ,'ATTRIBUTE11'
4323 ,ATTRIBUTE11
4324 ,'ATTRIBUTE12'
4325 ,ATTRIBUTE12
4326 ,'ATTRIBUTE13'
4327 ,ATTRIBUTE13
4328 ,'ATTRIBUTE14'
4329 ,ATTRIBUTE14
4330 ,'ATTRIBUTE15'
4331 ,ATTRIBUTE15
4332 ,NULL
4333 )
4334 FROM
4335 mtl_system_items_b
4336 WHERE inventory_item_id=l_inventory_item_id
4337 AND organization_id=l_master_org_id
4338 AND attribute_category=l_item_attribute_category;
4339
4340 CURSOR c_item_model IS
4341 SELECT
4342
4343 decode(l_item_model_column
4344 ,'ATTRIBUTE1'
4345 ,ATTRIBUTE1
4346 ,'ATTRIBUTE2'
4347 ,ATTRIBUTE2
4348 ,'ATTRIBUTE3'
4349 ,ATTRIBUTE3
4350 ,'ATTRIBUTE4'
4351 ,ATTRIBUTE4
4352 ,'ATTRIBUTE5'
4353 ,ATTRIBUTE5
4354 ,'ATTRIBUTE6'
4355 ,ATTRIBUTE6
4356 ,'ATTRIBUTE7'
4357 ,ATTRIBUTE7
4358 ,'ATTRIBUTE8'
4359 ,ATTRIBUTE8
4360 ,'ATTRIBUTE9'
4361 ,ATTRIBUTE9
4362 ,'ATTRIBUTE10'
4363 ,ATTRIBUTE10
4364 ,'ATTRIBUTE11'
4365 ,ATTRIBUTE11
4366 ,'ATTRIBUTE12'
4367 ,ATTRIBUTE12
4368 ,'ATTRIBUTE13'
4369 ,ATTRIBUTE13
4370 ,'ATTRIBUTE14'
4371 ,ATTRIBUTE14
4372 ,'ATTRIBUTE15'
4373 ,ATTRIBUTE15
4374 ,NULL
4375 )
4376 FROM
4377 mtl_system_items_b
4378 WHERE inventory_item_id=l_inventory_item_id
4379 AND organization_id=l_master_org_id
4380 AND attribute_category=l_item_attribute_category;
4381
4382 l_item_length_exp g_item_tbl;
4383 l_item_length_exp_idx NUMBER;
4384
4385 l_export_item g_item_tbl;
4386 l_export_item_idx NUMBER;
4387 l_procedure_name VARCHAR2(50) :='Export_Items';
4388 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
4389 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
4390 l_nodatafound_msg VARCHAR2(1000);
4391
4392 BEGIN
4393
4394 --log for debug
4395
4396 IF( l_proc_level >= l_dbg_level )
4397 THEN
4398 FND_LOG.STRING(l_proc_level
4399 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
4400 ,'Enter procedure');
4401
4402 FND_LOG.STRING(l_proc_level
4403 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.parameters'
4404 ,'p_org_id '||p_org_id);
4405
4406 FND_LOG.STRING(l_proc_level
4407 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.parameters'
4408 ,'p_master_org_id '||p_master_org_id);
4409
4410 FND_LOG.STRING(l_proc_level
4411 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.parameters'
4412 ,'p_item_num_from '||p_item_num_from);
4413
4414 FND_LOG.STRING(l_proc_level
4415 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.parameters'
4416 ,'p_item_num_to '||p_item_num_to);
4417
4418 FND_LOG.STRING(l_proc_level
4419 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.parameters'
4420 ,'p_category_set_id '||p_category_set_id);
4421
4422 FND_LOG.STRING(l_proc_level
4423 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.parameters'
4424 ,'p_category_structure_id '||p_category_structure_id);
4425
4426 FND_LOG.STRING(l_proc_level
4427 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.parameters'
4428 ,'p_item_category_from '||p_item_category_from);
4429
4430 FND_LOG.STRING(l_proc_level
4431 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.parameters'
4432 ,'p_item_category_to '||p_item_category_to);
4433
4434 FND_LOG.STRING(l_proc_level
4435 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.parameters'
4436 ,'p_item_name_source '||p_item_name_source);
4437
4438 FND_LOG.STRING(l_proc_level
4439 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.parameters'
4440 ,'p_cross_reference_type '||p_cross_reference_type);
4441
4442 FND_LOG.STRING(l_proc_level
4443 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.parameters'
4444 ,'p_item_status '||p_item_status);
4445
4446 FND_LOG.STRING(l_proc_level
4447 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.parameters'
4448 ,'p_creation_date_from '||p_creation_date_from);
4449
4450 FND_LOG.STRING(l_proc_level
4451 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.parameters'
4452 ,'p_creation_date_to '||p_creation_date_to);
4453 END IF; --( l_proc_level >= l_dbg_level )
4454
4455
4456 --To convert date parameters to date type from varchar2 type
4457
4458 IF p_creation_date_from IS NOT NULL
4459 THEN
4460 l_creation_date_from:=FND_DATE.Canonical_To_Date(p_creation_date_from);
4461 END IF;
4462
4463 IF p_creation_date_to IS NOT NULL
4464 THEN
4465 l_creation_date_to:=FND_DATE.Canonical_To_Date(p_creation_date_to);
4466 END IF;
4467
4468
4469 l_export_item_count:=0;
4470 l_noreference_idx:=0;
4471 l_export_item_idx:=0;
4472 l_item_length_exp_idx:=0;
4473
4474 --To get DFF definition on items
4475 OPEN c_get_dff;
4476 FETCH c_get_dff INTO l_item_attribute_category
4477 ,l_tax_name_column
4478 ,l_item_model_column
4479 ;
4480
4481 CLOSE c_get_dff;
4482
4483 OPEN c_item;
4484 FETCH c_item INTO l_item;
4485 WHILE c_item%FOUND --Find item that accord with criteria by paramters
4486 LOOP
4487 l_export_item_count:=l_export_item_count+1;
4488 l_item_number:=l_item.item_number;
4489
4490 --Intial variables
4491 l_item_name:='';
4492 l_item_model:='';
4493 l_inventory_item_id:=l_item.inventory_item_id;
4494
4495 --To determine item name according to value of the parameter 'P_ITEM_NAME_SOURCE'
4496 IF (l_item_name_source='MASTER_ITEM') --regard item descripiton defined in Oracle EBS system as item name
4497 THEN
4498 l_item_name:=l_item.item_name;
4499 ELSIF (l_item_name_source='LATEST_ITEM_CROSS_REFERENCE')
4500 THEN
4501
4502 --Get description of lastest cross referece of the item
4503 OPEN c_reference_desc;
4504 FETCH c_reference_desc INTO l_item_name;
4505 CLOSE c_reference_desc;
4506 END IF; --(l_item_name_source='MASTER_ITEM')
4507
4508 --yawang plesae move the comment on top of this code, don't make coding line too long
4509 IF (l_item_name IS NULL) --Corss reference is not defined for this item,export this item as exception
4510 THEN
4511 l_noreference_idx:=l_noreference_idx+1;
4512 l_noreference(l_noreference_idx):=l_item_number;
4513 ELSE
4514 IF l_item_attribute_category IS NULL --Not setup item context code in system option form
4515 THEN
4516 l_tax_name:='';
4517 l_item_model:='';
4518 ELSE
4519
4520 --Get Tax domination
4521 OPEN c_tax_name;
4522 FETCH c_tax_name INTO l_tax_name;
4523 CLOSE c_tax_name;
4524
4525 --Get item model
4526 OPEN c_item_model;
4527 FETCH c_item_model INTO l_item_model;
4528 CLOSE c_item_model;
4529
4530 END IF; --l_item_attribute_category IS NULL
4531
4532 --Check whether the fields that will be exorted exceed length limitation
4533 IF check_item_length(l_item_number
4534
4535 ,l_item_name
4536 ,l_tax_name
4537 ,l_item_model
4538 ,l_item.uom
4539 )
4540 THEN
4541 l_export_item_idx:=l_export_item_idx+1;
4542 l_export_item(l_export_item_idx).item_number:=l_item_number;
4543 l_export_item(l_export_item_idx).item_name:=l_item_name;
4544 l_export_item(l_export_item_idx).tax_name:=l_tax_name;
4545 l_export_item(l_export_item_idx).item_model:=l_item_model;
4546 l_export_item(l_export_item_idx).uom:=l_item.uom;
4547 ELSE
4548 l_item_length_exp_idx:=l_item_length_exp_idx+1;
4549 l_item_length_exp(l_item_length_exp_idx).item_number:=l_item_number;
4550 l_item_length_exp(l_item_length_exp_idx).item_name:=l_item_name;
4551 l_item_length_exp(l_item_length_exp_idx).tax_name:=l_tax_name;
4552 l_item_length_exp(l_item_length_exp_idx).item_model:=l_item_model;
4553 l_item_length_exp(l_item_length_exp_idx).uom:=l_item.uom;
4554 END IF; --check_item_length(l_item_number,l_item_name,l_tax_name,l_item_model,l_item.uom)
4555 END IF;--(l_item_name IS NULL)
4556
4557 FETCH c_item INTO l_item;
4558
4559 END LOOP;--c_item%FOUND
4560
4561 --Export item to flat file
4562 IF l_export_item_count=0
4563 THEN --no data found
4564 FND_MESSAGE.Set_Name('JMF','JMF_GTA_NO_DATA_FOUND');
4565 l_nodatafound_msg:='//'||FND_MESSAGE.Get;
4566 FND_FILE.Put_Line(FND_FILE.Output
4567 ,l_nodatafound_msg
4568 );
4569 ELSIF l_export_item_count>0
4570 THEN
4571 Export_To_Flat_File(p_noreference => l_noreference
4572 ,p_export_item => l_export_item
4573 ,p_item_length_exp => l_item_length_exp
4574 );
4575 END IF; --l_export_item_count=0
4576 IF( l_proc_level >= l_dbg_level )
4577 THEN
4578 FND_LOG.String(l_proc_level
4579 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
4580 ,'Exit procedure');
4581 END IF; --( l_proc_level >= l_dbg_level )
4582
4583 EXCEPTION
4584 WHEN OTHERS THEN
4585 IF(l_proc_level >= l_dbg_level)
4586 THEN
4587 FND_LOG.string( l_proc_level
4588 , G_MODULE_PREFIX || l_procedure_name || '.OTHER_EXCEPTION '
4589 , SQLCODE||':'||SQLERRM);
4590 END IF;--(l_proc_level >= l_dbg_level)
4591 END Export_items;
4592
4593 END JMF_GTA_TXT_OPERATOR_PROC;
4594