[Home] [Help]
PACKAGE BODY: APPS.JA_CN_AP_PBD_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_AP_PBD_EXPORT_PKG AS
2 --$Header: JACNPBDB.pls 120.11.12020000.8 2013/04/09 09:04:56 chongwan ship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNPBDB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| To export receivable balance and details for each customer |
13 --| as per liability accounts, such as "Account Payable", |
14 --| "Payable in Advance", "Bill Payable", And "Other Payable", |
15 --| by period. |
16 --| |
17 --| PROCEDURE LIST |
18 --| PROCEDURE Add_XML_Node |
19 --| PROCEDURE Add_Payables_Balance_Detail |
20 --| |
21 --| FUNCTION LIST |
22 --| FUNCTION Get_Accounting_Period_Number |
23 --| FUNCTION Get_Prepaid_Account_Number |
24 --| FUNCTION Get_Journal_Number |
25 --| FUNCTION Get_Liability_Amount |
26 --| FUNCTION Get_Prepaid_Amount |
27 --| FUNCTION Get_Liability_Balance |
28 --| FUNCTION Get_Prepaid_Balance |
29 --| FUNCTION Get_Balance_Side |
30 --| FUNCTION Get_Remittance_Bill_Number |
31 --| FUNCTION Get_Due_Date |
32 --| FUNCTION Get_Liability_Clear_Flag |
33 --| FUNCTION Get_Prepaid_Clear_Flag |
34 --| |
35 --| HISTORY |
36 --| 06-Apr-2010 Chaoqun Wu created |
37 --| 26-May-2010 Chaoqun Wu Updated for fixing bug# 9747676, too |
38 --| many prepaid lines are exported |
39 --| 02-Jun-2010 Chaoqun Wu Updated for fixing bug# 9747960, clear |
40 --| flag is incorrect for multiple invoices |
41 --| applied to the same payment case |
42 --| 12-Jun-2010 Chaoqun Wu Updated for new solution described in |
43 --| bug# 9793920, new solution to populate |
44 --| remittance bill number |
45 --| 02-Aug-2010 Chaoqun Wu Updated for new solution descibed in |
46 --| bug# 9962326, new solution to populate |
47 --| applied date. |
48 --| 10-Aug-2010 Chaoqun Wu Fix bug 10008055, duplicate records |
49 --| occures when start date defined |
50 --| differently for a period in those ledger|
51 --| with the same period type. |
52 --| 24-Aug-2010 Chaoqun Wu Updated for new solution described in |
53 --| bug# 10041443, to poluplate year |
54 --| beginning balance |
55 --| 17-Sep-2010 Chaoqun Wu Updated for solution changes about Due |
56 --| Date and Applied Date described in bug |
57 --| 10121399 |
58 --| 06-Jan-2011 Jianchao Chi Updated for new solution to change the |
59 --| logic of Voucher Number and Legal |
60 --| Entity |
61 --| 20-Jun-2011 Choli updated, fix bug 12635156 |
62 --+======================================================================*/
63
64 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_AP_PBD_EXPORT_PKG';
65 TYPE ACCOUNT_NUMBER_TBL IS VARRAY(100) OF VARCHAR2(50); --Fixing bug 10041443
66
67 --==========================================================================
68 -- PROCEDURE NAME:
69 --
70 -- Add_XML_Node Public
71 --
72 -- DESCRIPTION:
73 --
74 -- To add payables balance detail information into xml nodes
75 --
76 -- PARAMETERS:
77 -- In: pv_supplier_number IN VARCHAR2 supplier number
78 -- pv_account_number IN VARCHAR2 account number
79 -- pv_journal_created_date IN VARCHAR2 journal created date
80 -- pv_journal_date IN VARCHAR2 journal date
81 -- pv_accounting_year IN VARCHAR2 accounting year
82 -- pv_accounting_period_number IN VARCHAR2 accounting period number
83 -- pv_journal_category_number IN VARCHAR2 journal category number
84 -- pv_journal_number IN VARCHAR2 journal number
85 -- pv_functional_currency IN VARCHAR2 functional currency
86 -- pn_exchange_rate IN NUMBER exchage rate
87 -- pv_balance_side IN VARCHAR2 balance side
88 -- pn_functional_balance IN NUMBER functional balance
89 -- pn_entered_balance IN NUMBER entered balance
90 -- pn_functional_amount IN NUMBER functional amount
91 -- pv_entered_currency IN VARCHAR2 entered currency
92 -- pn_entered_amount IN NUMBER entered amount
93 -- pv_description IN VARCHAR2 description
94 -- pv_due_date IN VARCHAR2 due date
95 -- pv_applied_journal_number IN VARCHAR2 applied journal number
96 -- pv_applied_date IN VARCHAR2 applied date
97 -- pv_document_type_number IN VARCHAR2 document type number
98 -- pv_transaction_type_number IN VARCHAR2 transaction type number
99 -- pv_transaction_number IN VARCHAR2 transaction number
100 -- pv_invoice_number IN VARCHAR2 invoice number
101 -- pv_contract_number IN VARCHAR2 contract number
102 -- pv_project_number IN VARCHAR2 project number
103 -- pv_settlement_method_number IN VARCHAR2 settlement method number
104 -- pv_payment_date IN VARCHAR2 payment date
105 -- pv_clear_flag IN VARCHAR2 clear flag
106 -- pv_remittance_bill_number IN VARCHAR2 remittance bill number
107 --
108 -- DESIGN REFERENCES:
109 -- APAR_Chaoqun.doc
110 --
111 -- CHANGE HISTORY:
112 -- 06-Apr-2010 Chaoqun Wu created
113 --==========================================================================
114
115 PROCEDURE Add_XML_Node(pv_supplier_number IN VARCHAR2,
116 pv_account_number IN VARCHAR2,
117 pv_journal_created_date IN VARCHAR2,
118 pv_journal_date IN VARCHAR2,
119 pv_accounting_year IN VARCHAR2,
120 pv_accounting_period_number IN VARCHAR2,
121 pv_journal_category_number IN VARCHAR2,
122 pv_journal_number IN VARCHAR2,
123 pv_functional_currency IN VARCHAR2,
124 pn_exchange_rate IN NUMBER,
125 pv_balance_side IN VARCHAR2,
126 pn_functional_balance IN NUMBER,
127 pn_entered_balance IN NUMBER,
128 pn_functional_amount IN NUMBER,
129 pv_entered_currency IN VARCHAR2,
130 pn_entered_amount IN NUMBER,
131 pv_description IN VARCHAR2,
132 pv_due_date IN VARCHAR2,
133 pv_applied_journal_number IN VARCHAR2,
134 pv_applied_date IN VARCHAR2,
135 pv_document_type_number IN VARCHAR2,
136 pv_transaction_type_number IN VARCHAR2,
137 pv_transaction_number IN VARCHAR2,
138 pv_invoice_number IN VARCHAR2,
139 pv_contract_number IN VARCHAR2,
140 pv_project_number IN VARCHAR2,
141 pv_settlement_method_number IN VARCHAR2,
142 pv_payment_date IN VARCHAR2,
143 pv_clear_flag IN VARCHAR2,
144 pv_remittance_bill_number IN VARCHAR2) IS
145 lv_procedure_name VARCHAR2(40) := 'Add_XML_Node';
146 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
147 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
148
149 BEGIN
150 --logging for debug
151 IF (ln_proc_level >= ln_dbg_level) THEN
152 FND_LOG.STRING(ln_proc_level,
153 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
154 '.begin',
155 'Enter procedure');
156 END IF; --ln_proc_level>=ln_dbg_level
157
158 Ja_Cn_Utility.Add_Sub_Root_Node('PAYABLE_DETAIL_REPORT',
159 Ja_Cn_Utility.GV_TAG_TYPE_START,
160 Ja_Cn_Utility.GV_MODULE_APAR);
161 Ja_Cn_Utility.Add_Child_Node('SUPPLIER_NUMBER',
162 pv_supplier_number,
163 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
164 Ja_Cn_Utility.GV_REQUIRED_YES,
165 Ja_Cn_Utility.GV_MODULE_APAR);
166 Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER',
167 pv_account_number,
168 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
169 Ja_Cn_Utility.GV_REQUIRED_YES,
170 Ja_Cn_Utility.GV_MODULE_APAR);
171 Ja_Cn_Utility.Add_Fixed_Child_Node('JOURNAL_CREATED_DATE',
172 pv_journal_created_date,
173 8,
174 Ja_Cn_Utility.GV_REQUIRED_YES,
175 Ja_Cn_Utility.GV_MODULE_APAR);
176 Ja_Cn_Utility.Add_Fixed_Child_Node('JOURNAL_DATE',
177 pv_journal_date,
178 8,
179 Ja_Cn_Utility.GV_REQUIRED_YES,
180 Ja_Cn_Utility.GV_MODULE_APAR);
181 Ja_Cn_Utility.Add_Fixed_Child_Node('ACCOUNTING_YEAR',
182 pv_accounting_year,
183 4,
184 Ja_Cn_Utility.GV_REQUIRED_YES,
185 Ja_Cn_Utility.GV_MODULE_APAR);
186 Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER',
187 pv_accounting_period_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('JOURNAL_CATEGORY_NUMBER',
192 pv_journal_category_number,
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('JOURNAL_NUMBER',
197 pv_journal_number,
198 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
199 Ja_Cn_Utility.GV_REQUIRED_YES,
200 Ja_Cn_Utility.GV_MODULE_APAR);
201 Ja_Cn_Utility.Add_Child_Node('FUNCTIONAL_CURRENCY',
202 pv_functional_currency,
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('EXCHANGE_RATE',
207 pn_exchange_rate,
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('BALANCE_SIDE',
212 pv_balance_side,
213 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
214 Ja_Cn_Utility.GV_REQUIRED_YES,
215 Ja_Cn_Utility.GV_MODULE_APAR);
216 Ja_Cn_Utility.Add_Child_Node('FUNCTIONAL_BALANCE',
217 pn_functional_balance,
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_BALANCE',
222 pn_entered_balance,
223 Ja_Cn_Utility.GV_TYPE_NUMBER,
224 Ja_Cn_Utility.GV_REQUIRED_YES,
225 Ja_Cn_Utility.GV_MODULE_APAR);
226 Ja_Cn_Utility.Add_Child_Node('FUNCTIONAL_AMOUNT',
227 pn_functional_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('ENTERED_CURRENCY',
232 pv_entered_currency,
233 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
234 Ja_Cn_Utility.GV_REQUIRED_YES,
235 Ja_Cn_Utility.GV_MODULE_APAR);
236 Ja_Cn_Utility.Add_Child_Node('ENTERED_AMOUNT',
237 pn_entered_amount,
238 Ja_Cn_Utility.GV_TYPE_NUMBER,
239 Ja_Cn_Utility.GV_REQUIRED_YES,
240 Ja_Cn_Utility.GV_MODULE_APAR);
241 Ja_Cn_Utility.Add_Child_Node('DESCRIPTION',
242 pv_description,
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('DUE_DATE',
247 pv_due_date,
248 8,
249 Ja_Cn_Utility.GV_REQUIRED_NO,
250 Ja_Cn_Utility.GV_MODULE_APAR);
251 Ja_Cn_Utility.Add_Child_Node('APPLIED_JOURNAL_NUMBER',
252 pv_applied_journal_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_Fixed_Child_Node('APPLIED_DATE',
257 pv_applied_date,
258 8,
259 Ja_Cn_Utility.GV_REQUIRED_NO,
260 Ja_Cn_Utility.GV_MODULE_APAR);
261 Ja_Cn_Utility.Add_Child_Node('DOCUMENT_TYPE_NUMBER',
262 pv_document_type_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('TRANSACTION_TYPE_NUMBER',
267 pv_transaction_type_number,
268 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
269 Ja_Cn_Utility.GV_REQUIRED_YES,
270 Ja_Cn_Utility.GV_MODULE_APAR);
271 Ja_Cn_Utility.Add_Child_Node('TRANSACTION_NUMBER',
272 pv_transaction_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('INVOICE_NUMBER',
277 pv_invoice_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('CONTRACT_NUMBER',
282 pv_contract_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_Child_Node('PROJECT_NUMBER',
287 pv_project_number,
288 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
289 Ja_Cn_Utility.GV_REQUIRED_NO,
290 Ja_Cn_Utility.GV_MODULE_APAR);
291 Ja_Cn_Utility.Add_Child_Node('SETTLEMENT_METHOD_NUMBER',
292 pv_settlement_method_number,
293 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
294 Ja_Cn_Utility.GV_REQUIRED_NO,
295 Ja_Cn_Utility.GV_MODULE_APAR);
296 Ja_Cn_Utility.Add_Fixed_Child_Node('PAYMENT_DATE',
297 pv_payment_date,
298 8,
299 Ja_Cn_Utility.GV_REQUIRED_NO,
300 Ja_Cn_Utility.GV_MODULE_APAR);
301 Ja_Cn_Utility.Add_Fixed_Child_Node('CLEAR_FLAG',
302 pv_clear_flag,
303 1,
304 Ja_Cn_Utility.GV_REQUIRED_YES,
305 Ja_Cn_Utility.GV_MODULE_APAR);
306 Ja_Cn_Utility.Add_Child_Node('REMITTANCE_BILL_NUMBER',
307 pv_remittance_bill_number,
308 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
309 Ja_Cn_Utility.GV_REQUIRED_NO,
310 Ja_Cn_Utility.GV_MODULE_APAR);
311 Ja_Cn_Utility.Add_Sub_Root_Node('PAYABLE_DETAIL_REPORT',
312 Ja_Cn_Utility.GV_TAG_TYPE_END,
313 Ja_Cn_Utility.GV_MODULE_APAR);
314 --logging for debug
315 IF (ln_proc_level >= ln_dbg_level) THEN
316 FND_LOG.STRING(ln_proc_level,
317 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
318 'Exit procedure');
319 END IF; -- (ln_proc_level>=ln_dbg_level)
320
321 END Add_XML_Node;
322
323 --==========================================================================
324 -- PROCEDURE NAME:
325 --
326 -- Add_YBB_XML_Node Public
327 --
328 -- DESCRIPTION:
329 --
330 -- To add payables balance detail information into xml nodes
331 --
332 -- PARAMETERS:
333 -- In: pv_supplier_number IN VARCHAR2 supplier number
334 -- pv_account_number IN VARCHAR2 account number
335 -- pv_journal_created_date IN VARCHAR2 journal created date
336 -- pv_journal_date IN VARCHAR2 journal date
337 -- pv_accounting_year IN VARCHAR2 accounting year
338 -- pv_accounting_period_number IN VARCHAR2 accounting period number
339 -- pv_journal_category_number IN VARCHAR2 journal category number
340 -- pv_journal_number IN VARCHAR2 journal number
341 -- pv_functional_currency IN VARCHAR2 functional currency
342 -- pn_exchange_rate IN NUMBER exchage rate
343 -- pv_balance_side IN VARCHAR2 balance side
344 -- pn_functional_balance IN NUMBER functional balance
345 -- pn_entered_balance IN NUMBER entered balance
346 -- pn_functional_amount IN NUMBER functional amount
347 -- pv_entered_currency IN VARCHAR2 entered currency
348 -- pn_entered_amount IN NUMBER entered amount
349 -- pv_description IN VARCHAR2 description
350 -- pv_due_date IN VARCHAR2 due date
351 -- pv_applied_journal_number IN VARCHAR2 applied journal number
352 -- pv_applied_date IN VARCHAR2 applied date
353 -- pv_document_type_number IN VARCHAR2 document type number
354 -- pv_transaction_type_number IN VARCHAR2 transaction type number
355 -- pv_transaction_number IN VARCHAR2 transaction number
356 -- pv_invoice_number IN VARCHAR2 invoice number
357 -- pv_contract_number IN VARCHAR2 contract number
358 -- pv_project_number IN VARCHAR2 project number
359 -- pv_settlement_method_number IN VARCHAR2 settlement method number
360 -- pv_payment_date IN VARCHAR2 payment date
361 -- pv_clear_flag IN VARCHAR2 clear flag
362 -- pv_remittance_bill_number IN VARCHAR2 remittance bill number
363 --
364 -- DESIGN REFERENCES:
365 -- APAR_Chaoqun.doc
366 --
367 -- CHANGE HISTORY:
368 -- 24-Aug-2010 Chaoqun Wu created for new solution described in bug 10041443
369 --==========================================================================
370
371 PROCEDURE Add_YBB_XML_Node(pv_supplier_number IN VARCHAR2,
372 pv_account_number IN VARCHAR2,
373 pv_journal_created_date IN VARCHAR2,
374 pv_journal_date IN VARCHAR2,
375 pv_accounting_year IN VARCHAR2,
376 pv_accounting_period_number IN VARCHAR2,
377 pv_journal_category_number IN VARCHAR2,
378 pv_journal_number IN VARCHAR2,
379 pv_functional_currency IN VARCHAR2,
380 pn_exchange_rate IN NUMBER,
381 pv_balance_side IN VARCHAR2,
382 pn_functional_balance IN NUMBER,
383 pn_entered_balance IN NUMBER,
384 pn_functional_amount IN NUMBER,
385 pv_entered_currency IN VARCHAR2,
386 pn_entered_amount IN NUMBER,
387 pv_description IN VARCHAR2,
388 pv_due_date IN VARCHAR2,
389 pv_applied_journal_number IN VARCHAR2,
390 pv_applied_date IN VARCHAR2,
391 pv_document_type_number IN VARCHAR2,
392 pv_transaction_type_number IN VARCHAR2,
393 pv_transaction_number IN VARCHAR2,
394 pv_invoice_number IN VARCHAR2,
395 pv_contract_number IN VARCHAR2,
396 pv_project_number IN VARCHAR2,
397 pv_settlement_method_number IN VARCHAR2,
398 pv_payment_date IN VARCHAR2,
399 pv_clear_flag IN VARCHAR2,
400 pv_remittance_bill_number IN VARCHAR2) IS
401 lv_procedure_name VARCHAR2(40) := 'Add_YBB_XML_Node';
402 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
403 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
404
405 BEGIN
406 --logging for debug
407 IF (ln_proc_level >= ln_dbg_level) THEN
408 FND_LOG.STRING(ln_proc_level,
409 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
410 '.begin',
411 'Enter procedure');
412 END IF; --ln_proc_level>=ln_dbg_level
413
414 Ja_Cn_Utility.Add_Sub_Root_Node('PAYABLE_DETAIL_REPORT',
415 Ja_Cn_Utility.GV_TAG_TYPE_START,
416 Ja_Cn_Utility.GV_MODULE_APAR);
417 Ja_Cn_Utility.Add_Child_Node('SUPPLIER_NUMBER',
418 pv_supplier_number,
419 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
420 Ja_Cn_Utility.GV_REQUIRED_YES,
421 Ja_Cn_Utility.GV_MODULE_APAR);
422 Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER',
423 pv_account_number,
424 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
425 Ja_Cn_Utility.GV_REQUIRED_YES,
426 Ja_Cn_Utility.GV_MODULE_APAR);
427 Ja_Cn_Utility.Add_Child_Node('JOURNAL_CREATED_DATE',
428 pv_journal_created_date,
429 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
430 Ja_Cn_Utility.GV_REQUIRED_YES,
431 Ja_Cn_Utility.GV_MODULE_APAR);
432 Ja_Cn_Utility.Add_Child_Node('JOURNAL_DATE',
433 pv_journal_date,
434 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
435 Ja_Cn_Utility.GV_REQUIRED_YES,
436 Ja_Cn_Utility.GV_MODULE_APAR);
437 Ja_Cn_Utility.Add_Fixed_Child_Node('ACCOUNTING_YEAR',
438 pv_accounting_year,
439 4,
440 Ja_Cn_Utility.GV_REQUIRED_YES,
441 Ja_Cn_Utility.GV_MODULE_APAR);
442 Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER',
443 pv_accounting_period_number,
444 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
445 Ja_Cn_Utility.GV_REQUIRED_YES,
446 Ja_Cn_Utility.GV_MODULE_APAR);
447 Ja_Cn_Utility.Add_Child_Node('JOURNAL_CATEGORY_NUMBER',
448 pv_journal_category_number,
449 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
450 Ja_Cn_Utility.GV_REQUIRED_YES,
451 Ja_Cn_Utility.GV_MODULE_APAR);
452 Ja_Cn_Utility.Add_Child_Node('JOURNAL_NUMBER',
453 pv_journal_number,
454 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
455 Ja_Cn_Utility.GV_REQUIRED_YES,
456 Ja_Cn_Utility.GV_MODULE_APAR);
457 Ja_Cn_Utility.Add_Child_Node('FUNCTIONAL_CURRENCY',
458 pv_functional_currency,
459 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
460 Ja_Cn_Utility.GV_REQUIRED_YES,
461 Ja_Cn_Utility.GV_MODULE_APAR);
462 Ja_Cn_Utility.Add_Child_Node('EXCHANGE_RATE',
463 pn_exchange_rate,
464 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
465 Ja_Cn_Utility.GV_REQUIRED_YES,
466 Ja_Cn_Utility.GV_MODULE_APAR);
467 Ja_Cn_Utility.Add_Child_Node('BALANCE_SIDE',
468 pv_balance_side,
469 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
470 Ja_Cn_Utility.GV_REQUIRED_YES,
471 Ja_Cn_Utility.GV_MODULE_APAR);
472 Ja_Cn_Utility.Add_Child_Node('FUNCTIONAL_BALANCE',
473 pn_functional_balance,
474 Ja_Cn_Utility.GV_TYPE_NUMBER,
475 Ja_Cn_Utility.GV_REQUIRED_NO,
476 Ja_Cn_Utility.GV_MODULE_APAR);
477 Ja_Cn_Utility.Add_Child_Node('ENTERED_BALANCE',
478 pn_entered_balance,
479 Ja_Cn_Utility.GV_TYPE_NUMBER,
480 Ja_Cn_Utility.GV_REQUIRED_NO,
481 Ja_Cn_Utility.GV_MODULE_APAR);
482 Ja_Cn_Utility.Add_Child_Node('FUNCTIONAL_AMOUNT',
483 pn_functional_amount,
484 Ja_Cn_Utility.GV_TYPE_NUMBER,
485 Ja_Cn_Utility.GV_REQUIRED_YES,
486 Ja_Cn_Utility.GV_MODULE_APAR);
487 Ja_Cn_Utility.Add_Child_Node('ENTERED_CURRENCY',
488 pv_entered_currency,
489 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
490 Ja_Cn_Utility.GV_REQUIRED_YES,
491 Ja_Cn_Utility.GV_MODULE_APAR);
492 Ja_Cn_Utility.Add_Child_Node('ENTERED_AMOUNT',
493 pn_entered_amount,
494 Ja_Cn_Utility.GV_TYPE_NUMBER,
495 Ja_Cn_Utility.GV_REQUIRED_YES,
496 Ja_Cn_Utility.GV_MODULE_APAR);
497 Ja_Cn_Utility.Add_Child_Node('DESCRIPTION',
498 pv_description,
499 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
500 Ja_Cn_Utility.GV_REQUIRED_NO,
501 Ja_Cn_Utility.GV_MODULE_APAR);
502 Ja_Cn_Utility.Add_Fixed_Child_Node('DUE_DATE',
503 pv_due_date,
504 8,
505 Ja_Cn_Utility.GV_REQUIRED_NO,
506 Ja_Cn_Utility.GV_MODULE_APAR);
507 Ja_Cn_Utility.Add_Child_Node('APPLIED_JOURNAL_NUMBER',
508 pv_applied_journal_number,
509 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
510 Ja_Cn_Utility.GV_REQUIRED_NO,
511 Ja_Cn_Utility.GV_MODULE_APAR);
512 Ja_Cn_Utility.Add_Fixed_Child_Node('APPLIED_DATE',
513 pv_applied_date,
514 8,
515 Ja_Cn_Utility.GV_REQUIRED_NO,
516 Ja_Cn_Utility.GV_MODULE_APAR);
517 Ja_Cn_Utility.Add_Child_Node('DOCUMENT_TYPE_NUMBER',
518 pv_document_type_number,
519 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
520 Ja_Cn_Utility.GV_REQUIRED_NO,
521 Ja_Cn_Utility.GV_MODULE_APAR);
522 Ja_Cn_Utility.Add_Child_Node('TRANSACTION_TYPE_NUMBER',
523 pv_transaction_type_number,
524 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
525 Ja_Cn_Utility.GV_REQUIRED_YES,
526 Ja_Cn_Utility.GV_MODULE_APAR);
527 Ja_Cn_Utility.Add_Child_Node('TRANSACTION_NUMBER',
528 pv_transaction_number,
529 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
530 Ja_Cn_Utility.GV_REQUIRED_NO,
531 Ja_Cn_Utility.GV_MODULE_APAR);
532 Ja_Cn_Utility.Add_Child_Node('INVOICE_NUMBER',
533 pv_invoice_number,
534 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
535 Ja_Cn_Utility.GV_REQUIRED_NO,
536 Ja_Cn_Utility.GV_MODULE_APAR);
537 Ja_Cn_Utility.Add_Child_Node('CONTRACT_NUMBER',
538 pv_contract_number,
539 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
540 Ja_Cn_Utility.GV_REQUIRED_NO,
541 Ja_Cn_Utility.GV_MODULE_APAR);
542 Ja_Cn_Utility.Add_Child_Node('PROJECT_NUMBER',
543 pv_project_number,
544 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
545 Ja_Cn_Utility.GV_REQUIRED_NO,
546 Ja_Cn_Utility.GV_MODULE_APAR);
547 Ja_Cn_Utility.Add_Child_Node('SETTLEMENT_METHOD_NUMBER',
548 pv_settlement_method_number,
549 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
550 Ja_Cn_Utility.GV_REQUIRED_NO,
551 Ja_Cn_Utility.GV_MODULE_APAR);
552 Ja_Cn_Utility.Add_Fixed_Child_Node('PAYMENT_DATE',
553 pv_payment_date,
554 8,
555 Ja_Cn_Utility.GV_REQUIRED_NO,
556 Ja_Cn_Utility.GV_MODULE_APAR);
557 Ja_Cn_Utility.Add_Fixed_Child_Node('CLEAR_FLAG',
558 pv_clear_flag,
559 1,
560 Ja_Cn_Utility.GV_REQUIRED_YES,
561 Ja_Cn_Utility.GV_MODULE_APAR);
562 Ja_Cn_Utility.Add_Child_Node('REMITTANCE_BILL_NUMBER',
563 pv_remittance_bill_number,
564 Ja_Cn_Utility.GV_TYPE_VARCHAR2,
565 Ja_Cn_Utility.GV_REQUIRED_NO,
566 Ja_Cn_Utility.GV_MODULE_APAR);
567 Ja_Cn_Utility.Add_Sub_Root_Node('PAYABLE_DETAIL_REPORT',
568 Ja_Cn_Utility.GV_TAG_TYPE_END,
569 Ja_Cn_Utility.GV_MODULE_APAR);
570 --logging for debug
571 IF (ln_proc_level >= ln_dbg_level) THEN
572 FND_LOG.STRING(ln_proc_level,
573 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
574 'Exit procedure');
575 END IF; -- (ln_proc_level>=ln_dbg_level)
576
577 END Add_YBB_XML_Node;
578
579 --==========================================================================
580 -- PROCEDURE NAME:
581 --
582 -- Get_Supplier_Number Public
583 --
584 -- DESCRIPTION:
585 --
586 -- To get supplier number, including customer number from AR Refund
587 --
588 -- PARAMETERS:
589 -- In: pv_supplier_num IN VARCHAR2 supplier number
590 -- pn_invoice_id IN NUMBER invoice id
591 --
592 -- DESIGN REFERENCES:
593 -- APAR_Chaoqun.doc
594 --
595 -- CHANGE HISTORY:
596 -- 04-May-2010 Chaoqun Wu created
597 --==========================================================================
598 FUNCTION Get_Supplier_Number(pv_supplier_num IN VARCHAR2,
599 pn_invoice_id IN NUMBER) RETURN VARCHAR2 IS
600 lv_supplier_num VARCHAR(30) := pv_supplier_num;
601 lv_procedure_name VARCHAR2(40) := 'Get_Supplier_Number';
602 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
603 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
604
605 CURSOR customer_num_cur IS
606 SELECT DISTINCT part.party_number Customer_Number
607 FROM HZ_CUST_ACCOUNTS cust, HZ_PARTIES part, AP_INVOICES_ALL aia
608 WHERE cust.cust_account_id = aia.party_id
609 AND cust.party_id = part.party_id
610 AND aia.Invoice_Id = pn_invoice_id; --parameter: pn_invoice_id
611
612 BEGIN
613 --logging for debug
614 IF (ln_proc_level >= ln_dbg_level) THEN
615 FND_LOG.STRING(ln_proc_level,
616 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
617 '.begin',
618 'Enter procedure');
619 END IF; --ln_proc_level>=ln_dbg_level
620
621 IF lv_supplier_num IS NULL THEN
622 FOR v_customer_num_row IN customer_num_cur LOOP
623 lv_supplier_num := v_customer_num_row.Customer_Number;
624 EXIT;
625 END LOOP;
626 END IF;
627
628 --logging for debug
629 IF (ln_proc_level >= ln_dbg_level) THEN
630 FND_LOG.STRING(ln_proc_level,
631 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
632 'Exit procedure');
633 END IF; -- (ln_proc_level>=ln_dbg_level)
634
635 RETURN lv_supplier_num;
636 END Get_Supplier_Number;
637
638 --==========================================================================
639 -- PROCEDURE NAME:
640 --
641 -- Get_Accounting_Period_Number Public
642 --
643 -- DESCRIPTION:
644 --
645 -- To get accounting period number
646 --
647 -- PARAMETERS:
648 -- In: pn_ledger_id IN NUMBER ledger id
649 -- pn_invoice_id IN NUMBER invoice id
650 -- pv_period_name IN VARCHAR2 accounting period name
651 --
652 -- DESIGN REFERENCES:
653 -- APAR_Chaoqun.doc
654 --
655 -- CHANGE HISTORY:
656 -- 04-May-2010 Chaoqun Wu created
657 --==========================================================================
658 FUNCTION Get_Accounting_Period_Number(pn_ledger_id IN NUMBER,
659 pn_invoice_id IN NUMBER,
660 pv_period_name IN VARCHAR2)
661 RETURN VARCHAR2 IS
662 lv_period_num VARCHAR(5) := NULL;
663 lv_period_name VARCHAR(25) := pv_period_name;
664 lv_procedure_name VARCHAR2(40) := 'Get_Accounting_Period_Number';
665 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
666 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
667
668 CURSOR period_num_cur(lv_period_name VARCHAR2) IS
669 SELECT TO_CHAR(Gp.Period_Num)
670 FROM Gl_Periods Gp, Gl_Ledgers Gl
671 WHERE Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
672 AND Gl.period_set_name = Gp.period_set_name
673 AND Gl.accounted_period_type = Gp.period_type
674 AND Gp.Period_Name = lv_period_name; --variable: lv_period_name
675
676 CURSOR inv_period_name_cur IS
677 SELECT Period_Name
678 FROM Ap_Invoices_v
679 WHERE Invoice_Id = pn_invoice_id; --parameter: pn_invoice_id
680
681 BEGIN
682 --logging for debug
683 IF (ln_proc_level >= ln_dbg_level) THEN
684 FND_LOG.STRING(ln_proc_level,
685 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
686 '.begin',
687 'Enter procedure');
688 END IF; --ln_proc_level>=ln_dbg_level
689
690 IF pv_period_name IS NULL THEN
691 OPEN inv_period_name_cur;
692 FETCH inv_period_name_cur
693 INTO lv_period_name;
694 CLOSE inv_period_name_cur;
695 END IF;
696
697 OPEN period_num_cur(lv_period_name);
698 FETCH period_num_cur
699 INTO lv_period_num;
700 CLOSE period_num_cur;
701
702 --logging for debug
703 IF (ln_proc_level >= ln_dbg_level) THEN
704 FND_LOG.STRING(ln_proc_level,
705 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
706 'Exit procedure');
707 END IF; -- (ln_proc_level>=ln_dbg_level)
708
709 RETURN lv_period_num;
710 END Get_Accounting_Period_Number;
711
712 --==========================================================================
713 -- PROCEDURE NAME:
714 --
715 -- Get_Prepaid_Account_Number Public
716 --
717 -- DESCRIPTION:
718 --
719 -- To get account number with accounting class 'PREPAID_EXPENSE'
720 --
721 -- PARAMETERS:
722 -- In: pn_ledger_id IN NUMBER ledger id
723 -- pn_invoice_id IN NUMBER invoice id
724 --
725 -- DESIGN REFERENCES:
726 -- APAR_Chaoqun.doc
727 --
728 -- CHANGE HISTORY:
729 -- 04-May-2010 Chaoqun Wu created
730 --==========================================================================
731 FUNCTION Get_Prepaid_Account_Number(pn_invoice_id IN NUMBER,
732 pn_ledger_id IN NUMBER)
733 RETURN VARCHAR2 IS
734 lv_prepaid_account_num VARCHAR(30) := NULL;
735 lv_procedure_name VARCHAR2(40) := 'Get_Prepaid_Account_Number';
736 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
737 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
738
739 CURSOR prepaid_account_num_cur IS
740 SELECT DISTINCT Jcc.account_segment Prepaid_Account_Num
741 FROM Ap_Invoices_All Aia,
742 Xla_Transaction_Entities Ent,
743 Ja_Cn_Code_Combination_v Jcc,
744 Xla_Ae_Headers Aeh,
745 Xla_Ae_Lines Ael
746 WHERE Ent.Application_Id = 200
747 AND Aia.Invoice_Id = Ent.Source_Id_Int_1
748 AND Ent.Entity_Code = 'AP_INVOICES'
749 AND Ent.Entity_Id = Aeh.Entity_Id
750 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
751 AND Aia.Invoice_Id = pn_invoice_id --parameter: pn_invoice_id
752 AND Aia.Invoice_Type_Lookup_Code = 'PREPAYMENT'
753 AND Aeh.Ae_Header_Id = Ael.Ae_Header_Id
754 AND Ael.Accounting_Class_Code = 'PREPAID_EXPENSE'
755 AND Jcc.Code_Combination_Id = Ael.Code_Combination_Id
756 AND Jcc.Ledger_id = Aeh.Ledger_id;
757
758 BEGIN
759 --logging for debug
760 IF (ln_proc_level >= ln_dbg_level) THEN
761 FND_LOG.STRING(ln_proc_level,
762 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
763 '.begin',
764 'Enter procedure');
765 END IF; --ln_proc_level>=ln_dbg_level
766
767 For v_row IN prepaid_account_num_cur LOOP
768 lv_prepaid_account_num := v_row.Prepaid_Account_Num;
769 EXIT;
770 END LOOP;
771
772 --logging for debug
773 IF (ln_proc_level >= ln_dbg_level) THEN
774 FND_LOG.STRING(ln_proc_level,
775 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
776 'Exit procedure');
777 END IF; -- (ln_proc_level>=ln_dbg_level)
778
779 RETURN lv_prepaid_account_num;
780 END Get_Prepaid_Account_Number;
781
782 --==========================================================================
783 -- PROCEDURE NAME:
784 --
785 -- Get_Journal_Number Public
786 --
787 -- DESCRIPTION:
788 --
789 -- To get journal from from itemization table
790 --
791 -- PARAMETERS:
792 -- In: pn_je_header_id IN NUMBER journal header id
793 -- pn_legal_entity_id IN NUMBER legal entity id
794 --
795 -- DESIGN REFERENCES:
796 -- APAR_Chaoqun.doc
797 --
798 -- CHANGE HISTORY:
799 -- 04-May-2010 Chaoqun Wu created
800 -- 06-Jan-2010 Jianchao Chi Updated the logic of legal entity and voucher number
801 --==========================================================================
802 FUNCTION Get_Journal_Number(pn_je_header_id IN NUMBER,
803 pn_legal_entity_id IN NUMBER) --Add a parameter, by Jianchao Chi for V2 Upgrade 06-Jan-2011
804 RETURN VARCHAR2 IS
805 lv_journal_num VARCHAR(20) := NULL;
806 lv_procedure_name VARCHAR2(40) := 'Get_Journal_Number';
807 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
808 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
809
810 CURSOR journal_num_cur IS
811 --Update by Jianchao Chi, for cnaov2 upgrade 06-JAN-2011
812 --Add ja_cn_voucher_number table, voucher number and legal entity
813 --are fetched from ja_cn_voucher_number
814 --The new one is
815 SELECT DISTINCT voucher_number journal_number
816 FROM ja_cn_voucher_number
817 WHERE je_header_id = pn_je_header_id
818 AND legal_entity_id = pn_legal_entity_id;
819 --Comment by Jianchao Chi for V2 Upgrade, 06-JAN-2011
820 --get the journal number from ja_vn_voucher_number
821 --The previous one is:
822 /*SELECT DISTINCT jcjl.journal_number
823 FROM ja_cn_journal_lines jcjl
824 WHERE jcjl.je_header_id = pn_je_header_id;*/
825
826 BEGIN
827 --logging for debug
828 IF (ln_proc_level >= ln_dbg_level) THEN
829 FND_LOG.STRING(ln_proc_level,
830 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
831 '.begin',
832 'Enter procedure');
833 END IF; --ln_proc_level>=ln_dbg_level
834
835 For v_row IN journal_num_cur LOOP
836 lv_journal_num := v_row.Journal_Number;
837 EXIT;
838 END LOOP;
839
840 --logging for debug
841 IF (ln_proc_level >= ln_dbg_level) THEN
842 FND_LOG.STRING(ln_proc_level,
843 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
844 'Exit procedure');
845 END IF; -- (ln_proc_level>=ln_dbg_level)
846
847 RETURN lv_journal_num;
848 END Get_Journal_Number;
849
850 --==========================================================================
851 -- PROCEDURE NAME:
852 --
853 -- Get_Liability_Amount Public
854 --
855 -- DESCRIPTION:
856 --
857 -- To get functional amount and entered amount for liability account
858 --
859 -- PARAMETERS:
860 -- pn_ae_header_id IN NUMBER accounting header id
861 -- pn_ledger_id IN NUMBER ledger id
862 -- pn_legal_entity_id IN NUMBER legal entity id
863 -- pv_amount_type IN VARCHAR2 amount type, 'F' for getting
864 -- functional amount, 'E' for getting
865 -- entered amount
866 --
867 -- DESIGN REFERENCES:
868 -- APAR_Chaoqun.doc
869 --
870 -- CHANGE HISTORY:
871 -- 04-May-2010 Chaoqun Wu created
872 --==========================================================================
873 FUNCTION Get_Liability_Amount(pn_invoice_id IN NUMBER,
874 pn_ae_header_id IN NUMBER,
875 pn_ledger_id IN NUMBER,
876 pn_legal_entity_id IN NUMBER,
877 pv_amount_type IN VARCHAR2) RETURN NUMBER IS
878 ln_amount NUMBER := NULL;
879 lv_procedure_name VARCHAR2(40) := 'Get_Liability_Amount';
880 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
881 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
882
883 --Cursor for fetching invoice amount or payment amount at SLA level
884 CURSOR amount_cur IS
885 SELECT SUM(-1 * NVL(Ael.Accounted_Dr, 0) + NVL(Ael.Accounted_Cr, 0)) Functional_Amount,
886 SUM(-1 * NVL(Ael.Entered_Dr, 0) + NVL(Ael.Entered_Cr, 0)) Entered_Amount
887 FROM Xla_Ae_Lines Ael, Xla_Ae_Headers Aeh
888 WHERE Ael.Ae_Header_Id = Aeh.Ae_Header_Id
889 AND Ael.Accounting_Class_Code = 'LIABILITY'
890 AND ((pn_invoice_id IS NULL AND Aeh.Ae_Header_Id = pn_ae_header_id) --parameter: pn_ae_header_id
891 OR (pn_invoice_id IS NOT NULL AND
892 Aeh.Ae_Header_Id =
893 (DECODE((SELECT COUNT(*)
894 FROM Xla_Transaction_Entities Ent1,
895 Xla_Ae_Headers Aeh1
896 WHERE Ent1.Application_Id = 200
897 AND Ent1.Entity_Code = 'AP_INVOICES'
898 AND Ent1.Entity_Id = Aeh1.Entity_Id
899 AND Aeh1.Ae_Header_Id = pn_ae_header_id --parameter: pn_ae_header_id
900 AND Aeh1.Ledger_Id = pn_ledger_id), --parameter: pn_ledger_id
901 0,
902 (SELECT MIN(Aeh1.Ae_Header_Id)
903 FROM Ap_Invoices_All Aia1,
904 Xla_Transaction_Entities Ent1,
905 Xla_Ae_Headers Aeh1
906 WHERE Ent1.Application_Id = 200
907 AND Aia1.Invoice_Id = Ent1.Source_Id_Int_1
908 AND Ent1.Entity_Code = 'AP_INVOICES'
909 AND Ent1.Entity_Id = Aeh1.Entity_Id
910 AND Aia1.Invoice_Id = pn_invoice_id --parameter: pn_invoice_id
911 AND Aeh1.Ledger_Id = pn_ledger_id), --parameter: pn_ledger_id
912 pn_ae_header_id))))
913 --BSV condition, to handle one invoice having BSVs from different legal entity in distribution lines
914 AND EXISTS
915 (SELECT llbg.Bal_Seg_Value
916 FROM ja_cn_ledger_le_bsv_gt llbg
917 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Ael.CODE_COMBINATION_ID) =
918 llbg.Bal_Seg_Value
919 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
920 AND llbg.Legal_Entity_Id = pn_legal_entity_id); --parameter: pn_legal_entity_id
921
922 BEGIN
923 --logging for debug
924 IF (ln_proc_level >= ln_dbg_level) THEN
925 FND_LOG.STRING(ln_proc_level,
926 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
927 '.begin',
928 'Enter procedure');
929 END IF; --ln_proc_level>=ln_dbg_level
930
931 For v_row IN amount_cur LOOP
932 IF pv_amount_type = 'F' THEN
933 ln_amount := v_row.Functional_Amount;
934 ELSIF pv_amount_type = 'E' THEN
935 ln_amount := v_row.Entered_Amount;
936 END IF;
937 EXIT;
938 END LOOP;
939
940 --logging for debug
941 IF (ln_proc_level >= ln_dbg_level) THEN
942 FND_LOG.STRING(ln_proc_level,
943 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
944 'Exit procedure');
945 END IF; -- (ln_proc_level>=ln_dbg_level)
946
947 RETURN ln_amount;
948 END Get_Liability_Amount;
949
950 --==========================================================================
951 -- PROCEDURE NAME:
952 --
953 -- Get_Payment_Liability_Amount Public
954 --
955 -- DESCRIPTION:
956 --
957 -- To get functional amount and entered amount for payment liability account
958 --
959 -- PARAMETERS:
960 -- pn_invoice_payment_id IN NUMBER invoice payment id
961 -- pv_amount_type IN VARCHAR2 amount type, 'F' for getting
962 -- functional amount, 'E' for getting
963 -- entered amount
964 --
965 -- DESIGN REFERENCES:
966 -- APAR_Chaoqun.doc
967 --
968 -- CHANGE HISTORY:
969 -- 02-Jun-2010 Chaoqun Wu Created for fixing bug# 9747960
970 --==========================================================================
971 FUNCTION Get_Payment_Liability_Amount(pn_invoice_payment_id IN NUMBER,
972 pv_amount_type IN VARCHAR2)
973 RETURN NUMBER IS
974 ln_amount NUMBER := NULL;
975 lv_procedure_name VARCHAR2(40) := 'Get_Payment_Liability_Amount';
976 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
977 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
978
979 --Cursor for fetching invoice amount or payment amount at SLA level
980 CURSOR amount_cur IS
981 SELECT -1 * NVL(Aip.Amount, 0) Entered_Amount,
982 -1 * NVL(Aip.Payment_Base_Amount, NVL(Aip.Amount, 0)) Functional_Amount
983 FROM Ap_Invoice_Payments_All Aip
984 WHERE Aip.Invoice_Payment_Id = pn_invoice_payment_id; -- parameter: pn_invoice_payment_id
985
986 BEGIN
987 --logging for debug
988 IF (ln_proc_level >= ln_dbg_level) THEN
989 FND_LOG.STRING(ln_proc_level,
990 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
991 '.begin',
992 'Enter procedure');
993 END IF; --ln_proc_level>=ln_dbg_level
994
995 For v_row IN amount_cur LOOP
996 IF pv_amount_type = 'F' THEN
997 ln_amount := v_row.Functional_Amount;
998 ELSIF pv_amount_type = 'E' THEN
999 ln_amount := v_row.Entered_Amount;
1000 END IF;
1001 EXIT;
1002 END LOOP;
1003
1004 --logging for debug
1005 IF (ln_proc_level >= ln_dbg_level) THEN
1006 FND_LOG.STRING(ln_proc_level,
1007 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1008 'Exit procedure');
1009 END IF; -- (ln_proc_level>=ln_dbg_level)
1010
1011 RETURN ln_amount;
1012 END Get_Payment_Liability_Amount;
1013 --==========================================================================
1014 -- PROCEDURE NAME:
1015 --
1016 -- Get_Prepaid_Amount Public
1017 --
1018 -- DESCRIPTION:
1019 --
1020 -- To get functional amount and entered amount for prepaid account
1021 --
1022 -- PARAMETERS:
1023 -- pn_ae_header_id IN NUMBER accounting header id
1024 -- pn_ledger_id IN NUMBER ledger id
1025 -- pn_legal_entity_id IN NUMBER legal entity id
1026 -- pv_account_num IN VARCHAR2 prepaid account number
1027 -- pv_amount_type IN VARCHAR2 amount type, 'F' for getting
1028 -- functional amount, 'E' for getting
1029 -- entered amount
1030 --
1031 -- DESIGN REFERENCES:
1032 -- APAR_Chaoqun.doc
1033 --
1034 -- CHANGE HISTORY:
1035 -- 04-May-2010 Chaoqun Wu created
1036 --==========================================================================
1037 FUNCTION Get_Prepaid_Amount(pn_ae_header_id IN NUMBER,
1038 pn_ledger_id IN NUMBER,
1039 pn_legal_entity_id IN NUMBER,
1040 pv_account_num IN VARCHAR2,
1041 pv_amount_type IN VARCHAR2) RETURN NUMBER IS
1042 ln_amount NUMBER := NULL;
1043 lv_procedure_name VARCHAR2(40) := 'Get_Prepaid_Amount';
1044 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1045 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1046
1047 --Cursor for fetching invoice amount or payment amount at SLA level
1048 CURSOR amount_cur IS
1049 SELECT SUM(NVL(Ael.Accounted_Dr, 0) + -1 * NVL(Ael.Accounted_Cr, 0)) Functional_Amount,
1050 SUM(NVL(Ael.Entered_Dr, 0) + -1 * NVL(Ael.Entered_Cr, 0)) Entered_Amount
1051 FROM Xla_Ae_Lines Ael,
1052 Xla_Ae_Headers Aeh,
1053 Ja_Cn_Code_Combination_v Jcc
1054 WHERE Ael.Ae_Header_Id = Aeh.Ae_Header_Id
1055 AND Ael.Accounting_Class_Code <> 'LIABILITY'
1056 AND Aeh.Ae_Header_Id = pn_ae_header_id --parameter: pn_ae_header_id
1057 AND Jcc.Code_Combination_Id = Ael.Code_Combination_Id
1058 AND Jcc.account_segment = pv_account_num --parameter: pv_account_num
1059 AND Jcc.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1060 --BSV condition, to handle one invoice having BSVs from different legal entity in distribution lines
1061 AND EXISTS
1062 (SELECT llbg.Bal_Seg_Value
1063 FROM ja_cn_ledger_le_bsv_gt llbg
1064 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Ael.CODE_COMBINATION_ID) =
1065 llbg.Bal_Seg_Value
1066 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1067 AND llbg.Legal_Entity_Id = pn_legal_entity_id); --parameter: pn_legal_entity_id
1068
1069 BEGIN
1070 --logging for debug
1071 IF (ln_proc_level >= ln_dbg_level) THEN
1072 FND_LOG.STRING(ln_proc_level,
1073 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1074 '.begin',
1075 'Enter procedure');
1076 END IF; --ln_proc_level>=ln_dbg_level
1077
1078 For v_row IN amount_cur LOOP
1079 IF pv_amount_type = 'F' THEN
1080 ln_amount := v_row.Functional_Amount;
1081 ELSIF pv_amount_type = 'E' THEN
1082 ln_amount := v_row.Entered_Amount;
1083 END IF;
1084 EXIT;
1085 END LOOP;
1086
1087 --logging for debug
1088 IF (ln_proc_level >= ln_dbg_level) THEN
1089 FND_LOG.STRING(ln_proc_level,
1090 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1091 'Exit procedure');
1092 END IF; -- (ln_proc_level>=ln_dbg_level)
1093
1094 RETURN ln_amount;
1095 END Get_Prepaid_Amount;
1096
1097 --==========================================================================
1098 -- PROCEDURE NAME:
1099 --
1100 -- Get_Liability_Balance Public
1101 --
1102 -- DESCRIPTION:
1103 --
1104 -- To get functional balance and entered balance for liability account
1105 --
1106 -- PARAMETERS:
1107 -- pn_supplier_id IN NUMBER accounting header id
1108 -- pn_ledger_id IN NUMBER ledger id
1109 -- pn_legal_entity_id IN NUMBER legal entity id
1110 -- pv_account_num IN VARCHAR2 liability account number
1111 -- pv_currency_code IN VARCHAR2 currency code
1112 -- pv_period_name IN VARCHAR2 accounting period
1113 -- pv_balance_type IN VARCHAR2 balance type, 'F' for getting
1114 -- functional amount, 'E' for getting
1115 -- entered amount
1116 --
1117 -- DESIGN REFERENCES:
1118 -- APAR_Chaoqun.doc
1119 --
1120 -- CHANGE HISTORY:
1121 -- 04-May-2010 Chaoqun Wu created
1122 --==========================================================================
1123 FUNCTION Get_Liability_Balance(pn_supplier_id IN NUMBER,
1124 pn_ledger_id IN NUMBER,
1125 pn_legal_entity_id IN NUMBER,
1126 pv_account_number IN VARCHAR2,
1127 pv_currency_code IN VARCHAR2,
1128 pv_period_name IN VARCHAR2,
1129 pv_balance_type IN VARCHAR2)
1130 RETURN NUMBER IS
1131 ln_balance NUMBER := NULL;
1132 lv_procedure_name VARCHAR2(40) := 'Get_Liability_Balance';
1133 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1134 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1135
1136 --Cursor for fetching balance at itemization level
1137 CURSOR balance_cur IS
1138 SELECT SUM(func_end_balance) Functional_Balance,
1139 SUM(orig_end_balance) Entered_Balance
1140 FROM ja_cn_account_balances_v jcab
1141 WHERE jcab.ledger_id = pn_ledger_id --parameter: pn_ledger_id
1142 AND jcab.legal_entity_id = pn_legal_entity_id --parameter: pn_legal_entity_id
1143 AND jcab.third_party_id = pn_supplier_id --parameter: pn_supplier_id
1144 AND jcab.account_segment = pv_account_number --parameter: pv_account_number
1145 AND jcab.currency_code = pv_currency_code --parameter: pv_currency_code
1146 AND jcab.period_name = pv_period_name; --parameter: pv_period_name
1147
1148 BEGIN
1149 --logging for debug
1150 IF (ln_proc_level >= ln_dbg_level) THEN
1151 FND_LOG.STRING(ln_proc_level,
1152 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1153 '.begin',
1154 'Enter procedure');
1155 END IF; --ln_proc_level>=ln_dbg_level
1156
1157 For v_row IN balance_cur LOOP
1158 IF pv_balance_type = 'F' THEN
1159 ln_balance := v_row.Functional_Balance;
1160 ELSIF pv_balance_type = 'E' THEN
1161 ln_balance := v_row.Entered_Balance;
1162 END IF;
1163 EXIT;
1164 END LOOP;
1165
1166 --logging for debug
1167 IF (ln_proc_level >= ln_dbg_level) THEN
1168 FND_LOG.STRING(ln_proc_level,
1169 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1170 'Exit procedure');
1171 END IF; -- (ln_proc_level>=ln_dbg_level)
1172
1173 RETURN NVL(ln_balance, 0);
1174 END Get_Liability_Balance;
1175
1176 --==========================================================================
1177 -- PROCEDURE NAME:
1178 --
1179 -- Get_Prepaid_Balance Public
1180 --
1181 -- DESCRIPTION:
1182 --
1183 -- To get functional balance and entered balance for prepaid account
1184 --
1185 -- PARAMETERS:
1186 -- pn_supplier_id IN NUMBER accounting header id
1187 -- pn_ledger_id IN NUMBER ledger id
1188 -- pn_legal_entity_id IN NUMBER legal entity id
1189 -- pv_account_num IN VARCHAR2 prepaid account number
1190 -- pv_currency_code IN VARCHAR2 currency code
1191 -- pv_period_name IN VARCHAR2 accounting period
1192 -- pv_balance_type IN VARCHAR2 balance type, 'F' for getting
1193 -- functional amount, 'E' for getting
1194 -- entered amount
1195 --
1196 -- DESIGN REFERENCES:
1197 -- APAR_Chaoqun.doc
1198 --
1199 -- CHANGE HISTORY:
1200 -- 04-May-2010 Chaoqun Wu created
1201 --==========================================================================
1202 FUNCTION Get_Prepaid_Balance(pn_supplier_id IN NUMBER,
1203 pn_ledger_id IN NUMBER,
1204 pn_legal_entity_id IN NUMBER,
1205 pv_account_number IN VARCHAR2,
1206 pv_currency_code IN VARCHAR2,
1207 pv_period_name IN VARCHAR2,
1208 pv_balance_type IN VARCHAR2) RETURN NUMBER IS
1209 ln_balance NUMBER := NULL;
1210 lv_procedure_name VARCHAR2(40) := 'Get_Prepaid_Balance';
1211 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1212 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1213
1214 --Cursor for fetching balance at itemization level
1215 CURSOR balance_cur IS
1216 SELECT SUM(NVL(jcab.func_period_net_dr, 0) +
1217 -1 * NVL(jcab.func_period_net_cr, 0)) Functional_Balance,
1218 SUM(NVL(jcab.orig_period_net_dr, 0) +
1219 -1 * NVL(jcab.orig_period_net_cr, 0)) Entered_Balance
1220 FROM ja_cn_account_balances jcab
1221 WHERE jcab.ledger_id = pn_ledger_id --parameter: pn_ledger_id
1222 AND jcab.legal_entity_id = pn_legal_entity_id --parameter: pn_legal_entity_id
1223 AND jcab.third_party_id = pn_supplier_id --parameter: pn_supplier_id
1224 AND jcab.account_segment = pv_account_number --parameter: pv_account_number
1225 AND jcab.currency_code = pv_currency_code --parameter: pv_currency_code
1226 AND jcab.period_name = pv_period_name; --parameter: pv_period_name
1227
1228 BEGIN
1229 --logging for debug
1230 IF (ln_proc_level >= ln_dbg_level) THEN
1231 FND_LOG.STRING(ln_proc_level,
1232 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1233 '.begin',
1234 'Enter procedure');
1235 END IF; --ln_proc_level>=ln_dbg_level
1236
1237 For v_row IN balance_cur LOOP
1238 IF pv_balance_type = 'F' THEN
1239 ln_balance := v_row.Functional_Balance;
1240 ELSIF pv_balance_type = 'E' THEN
1241 ln_balance := v_row.Entered_Balance;
1242 END IF;
1243 EXIT;
1244 END LOOP;
1245
1246 --logging for debug
1247 IF (ln_proc_level >= ln_dbg_level) THEN
1248 FND_LOG.STRING(ln_proc_level,
1249 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1250 'Exit procedure');
1251 END IF; -- (ln_proc_level>=ln_dbg_level)
1252
1253 RETURN NVL(ln_balance, 0);
1254 END Get_Prepaid_Balance;
1255
1256 --==========================================================================
1257 -- PROCEDURE NAME:
1258 --
1259 -- Get_Balance_Side Public
1260 --
1261 -- DESCRIPTION:
1262 --
1263 -- To get balance side
1264 --
1265 -- PARAMETERS:
1266 -- pn_coa_id IN NUMBER chart of account id
1267 -- pv_account_num IN VARCHAR2 account number
1268 --
1269 -- DESIGN REFERENCES:
1270 -- APAR_Chaoqun.doc
1271 --
1272 -- CHANGE HISTORY:
1273 -- 04-May-2010 Chaoqun Wu created
1274 --==========================================================================
1275 FUNCTION Get_Balance_Side(pn_coa_id IN NUMBER,
1276 pv_account_number IN VARCHAR2) RETURN VARCHAR2 IS
1277 ln_flex_value_set_id NUMBER := NULL;
1278 lv_dr_cr_code VARCHAR2(10);
1279 lv_debit VARCHAR2(25);
1280 lv_credit VARCHAR2(25);
1281 lv_balance_side VARCHAR2(25);
1282 lv_attribute_column4bal_side ja_cn_dff_assignments.attribute_column%TYPE;
1283 lv_procedure_name VARCHAR2(40) := 'Get_Balance_Side';
1284 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1285 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1286
1287 CURSOR balance_side_cur(ln_flex_value_set_id NUMBER,
1288 lv_attribute_column4bal_side VARCHAR2) IS
1289 SELECT SUBSTR(TO_CHAR(COMPILED_VALUE_ATTRIBUTES), 5, 1) Account_Type,
1290 DECODE(Value_Category,
1291 'Subsidiary',
1292 DECODE(lv_attribute_column4bal_side,
1293 'ATTRIBUTE1',
1294 ATTRIBUTE1,
1295 'ATTRIBUTE2',
1296 ATTRIBUTE2,
1297 'ATTRIBUTE3',
1298 ATTRIBUTE3,
1299 'ATTRIBUTE4',
1300 ATTRIBUTE4,
1301 'ATTRIBUTE5',
1302 ATTRIBUTE5,
1303 'ATTRIBUTE6',
1304 ATTRIBUTE6,
1305 'ATTRIBUTE7',
1306 ATTRIBUTE7,
1307 'ATTRIBUTE8',
1308 ATTRIBUTE8,
1309 'ATTRIBUTE9',
1310 ATTRIBUTE9,
1311 'ATTRIBUTE10',
1312 ATTRIBUTE10,
1313 'ATTRIBUTE11',
1314 ATTRIBUTE11,
1315 'ATTRIBUTE12',
1316 ATTRIBUTE12,
1317 'ATTRIBUTE13',
1318 ATTRIBUTE13,
1319 'ATTRIBUTE14',
1320 ATTRIBUTE14,
1321 'ATTRIBUTE15',
1322 ATTRIBUTE15,
1323 'ATTRIBUTE16',
1324 ATTRIBUTE16,
1325 'ATTRIBUTE17',
1326 ATTRIBUTE17,
1327 'ATTRIBUTE18',
1328 ATTRIBUTE18,
1329 'ATTRIBUTE19',
1330 ATTRIBUTE19,
1331 'ATTRIBUTE20',
1332 ATTRIBUTE20,
1333 'ATTRIBUTE21',
1334 ATTRIBUTE21,
1335 'ATTRIBUTE22',
1336 ATTRIBUTE22,
1337 'ATTRIBUTE23',
1338 ATTRIBUTE23,
1339 'ATTRIBUTE24',
1340 ATTRIBUTE24,
1341 'ATTRIBUTE25',
1342 ATTRIBUTE25,
1343 'ATTRIBUTE26',
1344 ATTRIBUTE26,
1345 'ATTRIBUTE27',
1346 ATTRIBUTE27,
1347 'ATTRIBUTE28',
1348 ATTRIBUTE28,
1349 'ATTRIBUTE29',
1350 ATTRIBUTE29,
1351 'ATTRIBUTE30',
1352 ATTRIBUTE30,
1353 'ATTRIBUTE31',
1354 ATTRIBUTE31,
1355 'ATTRIBUTE32',
1356 ATTRIBUTE32,
1357 'ATTRIBUTE33',
1358 ATTRIBUTE33,
1359 'ATTRIBUTE34',
1360 ATTRIBUTE34,
1361 'ATTRIBUTE35',
1362 ATTRIBUTE35,
1363 'ATTRIBUTE36',
1364 ATTRIBUTE36,
1365 'ATTRIBUTE37',
1366 ATTRIBUTE37,
1367 'ATTRIBUTE38',
1368 ATTRIBUTE38,
1369 'ATTRIBUTE39',
1370 ATTRIBUTE39,
1371 'ATTRIBUTE40',
1372 ATTRIBUTE40,
1373 'ATTRIBUTE41',
1374 ATTRIBUTE41,
1375 'ATTRIBUTE42',
1376 ATTRIBUTE42,
1377 'ATTRIBUTE43',
1378 ATTRIBUTE43,
1379 'ATTRIBUTE44',
1380 ATTRIBUTE44,
1381 'ATTRIBUTE45',
1382 ATTRIBUTE45,
1383 'ATTRIBUTE46',
1384 ATTRIBUTE46,
1385 'ATTRIBUTE47',
1386 ATTRIBUTE47,
1387 'ATTRIBUTE48',
1388 ATTRIBUTE48,
1389 'ATTRIBUTE49',
1390 ATTRIBUTE49,
1391 'ATTRIBUTE50',
1392 ATTRIBUTE50,
1393 NULL),
1394 NULL) Balance_Side
1395 FROM FND_FLEX_VALUES FFV
1396 WHERE flex_value_set_id = ln_flex_value_set_id --variable: ln_flex_value_set_id
1397 AND flex_value = pv_account_number; --parameter: pv_account_number
1398
1399 --Cursor for getting the Attribute Column for storing Balance Side
1400 CURSOR dff_assignments_cur IS
1401 SELECT attribute_column
1402 FROM ja_cn_dff_assignments
1403 WHERE chart_of_accounts_id = pn_coa_id --parameter: pn_coa_id
1404 AND dff_title_code = 'ACBS';
1405
1406 BEGIN
1407 --logging for debug
1408 IF (ln_proc_level >= ln_dbg_level) THEN
1409 FND_LOG.STRING(ln_proc_level,
1410 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1411 '.begin',
1412 'Enter procedure');
1413 END IF; --ln_proc_level>=ln_dbg_level
1414
1415 SELECT MEANING
1416 INTO lv_debit
1417 FROM FND_LOOKUP_VALUES
1418 WHERE LOOKUP_TYPE = 'JA_CN_DEBIT_CREDIT'
1419 AND LOOKUP_CODE = 'D'
1420 AND LANGUAGE = USERENV('LANG');
1421
1422 SELECT MEANING
1423 INTO lv_credit
1424 FROM FND_LOOKUP_VALUES
1425 WHERE LOOKUP_TYPE = 'JA_CN_DEBIT_CREDIT'
1426 AND LOOKUP_CODE = 'C'
1427 AND LANGUAGE = USERENV('LANG');
1428
1429 SELECT s.flex_value_set_id
1430 INTO ln_flex_value_set_id
1431 FROM fnd_id_flex_segments s
1432 WHERE s.application_id = 101
1433 AND s.id_flex_num = pn_coa_id
1434 AND s.id_flex_code = 'GL#'
1435 AND s.application_column_name =
1436 (SELECT application_column_name
1437 FROM fnd_segment_attribute_values
1438 WHERE application_id = 101
1439 AND segment_attribute_type = 'GL_ACCOUNT'
1440 AND attribute_value = 'Y'
1441 AND id_flex_num = pn_coa_id
1442 AND id_flex_code = 'GL#');
1443
1444 FOR v_dff_assignments_row IN dff_assignments_cur LOOP
1445 lv_attribute_column4bal_side := v_dff_assignments_row.attribute_column;
1446 EXIT;
1447 END LOOP;
1448
1449 --Fetch the balance Side of account, the balance side setted in flexfield segment value has high priority
1450 FOR v_balance_side_row IN balance_side_cur(ln_flex_value_set_id,
1451 lv_attribute_column4bal_side) LOOP
1452 IF v_balance_side_row.balance_side IS NOT NULL THEN
1453 lv_dr_cr_code := v_balance_side_row.balance_side;
1454 ELSE
1455 IF v_balance_side_row.account_type = 'A' OR
1456 v_balance_side_row.account_type = 'E' THEN
1457 lv_dr_cr_code := 'D';
1458 ELSE
1459 lv_dr_cr_code := 'C';
1460 END IF;
1461 END IF;
1462 IF lv_dr_cr_code = 'D' THEN
1463 lv_balance_side := lv_debit;
1464 ELSE
1465 lv_balance_side := lv_credit;
1466 END IF;
1467 EXIT;
1468 END LOOP; --end balance_side_cur
1469
1470 --logging for debug
1471 IF (ln_proc_level >= ln_dbg_level) THEN
1472 FND_LOG.STRING(ln_proc_level,
1473 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1474 'Exit procedure');
1475 END IF; -- (ln_proc_level>=ln_dbg_level)
1476
1477 RETURN lv_balance_side;
1478 END Get_Balance_Side;
1479
1480 --==========================================================================
1481 -- PROCEDURE NAME:
1482 --
1483 -- Get_Remittance_Bill_Number Public
1484 --
1485 -- DESCRIPTION:
1486 --
1487 -- To get remittance bill number
1488 --
1489 -- PARAMETERS:
1490 -- pn_coa_id IN NUMBER chart of account id
1491 -- pn_check_id IN NUMBER payment id
1492 --
1493 -- DESIGN REFERENCES:
1494 -- APAR_Chaoqun.doc
1495 --
1496 -- CHANGE HISTORY:
1497 -- 04-May-2010 Chaoqun Wu created
1498 -- 12-Jun-2010 Chaoqun Wu Updated for new solution described in bug# 9793920
1499 --==========================================================================
1500 FUNCTION Get_Remittance_Bill_Number(pn_coa_id IN NUMBER,
1501 pn_check_id IN NUMBER) --Updated for fixing bug# 9793920
1502 RETURN VARCHAR2 IS
1503 lv_bill_num VARCHAR(50) := NULL;
1504 lv_context_code VARCHAR(255) := NULL;
1505 lv_attribute_column4bill_num ja_cn_dff_assignments.attribute_column%TYPE;
1506 lv_cursor_sql VARCHAR(200);
1507 TYPE ref_cursor is REF CURSOR;
1508 v_bill_num_row ref_cursor;
1509 lv_procedure_name VARCHAR2(40) := 'Get_Remittance_Bill_Number';
1510 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1511 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1512
1513 --Cursor for getting the Attribute Column for storing Remittance Bill Number
1514 CURSOR dff_assignments_cur IS
1515 SELECT attribute_column, context_code
1516 FROM ja_cn_dff_assignments
1517 WHERE chart_of_accounts_id = pn_coa_id --parameter: pn_coa_id
1518 AND dff_title_code = 'APIN';
1519
1520 BEGIN
1521 --logging for debug
1522 IF (ln_proc_level >= ln_dbg_level) THEN
1523 FND_LOG.STRING(ln_proc_level,
1524 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1525 '.begin',
1526 'Enter procedure');
1527 END IF; --ln_proc_level>=ln_dbg_level
1528
1529 FOR v_dff_assignments_row IN dff_assignments_cur LOOP
1530 lv_attribute_column4bill_num := v_dff_assignments_row.attribute_column;
1531 lv_context_code := v_dff_assignments_row.context_code;
1532 EXIT;
1533 END LOOP;
1534
1535 IF lv_attribute_column4bill_num IS NOT NULL --Defined in DFF assignment form
1536 THEN
1537 lv_cursor_sql := 'SELECT ' || lv_attribute_column4bill_num ||
1538 ' FROM Ap_Checks_All' || ' WHERE Check_Id =' ||
1539 pn_check_id || ' AND Attribute_Category=''' ||
1540 lv_context_code || ''''; --Updated for fixing bug# 9793920
1541
1542 OPEN v_bill_num_row for lv_cursor_sql;
1543 FETCH v_bill_num_row
1544 INTO lv_bill_num;
1545 CLOSE v_bill_num_row;
1546 END IF;
1547
1548 --logging for debug
1549 IF (ln_proc_level >= ln_dbg_level) THEN
1550 FND_LOG.STRING(ln_proc_level,
1551 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1552 'Exit procedure');
1553 END IF; -- (ln_proc_level>=ln_dbg_level)
1554
1555 RETURN lv_bill_num;
1556 END Get_Remittance_Bill_Number;
1557
1558 --==========================================================================
1559 -- PROCEDURE NAME:
1560 --
1561 -- Get_Due_Date Public
1562 --
1563 -- DESCRIPTION:
1564 --
1565 -- To get due date
1566 --
1567 -- PARAMETERS:
1568 -- pn_invoice_id IN NUMBER invoice id
1569 -- pn_invoice_payment_id IN NUMBER invoice payment id
1570 --
1571 -- DESIGN REFERENCES:
1572 -- APAR_Chaoqun.doc
1573 --
1574 -- CHANGE HISTORY:
1575 -- 04-May-2010 Chaoqun Wu created
1576 --==========================================================================
1577 FUNCTION Get_Due_Date(pn_invoice_id IN NUMBER,
1578 pn_inovice_payment_id IN NUMBER) RETURN VARCHAR2 IS
1579 lv_due_date VARCHAR(10) := NULL;
1580 lv_procedure_name VARCHAR2(40) := 'Get_Due_Date';
1581 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1582 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1583
1584 CURSOR due_date_cur IS
1585 SELECT TO_CHAR(apsa.Due_Date, 'YYYYMMDD') Due_Date
1586 FROM Ap_Payment_Schedules_All apsa
1587 WHERE apsa.Invoice_Id = pn_invoice_id --parameter: pn_invoice_id
1588 AND (pn_inovice_payment_id IS NULL
1589 --To handle schedule payment
1590 OR
1591 (pn_inovice_payment_id IS NOT NULL AND EXISTS
1592 (SELECT aipa.Payment_Num
1593 FROM Ap_Invoice_Payments_All aipa
1594 WHERE aipa.Invoice_Id = apsa.Invoice_Id
1595 AND aipa.Payment_Num = apsa.Payment_Num
1596 AND aipa.Invoice_Payment_id = pn_inovice_payment_id))) --parameter: pn_inovice_payment_id
1597 ORDER BY Due_Date;
1598
1599 BEGIN
1600 --logging for debug
1601 IF (ln_proc_level >= ln_dbg_level) THEN
1602 FND_LOG.STRING(ln_proc_level,
1603 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1604 '.begin',
1605 'Enter procedure');
1606 END IF; --ln_proc_level>=ln_dbg_level
1607
1608 For v_row IN due_date_cur LOOP
1609 lv_due_date := v_row.Due_Date;
1610 EXIT;
1611 END LOOP;
1612
1613 --logging for debug
1614 IF (ln_proc_level >= ln_dbg_level) THEN
1615 FND_LOG.STRING(ln_proc_level,
1616 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1617 'Exit procedure');
1618 END IF; -- (ln_proc_level>=ln_dbg_level)
1619
1620 RETURN lv_due_date;
1621 END Get_Due_Date;
1622
1623 --==========================================================================
1624 -- PROCEDURE NAME:
1625 --
1626 -- Get_Liability_Clear_Flag Public
1627 --
1628 -- DESCRIPTION:
1629 --
1630 -- To get clear flag for liability account
1631 --
1632 -- PARAMETERS:
1633 -- pn_invoice_id IN NUMBER invoice id
1634 -- pv_period_name IN VARCHAR2 accounting period
1635 -- pn_ledger_id IN NUMBER ledger id
1636 -- pn_legal_entity_id IN NUMBER legal entity id
1637 --
1638 -- DESIGN REFERENCES:
1639 -- APAR_Chaoqun.doc
1640 --
1641 -- CHANGE HISTORY:
1642 -- 04-May-2010 Chaoqun Wu created
1643 -- 02-Jun-2010 Chaoqun Wu Updated for fixing bug# 9747960
1644 -- 20-Jun-2011 Choli updated, fix bug 12635156
1645 --==========================================================================
1646 FUNCTION Get_Liability_Clear_Flag(pn_invoice_id IN NUMBER,
1647 pv_period_name IN VARCHAR2,
1648 pn_ledger_id IN NUMBER,
1649 pn_legal_entity_id IN NUMBER)
1650 RETURN VARCHAR2 IS
1651 lv_clear_flag VARCHAR(1) := '0';
1652 ln_paid_amount NUMBER;
1653 ln_inv_amount NUMBER;
1654 lv_procedure_name VARCHAR2(40) := 'Get_Liability_Clear_Flag';
1655 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1656 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1657
1658 --collecting invoice amount
1659 CURSOR inv_amount_cur IS
1660 SELECT SUM(-1 * NVL(Ael.Entered_Dr, 0) + NVL(Ael.Entered_Cr, 0)) Inv_Amount
1661 FROM Ap_Invoices_All Aia,
1662 Xla_Transaction_Entities Ent,
1663 Xla_Ae_Headers Aeh,
1664 Xla_Ae_Lines Ael
1665 WHERE Aia.Invoice_Id = pn_invoice_id -- parameter: pn_invoice_id
1666 --SLA condition
1667 AND Ent.Application_Id = 200
1668 AND Aia.Invoice_Id = Ent.Source_Id_Int_1
1669 AND Ent.Entity_Code = 'AP_INVOICES'
1670 AND Ent.Entity_Id = Aeh.Entity_Id
1671 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1672 AND Ael.Accounting_Class_Code = 'LIABILITY'
1673 AND (Aia.Invoice_Type_Lookup_Code = 'PREPAYMENT' OR NOT EXISTS
1674 (SELECT Ael1.Ae_HEADER_ID --filter accounting line for prepayment application
1675 FROM Xla_Ae_Lines Ael1
1676 WHERE Aeh.Ae_Header_Id = Ael1.Ae_Header_Id
1677 AND Ael1.Accounting_Class_Code = 'PREPAID_EXPENSE'))
1678 AND Aeh.Ae_Header_Id = Ael.Ae_Header_Id
1679 --BSV condition, to handle one invoice having BSVs from different legal entity in distribution lines
1680 AND EXISTS
1681 (SELECT llbg.Bal_Seg_Value
1682 FROM ja_cn_ledger_le_bsv_gt llbg
1683 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Ael.CODE_COMBINATION_ID) =
1684 llbg.Bal_Seg_Value
1685 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1686 AND llbg.Legal_Entity_Id = pn_legal_entity_id); --parameter: pn_legal_entity_id
1687
1688 CURSOR paid_amount_cur IS
1689 SELECT SUM(NVL(Paid_Amount, 0)) Paid_Amount
1690 FROM (
1691 --collecting payment amount
1692 --Begin: Updated for fixing bug# 9747960
1693 /*SELECT SUM(NVL(Ael.Entered_Dr,
1694 0) + -1 * NVL(Ael.Entered_Cr,
1695 0)) Paid_Amount
1696 FROM Ap_Invoices_All Aia,
1697 Ap_Checks_All Ach,
1698 Ap_Invoice_Payments_All Aip,
1699 Xla_Transaction_Entities Ent,
1700 Xla_Ae_Headers Aeh,
1701 Xla_Ae_Lines Ael,
1702 Gl_Je_Headers Jeh,
1703 Gl_Import_References Gir
1704 WHERE Aia.Invoice_Id = Aip.Invoice_Id
1705 AND Ach.Check_Id = Aip.Check_Id
1706 AND Aia.Invoice_Id = pn_invoice_id -- parameter: pn_invoice_id
1707 AND Aip.Accounting_Date <= (SELECT DISTINCT Gp.End_Date
1708 FROM Gl_Ledgers Gl,
1709 Gl_Periods Gp
1710 WHERE Gl.period_set_name = Gp.period_set_name
1711 AND Gl.accounted_period_type = Gp.period_type
1712 AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1713 AND Gp.period_name = pv_period_name) --parameter: pv_period_name
1714 --SLA condition
1715 AND Ent.Application_Id = 200
1716 AND Ach.Check_Id = Ent.Source_Id_Int_1
1717 AND Ent.Entity_Code = 'AP_PAYMENTS'
1718 AND Ent.Entity_Id = Aeh.Entity_Id
1719 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1720 AND Ael.Accounting_Class_Code = 'LIABILITY'
1721 --GL condition
1722 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
1723 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
1724 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
1725 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
1726 AND Jeh.Status = 'P' --only collecting posted payment
1727 --BSV condition, to handle one invoice having BSVs from different legal entity in distribution lines
1728 AND EXISTS (SELECT llbg.Bal_Seg_Value
1729 FROM ja_cn_ledger_le_bsv_gt llbg
1730 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Ael.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
1731 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1732 AND llbg.Legal_Entity_Id = pn_legal_entity_id) --parameter: pn_legal_entity_id*/
1733 SELECT SUM(NVL(Aip.Amount, 0)) Paid_Amount
1734 FROM Ap_Invoices_All Aia, Ap_Invoice_Payments_All Aip
1735 WHERE Aia.Invoice_Id = Aip.Invoice_Id
1736 AND Aia.Invoice_Id = pn_invoice_id -- parameter: pn_invoice_id
1737 AND EXISTS
1738 (SELECT Ach.Check_Id
1739 FROM Ap_Checks_All Ach,
1740 Xla_Transaction_Entities Ent,
1741 Xla_Ae_Headers Aeh,
1742 Xla_Ae_Lines Ael,
1743 Gl_Je_Headers Jeh,
1744 Gl_Import_References Gir
1745 WHERE Ach.Check_Id = Aip.Check_Id
1746 AND Aip.Accounting_Date <=
1747 (SELECT DISTINCT Gp.End_Date
1748 FROM Gl_Ledgers Gl, Gl_Periods Gp
1749 WHERE Gl.period_set_name = Gp.period_set_name
1750 AND Gl.accounted_period_type = Gp.period_type
1751 AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1752 AND Gp.period_name = pv_period_name) --parameter: pv_period_name
1753 -- Choli updated, fix bug 12635156
1754 AND Ach.cleared_date <=
1755 (SELECT DISTINCT Gp.End_Date
1756 FROM Gl_Ledgers Gl, Gl_Periods Gp
1757 WHERE Gl.period_set_name = Gp.period_set_name
1758 AND Gl.accounted_period_type = Gp.period_type
1759 AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1760 AND Gp.period_name = pv_period_name) --parameter: pv_period_name
1761 ------------------------------------------------------
1762
1763 --SLA condition
1764 AND Ent.Application_Id = 200
1765 AND Ach.Check_Id = Ent.Source_Id_Int_1
1766 AND Ent.Entity_Code = 'AP_PAYMENTS'
1767 AND Ent.Entity_Id = Aeh.Entity_Id
1768 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1769 AND Ael.Accounting_Class_Code = 'LIABILITY'
1770 --GL condition
1771 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
1772 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
1773 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
1774 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
1775 AND Jeh.Status = 'P') --only collecting posted payment
1776 --End: Updated for fixing bug# 9747960
1777 UNION
1778 --collecting prepaid amount
1779 SELECT SUM(-1 * NVL(Ael.Entered_Dr, 0) + NVL(Ael.Entered_Cr, 0)) Paid_Amount
1780 FROM Ap_Invoices_All Aia,
1781 Xla_Transaction_Entities Ent,
1782 Xla_Ae_Headers Aeh,
1783 Xla_Ae_Lines Ael,
1784 Gl_Je_Headers Jeh,
1785 Gl_Import_References Gir
1786 WHERE Aia.Invoice_Id = pn_invoice_id -- parameter: pn_invoice_id
1787 AND Aia.Invoice_Type_Lookup_Code <> 'PREPAYMENT'
1788 --SLA condition
1789 AND Ent.Application_Id = 200
1790 AND Aia.Invoice_Id = Ent.Source_Id_Int_1
1791 AND Ent.Entity_Code = 'AP_INVOICES'
1792 AND Ent.Entity_Id = Aeh.Entity_Id
1793 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1794 AND Ael.Accounting_Class_Code = 'PREPAID_EXPENSE'
1795 AND Aeh.Ae_Header_Id = Ael.Ae_Header_Id
1796 --GL condition
1797 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
1798 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
1799 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
1800 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
1801 AND Jeh.Status = 'P' --only collecting posted prepayment application
1802 --Period condition
1803 AND Jeh.Default_Effective_Date <=
1804 (SELECT DISTINCT Gp.End_Date
1805 FROM Gl_Ledgers Gl, Gl_Periods Gp
1806 WHERE Gl.period_set_name = Gp.period_set_name
1807 AND Gl.accounted_period_type = Gp.period_type
1808 AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1809 AND Gp.period_name = pv_period_name) --parameter: pv_period_name
1810 --BSV condition, to handle one invoice having BSVs from different legal entity in distribution lines
1811 AND EXISTS
1812 (SELECT llbg.Bal_Seg_Value
1813 FROM ja_cn_ledger_le_bsv_gt llbg
1814 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Ael.CODE_COMBINATION_ID) =
1815 llbg.Bal_Seg_Value
1816 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1817 AND llbg.Legal_Entity_Id = pn_legal_entity_id) --parameter: pn_legal_entity_id
1818 );
1819
1820 BEGIN
1821 --logging for debug
1822 IF (ln_proc_level >= ln_dbg_level) THEN
1823 FND_LOG.STRING(ln_proc_level,
1824 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1825 '.begin',
1826 'Enter procedure');
1827 END IF; --ln_proc_level>=ln_dbg_level
1828
1829 FOR v_inv_amount_row IN inv_amount_cur LOOP
1830 ln_inv_amount := v_inv_amount_row.Inv_Amount;
1831 EXIT;
1832 END LOOP;
1833
1834 FOR v_paid_amount_row IN paid_amount_cur LOOP
1835 ln_paid_amount := v_paid_amount_row.Paid_Amount;
1836 EXIT;
1837 END LOOP;
1838 --fnd_file.PUT_LINE(fnd_file.LOG,'Invoice_Id='||pn_invoice_id||',ln_paid_amount='||ln_paid_amount||',ln_inv_amount='||ln_inv_amount);
1839 IF ln_inv_amount = NVL(ln_paid_amount, 0) THEN
1840 lv_clear_flag := '1'; --cleared
1841 ELSE
1842 lv_clear_flag := '0'; --unclear
1843 END IF;
1844
1845 --logging for debug
1846 IF (ln_proc_level >= ln_dbg_level) THEN
1847 FND_LOG.STRING(ln_proc_level,
1848 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1849 'Exit procedure');
1850 END IF; -- (ln_proc_level>=ln_dbg_level)
1851
1852 RETURN lv_clear_flag;
1853 END Get_Liability_Clear_Flag;
1854
1855 --==========================================================================
1856 -- PROCEDURE NAME:
1857 --
1858 -- Get_Prepaid_Clear_Flag Public
1859 --
1860 -- DESCRIPTION:
1861 --
1862 -- To get clear flag for prepaid account
1863 --
1864 -- PARAMETERS:
1865 -- pn_invoice_id IN NUMBER invoice id
1866 -- pv_period_name IN VARCHAR2 accounting period
1867 -- pv_account_num IN VARCHAR2 prepaid account number
1868 -- pn_ledger_id IN NUMBER ledger id
1869 -- pn_legal_entity_id IN NUMBER legal entity id
1870 --
1871 -- DESIGN REFERENCES:
1872 -- APAR_Chaoqun.doc
1873 --
1874 -- CHANGE HISTORY:
1875 -- 04-May-2010 Chaoqun Wu created
1876 --==========================================================================
1877 FUNCTION Get_Prepaid_Clear_Flag(pn_invoice_id IN NUMBER,
1878 pv_period_name IN VARCHAR2,
1879 pv_account_num IN VARCHAR2,
1880 pn_ledger_id IN NUMBER,
1881 pn_legal_entity_id IN NUMBER)
1882 RETURN VARCHAR2 IS
1883 lv_clear_flag VARCHAR(1) := '0';
1884 ln_prepaid_amount NUMBER;
1885 ln_applied_amount NUMBER;
1886 lv_procedure_name VARCHAR2(40) := 'Get_Prepaid_Clear_Flag';
1887 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1888 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1889
1890 --collecting prepayment amount
1891 CURSOR prepaid_amount_cur IS
1892 SELECT SUM(NVL(Ael.Entered_Dr, 0) + -1 * NVL(Ael.Entered_Cr, 0)) Prepaid_Amount
1893 FROM Ap_Invoices_All Aia,
1894 Xla_Transaction_Entities Ent,
1895 Xla_Ae_Headers Aeh,
1896 Xla_Ae_Lines Ael,
1897 Ja_Cn_Code_Combination_v Jcc
1898 WHERE Aia.Invoice_Id = pn_invoice_id -- parameter: pn_invoice_id
1899 AND Aia.Invoice_Type_Lookup_Code = 'PREPAYMENT'
1900 --SLA condition
1901 AND Ent.Application_Id = 200
1902 AND Aia.Invoice_Id = Ent.Source_Id_Int_1
1903 AND Ent.Entity_Code = 'AP_INVOICES'
1904 AND Ent.Entity_Id = Aeh.Entity_Id
1905 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1906 --AND Ael.Accounting_Class_Code = 'PREPAID_EXPENSE'
1907 AND Ael.Accounting_Class_Code <> 'LIABILITY'
1908 AND Jcc.Code_Combination_Id = Ael.Code_Combination_Id
1909 AND Jcc.account_segment = pv_account_num --parameter: pv_account_num
1910 AND Jcc.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1911 AND Aeh.Ae_Header_Id = Ael.Ae_Header_Id
1912 --BSV condition, to handle one invoice having BSVs from different legal entity in distribution lines
1913 AND EXISTS
1914 (SELECT llbg.Bal_Seg_Value
1915 FROM ja_cn_ledger_le_bsv_gt llbg
1916 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Ael.CODE_COMBINATION_ID) =
1917 llbg.Bal_Seg_Value
1918 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1919 AND llbg.Legal_Entity_Id = pn_legal_entity_id); --parameter: pn_legal_entity_id
1920
1921 CURSOR applied_amount_cur IS
1922 --collecting applied amount
1923 SELECT SUM(-1 * NVL(Ael.Entered_Dr, 0) + NVL(Ael.Entered_Cr, 0)) Applied_Amount
1924 FROM Ap_Prepay_History_All Aph,
1925 Xla_Ae_Headers Aeh,
1926 Xla_Ae_Lines Ael,
1927 Ja_Cn_Code_Combination_v Jcc,
1928 Gl_Je_Headers Jeh,
1929 Gl_Import_References Gir
1930 WHERE Aph.Prepay_Invoice_Id = pn_invoice_id --parameter: pn_invoice_id
1931 AND Aph.Accounting_Event_Id = Aeh.Event_Id
1932 --SLA condition
1933 AND Aeh.Ae_Header_Id = Ael.Ae_Header_Id
1934 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1935 AND Ael.Accounting_Class_Code = 'PREPAID_EXPENSE'
1936 AND Jcc.Code_Combination_Id = Ael.Code_Combination_Id
1937 AND Jcc.account_segment = pv_account_num --parameter: pv_account_num
1938 AND Jcc.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1939 --GL condition
1940 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
1941 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
1942 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
1943 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
1944 AND Jeh.Status = 'P' --only collecting posted prepayment application
1945 --Period condition
1946 AND Jeh.Default_Effective_Date <=
1947 (SELECT DISTINCT Gp.End_Date
1948 FROM Gl_Ledgers Gl, Gl_Periods Gp
1949 WHERE Gl.period_set_name = Gp.period_set_name
1950 AND Gl.accounted_period_type = Gp.period_type
1951 AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1952 AND Gp.period_name = pv_period_name) --parameter: pv_period_name
1953 --BSV condition, to handle one invoice having BSVs from different legal entity in distribution lines
1954 AND EXISTS
1955 (SELECT llbg.Bal_Seg_Value
1956 FROM ja_cn_ledger_le_bsv_gt llbg
1957 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Ael.CODE_COMBINATION_ID) =
1958 llbg.Bal_Seg_Value
1959 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1960 AND llbg.Legal_Entity_Id = pn_legal_entity_id); --parameter: pn_legal_entity_id
1961
1962 BEGIN
1963 --logging for debug
1964 IF (ln_proc_level >= ln_dbg_level) THEN
1965 FND_LOG.STRING(ln_proc_level,
1966 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1967 '.begin',
1968 'Enter procedure');
1969 END IF; --ln_proc_level>=ln_dbg_level
1970
1971 FOR v_prepaid_amount_row IN prepaid_amount_cur LOOP
1972 ln_prepaid_amount := v_prepaid_amount_row.Prepaid_Amount;
1973 EXIT;
1974 END LOOP;
1975
1976 FOR v_applied_amount_row IN applied_amount_cur LOOP
1977 ln_applied_amount := v_applied_amount_row.Applied_Amount;
1978 EXIT;
1979 END LOOP;
1980
1981 IF ln_prepaid_amount = NVL(ln_applied_amount, 0) THEN
1982 lv_clear_flag := '1'; --cleared
1983 ELSE
1984 lv_clear_flag := '0'; --unclear
1985 END IF;
1986
1987 --logging for debug
1988 IF (ln_proc_level >= ln_dbg_level) THEN
1989 FND_LOG.STRING(ln_proc_level,
1990 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1991 'Exit procedure');
1992 END IF; -- (ln_proc_level>=ln_dbg_level)
1993
1994 RETURN lv_clear_flag;
1995 END Get_Prepaid_Clear_Flag;
1996
1997 --==========================================================================
1998 -- PROCEDURE NAME:
1999 --
2000 -- Get_Account_Number Public
2001 --
2002 -- DESCRIPTION:
2003 --
2004 -- To get liability and prepaid accounts
2005 --
2006 -- PARAMETERS:
2007 -- pn_ledger_id IN NUMBER ledger id
2008 -- pn_legal_entity_id IN NUMBER legal entity id
2009 -- pv_first_period IN VARCHAR first period of fiscal year
2010 -- xa_prepaid_account_num OUT ACCOUNT_NUMBER_TBL prepaid accounts array
2011 -- xa_liability_account_num OUT ACCOUNT_NUMBER_TBL liability accounts array
2012 --
2013 -- DESIGN REFERENCES:
2014 -- APAR_Chaoqun.doc
2015 --
2016 -- CHANGE HISTORY:
2017 -- 24-Aug-2010 Chaoqun Wu created for new solution described in bug 10041443
2018 --==========================================================================
2019 PROCEDURE Get_Account_Number(pn_ledger_id IN NUMBER,
2020 pn_legal_entity_id IN NUMBER,
2021 pv_first_period IN VARCHAR2,
2022 xa_prepaid_account_num OUT NOCOPY ACCOUNT_NUMBER_TBL,
2023 xa_liability_account_num OUT NOCOPY ACCOUNT_NUMBER_TBL) IS
2024 lv_procedure_name VARCHAR2(40) := 'Get_Account_Number';
2025 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2026 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
2027 la_prepaid_acc_num_array ACCOUNT_NUMBER_TBL := ACCOUNT_NUMBER_TBL();
2028 la_liability_acc_num_array ACCOUNT_NUMBER_TBL := ACCOUNT_NUMBER_TBL();
2029 ln_account_num_count NUMBER := 0;
2030
2031 --getting account number
2032 CURSOR account_number_cur(lv_accouting_class_code VARCHAR2) IS
2033 SELECT DISTINCT Account_Number
2034 FROM (SELECT Jab.Account_Segment Account_Number
2035 FROM Xla_Ae_Headers Aeh,
2036 Xla_Ae_Lines Ael,
2037 Ja_Cn_Code_Combination_v Jcc,
2038 Ja_Cn_Account_Balances Jab
2039 WHERE Jab.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2040 AND Jab.Legal_Entity_Id = pn_legal_entity_id --parameter: pn_legal_entity_id
2041 AND Jab.Period_Name = pv_first_period --parameter: pv_first_period
2042 AND Jab.Account_Segment = Jcc.Account_Segment
2043 AND Jab.Ledger_Id = Jcc.Ledger_id
2044 AND Jab.Third_Party_Id IS NOT NULL
2045 --SLA condition
2046 AND Aeh.Ae_Header_Id = Ael.Ae_Header_Id
2047 AND Ael.Accounting_Class_Code = lv_accouting_class_code --variable: lv_accouting_class_code
2048 AND Aeh.Ledger_id = pn_ledger_id --parameter: pn_ledger_id
2049 AND Jcc.Ledger_id = Aeh.Ledger_id
2050 AND Jcc.Code_Combination_Id = Ael.Code_Combination_Id
2051 --BSV condition, to handle one invoice having BSVs from different legal entity in distribution lines
2052 AND EXISTS
2053 (SELECT llbg.Bal_Seg_Value
2054 FROM ja_cn_ledger_le_bsv_gt llbg
2055 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Ael.CODE_COMBINATION_ID) =
2056 llbg.Bal_Seg_Value
2057 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2058 AND llbg.Legal_Entity_Id = pn_legal_entity_id) --parameter: pn_legal_entity_id
2059 )
2060 ORDER BY Account_Number;
2061
2062 BEGIN
2063 --logging for debug
2064 IF (ln_proc_level >= ln_dbg_level) THEN
2065 FND_LOG.STRING(ln_proc_level,
2066 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
2067 '.begin',
2068 'Enter procedure');
2069 END IF; --ln_proc_level>=ln_dbg_level
2070
2071 --Get liability account number
2072 FOR v_liability_account_num_row IN account_number_cur('LIABILITY') LOOP
2073 ln_account_num_count := ln_account_num_count + 1;
2074 la_liability_acc_num_array.EXTEND;
2075 la_liability_acc_num_array(ln_account_num_count) := v_liability_account_num_row.Account_Number;
2076 END LOOP;
2077 xa_liability_account_num := la_liability_acc_num_array;
2078
2079 --Get prepaid account number
2080 ln_account_num_count := 0;
2081 FOR v_prepaid_account_num_row IN account_number_cur('PREPAID_EXPENSE') LOOP
2082 ln_account_num_count := ln_account_num_count + 1;
2083 la_prepaid_acc_num_array.EXTEND;
2084 la_prepaid_acc_num_array(ln_account_num_count) := v_prepaid_account_num_row.Account_Number;
2085 END LOOP;
2086 xa_prepaid_account_num := la_prepaid_acc_num_array;
2087
2088 --logging for debug
2089 IF (ln_proc_level >= ln_dbg_level) THEN
2090 FND_LOG.STRING(ln_proc_level,
2091 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
2092 'Exit procedure');
2093 END IF; -- (ln_proc_level>=ln_dbg_level)
2094
2095 END Get_Account_Number;
2096
2097 --==========================================================================
2098 -- PROCEDURE NAME:
2099 --
2100 -- Get_Supplier_Number Public
2101 --
2102 -- DESCRIPTION:
2103 --
2104 -- To get supplier number, including customer number from AR Refund
2105 --
2106 -- PARAMETERS:
2107 -- In: pv_supplier_id IN NUMBER supplier id
2108 --
2109 -- DESIGN REFERENCES:
2110 -- APAR_Chaoqun.doc
2111 --
2112 -- CHANGE HISTORY:
2113 -- 24-Aug-2010 Chaoqun Wu created for new solution described in bug 10041443
2114 --==========================================================================
2115 FUNCTION Get_Supplier_Number(pn_supplier_id IN NUMBER) RETURN VARCHAR2 IS
2116 lv_supplier_num VARCHAR(30) := NULL;
2117 lv_procedure_name VARCHAR2(40) := 'Get_Supplier_Number';
2118 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2119 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
2120
2121 CURSOR supplier_num_cur IS
2122 SELECT part.party_number Supplier_Number
2123 FROM HZ_PARTIES part
2124 WHERE part.party_id = pn_supplier_id --parameter: pn_supplier_id
2125 UNION
2126 SELECT Sup.Segment1 Supplier_Number
2127 FROM ap_suppliers Sup
2128 WHERE Sup.vendor_id = pn_supplier_id; --parameter: pn_supplier_id
2129
2130 BEGIN
2131 --logging for debug
2132 IF (ln_proc_level >= ln_dbg_level) THEN
2133 FND_LOG.STRING(ln_proc_level,
2134 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
2135 '.begin',
2136 'Enter procedure');
2137 END IF; --ln_proc_level>=ln_dbg_level
2138
2139 FOR v_supplier_num_row IN supplier_num_cur LOOP
2140 lv_supplier_num := v_supplier_num_row.Supplier_Number;
2141 EXIT;
2142 END LOOP;
2143
2144 --logging for debug
2145 IF (ln_proc_level >= ln_dbg_level) THEN
2146 FND_LOG.STRING(ln_proc_level,
2147 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
2148 'Exit procedure');
2149 END IF; -- (ln_proc_level>=ln_dbg_level)
2150
2151 RETURN lv_supplier_num;
2152 END Get_Supplier_Number;
2153
2154 --==========================================================================
2155 -- PROCEDURE NAME:
2156 --
2157 -- Add_Beginning_Balance Public
2158 --
2159 -- DESCRIPTION:
2160 --
2161 -- To export beginning balance for supplier
2162 --
2163 -- PARAMETERS:
2164 -- pn_ledger_id IN NUMBER ledger id
2165 -- pn_legal_entity_id IN NUMBER legal entity id
2166 -- pn_coa_id IN NUMBER chart of account id
2167 -- pv_first_period IN VARCHAR2 first period of fiscal year
2168 -- pv_accounting_period_number IN VARCHAR2 accounting period number
2169 -- pv_accounting_year IN VARCHAR2 accounting year
2170 -- pa_liability_account_num IN ACCOUNT_NUMBER_TBL liability accounts array
2171 -- pa_prepaid_account_num IN ACCOUNT_NUMBER_TBL prepaid accounts array
2172 --
2173 -- DESIGN REFERENCES:
2174 -- APAR_Chaoqun.doc
2175 --
2176 -- CHANGE HISTORY:
2177 -- 24-Aug-2010 Chaoqun Wu created for new solution described in bug 10041443
2178 --==========================================================================
2179 FUNCTION Add_Year_Beginning_Balance(pn_ledger_id IN NUMBER,
2180 pn_legal_entity_id IN NUMBER,
2181 pn_coa_id IN NUMBER,
2182 pv_first_period IN VARCHAR2,
2183 pv_accounting_period_number IN VARCHAR2,
2184 pv_accounting_year IN VARCHAR2,
2185 pa_liability_account_num IN ACCOUNT_NUMBER_TBL,
2186 pa_prepaid_account_num IN ACCOUNT_NUMBER_TBL)
2187 RETURN VARCHAR2 IS
2188 lv_procedure_name VARCHAR2(40) := 'Add_Year_Beginning_Balance';
2189 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2190 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
2191 ln_account_num_count NUMBER := 0;
2192 lv_fun_currency VARCHAR2(100);
2193 lv_beginning_bal_exported_flag VARCHAR2(1) := 'N';
2194 lv_balance_desc VARCHAR2(200);
2195
2196 --collecting beginning balance
2197 CURSOR beginning_balance_cur(ln_supplier_id NUMBER,
2198 lv_account_number VARCHAR2,
2199 lv_accounting_class_code VARCHAR2) IS
2200 SELECT Fcv.NAME Entered_Currency,
2201 bal.Entered_Amount,
2202 bal.Functional_Amount
2203 FROM (SELECT currency_code,
2204 DECODE(lv_accounting_class_code,
2205 'LIABILITY',
2206 SUM(NVL(Orig_Begin_Balance_Cr, 0) -
2207 NVL(Orig_Begin_Balance_Dr, 0)),
2208 'PREPAID_EXPENSE',
2209 SUM(NVL(Orig_Begin_Balance_Dr, 0) -
2210 NVL(Orig_Begin_Balance_Cr, 0))) Entered_Amount,
2211 DECODE(lv_accounting_class_code,
2212 'LIABILITY',
2213 SUM(NVL(Func_Begin_Balance_Cr, 0) -
2214 NVL(Func_Begin_Balance_Dr, 0)),
2215 'PREPAID_EXPENSE',
2216 SUM(NVL(Func_Begin_Balance_Dr, 0) -
2217 NVL(Func_Begin_Balance_Cr, 0))) Functional_Amount
2218 FROM ja_cn_account_balances
2219 WHERE ledger_id = pn_ledger_id --parameter: pn_ledger_id
2220 AND legal_entity_id = pn_legal_entity_id --parameter: pn_legal_entity_id
2221 AND third_party_id = ln_supplier_id --variable: ln_supplier_id
2222 AND account_segment = lv_account_number --variable: lv_account_number
2223 AND period_name = pv_first_period --parameter: pv_first_period
2224 GROUP BY third_party_id, currency_code) bal,
2225 Fnd_Currencies_Vl Fcv
2226 WHERE bal.Functional_Amount <> 0
2227 AND Fcv.currency_code = bal.currency_code
2228 ORDER BY bal.currency_code;
2229
2230 --collecting all supplier id and customer id from account balance table
2231 CURSOR supplier_id_list_cur IS
2232 SELECT DISTINCT Third_Party_Id Supplier_Id
2233 FROM ja_cn_account_balances
2234 WHERE ledger_id = pn_ledger_id --parameter: pn_ledger_id
2235 AND legal_entity_id = pn_legal_entity_id --parameter: pn_legal_entity_id
2236 AND period_name = pv_first_period --parameter: pv_first_period
2237 AND Third_Party_Id IS NOT NULL
2238 ORDER BY Third_Party_Id;
2239
2240 --getting no invoice or payment flag
2241 CURSOR no_trans_flag_cur(ln_supplier_id NUMBER) IS
2242 SELECT DECODE(COUNT(*), 0, 'Y', 'N') no_trans_flag
2243 FROM Gl_Je_Lines Jel,
2244 Gl_Je_Headers Jeh,
2245 Gl_Import_References Gir,
2246 Ja_Cn_Code_Combination_v Jcc,
2247 Gl_Ledgers Gl,
2248 Gl_Periods Gp,
2249 Xla_Ae_Lines Ael,
2250 Xla_Ae_Headers Aeh,
2251 Ap_Suppliers Sup
2252 WHERE Jel.Je_Header_Id = Jeh.Je_Header_Id
2253 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
2254 AND Gir.Je_Line_Num = Jel.Je_Line_Num
2255 AND Jcc.Code_Combination_Id = Jel.Code_Combination_Id
2256 AND Jcc.Ledger_id = Jeh.Ledger_id
2257 AND Gl.Ledger_Id = Jcc.ledger_id
2258 AND Jeh.Status = 'P' --only collecting posted payable
2259 --Period condition
2260 AND Gl.period_set_name = Gp.period_set_name
2261 AND Jeh.Period_Name = Gp.Period_Name
2262 AND Gl.accounted_period_type = Gp.period_type
2263 AND Gp.period_year = to_number(pv_accounting_year) --parameter: pv_accounting_year
2264 AND Gp.start_date BETWEEN
2265 (SELECT Gp1.Start_Date
2266 FROM Gl_Periods Gp1
2267 WHERE Gl.period_set_name = Gp1.period_set_name
2268 AND Gl.accounted_period_type = Gp1.period_type
2269 AND Gp1.period_name = pv_first_period) --parameter: pv_first_period
2270 AND (SELECT Gp2.Start_Date
2271 FROM Gl_Periods Gp2
2272 WHERE Gl.period_set_name = Gp2.period_set_name
2273 AND Gl.accounted_period_type = Gp2.period_type
2274 AND Gp2.period_name = pv_first_period) --parameter: pv_first_period
2275 --SLA condition
2276 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
2277 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
2278 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
2279 AND Jcc.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2280 AND Ael.Party_Id = Sup.Vendor_Id(+)
2281 AND Ael.Party_Id = ln_supplier_id --variable: ln_supplier_id
2282 AND Ael.Accounting_Class_Code = 'LIABILITY' --only collecting 'Liability' account
2283 AND NVL(Sup.Vendor_Type_Lookup_Code, 'CUSTOMER') <> 'EMPLOYEE' --not considering employee supplier
2284 --BSV condition
2285 AND EXISTS
2286 (SELECT llbg.Bal_Seg_Value
2287 FROM ja_cn_ledger_le_bsv_gt llbg
2288 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Jcc.CODE_COMBINATION_ID) =
2289 llbg.Bal_Seg_Value
2290 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2291 AND llbg.Legal_Entity_Id = pn_legal_entity_id); --parameter: pn_legal_entity_id
2292
2293 BEGIN
2294 --logging for debug
2295 IF (ln_proc_level >= ln_dbg_level) THEN
2296 FND_LOG.STRING(ln_proc_level,
2297 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
2298 '.begin',
2299 'Enter procedure');
2300 END IF; --ln_proc_level>=ln_dbg_level
2301
2302 BEGIN
2303 SELECT Fcv.NAME Functional_Currency
2304 INTO lv_fun_currency
2305 FROM Gl_Ledgers Gl, Fnd_Currencies_Vl Fcv
2306 WHERE Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2307 AND Fcv.CURRENCY_CODE = Gl.Currency_Code;
2308 EXCEPTION
2309 WHEN NO_DATA_FOUND THEN
2310 NULL;
2311 END;
2312
2313 Fnd_Message.Set_Name(Application => 'JA',
2314 NAME => 'JA_CN_AP_BALANCE_DESC');
2315 lv_balance_desc := Fnd_Message.Get;
2316
2317 --Step1: get all supplier and customer ids from account balance table
2318 FOR v_supplier_id_list_row IN supplier_id_list_cur LOOP
2319 --Step2: only export beginning balance for supplier and customer with no transaction within first period
2320 /*FOR v_no_trans_flag_row IN no_trans_flag_cur(v_supplier_id_list_row.Supplier_Id)
2321 LOOP
2322 --If there is no invoice or payment created and posted in GL within first period, then export beginning balance
2323 IF v_no_trans_flag_row.no_trans_flag = 'Y'
2324 THEN*/
2325 ln_account_num_count := pa_liability_account_num.count;
2326 --Setp3.1: get liability accounts
2327 FOR ln_count IN 1 .. ln_account_num_count LOOP
2328 --Step4.1 get beginning balance for liability accounts
2329 FOR v_beginning_balance_row IN beginning_balance_cur(v_supplier_id_list_row.supplier_id,
2330 pa_liability_account_num(ln_count),
2331 'LIABILITY') LOOP
2332 lv_beginning_bal_exported_flag := 'Y';
2333 Add_YBB_XML_Node(pv_supplier_number => Get_Supplier_Number(v_supplier_id_list_row.supplier_id),
2334 pv_account_number => pa_liability_account_num(ln_count),
2335 pv_journal_created_date => NULL,
2336 pv_journal_date => NULL,
2337 pv_accounting_year => pv_accounting_year,
2338 pv_accounting_period_number => pv_accounting_period_number,
2339 pv_journal_category_number => NULL,
2340 pv_journal_number => NULL,
2341 pv_functional_currency => lv_fun_currency,
2342 pn_exchange_rate => NULL,
2343 pv_balance_side => Get_Balance_Side(pn_coa_id,
2344 pa_liability_account_num(ln_count)),
2345 pn_functional_balance => NULL,
2346 pn_entered_balance => NULL,
2347 pn_functional_amount => v_beginning_balance_row.functional_amount,
2348 pv_entered_currency => v_beginning_balance_row.entered_currency,
2349 pn_entered_amount => v_beginning_balance_row.entered_amount,
2350 pv_description => lv_balance_desc,
2351 pv_due_date => NULL,
2352 pv_applied_journal_number => NULL,
2353 pv_applied_date => NULL,
2354 pv_document_type_number => NULL,
2355 pv_transaction_type_number => NULL,
2356 pv_transaction_number => NULL,
2357 pv_invoice_number => NULL,
2358 pv_contract_number => NULL,
2359 pv_project_number => NULL,
2360 pv_settlement_method_number => NULL,
2361 pv_payment_date => NULL,
2362 pv_clear_flag => '0',
2363 pv_remittance_bill_number => NULL);
2364 END LOOP; --Step4.1: begining balance for liability account
2365 END LOOP; --Step3.1: liability account list
2366
2367 ln_account_num_count := pa_prepaid_account_num.count;
2368 --Setp3.2: get prepaid accounts
2369 FOR ln_count IN 1 .. ln_account_num_count LOOP
2370 --Step4.2 get beginning balance for prepaid accounts
2371 FOR v_beginning_balance_row IN beginning_balance_cur(v_supplier_id_list_row.Supplier_Id,
2372 pa_prepaid_account_num(ln_count),
2373 'PREPAID_EXPENSE') LOOP
2374 lv_beginning_bal_exported_flag := 'Y';
2375 Add_YBB_XML_Node(pv_supplier_number => Get_Supplier_Number(v_supplier_id_list_row.supplier_id),
2376 pv_account_number => pa_prepaid_account_num(ln_count),
2377 pv_journal_created_date => NULL,
2378 pv_journal_date => NULL,
2379 pv_accounting_year => pv_accounting_year,
2380 pv_accounting_period_number => pv_accounting_period_number,
2381 pv_journal_category_number => NULL,
2382 pv_journal_number => NULL,
2383 pv_functional_currency => lv_fun_currency,
2384 pn_exchange_rate => NULL,
2385 pv_balance_side => Get_Balance_Side(pn_coa_id,
2386 pa_prepaid_account_num(ln_count)),
2387 pn_functional_balance => NULL,
2388 pn_entered_balance => NULL,
2389 pn_functional_amount => v_beginning_balance_row.functional_amount,
2390 pv_entered_currency => v_beginning_balance_row.entered_currency,
2391 pn_entered_amount => v_beginning_balance_row.entered_amount,
2392 pv_description => lv_balance_desc,
2393 pv_due_date => NULL,
2394 pv_applied_journal_number => NULL,
2395 pv_applied_date => NULL,
2396 pv_document_type_number => NULL,
2397 pv_transaction_type_number => NULL,
2398 pv_transaction_number => NULL,
2399 pv_invoice_number => NULL,
2400 pv_contract_number => NULL,
2401 pv_project_number => NULL,
2402 pv_settlement_method_number => NULL,
2403 pv_payment_date => NULL,
2404 pv_clear_flag => '0',
2405 pv_remittance_bill_number => NULL);
2406 END LOOP; --Step4.2: begining balance for prepaid account
2407 END LOOP; --Step3.2: prepaid account list
2408 --END IF; --if no invoice or payment within first period
2409 --EXIT;
2410 --END LOOP; --Step2: no invoice or payment wihtin first period
2411 END LOOP; --Step1: supplier id list
2412
2413 --logging for debug
2414 IF (ln_proc_level >= ln_dbg_level) THEN
2415 FND_LOG.STRING(ln_proc_level,
2416 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
2417 'Exit procedure');
2418 END IF; -- (ln_proc_level>=ln_dbg_level)
2419
2420 RETURN lv_beginning_bal_exported_flag;
2421
2422 END Add_Year_Beginning_Balance;
2423
2424
2425 --==========================================================================
2426 -- PROCEDURE NAME:
2427 --
2428 -- Is_Void_Invoice private
2429 -- PARAMETERS:
2430 -- In: p_invioce_id NUMBER
2431 -- p_check_id NUMBER
2432 -- p_ae_header_id NUMBER
2433 -- p_invoice_payment_id NUMBER
2434 --
2435 --
2436 -- CHANGE HISTORY:
2437 -- 26-Mar-2013 Jar Wang created for bug 16203205
2438 --==========================================================================
2439
2440 function Is_Void_Invoice(p_invioce_id number,p_check_id number,
2441 p_ae_header_id number,p_invoice_payment_id number) return boolean
2442 is
2443 cursor cur_invoice is
2444 select trunc(accounting_date, 'mm') accounting_date
2445 from AP_INVOICE_PAYMENTS_ALL aip
2446 where aip.check_id = p_check_id;
2447 l_void_date date;
2448 l_invoice_status varchar2(30);
2449 l_enent_type_code varchar2(50);
2450 l_entered_amount number;
2451 lv_procedure_name VARCHAR2(40) := 'Is_Void_Invoice';
2452 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2453 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
2454 begin
2455 --logging for debug
2456 IF (ln_proc_level >= ln_dbg_level) THEN
2457 FND_LOG.STRING(ln_proc_level,
2458 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
2459 '.begin',
2460 'Enter procedure');
2461 END IF; --ln_proc_level>=ln_dbg_level
2462 for l_invoice in cur_invoice
2463 loop
2464
2465 if l_void_date is not null and l_void_date =l_invoice.accounting_date then
2466
2467 select AP_INVOICES_PKG.GET_APPROVAL_STATUS(ai.invoice_id,
2468 ai.invoice_amount,
2469 ai.payment_status_flag,
2470 ai.invoice_type_lookup_code) into l_invoice_status
2471 from ap_invoices_all ai
2472 where invoice_id = p_invioce_id;
2473
2474 select EVENT_TYPE_CODE
2475 into l_enent_type_code
2476 from Xla_Ae_Headers
2477 where Ae_Header_Id = p_ae_header_id;
2478
2479 if l_invoice_status='CANCELLED' then
2480
2481 l_entered_amount:= Get_Payment_Liability_Amount(p_invoice_payment_id,'E');
2482
2483 if (l_enent_type_code='INVOICE VALIDATED' and l_entered_amount>0) or (l_enent_type_code='INVOICE CANCELLED' and l_entered_amount<0) then
2484
2485 return false;
2486 end if;
2487 end if;
2488 end if;
2489 l_void_date:=l_invoice.accounting_date ;
2490
2491 end loop;
2492 --logging for debug
2493 IF (ln_proc_level >= ln_dbg_level) THEN
2494 FND_LOG.STRING(ln_proc_level,
2495 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
2496 'Exit procedure');
2497 END IF; -- (ln_proc_level>=ln_dbg_level)
2498 return true;
2499 exception
2500 when others then
2501 FND_FILE.PUT_LINE(FND_FILE.LOG,'Is_Void_Invoice:'|| sqlerrm);
2502 end Is_Void_Invoice;
2503
2504
2505 --==========================================================================
2506 -- PROCEDURE NAME:
2507 --
2508 -- Is_Different_Period private
2509 -- PARAMETERS:
2510 -- In: p_invioce_id NUMBER
2511 -- p_check_id NUMBER
2512 -- p_ae_header_id NUMBER
2513 -- pv_period_from NUMBER
2514 -- pv_period_to varchar2
2515 -- p_period_name date
2516 -- CHANGE HISTORY:
2517 -- 28-Mar-2013 Jar Wang created for bug 16548734
2518 --==========================================================================
2519 function Is_Different_Period(p_invioce_id number,p_check_id number,p_ae_header_id number,
2520 pv_period_from varchar2,pv_period_to varchar2, p_period_name date) return boolean
2521 is
2522 cursor cur_invoice is
2523 select trunc(accounting_date, 'mm') accounting_date
2524 from Xla_Ae_Headers aip
2525 where aip.ae_header_id = p_ae_header_id ;
2526
2527 l_void_date date;
2528 l_invoice_status varchar2(30);
2529 l_enent_type_code varchar2(50);
2530 l_entered_amount number;
2531 l_period_name date :=p_period_name;
2532 lv_procedure_name VARCHAR2(40) := 'Is_Void_Invoice';
2533 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2534 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
2535 begin
2536 --logging for debug
2537 IF (ln_proc_level >= ln_dbg_level) THEN
2538 FND_LOG.STRING(ln_proc_level,
2539 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
2540 '.begin',
2541 'Enter procedure');
2542 END IF; --ln_proc_level>=ln_dbg_level
2543 if pv_period_from<>pv_period_to then
2544 select AP_INVOICES_PKG.GET_APPROVAL_STATUS(ai.invoice_id,
2545 ai.invoice_amount,
2546 ai.payment_status_flag,
2547 ai.invoice_type_lookup_code)
2548 into l_invoice_status
2549 from ap_invoices_all ai
2550 where invoice_id = p_invioce_id;
2551 if l_invoice_status='CANCELLED' then
2552 for l_invoice in cur_invoice
2553 loop
2554 l_void_date:=l_invoice.accounting_date ;
2555
2556 if l_period_name<> l_void_date then
2557
2558 return false;
2559 else
2560
2561 return true;
2562 end if;
2563 end loop;
2564 end if;
2565 end if;
2566 --logging for debug
2567 IF (ln_proc_level >= ln_dbg_level) THEN
2568 FND_LOG.STRING(ln_proc_level,
2569 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
2570 'Exit procedure');
2571 END IF; -- (ln_proc_level>=ln_dbg_level)
2572
2573 return true;
2574 Exception
2575 when others then
2576 FND_FILE.PUT_LINE(FND_FILE.LOG,'Is_Different_Period:'|| sqlerrm);
2577 end Is_Different_Period;
2578
2579 --==========================================================================
2580 -- PROCEDURE NAME:
2581 --
2582 -- Add_Payables_Balance_Detail Public
2583 --
2584 -- DESCRIPTION:
2585 --
2586 -- To export receivable balance and details for each customer
2587 -- as per liability accounts, such as "Account Payable",
2588 -- "Payable in Advance", "Bill Payable", And "Other Payable",
2589 -- by period.
2590 --
2591 -- PARAMETERS:
2592 -- In: pn_legal_entity_id NUMBER identifier of legal entity
2593 -- pn_ledger_id NUMBER identifier of ledger
2594 -- pn_coa_id NUMBER identifier of chart of account
2595 -- pv_accounting_year VARCHAR2 accounting year
2596 -- pv_period_from VARCHAR2 period from
2597 -- pv_period_to VARCHAR2 period to
2598 --
2599 -- DESIGN REFERENCES:
2600 -- APAR_Chaoqun.doc
2601 --
2602 -- CHANGE HISTORY:
2603 -- 06-Apr-2010 Chaoqun Wu created
2604 -- 26-May-2010 Chaoqun Wu Updated for fixing bug# 9747676
2605 -- 2-Aug-2010 Chaoqun Wu Updated for fixing bug# 9962326
2606 -- 10-Aug-2010 Chaoqun Wu Fix bug 10008055
2607 -- 24-Aug-2010 Chaoqun Wu Updated for new solution described in bug 10041443
2608 -- 17-Sep-2010 Chaoqun Wu Updated for solution changes described in bug 10121399
2609 -- 06-Jan-2010 Jianchao Chi Updated the logic of legal entity
2610 -- 06-Mar-2013 Jar.Wang Updated for bug#16203205
2611 --==========================================================================
2612
2613 PROCEDURE Add_Payables_Balance_Detail(pn_legal_entity_id IN NUMBER,
2614 pn_ledger_id IN NUMBER,
2615 pn_coa_id IN NUMBER,
2616 pv_accounting_year IN VARCHAR2,
2617 pv_period_from IN VARCHAR2,
2618 pv_period_to IN VARCHAR2) IS
2619 lv_procedure_name VARCHAR2(40) := 'Add_Payables_Balance_Detail';
2620 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2621 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
2622 lv_invoice_exported_flag VARCHAR2(1); --indicate whether invoice information has been exported or not
2623 lv_prepay_export_flag VARCHAR(1); --use to indicate whether prepaid line for prepayment should be exported or not
2624 lv_inv_prepay_same_period_flag VARCHAR(1); --use to indicate whether invoice and its prepayment application are in the same period
2625 lv_prepaid_account_num VARCHAR(30) := NULL;
2626 ln_row_count NUMBER := 0;
2627 ln_prepaid_je_header_id NUMBER := NULL;
2628 ln_prepaid_journal_date VARCHAR(20) := NULL;
2629 --Begin: add for fixing bug 10041443
2630 lv_first_period_flag VARCHAR(1); --use to indicate whether pv_period_from is the first period
2631 la_prepaid_acc_num_array ACCOUNT_NUMBER_TBL := ACCOUNT_NUMBER_TBL();
2632 la_liability_acc_num_array ACCOUNT_NUMBER_TBL := ACCOUNT_NUMBER_TBL();
2633 lv_first_period_name VARCHAR2(30);
2634 ln_first_period_num NUMBER;
2635 lv_beginning_bal_exported_flag VARCHAR(1) := 'N'; --use to indicate whether beginning balance is exported for some supplier or not
2636 --End: add for fixing bug 10041443
2637
2638 --Step 1: Cursor for fetching data at GL and SLA level
2639 CURSOR gl_sla_cur IS
2640 SELECT DISTINCT Jeh.Je_Header_Id Je_Header_Id,
2641 Aeh.Ae_Header_Id Ae_Header_Id,
2642 Ael.Party_Id Supplier_Id,
2643 Gp.Period_Name Inv_Period_Name,
2644 Sup.Segment1 Supplier_Number,
2645 Jcc.Account_Segment Account_Number,
2646 TO_CHAR(Jeh.Date_Created, 'YYYYMMDD') Journal_Created_Date,
2647 TO_CHAR(Jeh.Default_Effective_Date, 'YYYYMMDD') Journal_Date,
2648 pv_accounting_year Accounting_Year,
2649 Gp.Period_Num Accounting_Period_Number,
2650 Jeh.Je_Category Journal_Category_Number,
2651 Fcv.Name Functional_Currency
2652 FROM Gl_Je_Lines Jel,
2653 Gl_Je_Headers Jeh,
2654 Gl_Import_References Gir,
2655 Ja_Cn_Code_Combination_v Jcc,
2656 Gl_Ledgers Gl,
2657 Gl_Periods Gp,
2658 Xla_Ae_Lines Ael,
2659 Xla_Ae_Headers Aeh,
2660 Ap_Suppliers Sup,
2661 Fnd_Currencies_Vl Fcv
2662 WHERE Jel.Je_Header_Id = Jeh.Je_Header_Id
2663 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
2664 AND Gir.Je_Line_Num = Jel.Je_Line_Num
2665 AND Jcc.Code_Combination_Id = Jel.Code_Combination_Id
2666 AND Jcc.Ledger_id = Jeh.Ledger_id
2667 AND Gl.Ledger_Id = Jcc.ledger_id
2668 AND Jeh.Status = 'P' --only collecting posted payable
2669 --Period condition
2670 AND Gl.period_set_name = Gp.period_set_name
2671 AND Jeh.Period_Name = Gp.Period_Name
2672 AND Gl.accounted_period_type = Gp.period_type
2673 AND Gp.period_year = to_number(pv_accounting_year) --parameter: pv_accounting_year
2674 AND Gp.start_date BETWEEN
2675 (SELECT Gp1.Start_Date
2676 FROM Gl_Periods Gp1
2677 WHERE Gl.period_set_name = Gp1.period_set_name
2678 AND Gl.accounted_period_type = Gp1.period_type
2679 AND Gp1.period_name = pv_period_from) --parameter: pv_period_from
2680 AND (SELECT Gp2.Start_Date
2681 FROM Gl_Periods Gp2
2682 WHERE Gl.period_set_name = Gp2.period_set_name
2683 AND Gl.accounted_period_type = Gp2.period_type
2684 AND Gp2.period_name = pv_period_to) --parameter: pv_period_to
2685 --SLA condition
2686 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
2687 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
2688 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
2689 AND Jcc.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2690 AND Ael.Party_Id = Sup.Vendor_Id(+)
2691 AND Ael.Accounting_Class_Code = 'LIABILITY' --only collecting 'Liability' account
2692 AND NVL(Sup.Vendor_Type_Lookup_Code, 'CUSTOMER') <> 'EMPLOYEE' --not considering employee supplier
2693 --BSV condition
2694 AND EXISTS (SELECT llbg.Bal_Seg_Value
2695 FROM ja_cn_ledger_le_bsv_gt llbg
2696 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Jcc.CODE_COMBINATION_ID) =
2697 llbg.Bal_Seg_Value
2698 AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2699 AND llbg.Legal_Entity_Id = pn_legal_entity_id) --parameter: pn_legal_entity_id
2700 AND Fcv.CURRENCY_CODE = Gl.Currency_Code
2701 ORDER BY Supplier_Id, Journal_Date, Je_Header_Id;
2702
2703 --Step 2: Cursor for fetching invoice id
2704 CURSOR inv_id_cur(ln_ae_header_id NUMBER) IS
2705 SELECT Aia.Invoice_Id Invoice_Id
2706 FROM Ap_Invoices_All Aia,
2707 Xla_Transaction_Entities Ent,
2708 Xla_Ae_Headers Aeh
2709 WHERE Ent.Application_Id = 200
2710 AND Aia.Invoice_Id = Ent.Source_Id_Int_1
2711 AND Ent.Entity_Code = 'AP_INVOICES'
2712 AND Ent.Entity_Id = Aeh.Entity_Id
2713 AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2714 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2715 UNION
2716 SELECT Aia.Invoice_Id Invoice_Id
2717 FROM Ap_Invoices_All Aia,
2718 Ap_Checks_All Ach,
2719 Ap_Invoice_Payments_All Aip,
2720 Xla_Transaction_Entities Ent,
2721 Xla_Ae_Headers Aeh
2722 WHERE Ent.Application_Id = 200
2723 AND Aia.Invoice_Id = Aip.Invoice_Id
2724 AND Ach.Check_Id = Aip.Check_Id
2725 AND Ach.Check_Id = Ent.Source_Id_Int_1
2726 AND Ent.Entity_Code = 'AP_PAYMENTS'
2727 AND Ent.Entity_Id = Aeh.Entity_Id
2728 AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2729 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2730 --Period condition
2731 AND Aia.Gl_Date NOT BETWEEN
2732 (SELECT Gp1.Start_Date
2733 FROM Gl_Periods Gp1, Gl_Ledgers Gl
2734 WHERE Gl.period_set_name = Gp1.period_set_name
2735 AND Gl.accounted_period_type = Gp1.period_type
2736 AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2737 AND Gp1.period_name = pv_period_from) --parameter: pv_period_from
2738 AND (SELECT Gp2.End_Date
2739 FROM Gl_Periods Gp2, Gl_Ledgers Gl
2740 WHERE Gl.period_set_name = Gp2.period_set_name
2741 AND Gl.accounted_period_type = Gp2.period_type
2742 AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2743 AND Gp2.period_name = pv_period_to); --parameter: pv_period_to
2744
2745 --Step 3: Cursor for getting accounting period for invoice and its payments
2746 CURSOR period_name_cur(lv_inv_period_name VARCHAR2,
2747 ln_invoice_id NUMBER) IS
2748 SELECT DISTINCT Gp.Period_Name, Gp.Start_Date
2749 FROM (SELECT lv_inv_period_name Period_Name
2750 FROM dual
2751 UNION
2752 SELECT DISTINCT Aip.Period_Name
2753 FROM Ap_Invoices_All Aia, Ap_Invoice_Payments_All Aip
2754 WHERE Aia.Invoice_Id = Aip.Invoice_Id
2755 AND Aia.Invoice_Id = ln_invoice_id --variable: ln_invoice_id
2756 ) Ppn --period name for payables
2757 ,
2758 Gl_Ledgers Gl,
2759 Gl_Periods Gp
2760 WHERE Ppn.Period_Name = Gp.Period_Name
2761 AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id --Fix bug 10008055
2762 AND Gl.period_set_name = Gp.period_set_name
2763 AND Gl.accounted_period_type = Gp.period_type
2764 AND Gp.period_year = to_number(pv_accounting_year) --parameter: pv_accounting_year
2765 AND Gp.start_date BETWEEN
2766 (SELECT Gp1.Start_Date
2767 FROM Gl_Periods Gp1
2768 WHERE Gl.period_set_name = Gp1.period_set_name
2769 AND Gl.accounted_period_type = Gp1.period_type
2770 AND Gp1.period_name = lv_inv_period_name) --variable: lv_inv_period_name
2771 AND (SELECT Gp2.Start_Date
2772 FROM Gl_Periods Gp2
2773 WHERE Gl.period_set_name = Gp2.period_set_name
2774 AND Gl.accounted_period_type = Gp2.period_type
2775 AND Gp2.period_name = pv_period_to) --parameter: pv_period_to
2776 ORDER BY Gp.Start_Date;
2777
2778 --Step 4: Cursor for fetching invoice
2779 CURSOR inv_cur(ln_invoice_id NUMBER,
2780 ln_ae_header_id NUMBER,
2781 lv_period_name VARCHAR2) IS
2782 SELECT DISTINCT Aia.Invoice_Id Invoice_Id,
2783 Aia.Terms_Id Terms_Id,
2784 Jeh.Je_Header_Id Je_Header_Id,
2785 Aia.Invoice_Currency_Code Currency_Code,
2786 Jeh.Period_Name Period_Name,
2787 TO_CHAR(Jeh.Date_Created, 'YYYYMMDD') Journal_Created_Date,
2788 TO_CHAR(Jeh.Default_Effective_Date, 'YYYYMMDD') Journal_Date,
2789 Jeh.Je_Category Journal_Category_Number,
2790 NVL(ROUND(Aia.Exchange_Rate, 4), 1) Exchange_Rate,
2791 '' Balance_Side,
2792 Fcv.Name Entered_Currency,
2793 Aia.Description Description,
2794 '' Due_Date,
2795 NULL Applied_Date,
2796 'PAYABLE' Document_Type_Number,
2797 Aia.Invoice_Type_Lookup_Code Transaction_Type_Number,
2798 Aia.Invoice_Num Transaction_Number,
2799 '' Invoice_Number,
2800 Pha.Segment1 Contract_Number,
2801 Pap.Segment1 Project_Number,
2802 Aia.Payment_Method_Code Settlement_Method_Number,
2803 NULL Payment_Date,
2804 '' Clear_Flag,
2805 '' Remittance_Bill_Number
2806 FROM Ap_Invoices_All Aia,
2807 Pa_Projects_All Pap,
2808 Po_Headers_All Pha,
2809 Fnd_Currencies_Vl Fcv,
2810 Xla_Transaction_Entities Ent,
2811 Xla_Ae_Headers Aeh,
2812 Xla_Ae_Lines Ael,
2813 Gl_Je_Headers Jeh,
2814 Gl_Import_References Gir
2815 WHERE Aia.Invoice_Id = ln_invoice_id --variable: ln_invoice_id
2816 --PO, project, currency condition
2817 AND Aia.Project_Id = Pap.Project_Id(+)
2818 AND Aia.Quick_Po_Header_Id = Pha.Po_Header_Id(+)
2819 AND Fcv.Currency_Code = Aia.Invoice_Currency_Code
2820 --SLA condition
2821 AND Ent.Application_Id = 200
2822 AND Aia.Invoice_Id = Ent.Source_Id_Int_1
2823 AND Ent.Entity_Code = 'AP_INVOICES'
2824 AND Ent.Entity_Id = Aeh.Entity_Id
2825 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2826 --AND Aeh.Event_Type_Code <> 'PREPAYMENT APPLIED'
2827 --GL condition
2828 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
2829 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
2830 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
2831 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
2832
2833 AND Jeh.Status = 'P' --only collecting posted invoice
2834 --Whether ae_header_id is derived from invoice or not
2835 --To handle case like prepayment application and one invoice with different period
2836 --If ae_header_id is derived from invoice,
2837 --it indicates that ae_header_id derived from invoice or prepayment application and it may be used as a condition
2838 AND ((EXISTS (SELECT Aid.Invoice_Id
2839 FROM Ap_Invoice_Distributions_All Aid
2840 WHERE Aid.Invoice_Id = ln_invoice_id --variable: ln_invoice_id
2841 AND Aid.period_name = lv_period_name) --variable: lv_period_name
2842 AND Aeh.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2843 OR (NOT EXISTS
2844 (SELECT Aid.Invoice_Id
2845 FROM Ap_Invoice_Distributions_All Aid
2846 WHERE Aid.Invoice_Id = ln_invoice_id --variable: ln_invoice_id
2847 AND Aid.period_name = lv_period_name)) --variable: lv_period_name
2848 );
2849
2850 --Step5: Cursor for fetching payments based on accounting period
2851 CURSOR pay_cur(ln_invoice_id NUMBER,
2852 ln_ae_header_id NUMBER,
2853 lv_period_name VARCHAR2) IS
2854 SELECT DISTINCT --updated by jarwang for bug 16611717
2855 Aia.Invoice_Id Invoice_Id,
2856 Aip.Invoice_Payment_Id Invoice_Payment_Id,
2857 Ach.Check_Id Check_Id,
2858 Aeh.Ae_Header_Id Ae_Header_Id,
2859 Jeh.Je_Header_Id Je_Header_Id,
2860 Aia.Terms_Id Terms_Id,
2861 Ach.Currency_Code Currency_Code,
2862 Aip.Period_Name Period_Name,
2863 TO_CHAR(Jeh.Date_Created, 'YYYYMMDD') Journal_Created_Date,
2864 TO_CHAR(Jeh.Default_Effective_Date, 'YYYYMMDD') Journal_Date,
2865 pv_accounting_year Accounting_Year,
2866 '' Accounting_Period_Number,
2867 Jeh.Je_Category Journal_Category_Number,
2868 NVL(ROUND(Ach.Exchange_Rate, 4), 1) Exchange_Rate,
2869 '' Balance_Side,
2870 Fcv.Name Entered_Currency,
2871 Ach.Description Description,
2872 Aia.Terms_Date Due_Date,
2873 --TO_CHAR(Aia.Gl_Date
2874 -- ,'YYYYMMDD') Applied_Date_Pay_Line,
2875 --TO_CHAR(Aip.Accounting_Date
2876 -- ,'YYYYMMDD') Applied_Date_Inv_Line,
2877 'PAYMENT' Document_Type_Number,
2878 Ach.Payment_Type_Flag Transaction_Type_Number,
2879 TO_CHAR(Ach.Check_Number) Transaction_Number,
2880 '' Invoice_Number,
2881 Pha.Segment1 Contract_Number,
2882 Pap.Segment1 Project_Number,
2883 Ach.Payment_Method_Code Settlement_Method_Number,
2884 TO_CHAR(Ach.CHECK_DATE, 'YYYYMMDD') Payment_Date,
2885 '' Clear_Flag,
2886 '' Remittance_Bill_Number
2887 FROM Ap_Invoices_All Aia,
2888 Ap_Checks_All Ach,
2889 Ap_Invoice_Payments_All Aip,
2890 Xla_Transaction_Entities Ent,
2891 Xla_Ae_Headers Aeh,
2892 Xla_Ae_Lines Ael,
2893 Gl_Je_Headers Jeh,
2894 Gl_Import_References Gir,
2895 Pa_Projects_All Pap,
2896 Po_Headers_All Pha,
2897 Fnd_Currencies_Vl Fcv
2898 WHERE Aia.Invoice_Id = Aip.Invoice_Id
2899 AND Ach.Check_Id = Aip.Check_Id
2900 AND Aia.Invoice_Id = ln_invoice_id -- variable: ln_invoice_id
2901 AND Aip.Period_Name = lv_period_name --variable: lv_period_name
2902 --SLA condition
2903 AND Ent.Application_Id = 200
2904 AND Ach.Check_Id = Ent.Source_Id_Int_1
2905 AND Ent.Entity_Code = 'AP_PAYMENTS'
2906 AND Ent.Entity_Id = Aeh.Entity_Id
2907 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2908 AND (Aia.Invoice_Type_Lookup_Code = 'PREPAYMENT' --collecting payment of prepayment invoice
2909 OR NOT EXISTS
2910 ( --to filter prepayment application
2911 SELECT Ael1.Ae_Header_Id
2912 FROM Xla_Ae_Lines Ael1
2913 WHERE Ael1.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2914 AND Ael1.Accounting_Class_Code = 'PREPAID_EXPENSE'))
2915 --Judge whether ae_header_id is from payment or not,
2916 --if Yes, indicating that invoice id was derived from payment journal
2917 --else, invoice id was derived from invoice journal itself
2918 AND ((EXISTS
2919 (SELECT Aeh1.Ae_Header_Id
2920 FROM Xla_Transaction_Entities Ent1, Xla_Ae_Headers Aeh1
2921 WHERE Ent1.Entity_Code = 'AP_PAYMENTS'
2922 AND Ent1.Entity_Id = Aeh1.Entity_Id
2923 AND Aeh1.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2924 AND Aeh1.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2925 AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2926 ) OR NOT EXISTS
2927 (SELECT Aeh1.Ae_Header_Id
2928 FROM Xla_Transaction_Entities Ent1, Xla_Ae_Headers Aeh1
2929 WHERE Ent1.Entity_Code = 'AP_PAYMENTS'
2930 AND Ent1.Entity_Id = Aeh1.Entity_Id
2931 AND Aeh1.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2932 AND Aeh1.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2933 )
2934 --GL condition
2935 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
2936 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
2937 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
2938 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
2939 AND Jeh.Status = 'P' --only collecting posted payment
2940 --PO, project condition
2941 AND Aia.Project_Id = Pap.Project_Id(+)
2942 AND Aia.Quick_Po_Header_Id = Pha.Po_Header_Id(+)
2943 AND Ael.business_class_code='AP_LIAB' --added by jarwang for bug#16203205
2944 AND Fcv.Currency_Code = Ach.Currency_Code;
2945
2946 --Step6: Cursor for fetching prepayment and its applied invoice information
2947 CURSOR prepay_cur(ln_invoice_id NUMBER) IS
2948 SELECT DISTINCT Aia.Invoice_Id Invoice_Id,
2949 Aeh.Ae_Header_Id Ae_Header_Id,
2950 Jeh.Je_Header_Id Je_Header_Id,
2951 Aia.Terms_Id Terms_Id,
2952 Aia.Invoice_Currency_Code Currency_Code,
2953 Jeh.Period_Name Period_Name,
2954 TO_CHAR(Jeh.Date_Created, 'YYYYMMDD') Journal_Created_Date,
2955 TO_CHAR(Jeh.Default_Effective_Date, 'YYYYMMDD') Journal_Date,
2956 pv_accounting_year Accounting_Year,
2957 '' Accounting_Period_Number,
2958 Jeh.Je_Category Journal_Category_Number,
2959 NVL(ROUND(Aia.Exchange_Rate, 4), 1) Exchange_Rate,
2960 '' Balance_Side,
2961 Fcv.Name Entered_Currency,
2962 Aia.Description Description,
2963 Aia.Terms_Date Due_Date,
2964 TO_CHAR(Aia.Gl_Date, 'YYYYMMDD') Applied_Date_Prepaid_Line,
2965 'PAYABLE' Document_Type_Number,
2966 Aia.Invoice_Type_Lookup_Code Transaction_Type_Number,
2967 TO_CHAR(Aia.Invoice_Num) Transaction_Number,
2968 '' Invoice_Number,
2969 Pha.Segment1 Contract_Number,
2970 Pap.Segment1 Project_Number,
2971 Aia.Payment_Method_Code Settlement_Method_Number,
2972 '' Payment_Date,
2973 DECODE(Aia.PAYMENT_STATUS_FLAG, 'Y', '1', '0') Clear_Flag,
2974 '' Remittance_Bill_Number
2975 FROM Ap_Invoices_All Aia,
2976 Xla_Transaction_Entities Ent,
2977 Xla_Ae_Headers Aeh,
2978 Xla_Ae_Lines Ael,
2979 Gl_Je_Headers Jeh,
2980 Gl_Import_References Gir,
2981 Pa_Projects_All Pap,
2982 Po_Headers_All Pha,
2983 Fnd_Currencies_Vl Fcv
2984 WHERE Aia.Invoice_Id = ln_invoice_id -- variable: ln_invoice_id
2985 --SLA condition
2986 AND Ent.Application_Id = 200
2987 AND Aia.Invoice_Id = Ent.Source_Id_Int_1
2988 AND Ent.Entity_Code = 'AP_INVOICES'
2989 AND Ent.Entity_Id = Aeh.Entity_Id
2990 AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2991 AND (Aia.Invoice_Type_Lookup_Code = 'PREPAYMENT' --collecting prepayment inforamtion
2992 OR NOT EXISTS
2993 ( --collecting invoice which is applied with prepayment
2994 SELECT Ael1.Ae_Header_Id
2995 FROM Xla_Ae_Lines Ael1
2996 WHERE Ael1.Ae_Header_Id = Aeh.Ae_Header_Id
2997 AND Ael1.Accounting_Class_Code = 'PREPAID_EXPENSE'))
2998 --GL condition
2999 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
3000 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
3001 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
3002 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
3003 AND Jeh.Status = 'P' --only collecting posted prepayment and its applied invoice
3004 --PO, project condition
3005 AND Aia.Project_Id = Pap.Project_Id(+)
3006 AND Aia.Quick_Po_Header_Id = Pha.Po_Header_Id(+)
3007 AND Fcv.Currency_Code = Aia.Invoice_Currency_Code;
3008
3009 --Cursor for fetching prepaid invoice id
3010 CURSOR prepay_inv_id_cur(ln_invoice_id NUMBER, ln_ae_header_id NUMBER) IS
3011 SELECT Aph.Prepay_Invoice_Id
3012 FROM Ap_Prepay_History_All Aph, Xla_Ae_Headers Aeh
3013 WHERE Aph.Invoice_Id = ln_invoice_id --variable: ln_invoice_id
3014 AND Aph.Accounting_Event_Id = Aeh.Event_Id
3015 AND Aeh.Ae_Header_Id = ln_ae_header_id; --variable: ln_ae_header_id
3016
3017 --Cursor for fetching prepaid account number
3018 CURSOR prepaid_account_num_cur(ln_ae_header_id NUMBER) IS
3019 SELECT DISTINCT Jcc.account_segment Prepaid_Account_Num
3020 FROM Ja_Cn_Code_Combination_v Jcc,
3021 Xla_Ae_Headers Aeh,
3022 Xla_Ae_Lines Ael
3023 WHERE Ael.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
3024 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
3025 AND Aeh.Ledger_Id = Jcc.Ledger_id
3026 AND ((Aeh.Event_Type_Code = 'PREPAYMENT APPLIED' AND
3027 Ael.Accounting_Class_Code = 'PREPAID_EXPENSE') OR
3028 (Aeh.Event_Type_Code <> 'PREPAYMENT APPLIED' AND
3029 Ael.Accounting_Class_Code <> 'LIABILITY'))
3030 AND Jcc.Code_Combination_Id = Ael.Code_Combination_Id
3031 AND Jcc.Ledger_id = pn_ledger_id; --parameter: pn_ledger_id
3032 l_reversal_flag pls_integer :=0 ;
3033 BEGIN
3034 --logging for debug
3035 IF (ln_proc_level >= ln_dbg_level) THEN
3036 FND_LOG.STRING(ln_proc_level,
3037 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
3038 '.begin',
3039 'Enter procedure');
3040 -- logging the parameters
3041 FND_LOG.STRING(ln_proc_level,
3042 lv_procedure_name || '.parameters',
3043 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
3044 'pn_ledger_id=' || pn_ledger_id || ',' || 'pn_coa_id=' ||
3045 pn_coa_id || ',' || 'pv_accounting_year=' ||
3046 pv_accounting_year || ',' || 'pv_period_from=' ||
3047 pv_period_from || ',' || 'pv_period_to=' ||
3048 pv_period_to);
3049 END IF; --ln_proc_level>=ln_dbg_level
3050
3051 --Begin: add for fixing bug 10041443
3052 SELECT DECODE(COUNT(Gp.Period_Name),
3053 0,
3054 'N', --Not first period, in this case, year begnning balance would not be exported
3055 'Y') --It is first period and year beginning balance should be exported
3056 INTO lv_first_period_flag
3057 FROM Gl_Ledgers Gl, Gl_Periods Gp
3058 WHERE Gl.period_set_name = Gp.period_set_name
3059 AND Gl.accounted_period_type = Gp.period_type
3060 AND Gp.period_year = TO_NUMBER(pv_accounting_year) --parameter: pv_accounting_year
3061 AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
3062 AND Gp.Period_Name = pv_period_from --parameter: pv_period_from
3063 AND Gp.Start_Date =
3064 (SELECT MIN(Gp1.START_DATE)
3065 FROM Gl_Periods Gp1
3066 WHERE Gl.period_set_name = Gp1.period_set_name
3067 AND Gl.accounted_period_type = Gp1.period_type
3068 AND Gp1.period_year = Gp.period_year);
3069 IF lv_first_period_flag = 'Y' THEN
3070 BEGIN
3071 SELECT Gp.Period_Name, Gp.Period_Num
3072 INTO lv_first_period_name, ln_first_period_num
3073 FROM Gl_Ledgers Gl, Gl_Periods Gp
3074 WHERE Gl.period_set_name = Gp.period_set_name
3075 AND Gl.accounted_period_type = Gp.period_type
3076 AND Gp.period_year = TO_NUMBER(pv_accounting_year) --parameter: pv_accounting_year
3077 AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
3078 AND Gp.Start_Date =
3079 (SELECT MIN(Gp1.START_DATE)
3080 FROM Gl_Periods Gp1
3081 WHERE Gl.period_set_name = Gp1.period_set_name
3082 AND Gl.accounted_period_type = Gp1.period_type
3083 AND Gp1.period_year = Gp.period_year);
3084 EXCEPTION
3085 WHEN NO_DATA_FOUND THEN
3086 NULL;
3087 END;
3088
3089 Get_Account_Number(pn_ledger_id => pn_ledger_id,
3090 pn_legal_entity_id => pn_legal_entity_id,
3091 pv_first_period => lv_first_period_name,
3092 xa_prepaid_account_num => la_prepaid_acc_num_array,
3093 xa_liability_account_num => la_liability_acc_num_array);
3094
3095 lv_beginning_bal_exported_flag := Add_Year_Beginning_Balance(pn_ledger_id => pn_ledger_id,
3096 pn_legal_entity_id => pn_legal_entity_id,
3097 pn_coa_id => pn_coa_id,
3098 pv_first_period => lv_first_period_name,
3099 pv_accounting_period_number => TO_CHAR(ln_first_period_num),
3100 pv_accounting_year => pv_accounting_year,
3101 pa_liability_account_num => la_liability_acc_num_array,
3102 pa_prepaid_account_num => la_prepaid_acc_num_array);
3103 END IF; --if it is period from is selected as first period
3104 --End: add for fixing bug 10041443
3105
3106 --Step 1: Fetching basic journal information of posted invoice with accounting class 'Liability'
3107 FOR v_gl_sla_row IN gl_sla_cur LOOP
3108 FND_FILE.PUT_LINE(FND_FILE.LOG, '**************Start**********************');
3109 FND_FILE.PUT_LINE(FND_FILE.LOG, 'step1: Supplier_Id='||v_gl_sla_row.Supplier_Id||',Ae_Header_Id='||v_gl_sla_row.Ae_Header_Id);
3110 --Step 2: Fetching invoice id
3111 FOR v_inv_id_row IN inv_id_cur(v_gl_sla_row.Ae_Header_Id) LOOP
3112 FND_FILE.PUT_LINE(FND_FILE.LOG, 'step2: Invoice_Id='||v_inv_id_row.Invoice_Id);
3113 lv_invoice_exported_flag := 'N'; --indicate invoice information has not been exported yet
3114 lv_prepay_export_flag := 'Y'; --indicate prepaid line for prepayment should be exported
3115 --Step 3: Fetching accounting period for invoice and its payments based on invoice id
3116 FOR v_period_name_row IN period_name_cur(v_gl_sla_row.Inv_Period_Name,
3117 v_inv_id_row.Invoice_Id) LOOP
3118 FND_FILE.PUT_LINE(FND_FILE.LOG, 'step3: period_name='||v_period_name_row.period_name||', Start_Date='||v_period_name_row.Start_Date);
3119 --Step 4: Fetching information about invoice based on accounting period
3120 FOR v_inv_row IN inv_cur(v_inv_id_row.Invoice_Id,
3121 v_gl_sla_row.Ae_Header_Id,
3122 v_period_name_row.period_name) LOOP
3123 ln_row_count := ln_row_count + 1;
3124 l_reversal_flag:=0;
3125 FND_FILE.PUT_LINE(FND_FILE.LOG, 'step4: Invoice_Number='||v_inv_row.Transaction_Number);
3126 --Step 5: Fetching information about payments based on accounting period
3127 FOR v_pay_row IN pay_cur(v_inv_id_row.Invoice_Id,
3128 v_gl_sla_row.Ae_Header_Id,
3129 v_period_name_row.period_name) LOOP
3130 FND_FILE.PUT_LINE(FND_FILE.LOG, 'step5: Check_Id='||v_pay_row.Check_Id);
3131 --indicate invoice information has been exported,
3132 --and would not be exported again if there is no payments for it posted to GL
3133 lv_invoice_exported_flag := 'Y';
3134
3135 --If 'Y', prepaid line for prepayment invoice should be exported,
3136 --Else prepaid line should not be exported
3137 SELECT DECODE(lv_prepay_export_flag,
3138 'Y', --if prepaid line has not been exported
3139 DECODE(COUNT(*),
3140 0,
3141 'N', --it is not a prepayment invoice or current period is not the one prepayment created in
3142 'Y'), --it is a prepayment invoice and current period is the one prepayment created in
3143 'N') --prepaid line has been exported
3144 INTO lv_prepay_export_flag
3145 FROM Ap_Invoices_v
3146 WHERE Invoice_Id = v_inv_id_row.Invoice_Id
3147 AND Period_Name = v_period_name_row.period_name
3148 AND Invoice_Type_Lookup_Code = 'PREPAYMENT';
3149
3150 --If 'Y', accounting period of prepayment applicaction is the same with its prepayment,
3151 --in this case, prepaid line for prepayment invoice would not be exported again,
3152 --Else they are in different accounting periods
3153 SELECT DECODE(COUNT(*), 0, 'N', 'Y')
3154 INTO lv_inv_prepay_same_period_flag
3155 FROM Ap_Prepay_History_All Aph,
3156 Gl_Ledgers Gl,
3157 Gl_Periods Gp
3158 WHERE Prepay_Invoice_Id = v_inv_id_row.Invoice_Id --Updated for fixing bug# 9747676
3159 AND Accounting_Date BETWEEN Gp.Start_Date AND Gp.End_Date
3160 AND Gl.period_set_name = Gp.period_set_name
3161 AND Gl.accounted_period_type = Gp.period_type
3162 AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
3163 AND Gp.period_name = v_period_name_row.period_name;
3164
3165 --Step5.1: Add Prepaid line
3166 IF lv_prepay_export_flag = 'Y' AND
3167 lv_inv_prepay_same_period_flag = 'N' THEN
3168 lv_prepay_export_flag := 'N';
3169
3170 /* --For prepaid account number
3171 IF lv_prepaid_account_num IS NULL
3172 THEN
3173 lv_prepaid_account_num := Get_Prepaid_Account_Number(v_inv_id_row.Invoice_Id
3174 ,pn_ledger_id);
3175 END IF;*/
3176 FOR v_prepaid_account_num_row IN prepaid_account_num_cur(v_gl_sla_row.Ae_Header_Id) LOOP
3177 lv_prepaid_account_num := v_prepaid_account_num_row.prepaid_account_num;
3178 Add_XML_Node(pv_supplier_number => Get_Supplier_Number(v_gl_sla_row.Supplier_Number,
3179 v_inv_id_row.Invoice_Id),
3180 pv_account_number => lv_prepaid_account_num,
3181 pv_journal_created_date => v_inv_row.Journal_Created_Date,
3182 pv_journal_date => v_inv_row.Journal_Date,
3183 pv_accounting_year => v_gl_sla_row.Accounting_Year,
3184 pv_accounting_period_number => Get_Accounting_Period_Number(pn_ledger_id,
3185 v_inv_row.Invoice_Id,
3186 v_inv_row.Period_Name),
3187 pv_journal_category_number => v_inv_row.Journal_Category_Number,
3188 pv_journal_number => Get_Journal_Number(v_inv_row.je_header_id,
3189 pn_legal_entity_id) --Add a parameter:legal entity, by Jianchao Chi for V2 Upgrade 06-Jan-2011
3190 ,
3191 pv_functional_currency => v_gl_sla_row.Functional_Currency,
3192 pn_exchange_rate => v_inv_row.Exchange_Rate,
3193 pv_balance_side => Get_Balance_Side(pn_coa_id,
3194 lv_prepaid_account_num),
3195 pn_functional_balance => Get_Prepaid_Balance(v_gl_sla_row.Supplier_Id,
3196 pn_ledger_id,
3197 pn_legal_entity_id,
3198 lv_prepaid_account_num,
3199 v_inv_row.currency_code,
3200 v_pay_row.period_name,
3201 'F'),
3202 pn_entered_balance => Get_Prepaid_Balance(v_gl_sla_row.Supplier_Id,
3203 pn_ledger_id,
3204 pn_legal_entity_id,
3205 lv_prepaid_account_num,
3206 v_inv_row.currency_code,
3207 v_pay_row.period_name,
3208 'E'),
3209 pn_functional_amount => Get_Prepaid_Amount(v_gl_sla_row.ae_header_id,
3210 pn_ledger_id,
3211 pn_legal_entity_id,
3212 lv_prepaid_account_num,
3213 'F'),
3214 pv_entered_currency => v_inv_row.Entered_Currency,
3215 pn_entered_amount => Get_Prepaid_Amount(v_gl_sla_row.ae_header_id,
3216 pn_ledger_id,
3217 pn_legal_entity_id,
3218 lv_prepaid_account_num,
3219 'E'),
3220 pv_description => v_inv_row.Description,
3221 pv_due_date => Get_Due_Date(v_inv_row.Invoice_Id,
3222 v_pay_row.Invoice_Payment_Id),
3223 pv_applied_journal_number => '',
3224 pv_applied_date => '',
3225 pv_document_type_number => v_inv_row.Document_Type_Number,
3226 pv_transaction_type_number => v_inv_row.Transaction_Type_Number,
3227 pv_transaction_number => v_inv_row.Transaction_Number,
3228 pv_invoice_number => v_inv_row.Invoice_Number,
3229 pv_contract_number => v_inv_row.Contract_Number,
3230 pv_project_number => v_inv_row.Project_Number,
3231 pv_settlement_method_number => v_inv_row.Settlement_Method_Number,
3232 pv_payment_date => v_inv_row.Payment_Date,
3233 pv_clear_flag => '0',
3234 pv_remittance_bill_number => '' /*Get_Remittance_Bill_Number(pn_coa_id
3235 ,v_inv_row.Invoice_Id)*/ --Updated for fixing bug# 9793920
3236 );
3237 END LOOP; --prepaid_account_num_cur
3238 END IF; --add prepaid line
3239
3240 if Is_Different_Period(v_inv_id_row.Invoice_Id,v_pay_row.Check_Id ,v_gl_sla_row.Ae_Header_Id,
3241 pv_period_from ,pv_period_to , v_period_name_row.Start_Date) then
3242
3243 IF l_reversal_flag=0 THEN
3244 --Step5.2: Add Invoice line
3245 Add_XML_Node(pv_supplier_number => Get_Supplier_Number(v_gl_sla_row.Supplier_Number,
3246 v_inv_id_row.Invoice_Id),
3247 pv_account_number => v_gl_sla_row.Account_Number,
3248 pv_journal_created_date => v_inv_row.Journal_Created_Date,
3249 pv_journal_date => v_inv_row.Journal_Date,
3250 pv_accounting_year => v_gl_sla_row.Accounting_Year,
3251 pv_accounting_period_number => Get_Accounting_Period_Number(pn_ledger_id,
3252 v_inv_row.Invoice_Id,
3253 v_inv_row.Period_Name),
3254 pv_journal_category_number => v_inv_row.Journal_Category_Number,
3255 pv_journal_number => Get_Journal_Number(v_inv_row.je_header_id,
3256 pn_legal_entity_id) --Add a parameter:legal entity, by Jianchao Chi for V2 Upgrade 06-Jan-2011
3257 ,
3258 pv_functional_currency => v_gl_sla_row.Functional_Currency,
3259 pn_exchange_rate => v_inv_row.Exchange_Rate,
3260 pv_balance_side => Get_Balance_Side(pn_coa_id,
3261 v_gl_sla_row.Account_Number),
3262 pn_functional_balance => Get_Liability_Balance(v_gl_sla_row.Supplier_Id,
3263 pn_ledger_id,
3264 pn_legal_entity_id,
3265 v_gl_sla_row.Account_Number,
3266 v_inv_row.currency_code,
3267 v_pay_row.period_name,
3268 'F'),
3269 pn_entered_balance => Get_Liability_Balance(v_gl_sla_row.Supplier_Id,
3270 pn_ledger_id,
3271 pn_legal_entity_id,
3272 v_gl_sla_row.Account_Number,
3273 v_inv_row.currency_code,
3274 v_pay_row.period_name,
3275 'E'),
3276 pn_functional_amount => Get_Liability_Amount(v_inv_row.Invoice_Id,
3277 v_gl_sla_row.ae_header_id,
3278 pn_ledger_id,
3279 pn_legal_entity_id,
3280 'F'),
3281 pv_entered_currency => v_inv_row.Entered_Currency,
3282 pn_entered_amount => Get_Liability_Amount(v_inv_row.Invoice_Id,
3283 v_gl_sla_row.ae_header_id,
3284 pn_ledger_id,
3285 pn_legal_entity_id,
3286 'E'),
3287 pv_description => v_inv_row.Description,
3288 pv_due_date => Get_Due_Date(v_inv_row.Invoice_Id,
3289 v_pay_row.Invoice_Payment_Id),
3290 pv_applied_journal_number => Get_Journal_Number(v_pay_row.je_header_id,
3291 pn_legal_entity_id) --Add a parameter:legal entity, by Jianchao Chi for V2 Upgrade 06-Jan-2011
3292 ,
3293 pv_applied_date => NULL --v_pay_row.Journal_Date --Update for fixing bug 10121399
3294 ,
3295 pv_document_type_number => v_inv_row.Document_Type_Number,
3296 pv_transaction_type_number => v_inv_row.Transaction_Type_Number,
3297 pv_transaction_number => v_inv_row.Transaction_Number,
3298 pv_invoice_number => v_inv_row.Invoice_Number,
3299 pv_contract_number => v_inv_row.Contract_Number,
3300 pv_project_number => v_inv_row.Project_Number,
3301 pv_settlement_method_number => v_inv_row.Settlement_Method_Number,
3302 pv_payment_date => v_inv_row.Payment_Date,
3303 pv_clear_flag => Get_Liability_Clear_Flag(v_inv_row.Invoice_Id,
3304 v_pay_row.period_name,
3305 pn_ledger_id,
3306 pn_legal_entity_id),
3307 pv_remittance_bill_number => '' /*Get_Remittance_Bill_Number(pn_coa_id
3308 ,v_inv_row.Invoice_Id)*/ --Updated for fixing bug# 9793920
3309 );
3310 l_reversal_flag:=l_reversal_flag+1;
3311
3312 End IF;
3313 --Step5.3: Add Payment line
3314 --added by jarwang for bug 16203205
3315
3316 IF Is_Void_Invoice(v_inv_id_row.Invoice_Id,v_pay_row.Check_Id,v_gl_sla_row.Ae_Header_Id,v_pay_row.invoice_payment_id) then
3317
3318 Add_XML_Node(pv_supplier_number => Get_Supplier_Number(v_gl_sla_row.Supplier_Number,
3319 v_inv_id_row.Invoice_Id),
3320 pv_account_number => v_gl_sla_row.Account_Number,
3321 pv_journal_created_date => v_pay_row.Journal_Created_Date,
3322 pv_journal_date => v_pay_row.Journal_Date,
3323 pv_accounting_year => v_pay_row.Accounting_Year,
3324 pv_accounting_period_number => Get_Accounting_Period_Number(pn_ledger_id,
3325 NULL,
3326 v_pay_row.Period_Name),
3327 pv_journal_category_number => v_pay_row.Journal_Category_Number,
3328 pv_journal_number => Get_Journal_Number(v_pay_row.je_header_id,
3329 pn_legal_entity_id) --Add a parameter:legal entity, by Jianchao Chi for V2 Upgrade 06-Jan-2011
3330 ,
3331 pv_functional_currency => v_gl_sla_row.Functional_Currency,
3332 pn_exchange_rate => TO_CHAR(v_pay_row.Exchange_Rate),
3333 pv_balance_side => Get_Balance_Side(pn_coa_id,
3334 v_gl_sla_row.Account_Number),
3335 pn_functional_balance => Get_Liability_Balance(v_gl_sla_row.Supplier_Id,
3336 pn_ledger_id,
3337 pn_legal_entity_id,
3338 v_gl_sla_row.Account_Number,
3339 v_pay_row.currency_code,
3340 v_pay_row.period_name,
3341 'F'),
3342 pn_entered_balance => Get_Liability_Balance(v_gl_sla_row.Supplier_Id,
3343 pn_ledger_id,
3344 pn_legal_entity_id,
3345 v_gl_sla_row.Account_Number,
3346 v_pay_row.currency_code,
3347 v_pay_row.period_name,
3348 'E'),
3349 pn_functional_amount => --Updated for fixing bug# 9747960
3350 /*Get_Liability_Amount(NULL
3351 ,v_pay_row.ae_header_id
3352 ,pn_ledger_id
3353 ,pn_legal_entity_id
3354 ,'F')*/ Get_Payment_Liability_Amount(v_pay_row.invoice_payment_id,
3355 'F'),
3356 pv_entered_currency => v_pay_row.Entered_Currency,
3357 pn_entered_amount => --Updated for fixing bug# 9747960
3358 /*Get_Liability_Amount(NULL
3359 ,v_pay_row.ae_header_id
3360 ,pn_ledger_id
3361 ,pn_legal_entity_id
3362 ,'E')*/ Get_Payment_Liability_Amount(v_pay_row.invoice_payment_id,
3363 'E'),
3364 pv_description => v_pay_row.Description,
3365 pv_due_date => NULL /*Get_Due_Date(v_inv_row.Invoice_Id --Updated for fixing bug 10121399
3366 ,v_pay_row.Invoice_Payment_Id)*/,
3367 pv_applied_journal_number => Get_Journal_Number(v_inv_row.je_header_id,
3368 pn_legal_entity_id) --Add a parameter:legal entity, by Jianchao Chi for V2 Upgrade 06-Jan-2011
3369 ,
3370 pv_applied_date => --v_inv_row.Journal_Date
3371 v_pay_row.Journal_Date --Updated for fixing bug 9962326
3372 ,
3373 pv_document_type_number => v_pay_row.Document_Type_Number,
3374 pv_transaction_type_number => v_pay_row.Transaction_Type_Number,
3375 pv_transaction_number => v_pay_row.Transaction_Number,
3376 pv_invoice_number => v_pay_row.Invoice_Number,
3377 pv_contract_number => v_inv_row.Contract_Number,
3378 pv_project_number => v_pay_row.Project_Number,
3379 pv_settlement_method_number => v_pay_row.Settlement_Method_Number,
3380 pv_payment_date => v_pay_row.Payment_Date,
3381 pv_clear_flag => Get_Liability_Clear_Flag(v_inv_row.Invoice_Id,
3382 v_pay_row.period_name,
3383 pn_ledger_id,
3384 pn_legal_entity_id),
3385 pv_remittance_bill_number => Get_Remittance_Bill_Number(pn_coa_id,
3386 v_pay_row.Check_Id) --Updated for fixing bug# 9793920
3387 );
3388 End if;
3389 end if;
3390 END LOOP; --Step5: pay_cur
3391
3392 IF lv_invoice_exported_flag = 'N' THEN
3393 lv_invoice_exported_flag := 'Y'; --indicate invioce information has been exported with its payment
3394 --Step6: Fetching information about prepayment application
3395 FOR v_prepay_inv_id_row IN prepay_inv_id_cur(v_inv_id_row.Invoice_Id,
3396 v_gl_sla_row.ae_header_id) LOOP
3397 --fnd_file.PUT_LINE(fnd_file.log,'--Step6: v_prepay_inv_row.Prepay_Invoice_Id='||v_prepay_inv_id_row.Prepay_Invoice_Id);
3398
3399 /*--For prepaid account number
3400 IF lv_prepaid_account_num IS NULL
3401 THEN
3402 lv_prepaid_account_num := Get_Prepaid_Account_Number(v_prepay_inv_id_row.Prepay_Invoice_Id
3403 ,pn_ledger_id);
3404 END IF;*/
3405
3406 --Step6.1: Add prepaid line for prepayment
3407 ln_prepaid_je_header_id := NULL;
3408 ln_prepaid_journal_date := NULL;
3409
3410 FOR v_prepaid_line_row IN prepay_cur(v_prepay_inv_id_row.Prepay_Invoice_Id) LOOP
3411 --fnd_file.PUT_LINE(fnd_file.log,'--Step6.1: v_prepay_inv_row.Prepay_Invoice_Id='||v_prepay_inv_id_row.Prepay_Invoice_Id||',v_gl_sla_row.je_header_id='||v_gl_sla_row.je_header_id);
3412 ln_prepaid_je_header_id := v_prepaid_line_row.je_header_id;
3413 ln_prepaid_journal_date := v_prepaid_line_row.journal_date;
3414
3415 FOR v_prepaid_account_num_row IN prepaid_account_num_cur(v_prepaid_line_row.Ae_Header_Id) LOOP
3416 --For prepaid account number
3417 lv_prepaid_account_num := v_prepaid_account_num_row.prepaid_account_num;
3418 Add_XML_Node(pv_supplier_number => Get_Supplier_Number(v_gl_sla_row.Supplier_Number,
3419 v_inv_id_row.Invoice_Id),
3420 pv_account_number => lv_prepaid_account_num,
3421 pv_journal_created_date => v_prepaid_line_row.Journal_Created_Date,
3422 pv_journal_date => v_prepaid_line_row.Journal_Date,
3423 pv_accounting_year => v_prepaid_line_row.Accounting_Year,
3424 pv_accounting_period_number => Get_Accounting_Period_Number(pn_ledger_id,
3425 v_prepaid_line_row.Invoice_Id,
3426 NULL),
3427 pv_journal_category_number => v_prepaid_line_row.Journal_Category_Number,
3428 pv_journal_number => Get_Journal_Number(v_prepaid_line_row.je_header_id,
3429 pn_legal_entity_id) --Add a parameter:legal entity, by Jianchao Chi for V2 Upgrade 06-Jan-2011
3430 ,
3431 pv_functional_currency => v_gl_sla_row.Functional_Currency,
3432 pn_exchange_rate => v_prepaid_line_row.Exchange_Rate,
3433 pv_balance_side => Get_Balance_Side(pn_coa_id,
3434 lv_prepaid_account_num),
3435 pn_functional_balance => Get_Prepaid_Balance(v_gl_sla_row.Supplier_Id,
3436 pn_ledger_id,
3437 pn_legal_entity_id,
3438 lv_prepaid_account_num,
3439 v_prepaid_line_row.currency_code,
3440 v_period_name_row.period_name,
3441 'F'),
3442 pn_entered_balance => Get_Prepaid_Balance(v_gl_sla_row.Supplier_Id,
3443 pn_ledger_id,
3444 pn_legal_entity_id,
3445 lv_prepaid_account_num,
3446 v_prepaid_line_row.currency_code,
3447 v_period_name_row.period_name,
3448 'E'),
3449 pn_functional_amount => Get_Prepaid_Amount(v_prepaid_line_row.ae_header_id,
3450 pn_ledger_id,
3451 pn_legal_entity_id,
3452 lv_prepaid_account_num,
3453 'F'),
3454 pv_entered_currency => v_prepaid_line_row.Entered_Currency,
3455 pn_entered_amount => Get_Prepaid_Amount(v_prepaid_line_row.ae_header_id,
3456 pn_ledger_id,
3457 pn_legal_entity_id,
3458 lv_prepaid_account_num,
3459 'E'),
3460 pv_description => v_prepaid_line_row.Description,
3461 pv_due_date => Get_Due_Date(v_prepaid_line_row.Invoice_Id,
3462 NULL),
3463 pv_applied_journal_number => Get_Journal_Number(v_gl_sla_row.je_header_id,
3464 pn_legal_entity_id) --Add a parameter:legal entity, by Jianchao Chi for V2 Upgrade 06-Jan-2011
3465 ,
3466 pv_applied_date => NULL --v_gl_sla_row.Journal_Date --Update for fixing bug 10121399
3467 ,
3468 pv_document_type_number => v_prepaid_line_row.Document_Type_Number,
3469 pv_transaction_type_number => v_prepaid_line_row.Transaction_Type_Number,
3470 pv_transaction_number => v_prepaid_line_row.Transaction_Number,
3471 pv_invoice_number => v_prepaid_line_row.Invoice_Number,
3472 pv_contract_number => v_prepaid_line_row.Contract_Number,
3473 pv_project_number => v_prepaid_line_row.Project_Number,
3474 pv_settlement_method_number => v_prepaid_line_row.Settlement_Method_Number,
3475 pv_payment_date => v_prepaid_line_row.Payment_Date,
3476 pv_clear_flag => Get_Prepaid_Clear_Flag(v_prepay_inv_id_row.Prepay_Invoice_Id,
3477 v_period_name_row.period_name,
3478 lv_prepaid_account_num,
3479 pn_ledger_id,
3480 pn_legal_entity_id),
3481 pv_remittance_bill_number => '' /*Get_Remittance_Bill_Number(pn_coa_id
3482 ,v_prepaid_line_row.Invoice_Id)*/ --Updated for fixing bug# 9793920
3483 );
3484 END LOOP; --prepaid_account_num_cur
3485 END LOOP; --prepay_cur: add prepaid line for prepayment
3486
3487 --Step6.2: Add invoice line for invoice
3488 FOR v_inv_line_row IN prepay_cur(v_inv_id_row.Invoice_Id) LOOP
3489 --fnd_file.PUT_LINE(fnd_file.log,'--Step6.2:v_inv_id_row.Invoice_Id='||v_inv_id_row.Invoice_Id);
3490 Add_XML_Node(pv_supplier_number => Get_Supplier_Number(v_gl_sla_row.Supplier_Number,
3491 v_inv_id_row.Invoice_Id),
3492 pv_account_number => v_gl_sla_row.account_number,
3493 pv_journal_created_date => v_inv_line_row.Journal_Created_Date,
3494 pv_journal_date => v_inv_line_row.Journal_Date,
3495 pv_accounting_year => v_inv_line_row.Accounting_Year,
3496 pv_accounting_period_number => Get_Accounting_Period_Number(pn_ledger_id,
3497 v_inv_line_row.Invoice_Id,
3498 NULL),
3499 pv_journal_category_number => v_inv_line_row.Journal_Category_Number,
3500 pv_journal_number => Get_Journal_Number(v_inv_line_row.je_header_id,
3501 pn_legal_entity_id) --Add a parameter:legal entity, by Jianchao Chi for V2 Upgrade 06-Jan-2011
3502 ,
3503 pv_functional_currency => v_gl_sla_row.Functional_Currency,
3504 pn_exchange_rate => v_inv_line_row.Exchange_Rate,
3505 pv_balance_side => Get_Balance_Side(pn_coa_id,
3506 v_gl_sla_row.Account_Number),
3507 pn_functional_balance => Get_Liability_Balance(v_gl_sla_row.Supplier_Id,
3508 pn_ledger_id,
3509 pn_legal_entity_id,
3510 v_gl_sla_row.Account_Number,
3511 v_inv_line_row.currency_code,
3512 v_period_name_row.period_name,
3513 'F'),
3514 pn_entered_balance => Get_Liability_Balance(v_gl_sla_row.Supplier_Id,
3515 pn_ledger_id,
3516 pn_legal_entity_id,
3517 v_gl_sla_row.Account_Number,
3518 v_inv_line_row.currency_code,
3519 v_period_name_row.period_name,
3520 'E'),
3521 pn_functional_amount => Get_Liability_Amount(NULL,
3522 v_inv_line_row.ae_header_id,
3523 pn_ledger_id,
3524 pn_legal_entity_id,
3525 'F'),
3526 pv_entered_currency => v_inv_line_row.Entered_Currency,
3527 pn_entered_amount => Get_Liability_Amount(NULL,
3528 v_inv_line_row.ae_header_id,
3529 pn_ledger_id,
3530 pn_legal_entity_id,
3531 'E'),
3532 pv_description => v_inv_line_row.Description,
3533 pv_due_date => Get_Due_Date(v_inv_line_row.Invoice_Id,
3534 NULL),
3535 pv_applied_journal_number => Get_Journal_Number(v_gl_sla_row.je_header_id,
3536 pn_legal_entity_id) --Add a parameter:legal entity, by Jianchao Chi for V2 Upgrade 06-Jan-2011
3537 ,
3538 pv_applied_date => NULL --v_gl_sla_row.Journal_Date --Update for fixing bug 10121399
3539 ,
3540 pv_document_type_number => v_inv_line_row.Document_Type_Number,
3541 pv_transaction_type_number => v_inv_line_row.Transaction_Type_Number,
3542 pv_transaction_number => v_inv_line_row.Transaction_Number,
3543 pv_invoice_number => v_inv_line_row.Invoice_Number,
3544 pv_contract_number => v_inv_line_row.Contract_Number,
3545 pv_project_number => v_inv_line_row.Project_Number,
3546 pv_settlement_method_number => v_inv_line_row.Settlement_Method_Number,
3547 pv_payment_date => v_inv_line_row.Payment_Date,
3548 pv_clear_flag => Get_Liability_Clear_Flag(v_inv_Id_row.Invoice_Id,
3549 v_period_name_row.period_name,
3550 pn_ledger_id,
3551 pn_legal_entity_id),
3552 pv_remittance_bill_number => '' /*Get_Remittance_Bill_Number(pn_coa_id
3553 ,v_inv_line_row.Invoice_Id)*/ --Updated for fixing bug# 9793920
3554 );
3555 --END LOOP; --prepay_cur: add invoice line for invoice
3556
3557 --step6.3: Add prepaid line for prepayment applicaction
3558 FOR v_prepaid_account_num_row IN prepaid_account_num_cur(v_gl_sla_row.Ae_Header_Id) LOOP
3559 lv_prepaid_account_num := v_prepaid_account_num_row.prepaid_account_num;
3560 Add_XML_Node(pv_supplier_number => Get_Supplier_Number(v_gl_sla_row.Supplier_Number,
3561 v_inv_id_row.Invoice_Id),
3562 pv_account_number => lv_prepaid_account_num,
3563 pv_journal_created_date => v_gl_sla_row.Journal_Created_Date,
3564 pv_journal_date => v_gl_sla_row.Journal_Date,
3565 pv_accounting_year => v_gl_sla_row.Accounting_Year,
3566 pv_accounting_period_number => TO_CHAR(v_gl_sla_row.Accounting_Period_Number),
3567 pv_journal_category_number => v_gl_sla_row.Journal_Category_Number,
3568 pv_journal_number => Get_Journal_Number(v_gl_sla_row.je_header_id,
3569 pn_legal_entity_id) --Add a parameter:legal entity, by Jianchao Chi for V2 Upgrade 06-Jan-2011
3570 ,
3571 pv_functional_currency => v_gl_sla_row.Functional_Currency,
3572 pn_exchange_rate => v_inv_row.Exchange_Rate,
3573 pv_balance_side => Get_Balance_Side(pn_coa_id,
3574 lv_prepaid_account_num),
3575 pn_functional_balance => Get_Prepaid_Balance(v_gl_sla_row.Supplier_Id,
3576 pn_ledger_id,
3577 pn_legal_entity_id,
3578 lv_prepaid_account_num,
3579 v_inv_row.currency_code,
3580 v_period_name_row.period_name,
3581 'F'),
3582 pn_entered_balance => Get_Prepaid_Balance(v_gl_sla_row.Supplier_Id,
3583 pn_ledger_id,
3584 pn_legal_entity_id,
3585 lv_prepaid_account_num,
3586 v_inv_row.currency_code,
3587 v_period_name_row.period_name,
3588 'E'),
3589 pn_functional_amount => Get_Prepaid_Amount(v_gl_sla_row.ae_header_id,
3590 pn_ledger_id,
3591 pn_legal_entity_id,
3592 lv_prepaid_account_num,
3593 'F'),
3594 pv_entered_currency => v_inv_row.Entered_Currency,
3595 pn_entered_amount => Get_Prepaid_Amount(v_gl_sla_row.ae_header_id,
3596 pn_ledger_id,
3597 pn_legal_entity_id,
3598 lv_prepaid_account_num,
3599 'E'),
3600 pv_description => v_inv_row.Description,
3601 pv_due_date => NULL /*Get_Due_Date(v_inv_row.Invoice_Id --Updated for fixing bug 10121399
3602 ,NULL)*/,
3603 pv_applied_journal_number => Get_Journal_Number(ln_prepaid_je_header_id,
3604 pn_legal_entity_id) --Add a parameter:legal entity, by Jianchao Chi for V2 Upgrade 06-Jan-2011
3605 ,
3606 pv_applied_date => --ln_prepaid_journal_date
3607 v_gl_sla_row.Journal_Date --Update for fixing bug 9962326
3608 ,
3609 pv_document_type_number => '',
3610 pv_transaction_type_number => '',
3611 pv_transaction_number => '',
3612 pv_invoice_number => v_inv_row.Invoice_Number,
3613 pv_contract_number => v_inv_row.Contract_Number,
3614 pv_project_number => v_inv_row.Project_Number,
3615 pv_settlement_method_number => v_inv_row.Settlement_Method_Number,
3616 pv_payment_date => v_inv_row.Payment_Date,
3617 pv_clear_flag => Get_Prepaid_Clear_Flag(v_prepay_inv_id_row.Prepay_Invoice_Id,
3618 v_period_name_row.period_name,
3619 lv_prepaid_account_num,
3620 pn_ledger_id,
3621 pn_legal_entity_id),
3622 pv_remittance_bill_number => '' /*Get_Remittance_Bill_Number(pn_coa_id
3623 ,v_inv_row.Invoice_Id)*/ --Updated for fixing bug# 9793920
3624 );
3625 END LOOP; --prepaid_account_num_cur
3626
3627 --step6.4: Add invoice line for prepayment application
3628 Add_XML_Node(pv_supplier_number => Get_Supplier_Number(v_gl_sla_row.Supplier_Number,
3629 v_inv_id_row.Invoice_Id),
3630 pv_account_number => v_gl_sla_row.Account_Number,
3631 pv_journal_created_date => v_gl_sla_row.Journal_Created_Date,
3632 pv_journal_date => v_gl_sla_row.Journal_Date,
3633 pv_accounting_year => v_gl_sla_row.Accounting_Year,
3634 pv_accounting_period_number => TO_CHAR(v_gl_sla_row.Accounting_Period_Number),
3635 pv_journal_category_number => v_gl_sla_row.Journal_Category_Number,
3636 pv_journal_number => Get_Journal_Number(v_gl_sla_row.je_header_id,
3637 pn_legal_entity_id) --Add a parameter:legal entity, by Jianchao Chi for V2 Upgrade 06-Jan-2011
3638 ,
3639 pv_functional_currency => v_gl_sla_row.Functional_Currency,
3640 pn_exchange_rate => v_inv_row.Exchange_Rate,
3641 pv_balance_side => Get_Balance_Side(pn_coa_id,
3642 v_gl_sla_row.Account_Number),
3643 pn_functional_balance => Get_Liability_Balance(v_gl_sla_row.Supplier_Id,
3644 pn_ledger_id,
3645 pn_legal_entity_id,
3646 v_gl_sla_row.Account_Number,
3647 v_inv_row.currency_code,
3648 v_period_name_row.period_name,
3649 'F'),
3650 pn_entered_balance => Get_Liability_Balance(v_gl_sla_row.Supplier_Id,
3651 pn_ledger_id,
3652 pn_legal_entity_id,
3653 v_gl_sla_row.Account_Number,
3654 v_inv_row.currency_code,
3655 v_period_name_row.period_name,
3656 'E'),
3657 pn_functional_amount => Get_Liability_Amount(NULL,
3658 v_gl_sla_row.ae_header_id,
3659 pn_ledger_id,
3660 pn_legal_entity_id,
3661 'F'),
3662 pv_entered_currency => v_inv_row.Entered_Currency,
3663 pn_entered_amount => Get_Liability_Amount(NULL,
3664 v_gl_sla_row.ae_header_id,
3665 pn_ledger_id,
3666 pn_legal_entity_id,
3667 'E'),
3668 pv_description => v_inv_row.Description,
3669 pv_due_date => NULL /*Get_Due_Date(v_inv_row.Invoice_Id --Updated for fixing bug 10121399
3670 ,NULL)*/,
3671 pv_applied_journal_number => Get_Journal_Number(v_inv_line_row.je_header_id,
3672 pn_legal_entity_id) --Add a parameter:legal entity, by Jianchao Chi for V2 Upgrade 06-Jan-2011
3673 ,
3674 pv_applied_date => --v_inv_line_row.journal_date
3675 v_gl_sla_row.Journal_Date --Update for fixing bug 9962326
3676 ,
3677 pv_document_type_number => '',
3678 pv_transaction_type_number => '',
3679 pv_transaction_number => '',
3680 pv_invoice_number => v_inv_row.Invoice_Number,
3681 pv_contract_number => v_inv_row.Contract_Number,
3682 pv_project_number => v_inv_row.Project_Number,
3683 pv_settlement_method_number => v_inv_row.Settlement_Method_Number,
3684 pv_payment_date => v_inv_row.Payment_Date,
3685 pv_clear_flag => Get_Liability_Clear_Flag(v_inv_Id_row.Invoice_Id,
3686 v_period_name_row.period_name,
3687 pn_ledger_id,
3688 pn_legal_entity_id),
3689 pv_remittance_bill_number => '' /*Get_Remittance_Bill_Number(pn_coa_id
3690 ,v_inv_row.Invoice_Id)*/ --Updated for fixing bug# 9793920
3691 );
3692 END LOOP; --prepay_cur: add invoice line for invoice
3693 END LOOP; --prepay_inv_id_cur
3694
3695 --If 'Y', accounting period of prepayment applicaction is the same with its invoice,
3696 --in this case, invoice line for invoice would not be exported again,
3697 --Else they are in different accounting periods
3698 SELECT DECODE(COUNT(*), 0, 'N', 'Y')
3699 INTO lv_inv_prepay_same_period_flag
3700 FROM Ap_Prepay_History_All Aph,
3701 Gl_Ledgers Gl,
3702 Gl_Periods Gp
3703 WHERE Invoice_Id = v_inv_id_row.Invoice_Id
3704 AND Accounting_Date BETWEEN Gp.Start_Date AND Gp.End_Date
3705 AND Gl.period_set_name = Gp.period_set_name
3706 AND Gl.accounted_period_type = Gp.period_type
3707 AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
3708 AND Gp.period_name = v_period_name_row.period_name;
3709
3710 IF lv_inv_prepay_same_period_flag = 'N' THEN
3711 --Step4.1: Add Prepaid line
3712 IF v_inv_row.Transaction_Type_Number = 'PREPAYMENT' THEN
3713 --For prepaid account number
3714 /*IF lv_prepaid_account_num IS NULL
3715 THEN
3716 lv_prepaid_account_num := Get_Prepaid_Account_Number(v_inv_id_row.Invoice_Id
3717 ,pn_ledger_id);
3718 END IF;*/
3719 FOR v_prepaid_account_num_row IN prepaid_account_num_cur(v_gl_sla_row.Ae_Header_Id) LOOP
3720 lv_prepaid_account_num := v_prepaid_account_num_row.prepaid_account_num;
3721 Add_XML_Node(pv_supplier_number => Get_Supplier_Number(v_gl_sla_row.Supplier_Number,
3722 v_inv_id_row.Invoice_Id),
3723 pv_account_number => lv_prepaid_account_num,
3724 pv_journal_created_date => v_gl_sla_row.Journal_Created_Date,
3725 pv_journal_date => v_gl_sla_row.Journal_Date,
3726 pv_accounting_year => v_gl_sla_row.Accounting_Year,
3727 pv_accounting_period_number => TO_CHAR(v_gl_sla_row.Accounting_Period_Number),
3728 pv_journal_category_number => v_gl_sla_row.Journal_Category_Number,
3729 pv_journal_number => Get_Journal_Number(v_gl_sla_row.je_header_id,
3730 pn_legal_entity_id) --Add a parameter:legal entity, by Jianchao Chi for V2 Upgrade 06-Jan-2011
3731 ,
3732 pv_functional_currency => v_gl_sla_row.Functional_Currency,
3733 pn_exchange_rate => v_inv_row.Exchange_Rate,
3734 pv_balance_side => Get_Balance_Side(pn_coa_id,
3735 lv_prepaid_account_num),
3736 pn_functional_balance => Get_Prepaid_Balance(v_gl_sla_row.Supplier_Id,
3737 pn_ledger_id,
3738 pn_legal_entity_id,
3739 lv_prepaid_account_num,
3740 v_inv_row.currency_code,
3741 v_period_name_row.period_name,
3742 'F'),
3743 pn_entered_balance => Get_Prepaid_Balance(v_gl_sla_row.Supplier_Id,
3744 pn_ledger_id,
3745 pn_legal_entity_id,
3746 lv_prepaid_account_num,
3747 v_inv_row.currency_code,
3748 v_period_name_row.period_name,
3749 'E'),
3750 pn_functional_amount => Get_Prepaid_Amount(v_gl_sla_row.ae_header_id,
3751 pn_ledger_id,
3752 pn_legal_entity_id,
3753 lv_prepaid_account_num,
3754 'F'),
3755 pv_entered_currency => v_inv_row.Entered_Currency,
3756 pn_entered_amount => Get_Prepaid_Amount(v_gl_sla_row.ae_header_id,
3757 pn_ledger_id,
3758 pn_legal_entity_id,
3759 lv_prepaid_account_num,
3760 'E'),
3761 pv_description => v_inv_row.Description,
3762 pv_due_date => Get_Due_Date(v_inv_row.Invoice_Id,
3763 NULL),
3764 pv_applied_journal_number => '',
3765 pv_applied_date => '',
3766 pv_document_type_number => v_inv_row.Document_Type_Number,
3767 pv_transaction_type_number => v_inv_row.Transaction_Type_Number,
3768 pv_transaction_number => v_inv_row.Transaction_Number,
3769 pv_invoice_number => v_inv_row.Invoice_Number,
3770 pv_contract_number => v_inv_row.Contract_Number,
3771 pv_project_number => v_inv_row.Project_Number,
3772 pv_settlement_method_number => v_inv_row.Settlement_Method_Number,
3773 pv_payment_date => v_inv_row.Payment_Date,
3774 pv_clear_flag => Get_Prepaid_Clear_Flag(v_inv_row.Invoice_Id,
3775 v_period_name_row.period_name,
3776 lv_prepaid_account_num,
3777 pn_ledger_id,
3778 pn_legal_entity_id),
3779 pv_remittance_bill_number => '' /*Get_Remittance_Bill_Number(pn_coa_id
3780 ,v_inv_row.Invoice_Id)*/ --Updated for fixing bug# 9793920
3781 );
3782 END LOOP; --prepaid_account_num_cur
3783 END IF;
3784 --Step4.2: Add Invoice line
3785 Add_XML_Node(pv_supplier_number => Get_Supplier_Number(v_gl_sla_row.Supplier_Number,
3786 v_inv_id_row.Invoice_Id),
3787 pv_account_number => v_gl_sla_row.Account_Number,
3788 pv_journal_created_date => v_gl_sla_row.Journal_Created_Date,
3789 pv_journal_date => v_gl_sla_row.Journal_Date,
3790 pv_accounting_year => v_gl_sla_row.Accounting_Year,
3791 pv_accounting_period_number => TO_CHAR(v_gl_sla_row.Accounting_Period_Number),
3792 pv_journal_category_number => v_gl_sla_row.Journal_Category_Number,
3793 pv_journal_number => Get_Journal_Number(v_gl_sla_row.je_header_id,
3794 pn_legal_entity_id) --Add a parameter:legal entity, by Jianchao Chi for V2 Upgrade 06-Jan-2011
3795 ,
3796 pv_functional_currency => v_gl_sla_row.Functional_Currency,
3797 pn_exchange_rate => v_inv_row.Exchange_Rate,
3798 pv_balance_side => Get_Balance_Side(pn_coa_id,
3799 v_gl_sla_row.Account_Number),
3800 pn_functional_balance => Get_Liability_Balance(v_gl_sla_row.Supplier_Id,
3801 pn_ledger_id,
3802 pn_legal_entity_id,
3803 v_gl_sla_row.Account_Number,
3804 v_inv_row.currency_code,
3805 v_period_name_row.period_name,
3806 'F'),
3807 pn_entered_balance => Get_Liability_Balance(v_gl_sla_row.Supplier_Id,
3808 pn_ledger_id,
3809 pn_legal_entity_id,
3810 v_gl_sla_row.Account_Number,
3811 v_inv_row.currency_code,
3812 v_period_name_row.period_name,
3813 'E'),
3814 pn_functional_amount => Get_Liability_Amount(NULL,
3815 v_gl_sla_row.ae_header_id,
3816 pn_ledger_id,
3817 pn_legal_entity_id,
3818 'F'),
3819 pv_entered_currency => v_inv_row.Entered_Currency,
3820 pn_entered_amount => Get_Liability_Amount(NULL,
3821 v_gl_sla_row.ae_header_id,
3822 pn_ledger_id,
3823 pn_legal_entity_id,
3824 'E'),
3825 pv_description => v_inv_row.Description,
3826 pv_due_date => Get_Due_Date(v_inv_row.Invoice_Id,
3827 NULL),
3828 pv_applied_journal_number => '',
3829 pv_applied_date => '',
3830 pv_document_type_number => v_inv_row.Document_Type_Number,
3831 pv_transaction_type_number => v_inv_row.Transaction_Type_Number,
3832 pv_transaction_number => v_inv_row.Transaction_Number,
3833 pv_invoice_number => v_inv_row.Invoice_Number,
3834 pv_contract_number => v_inv_row.Contract_Number,
3835 pv_project_number => v_inv_row.Project_Number,
3836 pv_settlement_method_number => v_inv_row.Settlement_Method_Number,
3837 pv_payment_date => v_inv_row.Payment_Date,
3838 pv_clear_flag => Get_Liability_Clear_Flag(v_inv_row.Invoice_Id,
3839 v_period_name_row.period_name,
3840 pn_ledger_id,
3841 pn_legal_entity_id),
3842 pv_remittance_bill_number => '' /*Get_Remittance_Bill_Number(pn_coa_id
3843 ,v_inv_row.Invoice_Id)*/ --Updated for fixing bug# 9793920
3844 );
3845 END IF; --lv_inv_prepay_period_flag
3846 END IF; --lv_invoice_exported_flag
3847 EXIT;
3848 END LOOP; --Step4: inv_cur
3849 END LOOP; --Step3: period_name_cur
3850 EXIT;
3851 END LOOP; --Step2: inv_id_cur
3852 FND_FILE.PUT_LINE(FND_FILE.LOG, '**************End**********************');
3853 END LOOP; --Step1: gl_sla_cur
3854
3855 -- if the row count is 0
3856 -- no need to handle if minOccurs=0 specified in xml schema
3857 IF ln_row_count = 0 AND lv_beginning_bal_exported_flag = 'N' --Update for fixing bug 10041443
3858 THEN
3859 Ja_Cn_Utility.Print_No_Data_Found_For_Log('PAYABLE_DETAIL_REPORT',
3860 Ja_Cn_Utility.GV_MODULE_APAR);
3861 Add_XML_Node(pv_supplier_number => NULL,
3862 pv_account_number => NULL,
3863 pv_journal_created_date => NULL,
3864 pv_journal_date => NULL,
3865 pv_accounting_year => NULL,
3866 pv_accounting_period_number => NULL,
3867 pv_journal_category_number => NULL,
3868 pv_journal_number => NULL,
3869 pv_functional_currency => NULL,
3870 pn_exchange_rate => NULL,
3871 pv_balance_side => NULL,
3872 pn_functional_balance => NULL,
3873 pn_entered_balance => NULL,
3874 pn_functional_amount => NULL,
3875 pv_entered_currency => NULL,
3876 pn_entered_amount => NULL,
3877 pv_description => NULL,
3878 pv_due_date => NULL,
3879 pv_applied_journal_number => NULL,
3880 pv_applied_date => NULL,
3881 pv_document_type_number => NULL,
3882 pv_transaction_type_number => NULL,
3883 pv_transaction_number => NULL,
3884 pv_invoice_number => NULL,
3885 pv_contract_number => NULL,
3886 pv_project_number => NULL,
3887 pv_settlement_method_number => NULL,
3888 pv_payment_date => NULL,
3889 pv_clear_flag => NULL,
3890 pv_remittance_bill_number => NULL);
3891 END IF; --(ln_row_count = 0)
3892
3893 --logging for debug
3894 IF (ln_proc_level >= ln_dbg_level) THEN
3895 FND_LOG.STRING(ln_proc_level,
3896 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
3897 'Exit procedure');
3898 END IF; -- (ln_proc_level>=ln_dbg_level)
3899
3900 EXCEPTION
3901 WHEN OTHERS THEN
3902 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3903 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3904 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
3905 '.Other_Exception ',
3906 SQLCODE || SQLERRM);
3907 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3908 FND_FILE.put_line(FND_FILE.log,
3909 lv_procedure_name || SQLCODE || SQLERRM);
3910 RAISE;
3911 END Add_Payables_Balance_Detail;
3912
3913 END JA_CN_AP_PBD_EXPORT_PKG;
3914
3915
3916
3917
3918
3919
3920
3921
3922
3923