[Home] [Help]
PACKAGE BODY: APPS.JMF_GTA_TRX_UTIL
Source
1 PACKAGE BODY JMF_GTA_TRX_UTIL AS
2 --$Header: JMFUGTAB.pls 120.33.12010000.2 2008/11/17 14:39:47 dwang ship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation
5 --| Redwood Shores, California, USA
6 --| All rights reserved.
7 --+===========================================================================
8 --|
9 --| FILENAME :
10 --| JMFUGTAS.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 --|
46 --|
47 --| HISTORY:
48 --| 20-APR-2005: Jim Zheng Created
49 --|
50 --| 22-Aug-2005: Jim Zheng Modify: New feature about registration
51 --| Number
52 --|
53 --| 11-Oct-2005: Jim Zheng Modify: modify some select tax_line_id code
54 --| in get_info_from_ebtax
55 --| add where entity_code = 'TRANSACTONS'.
56 --|
57 --| 13-OCt-2005: Jim Zheng Modify: modify the parametere of
58 --| get_tp_tax_registration. remove the
59 --| input para p_trx_line_id, add a new
60 --\ input parameter p_tax_line_id
61 --| add a new procedure verify_tax_line.
62 --| add a new procedure debug_output
63 --| 19-Oct-2005: Jim Zheng Modify: update the procedure
64 --| get_info_from_ebtax, add a output
65 --| parameter
66 --| x_taxable_amount_org for get original
67 --| currency amount.
68 --| 20-Oct-2005: Jim Zheng Modify: Add a procedure debug_output_conc for
69 --| dubug report. remove the hard code
70 --| for fp_registration_number
71 --| in get_info_from_ebtax
72 --| Add tax_rate/100 in output value
73 --| in get_info_from_ebtax
74 --| 24-Nov-2005 Donghai Wang Modify procedure 'Get_Arline_Amount'
75 --| to add a new parameter
76 --| and use real code to replace dummy code
77 --| 24-Nov-2005 Donghai Wang Add a new parameter for function
78 --| 'Get_Arline_Vattax_Amount'
79 --| 24-Nov-2005 Donghai Wang Add a new parameter for function
80 --| 'Get_Arline_Vattax_Rate'
81 --| 25-Nov-2005 Donghai Wang Add a new function
82 --| Get_Arline_Tp_Taxreg_Number
83 --| 25-Nov-2005 Donghai Wang Add a new function
84 --| 'Check_Taxcount_Of_Arline'
85 --| 25-Nov-2005 Donghai Wang Add a new function
86 --| 'Check_Taxcount_Of_Artrx'
87 --| 25-Nov-2005 Donghai Wang update function 'Get_Arinvoice_Amount'
88 --| to follow ebtax logic
89 --| 25-Nov-2005 Donghai Wang update functon
90 --| 'Get_Arinvoice_Tax_Amount'
91 --| to follow ebtax logic
92 --| 28-Nov-2005 Jim Zheng remove the default value of
93 --| fp regi number, procedure
94 --| get_info_from_ebtax
95 --| 28-Nov-2005 Jim Zheng remove the default value of return
96 --| status of procedure get_info_from_ebtax
97 --| 28-Nov-2005 Jim Zheng add GTA currency code when get tax line
98 --| in procedure verify_tax_line.
99 --| 01-DEC-2005 Qiang Li add a new function Get_AR_Batch_Source_Name
100 --| 29-JUN-2006 Shujuan Yan In Get_Info_From_Ebtax, Add a output
101 --| parameter x_tax_curr_unit_price to
102 --| store the unit price of tax currency
103 --| for bug 5168900
104 --| 14-Sep-2006 Donghai Wang Added the new function
105 --| To_Xsd_Date_String to convert date
106 --| values into XSD format so that they can
107 --| be formatted correctly in XML Publisher
108 --| Reports for bug 5521629.
109 --| 20-Sep-2006 Donghai Wang Added the new function
110 -- Fomrat_Monetary_Amount
111 --| 28-Dec-2007 Subba Added new function Get_Invoice_Type for R12.1
112 --| 23-Jan-2008 Subba Modified code of Get_invoice_Type
113 --+===========================================================================+
114
115
116
117 --=============================================================
118 -- FUNCTION NAME:
119 --
120 -- get_invoice_type Public
121 --
122 -- DESCRIPTION:
123 --
124 -- This function is to get invoice type for a given customer_trx_id and -- tax registration number.
125
126 -- PARAMETERS:
127
128 -- In: p_org_id Business Unit identifier.
129 -- In: p_customer_trx_id AR transaction identifier.
130 -- In: p_fp_tax_registration_num fisrt party registration number
131 -- Return: VARCHAR2
132 --
133 -- CHANGE HISTORY:
134 -- 28-Dec-2007 Subba Created.
135 --=============================================================
136
137
138 FUNCTION get_invoice_type
139 (p_org_id IN NUMBER
140 ,p_customer_trx_id IN NUMBER
141 ,p_fp_tax_registration_num IN NUMBER
142 )
143 RETURN VARCHAR2
144 IS
145 l_procedure_name VARCHAR2(30) := 'get_invoice_type';
146 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
147 l_proc_level NUMBER := fnd_log.LEVEL_PROCEDURE;
148 l_error_string VARCHAR2(1000);
149
150 l_invoice_type jmf_gta_tax_limits_all.invoice_type%TYPE;
151
152 BEGIN
153
154
155 SELECT
156 jgtla.invoice_type
157 INTO
158 l_invoice_type
159 FROM
160 jmf_gta_tax_limits_all jgtla
161 ,jmf_gta_type_mappings jgtm
162 ,ra_customer_trx_all rcta
163 WHERE rcta.customer_trx_id = p_customer_trx_id
164 AND rcta.cust_trx_type_id = jgtm.transaction_type_id
165 AND jgtm.limitation_id = jgtla.limitation_id
166 AND jgtla.fp_tax_registration_number = p_fp_tax_registration_num
167 AND jgtla.org_id = p_org_id;
168
169
170 RETURN(l_invoice_type);
171
172
173 EXCEPTION
174 WHEN NO_DATA_FOUND THEN
175
176 l_invoice_type := null;
177 l_error_string := fnd_message.get();
178
179
180 /*fnd_message.SET_NAME('JMF', 'JMF_GTA_MISSING_INVOICE_TYPE');
181 fnd_message.set_token('TRX_TYP',l_trx_typ);
182 fnd_message.set_token('TAX_REG_NUM',p_fp_tax_registration_num);
183
184
185
186 -- output error '<?xml version="1.0" encoding="UTF-8" ?>
187 fnd_file.put_line(fnd_file.output,
188 '<TransferReport>
189 <ReportFailed>Y</ReportFailed>
190 <ReportFailedMsg>'||l_error_string||'</ReportFailedMsg>
191 <FailedWithParameters>Y</FailedWithParameters>
192 </TransferReport>');*/
193 -- begin log
194 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
195 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
196 ,G_MODULE_PREFIX || l_procedure_name
197 , 'transaction type is not mapped to any invoice type.');
198 END IF;
199 -- end log
200 --RAISE;
201
202
203 RETURN(l_invoice_type);
204
205 END get_invoice_type;
206
207
208 --=============================================================================
209 -- PROCEDURE NAME:
210 -- log
211 -- TYPE:
212 -- private
213 --
214 -- DESCRIPTION :
215 -- This procedure log message
216 -- PARAMETERS :
217 -- p_message IN VARCHAR2
218 --
219 -- HISTORY:
220 -- 10-MAY-2005 : Jim.Zheng Create
221 --=============================================================================
222 PROCEDURE log
223 (p_message IN VARCHAR2)
224 IS
225 BEGIN
226 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
227 THEN
228 fnd_log.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE
229 ,MODULE => g_module_prefix || '.Debug'
230 ,MESSAGE => p_message
231 );
232 END IF;
233 END log;
234 --==========================================================================
235 -- PROCEDURE NAME:
236 --
237 -- Output_Conc Public
238 --
239 -- DESCRIPTION:
240 --
241 -- This procedure write data to concurrent output file
242 -- the data can be longer than 4000
243 --
244 -- PARAMETERS:
245 -- In: p_clob the content which need output to concurrent output
246 --
247 --
248 -- DESIGN REFERENCES:
249 --
250 --
251 -- CHANGE HISTORY:
252 --
253 -- 30-APR-2005: qugen.hu Created.
254 -- 24-Aug-2006: Jogen.hu change from search '>' to '<'
255 --
256 --===========================================================================
257 PROCEDURE output_conc
258 (p_clob IN CLOB)
259 IS/*
260 max_linesize NUMBER := 254;
261 l_pos_tag NUMBER;
262 l_pos NUMBER;
263 l_len NUMBER;
264 l_tmp NUMBER;
265 l_tmp1 NUMBER;
266 l_substr CLOB;
267 BEGIN
268 NULL;
269 --initalize
270 l_pos := 1;
271 l_len := length(p_clob);
272
273 WHILE l_pos <= l_len
274 LOOP
275 --get the XML tag from reverse direction
276 l_tmp := l_pos + max_linesize - 2 - l_len;
277 l_pos_tag := instr(p_clob
278 ,'>'
279 ,l_tmp);
280
281 --the pos didnot touch the end of string
282 l_tmp1 := l_pos - 1;
283
284 IF (l_pos_tag > l_tmp1)
285 AND (l_tmp < 0)
286 THEN
287 l_tmp := l_pos_tag - l_pos + 1;
288 fnd_file.put(fnd_file.output
289 ,substr(p_clob
290 ,l_pos
291 ,l_tmp));
292 l_pos := l_pos_tag + 1;
293 ELSE
294 l_substr := substr(p_clob
295 ,l_pos);
296 fnd_file.put(fnd_file.output
297 ,l_substr);
298 l_pos := l_len + 1;
299
300 END IF;
301
302 END LOOP;*/
303 --initalize
304 l_pos1 NUMBER; --position for '</'
305 l_pos2 NUMBER; --position for '>' follow '</'
306 l_pos3 NUMBER; --position for '/>'
307 l_pos NUMBER; --latest starting postion
308 l_len NUMBER;
309 l_prepos NUMBER;
310
311 BEGIN
312 --initalize
313 l_pos := 1;
314 l_len := length(p_clob);
315
316 WHILE TRUE
317 LOOP
318 l_prepos:=l_pos;
319
320 l_pos1:=instr(p_clob,'</',l_prepos);
321 IF l_pos1>0 THEN
322 l_pos2:=instr(p_clob,'>',l_pos1);
323 ELSE
324 l_pos2:=0;
325 END IF;
326
327 l_pos3:=instr(p_clob,'/>',l_prepos);
328
329 IF l_pos2>0 AND l_pos3> 0 THEN
330 IF l_pos2>l_pos3 THEN
331 l_pos:=l_pos3+2;
332 ELSE
333 l_pos:=l_pos2+1;
334 END IF;
335 ELSIF l_pos2>0 THEN
336 l_pos:=l_pos2+1;
337 ELSE
338 l_pos:=l_pos3+2;
339 END IF;
340
341 IF l_pos>2 THEN
342 FND_FILE.Put_Line(FND_FILE.Output
343 ,substr(p_clob
344 ,l_prepos
345 ,l_pos - l_prepos
346 )
347 );
348 ELSE
349 FND_FILE.Put_Line(FND_FILE.Log
350 ,substr(p_clob
351 ,l_prepos
352 )
353 );
354 EXIT;
355 END IF;
356 END LOOP;
357 EXCEPTION
358 WHEN OTHERS THEN
359 RAISE;
360 END output_conc;
361
362 --==========================================================================
363 -- PROCEDURE NAME:
364 --
365 -- debug_output_conc Public
366 --
367 -- DESCRIPTION:
368 --
369 -- This procedure write data to concurrent output file
370 -- the data can be longer than 4000
371 --
372 -- PARAMETERS:
373 -- In: p_clob the content which need output to concurrent output
374 --
375 --
376 -- DESIGN REFERENCES:
377 --
378 --
379 -- CHANGE HISTORY:
380 --
381 -- 30-APR-2005: Jim.zheng Created.
382 --
383 --===========================================================================
384 PROCEDURE debug_output_conc
385 (p_clob IN CLOB)
386 IS
387 max_linesize NUMBER := 254;
388 l_pos_tag NUMBER;
389 l_pos NUMBER;
390 l_len NUMBER;
391 l_tmp NUMBER;
392 l_tmp1 NUMBER;
393 l_substr CLOB;
394 BEGIN
395 NULL;
396 --initalize
397 l_pos := 1;
398 l_len := length(p_clob);
399
400 WHILE l_pos <= l_len
401 LOOP
402 --get the XML tag from reverse direction
403 l_tmp := l_pos + max_linesize - 2 - l_len;
404 l_pos_tag := instr(p_clob
405 ,'>'
406 ,l_tmp);
407
408 --the pos didnot touch the end of string
409 l_tmp1 := l_pos - 1;
410
411 IF (l_pos_tag > l_tmp1)
412 AND (l_tmp < 0)
413 THEN
414 l_tmp := l_pos_tag - l_pos + 1;
415 log(substr(p_clob,l_pos,l_tmp));
416 l_pos := l_pos_tag + 1;
417 ELSE
418 l_substr := substr(p_clob
419 ,l_pos);
420 log(l_substr);
421 l_pos := l_len + 1;
422
423 END IF;
424
425 END LOOP;
426 EXCEPTION
427 WHEN OTHERS THEN
428 NULL;
429 END debug_output_conc;
430
431 --==========================================================================
432 -- PROCEDURE NAME:
433 --
434 -- Create_Trxs Public
435 --
436 -- DESCRIPTION:
437 --
438 -- This package can insert a set of trx to JMF_GTA_TRX_HEADS_ALL
439 -- AND JMF_GTA_TRX_LINES_ALL.
440 --
441 -- PARAMETERS:
442 -- In: p_gta_trxs trx_tbl_type
443 --
444 --
445 -- DESIGN REFERENCES:
446 -- GTA-TRANSFER-PROGRAM-TD.doc
447 --
448 -- CHANGE HISTORY:
449 --
450 -- 30-APR-2005: Jim Zheng Created.
451 --
452 --===========================================================================
453 PROCEDURE create_trxs
454 (p_gta_trxs IN trx_tbl_type)
455 IS
456 l_procedure_name VARCHAR2(30) := 'create_TRXs';
457 l_gta_trx_tbl jmf_gta_trx_util.trx_tbl_type;
458 l_index NUMBER;
459
460 BEGIN
461 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
462 THEN
463 fnd_log.STRING(fnd_log.level_procedure
464 ,g_module_prefix || l_procedure_name
465 ,'Begin Procedure. ');
466 END IF;
467 -- begin log
468 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
469 THEN
470 log( 'begin create_trxs '||p_gta_trxs.COUNT);
471 END IF;
472 -- end log
473 l_gta_trx_tbl := p_gta_trxs;
474
475 -- loop by l_gta_trx_tbl, insert trx
476 l_index := l_gta_trx_tbl.FIRST;
477
478 WHILE l_index IS NOT NULL
479 LOOP
480 create_trx(l_gta_trx_tbl(l_index));
481 l_index := l_gta_trx_tbl.NEXT(l_index);
482
483 END LOOP;
484
485 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
486 THEN
487 fnd_log.STRING(fnd_log.level_procedure
488 ,g_module_prefix || l_procedure_name
489 ,'End Procedure. ');
490 END IF;
491
492 EXCEPTION
493 WHEN OTHERS THEN
494 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
495 THEN
496 fnd_log.STRING(fnd_log.level_unexpected
497 ,g_module_prefix || l_procedure_name ||
498 '. OTHER_EXCEPTION '
499 ,SQLCODE || SQLERRM);
500 END IF;
501 RAISE;
502
503 END create_trxs;
504
505 --==========================================================================
506 -- PROCEDURE NAME:
507 --
508 -- Create_Trx Public
509 --
510 -- DESCRIPTION:
511 --
512 -- This procedure is to insert a GTA transaction
513 --
514 -- PARAMETERS:
515 -- In: p_gta_trx Standard API parameter
516 --
517 --
518 -- DESIGN REFERENCES:
519 -- GTA-TRANSFER-PROGRAM-TD.doc
520 --
521 -- CHANGE HISTORY:
522 --
523 -- 30-APR-2005: Jim Zheng Created.
524 -- 03-JAN-2008: Subba added parameter for insert_row method calling
525 --===========================================================================
526 PROCEDURE create_trx
527 (p_gta_trx IN trx_rec_type)
528 IS
529
530 header_row_id VARCHAR2(30);
531 line_row_id VARCHAR2(30);
532 l_procedure_name VARCHAR2(30) := 'create_Trx';
533 l_count NUMBER;
534
535 BEGIN
536
537 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
538 THEN
539 fnd_log.STRING(fnd_log.level_procedure
540 ,g_module_prefix || l_procedure_name
541 ,'Begin Procedure. ');
542 END IF;
543
544 -- begin log
545 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
546 THEN
547 log('begin create_trx '||p_gta_trx.trx_header.ra_trx_id);
548 END IF;
549 -- end log
550
551 -- insert header
552 jmf_gta_trx_headers_all_pkg.insert_row
553 (p_row_id => header_row_id
554 ,p_ra_gl_date => p_gta_trx.trx_header.ra_gl_date
555 ,p_ra_gl_period => p_gta_trx.trx_header.ra_gl_period
556 ,p_set_of_books_id => p_gta_trx.trx_header.set_of_books_id
557 ,p_bill_to_customer_id => p_gta_trx.trx_header.bill_to_customer_id
558 ,p_bill_to_customer_number => p_gta_trx.trx_header.bill_to_customer_number
559 ,p_bill_to_customer_name => p_gta_trx.trx_header.bill_to_customer_name
560 ,p_source => p_gta_trx.trx_header.SOURCE
561 ,p_org_id => p_gta_trx.trx_header.org_id
562 ,p_rule_header_id => p_gta_trx.trx_header.rule_header_id
563 ,p_gta_trx_header_id => p_gta_trx.trx_header.gta_trx_header_id
564 ,p_gta_trx_number => p_gta_trx.trx_header.gta_trx_number
565 ,p_group_number => p_gta_trx.trx_header.group_number
566 ,p_version => p_gta_trx.trx_header.version
567 ,p_latest_version_flag => p_gta_trx.trx_header.latest_version_flag
568 ,p_transaction_date => p_gta_trx.trx_header.transaction_date
569 ,p_ra_trx_id => p_gta_trx.trx_header.ra_trx_id
570 ,p_ra_trx_number => p_gta_trx.trx_header.ra_trx_number
571 ,p_description => p_gta_trx.trx_header.description
572 ,p_customer_address => p_gta_trx.trx_header.customer_address
573 ,p_customer_phone => p_gta_trx.trx_header.customer_phone
574 ,p_customer_address_phone => p_gta_trx.trx_header.customer_address_phone
575 ,p_bank_account_name => p_gta_trx.trx_header.bank_account_name
576 ,p_bank_account_number => p_gta_trx.trx_header.bank_account_number
577 ,p_bank_account_name_number => p_gta_trx.trx_header.bank_account_name_number
578 ,p_fp_tax_registration_number => p_gta_trx.trx_header.fp_tax_registration_number -- fp registration number
579 ,p_tp_tax_registration_number => p_gta_trx.trx_header.tp_tax_registration_number -- tp registration number
580 ,p_legal_entity_id => p_gta_trx.trx_header.legal_entity_id -- legal entity id
581 ,p_ra_currency_code => p_gta_trx.trx_header.ra_currency_code
582 ,p_conversion_type => p_gta_trx.trx_header.conversion_type
583 ,p_conversion_date => p_gta_trx.trx_header.conversion_date
584 ,p_conversion_rate => p_gta_trx.trx_header.conversion_rate
585 ,p_gta_batch_number => p_gta_trx.trx_header.gta_batch_number
586 ,p_gt_invoice_number => p_gta_trx.trx_header.gt_invoice_number
587 ,p_gt_invoice_date => p_gta_trx.trx_header.gt_invoice_date
588 ,p_gt_invoice_net_amount => p_gta_trx.trx_header.gt_invoice_net_amount
589 ,p_gt_invoice_tax_amount => p_gta_trx.trx_header.gt_invoice_tax_amount
590 ,p_status => p_gta_trx.trx_header.status
591 ,p_sales_list_flag => p_gta_trx.trx_header.sales_list_flag
592 ,p_cancel_flag => p_gta_trx.trx_header.cancel_flag
593 ,p_gt_invoice_type => p_gta_trx.trx_header.gt_invoice_type
594 ,p_gt_invoice_class => p_gta_trx.trx_header.gt_invoice_class
595 ,p_gt_tax_month => p_gta_trx.trx_header.gt_tax_month
596 ,p_issuer_name => p_gta_trx.trx_header.issuer_name
597 ,p_reviewer_name => p_gta_trx.trx_header.reviewer_name
598 ,p_payee_name => p_gta_trx.trx_header.payee_name
599 ,p_tax_code => p_gta_trx.trx_header.tax_code
600 ,p_tax_rate => p_gta_trx.trx_header.tax_rate
601 ,p_generator_id => p_gta_trx.trx_header.generator_id
602 ,p_export_request_id => p_gta_trx.trx_header.export_request_id
603 ,p_request_id => p_gta_trx.trx_header.request_id
604 ,p_program_application_id => p_gta_trx.trx_header.program_application_id
605 ,p_program_id => p_gta_trx.trx_header.program_id
606 ,p_program_update_date => p_gta_trx.trx_header.program_update_date
607 ,p_attribute_category => p_gta_trx.trx_header.attribute_category
608 ,p_attribute1 => p_gta_trx.trx_header.attribute1
609 ,p_attribute2 => p_gta_trx.trx_header.attribute2
610 ,p_attribute3 => p_gta_trx.trx_header.attribute3
611 ,p_attribute4 => p_gta_trx.trx_header.attribute4
612 ,p_attribute5 => p_gta_trx.trx_header.attribute5
613 ,p_attribute6 => p_gta_trx.trx_header.attribute6
614 ,p_attribute7 => p_gta_trx.trx_header.attribute7
615 ,p_attribute8 => p_gta_trx.trx_header.attribute8
616 ,p_attribute9 => p_gta_trx.trx_header.attribute9
617 ,p_attribute10 => p_gta_trx.trx_header.attribute10
618 ,p_attribute11 => p_gta_trx.trx_header.attribute11
619 ,p_attribute12 => p_gta_trx.trx_header.attribute12
620 ,p_attribute13 => p_gta_trx.trx_header.attribute13
621 ,p_attribute14 => p_gta_trx.trx_header.attribute14
622 ,p_attribute15 => p_gta_trx.trx_header.attribute15
623 ,p_creation_date => p_gta_trx.trx_header.creation_date
624 ,p_created_by => p_gta_trx.trx_header.created_by
625 ,p_last_update_date => p_gta_trx.trx_header.last_update_date
626 ,p_last_updated_by => p_gta_trx.trx_header.last_updated_by
627 ,p_last_update_login => p_gta_trx.trx_header.last_update_login
628 ,p_invoice_type => p_gta_trx.trx_header.invoice_type
629 );
630
631 -- insert rows
632 l_count := p_gta_trx.trx_lines.FIRST;
633 WHILE l_count IS NOT NULL
634 LOOP
635 -- begin log
636 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
637 THEN
638 log( 'begin create_trx_line '||p_gta_trx.trx_lines(l_count).ar_trx_line_id);
639 END IF;
640 -- end log
641
642 jmf_gta_trx_lines_all_pkg.insert_row
643 (p_rowid => line_row_id
644 ,p_org_id => p_gta_trx.trx_lines(l_count).org_id
645 ,p_gta_trx_header_id => p_gta_trx.trx_lines(l_count).gta_trx_header_id
646 ,p_gta_trx_line_id => p_gta_trx.trx_lines(l_count).gta_trx_line_id
647 ,p_matched_flag => p_gta_trx.trx_lines(l_count).matched_flag
648 ,p_line_number => p_gta_trx.trx_lines(l_count).line_number
649 ,p_ar_trx_line_id => p_gta_trx.trx_lines(l_count).ar_trx_line_id
650 ,p_inventory_item_id => p_gta_trx.trx_lines(l_count).inventory_item_id
651 ,p_item_number => p_gta_trx.trx_lines(l_count).item_number
652 ,p_item_description => p_gta_trx.trx_lines(l_count).item_description
653 ,p_item_model => p_gta_trx.trx_lines(l_count).item_model
654 ,p_item_tax_denomination => p_gta_trx.trx_lines(l_count).item_tax_denomination
655 ,p_tax_rate => p_gta_trx.trx_lines(l_count).tax_rate
656 ,p_uom => p_gta_trx.trx_lines(l_count).uom
657 ,p_uom_name => p_gta_trx.trx_lines(l_count).uom_name
658 ,p_quantity => p_gta_trx.trx_lines(l_count).quantity
659 ,p_price_flag => p_gta_trx.trx_lines(l_count).price_flag
660 ,p_unit_price => p_gta_trx.trx_lines(l_count).unit_price
661 ,p_unit_tax_price => p_gta_trx.trx_lines(l_count).unit_tax_price
662 ,p_amount => p_gta_trx.trx_lines(l_count).amount
663 ,p_original_currency_amount => p_gta_trx.trx_lines(l_count).original_currency_amount
664 ,p_tax_amount => p_gta_trx.trx_lines(l_count).tax_amount
665 ,p_discount_flag => p_gta_trx.trx_lines(l_count).discount_flag
666 ,p_enabled_flag => p_gta_trx.trx_lines(l_count).enabled_flag
667 ,p_request_id => p_gta_trx.trx_lines(l_count).request_id
668 ,p_program_application_id => p_gta_trx.trx_lines(l_count).program_applicaton_id
669 ,p_program_id => p_gta_trx.trx_lines(l_count).program_id
670 ,p_program_update_date => p_gta_trx.trx_lines(l_count).program_update_date
671 ,p_attribute_category => p_gta_trx.trx_lines(l_count).attribute_category
672 ,p_attribute1 => p_gta_trx.trx_lines(l_count).attribute1
673 ,p_attribute2 => p_gta_trx.trx_lines(l_count).attribute2
674 ,p_attribute3 => p_gta_trx.trx_lines(l_count).attribute3
675 ,p_attribute4 => p_gta_trx.trx_lines(l_count).attribute4
676 ,p_attribute5 => p_gta_trx.trx_lines(l_count).attribute5
677 ,p_attribute6 => p_gta_trx.trx_lines(l_count).attribute6
678 ,p_attribute7 => p_gta_trx.trx_lines(l_count).attribute7
679 ,p_attribute8 => p_gta_trx.trx_lines(l_count).attribute8
680 ,p_attribute9 => p_gta_trx.trx_lines(l_count).attribute9
681 ,p_attribute10 => p_gta_trx.trx_lines(l_count).attribute10
682 ,p_attribute11 => p_gta_trx.trx_lines(l_count).attribute11
683 ,p_attribute12 => p_gta_trx.trx_lines(l_count).attribute12
684 ,p_attribute13 => p_gta_trx.trx_lines(l_count).attribute13
685 ,p_attribute14 => p_gta_trx.trx_lines(l_count).attribute14
686 ,p_attribute15 => p_gta_trx.trx_lines(l_count).attribute15
687 ,p_creation_date => p_gta_trx.trx_lines(l_count).creation_date
688 ,p_created_by => p_gta_trx.trx_lines(l_count).created_by
689 ,p_last_update_date => p_gta_trx.trx_lines(l_count).last_update_date
690 ,p_last_updated_by => p_gta_trx.trx_lines(l_count).last_updated_by
691 ,p_last_update_login => p_gta_trx.trx_lines(l_count).last_update_login
692 );
693
694 l_count := p_gta_trx.trx_lines.NEXT(l_count);
695 END LOOP;
696
697 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
698 THEN
699 fnd_log.STRING(fnd_log.level_procedure
700 ,g_module_prefix || l_procedure_name
701 ,'End Procedure. ');
702 END IF;
703
704 EXCEPTION
705 WHEN dup_val_on_index THEN
706 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
707 THEN
708 fnd_log.STRING(fnd_log.level_unexpected
709 ,g_module_prefix || l_procedure_name ||
710 '. dup_val_on_index '
711 ,SQLCODE || SQLERRM);
712 END IF;
713
714 WHEN OTHERS THEN
715 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
716 THEN
717 fnd_log.STRING(fnd_log.level_unexpected
718 ,g_module_prefix || l_procedure_name ||
719 '. OTHER_EXCEPTION '
720 ,'Exception occur when insert data into database' ||
721 SQLCODE || SQLERRM);
722
723 -- begin log
724 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
725 THEN
726 log( 'Exception occur when insert data into database' ||SQLCODE || SQLERRM);
727 END IF;
728 -- end log
729
730 END IF;
731 RAISE;
732
733 END create_trx;
734
735 --==========================================================================
736 -- FUNCTION NAME:
737 --
738 -- Get_Gtainvoice_Amount Public
739 --
740 -- DESCRIPTION:
741 --
742 -- This procedure is to calculate total amount of a GTA invoice
743 --
744 -- PARAMETERS:
745 -- In: p_header_id Identifier of GTA Invoice header
746 --
747 -- Return: NUMBER
748 --
749 -- DESIGN REFERENCES:
750 -- GTA-TRANSFER-PROGRAM-TD.doc
751 --
752 -- CHANGE HISTORY:
753 --
754 -- 30-APR-2005 Jim Zheng Created.
755 -- 04-AUG-2005 Donghai Wang modified query clause to remove
756 -- reference to price_flag
757 --
758 --===========================================================================
759 FUNCTION Get_Gtainvoice_Amount
760 (p_header_id IN NUMBER
761 )
762 RETURN NUMBER
763 IS
764 l_ret NUMBER;
765 BEGIN
766 SELECT
767 SUM(nvl(amount,0))
768 INTO
769 l_ret
770 FROM
771 jmf_gta_trx_lines_all
772 WHERE gta_trx_header_id = p_header_id
773 AND enabled_flag = 'Y';
774
775 RETURN l_ret;
776 END Get_Gtainvoice_Amount;
777
778 --==========================================================================
779 -- FUNCTION NAME:
780 --
781 -- Get_Gtainvoice_Original_Amount Public
782 --
783 -- DESCRIPTION:
784 --
785 -- This procedure is to calculate total amount of a GTA invoice
786 -- in original currency code
787 --
788 -- PARAMETERS:
789 -- In: p_header_id Identifier of GTA Invoice header
790 --
791 -- Return: NUMBER
792 --
793 -- DESIGN REFERENCES:
794 -- GTA-TRANSFER-PROGRAM-TD.doc
795 --
796 -- CHANGE HISTORY:
797 --
798 -- 30-APR-2005: Jim Zheng Created.
799 --
800 --===========================================================================
801 FUNCTION get_gtainvoice_original_amount
802 (p_header_id IN NUMBER)
803 RETURN NUMBER
804 IS
805 l_ret NUMBER;
806 CURSOR c_original_amount IS
807 SELECT
808 SUM(nvl(original_currency_amount,0))
809 FROM
810 jmf_gta_trx_lines_all
811 WHERE gta_trx_header_id = p_header_id
812 AND enabled_flag = 'Y';
813 BEGIN
814 OPEN c_original_amount;
815 FETCH c_original_amount
816 INTO l_ret;
817 CLOSE c_original_amount;
818
819 RETURN(nvl(l_ret
820 ,0));
821 END get_gtainvoice_original_amount;
822
823 --==========================================================================
824 -- PROCEDURE NAME:
825 --
826 -- Delete_Header_Line_Cascade Public
827 --
828 -- DESCRIPTION:
829 --
830 -- This procedure is to cascade delete a special GTA/GT
831 -- invoice header with all lines associated with it
832 --
833 -- PARAMETERS:
834 -- In: p_gta_trx_header_id GTA/GT invoice header identifier
835 --
836 -- DESIGN REFERENCES:
837 -- GTA-PURGE-PROGRAM-TD.doc
838 --
839 -- CHANGE HISTORY:
840 --
841 -- 8-MAY-2005: Qiang Li Created
842 --
843 --===========================================================================
844 PROCEDURE delete_header_line_cascade
845 (p_gta_trx_header_id IN NUMBER)
846 IS
847 BEGIN
848 --Delete lines
849 DELETE jmf_gta_trx_lines_all
850 WHERE gta_trx_header_id = p_gta_trx_header_id;
851
852 --Delete Headers
853 DELETE jmf_gta_trx_headers_all
854 WHERE gta_trx_header_id = p_gta_trx_header_id;
855 END delete_header_line_cascade;
856
857 --==========================================================================
858 -- FUNCTION NAME:
859 --
860 -- Get_Gtainvoice_Tax_Amount Public
861 --
862 -- DESCRIPTION:
863 --
864 -- This procedure Get Gtainvoice Tax Amount
865 --
866 -- PARAMETERS:
867 -- In: p_header_id identifier of Gta Invoice
868 --
869 -- Return: NUMBER
870 --
871 -- DESIGN REFERENCES:
872 -- GTA_Reports_TD.doc
873 --
874 -- CHANGE HISTORY:
875 --
876 -- 8-MAY-2005: Qiang Li Created
877 --
878 --===========================================================================
879 FUNCTION get_gtainvoice_tax_amount
880 (p_header_id IN NUMBER)
881 RETURN NUMBER
882 IS
883 l_ret NUMBER;
884 BEGIN
885 SELECT SUM(nvl(tax_amount
886 ,0))
887 INTO l_ret
888 FROM jmf_gta_trx_lines
889 WHERE gta_trx_header_id = p_header_id
890 AND enabled_flag = 'Y';
891 RETURN l_ret;
892 END get_gtainvoice_tax_amount;
893
894 --==========================================================================
895 -- FUNCTION NAME:
896 --
897 -- Check_Taxcount_Of_Arline Public
898 --
899 -- DESCRIPTION:
900 --
901 -- This function is used to check if one AR line has multiple tax line per
902 -- Tax type and GT currency defined on GTA system option form.
903 --
904 -- PARAMETERS:
905 -- In: p_org_id Identifier of operating unit
906 -- p_customer_trx_line_id Identifier of transaction line id
907 --
908 -- Return: BOOLEAN
909 --
910 -- DESIGN REFERENCES:
911 -- GTA_Reports_TD.doc
912 --
913 -- CHANGE HISTORY:
914 --
915 -- 25-Nov-2005: Donghai Wang Created
916 --
917 --===========================================================================
918 FUNCTION Check_Taxcount_Of_Arline
919 (p_org_id IN NUMBER
920 ,p_customer_trx_line_id IN NUMBER
921 )
922 RETURN BOOLEAN
923 IS
924 l_tax_type_code zx_lines.tax_type_code%TYPE;
925 l_taxline_count NUMBER;
926 l_gt_currency_code fnd_currencies.currency_code%TYPE;
927 l_trx_id ra_customer_trx_all.customer_trx_id%TYPE;--jogen bug5212702 May-17,2006
928
929 CURSOR c_tax_type_code
930 IS
931 SELECT
932 vat_tax_type_code
933 ,gt_currency_code
934 FROM
935 jmf_gta_system_parameters_all
936 WHERE org_id=p_org_id;
937
938 CURSOR c_taxline_count(pc_trx_id NUMBER)
939 IS
940 SELECT
941 COUNT(*)
942 FROM
943 zx_lines
944 WHERE trx_line_id=p_customer_trx_line_id
945 AND entity_code='TRANSACTIONS'
946 AND application_id = 222
947 AND trx_level_type='LINE'
948 AND tax_type_code=l_tax_type_code
949 AND tax_currency_code=l_gt_currency_code
950 AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--jogen bug5212702 May-17,2006
951 AND trx_id=pc_trx_id; --jogen bug5212702 May-17,2006
952
953 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
954 l_proc_level NUMBER := fnd_log.level_procedure;
955 l_procedure_name VARCHAR2(30) := 'Check_Taxcount_Of_Arline';
956
957 BEGIN
958 --logging for debug
959 IF (l_proc_level >= l_dbg_level)
960 THEN
961 fnd_log.STRING(l_proc_level
962 ,g_module_prefix || l_procedure_name || '.begin'
963 ,'Enter function');
964 END IF; --l_proc_level>=l_dbg_level)
965
966
967 --Get Vat tax type and GT currency coe defined in GTA system options form
968 --for current operating unit
969 OPEN c_tax_type_code;
970 FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
971 CLOSE c_tax_type_code;
972
973 --Get count of tax line for a AR line
974
975 ----jogen bug5212702 May-17,2006
976 -- OPEN c_taxline_count;
977 SELECT customer_trx_id
978 INTO l_trx_id
979 FROM ra_customer_trx_lines_all
980 WHERE customer_trx_line_id=p_customer_trx_line_id;
981
982 OPEN c_taxline_count(l_trx_id);
983 --jogen bug5212702 May-17,2006
984
985 FETCH c_taxline_count INTO l_taxline_count;
986 CLOSE c_taxline_count;
987
988 --logging for debug
989 IF (l_proc_level >= l_dbg_level)
990 THEN
991 fnd_log.STRING(l_proc_level
992 ,g_module_prefix || l_procedure_name || '.End'
993 ,'Exit function');
994 END IF; --l_proc_level>=l_dbg_level)
995
996 IF l_taxline_count=1
997 THEN
998 RETURN(TRUE);
999 ELSE
1000 RETURN(FALSE);
1001 END IF; --l_taxline_count=1
1002
1003 END Check_Taxcount_Of_Arline;
1004
1005
1006 --==========================================================================
1007 -- FUNCTION NAME:
1008 --
1009 -- Check_Taxcount_Of_Artrx Public
1010 --
1011 -- DESCRIPTION:
1012 --
1013 -- This function is used to check if AR lines belong to one AR transaction
1014 -- have multiple tax line per Tax type and GT currency defined on GTA system
1015 -- option form.
1016 --
1017 -- PARAMETERS:
1018 -- In: p_org_id Identifier of operating unit
1019 -- p_customer_trx_id Identifier of AR transaciton
1020 --
1021 -- Return: BOOLEAN
1022 --
1023 -- DESIGN REFERENCES:
1024 -- GTA_Reports_TD.doc
1025 --
1026 -- CHANGE HISTORY:
1027 --
1028 -- 25-Nov-2005: Donghai Wang Created
1029 --
1030 --===========================================================================
1031 FUNCTION Check_Taxcount_Of_Artrx
1032 (p_org_id IN NUMBER
1033 ,p_customer_trx_id IN NUMBER
1034 )
1035 RETURN BOOLEAN
1036 IS
1037 l_tax_type_code zx_lines.tax_type_code%TYPE;
1038 l_taxline_count NUMBER;
1039 l_gt_currency_code fnd_currencies.currency_code%TYPE;
1040
1041
1042
1043 CURSOR c_tax_type_code
1044 IS
1045 SELECT
1046 vat_tax_type_code
1047 ,gt_currency_code
1048 FROM
1049 jmf_gta_system_parameters_all
1050 WHERE org_id=p_org_id;
1051
1052 CURSOR c_tax_line_count
1053 IS
1054 SELECT COUNT(*)
1055 FROM
1056 (SELECT
1057 trx_line_id
1058 ,COUNT(*)
1059 FROM
1060 zx_lines
1061 WHERE application_id = 222
1062 AND trx_id=p_customer_trx_id
1063 AND trx_level_type='LINE'
1064 AND entity_code='TRANSACTIONS'
1065 AND tax_type_code=l_tax_type_code
1066 AND tax_currency_code=l_gt_currency_code
1067 AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--jogen bug5212702 May-17,2006
1068 GROUP BY trx_line_id
1069 HAVING COUNT(*)>1);
1070
1071
1072
1073 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1074 l_proc_level NUMBER := fnd_log.level_procedure;
1075 l_procedure_name VARCHAR2(30) := 'Check_Taxcount_Of_Artrx';
1076
1077 BEGIN
1078 --logging for debug
1079 IF (l_proc_level >= l_dbg_level)
1080 THEN
1081 fnd_log.STRING(l_proc_level
1082 ,g_module_prefix || l_procedure_name || '.begin'
1083 ,'Enter function');
1084 END IF; --l_proc_level>=l_dbg_level)
1085
1086
1087 --Get Vat tax type and GT currency code defined in GTA system options form
1088 --for current operating unit
1089 OPEN c_tax_type_code;
1090 FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
1091 CLOSE c_tax_type_code;
1092
1093 --Get count of lines which have multiple tax lines for an AR transactions
1094 OPEN c_tax_line_count;
1095 FETCH c_tax_line_count INTO l_taxline_count;
1096 CLOSE c_tax_line_count;
1097
1098 --logging for debug
1099 IF (l_proc_level >= l_dbg_level)
1100 THEN
1101 fnd_log.STRING(l_proc_level
1102 ,g_module_prefix || l_procedure_name || '.End'
1103 ,'Exit function');
1104 END IF; --l_proc_level>=l_dbg_level)
1105
1106 IF l_taxline_count=0
1107 THEN
1108 RETURN(TRUE);
1109 ELSE
1110 RETURN(FALSE);
1111 END IF; --l_taxline_count=0
1112
1113 END Check_Taxcount_Of_Artrx;
1114
1115 --==========================================================================
1116 -- FUNCTION NAME:
1117 --
1118 -- Get_Arinvoice_Amount Public
1119 --
1120 -- DESCRIPTION:
1121 --
1122 -- This Function is to get taxable amount of an AR transaction per VAT tax
1123 -- type and GT currency code defind in GTA 'system options' form
1124 --
1125 -- PARAMETERS:
1126 -- In: p_org_id identifier of operating unit
1127 -- p_customer_trx_id identifier of AR transaction
1128 --
1129 -- Return: NUMBER
1130 --
1131 -- DESIGN REFERENCES:
1132 -- GTA_Reports_TD.doc
1133 --
1134 -- CHANGE HISTORY:
1135 --
1136 -- 8-MAY-2005: Qiang Li Created
1137 -- 25-Nov-2005: Donghai Wang update code due to ebtax requirement
1138 --===========================================================================
1139 FUNCTION Get_Arinvoice_Amount
1140 (p_org_id IN NUMBER
1141 ,p_customer_trx_id IN NUMBER
1142 )
1143 RETURN NUMBER
1144 IS
1145 l_procedure_name VARCHAR2(30) := 'Get_Arinvoice_Amount';
1146 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1147 l_proc_level NUMBER := fnd_log.level_procedure;
1148
1149 l_tax_type_code zx_lines.tax_type_code%TYPE;
1150 l_gt_currency_code fnd_currencies.currency_code%TYPE;
1151 l_ar_taxable_amount NUMBER;
1152
1153
1154
1155 CURSOR c_tax_type_code
1156 IS
1157 SELECT
1158 vat_tax_type_code
1159 ,gt_currency_code
1160 FROM
1161 jmf_gta_system_parameters_all
1162 WHERE org_id=p_org_id;
1163
1164 CURSOR c_ar_taxable_amount
1165 IS
1166 SELECT
1167 NVL(SUM(taxable_amt_tax_curr),0)
1168 FROM
1169 zx_lines
1170 WHERE application_id = 222
1171 AND trx_id=p_customer_trx_id
1172 AND trx_level_type='LINE'
1173 AND entity_code='TRANSACTIONS'
1174 AND tax_type_code=l_tax_type_code
1175 AND tax_currency_code=l_gt_currency_code
1176 AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO'); --Donghai Wang bug5212702 May-17,2006
1177
1178 BEGIN
1179 --logging for debug
1180 IF (l_proc_level >= l_dbg_level)
1181 THEN
1182 fnd_log.STRING(l_proc_level
1183 ,g_module_prefix || l_procedure_name || '.begin'
1184 ,'enter function');
1185 END IF;--(l_proc_level >= l_dbg_level)
1186
1187 --Get Vat tax type and GT currency code defined in GTA system options form
1188 --for current operating unit
1189 OPEN c_tax_type_code;
1190 FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
1191 CLOSE c_tax_type_code;
1192
1193 --Get total taxable amount of lines for an AR transactions
1194 OPEN c_ar_taxable_amount;
1195 FETCH c_ar_taxable_amount INTO l_ar_taxable_amount;
1196 CLOSE c_ar_taxable_amount;
1197
1198
1199 --logging for debug
1200 IF (l_proc_level >= l_dbg_level)
1201 THEN
1202 fnd_log.STRING(l_proc_level
1203 ,g_module_prefix || l_procedure_name || '.end'
1204 ,'end function');
1205 END IF; --(l_proc_level >= l_dbg_level)
1206
1207 RETURN l_ar_taxable_amount;
1208 END Get_Arinvoice_Amount;
1209
1210 --==========================================================================
1211 -- FUNCTION NAME:
1212 --
1213 -- Get_Arinvoice_Tax_Amount Public
1214 --
1215 -- DESCRIPTION:
1216 --
1217 -- This Function is to get tax amount of an AR transaction per VAT tax
1218 -- type and GT currency code defind in GTA 'system options' form
1219 --
1220 -- PARAMETERS:
1221 -- In: p_org_id identifier of operating unit
1222 -- p_customer_trx_id identifier of AR transaction
1223 --
1224 -- Return: Number
1225 --
1226 -- DESIGN REFERENCES:
1227 -- GTA_Reports_TD.doc
1228 --
1229 -- CHANGE HISTORY:
1230 --
1231 -- 8-MAY-2005: Qiang Li Created
1232 -- 25-Nov-2005: Donghai Wang update code due to ebtax requirement
1233 --===========================================================================
1234 FUNCTION Get_Arinvoice_Tax_Amount
1235 (p_org_id IN NUMBER
1236 ,p_customer_trx_id IN NUMBER
1237 )
1238 RETURN NUMBER
1239 IS
1240 l_procedure_name VARCHAR2(30) := 'Get_Arinvoice_Tax_Amount';
1241 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1242 l_proc_level NUMBER := fnd_log.level_procedure;
1243
1244 l_tax_type_code zx_lines.tax_type_code%TYPE;
1245 l_gt_currency_code fnd_currencies.currency_code%TYPE;
1246 l_ar_tax_amount NUMBER;
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 jmf_gta_system_parameters_all
1257 WHERE org_id=p_org_id;
1258
1259 CURSOR c_ar_tax_amount
1260 IS
1261 SELECT
1262 NVL(SUM(tax_amt_tax_curr),0)
1263 FROM
1264 zx_lines
1265 WHERE application_id = 222
1266 AND trx_id=p_customer_trx_id
1267 AND trx_level_type='LINE'
1268 AND entity_code='TRANSACTIONS'
1269 AND tax_type_code=l_tax_type_code
1270 AND tax_currency_code=l_gt_currency_code
1271 AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO'); --Donghai Wang bug5212702 May-17,2006;
1272
1273 BEGIN
1274 --logging for debug
1275 IF (l_proc_level >= l_dbg_level)
1276 THEN
1277 fnd_log.STRING(l_proc_level
1278 ,g_module_prefix || l_procedure_name || '.begin'
1279 ,'enter function');
1280 END IF;--(l_proc_level >= l_dbg_level)
1281
1282 --Get Vat tax type and GT currency code defined in GTA system options form
1283 --for current operating unit
1284 OPEN c_tax_type_code;
1285 FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
1286 CLOSE c_tax_type_code;
1287
1288 --Get total VAT tax amount of AR transaction
1289 OPEN c_ar_tax_amount;
1290 FETCH c_ar_tax_amount INTO l_ar_tax_amount ;
1291 CLOSE c_ar_tax_amount;
1292
1293
1294 --logging for debug
1295 IF (l_proc_level >= l_dbg_level)
1296 THEN
1297 fnd_log.STRING(l_proc_level
1298 ,g_module_prefix || l_procedure_name || '.end'
1299 ,'end function');
1300 END IF; --(l_proc_level >= l_dbg_level)
1301
1302 RETURN l_ar_tax_amount;
1303 END Get_Arinvoice_Tax_Amount;
1304
1305
1306 --==========================================================================
1307 -- PROCEDURE NAME:
1308 --
1309 -- Get_New_TRX_Num Private
1310 --
1311 -- DESCRIPTION:
1312 --
1313 -- This procedure is to get a new trx number
1314 --
1315 -- PARAMETERS:
1316 -- In: p_trx_id Identifier of AR transaction
1317 -- p_group_number Group number
1318 -- p_version_number Version
1319 -- p_org_id Identifier of operating unit
1320 --
1321 -- Out: x_gta_trx_number Number of GTA invoice
1322 --
1323 -- DESIGN REFERENCES:
1324 -- GTA-TRANSFER-PROGRAM-TD.doc
1325 --
1326 -- CHANGE HISTORY:
1327 --
1328 -- 23-MAy-2005: Jim.zheng Creation
1329 --
1330 --===========================================================================
1331 PROCEDURE get_new_trx_num
1332 (p_trx_id IN VARCHAR2
1333 ,p_group_number IN VARCHAR2
1334 ,p_version_number IN VARCHAR2
1335 ,x_gta_trx_number OUT NOCOPY VARCHAR2
1336 )
1337 IS
1338 boundary VARCHAR2(1) := '-';
1339
1340 BEGIN
1341 x_gta_trx_number := p_trx_id || boundary || p_group_number || boundary ||
1342 p_version_number;
1343 END get_new_trx_num;
1344
1345 --==========================================================================
1346 -- FUNCTION NAME:
1347 --
1348 -- Format_Date Public
1349 --
1350 -- DESCRIPTION:
1351 --
1352 -- This funtion is to get appropriate format string for
1353 -- a given date according the ICX_DATE_FORMAT_MASK profile
1354 --
1355 -- PARAMETERS:
1356 -- In: p_date The date to be formate
1357 --
1358 -- Return: VARCHAR2
1359 --
1360 -- DESIGN REFERENCES:
1361 -- GTA_Reports_TD.doc
1362 --
1363 -- CHANGE HISTORY:
1364 --
1365 -- 23-MAy-2005: Qiang Li Creation
1366 --
1367 --===========================================================================
1368 FUNCTION format_date(p_date IN DATE) RETURN VARCHAR2 IS
1369 l_procedure_name VARCHAR2(30) := 'Format_Date';
1370 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1371 l_proc_level NUMBER := fnd_log.level_procedure;
1372 l_ret VARCHAR(40);
1373
1374 l_date_format fnd_profile_option_values.profile_option_value%TYPE := NULL;
1375
1376 BEGIN
1377 --logging for debug
1378 IF (l_proc_level >= l_dbg_level)
1379 THEN
1380 fnd_log.STRING(l_proc_level
1381 ,g_module_prefix || l_procedure_name || '.begin'
1382 ,'enter function');
1383 END IF;
1384
1385 fnd_profile.get('ICX_DATE_FORMAT_MASK'
1386 ,l_date_format);
1387 l_ret := to_char(p_date
1388 ,nvl(l_date_format
1389 ,'Rrrr-Mm-Dd'));
1390
1391 --logging for debug
1392 IF (l_proc_level >= l_dbg_level)
1393 THEN
1394 fnd_log.STRING(l_proc_level
1395 ,g_module_prefix || l_procedure_name || '.end'
1396 ,'end function');
1397 END IF;
1398
1399 RETURN l_ret;
1400 END format_date;
1401
1402 --==========================================================================
1403 -- FUNCTION NAME:
1404 --
1405 -- Get_Primary_Phone_Number Public
1406 --
1407 -- DESCRIPTION:
1408 --
1409 -- This procedure is to get primary phone number for a given customer
1410 --
1411 -- PARAMETERS:
1412 -- In: p_customer_id Customer identifier
1413 --
1414 -- Return: VARCHAR2
1415 --
1416 -- DESIGN REFERENCES:
1417 -- GTA_Reports_TD.doc
1418 --
1419 -- CHANGE HISTORY:
1420 --
1421 -- 23-May-2005: Donghai Wang Created
1422 -- 26-Jun-2006: Donghai Wang In the cursor c_phone_number, add sub
1423 -- query to fetch party_id by
1424 -- "bill to customer id" passed in,instead
1425 -- of using "bill to customer id"
1426 -- directly.
1427 -- 21-May-2006 Donghai Wang Fix the bug 5263009
1428 --
1429 --===========================================================================
1430 FUNCTION get_primary_phone_number
1431 (p_customer_id IN NUMBER
1432 )
1433 RETURN VARCHAR2
1434 IS
1435 l_customer_id hz_parties.party_id%TYPE := p_customer_id;
1436 l_phone_number hz_contact_points.phone_number%TYPE;
1437
1438 --Fix bug 5263009, Donghai Wang
1439 --Add the sub query to get party id by customer id
1440 CURSOR c_phone_number
1441 IS
1442 SELECT
1443 hcp.phone_number
1444 FROM
1445 hz_contact_points hcp
1446 WHERE hcp.contact_point_type = 'PHONE'
1447 AND hcp.owner_table_name = 'HZ_PARTIES'
1448 AND hcp.owner_table_id = (SELECT
1449 party_id
1450 FROM
1451 hz_cust_accounts_all
1452 WHERE cust_account_id=l_customer_id
1453 )
1454 AND hcp.primary_flag = 'Y';
1455
1456 l_procedure_name VARCHAR2(30) := 'Get_Primary_Phone_Number';
1457 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1458 l_proc_level NUMBER := fnd_log.level_procedure;
1459 BEGIN
1460
1461 --logging for debug
1462 IF (l_proc_level >= l_dbg_level)
1463 THEN
1464 fnd_log.STRING(l_proc_level
1465 ,g_module_prefix || l_procedure_name || '.begin'
1466 ,'Enter function');
1467 END IF; --l_proc_level>=l_dbg_level)
1468 OPEN c_phone_number;
1469 FETCH c_phone_number
1470 INTO l_phone_number;
1471 CLOSE c_phone_number;
1472
1473 --logging for debug
1474 IF (l_proc_level >= l_dbg_level)
1475 THEN
1476 fnd_log.STRING(l_proc_level
1477 ,g_module_prefix || l_procedure_name || '.End'
1478 ,'Exit function');
1479 END IF; --l_proc_level>=l_dbg_level)
1480
1481 RETURN(l_phone_number);
1482 END get_primary_phone_number;
1483
1484 --==========================================================================
1485 -- FUNCTION NAME:
1486 --
1487 -- Get_Operatingunit Public
1488 --
1489 -- DESCRIPTION:
1490 --
1491 -- This function is to get operating unit for a given org_id
1492 --
1493 -- PARAMETERS:
1494 -- In: p_org_id Identifier of Operating Unit
1495 --
1496 -- Return: VARCHAR2
1497 --
1498 -- DESIGN REFERENCES:
1499 -- GTA_Reports_TD.doc
1500 --
1501 -- CHANGE HISTORY:
1502 --
1503 -- 23-MAy-2005: Qiang Li Creation
1504 -- 26-Dec-2005: Qiang Li fix a performance issue
1505 --=========================================================================
1506 FUNCTION get_operatingunit(p_org_id IN NUMBER) RETURN VARCHAR2 IS
1507 l_procedure_name VARCHAR2(30) := 'Get_OperatingUnit';
1508 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1509 l_proc_level NUMBER := fnd_log.level_procedure;
1510
1511 l_operating_unit hr_operating_units.NAME%TYPE;
1512 CURSOR c_operating_unit IS
1513 SELECT OTL.NAME
1514 FROM HR_ALL_ORGANIZATION_UNITS O
1515 , HR_ALL_ORGANIZATION_UNITS_TL OTL
1516 WHERE O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
1517 AND OTL.LANGUAGE = userenv('LANG')
1518 AND O.ORGANIZATION_ID = p_org_id;
1519
1520 BEGIN
1521 --logging for debug
1522 IF (l_proc_level >= l_dbg_level)
1523 THEN
1524 fnd_log.STRING(l_proc_level
1525 ,g_module_prefix || l_procedure_name || '.begin'
1526 ,'enter function');
1527 END IF;
1528
1529 OPEN c_operating_unit;
1530 FETCH
1531 c_operating_unit
1532 INTO
1533 l_operating_unit;
1534
1535 CLOSE c_operating_unit;
1536
1537 --logging for debug
1538 IF (l_proc_level >= l_dbg_level)
1539 THEN
1540 fnd_log.STRING(l_proc_level
1541 ,g_module_prefix || l_procedure_name || '.end'
1542 ,'end function');
1543 END IF;
1544
1545 RETURN(l_operating_unit);
1546 END get_operatingunit;
1547
1548 --==========================================================================
1549 -- FUNCTION NAME:
1550 --
1551 -- Get_Customer_Name Public
1552 --
1553 -- DESCRIPTION:
1554 --
1555 -- This function is to get Customer name for a given customer id
1556 --
1557 -- PARAMETERS:
1558 -- In: p_customer_id customer identifier
1559 --
1560 -- Return: VARCHAR2
1561 --
1562 -- DESIGN REFERENCES:
1563 -- GTA_Reports_TD.doc
1564 --
1565 -- CHANGE HISTORY:
1566 --
1567 -- 23-MAy-2005: Qiang Li Creation
1568 --
1569 --=========================================================================
1570 FUNCTION get_customer_name
1571 (p_customer_id IN NUMBER)
1572 RETURN VARCHAR2
1573 IS
1574 l_procedure_name VARCHAR2(30) := 'Get_Customer_Name';
1575 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1576 l_proc_level NUMBER := fnd_log.level_procedure;
1577
1578 l_customer_name hz_parties.party_name%TYPE;
1579 CURSOR c_customer_name IS
1580 SELECT
1581 p.party_name
1582 FROM
1583 hz_parties p
1584 ,hz_cust_accounts a
1585 WHERE a.cust_account_id = p_customer_id
1586 AND p.party_id = a.party_id;
1587
1588 BEGIN
1589 --logging for debug
1590 IF (l_proc_level >= l_dbg_level)
1591 THEN
1592 fnd_log.STRING(l_proc_level
1593 ,g_module_prefix || l_procedure_name || '.begin'
1594 ,'enter function');
1595 END IF;
1596
1597 OPEN c_customer_name;
1598
1599 FETCH
1600 c_customer_name
1601 INTO
1602 l_customer_name;
1603
1604 CLOSE c_customer_name;
1605
1606 --logging for debug
1607 IF (l_proc_level >= l_dbg_level)
1608 THEN
1609 fnd_log.STRING(l_proc_level
1610 ,g_module_prefix || l_procedure_name || '.end'
1611 ,'end function');
1612 END IF;
1613
1614 RETURN(l_customer_name);
1615 END get_customer_name;
1616
1617 --==========================================================================
1618 -- FUNCTION NAME:
1619 --
1620 -- Get_Arline_Amount Public
1621 --
1622 -- DESCRIPTION:
1623 --
1624 -- This function is used to get line amount per Golden Tax currency for
1625 -- one AR line
1626 --
1627 --
1628 -- PARAMETERS:
1629 -- In: p_org_id identifier of operating unit
1630 -- p_customer_trx_line_id AR line identifier
1631 --
1632 -- Return: NUMBER
1633 --
1634 -- DESIGN REFERENCES:
1635 -- GTA_Reports_TD.doc
1636 --
1637 -- CHANGE HISTORY:
1638 --
1639 -- 13-Jun-2005: Donghai Wang Creation
1640 -- 24-Nov-2005: Modify program logic to get line amount per Golden
1641 -- Tax currency from the table zx_lines
1642 --
1643 --=========================================================================
1644 FUNCTION Get_Arline_Amount
1645 (p_org_id IN NUMBER
1646 ,p_customer_trx_line_id IN NUMBER
1647 )
1648 RETURN NUMBER
1649 IS
1650 l_tax_type_code zx_lines.tax_type_code%TYPE;
1651 l_arline_amount NUMBER;
1652 l_gt_currency_code fnd_currencies.currency_code%TYPE;
1653 l_trx_id ra_customer_trx_all.customer_trx_id%TYPE;
1654
1655 CURSOR c_tax_type_code
1656 IS
1657 SELECT
1658 vat_tax_type_code
1659 ,gt_currency_code
1660 FROM
1661 jmf_gta_system_parameters_all
1662 WHERE org_id=p_org_id;
1663
1664 --CURSOR c_ar_line_taxable_amount --Donghai Wang bug5212702 May-17,2006
1665 CURSOR c_ar_line_taxable_amount(pc_trx_id NUMBER)--Donghai Wang bug5212702 May-17,2006
1666 IS
1667 SELECT
1668 taxable_amt_tax_curr
1669 FROM
1670 zx_lines
1671 WHERE trx_line_id=p_customer_trx_line_id
1672 AND entity_code='TRANSACTIONS'
1673 AND application_id = 222
1674 AND trx_level_type='LINE'
1675 AND tax_type_code=l_tax_type_code
1676 AND tax_currency_code=l_gt_currency_code
1677 AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--Donghai Wang bug5212702 May-17,2006
1678 AND trx_id=pc_trx_id
1679 ORDER BY tax_line_id;
1680
1681
1682
1683 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1684 l_proc_level NUMBER := fnd_log.level_procedure;
1685 l_procedure_name VARCHAR2(30) := 'Get_Arline_Amount';
1686
1687 BEGIN
1688 --logging for debug
1689 IF (l_proc_level >= l_dbg_level)
1690 THEN
1691 fnd_log.STRING(l_proc_level
1692 ,g_module_prefix || l_procedure_name || '.begin'
1693 ,'Enter function');
1694 END IF; --l_proc_level>=l_dbg_level)
1695
1696
1697 --Get Vat tax type defined in GTA system options form for current
1698 --operating unit
1699 OPEN c_tax_type_code;
1700 FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
1701 CLOSE c_tax_type_code;
1702
1703 --Get taxable amount per Golden Tax Currency for one AR line
1704 --Donghai Wang bug5212702 May-17,2006
1705 --OPEN c_ar_line_taxable_amount;
1706
1707 SELECT customer_trx_id
1708 INTO l_trx_id
1709 FROM ra_customer_trx_lines_all
1710 WHERE customer_trx_line_id=p_customer_trx_line_id;
1711
1712 OPEN c_ar_line_taxable_amount(l_trx_id);
1713 --Donghai Wang bug5212702 May-17,2006
1714
1715 FETCH c_ar_line_taxable_amount INTO l_arline_amount;
1716 CLOSE c_ar_line_taxable_amount;
1717
1718 --logging for debug
1719 IF (l_proc_level >= l_dbg_level)
1720 THEN
1721 fnd_log.STRING(l_proc_level
1722 ,g_module_prefix || l_procedure_name || '.End'
1723 ,'Exit function');
1724 END IF; --l_proc_level>=l_dbg_level)
1725
1726 RETURN(l_arline_amount);
1727
1728 END Get_Arline_Amount;
1729
1730 --==========================================================================
1731 -- FUNCTION NAME:
1732 --
1733 -- Get_Arline_Vattax_Amount Public
1734 --
1735 -- DESCRIPTION:
1736 --
1737 -- This function is used to get VAT amount based on one AR line
1738 -- per Golden Tax currency
1739 --
1740 -- PARAMETERS:
1741 -- In: p_org_id Identifier of operating unit
1742 -- p_customer_trx_line_id AR line identifier
1743 --
1744 -- Return: NUMBER
1745 --
1746 -- DESIGN REFERENCES:
1747 -- GTA_Reports_TD.doc
1748 --
1749 -- CHANGE HISTORY:
1750 --
1751 -- 13-Jun-2005: Donghai Wang Creation
1752 -- 24-Nov-2005: Donghai Wang Add a new parameter 'p_org_id' and
1753 -- replace dummy code to real code
1754 --
1755 --=========================================================================
1756 FUNCTION Get_Arline_Vattax_Amount
1757 (p_org_id IN NUMBER
1758 ,p_customer_trx_line_id IN NUMBER
1759 )
1760 RETURN NUMBER
1761 IS
1762 l_tax_type_code zx_lines.tax_type_code%TYPE;
1763 l_arline_vatamount NUMBER;
1764 l_gt_currency_code fnd_currencies.currency_code%TYPE;
1765 l_trx_id ra_customer_trx_all.customer_trx_id%TYPE;--Donghai Wang bug5212702 May-17,2006
1766
1767 CURSOR c_tax_type_code
1768 IS
1769 SELECT
1770 vat_tax_type_code
1771 ,gt_currency_code
1772 FROM
1773 jmf_gta_system_parameters_all
1774 WHERE org_id=p_org_id;
1775
1776 --CURSOR c_ar_line_vatamount--Donghai Wang bug5212702 May-17,2006
1777 CURSOR c_ar_line_vatamount(pc_trx_id NUMBER)--Donghai Wang bug5212702 May-17,2006
1778 IS
1779 SELECT
1780 tax_amt_tax_curr
1781 FROM
1782 zx_lines
1783 WHERE trx_line_id=p_customer_trx_line_id
1784 AND entity_code='TRANSACTIONS'
1785 AND application_id = 222
1786 AND trx_level_type='LINE'
1787 AND tax_type_code=l_tax_type_code
1788 AND tax_currency_code=l_gt_currency_code
1789 AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--Donghai Wang bug5212702 May-17,2006
1790 AND trx_id=pc_trx_id
1791 ORDER BY tax_line_id;
1792
1793
1794
1795 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1796 l_proc_level NUMBER := fnd_log.level_procedure;
1797 l_procedure_name VARCHAR2(30) := 'Get_Arline_Vattax_Amount';
1798
1799 BEGIN
1800 --logging for debug
1801 IF (l_proc_level >= l_dbg_level)
1802 THEN
1803 fnd_log.STRING(l_proc_level
1804 ,g_module_prefix || l_procedure_name || '.begin'
1805 ,'Enter function');
1806 END IF; --l_proc_level>=l_dbg_level)
1807
1808
1809 --Get Vat tax type defined in GTA system options form for current
1810 --operating unit
1811 OPEN c_tax_type_code;
1812 FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
1813 CLOSE c_tax_type_code;
1814
1815 --Get tax amount per Golden Tax Currency for one AR line
1816
1817 --Donghai Wang bug5212702 May-17,2006
1818
1819 SELECT customer_trx_id
1820 INTO l_trx_id
1821 FROM ra_customer_trx_lines_all
1822 WHERE customer_trx_line_id=p_customer_trx_line_id;
1823 --OPEN c_ar_line_vatamount;
1824 OPEN c_ar_line_vatamount(l_trx_id);
1825
1826 --Donghai Wang bug5212702 May-17,2006
1827
1828 FETCH c_ar_line_vatamount INTO l_arline_vatamount;
1829 CLOSE c_ar_line_vatamount;
1830
1831 --logging for debug
1832 IF (l_proc_level >= l_dbg_level)
1833 THEN
1834 fnd_log.STRING(l_proc_level
1835 ,g_module_prefix || l_procedure_name || '.End'
1836 ,'Exit function');
1837 END IF; --l_proc_level>=l_dbg_level)
1838
1839 RETURN(l_arline_vatamount);
1840 END Get_Arline_Vattax_Amount;
1841
1842 --==========================================================================
1843 -- FUNCTION NAME:
1844 --
1845 -- Get_Arline_Vattax_Rate Public
1846 --
1847 -- DESCRIPTION:
1848 --
1849 -- This function is used to get VAT rate for one AR line
1850 --
1851 -- PARAMETERS:
1852 -- In: p_org_id Identifier of Operating Unit
1853 -- p_customer_trx_line_id AR line identifier
1854 --
1855 -- Return: NUMBER
1856 --
1857 -- DESIGN REFERENCES:
1858 -- GTA_Reports_TD.doc
1859 --
1860 -- CHANGE HISTORY:
1861 --
1862 -- 13-Jun-2005: Donghai Wang Creation
1863 -- 24-Nov-2005: Donghai Wang Add a new parameter 'p_org_id' and
1864 -- replace dummy code to real code
1865 --
1866 --=========================================================================
1867 FUNCTION Get_Arline_Vattax_Rate
1868 (p_org_id IN NUMBER
1869 ,p_customer_trx_line_id IN NUMBER
1870 )
1871 RETURN NUMBER
1872 IS
1873 l_tax_type_code zx_lines.tax_type_code%TYPE;
1874 l_tax_rate NUMBER;
1875 l_gt_currency_code fnd_currencies.currency_code%TYPE;
1876 l_trx_id ra_customer_trx_all.customer_trx_id%TYPE;--Donghai Wang bug5212702 May-17,2006
1877
1878 CURSOR c_tax_type_code
1879 IS
1880 SELECT
1881 vat_tax_type_code
1882 ,gt_currency_code
1883 FROM
1884 jmf_gta_system_parameters_all
1885 WHERE org_id=p_org_id;
1886
1887 --CURSOR c_ar_line_tax_rate --Donghai Wang bug5212702 May-17,2006
1888 CURSOR c_ar_line_tax_rate(pc_trx_id NUMBER) --Donghai Wang bug5212702 May-17,2006
1889 IS
1890 SELECT
1891 tax_rate
1892 FROM
1893 zx_lines
1894 WHERE trx_line_id=p_customer_trx_line_id
1895 AND entity_code='TRANSACTIONS'
1896 AND application_id = 222
1897 AND trx_level_type='LINE'
1898 AND tax_type_code=l_tax_type_code
1899 AND tax_currency_code=l_gt_currency_code
1900 AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--Donghai Wang bug5212702 May-17,2006
1901 AND trx_id=pc_trx_id --Donghai Wang bug5212702 May-17,2006
1902 ORDER BY tax_line_id;
1903
1904
1905
1906 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1907 l_proc_level NUMBER := fnd_log.level_procedure;
1908 l_procedure_name VARCHAR2(30) := 'Get_Arline_Vattax_Rate';
1909
1910 BEGIN
1911 --logging for debug
1912 IF (l_proc_level >= l_dbg_level)
1913 THEN
1914 fnd_log.STRING(l_proc_level
1915 ,g_module_prefix || l_procedure_name || '.begin'
1916 ,'Enter function');
1917 END IF; --l_proc_level>=l_dbg_level)
1918
1919
1920 --Get Vat tax type defined in GTA system options form for current
1921 --operating unit
1922 OPEN c_tax_type_code;
1923 FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
1924 CLOSE c_tax_type_code;
1925
1926 --Get tax rate for one AR line
1927 --Donghai Wang bug5212702 May-17,2006
1928 SELECT customer_trx_id
1929 INTO l_trx_id
1930 FROM ra_customer_trx_lines_all
1931 WHERE customer_trx_line_id=p_customer_trx_line_id;
1932
1933 --OPEN c_ar_line_tax_rate;
1934 OPEN c_ar_line_tax_rate(l_trx_id);
1935 --Donghai Wang bug5212702 May-17,2006
1936
1937 FETCH c_ar_line_tax_rate INTO l_tax_rate;
1938 CLOSE c_ar_line_tax_rate;
1939
1940 --logging for debug
1941 IF (l_proc_level >= l_dbg_level)
1942 THEN
1943 fnd_log.STRING(l_proc_level
1944 ,g_module_prefix || l_procedure_name || '.End'
1945 ,'Exit function');
1946 END IF; --l_proc_level>=l_dbg_level)
1947
1948 RETURN(l_tax_rate/100);
1949 END Get_Arline_Vattax_Rate;
1950
1951 --==========================================================================
1952 -- Procedure NAME:
1953 --
1954 -- get_bank_info Public
1955 --
1956 -- DESCRIPTION:
1957 --
1958 -- This function get bank infomations by cust_Trx_id, if the bank info from AR
1959 -- is null. then get bank infomations by customer_id
1960 --
1961 -- PARAMETERS:
1962 -- In:
1963 -- p_customer_trx_id IN NUMBER
1964 -- p_trxn_extension_id IN NUMBER
1965 -- OUT:
1966 -- x_bank_name OUT NOCOPY VARCHAR2
1967 -- x_bank_branch_name OUT NOCOPY VARCHAR2
1968 -- x_bank_account_name OUT NOCOPY VARCHAR2
1969 -- x_bank_account_num OUT NOCOPY VARCHAR2
1970 --
1971 --
1972 -- DESIGN REFERENCES:
1973 --
1974 --
1975 -- CHANGE HISTORY:
1976 --
1977 -- 17-AUG-2005: JIM.Zheng Created
1978 --
1979 --===========================================================================
1980 PROCEDURE Get_Bank_Info
1981 ( p_customer_trx_id IN NUMBER
1982 , p_org_id IN NUMBER
1983 , x_bank_name OUT NOCOPY VARCHAR2
1984 , x_bank_branch_name OUT NOCOPY VARCHAR2
1985 , x_bank_account_name OUT NOCOPY VARCHAR2
1986 , x_bank_account_num OUT NOCOPY VARCHAR2
1987 )
1988 IS
1989 l_procedure_name VARCHAR2(30) := 'Get_Bank_Info';
1990
1991 l_bill_to_customer_id ra_customer_trx_all.bill_to_customer_id%TYPE;
1992
1993 l_site_use_id hz_cust_site_uses.SITE_USE_ID%TYPE;
1994 l_cust_acct_site_id hz_cust_acct_sites.CUST_ACCT_SITE_ID%TYPE;
1995 l_currency_code jmf_gta_system_parameters_all.gt_currency_code%TYPE;
1996 l_error_string VARCHAR2(500);
1997
1998 l_paying_customer_id ra_customer_trx_all.paying_customer_id%TYPE;
1999 l_paying_site_use_id ra_customer_trx_all.paying_site_use_id%TYPE;
2000 l_paying_site_id hz_cust_acct_sites.CUST_ACCT_SITE_ID%TYPE;
2001 l_paying_party_id HZ_CUST_ACCOUNTS.party_id%TYPE;
2002 l_ext_payer_id IBY_EXTERNAL_PAYERS_ALL.ext_payer_id%TYPE;
2003 l_bank_account_name IBY_EXT_BANK_ACCOUNTS.bank_account_name%TYPE;
2004 l_bank_account_num IBY_EXT_BANK_ACCOUNTS.bank_account_num%TYPE;
2005 l_bank_id IBY_EXT_BANK_ACCOUNTS.bank_id%TYPE;
2006 l_bank_branch_id IBY_EXT_BANK_ACCOUNTS.branch_id%TYPE;
2007 l_bank_name HZ_PARTIES.party_name%TYPE;
2008 l_bank_branch_name HZ_PARTIES.party_name%TYPE;
2009 l_trxn_extension_id ra_customer_trx_all.payment_trxn_extension_id%TYPE;
2010
2011 l_instrument_id IBY_EXT_BANK_ACCOUNTS.ext_bank_account_id%TYPE;
2012
2013
2014
2015
2016 BEGIN
2017 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2018 THEN
2019 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2020 , G_MODULE_PREFIX || l_procedure_name
2021 ,'begin Procedure. ');
2022 END IF;
2023
2024 BEGIN
2025 SELECT
2026 gt_currency_code
2027 INTO
2028 l_currency_code
2029 FROM
2030 jmf_gta_system_parameters_all
2031 WHERE org_id=p_org_id;
2032
2033 EXCEPTION
2034 WHEN no_data_found THEN
2035 --report JMF_GTA_MISSING_ERROR
2036 fnd_message.set_name('JMF', 'JMF_GTA_MISSING_ERROR');
2037 l_error_string := fnd_message.get();
2038 -- output this error
2039 fnd_file.put_line(fnd_file.output, '<?xml version="1.0" encoding="UTF-8" ?>
2040 <TransferReport>
2041 <ReportFailed>Y</ReportFailed>
2042 <ReportFailedMsg>'||l_error_string||'</ReportFailedMsg>
2043 <TransferReport>');
2044
2045
2046 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2047 THEN
2048 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
2049 , G_MODULE_PREFIX || l_procedure_name
2050 , l_error_string);
2051 END IF;
2052 RAISE;
2053 END;
2054
2055 BEGIN
2056 SELECT
2057 h.paying_customer_id
2058 ,h.paying_site_use_id
2059 ,h.payment_trxn_extension_id
2060 INTO
2061 l_paying_customer_id
2062 , l_paying_site_use_id
2063 , l_trxn_extension_id
2064 FROM
2065 ra_customer_trx_all h
2066
2067 WHERE h.customer_trx_id = p_customer_trx_id ;
2068 EXCEPTION
2069 WHEN no_data_found THEN
2070 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2071 THEN
2072 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2073 , G_MODULE_PREFIX || l_procedure_name
2074 , 'no date found when select header info');
2075 END IF;
2076 END;
2077
2078 -- select bank information
2079 IF l_trxn_extension_id IS NOT NULL
2080 THEN
2081
2082 BEGIN
2083 SELECT
2084 u.instrument_id
2085 , b.bank_account_name
2086 , b.bank_name
2087 , b.bank_branch_name
2088 INTO
2089 l_instrument_id
2090 , l_bank_account_name
2091 , l_bank_name
2092 , l_bank_branch_name
2093 FROM IBY_CREDITCARD C,
2094 IBY_CREDITCARD_ISSUERS_VL I,
2095 IBY_EXT_BANK_ACCOUNTS_V B,
2096 IBY_FNDCPT_PMT_CHNNLS_VL P,
2097 IBY_FNDCPT_TX_EXTENSIONS X,
2098 IBY_FNDCPT_TX_OPERATIONS OP,
2099 IBY_PMT_INSTR_USES_ALL U,
2100 HZ_PARTIES HZP,
2101 FND_APPLICATION A
2102 WHERE (x.instr_assignment_id = u.instrument_payment_use_id(+))
2103 AND (DECODE(u.instrument_type, 'CREDITCARD', u.instrument_id, NULL) =
2104 c.instrid(+))
2105 AND (DECODE(u.instrument_type, 'BANKACCOUNT', u.instrument_id, NULL) =
2106 b.bank_account_id(+))
2107 AND (x.payment_channel_code = p.payment_channel_code)
2108 AND (c.card_issuer_code = i.card_issuer_code(+))
2109 AND (x.trxn_extension_id = op.trxn_extension_id(+))
2110 AND (c.card_owner_id = hzp.party_id(+))
2111 AND (x.origin_application_id = a.application_id)
2112 AND x.trxn_extension_id = l_trxn_extension_id;
2113
2114 EXCEPTION
2115 WHEN no_data_found THEN
2116 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2117 THEN
2118 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2119 , G_MODULE_PREFIX || l_procedure_name
2120 , 'no date found when select bank information');
2121 END IF;
2122 END;
2123
2124 BEGIN
2125 SELECT
2126 bank_account_num
2127 INTO
2128 l_bank_account_num
2129 FROM
2130 IBY_EXT_BANK_ACCOUNTS
2131 WHERE
2132 ext_bank_account_id = l_instrument_id;
2133 EXCEPTION
2134 WHEN no_data_found THEN
2135 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2136 THEN
2137 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2138 , G_MODULE_PREFIX || l_procedure_name
2139 , 'no date found when select bank information');
2140 END IF;
2141 END;
2142
2143
2144 END IF;/*l_trxn_extension_id IS NOT NULL*/
2145
2146 -- if the bank information come from AR is null. then select bank info by customer!
2147 IF l_bank_account_num IS NULL
2148 THEN
2149 -- get bank info by paying customer id and paying site use id.
2150 BEGIN
2151
2152 -- get party id of paying customer
2153 SELECT
2154 party_id
2155 INTO
2156 l_paying_party_id
2157 FROM
2158 HZ_CUST_ACCOUNTS
2159 WHERE
2160 CUST_ACCOUNT_ID = l_paying_customer_id ;
2161
2162 -- get ext_payer_id by party id , site account id , site use id and org id.
2163 SELECT
2164 ext_payer_id
2165 INTO
2166 l_ext_payer_id
2167 FROM
2168 IBY_EXTERNAL_PAYERS_ALL
2169 WHERE party_id = l_paying_party_id
2170 AND CUST_ACCOUNT_ID = l_paying_customer_id
2171 AND ACCT_SITE_USE_ID = l_paying_site_use_id
2172 AND ORG_ID = p_org_id -- org id
2173 AND org_type = 'OPERATING_UNIT' -- ou
2174 AND payment_function = 'CUSTOMER_PAYMENT';
2175
2176 -- get bank account name and bank account num
2177 SELECT
2178 bank_account_name
2179 , bank_account_num
2180 , bank_id
2181 , branch_id
2182 INTO
2183 l_bank_account_name
2184 , l_bank_account_num
2185 , l_bank_id
2186 , l_bank_branch_id
2187 FROM (SELECT ibybanks.bank_account_name
2188 , ibybanks.bank_account_num
2189 , ibybanks.bank_id
2190 , ibybanks.branch_id
2191 FROM IBY_PMT_INSTR_USES_ALL ExtPartyInstrumentsEO
2192 , IBY_EXT_BANK_ACCOUNTS ibybanks
2193 WHERE ibybanks.EXT_BANK_ACCOUNT_ID = ExtPartyInstrumentsEO.instrument_id
2194 AND ExtPartyInstrumentsEO.INSTRUMENT_TYPE = 'BANKACCOUNT'
2195 AND ExtPartyInstrumentsEO.EXT_PMT_PARTY_ID = l_ext_payer_id
2196 AND ExtPartyInstrumentsEO.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
2197 AND ibybanks.currency_code = l_currency_code
2198 AND SYSDATE BETWEEN nvl(ExtPartyInstrumentsEO.START_DATE, to_date('1900-01-01','RRRR-MM-DD'))
2199 AND nvl(ExtPartyInstrumentsEO.END_DATE, to_date('3000-01-01','RRRR-MM-DD'))
2200 ORDER BY ExtPartyInstrumentsEO.ORDER_OF_PREFERENCE)
2201 WHERE ROWNUM =1;
2202
2203
2204 -- get bank name
2205 SELECT
2206 party_name
2207 INTO
2208 l_bank_name
2209 FROM
2210 HZ_PARTIES
2211 WHERE
2212 party_id = l_bank_id;
2213
2214 -- get bank branch name
2215 SELECT
2216 party_name
2217 INTO
2218 l_bank_branch_name
2219 FROM
2220 HZ_PARTIES
2221 WHERE party_id = l_bank_branch_id;
2222
2223
2224 EXCEPTION
2225 WHEN no_data_found THEN
2226 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2227 THEN
2228 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2229 , G_MODULE_PREFIX || l_procedure_name
2230 , 'no date found when select bank information');
2231 END IF;
2232 END;/*l_apba_bank_account_num IS NULL*/
2233
2234 END IF;
2235
2236 x_bank_name := l_bank_name;
2237 x_bank_branch_name := l_bank_branch_name;
2238 x_bank_account_num := l_bank_account_num;
2239 x_bank_account_name := l_bank_account_name;
2240
2241 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2242 THEN
2243 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2244 , G_MODULE_PREFIX || l_procedure_name
2245 ,'End Procedure. ');
2246 END IF;
2247
2248 EXCEPTION
2249 WHEN OTHERS THEN
2250 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2251 THEN
2252 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
2253 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
2254 , Sqlcode||Sqlerrm);
2255 END IF;
2256 RAISE;
2257 END Get_Bank_Info;
2258
2259 --==========================================================================
2260 -- Procedure NAME:
2261 --
2262 -- verify_tax_line Public
2263 --
2264 -- DESCRIPTION:
2265 --
2266 -- Verify the tax lines number of a trx line, is it is not 1 , return fail
2267 --
2268 -- PARAMETERS:
2269
2270 -- p_trx_line_id IN NUMBER
2271 -- p_tax_type_code IN VARCHAR2
2272 -- x_status OUT NOCOPY NUMBER
2273 -- x_tax_line_id OUT NOCOPY zx_lines.tax_line_id%TYPE
2274 --
2275 -- DESIGN REFERENCES:
2276 --
2277 --
2278 -- CHANGE HISTORY:
2279 --
2280 -- 13-Oct-2005: JIM.Zheng Created
2281 --
2282 --===========================================================================
2283 PROCEDURE Verify_Tax_Line
2284 (p_trx_line_id IN NUMBER
2285 , p_tax_type_code IN VARCHAR2
2286 , p_currency_code IN VARCHAR2
2287 , x_status OUT NOCOPY NUMBER
2288 , x_tax_line_id OUT NOCOPY zx_lines.tax_line_id%TYPE
2289 )
2290 IS
2291 l_tax_line_count NUMBER;
2292 l_procedure_name VARCHAR2(50) := 'verify_tax_line';
2293 l_tax_line_id zx_lines.tax_line_id%TYPE;
2294 l_trx_id ra_customer_trx_all.customer_trx_id%TYPE;--jogen bug5212702 May-17,2006
2295
2296 BEGIN
2297
2298 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2299 THEN
2300 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2301 , G_MODULE_PREFIX || l_procedure_name
2302 ,'begin Procedure. ');
2303 END IF;
2304
2305 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2306 THEN
2307 log( 'Begin Verify_Tax_line---');
2308 log( 'p_trx_line_id :'||p_trx_line_id);
2309 log( 'p_tax_type_code :'||p_tax_type_code);
2310 log( 'p_currency_code :'||p_currency_code);
2311 END IF;
2312
2313 -- init status
2314 x_status := 0 ;
2315
2316 -- get the tax lines count of Ar line which the tax type is VAT
2317 SELECT customer_trx_id
2318 INTO l_trx_id
2319 FROM ra_customer_trx_lines_all
2320 WHERE customer_trx_line_id=p_trx_line_id;
2321
2322 SELECT
2323 COUNT(*)
2324 INTO
2325 l_tax_line_count
2326 FROM
2327 zx_lines tax
2328 WHERE tax.trx_line_id = p_trx_line_id
2329 AND tax.entity_code = 'TRANSACTIONS'
2330 AND application_id = 222
2331 AND tax.trx_level_type = 'LINE'
2332 AND tax.tax_currency_code = p_currency_code
2333 AND tax.tax_type_code = p_tax_type_code
2334 AND tax.event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--jogen bug5212702 May-17,2006
2335 AND tax.trx_id=l_trx_id; --jogen bug5212702 May-17,2006
2336
2337
2338 -- if the line number is 0, then x_status = -1
2339 -- if the line number is 1, then x_status = 0
2340 -- if the line number > 1 , then x_status = 1
2341 IF l_tax_line_count = 0
2342 THEN
2343 x_status := -1;
2344 ELSIF l_tax_line_count = 1
2345 THEN
2346 x_status := 0;
2347 BEGIN
2348 SELECT
2349 tax.tax_line_id
2350 INTO
2351 l_tax_line_id
2352 FROM
2353 zx_lines tax
2354 WHERE tax.trx_line_id = p_trx_line_id
2355 AND tax.application_id = 222
2356 AND tax.trx_level_type = 'LINE'
2357 AND tax.entity_code = 'TRANSACTIONS'
2358 AND tax.tax_type_code = p_tax_type_code
2359 AND tax.event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--jogen bug5212702 May-17,2006
2360 AND tax.trx_id=l_trx_id; --jogen bug5212702 May-17,2006
2361 END;
2362 ELSE
2363 x_status := 1;
2364
2365 END IF;/*l_tax_line_count = 0*/
2366
2367 x_tax_line_id := l_tax_line_id;
2368
2369 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2370 THEN
2371 log( 'x_status : '||x_status);
2372 END IF;
2373
2374 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2375 THEN
2376 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2377 , G_MODULE_PREFIX || l_procedure_name
2378 ,'End Procedure. ');
2379 END IF;
2380
2381 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2382 THEN
2383 log( 'End Verify_Tax_line---');
2384 END IF;
2385
2386 EXCEPTION
2387 WHEN OTHERS THEN
2388 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2389 THEN
2390 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
2391 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
2392 , Sqlcode||Sqlerrm);
2393 END IF;
2394 RAISE;
2395
2396
2397 END Verify_Tax_Line;
2398 --==========================================================================
2399 -- Procedure NAME:
2400 --
2401 -- get_info_from_ebtax Public
2402 --
2403 -- DESCRIPTION:
2404 --
2405 -- This function get data from ebtax
2406 --
2407 -- PARAMETERS:
2408 -- p_org_id IN NUMBER
2409 -- p_trx_id IN NUMBER
2410 -- p_trx_line_id IN NUMBER
2411 -- p_tax_type_code IN VARCHAR2
2412 -- x_tax_amount OUT NOCOPY NUMBER
2413 -- x_taxable_amount OUT NOCOPY NUMBER
2414 -- x_trx_line_quantity OUT NOCOPY NUMBER
2415 -- x_tax_rate OUT NOCOPY NUMBER
2416 -- x_unit_selling_price OUT NOCOPY NUMBER
2417 -- x_taxable_amount OUT NOCOPY NUMBER
2418 -- x_fp_registration_number OUT NOCOPY VARCHAR2
2419 -- x_tp_registration_number OUT NOCOPY VARCHAR2
2420 -- x_status OUT NOCOPY NUMBER
2421 -- x_invoice_type OUT NOCOPY VARCHAR2
2422 -- x_error_buffer OUT NOCOPY VARCHAR2
2423 --
2424 --
2425 -- DESIGN REFERENCES:
2426 --
2427 --
2428 -- CHANGE HISTORY:
2429 --
2430 -- 17-SEP-2005: JIM.Zheng Created
2431 -- 28-DEC-2007: Subba Changed for R12.1
2432 --===========================================================================
2433 PROCEDURE Get_Info_From_Ebtax
2434 (p_org_id IN NUMBER
2435 ,p_trx_id IN NUMBER
2436 ,p_trx_line_id IN NUMBER
2437 ,p_tax_type_code IN VARCHAR2
2438 ,x_tax_amount OUT NOCOPY NUMBER
2439 ,x_taxable_amount OUT NOCOPY NUMBER
2440 ,x_trx_line_quantity OUT NOCOPY NUMBER
2441 ,x_tax_rate OUT NOCOPY NUMBER
2442 ,x_unit_selling_price OUT NOCOPY NUMBER
2443 ,x_tax_curr_unit_price OUT NOCOPY NUMBER
2444 ,x_taxable_amount_org OUT NOCOPY NUMBER
2445 ,x_fp_registration_number OUT NOCOPY VARCHAR2
2446 ,x_tp_registration_number OUT NOCOPY VARCHAR2
2447 ,x_status OUT NOCOPY NUMBER
2448 ,x_invoice_type OUT NOCOPY VARCHAR2
2449 ,x_error_buffer OUT NOCOPY VARCHAR2
2450 )
2451 IS
2452 l_procedure_name VARCHAR2(30) := 'get_info_from_ebtax';
2453 l_lines_status NUMBER;
2454 l_status NUMBER;
2455 l_error_buffer VARCHAR2(180);
2456 l_tax_registration_number zx_lines.tax_registration_number%TYPE;
2457 l_tax_registration_count NUMBER;
2458 l_tax_line_id zx_lines.tax_line_id%TYPE;
2459 l_tax_rate zx_lines.tax_rate%TYPE;
2460 l_unit_price zx_lines.unit_price%TYPE;
2461 l_trx_line_quantity zx_lines.trx_line_quantity%TYPE;
2462 l_tax_amount zx_lines.tax_amt_funcl_curr%TYPE;
2463 l_taxable_amount zx_lines.taxable_amt_funcl_curr%TYPE;
2464 l_tax_curr_conversion_rate zx_lines.tax_currency_conversion_rate%TYPE;
2465 l_tp_registration_number zx_registrations.registration_number%TYPE;
2466 l_fp_reg_number_count NUMBER;
2467 l_amount zx_lines.taxable_amt%TYPE;
2468 l_currency_code VARCHAR2(30);
2469 l_error_string VARCHAR2(500);
2470 --added by subba for R12.1
2471 l_invoice_type jmf_gta_tax_limits_all.invoice_type%type;
2472
2473 tax_error_for_recycle EXCEPTION; --exception for tax_amount check for recycle Invoice
2474
2475 BEGIN
2476 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2477 THEN
2478 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2479 , G_MODULE_PREFIX || l_procedure_name
2480 ,'begin Procedure. ');
2481 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2482 , G_MODULE_PREFIX || l_procedure_name
2483 ,'p_trx_line_id: '||p_trx_line_id);
2484
2485 END IF;
2486
2487 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2488 THEN
2489 log( 'Begin Get_Info_From_Ebtax --');
2490 log( 'p_org_id : '||p_org_id);
2491 log( 'p_trx_id : '||p_trx_id);
2492 log( 'p_trx_line_id : '||p_trx_line_id);
2493 log( 'p_tax_type_code : '||p_tax_type_code);
2494 log( 'x_status : '||x_status);
2495 log( 'x_status : '||x_status);
2496 log( 'x_status : '||x_status);
2497 END IF;
2498
2499 -- init status
2500 x_status := 0 ;
2501
2502 BEGIN
2503 SELECT
2504 gt_currency_code
2505 INTO
2506 l_currency_code
2507 FROM
2508 jmf_gta_system_parameters_all
2509 WHERE org_id=p_org_id;
2510
2511 EXCEPTION
2512 WHEN no_data_found THEN
2513 --report JMF_GTA_MISSING_ERROR
2514 fnd_message.set_name('JMF', 'JMF_GTA_SYS_CONFIG_MISSING');
2515 l_error_string := fnd_message.get();
2516 -- output this error
2517 fnd_file.put_line(fnd_file.output,'<?xml version="1.0" encoding="UTF-8"?>
2518 <TransferReport>
2519 <ReportFailed>Y</ReportFailed>
2520 <ReportFailedMsg>'||l_error_string||'</ReportFailedMsg>
2521 <TransferReport>');
2522
2523
2524 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2525 THEN
2526 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
2527 , G_MODULE_PREFIX || l_procedure_name
2528 , l_error_string);
2529 END IF;
2530
2531 RAISE;
2532 END;
2533
2534
2535 -- verify tax Line number.
2536 verify_tax_line
2537 (p_trx_line_id => p_trx_line_id
2538 , p_tax_type_code => p_tax_type_code
2539 , p_currency_code => l_currency_code
2540 , x_status => l_lines_status
2541 , x_tax_line_id => l_tax_line_id
2542 );
2543
2544
2545 -- if the line count is 0, return -1 and the line can't be transfer and don't
2546 -- throw any exception
2547 -- if the line count > 1 , return 1 and throw exception
2548 -- if the line count = 1 , get data from zx_lines and transfer it to GTA
2549 -- 29-JUN-2006 Upated by Shujuan, insert Tax_currency_conversion_rate
2550 -- into l_tax_curr_conversion_rate in order to calculate the unit price of
2551 -- tax concurrency for bug 5168900
2552 IF l_lines_status = 0
2553 THEN
2554 SELECT
2555 tax.tax_line_id
2556 , tax.hq_estb_reg_number
2557 , tax.taxable_amt_tax_curr
2558 , tax.tax_rate
2559 , tax.tax_amt_tax_curr
2560 , tax.unit_price
2561 , tax.trx_line_quantity
2562 , tax.taxable_amt
2563 , tax.Tax_currency_conversion_rate
2564 INTO
2565 l_tax_line_id
2566 , l_tax_registration_number
2567 , l_taxable_amount
2568 , l_tax_rate
2569 , l_tax_amount
2570 , l_unit_price
2571 , l_trx_line_quantity
2572 , l_amount
2573 , l_tax_curr_conversion_rate
2574 FROM
2575 zx_lines tax
2576 WHERE tax.trx_line_id = p_trx_line_id
2577 AND tax.entity_code = 'TRANSACTIONS'
2578 AND application_id = 222
2579 AND tax.trx_level_type = 'LINE'
2580 AND tax.tax_currency_code = l_currency_code
2581 AND tax.tax_type_code = p_tax_type_code
2582 --jogen bug5212702 May-17,2006
2583 AND tax.event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')
2584 AND tax.trx_id=p_trx_id; --jogen bug5212702 May-17,2006
2585
2586
2587 -- find the registration number from system option
2588 -- if the number is exist then go ahead
2589 -- if the number is not exist , then throw a exception
2590 IF l_tax_registration_number IS NULL
2591 THEN
2592 -- throw first party registion number is null exception
2593 x_status := 1;
2594 l_error_buffer := 'JMF_GTA_FP_TAXREG_MISSING';
2595 ELSE /*l_tax_registration_number IS NULL*/
2596 -- find the first party registion number in parameter
2597 SELECT
2598 COUNT(*)
2599 INTO
2600 l_fp_reg_number_count
2601 FROM
2602 jmf_gta_tax_limits_all
2603 WHERE org_id = p_org_id
2604 AND fp_tax_registration_number = l_tax_registration_number;
2605
2606 IF l_fp_reg_number_count = 0
2607 THEN
2608 x_status := 2;
2609 l_error_buffer := 'JMF_GTA_SYS_CONFIG_MISSING';
2610 ELSE
2611 --if there no exception when get first party registration number then
2612 --get third party registration number
2613
2614 get_tp_tax_registration_number
2615 ( p_trx_id => p_trx_id
2616 , p_tax_line_id => l_tax_line_id
2617 , x_tp_tax_registration_number => l_tp_registration_number
2618 );
2619 END IF;/*l_fp_reg_number_count = 0*/
2620
2621 --following code added by subba for R12.1
2622
2623 --IF l_tax_registration_number IS NOT NULL THEN
2624
2625 l_invoice_type := get_invoice_type( p_org_id => p_org_id
2626 ,p_customer_trx_id => p_trx_id
2627 ,p_fp_tax_registration_num => l_tax_registration_number );
2628 --END IF;
2629
2630 -- throw a missing tp registration number exception when invoice type is not C
2631
2632
2633 -- 2 stands for Common Invoice, 1 for Recycle Invoice, 0 for Special Invoice.
2634
2635 -- to keep consistent with the flat file format of Asino.
2636
2637 IF l_invoice_type IS NULL THEN
2638 x_status := 1;
2639 l_error_buffer := 'JMF_GTA_MISSING_INVOICE_TYPE';
2640
2641
2642 ELSE /*IF l_invoice_type IS NULL*/
2643
2644 IF l_invoice_type <> '2' THEN --if not common VAT Invoice
2645
2646 IF l_tp_registration_number IS NULL THEN
2647 -- throw third party registion number is null exception
2648 x_status := 1;
2649 l_error_buffer := 'JMF_GTA_TP_TAXREG_MISSING';
2650 ELSE /*l_tp_registration_number IS NULL*/
2651 x_tp_registration_number := l_tp_registration_number;
2652 END IF;/*l_tp_registration_number IS NULL*/
2653 END IF; /* l_invoice_type <>'2'*/
2654
2655 --END IF;
2656
2657 -- validate tax rate and tax amount are zero when invoice type is R, added by Subba for R12.1
2658
2659 IF l_invoice_type = '1' -- 1 stands for Recycle Invoice
2660 THEN
2661 IF (l_tax_rate <> 0 OR l_tax_amount <> 0) THEN
2662 x_status := 1;
2663 l_error_buffer := 'JMF_GTA_TAX_ERROR_RECYCLE';
2664
2665 END IF;
2666 END IF;/*l_invoice_type = '1'*/
2667
2668 END IF;/*IF l_invoice_type IS NULL*/
2669
2670 IF l_taxable_amount IS NULL
2671 OR l_tax_rate IS NULL
2672 OR l_tax_amount IS NULL
2673 OR l_unit_price IS NULL
2674 OR l_trx_line_quantity IS NULL
2675 THEN
2676 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2677 THEN
2678 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2679 , G_MODULE_PREFIX || l_procedure_name
2680 ,'The data come from ebtax is null. ');
2681 END IF;
2682 END IF;
2683
2684 END IF; /*l_tax_registration_number IS NULL*/
2685
2686
2687 ELSIF l_lines_status = -1
2688 THEN
2689 x_status := -1 ;
2690 ELSIF l_lines_status = 1
2691 THEN
2692 -- throw JMF_GTA_MULTI_TAXLINE exception
2693 x_status := 1;
2694 l_error_buffer := 'JMF_GTA_MULTI_TAXLINE';
2695
2696 END IF;
2697
2698 -- output the status
2699 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2700 THEN
2701 log( 'x_status '||x_status);
2702 END IF;
2703
2704 x_tax_amount := l_tax_amount;
2705 x_taxable_amount := l_taxable_amount;
2706 x_trx_line_quantity := l_trx_line_quantity;
2707 x_tax_rate := l_tax_rate/100;
2708 x_unit_selling_price := l_unit_price;
2709 x_tax_curr_unit_price := round(l_unit_price * l_tax_curr_conversion_rate);
2710 x_fp_registration_number := l_tax_registration_number;
2711 x_tp_registration_number := l_tp_registration_number;
2712 x_taxable_amount_org := l_amount;
2713 x_error_buffer := l_error_buffer;
2714 x_invoice_type := l_invoice_type;
2715
2716
2717 -- 29-JUN-2006 Added by Shujuan, calculate the unit price of tax currency
2718 -- and return it for bug 5168900
2719 x_tax_curr_unit_price := round(l_unit_price * l_tax_curr_conversion_rate);
2720
2721
2722
2723 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2724 THEN
2725 log( 'End Get_Info_From_Ebtax --');
2726 END IF;
2727
2728 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2729 THEN
2730 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2731 , G_MODULE_PREFIX || l_procedure_name
2732 ,'End Procedure. ');
2733 END IF;
2734
2735 EXCEPTION
2736 /* WHEN tax_error_for_recycle THEN --added by subba for R12.1
2737
2738 fnd_message.SET_NAME('JMF', 'JMF_GTA_TAX_ERROR_RECYCLE');
2739 l_error_string := fnd_message.get();
2740 -- begin log
2741 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2742 THEN
2743 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
2744 , G_MODULE_PREFIX || l_procedure_name
2745 , 'tax rate and tax amount should be zero for Recycle Invoices');
2746 END IF;
2747 RAISE;
2748
2749 -- end log
2750 RAISE;*/
2751 WHEN OTHERS THEN
2752 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2753 THEN
2754 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
2755 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
2756 , Sqlcode||Sqlerrm);
2757 END IF;
2758 RAISE;
2759
2760 END Get_Info_From_Ebtax;
2761
2762 --==========================================================================
2763 -- Procedure NAME:
2764 --
2765 -- get_tp_tax_registration_number Public
2766 --
2767 -- DESCRIPTION:
2768 --
2769 -- This function third party registration number by trx line id
2770 --
2771 -- PARAMETERS:
2772 -- In:
2773
2774 -- p_tax_line_id in number
2775 -- OUT:
2776 -- x_tp_tax_registration_number OUT NOCOPY VARCHAR2
2777 --
2778 --
2779 --
2780 -- DESIGN REFERENCES:
2781 --
2782 --
2783 -- CHANGE HISTORY:
2784 --
2785 -- 17-AUG-2005: JIM.Zheng Created
2786 -- Mar-21, 2006 Jogen Hu Bug 5088458
2787 --===========================================================================
2788 PROCEDURE Get_Tp_Tax_Registration_Number
2789 ( p_trx_id IN NUMBER
2790 , p_tax_line_id IN NUMBER
2791 , x_tp_tax_registration_number OUT NOCOPY VARCHAR2
2792 )
2793 IS
2794 l_procedure_name VARCHAR2(80) := 'get_tp_tax_registration_number';
2795 l_bill_to_site_use_id ra_customer_trx_all.bill_to_site_use_id%TYPE;
2796 l_ra_cust_trx_id ra_customer_trx_all.customer_trx_id%TYPE;
2797 l_tax_regime_code zx_lines.tax_regime_code%TYPE;
2798 l_tax zx_lines.tax%TYPE;
2799 l_tax_jurisdiction_code zx_lines.tax_jurisdiction_code%TYPE;
2800 l_tax_determine_date zx_lines.tax_determine_date%TYPE;
2801 l_party_tax_profile_id zx_party_tax_profile.party_tax_profile_id%TYPE;
2802 l_tax_registration_number zx_registrations.registration_number%TYPE;
2803 l_reg_tax_regime_code zx_registrations.tax_regime_code%TYPE;
2804 l_reg_tax zx_registrations.tax%TYPE;
2805 l_reg_tax_jursidiction_code zx_registrations.tax_jurisdiction_code%TYPE;
2806
2807 l_cust_acct_site_id hz_cust_site_uses_all.cust_acct_site_id%TYPE;
2808 l_party_site_id hz_cust_acct_sites_all.party_site_id%TYPE;
2809
2810 l_tax_registration_count NUMBER;
2811 l_tax_profile_status NUMBER;
2812
2813 l_tp_registration_number zx_registrations.registration_number%TYPE;
2814 l_tp_registration_number_a zx_registrations.registration_number%TYPE;
2815 l_tp_registration_number_b zx_registrations.registration_number%TYPE;
2816 l_tp_registration_number_c zx_registrations.registration_number%TYPE;
2817
2818 l_return_status VARCHAR2(200);
2819 l_msg_count NUMBER;
2820 l_msg_data VARCHAR2(2000);
2821
2822 i NUMBER;
2823 l_indexO NUMBER;
2824 CURSOR c_tp_reg_num
2825 (p_party_tax_profile_id NUMBER
2826 ,p_tax_regime_code VARCHAR2
2827 ,p_tax VARCHAR2
2828 ,p_tax_jurisdiction_code VARCHAR2
2829 ,p_tax_determine_date Date
2830 )
2831 IS
2832 SELECT
2833 reg.registration_number
2834 ,reg.tax_regime_code
2835 ,reg.tax
2836 ,reg.tax_jurisdiction_code
2837 INTO
2838 l_tax_registration_number
2839 ,l_reg_tax_regime_code
2840 ,l_reg_tax
2841 ,l_reg_tax_jursidiction_code
2842 FROM
2843 zx_registrations reg
2844 WHERE reg.party_tax_profile_id =p_party_tax_profile_id
2845 AND (reg.tax is NULL or reg.tax = p_tax)
2846 AND reg.tax_regime_code = p_tax_regime_code -- tax_regime_code is not null
2847 AND (reg.tax_jurisdiction_code is NULL or reg.tax_jurisdiction_code = p_tax_jurisdiction_code)
2848 AND p_tax_determine_date >= reg.effective_from
2849 AND (p_tax_determine_date < reg.effective_to OR reg.effective_to IS NULL)
2850 AND reg.registration_number IS NOT NULL;
2851 BEGIN
2852 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2853 THEN
2854 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2855 , G_MODULE_PREFIX || l_procedure_name
2856 ,'begin Procedure. ');
2857
2858 END IF;
2859
2860 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2861 THEN
2862 log( 'Begin Get_Info_From_Ebtax --');
2863 log( 'p_trx_id :'||p_trx_id);
2864 log( 'p_tax_line_id:' ||p_tax_line_id);
2865 END IF;
2866
2867 --get party_site_id from trx_header
2868 BEGIN
2869 -- get site use id by trx id
2870 SELECT
2871 bill_to_site_use_id
2872 INTO
2873 l_bill_to_site_use_id
2874 FROM
2875 ra_customer_trx_all trx_header
2876 WHERE trx_header.customer_trx_id = p_trx_id;
2877
2878 -- get cust_acct_site_id by site_use_id
2879 SELECT
2880 cust_acct_site_id
2881 INTO
2882 l_cust_acct_site_id
2883 FROM
2884 hz_cust_site_uses_all
2885 WHERE SITE_USE_ID = l_bill_to_site_use_id;
2886
2887 --get party_site_id by cust_acct_site_id
2888 SELECT
2889 party_site_id
2890 INTO
2891 l_party_site_id
2892 FROM
2893 hz_cust_acct_sites_all
2894 WHERE cust_acct_site_id = l_cust_acct_site_id;
2895 EXCEPTION
2896 WHEN no_data_found THEN
2897 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2898 THEN
2899 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2900 , G_MODULE_PREFIX || l_procedure_name
2901 , l_procedure_name||'no data found ');
2902 END IF;/*(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)*/
2903 END;
2904
2905 -- get the tax_regime, tax, tax_jurisdiction by trx line id ;
2906 BEGIN
2907 SELECT
2908 tax.tax_regime_code
2909 , tax.tax
2910 , tax.tax_jurisdiction_code
2911 , tax.tax_determine_date
2912 INTO
2913 l_tax_regime_code
2914 , l_tax
2915 , l_tax_jurisdiction_code
2916 , l_tax_determine_date
2917 FROM
2918 zx_lines tax
2919 WHERE
2920 tax.tax_line_id = p_tax_line_id;
2921
2922 EXCEPTION
2923 WHEN no_data_found THEN
2924 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2925 THEN
2926 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2927 , G_MODULE_PREFIX || l_procedure_name
2928 , 'no data found ');
2929 END IF;/*(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)*/
2930
2931 END;
2932
2933 -- get tax_profile_id by party site id
2934 BEGIN
2935 SELECT
2936 party_tax_profile_id
2937 INTO
2938 l_party_tax_profile_id
2939 FROM
2940 zx_party_tax_profile tax_prof
2941 WHERE tax_prof.party_id = l_party_site_id
2942 AND tax_prof.party_type_code = 'THIRD_PARTY_SITE';
2943 EXCEPTION
2944 WHEN no_data_found THEN
2945 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2946 THEN
2947 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2948 , G_MODULE_PREFIX || l_procedure_name
2949 , 'no data found ');
2950 END IF;/*(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)*/
2951 WHEN too_many_rows THEN
2952 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2953 THEN
2954 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2955 , G_MODULE_PREFIX || l_procedure_name
2956 , 'too many rows ');
2957 END IF;/*(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)*/
2958 END;
2959
2960 /*
2961 dbms_output.put_line('l_bill_to_site_use_id: '||l_bill_to_site_use_id);
2962 dbms_output.put_line('l_tax_regime_code: '||l_tax_regime_code);
2963 dbms_output.put_line('l_tax: '||l_tax);
2964 dbms_output.put_line('l_tax_jurisdiction_code: '||l_tax_jurisdiction_code);
2965 dbms_output.put_line('l_party_tax_profile_id: '||l_party_tax_profile_id);
2966 */
2967 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2968 THEN
2969 log( 'l_bill_to_site_use_id :'||l_bill_to_site_use_id);
2970 log( 'l_tax_regime_code:' ||l_tax_regime_code);
2971 log( 'l_tax:' ||l_tax);
2972 log( 'l_tax_jurisdiction_code:' ||l_tax_jurisdiction_code);
2973 log( 'l_party_tax_profile_id:' ||l_party_tax_profile_id);
2974 END IF;
2975
2976 IF l_tax_regime_code IS NOT NULL AND l_tax IS NOT NULL AND l_tax_determine_date IS NOT NULL
2977 THEN
2978 OPEN c_tp_reg_num (p_party_tax_profile_id => l_party_tax_profile_id
2979 ,p_tax_regime_code => l_tax_regime_code
2980 ,p_tax => l_tax
2981 ,p_tax_jurisdiction_code => l_tax_jurisdiction_code
2982 ,p_tax_determine_date => l_tax_determine_date
2983 );
2984
2985 LOOP
2986 FETCH
2987 c_tp_reg_num
2988 INTO
2989 l_tp_registration_number
2990 , l_reg_tax_regime_code
2991 , l_reg_tax
2992 , l_reg_tax_jursidiction_code;
2993
2994 IF c_tp_reg_num%NOTFOUND
2995 THEN
2996 EXIT;
2997 END IF;
2998
2999 IF l_reg_tax = l_tax AND l_reg_tax_jursidiction_code = l_reg_tax_jursidiction_code
3000 THEN
3001 l_tp_registration_number_a := l_tp_registration_number;
3002 ELSIF l_reg_tax = l_tax AND l_reg_tax_jursidiction_code IS NULL
3003 THEN
3004 l_tp_registration_number_b := l_tp_registration_number;
3005 ELSIF l_reg_tax IS NULL AND l_reg_tax_jursidiction_code IS NULL
3006 THEN
3007 l_tp_registration_number_c := l_tp_registration_number;
3008 END IF;
3009 END LOOP;/*fetch c_tp_reg_num*/
3010 CLOSE c_tp_reg_num; --jogen Hu Apr-4, 2006 bug 5135169
3011
3012 IF l_tp_registration_number_a IS NOT NULL
3013 THEN
3014 x_tp_tax_registration_number := l_tp_registration_number_a;
3015 ELSIF x_tp_tax_registration_number IS NULL AND l_tp_registration_number_b IS NOT NULL
3016 THEN
3017 x_tp_tax_registration_number := l_tp_registration_number_b;
3018 ELSIF x_tp_tax_registration_number IS NULL AND l_tp_registration_number_c IS NOT NULL
3019 THEN
3020 x_tp_tax_registration_number := l_tp_registration_number_c;
3021 END IF;/*l_tp_registration_number_a IS NOT NULL*/
3022
3023 ELSE /*l_tax_regime_code IS NOT NULL AND l_tax IS NOT NULL AND l_tax_determine_date IS NOT NULL*/
3024 x_tp_tax_registration_number := NULL;
3025
3026 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3027 THEN
3028 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3029 , G_MODULE_PREFIX || l_procedure_name
3030 ,'tax or tax_jur is null in zx_lines ');
3031 END IF;
3032
3033 END IF;/*l_tax_regime_code IS NOT NULL AND l_tax IS NOT NULL AND l_tax_determine_date IS NOT NULL*/
3034
3035 --dbms_output.put_line('registration_number: '||l_tax_registration_number);
3036 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3037 THEN
3038 log( 'End Get_Tp_Tax_Registration_Number --');
3039 END IF;
3040
3041 --jogen Mar-21, 2006 bug 5088458
3042 IF x_tp_tax_registration_number IS NULL
3043 THEN
3044 x_tp_tax_registration_number := ZX_API_PUB.get_default_tax_reg(
3045 p_api_version => 1.0
3046 , p_init_msg_list => NULL
3047 , p_commit => NULL
3048 , p_validation_level => NULL
3049 , x_return_status => l_return_status
3050 , x_msg_count => l_msg_count
3051 , x_msg_data => l_msg_data
3052 , p_party_id => l_party_site_id
3053 , p_party_type => 'THIRD_PARTY_SITE'
3054 , p_effective_date => SYSDATE);
3055
3056 IF l_msg_count > 0
3057 THEN
3058
3059 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3060 THEN
3061 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3062 , G_MODULE_PREFIX || l_procedure_name
3063 , 'ZX_API_PUB.get_default_tax_reg error, see below '
3064 ||'the detail error messages' );
3065
3066 FOR i IN 1..l_msg_count
3067 LOOP
3068 FND_MSG_PUB.Get(i, FND_API.G_FALSE, l_msg_data, l_indexO);
3069 FND_MSG_PUB.Delete_Msg(l_indexO);
3070 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3071 , G_MODULE_PREFIX || l_procedure_name||'.ZX_API_PUB error'
3072 , l_msg_data);
3073
3074 END LOOP; --i IN 1..l_msg_count
3075
3076 END IF;--FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3077
3078 END if;--l_msg_count = 0
3079
3080 IF x_tp_tax_registration_number IS NULL
3081 THEN
3082 x_tp_tax_registration_number := ZX_API_PUB.get_default_tax_reg(
3083 p_api_version => 1.0
3084 , p_init_msg_list => NULL
3085 , p_commit => NULL
3086 , p_validation_level => NULL
3087 , x_return_status => l_return_status
3088 , x_msg_count => l_msg_count
3089 , x_msg_data => l_msg_data
3090 , p_party_id => l_party_site_id
3091 , p_party_type => 'THIRD_PARTY'
3092 , p_effective_date => SYSDATE);
3093 IF l_msg_count > 0
3094 THEN
3095
3096 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3097 THEN
3098 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3099 , G_MODULE_PREFIX || l_procedure_name
3100 , 'ZX_API_PUB.get_default_tax_reg error, see below '
3101 ||'the detail error messages' );
3102
3103 FOR i IN 1..l_msg_count
3104 LOOP
3105 FND_MSG_PUB.Get(i, FND_API.G_FALSE, l_msg_data, l_indexO);
3106 FND_MSG_PUB.Delete_Msg(l_indexO);
3107 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3108 , G_MODULE_PREFIX || l_procedure_name||'.ZX_API_PUB error'
3109 , l_msg_data);
3110
3111 END LOOP; --i IN 1..l_msg_count
3112
3113 END IF;--FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3114
3115 END if;--l_msg_count = 0
3116
3117 END IF;--x_tp_tax_registration_number IS NULL
3118
3119 END IF; --x_tp_tax_registration_number IS NULL
3120 --jogen Mar-21, 2006 bug 5088458
3121
3122 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3123 THEN
3124 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
3125 , G_MODULE_PREFIX || l_procedure_name
3126 ,'End Procedure. ');
3127 END IF;
3128
3129 EXCEPTION
3130 WHEN OTHERS THEN
3131 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3132 THEN
3133 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
3134 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
3135 , Sqlcode||Sqlerrm);
3136 END IF;
3137 RAISE;
3138 END Get_Tp_Tax_Registration_Number;
3139
3140 --==========================================================================
3141 -- Procedure NAME:
3142 --
3143 -- Get_Arline_Tp_Taxreg_Number Public
3144 --
3145 -- DESCRIPTION:
3146 --
3147 -- This function is to get third party tax registration number upon one
3148 -- AR line according to GTA logic
3149 --
3150 -- PARAMETERS:
3151 -- In: p_org_id Identifier of operating unit
3152 -- p_customer_trx_id Identifier of AR transaction
3153 -- p_customer_trx_line_id Identifier of AR transaction line
3154 --
3155 -- Out:
3156 --
3157 -- Return:
3158 -- VARCHAR2
3159 --
3160 --
3161 --
3162 -- DESIGN REFERENCES:
3163 -- GTA_Reports_TD.doc
3164 --
3165 -- CHANGE HISTORY:
3166 --
3167 -- 25-Nov-2005: Donghai Wang Created
3168 --
3169 --===========================================================================
3170 FUNCTION Get_Arline_Tp_Taxreg_Number
3171 (p_org_id IN NUMBER
3172 ,p_customer_trx_id IN NUMBER
3173 ,p_customer_trx_line_id IN NUMBER
3174 )
3175 RETURN VARCHAR2
3176 IS
3177 l_tax_type_code zx_lines.tax_type_code%TYPE;
3178 l_tax_rate NUMBER;
3179 l_gt_currency_code fnd_currencies.currency_code%TYPE;
3180 l_tax_line_id zx_lines.tax_line_id%TYPE;
3181 l_tp_tax_registration_number zx_registrations.registration_number%TYPE;
3182 l_trx_id ra_customer_trx_all.customer_trx_id%TYPE;--Donghai Wang bug5212702 May-17,2006
3183
3184
3185 CURSOR c_tax_type_code
3186 IS
3187 SELECT
3188 vat_tax_type_code
3189 ,gt_currency_code
3190 FROM
3191 jmf_gta_system_parameters_all
3192 WHERE org_id=p_org_id;
3193
3194 --CURSOR c_tax_line_id --Donghai Wang bug5212702 May-17,2006
3195 CURSOR c_tax_line_id(pc_trx_id NUMBER)--Donghai Wang bug5212702 May-17,2006
3196 IS
3197 SELECT
3198 tax_line_id
3199 FROM
3200 zx_lines
3201 WHERE trx_line_id=p_customer_trx_line_id
3202 AND entity_code='TRANSACTIONS'
3203 AND application_id = 222
3204 AND trx_id = p_customer_trx_id
3205 AND trx_level_type='LINE'
3206 AND tax_type_code=l_tax_type_code
3207 AND tax_currency_code=l_gt_currency_code
3208 AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--Donghai Wang bug5212702 May-17,2006
3209 AND trx_id=pc_trx_id --Donghai Wang bug5212702 May-17,2006
3210 ORDER BY tax_line_id;
3211
3212
3213
3214 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
3215 l_proc_level NUMBER := fnd_log.level_procedure;
3216 l_procedure_name VARCHAR2(30) :='Get_Arline_Tp_Taxreg_Number';
3217
3218 BEGIN
3219 --logging for debug
3220 IF (l_proc_level >= l_dbg_level)
3221 THEN
3222 fnd_log.STRING(l_proc_level
3223 ,g_module_prefix || l_procedure_name || '.begin'
3224 ,'Enter function');
3225 END IF; --l_proc_level>=l_dbg_level)
3226
3227
3228 --Get Vat tax type and GT currency code defined in GTA system options form
3229 --for current operating unit
3230 OPEN c_tax_type_code;
3231 FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
3232 CLOSE c_tax_type_code;
3233
3234 --Get VAT tax line id for current AR line
3235
3236 --Donghai Wang bug5212702 May-17,2006
3237 -- OPEN c_tax_line_id;
3238 SELECT customer_trx_id
3239 INTO l_trx_id
3240 FROM ra_customer_trx_lines_all
3241 WHERE customer_trx_line_id=p_customer_trx_line_id;
3242
3243 OPEN c_tax_line_id(l_trx_id);
3244 --Donghai Wang bug5212702 May-17,2006
3245
3246 FETCH c_tax_line_id INTO l_tax_line_id;
3247 CLOSE c_tax_line_id;
3248
3249 --To get third party tax registration number for cunrrent VAT tax line
3250 Get_Tp_Tax_Registration_Number(p_trx_id => p_customer_trx_id
3251 ,p_tax_line_id => l_tax_line_id
3252 ,x_tp_tax_registration_number => l_tp_tax_registration_number
3253 );
3254
3255 --logging for debug
3256 IF (l_proc_level >= l_dbg_level)
3257 THEN
3258 fnd_log.STRING(l_proc_level
3259 ,g_module_prefix || l_procedure_name || '.End'
3260 ,'Exit function');
3261 END IF; --l_proc_level>=l_dbg_level)
3262
3263 RETURN(l_tp_tax_registration_number);
3264 END Get_Arline_Tp_Taxreg_Number;
3265
3266
3267
3268 --========================================================================
3269 -- PROCEDURE : debug_output PUBLIC
3270 -- PARAMETERS: p_output_to Identifier of where to output to
3271 -- p_api_name the called api name
3272 -- p_log_level log level
3273 -- p_message the message that need to be output
3274 --
3275 -- COMMENT : the debug output, for using in readonly UT environment
3276 --
3277 -- PRE-COND :
3278 --
3279 -- EXCEPTIONS:
3280 --========================================================================
3281 PROCEDURE Debug_Output
3282 ( p_output_to IN VARCHAR2
3283 , p_log_level IN NUMBER
3284 , p_api_name IN VARCHAR2
3285 , p_message IN VARCHAR2
3286 )
3287 IS
3288 l_procedure_name VARCHAR2(30) := 'debug_output';
3289 BEGIN
3290
3291 CASE p_output_to
3292 WHEN 'FND_LOG.STRING' THEN
3293 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3294 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
3295 ,p_api_name || '.debug_output'
3296 ,p_message);
3297 END IF;
3298 WHEN 'FND_FILE.OUTPUT' THEN
3299 IF (FND_LOG.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3300 fnd_file.put_line(fnd_file.OUTPUT
3301 ,p_api_name || '.debug_output' || ': ' ||
3302 p_message);
3303 END IF;
3304 WHEN 'FND_FILE.LOG' THEN
3305 IF (FND_LOG.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3306 log(p_api_name || '.debug_output' || ': ' ||
3307 p_message);
3308 END IF;
3309 ELSE
3310 NULL;
3311 END CASE;
3312
3313 EXCEPTION
3314 WHEN OTHERS THEN
3315 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3316 THEN
3317 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
3318 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
3319 , Sqlcode||Sqlerrm);
3320 END IF;
3321 RAISE;
3322
3323 END Debug_Output;
3324
3325
3326 --==========================================================================
3327 -- FUNCTION NAME:
3328 --
3329 -- Get_AR_Batch_Source_Name Public
3330 --
3331 -- DESCRIPTION:
3332 --
3333 -- This function is to get AR Batch Source Name for a given org_id and
3334 -- source id
3335 --
3336 -- PARAMETERS:
3337 -- In: p_org_id Identifier of Operating Unit
3338 -- In: p_source_id AR batch source id
3339 -- Return: VARCHAR2
3340 --
3341 -- DESIGN REFERENCES:
3342 -- GTA_Reports_TD.doc
3343 --
3344 -- CHANGE HISTORY:
3345 --
3346 -- 01-Dec-2005: Qiang Li Creation
3347 --
3348 --=========================================================================
3349 FUNCTION Get_AR_Batch_Source_Name
3350 ( p_org_id IN NUMBER
3351 , p_source_id IN NUMBER
3352 )
3353 RETURN VARCHAR2 IS
3354 l_procedure_name VARCHAR2(30) := 'Get_AR_Batch_Source_Name';
3355 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
3356 l_proc_level NUMBER := fnd_log.level_procedure;
3357
3358 l_source_name RA_BATCH_SOURCES_all.NAME%TYPE;
3359 CURSOR c_source_name IS
3360 SELECT RA_BATCH_SOURCES_all.NAME
3361 FROM RA_BATCH_SOURCES_all
3362 WHERE org_id = p_org_id
3363 AND BATCH_SOURCE_ID = p_source_id;
3364
3365 BEGIN
3366 --logging for debug
3367 IF (l_proc_level >= l_dbg_level)
3368 THEN
3369 fnd_log.STRING(l_proc_level
3370 ,g_module_prefix || l_procedure_name || '.begin'
3371 ,'enter function');
3372 END IF;
3373
3374 OPEN c_source_name;
3375 FETCH
3376 c_source_name
3377 INTO
3378 l_source_name;
3379
3380 CLOSE c_source_name;
3381
3382 --logging for debug
3383 IF (l_proc_level >= l_dbg_level)
3384 THEN
3385 fnd_log.STRING(l_proc_level
3386 ,g_module_prefix || l_procedure_name || '.end'
3387 ,'end function');
3388 END IF;
3389
3390 RETURN(l_source_name);
3391 END Get_AR_Batch_Source_Name;
3392
3393 --==========================================================================
3394 -- FUNCTION NAME:
3395 --
3396 -- To_Xsd_Date_String Public
3397 --
3398 -- DESCRIPTION:
3399 --
3400 -- Convert an Oracle DB Date Object to a date string represented
3401 -- in the XSD Date Format. This is mainly for use by the
3402 -- XML Publisher Reports.
3403 --
3404 -- PARAMETERS:
3405 -- In: p_date Oracle Date to be converted to XSD Date Format
3406 --
3407 -- Return: VARCHAR2 A String representing the passed in Date in XSD
3408 -- Date Format
3409 --
3410 -- DESIGN REFERENCES:
3411 --
3412 --
3413 -- CHANGE HISTORY:
3414 --
3415 -- 14-Sep-2006: Donghai Wang Creation
3416 --
3417 --=========================================================================
3418 FUNCTION To_Xsd_Date_String
3419 ( p_date IN DATE
3420 )
3421 RETURN VARCHAR2
3422 IS
3423 l_xsd_date_string VARCHAR2(40);
3424 l_procedure_name VARCHAR2(30) := 'To_Xsd_Date_String';
3425 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
3426 l_proc_level NUMBER := fnd_log.level_procedure;
3427
3428 BEGIN
3429
3430 --logging for debug
3431 IF (l_proc_level >= l_dbg_level)
3432 THEN
3433 fnd_log.STRING(l_proc_level
3434 ,g_module_prefix || l_procedure_name || '.begin'
3435 ,'enter function');
3436 END IF;
3437
3438 --If input parameter is null, then returen a null string
3439 IF p_date IS NULL
3440 THEN
3441 IF (l_proc_level >= l_dbg_level)
3442 THEN
3443 FND_LOG.string( l_proc_level
3444 , G_MODULE_PREFIX
3445 , G_MODULE_PREFIX || l_procedure_name
3446 || '.end'
3447 );
3448 END IF;
3449
3450 RETURN NULL;
3451 END IF; --p_date IS NULL
3452
3453
3454
3455 SELECT TO_CHAR(p_date, 'YYYY-MM-DD')
3456 INTO l_xsd_date_string
3457 FROM DUAL;
3458
3459
3460 IF (l_proc_level >= l_dbg_level)
3461 THEN
3462 FND_LOG.string( l_proc_level
3463 , G_MODULE_PREFIX
3464 , G_MODULE_PREFIX || l_procedure_name
3465 || '.end: Returning XSD Date = '
3466 || l_xsd_date_string);
3467 END IF;
3468
3469 l_xsd_date_string := TRIM(l_xsd_date_string);
3470
3471 RETURN l_xsd_date_string;
3472
3473 EXCEPTION
3474
3475 WHEN OTHERS THEN
3476 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3477 THEN
3478 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
3479 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
3480 , Sqlcode||Sqlerrm);
3481 END IF;
3482 RAISE;
3483
3484
3485 END To_Xsd_Date_String;
3486
3487 --==========================================================================
3488 -- FUNCTION NAME:
3489 --
3490 -- Format_Monetary_Amount Public
3491 --
3492 -- DESCRIPTION:
3493 --
3494 -- Convert monetory amount with the format mask what is determined
3495 -- by VAT currency code and related profile values.
3496 --
3497 -- PARAMETERS:
3498 -- In: p_org_id Identifier of Operating Unit
3499 -- p_amount Monetary amount
3500 --
3501 -- Return: VARCHAR2
3502 --
3503 --
3504 -- DESIGN REFERENCES:
3505 --
3506 --
3507 -- CHANGE HISTORY:
3508 --
3509 -- 20-Sep-2006: Donghai Wang Creation
3510 --
3511 --=========================================================================
3512 FUNCTION Format_Monetary_Amount
3513 (p_org_id IN NUMBER
3514 ,p_amount IN NUMBER
3515 )
3516 RETURN VARCHAR2
3517 IS
3518 l_procedure_name VARCHAR2(30) := 'Format_Monetary_Amount';
3519 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
3520 l_proc_level NUMBER := fnd_log.level_procedure;
3521 l_base_currency jmf_gta_system_parameters_all.gt_currency_code%TYPE;
3522 l_format_mask VARCHAR2(50);
3523 l_formatted_amount VARCHAR2(50);
3524
3525 CURSOR c_base_currency IS
3526 SELECT
3527 gt_currency_code
3528 FROM
3529 jmf_gta_system_parameters_all
3530 WHERE
3531 org_id=p_org_id;
3532
3533 BEGIN
3534
3535 --logging for debug
3536 IF (l_proc_level >= l_dbg_level)
3537 THEN
3538 fnd_log.STRING(l_proc_level
3539 ,g_module_prefix || l_procedure_name || '.begin'
3540 ,'enter function');
3541 END IF;
3542
3543 --Get VAT Currency code of current operating unit
3544 OPEN c_base_currency;
3545 FETCH c_base_currency INTO l_base_currency;
3546 CLOSE c_base_currency;
3547
3548 --Get format mask for VAT currency code
3549 l_format_mask:=FND_CURRENCY.Get_Format_Mask(currency_code => l_base_currency
3550 ,field_length => 30
3551 );
3552 l_formatted_amount:=to_char(p_amount,l_format_mask);
3553
3554 --logging for debug
3555 IF (l_proc_level >= l_dbg_level)
3556 THEN
3557 fnd_log.STRING(l_proc_level
3558 ,g_module_prefix || l_procedure_name || '.end'
3559 ,'end function');
3560 END IF;
3561 RETURN l_formatted_amount;
3562
3563 EXCEPTION
3564
3565 WHEN OTHERS THEN
3566 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3567 THEN
3568 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
3569 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
3570 , Sqlcode||Sqlerrm);
3571 END IF;
3572 RAISE;
3573
3574 END Format_Monetary_Amount;
3575
3576
3577
3578 END JMF_GTA_TRX_UTIL;