[Home] [Help]
PACKAGE BODY: APPS.JA_JAINARDR_XMLP_PKG
Source
1 PACKAGE BODY JA_JAINARDR_XMLP_PKG AS
2 /* $Header: JAINARDRB.pls 120.1 2007/12/25 16:12:13 dwkrishn noship $ */
3 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
4 Y VARCHAR2(15);
5 BEGIN
6 RETURN (TRUE);
7 END BEFOREPFORM;
8 FUNCTION OPEN_BAL_TRFORMULA(CUSTOMER_ID IN NUMBER
9 ,CURR_CODE IN VARCHAR2) RETURN NUMBER IS
10 CURSOR GET_DEBIT_AMOUNT IS
11 SELECT
12 SUM((B.AMOUNT)) SUM_EXT_AMOUNT,
13 SUM((B.AMOUNT) * NVL(A.EXCHANGE_RATE
14 ,1))
15 FROM
16 RA_CUSTOMER_TRX_ALL A,
17 AR_PAYMENT_SCHEDULES_ALL C,
18 RA_CUST_TRX_LINE_GL_DIST_ALL B
19 WHERE A.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
20 AND TRUNC(C.GL_DATE) < TRUNC(P_START_DATE)
21 AND A.INVOICE_CURRENCY_CODE = CURR_CODE
22 AND A.COMPLETE_FLAG = 'Y'
23 AND B.CUSTOMER_TRX_ID = A.CUSTOMER_TRX_ID
24 AND B.ACCOUNT_CLASS = LV_REC_ACCOUNT_CLASS
25 AND B.LATEST_REC_FLAG = 'Y'
26 AND A.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
27 AND C.CLASS In ( LV_INV_CLASS , LV_DM_CLASS , LV_DEP_CLASS )
28 AND A.ORG_ID = P_ORG_ID
29 AND C.PAYMENT_SCHEDULE_ID IN (
30 SELECT
31 MIN(PAYMENT_SCHEDULE_ID)
32 FROM
33 AR_PAYMENT_SCHEDULES_ALL
34 WHERE CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID );
35 CURSOR GET_ADJUSTMENT_AMOUNT IS
36 SELECT
37 SUM((B.AMOUNT)) SUM_ADJ_AMOUNT,
38 SUM((B.AMOUNT) * NVL(C.EXCHANGE_RATE
39 ,1))
40 FROM
41 AR_ADJUSTMENTS_ALL B,
42 RA_CUSTOMER_TRX_ALL C,
43 AR_PAYMENT_SCHEDULES_ALL D,
44 GL_CODE_COMBINATIONS E
45 WHERE B.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
46 AND C.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
47 AND E.CODE_COMBINATION_ID = B.CODE_COMBINATION_ID
48 AND TRUNC(B.GL_DATE) < TRUNC(P_START_DATE)
49 AND C.INVOICE_CURRENCY_CODE = CURR_CODE
50 AND B.STATUS = 'A'
51 AND B.CUSTOMER_TRX_ID = D.CUSTOMER_TRX_ID
52 AND C.ORG_ID = P_ORG_ID
53 AND D.PAYMENT_SCHEDULE_ID IN (
54 SELECT
55 MIN(PAYMENT_SCHEDULE_ID)
56 FROM
57 AR_PAYMENT_SCHEDULES_ALL
58 WHERE CUSTOMER_TRX_ID = D.CUSTOMER_TRX_ID );
59 CURSOR GET_CREDIT_AMOUNT IS
60 SELECT
61 SUM(A.AMOUNT) SUM_AMOUNT,
62 SUM(A.AMOUNT * NVL(A.EXCHANGE_RATE
63 ,1.00)) SUM_AMOUNT_EXCHANGE
64 FROM
65 AR_CASH_RECEIPTS_ALL A
66 WHERE A.PAY_FROM_CUSTOMER = CUSTOMER_ID
67 AND A.CURRENCY_CODE = CURR_CODE
68 AND A.ORG_ID = P_ORG_ID
69 AND EXISTS (
70 SELECT
71 1
72 FROM
73 AR_CASH_RECEIPT_HISTORY_ALL
74 WHERE CASH_RECEIPT_ID = A.CASH_RECEIPT_ID
75 AND ORG_ID = P_ORG_ID
76 AND TRUNC(GL_DATE) < TRUNC(P_START_DATE) );
77 CURSOR GET_REVERSAL_AMOUNT IS
78 SELECT
79 SUM(A.AMOUNT) SUM_AMOUNT,
80 SUM(A.AMOUNT * NVL(A.EXCHANGE_RATE
81 ,1.00)) SUM_AMOUNT_EXCHANGE
82 FROM
83 AR_CASH_RECEIPTS_ALL A,
84 AR_CASH_RECEIPT_HISTORY_ALL B
85 WHERE A.CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
86 AND A.PAY_FROM_CUSTOMER = CUSTOMER_ID
87 AND TRUNC(B.GL_DATE) < TRUNC(P_START_DATE)
88 AND A.REVERSAL_DATE is not null
89 AND A.CURRENCY_CODE = CURR_CODE
90 AND B.STATUS = LV_REV_STATUS
91 AND A.ORG_ID = P_ORG_ID;
92 CURSOR GET_DISCOUNT_CUR(CP_APP_TYPE IN AR_RECEIVABLE_APPLICATIONS_ALL.APPLICATION_TYPE%TYPE) IS
93 SELECT
94 NVL(SUM(ABS(NVL(D.EARNED_DISCOUNT_TAKEN
95 ,0)))
96 ,0) SUM_AMOUNT,
97 NVL(SUM(ABS(NVL(D.ACCTD_EARNED_DISCOUNT_TAKEN
98 ,0)))
99 ,0) SUM_AMOUNT_EXCHANGE
100 FROM
101 RA_CUSTOMER_TRX_ALL B,
102 AR_RECEIVABLE_APPLICATIONS_ALL D
103 WHERE B.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
104 AND B.COMPLETE_FLAG = 'Y'
105 AND TRUNC(D.GL_DATE) < TRUNC(P_START_DATE)
106 AND D.APPLIED_CUSTOMER_TRX_ID = B.CUSTOMER_TRX_ID
107 AND B.INVOICE_CURRENCY_CODE = CURR_CODE
108 AND D.EARNED_DISCOUNT_TAKEN is not null
109 AND D.EARNED_DISCOUNT_TAKEN <> 0
110 AND B.ORG_ID = P_ORG_ID
111 AND D.APPLICATION_TYPE = CP_APP_TYPE
112 AND D.DISPLAY = 'Y';
113 CURSOR C_EXCH_GAINLOSS_CR IS
114 SELECT
115 SUM(E.AMOUNT_CR) SUM_AMOUNT,
116 SUM(E.ACCTD_AMOUNT_CR) SUM_EXCHANGE_AMOUNT
117 FROM
118 RA_CUSTOMER_TRX_ALL B,
119 AR_CASH_RECEIPTS_ALL C,
120 AR_RECEIVABLE_APPLICATIONS_ALL D,
121 AR_DISTRIBUTIONS_ALL E
122 WHERE B.CUSTOMER_TRX_ID = D.APPLIED_CUSTOMER_TRX_ID
123 AND C.CASH_RECEIPT_ID = D.CASH_RECEIPT_ID
124 AND E.SOURCE_ID = D.RECEIVABLE_APPLICATION_ID
125 AND B.ORG_ID = P_ORG_ID
126 AND E.SOURCE_TYPE IN ( LV_LOSS_SOURCE_TYPE , LV_GAIN_SOURCE_TYPE )
127 AND B.INVOICE_CURRENCY_CODE = CURR_CODE
128 AND B.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
129 AND TRUNC(D.GL_DATE) < TRUNC(P_START_DATE);
130 CURSOR C_EXCH_GAINLOSS_DR IS
131 SELECT
132 SUM(E.AMOUNT_DR) SUM_AMOUNT,
133 SUM(E.ACCTD_AMOUNT_DR) SUM_EXCHANGE_AMOUNT
134 FROM
135 RA_CUSTOMER_TRX_ALL B,
136 AR_CASH_RECEIPTS_ALL C,
137 AR_RECEIVABLE_APPLICATIONS_ALL D,
138 AR_DISTRIBUTIONS_ALL E
139 WHERE B.CUSTOMER_TRX_ID = D.APPLIED_CUSTOMER_TRX_ID
140 AND C.CASH_RECEIPT_ID = D.CASH_RECEIPT_ID
141 AND E.SOURCE_ID = D.RECEIVABLE_APPLICATION_ID
142 AND B.ORG_ID = P_ORG_ID
143 AND E.SOURCE_TYPE IN ( LV_LOSS_SOURCE_TYPE , LV_GAIN_SOURCE_TYPE )
144 AND B.INVOICE_CURRENCY_CODE = CURR_CODE
145 AND B.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
146 AND TRUNC(D.GL_DATE) < TRUNC(P_START_DATE);
147 CURSOR C_RECEIPT_W_OFF IS
148 SELECT
149 SUM(C.AMOUNT_APPLIED) SUM_AMOUNT,
150 SUM(C.AMOUNT_APPLIED * NVL(A.EXCHANGE_RATE
151 ,1.00)) SUM_AMOUNT_EXCHANGE
152 FROM
153 AR_CASH_RECEIPTS_ALL A,
154 AR_CASH_RECEIPT_HISTORY_ALL B,
155 AR_RECEIVABLE_APPLICATIONS_ALL C
156 WHERE A.PAY_FROM_CUSTOMER = CUSTOMER_ID
157 AND TRUNC(B.GL_DATE) < TRUNC(P_START_DATE)
158 AND A.CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
159 AND A.CASH_RECEIPT_ID = C.CASH_RECEIPT_ID
160 AND C.CASH_RECEIPT_HISTORY_ID = B.CASH_RECEIPT_HISTORY_ID
161 AND C.APPLIED_PAYMENT_SCHEDULE_ID = - 3
162 AND C.STATUS = LV_ACT_STATUS
163 AND A.CURRENCY_CODE = CURR_CODE
164 AND B.REVERSAL_GL_DATE IS NULL
165 AND B.CURRENT_RECORD_FLAG = 'Y'
166 AND A.ORG_ID = P_ORG_ID
167 AND not exists (
168 SELECT
169 1
170 FROM
171 AR_CASH_RECEIPT_HISTORY_ALL
172 WHERE CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
173 AND STATUS = LV_REV_STATUS );
174 V_TR_DR_AMT NUMBER;
175 V_FUNC_DR_AMT NUMBER;
176 V_TR_CR_AMT NUMBER;
177 V_FUNC_CR_AMT NUMBER;
178 V_TR_REV_AMT NUMBER;
179 V_FUNC_REV_AMT NUMBER;
180 V_TRAN_TOT_AMT NUMBER;
181 V_FUNC_TOT_AMT NUMBER;
182 V_TRAN_CL_BAL_DR NUMBER;
183 V_CRE_MEMO_AMT NUMBER;
184 V_CRE_MEMO_FUNC_AMT NUMBER;
185 V_TR_ADJ_AMT NUMBER;
186 V_FUNC_ADJ_AMT NUMBER;
187 V_FUNC_GAIN_AMT NUMBER;
188 V_FUNC_LOSS_AMT NUMBER;
189 V_TRAN_LOSS_AMT NUMBER;
190 V_TRAN_GAIN_AMT NUMBER;
191 V_TRAN_RCP_W_OFF NUMBER;
192 V_FUNC_RCP_W_OFF NUMBER;
193 V_TR_DISC_CR_AMT NUMBER;
194 V_FUNC_DISC_CR_AMT NUMBER;
195 BEGIN
196 SELECT
197 SUM((B.AMOUNT)) SUM_EXT_AMOUNT,
198 SUM((B.AMOUNT) * NVL(A.EXCHANGE_RATE
199 ,1))
200 INTO V_CRE_MEMO_AMT,V_CRE_MEMO_FUNC_AMT
201 FROM
202 RA_CUSTOMER_TRX_ALL A,
203 AR_PAYMENT_SCHEDULES_ALL C,
204 RA_CUST_TRX_LINE_GL_DIST_ALL B
205 WHERE A.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
206 AND TRUNC(C.GL_DATE) < TRUNC(P_START_DATE)
207 AND A.INVOICE_CURRENCY_CODE = CURR_CODE
208 AND A.COMPLETE_FLAG = 'Y'
209 AND B.CUSTOMER_TRX_ID = A.CUSTOMER_TRX_ID
210 AND B.ACCOUNT_CLASS = LV_REC_ACCOUNT_CLASS
211 AND B.LATEST_REC_FLAG = 'Y'
212 AND A.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
213 AND C.CLASS In ( LV_CM_CLASS )
214 AND A.ORG_ID = P_ORG_ID
215 AND C.PAYMENT_SCHEDULE_ID IN (
216 SELECT
217 MIN(PAYMENT_SCHEDULE_ID)
218 FROM
219 AR_PAYMENT_SCHEDULES_ALL
220 WHERE CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID );
221 OPEN GET_DEBIT_AMOUNT;
222 FETCH GET_DEBIT_AMOUNT
223 INTO V_TR_DR_AMT,V_FUNC_DR_AMT;
224 CLOSE GET_DEBIT_AMOUNT;
225 OPEN GET_CREDIT_AMOUNT;
226 FETCH GET_CREDIT_AMOUNT
227 INTO V_TR_CR_AMT,V_FUNC_CR_AMT;
228 CLOSE GET_CREDIT_AMOUNT;
229 OPEN GET_REVERSAL_AMOUNT;
230 FETCH GET_REVERSAL_AMOUNT
231 INTO V_TR_REV_AMT,V_FUNC_REV_AMT;
232 CLOSE GET_REVERSAL_AMOUNT;
233 OPEN GET_ADJUSTMENT_AMOUNT;
234 FETCH GET_ADJUSTMENT_AMOUNT
235 INTO V_TR_ADJ_AMT,V_FUNC_ADJ_AMT;
236 CLOSE GET_ADJUSTMENT_AMOUNT;
237 OPEN GET_DISCOUNT_CUR('CASH');
238 FETCH GET_DISCOUNT_CUR
239 INTO V_TR_DISC_CR_AMT,V_FUNC_DISC_CR_AMT;
240 CLOSE GET_DISCOUNT_CUR;
241 OPEN C_EXCH_GAINLOSS_DR;
242 FETCH C_EXCH_GAINLOSS_DR
243 INTO V_TRAN_GAIN_AMT,V_FUNC_GAIN_AMT;
244 CLOSE C_EXCH_GAINLOSS_DR;
245 OPEN C_EXCH_GAINLOSS_CR;
246 FETCH C_EXCH_GAINLOSS_CR
247 INTO V_TRAN_LOSS_AMT,V_FUNC_LOSS_AMT;
248 CLOSE C_EXCH_GAINLOSS_CR;
249 OPEN C_RECEIPT_W_OFF;
250 FETCH C_RECEIPT_W_OFF
251 INTO V_TRAN_RCP_W_OFF,V_FUNC_RCP_W_OFF;
252 CLOSE C_RECEIPT_W_OFF;
253 V_FUNC_TOT_AMT := (NVL(V_FUNC_DR_AMT
254 ,0) + NVL(V_CRE_MEMO_FUNC_AMT
255 ,0) + NVL(V_FUNC_REV_AMT
256 ,0) - NVL(V_FUNC_CR_AMT
257 ,0) + NVL(V_FUNC_RCP_W_OFF
258 ,0) + (NVL(V_FUNC_ADJ_AMT
259 ,0)) - NVL(V_FUNC_DISC_CR_AMT
260 ,0) - NVL(V_FUNC_GAIN_AMT
261 ,0) + NVL(V_FUNC_LOSS_AMT
262 ,0));
263 V_TRAN_TOT_AMT := ((NVL(V_TR_DR_AMT
264 ,0)) + NVL(V_CRE_MEMO_AMT
265 ,0) + (NVL(V_TR_REV_AMT
266 ,0)) - NVL(V_TR_CR_AMT
267 ,0) + NVL(V_TRAN_RCP_W_OFF
268 ,0) - ABS(NVL(V_TR_ADJ_AMT
269 ,0)) - NVL(V_TR_DISC_CR_AMT
270 ,0));
271 IF NVL(V_FUNC_TOT_AMT
272 ,0) < 0 THEN
273 FUNC_OPEN_BAL_CR := ABS(V_FUNC_TOT_AMT);
274 FUNC_OPEN_BAL_DR := 0;
275 ELSE
276 FUNC_OPEN_BAL_CR := 0;
277 FUNC_OPEN_BAL_DR := ABS(V_FUNC_TOT_AMT);
278 END IF;
279 IF NVL(V_TRAN_TOT_AMT
280 ,0) < 0 THEN
281 TRAN_OPEN_BAL_CR := ABS(NVL(V_TRAN_TOT_AMT
282 ,0));
283 RETURN (0);
284 ELSE
285 TRAN_OPEN_BAL_CR := 0;
286 RETURN (ABS(NVL(V_TRAN_TOT_AMT
287 ,0)));
288 END IF;
289 END OPEN_BAL_TRFORMULA;
290 FUNCTION FUNC_OPEN_BALFORMULA RETURN NUMBER IS
291 BEGIN
292 NULL;
293 END FUNC_OPEN_BALFORMULA;
294 FUNCTION CF_1FORMULA(CUSTOMER_ID2 IN NUMBER
295 ,CURR_CODE1 IN VARCHAR2) RETURN NUMBER IS
296 CURSOR GET_DEBIT_AMOUNT IS
297 SELECT
298 SUM((B.AMOUNT)) SUM_EXT_AMOUNT,
299 SUM((B.AMOUNT) * NVL(A.EXCHANGE_RATE
300 ,1))
301 FROM
302 RA_CUSTOMER_TRX_ALL A,
303 AR_PAYMENT_SCHEDULES_ALL C,
304 RA_CUST_TRX_LINE_GL_DIST_ALL B
305 WHERE A.BILL_TO_CUSTOMER_ID = CUSTOMER_ID2
306 AND TRUNC(C.GL_DATE) <= TRUNC(P_END_DATE)
307 AND A.INVOICE_CURRENCY_CODE = CURR_CODE1
308 AND A.COMPLETE_FLAG = 'Y'
309 AND B.CUSTOMER_TRX_ID = A.CUSTOMER_TRX_ID
310 AND B.ACCOUNT_CLASS = LV_REC_ACCOUNT_CLASS
311 AND B.LATEST_REC_FLAG = 'Y'
312 AND A.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
313 AND C.CLASS In ( LV_INV_CLASS , LV_DM_CLASS , LV_DEP_CLASS )
314 AND A.ORG_ID = P_ORG_ID
315 AND C.PAYMENT_SCHEDULE_ID IN (
316 SELECT
317 MIN(PAYMENT_SCHEDULE_ID)
318 FROM
319 AR_PAYMENT_SCHEDULES_ALL
320 WHERE CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID );
321 CURSOR GET_ADJUSTMENT_AMOUNT IS
322 SELECT
323 SUM((B.AMOUNT)) SUM_ADJ_AMOUNT,
324 SUM((B.AMOUNT) * NVL(C.EXCHANGE_RATE
325 ,1))
326 FROM
327 AR_ADJUSTMENTS_ALL B,
328 RA_CUSTOMER_TRX_ALL C,
329 AR_PAYMENT_SCHEDULES_ALL D,
330 GL_CODE_COMBINATIONS E
331 WHERE B.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
332 AND E.CODE_COMBINATION_ID = B.CODE_COMBINATION_ID
333 AND C.BILL_TO_CUSTOMER_ID = CUSTOMER_ID2
334 AND TRUNC(B.GL_DATE) <= TRUNC(P_END_DATE)
335 AND C.INVOICE_CURRENCY_CODE = CURR_CODE1
336 AND B.STATUS = 'A'
337 AND B.CUSTOMER_TRX_ID = D.CUSTOMER_TRX_ID
338 AND C.ORG_ID = P_ORG_ID
339 AND D.PAYMENT_SCHEDULE_ID IN (
340 SELECT
341 MIN(PAYMENT_SCHEDULE_ID)
342 FROM
343 AR_PAYMENT_SCHEDULES_ALL
344 WHERE CUSTOMER_TRX_ID = D.CUSTOMER_TRX_ID );
345 CURSOR GET_CREDIT_AMOUNT IS
346 SELECT
347 SUM(A.AMOUNT) SUM_AMOUNT,
348 SUM(A.AMOUNT * NVL(A.EXCHANGE_RATE
349 ,1.00)) SUM_AMOUNT_EXCHANGE
350 FROM
351 AR_CASH_RECEIPTS_ALL A
352 WHERE A.PAY_FROM_CUSTOMER = CUSTOMER_ID2
353 AND A.CURRENCY_CODE = CURR_CODE1
354 AND A.ORG_ID = P_ORG_ID
355 AND EXISTS (
356 SELECT
357 1
358 FROM
359 AR_CASH_RECEIPT_HISTORY_ALL
360 WHERE CASH_RECEIPT_ID = A.CASH_RECEIPT_ID
361 AND ORG_ID = P_ORG_ID
362 AND TRUNC(GL_DATE) <= TRUNC(P_END_DATE) );
363 CURSOR GET_REVERSAL_AMOUNT IS
364 SELECT
365 SUM(A.AMOUNT) SUM_AMOUNT,
366 SUM(A.AMOUNT * NVL(A.EXCHANGE_RATE
367 ,1.00)) SUM_AMOUNT_EXCHANGE
368 FROM
369 AR_CASH_RECEIPTS_ALL A,
370 AR_CASH_RECEIPT_HISTORY_ALL B
371 WHERE A.CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
372 AND A.PAY_FROM_CUSTOMER = CUSTOMER_ID2
373 AND TRUNC(B.GL_DATE) <= TRUNC(P_END_DATE)
374 AND A.REVERSAL_DATE is not null
375 AND A.CURRENCY_CODE = CURR_CODE1
376 AND B.STATUS = LV_REV_STATUS
377 AND A.ORG_ID = P_ORG_ID;
378 CURSOR GET_DISCOUNT_CUR(CP_APP_TYPE IN AR_RECEIVABLE_APPLICATIONS_ALL.APPLICATION_TYPE%TYPE) IS
379 SELECT
380 NVL(SUM(ABS(NVL(D.EARNED_DISCOUNT_TAKEN
381 ,0)))
382 ,0) SUM_AMOUNT,
383 NVL(SUM(ABS(NVL(D.ACCTD_EARNED_DISCOUNT_TAKEN
384 ,0)))
385 ,0) SUM_AMOUNT_EXCHANGE
386 FROM
387 RA_CUSTOMER_TRX_ALL B,
388 AR_RECEIVABLE_APPLICATIONS_ALL D
389 WHERE B.BILL_TO_CUSTOMER_ID = CUSTOMER_ID2
390 AND B.COMPLETE_FLAG = 'Y'
391 AND TRUNC(D.GL_DATE) <= TRUNC(P_END_DATE)
392 AND D.APPLIED_CUSTOMER_TRX_ID = B.CUSTOMER_TRX_ID
393 AND B.INVOICE_CURRENCY_CODE = CURR_CODE1
394 AND B.ORG_ID = P_ORG_ID
395 AND D.EARNED_DISCOUNT_TAKEN is not null
396 AND D.EARNED_DISCOUNT_TAKEN <> 0
397 AND D.APPLICATION_TYPE = CP_APP_TYPE
398 AND D.DISPLAY = 'Y';
399 CURSOR C_EXCH_GAINLOSS_CR IS
400 SELECT
401 SUM(E.AMOUNT_CR) SUM_AMOUNT,
402 SUM(E.ACCTD_AMOUNT_CR) SUM_EXCHANGE_AMOUNT
403 FROM
404 RA_CUSTOMER_TRX_ALL B,
405 AR_CASH_RECEIPTS_ALL C,
406 AR_RECEIVABLE_APPLICATIONS_ALL D,
407 AR_DISTRIBUTIONS_ALL E
408 WHERE B.CUSTOMER_TRX_ID = D.APPLIED_CUSTOMER_TRX_ID
409 AND C.CASH_RECEIPT_ID = D.CASH_RECEIPT_ID
410 AND E.SOURCE_ID = D.RECEIVABLE_APPLICATION_ID
411 AND B.ORG_ID = P_ORG_ID
412 AND E.SOURCE_TYPE IN ( LV_LOSS_SOURCE_TYPE , LV_GAIN_SOURCE_TYPE )
413 AND B.INVOICE_CURRENCY_CODE = CURR_CODE1
414 AND B.BILL_TO_CUSTOMER_ID = CUSTOMER_ID2
415 AND TRUNC(D.GL_DATE) <= TRUNC(P_END_DATE);
416 CURSOR C_EXCH_GAINLOSS_DR IS
417 SELECT
418 SUM(E.AMOUNT_DR) SUM_AMOUNT,
419 SUM(E.ACCTD_AMOUNT_DR) SUM_EXCHANGE_AMOUNT
420 FROM
421 RA_CUSTOMER_TRX_ALL B,
422 AR_CASH_RECEIPTS_ALL C,
423 AR_RECEIVABLE_APPLICATIONS_ALL D,
424 AR_DISTRIBUTIONS_ALL E
425 WHERE B.CUSTOMER_TRX_ID = D.APPLIED_CUSTOMER_TRX_ID
426 AND C.CASH_RECEIPT_ID = D.CASH_RECEIPT_ID
427 AND E.SOURCE_ID = D.RECEIVABLE_APPLICATION_ID
428 AND B.ORG_ID = P_ORG_ID
429 AND B.INVOICE_CURRENCY_CODE = CURR_CODE1
430 AND B.BILL_TO_CUSTOMER_ID = CUSTOMER_ID2
431 AND TRUNC(D.GL_DATE) <= TRUNC(P_END_DATE)
432 AND E.SOURCE_TYPE IN ( LV_LOSS_SOURCE_TYPE , LV_GAIN_SOURCE_TYPE );
433 CURSOR C_RECEIPT_W_OFF IS
434 SELECT
435 SUM(C.AMOUNT_APPLIED) SUM_AMOUNT,
436 SUM(C.AMOUNT_APPLIED * NVL(A.EXCHANGE_RATE
437 ,1.00)) SUM_AMOUNT_EXCHANGE
438 FROM
439 AR_CASH_RECEIPTS_ALL A,
440 AR_CASH_RECEIPT_HISTORY_ALL B,
441 AR_RECEIVABLE_APPLICATIONS_ALL C
442 WHERE A.PAY_FROM_CUSTOMER = CUSTOMER_ID2
443 AND TRUNC(B.GL_DATE) <= TRUNC(P_END_DATE)
444 AND A.CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
445 AND A.CASH_RECEIPT_ID = C.CASH_RECEIPT_ID
446 AND C.CASH_RECEIPT_HISTORY_ID = B.CASH_RECEIPT_HISTORY_ID
447 AND C.APPLIED_PAYMENT_SCHEDULE_ID = - 3
448 AND C.STATUS = LV_ACT_STATUS
449 AND A.CURRENCY_CODE = CURR_CODE1
450 AND B.REVERSAL_GL_DATE IS NULL
451 AND B.CURRENT_RECORD_FLAG = 'Y'
452 AND A.ORG_ID = P_ORG_ID
453 AND not exists (
454 SELECT
455 1
456 FROM
457 AR_CASH_RECEIPT_HISTORY_ALL
458 WHERE CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
459 AND STATUS = LV_REV_STATUS );
460 V_TR_DISC_CR_AMT NUMBER;
461 V_FUNC_DISC_CR_AMT NUMBER;
462 V_TR_DR_AMT NUMBER;
463 V_FUNC_DR_AMT NUMBER;
464 V_TR_CR_AMT NUMBER;
465 V_FUNC_CR_AMT NUMBER;
466 V_TR_REV_AMT NUMBER;
467 V_FUNC_REV_AMT NUMBER;
468 V_TRAN_TOT_AMT NUMBER;
469 V_FUNC_TOT_AMT NUMBER;
470 V_TRAN_CL_BAL_DR NUMBER;
471 V_CRE_MEMO_AMT NUMBER;
472 V_CRE_MEMO_FUNC_AMT NUMBER;
473 V_TR_ADJ_AMT NUMBER;
474 V_FUNC_ADJ_AMT NUMBER;
475 V_FUNC_GAIN_AMT NUMBER;
476 V_FUNC_LOSS_AMT NUMBER;
477 V_TRAN_LOSS_AMT NUMBER;
478 V_TRAN_GAIN_AMT NUMBER;
479 V_TRAN_RCP_W_OFF NUMBER;
480 V_FUNC_RCP_W_OFF NUMBER;
481 BEGIN
482 SELECT
483 SUM((B.AMOUNT)) SUM_EXT_AMOUNT,
484 SUM((B.AMOUNT) * NVL(A.EXCHANGE_RATE
485 ,1))
486 INTO V_CRE_MEMO_AMT,V_CRE_MEMO_FUNC_AMT
487 FROM
488 RA_CUSTOMER_TRX_ALL A,
489 AR_PAYMENT_SCHEDULES_ALL C,
490 RA_CUST_TRX_LINE_GL_DIST_ALL B
491 WHERE A.BILL_TO_CUSTOMER_ID = CUSTOMER_ID2
492 AND TRUNC(C.GL_DATE) <= TRUNC(P_END_DATE)
493 AND A.INVOICE_CURRENCY_CODE = CURR_CODE1
494 AND A.COMPLETE_FLAG = 'Y'
495 AND B.CUSTOMER_TRX_ID = A.CUSTOMER_TRX_ID
496 AND B.ACCOUNT_CLASS = LV_REC_ACCOUNT_CLASS
497 AND B.LATEST_REC_FLAG = 'Y'
498 AND A.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
499 AND C.CLASS In ( LV_CM_CLASS )
500 AND A.ORG_ID = P_ORG_ID
501 AND C.PAYMENT_SCHEDULE_ID IN (
502 SELECT
503 MIN(PAYMENT_SCHEDULE_ID)
504 FROM
505 AR_PAYMENT_SCHEDULES_ALL
506 WHERE CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID );
507 OPEN GET_DEBIT_AMOUNT;
508 FETCH GET_DEBIT_AMOUNT
509 INTO V_TR_DR_AMT,V_FUNC_DR_AMT;
510 CLOSE GET_DEBIT_AMOUNT;
511 OPEN GET_CREDIT_AMOUNT;
512 FETCH GET_CREDIT_AMOUNT
513 INTO V_TR_CR_AMT,V_FUNC_CR_AMT;
514 CLOSE GET_CREDIT_AMOUNT;
515 OPEN GET_REVERSAL_AMOUNT;
516 FETCH GET_REVERSAL_AMOUNT
517 INTO V_TR_REV_AMT,V_FUNC_REV_AMT;
518 CLOSE GET_REVERSAL_AMOUNT;
519 OPEN GET_ADJUSTMENT_AMOUNT;
520 FETCH GET_ADJUSTMENT_AMOUNT
521 INTO V_TR_ADJ_AMT,V_FUNC_ADJ_AMT;
522 CLOSE GET_ADJUSTMENT_AMOUNT;
523 OPEN GET_DISCOUNT_CUR('CASH');
524 FETCH GET_DISCOUNT_CUR
525 INTO V_TR_DISC_CR_AMT,V_FUNC_DISC_CR_AMT;
526 CLOSE GET_DISCOUNT_CUR;
527 OPEN C_EXCH_GAINLOSS_DR;
528 FETCH C_EXCH_GAINLOSS_DR
529 INTO V_TRAN_GAIN_AMT,V_FUNC_GAIN_AMT;
530 CLOSE C_EXCH_GAINLOSS_DR;
531 OPEN C_EXCH_GAINLOSS_CR;
532 FETCH C_EXCH_GAINLOSS_CR
533 INTO V_TRAN_LOSS_AMT,V_FUNC_LOSS_AMT;
534 CLOSE C_EXCH_GAINLOSS_CR;
535 OPEN C_RECEIPT_W_OFF;
536 FETCH C_RECEIPT_W_OFF
537 INTO V_TRAN_RCP_W_OFF,V_FUNC_RCP_W_OFF;
538 CLOSE C_RECEIPT_W_OFF;
539 V_FUNC_TOT_AMT := ((NVL(V_FUNC_DR_AMT
540 ,0) + NVL(V_CRE_MEMO_FUNC_AMT
541 ,0) + NVL(V_FUNC_REV_AMT
542 ,0)) - NVL(V_FUNC_CR_AMT
543 ,0) + NVL(V_FUNC_RCP_W_OFF
544 ,0) + (NVL(V_FUNC_ADJ_AMT
545 ,0)) - NVL(V_FUNC_DISC_CR_AMT
546 ,0) - NVL(V_FUNC_GAIN_AMT
547 ,0) + NVL(V_FUNC_LOSS_AMT
548 ,0));
549 V_TRAN_TOT_AMT := ((NVL(V_TR_DR_AMT
550 ,0)) + NVL(V_CRE_MEMO_AMT
551 ,0) + (NVL(V_TR_REV_AMT
552 ,0)) - NVL(V_TR_CR_AMT
553 ,0) + NVL(V_TRAN_RCP_W_OFF
554 ,0) - ABS(NVL(V_TR_ADJ_AMT
555 ,0)) - NVL(V_TR_DISC_CR_AMT
556 ,0));
557 IF NVL(V_FUNC_TOT_AMT
558 ,0) < 0 THEN
559 FUNC_CLOSING_BAL_CR := ABS(V_FUNC_TOT_AMT);
560 FUNC_CLOSING_BAL_DR := 0;
561 ELSE
562 FUNC_CLOSING_BAL_CR := 0;
563 FUNC_CLOSING_BAL_DR := ABS(V_FUNC_TOT_AMT);
564 END IF;
565 IF NVL(V_TRAN_TOT_AMT
566 ,0) < 0 THEN
567 TRAN_CLOSING_BAL_CR := ABS(NVL(V_TRAN_TOT_AMT
568 ,0));
569 RETURN (0);
570 ELSE
571 TRAN_CLOSING_BAL_CR := 0;
572 RETURN (ABS(NVL(V_TRAN_TOT_AMT
573 ,0)));
574 END IF;
575 END CF_1FORMULA;
576 FUNCTION CF_1FORMULA0031(CUSTOMER_TRX_ID_1 IN NUMBER) RETURN CHAR IS
577 CURSOR GET_ORDER_DETAILS IS
578 SELECT
579 INTERFACE_HEADER_ATTRIBUTE1
580 FROM
581 RA_CUSTOMER_TRX_ALL
582 WHERE CUSTOMER_TRX_ID = CUSTOMER_TRX_ID_1
583 AND NVL(CREATED_FROM
584 ,'###') = 'RAXTRX';
585 V_ORDER_NUMBER VARCHAR2(30);
586 BEGIN
587 IF CUSTOMER_TRX_ID_1 IS NOT NULL AND CUSTOMER_TRX_ID_1 <> 0 THEN
588 OPEN GET_ORDER_DETAILS;
589 FETCH GET_ORDER_DETAILS
590 INTO V_ORDER_NUMBER;
591 IF GET_ORDER_DETAILS%NOTFOUND THEN
592 V_ORDER_NUMBER := ' ';
593 END IF;
594 CLOSE GET_ORDER_DETAILS;
595 END IF;
596 RETURN V_ORDER_NUMBER;
597 END CF_1FORMULA0031;
598 FUNCTION ACCOUNT_CODEFORMULA(ACCOUNT_ID IN NUMBER) RETURN CHAR IS
599 V_ACCOUNT_CODE VARCHAR2(1000);
600 BEGIN
601 JAI_CMN_GL_PKG.GET_ACCOUNT_NUMBER(P_CHART_OF_ACCOUNTS_ID
602 ,ACCOUNT_ID
603 ,V_ACCOUNT_CODE);
604 IF V_ACCOUNT_CODE IS NOT NULL THEN
605 RETURN (V_ACCOUNT_CODE);
606 ELSE
607 RETURN ('N.A.');
608 END IF;
609 END ACCOUNT_CODEFORMULA;
610 FUNCTION CF_1FORMULA0034(CUSTOMER_ID IN NUMBER) RETURN CHAR IS
611 CURSOR GET_CREDIT_RATING IS
612 SELECT
613 DISTINCT
614 CPC.NAME PROFILE_CLASS_NAME
615 FROM
616 HZ_CUST_ACCOUNTS CUST_ACCT,
617 HZ_PARTIES CUST_PARTY,
618 HZ_CUST_PROFILE_CLASSES CPC,
619 HZ_CUSTOMER_PROFILES CP
620 WHERE CUST_ACCT.PARTY_ID = CUST_PARTY.PARTY_ID
621 AND CP.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
622 AND CP.PROFILE_CLASS_ID = CPC.PROFILE_CLASS_ID (+)
623 AND CUST_ACCT.CUST_ACCOUNT_ID = CUSTOMER_ID;
624 V_CREDIT_RATING VARCHAR2(30);
625 BEGIN
626 OPEN GET_CREDIT_RATING;
627 FETCH GET_CREDIT_RATING
628 INTO V_CREDIT_RATING;
629 IF GET_CREDIT_RATING%NOTFOUND THEN
630 V_CREDIT_RATING := ' ';
631 END IF;
632 CLOSE GET_CREDIT_RATING;
633 RETURN V_CREDIT_RATING;
634 END CF_1FORMULA0034;
635 FUNCTION CF_1FORMULA0037(TYPE IN VARCHAR2
636 ,AMOUNT IN NUMBER
637 ,AMOUNT_OTHER_CURRENCY IN NUMBER
638 ,REMARKS IN VARCHAR2) RETURN NUMBER IS
639 BEGIN
640 IF NVL(TYPE
641 ,'##') in ('INV','DM','REV','DEP') AND AMOUNT > 0 THEN
642 FUNC_DR_AMT := NVL(ABS(AMOUNT_OTHER_CURRENCY)
643 ,0);
644 TRAN_CR_AMT := 0;
645 FUNC_CR_AMT := 0;
646 RETURN (NVL(ABS(AMOUNT)
647 ,0));
648 ELSIF NVL(TYPE
649 ,'##') in ('INV','DM','REV','DEP') AND AMOUNT <= 0 THEN
650 FUNC_CR_AMT := NVL(ABS(AMOUNT_OTHER_CURRENCY)
651 ,0);
652 TRAN_CR_AMT := NVL(ABS(AMOUNT)
653 ,0);
654 FUNC_DR_AMT := 0;
655 ELSIF NVL(TYPE
656 ,'##') in ('CM','REC') THEN
657 TRAN_CR_AMT := NVL(ABS(AMOUNT)
658 ,0);
659 FUNC_CR_AMT := NVL(ABS(AMOUNT_OTHER_CURRENCY)
660 ,0);
661 FUNC_DR_AMT := 0;
662 RETURN (0);
663 ELSIF NVL(TYPE
664 ,'##') in ('ADJ') AND AMOUNT <= 0 THEN
665 TRAN_CR_AMT := NVL(ABS(AMOUNT)
666 ,0);
667 FUNC_CR_AMT := NVL(ABS(AMOUNT_OTHER_CURRENCY)
668 ,0);
669 FUNC_DR_AMT := 0;
670 RETURN (0);
671 ELSIF NVL(TYPE
672 ,'##') in ('ADJ') AND AMOUNT > 0 THEN
673 FUNC_DR_AMT := NVL(ABS(AMOUNT_OTHER_CURRENCY)
674 ,0);
675 FUNC_CR_AMT := 0;
676 TRAN_CR_AMT := 0;
677 RETURN (NVL(ABS(AMOUNT)
678 ,0));
679 ELSIF NVL(TYPE
680 ,'##') in ('W/O') AND AMOUNT <= 0 THEN
681 TRAN_CR_AMT := NVL(ABS(AMOUNT)
682 ,0);
683 FUNC_CR_AMT := NVL(ABS(AMOUNT_OTHER_CURRENCY)
684 ,0);
685 FUNC_DR_AMT := 0;
686 RETURN (0);
687 ELSIF NVL(TYPE
688 ,'##') in ('W/O') AND AMOUNT > 0 THEN
689 FUNC_DR_AMT := NVL(ABS(AMOUNT_OTHER_CURRENCY)
690 ,0);
691 FUNC_CR_AMT := 0;
692 TRAN_CR_AMT := 0;
693 RETURN (NVL(ABS(AMOUNT)
694 ,0));
695 ELSIF NVL(TYPE
696 ,'##') in ('DSC') THEN
697 TRAN_CR_AMT := NVL(ABS(AMOUNT)
698 ,0);
699 FUNC_CR_AMT := NVL(ABS(AMOUNT_OTHER_CURRENCY)
700 ,0);
701 FUNC_DR_AMT := 0;
702 RETURN (0);
703 ELSIF NVL(TYPE
704 ,'##') in ('EXCH_GAIN','EXCH_LOSS') THEN
705 TRAN_CR_AMT := 0;
706 IF REMARKS = 'CR' THEN
707 FUNC_CR_AMT := 0;
708 FUNC_DR_AMT := NVL(AMOUNT_OTHER_CURRENCY
709 ,0);
710 ELSIF REMARKS = 'DR' THEN
711 FUNC_DR_AMT := 0;
712 FUNC_CR_AMT := NVL(AMOUNT_OTHER_CURRENCY
713 ,0);
714 END IF;
715 RETURN (0);
716 END IF;
717 RETURN (0);
718 END CF_1FORMULA0037;
719 FUNCTION CF_1FORMULA0040 RETURN CHAR IS
720 CURSOR GET_ORGANIZATION_NAME IS
721 SELECT
722 ORGANIZATION_NAME
723 FROM
724 ORG_ORGANIZATION_DEFINITIONS
725 WHERE ORGANIZATION_ID = P_ORG_ID;
726 CURSOR GET_LOCATION_DETAILS IS
727 SELECT
728 LOCATION_ID,
729 ADDRESS_LINE_1,
730 ADDRESS_LINE_2,
731 ADDRESS_LINE_3,
732 COUNTRY
733 FROM
734 HR_ORGANIZATION_UNITS_V
735 WHERE ORGANIZATION_ID = P_ORG_ID;
736 CURSOR GET_LOCATION_NAME(V_LOC_ID IN NUMBER) IS
737 SELECT
738 DESCRIPTION
739 FROM
740 HR_LOCATIONS
741 WHERE LOCATION_ID = V_LOC_ID;
742 V_ORG_NAME VARCHAR2(60);
743 V_LOC_ID NUMBER;
744 BEGIN
745 OPEN GET_ORGANIZATION_NAME;
746 FETCH GET_ORGANIZATION_NAME
747 INTO V_ORG_NAME;
748 CLOSE GET_ORGANIZATION_NAME;
749 OPEN GET_LOCATION_DETAILS;
750 FETCH GET_LOCATION_DETAILS
751 INTO V_LOC_ID,ADD1,ADD2,ADD3,COUNTRY;
752 CLOSE GET_LOCATION_DETAILS;
753 OPEN GET_LOCATION_NAME(V_LOC_ID);
754 FETCH GET_LOCATION_NAME
755 INTO LOC_NAME;
756 CLOSE GET_LOCATION_NAME;
757 RETURN (V_ORG_NAME);
758 END CF_1FORMULA0040;
759 FUNCTION DESCRIPTIONFORMULA(ACCOUNT_ID IN NUMBER) RETURN CHAR IS
760 CURSOR GET_APP_COLUMN_NAME(CP_ID_FLEX_CODE IN FND_SEGMENT_ATTRIBUTE_VALUES.ID_FLEX_CODE%TYPE,CP_SEG_ATT_TYPE IN FND_SEGMENT_ATTRIBUTE_VALUES.SEGMENT_ATTRIBUTE_TYPE%TYPE) IS
761 SELECT
762 DISTINCT
763 APPLICATION_COLUMN_NAME
764 FROM
765 FND_SEGMENT_ATTRIBUTE_VALUES
766 WHERE APPLICATION_ID = 101
767 AND ID_FLEX_CODE = CP_ID_FLEX_CODE
768 AND ID_FLEX_NUM = P_CHART_OF_ACCOUNTS_ID
769 AND SEGMENT_ATTRIBUTE_TYPE = CP_SEG_ATT_TYPE
770 AND ATTRIBUTE_VALUE = 'Y';
771 CURSOR FLEX_VAL_SET_ID(V_COLUMN_NAME IN VARCHAR2,CP_ID_FLEX_CODE IN FND_SEGMENT_ATTRIBUTE_VALUES.ID_FLEX_CODE%TYPE) IS
772 SELECT
773 A.FLEX_VALUE_SET_ID
774 FROM
775 FND_ID_FLEX_SEGMENTS A
776 WHERE A.APPLICATION_COLUMN_NAME = V_COLUMN_NAME
777 AND A.APPLICATION_ID = 101
778 AND A.ID_FLEX_CODE = CP_ID_FLEX_CODE
779 AND A.ID_FLEX_NUM = P_CHART_OF_ACCOUNTS_ID;
780 V_COLUMN_NAME VARCHAR2(30);
781 V_COLUMN_VALUE VARCHAR2(30);
782 V_FLEX_ID NUMBER;
783 V_DESCRIPTION VARCHAR2(100);
784 CURSOR GET_DESCRIPTION IS
785 SELECT
786 SUBSTR(DESCRIPTION
787 ,1
788 ,15)
789 FROM
790 FND_FLEX_VALUES_VL
791 WHERE FLEX_VALUE_SET_ID = V_FLEX_ID
792 AND FLEX_VALUE = V_COLUMN_VALUE;
793 BEGIN
794 OPEN GET_APP_COLUMN_NAME('GL#','GL_ACCOUNT');
795 FETCH GET_APP_COLUMN_NAME
796 INTO V_COLUMN_NAME;
797 CLOSE GET_APP_COLUMN_NAME;
798 IF V_COLUMN_NAME IS NULL THEN
799 V_COLUMN_NAME := 'SEGMENT3';
800 END IF;
801 OPEN FLEX_VAL_SET_ID(V_COLUMN_NAME,'GL#');
802 FETCH FLEX_VAL_SET_ID
803 INTO V_FLEX_ID;
804 CLOSE FLEX_VAL_SET_ID;
805 EXECUTE IMMEDIATE
806 'select ' || V_COLUMN_NAME || ' from gl_code_combinations
807 where chart_of_accounts_id = :P_CHART_OF_ACCOUNTS_ID AND code_combination_id = :account_id'
808 INTO p_column_value
809 USING P_CHART_OF_ACCOUNTS_ID,account_id ;
810 V_COLUMN_VALUE := P_COLUMN_VALUE;
811 OPEN GET_DESCRIPTION;
812 FETCH GET_DESCRIPTION
813 INTO V_DESCRIPTION;
814 CLOSE GET_DESCRIPTION;
815 RETURN (V_DESCRIPTION);
816 END DESCRIPTIONFORMULA;
817 FUNCTION CF_1FORMULA0038(FUNC_OP_BAL_TOT_DR IN NUMBER
818 ,FUNC_OP_BAL_TOT_CR IN NUMBER) RETURN NUMBER IS
819 V_BAL NUMBER := 0;
820 BEGIN
821 V_BAL := NVL(FUNC_OP_BAL_TOT_DR
822 ,0) - NVL(FUNC_OP_BAL_TOT_CR
823 ,0);
824 IF V_BAL < 0 THEN
825 OP_TOT_CR := ABS(V_BAL);
826 RETURN (0);
827 ELSE
828 OP_TOT_CR := 0;
829 RETURN (NVL(ABS(V_BAL)
830 ,0));
831 END IF;
832 END CF_1FORMULA0038;
833 FUNCTION CL_TOT_DRFORMULA(FUNC_CL_BAL_DR IN NUMBER
834 ,FUNC_CL_BAL_CR IN NUMBER) RETURN NUMBER IS
835 V_BAL NUMBER := 0;
836 BEGIN
837 V_BAL := NVL(FUNC_CL_BAL_DR
838 ,0) - NVL(FUNC_CL_BAL_CR
839 ,0);
840 IF V_BAL < 0 THEN
841 CL_TOT_CR := ABS(V_BAL);
842 RETURN (0);
843 ELSE
844 CL_TOT_CR := 0;
845 RETURN (ABS(NVL(V_BAL
846 ,0)));
847 END IF;
848 END CL_TOT_DRFORMULA;
849 FUNCTION CF_1FORMULA0057(CS_1 IN NUMBER
850 ,CS_2 IN NUMBER) RETURN NUMBER IS
851 V_NET_TOTAL NUMBER;
852 BEGIN
853 V_NET_TOTAL := NVL(NVL(CS_1
854 ,0) - NVL(CS_2
855 ,0)
856 ,0);
857 RETURN (NVL(V_NET_TOTAL
858 ,0));
859 EXCEPTION
860 WHEN NO_DATA_FOUND THEN
861 /*SRW.MESSAGE(1
862 ,'NO DATA ')*/NULL;
863 END CF_1FORMULA0057;
864 FUNCTION CF_EXCISE_INVFORMULA(CUSTOMER_TRX_ID_1 IN NUMBER) RETURN NUMBER IS
865 BEGIN
866 DECLARE
867 V_EXCISE_INV_NO VARCHAR2(100);
868 BEGIN
869 IF CUSTOMER_TRX_ID_1 <> 0 THEN
870 SELECT
871 A.EXCISE_INVOICE_NO
872 INTO V_EXCISE_INV_NO
873 FROM
874 JAI_AR_TRX_LINES A
875 WHERE CUSTOMER_TRX_ID = CUSTOMER_TRX_ID_1
876 AND A.EXCISE_INVOICE_NO IS NOT NULL
877 AND ROWNUM = 1;
878 CP_EXCISE_INV_NO := V_EXCISE_INV_NO;
879 ELSE
880 CP_EXCISE_INV_NO := '';
881 END IF;
882 EXCEPTION
883 WHEN NO_DATA_FOUND THEN
884 CP_EXCISE_INV_NO := '';
885 RETURN (0);
886 WHEN OTHERS THEN
887 CP_EXCISE_INV_NO := '';
888 RETURN (0);
889 END;
890 RETURN (0);
891 END CF_EXCISE_INVFORMULA;
892 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
893 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
894 SELECT
895 CONCURRENT_PROGRAM_ID,
896 NVL(ENABLE_TRACE
897 ,'N')
898 FROM
899 FND_CONCURRENT_REQUESTS
900 WHERE REQUEST_ID = P_REQUEST_ID;
901 CURSOR GET_AUDSID IS
902 SELECT
903 A.SID,
904 A.SERIAL#,
905 B.SPID
906 FROM
907 V$SESSION A,
908 V$PROCESS B
909 WHERE AUDSID = USERENV('SESSIONID')
910 AND A.PADDR = B.ADDR;
911 CURSOR GET_DBNAME IS
912 SELECT
913 NAME
914 FROM
915 V$DATABASE;
916 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
917 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
918 V_AUDSID NUMBER := USERENV('SESSIONID');
919 V_SID NUMBER;
920 V_SERIAL NUMBER;
921 V_SPID VARCHAR2(9);
922 V_DBNAME VARCHAR2(25);
923 BEGIN
924 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
925 P_START_DATE1 := to_Char(P_START_DATE,'DD-MM-YYYY');
926 P_END_DATE1 := to_Char(P_END_DATE,'DD-MM-YYYY');
927 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
928 /*SRW.MESSAGE(1275
929 ,'Report Version is 120.3 Last modified date is 02/09/2005')*/NULL;
930 BEGIN
931 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
932 FETCH C_PROGRAM_ID
933 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
934 CLOSE C_PROGRAM_ID;
935 /*SRW.MESSAGE(1275
936 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
937 IF V_ENABLE_TRACE = 'Y' THEN
938 OPEN GET_AUDSID;
939 FETCH GET_AUDSID
940 INTO V_SID,V_SERIAL,V_SPID;
941 CLOSE GET_AUDSID;
942 OPEN GET_DBNAME;
943 FETCH GET_DBNAME
944 INTO V_DBNAME;
945 CLOSE GET_DBNAME;
946 /*SRW.MESSAGE(1275
947 ,'TraceFile Name = ' || LOWER(V_DBNAME) || '_ora_' || V_SPID || '.trc')*/NULL;
948 EXECUTE IMMEDIATE
949 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
950 END IF;
951 RETURN (TRUE);
952 EXCEPTION
953 WHEN OTHERS THEN
954 /*SRW.MESSAGE(1275
955 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
956 END;
957 END BEFOREREPORT;
958 FUNCTION AFTERREPORT RETURN BOOLEAN IS
959 BEGIN
960 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
961 RETURN (TRUE);
962 END AFTERREPORT;
963 FUNCTION OP_TOT_CR_P RETURN NUMBER IS
964 BEGIN
965 RETURN OP_TOT_CR;
966 END OP_TOT_CR_P;
967 FUNCTION CL_TOT_CR_P RETURN NUMBER IS
968 BEGIN
969 RETURN CL_TOT_CR;
970 END CL_TOT_CR_P;
971 FUNCTION TRAN_OPEN_BAL_CR_P RETURN NUMBER IS
972 BEGIN
973 RETURN TRAN_OPEN_BAL_CR;
974 END TRAN_OPEN_BAL_CR_P;
975 FUNCTION FUNC_OPEN_BAL_DR_P RETURN NUMBER IS
976 BEGIN
977 RETURN FUNC_OPEN_BAL_DR;
978 END FUNC_OPEN_BAL_DR_P;
979 FUNCTION FUNC_OPEN_BAL_CR_P RETURN NUMBER IS
980 BEGIN
981 RETURN FUNC_OPEN_BAL_CR;
982 END FUNC_OPEN_BAL_CR_P;
983 FUNCTION CP_EXCISE_INV_NO_P RETURN VARCHAR2 IS
984 BEGIN
985 RETURN CP_EXCISE_INV_NO;
986 END CP_EXCISE_INV_NO_P;
987 FUNCTION TRAN_CR_AMT_P RETURN NUMBER IS
988 BEGIN
989 RETURN TRAN_CR_AMT;
990 END TRAN_CR_AMT_P;
991 FUNCTION FUNC_DR_AMT_P RETURN NUMBER IS
992 BEGIN
993 RETURN FUNC_DR_AMT;
994 END FUNC_DR_AMT_P;
995 FUNCTION FUNC_CR_AMT_P RETURN NUMBER IS
996 BEGIN
997 RETURN FUNC_CR_AMT;
998 END FUNC_CR_AMT_P;
999 FUNCTION TRAN_CLOSING_BAL_CR_P RETURN NUMBER IS
1000 BEGIN
1001 RETURN TRAN_CLOSING_BAL_CR;
1002 END TRAN_CLOSING_BAL_CR_P;
1003 FUNCTION FUNC_CLOSING_BAL_DR_P RETURN NUMBER IS
1004 BEGIN
1005 RETURN FUNC_CLOSING_BAL_DR;
1006 END FUNC_CLOSING_BAL_DR_P;
1007 FUNCTION FUNC_CLOSING_BAL_CR_P RETURN NUMBER IS
1008 BEGIN
1009 RETURN FUNC_CLOSING_BAL_CR;
1010 END FUNC_CLOSING_BAL_CR_P;
1011 FUNCTION ADD1_P RETURN VARCHAR2 IS
1012 BEGIN
1013 RETURN ADD1;
1014 END ADD1_P;
1015 FUNCTION ADD2_P RETURN VARCHAR2 IS
1016 BEGIN
1017 RETURN ADD2;
1018 END ADD2_P;
1019 FUNCTION ADD3_P RETURN VARCHAR2 IS
1020 BEGIN
1021 RETURN ADD3;
1022 END ADD3_P;
1023 FUNCTION COUNTRY_P RETURN VARCHAR2 IS
1024 BEGIN
1025 RETURN COUNTRY;
1026 END COUNTRY_P;
1027 FUNCTION LOC_NAME_P RETURN VARCHAR2 IS
1028 BEGIN
1029 RETURN LOC_NAME;
1030 END LOC_NAME_P;
1031 END JA_JAINARDR_XMLP_PKG;
1032
1033