[Home] [Help]
PACKAGE BODY: APPS.JA_CN_AP_VALIDATION_PKG
Source
1 PACKAGE BODY JA_CN_AP_VALIDATION_PKG AS
2 --$Header: JACNAPVB.pls 120.1 2010/06/03 01:56:41 wuwu noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNAPVB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Validation report includes transaction as below: |
13 --| 1) Transaction in which the legal entity is not same |
14 --| with the legal entity that the Balance Segment Value in |
15 --| payables of the transaction is assigned. |
16 --| 2) Transaction which has not been itemized |
17 --| |
18 --| PROCEDURE LIST |
19 --| PROCEDURE Validate_Payables |
20 --| |
21 --| |
22 --| HISTORY |
23 --| 13-Apr-2010 Chaoqun Wu created |
24 --| 26-May-2010 Chaoqun Wu Updated for fixing bug# 9763810 |
25 --+======================================================================*/
26
27 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_AP_VALIDATION_PKG';
28
29 --==========================================================================
30 -- PROCEDURE NAME:
31 --
32 -- Get_Supplier_Name Public
33 --
34 -- DESCRIPTION:
35 --
36 -- To get supplier name, including customer name from AR Refund
37 --
38 -- PARAMETERS:
39 -- In: pv_supplier_name IN VARCHAR2 supplier name
40 -- pn_ae_header_id IN NUMBER ae header id
41 -- pn_ledger_id IN NUMBER ledger id
42 --
43 -- DESIGN REFERENCES:
44 -- APAR_Chaoqun.doc
45 --
46 -- CHANGE HISTORY:
47 -- 04-May-2010 Chaoqun Wu created
48 --==========================================================================
49 FUNCTION Get_Supplier_Name
50 (pv_supplier_name IN VARCHAR2
51 ,pn_ae_header_id IN NUMBER
52 ,pn_ledger_id IN NUMBER)
53 RETURN VARCHAR2
54 IS
55 lv_supplier_name VARCHAR(30) := pv_supplier_name;
56 lv_procedure_name VARCHAR2(40) := 'Get_Supplier_Name';
57 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
58 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
59
60
61 CURSOR customer_name_cur
62 (ln_invoice_id NUMBER)
63 IS
64 SELECT DISTINCT part.party_name Customer_Name
65 FROM HZ_CUST_ACCOUNTS cust,
66 HZ_PARTIES part,
67 AP_INVOICES_ALL aia
68 WHERE cust.cust_account_id = aia.party_id
69 AND cust.party_id = part.party_id
70 AND aia.Invoice_Id = ln_invoice_id; --variable: ln_invoice_id
71
72 CURSOR inv_id_cur
73 IS
74 SELECT Aia.Invoice_Id Invoice_Id
75 FROM Ap_Invoices_All Aia,
76 Xla_Transaction_Entities Ent,
77 Xla_Ae_Headers Aeh
78 WHERE Ent.Application_Id = 200
79 AND Aia.Invoice_Id = Ent.Source_Id_Int_1
80 AND Ent.Entity_Code = 'AP_INVOICES'
81 AND Ent.Entity_Id = Aeh.Entity_Id
82 AND Aeh.Ae_Header_Id = pn_ae_header_id --parameter: pn_ae_header_id
83 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
84 UNION
85 SELECT Aia.Invoice_Id Invoice_Id
86 FROM Ap_Invoices_All Aia,
87 Ap_Checks_All Ach,
88 Ap_Invoice_Payments_All Aip,
89 Xla_Transaction_Entities Ent,
90 Xla_Ae_Headers Aeh
91 WHERE Ent.Application_Id = 200
92 AND Aia.Invoice_Id = Aip.Invoice_Id
93 AND Ach.Check_Id = Aip.Check_Id
94 AND Ach.Check_Id = Ent.Source_Id_Int_1
95 AND Ent.Entity_Code = 'AP_PAYMENTS'
96 AND Ent.Entity_Id = Aeh.Entity_Id
97 AND Aeh.Ae_Header_Id = pn_ae_header_id --parameter: pn_ae_header_id
98 AND Aeh.Ledger_Id = pn_ledger_id; --parameter: pn_ledger_id
99
100 BEGIN
101 --logging for debug
102 IF (ln_proc_level >= ln_dbg_level)
103 THEN
104 FND_LOG.STRING(ln_proc_level,
105 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
106 '.begin',
107 'Enter procedure');
108 END IF; --ln_proc_level>=ln_dbg_level
109
110 IF lv_supplier_name IS NULL
111 THEN
112 FOR v_inv_id_row IN inv_id_cur
113 LOOP
114 FOR v_customer_name_row IN customer_name_cur(v_inv_id_row.Invoice_Id)
115 LOOP
116 lv_supplier_name := v_customer_name_row.Customer_Name;
117 EXIT;
118 END LOOP;
119 EXIT;
120 END LOOP;
121 END IF;
122
123 --logging for debug
124 IF (ln_proc_level >= ln_dbg_level)
125 THEN
126 FND_LOG.STRING(ln_proc_level,
127 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
128 'Exit procedure');
129 END IF; -- (ln_proc_level>=ln_dbg_level)
130
131 RETURN lv_supplier_name;
132 END Get_Supplier_Name;
133
134 --==========================================================================
135 -- PROCEDURE NAME:
136 --
137 -- Validate_Payables Public
138 --
139 -- DESCRIPTION:
140 --
141 -- Validation report includes transaction as below:
142 -- 1) Transaction in which the legal entity is not same
143 -- with the legal entity that the Balance Segment Value in
144 -- payables of the transaction is assigned.
145 -- 2) Transaction which has not been itemized
146 --
147 -- PARAMETERS:
148 -- Out: pv_errbuf NOCOPY VARCHAR2
149 -- pv_retcode NOCOPY VARCHAR2
150 -- In: pn_legal_entity_id NUMBER identifier of legal entity
151 -- pn_ledger_id NUMBER identifier of ledger
152 -- pv_accounting_year VARCHAR2 accounting year
153 -- pv_period_from VARCHAR2 period from
154 -- pv_period_to VARCHAR2 period to
155 --
156 -- DESIGN REFERENCES:
157 -- APAR_Chaoqun.doc
158 --
159 -- CHANGE HISTORY:
160 -- 13-Apr-2010 Chaoqun Wu created
161 -- 26-May-2010 Chaoqun Wu Updated for fixing bug# 9763810
162 --==========================================================================
163
164 PROCEDURE Validate_Payables
165 (pv_errbuf OUT NOCOPY VARCHAR2
166 ,pv_retcode OUT NOCOPY VARCHAR2
167 ,pn_legal_entity_id IN NUMBER
168 ,pn_ledger_id IN NUMBER
169 ,pv_accounting_year IN VARCHAR2
170 ,pv_period_from IN VARCHAR2
171 ,pv_period_to IN VARCHAR2
172 )
173 IS
174 lv_procedure_name VARCHAR2(40) := 'Validate_Payables';
175 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
176 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
177
178 L_XML_ITEM XMLTYPE;
179 L_XML_ROW_ITEMS XMLTYPE;
180 L_XML_ROW XMLTYPE;
181 L_XML_REPORT XMLTYPE;
182 L_XML_PARAMETER XMLTYPE;
183 L_XML_ROOT XMLTYPE;
184
185 lv_le_name VARCHAR2(255);
186 lv_ledger_name VARCHAR2(255);
187 lv_supplier_name VARCHAR2(255);
188
189 --Step 1: Payable validation at legal entity level and itemization level
190 --Step 1.1: Payables validation at Legal entity level
191 --Cursor for fetching payables not in current legal entity
192 CURSOR invalid_bsv_inv_cur
193 IS
194 SELECT DISTINCT
195 Jeh.Je_Header_Id Je_Header_Id,
196 Aeh.Ae_Header_Id Ae_Header_Id,
197 Ael.Party_Id Supplier_Id,
198 Gp.Start_date Start_Date,
199 Jeh.Period_Name Period_Name,
200 Sup.Vendor_Name Supplier_Name,
201 Aia.Invoice_Num Invoice_Number,
202 JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Jcc.CODE_COMBINATION_ID) Bal_Seg_Value
203 FROM Gl_Je_Lines Jel,
204 Gl_Je_Headers Jeh,
205 Gl_Import_References Gir,
206 Ja_Cn_Code_Combination_v Jcc,
207 Gl_Ledgers Gl,
208 Gl_Periods Gp,
209 Xla_Ae_Lines Ael,
210 Xla_Ae_Headers Aeh,
211 Ap_Suppliers Sup,
212 Xla_Transaction_Entities Ent,
213 Ap_Invoices_All Aia
214 WHERE Jel.Je_Header_Id = Jeh.Je_Header_Id
215 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
216 AND Gir.Je_Line_Num = Jel.Je_Line_Num
217 AND Jcc.Code_Combination_Id = Jel.Code_Combination_Id
218 AND Jcc.Ledger_id = Jeh.Ledger_id
219 AND Gl.Ledger_Id = Jcc.ledger_id
220 AND Jeh.Status = 'P' --only collecting posted payable
221 --Period condition
222 AND Gl.period_set_name = Gp.period_set_name
223 AND Jeh.Period_Name = Gp.Period_Name
224 AND Gl.accounted_period_type = Gp.period_type
225 AND Gp.period_year = to_number(pv_accounting_year) --parameter: pv_accounting_year
226 AND Gp.start_date BETWEEN
227 (SELECT Gp1.Start_Date
228 FROM Gl_Periods Gp1
229 WHERE Gl.period_set_name = Gp1.period_set_name
230 AND Gl.accounted_period_type = Gp1.period_type
231 AND Gp1.period_name = pv_period_from) --parameter: pv_period_from
232 AND (SELECT Gp2.Start_Date
233 FROM Gl_Periods Gp2
234 WHERE Gl.period_set_name = Gp2.period_set_name
235 AND Gl.accounted_period_type = Gp2.period_type
236 AND Gp2.period_name = pv_period_to) --parameter: pv_period_to
237 --SLA condition
238 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
239 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
240 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
241 AND Jcc.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
242 AND Ael.Party_Id = Sup.Vendor_Id(+)
243 AND NVL(Sup.Vendor_Type_Lookup_Code, 'CUSTOMER') <> 'EMPLOYEE' --not considering employee supplier
244 AND Ael.Accounting_Class_Code = 'LIABILITY' --only collecting 'Liability' account
245 ---Invoice level condition
246 AND Ent.Application_Id = 200
247 AND Aia.Invoice_Id = Ent.Source_Id_Int_1
248 AND Ent.Entity_Code = 'AP_INVOICES'
249 AND Ent.Entity_Id = Aeh.Entity_Id
250 --BSV condition
251 AND Aia.Legal_Entity_Id = pn_legal_entity_id --parameter: pn_legal_entity_id --Updated for fixing bug# 9763810
252 AND NOT EXISTS (SELECT llbg.Bal_Seg_Value
253 FROM ja_cn_ledger_le_bsv_gt llbg
254 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Jcc.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
255 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
256 AND llbg.Legal_Entity_Id = pn_legal_entity_id) --parameter: pn_legal_entity_id
257 ORDER BY Gp.start_date, Ael.Party_Id;
258
259 --Step 1.2: Payables validation at itemization level
260 --Cursor for fetching paybles not being itemized
261 CURSOR unitemized_inv_cur
262 IS
263 SELECT DISTINCT
264 Jeh.Je_Header_Id Je_Header_Id,
265 Aeh.Ae_Header_Id Ae_Header_Id,
266 Ael.Party_Id Supplier_Id,
267 Gp.Start_date Start_Date,
268 Jeh.Period_Name Period_Name,
269 Sup.Vendor_Name Supplier_Name,
270 Jeh.Name Journal_Name,
271 '' Inv_Pay_Number
272 FROM Gl_Je_Lines Jel,
273 Gl_Je_Headers Jeh,
274 Gl_Import_References Gir,
275 Ja_Cn_Code_Combination_v Jcc,
276 Gl_Ledgers Gl,
277 Gl_Periods Gp,
278 Xla_Ae_Lines Ael,
279 Xla_Ae_Headers Aeh,
280 Ap_Suppliers Sup
281 WHERE Jel.Je_Header_Id = Jeh.Je_Header_Id
282 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
283 AND Gir.Je_Line_Num = Jel.Je_Line_Num
284 AND Jcc.Code_Combination_Id = Jel.Code_Combination_Id
285 AND Jcc.Ledger_id = Jeh.Ledger_id
286 AND Gl.Ledger_Id = Jcc.ledger_id
287 AND Jeh.Status = 'P' --only collecting posted payable
288 AND NVL(Jel.Global_Attribute7,'U') <> 'P' --picking up those unitemzied invoices
289 --Period condition
290 AND Gl.period_set_name = Gp.period_set_name
291 AND Jeh.Period_Name = Gp.Period_Name
292 AND Gl.accounted_period_type = Gp.period_type
293 AND Gp.period_year = to_number(pv_accounting_year) --parameter: pv_accounting_year
294 AND Gp.start_date BETWEEN
295 (SELECT Gp1.Start_Date
296 FROM Gl_Periods Gp1
297 WHERE Gl.period_set_name = Gp1.period_set_name
298 AND Gl.accounted_period_type = Gp1.period_type
299 AND Gp1.period_name = pv_period_from) --parameter: pv_period_from
300 AND (SELECT Gp2.Start_Date
301 FROM Gl_Periods Gp2
302 WHERE Gl.period_set_name = Gp2.period_set_name
303 AND Gl.accounted_period_type = Gp2.period_type
304 AND Gp2.period_name = pv_period_to) --parameter: pv_period_to
305 --SLA condition
306 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
307 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
308 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
309 AND Jcc.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
310 AND Ael.Party_Id = Sup.Vendor_Id(+)
311 AND NVL(Sup.Vendor_Type_Lookup_Code, 'CUSTOMER') <> 'EMPLOYEE' --not considering employee supplier
312 AND Ael.Accounting_Class_Code = 'LIABILITY' --only collecting 'Liability' account
313 --BSV condition
314 AND EXISTS (SELECT llbg.Bal_Seg_Value
315 FROM ja_cn_ledger_le_bsv_gt llbg
316 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Jcc.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
317 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
318 AND llbg.Legal_Entity_Id = pn_legal_entity_id) --parameter: pn_legal_entity_id
319 ORDER BY Gp.start_date, Ael.Party_Id;
320
321 --Step 2: Cursor for fetching legal entity by code combination id
322 CURSOR legal_entity_cur
323 (lv_bal_seg_value NUMBER)
324 IS
325 SELECT xep.Name Legal_Entity
326 FROM Xle_Entity_Profiles xep
327 ,Gl_Ledger_Norm_Seg_Vals glnsv
328 WHERE glnsv.ledger_id = pn_ledger_id --parameter: pn_ledger_id
329 AND glnsv.legal_entity_id = xep.legal_entity_id
330 AND glnsv.Segment_Value = lv_bal_seg_value --variable: lv_bal_seg_value
331 AND glnsv.Segment_Type_Code = 'B'
332 AND Nvl(glnsv.Status_Code, 'I') <> 'D';
333
334 --Step 3: Cursor for fetching invoice number / payment number
335 CURSOR inv_pay_num_cur
336 (ln_ae_header_id NUMBER)
337 IS
338 SELECT Aia.Invoice_Num Inv_Pay_Number
339 FROM Ap_Invoices_All Aia,
340 Xla_Transaction_Entities Ent,
341 Xla_Ae_Headers Aeh
342 WHERE Ent.Application_Id = 200
343 AND Aia.Invoice_Id = Ent.Source_Id_Int_1
344 AND Ent.Entity_Code = 'AP_INVOICES'
345 AND Ent.Entity_Id = Aeh.Entity_Id
346 AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
347 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
348 UNION
349 SELECT TO_CHAR(Aca.Check_Number) Inv_Pay_Number
350 FROM Ap_Checks_All Aca,
351 Xla_Transaction_Entities Ent,
352 Xla_Ae_Headers Aeh
353 WHERE Ent.Application_Id = 200
354 AND Aca.Check_Id = Ent.Source_Id_Int_1
355 AND Ent.Entity_Code = 'AP_PAYMENTS'
356 AND Ent.Entity_Id = Aeh.Entity_Id
357 AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
358 AND Aeh.Ledger_Id = pn_ledger_id; --parameter: pn_ledger_id
359
360 BEGIN
361 --logging for debug
362 IF (ln_proc_level >= ln_dbg_level)
363 THEN
364 FND_LOG.STRING(ln_proc_level,
365 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
366 '.begin',
367 'Enter procedure');
368 -- logging the parameters
369 FND_LOG.STRING(ln_proc_level,
370 lv_procedure_name ||
371 '.parameters',
372 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
373 'pn_ledger_id=' || pn_ledger_id || ',' ||
374 'pv_accounting_year=' || pv_accounting_year || ',' ||
375 'pv_period_from=' || pv_period_from || ',' ||
376 'pv_period_to=' || pv_period_to);
377 END IF; --ln_proc_level>=ln_dbg_level
378 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
379 '.parameters:' ||
380 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
381 'pn_ledger_id=' || pn_ledger_id || ',' ||
382 'pv_accounting_year=' || pv_accounting_year || ',' ||
383 'pv_period_from=' || pv_period_from || ',' ||
384 'pv_period_to=' || pv_period_to);
385
386 --Step0: Populate balancing segment values to table ja_cn_ledger_le_bsv_gt
387 DELETE
388 FROM JA_CN_LEDGER_LE_BSV_GT;
389 COMMIT ;
390 IF ja_cn_utility.populate_ledger_le_bsv_gt(pn_ledger_id, pn_legal_entity_id) <> 'S'
391 THEN
392 RETURN;
393 END IF;
394
395 L_XML_REPORT := NULL;
396 --get legal entity name
397 SELECT name
398 INTO lv_le_name
399 FROM XLE_ENTITY_PROFILES
400 WHERE legal_entity_id=pn_legal_entity_id;
401
402 --get ledger name
403 SELECT name
404 INTO lv_ledger_name
405 FROM gl_ledgers
406 WHERE ledger_id=pn_ledger_id;
407
408 FND_FILE.put_line(FND_FILE.output,'<?xml version="1.0" encoding= '||'"'||JA_CN_UTILITY.Get_XML_Encoding||'"?>');
409 --Step1: report parameters
410 --for period from
411 SELECT XMLELEMENT("PV_PERIOD_FROM",
412 pv_period_from)
413 INTO L_XML_ITEM
414 FROM DUAL;
415 L_XML_PARAMETER := L_XML_ITEM;
416 --for period to
417 SELECT XMLELEMENT("PV_PERIOD_TO",
418 pv_period_to)
419 INTO L_XML_ITEM
420 FROM DUAL;
421 SELECT XMLCONCAT(L_XML_PARAMETER,
422 L_XML_ITEM)
423 INTO L_XML_PARAMETER
424 FROM DUAL;
425 --for accounting year
426 SELECT XMLELEMENT("PV_ACCOUNTING_YEAR",
427 pv_accounting_year)
428 INTO L_XML_ITEM
429 FROM DUAL;
430 SELECT XMLCONCAT(L_XML_PARAMETER,
431 L_XML_ITEM)
432 INTO L_XML_PARAMETER
433 FROM DUAL;
434 --for ledger name
435 SELECT XMLELEMENT("PV_LEDGER_NAME",
436 lv_ledger_name)
437 INTO L_XML_ITEM
438 FROM DUAL;
439 SELECT XMLCONCAT(L_XML_PARAMETER,
440 L_XML_ITEM)
441 INTO L_XML_PARAMETER
442 FROM DUAL;
443 --for legal entity
444 SELECT XMLELEMENT("PV_LEGAL_ENTITY",
445 lv_le_name)
446 INTO L_XML_ITEM
447 FROM DUAL;
448 SELECT XMLCONCAT(L_XML_PARAMETER,
449 L_XML_ITEM)
450 INTO L_XML_PARAMETER
451 FROM DUAL;
452
453 SELECT XMLCONCAT(L_XML_PARAMETER,
454 L_XML_REPORT)
455 INTO L_XML_REPORT
456 FROM DUAL;
457
458 --Step2: BSV part
459 L_XML_ITEM := NULL;
460
461 --for row name in bsv part
462 Fnd_Message.Set_Name(Application => 'JA',
463 NAME => 'JA_CN_AP_LEAGLE_ENTITY_WRONG'
464 );
465
466 SELECT XMLELEMENT("PV_BSV_ROW_NAME",
467 Fnd_Message.Get)
468 INTO L_XML_ITEM
469 FROM DUAL;
470 SELECT XMLCONCAT(L_XML_REPORT,
471 L_XML_ITEM)
472 INTO L_XML_REPORT
473 FROM DUAL;
474
475 FOR v_invalid_bsv_inv_row IN invalid_bsv_inv_cur
476 LOOP
477 L_XML_ROW := NULL;
478 L_XML_ITEM := NULL;
479 --for period name
480 SELECT XMLELEMENT("PV_PERIOD_NAME",
481 v_invalid_bsv_inv_row.Period_Name)
482 INTO L_XML_ITEM
483 FROM DUAL;
484 L_XML_ROW_ITEMS := L_XML_ITEM;
485
486 --for supplier
487 lv_supplier_name := Get_Supplier_Name(v_invalid_bsv_inv_row.Supplier_Name
488 ,v_invalid_bsv_inv_row.Ae_Header_Id
489 ,pn_ledger_id);
490 SELECT XMLELEMENT("PV_SUPPLIER_NAME",
491 lv_supplier_name)
492 INTO L_XML_ITEM
493 FROM DUAL;
494 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
495 L_XML_ITEM)
496 INTO L_XML_ROW_ITEMS
497 FROM DUAL;
498
499 --for invoice number
500 SELECT XMLELEMENT("PV_INVOICE_NUMBER",
501 v_invalid_bsv_inv_row.Invoice_Number)
502 INTO L_XML_ITEM
503 FROM DUAL;
504 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
505 L_XML_ITEM)
506 INTO L_XML_ROW_ITEMS
507 FROM DUAL;
508
509 --for legal entity in invoice
510 FOR v_legal_entity_row IN legal_entity_cur(v_invalid_bsv_inv_row.Bal_Seg_Value)
511 LOOP
512 SELECT XMLELEMENT("PV_LEGAL_ENTITY",
513 v_legal_entity_row.Legal_Entity)
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
523 SELECT XMLELEMENT("ROW_BSV",
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;
532
533 --for row name in un-itemized part
534 L_XML_ITEM := NULL;
535 Fnd_Message.Set_Name(Application => 'JA',
536 NAME => 'JA_CN_APAR_NO_ITEMIZED'
537 );
538
539 SELECT XMLELEMENT("PV_UNITEMIZED_ROW_NAME",
540 Fnd_Message.Get)
541 INTO L_XML_ITEM
542 FROM DUAL;
543 SELECT XMLCONCAT(L_XML_REPORT,
544 L_XML_ITEM)
545 INTO L_XML_REPORT
546 FROM DUAL;
547
548 FOR v_unitemized_inv_row IN unitemized_inv_cur
549 LOOP
550 L_XML_ROW := NULL;
551 L_XML_ITEM := NULL;
552 --for period name
553 SELECT XMLELEMENT("PV_PERIOD_NAME",
554 v_unitemized_inv_row.Period_Name)
555 INTO L_XML_ITEM
556 FROM DUAL;
557 L_XML_ROW_ITEMS := L_XML_ITEM;
558
559 --for journal name
560 SELECT XMLELEMENT("PV_JOURNAL_NAME",
561 v_unitemized_inv_row.Journal_Name)
562 INTO L_XML_ITEM
563 FROM DUAL;
564 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
565 L_XML_ITEM)
566 INTO L_XML_ROW_ITEMS
567 FROM DUAL;
568
569 --for supplier
570 lv_supplier_name := Get_Supplier_Name(v_unitemized_inv_row.Supplier_Name
571 ,v_unitemized_inv_row.Ae_Header_Id
572 ,pn_ledger_id);
573 SELECT XMLELEMENT("PV_SUPPLIER_NAME",
574 lv_supplier_name)
575 INTO L_XML_ITEM
576 FROM DUAL;
577 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
578 L_XML_ITEM)
579 INTO L_XML_ROW_ITEMS
580 FROM DUAL;
581
582 --for invoice number/payment number
583 FOR v_inv_pay_num_row IN inv_pay_num_cur(v_unitemized_inv_row.Ae_Header_Id)
584 LOOP
585 SELECT XMLELEMENT("PV_INV_PAY_NUMBER",
586 v_inv_pay_num_row.Inv_Pay_Number)
587 INTO L_XML_ITEM
588 FROM DUAL;
589 SELECT XMLCONCAT(L_XML_ROW_ITEMS,
590 L_XML_ITEM)
591 INTO L_XML_ROW_ITEMS
592 FROM DUAL;
593 EXIT;
594 END LOOP;
595
596 SELECT XMLELEMENT("ROW_UNITEMIZED",
597 L_XML_ROW_ITEMS)
598 INTO L_XML_ROW
599 FROM DUAL;
600 SELECT XMLCONCAT(L_XML_REPORT,
601 L_XML_ROW)
602 INTO L_XML_REPORT
603 FROM DUAL;
604 END LOOP;
605
606 SELECT XMLELEMENT("REPORT",
607 L_XML_REPORT)
608 INTO L_XML_ROOT
609 FROM DUAL;
610 JA_CN_UTILITY.OUTPUT_CONC(L_XML_ROOT.GETCLOBVAL());
611
612 --logging for debug
613 IF (ln_proc_level >= ln_dbg_level)
614 THEN
615 FND_LOG.STRING(ln_proc_level,
616 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
617 'Exit procedure');
618 END IF; -- (ln_proc_level>=ln_dbg_level)
619
620 EXCEPTION
621 WHEN OTHERS THEN
622 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
623 THEN
624 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
625 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
626 '.Other_Exception ',
627 SQLCODE || SQLERRM);
628 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
629 pv_retcode := '2';
630 pv_errbuf := SQLCODE || SQLERRM;
631 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
632
633 END Validate_Payables;
634
635 END JA_CN_AP_VALIDATION_PKG;