[Home] [Help]
PACKAGE BODY: APPS.JMF_GTA_SPLIT_TRX_PROC
Source
1 PACKAGE BODY JMF_GTA_SPLIT_TRX_PROC AS
2 --$Header: JMFRXTRB.pls 120.16.12010000.5 2009/01/23 06:04:24 yaozhan ship $
3
4 --+===========================================================================
5 --| Copyright (c) 2002 Oracle Corporation
6 --| Redwood Shores, California, USA
7 --| All rights reserved.
8 --+===========================================================================
9 --|
10 --| FILENAME :
11 --| JMFRXTRB.pls
12 --|
13 --| DESCRIPTION:
14 --| This procedure split GTA transactions against setup
15 --| of max amount,sales list flag and lines number
16 --|
17 --|
18 --|
19 --|
20 --| HISTORY:
21 --| Created : 10-MAY-2005 : Jim.Zheng
22 --| 30-sep-2005: Jim zheng modify because registration
23 --| issue.
24 --| 10-Oct-2005: Jim Zheng add some log for debug .
25 --|
26 --| 11-Oct-2005: Jim Zheng fix bug after debug on DMFDV11i
27 --|
28 --| 13-Oct-2005: Jim Zheng fix get trx type bug
29 --|
30 --| 01-Dec-2005: Jim Zheng Fix a init error in judge_cm_limit
31 --| 02-Dec-2005: Jim Zheng Fix a gta_trx_number bug, procedure Split_transactions
32 --|
33 --| 06-Dec-2005: Jim Zheng Fix a gta_trx_number bug, difference of l_gta_trx and l_gta_trx_new,
34 --| procedure Split_transactions
35 --| 06-Dec-2005: Jim Zheng Change the tax rate -- 0.17 to l_GTA_Trx_line.tax_rate
36 --| when split a trx line
37 --| 08-Dec-2005: Jim Zheng Add output log for debug
38 --| 14-Dec-2005: Jim Zheng Add gta_inv_number into temp table jmf_gta_transfer_temp
39 --| 15-Dec-2005: Jim Zheng Add transaction id for successful transaction in add_succ_to_temp
40 --| 17-Feb-2006: Jogen Hu Replace fnd_file.put_line(fnd_file.log by log procedure
41 --| 25-Apr-2006: Jogen Hu Modify Split_Transactions for bug 5168852
42 --| 12-Jun-2006: Shujuan Yan Modify Split_trx_by_taxreg_num for bug 5258345
43 --| 12-Jun-2006: Shujuan Yan Process_before_split for bug 5168900
44 --| 28-Dec-2007 Subba Modified for R12.1
45 --| 16-Dec-2008 Yao Zhang fix bug 7644235, CreditMemo should not be transfered or splited when
46 --| exceed the limition of max amount or max lines.
47 --| 15-Jan-2009 Yao Zhang fix bug 7709947 RECEIVABLES TRANSFER TO GOLDEN TAX ADAPTOR ENDS IN ERROR
48 --| 23-Jan-2009 Yao Zhang fix bug 7758496 CreditMemo whose line num exceeds max line number limitation
49 --| should be transfered when sales list is enabled
50 --|
51 --+===========================================================================
52
53 --=============================================================================
54 -- PROCEDURE NAME:
55 -- log
56 -- TYPE:
57 -- private
58 --
59 -- DESCRIPTION :
60 -- This procedure log message
61 -- PARAMETERS :
62 -- p_level IN VARCHAR2
63 -- p_module IN VARCHAR2
64 -- p_message IN VARCHAR2
65 --
66 -- HISTORY:
67 -- 10-MAY-2005 : Jim.Zheng Create
68 --=============================================================================
69 PROCEDURE log
70 (p_level IN VARCHAR2
71 ,p_module IN VARCHAR2
72 ,p_message IN VARCHAR2)
73 IS
74 BEGIN
75
76 IF(p_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
77 THEN
78 fnd_log.STRING(LOG_LEVEL => p_level
79 ,MODULE => p_module
80 ,MESSAGE => p_message
81 );
82 END IF;
83
84 END;
85
86 --=============================================================================
87 -- PROCEDURE NAME:
88 -- split_Transactions
89 -- TYPE:
90 -- PUBLIC
91 --
92 -- DESCRIPTION :
93 -- This procedure returns slpited tracsation by max_amount, max_lines
94 -- PARAMETERS :
95 -- P_ORG_ID IN org_id
96 -- p_transfer_id IN the transfer rule
97 -- P_GTA_TRX_Tbl IN a trx nested table as input
98 -- x_GTA_TRX_Tbl OUT a trx nestedt tabel as output
99 --
100 -- HISTORY:
101 -- 10-MAY-2005 : Jim.Zheng Create
102 --=============================================================================
103 PROCEDURE Split_Transactions
104 ( P_ORG_ID IN NUMBER
105 , p_transfer_id IN NUMBER
106 , P_GTA_TRX_Tbl IN JMF_GTA_TRX_UTIL.TRX_TBL_TYPE
107 , x_GTA_TRX_Tbl OUT NOCOPY JMF_GTA_TRX_UTIL.TRX_TBL_TYPE
108 )
109 IS
110 l_procedure_name VARCHAR2(30):='Split_Transactions';
111
112 --parameter for splite max_lines and max_amount;
113 l_sum_of_amount NUMBER := 0;
114 l_lines_number NUMBER;
115 l_processing_row NUMBER; -- a process number when split the trx ( this split action is loop by line )
116 l_accumulated_amount NUMBER; -- amount of the trx
117 l_amount NUMBER;
118
119 --get from option table,this four variable is the split codition
120 l_max_amount NUMBER;
121 l_max_line NUMBER;
122 l_sales_list_flag jmf_gta_rule_headers_all.Sales_List_Flag%TYPE;
123 l_split_flag jmf_gta_system_parameters.Trx_Line_Split_Flag%TYPE;
124
125 l_GTA_TRX JMF_GTA_TRX_UTIL.trx_rec_type; -- trx get from input parameter
126 l_gta_trx_succesed JMF_GTA_TRX_UTIL.trx_rec_type; -- trx for insert into temp table for report
127 l_gta_trx_init JMF_GTA_TRX_UTIL.trx_rec_type; -- trx for init
128
129 -- use when split a trx line
130 l_GTA_Trx_line_old JMF_GTA_TRX_UTIL.trx_line_rec_type;
131 l_GTA_TRX_line_new JMF_GTA_TRX_UTIL.trx_line_rec_type;
132 l_gta_trx_line_init JMF_GTA_TRX_UTIL.trx_line_rec_type;
133
134 -- use when split a new trx
135 l_GTA_Trx_new JMF_GTA_TRX_UTIL.trx_rec_type;
136 l_gta_trx_new_succ Jmf_Gta_Trx_Util.trx_rec_type;
137 l_trx_lines_new JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE := JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
138 l_trx_header_new JMF_GTA_TRX_UTIL.TRX_header_rec_TYPE;
139
140
141 -- use by split trx by tax rate and fp registration number
142 l_gta_rate_trx_tbl JMF_GTA_TRX_UTIL.TRX_Tbl_TYPE := JMF_GTA_TRX_UTIL.TRX_Tbl_TYPE();
143 l_gta_taxreg_trx_tbl JMF_GTA_TRX_UTIL.TRX_Tbl_TYPE := JMF_GTA_TRX_UTIL.TRX_Tbl_TYPE();
144
145 l_quantity_limit NUMBER;
146
147 l_trx_lines JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE := JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
148
149 l_trx_index NUMBER; -- loop index of l_gta_trx
150 l_trx_rate_index NUMBER; -- loop index of l_gta_rate_trx
151 l_trx_line_index NUMBER; -- loop index of trx lines
152 l_result BOOLEAN;
153
154 l_trx_type ra_cust_trx_types_all.type%TYPE;
155 l_fp_reg_num jmf_gta_trx_headers_all.fp_tax_registration_number%TYPE;
156
157 l_trx_group_number NUMBER;
158
159 l_functional_price jmf_gta_trx_lines_all.unit_price%TYPE; --25-Apr-2006: Jogen Hu bug 5168852
160
161 BEGIN
162
163 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
164 THEN
165 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
166 , G_MODULE_PREFIX || l_procedure_name
167 ,'Begin Procedure. ');
168 END IF;
169
170 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
171 THEN
172 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'Begin Split_trx');
173
174
175 END IF;
176
177
178 -- init all nested table
179 l_trx_lines_new := JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
180 l_trx_lines := JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
181 l_gta_trx_new.trx_lines := JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
182 l_gta_rate_trx_tbl := JMF_GTA_TRX_UTIL.TRX_Tbl_TYPE();
183 x_GTA_TRX_Tbl := JMF_GTA_TRX_UTIL.TRX_TBL_TYPE();
184
185 --begin select max amount, max number of lines and sales list flag
186 BEGIN
187 SELECT
188 TRX_LINE_SPLIT_FLAG
189 INTO
190 l_split_flag
191 FROM
192 JMF_GTA_SYSTEM_PARAMETERS_all
193 WHERE org_id = p_org_id;
194
195 -- begin log
196 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
197 THEN
198 --fnd_file.put_line(fnd_file.LOG, 'l_split_flag:'||l_split_flag);
199 log(FND_LOG.LEVEL_PROCEDURE
200 , G_MODULE_PREFIX || l_procedure_name
201 , 'l_split_flag:'||l_split_flag);
202 END IF;
203 -- end log
204
205 EXCEPTION
206 -- no data found , raise a data error
207 WHEN no_data_found THEN
208 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
209 THEN
210 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
211 , G_MODULE_PREFIX || l_procedure_name
212 , 'no data found');
213
214 --JMF_GTA_SYS_CONFIG_MISSING
215 fnd_message.set_name('JMF', 'JMF_GTA_SYS_CONFIG_MISSING');
216 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
217 , G_MODULE_PREFIX || l_procedure_name
218 , fnd_message.get());
219
220 END IF;
221 RAISE;
222 RETURN;
223 END;
224
225 BEGIN
226 SELECT
227 sales_list_flag
228 INTO
229 l_sales_list_flag
230 FROM
231 JMF_GTA_RULE_HEADERS_All
232 WHERE org_id = p_org_id
233 AND rule_header_id = p_transfer_id;
234 EXCEPTION
235 -- no data found , raise a data error
236 WHEN no_data_found THEN
237 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
238 THEN
239 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
240 , G_MODULE_PREFIX || l_procedure_name
241 , 'no data found');
242
243 --JMF_GTA_SYS_CONFIG_MISSING
244 fnd_message.set_name('JMF', 'JMF_GTA_SYS_CONFIG_MISSING');
245 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
246 , G_MODULE_PREFIX || l_procedure_name
247 , fnd_message.get());
248
249 END IF;
250 RAISE;
251 RETURN;
252 END;
253
254
255 -- begin log
256 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
257 THEN
258 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'l_sales_list_flag:'||l_sales_list_flag);
259 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'begin split_trx_loop......');
260 END IF;
261 -- end log
262
263
264
265 -- begin split trx
266 l_trx_index := P_GTA_TRX_Tbl.FIRST;
267
268 -- begin log
269 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
270 THEN
271 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'l_trx_index......'||l_trx_index);
272 END IF;
273 -- end log
274
275
276 WHILE l_trx_index IS NOT NULL
277 LOOP
278
279 l_gta_trx_succesed := P_GTA_TRX_Tbl(l_trx_index);
280
281
282 l_GTA_TRx := P_GTA_TRX_tbl(l_trx_index);
283
284 -- init trx group number
285 l_trx_group_number := 1;
286
287 -- begin split by tax registration number
288 split_trx_by_taxreg_number(p_gta_trx => l_GTA_TRX
289 , x_TRX_Tbl => l_gta_taxreg_trx_tbl);
290 -- end split by tax registration number
291
292 -- begin log
293 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
294 THEN
295 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'End Split_trx_by_taxreg_number:'||l_gta_taxreg_trx_tbl.COUNT);
296 END IF;
297 -- end log
298
299 -- begin split by tax rate
300 split_trx_by_rate(p_gta_tbl => l_gta_taxreg_trx_tbl
301 ,x_trx_tbl => l_gta_rate_trx_tbl
302 );
303 -- end split by tax rate
304
305
306 -- begin log
307 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
308 THEN
309 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'End split_trx_by_rate:'||l_gta_rate_trx_tbl.COUNT);
310 END IF;
311 -- end log
312
313 -- loop by a new trx nested table which split by rate
314 l_trx_rate_index := l_gta_rate_trx_tbl.FIRST;
315 WHILE l_trx_rate_index IS NOT NULL
316 LOOP
317 -- use l_gta_trx again
318 -- now we get a new trx of single rate
319 l_GTA_TRX := l_gta_rate_trx_tbl(l_trx_rate_index);
320
321 -- get trx type
322 get_trx_type(p_org_id => p_org_id
323 , p_gta_trx => l_GTA_TRX
324 , x_trx_type => l_trx_type);
325
326
327 -- begin log
328 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
329 THEN
330 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'l_trx_rate_index:'||l_trx_rate_index);
331 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'End get_trx_type:'||l_trx_type);
332 END IF;
333 -- end log
334
335 -- IS CM ?\
336 /* IF l_trx_type = 'CM'
337 THEN
338 -- judge the CM can fit the max amount and the max line counts
339 judge_cm_limit(p_gta_trx => l_gta_trx
340 , p_org_id => p_org_id
341 , x_result => l_result);
342 IF l_result
343 THEN
344 -- add this l_gta_trx into the result table
345 l_gta_trx.trx_header.group_number := l_trx_group_number;
346 l_trx_group_number := l_trx_group_number + 1;
347 process_before_split( x_gta_trx_rec => l_GTA_TRX
348 );
349 x_GTA_TRX_Tbl.EXTEND;
350 x_GTA_TRX_Tbl(x_GTA_TRX_Tbl.COUNT) := l_GTA_Trx;
351 ELSE
352 NULL;
353 -- throw exception
354 END IF;
355
356 ELSE --l_trx_type = 'CM'*/
357 -- get max_amount and max_line_count
358 get_max_amount_line(p_gta_trx => l_gta_trx
359 , p_org_id => P_ORG_ID
360 , x_max_amount => l_max_amount
361 , x_max_line => l_max_line
362 );
363
364 -- begin log
365 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
366 THEN
367 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'get_max_amount_line');
368 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'l_max_amount:'||l_max_amount);
369 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'l_max_line:'||l_max_line);
370 END IF;
371 -- end log
372
373 -- get lines number and amount
374 l_lines_number := l_GTA_TRX.trx_lines.COUNT;
375
376 l_trx_line_index := l_gta_trx.trx_lines.FIRST;
377 WHILE l_trx_line_index IS NOT NULL
378 LOOP
379 l_sum_of_amount := l_sum_of_amount + l_GTA_TRX.trx_lines(l_trx_line_index).amount;
380 l_trx_line_index := l_gta_trx.trx_lines.NEXT(l_trx_line_index);
381 END LOOP;
382
383
387
384 -- if the trx is regular and don't need split, push it into rusult
385 IF l_sum_of_amount<l_max_amount AND (l_lines_number<l_max_line OR l_sales_list_flag='Y' )
386 THEN
388
389 -- begin log
390 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
391 THEN
392 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'l_sum_of_amount<l_max_amount AND (l_lines_number<l_max_line OR l_sales_list_flag=''Y'' )');
393 END IF;
394 -- end log
395
396 -- insert trx into result table
397 l_gta_trx.trx_header.group_number := l_trx_group_number;
398 l_trx_group_number := l_trx_group_number + 1;
399 process_before_split( x_gta_trx_rec => l_GTA_TRX
400 );
401 x_GTA_TRX_Tbl.EXTEND;
402 x_GTA_TRX_Tbl(x_GTA_TRX_Tbl.COUNT) := l_GTA_Trx;
403 ELSE /*l_sum_of_amount<l_max_amount AND (l_lines_number<l_max_line OR l_sales_list_flag='Y' )*/
404
405 -- init the l_processing_row and l_accumulated_amount
406 l_processing_row:=1;
407 l_accumulated_amount:=0;
408
409 WHILE (l_processing_row<=l_GTA_TRX.trx_lines.COUNT)
410 LOOP
411 -- init l_gta_trx_line_old
412 l_gta_trx_line_old := l_gta_trx_line_init;
413 l_gta_trx_new := l_gta_trx_init;
414 l_gta_trx_new_succ := l_gta_trx_init;
415 l_trx_lines_new := JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
416
417 l_GTA_TRX_line_old :=l_GTA_TRX.trx_lines(l_processing_row);
418 l_amount:=l_GTA_TRX_line_old.amount;
419
420 -- 25-Apr-2006: Jogen Hu bug 5168852
421 l_functional_price:=l_GTA_TRX_line_old.amount/l_GTA_TRX_line_old.quantity;
422 -- begin log
423 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
424 THEN
425 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name
426 , 'l_functional_price:'||l_functional_price);
427 END IF;
428 -- end log
429 -- 25-Apr-2006: Jogen Hu bug 5168852
430
431 l_accumulated_amount := l_accumulated_amount + nvl(l_amount, 0);
432
433 IF l_accumulated_amount <= l_max_amount AND l_processing_row </*=*/ l_max_line
434 THEN
435
436 -- begin log
437 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
438 THEN
439 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'l_accumulated_amount <= l_max_amount AND l_processing_row </*=*/ l_max_line');
440 END IF;
441 -- end log
442
443 IF l_processing_row = l_GTA_TRX.trx_lines.COUNT
444 THEN
445 -- now this can due to the same condition at
446 l_gta_trx.trx_header.group_number := l_trx_group_number;
447 l_trx_group_number := l_trx_group_number + 1;
448 process_before_split(x_gta_trx_rec => l_GTA_TRX
449 );
450 x_GTA_TRX_Tbl.EXTEND;
451 x_GTA_TRX_Tbl(x_GTA_TRX_Tbl.COUNT) := l_GTA_TRX;
452
453 -- else goto end loop and due to next line
454 END IF; -- end if l_processing_row = l_GTA_TRX.trx_lines.COUNT
455
456 -- > line < amount and sales 'N', split by line
457 ELSIF l_processing_row >= l_max_line AND l_sales_list_flag = 'Y' AND l_accumulated_amount <= l_max_amount
458 THEN
459
460 -- begin log
461 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
462 THEN
463 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'l_processing_row >= l_max_line AND l_sales_list_flag = ''Y'' AND l_accumulated_amount <= l_max_amount');
464 END IF;
465 -- end log
466
467 IF l_processing_row = l_GTA_TRX.trx_lines.COUNT
468 THEN
469 l_gta_trx.trx_header.group_number := l_trx_group_number;
470 l_trx_group_number := l_trx_group_number + 1;
471 process_before_split(x_gta_trx_rec => l_GTA_TRX
472 );
473 x_GTA_TRX_Tbl.EXTEND;
474 x_GTA_TRX_Tbl(x_GTA_TRX_Tbl.COUNT) := l_GTA_TRX;
475
476 -- ELSE goto end loop and due to next line
477 END IF;/*end if l_processing_row = l_GTA_TRX.trx_lines.COUNT*/
478
479 ELSIF l_processing_row = l_max_line AND l_sales_list_flag = 'N' AND l_accumulated_amount <= l_max_amount
480 THEN
481 IF l_processing_row = l_GTA_TRX.trx_lines.COUNT
482 THEN
483 l_gta_trx.trx_header.group_number := l_trx_group_number;
484 l_trx_group_number := l_trx_group_number + 1;
485 process_before_split(x_gta_trx_rec => l_GTA_TRX
486 );
487
488 x_GTA_TRX_Tbl.EXTEND;
489 x_GTA_TRX_Tbl(x_GTA_TRX_Tbl.COUNT) := l_GTA_TRX;
490
491
492
493 ELSE /*l_processing_row < l_GTA_TRX.trx_lines.COUNT */
494
495 split_nested_table(p_trx_lines => l_gta_trx.trx_lines
499 );
496 , p_split_flag => l_processing_row
497 , x_first_lines => l_trx_lines_new
498 , x_last_lines => l_trx_lines
500
501 -- copy trx header
502 copy_header(p_GTA_TRX_Header_Rec => l_GTA_TRX.trx_header
503 , x_GTA_TRX_Header_Rec => l_trx_header_new
504 );
505
506
507 -- get new trx number
508 l_GTA_Trx_new.trx_header := l_GTA_TRX.trx_header;
509 l_gta_trx_new.trx_lines := l_trx_lines_new;
510
511 -- add new trx to result tbl
512 l_GTA_Trx_new.trx_header.group_number := l_trx_group_number;
513 l_trx_group_number := l_trx_group_number + 1;
514 process_before_split(x_gta_trx_rec => l_GTA_TRX_new
515 );
516
517 x_GTA_TRX_Tbl.EXTEND;
518 x_gta_trx_tbl(x_gta_trx_tbl.COUNT) := l_gta_trx_new;
519 --add_succ_to_temp(l_GTA_TRX);
520
521 -- update the header , replace the l_gta_trx header by new header
522 l_GTA_TRX.trx_header := l_trx_header_new;
523
524 -- update the old trx.trx_lines
525 l_GTA_TRX.trx_lines := l_trx_lines;
526
527 -- init l_processing_row and l_accumulateed_amount
528 l_processing_row := 0;
529 l_accumulated_amount := 0;
530
531 END IF; /*l_processing_row < l_GTA_TRX.trx_lines.COUNT */
532
533 /*This condition include the l_process_number > max_line, l_procecss_number = max_line, l_process_number < max_line,
534 * due to this three condition the process is same, split by amount.
535 */
536 ELSIF l_accumulated_amount > l_max_amount
537 THEN
538
539 -- begin log
540 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
541 THEN
542 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'l_accumulated_amount > l_max_amount');
543 END IF;
544 -- end log
545
546 l_trx_lines := l_GTA_TRX.trx_lines;
547
548 IF l_split_flag = 'Y'
549 THEN
550 IF l_gta_trx.trx_lines.COUNT = 1
551 THEN
552 l_GTA_Trx_line_old := l_GTA_TRX.trx_lines(l_processing_row);
553 l_GTA_TRX_line_new := l_GTA_Trx_line_old;
554
555 -- 25-Apr-2006: Jogen Hu bug 5168852
556 /*l_quantity_limit:=
557 floor((l_max_amount - l_accumulated_amount + l_GTA_Trx_line_old.amount)/l_GTA_Trx_line_old.unit_price);*/
558 l_quantity_limit:=
559 floor((l_max_amount - l_accumulated_amount + l_GTA_Trx_line_old.amount)/l_functional_price);
560 -- 25-Apr-2006: Jogen Hu bug 5168852
561
562 IF l_quantity_limit > 0
563 THEN
564 l_GTA_TRX_line_new.quantity := l_quantity_limit;
565
566 -- 25-Apr-2006: Jogen Hu bug 5168852
567 --l_GTA_TRX_line_new.amount := l_quantity_limit * l_GTA_TRX_line_new.unit_price;
568 l_GTA_TRX_line_new.amount := l_quantity_limit * l_functional_price;
569 -- 25-Apr-2006: Jogen Hu bug 5168852
570
571 -- it should be change after ebtax!!!
572
573 -- 25-Apr-2006: Jogen Hu bug 5168852
574 --l_GTA_TRX_line_new.tax_amount := l_GTA_TRX_line_new.tax_rate*(l_quantity_limit * l_GTA_TRX_line_new.unit_price);
575 l_GTA_TRX_line_new.tax_amount := l_GTA_TRX_line_new.tax_rate*(l_quantity_limit * l_functional_price);
576 -- 25-Apr-2006: Jogen Hu bug 5168852
577
578 l_gta_trx_line_new.original_currency_amount := l_quantity_limit * l_GTA_TRX_line_new.unit_price;
579
580 SELECT
581 JMF_GTA_TRX_LINES_ALL_S.NEXTVAL
582 INTO
583 l_GTA_TRX_line_new.GTA_TRX_LINE_ID
584 FROM
585 dual;
586
587 --l_GTA_TRX_line_new.GTA_TRX_LINE_ID := JMF_GTA_TRX_LINES_ALL_S.NEXTVAL;
588 l_trx_lines_new.EXTEND;
589 l_trx_lines_new(l_trx_lines_new.COUNT) := l_GTA_TRX_line_new;
590 -- ELSE l_quantity_limit =0 , then the old line is no change and new line is not needed
591 END IF;/*l_quantity_limit > 0*/
592
593 l_GTA_TRX_line_old.quantity := l_GTA_Trx_line_old.quantity - l_quantity_limit;
594
595 -- 25-Apr-2006: Jogen Hu bug 5168852
596 --l_GTA_TRX_line_old.amount := l_amount - l_quantity_limit*l_GTA_Trx_line_old.unit_price;
597 l_GTA_TRX_line_old.amount := l_amount - l_quantity_limit*l_functional_price;
598
599 --l_GTA_Trx_line_old.tax_amount := l_GTA_Trx_line_old.tax_rate*(l_amount - l_quantity_limit*l_GTA_Trx_line_old.unit_price);
603 l_GTA_Trx_line_old.original_currency_amount := l_amount - l_quantity_limit*l_GTA_Trx_line_old.unit_price;
600 l_GTA_Trx_line_old.tax_amount := l_GTA_Trx_line_old.tax_rate*(l_amount - l_quantity_limit*l_functional_price);
601 -- 25-Apr-2006: Jogen Hu bug 5168852
602
604 -- insert old line to the head of l_trx_lines
605 l_trx_lines(1) := l_GTA_Trx_line_old;
606 -- new trx
607 copy_header(p_GTA_TRX_Header_Rec => l_GTA_TRX.trx_header
608 , x_GTA_TRX_Header_Rec => l_trx_header_new
609 );
610
611 l_GTA_Trx_new.trx_header := l_GTA_TRX.trx_header;
612 l_GTA_Trx_new.trx_lines := l_trx_lines_new;
613
614 -- add the new trx to result table
615 l_gta_trx_new.trx_header.group_number := l_trx_group_number;
616 l_trx_group_number := l_trx_group_number + 1;
617 process_before_split(x_gta_trx_rec => l_GTA_TRX_new
618 );
619 x_GTA_TRX_Tbl.EXTEND;
620 x_GTA_TRX_Tbl(x_GTA_TRX_Tbl.COUNT) := l_GTA_Trx_new;
621 --add_succ_to_temp(l_GTA_TRX);
622
623 -- update the header of old trx
624 l_gta_trx.trx_header := l_trx_header_new;
625 -- update the lines of old trx
626 l_GTA_TRX.trx_lines := l_trx_lines;
627
628 -- init the l_processing_row and l_accumulated_amount
629 l_processing_row := 0;
630 l_accumulated_amount := 0;
631
632 ELSE /*l_gta_trx.trx_lines.COUNT = 1*/
633
634 -- split trx lines exclude the last line
635 split_nested_table(p_trx_lines => l_GTA_TRX.trx_lines
636 , p_split_flag => l_processing_row - 1
637 , x_first_lines => l_trx_lines_new
638 , x_last_lines => l_trx_lines
639 );
640
641 -- split line to new line and old line
642 l_GTA_Trx_line_old := l_GTA_TRX.trx_lines(l_processing_row);
643 l_GTA_TRX_line_new := l_GTA_Trx_line_old;
644
645 -- 25-Apr-2006: Jogen Hu bug 5168852
646 /*l_quantity_limit:=
647 floor((l_max_amount - l_accumulated_amount + l_GTA_Trx_line_old.amount)/l_GTA_Trx_line_old.unit_price);*/
648
649 l_quantity_limit:=
650 floor((l_max_amount - l_accumulated_amount + l_GTA_Trx_line_old.amount)/l_functional_price);
651 -- 25-Apr-2006: Jogen Hu bug 5168852
652
653 IF l_quantity_limit > 0
654 THEN
655 l_GTA_TRX_line_new.quantity := l_quantity_limit;
656
657 -- 25-Apr-2006: Jogen Hu bug 5168852
658 --l_GTA_TRX_line_new.amount := l_quantity_limit * l_GTA_TRX_line_new.unit_price;
659 l_GTA_TRX_line_new.amount := l_quantity_limit * l_functional_price;
660
661 --l_GTA_TRX_line_new.tax_amount := l_GTA_TRX_line_new.tax_rate * (l_quantity_limit * l_GTA_TRX_line_new.unit_price);
662 l_GTA_TRX_line_new.tax_amount := l_GTA_TRX_line_new.tax_rate * (l_quantity_limit * l_functional_price);
663 -- 25-Apr-2006: Jogen Hu bug 5168852
664
665 l_GTA_TRX_line_new.original_currency_amount := l_quantity_limit * l_GTA_TRX_line_new.unit_price;
666 SELECT
667 JMF_GTA_TRX_LINES_ALL_S.NEXTVAL
668 INTO
669 l_GTA_TRX_line_new.GTA_TRX_LINE_ID
670 FROM
671 dual;
672 --l_GTA_TRX_line_new.GTA_TRX_LINE_ID := JMF_GTA_TRX_LINES_ALL_S.NEXTVAL;
673 l_trx_lines_new.EXTEND;
674 l_trx_lines_new(l_trx_lines_new.COUNT) := l_GTA_TRX_line_new;
675 -- ELSE l_quantity_limit =0 , then the old line is no change and new line is not needed
676 END IF;/*l_quantity_limit > 0*/
677
678 l_GTA_TRX_line_old.quantity:=l_GTA_Trx_line_old.quantity - l_quantity_limit;
679
680 -- 25-Apr-2006: Jogen Hu bug 5168852
681 --l_GTA_TRX_line_old.amount:=l_amount - l_quantity_limit*l_GTA_Trx_line_old.unit_price;
682 l_GTA_TRX_line_old.amount:=l_amount - l_quantity_limit*l_functional_price;
683
684 --l_GTA_Trx_line_old.tax_amount := l_GTA_Trx_line_old.tax_rate * (l_amount - l_quantity_limit*l_GTA_Trx_line_old.unit_price);
685 l_GTA_Trx_line_old.tax_amount := l_GTA_Trx_line_old.tax_rate * (l_amount - l_quantity_limit*l_functional_price);
686 -- 25-Apr-2006: Jogen Hu bug 5168852
687
688 l_GTA_Trx_line_old.original_currency_amount := l_amount - l_quantity_limit*l_GTA_Trx_line_old.unit_price;
689 -- insert old line to the head of l_trx_lines
690 l_trx_lines(1) := l_GTA_Trx_line_old;
691 -- new trx
692 copy_header(p_GTA_TRX_Header_Rec => l_GTA_TRX.trx_header
693 , x_GTA_TRX_Header_Rec => l_trx_header_new);
697
694
695 l_GTA_Trx_new.trx_header := l_GTA_TRX.trx_header;
696 l_GTA_Trx_new.trx_lines := l_trx_lines_new;
698 -- add the new trx to result table
699 l_GTA_Trx_new.trx_header.group_number := l_trx_group_number;
700 l_trx_group_number := l_trx_group_number + 1;
701 process_before_split(x_gta_trx_rec => l_GTA_TRX_new
702 );
703 x_GTA_TRX_Tbl.EXTEND;
704 x_GTA_TRX_Tbl(x_GTA_TRX_Tbl.COUNT) := l_GTA_Trx_new;
705 --add_succ_to_temp(l_GTA_TRX);
706
707 -- update the header of old trx
708 l_gta_trx.trx_header := l_trx_header_new;
709 -- update the lines of old trx
710 l_GTA_TRX.trx_lines := l_trx_lines;
711
712 -- init the l_processing_row and l_accumulated_amount
713 l_processing_row := 0;
714 l_accumulated_amount := 0;
715
716 END IF ; /*l_gta_trx.trx_lines.COUNT = 1*/
717
718 ELSE /* l_split_flag = 'N' */
719 IF l_processing_row = 1
720 THEN
721 split_nested_table(p_trx_lines => l_GTA_TRX.trx_lines
722 , p_split_flag => l_processing_row -1
723 , x_first_lines => l_trx_lines_new
724 , x_last_lines => l_trx_lines);
725
726 -- split this line
727 l_GTA_Trx_line_old := l_GTA_TRX.trx_lines(l_processing_row);
728 l_GTA_TRX_line_new := l_GTA_Trx_line_old;
729
730 -- get quantity limit
731 -- 25-Apr-2006: Jogen Hu bug 5168852
732 /* l_quantity_limit:=
733 floor((l_max_amount - l_accumulated_amount + l_GTA_Trx_line_old.amount)/l_GTA_Trx_line_old.unit_price);*/
734 l_quantity_limit:=
735 floor((l_max_amount - l_accumulated_amount + l_GTA_Trx_line_old.amount)/l_functional_price);
736 -- 25-Apr-2006: Jogen Hu bug 5168852
737
738 IF l_quantity_limit > 0
739 THEN
740 l_GTA_TRX_line_new.quantity := l_quantity_limit;
741
742 -- 25-Apr-2006: Jogen Hu bug 5168852
743 --l_GTA_TRX_line_new.amount := l_quantity_limit * l_GTA_TRX_line_new.unit_price;
744 l_GTA_TRX_line_new.amount := l_quantity_limit * l_functional_price;
745
746 --l_gta_trx_line_new.tax_amount := l_gta_trx_line_new.tax_rate * (l_quantity_limit * l_GTA_TRX_line_new.unit_price);
747 l_gta_trx_line_new.tax_amount := l_gta_trx_line_new.tax_rate * (l_quantity_limit * l_functional_price);
748 -- 25-Apr-2006: Jogen Hu bug 5168852
749
750 l_gta_trx_line_new.original_currency_amount := l_quantity_limit * l_GTA_TRX_line_new.unit_price;
751 SELECT
752 JMF_GTA_TRX_LINES_ALL_S.NEXTVAL
753 INTO
754 l_GTA_TRX_line_new.GTA_TRX_LINE_ID
755 FROM
756 dual;
757 --l_GTA_TRX_line_new.GTA_TRX_LINE_ID := JMF_GTA_TRX_LINES_ALL_S.NEXTVAL;
758 l_trx_lines_new.EXTEND;
759 l_trx_lines_new(l_trx_lines_new.COUNT) := l_GTA_TRX_line_new;
760 -- ELSE l_quantity_limit =0 , then the old line is no change and new line is not needed
761 END IF;/*l_quantity_limit > 0*/
762
763 l_GTA_TRX_line_old.quantity:=l_GTA_Trx_line_old.quantity - l_quantity_limit;
764
765 -- 25-Apr-2006: Jogen Hu bug 5168852
766 --l_GTA_TRX_line_old.amount:=l_amount - l_quantity_limit*l_GTA_Trx_line_old.unit_price;
767 l_GTA_TRX_line_old.amount:=l_amount - l_quantity_limit*l_functional_price;
768
769 --l_GTA_Trx_line_old.tax_amount := l_GTA_Trx_line_old.tax_rate * (l_amount - l_quantity_limit*l_GTA_Trx_line_old.unit_price);
770 l_GTA_Trx_line_old.tax_amount := l_GTA_Trx_line_old.tax_rate * (l_amount - l_quantity_limit*l_functional_price);
771 -- 25-Apr-2006: Jogen Hu bug 5168852
772
773 l_GTA_TRX_line_old.original_currency_amount:= l_amount - l_quantity_limit*l_GTA_Trx_line_old.unit_price;
774 -- insert old line to the head of l_trx_lines
775 l_trx_lines(1) := l_GTA_Trx_line_old;
776 -- new trx
777 copy_header(p_GTA_TRX_Header_Rec => l_GTA_TRX.trx_header
778 , x_GTA_TRX_Header_Rec => l_trx_header_new);
779
780 l_GTA_Trx_new.trx_header := l_GTA_TRX.trx_header;
781 l_GTA_Trx_new.trx_lines := l_trx_lines_new;
782
783 -- add the new trx to result table
784 l_gta_trx_new.trx_header.group_number := l_trx_group_number;
785 l_trx_group_number := l_trx_group_number + 1;
786 process_before_split(x_gta_trx_rec => l_GTA_TRX_new
787 );
788 x_GTA_TRX_Tbl.EXTEND;
789 x_GTA_TRX_Tbl(x_GTA_TRX_Tbl.COUNT) := l_GTA_Trx_new;
790 --add_succ_to_temp(l_GTA_TRX);
791
792 -- update the header of old trx
793 l_gta_trx.trx_header := l_trx_header_new;
797 -- init the l_processing_row and l_accumulated_amount
794 -- update the lines of old trx
795 l_GTA_TRX.trx_lines := l_trx_lines;
796
798 l_processing_row := 0;
799 l_accumulated_amount := 0;
800
801 ELSE /*l_gta_trx.trx_lines.COUNT = 1*/
802 split_nested_table(p_trx_lines => l_GTA_TRX.trx_lines
803 , p_split_flag => l_processing_row -1
804 , x_first_lines => l_trx_lines_new
805 , x_last_lines => l_trx_lines);
806
807 -- copy trx header
808 copy_header(p_GTA_TRX_Header_Rec => l_GTA_TRX.trx_header
809 , x_GTA_TRX_Header_Rec => l_trx_header_new );
810
811 -- get new trx number
812 l_GTA_Trx_new.trx_header := l_GTA_TRX.trx_header;
813 l_gta_trx_new.trx_lines := l_trx_lines_new;
814
815 -- add it to result tbl
816 l_gta_trx_new.trx_header.group_number := l_trx_group_number;
817 l_trx_group_number := l_trx_group_number + 1;
818 process_before_split(x_gta_trx_rec => l_GTA_TRX_new
819 );
820
821 x_GTA_TRX_Tbl.EXTEND;
822 x_gta_trx_tbl(x_gta_trx_tbl.COUNT) := l_gta_trx_new;
823
824 --update the header of olf trx
825 l_gta_trx.trx_header := l_trx_header_new;
826 -- update the old trx.trx_lines
827 l_GTA_TRX.trx_lines := l_trx_lines;
828
829 -- init l_processing_row and l_accumulateed_amount
830 l_processing_row := 0;
831 l_accumulated_amount := 0;
832 END IF;/*l_gta_trx.trx_lines.COUNT = 1*/
833
834 END IF; -- end if l_split_flag = 'Y'
835
836 --ELSE
837 --l_processing_row = l_max_line AND l_sales_list_flag = 'N' and l_accumulated_amount > l_max_amount
838
839 END IF; -- /*end if l_accumulated_amount <= l_max_amount AND l_processing_row<l_max_line*/
840
841 l_processing_row := l_processing_row + 1;
842
843 END LOOP; --end loop by lines
844
845 END IF; -- end if (l_sum_of_amount<l_max_amount AND (l_lines_number<l_max_line or l_sales_list_flag='Y' ))
846
847 --END IF;/*l_trx_type = 'CM'*/
848
849 --begin split the l_gta_trx
850
851
852 l_trx_rate_index := l_gta_rate_trx_tbl.NEXT(l_trx_rate_index);
853 END LOOP; -- end loop of l_gta_rate_trx_tbl /*l_trx_rate_index IS NOT NULL*/
854
855 -- add the succesed infomation into the temp table
856
857 add_succ_to_temp(l_gta_trx_succesed);
858 l_trx_index := P_GTA_TRX_Tbl.NEXT(l_trx_index);
859
860 END LOOP; -- end loop by P_gta_trx_tbl /*l_trx_index IS NOT NULL*/
861
862 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
863 THEN
864 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
865 , G_MODULE_PREFIX || l_procedure_name
866 ,'End Procedure. ');
867 END IF;
868
869 EXCEPTION
870 WHEN OTHERS THEN
871 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
872 THEN
873 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
874 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
875 , Sqlcode||Sqlerrm);
876 END IF;
877 RAISE;
878 END Split_Transactions;
879
880
881 --=============================================================================
882 -- PROCEDURE NAME :
883 -- Copy_Header
884 -- TYPE:
885 -- PUBLIC
886 --
887 -- DESCRIPTION:
888 -- When split trx procedure new a trx, new a trx_header for the new trx
889 -- PARAMETERS:
890 -- p_GTA_TRX_Header_Rec IN old trx_header
891 -- x_GTA_TRX_Header_Rec OUT new trx_header
892 --
893 -- HISTORY:
894 -- 10-MAY-2005 : Jim.Zheng Create
895 --=============================================================================
896 PROCEDURE Copy_Header
897 ( p_GTA_TRX_Header_Rec IN JMF_GTA_TRX_UTIL.TRX_HEADER_REC_TYPE
898 , x_GTA_TRX_Header_Rec OUT NOCOPY JMF_GTA_TRX_UTIL.TRX_HEADER_REC_TYPE
899 ) AS
900 l_procedure_name VARCHAR2(30):= 'Copy_Header';
901 l_gta_header_id jmf_gta_trx_headers.gta_trx_header_id%TYPE;
902 l_gta_trx_header_rec JMF_GTA_TRX_UTIL.TRX_HEADER_REC_TYPE;
903
904 BEGIN
905 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
906 THEN
907 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
908 , G_MODULE_PREFIX || l_procedure_name
909 ,'Begin Procedure. ');
910 END IF;
911
912 l_GTA_TRX_Header_Rec := p_GTA_TRX_Header_Rec;
913 SELECT
914 JMF_GTA_TRX_HEADERS_ALL_S.NEXTVAL
915 INTO
916 l_GTA_TRX_Header_Rec.gta_trx_header_id
917 FROM
918 dual;
919 --x_GTA_TRX_Header_Rec.gta_trx_header_id := JMF_GTA_TRX_HEADERS_ALL_S.NEXTVAL;
920 -- use this if is due to the trx first split and
921
922 l_GTA_TRX_Header_Rec.group_number := p_GTA_TRX_Header_Rec.group_number + 1;
923
924 l_GTA_TRX_Header_rec.gta_trx_number := l_GTA_TRX_Header_Rec.ra_trx_id
925 || '-'
929
926 || l_GTA_TRX_Header_Rec.group_number
927 || '-'
928 || l_GTA_TRX_Header_rec.version;
930 x_GTA_TRX_Header_Rec := l_gta_trx_header_rec;
931
932 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
933 THEN
934 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
935 , G_MODULE_PREFIX || l_procedure_name
936 ,'End Procedure. ');
937 END IF;
938
939 EXCEPTION
940 WHEN OTHERS THEN
941 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
942 THEN
943 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
944 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
945 , Sqlcode||Sqlerrm);
946 END IF;
947 RAISE;
948
949 END Copy_Header;
950
951 --=============================================================================
952 -- PROCEDURE NAME:
953 -- split_nested_table
954 -- TYPE:
955 -- PUBLIC
956 --
957 -- DESCRIPTION :
958 -- a nested table hasn't a split method, this procedure make a
959 -- split table method for TRX_Line_tbl_type
960 -- be change.
961 -- PARAMETERS:
962 -- p_trx_lines IN source table
963 -- split_flag IN the position of table which be split
964 -- x_first_lines OUT first party of demanation table
965 -- x_last_lines OUT last party of demanation table
966 --
967 -- HISTORY:
968 -- 10-MAY-2005 : Jim.Zheng Create
969 --=============================================================================
970 PROCEDURE Split_Nested_Table
971 (p_trx_lines IN JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE
972 , p_split_flag IN NUMBER
973 , x_first_lines OUT NOCOPY JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE
974 , x_last_lines OUT NOCOPY JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE
975 )
976 IS
977 l_first_lines JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE := JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
978 l_last_lines JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE := JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
979 l_index NUMBER;
980 l_procedure_name VARCHAR2(50):='Split_Nested_Table';
981
982 BEGIN
983 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
984 THEN
985 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
986 , G_MODULE_PREFIX || l_procedure_name
987 ,'Begin Procedure. ');
988 END IF;
989
990 --init
991 x_first_lines := JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
992 x_last_lines := JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
993
994 IF p_split_flag = 0
995 THEN
996 l_last_lines := p_trx_lines;
997 ELSE
998 l_first_lines.EXTEND(p_split_flag);
999
1000 l_index := l_first_lines.FIRST;
1001 WHILE l_index <= p_split_flag
1002 LOOP
1003 l_first_lines(l_index) := p_trx_lines(l_index);
1004 l_index := l_first_lines.NEXT(l_index);
1005 END LOOP;
1006
1007 l_last_lines.EXTEND(p_trx_lines.COUNT - p_split_flag);
1008
1009 l_index := l_last_lines.FIRST;
1010 WHILE l_index <= (p_trx_lines.COUNT - p_split_flag)
1011 LOOP
1012 l_last_lines(l_index) := p_trx_lines(l_index + p_split_flag);
1013 l_index := l_last_lines.NEXT(l_index);
1014 END LOOP;
1015 END IF;
1016
1017 x_first_lines := l_first_lines;
1018 x_last_lines := l_last_lines;
1019
1020 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1021 THEN
1022 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1023 , G_MODULE_PREFIX || l_procedure_name
1024 ,'End Procedure. ');
1025 END IF;
1026
1027 EXCEPTION
1028 WHEN OTHERS THEN
1029 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1030 THEN
1031 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1032 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
1033 , Sqlcode||Sqlerrm);
1034 END IF;
1035 RAISE;
1036 END split_nested_table;
1037
1038 --=============================================================================
1039 -- PROCEDURE NAME:
1040 -- process_before_split
1041 -- TYPE:
1042 -- PUBLIC
1043 --
1044 -- DESCRIPTION:
1045 -- When split trx procedure new a trx, the line_num, trx_num, trx_header_id alse
1046 -- be change.
1047 -- PARAMETERS:
1048 -- x_gta_trx_rec IN OUT NOCOPY new trx which line number is changed
1049 --
1050 -- HISTORY:
1051 -- 10-MAY-2005 : Jim.Zheng Create
1052 --=============================================================================
1053 PROCEDURE process_before_split
1054 (
1055 x_gta_trx_rec IN OUT NOCOPY Jmf_Gta_Trx_Util.TRX_REC_TYPE
1056 )
1057 AS
1058
1059 l_procedure_name VARCHAR2(35) := 'get_trx_header_id';
1060 l_index NUMBER; -- loop index
1061
1062 BEGIN
1063
1064 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1065 THEN
1066 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1067 , G_MODULE_PREFIX || l_procedure_name
1068 ,'Begin Procedure. ');
1069 END IF;
1070
1071 -- generate gta_trx_number
1072 x_GTA_TRX_rec.trx_header.gta_trx_number := x_GTA_TRX_Rec.trx_header.ra_trx_id
1076 || x_GTA_TRX_Rec.trx_header.version;
1073 || '-'
1074 || x_GTA_TRX_Rec.trx_header.group_number
1075 || '-'
1077
1078 -- generate line number
1079 l_index := x_gta_trx_rec.trx_lines.FIRST;
1080 WHILE l_index IS NOT NULL
1081 LOOP
1082 SELECT
1083 Jmf_Gta_Trx_Lines_All_s.NEXTVAL
1084 INTO
1085 x_gta_trx_rec.trx_lines(l_index).gta_trx_line_id
1086 FROM
1087 dual;
1088 x_gta_trx_rec.trx_lines(l_index).line_number := to_char(l_index);
1089 x_gta_trx_rec.trx_lines(l_index).gta_trx_header_id := x_gta_trx_rec.trx_header.gta_trx_header_id;
1090 l_index := x_gta_trx_rec.trx_lines.NEXT(l_index);
1091 END LOOP;
1092
1093 -- get fp registration number and tp registration number;
1094 --12/06/2006 Shujuan Yan bug 5168900
1095 IF x_gta_trx_rec.trx_lines.FIRST IS NOT NULL
1096 THEN
1097 x_gta_trx_rec.trx_header.fp_tax_registration_number := x_gta_trx_rec.trx_lines(1).fp_tax_registration_number;
1098 x_gta_trx_rec.trx_header.tp_tax_registration_number := x_gta_trx_rec.trx_lines(1).tp_tax_registration_number;
1099
1100 --28/12/07 added by subba for R12.1
1101
1102
1103 x_gta_trx_rec.trx_header.invoice_type := jmf_gta_trx_util.get_invoice_type(p_org_id => x_gta_trx_rec.trx_header.org_id
1104 ,p_customer_trx_id=> x_gta_trx_rec.trx_header.ra_trx_id
1105 ,p_fp_tax_registration_num => x_gta_trx_rec.trx_lines(1).fp_tax_registration_number);
1106
1107
1108 END IF;
1109 --12/06/2006 Shujuan Yan bug 5168900
1110
1111 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1112 THEN
1113 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1114 , G_MODULE_PREFIX || l_procedure_name
1115 ,'Begin Procedure. ');
1116 END IF;
1117
1118 EXCEPTION
1119 WHEN OTHERS THEN
1120 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1121 THEN
1122 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1123 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
1124 , Sqlcode||Sqlerrm);
1125 END IF;
1126 RAISE;
1127 END Process_Before_Split;
1128
1129
1130 --=============================================================================
1131 -- PROCEDURE NAME:
1132 -- add_succ_to_temp
1133 -- TYPE:
1134 -- PUBLIC
1135 --
1136 -- DESCRIPTION :
1137 -- insert a sucess row to temp table when a trx is be due successed
1138 -- be change.
1139 -- PARAMETERS:
1140 -- p_gta_trx_rec in the successed trx
1141 --
1142 -- HISTORY:
1143 -- 10-MAY-2005 : Jim.Zheng Create
1144 -- 15-Jan-2009: Yao Zhang Modified for bug 7709947
1145 --=============================================================================
1146 PROCEDURE add_succ_to_temp
1147 (
1148 p_gta_trx_rec IN Jmf_Gta_Trx_Util.TRX_REC_TYPE
1149 )
1150 AS
1151 l_amount NUMBER;
1152 l_trx_num VARCHAR2(30);
1153 l_trx_type VARCHAR2(30);
1154 l_cust_name VARCHAR2(360);--Yao Zhang changed for bug 7709947
1155 l_gta_inv_num VARCHAR2(30);
1156
1157 l_procedure_name VARCHAR2(30) := 'add_succ_to_temp';
1158 l_index NUMBER; -- loop index
1159 l_warning_record_count NUMBER;
1160 l_customer_trx_id NUMBER(15);
1161
1162 BEGIN
1163 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1164 THEN
1165 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1166 , G_MODULE_PREFIX || l_procedure_name
1167 ,'Begin Procedure. ');
1168 END IF;
1169
1170 l_amount := 0;
1171
1172 l_index := p_gta_trx_rec.trx_lines.FIRST;
1173 WHILE l_index IS NOT NULL
1174 LOOP
1175 l_amount := l_amount + p_gta_trx_rec.trx_lines(l_index).amount;
1176 l_index := p_gta_trx_rec.trx_lines.NEXT(l_index);
1177 END LOOP;
1178
1179 l_trx_num := p_gta_trx_rec.trx_header.ra_trx_number;
1180 l_customer_trx_id := p_gta_trx_rec.trx_header.ra_trx_id;
1181 BEGIN
1182 SELECT
1183 ctt.TYPE
1184 INTO
1185 l_trx_type
1186 FROM
1187 ra_customer_trx_all h
1188 , ra_cust_trx_types_all ctt
1189 WHERE ctt.cust_trx_type_id = h.cust_trx_type_id
1190 AND ctt.Org_Id = h.Org_Id
1191 AND h.customer_trx_id = p_gta_trx_rec.trx_header.ra_trx_id;
1192 EXCEPTION
1193 WHEN no_data_found THEN
1194 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1195 THEN
1196 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
1197 , G_MODULE_PREFIX || l_procedure_name
1198 ,'No data found. ');
1199 END IF;
1200 END;
1201
1202 --l_trx_type := p_gta_trx_rec.trx_header.trx_type;
1203 l_cust_name := p_gta_trx_rec.trx_header.bill_to_customer_name;
1204 l_gta_inv_num := p_gta_trx_rec.trx_header.gta_trx_number;
1205 SELECT
1206 COUNT(*)
1207 INTO
1208 l_warning_record_count
1209 FROM
1210 jmf_gta_transfer_temp temp
1211 WHERE temp.transaction_id = l_customer_trx_id
1212 AND temp.succeeded = 'W';
1213
1214 IF l_warning_record_count = 0
1215 THEN
1216 INSERT INTO
1220 , t.transaction_id
1217 jmf_gta_transfer_temp t
1218 (t.seq
1219 , t.succeeded
1221 , t.transaction_num
1222 , t.transaction_type
1223 , t.customer_name
1224 , t.amount
1225 , t.failedreason
1226 , t.gta_invoice_num
1227 )
1228 SELECT
1229 jmf_gta_transfer_temp_s.NEXTVAL
1230 , 'Y'
1231 , l_customer_trx_id
1232 , l_trx_num
1233 , l_trx_type
1234 , l_cust_name
1235 , l_amount
1236 , NULL
1237 , l_gta_inv_num
1238 FROM
1239 dual;
1240 ELSIF l_warning_record_count = 1
1241 THEN
1242 UPDATE
1243 jmf_gta_transfer_temp temp
1244 SET
1245 temp.amount = l_amount
1246 WHERE temp.transaction_id = l_customer_trx_id
1247 AND temp.succeeded = 'W';
1248
1249 END IF;/*l_warning_record_count = 0*/
1250
1251
1252 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1253 THEN
1254 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1255 , G_MODULE_PREFIX || l_procedure_name
1256 ,'Begin Procedure. ');
1257 END IF;
1258
1259 EXCEPTION
1260 WHEN OTHERS THEN
1261 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1262 THEN
1263 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1264 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
1265 , Sqlcode||Sqlerrm);
1266 END IF;
1267 RAISE;
1268
1269 END add_succ_to_temp;
1270
1271
1272 --=============================================================================
1273 -- PROCEDURE NAME:
1274 -- split_trx_by_taxreg_number
1275 -- TYPE:
1276 -- PUBLIC
1277 --
1278 -- DESCRIPTION:
1279 -- split trx by first party registration number
1280 -- PARAMETERS:
1281 -- p_gta_trx in JMF_GTA_TRX_UTIL.trx_rec_type
1282 -- x_trx_tbl out Jmf_Gta_Trx_Util.trx_tbl_type
1283 -- HISTORY:
1284 -- 10-Sep-2005 : Jim.Zheng Create
1285 --=============================================================================
1286 PROCEDURE split_trx_by_taxreg_number
1287 (p_gta_trx IN JMF_GTA_TRX_UTIL.trx_rec_type
1288 ,x_trx_tbl OUT NOCOPY Jmf_Gta_Trx_Util.trx_tbl_type
1289 )
1290 IS
1291 l_procedure_name VARCHAR2(100) := 'split_trx_by_tax_registration_number';
1292 l_gta_rate_trx_line JMF_GTA_TRX_UTIL.trx_line_rec_type;
1293 l_gta_rate_trx JMF_GTA_TRX_UTIL.trx_rec_type;
1294 l_gta_rate_trx_tbl JMF_GTA_TRX_UTIL.TRX_Tbl_TYPE := JMF_GTA_TRX_UTIL.TRX_Tbl_TYPE();
1295 l_tax_reg_number jmf_gta_trx_headers_all.fp_tax_registration_number%TYPE;
1296 l_add_flag NUMBER := 0;
1297 l_gta_trx JMF_GTA_TRX_UTIL.trx_rec_type;
1298 l_gta_trx_line_init JMF_GTA_TRX_UTIL.trx_line_rec_type;
1299 l_trx_header_new JMF_GTA_TRX_UTIL.TRX_header_rec_TYPE;
1300 l_trx_rate_index NUMBER; -- index for nested table loop
1301 l_index NUMBER; -- index for nested table loop
1302 BEGIN
1303 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1304 THEN
1305 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1306 , G_MODULE_PREFIX || l_procedure_name
1307 ,'Begin Procedure. ');
1308 END IF;
1309 -- init l_gta_rate_trx_tbl
1310 l_gta_rate_trx_tbl := JMF_GTA_TRX_UTIL.TRX_Tbl_TYPE();
1311
1312 -- init x_trx_tbl
1313 x_trx_tbl := JMF_GTA_TRX_UTIL.TRX_Tbl_TYPE();
1314
1315 -- init l_gta_rate_trx
1316 l_gta_rate_trx.trx_lines := JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
1317
1318 l_gta_trx := p_gta_trx;
1319
1320 -- split the different rate lines to different trx, use loop and if else
1321 l_trx_rate_index := l_GTA_TRX.trx_lines.FIRST;
1322 WHILE l_trx_rate_index IS NOT NULL
1323 LOOP
1324 -- init l_gta_rate_trx_line
1325 l_gta_rate_trx_line := l_gta_trx_line_init;
1326 l_add_flag := 0; --12/06/2006 Shujuan Yan bug
1327
1328 -- read first line , if it is first line
1329 -- new a trx, and insert it into l_gta_trx_tbl
1330 IF l_trx_rate_index=1
1331 THEN
1332 l_gta_rate_trx_line := l_GTA_TRX.trx_lines(l_trx_rate_index);
1333
1334 l_gta_rate_trx.trx_header := l_GTA_TRX.trx_header;
1335
1336 l_gta_rate_trx.trx_lines.EXTEND;
1337 l_gta_rate_trx.trx_lines(l_gta_rate_trx.trx_lines.COUNT) := l_gta_rate_trx_line;
1338
1339 l_gta_rate_trx_tbl.EXTEND;
1340 l_gta_rate_trx_tbl(l_gta_rate_trx_tbl.COUNT) := l_gta_rate_trx;
1341 -- if it isn't first line , compare the tax_rate to the pre trx
1342 ELSE
1343 l_gta_rate_trx_line := l_GTA_TRX.trx_lines(l_trx_rate_index);
1344 l_tax_reg_number := l_gta_rate_trx_line.fp_tax_registration_number;
1345 -- compare the lines rate , if same rate ,add the line to trx, else new trx and insert it in to l_gta_trx_tbl
1346 l_index := l_gta_rate_trx_tbl.FIRST;
1347 WHILE l_index IS NOT NULL
1348 LOOP
1349 IF l_gta_rate_trx_tbl(l_index).trx_lines(1).fp_tax_registration_number = l_tax_reg_number
1350 THEN
1351 l_add_flag := l_index;
1352 END IF ;
1353 l_index := l_gta_rate_trx_tbl.NEXT(l_index);
1354 END LOOP; -- end loop l_gta_trx_tbl;
1355
1356 -- if the tax_rate is not equal pre trx
1357 IF l_add_flag = 0
1361 , x_GTA_TRX_Header_Rec => l_trx_header_new );
1358 THEN
1359 -- new trx for new tax_rate
1360 copy_header( p_GTA_TRX_Header_Rec => l_gta_rate_trx_tbl(l_gta_rate_trx_tbl.COUNT).trx_header
1362 l_gta_rate_trx.trx_header := l_trx_header_new;
1363 l_gta_rate_trx.trx_lines := JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
1364 l_gta_rate_trx.trx_lines.EXTEND;
1365 l_gta_rate_trx.trx_lines(l_gta_rate_trx.trx_lines.COUNT) := l_gta_rate_trx_line;
1366
1367 l_gta_rate_trx_tbl.EXTEND;
1368 l_gta_rate_trx_tbl(l_gta_rate_trx_tbl.COUNT) := l_gta_rate_trx;
1369
1370 ELSE
1371 -- add the line to the same tax_rate trx lines
1372 l_gta_rate_trx_tbl(l_add_flag).trx_lines.EXTEND;
1373 l_gta_rate_trx_tbl(l_add_flag).trx_lines(l_gta_rate_trx_tbl(l_add_flag).trx_lines.COUNT) := l_gta_rate_trx_line;
1374 END IF; -- end if l_add_flag = 0
1375
1376 END IF; -- end if i=1;
1377
1378 l_trx_rate_index := l_gta_trx.trx_lines.NEXT(l_trx_rate_index);
1379 END LOOP; -- end loop l_Gta_TRx.trx_lines. Now the trx is split in a trx_tbl
1380
1381 -- output
1382 x_trx_tbl := l_gta_rate_trx_tbl;
1383
1384 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1385 THEN
1386 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1387 , G_MODULE_PREFIX || l_procedure_name
1388 ,'Begin Procedure. ');
1389 END IF;
1390
1391 EXCEPTION
1392 WHEN OTHERS THEN
1393 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1394 THEN
1395 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1396 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
1397 , Sqlcode||Sqlerrm);
1398 END IF;
1399 RAISE;
1400 END split_trx_by_taxreg_number;
1401
1402 --=============================================================================
1403 -- PROCEDURE NAME:
1404 -- split_trx_by_rate
1405 -- TYPE:
1406 -- PUBLIC
1407 --
1408 -- DESCRIPTION:
1409 -- split trx by tax rate
1410 -- PARAMETERS:
1411 -- p_gta_trx in JMF_GTA_TRX_UTIL.trx_tbl_type
1412 -- x_trx_tbl out Jmf_Gta_Trx_Util.trx_tbl_type
1413 -- HISTORY:
1414 -- 10-Sep-2005 : Jim.Zheng Create
1415 --=============================================================================
1416 PROCEDURE split_trx_by_rate
1417 (p_gta_tbl IN JMF_GTA_TRX_UTIL.trx_tbl_type
1418 ,x_trx_tbl OUT NOCOPY Jmf_Gta_Trx_Util.trx_tbl_type
1419 )
1420 IS
1421 l_procedure_name VARCHAR2(100) := 'split_trx_by_tax_registration_number';
1422 l_gta_rate_trx_line JMF_GTA_TRX_UTIL.trx_line_rec_type;
1423 l_gta_rate_trx JMF_GTA_TRX_UTIL.trx_rec_type;
1424 l_gta_rate_trx_tbl JMF_GTA_TRX_UTIL.TRX_Tbl_TYPE := JMF_GTA_TRX_UTIL.TRX_Tbl_TYPE();
1425 l_tax_rate NUMBER;
1426 l_add_flag NUMBER := 0;
1427 l_gta_trx JMF_GTA_TRX_UTIL.trx_rec_type;
1428 l_gta_trx_line_init JMF_GTA_TRX_UTIL.trx_line_rec_type;
1429 l_trx_header_new JMF_GTA_TRX_UTIL.TRX_header_rec_TYPE;
1430 l_trx_rate_index NUMBER; -- index for nested table loop
1431 l_index NUMBER; -- index for nested table loop
1432 l_gta_index NUMBER; -- index for nested table loop
1433 BEGIN
1434 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1435 THEN
1436 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1437 , G_MODULE_PREFIX || l_procedure_name
1438 ,'Begin Procedure. ');
1439 END IF;
1440 -- init l_gta_rate_trx_tbl
1441 l_gta_rate_trx_tbl := JMF_GTA_TRX_UTIL.TRX_Tbl_TYPE();
1442
1443 -- init x_trx_tbl
1444 x_trx_tbl := JMF_GTA_TRX_UTIL.TRX_Tbl_TYPE();
1445
1446 l_gta_index := p_gta_tbl.FIRST;
1447 WHILE l_gta_index IS NOT NULL
1448 LOOP
1449 -- init l_gta_rate_trx
1450 l_gta_rate_trx.trx_lines := JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
1451
1452 l_gta_trx := p_gta_tbl(l_gta_index);
1453
1454 -- split the different rate lines to different trx, use loop and if else
1455 l_trx_rate_index := l_GTA_TRX.trx_lines.FIRST;
1456 WHILE l_trx_rate_index IS NOT NULL
1457 LOOP
1458 -- init l_gta_rate_trx_line
1459 l_gta_rate_trx_line := l_gta_trx_line_init;
1460 l_add_flag := 0; --12/06/2006 Shujuan Yan bug
1461
1462 -- read first line , if it is first line
1463 -- new a trx, and insert it into l_gta_trx_tbl
1464 IF l_trx_rate_index=1
1465 THEN
1466 l_gta_rate_trx_line := l_GTA_TRX.trx_lines(l_trx_rate_index);
1467
1468 l_gta_rate_trx.trx_header := l_GTA_TRX.trx_header;
1469
1470 l_gta_rate_trx.trx_lines.EXTEND;
1471 l_gta_rate_trx.trx_lines(l_gta_rate_trx.trx_lines.COUNT) := l_gta_rate_trx_line;
1472
1473 l_gta_rate_trx_tbl.EXTEND;
1474 l_gta_rate_trx_tbl(l_gta_rate_trx_tbl.COUNT) := l_gta_rate_trx;
1475 -- if it isn't first line , compare the tax_rate to the pre trx
1476 ELSE
1477 l_gta_rate_trx_line := l_GTA_TRX.trx_lines(l_trx_rate_index);
1478 l_tax_rate := l_gta_rate_trx_line.tax_rate;
1479 -- compare the lines rate , if same rate ,add the line to trx, else new trx and insert it in to l_gta_trx_tbl
1480 l_index := l_gta_rate_trx_tbl.FIRST;
1481 WHILE l_index IS NOT NULL
1482 LOOP
1483 IF l_gta_rate_trx_tbl(l_index).trx_lines(1).tax_rate = l_tax_rate
1484 THEN
1488 END LOOP; -- end loop l_gta_trx_tbl;
1485 l_add_flag := l_index;
1486 END IF ;
1487 l_index := l_gta_rate_trx_tbl.NEXT(l_index);
1489
1490 -- if the tax_rate is not equal pre trx
1491 IF l_add_flag = 0
1492 THEN
1493 -- new trx for new tax_rate
1494 copy_header( p_GTA_TRX_Header_Rec => l_gta_rate_trx_tbl(l_gta_rate_trx_tbl.COUNT).trx_header
1495 , x_GTA_TRX_Header_Rec => l_trx_header_new );
1496 l_gta_rate_trx.trx_header := l_trx_header_new;
1497 l_gta_rate_trx.trx_lines := JMF_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
1498 l_gta_rate_trx.trx_lines.EXTEND;
1499 l_gta_rate_trx.trx_lines(l_gta_rate_trx.trx_lines.COUNT) := l_gta_rate_trx_line;
1500
1501 l_gta_rate_trx_tbl.EXTEND;
1502 l_gta_rate_trx_tbl(l_gta_rate_trx_tbl.COUNT) := l_gta_rate_trx;
1503
1504 ELSE
1505 -- add the line to the same tax_rate trx lines
1506 l_gta_rate_trx_tbl(l_add_flag).trx_lines.EXTEND;
1507 l_gta_rate_trx_tbl(l_add_flag).trx_lines(l_gta_rate_trx_tbl(l_add_flag).trx_lines.COUNT) := l_gta_rate_trx_line;
1508 END IF; -- end if l_add_flag = 0
1509
1510 END IF; -- end if i=1;
1511
1512 l_trx_rate_index := l_gta_trx.trx_lines.NEXT(l_trx_rate_index);
1513 END LOOP; -- end loop l_Gta_TRx.trx_lines. Now the trx is split in a trx_tbl
1514
1515 l_gta_index := p_gta_tbl.NEXT(l_gta_index);
1516 END LOOP; -- end loop p_gta_tbl
1517
1518 -- output
1519 x_trx_tbl := l_gta_rate_trx_tbl;
1520
1521 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1522 THEN
1523 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1524 , G_MODULE_PREFIX || l_procedure_name
1525 ,'Begin Procedure. ');
1526 END IF;
1527
1528 EXCEPTION
1529 WHEN OTHERS THEN
1530 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1531 THEN
1532 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1533 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
1534 , Sqlcode||Sqlerrm);
1535 END IF;
1536 RAISE;
1537 END split_trx_by_rate;
1538
1539 --=============================================================================
1540 -- PROCEDURE NAME:
1541 -- get_trx_type
1542 -- TYPE:
1543 -- PUBLIC
1544 --
1545 -- DESCRIPTION:
1546 -- get trx type by trx id
1547 -- PARAMETERS:
1548 -- p_org_id IN NUMBER
1549 -- p_gta_trx in JMF_GTA_TRX_UTIL.trx_tbl_type
1550 -- x_trx_type out ra_cust_trx_types_all.type%TYPE
1551 -- HISTORY:
1552 -- 10-Sep-2005 : Jim.Zheng Create
1553 --=============================================================================
1554 PROCEDURE get_trx_type
1555 (p_org_id IN NUMBER
1556 , p_gta_trx IN Jmf_Gta_Trx_Util.trx_rec_type
1557 , x_trx_type OUT NOCOPY ra_cust_trx_types_all.type%TYPE
1558 )
1559 IS
1560
1561 l_procedure_name VARCHAR2(30) := 'get_trx_type';
1562 l_trx_id ra_customer_trx_all.customer_trx_id%TYPE;
1563 l_trx_type ra_cust_trx_types_all.type%TYPE;
1564 BEGIN
1565 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1566 THEN
1567 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1568 , G_MODULE_PREFIX || l_procedure_name
1569 ,'Begin Procedure. ');
1570 END IF;
1571
1572 IF p_gta_trx.trx_header.ra_trx_id IS NOT NULL
1573 THEN
1574 l_trx_id := p_gta_trx.trx_header.ra_trx_id;
1575
1576 SELECT
1577 ctt.TYPE
1578 INTO
1579 l_trx_type
1580 FROM
1581 ra_customer_trx_all h
1582 ,ra_cust_trx_types_all ctt
1583 WHERE h.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID(+)
1584 AND ctt.TYPE IN ('INV', 'CM', 'DM')
1585 AND ctt.org_id = p_org_id
1586 AND h.customer_trx_id = l_trx_id;
1587 END IF;/*p_gta_trx.trx_header.ra_trx_id IS NOT NULL*/
1588
1589 x_trx_type := l_trx_type;
1590
1591 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1592 THEN
1593 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1594 , G_MODULE_PREFIX || l_procedure_name
1595 ,'Begin Procedure. ');
1596 END IF;
1597
1598 EXCEPTION
1599 WHEN OTHERS THEN
1600 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1601 THEN
1602 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1603 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
1604 , Sqlcode||Sqlerrm);
1605 END IF;
1606 RAISE;
1607 END get_trx_type;
1608
1609 --=============================================================================
1610 -- PROCEDURE NAME:
1611 -- judge_cm_limit
1612 -- TYPE:
1613 -- PUBLIC
1614 --
1615 -- DESCRIPTION:
1616 -- Judge wether the CM exceed the max line and max amount
1617 -- PARAMETERS:
1618 -- p_gta_trx in JMF_GTA_TRX_UTIL.trx_tbl_type
1619 -- p_org_id in number
1620 -- x_result out BOOLEAN
1621 -- HISTORY:
1622 -- 10-Sep-2005 : Jim.Zheng Create
1623 -- 16-Dec-2008 Yao Zhang Changed for bug 7644235
1624 -- 23-Jan-2009 : Yao Zhang changed for bug 7758496
1625 --=============================================================================
1626 PROCEDURE judge_cm_limit
1630 , x_result OUT NOCOPY BOOLEAN)
1627 (p_gta_trx IN Jmf_Gta_Trx_Util.trx_rec_type
1628 , p_org_id IN NUMBER
1629 , p_transfer_id IN NUMBER --yao zhang changed for bug 7758496
1631 IS
1632 l_procedure_name VARCHAR2(30) := 'judge_cm_limit';
1633 l_gta_trx Jmf_Gta_Trx_Util.trx_rec_type;
1634 l_fp_reg_num jmf_gta_trx_headers_all.fp_tax_registration_number%TYPE;
1635 l_max_amount jmf_gta_tax_limits_all.max_amount%TYPE;
1636 l_max_line jmf_gta_tax_limits_all.max_num_of_line%TYPE;
1637 l_lines_number NUMBER;
1638 l_sum_of_amount NUMBER;
1639 l_trx_line_index NUMBER;
1640 l_invoice_type jmf_gta_trx_headers_all.invoice_type%TYPE; --added by subba for R12.1
1641 l_sales_list_flag jmf_gta_rule_headers_all.Sales_List_Flag%TYPE;--added by Yao Zhang for bug 7758496
1642 BEGIN
1643 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1644 THEN
1645 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1646 , G_MODULE_PREFIX || l_procedure_name
1647 ,'Begin Procedure. ');
1648 END IF;
1649
1650 x_result := TRUE;
1651 l_gta_trx := p_gta_trx;
1652 l_fp_reg_num := l_gta_trx.trx_lines(1).fp_tax_registration_number;
1653
1654 --added by subba for R12.1, getting invoice type to caliculate VAT limits
1655
1656 l_invoice_type := jmf_gta_trx_util.get_invoice_type(p_org_id => l_gta_trx.trx_header.org_id
1657 ,p_customer_trx_id=> l_gta_trx.trx_header.ra_trx_id
1658 ,p_fp_tax_registration_num => l_fp_reg_num);
1659 l_sum_of_amount := 0;
1660
1661 BEGIN
1662 SELECT
1663 limits.max_amount
1664 , limits.max_num_of_line
1665 INTO
1666 l_max_amount
1667 , l_max_line
1668 FROM
1669 jmf_gta_tax_limits_all limits
1670 WHERE limits.fp_tax_registration_number = l_fp_reg_num
1671 AND limits.invoice_type = l_invoice_type
1672 AND limits.org_id = p_org_id;
1673
1674 EXCEPTION
1675 WHEN no_data_found THEN
1676 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1677 THEN
1678 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
1679 , G_MODULE_PREFIX || l_procedure_name
1680 , 'no data found');
1681
1682 --JMF_GTA_SYS_CONFIG_MISSING
1683 fnd_message.set_name('JMF', 'JMF_GTA_SYS_CONFIG_MISSING');
1684 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
1685 , G_MODULE_PREFIX || l_procedure_name
1686 , fnd_message.get());
1687
1688 END IF;
1689 RAISE;
1690 RETURN;
1691 END;
1692
1693 --the following code is added by Yao Zhang for bug 7758496
1694 BEGIN
1695 SELECT
1696 sales_list_flag
1697 INTO
1698 l_sales_list_flag
1699 FROM
1700 JMF_GTA_RULE_HEADERS_All
1701 WHERE org_id = p_org_id
1702 AND rule_header_id = p_transfer_id;
1703 EXCEPTION
1704 -- no data found , raise a data error
1705 WHEN no_data_found THEN
1706 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1707 THEN
1708 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
1709 , G_MODULE_PREFIX || l_procedure_name
1710 , 'no data found');
1711
1712 --JMF_GTA_SYS_CONFIG_MISSING
1713 fnd_message.set_name('JMF', 'JMF_GTA_SYS_CONFIG_MISSING');
1714 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
1715 , G_MODULE_PREFIX || l_procedure_name
1716 , fnd_message.get());
1717
1718 END IF;
1719 RAISE;
1720 RETURN;
1721 END;
1722 --add end by yao zhang for bug 7758496
1723
1724 l_lines_number := l_GTA_TRX.trx_lines.COUNT;
1725
1726 l_trx_line_index := l_gta_trx.trx_lines.FIRST;
1727 WHILE l_trx_line_index IS NOT NULL
1728 LOOP
1729
1730 l_sum_of_amount := l_sum_of_amount + l_GTA_TRX.trx_lines(l_trx_line_index).amount*(-1);
1731
1732 IF l_GTA_TRX.trx_lines(l_trx_line_index).quantity IS NOT NULL
1733 THEN
1734 IF l_GTA_TRX.trx_lines(l_trx_line_index).quantity > 0
1735 THEN
1736 l_GTA_TRX.trx_lines(l_trx_line_index).quantity := l_GTA_TRX.trx_lines(l_trx_line_index).quantity*(-1);
1737 END IF;/*l_GTA_TRX.trx_lines(l_trx_line_index).quantity > 0*/
1738 END IF;/*l_GTA_TRX.trx_lines(l_trx_line_index).quantity IS NOT NULL*/
1739 l_trx_line_index := l_gta_trx.trx_lines.NEXT(l_trx_line_index);
1740 END LOOP;
1741 -- max amount and max line is mandotrary not null
1742 IF l_max_amount IS NULL OR l_max_line IS NULL
1743 THEN
1744 x_result := FALSE;
1745 END IF; /*l_max_amount IS NULL OR l_max_line IS NULL*/
1746 --IF l_sum_of_amount > l_max_amount OR l_lines_number > l_max_line --delete By Yao Zhang for bug 7644235
1747 IF ABS(l_sum_of_amount) > l_max_amount OR --add By Yao Zhang for bug 7644235
1748 (l_lines_number > l_max_line and l_sales_list_flag='N') --yao zhang changed for bug 7758496
1749
1750 THEN
1751 x_result := FALSE;
1752 END IF;/*l_sum_of_amount > l_max_amount OR l_lines_number > l_max_line*/
1753
1754 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1755 THEN
1756 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1757 , G_MODULE_PREFIX || l_procedure_name
1758 ,'End Procedure. ');
1762 WHEN OTHERS THEN
1759 END IF;
1760
1761 EXCEPTION
1763 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1764 THEN
1765 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1766 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
1767 , Sqlcode||Sqlerrm);
1768 END IF;
1769 RAISE;
1770 END;
1771
1772
1773 --=============================================================================
1774 -- PROCEDURE NAME:
1775 -- get_max_amount_line
1776 -- TYPE:
1777 -- PUBLIC
1778 --
1779 -- DESCRIPTION:
1780 -- get max line and max amount by fp registration number
1781 -- PARAMETERS:
1782 -- p_gta_trx in JMF_GTA_TRX_UTIL.trx_tbl_type
1783 -- p_org_id in number
1784 -- x_max_amount in number
1785 -- x_max_line in number
1786 -- HISTORY:
1787 -- 10-Sep-2005 : Jim.Zheng Create
1788 --=============================================================================
1789 PROCEDURE get_max_amount_line
1790 (p_gta_trx IN Jmf_Gta_Trx_Util.trx_rec_type
1791 , p_org_id IN NUMBER
1792 , x_max_amount OUT NOCOPY NUMBER
1793 , x_max_line OUT NOCOPY NUMBER)
1794 IS
1795 l_procedure_name VARCHAR2(30) := 'judge_cm_limit';
1796 l_gta_trx Jmf_Gta_Trx_Util.trx_rec_type;
1797 l_fp_reg_num jmf_gta_trx_headers_all.fp_tax_registration_number%TYPE;
1798 l_max_amount NUMBER;
1799 l_max_line NUMBER;
1800 l_error_string VARCHAR2(2000); --added by subba.
1801 BEGIN
1802 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1803 THEN
1804 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1805 , G_MODULE_PREFIX || l_procedure_name
1806 ,'Begin Procedure. ');
1807 END IF;
1808
1809 -- begin log
1810 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1811 THEN
1812 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'Begin get_max_amount_line '||'p_org_id:'||P_org_id);
1813 END IF;
1814 -- end log
1815
1816 l_gta_trx := p_gta_trx;
1817 l_fp_reg_num := l_gta_trx.trx_lines(1).fp_tax_registration_number;
1818
1819 -- begin log
1820 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1821 THEN
1822 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'l_fp_reg_num: '||l_fp_reg_num);
1823 END IF;
1824 -- end log
1825 /* 28/12/07 commented by Subba to change the logic for R12.1
1826 BEGIN
1827 SELECT
1828 limits.max_amount
1829 , limits.max_num_of_line
1830 INTO
1831 l_max_amount
1832 , l_max_line
1833 FROM
1834 jmf_gta_tax_limits_all limits
1835 WHERE limits.fp_tax_registration_number = l_fp_reg_num
1836 AND limits.org_id = p_org_id;
1837
1838 EXCEPTION
1839 WHEN no_data_found THEN
1840 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1841 THEN
1842 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
1843 , G_MODULE_PREFIX || l_procedure_name
1844 , 'no data found');
1845
1846 --JMF_GTA_SYS_CONFIG_MISSING
1847 fnd_message.set_name('JMF', 'JMF_GTA_SYS_CONFIG_MISSING');
1848 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
1849 , G_MODULE_PREFIX || l_procedure_name
1850 , fnd_message.get());
1851
1852 END IF;
1853 RAISE;
1854 RETURN;
1855 END;
1856 */ --New logic is added below
1857
1858
1859
1860 l_gta_trx.trx_header.invoice_type := jmf_gta_trx_util.get_invoice_type(p_org_id => p_org_id
1861 ,p_customer_trx_id=> l_gta_trx.trx_header.ra_trx_id
1862 ,p_fp_tax_registration_num => l_fp_reg_num);
1863
1864 BEGIN
1865 SELECT
1866 jgtla.max_amount
1867 ,jgtla.max_num_of_line
1868 INTO
1869 l_max_amount
1870 , l_max_line
1871 FROM
1872 jmf_gta_tax_limits_all jgtla
1873 WHERE jgtla.fp_tax_registration_number = l_fp_reg_num
1874 AND jgtla.invoice_type = l_gta_trx.trx_header.invoice_type
1875 AND jgtla.org_id = p_org_id;
1876
1877
1878
1879 EXCEPTION
1880 WHEN no_data_found THEN
1881
1882 --JMF_GTA_SYS_CONFIG_MISSING
1883 fnd_message.set_name('JMF', 'JMF_GTA_SYS_CONFIG_MISSING');
1884 l_error_string := fnd_message.get();
1885 -- output error
1886 fnd_file.put_line(fnd_file.output, '<?xml version="1.0" encoding="UTF-8" ?>
1887 <TransferReport>
1888 <ReportFailed>Y</ReportFailed>
1889 <ReportFailedMsg>'||l_error_string ||'</ReportFailedMsg>
1890 <TransferReport>');
1891
1892 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1893 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1894 ,G_MODULE_PREFIX || l_procedure_name
1895 ,'no data found for max_amt and mx_num_line'
1896 );
1897 END IF;
1898 RAISE;
1899 RETURN;
1900 END; -- added by Subba for R12.1
1901
1902
1903 IF l_max_line IS NOT NULL AND l_max_amount IS NOT NULL
1904 THEN
1905 x_max_line := l_max_line;
1906 x_max_amount := l_max_amount;
1907 END IF ; /*l_max_line IS NOT NULL AND l_max_amount IS NOT NULL*/
1908
1909 -- begin log
1910 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1911 THEN
1912 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'End get_max_amount_line:'||l_fp_reg_num);
1913 END IF;
1914 -- end log
1915
1916 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1917 THEN
1918 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1919 , G_MODULE_PREFIX || l_procedure_name
1920 ,'Begin Procedure. ');
1921 END IF;
1922
1923 EXCEPTION
1924 WHEN OTHERS THEN
1925 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1926 THEN
1927 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1928 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
1929 , Sqlcode||Sqlerrm);
1930 END IF;
1931 RAISE;
1932 END get_max_amount_line;
1933
1934 --=============================================================================
1935 -- PROCEDURE NAME:
1936 -- fileter_credit_memo
1937 -- TYPE:
1938 -- PUBLIC
1939 --
1940 -- DESCRIPTION:
1941 -- filter credit memo which amount and lines number exceeded.
1942 -- PARAMETERS:
1943 -- p_org_id in number
1944 -- p_gta_trx_tbl in jmf_gta_trx_util.trx_tbl_type
1945 -- x_gta_trx_tbl out nocopy jmf_gta_trx_util.trx_tbl_type
1946 --
1947 -- HISTORY:
1948 -- 10-Sep-2005 : Jim.Zheng Create
1949 -- 23-Jan-2009 : yao zhang changed for bug 7758496
1950 --=============================================================================
1951 PROCEDURE filter_credit_memo
1952 (p_org_id IN NUMBER
1956 )
1953 , p_transfer_id IN NUMBER--yao zhang changed for bug 7758496
1954 , p_gta_trx_tbl IN jmf_gta_trx_util.trx_tbl_type
1955 , x_gta_Trx_tbl OUT NOCOPY jmf_gta_trx_util.trx_tbl_type
1957 IS
1958 l_index NUMBER;
1959 l_procedure_name VARCHAR2(50) := 'filter_credit_memo';
1960 l_trx_type VARCHAR2(30);
1961 l_trx_reg_tbl jmf_gta_trx_util.trx_tbl_type;
1962 l_trx_rate_tbl jmf_gta_trx_util.trx_tbl_type;
1963 l_rate_index NUMBER;
1964 l_result BOOLEAN;
1965 l_gta_trx jmf_gta_trx_util.trx_rec_type;
1966 l_gta_rate_trx jmf_gta_trx_util.trx_rec_type;
1967 l_cm_exceed_limit EXCEPTION;
1968 l_error_string VARCHAR2(500);
1969
1970 BEGIN
1971
1972 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1973 THEN
1974 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1975 , G_MODULE_PREFIX || l_procedure_name
1976 ,'Begin Procedure. ');
1977 END IF;
1978
1979 -- begin log
1980 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1981 THEN
1982 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'Begin filter_credit_memo');
1983 END IF;
1984 -- end log
1985
1986 -- init x_gta_Trx_tbl
1987 x_gta_Trx_tbl := jmf_gta_trx_util.trx_tbl_type();
1988
1989 -- begin loop the p_gta_trx_tbl
1990 l_index := p_gta_trx_tbl.FIRST;
1991 WHILE l_index IS NOT NULL
1992 LOOP
1993 BEGIN
1994
1995 l_gta_trx := p_gta_trx_tbl(l_index);
1996 get_Trx_type(p_org_id => p_org_id
1997 , p_gta_trx => l_gta_trx
1998 , x_trx_type => l_trx_type);
1999 IF l_trx_type = 'CM'
2000 THEN
2001
2002 split_trx_by_taxreg_number(p_gta_trx => l_gta_trx
2003 , x_trx_tbl => l_trx_reg_tbl);
2004 split_trx_by_rate(p_gta_tbl => l_trx_reg_tbl
2005 ,x_trx_tbl => l_trx_rate_tbl);
2006
2007 l_rate_index := l_trx_rate_tbl.FIRST;
2008 WHILE l_rate_index IS NOT NULL
2009 LOOP
2010 l_gta_rate_trx := l_trx_rate_tbl(l_rate_index);
2011 judge_cm_limit(p_gta_trx => l_gta_rate_trx
2012 ,p_org_id => p_org_id
2013 , p_transfer_id => p_transfer_id
2014 ,x_result => l_result);
2015 IF l_result = FALSE
2016 THEN
2017 fnd_message.SET_NAME('JMF', 'JMF_GTA_CRMEMO_EXCEED_LIMIT');
2018 l_error_string := fnd_message.GET();
2019 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2020 THEN
2021 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2022 , G_MODULE_PREFIX || l_procedure_name
2023 , l_error_string
2024 );
2025 END IF;
2026 RAISE l_cm_exceed_limit;
2027 END IF;
2028 l_rate_index := l_trx_rate_tbl.NEXT(l_rate_index);
2029 END LOOP;/*l_rate_index IS NOT NULL*/
2030 x_gta_trx_tbl.EXTEND;
2031 x_gta_trx_tbl(x_gta_trx_tbl.COUNT) := l_gta_trx;
2032
2033 ELSE /*l_trx_type = 'CM'*/
2034 -- begin log
2035 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2036 THEN
2037 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'Is not a CM');
2038 END IF;
2039 -- end log
2040
2041 x_gta_trx_tbl.EXTEND;
2042 x_gta_trx_tbl(x_gta_trx_tbl.COUNT) := l_gta_trx;
2043 END IF;/*l_trx_type = 'CM'*/
2044
2045
2046 EXCEPTION
2047 WHEN l_cm_exceed_limit
2048 THEN
2049 --delete warning data from jmf_gta_transfer_temp
2050 DELETE
2051 jmf_gta_transfer_temp temp
2052 WHERE temp.transaction_id = l_gta_trx.trx_header.ra_trx_id
2053 AND temp.succeeded = 'W';
2054
2055 -- error message into temp table.
2056 INSERT INTO
2057 jmf_gta_transfer_temp t
2058 (t.seq
2059 , t.transaction_id
2060 , t.succeeded
2061 , t.transaction_num
2062 , t.transaction_type
2063 , t.customer_name
2064 , t.amount
2065 , t.failedreason
2066 , t.gta_invoice_num
2067 )
2068 SELECT
2069 jmf_gta_transfer_temp_s.NEXTVAL
2070 , l_gta_trx.trx_header.ra_trx_id
2071 , 'N'
2072 , l_gta_trx.trx_header.ra_trx_number
2073 , 'CM'
2074 , l_gta_trx.trx_header.bill_to_customer_name
2075 , NULL
2076 , l_error_string
2077 , NULL
2078 FROM
2079 dual;
2080 END;
2081 l_index := p_gta_trx_tbl.NEXT(l_index);
2082 END LOOP;/*l_index IS NOT NULL*/
2083
2084 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2085 THEN
2086 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2087 , G_MODULE_PREFIX || l_procedure_name
2088 ,'Begin Procedure. ');
2089 END IF;
2090
2091 -- begin log
2092 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2093 THEN
2094 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'End filter_credit_memo');
2095 END IF;
2096 -- end log
2097
2098 EXCEPTION
2099 WHEN OTHERS THEN
2100 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2101 THEN
2102 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
2103 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
2104 , Sqlcode||Sqlerrm);
2105 END IF;
2106 RAISE;
2107
2108 END filter_credit_memo;
2109
2110
2111 END JMF_GTA_SPLIT_TRX_PROC;