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