1 PACKAGE JMF_GTA_REPORTS_PKG AS
2 --$Header: JMFRREPS.pls 120.6.12010000.2 2008/11/17 14:34:12 dwang ship $
3 --+=======================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JMFRREPS.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 --+======================================================================*/
33
34 --Declare global variable for package name
35 G_MODULE_PREFIX VARCHAR2(50) :='jmf.plsql.JMF_GTA_REPORTS_PKG';
36 --==========================================================================
37 -- FUNCTION NAME:
38 --
39 -- Get_Gt_Count Public
40 --
41 -- DESCRIPTION:
42 --
43 -- This function get GT trxs count in a given
44 -- AR transaction
45 -- PARAMETERS:
46 -- In: p_ar_trx_header_id AR transaction header id
47 -- p_fp_tax_reg_num first party tax registration number
48 -- P_Gt_Inv_Date_From Golden Tax Invoice Date from
49 -- P_Gt_Inv_Date_To Golden Tax Invoice Date to
50 -- P_Gt_Inv_Num_From Golden Tax Invoice Number from
51 -- P_Gt_Inv_Num_To Golden Tax Invoice Number to
52 -- Out:
53 --
54 -- Return: VARCHAR2
55 --
56 --
57 -- DESIGN REFERENCES:
58 -- GTA_REPORTS_TD.doc
59 --
60 -- CHANGE HISTORY:
61 --
62 -- 27-Sep-05 Qiang Li Created.
63 -- 05-Dec-05 Qiang Li Rename to get_gt_count
64 -- Add four new parameters
65 --
66 --===========================================================================
67 FUNCTION Get_Gt_Count
68 ( p_ar_trx_header_id IN NUMBER
69 , p_fp_tax_reg_num IN VARCHAR2
70 , P_Gt_Inv_Date_From IN DATE
71 , P_Gt_Inv_Date_To IN DATE
72 , P_Gt_Inv_Num_From IN VARCHAR2
73 , P_Gt_Inv_Num_To IN VARCHAR2
74 )
75 RETURN VARCHAR2;
76
77 --==========================================================================
78 -- FUNCTION NAME:
79 --
80 -- Get_Ar_Trx Public
81 --
82 -- DESCRIPTION:
83 --
84 -- This function get xml data of AR transaction.
85 --
86 -- PARAMETERS:
87 -- In: p_org_id Operating unit id
88 -- p_ar_trx_header_id AR transaction header id
89 -- p_fp_tax_reg_num first party tax registration number
90 -- P_Gt_Inv_Date_From Golden Tax Invoice Date from
91 -- P_Gt_Inv_Date_To Golden Tax Invoice Date to
92 -- P_Gt_Inv_Num_From Golden Tax Invoice Number from
93 -- P_Gt_Inv_Num_To Golden Tax Invoice Number to
94 -- Out:
95 --
96 -- Return: XMLTYPE
97 --
98 --
99 -- DESIGN REFERENCES:
100 -- GTA_REPORTS_TD.doc
101 --
102 -- CHANGE HISTORY:
103 --
104 -- 05/08/05 Qiang Li Created.
105 -- 09/27/05 Qiang Li Add new parameter p_fp_tax_reg_num
106 -- 05-Dec-2005 Qiang Li add four new parameters
107 -- 07-Feb-2006 Qiang Li Change data type of p_fp_tax_reg_num
108 -- to Varchar2
109 --===========================================================================
110 FUNCTION Get_Ar_Trx
111 ( p_ar_trx_header_id IN NUMBER
112 , p_org_id IN NUMBER
113 , p_fp_tax_reg_num IN VARCHAR2
114 , P_Gt_Inv_Date_From IN DATE
115 , P_Gt_Inv_Date_To IN DATE
116 , P_Gt_Inv_Num_From IN VARCHAR2
117 , P_Gt_Inv_Num_To IN VARCHAR2
118 )
119 RETURN XMLTYPE;
120
121 --==========================================================================
122 -- FUNCTION NAME:
123 --
124 -- Get_Gt_Trxs Public
125 --
126 -- DESCRIPTION:
127 --
128 -- This function get XML data of Golden Tax transactions
129 --
130 -- PARAMETERS:
131 -- In: p_org_id Operating unit id
132 -- p_ar_trx_header_id AR transaction header id
133 -- p_Tax_Registration_Number First party tax registration number
134 -- P_Gt_Inv_Date_From Golden Tax Invoice Date from
135 -- P_Gt_Inv_Date_To Golden Tax Invoice Date to
136 -- P_Gt_Inv_Num_From Golden Tax Invoice Number from
137 -- P_Gt_Inv_Num_To Golden Tax Invoice Number to
138 --
139 -- Out:
140 --
141 -- Return: XMLTYPE
142 --
143 --
144 -- DESIGN REFERENCES:
145 -- GTA_REPORTS_TD.doc
146 --
147 -- CHANGE HISTORY:
148 --
149 -- 05/08/05 Qiang Li Created.
150 -- 05/12/05 Qiang Li add five new parameters
151 --
152 --===========================================================================
153 FUNCTION Get_Gt_Trxs
154 ( P_Ar_Trx_Header_Id IN NUMBER
155 , P_Org_Id IN NUMBER
156 , p_Tax_Registration_Number IN VARCHAR2
157 , P_Gt_Inv_Date_From IN DATE
158 , P_Gt_Inv_Date_To IN DATE
159 , P_Gt_Inv_Num_From IN VARCHAR2
160 , P_Gt_Inv_Num_To IN VARCHAR2
161 )
162 RETURN Xmltype;
163
164
165 --==========================================================================
166 -- PROCEDURE NAME:
167 --
168 -- Generate_Mapping_Rep Public
169 --
170 -- DESCRIPTION:
171 --
172 -- This procedure generate mapping report data
173 --
174 -- PARAMETERS:
175 -- In: p_fp_tax_reg_num First Party Tax Registration Number
176 -- p_org_id Operating unit id
177 -- p_trx_source Transaction source,GT or AR
178 -- p_customer_id Customer id
179 -- p_gt_inv_num_from GT invoice number low range
180 -- p_gt_inv_num_to GT invoice number high range
181 -- p_gt_inv_date_from GT invoice date low range
182 -- p_gt_inv_date_to GT invoice date high range
183 -- p_ar_inv_num_from AR invoice number low range
184 -- p_ar_inv_num_to AR invoice number high range
185 -- p_ar_inv_date_from AR invoice date low range
186 -- p_ar_inv_date_to AR invoice date high range
187 -- Out:
188 --
189 --
190 --
191 -- DESIGN REFERENCES:
192 -- GTA_REPORTS_TD.doc
193 --
194 -- CHANGE HISTORY:
195 --
196 -- 05/08/05 Qiang Li Created.
197 -- 27-Sep-2005:Qiang Li Add a new parameter fp_tax_reg_number.
198 --
199 --===========================================================================
200 Procedure Generate_Mapping_Rep
201 ( p_org_id IN NUMBER
202 , p_fp_tax_reg_num IN VARCHAR2
203 , p_trx_source IN NUMBER
204 , p_customer_id IN NUMBER
205 , p_gt_inv_num_from IN VARCHAR2
206 , p_gt_inv_num_to IN VARCHAR2
207 , p_gt_inv_date_from IN DATE
208 , p_gt_inv_date_to IN DATE
209 , p_ar_inv_num_from IN VARCHAR2
210 , p_ar_inv_num_to IN VARCHAR2
211 , p_ar_inv_date_from IN DATE
212 , p_ar_inv_date_to IN DATE
213 );
214
215 --==========================================================================
216 -- PROCEDURE NAME:
217 --
218 -- Compare_Header Public
219 --
220 -- DESCRIPTION:
221 --
222 -- This Procedure Compare Ar, Gta, Gt Headers AND Input Difference Record
223 -- Compared Columns Include: "Amount", "Tax Amount", "Customer Name",
224 -- "Bank Name Account" and "Tax Payer Id"
225 --
226 -- PARAMETERS:
227 -- In: p_org_id Operating unit id
228 -- p_ar_header_id AR Transaction id
229 --
230 -- Out: x_has_difference
231 --
232 --
233 -- DESIGN REFERENCES:
234 -- GTA_REPORTS_TD.doc
235 --
236 -- CHANGE HISTORY:
237 --
238 -- 05/17/05 Donghai Wang Created.
239 --
240 --===========================================================================
241 PROCEDURE Compare_Header
242 ( p_org_id IN NUMBER
243 , p_ar_header_id IN NUMBER
244 , x_has_difference OUT NOCOPY BOOLEAN
245 );
246
247 --==========================================================================
248 -- PROCEDURE NAME:
249 --
250 -- Compare_Lines Public
251 --
252 -- DESCRIPTION:
253 --
254 -- This Procedure Compare Ar, Gta, Gt Lines And Input Difference Record
255 -- Compared Columns Include: "Goods Description", "Line Amount",
256 -- "Vat Line Tax", "Vat Tax Rate", "Quantity", "Unit Price" And "Uom"
257 --
258 -- PARAMETERS:
259 -- In: p_org_id Operating unit id
260 -- p_ar_header_id AR Transaction id
261 --
262 -- Out: x_has_difference
263 --
264 --
265 -- DESIGN REFERENCES:
266 -- GTA_REPORTS_TD.doc
267 --
268 -- CHANGE HISTORY:
269 --
270 -- 05/17/05 Donghai Wang Created.
271 --
272 --===========================================================================
273 PROCEDURE Compare_Lines
274 ( p_org_id IN NUMBER
275 , p_ar_header_id IN NUMBER
276 , x_validated_lines OUT NOCOPY NUMBER
277 , x_ar_matching_lines OUT NOCOPY NUMBER
278 , x_ar_partially_import OUT NOCOPY NUMBER
279 , x_has_difference OUT NOCOPY BOOLEAN
280 );
281
282 --==========================================================================
283 -- PROCEDURE NAME:
284 --
285 -- Get_Unmatched_Lines Public
286 --
287 -- DESCRIPTION:
288 --
289 -- This Procedure Get Gta, Gt Unmatched Lines And Input Difference Record
290 --
291 --
292 -- PARAMETERS:
293 -- In: p_org_id Operating unit id
294 -- p_ar_header_id AR Transaction id
295 --
296 -- Out: x_has_difference
297 --
298 --
299 -- DESIGN REFERENCES:
300 -- GTA_REPORTS_TD.doc
301 --
302 -- CHANGE HISTORY:
303 --
304 -- 05/17/05 Donghai Wang Created.
305 --
306 --==========================================================================
307 PROCEDURE Get_Unmatched_Lines
308 ( p_org_id IN NUMBER
309 , p_ar_header_id IN NUMBER
310 , x_has_difference OUT NOCOPY BOOLEAN
311 );
312
313
314 --==========================================================================
315 -- PROCEDURE NAME:
316 --
317 -- Generate_Discrepancy_Xml Public
318 --
319 -- DESCRIPTION:
320 --
321 -- This Procedure is used to generate XML element content
322 -- for disrcepancy report output
323 --
324 --
325 -- PARAMETERS:
326 -- In: p_org_id Operating unit id
327 -- p_gta_batch_num_from GTA batch number low range
328 -- p_gta_batch_num_to GTA batch number high range
329 -- p_ar_transaction_type AR transaction type
330 -- p_cust_num_from Customer Number low range
331 -- p_cust_num_to Customer Number high range
332 -- p_cust_name_from Customer Name low range
333 -- p_cust_name_to Customer Name high range
334 -- p_gl_period GL period name
335 -- p_gl_date_from GL period date low range
336 -- p_gl_date_to GL period date high range
337 -- p_ar_trx_batch_from AR Transaction name low range
338 -- p_ar_trx_batch_to AR Transaction name high range
339 -- p_ar_trx_num_from AR Transaction number low range
340 -- p_ar_trx_num_to AR Transaction number high range
341 -- p_ar_trx_date_from AR Transaction date low range
342 -- p_ar_trx_date_to AR Transaction date high range
343 -- p_ar_doc_num_from AR transaction document
344 -- sequence low range
345 -- p_ar_doc_num_to AR transaction document sequence high range
346 -- p_original_curr_code Currency code on AR transaction
347 -- p_primary_sales Primary salesperson
348 -- p_validated_lines_total the number of ar lines that have been
349 -- validated by the report
350 -- p_ar_matching_lines_total the number of ar lines that exactly
351 -- match with GTA invoice
352 -- and GT invoice
356 --
353 -- p_ar_partially_import_total ar lines are not fully imported to GT
354 --
355 -- Out: x_output
357 -- DESIGN REFERENCES:
358 -- GTA_REPORTS_TD.doc
359 --
360 -- CHANGE HISTORY:
361 --
362 -- 05/17/05 Donghai Wang Created.
363 --
364 --==========================================================================
365 PROCEDURE Generate_Discrepancy_Xml
366 ( p_org_id IN NUMBER
367 , p_gta_batch_num_from IN VARCHAR2
368 , p_gta_batch_num_to IN VARCHAR2
369 , p_ar_transaction_type IN NUMBER
370 , p_cust_num_from IN VARCHAR2
371 , p_cust_num_to IN VARCHAR2
372 , p_cust_name_id IN NUMBER
373 , p_gl_period IN VARCHAR2
374 , p_gl_date_from IN DATE
375 , p_gl_date_to IN DATE
376 , p_ar_trx_batch_from IN VARCHAR2
377 , p_ar_trx_batch_to IN VARCHAR2
378 , P_ar_trx_num_from IN VARCHAR2
379 , p_ar_trx_num_to IN VARCHAR2
380 , p_ar_trx_date_from IN DATE
381 , p_ar_trx_date_to IN DATE
382 , p_ar_doc_num_from IN VARCHAR2
383 , p_ar_doc_num_to IN VARCHAR2
384 , p_original_curr_code IN VARCHAR2
385 , p_primary_sales IN NUMBER
386 , p_validated_lines_total IN NUMBER
387 , p_ar_matching_lines_total IN NUMBER
388 , p_ar_partially_import_total IN NUMBER
389 , x_output OUT NOCOPY XMLTYPE
390 );
391
392
393 --==========================================================================
394 -- PROCEDURE NAME:
395 --
396 -- Generate_Discrepancy_Rep Public
397 --
398 -- DESCRIPTION:
399 --
400 -- This Procedure Generate Discrepancy Report Data
401 --
402 --
403 -- PARAMETERS:
404 -- In: p_org_id Operating unit id
405 -- p_gta_batch_num_from GTA batch number low range
406 -- p_gta_batch_num_to GTA batch number high range
407 -- p_ar_transaction_type AR transaction type
408 -- p_cust_num_from Customer Number low range
409 -- p_cust_num_to Customer Number high range
410 -- p_cust_name_from Customer Name low range
411 -- p_cust_name_to Customer Name high range
412 -- p_gl_period GL period name
413 -- p_gl_date_from GL period date low range
414 -- p_gl_date_to GL period date high range
415 -- p_ar_trx_batch_from AR Transaction name low range
416 -- p_ar_trx_batch_to AR Transaction name high range
417 -- p_ar_trx_num_from AR Transaction number low range
418 -- p_ar_trx_num_to AR Transaction number high range
419 -- p_ar_trx_date_from AR Transaction date low range
420 -- p_ar_trx_date_to AR Transaction date high range
421 -- p_ar_doc_num_from AR transaction document sequence low range
422 -- p_ar_doc_num_to AR transaction document sequence high range
423 -- p_original_curr_code Currency code on AR transaction
424 -- p_primary_sales Primary salesperson
425 --
426 -- Out:
427 --
428 -- DESIGN REFERENCES:
429 -- GTA_REPORTS_TD.doc
430 --
431 -- CHANGE HISTORY:
432 --
433 -- 05/17/05 Donghai Wang Created.
434 --
435 --==========================================================================
436 PROCEDURE Generate_Discrepancy_Rep
437 ( p_org_id IN NUMBER
438 , p_gta_batch_num_from IN VARCHAR2
439 , p_gta_batch_num_to IN VARCHAR2
440 , p_ar_transaction_type IN NUMBER
441 , p_cust_num_from IN VARCHAR2
442 , p_cust_num_to IN VARCHAR2
443 , p_cust_name_id IN NUMBER
444 , p_gl_period IN VARCHAR2
445 , p_gl_date_from IN VARCHAR2
446 , p_gl_date_to IN VARCHAR2
447 , p_ar_trx_batch_from IN VARCHAR2
448 , p_ar_trx_batch_to IN VARCHAR2
449 , P_ar_trx_num_from IN VARCHAR2
450 , p_ar_trx_num_to IN VARCHAR2
451 , p_ar_trx_date_from IN VARCHAR2
452 , p_ar_trx_date_to IN VARCHAR2
453 , p_ar_doc_num_from IN VARCHAR2
454 , p_ar_doc_num_to IN VARCHAR2
455 , p_original_curr_code IN VARCHAR2
456 , p_primary_sales IN NUMBER
457 );
458
459 END JMF_GTA_REPORTS_PKG;
460
[Home] [Help]