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