1 PACKAGE BODY JAI_JRG_23AC2_TRIGGER_PKG AS
2 /* $Header: jai_jrg_23ac2_t.plb 120.2.12010000.2 2008/12/19 06:53:58 srjayara ship $ */
3
4 /*
5 REM +======================================================================+
6 REM NAME BRI_T1
7 REM
8 REM DESCRIPTION Called from trigger JAI_JRG_23AC2_BRIUD_T1
9 REM
10 REM NOTES Refers to old trigger JAI_JRG_23AC2_BRI_T1
11 REM
12 REM +======================================================================+
13 */
14 PROCEDURE BRI_T1 ( pr_old t_rec%type , pr_new in out t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
15 /*
16 This cursor is to fetch the Information regarding vendor change from JAI_RCV_CENVAT_CLAIMS.
17 */
18
19 CURSOR c_fetch_vendor_info(cp_transaction_type rcv_transactions.transaction_type%type)
20 IS
21 SELECT
22 vendor_changed_flag ,
23 vendor_id ,
24 vendor_site_id
25 FROM
26 JAI_RCV_CENVAT_CLAIMS
27 WHERE
28 transaction_id in
29 (
30 SELECT
31 transaction_id
32 FROM
33 rcv_transactions
34 WHERE
35 transaction_type = cp_transaction_type /* 'RECEIVE' --Ramananda for removal of SQL LITERALs */
36 START WITH
37 transaction_id = pr_new.receipt_ref
38 CONNECT BY PRIOR
39 parent_transaction_id = transaction_id
40 );
41
42 --Variable definitions
43 v_vendor_change JAI_RCV_CENVAT_CLAIMS.vendor_changed_flag%type ; /* To hold the Flag value whether vendor is changed or not. */
44 v_vendor_id JAI_RCV_CENVAT_CLAIMS.vendor_id%type ; /* To hold the vendor id of ja_in_receipt_cenvat. */
45 v_vendor_site_id JAI_RCV_CENVAT_CLAIMS.vendor_site_id%type ; /* To hold the vendor site id of ja_in_receipt_cenvat. */
46 v_range_no JAI_CMN_VENDOR_SITES.excise_duty_range%type ;
47 v_division_no JAI_CMN_VENDOR_SITES.excise_duty_division%type ;
48 --Added the below 4 variables by Sanjikum for Bug #3964409
49 ln_slno JAI_CMN_RG_23AC_II_TRXS.slno%TYPE;
50 ln_opening_balance JAI_CMN_RG_23AC_II_TRXS.opening_balance%TYPE;
51 ln_closing_balance JAI_CMN_RG_23AC_II_TRXS.closing_balance%TYPE;
52 ln_txn_amt JAI_CMN_RG_23AC_II_TRXS.cr_basic_ed%TYPE;
53 BEGIN
54 pv_return_code := jai_constants.successful ;
55 /*********************************************************************************************************************************************************************
56
57 Created By : Aiyer
58
59 Creation Date : 21-Jul-2003
60
61 Enhancement Bug : 3025626
62
63 Purpose : Modify the vendor_id, vendor_site_id, Range and Division information in JAI_CMN_RG_23AC_II_TRXS table from the
64 JAI_RCV_CENVAT_CLAIMS table when a third party supplier information has been registered through the
65 Claim Modvat On Receipt form.
66
67 Dependency : - The following dependency has been created in this bug
68 1. Technical dependency due to datamodel change :-
69 3 new fields VENDOR_CHANGED_FLAG,VENDOR_ID and VENDOR_SITE_ID have
70 been added to the table JAI_RCV_CENVAT_CLAIMS and the view JAI_RCV_CLAIM_MODVAT_V
71 (base view of the form JAINMVAT.fmb) has been modified to add vendor_changed_flag
72 and vendor_site_id. Also the way the vendor_id is fetched in the view has also been changed.
73
74 2. Functional Dependency:-
75 The form JAINMVAT, with this enhancement, has the capability to capture the third party vendor
76 information.
77 For this 3 new fields vendor_changed_flag, dsp_vendor_site_code and vendor_site_id have been added to the form.
78 Another 2 new triggers ja_in_rg23_part_i_bi_trg and ja_in_pla_bi_trg.sql have been created as a part of this enhancement bug.
79
80 Change History :
81
82 1. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
83 DB Entity as required for CASE COMPLAINCE. Version 116.1
84
85
86 2. 13-Jun-2005 File Version: 116.2
87 Ramananda for bug#4428980. Removal of SQL LITERALs is done
88
89 3. 3-Mar-2007 bduvarag for bug#5141459,File version 120.2
90 Forward porting the changes done in 11i bug#4548378
91
92 4. 19-Dec-2008 Bug 7637074 File version 120.0.12000000.3 / 120.2.12010000.2 / 120.3
93 Issue : Addl. CVD amount is not considered for the balances.
94 Fix : Included addl. CVD amounts in the calculation of ln_txn_amt.
95 This is the same as bug 5377703 (forward port for bug 5362183).
96
97 /*----------------------------------------------------------------------------------------------------------------------
98 CHANGE HISTORY: FILENAME: ja_in_rg23_part_ii_bi_trg.sql
99 S.No Date Author and Details
100 ------------------------------------------------------------------------------------------------------------------------
101 1 27/10/2004 Sanjikum for Bug # 3964409, File version 115.1
102
103 Issue
104 -----
105 Duplicate Slno is generated in the JAI_CMN_RG_PLA_TRXS table
106
107 Fix
108 ---
109 1. Commented the Trigger condition - "WHEN ( new.transaction_id = 18 )"
110 2. In the Begin Block, added the call to ja_in_rg_util.update_rg_slno
111 3. Added an If condition, similar to the Trigger when condition and moved all existing
112 Code in it
113
114
115 Future Dependencies For the release Of this Object:-
116 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
117 A datamodel change )
118
119 ----------------------------------------------------------------------------------------------------------------------------------------------------
120 Current Version Current Bug Dependent Files Version Author Date Remarks
121 Of File On Bug/Patchset Dependent On
122 ja_in_rg23_part_ii_bi_trg.sql
123 ----------------------------------------------------------------------------------------------------------------------------------------------------
124 616.1 3025626 IN60104D1+3025626 1. ja_3025626_alter.sql 616.1 Aiyer 21/07/2003 Enhancement, Introduced data
125 2. JAI_RCV_CLAIM_MODVAT_V.sql 616.1 model changes in table JAI_RCV_CENVAT_CLAIMS,
126 3. ja_in_pla_bi_trg.sql 616.1 alterations in view JAI_RCV_CLAIM_MODVAT_V,
127 4. ja_in_rg23_part_i_bi_trg.sql 616.1 added 3 new fields in form JAINMVAT.fmb and
128 5. JAINMVAT.fmb 616.4 2 new triggers - ja_in_pla_bi_trg.sql and
129 6. JAF23A_1.rdf 616.1 ja_in_rg23_part_i_bi_trg.sql. All present in patch 3025626.
130
131 115.1 3964409 1. jai_cmn_utils_pkg.sql 115.0 Sanjikum 27/10/2004
132 2. ja_in_create_3964409_apps.sql 115.0
133 3. ja_in_create_3964409_ja.sql 115.0
134 4. ja_in_datafix_po_3964409.sql 115.0
135 5. ja_in_pla_bi_trg.sql 115.0
136 **********************************************************************************************************************************************************************/
137
138 --Start added by Sanjikum for Bug #3964409
139 /*additional CVD fields included in formula for bug 7637074*/
140 ln_txn_amt := NVL(pr_new.cr_basic_ed,0) + NVL(pr_new.cr_additional_ed,0) + NVL(pr_new.cr_other_ed,0) + NVL(pr_new.cr_additional_cvd,0)
141 - NVL(pr_new.dr_basic_ed,0) - NVL(pr_new.dr_additional_ed,0) - NVL(pr_new.dr_other_ed,0) - NVL(pr_new.dr_additional_cvd,0);
142
143 IF pr_new.transaction_source_num IS NOT NULL THEN /*Bug 5141459 bduvarag*/
144 jai_cmn_utils_pkg.update_rg_slno(
145 pn_organization_id => pr_new.organization_id,
146 pn_location_id => pr_new.location_id,
147 pv_register_type => pr_new.register_type,
148 pn_fin_year => pr_new.fin_year,
149 pn_txn_amt => ln_txn_amt,
150 pn_slno => ln_slno,
151 pn_opening_balance => ln_opening_balance,
152 pn_closing_balance => ln_closing_balance
153 );
154
155 pr_new.slno := ln_slno;
156 pr_new.opening_balance := ln_opening_balance;
157 pr_new.closing_balance := ln_closing_balance;
158 END IF; /*Bug 5141459 bduvarag*/
159 --End added by Sanjikum for Bug #3964409
160
161 --Added the IF condition and commented the Trigger condition above by Sanjikum for Bug #3964409
162 IF pr_new.transaction_source_num = 18 THEN
163 OPEN c_fetch_vendor_info('RECEIVE');
164 FETCH c_fetch_vendor_info INTO v_vendor_change ,
165 v_vendor_id ,
166 v_vendor_site_id;
167
168 IF c_fetch_vendor_info%FOUND THEN
169 IF nvl(v_vendor_change,'N') = 'N' THEN
170 CLOSE c_fetch_vendor_info;
171 return; /* The Trigger should not do anything when vendor,vendor site is not changed. */
172
173 ELSE /* Incase the count is present, then this table should have the changed vendor id and vendor site id.*/
174 /* Assigning the changed vendor values. */
175 pr_new.vendor_id := v_vendor_id ;
176 pr_new.vendor_site_id := v_vendor_site_id;
177
178 /* To bring the Division and Range of the changed vendor/vendorsite id. */
179 jai_rcv_utils_pkg.get_div_range
180 (
181 v_vendor_id ,
182 v_vendor_site_id ,
183 v_range_no ,
184 v_division_no
185 );
186 pr_new.range_no := v_range_no;
187 pr_new.division_no := v_division_no;
188 END IF;
189 END IF;
190 CLOSE c_fetch_vendor_info;
191 END IF;
192 exception
193 when others THEN
194 /* raise_application_error(-20010,'Exception is raised in ja_in_rg23_part_ii_bi_trg' || sqlerrm);
195 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Exception is raised in ja_in_rg23_part_ii_bi_trg' || sqlerrm ; return ;
196 END BRI_T1 ;
197 END JAI_JRG_23AC2_TRIGGER_PKG ;
[Home] [Help]