4 /* --------------------------------------------------------------------------------------
1 PACKAGE BODY jai_cmn_rg_master_org_pkg AS
2 /* $Header: jai_cmn_rg_mst.plb 120.5.12020000.2 2012/07/16 12:06:09 nkodakan ship $ */
3
5 Filename:
6
7 Change History:
8
9 Date Remarks
10 --------- -------------------------------------------------------------
11 08-Jun-2005 File Version 116.1 Object is Modified to refer to New DB Entity names
12 in place of Old DB Entity Names as required for CASE COMPLAINCE.
13
14 14-Jun-2005 rchandan for bug#4428980, Version 116.2
15 Modified the object to remove literals from DML statements and CURSORS.
16
17 02-Sep-2005 Ramananda for Bug#4589502, File version 120.2
18 Proceudre CONSOLIDATE_RG_I
19 ==========================
20 Changed the statement for opening the cursor - balance_cur
21
22 06-Nov-2008 Bug 6118417 (FP for bug 6112850) File version 120.2.12010000.2 / 120.3
23 Description : When doing consolidation, the rg23 part 2 records for master
24 organization gets inserted with the creation_date as sysdate. But the RG23
25 part II report is filtering the rows based on creation date. Due to this
26 the report output becomes wrong.
27 Resolution : When populating jai_cmn_rg_23ac_ii_trxs and jai_cmn_rg_others
28 table for the master org, creation_date (and other who columns) are copied from
29 the child org record instead of using the session values.
30 Following procedures are modified:
31 1. consolidate_rg23_part_ii
32 2. insert_rg23_others
33
34 Feb 18, 2010 Bug 9382720
35 Added Additional CVD as it is not getting consolidated in the Master Org
36 Inserted Addl CVD into JAI_CMN_RG_23AC_II_TRXS and JAI_CMN_RG_23AC_I_TRXS
37
38 Apr 06, 2010 Bug 9550254
39 The opening balance for the RG23 Part I has been derived
40 from the previous financial year closing balance,
41 if no entries found for the current year.
42
43 --------------------------------------------------------------------------------------*/
44
45
46 PROCEDURE insert_rg23_others
47 (ERRBUF OUT NOCOPY VARCHAR2,
48 RETCODE OUT NOCOPY VARCHAR2,
49 p_previous_serial_no IN JAI_CMN_RG_23AC_II_TRXS.slno%TYPE,
50 p_tax_type IN JAI_CMN_RG_OTHERS.tax_type%TYPE,
51 p_register_id IN JAI_CMN_RG_23AC_II_TRXS.register_id%TYPE)
52 AS
53 Cursor rg_others_cur( p_register_id IN Number, p_tax_type JAI_CMN_RG_OTHERS.tax_type%TYPE ) IS
54 Select *
55 from JAI_CMN_RG_OTHERS
56 where source_register_id = p_register_id
60 rg_others_rec rg_others_cur%ROWTYPE ;
57 and source_type = 1
58 and tax_type = p_tax_type;
59
61
62
63 BEGIN
64 /*------------------------------------------------------------------------------------------
65 FILENAME: ja_in_master_org_other_taxes_p.sql
66
67 Harshita J: /**********************************************************************
68 CREATED BY : hjujjuru
69 CREATED DATE : 11-JAN-2005
70 ENHANCEMENT BUG : 4106667
71 PURPOSE : To consolidate the data in the table JAI_CMN_RG_OTHERS as a part of the Master-Org Consolidation
72 of JAI_CMN_RG_23AC_II_TRXS table
73 CALLED FROM : Called from the Concurrent - Master Org RG Entries Request Set
74
75 **********************************************************************/
76 /*
77
78 CHANGE HISTORY:
79
80 1. 2005/01/28 Harshita.J - For Bug #410667 Version - 115.0
81 Base Bug has been changed.
82 Base Bug #4146708. This bug creates all the database objects.
83
84 1. 2005/02/03 Harshita.J - For Bug #410667 Version - 115.1
85 Incorrect phase information has been entered in the phase comment during the
86 previous check in. This has been updated.
87
88
89 --------------------------------------------------------------------------------------------*/
90
91 -- Get the details of the existing record
92 OPEN rg_others_cur(p_register_id, p_tax_type);
93 FETCH rg_others_cur INTO rg_others_rec;
94
95 IF rg_others_cur%FOUND THEN
96 insert into JAI_CMN_RG_OTHERS
97 (
98 rg_other_id,
99 source_type,
100 source_register,
101 source_register_id,
102 tax_type,
103 credit,
104 debit,
105 created_by,
106 creation_date,
107 last_updated_by,
108 last_update_date
109 )
110 values
111 (
112 -rg_others_rec.rg_other_id,
113 rg_others_rec.source_type,
114 rg_others_rec.source_register,
115 -rg_others_rec.source_register_id,
116 rg_others_rec.tax_type,
117 rg_others_rec.credit,
118 rg_others_rec.debit,
119 /*start changes for bug 6118417 (FP for bug 6112850)*/
120 rg_others_rec.created_by, --FND_GLOBAL.USER_ID,
121 rg_others_rec.creation_date, --SYSDATE,
122 rg_others_rec.last_updated_by, --FND_GLOBAL.USER_ID,
123 rg_others_rec.last_update_date --SYSDATE
124 /*end bug 6118417*/
125 );
126
127 END IF ;
128
129 CLOSE rg_others_cur;
130
131 -- RETCODE := '0';
132 -- ERRBUF := NULL;
133 EXCEPTION
134 WHEN OTHERS THEN
135
136 IF rg_others_cur%ISOPEN then
137 close rg_others_cur;
138 end if;
139 RETCODE := '2' ;
140 ERRBUF := ' Error Encountered in - jai_cmn_rg_master_org_pkg.insert_rg23_others ' || substr(SQLERRM,1,1000);
141 END insert_rg23_others;
142
143
144 PROCEDURE insert_pla_others
145 (ERRBUF OUT NOCOPY VARCHAR2,
146 RETCODE OUT NOCOPY VARCHAR2,
147 p_previous_serial_no IN JAI_CMN_RG_PLA_TRXS.slno%TYPE,
148 p_tax_type IN JAI_CMN_RG_OTHERS.tax_type%TYPE,
149 p_register_id IN JAI_CMN_RG_PLA_TRXS.register_id%TYPE)
150 AS
151
152 Cursor rg_others_cur( p_register_id IN Number, p_tax_type JAI_CMN_RG_OTHERS.tax_type%TYPE ) IS
153 Select * from JAI_CMN_RG_OTHERS
154 where source_register_id = p_register_id
155 and source_type = 2
156 and tax_type = p_tax_type;
157
158 rg_others_rec rg_others_cur%ROWTYPE ;
159
160
161 BEGIN
162
163 /*------------------------------------------------------------------------------------------
164 FILENAME: jai_cmn_rg_master_org_pkg.sql
165 Harshita J: /**********************************************************************
166 CREATED BY : hjujjuru
167 CREATED DATE : 11-JAN-2005
168 ENHANCEMENT BUG : 4106667
169 PURPOSE : To consolidate the data in the table JAI_CMN_RG_OTHERS as a part of the Master-Org Consolidation
170 of JAI_CMN_RG_23AC_II_TRXS table
171 CALLED FROM : Called from the Concurrent - Master Org RG Entries Request Set
172
173 CHANGE HISTORY:
174
175 1. 2005/01/28 Harshita.J - For Bug #410667 Version - 115.1
176 Base Bug has been changed.
177 Base Bug #4146708. This bug creates all the database objects.
178
179 --------------------------------------------------------------------------------------------*/
180
181
182
183 -- Get the details of the existing record
184
185
186 OPEN rg_others_cur(p_register_id, p_tax_type);
187 FETCH rg_others_cur INTO rg_others_rec;
188
189
190 IF rg_others_cur%FOUND THEN
191
192 insert into JAI_CMN_RG_OTHERS
193 (
194 rg_other_id,
195 source_type,
196 source_register,
197 source_register_id,
198 tax_type,
199 credit,
200 debit,
201 created_by,
202 creation_date,
203 last_updated_by,
204 last_update_date
205 )
206 values
207 (
208 -rg_others_rec.rg_other_id,
209 rg_others_rec.source_type,
210 rg_others_rec.source_register,
211 -rg_others_rec.source_register_id,
212 rg_others_rec.tax_type,
213 rg_others_rec.credit,
214 rg_others_rec.debit,
215 FND_GLOBAL.USER_ID,
216 SYSDATE,
217 FND_GLOBAL.USER_ID,
218 SYSDATE
219 );
220 END IF ;
221
222 CLOSE rg_others_cur;
223
224 RETCODE := '0';
225 ERRBUF := NULL;
226 EXCEPTION
227 WHEN OTHERS THEN
228
229 RETCODE := '2' ;
230 ERRBUF := ' Error Encountered in - jai_cmn_rg_master_org_pkg ' || substr(SQLERRM,1,1000);
231 --RAISE_APPLICATION_ERROR(-20001,'This Concurrent Program has ended in an Error ' || SQLERRM);
232 END insert_pla_others;
233
234
235 PROCEDURE consolidate_rg23_part_i
236 (errbuf OUT NOCOPY VARCHAR2,
237 retcode OUT NOCOPY VARCHAR2,
238 p_organization_id IN NUMBER,
239 p_location_id IN NUMBER) as
240
241 Cursor master_ec_code_cur(p_organization_id IN NUMBER, p_location_id IN NUMBER) IS
242 Select ec_code
243 from JAI_CMN_INVENTORY_ORGS
244 Where organization_id = p_organization_id
245 And location_id = nvl(p_location_id,0);
246 v_ec_code Varchar2(50);
247
248 Cursor rg23_part_i_cur(p_register_id IN Number) IS
249 Select *
250 From JAI_CMN_RG_23AC_I_TRXS a
251 Where a.register_id = p_register_id;
252 rg23_rec rg23_part_i_cur%ROWTYPE;
253
254 Cursor rg23_part_i_register_id_cur(p_ec_code IN Varchar2) IS
255 Select register_id
256 From JAI_CMN_RG_23AC_I_TRXS a, JAI_CMN_INVENTORY_ORGS b
257 Where ( a.posted_flag IS NULL OR a.posted_flag = 'N' ) --rchandan for bug#4428980
258 And ( a.master_flag IS NULL OR a.master_flag = 'N') --rchandan for bug#4428980
259 And a.organization_id = b.organization_id
260 And a.location_id = b.location_id
261 And b.master_org_flag ='N' --Changed by Nagaraj.s for Bug2708516
262 And b.ec_code = p_ec_code
263 Order by a.Register_Id;
264
265
266 Cursor serial_no_cur(p_organization_id IN Number, p_location_id IN Number, p_inventory_item_id IN Number,
267 p_fin_year IN Number, p_register_type Char) IS
268 Select nvl(MAX(slno),0) , nvl(MAX(slno),0) + 1
269 From JAI_CMN_RG_23AC_I_TRXS
270 Where organization_id = p_organization_id
271 And location_id = nvl(p_location_id,0)
272 And inventory_item_id = p_inventory_item_id
273 And fin_year = p_fin_year
274 And register_type = p_register_type;
275 --And nvl(master_flag,'N') = 'Y'; --Commented by Nagaraj.s for Bug2708516
276
277 v_previous_serial_no Number := 0;
278 v_serial_no Number := 0;
279
280 Cursor opening_balance_qty_cur(p_previous_serial_no IN NUMBER, p_organization_id IN Number, p_location_id IN Number,
281 p_inventory_item_id IN Number,p_fin_year IN Number, p_register_type Char) IS
282 Select nvl(opening_balance_qty,0), nvl(closing_balance_qty,0)
283 From JAI_CMN_RG_23AC_I_TRXS
284 Where slno = p_previous_serial_no
285 And organization_id = p_organization_id
286 And location_id = nvl(p_location_id,0)
287 And register_type = p_register_type
288 And fin_year = p_fin_year
289 And inventory_item_id = p_inventory_item_id;
290 --And nvl(master_flag,'N') = 'Y'; --Commented by Nagaraj.s for Bug2708516
291
292 v_opening_balance_qty Number := 0;
293 v_transaction_quantity Number := 0;
294 v_closing_balance_qty Number := 0;
295 v_debug_flag VARCHAR2 (1); -- char(1) :='Y'; --Added by Nagaraj.s for Bug2708516 (For File.Sql.35 by Brathod)
296 lv_remarks jai_cmn_rg_23ac_i_trxs.remarks%TYPE;
297
298
299 Begin
300 /*------------------------------------------------------------------------------------------
301 FILENAME: jai_cmn_rg_master_org_pkg.consolidate_rg23_part_i.sql
302 CHANGE HISTORY:
303
304 1. 2002/12/14 Nagaraj.s - For BUG#2708516 Version - 615.1
305 Ideally In Master Organization, no transactions should happen
306 and only Consolidation needs to happen.
307 But since Transactions are bound to happen and no check
308 exists across the Localization objects, hence the following changes
309 are done.
310 1. rg23_part_ii_register_id_cur has been incorporated with a new condition
311 that the Consolidation should happen only for Child Organizations.
312 2. serial_no_cur has been commented with a condition And nvl(master_flag,'N') = 'Y'
313 3. opening_balance_cur has been commented with a condition And nvl(master_flag,'N') = 'Y';
314
315
316 --------------------------------------------------------------------------------------------*/
317
318 v_debug_flag :='Y'; -- File.Sql.35 by Brathod
319
320 if v_debug_flag = 'Y' THEN
321 FND_FILE.PUT_LINE(FND_FILE.LOG, '*************************** START OF LOG FILE ****************************************');
322 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.0 The Organization Id is ' || p_organization_id);
323 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.1 The Location Id is ' || p_location_id);
324 end if;
325
326 OPEN master_ec_code_cur(p_organization_id, nvl(p_location_id,0));
327 FETCH master_ec_code_cur INTO v_ec_code;
328 CLOSE master_ec_code_cur;
329
330 if v_debug_flag = 'Y' THEN
331 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.2 The EC Code is ' || v_ec_code);
332 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.3 ************** Before the Main Loop ********************');
333 end if;
334
335 For rg23_reg_rec IN rg23_part_i_register_id_cur(v_ec_code)
336 Loop
337 OPEN rg23_part_i_cur(rg23_reg_rec.register_id);
338 FETCH rg23_part_i_cur INTO rg23_rec;
339 CLOSE rg23_part_i_cur;
340
341 if v_debug_flag = 'Y' THEN
342 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.4 The Register Id is ' || rg23_reg_rec.register_id);
343 end if;
344
345 /*Bug 9550254 - Start*/
346 /*
347 OPEN serial_no_cur(p_organization_id, nvl(p_location_id,0), rg23_rec.inventory_item_id, rg23_rec.fin_year,
348 rg23_rec.register_type);
349 FETCH serial_no_cur INTO v_previous_serial_no, v_serial_no;
350 CLOSE serial_no_cur;
351 */
352
353 v_opening_balance_qty := jai_om_rg_pkg.ja_in_rg23i_balance(p_organization_id,nvl(p_location_id,0),rg23_rec.inventory_item_id,
354 rg23_rec.fin_year,rg23_rec.register_type,v_previous_serial_no);
355 /*Bug 9550254 - End*/
356
357 if v_debug_flag = 'Y' THEN
358 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.5 The v_previous_serial_no is ' || v_previous_serial_no);
359 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.6 The v_serial_no is ' || v_serial_no);
360 end if;
361
362 IF nvl(v_previous_serial_no, 0) = 0 -- Bug 9550254 - Added NVL
363 THEN
364 -- v_opening_balance_qty := 0;
365 -- v_closing_balance_qty := rg23_rec.closing_balance_qty;
366 v_serial_no := 1; -- Added for Bug 9550254
367 v_transaction_quantity := nvl(rg23_rec.closing_balance_qty,0) - nvl(rg23_rec.opening_balance_qty,0);-- Added for Bug 9550254
368 v_closing_balance_qty := v_transaction_quantity + v_opening_balance_qty; -- Added for Bug 9550254
369 ELSE
370 v_serial_no := v_previous_serial_no + 1; -- Added for Bug 9550254
371 OPEN opening_balance_qty_cur(v_previous_serial_no, p_organization_id, nvl(p_location_id,0), rg23_rec.inventory_item_id,
372 rg23_rec.fin_year, rg23_rec.register_type);
373 FETCH opening_balance_qty_cur INTO v_opening_balance_qty, v_closing_balance_qty;
374 CLOSE opening_balance_qty_cur;
375
376 v_transaction_quantity := nvl(rg23_rec.closing_balance_qty,0) - nvl(rg23_rec.opening_balance_qty,0);
377
378 v_opening_balance_qty := v_closing_balance_qty;
379 v_closing_balance_qty := nvl(v_opening_balance_qty,0) + nvl(v_transaction_quantity,0);
380 END IF;
381
382 if v_debug_flag = 'Y' THEN
383 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.7 The v_opening_balance_qty is ' || v_opening_balance_qty);
384 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.8 The v_closing_balance_qty is ' || v_closing_balance_qty);
385 end if;
386 lv_remarks := substr(rg23_rec.remarks,1,100); /*Added by nprashar for bug # 13649626*/
387 lv_remarks := substr(lv_remarks||' - Master Org',1,120); /*Added by nprashar for bug # 13649626*/
388
389 INSERT INTO JAI_CMN_RG_23AC_I_TRXS
390 (register_id,
391 fin_year,
392 slno,
393 TRANSACTION_SOURCE_NUM,
394 inventory_item_id,
395 organization_id,
396 quantity_received,
397 RECEIPT_REF,
398 transaction_type,
399 receipt_date,
400 range_no,
401 division_no,
402 po_header_id,
403 po_header_date,
404 po_line_id,
405 po_line_location_id,
406 vendor_id,
407 vendor_site_id,
408 customer_id,
409 customer_site_id,
410 GOODS_ISSUE_ID_REF,
411 goods_issue_date,
412 goods_issue_quantity,
413 SALES_INVOICE_NO,
414 sales_invoice_quantity,
415 EXCISE_INVOICE_NO,
416 excise_invoice_date,
417 OTH_RECEIPT_ID_REF,
418 oth_receipt_quantity,
419 oth_receipt_date,
420 register_type,
421 identification_no,
422 identification_mark,
423 brand_name,
424 date_of_verification,
425 date_of_installation,
426 date_of_commission,
427 REGISTER_ID_PART_II,
428 additional_cvd, -- Bug 9382720 - Added Additional CVD as it is not getting consolidated in the Master Org
429 place_of_install,
430 remarks,
431 location_id,
432 primary_uom_code,
433 transaction_uom_code,
434 transaction_date,
435 basic_ed,
436 other_ed,
437 additional_ed,
438 opening_balance_qty,
439 closing_balance_qty,
440 charge_account_id,
441 posted_flag,
442 master_flag,
443 creation_date,
444 created_by,
445 last_update_login,
446 last_update_date,
447 last_updated_by)
448 VALUES
449 (
450 -rg23_rec.register_id,
451 rg23_rec.fin_year,
452 v_serial_no,
453 rg23_rec.TRANSACTION_SOURCE_NUM,
454 rg23_rec.inventory_item_id,
455 p_organization_id,
456 rg23_rec.quantity_received,
457 rg23_rec.receipt_ref,
458 rg23_rec.transaction_type,
459 rg23_rec.receipt_date,
460 rg23_rec.range_no,
461 rg23_rec.division_no,
462 rg23_rec.po_header_id,
463 rg23_rec.po_header_date,
464 rg23_rec.po_line_id,
465 rg23_rec.po_line_location_id,
466 rg23_rec.vendor_id,
467 rg23_rec.vendor_site_id,
468 rg23_rec.customer_id,
469 rg23_rec.customer_site_id,
470 rg23_rec.goods_issue_id_ref,
471 rg23_rec.goods_issue_date,
472 rg23_rec.goods_issue_quantity,
473 rg23_rec.sales_invoice_no,
474 rg23_rec.sales_invoice_quantity,
475 rg23_rec.excise_invoice_no,
476 rg23_rec.excise_invoice_date,
477 rg23_rec.OTH_RECEIPT_ID_REF,
478 rg23_rec.oth_receipt_quantity,
479 rg23_rec.oth_receipt_date,
480 rg23_rec.register_type,
481 rg23_rec.identification_no,
482 rg23_rec.identification_mark,
483 rg23_rec.brand_name,
484 rg23_rec.date_of_verification,
485 rg23_rec.date_of_installation,
486 rg23_rec.date_of_commission,
487 -rg23_rec.REGISTER_ID_PART_II,
488 rg23_rec.additional_cvd, -- Bug 9382720 - Added Additional CVD as it is not getting consolidated in the Master Org
489 rg23_rec.place_of_install,
490 lv_remarks, --rchandan for bug#4428980
491 nvl(p_location_id,0),
492 rg23_rec.primary_uom_code,
493 rg23_rec.transaction_uom_code,
494 rg23_rec.transaction_date,
495 rg23_rec.basic_ed,
496 rg23_rec.other_ed,
497 rg23_rec.additional_ed,
501 'N',
498 v_opening_balance_qty, ----Changed by Nagaraj.s for Bug2708516 Previously : rg23_rec.opening_balance_qty
499 v_closing_balance_qty, --Changed by Nagaraj.s for Bug2708516 Previously : rg23_rec.closing_balance_qty
500 rg23_rec.charge_account_id,
502 'Y',
503 sysdate,
504 rg23_rec.created_by,
505 rg23_rec.last_update_login,
506 rg23_rec.last_update_date,
507 rg23_rec.last_updated_by);
508
509 UPDATE JAI_CMN_RG_23AC_I_TRXS
510 SET posted_flag = 'Y',
511 master_flag = 'N'
512 WHERE register_id = rg23_rec.register_id;
513
514 End Loop;
515
516 if v_debug_flag = 'Y' THEN
517 FND_FILE.PUT_LINE(FND_FILE.LOG, '*************************** END OF LOG FILE ****************************************');
518 end if;
519
520 --Exception added by Nagaraj.s for Bug2708516
521 Exception
522 WHEN OTHERS THEN
523 RAISE_APPLICATION_ERROR(-20001,'This Concurrent Program has ended in an Error ' || SQLERRM);
524 End consolidate_rg23_part_i;
525
526
527 PROCEDURE consolidate_rg23_part_ii
528 (ERRBUF OUT NOCOPY VARCHAR2,
529 RETCODE OUT NOCOPY VARCHAR2,
530 p_organization_id IN NUMBER,
531 p_location_id IN NUMBER) as
532
533 Cursor master_ec_code_cur(p_organization_id IN NUMBER, p_location_id IN NUMBER) IS
534 Select ec_code
535 from JAI_CMN_INVENTORY_ORGS
536 Where organization_id = p_organization_id
537 And location_id = nvl(p_location_id,0);
538 v_ec_code Varchar2(50);
539
540 Cursor rg23_part_ii_cur(p_register_id IN Number) IS
541 Select *
542 From JAI_CMN_RG_23AC_II_TRXS a
543 Where a.register_id = p_register_id;
544 rg23_rec rg23_part_ii_cur%ROWTYPE;
545
546 Cursor rg23_part_ii_register_id_cur(p_ec_code IN Varchar2) IS
547 Select register_id
548 From JAI_CMN_RG_23AC_II_TRXS a, JAI_CMN_INVENTORY_ORGS b
549 Where ( a.posted_flag IS NULL OR a.posted_flag = 'N' ) --rchandan for bug#4428980
550 And ( a.master_flag IS NULL OR a.master_flag = 'N' ) --rchandan for bug#4428980
551 And a.organization_id = b.organization_id
552 And a.location_id = b.location_id
553 And b.master_org_flag ='N' --Changed by Nagaraj.s for Bug2636714
554 And b.ec_code = p_ec_code
555 Order by a.Register_Id;
556
557
558 Cursor serial_no_cur(p_organization_id IN Number, p_location_id IN Number,
559 p_fin_year IN Number, p_register_type Char) IS
560 Select nvl(MAX(slno),0) , nvl(MAX(slno),0) + 1
561 From JAI_CMN_RG_23AC_II_TRXS
562 Where organization_id = p_organization_id
563 And location_id = nvl(p_location_id,0)
564 And fin_year = p_fin_year
565 And register_type = p_register_type;
566 --And nvl(master_flag,'N') = 'Y';--Commented by Nagaraj.s for Bug2636714
567 v_previous_serial_no JAI_CMN_RG_23AC_II_TRXS.slno%TYPE := 0;
568 v_serial_no JAI_CMN_RG_23AC_II_TRXS.slno%TYPE := 0;
569
570 Cursor opening_balance_cur(p_previous_serial_no IN NUMBER, p_organization_id IN Number, p_location_id IN Number,
571 p_fin_year IN Number, p_register_type Char) IS
572 Select nvl(opening_balance,0), nvl(closing_balance,0)
573 From JAI_CMN_RG_23AC_II_TRXS
574 Where slno = p_previous_serial_no
575 And organization_id = p_organization_id
576 And location_id = nvl(p_location_id,0)
577 And register_type = p_register_type
578 And fin_year = p_fin_year ;
579 --And nvl(master_flag,'N') = 'Y';--Commented by Nagaraj.s for Bug2636714
580
581 --Added by Nagaraj.s for Bug2636714
582 Cursor c_fetch_fin_active_year(p_organization_id IN number) IS
583 SELECT FIN_YEAR FROM
584 JAI_CMN_FIN_YEARS
585 WHERE ORGANIZATION_ID=p_organization_id
586 and FIN_ACTIVE_FLAG='Y';
587
588 Cursor c_final_balance_rg23(p_organization_id IN NUMBER,p_location_id IN NUMBER,p_fin_year IN NUMBER,P_REGISTER_TYPE CHAR) IS
589 SELECT NVL(CLOSING_BALANCE,0)
590 FROM JAI_CMN_RG_23AC_II_TRXS
591 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
592 AND LOCATION_ID = P_LOCATION_ID
593 AND REGISTER_TYPE = P_REGISTER_TYPE
594 AND FIN_YEAR = P_FIN_YEAR
595 AND SLNO IN
596 (SELECT NVL(MAX(SLNO),0) FROM JAI_CMN_RG_23AC_II_TRXS
597 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
598 AND LOCATION_ID = P_LOCATION_ID
599 AND FIN_YEAR = P_FIN_YEAR
600 AND REGISTER_TYPE =P_REGISTER_TYPE);
601
602
603 v_opening_balance Number := 0;
604 v_transaction_balance Number := 0;
605 v_closing_balance Number := 0;
606 v_debug_flag varchar2(1); -- :='Y' --Added by Nagaraj.s for Bug2636714 File.Sql.35 by Brathod
607 v_fin_active_year NUMBER;
608 v_rg23a_final_balance NUMBER;
609 v_rg23c_final_balance NUMBER;
610 lv_buffer varchar2(1996) ;
611 lv_retcode varchar2(100);
612
613 Begin
614 /*------------------------------------------------------------------------------------------
615 FILENAME: jai_cmn_rg_master_org_pkg.consolidate_rg23_part_ii.sql
616 CHANGE HISTORY:
617
618 1. 2002/12/09 Nagaraj.s - For BUG#2636714 Version - 615.1
619 Ideally In Master Organization, no transactions should happen
620 and only Consolidation needs to happen.
621 But since Transactions are bound to happen and no check
622 exists across the Localization objects, hence the following changes
623 are done.
624 1. rg23_part_ii_register_id_cur has been incorporated with a new condition
625 that the Consolidation should happen only for Child Organizations.
629 5. A new Cusor is added to fetch the Closing Balance of RG23A and RG23C Registers
626 2. serial_no_cur has been commented with a condition And nvl(master_flag,'N') = 'Y'
627 3. opening_balance_cur has been commented with a condition And nvl(master_flag,'N') = 'Y';
628 4. A new cursor is added to fetch the Active Financial Year(c_fetch_fin_active_year).
630 (c_final_balance_rg23)
631 6. Updation of JAI_CMN_RG_BALANCES is done for Master Organization/Location.
632
633 2. 2005/01/07 Harshita.J - For Bug #410667 Version - 115.0
634 Master Org Consolidation has been implemented for CESS taxes and any other new
635 tax types that may be added in future.
636 Procedure jai_cmn_rg_master_org_pkg.insert_rg23_others is called for each record that has been consolidated
637 with the master to consolidate the taxes for CESS.
638 Base Bug #4106633
639
640 3. 2005/01/28 Harshita.J - For Bug #410667 Version - 115.1
641 Base Bug has been changed.
642 Base Bug #4146708. This bug creates all the database objects.
643
644
645 --------------------------------------------------------------------------------------------*/
646 v_debug_flag := 'Y'; -- File.Sql.35 by Brathod
647
648 if v_debug_flag = 'Y' THEN
649 FND_FILE.PUT_LINE(FND_FILE.LOG, '*************************** START OF LOG FILE ****************************************');
650 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.0 The Organization Id is ' || p_organization_id);
651 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.1 The Location Id is ' || p_location_id);
652 end if;
653 OPEN master_ec_code_cur(p_organization_id, nvl(p_location_id,0));
654 FETCH master_ec_code_cur INTO v_ec_code;
655 CLOSE master_ec_code_cur;
656
657 if v_debug_flag = 'Y' THEN
658 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.2 The EC Code is ' || v_ec_code);
659 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.3 ************** Before the Main Loop ********************');
660 end if;
661
662 For rg23_reg_rec IN rg23_part_ii_register_id_cur(v_ec_code)
663 Loop
664
665 OPEN rg23_part_ii_cur(rg23_reg_rec.register_id);
666 FETCH rg23_part_ii_cur INTO rg23_rec;
667 CLOSE rg23_part_ii_cur;
668
669 if v_debug_flag = 'Y' THEN
670 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.4 The Register Id is ' || rg23_reg_rec.register_id);
671 end if;
672
673 OPEN serial_no_cur(p_organization_id, nvl(p_location_id,0), rg23_rec.fin_year, rg23_rec.register_type);
674 FETCH serial_no_cur INTO v_previous_serial_no, v_serial_no;
675 CLOSE serial_no_cur;
676
677 if v_debug_flag = 'Y' THEN
678 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.5 The v_previous_serial_no is ' || v_previous_serial_no);
679 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.6 The v_serial_no is ' || v_serial_no);
680 end if;
681
682 IF v_previous_serial_no = 0
683 THEN
684 v_opening_balance := 0;
685 v_closing_balance := rg23_rec.closing_balance;
686 ELSE
687 OPEN opening_balance_cur(v_previous_serial_no, p_organization_id, nvl(p_location_id,0), rg23_rec.fin_year, rg23_rec.register_type);
688 FETCH opening_balance_cur INTO v_opening_balance, v_closing_balance;
689 CLOSE opening_balance_cur;
690
691 v_transaction_balance := nvl(rg23_rec.closing_balance,0) - nvl(rg23_rec.opening_balance,0);
692 v_opening_balance := v_closing_balance;
693 v_closing_balance := nvl(v_opening_balance,0) + nvl(v_transaction_balance,0);
694 END IF;
695 if v_debug_flag = 'Y' THEN
696 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.7 The v_opening_balance is ' || v_opening_balance);
697 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.8 The v_closing_balance is ' || v_closing_balance);
698 end if;
699
700 INSERT INTO JAI_CMN_RG_23AC_II_TRXS (register_id,
701 fin_year,
702 slno,
703 TRANSACTION_SOURCE_NUM,
704 inventory_item_id,
705 organization_id,
706 RECEIPT_REF,
707 receipt_date,
708 range_no,
709 division_no,
710 cr_basic_ed,
711 cr_additional_Ed,
712 cr_other_ed,
713 dr_basic_ed,
714 dr_additional_ed,
715 dr_other_ed,
716 excise_invoice_no,
717 excise_invoice_date,
718 register_type,
719 remarks,
720 vendor_id,
721 vendor_site_id,
722 customer_id,
723 customer_site_id,
724 location_id,
725 transaction_date,
726 opening_balance,
727 closing_balance,
728 charge_account_id,
729 register_id_part_i,
730 posted_flag,
731 master_flag,
732 creation_date,
733 created_by,
734 last_update_date,
735 last_updated_by,
736 last_update_login,
740 additional_cvd_amt,
737 other_tax_credit,
738 other_tax_debit,
739 -- Bug 9382720 - Added Additional CVD as it is not getting consolidated in the Master Org
741 cr_additional_cvd,
742 dr_additional_cvd
743 )
744 VALUES
745 (
746 -rg23_rec.register_id,
747 rg23_rec.fin_year,
748 v_serial_no,
749 rg23_rec.transaction_source_num,
750 rg23_rec.inventory_item_id,
751 p_organization_id,
752 rg23_rec.receipt_ref,
753 rg23_rec.receipt_date,
754 rg23_rec.range_no,
755 rg23_rec.division_no,
756 rg23_rec.cr_basic_ed,
757 rg23_rec.cr_additional_Ed,
758 rg23_rec.cr_other_ed,
759 rg23_rec.dr_basic_ed,
760 rg23_rec.dr_additional_ed,
761 rg23_rec.dr_other_ed,
762 rg23_rec.excise_invoice_no,
763 rg23_rec.excise_invoice_date,
764 rg23_rec.register_type,
765 rg23_rec.remarks,
766 rg23_rec.vendor_id,
767 rg23_rec.vendor_site_id,
768 rg23_rec.customer_id,
769 rg23_rec.customer_site_id,
770 nvl(p_location_id,0),
771 rg23_rec.transaction_date,
772 v_opening_balance,
773 v_closing_balance,
774 rg23_rec.charge_account_id,
775 -rg23_rec.register_id_part_i,
776 'N',
777 'Y',
778 rg23_rec.creation_date, --sysdate,/*changed for bug 6118417 (FP for bug 6112850)*/
779 rg23_rec.created_by,
780 rg23_rec.last_update_date,
781 rg23_rec.last_updated_by,
782 rg23_rec.last_update_login,
783 rg23_rec.other_tax_credit,
784 rg23_rec.other_tax_debit,
785 -- Bug 9382720 - Added Additional CVD as it is not getting consolidated in the Master Org
786 rg23_rec.additional_cvd_amt,
787 rg23_rec.cr_additional_cvd,
788 rg23_rec.dr_additional_cvd );
789 if v_debug_flag = 'Y' THEN
790 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.9 After Insert into JAI_CMN_RG_23AC_II_TRXS table');
791 FND_FILE.PUT_LINE(FND_FILE.LOG, '2.0 After the Register id is Processed' );
792 end if;
793
794 UPDATE JAI_CMN_RG_23AC_II_TRXS
795 SET posted_flag = 'Y',
796 master_flag = 'N'
797 WHERE register_id = rg23_rec.register_id;
798
799 -- added by hjujjuru for #Bug 4106667
800
801 FOR tax_types_rec IN
802 (select tax_type
803 from JAI_CMN_RG_OTHERS
804 where source_register_id = rg23_rec.register_id
805 and source_type = 1
806 )
807 LOOP
808 if v_debug_flag = 'Y' THEN
809 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.812 . Before calling other taxes ');
810 end if;
811 jai_cmn_rg_master_org_pkg.insert_rg23_others
812 (errbuf => lv_buffer,
813 retcode => lv_retcode,
814 p_previous_serial_no => v_previous_serial_no,
815 p_tax_type => tax_types_rec.tax_type,
816 p_register_id => rg23_rec.register_id ) ;
817
818 if v_debug_flag = 'Y' THEN
819 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.812 . After calling other taxes with status = ' || lv_retcode);
820 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.812 . After calling other taxes with error = ' || lv_buffer);
821 end if;
822
823
824
825
826 if nvl(lv_retcode,'0') <> '0' then /* if not success then get out the loop */
827 if v_debug_flag = 'Y' THEN
828 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.80 . Error is ' || lv_buffer || ' Halting the processing ... ');
829 end if;
830 ERRBUF := lv_buffer ;
831 goto errhandling_block; /* Halt the processing and no more records would get processed. */
832 end if;
833 END LOOP ;
834
835 -- ended by hjujjuru for #Bug 4106667
836
837 if v_debug_flag = 'Y' THEN
838 FND_FILE.PUT_LINE(FND_FILE.LOG, '*******************************************************************');
839 end if;
840
841 End Loop;
842
843 --Added by Nagaraj.s for Bug263674
844 OPEN c_fetch_fin_active_year(p_organization_id);
845 FETCH c_fetch_fin_active_year into v_fin_active_year;
846 CLOSE c_fetch_fin_active_year;
847
848 OPEN c_final_balance_rg23(p_organization_id,p_location_id,v_fin_active_year,'A');
849 FETCH c_final_balance_rg23 INTO v_rg23a_final_balance;
850 CLOSE c_final_balance_rg23;
851
852 OPEN c_final_balance_rg23(p_organization_id,p_location_id,v_fin_active_year,'C');
856 if v_debug_flag = 'Y' THEN
853 FETCH c_final_balance_rg23 INTO v_rg23c_final_balance;
854 CLOSE c_final_balance_rg23;
855
857 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.7 The v_rg23a_final_balance is ' || v_rg23a_final_balance);
858 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.8 The v_rg23c_final_balance is ' || v_rg23c_final_balance);
859 end if;
860 UPDATE JAI_CMN_RG_BALANCES
861 SET RG23A_BALANCE = v_rg23a_final_balance, RG23C_BALANCE=v_rg23c_final_balance
862 where organization_id=p_organization_id
863 and location_id =p_location_id;
864 --Ends here.....
865 if v_debug_flag = 'Y' THEN
866 FND_FILE.PUT_LINE(FND_FILE.LOG, '*************************** END OF LOG FILE ****************************************');
867 end if;
868 --Exception added by Nagaraj.s for Bug263674
869
870 retcode := '0';
871 ERRBUF := NULL;
872 return;
873 <<errhandling_block>>
874 Rollback; /* Rolling back because there was some problem */
875 RETCODE := '2';
876 if v_debug_flag = 'Y' THEN
877 FND_FILE.PUT_LINE(FND_FILE.LOG, 'in the err handling block ' || lv_buffer );
878 end if;
879 Exception
880 WHEN OTHERS THEN
881
882 RETCODE := '2' ;
883 RAISE_APPLICATION_ERROR(-20001,'This Concurrent Program has ended in an Error ' || SQLERRM);
884 if v_debug_flag = 'Y' THEN
885 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception !!!! ' || SQLERRM );
886 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception !!!! ' || SQLERRM );
887 end if;
888 End consolidate_rg23_part_ii;
889
890 PROCEDURE consolidate_pla
891 (ERRBUF OUT NOCOPY VARCHAR2,
892 RETCODE OUT NOCOPY VARCHAR2,
893 p_organization_id IN NUMBER,
894 p_location_id IN NUMBER) as
895
896 Cursor master_ec_code_cur(p_organization_id IN NUMBER, p_location_id IN NUMBER) IS
897 Select ec_code
898 from JAI_CMN_INVENTORY_ORGS
899 Where organization_id = p_organization_id
900 And location_id = nvl(p_location_id,0);
901 v_ec_code Varchar2(50);
902
903 Cursor pla_cur(p_register_id IN Number) IS
904 Select *
905 From JAI_CMN_RG_PLA_TRXS a
906 Where a.register_id = p_register_id;
907 pla_rec pla_cur%ROWTYPE;
908
909 Cursor pla_register_id_cur(p_ec_code IN Varchar2) IS
910 Select register_id
911 From JAI_CMN_RG_PLA_TRXS a, JAI_CMN_INVENTORY_ORGS b
912 Where ( a.posted_flag IS NULL OR a.posted_flag = 'N' ) --rchandan for bug#4428980
913 And ( a.master_flag IS NULL OR a.master_flag = 'N' ) --rchandan for bug#4428980
914 And a.organization_id = b.organization_id
915 And a.location_id = b.location_id
916 And b.master_org_flag ='N' --Changed by Nagaraj.s for Bug2708514
917 And b.ec_code = p_ec_code
918 Order by a.Register_Id;
919
920
921 Cursor serial_no_cur(p_organization_id IN Number, p_location_id IN Number,
922 p_fin_year IN Number /*, p_register_type Char */ ) IS
923 Select nvl(MAX(slno),0) , nvl(MAX(slno),0) + 1
924 From JAI_CMN_RG_PLA_TRXS
925 Where organization_id = p_organization_id
926 And location_id = nvl(p_location_id,0)
927 And fin_year = p_fin_year ;
928 -- And register_type = p_register_type
929 -- And nvl(master_flag,'N') = 'Y'; --Commented by Nagaraj.s for Bug2708514
930 v_previous_serial_no Number := 0;
931 v_serial_no Number := 0;
932
933 Cursor opening_balance_cur(p_previous_serial_no IN NUMBER, p_organization_id IN Number, p_location_id IN Number,
934 p_fin_year IN Number /*, p_register_type Char*/ ) IS
935 Select nvl(opening_balance,0), nvl(closing_balance,0)
936 From JAI_CMN_RG_PLA_TRXS
937 Where slno = p_previous_serial_no
938 And organization_id = p_organization_id
939 And location_id = nvl(p_location_id,0)
940 -- And register_type = p_register_type
941 And fin_year = p_fin_year;
942 --And nvl(master_flag,'N') = 'Y';--Commented by Nagaraj.s for Bug2708514
943
944 --Added by Nagaraj.s for Bug2708514
945 Cursor c_fetch_fin_active_year(p_organization_id IN number) IS
946 SELECT FIN_YEAR FROM
947 JAI_CMN_FIN_YEARS
948 WHERE ORGANIZATION_ID=p_organization_id
949 and FIN_ACTIVE_FLAG='Y';
950
951 Cursor c_final_balance_pla(p_organization_id IN NUMBER,p_location_id IN NUMBER,p_fin_year IN NUMBER) IS
952 SELECT NVL(CLOSING_BALANCE,0)
953 FROM JAI_CMN_RG_PLA_TRXS
954 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
955 AND LOCATION_ID = P_LOCATION_ID
956 AND FIN_YEAR = P_FIN_YEAR
957 AND SLNO IN
958 (SELECT NVL(MAX(SLNO),0) FROM JAI_CMN_RG_PLA_TRXS
959 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
960 AND LOCATION_ID = P_LOCATION_ID
961 AND FIN_YEAR = P_FIN_YEAR
962 );
963
964 v_opening_balance Number := 0;
965 v_transaction_balance Number := 0;
966 v_closing_balance Number := 0;
967 v_debug_flag varchar2(1); --:='Y'; --Added by Nagaraj.s for Bug2708514 File.Sql.35 by Brathod
968 v_pla_balance NUMBER :=0;
969 v_fin_active_year NUMBER;
970 v_record_count number :=0; --3335814
971 lv_buffer varchar2(1996) ; -- 4106667
972 lv_retcode varchar2(100); -- 4106667
973
974
975 Begin
976 /*------------------------------------------------------------------------------------------
977 FILENAME: ja_in_master_organizationpla_p.sql
978 CHANGE HISTORY:
979
980 1. 2002/12/14 Nagaraj.s - For Bug#2708514 Version - 615.1
981 Ideally In Master Organization, no transactions should happen
982 and only Consolidation needs to happen.
983 But since Transactions are bound to happen and no check
987 that the Consolidation should happen only for Child Organizations.
984 exists across the Localization objects, hence the following changes
985 are done.
986 1. pla_register_id_cur has been incorporated with a new condition
988 2. serial_no_cur has been commented with a condition And nvl(master_flag,'N') = 'Y'
989 3. opening_balance_cur has been commented with a condition And nvl(master_flag,'N') = 'Y';
990 4. A new cursor is added to fetch the Active Financial Year(c_fetch_fin_active_year).
991 5. A new Cusor is added to fetch the Closing Balance of RG23A and RG23C Registers
992 (c_final_balance_pla)
993 6. Updation of JAI_CMN_RG_BALANCES.pla_balance is done for Master Organization/Location.
994
995
996 2. 2003/12/30 Nagaraj.s for Bug#3335814. Version - 618.1
997
998 In scenarios where Master Org records is not present and not processed, and if
999 no data existed for Organization, Location, Present Financial Year, then the
1000 Updation of JAI_CMN_RG_BALANCES was going wrong.
1001
1002 This has been rectified by ensuring that the updation of JAI_CMN_RG_BALANCES
1003 happens only if child records are present and processed. Hence now a record count
1004 is kept of the records processed in the loop and if the record count > 0 only
1005 then the updation of JAI_CMN_RG_BALANCES would happen.
1006
1007 3. 2005/01/07 Harshita.J - For Bug #410667 Version - 115.0
1008 Master Org Consolidation has been implemented for CESS taxes and any other new
1009 tax types that may be added in future.
1010 Procedure jai_cmn_rg_master_org_pkg.insert_rg23_others is called for each record that has been consolidated
1011 with the master to consolidate the taxes for CESS.
1012 Base Bug #4106633
1013
1014 3. 2005/01/28 Harshita.J - For Bug #410667 Version - 115.1
1015 Base Bug has been changed.
1016 Base Bug #4146708. This bug creates all the database objects.
1017
1018 --------------------------------------------------------------------------------------------*/
1019
1020 v_debug_flag :='Y'; -- File.Sql.35 by Brathod
1021 if v_debug_flag = 'Y' THEN
1022 FND_FILE.PUT_LINE(FND_FILE.LOG, '*************************** START OF LOG FILE ****************************************');
1023 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.0 The Organization Id is ' || p_organization_id);
1024 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.1 The Location Id is ' || p_location_id);
1025 end if;
1026
1027 OPEN master_ec_code_cur(p_organization_id, nvl(p_location_id,0));
1028 FETCH master_ec_code_cur INTO v_ec_code;
1029 CLOSE master_ec_code_cur;
1030
1031 if v_debug_flag = 'Y' THEN
1032 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.2 The EC Code is ' || v_ec_code);
1033 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.3 ************** Before the Main Loop ********************');
1034 end if;
1035
1036 For pla_reg_rec IN pla_register_id_cur(v_ec_code)
1037 Loop
1038 OPEN pla_cur(pla_reg_rec.register_id);
1039 FETCH pla_cur INTO pla_rec;
1040 CLOSE pla_cur;
1041
1042 if v_debug_flag = 'Y' THEN
1043 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.4 The Register Id is ' || pla_reg_rec.register_id);
1044 end if;
1045
1046 OPEN serial_no_cur(p_organization_id, nvl(p_location_id,0), pla_rec.fin_year /*, pla_rec.register_type*/ );
1047 FETCH serial_no_cur INTO v_previous_serial_no, v_serial_no;
1048 CLOSE serial_no_cur;
1049
1050 if v_debug_flag = 'Y' THEN
1051 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.5 The v_previous_serial_no is ' || v_previous_serial_no);
1052 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.6 The v_serial_no is ' || v_serial_no);
1053 end if;
1054
1055 IF v_previous_serial_no = 0
1056 THEN
1057 v_opening_balance := 0;
1058 v_closing_balance := pla_rec.closing_balance;
1059 ELSE
1060 OPEN opening_balance_cur(v_previous_serial_no, p_organization_id, nvl(p_location_id,0), pla_rec.fin_year /*, rg23_rec.register_type */ );
1061 FETCH opening_balance_cur INTO v_opening_balance, v_closing_balance;
1062 CLOSE opening_balance_cur;
1063
1064 v_transaction_balance := nvl(pla_rec.closing_balance,0) - nvl(pla_rec.opening_balance,0);
1065
1066 v_opening_balance := v_closing_balance;
1067 v_closing_balance := nvl(v_opening_balance,0) + nvl(v_transaction_balance,0);
1068 END IF;
1069 if v_debug_flag = 'Y' THEN
1070 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.7 The v_opening_balance is ' || v_opening_balance);
1071 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.8 The v_closing_balance is ' || v_closing_balance);
1072 end if;
1073 INSERT INTO JAI_CMN_RG_PLA_TRXS (register_id,
1074 fin_year,
1075 slno,
1076 tr6_challan_no,
1077 tr6_challan_date,
1078 cr_basic_ed,
1079 cr_additional_ed,
1080 cr_other_ed,
1081 TRANSACTION_SOURCE_NUM,
1082 ref_document_id,
1083 ref_document_date,
1084 DR_INVOICE_NO,
1085 dr_invoice_date,
1086 dr_basic_ed,
1087 dr_additional_ed,
1088 dr_other_ed,
1089 organization_id,
1090 location_id,
1091 bank_branch_id,
1092 entry_date,
1093 inventory_item_id,
1094 vendor_cust_flag,
1095 vendor_id,
1096 vendor_site_id,
1097 range_no,
1101 transaction_date,
1098 division_no,
1099 excise_invoice_no,
1100 remarks,
1102 opening_balance,
1103 closing_balance,
1104 charge_account_id,
1105 posted_flag,
1106 master_flag,
1107 creation_date,
1108 created_by,
1109 last_update_date,
1110 last_updated_by,
1111 last_update_login,
1112 other_tax_credit,
1113 other_tax_debit)
1114 VALUES( -1 * pla_rec.register_id,
1115 pla_rec.fin_year,
1116 v_serial_no,
1117 pla_rec.tr6_challan_no,
1118 pla_rec.tr6_challan_date,
1119 pla_rec.cr_basic_ed,
1120 pla_rec.cr_additional_ed,
1121 pla_rec.cr_other_ed,
1122 pla_rec.TRANSACTION_SOURCE_NUM,
1123 pla_rec.ref_document_id,
1124 pla_rec.ref_document_date,
1125 pla_rec.DR_INVOICE_NO,
1126 pla_rec.dr_invoice_date,
1127 pla_rec.dr_basic_ed,
1128 pla_rec.dr_additional_ed,
1129 pla_rec.dr_other_ed,
1130 p_organization_id,
1131 nvl(p_location_id,0),
1132 pla_rec.bank_branch_id,
1133 pla_rec.entry_date,
1134 pla_rec.inventory_item_id,
1135 pla_rec.vendor_cust_flag,
1136 pla_rec.vendor_id,
1137 pla_rec.vendor_site_id,
1138 pla_rec.range_no,
1139 pla_rec.division_no,
1140 pla_rec.excise_invoice_no,
1141 pla_rec.remarks,
1142 pla_rec.transaction_date,
1143 v_opening_balance,
1144 v_closing_balance,
1145 pla_rec.charge_account_id,
1146 'N', --posted_flag,
1147 'Y', --master_flag,
1148 pla_rec.creation_date,
1149 pla_rec.created_by,
1150 pla_rec.last_update_date,
1151 pla_rec.last_updated_by,
1152 pla_rec.last_update_login,
1153 pla_rec.other_tax_credit,
1154 pla_rec.other_tax_debit);
1155
1156 UPDATE JAI_CMN_RG_PLA_TRXS
1157 SET posted_flag = 'Y',
1158 master_flag = 'N'
1159 WHERE register_id = pla_rec.register_id;
1160
1161 v_record_count := v_record_count + 1; --3335814
1162
1163 -- added by hjujjuru for #Bug 4106667
1164
1165
1166 FOR tax_types_rec IN
1167 ( select tax_type
1168 from JAI_CMN_RG_OTHERS
1169 where
1170 source_register_id = pla_rec.register_id
1171 and source_type = 2
1172 )
1173
1174 LOOP
1175
1176
1177 jai_cmn_rg_master_org_pkg.insert_pla_others
1178 ( errbuf => lv_buffer,
1179 retcode => lv_retcode,
1180 p_previous_serial_no => v_previous_serial_no,
1181 p_tax_type => tax_types_rec.tax_type,
1182 p_register_id => pla_rec.register_id
1183 );
1184
1185 if nvl(lv_retcode,'0') <> '0' then /* if not success then get out the loop */
1186 if v_debug_flag = 'Y' THEN
1187 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.80 . Error is ' || lv_buffer || ' Halting the processing ... ');
1188 end if;
1189 ERRBUF := lv_buffer ;
1190 goto errhandling_block; /* Halt the processing and no more records would get processed. */
1191 end if;
1192
1193 END LOOP ;
1194
1195 -- ended by hjujjuru for #Bug 4106667
1196
1197 End Loop;
1198
1199 if v_debug_flag = 'Y' THEN
1200 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.81 The v_record_count is ' || v_record_count);
1201 end if;
1202
1203 if v_record_count >0 then --3335814
1204 --Added by Nagaraj.s for Bug2708514
1205 --This Cursor Fetches the Active Financial Year
1206 OPEN c_fetch_fin_active_year(p_organization_id);
1207 FETCH c_fetch_fin_active_year into v_fin_active_year;
1208 CLOSE c_fetch_fin_active_year;
1209
1210 --This Cursor Fetches the Closing Balance of the Org/Location
1211 OPEN c_final_balance_pla(p_organization_id,p_location_id,v_fin_active_year);
1212 FETCH c_final_balance_pla INTO v_pla_balance;
1213 CLOSE c_final_balance_pla;
1214
1215 if v_debug_flag = 'Y' THEN
1216 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.82 The v_fin_active_year is ' || v_fin_active_year);
1217 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.83 The pla balance that is updated in JAI_CMN_RG_BALANCES is ' || v_pla_balance);
1218 end if;
1219
1220 --This is for Updation of RG Balances table
1221 UPDATE JAI_CMN_RG_BALANCES
1222 SET PLA_BALANCE = v_pla_balance
1223 where organization_id=p_organization_id
1224 and location_id =p_location_id;
1225 end if; --end if for v_record_count.
1226
1227 --Ends here.....
1228 if v_debug_flag = 'Y' THEN
1229 FND_FILE.PUT_LINE(FND_FILE.LOG, '*************************** END OF LOG FILE ****************************************');
1230 end if;
1231
1232 retcode := '0';
1236 Rollback; /* Rolling back because there was some problem */
1233 ERRBUF := NULL;
1234 return;
1235 <<errhandling_block>>
1237 RETCODE := '2';
1238 --Exception added by Nagaraj.s for Bug2708514
1239 Exception
1240 WHEN OTHERS THEN
1241 retcode := '2';
1242 ERRBUF := substr(sqlerrm,1,950);
1243 RAISE_APPLICATION_ERROR(-20001,'This Concurrent Program has ended in an Error ' || SQLERRM);
1244 End consolidate_pla;
1245
1246
1247 PROCEDURE consolidate_rg_i
1248 (errbuf OUT NOCOPY VARCHAR2,
1249 retcode OUT NOCOPY VARCHAR2,
1250 p_organization_id IN NUMBER,
1251 p_location_id IN NUMBER )
1252 AS
1253
1254 CURSOR master_ec_code_cur(p_organization_id IN NUMBER, p_location_id IN NUMBER) IS
1255 SELECT ec_code
1256 FROM JAI_CMN_INVENTORY_ORGS
1257 WHERE organization_id = p_organization_id
1258 AND location_id = nvl(p_location_id,0);
1259
1260 CURSOR rgi_register_id_cur(p_ec_code IN VARCHAR2) IS
1261 SELECT register_id
1262 FROM JAI_CMN_RG_I_TRXS a, JAI_CMN_INVENTORY_ORGS b
1263 WHERE ( a.posted_flag IS NULL OR a.posted_flag = 'N' ) --rchandan for bug#4428980
1264 AND ( a.master_flag IS NULL OR a.master_flag = 'N' ) --rchandan for bug#4428980
1265 AND a.organization_id = b.organization_id
1266 AND a.location_id = b.location_id
1267 And b.master_org_flag ='N' --Changed by Nagaraj.s for Bug2708518
1268 AND b.ec_code = p_ec_code
1269 ORDER BY a.inventory_item_id, a.Register_Id;
1270
1271 CURSOR rgi_cur(p_register_id IN NUMBER) IS
1272 SELECT *
1273 FROM JAI_CMN_RG_I_TRXS a
1274 WHERE a.register_id = p_register_id;
1275
1276 CURSOR balance_cur(p_serial_no IN NUMBER, p_organization_id IN Number, p_location_id IN Number,
1277 p_inventory_item_id IN Number, p_fin_year IN Number) IS
1278 SELECT NVL(balance_packed, 0), NVL(balance_loose, 0)
1279 , nvl(manufactured_qty, 0) -- Vijay Shankar for Bug# 3165687
1280 FROM JAI_CMN_RG_I_TRXS
1281 WHERE slno = p_serial_no
1282 AND organization_id = p_organization_id
1283 AND location_id = nvl(p_location_id,0)
1284 AND fin_year = p_fin_year
1285 AND inventory_item_id = p_inventory_item_id;
1286
1287 CURSOR c_max_fin_year(p_organization_id IN NUMBER, p_location_id IN NUMBER, p_inventory_item_id NUMBER) IS
1288 SELECT max(fin_year)
1289 FROM JAI_CMN_RG_I_TRXS
1290 WHERE organization_id = p_organization_id
1291 AND location_id = nvl(p_location_id,0)
1292 AND inventory_item_id = p_inventory_item_id;
1293
1294 CURSOR serial_no_cur(p_organization_id IN NUMBER, p_location_id IN NUMBER, p_inventory_item_id NUMBER, p_fin_year NUMBER) IS
1295 SELECT nvl(max(slno), 0)
1296 FROM JAI_CMN_RG_I_TRXS
1297 WHERE organization_id = p_organization_id
1298 AND location_id = nvl(p_location_id,0)
1299 AND inventory_item_id = p_inventory_item_id
1300 AND fin_year = p_fin_year;
1301
1302
1303 rgi_rec rgi_cur%ROWTYPE;
1304 v_ec_code VARCHAR2(50);
1305
1306 -- Vijay Shankar for Bug# 3587423
1307 v_prev_item_id NUMBER := 0;
1308 v_fin_year NUMBER;
1309
1310 v_serial_no NUMBER := 0;
1311 v_balance_packed NUMBER := 0;
1312 v_balance_loose NUMBER := 0;
1313 v_debug_flag VARCHAR2 (1) ; -- CHAR(1) := 'Y'; --Added by Nagaraj.s for Bug2708518 File.Sql.35 by Brathod
1314
1315 -- Vijay Shankar for BUG#3165687
1316 v_manu_qty NUMBER := 0;
1317 v_statement_id VARCHAR2(3);
1318 v_regid_being_processed NUMBER;
1319
1320
1321 BEGIN
1322 /*------------------------------------------------------------------------------------------
1323 CHANGE HISTORY for FILENAME: jai_cmn_rg_master_org_pkg.consolidate_rg23_part_i.sql
1324
1325 SlNo yyyy/mm/dd Details of Changes
1326 ------------------------------------------------------------------------------------------
1327 1. 2002/12/14 Nagaraj.s - For BUG#2708518 Version - 615.1
1328 Ideally In Master Organization, no transactions should happen
1329 and only Consolidation needs to happen.
1330 But since Transactions are bound to happen and no check
1331 exists across the Localization objects, hence the following changes
1332 are done.
1333 1. rgi_register_id_cur has been incorporated with a new condition
1334 that the Consolidation should happen only for Child Organizations.
1335 2. serial_no_cur has been commented with a condition And nvl(master_flag,'N') = 'Y'
1336 3. balance_cur has been commented with a condition And nvl(master_flag,'N') = 'Y';
1337 4. Fetching of Balances for Packed and Loose Quantities were commented.
1338 Previously which is necessary to see that the Previous Balance is picked for
1339 deciding on the Balance_Loose and Balance_Packed Columns of the Database.
1340
1341 2. 2004/02/17 Vijay Shankar for BUG#3165687, FileVersion - 619.1
1342 Balance Loose and Balance packed are calculated wrongly which is made correct.
1343 Also Manufactured quantity is not getting populated correctly which is also made correct with this fix
1344
1345 3. 2004/04/27 Vijay Shankar for BUG#3587423, FileVersion - 619.2
1346 Modified the procedure to calculate balances by carrying forward the previous year balances. Modified CURSOR
1347 rgi_register_id_cur to ORDER BY inventory_item_id, register_id instead of register_id and previous balances are
1351 Please refer to previous version for the changes made in this bug
1348 fetched only for first record of consolidated inventory item. Removed the execution of CURSOR serial_no_cur
1349 for every record posted into Master Org, by using previous record balances.
1350
1352 --------------------------------------------------------------------------------------------*/
1353
1354 v_debug_flag :='Y'; -- File.Sql.35 by Brathod
1355
1356 if v_debug_flag = 'Y' THEN
1357 FND_FILE.PUT_LINE(FND_FILE.LOG, '********* START OF LOG FILE - '||to_char(SYSDATE,'dd-mon-yyyy hh24:mi:ss')||' *********');
1358 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.0 organization->' || p_organization_id||', Location->' || p_location_id);
1359 end if;
1360
1361 v_statement_id := '1';
1362 OPEN master_ec_code_cur(p_organization_id, nvl(p_location_id,0));
1363 FETCH master_ec_code_cur INTO v_ec_code;
1364 CLOSE master_ec_code_cur;
1365
1366 if v_debug_flag = 'Y' THEN
1367 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.2 Before the Main Loop, EC Code->' || v_ec_code);
1368 end if;
1369
1370 FOR rgi_reg_rec IN rgi_register_id_cur(v_ec_code) LOOP
1371
1372 v_regid_being_processed := rgi_reg_rec.register_id;
1373
1374 v_statement_id := '2';
1375 if v_debug_flag = 'Y' THEN
1376 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.4 The Register Id is ' || rgi_reg_rec.register_id);
1377 end if;
1378
1379 v_statement_id := '2a';
1380
1381 OPEN rgi_cur(rgi_reg_rec.register_id);
1382 FETCH rgi_cur INTO rgi_rec;
1383 CLOSE rgi_cur;
1384
1385 v_statement_id := '3';
1386 --Start, Vijay Shankar for Bug# 3587423
1387 IF v_prev_item_id <> rgi_rec.inventory_item_id THEN
1388 -- This code is executed for the first record of each inventory_item_id. For the remaining records, these balances will be used
1389 -- removed fin_year from filtering condition, so that previous year balances are carried forward to next financial year
1390 v_statement_id := '3a';
1391 /*Bug 9550254 - Start*/
1392 /*
1393 v_fin_year := null;
1394 OPEN c_max_fin_year(p_organization_id, nvl(p_location_id,0), rgi_rec.inventory_item_id);
1395 FETCH c_max_fin_year INTO v_fin_year;
1396 CLOSE c_max_fin_year;
1397 */
1398
1399 v_fin_year := rgi_rec.fin_year;
1400
1401 v_statement_id := '3b';
1402 /*
1403 OPEN serial_no_cur(p_organization_id, nvl(p_location_id,0), rgi_rec.inventory_item_id, v_fin_year);
1404 FETCH serial_no_cur INTO v_serial_no;
1405 CLOSE serial_no_cur;
1406 */
1407
1408 v_balance_loose := jai_om_rg_pkg.ja_in_rgi_balance(p_organization_id,nvl(p_location_id,0),rgi_rec.inventory_item_id,v_fin_year,
1409 v_serial_no,v_balance_packed);
1410
1411 IF nvl(v_serial_no, 0) = 0 THEN --'NVL' added for Bug 9550254
1412 -- v_balance_packed := 0;
1413 -- v_balance_loose := 0;
1414 v_manu_qty := 0;
1415 v_serial_no := 0;
1416 v_fin_year := rgi_rec.fin_year;
1417 ELSE
1418 v_statement_id := '7';
1419 v_statement_id := '3d';
1420 --OPEN balance_cur(v_serial_no, p_organization_id, nvl(p_location_id,0), v_fin_year, rgi_rec.inventory_item_id);
1421 --commented the above and added the below by Ramananda for Bug# 4589502
1422 OPEN balance_cur(v_serial_no, p_organization_id, nvl(p_location_id,0), rgi_rec.inventory_item_id, v_fin_year);
1423 FETCH balance_cur INTO v_balance_packed, v_balance_loose, v_manu_qty;
1424 CLOSE balance_cur;
1425 END IF;
1426 /*Bug 9550254 - End*/
1427
1428 v_prev_item_id := rgi_rec.inventory_item_id;
1429
1430 IF v_debug_flag = 'Y' THEN
1431 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.5 ChildItem->' || rgi_rec.inventory_item_id
1432 ||', v_fin_year->' || v_fin_year
1433 ||', v_serial_no->' || v_serial_no
1434 ||', bal_pac->' || v_balance_packed||', bal_loo->' || v_balance_loose||', man_qty->' || v_manu_qty);
1435 END IF;
1436
1437 END IF;
1438 /*Commented for Bug 9550254*/
1439 /*
1440 IF v_fin_year <> rgi_rec.fin_year THEN
1441 v_statement_id := '7.1';
1442 v_serial_no := 1;
1443 ELSE
1444 v_statement_id := '6';
1445 v_serial_no := v_serial_no + 1;
1446 END IF;
1447 v_fin_year := rgi_rec.fin_year;
1448 */
1449 --End, Vijay Shankar for Bug# 3587423
1450
1451 v_statement_id := '7a';
1452 v_manu_qty := nvl(rgi_rec.manufactured_packed_qty,0) + nvl(rgi_rec.manufactured_loose_qty,0);
1453
1454 v_balance_packed := nvl(v_balance_packed, 0)
1455 + nvl(rgi_rec.manufactured_packed_qty,0);
1456
1457 v_statement_id := '7b';
1458 v_balance_loose := nvl(v_balance_loose, 0)
1459 - nvl(rgi_rec.for_home_use_pay_ed_qty,0)
1460 - nvl(rgi_rec.for_export_pay_ed_qty,0)
1461 - nvl(rgi_rec.for_export_n_pay_ed_qty,0)
1462 - nvl(rgi_rec.to_other_factory_n_pay_ed_qty,0)
1463 - nvl(rgi_rec.other_purpose_n_pay_ed_qty,0)
1464 - nvl(rgi_rec.other_purpose_pay_ed_qty,0)
1465 + nvl(rgi_rec.manufactured_loose_qty,0);
1466
1467 v_serial_no := v_serial_no + 1; /*Bug 9550254*/
1468
1469 IF v_debug_flag = 'Y' THEN
1470 FND_FILE.PUT_LINE(FND_FILE.LOG, '1.51 v_fin_year->' || v_fin_year||', bal_loo->' || v_balance_loose
1471 ||', serial_no->' || v_serial_no);
1472 END IF;
1473
1474 v_statement_id := '8';
1475 INSERT INTO JAI_CMN_RG_I_TRXS ( register_id,
1479 TRANSACTION_SOURCE_NUM,
1476 register_id_part_ii,
1477 fin_year,
1478 slno,
1480 organization_id,
1481 location_id,
1482 transaction_date,
1483 inventory_item_id,
1484 transaction_type,
1485 REF_DOC_NO,
1486 manufactured_qty,
1487 manufactured_packed_qty,
1488 manufactured_loose_qty,
1492 for_export_pay_ed_val,
1489 for_home_use_pay_ed_qty,
1490 for_home_use_pay_ed_val,
1491 for_export_pay_ed_qty,
1493 for_export_n_pay_ed_qty,
1494 for_export_n_pay_ed_val,
1495 other_purpose,
1496 to_other_factory_n_pay_ed_qty,
1497 to_other_factory_n_pay_ed_val,
1498 other_purpose_n_pay_ed_qty,
1499 other_purpose_n_pay_ed_val,
1500 other_purpose_pay_ed_qty,
1501 other_purpose_pay_ed_val,
1502 primary_uom_code,
1503 transaction_uom_code,
1504 balance_packed,
1505 balance_loose,
1506 issue_type,
1507 excise_duty_amount,
1508 excise_invoice_number,
1509 excise_invoice_date,
1510 payment_register,
1511 charge_account_id,
1512 range_no,
1513 division_no,
1514 remarks,
1515 basic_ed,
1516 additional_ed,
1517 other_ed,
1518 excise_duty_rate,
1519 vendor_id,
1520 vendor_site_id,
1521 customer_id,
1522 customer_site_id,
1523 creation_date,
1524 created_by,
1525 last_update_date,
1526 last_updated_by,
1527 last_update_login,
1528 posted_flag,
1529 master_flag )
1530 VALUES( -1 * rgi_rec.register_id,
1531 -1 * rgi_rec.register_id_part_ii,
1532 rgi_rec.fin_year,
1533 v_serial_no,
1534 rgi_rec.transaction_source_num,
1535 p_organization_id,
1536 nvl(p_location_id,0),
1537 rgi_rec.transaction_date,
1538 rgi_rec.inventory_item_id,
1539 rgi_rec.transaction_type,
1540 rgi_rec.REF_DOC_NO,
1541 -- Modified by Vijay Shankar for Bug# 3165687
1542 v_manu_qty, -- rgi_rec.manufactured_qty,
1543 rgi_rec.manufactured_packed_qty,
1544 rgi_rec.manufactured_loose_qty,
1545 rgi_rec.for_home_use_pay_ed_qty,
1546 rgi_rec.for_home_use_pay_ed_val,
1547 rgi_rec.for_export_pay_ed_qty,
1548 rgi_rec.for_export_pay_ed_val,
1549 rgi_rec.for_export_n_pay_ed_qty,
1550 rgi_rec.for_export_n_pay_ed_val,
1551 rgi_rec.other_purpose,
1552 rgi_rec.to_other_factory_n_pay_ed_qty,
1553 rgi_rec.to_other_factory_n_pay_ed_val,
1554 rgi_rec.other_purpose_n_pay_ed_qty,
1555 rgi_rec.other_purpose_n_pay_ed_val,
1556 rgi_rec.other_purpose_pay_ed_qty,
1557 rgi_rec.other_purpose_pay_ed_val,
1558 rgi_rec.primary_uom_code,
1559 rgi_rec.transaction_uom_code,
1560 nvl(v_balance_packed,0),--nvl(rgi_rec.balance_packed,0),--By Nagaraj.s for Bug2708518
1561 nvl(v_balance_loose,0), --nvl(rgi_rec.balance_loose,0),--By Nagaraj.s for Bug2708518
1562 rgi_rec.issue_type,
1563 rgi_rec.excise_duty_amount,
1564 rgi_rec.excise_invoice_number,
1568 rgi_rec.range_no,
1565 rgi_rec.excise_invoice_date,
1566 rgi_rec.payment_register,
1567 rgi_rec.charge_account_id,
1569 rgi_rec.division_no,
1570 rgi_rec.remarks,
1571 rgi_rec.basic_ed,
1572 rgi_rec.additional_ed,
1573 rgi_rec.other_ed,
1574 rgi_rec.excise_duty_rate,
1575 rgi_rec.vendor_id,
1576 rgi_rec.vendor_site_id,
1577 rgi_rec.customer_id,
1578 rgi_rec.customer_site_id,
1579 rgi_rec.creation_date,
1580 rgi_rec.created_by,
1581 rgi_rec.last_update_date,
1582 rgi_rec.last_updated_by,
1583 rgi_rec.last_update_login,
1584 'N',
1585 'Y' );
1586
1587 v_statement_id := '9';
1588 UPDATE JAI_CMN_RG_I_TRXS
1589 SET posted_flag = 'Y',
1590 master_flag = 'N'
1591 WHERE register_id = rgi_rec.register_id;
1592
1593 END LOOP;
1594
1595 if v_debug_flag = 'Y' THEN
1596 FND_FILE.PUT_LINE(FND_FILE.LOG, '*************************** END OF LOG FILE ****************************************');
1597 end if;
1598
1599 --Exception added by Nagaraj.s for Bug2708518
1600 Exception
1601 WHEN OTHERS THEN
1602
1603 FND_FILE.PUT_LINE(FND_FILE.LOG, '######### Error at: v_regid_being_processed->'||v_regid_being_processed
1604 ||', statement_id->'||v_statement_id
1605 ||', SQLERRM->'||SQLERRM
1606 );
1607
1608 RAISE_APPLICATION_ERROR(-20001,'This Concurrent Program has ended in an Error ' || SQLERRM);
1609
1610 END consolidate_rg_i;
1611
1612 END jai_cmn_rg_master_org_pkg ;
[Home] [Help]