1 PACKAGE AR_GTA_REPORTS_PKG AUTHID CURRENT_USER AS
2 --$Header: ARGRREPS.pls 120.0.12010000.3 2010/01/19 08:38:42 choli noship $
3 --+=======================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| ARRREPS.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is used to generate Golden Tax Adaptor reports |
13 --| |
14 --| HISTORY |
15 --| 05/08/05 Qiang Li Created |
16 --| 05/17/05 Donghai Wang Add procedures: |
17 --| Compare_Header |
18 --| Compare_Lines |
19 --| Get_Unmatched_Lines |
20 --| Generate_Discrepancy_Xml |
21 --| Generate_Discrepancy_Rep |
22 --| 09/27/05 Qiang Li Add function: |
23 --| Get_Gt_Tax_Reg_Count |
24 --| 05/12/05 Qiang Li Update Generate_Mapping_Rep |
25 --| Update Get_Gt_Trx |
26 --| Update Get_Ar_Trx |
27 --| Rename Get_Gt_Tax_Reg_Count to |
28 --| Get_Gt_Count |
29 --| 07/02/06 Qiang Li Update FUNCTION Get_Ar_Trx |
30 --| 06/18/07 Donghai Wang Update G_MODULE_PREFIX to follow|
31 --| FND log Standards |
32 --| 25-Jul-2009 Allen Yang Add functions and procedure: |
33 --| Get_Consolidation_Trx |
34 --| Get_Consolidated_Trxs |
35 --| Generate_Consol_Mapping_Rep|
36 --| for bug 8605196: ENHANCEMENT FOR|
37 --| GOLDEN TAX ADAPTER R12.1.2 |
38 --+======================================================================*/
39
40 --Declare global variable for package name
41 G_MODULE_PREFIX VARCHAR2(50) :='ar.plsql.AR_GTA_REPORTS_PKG';
42 --==========================================================================
43 -- FUNCTION NAME:
44 --
45 -- Get_Gt_Count Public
46 --
47 -- DESCRIPTION:
48 --
49 -- This function get GT trxs count in a given
50 -- AR transaction
51 -- PARAMETERS:
52 -- In: p_ar_trx_header_id AR transaction header id
53 -- p_fp_tax_reg_num first party tax registration number
54 -- P_Gt_Inv_Date_From Golden Tax Invoice Date from
55 -- P_Gt_Inv_Date_To Golden Tax Invoice Date to
56 -- P_Gt_Inv_Num_From Golden Tax Invoice Number from
57 -- P_Gt_Inv_Num_To Golden Tax Invoice Number to
58 -- Out:
59 --
60 -- Return: VARCHAR2
61 --
62 --
63 -- DESIGN REFERENCES:
64 -- GTA_REPORTS_TD.doc
65 --
66 -- CHANGE HISTORY:
67 --
68 -- 27-Sep-05 Qiang Li Created.
69 -- 05-Dec-05 Qiang Li Rename to get_gt_count
70 -- Add four new parameters
71 --
72 --===========================================================================
73 FUNCTION Get_Gt_Count
74 ( p_ar_trx_header_id IN NUMBER
75 , p_fp_tax_reg_num IN VARCHAR2
76 , P_Gt_Inv_Date_From IN DATE
77 , P_Gt_Inv_Date_To IN DATE
78 , P_Gt_Inv_Num_From IN VARCHAR2
79 , P_Gt_Inv_Num_To IN VARCHAR2
80 )
81 RETURN VARCHAR2;
82
83 --==========================================================================
84 -- FUNCTION NAME:
85 --
86 -- Get_Ar_Trx Public
87 --
88 -- DESCRIPTION:
89 --
90 -- This function get xml data of AR transaction.
91 --
92 -- PARAMETERS:
93 -- In: p_org_id Operating unit id
94 -- p_ar_trx_header_id AR transaction header id
95 -- p_fp_tax_reg_num first party tax registration number
96 -- P_Gt_Inv_Date_From Golden Tax Invoice Date from
97 -- P_Gt_Inv_Date_To Golden Tax Invoice Date to
98 -- P_Gt_Inv_Num_From Golden Tax Invoice Number from
99 -- P_Gt_Inv_Num_To Golden Tax Invoice Number to
100 -- Out:
101 --
102 -- Return: XMLTYPE
103 --
104 --
105 -- DESIGN REFERENCES:
106 -- GTA_REPORTS_TD.doc
107 --
108 -- CHANGE HISTORY:
109 --
110 -- 05/08/05 Qiang Li Created.
111 -- 09/27/05 Qiang Li Add new parameter p_fp_tax_reg_num
112 -- 05-Dec-2005 Qiang Li add four new parameters
113 -- 07-Feb-2006 Qiang Li Change data type of p_fp_tax_reg_num
114 -- to Varchar2
115 --===========================================================================
116 FUNCTION Get_Ar_Trx
117 ( p_ar_trx_header_id IN NUMBER
118 , p_org_id IN NUMBER
119 , p_fp_tax_reg_num IN VARCHAR2
120 , P_Gt_Inv_Date_From IN DATE
121 , P_Gt_Inv_Date_To IN DATE
122 , P_Gt_Inv_Num_From IN VARCHAR2
123 , P_Gt_Inv_Num_To IN VARCHAR2
124 )
125 RETURN XMLTYPE;
126
127 --==========================================================================
128 -- FUNCTION NAME:
129 --
130 -- Get_Gt_Trxs Public
131 --
132 -- DESCRIPTION:
133 --
134 -- This function get XML data of Golden Tax transactions
135 --
136 -- PARAMETERS:
137 -- In: p_org_id Operating unit id
138 -- p_ar_trx_header_id AR transaction header id
139 -- p_Tax_Registration_Number First party tax registration number
140 -- P_Gt_Inv_Date_From Golden Tax Invoice Date from
141 -- P_Gt_Inv_Date_To Golden Tax Invoice Date to
142 -- P_Gt_Inv_Num_From Golden Tax Invoice Number from
143 -- P_Gt_Inv_Num_To Golden Tax Invoice Number to
144 --
145 -- Out:
146 --
147 -- Return: XMLTYPE
148 --
149 --
150 -- DESIGN REFERENCES:
151 -- GTA_REPORTS_TD.doc
152 --
153 -- CHANGE HISTORY:
154 --
155 -- 05/08/05 Qiang Li Created.
156 -- 05/12/05 Qiang Li add five new parameters
157 --
158 --===========================================================================
159 FUNCTION Get_Gt_Trxs
160 ( P_Ar_Trx_Header_Id IN NUMBER
161 , P_Org_Id IN NUMBER
162 , p_Tax_Registration_Number IN VARCHAR2
163 , P_Gt_Inv_Date_From IN DATE
164 , P_Gt_Inv_Date_To IN DATE
165 , P_Gt_Inv_Num_From IN VARCHAR2
166 , P_Gt_Inv_Num_To IN VARCHAR2
167 )
168 RETURN Xmltype;
169
170
171 --==========================================================================
172 -- PROCEDURE NAME:
173 --
174 -- Generate_Mapping_Rep Public
175 --
176 -- DESCRIPTION:
177 --
178 -- This procedure generate mapping report data
179 --
180 -- PARAMETERS:
181 -- In: p_fp_tax_reg_num First Party Tax Registration Number
182 -- p_org_id Operating unit id
183 -- p_trx_source Transaction source,GT or AR
184 -- p_customer_id Customer id
185 -- p_gt_inv_num_from GT invoice number low range
186 -- p_gt_inv_num_to GT invoice number high range
187 -- p_gt_inv_date_from GT invoice date low range
188 -- p_gt_inv_date_to GT invoice date high range
189 -- p_ar_inv_num_from AR invoice number low range
190 -- p_ar_inv_num_to AR invoice number high range
191 -- p_ar_inv_date_from AR invoice date low range
192 -- p_ar_inv_date_to AR invoice date high range
193 -- Out:
194 --
195 --
196 --
197 -- DESIGN REFERENCES:
198 -- GTA_REPORTS_TD.doc
199 --
200 -- CHANGE HISTORY:
201 --
202 -- 05/08/05 Qiang Li Created.
203 -- 27-Sep-2005:Qiang Li Add a new parameter fp_tax_reg_number.
204 --
205 --===========================================================================
206 Procedure Generate_Mapping_Rep
207 ( p_org_id IN NUMBER
208 , p_fp_tax_reg_num IN VARCHAR2
209 , p_trx_source IN NUMBER
210 , p_customer_id IN NUMBER
211 , p_gt_inv_num_from IN VARCHAR2
212 , p_gt_inv_num_to IN VARCHAR2
213 , p_gt_inv_date_from IN DATE
214 , p_gt_inv_date_to IN DATE
215 , p_ar_inv_num_from IN VARCHAR2
216 , p_ar_inv_num_to IN VARCHAR2
217 , p_ar_inv_date_from IN DATE
218 , p_ar_inv_date_to IN DATE
219 );
220
221 --==========================================================================
222 -- PROCEDURE NAME:
223 --
224 -- Compare_Header Public
225 --
226 -- DESCRIPTION:
227 --
228 -- This Procedure Compare Ar, Gta, Gt Headers AND Input Difference Record
229 -- Compared Columns Include: "Amount", "Tax Amount", "Customer Name",
230 -- "Bank Name Account" and "Tax Payer Id"
231 --
232 -- PARAMETERS:
233 -- In: p_org_id Operating unit id
234 -- p_ar_header_id AR Transaction id
235 --
236 -- Out: x_has_difference
237 --
238 --
239 -- DESIGN REFERENCES:
240 -- GTA_REPORTS_TD.doc
241 --
242 -- CHANGE HISTORY:
243 --
244 -- 05/17/05 Donghai Wang Created.
245 --
246 --===========================================================================
247 PROCEDURE Compare_Header
248 ( p_org_id IN NUMBER
249 , p_ar_header_id IN NUMBER
250 , x_has_difference OUT NOCOPY BOOLEAN
251 );
252
253 --==========================================================================
254 -- PROCEDURE NAME:
255 --
256 -- Compare_Lines Public
257 --
258 -- DESCRIPTION:
259 --
260 -- This Procedure Compare Ar, Gta, Gt Lines And Input Difference Record
261 -- Compared Columns Include: "Goods Description", "Line Amount",
262 -- "Vat Line Tax", "Vat Tax Rate", "Quantity", "Unit Price" And "Uom"
263 --
264 -- PARAMETERS:
265 -- In: p_org_id Operating unit id
266 -- p_ar_header_id AR Transaction id
267 --
268 -- Out: x_has_difference
269 --
270 --
271 -- DESIGN REFERENCES:
272 -- GTA_REPORTS_TD.doc
273 --
274 -- CHANGE HISTORY:
275 --
276 -- 05/17/05 Donghai Wang Created.
277 --
278 --===========================================================================
279 PROCEDURE Compare_Lines
280 ( p_org_id IN NUMBER
281 , p_ar_header_id IN NUMBER
282 , x_validated_lines OUT NOCOPY NUMBER
283 , x_ar_matching_lines OUT NOCOPY NUMBER
284 , x_ar_partially_import OUT NOCOPY NUMBER
285 , x_has_difference OUT NOCOPY BOOLEAN
286 );
287
288 --==========================================================================
289 -- PROCEDURE NAME:
290 --
291 -- Get_Unmatched_Lines Public
292 --
293 -- DESCRIPTION:
294 --
295 -- This Procedure Get Gta, Gt Unmatched Lines And Input Difference Record
296 --
297 --
298 -- PARAMETERS:
299 -- In: p_org_id Operating unit id
300 -- p_ar_header_id AR Transaction id
301 --
302 -- Out: x_has_difference
303 --
304 --
305 -- DESIGN REFERENCES:
306 -- GTA_REPORTS_TD.doc
307 --
308 -- CHANGE HISTORY:
309 --
310 -- 05/17/05 Donghai Wang Created.
311 --
312 --==========================================================================
313 PROCEDURE Get_Unmatched_Lines
314 ( p_org_id IN NUMBER
315 , p_ar_header_id IN NUMBER
316 , x_has_difference OUT NOCOPY BOOLEAN
317 );
318
319
320 --==========================================================================
321 -- PROCEDURE NAME:
322 --
323 -- Generate_Discrepancy_Xml Public
324 --
325 -- DESCRIPTION:
326 --
327 -- This Procedure is used to generate XML element content
328 -- for disrcepancy report output
329 --
330 --
331 -- PARAMETERS:
332 -- In: p_org_id Operating unit id
333 -- p_gta_batch_num_from GTA batch number low range
334 -- p_gta_batch_num_to GTA batch number high range
335 -- p_ar_transaction_type AR transaction type
336 -- p_cust_num_from Customer Number low range
337 -- p_cust_num_to Customer Number high range
341 -- p_gl_date_from GL period date low range
338 -- p_cust_name_from Customer Name low range
339 -- p_cust_name_to Customer Name high range
340 -- p_gl_period GL period name
342 -- p_gl_date_to GL period date high range
343 -- p_ar_trx_batch_from AR Transaction name low range
344 -- p_ar_trx_batch_to AR Transaction name high range
345 -- p_ar_trx_num_from AR Transaction number low range
346 -- p_ar_trx_num_to AR Transaction number high range
347 -- p_ar_trx_date_from AR Transaction date low range
348 -- p_ar_trx_date_to AR Transaction date high range
349 -- p_ar_doc_num_from AR transaction document
350 -- sequence low range
351 -- p_ar_doc_num_to AR transaction document sequence high range
352 -- p_original_curr_code Currency code on AR transaction
353 -- p_primary_sales Primary salesperson
354 -- p_validated_lines_total the number of ar lines that have been
355 -- validated by the report
356 -- p_ar_matching_lines_total the number of ar lines that exactly
357 -- match with GTA invoice
358 -- and GT invoice
359 -- p_ar_partially_import_total ar lines are not fully imported to GT
360 --
361 -- Out: x_output
362 --
363 -- DESIGN REFERENCES:
364 -- GTA_REPORTS_TD.doc
365 --
366 -- CHANGE HISTORY:
367 --
368 -- 05/17/05 Donghai Wang Created.
369 --
370 --==========================================================================
371 PROCEDURE Generate_Discrepancy_Xml
372 ( p_org_id IN NUMBER
373 , p_gta_batch_num_from IN VARCHAR2
374 , p_gta_batch_num_to IN VARCHAR2
375 , p_ar_transaction_type IN NUMBER
376 , p_cust_num_from IN VARCHAR2
377 , p_cust_num_to IN VARCHAR2
378 , p_cust_name_id IN NUMBER
379 , p_gl_period IN VARCHAR2
380 , p_gl_date_from IN DATE
381 , p_gl_date_to IN DATE
382 , p_ar_trx_batch_from IN VARCHAR2
383 , p_ar_trx_batch_to IN VARCHAR2
384 , P_ar_trx_num_from IN VARCHAR2
385 , p_ar_trx_num_to IN VARCHAR2
386 , p_ar_trx_date_from IN DATE
387 , p_ar_trx_date_to IN DATE
388 , p_ar_doc_num_from IN VARCHAR2
389 , p_ar_doc_num_to IN VARCHAR2
390 , p_original_curr_code IN VARCHAR2
391 , p_primary_sales IN NUMBER
392 , p_validated_lines_total IN NUMBER
393 , p_ar_matching_lines_total IN NUMBER
394 , p_ar_partially_import_total IN NUMBER
395 , x_output OUT NOCOPY XMLTYPE
396 );
397
398
399 --==========================================================================
400 -- PROCEDURE NAME:
401 --
402 -- Generate_Discrepancy_Rep Public
403 --
404 -- DESCRIPTION:
405 --
406 -- This Procedure Generate Discrepancy Report Data
407 --
408 --
409 -- PARAMETERS:
410 -- In: p_org_id Operating unit id
411 -- p_gta_batch_num_from GTA batch number low range
412 -- p_gta_batch_num_to GTA batch number high range
413 -- p_ar_transaction_type AR transaction type
414 -- p_cust_num_from Customer Number low range
415 -- p_cust_num_to Customer Number high range
416 -- p_cust_name_from Customer Name low range
417 -- p_cust_name_to Customer Name high range
418 -- p_gl_period GL period name
419 -- p_gl_date_from GL period date low range
420 -- p_gl_date_to GL period date high range
421 -- p_ar_trx_batch_from AR Transaction name low range
422 -- p_ar_trx_batch_to AR Transaction name high range
423 -- p_ar_trx_num_from AR Transaction number low range
424 -- p_ar_trx_num_to AR Transaction number high range
425 -- p_ar_trx_date_from AR Transaction date low range
426 -- p_ar_trx_date_to AR Transaction date high range
427 -- p_ar_doc_num_from AR transaction document sequence low range
428 -- p_ar_doc_num_to AR transaction document sequence high range
429 -- p_original_curr_code Currency code on AR transaction
430 -- p_primary_sales Primary salesperson
431 --
432 -- Out:
433 --
434 -- DESIGN REFERENCES:
435 -- GTA_REPORTS_TD.doc
436 --
437 -- CHANGE HISTORY:
438 --
439 -- 05/17/05 Donghai Wang Created.
440 --
441 --==========================================================================
442 PROCEDURE Generate_Discrepancy_Rep
443 ( p_org_id IN NUMBER
444 , p_gta_batch_num_from IN VARCHAR2
448 , p_cust_num_to IN VARCHAR2
445 , p_gta_batch_num_to IN VARCHAR2
446 , p_ar_transaction_type IN NUMBER
447 , p_cust_num_from IN VARCHAR2
449 , p_cust_name_id IN NUMBER
450 , p_gl_period IN VARCHAR2
451 , p_gl_date_from IN VARCHAR2
452 , p_gl_date_to IN VARCHAR2
453 , p_ar_trx_batch_from IN VARCHAR2
454 , p_ar_trx_batch_to IN VARCHAR2
455 , P_ar_trx_num_from IN VARCHAR2
456 , p_ar_trx_num_to IN VARCHAR2
457 , p_ar_trx_date_from IN VARCHAR2
458 , p_ar_trx_date_to IN VARCHAR2
459 , p_ar_doc_num_from IN VARCHAR2
460 , p_ar_doc_num_to IN VARCHAR2
461 , p_original_curr_code IN VARCHAR2
462 , p_primary_sales IN NUMBER
463 );
464
465 --==========================================================================
466 -- PROCEDURE NAME:
467 --
468 -- Generate_Consol_Mapping_Rep Public
469 --
470 -- DESCRIPTION:
471 --
472 -- This procedure generates Invoice Consolidation Mapping Report data.
473 --
474 --
475 -- PARAMETERS:
476 -- In: p_org_id Operating unit id
477 -- p_gl_period GL period
478 -- p_customer_num_from Customer number low range
479 -- p_customer_num_to Customer number high range
480 -- p_customer_name_from Customer name low range
481 -- p_customer_name_to Customer name high range
482 -- p_consol_trx_num_from Consolidated invoice number low range
483 -- p_consol_trx_num_to Consolidated invoice number high range
484 -- p_invoice_type Invoice type
485 --
486 -- Out:
487 --
488 -- DESIGN REFERENCES:
489 -- GTA_12.1.2_Technical_Design.doc
490 --
491 -- CHANGE HISTORY:
492 --
493 -- 25-Jul-2009 Allen Yang Created.
494 --
495 --==========================================================================
496 Procedure Generate_Consol_Mapping_Rep
497 ( p_org_Id IN NUMBER
498 , p_gl_period IN VARCHAR2
499 , p_customer_num_from IN VARCHAR2
500 , p_customer_num_to IN VARCHAR2
501 , p_customer_name_from IN VARCHAR2
502 , p_customer_name_to IN VARCHAR2
503 , p_consol_trx_num_from IN VARCHAR2
504 , p_consol_trx_num_to IN VARCHAR2
505 , p_invoice_type IN VARCHAR2
506 );
507
508 --==========================================================================
509 -- PROCEDURE NAME:
510 --
511 -- Get_Consolidation_Trx Public
512 --
513 -- DESCRIPTION:
514 --
515 -- This procedure returns XML data for a given consolidated invoice.
516 --
517 --
518 -- PARAMETERS:
519 -- In: p_trx_header_id invoice header identifier
520 --
521 --
522 -- Out:
523 --
524 -- DESIGN REFERENCES:
525 -- GTA_12.1.2_Technical_Design.doc
526 --
527 -- CHANGE HISTORY:
528 --
529 -- 25-Jul-2009 Allen Yang Created.
530 --
531 --==========================================================================
532 FUNCTION Get_Consolidation_Trx(p_trx_header_id IN NUMBER)
533 RETURN XMLTYPE;
534
535 --==========================================================================
536 -- PROCEDURE NAME:
537 --
538 -- Get_Consolidated_Trxs Public
539 --
540 -- DESCRIPTION:
541 --
542 -- For a given consolidated invoice, get xml data of its
543 -- consolidation invoices.
544 --
545 --
546 -- PARAMETERS:
547 -- In: p_trx_header_id invoice header identifier
548 --
549 --
550 -- Out:
551 --
552 -- DESIGN REFERENCES:
553 -- GTA_12.1.2_Technical_Design.doc
554 --
555 -- CHANGE HISTORY:
556 --
557 -- 25-Jul-2009 Allen Yang Created.
558 --
559 --==========================================================================
560 FUNCTION Get_Consolidated_Trxs(p_trx_header_id IN NUMBER)
561 RETURN XMLTYPE;
562 --==========================================================================
563 -- PROCEDURE NAME:
564 --
565 -- Compare_Consolidated_Inv Public
566 --
567 -- DESCRIPTION:
568 --
569 -- This Procedure Compare completed consolidated_invs with gt invoice
570 --
571 -- PARAMETERS:
572 -- In: p_org_id Operating unit id
573 -- p_gta_header_id GTA invoice header id
574 --
575 -- Out: x_has_difference
576 --
577 --
578 -- DESIGN REFERENCES:
579 --
580 --
581 -- CHANGE HISTORY:
582 --
583 -- 09-Aug-2009 Yao Zhang Created.
584 --===========================================================================
585 PROCEDURE Compare_Consolidated_Inv
586 (p_org_id NUMBER
587 ,p_gta_header_id NUMBER
588 ,x_has_difference OUT NOCOPY BOOLEAN
589 ) ;
590 END AR_GTA_REPORTS_PKG;
[Home] [Help]