[Home] [Help]
PACKAGE BODY: APPS.JAI_AP_DVA_TRIGGER_PKG
Source
1 PACKAGE BODY JAI_AP_DVA_TRIGGER_PKG AS
2 /* $Header: jai_ap_dva_t.plb 120.0 2005/09/01 12:34:27 rallamse noship $ */
3 /*
4 REM +======================================================================+
5 REM NAME ARI_T1
6 REM
7 REM DESCRIPTION Called from trigger JAI_AP_DVA_ARIUD_T1
8 REM
9 REM NOTES Refers to old trigger JAI_AP_DVA_ARI_T1
10 REM
11 REM +======================================================================+
12 */
13 PROCEDURE ARI_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
14 -- Trigger used for Supplier Merge. When this process is done then the taxes
15 -- associated for the old supplier site needs to be copied on to the new site
16 -- of the new supplier. TDS info. also has to be copied.
17 /* Ramananda for File.Sql.35, start */
18 v_vendor_id NUMBER; -- := pr_new.Vendor_Id;
19 v_vendor_site_id NUMBER; -- := pr_new.Vendor_Site_Id;
20 v_dup_vendor_id NUMBER; -- := pr_new.Duplicate_Vendor_Id;
21 v_dup_vendor_site_id NUMBER; -- := pr_new.Duplicate_Vendor_Site_Id;
22 v_keep_site_flag VARCHAR2(1); -- := pr_new.Keep_Site_Flag;
23 v_cre_dt DATE; -- := pr_new.Creation_Date;
24 v_cre_by NUMBER;-- := pr_new.Created_By;
25 v_last_upd_dt DATE; -- := pr_new.Last_Update_Date ;
26 v_last_upd_by NUMBER; -- := pr_new.Last_Updated_By;
27 v_last_upd_login NUMBER; -- := pr_new.Last_Update_Login;
28 /* --Ramananda for File.Sql.35, end */
29
30 v_code VARCHAR2(15);
31 v_n_vendor_site_id NUMBER;
32
33 ------------------------------>
34
35 CURSOR Fetch_Vendor_Site_Id_Cur( code IN VARCHAR2 ) IS
36 SELECT MAX( Vendor_Site_Id )
37 FROM Po_Vendor_Sites_All
38 WHERE Vendor_Site_Code = code;
39
40 CURSOR Fetch_Vendor_Code_Cur IS
41 SELECT Vendor_Site_Code
42 FROM Po_Vendor_Sites_All
43 WHERE Vendor_Site_Id = v_dup_vendor_site_id;
44 -- AND Vendor_Id = v_vendor_id;
45
46 ------------------------------>
47
48 CURSOR Fetch_Excise_Dtl_Cur( v_n_vendor_site_id IN NUMBER ) IS
49 SELECT Excise_Duty_Region, Excise_Duty_Zone, Excise_Duty_Reg_No,
50 Excise_Duty_Range, Excise_Duty_Division, Excise_Duty_Circle,
51 Excise_Duty_Comm, St_Reg_No, Cst_Reg_No, Ec_Code,
52 Tax_Category_List
53 FROM JAI_CMN_VENDOR_SITES
54 WHERE Vendor_Id = v_dup_vendor_id
55 AND Vendor_Site_Id = v_n_vendor_site_id;
56
57 ------------------------------>
58
59 Excise_Rec Fetch_Excise_Dtl_Cur%ROWTYPE;
60
61 BEGIN
62 pv_return_code := jai_constants.successful ;
63 /*------------------------------------------------------------------------------------------
64 FILENAME:ja_in_supplier_merg_trg.sql
65
66 CHANGE HISTORY:
67 S.No Date Author and Details
68 ------------------------------------------------------------------------------------------
69 1 29-Nov-2004 Sanjikum for 4035297. Version 115.1
70 For 'INR' check, added the call to jai_cmn_utils_pkg.check_jai_exists
71
72 Dependency Due to this Bug:-
73 The current trigger becomes dependent on the function jai_cmn_utils_pkg.check_jai_exists version 115.0.
74
75 2. 13-Jun-2005 File Version: 116.3
76 Ramananda for bug#4428980. Removal of SQL LITERALs is done
77
78 Future Dependencies For the release Of this Object:-
79 (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/
80 A datamodel change )
81 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
82 Current Version Current Bug Dependent Files Version Author Date Remarks
83 Of File On Bug/Patchset Dependent On
84
85 ja_in_supplier_merg_trg.sql
86
87 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
88 DB Entity as required for CASE COMPLAINCE. Version 116.1
89 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
90 115.1 4035297 IN60105D2+4033992 ja_in_util_pkg_s.sql 115.0 Sanjikum 29-Nov-2004 Call to this function.
91 ja_in_util_pkg_s.sql 115.0 Sanjikum
92 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
93
94 /* --Ramananda for File.Sql.35, start */
95 v_vendor_id := pr_new.Vendor_Id;
96 v_vendor_site_id := pr_new.Vendor_Site_Id;
97 v_dup_vendor_id := pr_new.Duplicate_Vendor_Id;
98 v_dup_vendor_site_id := pr_new.Duplicate_Vendor_Site_Id;
99 v_keep_site_flag := pr_new.Keep_Site_Flag;
100 v_cre_dt := pr_new.Creation_Date;
101 v_cre_by := pr_new.Created_By;
102 v_last_upd_dt := pr_new.Last_Update_Date ;
103 v_last_upd_by := pr_new.Last_Updated_By;
104 v_last_upd_login := pr_new.Last_Update_Login;
105 /* --Ramananda for File.Sql.35, end */
106
107 --added the below by Sanjikum for Bug#4035297
108 --IF jai_cmn_utils_pkg.check_jai_exists(p_calling_object => 'JA_IN_SUPPLIER_MERG_TRG',
109 -- p_org_id => pr_new.org_id) = FALSE THEN
110 -- RETURN;
111 --END IF;
112
113 UPDATE JAI_CMN_VENDOR_SITES
114 SET Inactive_Flag = 'Y',
115 Last_Updated_By = v_last_upd_by,
116 Last_Update_Date = v_last_upd_dt,
117 Last_Update_Login = v_last_upd_login
118 WHERE Vendor_id = v_dup_vendor_id
119 AND Vendor_Site_Id = v_dup_vendor_site_id;
120
121 /* copied the below update from ja_in_po_tds_ins_supp_merg as part of its obsoletion */
122 UPDATE JAI_CMN_VENDOR_SITES --Ja_In_Po_Vendor_Sites
123 SET Inactive_Flag = 'Y',
124 Last_Updated_By = v_last_upd_by,
125 Last_Update_Date = v_last_upd_dt,
126 Last_Update_Login = v_last_upd_login
127 WHERE Vendor_id = v_dup_vendor_id
128 AND Vendor_Site_Id = 0;
129
130 IF v_keep_site_flag = 'Y' THEN
131 OPEN Fetch_Vendor_Code_Cur;
132 FETCH Fetch_Vendor_Code_Cur INTO v_code;
133 CLOSE Fetch_Vendor_Code_Cur;
134
135 OPEN Fetch_Vendor_Site_Id_Cur( v_code );
136 FETCH Fetch_Vendor_Site_Id_Cur INTO v_n_vendor_site_id;
137 CLOSE Fetch_Vendor_Site_Id_Cur;
138
139 OPEN Fetch_Excise_Dtl_Cur( v_n_vendor_site_id);
140 FETCH Fetch_Excise_Dtl_Cur INTO Excise_Rec;
141 CLOSE Fetch_Excise_Dtl_Cur;
142
143 INSERT INTO JAI_CMN_VENDOR_SITES( VENDOR_ID,
144 VENDOR_SITE_ID,
145 EXCISE_DUTY_REGION,
146 EXCISE_DUTY_ZONE,
147 EXCISE_DUTY_REG_NO,
148 EXCISE_DUTY_RANGE,
149 EXCISE_DUTY_DIVISION,
150 EXCISE_DUTY_CIRCLE,
151 EXCISE_DUTY_COMM,
152 ST_REG_NO,
153 CST_REG_NO,
154 EC_CODE,
155 CREATION_DATE,
156 CREATED_BY,
157 LAST_UPDATE_DATE,
158 LAST_UPDATED_BY,
159 LAST_UPDATE_LOGIN,
160 TAX_CATEGORY_LIST,
161 INACTIVE_FLAG )
162 VALUES
163 ( v_vendor_id,
164 v_n_vendor_site_id,
165 Excise_Rec.Excise_Duty_Region,
166 Excise_Rec.Excise_Duty_Zone,
167 Excise_Rec.Excise_Duty_Reg_No,
168 Excise_Rec.Excise_Duty_Range,
169 Excise_Rec.Excise_Duty_Division,
170 Excise_Rec.Excise_Duty_Circle,
171 Excise_Rec.Excise_Duty_Comm,
172 Excise_Rec.St_Reg_No,
173 Excise_Rec.Cst_Reg_No,
174 Excise_Rec.Ec_Code,
175 v_cre_dt,
176 v_cre_by,
177 v_last_upd_dt,
178 v_last_upd_by,
179 v_last_upd_login,
180 Excise_Rec.Tax_Category_List,
181 NULL
182 );
183 END IF;
184
185 /*
186 || Commented by Ramananda as a part of removal of SQL LITERALs
187 jai_cmn_vendor_pkg.supplier_merge(
188 v_vendor_id,
189 v_vendor_site_id,
190 v_dup_vendor_id,
191 v_dup_vendor_site_id,
192 v_cre_dt,
193 v_cre_by,
194 v_last_upd_dt,
195 v_last_upd_by,
196 v_last_upd_login );
197 */
198 /* Added an exception block by Ramananda for bug#4570303 */
199 EXCEPTION
200 WHEN OTHERS THEN
201 Pv_return_code := jai_constants.unexpected_error;
202 Pv_return_message := 'Encountered an error in JAI_AP_DVA_TRIGGER_PKG.ARI_T1 ' || substr(sqlerrm,1,1900);
203 END ARI_T1 ;
204
205 END JAI_AP_DVA_TRIGGER_PKG ;