[Home] [Help]
PACKAGE BODY: APPS.JA_CN_AR_VALIDATION_PKG
Source
1 PACKAGE BODY JA_CN_AR_VALIDATION_PKG AS
2 --$Header: JACNARVB.pls 120.2 2010/06/02 11:42:18 weihuang noship $
3 --+=======================================================================+
4 --| Copyright (c) 2010 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNARVB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| |
13 --| This package contains the following PL/SQL tables/procedures |
14 --| to validate the receivable balance and details export. |
15 --| |
16 --| |
17 --| PROCEDURE LIST |
18 --| Validate_Receivables |
19 --| |
20 --| HISTORY |
21 --| 14-Apr-2010 Wei Huang Created |
22 --| |
23 --+======================================================================*/
24 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_AR_VALIDATION_PKG';
25
26 --==========================================================================
27 -- PROCEDURE NAME:
28 --
29 -- Validate_Receivables Public
30 --
31 -- DESCRIPTION:
32 --
33 -- This package contains the following PL/SQL tables/procedures
34 -- to validate the receivable balance and details export.
35 -- Validation report includes transaction as below:
36 -- 1) Transaction in which the legal entity is not same with the
37 -- legal entity that the Balance Segment Value in
38 -- payables of the transaction is assigned.
39 -- 2) Transaction which has not been itemized.
40 --
41 -- PARAMETERS:
42 -- In: pn_legal_entity_id LEGAL_ENTITY_ID
43 -- pn_ledger_id Ledger ID
44 -- pv_accounting_year Accounting Year
45 -- pv_period_from Period From
46 -- pv_period_to Period To
47 --
48 -- Out:
49 -- pv_errbuf OUT NOCOPY VARCHAR2
50 -- pv_retcode OUT NOCOPY VARCHAR2
51 -- DESIGN REFERENCES:
52 --
53 --
54 -- CHANGE HISTORY:
55 -- 14-Apr-2010 Wei Huang Created
56 --
57 --===========================================================================
58 PROCEDURE Validate_Receivables
59 (pv_errbuf OUT NOCOPY VARCHAR2
60 ,pv_retcode OUT NOCOPY VARCHAR2
61 ,pn_legal_entity_id IN NUMBER
62 ,pn_ledger_id IN NUMBER
63 ,pv_accounting_year IN VARCHAR2
64 ,pv_period_from IN VARCHAR2
65 ,pv_period_to IN VARCHAR2
66 )
67 IS
68 lv_procedure_name VARCHAR2(40) := 'Validate_Receivables';
69 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
70 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
71 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
72 NO_DATA EXCEPTION;
73
74 ld_start_date DATE;
75 ld_end_date DATE;
76 lv_le_name VARCHAR2(255);
77 lv_ledger_name VARCHAR2(255);
78
79 L_XML_ITEM XMLTYPE;
80 L_XML_ROW_ITEMS XMLTYPE;
81 L_XML_ROW XMLTYPE;
82 L_XML_REPORT XMLTYPE;
83 L_XML_PARAMETER XMLTYPE;
84 L_XML_ROOT XMLTYPE;
85
86 --Cursor for getting data which satisty the below contidion:
87 --The Legal Entity stamp on transaction is not same with the legal entity
88 --to which the BSV is assigned in Accounting Setup Manager.
89 CURSOR invalid_transaction_cur
90 IS
91 SELECT DISTINCT
92 jeh.je_header_id Je_Header_Id,
93 aeh.ae_header_id Ae_Header_Id,
94 part.party_id Party_Id,
95 gps.start_date Start_Date,
96 gps.period_name Period_Name,
97 part.party_number Customer_Number,
98 part.party_name Customer_Name,
99 rct.trx_number Transaction_Number,
100 JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(jcc.code_combination_id) Bal_Seg_Value
101 FROM GL_JE_HEADERS jeh,
102 GL_JE_LINES jel,
103 GL_IMPORT_REFERENCES gir,
104 XLA_AE_LINES ael,
105 XLA_AE_HEADERS aeh,
106 JA_CN_CODE_COMBINATION_V jcc,
107 HZ_CUST_ACCOUNTS cust,
108 HZ_PARTIES part,
109 GL_PERIOD_STATUSES gps,
110 Xla_Transaction_Entities ent,
111 RA_CUSTOMER_TRX_ALL rct
112 WHERE jeh.je_header_id = jel.je_header_id
113 AND jeh.je_header_id = gir.je_header_id
114 AND jel.je_line_num = gir.je_line_num
115 AND gir.gl_sl_link_id = ael.gl_sl_link_id
116 AND gir.gl_sl_link_table = ael.gl_sl_link_table
117 AND ael.ae_header_id = aeh.ae_header_id
118 AND jcc.code_combination_id = jel.code_combination_id
119 AND jcc.ledger_id = jeh.ledger_id
120 AND cust.cust_account_id(+) = ael.party_id
121 AND cust.party_id = part.party_id(+)
122 AND jeh.period_name = gps.period_name
123 AND jeh.ledger_id = gps.ledger_id
124 AND jeh.status = 'P' -- AR posted to GL
125 AND ael.accounting_class_code = 'RECEIVABLE'--accountingclass is 'Receivable' in the accounting line in SLA
126 --BSV Condition
127 AND rct.legal_entity_id = pn_legal_entity_id
128 AND NOT EXISTS (SELECT llbg.Bal_Seg_Value
129 FROM ja_cn_ledger_le_bsv_gt llbg
130 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Jcc.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
131 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
132 AND llbg.Legal_Entity_Id = pn_legal_entity_id) --parameter: pn_legal_entity_id
133 --ld_start_date and ld_end_date come from pv_accounting_year, pv_period_from, pv_period_to
134 AND gps.application_id = 101
135 AND ((gps.start_date BETWEEN ld_start_date AND ld_end_date) AND
136 (gps.end_date BETWEEN ld_start_date AND ld_end_date))
137 AND jeh.ledger_id = pn_ledger_id --parameter: pn_ledger_id
138 -- Transaction condition
139 AND ent.Application_Id = 222
140 AND rct.Customer_Trx_Id = ent.Source_Id_Int_1
141 AND ent.Entity_Code = 'TRANSACTIONS'
142 AND ent.Entity_Id = aeh.Entity_Id
143 AND Aeh.Ledger_Id =jeh.ledger_id
144 ORDER BY gps.start_date, part.party_id,rct.trx_number;
145
146 --2. Cursor for getting data which satisty the below contidion:
147 --Account and Journal Itemization Program has not run for these
148 --Journals that has been posted in GL.
149 CURSOR unitemized_transaction_cur
150 IS
151 SELECT DISTINCT
152 jeh.je_header_id Je_Header_Id,
153 aeh.ae_header_id Ae_Header_Id,
154 part.party_id Party_Id,
155 gps.start_date Start_Date,
156 gps.period_name Period_Name,
157 part.party_number Customer_Number,
158 part.party_name Customer_Name,
159 jeh.name Journal_Name,
160 '' Trx_Receipt_Number
161 FROM GL_JE_HEADERS jeh,
162 GL_JE_LINES jel,
163 GL_IMPORT_REFERENCES gir,
164 XLA_AE_LINES ael,
165 XLA_AE_HEADERS aeh,
166 JA_CN_CODE_COMBINATION_V jcc,
167 HZ_CUST_ACCOUNTS cust,
168 HZ_PARTIES part,
169 GL_PERIOD_STATUSES gps
170 WHERE jeh.je_header_id = jel.je_header_id
171 AND jeh.je_header_id = gir.je_header_id
172 AND jel.je_line_num = gir.je_line_num
173 AND gir.gl_sl_link_id = ael.gl_sl_link_id
174 AND gir.gl_sl_link_table = ael.gl_sl_link_table
175 AND ael.ae_header_id = aeh.ae_header_id
176 AND jcc.code_combination_id = jel.code_combination_id
177 AND jcc.ledger_id = jeh.ledger_id
178 AND cust.cust_account_id(+) = ael.party_id
179 AND cust.party_id = part.party_id(+)
180 AND jeh.period_name = gps.period_name
181 AND jeh.ledger_id = gps.ledger_id
182 AND jeh.status = 'P' -- AR posted to GL
183 AND NVL(Jel.Global_Attribute7,'*') <> 'P' --not itemized
184 AND ael.accounting_class_code = 'RECEIVABLE'
185 --BSV Condition
186 AND EXISTS (SELECT llbg.Bal_Seg_Value
187 FROM ja_cn_ledger_le_bsv_gt llbg
188 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Jcc.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
189 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
190 AND llbg.Legal_Entity_Id = pn_legal_entity_id) --parameter: pn_legal_entity_id
191 --ld_start_date and ld_end_date come from pv_accounting_year, pv_period_from, pv_period_to
192 AND gps.application_id = 101
193 AND ((gps.start_date BETWEEN ld_start_date AND ld_end_date) AND
194 (gps.end_date BETWEEN ld_start_date AND ld_end_date))
195 AND jeh.ledger_id = pn_ledger_id
196 ORDER BY gps.start_date, part.party_id;
197
198 --Cursor for fetching legal entity by code combination id
199 CURSOR legal_entity_cur
200 (lv_bal_seg_value NUMBER)
201 IS
202 SELECT xep.Name Legal_Entity
203 FROM Xle_Entity_Profiles xep
204 ,Gl_Ledger_Norm_Seg_Vals glnsv
205 WHERE glnsv.ledger_id = pn_ledger_id --parameter: pn_ledger_id
206 AND glnsv.legal_entity_id = xep.legal_entity_id
207 AND glnsv.Segment_Value = lv_bal_seg_value --variable: lv_bal_seg_value
208 AND glnsv.Segment_Type_Code = 'B'
209 AND Nvl(glnsv.Status_Code, 'I') <> 'D';
210
211 --Cursor for getting Transaction Number or Receipt Number based on ae_header_id
212 CURSOR trx_receipt_num_cur
213 (ln_ae_header_id NUMBER)
214 IS
215 SELECT Rct.Trx_Number Trx_Receipt_Number
216 FROM RA_CUSTOMER_TRX_ALL Rct,
217 Xla_Transaction_Entities Ent,
218 Xla_Ae_Headers Aeh
219 WHERE Ent.Application_Id = 222
220 AND Rct.Customer_Trx_Id = Ent.Source_Id_Int_1
221 AND Ent.Entity_Code = 'TRANSACTIONS'
222 AND Ent.Entity_Id = Aeh.Entity_Id
223 AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
224 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
225 UNION
226 SELECT Acr.Receipt_Number Trx_Receipt_Number
227 FROM AR_CASH_RECEIPTS_ALL Acr,
228 Xla_Transaction_Entities Ent,
229 Xla_Ae_Headers Aeh
230 WHERE Ent.Application_Id = 222
231 AND Acr.Cash_Receipt_Id = Ent.Source_Id_Int_1
232 AND Ent.Entity_Code = 'RECEIPTS'
233 AND Ent.Entity_Id = Aeh.Entity_Id
234 AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
235 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
236 UNION
237 SELECT Rct.Trx_Number Trx_Receipt_Number
238 FROM RA_CUSTOMER_TRX_ALL Rct,
239 AR_ADJUSTMENTS_ALL Adj,
240 Xla_Transaction_Entities Ent,
241 Xla_Ae_Headers Aeh
242 WHERE Adj.Customer_Trx_Id = Rct.Customer_Trx_Id
243 AND Ent.Application_Id = 222
244 AND Adj.Adjustment_Id = Ent.Source_Id_Int_1
245 AND Ent.Entity_Code = 'ADJUSTMENTS'
246 AND Ent.Entity_Id = Aeh.Entity_Id
247 AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
248 AND Aeh.Ledger_Id = pn_ledger_id; --parameter: pn_ledger_id
249
250 BEGIN
251 --logging for debug
252 IF (ln_proc_level >= ln_dbg_level)
253 THEN
254 FND_LOG.STRING(ln_proc_level,
255 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
256 '.begin',
257 'Enter procedure');
258 -- logging the parameters
259 FND_LOG.STRING(ln_proc_level,
260 lv_procedure_name ||
261 '.parameters',
262 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
263 'pn_ledger_id=' || pn_ledger_id || ',' ||
264 'pv_accounting_year=' || pv_accounting_year || ',' ||
265 'pv_period_from=' || pv_period_from || ',' ||
266 'pv_period_to=' || pv_period_to);
267 END IF; --ln_proc_level >= ln_dbg_level
268
269 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
270 '.parameters:' ||
271 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
272 'pn_ledger_id=' || pn_ledger_id || ',' ||
273 'pv_accounting_year=' || pv_accounting_year || ',' ||
274 'pv_period_from=' || pv_period_from || ',' ||
275 'pv_period_to=' || pv_period_to);
276
277 --1. Populate balancing segment values to table ja_cn_ledger_le_bsv_gt
278 DELETE FROM JA_CN_LEDGER_LE_BSV_GT;
279 COMMIT;
280 IF Ja_Cn_Utility.Populate_Ledger_Le_Bsv_Gt(pn_Ledger_Id,pn_legal_entity_id) <> 'S'
281 THEN
282 RETURN;
283 END IF;
284
285 --2. Fetch start date and end date according to the pv_accounting_year,
286 --pv_period_from, pv_period_to.
287 IF pv_period_from IS NOT NULL
288 THEN
289 SELECT start_date
290 INTO ld_start_date
291 FROM GL_PERIOD_STATUSES
292 WHERE ledger_id = pn_ledger_id
293 AND application_id = 101
294 AND period_name = pv_period_from
295 AND to_char(period_year) = pv_accounting_year;
296 ELSE
297 ld_start_date := to_date(pv_accounting_year||'0101','YYYYMMDD');
298 END IF; --pv_period_from IS NOT NULL
299
300 IF pv_period_to IS NOT NULL
301 THEN
302 SELECT end_date
303 INTO ld_end_date
304 FROM GL_PERIOD_STATUSES
305 WHERE ledger_id = pn_ledger_id
306 AND application_id = 101
307 AND period_name = pv_period_to
308 AND to_char(period_year) = pv_accounting_year;
309 ELSE
310 ld_end_date := to_date(pv_accounting_year||'1231','YYYYMMDD');
311 END IF; --pv_period_to IS NOT NULL
312
313 --logging the variables
314 IF (ln_statement_level >= ln_dbg_level)
315 THEN
316 FND_LOG.STRING(ln_statement_level,
317 lv_procedure_name,
318 'ld_start_date=' || ld_start_date || ',' ||
319 'ld_end_date=' || ld_end_date);
320 END IF; --(ln_statement_level >= ln_dbg_level)
321 /*FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
322 '.variable:' ||
323 'ld_start_date=' || ld_start_date || ',' ||
324 'ld_end_date=' || ld_end_date);*/
325
326 L_XML_REPORT := NULL;
327 --get legal entity name
328 SELECT name
329 INTO lv_le_name
330 FROM XLE_ENTITY_PROFILES
331 WHERE legal_entity_id = pn_legal_entity_id;
332
333 --get ledger name
334 SELECT name
335 INTO lv_ledger_name
336 FROM gl_ledgers
337 WHERE ledger_id = pn_ledger_id;
338
339 FND_FILE.put_line(FND_FILE.output,'<?xml version="1.0" encoding= '||'"'||JA_CN_UTILITY.Get_XML_Encoding||'"?>');
340
341 --3. Add report parameters to XML data
342 --for period from
343 SELECT XMLELEMENT("PV_PERIOD_FROM",
344 pv_period_from)
345 INTO L_XML_ITEM
346 FROM DUAL;
347 L_XML_PARAMETER := L_XML_ITEM;
348 --for period to
349 SELECT XMLELEMENT("PV_PERIOD_TO",
350 pv_period_to)
351 INTO L_XML_ITEM
352 FROM DUAL;
353 SELECT XMLCONCAT(L_XML_PARAMETER,
354 L_XML_ITEM)
355 INTO L_XML_PARAMETER
356 FROM DUAL;
357 --for accounting year
358 SELECT XMLELEMENT("PV_ACCOUNTING_YEAR",
359 pv_accounting_year)
360 INTO L_XML_ITEM
361 FROM DUAL;
362 SELECT XMLCONCAT(L_XML_PARAMETER,
363 L_XML_ITEM)
364 INTO L_XML_PARAMETER
365 FROM DUAL;
366 --for ledger name
367 SELECT XMLELEMENT("PV_LEDGER_NAME",
368 lv_ledger_name)
369 INTO L_XML_ITEM
370 FROM DUAL;
371 SELECT XMLCONCAT(L_XML_PARAMETER,
372 L_XML_ITEM)
373 INTO L_XML_PARAMETER
374 FROM DUAL;
375 --for legal entity
376 SELECT XMLELEMENT("PV_LEGAL_ENTITY",
377 lv_le_name)
378 INTO L_XML_ITEM
379 FROM DUAL;
380 SELECT XMLCONCAT(L_XML_PARAMETER,
381 L_XML_ITEM)
382 INTO L_XML_PARAMETER
383 FROM DUAL;
384
385 SELECT XMLCONCAT(L_XML_PARAMETER,
386 L_XML_REPORT)
387 INTO L_XML_REPORT
388 FROM DUAL;
389
390 --4. Add BSV row header to XML data
391 L_XML_ITEM := NULL;
392 Fnd_Message.Set_Name(Application => 'JA',
393 NAME => 'JA_CN_AR_LEAGLE_ENTITY_WRONG'
394 );
395 SELECT XMLELEMENT("PV_BSV_ROW_NAME",
396 Fnd_Message.Get)
397 INTO L_XML_ITEM
398 FROM DUAL;
399 SELECT XMLCONCAT(L_XML_REPORT,
400 L_XML_ITEM)
401 INTO L_XML_REPORT
402 FROM DUAL;
403
404 --5. Add data for the legal entity diferrent with BSV
405 --The Legal Entity stamp on transaction is not same with the legal
406 --entity to which the BSV is assigned in Accounting Setup Manager
407 FOR v_invalid_trx_row IN invalid_transaction_cur
408 LOOP
409 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Loop invalid_transaction_cur: Ae_Header_Id='||v_invalid_trx_row.ae_header_id);
410
411 L_XML_ROW := NULL;
412 L_XML_ITEM := NULL;
413 --for period name
414 SELECT XMLELEMENT("PV_PERIOD_NAME",
415 v_invalid_trx_row.Period_Name)
416 INTO L_XML_ITEM
417 FROM DUAL;
418 L_XML_ROW_ITEMS := L_XML_ITEM;
419 --for customer
420 SELECT XMLELEMENT("PV_CUSTOMER_NAME",
421 v_invalid_trx_row.Customer_Name)
422 INTO L_XML_ITEM
423 FROM DUAL;
424 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
425 L_XML_ITEM)
426 INTO L_XML_ROW_ITEMS
427 FROM DUAL;
428 --for transaction number
429 SELECT XMLELEMENT("PV_TRANSACTION_NUMBER",
430 v_invalid_trx_row.Transaction_Number)
431 INTO L_XML_ITEM
432 FROM DUAL;
433 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
434 L_XML_ITEM)
435 INTO L_XML_ROW_ITEMS
436 FROM DUAL;
437 --for legal entity in transaction
438 FOR v_legal_entity_row IN legal_entity_cur(v_invalid_trx_row.Bal_Seg_Value)
439 LOOP
440 SELECT XMLELEMENT("PV_LEGAL_ENTITY",
441 v_legal_entity_row.Legal_Entity)
442 INTO L_XML_ITEM
443 FROM DUAL;
444 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
445 L_XML_ITEM)
446 INTO L_XML_ROW_ITEMS
447 FROM DUAL;
448 EXIT;
449 END LOOP;
450 --Combine row items to a row
451 SELECT XMLELEMENT("ROW_BSV",
452 L_XML_ROW_ITEMS)
453 INTO L_XML_ROW
454 FROM DUAL;
455 SELECT XMLCONCAT(L_XML_REPORT,
456 L_XML_ROW)
457 INTO L_XML_REPORT
458 FROM DUAL;
459
460 END LOOP; --end invalid_transaction_cur
461
462 --6. Add un-itemized row header to XML data
463 L_XML_ITEM := NULL;
464 Fnd_Message.Set_Name(Application => 'JA',
465 NAME => 'JA_CN_APAR_NO_ITEMIZED'
466 );
467 SELECT XMLELEMENT("PV_UNITEMIZED_ROW_NAME",
468 Fnd_Message.Get)
469 INTO L_XML_ITEM
470 FROM DUAL;
471 SELECT XMLCONCAT(L_XML_REPORT,
472 L_XML_ITEM)
473 INTO L_XML_REPORT
474 FROM DUAL;
475
476 --7. Add data for unitemized journal
477 --Account and Journal Itemization Program has not run for these
478 --Journals that has been posted in GL.
479 FOR v_unitemized_trx_row IN unitemized_transaction_cur
480 LOOP
481 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Loop unitemized_transaction_cur: Ae_Header_Id='||v_unitemized_trx_row.ae_header_id);
482
483 L_XML_ROW := NULL;
484 L_XML_ITEM := NULL;
485 --for period name
486 SELECT XMLELEMENT("PV_PERIOD_NAME",
487 v_unitemized_trx_row.Period_Name)
488 INTO L_XML_ITEM
489 FROM DUAL;
490 L_XML_ROW_ITEMS := L_XML_ITEM;
491 --for journal name
492 SELECT XMLELEMENT("PV_JOURNAL_NAME",
493 v_unitemized_trx_row.Journal_Name)
494 INTO L_XML_ITEM
495 FROM DUAL;
496 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
497 L_XML_ITEM)
498 INTO L_XML_ROW_ITEMS
499 FROM DUAL;
500 --for customer
501 SELECT XMLELEMENT("PV_CUSTOMER_NAME",
502 v_unitemized_trx_row.Customer_Name)
503 INTO L_XML_ITEM
504 FROM DUAL;
505 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
506 L_XML_ITEM)
507 INTO L_XML_ROW_ITEMS
508 FROM DUAL;
509 --for transaction number/receipt number
510 FOR v_trx_receipt_num_row IN trx_receipt_num_cur(v_unitemized_trx_row.Ae_Header_Id)
511 LOOP
512 SELECT XMLELEMENT("PV_TRX_RECEIPT_NUMBER",
513 v_trx_receipt_num_row.TRX_RECEIPT_NUMBER)
514 INTO L_XML_ITEM
515 FROM DUAL;
516 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
517 L_XML_ITEM)
518 INTO L_XML_ROW_ITEMS
519 FROM DUAL;
520 EXIT;
521 END LOOP;
522 --Combine row items to row
523 SELECT XMLELEMENT("ROW_UNITEMIZED",
524 L_XML_ROW_ITEMS)
525 INTO L_XML_ROW
526 FROM DUAL;
527 SELECT XMLCONCAT(L_XML_REPORT,
528 L_XML_ROW)
529 INTO L_XML_REPORT
530 FROM DUAL;
531 END LOOP; --end unitemized_transaction_cur
532
533 --8. Combine the XML data together
534 SELECT XMLELEMENT("REPORT",
535 L_XML_REPORT)
536 INTO L_XML_ROOT
537 FROM DUAL;
538 JA_CN_UTILITY.OUTPUT_CONC(L_XML_ROOT.GETCLOBVAL());
539
540 --logging for debug
541 IF (ln_proc_level >= ln_dbg_level)
542 THEN
543 FND_LOG.STRING(ln_proc_level,
544 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
545 'Exit procedure');
546 END IF; -- (ln_proc_level>=ln_dbg_level)
547
548 EXCEPTION
549 WHEN OTHERS THEN
550 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
551 THEN
552 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
553 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
554 '.Other_Exception ',
555 SQLCODE || SQLERRM);
556 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
557 pv_retcode := '2';
558 pv_errbuf := SQLCODE || SQLERRM;
559 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
560
561 END Validate_Receivables;
562
563 END JA_CN_AR_VALIDATION_PKG;