[Home] [Help]
PACKAGE BODY: APPS.PO_DOCUMENT_ACTION_HOLD
Source
1 PACKAGE BODY PO_DOCUMENT_ACTION_HOLD AS
2 -- $Header: POXDAHFB.pls 120.2 2005/09/07 15:10:04 spangulu noship $
3
4 -- Private package constants
5
6 g_pkg_name CONSTANT varchar2(30) := 'PO_DOCUMENT_ACTION_PAUSE';
7 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| g_pkg_name || '.';
8
9
10 PROCEDURE freeze_unfreeze(
11 p_action_ctl_rec IN OUT NOCOPY PO_DOCUMENT_ACTION_PVT.doc_action_call_rec_type
12 )
13 IS
14
15 d_progress NUMBER;
16 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_PAUSE.freeze_unfreeze';
17 d_msg VARCHAR2(200);
18
19 l_allowed_states PO_DOCUMENT_ACTION_UTIL.doc_state_rec_type;
20 l_doc_state_ok BOOLEAN;
21 l_ret_sts VARCHAR2(1);
22
23 l_user_id NUMBER;
24 l_login_id NUMBER;
25
26 BEGIN
27
28 d_progress := 0;
29 IF (PO_LOG.d_proc) THEN
30 PO_LOG.proc_begin(d_module);
31 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_id', p_action_ctl_rec.document_id);
32 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_type', p_action_ctl_rec.document_type);
33 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_subtype', p_action_ctl_rec.document_subtype);
34 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.action', p_action_ctl_rec.action);
35 END IF;
36
37 BEGIN
38
39 d_progress := 10;
40
41 l_user_id := FND_GLOBAL.USER_ID;
42 l_login_id := FND_GLOBAL.LOGIN_ID;
43
44 d_progress := 15;
45
46 l_allowed_states.auth_states(1) := PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED;
47 l_allowed_states.hold_flag := 'N';
48 l_allowed_states.closed_states(1) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED;
49 l_allowed_states.closed_states(2) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_OPEN;
50 l_allowed_states.fully_reserved_flag := NULL;
51
52 IF (p_action_ctl_rec.action = PO_DOCUMENT_ACTION_PVT.g_doc_action_FREEZE)
53 THEN
54
55 l_allowed_states.frozen_flag := 'N';
56
57 ELSIF (p_action_ctl_rec.action = PO_DOCUMENT_ACTION_PVT.g_doc_action_UNFREEZE)
58 THEN
59
60 l_allowed_states.frozen_flag := 'Y';
61
62 ELSE
63
64 d_progress := 20;
65 l_ret_sts := 'U';
66 d_msg := 'Invalid action';
67 RAISE PO_CORE_S.g_early_return_exc;
68
69 END IF; -- p_action_ctl_rec.action = ...
70
71 d_progress := 30;
72
73 l_doc_state_ok := PO_DOCUMENT_ACTION_UTIL.check_doc_state(
74 p_document_id => p_action_ctl_rec.document_id
75 , p_document_type => p_action_ctl_rec.document_type
76 , p_allowed_states => l_allowed_states
77 , x_return_status => l_ret_sts
78 );
79
80 IF (l_ret_sts <> 'S')
81 THEN
82
83 d_progress := 40;
84 d_msg := 'check_doc_state not successful';
85 RAISE PO_CORE_S.g_early_return_exc;
86
87 END IF;
88
89 d_progress := 50;
90
91 IF (NOT l_doc_state_ok) THEN
92
93 d_progress := 60;
94 p_action_ctl_rec.return_code := 'STATE_FAILED';
95 RAISE PO_CORE_S.g_early_return_exc;
96
97 END IF; -- if l_doc_state_ok
98
99 IF (p_action_ctl_rec.document_type IN ('PO', 'PA'))
100 THEN
101
102 d_progress := 70;
103
104 UPDATE po_headers poh
105 SET poh.frozen_flag = DECODE(p_action_ctl_rec.action,
106 PO_DOCUMENT_ACTION_PVT.g_doc_action_FREEZE, 'Y',
107 PO_DOCUMENT_ACTION_PVT.g_doc_action_UNFREEZE, 'N')
108 , poh.last_update_date = SYSDATE
109 , poh.last_updated_by = l_user_id
110 , poh.last_update_login = l_login_id
111 WHERE poh.po_header_id = p_action_ctl_rec.document_id;
112
113 ELSIF (p_action_ctl_rec.document_type = 'RELEASE')
114 THEN
115
116 d_progress := 80;
117
118 UPDATE po_releases por
119 SET por.frozen_flag = DECODE(p_action_ctl_rec.action,
120 PO_DOCUMENT_ACTION_PVT.g_doc_action_FREEZE, 'Y',
121 PO_DOCUMENT_ACTION_PVT.g_doc_action_UNFREEZE, 'N')
122 , por.last_update_date = SYSDATE
123 , por.last_updated_by = l_user_id
124 , por.last_update_login = l_login_id
125 WHERE por.po_release_id = p_action_ctl_rec.document_id;
126
127 ELSE
128
129 d_progress := 90;
130 l_ret_sts := 'U';
131 d_msg := 'Invalid document type';
132 RAISE PO_CORE_S.g_early_return_exc;
133
134 END IF; -- p_action_ctl_rec.document_type ...
135
136 d_progress := 100;
137
138 PO_DOCUMENT_ACTION_UTIL.handle_ctl_action_history(
139 p_document_id => p_action_ctl_rec.document_id
140 , p_document_type => p_action_ctl_rec.document_type
141 , p_document_subtype => p_action_ctl_rec.document_subtype
142 , p_line_id => p_action_ctl_rec.line_id
143 , p_shipment_id => p_action_ctl_rec.shipment_id
144 , p_action => p_action_ctl_rec.action
145 , p_reason => p_action_ctl_rec.note
146 , p_user_id => l_user_id
147 , p_login_id => l_login_id
148 , x_return_status => l_ret_sts
149 );
150
151 IF (l_ret_sts <> 'S')
152 THEN
153
154 d_progress := 110;
155 d_msg := 'handle_ctl_action_history not successful';
156 RAISE PO_CORE_S.g_early_return_exc;
157
158 END IF;
159
160 l_ret_sts := 'S';
161
162 EXCEPTION
163 WHEN PO_CORE_S.g_early_return_exc THEN
164 IF (l_ret_sts = 'U')
165 THEN
166 IF (PO_LOG.d_exc) THEN
167 PO_LOG.exc(d_module, d_progress, d_msg);
168 END IF;
169 END IF;
170
171 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
172
173 END;
174
175 p_action_ctl_rec.return_status := l_ret_sts;
176
177 IF (PO_LOG.d_proc) THEN
178 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_status', p_action_ctl_rec.return_status);
179 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_code', p_action_ctl_rec.return_code);
180 PO_LOG.proc_end(d_module);
181 END IF;
182
183 RETURN;
184
185 EXCEPTION
186 WHEN OTHERS THEN
187 p_action_ctl_rec.return_status := 'U';
188
189 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
190 IF (PO_LOG.d_exc) THEN
191 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
192 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_status', p_action_ctl_rec.return_status);
193 PO_LOG.proc_end(d_module);
194 END IF;
195
196 RETURN;
197
198 END freeze_unfreeze;
199
200 PROCEDURE hold_unhold(
201 p_action_ctl_rec IN OUT NOCOPY PO_DOCUMENT_ACTION_PVT.doc_action_call_rec_type
202 )
203 IS
204
205 d_progress NUMBER;
206 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_PAUSE.hold_unhold';
207 d_msg VARCHAR2(200);
208
209 l_allowed_states PO_DOCUMENT_ACTION_UTIL.doc_state_rec_type;
210 l_doc_state_ok BOOLEAN;
211 l_ret_sts VARCHAR2(1);
212
213 l_user_id NUMBER;
214 l_login_id NUMBER;
215
216 BEGIN
217
218 d_progress := 0;
219 IF (PO_LOG.d_proc) THEN
220 PO_LOG.proc_begin(d_module);
221 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_id', p_action_ctl_rec.document_id);
222 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_type', p_action_ctl_rec.document_type);
223 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_subtype', p_action_ctl_rec.document_subtype);
224 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.action', p_action_ctl_rec.action);
225 END IF;
226
227 BEGIN
228
229 d_progress := 10;
230
231 l_user_id := FND_GLOBAL.USER_ID;
232 l_login_id := FND_GLOBAL.LOGIN_ID;
233
234 d_progress := 15;
235
236 l_allowed_states.auth_states(1) := PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE;
237 l_allowed_states.auth_states(2) := PO_DOCUMENT_ACTION_PVT.g_doc_status_INPROCESS;
238 l_allowed_states.auth_states(3) := PO_DOCUMENT_ACTION_PVT.g_doc_status_REJECTED;
239 l_allowed_states.auth_states(4) := PO_DOCUMENT_ACTION_PVT.g_doc_status_RETURNED;
240 l_allowed_states.auth_states(5) := PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED;
241 l_allowed_states.auth_states(6) := PO_DOCUMENT_ACTION_PVT.g_doc_status_REAPPROVAL;
242 l_allowed_states.auth_states(7) := PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED;
243 l_allowed_states.closed_states(1) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED;
244 l_allowed_states.closed_states(2) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_OPEN;
245 l_allowed_states.fully_reserved_flag := NULL;
246 l_allowed_states.frozen_flag := NULL;
247
248 IF (p_action_ctl_rec.action = PO_DOCUMENT_ACTION_PVT.g_doc_action_HOLD)
249 THEN
250
251 l_allowed_states.hold_flag := 'N';
252
253 ELSIF (p_action_ctl_rec.action = PO_DOCUMENT_ACTION_PVT.g_doc_action_RELEASE_HOLD)
254 THEN
255
256 l_allowed_states.hold_flag := 'Y';
257
258 ELSE
259
260 d_progress := 20;
261 l_ret_sts := 'U';
262 d_msg := 'Invalid action';
263 RAISE PO_CORE_S.g_early_return_exc;
264
265 END IF; -- p_action_ctl_rec.action = ...
266
267 d_progress := 30;
268
269 l_doc_state_ok := PO_DOCUMENT_ACTION_UTIL.check_doc_state(
270 p_document_id => p_action_ctl_rec.document_id
271 , p_document_type => p_action_ctl_rec.document_type
272 , p_allowed_states => l_allowed_states
273 , x_return_status => l_ret_sts
274 );
275
276 IF (l_ret_sts <> 'S')
277 THEN
278
279 d_progress := 40;
280 d_msg := 'check_doc_state not successful';
281 RAISE PO_CORE_S.g_early_return_exc;
282
283 END IF;
284
285 d_progress := 50;
286
287 IF (NOT l_doc_state_ok) THEN
288
289 d_progress := 60;
290 p_action_ctl_rec.return_code := 'STATE_FAILED';
291 RAISE PO_CORE_S.g_early_return_exc;
292
293 END IF; -- if l_doc_state_ok
294
295 IF (p_action_ctl_rec.document_type IN ('PO', 'PA'))
296 THEN
297
298 d_progress := 70;
299
300 UPDATE po_headers poh
301 SET poh.user_hold_flag = DECODE(p_action_ctl_rec.action,
302 PO_DOCUMENT_ACTION_PVT.g_doc_action_HOLD, 'Y',
303 PO_DOCUMENT_ACTION_PVT.g_doc_action_RELEASE_HOLD, 'N')
304 , poh.approved_flag = DECODE(p_action_ctl_rec.action,
305 PO_DOCUMENT_ACTION_PVT.g_doc_action_HOLD,
306 DECODE(poh.approved_flag, 'Y', 'R', poh.approved_flag),
307 PO_DOCUMENT_ACTION_PVT.g_doc_action_RELEASE_HOLD, poh.approved_flag)
308 , poh.authorization_status = DECODE(p_action_ctl_rec.action,
309 PO_DOCUMENT_ACTION_PVT.g_doc_action_HOLD,
310 DECODE(poh.authorization_status,
311 'APPROVED', 'REQUIRES REAPPROVAL',
312 poh.authorization_status),
313 PO_DOCUMENT_ACTION_PVT.g_doc_action_RELEASE_HOLD, poh.authorization_status)
314 , poh.last_update_date = sysdate
315 , poh.last_updated_by = l_user_id
316 , poh.last_update_login = l_login_id
317 WHERE poh.po_header_id = p_action_ctl_rec.document_id;
318
319 IF (p_action_ctl_rec.document_type = 'PO')
320 THEN
321
322 d_progress := 80;
323
324 UPDATE po_line_locations poll
325 SET poll.approved_flag = DECODE(p_action_ctl_rec.action,
326 PO_DOCUMENT_ACTION_PVT.g_doc_action_HOLD,
327 DECODE(poll.approved_flag, 'Y', 'R', poll.approved_flag),
328 PO_DOCUMENT_ACTION_PVT.g_doc_action_RELEASE_HOLD, poll.approved_flag)
329 , poll.last_update_date = SYSDATE
330 , poll.last_updated_by = l_user_id
331 , poll.last_update_login = l_login_id
332 WHERE poll.po_header_id = p_action_ctl_rec.document_id
333 -- <Complex Work R12>: Include PREPAYMENT shipment_type
334 AND poll.shipment_type in ('STANDARD', 'PLANNED', 'PREPAYMENT');
335
336 END IF; -- p_action_ctl_rec.document_type = 'PO'
337
338 ELSIF (p_action_ctl_rec.document_type = 'RELEASE')
339 THEN
340
341 d_progress := 90;
342
343 UPDATE po_releases por
344 SET por.hold_flag = DECODE(p_action_ctl_rec.action,
345 PO_DOCUMENT_ACTION_PVT.g_doc_action_HOLD, 'Y',
346 PO_DOCUMENT_ACTION_PVT.g_doc_action_RELEASE_HOLD, 'N')
347 , por.approved_flag = DECODE(p_action_ctl_rec.action,
348 PO_DOCUMENT_ACTION_PVT.g_doc_action_HOLD,
349 DECODE(por.approved_flag, 'Y', 'R', por.approved_flag),
350 PO_DOCUMENT_ACTION_PVT.g_doc_action_RELEASE_HOLD, por.approved_flag)
351 , por.authorization_status = DECODE(p_action_ctl_rec.action,
352 PO_DOCUMENT_ACTION_PVT.g_doc_action_HOLD,
353 DECODE(por.authorization_status,
354 'APPROVED', 'REQUIRES REAPPROVAL',
355 por.authorization_status),
356 PO_DOCUMENT_ACTION_PVT.g_doc_action_RELEASE_HOLD, por.authorization_status)
357 , por.last_update_date = sysdate
358 , por.last_updated_by = l_user_id
359 , por.last_update_login = l_login_id
360 WHERE por.po_release_id = p_action_ctl_rec.document_id;
361
362 d_progress := 100;
363
364 UPDATE po_line_locations poll
365 SET poll.approved_flag = DECODE(p_action_ctl_rec.action,
366 PO_DOCUMENT_ACTION_PVT.g_doc_action_HOLD,
367 DECODE(poll.approved_flag, 'Y', 'R', poll.approved_flag),
368 PO_DOCUMENT_ACTION_PVT.g_doc_action_RELEASE_HOLD, poll.approved_flag)
369 , poll.last_update_date = SYSDATE
370 , poll.last_updated_by = l_user_id
371 , poll.last_update_login = l_login_id
372 WHERE poll.po_release_id = p_action_ctl_rec.document_id
373 AND poll.shipment_type in ('BLANKET', 'SCHEDULED');
374
375 ELSE
376
377 d_progress := 110;
378 l_ret_sts := 'U';
379 d_msg := 'Invalid document type';
380 RAISE PO_CORE_S.g_early_return_exc;
381
382 END IF; -- p_action_ctl_rec.document_type ...
383
384 d_progress := 120;
385
386 PO_DOCUMENT_ACTION_UTIL.handle_ctl_action_history(
387 p_document_id => p_action_ctl_rec.document_id
388 , p_document_type => p_action_ctl_rec.document_type
389 , p_document_subtype => p_action_ctl_rec.document_subtype
390 , p_line_id => p_action_ctl_rec.line_id
391 , p_shipment_id => p_action_ctl_rec.shipment_id
392 , p_action => p_action_ctl_rec.action
393 , p_reason => p_action_ctl_rec.note
394 , p_user_id => l_user_id
395 , p_login_id => l_login_id
396 , x_return_status => l_ret_sts
397 );
398
399 IF (l_ret_sts <> 'S')
400 THEN
401
402 d_progress := 130;
403 d_msg := 'handle_ctl_action_history not successful';
404 RAISE PO_CORE_S.g_early_return_exc;
405
406 END IF;
407
408 l_ret_sts := 'S';
409
410 EXCEPTION
411 WHEN PO_CORE_S.g_early_return_exc THEN
412 IF (l_ret_sts = 'U')
413 THEN
414 IF (PO_LOG.d_exc) THEN
415 PO_LOG.exc(d_module, d_progress, d_msg);
416 END IF;
417 END IF;
418
419 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
420
421 END;
422
423 p_action_ctl_rec.return_status := l_ret_sts;
424
425 IF (PO_LOG.d_proc) THEN
426 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_status', p_action_ctl_rec.return_status);
427 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_code', p_action_ctl_rec.return_code);
428 PO_LOG.proc_end(d_module);
429 END IF;
430
431 RETURN;
432
433 EXCEPTION
434 WHEN OTHERS THEN
435 p_action_ctl_rec.return_status := 'U';
436
437 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
438 IF (PO_LOG.d_exc) THEN
439 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
440 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_status', p_action_ctl_rec.return_status);
441 PO_LOG.proc_end(d_module);
442 END IF;
443
444 RETURN;
445
446
447 END hold_unhold;
448
449
450
451
452 END PO_DOCUMENT_ACTION_HOLD;