[Home] [Help]
PACKAGE BODY: APPS.JAI_RCV_JOURNAL_PKG
Source
1 PACKAGE BODY jai_rcv_journal_pkg AS
2 /* $Header: jai_rcv_jrnl.plb 120.4 2006/05/26 11:52:04 lgopalsa ship $ */
3
4 /* --------------------------------------------------------------------------------------
5 Filename:
6
7 Change History:
8
9 Date Bug Remarks
10 --------- ---------- -------------------------------------------------------------
11 08-Jun-2005 Version 116.2 jai_rcv_jrnl -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 4428980 File Version: 116.3
15 Ramananda for bug#4428980. Removal of SQL LITERALs is done
16 --------------------------------------------------------------------------------------*/
17
18 PROCEDURE insert_row(
19
20 P_ORGANIZATION_ID IN NUMBER,
21 P_ORGANIZATION_CODE IN JAI_RCV_JOURNAL_ENTRIES.organization_code%TYPE,
22 P_RECEIPT_NUM IN JAI_RCV_JOURNAL_ENTRIES.receipt_num%TYPE,
23 P_TRANSACTION_ID IN JAI_RCV_JOURNAL_ENTRIES.transaction_id%TYPE,
24 P_TRANSACTION_DATE IN JAI_RCV_JOURNAL_ENTRIES.transaction_date%TYPE,
25 P_SHIPMENT_LINE_ID IN JAI_RCV_JOURNAL_ENTRIES.shipment_line_id%TYPE,
26 P_ACCT_TYPE IN JAI_RCV_JOURNAL_ENTRIES.acct_type%TYPE,
27 P_ACCT_NATURE IN JAI_RCV_JOURNAL_ENTRIES.acct_nature%TYPE,
28 P_SOURCE_NAME IN JAI_RCV_JOURNAL_ENTRIES.source_name%TYPE,
29 P_CATEGORY_NAME IN JAI_RCV_JOURNAL_ENTRIES.category_name%TYPE,
30 P_CODE_COMBINATION_ID IN JAI_RCV_JOURNAL_ENTRIES.code_combination_id%TYPE,
31 P_ENTERED_DR IN JAI_RCV_JOURNAL_ENTRIES.entered_dr%TYPE,
32 P_ENTERED_CR IN JAI_RCV_JOURNAL_ENTRIES.entered_cr%TYPE,
33 P_TRANSACTION_TYPE IN JAI_RCV_JOURNAL_ENTRIES.transaction_type%TYPE,
34 P_PERIOD_NAME IN JAI_RCV_JOURNAL_ENTRIES.period_name%TYPE,
35 P_CURRENCY_CODE IN JAI_RCV_JOURNAL_ENTRIES.currency_code%TYPE,
36 P_CURRENCY_CONVERSION_TYPE IN JAI_RCV_JOURNAL_ENTRIES.currency_conversion_type%TYPE,
37 P_CURRENCY_CONVERSION_DATE IN JAI_RCV_JOURNAL_ENTRIES.currency_conversion_date%TYPE,
38 P_CURRENCY_CONVERSION_RATE IN JAI_RCV_JOURNAL_ENTRIES.currency_conversion_rate%TYPE,
39 P_SIMULATE_FLAG IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
40 P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
41 P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2,
42 /* two parameters added by Vijay Shankar for Bug#4250236(4245089). VAT Implementation */
43 p_reference_name in varchar2 ,
44 p_reference_id in number
45 ) IS
46
47 ld_creation_date DATE;
48 ln_created_by NUMBER;
49 ln_last_update_login JAI_RCV_JOURNAL_ENTRIES.LAST_UPDATE_LOGIN%TYPE ;
50
51 lv_period_name JAI_RCV_JOURNAL_ENTRIES.period_name%TYPE;
52 lv_organization_code ORG_ORGANIZATION_DEFINITIONS.organization_code%TYPE;
53
54 /* Bug 5243532. Added by Lakshmi Gopalsami
55 * Removed org_organization_definitions from the cursor c_period_name
56 * and passed set_of_books_id to the cursor. Also removed
57 * gl_sets_of_books and included gl_ledgers.
58 */
59
60 CURSOR c_period_name(cp_set_of_books_id IN NUMBER, cp_transaction_date IN DATE) IS
61 SELECT gd.period_name
62 FROM gl_ledgers gle, gl_periods gd
63 WHERE gle.ledger_id = cp_set_of_books_id
64 AND gd.period_set_name = gle.period_set_name
65 AND cp_transaction_date BETWEEN gd.start_date and gd.end_date
66 AND gd.adjustment_period_flag = 'N';
67
68 lv_statement_id VARCHAR2(5);
69
70 /* Bug 5243532. Added by Lakshmi Gopalsami
71 * Defined variable for implementing caching logic.
72 */
73 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
74 ln_set_of_books_id NUMBER;
75
76 BEGIN
77
78 /*----------------------------------------------------------------------------------------------------------------------------
79 CHANGE HISTORY for FILENAME: jai_rcv_journal_pkg.sql
80 S.No dd/mm/yyyy Author and Details
81 ------------------------------------------------------------------------------------------------------------------------------
82 1 16/07/2002 Vijay Shankar for Bug# 3496408, Version:115.0
83 Table Handler coded for JAI_RCV_JOURNAL_ENTRIES table. Update_row of the package was just a skeleton that needs to be modified
84 whenever it is being used
85
86 2 10/11/2004 Vijay Shankar for Bug#4003518, Version:115.1
87 Modified the INSERT_ROW definition to DEFAULT 'N' for p_simulate_flag parameter. without this, its not a
88 problem in Oracle8i, however it is problem in 9i and thus the bugfix
89
90 3 19/03/2005 Vijay Shankar for Bug#4250236(4245089). FileVersion: 115.2
91 added two more parameters in insert_row procedure as part of VAT Implementation
92 4 28/11/2005 Hjujjuru for the bug 4762433 File version 120.3
93 added the who columns in the insert of jai_rcv_journals.
94 Dependencies Due to this bug:-
95 None
96 Dependencies
97 IN60106 + 4245089
98 ----------------------------------------------------------------------------------------------------------------------------*/
99
100 ld_creation_date := SYSDATE;
101 ln_created_by := FND_GLOBAL.user_id;
102 ln_last_update_login := fnd_global.login_id; -- added, Harshita for Bug 4762433
103
104 lv_statement_id := '1';
105 IF p_period_name IS NULL OR p_organization_code IS NULL THEN
106 /* Bug 5243532. Added by Lakshmi Gopalsami
107 * Implemented caching logic for getting organization_code
108 */
109 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
110 (p_org_id => p_organization_id);
111 lv_organization_code := l_func_curr_det.organization_code;
112 ln_set_of_books_id := l_func_curr_det.ledger_id;
113 /* Bug 5243532. Added by Lakshmi Gopalsami
114 * Passes ln_set_of_books_id instead of p_transaction_id
115 */
116 OPEN c_period_name(ln_set_of_books_id, trunc(p_transaction_date));
117 FETCH c_period_name INTO lv_period_name;
118 CLOSE c_period_name;
119 END IF;
120
121 lv_statement_id := '1.1';
122 IF p_period_name IS NOT NULL THEN
123 lv_period_name := p_period_name;
124 END IF;
125 IF p_organization_code IS NOT NULL THEN
126 lv_organization_code := p_organization_code;
127 END IF;
128
129 lv_statement_id := '2';
130 INSERT INTO JAI_RCV_JOURNAL_ENTRIES(JOURNAL_ENTRY_ID,
131 ORGANIZATION_CODE,
132 RECEIPT_NUM,
133 TRANSACTION_ID,
134 CREATION_DATE,
135 TRANSACTION_DATE,
136 SHIPMENT_LINE_ID,
137 ACCT_TYPE,
138 ACCT_NATURE,
139 SOURCE_NAME,
140 CATEGORY_NAME,
141 CODE_COMBINATION_ID,
142 ENTERED_DR,
143 ENTERED_CR,
144 TRANSACTION_TYPE,
145 PERIOD_NAME,
146 CREATED_BY,
147 CURRENCY_CODE,
148 CURRENCY_CONVERSION_TYPE,
149 CURRENCY_CONVERSION_DATE,
150 CURRENCY_CONVERSION_RATE,
151 -- DUMMY_FLAG,
152 /* following two parameters added by Vijay Shankar for Bug#4250236(JOURNAL_ENTRY_ID,4245089). VAT Implementation */
153 reference_name,
154 reference_id,
155 -- following 3 parameters added by Harshita for Bug 4762433
156 LAST_UPDATED_BY,
157 LAST_UPDATE_DATE,
158 LAST_UPDATE_LOGIN
159 ) VALUES (JAI_RCV_JOURNAL_ENTRIES_S.nextval,
160 lv_organization_code, -- P_ORGANIZATION_CODE,
161 P_RECEIPT_NUM,
162 P_TRANSACTION_ID,
163 ld_creation_date,
164 P_TRANSACTION_DATE,
165 P_SHIPMENT_LINE_ID,
166 P_ACCT_TYPE,
167 P_ACCT_NATURE,
168 P_SOURCE_NAME,
169 P_CATEGORY_NAME,
170 P_CODE_COMBINATION_ID,
171 P_ENTERED_DR,
172 P_ENTERED_CR,
173 P_TRANSACTION_TYPE,
174 lv_period_name,
175 ln_created_by,
176 P_CURRENCY_CODE,
177 P_CURRENCY_CONVERSION_TYPE,
178 P_CURRENCY_CONVERSION_DATE,
179 P_CURRENCY_CONVERSION_RATE,
180 -- p_simulate_flag,
181 p_reference_name,
182 p_reference_id,
183 ln_created_by, -- Harshita for Bug 4762433
184 ld_creation_date , -- Harshita for Bug 4762433
185 ln_last_update_login -- Harshita for Bug 4762433
186 );
187
188 EXCEPTION
189 WHEN OTHERS THEN
190 p_process_status := 'E';
191 p_process_message := 'RCV_JOURNALS_PKG.insert_row->'||SQLERRM||', StmtId->'||lv_statement_id;
192 FND_FILE.put_line( FND_FILE.log, p_process_message);
193
194 END insert_row;
195
196 PROCEDURE update_row(
197
198 P_ORGANIZATION_CODE IN JAI_RCV_JOURNAL_ENTRIES.organization_code%TYPE DEFAULT NULL,
199 P_RECEIPT_NUM IN JAI_RCV_JOURNAL_ENTRIES.receipt_num%TYPE DEFAULT NULL,
200 P_TRANSACTION_ID IN JAI_RCV_JOURNAL_ENTRIES.transaction_id%TYPE DEFAULT NULL,
201 P_CREATION_DATE IN JAI_RCV_JOURNAL_ENTRIES.creation_date%TYPE DEFAULT NULL,
202 P_TRANSACTION_DATE IN JAI_RCV_JOURNAL_ENTRIES.transaction_date%TYPE DEFAULT NULL,
203 P_SHIPMENT_LINE_ID IN JAI_RCV_JOURNAL_ENTRIES.shipment_line_id%TYPE DEFAULT NULL,
204 P_ACCT_TYPE IN JAI_RCV_JOURNAL_ENTRIES.acct_type%TYPE DEFAULT NULL,
205 P_ACCT_NATURE IN JAI_RCV_JOURNAL_ENTRIES.acct_nature%TYPE DEFAULT NULL,
206 P_SOURCE_NAME IN JAI_RCV_JOURNAL_ENTRIES.source_name%TYPE DEFAULT NULL,
207 P_CATEGORY_NAME IN JAI_RCV_JOURNAL_ENTRIES.category_name%TYPE DEFAULT NULL,
208 P_CODE_COMBINATION_ID IN JAI_RCV_JOURNAL_ENTRIES.code_combination_id%TYPE DEFAULT NULL,
209 P_ENTERED_DR IN JAI_RCV_JOURNAL_ENTRIES.entered_dr%TYPE DEFAULT NULL,
210 P_ENTERED_CR IN JAI_RCV_JOURNAL_ENTRIES.entered_cr%TYPE DEFAULT NULL,
211 P_TRANSACTION_TYPE IN JAI_RCV_JOURNAL_ENTRIES.transaction_type%TYPE DEFAULT NULL,
212 P_PERIOD_NAME IN JAI_RCV_JOURNAL_ENTRIES.period_name%TYPE DEFAULT NULL,
213 P_CREATED_BY IN JAI_RCV_JOURNAL_ENTRIES.created_by%TYPE DEFAULT NULL,
214 P_CURRENCY_CODE IN JAI_RCV_JOURNAL_ENTRIES.currency_code%TYPE DEFAULT NULL,
215 P_CURRENCY_CONVERSION_TYPE IN JAI_RCV_JOURNAL_ENTRIES.currency_conversion_type%TYPE DEFAULT NULL,
216 P_CURRENCY_CONVERSION_DATE IN JAI_RCV_JOURNAL_ENTRIES.currency_conversion_date%TYPE DEFAULT NULL,
217 P_CURRENCY_CONVERSION_RATE IN JAI_RCV_JOURNAL_ENTRIES.currency_conversion_rate%TYPE DEFAULT NULL
218 ) IS
219 BEGIN
220
221 UPDATE JAI_RCV_JOURNAL_ENTRIES SET
222 ORGANIZATION_CODE = nvl(P_ORGANIZATION_CODE, ORGANIZATION_CODE),
223 RECEIPT_NUM = nvl(P_RECEIPT_NUM, RECEIPT_NUM),
224 TRANSACTION_ID = nvl(P_TRANSACTION_ID, TRANSACTION_ID),
225 CREATION_DATE = nvl(P_CREATION_DATE, CREATION_DATE),
226 TRANSACTION_DATE = nvl(P_TRANSACTION_DATE, TRANSACTION_DATE),
227 SHIPMENT_LINE_ID = nvl(P_SHIPMENT_LINE_ID, SHIPMENT_LINE_ID),
228 ACCT_TYPE = nvl(P_ACCT_TYPE, ACCT_TYPE),
229 ACCT_NATURE = nvl(P_ACCT_NATURE, ACCT_NATURE),
230 SOURCE_NAME = nvl(P_SOURCE_NAME, SOURCE_NAME),
231 CATEGORY_NAME = nvl(P_CATEGORY_NAME, CATEGORY_NAME),
232 CODE_COMBINATION_ID = nvl(P_CODE_COMBINATION_ID, CODE_COMBINATION_ID),
233 ENTERED_DR = nvl(P_ENTERED_DR, ENTERED_DR),
234 ENTERED_CR = nvl(P_ENTERED_CR, ENTERED_CR),
235 TRANSACTION_TYPE = nvl(P_TRANSACTION_TYPE, TRANSACTION_TYPE),
236 PERIOD_NAME = nvl(P_PERIOD_NAME, PERIOD_NAME),
237 CREATED_BY = nvl(P_CREATED_BY, CREATED_BY),
238 CURRENCY_CODE = nvl(P_CURRENCY_CODE, CURRENCY_CODE),
239 CURRENCY_CONVERSION_TYPE = nvl(P_CURRENCY_CONVERSION_TYPE, CURRENCY_CONVERSION_TYPE),
240 CURRENCY_CONVERSION_DATE = nvl(P_CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_DATE),
241 CURRENCY_CONVERSION_RATE = nvl(P_CURRENCY_CONVERSION_RATE, CURRENCY_CONVERSION_RATE)
242 WHERE transaction_id = p_transaction_id;
243
244 END update_row;
245
246 /*------------------------------------------------------------------------------------------------------------*/
247 PROCEDURE create_subledger_entry
248 (
249 p_transaction_id number,
250 p_organization_id number,
251 p_currency_code varchar2,
252 p_credit_amount number,
253 p_debit_amount number,
254 p_cc_id number,
255 p_created_by number,
256 p_accounting_date date default null,
257 p_currency_conversion_date date default null,
258 p_currency_conversion_type varchar2 default null,
259 p_currency_conversion_rate number default null
260 )IS
261
262 v_last_update_login number;
263 v_creation_date date;
264 v_created_by number;
265 v_last_update_date date;
266 v_last_updated_by number;
267 v_set_of_books_id number;
268 v_accounting_date date;
269 v_sysdate DATE; -- := SYSDATE; File.Sql.35 by Brathod
270 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_rcv_journal_pkg.create_subledger_entry';
271
272 Cursor sub_cur IS
273 SELECT actual_flag,
274 je_source_name,
275 je_category_name,
276 period_name,
277 chart_of_accounts_id,
278 functional_currency_code,
282 je_line_description,
279 je_batch_name,
280 je_batch_description,
281 je_header_name,
283 reference1,
284 reference2,
285 reference3,
286 reference4,
287 source_doc_quantity
288 FROM rcv_receiving_sub_ledger
289 WHERE rcv_transaction_id = p_transaction_id
290 AND rownum = 1;
291
292 CURSOR rcv_cur IS
293 SELECT source_document_code,
294 shipment_line_id,
295 po_line_location_id,
296 requisition_line_id
297 FROM rcv_transactions
298 WHERE transaction_id = p_transaction_id;
299
300 v_rcv_rec rcv_cur % ROWTYPE;
301
302 CURSOR ship_rec IS
303 SELECT item_id
304 FROM rcv_shipment_lines
305 WHERE shipment_line_id = v_rcv_rec.shipment_line_id;
306
307 v_sub_rec sub_cur % ROWTYPE;
308 v_item_id rcv_shipment_lines.item_id % type;
309 v_unit_price number;
310 v_amount number;
311
312 /* Bug 5243532. Added by Lakshmi Gopalsami
313 * Defined variable for implementing caching logic.
314 */
315 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
316
317 BEGIN
318 v_sysdate := sysdate; --File.Sql.35 by Brathod
319 /* Bug 5243532. Added by Lakshmi Gopalsami
320 * Removed set_rec cursor and implemented caching logic.
321 */
322 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
323 (p_org_id => p_organization_id);
324 v_set_of_books_id := l_func_curr_det.ledger_id;
325
326 OPEN sub_cur;
327 FETCH sub_cur INTO v_sub_rec;
328 CLOSE sub_cur;
329
330 IF v_sub_rec.je_source_name IS NOT NULL
331 THEN
332
333 OPEN rcv_cur;
334 FETCH rcv_cur INTO v_rcv_rec;
335 CLOSE rcv_cur;
336
337 OPEN ship_rec;
338 FETCH ship_rec INTO v_item_id;
339 CLOSE ship_rec;
340
341 IF v_rcv_rec.source_document_code = 'PO'
342 THEN
343 For price_rec IN (SELECT price_override
344 FROM po_line_locations_all
345 WHERE line_location_id = v_rcv_rec.po_line_location_id)
346 LOOP
347 v_unit_price := price_rec.price_override;
348 END LOOP;
349 ELSIF v_rcv_rec.source_document_code = 'INVENTORY'
350 THEN
351 For price_rec IN (SELECT list_price_per_unit price
352 FROM mtl_system_items
353 WHERE inventory_item_id = v_item_id
354 AND organization_id = p_organization_id)
355 LOOP
356 v_unit_price := price_rec.price;
357 END LOOP;
358 ELSIF v_rcv_rec.source_document_code = 'REQ'
359 THEN
360 For price_rec IN (SELECT unit_price
361 FROM po_requisition_lines_all
362 WHERE requisition_line_id = v_rcv_rec.requisition_line_id)
363 LOOP
364 v_unit_price := price_rec.unit_price;
365 END LOOP;
366 END IF;
367
368 v_amount := NVL(p_credit_amount, p_debit_amount);
369
370 IF v_amount is NOT NULL and v_unit_price <> 0 -- Added by Ramakrishna to overcome zero divide
371 THEN
372 v_amount := v_amount / v_unit_price;
373 END IF;
374
375 IF p_accounting_date is null
376 THEN
377 v_accounting_date := v_sysdate;
378 ELSE
379 v_accounting_date := p_accounting_date;
380 END IF;
381
382 IF NVL(p_credit_amount, 0) <> 0 OR
383 NVL(p_debit_amount, 0) <> 0
384 THEN
385 INSERT into JAI_RCV_SUBLED_ENTRIES
386 (SUBLED_ENTRY_ID,rcv_transaction_id,
387 set_of_books_id,
388 je_source_name,
389 je_category_name,
390 accounting_date,
391 currency_code,
392 date_created_in_gl,
393 entered_cr,
394 entered_dr,
395 transaction_date,
396 code_combination_id,
397 currency_conversion_date,
398 user_currency_conversion_type,
399 currency_conversion_rate,
400 actual_flag,
401 period_name,
402 chart_of_accounts_id,
403 functional_currency_code,
404 je_batch_name,
405 je_batch_description,
406 je_header_name,
407 je_line_description,
408 reference1,
409 reference2,
410 reference3,
411 reference4,
412 source_doc_quantity,
413 created_by,
414 creation_date,
415 last_update_date,
416 last_updated_by,
417 last_update_login,
418 from_type,
419 PROGRAM_LOGIN_ID)
420 VALUES ( JAI_RCV_SUBLED_ENTRIES_S.nextval, p_transaction_id,
421 v_set_of_books_id,
422 v_sub_rec.je_source_name,
423 v_sub_rec.je_category_name,
424 v_accounting_date,
425 p_currency_code,
426 v_sysdate,
427 p_credit_amount,
428 p_debit_amount,
429 v_accounting_date,
430 p_cc_id,
431 p_currency_conversion_date,
432 p_currency_conversion_type,
433 p_currency_conversion_rate,
434 v_sub_rec.actual_flag,
435 v_sub_rec.period_name,
436 v_sub_rec.chart_of_accounts_id,
437 v_sub_rec.functional_currency_code,
438 v_sub_rec.je_batch_name,
439 v_sub_rec.je_batch_description,
440 v_sub_rec.je_header_name,
441 v_sub_rec.je_line_description,
442 v_sub_rec.reference1,
443 v_sub_rec.reference2,
444 v_sub_rec.reference3,
445 v_sub_rec.reference4,
446 v_amount,
447 p_created_by,
448 v_sysdate,
449 v_sysdate,
450 p_created_by,
451 p_created_by,
452 'L',
453 fnd_profile.value('PROG_APPL_ID'));
454 END IF;
455 END IF;
456 EXCEPTION
457 WHEN OTHERS THEN
458 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
459 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
460 app_exception.raise_exception;
461 END create_subledger_entry;
462
463 END jai_rcv_journal_pkg;