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