[Home] [Help]
PACKAGE BODY: APPS.AR_GTA_REPORTS_PKG
Source
1 PACKAGE BODY AR_GTA_REPORTS_PKG AS
2 --$Header: ARGRREPB.pls 120.3.12020000.2 2012/07/09 10:21:12 riqi ship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| ARRREPB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is used to generate Golden Tax Adaptor reports. |
13 --| |
14 --| PROCEDURE LIST |
15 --| FUNCTION Get_Ar_Trx |
16 --| FUNCTION Get_Gt_Trxs |
17 --| PROCEDURE Generate_Mapping_Rep |
18 --| PROCEDURE Compare_Header |
19 --| PROCEDURE Compare_Lines |
20 --| PROCEDURE Get_Unmatched_Lines |
21 --| PROCEDURE Generate_Discrepancy_Xml |
22 --| PROCEDURE Generate_Discrepancy_Rep |
23 --| PROCEDURE Generate_Consol_Mapping_Rep |
24 --| PROCEDURE Get_Consolidation_Trx |
25 --| PROCEDURE Get_Consolidated_Trxs |
26 --| |
27 --| HISTORY |
28 --| 05/08/05 Qiang Li Created |
29 --| 05/17/05 Donghai Wang Add procedures: |
30 --| Compare_Header |
31 --| Compare_Lines |
32 --| Get_Unmatched_Lines |
33 --| Generate_Discrepancy_Xml |
34 --| Generate_Discrepancy_Rep |
35 --| 09/27/05 Qiang Li Add function: |
36 --| Get_Gt_Tax_Reg_Count |
37 --| 25/11/05 Donghai Wang modify procedure Compare_Header |
38 --| ,Compare_Lines, |
39 --| ,Get_Unmatched_Lines and |
40 --| Generate_Discrepancy_Xml |
41 --| according to ebtax functionality|
42 --| 30/11/05 Donghai Wang Update procedure Compare_Header |
43 --| 01/12/05 Qiang Li Update Generate_Mapping_Rep |
44 --| 05/12/05 Qiang Li Update Generate_Mapping_Rep |
45 --| Update Get_Gt_Trx |
46 --| Update Get_Ar_Trx |
47 --| Rename Get_Gt_Tax_Reg_Count to |
48 --| Get_Gt_Count |
49 --| 07/02/06 Qiang Li Update FUNCTION Get_Ar_Trx |
50 --| 06/03/06 Donghai Wang Update Compare_Header, |
51 --| Compare_Lines,Get_Unmatched_lines|
52 --| Generate_Discrepancy_Xml, |
53 --| Generate_Discrepancy_Rep for |
54 --| Adding fnd log |
55 --| 06/18/07 Donghai Wang Update the procedure compare_lines|
56 --| to fix bug 6132187 |
57 --| 07/05/07 Allen Yang Update procedure compare_header |
58 --| to fix bug 6147067 |
59 --| 01/02/08 Subba Updated procedure compare_header |
60 --| for R12.1
61 --| 13-May-2009 Yao Zhang Fix bug#5604079 FOR FOREIGN CURR. |
62 --| TRXN, DISCREPANCY SHOWN DUE TO CURR|.
63 --| ROUNDING ISSU |
64 --| 25-Jul-2009 Allen Yang Add functions and procedure: |
65 --| Generate_Consol_Mapping_Rep |
66 --| Get_Consolidation_Trx |
67 --| Get_Consolidated_Trxs |
68 --| for bug 8605196: ENHANCEMENT FOR |
69 --| GOLDEN TAX ADAPTER R12.1.2 |
70 --| 10-Aug-2009 Yao Zhang Modified for bug# 8765631 R12.1.2 |
71 --| 14-July-2009 Yao Zhang Fix bug# 8766075 GTA INVOICE MAPPING REPORT ERROR|
72 --| 19-Aug-2009 Yao Zhang modified for bug#8809860 'Continue' can not be used in pl/sql package
73 --| 02-Sep-2009 Allen Yang modified for bug 8848696
74 --| 23-Sep-2009 Yao Zhang fix bug#8289585 TST1211.ST:THE GOLDEN TAX DISCREPANCY|
75 --| 24-Sep-2009 Allen Yang Modified function Get_Ar_Trx to |
76 --| fix bug 8920326 |
77 --| 11-Jan-2011 Qiong Liu fix bug#10638369 INCLUSIVE TAX UNIT PRICE |
78 --| IS RE-CALCULATED AFTER CONSOLIDATION |
79 --+======================================================================*/
80
81 --==========================================================================
82 -- FUNCTION NAME:
83 --
84 -- Get_Gt_Count Public
85 --
86 -- DESCRIPTION:
87 --
88 -- This function get GT trxs count in a given
89 -- AR transaction
90 -- PARAMETERS:
91 -- In: p_ar_trx_header_id AR transaction header id
92 -- p_fp_tax_reg_num first party tax registration number
93 -- P_Gt_Inv_Date_From Golden Tax Invoice Date from
94 -- P_Gt_Inv_Date_To Golden Tax Invoice Date to
95 -- P_Gt_Inv_Num_From Golden Tax Invoice Number from
96 -- P_Gt_Inv_Num_To Golden Tax Invoice Number to
97 -- Out:
98 --
99 -- Return: VARCHAR2
100 --
101 --
102 -- DESIGN REFERENCES:
103 -- GTA_REPORTS_TD.doc
104 --
105 -- CHANGE HISTORY:
106 --
107 -- 27-Sep-05 Qiang Li Created.
108 -- 05-Dec-05 Qiang Li Rename to get_gt_count
109 -- Add four new parameters
110 --
111 --===========================================================================
112 FUNCTION Get_Gt_Count
113 ( p_ar_trx_header_id IN NUMBER
114 , p_fp_tax_reg_num IN VARCHAR2
115 , P_Gt_Inv_Date_From IN DATE
116 , P_Gt_Inv_Date_To IN DATE
117 , P_Gt_Inv_Num_From IN VARCHAR2
118 , P_Gt_Inv_Num_To IN VARCHAR2
119 )
120 RETURN VARCHAR2
121 IS
122 l_procedure_name VARCHAR2(40):='Get_Gt_Count';
123 l_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
124 l_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
125
126 l_count NUMBER:=0;
127 BEGIN
128 --logging for debug
129 IF (l_proc_level>=l_dbg_level)
130 THEN
131 FND_LOG.string( l_proc_level
132 , G_MODULE_PREFIX||l_procedure_name||'.begin'
133 , 'enter function'
134 );
135 END IF;
136
137 SELECT
138 COUNT(*)
139 INTO
140 l_count
141 FROM
142 AR_Gta_Trx_Headers_All Gt
143 WHERE Gt.Ra_Trx_Id = p_ar_trx_header_id
144 AND Source='GT'
145 AND Status='COMPLETED'
146 AND (Gt_Invoice_Number NOT BETWEEN p_Gt_Inv_Num_From
147 AND p_Gt_Inv_Num_To
148 OR Gt_Invoice_Date NOT BETWEEN p_Gt_Inv_Date_From
149 AND p_Gt_Inv_Date_To
150 OR Fp_Tax_Registration_Number <> NVL(p_fp_tax_reg_num
151 ,Fp_Tax_Registration_Number)
152 );
153
154 --logging for debug
155 IF (l_proc_level>=l_dbg_level)
156 THEN
157 FND_LOG.string( l_proc_level
158 , G_MODULE_PREFIX||l_procedure_name||'.end'
159 , 'end function'
160 );
161 END IF;
162
163 IF (l_count>0)
164 THEN
165 RETURN 'YES';
166 ELSE
167 RETURN 'NO';
168 END IF;
169
170
171 END Get_Gt_Count;
172
173 --==========================================================================
174 -- FUNCTION NAME:
175 --
176 -- Get_Ar_Trx Public
177 --
178 -- DESCRIPTION:
179 --
180 -- This function get xml data of AR transaction.
181 --
182 -- PARAMETERS:
183 -- In: p_org_id Operating unit id
184 -- p_ar_trx_header_id AR transaction header id
185 -- p_fp_tax_reg_num first party tax registration number
186 -- P_Gt_Inv_Date_From Golden Tax Invoice Date from
187 -- P_Gt_Inv_Date_To Golden Tax Invoice Date to
188 -- P_Gt_Inv_Num_From Golden Tax Invoice Number from
189 -- P_Gt_Inv_Num_To Golden Tax Invoice Number to
190 -- Out:
191 --
192 -- Return: XMLTYPE
193 --
194 --
195 -- DESIGN REFERENCES:
196 -- GTA_REPORTS_TD.doc
197 --
198 -- CHANGE HISTORY:
199 --
200 -- 05/08/05 Qiang Li Created.
201 -- 09/27/05 Qiang Li Add new parameter p_fp_tax_reg_num
202 -- 05-Dec-2005 Qiang Li add four new parameters
203 -- 07-Feb-2006 Qiang Li Change data type of p_fp_tax_reg_num
204 -- to Varchar2
205 -- 24-Sep-2009 Allen Yang modified to fix bug 8920326.
206 --===========================================================================
207 FUNCTION Get_Ar_Trx
208 ( p_ar_trx_header_id IN NUMBER
209 , p_org_id IN NUMBER
210 , p_fp_tax_reg_num IN VARCHAR2
211 , P_Gt_Inv_Date_From IN DATE
212 , P_Gt_Inv_Date_To IN DATE
213 , P_Gt_Inv_Num_From IN VARCHAR2
214 , P_Gt_Inv_Num_To IN VARCHAR2
215 )
216 RETURN XMLTYPE
217 IS
218 l_procedure_name VARCHAR2(40):='Get_Ar_Trx';
219 l_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
220 l_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
221
222 l_ret_xmlelement Xmltype;
223 BEGIN
224 --logging for debug
225 IF (l_proc_level>=l_dbg_level)
226 THEN
227 FND_LOG.string( l_proc_level
228 , G_MODULE_PREFIX||l_procedure_name||'.begin'
229 , 'enter function'
230 );
231 END IF;
232 IF AR_Gta_Trx_Util.Check_Taxcount_Of_Artrx(P_Org_Id, p_ar_trx_header_id)
233 THEN
234
235 SELECT Xmlforest(
236 ract.Trx_Number AS "ARInvoiceNo"
237 ,Get_Gt_Count
238 ( p_ar_trx_header_id
239 , p_fp_tax_reg_num
240 , P_Gt_Inv_Date_From
241 , P_Gt_Inv_Date_To
242 , P_Gt_Inv_Num_From
243 , P_Gt_Inv_Num_To
244 ) AS "Split"
245 --yao zhang modified for bug8766075
246 -- ,bat.name AS "ARSource"
247 ,bas.name AS "ARSource"
248 -- modified by Allen Yang 24-Sep-2009 for bug 8920326
249 -----------------------------------------------------------------
250 ,al.meaning AS "ARClass"
251 --,CTT.TYPE AS "ARClass"
252 ------------------------------------------------------------------
253 ,ar_gta_trx_util.To_Xsd_Date_String(ract.Trx_Date) AS "ARDate"
254 ,RAC_BILL_PARTY.PARTY_NAME AS "ARCustomer"
255 ,AR_Gta_Trx_Util.Get_Arinvoice_Amount
256 ( P_Org_Id
257 , ract.Customer_Trx_Id
258 ) AS "ARAmount"
259 ,AR_Gta_Trx_Util.Get_Arinvoice_Tax_Amount
260 ( P_Org_Id
261 , ract.Customer_Trx_Id
262 ) AS "ARTaxAmount"
263 ,AR_Gta_Trx_Util.Get_Arinvoice_Amount
264 ( P_Org_Id
265 , ract.Customer_Trx_Id
266 )
267 +AR_Gta_Trx_Util.Get_Arinvoice_Tax_Amount
268 ( P_Org_Id
269 , ract.Customer_Trx_Id
270 ) AS "ARTotalAmount"
271 )
272 INTO
273 L_Ret_Xmlelement
274 FROM
275 Ra_Customer_Trx_all ract
276 --yao zhang modified for bug8766075
277 -- , ra_batches_all bat
278 ,RA_BATCH_SOURCES_ALL bas
279 , Ra_Cust_Trx_Types_all ctt
280 , Hz_Cust_Accounts RAC_BILL
281 , Hz_Parties RAC_BILL_PARTY
282 -- added by Allen Yang 24-Sep-2009 for bug 8920326
283 ----------------------------------------------------
284 , AR_LOOKUPS al
285 ----------------------------------------------------
286
287 WHERE Customer_Trx_Id = P_Ar_Trx_Header_Id
288 AND ract.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID
289 AND ract.org_id = ctt.org_id
290 --yao zhang modified for bug8766075
291 --AND ract.batch_id = bat.batch_id(+)
292 AND ract.batch_source_id = bas.BATCH_SOURCE_ID(+)
293 AND ract.org_id =bas.org_id
294 AND ract.bill_to_customer_id = RAC_BILL.CUST_ACCOUNT_ID
295 AND RAC_BILL.party_id = RAC_BILL_PARTY.Party_Id
296 -- added by Allen Yang 24-Sep-2009 for bug 8920326
297 ----------------------------------------------------------
298 AND ctt.TYPE = al.LOOKUP_CODE
299 AND al.LOOKUP_TYPE = 'INV/CM'
300 ----------------------------------------------------------
301 ;
302 ELSE
303 SELECT Xmlforest(
304 ract.Trx_Number AS "ARInvoiceNo"
305 ,Get_Gt_Count
306 ( p_ar_trx_header_id
307 , p_fp_tax_reg_num
308 , P_Gt_Inv_Date_From
309 , P_Gt_Inv_Date_To
310 , P_Gt_Inv_Num_From
311 , P_Gt_Inv_Num_To
312 ) AS "Split"
313 --yao zhang modified for bug8766075
314 -- ,bat.name AS "ARSource"
315 ,bas.name AS "ARSource"
316 -- modified by Allen Yang 24-Sep-2009 for bug 8920326
317 -----------------------------------------------------------------
318 --,CTT.TYPE AS "ARClass"
319 ,al.meaning AS "ARClass"
320 -----------------------------------------------------------------
321 ,ar_gta_trx_util.To_Xsd_Date_String(ract.Trx_Date) AS "ARDate"
322 ,RAC_BILL_PARTY.PARTY_NAME AS "ARCustomer"
323 ,'' AS "ARAmount"
324 ,'' AS "ARTaxAmount"
325 ,'' AS "ARTotalAmount"
326 )
327 INTO
328 L_Ret_Xmlelement
329 FROM
330 Ra_Customer_Trx_all ract
331 --yao zhang modified for bug8766075
332 -- , ra_batches_all bat
333 ,RA_BATCH_SOURCES_ALL bas
334 , Ra_Cust_Trx_Types_all ctt
335 , Hz_Cust_Accounts RAC_BILL
336 , Hz_Parties RAC_BILL_PARTY
337 -- added by Allen Yang 24-Sep-2009 for bug 8920326
338 -----------------------------------------------------
339 , AR_LOOKUPS al
340 -----------------------------------------------------
341
342 WHERE Customer_Trx_Id = P_Ar_Trx_Header_Id
343 AND ract.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID
344 AND ract.org_id = ctt.org_id
345 --yao zhang modified for bug8766075
346 --AND ract.batch_id = bat.batch_id(+)
347 AND ract.batch_source_id = bas.BATCH_SOURCE_ID(+)
348 AND ract.org_id =bas.org_id
349 AND ract.bill_to_customer_id = RAC_BILL.CUST_ACCOUNT_ID
350 AND RAC_BILL.party_id = RAC_BILL_PARTY.Party_Id
351 -- added by Allen Yang 24-Sep-2009 for bug 8920326
352 -------------------------------------------------------
353 AND al.LOOKUP_TYPE = 'INV/CM'
354 AND ctt.TYPE = al.LOOKUP_CODE
355 -------------------------------------------------------
356 ;
357
358
359 END IF;
360
361 --logging for debug
362 IF (l_proc_level>=l_dbg_level)
363 THEN
364 FND_LOG.string( l_proc_level
365 , G_MODULE_PREFIX||l_procedure_name||'.end'
366 , 'end function'
367 );
368 END IF;
369
370 RETURN L_Ret_Xmlelement;
371 END Get_Ar_Trx;
372
373
374
375 --==========================================================================
376 -- FUNCTION NAME:
377 --
378 -- Get_Gt_Trxs Public
379 --
380 -- DESCRIPTION:
381 --
382 -- This function get XML data of Golden Tax transactions
383 --
384 -- PARAMETERS:
385 -- In: p_org_id Operating unit id
386 -- p_ar_trx_header_id AR transaction header id
387 -- p_Tax_Registration_Number First party tax registration number
388 -- P_Gt_Inv_Date_From Golden Tax Invoice Date from
389 -- P_Gt_Inv_Date_To Golden Tax Invoice Date to
390 -- P_Gt_Inv_Num_From Golden Tax Invoice Number from
391 -- P_Gt_Inv_Num_To Golden Tax Invoice Number to
392 --
393 -- Out:
394 --
395 -- Return: XMLTYPE
396 --
397 --
398 -- DESIGN REFERENCES:
399 -- GTA_REPORTS_TD.doc
400 --
401 -- CHANGE HISTORY:
402 --
403 -- 05/08/05 Qiang Li Created.
404 -- 05/12/05 Qiang Li add five new parameters
405 --
406 --===========================================================================
407 FUNCTION Get_Gt_Trxs
408 ( P_Ar_Trx_Header_Id IN NUMBER
409 , P_Org_Id IN NUMBER
410 , p_Tax_Registration_Number IN VARCHAR2
411 , P_Gt_Inv_Date_From IN DATE
412 , P_Gt_Inv_Date_To IN DATE
413 , P_Gt_Inv_Num_From IN VARCHAR2
414 , P_Gt_Inv_Num_To IN VARCHAR2
415 )
416 RETURN Xmltype
417 IS
418 l_procedure_name VARCHAR2(40):='Get_Gt_Trxs';
419 l_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
420 l_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
421
422 l_Ret_Xmlelement Xmltype;
423 l_count NUMBER;
424 BEGIN
425 --logging for debug
426 IF (l_proc_level>=l_dbg_level)
427 THEN
428 FND_LOG.string( l_proc_level
429 , G_MODULE_PREFIX||l_procedure_name||'.begin'
430 , 'enter function'
431 );
432 END IF;
433
434 -- get GT invoices count for a given ar transaction
435 SELECT
436 COUNT(*)
437 INTO
438 l_count
439 FROM
440 ar_gta_trx_headers_all
441 WHERE Ra_Trx_Id=P_Ar_Trx_Header_Id
442 AND Source='GT'
443 AND Status='COMPLETED'
444 AND Gt_Invoice_Number BETWEEN p_Gt_Inv_Num_From
445 AND p_Gt_Inv_Num_To
446 AND Gt_Invoice_Date BETWEEN p_Gt_Inv_Date_From
447 AND p_Gt_Inv_Date_To
448 AND Fp_Tax_Registration_Number=NVL(p_Tax_Registration_Number
449 ,Fp_Tax_Registration_Number
450 );
451
452 -- get GT invoices XML data for a given ar transaction
453 SELECT Xmlelement("GTInvoices",
454 Xmlconcat(Xmlelement("Count",l_count)
455 , Xmlagg(Xmlelement("GTInvoice",Xmlforest
456 ( Gt_Invoice_Number AS "InvoiceNo"
457 , ar_gta_trx_util.To_Xsd_Date_String(Gt_Invoice_Date) AS "Date"
458 , Bill_To_Customer_Name AS "Customer"
459 , Gt_Invoice_Net_Amount AS "Amount"
460 , Gt_Invoice_Tax_Amount AS "TaxAmount"
461 , Gt_Invoice_Net_Amount + Gt_Invoice_Tax_Amount AS "TotalAmount"
462 )
463 ))))
464 INTO
465 l_Ret_Xmlelement
466 FROM
467 AR_Gta_Trx_Headers_all
468 WHERE Ra_Trx_Id=P_Ar_Trx_Header_Id
469 AND Source='GT'
470 AND Status='COMPLETED'
471 AND Gt_Invoice_Number BETWEEN p_Gt_Inv_Num_From
472 AND p_Gt_Inv_Num_To
473 AND Gt_Invoice_Date BETWEEN p_Gt_Inv_Date_From
474 AND p_Gt_Inv_Date_To
475 AND Fp_Tax_Registration_Number=NVL(p_Tax_Registration_Number
476 ,Fp_Tax_Registration_Number
477 );
478
479 --logging for debug
480 IF (l_proc_level>=l_dbg_level)
481 THEN
482 FND_LOG.string( l_proc_level
483 , G_MODULE_PREFIX||l_procedure_name||'.end'
484 , 'end function'
485 );
486 END IF;
487
488 RETURN l_Ret_Xmlelement;
489 END Get_Gt_Trxs;
490
491
492
493 --==========================================================================
494 -- PROCEDURE NAME:
495 --
496 -- Generate_Mapping_Rep Public
497 --
498 -- DESCRIPTION:
499 --
500 -- This procedure generate mapping report data
501 --
502 -- PARAMETERS:
503 -- In: p_fp_tax_reg_num First Party Tax Registration Number
504 -- p_org_id Operating unit id
505 -- p_trx_source Transaction source,GT or AR
506 -- p_customer_id Customer id
507 -- p_gt_inv_num_from GT invoice number low range
508 -- p_gt_inv_num_to GT invoice number high range
509 -- p_gt_inv_date_from GT invoice date low range
510 -- p_gt_inv_date_to GT invoice date high range
511 -- p_ar_inv_num_from AR invoice number low range
512 -- p_ar_inv_num_to AR invoice number high range
513 -- p_ar_inv_date_from AR invoice date low range
514 -- p_ar_inv_date_to AR invoice date high range
515 -- Out:
516 --
517 --
518 --
519 -- DESIGN REFERENCES:
520 -- GTA_REPORTS_TD.doc
521 --
522 -- CHANGE HISTORY:
523 --
524 -- 05/08/05 Qiang Li Created.
525 -- 27-Sep-2005 Qiang Li Add a new parameter fp_tax_reg_number.
526 -- 05-Dec-2005 Qiang Li Update the logic to just display the
527 -- coincidental GT invoice
528 --===========================================================================
529 Procedure Generate_Mapping_Rep
530 ( P_Org_Id IN NUMBER
531 , p_fp_tax_reg_num IN VARCHAR2
532 , P_Trx_Source IN NUMBER
533 , P_Customer_Id IN NUMBER
534 , P_Gt_Inv_Num_From IN VARCHAR2
535 , P_Gt_Inv_Num_To IN VARCHAR2
536 , P_Gt_Inv_Date_From IN DATE
537 , P_Gt_Inv_Date_To IN DATE
538 , P_Ar_Inv_Num_From IN VARCHAR2
539 , P_Ar_Inv_Num_To IN VARCHAR2
540 , P_Ar_Inv_Date_From IN DATE
541 , P_Ar_Inv_Date_To IN DATE
542 )
543 IS
544 l_procedure_name VARCHAR2(40):='Generate_Mapping_Rep';
545 l_no_data_message VARCHAR2(500);
546 l_Ar_Trx_Id AR_Gta_Trx_Headers.Ra_Trx_Id%TYPE;
547 l_Parameter Xmltype;
548 l_Summary Xmltype;
549 L_gt_currency Xmltype;
550 l_Ar_Trx Xmltype;
551 l_Gt_Invoices Xmltype;
552 l_ar_trxs xmltype;
553 l_Report Xmltype;
554 l_Ar_Rows NUMBER;
555 I NUMBER;
556 l_Gt_Rows NUMBER;
557 l_no_data_flag VARCHAR2(1):='N';
558
559 l_Gt_Inv_Num_From VARCHAR2(30);
560 l_Gt_Inv_Num_To VARCHAR2(30);
561 l_Gt_Inv_Date_From DATE;
562 l_Gt_Inv_Date_To DATE;
563 l_Ar_Inv_Num_From VARCHAR2(20);
564 l_Ar_Inv_Num_To VARCHAR2(20);
565 l_Ar_Inv_Date_From DATE;
566 l_Ar_Inv_Date_To DATE;
567 l_gt_cur VARCHAR2(100);
568
569 l_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
570 l_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
571
572 CURSOR C_cur IS
573 SELECT
574 sysp.gt_currency_code
575 FROM
576 ar_gta_system_parameters_all sysp
577 WHERE sysp.org_id = P_Org_Id;
578
579 CURSOR C_Trx_Ids IS
580 SELECT
581 DISTINCT Gt.Ra_Trx_Id
582 FROM
583 AR_Gta_Trx_Headers_All Gt
584 ,Ra_Customer_Trx_All Ar
585 WHERE Gt.Ra_Trx_Id=Ar.Customer_Trx_Id
586 AND Gt.Org_Id=P_Org_Id
587 AND Gt.Fp_Tax_Registration_Number=NVL(p_fp_tax_reg_num
588 ,Gt.Fp_Tax_Registration_Number
589 )
590 AND Ar.Batch_Source_Id=NVL(P_Trx_Source,Ar.Batch_Source_Id)
591 AND Ar.Bill_To_Customer_Id = NVL(P_Customer_Id,Ar.Bill_To_Customer_Id)
592 AND Gt.Source='GT'
593 AND Gt.Status='COMPLETED'
594 AND Gt.Gt_Invoice_Number BETWEEN l_Gt_Inv_Num_From
595 AND l_Gt_Inv_Num_To
596 AND Gt.Gt_Invoice_Date BETWEEN l_Gt_Inv_Date_From
597 AND l_Gt_Inv_Date_To
598 AND Ar.Trx_Number BETWEEN l_Ar_Inv_Num_From
599 AND l_Ar_Inv_Num_To
600 AND Ar.Trx_Date BETWEEN l_Ar_Inv_Date_From
601 AND l_Ar_Inv_Date_To;
602
603
604 BEGIN
605 --logging for debug
606 IF (l_proc_level>=l_dbg_level)
607 THEN
608 FND_LOG.string( l_proc_level
609 , G_MODULE_PREFIX||l_procedure_name||'.begin'
610 , 'enter procedure'
611 );
612 END IF;
613
614 l_Gt_Inv_Num_From:=Nvl(P_Gt_Inv_Num_From,' ');
615 l_Gt_Inv_Num_To:=Nvl(P_Gt_Inv_Num_To,'zzz');
616 l_Gt_Inv_Date_From:=Nvl(P_Gt_Inv_Date_From,To_Date('1900-01-01'
617 ,'Rrrr-Mm-Dd'
618 )
619 );
620 l_Gt_Inv_Date_To:=Nvl(P_Gt_Inv_Date_To,To_Date('2100-12-31'
621 ,'Rrrr-Mm-Dd')
622 );
623 l_Ar_Inv_Num_From:=Nvl(P_Ar_Inv_Num_From,' ');
624 l_Ar_Inv_Num_To:=Nvl(P_Ar_Inv_Num_To,'zzz');
625 l_Ar_Inv_Date_From:=Nvl(P_Ar_Inv_Date_From,To_Date('1900-01-01'
626 ,'Rrrr-Mm-Dd'
627 )
628 );
629 l_Ar_Inv_Date_To:=Nvl(P_Ar_Inv_Date_To,To_Date('2100-12-31'
630 ,'Rrrr-Mm-Dd'
631 )
632 );
633
634 --Get Gt Invoice Rows
635 SELECT
636 COUNT(*)
637 INTO
638 l_Gt_Rows
639 FROM
640 AR_Gta_Trx_Headers Gt
641 ,Ra_Customer_Trx Ar
642 WHERE Gt.Ra_Trx_Id=Ar.Customer_Trx_Id
643 AND Gt.Org_Id=P_Org_Id
644 AND Gt.Fp_Tax_Registration_Number=NVL(p_fp_tax_reg_num
645 ,Gt.Fp_Tax_Registration_Number
646 )
647 AND Ar.Batch_Source_Id=NVL(P_Trx_Source,Ar.Batch_Source_Id)
648 AND Ar.Bill_To_Customer_Id = NVL(P_Customer_Id
649 ,Ar.Bill_To_Customer_Id
650 )
651 AND Gt.Source='GT'
652 AND Gt.Status='COMPLETED'
653 AND Gt.Gt_Invoice_Number BETWEEN l_Gt_Inv_Num_From
654 AND l_Gt_Inv_Num_To
655 AND Gt.Gt_Invoice_Date BETWEEN l_Gt_Inv_Date_From
656 AND l_Gt_Inv_Date_To
657 AND Ar.Trx_Number BETWEEN l_Ar_Inv_Num_From
658 AND l_Ar_Inv_Num_To
659 AND Ar.Trx_Date BETWEEN l_Ar_Inv_Date_From
660 AND l_Ar_Inv_Date_To;
661
662 OPEN C_Trx_Ids;
663 FETCH C_Trx_Ids INTO L_Ar_Trx_Id;
664 I:=0;
665
666 WHILE C_Trx_Ids%FOUND LOOP
667 I:=I+1;
668
669 L_Ar_Trx:=Get_Ar_Trx( P_Ar_Trx_Header_Id => L_Ar_Trx_Id
670 , P_Org_Id => P_Org_Id
671 , p_fp_tax_reg_num => p_fp_tax_reg_num
672 , P_Gt_Inv_Date_From => l_Gt_Inv_Date_From
673 , P_Gt_Inv_Date_To => l_Gt_Inv_Date_To
674 , P_Gt_Inv_Num_From => l_Gt_Inv_Num_From
675 , P_Gt_Inv_Num_To => l_Gt_Inv_Num_To
676 );
677 L_Gt_Invoices:=Get_Gt_Trxs( P_Ar_Trx_Header_Id => L_Ar_Trx_Id
678 , P_Org_Id => p_Org_Id
679 , p_Tax_Registration_Number => p_fp_tax_reg_num
680 , P_Gt_Inv_Date_From => l_Gt_Inv_Date_From
681 , P_Gt_Inv_Date_To => l_Gt_Inv_Date_To
682 , P_Gt_Inv_Num_From => l_Gt_Inv_Num_From
683 , P_Gt_Inv_Num_To => l_Gt_Inv_Num_To
684 );
685
686 SELECT
687 Xmlconcat(l_ar_trxs,Xmlelement( "Invoice"
688 , Xmlconcat(L_Ar_Trx,L_Gt_Invoices)))
689 INTO
690 l_ar_trxs
691 FROM
692 dual;
693
694 FETCH C_Trx_Ids INTO L_Ar_Trx_Id;
695 END LOOP;
696
697 CLOSE C_Trx_Ids;
698
699 --Get Ar Invoice Rows
700 L_Ar_Rows:=I;
701
702 IF (L_Ar_Rows=0) AND
703 (l_Gt_Rows=0)
704 THEN
705 l_no_data_flag:='Y';
706 END IF;
707
708 --Generate Parameter Section
709 SELECT
710 Xmlelement("Parameters",Xmlforest
711 ( ar_gta_trx_util.Get_OperatingUnit(P_Org_Id) AS "OperationUnit"
712 , p_fp_tax_reg_num AS "TaxRegistrationNumber"
713 , ar_gta_trx_util.Get_AR_Batch_Source_Name
714 ( P_Org_Id
715 , P_Trx_Source) AS "TransactionSource"
716 , ar_gta_trx_util.Get_Customer_Name(P_Customer_Id) AS "ARCustomerName"
717 , P_Gt_Inv_Num_From AS "GTInvoiceNumFrom"
718 , P_Gt_Inv_Num_To AS "GTInvoiceNumTo"
719 , ar_gta_trx_util.To_Xsd_Date_String(P_Gt_Inv_Date_From) AS "GTDateFrom"
720 , ar_gta_trx_util.To_Xsd_Date_String(P_Gt_Inv_Date_To) AS "GTDateTo"
721 , P_Ar_Inv_Num_From AS "ARTrxNumberFrom"
722 , P_Ar_Inv_Num_To AS "ARTrxNumberTo"
723 , ar_gta_trx_util.To_Xsd_Date_String(P_Ar_Inv_Date_From) AS "ARTrxDateFrom"
724 , ar_gta_trx_util.To_Xsd_Date_String(P_Ar_Inv_Date_TO) AS "ARTrxDateTo"))
725 INTO
726 l_Parameter
727 FROM DUAL;
728
729 --Generate Summary Section
730 SELECT
731 Xmlelement("Summary", Xmlforest
732 ( L_Ar_Rows AS "NumOfARTrxs"
733 , L_Gt_Rows AS "NumOfGTInvoices"
734 )
735 )
736 INTO
737 L_Summary
738 FROM
739 DUAL;
740
741 --Generate Golden Tax Currency Section
742 OPEN C_cur;
743 FETCH C_cur INTO l_gt_cur;
744 CLOSE C_cur;
745
746 SELECT
747 Xmlelement("RepCurr", l_gt_cur)
748 INTO
749 L_gt_currency
750 FROM
751 DUAL;
752
753 --Generate Reports Xml Data
754 IF l_no_data_flag='Y'
755 THEN
756 FND_MESSAGE.SET_NAME('AR','AR_GTA_NO_DATA_FOUND');
757 l_no_data_message := FND_MESSAGE.GET();
758
759 SELECT Xmlelement( "MappingReport", Xmlconcat
760 ( Xmlelement("ReportFailed",'N')
761 , Xmlelement("FailedWithParameters",'Y')
762 , Xmlelement("FailedMsgWithParameters",l_no_data_message)
763 , Xmlelement("RepDate",ar_gta_trx_util.To_Xsd_Date_String(SYSDATE))
764 , L_Parameter
765 ))
766 INTO
767 L_Report
768 FROM
769 DUAL;
770 ELSE
771
772 SELECT Xmlelement( "MappingReport", Xmlconcat
773 ( Xmlelement("ReportFailed",'N')
774 , Xmlelement("FailedWithParameters",'N')
775 , Xmlelement("RepDate",ar_gta_trx_util.To_Xsd_Date_String(SYSDATE))
776 , L_Parameter
777 , L_Summary
778 , L_gt_currency
779 , xmlelement("Invoices",l_ar_trxs)
780 ))
781 INTO
782 L_Report
783 FROM
784 DUAL;
785 END IF;
786
787 ar_gta_trx_util.output_conc(L_Report.Getclobval());
788
789 --logging for debug
790 IF (l_proc_level>=l_dbg_level)
791 THEN
792 FND_LOG.string( l_proc_level
793 , G_MODULE_PREFIX||l_procedure_name||'.end'
794 , 'end procedure'
795 );
796 END IF;
797
798 EXCEPTION
799 WHEN OTHERS THEN
800 IF(Fnd_Log.Level_Unexpected >= Fnd_Log.G_Current_Runtime_Level)
801 THEN
802 Fnd_Log.String( Fnd_Log.Level_Unexpected
803 , G_MODULE_PREFIX || l_procedure_name || '.Other_Exception '
804 , Sqlcode||Sqlerrm);
805 END IF;
806
807 END Generate_Mapping_Rep;
808
809 --==========================================================================
810 -- PROCEDURE NAME:
811 --
812 -- Compare_Header Public
813 --
814 -- DESCRIPTION:
815 --
816 -- This Procedure Compare Ar, Gta, Gt Headers AND Input Difference Record
817 -- Compared Columns Include: "Amount", "Tax Amount", "Customer Name",
818 -- "Bank Name Account" and "Tax Payer Id"
819 --
820 -- PARAMETERS:
821 -- In: p_org_id Operating unit id
822 -- p_ar_header_id AR Transaction id
823 --
824 -- Out: x_has_difference
825 --
826 --
827 -- DESIGN REFERENCES:
828 -- GTA_REPORTS_TD.doc
829 --
830 -- CHANGE HISTORY:
831 --
832 -- 05/17/05 Donghai Wang Created.
833 -- 11/25/05 Donghai Wang modify code to follow ebtax
834 -- requirement
835 -- 30/12/05 Donghai Wang Update cursor c_ar_header to
836 -- modify table names
837 -- 03/04/05 Donghai Wang Add FND Log
838 -- 07/05/07 Allen Yang Modify code to fix bug 6147067
839 -- 01/02/08 Subba Updated code for R12.1
840 -- 05-Aug-2009 Yao Zhang Fix bug#8765631 Modified.
841 -- 11-Jan-2011 Qiong Liu Fix bug#10638369
842 --===========================================================================
843 PROCEDURE Compare_Header
844 ( p_org_id IN NUMBER
845 , p_ar_header_id IN NUMBER
846 , x_has_difference OUT NOCOPY BOOLEAN
847 )
848 IS
849 l_ar_header_id NUMBER
850 :=p_ar_header_id;
851
852 l_org_id hr_all_organization_units.organization_id%TYPE
853 :=p_org_id;
854
855 l_ar_amount NUMBER;
856 l_ar_amount_disp VARCHAR2(50);
857 l_ar_taxamount NUMBER;
858 l_ar_taxamount_disp VARCHAR2(50);
859 l_ar_customer_id ra_customer_trx_all.bill_to_customer_id%TYPE;
860 l_ar_customer_name hz_parties.party_name%TYPE;
861 l_ar_taxpayer_id hz_parties.jgzz_fiscal_code%TYPE;
862 l_ar_customer_bank_account VARCHAR2(360);
863 l_ar_customer_address VARCHAR2(4000);
864 l_ar_customer_address_phone VARCHAR2(4000);
865 l_ar_customer_phone hz_contact_points.phone_number%TYPE;
866 l_gta_amount NUMBER;
867 l_gta_taxamount NUMBER;
868 l_gta_customer_name ar_gta_trx_headers_all.bill_to_customer_name%TYPE;
869 l_gta_taxpayer_id ar_gta_trx_headers_all.tp_tax_registration_number%TYPE;
870 l_gta_customer_bank_account ar_gta_trx_headers_all.bank_account_name_number%TYPE;
871 l_gta_customer_address_phone ar_gta_trx_headers_all.customer_address_phone%TYPE;
872 l_gta_trx_number ar_gta_trx_headers_all.gta_trx_number%TYPE;
873 l_gta_amount_sum NUMBER;
874 l_gta_amount_sum_disp VARCHAR2(50);
875 l_gta_taxamount_sum NUMBER;
876 l_gta_taxamount_sum_disp VARCHAR2(50);
877 l_gta_trx_number_con VARCHAR2(4000);
878 l_gt_amount NUMBER;
879 l_gt_taxamount NUMBER;
880 l_gt_customer_name ar_gta_trx_headers_all.bill_to_customer_name%TYPE;
881 l_gt_taxpayer_id ar_gta_trx_headers_all.tp_tax_registration_number%TYPE;
882 l_gt_customer_bank_account ar_gta_trx_headers_all.bank_account_name_number%TYPE;
883 l_gt_customer_address_phone ar_gta_trx_headers_all.customer_address_phone%TYPE;
884 l_gt_amount_sum NUMBER;
885 l_gt_amount_sum_disp VARCHAR2(50);
886 l_gt_taxamount_sum NUMBER;
887 l_gt_taxamount_sum_disp VARCHAR2(50);
888 l_gt_invoice_number ar_gta_trx_headers_all.gt_invoice_number%TYPE;
889 l_gt_invoice_number_con VARCHAR2(4000);
890 l_gta_header_id NUMBER;
891 l_ar_mask_bank VARCHAR2(50);
892 l_amount_discrepancy VARCHAR2(40);
893 l_taxamount_discrepancy VARCHAR2(40);
894 l_has_difference BOOLEAN;
895
896 l_amount_attr fnd_lookup_values.meaning%TYPE;
897 l_taxamount_attr fnd_lookup_values.meaning%TYPE;
898 l_cust_name_attr fnd_lookup_values.meaning%TYPE;
899 l_bank_name_account_attr fnd_lookup_values.meaning%TYPE;
900 l_address_phone_attr fnd_lookup_values.meaning%TYPE;
901 l_taxpayer_id_attr fnd_lookup_values.meaning%TYPE;
902
903 l_ar_customer_bank_account_m VARCHAR2(360);
904 l_gta_customer_bank_account_m ar_gta_trx_headers_all.bank_account_name_number%TYPE;
905 l_gt_customer_bank_account_m ar_gta_trx_headers_all.bank_account_name_number%TYPE;
906 l_ar_bank_name ce_bank_branches_v.bank_name%TYPE;
907 l_ar_branch_name ce_bank_branches_v.bank_branch_name%TYPE;
908 l_ar_bank_account_num ce_bank_accounts.bank_account_num%TYPE;
909 l_ar_bank_account_name ce_bank_accounts.bank_account_name%TYPE;
910
911 l_api_name VARCHAR2(50):='Compare_Header';
912 l_dbg_msg VARCHAR2(100);
913 l_error_msg VARCHAR2(4000);
914
915 l_gta_loop_count NUMBER;
916
917 --Added by Allen to fix issue #1 & #2 in bug 6147067
918 l_gta_amount_loop_count NUMBER;
919
920 --Added by Subba for R12.1
921
922 l_gta_invoice_type VARCHAR2(1);
923 l_gta_invoice_type_name VARCHAR2(80);
924 l_gt_invoice_type VARCHAR2(1);
925 l_gt_invoice_type_name VARCHAR2(80);
926
927 l_invoicetype_attr fnd_lookup_values.meaning%TYPE;
928 l_gta_status VARCHAR2(15);--Yao Zhang add for bug#8765631
929 l_csldt_flag VARCHAR2(1);
930
931
932 CURSOR c_ar_header IS
933 SELECT
934 --commented by Donghai due to ebtax functionality
935 /*AR_GTA_TRX_UTIL.Get_Arinvoice_Amount(rct.customer_trx_id
936 ,rct.invoice_currency_code
937 ,rct.trx_date
938 ,l_org_id) amount
939 , AR_GTA_TRX_UTIL.Get_Arinvoice_Tax_Amount(rct.customer_trx_id
940 ,rct.invoice_currency_code
941 ,rct.trx_date
942 ,l_org_id) tax_amount*/
943 rct.bill_to_customer_id
944 --Yao Zhang modified for bug#8765631
945 ,decode(RAC_BILL_PARTY.Known_As
946 ,null,RAC_BILL_PARTY.PARTY_NAME
947 ,RAC_BILL_PARTY.Known_As) customer_name
948 --commented by Donghai due to ebtax functionality
949 --, rac_bill_party.jgzz_fiscal_code taxpayer_id
950 , DECODE(RAA_BILL.CUST_ACCT_SITE_ID,
951 NULL,
952 NULL,
953 decode(RAA_BILL_LOC.Address_Lines_Phonetic,
954 null,
955 ARH_ADDR_PKG.ARXTW_FORMAT_ADDRESS(RAA_BILL_LOC.ADDRESS_STYLE,
956 RAA_BILL_LOC.ADDRESS1,
957 RAA_BILL_LOC.ADDRESS2,
958 RAA_BILL_LOC.ADDRESS3,
959 RAA_BILL_LOC.ADDRESS4,
960 RAA_BILL_LOC.CITY,
961 RAA_BILL_LOC.COUNTY,
962 RAA_BILL_LOC.STATE,
963 RAA_BILL_LOC.PROVINCE,
964 RAA_BILL_LOC.POSTAL_CODE,
965 FT_BILL.TERRITORY_SHORT_NAME),
966 RAA_BILL_LOC.Address_Lines_Phonetic)) customer_address
967 FROM
968 ra_customer_trx_all rct
969 ,hz_parties rac_bill_party
970 ,hz_cust_accounts rac_bill
971 --,ap_bank_accounts apba
972 --,ap_bank_branches apb
973 ,hz_cust_site_uses_all su_bill
974 ,hz_party_sites raa_bill_ps
975 ,hz_cust_acct_sites_all raa_bill
976 ,hz_locations raa_bill_loc
977 ,fnd_territories_vl ft_bill
978 WHERE rct.customer_trx_id=l_ar_header_id
979 AND rct.bill_to_customer_id=rac_bill.cust_account_id
980 AND rac_bill.party_id=rac_bill_party.party_id
981 -- AND rct.customer_bank_account_id=apba.bank_account_id(+)
982 -- AND apba.bank_branch_id=apb.bank_branch_id(+)
983 AND rct.bill_to_site_use_id=su_bill.site_use_id
984 AND su_bill.cust_acct_site_id=raa_bill.cust_acct_site_id
985 AND raa_bill.party_site_id=raa_bill_ps.party_site_id
986 AND raa_bill_loc.location_id=raa_bill_ps.location_id
987 AND raa_bill_loc.country=ft_bill.territory_code(+);
988
989 -- Commented by Allen to fix issue #2 in bug 6147067
990 CURSOR c_gta_headers IS
991 SELECT
992 /*AR_GTA_TRX_UTIL.Get_Gtainvoice_Amount(gta.gta_trx_header_id)
993 amount
994 , AR_GTA_TRX_UTIL.Get_Gtainvoice_Tax_Amount(gta.gta_trx_header_id)
995 taxamount,
996 */
997 gta.bill_to_customer_name
998 customer_name
999 , gta.tp_tax_registration_number
1000 tax_registration_number
1001 , gta.bank_account_name_number
1002 customer_bank_account
1003 , gta.customer_address_phone
1004 customer_address_phone
1005 , gta.gta_trx_number
1006 ,gta.invoice_type invoice_type --added by subba.
1007 ,lk.meaning invoice_type_name --added by subba.
1008 ,gta.status--Yao Zhang add for bug#8765631
1009 FROM
1010 ar_gta_trx_headers gta, fnd_lookup_values_vl lk --added by subba.
1011 WHERE gta.ra_trx_id=l_ar_header_id
1012 AND gta.source='AR'
1013 AND (gta.status='COMPLETED' OR gta.status='CONSOLIDATED')--Yao Zhang modified for bug#8765631
1014 AND gta.latest_version_flag='Y'
1015 AND gta.invoice_type = lk.lookup_code --added by subba for R12.1
1016 AND lk.lookup_type='AR_GTA_INVOICE_TYPE';
1017
1018
1019 CURSOR c_gt_headers IS
1020 SELECT
1021 AR_GTA_TRX_UTIL.Get_Gtainvoice_Amount(gt.gta_trx_header_id)
1022 amount
1023 , AR_GTA_TRX_UTIL.Get_Gtainvoice_Tax_Amount(gt.gta_trx_header_id)
1024 taxamount
1025 , gt.bill_to_customer_name
1026 customer_name
1027 , gt.tp_tax_registration_number
1028 tax_registration_number
1029 , gt.bank_account_name_number
1030 customer_bank_account
1031 , gt.customer_address_phone
1032 customer_address_phone
1033 , gt.gt_invoice_number
1034 , gt.invoice_type invoice_type
1035 , lk.meaning invoice_type_name
1036 FROM
1037 ar_gta_trx_headers gt,
1038 fnd_lookup_values_vl lk --added by Subba for R12.1
1039 WHERE gt.gta_trx_number=l_gta_trx_number
1040 AND gt.source='GT'
1041 AND gt.invoice_type = lk.lookup_code
1042 AND lk.lookup_type='AR_GTA_INVOICE_TYPE';
1043
1044
1045 --This cursor is added by Allen to fix issue #2 in bug 6147067.
1046 CURSOR c_gta_amounts IS
1047 SELECT
1048 AR_GTA_TRX_UTIL.Get_Gtainvoice_Amount(gta.gta_trx_header_id)
1049 amount
1050 , AR_GTA_TRX_UTIL.Get_Gtainvoice_Tax_Amount(gta.gta_trx_header_id)
1051 taxamount
1052 , gta.gta_trx_number
1053 FROM
1054 ar_gta_trx_headers gta
1055 WHERE gta.ra_trx_id=l_ar_header_id
1056 AND gta.source='AR'
1057 AND gta.status<>'CANCELLED'
1058 AND gta.latest_version_flag='Y';
1059
1060
1061 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1062 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1063
1064 BEGIN
1065
1066 --log for debug
1067 IF( l_proc_level >= l_dbg_level)
1068 THEN
1069
1070 FND_LOG.String(l_proc_level
1071 ,G_MODULE_PREFIX||'.'||l_api_name||'.begin'
1072 ,'Enter procedure'
1073 );
1074
1075
1076 FND_LOG.String(l_proc_level
1077 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
1078 ,'l_ar_header_id '||l_ar_header_id
1079 );
1080
1081 FND_LOG.String(l_proc_level
1082 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
1083 ,'l_org_id '||l_org_id
1084 );
1085
1086
1087
1088 END IF; --( l_proc_level >= l_dbg_level)
1089
1090
1091 l_ar_mask_bank:=FND_PROFILE.Value('CE_MASK_INTERNAL_BANK_ACCT_NUM');
1092
1093 --log for debug
1094 IF( l_proc_level >= l_dbg_level)
1095 THEN
1096 FND_LOG.String(l_proc_level
1097 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1098 ,'l_ar_mask_bank '||l_ar_mask_bank
1099 );
1100 END IF; --( l_proc_level >= l_dbg_level)
1101
1102
1103
1104 l_has_difference:=FALSE;
1105
1106 -- To get meaning of header level attribute lookup code
1107 SELECT
1108 flv.meaning
1109 INTO
1110 l_amount_attr
1111 FROM
1112 fnd_lookup_values_vl flv
1113 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
1114 AND flv.lookup_code='AMOUNT';
1115
1116 SELECT
1117 flv.meaning
1118 INTO
1119 l_taxamount_attr
1120 FROM
1121 fnd_lookup_values_vl flv
1122 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
1123 AND flv.lookup_code='VAT_TAX_AMOUNT';
1124
1125 SELECT
1126 flv.meaning
1127 INTO
1128 l_cust_name_attr
1129 FROM
1130 fnd_lookup_values_vl flv
1131 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
1132 AND flv.lookup_code='CUSTOMER_NAME';
1133
1134 SELECT
1135 flv.meaning
1136 INTO
1137 l_bank_name_account_attr
1138 FROM
1139 fnd_lookup_values_vl flv
1140 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
1141 AND flv.lookup_code='BANK_NAME_ACCOUNT';
1142
1143 SELECT
1144 flv.meaning
1145 INTO
1146 l_address_phone_attr
1147 FROM
1148 fnd_lookup_values_vl flv
1149 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
1150 AND flv.lookup_code='ADDRESS_PHONE_NUMBER';
1151
1152 --added by Subba for R12.1
1153
1154 SELECT
1155 flv.meaning
1156 INTO
1157 l_invoicetype_attr
1158 FROM
1159 fnd_lookup_values_vl flv
1160 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
1161 AND flv.lookup_code='INVOICE_TYPE';
1162
1163
1164 --commented by Donghai due to ebtax functionality
1165 /* SELECT
1166 flv.meaning
1167 INTO
1168 l_taxpayer_id_attr
1169 FROM
1170 fnd_lookup_values_vl flv
1171 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
1172 AND flv.lookup_code='TAX_PAYER_ID';*/
1173
1174
1175
1176
1177 --Get AR Attribute Value
1178 OPEN c_ar_header;
1179
1180 --commented by Donghai due to ebtax functionality
1181 FETCH c_ar_header
1182 INTO --l_ar_amount
1183 --,l_ar_taxamount
1184 l_ar_customer_id
1185 ,l_ar_customer_name
1186 -- ,l_ar_taxpayer_id
1187 ,l_ar_customer_address
1188 ;
1189
1190 CLOSE c_ar_header;
1191
1192 --log for debug
1193 IF( l_proc_level >= l_dbg_level)
1194 THEN
1195 FND_LOG.String(l_proc_level
1196 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1197 ,'l_ar_customer_id '||l_ar_customer_id
1198 );
1199
1200 FND_LOG.String(l_proc_level
1201 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1202 ,'l_ar_customer_name '||l_ar_customer_name
1203 );
1204
1205 FND_LOG.String(l_proc_level
1206 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1207 ,'l_ar_customer_address '||l_ar_customer_address
1208 );
1209
1210
1211 END IF; --( l_proc_level >= l_dbg_level)
1212
1213
1214
1215 --Get taxable amount and tax amount of AR transaction
1216 --First check if current AR transaction have AR lines with multiple VAT tax
1217 --lines per GT currency code and VAT tax type defined on GTA 'system
1218 --options form.If 'Yes', then will set NULL to the variables, if 'No',
1219 --then return exact taxable amount and tax amount
1220
1221 IF AR_GTA_TRX_UTIL.Check_Taxcount_Of_Artrx
1222 (p_org_id => l_org_id
1223 ,p_customer_trx_id => l_ar_header_id
1224 )
1225 THEN
1226 l_ar_amount:=AR_GTA_TRX_UTIL.Get_Arinvoice_Amount
1227 (p_org_id => l_org_id
1228 ,p_customer_trx_id => l_ar_header_id
1229 );
1230
1231 l_ar_taxamount:=AR_GTA_TRX_UTIL.Get_Arinvoice_Tax_Amount
1232 (p_org_id => l_org_id
1233 ,p_customer_trx_id => l_ar_header_id
1234 );
1235 ELSE
1236 l_ar_amount:='';
1237 l_ar_taxamount:='';
1238 END IF; --AR_GTA_TRX_UTIL.Check_Taxcount_Of_Artrx.....
1239
1240 --log for debug
1241 IF( l_proc_level >= l_dbg_level)
1242 THEN
1243 FND_LOG.String(l_proc_level
1244 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1245 ,'l_ar_amount '||l_ar_amount
1246 );
1247
1248 FND_LOG.String(l_proc_level
1249 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1250 ,'l_ar_taxamount '||l_ar_taxamount
1251 );
1252
1253
1254 END IF; --( l_proc_level >= l_dbg_level)
1255
1256
1257
1258
1259 --Get Bank account name and number of a specific AR transaction
1260 AR_GTA_TRX_UTIL.Get_Bank_Info
1261 (p_customer_trx_id => l_ar_header_id
1262 ,p_org_id => l_org_id
1263 ,x_bank_name => l_ar_bank_name
1264 ,x_bank_branch_name => l_ar_branch_name
1265 ,x_bank_account_name => l_ar_bank_account_name
1266 ,x_bank_account_num => l_ar_bank_account_num
1267 );
1268 l_ar_customer_bank_account:=l_ar_bank_name||' '||
1269 l_ar_branch_name||' '||l_ar_bank_account_num;
1270
1271
1272 l_ar_customer_phone:=AR_GTA_TRX_UTIL.Get_Primary_Phone_Number
1273 (p_customer_id => l_ar_customer_id
1274 );
1275
1276
1277
1278 --To generate ar customer address phone
1279 IF l_ar_customer_phone IS NOT NULL
1280 THEN
1281 l_ar_customer_address_phone:=l_ar_customer_address||' '||
1282 l_ar_customer_phone;
1283 ELSE
1284 l_ar_customer_address_phone:=l_ar_customer_address;
1285 END IF; --l_ar_customer_phone IS NOT NULL
1286
1287 --log for debug
1288 IF( l_proc_level >= l_dbg_level)
1289 THEN
1290 FND_LOG.String(l_proc_level
1291 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1292 ,'l_ar_customer_bank_account '||l_ar_customer_bank_account
1293 );
1294
1295 FND_LOG.String(l_proc_level
1296 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1297 ,'l_ar_customer_address_phone '||l_ar_customer_address_phone
1298 );
1299
1300
1301 END IF; --( l_proc_level >= l_dbg_level)
1302
1303 --Added by Allen to fix issue #2 in bug 6147067
1304 --To accumulate GTA invoice amount, tax amount and concatenate GTA invoice number.
1305
1306 l_gta_amount_sum:=0;
1307 l_gta_taxamount_sum:=0;
1308 l_gta_trx_number_con:='';
1309 l_gta_amount_loop_count:=0;
1310
1311 OPEN c_gta_amounts;
1312 FETCH c_gta_amounts
1313 INTO l_gta_amount
1314 ,l_gta_taxamount
1315 ,l_gta_trx_number
1316 ;
1317
1318 WHILE c_gta_amounts%FOUND
1319 LOOP
1320 l_gta_amount_loop_count:=l_gta_amount_loop_count+1;
1321
1322 --Log for debug
1323 IF( l_proc_level >= l_dbg_level)
1324 THEN
1325 FND_LOG.String(l_proc_level
1326 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1327 ,'l_gta_amount_loop_count '||l_gta_amount_loop_count||','
1328 ||'l_gta_amount_sum '||l_gta_amount_sum||','
1329 ||'l_gta_taxamount_sum '||l_gta_taxamount_sum||','
1330 ||'l_gta_trx_number_con '||l_gta_trx_number_con
1331 );
1332 END IF; --( l_proc_level >= l_dbg_level)
1333
1334 --To accumulate GTA invoice amount
1335 l_gta_amount_sum:=l_gta_amount_sum+l_gta_amount;
1336
1337 --To accumulate GTA invoice tax amount
1338 l_gta_taxamount_sum:=l_gta_taxamount_sum+l_gta_taxamount;
1339
1340 --To concatenate GTA invoice number
1341 IF (l_gta_trx_number_con IS NULL)
1342 THEN
1343 l_gta_trx_number_con:=l_gta_trx_number;
1344 ELSE
1345 l_gta_trx_number_con:=l_gta_trx_number_con||','||l_gta_trx_number;
1346 END IF; --(l_gta_trx_number_con IS NULL)
1347
1348 FETCH c_gta_amounts
1349 INTO l_gta_amount
1350 ,l_gta_taxamount
1351 ,l_gta_trx_number
1352 ;
1353
1354 END LOOP; --c_gta_amounts%FOUND
1355 CLOSE c_gta_amounts;
1356
1357
1358 --compare AR header with GTA header and GT header
1359
1360 --Commented by Allen to fix issue #2 in bug 6147067
1361 --l_gta_amount_sum:=0;
1362 --l_gta_taxamount_sum:=0;
1363 --l_gta_trx_number_con:='';
1364 l_gt_amount_sum:=0;
1365 l_gt_taxamount_sum:=0;
1366 l_gt_invoice_number_con:='';
1367
1368 l_gta_loop_count:=0;
1369
1370 OPEN c_gta_headers;
1371 FETCH c_gta_headers
1372 INTO --l_gta_amount
1373 --,l_gta_taxamount,
1374 l_gta_customer_name
1375 ,l_gta_taxpayer_id
1376 ,l_gta_customer_bank_account
1377 ,l_gta_customer_address_phone
1378 ,l_gta_trx_number
1379 ,l_gta_invoice_type --added by subba for R12.1
1380 ,l_gta_invoice_type_name
1381 ,l_gta_status--Yao Zhang add for bug#8765631
1382 ;
1383
1384 WHILE c_gta_headers%FOUND
1385 LOOP
1386
1387 l_gta_loop_count:=l_gta_loop_count+1;
1388 IF(l_csldt_flag IS NULL AND l_gta_status='CONSOLIDATED')
1389 THEN
1390 l_csldt_flag:='Y';
1391 END IF;
1392
1393 --log for debug
1394 IF( l_proc_level >= l_dbg_level)
1395 THEN
1396 FND_LOG.String(l_proc_level
1397 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1398 ,'l_gta_loop_count '||l_gta_loop_count
1399 );
1400
1401 /*Commented by Allen to fix issue #2 in bug 6147067
1402 FND_LOG.String(l_proc_level
1403 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1404 ,'l_gta_amount '||l_gta_amount
1405 );
1406
1407 FND_LOG.String(l_proc_level
1408 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1409 ,'l_gta_taxamount '||l_gta_taxamount
1410 );
1411 */
1412
1413 FND_LOG.String(l_proc_level
1414 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1415 ,'l_gta_customer_name '||l_gta_customer_name
1416 );
1417
1418 FND_LOG.String(l_proc_level
1419 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1420 ,'l_gta_taxpayer_id '||l_gta_taxpayer_id
1421 );
1422
1423 FND_LOG.String(l_proc_level
1424 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1425 ,'l_gta_customer_bank_account '||
1426 l_gta_customer_bank_account
1427 );
1428
1429 FND_LOG.String(l_proc_level
1430 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1431 ,'l_gta_customer_address_phone '||
1432 l_gta_customer_address_phone
1433 );
1434
1435 FND_LOG.String(l_proc_level
1436 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1437 ,'l_gta_trx_number '||l_gta_trx_number
1438 );
1439 --added by subba for R12.1
1440
1441 FND_LOG.String(l_proc_level
1442 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1443 ,'l_gta_invoice_type '||l_gta_invoice_type
1444 );
1445
1446
1447 END IF; --( l_proc_level >= l_dbg_level)
1448
1449 --To get related GT invoice to current GTA invoice
1450 --Yao Zhang add for bug#8765631
1451 l_gt_amount:=NULL;
1452 l_gt_taxamount:=NULL;
1453 l_gt_customer_name:=NULL;
1454 l_gt_taxpayer_id:=NULL;
1455 l_gt_customer_bank_account:=NULL;
1456 l_gt_customer_address_phone:=NULL;
1457 l_gt_invoice_number:=NULL;
1458 l_gt_invoice_type:=NULL;
1459 l_gt_invoice_type_name:=NULL;
1460 --yao zhang add end
1461 OPEN c_gt_headers;
1462 FETCH c_gt_headers
1463 INTO l_gt_amount
1464 ,l_gt_taxamount
1465 ,l_gt_customer_name
1466 ,l_gt_taxpayer_id
1467 ,l_gt_customer_bank_account
1468 ,l_gt_customer_address_phone
1469 ,l_gt_invoice_number
1470 ,l_gt_invoice_type --added by subba for R12.1
1471 ,l_gt_invoice_type_name
1472 ;
1473 CLOSE c_gt_headers;
1474
1475 IF( l_proc_level >= l_dbg_level)
1476 THEN
1477
1478 FND_LOG.String(l_proc_level
1479 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1480 ,'l_gt_amount '||l_gt_amount
1481 );
1482
1483 FND_LOG.String(l_proc_level
1484 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1485 ,'l_gt_taxamount '||l_gt_taxamount
1486 );
1487
1488 FND_LOG.String(l_proc_level
1489 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1490 ,'l_gt_customer_name '||l_gt_customer_name
1491 );
1492
1493 FND_LOG.String(l_proc_level
1494 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1495 ,'l_gt_taxpayer_id '||l_gt_taxpayer_id
1496 );
1497
1498 FND_LOG.String(l_proc_level
1499 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1500 ,'l_gt_customer_bank_account '||
1501 l_gt_customer_bank_account
1502 );
1503
1504 FND_LOG.String(l_proc_level
1505 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1506 ,'l_gt_customer_address_phone '||
1507 l_gt_customer_address_phone
1508 );
1509
1510 FND_LOG.String(l_proc_level
1511 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1512 ,'l_gt_invoice_number '||l_gt_invoice_number
1513 );
1514
1515 FND_LOG.String(l_proc_level
1516 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
1517 ,'l_gt_invoice_type '||l_gt_invoice_type
1518 );
1519
1520
1521 END IF; --( l_proc_level >= l_dbg_level)
1522
1523
1524 --Commented by Allen to fix issue #2 in bug 6147067
1525
1526 --To accumulate GTA invoice amount and tax amount
1527 --l_gta_amount_sum:=l_gta_amount_sum+l_gta_amount;
1528 --l_gta_taxamount_sum:=l_gta_taxamount_sum+l_gta_taxamount;
1529
1530 --To accumulate GT invoice amount and tax amount
1531 l_gt_amount_sum:=l_gt_amount_sum+nvl(l_gt_amount,0);
1532 l_gt_taxamount_sum:=l_gt_taxamount_sum+nvl(l_gt_taxamount,0);
1533
1534 --To concatenate GTA invoice number
1535 /*
1536 IF (l_gta_trx_number_con IS NULL)
1537 THEN
1538 l_gta_trx_number_con:=l_gta_trx_number;
1539 ELSE
1540 l_gta_trx_number_con:=l_gta_trx_number_con||','||l_gta_trx_number;
1541 END IF; --(l_gta_trx_number_con IS NULL)
1542 */
1543
1544 --To concatenate GT invoice number
1545 IF (l_gt_invoice_number_con IS NULL)
1546 THEN
1547 l_gt_invoice_number_con:=l_gt_invoice_number;
1548 ELSE
1549 l_gt_invoice_number_con:=l_gt_invoice_number_con||','||
1550 l_gt_invoice_number;
1551 END IF; --(l_gt_invoice_number_con IS NULL)
1552
1553 --To compare customer name
1554
1555 --Updated by Allen to fix issue#1 in bug 6147067
1556 --Add trim() function to avoid the discrepancy caused by space character
1557 l_ar_customer_name:=trim(l_ar_customer_name);
1558 l_gta_customer_name:=trim(l_gta_customer_name);
1559 l_gt_customer_name:=trim(l_gt_customer_name);
1560 /*fnd_file.PUT_LINE(fnd_file.LOG,'l_ar_customer_name:'||l_ar_customer_name);
1561 -- fnd_file.PUT_LINE(fnd_file.LOG,'l_gta_customer_name:'||l_gta_customer_name);
1562 -- fnd_file.PUT_LINE(fnd_file.LOG,'l_gt_customer_name'||l_gt_customer_name);*/
1563 IF (nvl(l_ar_customer_name,' ')<>nvl(l_gta_customer_name,' ')) OR
1564 --Yao Zhang Modified for bug#8765631
1565 ((nvl(l_ar_customer_name,' ')<>nvl(l_gt_customer_name,' '))AND l_gta_status='COMPLETED')
1566 THEN
1567
1568 --Insert this discrepancy record to temp table ar_gta_difference_temp
1569 INSERT INTO ar_gta_difference_temp(type
1570 ,ar_header_id
1571 ,attribute
1572 ,ar_value
1573 ,gta_invoice_num
1574 ,gta_value
1575 ,gt_invoice_num
1576 ,gt_value
1577 ,discrepancy
1578 )
1579 VALUES('HEADER'
1580 ,l_ar_header_id
1581 ,l_cust_name_attr
1582 ,l_ar_customer_name
1583 ,l_gta_trx_number
1584 ,l_gta_customer_name
1585 ,l_gt_invoice_number
1586 ,l_gt_customer_name
1587 ,'-'
1588 );
1589 l_has_difference:=TRUE;
1590 END IF; --l_ar_customer_name<>l_gta_customer_name) OR ......
1591
1592 --added by subba for R12.1, check to see the discrepancy of Invoice_type
1593
1594 IF (l_gta_invoice_type IS NOT NULL
1595 AND l_gt_invoice_type IS NOT NULL
1596 AND l_gta_invoice_type <> l_gt_invoice_type
1597 AND l_gta_status='COMPLETED')--Yao Zhang add for bug#8765631
1598 THEN
1599
1600 --Insert this discrepancy record to temp table ar_gta_difference_temp
1601
1602 INSERT INTO ar_gta_difference_temp(type
1603 ,ar_header_id
1604 ,attribute
1605 ,ar_value
1606 ,gta_invoice_num
1607 ,gta_value
1608 ,gt_invoice_num
1609 ,gt_value
1610 ,discrepancy )
1611
1612 VALUES('HEADER'
1613 ,l_ar_header_id
1614 ,l_invoicetype_attr
1615 ,l_gta_invoice_type_name
1616 ,l_gta_trx_number
1617 ,l_gta_invoice_type_name
1618 ,l_gt_invoice_number
1619 ,l_gt_invoice_type_name
1620 ,'-'
1621 );
1622
1623 l_has_difference:=TRUE;
1624
1625 END IF; --l_gta_invoice_type IS NOT NULL AND....
1626
1627
1628 --To compare bank name account
1629
1630 --Updated by Allen to fix issue#1 in bug 6147067
1631 --Add trim() function to avoid the discrepancy caused by space character
1632 l_ar_customer_bank_account:=trim(l_ar_customer_bank_account);
1633 l_gta_customer_bank_account:=trim(l_gta_customer_bank_account);
1634 l_gt_customer_bank_account:=trim(l_gt_customer_bank_account);
1635
1636 IF (nvl(l_ar_customer_bank_account,' ')<>nvl(l_gta_customer_bank_account,' ')) OR
1637 ((nvl(l_ar_customer_bank_account,' ')<>nvl(l_gt_customer_bank_account,' ')) AND l_gta_status='COMPLETED')
1638 /*l_ar_customer_bank_account IS NOT NULL AND
1639 l_gta_customer_bank_account IS NOT NULL
1640 ) OR
1641 (l_ar_customer_bank_account IS NULL AND
1642 l_gta_customer_bank_account IS NOT NULL
1643 ) OR
1644 (l_ar_customer_bank_account IS NOT NULL AND
1645 l_gta_customer_bank_account IS NULL
1646 ) OR
1647 (l_ar_customer_bank_account<>l_gt_customer_bank_account AND
1648 l_ar_customer_bank_account IS NOT NULL AND
1649 l_gt_customer_bank_account IS NOT NULL
1650 ) OR
1651 (l_ar_customer_bank_account IS NULL AND
1652 l_gt_customer_bank_account IS NOT NULL
1653 ) OR
1654 (l_ar_customer_bank_account IS NOT NULL AND
1655 l_gt_customer_bank_account IS NULL*/
1656
1657 THEN
1658
1659
1660 IF l_ar_customer_bank_account IS NOT NULL
1661 THEN
1662
1663 --To mask ar bank name account according to
1664 --profile AR_MASK_BANK_ACCOUNT_NUMBERS
1665 SELECT
1666 DECODE( NVL(l_ar_mask_bank, 'FIRST FOUR VISIBLE')
1667 ,'FIRST FOUR VISIBLE', RPAD('*'
1668 ,LENGTH(l_ar_customer_bank_account)
1669 ,'*'
1670 )
1671 ,'LAST FOUR VISIBLE',LPAD('*'
1672 ,LENGTHB(l_ar_customer_bank_account)
1673 , '*'
1674 )
1675 , 'NO MASK',l_ar_customer_bank_account
1676 )
1677 INTO
1678 l_ar_customer_bank_account_m
1679 FROM
1680 dual;
1681 ELSE
1682 l_ar_customer_bank_account_m:='';
1683 END IF; --l_ar_customer_bank_account IS NOT NULL
1684
1685
1686 IF l_gta_customer_bank_account IS NOT NULL
1687 THEN
1688 --To mask gta bank name account according to
1689 --profile AR_MASK_BANK_ACCOUNT_NUMBERS
1690 SELECT
1691 DECODE( NVL(l_ar_mask_bank, 'FIRST FOUR VISIBLE')
1692 , 'FIRST FOUR VISIBLE', RPAD('*'
1693 ,LENGTH(l_gta_customer_bank_account)
1694 , '*'
1695 )
1696 , 'LAST FOUR VISIBLE', LPAD('*'
1697 ,LENGTHB(l_gta_customer_bank_account)
1698 , '*'
1699 )
1700 , 'NO MASK',l_gta_customer_bank_account
1701 )
1702 INTO
1703 l_gta_customer_bank_account_m
1704 FROM
1705 dual;
1706 ELSE
1707 l_gta_customer_bank_account_m:='';
1708 END IF; --l_gta_customer_bank_account IS NOT NULL
1709
1710 IF l_gt_customer_bank_account IS NOT NULL
1711 THEN
1712 --To mask gt bank name account according to
1713 --profile AR_MASK_BANK_ACCOUNT_NUMBERS
1714 SELECT
1715 DECODE( NVL(l_ar_mask_bank, 'FIRST FOUR VISIBLE')
1716 , 'FIRST FOUR VISIBLE', RPAD('*'
1717 ,LENGTH(l_gt_customer_bank_account)
1718 , '*'
1719 )
1720 , 'LAST FOUR VISIBLE', LPAD('*'
1721 ,LENGTHB(l_gt_customer_bank_account)
1722 , '*'
1723 )
1724 , 'NO MASK',l_gt_customer_bank_account
1725 )
1726 INTO
1727 l_gt_customer_bank_account_m
1728 FROM
1729 dual;
1730 ELSE
1731 l_gt_customer_bank_account_m:='';
1732 END IF; --l_gt_customer_bank_account IS NOT NULL
1733
1734
1735 --Insert this discrepancy record to temp table ar_gta_difference_temp
1736 INSERT INTO ar_gta_difference_temp(type
1737 ,ar_header_id
1738 ,attribute
1739 ,ar_value
1740 ,gta_invoice_num
1741 ,gta_value
1742 ,gt_invoice_num
1743 ,gt_value
1744 ,discrepancy
1745 )
1746 VALUES('HEADER'
1747 ,l_ar_header_id
1748 ,l_bank_name_account_attr
1749 ,l_ar_customer_bank_account_m
1750 ,l_gta_trx_number
1751 ,l_gta_customer_bank_account_m
1752 ,l_gt_invoice_number
1753 ,l_gt_customer_bank_account_m
1754 ,'-'
1755 );
1756 l_has_difference:=TRUE;
1757 END IF; --(l_ar_customer_bank_account<>l_gta_customer_bank_account)
1758 --OR (l_ar_customer_bank_account<>l_gt_customer_bank_account)
1759
1760 --To compare address and phone number
1761
1762 --Updated by Allen to fix issue#1 in bug 6147067
1763 --Add trim() function to avoid the discrepancy caused by space character
1764 l_ar_customer_address_phone := trim(l_ar_customer_address_phone);
1765 l_gta_customer_address_phone := trim(l_gta_customer_address_phone);
1766 l_gt_customer_address_phone := trim(l_gt_customer_address_phone);
1767 --Yao modified for bug#8765631
1768 IF (nvl(l_ar_customer_address_phone,' ')<>nvl(l_gta_customer_address_phone,' ')) OR
1769 ((nvl(l_ar_customer_address_phone,' ')<>nvl(l_gt_customer_address_phone,' ')) AND l_gta_status='COMPLETED')
1770 /*l_ar_customer_address_phone IS NOT NULL AND
1771 l_gta_customer_address_phone IS NOT NULL
1772 ) OR
1773 (l_ar_customer_address_phone IS NOT NULL AND
1774 l_gta_customer_address_phone IS NULL
1775 ) OR
1776 (l_ar_customer_address_phone IS NULL AND
1777 l_gta_customer_address_phone IS NOT NULL
1778 ) OR
1779 (l_ar_customer_address_phone<>l_gt_customer_address_phone AND
1780 l_ar_customer_address_phone IS NOT NULL AND
1781 l_gt_customer_address_phone IS NOT NULL
1782 ) OR
1783 (l_ar_customer_address_phone IS NOT NULL AND
1784 l_gt_customer_address_phone IS NULL
1785 ) OR
1786 (l_ar_customer_address_phone IS NULL AND
1787 l_gt_customer_address_phone IS NOT NULL
1788 )*/
1789 THEN
1790
1791
1792 --Insert this discrepancy record to temp table ar_gta_difference_temp
1793
1794 INSERT INTO ar_gta_difference_temp(type
1795 ,ar_header_id
1796 ,attribute
1797 ,ar_value
1798 ,gta_invoice_num
1799 ,gta_value
1800 ,gt_invoice_num
1801 ,gt_value
1802 ,discrepancy
1803 )
1804 VALUES('HEADER'
1805 ,l_ar_header_id
1806 ,l_address_phone_attr
1807 ,l_ar_customer_address_phone
1808 ,l_gta_trx_number
1809 ,l_gta_customer_address_phone
1810 ,l_gt_invoice_number
1811 ,l_gt_customer_address_phone
1812 ,'-'
1813 );
1814 l_has_difference:=TRUE;
1815 END IF; --(l_ar_customer_address_phone<>l_gta_customer_address_phone)
1816 --OR (l_ar_customer_address_phone<>l_gt_customer_address_phone)
1817
1818 --commented by Donghai due to ebtax functionality
1819 --To compare tax payer ID
1820 /* IF (l_ar_taxpayer_id<>l_gta_taxpayer_id AND
1821 l_ar_taxpayer_id IS NOT NULL AND
1822 l_gta_taxpayer_id IS NOT NULL
1823 ) OR
1824 (l_ar_taxpayer_id IS NOT NULL AND
1825 l_gta_taxpayer_id IS NULL
1826 ) OR
1827 (l_ar_taxpayer_id IS NULL AND
1828 l_gta_taxpayer_id IS NOT NULL
1829 ) OR
1830 (l_ar_taxpayer_id<>l_gt_taxpayer_id AND
1831 l_ar_taxpayer_id IS NOT NULL AND
1832 l_gta_taxpayer_id IS NOT NULL
1833 ) OR
1834 (l_ar_taxpayer_id IS NOT NULL AND
1835 l_gta_taxpayer_id IS NULL
1836 ) OR
1837 (l_ar_taxpayer_id IS NULL AND
1838 l_gta_taxpayer_id IS NOT NULL
1839 )
1840 THEN
1841 --Insert this discrepancy record to temp table ar_gta_difference_temp
1842 INSERT INTO ar_gta_difference_temp(type
1843 ,ar_header_id
1844 ,attribute
1845 ,ar_value
1846 ,gta_invoice_num
1847 ,gta_value
1848 ,gt_invoice_num
1849 ,gt_value
1850 ,discrepancy
1851 )
1852 VALUES('HEADER'
1853 ,l_ar_header_id
1854 ,l_taxpayer_id_attr
1855 ,l_ar_taxpayer_id
1856 ,l_gta_trx_number
1857 ,l_gta_taxpayer_id
1858 ,l_gt_invoice_number
1859 ,l_gt_taxpayer_id
1860 ,'-'
1861 );
1862 l_has_difference:=TRUE;
1863 END IF; --(l_ar_taxpayer_id<>l_gta_taxpayer_id)
1864 --OR (l_ar_taxpayer_id<>l_gt_taxpayer_id)
1865 */
1866
1867
1868
1869 --To compare tax registration number on GTA and GT
1870 --If tax registration number on GTA header lever is different from
1871 --that on GT header level,then mark all lines belong to corresponding
1872 --GTA invoice and GT invoice as unmatched,saying 'matched_flag='N'
1873
1874 --Updated by Allen to fix issue#1 in bug 6147067
1875 --Add trim() function to avoid the discrepancy caused by space character
1876
1877 l_gta_taxpayer_id:=trim(l_gta_taxpayer_id);
1878 l_gt_taxpayer_id:=trim(l_gt_taxpayer_id);
1879
1880 IF ((l_gta_taxpayer_id<>l_gt_taxpayer_id)AND l_gta_status='COMPLETED')--Yao Zhang add for bug#8765631
1881 THEN
1882 --update lines belong to GTA invoice
1883 UPDATE
1884 ar_gta_trx_lines_all gta_line
1885 SET
1886 gta_line.matched_flag='N'
1887 WHERE gta_line.gta_trx_header_id=
1888 (SELECT
1889 gta_header.gta_trx_header_id
1890 FROM
1891 ar_gta_trx_headers_all gta_header
1892 WHERE gta_header.source='AR'
1893 AND gta_header.gta_trx_number=l_gta_trx_number
1894 )
1895 AND gta_line.enabled_flag='Y';
1896
1897 --update lines belong to GT invoice
1898 UPDATE ar_gta_trx_lines_all gt_line
1899 SET gt_line.matched_flag='N'
1900 WHERE gt_line.gta_trx_header_id=
1901 (SELECT
1902 gt_header.gta_trx_header_id
1903 FROM
1904 ar_gta_trx_headers_all gt_header
1905 WHERE gt_header.source='GT'
1906 AND gt_header.gta_trx_number=l_gta_trx_number
1907 );
1908 END IF; --(l_gta_taxpayer_id<>l_gt_taxpayer_id)
1909
1910
1911 --Commented by Allen to fix issue #2 in bug 6147067
1912 FETCH c_gta_headers
1913 INTO --l_gta_amount
1914 --,l_gta_taxamount,
1915 l_gta_customer_name
1916 ,l_gta_taxpayer_id
1917 ,l_gta_customer_bank_account
1918 ,l_gta_customer_address_phone
1919 ,l_gta_trx_number
1920 ,l_gta_invoice_type --added by Subba.
1921 ,l_gta_invoice_type_name
1922 ,l_gta_status--Yao Zhang add for bug#8765631
1923 ;
1924
1925 END LOOP; --c_gta_headers%FOUND
1926 CLOSE c_gta_headers;
1927
1928 --compare amount
1929 l_ar_amount:=round(l_ar_amount,2);--Qiong add for bug 10638369
1930 l_gta_amount_sum:=round(l_gta_amount_sum,2);--Qiong add for bug 10638369
1931 IF (nvl(l_ar_amount,0)<>nvl(l_gta_amount_sum,0)) OR
1932 (nvl(l_ar_amount,0)<>nvl(l_gt_amount_sum,0) AND l_csldt_flag IS NULL)--Yao Modified for bug#8765631
1933 THEN
1934
1935 IF l_ar_amount IS NULL
1936 THEN
1937 l_amount_discrepancy:='-';
1938 ELSE
1939 --To compute discrepancy of amount (GT-AR)
1940 l_amount_discrepancy:=AR_GTA_TRX_UTIL.Format_Monetary_Amount(p_org_id => p_org_id
1941 ,p_amount => l_gt_amount_sum-l_ar_amount
1942 );
1943 END IF; --l_ar_amount IS NULL
1944
1945 --Format Amount
1946 l_ar_amount_disp:=AR_GTA_TRX_UTIL.Format_Monetary_Amount(p_org_id => p_org_id
1947 ,p_amount => l_ar_amount
1948 );
1949
1950 l_gta_amount_sum_disp:=AR_GTA_TRX_UTIL.Format_Monetary_Amount(p_org_id => p_org_id
1951 ,p_amount => l_gta_amount_sum
1952 );
1953
1954 l_gt_amount_sum_disp:=AR_GTA_TRX_UTIL.Format_Monetary_Amount(p_org_id => p_org_id
1955 ,p_amount =>l_gt_amount_sum
1956 );
1957
1958 --Insert this discrepancy record to temp table ar_gta_difference_temp
1959 INSERT INTO ar_gta_difference_temp(type
1960 ,ar_header_id
1961 ,attribute
1962 ,ar_value
1963 ,gta_invoice_num
1964 ,gta_value
1965 ,gt_invoice_num
1966 ,gt_value
1967 ,discrepancy
1968 )
1969 VALUES('HEADER'
1970 ,l_ar_header_id
1971 ,l_amount_attr
1972 ,l_ar_amount_disp
1973 ,l_gta_trx_number_con
1974 ,l_gta_amount_sum_disp
1975 ,l_gt_invoice_number_con
1976 ,l_gt_amount_sum_disp
1977 ,l_amount_discrepancy
1978 );
1979 l_has_difference:=TRUE;
1980 END IF; --(l_ar_amount<>l_gta_amount_sum) OR (l_ar_amount<>l_gt_amount_sum)
1981
1982 l_ar_taxamount:=round(l_ar_taxamount,2);--Qiong add for bug 10638369
1983 l_gta_taxamount_sum:=round(l_gta_taxamount_sum,2);--Qiong add for bug 10638369
1984 IF (nvl(l_ar_taxamount,0)<>nvl(l_gta_taxamount_sum,0)) OR
1985 (nvl(l_ar_taxamount,0)<>nvl(l_gt_taxamount_sum,0) AND l_csldt_flag IS NULL)
1986 THEN
1987
1988 IF l_ar_taxamount IS NULL
1989 THEN
1990 l_taxamount_discrepancy:='-';
1991 ELSE
1992 --To compute discrepancy of tax amount (GT-AR)
1993 l_taxamount_discrepancy:=AR_GTA_TRX_UTIL.Format_Monetary_Amount(p_org_id => p_org_id
1994 ,p_amount => l_gt_taxamount_sum-l_ar_taxamount
1995 );
1996 END IF; -- l_ar_taxamount IS NULL
1997
1998 --Format Amount
1999 l_ar_taxamount_disp:=AR_GTA_TRX_UTIL.Format_Monetary_Amount(p_org_id => p_org_id
2000 ,p_amount => l_ar_taxamount
2001 );
2002
2003 l_gta_taxamount_sum_disp:=AR_GTA_TRX_UTIL.Format_Monetary_Amount(p_org_id => p_org_id
2004 ,p_amount => l_gta_taxamount_sum
2005 );
2006
2007 l_gt_taxamount_sum_disp:=AR_GTA_TRX_UTIL.Format_Monetary_Amount(p_org_id => p_org_id
2008 ,p_amount => l_gt_taxamount_sum
2009 );
2010
2011
2012 --Insert this discrepancy record to temp table ar_gta_difference_temp
2013 INSERT INTO ar_gta_difference_temp(type
2014 ,ar_header_id
2015 ,attribute
2016 ,ar_value
2017 ,gta_invoice_num
2018 ,gta_value
2019 ,gt_invoice_num
2020 ,gt_value
2021 ,discrepancy
2022 )
2023 VALUES('HEADER'
2024 ,l_ar_header_id
2025 ,l_taxamount_attr
2026 ,l_ar_taxamount_disp
2027 ,l_gta_trx_number_con
2028 ,l_gta_taxamount_sum_disp
2029 ,l_gt_invoice_number_con
2030 ,l_gt_taxamount_sum_disp
2031 ,l_taxamount_discrepancy
2032 );
2033 l_has_difference:=TRUE;
2034 END IF;--(l_ar_taxamount<>l_gta_tax_amount_sum)
2035 --OR (l_ar_taxamount<>l_gt_taxamount_sum)
2036
2037 x_has_difference:=l_has_difference;
2038
2039 --log for debug
2040 IF( l_proc_level >= l_dbg_level)
2041 THEN
2042 FND_LOG.String(l_proc_level
2043 ,G_MODULE_PREFIX||'.'||l_api_name||'.end'
2044 ,'Exit procedure');
2045
2046
2047 END IF; --( l_proc_level >= l_dbg_level)
2048
2049 EXCEPTION
2050 WHEN OTHERS THEN
2051 IF(l_proc_level >= l_dbg_level)
2052 THEN
2053 l_error_msg:=SQLCODE||':'||SQLERRM;
2054 FND_LOG.String( l_proc_level
2055 , g_module_prefix || l_api_name || '. Other_Exception '
2056 , l_error_msg
2057 );
2058
2059
2060 END IF; --(FND_LOG.Level_Unexpected >= FND_LOG.G_Current_Runtime_Level)
2061
2062 IF c_gta_headers%ISOPEN
2063 THEN
2064 CLOSE c_gta_headers;
2065 END IF;--c_gta_headers%ISOPEN
2066 RAISE;
2067 END Compare_Header;
2068
2069 --==========================================================================
2070 -- PROCEDURE NAME:
2071 --
2072 -- Compare_Lines Public
2073 --
2074 -- DESCRIPTION:
2075 --
2076 -- This Procedure Compare Ar, Gta, Gt Lines And Input Difference Record
2077 -- Compared Columns Include: "Goods Description", "Line Amount",
2078 -- "Vat Line Tax", "Vat Tax Rate", "Quantity", "Unit Price" And "Uom"
2079 --
2080 -- PARAMETERS:
2081 -- In: p_org_id Operating unit id
2082 -- p_ar_header_id AR Transaction id
2083 --
2084 -- Out: x_has_difference
2085 --
2086 --
2087 -- DESIGN REFERENCES:
2088 -- GTA_REPORTS_TD.doc
2089 --
2090 -- CHANGE HISTORY:
2091 --
2092 -- 05/17/05 Donghai Wang Created.
2093 -- 11/25/05 Donghai Wang modify code to follow ebtax
2094 -- requirement
2095 -- 03/04/05 Donghai Wang Add FND Log
2096 -- 06/28/06 Donghai Wang Fix the bug 5263009
2097 -- 07/21/06 Donghai Wang Fix the bug 5381833
2098 -- 06/18/07 Donghai Wang Fix the bug 6132187
2099 -- 13-MAY-2009 Yao Zhang Changed for bug 5604079
2100 -- 06-Aug-2009 Yao Zhang Changed for bug#8765631
2101 -- 19-Aug-2009 Yao Zhang modified for bug#8809860
2102 -- 23-Sep-2009 Yao Zhang fix bug#8289585
2103 -- 11-Jan-2011 Qiong Liu Fix bug#10638369
2104 --===========================================================================
2105 PROCEDURE Compare_Lines
2106 ( p_org_id IN NUMBER
2107 , p_ar_header_id IN NUMBER
2108 , x_validated_lines OUT NOCOPY NUMBER
2109 , x_ar_matching_lines OUT NOCOPY NUMBER
2110 , x_ar_partially_import OUT NOCOPY NUMBER
2111 , x_has_difference OUT NOCOPY BOOLEAN
2112 )
2113 IS
2114 l_org_id hr_all_organization_units.organization_id%TYPE
2115 :=p_org_id;
2116 l_ar_header_id ra_customer_trx_all.customer_trx_id%TYPE
2117 :=p_ar_header_id;
2118 l_has_difference BOOLEAN;
2119 l_ar_line_id ra_customer_trx_lines_all.customer_trx_line_id%TYPE;
2120 l_ar_line_number ra_customer_trx_lines_all.line_number%TYPE;
2121 l_ar_goods_description ra_customer_trx_lines_all.description%TYPE;
2122 l_ar_line_amount NUMBER;
2123 l_ar_line_amount_disp VARCHAR2(50);
2124 l_ar_vat_line_tax NUMBER;
2125 l_ar_vat_line_tax_disp VARCHAR2(50);
2126 l_ar_vat_tax_rate NUMBER;
2127 l_ar_quantity NUMBER;
2128 l_ar_unit_price NUMBER;
2129 l_ar_unit_price_disp VARCHAR2(50);
2130 l_ar_uom ra_customer_trx_lines_all.uom_code%TYPE;
2131 l_ar_tax_reg_number zx_registrations.registration_number%TYPE;
2132 l_gta_trx_number ar_gta_trx_headers_all.gta_trx_number%TYPE;
2133 l_gta_line_number ar_gta_trx_lines_all.line_number%TYPE;
2134 l_gta_goods_description ra_customer_trx_lines_all.description%TYPE;
2135 l_gta_line_amount NUMBER;
2136 l_gta_vat_line_tax NUMBER;
2137 l_gta_vat_tax_rate NUMBER;
2138 l_gta_quantity NUMBER;
2139 l_gta_unit_price NUMBER;
2140 l_gta_unit_price_disp VARCHAR2(50);
2141 l_gta_uom ra_customer_trx_lines_all.uom_code%TYPE;
2142 l_gta_line_amount_sum NUMBER;
2143 l_gta_line_amount_sum_disp VARCHAR2(50);
2144 l_gta_line_taxamount_sum NUMBER;
2145 l_gta_line_taxamount_sum_disp VARCHAR2(50);
2146 l_gta_line_quantity_sum NUMBER;
2147 l_gta_tax_reg_number ar_gta_trx_headers_all.tp_tax_registration_number%TYPE;
2148 l_matched_flag ar_gta_trx_lines_all.matched_flag%TYPE;
2149 l_matched_flag_total ar_gta_trx_lines_all.matched_flag%TYPE;
2150 l_goods_description_attr fnd_lookup_values.meaning%TYPE;
2151 l_line_amount_attr fnd_lookup_values.meaning%TYPE;
2152 l_vat_line_tax_attr fnd_lookup_values.meaning%TYPE;
2153 l_vat_tax_rate_attr fnd_lookup_values.meaning%TYPE;
2154 l_quantity_attr fnd_lookup_values.meaning%TYPE;
2155 l_unit_price_attr fnd_lookup_values.meaning%TYPE;
2156 l_tax_reg_number_attr fnd_lookup_values.meaning%TYPE;
2157 l_unmatched_attr VARCHAR2(100);
2158 l_uom_attr fnd_lookup_values.meaning%TYPE;
2159 l_gt_value VARCHAR2(500);
2160
2161 --Change length of the variable l_gt_invoice_number to 4000 to fix bug 6132187
2162 --l_gt_invoice_number ar_gta_trx_headers_all.gt_invoice_number%TYPE;
2163 l_gt_invoice_number VARCHAR2(4000);
2164 --------------------------------------------------
2165
2166 l_gta_trx_number_con VARCHAR2(4000);
2167 l_gt_invoice_number_con VARCHAR2(4000);
2168 l_gta_line_number_con VARCHAR2(4000);
2169 l_validated_lines NUMBER;
2170 l_ar_matching_line_flag VARCHAR2(1);
2171 l_ar_matching_lines NUMBER;
2172 l_gta_lines_not_enabled_count NUMBER;
2173 l_ar_partially_import NUMBER;
2174
2175 l_api_name VARCHAR2(50):='Compare_Lines';
2176 l_dbg_msg VARCHAR2(100);
2177 l_error_msg VARCHAR2(4000);
2178
2179 --Added for fixing the bug 5381833
2180 l_tax_type_code zx_lines.tax_type_code%TYPE;
2181 l_gt_currency_code fnd_currencies.currency_code%TYPE;
2182 l_no_value VARCHAR2(1):='-';
2183 --------------------------------------
2184 l_order_number ra_customer_trx_lines_all.interface_line_attribute1%TYPE;
2185 l_om_line_id ra_customer_trx_lines_all.interface_line_attribute1%TYPE;
2186 l_discount_adjustment_id ra_customer_trx_lines_all.interface_line_attribute11%TYPE;
2187 l_price_adjustment_id ra_customer_trx_lines_all.interface_line_attribute1%TYPE;
2188 l_ar_line_context ra_customer_trx_lines_all.interface_line_context%TYPE;
2189 l_adjustment_type OE_PRICE_ADJUSTMENTS.list_line_type_code%TYPE;
2190 l_discount_amount ar_gta_trx_lines_all.discount_amount%TYPE;
2191 l_discount_tax_amount ar_gta_trx_lines_all.discount_tax_amount%TYPE;
2192 l_discount_cust_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%TYPE;
2193
2194
2195 CURSOR c_ar_lines IS
2196 SELECT
2197 rctl.customer_trx_line_id
2198 ,rctl.line_number
2199 ,rctl.description goods_description
2200 ,rctl.quantity_invoiced quantity
2201 ,rctl.unit_selling_price unit_price
2202 ,rctl.uom_code uom
2203 --Yao add for bug#8765631
2204 ,rctl.interface_line_context
2205 ,rctl.interface_line_attribute1
2206 ,rctl.interface_line_attribute6
2207 ,rctl.interface_line_attribute11
2208 --Yao add end
2209 FROM
2210 ra_customer_trx_lines rctl
2211 WHERE rctl.customer_trx_id=l_ar_header_id
2212 AND rctl.line_type='LINE';
2213
2214 CURSOR c_gta_lines IS
2215 SELECT
2216 jgth.gta_trx_number
2217 ,jgth.tp_tax_registration_number
2218 ,jgtl.line_number
2219 ,jgtl.item_description
2220 --Yao modified for bug8765631
2221 ,(jgtl.amount+nvl(jgtl.discount_amount,0)) amount
2222 ,(jgtl.tax_amount+nvl(jgtl.discount_tax_amount,0)) discount_amount
2223 ,jgtl.tax_rate
2224 ,jgtl.quantity
2225 ,round(jgtl.unit_price,2)--Qiong changed from jgtl.unit_price to round(,2) for bug10638369
2226 ,jgtl.uom
2227 ,jgtl.matched_flag
2228 FROM
2229 ar_gta_trx_headers jgth
2230 ,ar_gta_trx_lines_all jgtl
2231 WHERE jgth.ra_trx_id=l_ar_header_id
2232 AND jgth.source='AR'
2233 AND (jgth.status='COMPLETED' OR jgth.status='CONSOLIDATED')
2234 AND jgth.latest_version_flag='Y'
2235 AND jgtl.gta_trx_header_id=jgth.gta_trx_header_id
2236 AND jgtl.ar_trx_line_id=l_ar_line_id
2237 AND jgtl.enabled_flag='Y'
2238 ORDER BY jgth.gta_trx_number;
2239
2240 CURSOR c_gta_lines_not_enabled IS
2241 SELECT
2242 COUNT(*)
2243 FROM
2244 ar_gta_trx_headers jgth
2245 ,ar_gta_trx_lines_all jgtl
2246 WHERE jgth.ra_trx_id=l_ar_header_id
2247 AND jgth.source='AR'
2248 AND jgth.status='COMPLETED'
2249 AND jgth.latest_version_flag='Y'
2250 AND jgtl.gta_trx_header_id=jgth.gta_trx_header_id
2251 AND jgtl.ar_trx_line_id=l_ar_line_id
2252 AND jgtl.enabled_flag='N';
2253
2254 CURSOR c_gt_invoice_number IS
2255 SELECT
2256 jgth.gt_invoice_number
2257 FROM
2258 ar_gta_trx_headers_all jgth
2259 WHERE jgth.source='GT'
2260 AND jgth.gta_trx_number=l_gta_trx_number;
2261
2262 CURSOR c_missing_ar_line IS
2263 SELECT
2264 jgth.gta_trx_number
2265 ,jgtl.line_number
2266 FROM
2267 ar_gta_trx_headers jgth
2268 ,ar_gta_trx_lines_all jgtl
2269 WHERE jgth.ra_trx_id=l_ar_header_id
2270 AND jgth.source='AR'
2271 AND jgth.status='COMPLETED'
2272 AND jgth.latest_version_flag='Y'
2273 AND jgtl.gta_trx_header_id=jgth.gta_trx_header_id
2274 AND NOT EXISTS (SELECT
2275 rctl.customer_trx_line_id
2276 FROM
2277 ra_customer_trx_lines rctl
2278 WHERE rctl.customer_trx_id=l_ar_header_id
2279 AND rctl.customer_trx_line_id=jgtl.ar_trx_line_id
2280 );
2281
2282 --Add this cursor to fix bug 5381833
2283 CURSOR c_tax_type_code
2284 IS
2285 SELECT
2286 vat_tax_type_code
2287 ,gt_currency_code
2288 FROM
2289 ar_gta_system_parameters_all
2290 WHERE org_id=p_org_id;
2291
2292 --Add this cursor to fix bug 5381833
2293 CURSOR c_not_transferred_ar_lines
2294 IS
2295 SELECT
2296 rctl.customer_trx_line_id ar_line_id
2297 ,rctl.line_number ar_line_num
2298 FROM
2299 ra_customer_trx_lines rctl
2300 WHERE rctl.customer_trx_id=l_ar_header_id
2301 --Yao add to fix bug#8765631 to exclude discount line
2302 AND NOT EXISTS ( SELECT opa.list_line_type_code
2303 FROM oe_price_adjustments opa
2304 WHERE rctl.interface_line_context='ORDER ENTRY'
2305 AND opa.price_adjustment_id = rctl.interface_line_attribute11
2306 AND opa.list_line_type_code='DIS')
2307 AND EXISTS (SELECT
2308 zl.trx_line_id
2309 FROM
2310 zx_lines zl
2311 WHERE zl.application_id = 222
2312 AND zl.trx_id=l_ar_header_id
2313 AND zl.trx_level_type='LINE'
2314 AND zl.entity_code='TRANSACTIONS'
2315 AND zl.trx_line_id=rctl.customer_trx_line_id
2316 AND zl.tax_type_code=l_tax_type_code
2317 AND zl.tax_currency_code=l_gt_currency_code
2318 AND zl.event_class_code IN ('INVOICE'
2319 ,'CREDIT_MEMO'
2320 ,'DEBIT_MEMO'
2321 )
2322 )
2323 AND NOT EXISTS (SELECT
2324 jgtl.ar_trx_line_id
2325 FROM
2326 ar_gta_trx_headers jgth
2327 ,ar_gta_trx_lines_all jgtl
2328 WHERE jgth.ra_trx_id=l_ar_header_id
2329 AND jgth.source='AR'
2330 AND jgtl.gta_trx_header_id=jgth.gta_trx_header_id
2331 AND jgtl.ar_trx_line_id=rctl.customer_trx_line_id
2332 );
2333
2334 --Yao Zhang Add for bug#8605196 to support discount line
2335 --c_discount_lines used to query discount lines for ar transaction line.
2336 CURSOR c_discount_lines(l_line_id IN NUMBER) IS
2337 SELECT opa.price_adjustment_id
2338 FROM oe_price_adjustments opa
2339 WHERE opa.line_id = l_line_id
2340 AND opa.list_line_type_code = 'DIS';
2341
2342 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
2343 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
2344 l_ar_loop_count NUMBER;
2345 l_gta_loop_count NUMBER;
2346 l_conversion_rate ra_customer_trx_all.exchange_rate%type;--Yao Zhang add for bug#5604079
2347
2348
2349 BEGIN
2350
2351 --log for debug
2352 IF( l_proc_level >= l_dbg_level)
2353 THEN
2354 FND_LOG.String(l_proc_level
2355 ,G_MODULE_PREFIX||'.'||l_api_name||'.begin'
2356 ,'Enter procedure'
2357 );
2358
2359 FND_LOG.String(l_proc_level
2360 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
2361 ,'l_ar_header_id '||l_ar_header_id
2362 );
2363
2364 FND_LOG.String(l_proc_level
2365 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
2366 ,'l_org_id '||l_org_id
2367 );
2368
2369
2370
2371 END IF; --( l_proc_level >= l_dbg_level)
2372
2373
2374 --uncomment for bug#8289585
2375 --Yao Zhang add for bug#5604079
2376 select exchange_rate into l_conversion_rate
2377 from ra_customer_trx_all
2378 where customer_trx_id=l_ar_header_id;
2379 --Yao Zhang add end
2380 -- To get meaning of line level attribute lookup code
2381 SELECT
2382 flv.meaning
2383 INTO
2384 l_goods_description_attr
2385 FROM
2386 fnd_lookup_values_vl flv
2387 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
2388 AND flv.lookup_code='GOODS_DESCRIPTION';
2389
2390 SELECT
2391 flv.meaning
2392 INTO
2393 l_line_amount_attr
2394 FROM
2395 fnd_lookup_values_vl flv
2396 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
2397 AND flv.lookup_code='LINE_AMOUNT';
2398
2399 SELECT
2400 flv.meaning
2401 INTO
2402 l_vat_line_tax_attr
2403 FROM
2404 fnd_lookup_values_vl flv
2405 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
2406 AND flv.lookup_code='VAT_TAX_AMOUNT';
2407
2408 SELECT
2409 flv.meaning
2410 INTO
2411 l_vat_tax_rate_attr
2412 FROM
2413 fnd_lookup_values_vl flv
2414 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
2415 AND flv.lookup_code='VAT_TAX_RATE';
2416
2417 SELECT
2418 flv.meaning
2419 INTO
2420 l_quantity_attr
2421 FROM
2422 fnd_lookup_values_vl flv
2423 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
2424 AND flv.lookup_code='QUANTITY';
2425
2426 SELECT
2427 flv.meaning
2428 INTO
2429 l_unit_price_attr
2430 FROM
2431 fnd_lookup_values_vl flv
2432 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
2433 AND flv.lookup_code='UNIT_PRICE';
2434
2435 SELECT
2436 flv.meaning
2437 INTO
2438 l_uom_attr
2439 FROM
2440 fnd_lookup_values_vl flv
2441 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
2442 AND flv.lookup_code='UOM';
2443
2444 SELECT
2445 flv.meaning
2446 INTO
2447 l_tax_reg_number_attr
2448 FROM
2449 fnd_lookup_values_vl flv
2450 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
2451 AND flv.lookup_code='TAX_REGISTRATION_NUMBER';
2452
2453 FND_MESSAGE.Set_Name(APPLICATION => 'AR'
2454 ,NAME =>'AR_GTA_REFER_UNMATCH_LINE'
2455 );
2456 l_unmatched_attr:=FND_MESSAGE.Get;
2457
2458
2459 --Get Vat tax type and GT currency code defined in GTA system options form
2460 --for current operating unit
2461 OPEN c_tax_type_code;
2462 FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
2463 CLOSE c_tax_type_code;
2464 --To initialize variables
2465 l_validated_lines:=0;
2466 l_ar_matching_lines:=0;
2467 l_ar_partially_import:=0;
2468
2469 l_ar_loop_count:=0;
2470
2471
2472 --Get AR Line information
2473 OPEN c_ar_lines;
2474 LOOP
2475 FETCH c_ar_lines
2476 INTO l_ar_line_id
2477 ,l_ar_line_number
2478 ,l_ar_goods_description
2479 --commented by Donghai due to ebtax functionality
2480 --,l_ar_line_amount
2481 --,l_ar_vat_line_tax
2482 --,l_ar_vat_tax_rate
2483 ,l_ar_quantity
2484 ,l_ar_unit_price
2485 ,l_ar_uom
2486 --add by Yao for bug#8605196 to support discount line
2487 ,l_ar_line_context
2488 ,l_order_number
2489 ,l_om_line_id
2490 ,l_price_adjustment_id;
2491 EXIT WHEN c_ar_lines%NOTFOUND;
2492 --c_ar_lines%FOUND
2493 --fnd_file.PUT_LINE(fnd_file.LOG,'l_ar_line_context'||l_ar_line_context);
2494 --fnd_file.PUT_LINE(fnd_file.LOG,'l_price_adjustment_id'||l_price_adjustment_id);
2495
2496 --The following code is added by Yao to fix bug#8765631
2497 l_adjustment_type:=null;
2498 l_discount_amount:=NULL;
2499 l_discount_tax_amount:=NULL;
2500
2501 IF(l_ar_line_context='ORDER ENTRY'AND l_price_adjustment_id<>0)
2502 THEN
2503 BEGIN
2504 SELECT opa.list_line_type_code
2505 INTO l_adjustment_type
2506 FROM oe_price_adjustments opa
2507 WHERE opa.price_adjustment_id = l_price_adjustment_id;
2508 EXCEPTION
2509 WHEN no_data_found THEN
2510 CLOSE c_ar_lines;
2511 RAISE;
2512 END;
2513 --Yao Zhang comment for bug#8809860
2514 /*IF l_adjustment_type='DIS'
2515 THEN
2516 CONTINUE;
2517 END IF;/*l_adjustment_type='DIS'*/
2518 END IF;/*(l_interface_line_context='ORDER ENTRY'AND l_price_adjustment_id<>0)*/
2519
2520 --The following code is added by Yao Zhang for bug#8605196 to support discount line
2521 IF(l_ar_line_context='ORDER ENTRY' and l_price_adjustment_id=0)
2522 THEN--the original transction line
2523 --fnd_file.PUT_LINE(fnd_file.LOG,'NormalLine with discount'||l_om_line_id);
2524 OPEN c_discount_lines(l_om_line_id);
2525 LOOP
2526 FETCH c_discount_lines INTO l_discount_adjustment_id;
2527 EXIT WHEN c_discount_lines%NOTFOUND;
2528 --calculate discount amount
2529 SELECT rctl.revenue_amount + nvl(l_discount_amount, 0),
2530 rctl.customer_trx_line_id
2531 INTO l_discount_amount, l_discount_cust_trx_line_id
2532 FROM ra_customer_trx_lines_all rctl
2533 WHERE rctl.customer_trx_id = l_ar_header_id
2534 AND rctl.line_type = 'LINE'
2535 AND rctl.interface_line_attribute11 =
2536 l_discount_adjustment_id;
2537 -- fnd_file.PUT_LINE(fnd_file.LOG,'l_discount_amount IN CURSOR:'||l_discount_amount);
2538 --Calculate the discount tax amount
2539 SELECT tax.tax_amt_tax_curr + nvl(l_discount_tax_amount, 0)
2540 INTO l_discount_tax_amount
2541 FROM zx_lines tax
2542 WHERE tax.trx_line_id = l_discount_cust_trx_line_id
2543 AND tax.entity_code = 'TRANSACTIONS'
2544 AND application_id = 222
2545 AND tax.trx_level_type = 'LINE'
2546 AND tax.tax_currency_code = l_gt_currency_code
2547 AND tax.tax_type_code = l_tax_type_code
2548 AND tax.trx_id = l_ar_header_id;
2549 -- fnd_file.PUT_LINE(fnd_file.LOG,'l_discount_tax_amount IN CURSOR:'||l_discount_tax_amount);
2550 END LOOP;/*c_discount_lines*/
2551 CLOSE c_discount_lines;
2552 END IF;/*(l_interface_line_context='ORDER ENTRY' and l_price_adjustment_id=0)*/
2553 IF l_adjustment_type IS NULL OR l_adjustment_type <>'DIS'
2554 THEN
2555 l_ar_matching_line_flag:='';
2556 l_ar_tax_reg_number:='';
2557 l_ar_line_amount:=0;
2558 l_ar_vat_line_tax:=0;
2559 l_ar_vat_tax_rate:=0;
2560
2561 --To summary the number of validated lines
2562 l_validated_lines:=l_validated_lines+1;
2563 l_gta_line_amount_sum:=0;
2564 l_gta_line_taxamount_sum:=0;
2565 l_gta_line_quantity_sum:=0;
2566 l_matched_flag_total:='Y';
2567 l_gta_trx_number_con:='';
2568 l_gt_invoice_number_con:='';
2569 l_gta_line_number_con:='';
2570
2571 l_ar_loop_count:=l_ar_loop_count+1;
2572
2573 --Yao Zhang add for bug#5604079
2574 IF l_conversion_rate IS NOT NULL
2575 THEN l_ar_unit_price:=round(l_ar_unit_price*l_conversion_rate,2);
2576 ELSE
2577 l_ar_unit_price:=round(l_ar_unit_price,2);--Qiong add for bug10638369
2578 END IF;
2579
2580 --log for debug
2581 IF( l_proc_level >= l_dbg_level)
2582 THEN
2583
2584 FND_LOG.String(l_proc_level
2585 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2586 ,'l_ar_loop_count '||l_ar_loop_count
2587 );
2588
2589 FND_LOG.String(l_proc_level
2590 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2591 ,'l_ar_line_id '||l_ar_line_id
2592 );
2593
2594 FND_LOG.String(l_proc_level
2595 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2596 ,'l_ar_line_number '||l_ar_line_number
2597 );
2598
2599 FND_LOG.String(l_proc_level
2600 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2601 ,'l_ar_goods_description '||l_ar_goods_description
2602 );
2603
2604 FND_LOG.String(l_proc_level
2605 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2606 ,'l_ar_quantity '||l_ar_quantity
2607 );
2608
2609 FND_LOG.String(l_proc_level
2610 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2611 ,'l_ar_unit_price '||l_ar_unit_price
2612 );
2613
2614 FND_LOG.String(l_proc_level
2615 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2616 ,'l_ar_uom '||l_ar_uom
2617 );
2618
2619 END IF; --( l_proc_level >= l_dbg_level)
2620
2621 --First check if current AR line have multiple tax lines per VAT tax
2622 --type and GT currency code defined on GTA 'system options' form,
2623 --if 'YES', then set NULL to tax registration number,taxable amount,
2624 --tax amount and tax rate, if 'No', then give exact values to tax
2625 --registration number, taxable amount,tax amount and tax rate.
2626 IF AR_GTA_TRX_UTIL.Check_Taxcount_Of_Arline
2627 (p_org_id => l_org_id
2628 ,p_customer_trx_line_id => l_ar_line_id
2629 )
2630 THEN
2631 --Third party tax registration number
2632 l_ar_tax_reg_number:=AR_GTA_TRX_UTIL.Get_Arline_Tp_Taxreg_Number
2633 (p_org_id => l_org_id
2634 ,p_customer_trx_id => l_ar_header_id
2635 ,p_customer_trx_line_id => l_ar_line_id
2636 );
2637 --taxable amount of AR line
2638 l_ar_line_amount:=AR_GTA_TRX_UTIL.Get_Arline_Amount
2639 (p_org_id => l_org_id
2640 ,p_customer_trx_line_id => l_ar_line_id
2641 )+ nvl(l_discount_amount,0);
2642 --tax amount of AR line
2643 l_ar_vat_line_tax:=AR_GTA_TRX_UTIL.Get_Arline_Vattax_Amount
2644 (p_org_id => l_org_id
2645 ,p_customer_trx_line_id => l_ar_line_id
2646 )+nvl(l_discount_tax_amount,0);
2647 --tax rate of AR line
2648 l_ar_vat_tax_rate:=AR_GTA_TRX_UTIL.Get_Arline_Vattax_Rate
2649 (p_org_id => l_org_id
2650 ,p_customer_trx_line_id => l_ar_line_id
2651 );
2652
2653 ELSE
2654 l_ar_tax_reg_number:='';
2655 l_ar_line_amount:='';
2656 l_ar_vat_line_tax:='';
2657 l_ar_vat_tax_rate:='';
2658 END IF; --AR_GTA_TRX_UTIL.Check_Taxcount_Of_Arline
2659
2660 --log for debug
2661 IF( l_proc_level >= l_dbg_level)
2662 THEN
2663
2664 FND_LOG.String(l_proc_level
2665 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2666 ,'l_ar_tax_reg_number '||l_ar_tax_reg_number
2667 );
2668
2669 FND_LOG.String(l_proc_level
2670 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2671 ,'l_ar_line_amount '||l_ar_line_amount
2672 );
2673
2674 FND_LOG.String(l_proc_level
2675 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2676 ,'l_ar_vat_line_tax '||l_ar_vat_line_tax
2677 );
2678
2679 FND_LOG.String(l_proc_level
2680 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2681 ,'l_ar_vat_tax_rate '||l_ar_vat_tax_rate
2682 );
2683
2684
2685
2686
2687 END IF; --( l_proc_level >= l_dbg_level)
2688
2689 l_gta_loop_count:=0;
2690
2691 OPEN c_gta_lines;
2692 LOOP
2693 FETCH c_gta_lines
2694 INTO l_gta_trx_number
2695 ,l_gta_tax_reg_number
2696 ,l_gta_line_number
2697 ,l_gta_goods_description
2698 ,l_gta_line_amount
2699 ,l_gta_vat_line_tax
2700 ,l_gta_vat_tax_rate
2701 ,l_gta_quantity
2702 ,l_gta_unit_price
2703 ,l_gta_uom
2704 ,l_matched_flag;
2705 EXIT WHEN c_gta_lines%NOTFOUND;
2706 l_gta_loop_count:=l_gta_loop_count+1;
2707
2708 --log for debug
2709 IF( l_proc_level >= l_dbg_level)
2710 THEN
2711
2712 FND_LOG.String(l_proc_level
2713 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2714 ,'l_gta_loop_count '||l_gta_loop_count
2715 );
2716
2717 FND_LOG.String(l_proc_level
2718 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2719 ,'l_gta_trx_number '||l_gta_trx_number
2720 );
2721
2722 FND_LOG.String(l_proc_level
2723 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2724 ,'l_gta_tax_reg_number '||l_gta_tax_reg_number
2725 );
2726
2727 FND_LOG.String(l_proc_level
2728 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2729 ,'l_gta_line_number '||l_gta_line_number
2730 );
2731
2732 FND_LOG.String(l_proc_level
2733 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2734 ,'l_gta_vat_line_tax '||l_gta_vat_line_tax
2735 );
2736
2737 FND_LOG.String(l_proc_level
2738 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2739 ,'l_gta_vat_tax_rate '||l_gta_vat_tax_rate
2740 );
2741
2742 FND_LOG.String(l_proc_level
2743 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2744 ,'l_gta_quantity '||l_gta_quantity
2745 );
2746
2747 FND_LOG.String(l_proc_level
2748 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2749 ,'l_gta_unit_price '||l_gta_unit_price
2750 );
2751
2752 FND_LOG.String(l_proc_level
2753 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2754 ,'l_gta_uom '||l_gta_uom
2755 );
2756
2757 FND_LOG.String(l_proc_level
2758 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2759 ,'l_matched_flag '||l_matched_flag
2760 );
2761
2762
2763
2764
2765
2766 END IF; --( l_proc_level >= l_dbg_level)
2767
2768
2769 l_ar_matching_line_flag:='Y';
2770
2771 --accumulate line amount, quantity, line tax amount for all gta invoice
2772 --line against an AR invoice line.
2773 --Yao modified for bug#8765631
2774
2775 l_gta_line_amount_sum:=l_gta_line_amount_sum+l_gta_line_amount;
2776 l_gta_line_taxamount_sum:=l_gta_line_taxamount_sum+l_gta_vat_line_tax;
2777 l_gta_line_quantity_sum:=l_gta_line_quantity_sum+l_gta_quantity;
2778
2779 --log for debug
2780 IF( l_proc_level >= l_dbg_level)
2781 THEN
2782
2783 FND_LOG.String(l_proc_level
2784 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2785 ,'l_gta_line_amount_sum '||l_gta_line_amount_sum
2786 );
2787
2788 FND_LOG.String(l_proc_level
2789 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2790 ,'l_gta_line_taxamount_sum '||l_gta_line_taxamount_sum
2791 );
2792
2793 FND_LOG.String(l_proc_level
2794 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2795 ,'l_gta_line_quantity_sum '||l_gta_line_quantity_sum
2796 );
2797
2798
2799 END IF; --( l_proc_level >= l_dbg_level)
2800
2801 OPEN c_gt_invoice_number;
2802 FETCH c_gt_invoice_number INTO l_gt_invoice_number;
2803 CLOSE c_gt_invoice_number;
2804
2805 --log for debug
2806 IF( l_proc_level >= l_dbg_level)
2807 THEN
2808
2809 FND_LOG.String(l_proc_level
2810 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2811 ,'l_gt_invoice_number '||l_gt_invoice_number
2812 );
2813 END IF; --( l_proc_level >= l_dbg_level)
2814
2815 --To concate GTA Trx NUmber
2816 IF (l_gta_trx_number_con IS NULL)
2817 THEN
2818 l_gta_trx_number_con:=l_gta_trx_number;
2819 ELSIF (instr(l_gta_trx_number_con,l_gta_trx_number)=0)
2820 THEN
2821 l_gta_trx_number_con:=l_gta_trx_number_con||','||l_gta_trx_number;
2822 END IF; --(l_gta_trx_number_con IS NULL)
2823
2824 --log for debug
2825 IF( l_proc_level >= l_dbg_level)
2826 THEN
2827
2828 FND_LOG.String(l_proc_level
2829 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2830 ,'l_gta_trx_number_con '||l_gta_trx_number_con
2831 );
2832 END IF; --( l_proc_level >= l_dbg_level)
2833
2834 --To concate GT invoice number
2835 IF (l_gt_invoice_number_con IS NULL)
2836 THEN
2837 l_gt_invoice_number_con:=l_gt_invoice_number;
2838 ELSIF (instr(l_gt_invoice_number_con,l_gt_invoice_number)=0)
2839 THEN
2840 l_gt_invoice_number_con:=l_gt_invoice_number_con||','||l_gt_invoice_number;
2841 END IF; --(l_gt_invoice_number_con IS NULL)
2842
2843 --log for debug
2844 IF( l_proc_level >= l_dbg_level)
2845 THEN
2846
2847 FND_LOG.String(l_proc_level
2848 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2849 ,'l_gt_invoice_number_con '||l_gt_invoice_number_con
2850 );
2851 END IF; --( l_proc_level >= l_dbg_level)
2852
2853 --To concate GTA line number
2854 IF (l_gta_line_number_con IS NULL)
2855 THEN
2856 l_gta_line_number_con:=l_gta_line_number;
2857 ELSE
2858 l_gta_line_number_con:=l_gta_line_number_con||','||l_gta_line_number;
2859 END IF; --(l_gta_line_number_con IS NULL)
2860
2861
2862 --log for debug
2863 IF( l_proc_level >= l_dbg_level)
2864 THEN
2865
2866 FND_LOG.String(l_proc_level
2867 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
2868 ,'l_gta_line_number_con '||l_gta_line_number_con
2869 );
2870 END IF; --( l_proc_level >= l_dbg_level)
2871
2872 IF l_matched_flag='N'
2873 THEN
2874 l_matched_flag_total:='N';
2875 END IF; --l_matched_flag='N'
2876
2877
2878
2879 --Compare third party tax registration number between AR line and GTA line
2880 IF (
2881 (l_ar_tax_reg_number<>l_gta_tax_reg_number) AND
2882 (l_ar_tax_reg_number IS NOT NULL)
2883 ) OR
2884 (l_ar_tax_reg_number IS NULL)
2885 THEN
2886 IF (l_matched_flag='Y')
2887 THEN
2888 l_gt_value:=l_gta_tax_reg_number;
2889 ELSE
2890 l_gt_invoice_number:='-';
2891 -- l_gt_value:=l_unmatched_attr;
2892 END IF; --(l_matched_flag='Y')
2893
2894 --insert discrepancy record into temp table ar_gta_difference_temp
2895 INSERT INTO ar_gta_difference_temp(TYPE
2896 ,ar_header_id
2897 ,ar_line_id
2898 ,ATTRIBUTE
2899 ,ar_line_num
2900 ,ar_value
2901 ,gta_invoice_num
2902 ,gta_line_num
2903 ,gta_value
2904 ,gt_invoice_num
2905 ,gt_value
2906 )
2907 VALUES('LINE'
2908 ,l_ar_header_id
2909 ,l_ar_line_id
2910 ,l_tax_reg_number_attr
2911 ,l_ar_line_number
2912 ,l_ar_tax_reg_number
2913 ,l_gta_trx_number
2914 ,l_gta_line_number
2915 ,l_gta_tax_reg_number
2916 ,l_gt_invoice_number
2917 ,l_gt_value
2918 );
2919
2920 l_ar_matching_line_flag:='N';
2921 l_has_difference:=TRUE;
2922
2923 --Add folllowing logic for fix the issue#4 in the bug 5263009
2924 --if tax registration number of line match with GTA's,
2925 --but GTA dosen't match GT, then ar line should be regarded as
2926 -- not maching.
2927 ELSE
2928 IF (l_matched_flag='N')
2929 THEN
2930 l_ar_matching_line_flag:='N';
2931 l_has_difference:=TRUE;
2932 END IF; -- (l_matched_flag='N')
2933 END IF; --((l_ar_tax_reg_number<>l_gta_tax_reg_number)......
2934
2935 --compare goods descripiton between AR line and GTA line
2936 IF (l_ar_goods_description<>l_gta_goods_description)
2937 THEN
2938 IF (l_matched_flag='Y')
2939 THEN
2940 l_gt_value:=l_gta_goods_description;
2941 ELSE
2942 l_gt_invoice_number:='-';
2943 --l_gt_value:=l_unmatched_attr;
2944 END IF; --(l_matched_flag='Y')
2945
2946 --insert discrepancy record into temp table ar_gta_difference_temp
2947 INSERT INTO ar_gta_difference_temp(TYPE
2948 ,ar_header_id
2949 ,ar_line_id
2950 ,ATTRIBUTE
2951 ,ar_line_num
2952 ,ar_value
2953 ,gta_invoice_num
2954 ,gta_line_num
2955 ,gta_value
2956 ,gt_invoice_num
2957 ,gt_value
2958 )
2959 VALUES('LINE'
2960 ,l_ar_header_id
2961 ,l_ar_line_id
2962 ,l_goods_description_attr
2963 ,l_ar_line_number
2964 ,l_ar_goods_description
2965 ,l_gta_trx_number
2966 ,l_gta_line_number
2967 ,l_gta_goods_description
2968 ,l_gt_invoice_number
2969 ,l_gt_value
2970 );
2971
2972 l_ar_matching_line_flag:='N';
2973 l_has_difference:=TRUE;
2974
2975 --Add folllowing logic for fix the issue#4 in the bug 5263009
2976 --if goods descripiton of line match with GTA's,
2977 --but GTA dosen't match GT, then ar line should be regarded as
2978 -- not maching.
2979 ELSE
2980 IF (l_matched_flag='N')
2981 THEN
2982 l_ar_matching_line_flag:='N';
2983 l_has_difference:=TRUE;
2984 END IF; -- (l_matched_flag='N')
2985 END IF; --(l_ar_goods_description<>l_gta_goods_description)
2986
2987 --compare VAT Tax Rate
2988 IF (l_ar_vat_tax_rate<>l_gta_vat_tax_rate)
2989 THEN
2990 IF (l_matched_flag='Y')
2991 THEN
2992 l_gt_value:=l_gta_vat_tax_rate;
2993 ELSE
2994 l_gt_invoice_number:='-';
2995 -- l_gt_value:=l_unmatched_attr;
2996 END IF; --(l_matched_flag='Y')
2997
2998 --insert discrepancy record into temp table ar_gta_difference_temp
2999 INSERT INTO ar_gta_difference_temp(TYPE
3000 ,ar_header_id
3001 ,ar_line_id
3002 ,ATTRIBUTE
3003 ,ar_line_num
3004 ,ar_value
3005 ,gta_invoice_num
3006 ,gta_line_num
3007 ,gta_value
3008 ,gt_invoice_num
3009 ,gt_value
3010 )
3011 VALUES('LINE'
3012 ,l_ar_header_id
3013 ,l_ar_line_id
3014 ,l_vat_tax_rate_attr
3015 ,l_ar_line_number
3016 ,l_ar_vat_tax_rate
3017 ,l_gta_trx_number
3018 ,l_gta_line_number
3019 ,l_gta_vat_tax_rate
3020 ,l_gt_invoice_number
3021 ,l_gt_value
3022 );
3023 l_ar_matching_line_flag:='N';
3024 l_has_difference:=TRUE;
3025
3026 --Add folllowing logic for fix the issue#4 in the bug 5263009
3027 --if VAT Tax Rate of line match with GTA's,
3028 --but GTA dosen't match GT, then ar line should be regarded as
3029 -- not maching.
3030 ELSE
3031 IF (l_matched_flag='N')
3032 THEN
3033 l_ar_matching_line_flag:='N';
3034 l_has_difference:=TRUE;
3035 END IF; -- (l_matched_flag='N')
3036 END IF; --(l_ar_vat_tax_rate<>l_gta_vat_tax_rate)
3037
3038
3039
3040 --Compare Unit Price
3041 IF (l_ar_unit_price<>l_gta_unit_price)
3042 THEN
3043 IF (l_matched_flag='Y')
3044 THEN
3045 l_gt_value:=AR_GTA_TRX_UTIL.Format_Monetary_Amount(p_org_id => p_org_id
3046 ,p_amount => l_gta_unit_price
3047 );
3048 ELSE
3049 l_gt_invoice_number:='-';
3050 --l_gt_value:=l_unmatched_attr;
3051 END IF; --(l_matched_flag='Y')
3052
3053 --Fomrat Amount
3054 l_ar_unit_price_disp:=AR_GTA_TRX_UTIL.Format_Monetary_Amount(p_org_id => p_org_id
3055 ,p_amount => l_ar_unit_price
3056 );
3057 l_gta_unit_price_disp:=AR_GTA_TRX_UTIL.Format_Monetary_Amount(p_org_id => p_org_id
3058 ,p_amount => l_gta_unit_price
3059 );
3060
3061 --insert discrepancy record into temp table ar_gta_difference_temp
3062 INSERT INTO ar_gta_difference_temp(TYPE
3063 ,ar_header_id
3064 ,ar_line_id
3065 ,ATTRIBUTE
3066 ,ar_line_num
3067 ,ar_value
3068 ,gta_invoice_num
3069 ,gta_line_num
3070 ,gta_value
3071 ,gt_invoice_num
3072 ,gt_value
3073 )
3074 VALUES('LINE'
3075 ,l_ar_header_id
3076 ,l_ar_line_id
3077 ,l_unit_price_attr
3078 ,l_ar_line_number
3079 ,l_ar_unit_price_disp
3080 ,l_gta_trx_number
3081 ,l_gta_line_number
3082 ,l_gta_unit_price_disp
3083 ,l_gt_invoice_number
3084 ,l_gt_value
3085 );
3086 l_ar_matching_line_flag:='N';
3087 l_has_difference:=TRUE;
3088 --Add folllowing logic for fix the issue#4 in the bug 5263009
3089 --if Unit Price of line match with GTA's,
3090 --but GTA dosen't match GT, then ar line should be regarded as
3091 -- not maching.
3092 ELSE
3093 IF (l_matched_flag='N')
3094 THEN
3095 l_ar_matching_line_flag:='N';
3096 l_has_difference:=TRUE;
3097 END IF; -- (l_matched_flag='N')
3098 END IF; --(l_ar_unit_price<>l_gta_unit_price)
3099
3100 --Compare UOM
3101 IF (l_ar_uom<>l_gta_uom)
3102 THEN
3103 IF (l_matched_flag='Y')
3104 THEN
3105 l_gt_value:=l_gta_uom;
3106 ELSE
3107 l_gt_invoice_number:='-';
3108 -- l_gt_value:=l_unmatched_attr;
3109 END IF; --(l_matched_flag='Y')
3110
3111 --insert discrepancy record into temp table ar_gta_difference_temp
3112 INSERT INTO ar_gta_difference_temp(TYPE
3113 ,ar_header_id
3114 ,ar_line_id
3115 ,ATTRIBUTE
3116 ,ar_line_num
3117 ,ar_value
3118 ,gta_invoice_num
3119 ,gta_line_num
3120 ,gta_value
3121 ,gt_invoice_num
3122 ,gt_value
3123 )
3124 VALUES('LINE'
3125 ,l_ar_header_id
3126 ,l_ar_line_id
3127 ,l_uom_attr
3128 ,l_ar_line_number
3129 ,l_ar_uom
3130 ,l_gta_trx_number
3131 ,l_gta_line_number
3132 ,l_gta_uom
3133 ,l_gt_invoice_number
3134 ,l_gt_value
3135 );
3136
3137 l_ar_matching_line_flag:='N';
3138 l_has_difference:=TRUE;
3139
3140 --Add folllowing logic for fix the issue#4 in the bug 5263009
3141 --if UOM of line match with GTA's,
3142 --but GTA dosen't match GT, then ar line should be regarded as
3143 -- not maching.
3144 ELSE
3145 IF (l_matched_flag='N')
3146 THEN
3147 l_ar_matching_line_flag:='N';
3148 l_has_difference:=TRUE;
3149 END IF; -- (l_matched_flag='N')
3150 END IF; --(l_ar_uom<>l_gta_uom)
3151 END LOOP;--c_gta_lines%FOUND
3152 CLOSE c_gta_lines;
3153
3154 --compare quantity
3155 IF (l_ar_quantity<>l_gta_line_quantity_sum) AND
3156 (l_gta_trx_number_con IS NOT NULL) --To validate that current AR line
3157 -- was already transferred to GTA
3158 THEN
3159
3160 IF (l_matched_flag_total='Y')
3161 THEN
3162 l_gt_invoice_number:=l_gt_invoice_number_con;
3163 l_gt_value:=l_gta_line_quantity_sum;
3164
3165 ELSE
3166 l_gt_invoice_number:='-';
3167 -- l_gt_value:=l_unmatched_attr;
3168 END IF; --(l_matched_flag_sum='Y')
3169
3170
3171 --insert discrepancy record into temp table ar_gta_difference_temp
3172 INSERT INTO ar_gta_difference_temp(TYPE
3173 ,ar_header_id
3174 ,ar_line_id
3175 ,ATTRIBUTE
3176 ,ar_line_num
3177 ,ar_value
3178 ,gta_invoice_num
3179 ,gta_line_num
3180 ,gta_value
3181 ,gt_invoice_num
3182 ,gt_value
3183 )
3184 VALUES('LINE'
3185 ,l_ar_header_id
3186 ,l_ar_line_id
3187 ,l_quantity_attr
3188 ,l_ar_line_number
3189 ,l_ar_quantity
3190 ,l_gta_trx_number_con
3191 ,l_gta_line_number_con
3192 ,l_gta_line_quantity_sum
3193 ,l_gt_invoice_number
3194 ,l_gt_value
3195 );
3196 l_ar_matching_line_flag:='N';
3197 l_has_difference:=TRUE;
3198
3199 --Add folllowing logic for fix the issue#4 in the bug 5263009
3200 --if quantity of ar line match with GTA's,
3201 --but GTA dosen't match GT, then ar line should be regarded as
3202 -- not maching.
3203 ELSE
3204 IF (l_matched_flag_total='N')
3205 THEN
3206 l_ar_matching_line_flag:='N';
3207 l_has_difference:=TRUE;
3208 END IF; -- (l_matched_flag_total='N')
3209
3210 END IF; --(l_ar_quantity<>l_gta_line_quantity_sum)
3211
3212 --compare Line Amount
3213 --Yao modified for bug#8765631
3214 --fnd_file.PUT_LINE(fnd_file.LOG,'l_ar_line_amount'||nvl(l_ar_line_amount,0));
3215 --fnd_file.PUT_LINE(fnd_file.LOG,'l_gta_line_amount_sum'||nvl(l_gta_line_amount_sum,0));
3216 l_ar_line_amount:=round(l_ar_line_amount,2);--Qiong add for bug 10638369
3217 l_gta_line_amount_sum:=round(l_gta_line_amount_sum,2);--Qiong add for bug 10638369
3218 IF (nvl(l_ar_line_amount,0)<>nvl(l_gta_line_amount_sum,0)) AND
3219 (l_gta_trx_number_con IS NOT NULL) --To validate that current
3220 --AR line was already transferred
3221 -- to GTA
3222 THEN
3223 IF (l_matched_flag_total='Y')
3224 THEN
3225 l_gt_invoice_number:=l_gt_invoice_number_con;
3226 l_gt_value:=AR_GTA_TRX_UTIL.Format_Monetary_Amount(p_org_id => p_org_id
3227 ,p_amount => l_gta_line_amount_sum
3228 );
3229
3230 ELSE
3231 l_gt_invoice_number:='-';
3232 -- l_gt_value:=l_unmatched_attr;
3233 END IF; --(l_matched_flag_sum='Y')
3234
3235 --Format Amount
3236 l_ar_line_amount_disp:=AR_GTA_TRX_UTIL.Format_Monetary_Amount(p_org_id => p_org_id
3237 ,p_amount => l_ar_line_amount
3238 );
3239 l_gta_line_amount_sum_disp:=AR_GTA_TRX_UTIL.Format_Monetary_Amount(p_org_id => p_org_id
3240 ,p_amount => l_gta_line_amount_sum
3241 );
3242
3243 --insert discrepancy record into temp table ar_gta_difference_temp
3244 INSERT INTO ar_gta_difference_temp(TYPE
3245 ,ar_header_id
3246 ,ar_line_id
3247 ,ATTRIBUTE
3248 ,ar_line_num
3249 ,ar_value
3250 ,gta_invoice_num
3251 ,gta_line_num
3252 ,gta_value
3253 ,gt_invoice_num
3254 ,gt_value
3255 )
3256 VALUES('LINE'
3257 ,l_ar_header_id
3258 ,l_ar_line_id
3259 ,l_line_amount_attr
3260 ,l_ar_line_number
3261 ,l_ar_line_amount_disp
3262 ,l_gta_trx_number_con
3263 ,l_gta_line_number_con
3264 ,l_gta_line_amount_sum_disp
3265 ,l_gt_invoice_number
3266 ,l_gt_value
3267 );
3268 l_ar_matching_line_flag:='N';
3269 l_has_difference:=TRUE;
3270
3271 --Add folllowing logic for fix the issue#4 in the bug 5263009
3272 --if amount of AR line match with GTA's,
3273 --but GTA dosen't match GT, then ar line should be regarded as
3274 -- not maching.
3275 ELSE
3276 IF (l_matched_flag_total='N')
3277 THEN
3278 l_ar_matching_line_flag:='N';
3279 l_has_difference:=TRUE;
3280 END IF; -- (l_matched_flag_total='N')
3281 END IF; --(l_ar_line_amount<>l_gta_line_amount_sum)
3282
3283 --compare VAT Line Tax
3284 --Yao modified for bug#8765631
3285 IF (nvl(l_ar_vat_line_tax,0)<>nvl(l_gta_line_taxamount_sum,0)) AND
3286 (l_gta_trx_number_con IS NOT NULL) --To validate that current AR
3287 --line was already transferred
3288 --to GTA
3289 THEN
3290 IF (l_matched_flag_total='Y')
3291 THEN
3292 l_gt_invoice_number:=l_gt_invoice_number_con;
3293 l_gt_value:=AR_GTA_TRX_UTIL.Format_Monetary_Amount(p_org_id => p_org_id
3294 ,p_amount => l_gta_line_taxamount_sum
3295 );
3296
3297 ELSE
3298 l_gt_invoice_number:='-';
3299 --l_gt_value:=l_unmatched_attr;
3300 END IF; --(l_matched_flag_sum='Y')
3301
3302 --Format Amount
3303 l_ar_vat_line_tax_disp:=AR_GTA_TRX_UTIL.Format_Monetary_Amount(p_org_id => p_org_id
3304 ,p_amount => l_ar_vat_line_tax
3305 );
3306 l_gta_line_taxamount_sum_disp:=AR_GTA_TRX_UTIL.Format_Monetary_Amount(p_org_id => p_org_id
3307 ,p_amount => l_gta_line_taxamount_sum
3308 );
3309
3310 --insert discrepancy record into temp table ar_gta_difference_temp
3311 INSERT INTO ar_gta_difference_temp(TYPE
3312 ,ar_header_id
3313 ,ar_line_id
3314 ,ATTRIBUTE
3315 ,ar_line_num
3316 ,ar_value
3317 ,gta_invoice_num
3318 ,gta_line_num
3319 ,gta_value
3320 ,gt_invoice_num
3321 ,gt_value
3322 )
3323 VALUES('LINE'
3324 ,l_ar_header_id
3325 ,l_ar_line_id
3326 ,l_vat_line_tax_attr
3327 ,l_ar_line_number
3328 ,l_ar_vat_line_tax_disp
3329 ,l_gta_trx_number_con
3330 ,l_gta_line_number_con
3331 ,l_gta_line_taxamount_sum_disp
3332 ,l_gt_invoice_number
3333 ,l_gt_value
3334 );
3335 l_ar_matching_line_flag:='N';
3336 l_has_difference:=TRUE;
3337
3338 --Add folllowing logic for fix the issue#4 in the bug 5263009
3339 --if VAT Line Tax of AR line match with GTA's,
3340 --but GTA dosen't match GT, then ar line should be regarded as
3341 -- not maching.
3342 ELSE
3343 IF (l_matched_flag_total='N')
3344 THEN
3345 l_ar_matching_line_flag:='N';
3346 l_has_difference:=TRUE;
3347 END IF; -- (l_matched_flag_total='N')
3348 END IF; --(l_ar_vat_line_tax<>l_line_gta_taxamount_sum)
3349
3350 --To summary ar lines that have matching data with GTA nad GT
3351 IF l_ar_matching_line_flag='Y'
3352 THEN
3353 l_ar_matching_lines:=l_ar_matching_lines+1;
3354 END IF; --l_ar_matching_line_flag:='Y'
3355
3356 --To judge if this ar line is partially imported
3357 OPEN c_gta_lines_not_enabled;
3358 FETCH c_gta_lines_not_enabled INTO l_gta_lines_not_enabled_count;
3359 CLOSE c_gta_lines_not_enabled;
3360
3361
3362 IF l_gta_lines_not_enabled_count>0
3363 THEN
3364 l_ar_partially_import:=l_ar_partially_import+1;
3365 END IF; --l_gta_lines_not_enabled_count>0
3366 --yao add for bug#8809860
3367 END IF;--IF l_adjustment_type is null or l_adjustment_type <>'dis'
3368 END LOOP;
3369 CLOSE c_ar_lines;
3370
3371 --To deal with missing ar line if any
3372 OPEN c_missing_ar_line;
3373 FETCH c_missing_ar_line INTO l_gta_trx_number,l_gta_line_number;
3374 WHILE c_missing_ar_line%FOUND
3375 LOOP
3376
3377 --log for debug
3378 IF( l_proc_level >= l_dbg_level)
3379 THEN
3380
3381 FND_LOG.String(l_proc_level
3382 ,G_MODULE_PREFIX||'.'||l_api_name||'.phase'
3383 ,'c_missing_ar_line'
3384 );
3385 END IF; --( l_proc_level >= l_dbg_level)
3386
3387
3388 OPEN c_gt_invoice_number;
3389 FETCH c_gt_invoice_number INTO l_gt_invoice_number;
3390 CLOSE c_gt_invoice_number;
3391
3392 --To Insert GTA trx number and GT invoice number that missed ar line to
3393 -- temp table ar_gta_difference_temp
3394 INSERT INTO ar_gta_difference_temp(TYPE
3395 ,ar_header_id
3396 ,ar_line_num
3397 ,gta_invoice_num
3398 ,gta_line_num
3399 ,gt_invoice_num
3400 )
3401 VALUES('MISSING_AR_LINE'
3402 ,l_ar_header_id
3403 ,l_no_value
3404 ,l_gta_trx_number
3405 ,l_gta_line_number
3406 ,l_gt_invoice_number
3407 );
3408 l_has_difference:=TRUE;
3409 FETCH c_missing_ar_line INTO l_gta_trx_number,l_gta_line_number;
3410
3411
3412 END LOOP; --c_missing_ar_line%FOUND
3413
3414 CLOSE c_missing_ar_line;
3415
3416 --Add the following logic for fixing issue #2 in the bug 5381833
3417 --Any AR line that have VAT tax lines and were added after current
3418 --AR transaction was transferred to GTA should be listed in the
3419 --section "Missing AR Line" as well.
3420 FOR l_not_transferred_ar_line IN c_not_transferred_ar_lines
3421 LOOP
3422 --log for debug
3423 IF( l_proc_level >= l_dbg_level)
3424 THEN
3425
3426 FND_LOG.String(l_proc_level
3427 ,G_MODULE_PREFIX||'.'||l_api_name||'.phase'
3428 ,'c_not_transferred_ar_lines'
3429 );
3430 END IF; --( l_proc_level >= l_dbg_level)
3431
3432 INSERT INTO ar_gta_difference_temp(TYPE
3433 ,ar_header_id
3434 ,ar_line_id
3435 ,ar_line_num
3436 ,gta_invoice_num
3437 ,gta_line_num
3438 ,gt_invoice_num
3439 )
3440 VALUES('MISSING_AR_LINE'
3441 ,l_ar_header_id
3442 ,l_not_transferred_ar_line.ar_line_id
3443 ,l_not_transferred_ar_line.ar_line_num
3444 ,l_no_value
3445 ,l_no_value
3446 ,l_no_value
3447 );
3448 l_has_difference:=TRUE;
3449 END LOOP; --l_not_transferred_ar_line IN c_not_transferred_ar_lines
3450
3451 --Give values to output parameters
3452 x_validated_lines:=l_validated_lines;
3453 x_ar_matching_lines:=l_ar_matching_lines;
3454 x_ar_partially_import:=l_ar_partially_import;
3455 x_has_difference:=l_has_difference;
3456
3457 --log for debug
3458 IF( l_proc_level >= l_dbg_level)
3459 THEN
3460 FND_LOG.String(l_proc_level
3461 ,G_MODULE_PREFIX||'.'||l_api_name||'.returned_value'
3462 ,'x_validated_lines '||l_validated_lines
3463 );
3464
3465 FND_LOG.String(l_proc_level
3466 ,G_MODULE_PREFIX||'.'||l_api_name||'.returned_value'
3467 ,'x_ar_matching_lines '||l_ar_matching_lines
3468 );
3469
3470 FND_LOG.String(l_proc_level
3471 ,G_MODULE_PREFIX||'.'||l_api_name||'.returned_value'
3472 ,'x_ar_partially_import '||l_ar_partially_import
3473 );
3474
3475 FND_LOG.String(l_proc_level
3476 ,G_MODULE_PREFIX||'.'||l_api_name||'.end'
3477 ,'Exit procedure'
3478 );
3479
3480
3481
3482
3483 END IF; --( l_proc_level >= l_dbg_level)
3484
3485 EXCEPTION
3486 WHEN OTHERS THEN
3487 IF(l_proc_level >= l_dbg_level)
3488 THEN
3489 l_error_msg:=SQLCODE||':'||SQLERRM;
3490 FND_LOG.String( l_proc_level
3491 , G_Module_Prefix || l_api_name || '. Other_Exception '
3492 , l_error_msg);
3493
3494
3495 END IF; --(l_proc_level >= l_dbg_level)
3496 RAISE;
3497 END Compare_Lines;
3498
3499
3500 --==========================================================================
3501 -- PROCEDURE NAME:
3502 --
3503 -- Get_Unmatched_Lines Public
3504 --
3505 -- DESCRIPTION:
3506 --
3507 -- This Procedure Get Gta, Gt Unmatched Lines And Input Difference Record
3508 --
3509 --
3510 -- PARAMETERS:
3511 -- In: p_org_id Operating unit id
3512 -- p_ar_header_id AR Transaction id
3513 --
3514 -- Out: x_has_difference
3515 --
3516 --
3517 -- DESIGN REFERENCES:
3518 -- GTA_REPORTS_TD.doc
3519 --
3520 -- CHANGE HISTORY:
3521 --
3522 -- 05/17/05 Donghai Wang Created.
3523 -- 11/25/05 Donghai Wang modify code to follow ebtax
3524 -- requirement
3525 -- 03/04/05 Donghai Wang Add FND Log
3526 -- 07/21/06 Donghai Wang Fix bug 5193632
3527 -- 10-Aug-2009 Yao Zhang Modified for bug# 8765631
3528 --==========================================================================
3529 PROCEDURE Get_Unmatched_Lines
3530 ( p_org_id IN NUMBER
3531 , p_ar_header_id IN NUMBER
3532 , x_has_difference OUT NOCOPY BOOLEAN
3533 )
3534 IS
3535
3536 l_org_id hr_all_organization_units.organization_id%TYPE
3537 :=p_org_id;
3538 l_ar_header_id ra_customer_trx_all.customer_trx_id%TYPE
3539 :=p_ar_header_id;
3540 l_has_difference BOOLEAN;
3541 l_api_name VARCHAR2(50)
3542 :='Get_Unmatched_Lines';
3543 l_dbg_msg VARCHAR2(100);
3544
3545 --Add following variables for fixing the bug 5193632
3546 l_source_gta VARCHAR2(100);
3547 l_source_gt VARCHAR2(100);
3548 ----------------------------------------------------
3549
3550 CURSOR c_gta_unmatched_line IS
3551 SELECT
3552 gta_header.gta_trx_number
3553 ,gta_header.tp_tax_registration_number
3554 ,gta_line.line_number
3555 ,gta_line.item_description
3556 ,gta_line.item_model
3557 ,gta_line.unit_price
3558 ,gta_line.quantity
3559 ,gta_line.uom_name
3560 ,gta_line.amount
3561 ,gta_line.tax_amount
3562 ,gta_line.tax_rate
3563 FROM
3564 ar_gta_trx_headers gta_header
3565 ,ar_gta_trx_lines gta_line
3566 WHERE gta_header.ra_trx_id=l_ar_header_id
3567 AND gta_header.source='AR'
3568 AND gta_header.status='COMPLETED'
3569 AND gta_header.latest_version_flag='Y'
3570 AND gta_line.gta_trx_header_id=gta_header.gta_trx_header_id
3571 AND gta_line.enabled_flag='Y'
3572 AND gta_line.matched_flag='N';
3573
3574 l_gta_unmatched_line c_gta_unmatched_line%ROWTYPE;
3575
3576
3577 CURSOR c_gt_unmatched_line IS
3578 SELECT
3579 gt_header.gt_invoice_number
3580 ,gt_header.tp_tax_registration_number
3581 ,gt_line.line_number
3582 ,gt_line.item_description
3583 ,gt_line.item_model
3584 ,gt_line.unit_price
3585 ,gt_line.quantity
3586 ,gt_line.uom_name
3587 ,gt_line.amount
3588 ,gt_line.tax_amount
3589 ,gt_line.tax_rate
3590 FROM
3591 ar_gta_trx_headers gt_header
3592 ,ar_gta_trx_lines gt_line
3593 WHERE gt_header.ra_trx_id=l_ar_header_id
3594 AND gt_header.source='GT'
3595 AND gt_line.gta_trx_header_id=gt_header.gta_trx_header_id
3596 AND gt_line.matched_flag='N'
3597 --Yao Zhang add for bug#8765631
3598 AND gt_line.discount_flag='0';
3599
3600 l_gt_unmatched_line c_gt_unmatched_line%ROWTYPE;
3601 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
3602 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
3603
3604 BEGIN
3605
3606 --log for debug
3607 IF (l_proc_level >= l_dbg_level)
3608 THEN
3609 FND_LOG.String(l_proc_level
3610 ,G_MODULE_PREFIX||'.'||l_api_name||'.begin'
3611 ,'Enter procedure'
3612 );
3613 END IF; --(l_proc_level >= l_dbg_level)
3614
3615 --log for debug
3616 IF (l_proc_level >= l_dbg_level)
3617 THEN
3618 FND_LOG.String(l_proc_level
3619 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
3620 ,'p_org_id '||p_org_id
3621 );
3622
3623 FND_LOG.String(l_proc_level
3624 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
3625 ,'p_ar_header_id '||p_ar_header_id
3626 );
3627 END IF; --(l_proc_level >= l_dbg_level)
3628
3629 --Fix the bug 5193632 to get translated source name
3630 --Of Golden Tax Adaptor and Golden Tax System
3631 FND_MESSAGE.Set_Name('AR','AR_GTA_SOURCE_GTA');
3632 l_source_gta:=FND_MESSAGE.Get;
3633
3634 FND_MESSAGE.Set_Name('AR','AR_GTA_SOURCE_GT');
3635 l_source_gt:=FND_MESSAGE.Get;
3636
3637
3638
3639 OPEN c_gta_unmatched_line;
3640 FETCH c_gta_unmatched_line INTO l_gta_unmatched_line;
3641
3642 WHILE c_gta_unmatched_line%FOUND
3643 LOOP
3644
3645 --INSERT unmatched GTA lines belong to current AR to temp
3646 --table ar_gta_unmatched_temp
3647 INSERT INTO ar_gta_unmatched_temp(source
3648 ,ar_header_id
3649 ,invoice_number
3650 ,tp_tax_registration_number
3651 ,line_number
3652 ,item_name
3653 ,model
3654 ,unit_price
3655 ,quantity
3656 ,uom
3657 ,line_amount
3658 ,tax_rate
3659 ,vat_line_tax
3660 )
3661 VALUES(l_source_gta
3662 ,l_ar_header_id
3663 ,l_gta_unmatched_line.gta_trx_number
3664 ,l_gta_unmatched_line.tp_tax_registration_number
3665 ,l_gta_unmatched_line.line_number
3666 ,l_gta_unmatched_line.item_description
3667 ,l_gta_unmatched_line.item_model
3668 ,l_gta_unmatched_line.unit_price
3669 ,l_gta_unmatched_line.quantity
3670 ,l_gta_unmatched_line.uom_name
3671 ,l_gta_unmatched_line.amount
3672 ,l_gta_unmatched_line.tax_rate
3673 ,l_gta_unmatched_line.tax_amount
3674 );
3675 l_has_difference:=TRUE;
3676 FETCH c_gta_unmatched_line INTO l_gta_unmatched_line;
3677 END LOOP; --c_gta_unmatched_line%FOUND
3678
3679 CLOSE c_gta_unmatched_line;
3680
3681 OPEN c_gt_unmatched_line;
3682 FETCH c_gt_unmatched_line INTO l_gt_unmatched_line;
3683
3684 WHILE c_gt_unmatched_line%FOUND
3685 LOOP
3686
3687 --INSERT unmatched GT lines belong to current AR to temp
3688 --table ar_gta_unmatched_temp
3689 INSERT INTO ar_gta_unmatched_temp(source
3690 ,ar_header_id
3691 ,invoice_number
3692 ,tp_tax_registration_number
3693 ,line_number
3694 ,item_name
3695 ,model
3696 ,unit_price
3697 ,quantity
3698 ,uom
3699 ,line_amount
3700 ,tax_rate
3701 ,vat_line_tax
3702 )
3703 VALUES(l_source_gt
3704 ,l_ar_header_id
3705 ,l_gt_unmatched_line.gt_invoice_number
3706 ,l_gt_unmatched_line.tp_tax_registration_number
3707 ,l_gt_unmatched_line.line_number
3708 ,l_gt_unmatched_line.item_description
3709 ,l_gt_unmatched_line.item_model
3710 ,l_gt_unmatched_line.unit_price
3711 ,l_gt_unmatched_line.quantity
3712 ,l_gt_unmatched_line.uom_name
3713 ,l_gt_unmatched_line.amount
3714 ,l_gt_unmatched_line.tax_rate
3715 ,l_gt_unmatched_line.tax_amount
3716 );
3717 l_has_difference:=TRUE;
3718 FETCH c_gt_unmatched_line INTO l_gt_unmatched_line;
3719 END LOOP; --c_gt_unmatched_line%FOUND
3720
3721 CLOSE c_gt_unmatched_line;
3722
3723 x_has_difference:=l_has_difference;
3724
3725
3726 --log for debug
3727 IF ( l_proc_level >= l_dbg_level)
3728 THEN
3729 FND_LOG.STRING(l_proc_level
3730 ,G_MODULE_PREFIX||'.'||l_api_name||'.end'
3731 ,'Exit procedure'
3732 );
3733 END IF; --( l_proc_level >= l_dbg_level )
3734
3735 EXCEPTION
3736 WHEN OTHERS THEN
3737 IF (l_proc_level >= l_dbg_level)
3738 THEN
3739 FND_LOG.String( l_proc_level
3740 , G_Module_Prefix || l_api_name || '. Other_Exception '
3741 , SQLCODE||':'||SQLERRM);
3742 END IF; --(l_proc_level >= l_dbg_level)
3743 RAISE;
3744 END Get_Unmatched_Lines;
3745
3746
3747 --==========================================================================
3748 -- PROCEDURE NAME:
3749 --
3750 -- Generate_Discrepancy_Xml Public
3751 --
3752 -- DESCRIPTION:
3753 --
3754 -- This Procedure is used to generate XML element content
3755 -- for disrcepancy report output
3756 --
3757 --
3758 -- PARAMETERS:
3759 -- In: p_org_id Operating unit id
3760 -- p_gta_batch_num_from GTA batch number low range
3761 -- p_gta_batch_num_to GTA batch number high range
3762 -- p_ar_transaction_type AR transaction type
3763 -- p_cust_num_from Customer Number low range
3764 -- p_cust_num_to Customer Number high range
3765 -- p_cust_name_from Customer Name low range
3766 -- p_cust_name_to Customer Name high range
3767 -- p_gl_period GL period name
3768 -- p_gl_date_from GL period date low range
3769 -- p_gl_date_to GL period date high range
3770 -- p_ar_trx_batch_from AR Transaction name low range
3771 -- p_ar_trx_batch_to AR Transaction name high range
3772 -- p_ar_trx_num_from AR Transaction number low range
3773 -- p_ar_trx_num_to AR Transaction number high range
3774 -- p_ar_trx_date_from AR Transaction date low range
3775 -- p_ar_trx_date_to AR Transaction date high range
3776 -- p_ar_doc_num_from AR transaction document
3777 -- sequence low range
3778 -- p_ar_doc_num_to AR transaction document sequence high
3779 -- range
3780 -- p_original_curr_code Currency code on AR transaction
3781 -- p_primary_sales Primary salesperson
3782 -- p_validated_lines_total the number of ar lines that have been
3783 -- validated by the report
3784 -- p_ar_matching_lines_total the number of ar lines that exactly
3785 -- match with GTA invoice
3786 -- and GT invoice
3787 -- p_ar_partially_import_total ar lines are not fully imported to GT
3788 --
3789 -- Out: x_output
3790 --
3791 -- DESIGN REFERENCES:
3792 -- GTA_REPORTS_TD.doc
3793 --
3794 -- CHANGE HISTORY:
3795 --
3796 -- 05/17/05 Donghai Wang Created.
3797 -- 11/25/05 Donghai Wang modify code to follow ebtax
3798 -- requirement
3799 -- 03/04/05 Donghai Wang Add FND Log
3800 -- 06/28/06 Donghai Wang Fix the bug 5263009
3801 -- 07/21/06 Donghai Wang Fix the bug 5381833
3802 -- 09/14/06 Donghai Wang format output date to XSD date
3803 -- format to fix the bug 5521629
3804 -- 10-Aug-2009 Yao Zhang Modified for bug 8765631
3805 --==========================================================================
3806 PROCEDURE Generate_Discrepancy_Xml
3807 ( p_org_id IN NUMBER
3808 , p_gta_batch_num_from IN VARCHAR2
3809 , p_gta_batch_num_to IN VARCHAR2
3810 , p_ar_transaction_type IN NUMBER
3811 , p_cust_num_from IN VARCHAR2
3812 , p_cust_num_to IN VARCHAR2
3813 , p_cust_name_id IN NUMBER
3814 , p_gl_period IN VARCHAR2
3815 , p_gl_date_from IN DATE
3816 , p_gl_date_to IN DATE
3817 , p_ar_trx_batch_from IN VARCHAR2
3818 , p_ar_trx_batch_to IN VARCHAR2
3819 , p_ar_trx_num_from IN VARCHAR2
3820 , p_ar_trx_num_to IN VARCHAR2
3821 , p_ar_trx_date_from IN DATE
3822 , p_ar_trx_date_to IN DATE
3823 , p_ar_doc_num_from IN VARCHAR2
3824 , p_ar_doc_num_to IN VARCHAR2
3825 , p_original_curr_code IN VARCHAR2
3826 , p_primary_sales IN NUMBER
3827 , p_validated_lines_total IN NUMBER
3828 , p_ar_matching_lines_total IN NUMBER
3829 , p_ar_partially_import_total IN NUMBER
3830 , x_output OUT NOCOPY XMLTYPE
3831 )
3832 IS
3833 l_operating_unit hr_operating_units.name%TYPE;
3834 l_customer_name hz_parties.party_name%TYPE;
3835 l_primary_sales_name ra_salesreps_all.name%TYPE;
3836 l_customer_id hz_cust_accounts.cust_account_id%TYPE
3837 :=p_cust_name_id;
3838 l_ar_header_id ra_customer_trx_all.customer_trx_id%TYPE;
3839 l_gta_batch_num_from ar_gta_trx_headers_all.gta_batch_number%TYPE
3840 :=p_gta_batch_num_from;
3841 l_gta_batch_num_to ar_gta_trx_headers_all.gta_batch_number%TYPE
3842 :=p_gta_batch_num_to;
3843 l_ar_transaction_type ra_cust_trx_types_all.name%TYPE;
3844 l_cust_num_from hz_cust_accounts.account_number%TYPE
3845 :=p_cust_num_from;
3846 l_cust_num_to hz_cust_accounts.account_number%TYPE
3847 :=p_cust_num_to;
3848 l_gl_period gl_periods.period_name%TYPE
3849 :=p_gl_period;
3850 l_ar_trx_batch_from ra_batches_all.name%TYPE
3851 :=p_ar_trx_batch_from;
3852 l_ar_trx_batch_to ra_batches_all.name%TYPE
3853 :=p_ar_trx_batch_to;
3854 l_ar_trx_num_from ra_customer_trx_all.trx_number%TYPE
3855 :=p_ar_trx_num_from;
3856 l_ar_trx_num_to ra_customer_trx_all.trx_number%TYPE
3857 :=p_ar_trx_num_to;
3858 l_ar_doc_num_from VARCHAR2(15)
3859 :=p_ar_doc_num_from;
3860 l_ar_doc_num_to VARCHAR2(15)
3861 :=p_ar_doc_num_to;
3862 l_original_curr_code fnd_currencies.currency_code%TYPE
3863 :=p_original_curr_code;
3864 l_gl_date_from_f VARCHAR2(50);
3865 l_gl_date_to_f VARCHAR2(50);
3866 l_ar_trx_date_from_f VARCHAR2(50);
3867 l_ar_trx_date_to_f VARCHAR2(50);
3868 l_report_date VARCHAR2(50);
3869 l_ar_diff_count NUMBER;
3870 l_missing_artrx_count NUMBER;
3871 l_dbg_msg VARCHAR2(100);
3872 l_no_data_found_msg VARCHAR2(500);
3873 l_validated_lines_total NUMBER
3874 :=p_validated_lines_total;
3875 l_ar_matching_lines_total NUMBER
3876 :=p_ar_matching_lines_total;
3877 l_ar_partially_import_total NUMBER
3878 :=p_ar_partially_import_total;
3879 l_ar_line_notmatching_total NUMBER;
3880 l_ar_missingtrx_total NUMBER;
3881 l_parameter_xml XMLTYPE;
3882 l_summary_xml XMLTYPE;
3883 l_report_xml XMLTYPE;
3884 l_ar_trx_header_id ra_customer_trx_all.customer_trx_id%TYPE;
3885 l_gta_header_xml_tmp XMLTYPE;
3886 l_gta_header_xml XMLTYPE;
3887 l_gta_line_xml_tmp XMLTYPE;
3888 l_gta_line_xml XMLTYPE;
3889 l_missing_line_xml_tmp XMLTYPE;
3890 l_missing_line_xml XMLTYPE;
3891 l_unmatched_line_xml_tmp XMLTYPE;
3892 l_unmatched_line_xml XMLTYPE;
3893 l_xml_null XMLTYPE;
3894 l_ar_invoice_xml XMLTYPE;
3895 l_ar_invoice_xml_tmp XMLTYPE;
3896 l_missing_artrx_xml_tmp XMLTYPE;
3897 l_missing_artrx_xml XMLTYPE;
3898 l_base_currency ar_gta_system_parameters_all.gt_currency_code%TYPE;
3899
3900 l_api_name VARCHAR2(50)
3901 :='Generate_Discrepancy_Xml';
3902 l_error_msg VARCHAR2(4000);
3903 l_no_char VARCHAR2(1)
3904 :='N';
3905 l_ar_trx_date VARCHAR2(50);
3906 --Yao Zhang add for bug#8765631
3907 l_consolidate_count NUMBER;
3908 l_description VARCHAR2(1000);
3909 --Yao Zhang add end
3910
3911
3912 CURSOR c_operating_unit IS
3913 SELECT
3914 otl.name
3915 FROM
3916 hr_all_organization_units o
3917 ,hr_all_organization_units_tl otl
3918 WHERE o.organization_id = otl.organization_id
3919 AND otl.language = userenv('LANG')
3920 AND o.organization_id = p_org_id;
3921
3922 CURSOR c_ar_transaction_type IS
3923 SELECT
3924 name
3925 FROM
3926 ra_cust_trx_types_all
3927 WHERE cust_trx_type_id=p_ar_transaction_type;
3928
3929 CURSOR c_customer_name IS
3930 SELECT
3931 hp.party_name
3932 FROM
3933 hz_cust_accounts hca
3934 ,hz_parties hp
3935 WHERE hca.cust_account_id=l_customer_id
3936 AND hp.party_id=hca.party_id;
3937
3938 CURSOR c_primary_salesrep_name IS
3939 SELECT
3940 name
3941 FROM
3942 ra_salesreps_all
3943 WHERE salesrep_id=p_primary_sales;
3944
3945
3946 CURSOR c_ar_diff_count IS
3947 SELECT
3948 count(*)
3949 FROM
3950 ar_gta_ar_difference_temp;
3951
3952 CURSOR c_missing_artrx_count IS
3953 SELECT
3954 COUNT(*)
3955 FROM
3956 ar_gta_missing_artrx_temp;
3957
3958
3959 CURSOR c_ar_difference IS
3960 SELECT
3961 adt.customer_trx_id
3962 ,adt.trx_number
3963 ,adt.trx_date
3964 ,adt.customer_name
3965 ,adt.invoice_currency_code
3966 FROM
3967 ar_gta_ar_difference_temp adt;
3968
3969 l_ar_difference c_ar_difference%ROWTYPE;
3970
3971 /*Comment this part out for fix the bug 5263009
3972 CURSOR c_ar_line_notmatching IS
3973 SELECT
3974 COUNT(DISTINCT ar_line_id)
3975 FROM
3976 ar_gta_difference_temp
3977 WHERE TYPE='LINE';
3978 */
3979
3980 CURSOR c_gta_header_xml IS
3981 SELECT XMLELEMENT("Difference"
3982 ,XMLFOREST(attribute AS "ARAttribute"
3983 ,ar_value AS "ARValue"
3984 ,gta_invoice_num AS "GTAInvoiceNum"
3985 ,gta_value AS "GTAValue"
3986 ,gt_invoice_num AS "GT_InvoiceNum"
3987 ,gt_value AS "GT_Value"
3988 ,discrepancy AS "Discrepancy"
3989 )
3990 )
3991 FROM
3992 ar_gta_difference_temp
3993 WHERE ar_header_id=l_ar_header_id
3994 AND type='HEADER';
3995
3996
3997
3998 CURSOR c_gta_line_xml IS
3999 SELECT
4000 XMLELEMENT("Difference"
4001 ,XMLFOREST(attribute AS "ARAttribute"
4002 ,ar_line_num AS "ARLineNum"
4003 ,ar_value AS "ARValue"
4004 ,gta_invoice_num AS "GTAInvoiceNum"
4005 ,gta_line_num AS "GTALineNum"
4006 ,gta_value AS "GTAValue"
4007 ,gt_invoice_num AS "GT_InvoiceNum"
4008 ,gt_line_num AS "GT_LineNum"
4009 ,gt_value AS "GT_Value"
4010 )
4011 )
4012 FROM
4013 ar_gta_difference_temp
4014 WHERE ar_header_id=l_ar_header_id
4015 AND type='LINE';
4016
4017 --Update the cursor for fixing the bug 5381833
4018 --Add the the column "ar_line_num AS "ARLineNum""
4019 --in the XMLFOREST function
4020 CURSOR c_missing_line_xml IS
4021 SELECT
4022 XMLELEMENT("Difference"
4023 ,XMLFOREST(ar_line_num AS "ARLineNum"
4024 ,gta_invoice_num AS "GTAInvoiceNum"
4025 ,gta_line_num AS "GTALineNum"
4026 ,gt_invoice_num AS "GT_InvoiceNum"
4027 )
4028 )
4029 FROM
4030 ar_gta_difference_temp
4031 WHERE ar_header_id=l_ar_header_id
4032 AND TYPE='MISSING_AR_LINE';
4033
4034 CURSOR c_unmatched_line_xml IS
4035 SELECT
4036 XMLELEMENT("Line"
4037 ,XMLFOREST(source AS "Source"
4038 ,invoice_number AS "InvoiceNum"
4039 ,tp_tax_registration_number AS "TaxRegNum"
4040 ,line_number AS "LineNum"
4041 ,item_name AS "ItemName"
4042 ,model AS "Model"
4043 ,unit_price AS "UnitPrice"
4044 ,quantity AS "Quantity"
4045 ,uom AS "UOM"
4046 ,line_amount AS "LineAmount"
4047 ,tax_rate AS "TaxRate"
4048 ,vat_line_tax AS "VatLineTax"
4049 )
4050 )
4051
4052 FROM
4053 ar_gta_unmatched_temp
4054 WHERE ar_header_id=l_ar_header_id
4055 ORDER BY source DESC;
4056
4057 CURSOR c_missing_artrx_xml IS
4058 SELECT
4059 XMLELEMENT("Invoice"
4060 ,XMLFOREST(record_number AS "InvoiceNumber"
4061 ,ar_trx_number AS "OriginalARTrxNum"
4062 ,gta_trx_number AS "GTAInvoiceNumber"
4063 ,gt_invoice_number AS "GT_InvoiceNum"
4064 ,gt_invoice_amount AS "GT_InvoiceAmount"
4065 )
4066 )
4067 FROM
4068 ar_gta_missing_artrx_temp;
4069
4070 CURSOR c_base_currency IS
4071 SELECT
4072 gt_currency_code
4073 FROM
4074 ar_gta_system_parameters_all
4075 WHERE
4076 org_id=p_org_id;
4077
4078
4079 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
4080 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
4081
4082
4083 BEGIN
4084
4085 --log for debug
4086 IF( l_proc_level >= l_dbg_level)
4087 THEN
4088 FND_LOG.STRING(l_proc_level
4089 ,G_MODULE_PREFIX||'.'||l_api_name||'.begin'
4090 ,'Enter procedure');
4091 END IF; --( l_proc_level >= l_dbg_level)
4092
4093
4094 --log for debug
4095 IF( l_proc_level >= l_dbg_level)
4096 THEN
4097 FND_LOG.STRING(l_proc_level
4098 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4099 ,'p_org_id '||p_org_id);
4100
4101 FND_LOG.STRING(l_proc_level
4102 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4103 ,'p_gta_batch_num_from '||p_gta_batch_num_from);
4104
4105 FND_LOG.STRING(l_proc_level
4106 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4107 ,'p_gta_batch_num_to '||p_gta_batch_num_to);
4108
4109 FND_LOG.STRING(l_proc_level
4110 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4111 ,'p_ar_transaction_type '||p_ar_transaction_type);
4112
4113 FND_LOG.STRING(l_proc_level
4114 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4115 ,'p_cust_num_from '||p_cust_num_from);
4116
4117 FND_LOG.STRING(l_proc_level
4118 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4119 ,'p_cust_num_to '||p_cust_num_to);
4120
4121 FND_LOG.STRING(l_proc_level
4122 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4123 ,'p_cust_name_id '||p_cust_name_id);
4124
4125 FND_LOG.STRING(l_proc_level
4126 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4127 ,'p_gl_period '||p_gl_period);
4128
4129 FND_LOG.STRING(l_proc_level
4130 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4131 ,'p_gl_date_from '||p_gl_date_from);
4132
4133 FND_LOG.STRING(l_proc_level
4134 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4135 ,'p_gl_date_to '||p_gl_date_to);
4136
4137 FND_LOG.STRING(l_proc_level
4138 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4139 ,'p_ar_trx_batch_from '||p_ar_trx_batch_from);
4140
4141 FND_LOG.STRING(l_proc_level
4142 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4143 ,'p_ar_trx_batch_to '||p_ar_trx_batch_to);
4144
4145 FND_LOG.STRING(l_proc_level
4146 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4147 ,'p_ar_trx_num_from '||p_ar_trx_num_from);
4148
4149 FND_LOG.STRING(l_proc_level
4150 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4151 ,'p_ar_trx_num_to '||p_ar_trx_num_to);
4152
4153 FND_LOG.STRING(l_proc_level
4154 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4155 ,'p_ar_trx_date_from '||p_ar_trx_date_from);
4156
4157 FND_LOG.STRING(l_proc_level
4158 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4159 ,'p_ar_trx_date_to '||p_ar_trx_date_to);
4160
4161 FND_LOG.STRING(l_proc_level
4162 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4163 ,'p_ar_doc_num_from '||p_ar_doc_num_from);
4164
4165 FND_LOG.STRING(l_proc_level
4166 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4167 ,'p_ar_doc_num_to '||p_ar_doc_num_to);
4168
4169 FND_LOG.STRING(l_proc_level
4170 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4171 ,'p_original_curr_code '||p_original_curr_code);
4172
4173 FND_LOG.STRING(l_proc_level
4174 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4175 ,'p_primary_sales '||p_primary_sales);
4176
4177 FND_LOG.STRING(l_proc_level
4178 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4179 ,'p_validated_lines_total '||p_validated_lines_total);
4180
4181 FND_LOG.STRING(l_proc_level
4182 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4183 ,'p_ar_matching_lines_total '||p_ar_matching_lines_total);
4184
4185 FND_LOG.STRING(l_proc_level
4186 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4187 ,'p_ar_partially_import_total '
4188 ||p_ar_partially_import_total
4189 );
4190 END IF; --( l_proc_level >= l_dbg_level)
4191
4192
4193 --
4194 --prepare parameter
4195 --
4196
4197 --Get Operating Unit Name
4198 OPEN c_operating_unit;
4199 FETCH c_operating_unit INTO l_operating_unit;
4200 CLOSE c_operating_unit;
4201
4202 --Get Custome Name
4203 OPEN c_customer_name;
4204 FETCH c_customer_name INTO l_customer_name;
4205 CLOSE c_customer_name;
4206
4207 --Get AR Transaction Type name
4208 OPEN c_ar_transaction_type;
4209 FETCH c_ar_transaction_type INTO l_ar_transaction_type;
4210 CLOSE c_ar_transaction_type;
4211
4212 --Get Primary Salesperson Name
4213 OPEN c_primary_salesrep_name;
4214 FETCH c_primary_salesrep_name INTO l_primary_sales_name;
4215 CLOSE c_primary_salesrep_name;
4216
4217 --Bug 5521629
4218 --Format date to XSD Date format
4219 l_gl_date_from_f:=AR_GTA_TRX_UTIL.To_Xsd_Date_String(p_gl_date_from);
4220 l_gl_date_to_f:=AR_GTA_TRX_UTIL.To_Xsd_Date_String(p_gl_date_to);
4221 l_ar_trx_date_from_f:=AR_GTA_TRX_UTIL.To_Xsd_Date_String(p_ar_trx_date_from);
4222 l_ar_trx_date_to_f:=AR_GTA_TRX_UTIL.To_Xsd_Date_String(p_ar_trx_date_to);
4223 l_report_date:=AR_GTA_TRX_UTIL.To_Xsd_Date_String(SYSDATE);
4224
4225 --generate xml elements for Parameters
4226 SELECT
4227 xmlforest( l_operating_unit AS "OperationUnit"
4228 ,l_gta_batch_num_from AS "BatchNumFrom"
4229 ,l_gta_batch_num_to AS "BatchNumTo"
4230 ,l_ar_transaction_type AS "TransactionType"
4231 ,l_cust_num_from AS "CustomerNumberFrom"
4232 ,l_cust_num_to AS "CustomerNumberTo"
4233 ,l_customer_name AS "CustomerName"
4234 ,l_gl_period AS "GLPeriod"
4235 ,l_gl_date_from_f AS "GLDateFrom"
4236 ,l_gl_date_to_f AS "GLDateTo"
4237 ,l_ar_trx_batch_from AS "TransactionBatchFrom"
4238 ,l_ar_trx_batch_to AS "TransactionBatchTo"
4239 ,l_ar_trx_num_from AS "TransactionNumberFrom"
4240 ,l_ar_trx_num_to AS "TransactionNumberTo"
4241 ,l_ar_trx_date_from_f AS "TransactionDateFrom"
4242 ,l_ar_trx_date_to_f AS "TransactionDateTo"
4243 ,l_ar_doc_num_from AS "DocNumberFrom"
4244 ,l_ar_doc_num_to AS "DocNumberTo"
4245 ,l_original_curr_code AS "OriginalCurrency"
4246 ,l_primary_sales_name AS "PrimarySalesPerson"
4247 )
4248 INTO
4249 l_parameter_xml
4250 FROM dual;
4251
4252 --To calculate total number of AR transactions that have discrepancy
4253 OPEN c_ar_diff_count;
4254 FETCH c_ar_diff_count INTO l_ar_diff_count;
4255 CLOSE c_ar_diff_count;
4256
4257 --To Calculate total number of GTA invoice that miss AR transaction
4258 OPEN c_missing_artrx_count;
4259 FETCH c_missing_artrx_count INTO l_missing_artrx_count;
4260 CLOSE c_missing_artrx_count;
4261
4262 IF (l_ar_diff_count=0) AND
4263 (l_missing_artrx_count=0) --No Data Found
4264 THEN
4265 --Get Message For No Data Found
4266 FND_MESSAGE.Set_Name('AR','AR_GTA_NO_DATA_FOUND');
4267 l_no_data_found_msg:=FND_MESSAGE.Get;
4268
4269 --To generat report xml with message no data found
4270 SELECT
4271 xmlelement("DiscrepancyReport"
4272 ,xmlforest(l_report_date AS "RepDate"
4273 ,'N' AS "ReportFailed"
4274 ,l_parameter_xml AS "Parameters"
4275 ,'Y' AS "FailedWithParameters"
4276 ,l_no_data_found_msg AS "FailedMsgWithParameters"
4277 )
4278 )
4279 INTO
4280 l_report_xml
4281 FROM
4282 dual;
4283
4284 ELSE
4285 --
4286 --Generate xml for summary section
4287 --
4288
4289 --To calculate total AR transaction lines with discrepancy
4290 --within AR, GTA and GT
4291 /*Comment this part out to fix the bug 5263009
4292 OPEN c_ar_line_notmatching;
4293 FETCH c_ar_line_notmatching INTO l_ar_line_notmatching_total;
4294 CLOSE c_ar_line_notmatching;*/
4295
4296 --Add this part to fix the bug 5263009
4297 --To calculate number of AR lines that don't match with related GT lines
4298 l_ar_line_notmatching_total:=l_validated_lines_total-l_ar_matching_lines_total;
4299
4300
4301 --To calculate total AR transactions originally existing, but
4302 --being deleted after transferred into GTA
4303
4304 l_ar_missingtrx_total:=l_missing_artrx_count;
4305
4306
4307 SELECT
4308 XMLFOREST(l_validated_lines_total AS "NumOfARLines"
4309 ,l_ar_matching_lines_total AS "NumOfLinesMatch"
4310 ,l_ar_line_notmatching_total AS "NumOfLinesNoMatch"
4311 ,l_ar_partially_import_total AS "NumOfNotToGT"
4312 ,l_ar_missingtrx_total AS "NumOfMissingAR"
4313 )
4314 INTO
4315 l_summary_xml
4316 FROM
4317 dual;
4318
4319 --To get base currency
4320 OPEN c_base_currency;
4321 FETCH c_base_currency INTO l_base_currency;
4322 CLOSE c_base_currency;
4323
4324
4325 --To Genrete XML for HeaderLevel by each AR tranaction
4326 OPEN c_ar_difference;
4327 FETCH c_ar_difference INTO l_ar_difference;
4328 WHILE c_ar_difference%FOUND
4329 LOOP
4330 l_ar_header_id:=l_ar_difference.customer_trx_id;
4331 l_description:=NULL;
4332 --the following code is added by Yao Zhang for bug#8765631
4333 SELECT COUNT(*)
4334 INTO l_consolidate_count
4335 FROM ar_gta_trx_headers
4336 WHERE ra_trx_id = l_ar_header_id
4337 AND status = 'CONSOLIDATED';
4338 IF l_consolidate_count>0
4339 THEN
4340 fnd_message.set_name('AR'
4341 ,'AR_GTA_DISC_AR_GT_CON');
4342 l_description:=fnd_message.get();
4343 END IF;
4344 IF l_ar_difference.invoice_currency_code IS NULL
4345 THEN
4346 fnd_message.set_name('AR'
4347 ,'AR_GTA_DISC_GTA_GT_CON');
4348 l_description:=fnd_message.get();
4349 END IF;
4350 --Yao Zhang add end;
4351
4352 --To empty xml variable
4353 l_gta_header_xml:=l_xml_null;
4354 l_gta_header_xml_tmp:=l_xml_null;
4355 OPEN c_gta_header_xml;
4356 FETCH c_gta_header_xml INTO l_gta_header_xml_tmp;
4357 WHILE c_gta_header_xml%FOUND
4358 LOOP
4359 IF l_gta_header_xml IS NULL
4360 THEN
4361 l_gta_header_xml:=l_gta_header_xml_tmp;
4362 ELSE
4363 SELECT
4364 XMLCONCAT(l_gta_header_xml
4365 ,l_gta_header_xml_tmp
4366 )
4367 INTO
4368 l_gta_header_xml
4369 FROM
4370 dual;
4371 END IF; --l_gta_header_xml IS NULL
4372 FETCH c_gta_header_xml INTO l_gta_header_xml_tmp;
4373 END LOOP; --c_gta_header_xml%FOUND
4374 CLOSE c_gta_header_xml;
4375
4376
4377 --To empty xml variable
4378 l_gta_line_xml:=l_xml_null;
4379 l_gta_line_xml_tmp:=l_xml_null;
4380
4381 --To Generate XML for LineLevel by each AR transaction
4382 OPEN c_gta_line_xml;
4383 FETCH c_gta_line_xml INTO l_gta_line_xml_tmp;
4384 WHILE c_gta_line_xml%FOUND
4385 LOOP
4386 IF l_gta_line_xml IS NULL
4387 THEN
4388 l_gta_line_xml:=l_gta_line_xml_tmp;
4389 ELSE
4390 SELECT
4391 XMLCONCAT(l_gta_line_xml
4392 ,l_gta_line_xml_tmp
4393 )
4394 INTO
4395 l_gta_line_xml
4396 FROM
4397 dual;
4398 END IF; --l_gta_line_xml IS NULL
4399 FETCH c_gta_line_xml INTO l_gta_line_xml_tmp;
4400 END LOOP; --c_gta_line_xml%FOUND
4401 CLOSE c_gta_line_xml;
4402
4403 --To empty xml variable
4404 l_missing_line_xml:=l_xml_null;
4405 l_missing_line_xml_tmp:=l_xml_null;
4406
4407 --To Generate XML for Missing AR Line by each AR transaction
4408 OPEN c_missing_line_xml;
4409 FETCH c_missing_line_xml INTO l_missing_line_xml_tmp;
4410 WHILE c_missing_line_xml%FOUND
4411 LOOP
4412 IF l_missing_line_xml IS NULL
4413 THEN
4414 l_missing_line_xml:=l_missing_line_xml_tmp;
4415 ELSE
4416 SELECT
4417 XMLCONCAT(l_missing_line_xml
4418 ,l_missing_line_xml_tmp
4419 )
4420 INTO
4421 l_missing_line_xml
4422 FROM
4423 dual;
4424 END IF; --l_missing_line_xml IS NULL
4425 FETCH c_missing_line_xml INTO l_missing_line_xml_tmp;
4426 END LOOP; --c_missing_line_xml%FOUND
4427 CLOSE c_missing_line_xml;
4428
4429 --To empty xml variable
4430 l_unmatched_line_xml:=l_xml_null;
4431 l_unmatched_line_xml_tmp:=l_xml_null;
4432
4433 --To Generate XML for Missing AR Line by each AR transaction
4434 OPEN c_unmatched_line_xml;
4435 FETCH c_unmatched_line_xml INTO l_unmatched_line_xml_tmp;
4436 WHILE c_unmatched_line_xml%FOUND
4437 LOOP
4438 IF l_unmatched_line_xml IS NULL
4439 THEN
4440 l_unmatched_line_xml:=l_unmatched_line_xml_tmp;
4441 ELSE
4442 SELECT
4443 XMLCONCAT(l_unmatched_line_xml
4444 ,l_unmatched_line_xml_tmp
4445 )
4446 INTO
4447 l_unmatched_line_xml
4448 FROM
4449 dual;
4450 END IF; --l_unmatched_line_xml IS NULL
4451 FETCH c_unmatched_line_xml INTO l_unmatched_line_xml_tmp;
4452 END LOOP; --c_unmatched_line_xml%FOUND
4453 CLOSE c_unmatched_line_xml;
4454
4455 --To Generate XML for current AR Trnasaction
4456
4457
4458 --Bug 5521629
4459 --Format date to XSD Date formate
4460 l_ar_trx_date:=AR_GTA_TRX_UTIL.To_Xsd_Date_String(l_ar_difference.trx_date);
4461 SELECT
4462 XMLELEMENT("Invoice"
4463 ,XMLFOREST
4464 (l_ar_difference.trx_number AS "ARInvoiceNo"
4465 ,l_ar_trx_date AS "ARDate"
4466 ,l_ar_difference.customer_name AS "Customer"
4467 ,l_ar_difference.invoice_currency_code AS "OriginalCurrency"
4468 ,l_description AS "Description"--Yao Zhang add for bug#8765631
4469 ,l_gta_header_xml AS "HeaderLevel"
4470 ,l_gta_line_xml AS "LineLevel"
4471 ,l_missing_line_xml AS "MissARLine"
4472 ,l_unmatched_line_xml AS "UnmatchedLines"
4473 )
4474 )
4475 INTO l_ar_invoice_xml_tmp
4476 FROM
4477 dual;
4478
4479 IF l_ar_invoice_xml IS NULL
4480 THEN
4481 l_ar_invoice_xml:=l_ar_invoice_xml_tmp;
4482 ELSE
4483 SELECT
4484 XMLCONCAT(l_ar_invoice_xml
4485 ,l_ar_invoice_xml_tmp
4486 )
4487 INTO
4488 l_ar_invoice_xml
4489 FROM dual;
4490 END IF; --l_ar_invoice_xml IS NULL
4491 FETCH c_ar_difference INTO l_ar_difference;
4492 END LOOP; --c_ar_difference%found
4493 CLOSE c_ar_difference;
4494
4495
4496 --To generate xml for missed AR transacitons
4497 OPEN c_missing_artrx_xml;
4498 FETCH c_missing_artrx_xml INTO l_missing_artrx_xml_tmp;
4499 WHILE c_missing_artrx_xml%FOUND
4500 LOOP
4501 IF l_missing_artrx_xml IS NULL
4502 THEN
4503 l_missing_artrx_xml:=l_missing_artrx_xml_tmp;
4504 ELSE
4505 SELECT
4506 XMLCONCAT(l_missing_artrx_xml
4507 ,l_missing_artrx_xml_tmp
4508 )
4509 INTO
4510 l_missing_artrx_xml
4511 FROM
4512 dual;
4513 END IF;--l_missing_artrx_xml IS NULL
4514
4515 FETCH c_missing_artrx_xml INTO l_missing_artrx_xml_tmp;
4516 END LOOP;--c_missing_artrx_xml%FOUND
4517 CLOSE c_missing_artrx_xml;
4518
4519 --To Generate xml for whole report
4520 SELECT
4521 XMLELEMENT("DiscrepancyReport"
4522 ,XMLFOREST(l_report_date AS "RepDate"
4523 ,l_no_char AS "ReportFailed"
4524 ,l_parameter_xml AS "Parameters"
4525 ,l_no_char AS "FailedWithParameters"
4526 ,l_summary_xml AS "Summary"
4527 ,l_base_currency AS "RepCurr"
4528 ,l_ar_invoice_xml AS "Invoices"
4529 ,l_missing_artrx_xml AS "MissingInvoices"
4530 )
4531 )
4532 INTO
4533 l_report_xml
4534 FROM
4535 dual;
4536 END IF; -- (l_ar_diff_count=0) AND (l_missing_artrx_count=0)
4537
4538
4539
4540 x_output:=l_report_xml;
4541
4542 --log for debug
4543 IF( l_proc_level >= l_dbg_level )
4544 THEN
4545 FND_LOG.STRING(l_proc_level
4546 ,G_MODULE_PREFIX||'.'||l_api_name||'.end'
4547 ,'Exit procedure');
4548 END IF; --( l_proc_level >= l_dbg_level)
4549
4550
4551 EXCEPTION
4552 WHEN OTHERS THEN
4553 IF (l_proc_level >= l_dbg_level)
4554 THEN
4555 l_error_msg:=SQLCODE||':'||SQLERRM;
4556 FND_LOG.String( l_proc_level
4557 , G_Module_Prefix || l_api_name || '. Other_Exception '
4558 , l_error_msg);
4559
4560
4561
4562 END IF; --(l_proc_level >= l_dbg_level)
4563 RAISE;
4564 END Generate_Discrepancy_Xml;
4565
4566 --==========================================================================
4567 -- PROCEDURE NAME:
4568 --
4569 -- Generate_Discrepancy_Rep Public
4570 --
4571 -- DESCRIPTION:
4572 --
4573 -- This Procedure Generate Discrepancy Report Data
4574 --
4575 --
4576 -- PARAMETERS:
4577 -- In: p_org_id Operating unit id
4578 -- p_gta_batch_num_from GTA batch number low range
4579 -- p_gta_batch_num_to GTA batch number high range
4580 -- p_ar_transaction_type AR transaction type
4581 -- p_cust_num_from Customer Number low range
4582 -- p_cust_num_to Customer Number high range
4583 -- p_cust_name_from Customer Name low range
4584 -- p_cust_name_to Customer Name high range
4585 -- p_gl_period GL period name
4586 -- p_gl_date_from GL period date low range
4587 -- p_gl_date_to GL period date high range
4588 -- p_ar_trx_batch_from AR Transaction name low range
4589 -- p_ar_trx_batch_to AR Transaction name high range
4590 -- p_ar_trx_num_from AR Transaction number low range
4591 -- p_ar_trx_num_to AR Transaction number high range
4592 -- p_ar_trx_date_from AR Transaction date low range
4593 -- p_ar_trx_date_to AR Transaction date high range
4594 -- p_ar_doc_num_from AR transaction document sequence
4595 -- low range
4596 -- p_ar_doc_num_to AR transaction document sequence
4597 -- high range
4598 -- p_original_curr_code Currency code on AR transaction
4599 -- p_primary_sales Primary salesperson
4600 --
4601 -- Out:
4602 --
4603 -- DESIGN REFERENCES:
4604 -- GTA_REPORTS_TD.doc
4605 --
4606 -- CHANGE HISTORY:
4607 --
4608 -- 05/17/05 Donghai Wang Created.
4609 -- 03/04/05 Donghai Wang Add FND Log
4610 -- 05-Aug-2009 Yao Zhang Fix bug#8765631 Modified
4611 --==========================================================================
4612 PROCEDURE Generate_Discrepancy_Rep
4613 ( p_org_id IN NUMBER
4614 , p_gta_batch_num_from IN VARCHAR2
4615 , p_gta_batch_num_to IN VARCHAR2
4616 , p_ar_transaction_type IN NUMBER
4617 , p_cust_num_from IN VARCHAR2
4618 , p_cust_num_to IN VARCHAR2
4619 , p_cust_name_id IN NUMBER
4620 , p_gl_period IN VARCHAR2
4621 , p_gl_date_from IN VARCHAR2
4622 , p_gl_date_to IN VARCHAR2
4623 , p_ar_trx_batch_from IN VARCHAR2
4624 , p_ar_trx_batch_to IN VARCHAR2
4625 , P_ar_trx_num_from IN VARCHAR2
4626 , p_ar_trx_num_to IN VARCHAR2
4627 , p_ar_trx_date_from IN VARCHAR2
4628 , p_ar_trx_date_to IN VARCHAR2
4629 , p_ar_doc_num_from IN VARCHAR2
4630 , p_ar_doc_num_to IN VARCHAR2
4631 , p_original_curr_code IN VARCHAR2
4632 , p_primary_sales IN NUMBER
4633 )
4634 IS
4635 l_org_id NUMBER
4636 :=p_org_id;
4637 l_gta_batch_num_from VARCHAR2(30)
4638 :=p_gta_batch_num_from;
4639 l_gta_batch_num_to VARCHAR2(30)
4640 :=p_gta_batch_num_to;
4641 l_ar_transaction_type NUMBER
4642 :=p_ar_transaction_type;
4643 l_cust_num_from VARCHAR2(30)
4644 :=p_cust_num_from;
4645 l_cust_num_to VARCHAR2(30)
4646 :=p_cust_num_to;
4647 l_cust_id NUMBER(15)
4648 :=p_cust_name_id;
4649 l_gl_period VARCHAR2(30)
4650 :=p_gl_period;
4651 l_gl_date_from VARCHAR2(20)
4652 :=p_gl_date_from;
4653 l_gl_date_from_d DATE;
4654 l_gl_date_to VARCHAR2(20)
4655 :=p_gl_date_to;
4656 l_gl_date_to_d DATE;
4657 l_ar_trx_batch_from VARCHAR2(30)
4658 :=p_ar_trx_batch_from;
4659 l_ar_trx_batch_to VARCHAR2(30)
4660 :=p_ar_trx_batch_to;
4661 l_ar_trx_num_from VARCHAR2(30)
4662 :=p_ar_trx_num_from;
4663 l_ar_trx_num_to VARCHAR2(30)
4664 :=p_ar_trx_num_to;
4665 l_ar_trx_date_from VARCHAR2(20)
4666 :=p_ar_trx_date_from;
4667 l_ar_trx_date_from_d DATE;
4668 l_ar_trx_date_to VARCHAR2(20)
4669 :=p_ar_trx_date_to;
4670 l_ar_trx_date_to_d DATE;
4671 l_ar_doc_num_from VARCHAR2(30)
4672 :=p_ar_doc_num_from;
4673 l_ar_doc_num_to VARCHAR2(30)
4674 :=p_ar_doc_num_from;
4675 l_original_curr_code VARCHAR2(30)
4676 :=p_original_curr_code ;
4677 l_primary_sales NUMBER
4678 :=p_primary_sales;
4679 l_ar_trx_header_id NUMBER;
4680 l_header_difference BOOLEAN;
4681 l_line_difference BOOLEAN;
4682 l_has_unmatched BOOLEAN;
4683 l_xml_output XMLTYPE;
4684 l_ar_trx_number ra_customer_trx_all.trx_number%TYPE;
4685 l_ar_trx_date ra_customer_trx_all.trx_date%TYPE;
4686 l_ar_customer_name hz_parties.party_name%TYPE;
4687 l_ar_currency_code ra_customer_trx_all.invoice_currency_code%TYPE;
4688 l_missing_artrx_seq NUMBER;
4689 l_gta_trx_number_missing VARCHAR2(2000);
4690 l_gt_invoice_number_missing VARCHAR2(2000);
4691 l_gt_invoice_amount_missing NUMBER;
4692 l_ar_trx_number_missing ra_customer_trx_all.trx_number%TYPE;
4693
4694 l_delimiter VARCHAR2(1)
4695 :=',';
4696 l_validated_lines NUMBER;
4697 l_validated_lines_total NUMBER;
4698 l_ar_matching_lines NUMBER;
4699 l_ar_matching_lines_total NUMBER;
4700 l_ar_partially_import NUMBER;
4701 l_ar_partially_import_total NUMBER;
4702
4703 l_api_name VARCHAR2(50)
4704 :='Generate_Discrepancy_Rep';
4705 l_dbg_msg VARCHAR2(100);
4706
4707 l_gta_consolidation_flag ar_gta_trx_headers_all.consolidation_flag%TYPE;
4708
4709
4710 CURSOR c_trx_header IS
4711 SELECT DISTINCT
4712 gta.ra_trx_id
4713 ,ct.trx_number
4714 ,ct.trx_date
4715 ,rac_bill_party.party_name customer_name
4716 ,ct.invoice_currency_code
4717 FROM
4718 ar_gta_trx_headers gta
4719 ,ra_customer_trx_all ct
4720 ,hz_cust_accounts rac_bill
4721 ,hz_parties rac_bill_party
4722 ,ra_cust_trx_line_gl_dist_all gd
4723 ,ra_batches_all rb
4724 WHERE gta.ra_trx_id=ct.customer_trx_id(+)
4725 AND rb.batch_id(+)=ct.batch_id
4726 AND gta.source='AR'
4727 AND(gta.status='COMPLETED' OR gta.status='CONSOLIDATED')--Yao Zhang Modified for bug#8765631
4728 AND gta.latest_version_flag='Y'
4729 AND gta.org_id=l_org_id
4730 AND ((gta.gta_batch_number>=l_gta_batch_num_from) OR
4731 (l_gta_batch_num_from IS NULL))
4732 AND ((gta.gta_batch_number<=l_gta_batch_num_to) OR
4733 (l_gta_batch_num_to IS NULL))
4734 AND ((ct.cust_trx_type_id=l_ar_transaction_type) OR
4735 (l_ar_transaction_type IS NULL))
4736 AND ct.bill_to_customer_id=rac_bill.cust_account_id(+)
4737 AND rac_bill.party_id = rac_bill_party.party_id(+)
4738 AND ((rac_bill.account_number>=l_cust_num_from) OR
4739 (l_cust_num_from IS NULL))
4740 AND ((rac_bill.account_number<=l_cust_num_to) OR
4741 (l_cust_num_to IS NULL))
4742 AND ((ct.bill_to_customer_id=l_cust_id) OR
4743 (l_cust_id IS NULL))
4744 AND ((gta.ra_gl_period=l_gl_period) OR
4745 (l_gl_period IS NULL))
4746 AND ct.customer_trx_id = gd.customer_trx_id(+)
4747 AND 'REC' = gd.account_class(+)
4748 AND 'Y' = gd.latest_rec_flag(+)
4749 AND ((gd.gl_date>=l_gl_date_from_d) OR
4750 (l_gl_date_from_d IS NULL ))
4751 AND ((gd.gl_date<=l_gl_date_to_d) OR
4752 (l_gl_date_to_d IS NULL))
4753 AND ((rb.name>=l_ar_trx_batch_from) OR
4754 (l_ar_trx_batch_from IS null))
4755 AND ((rb.name<=l_ar_trx_batch_to) OR
4756 (l_ar_trx_batch_to IS NULL))
4757 AND ((ct.trx_number>=l_ar_trx_num_from) OR
4758 (l_ar_trx_num_from IS NULL))
4759 AND ((ct.trx_number<=l_ar_trx_num_to) OR
4760 (l_ar_trx_num_to IS NULL))
4761 AND ((ct.trx_date>=l_ar_trx_date_from_d) OR
4762 (l_ar_trx_date_from_d IS NULL))
4763 AND ((ct.trx_date<=l_ar_trx_date_to_d) OR
4764 (l_ar_trx_date_to_d IS NULL))
4765 AND ((ct.doc_sequence_value>=l_ar_doc_num_from) OR
4766 (l_ar_doc_num_from IS NULL))
4767 AND ((ct.doc_sequence_value<=l_ar_doc_num_to) OR
4768 (l_ar_doc_num_to IS NULL))
4769 AND ((ct.invoice_currency_code=l_original_curr_code) OR
4770 (l_original_curr_code IS NULL))
4771 AND ((ct.primary_salesrep_id=l_primary_sales) OR
4772 (l_primary_sales IS null))
4773 --Yao Zhang add for bug#8765631
4774 AND (gta.consolidation_flag IS NULL OR gta.consolidation_flag='1');
4775
4776 l_trx_header c_trx_header%ROWTYPE;
4777
4778
4779 CURSOR c_missing_artrx IS
4780 SELECT
4781 gth.gta_trx_number
4782 ,gth.ra_trx_number
4783 ,gth.gt_invoice_number
4784 ,gth.gta_trx_header_id
4785 FROM
4786 ar_gta_trx_headers gth
4787 WHERE gth.SOURCE='AR'--Yao Zhang add for bug#8765631
4788 AND (gth.status='COMPLETED' OR gth.status='CONSOLIDATED')
4789 AND gth.ra_trx_id=l_ar_trx_header_id;
4790 --gth.SOURCE='GT'
4791 CURSOR c_ar_trx_number_missing IS
4792 SELECT
4793 DISTINCT gth.ra_trx_number
4794 FROM
4795 ar_gta_trx_headers gth
4796 WHERE gth.SOURCE='AR'--Yao Zhang add for bug#8765631
4797 AND (gth.status='COMPLETED' OR gth.status='CONSOLIDATED')
4798 AND gth.ra_trx_id=l_ar_trx_header_id;
4799 --gth.SOURCE='GT'
4800 --The following code is added by Yao for bug#8765631
4801 CURSOR c_consolidated_invs IS
4802 SELECT gta.gta_trx_header_id
4803 ,gta.gta_trx_number
4804 ,gta.ra_gl_date
4805 ,gta.BILL_TO_CUSTOMER_NAME customer_name
4806 FROM ar_gta_trx_headers gta
4807 WHERE gta.consolidation_flag = '0'
4808 AND gta.status = 'COMPLETED'
4809 AND gta.SOURCE = 'AR'
4810 AND gta.org_id = l_org_id
4811 AND gta.latest_version_flag = 'Y'
4812 AND ((gta.gta_batch_number >= l_gta_batch_num_from) OR
4813 (l_gta_batch_num_from IS NULL))
4814 AND ((gta.gta_batch_number <= l_gta_batch_num_to) OR
4815 (l_gta_batch_num_to IS NULL))
4816 AND l_ar_transaction_type IS NULL
4817 AND ((gta.BILL_TO_CUSTOMER_NUMBER>= l_cust_num_from) OR
4818 (l_cust_num_from IS NULL))
4819 AND ((gta.BILL_TO_CUSTOMER_NUMBER <= l_cust_num_to) OR
4820 (l_cust_num_to IS NULL))
4821 AND ((gta.BILL_TO_CUSTOMER_ID = l_cust_id) OR (l_cust_id IS NULL))
4822 AND ((gta.ra_gl_period = l_gl_period) OR (l_gl_period IS NULL))
4823 AND ((gta.ra_gl_date >= l_gl_date_from_d) OR (l_gl_date_from_d IS NULL))
4824 AND ((gta.ra_gl_date <= l_gl_date_to_d) OR (l_gl_date_to_d IS NULL))
4825 AND l_ar_trx_batch_from IS NULL
4826 AND l_ar_trx_batch_to IS NULL
4827 AND l_ar_trx_num_from IS NULL
4828 AND l_ar_trx_num_to IS NULL
4829 AND l_ar_trx_date_from_d IS NULL
4830 AND l_ar_trx_date_to_d IS NULL
4831 AND l_ar_doc_num_from IS NULL
4832 AND l_ar_doc_num_to IS NULL
4833 AND l_original_curr_code IS NULL
4834 AND l_primary_sales IS NULL;
4835
4836 l_consolidated_inv c_consolidated_invs%ROWTYPE;
4837 l_consolidated_inv_header_id NUMBER;
4838 l_consolidated_difference BOOLEAN;
4839 --Yao Zhang add end;
4840 l_missing_artrx_rec c_missing_artrx%ROWTYPE;
4841 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
4842 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
4843
4844
4845 BEGIN
4846
4847 --Logging for debug
4848 IF (l_proc_level>=l_dbg_level)
4849 THEN
4850 FND_LOG.string(l_proc_level
4851 ,G_MODULE_PREFIX||'.'||l_api_name||'.begin'
4852 ,'Enter procedure'
4853 );
4854 END IF; --(l_proc_level>=l_proc_level)
4855
4856 --log for debug
4857 IF( l_proc_level >= l_dbg_level)
4858 THEN
4859 FND_LOG.STRING(l_proc_level
4860 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4861 ,'p_org_id '||p_org_id);
4862
4863 FND_LOG.STRING(l_proc_level
4864 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4865 ,'p_gta_batch_num_from '||p_gta_batch_num_from);
4866
4867 FND_LOG.STRING(l_proc_level
4868 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4869 ,'p_gta_batch_num_to '||p_gta_batch_num_to);
4870
4871 FND_LOG.STRING(l_proc_level
4872 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4873 ,'p_ar_transaction_type '||p_ar_transaction_type);
4874
4875 FND_LOG.STRING(l_proc_level
4876 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4877 ,'p_cust_num_from '||p_cust_num_from);
4878
4879 FND_LOG.STRING(l_proc_level
4880 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4881 ,'p_cust_num_to '||p_cust_num_to);
4882
4883 FND_LOG.STRING(l_proc_level
4884 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4885 ,'p_cust_name_id '||p_cust_name_id);
4886
4887 FND_LOG.STRING(l_proc_level
4888 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4889 ,'p_gl_period '||p_gl_period);
4890
4891 FND_LOG.STRING(l_proc_level
4892 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4893 ,'p_gl_date_from '||p_gl_date_from);
4894
4895 FND_LOG.STRING(l_proc_level
4896 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4897 ,'p_gl_date_to '||p_gl_date_to);
4898
4899 FND_LOG.STRING(l_proc_level
4900 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4901 ,'p_ar_trx_batch_from '||p_ar_trx_batch_from);
4902
4903 FND_LOG.STRING(l_proc_level
4904 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4905 ,'p_ar_trx_batch_to '||p_ar_trx_batch_to);
4906
4907 FND_LOG.STRING(l_proc_level
4908 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4909 ,'p_ar_trx_num_from '||p_ar_trx_num_from);
4910
4911 FND_LOG.STRING(l_proc_level
4912 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4913 ,'p_ar_trx_num_to '||p_ar_trx_num_to);
4914
4915 FND_LOG.STRING(l_proc_level
4916 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4917 ,'p_ar_trx_date_from '||p_ar_trx_date_from);
4918
4919 FND_LOG.STRING(l_proc_level
4920 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4921 ,'p_ar_trx_date_to '||p_ar_trx_date_to);
4922
4923 FND_LOG.STRING(l_proc_level
4924 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4925 ,'p_ar_doc_num_from '||p_ar_doc_num_from);
4926
4927 FND_LOG.STRING(l_proc_level
4928 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4929 ,'p_ar_doc_num_to '||p_ar_doc_num_to);
4930
4931 FND_LOG.STRING(l_proc_level
4932 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4933 ,'p_original_curr_code '||p_original_curr_code);
4934
4935 FND_LOG.STRING(l_proc_level
4936 ,G_MODULE_PREFIX||'.'||l_api_name||'.parameters'
4937 ,'p_primary_sales '||p_primary_sales);
4938
4939 END IF; --( l_proc_level >= l_dbg_level)
4940
4941 --Initialization
4942 l_missing_artrx_seq:=0;
4943
4944 --Convert canonical date format to pl/sql date
4945 l_gl_date_from_d:=FND_DATE.Canonical_To_Date(l_gl_date_from);
4946 l_gl_date_to_d:=FND_DATE.Canonical_To_Date(l_gl_date_to);
4947 l_ar_trx_date_from_d:=FND_DATE.Canonical_To_Date(l_ar_trx_date_from);
4948 l_ar_trx_date_to_d:=FND_DATE.Canonical_To_Date(l_ar_trx_date_to);
4949
4950 l_validated_lines_total:=0;
4951 l_ar_matching_lines_total:=0;
4952 l_ar_partially_import_total:=0;
4953
4954 OPEN c_trx_header;
4955
4956 FETCH c_trx_header INTO l_trx_header;
4957
4958
4959 WHILE c_trx_header%FOUND
4960 LOOP
4961
4962 l_ar_trx_header_id:=l_trx_header.ra_trx_id;
4963 l_ar_trx_number:=l_trx_header.trx_number;
4964 l_ar_trx_date:=l_trx_header.trx_date;
4965 l_ar_customer_name:=l_trx_header.customer_name;
4966 l_ar_currency_code:=l_trx_header.invoice_currency_code;
4967
4968 --Logging for debug
4969 IF (l_proc_level>=l_dbg_level)
4970 THEN
4971 FND_LOG.string(l_proc_level
4972 ,G_MODULE_PREFIX||'.'||l_api_name||'.phase'
4973 ,'c_trx_header'
4974 );
4975
4976 FND_LOG.string(l_proc_level
4977 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
4978 ,'l_ar_trx_header_id '||l_ar_trx_header_id
4979 );
4980
4981 FND_LOG.string(l_proc_level
4982 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
4983 ,'l_ar_trx_number '||l_ar_trx_number
4984 );
4985
4986 FND_LOG.string(l_proc_level
4987 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
4988 ,'l_ar_trx_date '||l_ar_trx_date
4989 );
4990
4991
4992 FND_LOG.string(l_proc_level
4993 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
4994 ,'l_ar_customer_name '||l_ar_customer_name
4995 );
4996
4997 FND_LOG.string(l_proc_level
4998 ,G_MODULE_PREFIX||'.'||l_api_name||'.variables'
4999 ,'l_ar_currency_code '||l_ar_currency_code
5000 );
5001 END IF; --(l_proc_level>=l_proc_level)
5002
5003
5004
5005 --Compare if AR missing - start
5006 IF (l_ar_trx_header_id IS NOT NULL) AND
5007 (l_ar_trx_number IS NULL) AND
5008 (l_ar_trx_date IS NULL) AND
5009 (l_ar_customer_name IS NULL) AND
5010 (l_ar_currency_code IS NULL)
5011 THEN --Missing AR transaction
5012 --{
5013 --
5014 --To calculate total INVOICE AMOUNT(no tax) by each ar transaction
5015 --To concatenate GTA invoice numbers
5016 --To concatenate VAT invoice numbers
5017 --
5018 l_gta_trx_number_missing:='';
5019 l_gt_invoice_number_missing:='';
5020 l_gt_invoice_amount_missing:=0;
5021 OPEN c_missing_artrx;
5022 FETCH c_missing_artrx INTO l_missing_artrx_rec;
5023 WHILE c_missing_artrx%FOUND
5024 LOOP
5025 IF (l_gta_trx_number_missing IS NULL)
5026 THEN
5027 l_gta_trx_number_missing:=l_missing_artrx_rec.gta_trx_number;
5028 ELSE
5029 l_gta_trx_number_missing:=l_gta_trx_number_missing||l_delimiter||
5030 l_missing_artrx_rec.gta_trx_number;
5031 END IF; -- l_gta_trx_number_missing is null
5032
5033
5034 IF (l_gt_invoice_number_missing IS NULL)
5035 THEN
5036 l_gt_invoice_number_missing:=l_missing_artrx_rec.gt_invoice_number;
5037 ELSE
5038 l_gt_invoice_number_missing:=l_gt_invoice_number_missing||
5039 l_delimiter||
5040 l_missing_artrx_rec.gt_invoice_number;
5041 END IF; -- l_gt_invoice_number_missing IS NULL
5042
5043
5044 l_gt_invoice_amount_missing :=l_gt_invoice_amount_missing +
5045 AR_GTA_TRX_UTIL.Get_Gtainvoice_Amount
5046 (l_missing_artrx_rec.gta_trx_header_id
5047 );
5048
5049 FETCH c_missing_artrx INTO l_missing_artrx_rec;
5050 END LOOP; --WHILE c_missing_artrx%FOUND
5051
5052 CLOSE c_missing_artrx;
5053
5054
5055 l_missing_artrx_seq:=l_missing_artrx_seq+1;
5056
5057
5058 --Get number of missed AR transaciton
5059 OPEN c_ar_trx_number_missing;
5060 FETCH c_ar_trx_number_missing INTO l_ar_trx_number_missing;
5061 CLOSE c_ar_trx_number_missing;
5062
5063 INSERT INTO ar_gta_missing_artrx_temp(record_number
5064 ,gta_trx_number
5065 ,ar_trx_number
5066 ,gt_invoice_number
5067 ,gt_invoice_amount
5068 )
5069 VALUES(l_missing_artrx_seq
5070 ,l_gta_trx_number_missing
5071 ,l_ar_trx_number_missing
5072 ,l_gt_invoice_number_missing
5073 ,l_gt_invoice_amount_missing
5074 );
5075 --}
5076 ELSE
5077 --{
5078
5079 l_validated_lines:=0;
5080 l_ar_matching_lines:=0;
5081 l_ar_partially_import:=0;
5082
5083
5084 Compare_Header( p_org_id =>p_org_id
5085 , p_ar_header_id =>l_ar_trx_header_id
5086 , x_has_difference =>l_header_difference
5087 );
5088
5089 Compare_Lines( p_org_id =>p_org_id
5090 , p_ar_header_id =>l_ar_trx_header_id
5091 , x_validated_lines =>l_validated_lines
5092 , x_ar_matching_lines =>l_ar_matching_lines
5093 , x_ar_partially_import =>l_ar_partially_import
5094 , x_has_difference =>l_line_difference
5095 );
5096
5097 Get_Unmatched_Lines( p_org_id =>p_org_id
5098 , p_ar_header_id =>l_ar_trx_header_id
5099 , x_has_difference=>l_has_unmatched
5100 );
5101
5102 IF (l_header_difference OR l_line_difference OR l_has_unmatched)
5103 THEN
5104 INSERT INTO ar_gta_ar_difference_temp(customer_trx_id
5105 ,trx_number
5106 ,trx_date
5107 ,customer_name
5108 ,invoice_currency_code
5109 )
5110 VALUES(l_ar_trx_header_id
5111 ,l_ar_trx_number
5112 ,l_ar_trx_date
5113 ,l_ar_customer_name
5114 ,l_ar_currency_code
5115 );
5116
5117 END IF; --(l_header_difference OR l_line_difference OR l_has_unmatched)
5118
5119 --To calculate the number of validated AR transaction lines
5120 l_validated_lines_total:=l_validated_lines_total+l_validated_lines;
5121
5122 --To calculate the number of AR lines that match with both GTA and GT
5123 l_ar_matching_lines_total:=l_ar_matching_lines_total+
5124 l_ar_matching_lines;
5125
5126
5127
5128 --To calculatee the number of AR transaction lines that are split into
5129 --multiple GTA and GT lines, and some split lines not imported into GT.
5130 l_ar_partially_import_total:=l_ar_partially_import_total+
5131 l_ar_partially_import;
5132
5133 --}
5134 END IF; --"Compare if AR missing" end
5135
5136 FETCH c_trx_header INTO l_trx_header;
5137 END LOOP;
5138
5139 CLOSE c_trx_header;
5140 --the following code is added by Yao for bug#8765631
5141 OPEN c_consolidated_invs;
5142 LOOP
5143 FETCH c_consolidated_invs INTO l_consolidated_inv;
5144 EXIT WHEN c_consolidated_invs%NOTFOUND;
5145 Compare_Consolidated_Inv(p_org_id =>p_org_id
5146 ,p_gta_header_id =>l_consolidated_inv.gta_trx_header_id
5147 ,x_has_difference =>l_consolidated_difference
5148 );
5149 IF l_consolidated_difference
5150 THEN
5151 INSERT INTO ar_gta_ar_difference_temp(customer_trx_id
5152 ,trx_number
5153 ,trx_date
5154 ,customer_name
5155 ,invoice_currency_code
5156 )
5157 VALUES(l_consolidated_inv.gta_trx_header_id
5158 ,l_consolidated_inv.gta_trx_number
5159 ,l_consolidated_inv.ra_gl_date
5160 ,l_consolidated_inv.customer_name
5161 ,NULL
5162 );
5163 END IF;
5164 END LOOP;
5165 CLOSE c_consolidated_invs;
5166 --Yao Zhang add end;
5167
5168
5169 --Call Generate_Discrepancy_Xml to generate XML statements
5170 Generate_Discrepancy_Xml
5171 ( p_org_id =>l_org_id
5172 , p_gta_batch_num_from =>l_gta_batch_num_from
5173 , p_gta_batch_num_to =>l_gta_batch_num_to
5174 , p_ar_transaction_type =>l_ar_transaction_type
5175 , p_cust_num_from =>l_cust_num_from
5176 , p_cust_num_to =>l_cust_num_to
5177 , p_cust_name_id =>l_cust_id
5178 , p_gl_period =>l_gl_period
5179 , p_gl_date_from =>l_gl_date_from_d
5180 , p_gl_date_to =>l_gl_date_to_d
5181 , p_ar_trx_batch_from =>l_ar_trx_batch_from
5182 , p_ar_trx_batch_to =>l_ar_trx_batch_to
5183 , P_ar_trx_num_from =>l_ar_trx_num_from
5184 , p_ar_trx_num_to =>l_ar_trx_num_to
5185 , p_ar_trx_date_from =>l_ar_trx_date_from_d
5186 , p_ar_trx_date_to =>l_ar_trx_date_to_d
5187 , p_ar_doc_num_from =>l_ar_doc_num_from
5188 , p_ar_doc_num_to =>l_ar_doc_num_to
5189 , p_original_curr_code =>l_original_curr_code
5190 , p_primary_sales =>l_primary_sales
5191 , p_validated_lines_total =>l_validated_lines_total
5192 , p_ar_matching_lines_total =>l_ar_matching_lines_total
5193 , p_ar_partially_import_total =>l_ar_partially_import_total
5194 , x_output =>l_xml_output
5195 );
5196
5197
5198 --Output xml script
5199
5200 FND_FILE.Put_Line(FND_FILE.Output,'<?xml version="1.0" encoding="UTF-8"?>');
5201 AR_GTA_TRX_UTIL.Output_Conc(l_xml_output.Getclobval);
5202
5203 --Logging for debug
5204
5205 IF (l_proc_level>=l_dbg_level)
5206 THEN
5207 FND_LOG.string(l_proc_level
5208 ,G_MODULE_PREFIX||'.'||l_api_name||'.end'
5209 ,'Exit Procedure'
5210 );
5211 END IF;
5212
5213 EXCEPTION
5214 WHEN OTHERS THEN
5215 IF(l_proc_level>=l_dbg_level)
5216 THEN
5217 Fnd_Log.String( l_proc_level
5218 , G_Module_Prefix || l_api_name || '. Other_Exception '
5219 , Sqlcode||':'||Sqlerrm);
5220
5221 END IF;
5222 RAISE;
5223 END Generate_Discrepancy_Rep;
5224
5225 --==========================================================================
5226 -- PROCEDURE NAME:
5227 --
5228 -- Generate_Consol_Mapping_Rep Public
5229 --
5230 -- DESCRIPTION:
5231 --
5232 -- This procedure generates Invoice Consolidation Mapping Report data.
5233 --
5234 --
5235 -- PARAMETERS:
5236 -- In: p_org_id Operating unit id
5237 -- p_gl_period GL period
5238 -- p_customer_num_from Customer number low range
5239 -- p_customer_num_to Customer number high range
5240 -- p_customer_name_from Customer name low range
5241 -- p_customer_name_to Customer name high range
5242 -- p_consol_trx_num_from Consolidated invoice number low range
5243 -- p_consol_trx_num_to Consolidated invoice number high range
5244 -- p_invoice_type Invoice type
5245 --
5246 -- Out:
5247 --
5248 -- DESIGN REFERENCES:
5249 -- GTA_12.1.2_Technical_Design.doc
5250 --
5251 -- CHANGE HISTORY:
5252 --
5253 -- 25-Jul-2009 Allen Yang Created.
5254 -- 02-Sep-2009 Allen Yang Modified for bug 8848696
5255 --==========================================================================
5256 Procedure Generate_Consol_Mapping_Rep
5257 ( p_org_Id IN NUMBER
5258 , p_gl_period IN VARCHAR2
5259 , p_customer_num_from IN VARCHAR2
5260 , p_customer_num_to IN VARCHAR2
5261 , p_customer_name_from IN VARCHAR2
5262 , p_customer_name_to IN VARCHAR2
5263 , p_consol_trx_num_from IN VARCHAR2
5264 , p_consol_trx_num_to IN VARCHAR2
5265 , p_invoice_type IN VARCHAR2
5266 )
5267 IS
5268 /* Note: Due to FD change, the meaning of words 'consolidation' and 'consolidated'
5269 has been reversed in code.
5270 consolidated -- invoice which consists of several consolidation invoices
5271 consolidation -- invoice which is consolidated into a consolidated invoice
5272 */
5273 l_procedure_name VARCHAR2(40):='Generate_Consol_Mapping_Rep';
5274 l_no_data_message VARCHAR2(500);
5275 l_consolidation_trx_id AR_Gta_Trx_Headers.GTA_TRX_HEADER_ID%TYPE;
5276 l_Parameter Xmltype;
5277 l_consolidation_trx Xmltype;
5278 l_consolidated_trxs Xmltype;
5279 l_consolidation_trxs Xmltype;
5280 l_report Xmltype;
5281 l_consolidation_rows NUMBER;
5282 i NUMBER;
5283 l_consolidated_rows NUMBER;
5284 l_no_data_flag VARCHAR2(1):='N';
5285
5286 l_consol_trx_num_from AR_Gta_Trx_Headers_All.CONSOLIDATION_TRX_NUM%TYPE
5287 := p_consol_trx_num_from;
5288 l_consol_trx_num_to AR_Gta_Trx_Headers_All.CONSOLIDATION_TRX_NUM%TYPE
5289 := p_consol_trx_num_to;
5290 l_customer_num_from AR_Gta_Trx_Headers_All.BILL_TO_CUSTOMER_NUMBER%TYPE
5291 := p_customer_num_from;
5292 l_customer_num_to AR_Gta_Trx_Headers_All.BILL_TO_CUSTOMER_NUMBER%TYPE
5293 := p_customer_num_to;
5294 l_customer_name_from AR_Gta_Trx_Headers_All.BILL_TO_CUSTOMER_NAME%TYPE
5295 := p_customer_name_from;
5296 l_customer_name_to AR_Gta_Trx_Headers_All.BILL_TO_CUSTOMER_NAME%TYPE
5297 := p_customer_name_to;
5298 l_invoice_type AR_Gta_Trx_Headers_All.INVOICE_TYPE%TYPE
5299 := p_invoice_type;
5300
5301 -- added by Allen Yang for bug 8848696 02-Sep-2009
5302 l_invoice_type_disp FND_LOOKUP_VALUES_VL.MEANING%TYPE;
5303 -- end added by Allen Yang
5304
5305 l_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5306 l_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
5307
5308 CURSOR c_consolidation_trx_ids IS
5309 SELECT
5310 GTA_TRX_HEADER_ID
5311 FROM
5312 AR_Gta_Trx_Headers_All
5313 WHERE ORG_ID = p_org_id
5314 AND RA_GL_PERIOD = NVL(p_gl_period, RA_GL_PERIOD)
5315 AND SOURCE = 'AR'
5316 AND STATUS NOT IN ('CANCELLED') --fix bug 14078699
5317 AND CONSOLIDATION_FLAG = '0'
5318 AND BILL_TO_CUSTOMER_NUMBER BETWEEN NVL(l_customer_num_from
5319 ,BILL_TO_CUSTOMER_NUMBER)
5320 AND NVL(l_customer_num_to
5321 ,BILL_TO_CUSTOMER_NUMBER)
5322 AND BILL_TO_CUSTOMER_NAME BETWEEN NVL(l_customer_name_from
5323 ,BILL_TO_CUSTOMER_NAME)
5324 AND NVL(l_customer_name_to
5325 ,BILL_TO_CUSTOMER_NAME)
5326 AND GTA_TRX_NUMBER BETWEEN NVL(l_consol_trx_num_from, GTA_TRX_NUMBER)
5327 AND NVL(l_consol_trx_num_to, GTA_TRX_NUMBER)
5328 AND INVOICE_TYPE = NVL(l_invoice_type, INVOICE_TYPE)
5329 ORDER BY
5330 BILL_TO_CUSTOMER_NAME
5331 ,TP_TAX_REGISTRATION_NUMBER
5332 ,INVOICE_TYPE
5333 ,RA_GL_PERIOD
5334 ,CUSTOMER_ADDRESS_PHONE
5335 ,BANK_ACCOUNT_NAME
5336 ,BANK_ACCOUNT_NUMBER
5337 ,GTA_TRX_NUMBER;
5338
5339 BEGIN
5340 --logging for debug
5341 IF (l_proc_level>=l_dbg_level)
5342 THEN
5343 FND_LOG.string( l_proc_level
5344 , G_MODULE_PREFIX||l_procedure_name||'.begin'
5345 , 'enter procedure');
5346 END IF; --(l_proc_level>=l_dbg_level)
5347
5348 --Get Consolidation Invoice Rows
5349 SELECT
5350 COUNT(*)
5351 INTO
5352 l_consolidation_rows
5353 FROM
5354 AR_Gta_Trx_Headers
5355 WHERE ORG_ID = p_org_id
5356 AND RA_GL_PERIOD = NVL(p_gl_period, RA_GL_PERIOD)
5357 AND Source = 'AR'
5358 AND STATUS NOT IN ('CANCELLED') --fix bug 14078699
5359 AND CONSOLIDATION_FLAG = '0'
5360 AND BILL_TO_CUSTOMER_NUMBER BETWEEN NVL(l_customer_num_from
5361 , BILL_TO_CUSTOMER_NUMBER)
5362 AND NVL(l_customer_num_to
5363 , BILL_TO_CUSTOMER_NUMBER)
5364 AND BILL_TO_CUSTOMER_NAME BETWEEN NVL(l_customer_name_from
5365 , BILL_TO_CUSTOMER_NAME)
5366 AND NVL(l_customer_name_to
5367 , BILL_TO_CUSTOMER_NAME)
5368 AND CONSOLIDATION_TRX_NUM BETWEEN NVL(l_consol_trx_num_from
5369 , CONSOLIDATION_TRX_NUM)
5370 AND NVL(l_consol_trx_num_to
5371 , CONSOLIDATION_TRX_NUM)
5372 AND INVOICE_TYPE = NVL(l_invoice_type, INVOICE_TYPE);
5373
5374 OPEN c_consolidation_trx_ids;
5375 FETCH c_consolidation_trx_ids INTO l_consolidation_trx_id;
5376
5377 i:=0;
5378 WHILE c_consolidation_trx_ids%FOUND LOOP
5379 i:=i+1;
5380 l_consolidation_trx := get_consolidation_trx(p_trx_header_id =>
5381 l_consolidation_trx_id);
5382 l_consolidated_trxs := get_consolidated_trxs(p_trx_header_id =>
5383 l_consolidation_trx_id);
5384 SELECT
5385 Xmlconcat(l_consolidation_trxs
5386 , Xmlelement("Invoice"
5387 , Xmlconcat(l_consolidation_trx
5388 , l_consolidated_trxs)))
5389 INTO
5390 l_consolidation_trxs
5391 FROM DUAL;
5392
5393 FETCH c_consolidation_trx_ids INTO l_consolidation_trx_id;
5394 END LOOP; --c_consolidation_trx_ids%FOUND
5395 CLOSE c_consolidation_trx_ids;
5396
5397 --Get Consolidated Invoice Rows
5398 l_consolidated_rows:=i;
5399 IF (l_consolidation_rows =0) AND (l_consolidated_rows =0)
5400 THEN
5401 l_no_data_flag:='Y';
5402 END IF; -- (l_consolidation_rows =0) AND (l_consolidated_rows =0)
5403
5404 -- added by Allen Yang 02-Sep-2009 for bug 8848696
5405 ---------------------------------------------------------------
5406 BEGIN
5407 SELECT meaning
5408 INTO l_invoice_type_disp
5409 FROM FND_LOOKUP_VALUES_VL
5410 WHERE lookup_type = 'AR_GTA_INVOICE_TYPE'
5411 AND lookup_code = p_invoice_type;
5412 EXCEPTION
5413 WHEN NO_DATA_FOUND THEN
5414 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5415 fnd_log.String(fnd_log.LEVEL_EXCEPTION,
5416 G_MODULE_PREFIX || l_procedure_name,
5417 'No data found ');
5418 END IF;
5419 RAISE;
5420 WHEN OTHERS THEN
5421 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5422 fnd_log.String(fnd_log.LEVEL_EXCEPTION,
5423 G_MODULE_PREFIX || l_procedure_name,
5424 'Other exception');
5425 END IF;
5426 RAISE;
5427 END;
5428 ---------------------------------------------------------------
5429
5430 --Generate Parameter Section
5431 SELECT
5432 Xmlelement("Parameters"
5433 , Xmlforest( ar_gta_trx_util.Get_OperatingUnit(p_org_id)
5434 AS "OperationUnit"
5435 , p_gl_period AS "GLPeriod"
5436 , p_customer_num_from AS "CustNumFrom"
5437 , p_customer_num_to AS "CustNumTo"
5438 , p_customer_name_from AS "CustNameFrom"
5439 , p_customer_name_to AS "CustNameTo"
5440 , p_consol_trx_num_from AS "ConsolTrxNumFrom"
5441 , p_consol_trx_num_to AS "ConsolTrxNumTo"
5442 -- modified by Allen Yang 02-Sep-2009 for bug 8848696
5443 --, p_invoice_type AS "InvoiceType"))
5444 , l_invoice_type_disp AS "InvoiceType"))
5445 -- end modified by Allen Yang
5446 INTO
5447 l_parameter
5448 FROM DUAL;
5449
5450 --Generate Reports Xml Data
5451 IF l_no_data_flag='Y'
5452 THEN
5453 FND_MESSAGE.SET_NAME('AR','AR_GTA_NO_DATA_FOUND');
5454 l_no_data_message := FND_MESSAGE.GET();
5455
5456 SELECT Xmlelement("ConsolidationMappingReport"
5457 , Xmlconcat(Xmlelement("ReportFailed",'N')
5458 , Xmlelement("FailedWithParameters",'Y')
5459 , Xmlelement("FailedMsgWithParameters"
5460 , l_no_data_message)
5461 , Xmlelement("RepDate"
5462 , ar_gta_trx_util.To_Xsd_Date_String(SYSDATE))
5463 , l_parameter))
5464 INTO
5465 l_report
5466 FROM DUAL;
5467 ELSE
5468 SELECT Xmlelement("ConsolidationMappingReport"
5469 , Xmlconcat(Xmlelement("ReportFailed",'N')
5470 , Xmlelement("FailedWithParameters",'N')
5471 , Xmlelement("RepDate"
5472 ,ar_gta_trx_util.To_Xsd_Date_String(SYSDATE))
5473 , l_parameter
5474 , xmlelement("Invoices", l_consolidation_trxs)))
5475 INTO
5476 l_report
5477 FROM DUAL;
5478 END IF; --l_no_data_flag='Y'
5479
5480 ar_gta_trx_util.output_conc(l_report.getclobval());
5481
5482 --logging for debug
5483 IF (l_proc_level>=l_dbg_level)
5484 THEN
5485 FND_LOG.string( l_proc_level
5486 , G_MODULE_PREFIX||l_procedure_name||'.end'
5487 , 'end procedure');
5488 END IF; --l_proc_level>=l_dbg_level
5489
5490 EXCEPTION
5491 WHEN OTHERS THEN
5492 IF(Fnd_Log.Level_Unexpected >= Fnd_Log.G_Current_Runtime_Level)
5493 THEN
5494 Fnd_Log.String( Fnd_Log.Level_Unexpected
5495 , G_MODULE_PREFIX || l_procedure_name || '.Other_Exception '
5496 , Sqlcode||Sqlerrm);
5497 END IF; --Fnd_Log.Level_Unexpected >= Fnd_Log.G_Current_Runtime_Level
5498
5499 END Generate_Consol_Mapping_Rep;
5500
5501 --==========================================================================
5502 -- PROCEDURE NAME:
5503 --
5504 -- Get_Consolidation_Trx Public
5505 --
5506 -- DESCRIPTION:
5507 --
5508 -- This procedure returns XML data for a given consolidated invoice.
5509 --
5510 --
5511 -- PARAMETERS:
5512 -- In: p_trx_header_id invoice header identifier
5513 --
5514 --
5515 -- Out:
5516 --
5517 -- DESIGN REFERENCES:
5518 -- GTA_12.1.2_Technical_Design.doc
5519 --
5520 -- CHANGE HISTORY:
5521 --
5522 -- 25-Jul-2009 Allen Yang Created.
5523 -- 25-Aug-2009 Allen Yang Modified for bug 8809860
5524 -- 02-Sep-2009 Allen Yang Modified for bug 8848696
5525 --==========================================================================
5526 FUNCTION Get_Consolidation_Trx(p_trx_header_id IN NUMBER)
5527 RETURN XMLTYPE
5528 IS
5529 /* Note: Due to FD change, the meaning of words 'consolidation' and 'consolidated'
5530 has been reversed in code.
5531 consolidated -- invoice which consists of several consolidation invoices
5532 consolidation -- invoice which is consolidated into a consolidated invoice
5533 */
5534 l_procedure_name VARCHAR2(40):='Get_Consolidation_Trx';
5535 l_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5536 l_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
5537 l_ret_xmlelement Xmltype;
5538
5539 BEGIN
5540 --logging for debug
5541 IF (l_proc_level>=l_dbg_level)
5542 THEN
5543 FND_LOG.string( l_proc_level
5544 , G_MODULE_PREFIX||l_procedure_name||'.begin'
5545 , 'enter procedure');
5546 END IF; --(l_proc_level>=l_dbg_level)
5547
5548 -- modified by Allen Yang 02-Sep-2009 for bug 8848696
5549 ---------------------------------------------------------------------------
5550 /*
5551 SELECT Xmlforest(
5552 GTA_TRX_NUMBER AS "ConsolidationTrxNum"
5553 , CUSTOMER_ADDRESS_PHONE AS "CustAddressPhone"
5554 , RA_GL_PERIOD AS "GLPeriod"
5555 , BILL_TO_CUSTOMER_NAME AS "CustomerName"
5556 , TP_TAX_REGISTRATION_NUMBER AS "TaxRegistrationNum"
5557 , INVOICE_TYPE AS "InvoiceType"
5558 , BANK_ACCOUNT_NAME AS "BankName"
5559 , BANK_ACCOUNT_NUMBER AS "BankAccountNum"
5560 -- modified by Allen Yang 25-Aug-2009 for bug 8809860
5561 --, ar_gta_trx_util.Get_Gtainvoice_Amount(p_header_id =>
5562 -- p_trx_header_id) AS "Amount")
5563 , ar_gta_trx_util.Get_Gtainvoice_Amount(p_trx_header_id) AS "Amount")
5564 -- end modified by Allen
5565 INTO
5566 l_ret_xmlelement
5567 FROM AR_GTA_TRX_HEADERS_ALL
5568 WHERE GTA_TRX_HEADER_ID = p_trx_header_id;
5569 */
5570 SELECT Xmlforest(
5571 JGTHA.GTA_TRX_NUMBER AS "ConsolidationTrxNum"
5572 , JGTHA.CUSTOMER_ADDRESS_PHONE AS "CustAddressPhone"
5573 , JGTHA.RA_GL_PERIOD AS "GLPeriod"
5574 , JGTHA.BILL_TO_CUSTOMER_NAME AS "CustomerName"
5575 , JGTHA.TP_TAX_REGISTRATION_NUMBER AS "TaxRegistrationNum"
5576 , FLTV.MEANING AS "InvoiceType"
5577 , JGTHA.BANK_ACCOUNT_NAME AS "BankName"
5578 , JGTHA.BANK_ACCOUNT_NUMBER AS "BankAccountNum"
5579 -- modified by Allen Yang 25-Aug-2009 for bug 8809860
5580 --, ar_gta_trx_util.Get_Gtainvoice_Amount(p_header_id =>
5581 -- p_trx_header_id) AS "Amount")
5582 , ar_gta_trx_util.Get_Gtainvoice_Amount(p_trx_header_id) AS "Amount")
5583 -- end modified by Allen
5584 INTO
5585 l_ret_xmlelement
5586 FROM AR_GTA_TRX_HEADERS_ALL JGTHA
5587 , FND_LOOKUP_VALUES_VL FLTV
5588 WHERE GTA_TRX_HEADER_ID = p_trx_header_id
5589 AND FLTV.LOOKUP_TYPE = 'AR_GTA_INVOICE_TYPE'
5590 AND JGTHA.INVOICE_TYPE = FLTV.LOOKUP_CODE;
5591 -----------------------------------------------------------------------------
5592
5593 --logging for debug
5594 IF (l_proc_level>=l_dbg_level)
5595 THEN
5596 FND_LOG.string( l_proc_level
5597 , G_MODULE_PREFIX||l_procedure_name||'.end'
5598 , 'end procedure');
5599 END IF; --l_proc_level>=l_dbg_level
5600
5601 RETURN l_ret_xmlelement;
5602
5603 EXCEPTION
5604 WHEN OTHERS THEN
5605 IF(Fnd_Log.Level_Unexpected >= Fnd_Log.G_Current_Runtime_Level)
5606 THEN
5607 Fnd_Log.String( Fnd_Log.Level_Unexpected
5608 , G_MODULE_PREFIX || l_procedure_name || '.Other_Exception '
5609 , Sqlcode||Sqlerrm);
5610 END IF; --Fnd_Log.Level_Unexpected >= Fnd_Log.G_Current_Runtime_Level
5611 END Get_Consolidation_Trx;
5612
5613 --==========================================================================
5614 -- PROCEDURE NAME:
5615 --
5616 -- Get_Consolidated_Trxs Public
5617 --
5618 -- DESCRIPTION:
5619 --
5620 -- For a given consolidated invoice, get xml data of its
5621 -- consolidation invoices.
5622 --
5623 --
5624 -- PARAMETERS:
5625 -- In: p_trx_header_id invoice header identifier
5626 --
5627 --
5628 -- Out:
5629 --
5630 -- DESIGN REFERENCES:
5631 -- GTA_12.1.2_Technical_Design.doc
5632 --
5633 -- CHANGE HISTORY:
5634 --
5635 -- 25-Jul-2009 Allen Yang Created.
5636 -- 25-Aug-2009 Allen Yang Modified for bug 8809860
5637 --==========================================================================
5638 FUNCTION Get_Consolidated_Trxs(p_trx_header_id IN NUMBER)
5639 RETURN XMLTYPE
5640 IS
5641 /* Note: Due to FD change, the meaning of words 'consolidation' and 'consolidated'
5642 has been reversed in code.
5643 consolidated -- invoice which consists of several consolidation invoices
5644 consolidation -- invoice which is consolidated into a consolidated invoice
5645 */
5646 l_procedure_name VARCHAR2(40):='Get_Consolidated_Trxs';
5647 l_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5648 l_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
5649 l_ret_xmlelement Xmltype;
5650
5651 BEGIN
5652 --logging for debug
5653 IF (l_proc_level>=l_dbg_level)
5654 THEN
5655 FND_LOG.string( l_proc_level
5656 , G_MODULE_PREFIX||l_procedure_name||'.begin'
5657 , 'enter procedure');
5658 END IF; --(l_proc_level>=l_dbg_level)
5659
5660 SELECT Xmlelement("ConsolidatedInvoices"
5661 , Xmlagg(Xmlelement("ConsolidatedInvoice"
5662 ,Xmlforest(jgtha.RA_GL_PERIOD AS "ConsolidatedGLPeriod"
5663 ,jgtha.GTA_TRX_NUMBER AS "ConsolidatedTrxNum"
5664 ,jgtha.RA_TRX_NUMBER AS "ARTrxNum"
5665 ,rctt.NAME AS "ARTrxType"
5666 ,ar_gta_trx_util.Get_Gtainvoice_Amount
5667 -- modified by Allen Yang 25-Aug-2009 for bug 8809860
5668 --(p_header_id =>p_trx_header_id)
5669 (jgtha.GTA_TRX_HEADER_ID)
5670 -- end modified by Allen
5671 AS "ConsolidatedAmount"))))
5672 INTO
5673 l_ret_xmlelement
5674 FROM
5675 AR_Gta_Trx_Headers_all jgtha
5676 , ra_customer_trx_all rcta
5677 , ra_cust_trx_types rctt
5678 WHERE jgtha.CONSOLIDATION_FLAG = '1'
5679 AND jgtha.SOURCE = 'AR'
5680 AND jgtha.Status='CONSOLIDATED'
5681 AND jgtha.CONSOLIDATION_TRX_NUM =
5682 (SELECT GTA_TRX_NUMBER
5683 FROM AR_Gta_Trx_Headers_all
5684 WHERE GTA_TRX_HEADER_ID = p_trx_header_id)
5685 AND rctt.CUST_TRX_TYPE_ID = rcta.CUST_TRX_TYPE_ID
5686 AND rcta.CUSTOMER_TRX_ID = jgtha.RA_TRX_ID;
5687
5688
5689 --logging for debug
5690 IF (l_proc_level>=l_dbg_level)
5691 THEN
5692 FND_LOG.string( l_proc_level
5693 , G_MODULE_PREFIX||l_procedure_name||'.end'
5694 , 'end procedure');
5695 END IF; --l_proc_level>=l_dbg_level
5696
5697 RETURN l_ret_xmlelement;
5698
5699 EXCEPTION
5700 WHEN OTHERS THEN
5701 IF(Fnd_Log.Level_Unexpected >= Fnd_Log.G_Current_Runtime_Level)
5702 THEN
5703 Fnd_Log.String( Fnd_Log.Level_Unexpected
5704 , G_MODULE_PREFIX || l_procedure_name || '.Other_Exception '
5705 , Sqlcode||Sqlerrm);
5706 END IF; --Fnd_Log.Level_Unexpected >= Fnd_Log.G_Current_Runtime_Level
5707 END Get_Consolidated_Trxs;
5708 --==========================================================================
5709 -- PROCEDURE NAME:
5710 --
5711 -- Compare_Consolidated_Inv Public
5712 --
5713 -- DESCRIPTION:
5714 --
5715 -- This Procedure Compare completed consolidated_invs with gt invoice
5716 --
5717 -- PARAMETERS:
5718 -- In: p_org_id Operating unit id
5719 -- p_gta_header_id GTA invoice header id
5720 --
5721 -- Out: x_has_difference
5722 --
5723 --
5724 -- DESIGN REFERENCES:
5725 --
5726 --
5727 -- CHANGE HISTORY:
5728 --
5729 -- 09-Aug-2009 Yao Zhang Created.
5730 --===========================================================================
5731 PROCEDURE Compare_Consolidated_Inv
5732 (p_org_id NUMBER
5733 ,p_gta_header_id NUMBER
5734 ,x_has_difference OUT NOCOPY BOOLEAN
5735 ) IS
5736 l_procedure_name VARCHAR2(50):='Compare_Consolidated_Inv';
5737 l_org_id NUMBER;
5738 l_gta_header_id NUMBER;
5739 l_gta_amount NUMBER;
5740 l_gta_taxamount NUMBER;
5741 l_gta_customer_name ar_gta_trx_headers_all.bill_to_customer_name%TYPE;
5742 l_gta_taxpayer_id ar_gta_trx_headers_all.tp_tax_registration_number%TYPE;
5743 l_gta_customer_bank_account ar_gta_trx_headers_all.bank_account_name_number%TYPE;
5744 l_gta_customer_address_phone ar_gta_trx_headers_all.customer_address_phone%TYPE;
5745 l_gta_invoice_type ar_gta_trx_headers_all.invoice_type%TYPE;
5746 l_gta_invoice_type_name VARCHAR2(80);
5747 l_gta_trx_number ar_gta_trx_headers_all.gta_trx_number%TYPE;
5748
5749 l_gt_amount NUMBER;
5750 l_gt_taxamount NUMBER;
5751 l_gt_customer_name ar_gta_trx_headers_all.bill_to_customer_name%TYPE;
5752 l_gt_taxpayer_id ar_gta_trx_headers_all.tp_tax_registration_number%TYPE;
5753 l_gt_customer_bank_account ar_gta_trx_headers_all.bank_account_name_number%TYPE;
5754 l_gt_customer_address_phone ar_gta_trx_headers_all.customer_address_phone%TYPE;
5755 l_gt_invoice_type ar_gta_trx_headers_all.invoice_type%TYPE;
5756 l_gt_invoice_type_name VARCHAR2(80);
5757 l_gt_invoice_number ar_gta_trx_headers_all.gt_invoice_number%TYPE;
5758 l_gt_trx_number ar_gta_trx_headers_all.gta_trx_number%TYPE;
5759
5760 l_amount_attr fnd_lookup_values.meaning%TYPE;
5761 l_taxamount_attr fnd_lookup_values.meaning%TYPE;
5762 l_cust_name_attr fnd_lookup_values.meaning%TYPE;
5763 l_bank_name_account_attr fnd_lookup_values.meaning%TYPE;
5764 l_address_phone_attr fnd_lookup_values.meaning%TYPE;
5765 l_taxpayer_id_attr fnd_lookup_values.meaning%TYPE;
5766 l_invoicetype_attr fnd_lookup_values.meaning%TYPE;
5767
5768 l_has_difference BOOLEAN;
5769
5770
5771 CURSOR c_gta_headers IS
5772 SELECT
5773 AR_GTA_TRX_UTIL.Get_Gtainvoice_Amount(gta.gta_trx_header_id)
5774 amount
5775 , AR_GTA_TRX_UTIL.Get_Gtainvoice_Tax_Amount(gta.gta_trx_header_id)
5776 taxamount
5777 , gta.bill_to_customer_name
5778 customer_name
5779 , gta.tp_tax_registration_number
5780 tax_registration_number
5781 , gta.bank_account_name_number
5782 customer_bank_account
5783 , gta.customer_address_phone
5784 customer_address_phone
5785 , gta.gta_trx_number
5786 ,gta.invoice_type invoice_type
5787 ,lk.meaning invoice_type_name
5788 FROM
5789 ar_gta_trx_headers gta, fnd_lookup_values_vl lk
5790 WHERE gta.gta_trx_header_id=l_gta_header_id
5791 AND gta.invoice_type = lk.lookup_code
5792 AND lk.lookup_type='AR_GTA_INVOICE_TYPE';
5793
5794 CURSOR c_gt_headers IS
5795 SELECT ar_gta_trx_util.get_gtainvoice_amount(gt.gta_trx_header_id) amount,
5796 ar_gta_trx_util.get_gtainvoice_tax_amount(gt.gta_trx_header_id) taxamount,
5797 gt.bill_to_customer_name customer_name,
5798 gt.tp_tax_registration_number tax_registration_number,
5799 gt.bank_account_name_number customer_bank_account,
5800 gt.customer_address_phone customer_address_phone,
5801 gt.gt_invoice_number,
5802 gt.invoice_type invoice_type,
5803 lk.meaning invoice_type_name
5804 FROM ar_gta_trx_headers gt, fnd_lookup_values_vl lk
5805 WHERE gt.gta_trx_number IN
5806 (SELECT gta_trx_number
5807 FROM ar_gta_trx_headers gta
5808 WHERE gta.gta_trx_header_id = l_gta_header_id)
5809 AND gt.SOURCE = 'GT'
5810 AND gt.invoice_type = lk.lookup_code
5811 AND lk.lookup_type = 'AR_GTA_INVOICE_TYPE';
5812
5813 BEGIN
5814
5815 --logging for debug
5816 IF(Fnd_Log.Level_Unexpected >= Fnd_Log.G_Current_Runtime_Level)
5817 THEN
5818 FND_LOG.string( Fnd_Log.Level_Unexpected
5819 , G_MODULE_PREFIX||l_procedure_name||'.begin'
5820 , 'enter procedure');
5821 END IF; --(l_proc_level>=l_dbg_level)
5822
5823 l_org_id:=p_org_id;
5824 l_gta_header_id:=p_gta_header_id;
5825 l_has_difference:=FALSE;
5826
5827 -- To get meaning of header level attribute lookup code
5828 SELECT
5829 flv.meaning
5830 INTO
5831 l_amount_attr
5832 FROM
5833 fnd_lookup_values_vl flv
5834 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
5835 AND flv.lookup_code='AMOUNT';
5836
5837 SELECT
5838 flv.meaning
5839 INTO
5840 l_taxamount_attr
5841 FROM
5842 fnd_lookup_values_vl flv
5843 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
5844 AND flv.lookup_code='VAT_TAX_AMOUNT';
5845
5846 SELECT
5847 flv.meaning
5848 INTO
5849 l_cust_name_attr
5850 FROM
5851 fnd_lookup_values_vl flv
5852 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
5853 AND flv.lookup_code='CUSTOMER_NAME';
5854
5855 SELECT
5856 flv.meaning
5857 INTO
5858 l_bank_name_account_attr
5859 FROM
5860 fnd_lookup_values_vl flv
5861 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
5862 AND flv.lookup_code='BANK_NAME_ACCOUNT';
5863
5864 SELECT
5865 flv.meaning
5866 INTO
5867 l_address_phone_attr
5868 FROM
5869 fnd_lookup_values_vl flv
5870 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
5871 AND flv.lookup_code='ADDRESS_PHONE_NUMBER';
5872
5873 SELECT
5874 flv.meaning
5875 INTO
5876 l_invoicetype_attr
5877 FROM
5878 fnd_lookup_values_vl flv
5879 WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
5880 AND flv.lookup_code='INVOICE_TYPE';
5881
5882
5883
5884 OPEN c_gta_headers;
5885 LOOP
5886 FETCH c_gta_headers INTO
5887 l_gta_amount
5888 ,l_gta_taxamount
5889 ,l_gta_customer_name
5890 ,l_gta_taxpayer_id
5891 ,l_gta_customer_bank_account
5892 ,l_gta_customer_address_phone
5893 ,l_gta_trx_number
5894 ,l_gta_invoice_type
5895 ,l_gta_invoice_type_name;
5896 EXIT WHEN c_gta_headers%NOTFOUND;
5897 END LOOP;
5898 CLOSE c_gta_headers;
5899
5900 OPEN c_gt_headers;
5901 LOOP
5902 FETCH c_gt_headers INTO
5903 l_gt_amount
5904 ,l_gt_taxamount
5905 ,l_gt_customer_name
5906 ,l_gt_taxpayer_id
5907 ,l_gt_customer_bank_account
5908 ,l_gt_customer_address_phone
5909 ,l_gt_invoice_number
5910 ,l_gt_invoice_type
5911 ,l_gt_invoice_type_name;
5912 EXIT WHEN c_gt_headers%NOTFOUND;
5913 END LOOP;
5914 CLOSE c_gt_headers;
5915 --compare amount
5916 IF l_gta_amount<>l_gt_amount
5917 THEN
5918 INSERT INTO ar_gta_difference_temp(type
5919 ,ar_header_id
5920 ,attribute
5921 ,ar_value
5922 ,gta_invoice_num
5923 ,gta_value
5924 ,gt_invoice_num
5925 ,gt_value
5926 ,discrepancy
5927 )
5928 VALUES('HEADER'
5929 ,l_gta_header_id
5930 ,l_amount_attr
5931 ,NULL
5932 ,l_gta_trx_number
5933 ,l_gta_amount
5934 ,l_gt_invoice_number
5935 ,l_gt_amount
5936 ,l_gta_amount-l_gt_amount
5937 );
5938 l_has_difference:=TRUE;
5939 END IF;
5940 --compare tax amount
5941 IF l_gta_taxamount<>l_gt_taxamount
5942 THEN
5943 INSERT INTO ar_gta_difference_temp(type
5944 ,ar_header_id
5945 ,attribute
5946 ,ar_value
5947 ,gta_invoice_num
5948 ,gta_value
5949 ,gt_invoice_num
5950 ,gt_value
5951 ,discrepancy
5952 )
5953 VALUES('HEADER'
5954 ,l_gta_header_id
5955 ,l_taxamount_attr
5956 ,NULL
5957 ,l_gta_trx_number
5958 ,l_gta_taxamount
5959 ,l_gt_invoice_number
5960 ,l_gt_taxamount
5961 ,l_gta_taxamount-l_gt_taxamount
5962 );
5963 l_has_difference:=TRUE;
5964 END IF;
5965 --compare customer name
5966 IF trim(l_gta_customer_name)<>TRIM(l_gt_customer_name)
5967 THEN
5968 INSERT INTO ar_gta_difference_temp(type
5969 ,ar_header_id
5970 ,attribute
5971 ,ar_value
5972 ,gta_invoice_num
5973 ,gta_value
5974 ,gt_invoice_num
5975 ,gt_value
5976 ,discrepancy
5977 )
5978 VALUES('HEADER'
5979 ,l_gta_header_id
5980 ,l_cust_name_attr
5981 ,NULL
5982 ,l_gta_trx_number
5983 ,l_gta_customer_name
5984 ,l_gt_invoice_number
5985 ,l_gt_customer_name
5986 ,NULL
5987 );
5988 l_has_difference:=TRUE;
5989 END IF;
5990 --compare tax_payer_id
5991 IF trim(l_gta_taxpayer_id)<>TRIM(l_gt_taxpayer_id)
5992 THEN
5993 INSERT INTO ar_gta_difference_temp(type
5994 ,ar_header_id
5995 ,attribute
5996 ,ar_value
5997 ,gta_invoice_num
5998 ,gta_value
5999 ,gt_invoice_num
6000 ,gt_value
6001 ,discrepancy
6002 )
6003 VALUES('HEADER'
6004 ,l_gta_header_id
6005 ,l_taxpayer_id_attr
6006 ,NULL
6007 ,l_gta_trx_number
6008 ,l_gta_taxpayer_id
6009 ,l_gt_invoice_number
6010 ,l_gt_taxpayer_id
6011 ,NULL
6012 );
6013 l_has_difference:=TRUE;
6014 END IF;
6015
6016 --compare customer_bank_account
6017 IF trim(l_gta_customer_bank_account)<>TRIM(l_gt_customer_bank_account)
6018 THEN
6019 INSERT INTO ar_gta_difference_temp(type
6020 ,ar_header_id
6021 ,attribute
6022 ,ar_value
6023 ,gta_invoice_num
6024 ,gta_value
6025 ,gt_invoice_num
6026 ,gt_value
6027 ,discrepancy
6028 )
6029 VALUES('HEADER'
6030 ,l_gta_header_id
6031 ,l_bank_name_account_attr
6032 ,NULL
6033 ,l_gta_trx_number
6034 ,l_gta_customer_bank_account
6035 ,l_gt_invoice_number
6036 ,l_gt_customer_bank_account
6037 ,NULL
6038 );
6039 l_has_difference:=TRUE;
6040 END IF;
6041
6042 --compare customer address phone
6043 IF trim(l_gta_customer_address_phone)<>TRIM(l_gt_customer_address_phone)
6044 THEN
6045 INSERT INTO ar_gta_difference_temp(type
6046 ,ar_header_id
6047 ,attribute
6048 ,ar_value
6049 ,gta_invoice_num
6050 ,gta_value
6051 ,gt_invoice_num
6052 ,gt_value
6053 ,discrepancy
6054 )
6055 VALUES('HEADER'
6056 ,l_gta_header_id
6057 ,l_address_phone_attr
6058 ,NULL
6059 ,l_gta_trx_number
6060 ,l_gta_customer_address_phone
6061 ,l_gt_invoice_number
6062 ,l_gt_customer_address_phone
6063 ,NULL
6064 );
6065 l_has_difference:=TRUE;
6066 END IF;
6067
6068 --compare invoice type
6069 IF trim(l_gta_invoice_type_name)<>TRIM(l_gt_invoice_type_name)
6070 THEN
6071 INSERT INTO ar_gta_difference_temp(type
6072 ,ar_header_id
6073 ,attribute
6074 ,ar_value
6075 ,gta_invoice_num
6076 ,gta_value
6077 ,gt_invoice_num
6078 ,gt_value
6079 ,discrepancy
6080 )
6081 VALUES('HEADER'
6082 ,l_gta_header_id
6083 ,l_invoicetype_attr
6084 ,NULL
6085 ,l_gta_trx_number
6086 ,l_gta_invoice_type_name
6087 ,l_gt_invoice_number
6088 ,l_gt_invoice_type_name
6089 ,NULL
6090 );
6091 l_has_difference:=TRUE;
6092 END IF;
6093
6094 x_has_difference:=l_has_difference;
6095 EXCEPTION
6096 WHEN OTHERS THEN
6097 IF(Fnd_Log.Level_Unexpected >= Fnd_Log.G_Current_Runtime_Level)
6098 THEN
6099 Fnd_Log.String( Fnd_Log.Level_Unexpected
6100 , G_MODULE_PREFIX || l_procedure_name || '.Other_Exception '
6101 , Sqlcode||Sqlerrm);
6102 END IF; --Fnd_Log.Level_Unexpected >= Fnd_Log.G_Current_Runtime_Level
6103
6104 END Compare_Consolidated_Inv;
6105
6106 END AR_GTA_REPORTS_PKG;