[Home] [Help]
PACKAGE BODY: APPS.JAI_CMN_RPT_BBR_PKG
Source
1 PACKAGE BODY jai_cmn_rpt_bbr_pkg AS
2 /* $Header: jai_cmn_rpt_bbr.plb 120.1 2005/07/20 12:57:39 avallabh ship $ */
3
4 /*----------------------------------------------------------------------------------------
5 Filename:
6
7 Change History:
8
9 Date Bug Remarks
10 --------- ---------- -------------------------------------------------------------
11 08-Jun-2005 Version 116.2 jai_cmn_rpt_bbr -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
12 as required for CASE COMPLAINCE.
13
14 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.3
15 Removal of SQL LITERALs is done
16
17 06-Jul-2005 Ramananda for bug#4477004. File Version: 116.4
18 GL Sources and GL Categories got changed. Refer bug for the details
19
20 ----------------------------------------------------------------------------------------*/
21
22 FUNCTION get_credit_balance(
23 b_start_date DATE,
24 b_bank_account_name VARCHAR,
25 b_bank_account_num VARCHAR,
26 b_org_id NUMBER) return Number is
27 amt NUMBER;
28 amt1 NUMBER;
29
30 /* Added by Ramananda for bug#4407165 */
31 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rpt_bbr_pkg.get_credit_balance';
32
33 /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
34 lv_status_cleared ar_cash_receipt_history_all.status%type ;
35 lv_status_remitted ar_cash_receipt_history_all.status%type ;
36 lv_status_confirmed ar_cash_receipt_history_all.status%type ;
37 lv_status_reversed ar_cash_receipt_history_all.status%type ;
38
39 lv_src_payables gl_je_headers.je_source%type ;
40 lv_src_rcv gl_je_headers.je_source%type ;
41
42
43 BEGIN
44
45 --Stored function to calculate the o/b and c/b for Cash and Bank Book report
46 --code modified by sridhar k to consider reversal receipts
47 --whereever there is a reversal receipt, it is made negative to nullify the overall effect.
48
49 lv_status_cleared := 'CLEARED';
50 lv_status_remitted := 'REMITTED';
51 lv_status_confirmed := 'CONFIRMED';
52 lv_status_reversed := 'REVERSED' ;
53
54 lv_src_payables := 'Payables India';
55 lv_src_rcv := 'Receivables India' ;
56
57
58 SELECT SUM(DECODE(acrh.status, 'REVERSED',(acrh.amount*NVL(acrh.exchange_rate,1))*-1,
59 NVL(acrh.amount, 0)*NVL(acrh.exchange_rate,1))) INTO amt
60 FROM ar_cash_receipt_history_all acrh,
61 ar_cash_receipts_all acr,
62 hz_cust_accounts rc,
63 ce_bank_accounts ceba
64 WHERE acrh.cash_receipt_id = acr.cash_receipt_id
65 AND acr.remittance_bank_account_id = ceba.bank_account_id
66 AND acr.pay_from_customer = rc.cust_account_id (+)
67 AND acrh.status IN (lv_status_cleared, lv_status_remitted, lv_status_confirmed, lv_status_reversed) --'CLEARED', 'REMITTED', 'CONFIRMED','REVERSED') --reversal entries considered
68 AND ceba.bank_account_name = NVL(b_bank_account_name, ceba.bank_account_name)
69 AND ceba.bank_account_num = NVL(b_bank_account_num, ceba.bank_account_num)
70 AND TRUNC(acrh.gl_date ) < TRUNC(b_start_date)
71 AND (acr.org_id = b_org_id OR acr.org_id IS NULL);
72 --SRW.message(3, to_char(NVL(amt, 0)));
73
74 SELECT SUM(accounted_dr) INTO amt1
75 FROM gl_je_headers glh,
76 gl_je_lines gll,
77 ce_gl_accounts_ccid cega,
78 ce_bank_acct_uses_all cebau,
79 ce_bank_accounts ceba
80 WHERE cega.bank_acct_use_id = cebau.bank_acct_use_id
81 AND cebau.bank_account_id = ceba.bank_account_id
82 AND ceba.ap_use_allowed_flag = 'Y'
83 AND (ceba.start_date IS NULL OR ceba.start_date <= trunc(sysdate))
84 AND (ceba.end_date IS NULL OR ceba.end_date >= trunc(sysdate))
85 AND cebau.ap_use_enable_flag = 'Y'
86 AND (cebau.end_date IS NULL OR cebau.end_date >= trunc(sysdate))
87 AND glh.je_header_id = gll.je_header_id
88 AND cega.ap_asset_ccid = gll.code_combination_id
89 AND ceba.bank_account_name = NVL(b_bank_account_name, ceba.bank_account_name)
90 AND ceba.bank_account_num = NVL(b_bank_account_num, ceba.bank_account_num)
91 AND glh.je_source NOT IN (lv_src_payables, lv_src_rcv) --'Payables India', 'Receivables India')
92 AND TRUNC(glh.default_effective_date ) < TRUNC(b_start_date)
93 AND (cebau.org_id = b_org_id OR cebau.org_id IS NULL);
94 --SRW.message(4, to_char(NVL(amt1, 0)));
95 RETURN(NVL(amt, 0) + NVL(amt1, 0));
96
97 /* Added by Ramananda for bug#4407165 */
98 EXCEPTION
99 WHEN OTHERS THEN
100 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
101 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
102 app_exception.raise_exception;
103
104 END get_credit_balance;
105
106 FUNCTION get_debit_balance(
107 b_start_date DATE,
108 b_bank_account_name VARCHAR,
109 b_bank_account_num VARCHAR,
110 b_org_id NUMBER)
111 RETURN Number is
112 amt NUMBER;
113 amt1 NUMBER;
114
115 /* Added by Ramananda for bug#4407165 */
116 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rpt_bbr_pkg.get_debit_balance';
117
118 lv_negotiable ap_checks_all.status_lookup_code%TYPE;
119 lv_cleared ap_checks_all.status_lookup_code%TYPE;
120 lv_voided ap_checks_all.status_lookup_code%TYPE;
121
122 lv_src_payables gl_je_headers.je_source%type ;
123 lv_src_rcv gl_je_headers.je_source%type ;
124
125 --Stored function to calculate the o/b and c/b for Cash and Bank Book report
126 BEGIN
127
128 lv_negotiable := 'NEGOTIABLE';
129 lv_cleared := 'CLEARED';
130 lv_voided := 'VOIDED' ;
131
132 lv_src_payables := 'Payables India';
133 lv_src_rcv := 'Receivables India' ;
134
135 SELECT SUM(NVL(aip.amount, 0)*NVL(aip.exchange_rate,1)) INTO amt /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
136 FROM ap_invoice_payments_all aip,
137 ap_invoices_all api,
138 ap_checks_all apc,
139 ce_bank_accounts ceba
140 WHERE api.invoice_id = aip.invoice_id
141 AND aip.check_id = apc.check_id
142 AND apc.bank_account_id = ceba.bank_account_id
143 AND apc.status_lookup_code IN (lv_negotiable, lv_cleared, lv_voided) --'NEGOTIABLE', 'CLEARED','VOIDED') --added for voided payments by sridhar k
144 AND ceba.bank_account_name = NVL(b_bank_account_name, ceba.bank_account_name)
145 AND ceba.bank_account_num = NVL(b_bank_account_num, ceba.bank_account_num)
146 AND TRUNC(aip.accounting_date ) < TRUNC(b_start_date)
147 AND (api.org_id = b_org_id OR api.org_id IS NULL);
148 --SRW.message(1, to_char(NVL(amt, 0)));
149 SELECT SUM(accounted_cr) INTO amt1
150 FROM gl_je_headers glh,
151 gl_je_lines gll,
152 ce_gl_accounts_ccid cega,
153 ce_bank_acct_uses_all cebau,
154 ce_bank_accounts ceba
155 WHERE cega.bank_acct_use_id = cebau.bank_acct_use_id
156 AND cebau.bank_account_id = ceba.bank_account_id
157 AND ceba.ap_use_allowed_flag = 'Y'
158 AND (ceba.start_date IS NULL OR ceba.start_date <= TRUNC (sysdate))
159 AND (ceba.end_date IS NULL OR ceba.end_date >= trunc(sysdate))
160 AND cebau.ap_use_enable_flag = 'Y'
161 AND (cebau.end_date IS NULL OR cebau.end_date >= trunc(sysdate))
162 AND glh.je_header_id = gll.je_header_id
163 AND cega.ap_asset_ccid = gll.code_combination_id
164 AND ceba.bank_account_name = NVL(b_bank_account_name, ceba.bank_account_name)
165 AND ceba.bank_account_num = NVL(b_bank_account_num, ceba.bank_account_num)
166 AND glh.je_source NOT IN (lv_src_payables, lv_src_rcv) --'Payables India', 'Receivables India')
167 AND TRUNC(glh.default_effective_date ) < TRUNC(b_start_date)
168 AND (cebau.org_id = b_org_id OR cebau.org_id IS NULL);
169 --SRW.message(2, to_char(NVL(amt1, 0)));
170 RETURN(NVL(amt, 0) + NVL(amt1, 0));
171
172 /* Added by Ramananda for bug#4407165 */
173 EXCEPTION
174 WHEN OTHERS THEN
175 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
176 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
177 app_exception.raise_exception;
178
179 END get_debit_balance;
180
181 END jai_cmn_rpt_bbr_pkg;