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