[Home] [Help]
PACKAGE BODY: APPS.FUN_REPORT_PVT
Source
1 PACKAGE BODY FUN_REPORT_PVT AS
2 /* $Header: FUNVRPTB.pls 120.20 2011/09/09 08:55:08 srampure ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'FUN_REPORT_PVT';
5
6
7
8
9 -------------------------------------------------------------------------------
10 --Start of Comments
11 --Function:
12 -- Build the outbound transaction query string for intercompany transaction
13 -- summary report based on report parameters
14 --
15 --End of Comments
16 -------------------------------------------------------------------------------
17 PROCEDURE build_summary_outquery(
18 x_return_status OUT NOCOPY VARCHAR2,
19 p_para_rec IN FUN_REPORT_PVT.summaryreport_para_rec_type,
20 x_outbound_query OUT NOCOPY VARCHAR2
21 ) IS
22 l_api_name VARCHAR2(30);
23 l_progress VARCHAR2(3);
24
25 l_person_id HZ_PARTIES.party_id%TYPE;
26 l_grantee_key FND_GRANTS.grantee_key%TYPE;
27 BEGIN
28 l_api_name := 'build_summary_outquery';
29 l_progress := '000';
30
31 FUN_UTIL.log_conc_start(g_pkg_name, l_api_name);
32
33
34 -- Initialize API return status to success
35 x_return_status := FND_API.G_RET_STS_SUCCESS;
36
37
38 -- Get parameters for security settings
39 l_progress := '010';
40 BEGIN
41 SELECT distinct(HZP.party_id)
42 INTO l_person_id
43 FROM HZ_PARTIES HZP,
44 FND_USER U,
45 PER_ALL_PEOPLE_F PAP,
46 (SELECT FND_GLOBAL.user_id() AS user_id FROM DUAL) CURR
47 WHERE CURR.user_id = U.user_id
48 AND U.employee_id = PAP.person_id
49 AND PAP.party_id = HZP.party_id;
50 EXCEPTION
51 WHEN OTHERS THEN
52 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
53 FUN_UTIL.log_conc_unexp(g_pkg_name, l_api_name, l_progress);
54 RAISE;
55 END;
56 FUN_UTIL.log_conc_stmt(
57 g_pkg_name, l_api_name, l_progress, 'l_person_id', l_person_id);
58
59 l_grantee_key := 'HZ_PARTY:' || TO_CHAR(l_person_id);
60 FUN_UTIL.log_conc_stmt(
61 g_pkg_name, l_api_name, l_progress, 'l_grantee_key', l_grantee_key);
62
63
64 -- Build query string for outbound transactions
65 l_progress := '020';
66 x_outbound_query := '
67 SELECT FTB.batch_number batch_number,
68 INIT_P.party_name initiator,
69 XLE.name init_le,
70 GL.name from_ledger_name,
71 FCV.name entered_currency,
72 FTB.exchange_rate_type exchange_rate_type,
73 LKUP.meaning batch_status,
74 FTB.description batch_description,
75 FTB.note batch_note,
76 FTTVL.trx_type_name transaction_type,
77 FTB.gl_date gl_date,
78 FTB.batch_date batch_date,
79 FTB.reject_allow_flag reject_allow_flag,
80 FTB_ORIG.batch_number original_batch_number,
81 FTB_REV.batch_number reversed_batch_number,
82 FTB.initiator_source initiator_source,
83 FTB.attribute1,
84 FTB.attribute2,
85 FTB.attribute3,
86 FTB.attribute4,
87 FTB.attribute5,
88 FTB.attribute6,
89 FTB.attribute7,
90 FTB.attribute8,
91 FTB.attribute9,
92 FTB.attribute10,
93 FTB.attribute11,
94 FTB.attribute12,
95 FTB.attribute13,
96 FTB.attribute14,
97 FTB.attribute15,
98 FTB.attribute_category,
99 FTH.trx_number,
100 RECI_P.party_name recipient,
101 XLE1.name recipient_le,
102 GL1.name to_ledger_name,
103 LKUP1.meaning trx_status,
104 FTH.init_amount_cr initiator_credit,
105 FTH.init_amount_dr initiator_debit,
106 FTH.reci_amount_cr recipient_credit,
107 FTH.reci_amount_dr recipient_debit,
108 FTH.ar_invoice_number ar_invoice_number,
109 FTH.invoice_flag invoice_flag,
110 FTH.approval_date,
111 FTH_ORIG.trx_number orig_trx_number,
112 FTH_REV.trx_number reversed_trx_number,
113 FTH.initiator_instance_flag,
114 FTH.recipient_instance_flag,
115 FTH.reject_reason reject_reason,
116 FTH.description header_description,
117 FTH.attribute1,
118 FTH.attribute2,
119 FTH.attribute3,
120 FTH.attribute4,
121 FTH.attribute5,
122 FTH.attribute6,
123 FTH.attribute7,
124 FTH.attribute8,
125 FTH.attribute9,
126 FTH.attribute10,
127 FTH.attribute11,
128 FTH.attribute12,
129 FTH.attribute13,
130 FTH.attribute14,
131 FTH.attribute15,
132 FTH.attribute_category
133 FROM FUN_TRX_BATCHES FTB,
134 FUN_TRX_HEADERS FTH,
135 XLE_ENTITY_PROFILES XLE,
136 FND_LOOKUP_VALUES LKUP,
137 HZ_PARTIES INIT_P,
138 FUN_TRX_TYPES_VL FTTVL,
139 FND_CURRENCIES_VL FCV,
140 GL_LEDGERS GL,
141 FUN_TRX_BATCHES FTB_ORIG,
142 FUN_TRX_BATCHES FTB_REV,
143 GL_LEDGERS GL1,
144 FUN_TRX_HEADERS FTH_ORIG,
145 FUN_TRX_HEADERS FTH_REV,
146 HZ_PARTIES RECI_P,
147 XLE_ENTITY_PROFILES XLE1,
148 FND_LOOKUP_VALUES LKUP1,
149 FND_GRANTS FG,
150 FND_OBJECT_INSTANCE_SETS FOIS,
151 HZ_RELATIONSHIPS HZR,
152 HZ_ORG_CONTACTS HZC,
153 HZ_ORG_CONTACT_ROLES HZCR
154 WHERE FTH.batch_id(+) = FTB.batch_id
155 AND XLE.legal_entity_id = FTB.from_le_id
156 AND LKUP.lookup_type = ''FUN_BATCH_STATUS''
157 AND LKUP.lookup_code = FTB.status
158 AND LKUP.VIEW_APPLICATION_ID = 435
159 AND LKUP.language=USERENV(''LANG'')
160 AND LKUP.security_group_id=fnd_global.lookup_security_group(LKUP.lookup_type,435)
161 AND FTB.status IN (''NEW'', ''SENT'', ''ERROR'', ''COMPLETE'')
162 AND INIT_P.party_id = FTB.initiator_id
163 AND FTTVL.trx_type_id = FTB.trx_type_id
164 AND RECI_P.party_id(+) = FTH.recipient_id
165 AND XLE1.legal_entity_id(+) = FTH.to_le_id
166 AND FCV.currency_code = FTB.currency_code
167 AND LKUP1.lookup_type(+) = ''FUN_TRX_STATUS''
168 AND LKUP1.lookup_code(+) = FTH.status
169 AND LKUP1.view_application_id = 435
170 AND LKUP1.security_group_id=fnd_global.lookup_security_group(LKUP1.lookup_type,435)
171 AND LKUP1.language = USERENV(''LANG'')
172 AND GL.ledger_id = FTB.from_ledger_id
173 AND FTB_ORIG.batch_id(+) = FTB.original_batch_id
174 AND FTB_REV.batch_id(+) = FTB.reversed_batch_id
175 AND GL1.ledger_id(+) = FTH.to_ledger_id
176 AND FTH_ORIG.trx_id(+) = FTH.original_trx_id
177 AND FTH_REV.trx_id(+) = FTH.reversed_trx_id
178 AND FG.grantee_key = ''' || l_grantee_key
179 || '''
180 AND FG.parameter1 = TO_CHAR(FTB.initiator_id)
181 AND FG.instance_set_id = FOIS.instance_set_id
182 AND FOIS.instance_set_name = ''FUN_TRX_BATCHES_SET''
183 AND HZR.RELATIONSHIP_CODE = ''CONTACT_OF''
184 AND HZR.RELATIONSHIP_TYPE = ''CONTACT''
185 AND HZC.PARTY_RELATIONSHIP_ID = HZR.RELATIONSHIP_ID
186 AND HZCR.ORG_CONTACT_ID = HZC.ORG_CONTACT_ID
187 AND HZCR.ROLE_TYPE = ''INTERCOMPANY_CONTACT_FOR''
188 AND HZR.DIRECTIONAL_FLAG = ''F''
189 AND HZR.SUBJECT_TABLE_NAME = ''HZ_PARTIES''
190 AND HZR.OBJECT_TABLE_NAME = ''HZ_PARTIES''
191 AND HZR.SUBJECT_TYPE = ''PERSON''
192 AND HZR.OBJECT_ID = INIT_P.PARTY_ID
193 AND HZR.STATUS = ''A''
194 AND HZR.SUBJECT_ID= ' || l_person_id;
195
196
197 -- Based on the passed in parameters, build additional query conditions
198 l_progress := '030';
199 IF (p_para_rec.initiator_id IS NOT NULL) THEN
200 x_outbound_query := x_outbound_query ||
201 ' AND FTB.initiator_id = ' || p_para_rec.initiator_id;
202 END IF;
203
204 IF (p_para_rec.recipient_id IS NOT NULL) THEN
205 x_outbound_query := x_outbound_query ||
206 ' AND FTH.recipient_id = ' || p_para_rec.recipient_id;
207 END IF;
208
209 IF (p_para_rec.batch_number_from IS NOT NULL) AND
210 (p_para_rec.batch_number_to IS NOT NULL) THEN
211 x_outbound_query := x_outbound_query ||
212 ' AND FTB.batch_number BETWEEN ''' || p_para_rec.batch_number_from
213 || ''' AND ''' || p_para_rec.batch_number_to || '''';
214 END IF;
215
216 IF (p_para_rec.gl_date_from IS NOT NULL) AND
217 (p_para_rec.gl_date_to IS NOT NULL) THEN
218 x_outbound_query := x_outbound_query ||
219 ' AND FTB.gl_date BETWEEN TO_DATE(''' || p_para_rec.gl_date_from
220 || ''', ''YYYY/MM/DD HH24:MI:SS'') AND '
221 || ' TO_DATE(''' || p_para_rec.gl_date_to
222 || ''', ''YYYY/MM/DD HH24:MI:SS'')';
223 END IF;
224
225 IF (p_para_rec.batch_date_from IS NOT NULL) AND
226 (p_para_rec.batch_date_to IS NOT NULL) THEN
227 x_outbound_query := x_outbound_query ||
228 ' AND FTB.gl_date BETWEEN TO_DATE(''' || p_para_rec.batch_date_from
229 || ''', ''YYYY/MM/DD HH24:MI:SS'') AND '
230 || ' TO_DATE(''' || p_para_rec.batch_date_to
231 || ''', ''YYYY/MM/DD HH24:MI:SS'')';
232 END IF;
233
234 IF (p_para_rec.batch_status IS NOT NULL) THEN
235 x_outbound_query := x_outbound_query ||
236 ' AND FTB.status = ''' || p_para_rec.batch_status || '''';
237 END IF;
238
239 IF (p_para_rec.transaction_status IS NOT NULL) THEN
240 x_outbound_query := x_outbound_query ||
241 ' AND FTH.status = ''' || p_para_rec.transaction_status || '''';
242 END IF;
243
244 IF (p_para_rec.trx_type_id IS NOT NULL) THEN
245 x_outbound_query := x_outbound_query ||
246 ' AND FTB.trx_type_id = ' || p_para_rec.trx_type_id;
247 END IF;
248
249 IF (p_para_rec.currency_code IS NOT NULL) THEN
250 x_outbound_query := x_outbound_query ||
251 ' AND FTB.currency_code = ''' || p_para_rec.currency_code || '''';
252 END IF;
253
254 IF (p_para_rec.invoice_flag IS NOT NULL) THEN
255 x_outbound_query := x_outbound_query ||
256 ' AND FTH.invoice_flag = ''' || p_para_rec.invoice_flag || '''';
257 END IF;
258
259 IF (p_para_rec.ar_invoice_number IS NOT NULL) THEN
260 x_outbound_query := x_outbound_query ||
261 ' AND FTH.ar_invoice_number = ''' || p_para_rec.ar_invoice_number
262 || '''';
263 END IF;
264
265 FUN_UTIL.log_conc_stmt(
266 g_pkg_name, l_api_name, l_progress, 'x_outbound_query', x_outbound_query);
267 FUN_UTIL.log_conc_end(g_pkg_name, l_api_name);
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
272 FUN_UTIL.log_conc_unexp(
273 g_pkg_name, l_api_name, l_progress);
274 RAISE;
275 END build_summary_outquery;
276
277 -------------------------------------------------------------------------------
278 --Start of Comments
279 --Function:
280 -- Build the outbound transaction query string for intercompany account
281 -- details report based on report parameters
282 --
283 --End of Comments
284 -------------------------------------------------------------------------------
285 PROCEDURE build_account_outquery(
286 x_return_status OUT NOCOPY VARCHAR2,
287 p_para_rec IN FUN_REPORT_PVT.accountreport_para_rec_type,
288 x_outbound_query OUT NOCOPY VARCHAR2
289 ) IS
290 l_api_name VARCHAR2(30);
291 l_progress VARCHAR2(3);
292
293 l_acc_segment fnd_id_flex_segments.application_column_name%TYPE;
294
295
296 l_person_id HZ_PARTIES.party_id%TYPE;
297 l_grantee_key FND_GRANTS.grantee_key%TYPE;
298 BEGIN
299 l_api_name := 'build_account_outquery';
300 l_progress := '000';
301
302 FUN_UTIL.log_conc_start(g_pkg_name, l_api_name);
303
304
305 -- Initialize API return status to success
306 x_return_status := FND_API.G_RET_STS_SUCCESS;
307
308
309 -- Get parameters for security settings
310 l_progress := '010';
311 BEGIN
312 SELECT distinct(HZP.party_id)
313 INTO l_person_id
314 FROM HZ_PARTIES HZP,
315 FND_USER U,
316 PER_ALL_PEOPLE_F PAP,
317 (SELECT FND_GLOBAL.user_id() AS user_id FROM DUAL) CURR
318 WHERE CURR.user_id = U.user_id
319 AND U.employee_id = PAP.person_id
320 AND PAP.party_id = HZP.party_id;
321
322 IF (p_para_rec.coa_initiator IS NOT NULL) THEN
323
324 SELECT application_column_name
325 INTO l_acc_segment
326 FROM fnd_segment_attribute_values
327 WHERE application_id = 101
328 AND id_flex_code = 'GL#'
329 AND id_flex_num = p_para_rec.coa_initiator
330 AND segment_attribute_type = 'GL_ACCOUNT'
331 AND attribute_value = 'Y';
332 end if;
333
334
335 EXCEPTION
336 WHEN OTHERS THEN
337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
338 FUN_UTIL.log_conc_unexp(g_pkg_name, l_api_name, l_progress);
339 RAISE;
340 END;
341 FUN_UTIL.log_conc_stmt(
342 g_pkg_name, l_api_name, l_progress, 'l_person_id', l_person_id);
343
344 l_grantee_key := 'HZ_PARTY:' || TO_CHAR(l_person_id);
345 FUN_UTIL.log_conc_stmt(
346 g_pkg_name, l_api_name, l_progress, 'l_grantee_key', l_grantee_key);
347
348
349 -- Build query string for outbound transactions
350 l_progress := '020';
351 x_outbound_query :='
352 SELECT fun_trx_entry_util.get_concatenated_account(fdl.ccid) account,
353 ''OUTBOUND'' batch_type,
354 lkup.meaning account_type,
355 FTB.batch_number batch_number,
356 FTH.trx_number trx_number,
357 lkup1.meaning batch_status,
358 FTB.batch_date batch_date,
359 INIT_P.party_name initiator,
360 RECI_P.party_name recipient,
361 lkup2.meaning trx_status,
362 FTTVL.trx_type_name transaction_type,
363 FTB.gl_date gl_date,
364 GLP.period_name gl_period,
365 FCV.name entered_currency,
366 FDL.amount_cr entered_credit,
367 fdl.amount_dr entered_debit,
368 fdl.description description,
369 FTH.invoice_flag invoice_flag,
370 NVL2(FTH.original_trx_id,NVL2(FTH.reversed_trx_id,0,FTH_REV.trx_number),FTH_ORIG.trx_number) reverse_reference,
371 DECODE(NVL(FTH.original_trx_id,0),0,''N'',''Y'') reverse_trx_flag,
372 DECODE(NVL(FTH.reversed_trx_id,0),0,''N'',''Y'') reversed_flag,
373 FTH.ar_invoice_number ar_invoice_number,
374 FTH_ORIG.trx_number orig_trx_number,
375 FTH_REV.trx_number rev_trx_number,
376 FTB_ORIG.batch_number orig_batch_number,
377 FTB_REV.batch_number rev_batch_number,
378 FTB.reject_allow_flag reject_allow_flag,
379 FTB.initiator_source initiator_source,
380 FTB.description batch_description,
381 FTB.attribute1,
382 FTB.attribute2,
383 FTB.attribute3,
384 FTB.attribute4,
385 FTB.attribute5,
386 FTB.attribute6,
387 FTB.attribute7,
388 FTB.attribute8,
389 FTB.attribute9,
390 FTB.attribute10,
391 FTB.attribute11,
392 FTB.attribute12,
393 FTB.attribute13,
394 FTB.attribute14,
395 FTB.attribute15,
396 FTB.attribute_category,
397 fdl.dist_number distribution_number,
398 fdl.party_type_flag party_type_flag,
399 fdl.dist_type_flag dist_type_flag,
400 fdl.auto_generate_flag auto_generate_flag,
401 fdl.attribute1,
402 fdl.attribute2,
403 fdl.attribute3,
404 fdl.attribute4,
405 fdl.attribute5,
406 fdl.attribute6,
407 fdl.attribute7,
408 fdl.attribute8,
409 fdl.attribute9,
410 fdl.attribute10,
411 fdl.attribute11,
412 fdl.attribute12,
413 fdl.attribute13,
414 fdl.attribute14,
415 fdl.attribute15,
416 fdl.attribute_category,
417 XLE.name init_le,
418 XLE1.name recipient_le,
419 GL0.name from_ledger_name,
420 GL1.name to_ledger_name,
421 FTH.init_amount_cr initiator_credit,
422 FTH.init_amount_dr initiator_debit,
423 FTH.reci_amount_cr recipient_credit,
424 FTH.reci_amount_dr recipient_debit,
425 FTH.approval_date approval_date,
426 FTH.initiator_instance_flag initiator_instance_flag,
427 FTH.recipient_instance_flag recipient_instance_flag,
428 FTH.reject_reason reject_reason,
429 FTH.description header_description,
430 FTH.attribute1,
431 FTH.attribute2,
432 FTH.attribute3,
433 FTH.attribute4,
434 FTH.attribute5,
435 FTH.attribute6,
436 FTH.attribute7,
437 FTH.attribute8,
438 FTH.attribute9,
439 FTH.attribute10,
440 FTH.attribute11,
441 FTH.attribute12,
442 FTH.attribute13,
443 FTH.attribute14,
444 FTH.attribute15,
445 FTH.attribute_category
446 FROM fun_dist_lines fdl,
447 gl_code_combinations glcc,
448 fnd_lookups lkup,
449 fnd_lookup_values lkup1,
450 fnd_lookup_values lkup2,
451 FUN_TRX_BATCHES FTB,
452 FUN_TRX_BATCHES FTB_REV,
453 FUN_TRX_BATCHES FTB_ORIG,
454 HZ_PARTIES INIT_P,
455 HZ_PARTIES RECI_P,
456 FUN_TRX_HEADERS FTH,
457 FUN_TRX_HEADERS FTH_REV,
458 FUN_TRX_HEADERS FTH_ORIG,
459 FUN_TRX_TYPES_VL FTTVL,
460 GL_PERIODS GLP,
461 FND_CURRENCIES_VL FCV,
462 FUN_TRX_LINES FTL,
463 GL_LEDGERS GL0,
464 GL_LEDGERS GL1,
465 XLE_ENTITY_PROFILES XLE,
466 XLE_ENTITY_PROFILES XLE1,
467 FND_GRANTS FG,
468 FND_OBJECT_INSTANCE_SETS FOIS,
469 HZ_RELATIONSHIPS HZR,
470 HZ_ORG_CONTACTS HZC,
471 HZ_ORG_CONTACT_ROLES HZCR
472 WHERE fdl.ccid = glcc.code_combination_id
473 AND glcc.account_type = lkup.lookup_code
474 AND lkup.lookup_type =''ACCOUNT_TYPE''
475 AND lkup.lookup_code IN (''A'',''E'',''L'',''R'', ''O'')
476 AND lkup1.lookup_type =''FUN_BATCH_STATUS''
477 AND lkup1.view_application_id = 435
478 AND lkup1.security_group_id = fnd_global.lookup_security_group(lkup1.lookup_type,435)
479 AND lkup1.language = USERENV(''LANG'')
480 AND lkup1.lookup_code = FTB.status
481 AND lkup1.lookup_code IN (''NEW'',''SENT'',''ERROR'',''COMPLETE'')
482 AND INIT_P.party_id = FTB.initiator_id
483 AND RECI_P.party_id(+) = FTH.recipient_id
484 AND lkup2.lookup_code = FTH.status
485 AND lkup2.view_application_id = 435
486 AND lkup2.security_group_id = fnd_global.lookup_security_group(lkup2.lookup_type,435)
487 AND lkup2.language = USERENV(''LANG'')
488 AND LKUP2.lookup_type = ''FUN_TRX_STATUS''
489 AND FTH.batch_id(+) = FTB.batch_id
490 AND FTTVL.trx_type_id = FTB.trx_type_id
491 AND FTB.gl_date BETWEEN GLP.start_date AND GLP.end_date
492 AND FCV.currency_code = FTB.currency_code
493 AND FTL.trx_id = FTH.trx_id
494 AND FDL.line_id = FTL.line_id
495 AND FDL.party_type_flag = ''I''
496 AND FTH.original_trx_id=FTH_ORIG.trx_id(+)
497 AND FTB.original_batch_id=FTB_ORIG.batch_id(+)
498 AND FTH.reversed_trx_id=FTH_REV.trx_id(+)
499 AND FTB.reversed_batch_id=FTB_REV.batch_id(+)
500 AND XLE.legal_entity_id = FTB.from_le_id
501 AND XLE1.legal_entity_id(+) = FTH.to_le_id
502 AND GL0.ledger_id = FTB.from_ledger_id
503 AND GL1.ledger_id(+) = FTH.to_ledger_id
504 AND GLP.period_set_name =GL0.period_set_name
505 AND GLP.period_type = GL0.accounted_period_type
506 AND fdl.dist_type_flag IN (''R'',''L'')
507 AND FG.grantee_key = ''' || l_grantee_key
508 || '''
509 AND FG.parameter1 = TO_CHAR(FTB.initiator_id)
510 AND FG.instance_set_id = FOIS.instance_set_id
511 AND NVL(FG.end_date, SYSDATE+1) > SYSDATE
512 AND FOIS.instance_set_name = ''FUN_TRX_BATCHES_SET''
513 AND HZR.RELATIONSHIP_CODE = ''CONTACT_OF''
514 AND HZR.RELATIONSHIP_TYPE = ''CONTACT''
515 AND HZC.PARTY_RELATIONSHIP_ID = HZR.RELATIONSHIP_ID
516 AND HZCR.ORG_CONTACT_ID = HZC.ORG_CONTACT_ID
517 AND HZCR.ROLE_TYPE = ''INTERCOMPANY_CONTACT_FOR''
518 AND HZR.DIRECTIONAL_FLAG = ''F''
519 AND HZR.SUBJECT_TABLE_NAME = ''HZ_PARTIES''
520 AND HZR.OBJECT_TABLE_NAME = ''HZ_PARTIES''
521 AND HZR.SUBJECT_TYPE = ''PERSON''
522 AND HZR.direction_code=''P''
523 AND HZR.OBJECT_ID = INIT_P.PARTY_ID
524 AND HZR.STATUS = ''A''
525 AND HZR.SUBJECT_ID='|| l_person_id;
526
527
528
529
530
531 -- Based on the passed in parameters, build additional query conditions
532 l_progress := '030';
533
534
535 IF ((p_para_rec.rec_account_from IS NOT NULL) AND (p_para_rec.rec_account_to
536 IS NOT NULL) AND (p_para_rec.init_dist_acc_from IS NOT NULL) AND
537 (p_para_rec.init_dist_acc_to IS NOT NULL)) THEN
538
539 x_outbound_query:=x_outbound_query ||' AND GLCC.'||l_acc_segment||
540 ' BETWEEN decode(fdl.dist_type_flag,''R'','||p_para_rec.rec_account_from||',''L'','||
541 p_para_rec.init_dist_acc_from||') '||
542 ' AND decode(fdl.dist_type_flag,''R'','||p_para_rec.rec_account_to||',''L'','||
543 p_para_rec.init_dist_acc_to||') ';
544
545 ELSIF ((p_para_rec.rec_account_from IS NOT NULL) AND
546 (p_para_rec.rec_account_to IS NOT NULL)) THEN
547
548 x_outbound_query:=x_outbound_query ||' AND GLCC.'||l_acc_segment||
549 ' BETWEEN decode(fdl.dist_type_flag,''R'','||p_para_rec.rec_account_from||',GLCC.'||l_acc_segment||') '||
550 ' AND decode(fdl.dist_type_flag,''R'','||p_para_rec.rec_account_to||',GLCC.'||l_acc_segment||') ';
551
552
553 ELSIF ((p_para_rec.init_dist_acc_from IS NOT NULL) AND
554 (p_para_rec.init_dist_acc_to IS NOT NULL)) THEN
555
556 x_outbound_query:=x_outbound_query ||' AND GLCC.'||l_acc_segment||
557 ' BETWEEN decode(fdl.dist_type_flag,''L'','||p_para_rec.init_dist_acc_from||',GLCC.'||l_acc_segment||') '||
558 ' AND decode(fdl.dist_type_flag,''L'','||p_para_rec.init_dist_acc_to||',GLCC.'||l_acc_segment||') ';
559
560
561 END IF;
562
563
564
565 -- Based on the passed in parameters, build additional query conditions
566 l_progress := '030';
567
568
569 IF ((p_para_rec.initiator_from IS NOT NULL) AND (p_para_rec.initiator_to
570 IS
571 NOT NULL)) THEN
572 x_outbound_query := x_outbound_query ||
573 ' AND INIT_P.PARTY_NAME BETWEEN ''' || p_para_rec.initiator_from
574 ||''' AND'''||
575 p_para_rec.initiator_to||'''';
576 END IF;
577
578
579
580 IF ((p_para_rec.recipient_from IS NOT NULL) AND (p_para_rec.recipient_to IS
581 NOT NULL)) THEN
582 x_outbound_query := x_outbound_query ||
583 ' AND RECI_P.PARTY_NAME BETWEEN ''' || p_para_rec.recipient_from
584 ||''' AND'''||
585 p_para_rec.recipient_to||'''';
586 END IF;
587 IF (p_para_rec.transact_le IS NOT NULL) THEN
588 x_outbound_query:= x_outbound_query||
589 ' AND XLE.NAME = '''|| p_para_rec.transact_le||'''';
590 END IF;
591
592 IF (p_para_rec.trading_le IS NOT NULL) THEN
593 x_outbound_query:= x_outbound_query ||
594 ' AND XLE1.NAME ='''||p_para_rec.trading_le||'''';
595 END IF;
596 IF (p_para_rec.transact_ledger IS NOT NULL) THEN
597 x_outbound_query:=x_outbound_query ||
598 ' AND GL0.ledger_id ='||p_para_rec.transact_ledger;
599 end if;
600
601 IF (p_para_rec.trading_ledger IS NOT NULL) THEN
602 x_outbound_query:= x_outbound_query ||
603 ' AND GL1.ledger_id(+)='||p_para_rec.trading_ledger;
604 END IF;
605
606
607 IF (p_para_rec.batch_number_from IS NOT NULL) AND
608 (p_para_rec.batch_number_to IS NOT NULL) THEN
609 x_outbound_query := x_outbound_query ||
610 ' AND FTB.batch_number BETWEEN ''' || p_para_rec.batch_number_from
611 || ''' AND ''' || p_para_rec.batch_number_to || '''';
612 END IF;
613
614 IF (p_para_rec.gl_date_from IS NOT NULL) AND
615 (p_para_rec.gl_date_to IS NOT NULL) THEN
616 x_outbound_query := x_outbound_query ||
617 ' AND FTB.gl_date BETWEEN TO_DATE(''' || p_para_rec.gl_date_from
618 || ''', ''YYYY/MM/DD HH24:MI:SS'') AND '
619 || ' TO_DATE(''' || p_para_rec.gl_date_to
620 || ''', ''YYYY/MM/DD HH24:MI:SS'')';
621 END IF;
622 IF (p_para_rec.batch_status IS NOT NULL) THEN
623 x_outbound_query := x_outbound_query ||
624 ' AND FTB.status = ''' || p_para_rec.batch_status || '''';
625 END IF;
626
627 IF (p_para_rec.transaction_status IS NOT NULL) THEN
628 x_outbound_query := x_outbound_query ||
629 ' AND FTH.status = ''' || p_para_rec.transaction_status || '''';
630 END IF;
631
632 IF (p_para_rec.trx_type_id IS NOT NULL) THEN
633 x_outbound_query := x_outbound_query ||
634 ' AND FTB.trx_type_id = ' || p_para_rec.trx_type_id;
635 END IF;
636
637 IF (p_para_rec.currency_code IS NOT NULL) THEN
638 x_outbound_query := x_outbound_query ||
639 ' AND FTB.currency_code = ''' || p_para_rec.currency_code || '''';
640 END IF;
641 IF (p_para_rec.account_type IS NOT NULL) THEN
642 x_outbound_query:= x_outbound_query ||
643 ' AND lkup.lookup_code ='''||p_para_rec.account_type||'''';
644
645 END IF;
646
647
648
649 IF (p_para_rec.ar_invoice_number IS NOT NULL) THEN
650 x_outbound_query := x_outbound_query ||
651 ' AND FTH.ar_invoice_number = ''' || p_para_rec.ar_invoice_number
652 || '''';
653 END IF;
654
655 FUN_UTIL.log_conc_stmt(
656 g_pkg_name, l_api_name, l_progress, 'x_outbound_query', x_outbound_query);
657 FUN_UTIL.log_conc_end(g_pkg_name, l_api_name);
658
659 EXCEPTION
660 WHEN OTHERS THEN
661 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
662 FUN_UTIL.log_conc_unexp(
663 g_pkg_name, l_api_name, l_progress);
664 RAISE;
665 END build_account_outquery;
666
667
668
669
670
671
672 -------------------------------------------------------------------------------
673 --Start of Comments
674 --Function:
675 -- Build the inbound transaction query string for intercompany transaction
676 -- summary report based on report parameters
677 --
678 --End of Comments
679 -------------------------------------------------------------------------------
680 PROCEDURE build_summary_inquery(
681 x_return_status OUT NOCOPY VARCHAR2,
682 p_para_rec IN FUN_REPORT_PVT.summaryreport_para_rec_type,
683 x_inbound_query OUT NOCOPY VARCHAR2
684 ) IS
685 l_api_name VARCHAR2(30);
686 l_progress VARCHAR2(3);
687
688 l_person_id HZ_PARTIES.party_id%TYPE;
689 l_grantee_key FND_GRANTS.grantee_key%TYPE;
690 BEGIN
691 l_api_name := 'build_summary_inquery';
692 l_progress := '000';
693
694 FUN_UTIL.log_conc_start(g_pkg_name, l_api_name);
695
696
697 -- Initialize API return status to success
698 x_return_status := FND_API.G_RET_STS_SUCCESS;
699
700
701 -- Get parameters for security settings
702 l_progress := '010';
703 BEGIN
704 SELECT distinct(HZP.party_id)
705 INTO l_person_id
706 FROM HZ_PARTIES HZP,
707 FND_USER U,
708 PER_ALL_PEOPLE_F PAP,
709 (SELECT FND_GLOBAL.user_id() AS user_id FROM DUAL) CURR
710 WHERE CURR.user_id = U.user_id
711 AND U.employee_id = PAP.person_id
712 AND PAP.party_id = HZP.party_id;
713 EXCEPTION
714 WHEN OTHERS THEN
715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
716 FUN_UTIL.log_conc_unexp(g_pkg_name, l_api_name, l_progress);
717 RAISE;
718 END;
719 FUN_UTIL.log_conc_stmt(
720 g_pkg_name, l_api_name, l_progress, 'l_person_id', l_person_id);
721
722 l_grantee_key := 'HZ_PARTY:' || TO_CHAR(l_person_id);
723 FUN_UTIL.log_conc_stmt(
724 g_pkg_name, l_api_name, l_progress, 'l_grantee_key', l_grantee_key);
725
726
727 -- Build query string for inbound transactions
728 l_progress := '020';
729 x_inbound_query := '
730 SELECT FTB.batch_number batch_number,
731 INIT_P.party_name initiator,
732 XLE.name init_le,
733 GL.name from_ledger_name,
734 FCV.name entered_currency,
735 FTB.exchange_rate_type exchange_rate_type,
736 LKUP.meaning batch_status,
737 FTB.description batch_description,
738 FTB.note batch_note,
739 FTTVL.trx_type_name transaction_type,
740 FTB.gl_date gl_date,
741 FTB.batch_date batch_date,
742 FTB.reject_allow_flag reject_allow_flag,
743 FTB_ORIG.batch_number original_batch_number,
744 FTB_REV.batch_number reversed_batch_number,
745 FTB.initiator_source initiator_source,
746 FTB.attribute1,
747 FTB.attribute2,
748 FTB.attribute3,
749 FTB.attribute4,
750 FTB.attribute5,
751 FTB.attribute6,
752 FTB.attribute7,
753 FTB.attribute8,
754 FTB.attribute9,
755 FTB.attribute10,
756 FTB.attribute11,
757 FTB.attribute12,
758 FTB.attribute13,
759 FTB.attribute14,
760 FTB.attribute15,
761 FTB.attribute_category,
762 FTH.trx_number,
763 RECI_P.party_name recipient,
764 XLE1.name recipient_le,
765 GL1.name to_ledger_name,
766 LKUP1.meaning trx_status,
767 FTH.init_amount_cr initiator_credit,
768 FTH.init_amount_dr initiator_debit,
769 FTH.reci_amount_cr recipient_credit,
770 FTH.reci_amount_dr recipient_debit,
771 FTH.ar_invoice_number ar_invoice_number,
772 FTH.invoice_flag invoice_flag,
773 FTH.approval_date,
774 FTH_ORIG.trx_number orig_trx_number,
775 FTH_REV.trx_number reversed_trx_number,
776 FTH.initiator_instance_flag,
777 FTH.recipient_instance_flag,
778 FTH.reject_reason reject_reason,
779 FTH.description header_description,
780 FTH.attribute1,
781 FTH.attribute2,
782 FTH.attribute3,
783 FTH.attribute4,
784 FTH.attribute5,
785 FTH.attribute6,
786 FTH.attribute7,
787 FTH.attribute8,
788 FTH.attribute9,
789 FTH.attribute10,
790 FTH.attribute11,
791 FTH.attribute12,
792 FTH.attribute13,
793 FTH.attribute14,
794 FTH.attribute15,
795 FTH.attribute_category
796 FROM FUN_TRX_BATCHES FTB,
797 FUN_TRX_HEADERS FTH,
798 XLE_ENTITY_PROFILES XLE,
799 FND_LOOKUP_VALUES LKUP,
800 HZ_PARTIES INIT_P,
801 FUN_TRX_TYPES_VL FTTVL,
802 FND_CURRENCIES_VL FCV,
803 GL_LEDGERS GL,
804 FUN_TRX_BATCHES FTB_ORIG,
805 FUN_TRX_BATCHES FTB_REV,
806 GL_LEDGERS GL1,
807 FUN_TRX_HEADERS FTH_ORIG,
808 FUN_TRX_HEADERS FTH_REV,
809 HZ_PARTIES RECI_P,
810 XLE_ENTITY_PROFILES XLE1,
811 FND_LOOKUP_VALUES LKUP1,
812 FND_GRANTS FG,
813 FND_OBJECT_INSTANCE_SETS FOIS
814 WHERE FTH.batch_id(+) = FTB.batch_id
815 AND XLE.legal_entity_id = FTB.from_le_id
816 AND LKUP.lookup_type = ''FUN_BATCH_STATUS''
817 AND LKUP.lookup_code = FTB.status
818 AND LKUP.view_application_id = 435
819 AND LKUP.security_group_id=fnd_global.lookup_security_group(lkup.lookup_type,435)
820 AND LKUP.language = USERENV(''LANG'')
821 AND FTH.status IN (''RECEIVED'', ''APPROVED'', ''REJECTED'',
822 ''COMPLETE'', ''XFER_RECI_GL'', ''XFER_AR'', ''XFER_INI_GL'')
823 AND INIT_P.party_id = FTB.initiator_id
824 AND FTTVL.trx_type_id = FTB.trx_type_id
825 AND RECI_P.party_id(+) = FTH.recipient_id
826 AND XLE1.legal_entity_id(+) = FTH.to_le_id
827 AND FCV.currency_code = FTB.currency_code
828 AND LKUP1.lookup_type(+) = ''FUN_TRX_STATUS''
829 AND LKUP1.view_application_id = 435
830 AND LKUP1.security_group_id=fnd_global.lookup_security_group(lkup1.lookup_type,435)
831 AND LKUP1.language = USERENV(''LANG'')
832 AND LKUP1.lookup_code(+) = FTH.status
833 AND GL.ledger_id = FTB.from_ledger_id
834 AND FTB_ORIG.batch_id(+) = FTB.original_batch_id
835 AND FTB_REV.batch_id(+) = FTB.reversed_batch_id
836 AND GL1.ledger_id(+) = FTH.to_ledger_id
837 AND FTH_ORIG.trx_id(+) = FTH.original_trx_id
838 AND FTH_REV.trx_id(+) = FTH.reversed_trx_id
839 AND FG.parameter1 = TO_CHAR(FTH.recipient_id)
840 AND FG.instance_set_id = FOIS.instance_set_id
841 AND FOIS.instance_set_name = ''FUN_TRX_HEADERS_SET''
842 AND NVL(FG.end_date, SYSDATE+1) > SYSDATE
843 AND FG.grantee_key = ''' || l_grantee_key || '''';
844
845
846 -- Based on the passed in parameters, build additional query conditions
847 l_progress := '030';
848 IF (p_para_rec.initiator_id IS NOT NULL) THEN
849 x_inbound_query := x_inbound_query ||
850 ' AND FTB.initiator_id = ' || p_para_rec.initiator_id;
851 END IF;
852
853 IF (p_para_rec.recipient_id IS NOT NULL) THEN
854 x_inbound_query := x_inbound_query ||
855 ' AND FTH.recipient_id = ' || p_para_rec.recipient_id;
856 END IF;
857
858 IF (p_para_rec.batch_number_from IS NOT NULL) AND
859 (p_para_rec.batch_number_to IS NOT NULL) THEN
860 x_inbound_query := x_inbound_query ||
861 ' AND FTB.batch_number BETWEEN ''' || p_para_rec.batch_number_from
862 || ''' AND ''' || p_para_rec.batch_number_to || '''';
863 END IF;
864
865 IF (p_para_rec.gl_date_from IS NOT NULL) AND
866 (p_para_rec.gl_date_to IS NOT NULL) THEN
867 x_inbound_query := x_inbound_query ||
868 ' AND FTB.gl_date BETWEEN TO_DATE(''' || p_para_rec.gl_date_from
869 || ''', ''YYYY/MM/DD HH24:MI:SS'') AND '
870 || ' TO_DATE(''' || p_para_rec.gl_date_to
871 || ''', ''YYYY/MM/DD HH24:MI:SS'')';
872 END IF;
873
874 IF (p_para_rec.batch_date_from IS NOT NULL) AND
875 (p_para_rec.batch_date_to IS NOT NULL) THEN
876 x_inbound_query := x_inbound_query ||
877 ' AND FTB.gl_date BETWEEN TO_DATE(''' || p_para_rec.batch_date_from
878 || ''', ''YYYY/MM/DD HH24:MI:SS'') AND '
879 || ' TO_DATE(''' || p_para_rec.batch_date_to
880 || ''', ''YYYY/MM/DD HH24:MI:SS'')';
881 END IF;
882
883
884
885 IF (p_para_rec.batch_status IS NOT NULL) THEN
886 x_inbound_query := x_inbound_query ||
887 ' AND FTB.status = ''' || p_para_rec.batch_status || '''';
888 END IF;
889 IF (p_para_rec.transaction_status IS NOT NULL) THEN
890 x_inbound_query := x_inbound_query ||
891 ' AND FTH.status = ''' || p_para_rec.transaction_status || '''';
892 END IF;
893 IF (p_para_rec.trx_type_id IS NOT NULL) THEN
894 x_inbound_query := x_inbound_query ||
895 ' AND FTB.trx_type_id = ' || p_para_rec.trx_type_id;
896 END IF;
897 IF (p_para_rec.currency_code IS NOT NULL) THEN
898 x_inbound_query := x_inbound_query ||
899 ' AND FTB.currency_code = ''' || p_para_rec.currency_code || '''';
900 END IF;
901 IF (p_para_rec.invoice_flag IS NOT NULL) THEN
902 x_inbound_query := x_inbound_query ||
903 ' AND FTH.invoice_flag = ''' || p_para_rec.invoice_flag || '''';
904 END IF;
905 IF (p_para_rec.ar_invoice_number IS NOT NULL) THEN
906 x_inbound_query := x_inbound_query ||
907 ' AND FTH.ar_invoice_number = ''' || p_para_rec.ar_invoice_number
908 || '''';
909 END IF;
910
911 FUN_UTIL.log_conc_stmt(
912 g_pkg_name, l_api_name, l_progress, 'x_inbound_query', x_inbound_query);
913 FUN_UTIL.log_conc_end(g_pkg_name, l_api_name);
914
915 EXCEPTION
916 WHEN OTHERS THEN
917 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
918 FUN_UTIL.log_conc_unexp(
919 g_pkg_name, l_api_name, l_progress);
920 RAISE;
921 END build_summary_inquery;
922
923
924
925 -------------------------------------------------------------------------------
926 --Start of Comments
927 --Function:
928 -- Build the inbound account query string for intercompany account
929 -- details report based on report parameters
930 --
931 --End of Comments
932 -------------------------------------------------------------------------------
933 PROCEDURE build_account_inquery(
934 x_return_status OUT NOCOPY VARCHAR2,
935 p_para_rec IN FUN_REPORT_PVT.accountreport_para_rec_type,
936 x_inbound_query OUT NOCOPY VARCHAR2
937 ) IS
938 l_api_name VARCHAR2(30);
939 l_progress VARCHAR2(30);
940
941 l_acc_segment fnd_id_flex_segments.application_column_name%TYPE;
942
943 l_person_id HZ_PARTIES.party_id%TYPE;
944 l_grantee_key FND_GRANTS.grantee_key%TYPE;
945 BEGIN
946 l_api_name := 'build_account_inquery';
947 l_progress := '000';
948
949 FUN_UTIL.log_conc_start(g_pkg_name, l_api_name);
950
951
952 -- Initialize API return status to success
953 x_return_status := FND_API.G_RET_STS_SUCCESS;
954
955
956 -- Get parameters for security settings
957 l_progress := '010';
958 BEGIN
959 SELECT distinct(HZP.party_id)
960 INTO l_person_id
961 FROM HZ_PARTIES HZP,
962 FND_USER U,
963 PER_ALL_PEOPLE_F PAP,
964 (SELECT FND_GLOBAL.user_id() AS user_id FROM DUAL) CURR
965 WHERE CURR.user_id = U.user_id
966 AND U.employee_id = PAP.person_id
967 AND PAP.party_id = HZP.party_id;
968
969 IF (p_para_rec.coa_recipient IS NOT NULL) THEN
970
971 SELECT application_column_name
972 INTO l_acc_segment
973 FROM fnd_segment_attribute_values
974 WHERE application_id = 101
975 AND id_flex_code = 'GL#'
976 AND id_flex_num = p_para_rec.coa_recipient
977 AND segment_attribute_type = 'GL_ACCOUNT'
978 AND attribute_value = 'Y';
979 end if;
980
981 EXCEPTION
982 WHEN OTHERS THEN
983 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
984 FUN_UTIL.log_conc_unexp(g_pkg_name, l_api_name, l_progress);
985 RAISE;
986 END;
987 FUN_UTIL.log_conc_stmt(
988 g_pkg_name, l_api_name, l_progress, 'l_person_id', l_person_id);
989
990 l_grantee_key := 'HZ_PARTY:' || TO_CHAR(l_person_id);
991 FUN_UTIL.log_conc_stmt(
992 g_pkg_name, l_api_name, l_progress, 'l_grantee_key', l_grantee_key);
993
994
995 -- Build query string for inbound transactions
996 l_progress := '020';
997 x_inbound_query := '
998 SELECT fun_trx_entry_util.get_concatenated_account(fdl.ccid) account,
999 ''INBOUND'' batch_type,
1000 lkup.meaning account_type,
1001 FTB.batch_number batch_number,
1002 FTH.trx_number trx_number,
1003 lkup1.meaning batch_status,
1004 FTB.batch_date batch_date,
1005 INIT_P.party_name initiator,
1006 RECI_P.party_name recipient,
1007 lkup2.meaning trx_status,
1008 FTTVL.trx_type_name transaction_type,
1009 FTB.gl_date gl_date,
1010 GLP.period_name gl_period,
1011 FCV.name entered_currency,
1012 FDL.amount_cr entered_credit,
1013 fdl.amount_dr entered_debit,
1014 fdl.description description,
1015 FTH.invoice_flag invoice_flag,
1016 NVL2(FTH.original_trx_id,NVL2(FTH.reversed_trx_id,0,FTH_REV.trx_number),FTH_ORIG.trx_number) reverse_reference,
1017 DECODE(NVL(FTH.original_trx_id,0),0,''N'',''Y'') reverse_trx_flag,
1018 DECODE(NVL(FTH.reversed_trx_id,0),0,''N'',''Y'') reversed_flag,
1019 FTH.ar_invoice_number ar_invoice_number,
1020 FTH_ORIG.trx_number orig_trx_number,
1021 FTH_REV.trx_number rev_trx_number,
1022 FTB_ORIG.batch_number orig_batch_number,
1023 FTB_REV.batch_number rev_batch_number,
1024 FTB.reject_allow_flag reject_allow_flag,
1025 FTB.initiator_source initiator_source,
1026 FTB.description batch_description,
1027 FTB.attribute1,
1028 FTB.attribute2,
1029 FTB.attribute3,
1030 FTB.attribute4,
1031 FTB.attribute5,
1032 FTB.attribute6,
1033 FTB.attribute7,
1034 FTB.attribute8,
1035 FTB.attribute9,
1036 FTB.attribute10,
1037 FTB.attribute11,
1038 FTB.attribute12,
1039 FTB.attribute13,
1040 FTB.attribute14,
1041 FTB.attribute15,
1042 FTB.attribute_category,
1043 fdl.dist_number distribution_number,
1044 fdl.party_type_flag party_type_flag,
1045 fdl.dist_type_flag dist_type_flag,
1046 fdl.auto_generate_flag auto_generate_flag,
1047 fdl.attribute1,
1048 fdl.attribute2,
1049 fdl.attribute3,
1050 fdl.attribute4,
1051 fdl.attribute5,
1052 fdl.attribute6,
1053 fdl.attribute7,
1054 fdl.attribute8,
1055 fdl.attribute9,
1056 fdl.attribute10,
1057 fdl.attribute11,
1058 fdl.attribute12,
1059 fdl.attribute13,
1060 fdl.attribute14,
1061 fdl.attribute15,
1062 fdl.attribute_category,
1063 XLE.name init_le,
1064 XLE1.name recipient_le,
1065 GL0.name from_ledger_name,
1066 GL1.name to_ledger_name,
1067 FTH.init_amount_cr initiator_credit,
1068 FTH.init_amount_dr initiator_debit,
1069 FTH.reci_amount_cr recipient_credit,
1070 FTH.reci_amount_dr recipient_debit,
1071 FTH.approval_date approval_date,
1072 FTH.initiator_instance_flag initiator_instance_flag,
1073 FTH.recipient_instance_flag recipient_instance_flag,
1074 FTH.reject_reason reject_reason,
1075 FTH.description header_description,
1076 FTH.attribute1,
1077 FTH.attribute2,
1078 FTH.attribute3,
1079 FTH.attribute4,
1080 FTH.attribute5,
1081 FTH.attribute6,
1082 FTH.attribute7,
1083 FTH.attribute8,
1084 FTH.attribute9,
1085 FTH.attribute10,
1086 FTH.attribute11,
1087 FTH.attribute12,
1088 FTH.attribute13,
1089 FTH.attribute14,
1090 FTH.attribute15,
1091 FTH.attribute_category
1092 FROM fun_dist_lines fdl,
1093 gl_code_combinations glcc,
1094 fnd_lookups lkup,
1095 fnd_lookup_values lkup1,
1096 fnd_lookup_values lkup2,
1097 FUN_TRX_BATCHES FTB,
1098 FUN_TRX_BATCHES FTB_REV,
1099 FUN_TRX_BATCHES FTB_ORIG,
1100 HZ_PARTIES INIT_P,
1101 HZ_PARTIES RECI_P,
1102 FUN_TRX_HEADERS FTH,
1103 FUN_TRX_HEADERS FTH_REV,
1104 FUN_TRX_HEADERS FTH_ORIG,
1105 FUN_TRX_TYPES_VL FTTVL,
1106 GL_PERIODS GLP,
1107 FND_CURRENCIES_VL FCV,
1108 FUN_TRX_LINES FTL,
1109 GL_LEDGERS GL0,
1110 GL_LEDGERS GL1,
1111 XLE_ENTITY_PROFILES XLE,
1112 XLE_ENTITY_PROFILES XLE1,
1113 FND_GRANTS FG,
1114 FND_OBJECT_INSTANCE_SETS FOIS
1115 WHERE fdl.ccid = glcc.code_combination_id
1116 AND glcc.account_type = lkup.lookup_code
1117 AND lkup.lookup_type =''ACCOUNT_TYPE''
1118 AND lkup.lookup_code IN (''A'',''E'',''L'',''R'', ''O'')
1119 AND lkup1.lookup_type =''FUN_BATCH_STATUS''
1120 AND lkup1.lookup_code = FTB.status
1121 AND lkup1.view_application_id = 435
1122 AND lkup1.security_group_id =fnd_global.lookup_security_group(lkup1.lookup_type,435)
1123 AND lkup1.language = USERENV(''LANG'')
1124 AND INIT_P.party_id = FTB.initiator_id
1125 AND RECI_P.party_id(+) = FTH.recipient_id
1126 AND lkup2.lookup_code = FTH.status
1127 AND LKUP2.lookup_type = ''FUN_TRX_STATUS''
1128 AND lkup2.view_application_id = 435
1129 AND lkup2.security_group_id =fnd_global.lookup_security_group(lkup2.lookup_type,435)
1130 AND lkup2.language = USERENV(''LANG'')
1131 AND LKUP2.lookup_code IN (''RECEIVED'',''APPROVED'',''REJECTED'',''COMPLETE'',''XFER_RECI_GL'',''XFER_AR'',''XFER_INI_GL'')
1132 AND FTH.batch_id(+) = FTB.batch_id
1133 AND FTTVL.trx_type_id = FTB.trx_type_id
1134 AND FTB.gl_date BETWEEN GLP.start_date AND GLP.end_date
1135 AND FCV.currency_code = FTB.currency_code
1136 AND FTL.trx_id = FTH.trx_id
1137 AND FDL.party_type_flag =''R''
1138 AND FDL.line_id = FTL.line_id
1139 AND FTH.original_trx_id=FTH_ORIG.trx_id(+)
1140 AND FTB.original_batch_id=FTB_ORIG.batch_id(+)
1141 AND FTH.reversed_trx_id=FTH_REV.trx_id(+)
1142 AND FTB.reversed_batch_id=FTB_REV.batch_id(+)
1143 AND XLE.legal_entity_id = FTB.from_le_id
1144 AND XLE1.legal_entity_id = FTH.to_le_id
1145 AND fdl.dist_type_flag IN (''P'',''L'')
1146 AND GL0.ledger_id = FTB.from_ledger_id
1147 AND GLP.period_set_name =GL0.period_set_name
1148 AND GLP.period_type = GL0.accounted_period_type
1149 AND GL1.ledger_id(+) = FTH.to_ledger_id
1150 AND FG.parameter1 = TO_CHAR(FTH.recipient_id)
1151 AND FG.instance_set_id = FOIS.instance_set_id
1152 AND FOIS.instance_set_name = ''FUN_TRX_HEADERS_SET''
1153 AND NVL(FG.end_date, SYSDATE+1) > SYSDATE
1154 AND FG.grantee_key = ''' || l_grantee_key||'''';
1155
1156
1157
1158
1159 IF ((p_para_rec.pay_account_from IS NOT NULL) AND (p_para_rec.pay_account_to
1160 IS NOT NULL) AND (p_para_rec.rec_dist_acc_from IS NOT NULL) AND
1161 (p_para_rec.rec_dist_acc_to IS NOT NULL)) THEN
1162
1163 x_inbound_query:=x_inbound_query ||' AND GLCC.'||l_acc_segment||
1164 ' BETWEEN decode(fdl.dist_type_flag,''P'','||p_para_rec.pay_account_from||',''L'','||
1165 p_para_rec.rec_dist_acc_from||') '||
1166
1167
1168 ' AND decode(fdl.dist_type_flag,''P'','||p_para_rec.pay_account_to||',''L'','||
1169 p_para_rec.rec_dist_acc_to||') ';
1170
1171 ELSIF ((p_para_rec.pay_account_from IS NOT NULL) AND
1172 (p_para_rec.pay_account_to IS NOT NULL)) THEN
1173
1174 x_inbound_query:=x_inbound_query ||' AND GLCC.'||l_acc_segment||
1175 ' BETWEEN decode(fdl.dist_type_flag,''P'','||p_para_rec.rec_account_from||',GLCC.'||l_acc_segment||') '||
1176 ' AND decode(fdl.dist_type_flag,''P'','||p_para_rec.rec_account_to||',GLCC.'||l_acc_segment||') ';
1177
1178
1179
1180 ELSIF ((p_para_rec.rec_dist_acc_from IS NOT NULL) AND
1181 (p_para_rec.rec_dist_acc_to IS NOT NULL)) THEN
1182
1183 x_inbound_query:=x_inbound_query ||' AND GLCC.'||l_acc_segment||
1184 ' BETWEEN decode(fdl.dist_type_flag,''L'','||p_para_rec.rec_dist_acc_from||',GLCC.'||l_acc_segment||') '||
1185 ' AND decode(fdl.dist_type_flag,''L'','||p_para_rec.rec_dist_acc_to||',GLCC.'||l_acc_segment||') ';
1186
1187
1188
1189 END IF;
1190
1191
1192
1193
1194 -- Based on the passed in parameters, build additional query conditions
1195 l_progress := '030';
1196
1197
1198
1199 IF ((p_para_rec.initiator_from IS NOT NULL) AND (p_para_rec.initiator_to IS
1200 NOT NULL)) THEN
1201 x_inbound_query := x_inbound_query ||
1202 ' AND INIT_P.PARTY_NAME BETWEEN ''' || p_para_rec.initiator_from ||''' AND'''||
1203 p_para_rec.initiator_to||'''';
1204 END IF;
1205 IF ((p_para_rec.recipient_from IS NOT NULL) AND (p_para_rec.recipient_to IS
1206 NOT NULL)) THEN
1207 x_inbound_query := x_inbound_query ||
1208 ' AND RECI_P.PARTY_NAME BETWEEN ''' || p_para_rec.recipient_from ||''' AND'''||
1209 p_para_rec.recipient_to||'''';
1210 END IF;
1211 IF (p_para_rec.transact_le IS NOT NULL) THEN
1212 x_inbound_query:= x_inbound_query||
1213 ' AND XLE.NAME = '''|| p_para_rec.transact_le||'''';
1214 END IF;
1215
1216 IF (p_para_rec.trading_le IS NOT NULL) THEN
1217 x_inbound_query:= x_inbound_query ||
1218 ' AND XLE1.NAME ='''||p_para_rec.trading_le||'''';
1219 END IF;
1220 IF (p_para_rec.transact_ledger IS NOT NULL) THEN
1221 x_inbound_query:=x_inbound_query ||
1222 ' AND GL0.ledger_id ='||p_para_rec.transact_ledger;
1223 end if;
1224
1225 IF (p_para_rec.trading_ledger IS NOT NULL) THEN
1226 x_inbound_query:= x_inbound_query ||
1227 ' AND GL1.ledger_id(+)='||p_para_rec.trading_ledger;
1228 END IF;
1229
1230
1231 IF (p_para_rec.batch_number_from IS NOT NULL) AND
1232 (p_para_rec.batch_number_to IS NOT NULL) THEN
1233 x_inbound_query := x_inbound_query ||
1234 ' AND FTB.batch_number BETWEEN ''' || p_para_rec.batch_number_from
1235 || ''' AND ''' || p_para_rec.batch_number_to || '''';
1236 END IF;
1237
1238
1239
1240 IF (p_para_rec.gl_date_from IS NOT NULL) AND
1241 (p_para_rec.gl_date_to IS NOT NULL) THEN
1242 x_inbound_query := x_inbound_query ||
1243 ' AND FTB.gl_date BETWEEN TO_DATE(''' || p_para_rec.gl_date_from
1244 || ''', ''YYYY/MM/DD HH24:MI:SS'') AND '
1245 || ' TO_DATE(''' || p_para_rec.gl_date_to
1246 || ''', ''YYYY/MM/DD HH24:MI:SS'')';
1247 END IF;
1248 IF (p_para_rec.batch_status IS NOT NULL) THEN
1249 x_inbound_query := x_inbound_query ||
1250 ' AND FTB.status = ''' || p_para_rec.batch_status || '''';
1251 END IF;
1252 IF (p_para_rec.transaction_status IS NOT NULL) THEN
1253 x_inbound_query := x_inbound_query ||
1254 ' AND FTH.status = ''' || p_para_rec.transaction_status || '''';
1255 END IF;
1256 IF (p_para_rec.trx_type_id IS NOT NULL) THEN
1257 x_inbound_query := x_inbound_query ||
1258 ' AND FTB.trx_type_id = ' || p_para_rec.trx_type_id;
1259 END IF;
1260 IF (p_para_rec.currency_code IS NOT NULL) THEN
1261 x_inbound_query := x_inbound_query ||
1262 ' AND FTB.currency_code = ''' || p_para_rec.currency_code || '''';
1263 END IF;
1264 IF (p_para_rec.ar_invoice_number IS NOT NULL) THEN
1265 x_inbound_query := x_inbound_query ||
1266 ' AND FTH.ar_invoice_number = ''' || p_para_rec.ar_invoice_number
1267 || '''';
1268 END IF;
1269
1270 IF (p_para_rec.account_type IS NOT NULL) THEN
1271 x_inbound_query:= x_inbound_query ||
1272 ' AND lkup.lookup_code ='''||p_para_rec.account_type||'''';
1273 END IF;
1274
1275
1276
1277
1278
1279 FUN_UTIL.log_conc_stmt(
1280 g_pkg_name, l_api_name, l_progress, 'x_inbound_query', x_inbound_query);
1281 FUN_UTIL.log_conc_end(g_pkg_name, l_api_name);
1282
1283 EXCEPTION
1284 WHEN OTHERS THEN
1285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1286 FUN_UTIL.log_conc_unexp(
1287 g_pkg_name, l_api_name, l_progress);
1288 RAISE;
1289 END build_account_inquery;
1290
1291
1292
1293
1294 -------------------------------------------------------------------------------
1295 --Start of Comments
1296 --Function:
1297 -- Return XML data given the query string
1298 -- Also change XML data to given rowset tag and row tag
1299 --
1300 --End of Comments
1301 -------------------------------------------------------------------------------
1302 PROCEDURE get_xml(
1303 x_return_status OUT NOCOPY VARCHAR2,
1304 p_query IN VARCHAR2,
1305 p_rowset_tag IN VARCHAR2 DEFAULT NULL,
1306 p_row_tag IN VARCHAR2 DEFAULT NULL,
1307 x_xml OUT NOCOPY CLOB
1308 ) IS
1309 l_api_name VARCHAR2(20);
1310 l_progress VARCHAR2(3);
1311
1312 l_ctx dbms_xmlgen.ctxHandle;
1313 BEGIN
1314 l_api_name := 'get_xml';
1315 l_progress := '000';
1316
1317 FUN_UTIL.log_conc_start(g_pkg_name, l_api_name);
1318
1319 -- Initialize API return status to success
1320 x_return_status := FND_API.G_RET_STS_SUCCESS;
1321
1322 l_progress := '010';
1323 l_ctx := DBMS_XMLGEN.newcontext(p_query);
1324 FUN_UTIL.log_conc_stmt(
1325 g_pkg_name, l_api_name, l_progress, 'l_ctx', l_ctx);
1326
1327
1328 -- change rowset tag
1329 IF p_rowset_tag IS NOT NULL THEN
1330 DBMS_XMLGEN.setRowSetTag(l_ctx, p_rowset_tag);
1331 END IF;
1332
1333 -- change row tag
1334 IF p_row_tag IS NOT NULL THEN
1335 DBMS_XMLGEN.setRowTag(l_ctx, p_row_tag);
1336 END IF;
1337
1338
1339 l_progress := '020';
1340 x_xml := DBMS_XMLGEN.getXML(l_ctx);
1341
1342 DBMS_XMLGEN.closecontext(l_ctx);
1343
1344 EXCEPTION
1345 WHEN OTHERS THEN
1346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1347 DBMS_XMLGEN.closecontext(l_ctx);
1348 FUN_UTIL.log_conc_unexp(g_pkg_name, l_api_name, l_progress);
1349 RAISE;
1350 END get_xml;
1351
1352
1353
1354
1355 -------------------------------------------------------------------------------
1356 --Start of Comments
1357 --Function:
1358 -- Construct XML data source based on report parameters, XML data of
1359 -- outbound/inbound, format the data source to be XML Publisher compatible
1360 --
1361 --End of Comments
1362 -------------------------------------------------------------------------------
1363 PROCEDURE construct_output(
1364 x_return_status OUT NOCOPY VARCHAR2,
1365 p_para_rec IN FUN_REPORT_PVT.summaryreport_para_rec_type,
1366 p_outbound_trxs IN CLOB,
1367 p_inbound_trxs IN CLOB
1368 ) IS
1369 l_api_name VARCHAR2(30);
1370 l_progress VARCHAR2(5);
1371
1372 l_para_meaning_list VARCHAR2(2000);
1373
1374 l_initiator_name HZ_PARTIES.party_name%TYPE;
1375 l_recipient_name HZ_PARTIES.party_name%TYPE;
1376 l_batch_status FND_LOOKUPS.meaning%TYPE;
1377 l_trx_status FND_LOOKUPS.meaning%TYPE;
1378 l_trx_type FUN_TRX_TYPES_VL.trx_type_name%TYPE;
1379 l_currency FND_CURRENCIES_VL.name%TYPE;
1380 l_encoding VARCHAR2(20);
1381 l_offset INTEGER;
1382 BEGIN
1383 l_api_name := 'construct_output';
1384 l_progress := '000';
1385
1386 FUN_UTIL.log_conc_start(g_pkg_name, l_api_name);
1387
1388
1389 -- Initialize API return status to success
1390 x_return_status := FND_API.G_RET_STS_SUCCESS;
1391
1392
1393 -- Construct the parameter list section
1394 l_progress := '010';
1395
1396 BEGIN
1397 IF p_para_rec.initiator_id IS NOT NULL THEN
1398 SELECT party_name
1399 INTO l_initiator_name
1400 FROM HZ_PARTIES
1401 WHERE party_id = p_para_rec.initiator_id;
1402 END IF;
1403 IF p_para_rec.recipient_id IS NOT NULL THEN
1404 SELECT party_name
1405 INTO l_recipient_name
1406 FROM HZ_PARTIES
1407 WHERE party_id = p_para_rec.recipient_id;
1408 END IF;
1409 IF p_para_rec.batch_status IS NOT NULL THEN
1410 SELECT meaning
1411 INTO l_batch_status
1412 FROM FND_LOOKUP_VALUES
1413 WHERE lookup_type = 'FUN_BATCH_STATUS'
1414 AND view_application_id = 435
1415 AND security_group_id =fnd_global.lookup_security_group(lookup_type,435)
1416 AND language = USERENV('LANG')
1417 AND lookup_code = p_para_rec.batch_status;
1418 END IF;
1419
1420 IF p_para_rec.transaction_status IS NOT NULL THEN
1421 SELECT meaning
1422 INTO l_trx_status
1423 FROM FND_LOOKUP_VALUES
1424 WHERE lookup_type = 'FUN_TRX_STATUS'
1425 AND view_application_id = 435
1426 AND security_group_id=fnd_global.lookup_security_group(lookup_type,435)
1427 AND language = USERENV('LANG')
1428 AND lookup_code = p_para_rec.transaction_status;
1429 END IF;
1430 IF p_para_rec.trx_type_id IS NOT NULL THEN
1431 SELECT trx_type_name
1432 INTO l_trx_type
1433 FROM FUN_TRX_TYPES_VL
1434 WHERE trx_type_id = p_para_rec.trx_type_id;
1435 END IF;
1436 IF p_para_rec.currency_code IS NOT NULL THEN
1437 SELECT name
1438 INTO l_currency
1439 FROM FND_CURRENCIES_VL
1440 WHERE currency_code = p_para_rec.currency_code;
1441 END IF;
1442 EXCEPTION
1443 WHEN OTHERS THEN
1444 FUN_UTIL.log_conc_unexp(g_pkg_name, l_api_name, l_progress);
1445 END;
1446 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
1447 l_para_meaning_list :=
1448 '<?xml version="1.0" encoding="'||l_encoding||'"?>' ||
1449 '<REPORT_ROOT>' ||
1450 '<PARAMETERS>' ||
1451 '<PARA_BATCH_TYPE>' || p_para_rec.batch_type || '</PARA_BATCH_TYPE>' ||
1452 '<PARA_INITIATOR>' || l_initiator_name || '</PARA_INITIATOR>' ||
1453 '<PARA_RECIPIENT>' || l_recipient_name || '</PARA_RECIPIENT>' ||
1454 '<PARA_GL_DATE_FROM>' || p_para_rec.gl_date_from || '</PARA_GL_DATE_FROM>' ||
1455 '<PARA_GL_DATE_TO>' || p_para_rec.gl_date_to ||'</PARA_GL_DATE_TO>' ||
1456 '<PARA_BATCH_DATE_FROM>' || p_para_rec.batch_date_from || '</PARA_BATCH_DATE_FROM>' ||
1457 '<PARA_BATCH_DATE_TO>' || p_para_rec.batch_date_to || '</PARA_BATCH_DATE_TO>' ||
1458 '<PARA_BATCH_NUMBER_FROM>' || p_para_rec.batch_number_from || '</PARA_BATCH_NUMBER_FROM>' ||
1459 '<PARA_BATCH_NUMBER_TO>' || p_para_rec.batch_number_to || '</PARA_BATCH_NUMBER_TO>' ||
1460 '<PARA_BATCH_STATUS>' || l_batch_status || '</PARA_BATCH_STATUS>' ||
1461 '<PARA_TRX_STATUS>' || l_trx_status || '</PARA_TRX_STATUS>' ||
1462 '<PARA_TRX_TYPE>' || l_trx_type || '</PARA_TRX_TYPE>' ||
1463 '<PARA_CURRENCY>' || l_currency || '</PARA_CURRENCY>' ||
1464 '<PARA_INVOCING_RULE>' || p_para_rec.invoice_flag || '</PARA_INVOCING_RULE>' ||
1465 '<PARA_INVOICE_NUMBER>' || p_para_rec.ar_invoice_number || '</PARA_INVOICE_NUMBER>' ||
1466 '</PARAMETERS>';
1467
1468
1469 -- Save the parameter list to output file
1470 l_progress := '020';
1471 FND_FILE.put_line(FND_FILE.output, l_para_meaning_list);
1472
1473
1474 -- Process the XML data source and save to output file
1475 l_progress := '030';
1476 IF (p_para_rec.batch_type = 'BOTH') THEN
1477 FUN_UTIL.log_conc_stmt(
1478 g_pkg_name, l_api_name, l_progress, 'construct both', '');
1479 IF DBMS_LOB.getlength(p_outbound_trxs) IS NULL THEN
1480 l_progress := '030.a';
1481 FND_FILE.put_line(FND_FILE.output, '<OUTBOUND> </OUTBOUND>');
1482 ELSE
1483 -- trim header of outbound trxs
1484 -- save outbound trxs
1485 l_progress := '030.b';
1486 l_offset := DBMS_LOB.instr (
1487 lob_loc => p_outbound_trxs,
1488 pattern => '?>',
1489 offset => 1,
1490 nth => 1);
1491 FUN_UTIL.log_conc_stmt(
1492 g_pkg_name, l_api_name, l_progress, 'l_offset', l_offset);
1493
1494 save_xml(
1495 x_return_status => x_return_status,
1496 p_trxs => p_outbound_trxs,
1497 p_offset => l_offset+2);
1498 END IF;
1499
1500 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1501 RAISE FND_API.G_EXC_ERROR;
1502 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1504 END IF;
1505
1506
1507 IF DBMS_LOB.getlength(p_inbound_trxs) IS NULL THEN
1508 l_progress := '030.c';
1509 FND_FILE.put_line(FND_FILE.output, '<INBOUND> </INBOUND>');
1510 ELSE
1511 -- trim header of inbound trxs
1512 -- save inbound trxs
1513 l_progress := '030.d';
1514 l_offset := DBMS_LOB.instr (
1515 lob_loc => p_inbound_trxs,
1516 pattern => '?>',
1517 offset => 1,
1518 nth => 1);
1519 FUN_UTIL.log_conc_stmt(
1520 g_pkg_name, l_api_name, l_progress, 'l_offset', l_offset);
1521
1522 save_xml(
1523 x_return_status => x_return_status,
1524 p_trxs => p_inbound_trxs,
1525 p_offset => l_offset+2);
1526 END IF;
1527
1528
1529
1530 ELSIF (p_para_rec.batch_type = 'IN') THEN
1531 FND_FILE.put_line(FND_FILE.output, '<OUTBOUND> </OUTBOUND>');
1532
1533 IF DBMS_LOB.getlength(p_inbound_trxs) IS NULL THEN
1534 l_progress := '030.e';
1535 FND_FILE.put_line(FND_FILE.output, '<INBOUND> </INBOUND>');
1536 ELSE
1537 -- trim header of inbound trxs
1538 -- append inbound trxs to outbound trxs
1539 l_progress := '030.f';
1540 l_offset := DBMS_LOB.instr (
1541 lob_loc => p_inbound_trxs,
1542 pattern => '?>',
1543 offset => 1,
1544 nth => 1);
1545 FUN_UTIL.log_conc_stmt(
1546 g_pkg_name, l_api_name, l_progress, 'l_offset', l_offset);
1547
1548 save_xml(
1549 x_return_status => x_return_status,
1550 p_trxs => p_inbound_trxs,
1551 p_offset => l_offset+2);
1552 END IF;
1553
1554 ELSE -- (p_para_rec.batch_type = 'OUT')
1555 IF DBMS_LOB.getlength(p_outbound_trxs) IS NULL THEN
1556 l_progress := '030.g';
1557 FND_FILE.put_line(FND_FILE.output, '<OUTBOUND> </OUTBOUND>');
1558 ELSE
1559 -- trim header of outbound trxs
1560 -- append outbound trxs to outbound trxs
1561 l_progress := '030.h';
1562 l_offset := DBMS_LOB.instr (
1563 lob_loc => p_outbound_trxs,
1564 pattern => '?>',
1565 offset => 1,
1566 nth => 1);
1567 FUN_UTIL.log_conc_stmt(
1568 g_pkg_name, l_api_name, l_progress, 'l_offset', l_offset);
1569
1570 save_xml(
1571 x_return_status => x_return_status,
1572 p_trxs => p_outbound_trxs,
1573 p_offset => l_offset+2);
1574 END IF;
1575
1576 FND_FILE.put_line(FND_FILE.output, '<INBOUND> </INBOUND>');
1577
1578 END IF;
1579
1580 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1581 RAISE FND_API.G_EXC_ERROR;
1582 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1583 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1584 END IF;
1585
1586
1587 FND_FILE.put_line(FND_FILE.output, '</REPORT_ROOT>');
1588
1589
1590 FUN_UTIL.log_conc_end(g_pkg_name, l_api_name);
1591
1592 EXCEPTION
1593 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1594 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1595 FUN_UTIL.log_conc_err(
1596 g_pkg_name, l_api_name, l_progress, 'error in construct_output');
1597 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1598 WHEN FND_API.G_EXC_ERROR THEN
1599 x_return_status := FND_API.G_RET_STS_ERROR;
1600 FUN_UTIL.log_conc_err(
1601 g_pkg_name, l_api_name, l_progress, 'error in construct_output');
1602 RAISE FND_API.G_EXC_ERROR;
1603 WHEN OTHERS THEN
1604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1605 FUN_UTIL.log_conc_unexp(
1606 g_pkg_name, l_api_name, l_progress);
1607 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1608 END construct_output;
1609
1610
1611
1612
1613
1614 -------------------------------------------------------------------------------
1615 --Start of Comments
1616 --Function:
1617 -- Construct XML data source based on report parameters, XML data of
1618 -- outbound/inbound, format the data source to be XML Publisher compatible
1619 --
1620 --End of Comments
1621 -------------------------------------------------------------------------------
1622 PROCEDURE construct_account_output(
1623 x_return_status OUT NOCOPY VARCHAR2,
1624 p_para_rec IN FUN_REPORT_PVT.accountreport_para_rec_type,
1625 p_trxs IN CLOB
1626 ) IS
1627 l_api_name VARCHAR2(30);
1628 l_progress VARCHAR2(5);
1629
1630 l_para_meaning_list VARCHAR2(2000);
1631
1632 l_initiator_name HZ_PARTIES.party_name%TYPE;
1633 l_recipient_name HZ_PARTIES.party_name%TYPE;
1634 l_batch_status FND_LOOKUPS.meaning%TYPE;
1635 l_trx_status FND_LOOKUPS.meaning%TYPE;
1636 l_acc_type FND_LOOKUPS.meaning%TYPE;
1637 l_trx_type FUN_TRX_TYPES_VL.trx_type_name%TYPE;
1638 l_currency FND_CURRENCIES_VL.name%TYPE;
1639 l_transact_ledger GL_LEDGERS.name%TYPE;
1640 l_trading_ledger GL_LEDGERS.name%TYPE;
1641 l_encoding VARCHAR2(20);
1642 l_offset INTEGER;
1643 BEGIN
1644 l_api_name := 'construct_account_output';
1645 l_progress := '000';
1646
1647 FUN_UTIL.log_conc_start(g_pkg_name, l_api_name);
1648
1649
1650 -- Initialize API return status to success
1651 x_return_status := FND_API.G_RET_STS_SUCCESS;
1652
1653
1654 -- Construct the parameter list section
1655 l_progress := '010';
1656
1657 BEGIN
1658 IF p_para_rec.batch_status IS NOT NULL THEN
1659 SELECT meaning
1660 INTO l_batch_status
1661 FROM FND_LOOKUP_VALUES
1662 WHERE lookup_type = 'FUN_BATCH_STATUS'
1663 AND view_application_id = 435
1664 AND security_group_id=fnd_global.lookup_security_group(lookup_type,435)
1665 AND language = USERENV('LANG')
1666 AND lookup_code = p_para_rec.batch_status;
1667 END IF;
1668 IF p_para_rec.transaction_status IS NOT NULL THEN
1669 SELECT meaning
1670 INTO l_trx_status
1671 FROM FND_LOOKUP_VALUES
1672 WHERE lookup_type = 'FUN_TRX_STATUS'
1673 AND view_application_id = 435
1674 AND security_group_id=fnd_global.lookup_security_group(lookup_type,435)
1675 AND language = USERENV('LANG')
1676 AND lookup_code = p_para_rec.transaction_status;
1677 END IF;
1678 IF p_para_rec.trx_type_id IS NOT NULL THEN
1679 SELECT trx_type_name
1680 INTO l_trx_type
1681 FROM FUN_TRX_TYPES_VL
1682 WHERE trx_type_id = p_para_rec.trx_type_id;
1683 END IF;
1684 IF p_para_rec.currency_code IS NOT NULL THEN
1685 SELECT name
1686 INTO l_currency
1687 FROM FND_CURRENCIES_VL
1688 WHERE currency_code = p_para_rec.currency_code;
1689 END IF;
1690 IF p_para_rec.account_type IS NOT NULL THEN
1691 SELECT meaning
1692 INTO l_acc_type
1693 FROM FND_LOOKUPS
1694 WHERE lookup_code = p_para_rec.account_type
1695 AND lookup_type ='ACCOUNT_TYPE';
1696 END IF;
1697 IF p_para_rec.transact_ledger IS NOT NULL THEN
1698 SELECT name
1699 INTO l_transact_ledger
1700 FROM gl_ledgers
1701 WHERE ledger_id = p_para_rec.transact_ledger;
1702 END IF;
1703 IF p_para_rec.trading_ledger IS NOT NULL THEN
1704 SELECT name
1705 INTO l_trading_ledger
1706 FROM gl_ledgers
1707 WHERE ledger_id = p_para_rec.trading_ledger;
1708 END IF;
1709 EXCEPTION
1710 WHEN OTHERS THEN
1711 FUN_UTIL.log_conc_unexp(g_pkg_name, l_api_name, l_progress);
1712 END;
1713 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
1714 l_para_meaning_list :=
1715 '<?xml version="1.0" encoding="'||l_encoding||'"?>' ||
1716 '<REPORT_ROOT>' ||
1717 '<PARAMETERS>' ||
1718 '<PARA_INITIATOR_FROM>'||p_para_rec.initiator_from||
1719 '</PARA_INITIATOR_FROM>'||
1720 '<PARA_INITIATOR_TO>'||p_para_rec.initiator_to||'</PARA_INITIATOR_TO>'||
1721 '<PARA_TRANSACT_LE>'||p_para_rec.transact_le||'</PARA_TRANSACT_LE>'||
1722 '<PARA_TRANSACT_LEDGER>'||l_transact_ledger||'</PARA_TRANSACT_LEDGER>'||
1723 '<PARA_RECIPIENT_FROM>'||p_para_rec.recipient_from||'</PARA_RECIPIENT_FROM>'||
1724 '<PARA_RECIPIENT_TO>'||p_para_rec.recipient_to||'</PARA_RECIPIENT_TO>'||
1725 '<PARA_TRADING_LE>'||p_para_rec.trading_le||'</PARA_TRADING_LE>'||
1726 '<PARA_TRADING_LEDGER>'||l_trading_ledger||'</PARA_TRADING_LEDGER>'||
1727 '<PARA_GL_DATE_FROM>' || p_para_rec.gl_date_from || '</PARA_GL_DATE_FROM>' ||
1728 '<PARA_GL_DATE_TO>' || p_para_rec.gl_date_to ||'</PARA_GL_DATE_TO>' ||
1729 '<PARA_BATCH_TYPE>' || p_para_rec.batch_type || '</PARA_BATCH_TYPE>' ||
1730 '<PARA_BATCH_NUMBER_FROM>' || p_para_rec.batch_number_from || '</PARA_BATCH_NUMBER_FROM>' ||
1731 '<PARA_BATCH_NUMBER_TO>' || p_para_rec.batch_number_to || '</PARA_BATCH_NUMBER_TO>' ||
1732 '<PARA_BATCH_STATUS>' || l_batch_status || '</PARA_BATCH_STATUS>' ||
1733 '<PARA_TRX_STATUS>' || l_trx_status || '</PARA_TRX_STATUS>' ||
1734 '<PARA_TRX_TYPE>' || l_trx_type || '</PARA_TRX_TYPE>' ||
1735 '<PARA_ACC_TYPE>' || l_acc_type || '</PARA_ACC_TYPE>' ||
1736 '<PARA_CURRENCY>' || l_currency || '</PARA_CURRENCY>' ||
1737 '<PARA_RECEIV_ACCOUNT_FROM>'||p_para_rec.rec_account_from||'</PARA_RECEIV_ACCOUNT_FROM>'||
1738 '<PARA_RECEIV_ACCOUNT_TO>'||p_para_rec.rec_account_to||'</PARA_RECEIV_ACCOUNT_TO>'||
1739 '<PARA_PAY_ACCOUNT_FROM>'||p_para_rec.pay_account_from||'</PARA_PAY_ACCOUNT_FROM>'||
1740 '<PARA_PAY_ACCOUNT_TO>'||p_para_rec.pay_account_to||'</PARA_PAY_ACCOUNT_TO>'||
1741 '<PARA_INIT_DIST_ACCOUNT_FROM>'||p_para_rec.init_dist_acc_from||'</PARA_INIT_DIST_ACCOUNT_FROM>'||
1742 '<PARA_INIT_DIST_ACCOUNT_TO>'||p_para_rec.init_dist_acc_to||'</PARA_INIT_DIST_ACCOUNT_TO>'||
1743 '<PARA_RECIP_DIST_ACCOUNT_FROM>'||p_para_rec.rec_dist_acc_from||'</PARA_RECIP_DIST_ACCOUNT_FROM>'||
1744 '<PARA_RECIP_DIST_ACCOUNT_TO>'||p_para_rec.rec_dist_acc_to||'</PARA_RECIP_DIST_ACCOUNT_TO>'||
1745 '<PARA_INVOICE_NUMBER>' || p_para_rec.ar_invoice_number || '</PARA_INVOICE_NUMBER>' ||
1746
1747 '</PARAMETERS>';
1748
1749
1750 -- Save the parameter list to output file
1751 l_progress := '020';
1752 FND_FILE.put_line(FND_FILE.output, l_para_meaning_list);
1753 FUN_UTIL.log_conc_stmt(
1754 g_pkg_name, l_api_name, l_progress, 'xml_parameters',l_para_meaning_list);
1755
1756
1757
1758 -- Process the XML data source and save to output file
1759 l_progress := '030';
1760
1761
1762 FUN_UTIL.log_conc_stmt(
1763 g_pkg_name, l_api_name, l_progress, 'construct both', '');
1764 IF DBMS_LOB.getlength(p_trxs) IS NULL THEN
1765 l_progress := '030.a';
1766 ELSE
1767 -- trim header of trxs
1768 -- save trxs
1769 l_progress := '030.b';
1770 l_offset := DBMS_LOB.instr (
1771 lob_loc => p_trxs,
1772 pattern => '?>',
1773 offset => 1,
1774 nth => 1);
1775 FUN_UTIL.log_conc_stmt(
1776 g_pkg_name, l_api_name, l_progress, 'l_offset', l_offset);
1777
1778 save_xml(
1779 x_return_status => x_return_status,
1780 p_trxs => p_trxs,
1781 p_offset => l_offset+2);
1782 END IF;
1783
1784 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1785 RAISE FND_API.G_EXC_ERROR;
1786 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1787 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1788 END IF;
1789
1790
1791
1792
1793
1794
1795 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1796 RAISE FND_API.G_EXC_ERROR;
1797 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1798 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1799 END IF;
1800
1801
1802 FND_FILE.put_line(FND_FILE.output, '</REPORT_ROOT>');
1803
1804
1805 FUN_UTIL.log_conc_end(g_pkg_name, l_api_name);
1806
1807 EXCEPTION
1808 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1809 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1810 FUN_UTIL.log_conc_err(
1811 g_pkg_name, l_api_name, l_progress, 'error in construct_output');
1812 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1813 WHEN FND_API.G_EXC_ERROR THEN
1814 x_return_status := FND_API.G_RET_STS_ERROR;
1815 FUN_UTIL.log_conc_err(
1816 g_pkg_name, l_api_name, l_progress, 'error in construct_output');
1817 RAISE FND_API.G_EXC_ERROR;
1818 WHEN OTHERS THEN
1819 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1820 FUN_UTIL.log_conc_unexp(
1821 g_pkg_name, l_api_name, l_progress);
1822 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1823 END construct_account_output;
1824
1825
1826
1827
1828 -------------------------------------------------------------------------------
1829 --Start of Comments
1830 --Function:
1831 -- Save CLOB to concurrent program output file given CLOB offset
1832 --
1833 --End of Comments
1834 ------------------------------------------------------------------------------
1835 PROCEDURE save_xml(
1836 x_return_status OUT NOCOPY VARCHAR2,
1837 p_trxs IN CLOB,
1838 p_offset IN INTEGER DEFAULT 1
1839 ) IS
1840 l_api_name VARCHAR2(100);
1841 l_progress VARCHAR2(3);
1842
1843 l_length INTEGER;
1844 l_buffer VARCHAR2(32766);
1845 l_amount BINARY_INTEGER := 8175; -- 32700/4 Since a single character can accomidate 4 bites
1846 l_pos INTEGER;
1847 BEGIN
1848 l_api_name := 'save_xml';
1849 l_progress := '000';
1850 l_pos := p_offset;
1851
1852 FUN_UTIL.log_conc_start(g_pkg_name, l_api_name);
1853
1854
1855 -- Initialize API return status to success
1856 x_return_status := FND_API.G_RET_STS_SUCCESS;
1857
1858 l_progress := '010';
1859 l_length := DBMS_LOB.getlength(p_trxs);
1860 FUN_UTIL.log_conc_stmt(
1861 g_pkg_name, l_api_name, l_progress, 'l_length', l_length);
1862
1863 WHILE (l_pos <= l_length)
1864 LOOP
1865 DBMS_LOB.read(p_trxs, l_amount, l_pos, l_buffer);
1866 FND_FILE.put(FND_FILE.output, l_buffer);
1867
1868 -- FUN_UTIL.log_conc_stmt(g_pkg_name, l_api_name, l_progress, 'xml_date',l_buffer);
1869 -- l_buffer:=NULL;
1870
1871 l_pos := l_pos + l_amount;
1872 END LOOP;
1873 EXCEPTION
1874 WHEN OTHERS THEN
1875 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1876 FUN_UTIL.log_conc_unexp(
1877 g_pkg_name, l_api_name, l_progress);
1878 RAISE;
1879 END save_xml;
1880
1881
1882
1883
1884 -------------------------------------------------------------------------------
1885 --Start of Comments
1886 --Function:
1887 -- Main program of data extractor for Intercompany Transaction Summary
1888 -- Report, and save the XML Publisher compatible data to concurrent
1889 -- program output file
1890 --
1891 --End of Comments
1892 ------------------------------------------------------------------------------
1893 PROCEDURE create_summaryreport(
1894 errbuf OUT NOCOPY VARCHAR2,
1895 retcode OUT NOCOPY NUMBER,
1896 p_batch_type IN VARCHAR2,
1897 p_initiator_id IN NUMBER DEFAULT NULL,
1898 p_recipient_id IN NUMBER DEFAULT NULL,
1899 p_batch_number_from IN VARCHAR2 DEFAULT NULL,
1900 p_batch_number_to IN VARCHAR2 DEFAULT NULL,
1901 p_gl_date_from IN VARCHAR2 DEFAULT NULL,
1902 p_gl_date_to IN VARCHAR2 DEFAULT NULL,
1903 p_batch_date_from IN VARCHAR2 DEFAULT NULL,
1904 p_batch_date_to IN VARCHAR2 DEFAULT NULL,
1905 p_batch_status IN VARCHAR2 DEFAULT NULL,
1906 p_transaction_status IN VARCHAR2 DEFAULT NULL,
1907 p_trx_type_id IN NUMBER DEFAULT NULL,
1908 p_currency_code IN VARCHAR2 DEFAULT NULL,
1909 p_invoice_flag IN VARCHAR2 DEFAULT NULL,
1910 p_ar_invoice_number IN VARCHAR2 DEFAULT NULL
1911 ) IS
1912 l_api_name VARCHAR2(30);
1913 l_progress VARCHAR2(3);
1914
1915 l_para_rec FUN_REPORT_PVT.summaryreport_para_rec_type;
1916 l_return_status VARCHAR2(20);
1917 l_outbound_query VARCHAR2(20000);
1918 l_inbound_query VARCHAR2(20000);
1919 l_trxs CLOB;
1920 l_outbound_trxs CLOB;
1921 l_inbound_trxs CLOB;
1922 BEGIN
1923 l_api_name := 'create_summaryreport';
1924 l_progress := '000';
1925 errbuf := NULL;
1926 retcode := 0;
1927
1928
1929 -- Save the in parameters in fnd log file
1930 FUN_UTIL.log_conc_start(g_pkg_name, l_api_name);
1931 FUN_UTIL.log_conc_stmt(
1932 g_pkg_name, l_api_name, l_progress, 'p_batch_type', p_batch_type);
1933 FUN_UTIL.log_conc_stmt(
1934 g_pkg_name, l_api_name, l_progress, 'p_initiator_id', p_initiator_id);
1935 FUN_UTIL.log_conc_stmt(
1936 g_pkg_name, l_api_name, l_progress, 'p_recipient_id', p_recipient_id);
1937 FUN_UTIL.log_conc_stmt(
1938 g_pkg_name, l_api_name, l_progress, 'p_batch_number_from', p_batch_number_from);
1939 FUN_UTIL.log_conc_stmt(
1940 g_pkg_name, l_api_name, l_progress, 'p_batch_number_to', p_batch_number_to);
1941 FUN_UTIL.log_conc_stmt(
1942 g_pkg_name, l_api_name, l_progress, 'p_gl_date_from', p_gl_date_from);
1943 FUN_UTIL.log_conc_stmt(
1944 g_pkg_name, l_api_name, l_progress, 'p_gl_date_to', p_gl_date_to);
1945 FUN_UTIL.log_conc_stmt(
1946 g_pkg_name, l_api_name, l_progress, 'p_batch_date_from', p_batch_date_from);
1947 FUN_UTIL.log_conc_stmt(
1948 g_pkg_name, l_api_name, l_progress, 'p_batch_date_to', p_batch_date_to);
1949 FUN_UTIL.log_conc_stmt(
1950 g_pkg_name, l_api_name, l_progress, 'p_batch_status', p_batch_status);
1951 FUN_UTIL.log_conc_stmt(
1952 g_pkg_name, l_api_name, l_progress, 'p_transaction_status', p_transaction_status);
1953 FUN_UTIL.log_conc_stmt(
1954 g_pkg_name, l_api_name, l_progress, 'p_trx_type_id', p_trx_type_id);
1955 FUN_UTIL.log_conc_stmt(
1956 g_pkg_name, l_api_name, l_progress, 'p_currency_code', p_currency_code);
1957 FUN_UTIL.log_conc_stmt(
1958 g_pkg_name, l_api_name, l_progress, 'p_invoice_flag', p_invoice_flag);
1959 FUN_UTIL.log_conc_stmt(
1960 g_pkg_name, l_api_name, l_progress, 'p_ar_invoice_number', p_ar_invoice_number);
1961
1962
1963 -- initialize summaryreport parameters record
1964 l_progress := '010';
1965 l_para_rec.batch_type := p_batch_type;
1966 l_para_rec.initiator_id := p_initiator_id;
1967 l_para_rec.recipient_id := p_recipient_id;
1968 l_para_rec.batch_number_from := p_batch_number_from;
1969 l_para_rec.batch_number_to := p_batch_number_to;
1970 l_para_rec.gl_date_from := p_gl_date_from;
1971 l_para_rec.gl_date_to := p_gl_date_to;
1972 l_para_rec.batch_date_from := p_batch_date_from;
1973 l_para_rec.batch_date_to := p_batch_date_to;
1974 l_para_rec.batch_status := p_batch_status;
1975 l_para_rec.transaction_status := p_transaction_status;
1976 l_para_rec.trx_type_id := p_trx_type_id;
1977 l_para_rec.currency_code := p_currency_code;
1978 l_para_rec.invoice_flag := p_invoice_flag;
1979 l_para_rec.ar_invoice_number := p_ar_invoice_number;
1980
1981
1982 -- Based on batch type, build the query to get inbound, outbound or both
1983 -- transactions
1984 l_progress := '030';
1985 IF (p_batch_type = 'BOTH') THEN
1986 FUN_UTIL.log_conc_stmt(
1987 g_pkg_name, l_api_name, l_progress, '', 'call build_summary_outquery');
1988 build_summary_outquery(
1989 x_return_status => l_return_status,
1990 p_para_rec => l_para_rec,
1991 x_outbound_query => l_outbound_query
1992 );
1993
1994 FUN_UTIL.log_conc_stmt(
1995 g_pkg_name, l_api_name, l_progress, '', 'call build_summary_inquery');
1996 build_summary_inquery(
1997 x_return_status => l_return_status,
1998 p_para_rec => l_para_rec,
1999 x_inbound_query => l_inbound_query
2000 );
2001 ELSIF (p_batch_type = 'IN') THEN
2002 FUN_UTIL.log_conc_stmt(
2003 g_pkg_name, l_api_name, l_progress, '', 'call build_summary_inquery');
2004 build_summary_inquery(
2005 x_return_status => l_return_status,
2006 p_para_rec => l_para_rec,
2007 x_inbound_query => l_inbound_query
2008 );
2009 ELSIF (p_batch_type = 'OUT') THEN
2010 FUN_UTIL.log_conc_stmt(
2011 g_pkg_name, l_api_name, l_progress, '', 'call build_summary_outquery');
2012 build_summary_outquery(
2013 x_return_status => l_return_status,
2014 p_para_rec => l_para_rec,
2015 x_outbound_query => l_outbound_query
2016 );
2017 ELSE
2018 FUN_UTIL.log_conc_stmt(
2019 g_pkg_name, l_api_name, l_progress, 'batch type is incorrect');
2020 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2021 END IF;
2022
2023 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2024 RAISE FND_API.G_EXC_ERROR;
2025 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2026 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2027 END IF;
2028
2029
2030 -- Get the XML data source
2031 l_progress := '040';
2032 IF (p_batch_type = 'BOTH') THEN
2033 FUN_UTIL.log_conc_stmt(
2034 g_pkg_name, l_api_name, l_progress, '', 'call get_xml for outbound');
2035 get_xml(
2036 x_return_status => l_return_status,
2037 p_query => l_outbound_query,
2038 p_rowset_tag => 'OUTBOUND',
2039 p_row_tag => 'OUTBOUND_BATCH',
2040 x_xml => l_outbound_trxs
2041 );
2042
2043 FUN_UTIL.log_conc_stmt(
2044 g_pkg_name, l_api_name, l_progress, '', 'call get_xml for inbound');
2045 get_xml(
2046 x_return_status => l_return_status,
2047 p_query => l_inbound_query,
2048 p_rowset_tag => 'INBOUND',
2049 p_row_tag => 'INBOUND_BATCH',
2050 x_xml => l_inbound_trxs
2051 );
2052 ELSIF (p_batch_type = 'IN') THEN
2053 FUN_UTIL.log_conc_stmt(
2054 g_pkg_name, l_api_name, l_progress, '', 'call get_xml for inbound');
2055 get_xml(
2056 x_return_status => l_return_status,
2057 p_query => l_inbound_query,
2058 p_rowset_tag => 'INBOUND',
2059 p_row_tag => 'INBOUND_BATCH',
2060 x_xml => l_inbound_trxs
2061 );
2062 ELSIF (p_batch_type = 'OUT') THEN
2063 FUN_UTIL.log_conc_stmt(
2064 g_pkg_name, l_api_name, l_progress, '', 'call get_xml for outbound');
2065 get_xml(
2066 x_return_status => l_return_status,
2067 p_query => l_outbound_query,
2068 p_rowset_tag => 'OUTBOUND',
2069 p_row_tag => 'OUTBOUND_BATCH',
2070 x_xml => l_outbound_trxs
2071 );
2072 END IF;
2073
2074 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2075 RAISE FND_API.G_EXC_ERROR;
2076 END IF;
2077
2078
2079 -- Manupulate XML data source to XML Publisher compatiable format
2080 -- and save it to output file
2081 l_progress := '050';
2082 FUN_UTIL.log_conc_stmt(
2083 g_pkg_name, l_api_name, l_progress, '', 'call construct_output');
2084 construct_output(
2085 x_return_status => l_return_status,
2086 p_para_rec => l_para_rec,
2087 p_outbound_trxs => l_outbound_trxs,
2088 p_inbound_trxs => l_inbound_trxs
2089 );
2090
2091 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2092 RAISE FND_API.G_EXC_ERROR;
2093 END IF;
2094
2095 FUN_UTIL.log_conc_end(g_pkg_name, l_api_name);
2096
2097 EXCEPTION
2098 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2099 retcode := 2;
2100 FUN_UTIL.log_conc_err(
2101 g_pkg_name, l_api_name, l_progress, 'error in create_summaryreport');
2102 WHEN FND_API.G_EXC_ERROR THEN
2103 retcode := 2;
2104 FUN_UTIL.log_conc_unexp(
2105 g_pkg_name, l_api_name, l_progress);
2106 FUN_UTIL.log_conc_err(
2107 g_pkg_name, l_api_name, l_progress, 'error in create_summaryreport');
2108 WHEN OTHERS THEN
2109 retcode := 2;
2110 FUN_UTIL.log_conc_err(
2111 g_pkg_name, l_api_name, l_progress, 'error in create_summaryreport');
2112 END create_summaryreport;
2113
2114
2115 -------------------------------------------------------------------------------
2116 --Start of Comments
2117 --Function:
2118 -- Main program of data extractor for Intercompany Account details
2119 -- Report, and save the XML Publisher compatible data to concurrent
2120 -- program output file
2121 --
2122 --End of Comments
2123 ------------------------------------------------------------------------------
2124 PROCEDURE create_accountreport(
2125 errbuf OUT NOCOPY VARCHAR2,
2126 retcode OUT NOCOPY NUMBER,
2127 p_initiator_from IN VARCHAR2 DEFAULT NULL,
2128 p_initiator_to IN VARCHAR2 DEFAULT NULL,
2129 p_transact_le IN VARCHAR2 DEFAULT NULL,
2130 p_transact_ledger_id IN NUMBER DEFAULT NULL,
2131 p_recipient_from IN VARCHAR2 DEFAULT NULL,
2132 p_recipient_to IN VARCHAR2 DEFAULT NULL,
2133 p_trading_le IN VARCHAR2 DEFAULT NULL,
2134 p_trading_ledger_id IN NUMBER DEFAULT NULL,
2135 p_gl_date_from IN VARCHAR2 DEFAULT NULL,
2136 p_gl_date_to IN VARCHAR2 DEFAULT NULL,
2137 p_batch_type IN VARCHAR2,
2138 p_batch_number_from IN VARCHAR2 DEFAULT NULL,
2139 p_batch_number_to IN VARCHAR2 DEFAULT NULL,
2140 p_batch_status IN VARCHAR2 DEFAULT NULL,
2141 p_transaction_status IN VARCHAR2 DEFAULT NULL,
2142 p_trx_type_id IN NUMBER DEFAULT NULL,
2143 p_currency_code IN VARCHAR2 DEFAULT NULL,
2144 p_acc_type IN VARCHAR2 DEFAULT NULL,
2145 p_coa_initiator IN NUMBER DEFAULT NULL,
2146 p_coa_recipient IN NUMBER DEFAULT NULL,
2147 p_ar_invoice_number IN VARCHAR2 DEFAULT NULL,
2148 p_rec_account_from IN VARCHAR2 DEFAULT NULL,
2149 p_rec_account_to IN VARCHAR2 DEFAULT NULL,
2150 p_pay_account_from IN VARCHAR2 DEFAULT NULL,
2151 p_pay_account_to IN VARCHAR2 DEFAULT NULL,
2152 p_init_d_account_from IN VARCHAR2 DEFAULT NULL,
2153 p_init_d_account_to IN VARCHAR2 DEFAULT NULL,
2154 p_recip_d_account_from IN VARCHAR2 DEFAULT NULL,
2155 p_recip_d_account_to IN VARCHAR2 DEFAULT NULL
2156 ) IS
2157 l_api_name VARCHAR2(30);
2158 l_progress VARCHAR2(3);
2159
2160 l_para_rec FUN_REPORT_PVT.accountreport_para_rec_type;
2161 l_return_status VARCHAR2(20);
2162 l_outbound_query VARCHAR2(15000) DEFAULT NULL;
2163 l_inbound_query VARCHAR2(15000) DEFAULT NULL;
2164 l_query VARCHAR2(31000);
2165 l_trxs CLOB;
2166 BEGIN
2167 l_api_name := 'create_accountreport';
2168 l_progress := '000';
2169 errbuf := NULL;
2170 retcode := 0;
2171
2172
2173 -- Save the in parameters in fnd log file
2174 FUN_UTIL.log_conc_start(g_pkg_name, l_api_name);
2175 FUN_UTIL.log_conc_stmt(
2176 g_pkg_name, l_api_name, l_progress, 'p_batch_type', p_batch_type);
2177 FUN_UTIL.log_conc_stmt(
2178 g_pkg_name, l_api_name, l_progress, 'p_initiator_from', p_initiator_from);
2179 FUN_UTIL.log_conc_stmt(
2180 g_pkg_name, l_api_name, l_progress, 'p_recipient_from', p_recipient_from);
2181 FUN_UTIL.log_conc_stmt(
2182 g_pkg_name, l_api_name, l_progress, 'p_batch_number_from', p_batch_number_from);
2183 FUN_UTIL.log_conc_stmt(
2184 g_pkg_name, l_api_name, l_progress, 'p_batch_number_to', p_batch_number_to);
2185 FUN_UTIL.log_conc_stmt(
2186 g_pkg_name, l_api_name, l_progress, 'p_gl_date_from', p_gl_date_from);
2187 FUN_UTIL.log_conc_stmt(
2188 g_pkg_name, l_api_name, l_progress, 'p_gl_date_to', p_gl_date_to);
2189 FUN_UTIL.log_conc_stmt(
2190 g_pkg_name, l_api_name, l_progress, 'p_batch_status', p_batch_status);
2191 FUN_UTIL.log_conc_stmt(
2192 g_pkg_name, l_api_name, l_progress, 'p_transaction_status', p_transaction_status);
2193 FUN_UTIL.log_conc_stmt(
2194 g_pkg_name, l_api_name, l_progress, 'p_trx_type_id', p_trx_type_id);
2195 FUN_UTIL.log_conc_stmt(
2196 g_pkg_name, l_api_name, l_progress, 'p_currency_code', p_currency_code);
2197 FUN_UTIL.log_conc_stmt(
2198 g_pkg_name, l_api_name, l_progress, 'p_acc_type', p_acc_type);
2199 FUN_UTIL.log_conc_stmt(
2200 g_pkg_name, l_api_name, l_progress, 'p_ar_invoice_number', p_ar_invoice_number);
2201
2202
2203 -- initialize accountreport parameters record
2204 l_progress := '010';
2205 l_para_rec.batch_type := p_batch_type;
2206 l_para_rec.initiator_from := p_initiator_from;
2207 l_para_rec.initiator_to := p_initiator_to;
2208 l_para_rec.transact_le := p_transact_le;
2209 l_para_rec.transact_ledger:=p_transact_ledger_id;
2210 l_para_rec.recipient_to := p_recipient_to;
2211 l_para_rec.recipient_from := p_recipient_from;
2212 l_para_rec.trading_le := p_trading_le;
2213 l_para_rec.trading_ledger:=p_trading_ledger_id;
2214 l_para_rec.batch_number_from := p_batch_number_from;
2215 l_para_rec.batch_number_to := p_batch_number_to;
2216 l_para_rec.gl_date_from := p_gl_date_from;
2217 l_para_rec.gl_date_to := p_gl_date_to;
2218 l_para_rec.batch_status := p_batch_status;
2219 l_para_rec.transaction_status := p_transaction_status;
2220 l_para_rec.trx_type_id := p_trx_type_id;
2221 l_para_rec.currency_code := p_currency_code;
2222 l_para_rec.account_type := p_acc_type;
2223 l_para_rec.rec_account_from :=p_rec_account_from;
2224 l_para_rec.rec_account_to := p_rec_account_to;
2225 l_para_rec.pay_account_from:=p_pay_account_from;
2226 l_para_rec.pay_account_to:= p_pay_account_to;
2227 l_para_rec.init_dist_acc_from:= p_init_d_account_from;
2228 l_para_rec.init_dist_acc_to:=p_init_d_account_to;
2229 l_para_rec.rec_dist_acc_from:=p_recip_d_account_from;
2230 l_para_rec.rec_dist_acc_to:=p_recip_d_account_to;
2231 l_para_rec.ar_invoice_number := substr(p_ar_invoice_number, 1, instr(p_ar_invoice_number, '~')-1);
2232 l_para_rec.coa_initiator:= p_coa_initiator;
2233 l_para_rec.coa_recipient:=p_coa_recipient;
2234
2235
2236 -- Based on batch type, build the query to get inbound, outbound or both
2237 -- transactions
2238 l_progress := '030';
2239
2240
2241
2242 IF (p_batch_type = 'BOTH') THEN
2243 FUN_UTIL.log_conc_stmt(
2244 g_pkg_name, l_api_name, l_progress, '', 'call build_account_outquery');
2245 build_account_outquery(
2246 x_return_status => l_return_status,
2247 p_para_rec => l_para_rec,
2248 x_outbound_query => l_outbound_query
2249 );
2250
2251 FUN_UTIL.log_conc_stmt(
2252 g_pkg_name, l_api_name, l_progress, '', 'call build_account_inquery');
2253 build_account_inquery(
2254 x_return_status => l_return_status,
2255 p_para_rec => l_para_rec,
2256 x_inbound_query => l_inbound_query
2257 );
2258 ELSIF (p_batch_type = 'IN') THEN
2259 FUN_UTIL.log_conc_stmt(
2260 g_pkg_name, l_api_name, l_progress, '', 'call build_account_inquery');
2261 build_account_inquery(
2262 x_return_status => l_return_status,
2263 p_para_rec => l_para_rec,
2264 x_inbound_query => l_inbound_query
2265 );
2266 ELSIF (p_batch_type = 'OUT') THEN
2267 FUN_UTIL.log_conc_stmt(
2268 g_pkg_name, l_api_name, l_progress, '', 'call build_account_outquery');
2269 build_account_outquery(
2270 x_return_status => l_return_status,
2271 p_para_rec => l_para_rec,
2272 x_outbound_query => l_outbound_query
2273 );
2274 ELSE
2275 FUN_UTIL.log_conc_stmt(
2276 g_pkg_name, l_api_name, l_progress, 'batch type is incorrect');
2277 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2278 END IF;
2279
2280 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2281 RAISE FND_API.G_EXC_ERROR;
2282 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2284 END IF;
2285
2286 if (l_outbound_query IS NULL) THEN
2287 l_query:=l_inbound_query;
2288 elsif (l_inbound_query IS NULL) THEN
2289 l_query:=l_outbound_query;
2290 else
2291 l_query:= l_outbound_query||' union '||l_inbound_query;
2292 end if;
2293
2294
2295 -- Get the XML data source
2296 l_progress := '040';
2297
2298 get_xml(
2299 x_return_status => l_return_status,
2300 p_query => l_query,
2301 p_rowset_tag => 'TRANSACTION_BATCHES',
2302 p_row_tag => 'BATCH',
2303 x_xml => l_trxs
2304 );
2305 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2306 RAISE FND_API.G_EXC_ERROR;
2307 END IF;
2308
2309
2310 -- Manupulate XML data source to XML Publisher compatiable format
2311 -- and save it to output file
2312 l_progress := '050';
2313 FUN_UTIL.log_conc_stmt(
2314 g_pkg_name, l_api_name, l_progress, '', 'call construct_account_output');
2315 construct_account_output(
2316 x_return_status => l_return_status,
2317 p_para_rec => l_para_rec,
2318 p_trxs => l_trxs
2319 );
2320
2321 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2322 RAISE FND_API.G_EXC_ERROR;
2323 END IF;
2324
2325 FUN_UTIL.log_conc_end(g_pkg_name, l_api_name);
2326
2327 EXCEPTION
2328 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2329 retcode := 2;
2330 FUN_UTIL.log_conc_err(
2331 g_pkg_name, l_api_name, l_progress, 'error in create_accountreport');
2332 WHEN FND_API.G_EXC_ERROR THEN
2333 retcode := 2;
2334 FUN_UTIL.log_conc_unexp(
2335 g_pkg_name, l_api_name, l_progress);
2336 FUN_UTIL.log_conc_err(
2337 g_pkg_name, l_api_name, l_progress, 'error in create_accountreport');
2338 WHEN OTHERS THEN
2339 retcode := 2;
2340 FUN_UTIL.log_conc_err(
2341 g_pkg_name, l_api_name, l_progress, 'error in create_accountreport');
2342 END create_accountreport;
2343
2344 END FUN_REPORT_PVT;
2345
2346