1 PACKAGE BODY PO_CLM_DOCUMENT_CONTROL_PVT AS
2 -- $Header: PO_CLM_DOCUMENT_CONTROL_PVT.plb 120.1.12020000.4 2013/05/02 11:22:46 vpeddi ship $
3
4 -- Forward declaration of local procedures
5 PROCEDURE validate_doc(
6 p_control_action IN VARCHAR2,
7 p_doc_level IN VARCHAR2,
8 p_doc_header_id IN NUMBER,
9 p_doc_line_id IN NUMBER,
10 p_doc_line_loc_id IN NUMBER,
11 p_doc_type IN VARCHAR2,
12 p_doc_subtype IN VARCHAR2,
13 p_closeout_status IN VARCHAR2,
14 x_return_status OUT NOCOPY VARCHAR2
15 );
16
17 -----------------------------------------------------------------------
18 --Start of Comments
19 --Name: process_doc_control_action
20 --Pre-reqs: org context is set
21 --Modifies: po_headers_all
22 --Locks:
23 -- po_headers_all
24 --Function:
25 -- Performs the document control action for the CLM Document passed.
26 --Parameters:
27 --IN:
28 --p_control_action
29 -- control action to be performed
30 --p_doc_level
31 -- document level at which control action is to be performed
32 --p_doc_header_id
33 -- document header id
34 --p_doc_line_id
35 -- document line id
36 --p_doc_line_loc_id
37 -- document line location id
38 --p_doc_type
39 -- document type code (PO, PA)
40 --p_doc_subtype
41 -- document subtype code (BLANKET, CONTRACT, STANDARD)
42 --p_reason
43 -- reason/justification entered by the user
44 --p_closeout_status
45 -- closeout status selected by the user for closeout
46 --IN OUT:
47 --OUT:
48 -- x_return_status
49 --End of Comments
50 ------------------------------------------------------------------------
51 PROCEDURE process_doc_control_action(
52 p_control_action IN VARCHAR2,
53 p_doc_level IN VARCHAR2,
54 p_doc_header_id IN NUMBER,
55 p_doc_line_id IN NUMBER,
56 p_doc_line_loc_id IN NUMBER,
57 p_doc_type IN VARCHAR2,
58 p_doc_subtype IN VARCHAR2,
59 p_reason IN VARCHAR2,
60 p_closeout_status IN VARCHAR2,
61 x_return_status OUT NOCOPY VARCHAR2
62 )
63 IS
64 d_pos NUMBER;
65 l_api_name CONSTANT VARCHAR2(30) := 'process_doc_control_action';
66 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_CLM_DOCUMENT_CONTROL_PVT.process_doc_control_action';
67 l_return_status VARCHAR2(1);
68 l_return_code VARCHAR2(25);
69 l_exc_msg VARCHAR2(2000);
70
71 BEGIN
72 IF (PO_LOG.d_proc) THEN
73 PO_LOG.proc_begin(d_module,'p_control_action',p_control_action);
74 PO_LOG.proc_begin(d_module,'p_doc_level',p_doc_level);
75 PO_LOG.proc_begin(d_module,'p_doc_header_id',p_doc_header_id);
76 PO_LOG.proc_begin(d_module,'p_doc_line_id',p_doc_line_id);
77 PO_LOG.proc_begin(d_module,'p_doc_line_loc_id',p_doc_line_loc_id);
78 PO_LOG.proc_begin(d_module,'p_doc_type',p_doc_type);
79 PO_LOG.proc_begin(d_module,'p_doc_subtype',p_doc_subtype);
80 PO_LOG.proc_begin(d_module,'p_reason',p_reason);
81 PO_LOG.proc_begin(d_module,'p_closeout_status',p_closeout_status);
82 END IF;
83
84 -- Validate document and raise exception if required
85 /*validate_doc(
86 p_control_action => p_control_action,
87 p_doc_level => p_doc_level,
88 p_doc_header_id => p_doc_header_id,
89 p_doc_line_id => p_doc_line_id,
90 p_doc_line_loc_id => p_doc_line_loc_id,
91 p_doc_type => p_doc_type,
92 p_doc_subtype => p_doc_subtype,
93 p_closeout_status => p_closeout_status,
94 x_return_status => l_return_status
95 );
96
97 d_pos := 10;
98 IF (PO_LOG.d_stmt) THEN
99 PO_LOG.stmt(d_module,d_pos,'l_return_status',l_return_status);
100 END IF;
101 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
103 END IF;*/
104
105 -- Lock the document
106 PO_DOCUMENT_LOCK_GRP.lock_document(
107 p_api_version => 1.0,
108 p_init_msg_list => FND_API.G_FALSE,
109 x_return_status => l_return_status,
110 p_document_type => p_doc_type,
111 p_document_id => p_doc_header_id
112 );
113
114 d_pos := 20;
115 IF (PO_LOG.d_stmt) THEN
116 PO_LOG.stmt(d_module,d_pos,'l_return_status',l_return_status);
117 END IF;
118 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
119 RAISE FND_API.G_EXC_ERROR;
120 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
121 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
122 END IF;
123
124 -- Validation successful
125
126 -- Update clm_closeout_status
127 IF p_control_action = g_action_clm_reopen
128 THEN
129 UPDATE po_headers_all
130 SET clm_closeout_status = NULL,
131 last_update_date = SYSDATE,
132 last_updated_by = FND_GLOBAL.USER_ID,
133 last_update_login = FND_GLOBAL.LOGIN_ID
134 WHERE po_header_id = p_doc_header_id;
135
136 d_pos := 25;
137
138 UPDATE PO_CLOSEOUT_DETAILS
139 SET closeout_date = NULL,
140 incloseout = NULL,
141 is_reopened = 'Y',
142 last_update_date = SYSDATE,
143 last_updated_by = FND_GLOBAL.USER_ID,
144 last_update_login = FND_GLOBAL.LOGIN_ID
145 WHERE reference_doc_id = p_doc_header_id
146 AND closeout_doc_type = po_closeout_pvt.g_CLOSEOUT_TYPE_INDIVIDUAL;
147
148 d_pos := 30;
149
150 --unfreeze the document
151 PO_DOCUMENT_ACTION_PVT.do_unfreeze
152 (
153 p_document_id => p_doc_header_id,
154 p_document_type => p_doc_type,
155 p_document_subtype => p_doc_subtype,
156 p_reason => p_reason,
157 x_return_status => l_return_status,
158 x_return_code => l_return_code,
159 x_exception_msg => l_exc_msg
160 );
161 END IF;
162
163 d_pos := 40;
164 IF (PO_LOG.d_stmt) THEN
165 PO_LOG.stmt(d_module,d_pos,'l_return_status',l_return_status);
166 PO_LOG.stmt(d_module,d_pos,'l_return_code',l_return_code);
167 END IF;
168
169 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
170 IF (l_return_code = 'STATE_FAILED') THEN
171 -- The document state was not valid for this control action
172 FND_MESSAGE.set_name('PO','PO_APP_APP_FAILED');
173 FND_MSG_PUB.add;
174 RAISE FND_API.G_EXC_ERROR;
175 END IF;
176 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
177 IF l_exc_msg IS NOT NULL AND PO_LOG.d_exc THEN
178 PO_LOG.exc(d_module,d_pos,l_exc_msg);
179 END IF;
180 RAISE FND_API.G_EXC_ERROR;
181 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
182 IF l_exc_msg IS NOT NULL AND PO_LOG.d_exc THEN
183 PO_LOG.exc(d_module,d_pos,l_exc_msg);
184 END IF;
185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
186 END IF;
187
188 x_return_status := FND_API.G_RET_STS_SUCCESS;
189
190 IF PO_LOG.d_proc THEN
191 PO_LOG.proc_end(d_module,'x_return_status',x_return_status);
192 END IF;
193
194 EXCEPTION
195 WHEN FND_API.G_EXC_ERROR THEN
196 x_return_status := FND_API.G_RET_STS_ERROR;
197 IF PO_LOG.d_exc THEN
198 PO_LOG.exc(d_module,d_pos,'Error in ' || d_module);
199 END IF;
200 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
202 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name||':'||d_pos);
203 IF PO_LOG.d_exc THEN
204 PO_LOG.exc(d_module,d_pos,'Unexpected Error in ' || d_module);
205 END IF;
206 WHEN OTHERS THEN
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
208 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name||':'||d_pos);
209 IF PO_LOG.d_exc THEN
210 PO_LOG.exc(d_module,d_pos,'Unhandled Exception in' || d_module);
211 END IF;
212
213 END process_doc_control_action;
214
215
216 -----------------------------------------------------------------------
217 --Start of Comments
218 --Name: validate_doc
219 --Pre-reqs:
220 --Modifies:
221 --Locks:
222 --Function:
223 -- Validates the document as per the document action passed.
224 --Parameters:
225 --IN:
226 --p_control_action
227 -- control action to be performed
228 --p_doc_level
229 -- document level at which control action is to be performed
230 --p_doc_header_id
231 -- document header id
232 --p_doc_line_id
233 -- document line id
234 --p_doc_line_loc_id
235 -- document line location id
236 --p_doc_type
237 -- document type code (PO, PA)
238 --p_doc_subtype
239 -- document subtype code (BLANKET, CONTRACT, STANDARD)
240 --p_closeout_status
241 -- closeout status selected by the user for closeout
242 --IN OUT:
243 --OUT:
244 -- x_return_status
245 --End of Comments
246 ------------------------------------------------------------------------
247 PROCEDURE validate_doc(
248 p_control_action IN VARCHAR2,
249 p_doc_level IN VARCHAR2,
250 p_doc_header_id IN NUMBER,
251 p_doc_line_id IN NUMBER,
252 p_doc_line_loc_id IN NUMBER,
253 p_doc_type IN VARCHAR2,
254 p_doc_subtype IN VARCHAR2,
255 p_closeout_status IN VARCHAR2,
256 x_return_status OUT NOCOPY VARCHAR2
257 )
258 IS
259 l_validation_error_flag VARCHAR(1) := 'N';
260 l_open_mod_exists_flag VARCHAR(1) := 'N';
261 l_open_award_exists_flag VARCHAR(1) := 'N';
262 l_idv_not_open_flag VARCHAR(1) := 'N';
263
264 BEGIN
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266
267 -- Clear the message stack
268 FND_MSG_PUB.initialize;
269
270 -- Validation 1: No open Modification exists for the Document, if control
271 -- action is closeout
272 IF p_control_action = g_action_clm_closeout
273 AND p_closeout_status = g_closed_out
274 THEN
275 BEGIN
276 SELECT 'Y'
277 INTO l_open_mod_exists_flag
278 FROM dual
279 WHERE EXISTS (SELECT 'open mod exists'
280 FROM po_drafts
281 WHERE document_id = p_doc_header_id
282 AND status NOT IN ('COMPLETED', 'WITHDRAWN')
283 -- IN ('DRAFT', 'IN PROCESS', 'REJECTED',
284 -- 'PRE-APPROVED', 'SUPPLIER SIGN')
285 );
286 EXCEPTION
287 WHEN NO_DATA_FOUND THEN
288 l_open_mod_exists_flag := 'N';
289 END;
290
291 IF l_open_mod_exists_flag = 'Y' THEN
292 FND_MESSAGE.set_name('PO','PO_OPEN_MOD_EXISTS_ERR');
293 FND_MSG_PUB.add;
294 l_validation_error_flag := 'Y';
295 END IF;
296
297 END IF;
298
299 -- Validation 2: No open Award exists for the IDV, if control action is closeout
300 IF p_control_action = g_action_clm_closeout
301 AND p_closeout_status = g_closed_out
302 AND p_doc_type = 'PA'
303 THEN
304 BEGIN
305 SELECT 'Y'
306 INTO l_open_award_exists_flag
307 FROM dual
308 WHERE EXISTS (SELECT 'open reference exists at header level'
309 FROM po_headers_all
310 WHERE clm_source_document_id = p_doc_header_id
311 AND NVL(frozen_flag,'N') = 'N'
312 )
313 OR EXISTS (SELECT 'open reference exists at line level'
314 FROM po_headers_all poh,
315 po_lines_all pol
316 WHERE poh.po_header_id = pol.po_header_id
317 AND (pol.from_header_id = p_doc_header_id
318 OR pol.contract_id = p_doc_header_id)
319 AND NVL(poh.frozen_flag,'N') = 'N'
320 );
321 EXCEPTION
322 WHEN NO_DATA_FOUND THEN
323 l_open_award_exists_flag := 'N';
324 END;
325
326 IF l_open_award_exists_flag = 'Y' THEN
327 FND_MESSAGE.set_name('PO','PO_OPEN_AWARD_EXISTS_ERR');
328 FND_MSG_PUB.add;
329 l_validation_error_flag := 'Y';
330 END IF;
331
332 END IF;
333
334 -- Validation 3: Source IDV is open if Award is being reopened
335 IF p_control_action = g_action_clm_reopen
336 AND p_doc_type = 'PO'
337 THEN
338 BEGIN
339 SELECT 'Y'
340 INTO l_idv_not_open_flag
341 FROM po_headers_all award
342 WHERE award.po_header_id = p_doc_header_id AND
343 ( EXISTS (SELECT 'source IDV at header level not open'
344 FROM po_headers_all idv
345 WHERE idv.po_header_id = award.clm_source_document_id
346 AND NVL(idv.frozen_flag,'N') = 'Y'
347 )
348 OR EXISTS (SELECT 'source IDV at line level not open'
349 FROM po_headers_all idv,
350 po_lines_all award_line
351 WHERE award_line.po_header_id = award.po_header_id
352 AND (award_line.from_header_id = idv.po_header_id
353 OR award_line.contract_id = idv.po_header_id)
354 AND NVL(idv.frozen_flag,'N') = 'Y'
355 )
356 );
357 EXCEPTION
358 WHEN NO_DATA_FOUND THEN
359 l_idv_not_open_flag := 'N';
360 END;
361
362 IF l_idv_not_open_flag = 'Y' THEN
363 FND_MESSAGE.set_name('PO','PO_IDV_NOT_OPEN_ERR');
364 FND_MSG_PUB.add;
365 l_validation_error_flag := 'Y';
366 END IF;
367
368 END IF;
369
370 -- Raise exception in case of any validation error
371 IF l_validation_error_flag = 'Y'
372 THEN
373 x_return_status := FND_API.G_RET_STS_ERROR;
374 RAISE FND_API.G_EXC_ERROR;
375 END IF;
376
377 x_return_status := FND_API.G_RET_STS_SUCCESS;
378 END validate_doc;
379
380 END PO_CLM_DOCUMENT_CONTROL_PVT;
381
[Home] [Help]