[Home] [Help]
PACKAGE BODY: APPS.JAI_PA_SETUP_CTX_PKG
Source
1 PACKAGE BODY jai_pa_setup_ctx_pkg AS
2 /* $Header: jai_pa_setup_ctx.plb 120.0.12000000.1 2007/10/24 18:20:39 rallamse noship $ */
3 /*----------------------------------------------------------------------------------------
4 Change History
5 S.No. DATE Description
6 ------------------------------------------------------------------------------------------
7
8 1 24/04/1005 cbabu for bug#6012570 (5876390) Version: 120.0
9 Projects Billing Enh.
10 forward ported from R11i to R12
11
12 ---------------------------------------------------------------------------------------- */
13
14
15
16 PROCEDURE Load_Row( x_context_id NUMBER ,
17 x_context VARCHAR2 ,
18 x_attribute1_usage VARCHAR2 ,
19 x_attribute2_usage VARCHAR2 ,
20 x_attribute3_usage VARCHAR2 ,
21 x_attribute4_usage VARCHAR2 ,
22 x_attribute5_usage VARCHAR2 ,
23 x_setup_value1_usage VARCHAR2 ,
24 x_setup_value2_usage VARCHAR2 ,
25 x_setup_value3_usage VARCHAR2 ,
26 x_setup_value4_usage VARCHAR2 ,
27 x_setup_value5_usage VARCHAR2 ,
28 x_owner VARCHAR2,
29 x_last_update_date VARCHAR2,
30 x_force_edits VARCHAR2 ) IS
31 v_creation_date date;
32 v_rowid rowid := null;
33 v_user_id NUMBER := fnd_load_util.owner_id(x_owner);
34 vf_ludate DATE := to_date(x_last_update_date, 'DD-MM-YYYY HH:MI:SS');
35 vd_ludate DATE;
36
37 BEGIN
38
39 -- validate input parameters
40 IF(x_context_id is NULL) then
41 fnd_message.set_name('JA', 'JAI_VALUE_MESSAGE');
42 fnd_message.set_token('JAI_MESSAGE', 'Load_Row: Required data is not provided.');
43 app_exception.raise_exception;
44 end if;
45
46
47
48 DECLARE
49
50 CURSOR cur_creation_date IS
51 select creation_date, last_update_date
52 from JAI_PA_SETUP_CONTEXTS
53 where context_id = x_context_id ;
54
55 BEGIN
56 /* Check if the row exists in the database. If it does, retrieves the creation date for update_row. */
57 OPEN cur_creation_date ;
58 FETCH cur_creation_date into v_creation_date, vd_ludate ;
59 IF cur_creation_date%NOTFOUND THEN
60 raise NO_DATA_FOUND ;
61 END IF;
62 CLOSE cur_creation_date ;
63
64 /* Removed the check for userid and added check using last_update_date
65 for bug 4967445 */
66
67 /* Update only if force_edits is 'Y' or if vf_ludate > vd_ludate */
68 if ( vf_ludate > vd_ludate or X_Force_Edits = 'Y' ) then
69 -- update row if present
70 jai_pa_setup_ctx_pkg.Update_Row(
71 X_Rowid => v_rowid ,
72 x_context_id => x_context_id ,
73 x_context => x_context ,
74 x_attribute1_usage => x_attribute1_usage ,
75 x_attribute2_usage => x_attribute2_usage ,
76 x_attribute3_usage => x_attribute3_usage ,
77 x_attribute4_usage => x_attribute4_usage ,
78 x_attribute5_usage => x_attribute5_usage ,
79 x_setup_value1_usage => x_setup_value1_usage ,
80 x_setup_value2_usage => x_setup_value2_usage ,
81 x_setup_value3_usage => x_setup_value3_usage ,
82 x_setup_value4_usage => x_setup_value4_usage ,
83 x_setup_value5_usage => x_setup_value5_usage ,
84 x_creation_date => v_creation_date ,
85 X_last_update_date => vf_ludate ,
86 X_last_updated_by => v_user_id ,
87 X_last_update_login => 0);
88 end if;
89 exception
90 when NO_DATA_FOUND then
91 jai_pa_setup_ctx_pkg.Insert_Row(
92 X_Rowid => v_rowid,
93 X_context_id => x_context_id ,
94 X_context => x_context ,
95 X_attribute1_usage => x_attribute1_usage ,
96 X_attribute2_usage => x_attribute2_usage ,
97 X_attribute3_usage => x_attribute3_usage ,
98 X_attribute4_usage => x_attribute4_usage ,
99 X_attribute5_usage => x_attribute5_usage ,
100 X_setup_value1_usage => x_setup_value1_usage ,
101 X_setup_value2_usage => x_setup_value2_usage ,
102 X_setup_value3_usage => x_setup_value3_usage ,
103 X_setup_value4_usage => x_setup_value4_usage ,
104 X_setup_value5_usage => x_setup_value5_usage ,
105 X_last_update_date => vf_ludate,
106 X_last_updated_by => v_user_id,
107 X_creation_date => sysdate,
108 X_created_by => v_user_id,
109 X_last_update_login => 0) ;
110 END;
111 EXCEPTION
112 WHEN OTHERS then
113 fnd_message.set_name('JA', 'JAI_VALUE_MESSAGE');
114 fnd_message.set_token('JAI_MESSAGE', 'Error in Load_Row: jai_rgm_defs_pkg');
115 app_exception.raise_exception;
116 END Load_Row;
117
118 PROCEDURE Insert_Row(
119 X_Rowid IN OUT NOCOPY ROWID,
120 x_context_id NUMBER ,
121 x_context VARCHAR2,
122 x_attribute1_usage VARCHAR2,
123 x_attribute2_usage VARCHAR2,
124 x_attribute3_usage VARCHAR2,
125 x_attribute4_usage VARCHAR2,
126 x_attribute5_usage VARCHAR2,
127 x_setup_value1_usage VARCHAR2,
128 x_setup_value2_usage VARCHAR2,
129 x_setup_value3_usage VARCHAR2,
130 x_setup_value4_usage VARCHAR2,
131 x_setup_value5_usage VARCHAR2,
132 X_last_update_date DATE,
133 X_last_updated_by NUMBER,
134 X_creation_date DATE ,
135 X_created_by NUMBER,
136 X_last_update_login NUMBER) IS
137
138 BEGIN
139
140 INSERT INTO JAI_PA_SETUP_CONTEXTS(
141 context_id ,
142 context ,
143 attribute1_usage ,
144 attribute2_usage ,
145 attribute3_usage ,
146 attribute4_usage ,
147 attribute5_usage ,
148 setup_value1_usage ,
149 setup_value2_usage ,
150 setup_value3_usage ,
151 setup_value4_usage ,
152 setup_value5_usage ,
153 creation_date ,
154 created_by ,
155 last_update_date ,
156 last_updated_by ,
157 last_update_login
158 )
159 SELECT
160 X_CONTEXT_ID ,
161 x_context ,
162 x_attribute1_usage ,
163 x_attribute2_usage ,
164 x_attribute3_usage ,
165 x_attribute4_usage ,
166 x_attribute5_usage ,
167 x_setup_value1_usage ,
168 x_setup_value2_usage ,
169 x_setup_value3_usage ,
170 x_setup_value4_usage ,
171 x_setup_value5_usage ,
172 X_creation_date ,
173 X_created_by ,
174 X_last_update_date ,
175 X_last_updated_by ,
176 X_last_update_login
177 FROM DUAL ;
178
179 END Insert_Row;
180
181 PROCEDURE Update_Row(X_Rowid IN OUT NOCOPY ROWID,
182 x_context_id NUMBER ,
183 x_context VARCHAR2,
184 x_attribute1_usage VARCHAR2,
185 x_attribute2_usage VARCHAR2,
186 x_attribute3_usage VARCHAR2,
187 x_attribute4_usage VARCHAR2,
188 x_attribute5_usage VARCHAR2,
189 x_setup_value1_usage VARCHAR2,
190 x_setup_value2_usage VARCHAR2,
191 x_setup_value3_usage VARCHAR2,
192 x_setup_value4_usage VARCHAR2,
193 x_setup_value5_usage VARCHAR2,
194 x_creation_date DATE,
195 X_last_update_date DATE,
196 X_last_updated_by NUMBER,
197 X_last_update_login NUMBER)
198 IS
199 BEGIN
200 UPDATE JAI_PA_SETUP_CONTEXTS
201 SET
202 CONTEXT = x_context ,
203 ATTRIBUTE1_USAGE = x_attribute1_usage ,
204 ATTRIBUTE2_USAGE = x_attribute2_usage ,
205 ATTRIBUTE3_USAGE = x_attribute3_usage ,
206 ATTRIBUTE4_USAGE = x_attribute4_usage ,
207 ATTRIBUTE5_USAGE = x_attribute5_usage ,
208 SETUP_VALUE1_USAGE = x_setup_value1_usage,
209 SETUP_VALUE2_USAGE = x_setup_value2_usage,
210 SETUP_VALUE3_USAGE = x_setup_value3_usage,
211 SETUP_VALUE4_USAGE = x_setup_value4_usage,
212 SETUP_VALUE5_USAGE = x_setup_value5_usage,
213 creation_date = x_creation_date ,
214 last_update_date = x_last_update_date ,
215 last_updated_by = x_last_updated_by ,
216 last_update_login = x_last_update_login
217 WHERE
218 context_id = x_context_id;
219
220 if (SQL%NOTFOUND) then
221 RAISE NO_DATA_FOUND;
222 end if;
223
224 END Update_Row;
225
226 END jai_pa_setup_ctx_pkg ;