[Home] [Help]
PACKAGE: APPS.AR_GTA_ARTRX_PROC
Source
1 PACKAGE AR_GTA_ARTRX_PROC AUTHID CURRENT_USER AS
2 --$Header: ARGRARTS.pls 120.0.12010000.3 2010/01/19 07:53:48 choli noship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, California, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| |
9 --| FILENAME : |
10 --| ARRARTS.pls |
11 --| |
12 --| DESCRIPTION: |
13 --| This package provide the functionality to retrieve |
14 --| transaction data from Oracle Receivable against the|
15 --| condition defined in Setup Form. |
16 --| |
17 --| |
18 --| HISTORY: |
19 --| 20-APR-2005: Jim Zheng |
20 --| 11-Oct-2005: Jim Zheng. Fix some bug after debug on DMFDV11i |
21 --| 12/04/2006 Jogen Hu Add function get_gta_number against bug |
22 --| 5144561 |
23 --| 18-Jun-2007: Yanping Wang Modify g_module_prefix to use small case |
24 --| of ar
25 --| 22/Jan/2009 Yao Zhang fix bug 7829039 ITEM NAME ON GTA INVOICE LINE IS NULL |
26 --+===========================================================================+
27
28
29
30 --==========================================================================
31 -- PROCEDURE NAME:
32 -- retrive_valid_AR_TRXs
33 --
34 -- DESCRIPTION:
35 -- This procedure is for invoices transfer concurrent implementation from Receivable to GTA
36 --
37 -- PARAMETERS:
38 -- In: P_ORG_ID NUMBER
42 -- OUT: errbuf varchar2
39 -- P_transfer_rule NUMBER
40 -- p_conc_parameters AR_GTA_TRX_UTIL.transferParas_rec_type
41 -- p_DEBUG VARCHAR2
43 -- retcode VARCHAR2
44
45 -- DESIGN REFERENCES:
46 -- GTA-TRANSFER-PROGRAM-TD.doc
47 --
48 -- CHANGE HISTORY:
49 -- 20-APR-2005: Jim Zheng Created.
50 --===========================================================================
51 PROCEDURE transfer_AR_to_GTA
52 ( errbuf OUT NOCOPY VARCHAR2
53 , retcode OUT NOCOPY VARCHAR2
54 , P_ORG_ID IN NUMBER
55 , P_TRANSFER_id IN NUMBER
56 , p_conc_parameters IN AR_GTA_TRX_UTIL.transferParas_rec_type
57 );
58
59 --==========================================================================
60 -- PROCEDURE NAME:
61 -- Generate_XML_output
62 --
63 -- DESCRIPTION:
64 -- This procedure generate XML string as concurrent output from temporary table
65 --
66 -- PARAMETERS:
67 -- In: P_ORG_ID NUMBER
68 -- p_transfer_id NUMBER
69 -- p_conc_parameters AR_GTA_TRX_UTIL.transferParas_rec_type
70 --
71 -- DESIGN REFERENCES:
72 -- GTA-TRANSFER-PROGRAM-TD.doc
73 --
74 -- CHANGE HISTORY:
75 -- 20-APR-2005: Jim Zheng Created.
76 --===========================================================================
77 PROCEDURE Generate_XML_output
78 (p_org_id IN NUMBER
79 , p_transfer_id IN NUMBER
80 , p_conc_parameters IN AR_GTA_TRX_UTIL.transferParas_rec_type
81 );
82
83
84 --==========================================================================
85 -- API NAME:
86 -- retrive_valid_AR_TRXs
87 --
88 -- DESCRIPTION:
89 -- this procedure returns the valid Receivable VAT transaction
90 --
91 -- PARAMETERS:
92 -- In: P_ORG_ID NUMBER
93 -- p_transfer_id VARCHAR2
94 -- p_conc_parameters AR_GTA_TRX_UTIL.transferParas_rec_type
95 -- OUT: x_GTA_TRX_Tbl AR_GTA_TRX_UTIL.TRX_TBL_TYPE
96
97 -- DESIGN REFERENCES:
98 -- GTA-TRANSFER-PROGRAM-TD.doc
99 --
100 -- CHANGE HISTORY:
101 -- 20-APR-2005: Jim Zheng Created.
102 --
103 --===========================================================================
104 PROCEDURE retrive_valid_AR_TRXs
105 ( P_ORG_ID IN NUMBER
106 , p_transfer_id IN NUMBER
107 , p_conc_parameters IN AR_GTA_TRX_UTIL.transferParas_rec_type
108 , x_GTA_TRX_Tbl OUT NOCOPY AR_GTA_TRX_UTIL.TRX_TBL_TYPE
109 );
110
111 --==========================================================================
112 -- PROCEDURE NAME:
113 -- Get_AR_SQL
114 --
115 -- DESCRIPTION:
116 -- This procedure returns the SQL for Receivable VAT transaction retrieval
117 --
118 -- PARAMETERS:
119 -- In: P_ORG_ID NUMBER
120 -- p_transfer_id VARCHAR2
121 -- p_conc_parameters AR_GTA_TRX_UTIL.transferParas_rec_type
122 -- OUT: x_query_sql VARCHAR2
123 -- x_trxtype_parameter AR_GTA_TRX_UTIL.Condition_para_tbl_type
124 -- x_flex_parameter AR_GTA_TRX_UTIL.Condition_para_tbl_type
125 -- x_other_parameter AR_GTA_TRX_UTIL.Condition_para_tbl_type
126 -- x_currency_code VARCHAR2
127
128
129 -- DESIGN REFERENCES:
130 -- GTA-TRANSFER-PROGRAM-TD.doc
131 --
132 -- CHANGE HISTORY:
133 -- 20-APR-2005: Jim Zheng Created.
134 --
135 --===========================================================================
136 PROCEDURE Get_AR_SQL
137 ( P_ORG_ID IN NUMBER
138 , p_transfer_id IN NUMBER
139 , p_conc_parameters IN AR_GTA_TRX_UTIL.transferParas_rec_type
140 , x_query_sql OUT NOCOPY VARCHAR2
141 , x_trxtype_parameter OUT NOCOPY AR_GTA_TRX_UTIL.Condition_para_tbl_type
142 , x_flex_parameter OUT NOCOPY AR_GTA_TRX_UTIL.Condition_para_tbl_type
143 , x_other_parameter OUT NOCOPY AR_GTA_TRX_UTIL.Condition_para_tbl_type
144 , x_currency_code OUT NOCOPY VARCHAR2
145 );
146
147 --==========================================================================
148 -- PROCEDURE NAME:
149 -- Get_AR_TrxType_Cond
150 --
151 -- DESCRIPTION:
152 -- This procedure returns the WHERE clause about transaction type
153 --
154 -- PARAMETERS:
155 -- In: P_ORG_ID NUMBER
156 -- p_transfer_id VARCHAR2
157
158 -- OUT: x_condition_sql Varchar2
159 -- x_query_parameter AR_GTA_TRX_UTIL.Condition_para_tbl_type
160
161
162 -- DESIGN REFERENCES:
163 -- GTA-TRANSFER-PROGRAM-TD.doc
164 --
165 -- CHANGE HISTORY:
166 -- 20-APR-2005: Jim Zheng Created.
167 --===========================================================================
168 PROCEDURE Get_AR_TrxType_Cond
169 ( p_ORG_ID IN NUMBER
170 , p_transfer_id IN NUMBER
171 , x_condition_sql OUT NOCOPY VARCHAR2
172 , x_query_parameter OUT NOCOPY AR_GTA_TRX_UTIL.Condition_para_tbl_type
176
173 );
174
175
177 --==========================================================================
178 -- PROCEDURE NAME:
179 -- Get_AR_FLEX_COND
180 --
181 -- DESCRIPTION:
182 -- This procedure returns the WHERE clause about flexfield condition
183 --
184 -- PARAMETERS:
185 -- In: P_ORG_ID NUMBER
186 -- p_transfer_id VARCHAR2
187
188 -- OUT: x_condition_sql Varchar2
189 -- x_query_parameter AR_GTA_TRX_UTIL.Condition_para_tbl_type
190
191
192 -- DESIGN REFERENCES:
193 -- GTA-TRANSFER-PROGRAM-TD.doc
194 --
195 -- CHANGE HISTORY:
196 -- 20-APR-2005: Jim Zheng Created.
197 --===========================================================================
198 PROCEDURE Get_AR_FLEX_COND
199 ( P_ORG_ID IN NUMBER
200 , p_transfer_id IN NUMBER
201 , x_condition_sql OUT NOCOPY VARCHAR2
202 , x_query_parameter OUT NOCOPY AR_GTA_TRX_UTIL.Condition_para_tbl_type
203 );
204
205
206 --==========================================================================
207 -- PROCEDURE NAME:
208 -- Get_Param_Cond
209 --
210 -- DESCRIPTION:
211 -- This procedure returns the WHERE clause about request parameter and fixed condition
212 --
213 -- PARAMETERS:
214 -- In: P_ORG_ID NUMBER
215 -- p_transfer_id VARCHAR2
216 -- p_conc_parameters AR_GTA_TRX_UTIL.transferParas_rec_type
217
218 -- OUT: x_condition_sql Varchar2
219 -- x_query_parameter AR_GTA_TRX_UTIL.Condition_para_tbl_type
220
221
222 -- DESIGN REFERENCES:
223 -- GTA-TRANSFER-PROGRAM-TD.doc
224 --
225 -- CHANGE HISTORY:
226 -- 20-APR-2005: Jim Zheng Created.
227 --
228 --===========================================================================
229 PROCEDURE Get_Param_Cond
230 ( P_ORG_ID IN NUMBER
231 , p_transfer_id IN NUMBER
232 , p_conc_parameters IN AR_GTA_TRX_UTIL.transferParas_rec_type
233 , x_condition_sql OUT NOCOPY VARCHAR2
234 , x_query_parameter OUT NOCOPY AR_GTA_TRX_UTIL.Condition_para_tbl_type
235 );
236
237 --==========================================================================
238 -- PROCEDURE NAME:
239 -- Get_AR_Currency_Cond
240 --
241 -- DESCRIPTION:
242 -- This procedure returns the WHERE clause about transaction Currency code
243 --
244 -- PARAMETERS:
245 -- In: P_ORG_ID NUMBER
246 -- p_transfer_id VARCHAR2
247
248 -- OUT: x_condition_sql Varchar2
249 -- x_currency_code VARCHAR2
250
251
252 -- DESIGN REFERENCES:
253 -- GTA-TRANSFER-PROGRAM-TD.doc
254 --
255 -- CHANGE HISTORY:
256 -- 17-AUG-2005: Jim Zheng Created.
257 --===========================================================================
258 PROCEDURE Get_AR_Currency_Cond
259 ( p_ORG_ID IN NUMBER
260 , p_transfer_id IN NUMBER
261 , x_condition_sql OUT NOCOPY VARCHAR2
262 , x_currency_code OUT NOCOPY VARCHAR2
263 );
264
265 --==========================================================================
266 -- PROCEDURE NAME:
267 -- Retrieve_AR_TRXs
268 --
269 -- DESCRIPTION:
270 -- This procedure retrieve Receivable VAT transaction
271 --
272 -- PARAMETERS:
273 -- In: P_ORG_ID NUMBER
274 -- p_transfer_id VARCHAR2
275 -- P_trxtype_para AR_GTA_TRX_UTIL.Condition_para_tbl_type
276 -- p_flex_para AR_GTA_TRX_UTIL.Condition_para_tbl_type
277 -- p_other_para AR_GTA_TRX_UTIL.Condition_para_tbl_type
278 -- p_currency_code IN VARCHAR2
279 -- OUT: x_GTA_Trx_Tbl AR_GTA_TRX_UTIL.TRX_TBL_TYPE
280
281
282 -- DESIGN REFERENCES:
283 -- GTA-TRANSFER-PROGRAM-TD.doc
284 --
285 -- CHANGE HISTORY:
286 -- 20-APR-2005: Jim Zheng Created.
287 --===========================================================================
288 PROCEDURE Retrieve_AR_TRXs
289 ( p_org_id IN NUMBER
290 , p_transfer_id IN NUMBER
291 , P_query_SQL IN VARCHAR2
292 , P_trxtype_query_para IN AR_GTA_TRX_UTIL.Condition_para_tbl_type
293 , p_flex_query_para IN AR_GTA_TRX_UTIL.Condition_para_tbl_type
294 , p_other_query_para IN AR_GTA_TRX_UTIL.Condition_para_tbl_type
295 , p_currency_code IN VARCHAR2
296 , x_GTA_TRX_TBL OUT NOCOPY AR_GTA_TRX_UTIL.TRX_TBL_TYPE
297 );
298
299 --==========================================================================
300 -- PROCEDURE NAME:
301 -- get_inv_item_model
302 --
303 -- DESCRIPTION:
304 -- This procedure get_item model by p_inventory_item_id and org_id
305 --
306 -- PARAMETERS: p_org_id IN NUMBER
307 -- p_inventory_item_id IN NUMBER
308 -- p_attribute_column IN VARCHAR2
309 -- x_attribute_value OUT NOCOPY VARCHAR2
310
314 -- CHANGE HISTORY:
311 -- DESIGN REFERENCES:
312 -- GTA-TRANSFER-PROGRAM-TD.doc
313 --
315 -- 20-APR-2005: Jim Zheng Created.
316 -- 22/Jan/2009 Yao Zhang modified for bug 7829039
317 --
318 --===========================================================================
319 PROCEDURE get_inv_item_model
320 (
321 p_item_master_org_id IN NUMBER
322 , p_inventory_item_id IN NUMBER
323 , p_attribute_column IN VARCHAR2
324 , x_attribute_value OUT NOCOPY VARCHAR2
325 );
326
327 --==========================================================================
328 -- PROCEDURE NAME:
329 -- get_ra_item_model
330 --
331 -- DESCRIPTION:
332 -- This procedure get_item model from ra line by ra_line_id and
333 -- attribute_column. This procedure replace the dynamic sql
334 --
335 -- PARAMETERS:
336 -- p_ra_line_id IN NUMBER
337 -- p_attribute_column IN VARCHAR2
338 -- x_attribute_value OUT NOCOPY VARCHAR2
339
340 -- DESIGN REFERENCES:
341 -- GTA-TRANSFER-PROGRAM-TD.doc
342 --
343 -- CHANGE HISTORY:
344 -- 20-APR-2005: Jim Zheng Created.
345 --
346 --===========================================================================
347 PROCEDURE get_ra_item_model
348 ( p_ra_line_id IN NUMBER
349 , p_attribute_column IN VARCHAR2
350 , x_attribute_value OUT NOCOPY VARCHAR2
351 );
352
353 --==========================================================================
354 -- FUNCTION NAME:
355 -- get_inventory_item_number
356 --
357 -- DESCRIPTION:
358 -- This procedure get item number by inventory_item_id
359 --
360 -- PARAMETERS:
361 -- p_inventory_item_id IN NUMBER
362 -- p_org_id IN NUMBER
363 -- x_inventory_item_code OUT NOCOPY VARCHAR2
364 --
365 -- DESIGN REFERENCES:
366 -- GTA-TRANSFER-PROGRAM-TD.doc
367 --
368 -- CHANGE HISTORY:
369 -- 20-APR-2005: Jim Zheng Created.
370 -- 22/Jan/2009 Yao Zhang modified for bug 7829039
371 --===========================================================================
372 PROCEDURE get_inventory_item_number
373 ( p_inventory_item_id IN NUMBER
374 , p_item_master_org_id IN NUMBER
375 , x_inventory_item_code OUT NOCOPY VARCHAR2
376 );
377
378 --==========================================================================
379 -- FUNCTION NAME:
380 -- get_uom_name
381 --
382 -- DESCRIPTION:
383 -- This procedure get item number by inventory_item_id
384 --
385 -- PARAMETERS:
386 -- p_uom_code IN VARCHAR2
387 -- x_uom_name OUT NOCOPY VARCHAR2
388 --
389 -- DESIGN REFERENCES:
390 -- GTA-TRANSFER-PROGRAM-TD.doc
391 --
392 -- CHANGE HISTORY:
393 -- 11-Oct2005: Jim Zheng Created.
394 --===========================================================================
395 PROCEDURE get_uom_name
396 (p_uom_code IN VARCHAR2
397 ,x_uom_name OUT NOCOPY VARCHAR2
398 );
399
400 --==========================================================================
401 -- PROCEDURE NAME:
402 -- Get_Taxinfo_From_Ebtax
403 --
404 -- DESCRIPTION:
405 -- This procedure get the tax info from ebtax by tax_line_id
406 --
407 -- PARAMETERS:
408 -- p_tax_line_id IN NUMBER
409 -- x_taxable_amt OUT NOCOPY NUMBER
410 -- x_taxable_amt_tax_curr OUT NOCOPY NUMBER
411 -- x_trx_line_quantity OUT NOCOPY NUMBER
412 -- x_unit_price OUT NOCOPY NUMBER
413 -- x_tax_rate OUT NOCOPY NUMBER
414 -- x_tax_amt_tax_curr OUT NOCOPY NUMBER
415 --
416 -- DESIGN REFERENCES:
417 -- GTA-TRANSFER-PROGRAM-TD.doc
418 --
419 -- CHANGE HISTORY:
420 -- 20-APR-2005: Jim Zheng Created.
421 --===========================================================================
422 /*
423 PROCEDURE Get_Taxinfo_From_Ebtax
424 (p_tax_line_id IN NUMBER
425 , x_taxable_amt OUT NOCOPY NUMBER
426 , x_taxable_amt_tax_curr OUT NOCOPY NUMBER
427 , x_trx_line_quantity OUT NOCOPY NUMBER
428 , x_unit_price OUT NOCOPY NUMBER
429 , x_tax_rate OUT NOCOPY NUMBER
430 , x_tax_amt_tax_curr OUT NOCOPY NUMBER
431 );
432 */
433
434 --==========================================================================
435 -- PROCEDURE NAME:
436 -- get_gta_number
437 --
438 -- DESCRIPTION:
439 -- This function get concated GTA number by a AR trx ID
440 --
441 -- PARAMETERS:
442 -- p_ar_trxId IN NUMBER
443 -- RETURN:
444 -- varchar2 concated GTA number
445 --
446 -- DESIGN REFERENCES:
447 -- GTA-TRANSFER-PROGRAM-TD.doc
448 --
449 -- CHANGE HISTORY:
450 -- 12-APR-2006: Jogen Hu Created.
451 --===========================================================================
452 FUNCTION get_gta_number(p_ar_trxId IN NUMBER)
453 RETURN VARCHAR2;
454
455 --============================================================================
456
457 -- PROCEDURE NAME:
458 -- Get_Invoice_Type
459 --
460 -- DESCRIPTION:
461 -- This procedure returns the WHERE clause about Invoice Type using the
462 -- Invoice Type,Transaction Type mapping relationship defined in GTA
463 -- System Option.
464 -- PARAMETERS:
465 -- In: P_ORG_ID NUMBER
466 -- p_transfer_id VARCHAR2
467
468 -- OUT: x_condition_sql Varchar2
469 -- CHANGE HISTORY:
470 -- 28-Dec-2007: Subba Created.
471 -- ===========================================================================
472
473 PROCEDURE Get_Invoice_Type
474 ( p_ORG_ID IN NUMBER
475 , p_transfer_id IN NUMBER
476 , x_condition_sql OUT NOCOPY VARCHAR2
477 );
478
479
480 G_MODULE_PREFIX VARCHAR2(100) := 'ar.plsql.AR_GTA_ARTRX_PROC';
481
482 END AR_GTA_ARTRX_PROC;