1 PACKAGE "JMF_GTA_TRX_UTIL" AUTHID CURRENT_USER AS
2 ----$Header: JMFUGTAS.pls 120.10.12010000.2 2008/11/17 14:40:26 dwang ship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation
5 --| Redwood Shores, California, USA
6 --| All rights reserved.
7 --+===========================================================================
8 --|
9 --| FILENAME :
10 --| JMFUGTAS.pls
11 --|
12 --| DESCRIPTION:
13 --| This package is a collection of the util procedure
14 --| or function.
15 --|
16 --| PROCEDURE LIST
17 --| PROCEDURE Output_Conc
18 --| PROCEDURE Create_Trxs
19 --| PROCEDURE Create_Trx
20 --| FUNCTION Get_Gtainvoice_Amount
21 --| FUNCTION Get_Gtainvoice_Original_Amount
22 --| PROCEDURE Delete_Header_Line_Cascade
23 --| FUNCTION Get_Gtainvoice_Tax_Amount
24 --| FUNCTION Check_Taxcount_Of_Arline
25 --| FUNCTION Check_Taxcount_Of_Artrx
26 --| FUNCTION Get_Arinvoice_Amount
27 --| FUNCTION Get_Arinvoice_Tax_Amount
28 --| FUNCTION Format_Date
29 --| FUNCTION Get_Primary_Phone_Number
30 --| FUNCTION Get_Operatingunit
31 --| FUNCTION Get_Customer_Name
32 --| FUNCTION Get_Arline_Amount
33 --| FUNCTION Get_Arline_Vattax_Amount
34 --| FUNCTION Get_Arline_Vattax_Rate
35 --| PROCEDURE Get_Bank_Info
36 --| PROCEDURE Verify_Tax_Line
37 --| PROCEDURE Get_Info_From_Ebtax
38 --| PROCEDURE Get_Tp_Tax_Registration_Number
39 --| FUNCTION Get_Arline_Tp_Taxreg_Number
40 --| PROCEDURE Debug_Output
41 --| FUNCTION Get_AR_Batch_Source_Name
42 --| FUNCTION To_Xsd_Date_String
43 --| FUNCTION Format_Monetary_Amount
44 --| FUNCTION Get_Invoice_Type
45 --|
46 --| HISTORY:
47 --| 20-APR-2005: Jim Zheng Created
48 --|
49 --| 22-Aug-2005: Jim Zheng Modify: New feature about registration
50 --| Number
51 --|
52 --| 11-Oct-2005: Jim Zheng Modify: modify some select tax_line_id code
53 --| in get_info_from_ebtax
54 --| add where entity_code = 'TRANSACTONS'.
55 --|
56 --| 13-OCt-2005: Jim Zheng Modify: modify the parametere of
57 --| get_tp_tax_registration. remove the
58 --| input para p_trx_line_id, add a new
59 --\ input parameter p_tax_line_id
60 --| add a new procedure verify_tax_line.
61 --| add a new procedure debug_output
62 --| 19-Oct-2005: Jim Zheng Modify: update the procedure
63 --| get_info_from_ebtax, add a output
64 --| parameter
65 --| x_taxable_amount_org for get original
66 --| currency amount.
67 --| 20-Oct-2005: Jim Zheng Modify: Add a procedure debug_output_conc for
68 --| dubug report. remove the hard code
69 --| for fp_registration_number
70 --| in get_info_from_ebtax
71 --| Add tax_rate/100 in output value
72 --| in get_info_from_ebtax
73 --| 24-Nov-2005 Donghai Wang Modify procedure 'Get_Arline_Amount'
74 --| to add a new parameter
75 --| and use real code to replace dummy code
76 --| 24-Nov-2005 Donghai Wang Add a new parameter for function
77 --| 'Get_Arline_Vattax_Amount'
78 --| 24-Nov-2005 Donghai Wang Add a new parameter for function
79 --| 'Get_Arline_Vattax_Rate'
80 --| 25-Nov-2005 Donghai Wang Add a new function
81 --| Get_Arline_Tp_Taxreg_Number
82 --| 25-Nov-2005 Donghai Wang Add a new function
83 --| 'Check_Taxcount_Of_Arline'
84 --| 25-Nov-2005 Donghai Wang Add a new function
85 --| 'Check_Taxcount_Of_Artrx'
86 --| 25-Nov-2005 Donghai Wang update function 'Get_Arinvoice_Amount'
87 --| to follow ebtax logic
88 --| 25-Nov-2005 Donghai Wang update functon
89 --| 'Get_Arinvoice_Tax_Amount'
90 --| to follow ebtax logic
91 --| 28-Nov-2005 Jim Zheng remove the default value of
92 --| fp regi number, procedure
93 --| get_info_from_ebtax
94 --| 28-Nov-2005 Jim Zheng remove the default value of return
95 --| status of procedure get_info_from_ebtax
96 --| 28-Nov-2005 Jim Zheng add GTA currency code when get tax line
97 --| in procedure verify_tax_line.
98 --| 01-DEC-2005 Qiang Li add a new function Get_AR_Batch_Source_Name
99 --| 29-JUN-2006 Shujuan Yan In Get_Info_From_Ebtax, Add a output
100 --| parameter x_tax_curr_unit_price to
101 --| store the unit price of tax currency
102 --| for bug 5168900
103 --| 14-Sep-2006 Donghai Wang Added the new function
104 --| To_Xsd_Date_String to convert date
105 --| values into XSD format so that they can
106 --| be formatted correctly in XML Publisher
107 --| Reports for bug 5521629.
108 --| 20-Sep-2006 Donghai Wang Added the new function
109 -- Fomrat_Monetary_Amount
110 --
111 --| 28-Dec-2007 Subba Added new function get_invoice_type for R12.1,
112 --| Added new column 'invoice_type' to trx_header_rec_type
113 --+===========================================================================+
114
115 --Declare global variable for package name
116 g_module_prefix VARCHAR2(30) := 'jmf.plsql.JMF_GTA_TRX_UTIL';
117
118 --The Record for the parameters of transfer program
119 TYPE transferparas_rec_type IS RECORD
120 (customer_num_from hz_cust_accounts.account_number%TYPE
121 ,customer_num_to hz_cust_accounts.account_number%TYPE
122 ,customer_name_from hz_parties.party_name%TYPE
123 ,customer_name_to hz_parties.party_name%TYPE
124 ,gl_period VARCHAR2(30)
125 ,gl_date_from ra_cust_trx_line_gl_dist_all.gl_date%TYPE
126 ,gl_date_to ra_cust_trx_line_gl_dist_all.gl_date%TYPE
127 ,trx_batch_from ra_batches_all.NAME%TYPE
128 ,trx_batch_to ra_batches_all.NAME%TYPE
129 ,trx_number_from ra_customer_trx_all.trx_number%TYPE
130 ,trx_number_to ra_customer_trx_all.trx_number%TYPE
131 ,trx_date_from ra_customer_trx_all.trx_date%TYPE
132 ,trx_date_to ra_customer_trx_all.trx_date%TYPE
133 ,doc_num_from ra_customer_trx_all.doc_sequence_value%TYPE
134 ,doc_num_to ra_customer_trx_all.doc_sequence_value%TYPE);
135
136 TYPE condition_para_tbl_type IS TABLE OF VARCHAR2(100);
137
138 --This record is the data type of JMF_GTA_TRX_HEADERS_ALL;
139 TYPE trx_header_rec_type IS RECORD
140 (row_id VARCHAR2(30)
141 ,ra_gl_date jmf_gta_trx_headers_all.ra_gl_date%TYPE
142 ,ra_gl_period jmf_gta_trx_headers_all.ra_gl_period%TYPE
143 ,set_of_books_id jmf_gta_trx_headers_all.set_of_books_id%TYPE
144 ,bill_to_customer_id jmf_gta_trx_headers_all.bill_to_customer_id%TYPE
145 ,bill_to_customer_number jmf_gta_trx_headers_all.bill_to_customer_number%TYPE
146 ,bill_to_customer_name jmf_gta_trx_headers_all.bill_to_customer_name%TYPE
147 ,SOURCE jmf_gta_trx_headers_all.SOURCE%TYPE
148 ,org_id jmf_gta_trx_headers_all.org_id%TYPE
149 ,rule_header_id jmf_gta_trx_headers_all.rule_header_id%TYPE
150 ,gta_trx_header_id jmf_gta_trx_headers_all.gta_trx_header_id%TYPE
151 ,gta_trx_number jmf_gta_trx_headers_all.gta_trx_number%TYPE
152 ,group_number jmf_gta_trx_headers_all.group_number%TYPE
153 ,version jmf_gta_trx_headers_all.version%TYPE
154 ,latest_version_flag jmf_gta_trx_headers_all.latest_version_flag%TYPE
155 ,transaction_date jmf_gta_trx_headers_all.transaction_date%TYPE
156 ,ra_trx_id jmf_gta_trx_headers_all.ra_trx_id%TYPE
157 ,ra_trx_number jmf_gta_trx_headers_all.ra_trx_number%TYPE
158 ,description jmf_gta_trx_headers_all.description%TYPE
159 ,customer_address jmf_gta_trx_headers_all.customer_address%TYPE
160 ,customer_phone jmf_gta_trx_headers_all.customer_phone%TYPE
161 ,customer_address_phone jmf_gta_trx_headers_all.customer_address_phone%TYPE
162 ,bank_account_name jmf_gta_trx_headers_all.bank_account_name%TYPE
163 ,bank_account_number jmf_gta_trx_headers_all.bank_account_number%TYPE
164 ,bank_account_name_number jmf_gta_trx_headers_all.bank_account_name_number%TYPE
165 ,fp_tax_registration_number jmf_gta_trx_headers_all.fp_tax_registration_number%TYPE --fp registration number
166 ,tp_tax_registration_number jmf_gta_trx_headers_all.tp_tax_registration_number%TYPE --tp registration number
167 ,legal_entity_id jmf_gta_trx_headers_all.legal_entity_id%TYPE -- legal entity id
168 ,ra_currency_code jmf_gta_trx_headers_all.ra_currency_code%TYPE
169 ,conversion_type jmf_gta_trx_headers_all.conversion_type%TYPE
170 ,conversion_date jmf_gta_trx_headers_all.conversion_date%TYPE
171 ,conversion_rate jmf_gta_trx_headers_all.conversion_rate%TYPE
172 ,gta_batch_number jmf_gta_trx_headers_all.gta_batch_number%TYPE
173 ,gt_invoice_number jmf_gta_trx_headers_all.gt_invoice_number%TYPE
174 ,gt_invoice_date jmf_gta_trx_headers_all.gt_invoice_date%TYPE
175 ,gt_invoice_net_amount jmf_gta_trx_headers_all.gt_invoice_net_amount%TYPE
176 ,gt_invoice_tax_amount jmf_gta_trx_headers_all.gt_invoice_tax_amount%TYPE
177 ,status jmf_gta_trx_headers_all.status%TYPE
178 ,sales_list_flag jmf_gta_trx_headers_all.sales_list_flag%TYPE
179 ,cancel_flag jmf_gta_trx_headers_all.cancel_flag%TYPE
180 ,gt_invoice_type jmf_gta_trx_headers_all.gt_invoice_type%TYPE
181 ,gt_invoice_class jmf_gta_trx_headers_all.gt_invoice_class%TYPE
182 ,gt_tax_month jmf_gta_trx_headers_all.gt_tax_month%TYPE
183 ,issuer_name jmf_gta_trx_headers_all.issuer_name%TYPE
184 ,reviewer_name jmf_gta_trx_headers_all.reviewer_name%TYPE
185 ,payee_name jmf_gta_trx_headers_all.payee_name%TYPE
186 ,tax_code jmf_gta_trx_headers_all.tax_code%TYPE
187 ,tax_rate jmf_gta_trx_headers_all.tax_rate%TYPE
188 ,generator_id jmf_gta_trx_headers_all.generator_id%TYPE
189 ,export_request_id jmf_gta_trx_headers_all.export_request_id%TYPE
190 ,request_id jmf_gta_trx_headers_all.request_id%TYPE
191 ,program_application_id jmf_gta_trx_headers_all.program_application_id%TYPE
192 ,program_id jmf_gta_trx_headers_all.program_id%TYPE
193 ,program_update_date jmf_gta_trx_headers_all.program_update_date%TYPE
194 ,attribute_category jmf_gta_trx_headers_all.attribute_category%TYPE
195 ,attribute1 jmf_gta_trx_headers_all.attribute1%TYPE
196 ,attribute2 jmf_gta_trx_headers_all.attribute2%TYPE
197 ,attribute3 jmf_gta_trx_headers_all.attribute3%TYPE
198 ,attribute4 jmf_gta_trx_headers_all.attribute4%TYPE
199 ,attribute5 jmf_gta_trx_headers_all.attribute5%TYPE
200 ,attribute6 jmf_gta_trx_headers_all.attribute6%TYPE
201 ,attribute7 jmf_gta_trx_headers_all.attribute7%TYPE
202 ,attribute8 jmf_gta_trx_headers_all.attribute8%TYPE
203 ,attribute9 jmf_gta_trx_headers_all.attribute9%TYPE
204 ,attribute10 jmf_gta_trx_headers_all.attribute10%TYPE
205 ,attribute11 jmf_gta_trx_headers_all.attribute11%TYPE
206 ,attribute12 jmf_gta_trx_headers_all.attribute12%TYPE
207 ,attribute13 jmf_gta_trx_headers_all.attribute13%TYPE
208 ,attribute14 jmf_gta_trx_headers_all.attribute14%TYPE
209 ,attribute15 jmf_gta_trx_headers_all.attribute15%TYPE
210 ,creation_date jmf_gta_trx_headers_all.creation_date%TYPE
211 ,created_by jmf_gta_trx_headers_all.created_by%TYPE
212 ,last_update_date jmf_gta_trx_headers_all.last_update_date%TYPE
213 ,last_updated_by jmf_gta_trx_headers_all.last_updated_by%TYPE
214 ,last_update_login jmf_gta_trx_headers_all.last_update_login%TYPE
215 ,invoice_type jmf_gta_trx_headers_all.invoice_type%TYPE --added by subba for R12.1
216 );
217
218 --This record is the data type of JMF_GTA_TRX_LINES_ALL;
219 TYPE trx_line_rec_type IS RECORD
220 (
221 row_id VARCHAR2(30)
222 ,org_id jmf_gta_trx_lines_all.org_id%TYPE
223 ,gta_trx_header_id jmf_gta_trx_lines_all.gta_trx_header_id%TYPE
224 ,gta_trx_line_id jmf_gta_trx_lines_all.gta_trx_line_id%TYPE
225 ,matched_flag jmf_gta_trx_lines_all.matched_flag%TYPE
226 ,line_number jmf_gta_trx_lines_all.line_number%TYPE
227 ,ar_trx_line_id jmf_gta_trx_lines_all.ar_trx_line_id%TYPE
228 ,inventory_item_id jmf_gta_trx_lines_all.inventory_item_id%TYPE
232 ,item_tax_denomination jmf_gta_trx_lines_all.item_tax_denomination%TYPE
229 ,item_number VARCHAR2(30)--jmf_gta_trx_lines_all.item_number%TYPE
230 ,item_description jmf_gta_trx_lines_all.item_description%TYPE
231 ,item_model jmf_gta_trx_lines_all.item_model%TYPE
233 ,tax_rate jmf_gta_trx_lines_all.tax_rate%TYPE
234 ,uom jmf_gta_trx_lines_all.uom%TYPE
235 ,uom_name jmf_gta_trx_lines_all.uom_name%TYPE
236 ,quantity jmf_gta_trx_lines_all.quantity%TYPE
237 ,price_flag jmf_gta_trx_lines_all.price_flag%TYPE
238 ,unit_price jmf_gta_trx_lines_all.unit_price%TYPE
239 ,unit_tax_price jmf_gta_trx_lines_all.unit_tax_price%TYPE
240 ,amount jmf_gta_trx_lines_all.amount%TYPE
241 ,original_currency_amount jmf_gta_trx_lines_all.original_currency_amount%TYPE
242 ,tax_amount jmf_gta_trx_lines_all.tax_amount%TYPE
243 ,discount_flag jmf_gta_trx_lines_all.discount_flag%TYPE
244 ,enabled_flag jmf_gta_trx_lines_all.enabled_flag%TYPE
245 ,request_id jmf_gta_trx_lines_all.request_id%TYPE
246 ,program_applicaton_id jmf_gta_trx_lines_all.program_application_id%TYPE
247 ,program_id jmf_gta_trx_lines_all.program_id%TYPE
248 ,program_update_date jmf_gta_trx_lines_all.program_update_date%TYPE
249 ,attribute_category jmf_gta_trx_lines_all.attribute_category%TYPE
250 ,attribute1 jmf_gta_trx_lines_all.attribute1%TYPE
251 ,attribute2 jmf_gta_trx_lines_all.attribute2%TYPE
252 ,attribute3 jmf_gta_trx_lines_all.attribute3%TYPE
253 ,attribute4 jmf_gta_trx_lines_all.attribute4%TYPE
254 ,attribute5 jmf_gta_trx_lines_all.attribute5%TYPE
255 ,attribute6 jmf_gta_trx_lines_all.attribute6%TYPE
256 ,attribute7 jmf_gta_trx_lines_all.attribute7%TYPE
257 ,attribute8 jmf_gta_trx_lines_all.attribute8%TYPE
258 ,attribute9 jmf_gta_trx_lines_all.attribute9%TYPE
259 ,attribute10 jmf_gta_trx_lines_all.attribute10%TYPE
260 ,attribute11 jmf_gta_trx_lines_all.attribute11%TYPE
261 ,attribute12 jmf_gta_trx_lines_all.attribute12%TYPE
262 ,attribute13 jmf_gta_trx_lines_all.attribute13%TYPE
263 ,attribute14 jmf_gta_trx_lines_all.attribute14%TYPE
264 ,attribute15 jmf_gta_trx_lines_all.attribute15%TYPE
265 ,creation_date jmf_gta_trx_lines_all.creation_date%TYPE
266 ,created_by jmf_gta_trx_lines_all.created_by%TYPE
267 ,last_update_date jmf_gta_trx_lines_all.last_update_date%TYPE
268 ,last_updated_by jmf_gta_trx_lines_all.last_updated_by%TYPE
269 ,last_update_login jmf_gta_trx_lines_all.last_update_login%TYPE
270 ,fp_tax_registration_number VARCHAR2(50)
271 ,tp_tax_registration_number VARCHAR2(50)
272 );
273
274 -- This type is a group of TRX_line_rec_TYPE;
275 TYPE trx_line_tbl_type IS TABLE OF trx_line_rec_type;
276
277 -- This Type is a invoice which include a header record and a group of line record;
278 TYPE trx_rec_type IS RECORD(
279 trx_header trx_header_rec_type
280 ,trx_lines trx_line_tbl_type);
281
282 --This type is a group of TRX_Tbl_TYPE; in fact it is a group of invoice.
283 TYPE trx_tbl_type IS TABLE OF trx_rec_type;
284
285 --==========================================================================
286 -- PROCEDURE NAME:
287 --
288 -- Output_Conc Public
289 --
290 -- DESCRIPTION:
291 --
292 -- This procedure write data to concurrent output file
293 -- the data can be longer than 4000
294 --
295 -- PARAMETERS:
296 -- In: p_clob the content which need output to concurrent output
297 --
298 --
299 -- DESIGN REFERENCES:
300 --
301 --
302 -- CHANGE HISTORY:
303 --
304 -- 30-APR-2005: qugen.hu Created.
305 --
306 --===========================================================================
307 PROCEDURE output_conc(p_clob IN CLOB);
308
309
310 --==========================================================================
311 -- PROCEDURE NAME:
312 --
313 -- debug_output_conc Public
314 --
315 -- DESCRIPTION:
316 --
317 -- This procedure write data to concurrent output file
318 -- the data can be longer than 4000
319 --
320 -- PARAMETERS:
321 -- In: p_clob the content which need output to concurrent output
322 --
323 --
324 -- DESIGN REFERENCES:
325 --
326 --
327 -- CHANGE HISTORY:
328 --
329 -- 30-APR-2005: Jim.zheng Created.
330 --
331 --===========================================================================
332 PROCEDURE debug_output_conc(p_clob IN CLOB);
333
334 --==========================================================================
335 -- PROCEDURE NAME:
336 --
337 -- Create_Trxs Public
338 --
339 -- DESCRIPTION:
340 --
341 -- This package can insert a set of trx to JMF_GTA_TRX_HEADS_ALL
342 -- AND JMF_GTA_TRX_LINES_ALL.
343 --
344 -- PARAMETERS:
345 -- In: p_gta_trxs trx_tbl_type
346 --
350 --
347 --
348 -- DESIGN REFERENCES:
349 -- GTA-TRANSFER-PROGRAM-TD.doc
351 -- CHANGE HISTORY:
352 --
353 -- 30-APR-2005: Jim Zheng Created.
354 --
355 --===========================================================================
356 PROCEDURE create_trxs(p_gta_trxs IN trx_tbl_type);
357
358 --==========================================================================
359 -- PROCEDURE NAME:
360 --
361 -- Create_Trx Public
362 --
363 -- DESCRIPTION:
364 --
365 -- This procedure is to insert a GTA transaction
366 --
367 -- PARAMETERS:
368 -- In: p_gta_trx Standard API parameter
369 --
370 --
371 -- DESIGN REFERENCES:
372 -- GTA-TRANSFER-PROGRAM-TD.doc
373 --
374 -- CHANGE HISTORY:
375 --
376 -- 30-APR-2005: Jim Zheng Created.
377 --
378 --===========================================================================
379 PROCEDURE create_trx(p_gta_trx IN trx_rec_type);
380
381 --==========================================================================
382 -- FUNCTION NAME:
383 --
384 -- Get_Gtainvoice_Amount Public
385 --
386 -- DESCRIPTION:
387 --
388 -- This procedure is to calculate total amount of a GTA invoice
389 --
390 -- PARAMETERS:
391 -- In: p_header_id Identifier of GTA Invoice header
392 --
393 -- Return: NUMBER
394 --
395 -- DESIGN REFERENCES:
396 -- GTA-TRANSFER-PROGRAM-TD.doc
397 --
398 -- CHANGE HISTORY:
399 --
400 -- 30-APR-2005: Jim Zheng Created.
401 --
402 --===========================================================================
403 FUNCTION get_gtainvoice_amount(p_header_id IN NUMBER) RETURN NUMBER;
404
405 --==========================================================================
406 -- FUNCTION NAME:
407 --
408 -- Get_Gtainvoice_Original_Amount Public
409 --
410 -- DESCRIPTION:
411 --
412 -- This procedure is to calculate total amount of a GTA invoice
413 -- in original currency code
414 --
415 -- PARAMETERS:
416 -- In: p_header_id Identifier of GTA Invoice header
417 --
418 -- Return: NUMBER
419 --
420 -- DESIGN REFERENCES:
421 -- GTA-TRANSFER-PROGRAM-TD.doc
422 --
423 -- CHANGE HISTORY:
424 --
425 -- 30-APR-2005: Jim Zheng Created.
426 --
427 --===========================================================================
428 FUNCTION get_gtainvoice_original_amount(p_header_id IN NUMBER) RETURN NUMBER;
429
430 --==========================================================================
431 -- PROCEDURE NAME:
432 --
433 -- Delete_Header_Line_Cascade Public
434 --
435 -- DESCRIPTION:
436 --
437 -- This procedure is to cascade delete a special GTA/GT
438 -- invoice header with all lines associated with it
439 --
440 -- PARAMETERS:
441 -- In: p_gta_trx_header_id GTA/GT invoice header identifier
442 --
443 -- DESIGN REFERENCES:
444 -- GTA-PURGE-PROGRAM-TD.doc
445 --
446 -- CHANGE HISTORY:
447 --
448 -- 8-MAY-2005: Qiang Li Created
449 --
450 --===========================================================================
451 PROCEDURE delete_header_line_cascade(p_gta_trx_header_id IN NUMBER);
452
453 --==========================================================================
454 -- FUNCTION NAME:
455 --
456 -- Get_Gtainvoice_Tax_Amount Public
457 --
458 -- DESCRIPTION:
459 --
460 -- This procedure Get Gtainvoice Tax Amount
461 --
462 -- PARAMETERS:
463 -- In: p_header_id identifier of Gta Invoice
464 --
465 -- Return: NUMBER
466 --
467 -- DESIGN REFERENCES:
468 -- GTA_Reports_TD.doc
469 --
470 -- CHANGE HISTORY:
471 --
472 -- 8-MAY-2005: Qiang Li Created
473 --
474 --===========================================================================
475 FUNCTION get_gtainvoice_tax_amount(p_header_id IN NUMBER) RETURN NUMBER;
476
477
478 --==========================================================================
479 -- FUNCTION NAME:
480 --
481 -- Check_Taxcount_Of_Arline Public
482 --
483 -- DESCRIPTION:
484 --
485 -- This function is used to check if one AR line has multiple tax line per
486 -- Tax type and GT currency defined on GTA system option form
487 --
488 -- PARAMETERS:
489 -- In: p_org_id Identifier of operating unit
490 -- p_customer_trx_line_id Identifier of transaction line id
491 --
492 -- Return: BOOLEAN
493 --
494 -- DESIGN REFERENCES:
495 -- GTA_Reports_TD.doc
496 --
497 -- CHANGE HISTORY:
498 --
499 -- 25-Nov-2005: Donghai Wang Created
500 --
501 --===========================================================================
502 FUNCTION Check_Taxcount_Of_Arline
503 (p_org_id IN NUMBER
507
504 ,p_customer_trx_line_id IN NUMBER
505 )
506 RETURN BOOLEAN;
508 --==========================================================================
509 -- FUNCTION NAME:
510 --
511 -- Check_Taxcount_Of_Artrx Public
512 --
513 -- DESCRIPTION:
514 --
515 -- This function is used to check if AR lines belong to one AR transaction
516 -- have multiple tax line per Tax type and GT currency defined on GTA system
517 -- option form.
518 --
519 -- PARAMETERS:
520 -- In: p_org_id Identifier of operating unit
521 -- p_customer_trx_id Identifier of AR transaciton
522 --
523 -- Return: BOOLEAN
524 --
525 -- DESIGN REFERENCES:
526 -- GTA_Reports_TD.doc
527 --
528 -- CHANGE HISTORY:
529 --
530 -- 25-Nov-2005: Donghai Wang Created
531 --
532 --===========================================================================
533 FUNCTION Check_Taxcount_Of_Artrx
534 (p_org_id IN NUMBER
535 ,p_customer_trx_id IN NUMBER
536 )
537 RETURN BOOLEAN;
538
539 --==========================================================================
540 -- FUNCTION NAME:
541 --
542 -- Get_Arinvoice_Amount Public
543 --
544 -- DESCRIPTION:
545 --
546 -- This Function is to get taxable amount of an AR transaction per VAT tax
547 -- type and GT currency code defind in GTA 'system options' form
548 --
549 -- PARAMETERS:
550 -- In: p_org_id identifier of operating unit
551 -- p_customer_trx_id identifier of AR transaction
552 --
553 -- Return: NUMBER
554 --
555 -- DESIGN REFERENCES:
556 -- GTA_Reports_TD.doc
557 --
558 -- CHANGE HISTORY:
559 --
560 -- 8-MAY-2005: Qiang Li Created
561 -- 25-Nov-2005: Donghai Wang update code due to ebtax requirement
562 --===========================================================================
563 FUNCTION Get_Arinvoice_Amount
564 (p_org_id IN NUMBER
565 ,p_customer_trx_id IN NUMBER
566 )
567 RETURN NUMBER;
568
569 --==========================================================================
570 -- FUNCTION NAME:
571 --
572 -- Get_Arinvoice_Tax_Amount Public
573 --
574 -- DESCRIPTION:
575 --
576 -- This Function is to get tax amount of an AR transaction per VAT tax
577 -- type and GT currency code defind in GTA 'system options' form
578 --
579 -- PARAMETERS:
580 -- In: p_org_id identifier of operating unit
581 -- p_customer_trx_id identifier of AR transaction
582 --
583 -- Return: Number
584 --
585 -- DESIGN REFERENCES:
586 -- GTA_Reports_TD.doc
587 --
588 -- CHANGE HISTORY:
589 --
590 -- 8-MAY-2005: Qiang Li Created
591 -- 25-Nov-2005: Donghai Wang update code due to ebtax requirement
592 --===========================================================================
593 FUNCTION Get_Arinvoice_Tax_Amount
594 (p_org_id IN NUMBER
595 ,p_customer_trx_id IN NUMBER
596 )
597 RETURN NUMBER;
598
599 --==========================================================================
600 -- FUNCTION NAME:
601 --
602 -- Format_Date Public
603 --
604 -- DESCRIPTION:
605 --
606 -- This funtion is to get appropriate format string for
607 -- a given date according the ICX_DATE_FORMAT_MASK profile
608 --
609 -- PARAMETERS:
610 -- In: p_date The date to be formate
611 --
612 -- Return: VARCHAR2
613 --
614 -- DESIGN REFERENCES:
615 -- GTA_Reports_TD.doc
616 --
617 -- CHANGE HISTORY:
618 --
619 -- 23-MAy-2005: Qiang Li Creation
620 --
621 --===========================================================================
622 FUNCTION format_date(p_date IN DATE) RETURN VARCHAR2;
623
624 --==========================================================================
625 -- FUNCTION NAME:
626 --
627 -- Get_Primary_Phone_Number Public
628 --
629 -- DESCRIPTION:
630 --
631 -- This procedure is to get primary phone number for a given customer
632 --
633 -- PARAMETERS:
634 -- In: p_customer_id Customer identifier
635 --
636 -- Return: VARCHAR2
637 --
638 -- DESIGN REFERENCES:
639 -- GTA_Reports_TD.doc
640 --
641 -- CHANGE HISTORY:
642 --
643 -- 23-MAy-2005: Donghai Wang Created
644 --
645 --===========================================================================
646 FUNCTION get_primary_phone_number(p_customer_id IN NUMBER) RETURN VARCHAR2;
647
648 --==========================================================================
649 -- FUNCTION NAME:
650 --
651 -- Get_Operatingunit Public
652 --
653 -- DESCRIPTION:
654 --
655 -- This function is to get operating unit for a given org_id
656 --
657 -- PARAMETERS:
658 -- In: p_org_id Identifier of Operating Unit
659 --
660 -- Return: VARCHAR2
661 --
662 -- DESIGN REFERENCES:
663 -- GTA_Reports_TD.doc
664 --
668 --
665 -- CHANGE HISTORY:
666 --
667 -- 23-MAy-2005: Qiang Li Creation
669 --=========================================================================
670 FUNCTION get_operatingunit(p_org_id IN NUMBER) RETURN VARCHAR2;
671
672 --==========================================================================
673 -- FUNCTION NAME:
674 --
675 -- Get_Customer_Name Public
676 --
677 -- DESCRIPTION:
678 --
679 -- This function is to get Customer name for a given customer id
680 --
681 -- PARAMETERS:
682 -- In: p_customer_id customer identifier
683 --
684 -- Return: VARCHAR2
685 --
686 -- DESIGN REFERENCES:
687 -- GTA_Reports_TD.doc
688 --
689 -- CHANGE HISTORY:
690 --
691 -- 23-MAy-2005: Qiang Li Creation
692 --
693 --=========================================================================
694 FUNCTION get_customer_name(p_customer_id IN NUMBER) RETURN VARCHAR2;
695
696 --==========================================================================
697 -- FUNCTION NAME:
698 --
699 -- Get_Arline_Amount Public
700 --
701 -- DESCRIPTION:
702 --
703 -- This function is used to get line amount per Golden Tax currency for
704 -- one AR line
705 --
706 --
707 -- PARAMETERS:
708 -- In: p_org_id identifier of operating unit
709 -- p_customer_trx_line_id AR line identifier
710 -- Return: NUMBER
711 --
712 -- DESIGN REFERENCES:
713 -- GTA_Reports_TD.doc
714 --
715 -- CHANGE HISTORY:
716 --
717 -- 13-Jun-2005: Donghai Wang Creation
718 -- 24-Nov-2005: Donghai Wang Add a new parameter
719 -- 'p_org_id'
720 --
721 --
722 --=========================================================================
723 FUNCTION Get_Arline_Amount
724 (p_org_id IN NUMBER
725 ,p_customer_trx_line_id IN NUMBER
726 )
727 RETURN NUMBER;
728
729 --==========================================================================
730 -- FUNCTION NAME:
731 --
732 -- Get_Arline_Vattax_Amount Public
733 --
734 -- DESCRIPTION:
735 --
736 -- This function is used to get VAT amount based on one AR line
737 -- per Golden Tax currency
738 --
739 -- PARAMETERS:
740 -- In: p_org_id Identifier of operating unit
741 -- p_customer_trx_line_id AR line identifier
742 --
743 -- Return: NUMBER
744 --
745 -- DESIGN REFERENCES:
746 -- GTA_Reports_TD.doc
747 --
748 -- CHANGE HISTORY:
749 --
750 -- 13-Jun-2005: Donghai Wang Creation
751 --
752 --=========================================================================
753 FUNCTION Get_Arline_Vattax_Amount
754 (p_org_id IN NUMBER
755 ,p_customer_trx_line_id IN NUMBER
756 )
757 RETURN NUMBER;
758
759 --==========================================================================
760 -- FUNCTION NAME:
761 --
762 -- Get_Arline_Vattax_Rate Public
763 --
764 -- DESCRIPTION:
765 --
766 -- This function is used to get VAT rate for one AR line
767 --
768 -- PARAMETERS:
769 -- In: p_org_id Identifier of Operating Unit
770 -- p_customer_trx_line_id AR line identifier
771 --
772 -- Return: NUMBER
773 --
774 -- DESIGN REFERENCES:
775 -- GTA_Reports_TD.doc
776 --
777 -- CHANGE HISTORY:
778 --
779 -- 13-Jun-2005: Donghai Wang Creation
780 --
781 --=========================================================================
782 FUNCTION Get_Arline_Vattax_Rate
783 (p_org_id IN NUMBER
784 ,p_customer_trx_line_id IN NUMBER
785 )
786 RETURN NUMBER;
787
788 --==========================================================================
789 -- Procedure NAME:
790 --
791 -- get_bank_info Public
792 --
793 -- DESCRIPTION:
794 --
795 -- This function get bank infomations by cust_Trx_id, if the bank info from AR
796 -- is null. then get bank infomations by customer_id
797 --
798 -- PARAMETERS:
799 -- In:
800 -- p_customer_trx_id IN NUMBER
801 -- p_org_id in NUMBER
802 -- OUT:
803 -- x_bank_name OUT NOCOPY VARCHAR2
804 -- x_bank_branch_name OUT NOCOPY VARCHAR2
805 -- x_bank_account_name OUT NOCOPY VARCHAR2
806 -- x_bank_account_num OUT NOCOPY VARCHAR2
807 --
808 --
809 -- DESIGN REFERENCES:
810 --
811 --
812 -- CHANGE HISTORY:
813 --
814 -- 17-AUG-2005: JIM.Zheng Created
815 --
816 --===========================================================================
817 PROCEDURE get_bank_info
818 ( p_customer_trx_id IN NUMBER
819 , p_org_id IN NUMBER
820 , x_bank_name OUT NOCOPY VARCHAR2
824 );
821 , x_bank_branch_name OUT NOCOPY VARCHAR2
822 , x_bank_account_name OUT NOCOPY VARCHAR2
823 , x_bank_account_num OUT NOCOPY VARCHAR2
825
826 --==========================================================================
827 -- Procedure NAME:
828 --
829 -- verify_tax_line Public
830 --
831 -- DESCRIPTION:
832 --
833 -- Verify the tax lines number of a trx line, is it is not 1 , return fail
834 --
835 -- PARAMETERS:
836
837 -- p_trx_line_id IN NUMBER
838 -- p_tax_type_code IN VARCHAR2
839 -- p_currency_code in varchar2
840 -- x_status OUT NOCOPY NUMBER
841 -- x_tax_line_id OUT NOCOPY zx_lines.tax_line_id%TYPE
842 --
843 --
844 -- DESIGN REFERENCES:
845 --
846 --
847 -- CHANGE HISTORY:
848 --
849 -- 13-Oct-2005: JIM.Zheng Created
850 --
851 --===========================================================================
852 PROCEDURE verify_tax_line
853 (p_trx_line_id IN NUMBER
854 , p_tax_type_code IN VARCHAR2
855 , p_currency_code IN VARCHAR2
856 , x_status OUT NOCOPY NUMBER
857 , x_tax_line_id OUT NOCOPY zx_lines.tax_line_id%TYPE
858 );
859 --==========================================================================
860 -- Procedure NAME:
861 --
862 -- get_info_from_ebtax Public
863 --
864 -- DESCRIPTION:
865 --
866 -- This function get data from ebtax
867 --
868 -- PARAMETERS:
869 -- p_org_id IN NUMBER
870 -- p_trx_id IN NUMBER
871 -- p_trx_line_id IN NUMBER
872 -- p_tax_type_code IN VARCHAR2
873 -- x_tax_amount OUT NOCOPY NUMBER
874 -- x_taxable_amount OUT NOCOPY NUMBER
875 -- x_trx_line_quantity OUT NOCOPY NUMBER
876 -- x_tax_rate OUT NOCOPY NUMBER
877 -- x_unit_selling_price OUT NOCOPY NUMBER
878 -- x_taxable_amount OUT NOCOPY NUMBER
879 -- x_fp_registration_number OUT NOCOPY VARCHAR2
880 -- x_tp_registration_number OUT NOCOPY VARCHAR2
881 -- x_status OUT NOCOPY NUMBER
882 -- x_error_buffer OUT NOCOPY VARCHAR2
883 --
884 --
885 -- DESIGN REFERENCES:
886 --
887 --
888 -- CHANGE HISTORY:
889 --
890 -- 17-SEP-2005: JIM.Zheng Created
891 --
892 --===========================================================================
893 -- 29-JUN-2006 Added a parameter x_tax_curr_unit_price to store the unit
894 -- price of tax currency by Shujuan for bug 5168900
895 PROCEDURE Get_Info_From_Ebtax
896 (p_org_id IN NUMBER
897 ,p_trx_id IN NUMBER
898 ,p_trx_line_id IN NUMBER
899 ,p_tax_type_code IN VARCHAR2
900 ,x_tax_amount OUT NOCOPY NUMBER
901 ,x_taxable_amount OUT NOCOPY NUMBER
902 ,x_trx_line_quantity OUT NOCOPY NUMBER
903 ,x_tax_rate OUT NOCOPY NUMBER
904 ,x_unit_selling_price OUT NOCOPY NUMBER
905 ,x_tax_curr_unit_price OUT NOCOPY NUMBER
906 ,x_taxable_amount_org OUT NOCOPY NUMBER
907 ,x_fp_registration_number OUT NOCOPY VARCHAR2
908 ,x_tp_registration_number OUT NOCOPY VARCHAR2
909 ,x_status OUT NOCOPY NUMBER
910 ,x_invoice_type OUT NOCOPY VARCHAR2
911 ,x_error_buffer OUT NOCOPY VARCHAR2
912 );
913
914 --==========================================================================
915 -- Procedure NAME:
916 --
917 -- get_tp_tax_registration_number Public
918 --
919 -- DESCRIPTION:
920 --
921 -- This function third party registration number by trx line id
922 --
923 -- PARAMETERS:
924 -- In:
925 -- p_trx_id IN NUMBER
926 -- p_tax_line_id in number
927 -- OUT:
928 -- x_tp_tax_registration_number OUT NOCOPY VARCHAR2
929 --
930 --
931 --
932 -- DESIGN REFERENCES:
933 --
934 --
935 -- CHANGE HISTORY:
936 --
937 -- 17-AUG-2005: JIM.Zheng Created
938 --
939 --===========================================================================
940 PROCEDURE get_tp_tax_registration_number
941 ( p_trx_id IN NUMBER
942 , p_tax_line_id IN NUMBER
943 , x_tp_tax_registration_number OUT NOCOPY VARCHAR2
944 );
945
946 --==========================================================================
947 -- Procedure NAME:
948 --
949 -- Get_Arline_Tp_Taxreg_Number Public
950 --
951 -- DESCRIPTION:
952 --
953 -- This function is to get third party tax registration number upon one
954 -- AR line according to GTA logic
955 --
956 -- PARAMETERS:
957 -- In: p_org_id Identifier of operating unit
958 -- p_customer_trx_id Identifier of AR transaction
959 -- p_customer_trx_line_id Identifier of AR transaction line
960 --
961 -- Out:
962 --
963 -- Return:
964 -- VARCHAR2
965 --
966 --
967 --
968 -- DESIGN REFERENCES:
969 -- GTA_Reports_TD.doc
970 --
971 -- CHANGE HISTORY:
972 --
973 -- 25-Nov-2005: Donghai Wang Created
974 --
975 --===========================================================================
976 FUNCTION Get_Arline_Tp_Taxreg_Number
977 (p_org_id IN NUMBER
978 ,p_customer_trx_id IN NUMBER
979 ,p_customer_trx_line_id IN NUMBER
980 )
981 RETURN VARCHAR2;
982
983 --========================================================================
984 -- PROCEDURE : debug_output PUBLIC
985 -- PARAMETERS: p_output_to Identifier of where to output to
986 -- p_log_level log level
987 -- p_api_name the called api name
988 -- p_message the message that need to be output
989 -- COMMENT : the debug output, for using in readonly UT environment
990 -- PRE-COND :
991 -- EXCEPTIONS:
992 --========================================================================
993 PROCEDURE debug_output
994 ( p_output_to IN VARCHAR2
995 , p_log_level IN NUMBER
996 , p_api_name IN VARCHAR2
997 , p_message IN VARCHAR2
998 );
999
1000 --==========================================================================
1001 -- FUNCTION NAME:
1002 --
1003 -- Get_AR_Batch_Source_Name Public
1004 --
1005 -- DESCRIPTION:
1006 --
1007 -- This function is to get AR Batch Source Name for a given org_id and
1008 -- source id
1009 --
1010 -- PARAMETERS:
1011 -- In: p_org_id Identifier of Operating Unit
1012 -- In: p_source_id AR batch source id
1013 -- Return: VARCHAR2
1014 --
1015 -- DESIGN REFERENCES:
1016 -- GTA_Reports_TD.doc
1017 --
1018 -- CHANGE HISTORY:
1019 --
1020 -- 01-Dec-2005: Qiang Li Creation
1021 --
1022 --=========================================================================
1023 FUNCTION Get_AR_Batch_Source_Name
1024 ( p_org_id IN NUMBER
1025 , p_source_id IN NUMBER
1026 )
1027 RETURN VARCHAR2;
1028
1029 --==========================================================================
1030 -- FUNCTION NAME:
1031 --
1032 -- To_Xsd_Date_String Public
1033 --
1034 -- DESCRIPTION:
1035 --
1036 -- Convert an Oracle DB Date Object to a date string represented
1037 -- in the XSD Date Format. This is mainly for use by the
1038 -- XML Publisher Reports.
1039 --
1040 -- PARAMETERS:
1041 -- In: p_date Oracle Date to be converted to XSD Date Format
1042 --
1043 -- Return: VARCHAR2 A String representing the passed in Date in XSD
1044 -- Date Format
1045 --
1046 -- DESIGN REFERENCES:
1047 --
1048 --
1049 -- CHANGE HISTORY:
1050 --
1051 -- 14-Sep-2006: Donghai Wang Creation
1052 --
1053 --=========================================================================
1054 FUNCTION To_Xsd_Date_String
1055 ( p_date IN DATE
1056 )
1057 RETURN VARCHAR2;
1058
1059 --==========================================================================
1060 -- FUNCTION NAME:
1061 --
1062 -- Format_Monetary_Amount Public
1063 --
1064 -- DESCRIPTION:
1065 --
1066 -- Convert monetory amount with the format mask what is determined
1067 -- by VAT currency code and related profile values.
1068 --
1069 -- PARAMETERS:
1070 -- In: p_org_id Identifier of Operating Unit
1071 -- p_amount Monetary amount
1072 --
1073 -- Return: VARCHAR2
1074 --
1075 --
1076 -- DESIGN REFERENCES:
1077 --
1078 --
1079 -- CHANGE HISTORY:
1080 --
1081 -- 20-Sep-2006: Donghai Wang Creation
1082 --
1083 --=========================================================================
1084 FUNCTION Format_Monetary_Amount
1085 (p_org_id IN NUMBER
1086 ,p_amount IN NUMBER
1087 )
1088 RETURN VARCHAR2;
1089
1090 --=============================================================
1091 -- FUNCTION NAME:
1092 --
1093 -- get_invoice_type Public
1094 --
1095 -- DESCRIPTION:
1096 --
1097 -- This function is to get invoice type for a given customer_trx_id and -- tax registration number.
1098
1099 -- PARAMETERS:
1100
1101 -- In: p_org_id Business Unit identifier.
1102 -- In: p_customer_trx_id AR transaction identifier.
1103 -- In: p_fp_tax_registration_num fisrt party registration number
1104 -- Return: VARCHAR2
1105 --
1106 --CHANGE HISTORY:
1107 -- 28-Dec-2007: Subba Created.
1108 --=============================================================
1109
1110 FUNCTION get_invoice_type
1111 (p_org_id IN NUMBER
1112 ,p_customer_trx_id IN NUMBER
1113 ,p_fp_tax_registration_num IN NUMBER
1114 )
1115 RETURN VARCHAR2;
1116
1117
1118 END JMF_GTA_TRX_UTIL;
[Home] [Help]