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