1 PACKAGE "AR_GTA_TXT_OPERATOR_PROC" AUTHID CURRENT_USER AS
2 ----$Header: ARGRIETS.pls 120.1.12020000.2 2012/11/22 02:45:57 szhi ship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, California, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| |
9 --| FILENAME : |
10 --| ARRIETS.pls |
11 --| |
12 --| DESCRIPTION: |
13 --| This package consists of server procedures, which are used to |
14 --| export customers, items and invoice to flat files respectively, |
15 --| also there is a procedure to import data from GT through flat |
16 --| file |
17 --| |
18 --| |
19 --| HISTORY: |
20 --| 05/12/2005 Jogen Created |
21 --| 05/17/2005 Jim Zheng Add procedure Export_Customers |
22 --| 05/17/2005 Donghai Wang Add procedure Export_Items |
23 --| 08/25/2005 Jogen Update invoice_import |
24 --| change Clear_Imp_Temp_Table to public |
25 --| 09/28/2005 Jogen Update invoice_import due to TCA |
26 --| change |
27 --| 09/29/2005 Jim.Zheng Update Customer Export, give up |
28 --| the tax_payer_id export |
29 --| |
30 --| 06/04/2006 Donghai Wang Modify record type g_item_rec to |
31 --| remove tax_rate |
32 --| 10/12/2008 Lv Xiao Modified for bug#7626503 |
33 --| 15/11/2012 Sabrina Modified for ER#14830536 |
34 --+===========================================================================+
35 TYPE COLUMN_VALUES IS TABLE OF VARCHAR2(200);
36 TYPE g_noreference_tbl IS TABLE OF mtl_system_items_b_kfv.concatenated_segments%TYPE
37 INDEX BY BINARY_INTEGER;
38
39 TYPE g_item_rec IS RECORD(item_number VARCHAR2(4000)
40 ,item_name VARCHAR2(500)
41 ,tax_name VARCHAR2(240)
42 ,item_model VARCHAR2(240)
43 ,uom VARCHAR2(25)
44 );
45
46 TYPE g_item_tbl IS TABLE OF g_item_rec
47 INDEX BY BINARY_INTEGER;
48
49 --add by Lv Xiao for bug#7626503 on 10-Dec-2008, begin
50 ---------------------------------------------------------
51 --this cursor is to keep all the duplicated description record
52 TYPE draft_dup_record IS RECORD
53 (ra_trx_id AR_GTA_TRX_HEADERS.ra_trx_id%TYPE,
54 description AR_GTA_TRX_HEADERS.description%TYPE,
55 org_id AR_GTA_TRX_HEADERS.org_id%TYPE,
56 gta_trx_number AR_GTA_TRX_HEADERS.gta_trx_number%TYPE
57 ) ;
58 TYPE crmemo_dup_cur_TYPE IS REF CURSOR RETURN draft_dup_record;
59
60 --this record is to keep all the dupliacted desription record
61 --that fatch from the cursor
62
63 TYPE dup_record IS RECORD
64 (ra_trx_id AR_GTA_TRX_HEADERS.ra_trx_id%TYPE,
65 description AR_GTA_TRX_HEADERS.description%TYPE,
66 org_id AR_GTA_TRX_HEADERS.org_id%TYPE,
67 gta_trx_number AR_GTA_TRX_HEADERS.gta_trx_number%TYPE
68 ) ;
69 TYPE dup_record_tbl IS TABLE OF dup_record;
70 ---------------------------------------------------------
71 --add by Lv Xiao for bug#7626503 on 10-Dec-2008, end
72
73 TYPE vat_tbl_type IS TABLE OF AR_GTA_TRX_UTIL.TRX_header_rec_TYPE INDEX BY AR_Gta_Trx_Headers_All.Gta_Trx_Number%TYPE; --Add by Sabrina for ER#14830536
74
75 --==========================================================================
76 -- PROCEDURE NAME:
77 --
78 -- Put_Line Public
79 --
80 -- DESCRIPTION:
81 --
82 -- This procedure write data to log file.
83 --
84 -- PARAMETERS:
85 -- In: p_str VARCHAR2
86 --
87 -- Out:
88 --
89 -- DESIGN REFERENCES:
90 -- GTA-Txt-Interface-TD.doc
91 --
92 -- CHANGE HISTORY:
93 -- 05/12/05 Jogen Hu Created
94 --===========================================================================
95 PROCEDURE Put_Line
96 ( p_str IN VARCHAR2
97 );
98
99 --==========================================================================
100 -- PROCEDURE NAME:
101 --
102 -- Put_Log Public
103 --
104 -- DESCRIPTION:
105 --
106 -- This procedure write data to log file.
107 --
108 -- PARAMETERS:
109 -- In: p_str VARCHAR2
110 --
111 -- Out:
112 --
113 -- DESIGN REFERENCES:
114 -- GTA-Txt-Interface-TD.doc
115 --
116 -- CHANGE HISTORY:
117 -- 05/12/05 Jogen Hu Created
118 --===========================================================================
119 PROCEDURE Put_Log
120 ( p_str IN VARCHAR2
121 );
122
123
124 --==========================================================================
125 -- PROCEDURE NAME:
126 --
127 -- Import_Invoices Public
128 --
129 -- DESCRIPTION:
130 --
131 -- This procedure import VAT invoices from flat file to GTA
132 -- Because SQL*Loader will import flat file to temporary table
133 -- AR_GTA_TRXIMP_TMP and GTA_TRX_NUMBER is a unique column
134 -- in GTA, so no parameter is needed here
135 --
136 -- PARAMETERS:
137 -- In: None
138 -- Out: None
139 --
140 -- DESIGN REFERENCES:
141 -- GTA-Txt-Interface-TD.doc
142 --
143 -- CHANGE HISTORY:
144 -- 05/12/05 Jogen Hu Created
145 --===========================================================================
146 PROCEDURE Import_Invoices;
147
148 --==========================================================================
149 -- PROCEDURE NAME:
150 --
151 -- Clear_Imp_Temp_Table Public
152 --
153 -- DESCRIPTION:
154 --
155 -- This procedure clear the data imported from flat file
156 -- in temporary table
157 --
158 -- PARAMETERS:
159 -- In: None
160 -- Out: None
161 --
162 -- DESIGN REFERENCES:
163 -- GTA-Txt-Interface-TD.doc
164 --
165 -- CHANGE HISTORY:
166 -- 05/12/05 Jogen Hu Created
167 --===========================================================================
168 PROCEDURE Clear_Imp_Temp_Table;
169
170 --==========================================================================
171 -- PROCEDURE NAME:
172 --
173 -- Export_Invoices_From_Conc Public
174 --
175 -- DESCRIPTION:
176 --
177 -- This procedure will export GTA invoices to the flat file
178 -- Its output will be printed on concurrent output and will
179 -- be save as flat file by users.
180 --
181 -- PARAMETERS:
182 -- In: p_org_id Identifier of operation unit
183 -- p_regeneration New batch('N') or regeneration('Y')
184 -- p_FP_Tax_reg_Number The first party tax registration number
185 -- p_transfer_rule_id GTA transfer rule header ID
186 -- p_batch_number Export batch number
187 -- p_customer_id_from_number AccountID against customer Number
188 -- p_customer_id_from_name AccountID against customer Name
189 -- p_cust_id_from_taxpayer AccountID against taxpayerid
190 -- p_ar_trx_num_from AR transaction Number
191 -- p_ar_trx_num_to AR transaction Number
192 -- p_ar_trx_date_from AR transaction date
193 -- p_ar_trx_date_to AR transaction date
194 -- p_ar_trx_gl_date_from AR transaction GL date
195 -- p_ar_trx_gl_date_to AR transaction GL date
196 -- p_ar_trx_batch_from AR transaction batch name
197 -- p_ar_trx_batch_to AR transaction batch name
198 -- p_trx_class AR transaction class: INV, CM, DM
199 -- P_Batch_ID GTA batch number
200 -- P_Invoice_Type_ID Invoice Type
201 --
202 -- Out:
203 --
204 -- DESIGN REFERENCES:
205 -- GTA-Txt-Interface-TD.doc
206 --
207 -- CHANGE HISTORY:
208 -- 05/12/05 Jogen Hu Created
209 -- 09/29/05 Jogen Hu add parameter p_FP_Tax_reg_Number
210 -- due to TCA change
211 -- 01/02/08 Subba add parameter p_Invoice_Type_ID
212 --===========================================================================
213 PROCEDURE Export_Invoices_From_Conc
214 ( p_org_id IN NUMBER
215 , p_regeneration IN VARCHAR2
216 , p_FP_Tax_reg_Number IN VARCHAR2
217 , p_transfer_rule_id IN NUMBER
218 , p_batch_number IN VARCHAR2
219 , p_customer_id_from_number IN NUMBER
220 , p_customer_id_from_name IN NUMBER
221 , p_cust_id_from_taxpayer IN NUMBER
222 , p_ar_trx_num_from IN VARCHAR2
223 , p_ar_trx_num_to IN VARCHAR2
224 , p_ar_trx_date_from IN DATE
225 , p_ar_trx_date_to IN DATE
226 , p_ar_trx_gl_date_from IN DATE
227 , p_ar_trx_gl_date_to IN DATE
228 , p_ar_trx_batch_from IN VARCHAR2
229 , p_ar_trx_batch_to IN VARCHAR2
230 , p_trx_class IN VARCHAR2
231 , p_batch_id IN VARCHAR2
232 , p_invoice_type_id IN VARCHAR2
233 );
234
235 --==========================================================================
236 -- PROCEDURE NAME:
237 --
238 -- Export_Invoices_From_Workbench Public
239 --
240 -- DESCRIPTION:
241 --
242 -- This procedure export VAT invoices from GTA to flat file
243 -- and is invoked in workbench.
244 --
245 -- PARAMETERS:
246 -- In: p_org_id Identifier of operating unit
247 -- p_generator_id Indicate which need export(choose in workbench)
248 -- P_Batch_ID export batch number
249 --
250 -- Out:
251 --
252 -- DESIGN REFERENCES:
253 -- GTA-Txt-Interface-TD.docexport_invoices_from_workbench
254 --
255 -- CHANGE HISTORY:
256 --
257 -- 05/12/05 Jogen Hu Created
258 --===========================================================================
259 PROCEDURE Export_Invoices_From_Workbench
260 ( p_org_id IN NUMBER
261 , p_generator_id IN NUMBER
262 , p_batch_id IN VARCHAR2
263 );
264
265 --==========================================================================
266 -- FUNCTION NAME:
267 --
268 -- Get_Trx_Class Public
269 --
270 -- DESCRIPTION:
271 --
272 -- This procedure get transaction class
273 --
274 -- PARAMETERS:
275 -- In: p_GTA_org_id GTA transaction org id
276 -- In: p_GTA_trx_id GTA transaction id
277 --
278 -- Out:
279 -- Return: VARCHAR2;
280 --
281 -- DESIGN REFERENCES:
282 -- GTA-Txt-Interface-TD.doc
283 --
284 -- CHANGE HISTORY:
285 -- 09/12/08 Lv Xiao Created
286 --===========================================================================
287 FUNCTION Get_Trx_Class
288 ( p_gta_org_id IN NUMBER
289 , p_gta_trx_id IN NUMBER
290 )RETURN VARCHAR2;
291
292
293 --==========================================================================
294 -- FUNCTION NAME:
295 --
296 -- Check_Header Public
297 --
298 -- DESCRIPTION:
299 --
300 -- This procedure check whether the columns of export data
301 -- exceeding Golden Tax required length.
302 --
303 -- PARAMETERS:
304 -- In: p_gta_trx_header GTA transaction header
305 -- Out:
306 -- Return: PLS_INTEGER
307 --
308 -- DESIGN REFERENCES:
309 -- GTA-Txt-Interface-TD.doc
310 --
311 -- CHANGE HISTORY:
312 -- 05/12/05 Jogen Hu Created
313 --===========================================================================
314 FUNCTION Check_Header
315 ( p_gta_trx_header IN AR_GTA_TRX_UTIL.TRX_HEADER_REC_TYPE
316 )RETURN PLS_INTEGER;
317
318 --==========================================================================
319 -- FUNCTION NAME:
320 --
321 -- Check_Line_Length Public
322 --
323 -- DESCRIPTION:
324 --
325 -- This procedure check whether the columns of export data
326 -- exceeding Golden Tax required length
327 --
328 -- PARAMETERS:
329 -- In: p_gta_trx_line GTA transaction line record
330 --
331 -- Out:
332 -- Return: BOOLEAN;
333 --
334 -- DESIGN REFERENCES:
335 -- GTA-Txt-Interface-TD.doc
336 --
337 -- CHANGE HISTORY:
338 -- 05/12/05 Jogen Hu Created
339 --===========================================================================
340 FUNCTION Check_Line_Length
341 ( p_gta_trx_line IN AR_GTA_TRX_UTIL.TRX_LINE_REC_TYPE
342 )RETURN BOOLEAN;
343
344 --==========================================================================
345 -- PROCEDURE NAME:
346 --
347 -- Export_Customers Public
348 --
349 -- DESCRIPTION:
350 --
351 -- This procedure export customers information from GTA to flat file
352 --
353 -- PARAMETERS:
354 -- In: p_org_id Identifier of operating unit
355 -- p_customer_num_from Customer number low range
356 -- p_customer_num_to Customer number high range
357 -- p_customer_name_from Customer name low range
358 -- p_customer_name_to Customer name high range
359 -- p_taxpayee_id Identifier of taxpayer
360 -- p_creation_date_from Creation date low range
361 -- p_creation_date_to Creation date high range
362 --
363 -- OUt:
364 --
365 -- DESIGN REFERENCES:
366 -- GTA-Txt-Interface-TD.doc
367 --
368 -- CHANGE HISTORY:
369 --
370 -- 06/05/05 Jim Zheng Created
371 -- 30/09/05 Jim Zheng updated delete the export of tax_payer_id
372 --
373 --===========================================================================
374 PROCEDURE Export_Customers
375 ( p_org_id IN NUMBER
376 , p_customer_num_from IN VARCHAR2
377 , p_customer_num_to IN VARCHAR2
378 , p_customer_name_from IN VARCHAR2
379 , p_customer_name_to IN VARCHAR2
380 --, p_taxpayee_id IN VARCHAR2
381 , p_creation_date_from IN DATE
382 , p_creation_date_to IN DATE
383 );
384
385 --==========================================================================
386 -- PROCEDURE NAME:
387 --
388 -- Export_Items Public
389 --
390 -- DESCRIPTION:
391 --
392 -- This procedure is to export item information from
393 -- inventory to a flat file with special format, the flat
394 -- will be used as import file to import items into GT system
395 --
396 -- PARAMETERS:
397 -- In: p_org_id Identifier for Operating Unit
398 -- p_master_org_id Identifier for Master Organization
399 -- of inventory organization
400 -- p_item_num_from High range of item number
401 -- p_item_num_to Low range of item number
402 -- p_category_set_id Identifier of item category set
403 -- p_category_structure_id Identifier for structure of item
404 -- category
405 -- p_item_category_from High range of item category
406 -- p_item_category_to Low range of item category
407 -- p_item_name_source Iten name source, alternative
408 -- value is 'MASTER_ITEM' or
409 -- 'LATEST_ITEM_CROSS_REFERENCE',
410 -- this parameter is to decide
411 -- where item name is got from
412 -- p_cross_reference_type Cross reference type of item
413 -- p_item_status Item status
414 -- p_creation_date_from High range of item creation date
415 -- p_creation_date_to Low range of item creation date
416 --
417 -- Out:
418 --
419 --
420 -- DESIGN REFERENCES:
421 -- GTA-Txt-Interface-TD.doc
422 --
423 -- CHANGE HISTORY:
424 --
425 -- 17-MAY-2005 Donghai Wang Created
426 --
427 --===========================================================================
428 PROCEDURE Export_Items
429 ( p_org_id IN NUMBER
430 , p_master_org_id IN NUMBER
431 , p_item_num_from IN VARCHAR2
432 , p_item_num_to IN VARCHAR2
433 , p_category_set_id IN NUMBER
434 , p_category_structure_id IN NUMBER
435 , p_item_category_from IN VARCHAR2
436 , p_item_category_to IN VARCHAR2
437 , p_item_name_source IN VARCHAR2
438 , p_cross_reference_type IN VARCHAR2
439 , p_item_status IN VARCHAR2
440 , p_creation_date_from IN VARCHAR2
441 , p_creation_date_to IN VARCHAR2
442 );
443
444 END AR_GTA_TXT_OPERATOR_PROC;
445
446
[Home] [Help]