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