[Home] [Help]
PACKAGE BODY: APPS.FUN_XML_REPORT_PKG
Source
1 PACKAGE BODY FUN_XML_REPORT_PKG AS
2 /* $Header: FUNXRPTB.pls 120.4.12010000.2 2008/12/17 03:15:32 abhaktha ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FUN_XML_REPORT_PKG';
5 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
6 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
7 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
8 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
9 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
10 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
11 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
12
13 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
14 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
15 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
16 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
17 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
18 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
19 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
20 G_MODULE_NAME CONSTANT VARCHAR2(80) := 'FUN_XML_REPORT_PKG';
21
22
23
24 PROCEDURE clob_to_file
25 (p_xml_clob IN CLOB) IS
26
27 l_clob_size NUMBER;
28 l_offset NUMBER;
29 l_chunk_size INTEGER;
30 l_chunk VARCHAR2(32767);
31 l_log_module VARCHAR2(240);
32
33 BEGIN
34
35
36 l_clob_size := dbms_lob.getlength(p_xml_clob);
37
38 IF (l_clob_size = 0) THEN
39 RETURN;
40 END IF;
41
42 l_offset := 1;
43 l_chunk_size := 3000;
44
45 WHILE (l_clob_size > 0) LOOP
46 l_chunk := dbms_lob.substr (p_xml_clob, l_chunk_size, l_offset);
47 fnd_file.put
48 (which => fnd_file.output
49 ,buff => l_chunk);
50
51 l_clob_size := l_clob_size - l_chunk_size;
52 l_offset := l_offset + l_chunk_size;
53 END LOOP;
54
55 fnd_file.new_line(fnd_file.output,1);
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 APP_EXCEPTION.RAISE_EXCEPTION;
60
61 END clob_to_file;
62
63
64 PROCEDURE put_starttag(tag_name IN VARCHAR2) IS
65 BEGIN
66
67 fnd_file.put_line(fnd_file.output, '<'||tag_name||'>');
68 --fnd_file.new_line(fnd_file.output,1);
69
70 EXCEPTION
71
72 WHEN OTHERS then
73 APP_EXCEPTION.RAISE_EXCEPTION;
74
75 END;
76
77 PROCEDURE put_endtag(tag_name IN VARCHAR2) IS
78 BEGIN
79
80 fnd_file.put_line(fnd_file.output, '</'||tag_name||'>');
81 --fnd_file.new_line(fnd_file.output,1);
82
83 EXCEPTION
84
85 WHEN OTHERS then
86 APP_EXCEPTION.RAISE_EXCEPTION;
87
88 END;
89
90 PROCEDURE put_element(tag_name IN VARCHAR2,
91 value IN VARCHAR2) IS
92 BEGIN
93
94 fnd_file.put(fnd_file.output, '<'||tag_name||'>');
95 fnd_file.put(fnd_file.output, '<![CDATA[');
96 fnd_file.put(fnd_file.output, value);
97 fnd_file.put(fnd_file.output, ']]>');
98 fnd_file.put_line(fnd_file.output, '</'||tag_name||'>');
99
100 EXCEPTION
101
102 WHEN OTHERS then
103 APP_EXCEPTION.RAISE_EXCEPTION;
104
105 END;
106
107
108
109 PROCEDURE proposed_netting_report(
110 errbuf OUT NOCOPY VARCHAR2,
111 retcode OUT NOCOPY NUMBER,
112 p_batch_id IN VARCHAR2 ) IS
113
114 l_qryCtx DBMS_XMLGEN.ctxHandle;
115 l_result_clob CLOB;
116 l_current_calling_sequence varchar2(2000);
117 l_debug_info varchar2(200);
118
119 l_report_name varchar2(80) := 'Proposed Netting Report';
120
121 l_batch_count number;
122 l_invoice_count number;
123 l_trx_count number;
124 l_temp_invoice_count number;
125 l_temp_trx_count number;
126 l_encoding VARCHAR2(20);
127
128 BEGIN
129
130 l_current_calling_sequence := 'FUN_XML_REPORT_PKG.proposed_netting_report';
131 l_debug_info := 'Select Batch Info...';
132
133 l_batch_count := 0;
134 l_invoice_count := 0;
135 l_trx_count := 0;
136 select tag INTO l_encoding from fnd_lookup_values
137 where lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
138 and lookup_code = ( select value from v$nls_parameters where parameter='NLS_CHARACTERSET')
139 and language='US' ;
140
141 put_starttag('?xml version="1.0" encoding="'||l_encoding||'"?');
142 put_starttag('NETTING_REPORT');
143
144 l_qryCtx := DBMS_XMLGEN.newContext(
145 'SELECT HOU.NAME AS OPERATING_UNIT,
146 FNA.AGREEMENT_NAME,
147 FNA.AGREEMENT_START_DATE,
148 FNA.AGREEMENT_END_DATE,
149 FNA.PARTNER_REFERENCE,
150 CBA.BANK_ACCOUNT_NAME,
151 DECODE(FNA.SEL_REC_PAST_DUE_TXNS_FLAG, ''Y'', ''Yes'', ''N'', ''No'') AS SELECT_REC_PAST_DUE_TXNS,
152 FNA.DAYS_PAST_DUE,
153 FLC1.MEANING AS NETTING_ORDER_RULE,
154 FLC2.MEANING AS NETTING_BALANCE_RULE,
155 FLC3.MEANING AS NETTING_CURRENCY_RULE,
156 FNA.NET_CURRENCY_CODE,
157 GLC.USER_CONVERSION_TYPE AS EXCHANGE_RATE_TYPE,
158 FNB.EXCHANGE_RATE,
159 FNB.BATCH_NUMBER,
160 FNB.BATCH_NAME,
161 FNB.BATCH_CURRENCY,
162 FNB.SETTLEMENT_DATE,
163 FNB.TRANSACTION_DUE_DATE,
164 FNB.RESPONSE_DATE,
165 FNB.TOTAL_NETTED_AMT
166 FROM FUN_NET_BATCHES_ALL FNB,
167 FUN_NET_AGREEMENTS_ALL FNA,
168 HR_OPERATING_UNITS HOU,
169 CE_BANK_ACCOUNTS CBA,
170 FUN_LOOKUPS FLC1,
171 FUN_LOOKUPS FLC2,
172 FUN_LOOKUPS FLC3,
173 gl_daily_conversion_types glc
174 WHERE FNA.AGREEMENT_ID = FNB.AGREEMENT_ID
175 AND GLC.CONVERSION_TYPE = FNB.EXCHANGE_RATE_TYPE
176 AND HOU.ORGANIZATION_ID = FNB.ORG_ID
177 AND CBA.BANK_ACCOUNT_ID = FNA.BANK_ACCOUNT_ID
178 AND FLC1.LOOKUP_TYPE = ''FUN_NET_ORDER_RULE''
179 AND FLC1.LOOKUP_CODE = FNA.NET_ORDER_RULE_CODE
180 AND FLC2.LOOKUP_TYPE = ''FUN_NET_BALANCE_RULE''
181 AND FLC2.LOOKUP_CODE = FNA.NET_BALANCE_RULE_CODE
182 AND FLC3.LOOKUP_TYPE = ''FUN_NET_CURRENCY_RULE''
183 AND FLC3.LOOKUP_CODE = FNA.NET_CURRENCY_RULE_CODE
184 AND FNB.BATCH_ID = :BATCH_ID');
185
186
187 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'BATCH_DETAILS_SET');
188 DBMS_XMLGEN.setRowTag(l_qryCtx, 'BATCH_DETAILS');
189 DBMS_XMLGEN.setBindValue(l_qryCtx,'BATCH_ID', p_batch_id);
190 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
191 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
192
193 l_batch_count := DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx);
194 DBMS_XMLGEN.closeContext(l_qryCtx);
195 clob_to_file(l_result_clob);
196
197
198 l_debug_info := 'Select AP invoices...';
199
200 put_starttag('SUPPLIER_SET');
201
202 for rec in (
203 SELECT distinct
204 PV.VENDOR_ID AS SUPPLIER_ID,
205 PVS.VENDOR_SITE_ID AS SITE_ID,
206 PV.VENDOR_NAME AS SUPPLIER_NAME,
207 PV.SEGMENT1 AS SUPPLIER_NUM,
208 PVS.VENDOR_SITE_CODE AS SITE,
209 PV.NUM_1099 AS SUPPLIER_TAXPAYER_ID,
210 PV.VAT_REGISTRATION_NUM AS SUPPLIER_TAX_REGN_NUM
211 FROM FUN_NET_AP_INVS_ALL FNAP,
212 FUN_NET_BATCHES_ALL FNB,
213 AP_INVOICES_ALL API,
214 AP_LOOKUP_CODES ALC,
215 PO_VENDORS PV,
216 PO_VENDOR_SITES_ALL PVS
217 WHERE FNAP.BATCH_ID = p_batch_id
218 AND FNAP.BATCH_ID = FNB.BATCH_ID
219 AND FNAP.INVOICE_ID = API.INVOICE_ID
220 AND ALC.LOOKUP_CODE = API.INVOICE_TYPE_LOOKUP_CODE
221 AND ALC.LOOKUP_TYPE = 'INVOICE TYPE'
222 AND PV.VENDOR_ID = API.VENDOR_ID
223 AND PVS.VENDOR_SITE_ID = API.VENDOR_SITE_ID
224 ORDER BY PV.VENDOR_NAME,
225 PVS.VENDOR_SITE_CODE
226 ) loop
227
228 put_starttag('SUPPLIER_RECORD');
229
230 put_element('SUPPLIER_ID',rec.SUPPLIER_ID);
231 put_element('SITE_ID',rec.SITE_ID);
232 put_element('SUPPLIER_NAME',rec.SUPPLIER_NAME);
233 put_element('SUPPLIER_NUM',rec.SUPPLIER_NUM);
234 put_element('SITE',rec.SITE);
235 put_element('SUPPLIER_TAXPAYER_ID',rec.SUPPLIER_TAXPAYER_ID);
236 put_element('SUPPLIER_TAX_REGN_NUM',rec.SUPPLIER_TAX_REGN_NUM);
237
238
239 l_qryCtx := DBMS_XMLGEN.newContext(
240 'SELECT
241 API.INVOICE_NUM,
242 ALC.DISPLAYED_FIELD AS INVOICE_TYPE,
243 API.INVOICE_DATE AS INVOICE_DATE,
244 API.INVOICE_AMOUNT,
245 FNAP.INV_CURR_OPEN_AMT AS INVOICE_CURRENCY_OPEN_AMOUNT,
246 API.INVOICE_CURRENCY_CODE AS INVOICE_CURRENCY,
247 FNAP.OPEN_AMT AS INV_RECKONING_OPEN_AMOUNT,
248 FNB.BATCH_CURRENCY AS RECKONING_CURRENCY,
249 MIN(APS.DUE_DATE) AS DUE_DATE,
250 SUM(nvl(vat.vat_amount,0)) AS VAT_AMOUNT
251 FROM FUN_NET_AP_INVS_ALL FNAP,
252 FUN_NET_BATCHES_ALL FNB,
253 AP_INVOICES_ALL API,
254 ap_invoice_lines_all ail,
255 AP_LOOKUP_CODES ALC,
256 AP_PAYMENT_SCHEDULES_ALL APS,
257 PO_VENDORS PV,
258 PO_VENDOR_SITES_ALL PVS,
259 (select ail2.invoice_id
260 ,sum(ail2.amount) vat_amount
261 from ap_invoices_all ai2
262 ,ap_invoice_lines_all ail2
263 ,ap_tax_codes_all atc
264 where ai2.vendor_id = :SUPPLIER_ID
265 and ai2.vendor_site_id = :SITE_ID
266 and ail2.invoice_id = ai2.invoice_id
267 and ail2.line_type_lookup_code = ''TAX''
268 and atc.name = ail2.tax_classification_code
269 and atc.tax_type = ''SALES''
270 and atc.org_id = ail2.org_id
271 group by ail2.invoice_id
272 ) vat
273 WHERE FNAP.INVOICE_ID = API.INVOICE_ID
274 AND FNAP.BATCH_ID = FNB.BATCH_ID
275 AND ALC.LOOKUP_CODE = API.INVOICE_TYPE_LOOKUP_CODE
276 AND ALC.LOOKUP_TYPE = ''INVOICE TYPE''
277 AND APS.INVOICE_ID = API.INVOICE_ID
278 AND PV.VENDOR_ID = API.VENDOR_ID
279 AND PVS.VENDOR_SITE_ID = API.VENDOR_SITE_ID
280 AND FNAP.BATCH_ID = :BATCH_ID
281 AND PV.VENDOR_ID = :SUPPLIER_ID
282 AND PVS.VENDOR_SITE_ID = :SITE_ID
283 and vat.invoice_id(+) = API.INVOICE_ID
284 GROUP BY
285 API.INVOICE_NUM,
286 ALC.DISPLAYED_FIELD,
287 API.INVOICE_DATE,
288 API.INVOICE_AMOUNT,
289 FNAP.INV_CURR_OPEN_AMT,
290 API.INVOICE_CURRENCY_CODE,
291 FNAP.OPEN_AMT,
292 FNB.BATCH_CURRENCY
293 ORDER BY
294 API.INVOICE_NUM');
295
296
297 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'INVOICE_SET');
298 DBMS_XMLGEN.setRowTag(l_qryCtx, 'INVOICE_RECORD');
299 DBMS_XMLGEN.setBindValue(l_qryCtx,'BATCH_ID', p_batch_id);
300 DBMS_XMLGEN.setBindValue(l_qryCtx,'SUPPLIER_ID', rec.supplier_id);
301 DBMS_XMLGEN.setBindValue(l_qryCtx,'SITE_ID', rec.site_id);
302 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
303 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
304 l_temp_invoice_count := DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx);
305 l_invoice_count := l_invoice_count + l_temp_invoice_count;
306 DBMS_XMLGEN.closeContext(l_qryCtx);
307 clob_to_file(l_result_clob);
308
309 put_endtag('SUPPLIER_RECORD');
310
311 end loop;
312
313 put_endtag('SUPPLIER_SET');
314
315
316
317 l_debug_info := 'Select AR transactions...';
318
319 put_starttag('CUSTOMER_SET');
320
321 for rec in (
322 SELECT distinct
323 HP.PARTY_NAME AS CUSTOMER,
324 HCA.CUST_ACCOUNT_ID AS CUST_ACCOUNT_ID,
325 HCA.ACCOUNT_NUMBER AS CUSTOMER_NUMBER,
326 HCSU.LOCATION,
327 HCSU.SITE_USE_ID AS SITE_USE_ID,
328 HP.JGZZ_FISCAL_CODE AS CUST_TAXPAYER_ID,
329 HP.TAX_REFERENCE AS CUST_TAX_REGN_NUM
330 FROM FUN_NET_AR_TXNS_ALL FNAR,
331 FUN_NET_BATCHES_ALL FNB,
332 RA_CUSTOMER_TRX_ALL RCT,
333 RA_CUST_TRX_TYPES_ALL RCTT,
334 HZ_CUST_ACCOUNTS_ALL HCA,
335 HZ_PARTIES HP,
336 HZ_CUST_SITE_USES_ALL HCSU
337 WHERE FNAR.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
338 AND FNAR.BATCH_ID = FNB.BATCH_ID
339 AND RCTT.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
340 AND RCT.ORG_ID = RCTT.ORG_ID
341 AND HCA.CUST_ACCOUNT_ID = RCT.BILL_TO_CUSTOMER_ID
342 AND HP.PARTY_ID = HCA.PARTY_ID
343 AND HCSU.SITE_USE_ID = RCT.BILL_TO_SITE_USE_ID
344 AND FNAR.BATCH_ID = p_batch_id
345 ORDER BY HP.PARTY_NAME,
346 HCSU.LOCATION
347 ) loop
348
349 put_starttag('CUSTOMER_RECORD');
350
351 put_element('CUST_ACCOUNT_ID',rec.CUST_ACCOUNT_ID);
352 put_element('SITE_USE_ID',rec.SITE_USE_ID);
353 put_element('CUSTOMER',rec.CUSTOMER);
354 put_element('CUSTOMER_NUMBER',rec.CUSTOMER_NUMBER);
355 put_element('LOCATION',rec.LOCATION);
356 put_element('CUST_TAXPAYER_ID',rec.CUST_TAXPAYER_ID);
357 put_element('CUST_TAX_REGN_NUM',rec.CUST_TAX_REGN_NUM);
358
359 l_qryCtx := DBMS_XMLGEN.newContext(
360 'SELECT
361 RCT.TRX_NUMBER,
362 RCTT.NAME AS TRX_TYPE,
363 RCT.TRX_DATE,
364 SUM(APS.AMOUNT_DUE_ORIGINAL) AS TRX_AMOUNT,
365 APS.INVOICE_CURRENCY_CODE AS TRX_CURRENCY,
366 FNAR.TXN_CURR_OPEN_AMT AS TXN_CURR_OPEN_AMOUNT,
367 FNB.BATCH_CURRENCY AS RECKONING_CURRENCY,
368 FNAR.OPEN_AMT AS TRX_RECKONING_OPEN_AMOUNT,
369 MIN(APS.DUE_DATE) AS DUE_DATE,
370 sum(nvl(vat_amount,0)) AS VAT_AMOUNT -- Russian Requirement
371 FROM FUN_NET_AR_TXNS_ALL FNAR,
372 FUN_NET_BATCHES_ALL FNB,
373 RA_CUSTOMER_TRX_ALL RCT,
374 RA_CUST_TRX_TYPES_ALL RCTT,
375 AR_PAYMENT_SCHEDULES_ALL APS,
376 HZ_CUST_ACCOUNTS_ALL HCA,
377 HZ_PARTIES HP,
378 HZ_CUST_SITE_USES_ALL HCSU,
379 -- Russian Requirement
380 (select
381 rctl2.customer_trx_id
382 ,sum(rctl2.extended_amount) AS VAT_AMOUNT
383 from
384 ra_customer_trx_all rct2
385 ,ra_customer_trx_lines_all rctl2
386 ,ar_vat_tax_all_b avt
387 where
388 rct2.bill_to_customer_id=:CUST_ACCOUNT_ID
389 and rct2.bill_to_site_use_id=:SITE_USE_ID
390 and rctl2.customer_trx_id=rct2.customer_trx_id
391 and rctl2.line_type = ''TAX''
392 and avt.vat_tax_id = rctl2.vat_tax_id
393 and avt.tax_type = ''VAT''
394 group by rctl2.customer_trx_id
395 ) rctl3
396 WHERE FNAR.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
397 AND FNAR.BATCH_ID = FNB.BATCH_ID
398 AND RCTT.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
399 AND RCT.ORG_ID = RCTT.ORG_ID
400 AND APS.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
401 AND HCA.CUST_ACCOUNT_ID = RCT.BILL_TO_CUSTOMER_ID
402 AND HP.PARTY_ID = HCA.PARTY_ID
403 AND HCSU.SITE_USE_ID = RCT.BILL_TO_SITE_USE_ID
404 AND FNAR.BATCH_ID = :BATCH_ID
405 -- Detail mode
406 AND RCT.BILL_TO_CUSTOMER_ID = :CUST_ACCOUNT_ID
407 AND RCT.BILL_TO_SITE_USE_ID = :SITE_USE_ID
408 -- Russian Requirement
409 and rctl3.customer_trx_id(+) = rct.customer_trx_id
410 GROUP BY
411 RCT.TRX_NUMBER,
412 RCTT.NAME,
413 RCT.TRX_DATE,
414 APS.INVOICE_CURRENCY_CODE,
415 FNAR.TXN_CURR_OPEN_AMT,
416 FNB.BATCH_CURRENCY,
417 FNAR.OPEN_AMT
418 ORDER BY
419 RCT.TRX_NUMBER');
420
421 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'TRANSACTION_SET');
422 DBMS_XMLGEN.setRowTag(l_qryCtx, 'TRANSACTION_RECORD');
423 DBMS_XMLGEN.setBindValue(l_qryCtx,'BATCH_ID', p_batch_id);
424 DBMS_XMLGEN.setBindValue(l_qryCtx,'CUST_ACCOUNT_ID', rec.CUST_ACCOUNT_ID);
425 DBMS_XMLGEN.setBindValue(l_qryCtx,'SITE_USE_ID', rec.SITE_USE_ID);
426 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
427 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
428 l_temp_trx_count := DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx);
429 l_trx_count := l_trx_count + l_temp_trx_count;
430 DBMS_XMLGEN.closeContext(l_qryCtx);
431 clob_to_file(l_result_clob);
432
433 put_endtag('CUSTOMER_RECORD');
434
435 end loop;
436
437 put_endtag('CUSTOMER_SET');
438
439
440 put_starttag('SETUP');
441 put_element('REPORT_NAME',l_report_name);
442 put_element('BATCH_ID',p_batch_id);
443 put_element('BATCH_COUNT',l_batch_count);
444 put_element('INVOICE_COUNT',l_invoice_count);
445 put_element('TRX_COUNT',l_trx_count);
446 put_endtag('SETUP');
447
448
449 put_endtag('NETTING_REPORT');
450
451
452 EXCEPTION
453
454 WHEN OTHERS then
455 FUN_UTIL.log_conc_unexp(l_current_calling_sequence, SQLERRM);
456 APP_EXCEPTION.RAISE_EXCEPTION;
457
458 END proposed_netting_report;
459
460
461
462 PROCEDURE final_netting_report(
463 errbuf OUT NOCOPY VARCHAR2,
464 retcode OUT NOCOPY NUMBER,
465 p_batch_id IN VARCHAR2 ) IS
466
467 l_qryCtx DBMS_XMLGEN.ctxHandle;
468 l_result_clob CLOB;
469 l_current_calling_sequence varchar2(2000);
470 l_debug_info varchar2(200);
471
472 l_report_name varchar2(80) := 'Final Netting Report';
473
474 l_batch_count number;
475 l_invoice_count number;
476 l_trx_count number;
477 l_temp_invoice_count number;
478 l_temp_trx_count number;
479 l_encoding VARCHAR2(20);
480
481 BEGIN
482
483 l_current_calling_sequence := 'FUN_XML_REPORT_PKG.final_netting_report';
484 l_debug_info := 'Select Batch Info...';
485
486 l_batch_count := 0;
487 l_invoice_count := 0;
488 l_trx_count := 0;
489
490 select tag INTO l_encoding from fnd_lookup_values
491 where lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
492 and lookup_code = ( select value from v$nls_parameters where parameter='NLS_CHARACTERSET')
493 and language='US' ;
494
495 put_starttag('?xml version="1.0" encoding="'||l_encoding||'"?');
496 put_starttag('NETTING_REPORT');
497
498 l_qryCtx := DBMS_XMLGEN.newContext(
499 'SELECT HOU.NAME AS OPERATING_UNIT,
500 FNA.AGREEMENT_NAME,
501 FNA.AGREEMENT_START_DATE,
502 FNA.AGREEMENT_END_DATE,
503 FNA.PARTNER_REFERENCE,
504 CBA.BANK_ACCOUNT_NAME,
505 -- Dropped; DECODE(FNA.APPLY_EARNED_DISCOUNT
506 DECODE(FNA.SEL_REC_PAST_DUE_TXNS_FLAG, ''Y'', ''Yes'', ''N'', ''No'') AS SELECT_REC_PAST_DUE_TXNS,
507 FNA.DAYS_PAST_DUE,
508 FLC1.MEANING AS NETTING_ORDER_RULE,
509 FLC2.MEANING AS NETTING_BALANCE_RULE,
510 FLC3.MEANING AS NETTING_CURRENCY_RULE,
511 FNA.NET_CURRENCY_CODE,
512 GLC.USER_CONVERSION_TYPE AS EXCHANGE_RATE_TYPE,
513 FNB.EXCHANGE_RATE,
514 FNB.BATCH_NUMBER,
515 FNB.BATCH_NAME,
516 FNB.BATCH_CURRENCY,
517 FNB.SETTLEMENT_DATE,
518 FNB.TRANSACTION_DUE_DATE,
519 FNB.RESPONSE_DATE,
520 FNB.TOTAL_NETTED_AMT
521 FROM FUN_NET_BATCHES_ALL FNB,
522 FUN_NET_AGREEMENTS_ALL FNA,
523 HR_OPERATING_UNITS HOU,
524 CE_BANK_ACCOUNTS CBA,
525 FUN_LOOKUPS FLC1,
526 FUN_LOOKUPS FLC2,
527 FUN_LOOKUPS FLC3,
528 gl_daily_conversion_types GLC
529 WHERE FNA.AGREEMENT_ID = FNB.AGREEMENT_ID
530 AND GLC.CONVERSION_TYPE = FNB.EXCHANGE_RATE_TYPE
531 AND HOU.ORGANIZATION_ID = FNB.ORG_ID
532 AND CBA.BANK_ACCOUNT_ID = FNA.BANK_ACCOUNT_ID
533 AND FLC1.LOOKUP_TYPE = ''FUN_NET_ORDER_RULE''
534 AND FLC1.LOOKUP_CODE = FNA.NET_ORDER_RULE_CODE
535 AND FLC2.LOOKUP_TYPE = ''FUN_NET_BALANCE_RULE''
536 AND FLC2.LOOKUP_CODE = FNA.NET_BALANCE_RULE_CODE
537 AND FLC3.LOOKUP_TYPE = ''FUN_NET_CURRENCY_RULE''
538 AND FLC3.LOOKUP_CODE = FNA.NET_CURRENCY_RULE_CODE
539 AND FNB.BATCH_ID = :BATCH_ID');
540
541
542 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'BATCH_DETAILS_SET');
543 DBMS_XMLGEN.setRowTag(l_qryCtx, 'BATCH_DETAILS');
544 DBMS_XMLGEN.setBindValue(l_qryCtx,'BATCH_ID', p_batch_id);
545 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
546 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
547
548 l_batch_count := DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx);
549 DBMS_XMLGEN.closeContext(l_qryCtx);
550 clob_to_file(l_result_clob);
551
552
553 l_debug_info := 'Select AP invoices...';
554
555 put_starttag('SUPPLIER_SET');
556
557 for rec in (
558 SELECT distinct
559 PV.VENDOR_ID AS SUPPLIER_ID,
560 PVS.VENDOR_SITE_ID AS SITE_ID,
561 PV.VENDOR_NAME AS SUPPLIER_NAME,
562 PV.SEGMENT1 AS SUPPLIER_NUM,
563 PVS.VENDOR_SITE_CODE AS SITE,
564 PV.NUM_1099 AS SUPPLIER_TAXPAYER_ID,
565 PV.VAT_REGISTRATION_NUM AS SUPPLIER_TAX_REGN_NUM
566 FROM FUN_NET_AP_INVS_ALL FNAP,
567 FUN_NET_BATCHES_ALL FNB,
568 AP_INVOICES_ALL API,
569 AP_LOOKUP_CODES ALC,
570 PO_VENDORS PV,
571 PO_VENDOR_SITES_ALL PVS
572 WHERE FNAP.BATCH_ID = p_batch_id
573 AND FNAP.BATCH_ID = FNB.BATCH_ID
574 AND FNAP.INVOICE_ID = API.INVOICE_ID
575 AND ALC.LOOKUP_CODE = API.INVOICE_TYPE_LOOKUP_CODE
576 AND ALC.LOOKUP_TYPE = 'INVOICE TYPE'
577 AND PV.VENDOR_ID = API.VENDOR_ID
578 AND PVS.VENDOR_SITE_ID = API.VENDOR_SITE_ID
579 ORDER BY PV.VENDOR_NAME,
580 PVS.VENDOR_SITE_CODE
581 ) loop
582
583 put_starttag('SUPPLIER_RECORD');
584
585 put_element('SUPPLIER_ID',rec.SUPPLIER_ID);
586 put_element('SITE_ID',rec.SITE_ID);
587 put_element('SUPPLIER_NAME',rec.SUPPLIER_NAME);
588 put_element('SUPPLIER_NUM',rec.SUPPLIER_NUM);
589 put_element('SITE',rec.SITE);
590 put_element('SUPPLIER_TAXPAYER_ID',rec.SUPPLIER_TAXPAYER_ID);
591 put_element('SUPPLIER_TAX_REGN_NUM',rec.SUPPLIER_TAX_REGN_NUM);
592
593 l_qryCtx := DBMS_XMLGEN.newContext(
594 'SELECT
595 API.INVOICE_NUM,
596 ALC.DISPLAYED_FIELD AS INVOICE_TYPE,
597 API.INVOICE_DATE AS INVOICE_DATE,
598 API.INVOICE_AMOUNT AS INVOICE_AMOUNT,
599 FNAP.INV_CURR_NET_AMT AS NETTED_AMT_INV_CURR,
600 API.INVOICE_CURRENCY_CODE AS INVOICE_CURRENCY,
601 FNAP.NETTED_AMT AS NETTED_AMT_BATCH_CURR,
602 FNB.BATCH_CURRENCY AS RECKONING_CURRENCY,
603 AC.CHECK_NUMBER AS PAYMENT_NUMBER,
604 SUM(nvl(vat.vat_amount,0)) AS VAT_AMOUNT
605 FROM FUN_NET_AP_INVS_ALL FNAP,
606 FUN_NET_BATCHES_ALL FNB,
607 AP_INVOICES_ALL API,
608 AP_LOOKUP_CODES ALC,
609 PO_VENDORS PV,
610 PO_VENDOR_SITES_ALL PVS,
611 AP_CHECKS_ALL AC,
612 (select ail2.invoice_id
613 ,sum(ail2.amount) vat_amount
614 from ap_invoices_all ai2
615 ,ap_invoice_lines_all ail2
616 ,ap_tax_codes_all atc
617 where ai2.vendor_id = :SUPPLIER_ID
618 and ai2.vendor_site_id = :SITE_ID
619 and ail2.invoice_id = ai2.invoice_id
620 and ail2.line_type_lookup_code = ''TAX''
621 and atc.name = ail2.tax_classification_code
622 and atc.tax_type = ''SALES''
623 and atc.org_id = ail2.org_id
624 group by ail2.invoice_id
625 ) vat
626 WHERE FNAP.INVOICE_ID = API.INVOICE_ID
627 AND FNAP.BATCH_ID = FNB.BATCH_ID
628 AND ALC.LOOKUP_CODE = API.INVOICE_TYPE_LOOKUP_CODE
629 AND ALC.LOOKUP_TYPE = ''INVOICE TYPE''
630 AND PV.VENDOR_ID = API.VENDOR_ID
631 AND PVS.VENDOR_SITE_ID = API.VENDOR_SITE_ID
632 AND AC.CHECK_ID = FNAP.CHECK_ID(+)
633 AND FNAP.BATCH_ID = :BATCH_ID
634 AND PV.VENDOR_ID = :SUPPLIER_ID
635 AND PVS.VENDOR_SITE_ID = :SITE_ID
636 and vat.invoice_id(+) = API.INVOICE_ID
637 GROUP BY
638 API.INVOICE_NUM,
639 ALC.DISPLAYED_FIELD,
640 API.INVOICE_DATE,
641 API.INVOICE_AMOUNT,
642 FNAP.INV_CURR_NET_AMT,
643 API.INVOICE_CURRENCY_CODE,
644 FNAP.NETTED_AMT,
645 FNB.BATCH_CURRENCY,
646 AC.CHECK_NUMBER
647 ORDER BY
648 API.INVOICE_NUM');
649
650
651 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'INVOICE_SET');
652 DBMS_XMLGEN.setRowTag(l_qryCtx, 'INVOICE_RECORD');
653 DBMS_XMLGEN.setBindValue(l_qryCtx,'BATCH_ID', p_batch_id);
654 DBMS_XMLGEN.setBindValue(l_qryCtx,'SUPPLIER_ID', rec.SUPPLIER_ID);
655 DBMS_XMLGEN.setBindValue(l_qryCtx,'SITE_ID', rec.SITE_ID);
656 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
657 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
658 l_temp_invoice_count := DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx);
659 l_invoice_count := l_invoice_count + l_temp_invoice_count;
660 DBMS_XMLGEN.closeContext(l_qryCtx);
661 clob_to_file(l_result_clob);
662
663 put_endtag('SUPPLIER_RECORD');
664
665 end loop;
666
667 put_endtag('SUPPLIER_SET');
668
669
670 l_debug_info := 'Select AR transactions...';
671
672 put_starttag('CUSTOMER_SET');
673
674 for rec in (
675 SELECT distinct
676 HP.PARTY_NAME AS CUSTOMER,
677 HCA.CUST_ACCOUNT_ID AS CUST_ACCOUNT_ID,
678 HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
679 HCSU.LOCATION,
680 HCSU.SITE_USE_ID AS SITE_USE_ID,
681 HP.JGZZ_FISCAL_CODE AS CUST_TAXPAYER_ID,
682 HP.TAX_REFERENCE AS CUST_TAX_REGN_NUM
683 FROM FUN_NET_AR_TXNS_ALL FNAR,
684 FUN_NET_BATCHES_ALL FNB,
685 RA_CUSTOMER_TRX_ALL RCT,
686 RA_CUSTOMER_TRX_LINES_ALL RCTL,
687 RA_CUST_TRX_TYPES_ALL RCTT,
688 HZ_CUST_ACCOUNTS_ALL HCA,
689 HZ_PARTIES HP,
690 HZ_CUST_SITE_USES_ALL HCSU,
691 AR_CASH_RECEIPTS_ALL ACR
692 WHERE FNAR.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
693 AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID
694 AND RCTT.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
695 AND RCT.ORG_ID = RCTT.ORG_ID
696 AND HCA.CUST_ACCOUNT_ID = RCT.BILL_TO_CUSTOMER_ID
697 AND HP.PARTY_ID = HCA.PARTY_ID
698 AND HCSU.SITE_USE_ID = RCT.BILL_TO_SITE_USE_ID
699 AND ACR.CASH_RECEIPT_ID = FNAR.CASH_RECEIPT_ID
700 AND FNB.BATCH_ID = FNAR.BATCH_ID
701 AND FNAR.BATCH_ID = p_batch_id
702 ORDER BY HP.PARTY_NAME,
703 HCSU.LOCATION
704 ) loop
705
706
707 put_starttag('CUSTOMER_RECORD');
708
709 put_element('CUST_ACCOUNT_ID',rec.CUST_ACCOUNT_ID);
710 put_element('SITE_USE_ID',rec.SITE_USE_ID);
711 put_element('CUSTOMER',rec.CUSTOMER);
712 put_element('CUSTOMER_NUMBER',rec.CUSTOMER_NUMBER);
713 put_element('LOCATION',rec.LOCATION);
714 put_element('CUST_TAXPAYER_ID',rec.CUST_TAXPAYER_ID);
715 put_element('CUST_TAX_REGN_NUM',rec.CUST_TAX_REGN_NUM);
716
717 l_qryCtx := DBMS_XMLGEN.newContext(
718 'SELECT
719 RCT.TRX_NUMBER,
720 RCTT.NAME AS TRX_TYPE,
721 RCT.TRX_DATE,
722 SUM(RCTL.EXTENDED_AMOUNT) AS TRX_AMOUNT,
723 FNAR.TXN_CURR_NET_AMT AS NETTED_AMT_TRX_CURR,
724 RCT.INVOICE_CURRENCY_CODE AS TRX_CURRENCY,
725 FNAR.NETTED_AMT AS NETTED_AMT_RECKONING_CURR,
726 FNB.BATCH_CURRENCY AS RECKONING_CURRENCY,
727 ACR.RECEIPT_NUMBER,
728 sum(nvl(vat_amount,0)) AS VAT_AMOUNT -- Russian Requirement
729 FROM FUN_NET_AR_TXNS_ALL FNAR,
730 RA_CUSTOMER_TRX_ALL RCT,
731 RA_CUSTOMER_TRX_LINES_ALL RCTL,
732 RA_CUST_TRX_TYPES_ALL RCTT,
733 HZ_CUST_ACCOUNTS_ALL HCA,
734 HZ_PARTIES HP,
735 HZ_CUST_SITE_USES_ALL HCSU,
736 AR_CASH_RECEIPTS_ALL ACR,
737 FUN_NET_BATCHES_ALL FNB,
738 -- Russian Requirement
739 (select
740 rctl2.customer_trx_id
741 ,sum(rctl2.extended_amount) AS VAT_AMOUNT
742 from
743 ra_customer_trx_all rct2
744 ,ra_customer_trx_lines_all rctl2
745 ,ar_vat_tax_all_b avt
746 where
747 rct2.bill_to_customer_id=:CUST_ACCOUNT_ID
748 and rct2.bill_to_site_use_id=:SITE_USE_ID
749 and rctl2.customer_trx_id=rct2.customer_trx_id
750 and rctl2.line_type = ''TAX''
751 and avt.vat_tax_id = rctl2.vat_tax_id
752 and avt.tax_type = ''VAT''
753 group by rctl2.customer_trx_id
754 ) rctl3
755 WHERE FNAR.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
756 AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID
757 AND RCTT.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
758 AND RCT.ORG_ID = RCTT.ORG_ID
759 AND HCA.CUST_ACCOUNT_ID = RCT.BILL_TO_CUSTOMER_ID
760 AND HP.PARTY_ID = HCA.PARTY_ID
761 AND HCSU.SITE_USE_ID = RCT.BILL_TO_SITE_USE_ID
762 AND ACR.CASH_RECEIPT_ID = FNAR.CASH_RECEIPT_ID
763 AND FNB.BATCH_ID = FNAR.BATCH_ID
764 AND FNAR.BATCH_ID = :BATCH_ID
765 -- Detail mode
766 AND RCT.BILL_TO_CUSTOMER_ID = :CUST_ACCOUNT_ID
767 AND RCT.BILL_TO_SITE_USE_ID = :SITE_USE_ID
768 -- Russian Requirement
769 and rctl3.customer_trx_id(+) = rct.customer_trx_id
770 GROUP BY
771 RCT.TRX_NUMBER,
772 RCTT.NAME,
773 RCT.TRX_DATE,
774 FNAR.TXN_CURR_NET_AMT,
775 RCT.INVOICE_CURRENCY_CODE,
776 FNAR.NETTED_AMT,
777 FNB.BATCH_CURRENCY,
778 ACR.RECEIPT_NUMBER
779 ORDER BY
780 RCT.TRX_NUMBER');
781
782
783 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'TRANSACTION_SET');
784 DBMS_XMLGEN.setRowTag(l_qryCtx, 'TRANSACTION_RECORD');
785 DBMS_XMLGEN.setBindValue(l_qryCtx,'BATCH_ID', p_batch_id);
786 DBMS_XMLGEN.setBindValue(l_qryCtx,'CUST_ACCOUNT_ID', rec.CUST_ACCOUNT_ID);
787 DBMS_XMLGEN.setBindValue(l_qryCtx,'SITE_USE_ID', rec.SITE_USE_ID);
788 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
789 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
790 l_temp_trx_count := DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx);
791 l_trx_count := l_trx_count + l_temp_trx_count;
792 DBMS_XMLGEN.closeContext(l_qryCtx);
793 clob_to_file(l_result_clob);
794
795 put_endtag('CUSTOMER_RECORD');
796
797 end loop;
798
799 put_endtag('CUSTOMER_SET');
800
801
802 put_starttag('SETUP');
803 put_element('REPORT_NAME',l_report_name);
804 put_element('BATCH_ID',p_batch_id);
805 put_element('BATCH_COUNT',l_batch_count);
806 put_element('INVOICE_COUNT',l_invoice_count);
807 put_element('TRX_COUNT',l_trx_count);
808 put_endtag('SETUP');
809
810
811 put_endtag('NETTING_REPORT');
812
813 EXCEPTION
814
815 WHEN OTHERS then
816 FUN_UTIL.log_conc_unexp(l_current_calling_sequence, SQLERRM);
817 APP_EXCEPTION.RAISE_EXCEPTION;
818
819 END final_netting_report;
820
821
822 END FUN_XML_REPORT_PKG;