[Home] [Help]
PACKAGE BODY: APPS.JAI_RGM_LKPS_PKG
Source
1 PACKAGE BODY jai_rgm_lkps_pkg AS
2 /* $Header: jai_rgm_lkps_th.plb 120.3 2006/01/20 16:14:03 avallabh ship $ */
3
4 PROCEDURE Load_Row( x_regime_code VARCHAR2,
5 x_attribute_type_code VARCHAR2,
6 x_attribute_code VARCHAR2,
7 x_attribute_value VARCHAR2,
8 x_display_value VARCHAR2,
9 x_default_record VARCHAR2,
10 x_owner VARCHAR2,
11 x_last_update_date VARCHAR2, /* 4967445 */
12 x_force_edits VARCHAR2 ) IS
13 v_creation_date date;
14 v_rowid rowid := null;
15 v_user_id NUMBER := fnd_load_util.owner_id(x_owner);
16 vf_ludate DATE := to_date(x_last_update_date, 'DD-MM-YYYY HH:MI:SS');
17 vd_ludate DATE;
18
19 BEGIN
20
21 -- validate input parameters
22 IF(X_REGIME_CODE is NULL)
23 then
24 fnd_message.set_name('JA', 'JAI_VALUE_MESSAGE');
25 fnd_message.set_token('JAI_MESSAGE', 'Load_Row: Required data is not provided.');
26 app_exception.raise_exception;
27 end if;
28
29 /* Commented the below since we got the user_id from fnd_load_util
30 if (X_OWNER = 'SEED') then
31 v_user_id := 1;
32 end if; */
33
34 DECLARE
35
36 CURSOR cur_creation_date
37 IS
38 select creation_date, last_update_date
39 from jai_rgm_lookups
40 where regime_code = x_regime_code
41 and attribute_type_code = x_attribute_type_code
42 and attribute_code = x_attribute_code
43 and nvl(attribute_value, 0) = nvl(ltrim(rtrim(x_attribute_value)), 0) ;
44
45 BEGIN
46 /* Check if the row exists in the database. If it does, retrieves the creation date for update_row. */
47 OPEN cur_creation_date ;
48 FETCH cur_creation_date into v_creation_date, vd_ludate ;
49 IF cur_creation_date%NOTFOUND THEN
50 raise NO_DATA_FOUND ;
51 END IF ;
52 CLOSE cur_creation_date;
53
54 /* Removed the check for userid and added check using last_update_date
55 for bug 4967445 */
56 /* Update only if force_edits is 'Y' or if vf_ludate > vd_ludate */
57
58 if ( vf_ludate > vd_ludate or X_Force_Edits = 'Y' ) then
59 -- update row if present
60 jai_rgm_lkps_pkg.Update_Row(
61 X_Rowid => v_rowid,
62 X_regime_code => X_regime_code ,
63 x_attribute_type_code => x_attribute_type_code ,
64 x_attribute_code => x_attribute_code ,
65 x_attribute_value => x_attribute_value ,
66 x_display_value => x_display_value ,
67 x_default_record => x_default_record ,
68 x_creation_date => v_creation_date,
69 X_last_update_date => vf_ludate,
70 X_last_updated_by => v_user_id,
71 X_last_update_login => 0);
72 end if;
73 EXCEPTION
74 when NO_DATA_FOUND then
75 jai_rgm_lkps_pkg.Insert_Row(
76 X_Rowid => v_rowid,
77 X_regime_code => X_regime_code ,
78 x_attribute_type_code => x_attribute_type_code ,
79 x_attribute_code => x_attribute_code ,
80 x_attribute_value => x_attribute_value ,
81 x_display_value => x_display_value ,
82 x_default_record => x_default_record ,
83 X_last_update_date => vf_ludate,
84 X_last_updated_by => v_user_id,
85 X_creation_date => sysdate,
86 X_created_by => v_user_id,
87 X_last_update_login => 0) ;
88 END;
89 EXCEPTION
90 WHEN OTHERS then
91 fnd_message.set_name('JA', 'JAI_VALUE_MESSAGE');
92 fnd_message.set_token('JAI_MESSAGE', 'Error in Load_Row: jai_rgm_lkps_pkg');
93 app_exception.raise_exception;
94 END Load_Row;
95
96 PROCEDURE Insert_Row(
97 X_Rowid IN OUT NOCOPY rowid,
98 X_regime_code VARCHAR2,
99 x_attribute_type_code VARCHAR2,
100 x_attribute_code VARCHAR2,
101 x_attribute_value VARCHAR2,
102 x_display_value VARCHAR2,
103 x_default_record VARCHAR2,
104 x_creation_date DATE ,
105 X_Last_Update_Date DATE,
106 X_Last_Updated_By NUMBER,
107 X_Created_by NUMBER,
108 X_Last_Update_Login NUMBER) IS
109 CURSOR C_LOOKUP_ID IS SELECT JAI_RGM_LOOKUPS_S.nextval FROM dual;
110 X_lookup_id NUMBER ;
111 BEGIN
112
113 OPEN C_LOOKUP_ID;
114 FETCH C_LOOKUP_ID INTO X_lookup_id;
115 CLOSE C_LOOKUP_ID;
116
117 INSERT INTO JAI_RGM_LOOKUPS(
118 lookup_id ,
119 regime_code ,
120 attribute_type_code ,
121 attribute_code ,
122 attribute_value ,
123 display_value ,
124 default_record ,
125 creation_date ,
126 created_by ,
127 last_update_date ,
128 last_update_login ,
129 last_updated_by )
130 SELECT
131 X_lookup_id ,
132 X_regime_code ,
133 X_attribute_type_code ,
134 X_attribute_code ,
135 X_attribute_value ,
136 X_display_value ,
137 X_default_record ,
138 X_Creation_Date ,
139 X_Created_by ,
140 X_Last_Update_Date ,
141 X_Last_Update_Login ,
142 X_Last_Updated_By
143 FROM DUAL ;
144
145 if (SQL%NOTFOUND) then
146 RAISE NO_DATA_FOUND;
147 end if;
148
149 END Insert_Row;
150
151 PROCEDURE Update_Row(X_Rowid IN OUT NOCOPY rowid,
152 X_regime_code VARCHAR2,
153 x_attribute_type_code VARCHAR2,
154 x_attribute_code VARCHAR2,
155 x_attribute_value VARCHAR2,
156 x_display_value VARCHAR2,
157 x_default_record VARCHAR2,
158 x_creation_date DATE,
159 X_last_update_date DATE,
160 X_last_updated_by NUMBER,
161 X_last_update_login NUMBER)
162 IS
163 BEGIN
164 UPDATE JAI_RGM_LOOKUPS
165 SET
166 regime_code = x_regime_code ,
167 attribute_type_code = x_attribute_type_code ,
168 attribute_code = x_attribute_code ,
169 attribute_value = x_attribute_value ,
170 display_value = x_display_value ,
171 default_record = x_default_record ,
172 creation_date = x_creation_date ,
173 last_update_date = x_last_update_date ,
174 last_update_login = x_last_update_login ,
175 last_updated_by = x_last_updated_by
176 WHERE
177 regime_code = x_regime_code
178 and attribute_type_code = x_attribute_type_code
179 and attribute_code = x_attribute_code
180 and nvl(attribute_value, 0) = nvl(ltrim(rtrim(x_attribute_value)), 0) ; --4477004
181
182
183 if (SQL%NOTFOUND) then
184 RAISE NO_DATA_FOUND;
185 end if;
186
187 END Update_Row;
188
189 END jai_rgm_lkps_pkg ;