[Home] [Help]
PACKAGE BODY: APPS.JAI_CMN_RG_PLA_TRXS_PKG
Source
1 PACKAGE BODY jai_cmn_rg_pla_trxs_pkg AS
2 /* $Header: jai_cmn_rg_pla.plb 120.4 2007/08/07 07:46:46 vkaranam ship $ */
3
4 /* --------------------------------------------------------------------------------------
5 Filename:
6
7 Change History:
8
9 Date Bug Remarks
10 --------- ---------- -------------------------------------------------------------
11 08-Jun-2005 Version 116.2 jai_cmn_rg_pla -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 14/07/2005 4485801 Brathod, File Version 117.1
15 Issue: Inventory Convergence Uptake for R12 Initiative
16
17 28/12/2005 4892111 Hjujjuru, File Version 120.2
18 Issue : In the insert into the JAI_CMN_RG_PLA_TRXS , register_id
19 was inserted as JAI_CMN_RG_PLA_TRXS_S1.nextval. This is errorring
20 out.
21
22 Fix : Created a cursor to retrieve the register_id value from the
23 sequence and inserted the same into the JAI_CMN_RG_PLA_TRXS table
24
25 24/04/2007 Vijay Shankar for Bug# 6012570(5876390), Version:120.3 (115.7)
26 FP: Modified the code in get_trxn_type_and_id to return a transaction_id for Projects Billing
27
28 07/05/2007 Vkaranam for Bug# 6030615, Version:120.4
29 Forward Porting the changes done in 115 bug 2942973(Interorg Transfer)
30 FP: Modified the code in get_trxn_type_and_id to return a transaction_id for Interorg Transfer.
31
32 */
33
34 PROCEDURE insert_row(
35
36 P_REGISTER_ID OUT NOCOPY JAI_CMN_RG_PLA_TRXS.register_id%TYPE,
37 P_TR6_CHALLAN_NO IN JAI_CMN_RG_PLA_TRXS.tr6_challan_no%TYPE,
38 P_TR6_CHALLAN_DATE IN JAI_CMN_RG_PLA_TRXS.tr6_challan_date%TYPE,
39 P_CR_BASIC_ED IN JAI_CMN_RG_PLA_TRXS.cr_basic_ed%TYPE,
40 P_CR_ADDITIONAL_ED IN JAI_CMN_RG_PLA_TRXS.cr_additional_ed%TYPE,
41 P_CR_OTHER_ED IN JAI_CMN_RG_PLA_TRXS.cr_other_ed%TYPE,
42 P_REF_DOCUMENT_ID IN JAI_CMN_RG_PLA_TRXS.ref_document_id%TYPE,
43 P_REF_DOCUMENT_DATE IN JAI_CMN_RG_PLA_TRXS.ref_document_date%TYPE,
44 P_DR_INVOICE_ID IN JAI_CMN_RG_PLA_TRXS.DR_INVOICE_NO%TYPE,
45 P_DR_INVOICE_DATE IN JAI_CMN_RG_PLA_TRXS.dr_invoice_date%TYPE,
46 P_DR_BASIC_ED IN JAI_CMN_RG_PLA_TRXS.dr_basic_ed%TYPE,
47 P_DR_ADDITIONAL_ED IN JAI_CMN_RG_PLA_TRXS.dr_additional_ed%TYPE,
48 P_DR_OTHER_ED IN JAI_CMN_RG_PLA_TRXS.dr_other_ed%TYPE,
49 P_ORGANIZATION_ID IN JAI_CMN_RG_PLA_TRXS.organization_id%TYPE,
50 P_LOCATION_ID IN JAI_CMN_RG_PLA_TRXS.location_id%TYPE,
51 P_BANK_BRANCH_ID IN JAI_CMN_RG_PLA_TRXS.bank_branch_id%TYPE,
52 P_ENTRY_DATE IN JAI_CMN_RG_PLA_TRXS.entry_date%TYPE,
53 P_INVENTORY_ITEM_ID IN JAI_CMN_RG_PLA_TRXS.inventory_item_id%TYPE,
54 P_VENDOR_CUST_FLAG IN JAI_CMN_RG_PLA_TRXS.vendor_cust_flag%TYPE,
55 P_VENDOR_ID IN JAI_CMN_RG_PLA_TRXS.vendor_id%TYPE,
56 P_VENDOR_SITE_ID IN JAI_CMN_RG_PLA_TRXS.vendor_site_id%TYPE,
57 P_EXCISE_INVOICE_NO IN JAI_CMN_RG_PLA_TRXS.excise_invoice_no%TYPE,
58 P_REMARKS IN JAI_CMN_RG_PLA_TRXS.remarks%TYPE,
59 P_TRANSACTION_DATE IN JAI_CMN_RG_PLA_TRXS.transaction_date%TYPE,
60 P_CHARGE_ACCOUNT_ID IN JAI_CMN_RG_PLA_TRXS.charge_account_id%TYPE,
61 -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
62 P_OTHER_TAX_CREDIT IN JAI_CMN_RG_PLA_TRXS.other_tax_credit%TYPE,
63 P_OTHER_TAX_DEBIT IN JAI_CMN_RG_PLA_TRXS.other_tax_debit%TYPE,
64 p_transaction_type IN VARCHAR2,
65 p_transaction_source IN VARCHAR2,
66 p_called_from IN VARCHAR2,
67 P_SIMULATE_FLAG IN VARCHAR2,
68 P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
69 P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2,
70 p_rounding_id IN NUMBER default null -- Vijay Shankar for Bug#4103161
71 ) IS
72
73 ld_creation_date DATE;
74 ln_created_by NUMBER(15);
75 ld_last_update_date DATE;
76 ln_last_updated_by NUMBER(15);
77 ln_last_update_login NUMBER(15);
78
79 lv_last_register_id NUMBER;
80 ln_slno NUMBER(10) := 0;
81 ln_transaction_id NUMBER(10);
82 lv_transaction_type VARCHAR2(50);
83 ln_opening_balance NUMBER;
84 ln_closing_balance NUMBER;
85 ln_tr_amount NUMBER;
86
87 ln_fin_year NUMBER(4);
88 lv_range JAI_CMN_RG_PLA_TRXS.range_no%TYPE;
89 lv_division JAI_CMN_RG_PLA_TRXS.division_no%TYPE;
90 lv_master_flag JAI_CMN_RG_PLA_TRXS.master_flag%TYPE;
91
92 r_last_record c_get_last_record%ROWTYPE;
93 r_orgn_info c_orgn_info%ROWTYPE;
94 lv_message VARCHAR2(200);
95
96 ln_record_exist_cnt NUMBER(4);
97
98 lv_statement_id VARCHAR2(5);
99
100 -- added, Harshita for 4892111
101 Cursor c_fetch_register_id IS
102 select JAI_CMN_RG_PLA_TRXS_S1.nextval from dual ;
103
104 ln_register_id jai_cmn_rg_pla_trxs.register_id%type ;
105 -- ended, Harshita for 4892111
106
107 BEGIN
108
109 /*----------------------------------------------------------------------------------------------------------------------------
110 CHANGE HISTORY for FILENAME: jai_cmn_rg_pla_trxs_pkg.sql
111 S.No dd/mm/yyyy Author and Details
112 ------------------------------------------------------------------------------------------------------------------------------
113 1 16/07/2002 Vijay Shankar for Bug# 3496408, Version:115.0
114 Table Handler coded for PLA table. Update_row of the package was just a skeleton that needs to be modified
115 whenever it is being used
116
117 2 03/01/2005 Vijay Shankar for Bug# 3940588, Version:115.1
118 Modified Insert and Update procedures to include p_other_tax_credit and p_other_tax_debit parameters for
119 Education Cess Enhancement
120
121 3 19/04/2005 Vijay Shankar for Bug# 4103161, Version:115.2
122 added a parameter ROUNDING_ID in insert and update procedures as part of RTV Rounding
123
124 * dependancy for later versions of this package *
125
126 Dependancy:
127 -----------
128 IN60105D2 + 3496408
129 IN60106 + 3940588 + 4103161
130
131 ----------------------------------------------------------------------------------------------------------------------------*/
132
133
134 lv_transaction_type := p_transaction_type;
135
136 lv_statement_id := '1';
137 get_trxn_type_and_id(lv_transaction_type, p_transaction_source, ln_transaction_id);
138
139 lv_statement_id := '2';
140 ln_record_exist_cnt := get_trxn_entry_cnt(p_organization_id, p_location_id,
141 p_inventory_item_id, p_ref_document_id, ln_transaction_id);
142
143 IF ln_record_exist_cnt > 0 THEN
144 p_process_status := 'X';
145 p_process_message := 'PLA Entry was already made for the transaction';
146 GOTO end_of_processing;
147 END IF;
148
149 lv_statement_id := '3';
150 ln_tr_amount := ( nvl(p_cr_basic_ed,0) + nvl(p_cr_additional_ed,0)+ nvl(p_cr_other_ed,0))
151 - ( nvl(p_dr_basic_ed,0) + nvl(p_dr_additional_ed,0)+ nvl(p_dr_other_ed,0));
152
153 lv_statement_id := '4';
154 OPEN c_orgn_info(p_organization_id, p_location_id);
155 FETCH c_orgn_info INTO r_orgn_info;
156 CLOSE c_orgn_info;
157
158 lv_statement_id := '5';
159 ln_fin_year := jai_general_pkg.get_fin_year(p_organization_id);
160
161 lv_statement_id := '6';
162 lv_last_register_id := jai_general_pkg.get_last_record_of_rg
163 ('PLA', p_organization_id, p_location_id, p_inventory_item_id, ln_fin_year);
164
165 IF lv_last_register_id IS NULL THEN
166 IF r_orgn_info.ssi_unit_flag = jai_general_pkg.NO THEN
167 -- this is not an Error Condition
168 lv_message := 'PLA Register doesnt have sufficient balances';
169 GOTO end_of_processing;
170 ELSE
171 ln_slno := 1;
172 END IF;
173
174 ELSE
175 lv_statement_id := '7';
176 OPEN c_get_last_record(lv_last_register_id);
177 FETCH c_get_last_record INTO r_last_record;
178 CLOSE c_get_last_record;
179
180 IF r_last_record.fin_year = ln_fin_year THEN
181 ln_slno := nvl(r_last_record.slno, 0) + 1;
182 -- Start the serial number again in the new financial year
183 ELSE
184 ln_slno := 1;
185 END IF;
186 END IF;
187
188 lv_statement_id := '8';
189 jai_cmn_rg_balances_pkg.get_balance(
190 P_ORGANIZATION_ID => p_organization_id,
191 P_LOCATION_ID => p_location_id,
192 P_REGISTER_TYPE => 'PLA',
193 P_OPENING_BALANCE => ln_opening_balance,
194 P_PROCESS_STATUS => p_process_status,
195 P_PROCESS_MESSAGE => p_process_message
196 );
197
198 ln_closing_balance := ln_opening_balance + ln_tr_amount;
199
200 IF r_orgn_info.ssi_unit_flag = jai_general_pkg.NO THEN
201 -- *** check whether the balances are enough
202 IF ln_closing_balance < 0 THEN
203 lv_message := 'PLA Register doesnt have sufficient balances';
204 GOTO end_of_processing;
205 END IF;
206 END IF;
207
208 ld_creation_date := SYSDATE;
209 ln_created_by := FND_GLOBAL.user_id;
210 ld_last_update_date := SYSDATE;
211 ln_last_updated_by := ln_created_by;
212 ln_last_update_login := FND_GLOBAL.login_id;
213
214 lv_statement_id := '9';
215 lv_master_flag := jai_general_pkg.get_orgn_master_flag(p_organization_id, p_location_id);
216
217 lv_statement_id := '10';
218 jai_general_pkg.get_range_division(p_vendor_id, p_vendor_site_id, lv_range, lv_division);
219
220 -- added, Harshita for 4892111
221 OPEN c_fetch_register_id ;
222 FETCH c_fetch_register_id INTO ln_register_id ;
223 CLOSE c_fetch_register_id ;
224 -- ended, Harshita for 4892111
225
226 INSERT INTO JAI_CMN_RG_PLA_TRXS(
227 REGISTER_ID,
228 FIN_YEAR,
229 SLNO,
230 TR6_CHALLAN_NO,
231 TR6_CHALLAN_DATE,
232 CR_BASIC_ED,
233 CR_ADDITIONAL_ED,
234 CR_OTHER_ED,
235 TRANSACTION_SOURCE_NUM,
236 REF_DOCUMENT_ID,
237 REF_DOCUMENT_DATE,
238 DR_INVOICE_NO,
239 DR_INVOICE_DATE,
240 DR_BASIC_ED,
241 DR_ADDITIONAL_ED,
242 DR_OTHER_ED,
243 ORGANIZATION_ID,
244 LOCATION_ID,
245 BANK_BRANCH_ID,
246 ENTRY_DATE,
247 INVENTORY_ITEM_ID,
248 VENDOR_CUST_FLAG,
249 VENDOR_ID,
250 VENDOR_SITE_ID,
251 RANGE_NO,
252 DIVISION_NO,
253 EXCISE_INVOICE_NO,
254 REMARKS,
255 TRANSACTION_DATE,
256 OPENING_BALANCE,
257 CLOSING_BALANCE,
258 CHARGE_ACCOUNT_ID,
259 CREATION_DATE,
260 CREATED_BY,
261 LAST_UPDATE_DATE,
262 LAST_UPDATED_BY,
263 LAST_UPDATE_LOGIN,
264 POSTED_FLAG,
265 MASTER_FLAG,
266 BASIC_OPENING_BALANCE,
267 BASIC_CLOSING_BALANCE,
268 ADDITIONAL_OPENING_BALANCE,
269 ADDITIONAL_CLOSING_BALANCE,
270 OTHER_OPENING_BALANCE,
271 OTHER_CLOSING_BALANCE,
272 -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
273 other_tax_credit,
274 other_tax_debit,
275 rounding_id
276 ) VALUES (
277 ln_register_id, --JAI_CMN_RG_PLA_TRXS_S1.nextval, Harshita for 4892111 --P_REGISTER_ID,
278 ln_fin_year, --P_FIN_YEAR,
279 ln_slno, --P_SLNO,
280 P_TR6_CHALLAN_NO,
281 P_TR6_CHALLAN_DATE,
282 P_CR_BASIC_ED,
283 P_CR_ADDITIONAL_ED,
284 P_CR_OTHER_ED,
285 ln_transaction_id, --P_TRANSACTION_ID,
286 P_REF_DOCUMENT_ID,
287 P_REF_DOCUMENT_DATE,
288 P_DR_INVOICE_ID,
289 P_DR_INVOICE_DATE,
290 P_DR_BASIC_ED,
291 P_DR_ADDITIONAL_ED,
292 P_DR_OTHER_ED,
293 P_ORGANIZATION_ID,
294 P_LOCATION_ID,
295 P_BANK_BRANCH_ID,
296 P_ENTRY_DATE,
297 P_INVENTORY_ITEM_ID,
298 P_VENDOR_CUST_FLAG,
299 P_VENDOR_ID,
300 P_VENDOR_SITE_ID,
301 lv_range, --P_RANGE_NO,
302 lv_division, --P_DIVISION_NO,
303 P_EXCISE_INVOICE_NO,
304 P_REMARKS,
305 P_TRANSACTION_DATE,
306 ln_opening_balance, --P_OPENING_BALANCE,
307 ln_closing_balance, --P_CLOSING_BALANCE,
308 P_CHARGE_ACCOUNT_ID,
309 ld_creation_date, --P_CREATION_DATE,
310 ln_created_by, --P_CREATED_BY,
311 ld_last_update_date, --P_LAST_UPDATE_DATE,
312 ln_last_updated_by, --P_LAST_UPDATED_BY,
313 ln_last_update_login, --P_LAST_UPDATE_LOGIN,
314 'N', --P_POSTED_FLAG,
315 lv_master_flag, --P_MASTER_FLAG,
316 NULL, --P_BASIC_OPENING_BALANCE,
317 NULL, --P_BASIC_CLOSING_BALANCE,
318 NULL, --P_ADDITIONAL_OPENING_BALANCE,
319 NULL, --P_ADDITIONAL_CLOSING_BALANCE,
320 NULL, --P_OTHER_OPENING_BALANCE,
321 NULL, --P_OTHER_CLOSING_BALANCE
322 -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
323 p_other_tax_credit,
324 p_other_tax_debit,
325 p_rounding_id -- Vijay Shankar for Bug#4103161
326 ) RETURNING register_id INTO P_REGISTER_ID;
327
328 lv_statement_id := '11';
329 jai_cmn_rg_balances_pkg.update_row(
330 p_organization_id => p_organization_id,
331 p_location_id => p_location_id,
332 p_register_type => 'PLA',
333 p_amount_to_be_added=> ln_tr_amount,
334 p_simulate_flag => p_simulate_flag,
335 p_process_status => p_process_status,
336 p_process_message => p_process_message
337 );
338
339 <<end_of_processing>>
340
341 NULL;
342
343 EXCEPTION
344 WHEN OTHERS THEN
345 p_process_status := 'E';
346 p_process_message := 'PLA_PKG.insert_row->'||SQLERRM||', StmtId->'||lv_statement_id;
347 FND_FILE.put_line( FND_FILE.log, p_process_message);
348
349 END insert_row;
350
351 PROCEDURE update_row(
352
353 P_REGISTER_ID IN JAI_CMN_RG_PLA_TRXS.register_id%TYPE DEFAULT NULL,
354 P_FIN_YEAR IN JAI_CMN_RG_PLA_TRXS.fin_year%TYPE DEFAULT NULL,
355 P_SLNO IN JAI_CMN_RG_PLA_TRXS.slno%TYPE DEFAULT NULL,
356 P_TR6_CHALLAN_NO IN JAI_CMN_RG_PLA_TRXS.tr6_challan_no%TYPE DEFAULT NULL,
357 P_TR6_CHALLAN_DATE IN JAI_CMN_RG_PLA_TRXS.tr6_challan_date%TYPE DEFAULT NULL,
358 P_CR_BASIC_ED IN JAI_CMN_RG_PLA_TRXS.cr_basic_ed%TYPE DEFAULT NULL,
359 P_CR_ADDITIONAL_ED IN JAI_CMN_RG_PLA_TRXS.cr_additional_ed%TYPE DEFAULT NULL,
360 P_CR_OTHER_ED IN JAI_CMN_RG_PLA_TRXS.cr_other_ed%TYPE DEFAULT NULL,
361 P_TRANSACTION_ID IN JAI_CMN_RG_PLA_TRXS.TRANSACTION_SOURCE_NUM%TYPE DEFAULT NULL,
362 P_REF_DOCUMENT_ID IN JAI_CMN_RG_PLA_TRXS.ref_document_id%TYPE DEFAULT NULL,
363 P_REF_DOCUMENT_DATE IN JAI_CMN_RG_PLA_TRXS.ref_document_date%TYPE DEFAULT NULL,
364 P_DR_INVOICE_ID IN JAI_CMN_RG_PLA_TRXS.DR_INVOICE_NO%TYPE DEFAULT NULL,
365 P_DR_INVOICE_DATE IN JAI_CMN_RG_PLA_TRXS.dr_invoice_date%TYPE DEFAULT NULL,
366 P_DR_BASIC_ED IN JAI_CMN_RG_PLA_TRXS.dr_basic_ed%TYPE DEFAULT NULL,
367 P_DR_ADDITIONAL_ED IN JAI_CMN_RG_PLA_TRXS.dr_additional_ed%TYPE DEFAULT NULL,
368 P_DR_OTHER_ED IN JAI_CMN_RG_PLA_TRXS.dr_other_ed%TYPE DEFAULT NULL,
369 P_ORGANIZATION_ID IN JAI_CMN_RG_PLA_TRXS.organization_id%TYPE DEFAULT NULL,
370 P_LOCATION_ID IN JAI_CMN_RG_PLA_TRXS.location_id%TYPE DEFAULT NULL,
371 P_BANK_BRANCH_ID IN JAI_CMN_RG_PLA_TRXS.bank_branch_id%TYPE DEFAULT NULL,
372 P_ENTRY_DATE IN JAI_CMN_RG_PLA_TRXS.entry_date%TYPE DEFAULT NULL,
373 P_INVENTORY_ITEM_ID IN JAI_CMN_RG_PLA_TRXS.inventory_item_id%TYPE DEFAULT NULL,
374 P_VENDOR_CUST_FLAG IN JAI_CMN_RG_PLA_TRXS.vendor_cust_flag%TYPE DEFAULT NULL,
375 P_VENDOR_ID IN JAI_CMN_RG_PLA_TRXS.vendor_id%TYPE DEFAULT NULL,
376 P_VENDOR_SITE_ID IN JAI_CMN_RG_PLA_TRXS.vendor_site_id%TYPE DEFAULT NULL,
377 P_RANGE_NO IN JAI_CMN_RG_PLA_TRXS.range_no%TYPE DEFAULT NULL,
378 P_DIVISION_NO IN JAI_CMN_RG_PLA_TRXS.division_no%TYPE DEFAULT NULL,
379 P_EXCISE_INVOICE_NO IN JAI_CMN_RG_PLA_TRXS.excise_invoice_no%TYPE DEFAULT NULL,
380 P_REMARKS IN JAI_CMN_RG_PLA_TRXS.remarks%TYPE DEFAULT NULL,
381 P_TRANSACTION_DATE IN JAI_CMN_RG_PLA_TRXS.transaction_date%TYPE DEFAULT NULL,
382 P_OPENING_BALANCE IN JAI_CMN_RG_PLA_TRXS.opening_balance%TYPE DEFAULT NULL,
383 P_CLOSING_BALANCE IN JAI_CMN_RG_PLA_TRXS.closing_balance%TYPE DEFAULT NULL,
384 P_CHARGE_ACCOUNT_ID IN JAI_CMN_RG_PLA_TRXS.charge_account_id%TYPE DEFAULT NULL,
385 P_POSTED_FLAG IN JAI_CMN_RG_PLA_TRXS.posted_flag%TYPE DEFAULT NULL,
386 P_MASTER_FLAG IN JAI_CMN_RG_PLA_TRXS.master_flag%TYPE DEFAULT NULL,
387 -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
388 P_OTHER_TAX_CREDIT IN JAI_CMN_RG_PLA_TRXS.other_tax_credit%TYPE DEFAULT NULL,
389 P_OTHER_TAX_DEBIT IN JAI_CMN_RG_PLA_TRXS.other_tax_debit%TYPE DEFAULT NULL,
390 p_rounding_id IN NUMBER default null -- Vijay Shankar for Bug#4103161
391 ) IS
392
393 /* Added by Ramananda for bug#4407165 */
394 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_pla_trxs_pkg.update_row';
395
396 BEGIN
397
398 UPDATE JAI_CMN_RG_PLA_TRXS SET
399 REGISTER_ID = nvl(P_REGISTER_ID, REGISTER_ID),
400 FIN_YEAR = nvl(P_FIN_YEAR, FIN_YEAR),
401 SLNO = nvl(P_SLNO, SLNO),
402 TR6_CHALLAN_NO = nvl(P_TR6_CHALLAN_NO, TR6_CHALLAN_NO),
403 TR6_CHALLAN_DATE = nvl(P_TR6_CHALLAN_DATE, TR6_CHALLAN_DATE),
404 CR_BASIC_ED = nvl(P_CR_BASIC_ED, CR_BASIC_ED),
405 CR_ADDITIONAL_ED = nvl(P_CR_ADDITIONAL_ED, CR_ADDITIONAL_ED),
406 CR_OTHER_ED = nvl(P_CR_OTHER_ED, CR_OTHER_ED),
407 TRANSACTION_SOURCE_NUM = nvl(P_TRANSACTION_ID, TRANSACTION_SOURCE_NUM),
408 REF_DOCUMENT_ID = nvl(P_REF_DOCUMENT_ID, REF_DOCUMENT_ID),
409 REF_DOCUMENT_DATE = nvl(P_REF_DOCUMENT_DATE, REF_DOCUMENT_DATE),
410 DR_INVOICE_NO = nvl(P_DR_INVOICE_ID, DR_INVOICE_NO),
411 DR_INVOICE_DATE = nvl(P_DR_INVOICE_DATE, DR_INVOICE_DATE),
412 DR_BASIC_ED = nvl(P_DR_BASIC_ED, DR_BASIC_ED),
413 DR_ADDITIONAL_ED = nvl(P_DR_ADDITIONAL_ED, DR_ADDITIONAL_ED),
414 DR_OTHER_ED = nvl(P_DR_OTHER_ED, DR_OTHER_ED),
415 ORGANIZATION_ID = nvl(P_ORGANIZATION_ID, ORGANIZATION_ID),
416 LOCATION_ID = nvl(P_LOCATION_ID, LOCATION_ID),
417 BANK_BRANCH_ID = nvl(P_BANK_BRANCH_ID, BANK_BRANCH_ID),
418 ENTRY_DATE = nvl(P_ENTRY_DATE, ENTRY_DATE),
419 INVENTORY_ITEM_ID = nvl(P_INVENTORY_ITEM_ID, INVENTORY_ITEM_ID),
420 VENDOR_CUST_FLAG = nvl(P_VENDOR_CUST_FLAG, VENDOR_CUST_FLAG),
421 VENDOR_ID = nvl(P_VENDOR_ID, VENDOR_ID),
422 VENDOR_SITE_ID = nvl(P_VENDOR_SITE_ID, VENDOR_SITE_ID),
423 RANGE_NO = nvl(P_RANGE_NO, RANGE_NO),
424 DIVISION_NO = nvl(P_DIVISION_NO, DIVISION_NO),
425 EXCISE_INVOICE_NO = nvl(P_EXCISE_INVOICE_NO, EXCISE_INVOICE_NO),
426 REMARKS = nvl(P_REMARKS, REMARKS),
427 TRANSACTION_DATE = nvl(P_TRANSACTION_DATE, TRANSACTION_DATE),
428 OPENING_BALANCE = nvl(P_OPENING_BALANCE, OPENING_BALANCE),
429 CLOSING_BALANCE = nvl(P_CLOSING_BALANCE, CLOSING_BALANCE),
430 CHARGE_ACCOUNT_ID = nvl(P_CHARGE_ACCOUNT_ID, CHARGE_ACCOUNT_ID),
431 LAST_UPDATE_DATE = sysdate,
432 LAST_UPDATED_BY = fnd_global.user_id,
433 LAST_UPDATE_LOGIN = fnd_global.login_id,
434 POSTED_FLAG = nvl(P_POSTED_FLAG, POSTED_FLAG),
435 MASTER_FLAG = nvl(P_MASTER_FLAG, MASTER_FLAG),
436 -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
437 OTHER_TAX_CREDIT = nvl(P_OTHER_TAX_CREDIT, OTHER_TAX_CREDIT),
438 OTHER_TAX_debit = nvl(P_OTHER_TAX_debit, OTHER_TAX_debit),
439 rounding_id = nvl(p_rounding_id, rounding_id) -- Vijay Shankar for Bug#4103161
440 WHERE register_id = p_register_id;
441
442 /* Added by Ramananda for bug#4407165 */
443 EXCEPTION
444 WHEN OTHERS THEN
445 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
446 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
447 app_exception.raise_exception;
448
449 END update_row;
450
451 PROCEDURE update_payment_details(
452 p_register_id IN NUMBER,
453 p_charge_account_id IN NUMBER
454 ) IS
455
456 BEGIN
457
458 UPDATE JAI_CMN_RG_PLA_TRXS
459 SET charge_account_id = p_charge_account_id,
460 last_update_date= SYSDATE
461 WHERE register_id = p_register_id;
462
463 END update_payment_details;
464
465 FUNCTION get_trxn_entry_cnt(
466 p_organization_id IN NUMBER,
467 p_location_id IN NUMBER,
468 p_inventory_item_id IN NUMBER,
469 p_ref_document_id IN NUMBER,
470 p_transaction_id IN NUMBER
471 ) RETURN NUMBER IS
472
473 ln_record_exist_cnt NUMBER(4);
474 CURSOR c_record_exist IS
475 SELECT count(1)
476 FROM JAI_CMN_RG_PLA_TRXS
477 WHERE organization_id = p_organization_id
478 AND location_id = p_location_id
479 AND inventory_item_id = p_inventory_item_id
480 AND ref_document_id = p_ref_document_id
481 AND transaction_source_num = p_transaction_id;
482
483 BEGIN
484
485 OPEN c_record_exist;
486 FETCH c_record_exist INTO ln_record_exist_cnt;
487 CLOSE c_record_exist;
488
489 IF ln_record_exist_cnt > 0 THEN
490 FND_FILE.put_line( FND_FILE.log, 'PLA Duplicate Chk:'||ln_record_exist_cnt
491 ||', PARAMS: Orgn>'||p_organization_id||', Loc>'||p_location_id
492 ||', Item>'||p_inventory_item_id
493 ||', TrxId>'||p_ref_document_id||', type>'||p_transaction_id
494 );
495 END IF;
496
497 RETURN ln_record_exist_cnt;
498
499 END get_trxn_entry_cnt;
500
501 ----------------------- Get transaction id -------------------------------------------
502 PROCEDURE get_trxn_type_and_id(
503 p_transaction_type IN OUT NOCOPY VARCHAR2,
504 p_transaction_source IN VARCHAR2,
505 p_transaction_id OUT NOCOPY NUMBER
506 ) IS
507
508 BEGIN
509 IF p_transaction_type = 'RECEIVE' AND p_transaction_source = 'RMA' THEN
510 p_transaction_id := 18;
511 p_transaction_type := 'CR';
512 ELSIF p_transaction_type = 'RECEIVE' THEN
513 p_transaction_id := 18;
514 p_transaction_type := 'R';
515 ELSIF p_transaction_type = 'RETURN TO RECEIVING' THEN
516 p_transaction_id := 18;
517 p_transaction_type := 'R';
518 ELSIF p_transaction_type = 'DELIVER' THEN
519 p_transaction_id := 18;
520 p_transaction_type := 'R';
521 ELSIF p_transaction_type = 'RETURN TO VENDOR' THEN
522 p_transaction_id := 19;
523 p_transaction_type := 'RTV';
524 ELSIF p_transaction_type = jai_constants.service_src_distribute_in THEN
525 p_transaction_id := 151;
526 p_transaction_type := jai_constants.service_src_distribute_in;
527 ELSIF p_transaction_type = jai_constants.service_src_distribute_out THEN
528 p_transaction_id := 152;
529 p_transaction_type := jai_constants.service_src_distribute_out ;
530 -- Added by Brathod, for Inv.Convergence
531 ELSIF p_transaction_source = 'OPM_OSP' AND p_transaction_type ='I' THEN
532 p_transaction_type := 201;
533 ELSIF p_transaction_source = 'OPM_OSP' AND p_transaction_type ='R' THEN
534 p_transaction_type := 202;
535
536 /* cbabu for bug# 6012570 (5876390). Projects Billing implementation */
537 ELSIF p_transaction_type = 'DRAFT_INVOICE' and p_transaction_source = 'DRAFT_INVOICE_RELEASE' then
538 p_transaction_id := 30;
539 p_transaction_type := 'PROJECTS-BILLING';
540 /*added by vkaranam for bug 6030615 */
541 --start
542 ELSIF p_transaction_type='INTERORG_XFER' and p_transaction_source='Direct Org Transfer' then
543 p_transaction_id := 3;
544 ELSIF p_transaction_type='INTERORG_XFER' and p_transaction_source='Intransit Shipment' then
545 p_transaction_id := 21;
546 --end 6030615
547
548 ELSE
549 p_transaction_id := 20;
550 p_transaction_type := 'MISC';
551 END IF;
552
553 END get_trxn_type_and_id;
554
555 PROCEDURE generate_component_balances
556 (
557 errbuf VARCHAR2,
558 retcode VARCHAR2
559 )
560 IS
561 CURSOR FETCH_REGISTER_DETAILS IS
562 SELECT
563 A.ORGANIZATION_ID,
564 A.LOCATION_ID ,
565 A.INVENTORY_ITEM_ID,
566 A.FIN_YEAR,
567 A.REGISTER_ID,
568 A.SLNO,
569 A.CR_BASIC_ED,
570 A.CR_ADDITIONAL_ED,
571 A.CR_OTHER_ED,
572 A.DR_BASIC_ED,
573 A.DR_ADDITIONAL_ED,
574 A.DR_OTHER_ED,
575 A.CREATION_DATE,
576 A.CREATED_BY,
577 A.LAST_UPDATE_DATE,
578 A.LAST_UPDATED_BY,
579 A.TRANSACTION_DATE,
580 A.LAST_UPDATE_LOGIN
581 FROM JAI_CMN_RG_PLA_TRXS A
582 WHERE NOT EXISTS ( SELECT '1'
583 FROM JAI_CMN_RG_PLA_CMP_DTLS B
584 WHERE B.REGISTER_ID = A.REGISTER_ID
585 AND B.SLNO = A.SLNO)
586 ORDER BY REGISTER_ID,SLNO;
587
588 --Variable Declarations starts here.......
589 V_BASIC_OPENING_BALANCE NUMBER :=0;
590 V_ADDITIONAL_OPENING_BALANCE NUMBER :=0;
591 V_OTHER_OPENING_BALANCE NUMBER :=0;
592 V_BASIC_CLOSING_BALANCE NUMBER;
593 V_ADDITIONAL_CLOSING_BALANCE NUMBER;
594 V_OTHER_CLOSING_BALANCE NUMBER;
595 V_COUNT NUMBER;
596
597 v_commit_count NUMBER:=0;
598 --Variable Declarations Ends here..........
599
600
601 BEGIN --B1
602
603 /*------------------------------------------------------------------------------------------
604 FILENAME: jai_cmn_rg_pla_trxs_pkg.generate_component_balances.sql
605 CHANGE HISTORY:
606
607 1. 2002/07/28 Nagaraj.s - For Enh#2371031
608 This Procedure is Created for Textile Industry specifically wherin individual
609 balances of Excise components are to be maintained.
610 In case of data not existing in Excise Component Balances for an combination of
611 Organization/Location, this will inserts data into JAI_CMN_RG_COMP_BALS
612 and JAI_CMN_RG_PLA_CMP_DTLS tables and if data exists, then it will insert data
613 into JAI_CMN_RG_PLA_CMP_DTLS and updates JAI_CMN_RG_COMP_BALS table.
614
615 -----------------------------------------------------------------------------------------------
616 */
617
618
619 FOR CUR_REC IN FETCH_REGISTER_DETAILS
620 LOOP --L1
621 BEGIN
622 SELECT NVL(BASIC_PLA_BALANCE,0),
623 NVL(ADDITIONAL_PLA_BALANCE,0),
624 NVL(OTHER_PLA_BALANCE,0)
625 INTO V_BASIC_OPENING_BALANCE,
626 V_ADDITIONAL_OPENING_BALANCE,
627 V_OTHER_OPENING_BALANCE
628 FROM JAI_CMN_RG_COMP_BALS
629 WHERE ORGANIZATION_ID= CUR_REC.ORGANIZATION_ID AND
630 LOCATION_ID= CUR_REC.LOCATION_ID;
631
632 EXCEPTION
633 WHEN NO_DATA_FOUND THEN
634 --If the combination of Organization and Location do not exist then
635 INSERT INTO JAI_CMN_RG_COMP_BALS
636 (COMPONENT_BALANCE_ID,
637 ORGANIZATION_ID,
638 LOCATION_ID,
639 BASIC_RG23A_BALANCE,
640 ADDITIONAL_RG23A_BALANCE,
641 OTHER_RG23A_BALANCE,
642 BASIC_RG23C_BALANCE,
643 ADDITIONAL_RG23C_BALANCE,
644 OTHER_RG23C_BALANCE,
645 BASIC_PLA_BALANCE,
646 ADDITIONAL_PLA_BALANCE,
647 OTHER_PLA_BALANCE,
648 CREATION_DATE,
649 CREATED_BY,
650 LAST_UPDATE_DATE,
651 LAST_UPDATED_BY,
652 LAST_UPDATE_LOGIN
653 )
654 VALUES
655 ( JAI_CMN_RG_COMP_BALS_S.nextval,
656 CUR_REC.ORGANIZATION_ID,
657 CUR_REC.LOCATION_ID,
658 0,
659 0,
660 0,
661 0,
662 0,
663 0,
664 0,
665 0,
666 0,
667 TRUNC(SYSDATE),
668 CUR_REC.CREATED_BY,
669 TRUNC(SYSDATE),
670 CUR_REC.LAST_UPDATED_BY,
671 CUR_REC.LAST_UPDATE_LOGIN
672 );
673
674 V_BASIC_OPENING_BALANCE := 0;
675 V_ADDITIONAL_OPENING_BALANCE := 0;
676 V_OTHER_OPENING_BALANCE := 0;
677
678 END;
679
680
681 --Calculation of present Lines Closing Balances..........
682 BEGIN
683 V_BASIC_CLOSING_BALANCE := V_BASIC_OPENING_BALANCE + NVL(CUR_REC.CR_BASIC_ED,0) - NVL(CUR_REC.DR_BASIC_ED,0);
684 V_ADDITIONAL_CLOSING_BALANCE := V_ADDITIONAL_OPENING_BALANCE + NVL(CUR_REC.CR_ADDITIONAL_ED,0) - NVL(CUR_REC.DR_ADDITIONAL_ED,0);
685 V_OTHER_CLOSING_BALANCE := V_OTHER_OPENING_BALANCE + NVL(CUR_REC.CR_OTHER_ED,0) - NVL(CUR_REC.DR_OTHER_ED,0);
686 END;
687
688
689 INSERT INTO JAI_CMN_RG_PLA_CMP_DTLS
690 (
691 ORGANIZATION_ID,
692 LOCATION_ID,
693 INVENTORY_ITEM_ID,
694 FIN_YEAR,
695 REGISTER_ID,
696 SLNO ,
697 BASIC_OPENING_BALANCE,
698 ADDITIONAL_OPENING_BALANCE,
699 OTHER_OPENING_BALANCE,
700 CR_BASIC_ED,
701 CR_ADDITIONAL_ED,
702 CR_OTHER_ED ,
703 DR_BASIC_ED,
704 DR_ADDITIONAL_ED ,
705 DR_OTHER_ED,
706 BASIC_CLOSING_BALANCE,
707 ADDITIONAL_CLOSING_BALANCE,
708 OTHER_CLOSING_BALANCE,
709 CREATION_DATE ,
710 CREATED_BY ,
711 LAST_UPDATE_DATE ,
712 LAST_UPDATED_BY,
713 LAST_UPDATE_LOGIN,
714 TRANSACTION_DATE
715 )
716 VALUES
717 (
718 CUR_REC.ORGANIZATION_ID,
719 CUR_REC.LOCATION_ID,
720 CUR_REC.INVENTORY_ITEM_ID,
721 CUR_REC.FIN_YEAR,
722 CUR_REC.REGISTER_ID,
723 CUR_REC.SLNO,
724 V_BASIC_OPENING_BALANCE,
725 V_ADDITIONAL_OPENING_BALANCE,
726 V_OTHER_OPENING_BALANCE,
727 CUR_REC.CR_BASIC_ED,
728 CUR_REC.CR_ADDITIONAL_ED,
729 CUR_REC.CR_OTHER_ED,
730 CUR_REC.DR_BASIC_ED,
731 CUR_REC.DR_ADDITIONAL_ED,
732 CUR_REC.DR_OTHER_ED,
733 V_BASIC_CLOSING_BALANCE,
734 V_ADDITIONAL_CLOSING_BALANCE,
735 V_OTHER_CLOSING_BALANCE,
736 TRUNC(SYSDATE),
737 CUR_REC.CREATED_BY,
738 TRUNC(SYSDATE),
739 CUR_REC.LAST_UPDATED_BY,
740 CUR_REC.LAST_UPDATE_LOGIN,
741 CUR_REC.TRANSACTION_DATE
742 );
743
744 --To Update Register Balances.................
745 UPDATE JAI_CMN_RG_COMP_BALS
746 SET
747 BASIC_PLA_BALANCE = V_BASIC_CLOSING_BALANCE,
748 ADDITIONAL_PLA_BALANCE = V_ADDITIONAL_CLOSING_BALANCE,
749 OTHER_PLA_BALANCE = V_OTHER_CLOSING_BALANCE,
750 LAST_UPDATE_DATE = TRUNC(SYSDATE),
751 LAST_UPDATED_BY = CUR_REC.LAST_UPDATED_BY
752 WHERE ORGANIZATION_ID = CUR_REC.ORGANIZATION_ID AND
753 LOCATION_ID = CUR_REC.LOCATION_ID;
754 --Updation Ends here.....................................
755 -- COMMIT;
756 IF v_commit_count = 100 THEN
757 COMMIT;
758 v_commit_count := 0;
759 ELSE
760 v_commit_count := v_commit_count + 1;
761 END IF;
762 END LOOP; --L1
763
764 COMMIT;
765
766 EXCEPTION --Ex1
767 WHEN OTHERS THEN
768 ROLLBACK;
769 END generate_component_balances; --E1
770
771
772 END jai_cmn_rg_pla_trxs_pkg;