[Home] [Help]
PACKAGE BODY: APPS.AR_GTA_SPLIT_TRX_PROC
Source
1 PACKAGE BODY AR_GTA_SPLIT_TRX_PROC AS
2 --$Header: ARGRXTRB.pls 120.8.12020000.3 2013/01/17 03:09:07 jixuhuan ship $
3
4 --+===========================================================================
5 --| Copyright (c) 2002 Oracle Corporation
6 --| Redwood Shores, California, USA
7 --| All rights reserved.
11 --| ARRXTRB.pls
8 --+===========================================================================
9 --|
10 --| FILENAME :
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 ar_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 --| 16-Jun-2009 Yao Zhang fix bug#8605196 ENHANCEMENT FOR GOLDEN TAX ADAPTER R12.1.2
51 --| ER1 Support discount lines
52 --| ER2 Support customer name,address,bank info in Chinese
53 --| 04-08-2009 Yao Zhang fix bug#8756943 TRANSFER AND CONSOLIDATION LOGIC FOR CREDIT MEMO WITH DISCOUNT LINES .
54 --| 28-10-2009 Yao Zhang fix bug#9045187 CREDIT MEMO WITH DISCOUNT TRANSFER AMOUNT LIMIT ISSUE
55 --| 12-Mar-2009 Yao Zhang Fix bug#9398467 LENGTH OF DISCOUNT TAX AMOUNT IN GTA WORKBENCH IS OVER SIZE
56 --| 11-Jan-2010 Qiong Liu changed for bug#10638369 INCLUSIVE TAX UNIT PRICE IS RE-CALCULATED AFTER CONSOLIDATION
57 --| 01-Apr-2011 yoyo for GTA TRX split by unit price exceed max amount limitation
58 --| 13-Apr-2011 yoyo changed for Bug# 12345355, discount value will be set NULL if value is 0
59 --| 15-Apr-2011 yoyo changed for Bug# 12359151, tax value and discount tax value are not right.
60 --| 27-Dec-2012 Jixun changed for Bug#16027677, ORIGNAL CURRENCY AMOUNT DISPLAYED ERROR IN GTA HEADER.
61 --| 10-Jan-2013 Jixun changed for Bug#16077237, GTA WORKBENCH ORIGINAL AMOUNT DISPLAY ERROR WHEN QUANTITY IS DECIMAL.
62 --+===========================================================================
63
64 --=============================================================================
65 -- PROCEDURE NAME:
66 -- log
67 -- TYPE:
68 -- private
69 --
70 -- DESCRIPTION :
71 -- This procedure log message
72 -- PARAMETERS :
73 -- p_level IN VARCHAR2
74 -- p_module IN VARCHAR2
75 -- p_message IN VARCHAR2
76 --
77 -- HISTORY:
78 -- 10-MAY-2005 : Jim.Zheng Create
79 --=============================================================================
80 PROCEDURE log
81 (p_level IN VARCHAR2
82 ,p_module IN VARCHAR2
83 ,p_message IN VARCHAR2)
84 IS
85 BEGIN
86
87 IF(p_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
88 THEN
89 fnd_log.STRING(LOG_LEVEL => p_level
90 ,MODULE => p_module
91 ,MESSAGE => p_message
92 );
93 END IF;
94
95 END;
96
97 --=============================================================================
98 -- PROCEDURE NAME:
99 -- split_Transactions
100 -- TYPE:
101 -- PUBLIC
102 --
103 -- DESCRIPTION :
104 -- This procedure returns slpited tracsation by max_amount, max_lines
105 -- PARAMETERS :
106 -- P_ORG_ID IN org_id
107 -- p_transfer_id IN the transfer rule
108 -- P_GTA_TRX_Tbl IN a trx nested table as input
109 -- x_GTA_TRX_Tbl OUT a trx nestedt tabel as output
110 --
111 -- HISTORY:
112 -- 10-MAY-2005 : Jim.Zheng Create
113 -- 16-Jun-2009 Yao Zhang fix bug 8605196 Modified
114 -- 04-Aug-2009 Yao Zhang fix bug#8756943 Modified
118 -- 13-Apr-2011 Yoyo Cao Fix Bug# 12345355
115 -- 12-Mar-2009 Yao Zhang Fix bug#9398467 Modified
116 -- 11-Jan-2010 Qiong Liu Fix bug#10638369
117 -- 01-Apr-2011 Yoyo Cao GTA split for unit price exceed max amount limitation enhancement
119 -- 15-Apr-2011 yoyo changed for Bug# 12359151, tax value and discount tax value are not right.
120 -- 27-Dec-2012 Jixun changed for Bug#16027677, ORIGNAL CURRENCY AMOUNT DISPLAYED ERROR IN GTA HEADER.
121 -- 10-Jan-2013 Jixun changed for Bug#16077237, GTA WORKBENCH ORIGINAL AMOUNT DISPLAY ERROR WHEN QUANTITY IS DECIMAL.
125 , p_transfer_id IN NUMBER
122 --=============================================================================
123 PROCEDURE Split_Transactions
124 ( P_ORG_ID IN NUMBER
126 , P_GTA_TRX_Tbl IN AR_GTA_TRX_UTIL.TRX_TBL_TYPE
127 , x_GTA_TRX_Tbl OUT NOCOPY AR_GTA_TRX_UTIL.TRX_TBL_TYPE
128 )
129 IS
130 l_procedure_name VARCHAR2(30):='Split_Transactions';
131
132 --parameter for splite max_lines and max_amount;
133 l_sum_of_amount NUMBER := 0;
134 l_lines_number NUMBER;
135 l_processing_row NUMBER; -- a process number when split the trx ( this split action is loop by line )
136 l_accumulated_amount NUMBER; -- amount of the trx
137 l_amount NUMBER;
138 l_original_c_amount NUMBER; --original_currency_amount qiong add for bug 10638369
139 -- added by yoyo, for bug 11829536
140 l_split_amount NUMBER;
141 l_split_tax_amount NUMBER;
142 l_split_discount_amount NUMBER;
143 l_split_distax_amount NUMBER;
144 l_split_currency_amount NUMBER;
145
146
147 l_ori_quantity NUMBER := 0;
148 l_ori_line_amount NUMBER := 0;
149 l_ori_discount_amount NUMBER := 0;
150 l_ori_tax_amount NUMBER := 0;
151 l_ori_discount_taxamount NUMBER := 0;
152 l_ar_trx_line_id ar_gta_trx_lines_all.ar_trx_line_id%TYPE;
153
154 -- added end.
155
156 --get from option table,this four variable is the split codition
157 l_max_amount NUMBER;
158 l_max_line NUMBER;
159 l_sales_list_flag ar_gta_rule_headers_all.Sales_List_Flag%TYPE;
160 l_split_flag ar_gta_system_parameters.Trx_Line_Split_Flag%TYPE;
161
162 l_GTA_TRX AR_GTA_TRX_UTIL.trx_rec_type; -- trx get from input parameter
163 l_gta_trx_succesed AR_GTA_TRX_UTIL.trx_rec_type; -- trx for insert into temp table for report
164 l_gta_trx_init AR_GTA_TRX_UTIL.trx_rec_type; -- trx for init
165
166 -- use when split a trx line
167 l_GTA_Trx_line_old AR_GTA_TRX_UTIL.trx_line_rec_type;
168 l_GTA_TRX_line_new AR_GTA_TRX_UTIL.trx_line_rec_type;
169 l_gta_trx_line_init AR_GTA_TRX_UTIL.trx_line_rec_type;
170
171 -- use when split a new trx
172 l_GTA_Trx_new AR_GTA_TRX_UTIL.trx_rec_type;
173 l_gta_trx_new_succ AR_Gta_Trx_Util.trx_rec_type;
174 l_trx_lines_new AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE := AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
175 l_trx_header_new AR_GTA_TRX_UTIL.TRX_header_rec_TYPE;
176
177
178 -- use by split trx by tax rate and fp registration number
179 l_gta_rate_trx_tbl AR_GTA_TRX_UTIL.TRX_Tbl_TYPE := AR_GTA_TRX_UTIL.TRX_Tbl_TYPE();
180 l_gta_taxreg_trx_tbl AR_GTA_TRX_UTIL.TRX_Tbl_TYPE := AR_GTA_TRX_UTIL.TRX_Tbl_TYPE();
181
182 l_quantity_limit NUMBER;
183
184 l_trx_lines AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE := AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
185
186 l_trx_index NUMBER; -- loop index of l_gta_trx
187 l_trx_rate_index NUMBER; -- loop index of l_gta_rate_trx
188 l_trx_line_index NUMBER; -- loop index of trx lines
189 l_result BOOLEAN;
190
191 l_trx_type ra_cust_trx_types_all.type%TYPE;
192 l_fp_reg_num ar_gta_trx_headers_all.fp_tax_registration_number%TYPE;
193
194 l_trx_group_number NUMBER;
195
196 l_functional_price ar_gta_trx_lines_all.unit_price%TYPE; --25-Apr-2006: Jogen Hu bug 5168852
197
198 --Yao add for bug#8605196 to support discount line
199 l_discount_line_number NUMBER;--discount line number for the transaction
200 l_discount_row NUMBER;--count the discount line which will be printed on VAT invoice
201 l_actual_amount NUMBER;--transaction amount with discount amount
202 l_actual_unit_price NUMBER;
203
204
205 BEGIN
206
207 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
208 THEN
209 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
210 , G_MODULE_PREFIX || l_procedure_name
211 ,'Begin Procedure. ');
212 END IF;
213
214 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
215 THEN
216 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'Begin Split_trx');
217
218
219 END IF;
220 -- init all nested table
224 l_gta_rate_trx_tbl := AR_GTA_TRX_UTIL.TRX_Tbl_TYPE();
221 l_trx_lines_new := AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
222 l_trx_lines := AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
223 l_gta_trx_new.trx_lines := AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
225 x_GTA_TRX_Tbl := AR_GTA_TRX_UTIL.TRX_TBL_TYPE();
226
227 --begin select max amount, max number of lines and sales list flag
228 BEGIN
229 SELECT
230 TRX_LINE_SPLIT_FLAG
231 INTO
232 l_split_flag
233 FROM
234 AR_GTA_SYSTEM_PARAMETERS_all
235 WHERE org_id = p_org_id;
236
237 -- begin log
238 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
239 THEN
240 --fnd_file.put_line(fnd_file.LOG, 'l_split_flag:'||l_split_flag);
241 log(FND_LOG.LEVEL_PROCEDURE
242 , G_MODULE_PREFIX || l_procedure_name
243 , 'l_split_flag:'||l_split_flag);
244 END IF;
245 -- end log
246
247 EXCEPTION
248 -- no data found , raise a data error
249 WHEN no_data_found THEN
250 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
251 THEN
252 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
253 , G_MODULE_PREFIX || l_procedure_name
254 , 'no data found');
255
256 --AR_GTA_SYS_CONFIG_MISSING
257 fnd_message.set_name('AR', 'AR_GTA_SYS_CONFIG_MISSING');
258 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
259 , G_MODULE_PREFIX || l_procedure_name
260 , fnd_message.get());
261
262 END IF;
263 RAISE;
264 RETURN;
265 END;
266
267 BEGIN
268 SELECT
269 sales_list_flag
270 INTO
271 l_sales_list_flag
272 FROM
273 AR_GTA_RULE_HEADERS_All
274 WHERE org_id = p_org_id
275 AND rule_header_id = p_transfer_id;
276 EXCEPTION
277 -- no data found , raise a data error
278 WHEN no_data_found THEN
279 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
280 THEN
281 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
282 , G_MODULE_PREFIX || l_procedure_name
283 , 'no data found');
284
285 --AR_GTA_SYS_CONFIG_MISSING
286 fnd_message.set_name('AR', 'AR_GTA_SYS_CONFIG_MISSING');
287 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
288 , G_MODULE_PREFIX || l_procedure_name
289 , fnd_message.get());
290
291 END IF;
292 RAISE;
293 RETURN;
294 END;
295
296
297 -- begin log
298 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
299 THEN
300 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'l_sales_list_flag:'||l_sales_list_flag);
301 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'begin split_trx_loop......');
302 END IF;
303 -- end log
304
305
306
307 -- begin split trx
308 l_trx_index := P_GTA_TRX_Tbl.FIRST;
309
310 -- begin log
311 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
312 THEN
313 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'l_trx_index......'||l_trx_index);
314 END IF;
315 -- end log
316
317
318 WHILE l_trx_index IS NOT NULL
319 LOOP
320
321 l_gta_trx_succesed := P_GTA_TRX_Tbl(l_trx_index);
322
323
324 l_GTA_TRx := P_GTA_TRX_tbl(l_trx_index);
325
326 -- init trx group number
327 l_trx_group_number := 1;
328
332 -- end split by tax registration number
329 -- begin split by tax registration number
330 split_trx_by_taxreg_number(p_gta_trx => l_GTA_TRX
331 , x_TRX_Tbl => l_gta_taxreg_trx_tbl);
333
334 -- begin log
335 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
336 THEN
337 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'End Split_trx_by_taxreg_number:'||l_gta_taxreg_trx_tbl.COUNT);
338 END IF;
339 -- end log
340
341 -- begin split by tax rate
342 split_trx_by_rate(p_gta_tbl => l_gta_taxreg_trx_tbl
343 ,x_trx_tbl => l_gta_rate_trx_tbl
344 );
345 -- end split by tax rate
346
347
348 -- begin log
349 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
350 THEN
351 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'End split_trx_by_rate:'||l_gta_rate_trx_tbl.COUNT);
352 END IF;
353 -- end log
354
355 -- loop by a new trx nested table which split by rate
356 l_trx_rate_index := l_gta_rate_trx_tbl.FIRST;
357 WHILE l_trx_rate_index IS NOT NULL
358 LOOP
359 -- use l_gta_trx again
360 -- now we get a new trx of single rate
361 l_GTA_TRX := l_gta_rate_trx_tbl(l_trx_rate_index);
362
363 -- get trx type
364 get_trx_type(p_org_id => p_org_id
365 , p_gta_trx => l_GTA_TRX
366 , x_trx_type => l_trx_type);
367
368
369 -- begin log
370 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
371 THEN
372 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'l_trx_rate_index:'||l_trx_rate_index);
373 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'End get_trx_type:'||l_trx_type);
374 END IF;
375 -- end log
376
377 -- IS CM ?\
378 /* IF l_trx_type = 'CM'
379 THEN
380 -- judge the CM can fit the max amount and the max line counts
381 judge_cm_limit(p_gta_trx => l_gta_trx
382 , p_org_id => p_org_id
383 , x_result => l_result);
384 IF l_result
385 THEN
386 -- add this l_gta_trx into the result table
387 l_gta_trx.trx_header.group_number := l_trx_group_number;
388 l_trx_group_number := l_trx_group_number + 1;
389 process_before_split( x_gta_trx_rec => l_GTA_TRX
390 );
391 x_GTA_TRX_Tbl.EXTEND;
392 x_GTA_TRX_Tbl(x_GTA_TRX_Tbl.COUNT) := l_GTA_Trx;
393 ELSE
394 NULL;
395 -- throw exception
396 END IF;
397
398 ELSE --l_trx_type = 'CM'*/
399 -- get max_amount and max_line_count
400 --Yao Zhang add for credit memo with discount lines exceed max line number should be transfered
401 --without split Fix bug#8756943
402 IF l_trx_type = 'CM'
403 THEN
404 l_gta_trx.trx_header.group_number := l_trx_group_number;
405 l_trx_group_number := l_trx_group_number + 1;
406 process_before_split( x_gta_trx_rec => l_GTA_TRX
407 );
408 x_GTA_TRX_Tbl.EXTEND;
409 x_GTA_TRX_Tbl(x_GTA_TRX_Tbl.COUNT) := l_GTA_Trx;
410 ELSE
411 --Yao Zhang add end
412 get_max_amount_line(p_gta_trx => l_gta_trx
413 , p_org_id => P_ORG_ID
414 , x_max_amount => l_max_amount
415 , x_max_line => l_max_line
416 );
417
418 -- begin log
419 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
420 THEN
421 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'get_max_amount_line');
422 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'l_max_amount:'||l_max_amount);
423 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'l_max_line:'||l_max_line);
424 END IF;
425 -- end log
426 --The following code is Modifie by Yao for bug#8605196 to support discount line
427 -- get lines number and amount
428 l_trx_line_index := l_gta_trx.trx_lines.FIRST;
429 WHILE l_trx_line_index IS NOT NULL
430 LOOP
431 l_sum_of_amount := l_sum_of_amount + l_GTA_TRX.trx_lines(l_trx_line_index).amount
432 +nvl(l_GTA_TRX.trx_lines(l_trx_line_index).discount_amount,0);
433 IF l_GTA_TRX.trx_lines(l_trx_line_index).discount_flag='1'
434 Then
435 l_discount_line_number:=nvl(l_discount_line_number,0)+1;
436 END IF;/*l_GTA_TRX.trx_lines(l_trx_line_index).discount_flag='1'*/
437 --Yao Zhang add end to support discount line number
438 l_trx_line_index := l_gta_trx.trx_lines.NEXT(l_trx_line_index);
439 END LOOP;
440 l_lines_number := l_GTA_TRX.trx_lines.COUNT+nvl(l_discount_line_number,0);--Modified by Yao for bug#8605196
441 -- if the trx is regular and don't need split, push it into rusult
442 IF l_sum_of_amount<l_max_amount AND (l_lines_number<l_max_line OR l_sales_list_flag='Y' )
443 THEN
444 -- begin log
445 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
446 THEN
447 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'' )');
448 END IF;
449 -- end log
450
451 -- insert trx into result table
452 l_gta_trx.trx_header.group_number := l_trx_group_number;
453 l_trx_group_number := l_trx_group_number + 1;
454 process_before_split( x_gta_trx_rec => l_GTA_TRX
455 );
456 x_GTA_TRX_Tbl.EXTEND;
457 x_GTA_TRX_Tbl(x_GTA_TRX_Tbl.COUNT) := l_GTA_Trx;
458 ELSE /*l_sum_of_amount<l_max_amount AND (l_lines_number<l_max_line OR l_sales_list_flag='Y' )*/
459
460 -- init the l_processing_row and l_accumulated_amount
461 l_processing_row:=1;
462 l_accumulated_amount:=0;
463 l_discount_row:=0;--Yao add for bug#8605196
464 l_ar_trx_line_id := 0; -- added by yoyo
465
466 WHILE (l_processing_row<=l_GTA_TRX.trx_lines.COUNT)
467 LOOP
468 -- init l_gta_trx_line_old
469 l_gta_trx_line_old := l_gta_trx_line_init;
470 l_gta_trx_new := l_gta_trx_init;
471 l_gta_trx_new_succ := l_gta_trx_init;
472 l_trx_lines_new := AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
473 l_GTA_TRX_line_old :=l_GTA_TRX.trx_lines(l_processing_row);
474 l_amount:=l_GTA_TRX_line_old.amount;
475 l_original_c_amount:=l_GTA_TRX_line_old.original_currency_amount;--add by Qion.liu for bug 10638369
479 THEN
476 l_actual_amount:=l_GTA_TRX_line_old.amount+nvl(l_GTA_TRX_line_old.discount_amount,0);
477 l_actual_unit_price:=(l_GTA_TRX_line_old.amount+nvl(l_GTA_TRX_line_old.discount_amount,0))/l_GTA_TRX_line_old.quantity;
478 IF l_GTA_TRX_line_old.discount_flag='1'
480 l_discount_row:=l_discount_row+1;
481 END IF;/*l_GTA_TRX_line_old.discount_flag='1'*/
482 -- 25-Apr-2006: Jogen Hu bug 5168852
483 --the following code is changed by Yao for bug 9398467
484 -- 13-Jul-2009: Allen Yang modified for bug 8619860
485 --------------------------------------------------------------------------
486 --l_functional_price:=l_GTA_TRX_line_old.amount/l_GTA_TRX_line_old.quantity;
487 --l_functional_price:= round(l_GTA_TRX_line_old.amount/l_GTA_TRX_line_old.quantity, 2);
488 l_functional_price:= round(l_GTA_TRX_line_old.amount/l_GTA_TRX_line_old.quantity, 6);
489 --------------------------------------------------------------------------
490
491 -- begin log
492 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
493 THEN
494 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name
495 , 'l_functional_price:'||l_functional_price);
496 END IF;
497 -- end log
498 -- 25-Apr-2006: Jogen Hu bug 5168852
499
500 l_accumulated_amount := l_accumulated_amount + nvl( l_actual_amount, 0);
501 IF l_accumulated_amount <= l_max_amount AND l_processing_row+l_discount_row </*=*/ l_max_line
502 THEN
503
504 -- begin log
505 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
506 THEN
507 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'l_accumulated_amount <= l_max_amount AND l_processing_row </*=*/ l_max_line');
508 END IF;
509 -- end log
510
511 IF l_processing_row = l_GTA_TRX.trx_lines.COUNT
512 THEN
513 -- now this can due to the same condition at
514 l_gta_trx.trx_header.group_number := l_trx_group_number;
515 l_trx_group_number := l_trx_group_number + 1;
516 process_before_split(x_gta_trx_rec => l_GTA_TRX
517 );
518 x_GTA_TRX_Tbl.EXTEND;
519 x_GTA_TRX_Tbl(x_GTA_TRX_Tbl.COUNT) := l_GTA_TRX;
520
521 -- else goto end loop and due to next line
522 END IF; -- end if l_processing_row = l_GTA_TRX.trx_lines.COUNT
523
524 -- > line < amount and sales 'N', split by line
525 ELSIF l_processing_row+l_discount_row >= l_max_line AND l_sales_list_flag = 'Y' AND l_accumulated_amount <= l_max_amount
526 THEN
527
528 -- begin log
529 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
530 THEN
531 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');
532 END IF;
533 -- end log
534
535 IF l_processing_row = l_GTA_TRX.trx_lines.COUNT
536 THEN
537 l_gta_trx.trx_header.group_number := l_trx_group_number;
538 l_trx_group_number := l_trx_group_number + 1;
539 process_before_split(x_gta_trx_rec => l_GTA_TRX
540 );
541 x_GTA_TRX_Tbl.EXTEND;
542 x_GTA_TRX_Tbl(x_GTA_TRX_Tbl.COUNT) := l_GTA_TRX;
543
544 -- ELSE goto end loop and due to next line
545 END IF;/*end if l_processing_row = l_GTA_TRX.trx_lines.COUNT*/
546
547 ELSIF l_processing_row+l_discount_row = l_max_line AND l_sales_list_flag = 'N' AND l_accumulated_amount <= l_max_amount
548 THEN
549 IF l_processing_row = l_GTA_TRX.trx_lines.COUNT
550 THEN
551 l_gta_trx.trx_header.group_number := l_trx_group_number;
552 l_trx_group_number := l_trx_group_number + 1;
553 process_before_split(x_gta_trx_rec => l_GTA_TRX
554 );
555
556 x_GTA_TRX_Tbl.EXTEND;
557 x_GTA_TRX_Tbl(x_GTA_TRX_Tbl.COUNT) := l_GTA_TRX;
558
559
560
561 ELSE /*l_processing_row < l_GTA_TRX.trx_lines.COUNT */
562
563 split_nested_table(p_trx_lines => l_gta_trx.trx_lines
564 , p_split_flag => l_processing_row
565 , x_first_lines => l_trx_lines_new
566 , x_last_lines => l_trx_lines
567 );
568
569 -- copy trx header
570 copy_header(p_GTA_TRX_Header_Rec => l_GTA_TRX.trx_header
571 , x_GTA_TRX_Header_Rec => l_trx_header_new
572 );
573
574
575 -- get new trx number
576 l_GTA_Trx_new.trx_header := l_GTA_TRX.trx_header;
577 l_gta_trx_new.trx_lines := l_trx_lines_new;
578
579 -- add new trx to result tbl
580 l_GTA_Trx_new.trx_header.group_number := l_trx_group_number;
581 l_trx_group_number := l_trx_group_number + 1;
582 process_before_split(x_gta_trx_rec => l_GTA_TRX_new
583 );
584
585 x_GTA_TRX_Tbl.EXTEND;
586 x_gta_trx_tbl(x_gta_trx_tbl.COUNT) := l_gta_trx_new;
587 --add_succ_to_temp(l_GTA_TRX);
588
589 -- update the header , replace the l_gta_trx header by new header
590 l_GTA_TRX.trx_header := l_trx_header_new;
591
592 -- update the old trx.trx_lines
593 l_GTA_TRX.trx_lines := l_trx_lines;
594
595 -- init l_processing_row and l_accumulateed_amount
596 l_processing_row := 0;
597 l_accumulated_amount := 0;
598 l_discount_row:=0;
599
600 END IF; /*l_processing_row < l_GTA_TRX.trx_lines.COUNT */
601 --The following code is added by Yao Zhang for bug#8605196 to support discount line
602 ELSIF l_processing_row+l_discount_row = l_max_line+1 AND l_sales_list_flag = 'N' AND l_accumulated_amount <= l_max_amount
603 THEN
604 /*l_processing_row < l_GTA_TRX.trx_lines.COUNT */
605
606 split_nested_table(p_trx_lines => l_gta_trx.trx_lines
607 , p_split_flag => l_processing_row-1
608 , x_first_lines => l_trx_lines_new
609 , x_last_lines => l_trx_lines
610 );
611
612 -- copy trx header
613 copy_header(p_GTA_TRX_Header_Rec => l_GTA_TRX.trx_header
614 , x_GTA_TRX_Header_Rec => l_trx_header_new
615 );
616
617
618 -- get new trx number
619 l_GTA_Trx_new.trx_header := l_GTA_TRX.trx_header;
620 l_gta_trx_new.trx_lines := l_trx_lines_new;
621
622 -- add new trx to result tbl
623 l_GTA_Trx_new.trx_header.group_number := l_trx_group_number;
624 l_trx_group_number := l_trx_group_number + 1;
625 process_before_split(x_gta_trx_rec => l_GTA_TRX_new
626 );
627
628 x_GTA_TRX_Tbl.EXTEND;
629 x_gta_trx_tbl(x_gta_trx_tbl.COUNT) := l_gta_trx_new;
630 --add_succ_to_temp(l_GTA_TRX);
631 -- update the header , replace the l_gta_trx header by new header
632 l_GTA_TRX.trx_header := l_trx_header_new;
633 -- update the old trx.trx_lines
634 l_GTA_TRX.trx_lines := l_trx_lines;
635 -- init l_processing_row and l_accumulateed_amount
636 l_processing_row := 0;
637 l_accumulated_amount := 0;
638 l_discount_row:=0;
639 --The above code is added by Yao Zhang for bug#8605196 to support discount line
640 /*This condition include the l_process_number > max_line, l_procecss_number = max_line, l_process_number < max_line,
641 * due to this three condition the process is same, split by amount.
642 */
643 ELSIF l_accumulated_amount > l_max_amount
644 THEN
645 -- begin log
646 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
647 THEN
648 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'l_accumulated_amount > l_max_amount');
649 END IF;
650 -- end log
651
652 l_trx_lines := l_GTA_TRX.trx_lines;
653
654
655
656
657
658 -- Following code is revised by yoyo for enhancement when the unit price exceed
659 -- Changed unit price filter function in AR_GTA_ARTRX_PROC, line 3485 - 3511
660 -- 2011-03-22, for bug 11829536
661 l_split_amount := 0;
662 l_split_tax_amount := 0;
663 l_split_discount_amount := 0;
664 l_split_distax_amount := 0;
665 l_split_currency_amount := 0;
666
667 IF l_processing_row > 1
668 THEN
669 -- Split previous lines for new VAT invoice at first
670 split_nested_table(p_trx_lines => l_gta_trx.trx_lines,
671 p_split_flag => l_processing_row - 1,
672 x_first_lines => l_trx_lines_new,
673 x_last_lines => l_trx_lines
674 );
675 END IF; /* l_processing_row > 1 */
676
677 -- check option and rows to split the current line, else don't split it.
678 IF (l_split_flag = 'Y') OR (l_processing_row = 1)
679 THEN
680 -- Enforce to split the line by different amount
681 -- No enforce to split the single line, but need to split for special lines that the line amount is more than max amount
682
683 l_gta_trx_line_old := l_gta_trx.trx_lines(l_processing_row);
684 l_gta_trx_line_new := l_gta_trx_line_old;
685
686 -- When the unit price exceed max amount, the split quantity will be less than 1, but keep the amount is integer
687 -- The previous VAT invoice includes one or more lines, then just calculate remaining amount within max amount, the quantity will be less than 1
688 -- Check if the unit price is more than max amount
689 IF l_actual_unit_price > l_max_amount
690 THEN
691 IF l_ar_trx_line_id <> l_gta_trx_line_old.ar_trx_line_id
692 THEN
693 l_ar_trx_line_id := nvl(l_gta_trx_line_old.ar_trx_line_id, 0);
694 l_ori_quantity := nvl(l_gta_trx_line_old.quantity, 0);
695 l_ori_line_amount := nvl(l_gta_trx_line_old.amount, 0);
696 l_ori_discount_amount := nvl(l_gta_trx_line_old.discount_amount, 0);
697 l_ori_tax_amount := nvl(l_gta_trx_line_old.tax_amount, 0);
698 l_ori_discount_taxamount := nvl(l_gta_trx_line_old.discount_tax_amount, 0);
699 END IF;
700
701 /*
705 fnd_file.PUT_LINE(fnd_file.LOG,'l_ori_discount_amount='||l_ori_discount_amount);
702 fnd_file.PUT_LINE(fnd_file.LOG,'l_ar_trx_line_id='||l_gta_trx_line_old.ar_trx_line_id);
703 fnd_file.PUT_LINE(fnd_file.LOG,'l_ori_quantity='||l_ori_quantity);
704 fnd_file.PUT_LINE(fnd_file.LOG,'l_ori_line_amount='||l_ori_line_amount);
706 fnd_file.PUT_LINE(fnd_file.LOG,'l_ori_tax_amount='||l_ori_tax_amount);
707 fnd_file.PUT_LINE(fnd_file.LOG,'l_ori_discount_taxamount='||l_ori_discount_taxamount);
708 */
709 -- Quantity value must be decimal
710 l_split_amount := floor(l_max_amount - (l_accumulated_amount - l_actual_amount)); -- actual split amount
711
712 l_split_discount_amount := (l_split_amount / (l_ori_line_amount + l_ori_discount_amount)) * l_ori_discount_amount;
713 -- new line discount amount
714 IF l_split_discount_amount < 0
715 THEN
716 l_split_discount_amount := -1 * floor(abs(l_split_discount_amount));
717 ELSE
718 l_split_discount_amount := floor(l_split_discount_amount);
719 END IF;
720
721 -- functional amount to be split
722 l_split_amount := l_split_amount - l_split_discount_amount;
723
724 l_quantity_limit := round((l_split_amount / l_ori_line_amount) * l_ori_quantity, 8); -- 0.xxxxxxxx, remaining multi digits for the precision
725 -- 2011-04-15, Fixed by yoyo for Bug# 12359151
726 --l_split_tax_amount := floor((l_split_amount / l_ori_line_amount) * l_ori_tax_amount); -- new line tax amount
727 l_split_tax_amount := round(l_split_amount * l_gta_trx_line_old.tax_rate, 2);
728
729 -- new line discount tax amount
730 -- 2011-04-15, Fixed by yoyo for Bug# 12359151
731 l_split_distax_amount := round(l_split_discount_amount * l_gta_trx_line_old.tax_rate, 2);
732 /*
733 l_split_distax_amount := (l_split_amount / l_ori_line_amount) * l_ori_discount_taxamount;
734 IF l_split_distax_amount < 0
735 THEN
736 l_split_distax_amount := -1 * floor(abs(l_split_distax_amount));
737 ELSE
738 l_split_distax_amount := floor(l_split_distax_amount);
739 END IF;
740 */
741
742 --modified by Jixun for bug#16077237 begin
743 --l_split_currency_amount := floor((l_split_amount / l_ori_line_amount) * l_gta_trx_line_new.unit_price);
744 l_split_currency_amount := l_split_amount;
745 --modified by Jixun for bug#16077237 end
746
747 --fnd_file.PUT_LINE(fnd_file.LOG,'l_split_amount='||l_split_amount);
748 --fnd_file.PUT_LINE(fnd_file.LOG,'l_split_discount_amount='||l_split_discount_amount);
749 --fnd_file.PUT_LINE(fnd_file.LOG,'l_actual_unit_price='||l_actual_unit_price);
750 --fnd_file.PUT_LINE(fnd_file.LOG,'l_functional_price='||l_functional_price);
751 --fnd_file.PUT_LINE(fnd_file.LOG,'l_quantity_limit='||l_quantity_limit);
752 ELSE
753 l_split_amount := l_max_amount - (l_accumulated_amount - l_actual_amount); -- split actual amount
754 l_quantity_limit := floor(l_split_amount / l_actual_unit_price); -- integer
755
756 l_split_amount := round(l_quantity_limit * l_functional_price, 2); -- new line amount
757 l_split_tax_amount := round(l_split_amount * l_gta_trx_line_new.tax_rate, 2); -- new line tax amount
758 l_split_discount_amount := round((l_quantity_limit / l_gta_trx_line_old.quantity) * nvl(l_gta_trx_line_old.discount_amount, 0), 2); -- new line discount amount
759 -- 2011-04-15, Fixed by yoyo for Bug# 12359151
763 END IF; /* l_actual_unit_price > l_max_amount */
760 --l_split_distax_amount := round((l_quantity_limit / l_gta_trx_line_old.quantity) * nvl(l_gta_trx_line_old.discount_tax_amount, 0), 2); -- new line discount tax amount
761 l_split_distax_amount := round(l_split_discount_amount * l_gta_trx_line_old.tax_rate, 2);
762 l_split_currency_amount := round(l_quantity_limit * l_gta_trx_line_new.unit_price, 2);
764
765 IF l_quantity_limit > 0
766 THEN
767 -- final split amount
768 -- l_split_amount = round(l_quantity_limit * l_functional_price, 2);
769
770 l_gta_trx_line_new.quantity := l_quantity_limit;
771 l_gta_trx_line_new.amount := l_split_amount;
772 l_gta_trx_line_new.tax_amount := l_split_tax_amount;
773 -- Added by yoyo, 2011-04-13 for Bug# 12345355
774 IF l_split_discount_amount <> 0
775 THEN
776 l_gta_trx_line_new.discount_amount := l_split_discount_amount;
777 END IF;
778 IF l_split_distax_amount <> 0
779 THEN
780 l_gta_trx_line_new.discount_tax_amount := l_split_distax_amount;
781 END IF;
782 -- end
783
784 --modified by Jixun for bug#16027677 begin
785 --l_gta_trx_line_new.original_currency_amount := l_split_currency_amount;
786 l_gta_trx_line_new.original_currency_amount := round(l_split_currency_amount/nvl(l_gta_trx.trx_header.conversion_rate,1), 2);
787 --modified by Jixun for bug#16027677 end
788
789 SELECT ar_gta_trx_lines_all_s.NEXTVAL INTO l_gta_trx_line_new.gta_trx_line_id FROM dual;
790
791 -- add new line to VAT invoice
792 l_trx_lines_new.EXTEND;
793 l_trx_lines_new(l_trx_lines_new.COUNT) := l_gta_trx_line_new;
794 END IF;
795
796 l_gta_trx_line_old.quantity := l_gta_trx_line_old.quantity - l_quantity_limit;
797 l_gta_trx_line_old.amount := l_amount - l_split_amount;
798 l_gta_trx_line_old.tax_amount := l_gta_trx_line_old.tax_amount - l_split_tax_amount;
799 -- Added by yoyo, 2011-04-13 for Bug# 12345355
800 IF l_split_discount_amount <> 0
801 THEN
802 l_gta_trx_line_old.discount_amount := nvl(l_gta_trx_line_old.discount_amount, 0) - l_split_discount_amount;
803 END IF;
804 IF l_split_distax_amount <> 0
805 THEN
806 l_gta_trx_line_old.discount_tax_amount := nvl(l_gta_trx_line_old.discount_tax_amount, 0) - l_split_distax_amount;
807 END IF;
808 -- end
809
810 --modified by Jixun for bug#16027677 begin
811 --l_gta_trx_line_old.original_currency_amount := l_amount - l_split_currency_amount;
812 l_gta_trx_line_old.original_currency_amount := round((l_amount - l_split_currency_amount)/nvl(l_gta_trx.trx_header.conversion_rate,1), 2);
813 --modified by Jixun for bug#16027677 end
814
815 -- Update old line to be split a part to previous VAT invoice
816 l_trx_lines(1) := l_gta_trx_line_old;
817
818 END IF; /* (l_split_flag = 'Y') OR (l_processing_row = 1) */
819
820 -- copy trx header for next VAT invoice
821 copy_header(p_gta_trx_header_rec => l_gta_trx.trx_header,
822 x_gta_trx_header_rec => l_trx_header_new
823 );
824
825 -- get new trx number
826 l_gta_trx_new.trx_header := l_gta_trx.trx_header;
827 l_gta_trx_new.trx_header.group_number := l_trx_group_number;
828 l_gta_trx_new.trx_lines := l_trx_lines_new;
829 process_before_split(x_gta_trx_rec => l_gta_trx_new);
830
831 -- add new trx to result tbl
832 x_gta_trx_tbl.EXTEND;
833 x_gta_trx_tbl(x_gta_trx_tbl.COUNT) := l_gta_trx_new;
834
835 l_trx_group_number := l_trx_group_number + 1;
836
837 -- update the header of old trx
838 l_gta_trx.trx_header := l_trx_header_new;
839 -- update the lines of old trx
840 l_gta_trx.trx_lines := l_trx_lines;
841
842 -- init the l_processing_row and l_accumulated_amount
843 l_processing_row := 0;
844 l_discount_row := 0;
845 l_accumulated_amount := 0;
846
847 -- end by yoyo. 2011-03-22 for bug 11829536
848
849
850 -- original code is marked by yoyo--
851 /*
852 IF l_split_flag = 'Y'
853 THEN
854 IF l_gta_trx.trx_lines.COUNT = 1
855 THEN
856 l_GTA_Trx_line_old := l_GTA_TRX.trx_lines(l_processing_row);
857 l_GTA_TRX_line_new := l_GTA_Trx_line_old;
858 -- 25-Apr-2006: Jogen Hu bug 5168852
859 --l_quantity_limit:=--
860 -- floor((l_max_amount - l_accumulated_amount + l_GTA_Trx_line_old.amount)/l_GTA_Trx_line_old.unit_price);--
861 l_quantity_limit:=floor((l_max_amount-l_accumulated_amount+l_actual_amount)/l_actual_unit_price );
862 IF l_quantity_limit > 0
863 THEN
864 l_GTA_TRX_line_new.quantity := l_quantity_limit;
865
866 -- 25-Apr-2006: Jogen Hu bug 5168852
867 --l_GTA_TRX_line_new.amount := l_quantity_limit * l_GTA_TRX_line_new.unit_price;
871 --The following code is changed by Yao for bug 9398467
868 --l_GTA_TRX_line_new.amount := l_quantity_limit * l_functional_price;
869 --Yao modified for bug 9369455
870 l_GTA_TRX_line_new.amount := round(l_quantity_limit * l_functional_price,2);
872
873 --l_gta_trx_line_new.discount_amount:=--
874 --(l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_amount;--Yao add for bug#8605196--
875 --l_gta_trx_line_new.discount_tax_amount:=--
876 --(l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_tax_amount;--Yao add for bug#8605196--
877
878 l_gta_trx_line_new.discount_amount:=round(
879 (l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_amount,2);
880 l_gta_trx_line_new.discount_tax_amount:=round(
881 (l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_tax_amount,2);
882 --Yao modified end for bug 9398467
883
884 -- 25-Apr-2006: Jogen Hu bug 5168852
885 -- it should be change after ebtax!!!
886 -- 25-Apr-2006: Jogen Hu bug 5168852
887 --l_GTA_TRX_line_new.tax_amount := l_GTA_TRX_line_new.tax_rate*(l_quantity_limit * l_GTA_TRX_line_new.unit_price);
888 l_GTA_TRX_line_new.tax_amount := round(l_GTA_TRX_line_new.tax_rate*(l_quantity_limit * l_functional_price),2);
889 -- 25-Apr-2006: Jogen Hu bug 5168852
890 --l_gta_trx_line_new.original_currency_amount := l_quantity_limit * l_GTA_TRX_line_new.unit_price;
891 l_gta_trx_line_new.original_currency_amount:=round(l_original_c_amount*(l_quantity_limit/l_GTA_TRX_line_old.quantity),2);
892 --qiong changed for bug 10638369
893
894 SELECT
895 AR_GTA_TRX_LINES_ALL_S.NEXTVAL
896 INTO
897 l_GTA_TRX_line_new.GTA_TRX_LINE_ID
898 FROM
899 dual;
900
901 --l_GTA_TRX_line_new.GTA_TRX_LINE_ID := AR_GTA_TRX_LINES_ALL_S.NEXTVAL;
902 l_trx_lines_new.EXTEND;
903 l_trx_lines_new(l_trx_lines_new.COUNT) := l_GTA_TRX_line_new;
904 -- ELSE l_quantity_limit =0 , then the old line is no change and new line is not needed
905 END IF;--l_quantity_limit > 0--
906
907
908
909 -- 25-Apr-2006: Jogen Hu bug 5168852
910 --l_GTA_TRX_line_old.amount := l_amount - l_quantity_limit*l_GTA_Trx_line_old.unit_price;
911 l_GTA_TRX_line_old.amount:=
912 l_amount - round(l_quantity_limit*l_functional_price,2);--round for bug
913 --the following code is changed by Yao for bug#9398467
914
915 --Yao add for bug#8605196
916 --l_gta_trx_line_old.discount_amount:=--
917 -- l_GTA_TRX_line_old.discount_amount-(l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_amount;--
918 --Yao add for bug#8605196
919 --l_gta_trx_line_old.discount_tax_amount:=--
920 -- l_GTA_TRX_line_old.discount_tax_amount-(l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_tax_amount;--
921
922 l_gta_trx_line_old.discount_amount:=l_GTA_TRX_line_old.discount_amount-
923 round((l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_amount,2);
927 --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);
924 l_gta_trx_line_old.discount_tax_amount:=l_GTA_TRX_line_old.discount_tax_amount-
925 round((l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_tax_amount,2);
926 --Yao changed end for bug#9398467
928 l_GTA_Trx_line_old.tax_amount := round(l_GTA_Trx_line_old.tax_rate*(l_amount - l_quantity_limit*l_functional_price),2);
929 -- 25-Apr-2006: Jogen Hu bug 5168852
930 --l_GTA_Trx_line_old.original_currency_amount := l_amount - l_quantity_limit*l_GTA_Trx_line_old.unit_price;
931 l_gta_trx_line_old.original_currency_amount:=l_original_c_amount-round(l_original_c_amount*(l_quantity_limit/l_GTA_TRX_line_old.quantity),2);
932 --qiong changed for bug 10638369
933
934 l_GTA_TRX_line_old.quantity := l_GTA_Trx_line_old.quantity - l_quantity_limit;
935 l_GTA_Trx_line_old.original_currency_amount := l_amount - l_quantity_limit*l_GTA_Trx_line_old.unit_price;
936 -- insert old line to the head of l_trx_lines
940 , x_GTA_TRX_Header_Rec => l_trx_header_new
937 l_trx_lines(1) := l_GTA_Trx_line_old;
938 -- new trx
939 copy_header(p_GTA_TRX_Header_Rec => l_GTA_TRX.trx_header
941 );
942
943 l_GTA_Trx_new.trx_header := l_GTA_TRX.trx_header;
944 l_GTA_Trx_new.trx_lines := l_trx_lines_new;
945
946 -- add the new trx to result table
947 l_gta_trx_new.trx_header.group_number := l_trx_group_number;
948 l_trx_group_number := l_trx_group_number + 1;
949 process_before_split(x_gta_trx_rec => l_GTA_TRX_new
950 );
951 x_GTA_TRX_Tbl.EXTEND;
952 x_GTA_TRX_Tbl(x_GTA_TRX_Tbl.COUNT) := l_GTA_Trx_new;
953 --add_succ_to_temp(l_GTA_TRX);
954 -- update the header of old trx
955 l_gta_trx.trx_header := l_trx_header_new;
956 -- update the lines of old trx
957 l_GTA_TRX.trx_lines := l_trx_lines;
958
959 -- init the l_processing_row and l_accumulated_amount
960 l_processing_row := 0;
961 l_accumulated_amount := 0;
962 l_discount_row:=0;--Yao add for bug#8605196
963
964 ELSE --l_gta_trx.trx_lines.COUNT = 1--
965
966 -- split trx lines exclude the last line
967 split_nested_table(p_trx_lines => l_GTA_TRX.trx_lines
968 , p_split_flag => l_processing_row - 1
969 , x_first_lines => l_trx_lines_new
970 , x_last_lines => l_trx_lines
971 );
972
973 -- split line to new line and old line
974 l_GTA_Trx_line_old := l_GTA_TRX.trx_lines(l_processing_row);
975 l_GTA_TRX_line_new := l_GTA_Trx_line_old;
976
977 -- 25-Apr-2006: Jogen Hu bug 5168852
978 --l_quantity_limit:=--
979 -- floor((l_max_amount - l_accumulated_amount + l_GTA_Trx_line_old.amount)/l_GTA_Trx_line_old.unit_price);--
980
981 l_quantity_limit:=floor((l_max_amount-l_accumulated_amount+l_actual_amount)/l_actual_unit_price ) ;
982 -- 25-Apr-2006: Jogen Hu bug 5168852
983
984 IF l_quantity_limit > 0
985 THEN
986 l_GTA_TRX_line_new.quantity := l_quantity_limit;
987
988 -- 25-Apr-2006: Jogen Hu bug 5168852
989 --l_GTA_TRX_line_new.amount := l_quantity_limit * l_GTA_TRX_line_new.unit_price;
990 --l_GTA_TRX_line_new.amount := l_quantity_limit * l_functional_price;
991 --Yao modified for bug 9369455
992 l_GTA_TRX_line_new.amount := round(l_quantity_limit * l_functional_price,2);
993 --the following code is changed by Yao for bug 9398467
994 --Yao add for bug#8605196
995 --l_gta_trx_line_new.discount_amount:=--
996 --(l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_amount;--Yao add for bug#8605196--
997 --l_gta_trx_line_new.discount_tax_amount:=--
998 --(l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_tax_amount;--Yao add for bug#8605196--
999
1000 l_gta_trx_line_new.discount_amount:=round(
1001 (l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_amount,2);
1002 l_gta_trx_line_new.discount_tax_amount:=round(
1003 (l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_tax_amount,2);
1004 --Yao modified end for bug 9398467
1005
1006 --l_GTA_TRX_line_new.tax_amount := l_GTA_TRX_line_new.tax_rate * (l_quantity_limit * l_GTA_TRX_line_new.unit_price);
1007 l_GTA_TRX_line_new.tax_amount := round(l_GTA_TRX_line_new.tax_rate * (l_quantity_limit * l_functional_price),2);
1008 -- 25-Apr-2006: Jogen Hu bug 5168852
1009 --l_GTA_TRX_line_new.original_currency_amount := l_quantity_limit * l_GTA_TRX_line_new.unit_price;
1010 l_gta_trx_line_new.original_currency_amount :=round(l_original_c_amount*(l_quantity_limit/l_GTA_TRX_line_old.quantity),2);
1011 --qiong changed for bug 10638369
1012
1013 SELECT
1014 AR_GTA_TRX_LINES_ALL_S.NEXTVAL
1015 INTO
1016 l_GTA_TRX_line_new.GTA_TRX_LINE_ID
1017 FROM
1018 dual;
1019 --l_GTA_TRX_line_new.GTA_TRX_LINE_ID := AR_GTA_TRX_LINES_ALL_S.NEXTVAL;
1020 l_trx_lines_new.EXTEND;
1021 l_trx_lines_new(l_trx_lines_new.COUNT) := l_GTA_TRX_line_new;
1022 -- ELSE l_quantity_limit =0 , then the old line is no change and new line is not needed
1023 END IF;--l_quantity_limit > 0--
1024 -- 25-Apr-2006: Jogen Hu bug 5168852
1025 --l_GTA_TRX_line_old.amount:=l_amount - l_quantity_limit*l_GTA_Trx_line_old.unit_price;
1026 l_GTA_TRX_line_old.amount:=l_amount - round(l_quantity_limit*l_functional_price,2);--round for bug 9369455
1027
1028 --the following code is changed by Yao for bug#9398467
1029 --Yao add for bug#8605196
1030 --l_gta_trx_line_old.discount_amount:=--
1034 -- l_GTA_TRX_line_old.discount_tax_amount-(l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_tax_amount;--
1031 --l_GTA_TRX_line_old.discount_amount-(l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_amount;--
1032 --Yao add for bug#8605196
1033 --l_gta_trx_line_old.discount_tax_amount:=--
1035 l_gta_trx_line_old.discount_amount:=l_GTA_TRX_line_old.discount_amount-
1036 round((l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_amount,2);
1037 l_gta_trx_line_old.discount_tax_amount:=l_GTA_TRX_line_old.discount_tax_amount-
1038 round((l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_tax_amount,2);
1039
1040 --Yao changed end for bug#9398467
1041 --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);
1042 l_GTA_Trx_line_old.tax_amount := round(l_GTA_Trx_line_old.tax_rate * (l_amount - l_quantity_limit*l_functional_price),2);--round for bug 9369455
1043 -- 25-Apr-2006: Jogen Hu bug 5168852
1044 --l_GTA_Trx_line_old.original_currency_amount := l_amount - l_quantity_limit*l_GTA_Trx_line_old.unit_price;
1045 l_gta_trx_line_old.original_currency_amount:=l_original_c_amount-round(l_original_c_amount*(l_quantity_limit/l_GTA_TRX_line_old.quantity),2);
1046 --qiong changed for bug 10638369
1047 l_GTA_TRX_line_old.quantity:=l_GTA_Trx_line_old.quantity - l_quantity_limit;
1048 l_GTA_Trx_line_old.original_currency_amount := l_amount - l_quantity_limit*l_GTA_Trx_line_old.unit_price;
1049 -- insert old line to the head of l_trx_lines
1050 l_trx_lines(1) := l_GTA_Trx_line_old;
1051 -- new trx
1052 copy_header(p_GTA_TRX_Header_Rec => l_GTA_TRX.trx_header
1053 , x_GTA_TRX_Header_Rec => l_trx_header_new);
1054
1055 l_GTA_Trx_new.trx_header := l_GTA_TRX.trx_header;
1056 l_GTA_Trx_new.trx_lines := l_trx_lines_new;
1057
1058 -- add the new trx to result table
1059 l_GTA_Trx_new.trx_header.group_number := l_trx_group_number;
1060 l_trx_group_number := l_trx_group_number + 1;
1061 process_before_split(x_gta_trx_rec => l_GTA_TRX_new
1062 );
1063 x_GTA_TRX_Tbl.EXTEND;
1064 x_GTA_TRX_Tbl(x_GTA_TRX_Tbl.COUNT) := l_GTA_Trx_new;
1065 --add_succ_to_temp(l_GTA_TRX);
1066
1067 -- update the header of old trx
1068 l_gta_trx.trx_header := l_trx_header_new;
1069 -- update the lines of old trx
1070 l_GTA_TRX.trx_lines := l_trx_lines;
1071
1072 -- init the l_processing_row and l_accumulated_amount
1073 l_processing_row := 0;
1074 l_accumulated_amount := 0;
1075 l_discount_row:=0;--Yao add for bug#8605196
1076
1077 END IF ; --l_gta_trx.trx_lines.COUNT = 1--
1078
1079 ELSE -- l_split_flag = 'N' --
1080 IF l_processing_row = 1
1081 THEN
1082 split_nested_table(p_trx_lines => l_GTA_TRX.trx_lines
1083 , p_split_flag => l_processing_row -1
1084 , x_first_lines => l_trx_lines_new
1085 , x_last_lines => l_trx_lines);
1086
1087 -- split this line
1088 l_GTA_Trx_line_old := l_GTA_TRX.trx_lines(l_processing_row);
1089 l_GTA_TRX_line_new := l_GTA_Trx_line_old;
1090
1091 -- get quantity limit
1092 -- 25-Apr-2006: Jogen Hu bug 5168852
1093 -- l_quantity_limit:=--
1094 -- floor((l_max_amount - l_accumulated_amount + l_GTA_Trx_line_old.amount)/l_GTA_Trx_line_old.unit_price);--
1095 l_quantity_limit:=floor((l_max_amount-l_accumulated_amount+l_actual_amount)/l_actual_unit_price ) ;
1096 -- 25-Apr-2006: Jogen Hu bug 5168852
1097
1098 IF l_quantity_limit > 0
1099 THEN
1100 l_GTA_TRX_line_new.quantity := l_quantity_limit;
1101
1102 -- 25-Apr-2006: Jogen Hu bug 5168852
1103 --l_GTA_TRX_line_new.amount := l_quantity_limit * l_GTA_TRX_line_new.unit_price;
1104 --l_GTA_TRX_line_new.amount := l_quantity_limit * l_functional_price;
1105 --Yao modified for bug 9369455
1106 l_GTA_TRX_line_new.amount := round(l_quantity_limit * l_functional_price,2);
1110 --(l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_amount;--Yao add for bug#8605196--
1107 --the following code is changed by Yao for bug9398467
1108 --Yao add for bug#8605196
1109 --l_gta_trx_line_new.discount_amount:=--
1111 --Yao add for bug#8605196
1112 --l_gta_trx_line_new.discount_tax_amount:=--
1113 --(l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_tax_amount;--Yao add for bug#8605196--
1114
1115 l_gta_trx_line_new.discount_amount:=round(
1116 (l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_amount,2);
1117 l_gta_trx_line_new.discount_tax_amount:=round(
1118 (l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_tax_amount,2);
1119 --Yao modified end for bug 9398467
1120
1121 --l_gta_trx_line_new.tax_amount := l_gta_trx_line_new.tax_rate * (l_quantity_limit * l_GTA_TRX_line_new.unit_price);
1122 l_gta_trx_line_new.tax_amount := round(l_gta_trx_line_new.tax_rate * (l_quantity_limit * l_functional_price),2);
1123 -- 25-Apr-2006: Jogen Hu bug 5168852
1124
1125 --l_gta_trx_line_new.original_currency_amount := l_quantity_limit * l_GTA_TRX_line_new.unit_price;
1126 l_gta_trx_line_new.original_currency_amount :=round(l_original_c_amount*(l_quantity_limit/l_GTA_TRX_line_old.quantity),2);
1127 --qiong changed for bug 10638369
1128 SELECT
1129 AR_GTA_TRX_LINES_ALL_S.NEXTVAL
1130 INTO
1131 l_GTA_TRX_line_new.GTA_TRX_LINE_ID
1132 FROM
1133 dual;
1134 --l_GTA_TRX_line_new.GTA_TRX_LINE_ID := AR_GTA_TRX_LINES_ALL_S.NEXTVAL;
1135 l_trx_lines_new.EXTEND;
1139
1136 l_trx_lines_new(l_trx_lines_new.COUNT) := l_GTA_TRX_line_new;
1137 -- ELSE l_quantity_limit =0 , then the old line is no change and new line is not needed
1138 END IF;--l_quantity_limit > 0--
1140
1141 -- 25-Apr-2006: Jogen Hu bug 5168852
1142 --l_GTA_TRX_line_old.amount:=l_amount - l_quantity_limit*l_GTA_Trx_line_old.unit_price;
1143 l_GTA_TRX_line_old.amount:=l_amount - round(l_quantity_limit*l_functional_price,2);--round for bug 9369455
1144 --the following code is changed by Yao for bug#9398467
1145 --Yao add for bug#8605196
1146 --l_gta_trx_line_old.discount_amount:=--
1147 -- l_GTA_TRX_line_old.discount_amount-(l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_amount;--
1148 --Yao add for bug#8605196
1149 --l_gta_trx_line_old.discount_tax_amount:=--
1150 -- l_GTA_TRX_line_old.discount_tax_amount-(l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_tax_amount;--
1151 l_gta_trx_line_old.discount_amount:=l_GTA_TRX_line_old.discount_amount-
1152 round((l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_amount,2);
1153 l_gta_trx_line_old.discount_tax_amount:=l_GTA_TRX_line_old.discount_tax_amount-
1154 round((l_quantity_limit/l_GTA_Trx_line_old.quantity)*l_GTA_TRX_line_old.discount_tax_amount,2);
1155 --Yao changed end for bug#9398467
1156 --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);
1157 l_GTA_Trx_line_old.tax_amount := round(l_GTA_Trx_line_old.tax_rate * (l_amount - l_quantity_limit*l_functional_price),2);
1158 -- 25-Apr-2006: Jogen Hu bug 5168852
1159 --l_GTA_TRX_line_old.original_currency_amount:= l_amount - l_quantity_limit*l_GTA_Trx_line_old.unit_price;
1160 l_gta_trx_line_old.original_currency_amount :=l_original_c_amount-round(l_original_c_amount*(l_quantity_limit/l_GTA_TRX_line_old.quantity),2);
1161 --qiong changed for bug 10638369
1162 l_GTA_TRX_line_old.quantity:=l_GTA_Trx_line_old.quantity - l_quantity_limit;
1163 l_GTA_TRX_line_old.original_currency_amount:= l_amount - l_quantity_limit*l_GTA_Trx_line_old.unit_price;
1164 -- insert old line to the head of l_trx_lines
1165 l_trx_lines(1) := l_GTA_Trx_line_old;
1166 -- new trx
1167 copy_header(p_GTA_TRX_Header_Rec => l_GTA_TRX.trx_header
1168 , x_GTA_TRX_Header_Rec => l_trx_header_new);
1169
1170 l_GTA_Trx_new.trx_header := l_GTA_TRX.trx_header;
1171 l_GTA_Trx_new.trx_lines := l_trx_lines_new;
1172
1173 -- add the new trx to result table
1174 l_gta_trx_new.trx_header.group_number := l_trx_group_number;
1175 l_trx_group_number := l_trx_group_number + 1;
1176 process_before_split(x_gta_trx_rec => l_GTA_TRX_new
1177 );
1178 x_GTA_TRX_Tbl.EXTEND;
1179 x_GTA_TRX_Tbl(x_GTA_TRX_Tbl.COUNT) := l_GTA_Trx_new;
1180 --add_succ_to_temp(l_GTA_TRX);
1181
1182 -- update the header of old trx
1183 l_gta_trx.trx_header := l_trx_header_new;
1184 -- update the lines of old trx
1185 l_GTA_TRX.trx_lines := l_trx_lines;
1186
1187 -- init the l_processing_row and l_accumulated_amount
1188 l_processing_row := 0;
1189 l_accumulated_amount := 0;
1190
1191 ELSE --l_gta_trx.trx_lines.COUNT = 1--
1192 split_nested_table(p_trx_lines => l_GTA_TRX.trx_lines
1193 , p_split_flag => l_processing_row -1
1194 , x_first_lines => l_trx_lines_new
1195 , x_last_lines => l_trx_lines);
1196
1197 -- copy trx header
1198 copy_header(p_GTA_TRX_Header_Rec => l_GTA_TRX.trx_header
1199 , x_GTA_TRX_Header_Rec => l_trx_header_new );
1200
1201 -- get new trx number
1202 l_GTA_Trx_new.trx_header := l_GTA_TRX.trx_header;
1203 l_gta_trx_new.trx_lines := l_trx_lines_new;
1204
1205 -- add it to result tbl
1206 l_gta_trx_new.trx_header.group_number := l_trx_group_number;
1207 l_trx_group_number := l_trx_group_number + 1;
1208 process_before_split(x_gta_trx_rec => l_GTA_TRX_new
1209 );
1210
1211 x_GTA_TRX_Tbl.EXTEND;
1212 x_gta_trx_tbl(x_gta_trx_tbl.COUNT) := l_gta_trx_new;
1213
1214 --update the header of olf trx
1215 l_gta_trx.trx_header := l_trx_header_new;
1216 -- update the old trx.trx_lines
1217 l_GTA_TRX.trx_lines := l_trx_lines;
1218
1219 -- init l_processing_row and l_accumulateed_amount
1220 l_processing_row := 0;
1221 l_accumulated_amount := 0;
1222 l_discount_row:=0;
1223 END IF;--l_gta_trx.trx_lines.COUNT = 1--
1224 END IF; -- end if l_split_flag = 'Y' -- */
1225
1226
1227 --ELSE
1228 --l_processing_row = l_max_line AND l_sales_list_flag = 'N' and l_accumulated_amount > l_max_amount
1229 END IF; -- /*end if l_accumulated_amount <= l_max_amount AND l_processing_row<l_max_line*/
1230 l_processing_row := l_processing_row + 1;
1231 END LOOP; --end loop by lines
1232 END IF; -- end if (l_sum_of_amount<l_max_amount AND (l_lines_number<l_max_line or l_sales_list_flag='Y' ))
1233
1234 END IF;/*l_trx_type <> 'CM'*/
1235
1236 --begin split the l_gta_trx
1237 l_trx_rate_index := l_gta_rate_trx_tbl.NEXT(l_trx_rate_index);
1238 END LOOP; -- end loop of l_gta_rate_trx_tbl /*l_trx_rate_index IS NOT NULL*/
1239
1240 -- add the succesed infomation into the temp table
1241
1242 add_succ_to_temp(l_gta_trx_succesed);
1243 l_trx_index := P_GTA_TRX_Tbl.NEXT(l_trx_index);
1244
1245 END LOOP; -- end loop by P_gta_trx_tbl /*l_trx_index IS NOT NULL*/
1246
1247 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1248 THEN
1252 END IF;
1249 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1250 , G_MODULE_PREFIX || l_procedure_name
1251 ,'End Procedure. ');
1253
1254 EXCEPTION
1255 WHEN OTHERS THEN
1259 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
1256 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1257 THEN
1258 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1260 , Sqlcode||Sqlerrm);
1261 END IF;
1262 RAISE;
1263 END Split_Transactions;
1264
1265
1266 --=============================================================================
1267 -- PROCEDURE NAME :
1268 -- Copy_Header
1269 -- TYPE:
1270 -- PUBLIC
1271 --
1272 -- DESCRIPTION:
1273 -- When split trx procedure new a trx, new a trx_header for the new trx
1274 -- PARAMETERS:
1275 -- p_GTA_TRX_Header_Rec IN old trx_header
1276 -- x_GTA_TRX_Header_Rec OUT new trx_header
1277 --
1278 -- HISTORY:
1279 -- 10-MAY-2005 : Jim.Zheng Create
1280 --=============================================================================
1281 PROCEDURE Copy_Header
1282 ( p_GTA_TRX_Header_Rec IN AR_GTA_TRX_UTIL.TRX_HEADER_REC_TYPE
1283 , x_GTA_TRX_Header_Rec OUT NOCOPY AR_GTA_TRX_UTIL.TRX_HEADER_REC_TYPE
1284 ) AS
1285 l_procedure_name VARCHAR2(30):= 'Copy_Header';
1286 l_gta_header_id ar_gta_trx_headers.gta_trx_header_id%TYPE;
1287 l_gta_trx_header_rec AR_GTA_TRX_UTIL.TRX_HEADER_REC_TYPE;
1288
1289 BEGIN
1290 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1291 THEN
1292 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1293 , G_MODULE_PREFIX || l_procedure_name
1294 ,'Begin Procedure. ');
1295 END IF;
1296
1297 l_GTA_TRX_Header_Rec := p_GTA_TRX_Header_Rec;
1298 SELECT
1299 AR_GTA_TRX_HEADERS_ALL_S.NEXTVAL
1300 INTO
1301 l_GTA_TRX_Header_Rec.gta_trx_header_id
1302 FROM
1303 dual;
1304 --x_GTA_TRX_Header_Rec.gta_trx_header_id := AR_GTA_TRX_HEADERS_ALL_S.NEXTVAL;
1305 -- use this if is due to the trx first split and
1306
1307 l_GTA_TRX_Header_Rec.group_number := p_GTA_TRX_Header_Rec.group_number + 1;
1308
1309 l_GTA_TRX_Header_rec.gta_trx_number := l_GTA_TRX_Header_Rec.ra_trx_id
1310 || '-'
1311 || l_GTA_TRX_Header_Rec.group_number
1312 || '-'
1313 || l_GTA_TRX_Header_rec.version;
1314
1315 x_GTA_TRX_Header_Rec := l_gta_trx_header_rec;
1316
1317 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1318 THEN
1319 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1320 , G_MODULE_PREFIX || l_procedure_name
1321 ,'End Procedure. ');
1322 END IF;
1323
1324 EXCEPTION
1325 WHEN OTHERS THEN
1326 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1327 THEN
1328 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1329 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
1330 , Sqlcode||Sqlerrm);
1331 END IF;
1332 RAISE;
1333
1334 END Copy_Header;
1335
1336 --=============================================================================
1337 -- PROCEDURE NAME:
1338 -- split_nested_table
1339 -- TYPE:
1340 -- PUBLIC
1341 --
1342 -- DESCRIPTION :
1343 -- a nested table hasn't a split method, this procedure make a
1344 -- split table method for TRX_Line_tbl_type
1345 -- be change.
1346 -- PARAMETERS:
1347 -- p_trx_lines IN source table
1348 -- split_flag IN the position of table which be split
1349 -- x_first_lines OUT first party of demanation table
1350 -- x_last_lines OUT last party of demanation table
1351 --
1352 -- HISTORY:
1353 -- 10-MAY-2005 : Jim.Zheng Create
1354 --=============================================================================
1355 PROCEDURE Split_Nested_Table
1356 (p_trx_lines IN AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE
1357 , p_split_flag IN NUMBER
1358 , x_first_lines OUT NOCOPY AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE
1359 , x_last_lines OUT NOCOPY AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE
1360 )
1361 IS
1362 l_first_lines AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE := AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
1363 l_last_lines AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE := AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
1364 l_index NUMBER;
1365 l_procedure_name VARCHAR2(50):='Split_Nested_Table';
1369 THEN
1366
1367 BEGIN
1368 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1370 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1371 , G_MODULE_PREFIX || l_procedure_name
1372 ,'Begin Procedure. ');
1373 END IF;
1374
1375 --init
1376 x_first_lines := AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
1377 x_last_lines := AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
1378
1379 IF p_split_flag = 0
1380 THEN
1381 l_last_lines := p_trx_lines;
1382 ELSE
1383 l_first_lines.EXTEND(p_split_flag);
1384
1385 l_index := l_first_lines.FIRST;
1386 WHILE l_index <= p_split_flag
1387 LOOP
1388 l_first_lines(l_index) := p_trx_lines(l_index);
1389 l_index := l_first_lines.NEXT(l_index);
1390 END LOOP;
1391
1392 l_last_lines.EXTEND(p_trx_lines.COUNT - p_split_flag);
1393
1394 l_index := l_last_lines.FIRST;
1395 WHILE l_index <= (p_trx_lines.COUNT - p_split_flag)
1396 LOOP
1397 l_last_lines(l_index) := p_trx_lines(l_index + p_split_flag);
1398 l_index := l_last_lines.NEXT(l_index);
1399 END LOOP;
1400 END IF;
1401
1402 x_first_lines := l_first_lines;
1403 x_last_lines := l_last_lines;
1404
1405 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1406 THEN
1407 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1408 , G_MODULE_PREFIX || l_procedure_name
1409 ,'End Procedure. ');
1410 END IF;
1411
1412 EXCEPTION
1413 WHEN OTHERS THEN
1414 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1415 THEN
1416 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1417 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
1418 , Sqlcode||Sqlerrm);
1419 END IF;
1420 RAISE;
1421 END split_nested_table;
1422
1423 --=============================================================================
1424 -- PROCEDURE NAME:
1425 -- process_before_split
1426 -- TYPE:
1427 -- PUBLIC
1428 --
1429 -- DESCRIPTION:
1433 -- x_gta_trx_rec IN OUT NOCOPY new trx which line number is changed
1430 -- When split trx procedure new a trx, the line_num, trx_num, trx_header_id alse
1431 -- be change.
1432 -- PARAMETERS:
1434 --
1435 -- HISTORY:
1436 -- 10-MAY-2005 : Jim.Zheng Create
1437 --=============================================================================
1438 PROCEDURE process_before_split
1439 (
1440 x_gta_trx_rec IN OUT NOCOPY AR_Gta_Trx_Util.TRX_REC_TYPE
1441 )
1442 AS
1443
1444 l_procedure_name VARCHAR2(35) := 'get_trx_header_id';
1445 l_index NUMBER; -- loop index
1446
1447 BEGIN
1448
1449 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1450 THEN
1451 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1452 , G_MODULE_PREFIX || l_procedure_name
1453 ,'Begin Procedure. ');
1454 END IF;
1455
1456 -- generate gta_trx_number
1457 x_GTA_TRX_rec.trx_header.gta_trx_number := x_GTA_TRX_Rec.trx_header.ra_trx_id
1458 || '-'
1459 || x_GTA_TRX_Rec.trx_header.group_number
1460 || '-'
1461 || x_GTA_TRX_Rec.trx_header.version;
1462
1463 -- generate line number
1464 l_index := x_gta_trx_rec.trx_lines.FIRST;
1465 WHILE l_index IS NOT NULL
1466 LOOP
1467 SELECT
1468 AR_Gta_Trx_Lines_All_s.NEXTVAL
1469 INTO
1470 x_gta_trx_rec.trx_lines(l_index).gta_trx_line_id
1471 FROM
1472 dual;
1473 x_gta_trx_rec.trx_lines(l_index).line_number := to_char(l_index);
1474 x_gta_trx_rec.trx_lines(l_index).gta_trx_header_id := x_gta_trx_rec.trx_header.gta_trx_header_id;
1475 l_index := x_gta_trx_rec.trx_lines.NEXT(l_index);
1476 END LOOP;
1477
1478 -- get fp registration number and tp registration number;
1479 --12/06/2006 Shujuan Yan bug 5168900
1480 IF x_gta_trx_rec.trx_lines.FIRST IS NOT NULL
1481 THEN
1482 x_gta_trx_rec.trx_header.fp_tax_registration_number := x_gta_trx_rec.trx_lines(1).fp_tax_registration_number;
1483 x_gta_trx_rec.trx_header.tp_tax_registration_number := x_gta_trx_rec.trx_lines(1).tp_tax_registration_number;
1484
1485 --28/12/07 added by subba for R12.1
1486
1487
1488 x_gta_trx_rec.trx_header.invoice_type := ar_gta_trx_util.get_invoice_type(p_org_id => x_gta_trx_rec.trx_header.org_id
1489 ,p_customer_trx_id=> x_gta_trx_rec.trx_header.ra_trx_id
1490 ,p_trx_line_id=> x_gta_trx_rec.trx_lines(1).ar_trx_line_id
1494 END IF;
1491 ,p_fp_tax_registration_num => x_gta_trx_rec.trx_lines(1).fp_tax_registration_number);
1492
1493
1495 --12/06/2006 Shujuan Yan bug 5168900
1496
1497 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1498 THEN
1499 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1500 , G_MODULE_PREFIX || l_procedure_name
1501 ,'Begin Procedure. ');
1502 END IF;
1503
1504 EXCEPTION
1505 WHEN OTHERS THEN
1506 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1507 THEN
1508 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1509 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
1510 , Sqlcode||Sqlerrm);
1511 END IF;
1512 RAISE;
1513 END Process_Before_Split;
1514
1515
1516 --=============================================================================
1517 -- PROCEDURE NAME:
1518 -- add_succ_to_temp
1519 -- TYPE:
1520 -- PUBLIC
1521 --
1522 -- DESCRIPTION :
1523 -- insert a sucess row to temp table when a trx is be due successed
1524 -- be change.
1525 -- PARAMETERS:
1526 -- p_gta_trx_rec in the successed trx
1527 --
1528 -- HISTORY:
1529 -- 10-MAY-2005 : Jim.Zheng Create
1530 -- 15-Jan-2009: Yao Zhang Modified for bug 7709947
1531 --=============================================================================
1532 PROCEDURE add_succ_to_temp
1533 (
1534 p_gta_trx_rec IN AR_Gta_Trx_Util.TRX_REC_TYPE
1535 )
1536 AS
1537 l_amount NUMBER;
1538 l_trx_num VARCHAR2(30);
1539 l_trx_type VARCHAR2(30);
1540 l_cust_name VARCHAR2(360);--Yao Zhang changed for bug 7709947
1541 l_gta_inv_num VARCHAR2(30);
1542
1543 l_procedure_name VARCHAR2(30) := 'add_succ_to_temp';
1544 l_index NUMBER; -- loop index
1545 l_warning_record_count NUMBER;
1546 l_customer_trx_id NUMBER(15);
1547
1548 BEGIN
1549 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1550 THEN
1551 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1552 , G_MODULE_PREFIX || l_procedure_name
1553 ,'Begin Procedure. ');
1554 END IF;
1555
1556 l_amount := 0;
1557
1558 l_index := p_gta_trx_rec.trx_lines.FIRST;
1559 WHILE l_index IS NOT NULL
1560 LOOP
1561 l_amount := l_amount + p_gta_trx_rec.trx_lines(l_index).amount;
1562 l_index := p_gta_trx_rec.trx_lines.NEXT(l_index);
1563 END LOOP;
1564
1565 l_trx_num := p_gta_trx_rec.trx_header.ra_trx_number;
1566 l_customer_trx_id := p_gta_trx_rec.trx_header.ra_trx_id;
1567 BEGIN
1568 SELECT
1569 ctt.TYPE
1570 INTO
1571 l_trx_type
1572 FROM
1573 ra_customer_trx_all h
1574 , ra_cust_trx_types_all ctt
1575 WHERE ctt.cust_trx_type_id = h.cust_trx_type_id
1576 AND ctt.Org_Id = h.Org_Id
1577 AND h.customer_trx_id = p_gta_trx_rec.trx_header.ra_trx_id;
1578 EXCEPTION
1579 WHEN no_data_found THEN
1580 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1581 THEN
1582 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
1583 , G_MODULE_PREFIX || l_procedure_name
1584 ,'No data found. ');
1585 END IF;
1586 END;
1587
1588 --l_trx_type := p_gta_trx_rec.trx_header.trx_type;
1589 l_cust_name := p_gta_trx_rec.trx_header.bill_to_customer_name;
1590 l_gta_inv_num := p_gta_trx_rec.trx_header.gta_trx_number;
1591 SELECT
1592 COUNT(*)
1593 INTO
1594 l_warning_record_count
1595 FROM
1596 ar_gta_transfer_temp temp
1597 WHERE temp.transaction_id = l_customer_trx_id
1598 AND temp.succeeded = 'W';
1599
1600 IF l_warning_record_count = 0
1601 THEN
1602 INSERT INTO
1603 ar_gta_transfer_temp t
1604 (t.seq
1605 , t.succeeded
1606 , t.transaction_id
1607 , t.transaction_num
1608 , t.transaction_type
1609 , t.customer_name
1610 , t.amount
1611 , t.failedreason
1612 , t.gta_invoice_num
1613 )
1614 SELECT
1615 ar_gta_transfer_temp_s.NEXTVAL
1616 , 'Y'
1617 , l_customer_trx_id
1618 , l_trx_num
1619 , l_trx_type
1620 , l_cust_name
1621 , l_amount
1622 , NULL
1623 , l_gta_inv_num
1624 FROM
1625 dual;
1626 ELSIF l_warning_record_count = 1
1627 THEN
1628 UPDATE
1629 ar_gta_transfer_temp temp
1630 SET
1631 temp.amount = l_amount
1632 WHERE temp.transaction_id = l_customer_trx_id
1633 AND temp.succeeded = 'W';
1634
1635 END IF;/*l_warning_record_count = 0*/
1636
1637
1638 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1639 THEN
1640 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1641 , G_MODULE_PREFIX || l_procedure_name
1642 ,'Begin Procedure. ');
1643 END IF;
1644
1645 EXCEPTION
1646 WHEN OTHERS THEN
1647 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1648 THEN
1649 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1650 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
1651 , Sqlcode||Sqlerrm);
1652 END IF;
1653 RAISE;
1654
1655 END add_succ_to_temp;
1656
1657
1658 --=============================================================================
1659 -- PROCEDURE NAME:
1660 -- split_trx_by_taxreg_number
1664 -- DESCRIPTION:
1661 -- TYPE:
1662 -- PUBLIC
1663 --
1665 -- split trx by first party registration number
1666 -- PARAMETERS:
1667 -- p_gta_trx in AR_GTA_TRX_UTIL.trx_rec_type
1668 -- x_trx_tbl out AR_Gta_Trx_Util.trx_tbl_type
1669 -- HISTORY:
1670 -- 10-Sep-2005 : Jim.Zheng Create
1671 --=============================================================================
1672 PROCEDURE split_trx_by_taxreg_number
1673 (p_gta_trx IN AR_GTA_TRX_UTIL.trx_rec_type
1674 ,x_trx_tbl OUT NOCOPY AR_Gta_Trx_Util.trx_tbl_type
1675 )
1676 IS
1677 l_procedure_name VARCHAR2(100) := 'split_trx_by_tax_registration_number';
1678 l_gta_rate_trx_line AR_GTA_TRX_UTIL.trx_line_rec_type;
1679 l_gta_rate_trx AR_GTA_TRX_UTIL.trx_rec_type;
1680 l_gta_rate_trx_tbl AR_GTA_TRX_UTIL.TRX_Tbl_TYPE := AR_GTA_TRX_UTIL.TRX_Tbl_TYPE();
1681 l_tax_reg_number ar_gta_trx_headers_all.fp_tax_registration_number%TYPE;
1682 l_add_flag NUMBER := 0;
1683 l_gta_trx AR_GTA_TRX_UTIL.trx_rec_type;
1684 l_gta_trx_line_init AR_GTA_TRX_UTIL.trx_line_rec_type;
1685 l_trx_header_new AR_GTA_TRX_UTIL.TRX_header_rec_TYPE;
1686 l_trx_rate_index NUMBER; -- index for nested table loop
1687 l_index NUMBER; -- index for nested table loop
1688 BEGIN
1689 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1690 THEN
1691 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1692 , G_MODULE_PREFIX || l_procedure_name
1693 ,'Begin Procedure. ');
1694 END IF;
1695 -- init l_gta_rate_trx_tbl
1696 l_gta_rate_trx_tbl := AR_GTA_TRX_UTIL.TRX_Tbl_TYPE();
1697
1698 -- init x_trx_tbl
1699 x_trx_tbl := AR_GTA_TRX_UTIL.TRX_Tbl_TYPE();
1700
1701 -- init l_gta_rate_trx
1702 l_gta_rate_trx.trx_lines := AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
1703
1704 l_gta_trx := p_gta_trx;
1705
1706 -- split the different rate lines to different trx, use loop and if else
1707 l_trx_rate_index := l_GTA_TRX.trx_lines.FIRST;
1708 WHILE l_trx_rate_index IS NOT NULL
1709 LOOP
1710 -- init l_gta_rate_trx_line
1711 l_gta_rate_trx_line := l_gta_trx_line_init;
1712 l_add_flag := 0; --12/06/2006 Shujuan Yan bug
1713
1714 -- read first line , if it is first line
1715 -- new a trx, and insert it into l_gta_trx_tbl
1716 IF l_trx_rate_index=1
1717 THEN
1718 l_gta_rate_trx_line := l_GTA_TRX.trx_lines(l_trx_rate_index);
1719
1720 l_gta_rate_trx.trx_header := l_GTA_TRX.trx_header;
1721
1722 l_gta_rate_trx.trx_lines.EXTEND;
1723 l_gta_rate_trx.trx_lines(l_gta_rate_trx.trx_lines.COUNT) := l_gta_rate_trx_line;
1724
1725 l_gta_rate_trx_tbl.EXTEND;
1726 l_gta_rate_trx_tbl(l_gta_rate_trx_tbl.COUNT) := l_gta_rate_trx;
1727 -- if it isn't first line , compare the tax_rate to the pre trx
1728 ELSE
1729 l_gta_rate_trx_line := l_GTA_TRX.trx_lines(l_trx_rate_index);
1730 l_tax_reg_number := l_gta_rate_trx_line.fp_tax_registration_number;
1731 -- compare the lines rate , if same rate ,add the line to trx, else new trx and insert it in to l_gta_trx_tbl
1732 l_index := l_gta_rate_trx_tbl.FIRST;
1733 WHILE l_index IS NOT NULL
1734 LOOP
1735 IF l_gta_rate_trx_tbl(l_index).trx_lines(1).fp_tax_registration_number = l_tax_reg_number
1739 l_index := l_gta_rate_trx_tbl.NEXT(l_index);
1736 THEN
1737 l_add_flag := l_index;
1738 END IF ;
1740 END LOOP; -- end loop l_gta_trx_tbl;
1741
1742 -- if the tax_rate is not equal pre trx
1743 IF l_add_flag = 0
1744 THEN
1745 -- new trx for new tax_rate
1746 copy_header( p_GTA_TRX_Header_Rec => l_gta_rate_trx_tbl(l_gta_rate_trx_tbl.COUNT).trx_header
1747 , x_GTA_TRX_Header_Rec => l_trx_header_new );
1748 l_gta_rate_trx.trx_header := l_trx_header_new;
1749 l_gta_rate_trx.trx_lines := AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
1750 l_gta_rate_trx.trx_lines.EXTEND;
1751 l_gta_rate_trx.trx_lines(l_gta_rate_trx.trx_lines.COUNT) := l_gta_rate_trx_line;
1752
1753 l_gta_rate_trx_tbl.EXTEND;
1754 l_gta_rate_trx_tbl(l_gta_rate_trx_tbl.COUNT) := l_gta_rate_trx;
1755
1756 ELSE
1757 -- add the line to the same tax_rate trx lines
1758 l_gta_rate_trx_tbl(l_add_flag).trx_lines.EXTEND;
1759 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;
1760 END IF; -- end if l_add_flag = 0
1761
1762 END IF; -- end if i=1;
1763
1764 l_trx_rate_index := l_gta_trx.trx_lines.NEXT(l_trx_rate_index);
1765 END LOOP; -- end loop l_Gta_TRx.trx_lines. Now the trx is split in a trx_tbl
1766
1767 -- output
1768 x_trx_tbl := l_gta_rate_trx_tbl;
1769
1770 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1771 THEN
1772 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1773 , G_MODULE_PREFIX || l_procedure_name
1774 ,'Begin Procedure. ');
1775 END IF;
1776
1777 EXCEPTION
1778 WHEN OTHERS THEN
1779 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1780 THEN
1781 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1782 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
1783 , Sqlcode||Sqlerrm);
1784 END IF;
1785 RAISE;
1786 END split_trx_by_taxreg_number;
1787
1788 --=============================================================================
1789 -- PROCEDURE NAME:
1790 -- split_trx_by_rate
1791 -- TYPE:
1792 -- PUBLIC
1793 --
1794 -- DESCRIPTION:
1795 -- split trx by tax rate
1796 -- PARAMETERS:
1797 -- p_gta_trx in AR_GTA_TRX_UTIL.trx_tbl_type
1798 -- x_trx_tbl out AR_Gta_Trx_Util.trx_tbl_type
1799 -- HISTORY:
1800 -- 10-Sep-2005 : Jim.Zheng Create
1801 --=============================================================================
1802 PROCEDURE split_trx_by_rate
1803 (p_gta_tbl IN AR_GTA_TRX_UTIL.trx_tbl_type
1804 ,x_trx_tbl OUT NOCOPY AR_Gta_Trx_Util.trx_tbl_type
1805 )
1806 IS
1807 l_procedure_name VARCHAR2(100) := 'split_trx_by_tax_registration_number';
1808 l_gta_rate_trx_line AR_GTA_TRX_UTIL.trx_line_rec_type;
1809 l_gta_rate_trx AR_GTA_TRX_UTIL.trx_rec_type;
1810 l_gta_rate_trx_tbl AR_GTA_TRX_UTIL.TRX_Tbl_TYPE := AR_GTA_TRX_UTIL.TRX_Tbl_TYPE();
1811 l_tax_rate NUMBER;
1812 l_add_flag NUMBER := 0;
1813 l_gta_trx AR_GTA_TRX_UTIL.trx_rec_type;
1814 l_gta_trx_line_init AR_GTA_TRX_UTIL.trx_line_rec_type;
1815 l_trx_header_new AR_GTA_TRX_UTIL.TRX_header_rec_TYPE;
1816 l_trx_rate_index NUMBER; -- index for nested table loop
1817 l_index NUMBER; -- index for nested table loop
1818 l_gta_index NUMBER; -- index for nested table loop
1819 BEGIN
1820 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1821 THEN
1822 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1823 , G_MODULE_PREFIX || l_procedure_name
1824 ,'Begin Procedure. ');
1825 END IF;
1826 -- init l_gta_rate_trx_tbl
1827 l_gta_rate_trx_tbl := AR_GTA_TRX_UTIL.TRX_Tbl_TYPE();
1828
1829 -- init x_trx_tbl
1830 x_trx_tbl := AR_GTA_TRX_UTIL.TRX_Tbl_TYPE();
1831
1832 l_gta_index := p_gta_tbl.FIRST;
1833 WHILE l_gta_index IS NOT NULL
1834 LOOP
1835 -- init l_gta_rate_trx
1836 l_gta_rate_trx.trx_lines := AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
1837
1838 l_gta_trx := p_gta_tbl(l_gta_index);
1839
1840 -- split the different rate lines to different trx, use loop and if else
1841 l_trx_rate_index := l_GTA_TRX.trx_lines.FIRST;
1842 WHILE l_trx_rate_index IS NOT NULL
1843 LOOP
1844 -- init l_gta_rate_trx_line
1845 l_gta_rate_trx_line := l_gta_trx_line_init;
1846 l_add_flag := 0; --12/06/2006 Shujuan Yan bug
1847
1848 -- read first line , if it is first line
1849 -- new a trx, and insert it into l_gta_trx_tbl
1850 IF l_trx_rate_index=1
1851 THEN
1852 l_gta_rate_trx_line := l_GTA_TRX.trx_lines(l_trx_rate_index);
1853
1854 l_gta_rate_trx.trx_header := l_GTA_TRX.trx_header;
1855
1856 l_gta_rate_trx.trx_lines.EXTEND;
1857 l_gta_rate_trx.trx_lines(l_gta_rate_trx.trx_lines.COUNT) := l_gta_rate_trx_line;
1858
1859 l_gta_rate_trx_tbl.EXTEND;
1860 l_gta_rate_trx_tbl(l_gta_rate_trx_tbl.COUNT) := l_gta_rate_trx;
1861 -- if it isn't first line , compare the tax_rate to the pre trx
1862 ELSE
1863 l_gta_rate_trx_line := l_GTA_TRX.trx_lines(l_trx_rate_index);
1864 l_tax_rate := l_gta_rate_trx_line.tax_rate;
1865 -- compare the lines rate , if same rate ,add the line to trx, else new trx and insert it in to l_gta_trx_tbl
1866 l_index := l_gta_rate_trx_tbl.FIRST;
1867 WHILE l_index IS NOT NULL
1868 LOOP
1869 IF l_gta_rate_trx_tbl(l_index).trx_lines(1).tax_rate = l_tax_rate
1870 THEN
1871 l_add_flag := l_index;
1875
1872 END IF ;
1873 l_index := l_gta_rate_trx_tbl.NEXT(l_index);
1874 END LOOP; -- end loop l_gta_trx_tbl;
1876 -- if the tax_rate is not equal pre trx
1877 IF l_add_flag = 0
1878 THEN
1879 -- new trx for new tax_rate
1880 copy_header( p_GTA_TRX_Header_Rec => l_gta_rate_trx_tbl(l_gta_rate_trx_tbl.COUNT).trx_header
1881 , x_GTA_TRX_Header_Rec => l_trx_header_new );
1882 l_gta_rate_trx.trx_header := l_trx_header_new;
1883 l_gta_rate_trx.trx_lines := AR_GTA_TRX_UTIL.TRX_line_Tbl_TYPE();
1884 l_gta_rate_trx.trx_lines.EXTEND;
1885 l_gta_rate_trx.trx_lines(l_gta_rate_trx.trx_lines.COUNT) := l_gta_rate_trx_line;
1886
1887 l_gta_rate_trx_tbl.EXTEND;
1888 l_gta_rate_trx_tbl(l_gta_rate_trx_tbl.COUNT) := l_gta_rate_trx;
1889
1890 ELSE
1891 -- add the line to the same tax_rate trx lines
1892 l_gta_rate_trx_tbl(l_add_flag).trx_lines.EXTEND;
1893 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;
1894 END IF; -- end if l_add_flag = 0
1895
1896 END IF; -- end if i=1;
1897
1898 l_trx_rate_index := l_gta_trx.trx_lines.NEXT(l_trx_rate_index);
1899 END LOOP; -- end loop l_Gta_TRx.trx_lines. Now the trx is split in a trx_tbl
1900
1901 l_gta_index := p_gta_tbl.NEXT(l_gta_index);
1902 END LOOP; -- end loop p_gta_tbl
1903
1904 -- output
1905 x_trx_tbl := l_gta_rate_trx_tbl;
1906
1907 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1908 THEN
1909 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1910 , G_MODULE_PREFIX || l_procedure_name
1911 ,'Begin Procedure. ');
1912 END IF;
1913
1914 EXCEPTION
1915 WHEN OTHERS THEN
1916 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1917 THEN
1918 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1919 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
1920 , Sqlcode||Sqlerrm);
1921 END IF;
1922 RAISE;
1923 END split_trx_by_rate;
1924
1925 --=============================================================================
1926 -- PROCEDURE NAME:
1927 -- get_trx_type
1928 -- TYPE:
1929 -- PUBLIC
1930 --
1931 -- DESCRIPTION:
1932 -- get trx type by trx id
1933 -- PARAMETERS:
1934 -- p_org_id IN NUMBER
1935 -- p_gta_trx in AR_GTA_TRX_UTIL.trx_tbl_type
1936 -- x_trx_type out ra_cust_trx_types_all.type%TYPE
1937 -- HISTORY:
1938 -- 10-Sep-2005 : Jim.Zheng Create
1939 -- 04-Aug-2009 : Yao Zhang fix bug#8756943 modified
1940 --=============================================================================
1941 PROCEDURE get_trx_type
1942 (p_org_id IN NUMBER
1943 , p_gta_trx IN AR_Gta_Trx_Util.trx_rec_type
1944 , x_trx_type OUT NOCOPY ra_cust_trx_types_all.type%TYPE
1945 )
1946 IS
1947
1948 l_procedure_name VARCHAR2(30) := 'get_trx_type';
1949 l_trx_id ra_customer_trx_all.customer_trx_id%TYPE;
1950 l_trx_type ra_cust_trx_types_all.type%TYPE;
1951 BEGIN
1952 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1953 THEN
1954 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1955 , G_MODULE_PREFIX || l_procedure_name
1956 ,'Begin Procedure. ');
1957 END IF;
1958
1959 IF p_gta_trx.trx_header.ra_trx_id IS NOT NULL
1960 THEN
1961 l_trx_id := p_gta_trx.trx_header.ra_trx_id;
1962
1963 --Yao Zhang add for credit memo with discount lines exceed max line number should be transfered
1964 --without split for bug#8756943
1965 IF p_gta_trx.trx_header.consolidation_flag='0'
1966 THEN
1967 IF AR_GTA_TRX_UTIL.Get_Gtainvoice_Amount(p_gta_trx.trx_header.gta_trx_header_id)>0
1968 THEN l_trx_type:='INV';
1969 ELSE l_trx_type:='CM';
1970 END IF;
1971 ELSE
1972 BEGIN
1973 SELECT
1974 ctt.TYPE
1975 INTO
1976 l_trx_type
1977 FROM
1978 ra_customer_trx_all h
1979 ,ra_cust_trx_types_all ctt
1980 WHERE h.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID(+)
1981 AND ctt.TYPE IN ('INV', 'CM', 'DM')
1982 AND ctt.org_id = p_org_id
1983 AND h.customer_trx_id = l_trx_id;
1984 EXCEPTION
1985 WHEN OTHERS THEN
1986 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1987 THEN
1988 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1989 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
1990 , Sqlcode||Sqlerrm);
1991 END IF;
1992 RAISE;
1993 END;
1994 END IF;--p_gta_trx.trx_header.consolidation_flag='0'
1995 END IF;/*p_gta_trx.trx_header.ra_trx_id IS NOT NULL*/
1996
1997 x_trx_type := l_trx_type;
1998
1999 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2000 THEN
2001 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2002 , G_MODULE_PREFIX || l_procedure_name
2003 ,'Begin Procedure. ');
2004 END IF;
2005
2006 EXCEPTION
2007 WHEN OTHERS THEN
2008 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2009 THEN
2010 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
2011 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
2012 , Sqlcode||Sqlerrm);
2013 END IF;
2014 RAISE;
2015 END get_trx_type;
2016
2017 --=============================================================================
2021 -- PUBLIC
2018 -- PROCEDURE NAME:
2019 -- judge_cm_limit
2020 -- TYPE:
2022 --
2023 -- DESCRIPTION:
2024 -- Judge wether the CM exceed the max line and max amount
2025 -- PARAMETERS:
2026 -- p_gta_trx in AR_GTA_TRX_UTIL.trx_tbl_type
2027 -- p_org_id in number
2028 -- x_result out BOOLEAN
2029 -- HISTORY:
2030 -- 10-Sep-2005 : Jim.Zheng Create
2031 -- 16-Dec-2008 Yao Zhang Changed for bug 7644235
2032 -- 23-Jan-2009 : Yao Zhang changed for bug 7758496
2033 -- 28-Oct-2009 : Yao Zhang changed for bug 9045187
2034 --=============================================================================
2035 PROCEDURE judge_cm_limit
2036 (p_gta_trx IN AR_Gta_Trx_Util.trx_rec_type
2037 , p_org_id IN NUMBER
2038 , p_transfer_id IN NUMBER --yao zhang changed for bug 7758496
2039 , x_result OUT NOCOPY BOOLEAN)
2040 IS
2041 l_procedure_name VARCHAR2(30) := 'judge_cm_limit';
2042 l_gta_trx AR_Gta_Trx_Util.trx_rec_type;
2043 l_fp_reg_num ar_gta_trx_headers_all.fp_tax_registration_number%TYPE;
2044 l_max_amount ar_gta_tax_limits_all.max_amount%TYPE;
2045 l_max_line ar_gta_tax_limits_all.max_num_of_line%TYPE;
2046 l_lines_number NUMBER;
2047 l_sum_of_amount NUMBER;
2048 l_trx_line_index NUMBER;
2049 l_invoice_type ar_gta_trx_headers_all.invoice_type%TYPE; --added by subba for R12.1
2050 l_sales_list_flag ar_gta_rule_headers_all.Sales_List_Flag%TYPE;--added by Yao Zhang for bug 7758496
2051 BEGIN
2052 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2053 THEN
2054 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2055 , G_MODULE_PREFIX || l_procedure_name
2056 ,'Begin Procedure. ');
2057 END IF;
2058
2059 x_result := TRUE;
2060 l_gta_trx := p_gta_trx;
2061 l_fp_reg_num := l_gta_trx.trx_lines(1).fp_tax_registration_number;
2062
2063 --added by subba for R12.1, getting invoice type to caliculate VAT limits
2064
2065 l_invoice_type := ar_gta_trx_util.get_invoice_type(p_org_id => l_gta_trx.trx_header.org_id
2066 ,p_customer_trx_id=> l_gta_trx.trx_header.ra_trx_id
2067 ,p_trx_line_id=> l_gta_trx.trx_lines(1).ar_trx_line_id
2068 ,p_fp_tax_registration_num => l_fp_reg_num);
2069 l_sum_of_amount := 0;
2070
2071 BEGIN
2072 SELECT
2073 limits.max_amount
2074 , limits.max_num_of_line
2075 INTO
2076 l_max_amount
2077 , l_max_line
2078 FROM
2079 ar_gta_tax_limits_all limits
2080 WHERE limits.fp_tax_registration_number = l_fp_reg_num
2081 AND limits.invoice_type = l_invoice_type
2082 AND limits.org_id = p_org_id;
2083
2084 EXCEPTION
2085 WHEN no_data_found THEN
2086 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2087 THEN
2088 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
2089 , G_MODULE_PREFIX || l_procedure_name
2090 , 'no data found');
2091
2092 --AR_GTA_SYS_CONFIG_MISSING
2093 fnd_message.set_name('AR', 'AR_GTA_SYS_CONFIG_MISSING');
2094 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
2095 , G_MODULE_PREFIX || l_procedure_name
2096 , fnd_message.get());
2097
2098 END IF;
2099 RAISE;
2100 RETURN;
2101 END;
2102
2103 --the following code is added by Yao Zhang for bug 7758496
2104 BEGIN
2105 SELECT
2106 sales_list_flag
2107 INTO
2108 l_sales_list_flag
2109 FROM
2113 EXCEPTION
2110 AR_GTA_RULE_HEADERS_All
2111 WHERE org_id = p_org_id
2112 AND rule_header_id = p_transfer_id;
2114 -- no data found , raise a data error
2115 WHEN no_data_found THEN
2116 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2117 THEN
2118 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
2119 , G_MODULE_PREFIX || l_procedure_name
2120 , 'no data found');
2121
2122 --AR_GTA_SYS_CONFIG_MISSING
2123 fnd_message.set_name('AR', 'AR_GTA_SYS_CONFIG_MISSING');
2124 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
2125 , G_MODULE_PREFIX || l_procedure_name
2126 , fnd_message.get());
2127
2128 END IF;
2129 RAISE;
2130 RETURN;
2131 END;
2132 --add end by yao zhang for bug 7758496
2133
2134 l_lines_number := l_GTA_TRX.trx_lines.COUNT;
2135
2136 l_trx_line_index := l_gta_trx.trx_lines.FIRST;
2137 WHILE l_trx_line_index IS NOT NULL
2138 LOOP
2139 --Yao Modified for bug#9045187
2140 --l_sum_of_amount := l_sum_of_amount + l_GTA_TRX.trx_lines(l_trx_line_index).amount*(-1);
2141 l_sum_of_amount:=l_sum_of_amount+l_GTA_TRX.trx_lines(l_trx_line_index).amount
2142 +nvl(l_GTA_TRX.trx_lines(l_trx_line_index).discount_amount,0);
2143 IF l_GTA_TRX.trx_lines(l_trx_line_index).quantity IS NOT NULL
2144 THEN
2145 IF l_GTA_TRX.trx_lines(l_trx_line_index).quantity > 0
2146 THEN
2147 l_GTA_TRX.trx_lines(l_trx_line_index).quantity := l_GTA_TRX.trx_lines(l_trx_line_index).quantity*(-1);
2148 END IF;/*l_GTA_TRX.trx_lines(l_trx_line_index).quantity > 0*/
2149 END IF;/*l_GTA_TRX.trx_lines(l_trx_line_index).quantity IS NOT NULL*/
2150 l_trx_line_index := l_gta_trx.trx_lines.NEXT(l_trx_line_index);
2151 END LOOP;
2152 -- max amount and max line is mandotrary not null
2153 IF l_max_amount IS NULL OR l_max_line IS NULL
2154 THEN
2155 x_result := FALSE;
2156 END IF; /*l_max_amount IS NULL OR l_max_line IS NULL*/
2157 --IF l_sum_of_amount > l_max_amount OR l_lines_number > l_max_line --delete By Yao Zhang for bug 7644235
2158 IF ABS(l_sum_of_amount) > l_max_amount OR --add By Yao Zhang for bug 7644235
2159 (l_lines_number > l_max_line and l_sales_list_flag='N') --yao zhang changed for bug 7758496
2160
2161 THEN
2162 x_result := FALSE;
2163 END IF;/*l_sum_of_amount > l_max_amount OR l_lines_number > l_max_line*/
2164
2165 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2166 THEN
2167 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2168 , G_MODULE_PREFIX || l_procedure_name
2169 ,'End Procedure. ');
2170 END IF;
2171
2172 EXCEPTION
2173 WHEN OTHERS THEN
2174 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2175 THEN
2176 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
2177 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
2178 , Sqlcode||Sqlerrm);
2179 END IF;
2180 RAISE;
2181 END;
2182
2183
2184 --=============================================================================
2185 -- PROCEDURE NAME:
2186 -- get_max_amount_line
2187 -- TYPE:
2188 -- PUBLIC
2189 --
2190 -- DESCRIPTION:
2191 -- get max line and max amount by fp registration number
2192 -- PARAMETERS:
2193 -- p_gta_trx in AR_GTA_TRX_UTIL.trx_tbl_type
2194 -- p_org_id in number
2195 -- x_max_amount in number
2196 -- x_max_line in number
2197 -- HISTORY:
2198 -- 10-Sep-2005 : Jim.Zheng Create
2199 --=============================================================================
2200 PROCEDURE get_max_amount_line
2201 (p_gta_trx IN AR_Gta_Trx_Util.trx_rec_type
2202 , p_org_id IN NUMBER
2203 , x_max_amount OUT NOCOPY NUMBER
2204 , x_max_line OUT NOCOPY NUMBER)
2205 IS
2206 l_procedure_name VARCHAR2(30) := 'judge_cm_limit';
2207 l_gta_trx AR_Gta_Trx_Util.trx_rec_type;
2208 l_fp_reg_num ar_gta_trx_headers_all.fp_tax_registration_number%TYPE;
2209 l_max_amount NUMBER;
2210 l_max_line NUMBER;
2211 l_error_string VARCHAR2(2000); --added by subba.
2212 BEGIN
2213 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2214 THEN
2215 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2216 , G_MODULE_PREFIX || l_procedure_name
2217 ,'Begin Procedure. ');
2218 END IF;
2219
2220 -- begin log
2221 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2222 THEN
2223 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'Begin get_max_amount_line '||'p_org_id:'||P_org_id);
2224 END IF;
2225 -- end log
2226
2227 l_gta_trx := p_gta_trx;
2228 l_fp_reg_num := l_gta_trx.trx_lines(1).fp_tax_registration_number;
2229
2230 -- begin log
2231 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2232 THEN
2233 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'l_fp_reg_num: '||l_fp_reg_num);
2234 END IF;
2235 -- end log
2236 /* 28/12/07 commented by Subba to change the logic for R12.1
2237 BEGIN
2238 SELECT
2239 limits.max_amount
2240 , limits.max_num_of_line
2241 INTO
2242 l_max_amount
2243 , l_max_line
2244 FROM
2245 ar_gta_tax_limits_all limits
2246 WHERE limits.fp_tax_registration_number = l_fp_reg_num
2247 AND limits.org_id = p_org_id;
2248
2249 EXCEPTION
2250 WHEN no_data_found THEN
2251 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2252 THEN
2253 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
2254 , G_MODULE_PREFIX || l_procedure_name
2255 , 'no data found');
2256
2257 --AR_GTA_SYS_CONFIG_MISSING
2258 fnd_message.set_name('AR', 'AR_GTA_SYS_CONFIG_MISSING');
2259 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
2260 , G_MODULE_PREFIX || l_procedure_name
2261 , fnd_message.get());
2262
2263 END IF;
2264 RAISE;
2265 RETURN;
2269
2266 END;
2267 */ --New logic is added below
2268
2270
2271 l_gta_trx.trx_header.invoice_type := ar_gta_trx_util.get_invoice_type(p_org_id => p_org_id
2272 ,p_customer_trx_id=> l_gta_trx.trx_header.ra_trx_id
2273 ,p_trx_line_id=> l_gta_trx.trx_lines(1).ar_trx_line_id
2274 ,p_fp_tax_registration_num => l_fp_reg_num);
2275
2276 BEGIN
2277 SELECT
2281 l_max_amount
2278 jgtla.max_amount
2279 ,jgtla.max_num_of_line
2280 INTO
2282 , l_max_line
2283 FROM
2284 ar_gta_tax_limits_all jgtla
2285 WHERE jgtla.fp_tax_registration_number = l_fp_reg_num
2286 AND jgtla.invoice_type = l_gta_trx.trx_header.invoice_type
2287 AND jgtla.org_id = p_org_id;
2288
2289
2290
2291 EXCEPTION
2292 WHEN no_data_found THEN
2293
2294 --AR_GTA_SYS_CONFIG_MISSING
2295 fnd_message.set_name('AR', 'AR_GTA_SYS_CONFIG_MISSING');
2296 l_error_string := fnd_message.get();
2297 -- output error
2298 fnd_file.put_line(fnd_file.output, '<?xml version="1.0" encoding="UTF-8" ?>
2299 <TransferReport>
2300 <ReportFailed>Y</ReportFailed>
2301 <ReportFailedMsg>'||l_error_string ||'</ReportFailedMsg>
2302 <TransferReport>');
2303
2304 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2305 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
2306 ,G_MODULE_PREFIX || l_procedure_name
2307 ,'no data found for max_amt and mx_num_line'
2308 );
2309 END IF;
2310 RAISE;
2311 RETURN;
2312 END; -- added by Subba for R12.1
2313
2314
2315 IF l_max_line IS NOT NULL AND l_max_amount IS NOT NULL
2316 THEN
2317 x_max_line := l_max_line;
2318 x_max_amount := l_max_amount;
2319 END IF ; /*l_max_line IS NOT NULL AND l_max_amount IS NOT NULL*/
2320
2321 -- begin log
2322 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2323 THEN
2324 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'End get_max_amount_line:'||l_fp_reg_num);
2325 END IF;
2326 -- end log
2327
2328 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2329 THEN
2330 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2331 , G_MODULE_PREFIX || l_procedure_name
2332 ,'Begin Procedure. ');
2333 END IF;
2334
2335 EXCEPTION
2336 WHEN OTHERS THEN
2337 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2338 THEN
2339 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
2340 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
2341 , Sqlcode||Sqlerrm);
2342 END IF;
2343 RAISE;
2344 END get_max_amount_line;
2345
2346 --=============================================================================
2347 -- PROCEDURE NAME:
2348 -- fileter_credit_memo
2349 -- TYPE:
2350 -- PUBLIC
2351 --
2352 -- DESCRIPTION:
2353 -- filter credit memo which amount and lines number exceeded.
2354 -- PARAMETERS:
2355 -- p_org_id in number
2356 -- p_gta_trx_tbl in ar_gta_trx_util.trx_tbl_type
2357 -- x_gta_trx_tbl out nocopy ar_gta_trx_util.trx_tbl_type
2358 --
2359 -- HISTORY:
2360 -- 10-Sep-2005 : Jim.Zheng Create
2361 -- 23-Jan-2009 : yao zhang changed for bug 7758496
2362 --=============================================================================
2363 PROCEDURE filter_credit_memo
2364 (p_org_id IN NUMBER
2365 , p_transfer_id IN NUMBER--yao zhang changed for bug 7758496
2366 , p_gta_trx_tbl IN ar_gta_trx_util.trx_tbl_type
2367 , x_gta_Trx_tbl OUT NOCOPY ar_gta_trx_util.trx_tbl_type
2368 )
2369 IS
2370 l_index NUMBER;
2371 l_procedure_name VARCHAR2(50) := 'filter_credit_memo';
2372 l_trx_type VARCHAR2(30);
2373 l_trx_reg_tbl ar_gta_trx_util.trx_tbl_type;
2374 l_trx_rate_tbl ar_gta_trx_util.trx_tbl_type;
2375 l_rate_index NUMBER;
2376 l_result BOOLEAN;
2377 l_gta_trx ar_gta_trx_util.trx_rec_type;
2378 l_gta_rate_trx ar_gta_trx_util.trx_rec_type;
2379 l_cm_exceed_limit EXCEPTION;
2380 l_error_string VARCHAR2(500);
2381
2382 BEGIN
2383
2384 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2385 THEN
2389 END IF;
2386 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2387 , G_MODULE_PREFIX || l_procedure_name
2388 ,'Begin Procedure. ');
2390
2391 -- begin log
2392 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2393 THEN
2394 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'Begin filter_credit_memo');
2395 END IF;
2396 -- end log
2397
2398 -- init x_gta_Trx_tbl
2399 x_gta_Trx_tbl := ar_gta_trx_util.trx_tbl_type();
2400
2401 -- begin loop the p_gta_trx_tbl
2402 l_index := p_gta_trx_tbl.FIRST;
2403 WHILE l_index IS NOT NULL
2404 LOOP
2405 BEGIN
2406
2407 l_gta_trx := p_gta_trx_tbl(l_index);
2408 get_Trx_type(p_org_id => p_org_id
2409 , p_gta_trx => l_gta_trx
2410 , x_trx_type => l_trx_type);
2411 IF l_trx_type = 'CM'
2412 THEN
2413
2414 split_trx_by_taxreg_number(p_gta_trx => l_gta_trx
2415 , x_trx_tbl => l_trx_reg_tbl);
2416 split_trx_by_rate(p_gta_tbl => l_trx_reg_tbl
2417 ,x_trx_tbl => l_trx_rate_tbl);
2418
2419 l_rate_index := l_trx_rate_tbl.FIRST;
2420 WHILE l_rate_index IS NOT NULL
2421 LOOP
2422 l_gta_rate_trx := l_trx_rate_tbl(l_rate_index);
2423 judge_cm_limit(p_gta_trx => l_gta_rate_trx
2424 ,p_org_id => p_org_id
2425 , p_transfer_id => p_transfer_id
2426 ,x_result => l_result);
2427 IF l_result = FALSE
2428 THEN
2429 fnd_message.SET_NAME('AR', 'AR_GTA_CRMEMO_EXCEED_LIMIT');
2430 l_error_string := fnd_message.GET();
2431 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2432 THEN
2433 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
2434 , G_MODULE_PREFIX || l_procedure_name
2435 , l_error_string
2436 );
2437 END IF;
2438 RAISE l_cm_exceed_limit;
2439 END IF;
2440 l_rate_index := l_trx_rate_tbl.NEXT(l_rate_index);
2441 END LOOP;/*l_rate_index IS NOT NULL*/
2442 x_gta_trx_tbl.EXTEND;
2443 x_gta_trx_tbl(x_gta_trx_tbl.COUNT) := l_gta_trx;
2444
2445 ELSE /*l_trx_type = 'CM'*/
2446 -- begin log
2447 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2448 THEN
2452
2449 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'Is not a CM');
2450 END IF;
2451 -- end log
2453 x_gta_trx_tbl.EXTEND;
2454 x_gta_trx_tbl(x_gta_trx_tbl.COUNT) := l_gta_trx;
2455 END IF;/*l_trx_type = 'CM'*/
2456
2457
2458 EXCEPTION
2459 WHEN l_cm_exceed_limit
2460 THEN
2461 --delete warning data from ar_gta_transfer_temp
2462 DELETE
2463 ar_gta_transfer_temp temp
2464 WHERE temp.transaction_id = l_gta_trx.trx_header.ra_trx_id
2465 AND temp.succeeded = 'W';
2466
2467 -- error message into temp table.
2468 INSERT INTO
2469 ar_gta_transfer_temp t
2470 (t.seq
2471 , t.transaction_id
2472 , t.succeeded
2473 , t.transaction_num
2474 , t.transaction_type
2475 , t.customer_name
2476 , t.amount
2477 , t.failedreason
2478 , t.gta_invoice_num
2479 )
2480 SELECT
2481 ar_gta_transfer_temp_s.NEXTVAL
2482 , l_gta_trx.trx_header.ra_trx_id
2483 , 'N'
2484 , l_gta_trx.trx_header.ra_trx_number
2485 , 'CM'
2486 , l_gta_trx.trx_header.bill_to_customer_name
2487 , NULL
2488 , l_error_string
2489 , NULL
2490 FROM
2491 dual;
2492 END;
2493 l_index := p_gta_trx_tbl.NEXT(l_index);
2494 END LOOP;/*l_index IS NOT NULL*/
2495
2496 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2497 THEN
2498 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
2499 , G_MODULE_PREFIX || l_procedure_name
2500 ,'Begin Procedure. ');
2501 END IF;
2502
2503 -- begin log
2504 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2505 THEN
2506 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX || l_procedure_name, 'End filter_credit_memo');
2507 END IF;
2508 -- end log
2509
2510 EXCEPTION
2511 WHEN OTHERS THEN
2512 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2513 THEN
2514 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
2515 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
2516 , Sqlcode||Sqlerrm);
2517 END IF;
2518 RAISE;
2519
2520 END filter_credit_memo;
2521
2522
2523 END AR_GTA_SPLIT_TRX_PROC;