[Home] [Help]
PACKAGE BODY: APPS.JAI_CMN_RG_23D_TRXS_PKG
Source
1 PACKAGE BODY jai_cmn_rg_23d_trxs_pkg AS
2 /* $Header: jai_cmn_rg_23d.plb 120.3 2007/08/07 09:45:14 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_23d -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 01-NOV-2006 version 120.2 SACSETHI for bug 5228046
15 Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
16 This bug has datamodel and spec changes.
17
18 */
19
20 PROCEDURE insert_row(
21 P_REGISTER_ID OUT NOCOPY JAI_CMN_RG_23D_TRXS.register_id%TYPE,
22 P_ORGANIZATION_ID IN JAI_CMN_RG_23D_TRXS.organization_id%TYPE,
23 P_LOCATION_ID IN JAI_CMN_RG_23D_TRXS.location_id%TYPE,
24 P_TRANSACTION_TYPE IN JAI_CMN_RG_23D_TRXS.transaction_type%TYPE,
25 P_RECEIPT_ID IN JAI_CMN_RG_23D_TRXS.RECEIPT_REF%TYPE,
26 P_QUANTITY_RECEIVED IN JAI_CMN_RG_23D_TRXS.quantity_received%TYPE,
27 P_INVENTORY_ITEM_ID IN JAI_CMN_RG_23D_TRXS.inventory_item_id%TYPE,
28 P_SUBINVENTORY IN JAI_CMN_RG_23D_TRXS.subinventory%TYPE,
29 P_REFERENCE_LINE_ID IN JAI_CMN_RG_23D_TRXS.reference_line_id%TYPE,
30 P_TRANSACTION_UOM_CODE IN JAI_CMN_RG_23D_TRXS.transaction_uom_code%TYPE,
31 P_CUSTOMER_ID IN JAI_CMN_RG_23D_TRXS.customer_id%TYPE,
32 P_BILL_TO_SITE_ID IN JAI_CMN_RG_23D_TRXS.bill_to_site_id%TYPE,
33 P_SHIP_TO_SITE_ID IN JAI_CMN_RG_23D_TRXS.ship_to_site_id%TYPE,
34 P_QUANTITY_ISSUED IN JAI_CMN_RG_23D_TRXS.quantity_issued%TYPE,
35 P_REGISTER_CODE IN JAI_CMN_RG_23D_TRXS.register_code%TYPE,
36 P_RELEASED_DATE IN JAI_CMN_RG_23D_TRXS.released_date%TYPE,
37 P_COMM_INVOICE_NO IN JAI_CMN_RG_23D_TRXS.comm_invoice_no%TYPE,
38 P_COMM_INVOICE_DATE IN JAI_CMN_RG_23D_TRXS.comm_invoice_date%TYPE,
39 P_RECEIPT_BOE_NUM IN JAI_CMN_RG_23D_TRXS.receipt_boe_num%TYPE,
40 P_OTH_RECEIPT_ID IN JAI_CMN_RG_23D_TRXS.OTH_RECEIPT_ID_REF%TYPE,
41 P_OTH_RECEIPT_DATE IN JAI_CMN_RG_23D_TRXS.oth_receipt_date%TYPE,
42 P_OTH_RECEIPT_QUANTITY IN JAI_CMN_RG_23D_TRXS.oth_receipt_quantity%TYPE,
43 P_REMARKS IN JAI_CMN_RG_23D_TRXS.remarks%TYPE,
44 P_QTY_TO_ADJUST IN JAI_CMN_RG_23D_TRXS.qty_to_adjust%TYPE,
45 P_RATE_PER_UNIT IN JAI_CMN_RG_23D_TRXS.rate_per_unit%TYPE,
46 P_EXCISE_DUTY_RATE IN JAI_CMN_RG_23D_TRXS.excise_duty_rate%TYPE,
47 P_CHARGE_ACCOUNT_ID IN JAI_CMN_RG_23D_TRXS.charge_account_id%TYPE,
48 P_DUTY_AMOUNT IN JAI_CMN_RG_23D_TRXS.duty_amount%TYPE,
49 P_RECEIPT_DATE IN JAI_CMN_RG_23D_TRXS.receipt_date%TYPE,
50 P_GOODS_ISSUE_ID IN JAI_CMN_RG_23D_TRXS.goods_issue_id%TYPE,
51 P_GOODS_ISSUE_DATE IN JAI_CMN_RG_23D_TRXS.goods_issue_date%TYPE,
52 P_GOODS_ISSUE_QUANTITY IN JAI_CMN_RG_23D_TRXS.goods_issue_quantity%TYPE,
53 P_TRANSACTION_DATE IN JAI_CMN_RG_23D_TRXS.transaction_date%TYPE,
54 P_BASIC_ED IN JAI_CMN_RG_23D_TRXS.basic_ed%TYPE,
55 P_ADDITIONAL_ED IN JAI_CMN_RG_23D_TRXS.additional_ed%TYPE,
56 P_ADDITIONAL_CVD IN JAI_CMN_RG_23D_TRXS.additional_cvd%TYPE DEFAULT NULL, -- Date 01/11/2006 Bug 5228046 added by SACSETHI
57 P_OTHER_ED IN JAI_CMN_RG_23D_TRXS.other_ed%TYPE,
58 P_CVD IN JAI_CMN_RG_23D_TRXS.cvd%TYPE,
59 P_VENDOR_ID IN JAI_CMN_RG_23D_TRXS.vendor_id%TYPE,
60 P_VENDOR_SITE_ID IN JAI_CMN_RG_23D_TRXS.vendor_site_id%TYPE,
61 P_RECEIPT_NUM IN JAI_CMN_RG_23D_TRXS.receipt_num%TYPE,
62 P_ATTRIBUTE1 IN JAI_CMN_RG_23D_TRXS.attribute1%TYPE,
63 P_ATTRIBUTE2 IN JAI_CMN_RG_23D_TRXS.attribute2%TYPE,
64 P_ATTRIBUTE3 IN JAI_CMN_RG_23D_TRXS.attribute3%TYPE,
65 P_ATTRIBUTE4 IN JAI_CMN_RG_23D_TRXS.attribute4%TYPE,
66 P_ATTRIBUTE5 IN JAI_CMN_RG_23D_TRXS.attribute5%TYPE,
67 P_CONSIGNEE IN JAI_CMN_RG_23D_TRXS.consignee%TYPE,
68 P_MANUFACTURER_NAME IN JAI_CMN_RG_23D_TRXS.manufacturer_name%TYPE,
69 P_MANUFACTURER_ADDRESS IN JAI_CMN_RG_23D_TRXS.manufacturer_address%TYPE,
70 P_MANUFACTURER_RATE_AMT_PER_UN IN JAI_CMN_RG_23D_TRXS.manufacturer_rate_amt_per_unit%TYPE,
71 P_QTY_RECEIVED_FROM_MANUFACTUR IN JAI_CMN_RG_23D_TRXS.qty_received_from_manufacturer%TYPE,
72 P_TOT_AMT_PAID_TO_MANUFACTURER IN JAI_CMN_RG_23D_TRXS.tot_amt_paid_to_manufacturer%TYPE,
73 -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
74 P_OTHER_TAX_CREDIT IN JAI_CMN_RG_23D_TRXS.other_tax_credit%TYPE,
75 P_OTHER_TAX_DEBIT IN JAI_CMN_RG_23D_TRXS.other_tax_debit%TYPE,
76 P_TRANSACTION_SOURCE IN VARCHAR2,
77 P_CALLED_FROM IN VARCHAR2,
78 P_SIMULATE_FLAG IN VARCHAR2,
79 P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
80 P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2
81 ) IS
82
83 ld_creation_date DATE;
84 ln_created_by NUMBER(15);
85 ld_last_update_date DATE;
86 ln_last_updated_by NUMBER(15);
87 ln_last_update_login NUMBER(15);
88
89 ln_last_register_id NUMBER;
90 ln_slno NUMBER(10) := 0;
91 ln_fin_year JAI_CMN_RG_23D_TRXS.fin_year%TYPE;
92 ln_transaction_id NUMBER(10);
93 lv_transaction_type JAI_RCV_TRANSACTIONS.transaction_type%TYPE;
94
95 ln_quantity NUMBER;
96 ln_qty_to_adjust NUMBER;
97 ln_quantity_issued NUMBER;
98
99 ln_opening_balance_qty NUMBER;
100 ln_closing_balance_qty NUMBER;
101 lv_primary_uom_code MTL_SYSTEM_ITEMS.primary_uom_code%TYPE;
102
103 vTransToPrimaryUOMConv NUMBER;
104
105 r_last_record c_get_last_record%ROWTYPE;
106
107 ln_record_exist_cnt NUMBER(4);
108
109 lv_statement_id VARCHAR2(5);
110
111 BEGIN
112
113 /*----------------------------------------------------------------------------------------------------------------------------
114 CHANGE HISTORY for FILENAME: jai_cmn_rg_23d_trxs_pkg.sql
115 S.No dd/mm/yyyy Author and Details
116 ------------------------------------------------------------------------------------------------------------------------------
117 1 16/07/2004 Vijay Shankar for Bug# 3496408, Version:115.0
118 Table handler Package for JAI_CMN_RG_23D_TRXS table
119
120 2 03/01/2005 Vijay Shankar for Bug# 3940588, Version:115.1
121 Modified Insert and Update procedures to include p_other_tax_credit and p_other_tax_debit parameters for
122 Education Cess Enhancement
123
124 Dependancy:
125 -----------
126 IN60105D2 + 3496408
127 IN60106 + 3940588
128
129 ----------------------------------------------------------------------------------------------------------------------------*/
130
131 ld_creation_date := SYSDATE;
132 ln_created_by := FND_GLOBAL.user_id;
133 ld_last_update_date := SYSDATE;
134 ln_last_updated_by := ln_created_by;
135 ln_last_update_login := FND_GLOBAL.login_id;
136
137 lv_statement_id := '1';
138 ln_fin_year := jai_general_pkg.get_fin_year(p_organization_id);
139 lv_statement_id := '2';
140 lv_primary_uom_code := jai_general_pkg.get_primary_uom_code(p_organization_id, p_inventory_item_id);
141
142 lv_transaction_type := p_transaction_type;
143
144 lv_statement_id := '3';
145 get_trxn_type_and_id(lv_transaction_type, p_transaction_source, ln_transaction_id);
146
147 lv_statement_id := '4';
148 ln_record_exist_cnt := get_trxn_entry_cnt(p_organization_id, p_location_id, p_inventory_item_id,
149 p_receipt_id, ln_transaction_id);
150 IF ln_record_exist_cnt > 0 THEN
151 p_process_status := 'X';
152 p_process_message := 'RG23D Entry was already made for the transaction';
153 GOTO end_of_processing;
154 END IF;
155
156 lv_statement_id := '5';
157 vTransToPrimaryUOMConv := jai_general_pkg.trxn_to_primary_conv_rate
158 (p_transaction_uom_code, lv_primary_uom_code, p_inventory_item_id);
159
160 ln_quantity := p_quantity_received * vTransToPrimaryUOMConv;
161 ln_qty_to_adjust := p_qty_to_adjust * vTransToPrimaryUOMConv;
162 ln_quantity_issued := p_quantity_issued * vTransToPrimaryUOMConv;
163
164 lv_statement_id := '6';
165 ln_last_register_id := jai_general_pkg.get_last_record_of_rg
166 ('RG23D', p_organization_id, p_location_id, p_inventory_item_id, ln_fin_year);
167
168 lv_statement_id := '7';
169 OPEN c_get_last_record(ln_last_register_id);
170 FETCH c_get_last_record INTO r_last_record;
171 CLOSE c_get_last_record;
172
173 ln_slno := nvl(r_last_record.slno, 0) + 1;
174 ln_opening_balance_qty := nvl(r_last_record.closing_balance_qty, 0);
175 ln_closing_balance_qty := ln_opening_balance_qty + ln_quantity;
176
177 lv_statement_id := '8';
178 INSERT INTO JAI_CMN_RG_23D_TRXS(
179 REGISTER_ID,
180 ORGANIZATION_ID,
181 LOCATION_ID,
182 SLNO,
183 FIN_YEAR,
184 TRANSACTION_TYPE,
185 RECEIPT_REF,
186 QUANTITY_RECEIVED,
187 INVENTORY_ITEM_ID,
188 SUBINVENTORY,
189 REFERENCE_LINE_ID,
190 PRIMARY_UOM_CODE,
191 TRANSACTION_UOM_CODE,
192 CUSTOMER_ID,
193 BILL_TO_SITE_ID,
194 SHIP_TO_SITE_ID,
195 QUANTITY_ISSUED,
196 REGISTER_CODE,
197 RELEASED_DATE,
198 COMM_INVOICE_NO,
199 COMM_INVOICE_DATE,
200 RECEIPT_BOE_NUM,
201 OTH_RECEIPT_ID_REF,
202 OTH_RECEIPT_DATE,
203 OTH_RECEIPT_QUANTITY,
204 REMARKS,
205 QTY_TO_ADJUST,
206 RATE_PER_UNIT,
207 EXCISE_DUTY_RATE,
208 CHARGE_ACCOUNT_ID,
209 CREATION_DATE,
210 CREATED_BY,
211 LAST_UPDATE_DATE,
212 LAST_UPDATE_LOGIN,
213 LAST_UPDATED_BY,
214 DUTY_AMOUNT,
215 TRANSACTION_SOURCE_NUM,
216 RECEIPT_DATE,
217 GOODS_ISSUE_ID,
218 GOODS_ISSUE_DATE,
219 GOODS_ISSUE_QUANTITY,
220 TRANSACTION_DATE,
221 OPENING_BALANCE_QTY,
222 CLOSING_BALANCE_QTY,
223 BASIC_ED,
224 ADDITIONAL_ED,
225 ADDITIONAL_CVD,
226 OTHER_ED,
227 CVD,
228 VENDOR_ID,
229 VENDOR_SITE_ID,
230 RECEIPT_NUM,
231 ATTRIBUTE1,
232 ATTRIBUTE2,
233 ATTRIBUTE3,
234 ATTRIBUTE4,
235 ATTRIBUTE5,
236 CONSIGNEE,
237 MANUFACTURER_NAME,
238 MANUFACTURER_ADDRESS,
239 MANUFACTURER_RATE_AMT_PER_UNIT,
240 QTY_RECEIVED_FROM_MANUFACTURER,
241 TOT_AMT_PAID_TO_MANUFACTURER,
242 -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
243 other_tax_credit,
244 other_tax_debit
245 ) VALUES (
246 JAI_CMN_RG_23D_TRXS_S.nextval,
247 P_ORGANIZATION_ID,
248 P_LOCATION_ID,
249 ln_slno, --
250 ln_fin_year, --
251 lv_transaction_type, --
252 P_RECEIPT_ID,
253 ln_quantity, -- P_QUANTITY_RECEIVED,
254 P_INVENTORY_ITEM_ID,
255 P_SUBINVENTORY,
256 P_REFERENCE_LINE_ID,
257 lv_primary_uom_code, ---
258 P_TRANSACTION_UOM_CODE,
259 P_CUSTOMER_ID,
260 P_BILL_TO_SITE_ID,
261 P_SHIP_TO_SITE_ID,
262 ln_quantity_issued, -- P_QUANTITY_ISSUED,
263 P_REGISTER_CODE,
264 P_RELEASED_DATE,
265 P_COMM_INVOICE_NO,
266 P_COMM_INVOICE_DATE,
267 P_RECEIPT_BOE_NUM,
268 P_OTH_RECEIPT_ID,
269 P_OTH_RECEIPT_DATE,
270 P_OTH_RECEIPT_QUANTITY,
271 P_REMARKS,
272 ln_qty_to_adjust, -- P_QTY_TO_ADJUST,
273 P_RATE_PER_UNIT,
274 P_EXCISE_DUTY_RATE,
275 P_CHARGE_ACCOUNT_ID,
276 ld_creation_date, --
277 ln_created_by, --
278 ld_last_update_date, --
279 ln_last_update_login, --
280 ln_last_updated_by, ---
281 P_DUTY_AMOUNT,
282 ln_transaction_id, -- P_TRANSACTION_ID,
283 P_RECEIPT_DATE,
284 P_GOODS_ISSUE_ID,
285 P_GOODS_ISSUE_DATE,
286 P_GOODS_ISSUE_QUANTITY,
287 P_TRANSACTION_DATE,
288 ln_opening_balance_qty, --P_OPENING_BALANCE_QTY,
289 ln_closing_balance_qty, --P_CLOSING_BALANCE_QTY,
290 P_BASIC_ED,
291 P_ADDITIONAL_ED,
292 P_ADDITIONAL_CVD,
293 P_OTHER_ED,
294 P_CVD,
295 P_VENDOR_ID,
296 P_VENDOR_SITE_ID,
297 P_RECEIPT_NUM,
298 P_ATTRIBUTE1,
299 P_ATTRIBUTE2,
300 P_ATTRIBUTE3,
301 P_ATTRIBUTE4,
302 P_ATTRIBUTE5,
303 P_CONSIGNEE,
304 P_MANUFACTURER_NAME,
305 P_MANUFACTURER_ADDRESS,
306 P_MANUFACTURER_RATE_AMT_PER_UN,
307 P_QTY_RECEIVED_FROM_MANUFACTUR,
308 P_TOT_AMT_PAID_TO_MANUFACTURER,
309 -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
310 p_other_tax_credit,
311 p_other_tax_debit
312 ) RETURNING register_id INTO P_REGISTER_ID;
313
314 <<end_of_processing>>
315
316 NULL;
317 --IF p_process_message IS NOT NULL THEN
318 -- p_process_status := 'E';
319 -- RETURN;
320 --END IF;
321
322 EXCEPTION
323 WHEN OTHERS THEN
324 p_process_status := 'E';
325 p_process_message := 'RG23_D_PKG.insert_row->'||SQLERRM||', StmtId->'||lv_statement_id;
326 FND_FILE.put_line( FND_FILE.log, p_process_message);
327
328 END insert_row;
329
330 PROCEDURE update_row(
331
332 P_REGISTER_ID IN JAI_CMN_RG_23D_TRXS.register_id%TYPE DEFAULT NULL,
333 P_ORGANIZATION_ID IN JAI_CMN_RG_23D_TRXS.organization_id%TYPE DEFAULT NULL,
334 P_LOCATION_ID IN JAI_CMN_RG_23D_TRXS.location_id%TYPE DEFAULT NULL,
335 P_SLNO IN JAI_CMN_RG_23D_TRXS.slno%TYPE DEFAULT NULL,
336 P_FIN_YEAR IN JAI_CMN_RG_23D_TRXS.fin_year%TYPE DEFAULT NULL,
337 P_TRANSACTION_TYPE IN JAI_CMN_RG_23D_TRXS.transaction_type%TYPE DEFAULT NULL,
338 P_RECEIPT_ID IN JAI_CMN_RG_23D_TRXS.RECEIPT_REF%TYPE DEFAULT NULL,
339 P_QUANTITY_RECEIVED IN JAI_CMN_RG_23D_TRXS.quantity_received%TYPE DEFAULT NULL,
340 P_INVENTORY_ITEM_ID IN JAI_CMN_RG_23D_TRXS.inventory_item_id%TYPE DEFAULT NULL,
341 P_SUBINVENTORY IN JAI_CMN_RG_23D_TRXS.subinventory%TYPE DEFAULT NULL,
342 P_REFERENCE_LINE_ID IN JAI_CMN_RG_23D_TRXS.reference_line_id%TYPE DEFAULT NULL,
343 P_PRIMARY_UOM_CODE IN JAI_CMN_RG_23D_TRXS.primary_uom_code%TYPE DEFAULT NULL,
344 P_TRANSACTION_UOM_CODE IN JAI_CMN_RG_23D_TRXS.transaction_uom_code%TYPE DEFAULT NULL,
345 P_CUSTOMER_ID IN JAI_CMN_RG_23D_TRXS.customer_id%TYPE DEFAULT NULL,
346 P_BILL_TO_SITE_ID IN JAI_CMN_RG_23D_TRXS.bill_to_site_id%TYPE DEFAULT NULL,
347 P_SHIP_TO_SITE_ID IN JAI_CMN_RG_23D_TRXS.ship_to_site_id%TYPE DEFAULT NULL,
348 P_QUANTITY_ISSUED IN JAI_CMN_RG_23D_TRXS.quantity_issued%TYPE DEFAULT NULL,
349 P_REGISTER_CODE IN JAI_CMN_RG_23D_TRXS.register_code%TYPE DEFAULT NULL,
350 P_RELEASED_DATE IN JAI_CMN_RG_23D_TRXS.released_date%TYPE DEFAULT NULL,
351 P_COMM_INVOICE_NO IN JAI_CMN_RG_23D_TRXS.comm_invoice_no%TYPE DEFAULT NULL,
352 P_COMM_INVOICE_DATE IN JAI_CMN_RG_23D_TRXS.comm_invoice_date%TYPE DEFAULT NULL,
353 P_RECEIPT_BOE_NUM IN JAI_CMN_RG_23D_TRXS.receipt_boe_num%TYPE DEFAULT NULL,
354 P_OTH_RECEIPT_ID IN JAI_CMN_RG_23D_TRXS.OTH_RECEIPT_ID_REF%TYPE DEFAULT NULL,
355 P_OTH_RECEIPT_DATE IN JAI_CMN_RG_23D_TRXS.oth_receipt_date%TYPE DEFAULT NULL,
356 P_OTH_RECEIPT_QUANTITY IN JAI_CMN_RG_23D_TRXS.oth_receipt_quantity%TYPE DEFAULT NULL,
357 P_REMARKS IN JAI_CMN_RG_23D_TRXS.remarks%TYPE DEFAULT NULL,
358 P_QTY_TO_ADJUST IN JAI_CMN_RG_23D_TRXS.qty_to_adjust%TYPE DEFAULT NULL,
359 P_RATE_PER_UNIT IN JAI_CMN_RG_23D_TRXS.rate_per_unit%TYPE DEFAULT NULL,
360 P_EXCISE_DUTY_RATE IN JAI_CMN_RG_23D_TRXS.excise_duty_rate%TYPE DEFAULT NULL,
361 P_CHARGE_ACCOUNT_ID IN JAI_CMN_RG_23D_TRXS.charge_account_id%TYPE DEFAULT NULL,
362 P_DUTY_AMOUNT IN JAI_CMN_RG_23D_TRXS.duty_amount%TYPE DEFAULT NULL,
363 P_TRANSACTION_ID IN JAI_CMN_RG_23D_TRXS.TRANSACTION_SOURCE_NUM%TYPE DEFAULT NULL,
364 P_RECEIPT_DATE IN JAI_CMN_RG_23D_TRXS.receipt_date%TYPE DEFAULT NULL,
365 P_GOODS_ISSUE_ID IN JAI_CMN_RG_23D_TRXS.goods_issue_id%TYPE DEFAULT NULL,
366 P_GOODS_ISSUE_DATE IN JAI_CMN_RG_23D_TRXS.goods_issue_date%TYPE DEFAULT NULL,
367 P_GOODS_ISSUE_QUANTITY IN JAI_CMN_RG_23D_TRXS.goods_issue_quantity%TYPE DEFAULT NULL,
368 P_TRANSACTION_DATE IN JAI_CMN_RG_23D_TRXS.transaction_date%TYPE DEFAULT NULL,
369 P_OPENING_BALANCE_QTY IN JAI_CMN_RG_23D_TRXS.opening_balance_qty%TYPE DEFAULT NULL,
370 P_CLOSING_BALANCE_QTY IN JAI_CMN_RG_23D_TRXS.closing_balance_qty%TYPE DEFAULT NULL,
371 P_BASIC_ED IN JAI_CMN_RG_23D_TRXS.basic_ed%TYPE DEFAULT NULL,
372 P_ADDITIONAL_ED IN JAI_CMN_RG_23D_TRXS.additional_ed%TYPE DEFAULT NULL,
373 P_ADDITIONAL_CVD IN JAI_CMN_RG_23D_TRXS.additional_cvd%TYPE DEFAULT NULL, -- Date 01/11/2006 Bug 5228046 added by SACSETHI
374 P_OTHER_ED IN JAI_CMN_RG_23D_TRXS.other_ed%TYPE DEFAULT NULL,
375 P_CVD IN JAI_CMN_RG_23D_TRXS.cvd%TYPE DEFAULT NULL,
376 P_VENDOR_ID IN JAI_CMN_RG_23D_TRXS.vendor_id%TYPE DEFAULT NULL,
377 P_VENDOR_SITE_ID IN JAI_CMN_RG_23D_TRXS.vendor_site_id%TYPE DEFAULT NULL,
378 P_RECEIPT_NUM IN JAI_CMN_RG_23D_TRXS.receipt_num%TYPE DEFAULT NULL,
379 P_ATTRIBUTE1 IN JAI_CMN_RG_23D_TRXS.attribute1%TYPE DEFAULT NULL,
380 P_ATTRIBUTE2 IN JAI_CMN_RG_23D_TRXS.attribute2%TYPE DEFAULT NULL,
381 P_ATTRIBUTE3 IN JAI_CMN_RG_23D_TRXS.attribute3%TYPE DEFAULT NULL,
382 P_ATTRIBUTE4 IN JAI_CMN_RG_23D_TRXS.attribute4%TYPE DEFAULT NULL,
383 P_ATTRIBUTE5 IN JAI_CMN_RG_23D_TRXS.attribute5%TYPE DEFAULT NULL,
384 P_CONSIGNEE IN JAI_CMN_RG_23D_TRXS.consignee%TYPE DEFAULT NULL,
385 P_MANUFACTURER_NAME IN JAI_CMN_RG_23D_TRXS.manufacturer_name%TYPE DEFAULT NULL,
386 P_MANUFACTURER_ADDRESS IN JAI_CMN_RG_23D_TRXS.manufacturer_address%TYPE DEFAULT NULL,
387 P_MANUFACTURER_RATE_AMT_PER_UN IN JAI_CMN_RG_23D_TRXS.manufacturer_rate_amt_per_unit%TYPE DEFAULT NULL,
388 P_QTY_RECEIVED_FROM_MANUFACTUR IN JAI_CMN_RG_23D_TRXS.qty_received_from_manufacturer%TYPE DEFAULT NULL,
389 P_TOT_AMT_PAID_TO_MANUFACTURER IN JAI_CMN_RG_23D_TRXS.tot_amt_paid_to_manufacturer%TYPE DEFAULT NULL,
390 -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
391 P_OTHER_TAX_CREDIT IN JAI_CMN_RG_23D_TRXS.other_tax_credit%TYPE DEFAULT NULL,
392 P_OTHER_TAX_DEBIT IN JAI_CMN_RG_23D_TRXS.other_tax_debit%TYPE DEFAULT NULL,
393 P_SIMULATE_FLAG IN VARCHAR2,
394 P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
395 P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2
396 ) IS
397 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_23d_trxs_pkg.update_row'; /* Added by Ramananda for bug#4407165 */
398 BEGIN
399
400 UPDATE JAI_CMN_RG_23D_TRXS SET
401 REGISTER_ID = nvl(P_REGISTER_ID, REGISTER_ID),
402 ORGANIZATION_ID = nvl(P_ORGANIZATION_ID, ORGANIZATION_ID),
403 LOCATION_ID = nvl(P_LOCATION_ID, LOCATION_ID),
404 SLNO = nvl(P_SLNO, SLNO),
405 FIN_YEAR = nvl(P_FIN_YEAR, FIN_YEAR),
406 TRANSACTION_TYPE = nvl(P_TRANSACTION_TYPE, TRANSACTION_TYPE),
407 RECEIPT_REF = nvl(P_RECEIPT_ID, RECEIPT_REF),
408 QUANTITY_RECEIVED = nvl(P_QUANTITY_RECEIVED, QUANTITY_RECEIVED),
409 INVENTORY_ITEM_ID = nvl(P_INVENTORY_ITEM_ID, INVENTORY_ITEM_ID),
410 SUBINVENTORY = nvl(P_SUBINVENTORY, SUBINVENTORY),
411 REFERENCE_LINE_ID = nvl(P_REFERENCE_LINE_ID, REFERENCE_LINE_ID),
412 PRIMARY_UOM_CODE = nvl(P_PRIMARY_UOM_CODE, PRIMARY_UOM_CODE),
413 TRANSACTION_UOM_CODE = nvl(P_TRANSACTION_UOM_CODE, TRANSACTION_UOM_CODE),
414 CUSTOMER_ID = nvl(P_CUSTOMER_ID, CUSTOMER_ID),
415 BILL_TO_SITE_ID = nvl(P_BILL_TO_SITE_ID, BILL_TO_SITE_ID),
416 SHIP_TO_SITE_ID = nvl(P_SHIP_TO_SITE_ID, SHIP_TO_SITE_ID),
417 QUANTITY_ISSUED = nvl(P_QUANTITY_ISSUED, QUANTITY_ISSUED),
418 REGISTER_CODE = nvl(P_REGISTER_CODE, REGISTER_CODE),
419 RELEASED_DATE = nvl(P_RELEASED_DATE, RELEASED_DATE),
420 COMM_INVOICE_NO = nvl(P_COMM_INVOICE_NO, COMM_INVOICE_NO),
421 COMM_INVOICE_DATE = nvl(P_COMM_INVOICE_DATE, COMM_INVOICE_DATE),
422 RECEIPT_BOE_NUM = nvl(P_RECEIPT_BOE_NUM, RECEIPT_BOE_NUM),
423 OTH_RECEIPT_ID_REF = nvl(P_OTH_RECEIPT_ID, OTH_RECEIPT_ID_REF),
424 OTH_RECEIPT_DATE = nvl(P_OTH_RECEIPT_DATE, OTH_RECEIPT_DATE),
425 OTH_RECEIPT_QUANTITY = nvl(P_OTH_RECEIPT_QUANTITY, OTH_RECEIPT_QUANTITY),
426 REMARKS = nvl(P_REMARKS, REMARKS),
427 QTY_TO_ADJUST = nvl(P_QTY_TO_ADJUST, QTY_TO_ADJUST),
428 RATE_PER_UNIT = nvl(P_RATE_PER_UNIT, RATE_PER_UNIT),
429 EXCISE_DUTY_RATE = nvl(P_EXCISE_DUTY_RATE, EXCISE_DUTY_RATE),
430 CHARGE_ACCOUNT_ID = nvl(P_CHARGE_ACCOUNT_ID, CHARGE_ACCOUNT_ID),
431 DUTY_AMOUNT = nvl(P_DUTY_AMOUNT, DUTY_AMOUNT),
432 TRANSACTION_SOURCE_NUM = nvl(P_TRANSACTION_ID, TRANSACTION_SOURCE_NUM),
433 RECEIPT_DATE = nvl(P_RECEIPT_DATE, RECEIPT_DATE),
434 GOODS_ISSUE_ID = nvl(P_GOODS_ISSUE_ID, GOODS_ISSUE_ID),
435 GOODS_ISSUE_DATE = nvl(P_GOODS_ISSUE_DATE, GOODS_ISSUE_DATE),
436 GOODS_ISSUE_QUANTITY = nvl(P_GOODS_ISSUE_QUANTITY, GOODS_ISSUE_QUANTITY),
437 TRANSACTION_DATE = nvl(P_TRANSACTION_DATE, TRANSACTION_DATE),
438 OPENING_BALANCE_QTY = nvl(P_OPENING_BALANCE_QTY, OPENING_BALANCE_QTY),
439 CLOSING_BALANCE_QTY = nvl(P_CLOSING_BALANCE_QTY, CLOSING_BALANCE_QTY),
440 BASIC_ED = nvl(P_BASIC_ED, BASIC_ED),
441 ADDITIONAL_ED = nvl(P_ADDITIONAL_ED, ADDITIONAL_ED),
442 ADDITIONAL_CVD = nvl(P_ADDITIONAL_CVD,ADDITIONAL_CVD),
443 OTHER_ED = nvl(P_OTHER_ED, OTHER_ED),
444 CVD = nvl(P_CVD, CVD),
445 VENDOR_ID = nvl(P_VENDOR_ID, VENDOR_ID),
446 VENDOR_SITE_ID = nvl(P_VENDOR_SITE_ID, VENDOR_SITE_ID),
447 RECEIPT_NUM = nvl(P_RECEIPT_NUM, RECEIPT_NUM),
448 ATTRIBUTE1 = nvl(P_ATTRIBUTE1, ATTRIBUTE1),
449 ATTRIBUTE2 = nvl(P_ATTRIBUTE2, ATTRIBUTE2),
450 ATTRIBUTE3 = nvl(P_ATTRIBUTE3, ATTRIBUTE3),
451 ATTRIBUTE4 = nvl(P_ATTRIBUTE4, ATTRIBUTE4),
452 ATTRIBUTE5 = nvl(P_ATTRIBUTE5, ATTRIBUTE5),
453 CONSIGNEE = nvl(P_CONSIGNEE, CONSIGNEE),
454 MANUFACTURER_NAME = nvl(P_MANUFACTURER_NAME, MANUFACTURER_NAME),
455 MANUFACTURER_ADDRESS = nvl(P_MANUFACTURER_ADDRESS, MANUFACTURER_ADDRESS),
456 MANUFACTURER_RATE_AMT_PER_UNIT= nvl(P_MANUFACTURER_RATE_AMT_PER_UN, MANUFACTURER_RATE_AMT_PER_UNIT),
457 QTY_RECEIVED_FROM_MANUFACTURER= nvl(P_QTY_RECEIVED_FROM_MANUFACTUR, QTY_RECEIVED_FROM_MANUFACTURER),
458 TOT_AMT_PAID_TO_MANUFACTURER = nvl(P_TOT_AMT_PAID_TO_MANUFACTURER, TOT_AMT_PAID_TO_MANUFACTURER),
459 -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
460 other_tax_credit = nvl(p_other_tax_credit, other_tax_credit),
461 other_tax_debit = nvl(p_other_tax_debit, other_tax_debit)
462 WHERE register_id = p_register_id;
463
464 /* Added by Ramananda for bug#4407165 */
465 EXCEPTION
466 WHEN OTHERS THEN
467 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
468 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
469 app_exception.raise_exception;
470
471 END update_row;
472
473 PROCEDURE update_qty_to_adjust(
474 p_register_id IN NUMBER,
475 p_quantity IN NUMBER,
476 P_SIMULATE_FLAG IN VARCHAR2,
477 P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
478 P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2
479 ) IS
480
481 BEGIN
482 -- QTY_TO_ADJUST is a column which will be only INSERTED with a value for RECEIVE transaction and will be reduced
483 -- whenever a -ve Quantity Transaction or RTV Happens for Shipment Line
484 -- p_quantity can be +ve incase of regular RTV and +ve CORRECTion of RTV. -ve During Deliver to Non Trading
485 UPDATE JAI_CMN_RG_23D_TRXS
486 SET qty_to_adjust = nvl(qty_to_adjust, 0) - p_quantity,
487 last_update_date= SYSDATE
488 WHERE register_id = p_register_id;
489
490 END update_qty_to_adjust;
491
492 PROCEDURE update_payment_details(
493 p_register_id IN NUMBER,
494 p_charge_account_id IN NUMBER
495 ) IS
496
497 BEGIN
498
499 UPDATE JAI_CMN_RG_23D_TRXS
500 SET charge_account_id = p_charge_account_id,
501 last_update_date= SYSDATE
502 WHERE register_id = p_register_id;
503
504 END update_payment_details;
505
506
507 FUNCTION get_trxn_entry_cnt(
508 p_organization_id IN NUMBER,
509 p_location_id IN NUMBER,
510 p_inventory_item_id IN NUMBER,
511 p_receipt_id IN VARCHAR2,
512 p_transaction_id IN NUMBER
513 ) RETURN NUMBER IS
514
515 ln_record_exist_cnt NUMBER(4);
516 CURSOR c_record_exist IS
517 SELECT count(1)
518 FROM JAI_CMN_RG_23D_TRXS
519 WHERE organization_id = p_organization_id
520 AND location_id = p_location_id
521 AND inventory_item_id = p_inventory_item_id
522 AND receipt_ref = p_receipt_id
523 AND TRANSACTION_SOURCE_NUM = p_transaction_id;
524
525 BEGIN
526
527 OPEN c_record_exist;
528 FETCH c_record_exist INTO ln_record_exist_cnt;
529 CLOSE c_record_exist;
530
531 IF ln_record_exist_cnt > 0 THEN
532 FND_FILE.put_line( FND_FILE.log, '23D Duplicate Chk:'||ln_record_exist_cnt
533 ||', PARAMS: Orgn>'||p_organization_id||', Loc>'||p_location_id
534 ||', Item>'||p_inventory_item_id
535 ||', TrxId>'||p_receipt_id||', type>'||p_transaction_id
536 );
537 END IF;
538
539 RETURN ln_record_exist_cnt;
540
541 END get_trxn_entry_cnt;
542
543
544 PROCEDURE get_trxn_type_and_id(
545 p_transaction_type IN OUT NOCOPY VARCHAR2,
546 p_transaction_source IN VARCHAR2,
547 p_transaction_id OUT NOCOPY NUMBER
548 ) IS
549
550 BEGIN
551 IF p_transaction_type = 'RECEIVE' AND p_transaction_source = 'RMA' THEN
552 p_transaction_id := 18;
553 p_transaction_type := 'CR';
554 ELSIF p_transaction_type = 'RECEIVE' THEN
555 p_transaction_id := 18;
556 p_transaction_type := 'R';
557 ELSIF p_transaction_type = 'RETURN TO RECEIVING' THEN
558 p_transaction_id := 18;
559 p_transaction_type := 'R';
560 ELSIF p_transaction_type = 'DELIVER' THEN
561 p_transaction_id := 18;
562 p_transaction_type := 'R';
563 ELSIF p_transaction_type = 'RETURN TO VENDOR' THEN
564 p_transaction_id := 18;
565 p_transaction_type := 'RTV';
566 /* following two elsifs added - bug# 6030615 - interorg transfer*/
567 ELSIF p_transaction_type IN ('I','R') and p_transaction_source='Direct Org Transfer' then
568 p_transaction_id := 3;
569 ELSIF p_transaction_type IN ('I','R') and p_transaction_source='Intransit Shipment' then
570 p_transaction_id := 21;
571 ELSE
572 p_transaction_id := 20;
573 p_transaction_type := 'MISC';
574 END IF;
575
576 END get_trxn_type_and_id;
577
578 PROCEDURE make_entry
579 (p_org_id IN NUMBER,
580 p_location_id IN NUMBER,
581 p_trans_type IN VARCHAR2,
582 p_item_id IN NUMBER,
583 p_subinv_code IN VARCHAR2,
584 p_pr_uom_code IN VARCHAR2,
585 p_trans_uom_code IN VARCHAR2,
586 p_oth_receipt_id IN NUMBER,
587 p_oth_receipt_date IN DATE,
588 p_oth_receipt_qty IN NUMBER,
589 p_transaction_id IN NUMBER,
590 p_goods_issue_id IN NUMBER,
591 p_goods_issue_date IN DATE,
592 p_goods_issue_qty IN NUMBER,
593 p_trans_date IN DATE,
594 p_creation_date IN DATE,
595 p_created_by IN NUMBER,
596 p_last_update_date IN DATE,
597 p_last_update_login IN NUMBER,
598 p_last_updated_by IN NUMBER)
599 IS
600
601 /* Added by Ramananda for bug#4407165 */
602 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_23d_trxs_pkg.make_entry';
603
604 v_reg_id Number;
605 v_fin_year Number;
606 v_slno Number;
607 v_closing_bal_qty Number :=0;
608 v_opening_bal_qty Number :=0;
609
610 Cursor get_regid_cur IS
611 SELECT JAI_CMN_RG_23D_TRXS_S.nextval
612 FROM dual;
613
614 CURSOR fin_year_CUR is
615 SELECT max(fin_year) fin_year
616 FROM JAI_CMN_FIN_YEARS
617 WHERE organization_id = p_org_id
618 AND fin_active_flag = 'Y';
619
620 CURSOR SLNO_BAL_CUR(v_fin_year NUMBER) is
621 SELECT slno,NVL(closing_balance_qty, 0)
622 FROM JAI_CMN_RG_23D_TRXS
623 WHERE organization_id = p_org_id
624 AND location_id = p_location_id
625 AND fin_year = v_fin_year
626 and inventory_item_id = p_item_id --ashish 12/nov/2002;
627 AND slno = (SELECT max(slno)
628 FROM JAI_CMN_RG_23D_TRXS
629 WHERE organization_id = p_org_id
630 AND location_id = p_location_id
631 AND fin_year = v_fin_year
632 and inventory_item_id = p_item_id); --ashish 12/nov/2002;
633
634 v_issue_qty number; --ashish for bug # 2659989
635
636 Begin
637 /*-------------------------------------------------------------------------------------------------------------------------
638 S.No Date(DD/MM/YY) Author and Details of Changes
639 ---- -------------- -----------------------------
640 1 11/11/02 asshukla for bug # 2659989.
641 As observed by VPRABAKA
642 the sno which is unique for a oraganization , location , fin year and inventory item was gettin duplicated
643 as there was no check on the inventory item id. the code is added for the selection of the slno.
644 From now on the serial no will be generated itemwise.
645
646 Also closing balance was increasinf even if the transaction type was issue which is wrong,
647 It has been corrected by adding an if condition and making the quantity negative in case
648 the transaction type is issue.Added a variable v_issue_qty for the same.
649
650 --------------------------------------------------------------------------------------------------------------------------*/
651
652
653 OPEN get_regid_cur;
654 FETCH get_regid_cur INTO v_reg_id;
655 CLOSE get_regid_cur;
656
657 OPEN fin_year_CUR;
658 FETCH fin_year_CUR INTO v_fin_year;
659 CLOSE fin_year_CUR;
660
661 OPEN slno_bal_cur(v_fin_year);
662 FETCH slno_bal_cur INTO v_slno, v_closing_bal_qty;
663 CLOSE slno_bal_cur;
664
665 if p_trans_type in ('I') then --ashish bug # 2659989
666 v_issue_qty := -p_goods_issue_qty;
667 else
668 v_issue_qty := p_goods_issue_qty;
669 end if;
670
671 IF v_slno IS NULL THEN
672 v_slno := 0 ;
673 v_opening_bal_qty := 0;
674 ELSE
675 v_opening_bal_qty := v_closing_bal_qty;
676 END IF;
677
678 v_closing_bal_qty := v_opening_bal_qty + NVL(p_oth_receipt_qty, v_issue_qty);
679 v_slno := v_slno + 1;
680
681 INSERT INTO JAI_CMN_RG_23D_TRXS
682 (register_id,
683 organization_id,
684 location_id,
685 slno,
686 fin_year,
687 transaction_type,
688 inventory_item_id,
689 subinventory,
690 primary_uom_code,
691 transaction_uom_code,
692 OTH_RECEIPT_ID_REF,
693 oth_receipt_date,
694 oth_receipt_quantity,
695 TRANSACTION_SOURCE_NUM,
696 goods_issue_id,
697 goods_issue_date,
698 goods_issue_quantity,
699 transaction_date,
700 opening_balance_qty,
701 closing_balance_qty,
702 creation_date,
703 created_by,
704 last_update_date,
705 last_update_login,
706 last_updated_by)
707 VALUES
708 (v_reg_id,
709 p_org_id,
710 p_location_id,
711 v_slno,
712 v_fin_year,
713 p_trans_type,
714 p_item_id,
715 p_subinv_code,
716 p_pr_uom_code,
717 p_trans_uom_code,
718 p_oth_receipt_id,
719 p_oth_receipt_date,
720 p_oth_receipt_qty,
721 p_transaction_id,
722 p_goods_issue_id,
723 p_goods_issue_date,
724 v_issue_qty,
725 p_trans_date,
726 v_opening_bal_qty,
727 v_closing_bal_qty,
728 p_creation_date,
729 p_created_by,
730 p_last_update_date,
731 p_last_update_login,
732 p_last_updated_by);
733
734 /* Added by Ramananda for bug#4407165 */
735 EXCEPTION
736 WHEN OTHERS THEN
737 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
738 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
739 app_exception.raise_exception;
740
741
742 End make_entry;
743
744 --# ==================================================================================
745 --# FILENAME
746 --#
747 --#
748 --# DESCRIPTION
749 --# Procedure to calculate opening and closing Qty balnaces
750 --#
751 --# ==================================================================================
752 PROCEDURE calculate_qty_balances
753 (p_org_id IN NUMBER,
754 p_fin_year IN NUMBER,
755 p_mode VARCHAR2,
756 qty NUMBER,
757 v_opening_Qty IN OUT NOCOPY NUMBER,
758 v_closing_qty IN OUT NOCOPY NUMBER,
759 p_inventory_item_id Number) IS
760
761 /* Added by Ramananda for bug#4407165 */
762 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_23d_trxs_pkg.calculate_qty_balances';
763
764 v_previous_serial_no number;
765 v_serial_no number;
766 v_rg_balance number;
767 v_inventory_item_id Number;
768 err_msg Varchar2(200);
769
770 v_fin_year Number;
771 v_exists Number := 0;
772 cnt Number;
773
774 Cursor C_Item_id Is
775 Select b.Inventory_Item_Id
776 From Ic_Item_Mst a, Mtl_System_Items b
777 Where a.Item_no = b.segment1
778 And a.Item_Id = p_INVENTORY_ITEM_ID
779 And ( b.organization_Id = p_org_id
780 or b.organization_Id = 0 ) ;
781
782 -- start added by K V UDAY KUMAR on 10-MAY-2001, to pick slno from previous year in
783 -- case of change in fin_year transactions
784
785 Cursor prior_slno_cur(v_inventory_item_id Number) IS
786 select NVL(MAX(slno),0), NVL(MAX(slno),0) + 1 from JAI_CMN_RG_23D_TRXS
787 WHERE organization_id = p_org_id
788 and inventory_item_id = v_inventory_item_id
789 and fin_year = p_fin_year - 1;
790 -- end
791
792 Cursor serial_no_cur(v_inventory_item_id Number) IS
793 SELECT NVL(MAX(slno),0) , NVL(MAX(slno),0) + 1
794 FROM JAI_CMN_RG_23D_TRXS
795 WHERE organization_id = p_org_id and
796 -- location_id = p_location_id and
797 inventory_item_id = v_inventory_item_id
798 and fin_year = p_fin_year ;
799
800 CURSOR balance_cur(p_previous_serial_no IN NUMBER,v_inventory_item_id Number,x_fin_year Number) IS
801 SELECT NVL(opening_balance_qty,0),NVL(closing_balance_qty,0)
802 FROM JAI_CMN_RG_23D_TRXS
803 WHERE organization_id = p_org_id and
804 --location_id = p_location_id and
805 slno = p_previous_serial_no and
806 inventory_item_id = v_inventory_item_id and
807 fin_year = x_fin_year;
808
809
810
811 BEGIN
812
813 Open C_Item_id;
814 fetch C_Item_id into v_inventory_item_id;
815 close C_Item_id;
816
817 -- shifted below cursor in the (if cnt > 0) by K V UDAY KUMAR on 10-may-2001
818 /* OPEN serial_no_cur(v_inventory_item_id);
819 FETCH serial_no_cur INTO v_previous_serial_no, v_serial_no;
820 CLOSE serial_no_cur; */
821
822 -- added below code on 10-may-2001 by K V UDAY KUMAR
823 select count(*) into cnt from JAI_CMN_RG_23D_TRXS
824 WHERE organization_id = p_org_id
825 and inventory_item_id = v_inventory_item_id
826 and fin_year = p_fin_year;
827
828 if cnt > 0 then
829
830 OPEN serial_no_cur(v_inventory_item_id);
831 FETCH serial_no_cur INTO v_previous_serial_no, v_serial_no;
832 v_exists := 1;
833 CLOSE serial_no_cur;
834 else
835 OPEN prior_slno_cur(v_inventory_item_id);
836 FETCH prior_slno_cur INTO v_previous_serial_no, v_serial_no;
837 v_exists := 0;
838 CLOSE prior_slno_cur;
839 end if;
840
841 if v_exists = 1 then
842 v_fin_year := p_fin_year;
843 elsif v_exists = 0 then
844 v_fin_year := p_fin_year - 1;
845 end if;
846 -- end here.
847
848 IF NVL(v_previous_serial_no,0) = 0 THEN
849 v_previous_serial_no := 0;
850 v_serial_no := 1;
851 END IF;
852
853 IF NVL(v_previous_serial_no,0) > 0 THEN
854
855 OPEN balance_cur(v_previous_serial_no,v_inventory_item_id,v_fin_year);
856 FETCH balance_cur INTO v_opening_qty, v_closing_qty;
857 CLOSE balance_cur;
858
859 v_opening_qty := v_closing_qty;
860
861 IF p_mode = 'I' then
862 v_closing_qty := v_closing_qty - qty;
863 ELSIF p_mode = 'R' then
864 v_closing_qty := v_closing_qty + qty;
865 END IF;
866
867 ELSE
868 v_opening_qty := 0;
869 IF p_mode = 'I' then
870 v_closing_qty := nvl(v_closing_qty,0) - qty;
871 ELSIF p_mode = 'R' then
872 v_closing_qty := Nvl(v_closing_qty,0) + qty;
873 END IF;
874 END IF;
875
876
877 /* Added by Ramananda for bug#4407165 */
878 EXCEPTION
879 WHEN OTHERS THEN
880 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
881 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
882 app_exception.raise_exception;
883
884 END calculate_qty_balances;
885
886 procedure upd_receipt_qty_matched(p_receipt_id in number,p_quantity_applied in number,p_qty_to_adjust Number) is
887 begin
888 update JAI_CMN_RG_23D_TRXS
889 set qty_to_adjust= nvl(qty_to_adjust,0) - nvl(p_quantity_applied,0)
890 where register_id=p_receipt_id;
891 END upd_receipt_qty_matched;
892
893 END JAI_CMN_RG_23D_TRXS_PKG;