[Home] [Help]
PACKAGE BODY: APPS.FUN_XML_REPORT_PKG
Source
1 PACKAGE BODY FUN_XML_REPORT_PKG AS
2 /* $Header: FUNXRPTB.pls 120.19.12020000.2 2012/07/26 23:23:47 shnaraya 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 l_allow_disc_flag VARCHAR2(3); -- Bug: 8342465
128 l_net_currency_rule_code varchar(100);
129
130 BEGIN
131
132 l_current_calling_sequence := 'FUN_XML_REPORT_PKG.proposed_netting_report';
133 l_debug_info := 'Select Batch Info...';
134
135 l_batch_count := 0;
136 l_invoice_count := 0;
137 l_trx_count := 0;
138
139 -- Bug: 8342465
140 SELECT FNA.ALLOW_DISC_FLAG, fna.net_currency_rule_code
141 INTO l_allow_disc_flag, l_net_currency_rule_code
142 FROM FUN_NET_BATCHES_ALL FNB,
143 FUN_NET_AGREEMENTS_ALL FNA
144 WHERE FNA.AGREEMENT_ID = FNB.AGREEMENT_ID
145 AND FNB.BATCH_ID = p_batch_id;
146
147 select tag INTO l_encoding from fnd_lookup_values
148 where lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
149 and lookup_code = ( select value from v$nls_parameters where parameter='NLS_CHARACTERSET')
150 and language='US' ;
151
152 put_starttag('?xml version="1.0" encoding="'||l_encoding||'"?');
153 put_starttag('NETTING_REPORT');
154
155 --Bug: 13628570
156 l_qryCtx := DBMS_XMLGEN.newContext(
157 'SELECT HOU.NAME AS OPERATING_UNIT,
158 FNA.AGREEMENT_NAME,
159 SYSDATE AS REPORT_RUN_DATE,
160 FNA.AGREEMENT_START_DATE,
161 FNA.AGREEMENT_END_DATE,
162 FNA.PARTNER_REFERENCE,
163 CBA.BANK_ACCOUNT_NAME,
164 YesNo.Meaning AS SELECT_REC_PAST_DUE_TXNS,
165 FNA.DAYS_PAST_DUE,
166 FLC1.MEANING AS NETTING_ORDER_RULE,
167 FLC2.MEANING AS NETTING_BALANCE_RULE,
168 FLC3.MEANING AS NETTING_CURRENCY_RULE,
169 FNA.NET_CURRENCY_CODE,
170 GLC.USER_CONVERSION_TYPE AS EXCHANGE_RATE_TYPE,
171 FNB.EXCHANGE_RATE AS EXCHANGE_RATE,
172 FNB.BATCH_NUMBER,
173 FNB.BATCH_NAME,
174 FNB.BATCH_CURRENCY,
175 FNB.SETTLEMENT_DATE,
176 FNB.TRANSACTION_DUE_DATE,
177 FNB.RESPONSE_DATE,
178 LTRIM(TO_CHAR(FNB.TOTAL_NETTED_AMT,''999999999999999999999.999999999999'')) AS TOTAL_NETTED_AMT
179 FROM FUN_NET_BATCHES_ALL FNB,
180 FUN_NET_AGREEMENTS_ALL FNA,
181 HR_OPERATING_UNITS HOU,
182 CE_BANK_ACCOUNTS CBA,
183 FUN_LOOKUPS FLC1,
184 FUN_LOOKUPS FLC2,
185 FUN_LOOKUPS FLC3,
186 gl_daily_conversion_types glc,
187 FND_LOOKUPs YesNo
188 WHERE FNA.AGREEMENT_ID = FNB.AGREEMENT_ID
189 AND GLC.CONVERSION_TYPE = FNB.EXCHANGE_RATE_TYPE
190 AND HOU.ORGANIZATION_ID = FNB.ORG_ID
191 AND CBA.BANK_ACCOUNT_ID = FNA.BANK_ACCOUNT_ID
192 AND FLC1.LOOKUP_TYPE = ''FUN_NET_ORDER_RULE''
193 AND FLC1.LOOKUP_CODE = FNA.NET_ORDER_RULE_CODE
194 AND FLC2.LOOKUP_TYPE = ''FUN_NET_BALANCE_RULE''
195 AND FLC2.LOOKUP_CODE = FNA.NET_BALANCE_RULE_CODE
196 AND FLC3.LOOKUP_TYPE = ''FUN_NET_CURRENCY_RULE''
197 AND FLC3.LOOKUP_CODE = FNA.NET_CURRENCY_RULE_CODE
198 AND YesNo.LOOKUP_TYPE = ''YES_NO''
199 AND YesNo.LOOKUP_CODE = FNA.SEL_REC_PAST_DUE_TXNS_FLAG
200 AND FNB.BATCH_ID = :BATCH_ID');
201
202
203 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'BATCH_DETAILS_SET');
204 DBMS_XMLGEN.setRowTag(l_qryCtx, 'BATCH_DETAILS');
205 DBMS_XMLGEN.setBindValue(l_qryCtx,'BATCH_ID', p_batch_id);
206 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
207 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
208
209 l_batch_count := DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx);
210 DBMS_XMLGEN.closeContext(l_qryCtx);
211 clob_to_file(l_result_clob);
212
213
214 l_debug_info := 'Select AP invoices...';
215
216 put_starttag('SUPPLIER_SET');
217
218 for rec in (
219 SELECT distinct
220 PV.VENDOR_ID AS SUPPLIER_ID,
221 PVS.VENDOR_SITE_ID AS SITE_ID,
222 PV.VENDOR_NAME AS SUPPLIER_NAME,
223 PV.SEGMENT1 AS SUPPLIER_NUM,
224 PVS.VENDOR_SITE_CODE AS SITE,
225 PV.NUM_1099 AS SUPPLIER_TAXPAYER_ID,
226 PV.VAT_REGISTRATION_NUM AS SUPPLIER_TAX_REGN_NUM
227 FROM FUN_NET_AP_INVS_ALL FNAP,
228 FUN_NET_BATCHES_ALL FNB,
229 AP_INVOICES_ALL API,
230 AP_LOOKUP_CODES ALC,
231 PO_VENDORS PV,
232 PO_VENDOR_SITES_ALL PVS
233 WHERE FNAP.BATCH_ID = p_batch_id
234 AND FNAP.BATCH_ID = FNB.BATCH_ID
235 AND FNAP.INVOICE_ID = API.INVOICE_ID
236 AND ALC.LOOKUP_CODE = API.INVOICE_TYPE_LOOKUP_CODE
237 AND ALC.LOOKUP_TYPE = 'INVOICE TYPE'
238 AND PV.VENDOR_ID = API.VENDOR_ID
239 AND PVS.VENDOR_SITE_ID = API.VENDOR_SITE_ID
240 ORDER BY PV.VENDOR_NAME,
241 PVS.VENDOR_SITE_CODE
242 ) loop
243
244 put_starttag('SUPPLIER_RECORD');
245 put_element('INV_ALLOW_DISC_FLAG',l_allow_disc_flag);
246 put_element('SUPPLIER_ID',rec.SUPPLIER_ID);
247 put_element('SITE_ID',rec.SITE_ID);
248 put_element('SUPPLIER_NAME',rec.SUPPLIER_NAME);
249 put_element('SUPPLIER_NUM',rec.SUPPLIER_NUM);
250 put_element('SITE',rec.SITE);
251 put_element('SUPPLIER_TAXPAYER_ID',rec.SUPPLIER_TAXPAYER_ID);
252 put_element('SUPPLIER_TAX_REGN_NUM',rec.SUPPLIER_TAX_REGN_NUM);
253
254
255 l_qryCtx := DBMS_XMLGEN.newContext(
256 'SELECT
257 API.INVOICE_NUM,
258 ALC.DISPLAYED_FIELD AS INVOICE_TYPE,
259 API.INVOICE_DATE AS INVOICE_DATE,
260 LTRIM(TO_CHAR(API.INVOICE_AMOUNT,''999999999999999999999.999999999999'')) AS INVOICE_AMOUNT,
261 LTRIM(TO_CHAR(FNAP.INV_CURR_OPEN_AMT,''999999999999999999999.999999999999'')) AS INVOICE_CURRENCY_OPEN_AMOUNT,
262 LTRIM(TO_CHAR(decode(FNAP.APPLIED_DISC,0,null,decode('''||l_net_currency_rule_code||''', ''ACCOUNTING_CURRENCY'',
263 FUN_NET_ARAP_PKG.Derive_Conv_Amt(FNB.batch_id, API.invoice_id, FNAP.APPLIED_DISC, ''AP''), FNAP.APPLIED_DISC)),''999999999999999999999.999999999999'')) AS INV_APPLIED_DISC_AMOUNT,
264 LTRIM(TO_CHAR(decode(FNAP.NETTED_AMT, 0,null, decode('''||l_net_currency_rule_code||''', ''ACCOUNTING_CURRENCY'',
265 FUN_NET_ARAP_PKG.Derive_Conv_Amt(FNB.batch_id, API.invoice_id, FNAP.NETTED_AMT, ''AP''), FNAP.NETTED_AMT)),''999999999999999999999.999999999999'')) AS NETTED_AMT_INV_CURR,
266 API.INVOICE_CURRENCY_CODE AS INVOICE_CURRENCY,
267 LTRIM(TO_CHAR(FNAP.OPEN_AMT,''999999999999999999999.999999999999'')) AS INV_RECKONING_OPEN_AMOUNT,
268 FNB.BATCH_CURRENCY AS RECKONING_CURRENCY,
269 MIN(APS.DUE_DATE) AS DUE_DATE,
270 LTRIM(TO_CHAR(SUM(nvl(vat.vat_amount,0)),''999999999999999999999.999999999999'')) AS VAT_AMOUNT
271 FROM FUN_NET_AP_INVS_ALL FNAP,
272 FUN_NET_BATCHES_ALL FNB,
273 AP_INVOICES_ALL API,
274 ap_invoice_lines_all ail,
275 AP_LOOKUP_CODES ALC,
276 AP_PAYMENT_SCHEDULES_ALL APS,
277 PO_VENDORS PV,
278 PO_VENDOR_SITES_ALL PVS,
279 (select ail2.invoice_id
280 ,sum(ail2.amount) vat_amount
281 from ap_invoices_all ai2
282 ,ap_invoice_lines_all ail2
283 ,ap_tax_codes_all atc
284 where ai2.vendor_id = :SUPPLIER_ID
285 and ai2.vendor_site_id = :SITE_ID
286 and ail2.invoice_id = ai2.invoice_id
287 and ail2.line_type_lookup_code = ''TAX''
288 and atc.name = ail2.tax_classification_code
289 and atc.tax_type = ''SALES''
290 and atc.org_id = ail2.org_id
291 group by ail2.invoice_id
292 ) vat
293 WHERE FNAP.INVOICE_ID = API.INVOICE_ID
294 AND FNAP.BATCH_ID = FNB.BATCH_ID
295 AND ALC.LOOKUP_CODE = API.INVOICE_TYPE_LOOKUP_CODE
296 AND ALC.LOOKUP_TYPE = ''INVOICE TYPE''
297 AND APS.INVOICE_ID = API.INVOICE_ID
298 AND PV.VENDOR_ID = API.VENDOR_ID
299 AND PVS.VENDOR_SITE_ID = API.VENDOR_SITE_ID
300 AND FNAP.BATCH_ID = :BATCH_ID
301 AND PV.VENDOR_ID = :SUPPLIER_ID
302 AND PVS.VENDOR_SITE_ID = :SITE_ID
303 and vat.invoice_id(+) = API.INVOICE_ID
304 GROUP BY
305 API.INVOICE_NUM,
306 ALC.DISPLAYED_FIELD,
307 API.INVOICE_DATE,
308 API.INVOICE_AMOUNT,
309 FNAP.INV_CURR_OPEN_AMT,
310 API.INVOICE_CURRENCY_CODE,
311 FNAP.OPEN_AMT,
312 FNB.BATCH_CURRENCY,
313 FNB.batch_id, API.invoice_id, FNAP.APPLIED_DISC,FNAP.NETTED_AMT
314 ORDER BY
315 API.INVOICE_NUM');
316
317
318 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'INVOICE_SET');
319 DBMS_XMLGEN.setRowTag(l_qryCtx, 'INVOICE_RECORD');
320 DBMS_XMLGEN.setBindValue(l_qryCtx,'BATCH_ID', p_batch_id);
321 DBMS_XMLGEN.setBindValue(l_qryCtx,'SUPPLIER_ID', rec.supplier_id);
322 DBMS_XMLGEN.setBindValue(l_qryCtx,'SITE_ID', rec.site_id);
323 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
324 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
325 l_temp_invoice_count := DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx);
326 l_invoice_count := l_invoice_count + l_temp_invoice_count;
327 DBMS_XMLGEN.closeContext(l_qryCtx);
328 clob_to_file(l_result_clob);
329
330 put_endtag('SUPPLIER_RECORD');
331
332 end loop;
333
334 put_endtag('SUPPLIER_SET');
335
336
337
338 l_debug_info := 'Select AR transactions...';
339
340 put_starttag('CUSTOMER_SET');
341
342 for rec in (
343 SELECT distinct
344 HP.PARTY_NAME AS CUSTOMER,
345 HCA.CUST_ACCOUNT_ID AS CUST_ACCOUNT_ID,
346 HCA.ACCOUNT_NUMBER AS CUSTOMER_NUMBER,
347 HCSU.LOCATION,
348 HCSU.SITE_USE_ID AS SITE_USE_ID,
349 HP.JGZZ_FISCAL_CODE AS CUST_TAXPAYER_ID,
350 HP.TAX_REFERENCE AS CUST_TAX_REGN_NUM
351 FROM FUN_NET_AR_TXNS_ALL FNAR,
352 FUN_NET_BATCHES_ALL FNB,
353 RA_CUSTOMER_TRX_ALL RCT,
354 RA_CUST_TRX_TYPES_ALL RCTT,
355 HZ_CUST_ACCOUNTS_ALL HCA,
356 HZ_PARTIES HP,
357 HZ_CUST_SITE_USES_ALL HCSU
358 WHERE FNAR.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
359 AND FNAR.BATCH_ID = FNB.BATCH_ID
360 AND RCTT.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
361 AND RCT.ORG_ID = RCTT.ORG_ID
362 AND HCA.CUST_ACCOUNT_ID = RCT.BILL_TO_CUSTOMER_ID
363 AND HP.PARTY_ID = HCA.PARTY_ID
364 AND HCSU.SITE_USE_ID = RCT.BILL_TO_SITE_USE_ID
365 AND FNAR.BATCH_ID = p_batch_id
366 ORDER BY HP.PARTY_NAME,
367 HCSU.LOCATION
368 ) loop
369
370 put_starttag('CUSTOMER_RECORD');
371 put_element('TRX_ALLOW_DISC_FLAG',l_allow_disc_flag);
372 put_element('CUST_ACCOUNT_ID',rec.CUST_ACCOUNT_ID);
373 put_element('SITE_USE_ID',rec.SITE_USE_ID);
374 put_element('CUSTOMER',rec.CUSTOMER);
375 put_element('CUSTOMER_NUMBER',rec.CUSTOMER_NUMBER);
376 put_element('LOCATION',rec.LOCATION);
377 put_element('CUST_TAXPAYER_ID',rec.CUST_TAXPAYER_ID);
378 put_element('CUST_TAX_REGN_NUM',rec.CUST_TAX_REGN_NUM);
379
380 l_qryCtx := DBMS_XMLGEN.newContext(
381 'SELECT
382 RCT.TRX_NUMBER,
383 RCTT.NAME AS TRX_TYPE,
384 RCT.TRX_DATE,
385 LTRIM(TO_CHAR(SUM(APS.AMOUNT_DUE_ORIGINAL),''999999999999999999999.999999999999'')) AS TRX_AMOUNT,
386 APS.INVOICE_CURRENCY_CODE AS TRX_CURRENCY,
387 LTRIM(TO_CHAR(FNAR.TXN_CURR_OPEN_AMT,''999999999999999999999.999999999999'')) AS TXN_CURR_OPEN_AMOUNT,
388 LTRIM(TO_CHAR(decode(FNAR.APPLIED_DISC,0,null,decode('''||l_net_currency_rule_code||''', ''ACCOUNTING_CURRENCY'',
389 FUN_NET_ARAP_PKG.Derive_Conv_Amt(FNB.batch_id, RCT.CUSTOMER_TRX_ID, FNAR.APPLIED_DISC, ''AR''), FNAR.APPLIED_DISC)),''999999999999999999999.999999999999'')) AS TRX_APPLIED_DISC_AMOUNT,
390 LTRIM(TO_CHAR(decode(FNAR.NETTED_AMT,0,null,decode('''||l_net_currency_rule_code||''', ''ACCOUNTING_CURRENCY'',
391 FUN_NET_ARAP_PKG.Derive_Conv_Amt(FNB.batch_id, RCT.CUSTOMER_TRX_ID, FNAR.NETTED_AMT, ''AR''), FNAR.NETTED_AMT)),''999999999999999999999.999999999999'')) AS NETTED_AMT_TRX_CURR,
392 FNB.BATCH_CURRENCY AS RECKONING_CURRENCY,
393 LTRIM(TO_CHAR(FNAR.OPEN_AMT,''999999999999999999999.999999999999'')) AS TRX_RECKONING_OPEN_AMOUNT,
394 MIN(APS.DUE_DATE) AS DUE_DATE,
395 LTRIM(TO_CHAR(sum(nvl(vat_amount,0)),''999999999999999999999.999999999999'')) AS VAT_AMOUNT -- Russian Requirement
396 FROM FUN_NET_AR_TXNS_ALL FNAR,
397 FUN_NET_BATCHES_ALL FNB,
398 RA_CUSTOMER_TRX_ALL RCT,
399 RA_CUST_TRX_TYPES_ALL RCTT,
400 AR_PAYMENT_SCHEDULES_ALL APS,
401 HZ_CUST_ACCOUNTS_ALL HCA,
402 HZ_PARTIES HP,
403 HZ_CUST_SITE_USES_ALL HCSU,
404 -- Russian Requirement
405 (select
406 rctl2.customer_trx_id
407 ,sum(rctl2.extended_amount) AS VAT_AMOUNT
408 from
409 ra_customer_trx_all rct2
410 ,ra_customer_trx_lines_all rctl2
411 ,ar_vat_tax_all_b avt
412 where
413 rct2.bill_to_customer_id=:CUST_ACCOUNT_ID
414 and rct2.bill_to_site_use_id=:SITE_USE_ID
415 and rctl2.customer_trx_id=rct2.customer_trx_id
416 and rctl2.line_type = ''TAX''
417 and avt.vat_tax_id = rctl2.vat_tax_id
418 and avt.tax_type = ''VAT''
419 group by rctl2.customer_trx_id
420 ) rctl3
421 WHERE FNAR.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
422 AND FNAR.BATCH_ID = FNB.BATCH_ID
423 AND RCTT.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
424 AND RCT.ORG_ID = RCTT.ORG_ID
425 AND APS.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
426 AND HCA.CUST_ACCOUNT_ID = RCT.BILL_TO_CUSTOMER_ID
427 AND HP.PARTY_ID = HCA.PARTY_ID
428 AND HCSU.SITE_USE_ID = RCT.BILL_TO_SITE_USE_ID
429 AND FNAR.BATCH_ID = :BATCH_ID
430 -- Detail mode
431 AND RCT.BILL_TO_CUSTOMER_ID = :CUST_ACCOUNT_ID
432 AND RCT.BILL_TO_SITE_USE_ID = :SITE_USE_ID
433 -- Russian Requirement
434 and rctl3.customer_trx_id(+) = rct.customer_trx_id
435 GROUP BY
436 RCT.TRX_NUMBER,
437 RCTT.NAME,
438 RCT.TRX_DATE,
439 APS.INVOICE_CURRENCY_CODE,
440 FNAR.TXN_CURR_OPEN_AMT,
441 FNB.BATCH_CURRENCY,
442 FNAR.OPEN_AMT,FNB.batch_id, RCT.CUSTOMER_TRX_ID, FNAR.APPLIED_DISC,FNAR.NETTED_AMT
443 ORDER BY
444 RCT.TRX_NUMBER');
445
446 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'TRANSACTION_SET');
447 DBMS_XMLGEN.setRowTag(l_qryCtx, 'TRANSACTION_RECORD');
448 DBMS_XMLGEN.setBindValue(l_qryCtx,'BATCH_ID', p_batch_id);
449 DBMS_XMLGEN.setBindValue(l_qryCtx,'CUST_ACCOUNT_ID', rec.CUST_ACCOUNT_ID);
450 DBMS_XMLGEN.setBindValue(l_qryCtx,'SITE_USE_ID', rec.SITE_USE_ID);
451 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
452 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
453 l_temp_trx_count := DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx);
454 l_trx_count := l_trx_count + l_temp_trx_count;
455 DBMS_XMLGEN.closeContext(l_qryCtx);
456 clob_to_file(l_result_clob);
457
458 put_endtag('CUSTOMER_RECORD');
459
460 end loop;
461
462 put_endtag('CUSTOMER_SET');
463
464
465 put_starttag('SETUP');
466 put_element('REPORT_NAME',l_report_name);
467 put_element('BATCH_ID',p_batch_id);
468 put_element('BATCH_COUNT',l_batch_count);
469 put_element('INVOICE_COUNT',l_invoice_count);
470 put_element('TRX_COUNT',l_trx_count);
471 put_endtag('SETUP');
472
473
474 put_endtag('NETTING_REPORT');
475
476
477 EXCEPTION
478
479 WHEN OTHERS then
480 FUN_UTIL.log_conc_unexp(l_current_calling_sequence, SQLERRM);
481 APP_EXCEPTION.RAISE_EXCEPTION;
482
483 END proposed_netting_report;
484
485
486
487 PROCEDURE final_netting_report(
488 errbuf OUT NOCOPY VARCHAR2,
489 retcode OUT NOCOPY NUMBER,
490 p_batch_id IN VARCHAR2 ) IS
491
492 l_qryCtx DBMS_XMLGEN.ctxHandle;
493 l_result_clob CLOB;
494 l_current_calling_sequence varchar2(2000);
495 l_debug_info varchar2(200);
496
497 l_report_name varchar2(80) := 'Final Netting Report';
498
499 l_batch_count number;
500 l_invoice_count number;
501 l_trx_count number;
502 l_temp_invoice_count number;
503 l_temp_trx_count number;
504 l_encoding VARCHAR2(20);
505 l_allow_disc_flag VARCHAR2(3); -- Bug: 8342465
506 l_net_currency_rule_code varchar2(100);
507
508 BEGIN
509
510 l_current_calling_sequence := 'FUN_XML_REPORT_PKG.final_netting_report';
511 l_debug_info := 'Select Batch Info...';
512
513 l_batch_count := 0;
514 l_invoice_count := 0;
515 l_trx_count := 0;
516
517 -- Bug: 8342465
518 SELECT FNA.ALLOW_DISC_FLAG, fna.net_currency_rule_code
519 INTO l_allow_disc_flag, l_net_currency_rule_code
520 FROM FUN_NET_BATCHES_ALL FNB,
521 FUN_NET_AGREEMENTS_ALL FNA
522 WHERE FNA.AGREEMENT_ID = FNB.AGREEMENT_ID
523 AND FNB.BATCH_ID = p_batch_id;
524
525 select tag INTO l_encoding from fnd_lookup_values
526 where lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
527 and lookup_code = ( select value from v$nls_parameters where parameter='NLS_CHARACTERSET')
528 and language='US' ;
529
530 put_starttag('?xml version="1.0" encoding="'||l_encoding||'"?');
531 put_starttag('NETTING_REPORT');
532
533 --Bug: 13628570
534
535 l_qryCtx := DBMS_XMLGEN.newContext(
536 'SELECT HOU.NAME AS OPERATING_UNIT,
537 FNA.AGREEMENT_NAME,
538 SYSDATE AS REPORT_RUN_DATE,
539 FNA.AGREEMENT_START_DATE,
540 FNA.AGREEMENT_END_DATE,
541 FNA.PARTNER_REFERENCE,
542 CBA.BANK_ACCOUNT_NAME,
543 -- Dropped; DECODE(FNA.APPLY_EARNED_DISCOUNT
544 YesNo.Meaning AS SELECT_REC_PAST_DUE_TXNS,
545 FNA.DAYS_PAST_DUE,
546 FLC1.MEANING AS NETTING_ORDER_RULE,
547 FLC2.MEANING AS NETTING_BALANCE_RULE,
548 FLC3.MEANING AS NETTING_CURRENCY_RULE,
549 FNA.NET_CURRENCY_CODE,
550 GLC.USER_CONVERSION_TYPE AS EXCHANGE_RATE_TYPE,
551 FNB.EXCHANGE_RATE AS EXCHANGE_RATE,
552 FNB.BATCH_NUMBER,
553 FNB.BATCH_NAME,
554 FNB.BATCH_CURRENCY,
555 FNB.SETTLEMENT_DATE,
556 FNB.TRANSACTION_DUE_DATE,
557 FNB.RESPONSE_DATE,
558 LTRIM(TO_CHAR(FNB.TOTAL_NETTED_AMT,''999999999999999999999.999999999999'')) AS TOTAL_NETTED_AMT
559 FROM FUN_NET_BATCHES_ALL FNB,
560 FUN_NET_AGREEMENTS_ALL FNA,
561 HR_OPERATING_UNITS HOU,
562 CE_BANK_ACCOUNTS CBA,
563 FUN_LOOKUPS FLC1,
564 FUN_LOOKUPS FLC2,
565 FUN_LOOKUPS FLC3,
566 FND_LOOKUPS YesNo,
567 gl_daily_conversion_types GLC
568 WHERE FNA.AGREEMENT_ID = FNB.AGREEMENT_ID
569 AND GLC.CONVERSION_TYPE = FNB.EXCHANGE_RATE_TYPE
570 AND HOU.ORGANIZATION_ID = FNB.ORG_ID
571 AND CBA.BANK_ACCOUNT_ID = FNA.BANK_ACCOUNT_ID
572 AND FLC1.LOOKUP_TYPE = ''FUN_NET_ORDER_RULE''
573 AND FLC1.LOOKUP_CODE = FNA.NET_ORDER_RULE_CODE
574 AND FLC2.LOOKUP_TYPE = ''FUN_NET_BALANCE_RULE''
575 AND FLC2.LOOKUP_CODE = FNA.NET_BALANCE_RULE_CODE
576 AND FLC3.LOOKUP_TYPE = ''FUN_NET_CURRENCY_RULE''
577 AND FLC3.LOOKUP_CODE = FNA.NET_CURRENCY_RULE_CODE
578 AND YesNo.LOOKUP_TYPE = ''YES_NO''
579 AND YesNo.LOOKUP_CODE = FNA.SEL_REC_PAST_DUE_TXNS_FLAG
580 AND FNB.BATCH_ID = :BATCH_ID');
581
582
583 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'BATCH_DETAILS_SET');
584 DBMS_XMLGEN.setRowTag(l_qryCtx, 'BATCH_DETAILS');
585 DBMS_XMLGEN.setBindValue(l_qryCtx,'BATCH_ID', p_batch_id);
586 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
587 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
588
589 l_batch_count := DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx);
590 DBMS_XMLGEN.closeContext(l_qryCtx);
591 clob_to_file(l_result_clob);
592
593
594 l_debug_info := 'Select AP invoices...';
595
596 put_starttag('SUPPLIER_SET');
597
598 for rec in (
599 SELECT distinct
600 PV.VENDOR_ID AS SUPPLIER_ID,
601 PVS.VENDOR_SITE_ID AS SITE_ID,
602 PV.VENDOR_NAME AS SUPPLIER_NAME,
603 PV.SEGMENT1 AS SUPPLIER_NUM,
604 PVS.VENDOR_SITE_CODE AS SITE,
605 PV.NUM_1099 AS SUPPLIER_TAXPAYER_ID,
606 PV.VAT_REGISTRATION_NUM AS SUPPLIER_TAX_REGN_NUM,
607 PVS.ADDRESS_LINE1 AS SUPPLIER_ADDRESS1,
608 PVS.ADDRESS_LINE2 AS SUPPLIER_ADDRESS2,
609 PVS.ADDRESS_LINE3 AS SUPPLIER_ADDRESS3,
610 PVS.CITY AS SUPPLIER_CITY,
611 PVS.STATE AS SUPPLIER_STATE,
612 PVS.ZIP AS SUPPLIER_ZIP,
613 PVC.FIRST_NAME||' '||PVC.LAST_NAME AS CONTACT_FIRST_LAST_NAME
614 FROM FUN_NET_AP_INVS_ALL FNAP,
615 FUN_NET_BATCHES_ALL FNB,
616 AP_INVOICES_ALL API,
617 AP_LOOKUP_CODES ALC,
618 PO_VENDORS PV,
619 PO_VENDOR_SITES_ALL PVS,
620 PO_VENDOR_CONTACTS PVC
621 WHERE FNAP.BATCH_ID = p_batch_id
622 AND FNAP.BATCH_ID = FNB.BATCH_ID
623 AND FNAP.INVOICE_ID = API.INVOICE_ID
624 AND ALC.LOOKUP_CODE = API.INVOICE_TYPE_LOOKUP_CODE
625 AND ALC.LOOKUP_TYPE = 'INVOICE TYPE'
626 AND PV.VENDOR_ID = API.VENDOR_ID
627 AND PVS.VENDOR_SITE_ID = API.VENDOR_SITE_ID
628 AND PVS.VENDOR_SITE_ID = PVC.VENDOR_SITE_ID (+)
629 AND NVL(TRUNC(PVC.INACTIVE_DATE (+) ), SYSDATE + 1) > SYSDATE
630 AND NVL(PVC.VENDOR_CONTACT_ID , -9999) = (
631 SELECT VENDOR_CONTACT_ID
632 FROM PO_VENDOR_CONTACTS
633 WHERE VENDOR_SITE_ID = PVC.VENDOR_SITE_ID
634 AND NVL(TRUNC(INACTIVE_DATE), SYSDATE + 1)
635 > SYSDATE
636 AND ROWNUM = 1
637 UNION
638 SELECT -9999
639 FROM DUAL
640 WHERE PVC.VENDOR_CONTACT_ID IS NULL)
641
642 ORDER BY PV.VENDOR_NAME,
643 PVS.VENDOR_SITE_CODE
644
645 ) loop
646
647 put_starttag('SUPPLIER_RECORD');
648 put_element('INV_ALLOW_DISC_FLAG',l_allow_disc_flag);
649 put_element('SUPPLIER_ID',rec.SUPPLIER_ID);
650 put_element('SITE_ID',rec.SITE_ID);
651 put_element('SUPPLIER_NAME',rec.SUPPLIER_NAME);
652 put_element('SUPPLIER_NUM',rec.SUPPLIER_NUM);
653 put_element('SITE',rec.SITE);
654 put_element('SUPPLIER_TAXPAYER_ID',rec.SUPPLIER_TAXPAYER_ID);
655 put_element('SUPPLIER_TAX_REGN_NUM',rec.SUPPLIER_TAX_REGN_NUM);
656 put_element('SUPPLIER_ADDRESS1',rec.SUPPLIER_ADDRESS1);
657 put_element('SUPPLIER_ADDRESS2',rec.SUPPLIER_ADDRESS2);
658 put_element('SUPPLIER_ADDRESS3',rec.SUPPLIER_ADDRESS3);
659 put_element('SUPPLIER_CITY',rec.SUPPLIER_CITY);
660 put_element('SUPPLIER_STATE',rec.SUPPLIER_STATE);
661 put_element('SUPPLIER_ZIP',rec.SUPPLIER_ZIP);
662 put_element('CONTACT_FIRST_LAST_NAME',rec.CONTACT_FIRST_LAST_NAME);
663
664
665 l_qryCtx := DBMS_XMLGEN.newContext(
666 'SELECT
667 API.INVOICE_NUM,
668 ALC.DISPLAYED_FIELD AS INVOICE_TYPE,
669 API.INVOICE_DATE AS INVOICE_DATE,
670 LTRIM(TO_CHAR(API.INVOICE_AMOUNT,''999999999999999999999.999999999999'')) AS INVOICE_AMOUNT,
671 LTRIM(TO_CHAR(decode(FNAP.APPLIED_DISC, 0,null, decode('''||l_net_currency_rule_code||''', ''ACCOUNTING_CURRENCY'',
672 FUN_NET_ARAP_PKG.Derive_Conv_Amt(FNB.batch_id, API.invoice_id, FNAP.APPLIED_DISC, ''AP''), FNAP.APPLIED_DISC)),''999999999999999999999.999999999999'')) AS INV_APPLIED_DISC_AMOUNT,
673 LTRIM(TO_CHAR(decode(FNAP.INV_CURR_NET_AMT, 0, null, FNAP.INV_CURR_NET_AMT),''999999999999999999999.999999999999'')) AS NETTED_AMT_INV_CURR,
674 API.INVOICE_CURRENCY_CODE AS INVOICE_CURRENCY,
675 LTRIM(TO_CHAR(decode(FNAP.NETTED_AMT,0,null,FNAP.NETTED_AMT),''999999999999999999999.999999999999'')) AS NETTED_AMT_BATCH_CURR,
676 FNB.BATCH_CURRENCY AS RECKONING_CURRENCY,
677 AC.CHECK_NUMBER AS PAYMENT_NUMBER,
678 LTRIM(TO_CHAR(SUM(nvl(vat.vat_amount,0)),''999999999999999999999.999999999999'')) AS VAT_AMOUNT
679 FROM FUN_NET_AP_INVS_ALL FNAP,
680 FUN_NET_BATCHES_ALL FNB,
681 AP_INVOICES_ALL API,
682 AP_LOOKUP_CODES ALC,
683 PO_VENDORS PV,
684 PO_VENDOR_SITES_ALL PVS,
685 AP_CHECKS_ALL AC,
686 (select ail2.invoice_id
687 ,sum(ail2.amount) vat_amount
688 from ap_invoices_all ai2
689 ,ap_invoice_lines_all ail2
690 ,ap_tax_codes_all atc
691 where ai2.vendor_id = :SUPPLIER_ID
692 and ai2.vendor_site_id = :SITE_ID
693 and ail2.invoice_id = ai2.invoice_id
694 and ail2.line_type_lookup_code = ''TAX''
695 and atc.name = ail2.tax_classification_code
696 and atc.tax_type = ''SALES''
697 and atc.org_id = ail2.org_id
698 group by ail2.invoice_id
699 ) vat
700 WHERE FNAP.INVOICE_ID = API.INVOICE_ID
701 AND FNAP.BATCH_ID = FNB.BATCH_ID
702 AND ALC.LOOKUP_CODE = API.INVOICE_TYPE_LOOKUP_CODE
703 AND ALC.LOOKUP_TYPE = ''INVOICE TYPE''
704 AND PV.VENDOR_ID = API.VENDOR_ID
705 AND PVS.VENDOR_SITE_ID = API.VENDOR_SITE_ID
706 AND AC.CHECK_ID = FNAP.CHECK_ID(+)
707 AND FNAP.BATCH_ID = :BATCH_ID
708 AND PV.VENDOR_ID = :SUPPLIER_ID
709 AND PVS.VENDOR_SITE_ID = :SITE_ID
710 and vat.invoice_id(+) = API.INVOICE_ID
711 GROUP BY
712 API.INVOICE_NUM,
713 ALC.DISPLAYED_FIELD,
714 API.INVOICE_DATE,
715 API.INVOICE_AMOUNT,
716 FNAP.INV_CURR_NET_AMT,
717 API.INVOICE_CURRENCY_CODE,
718 FNAP.NETTED_AMT,
719 FNB.BATCH_CURRENCY,
720 AC.CHECK_NUMBER,FNB.batch_id, API.invoice_id, FNAP.APPLIED_DISC
721 ORDER BY
722 API.INVOICE_NUM');
723
724
725 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'INVOICE_SET');
726 DBMS_XMLGEN.setRowTag(l_qryCtx, 'INVOICE_RECORD');
727 DBMS_XMLGEN.setBindValue(l_qryCtx,'BATCH_ID', p_batch_id);
728 DBMS_XMLGEN.setBindValue(l_qryCtx,'SUPPLIER_ID', rec.SUPPLIER_ID);
729 DBMS_XMLGEN.setBindValue(l_qryCtx,'SITE_ID', rec.SITE_ID);
730 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
731 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
732 l_temp_invoice_count := DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx);
733 l_invoice_count := l_invoice_count + l_temp_invoice_count;
734 DBMS_XMLGEN.closeContext(l_qryCtx);
735 clob_to_file(l_result_clob);
736
737 put_endtag('SUPPLIER_RECORD');
738
739 end loop;
740
741 put_endtag('SUPPLIER_SET');
742
743
744 l_debug_info := 'Select AR transactions...';
745
746 put_starttag('CUSTOMER_SET');
747
748 for rec in (
749 SELECT DISTINCT HP.PARTY_NAME AS CUSTOMER,
750 HCA.CUST_ACCOUNT_ID AS CUST_ACCOUNT_ID,
751 HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
752 HCSU.LOCATION,
753 HCSU.SITE_USE_ID AS SITE_USE_ID,
754 HP.JGZZ_FISCAL_CODE AS CUST_TAXPAYER_ID,
755 HP.TAX_REFERENCE AS CUST_TAX_REGN_NUM,
756 HZL.ADDRESS1 AS CUSTOMER_ADDRESS1,
757 HZL.ADDRESS2 AS CUSTOMER_ADDRESS2,
758 HZL.ADDRESS3 AS CUSTOMER_ADDRESS3,
759 HZL.CITY AS CUSTOMER_CITY,
760 HZL.STATE AS CUSTOMER_STATE,
761 HZL.POSTAL_CODE AS CUSTOMER_POSTAL_CODE
762 FROM FUN_NET_AR_TXNS_ALL FNAR,
763 FUN_NET_BATCHES_ALL FNB,
764 RA_CUSTOMER_TRX_ALL RCT,
765 RA_CUSTOMER_TRX_LINES_ALL RCTL,
766 RA_CUST_TRX_TYPES_ALL RCTT,
767 HZ_CUST_ACCOUNTS_ALL HCA,
768 HZ_PARTIES HP,
769 HZ_CUST_SITE_USES_ALL HCSU,
770 AR_CASH_RECEIPTS_ALL ACR,
771 HZ_CUST_ACCT_SITES_ALL HCAS,
772 HZ_PARTY_SITES HPS,
773 HZ_LOCATIONS HZL
774 WHERE FNAR.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
775 AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID
776 AND RCTT.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
777 AND RCT.ORG_ID = RCTT.ORG_ID
778 AND HCA.CUST_ACCOUNT_ID = RCT.BILL_TO_CUSTOMER_ID
779 AND HP.PARTY_ID = HCA.PARTY_ID
780 AND HCSU.SITE_USE_ID = RCT.BILL_TO_SITE_USE_ID
781 AND ACR.CASH_RECEIPT_ID = FNAR.CASH_RECEIPT_ID
782 AND FNB.BATCH_ID = FNAR.BATCH_ID
783 AND FNAR.BATCH_ID = p_batch_id
784 AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
785 AND HPS.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
786 AND HZL.LOCATION_ID = HPS.LOCATION_ID
787 ORDER BY HP.PARTY_NAME,
788 HCSU.LOCATION ) loop
789
790
791 put_starttag('CUSTOMER_RECORD');
792 put_element('TRX_ALLOW_DISC_FLAG',l_allow_disc_flag);
793 put_element('CUST_ACCOUNT_ID',rec.CUST_ACCOUNT_ID);
794 put_element('SITE_USE_ID',rec.SITE_USE_ID);
795 put_element('CUSTOMER',rec.CUSTOMER);
796 put_element('CUSTOMER_NUMBER',rec.CUSTOMER_NUMBER);
797 put_element('LOCATION',rec.LOCATION);
798 put_element('CUST_TAXPAYER_ID',rec.CUST_TAXPAYER_ID);
799 put_element('CUST_TAX_REGN_NUM',rec.CUST_TAX_REGN_NUM);
800 put_element('CUSTOMER_ADDRESS1',rec.CUSTOMER_ADDRESS1);
801 put_element('CUSTOMER_ADDRESS2',rec.CUSTOMER_ADDRESS2);
802 put_element('CUSTOMER_ADDRESS3',rec.CUSTOMER_ADDRESS3);
803 put_element('CUSTOMER_CITY',rec.CUSTOMER_CITY);
804 put_element('CUSTOMER_STATE',rec.CUSTOMER_STATE);
805 put_element('CUSTOMER_POSTAL_CODE',rec.CUSTOMER_POSTAL_CODE);
806 --put_element('CONTACT_CUSTOMER_NAME',rec.CONTACT_CUSTOMER_NAME); /8787753
807
808 l_qryCtx := DBMS_XMLGEN.newContext(
809 'SELECT
810 RCT.TRX_NUMBER,
811 RCTT.NAME AS TRX_TYPE,
812 RCT.TRX_DATE,
813 SUM(RCTL.EXTENDED_AMOUNT) AS TRX_AMOUNT,
814 LTRIM(TO_CHAR(decode(FNAR.APPLIED_DISC,0,null, decode('''||l_net_currency_rule_code||''', ''ACCOUNTING_CURRENCY'',
815 FUN_NET_ARAP_PKG.Derive_Conv_Amt(FNB.batch_id, RCT.CUSTOMER_TRX_ID, FNAR.APPLIED_DISC, ''AR''), FNAR.APPLIED_DISC)),''999999999999999999999.999999999999'')) AS TRX_APPLIED_DISC_AMOUNT,
816 LTRIM(TO_CHAR(decode(FNAR.TXN_CURR_NET_AMT,0,null,FNAR.TXN_CURR_NET_AMT),''999999999999999999999.999999999999'')) AS NETTED_AMT_TRX_CURR,
817 RCT.INVOICE_CURRENCY_CODE AS TRX_CURRENCY,
818 LTRIM(TO_CHAR(decode(FNAR.NETTED_AMT, 0, null, FNAR.NETTED_AMT),''999999999999999999999.999999999999'')) AS NETTED_AMT_RECKONING_CURR,
819 FNB.BATCH_CURRENCY AS RECKONING_CURRENCY,
820 ACR.RECEIPT_NUMBER,
821 LTRIM(TO_CHAR(sum(nvl(vat_amount,0)),''999999999999999999999.999999999999'')) AS VAT_AMOUNT -- Russian Requirement
822 FROM FUN_NET_AR_TXNS_ALL FNAR,
823 RA_CUSTOMER_TRX_ALL RCT,
824 RA_CUSTOMER_TRX_LINES_ALL RCTL,
825 RA_CUST_TRX_TYPES_ALL RCTT,
826 HZ_CUST_ACCOUNTS_ALL HCA,
827 HZ_PARTIES HP,
828 HZ_CUST_SITE_USES_ALL HCSU,
829 AR_CASH_RECEIPTS_ALL ACR,
830 FUN_NET_BATCHES_ALL FNB,
831 -- Russian Requirement
832 (select
833 rctl2.customer_trx_id
834 ,sum(rctl2.extended_amount) AS VAT_AMOUNT
835 from
836 ra_customer_trx_all rct2
837 ,ra_customer_trx_lines_all rctl2
838 ,ar_vat_tax_all_b avt
839 where
840 rct2.bill_to_customer_id=:CUST_ACCOUNT_ID
841 and rct2.bill_to_site_use_id=:SITE_USE_ID
842 and rctl2.customer_trx_id=rct2.customer_trx_id
843 and rctl2.line_type = ''TAX''
844 and avt.vat_tax_id = rctl2.vat_tax_id
845 and avt.tax_type = ''VAT''
846 group by rctl2.customer_trx_id
847 ) rctl3
848 WHERE FNAR.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
849 AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID
850 AND RCTT.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
851 AND RCT.ORG_ID = RCTT.ORG_ID
852 AND HCA.CUST_ACCOUNT_ID = RCT.BILL_TO_CUSTOMER_ID
853 AND HP.PARTY_ID = HCA.PARTY_ID
854 AND HCSU.SITE_USE_ID = RCT.BILL_TO_SITE_USE_ID
855 AND ACR.CASH_RECEIPT_ID = FNAR.CASH_RECEIPT_ID
856 AND FNB.BATCH_ID = FNAR.BATCH_ID
857 AND FNAR.BATCH_ID = :BATCH_ID
858 -- Detail mode
859 AND RCT.BILL_TO_CUSTOMER_ID = :CUST_ACCOUNT_ID
860 AND RCT.BILL_TO_SITE_USE_ID = :SITE_USE_ID
861 -- Russian Requirement
862 and rctl3.customer_trx_id(+) = rct.customer_trx_id
863 GROUP BY
864 RCT.TRX_NUMBER,
865 RCTT.NAME,
866 RCT.TRX_DATE,
867 FNAR.TXN_CURR_NET_AMT,
868 RCT.INVOICE_CURRENCY_CODE,
869 FNAR.NETTED_AMT,
870 FNB.BATCH_CURRENCY,
871 ACR.RECEIPT_NUMBER,
872 FNB.batch_id,
873 RCT.CUSTOMER_TRX_ID,
874 FNAR.APPLIED_DISC
875 ORDER BY
876 RCT.TRX_NUMBER');
877
878
879 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'TRANSACTION_SET');
880 DBMS_XMLGEN.setRowTag(l_qryCtx, 'TRANSACTION_RECORD');
881 DBMS_XMLGEN.setBindValue(l_qryCtx,'BATCH_ID', p_batch_id);
882 DBMS_XMLGEN.setBindValue(l_qryCtx,'CUST_ACCOUNT_ID', rec.CUST_ACCOUNT_ID);
883 DBMS_XMLGEN.setBindValue(l_qryCtx,'SITE_USE_ID', rec.SITE_USE_ID);
884 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
885 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
886 l_temp_trx_count := DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx);
887 l_trx_count := l_trx_count + l_temp_trx_count;
888 DBMS_XMLGEN.closeContext(l_qryCtx);
889 clob_to_file(l_result_clob);
890
891 put_endtag('CUSTOMER_RECORD');
892
893 end loop;
894
895 put_endtag('CUSTOMER_SET');
896
897
898 put_starttag('SETUP');
899 put_element('REPORT_NAME',l_report_name);
900 put_element('BATCH_ID',p_batch_id);
901 put_element('BATCH_COUNT',l_batch_count);
902 put_element('INVOICE_COUNT',l_invoice_count);
903 put_element('TRX_COUNT',l_trx_count);
904 put_endtag('SETUP');
905
906
907 put_endtag('NETTING_REPORT');
908
909 EXCEPTION
910
911 WHEN OTHERS then
912 FUN_UTIL.log_conc_unexp(l_current_calling_sequence, SQLERRM);
913 APP_EXCEPTION.RAISE_EXCEPTION;
914
915 END final_netting_report;
916
917
918 END FUN_XML_REPORT_PKG;