1 PACKAGE BODY jai_cmn_gl_pkg AS
2 /* $Header: jai_cmn_gl.plb 120.2.12020000.3 2012/09/25 06:50:08 vkaranam ship $ */
3
4 /* --------------------------------------------------------------------------------------
5 Filename:
6
7 Change History:
8
9 Date Bug Remarks
10 --------- ---------- -------------------------------------------------------------
11 08-Jun-2005 Version 116.3 jai_cmn_gl -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 PROCEDURE get_account_number(p_chart_of_accounts_id IN NUMBER,
15 p_ccid IN NUMBER, p_account_number OUT NOCOPY VARCHAR2) IS
16
17 CURSOR get_segments_used(coa_id NUMBER,c_flex_code fnd_id_flex_segments.id_flex_code%TYPE ) IS
18 SELECT segment_num, application_column_name
19 FROM fnd_id_flex_segments
20 WHERE id_flex_num = coa_id
21 AND id_flex_code = c_flex_code
22 ORDER BY segment_num;
23
24 v_account_number VARCHAR2(1000) := NULL;
25 v_segment VARCHAR2(25);
26 v_segment_cur INTEGER;
27 v_execute INTEGER;
28 v_rows INTEGER;
29
30 BEGIN
31
32 FOR i IN get_segments_used(p_chart_of_accounts_id,'GL#') LOOP --L1
33 v_segment_cur := DBMS_SQL.OPEN_CURSOR;
34 DBMS_SQL.PARSE(v_segment_cur, 'SELECT '||i.application_column_name||' FROM gl_code_combinations'
35 ||' WHERE code_combination_id = :p_ccid', DBMS_SQL.NATIVE); -- Modified by Brathod, for 4407184 (Bind Var)
36 DBMS_SQL.BIND_VARIABLE(v_segment_cur,':p_ccid',p_ccid); -- Added by Brathod, for 4407184 (Bind Var)
37 DBMS_SQL.DEFINE_COLUMN(v_segment_cur, 1, v_segment, 1000);
38 v_execute := DBMS_SQL.EXECUTE(v_segment_cur);
39 v_rows := DBMS_SQL.FETCH_ROWS(v_segment_cur);
40 DBMS_SQL.COLUMN_VALUE(v_segment_cur, 1, v_segment);
41
42 IF v_account_number IS NOT NULL AND v_segment IS NOT NULL THEN
43 v_account_number := v_account_number||'-'||v_segment;
44 ELSIF v_account_number IS NULL AND v_segment IS NOT NULL THEN
45 v_account_number := v_segment;
46 END IF;
47 DBMS_SQL.CLOSE_CURSOR(v_segment_cur);
48 END LOOP; --L1
49 p_account_number := v_account_number;
50 EXCEPTION
51 WHEN OTHERS THEN
52 IF dbms_sql.is_open(v_segment_cur) THEN
53 dbms_sql.close_cursor(v_segment_cur);
54 END IF;
55 END get_account_number ;
56
57 PROCEDURE create_gl_entry
58 (p_organization_id number,
59 p_currency_code varchar2,
60 p_credit_amount number,
61 p_debit_amount number,
62 p_cc_id number,
63 p_je_source_name varchar2,
64 p_je_category_name varchar,
65 p_created_by number,
66 p_accounting_date date default null,
67 p_currency_conversion_date date default null,
68 p_currency_conversion_type varchar2 default null,
69 p_currency_conversion_rate number default null,
73 p_reference_25 varchar2 default null, --Added by Nagaraj.s for Bug2801751. Populated- Unique Key Column Name
70 p_reference_10 varchar2 default null, --Added by Nagaraj.s for Bug2801751. Populated - Transaction Types || Document Number || Transaction Description
71 p_reference_23 varchar2 default null, --Added by Nagaraj.s for Bug2801751. Populated - Object Name
72 p_reference_24 varchar2 default null, --Added by Nagaraj.s for Bug2801751. Populated- Unique Key Table Name
74 p_reference_26 varchar2 default null, --Added by Nagaraj.s for Bug2801751. Populated- Unique Key
75 p_v_reference1 varchar2 default null --Added by nprashar for bug # 13824043
76 ) IS
77 v_last_update_login number;
78 v_creation_date date;
79 v_created_by number;
80 v_last_update_date date;
81 v_last_updated_by number;
82 v_set_of_books_id number;
83 v_accounting_date date;
84 v_organization_code org_organization_definitions.organization_code%type;
85 v_reference_entry varchar2(240); -- := 'India Localization Entry'; --Ramananda for File.Sql.35
86 v_debug char(1); -- :='Y'; --Ramananda for File.Sql.35
87 v_reference_10 gl_interface.reference10%type;
88 v_reference_23 gl_interface.reference23%type;
89 v_reference_24 gl_interface.reference24%type;
90 v_reference_25 gl_interface.reference25%type;
91 v_reference_26 gl_interface.reference26%type;
92 lv_status gl_interface.status%TYPE ;--rchandan for bug#4428980
93
94 cursor c_trunc_references is
95 select
96 substr(v_reference_10,1,240),
97 substr(p_reference_23,1,240),
98 substr(p_reference_24,1,240),
99 substr(p_reference_25,1,240),
100 substr(p_reference_26,1,240)
101 from dual;
102
103 cursor c_curr_code (cp_Set_of_books_id fnd_currencies.currency_code%type )is
104 select currency_Code
105 from gl_Sets_of_books
106 where set_of_books_id = cp_Set_of_books_id;
107
108 lv_currency_code fnd_currencies.currency_code%type;
109
110 /* Bug 5243532. Added by Lakshmi Gopalsami
111 Implemented caching logic.
112 */
113 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
114
115 /*------------------------------------------------------------------------------------------
116 FILENAME: ja_in_gl_interface_p.sql
117
118 CHANGE HISTORY:
119 S.No Date Author and Details
120 1 08/08/2003 By Nagaraj.s for Bug2801751 Version : 616.1
121 Added 5 parameters to capture the reference values
122 in gl_interface and also changed the Insert statement
123 of gl_interface to include organization code,
124 reference10,22,23,24,25,26,27 for Link to GL.
125 As Parameters are added, this is an huge dependency.
126 It is to be noted that the columns reference25 and 26 are swapped
127 to maintain upward compatibility.
128
129 2 20/02/2004 Nagaraj.s for Bug3456481. Version : 618.1
130 Accounting Date should not be punched with time stamp.
131 This may lead to a scenario where, GL Import Program
132 does not pick these records if the end date given as parameter
133 is equal to the date_created in gl_interface.
134 Hence trunc(v_accounting_date) is written to ensure that these
135 records are picked up.
136
137 3. 11/01/2005
138 ssumIth - bug# 4136981
139 fetch the currency code based on the set of books id retreived and use it for inserting into the
140 gl_interface table.
141
142
143 Future Dependencies For the release Of this Object:-
144 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
145 A datamodel change )
146
147 ----------------------------------------------------------------------------------------------------------------------------------------------------
148 Current Version Current Bug Dependent Files Version Author Date Remarks
149 Of File On Bug/Patchset Dependent On
150 ja_in_gl_interface_p.sql
151 ----------------------------------------------------------------------------------------------------------------------------------------------------
152 616.1 2801751 IN60104D1+2801751 1. ja_in_gl_interface_p.sql
153 2. ja_in_receipt_acct_pkg.sql
154 3. ja_in_rma_receipt_p.sql
155 4. JAINRGAC.fmb
156 5. JAINRGCO.fmb
157 6. JAINRTVN.fmb
158 7. JAINPLAM.fmb
159 8. JAINOSPM.fmb
160 9. JAINIRGI.fmb
161 10. JAIN57F4.fmb
162 11. JAINIBOE.fmb
163
164
165 --------------------------------------------------------------------------------------------*/
166
167 BEGIN
168
169 v_reference_entry := 'India Localization Entry'; --Ramananda for File.Sql.35
170 v_debug := jai_constants.yes; --Ramananda for File.Sql.35
171
172 /* Bug 5243532. Added by Lakshmi Gopalsami
173 Eliminated the usage of org_organization_definitions
174 and implemented the same using caching logic
175 */
176
177 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
178 (p_org_id => p_organization_id);
182 -- End for bug 5243532
179
180 v_set_of_books_id := l_func_curr_det.ledger_id;
181 v_organization_code := l_func_curr_det.organization_code;
183
184
185 v_reference_10 := p_reference_10 || ' for the Organization code ' || v_organization_code;
186
187 /* start additions by ssumaith - bug#4136981
188 coding here to get the currency code based on the set of books id fetched.
189 Its better this is done at a central place rather than at all the satellite calling procedures / triggers
190 */
191 /* Bug 5243532. Added by Lakshmi Gopalsami
192 Removed the cursor which is getting currency code for SOB
193 as it is already fetched using caching logic.
194 */
195 lv_currency_code := l_func_curr_det.currency_code;
196
197 /* end additions by ssumaith - bug#4136981*/
198
199 --This is introduced to ensure that if the reference values goes beyond the specified width,
200 --then the value would be restriced to an width of 240 so that exception would not occur.
201 open c_trunc_references;
202 fetch c_trunc_references
203 into
204 v_reference_10,
205 v_reference_23,
206 v_reference_24,
207 v_reference_25,
208 v_reference_26;
209 close c_trunc_references;
210
211 IF v_debug='Y' THEN
212 FND_FILE.PUT_LINE(FND_FILE.LOG, '**********Inside gl_interface_insert_new Procedure' );
213 FND_FILE.PUT_LINE(FND_FILE.LOG, '9.0 The Value of v_reference_10 is ' || v_reference_10 );
214 FND_FILE.PUT_LINE(FND_FILE.LOG, '9.1 The Value of v_reference_23 is ' || v_reference_23 );
215 FND_FILE.PUT_LINE(FND_FILE.LOG, '9.2 The Value of v_reference_24 is ' || v_reference_24 );
216 FND_FILE.PUT_LINE(FND_FILE.LOG, '9.3 The Value of v_reference_25 is ' || v_reference_25 );
217 FND_FILE.PUT_LINE(FND_FILE.LOG, '9.4 The Value of v_reference_26 is ' || v_reference_26 );
218 FND_FILE.PUT_LINE(FND_FILE.LOG, '9.4 The Value of v_set_of_books_id is ' || v_set_of_books_id );
219 FND_FILE.PUT_LINE(FND_FILE.LOG, '9.4 The Value of v_organization_code is ' || v_organization_code );
220 FND_FILE.PUT_LINE(FND_FILE.LOG, '9.4 The Value of p_organization_id is ' || p_organization_id);
221 FND_FILE.PUT_LINE(FND_FILE.LOG, '9.4 The Value of p_credit_amount is ' || p_credit_amount);
222 FND_FILE.PUT_LINE(FND_FILE.LOG, '9.4 The Value of p_debit_amount is ' || p_debit_amount);
223 END IF;
224
225
226
227 IF p_accounting_date is null
228 THEN
229 v_accounting_date := sysdate;
230 ELSE
231 v_accounting_date := p_accounting_date;
232 END IF;
233
234 v_accounting_date := trunc(v_accounting_date); --Trunc Added by Nagaraj.s for Bug3456481
235
236 --This is added by Nagaraj.s for Bug2801751, so that in case ccid is null then an application error is raised.
237 IF p_cc_id is NULL THEN
238 RAISE_APPLICATION_ERROR(-20000,'Error raised in jai_cmn_gl_pkg.create_gl_entry. Code Combination Id Cannot be Populated as Null. Please Check.');
239 END IF;
240
241
242 IF NVL(p_credit_amount, 0) <> 0 OR
243 NVL(p_debit_amount, 0) <> 0
244 THEN
245
246 --The Reference 25 and 26 have been swapped with values as Reference25 needs to be maintained
247 --as the Unique key reference value,as many clients would have got their own customizations
248 --and changing the column for unique key reference value may result in not supporting
249 --the upward compatibility of the existing feature and this decision was done, after the code
250 --was implemented and hence now reference25 is populated with p_reference_26 and reference26
251 --is populated with p_reference_25.
252 lv_status := 'NEW';----rchandan for bug#4428980
253 INSERT into gl_interface
254 (status,
255 set_of_books_id,
256 user_je_source_name,
257 user_je_category_name,
258 accounting_date,
259 currency_code,
260 date_created,
261 created_by, actual_flag,
262 entered_cr,
263 accounted_cr, --Added for bug # 13824043
264 entered_dr,
265 accounted_dr, --Added for bug # 13824043
266 transaction_date,
267 code_combination_id,
268 currency_conversion_date,
269 user_currency_conversion_type,
270 currency_conversion_rate,
271 --These References are added by Nagaraj.s for Bug2801751
272 reference1,
273 reference10,
274 reference22,
275 reference23,
276 reference24,
277 reference25,
278 reference26,
279 reference27
280 )
281 VALUES (lv_status,--rchandan for bug#4428980
282 v_set_of_books_id,
283 p_je_source_name,
284 p_je_category_name,
285 v_accounting_date,
286 lv_currency_code , --p_currency_code,
287 sysdate,
288 p_created_by,
289 'A',
290 p_credit_amount,
291 p_credit_amount, --Added for bug # 13824043
292 p_debit_amount,
293 p_debit_amount, --Added for bug # 13824043
294 sysdate,
295 p_cc_id,
296 p_currency_conversion_date,
297 p_currency_conversion_type,
298 p_currency_conversion_rate,
299 --These References are added by Nagaraj.s for Bug2801751
300 nvl(p_v_reference1,v_organization_code) , --Added by nprashar for bug # 13824043
301 v_reference_10,
302 v_reference_entry,
303 v_reference_23,
304 v_reference_24,
305 v_reference_26,
306 v_reference_25,
307 to_char(p_organization_id)
308 );
309 END IF;
310 IF v_debug='Y' THEN
314 exception
311 FND_FILE.PUT_LINE(FND_FILE.LOG, '**********End of gl_interface_insert_new Procedure' );
312 END IF;
313 --Exception Introduced by Nagaraj.s for Bug2801751.
315 when others then
316 IF v_debug='Y' THEN
317 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error is raised in gl_interface procedure ' || sqlerrm );
318 END IF;
319 raise_application_error(-20106,'Error is raised in gl_interface procedure ' || sqlerrm);
320 END create_gl_entry;
321
322 Procedure create_gl_entry_for_opm(n_organization_id number,
323 n_currency_code varchar2,
324 n_credit_amount number,
325 n_debit_amount number,
326 n_cc_id number,
327 n_je_source_name in varchar2,
328 n_je_category_name in varchar2,
329 n_created_by in number) is
330 v_last_update_login number;
331 v_creation_date date;
332 v_created_by number;
333 v_last_update_date date;
334 v_last_updated_by number;
335 v_set_of_books_id number;
336 lv_status gl_interface.status%TYPE ;--rchandan for bug#4428980
337
338 Begin
339 select set_of_books_id
340 into v_set_of_books_id
341 From org_organization_definitions
342 where organization_id = n_organization_id;
343 If NVL(n_credit_amount,0) <> 0 Or NVL(n_debit_amount,0) <> 0 Then
344 lv_status := 'NEW';--rchandan for bug#4428980
345 INSERT into gl_interface(status,
346 set_of_books_id,
347 user_je_source_name,
348 user_je_category_name,
349 accounting_date,
350 currency_code,
351 date_created,
352 created_by,
353 actual_flag,
354 entered_cr,
355 accounted_cr, --Added for bug # 13824043
356 entered_dr,
357 accounted_dr, --Added for bug # 13824043
358 transaction_date,
359 code_combination_id)
360 VALUES
361 (lv_status,--rchandan for bug#4428980
362 v_set_of_books_id,
363 n_je_source_name,
364 n_je_category_name,
365 sysdate,
366 n_currency_code,
367 sysdate,
368 n_created_by,
369 'A',
370 n_credit_amount,
371 n_credit_amount, --Added for bug # 13824043
372 n_debit_amount,
373 n_debit_amount, --Added for bug # 13824043
374 sysdate,
375 n_cc_id);
376
377 End If;
378 End create_gl_entry_for_opm;
379
380
381 END jai_cmn_gl_pkg ;
[Home] [Help]