1 PACKAGE BODY AR_GTA_TRX_UTIL AS
2 --$Header: ARGUGTAB.pls 120.5.12020000.4 2013/01/17 03:13:23 jixuhuan ship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation
5 --| Redwood Shores, California, USA
6 --| All rights reserved.
7 --+===========================================================================
8 --|
9 --| FILENAME :
10 --| ARUGTAB.pls
11 --|
12 --| DESCRIPTION:
13 --| This package is a collection of the util procedure
14 --| or function.
15 --|
16 --| PROCEDURE LIST
17 --| PROCEDURE Output_Conc
18 --| PROCEDURE Create_Trxs
19 --| PROCEDURE Create_Trx
20 --| FUNCTION Get_Gtainvoice_Amount
21 --| FUNCTION Get_Gtainvoice_Original_Amount
22 --| PROCEDURE Delete_Header_Line_Cascade
23 --| FUNCTION Get_Gtainvoice_Tax_Amount
24 --| FUNCTION Check_Taxcount_Of_Arline
25 --| FUNCTION Check_Taxcount_Of_Artrx
26 --| FUNCTION Get_Arinvoice_Amount
27 --| FUNCTION Get_Arinvoice_Tax_Amount
28 --| FUNCTION Format_Date
29 --| FUNCTION Get_Primary_Phone_Number
30 --| FUNCTION Get_Operatingunit
31 --| FUNCTION Get_Customer_Name
32 --| FUNCTION Get_Arline_Amount
33 --| FUNCTION Get_Arline_Vattax_Amount
34 --| FUNCTION Get_Arline_Vattax_Rate
35 --| PROCEDURE Get_Bank_Info
36 --| PROCEDURE Verify_Tax_Line
37 --| PROCEDURE Get_Info_From_Ebtax
38 --| PROCEDURE Get_Tp_Tax_Registration_Number
39 --| FUNCTION Get_Arline_Tp_Taxreg_Number
40 --| PROCEDURE Debug_Output
41 --| FUNCTION Get_AR_Batch_Source_Name
42 --| FUNCTION To_Xsd_Date_String
43 --| FUNCTION Format_Monetary_Amount
44 --| FUNCTION Get_Invoice_Type --added by subba for R12.1
45 --| PROCEDURE Populate_Invoice_Type
46 --| PROCEDURE Populate_Invoice_Type_Header
47 --|
48 --|
49 --| HISTORY:
50 --| 20-APR-2005: Jim Zheng Created
51 --|
52 --| 22-Aug-2005: Jim Zheng Modify: New feature about registration
53 --| Number
54 --|
55 --| 11-Oct-2005: Jim Zheng Modify: modify some select tax_line_id code
56 --| in get_info_from_ebtax
57 --| add where entity_code = 'TRANSACTONS'.
58 --|
59 --| 13-OCt-2005: Jim Zheng Modify: modify the parametere of
60 --| get_tp_tax_registration. remove the
61 --| input para p_trx_line_id, add a new
62 --\ input parameter p_tax_line_id
63 --| add a new procedure verify_tax_line.
64 --| add a new procedure debug_output
65 --| 19-Oct-2005: Jim Zheng Modify: update the procedure
66 --| get_info_from_ebtax, add a output
67 --| parameter
68 --| x_taxable_amount_org for get original
69 --| currency amount.
70 --| 20-Oct-2005: Jim Zheng Modify: Add a procedure debug_output_conc for
71 --| dubug report. remove the hard code
72 --| for fp_registration_number
73 --| in get_info_from_ebtax
74 --| Add tax_rate/100 in output value
75 --| in get_info_from_ebtax
76 --| 24-Nov-2005 Donghai Wang Modify procedure 'Get_Arline_Amount'
77 --| to add a new parameter
78 --| and use real code to replace dummy code
79 --| 24-Nov-2005 Donghai Wang Add a new parameter for function
80 --| 'Get_Arline_Vattax_Amount'
81 --| 24-Nov-2005 Donghai Wang Add a new parameter for function
82 --| 'Get_Arline_Vattax_Rate'
83 --| 25-Nov-2005 Donghai Wang Add a new function
84 --| Get_Arline_Tp_Taxreg_Number
85 --| 25-Nov-2005 Donghai Wang Add a new function
86 --| 'Check_Taxcount_Of_Arline'
87 --| 25-Nov-2005 Donghai Wang Add a new function
88 --| 'Check_Taxcount_Of_Artrx'
89 --| 25-Nov-2005 Donghai Wang update function 'Get_Arinvoice_Amount'
90 --| to follow ebtax logic
91 --| 25-Nov-2005 Donghai Wang update functon
92 --| 'Get_Arinvoice_Tax_Amount'
93 --| to follow ebtax logic
94 --| 28-Nov-2005 Jim Zheng remove the default value of
95 --| fp regi number, procedure
96 --| get_info_from_ebtax
97 --| 28-Nov-2005 Jim Zheng remove the default value of return
98 --| status of procedure get_info_from_ebtax
99 --| 28-Nov-2005 Jim Zheng add GTA currency code when get tax line
100 --| in procedure verify_tax_line.
101 --| 01-DEC-2005 Qiang Li add a new function Get_AR_Batch_Source_Name
102 --| 29-JUN-2006 Shujuan Yan In Get_Info_From_Ebtax, Add a output
103 --| parameter x_tax_curr_unit_price to
104 --| store the unit price of tax currency
105 --| for bug 5168900
106 --| 14-Sep-2006 Donghai Wang Added the new function
107 --| To_Xsd_Date_String to convert date
108 --| values into XSD format so that they can
109 --| be formatted correctly in XML Publisher
110 --| Reports for bug 5521629.
111 --| 20-Sep-2006 Donghai Wang Added the new function
112 -- Fomrat_Monetary_Amount
113 --| 28-Dec-2007 Subba Added new function Get_Invoice_Type for R12.1
114 --| 23-Jan-2008 Subba Modified code of Get_invoice_Type
115 --| 13-Feb-2009 Yao Zhang Fix bug 8234250,Modifiy bank information getting logic
116 --| for Credit Memo. Add new function get_cm_bank_info to
117 --| get bank info for credit memos which is created by crediting invoice.
118 --| 13-May-2009 Yao Zhang Fix bug#5604079 FOR FOREIGN CURR. TRXN, DISCREPANCY SHOWN DUE TO CURR.
119 --| ROUNDING ISSU
120 --| 16-Jun-2009 Yao Zhang Modified for bug#8605196
121 --| ER1 Support discount lines:added parameter for insert_row method to support discount line
122 --| ER2 Support customer name,address,bank info in Chinese
123 --| 20-Jul-2009 Yao Zhang Add procedure get_trx for bug#8605196 to query gta trx from database
124 --| 16-Aug-2009 Allen Yang Add procedures Populate_Invoice_Type and
125 --| Populate_Invoice_Type_Header to do data migration
126 --| from R12.0 to R12.1.X
127 --| 26-Aug-2009 Allen Yang Modified procedure Populate_Invoice_Type_Header
128 --| for bug 8839141.
129 --| 12-Mar-2010 Yao Zhang Fix bug9369455 SPLITED AMOUNT IS NOT CORRECT FOR A USD INVOICE IN GTA
130 --| 24-Dec-2010 Qiong Liu Fix bug#10311408 AR TRANSACTION TAX TOTAL AMOUNT NOT EQUAL GTA VAT INVOICE TOTAL TAX AMOUNT
131 --| 11-Jan-2011 Qiong Liu Fix bug#10638369 INCLUSIVE TAX UNIT PRICE IS RE-CALCULATED AFTER CONSOLIDATION
132 --| 25-Jun-2011 Chuan Ling Fix bug#12664154 TST122.XB5.QA.ORIGINAL CURRENCY AMOUNT IS WRONG
133 --| 14-Mar-2012 Jianchao Chi Fix bug#13812077, AR transfer program end with error due to the conversion error
134 --| 21/08/2012 Yao zhang fix bug 14500600 GOLDEN TAX ADAPTOR CAPTURE VAT REGISTRATION FROM A INCORRECT PARTY
135 --| 05/09/2012 Yao Zhang Fix bug 14580597 TST1213：TRANSFER VAT INVOICE TO GTA WORKBENCH WITH ERROR
136 --| 24-Dec-2012 Jixun Huang Fix bug#16007984, Total Amount in VAT Invoice window should include Tax Amount
137 --| 27-Dec-2012 Jixun Huang Fix bug#16027677, ORIGNAL CURRENCY AMOUNT DISPLAYED ERROR IN GTA HEADER
138 --+===========================================================================+
139
140
141
142 --=============================================================
143 -- FUNCTION NAME:
144 --
145 -- get_invoice_type Public
146 --
147 -- DESCRIPTION:
148 --
149 -- This function is to get invoice type for a given customer_trx_id and -- tax registration number.
150
151 -- PARAMETERS:
152
153 -- In: p_org_id Business Unit identifier.
154 -- In: p_customer_trx_id AR transaction identifier.
155 -- In: p_tax_line_id AR transaction line identifier.
156 -- In: p_fp_tax_registration_num fisrt party registration number
157 -- Return: VARCHAR2
158 --
159 -- CHANGE HISTORY:
160 -- 28-Dec-2007 Subba Created.
161 -- 14-Mar-2012 Jianchao Chi Update for bug 13812077.
162 -- 05-Sep-2012 Yao Zhang Update for bug 14580597.
163 --=============================================================
164
165
166 FUNCTION get_invoice_type
167 (p_org_id IN NUMBER
168 ,p_customer_trx_id IN NUMBER
169 ,p_trx_line_id IN NUMBER
170 ,p_fp_tax_registration_num IN VARCHAR2
171 ) --Change the type of p_fp_tax_registration_num from NUMBER to VARCHAR2 for bug 13812077
172 RETURN VARCHAR2
173 IS
174 l_procedure_name VARCHAR2(30) := 'get_invoice_type';
175 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
176 l_proc_level NUMBER := fnd_log.LEVEL_PROCEDURE;
177 l_error_string VARCHAR2(1000);
178
179 l_invoice_type ar_gta_tax_limits_all.invoice_type%TYPE;
180 l_class_code hz_code_assignments.CLASS_CODE%TYPE;
181 l_document_subtype zx_lines_det_factors.DOCUMENT_SUB_TYPE%TYPE;
182
183 BEGIN
184
185 /*
186 SELECT
187 jgtla.invoice_type
188 INTO
189 l_invoice_type
190 FROM
191 ar_gta_tax_limits_all jgtla
192 ,ar_gta_type_mappings jgtm
193 ,ra_customer_trx_all rcta
194 WHERE rcta.customer_trx_id = p_customer_trx_id
195 AND rcta.cust_trx_type_id = jgtm.transaction_type_id
196 AND jgtm.limitation_id = jgtla.limitation_id
197 AND jgtla.fp_tax_registration_number = p_fp_tax_registration_num
198 AND jgtla.org_id = p_org_id;
199
200
201 RETURN(l_invoice_type);
202 */
203
204 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
205 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
206 G_MODULE_PREFIX || l_procedure_name,
207 'begin Procedure. ');
208 END IF;
209
210 --select tax classification from customer site level
211 -- Yao zhang modified for bug 14580597
212 BEGIN
213 SELECT HCA.CLASS_CODE
214 INTO l_class_code
215 FROM ra_customer_trx_all h,
216 hz_cust_site_uses_all hcsua,
217 zx_party_tax_profile ZPTP,
218 hz_code_assignments HCA,
219 HZ_CUST_ACCT_SITES_ALL HCASA
220 WHERE h.bill_to_site_use_id=hcsua.SITE_USE_ID
221 AND hcsua.cust_acct_site_id=HCASA.CUST_ACCT_SITE_ID
222 AND HCASA.PARTY_SITE_ID=ZPTP.PARTY_ID
223 AND ZPTP.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE'
224 --AND ZPTP.CUSTOMER_FLAG = 'Y'
225 AND ZPTP.PARTY_TAX_PROFILE_ID = HCA.OWNER_TABLE_ID
226 AND HCA.OWNER_TABLE_NAME = 'ZX_PARTY_TAX_PROFILE'
227 AND HCA.CLASS_CATEGORY = 'AR_GTA_TAXPAYER_TYPE'
228 AND SYSDATE BETWEEN HCA.START_DATE_ACTIVE AND NVL(HCA.END_DATE_ACTIVE,SYSDATE+1)
229 AND h.customer_trx_id = p_customer_trx_id;
230 EXCEPTION
231 WHEN NO_DATA_FOUND THEN
232 l_invoice_type := null;
233 END;
234 -- if there is no tax profile defined on site level, select tax classification from account level
235 IF l_class_code is null then
236 BEGIN
237 SELECT HCA.CLASS_CODE
238 INTO l_class_code
239 FROM ra_customer_trx_all h,
240 Hz_Parties RAC_BILL_PARTY,
241 Hz_Cust_Accounts RAC_BILL,
242 zx_party_tax_profile ZPTP,
243 hz_code_assignments HCA
244 WHERE h.bill_to_customer_id = RAC_BILL.CUST_ACCOUNT_ID
245 AND rac_bill.party_id = RAC_BILL_PARTY.Party_Id
246 AND ZPTP.PARTY_ID = RAC_BILL_PARTY.Party_Id
247 AND ZPTP.PARTY_TYPE_CODE = 'THIRD_PARTY'
248 --AND ZPTP.CUSTOMER_FLAG = 'Y'
249 AND ZPTP.PARTY_TAX_PROFILE_ID = HCA.OWNER_TABLE_ID
250 AND HCA.OWNER_TABLE_NAME = 'ZX_PARTY_TAX_PROFILE'
251 AND HCA.CLASS_CATEGORY = 'AR_GTA_TAXPAYER_TYPE'
252 AND SYSDATE BETWEEN HCA.START_DATE_ACTIVE AND NVL(HCA.END_DATE_ACTIVE,SYSDATE+1)
253 AND h.customer_trx_id = p_customer_trx_id;
254 EXCEPTION
255 WHEN no_data_found THEN
256 l_invoice_type := NULL;
257 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
258 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
259 ,G_MODULE_PREFIX || l_procedure_name
260 , 'tax classification is not defined at any level.');
261 END IF;
262 --RAISE;--Yao zhang Modified for bug 14580597
263 END;
264 END IF;
265
266 IF l_class_code = 'AR_GTA_GENERAL_TAXPAYER' THEN
267 --check if document type specifis the invoice as common invoice.
268 SELECT z.DOCUMENT_SUB_TYPE
269 INTO l_document_subtype
270 FROM zx_lines_det_factors z
271 WHERE z.TRX_ID = p_customer_trx_id
272 AND z.TRX_LINE_ID = p_trx_line_id;
273 IF l_document_subtype = 'AR_GTA_COM_VAT_INV' THEN
274 l_invoice_type := '2'; -- 'COMMON';
275 ELSE
276 l_invoice_type := '0'; -- 'SPECIAL';
277 END IF;
278 ELSIF l_class_code = 'AR_GTA_SMALL_SCALE_TAXPAYER' THEN
279 l_invoice_type := '2'; -- 'COMMON';
280 END IF;
281
282 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
283 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
284 G_MODULE_PREFIX || l_procedure_name,
285 'End Procedure. ');
286 END IF;
287
288 RETURN(l_invoice_type);
289
290 EXCEPTION
291 WHEN OTHERS THEN
292 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
293 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
294 G_MODULE_PREFIX || l_procedure_name ||
295 '. OTHER_EXCEPTION',
296 SQLCODE || SQLERRM);
297 END IF;
298 RAISE;
299
300 END get_invoice_type;
301
302
303 --=============================================================================
304 -- PROCEDURE NAME:
305 -- log
306 -- TYPE:
307 -- private
308 --
309 -- DESCRIPTION :
310 -- This procedure log message
311 -- PARAMETERS :
312 -- p_message IN VARCHAR2
313 --
314 -- HISTORY:
315 -- 10-MAY-2005 : Jim.Zheng Create
316 --=============================================================================
317 PROCEDURE log
318 (p_message IN VARCHAR2)
319 IS
320 BEGIN
321 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
322 THEN
323 fnd_log.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE
324 ,MODULE => g_module_prefix || '.Debug'
325 ,MESSAGE => p_message
326 );
327 END IF;
328 END log;
329 --==========================================================================
330 -- PROCEDURE NAME:
331 --
332 -- Output_Conc Public
333 --
334 -- DESCRIPTION:
335 --
336 -- This procedure write data to concurrent output file
337 -- the data can be longer than 4000
338 --
339 -- PARAMETERS:
340 -- In: p_clob the content which need output to concurrent output
341 --
342 --
343 -- DESIGN REFERENCES:
344 --
345 --
346 -- CHANGE HISTORY:
347 --
348 -- 30-APR-2005: qugen.hu Created.
349 -- 24-Aug-2006: Jogen.hu change from search '>' to '<'
350 --
351 --===========================================================================
352 PROCEDURE output_conc
353 (p_clob IN CLOB)
354 IS/*
355 max_linesize NUMBER := 254;
356 l_pos_tag NUMBER;
357 l_pos NUMBER;
358 l_len NUMBER;
359 l_tmp NUMBER;
360 l_tmp1 NUMBER;
361 l_substr CLOB;
362 BEGIN
363 NULL;
364 --initalize
365 l_pos := 1;
366 l_len := length(p_clob);
367
368 WHILE l_pos <= l_len
369 LOOP
370 --get the XML tag from reverse direction
371 l_tmp := l_pos + max_linesize - 2 - l_len;
372 l_pos_tag := instr(p_clob
373 ,'>'
374 ,l_tmp);
375
376 --the pos didnot touch the end of string
377 l_tmp1 := l_pos - 1;
378
379 IF (l_pos_tag > l_tmp1)
380 AND (l_tmp < 0)
381 THEN
382 l_tmp := l_pos_tag - l_pos + 1;
383 fnd_file.put(fnd_file.output
384 ,substr(p_clob
385 ,l_pos
386 ,l_tmp));
387 l_pos := l_pos_tag + 1;
388 ELSE
389 l_substr := substr(p_clob
390 ,l_pos);
391 fnd_file.put(fnd_file.output
392 ,l_substr);
393 l_pos := l_len + 1;
394
395 END IF;
396
397 END LOOP;*/
398 --initalize
399 l_pos1 NUMBER; --position for '</'
400 l_pos2 NUMBER; --position for '>' follow '</'
401 l_pos3 NUMBER; --position for '/>'
402 l_pos NUMBER; --latest starting postion
403 l_len NUMBER;
404 l_prepos NUMBER;
405
406 BEGIN
407 --initalize
408 l_pos := 1;
409 l_len := length(p_clob);
410
411 WHILE TRUE
412 LOOP
413 l_prepos:=l_pos;
414
415 l_pos1:=instr(p_clob,'</',l_prepos);
416 IF l_pos1>0 THEN
417 l_pos2:=instr(p_clob,'>',l_pos1);
418 ELSE
419 l_pos2:=0;
420 END IF;
421
422 l_pos3:=instr(p_clob,'/>',l_prepos);
423
424 IF l_pos2>0 AND l_pos3> 0 THEN
425 IF l_pos2>l_pos3 THEN
426 l_pos:=l_pos3+2;
427 ELSE
428 l_pos:=l_pos2+1;
429 END IF;
430 ELSIF l_pos2>0 THEN
431 l_pos:=l_pos2+1;
432 ELSE
433 l_pos:=l_pos3+2;
434 END IF;
435
436 IF l_pos>2 THEN
437 FND_FILE.Put_Line(FND_FILE.Output
438 ,substr(p_clob
439 ,l_prepos
440 ,l_pos - l_prepos
441 )
442 );
443 ELSE
444 FND_FILE.Put_Line(FND_FILE.Log
445 ,substr(p_clob
446 ,l_prepos
447 )
448 );
449 EXIT;
450 END IF;
451 END LOOP;
452 EXCEPTION
453 WHEN OTHERS THEN
454 RAISE;
455 END output_conc;
456
457 --==========================================================================
458 -- PROCEDURE NAME:
459 --
460 -- debug_output_conc Public
461 --
462 -- DESCRIPTION:
463 --
464 -- This procedure write data to concurrent output file
465 -- the data can be longer than 4000
466 --
467 -- PARAMETERS:
468 -- In: p_clob the content which need output to concurrent output
469 --
470 --
471 -- DESIGN REFERENCES:
472 --
473 --
474 -- CHANGE HISTORY:
475 --
476 -- 30-APR-2005: Jim.zheng Created.
477 --
478 --===========================================================================
479 PROCEDURE debug_output_conc
480 (p_clob IN CLOB)
481 IS
482 max_linesize NUMBER := 254;
483 l_pos_tag NUMBER;
484 l_pos NUMBER;
485 l_len NUMBER;
486 l_tmp NUMBER;
487 l_tmp1 NUMBER;
488 l_substr CLOB;
489 BEGIN
490 NULL;
491 --initalize
492 l_pos := 1;
493 l_len := length(p_clob);
494
495 WHILE l_pos <= l_len
496 LOOP
497 --get the XML tag from reverse direction
498 l_tmp := l_pos + max_linesize - 2 - l_len;
499 l_pos_tag := instr(p_clob
500 ,'>'
501 ,l_tmp);
502
503 --the pos didnot touch the end of string
504 l_tmp1 := l_pos - 1;
505
506 IF (l_pos_tag > l_tmp1)
507 AND (l_tmp < 0)
508 THEN
509 l_tmp := l_pos_tag - l_pos + 1;
510 log(substr(p_clob,l_pos,l_tmp));
511 l_pos := l_pos_tag + 1;
512 ELSE
513 l_substr := substr(p_clob
514 ,l_pos);
515 log(l_substr);
516 l_pos := l_len + 1;
517
518 END IF;
519
520 END LOOP;
521 EXCEPTION
522 WHEN OTHERS THEN
523 NULL;
524 END debug_output_conc;
525
526 --==========================================================================
527 -- PROCEDURE NAME:
528 --
529 -- Create_Trxs Public
530 --
531 -- DESCRIPTION:
532 --
533 -- This package can insert a set of trx to AR_GTA_TRX_HEADS_ALL
534 -- AND AR_GTA_TRX_LINES_ALL.
535 --
536 -- PARAMETERS:
537 -- In: p_gta_trxs trx_tbl_type
538 --
539 --
540 -- DESIGN REFERENCES:
541 -- GTA-TRANSFER-PROGRAM-TD.doc
542 --
543 -- CHANGE HISTORY:
544 --
545 -- 30-APR-2005: Jim Zheng Created.
546 --
547 --===========================================================================
548 PROCEDURE create_trxs
549 (p_gta_trxs IN trx_tbl_type)
550 IS
551 l_procedure_name VARCHAR2(30) := 'create_TRXs';
552 l_gta_trx_tbl ar_gta_trx_util.trx_tbl_type;
553 l_index NUMBER;
554
555 BEGIN
556 fnd_file.put_line(FND_FILE.LOG,
557 g_module_prefix || l_procedure_name ||
558 '----001 ');
559 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
560 THEN
561 fnd_log.STRING(fnd_log.level_procedure
562 ,g_module_prefix || l_procedure_name
563 ,'Begin Procedure. ');
564 END IF;
565 -- begin log
566 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
567 THEN
568 log( 'begin create_trxs '||p_gta_trxs.COUNT);
569 END IF;
570 -- end log
571 l_gta_trx_tbl := p_gta_trxs;
572
573 -- loop by l_gta_trx_tbl, insert trx
574 l_index := l_gta_trx_tbl.FIRST;
575
576 WHILE l_index IS NOT NULL
577 LOOP
578 create_trx(l_gta_trx_tbl(l_index));
579 l_index := l_gta_trx_tbl.NEXT(l_index);
580
581 END LOOP;
582
583 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
584 THEN
585 fnd_log.STRING(fnd_log.level_procedure
586 ,g_module_prefix || l_procedure_name
587 ,'End Procedure. ');
588 END IF;
589 fnd_file.put_line(FND_FILE.LOG,
590 g_module_prefix || l_procedure_name ||
591 '----002 ');
592
593 EXCEPTION
594 WHEN OTHERS THEN
595 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
596 THEN
597 fnd_log.STRING(fnd_log.level_unexpected
598 ,g_module_prefix || l_procedure_name ||
599 '. OTHER_EXCEPTION '
600 ,SQLCODE || SQLERRM);
601 END IF;
602 RAISE;
603
604 END create_trxs;
605
606 --==========================================================================
607 -- PROCEDURE NAME:
608 --
609 -- Create_Trx Public
610 --
611 -- DESCRIPTION:
612 --
613 -- This procedure is to insert a GTA transaction
614 --
615 -- PARAMETERS:
616 -- In: p_gta_trx Standard API parameter
617 --
618 --
619 -- DESIGN REFERENCES:
620 -- GTA-TRANSFER-PROGRAM-TD.doc
621 --
622 -- CHANGE HISTORY:
623 --
624 -- 30-APR-2005: Jim Zheng Created.
625 -- 03-JAN-2008: Subba added parameter for insert_row method calling
626 -- 16-Jun-2009: Yao Zhang Modified for bug#8605196
627 -- added parameter for insert_row method to support discount line
628 -- 20-Jul-2009:Yao Zhang Modified for bug#8605196 ER3 consolidate invoice
629 -- 27-Dec-2012:Jixun Modified for bug#16027677 ORIGNAL CURRENCY AMOUNT DISPLAYED ERROR IN GTA HEADER
630 --===========================================================================
631 PROCEDURE create_trx
632 (p_gta_trx IN trx_rec_type)
633 IS
634
635 header_row_id VARCHAR2(30);
636 line_row_id VARCHAR2(30);
637 l_procedure_name VARCHAR2(30) := 'create_Trx';
638 l_count NUMBER;
639
640 BEGIN
641
642 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
643 THEN
644 fnd_log.STRING(fnd_log.level_procedure
645 ,g_module_prefix || l_procedure_name
646 ,'Begin Procedure. ');
647 END IF;
648
649 -- begin log
650 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
651 THEN
652 log('begin create_trx '||p_gta_trx.trx_header.ra_trx_id);
653 END IF;
654 -- end log
655
656 -- insert header
657 ar_gta_trx_headers_all_pkg.insert_row
658 (p_row_id => header_row_id
659 ,p_ra_gl_date => p_gta_trx.trx_header.ra_gl_date
660 ,p_ra_gl_period => p_gta_trx.trx_header.ra_gl_period
661 ,p_set_of_books_id => p_gta_trx.trx_header.set_of_books_id
662 ,p_bill_to_customer_id => p_gta_trx.trx_header.bill_to_customer_id
663 ,p_bill_to_customer_number => p_gta_trx.trx_header.bill_to_customer_number
664 ,p_bill_to_customer_name => p_gta_trx.trx_header.bill_to_customer_name
665 ,p_source => p_gta_trx.trx_header.SOURCE
666 ,p_org_id => p_gta_trx.trx_header.org_id
667 ,p_rule_header_id => p_gta_trx.trx_header.rule_header_id
668 ,p_gta_trx_header_id => p_gta_trx.trx_header.gta_trx_header_id
669 ,p_gta_trx_number => p_gta_trx.trx_header.gta_trx_number
670 ,p_group_number => p_gta_trx.trx_header.group_number
671 ,p_version => p_gta_trx.trx_header.version
672 ,p_latest_version_flag => p_gta_trx.trx_header.latest_version_flag
673 ,p_transaction_date => p_gta_trx.trx_header.transaction_date
674 ,p_ra_trx_id => p_gta_trx.trx_header.ra_trx_id
675 ,p_ra_trx_number => p_gta_trx.trx_header.ra_trx_number
676 ,p_description => p_gta_trx.trx_header.description
677 ,p_customer_address => p_gta_trx.trx_header.customer_address
678 ,p_customer_phone => p_gta_trx.trx_header.customer_phone
679 ,p_customer_address_phone => p_gta_trx.trx_header.customer_address_phone
680 ,p_bank_account_name => p_gta_trx.trx_header.bank_account_name
681 ,p_bank_account_number => p_gta_trx.trx_header.bank_account_number
682 ,p_bank_account_name_number => p_gta_trx.trx_header.bank_account_name_number
683 ,p_fp_tax_registration_number => p_gta_trx.trx_header.fp_tax_registration_number -- fp registration number
684 ,p_tp_tax_registration_number => p_gta_trx.trx_header.tp_tax_registration_number -- tp registration number
685 ,p_legal_entity_id => p_gta_trx.trx_header.legal_entity_id -- legal entity id
686 ,p_ra_currency_code => p_gta_trx.trx_header.ra_currency_code
687 ,p_conversion_type => p_gta_trx.trx_header.conversion_type
688 ,p_conversion_date => p_gta_trx.trx_header.conversion_date
689 ,p_conversion_rate => p_gta_trx.trx_header.conversion_rate
690 ,p_gta_batch_number => p_gta_trx.trx_header.gta_batch_number
691 ,p_gt_invoice_number => p_gta_trx.trx_header.gt_invoice_number
692 ,p_gt_invoice_date => p_gta_trx.trx_header.gt_invoice_date
693 ,p_gt_invoice_net_amount => p_gta_trx.trx_header.gt_invoice_net_amount
694 ,p_gt_invoice_tax_amount => p_gta_trx.trx_header.gt_invoice_tax_amount
695 ,p_status => p_gta_trx.trx_header.status
696 ,p_sales_list_flag => p_gta_trx.trx_header.sales_list_flag
697 ,p_cancel_flag => p_gta_trx.trx_header.cancel_flag
698 ,p_gt_invoice_type => p_gta_trx.trx_header.gt_invoice_type
699 ,p_gt_invoice_class => p_gta_trx.trx_header.gt_invoice_class
700 ,p_gt_tax_month => p_gta_trx.trx_header.gt_tax_month
701 ,p_issuer_name => p_gta_trx.trx_header.issuer_name
702 ,p_reviewer_name => p_gta_trx.trx_header.reviewer_name
703 ,p_payee_name => p_gta_trx.trx_header.payee_name
704 ,p_tax_code => p_gta_trx.trx_header.tax_code
705 ,p_tax_rate => p_gta_trx.trx_header.tax_rate
706 ,p_generator_id => p_gta_trx.trx_header.generator_id
707 ,p_export_request_id => p_gta_trx.trx_header.export_request_id
708 ,p_request_id => p_gta_trx.trx_header.request_id
709 ,p_program_application_id => p_gta_trx.trx_header.program_application_id
710 ,p_program_id => p_gta_trx.trx_header.program_id
711 ,p_program_update_date => p_gta_trx.trx_header.program_update_date
712 ,p_attribute_category => p_gta_trx.trx_header.attribute_category
713 ,p_attribute1 => p_gta_trx.trx_header.attribute1
714 ,p_attribute2 => p_gta_trx.trx_header.attribute2
715 ,p_attribute3 => p_gta_trx.trx_header.attribute3
716 ,p_attribute4 => p_gta_trx.trx_header.attribute4
717 ,p_attribute5 => p_gta_trx.trx_header.attribute5
718 ,p_attribute6 => p_gta_trx.trx_header.attribute6
719 ,p_attribute7 => p_gta_trx.trx_header.attribute7
720 ,p_attribute8 => p_gta_trx.trx_header.attribute8
721 ,p_attribute9 => p_gta_trx.trx_header.attribute9
722 ,p_attribute10 => p_gta_trx.trx_header.attribute10
723 ,p_attribute11 => p_gta_trx.trx_header.attribute11
724 ,p_attribute12 => p_gta_trx.trx_header.attribute12
725 ,p_attribute13 => p_gta_trx.trx_header.attribute13
726 ,p_attribute14 => p_gta_trx.trx_header.attribute14
727 ,p_attribute15 => p_gta_trx.trx_header.attribute15
728 ,p_creation_date => p_gta_trx.trx_header.creation_date
729 ,p_created_by => p_gta_trx.trx_header.created_by
730 ,p_last_update_date => p_gta_trx.trx_header.last_update_date
731 ,p_last_updated_by => p_gta_trx.trx_header.last_updated_by
732 ,p_last_update_login => p_gta_trx.trx_header.last_update_login
733 ,p_invoice_type => p_gta_trx.trx_header.invoice_type
734 --Yao Zhang add begin for bug#8605196 ER3 consolidate invoice
735 ,p_consolidation_flag => p_gta_trx.trx_header.consolidation_flag
736 ,p_consolidation_id => p_gta_trx.trx_header.consolidation_id
737 ,p_consolidation_trx_num => p_gta_trx.trx_header.consolidation_trx_num
738 --Yao Zhang add end for bug#8605196 ER3 consolidate invoice
739 );
740
741 -- insert rows
742 l_count := p_gta_trx.trx_lines.FIRST;
743 WHILE l_count IS NOT NULL
744 LOOP
745 -- begin log
746 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
747 THEN
748 log( 'begin create_trx_line '||p_gta_trx.trx_lines(l_count).ar_trx_line_id);
749 END IF;
750 -- end log
751
752 ar_gta_trx_lines_all_pkg.insert_row
753 (p_rowid => line_row_id
754 ,p_org_id => p_gta_trx.trx_lines(l_count).org_id
755 ,p_gta_trx_header_id => p_gta_trx.trx_lines(l_count).gta_trx_header_id
756 ,p_gta_trx_line_id => p_gta_trx.trx_lines(l_count).gta_trx_line_id
757 ,p_matched_flag => p_gta_trx.trx_lines(l_count).matched_flag
758 ,p_line_number => p_gta_trx.trx_lines(l_count).line_number
759 ,p_ar_trx_line_id => p_gta_trx.trx_lines(l_count).ar_trx_line_id
760 ,p_inventory_item_id => p_gta_trx.trx_lines(l_count).inventory_item_id
761 ,p_item_number => p_gta_trx.trx_lines(l_count).item_number
762 ,p_item_description => p_gta_trx.trx_lines(l_count).item_description
763 ,p_item_model => p_gta_trx.trx_lines(l_count).item_model
764 ,p_item_tax_denomination => p_gta_trx.trx_lines(l_count).item_tax_denomination
765 ,p_tax_rate => p_gta_trx.trx_lines(l_count).tax_rate
766 ,p_uom => p_gta_trx.trx_lines(l_count).uom
767 ,p_uom_name => p_gta_trx.trx_lines(l_count).uom_name
768 ,p_quantity => p_gta_trx.trx_lines(l_count).quantity
769 ,p_price_flag => p_gta_trx.trx_lines(l_count).price_flag
770 ,p_unit_price => p_gta_trx.trx_lines(l_count).unit_price
771 ,p_unit_tax_price => p_gta_trx.trx_lines(l_count).unit_tax_price
772 ,p_amount => p_gta_trx.trx_lines(l_count).amount
773 --modified by Jixun for bug#16027677 begin
774 ,p_original_currency_amount => p_gta_trx.trx_lines(l_count).original_currency_amount
775 --,p_original_currency_amount => round(p_gta_trx.trx_lines(l_count).original_currency_amount/nvl(p_gta_trx.trx_header.conversion_rate,1),2)--added by shaoclbj for bug 12664154
776 --modified by Jixun for bug#16027677 end
777 ,p_tax_amount => p_gta_trx.trx_lines(l_count).tax_amount
778 ,p_discount_flag => p_gta_trx.trx_lines(l_count).discount_flag
779 ,p_enabled_flag => p_gta_trx.trx_lines(l_count).enabled_flag
780 ,p_request_id => p_gta_trx.trx_lines(l_count).request_id
781 ,p_program_application_id => p_gta_trx.trx_lines(l_count).program_applicaton_id
782 ,p_program_id => p_gta_trx.trx_lines(l_count).program_id
783 ,p_program_update_date => p_gta_trx.trx_lines(l_count).program_update_date
784 ,p_attribute_category => p_gta_trx.trx_lines(l_count).attribute_category
785 ,p_attribute1 => p_gta_trx.trx_lines(l_count).attribute1
786 ,p_attribute2 => p_gta_trx.trx_lines(l_count).attribute2
787 ,p_attribute3 => p_gta_trx.trx_lines(l_count).attribute3
788 ,p_attribute4 => p_gta_trx.trx_lines(l_count).attribute4
789 ,p_attribute5 => p_gta_trx.trx_lines(l_count).attribute5
790 ,p_attribute6 => p_gta_trx.trx_lines(l_count).attribute6
791 ,p_attribute7 => p_gta_trx.trx_lines(l_count).attribute7
792 ,p_attribute8 => p_gta_trx.trx_lines(l_count).attribute8
793 ,p_attribute9 => p_gta_trx.trx_lines(l_count).attribute9
794 ,p_attribute10 => p_gta_trx.trx_lines(l_count).attribute10
795 ,p_attribute11 => p_gta_trx.trx_lines(l_count).attribute11
796 ,p_attribute12 => p_gta_trx.trx_lines(l_count).attribute12
797 ,p_attribute13 => p_gta_trx.trx_lines(l_count).attribute13
798 ,p_attribute14 => p_gta_trx.trx_lines(l_count).attribute14
799 ,p_attribute15 => p_gta_trx.trx_lines(l_count).attribute15
800 ,p_creation_date => p_gta_trx.trx_lines(l_count).creation_date
801 ,p_created_by => p_gta_trx.trx_lines(l_count).created_by
802 ,p_last_update_date => p_gta_trx.trx_lines(l_count).last_update_date
803 ,p_last_updated_by => p_gta_trx.trx_lines(l_count).last_updated_by
804 ,p_last_update_login => p_gta_trx.trx_lines(l_count).last_update_login
805 --Yao Zhang add for bug#8605196 to support discount line
806 ,p_discount_amount => p_gta_trx.trx_lines(l_count).discount_amount
807 ,p_discount_tax_amount => p_gta_trx.trx_lines(l_count).discount_tax_amount
808 ,p_discount_rate => p_gta_trx.trx_lines(l_count).discount_rate
809 );
810
811 l_count := p_gta_trx.trx_lines.NEXT(l_count);
812 END LOOP;
813
814 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
815 THEN
816 fnd_log.STRING(fnd_log.level_procedure
817 ,g_module_prefix || l_procedure_name
818 ,'End Procedure. ');
819 END IF;
820
821 EXCEPTION
822 WHEN dup_val_on_index THEN
823 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
824 THEN
825 fnd_log.STRING(fnd_log.level_unexpected
826 ,g_module_prefix || l_procedure_name ||
827 '. dup_val_on_index '
828 ,SQLCODE || SQLERRM);
829 END IF;
830
831 WHEN OTHERS THEN
832 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
833 THEN
834 fnd_log.STRING(fnd_log.level_unexpected
835 ,g_module_prefix || l_procedure_name ||
836 '. OTHER_EXCEPTION '
837 ,'Exception occur when insert data into database' ||
838 SQLCODE || SQLERRM);
839
840 -- begin log
841 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
842 THEN
843 log( 'Exception occur when insert data into database' ||SQLCODE || SQLERRM);
844 END IF;
845 -- end log
846
847 END IF;
848 RAISE;
849
850 END create_trx;
851 --==========================================================================
852 -- PROCEDURE NAME:
853 --
854 -- Get_Trx Public
855 --
856 -- DESCRIPTION:
857 --
858 -- This procedure is to get GTA transaction by trx header id
859 --
860 -- PARAMETERS:
861 -- In: p_trx_header_id Identifier of GTA invoice header
862 -- Out: x_trx_rec Record to store gta transaction
863 --
864 -- DESIGN REFERENCES:
865 -- GTA_12.1.2_Technical_Design.doc
866 --
867 -- CHANGE HISTORY:
868 --
869 -- 30-Jun-2009: Yao Zhang Created.
870 --===========================================================================
871 PROCEDURE Get_Trx
872 (p_trx_header_id IN NUMBER
873 ,x_trx_rec OUT NOCOPY trx_rec_type
874 )
875 IS
876 l_procedure_name VARCHAR2(100) :='Get_Trx';
877 l_trx_rec trx_rec_type;
878 l_line_count NUMBER;
879 l_gta_trx_line_id ar_gta_trx_lines_all.gta_trx_line_id%TYPE;
880 CURSOR c_trx_lines(l_header_id IN NUMBER) IS
881 SELECT gta_trx_line_id
882 FROM ar_gta_trx_lines_all
883 WHERE gta_trx_header_id = l_header_id;
884
885 BEGIN
886 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
887 THEN
888 fnd_log.STRING(fnd_log.level_procedure
889 ,g_module_prefix || l_procedure_name
890 ,'Begin Procedure. ');
891 END IF;
892 --get trx header
893 l_trx_rec:=NULL;
894
895 AR_GTA_TRX_HEADERS_ALL_PKG.Query_Row
896 (p_header_id => p_trx_header_id
897 ,x_trx_header_rec => l_trx_rec.trx_header);
898 --init
899 l_trx_rec.trx_lines:=trx_line_tbl_type();
900 --get trx lines
901 OPEN c_trx_lines(p_trx_header_id);
902 LOOP
903 FETCH c_trx_lines INTO l_gta_trx_line_id;
904 EXIT WHEN c_trx_lines%NOTFOUND;
905
906 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
907 THEN
908 log('get trx_line_id: '||l_gta_trx_line_id);
909 END IF;
910 l_trx_rec.trx_lines.EXTEND;
911 AR_GTA_TRX_LINES_ALL_PKG.Query_Row
912 (p_trx_line_id =>l_gta_trx_line_id
913 ,x_trx_line_rec =>l_trx_rec.trx_lines(l_trx_rec.trx_lines.count));
914
915 END LOOP;
916 CLOSE c_trx_lines;
917 -- end log
918 x_trx_rec:=l_trx_rec;
919 --log for debug
920 IF(fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
921 THEN
922
923 FND_LOG.String(fnd_log.level_procedure
924 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
925 ,'Exit procedure'
926 );
927
928 END IF; --( l_proc_level >= l_dbg_level)
929
930 END Get_Trx;
931
932 --==========================================================================
933 -- FUNCTION NAME:
934 --
935 -- Get_Gtainvoice_Amount Public
936 --
937 -- DESCRIPTION:
938 --
939 -- This procedure is to calculate total amount of a GTA invoice
940 --
941 -- PARAMETERS:
942 -- In: p_header_id Identifier of GTA Invoice header
943 --
944 -- Return: NUMBER
945 --
946 -- DESIGN REFERENCES:
947 -- GTA-TRANSFER-PROGRAM-TD.doc
948 --
949 -- CHANGE HISTORY:
950 --
951 -- 30-APR-2005 Jim Zheng Created.
952 -- 04-AUG-2005 Donghai Wang modified query clause to remove
953 -- reference to price_flag
954 -- 06-Aug-2009 Yao Zhang modified for bug#8605196 to support discount line
955 -- 24-Dec-2012 Jixun Huang Modified for bug#16007984
956 --
957 --===========================================================================
958 FUNCTION Get_Gtainvoice_Amount(p_header_id IN NUMBER,
959 p_tax_flag IN VARCHAR2 DEFAULT 'N' --added by Jixun for bug#16007984
960 ) RETURN NUMBER IS
961 l_ret NUMBER;
962 BEGIN
963 --added by Jixun for bug#16007984 begin
964 IF p_tax_flag = 'Y' THEN
965 SELECT SUM(nvl(amount, 0) + nvl(tax_amount, 0) +
966 nvl(discount_amount, 0) + nvl(discount_tax_amount, 0))
967 INTO l_ret
968 FROM ar_gta_trx_lines_all
969 WHERE gta_trx_header_id = p_header_id
970 AND enabled_flag = 'Y';
971 ELSE
972 --added by Jixun for bug#16007984 end
973 SELECT
974 --SUM(nvl(amount,0))
975 SUM(nvl(amount, 0) + nvl(discount_amount, 0)) --Yao Modified for R12.1.2 to support discount line
976 INTO l_ret
977 FROM ar_gta_trx_lines_all
978 WHERE gta_trx_header_id = p_header_id
979 AND enabled_flag = 'Y';
980 END IF; --added by Jixun for bug#16007984
981
982 RETURN l_ret;
983 END Get_Gtainvoice_Amount;
984
985 --==========================================================================
986 -- FUNCTION NAME:
987 --
988 -- Get_Gtainvoice_Original_Amount Public
989 --
990 -- DESCRIPTION:
991 --
992 -- This procedure is to calculate total amount of a GTA invoice
993 -- in original currency code
994 --
995 -- PARAMETERS:
996 -- In: p_header_id Identifier of GTA Invoice header
997 --
998 -- Return: NUMBER
999 --
1000 -- DESIGN REFERENCES:
1001 -- GTA-TRANSFER-PROGRAM-TD.doc
1002 --
1003 -- CHANGE HISTORY:
1004 --
1005 -- 30-APR-2005: Jim Zheng Created.
1006 --
1007 --===========================================================================
1008 FUNCTION get_gtainvoice_original_amount
1009 (p_header_id IN NUMBER)
1010 RETURN NUMBER
1011 IS
1012 l_ret NUMBER;
1013 CURSOR c_original_amount IS
1014 SELECT
1015 SUM(nvl(original_currency_amount,0))
1016 FROM
1017 ar_gta_trx_lines_all
1018 WHERE gta_trx_header_id = p_header_id
1019 AND enabled_flag = 'Y';
1020 BEGIN
1021 OPEN c_original_amount;
1022 FETCH c_original_amount
1023 INTO l_ret;
1024 CLOSE c_original_amount;
1025
1026 RETURN(nvl(l_ret
1027 ,0));
1028 END get_gtainvoice_original_amount;
1029
1030 --==========================================================================
1031 -- PROCEDURE NAME:
1032 --
1033 -- Delete_Header_Line_Cascade Public
1034 --
1035 -- DESCRIPTION:
1036 --
1037 -- This procedure is to cascade delete a special GTA/GT
1038 -- invoice header with all lines associated with it
1039 --
1040 -- PARAMETERS:
1041 -- In: p_gta_trx_header_id GTA/GT invoice header identifier
1042 --
1043 -- DESIGN REFERENCES:
1044 -- GTA-PURGE-PROGRAM-TD.doc
1045 --
1046 -- CHANGE HISTORY:
1047 --
1048 -- 8-MAY-2005: Qiang Li Created
1049 --
1050 --===========================================================================
1051 PROCEDURE delete_header_line_cascade
1052 (p_gta_trx_header_id IN NUMBER)
1053 IS
1054 BEGIN
1055 --Delete lines
1056 DELETE ar_gta_trx_lines_all
1057 WHERE gta_trx_header_id = p_gta_trx_header_id;
1058
1059 --Delete Headers
1060 DELETE ar_gta_trx_headers_all
1061 WHERE gta_trx_header_id = p_gta_trx_header_id;
1062 END delete_header_line_cascade;
1063
1064 --==========================================================================
1065 -- FUNCTION NAME:
1066 --
1067 -- Get_Gtainvoice_Tax_Amount Public
1068 --
1069 -- DESCRIPTION:
1070 --
1071 -- This procedure Get Gtainvoice Tax Amount
1072 --
1073 -- PARAMETERS:
1074 -- In: p_header_id identifier of Gta Invoice
1075 --
1076 -- Return: NUMBER
1077 --
1078 -- DESIGN REFERENCES:
1079 -- GTA_Reports_TD.doc
1080 --
1081 -- CHANGE HISTORY:
1082 --
1083 -- 8-MAY-2005: Qiang Li Created
1084 -- 08-Aug-2009 Yao Zhang Modified for R12.1.2 to support discount line
1085 --===========================================================================
1086 FUNCTION get_gtainvoice_tax_amount
1087 (p_header_id IN NUMBER)
1088 RETURN NUMBER
1089 IS
1090 l_ret NUMBER;
1091 BEGIN
1092 SELECT --SUM(nvl(tax_amount,0))
1093 SUM(nvl(tax_amount,0)+nvl(discount_tax_amount,0))--Yao Modified for R12.1.2
1094 INTO l_ret
1095 FROM ar_gta_trx_lines
1096 WHERE gta_trx_header_id = p_header_id
1097 AND enabled_flag = 'Y';
1098 RETURN l_ret;
1099 END get_gtainvoice_tax_amount;
1100
1101 --==========================================================================
1102 -- FUNCTION NAME:
1103 --
1104 -- Check_Taxcount_Of_Arline Public
1105 --
1106 -- DESCRIPTION:
1107 --
1108 -- This function is used to check if one AR line has multiple tax line per
1109 -- Tax type and GT currency defined on GTA system option form.
1110 --
1111 -- PARAMETERS:
1112 -- In: p_org_id Identifier of operating unit
1113 -- p_customer_trx_line_id Identifier of transaction line id
1114 --
1115 -- Return: BOOLEAN
1116 --
1117 -- DESIGN REFERENCES:
1118 -- GTA_Reports_TD.doc
1119 --
1120 -- CHANGE HISTORY:
1121 --
1122 -- 25-Nov-2005: Donghai Wang Created
1123 --
1124 --===========================================================================
1125 FUNCTION Check_Taxcount_Of_Arline
1126 (p_org_id IN NUMBER
1127 ,p_customer_trx_line_id IN NUMBER
1128 )
1129 RETURN BOOLEAN
1130 IS
1131 l_tax_type_code zx_lines.tax_type_code%TYPE;
1132 l_taxline_count NUMBER;
1133 l_gt_currency_code fnd_currencies.currency_code%TYPE;
1134 l_trx_id ra_customer_trx_all.customer_trx_id%TYPE;--jogen bug5212702 May-17,2006
1135
1136 CURSOR c_tax_type_code
1137 IS
1138 SELECT
1139 vat_tax_type_code
1140 ,gt_currency_code
1141 FROM
1142 ar_gta_system_parameters_all
1143 WHERE org_id=p_org_id;
1144
1145 CURSOR c_taxline_count(pc_trx_id NUMBER)
1146 IS
1147 SELECT
1148 COUNT(*)
1149 FROM
1150 zx_lines
1151 WHERE trx_line_id=p_customer_trx_line_id
1152 AND entity_code='TRANSACTIONS'
1153 AND application_id = 222
1154 AND trx_level_type='LINE'
1155 AND tax_type_code=l_tax_type_code
1156 AND tax_currency_code=l_gt_currency_code
1157 AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--jogen bug5212702 May-17,2006
1158 AND trx_id=pc_trx_id; --jogen bug5212702 May-17,2006
1159
1160 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1161 l_proc_level NUMBER := fnd_log.level_procedure;
1162 l_procedure_name VARCHAR2(30) := 'Check_Taxcount_Of_Arline';
1163
1164 BEGIN
1165 --logging for debug
1166 IF (l_proc_level >= l_dbg_level)
1167 THEN
1168 fnd_log.STRING(l_proc_level
1169 ,g_module_prefix || l_procedure_name || '.begin'
1170 ,'Enter function');
1171 END IF; --l_proc_level>=l_dbg_level)
1172
1173
1174 --Get Vat tax type and GT currency coe defined in GTA system options form
1175 --for current operating unit
1176 OPEN c_tax_type_code;
1177 FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
1178 CLOSE c_tax_type_code;
1179
1180 --Get count of tax line for a AR line
1181
1182 ----jogen bug5212702 May-17,2006
1183 -- OPEN c_taxline_count;
1184 SELECT customer_trx_id
1185 INTO l_trx_id
1186 FROM ra_customer_trx_lines_all
1187 WHERE customer_trx_line_id=p_customer_trx_line_id;
1188
1189 OPEN c_taxline_count(l_trx_id);
1190 --jogen bug5212702 May-17,2006
1191
1192 FETCH c_taxline_count INTO l_taxline_count;
1193 CLOSE c_taxline_count;
1194
1195 --logging for debug
1196 IF (l_proc_level >= l_dbg_level)
1197 THEN
1198 fnd_log.STRING(l_proc_level
1199 ,g_module_prefix || l_procedure_name || '.End'
1200 ,'Exit function');
1201 END IF; --l_proc_level>=l_dbg_level)
1202
1203 IF l_taxline_count=1
1204 THEN
1205 RETURN(TRUE);
1206 ELSE
1207 RETURN(FALSE);
1208 END IF; --l_taxline_count=1
1209
1210 END Check_Taxcount_Of_Arline;
1211
1212
1213 --==========================================================================
1214 -- FUNCTION NAME:
1215 --
1216 -- Check_Taxcount_Of_Artrx Public
1217 --
1218 -- DESCRIPTION:
1219 --
1220 -- This function is used to check if AR lines belong to one AR transaction
1221 -- have multiple tax line per Tax type and GT currency defined on GTA system
1222 -- option form.
1223 --
1224 -- PARAMETERS:
1225 -- In: p_org_id Identifier of operating unit
1226 -- p_customer_trx_id Identifier of AR transaciton
1227 --
1228 -- Return: BOOLEAN
1229 --
1230 -- DESIGN REFERENCES:
1231 -- GTA_Reports_TD.doc
1232 --
1233 -- CHANGE HISTORY:
1234 --
1235 -- 25-Nov-2005: Donghai Wang Created
1236 --
1237 --===========================================================================
1238 FUNCTION Check_Taxcount_Of_Artrx
1239 (p_org_id IN NUMBER
1240 ,p_customer_trx_id IN NUMBER
1241 )
1242 RETURN BOOLEAN
1243 IS
1244 l_tax_type_code zx_lines.tax_type_code%TYPE;
1245 l_taxline_count NUMBER;
1246 l_gt_currency_code fnd_currencies.currency_code%TYPE;
1247
1248
1249
1250 CURSOR c_tax_type_code
1251 IS
1252 SELECT
1253 vat_tax_type_code
1254 ,gt_currency_code
1255 FROM
1256 ar_gta_system_parameters_all
1257 WHERE org_id=p_org_id;
1258
1259 CURSOR c_tax_line_count
1260 IS
1261 SELECT COUNT(*)
1262 FROM
1263 (SELECT
1264 trx_line_id
1265 ,COUNT(*)
1266 FROM
1267 zx_lines
1268 WHERE application_id = 222
1269 AND trx_id=p_customer_trx_id
1270 AND trx_level_type='LINE'
1271 AND entity_code='TRANSACTIONS'
1272 AND tax_type_code=l_tax_type_code
1273 AND tax_currency_code=l_gt_currency_code
1274 AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--jogen bug5212702 May-17,2006
1275 GROUP BY trx_line_id
1276 HAVING COUNT(*)>1);
1277
1278
1279
1280 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1281 l_proc_level NUMBER := fnd_log.level_procedure;
1282 l_procedure_name VARCHAR2(30) := 'Check_Taxcount_Of_Artrx';
1283
1284 BEGIN
1285 --logging for debug
1286 IF (l_proc_level >= l_dbg_level)
1287 THEN
1288 fnd_log.STRING(l_proc_level
1289 ,g_module_prefix || l_procedure_name || '.begin'
1290 ,'Enter function');
1291 END IF; --l_proc_level>=l_dbg_level)
1292
1293
1294 --Get Vat tax type and GT currency code defined in GTA system options form
1295 --for current operating unit
1296 OPEN c_tax_type_code;
1297 FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
1298 CLOSE c_tax_type_code;
1299
1300 --Get count of lines which have multiple tax lines for an AR transactions
1301 OPEN c_tax_line_count;
1302 FETCH c_tax_line_count INTO l_taxline_count;
1303 CLOSE c_tax_line_count;
1304
1305 --logging for debug
1306 IF (l_proc_level >= l_dbg_level)
1307 THEN
1308 fnd_log.STRING(l_proc_level
1309 ,g_module_prefix || l_procedure_name || '.End'
1310 ,'Exit function');
1311 END IF; --l_proc_level>=l_dbg_level)
1312
1313 IF l_taxline_count=0
1314 THEN
1315 RETURN(TRUE);
1316 ELSE
1317 RETURN(FALSE);
1318 END IF; --l_taxline_count=0
1319
1320 END Check_Taxcount_Of_Artrx;
1321
1322 --==========================================================================
1323 -- FUNCTION NAME:
1324 --
1325 -- Get_Arinvoice_Amount Public
1326 --
1327 -- DESCRIPTION:
1328 --
1329 -- This Function is to get taxable amount of an AR transaction per VAT tax
1330 -- type and GT currency code defind in GTA 'system options' form
1331 --
1332 -- PARAMETERS:
1333 -- In: p_org_id identifier of operating unit
1334 -- p_customer_trx_id identifier of AR transaction
1335 --
1336 -- Return: NUMBER
1337 --
1338 -- DESIGN REFERENCES:
1339 -- GTA_Reports_TD.doc
1340 --
1341 -- CHANGE HISTORY:
1342 --
1343 -- 8-MAY-2005: Qiang Li Created
1344 -- 25-Nov-2005: Donghai Wang update code due to ebtax requirement
1345 --===========================================================================
1346 FUNCTION Get_Arinvoice_Amount
1347 (p_org_id IN NUMBER
1348 ,p_customer_trx_id IN NUMBER
1349 )
1350 RETURN NUMBER
1351 IS
1352 l_procedure_name VARCHAR2(30) := 'Get_Arinvoice_Amount';
1353 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1354 l_proc_level NUMBER := fnd_log.level_procedure;
1355
1356 l_tax_type_code zx_lines.tax_type_code%TYPE;
1357 l_gt_currency_code fnd_currencies.currency_code%TYPE;
1358 l_ar_taxable_amount NUMBER;
1359
1360
1361
1362 CURSOR c_tax_type_code
1363 IS
1364 SELECT
1365 vat_tax_type_code
1366 ,gt_currency_code
1367 FROM
1368 ar_gta_system_parameters_all
1369 WHERE org_id=p_org_id;
1370
1371 CURSOR c_ar_taxable_amount
1372 IS
1373 SELECT
1374 NVL(SUM(taxable_amt_tax_curr),0)
1375 FROM
1376 zx_lines
1377 WHERE application_id = 222
1378 AND trx_id=p_customer_trx_id
1379 AND trx_level_type='LINE'
1380 AND entity_code='TRANSACTIONS'
1381 AND tax_type_code=l_tax_type_code
1382 AND tax_currency_code=l_gt_currency_code
1383 AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO'); --Donghai Wang bug5212702 May-17,2006
1384
1385 BEGIN
1386 --logging for debug
1387 IF (l_proc_level >= l_dbg_level)
1388 THEN
1389 fnd_log.STRING(l_proc_level
1390 ,g_module_prefix || l_procedure_name || '.begin'
1391 ,'enter function');
1392 END IF;--(l_proc_level >= l_dbg_level)
1393
1394 --Get Vat tax type and GT currency code defined in GTA system options form
1395 --for current operating unit
1396 OPEN c_tax_type_code;
1397 FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
1398 CLOSE c_tax_type_code;
1399
1400 --Get total taxable amount of lines for an AR transactions
1401 OPEN c_ar_taxable_amount;
1402 FETCH c_ar_taxable_amount INTO l_ar_taxable_amount;
1403 CLOSE c_ar_taxable_amount;
1404
1405
1406 --logging for debug
1407 IF (l_proc_level >= l_dbg_level)
1408 THEN
1409 fnd_log.STRING(l_proc_level
1410 ,g_module_prefix || l_procedure_name || '.end'
1411 ,'end function');
1412 END IF; --(l_proc_level >= l_dbg_level)
1413
1414 RETURN l_ar_taxable_amount;
1415 END Get_Arinvoice_Amount;
1416
1417 --==========================================================================
1418 -- FUNCTION NAME:
1419 --
1420 -- Get_Arinvoice_Tax_Amount Public
1421 --
1422 -- DESCRIPTION:
1423 --
1424 -- This Function is to get tax amount of an AR transaction per VAT tax
1425 -- type and GT currency code defind in GTA 'system options' form
1426 --
1427 -- PARAMETERS:
1428 -- In: p_org_id identifier of operating unit
1429 -- p_customer_trx_id identifier of AR transaction
1430 --
1431 -- Return: Number
1432 --
1433 -- DESIGN REFERENCES:
1434 -- GTA_Reports_TD.doc
1435 --
1436 -- CHANGE HISTORY:
1437 --
1438 -- 8-MAY-2005: Qiang Li Created
1439 -- 25-Nov-2005: Donghai Wang update code due to ebtax requirement
1440 --===========================================================================
1441 FUNCTION Get_Arinvoice_Tax_Amount
1442 (p_org_id IN NUMBER
1443 ,p_customer_trx_id IN NUMBER
1444 )
1445 RETURN NUMBER
1446 IS
1447 l_procedure_name VARCHAR2(30) := 'Get_Arinvoice_Tax_Amount';
1448 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1449 l_proc_level NUMBER := fnd_log.level_procedure;
1450
1451 l_tax_type_code zx_lines.tax_type_code%TYPE;
1452 l_gt_currency_code fnd_currencies.currency_code%TYPE;
1453 l_ar_tax_amount NUMBER;
1454
1455
1456
1457 CURSOR c_tax_type_code
1458 IS
1459 SELECT
1460 vat_tax_type_code
1461 ,gt_currency_code
1462 FROM
1463 ar_gta_system_parameters_all
1464 WHERE org_id=p_org_id;
1465
1466 CURSOR c_ar_tax_amount
1467 IS
1468 SELECT
1469 NVL(SUM(tax_amt_tax_curr),0)
1470 FROM
1471 zx_lines
1472 WHERE application_id = 222
1473 AND trx_id=p_customer_trx_id
1474 AND trx_level_type='LINE'
1475 AND entity_code='TRANSACTIONS'
1476 AND tax_type_code=l_tax_type_code
1477 AND tax_currency_code=l_gt_currency_code
1478 AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO'); --Donghai Wang bug5212702 May-17,2006;
1479
1480 BEGIN
1481 --logging for debug
1482 IF (l_proc_level >= l_dbg_level)
1483 THEN
1484 fnd_log.STRING(l_proc_level
1485 ,g_module_prefix || l_procedure_name || '.begin'
1486 ,'enter function');
1487 END IF;--(l_proc_level >= l_dbg_level)
1488
1489 --Get Vat tax type and GT currency code defined in GTA system options form
1490 --for current operating unit
1491 OPEN c_tax_type_code;
1492 FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
1493 CLOSE c_tax_type_code;
1494
1495 --Get total VAT tax amount of AR transaction
1496 OPEN c_ar_tax_amount;
1497 FETCH c_ar_tax_amount INTO l_ar_tax_amount ;
1498 CLOSE c_ar_tax_amount;
1499
1500
1501 --logging for debug
1502 IF (l_proc_level >= l_dbg_level)
1503 THEN
1504 fnd_log.STRING(l_proc_level
1505 ,g_module_prefix || l_procedure_name || '.end'
1506 ,'end function');
1507 END IF; --(l_proc_level >= l_dbg_level)
1508
1509 RETURN l_ar_tax_amount;
1510 END Get_Arinvoice_Tax_Amount;
1511
1512
1513 --==========================================================================
1514 -- PROCEDURE NAME:
1515 --
1516 -- Get_New_TRX_Num Private
1517 --
1518 -- DESCRIPTION:
1519 --
1520 -- This procedure is to get a new trx number
1521 --
1522 -- PARAMETERS:
1523 -- In: p_trx_id Identifier of AR transaction
1524 -- p_group_number Group number
1525 -- p_version_number Version
1526 -- p_org_id Identifier of operating unit
1527 --
1528 -- Out: x_gta_trx_number Number of GTA invoice
1529 --
1530 -- DESIGN REFERENCES:
1531 -- GTA-TRANSFER-PROGRAM-TD.doc
1532 --
1533 -- CHANGE HISTORY:
1534 --
1535 -- 23-MAy-2005: Jim.zheng Creation
1536 --
1537 --===========================================================================
1538 PROCEDURE get_new_trx_num
1539 (p_trx_id IN VARCHAR2
1540 ,p_group_number IN VARCHAR2
1541 ,p_version_number IN VARCHAR2
1542 ,x_gta_trx_number OUT NOCOPY VARCHAR2
1543 )
1544 IS
1545 boundary VARCHAR2(1) := '-';
1546
1547 BEGIN
1548 x_gta_trx_number := p_trx_id || boundary || p_group_number || boundary ||
1549 p_version_number;
1550 END get_new_trx_num;
1551
1552 --==========================================================================
1553 -- FUNCTION NAME:
1554 --
1555 -- Format_Date Public
1556 --
1557 -- DESCRIPTION:
1558 --
1559 -- This funtion is to get appropriate format string for
1560 -- a given date according the ICX_DATE_FORMAT_MASK profile
1561 --
1562 -- PARAMETERS:
1563 -- In: p_date The date to be formate
1564 --
1565 -- Return: VARCHAR2
1566 --
1567 -- DESIGN REFERENCES:
1568 -- GTA_Reports_TD.doc
1569 --
1570 -- CHANGE HISTORY:
1571 --
1572 -- 23-MAy-2005: Qiang Li Creation
1573 --
1574 --===========================================================================
1575 FUNCTION format_date(p_date IN DATE) RETURN VARCHAR2 IS
1576 l_procedure_name VARCHAR2(30) := 'Format_Date';
1577 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1578 l_proc_level NUMBER := fnd_log.level_procedure;
1579 l_ret VARCHAR(40);
1580
1581 l_date_format fnd_profile_option_values.profile_option_value%TYPE := NULL;
1582
1583 BEGIN
1584 --logging for debug
1585 IF (l_proc_level >= l_dbg_level)
1586 THEN
1587 fnd_log.STRING(l_proc_level
1588 ,g_module_prefix || l_procedure_name || '.begin'
1589 ,'enter function');
1590 END IF;
1591
1592 fnd_profile.get('ICX_DATE_FORMAT_MASK'
1593 ,l_date_format);
1594 l_ret := to_char(p_date
1595 ,nvl(l_date_format
1596 ,'Rrrr-Mm-Dd'));
1597
1598 --logging for debug
1599 IF (l_proc_level >= l_dbg_level)
1600 THEN
1601 fnd_log.STRING(l_proc_level
1602 ,g_module_prefix || l_procedure_name || '.end'
1603 ,'end function');
1604 END IF;
1605
1606 RETURN l_ret;
1607 END format_date;
1608
1609 --==========================================================================
1610 -- FUNCTION NAME:
1611 --
1612 -- Get_Primary_Phone_Number Public
1613 --
1614 -- DESCRIPTION:
1615 --
1616 -- This procedure is to get primary phone number for a given customer
1617 --
1618 -- PARAMETERS:
1619 -- In: p_customer_id Customer identifier
1620 --
1621 -- Return: VARCHAR2
1622 --
1623 -- DESIGN REFERENCES:
1624 -- GTA_Reports_TD.doc
1625 --
1626 -- CHANGE HISTORY:
1627 --
1628 -- 23-May-2005: Donghai Wang Created
1629 -- 26-Jun-2006: Donghai Wang In the cursor c_phone_number, add sub
1630 -- query to fetch party_id by
1631 -- "bill to customer id" passed in,instead
1632 -- of using "bill to customer id"
1633 -- directly.
1634 -- 21-May-2006 Donghai Wang Fix the bug 5263009
1635 --
1636 --===========================================================================
1637 FUNCTION get_primary_phone_number
1638 (p_customer_id IN NUMBER
1639 )
1640 RETURN VARCHAR2
1641 IS
1642 l_customer_id hz_parties.party_id%TYPE := p_customer_id;
1643 l_phone_number hz_contact_points.phone_number%TYPE;
1644
1645 --Fix bug 5263009, Donghai Wang
1646 --Add the sub query to get party id by customer id
1647 CURSOR c_phone_number
1648 IS
1649 SELECT
1650 hcp.phone_number
1651 FROM
1652 hz_contact_points hcp
1653 WHERE hcp.contact_point_type = 'PHONE'
1654 AND hcp.owner_table_name = 'HZ_PARTIES'
1655 AND hcp.owner_table_id = (SELECT
1656 party_id
1657 FROM
1658 hz_cust_accounts_all
1659 WHERE cust_account_id=l_customer_id
1660 )
1661 AND hcp.primary_flag = 'Y';
1662
1663 l_procedure_name VARCHAR2(30) := 'Get_Primary_Phone_Number';
1664 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1665 l_proc_level NUMBER := fnd_log.level_procedure;
1666 BEGIN
1667
1668 --logging for debug
1669 IF (l_proc_level >= l_dbg_level)
1670 THEN
1671 fnd_log.STRING(l_proc_level
1672 ,g_module_prefix || l_procedure_name || '.begin'
1673 ,'Enter function');
1674 END IF; --l_proc_level>=l_dbg_level)
1675 OPEN c_phone_number;
1676 FETCH c_phone_number
1677 INTO l_phone_number;
1678 CLOSE c_phone_number;
1679
1680 --logging for debug
1681 IF (l_proc_level >= l_dbg_level)
1682 THEN
1683 fnd_log.STRING(l_proc_level
1684 ,g_module_prefix || l_procedure_name || '.End'
1685 ,'Exit function');
1686 END IF; --l_proc_level>=l_dbg_level)
1687
1688 RETURN(l_phone_number);
1689 END get_primary_phone_number;
1690
1691 --==========================================================================
1692 -- FUNCTION NAME:
1693 --
1694 -- Get_Operatingunit Public
1695 --
1696 -- DESCRIPTION:
1697 --
1698 -- This function is to get operating unit for a given org_id
1699 --
1700 -- PARAMETERS:
1701 -- In: p_org_id Identifier of Operating Unit
1702 --
1703 -- Return: VARCHAR2
1704 --
1705 -- DESIGN REFERENCES:
1706 -- GTA_Reports_TD.doc
1707 --
1708 -- CHANGE HISTORY:
1709 --
1710 -- 23-MAy-2005: Qiang Li Creation
1711 -- 26-Dec-2005: Qiang Li fix a performance issue
1712 --=========================================================================
1713 FUNCTION get_operatingunit(p_org_id IN NUMBER) RETURN VARCHAR2 IS
1714 l_procedure_name VARCHAR2(30) := 'Get_OperatingUnit';
1715 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1716 l_proc_level NUMBER := fnd_log.level_procedure;
1717
1718 l_operating_unit hr_operating_units.NAME%TYPE;
1719 CURSOR c_operating_unit IS
1720 SELECT OTL.NAME
1721 FROM HR_ALL_ORGANIZATION_UNITS O
1722 , HR_ALL_ORGANIZATION_UNITS_TL OTL
1723 WHERE O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
1724 AND OTL.LANGUAGE = userenv('LANG')
1725 AND O.ORGANIZATION_ID = p_org_id;
1726
1727 BEGIN
1728 --logging for debug
1729 IF (l_proc_level >= l_dbg_level)
1730 THEN
1731 fnd_log.STRING(l_proc_level
1732 ,g_module_prefix || l_procedure_name || '.begin'
1733 ,'enter function');
1734 END IF;
1735
1736 OPEN c_operating_unit;
1737 FETCH
1738 c_operating_unit
1739 INTO
1740 l_operating_unit;
1741
1742 CLOSE c_operating_unit;
1743
1744 --logging for debug
1745 IF (l_proc_level >= l_dbg_level)
1746 THEN
1747 fnd_log.STRING(l_proc_level
1748 ,g_module_prefix || l_procedure_name || '.end'
1749 ,'end function');
1750 END IF;
1751
1752 RETURN(l_operating_unit);
1753 END get_operatingunit;
1754
1755 --==========================================================================
1756 -- FUNCTION NAME:
1757 --
1758 -- Get_Customer_Name Public
1759 --
1760 -- DESCRIPTION:
1761 --
1762 -- This function is to get Customer name for a given customer id
1763 --
1764 -- PARAMETERS:
1765 -- In: p_customer_id customer identifier
1766 --
1767 -- Return: VARCHAR2
1768 --
1769 -- DESIGN REFERENCES:
1770 -- GTA_Reports_TD.doc
1771 --
1772 -- CHANGE HISTORY:
1773 --
1774 -- 23-MAy-2005: Qiang Li Creation
1775 --
1776 --=========================================================================
1777 FUNCTION get_customer_name
1778 (p_customer_id IN NUMBER)
1779 RETURN VARCHAR2
1780 IS
1781 l_procedure_name VARCHAR2(30) := 'Get_Customer_Name';
1782 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1783 l_proc_level NUMBER := fnd_log.level_procedure;
1784
1785 l_customer_name hz_parties.party_name%TYPE;
1786 CURSOR c_customer_name IS
1787 SELECT
1788 p.party_name
1789 FROM
1790 hz_parties p
1791 ,hz_cust_accounts a
1792 WHERE a.cust_account_id = p_customer_id
1793 AND p.party_id = a.party_id;
1794
1795 BEGIN
1796 --logging for debug
1797 IF (l_proc_level >= l_dbg_level)
1798 THEN
1799 fnd_log.STRING(l_proc_level
1800 ,g_module_prefix || l_procedure_name || '.begin'
1801 ,'enter function');
1802 END IF;
1803
1804 OPEN c_customer_name;
1805
1806 FETCH
1807 c_customer_name
1808 INTO
1809 l_customer_name;
1810
1811 CLOSE c_customer_name;
1812
1813 --logging for debug
1814 IF (l_proc_level >= l_dbg_level)
1815 THEN
1816 fnd_log.STRING(l_proc_level
1817 ,g_module_prefix || l_procedure_name || '.end'
1818 ,'end function');
1819 END IF;
1820
1821 RETURN(l_customer_name);
1822 END get_customer_name;
1823
1824 --==========================================================================
1825 -- FUNCTION NAME:
1826 --
1827 -- Get_Arline_Amount Public
1828 --
1829 -- DESCRIPTION:
1830 --
1831 -- This function is used to get line amount per Golden Tax currency for
1832 -- one AR line
1833 --
1834 --
1835 -- PARAMETERS:
1836 -- In: p_org_id identifier of operating unit
1837 -- p_customer_trx_line_id AR line identifier
1838 --
1839 -- Return: NUMBER
1840 --
1841 -- DESIGN REFERENCES:
1842 -- GTA_Reports_TD.doc
1843 --
1844 -- CHANGE HISTORY:
1845 --
1846 -- 13-Jun-2005: Donghai Wang Creation
1847 -- 24-Nov-2005: Modify program logic to get line amount per Golden
1848 -- Tax currency from the table zx_lines
1849 --
1850 --=========================================================================
1851 FUNCTION Get_Arline_Amount
1852 (p_org_id IN NUMBER
1853 ,p_customer_trx_line_id IN NUMBER
1854 )
1855 RETURN NUMBER
1856 IS
1857 l_tax_type_code zx_lines.tax_type_code%TYPE;
1858 l_arline_amount NUMBER;
1859 l_gt_currency_code fnd_currencies.currency_code%TYPE;
1860 l_trx_id ra_customer_trx_all.customer_trx_id%TYPE;
1861
1862 CURSOR c_tax_type_code
1863 IS
1864 SELECT
1865 vat_tax_type_code
1866 ,gt_currency_code
1867 FROM
1868 ar_gta_system_parameters_all
1869 WHERE org_id=p_org_id;
1870
1871 --CURSOR c_ar_line_taxable_amount --Donghai Wang bug5212702 May-17,2006
1872 CURSOR c_ar_line_taxable_amount(pc_trx_id NUMBER)--Donghai Wang bug5212702 May-17,2006
1873 IS
1874 SELECT
1875 taxable_amt_tax_curr
1876 FROM
1877 zx_lines
1878 WHERE trx_line_id=p_customer_trx_line_id
1879 AND entity_code='TRANSACTIONS'
1880 AND application_id = 222
1881 AND trx_level_type='LINE'
1882 AND tax_type_code=l_tax_type_code
1883 AND tax_currency_code=l_gt_currency_code
1884 AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--Donghai Wang bug5212702 May-17,2006
1885 AND trx_id=pc_trx_id
1886 ORDER BY tax_line_id;
1887
1888
1889
1890 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1891 l_proc_level NUMBER := fnd_log.level_procedure;
1892 l_procedure_name VARCHAR2(30) := 'Get_Arline_Amount';
1893
1894 BEGIN
1895 --logging for debug
1896 IF (l_proc_level >= l_dbg_level)
1897 THEN
1898 fnd_log.STRING(l_proc_level
1899 ,g_module_prefix || l_procedure_name || '.begin'
1900 ,'Enter function');
1901 END IF; --l_proc_level>=l_dbg_level)
1902
1903
1904 --Get Vat tax type defined in GTA system options form for current
1905 --operating unit
1906 OPEN c_tax_type_code;
1907 FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
1908 CLOSE c_tax_type_code;
1909
1910 --Get taxable amount per Golden Tax Currency for one AR line
1911 --Donghai Wang bug5212702 May-17,2006
1912 --OPEN c_ar_line_taxable_amount;
1913
1914 SELECT customer_trx_id
1915 INTO l_trx_id
1916 FROM ra_customer_trx_lines_all
1917 WHERE customer_trx_line_id=p_customer_trx_line_id;
1918
1919 OPEN c_ar_line_taxable_amount(l_trx_id);
1920 --Donghai Wang bug5212702 May-17,2006
1921
1922 FETCH c_ar_line_taxable_amount INTO l_arline_amount;
1923 CLOSE c_ar_line_taxable_amount;
1924
1925 --logging for debug
1926 IF (l_proc_level >= l_dbg_level)
1927 THEN
1928 fnd_log.STRING(l_proc_level
1929 ,g_module_prefix || l_procedure_name || '.End'
1930 ,'Exit function');
1931 END IF; --l_proc_level>=l_dbg_level)
1932
1933 RETURN(l_arline_amount);
1934
1935 END Get_Arline_Amount;
1936
1937 --==========================================================================
1938 -- FUNCTION NAME:
1939 --
1940 -- Get_Arline_Vattax_Amount Public
1941 --
1942 -- DESCRIPTION:
1943 --
1944 -- This function is used to get VAT amount based on one AR line
1945 -- per Golden Tax currency
1946 --
1947 -- PARAMETERS:
1948 -- In: p_org_id Identifier of operating unit
1949 -- p_customer_trx_line_id AR line identifier
1950 --
1951 -- Return: NUMBER
1952 --
1953 -- DESIGN REFERENCES:
1954 -- GTA_Reports_TD.doc
1955 --
1956 -- CHANGE HISTORY:
1957 --
1958 -- 13-Jun-2005: Donghai Wang Creation
1959 -- 24-Nov-2005: Donghai Wang Add a new parameter 'p_org_id' and
1960 -- replace dummy code to real code
1961 --
1962 --=========================================================================
1963 FUNCTION Get_Arline_Vattax_Amount
1964 (p_org_id IN NUMBER
1965 ,p_customer_trx_line_id IN NUMBER
1966 )
1967 RETURN NUMBER
1968 IS
1969 l_tax_type_code zx_lines.tax_type_code%TYPE;
1970 l_arline_vatamount NUMBER;
1971 l_gt_currency_code fnd_currencies.currency_code%TYPE;
1972 l_trx_id ra_customer_trx_all.customer_trx_id%TYPE;--Donghai Wang bug5212702 May-17,2006
1973
1974 CURSOR c_tax_type_code
1975 IS
1976 SELECT
1977 vat_tax_type_code
1978 ,gt_currency_code
1979 FROM
1980 ar_gta_system_parameters_all
1981 WHERE org_id=p_org_id;
1982
1983 --CURSOR c_ar_line_vatamount--Donghai Wang bug5212702 May-17,2006
1984 CURSOR c_ar_line_vatamount(pc_trx_id NUMBER)--Donghai Wang bug5212702 May-17,2006
1985 IS
1986 SELECT
1987 tax_amt_tax_curr
1988 FROM
1989 zx_lines
1990 WHERE trx_line_id=p_customer_trx_line_id
1991 AND entity_code='TRANSACTIONS'
1992 AND application_id = 222
1993 AND trx_level_type='LINE'
1994 AND tax_type_code=l_tax_type_code
1995 AND tax_currency_code=l_gt_currency_code
1996 AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--Donghai Wang bug5212702 May-17,2006
1997 AND trx_id=pc_trx_id
1998 ORDER BY tax_line_id;
1999
2000
2001
2002 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
2003 l_proc_level NUMBER := fnd_log.level_procedure;
2004 l_procedure_name VARCHAR2(30) := 'Get_Arline_Vattax_Amount';
2005
2006 BEGIN
2007 --logging for debug
2008 IF (l_proc_level >= l_dbg_level)
2009 THEN
2010 fnd_log.STRING(l_proc_level
2011 ,g_module_prefix || l_procedure_name || '.begin'
2012 ,'Enter function');
2013 END IF; --l_proc_level>=l_dbg_level)
2014
2015
2016 --Get Vat tax type defined in GTA system options form for current
2017 --operating unit
2018 OPEN c_tax_type_code;
2019 FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
2020 CLOSE c_tax_type_code;
2021
2022 --Get tax amount per Golden Tax Currency for one AR line
2023
2024 --Donghai Wang bug5212702 May-17,2006
2025
2026 SELECT customer_trx_id
2027 INTO l_trx_id
2028 FROM ra_customer_trx_lines_all
2029 WHERE customer_trx_line_id=p_customer_trx_line_id;
2030 --OPEN c_ar_line_vatamount;
2031 OPEN c_ar_line_vatamount(l_trx_id);
2032
2033 --Donghai Wang bug5212702 May-17,2006
2034
2035 FETCH c_ar_line_vatamount INTO l_arline_vatamount;
2036 CLOSE c_ar_line_vatamount;
2037
2038 --logging for debug
2039 IF (l_proc_level >= l_dbg_level)
2040 THEN
2041 fnd_log.STRING(l_proc_level
2042 ,g_module_prefix || l_procedure_name || '.End'
2043 ,'Exit function');
2044 END IF; --l_proc_level>=l_dbg_level)
2045
2046 RETURN(l_arline_vatamount);
2047 END Get_Arline_Vattax_Amount;
2048
2049 --==========================================================================
2050 -- FUNCTION NAME:
2051 --
2052 -- Get_Arline_Vattax_Rate Public
2053 --
2054 -- DESCRIPTION:
2055 --
2056 -- This function is used to get VAT rate for one AR line
2057 --
2058 -- PARAMETERS:
2059 -- In: p_org_id Identifier of Operating Unit
2060 -- p_customer_trx_line_id AR line identifier
2061 --
2062 -- Return: NUMBER
2063 --
2064 -- DESIGN REFERENCES:
2065 -- GTA_Reports_TD.doc
2066 --
2067 -- CHANGE HISTORY:
2068 --
2069 -- 13-Jun-2005: Donghai Wang Creation
2070 -- 24-Nov-2005: Donghai Wang Add a new parameter 'p_org_id' and
2071 -- replace dummy code to real code
2072 --
2073 --=========================================================================
2074 FUNCTION Get_Arline_Vattax_Rate
2075 (p_org_id IN NUMBER
2076 ,p_customer_trx_line_id IN NUMBER
2077 )
2078 RETURN NUMBER
2079 IS
2080 l_tax_type_code zx_lines.tax_type_code%TYPE;
2081 l_tax_rate NUMBER;
2082 l_gt_currency_code fnd_currencies.currency_code%TYPE;
2083 l_trx_id ra_customer_trx_all.customer_trx_id%TYPE;--Donghai Wang bug5212702 May-17,2006
2084
2085 CURSOR c_tax_type_code
2086 IS
2087 SELECT
2088 vat_tax_type_code
2089 ,gt_currency_code
2090 FROM
2091 ar_gta_system_parameters_all
2092 WHERE org_id=p_org_id;
2093
2094 --CURSOR c_ar_line_tax_rate --Donghai Wang bug5212702 May-17,2006
2095 CURSOR c_ar_line_tax_rate(pc_trx_id NUMBER) --Donghai Wang bug5212702 May-17,2006
2096 IS
2097 SELECT
2098 tax_rate
2099 FROM
2100 zx_lines
2101 WHERE trx_line_id=p_customer_trx_line_id
2102 AND entity_code='TRANSACTIONS'
2103 AND application_id = 222
2104 AND trx_level_type='LINE'
2105 AND tax_type_code=l_tax_type_code
2106 AND tax_currency_code=l_gt_currency_code
2107 AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--Donghai Wang bug5212702 May-17,2006
2108 AND trx_id=pc_trx_id --Donghai Wang bug5212702 May-17,2006
2109 ORDER BY tax_line_id;
2110
2111
2112
2113 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
2114 l_proc_level NUMBER := fnd_log.level_procedure;
2115 l_procedure_name VARCHAR2(30) := 'Get_Arline_Vattax_Rate';
2116
2117 BEGIN
2118 --logging for debug
2119 IF (l_proc_level >= l_dbg_level)
2120 THEN
2121 fnd_log.STRING(l_proc_level
2122 ,g_module_prefix || l_procedure_name || '.begin'
2123 ,'Enter function');
2124 END IF; --l_proc_level>=l_dbg_level)
2125
2126
2127 --Get Vat tax type defined in GTA system options form for current
2128 --operating unit
2129 OPEN c_tax_type_code;
2130 FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
2131 CLOSE c_tax_type_code;
2132
2133 --Get tax rate for one AR line
2134 --Donghai Wang bug5212702 May-17,2006
2135 SELECT customer_trx_id
2136 INTO l_trx_id
2137 FROM ra_customer_trx_lines_all
2138 WHERE customer_trx_line_id=p_customer_trx_line_id;
2139
2140 --OPEN c_ar_line_tax_rate;
2141 OPEN c_ar_line_tax_rate(l_trx_id);
2142 --Donghai Wang bug5212702 May-17,2006
2143
2144 FETCH c_ar_line_tax_rate INTO l_tax_rate;
2145 CLOSE c_ar_line_tax_rate;
2146
2147 --logging for debug
2148 IF (l_proc_level >= l_dbg_level)
2149 THEN
2150 fnd_log.STRING(l_proc_level
2151 ,g_module_prefix || l_procedure_name || '.End'
2152 ,'Exit function');
2153 END IF; --l_proc_level>=l_dbg_level)
2154
2155 RETURN(l_tax_rate/100);
2156 END Get_Arline_Vattax_Rate;
2157
2158 --==========================================================================
2159 -- Procedure NAME:
2160 --
2161 -- get_bank_info Public
2162 --
2163 -- DESCRIPTION:
2164 --
2165 -- This function get bank infomations by cust_Trx_id, if the bank info from AR
2166 -- is null. then get bank infomations by customer_id
2167 --
2168 -- PARAMETERS:
2169 -- In:
2170 -- p_customer_trx_id IN NUMBER
2171 -- p_trxn_extension_id IN NUMBER
2172 -- OUT:
2173 -- x_bank_name OUT NOCOPY VARCHAR2
2174 -- x_bank_branch_name OUT NOCOPY VARCHAR2
2175 -- x_bank_account_name OUT NOCOPY VARCHAR2
2176 -- x_bank_account_num OUT NOCOPY VARCHAR2
2177 --
2178 --
2179 -- DESIGN REFERENCES:
2180 --
2181 --
2182 -- CHANGE HISTORY:
2183 --
2184 -- 17-AUG-2005: JIM.Zheng Created
2185 -- 31-Apr2009: Yao Zhang Changed for bug 8234250
2186 -- 16-Jun-2009 Yao Zhang Changed for bug 8605196
2187 --===========================================================================
2188 PROCEDURE Get_Bank_Info
2189 ( p_customer_trx_id IN NUMBER
2190 , p_org_id IN NUMBER
2191 , x_bank_name OUT NOCOPY VARCHAR2
2192 , x_bank_branch_name OUT NOCOPY VARCHAR2
2193 , x_bank_account_name OUT NOCOPY VARCHAR2
2194 , x_bank_account_num OUT NOCOPY VARCHAR2
2195 )
2196 IS
2197 l_procedure_name VARCHAR2(30) := 'Get_Bank_Info';
2198
2199 l_bill_to_customer_id ra_customer_trx_all.bill_to_customer_id%TYPE;
2200 ----Yao Zhang add begin for bug#8404856
2201 l_bill_to_site_use_id ra_customer_trx_all.bill_to_site_use_id%TYPE;
2202 l_valid_customer_id ra_customer_trx_all.bill_to_customer_id%TYPE;
2203 l_valid_site_use_id ra_customer_trx_all.bill_to_site_use_id%TYPE;
2204 ----Yao Zhang add end for bug#8404856
2205
2206 l_site_use_id hz_cust_site_uses.SITE_USE_ID%TYPE;
2207 l_cust_acct_site_id hz_cust_acct_sites.CUST_ACCT_SITE_ID%TYPE;
2208 l_currency_code ar_gta_system_parameters_all.gt_currency_code%TYPE;
2209 l_error_string VARCHAR2(500);
2210
2211 l_paying_customer_id ra_customer_trx_all.paying_customer_id%TYPE;
2212 l_paying_site_use_id ra_customer_trx_all.paying_site_use_id%TYPE;
2213 l_paying_site_id hz_cust_acct_sites.CUST_ACCT_SITE_ID%TYPE;
2214 l_paying_party_id HZ_CUST_ACCOUNTS.party_id%TYPE;
2215 l_ext_payer_id IBY_EXTERNAL_PAYERS_ALL.ext_payer_id%TYPE;
2216 l_bank_account_name IBY_EXT_BANK_ACCOUNTS.bank_account_name%TYPE;
2217 l_bank_account_num IBY_EXT_BANK_ACCOUNTS.bank_account_num%TYPE;
2218 l_bank_id IBY_EXT_BANK_ACCOUNTS.bank_id%TYPE;
2219 l_bank_branch_id IBY_EXT_BANK_ACCOUNTS.branch_id%TYPE;
2220 l_bank_name HZ_PARTIES.party_name%TYPE;
2221 l_bank_branch_name HZ_PARTIES.party_name%TYPE;
2222 l_trxn_extension_id ra_customer_trx_all.payment_trxn_extension_id%TYPE;
2223
2224 l_instrument_id IBY_EXT_BANK_ACCOUNTS.ext_bank_account_id%TYPE;
2225
2226
2227
2228
2229 BEGIN
2230 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2231 THEN
2232 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2233 , G_MODULE_PREFIX || l_procedure_name
2234 ,'begin Procedure. ');
2235 END IF;
2236
2237 BEGIN
2238 SELECT
2239 gt_currency_code
2240 INTO
2241 l_currency_code
2242 FROM
2243 ar_gta_system_parameters_all
2244 WHERE org_id=p_org_id;
2245
2246 EXCEPTION
2247 WHEN no_data_found THEN
2248 --report AR_GTA_MISSING_ERROR
2249 fnd_message.set_name('AR', 'AR_GTA_MISSING_ERROR');
2250 l_error_string := fnd_message.get();
2251 -- output this error
2252 fnd_file.put_line(fnd_file.output, '<?xml version="1.0" encoding="UTF-8" ?>
2253 <TransferReport>
2254 <ReportFailed>Y</ReportFailed>
2255 <ReportFailedMsg>'||l_error_string||'</ReportFailedMsg>
2256 <TransferReport>');
2257
2258
2259 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2260 THEN
2261 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
2262 , G_MODULE_PREFIX || l_procedure_name
2263 , l_error_string);
2264 END IF;
2265 RAISE;
2266 END;
2267
2268 BEGIN
2269 SELECT
2270 h.paying_customer_id
2271 ,h.paying_site_use_id
2272 ,h.payment_trxn_extension_id
2273 --Yao Zhang add begin for bug#8404856
2274 ,h.bill_to_customer_id
2275 ,h.bill_to_site_use_id
2276 --Yao Zhang add end for bug#8404856
2277 INTO
2278 l_paying_customer_id
2279 , l_paying_site_use_id
2280 , l_trxn_extension_id
2281 --Yao Zhang add for bug#8404856
2282 , l_bill_to_customer_id
2283 , l_bill_to_site_use_id
2284 --Yao Zhang add end for bug#8404856
2285 FROM
2286 ra_customer_trx_all h
2287
2288 WHERE h.customer_trx_id = p_customer_trx_id ;
2289 EXCEPTION
2290 WHEN no_data_found THEN
2291 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2292 THEN
2293 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2294 , G_MODULE_PREFIX || l_procedure_name
2295 , 'no date found when select header info');
2296 END IF;
2297 END;
2298
2299 -- select bank information
2300 IF (l_paying_customer_id is not null) and (l_trxn_extension_id IS NOT NULL)--yao zhang changed for bug 8234250
2301 THEN
2302
2303 BEGIN
2304 SELECT
2305 u.instrument_id
2306 , b.bank_account_name
2307 --Modified by Yao begin for bug#8605196 to support Bank name in Chinese
2308 --, b.bank_name
2309 , decode(bhp.organization_name_phonetic
2310 ,null, bhp.party_name
2311 ,bhp.organization_name_phonetic)
2312 --, b.bank_branch_name
2313 , decode(brhp.organization_name_phonetic
2314 ,null, brhp.party_name
2315 ,brhp.organization_name_phonetic)
2316 --Modified by Yao for bug#8605196 end to support Bank name in Chinese
2317 INTO
2318 l_instrument_id
2319 , l_bank_account_name
2320 , l_bank_name
2321 , l_bank_branch_name
2322 FROM IBY_CREDITCARD C,
2323 IBY_CREDITCARD_ISSUERS_VL I,
2324 IBY_EXT_BANK_ACCOUNTS_V B,
2325 IBY_FNDCPT_PMT_CHNNLS_VL P,
2326 IBY_FNDCPT_TX_EXTENSIONS X,
2327 IBY_FNDCPT_TX_OPERATIONS OP,
2328 IBY_PMT_INSTR_USES_ALL U,
2329 HZ_PARTIES HZP,
2330 FND_APPLICATION A,
2331 --Add by Yao for bug#8605196 to support bank name in Chinese
2332 HZ_PARTIES bhp,
2333 HZ_PARTIES brhp
2334 WHERE (x.instr_assignment_id = u.instrument_payment_use_id(+))
2335 AND (DECODE(u.instrument_type, 'CREDITCARD', u.instrument_id, NULL) =
2336 c.instrid(+))
2337 AND (DECODE(u.instrument_type, 'BANKACCOUNT', u.instrument_id, NULL) =
2338 b.bank_account_id(+))
2339 AND (x.payment_channel_code = p.payment_channel_code)
2340 AND (c.card_issuer_code = i.card_issuer_code(+))
2341 AND (x.trxn_extension_id = op.trxn_extension_id(+))
2342 AND (c.card_owner_id = hzp.party_id(+))
2343 AND (x.origin_application_id = a.application_id)
2344 AND x.trxn_extension_id = l_trxn_extension_id
2345 --Add by Yao for bug#8605196 to support bank name in Chinese
2346 AND b.bank_party_id=bhp.party_id(+)
2347 AND b.branch_party_id=brhp.party_id(+);
2348
2349 EXCEPTION
2350 WHEN no_data_found THEN
2351 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2352 THEN
2353 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2354 , G_MODULE_PREFIX || l_procedure_name
2355 , 'no date found when select bank information');
2356 END IF;
2357 END;
2358
2359 BEGIN
2360 SELECT
2361 bank_account_num
2362 INTO
2363 l_bank_account_num
2364 FROM
2365 IBY_EXT_BANK_ACCOUNTS
2366 WHERE
2367 ext_bank_account_id = l_instrument_id;
2368 EXCEPTION
2369 WHEN no_data_found THEN
2370 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2371 THEN
2372 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2373 , G_MODULE_PREFIX || l_procedure_name
2374 , 'no date found when select bank information');
2375 END IF;
2376 END;
2377
2378
2379 END IF;/*l_trxn_extension_id IS NOT NULL*/
2380
2381 -- if the bank information come from AR is null. then select bank info by customer!
2382 IF l_bank_account_num IS NULL
2383 THEN
2384 -- get bank info by paying customer id and paying site use id.
2385 --Yao Zhang add begin for bug#8404856
2386 IF l_paying_customer_id IS NOT NULL
2387 THEN
2388 l_valid_customer_id:=l_paying_customer_id;
2389 l_valid_site_use_id:=l_paying_site_use_id;
2390 ELSE
2391 l_valid_customer_id:=l_bill_to_customer_id;
2392 l_valid_site_use_id:=l_bill_to_site_use_id;
2393 END IF;
2394 --Yao Zhang add end for bug#8404856
2395
2396 BEGIN
2397
2398 -- get party id of paying customer
2399 SELECT
2400 party_id
2401 INTO
2402 l_paying_party_id
2403 FROM
2404 HZ_CUST_ACCOUNTS
2405 WHERE
2406 CUST_ACCOUNT_ID = l_valid_customer_id ;--Yao Zhang modified for bug#8404856
2407
2408 -- get ext_payer_id by party id , site account id , site use id and org id.
2409 SELECT
2410 ext_payer_id
2411 INTO
2412 l_ext_payer_id
2413 FROM
2414 IBY_EXTERNAL_PAYERS_ALL
2415 WHERE party_id = l_paying_party_id
2416 AND CUST_ACCOUNT_ID = l_valid_customer_id--Yao Zhang modified for bug#8404856
2417 AND ACCT_SITE_USE_ID =l_valid_site_use_id--Yao Zhang modified for bug#8404856
2418 AND ORG_ID = p_org_id -- org id
2419 AND org_type = 'OPERATING_UNIT' -- ou
2420 AND payment_function = 'CUSTOMER_PAYMENT';
2421
2422 -- get bank account name and bank account num
2423 SELECT
2424 bank_account_name
2425 , bank_account_num
2426 , bank_id
2427 , branch_id
2428 INTO
2429 l_bank_account_name
2430 , l_bank_account_num
2431 , l_bank_id
2432 , l_bank_branch_id
2433 FROM (SELECT ibybanks.bank_account_name
2434 , ibybanks.bank_account_num
2435 , ibybanks.bank_id
2436 , ibybanks.branch_id
2437 FROM IBY_PMT_INSTR_USES_ALL ExtPartyInstrumentsEO
2438 , IBY_EXT_BANK_ACCOUNTS ibybanks
2439 WHERE ibybanks.EXT_BANK_ACCOUNT_ID = ExtPartyInstrumentsEO.instrument_id
2440 AND ExtPartyInstrumentsEO.INSTRUMENT_TYPE = 'BANKACCOUNT'
2441 AND ExtPartyInstrumentsEO.EXT_PMT_PARTY_ID = l_ext_payer_id
2442 AND ExtPartyInstrumentsEO.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
2443 AND (ibybanks.currency_code = l_currency_code OR ibybanks.currency_code IS NULL)
2444 AND SYSDATE BETWEEN nvl(ExtPartyInstrumentsEO.START_DATE, to_date('1900-01-01','RRRR-MM-DD'))
2445 AND nvl(ExtPartyInstrumentsEO.END_DATE, to_date('3000-01-01','RRRR-MM-DD'))
2446 ORDER BY ibybanks.currency_code,ExtPartyInstrumentsEO.ORDER_OF_PREFERENCE)
2447 WHERE ROWNUM =1;
2448
2449
2450 -- get bank name
2451 --Modified begin by Yao for bug#8605196 to support bank name in Chinese
2452 SELECT
2453 decode(organization_name_phonetic
2454 ,null, party_name
2455 ,organization_name_phonetic)
2456 --Modified end by Yao for bug#8605196 to support bank name in Chinese
2457 INTO
2458 l_bank_name
2459 FROM
2460 HZ_PARTIES
2461 WHERE
2462 party_id = l_bank_id;
2463
2464 -- get bank branch name
2465 SELECT
2466 --Modified begin by Yao for bug#8605196 to support bank name in Chinese
2467 decode(organization_name_phonetic
2468 ,null, party_name
2469 ,organization_name_phonetic)
2470 --Modified end by Yao for bug#8605196 to support bank name in Chinese
2471 INTO
2472 l_bank_branch_name
2473 FROM
2474 HZ_PARTIES
2475 WHERE party_id = l_bank_branch_id;
2476
2477
2478 EXCEPTION
2479 WHEN no_data_found THEN
2480 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2481 THEN
2482 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2483 , G_MODULE_PREFIX || l_procedure_name
2484 , 'no date found when select bank information');
2485 END IF;
2486 END;/*l_apba_bank_account_num IS NULL*/
2487
2488 END IF;
2489
2490 x_bank_name := l_bank_name;
2491 x_bank_branch_name := l_bank_branch_name;
2492 x_bank_account_num := l_bank_account_num;
2493 x_bank_account_name := l_bank_account_name;
2494
2495 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2496 THEN
2497 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2498 , G_MODULE_PREFIX || l_procedure_name
2499 ,'End Procedure. ');
2500 END IF;
2501
2502 EXCEPTION
2503 WHEN OTHERS THEN
2504 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2505 THEN
2506 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
2507 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
2508 , Sqlcode||Sqlerrm);
2509 END IF;
2510 RAISE;
2511 END Get_Bank_Info;
2512 --==========================================================================
2513 -- Procedure NAME:
2514 --
2515 -- get_CM_bank_info Public
2516 --
2517 -- DESCRIPTION:
2518 --
2519 -- This function get bank infomations for Credit Memos which is
2520 -- created by crediting AR invoice.
2521 --
2522 -- PARAMETERS:
2523 -- In:
2524 -- p_org_id IN NUMBER
2525 -- p_customer_trx_id IN NUMBER
2526 -- p_original_trx_id IN NUMBER
2527 -- OUT:
2528 -- x_bank_name OUT NOCOPY VARCHAR2
2529 -- x_bank_branch_name OUT NOCOPY VARCHAR2
2530 -- x_bank_account_name OUT NOCOPY VARCHAR2
2531 -- x_bank_account_num OUT NOCOPY VARCHAR2
2532 --
2533 --
2534 -- DESIGN REFERENCES:
2535 --
2536 --
2537 -- CHANGE HISTORY:
2538 --
2539 -- 31-Mar-2009: Yao Zhang Created
2540 --- 16-Jun-2009 Yao Zhang Changed for bug 8605196
2541 --===========================================================================
2542 PROCEDURE Get_CM_Bank_Info
2543 ( p_org_id IN NUMBER
2544 , p_customer_trx_id IN NUMBER
2545 , p_original_trx_id IN NUMBER
2546 , x_bank_name OUT NOCOPY VARCHAR2
2547 , x_bank_branch_name OUT NOCOPY VARCHAR2
2548 , x_bank_account_name OUT NOCOPY VARCHAR2
2549 , x_bank_account_num OUT NOCOPY VARCHAR2
2550 )
2551 IS
2552 l_procedure_name VARCHAR2(30) := 'Get_CM_Bank_Info';
2553
2554 l_bill_to_customer_id ra_customer_trx_all.bill_to_customer_id%TYPE;
2555 --Yao Zhang add begin for bug#8404856
2556 l_bill_to_site_use_id ra_customer_trx_all.bill_to_site_use_id%TYPE;
2557 l_valid_customer_id ra_customer_trx_all.bill_to_customer_id%TYPE;
2558 l_valid_site_use_id ra_customer_trx_all.bill_to_site_use_id%TYPE;
2559 --Yao Zhang add end for bug#8404856
2560 l_site_use_id hz_cust_site_uses.SITE_USE_ID%TYPE;
2561 l_cust_acct_site_id hz_cust_acct_sites.CUST_ACCT_SITE_ID%TYPE;
2562 l_currency_code ar_gta_system_parameters_all.gt_currency_code%TYPE;
2563 l_error_string VARCHAR2(500);
2564
2565 l_paying_customer_id ra_customer_trx_all.paying_customer_id%TYPE;
2566 l_paying_site_use_id ra_customer_trx_all.paying_site_use_id%TYPE;
2567 l_paying_site_id hz_cust_acct_sites.CUST_ACCT_SITE_ID%TYPE;
2568 l_paying_party_id HZ_CUST_ACCOUNTS.party_id%TYPE;
2569 l_ext_payer_id IBY_EXTERNAL_PAYERS_ALL.ext_payer_id%TYPE;
2570 l_bank_account_name IBY_EXT_BANK_ACCOUNTS.bank_account_name%TYPE;
2571 l_bank_account_num IBY_EXT_BANK_ACCOUNTS.bank_account_num%TYPE;
2572 l_bank_id IBY_EXT_BANK_ACCOUNTS.bank_id%TYPE;
2573 l_bank_branch_id IBY_EXT_BANK_ACCOUNTS.branch_id%TYPE;
2574 l_bank_name HZ_PARTIES.party_name%TYPE;
2575 l_bank_branch_name HZ_PARTIES.party_name%TYPE;
2576 l_trxn_extension_id ra_customer_trx_all.payment_trxn_extension_id%TYPE;
2577 l_instrument_id IBY_EXT_BANK_ACCOUNTS.ext_bank_account_id%TYPE;
2578
2579 l_ori_paying_customer_id ra_customer_trx_all.paying_customer_id%TYPE;
2580 l_ori_paying_site_use_id ra_customer_trx_all.paying_site_use_id%TYPE;
2581 l_ori_paying_site_id hz_cust_acct_sites.CUST_ACCT_SITE_ID%TYPE;
2582 l_ori_trxn_extension_id ra_customer_trx_all.payment_trxn_extension_id%TYPE;
2583
2584 BEGIN
2585 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2586 THEN
2587 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2588 , G_MODULE_PREFIX || l_procedure_name
2589 ,'begin Procedure. ');
2590 END IF;
2591
2592 BEGIN
2593 SELECT
2594 gt_currency_code
2595 INTO
2596 l_currency_code
2597 FROM
2598 ar_gta_system_parameters_all
2599 WHERE org_id=p_org_id;
2600
2601 EXCEPTION
2602 WHEN no_data_found THEN
2603 --report AR_GTA_MISSING_ERROR
2604 fnd_message.set_name('AR', 'AR_GTA_MISSING_ERROR');
2605 l_error_string := fnd_message.get();
2606 -- output this error
2607 fnd_file.put_line(fnd_file.output, '<?xml version="1.0" encoding="UTF-8" ?>
2608 <TransferReport>
2609 <ReportFailed>Y</ReportFailed>
2610 <ReportFailedMsg>'||l_error_string||'</ReportFailedMsg>
2611 <TransferReport>');
2612
2613
2614 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2615 THEN
2616 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
2617 , G_MODULE_PREFIX || l_procedure_name
2618 , l_error_string);
2619 END IF;
2620 RAISE;
2621 END;
2622
2623 --select bank info from Credit memo payment details
2624 BEGIN
2625 SELECT
2626 h.paying_customer_id
2627 ,h.paying_site_use_id
2628 ,h.payment_trxn_extension_id
2629 --Yao Zhang add begin for bug#8404856
2630 ,h.bill_to_customer_id
2631 ,h.bill_to_site_use_id
2632 --Yao Zhang add end for bug#8404856
2633 INTO
2634 l_paying_customer_id
2635 , l_paying_site_use_id
2636 , l_trxn_extension_id
2637 --Yao Zhang add begin for bug#8404856
2638 ,l_bill_to_customer_id
2639 ,l_bill_to_site_use_id
2640 --Yao Zhang add end for bug#8404856
2641 FROM
2642 ra_customer_trx_all h
2643 WHERE h.customer_trx_id = p_customer_trx_id ;
2644 EXCEPTION
2645 WHEN no_data_found THEN
2646 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2647 THEN
2648 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2649 , G_MODULE_PREFIX || l_procedure_name
2650 , 'no date found when select bank information');
2651 END IF;
2652 END;
2653
2654 -- select bank information
2655 IF (l_paying_customer_id is not null) and (l_trxn_extension_id IS NOT NULL)--yao zhang changed for bug 8234250
2656 THEN
2657
2658 BEGIN
2659 SELECT
2660 u.instrument_id
2661 , b.bank_account_name
2662 --Modified by Yao begin for bug#8605196 to support Bank name in Chinese
2663 --, b.bank_name
2664 , decode(bhp.organization_name_phonetic
2665 ,null, bhp.party_name
2666 ,bhp.organization_name_phonetic)
2667 --, b.bank_branch_name
2668 , decode(brhp.organization_name_phonetic
2669 ,null, brhp.party_name
2670 ,brhp.organization_name_phonetic)
2671 --Modified by Yao end for bug#8605196 to support Bank name in Chinese
2672 INTO
2673 l_instrument_id
2674 , l_bank_account_name
2675 , l_bank_name
2676 , l_bank_branch_name
2677 FROM IBY_CREDITCARD C,
2678 IBY_CREDITCARD_ISSUERS_VL I,
2679 IBY_EXT_BANK_ACCOUNTS_V B,
2680 IBY_FNDCPT_PMT_CHNNLS_VL P,
2681 IBY_FNDCPT_TX_EXTENSIONS X,
2682 IBY_FNDCPT_TX_OPERATIONS OP,
2683 IBY_PMT_INSTR_USES_ALL U,
2684 HZ_PARTIES HZP,
2685 FND_APPLICATION A,
2686 --Add by Yao for bug#8605196 to support bank name in Chinese
2687 HZ_PARTIES bhp,
2688 HZ_PARTIES brhp
2689 WHERE (x.instr_assignment_id = u.instrument_payment_use_id(+))
2690 AND (DECODE(u.instrument_type, 'CREDITCARD', u.instrument_id, NULL) =
2691 c.instrid(+))
2692 AND (DECODE(u.instrument_type, 'BANKACCOUNT', u.instrument_id, NULL) =
2693 b.bank_account_id(+))
2694 AND (x.payment_channel_code = p.payment_channel_code)
2695 AND (c.card_issuer_code = i.card_issuer_code(+))
2696 AND (x.trxn_extension_id = op.trxn_extension_id(+))
2697 AND (c.card_owner_id = hzp.party_id(+))
2698 AND (x.origin_application_id = a.application_id)
2699 AND x.trxn_extension_id = l_trxn_extension_id
2700 --Add by Yao for bug#8605196 to support bank name in Chinese
2701 AND b.bank_party_id=bhp.party_id(+)
2702 AND b.branch_party_id=brhp.party_id(+);
2703
2704 EXCEPTION
2705 WHEN no_data_found THEN
2706 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2707 THEN
2708 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2709 , G_MODULE_PREFIX || l_procedure_name
2710 , 'no date found when select bank information');
2711 END IF;
2712 END;
2713
2714 BEGIN
2715 SELECT
2716 bank_account_num
2717 INTO
2718 l_bank_account_num
2719 FROM
2720 IBY_EXT_BANK_ACCOUNTS
2721 WHERE
2722 ext_bank_account_id = l_instrument_id;
2723 EXCEPTION
2724 WHEN no_data_found THEN
2725 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2726 THEN
2727 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2728 , G_MODULE_PREFIX || l_procedure_name
2729 , 'no date found when select bank information');
2730 END IF;
2731 END;
2732 END IF;/*l_trxn_extension_id IS NOT NULL*/
2733
2734
2735 --If payment detail for CM is null, select bank info from AR invoice payment detail
2736 IF l_bank_account_num is null
2737 THEN
2738 BEGIN
2739 SELECT
2740 h.paying_customer_id
2741 ,h.paying_site_use_id
2742 ,h.payment_trxn_extension_id
2743 INTO
2744 l_ori_paying_customer_id
2745 , l_ori_paying_site_use_id
2746 , l_ori_trxn_extension_id
2747 FROM
2748 ra_customer_trx_all h
2749
2750 WHERE h.customer_trx_id = p_original_trx_id ;
2751 EXCEPTION
2752 WHEN no_data_found THEN
2753 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2754 THEN
2755 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2756 , G_MODULE_PREFIX || l_procedure_name
2757 , 'no date found when select header info');
2758 END IF;
2759 END;
2760
2761 -- select bank information
2762 IF (l_ori_paying_customer_id is not null) and (l_ori_trxn_extension_id IS NOT NULL)--yao zhang changed for bug 8234250
2763 THEN
2764 BEGIN
2765 SELECT
2766 u.instrument_id
2767 , b.bank_account_name
2768 --Modified by Yao begin for bug#8605196 to support Bank name in Chinese
2769 --, b.bank_name
2770 , decode(bhp.organization_name_phonetic
2771 ,null, bhp.party_name
2772 ,bhp.organization_name_phonetic)
2773 --, b.bank_branch_name
2774 , decode(brhp.organization_name_phonetic
2775 ,null, brhp.party_name
2776 ,brhp.organization_name_phonetic)
2777 --Modified by Yao end for bug#8605196 to support Bank name in Chinese
2778 INTO
2779 l_instrument_id
2780 , l_bank_account_name
2781 , l_bank_name
2782 , l_bank_branch_name
2783 FROM IBY_CREDITCARD C,
2784 IBY_CREDITCARD_ISSUERS_VL I,
2785 IBY_EXT_BANK_ACCOUNTS_V B,
2786 IBY_FNDCPT_PMT_CHNNLS_VL P,
2787 IBY_FNDCPT_TX_EXTENSIONS X,
2788 IBY_FNDCPT_TX_OPERATIONS OP,
2789 IBY_PMT_INSTR_USES_ALL U,
2790 HZ_PARTIES HZP,
2791 FND_APPLICATION A,
2792 --Add by Yao for bug#8605196 to support bank name in Chinese
2793 HZ_PARTIES bhp,
2794 HZ_PARTIES brhp
2795 WHERE (x.instr_assignment_id = u.instrument_payment_use_id(+))
2796 AND (DECODE(u.instrument_type, 'CREDITCARD', u.instrument_id, NULL) =
2797 c.instrid(+))
2798 AND (DECODE(u.instrument_type, 'BANKACCOUNT', u.instrument_id, NULL) =
2799 b.bank_account_id(+))
2800 AND (x.payment_channel_code = p.payment_channel_code)
2801 AND (c.card_issuer_code = i.card_issuer_code(+))
2802 AND (x.trxn_extension_id = op.trxn_extension_id(+))
2803 AND (c.card_owner_id = hzp.party_id(+))
2804 AND (x.origin_application_id = a.application_id)
2805 AND x.trxn_extension_id = l_ori_trxn_extension_id
2806 --Add by Yao to for bug#8605196 support bank name in Chinese
2807 AND b.bank_party_id=bhp.party_id(+)
2808 AND b.branch_party_id=brhp.party_id(+);
2809
2810 EXCEPTION
2811 WHEN no_data_found THEN
2812 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2813 THEN
2814 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2815 , G_MODULE_PREFIX || l_procedure_name
2816 , 'no date found when select bank information');
2817 END IF;
2818 END;
2819
2820 BEGIN
2821 SELECT
2822 bank_account_num
2823 INTO
2824 l_bank_account_num
2825 FROM
2826 IBY_EXT_BANK_ACCOUNTS
2827 WHERE
2828 ext_bank_account_id = l_instrument_id;
2829 EXCEPTION
2830 WHEN no_data_found THEN
2831 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2832 THEN
2833 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2834 , G_MODULE_PREFIX || l_procedure_name
2835 , 'no date found when select bank information');
2836 END IF;
2837 END;
2838
2839 END IF;/*l_trxn_extension_id IS NOT NULL*/
2840
2841 END IF; --// IF l_bank_account_num IS NULL
2842
2843 -- if the payment detail for AR invoice is null. then select CM paying customer bank info
2844 IF l_bank_account_num IS NULL
2845 THEN
2846 --Yao Zhang add begin for bug#8404856
2847 IF l_paying_customer_id IS NOT NULL
2848 THEN
2849 l_valid_customer_id:=l_paying_customer_id;
2850 l_valid_site_use_id:=l_paying_site_use_id;
2851 ELSIF l_ori_paying_customer_id IS NOT NULL
2852 THEN
2853 l_valid_customer_id:=l_ori_paying_customer_id;
2854 l_valid_site_use_id:=l_ori_paying_site_use_id;
2855 ELSE
2856 l_valid_customer_id:=l_bill_to_customer_id;
2857 l_valid_site_use_id:=l_bill_to_site_use_id;
2858 END IF;
2859 --Yao Zhang add end for bug#8404856
2860
2861
2862 BEGIN
2863 -- get party id of paying customer
2864 SELECT
2865 party_id
2866 INTO
2867 l_paying_party_id
2868 FROM
2869 HZ_CUST_ACCOUNTS
2870 WHERE
2871 CUST_ACCOUNT_ID = l_valid_customer_id ;--Yao Zhang modified for bug#8404856
2872 -- get ext_payer_id by party id , site account id , site use id and org id.
2873 SELECT
2874 ext_payer_id
2875 INTO
2876 l_ext_payer_id
2877 FROM
2878 IBY_EXTERNAL_PAYERS_ALL
2879 WHERE party_id = l_paying_party_id
2880 AND CUST_ACCOUNT_ID = l_valid_customer_id--Yao Zhang modified for bug#8404856
2881 AND ACCT_SITE_USE_ID = l_valid_site_use_id--Yao Zhang modified for bug#8404856
2882 AND ORG_ID = p_org_id -- org id
2883 AND org_type = 'OPERATING_UNIT' -- ou
2884 AND payment_function = 'CUSTOMER_PAYMENT';
2885
2886 -- get bank account name and bank account num
2887 SELECT
2888 bank_account_name
2889 , bank_account_num
2890 , bank_id
2891 , branch_id
2892 INTO
2893 l_bank_account_name
2894 , l_bank_account_num
2895 , l_bank_id
2896 , l_bank_branch_id
2897 FROM (SELECT ibybanks.bank_account_name
2898 , ibybanks.bank_account_num
2899 , ibybanks.bank_id
2900 , ibybanks.branch_id
2901 FROM IBY_PMT_INSTR_USES_ALL ExtPartyInstrumentsEO
2902 , IBY_EXT_BANK_ACCOUNTS ibybanks
2903 WHERE ibybanks.EXT_BANK_ACCOUNT_ID = ExtPartyInstrumentsEO.instrument_id
2904 AND ExtPartyInstrumentsEO.INSTRUMENT_TYPE = 'BANKACCOUNT'
2905 AND ExtPartyInstrumentsEO.EXT_PMT_PARTY_ID = l_ext_payer_id
2906 AND ExtPartyInstrumentsEO.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
2907 AND (ibybanks.currency_code = l_currency_code OR ibybanks.currency_code IS NULL)
2908 AND SYSDATE BETWEEN nvl(ExtPartyInstrumentsEO.START_DATE, to_date('1900-01-01','RRRR-MM-DD'))
2909 AND nvl(ExtPartyInstrumentsEO.END_DATE, to_date('3000-01-01','RRRR-MM-DD'))
2910 ORDER BY ibybanks.currency_code,ExtPartyInstrumentsEO.ORDER_OF_PREFERENCE)
2911 WHERE ROWNUM =1;
2912
2913 -- get bank name
2914 --Modified begin by Yao for bug#8605196 to support bank name in Chinese
2915 SELECT
2916 decode(organization_name_phonetic
2917 ,null, party_name
2918 ,organization_name_phonetic)
2919 --Modified end by Yao for bug#8605196 to support bank name in Chinese
2920 INTO
2921 l_bank_name
2922 FROM
2923 HZ_PARTIES
2924 WHERE
2925 party_id = l_bank_id;
2926
2927 -- get bank branch name
2928 SELECT
2929 --Modified begin by Yao for bug#8605196 to support bank name in Chinese
2930 decode(organization_name_phonetic
2931 ,null, party_name
2932 ,organization_name_phonetic)
2933 --Modified end by Yao for bug#8605196 to support bank name in Chinese
2934 INTO
2935 l_bank_branch_name
2936 FROM
2937 HZ_PARTIES
2938 WHERE party_id = l_bank_branch_id;
2939 EXCEPTION
2940 WHEN no_data_found THEN
2941 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2942 THEN
2943 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2944 , G_MODULE_PREFIX || l_procedure_name
2945 , 'no date found when select bank information');
2946 END IF;
2947 END;/*l_apba_bank_account_num IS NULL*/
2948 END IF;
2949
2950 x_bank_name := l_bank_name;
2951 x_bank_branch_name := l_bank_branch_name;
2952 x_bank_account_num := l_bank_account_num;
2953 x_bank_account_name := l_bank_account_name;
2954
2955 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2956 THEN
2957 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2958 , G_MODULE_PREFIX || l_procedure_name
2959 ,'End Procedure. ');
2960 END IF;
2961
2962 EXCEPTION
2963 WHEN OTHERS THEN
2964 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2965 THEN
2966 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
2967 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
2968 , Sqlcode||Sqlerrm);
2969 END IF;
2970 RAISE;
2971 END Get_CM_Bank_Info;
2972
2973 --==========================================================================
2974 -- Procedure NAME:
2975 --
2976 -- verify_tax_line Public
2977 --
2978 -- DESCRIPTION:
2979 --
2980 -- Verify the tax lines number of a trx line, is it is not 1 , return fail
2981 --
2982 -- PARAMETERS:
2983
2984 -- p_trx_line_id IN NUMBER
2985 -- p_tax_type_code IN VARCHAR2
2986 -- x_status OUT NOCOPY NUMBER
2987 -- x_tax_line_id OUT NOCOPY zx_lines.tax_line_id%TYPE
2988 --
2989 -- DESIGN REFERENCES:
2990 --
2991 --
2992 -- CHANGE HISTORY:
2993 --
2994 -- 13-Oct-2005: JIM.Zheng Created
2995 --
2996 --===========================================================================
2997 PROCEDURE Verify_Tax_Line
2998 (p_trx_line_id IN NUMBER
2999 , p_tax_type_code IN VARCHAR2
3000 , p_currency_code IN VARCHAR2
3001 , x_status OUT NOCOPY NUMBER
3002 , x_tax_line_id OUT NOCOPY zx_lines.tax_line_id%TYPE
3003 )
3004 IS
3005 l_tax_line_count NUMBER;
3006 l_procedure_name VARCHAR2(50) := 'verify_tax_line';
3007 l_tax_line_id zx_lines.tax_line_id%TYPE;
3008 l_trx_id ra_customer_trx_all.customer_trx_id%TYPE;--jogen bug5212702 May-17,2006
3009
3010 BEGIN
3011
3012 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3013 THEN
3014 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3015 , G_MODULE_PREFIX || l_procedure_name
3016 ,'begin Procedure. ');
3017 END IF;
3018
3019 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3020 THEN
3021 log( 'Begin Verify_Tax_line---');
3022 log( 'p_trx_line_id :'||p_trx_line_id);
3023 log( 'p_tax_type_code :'||p_tax_type_code);
3024 log( 'p_currency_code :'||p_currency_code);
3025 END IF;
3026
3027 -- init status
3028 x_status := 0 ;
3029
3030 -- get the tax lines count of Ar line which the tax type is VAT
3031 SELECT customer_trx_id
3032 INTO l_trx_id
3033 FROM ra_customer_trx_lines_all
3034 WHERE customer_trx_line_id=p_trx_line_id;
3035
3036 SELECT
3037 COUNT(*)
3038 INTO
3039 l_tax_line_count
3040 FROM
3041 zx_lines tax
3042 WHERE tax.trx_line_id = p_trx_line_id
3043 AND tax.entity_code = 'TRANSACTIONS'
3044 AND application_id = 222
3045 AND tax.trx_level_type = 'LINE'
3046 AND tax.tax_currency_code = p_currency_code
3047 AND tax.tax_type_code = p_tax_type_code
3048 AND tax.event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--jogen bug5212702 May-17,2006
3049 AND tax.trx_id=l_trx_id; --jogen bug5212702 May-17,2006
3050
3051
3052 -- if the line number is 0, then x_status = -1
3053 -- if the line number is 1, then x_status = 0
3054 -- if the line number > 1 , then x_status = 1
3055 IF l_tax_line_count = 0
3056 THEN
3057 x_status := -1;
3058 ELSIF l_tax_line_count = 1
3059 THEN
3060 x_status := 0;
3061 BEGIN
3062 SELECT
3063 tax.tax_line_id
3064 INTO
3065 l_tax_line_id
3066 FROM
3067 zx_lines tax
3068 WHERE tax.trx_line_id = p_trx_line_id
3069 AND tax.application_id = 222
3070 AND tax.trx_level_type = 'LINE'
3071 AND tax.entity_code = 'TRANSACTIONS'
3072 AND tax.tax_type_code = p_tax_type_code
3073 AND tax.event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--jogen bug5212702 May-17,2006
3074 AND tax.trx_id=l_trx_id; --jogen bug5212702 May-17,2006
3075 END;
3076 ELSE
3077 x_status := 1;
3078
3079 END IF;/*l_tax_line_count = 0*/
3080
3081 x_tax_line_id := l_tax_line_id;
3082
3083 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3084 THEN
3085 log( 'x_status : '||x_status);
3086 END IF;
3087
3088 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3089 THEN
3090 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3091 , G_MODULE_PREFIX || l_procedure_name
3092 ,'End Procedure. ');
3093 END IF;
3094
3095 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3096 THEN
3097 log( 'End Verify_Tax_line---');
3098 END IF;
3099
3100 EXCEPTION
3101 WHEN OTHERS THEN
3102 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3103 THEN
3104 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
3105 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
3106 , Sqlcode||Sqlerrm);
3107 END IF;
3108 RAISE;
3109
3110
3111 END Verify_Tax_Line;
3112 --==========================================================================
3113 -- Procedure NAME:
3114 --
3115 -- get_info_from_ebtax Public
3116 --
3117 -- DESCRIPTION:
3118 --
3119 -- This function get data from ebtax
3120 --
3121 -- PARAMETERS:
3122 -- p_org_id IN NUMBER
3123 -- p_trx_id IN NUMBER
3124 -- p_trx_line_id IN NUMBER
3125 -- p_tax_type_code IN VARCHAR2
3126 -- x_tax_amount OUT NOCOPY NUMBER
3127 -- x_taxable_amount OUT NOCOPY NUMBER
3128 -- x_trx_line_quantity OUT NOCOPY NUMBER
3129 -- x_tax_rate OUT NOCOPY NUMBER
3130 -- x_unit_selling_price OUT NOCOPY NUMBER
3131 -- x_taxable_amount OUT NOCOPY NUMBER
3132 -- x_fp_registration_number OUT NOCOPY VARCHAR2
3133 -- x_tp_registration_number OUT NOCOPY VARCHAR2
3134 -- x_status OUT NOCOPY NUMBER
3135 -- x_invoice_type OUT NOCOPY VARCHAR2
3136 -- x_error_buffer OUT NOCOPY VARCHAR2
3137 --
3138 --
3139 -- DESIGN REFERENCES:
3140 --
3141 --
3142 -- CHANGE HISTORY:
3143 --
3144 -- 17-SEP-2005: JIM.Zheng Created
3145 -- 28-DEC-2007: Subba Changed for R12.1
3146 -- 13-May-2009 Yao Zhang changed for bug#5604079
3147 -- 12-Mar-2010 Yao Zhang changed for bug#9369455
3148 -- 24-Dec-2010 Qiong Liu Fix bug#10311408
3149 -- 11-Jan-2011 Qiong Liu Fix bug#10638369
3150 --===========================================================================
3151 PROCEDURE Get_Info_From_Ebtax
3152 (p_org_id IN NUMBER
3153 ,p_trx_id IN NUMBER
3154 ,p_trx_line_id IN NUMBER
3155 ,p_tax_type_code IN VARCHAR2
3156 ,x_tax_amount OUT NOCOPY NUMBER
3157 ,x_taxable_amount OUT NOCOPY NUMBER
3158 ,x_trx_line_quantity OUT NOCOPY NUMBER
3159 ,x_tax_rate OUT NOCOPY NUMBER
3160 ,x_unit_selling_price OUT NOCOPY NUMBER
3161 ,x_tax_curr_unit_price OUT NOCOPY NUMBER
3162 ,x_taxable_amount_org OUT NOCOPY NUMBER
3163 ,x_fp_registration_number OUT NOCOPY VARCHAR2
3164 ,x_tp_registration_number OUT NOCOPY VARCHAR2
3165 ,x_status OUT NOCOPY NUMBER
3166 ,x_invoice_type OUT NOCOPY VARCHAR2
3167 ,x_error_buffer OUT NOCOPY VARCHAR2
3168 )
3169 IS
3170 l_procedure_name VARCHAR2(30) := 'get_info_from_ebtax';
3171 l_lines_status NUMBER;
3172 l_status NUMBER;
3173 l_error_buffer VARCHAR2(180);
3174 l_tax_registration_number zx_lines.tax_registration_number%TYPE;
3175 l_tax_registration_count NUMBER;
3176 l_tax_line_id zx_lines.tax_line_id%TYPE;
3177 l_tax_rate zx_lines.tax_rate%TYPE;
3178 l_unit_price zx_lines.unit_price%TYPE;
3179 l_trx_line_quantity zx_lines.trx_line_quantity%TYPE;
3180 l_tax_amount zx_lines.tax_amt_funcl_curr%TYPE;
3181 l_taxable_amount zx_lines.taxable_amt_funcl_curr%TYPE;
3182 l_tax_curr_conversion_rate zx_lines.tax_currency_conversion_rate%TYPE;
3183 l_tp_registration_number zx_registrations.registration_number%TYPE;
3184 l_fp_reg_number_count NUMBER;
3185 l_amount zx_lines.taxable_amt%TYPE;
3186 l_currency_code VARCHAR2(30);
3187 l_error_string VARCHAR2(500);
3188 --added by subba for R12.1
3189 l_invoice_type ar_gta_tax_limits_all.invoice_type%type;
3190
3191 tax_error_for_recycle EXCEPTION; --exception for tax_amount check for recycle Invoice
3192
3193 BEGIN
3194 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3195 THEN
3196 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3197 , G_MODULE_PREFIX || l_procedure_name
3198 ,'begin Procedure. ');
3199 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3200 , G_MODULE_PREFIX || l_procedure_name
3201 ,'p_trx_line_id: '||p_trx_line_id);
3202
3203 END IF;
3204
3205 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3206 THEN
3207 log( 'Begin Get_Info_From_Ebtax --');
3208 log( 'p_org_id : '||p_org_id);
3209 log( 'p_trx_id : '||p_trx_id);
3210 log( 'p_trx_line_id : '||p_trx_line_id);
3211 log( 'p_tax_type_code : '||p_tax_type_code);
3212 log( 'x_status : '||x_status);
3213 log( 'x_status : '||x_status);
3214 log( 'x_status : '||x_status);
3215 END IF;
3216
3217 -- init status
3218 x_status := 0 ;
3219
3220 BEGIN
3221 SELECT
3222 gt_currency_code
3223 INTO
3224 l_currency_code
3225 FROM
3226 ar_gta_system_parameters_all
3227 WHERE org_id=p_org_id;
3228
3229 EXCEPTION
3230 WHEN no_data_found THEN
3231 --report AR_GTA_MISSING_ERROR
3232 fnd_message.set_name('AR', 'AR_GTA_SYS_CONFIG_MISSING');
3233 l_error_string := fnd_message.get();
3234 -- output this error
3235 fnd_file.put_line(fnd_file.output,'<?xml version="1.0" encoding="UTF-8"?>
3236 <TransferReport>
3237 <ReportFailed>Y</ReportFailed>
3238 <ReportFailedMsg>'||l_error_string||'</ReportFailedMsg>
3239 <TransferReport>');
3240
3241
3242 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3243 THEN
3244 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
3245 , G_MODULE_PREFIX || l_procedure_name
3246 , l_error_string);
3247 END IF;
3248
3249 RAISE;
3250 END;
3251
3252
3253 -- verify tax Line number.
3254 verify_tax_line
3255 (p_trx_line_id => p_trx_line_id
3256 , p_tax_type_code => p_tax_type_code
3257 , p_currency_code => l_currency_code
3258 , x_status => l_lines_status
3259 , x_tax_line_id => l_tax_line_id
3260 );
3261
3262
3263 -- if the line count is 0, return -1 and the line can't be transfer and don't
3264 -- throw any exception
3265 -- if the line count > 1 , return 1 and throw exception
3266 -- if the line count = 1 , get data from zx_lines and transfer it to GTA
3267 -- 29-JUN-2006 Upated by Shujuan, insert Tax_currency_conversion_rate
3268 -- into l_tax_curr_conversion_rate in order to calculate the unit price of
3269 -- tax concurrency for bug 5168900
3270 IF l_lines_status = 0
3271 THEN
3272 SELECT
3273 tax.tax_line_id
3274 , tax.hq_estb_reg_number
3275 --Qiong modified for bug 10311408 change taxable_amt_tax_curr to (current exchange rate)*unrounded_taxable_amt
3276 ,nvl(tax.tax_currency_conversion_rate,1)*tax.unrounded_taxable_amt
3277 -- , tax.taxable_amt_tax_curr
3278 , tax.tax_rate
3279 , tax.tax_amt_tax_curr
3280 , tax.unit_price
3281 , tax.trx_line_quantity
3282 , tax.taxable_amt
3283 , tax.Tax_currency_conversion_rate
3284 INTO
3285 l_tax_line_id
3286 , l_tax_registration_number
3287 , l_taxable_amount
3288 , l_tax_rate
3289 , l_tax_amount
3290 , l_unit_price
3291 , l_trx_line_quantity
3292 , l_amount
3293 , l_tax_curr_conversion_rate
3294 FROM
3295 zx_lines tax
3296 WHERE tax.trx_line_id = p_trx_line_id
3297 AND tax.entity_code = 'TRANSACTIONS'
3298 AND application_id = 222
3299 AND tax.trx_level_type = 'LINE'
3300 AND tax.tax_currency_code = l_currency_code
3301 AND tax.tax_type_code = p_tax_type_code
3302 --jogen bug5212702 May-17,2006
3303 AND tax.event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')
3304 AND tax.trx_id=p_trx_id; --jogen bug5212702 May-17,2006
3305
3306
3307 -- find the registration number from system option
3308 -- if the number is exist then go ahead
3309 -- if the number is not exist , then throw a exception
3310 IF l_tax_registration_number IS NULL
3311 THEN
3312 -- throw first party registion number is null exception
3313 x_status := 1;
3314 l_error_buffer := 'AR_GTA_FP_TAXREG_MISSING';
3315 ELSE /*l_tax_registration_number IS NULL*/
3316 -- find the first party registion number in parameter
3317 SELECT
3318 COUNT(*)
3319 INTO
3320 l_fp_reg_number_count
3321 FROM
3322 ar_gta_tax_limits_all
3323 WHERE org_id = p_org_id
3324 AND fp_tax_registration_number = l_tax_registration_number;
3325
3326 IF l_fp_reg_number_count = 0
3327 THEN
3328 x_status := 2;
3329 l_error_buffer := 'AR_GTA_SYS_CONFIG_MISSING';
3330 ELSE
3331 --if there no exception when get first party registration number then
3332 --get third party registration number
3333
3334 get_tp_tax_registration_number
3335 ( p_trx_id => p_trx_id
3336 , p_tax_line_id => l_tax_line_id
3337 , x_tp_tax_registration_number => l_tp_registration_number
3338 );
3339 END IF;/*l_fp_reg_number_count = 0*/
3340
3341 --following code added by subba for R12.1
3342
3343 --IF l_tax_registration_number IS NOT NULL THEN
3344
3345 l_invoice_type := get_invoice_type( p_org_id => p_org_id
3346 ,p_customer_trx_id => p_trx_id
3347 ,p_trx_line_id => p_trx_line_id
3348 ,p_fp_tax_registration_num => l_tax_registration_number );
3349 --END IF;
3350
3351 -- throw a missing tp registration number exception when invoice type is not C
3352
3353
3354 -- 2 stands for Common Invoice, 1 for Recycle Invoice, 0 for Special Invoice.
3355
3356 -- to keep consistent with the flat file format of Asino.
3357
3358 IF l_invoice_type IS NULL THEN
3359 x_status := 1;
3360 l_error_buffer := 'AR_GTA_INVLD_TAXPAYER_TYPE';
3361
3362
3363 ELSE /*IF l_invoice_type IS NULL*/
3364
3365 IF l_invoice_type <> '2' THEN --if not common VAT Invoice
3366
3367 IF l_tp_registration_number IS NULL THEN
3368 -- throw third party registion number is null exception
3369 x_status := 1;
3370 l_error_buffer := 'AR_GTA_TP_TAXREG_MISSING';
3371 ELSE /*l_tp_registration_number IS NULL*/
3372 x_tp_registration_number := l_tp_registration_number;
3373 END IF;/*l_tp_registration_number IS NULL*/
3374 END IF; /* l_invoice_type <>'2'*/
3375
3376 --END IF;
3377
3378 -- validate tax rate and tax amount are zero when invoice type is R, added by Subba for R12.1
3379
3380 IF l_invoice_type = '1' -- 1 stands for Recycle Invoice
3381 THEN
3382 IF (l_tax_rate <> 0 OR l_tax_amount <> 0) THEN
3383 x_status := 1;
3384 l_error_buffer := 'AR_GTA_TAX_ERROR_RECYCLE';
3385
3386 END IF;
3387 END IF;/*l_invoice_type = '1'*/
3388
3389 END IF;/*IF l_invoice_type IS NULL*/
3390
3391 IF l_taxable_amount IS NULL
3392 OR l_tax_rate IS NULL
3393 OR l_tax_amount IS NULL
3394 OR l_unit_price IS NULL
3395 OR l_trx_line_quantity IS NULL
3396 THEN
3397 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3398 THEN
3399 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3400 , G_MODULE_PREFIX || l_procedure_name
3401 ,'The data come from ebtax is null. ');
3402 END IF;
3403 END IF;
3404
3405 END IF; /*l_tax_registration_number IS NULL*/
3406
3407
3408 ELSIF l_lines_status = -1
3409 THEN
3410 x_status := -1 ;
3411 ELSIF l_lines_status = 1
3412 THEN
3413 -- throw AR_GTA_MULTI_TAXLINE exception
3414 x_status := 1;
3415 l_error_buffer := 'AR_GTA_MULTI_TAXLINE';
3416
3417 END IF;
3418
3419 -- output the status
3420 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3421 THEN
3422 log( 'x_status '||x_status);
3423 END IF;
3424
3425 x_tax_amount := l_tax_amount;
3426 x_taxable_amount := l_taxable_amount;
3427 x_trx_line_quantity := l_trx_line_quantity;
3428 x_tax_rate := l_tax_rate/100;
3429 x_unit_selling_price := l_unit_price;
3430 -- x_tax_curr_unit_price := l_unit_price * l_tax_curr_conversion_rate;--Yao Zhang changed for bug 5604079/9369455
3431 x_tax_curr_unit_price := round(l_taxable_amount,2)/l_trx_line_quantity;--Qiong Liu changed for bug 10638369
3432 x_fp_registration_number := l_tax_registration_number;
3433 x_tp_registration_number := l_tp_registration_number;
3434 x_taxable_amount_org := l_amount;
3435 x_error_buffer := l_error_buffer;
3436 x_invoice_type := l_invoice_type;
3437
3438
3439 -- 29-JUN-2006 Added by Shujuan, calculate the unit price of tax currency
3440 -- and return it for bug 5168900
3441 --x_tax_curr_unit_price := round(l_unit_price * l_tax_curr_conversion_rate);
3442
3443
3444
3445 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3446 THEN
3447 log( 'End Get_Info_From_Ebtax --');
3448 END IF;
3449
3450 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3451 THEN
3452 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3453 , G_MODULE_PREFIX || l_procedure_name
3454 ,'End Procedure. ');
3455 END IF;
3456
3457 EXCEPTION
3458 /* WHEN tax_error_for_recycle THEN --added by subba for R12.1
3459
3460 fnd_message.SET_NAME('AR', 'AR_GTA_TAX_ERROR_RECYCLE');
3461 l_error_string := fnd_message.get();
3462 -- begin log
3463 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3464 THEN
3465 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
3466 , G_MODULE_PREFIX || l_procedure_name
3467 , 'tax rate and tax amount should be zero for Recycle Invoices');
3468 END IF;
3469 RAISE;
3470
3471 -- end log
3472 RAISE;*/
3473 WHEN OTHERS THEN
3474 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3475 THEN
3476 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
3477 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
3478 , Sqlcode||Sqlerrm);
3479 END IF;
3480 RAISE;
3481
3482 END Get_Info_From_Ebtax;
3483
3484 --==========================================================================
3485 -- Procedure NAME:
3486 --
3487 -- get_tp_tax_registration_number Public
3488 --
3489 -- DESCRIPTION:
3490 --
3491 -- This function third party registration number by trx line id
3492 --
3493 -- PARAMETERS:
3494 -- In:
3495
3496 -- p_tax_line_id in number
3497 -- OUT:
3498 -- x_tp_tax_registration_number OUT NOCOPY VARCHAR2
3499 --
3500 --
3501 --
3502 -- DESIGN REFERENCES:
3503 --
3504 --
3505 -- CHANGE HISTORY:
3506 --
3507 -- 17-AUG-2005: JIM.Zheng Created
3508 -- Mar-21, 2006 Jogen Hu Bug 5088458
3509 -- 21/08/2012 Yao zhang fix bug 14500600
3510 --===========================================================================
3511 PROCEDURE Get_Tp_Tax_Registration_Number
3512 ( p_trx_id IN NUMBER
3513 , p_tax_line_id IN NUMBER
3514 , x_tp_tax_registration_number OUT NOCOPY VARCHAR2
3515 )
3516 IS
3517 l_procedure_name VARCHAR2(80) := 'get_tp_tax_registration_number';
3518 l_bill_to_site_use_id ra_customer_trx_all.bill_to_site_use_id%TYPE;
3519 l_ra_cust_trx_id ra_customer_trx_all.customer_trx_id%TYPE;
3520 l_tax_regime_code zx_lines.tax_regime_code%TYPE;
3521 l_tax zx_lines.tax%TYPE;
3522 l_tax_jurisdiction_code zx_lines.tax_jurisdiction_code%TYPE;
3523 l_tax_determine_date zx_lines.tax_determine_date%TYPE;
3524 l_party_tax_profile_id zx_party_tax_profile.party_tax_profile_id%TYPE;
3525 l_tax_registration_number zx_registrations.registration_number%TYPE;
3526 l_reg_tax_regime_code zx_registrations.tax_regime_code%TYPE;
3527 l_reg_tax zx_registrations.tax%TYPE;
3528 l_reg_tax_jursidiction_code zx_registrations.tax_jurisdiction_code%TYPE;
3529
3530 l_cust_acct_site_id hz_cust_site_uses_all.cust_acct_site_id%TYPE;
3531 l_party_site_id hz_cust_acct_sites_all.party_site_id%TYPE;
3532 l_party_id HZ_CUST_ACCOUNTS.party_id%TYPE;
3533
3534 l_tax_registration_count NUMBER;
3535 l_tax_profile_status NUMBER;
3536
3537 l_tp_registration_number zx_registrations.registration_number%TYPE;
3538 l_tp_registration_number_a zx_registrations.registration_number%TYPE;
3539 l_tp_registration_number_b zx_registrations.registration_number%TYPE;
3540 l_tp_registration_number_c zx_registrations.registration_number%TYPE;
3541
3542 l_return_status VARCHAR2(200);
3543 l_msg_count NUMBER;
3544 l_msg_data VARCHAR2(2000);
3545
3546 i NUMBER;
3547 l_indexO NUMBER;
3548 CURSOR c_tp_reg_num
3549 (p_party_tax_profile_id NUMBER
3550 ,p_tax_regime_code VARCHAR2
3551 ,p_tax VARCHAR2
3552 ,p_tax_jurisdiction_code VARCHAR2
3553 ,p_tax_determine_date Date
3554 )
3555 IS
3556 SELECT
3557 reg.registration_number
3558 ,reg.tax_regime_code
3559 ,reg.tax
3560 ,reg.tax_jurisdiction_code
3561 INTO
3562 l_tax_registration_number
3563 ,l_reg_tax_regime_code
3564 ,l_reg_tax
3565 ,l_reg_tax_jursidiction_code
3566 FROM
3567 zx_registrations reg
3568 WHERE reg.party_tax_profile_id =p_party_tax_profile_id
3569 AND (reg.tax is NULL or reg.tax = p_tax)
3570 AND reg.tax_regime_code = p_tax_regime_code -- tax_regime_code is not null
3571 AND (reg.tax_jurisdiction_code is NULL or reg.tax_jurisdiction_code = p_tax_jurisdiction_code)
3572 AND p_tax_determine_date >= reg.effective_from
3573 AND (p_tax_determine_date < reg.effective_to OR reg.effective_to IS NULL)
3574 AND reg.registration_number IS NOT NULL;
3575 BEGIN
3576 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3577 THEN
3578 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3579 , G_MODULE_PREFIX || l_procedure_name
3580 ,'begin Procedure. ');
3581
3582 END IF;
3583
3584 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3585 THEN
3586 log( 'Begin Get_Info_From_Ebtax --');
3587 log( 'p_trx_id :'||p_trx_id);
3588 log( 'p_tax_line_id:' ||p_tax_line_id);
3589 END IF;
3590
3591 --get party_site_id from trx_header
3592 BEGIN
3593 -- get site use id by trx id
3594 SELECT
3595 bill_to_site_use_id
3596 INTO
3597 l_bill_to_site_use_id
3598 FROM
3599 ra_customer_trx_all trx_header
3600 WHERE trx_header.customer_trx_id = p_trx_id;
3601
3602 -- get cust_acct_site_id by site_use_id
3603 SELECT
3604 cust_acct_site_id
3605 INTO
3606 l_cust_acct_site_id
3607 FROM
3608 hz_cust_site_uses_all
3609 WHERE SITE_USE_ID = l_bill_to_site_use_id;
3610
3611 --get party_site_id by cust_acct_site_id
3612 SELECT
3613 party_site_id
3614 INTO
3615 l_party_site_id
3616 FROM
3617 hz_cust_acct_sites_all
3618 WHERE cust_acct_site_id = l_cust_acct_site_id;
3619 EXCEPTION
3620 WHEN no_data_found THEN
3621 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3622 THEN
3623 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
3624 , G_MODULE_PREFIX || l_procedure_name
3625 , l_procedure_name||'no data found ');
3626 END IF;/*(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)*/
3627 END;
3628
3629 -- get the tax_regime, tax, tax_jurisdiction by trx line id ;
3630 BEGIN
3631 SELECT
3632 tax.tax_regime_code
3633 , tax.tax
3634 , tax.tax_jurisdiction_code
3635 , tax.tax_determine_date
3636 INTO
3637 l_tax_regime_code
3638 , l_tax
3639 , l_tax_jurisdiction_code
3640 , l_tax_determine_date
3641 FROM
3642 zx_lines tax
3643 WHERE
3644 tax.tax_line_id = p_tax_line_id;
3645
3646 EXCEPTION
3647 WHEN no_data_found THEN
3648 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3649 THEN
3650 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
3651 , G_MODULE_PREFIX || l_procedure_name
3652 , 'no data found ');
3653 END IF;/*(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)*/
3654
3655 END;
3656
3657 -- get tax_profile_id by party site id
3658 BEGIN
3659 SELECT
3660 party_tax_profile_id
3661 INTO
3662 l_party_tax_profile_id
3663 FROM
3664 zx_party_tax_profile tax_prof
3665 WHERE tax_prof.party_id = l_party_site_id
3666 AND tax_prof.party_type_code = 'THIRD_PARTY_SITE';
3667 EXCEPTION
3668 WHEN no_data_found THEN
3669 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3670 THEN
3671 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
3672 , G_MODULE_PREFIX || l_procedure_name
3673 , 'no data found ');
3674 END IF;/*(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)*/
3675 WHEN too_many_rows THEN
3676 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3677 THEN
3678 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
3679 , G_MODULE_PREFIX || l_procedure_name
3680 , 'too many rows ');
3681 END IF;/*(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)*/
3682 END;
3683
3684 /*
3685 dbms_output.put_line('l_bill_to_site_use_id: '||l_bill_to_site_use_id);
3686 dbms_output.put_line('l_tax_regime_code: '||l_tax_regime_code);
3687 dbms_output.put_line('l_tax: '||l_tax);
3688 dbms_output.put_line('l_tax_jurisdiction_code: '||l_tax_jurisdiction_code);
3689 dbms_output.put_line('l_party_tax_profile_id: '||l_party_tax_profile_id);
3690 */
3691 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3692 THEN
3693 log( 'l_bill_to_site_use_id :'||l_bill_to_site_use_id);
3694 log( 'l_tax_regime_code:' ||l_tax_regime_code);
3695 log( 'l_tax:' ||l_tax);
3696 log( 'l_tax_jurisdiction_code:' ||l_tax_jurisdiction_code);
3697 log( 'l_party_tax_profile_id:' ||l_party_tax_profile_id);
3698 END IF;
3699
3700 IF l_tax_regime_code IS NOT NULL AND l_tax IS NOT NULL AND l_tax_determine_date IS NOT NULL
3701 THEN
3702 OPEN c_tp_reg_num (p_party_tax_profile_id => l_party_tax_profile_id
3703 ,p_tax_regime_code => l_tax_regime_code
3704 ,p_tax => l_tax
3705 ,p_tax_jurisdiction_code => l_tax_jurisdiction_code
3706 ,p_tax_determine_date => l_tax_determine_date
3707 );
3708
3709 LOOP
3710 FETCH
3711 c_tp_reg_num
3712 INTO
3713 l_tp_registration_number
3714 , l_reg_tax_regime_code
3715 , l_reg_tax
3716 , l_reg_tax_jursidiction_code;
3717
3718 IF c_tp_reg_num%NOTFOUND
3719 THEN
3720 EXIT;
3721 END IF;
3722
3723 IF l_reg_tax = l_tax AND l_reg_tax_jursidiction_code = l_reg_tax_jursidiction_code
3724 THEN
3725 l_tp_registration_number_a := l_tp_registration_number;
3726 ELSIF l_reg_tax = l_tax AND l_reg_tax_jursidiction_code IS NULL
3727 THEN
3728 l_tp_registration_number_b := l_tp_registration_number;
3729 ELSIF l_reg_tax IS NULL AND l_reg_tax_jursidiction_code IS NULL
3730 THEN
3731 l_tp_registration_number_c := l_tp_registration_number;
3732 END IF;
3733 END LOOP;/*fetch c_tp_reg_num*/
3734 CLOSE c_tp_reg_num; --jogen Hu Apr-4, 2006 bug 5135169
3735
3736 IF l_tp_registration_number_a IS NOT NULL
3737 THEN
3738 x_tp_tax_registration_number := l_tp_registration_number_a;
3739 ELSIF x_tp_tax_registration_number IS NULL AND l_tp_registration_number_b IS NOT NULL
3740 THEN
3741 x_tp_tax_registration_number := l_tp_registration_number_b;
3742 ELSIF x_tp_tax_registration_number IS NULL AND l_tp_registration_number_c IS NOT NULL
3743 THEN
3744 x_tp_tax_registration_number := l_tp_registration_number_c;
3745 END IF;/*l_tp_registration_number_a IS NOT NULL*/
3746
3747 ELSE /*l_tax_regime_code IS NOT NULL AND l_tax IS NOT NULL AND l_tax_determine_date IS NOT NULL*/
3748 x_tp_tax_registration_number := NULL;
3749
3750 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3751 THEN
3752 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3753 , G_MODULE_PREFIX || l_procedure_name
3754 ,'tax or tax_jur is null in zx_lines ');
3755 END IF;
3756
3757 END IF;/*l_tax_regime_code IS NOT NULL AND l_tax IS NOT NULL AND l_tax_determine_date IS NOT NULL*/
3758
3759 --dbms_output.put_line('registration_number: '||l_tax_registration_number);
3760 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3761 THEN
3762 log( 'End Get_Tp_Tax_Registration_Number --');
3763 END IF;
3764
3765 --jogen Mar-21, 2006 bug 5088458
3766 IF x_tp_tax_registration_number IS NULL
3767 THEN
3768 x_tp_tax_registration_number := ZX_API_PUB.get_default_tax_reg(
3769 p_api_version => 1.0
3770 , p_init_msg_list => NULL
3771 , p_commit => NULL
3772 , p_validation_level => NULL
3773 , x_return_status => l_return_status
3774 , x_msg_count => l_msg_count
3775 , x_msg_data => l_msg_data
3776 , p_party_id => l_party_site_id
3777 , p_party_type => 'THIRD_PARTY_SITE'
3778 , p_effective_date => SYSDATE);
3779
3780 IF l_msg_count > 0
3781 THEN
3782
3783 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3784 THEN
3785 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3786 , G_MODULE_PREFIX || l_procedure_name
3787 , 'ZX_API_PUB.get_default_tax_reg error, see below '
3788 ||'the detail error messages' );
3789
3790 FOR i IN 1..l_msg_count
3791 LOOP
3792 FND_MSG_PUB.Get(i, FND_API.G_FALSE, l_msg_data, l_indexO);
3793 FND_MSG_PUB.Delete_Msg(l_indexO);
3794 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3795 , G_MODULE_PREFIX || l_procedure_name||'.ZX_API_PUB error'
3796 , l_msg_data);
3797
3798 END LOOP; --i IN 1..l_msg_count
3799
3800 END IF;--FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3801
3802 END if;--l_msg_count = 0
3803 --add by Yao Zhang for bug 14500600
3804 IF x_tp_tax_registration_number IS NULL
3805 THEN
3806 BEGIN
3807 SELECT party_id
3808 INTO l_party_id
3809 FROM HZ_CUST_ACCOUNTS acct, hz_cust_acct_sites_all acct_site
3810 WHERE acct.CUST_ACCount_ID = acct_site.CUST_ACCount_ID
3811 AND acct_site.party_site_id = l_party_site_id;
3812
3813 SELECT reg.registration_number,
3814 reg.tax_regime_code,
3815 reg.tax,
3816 reg.tax_jurisdiction_code
3817 INTO l_tax_registration_number,
3818 l_reg_tax_regime_code,
3819 l_reg_tax,
3820 l_reg_tax_jursidiction_code
3821 FROM zx_registrations reg, zx_party_tax_profile tax_prof
3822 WHERE reg.party_tax_profile_id = tax_prof.party_tax_profile_id
3823 AND (reg.tax IS NULL OR reg.tax = l_tax)
3824 AND reg.tax_regime_code = l_tax_regime_code -- tax_regime_code is not null
3825 AND (reg.tax_jurisdiction_code IS NULL OR
3826 reg.tax_jurisdiction_code = l_tax_jurisdiction_code)
3827 AND l_tax_determine_date >= reg.effective_from
3828 AND (l_tax_determine_date < reg.effective_to OR
3829 reg.effective_to IS NULL)
3830 AND reg.registration_number IS NOT NULL
3831 AND tax_prof.party_type_code = 'THIRD_PARTY'
3832 AND tax_prof.party_id = l_party_id
3833 AND rownum = 1
3834 ORDER BY reg.tax, reg.tax_jurisdiction_code;
3835 EXCEPTION
3836 WHEN OTHERS THEN
3837 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3838 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3839 G_MODULE_PREFIX || l_procedure_name ||
3840 '. Exception while get tax reg number from customer level ',
3841 SQLCODE || SQLERRM);
3842 END IF;
3843 END;
3844 x_tp_tax_registration_number:=l_tax_registration_number;
3845 END IF;
3846 --add end by Yao Zhang for bug 14500600
3847
3848 IF x_tp_tax_registration_number IS NULL
3849 THEN
3850 x_tp_tax_registration_number := ZX_API_PUB.get_default_tax_reg(
3851 p_api_version => 1.0
3852 , p_init_msg_list => NULL
3853 , p_commit => NULL
3854 , p_validation_level => NULL
3855 , x_return_status => l_return_status
3856 , x_msg_count => l_msg_count
3857 , x_msg_data => l_msg_data
3858 , p_party_id => l_party_id-- changed for bug 14500600
3859 , p_party_type => 'THIRD_PARTY'
3860 , p_effective_date => SYSDATE);
3861 IF l_msg_count > 0
3862 THEN
3863
3864 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3865 THEN
3866 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3867 , G_MODULE_PREFIX || l_procedure_name
3868 , 'ZX_API_PUB.get_default_tax_reg error, see below '
3869 ||'the detail error messages' );
3870
3871 FOR i IN 1..l_msg_count
3872 LOOP
3873 FND_MSG_PUB.Get(i, FND_API.G_FALSE, l_msg_data, l_indexO);
3874 FND_MSG_PUB.Delete_Msg(l_indexO);
3875 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3876 , G_MODULE_PREFIX || l_procedure_name||'.ZX_API_PUB error'
3877 , l_msg_data);
3878
3879 END LOOP; --i IN 1..l_msg_count
3880
3881 END IF;--FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3882
3883 END if;--l_msg_count = 0
3884
3885 END IF;--x_tp_tax_registration_number IS NULL
3886
3887 END IF; --x_tp_tax_registration_number IS NULL
3888 --jogen Mar-21, 2006 bug 5088458
3889
3890 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3891 THEN
3892 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3893 , G_MODULE_PREFIX || l_procedure_name
3894 ,'End Procedure. ');
3895 END IF;
3896
3897 EXCEPTION
3898 WHEN OTHERS THEN
3899 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3900 THEN
3901 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
3902 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
3903 , Sqlcode||Sqlerrm);
3904 END IF;
3905 RAISE;
3906 END Get_Tp_Tax_Registration_Number;
3907
3908 --==========================================================================
3909 -- Procedure NAME:
3910 --
3911 -- Get_Arline_Tp_Taxreg_Number Public
3912 --
3913 -- DESCRIPTION:
3914 --
3915 -- This function is to get third party tax registration number upon one
3916 -- AR line according to GTA logic
3917 --
3918 -- PARAMETERS:
3919 -- In: p_org_id Identifier of operating unit
3920 -- p_customer_trx_id Identifier of AR transaction
3921 -- p_customer_trx_line_id Identifier of AR transaction line
3922 --
3923 -- Out:
3924 --
3925 -- Return:
3926 -- VARCHAR2
3927 --
3928 --
3929 --
3930 -- DESIGN REFERENCES:
3931 -- GTA_Reports_TD.doc
3932 --
3933 -- CHANGE HISTORY:
3934 --
3935 -- 25-Nov-2005: Donghai Wang Created
3936 --
3937 --===========================================================================
3938 FUNCTION Get_Arline_Tp_Taxreg_Number
3939 (p_org_id IN NUMBER
3940 ,p_customer_trx_id IN NUMBER
3941 ,p_customer_trx_line_id IN NUMBER
3942 )
3943 RETURN VARCHAR2
3944 IS
3945 l_tax_type_code zx_lines.tax_type_code%TYPE;
3946 l_tax_rate NUMBER;
3947 l_gt_currency_code fnd_currencies.currency_code%TYPE;
3948 l_tax_line_id zx_lines.tax_line_id%TYPE;
3949 l_tp_tax_registration_number zx_registrations.registration_number%TYPE;
3950 l_trx_id ra_customer_trx_all.customer_trx_id%TYPE;--Donghai Wang bug5212702 May-17,2006
3951
3952
3953 CURSOR c_tax_type_code
3954 IS
3955 SELECT
3956 vat_tax_type_code
3957 ,gt_currency_code
3958 FROM
3959 ar_gta_system_parameters_all
3960 WHERE org_id=p_org_id;
3961
3962 --CURSOR c_tax_line_id --Donghai Wang bug5212702 May-17,2006
3963 CURSOR c_tax_line_id(pc_trx_id NUMBER)--Donghai Wang bug5212702 May-17,2006
3964 IS
3965 SELECT
3966 tax_line_id
3967 FROM
3968 zx_lines
3969 WHERE trx_line_id=p_customer_trx_line_id
3970 AND entity_code='TRANSACTIONS'
3971 AND application_id = 222
3972 AND trx_id = p_customer_trx_id
3973 AND trx_level_type='LINE'
3974 AND tax_type_code=l_tax_type_code
3975 AND tax_currency_code=l_gt_currency_code
3976 AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--Donghai Wang bug5212702 May-17,2006
3977 AND trx_id=pc_trx_id --Donghai Wang bug5212702 May-17,2006
3978 ORDER BY tax_line_id;
3979
3980
3981
3982 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
3983 l_proc_level NUMBER := fnd_log.level_procedure;
3984 l_procedure_name VARCHAR2(30) :='Get_Arline_Tp_Taxreg_Number';
3985
3986 BEGIN
3987 --logging for debug
3988 IF (l_proc_level >= l_dbg_level)
3989 THEN
3990 fnd_log.STRING(l_proc_level
3991 ,g_module_prefix || l_procedure_name || '.begin'
3992 ,'Enter function');
3993 END IF; --l_proc_level>=l_dbg_level)
3994
3995
3996 --Get Vat tax type and GT currency code defined in GTA system options form
3997 --for current operating unit
3998 OPEN c_tax_type_code;
3999 FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
4000 CLOSE c_tax_type_code;
4001
4002 --Get VAT tax line id for current AR line
4003
4004 --Donghai Wang bug5212702 May-17,2006
4005 -- OPEN c_tax_line_id;
4006 SELECT customer_trx_id
4007 INTO l_trx_id
4008 FROM ra_customer_trx_lines_all
4009 WHERE customer_trx_line_id=p_customer_trx_line_id;
4010
4011 OPEN c_tax_line_id(l_trx_id);
4012 --Donghai Wang bug5212702 May-17,2006
4013
4014 FETCH c_tax_line_id INTO l_tax_line_id;
4015 CLOSE c_tax_line_id;
4016
4017 --To get third party tax registration number for cunrrent VAT tax line
4018 Get_Tp_Tax_Registration_Number(p_trx_id => p_customer_trx_id
4019 ,p_tax_line_id => l_tax_line_id
4020 ,x_tp_tax_registration_number => l_tp_tax_registration_number
4021 );
4022
4023 --logging for debug
4024 IF (l_proc_level >= l_dbg_level)
4025 THEN
4026 fnd_log.STRING(l_proc_level
4027 ,g_module_prefix || l_procedure_name || '.End'
4028 ,'Exit function');
4029 END IF; --l_proc_level>=l_dbg_level)
4030
4031 RETURN(l_tp_tax_registration_number);
4032 END Get_Arline_Tp_Taxreg_Number;
4033
4034
4035
4036 --========================================================================
4037 -- PROCEDURE : debug_output PUBLIC
4038 -- PARAMETERS: p_output_to Identifier of where to output to
4039 -- p_api_name the called api name
4040 -- p_log_level log level
4041 -- p_message the message that need to be output
4042 --
4043 -- COMMENT : the debug output, for using in readonly UT environment
4044 --
4045 -- PRE-COND :
4046 --
4047 -- EXCEPTIONS:
4048 --========================================================================
4049 PROCEDURE Debug_Output
4050 ( p_output_to IN VARCHAR2
4051 , p_log_level IN NUMBER
4052 , p_api_name IN VARCHAR2
4053 , p_message IN VARCHAR2
4054 )
4055 IS
4056 l_procedure_name VARCHAR2(30) := 'debug_output';
4057 BEGIN
4058
4059 CASE p_output_to
4060 WHEN 'FND_LOG.STRING' THEN
4061 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4062 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
4063 ,p_api_name || '.debug_output'
4064 ,p_message);
4065 END IF;
4066 WHEN 'FND_FILE.OUTPUT' THEN
4067 IF (FND_LOG.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
4068 fnd_file.put_line(fnd_file.OUTPUT
4069 ,p_api_name || '.debug_output' || ': ' ||
4070 p_message);
4071 END IF;
4072 WHEN 'FND_FILE.LOG' THEN
4073 IF (FND_LOG.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
4074 log(p_api_name || '.debug_output' || ': ' ||
4075 p_message);
4076 END IF;
4077 ELSE
4078 NULL;
4079 END CASE;
4080
4081 EXCEPTION
4082 WHEN OTHERS THEN
4083 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4084 THEN
4085 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
4086 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
4087 , Sqlcode||Sqlerrm);
4088 END IF;
4089 RAISE;
4090
4091 END Debug_Output;
4092
4093
4094 --==========================================================================
4095 -- FUNCTION NAME:
4096 --
4097 -- Get_AR_Batch_Source_Name Public
4098 --
4099 -- DESCRIPTION:
4100 --
4101 -- This function is to get AR Batch Source Name for a given org_id and
4102 -- source id
4103 --
4104 -- PARAMETERS:
4105 -- In: p_org_id Identifier of Operating Unit
4106 -- In: p_source_id AR batch source id
4107 -- Return: VARCHAR2
4108 --
4109 -- DESIGN REFERENCES:
4110 -- GTA_Reports_TD.doc
4111 --
4112 -- CHANGE HISTORY:
4113 --
4114 -- 01-Dec-2005: Qiang Li Creation
4115 --
4116 --=========================================================================
4117 FUNCTION Get_AR_Batch_Source_Name
4118 ( p_org_id IN NUMBER
4119 , p_source_id IN NUMBER
4120 )
4121 RETURN VARCHAR2 IS
4122 l_procedure_name VARCHAR2(30) := 'Get_AR_Batch_Source_Name';
4123 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
4124 l_proc_level NUMBER := fnd_log.level_procedure;
4125
4126 l_source_name RA_BATCH_SOURCES_all.NAME%TYPE;
4127 CURSOR c_source_name IS
4128 SELECT RA_BATCH_SOURCES_all.NAME
4129 FROM RA_BATCH_SOURCES_all
4130 WHERE org_id = p_org_id
4131 AND BATCH_SOURCE_ID = p_source_id;
4132
4133 BEGIN
4134 --logging for debug
4135 IF (l_proc_level >= l_dbg_level)
4136 THEN
4137 fnd_log.STRING(l_proc_level
4138 ,g_module_prefix || l_procedure_name || '.begin'
4139 ,'enter function');
4140 END IF;
4141
4142 OPEN c_source_name;
4143 FETCH
4144 c_source_name
4145 INTO
4146 l_source_name;
4147
4148 CLOSE c_source_name;
4149
4150 --logging for debug
4151 IF (l_proc_level >= l_dbg_level)
4152 THEN
4153 fnd_log.STRING(l_proc_level
4154 ,g_module_prefix || l_procedure_name || '.end'
4155 ,'end function');
4156 END IF;
4157
4158 RETURN(l_source_name);
4159 END Get_AR_Batch_Source_Name;
4160
4161 --==========================================================================
4162 -- FUNCTION NAME:
4163 --
4164 -- To_Xsd_Date_String Public
4165 --
4166 -- DESCRIPTION:
4167 --
4168 -- Convert an Oracle DB Date Object to a date string represented
4169 -- in the XSD Date Format. This is mainly for use by the
4170 -- XML Publisher Reports.
4171 --
4172 -- PARAMETERS:
4173 -- In: p_date Oracle Date to be converted to XSD Date Format
4174 --
4175 -- Return: VARCHAR2 A String representing the passed in Date in XSD
4176 -- Date Format
4177 --
4178 -- DESIGN REFERENCES:
4179 --
4180 --
4181 -- CHANGE HISTORY:
4182 --
4183 -- 14-Sep-2006: Donghai Wang Creation
4184 --
4185 --=========================================================================
4186 FUNCTION To_Xsd_Date_String
4187 ( p_date IN DATE
4188 )
4189 RETURN VARCHAR2
4190 IS
4191 l_xsd_date_string VARCHAR2(40);
4192 l_procedure_name VARCHAR2(30) := 'To_Xsd_Date_String';
4193 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
4194 l_proc_level NUMBER := fnd_log.level_procedure;
4195
4196 BEGIN
4197
4198 --logging for debug
4199 IF (l_proc_level >= l_dbg_level)
4200 THEN
4201 fnd_log.STRING(l_proc_level
4202 ,g_module_prefix || l_procedure_name || '.begin'
4203 ,'enter function');
4204 END IF;
4205
4206 --If input parameter is null, then returen a null string
4207 IF p_date IS NULL
4208 THEN
4209 IF (l_proc_level >= l_dbg_level)
4210 THEN
4211 FND_LOG.string( l_proc_level
4212 , G_MODULE_PREFIX
4213 , G_MODULE_PREFIX || l_procedure_name
4214 || '.end'
4215 );
4216 END IF;
4217
4218 RETURN NULL;
4219 END IF; --p_date IS NULL
4220
4221
4222
4223 SELECT TO_CHAR(p_date, 'YYYY-MM-DD')
4224 INTO l_xsd_date_string
4225 FROM DUAL;
4226
4227
4228 IF (l_proc_level >= l_dbg_level)
4229 THEN
4230 FND_LOG.string( l_proc_level
4231 , G_MODULE_PREFIX
4232 , G_MODULE_PREFIX || l_procedure_name
4233 || '.end: Returning XSD Date = '
4234 || l_xsd_date_string);
4235 END IF;
4236
4237 l_xsd_date_string := TRIM(l_xsd_date_string);
4238
4239 RETURN l_xsd_date_string;
4240
4241 EXCEPTION
4242
4243 WHEN OTHERS THEN
4244 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4245 THEN
4246 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
4247 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
4248 , Sqlcode||Sqlerrm);
4249 END IF;
4250 RAISE;
4251
4252
4253 END To_Xsd_Date_String;
4254
4255 --==========================================================================
4256 -- FUNCTION NAME:
4257 --
4258 -- Format_Monetary_Amount Public
4259 --
4260 -- DESCRIPTION:
4261 --
4262 -- Convert monetory amount with the format mask what is determined
4263 -- by VAT currency code and related profile values.
4264 --
4265 -- PARAMETERS:
4266 -- In: p_org_id Identifier of Operating Unit
4267 -- p_amount Monetary amount
4268 --
4269 -- Return: VARCHAR2
4270 --
4271 --
4272 -- DESIGN REFERENCES:
4273 --
4274 --
4275 -- CHANGE HISTORY:
4276 --
4277 -- 20-Sep-2006: Donghai Wang Creation
4278 --
4279 --=========================================================================
4280 FUNCTION Format_Monetary_Amount
4281 (p_org_id IN NUMBER
4282 ,p_amount IN NUMBER
4283 )
4284 RETURN VARCHAR2
4285 IS
4286 l_procedure_name VARCHAR2(30) := 'Format_Monetary_Amount';
4287 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
4288 l_proc_level NUMBER := fnd_log.level_procedure;
4289 l_base_currency ar_gta_system_parameters_all.gt_currency_code%TYPE;
4290 l_format_mask VARCHAR2(50);
4291 l_formatted_amount VARCHAR2(50);
4292
4293 CURSOR c_base_currency IS
4294 SELECT
4295 gt_currency_code
4296 FROM
4297 ar_gta_system_parameters_all
4298 WHERE
4299 org_id=p_org_id;
4300
4301 BEGIN
4302
4303 --logging for debug
4304 IF (l_proc_level >= l_dbg_level)
4305 THEN
4306 fnd_log.STRING(l_proc_level
4307 ,g_module_prefix || l_procedure_name || '.begin'
4308 ,'enter function');
4309 END IF;
4310
4311 --Get VAT Currency code of current operating unit
4312 OPEN c_base_currency;
4313 FETCH c_base_currency INTO l_base_currency;
4314 CLOSE c_base_currency;
4315
4316 --Get format mask for VAT currency code
4317 l_format_mask:=FND_CURRENCY.Get_Format_Mask(currency_code => l_base_currency
4318 ,field_length => 30
4319 );
4320 l_formatted_amount:=to_char(p_amount,l_format_mask);
4321
4322 --logging for debug
4323 IF (l_proc_level >= l_dbg_level)
4324 THEN
4325 fnd_log.STRING(l_proc_level
4326 ,g_module_prefix || l_procedure_name || '.end'
4327 ,'end function');
4328 END IF;
4329 RETURN l_formatted_amount;
4330
4331 EXCEPTION
4332
4333 WHEN OTHERS THEN
4334 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4335 THEN
4336 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
4337 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
4338 , Sqlcode||Sqlerrm);
4339 END IF;
4340 RAISE;
4341
4342 END Format_Monetary_Amount;
4343
4344 --==========================================================================
4345 -- PROCEDURE NAME:
4346 --
4347 -- Populate_Invoice_Type Public
4348 --
4349 -- DESCRIPTION:
4350 --
4351 -- This procedure is to populate invoice type column for Transfer Rule
4352 -- and System Option tables to do the data migration from R12.0 to R12.1.X.
4353 --
4354 -- PARAMETERS:
4355 -- In: p_org_id NUMBER
4356 -- Out:
4357 --
4358 -- DESIGN REFERENCES:
4359 -- GTA_12.1.2_Technical_Design.doc
4360 --
4361 -- CHANGE HISTORY:
4362 --
4363 -- 16-Aug-2009: Allen Yang Created.
4364 --
4365 --===========================================================================
4366 PROCEDURE Populate_Invoice_Type(p_org_id IN NUMBER)
4367 IS
4368 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
4369 l_proc_level NUMBER := fnd_log.level_procedure;
4370 l_procedure_name VARCHAR2(30) := 'Populate_Invoice_Type';
4371
4372
4373 BEGIN
4374 --logging for debug
4375 IF (l_proc_level >= l_dbg_level)
4376 THEN
4377 fnd_log.STRING(l_proc_level
4378 ,g_module_prefix || l_procedure_name || '.begin'
4379 ,'Enter procedure');
4380 END IF; --l_proc_level>=l_dbg_level)
4381 -- initialize invoice type for System Option and Transfer Rules
4382 UPDATE ar_gta_tax_limits_all
4383 SET invoice_type=0
4384 WHERE invoice_type IS NULL
4385 AND org_id = p_org_id;
4386
4387 UPDATE ar_gta_rule_headers_all
4388 SET invoice_type=0
4389 WHERE invoice_type IS NULL
4390 AND org_id = p_org_id;
4391
4392 COMMIT;
4393
4394 fnd_message.set_name('AR', 'AR_GTA_INV_TYPE_INIT');
4395 fnd_message.set_token('ORG_NAME',get_operatingunit(p_org_id));
4396 fnd_file.put_line(fnd_file.OUTPUT, fnd_message.get());
4397
4398 --logging for debug
4399 IF (l_proc_level >= l_dbg_level)
4400 THEN
4401 fnd_log.STRING(l_proc_level
4402 ,g_module_prefix || l_procedure_name || '.end'
4403 ,'end procedure');
4404 END IF;
4405
4406 EXCEPTION
4407
4408 WHEN OTHERS THEN
4409 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4410 THEN
4411 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
4412 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
4413 , Sqlcode||Sqlerrm);
4414 END IF;
4415 RAISE;
4416
4417 END Populate_Invoice_Type;
4418
4419 --==========================================================================
4420 -- PROCEDURE NAME:
4421 --
4422 -- Populate_Invoice_Type_Header Public
4423 --
4424 -- DESCRIPTION:
4425 --
4426 -- This procedure is to populate invoice type column for GTA Invoice Header
4427 -- table to do the data migration from R12.0 to R12.1.X.
4428 --
4429 -- PARAMETERS:
4430 -- In: p_org_id NUMBER
4431 -- Out:
4432 --
4433 -- DESIGN REFERENCES:
4434 -- GTA_12.1.2_Technical_Design.doc
4435 --
4436 -- CHANGE HISTORY:
4437 --
4438 -- 16-Aug-2009: Allen Yang Created.
4439 -- 26-Aug-2009: Allen Yang Modified for bug 8839141.
4440 --===========================================================================
4441 PROCEDURE Populate_Invoice_Type_Header(p_org_id IN NUMBER)
4442 IS
4443 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
4444 l_proc_level NUMBER := fnd_log.level_procedure;
4445 l_procedure_name VARCHAR2(30) := 'Populate_Invoice_Type_Header';
4446
4447 -- all GTA invoices whose AR transaction type is not associated to invoice type
4448 CURSOR c_inv_trx_type_no_inv_type
4449 IS
4450 SELECT JGTH.GTA_TRX_NUMBER, SOURCE
4451 FROM RA_CUSTOMER_TRX_ALL RCT
4452 , AR_GTA_TRX_HEADERS_ALL JGTH
4453 WHERE invoice_type is null
4454 AND JGTH.ORG_ID = p_org_id
4455 AND RCT.CUSTOMER_TRX_ID(+) = JGTH.Ra_Trx_Id
4456 AND NOT EXISTS (SELECT JGTL.Limitation_Id
4457 FROM ar_gta_tax_limits_all JGTL
4458 ,ar_gta_type_mappings JGTM
4459 WHERE JGTL.ORG_ID = JGTH.Org_Id
4460 AND JGTL.FP_TAX_REGISTRATION_NUMBER = JGTH.FP_TAX_REGISTRATION_NUMBER
4461 AND JGTM.Limitation_Id = JGTL.LIMITATION_ID
4462 AND JGTM.TRANSACTION_TYPE_ID = RCT.CUST_TRX_TYPE_ID);
4463
4464 -- all GTA invoices whose AR transaction type is associated to Recycle invoice type,
4465 -- but tax rate and amount is not zero.
4466 CURSOR c_recycle_tax_amount_not_zero
4467 IS
4468 SELECT JGTH.GTA_TRX_NUMBER, SOURCE
4469 FROM RA_CUSTOMER_TRX_ALL RCT
4470 , AR_GTA_TRX_HEADERS_ALL JGTH
4471 WHERE invoice_type is null
4472 AND JGTH.ORG_ID = p_org_id
4473 AND RCT.CUSTOMER_TRX_ID(+) = JGTH.Ra_Trx_Id
4474 AND EXISTS (SELECT JGTL.Limitation_Id
4475 FROM ar_gta_tax_limits_all JGTL
4476 ,ar_gta_type_mappings JGTM
4477 WHERE JGTL.ORG_ID = JGTH.Org_Id
4478 AND JGTL.FP_TAX_REGISTRATION_NUMBER = JGTH.FP_TAX_REGISTRATION_NUMBER
4479 AND JGTM.Limitation_Id = JGTL.LIMITATION_ID
4480 AND JGTM.TRANSACTION_TYPE_ID = RCT.CUST_TRX_TYPE_ID);
4481
4482 -- credit memo whose invoice type is different with invoice type of original transaction
4483 CURSOR c_cm_inv_type_different
4484 IS
4485 SELECT JGTH.GTA_TRX_NUMBER, SOURCE
4486 FROM RA_CUSTOMER_TRX_ALL RCT,
4487 RA_CUST_TRX_TYPES_ALL RCTT,
4488 AR_GTA_TRX_HEADERS_ALL JGTH
4489 WHERE invoice_type is not null
4490 AND JGTH.ORG_ID = p_org_id
4491 AND RCT.CUSTOMER_TRX_ID(+) = JGTH.Ra_Trx_Id
4492 AND RCT.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID(+)
4493 AND RCTT.ORG_ID=JGTH.ORG_ID
4494 AND RCTT.TYPE = 'CM'
4495 AND RCT.previous_customer_trx_id is not null
4496 AND JGTH.invoice_type <>
4497 (SELECT DISTINCT invoice_type
4498 FROM AR_GTA_TRX_HEADERS_ALL JGTH1
4499 WHERE JGTH1.RA_TRX_id = RCT.previous_customer_trx_id);
4500
4501 -- all GTA invoices need to be updated
4502 CURSOR c_all_inv_updated
4503 IS
4504 SELECT GTA_TRX_HEADER_ID
4505 , GTA_TRX_NUMBER
4506 , SOURCE
4507 , RA_TRX_ID
4508 , FP_TAX_REGISTRATION_NUMBER
4509 , ORG_ID
4510 FROM AR_GTA_TRX_HEADERS_ALL
4511 WHERE INVOICE_TYPE IS NULL
4512 AND ORG_ID = p_org_id;
4513
4514 l_gta_trx_number ar_gta_trx_headers_all.GTA_TRX_NUMBER%TYPE;
4515 l_source ar_gta_trx_headers_all.SOURCE%TYPE;
4516 l_gta_trx_header_id ar_gta_trx_headers_all.GTA_TRX_HEADER_ID%TYPE;
4517 l_ra_trx_id ar_gta_trx_headers_all.RA_TRX_ID%TYPE;
4518 l_fp_tax_registration_number ar_gta_trx_headers_all.FP_TAX_REGISTRATION_NUMBER%TYPE;
4519 l_org_id ar_gta_trx_headers_all.ORG_ID%TYPE;
4520 l_invoice_type ar_gta_trx_headers_all.INVOICE_TYPE%TYPE;
4521 l_pre_trx_invoice_type ar_gta_trx_headers_all.INVOICE_TYPE%TYPE;
4522 l_ar_trx_type RA_CUST_TRX_TYPES_ALL.TYPE%TYPE;
4523
4524
4525 BEGIN
4526 --logging for debug
4527 IF (l_proc_level >= l_dbg_level)
4528 THEN
4529 fnd_log.STRING(l_proc_level
4530 ,g_module_prefix || l_procedure_name || '.begin'
4531 ,'Enter procedure');
4532 END IF; --l_proc_level>=l_dbg_level)
4533
4534 -- log for successfully updated invoice numbers
4535 fnd_message.set_name('AR', 'AR_GTA_UPG_TRANSACTION_S');
4536 fnd_file.put_line(fnd_file.OUTPUT, fnd_message.get());
4537
4538 OPEN c_all_inv_updated;
4539 LOOP
4540 FETCH c_all_inv_updated
4541 INTO l_gta_trx_header_id
4542 , l_gta_trx_number
4543 , l_source
4544 , l_ra_trx_id
4545 , l_fp_tax_registration_number
4546 , l_org_id;
4547 EXIT WHEN c_all_inv_updated%NOTFOUND;
4548 BEGIN
4549 SELECT JGTL.invoice_type
4550 INTO l_invoice_type
4551 FROM RA_CUSTOMER_TRX_ALL RCT
4552 ,ar_gta_tax_limits_all JGTL
4553 WHERE RCT.CUSTOMER_TRX_ID = l_ra_trx_id
4554 AND JGTL.ORG_ID = l_org_id
4555 AND JGTL.FP_TAX_REGISTRATION_NUMBER = l_fp_tax_registration_number
4556 AND RCT.CUST_TRX_TYPE_ID in
4557 (SELECT JGTM.TRANSACTION_TYPE_ID
4558 FROM ar_gta_type_mappings JGTM
4559 WHERE JGTM.Limitation_Id = JGTL.LIMITATION_ID)
4560 AND (JGTL.invoice_type IN ('0', '2') OR
4561 (JGTL.invoice_type = '1' AND NOT EXISTS
4562 ( SELECT *
4563 FROM ar_gta_trx_lines_all JGTLA
4564 WHERE JGTLA.GTA_TRX_HEADER_ID = l_gta_trx_header_id
4565 AND JGTLA.Org_Id = l_org_id
4566 AND (JGTLA.Tax_Rate <> 0 OR
4567 JGTLA.Tax_Amount <> 0))));
4568 EXCEPTION
4569 WHEN no_data_found THEN
4570 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4571 THEN
4572 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
4573 , G_MODULE_PREFIX || l_procedure_name
4574 , l_procedure_name||'no data found ');
4575 END IF;/*(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)*/
4576 END;
4577 IF l_invoice_type IS NOT NULL
4578 THEN
4579 BEGIN
4580 SELECT RCTT.TYPE
4581 INTO l_ar_trx_type
4582 FROM RA_CUST_TRX_TYPES_ALL RCTT
4583 ,RA_CUSTOMER_TRX_ALL RCT
4584 ,AR_GTA_TRX_HEADERS_ALL JGTH
4585 WHERE JGTH.GTA_TRX_HEADER_ID = l_gta_trx_header_id
4586 AND RCT.CUSTOMER_TRX_ID(+) = JGTH.Ra_Trx_Id
4587 AND RCT.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID(+)
4588 AND RCTT.ORG_ID = l_org_id;
4589 EXCEPTION
4590 WHEN no_data_found THEN
4591 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4592 THEN
4593 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
4594 , G_MODULE_PREFIX || l_procedure_name
4595 , l_procedure_name||'no data found ');
4596 END IF;/*(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)*/
4597 END;
4598 -- if AR transaction Type is Credit Memo, then check invoice type of original transaction,
4599 -- else this GTA invoice can be successfully updated.
4600 IF (NVL(l_ar_trx_type, 'INV')='CM')
4601 THEN
4602 BEGIN
4603 SELECT DISTINCT JGTH.invoice_type
4604 INTO l_pre_trx_invoice_type
4605 FROM AR_GTA_TRX_HEADERS_ALL JGTH
4606 ,RA_CUSTOMER_TRX_ALL RCT
4607 WHERE RCT.CUSTOMER_TRX_ID(+) = l_ra_trx_id
4608 AND JGTH.RA_TRX_id = RCT.previous_customer_trx_id;
4609 EXCEPTION
4610 WHEN no_data_found THEN
4611 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4612 THEN
4613 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
4614 , G_MODULE_PREFIX || l_procedure_name
4615 , l_procedure_name||'no data found ');
4616 END IF;/*(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)*/
4617 END;
4618 IF (l_pre_trx_invoice_type IS NOT NULL AND l_pre_trx_invoice_type = l_invoice_type)
4619 THEN
4620 fnd_file.put_line(fnd_file.OUTPUT, l_gta_trx_number||'('||l_source||')');
4621 END IF;
4622 ELSE
4623 fnd_file.put_line(fnd_file.OUTPUT, l_gta_trx_number||'('||l_source||')');
4624 END IF;
4625 END IF; --l_invoice_type IS NOT NULL
4626 END LOOP; -- c_all_inv_updated%NOTFOUND;
4627 CLOSE c_all_inv_updated;
4628
4629 -- initialize invoice type for GTA invoices
4630 UPDATE AR_GTA_TRX_HEADERS_ALL JGTH
4631 SET invoice_type = (SELECT JGTL.invoice_type
4632 FROM RA_CUSTOMER_TRX_ALL RCT,
4633 ar_gta_tax_limits_all JGTL
4634 WHERE RCT.CUSTOMER_TRX_ID = JGTH.Ra_Trx_Id
4635 AND JGTL.ORG_ID = JGTH.Org_Id
4636 AND JGTL.FP_TAX_REGISTRATION_NUMBER =
4637 JGTH.FP_TAX_REGISTRATION_NUMBER
4638 AND RCT.CUST_TRX_TYPE_ID in
4639 (SELECT JGTM.TRANSACTION_TYPE_ID
4640 FROM ar_gta_type_mappings JGTM
4641 WHERE JGTM.Limitation_Id = JGTL.LIMITATION_ID)
4642 AND (JGTL.invoice_type IN ('0', '2') OR
4643 (JGTL.invoice_type = '1' AND NOT EXISTS
4644 ( SELECT *
4645 FROM ar_gta_trx_lines_all JGTLA
4646 WHERE JGTLA.GTA_TRX_HEADER_ID =
4647 JGTH.GTA_TRX_HEADER_ID
4648 AND JGTH.Org_Id = JGTLA.Org_Id
4649 AND (JGTLA.Tax_Rate <> 0 OR
4650 JGTLA.Tax_Amount <> 0)))))
4651 WHERE invoice_type IS NULL
4652 AND JGTH.ORG_ID = p_org_id;
4653 COMMIT;
4654
4655 /* commented by Allen Yang 26-Aug-2009 for bug 8839141.
4656 -- log for invoice type populating exceptions
4657 fnd_message.set_name('AR', 'AR_GTA_INV_TYPE_EXC_REASON');
4658 fnd_file.put_line(fnd_file.OUTPUT, fnd_message.get());
4659 */
4660
4661 -- added by Allen Yang 26-Aug-2009 for bug 8839141.
4662 OPEN c_cm_inv_type_different;
4663 FETCH c_cm_inv_type_different INTO l_gta_trx_number, l_source;
4664 IF c_cm_inv_type_different%FOUND
4665 THEN
4666 fnd_message.set_name('AR', 'AR_GTA_UPG_DIF_INVOICE_TYPE');
4667 fnd_file.put_line(fnd_file.OUTPUT, fnd_message.get());
4668 END IF; --c_cm_inv_type_different%FOUND
4669 WHILE c_cm_inv_type_different%FOUND
4670 LOOP
4671 fnd_file.put_line(fnd_file.OUTPUT, l_gta_trx_number||'('||l_source||')');
4672 FETCH c_cm_inv_type_different INTO l_gta_trx_number, l_source;
4673 END LOOP; --c_cm_inv_type_different%FOUND
4674 CLOSE c_cm_inv_type_different;
4675 -- end added by Allen Yang
4676
4677 OPEN c_inv_trx_type_no_inv_type;
4678 FETCH c_inv_trx_type_no_inv_type INTO l_gta_trx_number, l_source;
4679 IF c_inv_trx_type_no_inv_type%FOUND
4680 THEN
4681 -- modified by Allen Yang 26-Aug-2009 for bug 8839141
4682 --fnd_message.set_name('AR', 'AR_GTA_TRX_TYPE_NOT_ASS');
4683 fnd_message.set_name('AR', 'AR_GTA_UPG_NO_INVOICE_TYPE');
4684 -- end modified by Allen Yang
4685 fnd_file.put_line(fnd_file.OUTPUT, fnd_message.get());
4686 END IF; --c_inv_trx_type_no_inv_type%FOUND
4687 WHILE c_inv_trx_type_no_inv_type%FOUND
4688 LOOP
4689 fnd_file.put_line(fnd_file.OUTPUT, l_gta_trx_number||'('||l_source||')');
4690 FETCH c_inv_trx_type_no_inv_type INTO l_gta_trx_number, l_source;
4691 END LOOP; --c_inv_trx_type_no_inv_type%FOUND
4692 CLOSE c_inv_trx_type_no_inv_type;
4693
4694 OPEN c_recycle_tax_amount_not_zero;
4695 FETCH c_recycle_tax_amount_not_zero INTO l_gta_trx_number, l_source;
4696 IF c_recycle_tax_amount_not_zero%FOUND
4697 THEN
4698 -- modified by Allen Yang 26-Aug-2009 for bug 8839141
4699 --fnd_message.set_name('AR', 'AR_GTA_REC_TAX_NOT_ZERO');
4700 fnd_message.set_name('AR', 'AR_GTA_UPG_NOZERO_TAX_R');
4701 -- end modified by Allen Yang
4702 fnd_file.put_line(fnd_file.OUTPUT, fnd_message.get());
4703 END IF; --c_recycle_tax_amount_not_zero%FOUND
4704 WHILE c_recycle_tax_amount_not_zero%FOUND
4705 LOOP
4706 fnd_file.put_line(fnd_file.OUTPUT, l_gta_trx_number||'('||l_source||')');
4707 FETCH c_recycle_tax_amount_not_zero INTO l_gta_trx_number, l_source;
4708 END LOOP; --c_recycle_tax_amount_not_zero%FOUND
4709 CLOSE c_recycle_tax_amount_not_zero;
4710
4711 /* commented by Allen Yang 26-Aug-2009 for bug 8839141.
4712 OPEN c_cm_inv_type_different;
4713 FETCH c_cm_inv_type_different INTO l_gta_trx_number, l_source;
4714 IF c_cm_inv_type_different%FOUND
4715 THEN
4716 fnd_message.set_name('AR', 'AR_GTA_CM_INV_TYPE_DIFF');
4717 fnd_file.put_line(fnd_file.OUTPUT, fnd_message.get());
4718 END IF; --c_cm_inv_type_different%FOUND
4719 WHILE c_cm_inv_type_different%FOUND
4720 LOOP
4721 fnd_file.put_line(fnd_file.OUTPUT, l_gta_trx_number||'('||l_source||')');
4722 FETCH c_cm_inv_type_different INTO l_gta_trx_number, l_source;
4723 END LOOP; --c_cm_inv_type_different%FOUND
4724 CLOSE c_cm_inv_type_different;
4725 */
4726
4727 --logging for debug
4728 IF (l_proc_level >= l_dbg_level)
4729 THEN
4730 fnd_log.STRING(l_proc_level
4731 ,g_module_prefix || l_procedure_name || '.end'
4732 ,'end procedure');
4733 END IF;
4734
4735 EXCEPTION
4736
4737 WHEN OTHERS THEN
4738 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4739 THEN
4740 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
4741 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
4742 , Sqlcode||Sqlerrm);
4743 END IF;
4744 RAISE;
4745
4746 END Populate_Invoice_Type_Header;
4747
4748 END AR_GTA_TRX_UTIL;
[Home] [Help]