[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 2006/11/27 06:37:01 sacsethi 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
119 ld_creation_date := SYSDATE;
120 ln_created_by := FND_GLOBAL.user_id;
121 ld_last_update_date := SYSDATE;
122 ln_last_updated_by := ln_created_by;
123 ln_last_update_login := FND_GLOBAL.login_id;
124
125 lv_statement_id := '1';
126 ln_fin_year := jai_general_pkg.get_fin_year(p_organization_id);
127 lv_statement_id := '2';
128 lv_primary_uom_code := jai_general_pkg.get_primary_uom_code(p_organization_id, p_inventory_item_id);
129 lv_statement_id := '3';
130 lv_master_flag := jai_general_pkg.get_orgn_master_flag(p_organization_id, p_location_id);
131
132 lv_transaction_type := p_transaction_type;
133 lv_statement_id := '4';
134 get_trxn_type_and_id(lv_transaction_type, p_transaction_source, ln_transaction_id);
135
136 lv_statement_id := '5';
137
138 /*ln_record_exist_cnt := get_trxn_entry_cnt(p_register_type, p_organization_id, p_location_id,
139 p_inventory_item_id, p_receipt_id, ln_transaction_id);
140 IF ln_record_exist_cnt > 0 THEN
141 p_process_status := 'X';
142 p_process_message := 'RG23 Part I Entry was already made for the transaction';
143 GOTO end_of_processing;
144 END IF; */
145 --commented by Ramananda for Bug#4516650
146
147 lv_statement_id := '6';
148 jai_general_pkg.get_range_division(p_vendor_id, p_vendor_site_id, lv_range, lv_division);
149
150 lv_statement_id := '7';
151 vTransToPrimaryUOMConv := jai_general_pkg.trxn_to_primary_conv_rate
152 (p_transaction_uom_code, lv_primary_uom_code, p_inventory_item_id);
153
154 ln_quantity := nvl(p_quantity_received, 0) * vTransToPrimaryUOMConv;
155
156 lv_statement_id := '8';
157 lv_last_register_id := jai_general_pkg.get_last_record_of_rg
158 ('RG23'||p_register_type||'_1', p_organization_id, p_location_id, p_inventory_item_id, ln_fin_year);
159
160 lv_statement_id := '9';
161 OPEN c_get_last_record(lv_last_register_id);
162 FETCH c_get_last_record INTO r_last_record;
163 CLOSE c_get_last_record;
164
165 ln_slno := nvl(r_last_record.slno, 0) + 1;
166 ln_opening_balance_qty := nvl(r_last_record.closing_balance_qty, 0);
167 ln_closing_balance_qty := ln_opening_balance_qty + ln_quantity;
168
169 lv_statement_id := '10';
170 INSERT INTO JAI_CMN_RG_23AC_I_TRXS(
171 REGISTER_ID,
172 FIN_YEAR,
173 SLNO,
174 TRANSACTION_SOURCE_NUM,
175 INVENTORY_ITEM_ID,
176 ORGANIZATION_ID,
177 QUANTITY_RECEIVED,
178 RECEIPT_REF,
179 TRANSACTION_TYPE,
180 RECEIPT_DATE,
181 RANGE_NO,
182 DIVISION_NO,
183 PO_HEADER_ID,
184 PO_HEADER_DATE,
185 PO_LINE_ID,
186 PO_LINE_LOCATION_ID,
187 VENDOR_ID,
188 VENDOR_SITE_ID,
189 CUSTOMER_ID,
190 CUSTOMER_SITE_ID,
191 GOODS_ISSUE_ID_REF,
192 GOODS_ISSUE_DATE,
193 GOODS_ISSUE_QUANTITY,
194 SALES_INVOICE_NO,
195 SALES_INVOICE_DATE,
196 SALES_INVOICE_QUANTITY,
197 EXCISE_INVOICE_NO,
198 EXCISE_INVOICE_DATE,
199 OTH_RECEIPT_QUANTITY,
200 OTH_RECEIPT_ID_REF,
201 OTH_RECEIPT_DATE,
202 REGISTER_TYPE,
203 IDENTIFICATION_NO,
204 IDENTIFICATION_MARK,
205 BRAND_NAME,
206 DATE_OF_VERIFICATION,
207 DATE_OF_INSTALLATION,
208 DATE_OF_COMMISSION,
209 REGISTER_ID_PART_II,
210 PLACE_OF_INSTALL,
211 REMARKS,
212 LOCATION_ID,
213 PRIMARY_UOM_CODE,
214 TRANSACTION_UOM_CODE,
215 TRANSACTION_DATE,
216 BASIC_ED,
217 ADDITIONAL_ED,
218 ADDITIONAL_CVD, -- Bug 5228046 added by sacsethi
219 OTHER_ED,
220 OPENING_BALANCE_QTY,
221 CLOSING_BALANCE_QTY,
222 CHARGE_ACCOUNT_ID,
223 CREATION_DATE,
224 CREATED_BY,
225 LAST_UPDATE_DATE,
226 LAST_UPDATED_BY,
227 LAST_UPDATE_LOGIN,
228 POSTED_FLAG,
229 MASTER_FLAG
230 ) VALUES (
231 JAI_CMN_RG_23AC_I_TRXS_S.nextval,
232 ln_fin_year,
233 ln_slno,
234 ln_transaction_id,
235 P_INVENTORY_ITEM_ID,
236 P_ORGANIZATION_ID,
237 P_QUANTITY_RECEIVED,
238 P_RECEIPT_ID,
239 lv_transaction_type,
240 P_RECEIPT_DATE,
241 lv_range,
242 lv_division,
243 P_PO_HEADER_ID,
244 P_PO_HEADER_DATE,
245 P_PO_LINE_ID,
246 P_PO_LINE_LOCATION_ID,
247 P_VENDOR_ID,
248 P_VENDOR_SITE_ID,
249 P_CUSTOMER_ID,
250 P_CUSTOMER_SITE_ID,
251 P_GOODS_ISSUE_ID,
252 P_GOODS_ISSUE_DATE,
253 P_GOODS_ISSUE_QUANTITY,
254 P_SALES_INVOICE_ID,
255 P_SALES_INVOICE_DATE,
256 P_SALES_INVOICE_QUANTITY,
257 P_EXCISE_INVOICE_ID,
258 P_EXCISE_INVOICE_DATE,
259 P_OTH_RECEIPT_QUANTITY,
260 P_OTH_RECEIPT_ID,
261 P_OTH_RECEIPT_DATE,
262 P_REGISTER_TYPE,
263 P_IDENTIFICATION_NO,
264 P_IDENTIFICATION_MARK,
265 P_BRAND_NAME,
266 P_DATE_OF_VERIFICATION,
267 P_DATE_OF_INSTALLATION,
268 P_DATE_OF_COMMISSION,
269 P_REGISER_ID_PART_II,
270 P_PLACE_OF_INSTALL,
271 P_REMARKS,
272 P_LOCATION_ID,
273 lv_primary_uom_code,
274 P_TRANSACTION_UOM_CODE,
275 P_TRANSACTION_DATE,
276 P_BASIC_ED,
277 P_ADDITIONAL_ED,
278 P_ADDITIONAL_CVD, -- Bug 5228046 added by sacsethi
279 P_OTHER_ED,
280 ln_opening_balance_qty,
281 ln_closing_balance_qty,
282 P_CHARGE_ACCOUNT_ID,
283 ld_creation_date,
284 ln_created_by,
285 ld_last_update_date,
286 ln_last_updated_by,
287 ln_last_update_login,
288 'N',
289 lv_master_flag
290
291 ) RETURNING register_id INTO P_REGISTER_ID;
292
293 <<end_of_processing>>
294
295 NULL;
296 --IF p_process_message IS NOT NULL THEN
297 -- p_process_status := 'E';
298 -- RETURN;
299 --END IF;
300
301 EXCEPTION
302 WHEN OTHERS THEN
303 p_process_status := 'E';
304 p_process_message := 'RG23_PART_I_PKG.insert_row->'||SQLERRM||', StmtId->'||lv_statement_id;
305 FND_FILE.put_line( FND_FILE.log, p_process_message);
306
307 END insert_row;
308
309 PROCEDURE update_row(
310
311 P_REGISTER_ID IN JAI_CMN_RG_23AC_I_TRXS.register_id%TYPE DEFAULT NULL,
312 P_QUANTITY_RECEIVED IN JAI_CMN_RG_23AC_I_TRXS.quantity_received%TYPE DEFAULT NULL,
313 P_RECEIPT_ID IN JAI_CMN_RG_23AC_I_TRXS.RECEIPT_REF%TYPE DEFAULT NULL,
314 P_TRANSACTION_TYPE IN JAI_CMN_RG_23AC_I_TRXS.transaction_type%TYPE DEFAULT NULL,
315 P_RECEIPT_DATE IN JAI_CMN_RG_23AC_I_TRXS.receipt_date%TYPE DEFAULT NULL,
316 P_RANGE_NO IN JAI_CMN_RG_23AC_I_TRXS.range_no%TYPE DEFAULT NULL,
317 P_DIVISION_NO IN JAI_CMN_RG_23AC_I_TRXS.division_no%TYPE DEFAULT NULL,
318 P_PO_HEADER_ID IN JAI_CMN_RG_23AC_I_TRXS.po_header_id%TYPE DEFAULT NULL,
319 P_PO_HEADER_DATE IN JAI_CMN_RG_23AC_I_TRXS.po_header_date%TYPE DEFAULT NULL,
320 P_PO_LINE_ID IN JAI_CMN_RG_23AC_I_TRXS.po_line_id%TYPE DEFAULT NULL,
321 P_PO_LINE_LOCATION_ID IN JAI_CMN_RG_23AC_I_TRXS.po_line_location_id%TYPE DEFAULT NULL,
322 P_VENDOR_ID IN JAI_CMN_RG_23AC_I_TRXS.vendor_id%TYPE DEFAULT NULL,
323 P_VENDOR_SITE_ID IN JAI_CMN_RG_23AC_I_TRXS.vendor_site_id%TYPE DEFAULT NULL,
324 P_CUSTOMER_ID IN JAI_CMN_RG_23AC_I_TRXS.customer_id%TYPE DEFAULT NULL,
325 P_CUSTOMER_SITE_ID IN JAI_CMN_RG_23AC_I_TRXS.customer_site_id%TYPE DEFAULT NULL,
326 P_GOODS_ISSUE_ID IN JAI_CMN_RG_23AC_I_TRXS.GOODS_ISSUE_ID_REF%TYPE DEFAULT NULL,
327 P_GOODS_ISSUE_DATE IN JAI_CMN_RG_23AC_I_TRXS.goods_issue_date%TYPE DEFAULT NULL,
328 P_GOODS_ISSUE_QUANTITY IN JAI_CMN_RG_23AC_I_TRXS.goods_issue_quantity%TYPE DEFAULT NULL,
329 P_SALES_INVOICE_ID IN JAI_CMN_RG_23AC_I_TRXS.SALES_INVOICE_NO%TYPE DEFAULT NULL,
330 P_SALES_INVOICE_DATE IN JAI_CMN_RG_23AC_I_TRXS.sales_invoice_date%TYPE DEFAULT NULL,
331 P_SALES_INVOICE_QUANTITY IN JAI_CMN_RG_23AC_I_TRXS.sales_invoice_quantity%TYPE DEFAULT NULL,
332 P_EXCISE_INVOICE_ID IN JAI_CMN_RG_23AC_I_TRXS.EXCISE_INVOICE_NO%TYPE DEFAULT NULL,
333 P_EXCISE_INVOICE_DATE IN JAI_CMN_RG_23AC_I_TRXS.excise_invoice_date%TYPE DEFAULT NULL,
334 P_OTH_RECEIPT_QUANTITY IN JAI_CMN_RG_23AC_I_TRXS.oth_receipt_quantity%TYPE DEFAULT NULL,
335 P_OTH_RECEIPT_ID IN JAI_CMN_RG_23AC_I_TRXS.OTH_RECEIPT_ID_REF%TYPE DEFAULT NULL,
336 P_OTH_RECEIPT_DATE IN JAI_CMN_RG_23AC_I_TRXS.oth_receipt_date%TYPE DEFAULT NULL,
337 P_REGISTER_TYPE IN JAI_CMN_RG_23AC_I_TRXS.register_type%TYPE DEFAULT NULL,
341 P_DATE_OF_VERIFICATION IN JAI_CMN_RG_23AC_I_TRXS.date_of_verification%TYPE DEFAULT NULL,
338 P_IDENTIFICATION_NO IN JAI_CMN_RG_23AC_I_TRXS.identification_no%TYPE DEFAULT NULL,
339 P_IDENTIFICATION_MARK IN JAI_CMN_RG_23AC_I_TRXS.identification_mark%TYPE DEFAULT NULL,
340 P_BRAND_NAME IN JAI_CMN_RG_23AC_I_TRXS.brand_name%TYPE DEFAULT NULL,
342 P_DATE_OF_INSTALLATION IN JAI_CMN_RG_23AC_I_TRXS.date_of_installation%TYPE DEFAULT NULL,
343 P_DATE_OF_COMMISSION IN JAI_CMN_RG_23AC_I_TRXS.date_of_commission%TYPE DEFAULT NULL,
344 P_REGISER_ID_PART_II IN JAI_CMN_RG_23AC_I_TRXS.REGISTER_ID_PART_II%TYPE DEFAULT NULL,
345 P_PLACE_OF_INSTALL IN JAI_CMN_RG_23AC_I_TRXS.place_of_install%TYPE DEFAULT NULL,
346 P_REMARKS IN JAI_CMN_RG_23AC_I_TRXS.remarks%TYPE DEFAULT NULL,
347 P_BASIC_ED IN JAI_CMN_RG_23AC_I_TRXS.basic_ed%TYPE DEFAULT NULL,
348 P_ADDITIONAL_ED IN JAI_CMN_RG_23AC_I_TRXS.additional_ed%TYPE DEFAULT NULL,
349 P_ADDITIONAL_CVD IN JAI_CMN_RG_23AC_I_TRXS.additional_cvd%TYPE DEFAULT NULL, -- Bug 5228046 added by sacsethi
350 P_OTHER_ED IN JAI_CMN_RG_23AC_I_TRXS.other_ed%TYPE DEFAULT NULL,
351 P_CHARGE_ACCOUNT_ID IN JAI_CMN_RG_23AC_I_TRXS.charge_account_id%TYPE DEFAULT NULL,
352 P_POSTED_FLAG IN JAI_CMN_RG_23AC_I_TRXS.posted_flag%TYPE DEFAULT NULL,
353 P_MASTER_FLAG IN JAI_CMN_RG_23AC_I_TRXS.master_flag%TYPE DEFAULT NULL,
354 P_TRANSACTION_SOURCE IN VARCHAR2,
355 P_CALLED_FROM IN VARCHAR2,
356 P_SIMULATE_FLAG IN VARCHAR2,
357 P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
358 P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2
359 ) IS
360
361 ld_last_update_date DATE;
362 ln_last_updated_by NUMBER(15);
363 ln_last_update_login NUMBER(15);
364
365 ln_slno NUMBER(10) := 0;
366 ln_opening_balance_qty NUMBER;
367 ln_closing_balance_qty NUMBER;
368
369 BEGIN
370
371 ld_last_update_date := SYSDATE;
372 ln_last_updated_by := FND_GLOBAL.user_id;
373 ln_last_update_login := FND_GLOBAL.login_id;
374
375 UPDATE JAI_CMN_RG_23AC_I_TRXS SET
376 QUANTITY_RECEIVED = nvl(P_QUANTITY_RECEIVED, QUANTITY_RECEIVED),
377 RECEIPT_REF = nvl(P_RECEIPT_ID, RECEIPT_REF),
378 TRANSACTION_TYPE = nvl(P_TRANSACTION_TYPE, TRANSACTION_TYPE),
379 RECEIPT_DATE = nvl(P_RECEIPT_DATE, RECEIPT_DATE),
380 RANGE_NO = nvl(P_RANGE_NO, RANGE_NO),
381 DIVISION_NO = nvl(P_DIVISION_NO, DIVISION_NO),
382 PO_HEADER_ID = nvl(P_PO_HEADER_ID, PO_HEADER_ID),
383 PO_HEADER_DATE = nvl(P_PO_HEADER_DATE, PO_HEADER_DATE),
384 PO_LINE_ID = nvl(P_PO_LINE_ID, PO_LINE_ID),
385 PO_LINE_LOCATION_ID = nvl(P_PO_LINE_LOCATION_ID, PO_LINE_LOCATION_ID),
386 VENDOR_ID = nvl(P_VENDOR_ID, VENDOR_ID),
387 VENDOR_SITE_ID = nvl(P_VENDOR_SITE_ID, VENDOR_SITE_ID),
388 CUSTOMER_ID = nvl(P_CUSTOMER_ID, CUSTOMER_ID),
389 CUSTOMER_SITE_ID = nvl(P_CUSTOMER_SITE_ID, CUSTOMER_SITE_ID),
390 GOODS_ISSUE_ID_REF = nvl(P_GOODS_ISSUE_ID, GOODS_ISSUE_ID_REF),
391 GOODS_ISSUE_DATE = nvl(P_GOODS_ISSUE_DATE, GOODS_ISSUE_DATE),
392 GOODS_ISSUE_QUANTITY = nvl(P_GOODS_ISSUE_QUANTITY, GOODS_ISSUE_QUANTITY),
393 SALES_INVOICE_NO = nvl(P_SALES_INVOICE_ID, SALES_INVOICE_NO),
394 SALES_INVOICE_DATE = nvl(P_SALES_INVOICE_DATE, SALES_INVOICE_DATE),
395 SALES_INVOICE_QUANTITY = nvl(P_SALES_INVOICE_QUANTITY, SALES_INVOICE_QUANTITY),
396 EXCISE_INVOICE_NO = nvl(P_EXCISE_INVOICE_ID, EXCISE_INVOICE_NO),
397 EXCISE_INVOICE_DATE = nvl(P_EXCISE_INVOICE_DATE, EXCISE_INVOICE_DATE),
398 OTH_RECEIPT_QUANTITY = nvl(P_OTH_RECEIPT_QUANTITY, OTH_RECEIPT_QUANTITY),
399 OTH_RECEIPT_ID_REF = nvl(P_OTH_RECEIPT_ID, OTH_RECEIPT_ID_REF),
400 OTH_RECEIPT_DATE = nvl(P_OTH_RECEIPT_DATE, OTH_RECEIPT_DATE),
401 REGISTER_TYPE = nvl(P_REGISTER_TYPE, REGISTER_TYPE),
402 IDENTIFICATION_NO = nvl(P_IDENTIFICATION_NO, IDENTIFICATION_NO),
403 IDENTIFICATION_MARK = nvl(P_IDENTIFICATION_MARK, IDENTIFICATION_MARK),
404 BRAND_NAME = nvl(P_BRAND_NAME, BRAND_NAME),
405 DATE_OF_VERIFICATION = nvl(P_DATE_OF_VERIFICATION, DATE_OF_VERIFICATION),
406 DATE_OF_INSTALLATION = nvl(P_DATE_OF_INSTALLATION, DATE_OF_INSTALLATION),
407 DATE_OF_COMMISSION = nvl(P_DATE_OF_COMMISSION, DATE_OF_COMMISSION),
408 REGISTER_ID_PART_II = nvl(P_REGISER_ID_PART_II, REGISTER_ID_PART_II),
409 PLACE_OF_INSTALL = nvl(P_PLACE_OF_INSTALL, PLACE_OF_INSTALL),
410 REMARKS = nvl(P_REMARKS, REMARKS),
411 BASIC_ED = nvl(P_BASIC_ED, BASIC_ED),
412 ADDITIONAL_ED = nvl(P_ADDITIONAL_ED, ADDITIONAL_ED),
413 ADDITIONAL_CVD = nvl(P_ADDITIONAL_CVD, ADDITIONAL_CVD), --Bug 5228046 added by sacsethi
417 CHARGE_ACCOUNT_ID = nvl(P_CHARGE_ACCOUNT_ID, CHARGE_ACCOUNT_ID),
414 OTHER_ED = nvl(P_OTHER_ED, OTHER_ED),
415 OPENING_BALANCE_QTY = ln_opening_balance_qty,
416 CLOSING_BALANCE_QTY = ln_closing_balance_qty,
418 LAST_UPDATE_DATE = ld_last_update_date,
419 LAST_UPDATED_BY = ln_last_updated_by,
420 LAST_UPDATE_LOGIN = ln_last_update_login,
421 POSTED_FLAG = nvl(P_POSTED_FLAG, POSTED_FLAG),
422 MASTER_FLAG = nvl(P_MASTER_FLAG, MASTER_FLAG)
423 WHERE register_id = p_register_id;
424
425 END update_row;
426
427 PROCEDURE update_payment_details(
428 p_register_id IN NUMBER,
429 p_register_id_part_ii IN NUMBER,
430 p_charge_account_id IN NUMBER
431 ) IS
432
433 BEGIN
434
435 UPDATE JAI_CMN_RG_23AC_I_TRXS
436 SET
437 REGISTER_ID_PART_II = p_register_id_part_ii,
438 charge_account_id = p_charge_account_id,
439 last_update_date = SYSDATE
440 WHERE register_id = p_register_id;
441
442 END update_payment_details;
443
444 FUNCTION get_trxn_entry_cnt(
445 p_register_type IN VARCHAR2,
446 p_organization_id IN NUMBER,
447 p_location_id IN NUMBER,
448 p_inventory_item_id IN NUMBER,
449 p_receipt_id IN VARCHAR2,
450 p_transaction_id IN NUMBER
451 ) RETURN NUMBER IS
452
453 ln_record_exist_cnt NUMBER(4);
454 CURSOR c_record_exist IS
455 SELECT count(1)
456 FROM JAI_CMN_RG_23AC_I_TRXS
457 WHERE organization_id = p_organization_id
458 AND location_id = p_location_id
459 AND inventory_item_id = p_inventory_item_id
460 AND register_type = p_register_type
461 AND receipt_ref = p_receipt_id
462 AND TRANSACTION_SOURCE_NUM = p_transaction_id;
463
464 BEGIN
465
466 OPEN c_record_exist;
467 FETCH c_record_exist INTO ln_record_exist_cnt;
468 CLOSE c_record_exist;
469
470 IF ln_record_exist_cnt > 0 THEN
471 FND_FILE.put_line( FND_FILE.log, '23Part1 Duplicate Chk:'||ln_record_exist_cnt
472 ||', PARAMS: Orgn>'||p_organization_id||', Loc>'||p_location_id
473 ||', Item>'||p_inventory_item_id||', Reg>'||p_register_type
474 ||', TrxId>'||p_receipt_id||', type>'||p_transaction_id
475 );
476 END IF;
477
478 RETURN ln_record_exist_cnt;
479
480 END get_trxn_entry_cnt;
481
482 ----------------------- Get transaction id -------------------------------------------
483 PROCEDURE get_trxn_type_and_id(
484 p_transaction_type IN OUT NOCOPY VARCHAR2,
485 p_transaction_source IN VARCHAR2,
486 p_transaction_id OUT NOCOPY NUMBER
487 ) IS
488
489 BEGIN
490
491 IF p_transaction_type = 'RECEIVE' AND p_transaction_source = 'RMA' THEN
492 p_transaction_id := 18;
493 p_transaction_type := 'CR';
494 ELSIF p_transaction_type = 'RECEIVE' THEN
495 p_transaction_id := 18;
496 p_transaction_type := 'R';
497 ELSIF p_transaction_type = 'RETURN TO RECEIVING' THEN
498 p_transaction_id := 18;
499 p_transaction_type := 'R';
500 ELSIF p_transaction_type = 'DELIVER' THEN
501 p_transaction_id := 18;
502 p_transaction_type := 'R';
503 ELSIF p_transaction_type = 'RETURN TO VENDOR' THEN
504 p_transaction_id := 18;
505 p_transaction_type := 'RTV';
506 -- Added by Brathod, for Inv.Convergence
507 ELSIF p_transaction_source = 'OPM_OSP' THEN
508 IF p_transaction_type = 'R' THEN
509 p_transaction_id := 202;
510 ELSIF p_transaction_type = 'I' THEN
511 p_transaction_id := 201;
512 END IF;
513 ELSE
514 p_transaction_id := 20;
515 p_transaction_type := 'MISC';
516 END IF;
517
518 END get_trxn_type_and_id;
519
520 END jai_cmn_rg_23ac_i_trxs_pkg;