[Home] [Help]
PACKAGE BODY: APPS.JA_JAINDTBR_XMLP_PKG
Source
1 PACKAGE BODY JA_JAINDTBR_XMLP_PKG AS
2 /* $Header: JAINDTBRB.pls 120.1 2007/12/25 16:17:09 dwkrishn noship $ */
3 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
4 Y VARCHAR2(15);
5 BEGIN
6 RETURN (TRUE);
7 END BEFOREPFORM;
8
9 /* FUNCTION OPEN_BAL_TRFORMULA(CUSTOMER_ID IN NUMBER
10 ,CURR_CODE IN VARCHAR2) RETURN NUMBER IS
11 LV_INV_CLASS CONSTANT AR_PAYMENT_SCHEDULES_ALL.CLASS%TYPE DEFAULT 'INV';
12 LV_DM_CLASS CONSTANT AR_PAYMENT_SCHEDULES_ALL.CLASS%TYPE DEFAULT 'DM';
13 LV_CM_CLASS CONSTANT AR_PAYMENT_SCHEDULES_ALL.CLASS%TYPE DEFAULT 'CM';
14 LV_DEP_CLASS CONSTANT AR_PAYMENT_SCHEDULES_ALL.CLASS%TYPE DEFAULT 'DEP';
15 LV_REC_ACCOUNT_CLASS CONSTANT RA_CUST_TRX_LINE_GL_DIST_ALL.ACCOUNT_CLASS%TYPE DEFAULT 'REC';
16 LV_REV_STATUS CONSTANT AR_CASH_RECEIPT_HISTORY_ALL.STATUS%TYPE DEFAULT 'REVERSED';
17 LV_ACT_STATUS CONSTANT AR_CASH_RECEIPT_HISTORY_ALL.STATUS%TYPE DEFAULT 'ACTIVITY';
18 LV_LOSS_SOURCE_TYPE CONSTANT AR_DISTRIBUTIONS_ALL.SOURCE_TYPE%TYPE DEFAULT 'EXCH_LOSS';
19 LV_GAIN_SOURCE_TYPE CONSTANT AR_DISTRIBUTIONS_ALL.SOURCE_TYPE%TYPE DEFAULT 'EXCH_GAIN';
20 CURSOR GET_DEBIT_AMOUNT IS
21 SELECT
22 SUM((B.AMOUNT)) SUM_EXT_AMOUNT,
23 SUM((B.AMOUNT) * NVL(A.EXCHANGE_RATE
24 ,1))
25 FROM
26 RA_CUSTOMER_TRX_ALL A,
27 AR_PAYMENT_SCHEDULES_ALL C,
28 RA_CUST_TRX_LINE_GL_DIST_ALL B
29 WHERE A.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
30 AND A.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
31 AND C.CLASS In ( LV_INV_CLASS , LV_DM_CLASS , LV_DEP_CLASS )
32 AND C.GL_DATE <= TRUNC(P_START_DATE)
33 AND A.INVOICE_CURRENCY_CODE = CURR_CODE
34 AND A.COMPLETE_FLAG = 'Y'
35 AND B.CUSTOMER_TRX_ID = A.CUSTOMER_TRX_ID
36 AND A.ORG_ID = NVL(P_ORGANIZATION_ID
37 ,A.ORG_ID)
38 AND B.ACCOUNT_CLASS = LV_REC_ACCOUNT_CLASS
39 AND B.LATEST_REC_FLAG = 'Y'
40 AND C.PAYMENT_SCHEDULE_ID IN (
41 SELECT
42 MIN(PAYMENT_SCHEDULE_ID)
43 FROM
44 AR_PAYMENT_SCHEDULES_ALL
45 WHERE CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID );
46 CURSOR GET_CREDIT_AMOUNT IS
47 SELECT
48 SUM(A.AMOUNT) SUM_AMOUNT,
49 SUM(A.AMOUNT * NVL(A.EXCHANGE_RATE
50 ,1.00)) SUM_AMOUNT_EXCHANGE
51 FROM
52 AR_CASH_RECEIPTS_ALL A
53 WHERE A.PAY_FROM_CUSTOMER = CUSTOMER_ID
54 AND A.ORG_ID = NVL(P_ORGANIZATION_ID
55 ,A.ORG_ID)
56 AND A.CURRENCY_CODE = CURR_CODE
57 AND EXISTS (
58 SELECT
59 1
60 FROM
61 AR_CASH_RECEIPT_HISTORY_ALL
62 WHERE CASH_RECEIPT_ID = A.CASH_RECEIPT_ID
63 AND ORG_ID = NVL(P_ORGANIZATION_ID
64 ,A.ORG_ID)
65 AND GL_DATE <= TRUNC(P_START_DATE) );
66 CURSOR GET_REVERSAL_AMOUNT IS
67 SELECT
68 SUM(A.AMOUNT) SUM_AMOUNT,
69 SUM(A.AMOUNT * NVL(A.EXCHANGE_RATE
70 ,1.00)) SUM_AMOUNT_EXCHANGE
71 FROM
72 AR_CASH_RECEIPTS_ALL A,
73 AR_CASH_RECEIPT_HISTORY_ALL B
74 WHERE A.PAY_FROM_CUSTOMER = CUSTOMER_ID
75 AND A.CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
76 AND B.GL_DATE <= TRUNC(P_START_DATE)
77 AND B.STATUS = LV_REV_STATUS
78 AND A.REVERSAL_DATE is not null
79 AND A.ORG_ID = NVL(P_ORGANIZATION_ID
80 ,A.ORG_ID)
81 AND A.CURRENCY_CODE = CURR_CODE;
82 CURSOR GET_DISCOUNT_CUR(CP_APP_TYPE IN AR_RECEIVABLE_APPLICATIONS_ALL.APPLICATION_TYPE%TYPE) IS
83 SELECT
84 NVL(SUM(ABS(NVL(D.EARNED_DISCOUNT_TAKEN
85 ,0)))
86 ,0) SUM_AMOUNT,
87 NVL(SUM(ABS(NVL(D.ACCTD_EARNED_DISCOUNT_TAKEN
88 ,0)))
89 ,0) SUM_AMOUNT_EXCHANGE
90 FROM
91 RA_CUSTOMER_TRX_ALL B,
92 AR_RECEIVABLE_APPLICATIONS_ALL D
93 WHERE B.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
94 AND B.COMPLETE_FLAG = 'Y'
95 AND TRUNC(D.GL_DATE) <= TRUNC(P_START_DATE)
96 AND D.APPLIED_CUSTOMER_TRX_ID = B.CUSTOMER_TRX_ID
97 AND B.INVOICE_CURRENCY_CODE = CURR_CODE
98 AND D.EARNED_DISCOUNT_TAKEN is not null
99 AND D.EARNED_DISCOUNT_TAKEN <> 0
100 AND B.ORG_ID = NVL(P_ORGANIZATION_ID
101 ,B.ORG_ID)
102 AND D.APPLICATION_TYPE = CP_APP_TYPE
103 AND D.DISPLAY = 'Y';
104 CURSOR GET_ADJUSTMENT_AMOUNT IS
105 SELECT
106 SUM(A.AMOUNT),
107 SUM(A.AMOUNT * NVL(B.EXCHANGE_RATE
108 ,1.00)) SUM_AMOUNT_EXCHANGE
109 FROM
110 AR_ADJUSTMENTS_ALL A,
111 AR_CASH_RECEIPTS_ALL B
112 WHERE A.ASSOCIATED_CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
113 AND B.PAY_FROM_CUSTOMER = CUSTOMER_ID
114 AND A.GL_DATE <= TRUNC(P_START_DATE)
115 AND B.ORG_ID = NVL(P_ORGANIZATION_ID
116 ,B.ORG_ID)
117 AND B.CURRENCY_CODE = CURR_CODE;
118 CURSOR C_GET_NONFC_ADJ_AMOUNT IS
119 SELECT
120 SUM(B.AMOUNT),
121 SUM(B.AMOUNT * NVL(C.EXCHANGE_RATE
122 ,1.00)) SUM_AMOUNT_EXCHANGE
123 FROM
124 AR_ADJUSTMENTS_ALL B,
125 RA_CUSTOMER_TRX_ALL C,
126 AR_PAYMENT_SCHEDULES_ALL D,
127 GL_CODE_COMBINATIONS E
128 WHERE B.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
129 AND C.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
130 AND B.GL_DATE <= TRUNC(P_START_DATE)
131 AND E.CODE_COMBINATION_ID = B.CODE_COMBINATION_ID
132 AND B.PAYMENT_SCHEDULE_ID = D.PAYMENT_SCHEDULE_ID
133 AND B.CUSTOMER_TRX_ID = D.CUSTOMER_TRX_ID
134 AND B.STATUS = 'A'
135 AND C.ORG_ID = NVL(P_ORGANIZATION_ID
136 ,B.ORG_ID)
137 AND C.INVOICE_CURRENCY_CODE = CURR_CODE;
138 CURSOR GET_EXCHANGE_GAINLOSS_CR IS
139 SELECT
140 SUM(E.AMOUNT_CR) SUM_AMOUNT,
141 SUM(E.ACCTD_AMOUNT_CR) SUM_EXCHANGE_AMOUNT
142 FROM
143 RA_CUSTOMER_TRX_ALL B,
144 AR_CASH_RECEIPTS_ALL C,
145 AR_RECEIVABLE_APPLICATIONS_ALL D,
146 AR_DISTRIBUTIONS_ALL E
147 WHERE B.CUSTOMER_TRX_ID = D.APPLIED_CUSTOMER_TRX_ID
148 AND C.CASH_RECEIPT_ID = D.CASH_RECEIPT_ID
149 AND E.SOURCE_ID = D.RECEIVABLE_APPLICATION_ID
150 AND B.ORG_ID = NVL(P_ORGANIZATION_ID
151 ,B.ORG_ID)
152 AND E.SOURCE_TYPE IN ( LV_LOSS_SOURCE_TYPE , LV_GAIN_SOURCE_TYPE )
153 AND B.INVOICE_CURRENCY_CODE = CURR_CODE
154 AND B.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
155 AND TRUNC(D.GL_DATE) <= TRUNC(P_START_DATE);
156 CURSOR GET_EXCHANGE_GAINLOSS_DR IS
157 SELECT
158 SUM(E.AMOUNT_DR) SUM_AMOUNT,
159 SUM(E.ACCTD_AMOUNT_DR) SUM_EXCHANGE_AMOUNT
160 FROM
161 RA_CUSTOMER_TRX_ALL B,
162 AR_CASH_RECEIPTS_ALL C,
163 AR_RECEIVABLE_APPLICATIONS_ALL D,
164 AR_DISTRIBUTIONS_ALL E
165 WHERE B.CUSTOMER_TRX_ID = D.APPLIED_CUSTOMER_TRX_ID
166 AND C.CASH_RECEIPT_ID = D.CASH_RECEIPT_ID
167 AND E.SOURCE_ID = D.RECEIVABLE_APPLICATION_ID
168 AND B.ORG_ID = NVL(P_ORGANIZATION_ID
169 ,B.ORG_ID)
170 AND B.INVOICE_CURRENCY_CODE = CURR_CODE
171 AND B.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
172 AND TRUNC(D.GL_DATE) <= TRUNC(P_START_DATE)
173 AND E.SOURCE_TYPE IN ( LV_LOSS_SOURCE_TYPE , LV_GAIN_SOURCE_TYPE );
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_CRE_MEMO_AMT NUMBER;
183 V_CRE_MEMO_FUNC_AMT NUMBER;
184 V_TR_ADJ_AMT NUMBER;
185 V_FUNC_ADJ_AMT NUMBER;
186 V_EXCH_GAIN_AMT NUMBER;
187 V_EXCH_LOSS_AMT NUMBER;
188 V_TR_NONFC_ADJ_AMOUNT NUMBER;
189 V_FUNC_NONFC_ADJ_AMOUNT NUMBER;
190 V_TR_DISC_CR_AMT NUMBER;
191 V_FUNC_DISC_CR_AMT NUMBER;
192 V_TRAN_RCP_W_OFF NUMBER;
193 V_FUNC_RCP_W_OFF NUMBER;
194 V_EXCH_LOSS_FUNC_AMT NUMBER;
195 V_EXCH_GAIN_FUNC_AMT NUMBER;
196 CURSOR C_RECEIPT_W_OFF IS
197 SELECT
198 SUM(C.AMOUNT_APPLIED) SUM_AMOUNT,
199 SUM(C.AMOUNT_APPLIED * NVL(A.EXCHANGE_RATE
200 ,1.00)) SUM_AMOUNT_EXCHANGE
201 FROM
202 AR_CASH_RECEIPTS_ALL A,
203 AR_CASH_RECEIPT_HISTORY_ALL B,
204 AR_RECEIVABLE_APPLICATIONS_ALL C
205 WHERE A.PAY_FROM_CUSTOMER = CUSTOMER_ID
206 AND TRUNC(B.GL_DATE) <= TRUNC(P_START_DATE)
207 AND A.CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
208 AND A.CASH_RECEIPT_ID = C.CASH_RECEIPT_ID
209 AND C.CASH_RECEIPT_HISTORY_ID = B.CASH_RECEIPT_HISTORY_ID
210 AND C.APPLIED_PAYMENT_SCHEDULE_ID = - 3
211 AND C.STATUS = LV_ACT_STATUS
212 AND A.CURRENCY_CODE = CURR_CODE
213 AND B.REVERSAL_GL_DATE IS NULL
214 AND B.CURRENT_RECORD_FLAG = 'Y'
215 AND A.ORG_ID = NVL(P_ORGANIZATION_ID
216 ,A.ORG_ID)
217 AND not exists (
218 SELECT
219 1
220 FROM
221 AR_CASH_RECEIPT_HISTORY_ALL
222 WHERE CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
223 AND STATUS = LV_REV_STATUS );
224 BEGIN
225 SELECT
226 SUM((B.AMOUNT)) SUM_EXT_AMOUNT,
227 SUM((B.AMOUNT) * NVL(A.EXCHANGE_RATE
228 ,1))
229 INTO V_CRE_MEMO_AMT,V_CRE_MEMO_FUNC_AMT
230 FROM
231 RA_CUSTOMER_TRX_ALL A,
232 AR_PAYMENT_SCHEDULES_ALL C,
233 RA_CUST_TRX_LINE_GL_DIST_ALL B
234 WHERE A.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
235 AND A.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
236 AND C.CLASS In ( LV_CM_CLASS )
237 AND C.GL_DATE <= TRUNC(P_START_DATE)
238 AND A.INVOICE_CURRENCY_CODE = CURR_CODE
239 AND A.COMPLETE_FLAG = 'Y'
240 AND B.CUSTOMER_TRX_ID = A.CUSTOMER_TRX_ID
241 AND A.ORG_ID = NVL(P_ORGANIZATION_ID
242 ,A.ORG_ID)
243 AND B.ACCOUNT_CLASS = LV_REC_ACCOUNT_CLASS
244 AND C.PAYMENT_SCHEDULE_ID in (
245 SELECT
246 MIN(PAYMENT_SCHEDULE_ID)
247 FROM
248 AR_PAYMENT_SCHEDULES_ALL
249 WHERE CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID );
250 OPEN GET_DEBIT_AMOUNT;
251 FETCH GET_DEBIT_AMOUNT
252 INTO V_TR_DR_AMT,V_FUNC_DR_AMT;
253 CLOSE GET_DEBIT_AMOUNT;
254 OPEN GET_CREDIT_AMOUNT;
255 FETCH GET_CREDIT_AMOUNT
256 INTO V_TR_CR_AMT,V_FUNC_CR_AMT;
257 CLOSE GET_CREDIT_AMOUNT;
258 OPEN GET_REVERSAL_AMOUNT;
259 FETCH GET_REVERSAL_AMOUNT
260 INTO V_TR_REV_AMT,V_FUNC_REV_AMT;
261 CLOSE GET_REVERSAL_AMOUNT;
262 OPEN GET_ADJUSTMENT_AMOUNT;
263 FETCH GET_ADJUSTMENT_AMOUNT
264 INTO V_TR_ADJ_AMT,V_FUNC_ADJ_AMT;
265 CLOSE GET_ADJUSTMENT_AMOUNT;
266 OPEN C_GET_NONFC_ADJ_AMOUNT;
267 FETCH C_GET_NONFC_ADJ_AMOUNT
268 INTO V_TR_NONFC_ADJ_AMOUNT,V_FUNC_NONFC_ADJ_AMOUNT;
269 CLOSE C_GET_NONFC_ADJ_AMOUNT;
270 OPEN GET_EXCHANGE_GAINLOSS_CR;
271 FETCH GET_EXCHANGE_GAINLOSS_CR
272 INTO V_EXCH_GAIN_AMT,V_EXCH_GAIN_FUNC_AMT;
273 CLOSE GET_EXCHANGE_GAINLOSS_CR;
274 OPEN GET_EXCHANGE_GAINLOSS_DR;
275 FETCH GET_EXCHANGE_GAINLOSS_DR
276 INTO V_EXCH_LOSS_AMT,V_EXCH_LOSS_FUNC_AMT;
277 CLOSE GET_EXCHANGE_GAINLOSS_DR;
278 OPEN GET_DISCOUNT_CUR('CASH');
279 FETCH GET_DISCOUNT_CUR
280 INTO V_TR_DISC_CR_AMT,V_FUNC_DISC_CR_AMT;
281 CLOSE GET_DISCOUNT_CUR;
282 OPEN C_RECEIPT_W_OFF;
283 FETCH C_RECEIPT_W_OFF
284 INTO V_TRAN_RCP_W_OFF,V_FUNC_RCP_W_OFF;
285 CLOSE C_RECEIPT_W_OFF;
286 FUNC_OPEN_BAL := (NVL(V_FUNC_DR_AMT
287 ,0) + NVL(V_FUNC_REV_AMT
288 ,0)) + NVL(V_CRE_MEMO_FUNC_AMT
289 ,0) - NVL(V_FUNC_CR_AMT
290 ,0) - NVL(V_FUNC_DISC_CR_AMT
291 ,0) + NVL(V_FUNC_RCP_W_OFF
292 ,0) + NVL(V_EXCH_GAIN_FUNC_AMT
293 ,0) - NVL(V_EXCH_LOSS_FUNC_AMT
294 ,0) - ABS(NVL(V_FUNC_NONFC_ADJ_AMOUNT
295 ,0));
296 V_TRAN_TOT_AMT := (NVL(V_TR_DR_AMT
297 ,0) + NVL(V_TR_REV_AMT
298 ,0)) + NVL(V_CRE_MEMO_AMT
299 ,0) - NVL(V_TR_DISC_CR_AMT
300 ,0) - (NVL(V_TR_CR_AMT
301 ,0)) + NVL(V_TRAN_RCP_W_OFF
302 ,0) - ABS(NVL(V_TR_NONFC_ADJ_AMOUNT
303 ,0));
304 RETURN (NVL(V_TRAN_TOT_AMT
305 ,0));
306 END OPEN_BAL_TRFORMULA;
307 */
308 function open_bal_trFormula (P_CUSTOMER_ID IN NUMBER
309 ,P_CURR_CODE IN VARCHAR2) RETURN NUMBER IS
310
311 lv_inv_class constant ar_payment_schedules_all.class%type:= 'INV'; --rchandan for bug#4428980
312 lv_dm_class constant ar_payment_schedules_all.class%type:= 'DM'; --rchandan for bug#4428980
313 lv_cm_class constant ar_payment_schedules_all.class%type:= 'CM'; --rchandan for bug#4428980
314 lv_dep_class constant ar_payment_schedules_all.class%type:= 'DEP'; --rchandan for bug#4428980
315 lv_rec_account_class CONSTANT ra_cust_trx_line_gl_dist_all.account_class%TYPE := 'REC'; --rchandan for bug#4428980
316 lv_rev_status CONSTANT ar_cash_receipt_history_all.status%TYPE := 'REVERSED'; --rchandan for bug#4428980
317 lv_act_status CONSTANT ar_cash_receipt_history_all.status%TYPE := 'ACTIVITY'; --rchandan for bug#4428980
318 lv_loss_source_Type CONSTANT ar_distributions_all.source_Type%TYPE := 'EXCH_LOSS'; --rchandan for bug#4428980
319 lv_gain_source_Type CONSTANT ar_distributions_all.source_Type%TYPE := 'EXCH_GAIN' ; --rchandan for bug#4428980
320
321 Cursor Get_debit_amount IS
322 Select
323 sum((b.amount)) sum_ext_amount,
324 sum((b.amount) * NVL(a.exchange_rate,1))
325 From
326 ra_customer_trx_all A,
327 ar_payment_schedules_all C,
328 ra_cust_trx_line_gl_dist_all B
329 Where
330 a.bill_to_customer_id = P_CUSTOMER_ID
331 AND a.customer_trx_id = c.customer_trx_id
332 AND c.class In(lv_inv_class,lv_dm_class,lv_dep_class)--rchandan for bug#4428980
333 --AND trunc(a.trx_date) <= trunc( p_start_date)
334 AND c.gl_date <= trunc( p_start_date)
335 AND a.invoice_currency_code = P_CURR_CODE
336 AND a.complete_flag = 'Y'
337 AND b.customer_trx_id = a.customer_trx_id
338 AND a.org_id = NVL( P_ORGANIZATION_ID, a.org_id)
339 AND b.account_class = lv_rec_account_class--rchandan for bug#4428980
340 and b.latest_rec_flag = 'Y'
341 AND c.Payment_schedule_id
342 IN (SELECT MIN(PAYMENT_SCHEDULE_ID)
343 FROM AR_PAYMENT_SCHEDULES_ALL
344 WHERE CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
345 )
346 ;
347
348 Cursor Get_credit_amount IS
349 Select
350 sum(a.amount) sum_amount,
351 sum(a.amount * NVL(a.exchange_rate,1.00)) sum_amount_exchange
352 From
353 ar_cash_receipts_all A
354 Where
355 a.pay_from_customer = P_CUSTOMER_ID
356 AND a.org_id = NVL( P_ORGANIZATION_ID, a.org_id)
357 AND a.currency_code = P_CURR_CODE
358 --Added the below by Sanjikum for Bug #3962497
359 AND EXISTS ( SELECT 1
360 FROM ar_cash_receipt_history_all
361 WHERE cash_receipt_id = a.cash_receipt_id
362 AND org_id = NVL( P_ORGANIZATION_ID, a.org_id)
363 AND gl_date <= trunc( p_start_date)
364 );
365
366 Cursor get_reversal_amount IS
367 Select
368 sum(a.amount) sum_amount,
369 sum(a.amount * NVL(a.exchange_rate,1.00)) sum_amount_exchange
370 From
371 ar_cash_receipts_all A ,
372 ar_cash_receipt_history_all B
373 Where
374 a.pay_from_customer = P_CUSTOMER_ID
375 and a.cash_receipt_id = b.cash_receipt_id
376 AND b.gl_date <= trunc( p_start_date)
377 --and b.current_record_flag = 'Y' --Commented by Sanjikum for Bug #3962497
378 AND b.status = lv_rev_status --Added by Sanjikum for Bug #3962497--rchandan for bug#4428980
379 and a.reversal_date is not null
380 AND a.org_id = NVL( P_ORGANIZATION_ID, a.org_id) -- added by sriram
381 AND a.currency_code = P_CURR_CODE;
382
383
384
385 CURSOR Get_Discount_Cur(cp_app_type ar_receivable_applications_all.application_type%type) is--rchandan for bug#4428980
386 Select
387 nvl(sum(abs(NVL(d.earned_discount_taken,0))),0) sum_amount,
388 nvl(sum(abs(NVL(d.ACCTD_EARNED_DISCOUNT_TAKEN,0))),0) sum_amount_exchange
389 From ra_customer_trx_ALL B,
390 ar_receivable_applications_all d
391 Where
392 b.bill_to_customer_id = P_CUSTOMER_ID
393 AND b.complete_flag = 'Y'
394 AND trunc(d.GL_DATE) <= trunc( p_start_date)
395 AND d.applied_customer_trx_id = b.customer_trx_id
396 AND b.invoice_currency_code = P_CURR_CODE
397 AND d.earned_discount_taken is not null
398 and d.earned_discount_taken <> 0
399 AND B.org_id = nvl( P_ORGANIZATION_ID ,b.org_id)
400 and d.application_type = cp_app_type --rchandan for bug#4428980
401 and d.display = 'Y'
402 ;
403
404 Cursor get_adjustment_amount IS
405 SELECT SUM(A.amount),
406 SUM(A.amount * NVL(b.exchange_rate,1.00)) sum_amount_exchange
407 FROM ar_adjustments_all A,
408 ar_cash_receipts_all b
409 WHERE A.associated_cash_receipt_id = b.cash_receipt_id
410 and b.pay_from_customer = P_CUSTOMER_ID
411 --and trunc(a.apply_date) <= trunc( p_start_date)
412 and A.gl_date <= trunc( p_start_date)
413 AND b.org_id = NVL( P_ORGANIZATION_ID, b.org_id)
414 AND b.currency_code = P_CURR_CODE;
415
416
417 cursor c_get_nonfc_adj_amount is
418 select sum(b.amount),
419 sum(b.amount * NVL(c.exchange_rate,1.00)) sum_amount_exchange
420 FROM ar_adjustments_all b,
421 ra_customer_trx_all c,
422 ar_payment_schedules_all d,
423 gl_code_combinations e
424 WHERE
425 b.customer_trx_id = c.customer_trx_id
426 and c.bill_to_customer_id = P_CUSTOMER_ID
427 and b.gl_date <= trunc( p_start_date)
428 and e.code_combination_id = b.code_combination_id
429 and b.payment_schedule_id = d.payment_schedule_id
430 and b.customer_trx_id = d.customer_trx_id
431 and b.status = 'A'
432 and c.org_id = NVL( P_ORGANIZATION_ID, b.org_id)
433 and c.invoice_currency_code = P_CURR_CODE;
434
435
436
437 --Cursor get_exchange_gain_amount is
438 Cursor get_exchange_gainloss_cr is
439 SELECT
440 sum(e.amount_cr) sum_amount ,
441 sum(e.acctd_amount_cr) sum_exchange_amount
442 FROM ra_customer_trx_all b ,
443 ar_cash_receipts_all c,
444 ar_receivable_applications_all d,
445 ar_distributions_all e
446 WHERE b.customer_trx_id = d.APPLIED_CUSTOMER_TRX_ID
447 AND c.cash_receipt_id = d.cash_receipt_id
448 AND e.SOURCE_ID = d.receivable_application_id
449 AND b.org_id = nvl( p_organization_id,b.org_id)
450 AND e.source_Type IN (lv_loss_source_Type, lv_gain_source_Type)--rchandan for bug#4428980
451 AND b.invoice_currency_code = P_CURR_CODE
452 AND b.BILL_TO_CUSTOMER_ID = P_CUSTOMER_ID
453 AND TRUNC(d.gl_date) <= trunc( p_start_date);
454
455 --Cursor get_exchange_loss_amount is
456 Cursor get_exchange_gainloss_dr is
457 SELECT
458 sum(e.amount_dr) sum_amount ,
459 sum(e.acctd_amount_dr) sum_exchange_amount
460 FROM ra_customer_trx_all b ,
461 ar_cash_receipts_all c ,
462 ar_receivable_applications_all d ,
463 ar_distributions_all e
464 WHERE
465 b.customer_trx_id = d.APPLIED_CUSTOMER_TRX_ID
466 AND c.cash_receipt_id = d.cash_receipt_id
467 AND e.SOURCE_ID = d.receivable_application_id
468 AND b.org_id = NVL( p_organization_id,b.org_id)
469 AND b.invoice_currency_code = P_CURR_CODE
470 AND b.BILL_TO_CUSTOMER_ID = P_CUSTOMER_ID
471 AND TRUNC(d.gl_date) <= trunc( p_start_date)
472 AND e.source_Type IN (lv_loss_source_Type, lv_gain_source_Type );--rchandan for bug#4428980
473
474 ----------
475
476
477
478 v_tr_dr_amt Number;
479 v_func_dr_amt Number;
480 v_tr_cr_amt Number;
481 v_func_cr_amt Number;
482 v_tr_rev_amt Number;
483 v_func_rev_amt Number;
484 v_tran_tot_amt Number;
485 v_func_tot_amt Number;
486 v_cre_memo_amt Number;
487 v_cre_memo_func_amt Number;
488 v_tr_adj_amt Number;
489 v_func_adj_amt Number;
490 V_exch_gain_amt Number;
491 V_exch_loss_amt Number;
492 v_tr_nonfc_adj_amount Number;
493 v_func_nonfc_adj_amount Number;
494 v_tr_disc_cr_amt Number;
495 v_func_disc_cr_amt Number;
496 v_tran_rcp_w_off Number;
497 v_func_rcp_w_off Number;
498 v_exch_loss_func_amt Number;
499 v_exch_gain_func_amt Number;
500
501
502
503 Cursor c_receipt_w_off IS
504 Select
505 sum(c.amount_applied) sum_amount,
506 sum(c.amount_applied * NVL(a.exchange_rate,1.00)) sum_amount_exchange
507 From
508 ar_cash_receipts_all A,
509 ar_cash_receipt_history_all B,
510 ar_receivable_applications_all c
511 Where
512 a.pay_from_customer = P_CUSTOMER_ID
513 AND trunc(b.gl_date) <= trunc( p_start_date)
514 AND a.cash_receipt_id = b.cash_receipt_id
515 and a.cash_receipt_id = c .cash_receipt_id
516 and c.cash_receipt_history_id = b.cash_receipt_history_id
517 and c.applied_payment_schedule_id = -3
518 and c.status = lv_act_status--rchandan for bug#4428980
519 AND a.currency_code = P_CURR_CODE
520 AND B.REVERSAL_GL_DATE IS NULL
521 AND b.current_record_flag = 'Y'
522 AND a.org_id=NVL( P_ORGANIZATION_ID, a.org_id)
523 and not exists -- writing this query coz when a receipt is reversed , its write off details should not be shown
524 (select 1
525 from ar_cash_receipt_history_all
526 where cash_receipt_id = b.cash_receipt_id
527 and status = lv_rev_status--rchandan for bug#4428980
528 )
529 ;
530
531
532 begin
533 Select
534 sum((b.amount)) sum_ext_amount,
535 sum((b.amount) * NVL(a.exchange_rate,1))
536 Into v_cre_memo_amt,
537 v_cre_memo_func_amt
538 From
539 ra_customer_trx_all A,
540 ar_payment_schedules_all C,
541 ra_cust_trx_line_gl_dist_all B
542 Where
543 a.bill_to_customer_id = P_CUSTOMER_ID
544 AND a.customer_trx_id = c.customer_trx_id
545 AND c.class In(lv_cm_class)--rchandan for bug#4428980
546 --AND trunc(a.trx_date) <= trunc( p_start_date)
547 and c.gl_date <= trunc( p_start_date)
548 AND a.invoice_currency_code = P_CURR_CODE
549 AND a.complete_flag = 'Y'
550 AND b.customer_trx_id = a.customer_trx_id
551 AND a.org_id = NVL( P_ORGANIZATION_ID, a.org_id) -- added by sriram
552 AND b.account_class = lv_rec_account_class--rchandan for bug#4428980
553 AND c.payment_schedule_id in
554 ( select min(payment_schedule_id)
555 from ar_payment_schedules_all
556 where customer_trx_id = c.customer_trx_id
557 );
558
559 OPEN get_debit_amount;
560 FETCH get_debit_amount INTO v_tr_dr_amt, v_func_dr_amt;
561 CLOSE get_debit_amount;
562
563 OPEN get_credit_amount;
564 FETCH get_credit_amount INTO v_tr_cr_amt, v_func_cr_amt;
565 CLOSE get_credit_amount;
566
567 OPEN get_reversal_amount;
568 FETCH get_reversal_amount INTO v_tr_rev_amt, v_func_rev_amt;
569 CLOSE get_reversal_amount;
570
571
572 OPEN get_adjustment_amount;
573 FETCH get_adjustment_amount INTO v_tr_adj_amt, v_func_adj_amt;
574 CLOSE get_adjustment_amount;
575
576
577 open c_get_nonfc_adj_amount;
578 fetch c_get_nonfc_adj_amount into v_tr_nonfc_adj_amount,v_func_nonfc_adj_amount;
579 close c_get_nonfc_adj_amount;
580
581 Open get_exchange_gainloss_cr;
582 fetch get_exchange_gainloss_cr into v_exch_gain_amt , v_exch_gain_func_amt ;
583 Close get_exchange_gainloss_cr;
584
585 Open get_exchange_gainloss_dr;
586 fetch get_exchange_gainloss_dr into v_exch_loss_amt, v_exch_loss_func_amt;
587 Close get_exchange_gainloss_dr;
588
589 OPEN Get_Discount_Cur('CASH') ;--rchandan for bug#4428980
590 FETCH Get_Discount_Cur into v_tr_disc_cr_amt, v_func_disc_cr_amt ;
591 CLOSE Get_Discount_Cur ;
592
593 open c_receipt_w_off;
594 fetch c_receipt_w_off into v_tran_rcp_w_off,v_func_rcp_w_off;
595 close c_receipt_w_off;
596
597
598 func_open_bal := (NVL(v_func_dr_amt,0)
599 + NVL(v_func_rev_amt,0))
600 + nvl(v_cre_memo_func_amt,0)
601 - NVL(v_func_cr_amt,0)
602 - nvl(v_func_disc_cr_amt,0)
603 + nvl(v_func_rcp_w_off,0)
604 + nvl(v_exch_gain_func_amt,0)
605 - nvl(v_exch_loss_func_amt,0)
606 - abs(nvl(v_func_nonfc_adj_amount,0)
607 );
608
609 v_tran_tot_amt := ( NVL(v_tr_dr_amt,0)
610 + NVL(v_tr_rev_amt,0))
611 + nvl(v_cre_memo_amt,0)
612 - nvl(v_tr_disc_cr_amt,0)
613 -(NVL(v_tr_cr_amt,0))
614 + nvl(v_tran_rcp_w_off,0)
615 - abs(nvl(v_tr_nonfc_adj_amount,0));
616
617
618 Return(NVL(v_tran_tot_amt,0));
619
620 End;
621 FUNCTION FUNC_OPEN_BALFORMULA RETURN NUMBER IS
622 BEGIN
623 RETURN NULL;
624 END FUNC_OPEN_BALFORMULA;
625
626 FUNCTION CF_1FORMULA0040 RETURN VARCHAR2 IS
627 CURSOR GET_ORGANIZATION_NAME IS
628 SELECT
629 ORGANIZATION_NAME
630 FROM
631 ORG_ORGANIZATION_DEFINITIONS
632 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
633 CURSOR GET_LOCATION_DETAILS IS
634 SELECT
635 LOCATION_ID,
636 ADDRESS_LINE_1,
637 ADDRESS_LINE_2,
638 ADDRESS_LINE_3,
639 COUNTRY
640 FROM
641 HR_ORGANIZATION_UNITS_V
642 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
643 CURSOR GET_LOCATION_NAME(V_LOC_ID IN NUMBER) IS
644 SELECT
645 DESCRIPTION
646 FROM
647 HR_LOCATIONS
648 WHERE LOCATION_ID = V_LOC_ID;
649 V_ORG_NAME VARCHAR2(60);
650 V_LOC_ID NUMBER;
651 BEGIN
652 OPEN GET_ORGANIZATION_NAME;
653 FETCH GET_ORGANIZATION_NAME
654 INTO V_ORG_NAME;
655 CLOSE GET_ORGANIZATION_NAME;
656 OPEN GET_LOCATION_DETAILS;
657 FETCH GET_LOCATION_DETAILS
658 INTO V_LOC_ID,ADD1,ADD2,ADD3,COUNTRY;
659 CLOSE GET_LOCATION_DETAILS;
660 OPEN GET_LOCATION_NAME(V_LOC_ID);
661 FETCH GET_LOCATION_NAME
662 INTO LOC_NAME;
663 CLOSE GET_LOCATION_NAME;
664 RETURN (V_ORG_NAME);
665 END CF_1FORMULA0040;
666
667 FUNCTION P_CUSTOMER_TYPEVALIDTRIGGER RETURN BOOLEAN IS
668 BEGIN
669 RETURN (TRUE);
670 END P_CUSTOMER_TYPEVALIDTRIGGER;
671
672 FUNCTION P_CUSTOMER_IDVALIDTRIGGER RETURN BOOLEAN IS
673 BEGIN
674 RETURN (TRUE);
675 END P_CUSTOMER_IDVALIDTRIGGER;
676
677 FUNCTION P_CUSTOMER_ID2VALIDTRIGGER RETURN BOOLEAN IS
678 BEGIN
679 RETURN (TRUE);
680 END P_CUSTOMER_ID2VALIDTRIGGER;
681
682 FUNCTION AFTERPFORM RETURN BOOLEAN IS
683 BEGIN
684 /*SRW.MESSAGE(1001
685 ,' - after Param form With P_Organization_id = ' || P_ORGANIZATION_ID)*/NULL;
686 /*SRW.MESSAGE(1002
687 ,' - after Param form With P_customer_id = ' || P_CUSTOMER_ID)*/NULL;
688 /*SRW.MESSAGE(1003
689 ,' - after Param form With P_customer_id2 = ' || P_CUSTOMER_ID2)*/NULL;
690 /*SRW.MESSAGE(1004
691 ,' - after Param form With P_customer_type = ' || P_CUSTOMER_TYPE)*/NULL;
692 /*SRW.MESSAGE(1005
693 ,' - after Param form With P_end_date = ' || P_END_DATE1)*/NULL;
694 /*SRW.MESSAGE(1006
695 ,' - after Param form With P_start_date = ' || P_START_DATE)*/NULL;
696 RETURN (TRUE);
697 END AFTERPFORM;
698
699 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
700 BEGIN
701 /*SRW.MESSAGE(1275
702 ,'Report Version is 120.2 Last modified date is 25/07/2005')*/NULL;
703 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
704 P_START_DATE1 := TO_CHAR(P_START_DATE,'DD-MM-YYYY');
705 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
706 RETURN (TRUE);
707 END BEFOREREPORT;
708
709 FUNCTION AFTERREPORT RETURN BOOLEAN IS
710 BEGIN
711 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
712 RETURN (TRUE);
713 END AFTERREPORT;
714
715 FUNCTION FUNC_OPEN_BAL_P RETURN NUMBER IS
716 BEGIN
717 RETURN FUNC_OPEN_BAL;
718 END FUNC_OPEN_BAL_P;
719
720 FUNCTION ADD1_P RETURN VARCHAR2 IS
721 BEGIN
722 RETURN ADD1;
723 END ADD1_P;
724
725 FUNCTION ADD2_P RETURN VARCHAR2 IS
726 BEGIN
727 RETURN ADD2;
728 END ADD2_P;
729
730 FUNCTION ADD3_P RETURN VARCHAR2 IS
731 BEGIN
732 RETURN ADD3;
733 END ADD3_P;
734
735 FUNCTION COUNTRY_P RETURN VARCHAR2 IS
736 BEGIN
737 RETURN COUNTRY;
738 END COUNTRY_P;
739
740 FUNCTION LOC_NAME_P RETURN VARCHAR2 IS
741 BEGIN
742 RETURN LOC_NAME;
743 END LOC_NAME_P;
744
745 END JA_JAINDTBR_XMLP_PKG;
746
747