[Home] [Help]
PACKAGE BODY: APPS.PO_PRICE_DIFFERENTIALS_PKG
Source
1 PACKAGE BODY PO_PRICE_DIFFERENTIALS_PKG AS
2 /* $Header: POXVPDTB.pls 120.1 2005/08/31 07:13:45 arudas noship $*/
3
4 -------------------------------------------------------------------------------
5 -------------------------------------------------------------------------------
6 --Start of Comments
7 --Name: insert_row
8 --Pre-reqs:
9 -- None.
10 --Modifies:
11 -- None.
12 --Locks:
13 -- None.
14 --Function:
15 -- Table handler for INSERT of Price Differentials.
16 --Parameters:
17 -- None.
18 --Testing:
19 -- None.
20 --End of Comments
21 -------------------------------------------------------------------------------
22 -------------------------------------------------------------------------------
23 PROCEDURE insert_row
24 ( p_price_differential_rec IN PO_PRICE_DIFFERENTIALS%ROWTYPE
25 , x_row_id OUT NOCOPY ROWID
26 )
27 IS
28 BEGIN
29
30 -- Insert record into PO_PRICE_DIFFERENTIALS base table
31 --
32 INSERT INTO po_price_differentials
33 ( price_differential_id
34 , price_differential_num
35 , entity_type
36 , entity_id
37 , price_type
38 , multiplier
39 , min_multiplier
40 , max_multiplier
41 , enabled_flag
42 , created_by
43 , creation_date
44 , last_updated_by
45 , last_update_date
46 , last_update_login
47 )
48 VALUES
49 ( p_price_differential_rec.price_differential_id
50 , p_price_differential_rec.price_differential_num
51 , p_price_differential_rec.entity_type
52 , p_price_differential_rec.entity_id
53 , p_price_differential_rec.price_type
54 , p_price_differential_rec.multiplier
55 , p_price_differential_rec.min_multiplier
56 , p_price_differential_rec.max_multiplier
57 , p_price_differential_rec.enabled_flag
58 , p_price_differential_rec.created_by
59 , p_price_differential_rec.creation_date
60 , p_price_differential_rec.last_updated_by
61 , p_price_differential_rec.last_update_date
62 , p_price_differential_rec.last_update_login
63 )
64 RETURNING rowid
65 INTO x_row_id;
66
67 EXCEPTION
68
69 WHEN OTHERS THEN
70 PO_MESSAGE_S.sql_error('PO_PRICE_DIFFERENTIALS_PKG.insert_row','000',sqlcode);
71 raise;
72
73 END insert_row;
74
75 -------------------------------------------------------------------------------
76 -------------------------------------------------------------------------------
77 --Start of Comments
78 --Name: update_row
79 --Pre-reqs:
80 -- None.
81 --Modifies:
82 -- None.
83 --Locks:
84 -- None.
85 --Function:
86 -- Table handler for UPDATE of Price Differentials.
87 --Parameters:
88 -- None.
89 --Testing:
90 -- None.
91 --End of Comments
92 -------------------------------------------------------------------------------
93 -------------------------------------------------------------------------------
94 PROCEDURE update_row
95 ( p_price_differential_rec IN PO_PRICE_DIFFERENTIALS%ROWTYPE
96 , p_row_id IN ROWID
97 )
98 IS
99 BEGIN
100
101 -- Update record in PO_PRICE_DIFFERENTIALS base table
102 --
103 UPDATE po_price_differentials
104 SET price_differential_num = p_price_differential_rec.price_differential_num
105 , price_type = p_price_differential_rec.price_type
106 , multiplier = p_price_differential_rec.multiplier
107 , min_multiplier = p_price_differential_rec.min_multiplier
108 , max_multiplier = p_price_differential_rec.max_multiplier
109 , enabled_flag = p_price_differential_rec.enabled_flag
110 , last_updated_by = p_price_differential_rec.last_updated_by
111 , last_update_date = p_price_differential_rec.last_update_date
112 , last_update_login = p_price_differential_rec.last_update_login
113 WHERE rowid = p_row_id;
114
115 EXCEPTION
116
117 WHEN OTHERS THEN
118 PO_MESSAGE_S.sql_error('PO_PRICE_DIFFERENTIALS_PKG.lock_row','000',sqlcode);
119 raise;
120
121 END update_row;
122
123
124 -------------------------------------------------------------------------------
125 -------------------------------------------------------------------------------
126 --Start of Comments
127 --Name: lock_row
128 --Pre-reqs:
129 -- None.
130 --Modifies:
131 -- None.
132 --Locks:
133 -- None.
134 --Function:
135 -- Table handler for locking of Price Differentials.
136 --Parameters:
137 -- None.
138 --Testing:
139 -- None.
140 --End of Comments
141 -------------------------------------------------------------------------------
142 -------------------------------------------------------------------------------
143 PROCEDURE lock_row
144 ( p_form_rec IN PO_PRICE_DIFFERENTIALS%ROWTYPE
145 , p_row_id IN ROWID
146 )
147 IS
148 l_db_rec PO_PRICE_DIFFERENTIALS%ROWTYPE;
149
150 CURSOR price_diff_csr IS
151 SELECT *
152 FROM po_price_differentials
153 WHERE rowid = p_row_id
154 FOR UPDATE NOWAIT;
155
156 BEGIN
157
158 OPEN price_diff_csr;
159
160 -- Check if record is currently locked by someone else
161 -- ( APP_EXCEPTION.RECORD_LOCK_EXCEPTION will be thrown if fetch fails )
162 --
163 FETCH price_diff_csr INTO l_db_rec;
164
165 -- Check if record exists
166 --
167 IF ( price_diff_csr%NOTFOUND )
168 THEN
169 CLOSE price_diff_csr;
170 FND_MESSAGE.set_name('FND','FORM_RECORD_DELETED');
171 APP_EXCEPTION.raise_exception;
172 END IF;
173
174 -- Check if anybody has modified and committed the record from the time
175 -- the data was queried up in the form until now
176 --
177 IF ( ( l_db_rec.price_differential_num = p_form_rec.price_differential_num )
178 AND ( l_db_rec.price_type = p_form_rec.price_type )
179 AND ( ( l_db_rec.multiplier = p_form_rec.multiplier )
180 OR ( ( l_db_rec.multiplier IS NULL )
181 AND ( p_form_rec.multiplier IS NULL ) ) )
182 AND ( ( l_db_rec.min_multiplier = p_form_rec.min_multiplier )
183 OR ( ( l_db_rec.min_multiplier IS NULL )
184 AND ( p_form_rec.min_multiplier IS NULL ) ) )
185 AND ( ( l_db_rec.max_multiplier = p_form_rec.max_multiplier )
186 OR ( ( l_db_rec.max_multiplier IS NULL )
187 AND ( p_form_rec.max_multiplier IS NULL ) ) )
188 AND ( ( l_db_rec.enabled_flag = p_form_rec.enabled_flag )
189 OR ( ( l_db_rec.enabled_flag IS NULL )
190 AND ( p_form_rec.enabled_flag IS NULL ) ) )
191 )
192 THEN
193
194 return; -- all values match (record is current)
195
196 ELSE
197
198 -- Data in the form is stale. Prompt user to requery data.
199 --
200 FND_MESSAGE.set_name('FND','FORM_RECORD_CHANGED');
201 APP_EXCEPTION.raise_exception;
202
203 END IF;
204
205 EXCEPTION
206
207 WHEN OTHERS THEN
208 PO_MESSAGE_S.sql_error('PO_PRICE_DIFFERENTIALS_PKG.lock_row','000',sqlcode);
209 RAISE;
210
211 END lock_row;
212
213
214 -------------------------------------------------------------------------------
215 -------------------------------------------------------------------------------
216 --Start of Comments
217 --Name: delete_row
218 --Pre-reqs:
219 -- None.
220 --Modifies:
221 -- None.
222 --Locks:
223 -- None.
224 --Function:
225 -- Table handler for deletion of Price Differentials.
226 --Parameters:
227 -- None.
228 --Testing:
229 -- None.
230 --End of Comments
231 -------------------------------------------------------------------------------
232 -------------------------------------------------------------------------------
233 PROCEDURE delete_row
234 (
235 p_row_id IN ROWID
236 )
237 IS
238 BEGIN
239
240 DELETE FROM PO_PRICE_DIFFERENTIALS
241 WHERE rowid = p_row_id;
242
243 EXCEPTION
244
245 WHEN OTHERS THEN
246 PO_MESSAGE_S.sql_error('PO_PRICE_DIFFERENTIALS_PKG.delete_row','000',sqlcode);
247 RAISE;
248
249 END delete_row;
250
251 --<HTML Agreements R12 Start>
252 -------------------------------------------------------------------------------
253 --Start of Comments
254 --Name: del_level_specific_price_diff
255 --Pre-reqs:
256 -- None.
257 --Modifies:
258 -- PO_PRICE_DIFFERENTIALS.
259 --Locks:
260 -- None.
261 --Function:
262 -- Deletes all the price differentials for a given document level and document
263 -- level id combination.
264 --Parameters:
265 -- IN
266 -- p_doc_level
267 -- Document Level {LINE/SHIPMENT}
268 -- p_doc_level_id
269 -- Unique Identifier for the Document Line/Shipment
270 --Testing:
271 -- None.
272 --End of Comments
273 -------------------------------------------------------------------------------
274
275 PROCEDURE del_level_specific_price_diff( p_doc_level IN VARCHAR2
276 ,p_doc_level_id IN NUMBER)
277 IS
278 l_entity_type PO_PRICE_DIFFERENTIALS.entity_type%type := NULL;
279
280 d_module_name CONSTANT VARCHAR2(100) := 'DEL_LEVEL_SPECIFIC_PRICE_DIFF';
281 d_module_base CONSTANT VARCHAR2(70) := 'po.plsql.PO_PRICE_DIFFERENTIALS_PKG.del_level_specific_price_diff';
282 d_pos NUMBER := 0;
283 BEGIN
284 IF (PO_LOG.d_proc) THEN
285 PO_LOG.proc_begin(d_module_base, 'p_doc_level', p_doc_level); PO_LOG.proc_begin(d_module_base, 'p_doc_level_id', p_doc_level_id);
286 END IF;
287
288 --Get Price differentials entity type for the given Line
289 l_entity_type := PO_PRICE_DIFFERENTIALS_PVT.get_entity_type(
290 p_doc_level => p_doc_level
291 ,p_doc_level_id => p_doc_level_id);
292
293 IF(PO_LOG.d_stmt) THEN
294 PO_LOG.stmt(d_module_base, d_pos, 'l_entity_type', l_entity_type);
295 END IF;
296
297 d_pos :=10;
298 IF l_entity_type IS NULL THEN
299 RAISE PO_CORE_S.g_early_return_exc ;
300 END IF;
301
302 d_pos :=20;
303 PO_PRICE_DIFFERENTIALS_PVT.delete_price_differentials(
304 p_entity_type => l_entity_type
305 ,p_entity_id => p_doc_level_id);
306
307 d_pos :=30;
308
309 IF (PO_LOG.d_proc) THEN
310 PO_LOG.proc_end(d_module_base,'No of rows deleted',SQL%ROWCOUNT);
311 PO_LOG.proc_end(d_module_base);
312 END IF;
313
314 EXCEPTION
315 WHEN PO_CORE_S.g_early_return_exc THEN
316 IF PO_LOG.d_stmt THEN
317 PO_LOG.stmt(d_module_name,d_pos,'Early exit from ' || d_module_name);
318 END IF;
319 WHEN OTHERS THEN
320 FND_MSG_PUB.add_exc_msg(g_pkg_name, d_module_name|| ':'|| d_pos);
321 IF (PO_LOG.d_exc) THEN
322 PO_LOG.exc(d_module_base, d_pos, SQLCODE || SQLERRM);
323 END IF;
324 RAISE;
325 END del_level_specific_price_diff;
326 --<HTML Agreements R12 End>
327
328 END PO_PRICE_DIFFERENTIALS_PKG;