[Home] [Help]
PACKAGE BODY: APPS.JA_CN_AR_RBD_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_AR_RBD_EXPORT_PKG AS
2 --$Header: JACNRBEB.pls 120.10 2011/06/20 04:05:34 choli noship $
3 --+=======================================================================+
4 --| Copyright (c) 2010 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNRBEB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| |
13 --| This package contains the following PL/SQL tables/procedures |
14 --| to export receivable balance and details for each customer as per |
15 --| receivable accounts, such as "Account Receivable", "Receivable in |
16 --| Advance", and "Other Receivable". |
17 --| |
18 --| PROCEDURE LIST |
19 --| Add_XML_Node |
20 --| Get_Transaction_Clear_Flag |
21 --| Get_Account_Balance_Side |
22 --| Get_VAT_Invoice_Number |
23 --| Get_Balance |
24 --| Add_Receivables_Balance_Detail |
25 --| |
26 --| HISTORY |
27 --| 06-Apr-2010 Wei Huang Created |
28 --| 31-May-2010 Wei Huang Updated function Get_Transaction_Clear_Flag |
29 --| Updated procedure Add_Receivables_Balance_Detail |
30 --| |
31 --| 12-Jun-2010 Chaoqun Wu Updated for new solution described in |
32 --| bug# 9793920, new solution to populate |
33 --| remittance bill number |
34 --| 02-Aug-2010 Chaoqun Wu Updated for new solution descibed in |
35 --| bug# 9962326, new solution to populate |
36 --| applied date. |
37 --| 24-Aug-2010 Chaoqun Wu Updated for new solution described in |
38 --| bug# 10041443, to poluplate year |
39 --| beginning balance |
40 --| 17-Sep-2010 Chaoqun Wu Updated for solution changes about Due |
41 --| Date and Applied Date described in bug |
42 --| 10121399 |
43 --| 06-Jan-2011 Jianchao Chi Updated for the logic of Voucher Nuber |
44 --| and Legal Entity |
45 --| 20-Jun-2011 Choli updated, fix bug 12635156 |
46 --+======================================================================*/
47 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_AR_RBD_EXPORT_PKG';
48 TYPE ACCOUNT_NUMBER_TBL IS VARRAY(100) OF VARCHAR2(50); --Fixing bug 10041443
49 --==========================================================================
50 -- PROCEDURE NAME:
51 --
52 -- Add_XML_Node Public
53 --
54 -- DESCRIPTION:
55 --
56 -- This procedure is to add receivable balance and details information
57 -- into xml nodes.
58 --
59 -- PARAMETERS:
60 -- In: pv_customer_number customer number
61 -- pv_account_number account number
62 -- pv_journal_created_date journal created date
63 -- pv_journal_date journal date
64 -- pv_accounting_year accounting year
65 -- pv_accounting_period_number accounting period number
66 -- pv_journal_category_number journal category number
67 -- pv_journal_number journal number
68 -- pv_functional_currency functional currency
69 -- pn_exchange_rate exchage rate
70 -- pv_balance_side balance side
71 -- pn_functional_balance functional balance
72 -- pn_entered_balance entered balance
73 -- pn_functional_amount functional amount
74 -- pv_entered_currency entered currency
75 -- pv_entered_amount entered amount
76 -- pv_description description
77 -- pv_due_date due date
78 -- pv_applied_journal_number applied journal number
79 -- pv_applied_date applied date
80 -- pv_document_type_number document type number
81 -- pv_transaction_type_number transaction type number
82 -- pv_transaction_number transaction number
83 -- pv_invoice_number invoice number
84 -- pv_contract_number contract number
85 -- pv_project_number project number
86 -- pv_settlement_method_number settlement method number
87 -- pv_payment_date payment date
88 -- pv_clear_flag clear flag
89 -- pv_remittance_bill_number remittance bill number
90 --
91 -- Out:
92 --
93 -- DESIGN REFERENCES:
94 --
95 --
96 -- CHANGE HISTORY:
97 -- 06-Apr-2010 Wei Huang Created
98 --
99 --==========================================================================
100 PROCEDURE Add_XML_Node
101 (pv_customer_number IN VARCHAR2
102 ,pv_account_number IN VARCHAR2
103 ,pv_journal_created_date IN VARCHAR2
104 ,pv_journal_date IN VARCHAR2
105 ,pv_accounting_year IN VARCHAR2
106 ,pv_accounting_period_number IN VARCHAR2
107 ,pv_journal_category_number IN VARCHAR2
108 ,pv_journal_number IN VARCHAR2
109 ,pv_functional_currency IN VARCHAR2
110 ,pn_exchange_rate IN NUMBER
111 ,pv_balance_side IN VARCHAR2
112 ,pn_functional_balance IN NUMBER
113 ,pn_entered_balance IN NUMBER
114 ,pn_functional_amount IN NUMBER
115 ,pv_entered_currency IN VARCHAR2
116 ,pn_entered_amount IN NUMBER
117 ,pv_description IN VARCHAR2
118 ,pv_due_date IN VARCHAR2
119 ,pv_applied_journal_number IN VARCHAR2
120 ,pv_applied_date IN VARCHAR2
121 ,pv_document_type_number IN VARCHAR2
122 ,pv_transaction_type_number IN VARCHAR2
123 ,pv_transaction_number IN VARCHAR2
124 ,pv_invoice_number IN VARCHAR2
125 ,pv_contract_number IN VARCHAR2
126 ,pv_project_number IN VARCHAR2
127 ,pv_settlement_method_number IN VARCHAR2
128 ,pv_payment_date IN VARCHAR2
129 ,pv_clear_flag IN VARCHAR2
130 ,pv_remittance_bill_number IN VARCHAR2
131 )
132 IS
133 lv_procedure_name VARCHAR2(40) := 'Add_XML_Node';
134 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
135 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
136
137 BEGIN
138 --logging for debug
139 IF (ln_proc_level >= ln_dbg_level)
140 THEN
141 FND_LOG.STRING(ln_proc_level,
142 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
143 '.begin',
144 'Enter procedure');
145 END IF; --ln_proc_level>=ln_dbg_level
146
147 --Add XML Data
148 Ja_Cn_Utility.Add_Sub_Root_Node('RECEIVABLE_DETAIL_REPORT',
149 Ja_Cn_Utility.GV_TAG_TYPE_START,
150 Ja_Cn_Utility.GV_MODULE_APAR);
151 Ja_Cn_Utility.Add_Child_Node('CUSTOMER_NUMBER',
152 pv_customer_number,
153 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
154 Ja_Cn_Utility.GV_REQUIRED_YES,
155 Ja_Cn_Utility.GV_MODULE_APAR);
156 Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER',
157 pv_account_number,
158 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
159 Ja_Cn_Utility.GV_REQUIRED_YES,
160 Ja_Cn_Utility.GV_MODULE_APAR);
161 Ja_Cn_Utility.Add_Fixed_Child_Node('JOURNAL_CREATED_DATE',
162 pv_journal_created_date,
163 8,
164 Ja_Cn_Utility.GV_REQUIRED_YES,
165 Ja_Cn_Utility.GV_MODULE_APAR);
166 Ja_Cn_Utility.Add_Fixed_Child_Node('JOURNAL_DATE',
167 pv_journal_date,
168 8,
169 Ja_Cn_Utility.GV_REQUIRED_YES,
170 Ja_Cn_Utility.GV_MODULE_APAR);
171 Ja_Cn_Utility.Add_Fixed_Child_Node('ACCOUNTING_YEAR',
172 pv_accounting_year,
173 4,
174 Ja_Cn_Utility.GV_REQUIRED_YES,
175 Ja_Cn_Utility.GV_MODULE_APAR);
176 Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER',
177 pv_accounting_period_number,
178 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
179 Ja_Cn_Utility.GV_REQUIRED_YES,
180 Ja_Cn_Utility.GV_MODULE_APAR);
181 Ja_Cn_Utility.Add_Child_Node('JOURNAL_CATEGORY_NUMBER',
182 pv_journal_category_number,
183 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
184 Ja_Cn_Utility.GV_REQUIRED_YES,
185 Ja_Cn_Utility.GV_MODULE_APAR);
186 Ja_Cn_Utility.Add_Child_Node('JOURNAL_NUMBER',
187 pv_journal_number,
188 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
189 Ja_Cn_Utility.GV_REQUIRED_YES,
190 Ja_Cn_Utility.GV_MODULE_APAR);
191 Ja_Cn_Utility.Add_Child_Node('FUNCTIONAL_CURRENCY',
192 pv_functional_currency,
193 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
194 Ja_Cn_Utility.GV_REQUIRED_YES,
195 Ja_Cn_Utility.GV_MODULE_APAR);
196 Ja_Cn_Utility.Add_Child_Node('EXCHANGE_RATE',
197 pn_exchange_rate,
198 Ja_Cn_Utility.GV_TYPE_NUMBER,
199 Ja_Cn_Utility.GV_REQUIRED_YES,
200 Ja_Cn_Utility.GV_MODULE_APAR);
201 Ja_Cn_Utility.Add_Child_Node('BALANCE_SIDE',
202 pv_balance_side,
203 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
204 Ja_Cn_Utility.GV_REQUIRED_YES,
205 Ja_Cn_Utility.GV_MODULE_APAR);
206 Ja_Cn_Utility.Add_Child_Node('FUNCTIONAL_BALANCE',
207 pn_functional_balance,
208 Ja_Cn_Utility.GV_TYPE_NUMBER,
209 Ja_Cn_Utility.GV_REQUIRED_YES,
210 Ja_Cn_Utility.GV_MODULE_APAR);
211 Ja_Cn_Utility.Add_Child_Node('ENTERED_BALANCE',
212 pn_entered_balance,
213 Ja_Cn_Utility.GV_TYPE_NUMBER,
214 Ja_Cn_Utility.GV_REQUIRED_YES,
215 Ja_Cn_Utility.GV_MODULE_APAR);
216 Ja_Cn_Utility.Add_Child_Node('FUNCTIONAL_AMOUNT',
217 pn_functional_amount,
218 Ja_Cn_Utility.GV_TYPE_NUMBER,
219 Ja_Cn_Utility.GV_REQUIRED_YES,
220 Ja_Cn_Utility.GV_MODULE_APAR);
221 Ja_Cn_Utility.Add_Child_Node('ENTERED_CURRENCY',
222 pv_entered_currency,
223 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
224 Ja_Cn_Utility.GV_REQUIRED_YES,
225 Ja_Cn_Utility.GV_MODULE_APAR);
226 Ja_Cn_Utility.Add_Child_Node('ENTERED_AMOUNT',
227 pn_entered_amount,
228 Ja_Cn_Utility.GV_TYPE_NUMBER,
229 Ja_Cn_Utility.GV_REQUIRED_YES,
230 Ja_Cn_Utility.GV_MODULE_APAR);
231 Ja_Cn_Utility.Add_Child_Node('DESCRIPTION',
232 pv_description,
233 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
234 Ja_Cn_Utility.GV_REQUIRED_NO,
235 Ja_Cn_Utility.GV_MODULE_APAR);
236 Ja_Cn_Utility.Add_Fixed_Child_Node('DUE_DATE',
237 pv_due_date,
238 8,
239 Ja_Cn_Utility.GV_REQUIRED_NO,
240 Ja_Cn_Utility.GV_MODULE_APAR);
241 Ja_Cn_Utility.Add_Child_Node('APPLIED_JOURNAL_NUMBER',
242 pv_applied_journal_number,
243 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
244 Ja_Cn_Utility.GV_REQUIRED_NO,
245 Ja_Cn_Utility.GV_MODULE_APAR);
246 Ja_Cn_Utility.Add_Fixed_Child_Node('APPLIED_DATE',
247 pv_applied_date,
248 8,
249 Ja_Cn_Utility.GV_REQUIRED_NO,
250 Ja_Cn_Utility.GV_MODULE_APAR);
251 Ja_Cn_Utility.Add_Child_Node('DOCUMENT_TYPE_NUMBER',
252 pv_document_type_number,
253 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
254 Ja_Cn_Utility.GV_REQUIRED_NO,
255 Ja_Cn_Utility.GV_MODULE_APAR);
256 Ja_Cn_Utility.Add_Child_Node('TRANSACTION_TYPE_NUMBER',
257 pv_transaction_type_number,
258 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
259 Ja_Cn_Utility.GV_REQUIRED_YES,
260 Ja_Cn_Utility.GV_MODULE_APAR);
261 Ja_Cn_Utility.Add_Child_Node('TRANSACTION_NUMBER',
262 pv_transaction_number,
263 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
264 Ja_Cn_Utility.GV_REQUIRED_NO,
265 Ja_Cn_Utility.GV_MODULE_APAR);
266 Ja_Cn_Utility.Add_Child_Node('INVOICE_NUMBER',
267 pv_invoice_number,
268 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
269 Ja_Cn_Utility.GV_REQUIRED_NO,
270 Ja_Cn_Utility.GV_MODULE_APAR);
271 Ja_Cn_Utility.Add_Child_Node('CONTRACT_NUMBER',
272 pv_contract_number,
273 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
274 Ja_Cn_Utility.GV_REQUIRED_NO,
275 Ja_Cn_Utility.GV_MODULE_APAR);
276 Ja_Cn_Utility.Add_Child_Node('PROJECT_NUMBER',
277 pv_project_number,
278 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
279 Ja_Cn_Utility.GV_REQUIRED_NO,
280 Ja_Cn_Utility.GV_MODULE_APAR);
281 Ja_Cn_Utility.Add_Child_Node('SETTLEMENT_METHOD_NUMBER',
282 pv_settlement_method_number,
283 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
284 Ja_Cn_Utility.GV_REQUIRED_NO,
285 Ja_Cn_Utility.GV_MODULE_APAR);
286 Ja_Cn_Utility.Add_Fixed_Child_Node('PAYMENT_DATE',
287 pv_payment_date,
288 8,
289 Ja_Cn_Utility.GV_REQUIRED_NO,
290 Ja_Cn_Utility.GV_MODULE_APAR);
291 Ja_Cn_Utility.Add_Fixed_Child_Node('CLEAR_FLAG',
292 pv_clear_flag,
293 1,
294 Ja_Cn_Utility.GV_REQUIRED_YES,
295 Ja_Cn_Utility.GV_MODULE_APAR);
296 Ja_Cn_Utility.Add_Child_Node('REMITTANCE_BILL_NUMBER',
297 pv_remittance_bill_number,
298 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
299 Ja_Cn_Utility.GV_REQUIRED_NO,
300 Ja_Cn_Utility.GV_MODULE_APAR);
301 Ja_Cn_Utility.Add_Sub_Root_Node('RECEIVABLE_DETAIL_REPORT',
302 Ja_Cn_Utility.GV_TAG_TYPE_END,
303 Ja_Cn_Utility.GV_MODULE_APAR);
304
305 --logging for debug
306 IF (ln_proc_level >= ln_dbg_level)
307 THEN
308 FND_LOG.STRING(ln_proc_level,
309 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
310 'Exit procedure');
311 END IF; -- (ln_proc_level>=ln_dbg_level)
312
313 END Add_XML_Node;
314
315 --==========================================================================
316 -- PROCEDURE NAME:
317 --
318 -- Add_XML_Node Public
319 --
320 -- DESCRIPTION:
321 --
322 -- This procedure is to add receivable balance and details information
323 -- into xml nodes.
324 --
325 -- PARAMETERS:
326 -- In: pv_customer_number customer number
327 -- pv_account_number account number
328 -- pv_journal_created_date journal created date
329 -- pv_journal_date journal date
330 -- pv_accounting_year accounting year
331 -- pv_accounting_period_number accounting period number
332 -- pv_journal_category_number journal category number
333 -- pv_journal_number journal number
334 -- pv_functional_currency functional currency
335 -- pn_exchange_rate exchage rate
336 -- pv_balance_side balance side
337 -- pn_functional_balance functional balance
338 -- pn_entered_balance entered balance
339 -- pn_functional_amount functional amount
340 -- pv_entered_currency entered currency
341 -- pv_entered_amount entered amount
342 -- pv_description description
343 -- pv_due_date due date
344 -- pv_applied_journal_number applied journal number
345 -- pv_applied_date applied date
346 -- pv_document_type_number document type number
347 -- pv_transaction_type_number transaction type number
348 -- pv_transaction_number transaction number
349 -- pv_invoice_number invoice number
350 -- pv_contract_number contract number
351 -- pv_project_number project number
352 -- pv_settlement_method_number settlement method number
353 -- pv_payment_date payment date
354 -- pv_clear_flag clear flag
355 -- pv_remittance_bill_number remittance bill number
356 --
357 -- Out:
358 --
359 -- DESIGN REFERENCES:
360 --
361 --
362 -- CHANGE HISTORY:
363 -- 24-Aug-2010 Chaoqun Wu created for new solution described in bug 10041443
364 --==========================================================================
365 PROCEDURE Add_YBB_XML_Node
366 (pv_customer_number IN VARCHAR2
367 ,pv_account_number IN VARCHAR2
368 ,pv_journal_created_date IN VARCHAR2
369 ,pv_journal_date IN VARCHAR2
370 ,pv_accounting_year IN VARCHAR2
371 ,pv_accounting_period_number IN VARCHAR2
372 ,pv_journal_category_number IN VARCHAR2
373 ,pv_journal_number IN VARCHAR2
374 ,pv_functional_currency IN VARCHAR2
375 ,pn_exchange_rate IN NUMBER
376 ,pv_balance_side IN VARCHAR2
377 ,pn_functional_balance IN NUMBER
378 ,pn_entered_balance IN NUMBER
379 ,pn_functional_amount IN NUMBER
380 ,pv_entered_currency IN VARCHAR2
381 ,pn_entered_amount IN NUMBER
382 ,pv_description IN VARCHAR2
383 ,pv_due_date IN VARCHAR2
384 ,pv_applied_journal_number IN VARCHAR2
385 ,pv_applied_date IN VARCHAR2
386 ,pv_document_type_number IN VARCHAR2
387 ,pv_transaction_type_number IN VARCHAR2
388 ,pv_transaction_number IN VARCHAR2
389 ,pv_invoice_number IN VARCHAR2
390 ,pv_contract_number IN VARCHAR2
391 ,pv_project_number IN VARCHAR2
392 ,pv_settlement_method_number IN VARCHAR2
393 ,pv_payment_date IN VARCHAR2
394 ,pv_clear_flag IN VARCHAR2
395 ,pv_remittance_bill_number IN VARCHAR2
396 )
397 IS
398 lv_procedure_name VARCHAR2(40) := 'Add_XML_Node';
399 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
400 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
401
402 BEGIN
403 --logging for debug
404 IF (ln_proc_level >= ln_dbg_level)
405 THEN
406 FND_LOG.STRING(ln_proc_level,
407 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
408 '.begin',
409 'Enter procedure');
410 END IF; --ln_proc_level>=ln_dbg_level
411
412 --Add XML Data
413 Ja_Cn_Utility.Add_Sub_Root_Node('RECEIVABLE_DETAIL_REPORT',
414 Ja_Cn_Utility.GV_TAG_TYPE_START,
415 Ja_Cn_Utility.GV_MODULE_APAR);
416 Ja_Cn_Utility.Add_Child_Node('CUSTOMER_NUMBER',
417 pv_customer_number,
418 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
419 Ja_Cn_Utility.GV_REQUIRED_YES,
420 Ja_Cn_Utility.GV_MODULE_APAR);
421 Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER',
422 pv_account_number,
423 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
424 Ja_Cn_Utility.GV_REQUIRED_YES,
425 Ja_Cn_Utility.GV_MODULE_APAR);
426 Ja_Cn_Utility.Add_Child_Node('JOURNAL_CREATED_DATE',
427 pv_journal_created_date,
428 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
429 Ja_Cn_Utility.GV_REQUIRED_YES,
430 Ja_Cn_Utility.GV_MODULE_APAR);
431 Ja_Cn_Utility.Add_Child_Node('JOURNAL_DATE',
432 pv_journal_date,
433 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
434 Ja_Cn_Utility.GV_REQUIRED_YES,
435 Ja_Cn_Utility.GV_MODULE_APAR);
436 Ja_Cn_Utility.Add_Fixed_Child_Node('ACCOUNTING_YEAR',
437 pv_accounting_year,
438 4,
439 Ja_Cn_Utility.GV_REQUIRED_YES,
440 Ja_Cn_Utility.GV_MODULE_APAR);
441 Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER',
442 pv_accounting_period_number,
443 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
444 Ja_Cn_Utility.GV_REQUIRED_YES,
445 Ja_Cn_Utility.GV_MODULE_APAR);
446 Ja_Cn_Utility.Add_Child_Node('JOURNAL_CATEGORY_NUMBER',
447 pv_journal_category_number,
448 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
449 Ja_Cn_Utility.GV_REQUIRED_YES,
450 Ja_Cn_Utility.GV_MODULE_APAR);
451 Ja_Cn_Utility.Add_Child_Node('JOURNAL_NUMBER',
452 pv_journal_number,
453 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
454 Ja_Cn_Utility.GV_REQUIRED_YES,
455 Ja_Cn_Utility.GV_MODULE_APAR);
456 Ja_Cn_Utility.Add_Child_Node('FUNCTIONAL_CURRENCY',
457 pv_functional_currency,
458 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
459 Ja_Cn_Utility.GV_REQUIRED_YES,
460 Ja_Cn_Utility.GV_MODULE_APAR);
461 Ja_Cn_Utility.Add_Child_Node('EXCHANGE_RATE',
462 pn_exchange_rate,
463 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
464 Ja_Cn_Utility.GV_REQUIRED_YES,
465 Ja_Cn_Utility.GV_MODULE_APAR);
466 Ja_Cn_Utility.Add_Child_Node('BALANCE_SIDE',
467 pv_balance_side,
468 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
469 Ja_Cn_Utility.GV_REQUIRED_YES,
470 Ja_Cn_Utility.GV_MODULE_APAR);
471 Ja_Cn_Utility.Add_Child_Node('FUNCTIONAL_BALANCE',
472 pn_functional_balance,
473 Ja_Cn_Utility.GV_TYPE_NUMBER,
474 Ja_Cn_Utility.GV_REQUIRED_NO,
475 Ja_Cn_Utility.GV_MODULE_APAR);
476 Ja_Cn_Utility.Add_Child_Node('ENTERED_BALANCE',
477 pn_entered_balance,
478 Ja_Cn_Utility.GV_TYPE_NUMBER,
479 Ja_Cn_Utility.GV_REQUIRED_NO,
480 Ja_Cn_Utility.GV_MODULE_APAR);
481 Ja_Cn_Utility.Add_Child_Node('FUNCTIONAL_AMOUNT',
482 pn_functional_amount,
483 Ja_Cn_Utility.GV_TYPE_NUMBER,
484 Ja_Cn_Utility.GV_REQUIRED_YES,
485 Ja_Cn_Utility.GV_MODULE_APAR);
486 Ja_Cn_Utility.Add_Child_Node('ENTERED_CURRENCY',
487 pv_entered_currency,
488 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
489 Ja_Cn_Utility.GV_REQUIRED_YES,
490 Ja_Cn_Utility.GV_MODULE_APAR);
491 Ja_Cn_Utility.Add_Child_Node('ENTERED_AMOUNT',
492 pn_entered_amount,
493 Ja_Cn_Utility.GV_TYPE_NUMBER,
494 Ja_Cn_Utility.GV_REQUIRED_YES,
495 Ja_Cn_Utility.GV_MODULE_APAR);
496 Ja_Cn_Utility.Add_Child_Node('DESCRIPTION',
497 pv_description,
498 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
499 Ja_Cn_Utility.GV_REQUIRED_NO,
500 Ja_Cn_Utility.GV_MODULE_APAR);
501 Ja_Cn_Utility.Add_Fixed_Child_Node('DUE_DATE',
502 pv_due_date,
503 8,
504 Ja_Cn_Utility.GV_REQUIRED_NO,
505 Ja_Cn_Utility.GV_MODULE_APAR);
506 Ja_Cn_Utility.Add_Child_Node('APPLIED_JOURNAL_NUMBER',
507 pv_applied_journal_number,
508 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
509 Ja_Cn_Utility.GV_REQUIRED_NO,
510 Ja_Cn_Utility.GV_MODULE_APAR);
511 Ja_Cn_Utility.Add_Fixed_Child_Node('APPLIED_DATE',
512 pv_applied_date,
513 8,
514 Ja_Cn_Utility.GV_REQUIRED_NO,
515 Ja_Cn_Utility.GV_MODULE_APAR);
516 Ja_Cn_Utility.Add_Child_Node('DOCUMENT_TYPE_NUMBER',
517 pv_document_type_number,
518 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
519 Ja_Cn_Utility.GV_REQUIRED_NO,
520 Ja_Cn_Utility.GV_MODULE_APAR);
521 Ja_Cn_Utility.Add_Child_Node('TRANSACTION_TYPE_NUMBER',
522 pv_transaction_type_number,
523 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
524 Ja_Cn_Utility.GV_REQUIRED_YES,
525 Ja_Cn_Utility.GV_MODULE_APAR);
526 Ja_Cn_Utility.Add_Child_Node('TRANSACTION_NUMBER',
527 pv_transaction_number,
528 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
529 Ja_Cn_Utility.GV_REQUIRED_NO,
530 Ja_Cn_Utility.GV_MODULE_APAR);
531 Ja_Cn_Utility.Add_Child_Node('INVOICE_NUMBER',
532 pv_invoice_number,
533 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
534 Ja_Cn_Utility.GV_REQUIRED_NO,
535 Ja_Cn_Utility.GV_MODULE_APAR);
536 Ja_Cn_Utility.Add_Child_Node('CONTRACT_NUMBER',
537 pv_contract_number,
538 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
539 Ja_Cn_Utility.GV_REQUIRED_NO,
540 Ja_Cn_Utility.GV_MODULE_APAR);
541 Ja_Cn_Utility.Add_Child_Node('PROJECT_NUMBER',
542 pv_project_number,
543 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
544 Ja_Cn_Utility.GV_REQUIRED_NO,
545 Ja_Cn_Utility.GV_MODULE_APAR);
546 Ja_Cn_Utility.Add_Child_Node('SETTLEMENT_METHOD_NUMBER',
547 pv_settlement_method_number,
548 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
549 Ja_Cn_Utility.GV_REQUIRED_NO,
550 Ja_Cn_Utility.GV_MODULE_APAR);
551 Ja_Cn_Utility.Add_Fixed_Child_Node('PAYMENT_DATE',
552 pv_payment_date,
553 8,
554 Ja_Cn_Utility.GV_REQUIRED_NO,
555 Ja_Cn_Utility.GV_MODULE_APAR);
556 Ja_Cn_Utility.Add_Fixed_Child_Node('CLEAR_FLAG',
557 pv_clear_flag,
558 1,
559 Ja_Cn_Utility.GV_REQUIRED_YES,
560 Ja_Cn_Utility.GV_MODULE_APAR);
561 Ja_Cn_Utility.Add_Child_Node('REMITTANCE_BILL_NUMBER',
562 pv_remittance_bill_number,
563 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
564 Ja_Cn_Utility.GV_REQUIRED_NO,
565 Ja_Cn_Utility.GV_MODULE_APAR);
566 Ja_Cn_Utility.Add_Sub_Root_Node('RECEIVABLE_DETAIL_REPORT',
567 Ja_Cn_Utility.GV_TAG_TYPE_END,
568 Ja_Cn_Utility.GV_MODULE_APAR);
569
570 --logging for debug
571 IF (ln_proc_level >= ln_dbg_level)
572 THEN
573 FND_LOG.STRING(ln_proc_level,
574 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
575 'Exit procedure');
576 END IF; -- (ln_proc_level>=ln_dbg_level)
577
578 END Add_YBB_XML_Node;
579
580 --==========================================================================
581 -- PROCEDURE NAME:
582 --
583 -- Get_Transaction_Clear_Flag Public
584 --
585 -- DESCRIPTION:
586 --
587 -- This function is to get the clear flag of a Transaction in a
588 -- certain accounting period. '1' menas that one transaction has been
589 -- fully paid by customer by the accounting period, '0' means that the
590 -- Transaction is unclear.
591 --
592 -- PARAMETERS:
593 -- In: pn_ledger_id Ledger ID
594 -- pd_period_end_date Accounting period end date
595 -- pn_customer_trx_id Transaction ID
596 --
597 -- Out:
598 --
599 -- DESIGN REFERENCES:
600 --
601 --
602 -- CHANGE HISTORY:
603 -- 06-Apr-2010 Wei Huang Created
604 -- 28-May-2010 Wei Huang Added condition of comparing Accounting_Date
605 -- to cursor trx_amount_applied_cur
606 -- 20-Jun-2011 Choli updated, fix bug 12635156
607 --
608 --===========================================================================
609 FUNCTION Get_Transaction_Clear_Flag
610 (pn_ledger_id IN NUMBER
611 ,pd_period_end_date IN DATE
612 ,pn_customer_trx_id IN NUMBER
613 ) RETURN VARCHAR2
614 IS
615 lv_procedure_name VARCHAR2(40) := 'Get_Transaction_Clear_Flag';
616 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
617 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
618
619 ln_trx_entered_amount NUMBER := 0;
620 ln_trx_functional_amount NUMBER := 0;
621 ln_trx_e_amount_applied NUMBER := -1;
622 ln_trx_f_amount_applied NUMBER := -1;
623 lv_clear_flag VARCHAR2(1);
624
625 --Cursor for getting the Transaction Amount
626 CURSOR trx_amount_cur
627 IS
628 SELECT SUM(NVL(Ael.Accounted_Dr,0) + NVL(-1 * Ael.Accounted_Cr,0)) Functional_Amount,
629 SUM(NVL(Ael.Entered_Dr,0) + NVL(-1 * Ael.Entered_Cr, 0)) Entered_Amount
630 FROM RA_CUSTOMER_TRX_ALL Rct,
631 RA_CUST_TRX_LINE_GL_DIST_ALL Rctlgd,
632 Xla_Transaction_Entities Ent,
633 Xla_Ae_Headers Aeh,
634 Xla_Ae_Lines Ael,
635 Gl_Import_References Gir,
636 Gl_Je_Headers Jeh
637 WHERE Rct.Customer_Trx_Id = pn_customer_trx_id -- variable: pn_customer_trx_id
638 AND Rct.CUSTOMER_TRX_ID = Rctlgd.CUSTOMER_TRX_ID
639 AND Rctlgd.ACCOUNT_CLASS = 'REC'
640 AND Rctlgd.LATEST_REC_FLAG = 'Y'
641 --SLA condition
642 AND Ent.Application_Id = 222
643 AND Rct.Customer_Trx_Id = Ent.Source_Id_Int_1
644 AND Ent.Entity_Code = 'TRANSACTIONS'
645 AND Ent.Entity_Id = Aeh.Entity_Id
646 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
647 AND Ael.Accounting_Class_Code = 'RECEIVABLE'
648 --GL condition
649 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
650 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
651 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
652 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
653 AND Jeh.Status = 'P' --only collecting posted transaction
654 --Period condition
655 AND Rctlgd.Gl_Date <= pd_period_end_date; --parameter:pd_period_end_date
656
657 --Cursor for gettting Amount applied to Transaction from Receipt, Credit Memo, Adjustment
658 CURSOR trx_amount_applied_cur
659 IS
660 SELECT SUM(NVL(Accounted_Dr,0) + NVL(-1 * Accounted_Cr,0)) Functional_Amount,
661 SUM(NVL(Entered_Dr,0) + NVL(-1 * Entered_Cr, 0)) Entered_Amount
662 FROM (
663 --Receipt
664 /*SELECT Jeh.Je_Header_Id Je_Header_Id,
665 Aeh.Ae_Header_Id Ae_Header_Id,
666 Ael.Accounted_Dr Accounted_Dr,
667 Ael.Accounted_Cr Accounted_Cr,
668 Ael.Entered_Dr Entered_Dr,
669 Ael.Entered_Cr Entered_Cr
670 FROM AR_CASH_RECEIPTS_ALL Acr,
671 AR_RECEIVABLE_APPLICATIONS_ALL App,
672 Xla_Transaction_Entities Ent,
673 Xla_Ae_Headers Aeh,
674 Xla_Ae_Lines Ael,
675 Gl_Je_Headers Jeh,
676 Gl_Import_References Gir
677 WHERE Acr.Cash_Receipt_Id = App.Cash_Receipt_Id
678 AND App.Applied_Customer_Trx_Id = pn_customer_trx_id -- variable: pn_customer_trx_id
679 --SLA condition
680 AND Ent.Application_Id = 222
681 AND Acr.Cash_Receipt_Id = Ent.Source_Id_Int_1
682 AND Ent.Entity_Code = 'RECEIPTS'
683 AND Ent.Entity_Id = Aeh.Entity_Id
684 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
685 AND Ael.Accounting_Class_Code = 'RECEIVABLE'
686 --GL condition
687 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
688 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
689 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
690 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
691 AND Jeh.Status = 'P' --only collecting posted Receipt
692 --Period condition
693 AND App.Gl_Date <= pd_period_end_date --parameter:pd_period_end_date
694 AND Aeh.Accounting_Date = App.Gl_Date*/
695 SELECT Jeh.Je_Header_Id Je_Header_Id,
696 Ael.Ae_Header_Id Ae_Header_Id,
697 Ael.Accounted_Dr Accounted_Dr,
698 Ael.Accounted_Cr Accounted_Cr,
699 Ael.Entered_Dr Entered_Dr,
700 Ael.Entered_Cr Entered_Cr
701 FROM Xla_Ae_Lines Ael,
702 Gl_Import_References Gir,
703 Gl_Je_Headers Jeh
704 WHERE Ael.Accounting_Class_Code = 'RECEIVABLE'
705 AND Ael.Application_Id = 222
706 AND (Ael.Ae_Header_Id,Ael.Ae_Line_Num) IN
707 (SELECT Xdl.Ae_Header_Id,
708 Xdl.Ae_Line_Num
709 FROM AR_RECEIVABLE_APPLICATIONS_ALL App,
710 Xla_Ae_Headers Aeh,
711 Xla_Distribution_Links Xdl,
712 Ar_Distributions_All Ada
713 WHERE App.event_id = Aeh.event_id
714 AND Aeh.ae_header_id = Xdl.ae_header_id
715 AND Xdl.event_id = Aeh.event_id
716 --AND Xdl.source_distribution_id_num_1 = Ada.line_id
717 AND Xdl.Applied_To_Source_Id_Num_1 = App.applied_customer_trx_id
718 AND Ada.source_id = App.receivable_application_id
719 AND Ada.source_table = 'RA'
720 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
721 AND App.applied_customer_trx_id = pn_customer_trx_id
722 AND App.Gl_Date <= pd_period_end_date --parameter:pd_period_end_date
723 -- Choli updated, fix bug 12635156
724 AND (SELECT status
725 FROM AR_CASH_RECEIPT_HISTORY_ALL
726 WHERE cash_receipt_id = App.cash_receipt_id
727 AND cash_receipt_history_id =
728 (SELECT max(cash_receipt_history_id)
729 FROM AR_CASH_RECEIPT_HISTORY_ALL
730 WHERE cash_receipt_id = App.cash_receipt_id
731 AND gl_date < pd_period_end_date)) = 'CLEARED'
732 ------------------------------------
733 )
734 --GL condition
735 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
736 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
737 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
738 AND Jeh.Status = 'P' --only collecting posted Receipt
739 ---CreditMemo
740 UNION ALL
741 SELECT Jeh.Je_Header_Id Je_Header_Id,
742 Aeh.Ae_Header_Id Ae_Header_Id,
743 Ael.Accounted_Dr Accounted_Dr,
744 Ael.Accounted_Cr Accounted_Cr,
745 Ael.Entered_Dr Entered_Dr,
746 Ael.Entered_Cr Entered_Cr
747 FROM RA_CUSTOMER_TRX_ALL Rct,
748 RA_CUST_TRX_LINE_GL_DIST_ALL Rctlgd,
749 Xla_Transaction_Entities Ent,
750 Xla_Ae_Headers Aeh,
751 Xla_Ae_Lines Ael,
752 Gl_Import_References Gir,
753 Gl_Je_Headers Jeh
754 WHERE Rct.Previous_Customer_Trx_Id = pn_customer_trx_id -- variable: pn_customer_trx_id
755 AND Rct.CUSTOMER_TRX_ID = Rctlgd.CUSTOMER_TRX_ID
756 AND Rctlgd.ACCOUNT_CLASS = 'REC'
757 AND Rctlgd.LATEST_REC_FLAG = 'Y'
758 --SLA condition
759 AND Ent.Application_Id = 222
760 AND Rct.Customer_Trx_Id = Ent.Source_Id_Int_1
761 AND Ent.Entity_Code = 'TRANSACTIONS'
762 AND Ent.Entity_Id = Aeh.Entity_Id
763 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
764 AND Ael.Accounting_Class_Code = 'RECEIVABLE'
765 --GL condition
766 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
767 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
768 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
769 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
770 AND Jeh.Status = 'P' --only collecting posted CreditMemo
771 --Period condition
772 AND Rctlgd.Gl_Date <= pd_period_end_date --parameter:pd_period_end_date
773 AND Aeh.Accounting_Date = Rctlgd.Gl_Date
774 --Adjustment
775 UNION ALL
776 SELECT Jeh.Je_Header_Id Je_Header_Id,
777 Aeh.Ae_Header_Id Ae_Header_Id,
778 Ael.Accounted_Dr Accounted_Dr,
779 Ael.Accounted_Cr Accounted_Cr,
780 Ael.Entered_Dr Entered_Dr,
781 Ael.Entered_Cr Entered_Cr
782 FROM AR_ADJUSTMENTS_ALL Adj,
783 Xla_Transaction_Entities Ent,
784 Xla_Ae_Headers Aeh,
785 Xla_Ae_Lines Ael,
786 Gl_Import_References Gir,
787 Gl_Je_Headers Jeh
788 WHERE Adj.Customer_Trx_Id = pn_customer_trx_id -- variable: pn_customer_trx_id
789 --SLA condition
790 AND Ent.Application_Id = 222
791 AND Adj.Adjustment_Id = Ent.Source_Id_Int_1
792 AND Ent.Entity_Code = 'ADJUSTMENTS'
793 AND Ent.Entity_Id = Aeh.Entity_Id
794 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
795 AND Ael.Accounting_Class_Code = 'RECEIVABLE'
796 --GL condition
797 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
798 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
799 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
800 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
801 AND Jeh.Status = 'P' --only collecting posted Adjustment
802 --Period condition
803 AND Adj.Gl_Date <= pd_period_end_date --parameter:pd_period_end_date
804 AND Aeh.Accounting_Date = Adj.Gl_Date
805 );
806
807 BEGIN
808 --logging for debug
809 IF (ln_proc_level >= ln_dbg_level)
810 THEN
811 FND_LOG.STRING(ln_proc_level,
812 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
813 '.begin',
814 'Enter procedure');
815 END IF; --ln_proc_level>=ln_dbg_level
816
817 lv_clear_flag := '0';
818
819 --1. Get Transaction Amount
820 FOR v_trx_amount_row IN trx_amount_cur
821 LOOP
822 ln_trx_entered_amount := v_trx_amount_row.entered_amount;
823 ln_trx_functional_amount := v_trx_amount_row.functional_amount;
824 EXIT;
825 END LOOP;
826
827 --2. Get Amount applied to Transaction from Receipt, Credit Memo, Adjustment
828 FOR v_trx_amount_applied_row IN trx_amount_applied_cur
829 LOOP
830 ln_trx_e_amount_applied := v_trx_amount_applied_row.entered_amount;
831 ln_trx_f_amount_applied := v_trx_amount_applied_row.functional_amount;
832 EXIT;
833 END LOOP;
834
835 --3. Get the Clear Flag
836 IF ln_trx_entered_amount + ln_trx_e_amount_applied = 0 OR
837 ln_trx_functional_amount + ln_trx_f_amount_applied = 0
838 THEN
839 lv_clear_flag := '1';
840 END IF;
841
842 --logging for debug
843 IF (ln_proc_level >= ln_dbg_level)
844 THEN
845 FND_LOG.STRING(ln_proc_level,
846 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
847 'Exit procedure');
848 END IF; -- (ln_proc_level>=ln_dbg_level)
849
850 RETURN lv_clear_flag;
851
852 END Get_Transaction_Clear_Flag;
853
854 --==========================================================================
855 -- PROCEDURE NAME:
856 --
857 -- Get_Account_Balance_Side Public
858 --
859 -- DESCRIPTION:
860 --
861 -- This function is to get the balance side of a Account.
862 --
863 -- PARAMETERS:
864 -- In: pv_account_number Account Segment
865 -- pn_flex_value_set_id Flex Value Set ID of CN_GL_ACCOUNT
866 -- pv_attribute_column4bal_side Attribute Column to store Balance Side
867 --
868 -- Out:
869 --
870 -- DESIGN REFERENCES:
871 --
872 --
873 -- CHANGE HISTORY:
874 -- 06-Apr-2010 Wei Huang Created
875 --
876 --===========================================================================
877 FUNCTION Get_Account_Balance_Side
878 (pv_account_number IN VARCHAR2
879 ,pn_flex_value_set_id IN NUMBER
880 ,pv_attribute_column4bal_side IN VARCHAR2
881 ) RETURN VARCHAR2
882 IS
883 lv_procedure_name VARCHAR2(40) := 'Get_Account_Balance_Side';
884 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
885 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
886
887 lv_balance_side_code FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE;
888 lv_balance_side_meaning FND_LOOKUP_VALUES.MEANING%TYPE;
889
890 --Cursor for getting 'Balance Side' and 'Account Type' based on account
891 CURSOR balance_side_cur
892 IS
893 SELECT SUBSTR(TO_CHAR(COMPILED_VALUE_ATTRIBUTES),5,1) Account_Type,
894 DECODE(Value_Category,'Subsidiary',
895 DECODE(pv_attribute_column4bal_side,
896 'ATTRIBUTE1',ATTRIBUTE1,'ATTRIBUTE2',ATTRIBUTE2,'ATTRIBUTE3',ATTRIBUTE3,
897 'ATTRIBUTE4',ATTRIBUTE4,'ATTRIBUTE5',ATTRIBUTE5,'ATTRIBUTE6',ATTRIBUTE6,
898 'ATTRIBUTE7',ATTRIBUTE7,'ATTRIBUTE8',ATTRIBUTE8,'ATTRIBUTE9',ATTRIBUTE9,
899 'ATTRIBUTE10',ATTRIBUTE10,'ATTRIBUTE11',ATTRIBUTE11,'ATTRIBUTE12',ATTRIBUTE12,
900 'ATTRIBUTE13',ATTRIBUTE13,'ATTRIBUTE14',ATTRIBUTE14,'ATTRIBUTE15',ATTRIBUTE15,
901 'ATTRIBUTE16',ATTRIBUTE16,'ATTRIBUTE17',ATTRIBUTE17,'ATTRIBUTE18',ATTRIBUTE18,
902 'ATTRIBUTE19',ATTRIBUTE19,'ATTRIBUTE20',ATTRIBUTE20,'ATTRIBUTE21',ATTRIBUTE21,
903 'ATTRIBUTE22',ATTRIBUTE22,'ATTRIBUTE23',ATTRIBUTE23,'ATTRIBUTE24',ATTRIBUTE24,
904 'ATTRIBUTE25',ATTRIBUTE25,'ATTRIBUTE26',ATTRIBUTE26,'ATTRIBUTE27',ATTRIBUTE27,
905 'ATTRIBUTE28',ATTRIBUTE28,'ATTRIBUTE29',ATTRIBUTE29,'ATTRIBUTE30',ATTRIBUTE30,
906 'ATTRIBUTE31',ATTRIBUTE31,'ATTRIBUTE32',ATTRIBUTE32,'ATTRIBUTE33',ATTRIBUTE33,
907 'ATTRIBUTE34',ATTRIBUTE34,'ATTRIBUTE35',ATTRIBUTE35,'ATTRIBUTE36',ATTRIBUTE36,
908 'ATTRIBUTE37',ATTRIBUTE37,'ATTRIBUTE38',ATTRIBUTE38,'ATTRIBUTE39',ATTRIBUTE39,
909 'ATTRIBUTE40',ATTRIBUTE40,'ATTRIBUTE41',ATTRIBUTE41,'ATTRIBUTE42',ATTRIBUTE42,
910 'ATTRIBUTE43',ATTRIBUTE43,'ATTRIBUTE44',ATTRIBUTE44,'ATTRIBUTE45',ATTRIBUTE45,
911 'ATTRIBUTE46',ATTRIBUTE46,'ATTRIBUTE47',ATTRIBUTE47,'ATTRIBUTE48',ATTRIBUTE48,
912 'ATTRIBUTE49',ATTRIBUTE49,'ATTRIBUTE50',ATTRIBUTE50, NULL),NULL) Balance_Side
913 FROM FND_FLEX_VALUES FFV
914 WHERE flex_value_set_id = pn_flex_value_set_id --flex_value_set_id for CN_GL_ACCOUNT
915 AND flex_value = pv_account_number; --Account Segment
916
917 BEGIN
918 --logging for debug
919 IF (ln_proc_level >= ln_dbg_level)
920 THEN
921 FND_LOG.STRING(ln_proc_level,
922 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
923 '.begin',
924 'Enter procedure');
925 END IF; --ln_proc_level>=ln_dbg_level
926
927 --Fetch the balance Side of account, the balance side setted in flexfield segment value has high priority
928 FOR v_balance_side_row IN balance_side_cur
929 LOOP
930 IF v_balance_side_row.balance_side IS NOT NULL
931 THEN
932 lv_balance_side_code := v_balance_side_row.balance_side;
933 ELSE
934 IF v_balance_side_row.account_type = 'A' OR
935 v_balance_side_row.account_type = 'E'
936 THEN
937 lv_balance_side_code := 'D';
938 ELSE
939 lv_balance_side_code := 'C';
940 END IF;
941 END IF;
942 EXIT;
943 END LOOP; --end balance_side_cur
944
945 --Get the Chinese characters of Debit and Credit
946 BEGIN
947 SELECT MEANING
948 INTO lv_balance_side_meaning
949 FROM FND_LOOKUP_VALUES
950 WHERE LOOKUP_TYPE = 'JA_CN_DEBIT_CREDIT'
951 AND LOOKUP_CODE = lv_balance_side_code
952 AND LANGUAGE = USERENV('LANG');
953 EXCEPTION
954 WHEN NO_DATA_FOUND THEN
955 NULL;
956 END;
957
958 --logging for debug
959 IF (ln_proc_level >= ln_dbg_level)
960 THEN
961 FND_LOG.STRING(ln_proc_level,
962 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
963 'Exit procedure');
964 END IF; -- (ln_proc_level>=ln_dbg_level)
965
966 RETURN lv_balance_side_meaning;
967
968 END Get_Account_Balance_Side;
969
970 --==========================================================================
971 -- PROCEDURE NAME:
972 --
973 -- Get_VAT_Invoice_Number Public
974 --
975 -- DESCRIPTION:
976 --
977 -- This function is to get the VAT invoice number from Golden Tax
978 -- Invoice for transaction.
979 --
980 -- PARAMETERS:
981 -- In: pn_customer_trx_id Transaction ID
982 -- pv_period_name Period Name
983 --
984 -- Out:
985 --
986 -- DESIGN REFERENCES:
987 --
988 --
989 -- CHANGE HISTORY:
990 -- 06-Apr-2010 Wei Huang Created
991 --
992 --===========================================================================
993 FUNCTION Get_VAT_Invoice_Number
994 (pn_customer_trx_id IN NUMBER
995 ,pv_period_name IN VARCHAR2
996 ) RETURN VARCHAR2
997 IS
998 lv_procedure_name VARCHAR2(40) := 'Get_VAT_Invoice_Number';
999 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1000 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1001
1002 lv_vat_invoice_number AR_GTA_TRX_HEADERS_ALL.Gt_Invoice_Number%TYPE;
1003
1004 --Cursor for getting VAT invoice number from Golden Tax Invoice for transaction
1005 CURSOR vat_invoice_number_cur
1006 IS
1007 SELECT GT_INVOICE_NUMBER
1008 FROM AR_GTA_TRX_HEADERS_ALL
1009 WHERE RA_TRX_ID = pn_customer_trx_id
1010 AND RA_GL_PERIOD = pv_period_name
1011 AND SOURCE = 'GT';
1012
1013 BEGIN
1014 --logging for debug
1015 IF (ln_proc_level >= ln_dbg_level)
1016 THEN
1017 FND_LOG.STRING(ln_proc_level,
1018 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1019 '.begin',
1020 'Enter procedure');
1021 END IF; --ln_proc_level>=ln_dbg_level
1022
1023 lv_vat_invoice_number := '';
1024
1025 --Fetch the VAT Inovice Number for transaction from Golden Tax Invoice
1026 FOR v_vat_invoice_number_row IN vat_invoice_number_cur
1027 LOOP
1028 IF vat_invoice_number_cur%ROWCOUNT = 1 THEN
1029 lv_vat_invoice_number := v_vat_invoice_number_row.gt_invoice_number;
1030 ELSE
1031 lv_vat_invoice_number := lv_vat_invoice_number
1032 ||','|| v_vat_invoice_number_row.gt_invoice_number;
1033 END IF;
1034 END LOOP; --end vat_invoice_number_cur
1035
1036 --logging for debug
1037 IF (ln_proc_level >= ln_dbg_level)
1038 THEN
1039 FND_LOG.STRING(ln_proc_level,
1040 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1041 'Exit procedure');
1042 END IF; -- (ln_proc_level>=ln_dbg_level)
1043
1044 RETURN lv_vat_invoice_number;
1045
1046 END Get_VAT_Invoice_Number;
1047
1048 --==========================================================================
1049 -- PROCEDURE NAME:
1050 --
1051 -- Get_Remittance_Bill_Number Public
1052 --
1053 -- DESCRIPTION:
1054 --
1055 -- To get remittance bill number
1056 --
1057 -- PARAMETERS:
1058 -- pn_coa_id IN NUMBER chart of account id
1059 -- pn_receipt_id IN NUMBER receipt id
1060 --
1061 -- DESIGN REFERENCES:
1062 -- APAR_Chaoqun.doc
1063 --
1064 -- CHANGE HISTORY:
1065 -- 12-Jun-2010 Chaoqun Wu Created for new solution described in bug# 9793920
1066 --==========================================================================
1067 FUNCTION Get_Remittance_Bill_Number
1068 (pn_coa_id IN NUMBER
1069 ,pn_receipt_id IN NUMBER)
1070 RETURN VARCHAR2
1071 IS
1072 lv_bill_num VARCHAR(50) := NULL;
1073 lv_context_code VARCHAR(255) := NULL;
1074 lv_attribute_column4bill_num ja_cn_dff_assignments.attribute_column%TYPE;
1075 lv_cursor_sql VARCHAR(200);
1076 TYPE ref_cursor is REF CURSOR;
1077 v_bill_num_row ref_cursor;
1078 lv_procedure_name VARCHAR2(40) := 'Get_Remittance_Bill_Number';
1079 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1080 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1081
1082 --Cursor for getting the Attribute Column for storing Remittance Bill Number
1083 CURSOR dff_assignments_cur
1084 IS
1085 SELECT attribute_column
1086 ,context_code
1087 FROM ja_cn_dff_assignments
1088 WHERE chart_of_accounts_id = pn_coa_id --parameter: pn_coa_id
1089 AND dff_title_code = 'ARTR';
1090
1091 BEGIN
1092 --logging for debug
1093 IF (ln_proc_level >= ln_dbg_level)
1094 THEN
1095 FND_LOG.STRING(ln_proc_level,
1096 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1097 '.begin',
1098 'Enter procedure');
1099 END IF; --ln_proc_level>=ln_dbg_level
1100
1101 FOR v_dff_assignments_row IN dff_assignments_cur
1102 LOOP
1103 lv_attribute_column4bill_num := v_dff_assignments_row.attribute_column;
1104 lv_context_code := v_dff_assignments_row.context_code;
1105 EXIT;
1106 END LOOP;
1107
1108 IF lv_attribute_column4bill_num IS NOT NULL --Defined in DFF assignment form
1109 THEN
1110 lv_cursor_sql := 'SELECT '||lv_attribute_column4bill_num||
1111 ' FROM AR_CASH_RECEIPTS_ALL'||
1112 ' WHERE Cash_Receipt_Id ='||pn_receipt_id||
1113 ' AND Attribute_Category='''||lv_context_code||'''';
1114
1115 OPEN v_bill_num_row for lv_cursor_sql;
1116 FETCH v_bill_num_row INTO lv_bill_num;
1117 CLOSE v_bill_num_row;
1118 END IF;
1119
1120 --logging for debug
1121 IF (ln_proc_level >= ln_dbg_level)
1122 THEN
1123 FND_LOG.STRING(ln_proc_level,
1124 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1125 'Exit procedure');
1126 END IF; -- (ln_proc_level>=ln_dbg_level)
1127
1128 RETURN lv_bill_num;
1129 END Get_Remittance_Bill_Number;
1130
1131 --==========================================================================
1132 -- PROCEDURE NAME:
1133 --
1134 -- Get_Balance Public
1135 --
1136 -- DESCRIPTION:
1137 --
1138 -- To get functional balance and entered balance.
1139 --
1140 -- PARAMETERS:
1141 -- pn_party_id Party ID
1142 -- pn_ledger_id Ledger ID
1143 -- pn_legal_entity_id Legal Entity ID
1144 -- pv_account_number Receivable Account Number
1145 -- pv_currency_code Currency Code
1146 -- pv_period_name Accounting Period
1147 -- pv_balance_type balance type, 'F' for functional balance,
1148 -- and 'E' stand for entered balance
1149 --
1150 -- DESIGN REFERENCES:
1151 --
1152 --
1153 -- CHANGE HISTORY:
1154 -- 06-Apr-2010 Wei Huang Created
1155 --
1156 --===========================================================================
1157 FUNCTION Get_Balance
1158 (pn_party_id IN NUMBER
1159 ,pn_ledger_id IN NUMBER
1160 ,pn_legal_entity_id IN NUMBER
1161 ,pv_account_number IN VARCHAR2
1162 ,pv_currency_code IN VARCHAR2
1163 ,pv_period_name IN VARCHAR2
1164 ,pv_balance_type IN VARCHAR2
1165 ) RETURN NUMBER
1166 IS
1167 ln_balance NUMBER := NULL;
1168 lv_procedure_name VARCHAR2(40) := 'Get_Balance';
1169 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1170 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1171
1172 --Cursor for getting 'Functional Balance', 'Entered Balance' from
1173 --Account Balance Itemization table ja_cn_account_balances
1174 CURSOR account_balance_cur
1175 IS
1176 SELECT SUM(func_end_balance) Functional_Balance,
1177 SUM(orig_end_balance) Entered_Balance
1178 FROM ja_cn_account_balances_v
1179 WHERE third_party_id = pn_party_id
1180 AND ledger_id = pn_ledger_id
1181 AND legal_entity_id = pn_legal_entity_id
1182 AND account_segment = pv_account_number
1183 AND currency_code = pv_currency_code
1184 AND period_name = pv_period_name;
1185
1186 BEGIN
1187 --logging for debug
1188 IF (ln_proc_level >= ln_dbg_level)
1189 THEN
1190 FND_LOG.STRING(ln_proc_level,
1191 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1192 '.begin',
1193 'Enter procedure');
1194 END IF; --ln_proc_level>=ln_dbg_level
1195
1196 --Get Functional Balance and Entered Balance
1197 FOR v_account_balance_row IN account_balance_cur
1198 LOOP
1199 IF pv_balance_type = 'F'
1200 THEN
1201 ln_balance := v_account_balance_row.Functional_Balance;
1202 ELSIF pv_balance_type = 'E'
1203 THEN
1204 ln_balance := v_account_balance_row.Entered_Balance;
1205 END IF;
1206 EXIT;
1207 END LOOP;
1208
1209 --logging for debug
1210 IF (ln_proc_level >= ln_dbg_level)
1211 THEN
1212 FND_LOG.STRING(ln_proc_level,
1213 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1214 'Exit procedure');
1215 END IF; -- (ln_proc_level>=ln_dbg_level)
1216
1217 RETURN NVL(ln_balance,0);
1218
1219 END Get_Balance;
1220
1221 --==========================================================================
1222 -- PROCEDURE NAME:
1223 --
1224 -- Get_Account_Number Public
1225 --
1226 -- DESCRIPTION:
1227 --
1228 -- To get receivable accounts
1229 --
1230 -- PARAMETERS:
1231 -- pn_ledger_id IN NUMBER ledger id
1232 -- pn_legal_entity_id IN NUMBER legal entity id
1233 -- pv_first_period IN VARCHAR first period of fiscal year
1234 -- xa_receivable_account_num OUT ACCOUNT_NUMBER_TBL liability accounts array
1235 --
1236 -- DESIGN REFERENCES:
1237 -- APAR_Chaoqun.doc
1238 --
1239 -- CHANGE HISTORY:
1240 -- 24-Aug-2010 Chaoqun Wu created for new solution described in bug 10041443
1241 --==========================================================================
1242 PROCEDURE Get_Account_Number
1243 (pn_ledger_id IN NUMBER
1244 ,pn_legal_entity_id IN NUMBER
1245 ,pv_first_period IN VARCHAR2
1246 ,xa_receivable_account_num OUT NOCOPY ACCOUNT_NUMBER_TBL
1247 )
1248 IS
1249 lv_procedure_name VARCHAR2(40) := 'Get_Account_Number';
1250 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1251 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1252 la_receivable_account_num ACCOUNT_NUMBER_TBL := ACCOUNT_NUMBER_TBL();
1253 ln_account_num_count NUMBER := 0;
1254
1255 --getting account number
1256 CURSOR account_number_cur
1257 IS
1258 SELECT DISTINCT Account_Number FROM(
1259 SELECT Jab.Account_Segment Account_Number
1260 FROM Xla_Ae_Headers Aeh,
1261 Xla_Ae_Lines Ael,
1262 Ja_Cn_Code_Combination_v Jcc,
1263 Ja_Cn_Account_Balances Jab
1264 WHERE Jab.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1265 AND Jab.Legal_Entity_Id = pn_legal_entity_id --parameter: pn_legal_entity_id
1266 AND Jab.Period_Name = pv_first_period --parameter: pv_first_period
1267 AND Jab.Account_Segment = Jcc.Account_Segment
1268 AND Jab.Ledger_Id = Jcc.Ledger_id
1269 AND Jab.Third_Party_TYPE = 'C' --only collecting customer
1270 --SLA condition
1271 AND Aeh.Ae_Header_Id = Ael.Ae_Header_Id
1272 AND Ael.Accounting_Class_Code = 'RECEIVABLE'
1273 AND Aeh.Ledger_id = pn_ledger_id --parameter: pn_ledger_id
1274 AND Jcc.Ledger_id = Aeh.Ledger_id
1275 AND Jcc.Code_Combination_Id = Ael.Code_Combination_Id
1276 --BSV condition, to handle one invoice having BSVs from different legal entity in distribution lines
1277 AND EXISTS (SELECT llbg.Bal_Seg_Value
1278 FROM ja_cn_ledger_le_bsv_gt llbg
1279 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Ael.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
1280 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1281 AND llbg.Legal_Entity_Id = pn_legal_entity_id) --parameter: pn_legal_entity_id
1282 )
1283 ORDER BY Account_Number;
1284
1285 BEGIN
1286 --logging for debug
1287 IF (ln_proc_level >= ln_dbg_level)
1288 THEN
1289 FND_LOG.STRING(ln_proc_level,
1290 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1291 '.begin',
1292 'Enter procedure');
1293 END IF; --ln_proc_level>=ln_dbg_level
1294
1295 --Get receivable account number
1296 FOR v_receivable_account_num_row IN account_number_cur
1297 LOOP
1298 ln_account_num_count := ln_account_num_count + 1;
1299 la_receivable_account_num.EXTEND;
1300 la_receivable_account_num(ln_account_num_count) := v_receivable_account_num_row.Account_Number;
1301 END LOOP;
1302 xa_receivable_account_num := la_receivable_account_num;
1303
1304 --logging for debug
1305 IF (ln_proc_level >= ln_dbg_level)
1306 THEN
1307 FND_LOG.STRING(ln_proc_level,
1308 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1309 'Exit procedure');
1310 END IF; -- (ln_proc_level>=ln_dbg_level)
1311
1312 END Get_Account_Number;
1313
1314 --==========================================================================
1315 -- PROCEDURE NAME:
1316 --
1317 -- Get_Customer_Number Public
1318 --
1319 -- DESCRIPTION:
1320 --
1321 -- To get customer number
1322 --
1323 -- PARAMETERS:
1324 -- In: pv_supplier_id IN NUMBER supplier id
1325 --
1326 -- DESIGN REFERENCES:
1327 -- APAR_Chaoqun.doc
1328 --
1329 -- CHANGE HISTORY:
1330 -- 24-Aug-2010 Chaoqun Wu created for new solution described in bug 10041443
1331 --==========================================================================
1332 FUNCTION Get_Customer_Number
1333 (pn_customer_id IN NUMBER)
1334 RETURN VARCHAR2
1335 IS
1336 lv_customer_num VARCHAR(30) := NULL;
1337 lv_procedure_name VARCHAR2(40) := 'Get_Customer_Number';
1338 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1339 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1340
1341 CURSOR customer_num_cur
1342 IS
1343 SELECT cust.Account_Number Customer_Number
1344 FROM HZ_CUST_ACCOUNTS cust
1345 WHERE cust.cust_account_id = pn_customer_id; --parameter: pn_customer_id
1346
1347 BEGIN
1348 --logging for debug
1349 IF (ln_proc_level >= ln_dbg_level)
1350 THEN
1351 FND_LOG.STRING(ln_proc_level,
1352 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1353 '.begin',
1354 'Enter procedure');
1355 END IF; --ln_proc_level>=ln_dbg_level
1356
1357 FOR v_customer_num_row IN customer_num_cur
1358 LOOP
1359 lv_customer_num := v_customer_num_row.Customer_Number;
1360 EXIT;
1361 END LOOP;
1362
1363 --logging for debug
1364 IF (ln_proc_level >= ln_dbg_level)
1365 THEN
1366 FND_LOG.STRING(ln_proc_level,
1367 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1368 'Exit procedure');
1369 END IF; -- (ln_proc_level>=ln_dbg_level)
1370
1371 RETURN lv_customer_num;
1372 END Get_Customer_Number;
1373
1374 --==========================================================================
1375 -- PROCEDURE NAME:
1376 --
1377 -- Add_Beginning_Balance Public
1378 --
1379 -- DESCRIPTION:
1380 --
1381 -- To export beginning balance for supplier
1382 --
1383 -- PARAMETERS:
1384 -- pn_ledger_id IN NUMBER ledger id
1385 -- pn_legal_entity_id IN NUMBER legal entity id
1386 -- pn_coa_id IN NUMBER chart of account id
1387 -- pv_first_period IN VARCHAR2 first period of fiscal year
1388 -- pv_accounting_period_number IN VARCHAR2 accounting period number
1389 -- pv_accounting_year IN VARCHAR2 accounting year
1390 -- pa_receivable_account_num IN ACCOUNT_NUMBER_TBL liability accounts array
1391 --
1392 -- DESIGN REFERENCES:
1393 -- APAR_Chaoqun.doc
1394 --
1395 -- CHANGE HISTORY:
1396 -- 24-Aug-2010 Chaoqun Wu created for new solution described in bug 10041443
1397 --==========================================================================
1398 FUNCTION Add_Year_Beginning_Balance
1399 (pn_ledger_id IN NUMBER
1400 ,pn_legal_entity_id IN NUMBER
1401 ,pn_coa_id IN NUMBER
1402 ,pv_first_period IN VARCHAR2
1403 ,pv_accounting_period_number IN VARCHAR2
1404 ,pv_accounting_year IN VARCHAR2
1405 ,pa_receivable_account_num IN ACCOUNT_NUMBER_TBL
1406 )
1407 RETURN VARCHAR2
1408 IS
1409 lv_procedure_name VARCHAR2(40) := 'Add_Year_Beginning_Balance';
1410 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1411 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1412 ln_account_num_count NUMBER := 0;
1413 lv_fun_currency VARCHAR2(100);
1414 lv_beginning_bal_exported_flag VARCHAR2(1) := 'N';
1415 lv_balance_desc VARCHAR2(200);
1416
1417 --collecting beginning balance
1418 CURSOR beginning_balance_cur
1419 (ln_customer_id NUMBER
1420 ,lv_account_number VARCHAR2)
1421 IS
1422 SELECT Fcv.NAME Entered_Currency,
1423 bal.Entered_Amount,
1424 bal.Functional_Amount
1425 FROM(
1426 SELECT currency_code
1427 ,SUM(NVL(Orig_Begin_Balance_Dr,0) - NVL(Orig_Begin_Balance_Cr,0)) Entered_Amount
1428 ,SUM(NVL(Func_Begin_Balance_Dr,0) - NVL(Func_Begin_Balance_Cr,0)) Functional_Amount
1429 FROM ja_cn_account_balances
1430 WHERE ledger_id = pn_ledger_id --parameter: pn_ledger_id
1431 AND legal_entity_id = pn_legal_entity_id --parameter: pn_legal_entity_id
1432 AND third_party_id = ln_customer_id --variable: ln_customer_id
1433 AND account_segment = lv_account_number --variable: lv_account_number
1434 AND period_name = pv_first_period --parameter: pv_first_period
1435 GROUP BY third_party_id, currency_code) bal,
1436 Fnd_Currencies_Vl Fcv
1437 WHERE bal.Functional_Amount <> 0
1438 AND Fcv.currency_code = bal.currency_code
1439 ORDER BY bal.currency_code;
1440
1441 --collecting all customer id from account balance table
1442 CURSOR customer_id_list_cur
1443 IS
1444 SELECT DISTINCT Third_Party_Id Customer_Id
1445 FROM ja_cn_account_balances
1446 WHERE ledger_id = pn_ledger_id --parameter: pn_ledger_id
1447 AND legal_entity_id = pn_legal_entity_id --parameter: pn_legal_entity_id
1448 AND period_name = pv_first_period --parameter: pv_first_period
1449 AND third_party_type = 'C'
1450 ORDER BY Third_Party_Id;
1451
1452 --getting no invoice or payment flag
1453 CURSOR no_trans_flag_cur
1454 (ln_supplier_id NUMBER)
1455 IS
1456 SELECT DECODE(COUNT(*)
1457 ,0
1458 ,'Y'
1459 ,'N') no_trans_flag
1460 FROM GL_JE_HEADERS jeh,
1461 GL_JE_LINES jel,
1462 GL_IMPORT_REFERENCES gir,
1463 XLA_AE_LINES ael,
1464 XLA_AE_HEADERS aeh,
1465 JA_CN_CODE_COMBINATION_V jcc,
1466 HZ_CUST_ACCOUNTS cust,
1467 HZ_PARTIES part,
1468 Gl_Ledgers Gl,
1469 Gl_Periods Gp
1470 WHERE jeh.je_header_id = jel.je_header_id
1471 AND jeh.je_header_id = gir.je_header_id
1472 AND jel.je_line_num = gir.je_line_num
1473 AND gir.gl_sl_link_id = ael.gl_sl_link_id
1474 AND gir.gl_sl_link_table = ael.gl_sl_link_table
1475 AND ael.ae_header_id = aeh.ae_header_id
1476 AND jcc.code_combination_id = jel.code_combination_id
1477 AND jcc.ledger_id = jeh.ledger_id
1478 AND cust.cust_account_id(+) = ael.party_id
1479 AND cust.party_id = part.party_id(+)
1480 AND jeh.status = 'P' -- AR posted to GL
1481 AND ael.accounting_class_code = 'RECEIVABLE'--accountingclass is 'Receivable' in the accounting line in SLA
1482 --BSV Condition
1483 AND EXISTS (SELECT llbg.Bal_Seg_Value
1484 FROM ja_cn_ledger_le_bsv_gt llbg
1485 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Jcc.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
1486 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1487 AND llbg.Legal_Entity_Id = pn_legal_entity_id) --parameter: pn_legal_entity_id
1488 --Period condition
1489 AND Gl.Ledger_Id = Jcc.ledger_id
1490 AND Gl.period_set_name = Gp.period_set_name
1491 AND Jeh.Period_Name = Gp.Period_Name
1492 AND Gl.accounted_period_type = Gp.period_type
1493 AND Gp.period_year = to_number(pv_accounting_year) --parameter: pv_accounting_year
1494 AND Gp.start_date BETWEEN
1495 (SELECT Gp1.Start_Date
1496 FROM Gl_Periods Gp1
1497 WHERE Gl.period_set_name = Gp1.period_set_name
1498 AND Gl.accounted_period_type = Gp1.period_type
1499 AND Gp1.period_name = pv_first_period) --parameter: pv_first_period
1500 AND (SELECT Gp2.Start_Date
1501 FROM Gl_Periods Gp2
1502 WHERE Gl.period_set_name = Gp2.period_set_name
1503 AND Gl.accounted_period_type = Gp2.period_type
1504 AND Gp2.period_name = pv_first_period) --parameter: pv_first_period
1505 AND jeh.ledger_id = pn_ledger_id; --parameter: pn_ledger_id
1506
1507 BEGIN
1508 --logging for debug
1509 IF (ln_proc_level >= ln_dbg_level)
1510 THEN
1511 FND_LOG.STRING(ln_proc_level,
1512 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1513 '.begin',
1514 'Enter procedure');
1515 END IF; --ln_proc_level>=ln_dbg_level
1516
1517 BEGIN
1518 SELECT Fcv.NAME Functional_Currency
1519 INTO lv_fun_currency
1520 FROM Gl_Ledgers Gl, Fnd_Currencies_Vl Fcv
1521 WHERE Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1522 AND Fcv.CURRENCY_CODE = Gl.Currency_Code;
1523 EXCEPTION
1524 WHEN NO_DATA_FOUND THEN
1525 NULL;
1526 END;
1527
1528 Fnd_Message.Set_Name(Application => 'JA',
1529 NAME => 'JA_CN_AR_BALANCE_DESC'
1530 );
1531 lv_balance_desc := Fnd_Message.Get;
1532
1533 --Step1: get all customer ids from account balance table
1534 FOR v_customer_id_list_row IN customer_id_list_cur
1535 LOOP
1536 --Step2: only export beginning balance for customer with no transaction within first period
1537 /*FOR v_no_trans_flag_row IN no_trans_flag_cur(v_customer_id_list_row.Customer_Id)
1538 LOOP
1539 --If there is no transaction or receipt created and posted in GL within first period, then export beginning balance
1540 IF v_no_trans_flag_row.no_trans_flag = 'Y'
1541 THEN */
1542 ln_account_num_count := pa_receivable_account_num.count;
1543 --Setp3: get liability accounts
1544 FOR ln_count IN 1..ln_account_num_count
1545 LOOP
1546 --Step4: get beginning balance for liability accounts
1547 FOR v_beginning_balance_row IN beginning_balance_cur(v_customer_id_list_row.customer_id
1548 ,pa_receivable_account_num(ln_count))
1549 LOOP
1550 lv_beginning_bal_exported_flag := 'Y';
1551 Add_YBB_XML_Node(pv_customer_number => Get_Customer_Number(v_customer_id_list_row.customer_id)
1552 ,pv_account_number => pa_receivable_account_num(ln_count)
1553 ,pv_journal_created_date => NULL
1554 ,pv_journal_date => NULL
1555 ,pv_accounting_year => pv_accounting_year
1556 ,pv_accounting_period_number => pv_accounting_period_number
1557 ,pv_journal_category_number => NULL
1558 ,pv_journal_number => NULL
1559 ,pv_functional_currency => lv_fun_currency
1560 ,pn_exchange_rate => NULL
1561 ,pv_balance_side => JA_CN_AP_PBD_EXPORT_PKG.Get_Balance_Side(pn_coa_id
1562 ,pa_receivable_account_num(ln_count))
1563 ,pn_functional_balance => NULL
1564 ,pn_entered_balance => NULL
1565 ,pn_functional_amount => v_beginning_balance_row.functional_amount
1566 ,pv_entered_currency => v_beginning_balance_row.entered_currency
1567 ,pn_entered_amount => v_beginning_balance_row.entered_amount
1568 ,pv_description => lv_balance_desc
1569 ,pv_due_date => NULL
1570 ,pv_applied_journal_number => NULL
1571 ,pv_applied_date => NULL
1572 ,pv_document_type_number => NULL
1573 ,pv_transaction_type_number => NULL
1574 ,pv_transaction_number => NULL
1575 ,pv_invoice_number => NULL
1576 ,pv_contract_number => NULL
1577 ,pv_project_number => NULL
1578 ,pv_settlement_method_number => NULL
1579 ,pv_payment_date => NULL
1580 ,pv_clear_flag => '0'
1581 ,pv_remittance_bill_number => NULL);
1582 END LOOP; --Step4: begining balance for receivable account
1583 END LOOP; --Step3: receivable account list
1584 --END IF; --if no transaction or receipt within first period
1585 --EXIT;
1586 --END LOOP; --Step2: no transaction or receipt wihtin first period
1587 END LOOP; --Step1: customer id list
1588
1589 --logging for debug
1590 IF (ln_proc_level >= ln_dbg_level)
1591 THEN
1592 FND_LOG.STRING(ln_proc_level,
1593 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1594 'Exit procedure');
1595 END IF; -- (ln_proc_level>=ln_dbg_level)
1596
1597 RETURN lv_beginning_bal_exported_flag;
1598
1599 END Add_Year_Beginning_Balance;
1600
1601 --==========================================================================
1602 -- PROCEDURE NAME:
1603 --
1604 -- Add_Receivables_Balance_Detail Public
1605 --
1606 -- DESCRIPTION:
1607 --
1608 -- This procedure is to export receivable balance and details for
1609 -- each customer as per receivable accounts, such as "Account
1610 -- Receivable", "Receivable in Advance", and "Other Receivable".
1611 --
1612 --
1613 -- PARAMETERS:
1614 -- In: pn_legal_entity_id LEGAL_ENTITY_ID
1615 -- pn_ledger_id Ledger ID
1616 -- pv_accounting_year Accounting Year
1617 -- pn_coa_id Chart of Accounts ID
1618 -- pv_period_from Period From
1619 -- pv_period_to Period To
1620 --
1621 -- Out:
1622 --
1623 -- DESIGN REFERENCES:
1624 --
1625 --
1626 -- CHANGE HISTORY:
1627 -- 06-Apr-2010 Wei Huang Created
1628 -- 28-May-2010 Wei Huang Added condition of comparing Accounting_date
1629 -- and 'AND Ael.Accounting_Class_Code = 'RECEIVABLE'.
1630 -- Use cursor line_amount_cur to get receipt amount
1631 -- instead of amount_cur.
1632 -- 12-Jun-2010 Chaoqun Wu Updated for new solution described in bug# 9793920
1633 -- 2-Aug-2010 Chaoqun Wu Updated for fixing bug# 9962326
1634 -- 24-Aug-2010 Chaoqun Wu Updated for new solution described in bug 10041443
1635 -- 17-Sep-2010 Chaoqun Wu Updated for solution changes described in bug 10121399
1636 -- 06-Jan-2011 Jianchao Chi Updated for the logic of voucher nuber and legal entity
1637 --==========================================================================
1638 PROCEDURE Add_Receivables_Balance_Detail
1639 (pn_legal_entity_id IN NUMBER
1640 ,pn_ledger_id IN NUMBER
1641 ,pv_accounting_year IN VARCHAR2
1642 ,pn_coa_id IN NUMBER
1643 ,pv_period_from IN VARCHAR2
1644 ,pv_period_to IN VARCHAR2
1645 )
1646 IS
1647 lv_procedure_name VARCHAR2(40) := 'Add_Receivables_Balance_Detail';
1648 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1649 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1650 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1651 NO_DATA EXCEPTION;
1652
1653 ln_row_count NUMBER := 0;
1654 ld_start_date DATE;
1655 ld_end_date DATE;
1656 lv_balance_side FND_LOOKUP_VALUES.MEANING%TYPE;
1657 lv_functional_currency fnd_currencies_vl.NAME%TYPE;
1658
1659 ln_flex_value_set_id fnd_id_flex_segments.flex_value_set_id%TYPE;
1660 lr_dff_assignments_row ja_cn_dff_assignments%ROWTYPE;
1661 lv_attribute_column4bill_num ja_cn_dff_assignments.attribute_column%TYPE;
1662 lv_attribute_column4bal_side ja_cn_dff_assignments.attribute_column%TYPE;
1663 lv_transaction_exported_flag VARCHAR2(1);
1664 lv_vat_invoice_number AR_GTA_TRX_HEADERS_ALL.Gt_Invoice_Number%TYPE;
1665 lv_contract_number_column RA_BATCH_SOURCES_ALL.Default_Reference%TYPE;
1666 ln_journal_number NUMBER;
1667 ln_applied_journal_number NUMBER;
1668 ln_functional_amount NUMBER;
1669 ln_entered_amount NUMBER;
1670 ln_cm_customer_trx_id NUMBER := 0;
1671 ln_current_receipt_id NUMBER;
1672 ln_current_ae_header_id NUMBER;
1673 lv_clear_flag VARCHAR2(1);
1674 lv_current_account_number VARCHAR2(25);
1675
1676 --Begin: add for fixing bug 10041443
1677 lv_first_period_flag VARCHAR(1);--use to indicate whether pv_period_from is the first period
1678 la_receivable_acc_num_array ACCOUNT_NUMBER_TBL := ACCOUNT_NUMBER_TBL();
1679 lv_first_period_name VARCHAR2(30);
1680 ln_first_period_num NUMBER;
1681 lv_beginning_bal_exported_flag VARCHAR(1) := 'N'; --use to indicate whether beginning balance is exported for some supplier or not
1682 --End: add for fixing bug 10041443
1683
1684 --Cursor for fetching AR Transaction data when the accounting class
1685 --is 'Receivable' in the accounting line in SLA, and data posted to GL.
1686 CURSOR ar_gl_sla_cur
1687 IS
1688 SELECT DISTINCT
1689 jeh.je_header_id Je_Header_Id,
1690 aeh.ae_header_id Ae_Header_Id,
1691 gps.period_name Period_Name,
1692 ael.party_id Party_ID,
1693 part.party_number Customer_Number,
1694 jcc.Account_Segment Account_Number,
1695 pv_accounting_year Accounting_Year,
1696 jeh.default_effective_date
1697 FROM GL_JE_HEADERS jeh,
1698 GL_JE_LINES jel,
1699 GL_IMPORT_REFERENCES gir,
1700 XLA_AE_LINES ael,
1701 XLA_AE_HEADERS aeh,
1702 JA_CN_CODE_COMBINATION_V jcc,
1703 HZ_CUST_ACCOUNTS cust,
1704 HZ_PARTIES part,
1705 GL_PERIOD_STATUSES gps
1706 WHERE jeh.je_header_id = jel.je_header_id
1707 AND jeh.je_header_id = gir.je_header_id
1708 AND jel.je_line_num = gir.je_line_num
1709 AND gir.gl_sl_link_id = ael.gl_sl_link_id
1710 AND gir.gl_sl_link_table = ael.gl_sl_link_table
1711 AND ael.ae_header_id = aeh.ae_header_id
1712 AND jcc.code_combination_id = jel.code_combination_id
1713 AND jcc.ledger_id = jeh.ledger_id
1714 AND cust.cust_account_id(+) = ael.party_id
1715 AND cust.party_id = part.party_id(+)
1716 AND jeh.period_name = gps.period_name
1717 AND jeh.ledger_id = gps.ledger_id
1718 AND jeh.status = 'P' -- AR posted to GL
1719 AND ael.accounting_class_code = 'RECEIVABLE'--accountingclass is 'Receivable' in the accounting line in SLA
1720 --BSV Condition
1721 AND EXISTS (SELECT llbg.Bal_Seg_Value
1722 FROM ja_cn_ledger_le_bsv_gt llbg
1723 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Jcc.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
1724 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1725 AND llbg.Legal_Entity_Id = pn_legal_entity_id) --parameter: pn_legal_entity_id
1726 --ld_start_date and ld_end_date come from pv_accounting_year, pv_period_from, pv_period_to
1727 AND gps.application_id = 101
1728 AND ((gps.start_date BETWEEN ld_start_date AND ld_end_date) AND
1729 (gps.end_date BETWEEN ld_start_date AND ld_end_date))
1730 AND jeh.ledger_id = pn_ledger_id --parameter: pn_ledger_id
1731 ORDER BY part.party_number, jcc.Account_Segment,jeh.default_effective_date,jeh.Je_Header_Id;
1732
1733 --Cursor for getting Transaction ID based on ae_header_id
1734 CURSOR customer_trx_id_cur
1735 (ln_ae_header_id NUMBER)
1736 IS
1737 SELECT DISTINCT Rct.Customer_Trx_Id Customer_Trx_Id
1738 FROM RA_CUSTOMER_TRX_ALL Rct,
1739 Xla_Transaction_Entities Ent,
1740 Xla_Ae_Headers Aeh
1741 WHERE Ent.Application_Id = 222
1742 AND Rct.Customer_Trx_Id = Ent.Source_Id_Int_1
1743 AND Ent.Entity_Code = 'TRANSACTIONS'
1744 AND Ent.Entity_Id = Aeh.Entity_Id
1745 AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
1746 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1747 AND Rct.Previous_Customer_Trx_Id IS NULL --Not On-account Credit Memo should not be export seperately
1748 UNION
1749 SELECT DISTINCT Rcta.Customer_Trx_Id Customer_Trx_Id
1750 FROM RA_CUSTOMER_TRX_ALL Rcta,
1751 RA_CUST_TRX_LINE_GL_DIST_ALL Gd
1752 WHERE Rcta.Customer_Trx_Id IN (
1753 SELECT DISTINCT App.Applied_Customer_Trx_Id Customer_Trx_Id
1754 FROM AR_CASH_RECEIPTS_ALL Acr,
1755 AR_RECEIVABLE_APPLICATIONS_ALL App,
1756 Xla_Transaction_Entities Ent,
1757 Xla_Ae_Headers Aeh
1758 WHERE Ent.Application_Id = 222
1759 AND Acr.Cash_Receipt_Id = App.Cash_Receipt_Id
1760 AND Acr.Cash_Receipt_Id = Ent.Source_Id_Int_1
1761 AND Ent.Entity_Code = 'RECEIPTS'
1762 AND Ent.Entity_Id = Aeh.Entity_Id
1763 AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
1764 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1765 AND App.Applied_Customer_Trx_Id IS NOT NULL
1766 UNION
1767 SELECT DISTINCT Adj.Customer_Trx_Id Customer_Trx_Id
1768 FROM AR_ADJUSTMENTS_ALL Adj,
1769 Xla_Transaction_Entities Ent,
1770 Xla_Ae_Headers Aeh
1771 WHERE Ent.Application_Id = 222
1772 AND Adj.Adjustment_Id = Ent.Source_Id_Int_1
1773 AND Ent.Entity_Code = 'ADJUSTMENTS'
1774 AND Ent.Entity_Id = Aeh.Entity_Id
1775 AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
1776 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1777 UNION
1778 SELECT DISTINCT Rct.Previous_Customer_Trx_Id Customer_Trx_Id
1779 FROM RA_CUSTOMER_TRX_ALL Rct,
1780 Xla_Transaction_Entities Ent,
1781 Xla_Ae_Headers Aeh
1782 WHERE Ent.Application_Id = 222
1783 AND Rct.Customer_Trx_Id = Ent.Source_Id_Int_1
1784 AND Ent.Entity_Code = 'TRANSACTIONS'
1785 AND Ent.Entity_Id = Aeh.Entity_Id
1786 AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
1787 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1788 AND Rct.Previous_Customer_Trx_Id IS NOT NULL --Applied Credit Memo
1789 )
1790 --Period condition
1791 AND Rcta.CUSTOMER_TRX_ID = Gd.CUSTOMER_TRX_ID
1792 AND Gd.ACCOUNT_CLASS = 'REC'
1793 AND Gd.LATEST_REC_FLAG = 'Y'
1794 AND Gd.gl_date NOT BETWEEN
1795 (SELECT Gp1.Start_Date
1796 FROM Gl_Periods Gp1
1797 ,Gl_Ledgers Gl
1798 WHERE Gl.period_set_name = Gp1.period_set_name
1799 AND Gl.accounted_period_type = Gp1.period_type
1800 AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1801 AND Gp1.period_name = pv_period_from) --parameter: pv_period_from
1802 AND (SELECT Gp2.End_Date
1803 FROM Gl_Periods Gp2
1804 ,Gl_Ledgers Gl
1805 WHERE Gl.period_set_name = Gp2.period_set_name
1806 AND Gl.accounted_period_type = Gp2.period_type
1807 AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1808 AND Gp2.period_name = pv_period_to); --parameter: pv_period_to
1809
1810 --Cursor for getting accounting period for each Transaction and related Receipts
1811 CURSOR period_name_cur
1812 (ln_customer_trx_id NUMBER)
1813 IS
1814 --from Transaction
1815 SELECT DISTINCT Gp.Period_Name,Gp.Start_Date,Gp.End_Date
1816 FROM RA_CUSTOMER_TRX_ALL Rct,
1817 RA_CUST_TRX_LINE_GL_DIST_ALL Gd,
1818 Gl_Ledgers Gl,
1819 Gl_Periods Gp
1820 WHERE Rct.Customer_Trx_Id = ln_customer_trx_id --variable: ln_customer_trx_id
1821 AND Gl.ledger_id = pn_ledger_id --parameter: pn_ledger_id
1822 AND Rct.CUSTOMER_TRX_ID = Gd.CUSTOMER_TRX_ID
1823 AND Gd.ACCOUNT_CLASS = 'REC'
1824 AND Gd.LATEST_REC_FLAG = 'Y'
1825 AND Gl.period_set_name = Gp.period_set_name
1826 AND Gl.accounted_period_type = Gp.period_type
1827 AND Gd.gl_date BETWEEN Gp.start_date AND Gp.End_date
1828 AND ((gp.start_date BETWEEN ld_start_date AND ld_end_date) AND
1829 (gp.end_date BETWEEN ld_start_date AND ld_end_date))
1830 UNION
1831 --for Receipts from AR_RECEIVABLE_APPLICATIONS_ALL and the status must be Applied
1832 SELECT DISTINCT Gp.Period_Name,Gp.Start_Date,Gp.End_Date
1833 FROM AR_RECEIVABLE_APPLICATIONS_ALL App,
1834 Gl_Ledgers Gl,
1835 Gl_Periods Gp
1836 WHERE App.Applied_Customer_Trx_Id = ln_customer_trx_id --variable: ln_customer_trx_id
1837 AND Gl.ledger_id = pn_ledger_id --parameter: pn_ledger_id
1838 AND Gl.period_set_name = Gp.period_set_name
1839 AND Gl.accounted_period_type = Gp.period_type
1840 AND App.gl_date BETWEEN Gp.start_date AND Gp.End_date
1841 AND app.STATUS = 'APP' --Receipts must be applied
1842 AND ((gp.start_date BETWEEN ld_start_date AND ld_end_date) AND
1843 (gp.end_date BETWEEN ld_start_date AND ld_end_date))
1844 UNION
1845 --from Credit Memo
1846 SELECT DISTINCT Gp.Period_Name,Gp.Start_Date,Gp.End_Date
1847 FROM RA_CUSTOMER_TRX_ALL Rct,
1848 RA_CUST_TRX_LINE_GL_DIST_ALL Gd,
1849 Gl_Ledgers Gl,
1850 Gl_Periods Gp
1851 WHERE Rct.Previous_Customer_Trx_Id = ln_customer_trx_id --variable: ln_customer_trx_id
1852 AND Gl.ledger_id = pn_ledger_id --parameter: pn_ledger_id
1853 AND Rct.CUSTOMER_TRX_ID = Gd.CUSTOMER_TRX_ID
1854 AND Gd.ACCOUNT_CLASS = 'REC'
1855 AND Gd.LATEST_REC_FLAG = 'Y'
1856 AND Gl.period_set_name = Gp.period_set_name
1857 AND Gl.accounted_period_type = Gp.period_type
1858 AND Gd.gl_date BETWEEN Gp.start_date AND Gp.End_date
1859 AND ((gp.start_date BETWEEN ld_start_date AND ld_end_date) AND
1860 (gp.end_date BETWEEN ld_start_date AND ld_end_date))
1861 UNION
1862 --from adjustment
1863 SELECT DISTINCT Gp.Period_Name,Gp.Start_Date,Gp.End_Date
1864 FROM AR_ADJUSTMENTS_ALL Adj,
1865 Gl_Ledgers Gl,
1866 Gl_Periods Gp
1867 WHERE Adj.Customer_Trx_Id = ln_customer_trx_id --variable: ln_customer_trx_id
1868 AND Gl.ledger_id = pn_ledger_id --parameter: pn_ledger_id
1869 AND Gl.period_set_name = Gp.period_set_name
1870 AND Gl.accounted_period_type = Gp.period_type
1871 AND Adj.Gl_Date BETWEEN Gp.start_date AND Gp.End_date
1872 AND ((gp.start_date BETWEEN ld_start_date AND ld_end_date) AND
1873 (gp.end_date BETWEEN ld_start_date AND ld_end_date));
1874
1875 --Cursor for getting the Transaction information based on the Transaction Id
1876 CURSOR transaction_cur
1877 (ln_customer_trx_id NUMBER
1878 ,lv_period_name VARCHAR2
1879 )
1880 IS
1881 SELECT DISTINCT
1882 Rct.customer_trx_id Customer_Trx_Id,
1883 Jeh.Je_Header_Id Je_Header_Id,
1884 Aeh.Ae_Header_Id Ae_Header_Id,
1885 TO_CHAR(Jeh.Date_Created,'YYYYMMDD') Journal_Created_Date,
1886 TO_CHAR(Jeh.Default_Effective_Date,'YYYYMMDD') Journal_Date,
1887 Gp.Period_Num Accounting_Period_Number,
1888 Jeh.Je_Category Journal_Category_Number,
1889 NVL(round(Rct.Exchange_Rate,4),1) Exchange_Rate,
1890 Fcv.currency_code Currency_Code,
1891 Fcv.NAME Entered_Currency,
1892 Rct.comments Description,
1893 TO_CHAR(Rct.term_due_date,'YYYYMMDD') Due_Date,
1894 TO_CHAR(Rctlgd.Gl_Date,'YYYYMMDD') Applied_Date,
1895 'RECEIVABLE' Document_Type_Number,
1896 TO_CHAR(Rct.cust_trx_type_id) Transaction_Type_Number,
1897 Rct.trx_number Transaction_Number,
1898 DECODE(lv_contract_number_column,
1899 '1',Rct.interface_header_attribute1,'2',Rct.interface_header_attribute2,
1900 '3',Rct.interface_header_attribute3,'4',Rct.interface_header_attribute4,
1901 '5',Rct.interface_header_attribute5,'6',Rct.interface_header_attribute6,
1902 '7',Rct.interface_header_attribute7,'8',Rct.interface_header_attribute8,
1903 '9',Rct.interface_header_attribute9,'10',Rct.interface_header_attribute10,
1904 '11',Rct.interface_header_attribute11,'12',Rct.interface_header_attribute12,
1905 '13',Rct.interface_header_attribute13,'14',Rct.interface_header_attribute14,
1906 '15',Rct.interface_header_attribute15, NULL) Contract_Number,
1907 DECODE(Rct.interface_header_context,
1908 'PROJECTS INVOICES',
1909 Rct.interface_header_attribute1, NULL) Project_Number,
1910 TO_CHAR(Rct.RECEIPT_METHOD_ID) Settlement_Method_Number,
1911 NULL Payment_Date,
1912 NULL/*DECODE(Rct.attribute_category,'China Accounting', --Updated for for fixing bug# 9793920
1913 DECODE(lv_attribute_column4bill_num,
1914 'ATTRIBUTE1',Rct.ATTRIBUTE1,'ATTRIBUTE2',Rct.ATTRIBUTE2,
1915 'ATTRIBUTE3',Rct.ATTRIBUTE3,'ATTRIBUTE4',Rct.ATTRIBUTE4,
1916 'ATTRIBUTE5',Rct.ATTRIBUTE5,'ATTRIBUTE6',Rct.ATTRIBUTE6,
1917 'ATTRIBUTE7',Rct.ATTRIBUTE7,'ATTRIBUTE8',Rct.ATTRIBUTE8,
1918 'ATTRIBUTE9',Rct.ATTRIBUTE9,'ATTRIBUTE10',Rct.ATTRIBUTE10,
1919 'ATTRIBUTE11',Rct.ATTRIBUTE11,'ATTRIBUTE12',Rct.ATTRIBUTE12,
1920 'ATTRIBUTE13',Rct.ATTRIBUTE13,'ATTRIBUTE14',Rct.ATTRIBUTE14,
1921 'ATTRIBUTE15',Rct.ATTRIBUTE15,NULL), NULL)*/ Remittance_Bill_Number
1922 FROM RA_CUSTOMER_TRX_ALL Rct,
1923 RA_CUST_TRX_LINE_GL_DIST_ALL Rctlgd,
1924 Xla_Transaction_Entities Ent,
1925 Xla_Ae_Headers Aeh,
1926 Xla_Ae_Lines Ael,
1927 Gl_Import_References Gir,
1928 Gl_Je_Headers Jeh,
1929 Gl_Ledgers Gl,
1930 Gl_Periods Gp,
1931 fnd_currencies_vl Fcv
1932 WHERE Rct.customer_trx_id = ln_customer_trx_id --variable: ln_customer_trx_id
1933 AND Rct.CUSTOMER_TRX_ID = Rctlgd.CUSTOMER_TRX_ID
1934 AND Rctlgd.ACCOUNT_CLASS = 'REC'
1935 AND Rctlgd.LATEST_REC_FLAG = 'Y'
1936 --SLA condition
1937 AND Ent.Application_Id = 222
1938 AND Rct.Customer_Trx_Id = Ent.Source_Id_Int_1
1939 AND Ent.Entity_Code = 'TRANSACTIONS'
1940 AND Ent.Entity_Id = Aeh.Entity_Id
1941 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1942 AND Ael.Accounting_Class_Code = 'RECEIVABLE'
1943 --GL condition
1944 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
1945 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
1946 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
1947 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
1948 AND Jeh.Status = 'P' --only collecting posted transaction
1949 --Period condition
1950 AND Gl.period_set_name = Gp.period_set_name
1951 AND Gl.accounted_period_type = Gp.period_type
1952 AND Rctlgd.Gl_Date BETWEEN Gp.start_date AND Gp.End_date
1953 AND Gl.ledger_id = pn_ledger_id --parameter: pn_ledger_id
1954 --Currency condition
1955 AND Rct.invoice_currency_code = Fcv.currency_code;
1956
1957 --Cursor for getting Receipts information based on accounting period and
1958 --correspondance Transaction Id
1959 CURSOR receipt_cur
1960 (ln_customer_trx_id NUMBER
1961 ,ln_ae_header_id NUMBER
1962 ,lv_period_name VARCHAR2
1963 )
1964 IS
1965 SELECT DISTINCT
1966 Jeh.Je_Header_Id Je_Header_Id,
1967 Aeh.Ae_Header_Id Ae_Header_Id,
1968 TO_CHAR(Jeh.Date_Created,'YYYYMMDD') Journal_Created_Date,
1969 TO_CHAR(Jeh.Default_Effective_Date,'YYYYMMDD') Journal_Date,
1970 Gp.Period_Num Accounting_Period_Number,
1971 Jeh.Je_Category Journal_Category_Number,
1972 NVL(round(Acr.Exchange_Rate,4),1) Exchange_Rate,
1973 Acr.Cash_Receipt_Id Cash_Receipt_Id,
1974 Fcv.currency_code Currency_Code,
1975 Fcv.NAME Entered_Currency,
1976 Acr.Comments Description,
1977 NULL Due_Date,
1978 TO_CHAR(App.Gl_Date,'YYYYMMDD') Applied_Date,
1979 'RECEIPT' Document_Type_Number,
1980 Acr.Type Transaction_Type_Number,
1981 Acr.Receipt_Number Transaction_Number,
1982 Acr.Receipt_Method_Id Settlement_Method_Number,
1983 TO_CHAR(Acr.Receipt_Date,'YYYYMMDD') Payment_Date
1984 FROM AR_CASH_RECEIPTS_ALL Acr,
1985 AR_RECEIVABLE_APPLICATIONS_ALL App,
1986 Xla_Transaction_Entities Ent,
1987 Xla_Ae_Headers Aeh,
1988 Xla_Ae_Lines Ael,
1989 Gl_Je_Headers Jeh,
1990 Gl_Import_References Gir,
1991 Gl_Ledgers Gl,
1992 Gl_Periods Gp,
1993 fnd_currencies_vl Fcv
1994 WHERE Acr.Cash_Receipt_Id = App.Cash_Receipt_Id
1995 AND App.Applied_Customer_Trx_Id = ln_customer_trx_id -- variable: ln_customer_trx_id
1996 --SLA condition
1997 AND Ent.Application_Id = 222
1998 AND Acr.Cash_Receipt_Id = Ent.Source_Id_Int_1
1999 AND Ent.Entity_Code = 'RECEIPTS'
2000 AND Ent.Entity_Id = Aeh.Entity_Id
2001 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2002 AND Ael.Accounting_Class_Code = 'RECEIVABLE'
2003 --GL condition
2004 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
2005 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
2006 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
2007 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
2008 AND Jeh.Status = 'P' --only collecting posted payment
2009 --Period condition
2010 AND Gl.period_set_name = Gp.period_set_name
2011 AND Gl.accounted_period_type = Gp.period_type
2012 AND App.Gl_Date BETWEEN Gp.start_date AND Gp.End_date
2013 AND Gp.Period_Name = lv_period_name --variable: lv_period_name
2014 AND Gl.ledger_id = pn_ledger_id --parameter: pn_ledger_id
2015 AND Aeh.Accounting_Date = App.Gl_Date
2016 --Currency condition
2017 AND Acr.currency_code = Fcv.currency_code
2018 --EXISTS means that the customer_trx_id is derived from receipt,so
2019 --only this receipt need be exported.
2020 --NOT EXISTS means that the customer_trx_id is from Transaction,so
2021 --all the receipt applied to it need be exported.
2022 AND ((EXISTS(SELECT Aeh1.Ae_Header_Id
2023 FROM Xla_Transaction_Entities Ent1,
2024 Xla_Ae_Headers Aeh1
2025 WHERE Ent1.Entity_Code = 'RECEIPTS'
2026 AND Ent1.Entity_Id = Aeh1.Entity_Id
2027 AND Aeh1.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2028 AND Aeh1.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2029 AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2030 )
2031 OR NOT EXISTS(SELECT Aeh1.Ae_Header_Id
2032 FROM Xla_Transaction_Entities Ent1,
2033 Xla_Ae_Headers Aeh1
2034 WHERE Ent1.Entity_Code = 'RECEIPTS'
2035 AND Ent1.Entity_Id = Aeh1.Entity_Id
2036 AND Aeh1.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2037 AND Aeh1.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2038 )
2039 --NOT EXISTS means that the customer_trx_id not from CreditMemo
2040 AND NOT EXISTS(SELECT Aeh2.Ae_Header_Id
2041 FROM Xla_Transaction_Entities Ent2,
2042 Xla_Ae_Headers Aeh2,
2043 RA_CUSTOMER_TRX_ALL Rct2
2044 WHERE Ent2.Application_Id = 222
2045 AND Rct2.Customer_Trx_Id = Ent2.Source_Id_Int_1
2046 AND Ent2.Entity_Code = 'TRANSACTIONS'
2047 AND Ent2.Entity_Id = Aeh2.Entity_Id
2048 AND Aeh2.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2049 AND Aeh2.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2050 AND Rct2.Previous_Customer_Trx_Id IS NOT NULL) --variable: ln_ae_header_id
2051 --NOT EXISTS means that the customer_trx_id not from Adjustment
2052 AND NOT EXISTS(SELECT Aeh3.Ae_Header_Id
2053 FROM Xla_Transaction_Entities Ent3,
2054 Xla_Ae_Headers Aeh3
2055 WHERE Ent3.Entity_Code = 'ADJUSTMENTS'
2056 AND Ent3.Entity_Id = Aeh3.Entity_Id
2057 AND Aeh3.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2058 AND Aeh3.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2059 ORDER BY Aeh.Ae_Header_Id,Jeh.Je_Header_Id DESC;
2060
2061 --Cursor for getting Credit Memo information based on accounting period and
2062 --correspondance Transaction Id
2063 CURSOR credit_memo_cur
2064 (ln_customer_trx_id NUMBER
2065 ,ln_ae_header_id NUMBER
2066 ,lv_period_name VARCHAR2
2067 )
2068 IS
2069 SELECT DISTINCT
2070 Jeh.Je_Header_Id Je_Header_Id,
2071 Aeh.Ae_Header_Id Ae_Header_Id,
2072 Ael.Ae_Line_Num Ae_Line_Num,
2073 Rct.Customer_Trx_Id Customer_Trx_Id,
2074 TO_CHAR(Jeh.Date_Created,'YYYYMMDD') Journal_Created_Date,
2075 TO_CHAR(Jeh.Default_Effective_Date,'YYYYMMDD') Journal_Date,
2076 Gp.Period_Num Accounting_Period_Number,
2077 Jeh.Je_Category Journal_Category_Number,
2078 NVL(round(Rct.Exchange_Rate,4),1) Exchange_Rate,
2079 lv_period_name Period_Name,
2080 fcv.currency_code Currency_Code,
2081 fcv.NAME Entered_Currency,
2082 Rct.Comments Description,
2083 TO_CHAR(Rct.term_due_date,'YYYYMMDD') Due_Date,
2084 TO_CHAR(Rctlgd.Gl_Date,'YYYYMMDD') Applied_Date,
2085 'RECEIVABLE' Document_Type_Number,
2086 TO_CHAR(Rct.cust_trx_type_id) Transaction_Type_Number,
2087 Rct.Trx_Number Transaction_Number,
2088 DECODE(lv_contract_number_column,
2089 '1',Rct.interface_header_attribute1,'2',Rct.interface_header_attribute2,
2090 '3',Rct.interface_header_attribute3,'4',Rct.interface_header_attribute4,
2091 '5',Rct.interface_header_attribute5,'6',Rct.interface_header_attribute6,
2092 '7',Rct.interface_header_attribute7,'8',Rct.interface_header_attribute8,
2093 '9',Rct.interface_header_attribute9,'10',Rct.interface_header_attribute10,
2094 '11',Rct.interface_header_attribute11,'12',Rct.interface_header_attribute12,
2095 '13',Rct.interface_header_attribute13,'14',Rct.interface_header_attribute14,
2096 '15',Rct.interface_header_attribute15, NULL) Contract_Number,
2097 DECODE(Rct.interface_header_context,
2098 'PROJECTS INVOICES',
2099 Rct.interface_header_attribute1,
2100 NULL) Project_Number,
2101 TO_CHAR(Rct.RECEIPT_METHOD_ID) Settlement_Method_Number,
2102 NULL Payment_Date,
2103 NULL/*DECODE(Rct.attribute_category,'China Accounting', --Updated for for fixing bug# 9793920
2104 DECODE(lv_attribute_column4bill_num,
2105 'ATTRIBUTE1',Rct.ATTRIBUTE1,'ATTRIBUTE2',Rct.ATTRIBUTE2,
2106 'ATTRIBUTE3',Rct.ATTRIBUTE3,'ATTRIBUTE4',Rct.ATTRIBUTE4,
2107 'ATTRIBUTE5',Rct.ATTRIBUTE5,'ATTRIBUTE6',Rct.ATTRIBUTE6,
2108 'ATTRIBUTE7',Rct.ATTRIBUTE7,'ATTRIBUTE8',Rct.ATTRIBUTE8,
2109 'ATTRIBUTE9',Rct.ATTRIBUTE9,'ATTRIBUTE10',Rct.ATTRIBUTE10,
2110 'ATTRIBUTE11',Rct.ATTRIBUTE11,'ATTRIBUTE12',Rct.ATTRIBUTE12,
2111 'ATTRIBUTE13',Rct.ATTRIBUTE13,'ATTRIBUTE14',Rct.ATTRIBUTE14,
2112 'ATTRIBUTE15',Rct.ATTRIBUTE15,NULL), NULL)*/ Remittance_Bill_Number
2113 FROM RA_CUSTOMER_TRX_ALL Rct,
2114 RA_CUST_TRX_LINE_GL_DIST_ALL Rctlgd,
2115 Xla_Transaction_Entities Ent,
2116 Xla_Ae_Headers Aeh,
2117 Xla_Ae_Lines Ael,
2118 Gl_Import_References Gir,
2119 Gl_Je_Headers Jeh,
2120 Gl_Ledgers Gl,
2121 Gl_Periods Gp,
2122 fnd_currencies_vl fcv
2123 WHERE Rct.Previous_Customer_Trx_Id = ln_customer_trx_id -- variable: ln_customer_trx_id
2124 AND Rct.CUSTOMER_TRX_ID = Rctlgd.CUSTOMER_TRX_ID
2125 AND Rctlgd.ACCOUNT_CLASS = 'REC'
2126 AND Rctlgd.LATEST_REC_FLAG = 'Y'
2127 --SLA condition
2128 AND Ent.Application_Id = 222
2129 AND Rct.Customer_Trx_Id = Ent.Source_Id_Int_1
2130 AND Ent.Entity_Code = 'TRANSACTIONS'
2131 AND Ent.Entity_Id = Aeh.Entity_Id
2132 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2133 AND Ael.Accounting_Class_Code = 'RECEIVABLE'
2134 --GL condition
2135 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
2136 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
2137 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
2138 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
2139 AND Jeh.Status = 'P' --only collecting posted creditmemo
2140 --Period condition
2141 AND Gl.period_set_name = Gp.period_set_name
2142 AND Gl.accounted_period_type = Gp.period_type
2143 AND Rctlgd.Gl_Date BETWEEN Gp.start_date AND Gp.End_date
2144 AND Gp.Period_Name = lv_period_name --variable: lv_period_name
2145 AND Gl.ledger_id = pn_ledger_id --parameter: pn_ledger_id
2146 AND Aeh.Accounting_Date = Rctlgd.Gl_Date
2147 --currecny condition
2148 AND Rct.invoice_currency_code = fcv.currency_code
2149 --EXISTS means that the customer_trx_id is derived from creditmemo,so
2150 --only this creditmemo need be exported.
2151 --NOT EXISTS means that the customer_trx_id is from Transaction,so
2152 --all the creditmemo applied to it need be exported.
2153 AND ((EXISTS(SELECT Aeh1.Ae_Header_Id
2154 FROM Xla_Transaction_Entities Ent1,
2155 Xla_Ae_Headers Aeh1,
2156 RA_CUSTOMER_TRX_ALL Rct
2157 WHERE Ent1.Application_Id = 222
2158 AND Rct.Customer_Trx_Id = Ent1.Source_Id_Int_1
2159 AND Ent1.Entity_Code = 'TRANSACTIONS'
2160 AND Ent1.Entity_Id = Aeh1.Entity_Id
2161 AND Aeh1.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2162 AND Aeh1.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2163 AND Rct.Previous_Customer_Trx_Id IS NOT NULL) --variable: ln_ae_header_id
2164 AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2165 )
2166 OR NOT EXISTS(SELECT Aeh1.Ae_Header_Id
2167 FROM Xla_Transaction_Entities Ent1,
2168 Xla_Ae_Headers Aeh1,
2169 RA_CUSTOMER_TRX_ALL Rct
2170 WHERE Ent1.Application_Id = 222
2171 AND Rct.Customer_Trx_Id = Ent1.Source_Id_Int_1
2172 AND Ent1.Entity_Code = 'TRANSACTIONS'
2173 AND Ent1.Entity_Id = Aeh1.Entity_Id
2174 AND Aeh1.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2175 AND Aeh1.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2176 AND Rct.Previous_Customer_Trx_Id IS NOT NULL) --variable: ln_ae_header_id
2177 )
2178 --NOT EXISTS means that the customer_trx_id not from Receipt
2179 AND NOT EXISTS(SELECT Aeh2.Ae_Header_Id
2180 FROM Xla_Transaction_Entities Ent2,
2181 Xla_Ae_Headers Aeh2
2182 WHERE Ent2.Entity_Code = 'RECEIPTS'
2183 AND Ent2.Entity_Id = Aeh2.Entity_Id
2184 AND Aeh2.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2185 AND Aeh2.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2186 --NOT EXISTS means that the customer_trx_id not from Adjustment
2187 AND NOT EXISTS(SELECT Aeh3.Ae_Header_Id
2188 FROM Xla_Transaction_Entities Ent3,
2189 Xla_Ae_Headers Aeh3
2190 WHERE Ent3.Entity_Code = 'ADJUSTMENTS'
2191 AND Ent3.Entity_Id = Aeh3.Entity_Id
2192 AND Aeh3.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2193 AND Aeh3.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2194 ORDER BY Jeh.Je_Header_Id,Aeh.Ae_Header_Id,Ael.Ae_Line_Num;
2195
2196 --Cursor for getting Adjusment information based on accounting period and
2197 --correspondance Transaction Id
2198 CURSOR trx_adjustment_cur
2199 (ln_customer_trx_id NUMBER
2200 ,ln_ae_header_id NUMBER
2201 ,lv_period_name VARCHAR2
2202 )
2203 IS
2204 SELECT DISTINCT
2205 Jeh.Je_Header_Id Je_Header_Id,
2206 Aeh.Ae_Header_Id Ae_Header_Id,
2207 TO_CHAR(Jeh.Date_Created,'YYYYMMDD') Journal_Created_Date,
2208 TO_CHAR(Jeh.Default_Effective_Date,'YYYYMMDD') Journal_Date,
2209 Gp.Period_Num Accounting_Period_Number,
2210 Jeh.Je_Category Journal_Category_Number,
2211 Adj.Adjustment_Id Adjustment_Id,
2212 Adj.Comments Description,
2213 TO_CHAR(Adj.Gl_Date,'YYYYMMDD') Applied_Date
2214 FROM AR_ADJUSTMENTS_ALL Adj,
2215 Xla_Transaction_Entities Ent,
2216 Xla_Ae_Headers Aeh,
2217 Xla_Ae_Lines Ael,
2218 Gl_Import_References Gir,
2219 Gl_Je_Headers Jeh,
2220 Gl_Ledgers Gl,
2221 Gl_Periods Gp
2222 WHERE Adj.Customer_Trx_Id = ln_customer_trx_id -- variable: ln_customer_trx_id
2223 --SLA condition
2224 AND Ent.Application_Id = 222
2225 AND Adj.Adjustment_Id = Ent.Source_Id_Int_1
2226 AND Ent.Entity_Code = 'ADJUSTMENTS'
2227 AND Ent.Entity_Id = Aeh.Entity_Id
2228 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2229 AND Ael.Accounting_Class_Code = 'RECEIVABLE'
2230 --GL condition
2231 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
2232 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
2233 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
2234 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
2235 AND Jeh.Status = 'P' --only collecting posted adjustment
2236 --Period condition
2237 AND Gl.period_set_name = Gp.period_set_name
2238 AND Gl.accounted_period_type = Gp.period_type
2239 AND Adj.Gl_Date BETWEEN Gp.start_date AND Gp.End_date
2240 AND Gp.Period_Name = lv_period_name --variable: lv_period_name
2241 AND Gl.ledger_id = pn_ledger_id --parameter: pn_ledger_id
2242 AND Aeh.Accounting_Date = Adj.Gl_Date
2243 --EXISTS means that the customer_trx_id is derived from adjust,so
2244 --only this adjust need be exported.
2245 --NOT EXISTS means that the customer_trx_id is from Transaction,so
2246 --all Adjustments based on it need be exported.
2247 AND ((EXISTS(SELECT Aeh1.Ae_Header_Id
2248 FROM Xla_Transaction_Entities Ent1,
2249 Xla_Ae_Headers Aeh1
2250 WHERE Ent1.Entity_Code = 'ADJUSTMENTS'
2251 AND Ent1.Entity_Id = Aeh1.Entity_Id
2252 AND Aeh1.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2253 AND Aeh1.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2254 AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2255 )
2256 OR NOT EXISTS(SELECT Aeh1.Ae_Header_Id
2257 FROM Xla_Transaction_Entities Ent1,
2258 Xla_Ae_Headers Aeh1
2259 WHERE Ent1.Entity_Code = 'ADJUSTMENTS'
2260 AND Ent1.Entity_Id = Aeh1.Entity_Id
2261 AND Aeh1.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2262 AND Aeh1.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2263 )
2264 --NOT EXISTS means that the customer_trx_id not from CreditMemo
2265 AND NOT EXISTS(SELECT Aeh2.Ae_Header_Id
2266 FROM Xla_Transaction_Entities Ent2,
2267 Xla_Ae_Headers Aeh2,
2268 RA_CUSTOMER_TRX_ALL Rct2
2269 WHERE Ent2.Application_Id = 222
2270 AND Rct2.Customer_Trx_Id = Ent2.Source_Id_Int_1
2271 AND Ent2.Entity_Code = 'TRANSACTIONS'
2272 AND Ent2.Entity_Id = Aeh2.Entity_Id
2273 AND Aeh2.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2274 AND Aeh2.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2275 AND Rct2.Previous_Customer_Trx_Id IS NOT NULL) --variable: ln_ae_header_id
2276 --NOT EXISTS means that the customer_trx_id not from Receipt
2277 AND NOT EXISTS(SELECT Aeh3.Ae_Header_Id
2278 FROM Xla_Transaction_Entities Ent3,
2279 Xla_Ae_Headers Aeh3
2280 WHERE Ent3.Entity_Code = 'RECEIPTS'
2281 AND Ent3.Entity_Id = Aeh3.Entity_Id
2282 AND Aeh3.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2283 AND Aeh3.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2284 ORDER BY Jeh.Je_Header_Id,Aeh.Ae_Header_Id;
2285
2286 --Cursor for getting Journal_Number which derived from Journal Itemization table
2287 CURSOR journal_number_cur
2288 (ln_je_header_id NUMBER)
2289 IS
2290 --Update by Jianchao Chi, for cnaov2 upgrade 07-JAN-2011
2291 --Add ja_cn_voucher_number table, voucher number and legal entity
2292 --are fetched from ja_cn_voucher_number
2293 --The new one is
2294 SELECT DISTINCT voucher_number journal_number
2295 FROM ja_cn_voucher_number
2296 WHERE je_header_id = ln_je_header_id
2297 AND legal_entity_id = pn_legal_entity_id;
2298 --Comment by Jianchao Chi for V2 Upgrade, 07-JAN-2011
2299 --get the journal number from ja_vn_voucher_number
2300 --The previous one is:
2301 /*SELECT DISTINCT journal_number
2302 FROM ja_cn_journal_lines
2303 WHERE je_header_id = ln_je_header_id;*/
2304
2305 --Cursor for fetching 'Functional Amount' and 'Entered Amount' from SLA
2306 CURSOR amount_cur
2307 (ln_ae_header_id NUMBER)
2308 IS
2309 SELECT SUM(NVL(Ael.Accounted_Dr,0) + NVL(-1 * Ael.Accounted_Cr,0)) Functional_Amount,
2310 SUM(NVL(Ael.Entered_Dr,0) + NVL(-1 * Ael.Entered_Cr, 0)) Entered_Amount
2311 FROM Xla_Ae_Lines Ael,
2312 Xla_Ae_Headers Aeh
2313 WHERE Ael.Ae_Header_Id = Aeh.Ae_Header_Id
2314 AND Ael.Accounting_Class_Code = 'RECEIVABLE'
2315 AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2316 --BSV condition, one transaction having BSVs with different legal entity in distribution lines????
2317 AND EXISTS (SELECT llbg.Bal_Seg_Value
2318 FROM ja_cn_ledger_le_bsv_gt llbg
2319 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Ael.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
2320 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2321 AND llbg.Legal_Entity_Id = pn_legal_entity_id); --parameter: pn_legal_entity_id
2322
2323
2324 --Cursor for fetching 'Functional Amount' and 'Entered Amount' of Receipt from SLA
2325 CURSOR receipt_amount_cur
2326 (ln_ae_header_id NUMBER
2327 ,ln_customer_trx_id NUMBER)
2328 IS
2329 SELECT SUM(NVL(Ael.Accounted_Dr,0) + NVL(-1 * Ael.Accounted_Cr,0)) Functional_Amount,
2330 SUM(NVL(Ael.Entered_Dr,0) + NVL(-1 * Ael.Entered_Cr, 0)) Entered_Amount
2331 FROM Xla_Ae_Lines Ael
2332 WHERE Ael.Accounting_Class_Code = 'RECEIVABLE'
2333 AND Ael.Application_Id = 222
2334 AND (Ael.Ae_Header_Id,Ael.Ae_Line_Num) IN
2335 (SELECT Xdl.Ae_Header_Id,
2336 Xdl.Ae_Line_Num
2337 FROM AR_RECEIVABLE_APPLICATIONS_ALL App,
2338 Xla_Ae_Headers Aeh,
2339 Xla_Distribution_Links Xdl,
2340 Ar_Distributions_All Ada
2341 WHERE App.event_id = Aeh.event_id
2342 AND Aeh.ae_header_id = Xdl.ae_header_id
2343 AND Xdl.event_id = Aeh.event_id
2344 --AND Xdl.source_distribution_id_num_1 = Ada.line_id
2345 AND Xdl.Applied_To_Source_Id_Num_1 = App.applied_customer_trx_id
2346 AND Ada.source_id = App.receivable_application_id
2347 AND Ada.source_table = 'RA'
2348 AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2349 AND App.applied_customer_trx_id = ln_customer_trx_id
2350 )
2351 --BSV condition, one transaction having BSVs with different legal entity in distribution lines
2352 AND EXISTS (SELECT llbg.Bal_Seg_Value
2353 FROM ja_cn_ledger_le_bsv_gt llbg
2354 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Ael.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
2355 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2356 AND llbg.Legal_Entity_Id = pn_legal_entity_id); --parameter: pn_legal_entity_i
2357
2358 --Cursor for fetching 'Functional Amount' and 'Entered Amount' of Credit Memo from SLA
2359 CURSOR credit_memo_amount_cur
2360 (ln_ae_header_id NUMBER
2361 ,ln_ae_line_num NUMBER)
2362 IS
2363 SELECT SUM(NVL(Ael.Accounted_Dr,0) + NVL(-1 * Ael.Accounted_Cr,0)) Functional_Amount,
2364 SUM(NVL(Ael.Entered_Dr,0) + NVL(-1 * Ael.Entered_Cr, 0)) Entered_Amount
2365 FROM Xla_Ae_Lines Ael,
2366 Xla_Ae_Headers Aeh
2367 WHERE Ael.Ae_Header_Id = Aeh.Ae_Header_Id
2368 AND Ael.Accounting_Class_Code = 'RECEIVABLE'
2369 AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2370 AND Ael.Ae_Line_Num = ln_ae_line_num
2371 --BSV condition, one transaction having BSVs with different legal entity in distribution lines????
2372 AND EXISTS (SELECT llbg.Bal_Seg_Value
2373 FROM ja_cn_ledger_le_bsv_gt llbg
2374 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Ael.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
2375 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2376 AND llbg.Legal_Entity_Id = pn_legal_entity_id); --parameter: pn_legal_entity_id
2377
2378 --Cursor for getting the Attribute Column for storing the Remittance Bill Number and Balance Side
2379 CURSOR dff_assignments_cur
2380 IS
2381 SELECT *
2382 FROM ja_cn_dff_assignments
2383 WHERE chart_of_accounts_id = pn_coa_id
2384 AND (dff_title_code = 'ACBS' -- Balance Side
2385 OR dff_title_code = 'ARTR' -- Remittance Bill Number for Transaction
2386 );
2387
2388 --Cursor for getting the Attribute Column which used to store the Contract Number in Transaction
2389 CURSOR contract_number_cur
2390 IS
2391 SELECT DEFAULT_REFERENCE Contract_Number_Column
2392 FROM RA_BATCH_SOURCES_ALL
2393 WHERE NAME = 'ORDER ENTRY'
2394 AND org_id = pn_legal_entity_id;
2395
2396 BEGIN
2397 --logging for debug
2398 IF (ln_proc_level >= ln_dbg_level)
2399 THEN
2400 FND_LOG.STRING(ln_proc_level,
2401 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
2402 '.begin',
2403 'Enter procedure');
2404 -- logging the parameters
2405 FND_LOG.STRING(ln_proc_level,
2406 lv_procedure_name ||
2407 '.parameters',
2408 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
2409 'pn_ledger_id=' || pn_ledger_id || ',' ||
2410 'pv_accounting_year=' || pv_accounting_year || ',' ||
2411 'pn_coa_id=' || pn_coa_id || ',' ||
2412 'pv_period_from=' || pv_period_from || ',' ||
2413 'pv_period_to=' || pv_period_to);
2414 END IF; --ln_proc_level >= ln_dbg_level
2415
2416 --Begin: add for fixing bug 10041443
2417 SELECT DECODE(COUNT(Gp.Period_Name),
2418 0,
2419 'N', --Not first period, in this case, year begnning balance would not be exported
2420 'Y')--It is first period and year beginning balance should be exported
2421 INTO lv_first_period_flag
2422 FROM Gl_Ledgers Gl, Gl_Periods Gp
2423 WHERE Gl.period_set_name = Gp.period_set_name
2424 AND Gl.accounted_period_type = Gp.period_type
2425 AND Gp.period_year = TO_NUMBER(pv_accounting_year) --parameter: pv_accounting_year
2426 AND Gl.Ledger_Id = pn_ledger_id--parameter: pn_ledger_id
2427 AND Gp.Period_Name = pv_period_from--parameter: pv_period_from
2428 AND Gp.Start_Date =
2429 (SELECT MIN(Gp1.START_DATE)
2430 FROM Gl_Periods Gp1
2431 WHERE Gl.period_set_name = Gp1.period_set_name
2432 AND Gl.accounted_period_type = Gp1.period_type
2433 AND Gp1.period_year = Gp.period_year);
2434 IF lv_first_period_flag = 'Y'
2435 THEN
2436 BEGIN
2437 SELECT Gp.Period_Name, Gp.Period_Num
2438 INTO lv_first_period_name, ln_first_period_num
2439 FROM Gl_Ledgers Gl, Gl_Periods Gp
2440 WHERE Gl.period_set_name = Gp.period_set_name
2441 AND Gl.accounted_period_type = Gp.period_type
2442 AND Gp.period_year = TO_NUMBER(pv_accounting_year) --parameter: pv_accounting_year
2443 AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2444 AND Gp.Start_Date =
2445 (SELECT MIN(Gp1.START_DATE)
2446 FROM Gl_Periods Gp1
2447 WHERE Gl.period_set_name = Gp1.period_set_name
2448 AND Gl.accounted_period_type = Gp1.period_type
2449 AND Gp1.period_year = Gp.period_year);
2450 EXCEPTION
2451 WHEN NO_DATA_FOUND THEN
2452 NULL;
2453 END;
2454
2455 Get_Account_Number(pn_ledger_id => pn_ledger_id
2456 ,pn_legal_entity_id => pn_legal_entity_id
2457 ,pv_first_period => lv_first_period_name
2458 ,xa_receivable_account_num => la_receivable_acc_num_array);
2459
2460 lv_beginning_bal_exported_flag := Add_Year_Beginning_Balance(pn_ledger_id => pn_ledger_id
2461 ,pn_legal_entity_id => pn_legal_entity_id
2462 ,pn_coa_id => pn_coa_id
2463 ,pv_first_period => lv_first_period_name
2464 ,pv_accounting_period_number => TO_CHAR(ln_first_period_num)
2465 ,pv_accounting_year => pv_accounting_year
2466 ,pa_receivable_account_num => la_receivable_acc_num_array);
2467 END IF; --if it is period from is selected as first period
2468 --End: add for fixing bug 10041443
2469
2470 /*FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
2471 '.parameters:' ||
2472 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
2473 'pn_ledger_id=' || pn_ledger_id || ',' ||
2474 'pv_accounting_year=' || pv_accounting_year || ',' ||
2475 'pn_coa_id=' || pn_coa_id || ',' ||
2476 'pv_period_from=' || pv_period_from || ',' ||
2477 'pv_period_to=' || pv_period_to);*/
2478
2479 --1. Populate balancing segment values to table ja_cn_ledger_le_bsv_gt
2480 /*DELETE FROM JA_CN_LEDGER_LE_BSV_GT;
2481 COMMIT;
2482 IF Ja_Cn_Utility.Populate_Ledger_Le_Bsv_Gt(pn_Ledger_Id,pn_legal_entity_id) <> 'S'
2483 THEN
2484 RETURN;
2485 END IF;*/
2486
2487 --2. Fetch start date and end date according to the pv_accounting_year,
2488 --pv_period_from, pv_period_to.
2489 IF pv_period_from IS NOT NULL
2490 THEN
2491 SELECT start_date
2492 INTO ld_start_date
2493 FROM GL_PERIOD_STATUSES
2494 WHERE ledger_id = pn_ledger_id
2495 AND application_id = 101
2496 AND period_name = pv_period_from
2497 AND to_char(period_year) = pv_accounting_year;
2498 ELSE
2499 ld_start_date := to_date(pv_accounting_year||'0101','YYYYMMDD');
2500 END IF; --pv_period_from IS NOT NULL
2501
2502 IF pv_period_to IS NOT NULL
2503 THEN
2504 SELECT end_date
2505 INTO ld_end_date
2506 FROM GL_PERIOD_STATUSES
2507 WHERE ledger_id = pn_ledger_id
2508 AND application_id = 101
2509 AND period_name = pv_period_to
2510 AND to_char(period_year) = pv_accounting_year;
2511 ELSE
2512 ld_end_date := to_date(pv_accounting_year||'1231','YYYYMMDD');
2513 END IF; --pv_period_to IS NOT NULL
2514
2515 --logging the variables
2516 IF (ln_statement_level >= ln_dbg_level)
2517 THEN
2518 FND_LOG.STRING(ln_statement_level,
2519 lv_procedure_name,
2520 'ld_start_date=' || ld_start_date || ',' ||
2521 'ld_end_date=' || ld_end_date);
2522 END IF; --(ln_statement_level >= ln_dbg_level)
2523 /*FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
2524 '.variable:' ||
2525 'ld_start_date=' || ld_start_date || ',' ||
2526 'ld_end_date=' || ld_end_date);*/
2527
2528 --Fetch flex_value_set_id of CN_GL_ACCOUNT
2529 SELECT s.flex_value_set_id
2530 INTO ln_flex_value_set_id
2531 FROM fnd_id_flex_segments s
2532 WHERE s.application_id = 101
2533 AND s.id_flex_num = pn_coa_id
2534 AND s.id_flex_code = 'GL#'
2535 AND s.application_column_name =
2536 (SELECT application_column_name
2537 FROM fnd_segment_attribute_values
2538 WHERE application_id = 101
2539 AND segment_attribute_type = 'GL_ACCOUNT'
2540 AND attribute_value = 'Y'
2541 AND id_flex_num = pn_coa_id
2542 AND id_flex_code = 'GL#');
2543
2544 --logging the variables
2545 IF (ln_statement_level >= ln_dbg_level)
2546 THEN
2547 FND_LOG.STRING(ln_statement_level,
2548 lv_procedure_name,
2549 'ln_flex_value_set_id=' || ln_flex_value_set_id);
2550 END IF; --(ln_statement_level >= ln_dbg_level)
2551 /*FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
2552 '.variable:' ||
2553 'ln_flex_value_set_id=' || ln_flex_value_set_id);*/
2554
2555 --Fetch functional currency
2556 SELECT fnd_currencies_vl.NAME
2557 INTO lv_functional_currency
2558 FROM fnd_currencies_vl
2559 WHERE currency_code =
2560 (SELECT currency_code
2561 FROM gl_ledgers
2562 WHERE ledger_id = pn_ledger_id);
2563
2564 --logging the variables
2565 IF (ln_statement_level >= ln_dbg_level)
2566 THEN
2567 FND_LOG.STRING(ln_statement_level,
2568 lv_procedure_name,
2569 'lv_functional_currency=' || lv_functional_currency);
2570 END IF; --(ln_statement_level >= ln_dbg_level)
2571 /*FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
2572 '.variable:' ||
2573 'lv_functional_currency=' || lv_functional_currency);*/
2574
2575 --Fetch the attribute column for storing Remittance Bill Number and Balance Side
2576 OPEN dff_assignments_cur;
2577 LOOP
2578 FETCH dff_assignments_cur
2579 INTO lr_dff_assignments_row;
2580 EXIT WHEN dff_assignments_cur%NOTFOUND;
2581 CASE lr_dff_assignments_row.dff_title_code
2582 WHEN 'ACBS' THEN
2583 lv_attribute_column4bal_side := lr_dff_assignments_row.attribute_column;
2584 WHEN 'ARTR' THEN
2585 lv_attribute_column4bill_num := lr_dff_assignments_row.attribute_column;
2586 END CASE;
2587 END LOOP;
2588 CLOSE dff_assignments_cur;
2589
2590 --logging the variables
2591 IF (ln_statement_level >= ln_dbg_level)
2592 THEN
2593 FND_LOG.STRING(ln_statement_level,
2594 lv_procedure_name,
2595 'lv_attribute_column4bal_side=' || lv_attribute_column4bal_side || ',' ||
2596 'lv_attribute_column4bill_num=' || lv_attribute_column4bill_num);
2597 END IF; --(ln_statement_level >= ln_dbg_level)
2598 /*FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
2599 '.variable:' ||
2600 'lv_attribute_column4bal_side=' || lv_attribute_column4bal_side || ',' ||
2601 'lv_attribute_column4bill_num=' || lv_attribute_column4bill_num);*/
2602
2603 --Fetch the attribute column for storing contract number in transaction
2604 FOR v_contract_number_row IN contract_number_cur
2605 LOOP
2606 lv_contract_number_column := v_contract_number_row.Contract_Number_Column;
2607 END LOOP;
2608
2609 --logging the variables
2610 IF (ln_statement_level >= ln_dbg_level)
2611 THEN
2612 FND_LOG.STRING(ln_statement_level,
2613 lv_procedure_name,
2614 'lv_contract_number_column=' || lv_contract_number_column);
2615 END IF; --(ln_statement_level >= ln_dbg_level)
2616 /*FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
2617 '.variable:' ||
2618 'lv_contract_number_column=' || lv_contract_number_column);*/
2619
2620 --Add data to XML
2621 --Loop the Cursor for fetching AR Transaction data when the accounting class
2622 --is 'Receivable' in the accounting line in SLA, and data posted to GL.
2623 FOR v_gl_sla_row IN ar_gl_sla_cur
2624 LOOP
2625 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Loop ar_gl_sla_cur: Ae_Header_Id='||v_gl_sla_row.ae_header_id);
2626
2627 --Fetch the balance Side of account
2628 IF NVL(lv_current_account_number,'****') <> v_gl_sla_row.account_number THEN
2629 lv_current_account_number := v_gl_sla_row.account_number;
2630 lv_balance_side := Get_Account_Balance_Side(v_gl_sla_row.Account_Number,
2631 ln_flex_value_set_id,
2632 lv_attribute_column4bal_side);
2633 END IF; --end IF lv_account_number <> v_gl_sla_row.account_number
2634
2635 --Loop the Cursor for getting Transaction ID based on ae_header_id
2636 FOR v_customer_trx_id_row IN customer_trx_id_cur(v_gl_sla_row.ae_header_id)
2637 LOOP
2638 --FND_FILE.PUT_LINE(FND_FILE.LOG, ' Loop customer_trx_id_cur: Customer_Trx_Id='
2639 -- || v_customer_trx_id_row.customer_trx_id);
2640
2641 lv_transaction_exported_flag := 'N'; --Indicate transaction information has not been exported
2642
2643 --Loop the Cursor for getting accounting period for each Transaction and related Receipts
2644 FOR v_period_name_row IN period_name_cur(v_customer_trx_id_row.customer_trx_id)
2645 LOOP
2646 --FND_FILE.PUT_LINE(FND_FILE.LOG, ' Loop period_name_cur: period_name='||v_period_name_row.period_name);
2647
2648 --Loop the Cursor for getting the Transaction information based on the Transaction Id
2649 FOR v_transaction_row IN transaction_cur(v_customer_trx_id_row.customer_trx_id,
2650 v_period_name_row.period_name)
2651 LOOP
2652 --FND_FILE.PUT_LINE(FND_FILE.LOG, ' Loop transaction_cur: Transaction_Number='||v_transaction_row.Transaction_Number);
2653
2654 --Fetch the VAT Inovice Number for transaction from Golden Tax Invoice
2655 lv_vat_invoice_number := Get_VAT_Invoice_Number(v_customer_trx_id_row.customer_trx_id,
2656 v_period_name_row.period_name);
2657
2658 --Get the Clear Flag of a transaction
2659 lv_clear_flag := Get_Transaction_Clear_Flag(pn_ledger_id,
2660 v_period_name_row.end_date,
2661 v_customer_trx_id_row.customer_trx_id);
2662
2663 ln_current_receipt_id := 0;
2664 ln_current_ae_header_id := 0;
2665
2666 --Loop the Cursor for getting Receipts information based on accounting period and
2667 --correspondance Transaction Id
2668 FOR v_receipt_row IN receipt_cur(v_customer_trx_id_row.customer_trx_id,
2669 v_gl_sla_row.ae_header_id,
2670 v_period_name_row.period_name)
2671 LOOP
2672 --To judge whether the current receipt is already exported or not
2673 IF ln_current_receipt_id <> v_receipt_row.cash_receipt_id OR
2674 ln_current_ae_header_id <> v_receipt_row.ae_header_id
2675 THEN
2676 ln_current_receipt_id := v_receipt_row.cash_receipt_id;
2677 ln_current_ae_header_id := v_receipt_row.ae_header_id;
2678
2679 --FND_FILE.PUT_LINE(FND_FILE.LOG, ' Loop receipt_cur: Receipt_Number='||v_receipt_row.transaction_number);
2680 --FND_FILE.PUT_LINE(FND_FILE.LOG, ' ==>Export Transaction......for Receipt '||v_receipt_row.transaction_number);
2681
2682 ln_row_count := ln_row_count + 2; --one line for transaction and one line for receipt
2683 lv_transaction_exported_flag := 'Y';--Transaction information exported
2684
2685 --Add the correspondance Transaction information line of the reciept
2686 ln_journal_number := NULL;
2687 ln_functional_amount := NULL;
2688 ln_entered_amount := NULL;
2689 ln_applied_journal_number := NULL;
2690 --Get the Journal Number
2691 FOR v_journal_number_row IN journal_number_cur(v_transaction_row.Je_Header_Id)
2692 LOOP
2693 ln_journal_number := v_journal_number_row.Journal_Number;
2694 EXIT;
2695 END LOOP;
2696 --Get Functional Amount and Entered Amount
2697 FOR v_trx_amount_row IN amount_cur(v_transaction_row.ae_header_id)
2698 LOOP
2699 ln_functional_amount := v_trx_amount_row.Functional_Amount;
2700 ln_entered_amount := v_trx_amount_row.Entered_Amount;
2701 EXIT;
2702 END LOOP;
2703 --Get Applied Journal Number
2704 FOR v_applied_jour_num_row IN journal_number_cur(v_receipt_row.Je_Header_Id)
2705 LOOP
2706 ln_applied_journal_number := v_applied_jour_num_row.Journal_Number;
2707 EXIT;
2708 END LOOP;
2709 --Add XML data
2710 Add_XML_Node(pv_customer_number => v_gl_sla_row.Customer_Number
2711 ,pv_account_number => v_gl_sla_row.Account_Number
2712 ,pv_journal_created_date => v_transaction_row.Journal_Created_Date
2713 ,pv_journal_date => v_transaction_row.Journal_Date
2714 ,pv_accounting_year => v_gl_sla_row.Accounting_Year
2715 ,pv_accounting_period_number => TO_CHAR(v_transaction_row.Accounting_Period_Number)
2716 ,pv_journal_category_number => v_transaction_row.Journal_Category_Number
2717 ,pv_journal_number => TO_CHAR(ln_journal_number)
2718 ,pv_functional_currency => lv_functional_currency
2719 ,pn_exchange_rate => v_transaction_row.Exchange_Rate
2720 ,pv_balance_side => lv_balance_side
2721 ,pn_functional_balance => Get_Balance(v_gl_sla_row.party_id
2722 ,pn_ledger_id
2723 ,pn_legal_entity_id
2724 ,v_gl_sla_row.account_number
2725 ,v_transaction_row.currency_code
2726 ,v_period_name_row.period_name
2727 ,'F')
2728 ,pn_entered_balance => Get_Balance(v_gl_sla_row.party_id
2729 ,pn_ledger_id
2730 ,pn_legal_entity_id
2731 ,v_gl_sla_row.account_number
2732 ,v_transaction_row.currency_code
2733 ,v_period_name_row.period_name
2734 ,'E')
2735 ,pn_functional_amount => ln_functional_amount
2736 ,pv_entered_currency => v_transaction_row.Entered_Currency
2737 ,pn_entered_amount => ln_entered_amount
2738 ,pv_description => v_transaction_row.Description
2739 ,pv_due_date => v_transaction_row.Due_Date
2740 ,pv_applied_journal_number => TO_CHAR(ln_applied_journal_number)
2741 ,pv_applied_date => --v_receipt_row.Applied_Date
2742 --v_receipt_row.Journal_Date --Update for fixing bug 9962326
2743 NULL --Updated for fixing bug 10121399
2744 ,pv_document_type_number => v_transaction_row.Document_Type_Number
2745 ,pv_transaction_type_number => v_transaction_row.Transaction_Type_Number
2746 ,pv_transaction_number => v_transaction_row.Transaction_Number
2747 ,pv_invoice_number => lv_vat_invoice_number
2748 ,pv_contract_number => v_transaction_row.Contract_Number
2749 ,pv_project_number => v_transaction_row.Project_Number
2750 ,pv_settlement_method_number => v_transaction_row.Settlement_Method_Number
2751 ,pv_payment_date => v_transaction_row.Payment_Date
2752 ,pv_clear_flag => lv_clear_flag
2753 ,pv_remittance_bill_number => v_transaction_row.Remittance_Bill_Number);
2754
2755 --Add Receipt Information
2756 --FND_FILE.PUT_LINE(FND_FILE.LOG, ' ==>Export Receipt......for Receipt '||v_receipt_row.transaction_number);
2757
2758 ln_journal_number := NULL;
2759 ln_functional_amount := NULL;
2760 ln_entered_amount := NULL;
2761 ln_applied_journal_number := NULL;
2762 --Get the Journal Number
2763 FOR v_journal_number_row IN journal_number_cur(v_receipt_row.Je_Header_Id)
2764 LOOP
2765 ln_journal_number := v_journal_number_row.Journal_Number;
2766 EXIT;
2767 END LOOP;
2768 --Get Functional Amount and Entered Amount
2769 FOR v_receipt_amount_row IN receipt_amount_cur(v_receipt_row.ae_header_id,
2770 v_transaction_row.customer_trx_id)
2771 LOOP
2772 ln_functional_amount := v_receipt_amount_row.Functional_Amount;
2773 ln_entered_amount := v_receipt_amount_row.Entered_Amount;
2774 EXIT;
2775 END LOOP;
2776 --Get Applied Journal Number
2777 FOR v_applied_jour_num_row IN journal_number_cur(v_transaction_row.Je_Header_Id)
2778 LOOP
2779 ln_applied_journal_number := v_applied_jour_num_row.Journal_Number;
2780 EXIT;
2781 END LOOP;
2782 --Add XML data
2783 Add_XML_Node(pv_customer_number => v_gl_sla_row.Customer_Number
2784 ,pv_account_number => v_gl_sla_row.Account_Number
2785 ,pv_journal_created_date => v_receipt_row.Journal_Created_Date
2786 ,pv_journal_date => v_receipt_row.Journal_Date
2787 ,pv_accounting_year => v_gl_sla_row.Accounting_Year
2788 ,pv_accounting_period_number => TO_CHAR(v_receipt_row.Accounting_Period_Number)
2789 ,pv_journal_category_number => v_receipt_row.Journal_Category_Number
2790 ,pv_journal_number => TO_CHAR(ln_journal_number)
2791 ,pv_functional_currency => lv_functional_currency
2792 ,pn_exchange_rate => v_receipt_row.Exchange_Rate
2793 ,pv_balance_side => lv_balance_side
2794 ,pn_functional_balance => Get_Balance(v_gl_sla_row.party_id
2795 ,pn_ledger_id
2796 ,pn_legal_entity_id
2797 ,v_gl_sla_row.account_number
2798 ,v_receipt_row.currency_code
2799 ,v_period_name_row.period_name
2800 ,'F')
2801 ,pn_entered_balance => Get_Balance(v_gl_sla_row.party_id
2802 ,pn_ledger_id
2803 ,pn_legal_entity_id
2804 ,v_gl_sla_row.account_number
2805 ,v_receipt_row.currency_code
2806 ,v_period_name_row.period_name
2807 ,'E')
2808 ,pn_functional_amount => ln_functional_amount
2809 ,pv_entered_currency => v_receipt_row.Entered_Currency
2810 ,pn_entered_amount => ln_entered_amount
2811 ,pv_description => v_receipt_row.Description
2812 ,pv_due_date => --v_receipt_row.Due_Date
2813 NULL --Updated for fixing bug 10121399
2814 ,pv_applied_journal_number => TO_CHAR(ln_applied_journal_number)
2815 ,pv_applied_date => --v_transaction_row.Applied_Date
2816 v_receipt_row.Journal_Date --Update for fixing bug 9962326
2817 ,pv_document_type_number => v_receipt_row.Document_Type_Number
2818 ,pv_transaction_type_number => v_receipt_row.Transaction_Type_Number
2819 ,pv_transaction_number => v_receipt_row.Transaction_Number
2820 ,pv_invoice_number => lv_vat_invoice_number
2821 ,pv_contract_number => v_transaction_row.Contract_Number
2822 ,pv_project_number => v_transaction_row.Project_Number
2823 ,pv_settlement_method_number => v_receipt_row.Settlement_Method_Number
2824 ,pv_payment_date => v_receipt_row.Payment_Date
2825 ,pv_clear_flag => lv_clear_flag
2826 ,pv_remittance_bill_number => Get_Remittance_Bill_Number(pn_coa_id --Updated for fixing bug# 9747676
2827 ,v_receipt_row.cash_receipt_id)
2828 );
2829 END IF; --ln_current_receipt_id <> v_receipt_row.cash_receipt_id
2830 END LOOP; --end receipt_cur
2831
2832 --Loop the Cursor for getting Credit Memo information based on accounting period and
2833 --correspondance Transaction Id
2834 FOR v_credit_memo_row IN credit_memo_cur(v_customer_trx_id_row.customer_trx_id,
2835 v_gl_sla_row.ae_header_id,
2836 v_period_name_row.period_name)
2837 LOOP
2838 --FND_FILE.PUT_LINE(FND_FILE.LOG, ' Loop credit_memo_cur: Credit_Memo_Id='
2839 -- ||v_credit_memo_row.Customer_Trx_Id||',Ae_Line_Num='||v_credit_memo_row.Ae_Line_Num);
2840
2841 ln_row_count := ln_row_count + 4; --one line for transaction and three line for Credit Memo
2842 lv_transaction_exported_flag := 'Y';--Transaction information exported
2843
2844 --The same credit memo only need to export one correspondance transaction
2845 IF ln_cm_customer_trx_id <> v_credit_memo_row.customer_trx_id
2846 THEN
2847 ln_cm_customer_trx_id := v_credit_memo_row.customer_trx_id;
2848
2849 --FND_FILE.PUT_LINE(FND_FILE.LOG, ' ==>Export Transaction......for Credit Memo '||v_credit_memo_row.Transaction_Number);
2850
2851 --Add the correspondance Transaction information line of the reciept
2852 ln_journal_number := NULL;
2853 ln_functional_amount := NULL;
2854 ln_entered_amount := NULL;
2855 ln_applied_journal_number := NULL;
2856 --Get the Journal Number
2857 FOR v_journal_number_row IN journal_number_cur(v_transaction_row.Je_Header_Id)
2858 LOOP
2859 ln_journal_number := v_journal_number_row.Journal_Number;
2860 EXIT;
2861 END LOOP;
2862 --Get Functional Amount and Entered Amount
2863 FOR v_trx_amount_row IN amount_cur(v_transaction_row.ae_header_id)
2864 LOOP
2865 ln_functional_amount := v_trx_amount_row.Functional_Amount;
2866 ln_entered_amount := v_trx_amount_row.Entered_Amount;
2867 EXIT;
2868 END LOOP;
2869 --Get Applied Journal Number
2870 FOR v_applied_jour_num_row IN journal_number_cur(v_credit_memo_row.Je_Header_Id)
2871 LOOP
2872 ln_applied_journal_number := v_applied_jour_num_row.Journal_Number;
2873 EXIT;
2874 END LOOP;
2875 --Add XML data
2876 Add_XML_Node(pv_customer_number => v_gl_sla_row.Customer_Number
2877 ,pv_account_number => v_gl_sla_row.Account_Number
2878 ,pv_journal_created_date => v_transaction_row.Journal_Created_Date
2879 ,pv_journal_date => v_transaction_row.Journal_Date
2880 ,pv_accounting_year => v_gl_sla_row.Accounting_Year
2881 ,pv_accounting_period_number => TO_CHAR(v_transaction_row.Accounting_Period_Number)
2882 ,pv_journal_category_number => v_transaction_row.Journal_Category_Number
2883 ,pv_journal_number => TO_CHAR(ln_journal_number)
2884 ,pv_functional_currency => lv_functional_currency
2885 ,pn_exchange_rate => v_transaction_row.Exchange_Rate
2886 ,pv_balance_side => lv_balance_side
2887 ,pn_functional_balance => Get_Balance(v_gl_sla_row.party_id
2888 ,pn_ledger_id
2889 ,pn_legal_entity_id
2890 ,v_gl_sla_row.account_number
2891 ,v_transaction_row.currency_code
2892 ,v_period_name_row.period_name
2893 ,'F')
2894 ,pn_entered_balance => Get_Balance(v_gl_sla_row.party_id
2895 ,pn_ledger_id
2896 ,pn_legal_entity_id
2897 ,v_gl_sla_row.account_number
2898 ,v_transaction_row.currency_code
2899 ,v_period_name_row.period_name
2900 ,'E')
2901 ,pn_functional_amount => ln_functional_amount
2902 ,pv_entered_currency => v_transaction_row.Entered_Currency
2903 ,pn_entered_amount => ln_entered_amount
2904 ,pv_description => v_transaction_row.Description
2905 ,pv_due_date => v_transaction_row.Due_Date
2906 ,pv_applied_journal_number => TO_CHAR(ln_applied_journal_number)
2907 ,pv_applied_date => --v_credit_memo_row.Applied_Date
2908 --v_credit_memo_row.Journal_Date --Update for fixing bug 9962326
2909 NULL --Updated for fixing bug 10121399
2910 ,pv_document_type_number => v_transaction_row.Document_Type_Number
2911 ,pv_transaction_type_number => v_transaction_row.Transaction_Type_Number
2912 ,pv_transaction_number => v_transaction_row.Transaction_Number
2913 ,pv_invoice_number => lv_vat_invoice_number
2914 ,pv_contract_number => v_transaction_row.Contract_Number
2915 ,pv_project_number => v_transaction_row.Project_Number
2916 ,pv_settlement_method_number => v_transaction_row.Settlement_Method_Number
2917 ,pv_payment_date => v_transaction_row.Payment_Date
2918 ,pv_clear_flag => lv_clear_flag
2919 ,pv_remittance_bill_number => v_transaction_row.Remittance_Bill_Number);
2920 END IF; --ln_cm_customer_trx_id <> v_credit_memo_row.customer_trx_id
2921
2922 --FND_FILE.PUT_LINE(FND_FILE.LOG, ' ==>Export Credit Memo......for Credit Memo '||',Ae_Line_Num='||v_credit_memo_row.Ae_Line_Num);
2923 --Add Credit Memo Information
2924 ln_journal_number := NULL;
2925 ln_functional_amount := NULL;
2926 ln_entered_amount := NULL;
2927 ln_applied_journal_number := NULL;
2928 --Get the Journal Number
2929 FOR v_journal_number_row IN journal_number_cur(v_credit_memo_row.Je_Header_Id)
2930 LOOP
2931 ln_journal_number := v_journal_number_row.Journal_Number;
2932 EXIT;
2933 END LOOP;
2934 --Get Functional Amount and Entered Amount
2935 FOR v_cm_amount_row IN credit_memo_amount_cur(v_credit_memo_row.ae_header_id,
2936 v_credit_memo_row.ae_line_num)
2937 LOOP
2938 ln_functional_amount := v_cm_amount_row.Functional_Amount;
2939 ln_entered_amount := v_cm_amount_row.Entered_Amount;
2940 EXIT;
2941 END LOOP;
2942 --Get Applied Journal Number
2943 FOR v_applied_jour_num_row IN journal_number_cur(v_transaction_row.Je_Header_Id)
2944 LOOP
2945 ln_applied_journal_number := v_applied_jour_num_row.Journal_Number;
2946 EXIT;
2947 END LOOP;
2948 --Add XML data
2949 Add_XML_Node(pv_customer_number => v_gl_sla_row.Customer_Number
2950 ,pv_account_number => v_gl_sla_row.Account_Number
2951 ,pv_journal_created_date => v_credit_memo_row.Journal_Created_Date
2952 ,pv_journal_date => v_credit_memo_row.Journal_Date
2953 ,pv_accounting_year => v_gl_sla_row.Accounting_Year
2954 ,pv_accounting_period_number => TO_CHAR(v_credit_memo_row.Accounting_Period_Number)
2955 ,pv_journal_category_number => v_credit_memo_row.Journal_Category_Number
2956 ,pv_journal_number => TO_CHAR(ln_journal_number)
2957 ,pv_functional_currency => lv_functional_currency
2958 ,pn_exchange_rate => v_credit_memo_row.Exchange_Rate
2959 ,pv_balance_side => lv_balance_side
2960 ,pn_functional_balance => Get_Balance(v_gl_sla_row.party_id
2961 ,pn_ledger_id
2962 ,pn_legal_entity_id
2963 ,v_gl_sla_row.account_number
2964 ,v_credit_memo_row.currency_code
2965 ,v_period_name_row.period_name
2966 ,'F')
2967 ,pn_entered_balance => Get_Balance(v_gl_sla_row.party_id
2968 ,pn_ledger_id
2969 ,pn_legal_entity_id
2970 ,v_gl_sla_row.account_number
2971 ,v_credit_memo_row.currency_code
2972 ,v_period_name_row.period_name
2973 ,'E')
2974 ,pn_functional_amount => ln_functional_amount
2975 ,pv_entered_currency => v_credit_memo_row.Entered_Currency
2976 ,pn_entered_amount => ln_entered_amount
2977 ,pv_description => v_credit_memo_row.Description
2978 ,pv_due_date => --v_credit_memo_row.Due_Date
2979 NULL --Updated for fixing bug 10121399
2980 ,pv_applied_journal_number => TO_CHAR(ln_applied_journal_number)
2981 ,pv_applied_date => --v_transaction_row.Applied_Date
2982 v_credit_memo_row.Journal_Date --Update for fixing bug 9962326
2983 ,pv_document_type_number => v_credit_memo_row.Document_Type_Number
2984 ,pv_transaction_type_number => v_credit_memo_row.Transaction_Type_Number
2985 ,pv_transaction_number => v_credit_memo_row.Transaction_Number
2986 ,pv_invoice_number => lv_vat_invoice_number
2987 ,pv_contract_number => v_credit_memo_row.Contract_Number
2988 ,pv_project_number => v_credit_memo_row.Project_Number
2989 ,pv_settlement_method_number => v_credit_memo_row.Settlement_Method_Number
2990 ,pv_payment_date => v_credit_memo_row.Payment_Date
2991 ,pv_clear_flag => lv_clear_flag
2992 ,pv_remittance_bill_number => v_credit_memo_row.Remittance_Bill_Number);
2993
2994 END LOOP; --end credit_memo_cur
2995
2996 --If there is no reciept or credit momo applied to the transaction, and the current period
2997 --is the period when the transaction created, then the Transaction
2998 --information need to be exported seperately.
2999 IF lv_transaction_exported_flag = 'N' AND
3000 (TO_DATE(v_transaction_row.Applied_Date,'YYYYMMDD')
3001 BETWEEN v_period_name_row.Start_Date AND v_period_name_row.End_Date)
3002 THEN
3003 ln_row_count := ln_row_count + 1;
3004
3005 --FND_FILE.PUT_LINE(FND_FILE.LOG, ' ==>Export Transaction separately......Transaction_Number='||v_transaction_row.Transaction_Number);
3006
3007 ln_journal_number := NULL;
3008 ln_functional_amount := NULL;
3009 ln_entered_amount := NULL;
3010 ln_applied_journal_number := NULL;
3011 --Get the Journal Number
3012 FOR v_journal_number_row IN journal_number_cur(v_transaction_row.Je_Header_Id)
3013 LOOP
3014 ln_journal_number := v_journal_number_row.Journal_Number;
3015 EXIT;
3016 END LOOP;
3017 --Get Functional Amount and Entered Amount
3018 FOR v_trx_amount_row IN amount_cur(v_transaction_row.ae_header_id)
3019 LOOP
3020 ln_functional_amount := v_trx_amount_row.Functional_Amount;
3021 ln_entered_amount := v_trx_amount_row.Entered_Amount;
3022 EXIT;
3023 END LOOP;
3024 --Add XML data
3025 Add_XML_Node(pv_customer_number => v_gl_sla_row.Customer_Number
3026 ,pv_account_number => v_gl_sla_row.Account_Number
3027 ,pv_journal_created_date => v_transaction_row.Journal_Created_Date
3028 ,pv_journal_date => v_transaction_row.Journal_Date
3029 ,pv_accounting_year => v_gl_sla_row.Accounting_Year
3030 ,pv_accounting_period_number => TO_CHAR(v_transaction_row.Accounting_Period_Number)
3031 ,pv_journal_category_number => v_transaction_row.Journal_Category_Number
3032 ,pv_journal_number => TO_CHAR(ln_journal_number)
3033 ,pv_functional_currency => lv_functional_currency
3034 ,pn_exchange_rate => v_transaction_row.Exchange_Rate
3035 ,pv_balance_side => lv_balance_side
3036 ,pn_functional_balance => Get_Balance(v_gl_sla_row.party_id
3037 ,pn_ledger_id
3038 ,pn_legal_entity_id
3039 ,v_gl_sla_row.account_number
3040 ,v_transaction_row.currency_code
3041 ,v_period_name_row.period_name
3042 ,'F')
3043 ,pn_entered_balance => Get_Balance(v_gl_sla_row.party_id
3044 ,pn_ledger_id
3045 ,pn_legal_entity_id
3046 ,v_gl_sla_row.account_number
3047 ,v_transaction_row.currency_code
3048 ,v_period_name_row.period_name
3049 ,'E')
3050 ,pn_functional_amount => ln_functional_amount
3051 ,pv_entered_currency => v_transaction_row.Entered_Currency
3052 ,pn_entered_amount => ln_entered_amount
3053 ,pv_description => v_transaction_row.Description
3054 ,pv_due_date => v_transaction_row.Due_Date
3055 ,pv_applied_journal_number => NULL
3056 ,pv_applied_date => NULL
3057 ,pv_document_type_number => v_transaction_row.Document_Type_Number
3058 ,pv_transaction_type_number => v_transaction_row.Transaction_Type_Number
3059 ,pv_transaction_number => v_transaction_row.Transaction_Number
3060 ,pv_invoice_number => lv_vat_invoice_number
3061 ,pv_contract_number => v_transaction_row.Contract_Number
3062 ,pv_project_number => v_transaction_row.Project_Number
3063 ,pv_settlement_method_number => v_transaction_row.Settlement_Method_Number
3064 ,pv_payment_date => v_transaction_row.Payment_Date
3065 ,pv_clear_flag => lv_clear_flag
3066 ,pv_remittance_bill_number => v_transaction_row.Remittance_Bill_Number);
3067 END IF; --IF lv_transaction_exported_flag = 'N'
3068
3069 --Loop the Cursor for getting Adjustment information based on accounting period and
3070 --correspondance Transaction Id
3071 FOR v_trx_adjustment_row IN trx_adjustment_cur(v_customer_trx_id_row.customer_trx_id,
3072 v_gl_sla_row.ae_header_id,
3073 v_period_name_row.period_name)
3074 LOOP
3075 --FND_FILE.PUT_LINE(FND_FILE.LOG, ' Loop trx_adjustment_cur: Adjustment_Id='||v_trx_adjustment_row.Adjustment_Id);
3076 --FND_FILE.PUT_LINE(FND_FILE.LOG, ' ==>Export Adjustment ......Adjustment_Id='||v_trx_adjustment_row.Adjustment_Id);
3077
3078 ln_row_count := ln_row_count + 1;
3079
3080 ln_journal_number := NULL;
3081 ln_functional_amount := NULL;
3082 ln_entered_amount := NULL;
3083 ln_applied_journal_number := NULL;
3084 --Get the Journal Number
3085 FOR v_journal_number_row IN journal_number_cur(v_trx_adjustment_row.Je_Header_Id)
3086 LOOP
3087 ln_journal_number := v_journal_number_row.Journal_Number;
3088 EXIT;
3089 END LOOP;
3090 --Get Functional Amount and Entered Amount
3091 FOR v_trx_adjust_amount_row IN amount_cur(v_trx_adjustment_row.ae_header_id)
3092 LOOP
3093 ln_functional_amount := v_trx_adjust_amount_row.Functional_Amount;
3094 ln_entered_amount := v_trx_adjust_amount_row.Entered_Amount;
3095 EXIT;
3096 END LOOP;
3097 --Get Applied Journal Number
3098 FOR v_applied_jour_num_row IN journal_number_cur(v_transaction_row.Je_Header_Id)
3099 LOOP
3100 ln_applied_journal_number := v_applied_jour_num_row.Journal_Number;
3101 EXIT;
3102 END LOOP;
3103 --Add XML data
3104 Add_XML_Node(pv_customer_number => v_gl_sla_row.Customer_Number
3105 ,pv_account_number => v_gl_sla_row.Account_Number
3106 ,pv_journal_created_date => v_trx_adjustment_row.Journal_Created_Date
3107 ,pv_journal_date => v_trx_adjustment_row.Journal_Date
3108 ,pv_accounting_year => v_gl_sla_row.Accounting_Year
3109 ,pv_accounting_period_number => TO_CHAR(v_trx_adjustment_row.Accounting_Period_Number)
3110 ,pv_journal_category_number => v_trx_adjustment_row.Journal_Category_Number
3111 ,pv_journal_number => TO_CHAR(ln_journal_number)
3112 ,pv_functional_currency => lv_functional_currency
3113 ,pn_exchange_rate => v_transaction_row.Exchange_Rate
3114 ,pv_balance_side => lv_balance_side
3115 ,pn_functional_balance => Get_Balance(v_gl_sla_row.party_id
3116 ,pn_ledger_id
3117 ,pn_legal_entity_id
3118 ,v_gl_sla_row.account_number
3119 ,v_transaction_row.currency_code
3120 ,v_period_name_row.period_name
3121 ,'F')
3122 ,pn_entered_balance => Get_Balance(v_gl_sla_row.party_id
3123 ,pn_ledger_id
3124 ,pn_legal_entity_id
3125 ,v_gl_sla_row.account_number
3126 ,v_transaction_row.currency_code
3127 ,v_period_name_row.period_name
3128 ,'E')
3129 ,pn_functional_amount => ln_functional_amount
3130 ,pv_entered_currency => v_transaction_row.Entered_Currency
3131 ,pn_entered_amount => ln_entered_amount
3132 ,pv_description => v_trx_adjustment_row.Description
3133 ,pv_due_date => --v_transaction_row.Due_Date
3134 NULL --Updated for fixing bug 10121399
3135 ,pv_applied_journal_number => TO_CHAR(ln_applied_journal_number)
3136 ,pv_applied_date => --v_trx_adjustment_row.Applied_Date
3137 v_trx_adjustment_row.Journal_Date --Update for fixing bug 9962326
3138 ,pv_document_type_number => v_transaction_row.Document_Type_Number
3139 ,pv_transaction_type_number => v_transaction_row.Transaction_Type_Number
3140 ,pv_transaction_number => v_transaction_row.Transaction_Number
3141 ,pv_invoice_number => lv_vat_invoice_number
3142 ,pv_contract_number => v_transaction_row.Contract_Number
3143 ,pv_project_number => v_transaction_row.Project_Number
3144 ,pv_settlement_method_number => v_transaction_row.Settlement_Method_Number
3145 ,pv_payment_date => v_transaction_row.Payment_Date
3146 ,pv_clear_flag => lv_clear_flag
3147 ,pv_remittance_bill_number => v_transaction_row.Remittance_Bill_Number);
3148 END LOOP; -- end trx_adjustment_cur
3149
3150 END LOOP; --end transaction_cur
3151 END LOOP; --end period_name_cur
3152 END LOOP; --end customer_trx_id_cur
3153 END LOOP; --end ar_gl_sla_cur
3154
3155 -- To judge if the Cursor fetchs data. If the row count is 0 and the
3156 -- minOccurs=0 specified in xml schema, then need to handle it and raise exception
3157 IF ln_row_count = 0 AND lv_beginning_bal_exported_flag = 'N' --Update for fixing bug 10041443
3158 THEN
3159 Ja_Cn_Utility.Print_No_Data_Found_For_Log('RECEIVABLE_DETAIL_REPORT',
3160 Ja_Cn_Utility.GV_MODULE_APAR);
3161
3162 Add_XML_Node(pv_customer_number => NULL
3163 ,pv_account_number => NULL
3164 ,pv_journal_created_date => NULL
3165 ,pv_journal_date => NULL
3166 ,pv_accounting_year => NULL
3167 ,pv_accounting_period_number => NULL
3168 ,pv_journal_category_number => NULL
3169 ,pv_journal_number => NULL
3170 ,pv_functional_currency => NULL
3171 ,pn_exchange_rate => NULL
3172 ,pv_balance_side => NULL
3173 ,pn_functional_balance => NULL
3174 ,pn_entered_balance => NULL
3175 ,pn_functional_amount => NULL
3176 ,pv_entered_currency => NULL
3177 ,pn_entered_amount => NULL
3178 ,pv_description => NULL
3179 ,pv_due_date => NULL
3180 ,pv_applied_journal_number => NULL
3181 ,pv_applied_date => NULL
3182 ,pv_document_type_number => NULL
3183 ,pv_transaction_type_number => NULL
3184 ,pv_transaction_number => NULL
3185 ,pv_invoice_number => NULL
3186 ,pv_contract_number => NULL
3187 ,pv_project_number => NULL
3188 ,pv_settlement_method_number => NULL
3189 ,pv_payment_date => NULL
3190 ,pv_clear_flag => NULL
3191 ,pv_remittance_bill_number => NULL);
3192 END IF; --IF (ln_row_count = 0)
3193
3194 --logging for debug
3195 IF (ln_proc_level >= ln_dbg_level)
3196 THEN
3197 FND_LOG.STRING(ln_proc_level,
3198 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
3199 'Exit procedure');
3200 END IF; -- (ln_proc_level>=ln_dbg_level)
3201
3202 --To handle the No Data Found Exception
3203 EXCEPTION
3204 WHEN OTHERS THEN
3205 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3206 THEN
3207 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3208 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
3209 '.Other_Exception ',
3210 SQLCODE || SQLERRM);
3211 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3212 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
3213 RAISE;
3214
3215 END Add_Receivables_Balance_Detail;
3216
3217 END JA_CN_AR_RBD_EXPORT_PKG;
3218