[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.5.12020000.2 2013/04/09 09:29:44 mmurtuza 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 2. 09-APr-2013 mmurtuza for bug 14847057
651 Description: TST1221.DB3:MISC RECEIPT(RG UPDATE) NOT AVAILABLE FOR MATCHING AGAINST SO
652 Fix: Inserted the data into columns QUANTITY_RECEIVED and QTY_TO_ADJUST in table JAI_CMN_RG_23D_TRXS
653
654 --------------------------------------------------------------------------------------------------------------------------*/
655
656
657 OPEN get_regid_cur;
658 FETCH get_regid_cur INTO v_reg_id;
659 CLOSE get_regid_cur;
660
661 OPEN fin_year_CUR;
662 FETCH fin_year_CUR INTO v_fin_year;
663 CLOSE fin_year_CUR;
664
665 OPEN slno_bal_cur(v_fin_year);
666 FETCH slno_bal_cur INTO v_slno, v_closing_bal_qty;
667 CLOSE slno_bal_cur;
668
669 if p_trans_type in ('I','IA') then --ashish bug # 2659989 Added the IA condition for bug 8303258 by nprashar
670 v_issue_qty := -p_goods_issue_qty;
671 else
672 v_issue_qty := p_goods_issue_qty;
673 end if;
674
675 IF v_slno IS NULL THEN
676 v_slno := 0 ;
677 v_opening_bal_qty := 0;
678 ELSE
679 v_opening_bal_qty := v_closing_bal_qty;
680 END IF;
681
682 v_closing_bal_qty := v_opening_bal_qty + NVL(p_oth_receipt_qty, v_issue_qty);
683 v_slno := v_slno + 1;
684
685 INSERT INTO JAI_CMN_RG_23D_TRXS
686 (register_id,
687 organization_id,
688 location_id,
689 slno,
690 fin_year,
691 transaction_type,
692 inventory_item_id,
693 subinventory,
694 primary_uom_code,
695 transaction_uom_code,
696 OTH_RECEIPT_ID_REF,
697 oth_receipt_date,
698 oth_receipt_quantity,
699 QUANTITY_RECEIVED, /*Added by mmurtuza for bug 14847057*/
700 QTY_TO_ADJUST, /*Added by mmurtuza for bug 14847057*/
701 TRANSACTION_SOURCE_NUM,
702 goods_issue_id,
703 goods_issue_date,
704 goods_issue_quantity,
705 transaction_date,
706 opening_balance_qty,
707 closing_balance_qty,
708 creation_date,
709 created_by,
710 last_update_date,
711 last_update_login,
712 last_updated_by)
713 VALUES
714 (v_reg_id,
715 p_org_id,
716 p_location_id,
717 v_slno,
718 v_fin_year,
719 p_trans_type,
720 p_item_id,
721 p_subinv_code,
722 p_pr_uom_code,
723 p_trans_uom_code,
724 p_oth_receipt_id,
725 p_oth_receipt_date,
726 p_oth_receipt_qty,
727 p_oth_receipt_qty, /*Added by mmurtuza for bug 14847057*/
728 p_oth_receipt_qty, /*Added by mmurtuza for bug 14847057*/
729 p_transaction_id,
730 p_goods_issue_id,
731 p_goods_issue_date,
732 v_issue_qty,
733 p_trans_date,
734 v_opening_bal_qty,
735 v_closing_bal_qty,
736 p_creation_date,
740 p_last_updated_by);
737 p_created_by,
738 p_last_update_date,
739 p_last_update_login,
741
742 /* Added by Ramananda for bug#4407165 */
743 EXCEPTION
744 WHEN OTHERS THEN
745 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
746 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
747 app_exception.raise_exception;
748
749
750 End make_entry;
751
752 --# ==================================================================================
753 --# FILENAME
754 --#
755 --#
756 --# DESCRIPTION
757 --# Procedure to calculate opening and closing Qty balnaces
758 --#
759 --# ==================================================================================
760 PROCEDURE calculate_qty_balances
761 (p_org_id IN NUMBER,
762 p_fin_year IN NUMBER,
763 p_mode VARCHAR2,
764 qty NUMBER,
765 v_opening_Qty IN OUT NOCOPY NUMBER,
766 v_closing_qty IN OUT NOCOPY NUMBER,
767 p_inventory_item_id Number) IS
768
769 /* Added by Ramananda for bug#4407165 */
770 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_23d_trxs_pkg.calculate_qty_balances';
771
772 v_previous_serial_no number;
773 v_serial_no number;
774 v_rg_balance number;
775 v_inventory_item_id Number;
776 err_msg Varchar2(200);
777
778 v_fin_year Number;
779 v_exists Number := 0;
780 cnt Number;
781
782 Cursor C_Item_id Is
783 Select b.Inventory_Item_Id
784 From Ic_Item_Mst a, Mtl_System_Items b
785 Where a.Item_no = b.segment1
786 And a.Item_Id = p_INVENTORY_ITEM_ID
787 And ( b.organization_Id = p_org_id
788 or b.organization_Id = 0 ) ;
789
790 -- start added by K V UDAY KUMAR on 10-MAY-2001, to pick slno from previous year in
791 -- case of change in fin_year transactions
792
793 Cursor prior_slno_cur(v_inventory_item_id Number) IS
794 select NVL(MAX(slno),0), NVL(MAX(slno),0) + 1 from JAI_CMN_RG_23D_TRXS
795 WHERE organization_id = p_org_id
796 and inventory_item_id = v_inventory_item_id
797 and fin_year = p_fin_year - 1;
798 -- end
799
800 Cursor serial_no_cur(v_inventory_item_id Number) IS
801 SELECT NVL(MAX(slno),0) , NVL(MAX(slno),0) + 1
802 FROM JAI_CMN_RG_23D_TRXS
803 WHERE organization_id = p_org_id and
804 -- location_id = p_location_id and
805 inventory_item_id = v_inventory_item_id
806 and fin_year = p_fin_year ;
807
808 CURSOR balance_cur(p_previous_serial_no IN NUMBER,v_inventory_item_id Number,x_fin_year Number) IS
809 SELECT NVL(opening_balance_qty,0),NVL(closing_balance_qty,0)
810 FROM JAI_CMN_RG_23D_TRXS
811 WHERE organization_id = p_org_id and
812 --location_id = p_location_id and
813 slno = p_previous_serial_no and
814 inventory_item_id = v_inventory_item_id and
815 fin_year = x_fin_year;
816
817
818
819 BEGIN
820
821 Open C_Item_id;
822 fetch C_Item_id into v_inventory_item_id;
823 close C_Item_id;
824
825 -- shifted below cursor in the (if cnt > 0) by K V UDAY KUMAR on 10-may-2001
826 /* OPEN serial_no_cur(v_inventory_item_id);
827 FETCH serial_no_cur INTO v_previous_serial_no, v_serial_no;
828 CLOSE serial_no_cur; */
829
830 -- added below code on 10-may-2001 by K V UDAY KUMAR
831 select count(*) into cnt from JAI_CMN_RG_23D_TRXS
832 WHERE organization_id = p_org_id
833 and inventory_item_id = v_inventory_item_id
834 and fin_year = p_fin_year;
835
836 if cnt > 0 then
837
838 OPEN serial_no_cur(v_inventory_item_id);
839 FETCH serial_no_cur INTO v_previous_serial_no, v_serial_no;
840 v_exists := 1;
841 CLOSE serial_no_cur;
842 else
843 OPEN prior_slno_cur(v_inventory_item_id);
844 FETCH prior_slno_cur INTO v_previous_serial_no, v_serial_no;
845 v_exists := 0;
846 CLOSE prior_slno_cur;
847 end if;
848
849 if v_exists = 1 then
850 v_fin_year := p_fin_year;
851 elsif v_exists = 0 then
852 v_fin_year := p_fin_year - 1;
853 end if;
854 -- end here.
855
856 IF NVL(v_previous_serial_no,0) = 0 THEN
857 v_previous_serial_no := 0;
858 v_serial_no := 1;
859 END IF;
860
861 IF NVL(v_previous_serial_no,0) > 0 THEN
862
863 OPEN balance_cur(v_previous_serial_no,v_inventory_item_id,v_fin_year);
864 FETCH balance_cur INTO v_opening_qty, v_closing_qty;
865 CLOSE balance_cur;
866
867 v_opening_qty := v_closing_qty;
868
869 IF p_mode = 'I' then
870 v_closing_qty := v_closing_qty - qty;
871 ELSIF p_mode = 'R' then
872 v_closing_qty := v_closing_qty + qty;
873 END IF;
874
875 ELSE
876 v_opening_qty := 0;
877 IF p_mode = 'I' then
878 v_closing_qty := nvl(v_closing_qty,0) - qty;
879 ELSIF p_mode = 'R' then
880 v_closing_qty := Nvl(v_closing_qty,0) + qty;
881 END IF;
882 END IF;
883
884
885 /* Added by Ramananda for bug#4407165 */
886 EXCEPTION
887 WHEN OTHERS THEN
888 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
889 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
890 app_exception.raise_exception;
891
892 END calculate_qty_balances;
893
894 procedure upd_receipt_qty_matched(p_receipt_id in number,p_quantity_applied in number,p_qty_to_adjust Number) is
895 begin
896 update JAI_CMN_RG_23D_TRXS
897 set qty_to_adjust= nvl(qty_to_adjust,0) - nvl(p_quantity_applied,0)
898 where register_id=p_receipt_id;
899 END upd_receipt_qty_matched;
900
901 END JAI_CMN_RG_23D_TRXS_PKG;