[Home] [Help]
PACKAGE BODY: APPS.JA_CN_CFS_REPORT_PKG
Source
1 PACKAGE BODY Ja_Cn_Cfs_Report_Pkg AS
2 --$Header: JACNCFDB.pls 120.18.12010000.3 2009/01/04 06:30:56 shyan ship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNCFDB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is used to generate the CFS detail report. |
13 --| |
14 --| PROCEDURE LIST |
15 --| |
16 --| PROCEDURE Cfs_Detail_Report PUBLIC |
17 --| |
18 --| HISTORY |
19 --| 27/04/2007 Qingjun Zhao Created |
20 --| 28/02/2008 Arming Chen Fix bug#6751696 |
21 --| 01/03/2008 Xiao Lv Fix bug#6854438 |
22 --| 03/03/2008 Arming Chen Fix bug#6859513 |
23 --| 03/11/2008 Xiao Lv,Arming Chen Fix bug#6697073 |
24 --| 03/27/2008 Arming Chen Fix bug#6920953 |
25 --| 08/09/2008 Yao Zhang Fix bug#7334017 for R12 |
26 --| enhancement|
27 --| 17/10/2008 Yao Zhang fix bug 7487373 |
28 --| DETAIL REPORT SHOW AR/AP |
29 --| NUMBER FOR GL/AGIS DATA |
30 --| 17/10/2008 Yao Zhang Fix bug7487395 DETAIL REPORT|
31 --| SHOW ONE MEANINGLESS BLANK |
32 --| TITLE LINE when fun_amount is 0
33 --| 21/10/2008 Yao Zhang Fix bug #7497957 AP/AR DATA |
34 --| WILL BE SHOWN TWICE WHEN DATA|
35 --| WITH AMOUNT 0 EXIST |
36 --| 21/10/2008 Yao Zhang Fix bug 7488223 |
37 --| DATA COLLECTION PROGRAM |
38 --| COLLECT AGIS DATA BEYOND BSV |
39 --| QUALIFICATION(Detail report |
40 --| should filter data according |
41 --| to bsv) |
42 --| 16/12/2008 Shujuan Yan Fix bug 7626489
43 --+======================================================================*/
44 TYPE NUMBER_TBL IS VARRAY(100) OF NUMBER;
45 TYPE INVOICE_NUM_TBL IS VARRAY(100) OF VARCHAR2(50);
46 --==========================================================================
47 -- PROCEDURE NAME:
48 -- Process_AP_Detail Public
49 --
50 -- DESCRIPTION:
51 -- This procedure is used to get detail infromation for transaction in AR
52 --
53 -- PARAMETERS:
54 -- In: P_LEDGER_ID ID of Ledger
55 -- In: P_AE_HEADER_ID ID of SLA journal header
56 -- In: P_AE_LINE_NUM SLA journal line number
57 -- Out: X_TRX_NUMBER Transaction number
58 -- Out: X_INVOICE_NUMBER Invoice number
59 -- Out: X_THIRD_PARTY_NAME Third Party Name
60 -- Out: X_THIRD_PARTY_NUM Third Party Number
61 --
62 -- DESIGN REFERENCES:
63 -- None
64 --
65 -- CHANGE HISTORY:
66 -- 05/08/2007 Qingjun Zhao Created
67 --===========================================================================
68
69 PROCEDURE Process_Ap_Detail(p_Ledger_Id IN NUMBER,
70 p_Ae_Header_Id IN NUMBER,
71 p_Ae_Line_Num IN NUMBER,
72 x_Trx_Number OUT NOCOPY VARCHAR2,
73
74 -- Fix bug#6697073 begin -------------------------
75 -- x_Invoice_Num OUT NOCOPY VARCHAR2, --updated for bug 6697073
76 x_Func_Amount OUT NOCOPY NUMBER_TBL, --updated for bug 6697073
77
78 x_Invoice_Num OUT NOCOPY INVOICE_NUM_TBL, --updated for bug 6697073
79 -- Fix bug#6697073 end ----------------------------
80
81 x_Third_Party_Name OUT NOCOPY VARCHAR2,
82 x_Third_Party_Num OUT NOCOPY varchar2) IS
83 l_Invoice_Id NUMBER;
84 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
85 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
86 l_Proc_Name VARCHAR2(100) := 'Process_Ap_Detail';
87
88 --Fix bug#6697073 begin----------------------------------------
89 l_Invoice_idx NUMBER:=0;
90 l_invoice_num_temp varchar2(60);
91 l_invoice_num_array INVOICE_NUM_TBL := INVOICE_NUM_TBL();
92 l_func_amount number;
93 l_func_amount_array NUMBER_TBL := NUMBER_TBL();
94
95 cursor c_trx_number is
96 SELECT Aca.Check_Number, Pv.Vendor_Name, Pv.Segment1
97 FROM Ap_Checks_All Aca,
98 Xla_Transaction_Entities Ent,
99 Xla_Ae_Headers Aeh,
100 Po_Vendors Pv
101 WHERE Ent.Application_Id = 200
102 AND Aca.Check_Id = Ent.Source_Id_Int_1
103 AND Aca.Vendor_Id = Pv.Vendor_Id(+)
104 AND Ent.Entity_Code = 'AP_PAYMENTS'
105 AND Ent.Entity_Id = Aeh.Entity_Id
106 AND Aeh.Ae_Header_Id = p_Ae_Header_Id
107 AND Aeh.Ledger_Id = p_Ledger_Id;
108
109 cursor c_invoice_id is
110 SELECT DISTINCT Applied_To_Source_Id_Num_1,
111 DECODE(NVL(UNROUNDED_ACCOUNTED_DR, '-1'),
112 '-1',
113 UNROUNDED_ACCOUNTED_CR,
114 -1 * UNROUNDED_ACCOUNTED_DR)
115 FROM Xla_Distribution_Links
116 WHERE Ae_Header_Id = p_Ae_Header_Id
117 AND Ae_Line_Num = p_Ae_Line_Num;
118 --Fix bug#6697073 end----------------------------------------
119
120
121 BEGIN
122 IF (l_Proc_Level >= l_Dbg_Level) THEN
123 Fnd_Log.STRING(l_Proc_Level,
124 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
125 'Enter procedure');
126 Fnd_Log.STRING(l_Proc_Level,
127 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
128 'P_AE_HEADER_ID ' || p_Ae_Header_Id);
129 Fnd_Log.STRING(l_Proc_Level,
130 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
131 'P_AE_LINE_NUM' || p_Ae_Line_Num);
132 Fnd_Log.STRING(l_Proc_Level,
133 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
134 'P_LEDGER_ID ' || p_Ledger_Id);
135 END IF; --(l_proc_level >= l_dbg_level)
136
137 --Fix bug#6697073 begin-------------------------------------------------------
138 OPEN c_trx_number;
139 FETCH c_trx_number
140 INTO x_Trx_Number, x_Third_Party_Name, x_Third_Party_Num;
141 CLOSE C_trx_number;
142 Fnd_File.Put_Line(Fnd_File.Log,x_Trx_Number||','||x_Third_Party_Name||','|| x_Third_Party_Num);
143
144 /* SELECT Aca.Check_Number, Pv.Vendor_Name, Pv.Segment1
145 INTO x_Trx_Number, x_Third_Party_Name, x_Third_Party_Num
146 FROM Ap_Checks_All Aca,
147 Xla_Transaction_Entities Ent,
148 Xla_Ae_Headers Aeh,
149 Po_Vendors Pv
150 WHERE Ent.Application_Id = 200
151 AND Aca.Check_Id = Ent.Source_Id_Int_1
152 AND Aca.Vendor_Id = Pv.Vendor_Id(+)
153 AND Ent.Entity_Code = 'AP_PAYMENTS'
154 AND Ent.Entity_Id = Aeh.Entity_Id
155 AND Aeh.Ae_Header_Id = p_Ae_Header_Id
156 AND Aeh.Ledger_Id = p_Ledger_Id; */
157
158 --Get invoice id for current SLA journal line
159
160 open C_invoice_id;
161 loop
162 FETCH c_invoice_id
163 INTO l_Invoice_Id,l_func_amount;
164 exit when C_invoice_id%NOTFOUND;
165 l_Invoice_idx:=l_Invoice_idx+1;
166 IF l_invoice_id is not null
167 THEN
168 SELECT Invoice_Num
169 INTO l_invoice_num_temp
170 FROM Ap_Invoices_all
171 WHERE Invoice_Id = l_Invoice_Id;
172 ELSE
173 l_invoice_num_temp:='';
174 END IF;
175 l_invoice_num_array.EXTEND;
176 l_func_amount_array.EXTEND;
177 l_invoice_num_array(l_Invoice_idx):= l_invoice_num_temp;
178 l_func_amount_array(l_Invoice_idx):=l_func_amount;
179
180 Fnd_File.Put_Line(Fnd_File.Log,l_invoice_id);
181 END LOOP;
182 CLOSE C_invoice_id;
183
184 x_Invoice_Num:= l_invoice_num_array;
185 x_Func_Amount:= l_func_amount_array;
186
187 /* SELECT DISTINCT Applied_To_Source_Id_Num_1
188 INTO l_Invoice_Id
189 FROM Xla_Distribution_Links
190 WHERE Ae_Header_Id = p_Ae_Header_Id
191 AND Ae_Line_Num = p_Ae_Line_Num;
192 -- get invoice number for current invoice
193 IF l_invoice_id is not null
194 THEN
195 SELECT Invoice_Num
196 INTO x_Invoice_Num
197 FROM Ap_Invoices
198 WHERE Invoice_Id = l_Invoice_Id;
199 ELSE
200 x_invoice_num:='';
201 END IF;*/
202 -- Fix bug#6697073 end--------------------------------------------------------
203
204 --log for debug
205 IF (l_Proc_Level >= l_Dbg_Level) THEN
206 Fnd_Log.STRING(l_Proc_Level,
207 l_Module_Prefix || '.' || l_Proc_Name || '.end',
208 'Exit procedure');
209 END IF; --( l_proc_level >= l_dbg_level )
210
211 EXCEPTION
212 WHEN OTHERS THEN
213 IF (l_Proc_Level >= l_Dbg_Level) THEN
214 Fnd_Log.STRING(l_Proc_Level,
215 l_Module_Prefix || '.' || l_Proc_Name ||
216 '.Other_Exception AP',
217 SQLCODE || ':' || SQLERRM);
218 END IF; --(l_proc_level >= l_dbg_level)
219 FND_FILE.PUT_LINE(Fnd_File.Log,'AP'||SQLCODE || ':' || SQLERRM);
220 RAISE;
221 END Process_Ap_Detail;
222
223 --==========================================================================
224 -- PROCEDURE NAME:
225 -- Process_Ar_Detail Public
226 --
227 -- DESCRIPTION:
228 -- This procedure is used to get detail infromation for transaction in AR
229 --
230 -- PARAMETERS:
231 -- In: P_LEDGER_ID ID of Ledger
232 -- In: P_AE_HEADER_ID ID of SLA journal header
233 -- In: P_AE_LINE_NUM SLA journal line number
234 -- Out: X_TRX_NUMBER Transaction number
235 -- Out: X_INVOICE_NUMBER Invoice number
236 -- Out: X_THIRD_PARTY_NAME Third Party Name
237 -- Out: X_THIRD_PARTY_NUM Third Party Number
238 --
239 -- DESIGN REFERENCES:
240 -- None
241 --
242 -- CHANGE HISTORY:
243 -- 05/08/2007 Qingjun Zhao Created
244 --===========================================================================
245
246 PROCEDURE Process_Ar_Detail(p_Ledger_Id IN NUMBER,
247 p_Ae_Header_Id IN NUMBER,
248 p_Ae_Line_Num IN NUMBER,
249 x_Trx_Number OUT NOCOPY VARCHAR2,
250 --x_Invoice_Num OUT NOCOPY VARCHAR2, --Fix bug#6697073
251 x_Func_Amount OUT NOCOPY NUMBER_TBL, --Fix bug#6697073
252 x_Invoice_Num OUT NOCOPY INVOICE_NUM_TBL, --Fix bug#6697073
253 x_Third_Party_Name OUT NOCOPY VARCHAR2,
254 x_Third_Party_Num OUT NOCOPY varchar2) IS
255 l_Customer_Trx_Id NUMBER;
256 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
257 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
258 l_Proc_Name VARCHAR2(100) := 'Process_Ar_Detail';
259
260 --Fix bug#6697073 begin-----------------------------------------------------
261 l_Invoice_idx NUMBER:=0;
262 l_invoice_num_temp varchar2(60);
263 l_invoice_num_array INVOICE_NUM_TBL := INVOICE_NUM_TBL();
264 l_func_amount number;
265 l_func_amount_array NUMBER_TBL := NUMBER_TBL();
266 CURSOR c_trx_number is
267 SELECT Aca.Receipt_Number, Hp.Party_Name, Cust.Account_Number
268 FROM Ar_Cash_Receipts_All Aca,
269 Xla_Transaction_Entities Ent,
270 Xla_Ae_Headers Aeh,
271 Hz_Cust_Accounts Cust,
272 Hz_Parties Hp
273 WHERE Ent.Application_Id = 222
274 AND Aca.Cash_Receipt_Id = Ent.Source_Id_Int_1
275 AND Aca.Pay_From_Customer = Cust.Cust_Account_Id(+)
276 AND Cust.Party_Id = Hp.Party_Id(+)
277 AND Ent.Entity_Code = 'RECEIPTS'
278 AND Ent.Entity_Id = Aeh.Entity_Id
279 AND Aeh.Ae_Header_Id = p_Ae_Header_Id
280 AND Aeh.Ledger_Id = p_Ledger_Id;
281 CURSOR c_custom_trx_id is
282 SELECT DISTINCT Applied_To_Source_Id_Num_1 + 0,
283 DECODE(NVL(UNROUNDED_ACCOUNTED_DR, '-1'),
284 '-1',
285 UNROUNDED_ACCOUNTED_CR,
286 -1 * UNROUNDED_ACCOUNTED_DR)
287 FROM Xla_Distribution_Links
288 WHERE Ae_Header_Id = p_Ae_Header_Id
289 AND Ae_Line_Num = p_Ae_Line_Num;
290 --Fix bug#6697073 end-------------------------------------------------------
291
292
293 BEGIN
294 IF (l_Proc_Level >= l_Dbg_Level) THEN
295 Fnd_Log.STRING(l_Proc_Level,
296 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
297 'Enter procedure');
298 Fnd_Log.STRING(l_Proc_Level,
299 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
300 'P_AE_HEADER_ID ' || p_Ae_Header_Id);
301 Fnd_Log.STRING(l_Proc_Level,
302 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
303 'P_AE_LINE_NUM' || p_Ae_Line_Num);
304 Fnd_Log.STRING(l_Proc_Level,
305 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
306 'P_LEDGER_ID ' || p_Ledger_Id);
307 END IF; --(l_proc_level >= l_dbg_level)
308
309 --Fix bug#6697073 begin-------------------------------------------------------
310
311 /* SELECT Aca.Receipt_Number, Hp.Party_Name, Cust.Account_Number
312 INTO x_Trx_Number, x_Third_Party_Name, x_Third_Party_Num
313 FROM Ar_Cash_Receipts_All Aca,
314 Xla_Transaction_Entities Ent,
315 Xla_Ae_Headers Aeh,
316 Hz_Cust_Accounts Cust,
317 Hz_Parties Hp
318 WHERE Ent.Application_Id = 222
319 AND Aca.Cash_Receipt_Id = Ent.Source_Id_Int_1
320 AND Aca.Pay_From_Customer = Cust.Cust_Account_Id(+)
321 AND Cust.Party_Id = Hp.Party_Id(+)
322 AND Ent.Entity_Code = 'RECEIPTS'
323 AND Ent.Entity_Id = Aeh.Entity_Id
324 AND Aeh.Ae_Header_Id = p_Ae_Header_Id
325 AND Aeh.Ledger_Id = p_Ledger_Id; */
326
327 OPEN C_trx_number;
328 FETCH C_trx_number
329 INTO x_Trx_Number, x_Third_Party_Name, x_Third_Party_Num;
330 CLOSE c_trx_number;
331
332 open c_custom_trx_id;
333 loop
334 FETCH c_custom_trx_id
335 INTO l_Customer_Trx_Id,l_func_amount;
336 exit when c_custom_trx_id%NOTFOUND;
337
338 l_Invoice_idx:=l_Invoice_idx+1;
339 IF l_Customer_Trx_Id is not null
340 THEN
341 SELECT Trx_Number
342 INTO l_invoice_num_temp
343 FROM Ra_Customer_Trx_All
344 WHERE Customer_Trx_Id = l_Customer_Trx_Id;
345 ELSE
346 l_invoice_num_temp:='';
347 END IF;
348 l_invoice_num_array.EXTEND;
349 l_func_amount_array.EXTEND;
350 l_invoice_num_array(l_Invoice_idx):= l_invoice_num_temp;
351 l_func_amount_array(l_Invoice_idx):=l_func_amount;
352 END LOOP;
353 CLOSE c_custom_trx_id;
354
355 x_Invoice_Num:= l_invoice_num_array;
356 x_Func_Amount:= l_func_amount_array;
357
358 /*
359 --Get transaction ID
360 SELECT DISTINCT Applied_To_Source_Id_Num_1+0
361 INTO l_Customer_Trx_Id
362 FROM Xla_Distribution_Links
363 WHERE Ae_Header_Id = p_Ae_Header_Id
364 AND Ae_Line_Num = p_Ae_Line_Num;
365 --get invoice number
366 IF L_CUSTOMER_TRX_ID IS NOT NULL
367 THEN
368 SELECT Trx_Number
369 INTO x_Invoice_Num
370 FROM Ra_Customer_Trx_All
371 WHERE Customer_Trx_Id = l_Customer_Trx_Id;
372 ELSE
373 X_INVOICE_NUM:=to_char(null);
374 END IF; --L_CUSTOMER_TRX_ID IS NOT NULL
375 */
376
377
378 --Fix bug#6697073 end---------------------------------------------------------
379
380 --log for debug
381 IF (l_Proc_Level >= l_Dbg_Level) THEN
382 Fnd_Log.STRING(l_Proc_Level,
383 l_Module_Prefix || '.' || l_Proc_Name || '.end',
384 'Exit procedure');
385
386 END IF; --( l_proc_level >= l_dbg_level )
387
388 EXCEPTION
389 WHEN OTHERS THEN
390 IF (l_Proc_Level >= l_Dbg_Level) THEN
391 Fnd_Log.STRING(l_Proc_Level,
392 l_Module_Prefix || '.' || l_Proc_Name ||
393 '.Other_Exception AR',
394 SQLCODE || ':' || SQLERRM);
395 END IF; --(l_proc_level >= l_dbg_level)
396 /*
397 FND_FILE.PUT_LINE(FND_FILE.LOG,
398 'ar' ||sqlcode||sqlerrm);
399 fnd_file.put_line(fnd_file.log,'p_Ledger_Id:'||p_Ledger_Id||',p_Ae_Line_Num'||p_Ae_Line_Num||',p_Ae_Header_Id'||p_Ae_Header_Id);*/
400 RAISE;
401 END Process_Ar_Detail;
402 --==========================================================================
403 -- PROCEDURE NAME:
404 -- Cfs_Detail_Report Public
405 --
406 -- DESCRIPTION:
407 -- This procedure is to generate the cfs detail report.
408 --
409 -- PARAMETERS:
410 -- Out: errbuf
411 -- Out: retcode
412 -- In: P_LEGAL_ENTITY_ID ID of Legal Entity
413 -- In: P_LEDGER_ID ID of Ledger
414 -- In: P_Chart_of_accounts_ID Identifier of gl chart of account
415 -- In: P_ADHOC_PREFIX Ad hoc prefix for FSG report, a required
416 -- parameter for FSG report
417 -- In: P_INDUSTRY Industry with constant value 'C' for
418 -- now, a required parameter for FSG report
419 -- In: P_ID_FLEX_CODE ID flex code, a required parameter for
420 -- FSG report
421 -- In: P_REPORT_ID Identifier of FSG report
422 -- In: P_GL_PERIOD_FROM Start period
423 -- In: P_GL_PERIOD_TO End period
424 -- In: P_SOURCE Source of the collection
425 -- In: P_INTERNAL_TRX To indicate if intercompany transactions
426 -- should be involved in amount calculation
427 -- of cash flow statement.
428 --
429 -- DESIGN REFERENCES:
430 -- None
431 --
432 -- CHANGE HISTORY:
433 -- 04/27/2007 Qingjun Zhao Created
434 -- 28/12/2008 Shujuan Yan bug fixing 7626489
435 --===========================================================================
436
437 PROCEDURE CFS_DETAIL_REPORT
438 (
439 ERRBUF OUT NOCOPY VARCHAR2
440 ,RETCODE OUT NOCOPY VARCHAR2
441 ,P_LEGAL_ENTITY_ID IN NUMBER
442 ,P_LEDGER_ID IN NUMBER
443 ,P_CHART_OF_ACCOUNTS_ID IN NUMBER
444 ,P_ADHOC_PREFIX IN VARCHAR2
445 ,P_INDUSTRY IN VARCHAR2
446 ,P_ID_FLEX_CODE IN VARCHAR2
447 ,P_REPORT_ID IN NUMBER
448 ,P_ROW_SET_ID IN NUMBER
449 -- Fix bug#6751696 delete begin
450 --,P_ROW_NAME IN VARCHAR2
451 -- Fix bug#6751696 delete end
452 -- Fix bug#6751696 add begin
453 ,P_ROW_NAME IN NUMBER
454 -- Fix bug#6751696 add end
455 ,P_GL_PERIOD_FROM IN VARCHAR2
456 ,P_GL_PERIOD_TO IN VARCHAR2
457 ,P_SOURCE IN VARCHAR2
458 ,P_BSV IN VARCHAR2--Fix bug#7334017 add
459 ) IS
460
461 L_DBG_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
462 L_PROC_LEVEL NUMBER := FND_LOG.LEVEL_PROCEDURE;
463 L_PROC_NAME VARCHAR2(100) := 'Cfs_Detail_Report';
464 L_LE_NAME HR_ALL_ORGANIZATION_UNITS.NAME%TYPE;
465 L_PERIOD_NUM_FROM GL_PERIODS.PERIOD_NUM%TYPE;
466 L_PERIOD_NUM_TO GL_PERIODS.PERIOD_NUM%TYPE;
467 L_DATE_FROM GL_PERIODS.START_DATE%TYPE;
468 L_DATE_TO GL_PERIODS.END_DATE%TYPE;
469 L_PERIOD_SET_NAME GL_PERIODS.PERIOD_SET_NAME%TYPE;
470 L_PERIOD_TYPE GL_SETS_OF_BOOKS.ACCOUNTED_PERIOD_TYPE%TYPE;
471 L_YEAR_FROM GL_PERIODS.PERIOD_YEAR%TYPE;
472 L_YEAR_TO GL_PERIODS.PERIOD_YEAR%TYPE;
473 L_TRX_ID NUMBER;
474 L_TRX_LINE_ID NUMBER;
475
476 -- Fix bug#6697073 begin ------------------------
477 -- L_INVOICE_NUM varchar2(100);
478 -- L_TRX_NUMBER varchar2(100);
479 L_INVOICE_NUM varchar2(240):=null;
480 --L_INVOICE_NUM1 INVOICE_NUM_TBL;-- Fix bug#6920953 delete
481 L_INVOICE_NUM1 INVOICE_NUM_TBL := INVOICE_NUM_TBL();-- Fix bug#6920953 add
482 L_TRX_NUMBER varchar2(240):=null;
483 -- Fix bug#6697073 end --------------------------
484
485 L_PERIOD_NUM GL_PERIODS.PERIOD_NUM%TYPE;
486 L_PERIOD_NAME JA_CN_CFS_ACTIVITIES_ALL.PERIOD_NAME%TYPE;
487 L_FUNC_AMOUNT JA_CN_CFS_ACTIVITIES_ALL.FUNC_AMOUNT%TYPE;
488 L_ORIGINAL_AMOUNT JA_CN_CFS_ACTIVITIES_ALL.ORIGINAL_AMOUNT%TYPE;
489 L_DETAILED_CFS_ITEM JA_CN_CFS_ACTIVITIES_ALL.DETAILED_CFS_ITEM%TYPE;
490 L_THIRD_PARTY_NAME varchar2(100);
491 L_THIRD_PARTY_NUMBER varchar2(100);
492 L_REFERENCE_NUMBER JA_CN_CFS_ACTIVITIES_ALL.REFERENCE_NUMBER%TYPE;
493 L_SOURCE JA_CN_CFS_ACTIVITIES_ALL.SOURCE%TYPE;
494 -- Fix bug#6751696 delete begin
495 --L_ROW_NAME RG_REPORT_AXES.AXIS_NAME%TYPE;
496 -- Fix bug#6751696 delete end
497 -- Fix bug#6751696 add begin
498 L_ROW_NAME RG_REPORT_AXES.AXIS_SEQ%TYPE;
499 -- Fix bug#6751696 add end
500 L_ROW_DESCRIPTION RG_REPORT_AXES.DESCRIPTION%TYPE;
501 L_REPORT_NAME RG_REPORTS.NAME%TYPE;
502 L_LEDGER_NAME VARCHAR2(30);
503 L_INTERCOMPANY_FLAG JA_CN_CFS_ACTIVITIES_ALL.INTERCOMPANY_FLAG%TYPE;
504 L_TRANSACTION_TYPE JA_CN_CFS_ACTIVITIES_ALL.TRANSACTION_TYPE%TYPE;
505 L_BSV JA_CN_CFS_ACTIVITIES_ALL.Balancing_Segment%TYPE;--enhancment add
506
507 -- Fix bug#6697073 begin----------------------------------------
508 -- L_SOURCE_AP VARCHAR2(10) := 'AP';
509 L_SOURCE_AP VARCHAR2(10) := 'SQLAP';
510 -- Fix bug#6697073 end------------------------------------------
511
512 L_SOURCE_AR VARCHAR2(10) := 'AR';
513 L_ROW_AMOUNT NUMBER;
514 L_XML_ITEM XMLTYPE;
515 L_XML_ALL XMLTYPE;
516 L_XML_ROW_ITEMS XMLTYPE;
517 L_XML_ROW XMLTYPE;
518 L_XML_PERIOD XMLTYPE;
519 L_XML_REPORT XMLTYPE;
520 L_XML_PARAMETER XMLTYPE;
521 L_XML_ROOT XMLTYPE;
522
523 -- Fix bug#6854438 add new variable begin
524 L_XML_ROWS_ALL XMLTYPE; -- record all the rows
525 -- Fix bug#6854438 add new variable end
526 L_DIS_FLAG NUMBER;
527 L_ERROR_STATUS BOOLEAN;
528 JA_CN_INVALID_GLPERIOD EXCEPTION;
529 L_MSG_INVALID_GLPERIOD VARCHAR2(2000);
530 l_characterset varchar(245);
531
532 -- Fix bug#6697073 begin------------------------------------
533 L_invoice_count number;
534 -- L_FUNC_AMOUNT_TEMP NUMBER_TBL; -- Fix bug#6920953 delete
535 L_FUNC_AMOUNT_TEMP NUMBER_TBL := NUMBER_TBL();-- Fix bug#6920953 add
536 -- Fix bug#6697073 end--------------------------------------
537
538 --Period when
539 CURSOR C_PERIODS(L_DATE_FROM DATE
540 , L_DATE_TO DATE
541 , P_LE_ID NUMBER
542 , P_LEDGER_ID NUMBER
543 , P_SOURCE VARCHAR2
544 , P_REPORT_ID NUMBER
545 , p_bsv VARCHAR2) IS--enhancment add
546 SELECT DISTINCT JCA.PERIOD_NAME
547 ,GP.PERIOD_NUM
548 FROM JA_CN_CFS_ACTIVITIES_ALL JCA
549 ,JA_CN_CFS_ASSIGNMENTS_ALL JCCA
550 ,RG_REPORT_AXES RRA
551 ,RG_REPORTS RG
552 ,GL_LEDGERS LED
553 ,GL_PERIODS GP
554 WHERE JCA.LEGAL_ENTITY_ID = P_LE_ID
555 AND JCA.LEDGER_ID = P_LEDGER_ID
556 AND JCA.GL_DATE >= L_DATE_FROM
557 AND JCA.GL_DATE <= L_DATE_TO
558 AND JCA.SOURCE = NVL(P_SOURCE,
559 JCA.SOURCE)
560 AND JCCA.DETAILED_CFS_ITEM = JCA.DETAILED_CFS_ITEM
561 AND JCCA.CHART_OF_ACCOUNTS_ID = P_CHART_OF_ACCOUNTS_ID
562 AND JCCA.AXIS_SET_ID = RRA.AXIS_SET_ID
563 AND JCCA.AXIS_SEQ = RRA.AXIS_SEQ
564 AND RG.ROW_SET_ID = RRA.AXIS_SET_ID
565 AND RG.REPORT_ID = P_REPORT_ID
566 AND LED.LEDGER_ID = P_LEDGER_ID
567 AND GP.PERIOD_SET_NAME = LED.PERIOD_SET_NAME
568 AND GP.PERIOD_NAME = JCA.PERIOD_NAME
569 --AND (P_BSV is null or jca.balancing_segment=P_BSV)--Fix bug#7334017 add --fix bug 7488223 delete
570 -- fix bug 7488223 add begin
571 AND ((P_BSV is not null and jca.balancing_segment=P_BSV)
572 or(p_bsv is null and jca.balancing_segment in (select bal_seg_value from JA_CN_LEDGER_LE_BSV_GT
573 where legal_entity_id=P_LE_ID and ledger_id=P_LEDGER_ID)
574 ))
575 ORDER BY GP.PERIOD_NUM DESC;
576
577 --Rows that should be included
578 CURSOR C_ROWS(P_PERIOD_NAME VARCHAR2
579 , P_LE_ID NUMBER
580 , P_LEDGER_ID NUMBER
581 , P_SOURCE VARCHAR2
582 , P_REPORT_ID NUMBER
583 , p_bsv VARCHAR2) IS--enhancment add
584 SELECT DISTINCT
585 -- Fix bug#6751696 delete begin
586 --RRA.AXIS_NAME
587 -- Fix bug#6751696 delete end
588 -- Fix bug#6751696 add begin
589 RRA.AXIS_SEQ
590 -- Fix bug#6751696 add end
591 FROM JA_CN_CFS_ACTIVITIES_ALL JCA
592 ,JA_CN_CFS_ASSIGNMENTS_ALL JCCA
593 ,RG_REPORT_AXES RRA
594 ,RG_REPORTS RG
595 WHERE JCA.LEGAL_ENTITY_ID = P_LE_ID
596 AND JCA.PERIOD_NAME = P_PERIOD_NAME
597 AND JCCA.DETAILED_CFS_ITEM = JCA.DETAILED_CFS_ITEM
598 AND JCCA.CHART_OF_ACCOUNTS_ID = P_CHART_OF_ACCOUNTS_ID
599 AND JCA.SOURCE = NVL(P_SOURCE,
600 JCA.SOURCE)
601 AND JCCA.AXIS_SET_ID = RRA.AXIS_SET_ID
602 AND JCCA.AXIS_SEQ = RRA.AXIS_SEQ
603 -- Fix bug#6859513 add begin
604 AND RRA.AXIS_SEQ = NVL(P_ROW_NAME, RRA.AXIS_SEQ)
605 -- Fix bug#6859513 add end
606 AND JCA.LEDGER_ID = P_LEDGER_ID
607 AND RG.ROW_SET_ID = RRA.AXIS_SET_ID
608 AND RG.REPORT_ID = NVL(P_REPORT_ID,
609 RG.REPORT_ID)
610 --AND (P_BSV is null or jca.balancing_segment=P_BSV)--Fix bug#7334017 add --fix bug 7488223 delete
611 -- fix bug 7488223 add begin
612 AND ((P_BSV is not null and jca.balancing_segment=P_BSV)
613 or(p_bsv is null and jca.balancing_segment in (select bal_seg_value from JA_CN_LEDGER_LE_BSV_GT
614 where legal_entity_id=P_LE_ID and ledger_id=P_LEDGER_ID)
615 ));
616 -- -- fix bug 7488223 add end
617
618 --The reports that should be reported
619 CURSOR C_REPORTS(P_PERIOD_NAME VARCHAR2
620 -- Fix bug#6751696 delete begin
621 --, P_ROW_NAME VARCHAR2
622 -- Fix bug#6751696 delete end
623 -- Fix bug#6751696 add begin
624 , P_ROW_NAME NUMBER
625 -- Fix bug#6751696 add end
626 , P_LE_ID NUMBER
627 , P_LEDGER_ID NUMBER
628 , P_SOURCE VARCHAR2
629 , P_REPORT_ID VARCHAR2
630 , p_bsv VARCHAR2) IS--enhancment add
631 SELECT JCA.TRX_ID
632 ,JCA.SOURCE
633 ,JCA.TRANSACTION_TYPE
634 ,JCA.TRX_LINE_ID
635 ,JCA.FUNC_AMOUNT
636 ,JCA.ORIGINAL_AMOUNT
637 ,JCA.DETAILED_CFS_ITEM
638 ,JCA.THIRD_PARTY_NAME
639 ,JCA.THIRD_PARTY_NUMBER
640 ,JCA.REFERENCE_NUMBER
641 ,RRA.DESCRIPTION
642 -- Fix bug#6697073 begin--------------------------------
643 ,JCA.TRX_NUMBER
644 -- Fix bug#6697073 end----------------------------------
645 ,null--fix bug 7487373 add
646 ,JCA.Balancing_Segment--enhancment add
647 FROM JA_CN_CFS_ACTIVITIES_ALL JCA
648 ,JA_CN_CFS_ASSIGNMENTS_ALL JCCA
649 ,RG_REPORTS RG
650 ,RG_REPORT_AXES RRA
651 WHERE JCA.LEGAL_ENTITY_ID = P_LE_ID
652 AND JCA.PERIOD_NAME = P_PERIOD_NAME
653 AND JCA.SOURCE = NVL(P_SOURCE,
654 JCA.SOURCE)
655 AND JCCA.DETAILED_CFS_ITEM = JCA.DETAILED_CFS_ITEM
656 AND JCCA.CHART_OF_ACCOUNTS_ID = P_CHART_OF_ACCOUNTS_ID
657 AND JCCA.AXIS_SET_ID = RRA.AXIS_SET_ID
658 AND JCCA.AXIS_SEQ = RRA.AXIS_SEQ
659 AND RG.ROW_SET_ID = RRA.AXIS_SET_ID
660 AND RG.REPORT_ID = P_REPORT_ID
661 AND JCA.LEDGER_ID = P_LEDGER_ID
662 -- Fix bug#6751696 delete begin
663 --AND RRA.AXIS_NAME = P_ROW_NAME
664 -- Fix bug#6751696 delete end
665 -- Fix bug#6751696 add begin
666 AND RRA.AXIS_SEQ = P_ROW_NAME
667 -- Fix bug#6751696 add end
668 --AND (P_BSV is null or jca.balancing_segment=P_BSV)--Fix bug#7334017 add --fix bug 7488223 delete
669 -- fix bug 7488223 add begin
670 AND ((P_BSV is not null and jca.balancing_segment=P_BSV)
671 or(p_bsv is null and jca.balancing_segment in (select bal_seg_value from JA_CN_LEDGER_LE_BSV_GT
672 where legal_entity_id=P_LE_ID and ledger_id=P_LEDGER_ID)
673 ))
674 -- -- fix bug 7488223 add end
675 ORDER BY JCA.SOURCE
676 ,JCA.THIRD_PARTY_NUMBER
677 ,JCA.REFERENCE_NUMBER;
678
679 BEGIN
680 --log for debug
681 IF (L_PROC_LEVEL >= L_DBG_LEVEL)
682 THEN
683 FND_LOG.STRING(L_PROC_LEVEL,
684 L_MODULE_PREFIX || '.' || L_PROC_NAME || '.begin',
685 'Enter procedure');
686 FND_LOG.STRING(L_PROC_LEVEL,
687 L_MODULE_PREFIX || '.' || L_PROC_NAME || '.parameters',
688 'P_LEGAL_ENTITY_ID ' || P_LEGAL_ENTITY_ID);
689 FND_LOG.STRING(L_PROC_LEVEL,
690 L_MODULE_PREFIX || '.' || L_PROC_NAME || '.parameters',
691 'P_LEDGER_ID ' || P_LEDGER_ID);
692 FND_LOG.STRING(L_PROC_LEVEL,
693 L_MODULE_PREFIX || '.' || L_PROC_NAME || '.parameters',
694 'P_chart_of_accounts_ID ' || P_CHART_OF_ACCOUNTS_ID);
695
696 FND_LOG.STRING(L_PROC_LEVEL,
697 L_MODULE_PREFIX || '.' || L_PROC_NAME || '.parameters',
698 'P_ADHOC_PREFIX ' || P_ADHOC_PREFIX);
699
700 FND_LOG.STRING(L_PROC_LEVEL,
701 L_MODULE_PREFIX || '.' || L_PROC_NAME || '.parameters',
702 'P_INDUSTRY ' || P_INDUSTRY);
703
704 FND_LOG.STRING(L_PROC_LEVEL,
705 L_MODULE_PREFIX || '.' || L_PROC_NAME || '.parameters',
706 'P_ID_FLEX_CODE ' || P_ID_FLEX_CODE);
707 FND_LOG.STRING(L_PROC_LEVEL,
708 L_MODULE_PREFIX || '.' || L_PROC_NAME || '.parameters',
709 'P_GL_PERIOD_FROM ' || P_GL_PERIOD_FROM);
710 FND_LOG.STRING(L_PROC_LEVEL,
711 L_MODULE_PREFIX || '.' || L_PROC_NAME || '.parameters',
712 'P_GL_PERIOD_TO' || P_GL_PERIOD_TO);
713 FND_LOG.STRING(L_PROC_LEVEL,
714 L_MODULE_PREFIX || '.' || L_PROC_NAME || '.parameters',
715 'P_SOURCE ' || P_SOURCE);
716 FND_LOG.STRING(L_PROC_LEVEL,
717 L_MODULE_PREFIX || '.' || L_PROC_NAME || '.parameters',
718 'P_REPORT_ID ' || P_REPORT_ID);
719 FND_LOG.STRING(L_PROC_LEVEL, --enhancment add
720 L_MODULE_PREFIX || '.' || L_PROC_NAME || '.parameters',
721 'P_BSV ' || P_BSV);
722
723 END IF; --(l_proc_level >= l_dbg_level)
724
725 --fix bug 7488223 add begin
726 DELETE
727 FROM JA_CN_LEDGER_LE_BSV_GT;
728 COMMIT ;
729 --
730 --ja_cn_utility_pkg.populate_ledger_le_bsv_gt( P_LEDGER_ID,P_LE_ID);
731
732 IF ja_cn_utility.populate_ledger_le_bsv_gt(P_LEDGER_ID,P_LEGAL_ENTITY_ID) <> 'S' THEN
733 RETURN;
734 END IF;
735 --fix bug 7488223 add end
736
737 L_XML_REPORT := NULL;
738
739 --The Legal Entity related infromation stored in the XLE_ENTITY_PROFILES
740 --table in R12.
741 SELECT name
742 INTO l_le_name
743 FROM XLE_ENTITY_PROFILES
744 WHERE legal_entity_id=p_legal_entity_id;
745
746 --Get the Report Name
747 SELECT NAME
748 INTO L_REPORT_NAME
749 FROM RG_REPORTS
750 WHERE REPORT_ID = P_REPORT_ID;
751
752 --get ledger name
753 SELECT name
754 INTO l_ledger_name
755 FROM gl_ledgers
756 WHERE ledger_id=p_ledger_id;
757 --write the parameter infomation into variable
758 --l_xml_parameter and last into l_xml_report
759 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
760 -- '<?xml version="1.0" encoding="utf-8" ?>');
761 -- Updated by shujuan for bug 7626489
762 l_characterset :=Fnd_Profile.VALUE(NAME => 'ICX_CLIENT_IANA_ENCODING');
763 FND_FILE.put_line(FND_FILE.output,'<?xml version="1.0" encoding= '||'"'||l_characterset||'"?>');
764 --for start period
765 SELECT XMLELEMENT("P_START_PERIOD",
766 P_GL_PERIOD_FROM)
767 INTO L_XML_ITEM
768 FROM DUAL;
769 L_XML_PARAMETER := L_XML_ITEM;
770 --for end period
771 SELECT XMLELEMENT("P_END_PERIOD",
772 P_GL_PERIOD_TO)
773 INTO L_XML_ITEM
774 FROM DUAL;
775 SELECT XMLCONCAT(L_XML_PARAMETER,
776 L_XML_ITEM)
777 INTO L_XML_PARAMETER
778 FROM DUAL;
779 --for report name
780 SELECT XMLELEMENT("P_REPORT_NAME",
781 L_REPORT_NAME)
782 INTO L_XML_ITEM
783 FROM DUAL;
784 SELECT XMLCONCAT(L_XML_PARAMETER,
785 L_XML_ITEM)
786 INTO L_XML_PARAMETER
787 FROM DUAL;
788 --for source
789 SELECT XMLELEMENT("P_SOURCE",
790 P_SOURCE)
791 INTO L_XML_ITEM
792 FROM DUAL;
793 SELECT XMLCONCAT(L_XML_PARAMETER,
794 L_XML_ITEM)
795 INTO L_XML_PARAMETER
796 FROM DUAL;
797 --for ledger name
798 SELECT XMLELEMENT("P_LEDGER_NAME",
799 L_LEDGER_NAME)
800 INTO L_XML_ITEM
801 FROM DUAL;
802 SELECT XMLCONCAT(L_XML_PARAMETER,
803 L_XML_ITEM)
804 INTO L_XML_PARAMETER
805 FROM DUAL;
806 --for legal entity
807 SELECT XMLELEMENT("P_LEGAL_ENTITY",
808 L_LE_NAME)
809 INTO L_XML_ITEM
810 FROM DUAL;
811 SELECT XMLCONCAT(L_XML_PARAMETER,
812 L_XML_ITEM)
813 INTO L_XML_PARAMETER
814 FROM DUAL;
815
816 --for row name
817 SELECT XMLELEMENT("P_ROW_NAME",
818 P_ROW_NAME)
819 INTO L_XML_ITEM
820 FROM DUAL;
821 SELECT XMLCONCAT(L_XML_PARAMETER,
822 L_XML_ITEM)
823 INTO L_XML_PARAMETER
824 FROM DUAL;
825
826 --for bsv
827 SELECT XMLELEMENT("P_BSV",--enhancment add
828 P_BSV)
829 INTO L_XML_ITEM
830 FROM DUAL;
831 SELECT XMLCONCAT(L_XML_PARAMETER,
832 L_XML_ITEM)
833 INTO L_XML_PARAMETER
834 FROM DUAL;
835
836
837 SELECT XMLCONCAT(L_XML_PARAMETER,
838 L_XML_REPORT)
839 INTO L_XML_REPORT
840 FROM DUAL;
841
842
843 --Get Period set name, Year_From and Year_To
844 SELECT LED.PERIOD_SET_NAME
845 ,GP1.PERIOD_YEAR
846 ,GP2.PERIOD_YEAR
847 INTO L_PERIOD_SET_NAME
848 ,L_YEAR_FROM
849 ,L_YEAR_TO
850 FROM GL_LEDGERS LED
851 ,GL_PERIODS GP1
852 ,GL_PERIODS GP2
853 WHERE LED.LEDGER_ID = P_LEDGER_ID
854 AND GP1.PERIOD_SET_NAME = LED.PERIOD_SET_NAME
855 AND GP1.PERIOD_NAME = P_GL_PERIOD_FROM
856 AND GP2.PERIOD_SET_NAME = LED.PERIOD_SET_NAME
857 AND GP2.PERIOD_NAME = P_GL_PERIOD_TO;
858
859 --The from period and to period should be within one accounting year
860 IF L_YEAR_FROM <> L_YEAR_TO
861 THEN
862 RAISE JA_CN_INVALID_GLPERIOD;
863 END IF; --l_year_from <> l_year_to
864
865
866 --get period type
867 SELECT ACCOUNTED_PERIOD_TYPE
868 INTO L_PERIOD_TYPE
869 FROM GL_LEDGERS
870 WHERE LEDGER_ID = P_LEDGER_ID;
871
872 --get period number for start period
873 SELECT PERIOD_YEAR * 1000 + PERIOD_NUM
874 ,START_DATE
875 INTO L_PERIOD_NUM_FROM
876 ,L_DATE_FROM
877 FROM GL_PERIODS
878 WHERE PERIOD_SET_NAME = L_PERIOD_SET_NAME
879 AND PERIOD_NAME = P_GL_PERIOD_FROM
880 AND PERIOD_TYPE = L_PERIOD_TYPE;
881
882 -- get period number for end period
883 SELECT PERIOD_YEAR * 1000 + PERIOD_NUM
884 ,END_DATE
885 INTO L_PERIOD_NUM_TO
886 ,L_DATE_TO
887 FROM GL_PERIODS
888 WHERE PERIOD_SET_NAME = L_PERIOD_SET_NAME
889 AND PERIOD_NAME = P_GL_PERIOD_TO
890 AND PERIOD_TYPE = L_PERIOD_TYPE;
891 --for each periods
892 OPEN C_PERIODS(L_DATE_FROM,
893 L_DATE_TO,
894 P_LEGAL_ENTITY_ID,
895 P_LEDGER_ID,
896 P_SOURCE,
897 P_REPORT_ID,
898 P_BSV);--enhancment add
899
900 LOOP
901 FETCH C_PERIODS
902 INTO L_PERIOD_NAME, L_PERIOD_NUM;
903 EXIT WHEN C_PERIODS%NOTFOUND;
904
905 L_XML_PERIOD := NULL;
906
907 -- Fix bug #6854438 initiate variable begin
908 L_XML_ROW := NULL;
909 L_XML_ROWS_ALL := NULL;
910 -- Fix bug #6854438 initiate variable end
911
912 FND_FILE.PUT_LINE(FND_FILE.LOG,
913 'l_period_name' || L_PERIOD_NAME);
914 FND_FILE.PUT_LINE(FND_FILE.LOG,
915 'p_row_name' || P_ROW_NAME || P_SOURCE);
916
917 --initiate row amount
918 L_ROW_AMOUNT:=0;
919 OPEN C_ROWS(L_PERIOD_NAME,
920 P_LEGAL_ENTITY_ID,
921 P_LEDGER_ID,
922 P_SOURCE,
923 P_REPORT_ID,
924 P_BSV);--Fix bug#7334017 add
925 LOOP
926 FETCH C_ROWS
927 INTO L_ROW_NAME;
928 EXIT WHEN C_ROWS%NOTFOUND;
929 FND_FILE.PUT_LINE(FND_FILE.LOG,
930 'l_row_name' || L_ROW_NAME);
931 L_XML_ROW := NULL;
932 L_XML_ITEM:=null; --fix bug#7497957 add
933 l_dis_flag:=0;--fix bug 7487395 add
934
935 -- Fix bug #6854438 clear variable value begin
936 L_XML_PERIOD := NULL;
937 l_row_amount := 0;
938 -- Fix bug #6854438 clear variable value end
939
940 OPEN C_REPORTS(L_PERIOD_NAME,
941 L_ROW_NAME,
942 P_LEGAL_ENTITY_ID,
943 P_LEDGER_ID,
944 P_SOURCE,
945 P_REPORT_ID,
946 P_BSV);--Fix bug#7334017 add
947 LOOP
948 FETCH C_REPORTS
949 INTO L_TRX_ID
950 , L_SOURCE
951 , L_TRANSACTION_TYPE
952 , L_TRX_LINE_ID
953 , L_FUNC_AMOUNT
954 , L_ORIGINAL_AMOUNT
955 , L_DETAILED_CFS_ITEM
956 , L_THIRD_PARTY_NAME
957 , L_THIRD_PARTY_NUMBER
958 , L_REFERENCE_NUMBER
959 , L_ROW_DESCRIPTION
960 -- Fix bug#6697073 begin------------------------------------------
961 , L_INVOICE_NUM
962 -- Fix bug#6697073 end--------------------------------------------
963 ,L_TRX_NUMBER--fix bug 7487373 add
964 ,L_BSV;
965 EXIT WHEN C_REPORTS%NOTFOUND;
966 -- Fix bug#6920953 add begin
967 L_FUNC_AMOUNT_TEMP.DELETE;
968 L_INVOICE_NUM1.DELETE;
969 L_FUNC_AMOUNT_TEMP.EXTEND;
970 L_INVOICE_NUM1.EXTEND;
971 L_FUNC_AMOUNT_TEMP(1) := L_FUNC_AMOUNT;
972 L_INVOICE_NUM1(1) := L_INVOICE_NUM;
973 -- Fix bug#6920953 add end
974
975 IF L_TRANSACTION_TYPE = 'SLA'
976 AND L_SOURCE = L_SOURCE_AP
977 THEN
978 PROCESS_AP_DETAIL(P_LEDGER_ID => P_LEDGER_ID,
979 P_AE_HEADER_ID => L_TRX_ID,
980 P_AE_LINE_NUM => L_TRX_LINE_ID,
981 X_TRX_NUMBER => L_TRX_NUMBER,
982
983 -- Fix bug#6697073 begin---------------------------
984 -- X_INVOICE_NUM => L_INVOICE_NUM,
985 --change return type as table type, so it can return more than one invoices.
986 X_FUNC_AMOUNT => L_FUNC_AMOUNT_TEMP,
987 --change return type as table type, so it can return more than one invoices.
988 X_INVOICE_NUM => L_INVOICE_NUM1,
989 -- Fix bug#6697073 end-----------------------------
990
991 X_THIRD_PARTY_NAME => L_THIRD_PARTY_NAME,
992 X_THIRD_PARTY_NUM => L_THIRD_PARTY_NUMBER);
993 END IF; --L_TRANSACTION_TYPE = 'SLA' AND L_SOURCE = L_SOURCE_AP
994
995 IF L_FUNC_AMOUNT<>0 THEN --for bug 6717171
996
997 IF L_TRANSACTION_TYPE = 'SLA'
998 AND L_SOURCE = L_SOURCE_AR
999 THEN
1000 PROCESS_AR_DETAIL(P_LEDGER_ID => P_LEDGER_ID,
1001 P_AE_HEADER_ID => L_TRX_ID,
1002 P_AE_LINE_NUM => L_TRX_LINE_ID,
1003 X_TRX_NUMBER => L_TRX_NUMBER,
1004
1005 -- Fix bug#6697073 begin-------------------------------------
1006 -- X_INVOICE_NUM => L_INVOICE_NUM,
1007 --change return type as table type, so it can return more than one invoices.
1008 X_FUNC_AMOUNT => L_FUNC_AMOUNT_TEMP,
1009 --change return type as table type, so it can return more than one invoices.
1010 X_INVOICE_NUM => L_INVOICE_NUM1,
1011 -- Fix bug#6697073 end---------------------------------------
1012
1013 X_THIRD_PARTY_NAME => L_THIRD_PARTY_NAME,
1014 X_THIRD_PARTY_NUM => L_THIRD_PARTY_NUMBER);
1015 END IF; --L_TRANSACTION_TYPE = 'SLA' AND L_SOURCE = L_SOURCE_AR
1016
1017
1018 L_DIS_FLAG:=1;--fix bug 7487395 add
1019
1020 -- Fix bug#6697073 begin --------------------------------------------
1021
1022 L_invoice_count := L_INVOICE_NUM1.count;
1023 FOR l_count IN 1..L_invoice_count
1024 loop
1025 -- Fix bug#6697073 end ----------------------------------------------
1026 SELECT XMLELEMENT("PERIOD_NAME",
1027 L_PERIOD_NAME)
1028 INTO L_XML_ITEM
1029 FROM DUAL;
1030 L_XML_ROW_ITEMS := L_XML_ITEM;
1031
1032 SELECT XMLELEMENT("DETAILED_CFS_ITEM",
1033 L_DETAILED_CFS_ITEM)
1034 INTO L_XML_ITEM
1035 FROM DUAL;
1036 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
1037 L_XML_ITEM)
1038 INTO L_XML_ROW_ITEMS
1039 FROM DUAL;
1040 SELECT XMLELEMENT("ROW_NAME",
1041 L_ROW_DESCRIPTION)
1042 INTO L_XML_ITEM
1043 FROM DUAL;
1044 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
1045 L_XML_ITEM)
1046 INTO L_XML_ROW_ITEMS
1047 FROM DUAL;
1048 SELECT XMLELEMENT("SOURCE",
1049 L_SOURCE)
1050 INTO L_XML_ITEM
1051 FROM DUAL;
1052 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
1053 L_XML_ITEM)
1054 INTO L_XML_ROW_ITEMS
1055 FROM DUAL;
1056
1057 SELECT XMLELEMENT("BSV",--Fix bug#7334017 add
1058 L_BSV)
1059 INTO L_XML_ITEM
1060 FROM DUAL;
1061 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
1062 L_XML_ITEM)
1063 INTO L_XML_ROW_ITEMS
1064 FROM DUAL;
1065
1066
1067 SELECT XMLELEMENT("THIRD_PARTY_NAME",
1068 L_THIRD_PARTY_NAME)
1069 INTO L_XML_ITEM
1070 FROM DUAL;
1071 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
1072 L_XML_ITEM)
1073 INTO L_XML_ROW_ITEMS
1074 FROM DUAL;
1075 SELECT XMLELEMENT("RECEIPT_PAYMENT_NUMBER",
1076 L_TRX_NUMBER)
1077 INTO L_XML_ITEM
1078 FROM DUAL;
1079 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
1080 L_XML_ITEM)
1081 INTO L_XML_ROW_ITEMS
1082 FROM DUAL;
1083
1084 -- Fix bug#6697073 begin ----------------------------
1085 SELECT XMLELEMENT("TRANSACTION_NUMBER",
1086 L_INVOICE_NUM1(l_count))--L_INVOICE_NUM
1087 INTO L_XML_ITEM
1088 FROM DUAL;
1089 -- Fix bug#6697073 end ------------------------------
1090
1091 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
1092 L_XML_ITEM)
1093 INTO L_XML_ROW_ITEMS
1094 FROM DUAL;
1095
1096 -- Fix bug#6697073 begin ----------------------------
1097 SELECT XMLELEMENT("TRANSACTION_AMOUNT",
1098 L_FUNC_AMOUNT_TEMP(l_count))--L_FUNC_AMOUNT
1099 INTO L_XML_ITEM
1100 FROM DUAL;
1101
1102 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
1103 L_XML_ITEM)
1104 INTO L_XML_ROW_ITEMS
1105 FROM DUAL;
1106
1107 SELECT XMLELEMENT("ROW_DETAIL",
1108 L_XML_ROW_ITEMS)
1109 INTO L_XML_ITEM
1110 FROM DUAL;
1111 SELECT XMLCONCAT(L_XML_ROW,
1112 L_XML_ITEM)
1113 INTO L_XML_ROW
1114 FROM DUAL;
1115
1116 --count row amount
1117 l_row_amount:=l_row_amount+nvl(L_FUNC_AMOUNT_TEMP(l_count),0);
1118 end loop;
1119 -- Fix bug#6697073 end ----------------------------------------------
1120
1121
1122 END IF;-- IF L_FUNC_AMOUNT<>0
1123
1124 END LOOP;--loop c_reports
1125 CLOSE c_reports;
1126
1127 if L_DIS_FLAG<>0 then--fix bug 7487395 add
1128 SELECT XMLELEMENT("ROW_AMOUNT",
1129 L_ROW_AMOUNT)
1130 INTO L_XML_ITEM
1131 FROM DUAL;
1132 SELECT XMLCONCAT(L_XML_ROW,
1133 L_XML_ITEM)
1134 INTO L_XML_ROW
1135 FROM DUAL;
1136
1137 SELECT XMLELEMENT("ROW",
1138 L_XML_ROW)
1139 INTO L_XML_ITEM
1140 FROM DUAL;
1141 end if; --fix bug 7487395 add
1142 -- Fix bug #6854438: record all the rows info by L_XML_ROWS_ALL begin
1143 IF L_XML_ROWS_ALL IS NULL
1144 THEN
1145 L_XML_ROWS_ALL := L_XML_ITEM;
1146 ELSE
1147 SELECT XMLCONCAT(L_XML_ROWS_ALL,
1148 L_XML_ITEM)
1149 INTO L_XML_ROWS_ALL
1150 FROM DUAL;
1151 END IF;
1152 -- Fix bug #6854438: record all the rows info by L_XML_ROWS_ALL end
1153
1154 END LOOP; --cursor c_rows
1155 CLOSE c_rows;
1156
1157 SELECT XMLCONCAT(L_XML_PERIOD,
1158 L_XML_ROWS_ALL)
1159 INTO L_XML_PERIOD
1160 FROM DUAL;
1161
1162 SELECT XMLELEMENT("PERIOD",
1163 L_XML_PERIOD)
1164 INTO L_XML_ITEM
1165 FROM DUAL;
1166 SELECT XMLCONCAT(L_XML_REPORT,
1167 L_XML_ITEM)
1168 INTO L_XML_REPORT
1169 FROM DUAL;
1170
1171 -- END LOOP; --cursor c_rows
1172
1173 END LOOP; --cursor c_reports
1174 CLOSE c_periods;
1175
1176
1177 SELECT XMLELEMENT("REPORT",
1178 L_XML_REPORT)
1179 INTO L_XML_ROOT
1180 FROM DUAL;
1181 JA_CN_UTILITY.OUTPUT_CONC(L_XML_ROOT.GETCLOBVAL());
1182
1183 --log for debug
1184 IF (L_PROC_LEVEL >= L_DBG_LEVEL)
1185 THEN
1186 FND_LOG.STRING(L_PROC_LEVEL,
1187 L_MODULE_PREFIX || '.' || L_PROC_NAME || '.end',
1188 'Exit procedure');
1189 END IF; --( l_proc_level >= l_dbg_level )
1190
1191 EXCEPTION
1192 WHEN JA_CN_INVALID_GLPERIOD THEN
1193 FND_MESSAGE.SET_NAME(APPLICATION => 'JA',
1194 NAME => 'JA_CN_INVALID_GLPERIOD');
1195 L_MSG_INVALID_GLPERIOD := FND_MESSAGE.GET;
1196 IF (L_PROC_LEVEL >= L_DBG_LEVEL)
1197 THEN
1198 FND_LOG.STRING(L_PROC_LEVEL,
1199 L_MODULE_PREFIX || '.' || L_PROC_NAME ||
1200 '.JA_CN_INVALID_GLPERIOD ',
1201 L_MSG_INVALID_GLPERIOD);
1202 END IF; --(l_proc_level >= l_dbg_level)
1203
1204 SELECT XMLELEMENT("EXCEPTION",
1205 L_MSG_INVALID_GLPERIOD)
1206 INTO L_XML_ITEM
1207 FROM DUAL;
1208 SELECT XMLCONCAT(L_XML_PARAMETER,
1209 L_XML_ITEM)
1210 INTO L_XML_ALL
1211 FROM DUAL;
1212 --To add root node for the xml output and then output it
1213 SELECT XMLELEMENT("REPORT",
1214 L_XML_ALL)
1215 INTO L_XML_ROOT
1216 FROM DUAL;
1217 JA_CN_UTILITY.OUTPUT_CONC(L_XML_ROOT.GETCLOBVAL());
1218
1219 RETCODE := 1;
1220 ERRBUF := L_MSG_INVALID_GLPERIOD;
1221 WHEN OTHERS THEN
1222 IF (L_PROC_LEVEL >= L_DBG_LEVEL)
1223 THEN
1224 FND_LOG.STRING(L_PROC_LEVEL,
1225 L_MODULE_PREFIX || '.' || L_PROC_NAME ||
1226 '.Other_Exception ',
1227 SQLCODE || ':' || SQLERRM);
1228 END IF; --(l_proc_level >= l_dbg_level)
1229
1230 SELECT XMLELEMENT("EXCEPTION",
1231 'Other_Exception')
1232 INTO L_XML_ITEM
1233 FROM DUAL;
1234 SELECT XMLCONCAT(L_XML_PARAMETER,
1235 L_XML_ITEM)
1236 INTO L_XML_ALL
1237 FROM DUAL;
1238 --To add root node for the xml output and then output it
1239 SELECT XMLELEMENT("REPORT",
1240 L_XML_ALL)
1241 INTO L_XML_ROOT
1242 FROM DUAL;
1243
1244 JA_CN_UTILITY.OUTPUT_CONC(L_XML_ROOT.GETCLOBVAL());
1245 IF c_periods%ISOPEN THEN
1246 CLOSE c_periods;
1247 END IF;
1248 IF c_rows%ISOPEN THEN
1249 CLOSE c_rows;
1250 END IF;
1251 IF c_reports%ISOPEN THEN
1252 CLOSE c_reports;
1253 END IF;
1254 RETCODE := 2;
1255 ERRBUF := SQLCODE || ':' || SQLERRM;
1256 RAISE;
1257 END CFS_DETAIL_REPORT;
1258
1259 END Ja_Cn_Cfs_Report_Pkg;