[Home] [Help]
PACKAGE BODY: APPS.JAI_CMN_RG_23AC_I_TRXS_PKG
Source
1 PACKAGE BODY jai_cmn_rg_23ac_i_trxs_pkg AS
2 /* $Header: jai_cmn_rg_23p1.plb 120.4.12010000.3 2010/04/28 12:43:07 vkaranam ship $ */
3
4
5 PROCEDURE insert_row(
6
7 P_REGISTER_ID OUT NOCOPY JAI_CMN_RG_23AC_I_TRXS.register_id%TYPE,
8 -- P_FIN_YEAR IN JAI_CMN_RG_23AC_I_TRXS.fin_year%TYPE,
9 P_INVENTORY_ITEM_ID IN JAI_CMN_RG_23AC_I_TRXS.inventory_item_id%TYPE,
10 P_ORGANIZATION_ID IN JAI_CMN_RG_23AC_I_TRXS.organization_id%TYPE,
11 P_QUANTITY_RECEIVED IN JAI_CMN_RG_23AC_I_TRXS.quantity_received%TYPE,
12 P_RECEIPT_ID IN JAI_CMN_RG_23AC_I_TRXS.RECEIPT_REF%TYPE,
13 P_TRANSACTION_TYPE IN JAI_CMN_RG_23AC_I_TRXS.transaction_type%TYPE,
14 P_RECEIPT_DATE IN JAI_CMN_RG_23AC_I_TRXS.receipt_date%TYPE,
15 P_PO_HEADER_ID IN JAI_CMN_RG_23AC_I_TRXS.po_header_id%TYPE,
16 P_PO_HEADER_DATE IN JAI_CMN_RG_23AC_I_TRXS.po_header_date%TYPE,
17 P_PO_LINE_ID IN JAI_CMN_RG_23AC_I_TRXS.po_line_id%TYPE,
18 P_PO_LINE_LOCATION_ID IN JAI_CMN_RG_23AC_I_TRXS.po_line_location_id%TYPE,
19 P_VENDOR_ID IN JAI_CMN_RG_23AC_I_TRXS.vendor_id%TYPE,
20 P_VENDOR_SITE_ID IN JAI_CMN_RG_23AC_I_TRXS.vendor_site_id%TYPE,
21 P_CUSTOMER_ID IN JAI_CMN_RG_23AC_I_TRXS.customer_id%TYPE,
22 P_CUSTOMER_SITE_ID IN JAI_CMN_RG_23AC_I_TRXS.customer_site_id%TYPE,
23 P_GOODS_ISSUE_ID IN JAI_CMN_RG_23AC_I_TRXS.GOODS_ISSUE_ID_REF%TYPE,
24 P_GOODS_ISSUE_DATE IN JAI_CMN_RG_23AC_I_TRXS.goods_issue_date%TYPE,
25 P_GOODS_ISSUE_QUANTITY IN JAI_CMN_RG_23AC_I_TRXS.goods_issue_quantity%TYPE,
26 P_SALES_INVOICE_ID IN JAI_CMN_RG_23AC_I_TRXS.SALES_INVOICE_NO%TYPE,
27 P_SALES_INVOICE_DATE IN JAI_CMN_RG_23AC_I_TRXS.sales_invoice_date%TYPE,
28 P_SALES_INVOICE_QUANTITY IN JAI_CMN_RG_23AC_I_TRXS.sales_invoice_quantity%TYPE,
29 P_EXCISE_INVOICE_ID IN JAI_CMN_RG_23AC_I_TRXS.EXCISE_INVOICE_NO%TYPE,
30 P_EXCISE_INVOICE_DATE IN JAI_CMN_RG_23AC_I_TRXS.excise_invoice_date%TYPE,
31 P_OTH_RECEIPT_QUANTITY IN JAI_CMN_RG_23AC_I_TRXS.oth_receipt_quantity%TYPE,
32 P_OTH_RECEIPT_ID IN JAI_CMN_RG_23AC_I_TRXS.OTH_RECEIPT_ID_REF%TYPE,
33 P_OTH_RECEIPT_DATE IN JAI_CMN_RG_23AC_I_TRXS.oth_receipt_date%TYPE,
34 P_REGISTER_TYPE IN JAI_CMN_RG_23AC_I_TRXS.register_type%TYPE,
35 P_IDENTIFICATION_NO IN JAI_CMN_RG_23AC_I_TRXS.identification_no%TYPE,
36 P_IDENTIFICATION_MARK IN JAI_CMN_RG_23AC_I_TRXS.identification_mark%TYPE,
37 P_BRAND_NAME IN JAI_CMN_RG_23AC_I_TRXS.brand_name%TYPE,
38 P_DATE_OF_VERIFICATION IN JAI_CMN_RG_23AC_I_TRXS.date_of_verification%TYPE,
39 P_DATE_OF_INSTALLATION IN JAI_CMN_RG_23AC_I_TRXS.date_of_installation%TYPE,
40 P_DATE_OF_COMMISSION IN JAI_CMN_RG_23AC_I_TRXS.date_of_commission%TYPE,
41 P_REGISER_ID_PART_II IN JAI_CMN_RG_23AC_I_TRXS.REGISTER_ID_PART_II%TYPE,
42 P_PLACE_OF_INSTALL IN JAI_CMN_RG_23AC_I_TRXS.place_of_install%TYPE,
43 P_REMARKS IN JAI_CMN_RG_23AC_I_TRXS.remarks%TYPE,
44 P_LOCATION_ID IN JAI_CMN_RG_23AC_I_TRXS.location_id%TYPE,
45 P_TRANSACTION_UOM_CODE IN JAI_CMN_RG_23AC_I_TRXS.transaction_uom_code%TYPE,
46 P_TRANSACTION_DATE IN JAI_CMN_RG_23AC_I_TRXS.transaction_date%TYPE,
47 P_BASIC_ED IN JAI_CMN_RG_23AC_I_TRXS.basic_ed%TYPE,
48 P_ADDITIONAL_ED IN JAI_CMN_RG_23AC_I_TRXS.additional_ed%TYPE,
49 P_ADDITIONAL_CVD IN JAI_CMN_RG_23AC_I_TRXS.additional_cvd%TYPE DEFAULT NULL, /* Bug 5228046 added by sacsethi */
50 P_OTHER_ED IN JAI_CMN_RG_23AC_I_TRXS.other_ed%TYPE,
51 P_CHARGE_ACCOUNT_ID IN JAI_CMN_RG_23AC_I_TRXS.charge_account_id%TYPE,
52 P_TRANSACTION_SOURCE IN VARCHAR2,
53 P_CALLED_FROM IN VARCHAR2,
54 P_SIMULATE_FLAG IN VARCHAR2,
55 P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
56 P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2
57 ) IS
58
59 ld_creation_date DATE;
60 ln_created_by NUMBER(15);
61 ld_last_update_date DATE;
62 ln_last_updated_by NUMBER(15);
63 ln_last_update_login NUMBER(15);
64
65 lv_last_register_id NUMBER;
66 ln_slno NUMBER(10) := 0;
67 ln_transaction_id NUMBER(10);
68 lv_transaction_type JAI_RCV_TRANSACTIONS.transaction_type%TYPE;
69 ln_quantity NUMBER;
70 ln_opening_balance_qty NUMBER;
71 ln_closing_balance_qty NUMBER;
72 lv_primary_uom_code MTL_SYSTEM_ITEMS.primary_uom_code%TYPE;
73 vTransToPrimaryUOMConv NUMBER;
74
75 ln_fin_year NUMBER(4);
76 lv_range JAI_CMN_RG_23AC_I_TRXS.range_no%TYPE;
77 lv_division JAI_CMN_RG_23AC_I_TRXS.division_no%TYPE;
78 lv_master_flag JAI_CMN_RG_23AC_I_TRXS.master_flag%TYPE;
79
80 r_last_record c_get_last_record%ROWTYPE;
81
82 ln_record_exist_cnt NUMBER(4);
83 lv_statement_id VARCHAR2(5);
84
85 BEGIN
86
87 /*----------------------------------------------------------------------------------------------------------------------------
88 CHANGE HISTORY for FILENAME: jai_cmn_rg_23ac_i_trxs_pkg.sql
89 S.No dd/mm/yyyy Author and Details
90 ------------------------------------------------------------------------------------------------------------------------------
91 1 16/07/2002 Vijay Shankar for Bug# 3496408, Version:115.0
92 Table handler Package for JAI_CMN_RG_23AC_I_TRXS table
93
94 2. 08/06/2005 Version 116.1
95 Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
96 as required for CASE COMPLAINCE.
97
98 3. 14/07/2005 Brathod for bug#4485801, Version 117.1
99 Inventory Convergence Uptake
100
101 4. 19/05/2005 Ramananda for Bug#4516650, Version 120.2
102 Problem
103 -------
104 The problem is coming in case of customers, migrated from 11.0.3 to 11.5.
105 At the time of claiming the cenvat for receipts of RMIN Items, which are migrated.
106 The system is giving error message - "RG23 Part I Entry was already made for the transaction"
107
108 Fix
109 ---
110 As in 11.0.3, entry in the RG23_I is made at the time of Receipt Creation.
111 So for Migrated receipts, entry is already present in RG23_I.
112 So Commented the Call to get_trxn_entry_cnt in jai_cmn_rg_23ac_i_trxs_pkg.insert_row
113
114 5. 31/10/2006 SACSETHI for bug 5228046, File version 120.3
115 Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
116 This bug has datamodel and spec changes.
117
118 6 06/04/2010 Bug 9550254
119 The opening balance for the RG23 Part I has been derived from the previous
120 financial year closing balance, if no entries found for the current year.
121 7. 27-apr-2010 bug#9466919
122 issue :quantity in rg registers are not in sync with the inventory.
123 fix:
124 added the rounding precision of 5 to the quantity fields while inserting.
125 ----------------------------------------------------------------------------------------------------------------------------*/
126
127 ld_creation_date := SYSDATE;
128 ln_created_by := FND_GLOBAL.user_id;
129 ld_last_update_date := SYSDATE;
130 ln_last_updated_by := ln_created_by;
131 ln_last_update_login := FND_GLOBAL.login_id;
132
133 lv_statement_id := '1';
134 ln_fin_year := jai_general_pkg.get_fin_year(p_organization_id);
135 lv_statement_id := '2';
136 lv_primary_uom_code := jai_general_pkg.get_primary_uom_code(p_organization_id, p_inventory_item_id);
137 lv_statement_id := '3';
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 := '4';
142 get_trxn_type_and_id(lv_transaction_type, p_transaction_source, ln_transaction_id);
143
144 lv_statement_id := '5';
145
146 /*ln_record_exist_cnt := get_trxn_entry_cnt(p_register_type, p_organization_id, p_location_id,
147 p_inventory_item_id, p_receipt_id, ln_transaction_id);
148 IF ln_record_exist_cnt > 0 THEN
149 p_process_status := 'X';
150 p_process_message := 'RG23 Part I Entry was already made for the transaction';
151 GOTO end_of_processing;
152 END IF; */
153 --commented by Ramananda for Bug#4516650
154
155 lv_statement_id := '6';
156 jai_general_pkg.get_range_division(p_vendor_id, p_vendor_site_id, lv_range, lv_division);
157
158 lv_statement_id := '7';
159 vTransToPrimaryUOMConv := jai_general_pkg.trxn_to_primary_conv_rate
160 (p_transaction_uom_code, lv_primary_uom_code, p_inventory_item_id);
161
162 ln_quantity := nvl(p_quantity_received, 0) * vTransToPrimaryUOMConv;
163
164 lv_statement_id := '8';
165 lv_last_register_id := jai_general_pkg.get_last_record_of_rg
166 ('RG23'||p_register_type||'_1', p_organization_id, p_location_id, p_inventory_item_id, ln_fin_year);
167
168 lv_statement_id := '9';
169 /*Bug 9550254 - Start*/
170 /*
171 OPEN c_get_last_record(lv_last_register_id);
172 FETCH c_get_last_record INTO r_last_record;
173 CLOSE c_get_last_record;
174 */
175 ln_opening_balance_qty := jai_om_rg_pkg.ja_in_rg23i_balance(p_organization_id,p_location_id,p_inventory_item_id,
176 ln_fin_year,p_register_type,ln_slno);
177 ln_slno := nvl(ln_slno, 0) + 1;
178
179 /*Commenting the below statements as they are calculated above using jai_om_rg_pkg.ja_in_rg23i_balance*/
180 -- ln_slno := nvl(r_last_record.slno, 0) + 1;
181 -- ln_opening_balance_qty := nvl(r_last_record.closing_balance_qty, 0);
182 /*Bug 9550254 - End*/
183 ln_closing_balance_qty := ln_opening_balance_qty + ln_quantity;
184
185 lv_statement_id := '10';
186 INSERT INTO JAI_CMN_RG_23AC_I_TRXS(
187 REGISTER_ID,
188 FIN_YEAR,
189 SLNO,
190 TRANSACTION_SOURCE_NUM,
191 INVENTORY_ITEM_ID,
192 ORGANIZATION_ID,
193 QUANTITY_RECEIVED,
194 RECEIPT_REF,
195 TRANSACTION_TYPE,
196 RECEIPT_DATE,
197 RANGE_NO,
198 DIVISION_NO,
199 PO_HEADER_ID,
200 PO_HEADER_DATE,
201 PO_LINE_ID,
202 PO_LINE_LOCATION_ID,
203 VENDOR_ID,
204 VENDOR_SITE_ID,
205 CUSTOMER_ID,
206 CUSTOMER_SITE_ID,
207 GOODS_ISSUE_ID_REF,
208 GOODS_ISSUE_DATE,
209 GOODS_ISSUE_QUANTITY,
210 SALES_INVOICE_NO,
211 SALES_INVOICE_DATE,
212 SALES_INVOICE_QUANTITY,
213 EXCISE_INVOICE_NO,
214 EXCISE_INVOICE_DATE,
215 OTH_RECEIPT_QUANTITY,
216 OTH_RECEIPT_ID_REF,
217 OTH_RECEIPT_DATE,
218 REGISTER_TYPE,
219 IDENTIFICATION_NO,
220 IDENTIFICATION_MARK,
221 BRAND_NAME,
222 DATE_OF_VERIFICATION,
223 DATE_OF_INSTALLATION,
224 DATE_OF_COMMISSION,
225 REGISTER_ID_PART_II,
226 PLACE_OF_INSTALL,
227 REMARKS,
228 LOCATION_ID,
229 PRIMARY_UOM_CODE,
230 TRANSACTION_UOM_CODE,
231 TRANSACTION_DATE,
232 BASIC_ED,
233 ADDITIONAL_ED,
234 ADDITIONAL_CVD, -- Bug 5228046 added by sacsethi
235 OTHER_ED,
236 OPENING_BALANCE_QTY,
237 CLOSING_BALANCE_QTY,
238 CHARGE_ACCOUNT_ID,
239 CREATION_DATE,
240 CREATED_BY,
241 LAST_UPDATE_DATE,
242 LAST_UPDATED_BY,
243 LAST_UPDATE_LOGIN,
244 POSTED_FLAG,
245 MASTER_FLAG
246 ) VALUES (
247 JAI_CMN_RG_23AC_I_TRXS_S.nextval,
248 ln_fin_year,
249 ln_slno,
250 ln_transaction_id,
251 P_INVENTORY_ITEM_ID,
252 P_ORGANIZATION_ID,
253 round( P_QUANTITY_RECEIVED,5),
254 P_RECEIPT_ID,
255 lv_transaction_type,
256 P_RECEIPT_DATE,
257 lv_range,
258 lv_division,
259 P_PO_HEADER_ID,
260 P_PO_HEADER_DATE,
261 P_PO_LINE_ID,
262 P_PO_LINE_LOCATION_ID,
263 P_VENDOR_ID,
264 P_VENDOR_SITE_ID,
265 P_CUSTOMER_ID,
266 P_CUSTOMER_SITE_ID,
267 P_GOODS_ISSUE_ID,
268 P_GOODS_ISSUE_DATE,
269 round( P_GOODS_ISSUE_QUANTITY,5),
270 P_SALES_INVOICE_ID,
271 P_SALES_INVOICE_DATE,
272 round( P_SALES_INVOICE_QUANTITY,5),
273 P_EXCISE_INVOICE_ID,
274 P_EXCISE_INVOICE_DATE,
275 round( P_OTH_RECEIPT_QUANTITY,5),
276 P_OTH_RECEIPT_ID,
277 P_OTH_RECEIPT_DATE,
278 P_REGISTER_TYPE,
279 P_IDENTIFICATION_NO,
280 P_IDENTIFICATION_MARK,
281 P_BRAND_NAME,
282 P_DATE_OF_VERIFICATION,
283 P_DATE_OF_INSTALLATION,
284 P_DATE_OF_COMMISSION,
285 P_REGISER_ID_PART_II,
286 P_PLACE_OF_INSTALL,
287 P_REMARKS,
288 P_LOCATION_ID,
289 lv_primary_uom_code,
290 P_TRANSACTION_UOM_CODE,
291 P_TRANSACTION_DATE,
292 P_BASIC_ED,
293 P_ADDITIONAL_ED,
294 P_ADDITIONAL_CVD, -- Bug 5228046 added by sacsethi
295 P_OTHER_ED,
296 round( ln_opening_balance_qty,5),
297 round( ln_closing_balance_qty,5),
298 P_CHARGE_ACCOUNT_ID,
299 ld_creation_date,
300 ln_created_by,
301 ld_last_update_date,
302 ln_last_updated_by,
303 ln_last_update_login,
304 'N',
305 lv_master_flag
306
307 ) RETURNING register_id INTO P_REGISTER_ID;
308
309 <<end_of_processing>>
310
311 NULL;
312 --IF p_process_message IS NOT NULL THEN
313 -- p_process_status := 'E';
314 -- RETURN;
315 --END IF;
316
317 EXCEPTION
318 WHEN OTHERS THEN
319 p_process_status := 'E';
320 p_process_message := 'RG23_PART_I_PKG.insert_row->'||SQLERRM||', StmtId->'||lv_statement_id;
321 FND_FILE.put_line( FND_FILE.log, p_process_message);
322
323 END insert_row;
324
325 PROCEDURE update_row(
326
327 P_REGISTER_ID IN JAI_CMN_RG_23AC_I_TRXS.register_id%TYPE DEFAULT NULL,
328 P_QUANTITY_RECEIVED IN JAI_CMN_RG_23AC_I_TRXS.quantity_received%TYPE DEFAULT NULL,
329 P_RECEIPT_ID IN JAI_CMN_RG_23AC_I_TRXS.RECEIPT_REF%TYPE DEFAULT NULL,
330 P_TRANSACTION_TYPE IN JAI_CMN_RG_23AC_I_TRXS.transaction_type%TYPE DEFAULT NULL,
331 P_RECEIPT_DATE IN JAI_CMN_RG_23AC_I_TRXS.receipt_date%TYPE DEFAULT NULL,
332 P_RANGE_NO IN JAI_CMN_RG_23AC_I_TRXS.range_no%TYPE DEFAULT NULL,
333 P_DIVISION_NO IN JAI_CMN_RG_23AC_I_TRXS.division_no%TYPE DEFAULT NULL,
334 P_PO_HEADER_ID IN JAI_CMN_RG_23AC_I_TRXS.po_header_id%TYPE DEFAULT NULL,
335 P_PO_HEADER_DATE IN JAI_CMN_RG_23AC_I_TRXS.po_header_date%TYPE DEFAULT NULL,
336 P_PO_LINE_ID IN JAI_CMN_RG_23AC_I_TRXS.po_line_id%TYPE DEFAULT NULL,
337 P_PO_LINE_LOCATION_ID IN JAI_CMN_RG_23AC_I_TRXS.po_line_location_id%TYPE DEFAULT NULL,
338 P_VENDOR_ID IN JAI_CMN_RG_23AC_I_TRXS.vendor_id%TYPE DEFAULT NULL,
339 P_VENDOR_SITE_ID IN JAI_CMN_RG_23AC_I_TRXS.vendor_site_id%TYPE DEFAULT NULL,
340 P_CUSTOMER_ID IN JAI_CMN_RG_23AC_I_TRXS.customer_id%TYPE DEFAULT NULL,
341 P_CUSTOMER_SITE_ID IN JAI_CMN_RG_23AC_I_TRXS.customer_site_id%TYPE DEFAULT NULL,
342 P_GOODS_ISSUE_ID IN JAI_CMN_RG_23AC_I_TRXS.GOODS_ISSUE_ID_REF%TYPE DEFAULT NULL,
343 P_GOODS_ISSUE_DATE IN JAI_CMN_RG_23AC_I_TRXS.goods_issue_date%TYPE DEFAULT NULL,
344 P_GOODS_ISSUE_QUANTITY IN JAI_CMN_RG_23AC_I_TRXS.goods_issue_quantity%TYPE DEFAULT NULL,
345 P_SALES_INVOICE_ID IN JAI_CMN_RG_23AC_I_TRXS.SALES_INVOICE_NO%TYPE DEFAULT NULL,
346 P_SALES_INVOICE_DATE IN JAI_CMN_RG_23AC_I_TRXS.sales_invoice_date%TYPE DEFAULT NULL,
347 P_SALES_INVOICE_QUANTITY IN JAI_CMN_RG_23AC_I_TRXS.sales_invoice_quantity%TYPE DEFAULT NULL,
348 P_EXCISE_INVOICE_ID IN JAI_CMN_RG_23AC_I_TRXS.EXCISE_INVOICE_NO%TYPE DEFAULT NULL,
349 P_EXCISE_INVOICE_DATE IN JAI_CMN_RG_23AC_I_TRXS.excise_invoice_date%TYPE DEFAULT NULL,
350 P_OTH_RECEIPT_QUANTITY IN JAI_CMN_RG_23AC_I_TRXS.oth_receipt_quantity%TYPE DEFAULT NULL,
351 P_OTH_RECEIPT_ID IN JAI_CMN_RG_23AC_I_TRXS.OTH_RECEIPT_ID_REF%TYPE DEFAULT NULL,
352 P_OTH_RECEIPT_DATE IN JAI_CMN_RG_23AC_I_TRXS.oth_receipt_date%TYPE DEFAULT NULL,
353 P_REGISTER_TYPE IN JAI_CMN_RG_23AC_I_TRXS.register_type%TYPE DEFAULT NULL,
354 P_IDENTIFICATION_NO IN JAI_CMN_RG_23AC_I_TRXS.identification_no%TYPE DEFAULT NULL,
355 P_IDENTIFICATION_MARK IN JAI_CMN_RG_23AC_I_TRXS.identification_mark%TYPE DEFAULT NULL,
356 P_BRAND_NAME IN JAI_CMN_RG_23AC_I_TRXS.brand_name%TYPE DEFAULT NULL,
357 P_DATE_OF_VERIFICATION IN JAI_CMN_RG_23AC_I_TRXS.date_of_verification%TYPE DEFAULT NULL,
358 P_DATE_OF_INSTALLATION IN JAI_CMN_RG_23AC_I_TRXS.date_of_installation%TYPE DEFAULT NULL,
359 P_DATE_OF_COMMISSION IN JAI_CMN_RG_23AC_I_TRXS.date_of_commission%TYPE DEFAULT NULL,
360 P_REGISER_ID_PART_II IN JAI_CMN_RG_23AC_I_TRXS.REGISTER_ID_PART_II%TYPE DEFAULT NULL,
361 P_PLACE_OF_INSTALL IN JAI_CMN_RG_23AC_I_TRXS.place_of_install%TYPE DEFAULT NULL,
362 P_REMARKS IN JAI_CMN_RG_23AC_I_TRXS.remarks%TYPE DEFAULT NULL,
363 P_BASIC_ED IN JAI_CMN_RG_23AC_I_TRXS.basic_ed%TYPE DEFAULT NULL,
364 P_ADDITIONAL_ED IN JAI_CMN_RG_23AC_I_TRXS.additional_ed%TYPE DEFAULT NULL,
365 P_ADDITIONAL_CVD IN JAI_CMN_RG_23AC_I_TRXS.additional_cvd%TYPE DEFAULT NULL, -- Bug 5228046 added by sacsethi
366 P_OTHER_ED IN JAI_CMN_RG_23AC_I_TRXS.other_ed%TYPE DEFAULT NULL,
367 P_CHARGE_ACCOUNT_ID IN JAI_CMN_RG_23AC_I_TRXS.charge_account_id%TYPE DEFAULT NULL,
368 P_POSTED_FLAG IN JAI_CMN_RG_23AC_I_TRXS.posted_flag%TYPE DEFAULT NULL,
369 P_MASTER_FLAG IN JAI_CMN_RG_23AC_I_TRXS.master_flag%TYPE DEFAULT NULL,
370 P_TRANSACTION_SOURCE IN VARCHAR2,
371 P_CALLED_FROM IN VARCHAR2,
372 P_SIMULATE_FLAG IN VARCHAR2,
373 P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
374 P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2
375 ) IS
376
377 ld_last_update_date DATE;
378 ln_last_updated_by NUMBER(15);
379 ln_last_update_login NUMBER(15);
380
381 ln_slno NUMBER(10) := 0;
382 ln_opening_balance_qty NUMBER;
383 ln_closing_balance_qty NUMBER;
384
385 BEGIN
386
387 ld_last_update_date := SYSDATE;
388 ln_last_updated_by := FND_GLOBAL.user_id;
389 ln_last_update_login := FND_GLOBAL.login_id;
390
391 UPDATE JAI_CMN_RG_23AC_I_TRXS SET
392 QUANTITY_RECEIVED = nvl(round(P_QUANTITY_RECEIVED,5), QUANTITY_RECEIVED),
393 RECEIPT_REF = nvl(P_RECEIPT_ID, RECEIPT_REF),
394 TRANSACTION_TYPE = nvl(P_TRANSACTION_TYPE, TRANSACTION_TYPE),
395 RECEIPT_DATE = nvl(P_RECEIPT_DATE, RECEIPT_DATE),
396 RANGE_NO = nvl(P_RANGE_NO, RANGE_NO),
397 DIVISION_NO = nvl(P_DIVISION_NO, DIVISION_NO),
398 PO_HEADER_ID = nvl(P_PO_HEADER_ID, PO_HEADER_ID),
399 PO_HEADER_DATE = nvl(P_PO_HEADER_DATE, PO_HEADER_DATE),
400 PO_LINE_ID = nvl(P_PO_LINE_ID, PO_LINE_ID),
401 PO_LINE_LOCATION_ID = nvl(P_PO_LINE_LOCATION_ID, PO_LINE_LOCATION_ID),
402 VENDOR_ID = nvl(P_VENDOR_ID, VENDOR_ID),
403 VENDOR_SITE_ID = nvl(P_VENDOR_SITE_ID, VENDOR_SITE_ID),
404 CUSTOMER_ID = nvl(P_CUSTOMER_ID, CUSTOMER_ID),
405 CUSTOMER_SITE_ID = nvl(P_CUSTOMER_SITE_ID, CUSTOMER_SITE_ID),
406 GOODS_ISSUE_ID_REF = nvl(P_GOODS_ISSUE_ID, GOODS_ISSUE_ID_REF),
407 GOODS_ISSUE_DATE = nvl(P_GOODS_ISSUE_DATE, GOODS_ISSUE_DATE),
408 GOODS_ISSUE_QUANTITY = nvl(round(P_GOODS_ISSUE_QUANTITY,5), GOODS_ISSUE_QUANTITY),
409 SALES_INVOICE_NO = nvl(P_SALES_INVOICE_ID, SALES_INVOICE_NO),
410 SALES_INVOICE_DATE = nvl(P_SALES_INVOICE_DATE, SALES_INVOICE_DATE),
411 SALES_INVOICE_QUANTITY = nvl(round(P_SALES_INVOICE_QUANTITY,5), SALES_INVOICE_QUANTITY),
412 EXCISE_INVOICE_NO = nvl(P_EXCISE_INVOICE_ID, EXCISE_INVOICE_NO),
413 EXCISE_INVOICE_DATE = nvl(P_EXCISE_INVOICE_DATE, EXCISE_INVOICE_DATE),
414 OTH_RECEIPT_QUANTITY = nvl(round(P_OTH_RECEIPT_QUANTITY,5), OTH_RECEIPT_QUANTITY),
415 OTH_RECEIPT_ID_REF = nvl(P_OTH_RECEIPT_ID, OTH_RECEIPT_ID_REF),
416 OTH_RECEIPT_DATE = nvl(P_OTH_RECEIPT_DATE, OTH_RECEIPT_DATE),
417 REGISTER_TYPE = nvl(P_REGISTER_TYPE, REGISTER_TYPE),
418 IDENTIFICATION_NO = nvl(P_IDENTIFICATION_NO, IDENTIFICATION_NO),
419 IDENTIFICATION_MARK = nvl(P_IDENTIFICATION_MARK, IDENTIFICATION_MARK),
420 BRAND_NAME = nvl(P_BRAND_NAME, BRAND_NAME),
421 DATE_OF_VERIFICATION = nvl(P_DATE_OF_VERIFICATION, DATE_OF_VERIFICATION),
422 DATE_OF_INSTALLATION = nvl(P_DATE_OF_INSTALLATION, DATE_OF_INSTALLATION),
423 DATE_OF_COMMISSION = nvl(P_DATE_OF_COMMISSION, DATE_OF_COMMISSION),
424 REGISTER_ID_PART_II = nvl(P_REGISER_ID_PART_II, REGISTER_ID_PART_II),
425 PLACE_OF_INSTALL = nvl(P_PLACE_OF_INSTALL, PLACE_OF_INSTALL),
426 REMARKS = nvl(P_REMARKS, REMARKS),
427 BASIC_ED = nvl(P_BASIC_ED, BASIC_ED),
428 ADDITIONAL_ED = nvl(P_ADDITIONAL_ED, ADDITIONAL_ED),
429 ADDITIONAL_CVD = nvl(P_ADDITIONAL_CVD, ADDITIONAL_CVD), --Bug 5228046 added by sacsethi
430 OTHER_ED = nvl(P_OTHER_ED, OTHER_ED),
431 OPENING_BALANCE_QTY = round(ln_opening_balance_qty,5),
432 CLOSING_BALANCE_QTY = round(ln_closing_balance_qty,5),
433 CHARGE_ACCOUNT_ID = nvl(P_CHARGE_ACCOUNT_ID, CHARGE_ACCOUNT_ID),
434 LAST_UPDATE_DATE = ld_last_update_date,
435 LAST_UPDATED_BY = ln_last_updated_by,
436 LAST_UPDATE_LOGIN = ln_last_update_login,
437 POSTED_FLAG = nvl(P_POSTED_FLAG, POSTED_FLAG),
438 MASTER_FLAG = nvl(P_MASTER_FLAG, MASTER_FLAG)
439 WHERE register_id = p_register_id;
440
441 END update_row;
442
443 PROCEDURE update_payment_details(
444 p_register_id IN NUMBER,
445 p_register_id_part_ii IN NUMBER,
446 p_charge_account_id IN NUMBER
447 ) IS
448
449 BEGIN
450
451 UPDATE JAI_CMN_RG_23AC_I_TRXS
452 SET
453 REGISTER_ID_PART_II = p_register_id_part_ii,
454 charge_account_id = p_charge_account_id,
455 last_update_date = SYSDATE
456 WHERE register_id = p_register_id;
457
458 END update_payment_details;
459
460 FUNCTION get_trxn_entry_cnt(
461 p_register_type IN VARCHAR2,
462 p_organization_id IN NUMBER,
463 p_location_id IN NUMBER,
464 p_inventory_item_id IN NUMBER,
465 p_receipt_id IN VARCHAR2,
466 p_transaction_id IN NUMBER
467 ) RETURN NUMBER IS
468
469 ln_record_exist_cnt NUMBER(4);
470 CURSOR c_record_exist IS
471 SELECT count(1)
472 FROM JAI_CMN_RG_23AC_I_TRXS
473 WHERE organization_id = p_organization_id
474 AND location_id = p_location_id
475 AND inventory_item_id = p_inventory_item_id
476 AND register_type = p_register_type
477 AND receipt_ref = p_receipt_id
478 AND TRANSACTION_SOURCE_NUM = p_transaction_id;
479
480 BEGIN
481
482 OPEN c_record_exist;
483 FETCH c_record_exist INTO ln_record_exist_cnt;
484 CLOSE c_record_exist;
485
486 IF ln_record_exist_cnt > 0 THEN
487 FND_FILE.put_line( FND_FILE.log, '23Part1 Duplicate Chk:'||ln_record_exist_cnt
488 ||', PARAMS: Orgn>'||p_organization_id||', Loc>'||p_location_id
489 ||', Item>'||p_inventory_item_id||', Reg>'||p_register_type
490 ||', TrxId>'||p_receipt_id||', type>'||p_transaction_id
491 );
492 END IF;
493
494 RETURN ln_record_exist_cnt;
495
496 END get_trxn_entry_cnt;
497
498 ----------------------- Get transaction id -------------------------------------------
499 PROCEDURE get_trxn_type_and_id(
500 p_transaction_type IN OUT NOCOPY VARCHAR2,
501 p_transaction_source IN VARCHAR2,
502 p_transaction_id OUT NOCOPY NUMBER
503 ) IS
504
505 BEGIN
506
507 IF p_transaction_type = 'RECEIVE' AND p_transaction_source = 'RMA' THEN
508 p_transaction_id := 18;
509 p_transaction_type := 'CR';
510 ELSIF p_transaction_type = 'RECEIVE' THEN
511 p_transaction_id := 18;
512 p_transaction_type := 'R';
513 ELSIF p_transaction_type = 'RETURN TO RECEIVING' THEN
514 p_transaction_id := 18;
515 p_transaction_type := 'R';
516 ELSIF p_transaction_type = 'DELIVER' THEN
517 p_transaction_id := 18;
518 p_transaction_type := 'R';
519 ELSIF p_transaction_type = 'RETURN TO VENDOR' THEN
520 p_transaction_id := 18;
521 p_transaction_type := 'RTV';
522 -- Added by Brathod, for Inv.Convergence
523 ELSIF p_transaction_source = 'OPM_OSP' THEN
524 IF p_transaction_type = 'R' THEN
525 p_transaction_id := 202;
526 ELSIF p_transaction_type = 'I' THEN
527 p_transaction_id := 201;
528 END IF;
529 ELSE
530 p_transaction_id := 20;
531 p_transaction_type := 'MISC';
532 END IF;
533
534 END get_trxn_type_and_id;
535
536 END jai_cmn_rg_23ac_i_trxs_pkg;