[Home] [Help]
PACKAGE BODY: APPS.JA_JAINCTRB_XMLP_PKG
Source
1 PACKAGE BODY JA_JAINCTRB_XMLP_PKG AS
2 /* $Header: JAINCTRBB.pls 120.1 2007/12/25 16:15:35 dwkrishn noship $ */
3 FUNCTION CF_VENDOR_TYPEFORMULA(VENDOR_ID IN NUMBER) RETURN VARCHAR2 IS
4 V_VENDOR_TYPE_LOOKUP_CODE PO_VENDORS.VENDOR_TYPE_LOOKUP_CODE%TYPE;
5 BEGIN
6 SELECT
7 VENDOR_TYPE_LOOKUP_CODE
8 INTO V_VENDOR_TYPE_LOOKUP_CODE
9 FROM
10 PO_VENDORS
11 WHERE VENDOR_ID = cf_vendor_typeformula.VENDOR_ID;
12 RETURN (V_VENDOR_TYPE_LOOKUP_CODE);
13 END CF_VENDOR_TYPEFORMULA;
14
15 FUNCTION CF_VENDOR_NAMEFORMULA(VENDOR_ID IN NUMBER) RETURN VARCHAR2 IS
16 V_VENDOR_NAME VARCHAR2(80);
17 BEGIN
18 SELECT
19 VENDOR_NAME
20 INTO V_VENDOR_NAME
21 FROM
22 PO_VENDORS
23 WHERE VENDOR_ID = cf_vendor_nameformula.VENDOR_ID;
24 RETURN (V_VENDOR_NAME);
25 END CF_VENDOR_NAMEFORMULA;
26
27 FUNCTION CF_VENDOR_CODEFORMULA(VENDOR_ID IN NUMBER) RETURN VARCHAR2 IS
28 V_VENDOR_CODE VARCHAR2(30);
29 BEGIN
30 SELECT
31 SEGMENT1
32 INTO V_VENDOR_CODE
33 FROM
34 PO_VENDORS
35 WHERE VENDOR_ID = cf_vendor_codeformula.VENDOR_ID;
36 RETURN (V_VENDOR_CODE);
37 END CF_VENDOR_CODEFORMULA;
38
39 FUNCTION CF_ADDRFORMULA(ORG_ID IN NUMBER) RETURN VARCHAR2 IS
40 V_LEGAL_ENTITY NUMBER;
41 V_NAME VARCHAR2(60);
42 BEGIN
43 SELECT
44 NAME
45 INTO V_NAME
46 FROM
47 HR_ORGANIZATION_UNITS
48 WHERE NVL(ORGANIZATION_ID
49 ,0) = NVL(ORG_ID
50 ,0);
51 RETURN (V_NAME);
52 RETURN NULL;
53 EXCEPTION
54 WHEN OTHERS THEN
55 /*SRW.MESSAGE(2
56 ,SQLERRM)*/NULL;
57 RETURN (NULL);
58 END CF_ADDRFORMULA;
59
60 FUNCTION CF_ENTERED_AMOUNTFORMULA(ACCTS IN NUMBER
61 ,VENDOR_ID IN NUMBER
62 ,ORG_ID IN NUMBER
63 ,INVOICE_CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
64 V_ENTERED_AMOUNT NUMBER;
65 V_AMOUNT_DR1 NUMBER := 0;
66 V_AMOUNT_DR2 NUMBER := 0;
67 V_AMOUNT_CR1 NUMBER := 0;
68 V_AMOUNT_CR2 NUMBER := 0;
69 V_AMOUNT NUMBER := 0;
70 BEGIN
71 SELECT
72 SUM(B.AMOUNT)
73 INTO V_AMOUNT_CR1
74 FROM
75 AP_INVOICES_ALL A,
76 AP_INVOICE_DISTRIBUTIONS_ALL B,
77 PO_VENDORS POV,
78 PO_VENDOR_SITES_ALL POVS
79 WHERE A.INVOICE_ID = B.INVOICE_ID
80 AND POV.VENDOR_ID = A.VENDOR_ID
81 AND POV.VENDOR_ID = POVS.VENDOR_ID
82 AND A.VENDOR_ID = NVL(P_VENDOR_ID
83 ,A.VENDOR_ID)
84 AND A.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
85 AND A.ACCTS_PAY_CODE_COMBINATION_ID = NVL(ACCTS
86 ,A.ACCTS_PAY_CODE_COMBINATION_ID)
87 AND A.VENDOR_ID = cf_entered_amountformula.VENDOR_ID
88 AND ( A.ORG_ID = cf_entered_amountformula.ORG_ID
89 OR A.ORG_ID IS NULL )
90 AND A.INVOICE_CURRENCY_CODE = CF_ENTERED_AMOUNTFORMULA.INVOICE_CURRENCY_CODE
91 AND B.MATCH_STATUS_FLAG = 'A'
92 AND A.INVOICE_TYPE_LOOKUP_CODE NOT IN ( LV_PREPAYMENT_LOOKUP , LV_CREDIT_LOOKUP , LV_DEBIT_LOOKUP )
93 AND B.LINE_TYPE_LOOKUP_CODE <> LV_PREPAY_LINE_TYPE
94 AND TRUNC(B.ACCOUNTING_DATE) <= TRUNC(P_AS_OF_DATE);
95 SELECT
96 SUM(B.AMOUNT)
97 INTO V_AMOUNT_CR2
98 FROM
99 AP_INVOICES_ALL A,
100 AP_INVOICE_DISTRIBUTIONS_ALL B,
101 PO_VENDORS POV,
102 PO_VENDOR_SITES_ALL POVS
103 WHERE A.INVOICE_ID = B.INVOICE_ID
104 AND POV.VENDOR_ID = A.VENDOR_ID
105 AND POV.VENDOR_ID = POVS.VENDOR_ID
106 AND A.VENDOR_ID = NVL(P_VENDOR_ID
107 ,A.VENDOR_ID)
108 AND A.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
109 AND A.ACCTS_PAY_CODE_COMBINATION_ID = NVL(ACCTS
110 ,A.ACCTS_PAY_CODE_COMBINATION_ID)
111 AND A.VENDOR_ID = cf_entered_amountformula.VENDOR_ID
112 AND ( A.ORG_ID = cf_entered_amountformula.ORG_ID
113 OR A.ORG_ID IS NULL )
114 AND A.INVOICE_CURRENCY_CODE = CF_ENTERED_AMOUNTFORMULA.INVOICE_CURRENCY_CODE
115 AND A.INVOICE_TYPE_LOOKUP_CODE IN ( LV_CREDIT_LOOKUP )
116 AND B.MATCH_STATUS_FLAG = 'A'
117 AND TRUNC(B.ACCOUNTING_DATE) <= TRUNC(P_AS_OF_DATE);
118 SELECT
119 SUM(NVL(B.AMOUNT
120 ,0))
121 INTO V_AMOUNT_DR1
122 FROM
123 AP_INVOICES_ALL A,
124 AP_INVOICE_PAYMENTS_ALL B,
125 AP_CHECKS_ALL C,
126 PO_VENDORS POV,
127 PO_VENDOR_SITES_ALL POVS
128 WHERE A.INVOICE_ID = B.INVOICE_ID
129 AND POV.VENDOR_ID = A.VENDOR_ID
130 AND B.CHECK_ID = C.CHECK_ID
131 AND POV.VENDOR_ID = POVS.VENDOR_ID
132 AND A.VENDOR_ID = NVL(P_VENDOR_ID
133 ,A.VENDOR_ID)
134 AND A.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
135 AND A.ACCTS_PAY_CODE_COMBINATION_ID = NVL(ACCTS
136 ,A.ACCTS_PAY_CODE_COMBINATION_ID)
137 AND A.VENDOR_ID = cf_entered_amountformula.VENDOR_ID
138 AND ( A.ORG_ID = cf_entered_amountformula.ORG_ID
139 OR A.ORG_ID IS NULL )
140 AND A.PAYMENT_CURRENCY_CODE = CF_ENTERED_AMOUNTFORMULA.INVOICE_CURRENCY_CODE
141 AND C.STATUS_LOOKUP_CODE IN ( LV_NEGOT_LOOKUP , LV_CLEAR_LOOKUP , LV_VOIDED_LOOKUP , LV_REC_UNACC_LOOKUP , LV_REC_LOOKUP , LV_CLEAR_UNACC_LOOKUP , LV_ISSUED_LOOKUP , LV_OVERFLOW_LOOKUP )
142 AND TRUNC(B.ACCOUNTING_DATE) <= TRUNC(P_AS_OF_DATE);
143 SELECT
144 SUM(B.AMOUNT)
145 INTO V_AMOUNT_DR2
146 FROM
147 AP_INVOICES_ALL A,
148 AP_INVOICE_DISTRIBUTIONS_ALL B,
149 PO_VENDORS POV,
150 PO_VENDOR_SITES_ALL POVS
151 WHERE A.INVOICE_ID = B.INVOICE_ID
152 AND POV.VENDOR_ID = A.VENDOR_ID
153 AND POV.VENDOR_ID = POVS.VENDOR_ID
154 AND A.VENDOR_ID = NVL(P_VENDOR_ID
155 ,A.VENDOR_ID)
156 AND A.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
157 AND A.ACCTS_PAY_CODE_COMBINATION_ID = NVL(ACCTS
158 ,A.ACCTS_PAY_CODE_COMBINATION_ID)
159 AND A.VENDOR_ID = cf_entered_amountformula.VENDOR_ID
160 AND ( A.ORG_ID = cf_entered_amountformula.ORG_ID
161 OR A.ORG_ID IS NULL )
162 AND A.INVOICE_CURRENCY_CODE = CF_ENTERED_AMOUNTFORMULA.INVOICE_CURRENCY_CODE
163 AND A.INVOICE_TYPE_LOOKUP_CODE IN ( LV_DEBIT_LOOKUP )
164 AND B.LINE_TYPE_LOOKUP_CODE <> LV_PREPAY_LINE_TYPE
165 AND B.MATCH_STATUS_FLAG = 'A'
166 AND TRUNC(B.ACCOUNTING_DATE) <= TRUNC(P_AS_OF_DATE);
167 V_AMOUNT := NVL(V_AMOUNT_CR1
168 ,0) + NVL(V_AMOUNT_CR2
169 ,0) - NVL(ABS(V_AMOUNT_DR2)
170 ,0) - NVL(V_AMOUNT_DR1
171 ,0);
172 RETURN (V_AMOUNT);
173 RETURN NULL;
174 EXCEPTION
175 WHEN OTHERS THEN
176 /*SRW.MESSAGE(1
177 ,SQLERRM)*/NULL;
178 RETURN (NULL);
179 RETURN (V_AMOUNT);
180 END CF_ENTERED_AMOUNTFORMULA;
181
182 FUNCTION CF_RE_AMTFORMULA(ACCTS IN NUMBER
183 ,ORG_ID IN NUMBER
184 ,VENDOR_ID IN NUMBER
185 ,INVOICE_CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
186 LV_GAIN_LINE_TYPE CONSTANT XLA_AE_LINES.ACCOUNTING_CLASS_CODE%TYPE DEFAULT 'GAIN';
187 LV_LOSS_LINE_TYPE CONSTANT XLA_AE_LINES.ACCOUNTING_CLASS_CODE%TYPE DEFAULT 'LOSS';
188 LV_ENTITY_CODE XLA_AE_LINES.ACCOUNTING_CLASS_CODE%TYPE := 'AP_INVOICES';
189 CURSOR ALL_INVOICES(ORGN_ID IN NUMBER,VEND_ID IN NUMBER,CURR_CODE IN VARCHAR2) IS
190 SELECT
191 NVL(SUM(B.AMOUNT)
192 ,0) AMOUNT,
193 MIN(B.EXCHANGE_DATE) EXCHANGE_DATE,
194 MIN(B.EXCHANGE_RATE_TYPE) EXCHANGE_RATE_TYPE,
195 MIN(B.EXCHANGE_RATE) EXCHANGE_RATE
196 FROM
197 AP_INVOICES_ALL A,
198 AP_INVOICE_DISTRIBUTIONS_ALL B,
199 PO_VENDORS POV,
200 PO_VENDOR_SITES_ALL POVS
201 WHERE A.INVOICE_ID = B.INVOICE_ID
202 AND POV.VENDOR_ID = A.VENDOR_ID
203 AND POVS.VENDOR_ID = POV.VENDOR_ID
204 AND A.VENDOR_ID = NVL(P_VENDOR_ID
205 ,A.VENDOR_ID)
206 AND A.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
207 AND A.ACCTS_PAY_CODE_COMBINATION_ID = NVL(ACCTS
208 ,A.ACCTS_PAY_CODE_COMBINATION_ID)
209 AND ( A.ORG_ID = ORGN_ID
210 OR A.ORG_ID IS NULL )
211 AND A.VENDOR_ID = VEND_ID
212 AND A.INVOICE_CURRENCY_CODE = CURR_CODE
213 AND A.INVOICE_TYPE_LOOKUP_CODE NOT IN ( LV_PREPAYMENT_LOOKUP , LV_CREDIT_LOOKUP , LV_DEBIT_LOOKUP )
214 AND B.LINE_TYPE_LOOKUP_CODE <> LV_PREPAY_LINE_TYPE
215 AND B.MATCH_STATUS_FLAG = 'A'
216 AND TRUNC(B.ACCOUNTING_DATE) <= TRUNC(P_AS_OF_DATE)
217 GROUP BY
218 A.INVOICE_ID;
219 CURSOR ALL_PAYMENTS(ORGN_ID IN NUMBER,VEND_ID IN NUMBER,CURR_CODE IN VARCHAR2) IS
220 SELECT
221 B.AMOUNT,
222 A.PAYMENT_CURRENCY_CODE,
223 A.EXCHANGE_RATE,
224 B.EXCHANGE_DATE,
225 B.EXCHANGE_RATE_TYPE
226 FROM
227 AP_INVOICES_ALL A,
228 AP_INVOICE_PAYMENTS_ALL B,
229 AP_CHECKS_ALL C,
230 PO_VENDORS POV,
231 PO_VENDOR_SITES_ALL POVS
232 WHERE A.INVOICE_ID = B.INVOICE_ID
233 AND POV.VENDOR_ID = A.VENDOR_ID
234 AND B.CHECK_ID = C.CHECK_ID
235 AND POVS.VENDOR_ID = POV.VENDOR_ID
236 AND A.VENDOR_ID = NVL(P_VENDOR_ID
237 ,A.VENDOR_ID)
238 AND A.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
239 AND A.ACCTS_PAY_CODE_COMBINATION_ID = NVL(ACCTS
240 ,A.ACCTS_PAY_CODE_COMBINATION_ID)
241 AND ( A.ORG_ID = ORGN_ID
242 OR A.ORG_ID IS NULL )
243 AND A.VENDOR_ID = VEND_ID
244 AND A.PAYMENT_CURRENCY_CODE = CURR_CODE
245 AND C.STATUS_LOOKUP_CODE IN ( LV_NEGOT_LOOKUP , LV_CLEAR_LOOKUP , LV_VOIDED_LOOKUP , LV_REC_UNACC_LOOKUP , LV_REC_LOOKUP , LV_CLEAR_UNACC_LOOKUP , LV_ISSUED_LOOKUP , LV_OVERFLOW_LOOKUP )
246 AND TRUNC(B.ACCOUNTING_DATE) <= TRUNC(P_AS_OF_DATE);
247 CURSOR FOR_CM_AMT(ORGN_ID IN NUMBER,VEND_ID IN NUMBER,CURR_CODE IN VARCHAR2) IS
248 SELECT
249 B.AMOUNT,
250 B.EXCHANGE_DATE,
251 B.EXCHANGE_RATE_TYPE,
252 B.EXCHANGE_RATE,
253 A.INVOICE_CURRENCY_CODE
254 FROM
255 AP_INVOICES_ALL A,
256 AP_INVOICE_DISTRIBUTIONS_ALL B,
257 PO_VENDORS POV,
258 PO_VENDOR_SITES_ALL POVS
259 WHERE A.INVOICE_ID = B.INVOICE_ID
260 AND POV.VENDOR_ID = A.VENDOR_ID
261 AND POVS.VENDOR_ID = POV.VENDOR_ID
262 AND A.VENDOR_ID = NVL(P_VENDOR_ID
263 ,A.VENDOR_ID)
264 AND A.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
265 AND A.ACCTS_PAY_CODE_COMBINATION_ID = NVL(ACCTS
266 ,A.ACCTS_PAY_CODE_COMBINATION_ID)
267 AND A.VENDOR_ID = VEND_ID
268 AND ( A.ORG_ID = ORGN_ID
269 OR A.ORG_ID IS NULL )
270 AND A.INVOICE_CURRENCY_CODE = CURR_CODE
271 AND A.INVOICE_TYPE_LOOKUP_CODE IN ( LV_CREDIT_LOOKUP )
272 AND B.MATCH_STATUS_FLAG = 'A'
273 AND TRUNC(B.ACCOUNTING_DATE) <= TRUNC(P_AS_OF_DATE);
274 CURSOR FOR_DM_AMT(ORGN_ID IN NUMBER,VEND_ID IN NUMBER,CURR_CODE IN VARCHAR2) IS
275 SELECT
276 NVL(SUM(A.AMOUNT)
277 ,0) AMOUNT,
278 MIN(A.EXCHANGE_RATE_TYPE) EXCHANGE_RATE_TYPE,
279 MIN(A.EXCHANGE_RATE) EXCHANGE_RATE,
280 MIN(A.EXCHANGE_DATE) EXCHANGE_DATE
281 FROM
282 AP_INVOICES_ALL B,
283 AP_INVOICE_DISTRIBUTIONS_ALL A,
284 PO_VENDORS POV,
285 PO_VENDOR_SITES_ALL POVS
286 WHERE A.INVOICE_ID = B.INVOICE_ID
287 AND POV.VENDOR_ID = B.VENDOR_ID
288 AND B.VENDOR_ID = NVL(P_VENDOR_ID
289 ,B.VENDOR_ID)
290 AND POVS.VENDOR_ID = POV.VENDOR_ID
291 AND B.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
292 AND B.ACCTS_PAY_CODE_COMBINATION_ID = NVL(ACCTS
293 ,B.ACCTS_PAY_CODE_COMBINATION_ID)
294 AND ( B.ORG_ID = ORGN_ID
295 OR B.ORG_ID IS NULL )
296 AND B.VENDOR_ID = VEND_ID
297 AND A.MATCH_STATUS_FLAG = 'A'
298 AND B.INVOICE_CURRENCY_CODE = CURR_CODE
299 AND TRUNC(A.ACCOUNTING_DATE) <= TRUNC(P_AS_OF_DATE)
300 AND B.INVOICE_TYPE_LOOKUP_CODE = LV_DEBIT_LOOKUP
301 AND A.LINE_TYPE_LOOKUP_CODE <> LV_PREPAY_LINE_TYPE
302 GROUP BY
303 B.INVOICE_ID;
304 V_PAYMENT_CURRENCY_CODE VARCHAR2(15);
305 V_SET_OF_BOOKS_ID NUMBER;
306 V_FUNCTIONAL_CURRENCY VARCHAR2(15);
307 V_INT_AMT NUMBER;
308 V_RE_AMT_CR NUMBER := 0;
309 V_RE_AMT_DR NUMBER := 0;
310 V_RE_AMT NUMBER := 0;
311 V_CM_AMT NUMBER := 0;
312 V_DM_AMT NUMBER := 0;
313 V_SUM_EXCH_GAIN_AMT NUMBER := 0;
314 V_SUM_EXCH_LOSS_AMT NUMBER := 0;
315 BEGIN
316 SELECT
317 SET_OF_BOOKS_ID
318 INTO V_SET_OF_BOOKS_ID
319 FROM
320 ORG_ORGANIZATION_DEFINITIONS
321 WHERE NVL(OPERATING_UNIT
322 ,0) = NVL(ORG_ID
323 ,0)
324 AND ROWNUM = 1;
325 SELECT
326 CURRENCY_CODE
327 INTO V_FUNCTIONAL_CURRENCY
328 FROM
329 GL_SETS_OF_BOOKS
330 WHERE SET_OF_BOOKS_ID = V_SET_OF_BOOKS_ID;
331 FOR i IN ALL_INVOICES(NVL(org_id, 0), vendor_id, invoice_currency_code)LOOP
332 V_INT_AMT := JAI_CMN_UTILS_PKG.CURRENCY_CONVERSION(V_SET_OF_BOOKS_ID
333 ,INVOICE_CURRENCY_CODE
334 ,I.EXCHANGE_DATE
335 ,I.EXCHANGE_RATE_TYPE
336 ,I.EXCHANGE_RATE);
337 V_INT_AMT := V_INT_AMT * I.AMOUNT;
338 V_RE_AMT_CR := V_RE_AMT_CR + V_INT_AMT;
339 END LOOP;
340 FOR i IN FOR_CM_AMT(NVL(org_id, 0), vendor_id, invoice_currency_code) LOOP
341 V_INT_AMT := JAI_CMN_UTILS_PKG.CURRENCY_CONVERSION(V_SET_OF_BOOKS_ID
342 ,I.INVOICE_CURRENCY_CODE
343 ,I.EXCHANGE_DATE
344 ,I.EXCHANGE_RATE_TYPE
345 ,I.EXCHANGE_RATE);
346 V_INT_AMT := V_INT_AMT * I.AMOUNT;
347 V_RE_AMT_CR := V_RE_AMT_CR + V_INT_AMT;
348 END LOOP;
349 FOR i IN ALL_PAYMENTS(NVL(org_id, 0), vendor_id, invoice_currency_code) LOOP
350 V_INT_AMT := JAI_CMN_UTILS_PKG.CURRENCY_CONVERSION(V_SET_OF_BOOKS_ID
351 ,I.PAYMENT_CURRENCY_CODE
352 ,I.EXCHANGE_DATE
353 ,I.EXCHANGE_RATE_TYPE
354 ,I.EXCHANGE_RATE);
355 V_INT_AMT := V_INT_AMT * I.AMOUNT;
356 V_RE_AMT_DR := V_RE_AMT_DR + V_INT_AMT;
357 END LOOP;
358 FOR i IN FOR_DM_AMT(NVL(org_id, 0), vendor_id, invoice_currency_code) LOOP
359 V_INT_AMT := JAI_CMN_UTILS_PKG.CURRENCY_CONVERSION(V_SET_OF_BOOKS_ID
360 ,INVOICE_CURRENCY_CODE
361 ,I.EXCHANGE_DATE
362 ,I.EXCHANGE_RATE_TYPE
363 ,I.EXCHANGE_RATE);
364 V_INT_AMT := V_INT_AMT * ABS(I.AMOUNT);
365 V_RE_AMT_DR := V_RE_AMT_DR + V_INT_AMT;
366 END LOOP;
367 BEGIN
368 SELECT
369 SUM(NVL(XAL.ACCOUNTED_CR
370 ,0)),
371 SUM(NVL(XAL.ACCOUNTED_DR
372 ,0))
373 INTO V_SUM_EXCH_GAIN_AMT,V_SUM_EXCH_LOSS_AMT
374 FROM
375 AP_INVOICES_ALL A,
376 AP_INVOICE_DISTRIBUTIONS_ALL B,
377 PO_VENDORS POV,
378 PO_VENDOR_SITES_ALL POVS,
379 XLA_AE_LINES XAL,
380 XLA_AE_HEADERS XAH,
381 XLA_TRANSACTION_ENTITIES XTE
382 WHERE A.INVOICE_ID = B.INVOICE_ID
383 AND POV.VENDOR_ID = A.VENDOR_ID
384 AND POVS.VENDOR_ID = POV.VENDOR_ID
385 AND A.VENDOR_ID = NVL(P_VENDOR_ID
386 ,A.VENDOR_ID)
387 AND A.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
388 AND B.ACCOUNTING_EVENT_ID = XAH.EVENT_ID
389 AND XAH.APPLICATION_ID = 200
390 AND XAH.ENTITY_ID = XTE.ENTITY_ID
391 AND XTE.ENTITY_CODE = LV_ENTITY_CODE
392 AND XTE.APPLICATION_ID = 200
393 AND XTE.SOURCE_ID_INT_1 = A.INVOICE_ID
394 AND XAL.APPLICATION_ID = 200
395 AND XAL.AE_HEADER_ID = XAL.AE_HEADER_ID
396 AND XAH.LEDGER_ID = B.SET_OF_BOOKS_ID
397 AND A.ACCTS_PAY_CODE_COMBINATION_ID = NVL(ACCTS
398 ,A.ACCTS_PAY_CODE_COMBINATION_ID)
399 AND ( A.ORG_ID = CF_RE_AMTFORMULA.ORG_ID
400 OR A.ORG_ID IS NULL )
401 AND A.VENDOR_ID = CF_RE_AMTFORMULA.VENDOR_ID
402 AND A.INVOICE_CURRENCY_CODE = CF_RE_AMTFORMULA.INVOICE_CURRENCY_CODE
403 AND XAL.ACCOUNTING_CLASS_CODE IN ( LV_GAIN_LINE_TYPE , LV_LOSS_LINE_TYPE )
404 AND B.ACCOUNTING_DATE <= P_AS_OF_DATE;
405 EXCEPTION
406 WHEN OTHERS THEN
407 /*SRW.MESSAGE(1
408 ,SQLERRM)*/NULL;
409 V_SUM_EXCH_GAIN_AMT := 0;
410 V_SUM_EXCH_LOSS_AMT := 0;
411 END;
412 V_RE_AMT := V_RE_AMT_CR - V_RE_AMT_DR - NVL(V_SUM_EXCH_GAIN_AMT
413 ,0) + NVL(V_SUM_EXCH_LOSS_AMT
414 ,0);
415 RETURN (V_RE_AMT);
416 RETURN (V_RE_AMT);
417 RETURN NULL;
418 EXCEPTION
419 WHEN OTHERS THEN
420 /*SRW.MESSAGE(3
421 ,SQLERRM)*/NULL;
422 RETURN (0);
423 END CF_RE_AMTFORMULA;
424
425 FUNCTION CF_ACCTSFORMULA(ACCTS IN NUMBER) RETURN VARCHAR2 IS
426 V_ACCOUNT VARCHAR(1000);
427 BEGIN
428 JAI_CMN_GL_PKG.GET_ACCOUNT_NUMBER(P_CHARTS_OF_ACCTS_ID
429 ,ACCTS
430 ,V_ACCOUNT);
431 RETURN (V_ACCOUNT);
432 END CF_ACCTSFORMULA;
433
434 FUNCTION CF_ADDR1FORMULA(ORG_ID IN NUMBER) RETURN VARCHAR2 IS
435 V_LOCATION_ID NUMBER;
436 V_ADDRESS VARCHAR2(1000);
437 CURSOR FOR_LOCATION IS
438 SELECT
439 LOCATION_ID
440 FROM
441 HR_ORGANIZATION_UNITS
442 WHERE ORGANIZATION_ID = NVL(ORG_ID
443 ,0);
444 CURSOR FOR_ADDRESS IS
445 SELECT
446 ADDRESS_LINE_1 || ',' || ADDRESS_LINE_2 || ',' || ADDRESS_LINE_3 || ',' || TOWN_OR_CITY || ',' || COUNTRY || ',' || POSTAL_CODE || ',' || TELEPHONE_NUMBER_1 || ',' || TELEPHONE_NUMBER_2 || ',' || TELEPHONE_NUMBER_3
447 FROM
448 HR_LOCATIONS
449 WHERE LOCATION_ID = V_LOCATION_ID;
450 BEGIN
451 OPEN FOR_LOCATION;
452 FETCH FOR_LOCATION
453 INTO V_LOCATION_ID;
454 CLOSE FOR_LOCATION;
455 OPEN FOR_ADDRESS;
456 FETCH FOR_ADDRESS
457 INTO V_ADDRESS;
458 CLOSE FOR_ADDRESS;
459 RETURN (V_ADDRESS);
460 END CF_ADDR1FORMULA;
461
462 FUNCTION CF_SOB_NAMEFORMULA RETURN VARCHAR2 IS
463 CURSOR FOR_SOB_ID(ORG_ID IN NUMBER) IS
464 SELECT
465 SET_OF_BOOKS_ID
466 FROM
467 HR_OPERATING_UNITS
468 WHERE ORGANIZATION_ID = ORG_ID;
469 CURSOR FOR_SOB_NAME(SOB_ID IN NUMBER) IS
470 SELECT
471 NAME
472 FROM
473 GL_SETS_OF_BOOKS
474 WHERE SET_OF_BOOKS_ID = SOB_ID;
475 V_SET_OF_BOOKS_ID NUMBER;
476 V_SOB_NAME VARCHAR2(100);
477 BEGIN
478 OPEN FOR_SOB_ID(P_ORG_ID);
479 FETCH FOR_SOB_ID
480 INTO V_SET_OF_BOOKS_ID;
481 CLOSE FOR_SOB_ID;
482 OPEN FOR_SOB_NAME(V_SET_OF_BOOKS_ID);
483 FETCH FOR_SOB_NAME
484 INTO V_SOB_NAME;
485 CLOSE FOR_SOB_NAME;
486 RETURN (V_SOB_NAME);
487 END CF_SOB_NAMEFORMULA;
488
489 FUNCTION CF_P_VENDOR_NAMEFORMULA RETURN VARCHAR2 IS
490 CURSOR FOR_VENDOR_NAME(V_ID IN NUMBER) IS
491 SELECT
492 VENDOR_NAME
493 FROM
494 PO_VENDORS
495 WHERE VENDOR_ID = V_ID;
496 V_VENDOR_NAME VARCHAR(100);
497 BEGIN
498 OPEN FOR_VENDOR_NAME(P_VENDOR_ID);
499 FETCH FOR_VENDOR_NAME
500 INTO V_VENDOR_NAME;
501 CLOSE FOR_VENDOR_NAME;
502 RETURN (V_VENDOR_NAME);
503 END CF_P_VENDOR_NAMEFORMULA;
504
505 FUNCTION CF_P_ACCTSFORMULA RETURN VARCHAR2 IS
506 V_ACCOUNT VARCHAR(1000);
507 BEGIN
508 JAI_CMN_GL_PKG.GET_ACCOUNT_NUMBER(P_CHARTS_OF_ACCTS_ID
509 ,P_ACCTS_ID
510 ,V_ACCOUNT);
511 RETURN (V_ACCOUNT);
512 END CF_P_ACCTSFORMULA;
513
514 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
515 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
516 SELECT
517 CONCURRENT_PROGRAM_ID,
518 NVL(ENABLE_TRACE
519 ,'N')
520 FROM
521 FND_CONCURRENT_REQUESTS
522 WHERE REQUEST_ID = P_REQUEST_ID;
523 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
524 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
525 BEGIN
526 IF P_ORG_ID IS NOT NULL THEN
527 MO_GLOBAL.SET_POLICY_CONTEXT('S'
528 ,P_ORG_ID);
529 ELSE
530 MO_GLOBAL.SET_POLICY_CONTEXT('M'
531 ,NULL);
532 END IF;
533 /*SRW.MESSAGE(1275
534 ,'Report Version is 120.4 Last modified date is 19/07/2006')*/NULL;
535 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
536 CP_AS_OF_DATE := TO_CHAR(P_AS_OF_DATE,'DD-MON-YYYY');
537 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
538 BEGIN
539 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
540 FETCH C_PROGRAM_ID
541 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
542 CLOSE C_PROGRAM_ID;
543 /*SRW.MESSAGE(1275
544 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
545 IF V_ENABLE_TRACE = 'Y' THEN
546 EXECUTE IMMEDIATE
547 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
548 END IF;
549 RETURN (TRUE);
550 EXCEPTION
551 WHEN OTHERS THEN
552 /*SRW.MESSAGE(1275
553 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg ->' || SQLERRM)*/NULL;
554 RETURN (TRUE);
555 END;
556 END BEFOREREPORT;
557
558 FUNCTION F_SELECTED_VENDOR RETURN VARCHAR2 IS
559 RET_TEXT VARCHAR2(1000) := NULL;
560
561 BEGIN
562 IF P_VENDOR_ID IS NOT NULL THEN
563 RET_TEXT := ' AND api.vendor_id = :p_vendor_id ';
564 ELSE
565 RET_TEXT := ' AND api.vendor_id = api.vendor_id ';
566 END IF;
567 IF RET_TEXT IS NOT NULL THEN
568 RET_TEXT := RET_TEXT ;
569 END IF;
570 RETURN RET_TEXT;
571 END F_SELECTED_VENDOR;
572
573 FUNCTION F_SELECTED_VENDOR_NO RETURN VARCHAR2 IS
574 RET_TEXT VARCHAR2(1000) := NULL;
575
576 BEGIN
577 IF P_VENDOR_NO IS NOT NULL THEN
578 RET_TEXT := 'AND pov.segment1 = :P_vendor_no ';
579 ELSE
580 RET_TEXT := 'AND pov.segment1 = pov.segment1 ';
581 END IF;
582 IF RET_TEXT IS NOT NULL THEN
583 RET_TEXT := RET_TEXT ;
584 END IF;
585 RETURN RET_TEXT;
586 END F_SELECTED_VENDOR_NO;
587
588 FUNCTION AFTERPFORM RETURN BOOLEAN IS
589 BEGIN
590 LP_SELECTED_VENDOR_ID := F_SELECTED_VENDOR;
591 LP_SELECTED_VENDOR_NO := F_SELECTED_VENDOR_NO;
592 RETURN (TRUE);
593 END AFTERPFORM;
594
595 FUNCTION AFTERREPORT RETURN BOOLEAN IS
596 BEGIN
597 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
598 RETURN (TRUE);
599 END AFTERREPORT;
600
601 END JA_JAINCTRB_XMLP_PKG;
602
603