[Home] [Help]
PACKAGE BODY: APPS.JAI_CMN_RG_OTHERS_PKG
Source
1 PACKAGE BODY jai_cmn_rg_others_pkg AS
2 /* $Header: jai_cmn_rg_oth.plb 120.2 2007/05/04 13:43:25 csahoo ship $ */
3 /***************************************************************************************************
4 CREATED BY : ssumaith
5 CREATED DATE : 11-JAN-2005
6 ENHANCEMENT BUG : 4136981
7 PURPOSE : To pass cess register entries
8 CALLED FROM : jai_om_rg_pkg.ja_in_rg23_part_ii_entry , jai_om_rg_pkg.pla_emtry , jai_om_rg_pkg.ja_in23d_entry
9
10
11
12 /*----------------------------------------------------------------------------------------------------------------------------
13
14 CHANGE HISTORY for FILENAME: jai_rg_others_pkg_b.sql
15 S.No dd/mm/yyyy Author and Details
16 1 17/02/2005 ssumaith - File version 115.1
17 IF cess amount is passed as zero, an error is thrown to the user that cess amount cannot be zero
18 This may not be correct at all times. Hence code has been added to return control with success
19 and not process the insert into JAI_CMN_RG_OTHERS table with zero debit and credit.
20
21 2. 08-Jun-2005 Version 116.1 jai_cmn_rg_oth -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
22 as required for CASE COMPLAINCE.
23
24 3. 14-Jun-2005 rchandan for bug#4428980, Version 116.2
25 Modified the object to remove literals from DML statements and CURSORS.
26 4.16-apr-2007 Vkaranam For bug#5989740,File version 120.2
27 Forward Porting The changes in 115 bug #5907436
28 Added a new peocedure check_sh_balances.
29
30
31
32 ------------------------------------------------------------------------------------------------------------------------------
33
34 ***************************************************************************************************/
35
36 procedure insert_row (p_source_type JAI_CMN_RG_OTHERS.SOURCE_TYPE%TYPE ,
37 p_source_name JAI_CMN_RG_OTHERS.SOURCE_REGISTER%TYPE ,
38 p_source_id JAI_CMN_RG_OTHERS.SOURCE_REGISTER_ID%TYPE ,
39 p_tax_type JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE ,
40 debit_amt JAI_CMN_RG_OTHERS.DEBIT%TYPE ,
44 p_attribute1 VARCHAR2 DEFAULT NULL ,
41 credit_amt JAI_CMN_RG_OTHERS.CREDIT%TYPE ,
42 p_process_flag OUT NOCOPY VARCHAR2 ,
43 p_process_msg OUT NOCOPY VARCHAR2 ,
45 p_attribute2 VARCHAR2 DEFAULT NULL ,
46 p_attribute3 VARCHAR2 DEFAULT NULL ,
47 p_attribute4 VARCHAR2 DEFAULT NULL ,
48 p_attribute5 VARCHAR2 DEFAULT NULL
49 ) IS
50
51 CURSOR c_rg_other_id IS
52 SELECT JAI_CMN_RG_OTHERS_S.nextval
53 FROM dual;
54
55 ln_rg_other_id JAI_CMN_RG_OTHERS.RG_OTHER_ID%TYPE;
56 ln_user_id NUMBER ; --:= fnd_global.user_id File.Sql.35 by Brathod
57 BEGIN
58 ln_user_id := fnd_global.user_id ; -- File.Sql.35 by Brathod
59 IF nvl(debit_amt,0) = 0 AND NVL(credit_amt,0) =0 THEN
60 p_process_flag := jai_constants.successful;
61 p_process_msg := NULL;
62 return;
63 END IF;
64
65 IF p_source_type IS NULL THEN
66 p_process_flag := jai_constants.expected_error;
67 p_process_msg := 'jai_cmn_rg_others_pkg - Source type cannot be Null';
68 return;
69 END IF;
70
71 IF p_source_name IS NULL THEN
72 p_process_flag := jai_constants.expected_error;
73 p_process_msg := 'jai_cmn_rg_others_pkg -Source Name cannot be Null';
74 return;
75 end if;
76
77 IF p_tax_type IS NULL THEN
78 p_process_flag := jai_constants.expected_error;
79 p_process_msg := 'jai_cmn_rg_others_pkg - Tax type cannot be Null';
80 return;
81 END IF;
82
83 OPEN c_rg_other_id;
84 FETCH c_rg_other_id into ln_rg_other_id;
85 CLOSE c_rg_other_id;
86
87 Insert into JAI_CMN_RG_OTHERS
88 (
89 rg_other_id ,
90 source_type ,
91 source_register ,
92 source_register_id ,
93 tax_type ,
94 credit ,
95 debit ,
96 created_by ,
97 creation_date ,
98 last_updated_by ,
99 last_update_date ,
100 last_update_login
101 )
102 Values
103 (
104 ln_rg_other_id ,
105 p_source_type ,
106 p_source_name ,
107 p_source_id ,
108 p_tax_type ,
109 credit_amt ,
110 debit_amt ,
111 ln_user_id ,
112 sysdate ,
113 ln_user_id ,
114 sysdate ,
115 fnd_global.login_id
116 );
117 p_process_flag := jai_constants.successful;
118
119 EXCEPTION
120 WHEN OTHERS THEN
121 p_process_flag := jai_constants.unexpected_error;
122 p_process_msg := 'Error Occured in jai_cmn_rg_others_pkg.insert_row - ' || substr(sqlerrm,1,900);
123 END insert_row;
124
125
126 procedure check_balances(p_organization_id JAI_CMN_INVENTORY_ORGS.ORGANIZATION_ID%TYPE ,
127 p_location_id HR_LOCATIONS.LOCATION_ID%TYPE ,
128 p_register_type JAI_CMN_RG_OTH_BALANCES.REGISTER_TYPE%TYPE ,
129 p_trx_amount NUMBER ,
130 p_process_flag OUT NOCOPY VARCHAR2 ,
131 p_process_message OUT NOCOPY VARCHAR2
132 )
133 IS
134
135 CURSOR c_org_unit_id IS
136 SELECT org_unit_id
137 FROM JAI_CMN_INVENTORY_ORGS
138 WHERE organization_id = p_organization_id
139 AND location_id = p_location_id ;
140
141 CURSOR c_balance_cur(cp_org_unit_id JAI_CMN_INVENTORY_ORGS.ORG_UNIT_ID%TYPE) IS
142 SELECT NVL(SUM(balance),0)
143 FROM JAI_CMN_RG_OTH_BALANCES
144 WHERE org_unit_id = cp_org_unit_id
145 and register_type = p_register_type
146 and tax_type in ( jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_cvd_edu_cess); --rchandan for bug#4428980
147
148 ln_org_unit_id NUMBER;
149 ln_balance_avlbl NUMBER;
150
151 BEGIN
152
153 IF NVL(p_trx_amount,0) = 0 THEN
154 p_process_flag := jai_constants.successful;
155 p_process_message := NULL;
156 RETURN;
157 END IF;
158
159 OPEN c_org_unit_id;
160 FETCH c_org_unit_id into ln_org_unit_id;
161 CLOSE c_org_unit_id;
162
163 IF ln_org_unit_id IS NULL then
164 p_process_flag := jai_constants.expected_error;
165 p_process_message := 'JAI_CMN_RG_OTHERS.check_balances => Invalid Organization and Location Combination passed ';
166 return;
167 END IF;
168
169 OPEN c_balance_cur(ln_org_unit_id);
170 FETCH c_balance_cur into ln_balance_avlbl;
171 close c_balance_cur;
172
173 IF ln_balance_avlbl = 0 THEN
174 p_process_flag := jai_constants.expected_error;
175 p_process_message := 'JAI_CMN_RG_OTHERS.check_balances => No Balance Available for the organization , location and register type combination ';
176 return;
177 END IF;
178
179 IF p_trx_amount IS NULL THEN
180 p_process_flag := jai_constants.expected_error;
181 p_process_message := 'JAI_CMN_RG_OTHERS.check_balances => Input Transaction Amount is NULL ';
182 return;
183 END IF;
184
185 IF ln_balance_avlbl < p_trx_amount THEN
186 p_process_flag := jai_constants.expected_error;
187 p_process_message := 'JAI_CMN_RG_OTHERS.check_balances => Sufficient Balance Not Available for the organization , location and register type combination ';
188 return;
189 END IF;
190
191 p_process_flag := jai_constants.successful;
192 p_process_message := NULL;
193
194 EXCEPTION
195 WHEN others THEN
196 p_process_flag := jai_constants.unexpected_error;
197 p_process_message := 'JAI_CMN_RG_OTHERS.check_balances => Error Occured : ' || substr(sqlerrm,1,1000);
198 END;
199
200 /*Procedure check_sh_balances is added to check balances for secondary and higher education cess */
201 -- start 5989740
202 procedure check_sh_balances(p_organization_id JAI_CMN_INVENTORY_ORGS.ORGANIZATION_ID%TYPE ,
203 p_location_id HR_LOCATIONS.LOCATION_ID%TYPE ,
204 p_register_type JAI_CMN_RG_OTH_BALANCES.REGISTER_TYPE%TYPE ,
205 p_trx_amount NUMBER ,
206 p_process_flag OUT NOCOPY VARCHAR2 ,
207 p_process_message OUT NOCOPY VARCHAR2
208 )
209 IS
210
211 CURSOR c_org_unit_id IS
212 SELECT org_unit_id
213 FROM JAI_CMN_INVENTORY_ORGS
214 WHERE organization_id = p_organization_id
215 AND location_id = p_location_id ;
216
217
218 CURSOR c_balance_cur(cp_org_unit_id JAI_CMN_INVENTORY_ORGS.ORG_UNIT_ID%TYPE) IS
219 SELECT NVL(SUM(balance),0)
220 FROM JAI_CMN_RG_OTH_BALANCES
221 WHERE org_unit_id = cp_org_unit_id
222 and register_type = p_register_type
223 and tax_type in ( jai_constants.tax_type_sh_exc_edu_cess,
224 jai_constants.tax_type_sh_cvd_edu_cess
225 );
226
227 ln_org_unit_id NUMBER;
228 ln_balance_avlbl NUMBER;
229
230 BEGIN
231
232 IF NVL(p_trx_amount,0) = 0 THEN
233 p_process_flag := jai_constants.successful;
234 p_process_message := NULL;
235 RETURN;
236 END IF;
237
238 OPEN c_org_unit_id;
239 FETCH c_org_unit_id into ln_org_unit_id;
240 CLOSE c_org_unit_id;
241
242 IF ln_org_unit_id IS NULL then
243 p_process_flag := jai_constants.expected_error;
244 p_process_message := 'JAI_CMN_RG_OTHERS.check_sh_balances => Invalid Organization and Location Combination passed ';
245 return;
246 END IF;
247
248 OPEN c_balance_cur(ln_org_unit_id);
249 FETCH c_balance_cur into ln_balance_avlbl;
250 close c_balance_cur;
251
252 IF ln_balance_avlbl = 0 THEN
253 p_process_flag := jai_constants.expected_error;
254 p_process_message := 'JAI_CMN_RG_OTHERS.check_sh_balances => No Balance Available for the organization , location and register type combination ';
255 return;
256 END IF;
257
258 IF p_trx_amount IS NULL THEN
259 p_process_flag := jai_constants.expected_error;
260 p_process_message := 'JAI_CMN_RG_OTHERS.check_sh_balances => Input Transaction Amount is NULL ';
261 return;
262 END IF;
263
264 IF ln_balance_avlbl < p_trx_amount THEN
265 p_process_flag := jai_constants.expected_error;
266 p_process_message := 'JAI_CMN_RG_OTHERS.check_sh_balances => Sufficient Balance Not Available for the organization , location and register type combination ';
267 return;
268 END IF;
269
270 p_process_flag := jai_constants.successful;
271 p_process_message := NULL;
272
273 EXCEPTION
274 WHEN others THEN
275 p_process_flag := jai_constants.unexpected_error;
276 p_process_message := 'JAI_CMN_RG_OTHERS.check_sh_balances => Error Occured : ' || substr(sqlerrm,1,1000);
277 END check_sh_balances;
278 --end 5989740
279
280
281 END jai_cmn_rg_others_pkg;