1 PACKAGE BODY JAI_JRG_PLA_TRIGGER_PKG AS
2 /* $Header: jai_jrg_pla_t.plb 120.3 2007/09/21 13:07:45 csahoo 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
110
111
112 Future Dependencies For the release Of this Object:-
113 (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/
114 A datamodel change )
115
116 ----------------------------------------------------------------------------------------------------------------------------------------------------
117 Current Version Current Bug Dependent Files Version Author Date Remarks
118 Of File On Bug/Patchset Dependent On
119 ja_in_pla_bi_trg.sql
120 ----------------------------------------------------------------------------------------------------------------------------------------------------
121 616.1 3025626 IN60104D1+3025626 1. ja_3025626_alter.sql 616.1 Aiyer 21/07/2003 Enhancement, Introduced data
122 2. JAI_RCV_CLAIM_MODVAT_V.sql 616.1 model changes in table JAI_RCV_CENVAT_CLAIMS,
123 3. ja_in_rg23_part_i_bi_trg.sql 616.1 alterations in view JAI_RCV_CLAIM_MODVAT_V,
124 4. ja_in_rg23_part_ii_bi_trg.sql 616.1 added 3 new fields in form JAINMVAT.fmb and
125 5. JAINMVAT.fmb 616.4 2 new triggers - ja_in_rg23_part_i_bi_trg and
126 6. JAF23A_1.rdf 616.1 ja_in_rg23_part_ii_bi_trg. All present in patch 3025626.
127
128 115.1 3964409 1. jai_cmn_utils_pkg.sql 115.0 Sanjikum 27/10/2004
129 2. ja_in_create_3964409_apps.sql 115.0
130 3. ja_in_create_3964409_ja.sql 115.0
131 4. ja_in_datafix_po_3964409.sql 115.0
132 **********************************************************************************************************************************************************************/
133
134 --Start added by Sanjikum for Bug #3964409
135 ln_txn_amt := NVL(pr_new.cr_basic_ed,0) + NVL(pr_new.cr_additional_ed,0) + NVL(pr_new.cr_other_ed,0)
136 - NVL(pr_new.dr_basic_ed,0) - NVL(pr_new.dr_additional_ed,0) - NVL(pr_new.dr_other_ed,0);
137 /*Bug 5141459 start*/
138 IF pr_new.transaction_source_num is NULL or pr_new.tr6_source = 'CONSOLIDATION' OR pr_new.tr6_source ='MANUAL' THEN /*added OR pr_new.tr6_source ='MANUAL' for bug #5894216*/ /*rchandan for bug#4548378*/
139 return;
140 END IF;
141 /*Bug 5141459 End*/
142 jai_cmn_utils_pkg.update_rg_slno(
143 pn_organization_id => pr_new.organization_id,
144 pn_location_id => pr_new.location_id,
145 pv_register_type => 'PLA',
146 pn_fin_year => pr_new.fin_year,
147 pn_txn_amt => ln_txn_amt,
148 pn_slno => ln_slno,
149 pn_opening_balance => ln_opening_balance,
150 pn_closing_balance => ln_closing_balance
151 );
152
153 pr_new.slno := ln_slno;
154 pr_new.opening_balance := ln_opening_balance;
155 pr_new.closing_balance := ln_closing_balance;
156 --End added by Sanjikum for Bug #3964409
157
158 --Added the IF condition and commented the Trigger condition above by Sanjikum for Bug #3964409
159 IF pr_new.transaction_source_num = 19 THEN
160 OPEN c_fetch_vendor_info('RECEIVE');
161 FETCH c_fetch_vendor_info INTO v_vendor_change ,
162 v_vendor_id ,
163 v_vendor_site_id;
164
165 IF c_fetch_vendor_info%FOUND THEN
166 IF nvl(v_vendor_change,'N') = 'N' THEN
167 CLOSE c_fetch_vendor_info;
168 return; /* The Trigger should not do anything when vendor,vendor site is not changed. */
169
170 ELSE /* Incase the count is present, then this table should have the changed vendor id and vendor site id.*/
171 /* Assigning the changed vendor values. */
172 pr_new.vendor_id := v_vendor_id ;
173 pr_new.vendor_site_id := v_vendor_site_id;
174
175 /* To bring the Division and Range of the changed vendor/vendor site id. */
176 jai_rcv_utils_pkg.get_div_range
177 (
178 v_vendor_id ,
179 v_vendor_site_id ,
180 v_range_no ,
181 v_division_no
182 );
183 pr_new.range_no := v_range_no;
184 pr_new.division_no := v_division_no;
185 END IF;
186 END IF;
187 CLOSE c_fetch_vendor_info;
188 END IF;
189
190 /* Added an exception block by Ramananda for bug#4570303 */
191 EXCEPTION
192 WHEN OTHERS THEN
193 --raise_application_error(-20010,'Exception is raised in ja_in_pla_bi_trg' || sqlerrm);
194 Pv_return_code := jai_constants.unexpected_error;
195 Pv_return_message := 'Encountered an error in JAI_JRG_PLA_TRIGGER_PKG.BRIU_T1 ' || substr(sqlerrm,1,1900);
196
197
198 END BRI_T1 ;
199
200 END JAI_JRG_PLA_TRIGGER_PKG ;
[Home] [Help]