[Home] [Help]
PACKAGE BODY: APPS.JAI_CMN_RG_23AC_II_PKG
Source
1 PACKAGE BODY jai_cmn_rg_23ac_ii_pkg AS
2 /* $Header: jai_cmn_rg_23p2.plb 120.7 2007/08/07 06:14:49 rchandan ship $ */
3
4 /* --------------------------------------------------------------------------------------
5 Filename:
6
7 Change History:
8
9 Date Bug Remarks
10 --------- ---------- -------------------------------------------------------------
11 08-Jun-2005 Version 116.1 jai_cmn_rg_23p2 -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-Jun-2005 rchandan for bug#4428980, Version 116.2
15 Modified the object to remove literals from DML statements and CURSORS.
16
17 14/07/2005 4485801 Brathod, File Version 117.1
18 Issue: Inventory Convergence Uptake for R12 Initiative
19
20
21 01/11/2006 SACSETHI for bug 5228046, File version 120.3
22 Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
23 This bug has datamodel and spec changes.
24
25 24/04/2007 Vijay Shankar for Bug# 6012570(5876390), Version:120.5 (115.7)
26 FP: Modified the code in get_trxn_type_and_id to return a transaction_id for Projects Billing
27
28 08/05/2007 Arvind Goel - bug# 6030615 - version 120.6
29 added code to default the correct transsaction id based on the transaction types
30
31 01-08-2007 rchandan for bug#6030615 , Version 120.7
32 Issue : Inter org Forward porting
33 */
34
35 PROCEDURE insert_row(
36
37 P_REGISTER_ID OUT NOCOPY JAI_CMN_RG_23AC_II_TRXS.register_id%TYPE,
38 P_INVENTORY_ITEM_ID IN JAI_CMN_RG_23AC_II_TRXS.inventory_item_id%TYPE,
39 P_ORGANIZATION_ID IN JAI_CMN_RG_23AC_II_TRXS.organization_id%TYPE,
40 P_RECEIPT_ID IN JAI_CMN_RG_23AC_II_TRXS.RECEIPT_REF%TYPE,
41 P_RECEIPT_DATE IN JAI_CMN_RG_23AC_II_TRXS.receipt_date%TYPE,
42 P_CR_BASIC_ED IN JAI_CMN_RG_23AC_II_TRXS.cr_basic_ed%TYPE,
43 P_CR_ADDITIONAL_ED IN JAI_CMN_RG_23AC_II_TRXS.cr_additional_ed%TYPE,
44 P_CR_ADDITIONAL_CVD IN JAI_CMN_RG_23AC_II_TRXS.cr_additional_cvd%TYPE DEFAULT NULL, -- Date 01/11/2006 Bug 5228046 added by SACSETHI
45 P_CR_OTHER_ED IN JAI_CMN_RG_23AC_II_TRXS.cr_other_ed%TYPE,
46 P_DR_BASIC_ED IN JAI_CMN_RG_23AC_II_TRXS.dr_basic_ed%TYPE,
47 P_DR_ADDITIONAL_ED IN JAI_CMN_RG_23AC_II_TRXS.dr_additional_ed%TYPE,
48 P_DR_ADDITIONAL_CVD IN JAI_CMN_RG_23AC_II_TRXS.dr_additional_cvd%TYPE DEFAULT NULL, -- Date 01/11/2006 Bug 5228046 added by SACSETHI
49 P_DR_OTHER_ED IN JAI_CMN_RG_23AC_II_TRXS.dr_other_ed%TYPE,
50 P_EXCISE_INVOICE_NO IN JAI_CMN_RG_23AC_II_TRXS.excise_invoice_no%TYPE,
51 P_EXCISE_INVOICE_DATE IN JAI_CMN_RG_23AC_II_TRXS.excise_invoice_date%TYPE,
52 P_REGISTER_TYPE IN JAI_CMN_RG_23AC_II_TRXS.register_type%TYPE,
53 P_REMARKS IN JAI_CMN_RG_23AC_II_TRXS.remarks%TYPE,
54 P_VENDOR_ID IN JAI_CMN_RG_23AC_II_TRXS.vendor_id%TYPE,
55 P_VENDOR_SITE_ID IN JAI_CMN_RG_23AC_II_TRXS.vendor_site_id%TYPE,
56 P_CUSTOMER_ID IN JAI_CMN_RG_23AC_II_TRXS.customer_id%TYPE,
57 P_CUSTOMER_SITE_ID IN JAI_CMN_RG_23AC_II_TRXS.customer_site_id%TYPE,
58 P_LOCATION_ID IN JAI_CMN_RG_23AC_II_TRXS.location_id%TYPE,
59 P_TRANSACTION_DATE IN JAI_CMN_RG_23AC_II_TRXS.transaction_date%TYPE,
60 P_CHARGE_ACCOUNT_ID IN JAI_CMN_RG_23AC_II_TRXS.charge_account_id%TYPE,
61 P_REGISTER_ID_PART_I IN JAI_CMN_RG_23AC_II_TRXS.register_id_part_i%TYPE,
62 P_REFERENCE_NUM IN JAI_CMN_RG_23AC_II_TRXS.reference_num%TYPE,
63 P_ROUNDING_ID IN JAI_CMN_RG_23AC_II_TRXS.rounding_id%TYPE,
64 -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
65 P_OTHER_TAX_CREDIT IN JAI_CMN_RG_23AC_II_TRXS.other_tax_credit%TYPE,
66 P_OTHER_TAX_DEBIT IN JAI_CMN_RG_23AC_II_TRXS.other_tax_debit%TYPE,
67 p_transaction_type IN VARCHAR2,
68 P_TRANSACTION_SOURCE IN VARCHAR2,
69 P_CALLED_FROM IN VARCHAR2,
70 P_SIMULATE_FLAG IN VARCHAR2,
71 P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
72 P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2,
73 P_ADDITIONAL_CVD IN NUMBER DEFAULT NULL -- Harshita for bug 5096787
74 ) IS
75
76 ld_creation_date DATE;
77 ln_created_by NUMBER(15);
78 ld_last_update_date DATE;
79 ln_last_updated_by NUMBER(15);
80 ln_last_update_login NUMBER(15);
81
82 ln_last_register_id NUMBER;
83 ln_slno NUMBER(10) := 0;
84 ln_transaction_id NUMBER(10);
85 lv_transaction_type VARCHAR2(50);
86 ln_opening_balance NUMBER;
87 ln_closing_balance NUMBER;
88 ln_tr_amount NUMBER;
89
90 ln_fin_year NUMBER(4);
91 lv_range JAI_CMN_RG_23AC_II_TRXS.range_no%TYPE;
92 lv_division JAI_CMN_RG_23AC_II_TRXS.division_no%TYPE;
93 lv_master_flag JAI_CMN_RG_23AC_II_TRXS.master_flag%TYPE;
94 ln_rounding_id JAI_CMN_RG_23AC_II_TRXS.rounding_id%TYPE;
95
96 r_last_record c_get_last_record%ROWTYPE;
97
98 ln_record_exist_cnt NUMBER(4);
99 lv_statement_id VARCHAR2(5);
100
101 BEGIN
102
103 /*----------------------------------------------------------------------------------------------------------------------------
104 CHANGE HISTORY for FILENAME: jai_cmn_rg_23ac_ii_pkg.sql
105 S.No dd/mm/yyyy Author and Details
106 ------------------------------------------------------------------------------------------------------------------------------
107 1 16/07/2004 Vijay Shankar for Bug# 3496408, Version:115.0
108 Table handler Package for JAI_CMN_RG_23AC_II_TRXS table
109
110 2 03/01/2005 Vijay Shankar for Bug# 3940588, Version:115.1
111 Modified Insert and Update procedures to include p_other_tax_credit and p_other_tax_debit parameters for
112 Education Cess Enhancement
113
114 Dependancy:
115 -----------
116 IN60105D2 + 3496408
117 IN60106 + 3940588
118
119 ----------------------------------------------------------------------------------------------------------------------------*/
120
121 /* IMPORTANT NOTE:
122 For Receiving Transactions: In case of CGIN Claim a value is received for JAI_CMN_RG_23AC_II_TRXS.REFERENCE_NUM column
123 that will be used for Duplicate Checking.
124 Incase of RECEIVE transaction value received for 1st 50% Claim is '1st Claim'. During 2nd 50% Claim value received is '2nd Claim'
125 If 2nd Claim is happening from RTV transaction then TRANSACTION_ID of RTV is received as the value for REFERENCE_NUM
126 In all Other transactions value received for REFERENCE_NUM column is NULL
127 */
128
129 ld_creation_date := SYSDATE;
130 ln_created_by := FND_GLOBAL.user_id;
131 ld_last_update_date := SYSDATE;
132 ln_last_updated_by := ln_created_by;
133 ln_last_update_login := FND_GLOBAL.login_id;
134
135 lv_statement_id := '1';
136 ln_fin_year := jai_general_pkg.get_fin_year(p_organization_id);
137 lv_statement_id := '2';
138 lv_master_flag := jai_general_pkg.get_orgn_master_flag(p_organization_id, p_location_id);
139
140 lv_transaction_type := p_transaction_type;
141 lv_statement_id := '3';
142 get_trxn_type_and_id(lv_transaction_type, p_transaction_source, ln_transaction_id);
143
144 lv_statement_id := '4';
145 ln_record_exist_cnt := get_trxn_entry_cnt(p_register_type, p_organization_id, p_location_id, p_inventory_item_id,
146 p_receipt_id, ln_transaction_id, p_reference_num);
147
148 IF ln_record_exist_cnt > 0 THEN
149 p_process_status := 'X';
150 p_process_message := 'RG23 Part II Entry was already made for the transaction';
151 GOTO end_of_processing;
152 END IF;
153
154 lv_statement_id := '5';
155 jai_general_pkg.get_range_division(p_vendor_id, p_vendor_site_id, lv_range, lv_division);
156
157
158
159 -- Date 01/11/2006 Bug 5228046 added by SACSETHI
160
161 ln_tr_amount := ( nvl(p_cr_basic_ed,0) + nvl(p_cr_additional_ed,0)+ nvl(p_cr_other_ed,0) + nvl(p_cr_additional_cvd,0) )
162 - ( nvl(p_dr_basic_ed,0) + nvl(p_dr_additional_ed,0)+ nvl(p_dr_other_ed,0)+ nvl(p_dr_additional_cvd,0) );
163
164 lv_statement_id := '6';
165 ln_last_register_id := jai_general_pkg.get_last_record_of_rg
166 ('RG23'||p_register_type||'_2', p_organization_id, p_location_id, p_inventory_item_id, ln_fin_year);
167
168 IF ln_last_register_id IS NULL THEN
169 ln_slno := 1;
170 ELSE
171 lv_statement_id := '7';
172 OPEN c_get_last_record(ln_last_register_id);
173 FETCH c_get_last_record INTO r_last_record;
174 CLOSE c_get_last_record;
175
176 IF r_last_record.fin_year = ln_fin_year THEN
177 ln_slno := nvl(r_last_record.slno, 0) + 1;
178 -- Start the serial number again in the new financial year
179 ELSE
180 ln_slno := 1;
181 END IF;
182 END IF;
183
184 lv_statement_id := '8';
185 jai_cmn_rg_balances_pkg.get_balance(
186 P_ORGANIZATION_ID => p_organization_id,
187 P_LOCATION_ID => p_location_id,
188 P_REGISTER_TYPE => p_register_type,
189 P_OPENING_BALANCE => ln_opening_balance,
190 P_PROCESS_STATUS => p_process_status,
191 P_PROCESS_MESSAGE => p_process_message
192 );
193
194 ln_closing_balance := ln_opening_balance + ln_tr_amount;
195
196 IF p_rounding_id IS NOT NULL THEN
197 ln_rounding_id := p_rounding_id;
198 END IF;
199
200 lv_statement_id := '9';
201 INSERT INTO JAI_CMN_RG_23AC_II_TRXS(
202 REGISTER_ID,
203 FIN_YEAR,
204 SLNO,
205 TRANSACTION_SOURCE_NUM,
206 INVENTORY_ITEM_ID,
207 ORGANIZATION_ID,
208 RECEIPT_REF,
209 RECEIPT_DATE,
210 RANGE_NO,
211 DIVISION_NO,
212 CR_BASIC_ED,
213 CR_ADDITIONAL_ED,
214 CR_ADDITIONAL_CVD, -- Date 01/11/2006 Bug 5228046 added by SACSETHI
215 CR_OTHER_ED,
216 DR_BASIC_ED,
217 DR_ADDITIONAL_ED,
218 DR_ADDITIONAL_CVD, -- Date 01/11/2006 Bug 5228046 added by SACSETHI
219 DR_OTHER_ED,
220 EXCISE_INVOICE_NO,
221 EXCISE_INVOICE_DATE,
222 REGISTER_TYPE,
223 REMARKS,
224 VENDOR_ID,
225 VENDOR_SITE_ID,
226 CUSTOMER_ID,
227 CUSTOMER_SITE_ID,
228 LOCATION_ID,
229 TRANSACTION_DATE,
230 OPENING_BALANCE,
231 CLOSING_BALANCE,
232 CHARGE_ACCOUNT_ID,
233 REGISTER_ID_PART_I,
234 CREATION_DATE,
235 CREATED_BY,
236 LAST_UPDATE_DATE,
237 LAST_UPDATED_BY,
238 LAST_UPDATE_LOGIN,
239 POSTED_FLAG,
240 MASTER_FLAG,
241 REFERENCE_NUM,
242 ROUNDING_ID,
243 -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
244 other_tax_credit,
245 other_tax_debit
246 ) VALUES (
247 JAI_CMN_RG_23AC_II_TRXS_S.nextval, -- P_REGISTER_ID,
248 ln_fin_year, --P_FIN_YEAR,
249 ln_slno, --P_SLNO,
250 ln_transaction_id, --P_TRANSACTION_ID,
251 P_INVENTORY_ITEM_ID,
252 P_ORGANIZATION_ID,
253 P_RECEIPT_ID,
254 P_RECEIPT_DATE,
255 lv_range, --P_RANGE_NO,
256 lv_division, --P_DIVISION_NO,
257 P_CR_BASIC_ED,
258 P_CR_ADDITIONAL_ED,
259 P_CR_ADDITIONAL_CVD, -- Date 01/11/2006 Bug 5228046 added by SACSETHI
260 P_CR_OTHER_ED,
261 P_DR_BASIC_ED,
262 P_DR_ADDITIONAL_ED,
263 P_DR_ADDITIONAL_CVD, -- Date 01/11/2006 Bug 5228046 added by SACSETHI
264 P_DR_OTHER_ED,
265 P_EXCISE_INVOICE_NO,
266 P_EXCISE_INVOICE_DATE,
267 P_REGISTER_TYPE,
268 P_REMARKS,
269 P_VENDOR_ID,
270 P_VENDOR_SITE_ID,
271 P_CUSTOMER_ID,
272 P_CUSTOMER_SITE_ID,
273 P_LOCATION_ID,
274 P_TRANSACTION_DATE,
275 ln_opening_balance, --P_OPENING_BALANCE,
276 ln_closing_balance, --P_CLOSING_BALANCE,
277 P_CHARGE_ACCOUNT_ID,
278 P_REGISTER_ID_PART_I,
279 ld_creation_date, --P_CREATION_DATE,
280 ln_created_by, --P_CREATED_BY,
281 ld_last_update_date, --P_LAST_UPDATE_DATE,
282 ln_last_updated_by, --P_LAST_UPDATED_BY,
283 ln_last_update_login, --P_LAST_UPDATE_LOGIN,
284 'N',
285 lv_master_flag, --P_MASTER_FLAG,
286 P_REFERENCE_NUM,
287 ln_rounding_id, --P_ROUNDING_ID
288 -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
289 p_other_tax_credit,
290 p_other_tax_debit
291 ) RETURNING register_id INTO P_REGISTER_ID;
292
293 lv_statement_id := '10';
294 jai_cmn_rg_balances_pkg.update_row(
295 p_organization_id => p_organization_id,
296 p_location_id => p_location_id,
297 p_register_type => p_register_type,
298 p_amount_to_be_added=> ln_tr_amount,
299 p_simulate_flag => p_simulate_flag,
300 p_process_status => p_process_status,
301 p_process_message => p_process_message
302 );
303 --jai_general_pkg.update_rg_balances(p_organization_id, p_location_id, p_register_type,
304 -- ln_tr_amount, 'RECEIVING', 'RG23_II_PKG.insert_row');
305
306 <<end_of_processing>>
307
308 NULL;
309 --IF p_process_message IS NOT NULL THEN
310 -- p_process_status := 'E';
311 -- RETURN;
312 --END IF;
313
314 EXCEPTION
315 WHEN OTHERS THEN
316 p_process_status := 'E';
317 p_process_message := 'RG23_PART_II_PKG.insert_row->'||SQLERRM||', StmtId->'||lv_statement_id;
318 FND_FILE.put_line( FND_FILE.log, p_process_message);
319
320 END insert_row;
321
322 PROCEDURE update_row(
323
324 P_REGISTER_ID IN JAI_CMN_RG_23AC_II_TRXS.register_id%TYPE DEFAULT NULL,
325 P_FIN_YEAR IN JAI_CMN_RG_23AC_II_TRXS.fin_year%TYPE DEFAULT NULL,
326 P_SLNO IN JAI_CMN_RG_23AC_II_TRXS.slno%TYPE DEFAULT NULL,
327 P_TRANSACTION_ID IN JAI_CMN_RG_23AC_II_TRXS.TRANSACTION_SOURCE_NUM%TYPE DEFAULT NULL,
328 P_INVENTORY_ITEM_ID IN JAI_CMN_RG_23AC_II_TRXS.inventory_item_id%TYPE DEFAULT NULL,
329 P_ORGANIZATION_ID IN JAI_CMN_RG_23AC_II_TRXS.organization_id%TYPE DEFAULT NULL,
330 P_RECEIPT_ID IN JAI_CMN_RG_23AC_II_TRXS.RECEIPT_REF%TYPE DEFAULT NULL,
331 P_RECEIPT_DATE IN JAI_CMN_RG_23AC_II_TRXS.receipt_date%TYPE DEFAULT NULL,
332 P_RANGE_NO IN JAI_CMN_RG_23AC_II_TRXS.range_no%TYPE DEFAULT NULL,
333 P_DIVISION_NO IN JAI_CMN_RG_23AC_II_TRXS.division_no%TYPE DEFAULT NULL,
334 P_CR_BASIC_ED IN JAI_CMN_RG_23AC_II_TRXS.cr_basic_ed%TYPE DEFAULT NULL,
335 P_CR_ADDITIONAL_ED IN JAI_CMN_RG_23AC_II_TRXS.cr_additional_ed%TYPE DEFAULT NULL,
336 P_CR_ADDITIONAL_CVD IN JAI_CMN_RG_23AC_II_TRXS.cr_additional_cvd%TYPE DEFAULT NULL, -- Date 01/11/2006 Bug 5228046 added by SACSETHI
337 P_CR_OTHER_ED IN JAI_CMN_RG_23AC_II_TRXS.cr_other_ed%TYPE DEFAULT NULL,
338 P_DR_BASIC_ED IN JAI_CMN_RG_23AC_II_TRXS.dr_basic_ed%TYPE DEFAULT NULL,
339 P_DR_ADDITIONAL_ED IN JAI_CMN_RG_23AC_II_TRXS.dr_additional_ed%TYPE DEFAULT NULL,
340 P_DR_ADDITIONAL_CVD IN JAI_CMN_RG_23AC_II_TRXS.cr_additional_cvd%TYPE DEFAULT NULL, -- Date 01/11/2006 Bug 5228046 added by SACSETHI
344 P_REGISTER_TYPE IN JAI_CMN_RG_23AC_II_TRXS.register_type%TYPE DEFAULT NULL,
341 P_DR_OTHER_ED IN JAI_CMN_RG_23AC_II_TRXS.dr_other_ed%TYPE DEFAULT NULL,
342 P_EXCISE_INVOICE_NO IN JAI_CMN_RG_23AC_II_TRXS.excise_invoice_no%TYPE DEFAULT NULL,
343 P_EXCISE_INVOICE_DATE IN JAI_CMN_RG_23AC_II_TRXS.excise_invoice_date%TYPE DEFAULT NULL,
345 P_REMARKS IN JAI_CMN_RG_23AC_II_TRXS.remarks%TYPE DEFAULT NULL,
346 P_VENDOR_ID IN JAI_CMN_RG_23AC_II_TRXS.vendor_id%TYPE DEFAULT NULL,
347 P_VENDOR_SITE_ID IN JAI_CMN_RG_23AC_II_TRXS.vendor_site_id%TYPE DEFAULT NULL,
348 P_CUSTOMER_ID IN JAI_CMN_RG_23AC_II_TRXS.customer_id%TYPE DEFAULT NULL,
349 P_CUSTOMER_SITE_ID IN JAI_CMN_RG_23AC_II_TRXS.customer_site_id%TYPE DEFAULT NULL,
350 P_LOCATION_ID IN JAI_CMN_RG_23AC_II_TRXS.location_id%TYPE DEFAULT NULL,
351 P_TRANSACTION_DATE IN JAI_CMN_RG_23AC_II_TRXS.transaction_date%TYPE DEFAULT NULL,
352 P_OPENING_BALANCE IN JAI_CMN_RG_23AC_II_TRXS.opening_balance%TYPE DEFAULT NULL,
353 P_CLOSING_BALANCE IN JAI_CMN_RG_23AC_II_TRXS.closing_balance%TYPE DEFAULT NULL,
354 P_CHARGE_ACCOUNT_ID IN JAI_CMN_RG_23AC_II_TRXS.charge_account_id%TYPE DEFAULT NULL,
355 P_REGISTER_ID_PART_I IN JAI_CMN_RG_23AC_II_TRXS.register_id_part_i%TYPE DEFAULT NULL,
356 P_POSTED_FLAG IN JAI_CMN_RG_23AC_II_TRXS.posted_flag%TYPE DEFAULT NULL,
357 P_MASTER_FLAG IN JAI_CMN_RG_23AC_II_TRXS.master_flag%TYPE DEFAULT NULL,
358 P_REFERENCE_NUM IN JAI_CMN_RG_23AC_II_TRXS.reference_num%TYPE DEFAULT NULL,
359 P_ROUNDING_ID IN JAI_CMN_RG_23AC_II_TRXS.rounding_id%TYPE DEFAULT NULL,
360 -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
361 P_OTHER_TAX_CREDIT IN JAI_CMN_RG_23AC_II_TRXS.other_tax_credit%TYPE DEFAULT NULL,
362 P_OTHER_TAX_DEBIT IN JAI_CMN_RG_23AC_II_TRXS.other_tax_debit%TYPE DEFAULT NULL,
363 P_SIMULATE_FLAG IN VARCHAR2,
364 P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
365 P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2,
366 P_ADDITIONAL_CVD IN NUMBER DEFAULT NULL -- Harshita for bug 5096787
367 ) IS
368 BEGIN
369
370 UPDATE JAI_CMN_RG_23AC_II_TRXS SET
371 REGISTER_ID = nvl(P_REGISTER_ID, REGISTER_ID),
372 FIN_YEAR = nvl(P_FIN_YEAR, FIN_YEAR),
373 SLNO = nvl(P_SLNO, SLNO),
374 TRANSACTION_SOURCE_NUM = nvl(P_TRANSACTION_ID, TRANSACTION_SOURCE_NUM),
375 INVENTORY_ITEM_ID = nvl(P_INVENTORY_ITEM_ID, INVENTORY_ITEM_ID),
376 ORGANIZATION_ID = nvl(P_ORGANIZATION_ID, ORGANIZATION_ID),
377 RECEIPT_REF = nvl(P_RECEIPT_ID, RECEIPT_REF),
378 RECEIPT_DATE = nvl(P_RECEIPT_DATE, RECEIPT_DATE),
379 RANGE_NO = nvl(P_RANGE_NO, RANGE_NO),
380 DIVISION_NO = nvl(P_DIVISION_NO, DIVISION_NO),
381 CR_BASIC_ED = nvl(P_CR_BASIC_ED, CR_BASIC_ED),
382 CR_ADDITIONAL_ED = nvl(P_CR_ADDITIONAL_ED, CR_ADDITIONAL_ED),
383 CR_ADDITIONAL_CVD = nvl(P_CR_ADDITIONAL_CVD, CR_ADDITIONAL_CVD), -- Date 01/11/2006 Bug 5228046 added by SACSETHI
384 CR_OTHER_ED = nvl(P_CR_OTHER_ED, CR_OTHER_ED),
385 DR_BASIC_ED = nvl(P_DR_BASIC_ED, DR_BASIC_ED),
386 DR_ADDITIONAL_ED = nvl(P_DR_ADDITIONAL_ED, DR_ADDITIONAL_ED),
387 DR_ADDITIONAL_CVD = nvl(P_DR_ADDITIONAL_CVD, DR_ADDITIONAL_CVD), -- Date 01/11/2006 Bug 5228046 added by SACSETHI
388 DR_OTHER_ED = nvl(P_DR_OTHER_ED, DR_OTHER_ED),
389 EXCISE_INVOICE_NO = nvl(P_EXCISE_INVOICE_NO, EXCISE_INVOICE_NO),
390 EXCISE_INVOICE_DATE = nvl(P_EXCISE_INVOICE_DATE, EXCISE_INVOICE_DATE),
391 REGISTER_TYPE = nvl(P_REGISTER_TYPE, REGISTER_TYPE),
392 REMARKS = nvl(P_REMARKS, REMARKS),
393 VENDOR_ID = nvl(P_VENDOR_ID, VENDOR_ID),
394 VENDOR_SITE_ID = nvl(P_VENDOR_SITE_ID, VENDOR_SITE_ID),
395 CUSTOMER_ID = nvl(P_CUSTOMER_ID, CUSTOMER_ID),
396 CUSTOMER_SITE_ID = nvl(P_CUSTOMER_SITE_ID, CUSTOMER_SITE_ID),
397 LOCATION_ID = nvl(P_LOCATION_ID, LOCATION_ID),
398 TRANSACTION_DATE = nvl(P_TRANSACTION_DATE, TRANSACTION_DATE),
399 OPENING_BALANCE = nvl(P_OPENING_BALANCE, OPENING_BALANCE),
400 CLOSING_BALANCE = nvl(P_CLOSING_BALANCE, CLOSING_BALANCE),
401 CHARGE_ACCOUNT_ID = nvl(P_CHARGE_ACCOUNT_ID, CHARGE_ACCOUNT_ID),
402 REGISTER_ID_PART_I = nvl(P_REGISTER_ID_PART_I, REGISTER_ID_PART_I),
403 LAST_UPDATE_DATE = sysdate,
404 LAST_UPDATED_BY = fnd_global.user_id,
405 LAST_UPDATE_LOGIN = fnd_global.login_id,
406 POSTED_FLAG = nvl(P_POSTED_FLAG, POSTED_FLAG),
407 MASTER_FLAG = nvl(P_MASTER_FLAG, MASTER_FLAG),
408 REFERENCE_NUM = nvl(P_REFERENCE_NUM, REFERENCE_NUM),
409 ROUNDING_ID = nvl(P_ROUNDING_ID, ROUNDING_ID),
410 -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
414
411 OTHER_TAX_CREDIT = nvl(P_OTHER_TAX_CREDIT, OTHER_TAX_CREDIT),
412 OTHER_TAX_debit = nvl(P_OTHER_TAX_debit, OTHER_TAX_debit)
413 WHERE register_id = p_register_id;
415 END update_row;
416
417 PROCEDURE update_payment_details(
418 p_register_id IN NUMBER,
419 p_register_id_part_i IN NUMBER,
420 p_charge_account_id IN NUMBER
421 ) IS
422
423 BEGIN
424
425 UPDATE JAI_CMN_RG_23AC_II_TRXS
426 SET
427 register_id_part_i = p_register_id_part_i,
428 charge_account_id = p_charge_account_id,
429 last_update_date = SYSDATE
430 WHERE register_id = p_register_id;
431
432 END update_payment_details;
433
434 FUNCTION get_trxn_entry_cnt(
435 p_register_type IN VARCHAR2,
436 p_organization_id IN NUMBER,
437 p_location_id IN NUMBER,
438 p_inventory_item_id IN NUMBER,
439 p_receipt_id IN VARCHAR2,
440 p_transaction_id IN NUMBER,
441 p_reference_num IN VARCHAR2
442 ) RETURN NUMBER IS
443
444 ln_record_exist_cnt NUMBER(4);
445 CURSOR c_record_exist IS
446 SELECT count(1)
447 FROM JAI_CMN_RG_23AC_II_TRXS
448 WHERE organization_id = p_organization_id
449 AND location_id = p_location_id
450 AND inventory_item_id = p_inventory_item_id
451 AND register_type = p_register_type
452 AND receipt_ref = p_receipt_id
453 AND TRANSACTION_SOURCE_NUM = p_transaction_id
454 AND ((p_reference_num IS NULL AND reference_num IS NULL) OR reference_num = p_reference_num);
455
456 BEGIN
457
458 OPEN c_record_exist;
459 FETCH c_record_exist INTO ln_record_exist_cnt;
460 CLOSE c_record_exist;
461
462 IF ln_record_exist_cnt > 0 THEN
463 FND_FILE.put_line( FND_FILE.log, '23Part2 Duplicate Chk:'||ln_record_exist_cnt
464 ||', PARAMS: Orgn>'||p_organization_id||', Loc>'||p_location_id
465 ||', Item>'||p_inventory_item_id||', Reg>'||p_register_type
466 ||', TrxId>'||p_receipt_id||', type>'||p_transaction_id||', ref>'||p_reference_num
467 );
468 END IF;
469
470 RETURN ln_record_exist_cnt;
471
472 END get_trxn_entry_cnt;
473
474 ----------------------- Get transaction id -------------------------------------------
475 PROCEDURE get_trxn_type_and_id(
476 p_transaction_type IN OUT NOCOPY VARCHAR2,
477 p_transaction_source IN VARCHAR2,
478 p_transaction_id OUT NOCOPY NUMBER
479 ) IS
480
481 BEGIN
482
483 IF p_transaction_type = 'RECEIVE' AND p_transaction_source = 'RMA' THEN
484 p_transaction_id := 18;
485 p_transaction_type := 'CR';
486 ELSIF p_transaction_type = 'RECEIVE' THEN
487 p_transaction_id := 18;
488 p_transaction_type := 'R';
489 ELSIF p_transaction_type = 'RETURN TO RECEIVING' THEN
490 p_transaction_id := 18;
491 p_transaction_type := 'R';
492 ELSIF p_transaction_type = 'DELIVER' THEN
493 p_transaction_id := 18;
494 p_transaction_type := 'R';
495 ELSIF p_transaction_type = 'RETURN TO VENDOR' THEN
496 p_transaction_id := 18;
497 p_transaction_type := 'RTV';
498 ELSIF p_transaction_type = jai_constants.service_src_distribute_in THEN
499 p_transaction_id := 151;
500 p_transaction_type := jai_constants.service_src_distribute_in;
501 ELSIF p_transaction_type = jai_constants.service_src_distribute_out THEN
502 p_transaction_id := 152;
503 p_transaction_type := jai_constants.service_src_distribute_out ;
504 -- Added by Brathod, for Inv.Convergence
505 ELSIF p_transaction_source = 'OPM_OSP' AND p_transaction_type = 'I' THEN
506 p_transaction_id := 201;
507 ELSIF p_transaction_source = 'OPM_OSP' AND p_transaction_type = 'R' THEN
508 p_transaction_id := 202;
509
510 /* cbabu for bug# 6012570 (5876390). Projects Billing implementation */
511 ELSIF p_transaction_type = 'DRAFT_INVOICE' and p_transaction_source = 'DRAFT_INVOICE_RELEASE' then
512 p_transaction_id := 30;
513 p_transaction_type := 'PROJECTS-BILLING';
514
515 /* following two elsifs added by Arvind Goel - bug# 6030615 - interorg transfer*/
516 ELSIF p_transaction_type='INTERORG_XFER' and p_transaction_source='Direct Org Transfer' then
517 p_transaction_id := 3;
518 ELSIF p_transaction_type='INTERORG_XFER' and p_transaction_source='Intransit Shipment' then
519 p_transaction_id := 21;
520
521 ELSE
522 p_transaction_id := 20;
523 p_transaction_type := 'MISC';
524 END IF;
525
526 END get_trxn_type_and_id;
527
528 PROCEDURE generate_component_balances
529 (
530 errbuf VARCHAR2,
531 retcode VARCHAR2
532 ) IS
533
534 CURSOR FETCH_REGISTER_DETAILS IS
535 SELECT
536 A.ORGANIZATION_ID,
537 A.LOCATION_ID ,
538 A.INVENTORY_ITEM_ID,
539 A.FIN_YEAR,
540 A.REGISTER_ID,
541 A.SLNO,
542 A.REGISTER_TYPE,
543 A.CR_BASIC_ED,
544 A.CR_ADDITIONAL_ED,
545 A.CR_OTHER_ED,
546 A.DR_BASIC_ED,
547 A.DR_ADDITIONAL_ED,
548 A.DR_OTHER_ED,
549 A.CREATION_DATE,
550 A.CREATED_BY,
551 A.LAST_UPDATE_DATE,
552 A.LAST_UPDATED_BY,
553 A.LAST_UPDATE_LOGIN,
554 A.TRANSACTION_DATE
558 WHERE B.REGISTER_ID = A.REGISTER_ID)
555 FROM JAI_CMN_RG_23AC_II_TRXS A
556 WHERE NOT EXISTS ( SELECT '1'
557 FROM JAI_CMN_RG_COMP_DTLS B
559 ORDER BY ORGANIZATION_ID,LOCATION_ID,REGISTER_ID;
560 --Variable Declarations starts here.......
561 V_BASIC_OPENING_BALANCE NUMBER;
562 V_ADDITIONAL_OPENING_BALANCE NUMBER;
563 V_OTHER_OPENING_BALANCE NUMBER;
564 V_BASIC_OPENING_BALANCE_A NUMBER :=0;
565 V_ADDITIONAL_OPENING_BALANCE_A NUMBER :=0;
566 V_OTHER_OPENING_BALANCE_A NUMBER :=0;
567 V_BASIC_OPENING_BALANCE_C NUMBER:=0;
568 V_ADDITIONAL_OPENING_BALANCE_C NUMBER:=0;
569 V_OTHER_OPENING_BALANCE_C NUMBER:=0;
570 V_BASIC_CLOSING_BALANCE NUMBER;
571 V_ADDITIONAL_CLOSING_BALANCE NUMBER;
572 V_OTHER_CLOSING_BALANCE NUMBER;
573 V_COUNT NUMBER;
574 v_commit_count number:=0;
575 --Variable Declarations Ends here..........
576 --For UTL File..
577 v_myfilehandle UTL_FILE.FILE_TYPE; -- This is for File handling
578 v_utl_location VARCHAR2(512);
579 v_debug_flag VARCHAR2(1); -- := 'N' File.Sql.35 by Brathod
580 lv_name VARCHAR2(30); --rchandan for bug#4428980
581 --Ends here......
582 BEGIN --B1
583 /*------------------------------------------------------------------------------------------
584 FILENAME: jai_cmn_rg_23ac_ii_pkg.generate_component_balances.sql
585 CHANGE HISTORY:
586
587 1. 2002/07/28 Nagaraj.s - For Enh#2371031
588 This Procedure is Created for Textile Industry specifically wherin individual
589 balances of Excise components are to be maintained.
590 In case of data not existing in Excise Component Balances for an combination of
591 Organization/Location, this will inserts data into JAI_CMN_RG_COMP_BALS
592 and JAI_CMN_RG_COMP_DTLS tables and if data exists, then it will insert data
593 into JAI_CMN_RG_COMP_DTLS and updates JAI_CMN_RG_COMP_BALS table.
594
595 -----------------------------------------------------------------------------------------------
596 */
597 v_debug_flag := 'N';
598
599 IF v_debug_flag ='Y' THEN
600 BEGIN
601 lv_name := 'utl_file_dir';--rchandan for bug#4428980
602 SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,
603 Value,SUBSTR (value,1,INSTR(value,',') -1))
604 INTO v_utl_location
605 FROM v$parameter
606 WHERE name = lv_name;--rchandan for bug#4428980
607 EXCEPTION
608 WHEN OTHERS THEN
609 v_debug_flag:='N';
610 END;
611
612 v_myfilehandle := UTL_FILE.FOPEN(v_utl_location,'componentbalances.log','A');
613
614 UTL_FILE.PUT_LINE(v_myfilehandle,'************************Start************************************');
615 UTL_FILE.PUT_LINE(v_myfilehandle,'The Time Stamp this Entry is Created is ' ||TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI:SS'));
616 END IF;
617
618 FOR CUR_REC IN FETCH_REGISTER_DETAILS
619 LOOP --L1
620 IF V_DEBUG_FLAG='Y' THEN
621 UTL_FILE.PUT_LINE(v_myfilehandle,'After Loop starts' ||'The Organization id is ' || CUR_REC.ORGANIZATION_ID);
622 UTL_FILE.PUT_LINE(v_myfilehandle,'After Loop starts' ||'The Location id is ' || CUR_REC.LOCATION_ID);
623 UTL_FILE.PUT_LINE(v_myfilehandle,'After Loop starts' ||'The Register id is ' || CUR_REC.REGISTER_ID);
624 END IF;
625
626 BEGIN
627 SELECT NVL(BASIC_RG23A_BALANCE,0),
628 NVL(ADDITIONAL_RG23A_BALANCE,0),
629 NVL(OTHER_RG23A_BALANCE,0),
630 NVL(BASIC_RG23C_BALANCE,0),
631 NVL(ADDITIONAL_RG23C_BALANCE,0),
632 NVL(OTHER_RG23C_BALANCE,0)
633 INTO V_BASIC_OPENING_BALANCE_A,
634 V_ADDITIONAL_OPENING_BALANCE_A,
635 V_OTHER_OPENING_BALANCE_A,
636 V_BASIC_OPENING_BALANCE_C,
637 V_ADDITIONAL_OPENING_BALANCE_C,
638 V_OTHER_OPENING_BALANCE_C
639 FROM JAI_CMN_RG_COMP_BALS
640 WHERE ORGANIZATION_ID= CUR_REC.ORGANIZATION_ID AND
641 LOCATION_ID= CUR_REC.LOCATION_ID;
642 EXCEPTION
643 WHEN NO_DATA_FOUND THEN
644 V_BASIC_OPENING_BALANCE_A := 0;
645 V_ADDITIONAL_OPENING_BALANCE_A := 0;
646 V_OTHER_OPENING_BALANCE_A := 0;
647 V_BASIC_OPENING_BALANCE_C := 0;
648 V_ADDITIONAL_OPENING_BALANCE_C := 0;
649 V_OTHER_OPENING_BALANCE_C := 0;
650
651 IF V_DEBUG_FLAG='Y' THEN
652 UTL_FILE.PUT_LINE(v_myfilehandle,'Inside NDF' ||'The Organization id is ' || CUR_REC.ORGANIZATION_ID);
653 UTL_FILE.PUT_LINE(v_myfilehandle,'Inside NDF' ||'The Location id is ' || CUR_REC.LOCATION_ID);
654 END IF;
655 --DBMS_OUTPUT.PUT_LINE('Before Insert');
656 --If the combination of Organization and Location do not exist then
657 INSERT INTO JAI_CMN_RG_COMP_BALS
658 (COMPONENT_BALANCE_ID,
659 ORGANIZATION_ID,
660 LOCATION_ID,
661 BASIC_RG23A_BALANCE,
662 ADDITIONAL_RG23A_BALANCE,
663 OTHER_RG23A_BALANCE,
664 BASIC_RG23C_BALANCE,
665 ADDITIONAL_RG23C_BALANCE,
669 OTHER_PLA_BALANCE,
666 OTHER_RG23C_BALANCE,
667 BASIC_PLA_BALANCE,
668 ADDITIONAL_PLA_BALANCE,
670 CREATION_DATE,
671 CREATED_BY,
672 LAST_UPDATE_DATE,
673 LAST_UPDATED_BY,
674 LAST_UPDATE_LOGIN
675 )
676 VALUES
677 ( JAI_CMN_RG_COMP_BALS_S.nextval,
678 CUR_REC.ORGANIZATION_ID,
679 CUR_REC.LOCATION_ID,
680 0,
681 0,
682 0,
683 0,
684 0,
685 0,
686 0,
687 0,
688 0,
689 TRUNC(SYSDATE),
690 CUR_REC.CREATED_BY,
691 TRUNC(SYSDATE),
692 CUR_REC.LAST_UPDATED_BY,
693 CUR_REC.LAST_UPDATE_LOGIN
694 );
695 END;
696 IF CUR_REC.REGISTER_TYPE ='A' THEN
697 V_BASIC_OPENING_BALANCE:= NVL(V_BASIC_OPENING_BALANCE_A, 0);
698 V_ADDITIONAL_OPENING_BALANCE := NVL(V_ADDITIONAL_OPENING_BALANCE_A, 0);
699 V_OTHER_OPENING_BALANCE := NVL(V_OTHER_OPENING_BALANCE_A, 0);
700 ELSIF CUR_REC.REGISTER_TYPE ='C' THEN
701 V_BASIC_OPENING_BALANCE:= NVL(V_BASIC_OPENING_BALANCE_C, 0);
702 V_ADDITIONAL_OPENING_BALANCE := NVL(V_ADDITIONAL_OPENING_BALANCE_C, 0);
703 V_OTHER_OPENING_BALANCE := NVL(V_OTHER_OPENING_BALANCE_C, 0);
704 ELSE
705 RAISE_APPLICATION_ERROR(-20001,'The Register Type Cannot be other than A or C');
706 END IF;
707 --Calculation of present Lines Opening Balance and Closing Balances..........
708 BEGIN
709 V_BASIC_CLOSING_BALANCE := V_BASIC_OPENING_BALANCE + NVL(CUR_REC.CR_BASIC_ED,0) - NVL(CUR_REC.DR_BASIC_ED,0);
710 V_ADDITIONAL_CLOSING_BALANCE := V_ADDITIONAL_OPENING_BALANCE + NVL(CUR_REC.CR_ADDITIONAL_ED,0) - NVL(CUR_REC.DR_ADDITIONAL_ED,0);
711 V_OTHER_CLOSING_BALANCE := V_OTHER_OPENING_BALANCE + NVL(CUR_REC.CR_OTHER_ED,0) - NVL(CUR_REC.DR_OTHER_ED,0);
712 END;
713
714 IF V_DEBUG_FLAG='Y' THEN
715 UTL_FILE.PUT_LINE(v_myfilehandle,'Before Insert into Details table' ||'The Organization id is ' || CUR_REC.ORGANIZATION_ID);
716 UTL_FILE.PUT_LINE(v_myfilehandle,'Before Insert into Details table' ||'The Location id is ' || CUR_REC.LOCATION_ID);
717 UTL_FILE.PUT_LINE(v_myfilehandle,'Before Insert into Details table' ||'The Register id is ' || CUR_REC.REGISTER_ID);
718 UTL_FILE.PUT_LINE(v_myfilehandle,'Before Insert into Details table' ||'The V_BASIC_OPENING_BALANCE is ' || V_BASIC_OPENING_BALANCE);
719 UTL_FILE.PUT_LINE(v_myfilehandle,'Before Insert into Details table' ||'The V_BASIC_CLOSING_BALANCE is ' || V_BASIC_CLOSING_BALANCE);
720 END IF;
721
722 INSERT INTO JAI_CMN_RG_COMP_DTLS
723 (EXCISE_COMP_DTL_ID,
724 ORGANIZATION_ID,
725 LOCATION_ID,
726 INVENTORY_ITEM_ID,
727 FIN_YEAR ,
728 REGISTER_ID,
729 SLNO,
730 REGISTER_TYPE,
731 BASIC_OPENING_BALANCE,
732 ADDITIONAL_OPENING_BALANCE,
733 OTHER_OPENING_BALANCE,
734 CR_BASIC_ED,
735 CR_ADDITIONAL_ED ,
736 CR_OTHER_ED,
737 DR_BASIC_ED,
738 DR_ADDITIONAL_ED,
739 DR_OTHER_ED,
740 BASIC_CLOSING_BALANCE,
741 ADDITIONAL_CLOSING_BALANCE,
742 OTHER_CLOSING_BALANCE,
743 CREATION_DATE,
744 CREATED_BY,
745 LAST_UPDATE_DATE,
746 LAST_UPDATED_BY,
747 TRANSACTION_DATE,
748 LAST_UPDATE_LOGIN
749 )
750 VALUES
751 ( JAI_CMN_RG_COMP_DTLS_S.nextval,
752 CUR_REC.ORGANIZATION_ID,
753 CUR_REC.LOCATION_ID,
754 CUR_REC.INVENTORY_ITEM_ID,
755 CUR_REC.FIN_YEAR,
756 CUR_REC.REGISTER_ID,
757 CUR_REC.SLNO,
758 CUR_REC.REGISTER_TYPE,
759 V_BASIC_OPENING_BALANCE,
760 V_ADDITIONAL_OPENING_BALANCE,
761 V_OTHER_OPENING_BALANCE,
762 CUR_REC.CR_BASIC_ED,
763 CUR_REC.CR_ADDITIONAL_ED,
764 CUR_REC.CR_OTHER_ED,
765 CUR_REC.DR_BASIC_ED,
766 CUR_REC.DR_ADDITIONAL_ED,
767 CUR_REC.DR_OTHER_ED,
768 V_BASIC_CLOSING_BALANCE,
769 V_ADDITIONAL_CLOSING_BALANCE,
770 V_OTHER_CLOSING_BALANCE,
771 TRUNC(SYSDATE),
772 CUR_REC.CREATED_BY,
773 TRUNC(SYSDATE),
774 CUR_REC.LAST_UPDATED_BY,
775 CUR_REC.TRANSACTION_DATE,
776 CUR_REC.LAST_UPDATE_LOGIN
777 );
778 --To Update Register Balances.................
779 IF CUR_REC.REGISTER_TYPE = 'A' THEN
780 UPDATE JAI_CMN_RG_COMP_BALS
781 SET
782 BASIC_RG23A_BALANCE = V_BASIC_CLOSING_BALANCE,
783 ADDITIONAL_RG23A_BALANCE = V_ADDITIONAL_CLOSING_BALANCE,
784 OTHER_RG23A_BALANCE = V_OTHER_CLOSING_BALANCE,
785 LAST_UPDATE_DATE = TRUNC(SYSDATE),
786 LAST_UPDATED_BY = CUR_REC.LAST_UPDATED_BY
787 WHERE ORGANIZATION_ID = CUR_REC.ORGANIZATION_ID AND
788 LOCATION_ID = CUR_REC.LOCATION_ID;
789 ELSIF CUR_REC.REGISTER_TYPE = 'C' THEN
790 UPDATE JAI_CMN_RG_COMP_BALS
791 SET
792 BASIC_RG23C_BALANCE = V_BASIC_CLOSING_BALANCE,
793 ADDITIONAL_RG23C_BALANCE = V_ADDITIONAL_CLOSING_BALANCE,
794 OTHER_RG23C_BALANCE = V_OTHER_CLOSING_BALANCE,
795 LAST_UPDATE_DATE = TRUNC(SYSDATE),
796 LAST_UPDATED_BY = CUR_REC.LAST_UPDATED_BY
797 WHERE ORGANIZATION_ID = CUR_REC.ORGANIZATION_ID AND
798 LOCATION_ID = CUR_REC.LOCATION_ID;
799 END IF;
800 --Updation Ends here.....................................
801 --COMMIT;
802 IF V_DEBUG_FLAG='Y' THEN
803 UTL_FILE.PUT_LINE(v_myfilehandle,'Before Insert into Details table' ||'The Location id is ' || CUR_REC.LOCATION_ID);
804 END IF;
805 if v_commit_count = 100 then
806 commit;
807 v_commit_count := 0;
808 else
809 v_commit_count := v_commit_count + 1;
810 end if;
811 END LOOP; --L1
812 commit;
813 IF V_DEBUG_FLAG='Y' THEN
814 UTL_FILE.FCLOSE(v_myfilehandle);
815 END IF;
816 EXCEPTION --Ex1
817 WHEN OTHERS THEN
818 rollback;
819 END generate_component_balances; --E1
820
821 END jai_cmn_rg_23ac_ii_pkg;