DBA Data[Home] [Help]

PACKAGE BODY: APPS.JAI_CMN_RG_PLA_TRXS_PKG

Source


1 PACKAGE BODY jai_cmn_rg_pla_trxs_pkg AS
2 /* $Header: jai_cmn_rg_pla.plb 120.4 2007/08/07 07:46:46 vkaranam ship $ */
3 
4 /* --------------------------------------------------------------------------------------
5 Filename:
6 
7 Change History:
8 
9 Date         Bug         Remarks
10 ---------    ----------  -------------------------------------------------------------
11 08-Jun-2005  Version 116.2 jai_cmn_rg_pla -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
12     as required for CASE COMPLAINCE.
13 
14 14/07/2005   4485801 Brathod, File Version 117.1
15              Issue: Inventory Convergence Uptake for R12 Initiative
16 
17 28/12/2005   4892111 Hjujjuru, File Version 120.2
18              Issue : In the insert into the  JAI_CMN_RG_PLA_TRXS , register_id
19              was inserted as JAI_CMN_RG_PLA_TRXS_S1.nextval. This is errorring
20              out.
21 
22              Fix : Created a cursor to retrieve the register_id value from the
23              sequence and inserted the same into the JAI_CMN_RG_PLA_TRXS table
24 
25 24/04/2007   Vijay Shankar for Bug# 6012570(5876390), Version:120.3 (115.7)
26                     FP: Modified the code in get_trxn_type_and_id to return a transaction_id for Projects Billing
27 
28 07/05/2007   Vkaranam for Bug# 6030615, Version:120.4
29              Forward Porting the changes done in 115 bug 2942973(Interorg Transfer)
30                     FP: Modified the code in get_trxn_type_and_id to return a transaction_id for Interorg Transfer.
31 
32 */
33 
34 PROCEDURE insert_row(
35 
36   P_REGISTER_ID OUT NOCOPY JAI_CMN_RG_PLA_TRXS.register_id%TYPE,
37   P_TR6_CHALLAN_NO                IN  JAI_CMN_RG_PLA_TRXS.tr6_challan_no%TYPE,
38   P_TR6_CHALLAN_DATE              IN  JAI_CMN_RG_PLA_TRXS.tr6_challan_date%TYPE,
39   P_CR_BASIC_ED                   IN  JAI_CMN_RG_PLA_TRXS.cr_basic_ed%TYPE,
40   P_CR_ADDITIONAL_ED              IN  JAI_CMN_RG_PLA_TRXS.cr_additional_ed%TYPE,
41   P_CR_OTHER_ED                   IN  JAI_CMN_RG_PLA_TRXS.cr_other_ed%TYPE,
42   P_REF_DOCUMENT_ID               IN  JAI_CMN_RG_PLA_TRXS.ref_document_id%TYPE,
43   P_REF_DOCUMENT_DATE             IN  JAI_CMN_RG_PLA_TRXS.ref_document_date%TYPE,
44   P_DR_INVOICE_ID                 IN  JAI_CMN_RG_PLA_TRXS.DR_INVOICE_NO%TYPE,
45   P_DR_INVOICE_DATE               IN  JAI_CMN_RG_PLA_TRXS.dr_invoice_date%TYPE,
46   P_DR_BASIC_ED                   IN  JAI_CMN_RG_PLA_TRXS.dr_basic_ed%TYPE,
47   P_DR_ADDITIONAL_ED              IN  JAI_CMN_RG_PLA_TRXS.dr_additional_ed%TYPE,
48   P_DR_OTHER_ED                   IN  JAI_CMN_RG_PLA_TRXS.dr_other_ed%TYPE,
49   P_ORGANIZATION_ID               IN  JAI_CMN_RG_PLA_TRXS.organization_id%TYPE,
50   P_LOCATION_ID                   IN  JAI_CMN_RG_PLA_TRXS.location_id%TYPE,
51   P_BANK_BRANCH_ID                IN  JAI_CMN_RG_PLA_TRXS.bank_branch_id%TYPE,
52   P_ENTRY_DATE                    IN  JAI_CMN_RG_PLA_TRXS.entry_date%TYPE,
53   P_INVENTORY_ITEM_ID             IN  JAI_CMN_RG_PLA_TRXS.inventory_item_id%TYPE,
54   P_VENDOR_CUST_FLAG              IN  JAI_CMN_RG_PLA_TRXS.vendor_cust_flag%TYPE,
55   P_VENDOR_ID                     IN  JAI_CMN_RG_PLA_TRXS.vendor_id%TYPE,
56   P_VENDOR_SITE_ID                IN  JAI_CMN_RG_PLA_TRXS.vendor_site_id%TYPE,
57   P_EXCISE_INVOICE_NO             IN  JAI_CMN_RG_PLA_TRXS.excise_invoice_no%TYPE,
58   P_REMARKS                       IN  JAI_CMN_RG_PLA_TRXS.remarks%TYPE,
59   P_TRANSACTION_DATE              IN  JAI_CMN_RG_PLA_TRXS.transaction_date%TYPE,
60   P_CHARGE_ACCOUNT_ID             IN  JAI_CMN_RG_PLA_TRXS.charge_account_id%TYPE,
61   -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
62   P_OTHER_TAX_CREDIT              IN  JAI_CMN_RG_PLA_TRXS.other_tax_credit%TYPE,
63   P_OTHER_TAX_DEBIT               IN  JAI_CMN_RG_PLA_TRXS.other_tax_debit%TYPE,
64   p_transaction_type              IN  VARCHAR2,
65   p_transaction_source            IN  VARCHAR2,
66   p_called_from                   IN  VARCHAR2,
67   P_SIMULATE_FLAG                 IN  VARCHAR2,
68   P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
69   P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2,
70   p_rounding_id                   IN  NUMBER default null -- Vijay Shankar for Bug#4103161
71 ) IS
72 
73   ld_creation_date          DATE;
74   ln_created_by             NUMBER(15);
75   ld_last_update_date       DATE;
76   ln_last_updated_by        NUMBER(15);
77   ln_last_update_login      NUMBER(15);
78 
79   lv_last_register_id       NUMBER;
80   ln_slno                   NUMBER(10) := 0;
81   ln_transaction_id         NUMBER(10);
82   lv_transaction_type       VARCHAR2(50);
83   ln_opening_balance        NUMBER;
84   ln_closing_balance        NUMBER;
85   ln_tr_amount              NUMBER;
86 
87   ln_fin_year               NUMBER(4);
88   lv_range                  JAI_CMN_RG_PLA_TRXS.range_no%TYPE;
89   lv_division               JAI_CMN_RG_PLA_TRXS.division_no%TYPE;
90   lv_master_flag            JAI_CMN_RG_PLA_TRXS.master_flag%TYPE;
91 
92   r_last_record             c_get_last_record%ROWTYPE;
93   r_orgn_info               c_orgn_info%ROWTYPE;
94   lv_message                VARCHAR2(200);
95 
96   ln_record_exist_cnt       NUMBER(4);
97 
98   lv_statement_id           VARCHAR2(5);
99 
100   -- added, Harshita for 4892111
101   Cursor c_fetch_register_id IS
102   select JAI_CMN_RG_PLA_TRXS_S1.nextval from dual ;
103 
104   ln_register_id jai_cmn_rg_pla_trxs.register_id%type ;
105   -- ended, Harshita for 4892111
106 
107 BEGIN
108 
109 /*----------------------------------------------------------------------------------------------------------------------------
110 CHANGE HISTORY for FILENAME: jai_cmn_rg_pla_trxs_pkg.sql
111 S.No  dd/mm/yyyy   Author and Details
112 ------------------------------------------------------------------------------------------------------------------------------
113 1     16/07/2002   Vijay Shankar for Bug# 3496408, Version:115.0
114                     Table Handler coded for PLA table. Update_row of the package was just a skeleton that needs to be modified
115                     whenever it is being used
116 
117 2     03/01/2005   Vijay Shankar for Bug# 3940588, Version:115.1
118                     Modified Insert and Update procedures to include p_other_tax_credit and p_other_tax_debit parameters for
119                     Education Cess Enhancement
120 
121 3     19/04/2005   Vijay Shankar for Bug# 4103161, Version:115.2
122                     added a parameter ROUNDING_ID in insert and update procedures as part of RTV Rounding
123 
124                   * dependancy for later versions of this package *
125 
126 Dependancy:
127 -----------
128 IN60105D2 + 3496408
129 IN60106   + 3940588 + 4103161
130 
131 ----------------------------------------------------------------------------------------------------------------------------*/
132 
133 
134   lv_transaction_type   := p_transaction_type;
135 
136   lv_statement_id := '1';
137   get_trxn_type_and_id(lv_transaction_type, p_transaction_source, ln_transaction_id);
138 
139   lv_statement_id := '2';
140   ln_record_exist_cnt := get_trxn_entry_cnt(p_organization_id, p_location_id,
141                                             p_inventory_item_id, p_ref_document_id, ln_transaction_id);
142 
143   IF ln_record_exist_cnt > 0 THEN
144     p_process_status  := 'X';
145     p_process_message := 'PLA Entry was already made for the transaction';
146     GOTO end_of_processing;
147   END IF;
148 
149   lv_statement_id := '3';
150   ln_tr_amount := ( nvl(p_cr_basic_ed,0) + nvl(p_cr_additional_ed,0)+ nvl(p_cr_other_ed,0))
151                         - ( nvl(p_dr_basic_ed,0) + nvl(p_dr_additional_ed,0)+ nvl(p_dr_other_ed,0));
152 
153   lv_statement_id := '4';
154   OPEN c_orgn_info(p_organization_id, p_location_id);
155   FETCH c_orgn_info INTO r_orgn_info;
156   CLOSE c_orgn_info;
157 
158   lv_statement_id := '5';
159   ln_fin_year         := jai_general_pkg.get_fin_year(p_organization_id);
160 
161   lv_statement_id := '6';
162   lv_last_register_id := jai_general_pkg.get_last_record_of_rg
163                     ('PLA', p_organization_id, p_location_id, p_inventory_item_id, ln_fin_year);
164 
165   IF lv_last_register_id IS NULL THEN
166     IF r_orgn_info.ssi_unit_flag = jai_general_pkg.NO THEN
167       -- this is not an Error Condition
168       lv_message := 'PLA Register doesnt have sufficient balances';
169       GOTO end_of_processing;
170     ELSE
171       ln_slno := 1;
172     END IF;
173 
174   ELSE
175     lv_statement_id := '7';
176     OPEN c_get_last_record(lv_last_register_id);
177     FETCH c_get_last_record INTO r_last_record;
178     CLOSE c_get_last_record;
179 
180     IF r_last_record.fin_year = ln_fin_year THEN
181       ln_slno := nvl(r_last_record.slno, 0) + 1;
182     -- Start the serial number again in the new financial year
183     ELSE
184       ln_slno := 1;
185     END IF;
186   END IF;
187 
188   lv_statement_id := '8';
189   jai_cmn_rg_balances_pkg.get_balance(
190       P_ORGANIZATION_ID   => p_organization_id,
191       P_LOCATION_ID       => p_location_id,
192       P_REGISTER_TYPE     => 'PLA',
193       P_OPENING_BALANCE   => ln_opening_balance,
194       P_PROCESS_STATUS    => p_process_status,
195       P_PROCESS_MESSAGE   => p_process_message
196   );
197 
198   ln_closing_balance := ln_opening_balance + ln_tr_amount;
199 
200   IF r_orgn_info.ssi_unit_flag = jai_general_pkg.NO THEN
201     -- *** check whether the balances are enough
202     IF ln_closing_balance < 0 THEN
203       lv_message := 'PLA Register doesnt have sufficient balances';
204       GOTO end_of_processing;
205     END IF;
206   END IF;
207 
208   ld_creation_date      := SYSDATE;
209   ln_created_by         := FND_GLOBAL.user_id;
210   ld_last_update_date   := SYSDATE;
211   ln_last_updated_by    := ln_created_by;
212   ln_last_update_login  := FND_GLOBAL.login_id;
213 
214   lv_statement_id := '9';
215   lv_master_flag        := jai_general_pkg.get_orgn_master_flag(p_organization_id, p_location_id);
216 
217   lv_statement_id := '10';
218   jai_general_pkg.get_range_division(p_vendor_id, p_vendor_site_id, lv_range, lv_division);
219 
220   -- added, Harshita for 4892111
221   OPEN c_fetch_register_id ;
222   FETCH c_fetch_register_id INTO ln_register_id ;
223   CLOSE c_fetch_register_id ;
224   -- ended, Harshita for 4892111
225 
226   INSERT INTO JAI_CMN_RG_PLA_TRXS(
227     REGISTER_ID,
228     FIN_YEAR,
229     SLNO,
230     TR6_CHALLAN_NO,
231     TR6_CHALLAN_DATE,
232     CR_BASIC_ED,
233     CR_ADDITIONAL_ED,
234     CR_OTHER_ED,
235     TRANSACTION_SOURCE_NUM,
236     REF_DOCUMENT_ID,
237     REF_DOCUMENT_DATE,
238     DR_INVOICE_NO,
239     DR_INVOICE_DATE,
240     DR_BASIC_ED,
241     DR_ADDITIONAL_ED,
242     DR_OTHER_ED,
243     ORGANIZATION_ID,
244     LOCATION_ID,
245     BANK_BRANCH_ID,
246     ENTRY_DATE,
247     INVENTORY_ITEM_ID,
248     VENDOR_CUST_FLAG,
249     VENDOR_ID,
250     VENDOR_SITE_ID,
251     RANGE_NO,
252     DIVISION_NO,
253     EXCISE_INVOICE_NO,
254     REMARKS,
255     TRANSACTION_DATE,
256     OPENING_BALANCE,
257     CLOSING_BALANCE,
258     CHARGE_ACCOUNT_ID,
259     CREATION_DATE,
260     CREATED_BY,
261     LAST_UPDATE_DATE,
262     LAST_UPDATED_BY,
263     LAST_UPDATE_LOGIN,
264     POSTED_FLAG,
265     MASTER_FLAG,
266     BASIC_OPENING_BALANCE,
267     BASIC_CLOSING_BALANCE,
268     ADDITIONAL_OPENING_BALANCE,
269     ADDITIONAL_CLOSING_BALANCE,
270     OTHER_OPENING_BALANCE,
271     OTHER_CLOSING_BALANCE,
272     -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
273     other_tax_credit,
274     other_tax_debit,
275     rounding_id
276   ) VALUES (
277     ln_register_id, --JAI_CMN_RG_PLA_TRXS_S1.nextval, Harshita for 4892111        --P_REGISTER_ID,
278     ln_fin_year,                --P_FIN_YEAR,
279     ln_slno,                    --P_SLNO,
280     P_TR6_CHALLAN_NO,
281     P_TR6_CHALLAN_DATE,
282     P_CR_BASIC_ED,
283     P_CR_ADDITIONAL_ED,
284     P_CR_OTHER_ED,
285     ln_transaction_id,          --P_TRANSACTION_ID,
286     P_REF_DOCUMENT_ID,
287     P_REF_DOCUMENT_DATE,
288     P_DR_INVOICE_ID,
289     P_DR_INVOICE_DATE,
290     P_DR_BASIC_ED,
291     P_DR_ADDITIONAL_ED,
292     P_DR_OTHER_ED,
293     P_ORGANIZATION_ID,
294     P_LOCATION_ID,
295     P_BANK_BRANCH_ID,
296     P_ENTRY_DATE,
297     P_INVENTORY_ITEM_ID,
298     P_VENDOR_CUST_FLAG,
299     P_VENDOR_ID,
300     P_VENDOR_SITE_ID,
301     lv_range,                   --P_RANGE_NO,
302     lv_division,                --P_DIVISION_NO,
303     P_EXCISE_INVOICE_NO,
304     P_REMARKS,
305     P_TRANSACTION_DATE,
306     ln_opening_balance,         --P_OPENING_BALANCE,
307     ln_closing_balance,         --P_CLOSING_BALANCE,
308     P_CHARGE_ACCOUNT_ID,
309     ld_creation_date,           --P_CREATION_DATE,
310     ln_created_by,              --P_CREATED_BY,
311     ld_last_update_date,        --P_LAST_UPDATE_DATE,
312     ln_last_updated_by,         --P_LAST_UPDATED_BY,
313     ln_last_update_login,       --P_LAST_UPDATE_LOGIN,
314     'N',                        --P_POSTED_FLAG,
315     lv_master_flag,             --P_MASTER_FLAG,
316     NULL,                       --P_BASIC_OPENING_BALANCE,
317     NULL,                       --P_BASIC_CLOSING_BALANCE,
318     NULL,                       --P_ADDITIONAL_OPENING_BALANCE,
319     NULL,                       --P_ADDITIONAL_CLOSING_BALANCE,
320     NULL,                       --P_OTHER_OPENING_BALANCE,
321     NULL,                       --P_OTHER_CLOSING_BALANCE
322     -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
323     p_other_tax_credit,
324     p_other_tax_debit,
325     p_rounding_id   -- Vijay Shankar for Bug#4103161
326   ) RETURNING register_id INTO P_REGISTER_ID;
327 
328   lv_statement_id := '11';
329   jai_cmn_rg_balances_pkg.update_row(
330     p_organization_id   => p_organization_id,
331     p_location_id       => p_location_id,
332     p_register_type     => 'PLA',
333     p_amount_to_be_added=> ln_tr_amount,
334     p_simulate_flag     => p_simulate_flag,
335     p_process_status    => p_process_status,
336     p_process_message   => p_process_message
337   );
338 
339   <<end_of_processing>>
340 
341   NULL;
342 
343 EXCEPTION
344   WHEN OTHERS THEN
345     p_process_status := 'E';
346     p_process_message := 'PLA_PKG.insert_row->'||SQLERRM||', StmtId->'||lv_statement_id;
347     FND_FILE.put_line( FND_FILE.log, p_process_message);
348 
349 END insert_row;
350 
351 PROCEDURE update_row(
352 
353   P_REGISTER_ID                   IN  JAI_CMN_RG_PLA_TRXS.register_id%TYPE                                       DEFAULT NULL,
354   P_FIN_YEAR                      IN  JAI_CMN_RG_PLA_TRXS.fin_year%TYPE                                          DEFAULT NULL,
355   P_SLNO                          IN  JAI_CMN_RG_PLA_TRXS.slno%TYPE                                              DEFAULT NULL,
356   P_TR6_CHALLAN_NO                IN  JAI_CMN_RG_PLA_TRXS.tr6_challan_no%TYPE                                    DEFAULT NULL,
357   P_TR6_CHALLAN_DATE              IN  JAI_CMN_RG_PLA_TRXS.tr6_challan_date%TYPE                                  DEFAULT NULL,
358   P_CR_BASIC_ED                   IN  JAI_CMN_RG_PLA_TRXS.cr_basic_ed%TYPE                                       DEFAULT NULL,
359   P_CR_ADDITIONAL_ED              IN  JAI_CMN_RG_PLA_TRXS.cr_additional_ed%TYPE                                  DEFAULT NULL,
360   P_CR_OTHER_ED                   IN  JAI_CMN_RG_PLA_TRXS.cr_other_ed%TYPE                                       DEFAULT NULL,
361   P_TRANSACTION_ID                IN  JAI_CMN_RG_PLA_TRXS.TRANSACTION_SOURCE_NUM%TYPE                                    DEFAULT NULL,
362   P_REF_DOCUMENT_ID               IN  JAI_CMN_RG_PLA_TRXS.ref_document_id%TYPE                                   DEFAULT NULL,
363   P_REF_DOCUMENT_DATE             IN  JAI_CMN_RG_PLA_TRXS.ref_document_date%TYPE                                 DEFAULT NULL,
364   P_DR_INVOICE_ID                 IN  JAI_CMN_RG_PLA_TRXS.DR_INVOICE_NO%TYPE                                     DEFAULT NULL,
365   P_DR_INVOICE_DATE               IN  JAI_CMN_RG_PLA_TRXS.dr_invoice_date%TYPE                                   DEFAULT NULL,
366   P_DR_BASIC_ED                   IN  JAI_CMN_RG_PLA_TRXS.dr_basic_ed%TYPE                                       DEFAULT NULL,
367   P_DR_ADDITIONAL_ED              IN  JAI_CMN_RG_PLA_TRXS.dr_additional_ed%TYPE                                  DEFAULT NULL,
368   P_DR_OTHER_ED                   IN  JAI_CMN_RG_PLA_TRXS.dr_other_ed%TYPE                                       DEFAULT NULL,
369   P_ORGANIZATION_ID               IN  JAI_CMN_RG_PLA_TRXS.organization_id%TYPE                                   DEFAULT NULL,
370   P_LOCATION_ID                   IN  JAI_CMN_RG_PLA_TRXS.location_id%TYPE                                       DEFAULT NULL,
371   P_BANK_BRANCH_ID                IN  JAI_CMN_RG_PLA_TRXS.bank_branch_id%TYPE                                    DEFAULT NULL,
372   P_ENTRY_DATE                    IN  JAI_CMN_RG_PLA_TRXS.entry_date%TYPE                                        DEFAULT NULL,
373   P_INVENTORY_ITEM_ID             IN  JAI_CMN_RG_PLA_TRXS.inventory_item_id%TYPE                                 DEFAULT NULL,
374   P_VENDOR_CUST_FLAG              IN  JAI_CMN_RG_PLA_TRXS.vendor_cust_flag%TYPE                                  DEFAULT NULL,
375   P_VENDOR_ID                     IN  JAI_CMN_RG_PLA_TRXS.vendor_id%TYPE                                         DEFAULT NULL,
376   P_VENDOR_SITE_ID                IN  JAI_CMN_RG_PLA_TRXS.vendor_site_id%TYPE                                    DEFAULT NULL,
377   P_RANGE_NO                      IN  JAI_CMN_RG_PLA_TRXS.range_no%TYPE                                          DEFAULT NULL,
378   P_DIVISION_NO                   IN  JAI_CMN_RG_PLA_TRXS.division_no%TYPE                                       DEFAULT NULL,
379   P_EXCISE_INVOICE_NO             IN  JAI_CMN_RG_PLA_TRXS.excise_invoice_no%TYPE                                 DEFAULT NULL,
380   P_REMARKS                       IN  JAI_CMN_RG_PLA_TRXS.remarks%TYPE                                           DEFAULT NULL,
381   P_TRANSACTION_DATE              IN  JAI_CMN_RG_PLA_TRXS.transaction_date%TYPE                                  DEFAULT NULL,
382   P_OPENING_BALANCE               IN  JAI_CMN_RG_PLA_TRXS.opening_balance%TYPE                                   DEFAULT NULL,
383   P_CLOSING_BALANCE               IN  JAI_CMN_RG_PLA_TRXS.closing_balance%TYPE                                   DEFAULT NULL,
384   P_CHARGE_ACCOUNT_ID             IN  JAI_CMN_RG_PLA_TRXS.charge_account_id%TYPE                                 DEFAULT NULL,
385   P_POSTED_FLAG                   IN  JAI_CMN_RG_PLA_TRXS.posted_flag%TYPE                                       DEFAULT NULL,
386   P_MASTER_FLAG                   IN  JAI_CMN_RG_PLA_TRXS.master_flag%TYPE                                       DEFAULT NULL,
387   -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
388   P_OTHER_TAX_CREDIT              IN  JAI_CMN_RG_PLA_TRXS.other_tax_credit%TYPE                                  DEFAULT NULL,
389   P_OTHER_TAX_DEBIT               IN  JAI_CMN_RG_PLA_TRXS.other_tax_debit%TYPE                                   DEFAULT NULL,
390   p_rounding_id                   IN  NUMBER default null -- Vijay Shankar for Bug#4103161
391 ) IS
392 
393 /* Added by Ramananda for bug#4407165 */
394 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_pla_trxs_pkg.update_row';
395 
396 BEGIN
397 
398   UPDATE JAI_CMN_RG_PLA_TRXS SET
399     REGISTER_ID                   = nvl(P_REGISTER_ID, REGISTER_ID),
400     FIN_YEAR                      = nvl(P_FIN_YEAR, FIN_YEAR),
401     SLNO                          = nvl(P_SLNO, SLNO),
402     TR6_CHALLAN_NO                = nvl(P_TR6_CHALLAN_NO, TR6_CHALLAN_NO),
403     TR6_CHALLAN_DATE              = nvl(P_TR6_CHALLAN_DATE, TR6_CHALLAN_DATE),
404     CR_BASIC_ED                   = nvl(P_CR_BASIC_ED, CR_BASIC_ED),
405     CR_ADDITIONAL_ED              = nvl(P_CR_ADDITIONAL_ED, CR_ADDITIONAL_ED),
406     CR_OTHER_ED                   = nvl(P_CR_OTHER_ED, CR_OTHER_ED),
407     TRANSACTION_SOURCE_NUM                = nvl(P_TRANSACTION_ID, TRANSACTION_SOURCE_NUM),
408     REF_DOCUMENT_ID               = nvl(P_REF_DOCUMENT_ID, REF_DOCUMENT_ID),
409     REF_DOCUMENT_DATE             = nvl(P_REF_DOCUMENT_DATE, REF_DOCUMENT_DATE),
410     DR_INVOICE_NO                 = nvl(P_DR_INVOICE_ID, DR_INVOICE_NO),
411     DR_INVOICE_DATE               = nvl(P_DR_INVOICE_DATE, DR_INVOICE_DATE),
412     DR_BASIC_ED                   = nvl(P_DR_BASIC_ED, DR_BASIC_ED),
413     DR_ADDITIONAL_ED              = nvl(P_DR_ADDITIONAL_ED, DR_ADDITIONAL_ED),
414     DR_OTHER_ED                   = nvl(P_DR_OTHER_ED, DR_OTHER_ED),
415     ORGANIZATION_ID               = nvl(P_ORGANIZATION_ID, ORGANIZATION_ID),
416     LOCATION_ID                   = nvl(P_LOCATION_ID, LOCATION_ID),
417     BANK_BRANCH_ID                = nvl(P_BANK_BRANCH_ID, BANK_BRANCH_ID),
418     ENTRY_DATE                    = nvl(P_ENTRY_DATE, ENTRY_DATE),
419     INVENTORY_ITEM_ID             = nvl(P_INVENTORY_ITEM_ID, INVENTORY_ITEM_ID),
420     VENDOR_CUST_FLAG              = nvl(P_VENDOR_CUST_FLAG, VENDOR_CUST_FLAG),
421     VENDOR_ID                     = nvl(P_VENDOR_ID, VENDOR_ID),
422     VENDOR_SITE_ID                = nvl(P_VENDOR_SITE_ID, VENDOR_SITE_ID),
423     RANGE_NO                      = nvl(P_RANGE_NO, RANGE_NO),
424     DIVISION_NO                   = nvl(P_DIVISION_NO, DIVISION_NO),
425     EXCISE_INVOICE_NO             = nvl(P_EXCISE_INVOICE_NO, EXCISE_INVOICE_NO),
426     REMARKS                       = nvl(P_REMARKS, REMARKS),
427     TRANSACTION_DATE              = nvl(P_TRANSACTION_DATE, TRANSACTION_DATE),
428     OPENING_BALANCE               = nvl(P_OPENING_BALANCE, OPENING_BALANCE),
429     CLOSING_BALANCE               = nvl(P_CLOSING_BALANCE, CLOSING_BALANCE),
430     CHARGE_ACCOUNT_ID             = nvl(P_CHARGE_ACCOUNT_ID, CHARGE_ACCOUNT_ID),
431     LAST_UPDATE_DATE              = sysdate,
432     LAST_UPDATED_BY               = fnd_global.user_id,
433     LAST_UPDATE_LOGIN             = fnd_global.login_id,
434     POSTED_FLAG                   = nvl(P_POSTED_FLAG, POSTED_FLAG),
435     MASTER_FLAG                   = nvl(P_MASTER_FLAG, MASTER_FLAG),
436     -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
437     OTHER_TAX_CREDIT              = nvl(P_OTHER_TAX_CREDIT, OTHER_TAX_CREDIT),
438     OTHER_TAX_debit               = nvl(P_OTHER_TAX_debit, OTHER_TAX_debit),
439     rounding_id                   = nvl(p_rounding_id, rounding_id) -- Vijay Shankar for Bug#4103161
440   WHERE register_id = p_register_id;
441 
442 /* Added by Ramananda for bug#4407165 */
443  EXCEPTION
444   WHEN OTHERS THEN
445     FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
446     FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
447     app_exception.raise_exception;
448 
449 END update_row;
450 
451 PROCEDURE update_payment_details(
452   p_register_id         IN  NUMBER,
453   p_charge_account_id   IN  NUMBER
454 ) IS
455 
456 BEGIN
457 
458   UPDATE JAI_CMN_RG_PLA_TRXS
459   SET charge_account_id = p_charge_account_id,
460     last_update_date= SYSDATE
461   WHERE register_id = p_register_id;
462 
463 END update_payment_details;
464 
465 FUNCTION get_trxn_entry_cnt(
466   p_organization_id   IN NUMBER,
467   p_location_id     IN NUMBER,
468   p_inventory_item_id IN NUMBER,
469   p_ref_document_id    IN NUMBER,
470   p_transaction_id IN NUMBER
471 ) RETURN NUMBER IS
472 
473   ln_record_exist_cnt       NUMBER(4);
474   CURSOR c_record_exist IS
475     SELECT count(1)
476     FROM JAI_CMN_RG_PLA_TRXS
477     WHERE organization_id = p_organization_id
478     AND location_id = p_location_id
479     AND inventory_item_id = p_inventory_item_id
480     AND ref_document_id = p_ref_document_id
481     AND transaction_source_num = p_transaction_id;
482 
483 BEGIN
484 
485   OPEN c_record_exist;
486   FETCH c_record_exist INTO ln_record_exist_cnt;
487   CLOSE c_record_exist;
488 
489   IF ln_record_exist_cnt > 0 THEN
490     FND_FILE.put_line( FND_FILE.log, 'PLA Duplicate Chk:'||ln_record_exist_cnt
491       ||', PARAMS: Orgn>'||p_organization_id||', Loc>'||p_location_id
492       ||', Item>'||p_inventory_item_id
493       ||', TrxId>'||p_ref_document_id||', type>'||p_transaction_id
494     );
495   END IF;
496 
497   RETURN ln_record_exist_cnt;
498 
499 END get_trxn_entry_cnt;
500 
501 ----------------------- Get transaction id -------------------------------------------
502 PROCEDURE get_trxn_type_and_id(
503   p_transaction_type    IN OUT NOCOPY VARCHAR2,
504   p_transaction_source  IN      VARCHAR2,
505   p_transaction_id OUT NOCOPY NUMBER
506 ) IS
507 
508 BEGIN
509   IF p_transaction_type = 'RECEIVE' AND p_transaction_source = 'RMA' THEN
510     p_transaction_id := 18;
511     p_transaction_type := 'CR';
512   ELSIF p_transaction_type = 'RECEIVE' THEN
513     p_transaction_id := 18;
514     p_transaction_type := 'R';
515   ELSIF p_transaction_type = 'RETURN TO RECEIVING' THEN
516     p_transaction_id := 18;
517     p_transaction_type := 'R';
518   ELSIF p_transaction_type = 'DELIVER' THEN
519     p_transaction_id := 18;
520     p_transaction_type := 'R';
521   ELSIF p_transaction_type = 'RETURN TO VENDOR' THEN
522     p_transaction_id := 19;
523     p_transaction_type := 'RTV';
524   ELSIF p_transaction_type = jai_constants.service_src_distribute_in THEN
525     p_transaction_id    := 151;
526     p_transaction_type  := jai_constants.service_src_distribute_in;
527   ELSIF p_transaction_type = jai_constants.service_src_distribute_out THEN
528     p_transaction_id    := 152;
529     p_transaction_type  := jai_constants.service_src_distribute_out ;
530   -- Added by Brathod, for Inv.Convergence
531   ELSIF p_transaction_source  = 'OPM_OSP' AND p_transaction_type ='I' THEN
532     p_transaction_type  := 201;
533   ELSIF p_transaction_source  = 'OPM_OSP' AND p_transaction_type ='R' THEN
534     p_transaction_type  := 202;
535 
536   /* cbabu for bug# 6012570 (5876390). Projects Billing implementation */
537   ELSIF p_transaction_type = 'DRAFT_INVOICE' and p_transaction_source = 'DRAFT_INVOICE_RELEASE' then
538     p_transaction_id    := 30;
539     p_transaction_type := 'PROJECTS-BILLING';
540   /*added by vkaranam for bug 6030615 */
541  --start
542  ELSIF p_transaction_type='INTERORG_XFER' and p_transaction_source='Direct Org Transfer' then
543      p_transaction_id    := 3;
544  ELSIF p_transaction_type='INTERORG_XFER' and p_transaction_source='Intransit Shipment' then
545     p_transaction_id    := 21;
546  --end 6030615
547 
548   ELSE
549     p_transaction_id := 20;
550     p_transaction_type := 'MISC';
551   END IF;
552 
553 END get_trxn_type_and_id;
554 
555 PROCEDURE generate_component_balances
556     (
557         errbuf VARCHAR2,
558         retcode VARCHAR2
559     )
560 IS
561     CURSOR FETCH_REGISTER_DETAILS IS
562     SELECT
563         A.ORGANIZATION_ID,
564         A.LOCATION_ID ,
565         A.INVENTORY_ITEM_ID,
566         A.FIN_YEAR,
567         A.REGISTER_ID,
568         A.SLNO,
569         A.CR_BASIC_ED,
570         A.CR_ADDITIONAL_ED,
571         A.CR_OTHER_ED,
572         A.DR_BASIC_ED,
573         A.DR_ADDITIONAL_ED,
574         A.DR_OTHER_ED,
575         A.CREATION_DATE,
576         A.CREATED_BY,
577         A.LAST_UPDATE_DATE,
578         A.LAST_UPDATED_BY,
579         A.TRANSACTION_DATE,
580         A.LAST_UPDATE_LOGIN
581     FROM JAI_CMN_RG_PLA_TRXS A
582     WHERE NOT EXISTS (  SELECT '1'
583                         FROM   JAI_CMN_RG_PLA_CMP_DTLS B
584                         WHERE  B.REGISTER_ID = A.REGISTER_ID
585                         AND    B.SLNO        = A.SLNO)
586     ORDER BY REGISTER_ID,SLNO;
587 
588     --Variable Declarations starts here.......
589     V_BASIC_OPENING_BALANCE         NUMBER :=0;
590     V_ADDITIONAL_OPENING_BALANCE    NUMBER :=0;
591     V_OTHER_OPENING_BALANCE         NUMBER :=0;
592     V_BASIC_CLOSING_BALANCE         NUMBER;
593     V_ADDITIONAL_CLOSING_BALANCE    NUMBER;
594     V_OTHER_CLOSING_BALANCE         NUMBER;
595     V_COUNT NUMBER;
596 
597     v_commit_count                  NUMBER:=0;
598     --Variable Declarations Ends here..........
599 
600 
601 BEGIN --B1
602 
603 /*------------------------------------------------------------------------------------------
604      FILENAME: jai_cmn_rg_pla_trxs_pkg.generate_component_balances.sql
605      CHANGE HISTORY:
606 
607     1.  2002/07/28   Nagaraj.s - For Enh#2371031
608                      This Procedure is Created for Textile Industry specifically wherin individual
609                      balances of Excise components are to be maintained.
610                      In case of  data not existing in Excise Component Balances for an combination of
611                      Organization/Location, this will inserts data into JAI_CMN_RG_COMP_BALS
612                      and JAI_CMN_RG_PLA_CMP_DTLS tables and if data exists, then it will insert data
613                      into JAI_CMN_RG_PLA_CMP_DTLS and updates JAI_CMN_RG_COMP_BALS table.
614 
615 -----------------------------------------------------------------------------------------------
616 */
617 
618 
619   FOR CUR_REC IN FETCH_REGISTER_DETAILS
620   LOOP  --L1
621     BEGIN
622       SELECT NVL(BASIC_PLA_BALANCE,0),
623              NVL(ADDITIONAL_PLA_BALANCE,0),
624              NVL(OTHER_PLA_BALANCE,0)
625        INTO  V_BASIC_OPENING_BALANCE,
626              V_ADDITIONAL_OPENING_BALANCE,
627              V_OTHER_OPENING_BALANCE
628        FROM  JAI_CMN_RG_COMP_BALS
629        WHERE ORGANIZATION_ID= CUR_REC.ORGANIZATION_ID AND
630              LOCATION_ID= CUR_REC.LOCATION_ID;
631 
632     EXCEPTION
633       WHEN NO_DATA_FOUND THEN
634                     --If the combination of Organization and Location do not exist then
635                     INSERT INTO JAI_CMN_RG_COMP_BALS
636                     (COMPONENT_BALANCE_ID,
637                     ORGANIZATION_ID,
638                     LOCATION_ID,
639                     BASIC_RG23A_BALANCE,
640                     ADDITIONAL_RG23A_BALANCE,
641                     OTHER_RG23A_BALANCE,
642                     BASIC_RG23C_BALANCE,
643                     ADDITIONAL_RG23C_BALANCE,
644                     OTHER_RG23C_BALANCE,
645                     BASIC_PLA_BALANCE,
646                     ADDITIONAL_PLA_BALANCE,
647                     OTHER_PLA_BALANCE,
648                     CREATION_DATE,
649                     CREATED_BY,
650                     LAST_UPDATE_DATE,
651                     LAST_UPDATED_BY,
652                     LAST_UPDATE_LOGIN
653                     )
654                     VALUES
655                     ( JAI_CMN_RG_COMP_BALS_S.nextval,
656                     CUR_REC.ORGANIZATION_ID,
657                     CUR_REC.LOCATION_ID,
658                     0,
659                     0,
660                     0,
661                     0,
662                     0,
663                     0,
664                     0,
665                     0,
666                     0,
667                     TRUNC(SYSDATE),
668                     CUR_REC.CREATED_BY,
669                     TRUNC(SYSDATE),
670                     CUR_REC.LAST_UPDATED_BY,
671                     CUR_REC.LAST_UPDATE_LOGIN
672                     );
673 
674                     V_BASIC_OPENING_BALANCE := 0;
675                     V_ADDITIONAL_OPENING_BALANCE := 0;
676                     V_OTHER_OPENING_BALANCE := 0;
677 
678     END;
679 
680 
681     --Calculation of present Lines Closing Balances..........
682     BEGIN
683       V_BASIC_CLOSING_BALANCE       := V_BASIC_OPENING_BALANCE + NVL(CUR_REC.CR_BASIC_ED,0) - NVL(CUR_REC.DR_BASIC_ED,0);
684       V_ADDITIONAL_CLOSING_BALANCE  := V_ADDITIONAL_OPENING_BALANCE + NVL(CUR_REC.CR_ADDITIONAL_ED,0) - NVL(CUR_REC.DR_ADDITIONAL_ED,0);
685       V_OTHER_CLOSING_BALANCE       := V_OTHER_OPENING_BALANCE + NVL(CUR_REC.CR_OTHER_ED,0) - NVL(CUR_REC.DR_OTHER_ED,0);
686     END;
687 
688 
689     INSERT INTO JAI_CMN_RG_PLA_CMP_DTLS
690             (
691             ORGANIZATION_ID,
692             LOCATION_ID,
693             INVENTORY_ITEM_ID,
694             FIN_YEAR,
695             REGISTER_ID,
696             SLNO ,
697             BASIC_OPENING_BALANCE,
698             ADDITIONAL_OPENING_BALANCE,
699             OTHER_OPENING_BALANCE,
700             CR_BASIC_ED,
701             CR_ADDITIONAL_ED,
702             CR_OTHER_ED ,
703             DR_BASIC_ED,
704             DR_ADDITIONAL_ED ,
705             DR_OTHER_ED,
706             BASIC_CLOSING_BALANCE,
707             ADDITIONAL_CLOSING_BALANCE,
708             OTHER_CLOSING_BALANCE,
709             CREATION_DATE ,
710             CREATED_BY ,
711             LAST_UPDATE_DATE ,
712             LAST_UPDATED_BY,
713             LAST_UPDATE_LOGIN,
714             TRANSACTION_DATE
715             )
716             VALUES
717             (
718             CUR_REC.ORGANIZATION_ID,
719             CUR_REC.LOCATION_ID,
720             CUR_REC.INVENTORY_ITEM_ID,
721             CUR_REC.FIN_YEAR,
722             CUR_REC.REGISTER_ID,
723             CUR_REC.SLNO,
724             V_BASIC_OPENING_BALANCE,
725             V_ADDITIONAL_OPENING_BALANCE,
726             V_OTHER_OPENING_BALANCE,
727             CUR_REC.CR_BASIC_ED,
728             CUR_REC.CR_ADDITIONAL_ED,
729             CUR_REC.CR_OTHER_ED,
730             CUR_REC.DR_BASIC_ED,
731             CUR_REC.DR_ADDITIONAL_ED,
732             CUR_REC.DR_OTHER_ED,
733             V_BASIC_CLOSING_BALANCE,
734             V_ADDITIONAL_CLOSING_BALANCE,
735             V_OTHER_CLOSING_BALANCE,
736             TRUNC(SYSDATE),
737             CUR_REC.CREATED_BY,
738             TRUNC(SYSDATE),
739             CUR_REC.LAST_UPDATED_BY,
740             CUR_REC.LAST_UPDATE_LOGIN,
741             CUR_REC.TRANSACTION_DATE
742             );
743 
744             --To Update Register Balances.................
745             UPDATE JAI_CMN_RG_COMP_BALS
746             SET
747             BASIC_PLA_BALANCE        = V_BASIC_CLOSING_BALANCE,
748             ADDITIONAL_PLA_BALANCE   = V_ADDITIONAL_CLOSING_BALANCE,
749             OTHER_PLA_BALANCE        = V_OTHER_CLOSING_BALANCE,
750             LAST_UPDATE_DATE         = TRUNC(SYSDATE),
751             LAST_UPDATED_BY          = CUR_REC.LAST_UPDATED_BY
752             WHERE ORGANIZATION_ID    = CUR_REC.ORGANIZATION_ID AND
753             LOCATION_ID              = CUR_REC.LOCATION_ID;
754              --Updation Ends here.....................................
755            -- COMMIT;
756            IF v_commit_count = 100 THEN
757              COMMIT;
758              v_commit_count := 0;
759            ELSE
760              v_commit_count := v_commit_count + 1;
761            END IF;
762     END LOOP;  --L1
763 
764     COMMIT;
765 
766 EXCEPTION --Ex1
767     WHEN OTHERS THEN
768       ROLLBACK;
769 END generate_component_balances; --E1
770 
771 
772 END jai_cmn_rg_pla_trxs_pkg;