[Home] [Help]
PACKAGE BODY: APPS.AR_GTA_CONSOLIDATE_PROC
Source
1 PACKAGE BODY AR_GTA_CONSOLIDATE_PROC AS
2 --$Header: ARGRCONB.pls 120.5.12020000.4 2013/05/15 03:14:34 jixuhuan ship $
3
4 --+===========================================================================
5 --| Copyright (c) 2002 Oracle Corporation
6 --| Redwood Shores, California, USA
7 --| All rights reserved.
8 --+===========================================================================
9 --|
10 --| FILENAME :
11 --| ARRCONB.pls
12 --|
13 --| DESCRIPTION:
14 --| This procedure merge GTA invoice into
15 --| Consolidatation Invoices.
16 --|IMPORTANT NOTES---In this package, consolidation invoice indicate to invocice
17 --| generated from consolidation program, and consolidated
18 --| invoice indicate to invoices which is consolidated.
19 --|
20 --| HISTORY:
21 --| Created : 13-JUN-2009 : Yao Zhang
22 --| 04-Aug-2009 Yao Zhang Fix bug#8756943 TRANSFER AND CONSOLIDATION
23 --| LOGIC FOR CREDIT MEMO WITH DISCOUNT LINES .
24 --| 08-Aug-2009: Yao Zhang fix bug#8770356 Modified
25 --| 19-Aug-2009: Yao Zhang fix bug#8785665 Modify output report of consolidation program
26 --| 24-Aug-2009: Yao Zhang fix bug#8830170 support consolidation invoice lines with
27 --| amount=0 and quantity =0
28 --| 01-Sep-2009: Yao Zhang fix bug#8858364 Modified TST1212.DST2:SAME PRICE AND DIFFERENT
29 --| DISCOUNT CONSOLIDATE ISSUE
30 --| 09-Sep-2009: Yao Zhang fix bug#8882568 CONCURRENCY CONTROL FOR EXPORT AND CONSOLIDATION
31 --| PROGRAM
32 --| 17-Sep-2009: Yao Zhang fix bug#8915838 CONSOLIDATED INVOICE LINE NUMBER EXCEED THE LIMITS
33 --| 17-Sep-2009: Yao Zhang fix bug#8919922 WRONG WARNING MESSAGE APPEARS FOR CONSOLIDATION
34 --| 21-Sep-2009: Yao Zhang Fix bug#8920239 TRANSFER RECYCLE INVOICE WITH DISCOUNT WITH ERROR
35 --| 22-Sep-2009: Yao Zhang fix bug#8930324 SELECT SALE LIST ENABLE BUT NOT CONSOLIDATE SUCCESSFULLY
36 --| 27-Sep-2009: Yao Zhang fix bug#8946609 CONSOLIDATION COUNT LINE NUMBER ISSUE
37 --| 22-Oct-2009: Yao Zhang fix bug#9018341 CONSOLIDATION CUNCURRENT FINISH WITH ERROR IN KOREAN
38 --| SESSION.
39 --| 17-Mar-2010: Yao Zhang fix bug#9362043 DISMATCHED AMOUNT OF CREDIT MEMO CONSOLIDATION IN GTA.
40 --| 10-May-2010: Yao Zhang fix bug#9655856 INVOICE WITH DIFFERENT TAX RATE SHOULD NOT BE CONSOLIDATED
41 --| INTO ONE INVOICE
42 --| 11-Jan-2011: Qiong Liu fix bug#10638369 TAX UNIT PRICE IS RE-CALCULATED AFTER CONSOLIDATION
43 --| 13-Aug-2012: Yao Zhang fix bug# 14462789 - JN CHINA LOCALIZATION - GOLDEN TAX ADAPTOR
44 --| 26-Apr-2013: Jixun Huang modified for ER#16613351 GTA ER-CREDIT MEMO MANUALLY CREATED CAN BE
45 --| CONSOLIDATED WITH AN INVOICE WITH UOM
46 --| 14-May-2013: Jixun Huang modified for Bug#16274922 GTA CONSOLIDATION OUTPUT REPORT AMOUNT DISPLAY ISSUE
47 --+===========================================================================
48
49
50 --==========================================================================
51 -- PROCEDURE NAME:
52 -- Generate_XML_output
53 --
54 -- DESCRIPTION:
55 -- This procedure generate XML string as concurrent output
56 -- from temporary table
57 --
58 -- PARAMETERS:
59 -- In: p_conc_parameters AR_GTA_TRX_UTIL.consolparas_rec_type
60 --
61 -- DESIGN REFERENCES:
62 --
63 --
64 -- CHANGE HISTORY:
65 -- 30-Jun-2009: Yao Zhang Created.
66 -- 19-Aug-2009: Yao Zhang Modified for bug#8785665
67 -- 13-Aug-2012:Yao Zhang Modified for bug# 14462789
68 -- 26-Apr-2013: Jixun Huang modified for Bug#16274922
69 --===========================================================================
70
71 PROCEDURE Generate_XML_Output
72 (p_consolidation_paras IN AR_GTA_TRX_UTIL.consolparas_rec_type
73 )
74 IS
75 l_procedure_name VARCHAR2(30):='Generate_XML_output';
76 l_report_XML XMLType;
77 l_parameter XMLType;
78 l_success XMLType;
79 l_warning XMLType;
80 l_failed XMLType;
81 --Yao Zhang add for bug 14462789
82 l_consol_seq VARCHAR2(30);
83 l_success_invs XMLType;
84 l_warning_invs XMLType;
85 l_failed_invs XMLType;
86 l_success_all XMLType;
87 l_warning_all XMLType;
88 l_failed_all XMLType;
89 -- Yao zhang add end for bug 14462789 ;
90 l_Reportfailed XMLType;
91 l_failedwithparameters XMLType;
92 l_summary XMLType;
93 l_sameprisamedis VARCHAR2(1);
94 l_samepridiffdis VARCHAR2(1);
95 l_diffpri VARCHAR2(1);
96 l_saleslistflag VARCHAR2(1);
97 l_consolidation_id NUMBER;
98 l_succ_unm NUMBER;
99 l_warn_unm NUMBER;
100 l_error_unm NUMBER;
101 l_org_id NUMBER(15);
102 l_org_name hr_all_organization_units_tl.NAME%TYPE;
103 CURSOR c_consol_seq(l_status IN VARCHAR2) IS --Yao Zhang add for bug 14462789
104 SELECT jgct.seq FROM AR_gta_consol_temp jgct WHERE status = l_status;
105 BEGIN
106 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
107 THEN
108 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
109 , G_MODULE_PREFIX ||'.'|| l_procedure_name
110 ,'Begin Procedure. ');
111 END IF;
112 --parameters
113 l_sameprisamedis := p_consolidation_paras.same_pri_same_dis;
114 l_samepridiffdis := p_consolidation_paras.same_pri_diff_dis;
115 l_diffpri := p_consolidation_paras.diff_pri;
116 l_saleslistflag := p_consolidation_paras.sales_list_flag;
117 l_consolidation_id := p_consolidation_paras.consolidation_id;
118 l_org_id := p_consolidation_paras.org_id;
119
120 BEGIN
121 SELECT otl.NAME
122 INTO l_org_name
123 FROM hr_all_organization_units o, hr_all_organization_units_tl otl
124 WHERE o.organization_id = otl.organization_id
125 AND otl.LANGUAGE = userenv('LANG')
126 AND o.organization_id = l_org_id;
127 EXCEPTION
128 WHEN no_data_found THEN
129 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
130 FND_LOG.String(FND_LOG.LEVEL_UNEXPECTED,
131 G_MODULE_PREFIX || l_procedure_name,
132 'Wxception when retrive operating unit name' || SQLCODE || SQLERRM);
133 END IF;
134 RAISE;
135 END;
136
137
138 SELECT COUNT(*)
139 INTO l_succ_unm
140 FROM AR_gta_consol_temp
141 WHERE status= 'S';
142
143 SELECT COUNT(*)
144 INTO l_warn_unm
145 FROM AR_gta_consol_temp
146 WHERE status= 'W';
147
148 SELECT COUNT(*)
149 INTO l_error_unm
150 FROM AR_gta_consol_temp
151 WHERE status= 'E';
152
153 -- generate validate xml string
154 SELECT xmlelement("ReportFailed", 'N') INTO l_Reportfailed FROM dual;
155 SELECT xmlelement("FailedWithParameters", 'N')
156 INTO l_failedwithparameters
157 FROM dual;
158
159 -- generate xmlsring of parameters of transfer program
160 SELECT xmlelement("Parameters",
161 xmlforest(l_sameprisamedis AS "SamePriSameDis",
162 l_samepridiffdis AS "SamePriDiffDis",
163 l_diffpri AS "DiffPri",
164 l_saleslistflag AS "SalesList",
165 l_consolidation_id AS "ConsolidationId",
166 l_org_name AS "OrgName"
167 ))
168 INTO l_parameter
169 FROM dual;
170
171 SELECT xmlelement("Summary",
172 xmlforest(l_succ_unm AS "NumOfSucc",
173 l_warn_unm AS "NumOfWarning",
174 l_error_unm AS "NumOfFailed"))
175 INTO l_summary
176 FROM dual;
177
178
179 -- generate the xmltype for success inv
180 --Yao Zhang add for bug 14462789
181 OPEN c_consol_seq('S');
182 LOOP
183 FETCH c_consol_seq
184 INTO l_consol_seq;
185 EXIT WHEN c_consol_seq%NOTFOUND;
186 --Yao Zhang add end;
187 SELECT xmlagg(xmlelement("ConsolidatedInv",
188 xmlforest(jgcit.consolidated_inv_number AS
189 "Consolidated",
190 jgcit.gl_period AS "GLPeriod",
191 jgcit.ra_trx_num AS "RATrxNum",
192 jgcit.ra_trx_type AS "RATrxType",
193 --modified by Jixun for bug#16274922 begin
194 --jgcit.amount AS "Amount"
195 ar_gta_trx_util.format_monetary_amount(l_org_id, jgcit.amount) AS "Amount"
196 --modified by Jixun for bug#16274922 end
197 )))
198 INTO l_success_invs
199 FROM AR_gta_consol_invs_temp jgcit
200 WHERE jgcit.seq = l_consol_seq;
201 SELECT xmlelement("ConsolidationInv",
202 xmlforest(jgct.seq AS "SEQ",
203 jgct.consolidation_inv_number AS
204 "ConsolidationInvNum",
205 jgct.customer_name AS "CustomerName",
206 jgct.tp_tax_reg_num AS "TPTaxRegNum",
207 jgct.customer_address_phone AS
208 "CustomerAddrPhone",
209 jgct.bank_account_name AS "BankName",
210 jgct.bank_account_num AS
211 "BankAccountNumber",
212 lk.meaning AS "InvoiceType",
213 --modified by Jixun for bug#16274922 begin
214 --jgct.amount AS "Amount",
215 ar_gta_trx_util.format_monetary_amount(l_org_id, jgct.amount) AS "Amount",
216 --modified by Jixun for bug#16274922 end
217 jgct.failed_reason AS "FailedReason",
218 l_success_invs AS "ConsolidatedInvs"))
219 INTO l_success
220 FROM AR_gta_consol_temp jgct, fnd_lookup_values_vl lk
221 WHERE jgct.seq = l_consol_seq
222 AND jgct.invoice_type = lk.lookup_code
223 AND lk.lookup_type = 'AR_GTA_INVOICE_TYPE';
224 SELECT Xmlconcat(l_success_all, l_success)
225 INTO l_success_all
226 FROM dual;
227
228 END LOOP;
229 CLOSE c_consol_seq;
230 -- generate the xmltype for warning inv
231 --Yao Zhang add for bug 14462789
232 OPEN c_consol_seq('W');
233 LOOP
234 FETCH c_consol_seq
235 INTO l_consol_seq;
236 EXIT WHEN c_consol_seq%NOTFOUND;
237 SELECT xmlagg(xmlelement("ConsolidatedInv",
238 xmlforest(jgcit.consolidated_inv_number AS
239 "Consolidated",
240 jgcit.gl_period AS "GLPeriod",
241 jgcit.ra_trx_num AS "RATrxNum",
242 jgcit.ra_trx_type AS "RATrxType",
243 --modified by Jixun for bug#16274922 begin
244 --jgcit.amount AS "Amount"
245 ar_gta_trx_util.format_monetary_amount(l_org_id, jgcit.amount) AS "Amount"
246 --modified by Jixun for bug#16274922 end
247 )))
248 INTO l_warning_invs
249 FROM AR_gta_consol_invs_temp jgcit
250 WHERE jgcit.seq = l_consol_seq;
251 SELECT xmlagg(xmlelement("ConsolidationInv",
252 xmlforest(jgct.SEQ AS "SEQ",
253 jgct.consolidation_inv_number AS
254 "ConsolidationInvNum",
255 jgct.customer_name AS "CustomerName",
256 jgct.tp_tax_reg_num AS "TPTaxRegNum",
257 jgct.customer_address_phone AS
258 "CustomerAddrPhone",
259 jgct.bank_account_name AS "BankName",
260 jgct.bank_account_num AS
261 "BankAccountNumber",
262 lk.meaning AS "InvoiceType",
263 --modified by Jixun for bug#16274922 begin
264 --jgct.amount AS "Amount",
265 ar_gta_trx_util.format_monetary_amount(l_org_id, jgct.amount) AS "Amount",
266 --modified by Jixun for bug#16274922 end
267 jgct.failed_reason AS "FailedReason",
268 l_warning_invs AS "ConsolidatedInvs")))
269
270 INTO l_warning
271 FROM AR_gta_consol_temp jgct, fnd_lookup_values_vl lk
272 WHERE jgct.seq = l_consol_seq
273 AND jgct.invoice_type = lk.lookup_code
274 AND lk.lookup_type = 'AR_GTA_INVOICE_TYPE';
275 SELECT Xmlconcat(l_warning_all, l_warning)
276 INTO l_warning_all
277 FROM dual;
278 END LOOP;
279 CLOSE c_consol_seq;
280 --ORDER BY jgct.seq;
281 -- generate the xmltype for error inv
282 --Yao Zhang add for bug 14462789
283 OPEN c_consol_seq('E');
284 LOOP
285 FETCH c_consol_seq
286 INTO l_consol_seq;
287 EXIT WHEN c_consol_seq%NOTFOUND;
288 SELECT xmlagg(xmlelement("ConsolidatedInv",
289 xmlforest(jgcit.consolidated_inv_number AS
290 "Consolidated",
291 jgcit.gl_period AS "GLPeriod",
292 jgcit.ra_trx_num AS "RATrxNum",
293 jgcit.ra_trx_type AS "RATrxType",
294 --modified by Jixun for bug#16274922 begin
295 --jgcit.amount AS "Amount"
296 ar_gta_trx_util.format_monetary_amount(l_org_id, jgcit.amount) AS "Amount"
297 --modified by Jixun for bug#16274922 end
298 )))
299 INTO l_failed_invs
300 FROM AR_gta_consol_invs_temp jgcit
301 WHERE jgcit.seq = l_consol_seq;
302 SELECT xmlagg(xmlelement("ConsolidationInv",
303 xmlforest(jgct.SEQ AS "SEQ",
304 jgct.consolidation_inv_number AS
305 "ConsolidationInvNum",
306 jgct.customer_name AS
307 "CustomerName",
308 jgct.tp_tax_reg_num AS
309 "TPTaxRegNum",
310 jgct.customer_address_phone AS
311 "CustomerAddrPhone",
312 jgct.bank_account_name AS
313 "BankName",
314 jgct.bank_account_num AS
315 "BankAccountNumber",
316 lk.meaning AS "InvoiceType",
317 --modified by Jixun for bug#16274922 begin
318 --jgct.amount AS "Amount",
319 ar_gta_trx_util.format_monetary_amount(l_org_id, jgct.amount) AS "Amount",
320 --modified by Jixun for bug#16274922 end
321 jgct.failed_reason AS
322 "FailedReason",
323 l_failed_invs AS
324 "ConsolidatedInvs")))
325
326 INTO l_failed
327 FROM AR_gta_consol_temp jgct, fnd_lookup_values_vl lk
328 WHERE jgct.seq = l_consol_seq
329 AND jgct.invoice_type = lk.lookup_code
330 AND lk.lookup_type = 'AR_GTA_INVOICE_TYPE';
331
332 --ORDER BY jgct.seq;
333 SELECT Xmlconcat(l_failed_all, l_failed) INTO l_failed_all FROM dual;
334 END LOOP;
335 CLOSE c_consol_seq;
336 --generate the final report
337 SELECT xmlelement("ConsolidationReport",
338 xmlforest(l_reportfailed AS "ReportFailed",
339 l_failedwithparameters AS
340 "FailedWithParameters",
341 ar_gta_trx_util.to_xsd_date_string(SYSDATE) AS
342 "ReqDate",
343 l_parameter AS "Parameters",
344 l_summary AS "Summary",
345 l_success_all AS "SuccessInvs",
346 l_warning_all AS "WarningInvs",
347 l_failed_all AS "FailedInvs"))
348 INTO l_report_xml
349 FROM dual;
350
351 -- concurrent output
352 AR_GTA_TRX_UTIL.output_conc(l_report_XML.Getclobval);
353
354 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
355 AR_GTA_TRX_UTIL.debug_output_conc(l_report_XML.Getclobval);
356 END IF;
357
358
359 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
360 THEN
361 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
362 , G_MODULE_PREFIX ||'.'|| l_procedure_name
363 ,'End Procedure. ');
364 END IF;
365 EXCEPTION
366 WHEN OTHERS THEN
367 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
368 FND_LOG.String(FND_LOG.LEVEL_UNEXPECTED,
369 G_MODULE_PREFIX || l_procedure_name ||
370 '. OTHER_EXCEPTION ',
371 'Unknown error' || SQLCODE || SQLERRM);
372
373 END IF;
374 RAISE;
375 END Generate_XML_output;
376
377 --=============================================================================
378 -- PROCEDURE NAME:
379 -- create_consol_inv
380 -- TYPE:
381 -- PUBLIC
382 --
383 -- DESCRIPTION: This procedure is used to Consolidate GTA invoices
384 --
385 --
386 -- PARAMETERS:
387 -- IN p_consolidation_paras AR_GTA_TRX_UTIL.consolparas_rec_type
388 --
389 --
390 -- HISTORY:
391 -- 30-Jun-2009 : Yao Zhang Create
392 -- 04-Aug-2009: Yao Zhang Fix bug#8756943 Modified
393 -- 08-Aug-2009: Yao Zhang fix bug#8770356 Modified
394 -- 24-Aug-2009: Yao Zhang fix bug#8830170 Modified
395 -- 01-Sep-2009: Yao Zhang fix bug#8858364 Modified
396 -- 09-Sep-2009: Yao Zhang fix bug#8882568 Modified
397 -- 17-Sep-2009: Yao Zhang fix bug#8915838 Modified
398 -- 17-Sep-2009: Yao Zhang fix bug#8919922 Modified
399 -- 21-Sep-2009: Yao Zhang fix bug#8920239 Modified
400 -- 22-Sep-2009: Yao Zhang fix bug#8930324 Modified
401 -- 27-Sep-2009: Yao Zhang fix bug#8946609 Modified
402 -- 22-Oct-2009: Yao Zhang fix bug#9018341 Modified
403 -- 17-Mar-2010: Yao Zhang fix bug#9362043 Modified
404 -- 10-May-2010: Yao Zhang fix bug#9655856 Modified
405 -- 11-Jan-2011: Qiong Liu fix bug#10638369
406 -- 26-Apr-2013: Jixun Huang modified for ER#16613351
407 --=============================================================================
408 PROCEDURE Create_Consol_Inv
409 (p_consolidation_paras IN AR_GTA_TRX_UTIL.consolparas_rec_type
410 )
411 IS
412 l_procedure_name VARCHAR2(30):='Create_Consol_Inv';
413 l_consolidation_id NUMBER;
414 l_same_pri_same_dis VARCHAR2(1);
415 l_same_pri_diff_dis VARCHAR2(1);
416 l_diff_pri VARCHAR2(1);
417 l_sales_list_flag VARCHAR2(1);
418 l_org_id NUMBER(15); --Yao Zhang add for bug#8770356
419
420 l_csldted_invs ar_gta_trx_util.trx_tbl_type;
421 l_csldted_inv ar_gta_trx_util.trx_rec_type;
422 l_csldted_inv_lines ar_gta_trx_util.trx_line_tbl_type:=ar_gta_trx_util.trx_line_tbl_type();
423 l_csldted_inv_line ar_gta_trx_util.trx_line_rec_type;
424 l_csldted_invs_index NUMBER;
425 l_csldted_inv_lines_index NUMBER;
426
427
428 l_csldtion_inv ar_gta_trx_util.trx_rec_type;
429 l_csldtion_inv_lines ar_gta_trx_util.trx_line_tbl_type:=ar_gta_trx_util.trx_line_tbl_type();
430 l_csldtion_inv_line ar_gta_trx_util.trx_line_rec_type;
431 l_csldtion_inv_lines_index NUMBER;
432 l_csldtion_line_count NUMBER;
433 l_csldtion_discount_line_num NUMBER;
434 l_csldtion_line_num NUMBER;--Yao add for bug#8830170
435
436 --added by jixun for ER#16613351 begin
437 l_csldtion_inv_line1 ar_gta_trx_util.trx_line_rec_type;
438 l_csldtion_inv_lines_index1 NUMBER;
439 --added by jixun for ER#16613351 end
440
441 l_gta_trx_header_id ar_gta_trx_headers_all.gta_trx_header_id%TYPE;
442 l_bill_to_customer_name ar_gta_trx_headers_all.bill_to_customer_name%TYPE;
443 l_tp_tax_registration_number ar_gta_trx_headers_all.tp_tax_registration_number%TYPE;
444 l_fp_tax_registration_number ar_gta_trx_headers_all.fp_tax_registration_number%TYPE;
445 l_customer_address_phone ar_gta_trx_headers_all.customer_address_phone%TYPE;
446 l_bank_account_name ar_gta_trx_headers_all.bank_account_name%TYPE;
447 l_bank_account_number ar_gta_trx_headers_all.bank_account_number%TYPE;
448 l_invoice_type ar_gta_trx_headers_all.invoice_type%TYPE;
449 l_ra_trx_type Varchar2(20);
450 l_sum_amount NUMBER;
451 l_max_amount NUMBER;
452 l_max_line NUMBER;
453
454 l_consol_sign_flag NUMBER;
455 l_csldtion_line_sign_flag NUMBER;
456 l_result_flag VARCHAR2(1);
457
458 l_amount NUMBER;
459 --l_error_string Varchar2(200);--Yao delete for bug#9018341
460 l_error_string FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;--Yao add for bug#9018341
461 l_consolidation_gl_period Varchar2(20);
462 l_consolidation_gl_date DATE;
463 l_csldted_inv_status_flag VARCHAR2(1);--yao add for bug#8882568
464 l_gta_consol_temp_seq NUMBER;
465 l_tax_rate ar_gta_trx_lines_all.tax_rate%TYPE;--Yao add for bug 9655856
466
467 --added by jixun for ER#16613351 begin
468 /*-------------------------------------------------------
469 Store the Transaction Type of Consolidated GTA Invoices.
470 -------------------------------------------------------*/
471 TYPE csldtion_tab_type IS TABLE OF VARCHAR(10);
472 l_csldtion_type csldtion_tab_type := csldtion_tab_type();
473
474 /*-------------------------------------------------------
475 Flag whether exist Credit Memo without UOM and other
476 Invoices with different UOMs in selected GTA Invoices.
477 -------------------------------------------------------*/
478 l_diff_uoms_flag NUMBER;
479 --added by jixun for ER#16613351 end
480
481 CURSOR c_consolidation_groups(p_consolidation_id IN NUMBER)
482 IS
483 SELECT jgth.fp_tax_registration_number,
484 jgth.bill_to_customer_name,
485 jgth.tp_tax_registration_number,
486 jgth.customer_address_phone,
487 jgth.bank_account_name,
488 jgth.bank_account_number,
489 jgth.invoice_type,
490 jgtl.tax_rate --Yao add for bug9655856
491 FROM ar_gta_trx_headers_all jgth, ar_gta_trx_lines_all jgtl
492 WHERE jgth.consolidation_id = p_consolidation_id
493 --Yao add for bug 9655856
494 AND jgth.gta_trx_header_id = jgtl.gta_trx_header_id
495 AND jgth.org_id = jgtl.org_id
496 --Yao add end for bug 9655856
497 GROUP BY jgth.fp_tax_registration_number,
498 jgth.bill_to_customer_name,
499 jgth.tp_tax_registration_number,
500 jgth.customer_address_phone,
501 jgth.bank_account_name,
502 jgth.bank_account_number,
503 jgth.invoice_type,
504 jgtl.tax_rate; --Yao add for bug9655856
505 CURSOR c_consolidated_invs(p_consolidation_id IN NUMBER,
506 p_fp_tax_registration_number IN VARCHAR2,
507 p_bill_to_customer_name IN VARCHAR2,
508 p_tp_tax_registration_number IN VARCHAR2,
509 p_customer_address_phone IN VARCHAR2,
510 p_bank_account_name IN VARCHAR2,
511 p_bank_account_number IN VARCHAR2,
512 p_invoice_type IN VARCHAR2,
513 p_tax_rate IN NUMBER)--Yao add for bug 9655856
514 IS
515 SELECT jgth.gta_trx_header_id
516 FROM ar_gta_trx_headers_all jgth
517 WHERE jgth.consolidation_id = p_consolidation_id
518 AND jgth.fp_tax_registration_number=p_fp_tax_registration_number
519 AND jgth.bill_to_customer_name = p_bill_to_customer_name
520 AND (jgth.tp_tax_registration_number = p_tp_tax_registration_number OR
521 decode(p_tp_tax_registration_number,
522 NULL,
523 jgth.tp_tax_registration_number,
524 p_tp_tax_registration_number) IS NULL)
525 AND (jgth.customer_address_phone = p_customer_address_phone OR
526 decode(p_customer_address_phone,
527 NULL,
528 jgth.customer_address_phone,
529 p_customer_address_phone) IS NULL)
530 AND (jgth.bank_account_name = p_bank_account_name OR
531 decode(p_bank_account_name,
532 NULL,
533 jgth.bank_account_name,
534 p_bank_account_name) IS NULL)
535 AND (jgth.bank_account_number = p_bank_account_number OR
536 decode(p_bank_account_number,
537 NULL,
538 jgth.bank_account_number,
539 p_bank_account_number) IS NULL)
540 AND jgth.invoice_type = p_invoice_type
541 --Yao add for bug 9655856
542
543 AND jgth.gta_trx_header_id = (SELECT jgtl.gta_trx_header_id
544 FROM ar_gta_trx_lines_all jgtl
545 WHERE jgtl.tax_rate=p_tax_rate
546 AND jgth.gta_trx_header_id = jgtl.gta_trx_header_id
547 AND jgth.org_id = jgtl.org_id
548 GROUP BY jgtl.gta_trx_header_id)
549 --Yao add end for bug9655856
550 ORDER BY jgth.gta_trx_number;
551 BEGIN
552 fnd_file.PUT_LINE(fnd_file.LOG,'Begin Procedure.'||l_procedure_name);
553 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
554 THEN
555 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
556 , G_MODULE_PREFIX ||'.'|| l_procedure_name
557 ,'Begin Procedure. ');
558 END IF;
559 --get procedure parameters
560 l_consolidation_id:=p_consolidation_paras.consolidation_id;
561 l_same_pri_same_dis:=p_consolidation_paras.same_pri_same_dis;
562 l_same_pri_diff_dis:=p_consolidation_paras.same_pri_diff_dis;
563 l_diff_pri :=p_consolidation_paras.diff_pri ;
564 l_sales_list_flag :=p_consolidation_paras.sales_list_flag ;
565 l_org_id :=p_consolidation_paras.org_id;--Yao Zhang add for bug#8770356
566
567 OPEN c_consolidation_groups(l_consolidation_id);
568 LOOP
569 FETCH c_consolidation_groups INTO
570 l_fp_tax_registration_number,
571 l_bill_to_customer_name,
572 l_tp_tax_registration_number,
573 l_customer_address_phone,
574 l_bank_account_name,
575 l_bank_account_number,
576 l_invoice_type,
577 l_tax_rate;
578 EXIT WHEN c_consolidation_groups%NOTFOUND;
579
580 BEGIN
581 --get max amount and max num of line
582 SELECT jgtla.max_amount, jgtla.max_num_of_line
583 INTO l_max_amount, l_max_line
584 FROM ar_gta_tax_limits_all jgtla
585 WHERE jgtla.fp_tax_registration_number =
586 l_fp_tax_registration_number
587 AND jgtla.invoice_type = l_invoice_type
588 AND jgtla.org_id = l_org_id;--Yao Zhang add for bug#8770356
589 EXCEPTION
590 WHEN no_data_found THEN
591 --AR_GTA_SYS_CONFIG_MISSING
592 fnd_message.set_name('AR', 'AR_GTA_SYS_CONFIG_MISSING');
593 l_error_string := fnd_message.get();
594 -- output error
595 fnd_file.put_line(fnd_file.output, '<?xml version="1.0" encoding="UTF-8" ?>
596 <ConsolidationReport>
597 <ReportFailed>Y</ReportFailed>
598 <ReportFailedMsg>'||l_error_string ||'</ReportFailedMsg>
599 <COnsolidationReport>');
600
601 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
602 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
603 ,G_MODULE_PREFIX || l_procedure_name
604 ,'no data found for max_amt and mx_num_line'
605 );
606 END IF;
607 RAISE;
608 RETURN;
609 END;
610 --init
611 l_sum_amount:=0;
612 l_csldted_invs:= ar_gta_trx_util.trx_tbl_type();
613 l_csldted_inv:=NULL;
614 l_result_flag:=NULL;
615 l_error_string:=NULL;
616 l_csldted_inv_status_flag:=NULL;--yao add for bug#8882568
617 --Yao add for bug#8919922
618 l_consol_sign_flag:=NULL;
619 l_consolidation_gl_period:=NULL;
620 l_consolidation_gl_date:=NULL;
621 l_csldtion_inv:=NULL;
622 l_csldtion_inv_line:=NULL;
623 -- l_csldtion_line_count:=0;
624 l_csldtion_inv.trx_lines:=ar_gta_trx_util.trx_line_tbl_type();
625 --Yao add end for bug#8919922
626 l_csldtion_inv_line1 := NULL; --added by jixun for ER#16613351
627
628 OPEN c_consolidated_invs(l_consolidation_id
629 ,l_fp_tax_registration_number
630 ,l_bill_to_customer_name
631 ,l_tp_tax_registration_number
632 ,l_customer_address_phone
633 ,l_bank_account_name
634 ,l_bank_account_number
635 ,l_invoice_type
636 ,l_tax_rate);--Yao add for bug 9655856
637 LOOP
638 FETCH c_consolidated_invs
639 INTO l_gta_trx_header_id;
640 EXIT WHEN c_consolidated_invs%NOTFOUND;
641 AR_GTA_TRX_UTIL.get_trx(p_trx_header_id => l_gta_trx_header_id
642 ,x_trx_rec => l_csldted_inv
643 );
644 l_csldted_invs.EXTEND;
645 l_csldted_invs(l_csldted_invs.COUNT):=l_csldted_inv;
646 l_amount:=ar_gta_trx_util.get_gtainvoice_amount(l_gta_trx_header_id);
647 l_sum_amount:=l_sum_amount+l_amount;
648 --yao add for bug#8882568 check invoice status
649 IF l_csldted_inv.trx_header.status<>'DRAFT'
650 THEN
651 l_csldted_inv_status_flag:='1';
652 END IF;
653 --check if there are both positive and negative invoice in the group
654 IF l_consol_sign_flag IS NULL
655 THEN
656 IF l_amount>0 THEN
657 l_consol_sign_flag:=1;
658 ELSIF l_amount<0 THEN
659 l_consol_sign_flag:=-1;
660 END IF;
661 ELSIF (l_amount<0 AND l_consol_sign_flag=1) OR (l_amount>0 AND l_consol_sign_flag=-1)
662 THEN
663 l_consol_sign_flag:=0;
664 END IF;
665
666 --check if there are invoices with different gl_period
667 IF l_consolidation_gl_period IS NULL
668 THEN
669 l_consolidation_gl_period:=l_csldted_inv.trx_header.RA_GL_PERIOD;
670 l_consolidation_gl_date:= l_csldted_inv.trx_header.ra_gl_date;
671 ELSIF (l_consolidation_gl_date<> l_csldted_inv.trx_header.ra_gl_date)
672 THEN
673 IF(l_consolidation_gl_period<>l_csldted_inv.trx_header.RA_GL_PERIOD)
674 THEN
675 l_result_flag:='W';
676 fnd_message.set_name('AR'
677 ,'AR_GTA_CON_DIF_PERIOD');
678 l_error_string:=fnd_message.get();
679 END IF;
680 --consolidation_gl_date should be the latest date of consolidated invoice
681 --consolidation_gl_period should be the latest gl_period of consolidated invoice
682 IF(l_consolidation_gl_date< l_csldted_inv.trx_header.ra_gl_date)
683 THEN
684 l_consolidation_gl_date:= l_csldted_inv.trx_header.ra_gl_date;
685 l_consolidation_gl_period:=l_csldted_inv.trx_header.RA_GL_PERIOD;
686 END IF;
687 END IF;
688 END LOOP;
689 CLOSE c_consolidated_invs;
690
691 --yao add begin for bug#8882568
692 IF l_csldted_inv_status_flag='1'
693 THEN
694 l_result_flag:='E';
695 fnd_message.set_name('AR'
696 ,'AR_GTA_INV_STATUS_INVALID');
697 l_error_string:=fnd_message.get();
698 --yao add end for bug#8882568
699 ELSIF l_csldted_invs.COUNT<=1
700 THEN
701 l_result_flag:='E';
702 fnd_message.set_name('AR'
703 ,'AR_GTA_FAIL_ONLY_ONE_INV_C');
704 l_error_string:=fnd_message.get();
705 ELSIF ABS(l_sum_amount)>l_max_amount
706 THEN
707 l_result_flag:='E';
708 fnd_message.set_name('AR'
709 ,'AR_GTA_FAIL_EXCEED_LIMMITS');
710 l_error_string:=fnd_message.get();
711 ELSIF(l_consol_sign_flag=0 AND l_sum_amount<=0)
712 THEN
713 l_result_flag:='E';
714 fnd_message.set_name('AR'
715 ,'AR_GTA_CON_FAIL_NEGTIVE');
716 l_error_string:=fnd_message.get();
717 ELSE--ABS(l_sum_amount)>l_max_amount OR (l_consol_sign_flag=0 AND l_sum_amount<0) and
718 --l_csldted_invs.COUNT<=1 create consolidation invoice
719 l_csldted_invs_index:=l_csldted_invs.FIRST;
720
721 --added by jixun begin
722 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
723 fnd_log.string(fnd_log.level_procedure,
724 g_module_prefix || '.' || l_procedure_name,
725 'Consolidation process at the first time: begin');
726 END IF;
727 --added by jixun end
728
729 WHILE l_csldted_invs_index IS NOT NULL
730 LOOP
731 --init
732 l_csldted_inv:=l_csldted_invs(l_csldted_invs_index);
733
734 --added by jixun for ER#16613351 begin
735 /*-------------------------------------------------------------------------------------
736 Get amount of each GTA Invoice to judge whether it is a Credit Memo or Positive Invoice
737 --------------------------------------------------------------------------------------*/
738 l_amount := ar_gta_trx_util.get_gtainvoice_amount(l_csldted_inv.trx_header.gta_trx_header_id);
739 --added by jixun for ER#16613351 end
740
741 l_csldted_inv_lines:=l_csldted_inv.trx_lines;
742 l_csldted_inv_lines_index:=l_csldted_inv_lines.FIRST;
743 WHILE l_csldted_inv_lines_index IS NOT NULL
744 LOOP
745 --Yao comment begin for bug#8946609
746 /*IF (l_csldtion_line_count+l_csldtion_discount_line_num)<=l_max_line OR l_sales_list_flag='Y'
747 THEN*/
748 --Yao comment end for bug#8946609
749 l_csldted_inv_line:=l_csldted_inv_lines(l_csldted_inv_lines_index);
750 l_csldtion_inv_lines_index:=l_csldtion_inv.trx_lines.FIRST;
751 WHILE l_csldtion_inv_lines_index IS NOT NULL
752 LOOP
753 l_csldtion_inv_line:=l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index);
754
755 --added by jixun begin
756 IF (fnd_log.level_procedure >=
757 fnd_log.g_current_runtime_level) THEN
758 fnd_log.string(fnd_log.level_procedure,
759 g_module_prefix || '.' || l_procedure_name,
760 'l_csldtion_inv_line.inventory_item_id: ' ||
761 l_csldtion_inv_line.inventory_item_id);
762
763 fnd_log.string(fnd_log.level_procedure,
764 g_module_prefix || '.' || l_procedure_name,
765 'l_csldtion_inv_line.item_number: ' ||
766 l_csldtion_inv_line.item_number);
767
768 fnd_log.string(fnd_log.level_procedure,
769 g_module_prefix || '.' || l_procedure_name,
770 'l_csldtion_inv_line.item_description: ' ||
771 l_csldtion_inv_line.item_description);
772
773 fnd_log.string(fnd_log.level_procedure,
774 g_module_prefix || '.' || l_procedure_name,
775 'l_csldtion_inv_line.tax_rate: ' ||
776 l_csldtion_inv_line.tax_rate);
777
778 fnd_log.string(fnd_log.level_procedure,
779 g_module_prefix || '.' || l_procedure_name,
780 'l_csldtion_inv_line.uom: ' ||
781 l_csldtion_inv_line.uom);
782
783 fnd_log.string(fnd_log.level_procedure,
784 g_module_prefix || '.' || l_procedure_name,
785 'l_csldtion_inv_line.item_model: ' ||
786 l_csldtion_inv_line.item_model);
787
788 fnd_log.string(fnd_log.level_procedure,
789 g_module_prefix || '.' || l_procedure_name,
790 'l_csldtion_inv_line.item_tax_denomination: ' ||
791 l_csldtion_inv_line.item_tax_denomination);
792
793 fnd_log.string(fnd_log.level_procedure,
794 g_module_prefix || '.' || l_procedure_name,
795 'l_csldted_inv_line.inventory_item_id: ' ||
796 l_csldted_inv_line.inventory_item_id);
797
798 fnd_log.string(fnd_log.level_procedure,
799 g_module_prefix || '.' || l_procedure_name,
800 'l_csldted_inv_line.item_number: ' ||
801 l_csldted_inv_line.item_number);
802
803 fnd_log.string(fnd_log.level_procedure,
804 g_module_prefix || '.' || l_procedure_name,
805 'l_csldted_inv_line.item_description: ' ||
806 l_csldted_inv_line.item_description);
807
808 fnd_log.string(fnd_log.level_procedure,
809 g_module_prefix || '.' || l_procedure_name,
810 'l_csldted_inv_line.tax_rate: ' ||
811 l_csldted_inv_line.tax_rate);
812
813 fnd_log.string(fnd_log.level_procedure,
814 g_module_prefix || '.' || l_procedure_name,
815 'l_csldted_inv_line.uom: ' ||
816 l_csldted_inv_line.uom);
817
818 fnd_log.string(fnd_log.level_procedure,
819 g_module_prefix || '.' || l_procedure_name,
820 'l_csldted_inv_line.item_model: ' ||
821 l_csldted_inv_line.item_model);
822
823 fnd_log.string(fnd_log.level_procedure,
824 g_module_prefix || '.' || l_procedure_name,
825 'l_csldted_inv_line.item_tax_denomination: ' ||
826 l_csldted_inv_line.item_tax_denomination);
827 END IF;
828 --added by jixun end
829
830 --trx lines with same item description, tax rate, uom, item model and tax denomination can be merged into one line.
831 IF l_csldted_inv_line.INVENTORY_ITEM_ID= l_csldtion_inv_line.INVENTORY_ITEM_ID
832 AND l_csldted_inv_line.item_number=l_csldtion_inv_line.item_number
833 AND l_csldted_inv_line.item_description=l_csldtion_inv_line.item_description
834 AND l_csldted_inv_line.tax_rate=l_csldtion_inv_line.tax_rate
835 AND (l_csldted_inv_line.uom=l_csldtion_inv_line.uom OR (l_csldted_inv_line.uom IS NULL AND l_csldtion_inv_line.uom IS NULL))
836 AND l_csldted_inv_line.item_model =l_csldtion_inv_line.item_model
837 AND l_csldted_inv_line.item_tax_denomination =l_csldtion_inv_line.item_tax_denomination
838 THEN
839 IF (l_diff_pri='Y'OR (l_csldted_inv_line.unit_price=l_csldtion_inv_line.unit_price
840 AND l_same_pri_diff_dis='Y')
841 OR (l_csldted_inv_line.unit_price=l_csldtion_inv_line.unit_price
842 AND nvl(l_csldted_inv_line.discount_rate,0)=nvl(l_csldtion_inv_line.discount_rate,0)
843 AND l_same_pri_same_dis='Y'))
844 THEN
845 --l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).quantity :=l_csldtion_inv_line.quantity+l_csldted_inv_line.quantity;
846 --Yao changed for bug#9362043
847 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).quantity :=nvl(l_csldtion_inv_line.quantity,0)+nvl(l_csldted_inv_line.quantity,0);
848 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).amount :=l_csldtion_inv_line.amount+l_csldted_inv_line.amount;
849 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).discount_amount :=nvl(l_csldtion_inv_line.discount_amount,0)+nvl(l_csldted_inv_line.discount_amount,0);
850 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).tax_amount :=l_csldtion_inv_line.tax_amount+l_csldted_inv_line.tax_amount;
851 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).discount_tax_amount :=nvl(l_csldtion_inv_line.discount_tax_amount,0)+nvl(l_csldted_inv_line.discount_tax_amount,0);
852
853 --added by jixun for ER#16613351 begin
854 /*------------------------------------------------------------------
855 Record the Transaction Type of consolidated GTA Invoices.
856 CM: Consolidated GTA Invoices are all Credit Memo.
857 INV: Consolidated GTA Invoices are all Positive Invoices.
858 MIX: Consolidated GTA Invoices contain both Credit Memo and Positive
859 Invoices
860 ------------------------------------------------------------------*/
861 IF (l_amount >= 0 AND
862 l_csldtion_type(l_csldtion_inv_lines_index) = 'CM') OR
863 (l_amount < 0 AND
864 l_csldtion_type(l_csldtion_inv_lines_index) = 'INV') THEN
865
866 l_csldtion_type(l_csldtion_inv_lines_index) := 'MIX';
867 END IF;
868 --added by jixun for ER#16613351 end
869
870 --yao comment for bug#8830170 begin
871 /* l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).unit_price :=round((l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).amount
872 /l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).quantity),6) ;
873 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).discount_rate :=ABS(round((l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).discount_amount
874 /l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).amount),5));*/
875 --Yao comment for bug#8830170 end
876
877 --Yao comment begin for bug#8946609
878 --The following code is changed by Yao for bug#8930324
879 /*IF (l_csldted_inv_line.discount_flag='1'
880 AND l_csldtion_inv_line.discount_flag IS NULL
881 AND l_consol_sign_flag<>-1)
882 --Comented for bug#8930324
883 --AND (l_csldtion_line_count+l_csldtion_discount_line_num)<l_max_line OR l_sales_list_flag='Y')--Yao add for bug#8915838
884 THEN
885 --add begin for bug#8930324
886 IF (l_csldtion_line_count+l_csldtion_discount_line_num)<l_max_line OR l_sales_list_flag='Y'
887 THEN
888 --l_csldtion_inv_line.discount_flag:='1';--yao comment for bug8858364
889 --yao add for bug#8858364
890 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).discount_flag:='1';
891 l_csldtion_discount_line_num:=l_csldtion_discount_line_num+1;
892 --Yao add for bug#8915838
893 ELSE
894 l_result_flag:='E';
895 fnd_message.set_name('AR'
896 ,'AR_GTA_FAIL_EXCEED_LIMMITS');
897 l_error_string:=fnd_message.get();
898 EXIT;
899 END IF;
900 --add end for bug#8930324
901 --Yao add for bug#8915838 end
902 END IF;/*(l_csldted_inv_line.discount_flag='1'
903 AND l_csldtion_inv_line.discount_flag IS NULL
904 AND l_consol_sign_flag<>-1
905 AND (l_csldtion_line_count+l_csldtion_discount_line_num)<l_max_line)*/
906 --Yao comment end for bug#8946609
907
908 --Yao add begin for bug#8946609
909 IF l_csldted_inv_line.discount_flag='1'
910 THEN
911 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).discount_flag:='1';
912 END IF;
913 --Yao add end for bug#8946609
914
915 EXIT;--l_csldtion_inv_lines_index
916 END IF;/*(p_diff_pri='Y'OR (l_csldtion_line.unit_price=l_csldted_inv_line.unit_price
917 AND p_same_pri_diff_dis='Y')
918 OR (l_csldtion_line.unit_price=l_csldted_inv_line.unit_price
919 AND l_csldtion_line.discount_rate=l_csldted_inv_line.discount_rate
920 AND p_same_pri_diff_dis='Y'))*/
921 END IF;-- l_csldtion_line.item_description=l_csldted_inv_line.item_description
922 l_csldtion_inv_lines_index:=l_csldtion_inv.trx_lines.NEXT(l_csldtion_inv_lines_index);
923 END LOOP;--l_csldtion_inv_lines_index
924 --Yao comment begin for bug#8946609
925 /*IF (l_result_flag IS NULL OR l_result_flag='W')
926 AND l_csldtion_inv_lines_index IS NULL
927 AND ((l_csldtion_line_count+l_csldtion_discount_line_num)<l_max_line --Yao add for bug#8915838
928 OR l_sales_list_flag='Y') --add for bug#8930324*/
929 --Yao comment end for bug#8946609
930
931 IF l_csldtion_inv_lines_index IS NULL--Yao add for bug#8946609
932 THEN
933 --Yao comment begin for bug#8946609
934 /* l_csldtion_line_count := l_csldtion_line_count+1;
935 IF (l_csldted_inv_line.discount_flag='1'AND l_consol_sign_flag<>-1)
936 THEN
937 --add begin for bug#8930324
938 IF ((l_csldtion_line_count+l_csldtion_discount_line_num)<l_max_line OR l_sales_list_flag='Y')--Yao add for bug#8915838
939 THEN
940 l_csldtion_discount_line_num:=l_csldtion_discount_line_num+1;
941 --Yao add for bug#8915838
942 ELSE
943 l_result_flag:='E';
944 fnd_message.set_name('AR'
945 ,'AR_GTA_FAIL_EXCEED_LIMMITS');
946 l_error_string:=fnd_message.get();
947 EXIT;
948 END IF;
949 --add end for bug#8930324
950 --Yao add for bug#8915838 end
951 END IF;/*(l_csldted_inv_line.discount_flag='1'
952 AND l_consol_sign_flag<>-1*/
953 --Yao comment end for bug#8946609
954 l_csldtion_inv_line.org_id := l_csldted_inv_line.org_id;
955 --l_csldtion_inv_line.line_number := l_csldtion_line_count;
956 l_csldtion_inv_line.inventory_item_id := l_csldted_inv_line.inventory_item_id;
957 l_csldtion_inv_line.item_number := l_csldted_inv_line.item_number;
958 l_csldtion_inv_line.item_description := l_csldted_inv_line.item_description;
959 l_csldtion_inv_line.item_model := l_csldted_inv_line.item_model;
960 l_csldtion_inv_line.item_tax_denomination := l_csldted_inv_line.item_tax_denomination;
961 l_csldtion_inv_line.tax_rate := l_csldted_inv_line.tax_rate;
962 l_csldtion_inv_line.uom := l_csldted_inv_line.uom;
963 l_csldtion_inv_line.uom_name := l_csldted_inv_line.uom_name;
964 l_csldtion_inv_line.quantity := l_csldted_inv_line.quantity;
965 l_csldtion_inv_line.price_flag := l_csldted_inv_line.price_flag;
966 l_csldtion_inv_line.unit_price := l_csldted_inv_line.unit_price ;
967 l_csldtion_inv_line.amount := l_csldted_inv_line.amount;
968 l_csldtion_inv_line.tax_amount := l_csldted_inv_line.tax_amount;
969 l_csldtion_inv_line.discount_flag := l_csldted_inv_line.discount_flag;
970 l_csldtion_inv_line.enabled_flag := l_csldted_inv_line.enabled_flag;
971 l_csldtion_inv_line.last_update_date :=SYSDATE;
972 --l_csldtion_inv_line.last_updated_by := fnd_global.LOGIN_ID();--Qiong fix bug 10638369
973 l_csldtion_inv_line.last_updated_by := fnd_global.USER_ID();
974 l_csldtion_inv_line.creation_date :=SYSDATE;
975 --l_csldtion_inv_line.created_by := fnd_global.LOGIN_ID();
976 l_csldtion_inv_line.created_by := fnd_global.USER_ID();--Qiong fix bug 10638369
977 l_csldtion_inv_line.last_update_login := fnd_global.LOGIN_ID();
978 l_csldtion_inv_line.program_id := fnd_global.CONC_PROGRAM_ID;
979 l_csldtion_inv_line.PROGRAM_APPLICATON_ID := fnd_global.PROG_APPL_ID();
980 l_csldtion_inv_line.PROGRAM_UPDATE_DATE :=SYSDATE;
981 l_csldtion_inv_line.request_id := fnd_global.CONC_REQUEST_ID();
982 l_csldtion_inv_line.discount_tax_amount :=l_csldted_inv_line.discount_tax_amount;
983 l_csldtion_inv_line.discount_amount :=l_csldted_inv_line.discount_amount;
984 l_csldtion_inv_line.discount_rate :=l_csldted_inv_line.discount_rate;
985 l_csldtion_inv.trx_lines.EXTEND;
986 l_csldtion_inv.trx_lines(l_csldtion_inv.trx_lines.COUNT) := l_csldtion_inv_line;
987
988 --added by jixun for ER#16613351 begin
989 /*------------------------------------------------------------------
990 Record the Transaction Type of first Consolidated GTA Invoice
991 CM: Transaction Type of first Consolidated GTA Invoice is Credit Memo.
992 INV: Transaction Type of first Consolidated GTA Invoice is Positive Invoices.
993 ------------------------------------------------------------------*/
994 l_csldtion_type.EXTEND;
995 IF l_amount < 0 THEN
996 l_csldtion_type(l_csldtion_inv.trx_lines.COUNT) := 'CM';
997 ELSE
998 l_csldtion_type(l_csldtion_inv.trx_lines.COUNT) := 'INV';
999 END IF;
1000 --added by jixun for ER#16613351 end
1001
1002 --Yao comment begin for bug#8946609
1003 --Yao add for bug#8915838 begin
1004 /*ELSIF l_result_flag='E'
1005 OR (l_csldtion_inv_lines_index IS NULL
1006 AND (l_csldtion_line_count+l_csldtion_discount_line_num)=l_max_line
1007 AND l_sales_list_flag='N')
1008 THEN
1009 l_result_flag:='E';
1010 fnd_message.set_name('AR'
1011 ,'AR_GTA_FAIL_EXCEED_LIMMITS');
1012 l_error_string:=fnd_message.get();
1013 --Yao add for bug#8915838 end
1014 EXIT;--l_consol_inv_lines_index*/
1015 --Yao comment end for bug#8946609
1016
1017 END IF;--l_csldtion_inv_lines_index IS NULL AND (l_csldtion_line_count+l_csldtion_discount_line_num)<l_max_line
1018 --Yao comment begin for bug#8946609
1019 /*ELSE--(l_csldted_line_count<l_max_line)OR p_sales_list_flag='Y';
1020 l_result_flag:='E';
1021 fnd_message.set_name('AR'
1022 ,'AR_GTA_FAIL_EXCEED_LIMMITS');
1023 l_error_string:=fnd_message.get();
1024 EXIT;--l_consol_inv_lines_index
1025 END IF;--(l_csldted_line_count<l_max_line)OR p_sales_list_flag='Y';*/
1026 --Yao comment end for bug#8946609
1027 l_csldted_inv_lines_index:=l_csldted_inv_lines.NEXT(l_csldted_inv_lines_index);
1028 END LOOP;--l_consol_inv_lines_index
1029 --Yao comment begin for bug#8946609
1030 /* IF l_result_flag='E'
1031 THEN
1032 EXIT;--l_csldted_invs_index
1033 END IF;--l_result_flag:='E'*/
1034 --Yao comment end for bug#8946609
1035 l_csldted_invs_index:=l_csldted_invs.NEXT(l_csldted_invs_index);
1036 END LOOP; -- l_consol_invs_index
1037
1038 --added by jixun begin
1039 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1040 fnd_log.string(fnd_log.level_procedure,
1041 g_module_prefix || '.' || l_procedure_name,
1042 'Consolidation process at the first time: end');
1043 END IF;
1044 --added by jixun end
1045
1046 --added by Jixun for ER#16613351 begin
1047 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1048 fnd_log.string(fnd_log.level_procedure,
1049 g_module_prefix || '.' || l_procedure_name,
1050 'Consolidation process at the second time: begin');
1051 END IF;
1052
1053 /*---------------------------------------------------------------------------
1054 For same item, Credit Memos without UOM can be consolidated with Positive
1055 Invoices with same UOM, but if there are different UOMs in selected positive
1056 invoices, it will consolidate fail.
1057 ----------------------------------------------------------------------------*/
1058 l_csldtion_inv_lines_index := l_csldtion_inv.trx_lines.first;
1059 WHILE l_csldtion_inv_lines_index IS NOT NULL LOOP
1060 l_csldtion_inv_line := l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index);
1061
1062 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1063 fnd_log.string(fnd_log.level_procedure,
1064 g_module_prefix || '.' || l_procedure_name,
1065 'l_csldtion_inv_lines_index: ' ||
1066 l_csldtion_inv_lines_index);
1067
1068 fnd_log.string(fnd_log.level_procedure,
1069 g_module_prefix || '.' || l_procedure_name,
1070 'l_csldtion_type(' || l_csldtion_inv_lines_index ||
1071 '): ' ||
1072 l_csldtion_type(l_csldtion_inv_lines_index));
1073
1074 fnd_log.string(fnd_log.level_procedure,
1075 g_module_prefix || '.' || l_procedure_name,
1076 'l_csldtion_inv_line.UOM: ' ||
1077 l_csldtion_inv_line.UOM);
1078 END IF;
1079
1080 /*-------------------------------------------------------
1081 Only credit memos without UOM can be consolidated.
1082 -------------------------------------------------------*/
1083 IF l_csldtion_type(l_csldtion_inv_lines_index) = 'CM' AND
1084 l_csldtion_inv_line.UOM IS NULL THEN
1085
1086 l_diff_uoms_flag := 0;
1087 l_csldtion_inv_lines_index1 := l_csldtion_inv.trx_lines.first;
1088 WHILE l_csldtion_inv_lines_index1 IS NOT NULL LOOP
1089 IF l_csldtion_inv_lines_index <> l_csldtion_inv_lines_index1 THEN
1090 l_csldtion_inv_line1 := l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index1);
1091
1092 IF l_csldtion_inv_line1.INVENTORY_ITEM_ID =
1093 l_csldtion_inv_line.INVENTORY_ITEM_ID AND
1094 l_csldtion_inv_line1.item_number =
1095 l_csldtion_inv_line.item_number AND
1096 l_csldtion_inv_line1.item_description =
1097 l_csldtion_inv_line.item_description AND
1098 l_csldtion_inv_line1.tax_rate =
1099 l_csldtion_inv_line.tax_rate AND
1100 l_csldtion_inv_line1.item_model =
1101 l_csldtion_inv_line.item_model AND
1102 l_csldtion_inv_line1.item_tax_denomination =
1103 l_csldtion_inv_line.item_tax_denomination THEN
1104 IF (l_diff_pri = 'Y' OR (l_csldtion_inv_line1.unit_price =
1105 l_csldtion_inv_line.unit_price AND
1106 l_same_pri_diff_dis = 'Y') OR
1107 (l_csldtion_inv_line1.unit_price =
1108 l_csldtion_inv_line.unit_price AND
1109 nvl(l_csldtion_inv_line1.discount_rate, 0) =
1110 nvl(l_csldtion_inv_line.discount_rate, 0) AND
1111 l_same_pri_same_dis = 'Y')) THEN
1112
1113 IF (fnd_log.level_procedure >=
1114 fnd_log.g_current_runtime_level) THEN
1115
1116 fnd_log.string(fnd_log.level_procedure,
1117 g_module_prefix || '.' ||
1118 l_procedure_name,
1119 'l_csldtion_inv_lines_index1: ' ||
1120 l_csldtion_inv_lines_index1);
1121
1122 fnd_log.string(fnd_log.level_procedure,
1123 g_module_prefix || '.' ||
1124 l_procedure_name,
1125 'l_csldtion_type(' ||
1126 l_csldtion_inv_lines_index1 || '): ' ||
1127 l_csldtion_type(l_csldtion_inv_lines_index1));
1128
1129 fnd_log.string(fnd_log.level_procedure,
1130 g_module_prefix || '.' ||
1131 l_procedure_name,
1132 'l_diff_uoms_flag: ' ||
1133 l_diff_uoms_flag);
1134 END IF; --fnd_log.level_procedure >= fnd_log.g_current_runtime_level
1135
1136 /*------------------------------------------------------------------
1137 Only positive invoices with same UOM can be consolidated with credit
1138 memos without UOM, if positive invoices with different UOMs, it will
1139 consolidate fail.
1140 ------------------------------------------------------------------*/
1141 IF l_csldtion_type(l_csldtion_inv_lines_index1) IN
1142 ('INV', 'MIX') THEN
1143 l_diff_uoms_flag := l_diff_uoms_flag + 1;
1144 END IF;
1145 IF l_diff_uoms_flag > 1 THEN
1146 l_result_flag := 'E';
1147 fnd_message.set_name('AR', 'AR_GTA_CON_AR_DIFF_UOM');
1148 l_error_string := fnd_message.get();
1149 EXIT;
1150 ELSIF l_diff_uoms_flag = 1 AND (l_csldtion_type(l_csldtion_inv_lines_index1) IN
1151 ('INV', 'MIX')) THEN
1152 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).quantity := nvl(l_csldtion_inv_line.quantity,
1153 0) +
1154 nvl(l_csldtion_inv_line1.quantity,
1155 0);
1156 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).amount := l_csldtion_inv_line.amount +
1157 l_csldtion_inv_line1.amount;
1158 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).discount_amount := nvl(l_csldtion_inv_line.discount_amount,
1159 0) +
1160 nvl(l_csldtion_inv_line1.discount_amount,
1161 0);
1162 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).tax_amount := l_csldtion_inv_line.tax_amount +
1163 l_csldtion_inv_line1.tax_amount;
1164 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).discount_tax_amount := nvl(l_csldtion_inv_line.discount_tax_amount,
1165 0) +
1166 nvl(l_csldtion_inv_line1.discount_tax_amount,
1167 0);
1168 IF l_csldtion_inv_line1.discount_flag = '1' THEN
1169 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).discount_flag := '1';
1170 END IF;
1171 /*----------------------------------------------------------------------------------
1172 After credit memos without UOM and positive invoices with same UOM are consolidated,
1173 the UOM of new consolidated GTA Line should be the same with that of positive invoice.
1174 ----------------------------------------------------------------------------------*/
1175 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).uom := l_csldtion_inv_line1.uom;
1176
1177 l_csldtion_inv.trx_lines.delete(l_csldtion_inv_lines_index1);
1178 END IF; --l_diff_uoms_flag > 1
1179
1180 END IF;
1181 END IF;
1182
1183 END IF; --l_csldtion_inv_lines_index <> l_csldtion_inv_lines_index1
1184 l_csldtion_inv_lines_index1 := l_csldtion_inv.trx_lines.NEXT(l_csldtion_inv_lines_index1);
1185 END LOOP; --l_csldtion_inv_lines_index1 IS NOT NULL
1186
1187 IF l_diff_uoms_flag > 1 THEN
1188 EXIT;
1189 END IF;
1190 END IF; --l_csldtion_type(l_csldtion_inv_lines_index) = 'CM'
1191 l_csldtion_inv_lines_index := l_csldtion_inv.trx_lines.NEXT(l_csldtion_inv_lines_index);
1192 END LOOP; --l_csldtion_inv_lines_index IS NOT NULL
1193
1194 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1195 fnd_log.string(fnd_log.level_procedure,
1196 g_module_prefix || '.' || l_procedure_name,
1197 'l_diff_uoms_flag: ' || l_diff_uoms_flag);
1198
1199 fnd_log.string(fnd_log.level_procedure,
1200 g_module_prefix || '.' || l_procedure_name,
1201 'Consolidation process at the second time: end');
1202 END IF;
1203
1204 IF (l_result_flag IS NULL OR l_result_flag <> 'E') THEN
1205 --added by Jixun for ER#16613351 end
1206
1207 --Yao Zhang add for new consolidation logic, the consolidation invoice should be either posotive invoice
1208 --or credit memo, there should not be postive lines and negtive lines in one invoice.
1209 --Yao comment begin for bug#8946609
1210 /* IF l_result_flag IS NULL OR l_result_flag='W'
1211 THEN*/
1212 --Yao comment end for bug#8946609
1213 l_csldtion_inv_lines_index:=l_csldtion_inv.trx_lines.FIRST;
1214 l_csldtion_line_num:=0;
1215 l_csldtion_discount_line_num:=0;--Yao add for bug#8946609
1216 WHILE l_csldtion_inv_lines_index IS NOT NULL
1217 LOOP
1218 --yao zhang add for bug8830170 begin
1219 --Yao modified for bug9362043
1220 IF -- l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).quantity<>0 AND
1221 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).amount<>0
1222 THEN
1223 l_csldtion_line_num:=l_csldtion_line_num+1;
1224 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).line_number:=l_csldtion_line_num;
1225 --Yao added for bug9362043
1226 IF l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).quantity=0
1227 THEN
1228 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).quantity:=NULL;
1229 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).unit_price :=NULL;
1230 ELSE
1231 /* l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).unit_price :=round(l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).amount
1232 /l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).quantity,6);*/
1233 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).unit_price :=round(round(l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).amount,2)
1234 /l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).quantity,6);
1235 --Qiong changed for bug 10638369
1236 END IF;
1237 --Yao added end for bug9362043
1238
1239 --if discoutn _amount or discount tax amount is 0, set it to null.
1240 IF l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).discount_amount=0
1241 AND l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).discount_tax_amount=0
1242 THEN
1243 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).discount_amount:=NULL;
1244 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).discount_tax_amount:=NULL; --Yao add for bug#8920239
1245 END IF;
1246 --Yao delete begin for bug#8920239
1247 /*IF l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).discount_tax_amount=0
1248 THEN
1249 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).discount_tax_amount:=NULL;
1250 END IF;*/
1251 --Yao delete end for bug#8920239
1252 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).discount_rate :=-1*round((l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).discount_amount
1253 /l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).amount),5);
1254 --Yao add begin for bug#8946609
1255 IF l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).discount_flag='1'
1256 AND l_consol_sign_flag<>-1
1257 THEN
1258 l_csldtion_discount_line_num:=l_csldtion_discount_line_num+1;
1259 END IF;
1260 --Yao add end for bug#8946609
1261
1262 IF l_csldtion_line_sign_flag IS NULL
1263 THEN
1264 IF l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).amount>0
1265 THEN l_csldtion_line_sign_flag:=1;
1266 ELSE l_csldtion_line_sign_flag:=-1;
1267 END IF;
1268 ELSIF (l_csldtion_line_sign_flag=1 AND l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).amount<0)
1269 OR (l_csldtion_line_sign_flag=-1 AND l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).amount>0)
1270 THEN
1271 l_result_flag:='E';
1272 fnd_message.set_name('AR'
1273 ,'AR_GTA_CON_NEG_INV_LINES');
1274 l_error_string:=fnd_message.get();
1275 EXIT;
1276 END IF;
1277 ELSE /*l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).quantity<>0
1278 AND l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).amount<>0*/
1279 l_csldtion_inv.trx_lines.DELETE(l_csldtion_inv_lines_index);
1280 END IF;/*l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).quantity<>0
1281 AND l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).amount<>0*/
1282 --yao zhang add for bug8830170 end
1283 l_csldtion_inv_lines_index:= l_csldtion_inv.trx_lines.NEXT(l_csldtion_inv_lines_index);
1284 END LOOP;
1285 --END IF;--l_result_flag IS NULL OR l_result_flag='W'--Yao comment for bug#8946609
1286 END IF; --l_result_flag IS NULL OR l_result_flag <> 'E' added by jixun for ER#16613351
1287 --Yao Zhang add end
1288 --Yao add begin for bug#8946609
1289 --check line number and discount count line number of invoice
1290 IF (l_result_flag IS NULL OR l_result_flag='W')
1291 AND l_csldtion_discount_line_num+l_csldtion_line_num>l_max_line
1292 AND l_sales_list_flag<>'Y'
1293 THEN
1294 l_result_flag:='E';
1295 fnd_message.set_name('AR'
1296 ,'AR_GTA_FAIL_EXCEED_LIMMITS');
1297 l_error_string:=fnd_message.get();
1298 END IF;
1299 --Yao add end for bug#8946609
1300
1301
1302 --consolidation is successful
1303 IF l_result_flag IS NULL OR l_result_flag='W'
1304 THEN
1305 l_result_flag:=nvl(l_result_flag,'S');
1306 --new header sequence
1307 SELECT ar_gta_trx_headers_all_s.NEXTVAL
1308 INTO l_csldtion_inv.trx_header.gta_trx_header_id
1309 FROM dual;
1310 --copy trx header information
1311 l_csldtion_inv.trx_header.ra_gl_date :=l_csldted_inv.trx_header.ra_gl_date;
1312 l_csldtion_inv.trx_header.ra_gl_period :=l_csldted_inv.trx_header.ra_gl_period;
1313 l_csldtion_inv.trx_header.set_of_books_id :=l_csldted_inv.trx_header.set_of_books_id;
1314 l_csldtion_inv.trx_header.bill_to_customer_id :=l_csldted_inv.trx_header.bill_to_customer_id;
1315 l_csldtion_inv.trx_header.bill_to_customer_number :=l_csldted_inv.trx_header.bill_to_customer_number;
1316 l_csldtion_inv.trx_header.bill_to_customer_name :=l_bill_to_customer_name;
1317 l_csldtion_inv.trx_header.SOURCE :='AR';
1318 l_csldtion_inv.trx_header.org_id :=l_csldted_inv.trx_header.org_id;
1319 l_csldtion_inv.trx_header.version :='1';
1320 l_csldtion_inv.trx_header.latest_version_flag :='Y';
1321 -- l_csldtion_inv.trx_header.group_number :='0';
1322 l_csldtion_inv.trx_header.transaction_date :=l_csldted_inv.trx_header.transaction_date;
1323 l_csldtion_inv.trx_header.customer_address :=l_csldted_inv.trx_header.customer_address;
1324 l_csldtion_inv.trx_header.customer_phone :=l_csldted_inv.trx_header.customer_phone ;
1325 l_csldtion_inv.trx_header.customer_address_phone :=l_customer_address_phone;
1326 l_csldtion_inv.trx_header.bank_account_name :=l_bank_account_name;
1327 l_csldtion_inv.trx_header.bank_account_number :=l_bank_account_number;
1328 l_csldtion_inv.trx_header.bank_account_name_number :=l_csldted_inv.trx_header.bank_account_name_number;
1329 l_csldtion_inv.trx_header.status :='DRAFT';
1330 l_csldtion_inv.trx_header.sales_list_flag :=l_sales_list_flag;
1331 l_csldtion_inv.trx_header.cancel_flag :='N';
1332 l_csldtion_inv.trx_header.legal_entity_id :=l_csldted_inv.trx_header.legal_entity_id;
1333 l_csldtion_inv.trx_header.fp_tax_registration_number :=l_fp_tax_registration_number;
1334 l_csldtion_inv.trx_header.tp_tax_registration_number :=l_tp_tax_registration_number;
1335 l_csldtion_inv.trx_header.request_id := fnd_global.CONC_REQUEST_ID();
1336 l_csldtion_inv.trx_header.program_application_id := fnd_global.PROG_APPL_ID();
1337 l_csldtion_inv.trx_header.program_id := fnd_global.CONC_PROGRAM_ID;
1338 l_csldtion_inv.trx_header.program_update_date := SYSDATE;
1339 l_csldtion_inv.trx_header.creation_date := SYSDATE;
1340 -- l_csldtion_inv.trx_header.created_by := fnd_global.LOGIN_ID();
1341 l_csldtion_inv.trx_header.created_by := fnd_global.USER_ID();--Qiong fix bug 10638369
1342 l_csldtion_inv.trx_header.last_update_date := SYSDATE;
1343 -- l_csldtion_inv.trx_header.last_updated_by := fnd_global.LOGIN_ID();
1344 l_csldtion_inv.trx_header.last_updated_by := fnd_global.USER_ID();----Qiong fix bug 10638369
1345 l_csldtion_inv.trx_header.last_update_login := fnd_global.LOGIN_ID();
1346 l_csldtion_inv.trx_header.invoice_type :=l_invoice_type;
1347 l_csldtion_inv.trx_header.consolidation_flag :='0';
1348 --generate group number for consolidation invoice
1349 BEGIN
1350 SELECT MAX(group_number)+1
1351 INTO l_csldtion_inv.trx_header.group_number
1352 FROM ar_gta_trx_headers_all jgth
1353 WHERE jgth.gta_trx_number LIKE l_csldted_inv.trx_header.ra_trx_id||'-%';
1354 EXCEPTION
1355 WHEN no_data_found THEN
1356 l_csldtion_inv.trx_header.group_number:=1;
1357 END;
1358 l_csldtion_inv.trx_header.gta_trx_number := l_csldted_inv.trx_header.ra_trx_id
1359 || '-'
1360 || l_csldtion_inv.trx_header.group_number
1361 || '-'
1362 || l_csldtion_inv.trx_header.version;
1363
1364 l_csldtion_inv_lines_index:=l_csldtion_inv.trx_lines.FIRST;
1365 WHILE l_csldtion_inv_lines_index IS NOT NULL
1366 LOOP
1367 SELECT ar_gta_trx_lines_all_s.NEXTVAL
1368 INTO l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).gta_trx_line_id
1369 FROM dual;
1370 l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).gta_trx_header_id
1371 :=l_csldtion_inv.trx_header.gta_trx_header_id;
1372 l_csldtion_inv_lines_index:= l_csldtion_inv.trx_lines.NEXT(l_csldtion_inv_lines_index);
1373 END LOOP;
1374 --create consolidation inv
1375 AR_GTA_TRX_UTIL.create_trx(l_csldtion_inv);
1376
1377 l_csldted_invs_index:=l_csldted_invs.FIRST;
1378 WHILE l_csldted_invs_index IS NOT NULL
1379 LOOP
1380 UPDATE ar_gta_trx_headers_all
1381 SET status = 'CONSOLIDATED',
1382 consolidation_flag = '1',
1383 consolidation_trx_num = l_csldtion_inv.trx_header.gta_trx_number
1384 WHERE gta_trx_header_id = l_csldted_invs(l_csldted_invs_index).trx_header.gta_trx_header_id;
1385 l_csldted_invs_index := l_csldted_invs.NEXT(l_csldted_invs_index);
1386 END LOOP;
1387 END IF;--l_result_flag IS NULL OR l_result_flag='W'
1388 END IF;--ABS(l_sum_amount)>l_max_amount OR (l_consol_sign_flag=0 AND l_sum_amount<0) and
1389 --l_csldted_invs.COUNT<=1 create consolidation invoice
1390 SELECT AR_gta_consol_temp_s.NEXTVAL
1391 INTO l_gta_consol_temp_seq
1392 FROM dual;
1393 INSERT INTO AR_gta_consol_temp
1394 (seq
1395 ,status
1396 ,consolidation_inv_number
1397 ,customer_name
1398 ,tp_tax_reg_num
1399 ,customer_address_phone
1400 ,bank_account_name
1401 ,bank_account_num
1402 ,invoice_type
1403 ,amount
1404 ,failed_reason)
1405 SELECT
1406 l_gta_consol_temp_seq
1407 ,l_result_flag
1408 ,l_csldtion_inv.trx_header.gta_trx_number
1409 ,l_bill_to_customer_name
1410 ,l_tp_tax_registration_number
1411 ,l_customer_address_phone
1412 ,l_bank_account_name
1413 ,l_bank_account_number
1414 ,l_invoice_type
1415 ,l_sum_amount
1416 ,l_error_string
1417 FROM dual;
1418 --insert csldted invs to table ar_gta_consol_invs_temp
1419 --init l_csldted_invs_index
1420 l_csldted_invs_index:=l_csldted_invs.FIRST;
1421 WHILE l_csldted_invs_index IS NOT NULL
1422 LOOP
1423 BEGIN
1424 --set consolidation_id to be null for failed gta invoices
1425
1426 IF l_result_flag='E'
1427 THEN
1428 UPDATE ar_gta_trx_headers_all
1429 SET consolidation_id=NULL
1430 WHERE GTA_TRX_HEADER_ID=l_csldted_invs(l_csldted_invs_index).trx_header.gta_trx_header_id;
1431 END IF;
1432 --get ra transaction type and amount
1433 SELECT jgthv.ra_trx_type
1434 ,jgthv.amount
1435 INTO l_ra_trx_type
1436 ,l_amount
1437 FROM AR_GTA_TRX_HEADERS_V jgthv
1438 WHERE jgthv.GTA_TRX_HEADER_ID=l_csldted_invs(l_csldted_invs_index).trx_header.gta_trx_header_id;
1439 EXCEPTION
1440 WHEN no_data_found THEN
1441 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1442 FND_LOG.String(FND_LOG.LEVEL_UNEXPECTED,
1443 G_MODULE_PREFIX || l_procedure_name ||
1444 '. Can not get Transaction Type for GTA Invoice:'
1445 ||l_csldted_invs(l_csldted_invs_index).trx_header.gta_trx_header_id,
1446 'Unknown error' || SQLCODE || SQLERRM);
1447 END IF;
1448 RAISE;
1449 END;
1450 INSERT INTO AR_gta_consol_invs_temp
1451 (seq
1452 ,consolidated_inv_number
1453 ,gl_period
1454 ,ra_trx_num
1455 ,ra_trx_type
1456 ,amount)
1457 SELECT
1458 l_gta_consol_temp_seq
1459 ,l_csldted_invs(l_csldted_invs_index).trx_header.GTA_TRX_NUMBER
1460 ,l_csldted_invs(l_csldted_invs_index).trx_header.ra_gl_period
1461 ,l_csldted_invs(l_csldted_invs_index).trx_header.RA_TRX_NUMBER
1462 ,l_ra_trx_type
1463 ,l_amount
1464 FROM dual;
1465 l_csldted_invs_index:=l_csldted_invs.NEXT(l_csldted_invs_index);
1466 END LOOP;
1467
1468
1469 END LOOP;
1470 CLOSE c_consolidation_groups;
1471
1472 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1473 THEN
1474 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1475 , G_MODULE_PREFIX ||'.'|| l_procedure_name
1476 ,'End Procedure. ');
1477 END IF;
1478 EXCEPTION
1479 WHEN OTHERS THEN
1480 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1481 FND_LOG.String(FND_LOG.LEVEL_UNEXPECTED,
1482 G_MODULE_PREFIX || l_procedure_name ||
1483 '. OTHER_EXCEPTION ',
1484 'Unknown error' || SQLCODE || SQLERRM);
1485
1486 END IF;
1487 UPDATE ar_gta_trx_headers_all
1488 SET consolidation_id = NULL
1489 ,consolidation_trx_num=NULL
1490 ,consolidation_flag=NULL
1491 ,status='DRAFT'
1492 WHERE consolidation_id = l_consolidation_id;
1493 COMMIT;
1494 fnd_file.PUT_LINE(fnd_file.LOG,'Update consolidation id'||l_consolidation_id);
1495 RAISE;
1496 END create_consol_inv;
1497
1498
1499 END AR_GTA_CONSOLIDATE_PROC;