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