1 PACKAGE BODY PO_DOCUMENT_REVISION_GRP AS
2 /* $Header: POXDOCRB.pls 120.8.12020000.3 2013/02/10 17:27:01 vegajula ship $ */
3
4 -- Global Variables
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_DOCUMENT_REVISION_GRP';
6
7 c_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| G_PKG_NAME || '.';
8
9 -- Read the profile option that enables/disables the debug log
10 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
11 --
12
13 /* ----------------------------------------------------------------------- */
14 /* */
15 /* Private Function Definition */
16 /* */
17 /* ----------------------------------------------------------------------- */
18
19 FUNCTION PO_Archive_Check(p_doc_id IN NUMBER) return boolean;
20
21 FUNCTION Release_Archive_Check(p_doc_id IN NUMBER) return boolean;
22
23 PROCEDURE Compare_Table(
24 p_doc_id IN NUMBER,
25 p_doc_subtype IN VARCHAR2,
26 p_type IN VARCHAR2,
27 p_element IN VARCHAR2,
28 p_line_id IN NUMBER, --<CancelPO FPJ>
29 p_line_location_id IN NUMBER, --<CancelPO FPJ>
30 p_chk_cancel_flag IN VARCHAR2, --<CancelPO FPJ>
31 x_different IN OUT NOCOPY Varchar2);
32
33 /* ----------------------------------------------------------------------- */
34
35
36
37 PROCEDURE Check_New_Revision (p_api_version IN NUMBER,
38 p_doc_type IN Varchar2,
39 p_doc_subtype IN Varchar2,
40 p_doc_id IN Number,
41 p_table_name IN Varchar2,
42 x_return_status OUT NOCOPY VARCHAR2,
43 x_doc_revision_num IN OUT NOCOPY Number,
44 x_message IN OUT NOCOPY VARCHAR2) IS
45 l_need_new_revision boolean := FALSE;
46 l_progress varchar2(3);
47 l_api_version CONSTANT NUMBER := 1.0;
48 l_api_name CONSTANT VARCHAR2(30) := 'Check_New_Revision';
49 l_different Varchar2(1); --<CancelPO FPJ>
50 -- Bug 3616320 START
51 l_doc_type VARCHAR2(20);
52 l_keep_summary VARCHAR2(1);
53 l_msg_count NUMBER;
54 l_msg_data VARCHAR2(2000);
55 l_return_status VARCHAR2(1);
56 -- Bug 3616320 END
57
58 begin
59
60 l_progress := '000';
61 -- Standard call to check for call compatibility
62
63 IF (NOT FND_API.Compatible_API_Call(l_api_version
64 ,p_api_version
65 ,l_api_name
66 ,G_PKG_NAME))
67 THEN
68 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
69 END IF;
70
71 -- Initialize API return status to success
72
73 x_return_status := FND_API.G_RET_STS_SUCCESS;
74
75 /* Check the required fields */
76 If ((p_doc_type is NULL) OR(p_doc_subtype is NULL) OR
77 (p_doc_id IS NULL)) THEN
78 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
79 token1 => 'FILE',
80 value1 => 'PO_DOCUMENT_REVISION_GRP',
81 token2 => 'ERR_NUMBER',
82 value2 => '010',
83 token3 => 'SUBROUTINE',
84 value3 => 'Check_New_Revision()');
85
86 end if; /*p_doc_type is NULL) OR(p_doc_subtype is NULL */
87
88 l_progress := '020';
89 /* Check if a valid table value was given */
90 if ((p_table_name <> 'ALL') AND (p_table_name <> 'HEADER') AND
91 (p_table_name <> 'LINES') AND (p_table_name <> 'SHIPMENTS') AND
92 (p_table_name <> 'PO_LINE_PRICE_DIFF') AND (p_table_name <> 'PO_PB_PRICE_DIFF') AND -- SERVICES FPJ
93 (p_table_name <> 'DISTRIBUTIONS')) THEN
94 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
95 token1 => 'FILE',
96 value1 => 'PO_DOCUMENT_REVISION_GRP',
97 token2 => 'ERR_NUMBER',
98 value2 => '030',
99 token3 => 'SUBROUTINE',
100 value3 => 'Check_New_Revision()');
101
102 end if; /*(p_table_name <> 'ALL') AND ((p_table_name <> 'HEADER') */
103
104 l_progress := '040';
105 if ((p_doc_type = 'PO') OR (p_doc_type = 'PA')) THEN
106 l_need_new_revision :=
107 Check_PO_PA_Revision(
108 p_doc_id => p_doc_id,
109 p_doc_subtype => p_doc_subtype,
110 p_doc_type => p_doc_type,
111 p_table_name => p_table_name,
112 p_line_id => NULL, --<CancelPO FPJ>
113 p_line_location_id => NULL, --<CancelPO FPJ>
114 p_chk_cancel_flag => 'Y', --<CancelPO FPJ>
115 x_different => l_different); --<CancelPO FPJ>
116 elsif ((p_doc_type = 'RELEASE')) THEN
117 l_need_new_revision :=
118 Check_Release_Revision(
119 p_doc_id => p_doc_id,
120 p_doc_subtype => p_doc_subtype,
121 p_doc_type => p_doc_type,
122 p_table_name => p_table_name,
123 p_line_location_id => NULL, --<CancelPO FPJ>
124 p_chk_cancel_flag => 'Y', --<CancelPO FPJ>
125 x_different => l_different); --<CancelPO FPJ>
126 else
127 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
128 token1 => 'FILE',
129 value1 => 'PO_DOCUMENT_REVISION_GRP',
130 token2 => 'ERR_NUMBER',
131 value2 => '040',
132 token3 => 'SUBROUTINE',
133 value3 => 'Check_New_Revision()');
134
135 end if; /* (p_doc_type = 'PO') OR (p_doc_type = 'PA') */
136
137 if (l_need_new_revision) then
138 --DISPLAY THE MESSAGE IN THE PLDS
139 x_doc_revision_num := x_doc_revision_num + 1;
140 x_message:= 'PO_REV_POXCH_NEW_REV';
141 --<DBI Req Fulfillment 11.5.11 Start >
142 if ((p_doc_type = 'PO') OR (p_doc_type = 'PA')) THEN
143 update po_headers
144 set submit_date = NULL
145 where po_header_id = p_doc_id;
146
147 elsif ((p_doc_type = 'RELEASE')) THEN
148 update po_releases
149 set submit_date = NULL
150 where po_release_id = p_doc_id;
151 end if;
152 --<DBI Req Fulfillment 11.5.11 End >
153
154 -- Bug 3616320 START
155 -- Only clear amendment for PO/PA
156 IF ((p_doc_type = 'PO') OR (p_doc_type = 'PA')) THEN
157
158 -- p_doc_type is always passed as 'PO' regardless of the subtype
159 -- Should set doc_type to PA for Blanket and Contract
160 IF (p_doc_subtype IN ('BLANKET', 'CONTRACT')) THEN
161 l_doc_type := 'PA';
162 ELSE
163 l_doc_type := 'PO';
164 END IF; /*IF (p_doc_subtype IN ('BLANKET', 'CONTRACT'))*/
165
166 -- Call Clear_Amendment at the time of creating new revision.
167 -- o If the pervious version is approved or require-reapproval
168 -- the call OKC_TERMS_VERSION_GRP.CLEAR_AMENDMENT() with
169 -- p_keey_summary = 'N'
170 -- o Else call OKC_TERMS_VERSION_GRP.CLEAR_AMENDMENT() with
171 -- p_keey_summary = 'Y'
172 BEGIN
173 SELECT 'N'
174 INTO l_keep_summary
175 FROM dual
176 WHERE exists (SELECT 'approved document'
177 FROM po_headers
178 WHERE po_header_id = p_doc_id
179 AND NVL(approved_flag, 'N') IN ('R', 'Y'));
180 EXCEPTION
181 WHEN NO_DATA_FOUND THEN
182 l_keep_summary := 'Y';
183 END;
184
185
186 IF g_fnd_debug = 'Y' THEN
187 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
188 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
189 || l_progress, 'Call OKC_TERMS_VERSION_GRP.clear_amendment '
190 || ' p_doc_id:' || p_doc_id
191 || ' p_doc_type:' || (l_doc_type ||'_'||p_doc_subtype)
192 || ' p_keep_summary:' || l_keep_summary);
193 END IF;
194 END IF;
195
196 -- Calls Contracts API to clear Amendment related columns
197 OKC_TERMS_VERSION_GRP.clear_amendment(
198 p_api_version => 1.0,
199 p_init_msg_list => FND_API.G_FALSE,
200 p_commit => FND_API.G_FALSE,
201 x_return_status => l_return_status,
202 x_msg_data => l_msg_data,
203 x_msg_count => l_msg_count,
204 p_doc_type => (l_doc_type ||'_'||p_doc_subtype),
205 p_doc_id => p_doc_id,
206 p_keep_summary => l_keep_summary);
207
208 END IF; /*IF ((p_doc_type = 'PO') OR (p_doc_type = 'PA'))*/
209 -- Bug 3616320 END
210 end if;
211
212 EXCEPTION
213 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
215
216 when others then
217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
218 PO_MESSAGE_S.SQL_ERROR(routine => 'Check_New_Revision',
219 location => l_progress,
220 error_code => SQLCODE);
221
222 END Check_New_Revision;
223
224 FUNCTION Check_PO_PA_Revision (
225 p_doc_type IN Varchar2,
226 p_doc_subtype IN Varchar2,
227 p_doc_id IN Number,
228 p_table_name IN Varchar2,
229 p_line_id IN NUMBER, --<CancelPO FPJ>
230 p_line_location_id IN NUMBER, --<CancelPO FPJ>
231 p_chk_cancel_flag IN VARCHAR2, --<CancelPO FPJ>
232 x_different OUT NOCOPY Varchar2 --<CancelPO FPJ>
233 ) RETURN BOOLEAN IS
234 l_need_to_check boolean;
235 l_progress varchar2(3);
236 begin
237
238 l_progress := '000';
239 if ((p_doc_subtype <> 'STANDARD') AND (p_doc_subtype <> 'PLANNED') AND
240 (p_doc_subtype <> 'BLANKET') AND (p_doc_subtype <> 'CONTRACT')) THEN
241 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
242 token1 => 'FILE',
243 value1 => 'PO_DOCUMENT_REVISION_GRP',
244 token2 => 'ERR_NUMBER',
245 value2 => '010',
246 token3 => 'SUBROUTINE',
247 value3 => 'Check_PO_PA_Revision()');
248 return FALSE;
249
250 end if; /*(p_doc_subtype<>'STANDARD') AND (p_doc_subtype<>'PLANNED') */
251 /* Check whether the header revision is same as the archived
252 * Revision. If it is different then dont continue.
253 */
254 l_progress := '020';
255
256 IF p_chk_cancel_flag = 'Y' THEN --<CancelPO FPJ>
257 --IF Check Cancel Flag is N, then compare all invidual attributes except cancel flag
258
259 l_need_to_check := PO_Archive_Check(p_doc_id);
260
261 if (not l_need_to_check) then
262 --If current revision is higher than archived, Header is different
263 x_different := 'Y'; --<CancelPO FPJ>
264 return FALSE;
265 end if;
266
267 END IF; -- End of IF p_chk_cancel_flag = 'Y' --<CancelPO FPJ>
268
269 l_progress := '030';
270 /* All PO/PA types need to have their header checked
271 * when p_table_name in ('ALL', 'HEADER').
272 */
273 if ((p_table_name ='ALL') OR (p_table_name = 'HEADER')) THEN
274 compare_table(p_doc_id => p_doc_id,
275 p_doc_subtype => p_doc_subtype,
276 p_type => 'PORCH_PO',
277 p_element => 'PORCH_HEADER',
278 p_line_id => p_line_id, --<CancelPO FPJ>
279 p_line_location_id => p_line_location_id, --<CancelPO FPJ>
280 p_chk_cancel_flag => p_chk_cancel_flag, --<CancelPO FPJ>
281 x_different => x_different);
282
283 if (x_different = 'Y') then
284 return TRUE;
285 end if;
286
287 --< Shared Proc FPJ Start >
288 IF (p_doc_subtype IN ('BLANKET', 'CONTRACT')) AND
289 (PO_GA_PVT.is_global_agreement(p_doc_id))
290 THEN
291 compare_table(p_doc_id => p_doc_id,
292 p_doc_subtype => p_doc_subtype,
293 p_type => 'PORCH_PO',
294 p_element => 'PORCH_GA_ORG_ASSIGN',
295 p_line_id => p_line_id, --<CancelPO FPJ>
296 p_line_location_id => p_line_location_id, --<CancelPO FPJ>
297 p_chk_cancel_flag => p_chk_cancel_flag, --<CancelPO FPJ>
298 x_different => x_different);
299
300 IF (x_different = 'Y') THEN
301 RETURN TRUE;
302 END IF;
303
304 END IF; --< if PA and global agreement >
305 --< Shared Proc FPJ End >
306
307 END IF; /*(p_table_name ='ALL' OR (p_table_name = 'HEADER') */
308
309
310 l_progress := '040';
311
312 if ((p_table_name ='ALL') OR (p_table_name = 'LINES')) THEN
313 if ((p_doc_subtype = 'STANDARD') OR
314 (p_doc_subtype = 'PLANNED') OR
315 (p_doc_subtype = 'BLANKET')) THEN
316 compare_table(p_doc_id => p_doc_id,
317 p_doc_subtype => p_doc_subtype,
318 p_type => 'PORCH_PO',
319 p_element => 'PORCH_LINES',
320 p_line_id => p_line_id, --<CancelPO FPJ>
321 p_line_location_id => p_line_location_id, --<CancelPO FPJ>
322 p_chk_cancel_flag => p_chk_cancel_flag, --<CancelPO FPJ>
323 x_different => x_different);
324 end if; /*p_doc_subtype = 'STANDARD') OR ...*/
325
326 if (x_different = 'Y') then
327 return TRUE;
328 end if;
329 END IF; /*(p_table_name ='ALL' OR (p_table_name = 'LINES')*/
330
331 /* Subtypes STANDARD and PLANNED and BLANKET need to have
332 * their shipments checked if p_table_name in ('ALL', 'SHIPMENTS').
333 */
334
335 l_progress := '050';
336 if ((p_table_name ='ALL') OR (p_table_name = 'SHIPMENTS')) THEN
337 if ((p_doc_subtype = 'STANDARD') OR
338 (p_doc_subtype = 'PLANNED')) THEN
339 compare_table(p_doc_id => p_doc_id,
340 p_doc_subtype => p_doc_subtype,
341 p_type => 'PORCH_PO',
342 p_element => 'PORCH_SHIPMENTS',
343 p_line_id => p_line_id, --<CancelPO FPJ>
344 p_line_location_id => p_line_location_id, --<CancelPO FPJ>
345 p_chk_cancel_flag => p_chk_cancel_flag, --<CancelPO FPJ>
346 x_different => x_different);
347 if (x_different = 'Y') then
348 return TRUE;
349 end if;
350
351 elsif (p_doc_subtype = 'BLANKET') THEN
352 compare_table(p_doc_id => p_doc_id,
353 p_doc_subtype => p_doc_subtype,
354 p_type => 'PORCH_PO',
355 p_element => 'PORCH_PBREAK',
356 p_line_id => p_line_id, --<CancelPO FPJ>
357 p_line_location_id => p_line_location_id, --<CancelPO FPJ>
358 p_chk_cancel_flag => p_chk_cancel_flag, --<CancelPO FPJ>
359 x_different => x_different);
360 if (x_different = 'Y') then
361 return TRUE;
362 end if;
363 end if; /*p_doc_subtype = 'STANDARD') OR ...*/
364
365 END IF; /*(p_table_name ='ALL' OR (p_table_name = 'SHIPMENTS')*/
366
367 -- SERVICES FPJ Start
368 -- Check the price differentials table for standard PO's and GA's
369
370 l_progress := '060';
371 IF p_table_name = 'PO_LINE_PRICE_DIFF'
372 THEN
373 IF (p_doc_subtype = 'STANDARD') OR
374 (PO_GA_PVT.is_global_agreement(p_doc_id))
375 THEN
376 compare_table(p_doc_id => p_doc_id,
377 p_doc_subtype => p_doc_subtype,
378 p_type => 'PORCH_PO',
379 p_element => 'PORCH_LINE_PRICE_DIFF',
380 p_line_id => p_line_id, --<CancelPO FPJ>
381 p_line_location_id => p_line_location_id, --<CancelPO FPJ>
382 p_chk_cancel_flag => p_chk_cancel_flag, --<CancelPO FPJ>
383 x_different => x_different);
384 END IF;
385
386 IF (x_different = 'Y') THEN
387 RETURN TRUE;
388 END IF;
389
390 END IF;
391
392 l_progress := '070';
393 IF p_table_name = 'PO_PB_PRICE_DIFF'
394 THEN
395 IF (PO_GA_PVT.is_global_agreement(p_doc_id))
396 THEN
397 compare_table(p_doc_id => p_doc_id,
398 p_doc_subtype => p_doc_subtype,
399 p_type => 'PORCH_PO',
400 p_element => 'PORCH_PB_PRICE_DIFF',
401 p_line_id => p_line_id, --<CancelPO FPJ>
402 p_line_location_id => p_line_location_id, --<CancelPO FPJ>
403 p_chk_cancel_flag => p_chk_cancel_flag, --<CancelPO FPJ>
404 x_different => x_different);
405 END IF;
406
407 IF (x_different = 'Y') THEN
408 RETURN TRUE;
409 END IF;
410
411 END IF;
412 -- SERVICES FPJ End
413
414 l_progress := '080';
415 if ((p_table_name ='ALL') OR (p_table_name = 'DISTRIBUTIONS')) THEN
416 if ((p_doc_subtype = 'STANDARD') OR
417 (p_doc_subtype = 'PLANNED')) THEN
418 compare_table(p_doc_id => p_doc_id,
419 p_doc_subtype => p_doc_subtype,
420 p_type => 'PORCH_PO',
421 p_element => 'PORCH_DISTRIBUTIONS',
422 p_line_id => p_line_id, --<CancelPO FPJ>
423 p_line_location_id => p_line_location_id, --<CancelPO FPJ>
424 p_chk_cancel_flag => p_chk_cancel_flag, --<CancelPO FPJ>
425 x_different => x_different);
426 end if; /*p_doc_subtype = 'STANDARD') OR ...*/
427
428 if (x_different = 'Y') then
429 return TRUE;
430 end if;
431 END IF; /*(p_table_name ='ALL' OR (p_table_name = 'DISTRIBUTIONS')*/
432
433 return FALSE;
434 EXCEPTION
435 when others then
436 PO_MESSAGE_S.SQL_ERROR(routine => 'Check_PO_PA_Revision',
437 location => l_progress,
438 error_code => SQLCODE);
439
440 return(FALSE);
441 END Check_PO_PA_Revision;
442
443 FUNCTION Check_Release_Revision (
444 p_doc_type IN Varchar2,
445 p_doc_subtype IN Varchar2,
446 p_doc_id IN Number,
447 p_table_name IN Varchar2,
448 p_line_location_id IN NUMBER, --<CancelPO FPJ>
449 p_chk_cancel_flag IN VARCHAR2, --<CancelPO FPJ>
450 x_different OUT NOCOPY Varchar2) --<CancelPO FPJ>
451 RETURN BOOLEAN IS
452 l_need_to_check boolean;
453 l_progress varchar2(3);
454 begin
455
456 l_progress := '000';
457 if ((p_doc_subtype <> 'SCHEDULED')
458 AND (p_doc_subtype <> 'BLANKET')) THEN
459 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
460 token1 => 'FILE',
461 value1 => 'PO_DOCUMENT_REVISION_GRP',
462 token2 => 'ERR_NUMBER',
463 value2 => '010',
464 token3 => 'SUBROUTINE',
465 value3 => 'Check_Release_Revision()');
466 return FALSE;
467
468 end if;
469
470 IF p_chk_cancel_flag = 'Y' THEN --<CancelPO FPJ>
471 --IF Check Cancel Flag is N, then compare all invidual attributes except cancel flag
472
473 l_need_to_check := Release_Archive_Check(p_doc_id);
474
475 if (not l_need_to_check) then
476 --If current revision is higher than archived, Header is different
477 x_different := 'Y'; --<CancelPO FPJ>
478 return FALSE;
479 end if;
480
481 END IF; -- End of IF p_chk_cancel_flag = 'Y' --<CancelPO FPJ>
482
483 /* All RELEASE types need to have their header checked
484 if p_table_name in ('ALL', 'HEADER').
485 */
486 l_progress := '010';
487 if ((p_table_name ='ALL') OR (p_table_name = 'HEADER')) THEN
488 compare_table(p_doc_id => p_doc_id,
489 p_doc_subtype => p_doc_subtype,
490 p_type => 'PORCH_RELEASE',
491 p_element => 'PORCH_HEADER',
492 p_line_id => NULL, --<CancelPO FPJ>
493 p_line_location_id => p_line_location_id, --<CancelPO FPJ>
494 p_chk_cancel_flag => p_chk_cancel_flag, --<CancelPO FPJ>
495 x_different => x_different);
496
497 if (x_different = 'Y') then
498 return TRUE;
499 end if;
500 END IF; /*(p_table_name ='ALL' OR (p_table_name = 'HEADER') */
501
502 /* All RELEASE types need to have their shipments checked.
503 if p_table_name in ('ALL', 'SHIPMENTS').
504 */
505 l_progress := '020';
506
507 if ((p_table_name ='ALL') OR (p_table_name = 'SHIPMENTS')) THEN
508 compare_table(p_doc_id => p_doc_id,
509 p_doc_subtype => p_doc_subtype,
510 p_type => 'PORCH_RELEASE',
511 p_element => 'PORCH_SHIPMENTS',
512 p_line_id => NULL, --<CancelPO FPJ>
513 p_line_location_id => p_line_location_id, --<CancelPO FPJ>
514 p_chk_cancel_flag => p_chk_cancel_flag, --<CancelPO FPJ>
515 x_different => x_different);
516
517 if (x_different = 'Y') then
518 return TRUE;
519 end if;
520 END IF; /*(p_table_name ='ALL' OR (p_table_name = 'SHIPMENTS') */
521
522
523 /* All RELEASE types need to have their distributions checked
524 if p_table_name in ('ALL', 'DISTRIBUTIONS').
525 */
526 l_progress := '030';
527
528 if ((p_table_name ='ALL') OR (p_table_name = 'DISTRIBUTIONS')) THEN
529 compare_table(p_doc_id => p_doc_id,
530 p_doc_subtype => p_doc_subtype,
531 p_type => 'PORCH_RELEASE',
532 p_element => 'PORCH_DISTRIBUTIONS',
533 p_line_id => NULL, --<CancelPO FPJ>
534 p_line_location_id => p_line_location_id, --<CancelPO FPJ>
535 p_chk_cancel_flag => p_chk_cancel_flag, --<CancelPO FPJ>
536 x_different => x_different);
537
538 if (x_different = 'Y') then
539 return TRUE;
540 end if;
541 END IF; /*(p_table_name ='ALL' OR (p_table_name = 'DISTRIBUTIONS') */
542 return FALSE;
543 EXCEPTION
544 when others then
545 PO_MESSAGE_S.SQL_ERROR(routine => 'Check_Release_Revision',
546 location => l_progress,
547 error_code => SQLCODE);
548
549 return(FALSE);
550 END Check_Release_Revision;
551
552 /*******************************************************************
553 FUNCTION NAME: PO_Archive_Check
554
555 DESCRIPTION : Called from Check_PO_PA_Revision function.
556
557 Algr: Selects the revision number of the po_header and the latest
558 archived version (when it exists) and compares them.
559 If current revision_num = latest revision_num
560 return FALSE
561 Else
562 return TRUE
563 In case of a sql error need_to_check will be FALSE
564
565 Referenced by :
566 parameters : p_doc_Id IN NUMBER - Document Id.
567
568 CHANGE History: Created 30-Sep-2002 pparthas
569 *******************************************************************/
570
571 FUNCTION PO_Archive_Check(p_doc_id IN NUMBER)
572 RETURN BOOLEAN IS
573 l_revision_num po_headers_all.revision_num%type;
574 l_archived_number po_headers_archive.revision_num%type;
575 l_need_to_check boolean;
576 l_progress varchar2(3);
577 begin
578 l_progress := '000';
579 SELECT POH.revision_num, nvl(POHA.revision_num, -1)
580 into l_revision_num, l_archived_number
581 FROM PO_HEADERS POH,
582 PO_HEADERS_ARCHIVE POHA
583 WHERE POH.po_header_id = p_doc_id
584 AND POH.po_header_id = POHA.po_header_id (+)
585 AND POHA.latest_external_flag (+) = 'Y';
586
587 if (l_revision_num <> l_archived_number) then
588 l_need_to_check := FALSE;
589 else
590 l_need_to_check := TRUE;
591 end if;
592 return l_need_to_check;
593
594 EXCEPTION
595 when others then
596 PO_MESSAGE_S.SQL_ERROR(routine => 'PO_Archive_Check',
597 location => l_progress,
598 error_code => SQLCODE);
599
600 return(FALSE);
601 END PO_Archive_Check;
602
603 /*******************************************************************
604 FUNCTION NAME: Release_Archive_Check
605
606 DESCRIPTION : Called from Check_PO_PA_Revision function.
607
608 Algr: Selects the revision number of the po_header and the latest
609 archived version (when it exists) and compares them.
610 If current revision_num <> latest revision_num
611 return FALSE
612 Else
613 return TRUE
614 In case of a sql error need_to_check will be FALSE
615
616 Referenced by :
617 parameters : p_doc_Id IN NUMBER - Document Id.
618
619 CHANGE History: Created 30-Sep-2002 pparthas
620 *******************************************************************/
621
622 FUNCTION Release_Archive_Check(p_doc_id IN NUMBER)
623 RETURN BOOLEAN IS
624 l_revision_num po_headers_all.revision_num%type;
625 l_archived_number po_headers_archive.revision_num%type;
626 l_need_to_check boolean;
627 l_progress varchar2(3);
628 begin
629
630 l_progress := '000';
631 SELECT POR.revision_num, nvl(PORA.revision_num, -1)
632 INTO l_revision_num, l_archived_number
633 FROM PO_RELEASES POR,
634 PO_RELEASES_ARCHIVE PORA
635 WHERE POR.po_release_id = p_doc_id
636 AND POR.po_release_id = PORA.po_release_id (+)
637 AND PORA.latest_external_flag (+) = 'Y';
638
639 if (l_revision_num <> l_archived_number) then
640 l_need_to_check := FALSE;
641 else
642 l_need_to_check := TRUE;
643 end if;
644 return l_need_to_check;
645
646 EXCEPTION
647 when others then
648 PO_MESSAGE_S.SQL_ERROR(routine => 'Release_Archive_Check',
649 location => l_progress,
650 error_code => SQLCODE);
651
652 return(FALSE);
653 END Release_Archive_Check;
654
655
656 /*******************************************************************
657 PROCEDURE NAME: Compare_Table
658
659 DESCRIPTION : Called from Check_PO_PA_Revision function.
660
661 Algr: Compare the requested table with the latest archived version
662 If they are different then
663 return x_different = Y
664 Else
665 return x_different = N
666
667 Referenced by :
668 parameters : p_doc_Id IN NUMBER - Document Id.
669 p_doc_subtype IN VARCHAR2,
670 p_type IN VARCHAR2, -- PO or RELEASE
671 p_element IN VARCHAR2) -- Header or Lines etc
672 x_different IN VARCHAR2)
673
674 CHANGE History: Created 30-Sep-2002 pparthas
675 *******************************************************************/
676
677 PROCEDURE Compare_Table(
678 p_doc_id IN NUMBER,
679 p_doc_subtype IN VARCHAR2,
680 p_type IN VARCHAR2,
681 p_element IN VARCHAR2,
682 p_line_id IN NUMBER, --<CancelPO FPJ>
683 p_line_location_id IN NUMBER, --<CancelPO FPJ>
684 p_chk_cancel_flag IN VARCHAR2, --<CancelPO FPJ>
685 x_different IN OUT NOCOPY Varchar2) IS
686
687 l_progress varchar2(3);
688 l_accepted_flag varchar2(1); -- Bug 3388218
689
690 begin
691
692 l_progress := '000';
693 if (p_type = 'PORCH_PO') then
694
695 l_progress := '010';
696 if (p_element = 'PORCH_HEADER') then
697
698 /* Start Bug# 5943064, We need to consider 3 cases. I Supplier portal
699 if the PO is 'Accepted'/'Rejected' then we set the acceptance_required_flag
700 to 'N' so that we dont Enter any more acceptances.the accepted_flag can be 'Y'/'N'.
701 But when creating the document revision we were not considering that
702 accepted_flag can be 'N' when rejected and we should cause a document revision
703 when this change happens and these documents can be cancelled.
704 So we are now checking if the document is both Accepted and Rejected cases and
705 since normal Accetances also have 'N' we differentiate a 'Rejected' case
706 by also looking at the acceptance_required_flag in the po_headers table.
707 Doing the same for the acceptance_due_date. l_accepted_flag='X' will
708 represent lines which dont have acceptance Entered. */
709
710 -- Bug 3388218 Start
711 Begin
712 Select pav.accepted_flag
713 into l_accepted_flag
714 from po_acceptances_v pav,
715 po_headers poh
716 where poh.po_header_id=p_doc_id
717 and poh.po_header_id=pav.po_header_id
718 and pav.revision_num= poh.revision_num
719 and poh.acceptance_required_flag='N'
720 and rownum=1;
721 -- and pav.accepted_flag='Y';
722 Exception
723 when others then
724 l_accepted_flag:='X';
725 End;
726 --End Bug# 5943064
727 -- Bug 3388218 End
728 /*Bug5154626: cancel action on the PO's in approved state errors out on which
729 Mass update buyer program is run before to update buyer name.
730 Hence donot use the agent_id comparision for cancel flow*/
731
732 Select 'Y'
733 INTO x_different
734 from sys.dual
735 where exists(
736 select null
737 FROM PO_HEADERS POH,
738 PO_HEADERS_ARCHIVE POHA
739 WHERE POH.po_header_id = p_doc_id
740 AND POH.po_header_id = POHA.po_header_id (+)
741 AND POHA.latest_external_flag (+) = 'Y'
742 AND (
743 ( POHA.po_header_id IS NULL)
744 OR ( (POH.agent_id <> POHA.agent_id) AND (p_chk_cancel_flag='Y'))
745 OR (POH.vendor_site_id <> POHA.vendor_site_id)
746 OR (POH.vendor_site_id IS NULL
747 AND POHA.vendor_site_id IS NOT NULL)
748 OR (POH.vendor_site_id IS NOT NULL
749 AND POHA.vendor_site_id IS NULL)
750 OR (POH.vendor_contact_id <> POHA.vendor_contact_id)
751 OR (POH.vendor_contact_id IS NULL
752 AND POHA.vendor_contact_id IS NOT NULL)
753 OR (POH.vendor_contact_id IS NOT NULL
754 AND POHA.vendor_contact_id IS NULL)
755 OR (POH.ship_to_location_id <> POHA.ship_to_location_id)
756 OR (POH.ship_to_location_id IS NULL
757 AND POHA.ship_to_location_id IS NOT NULL)
758 OR (POH.ship_to_location_id IS NOT NULL
759 AND POHA.ship_to_location_id IS NULL)
760 OR (POH.bill_to_location_id <> POHA.bill_to_location_id)
761 OR (POH.bill_to_location_id IS NULL
762 AND POHA.bill_to_location_id IS NOT NULL)
763 OR (POH.bill_to_location_id IS NOT NULL
764 AND POHA.bill_to_location_id IS NULL)
765 OR (POH.terms_id <> POHA.terms_id)
766 OR (POH.terms_id IS NULL
767 AND POHA.terms_id IS NOT NULL)
768 OR (POH.terms_id IS NOT NULL
769 AND POHA.terms_id IS NULL)
770 OR (POH.ship_via_lookup_code <>
771 POHA.ship_via_lookup_code)
772 OR (POH.ship_via_lookup_code IS NULL
773 AND POHA.ship_via_lookup_code IS NOT NULL)
774 OR (POH.ship_via_lookup_code IS NOT NULL
775 AND POHA.ship_via_lookup_code IS NULL)
776 OR (POH.fob_lookup_code <> POHA.fob_lookup_code)
777 OR (POH.fob_lookup_code IS NULL
778 AND POHA.fob_lookup_code IS NOT NULL)
779 OR (POH.fob_lookup_code IS NOT NULL
780 AND POHA.fob_lookup_code IS NULL)
781 OR (POH.freight_terms_lookup_code <>
782 POHA.freight_terms_lookup_code)
783 OR (POH.freight_terms_lookup_code IS NULL
784 AND POHA.freight_terms_lookup_code IS NOT NULL)
785 OR (POH.freight_terms_lookup_code IS NOT NULL
786 AND POHA.freight_terms_lookup_code IS NULL)
787 -- <INBOUND LOGISTICS FPJ START>
788 OR (POH.shipping_control <>
789 POHA.shipping_control)
790 OR (POH.shipping_control IS NULL
791 AND POHA.shipping_control IS NOT NULL)
792 OR (POH.shipping_control IS NOT NULL
793 AND POHA.shipping_control IS NULL)
794 -- <INBOUND LOGISTICS FPJ END>
795 OR (POH.blanket_total_amount <>
796 POHA.blanket_total_amount)
797 OR (POH.blanket_total_amount IS NULL
798 AND POHA.blanket_total_amount IS NOT NULL)
799 OR (POH.blanket_total_amount IS NOT NULL
800 AND POHA.blanket_total_amount IS NULL)
801 OR (POH.note_to_vendor <> POHA.note_to_vendor)
802 OR (POH.note_to_vendor IS NULL
803 AND POHA.note_to_vendor IS NOT NULL)
804 OR (POH.note_to_vendor IS NOT NULL
805 AND POHA.note_to_vendor IS NULL)
806 OR (POH.confirming_order_flag <>
807 POHA.confirming_order_flag)
808 OR (POH.confirming_order_flag IS NULL
809 AND POHA.confirming_order_flag IS NOT NULL)
810 OR (POH.confirming_order_flag IS NOT NULL
811 AND POHA.confirming_order_flag IS NULL)
812 -- Start Bug 3659223: Clean up logic, and correctly handle
813 -- revisioning for PO rejected during signature process.
814 -- Replaced bug fix for bug 3388218
815 OR ((POH.acceptance_required_flag <> POHA.acceptance_required_flag)
816 AND (POH.acceptance_required_flag <> 'N'))
817 OR (POHA.acceptance_required_flag in ('Y','D')
818 AND POH.acceptance_required_flag ='N'
819 AND (nvl(l_accepted_flag,'X') not in ('N', 'Y'))) --Bug# 5943064
820 -- End Bug 3659223
821 OR (POH.acceptance_required_flag IS NULL
822 AND POHA.acceptance_required_flag IS NOT NULL)
823 OR (POH.acceptance_required_flag IS NOT NULL
824 AND POHA.acceptance_required_flag IS NULL)
825 OR (POH.acceptance_due_date <> POHA.acceptance_due_date)
826 OR (POH.acceptance_due_date IS NULL
827 AND POHA.acceptance_due_date IS NOT NULL
828 AND nvl(l_accepted_flag,'X') not in ('N','Y') -- Bug 3498816, Bug# 5943064
829 -- Bug 3659223: Do not revision for Doc and Sig, as
830 -- accepting/rejecting will null out the date.
831 AND nvl(POH.acceptance_required_flag, 'X') <> 'S')
832 OR (POH.acceptance_due_date IS NOT NULL
833 AND POHA.acceptance_due_date IS NULL)
834 OR (POH.amount_limit <> POHA.amount_limit)
835 OR (POH.amount_limit IS NULL
836 AND POHA.amount_limit IS NOT NULL)
837 OR (POH.amount_limit IS NOT NULL
838 AND POHA.amount_limit IS NULL)
839 OR (POH.start_date <> POHA.start_date)
840 OR (POH.start_date IS NULL
841 AND POHA.start_date IS NOT NULL)
842 OR (POH.start_date IS NOT NULL
843 AND POHA.start_date IS NULL)
844 OR (POH.end_date <> POHA.end_date)
845 OR (POH.end_date IS NULL
846 AND POHA.end_date IS NOT NULL)
847 OR (POH.end_date IS NOT NULL
848 AND POHA.end_date IS NULL)
849 OR (p_chk_cancel_flag = 'Y' AND --<CancelPO FPJ>
850 ((POH.cancel_flag <> POHA.cancel_flag)
851 OR (POH.cancel_flag IS NULL
852 AND POHA.cancel_flag IS NOT NULL)
853 OR (POH.cancel_flag IS NOT NULL
854 AND POHA.cancel_flag IS NULL)))
855
856 --<CONTERMS FPJ START> dependency popo.odf , poarc.odf
857 OR (POH.conterms_articles_upd_date <> POHA.conterms_articles_upd_date)
858 OR (POH.conterms_articles_upd_date IS NULL
859 AND POHA.conterms_articles_upd_date IS NOT NULL)
860 OR (POH.conterms_articles_upd_date IS NOT NULL
861 AND POHA.conterms_articles_upd_date IS NULL)
862 OR (POH.conterms_deliv_upd_date <> POHA.conterms_deliv_upd_date)
863 OR (POH.conterms_deliv_upd_date IS NULL
864 AND POHA.conterms_deliv_upd_date IS NOT NULL)
865 OR (POH.conterms_deliv_upd_date IS NOT NULL
866 AND POHA.conterms_deliv_upd_date IS NULL)
867
868 --<CONTERMS FPJ END>
869 ));
870
871 --< Shared Proc FPJ Start >
872 ELSIF (p_element = 'PORCH_GA_ORG_ASSIGN') AND
873 (p_doc_subtype IN ('BLANKET', 'CONTRACT'))
874 THEN
875
876 l_progress := '015';
877
878 --SQL What: Check latest external archived records with
879 -- the current records
880 --SQL Why: If certain columns are different, a new
881 -- revision is needed
882 SELECT 'Y'
883 INTO x_different
884 FROM po_ga_org_assignments pgoa,
885 po_ga_org_assignments_archive pgoaa
886 WHERE pgoa.po_header_id = p_doc_id
887 AND pgoa.po_header_id = pgoaa.po_header_id (+)
888 AND pgoa.organization_id = pgoaa.organization_id (+)
889 AND pgoaa.latest_external_flag (+) = 'Y'
890 AND ( (pgoaa.po_header_id IS NULL)
891 OR (pgoaa.organization_id <> pgoa.organization_id)
892 OR (pgoaa.purchasing_org_id <> pgoa.purchasing_org_id)
893 OR (pgoaa.vendor_site_id <> pgoa.vendor_site_id)
894 OR (pgoaa.enabled_flag <> pgoa.enabled_flag)
895 )
896 AND ROWNUM = 1;
897
898 --< Shared Proc FPJ End >
899
900 elsif (p_element = 'PORCH_LINES') then --p_element='PORCH_HEADER'*/
901
902 l_progress := '020';
903 if (p_doc_subtype = 'BLANKET') then
904 Select 'Y'
905 INTO x_different
906 from sys.dual
907 where exists(
908 select null
909 FROM PO_LINES POL,
910 PO_LINES_ARCHIVE POLA
911 WHERE POL.po_header_id = p_doc_id
912 AND (p_line_id IS NULL OR POL.po_line_id = p_line_id) --<CancelPO FPJ>
913 AND POL.po_line_id = POLA.po_line_id (+)
914 AND POLA.latest_external_flag (+) = 'Y'
915 AND (
916 (POLA.po_line_id is NULL)
917 OR (POL.line_num <> POLA.line_num)
918 OR (POL.item_id <> POLA.item_id)
919 OR (POL.item_id IS NULL
920 AND POLA.item_id IS NOT NULL)
921 OR (POL.item_id IS NOT NULL
922 AND POLA.item_id IS NULL)
923 -- SERVICES FPJ Start
924 OR (POL.job_id <> POLA.job_id)
925 OR (POL.job_id IS NULL
926 AND POLA.job_id IS NOT NULL)
927 OR (POL.job_id IS NOT NULL
928 AND POLA.job_id IS NULL)
929 OR (POL.amount <> POLA.amount)
930 OR (POL.amount IS NULL
931 AND POLA.amount IS NOT NULL)
932 OR (POL.amount IS NOT NULL
933 AND POLA.amount IS NULL)
934 -- SERVICES FPJ Start
935 OR (POL.item_revision <> POLA.item_revision)
936 OR (POL.item_revision IS NULL
937 AND POLA.item_revision IS NOT NULL)
938 OR (POL.item_revision IS NOT NULL
939 AND POLA.item_revision IS NULL)
940 OR (TRIM(POL.item_description) <>
941 TRIM(POLA.item_description)) --Bug14214404
942 OR (POL.item_description IS NULL
943 AND POLA.item_description IS NOT NULL)
944 OR (POL.item_description IS NOT NULL
945 AND POLA.item_description IS NULL)
946 OR (POL.unit_meas_lookup_code <>
947 POLA.unit_meas_lookup_code)
948 OR (POL.unit_meas_lookup_code IS NULL
949 AND POLA.unit_meas_lookup_code IS NOT NULL)
950 OR (POL.unit_meas_lookup_code IS NOT NULL
951 AND POLA.unit_meas_lookup_code IS NULL)
952 OR (POL.quantity_committed <>
953 POLA.quantity_committed)
954 OR (POL.quantity_committed IS NULL
955 AND POLA.quantity_committed IS NOT NULL)
956 OR (POL.quantity_committed IS NOT NULL
957 AND POLA.quantity_committed IS NULL)
958 OR (POL.committed_amount <>
959 POLA.committed_amount)
960 OR (POL.committed_amount IS NULL
961 AND POLA.committed_amount IS NOT NULL)
962 OR (POL.committed_amount IS NOT NULL
963 AND POLA.committed_amount IS NULL)
964 OR (POL.unit_price <> POLA.unit_price)
965 OR (POL.unit_price IS NULL
966 AND POLA.unit_price IS NOT NULL)
967 OR (POL.unit_price IS NOT NULL
968 AND POLA.unit_price IS NULL)
969 -- Bug 3471211
970 OR (POL.not_to_exceed_price <> POLA.not_to_exceed_price)
971 OR (POL.not_to_exceed_price IS NULL
972 AND POLA.not_to_exceed_price IS NOT NULL)
973 OR (POL.not_to_exceed_price IS NOT NULL
974 AND POLA.not_to_exceed_price IS NULL)
975 OR (POL.un_number_id <> POLA.un_number_id)
976 OR (POL.un_number_id IS NULL
977 AND POLA.un_number_id IS NOT NULL)
978 OR (POL.un_number_id IS NOT NULL
979 AND POLA.un_number_id IS NULL)
980 OR (POL.hazard_class_id <> POLA.hazard_class_id)
981 OR (POL.hazard_class_id IS NULL
982 AND POLA.hazard_class_id IS NOT NULL)
983 OR (POL.hazard_class_id IS NOT NULL
984 AND POLA.hazard_class_id IS NULL)
985 OR (POL.note_to_vendor <> POLA.note_to_vendor)
986 OR (POL.note_to_vendor IS NULL
987 AND POLA.note_to_vendor IS NOT NULL)
988 OR (POL.note_to_vendor IS NOT NULL
989 AND POLA.note_to_vendor IS NULL)
990 OR (POL.note_to_vendor <> POLA.note_to_vendor)
991 OR (POL.note_to_vendor IS NULL
992 AND POLA.note_to_vendor IS NOT NULL)
993 OR (POL.note_to_vendor IS NOT NULL
994 AND POLA.note_to_vendor IS NULL)
995 OR (POL.from_header_id <> POLA.from_header_id)
996 OR (POL.from_header_id IS NULL
997 AND POLA.from_header_id IS NOT NULL)
998 OR (POL.from_header_id IS NOT NULL
999 AND POLA.from_header_id IS NULL)
1000 OR (POL.from_line_id <> POLA.from_line_id)
1001 OR (POL.from_line_id IS NULL
1002 AND POLA.from_line_id IS NOT NULL)
1003 OR (POL.from_line_id IS NOT NULL
1004 AND POLA.from_line_id IS NULL)
1005 -- Bug 3305753: Closed code need not be compared
1006 -- Since close action is an internal action and
1007 -- should not affect the document revision.
1008 -- ((POL.closed_code <> POLA.closed_code)
1009 --OR (POL.closed_code IS NULL
1010 -- AND POLA.closed_code IS NOT NULL)
1011 --OR (POL.closed_code IS NOT NULL
1012 -- AND POLA.closed_code IS NULL))
1013 OR (POL.vendor_product_num <>
1014 POLA.vendor_product_num)
1015 OR (POL.vendor_product_num IS NULL
1016 AND POLA.vendor_product_num IS NOT NULL)
1017 OR (POL.vendor_product_num IS NOT NULL
1018 AND POLA.vendor_product_num IS NULL)
1019 -- <GC FPJ>
1020 -- Removing CONTRACT_NUM check because
1021 -- Blanket line cannot reference a contract
1022 OR (POL.price_type_lookup_code <>
1023 POLA.price_type_lookup_code)
1024 OR (POL.price_type_lookup_code IS NULL
1025 AND POLA.price_type_lookup_code IS NOT NULL)
1026 OR (POL.price_type_lookup_code IS NOT NULL
1027 AND POLA.price_type_lookup_code IS NULL)
1028 OR (POL.expiration_date IS NULL
1029 AND POLA.expiration_date IS NOT NULL)
1030 OR (POL.expiration_date IS NOT NULL
1031 AND POLA.expiration_date IS NULL)
1032 OR (trunc(POL.expiration_date) <>
1033 trunc(POLA.expiration_date))
1034 OR (p_chk_cancel_flag = 'Y' AND --<CancelPO FPJ>
1035 ((POL.cancel_flag <> POLA.cancel_flag)
1036 OR (POL.cancel_flag IS NULL
1037 AND POLA.cancel_flag IS NOT NULL)
1038 OR (POL.cancel_flag IS NOT NULL
1039 AND POLA.cancel_flag IS NULL)))));
1040
1041
1042 else -- (p_doc_subtype = 'BLANKET') */
1043 Select 'Y'
1044 INTO x_different
1045 from sys.dual
1046 where exists(
1047 select null
1048 FROM PO_LINES POL,
1049 PO_LINES_ARCHIVE POLA
1050 WHERE POL.po_header_id = p_doc_id
1051 AND (p_line_id IS NULL OR POL.po_line_id = p_line_id) --<CancelPO FPJ>
1052 AND POL.po_line_id = POLA.po_line_id (+)
1053 AND POLA.latest_external_flag (+) = 'Y'
1054 AND (
1055 (POLA.po_line_id is NULL)
1056 OR (POL.line_num <> POLA.line_num)
1057 OR (POL.item_id <> POLA.item_id)
1058 OR (POL.item_id IS NULL
1059 AND POLA.item_id IS NOT NULL)
1060 OR (POL.item_id IS NOT NULL
1061 AND POLA.item_id IS NULL)
1062 -- SERVICES FPJ Start
1063 OR (POL.job_id <> POLA.job_id)
1064 OR (POL.job_id IS NULL
1065 AND POLA.job_id IS NOT NULL)
1066 OR (POL.job_id IS NOT NULL
1067 AND POLA.job_id IS NULL)
1068 OR (POL.amount <> POLA.amount)
1069 OR (POL.amount IS NULL
1070 AND POLA.amount IS NOT NULL)
1071 OR (POL.amount IS NOT NULL
1072 AND POLA.amount IS NULL)
1073 OR (POL.expiration_date IS NULL
1074 AND POLA.expiration_date IS NOT NULL)
1075 OR (POL.expiration_date IS NOT NULL
1076 AND POLA.expiration_date IS NULL)
1077 OR (trunc(POL.expiration_date) <>
1078 trunc(POLA.expiration_date))
1079 OR (POL.start_date IS NULL
1080 AND POLA.start_date IS NOT NULL)
1081 OR (POL.start_date IS NOT NULL
1082 AND POLA.start_date IS NULL)
1083 OR (trunc(POL.start_date) <>
1084 trunc(POLA.start_date))
1085 OR (POL.contractor_first_name <>
1086 POLA.contractor_first_name)
1087 OR (POL.contractor_first_name IS NULL
1088 AND POLA.contractor_first_name IS NOT NULL)
1089 OR (POL.contractor_first_name IS NOT NULL
1090 AND POLA.contractor_first_name IS NULL)
1091 OR (POL.contractor_last_name <>
1092 POLA.contractor_last_name)
1093 OR (POL.contractor_last_name IS NULL
1094 AND POLA.contractor_last_name IS NOT NULL)
1095 OR (POL.contractor_last_name IS NOT NULL
1096 AND POLA.contractor_last_name IS NULL)
1097 -- SERVICES FPJ Start
1098 OR (POL.item_revision <> POLA.item_revision)
1099 OR (POL.item_revision IS NULL
1100 AND POLA.item_revision IS NOT NULL)
1101 OR (POL.item_revision IS NOT NULL
1102 AND POLA.item_revision IS NULL)
1103 OR (TRIM(POL.item_description) <>
1104 TRIM(POLA.item_description)) --Bug14214404
1105 OR (POL.item_description IS NULL
1106 AND POLA.item_description IS NOT NULL)
1107 OR (POL.item_description IS NOT NULL
1108 AND POLA.item_description IS NULL)
1109 OR (POL.unit_meas_lookup_code <>
1110 POLA.unit_meas_lookup_code)
1111 OR (POL.unit_meas_lookup_code IS NULL
1112 AND POLA.unit_meas_lookup_code IS NOT NULL)
1113 OR (POL.unit_meas_lookup_code IS NOT NULL
1114 AND POLA.unit_meas_lookup_code IS NULL)
1115 OR (p_chk_cancel_flag = 'Y' AND POL.quantity <> POLA.quantity) --<CancelPO FPJ>
1116 OR (POL.quantity IS NULL
1117 AND POLA.quantity IS NOT NULL)
1118 OR (POL.quantity_committed <>
1119 POLA.quantity_committed)
1120 OR (POL.quantity_committed IS NULL
1121 AND POLA.quantity_committed IS NOT NULL)
1122 OR (POL.quantity_committed IS NOT NULL
1123 AND POLA.quantity_committed IS NULL)
1124 OR (POL.committed_amount <>
1125 POLA.committed_amount)
1126 OR (POL.committed_amount IS NULL
1127 AND POLA.committed_amount IS NOT NULL)
1128 OR (POL.committed_amount IS NOT NULL
1129 AND POLA.committed_amount IS NULL)
1130 OR (POL.unit_price <> POLA.unit_price)
1131 OR (POL.unit_price IS NULL
1132 AND POLA.unit_price IS NOT NULL)
1133 OR (POL.unit_price IS NOT NULL
1134 AND POLA.unit_price IS NULL)
1135 -- Bug 3471211
1136 OR (POL.not_to_exceed_price <> POLA.not_to_exceed_price)
1137 OR (POL.not_to_exceed_price IS NULL
1138 AND POLA.not_to_exceed_price IS NOT NULL)
1139 OR (POL.not_to_exceed_price IS NOT NULL
1140 AND POLA.not_to_exceed_price IS NULL)
1141 OR (POL.un_number_id <> POLA.un_number_id)
1142 OR (POL.un_number_id IS NULL
1143 AND POLA.un_number_id IS NOT NULL)
1144 OR (POL.un_number_id IS NOT NULL
1145 AND POLA.un_number_id IS NULL)
1146 OR (POL.hazard_class_id <>
1147 POLA.hazard_class_id)
1148 OR (POL.hazard_class_id IS NULL
1149 AND POLA.hazard_class_id IS NOT NULL)
1150 OR (POL.hazard_class_id IS NOT NULL
1151 AND POLA.hazard_class_id IS NULL)
1152 OR (POL.note_to_vendor <> POLA.note_to_vendor)
1153 OR (POL.note_to_vendor IS NULL
1154 AND POLA.note_to_vendor IS NOT NULL)
1155 OR (POL.note_to_vendor IS NOT NULL
1156 AND POLA.note_to_vendor IS NULL)
1157 OR (POL.note_to_vendor <> POLA.note_to_vendor)
1158 OR (POL.note_to_vendor IS NULL
1159 AND POLA.note_to_vendor IS NOT NULL)
1160 OR (POL.note_to_vendor IS NOT NULL
1161 AND POLA.note_to_vendor IS NULL)
1162 OR (POL.from_header_id <> POLA.from_header_id)
1163 OR (POL.from_header_id IS NULL
1164 AND POLA.from_header_id IS NOT NULL)
1165 OR (POL.from_header_id IS NOT NULL
1166 AND POLA.from_header_id IS NULL)
1167 OR (POL.from_line_id <> POLA.from_line_id)
1168 OR (POL.from_line_id IS NULL
1169 AND POLA.from_line_id IS NOT NULL)
1170 OR (POL.from_line_id IS NOT NULL
1171 AND POLA.from_line_id IS NULL)
1172 -- Bug 3305753:Closed code need not be compared
1173 -- Since close action is an internal action and
1174 -- should not affect the document revision.
1175 -- ((POL.closed_code <> POLA.closed_code)
1176 -- OR (POL.closed_code IS NULL
1177 -- AND POLA.closed_code IS NOT NULL)
1178 -- OR (POL.closed_code IS NOT NULL
1179 -- AND POLA.closed_code IS NULL))
1180 OR (POL.vendor_product_num <>
1181 POLA.vendor_product_num)
1182 OR (POL.vendor_product_num IS NULL
1183 AND POLA.vendor_product_num IS NOT NULL)
1184 OR (POL.vendor_product_num IS NOT NULL
1185 AND POLA.vendor_product_num IS NULL)
1186 -- <GC FPJ>
1187 -- Compare contract_id instead of contract_num
1188 OR (POL.contract_id <> POLA.contract_id)
1189 OR (POL.contract_id IS NULL
1190 AND POLA.contract_id IS NOT NULL)
1191 OR (POL.contract_id IS NOT NULL
1192 AND POLA.contract_id IS NULL)
1193 OR (POL.price_type_lookup_code <>
1194 POLA.price_type_lookup_code)
1195 OR (POL.price_type_lookup_code IS NULL
1196 AND POLA.price_type_lookup_code IS NOT NULL)
1197 OR (POL.price_type_lookup_code IS NOT NULL
1198 AND POLA.price_type_lookup_code IS NULL)
1199 OR (p_chk_cancel_flag = 'Y' AND --<CancelPO FPJ>
1200 ((POL.cancel_flag <> POLA.cancel_flag)
1201 OR (POL.cancel_flag IS NULL
1202 AND POLA.cancel_flag IS NOT NULL)
1203 OR (POL.cancel_flag IS NOT NULL
1204 AND POLA.cancel_flag IS NULL)))
1205 -- <Complex Work R12 Start>
1206 OR (POL.retainage_rate <> POLA.retainage_rate)
1207 OR (POL.retainage_rate IS NULL
1208 AND POLA.retainage_rate IS NOT NULL)
1209 OR (POL.retainage_rate IS NOT NULL
1210 AND POLA.retainage_rate IS NULL)
1211 OR (POL.max_retainage_amount <> POLA.max_retainage_amount)
1212 OR (POL.max_retainage_amount IS NULL
1213 AND POLA.max_retainage_amount IS NOT NULL)
1214 OR (POL.max_retainage_amount IS NOT NULL
1215 AND POLA.max_retainage_amount IS NULL)
1216 OR (POL.progress_payment_rate <> POLA.progress_payment_rate)
1217 OR (POL.progress_payment_rate IS NULL
1218 AND POLA.progress_payment_rate IS NOT NULL)
1219 OR (POL.progress_payment_rate IS NOT NULL
1220 AND POLA.progress_payment_rate IS NULL)
1221 OR (POL.recoupment_rate <> POLA.recoupment_rate)
1222 OR (POL.recoupment_rate IS NULL
1223 AND POLA.recoupment_rate IS NOT NULL)
1224 OR (POL.recoupment_rate IS NOT NULL
1225 AND POLA.recoupment_rate IS NULL)
1226 -- <Complex Work R12 End>
1227 ));
1228
1229 end if; -- (p_doc_subtype = 'BLANKET') */
1230
1231
1232 elsif(p_element = 'PORCH_SHIPMENTS') then --p_element='PORCH_HEADER'*/
1233 Select 'Y'
1234 INTO x_different
1235 from sys.dual
1236 where exists(
1237 select null
1238 FROM PO_LINE_LOCATIONS POLL,
1239 PO_LINE_LOCATIONS_ARCHIVE POLLA
1240 WHERE POLL.po_header_id = p_doc_id
1241 AND POLL.po_release_id is null -- Bug 3876235
1242 AND (p_line_id IS NULL OR POLL.po_line_id = p_line_id) --<CancelPO FPJ>
1243 AND (p_line_location_id IS NULL OR POLL.line_location_id = p_line_location_id) --<CancelPO FPJ>
1244 AND POLL.line_location_id = POLLA.line_location_id (+)
1245 AND POLLA.latest_external_flag (+) = 'Y'
1246 AND (
1247 (POLLA.line_location_id is NULL)
1248 OR (POLL.quantity <> POLLA.quantity)
1249 OR (POLL.quantity IS NULL AND POLLA.quantity IS NOT NULL)
1250 OR (POLL.quantity IS NOT NULL AND POLLA.quantity IS NULL)
1251 -- SERVICES FPJ Start
1252 OR (POLL.amount <> POLLA.amount)
1253 OR (POLL.amount IS NULL AND POLLA.amount IS NOT NULL)
1254 OR (POLL.amount IS NOT NULL AND POLLA.amount IS NULL)
1255 -- SERVICES FPJ Start
1256 OR (POLL.ship_to_location_id <>
1257 POLLA.ship_to_location_id)
1258 OR (POLL.ship_to_location_id IS NULL
1259 AND POLLA.ship_to_location_id IS NOT NULL)
1260 OR (POLL.ship_to_location_id IS NOT NULL
1261 AND POLLA.ship_to_location_id IS NULL)
1262 OR (POLL.need_by_date <> POLLA.need_by_date)
1263 OR (POLL.need_by_date IS NULL
1264 AND POLLA.need_by_date IS NOT NULL)
1265 OR (POLL.need_by_date IS NOT NULL
1266 AND POLLA.need_by_date IS NULL)
1267 OR (POLL.promised_date <> POLLA.promised_date)
1268 OR (POLL.promised_date IS NULL
1269 AND POLLA.promised_date IS NOT NULL)
1270 OR (POLL.promised_date IS NOT NULL
1271 AND POLLA.promised_date IS NULL)
1272 OR (POLL.last_accept_date <> POLLA.last_accept_date)
1273 OR (POLL.last_accept_date IS NULL
1274 AND POLLA.last_accept_date IS NOT NULL)
1275 OR (POLL.last_accept_date IS NOT NULL
1276 AND POLLA.last_accept_date IS NULL)
1277 OR (POLL.price_override <> POLLA.price_override)
1278 OR (POLL.price_override IS NULL
1279 AND POLLA.price_override IS NOT NULL)
1280 OR (POLL.price_override IS NOT NULL
1281 AND POLLA.price_override IS NULL) --BUG7286203 REMOVED THE CHECK FOR TAXCODE ID
1282 -- <Complex Work R12 Start>
1283 OR (POLL.payment_type <> POLLA.payment_type)
1284 OR (POLL.payment_type IS NULL
1285 AND POLLA.payment_type IS NOT NULL)
1286 OR (POLL.payment_type IS NOT NULL
1287 AND POLLA.payment_type IS NULL)
1288 OR (POLL.description <> POLLA.description)
1289 OR (POLL.description IS NULL
1290 AND POLLA.description IS NOT NULL)
1291 OR (POLL.description IS NOT NULL
1292 AND POLLA.description IS NULL)
1293 OR (POLL.work_approver_id <> POLLA.work_approver_id)
1294 OR (POLL.work_approver_id IS NULL
1295 AND POLLA.work_approver_id IS NOT NULL)
1296 OR (POLL.work_approver_id IS NOT NULL
1297 AND POLLA.work_approver_id IS NULL)
1298 -- <Complex Work R12 End>
1299 OR (POLL.shipment_num <> POLLA.shipment_num)
1300 OR (POLL.shipment_num IS NULL
1301 AND POLLA.shipment_num IS NOT NULL)
1302 OR (POLL.shipment_num IS NOT NULL
1303 AND POLLA.shipment_num IS NULL)
1304 OR (POLL.sales_order_update_date <> POLLA.sales_order_update_date)
1305 OR (POLL.sales_order_update_date IS NULL
1306 AND POLLA.sales_order_update_date IS NOT NULL)
1307 OR (POLL.sales_order_update_date IS NOT NULL
1308 AND POLLA.sales_order_update_date IS NULL)
1309 OR (p_chk_cancel_flag = 'Y' AND --<CancelPO FPJ>
1310 ((POLL.cancel_flag <> POLLA.cancel_flag)
1311 OR (POLL.cancel_flag IS NULL
1312 AND POLLA.cancel_flag IS NOT NULL)
1313 OR (POLL.cancel_flag IS NOT NULL
1314 AND POLLA.cancel_flag IS NULL)))));
1315
1316 elsif (p_element = 'PORCH_PBREAK') then --p_element='PORCH_HEADER'*/
1317 /*
1318 ** note that change sin price discount will be reflected in
1319 ** changes in price_override, hence price_discount is not
1320 ** considered below.
1321 ** Also changes to ship_to_org will not cause a revision change.
1322 ** since print changed orders report does not cover that case.
1323 */
1324 Select 'Y'
1325 INTO x_different
1326 from sys.dual
1327 where exists(
1328 select null
1329 FROM PO_LINE_LOCATIONS POLL,
1330 PO_LINE_LOCATIONS_ARCHIVE POLLA
1331 WHERE POLL.po_header_id = p_doc_id
1332 AND POLL.po_release_id is null -- Bug 3876235
1333 AND (p_line_id IS NULL OR POLL.po_line_id = p_line_id) --<CancelPO FPJ>
1334 AND (p_line_location_id IS NULL OR POLL.line_location_id = p_line_location_id) --<CancelPO FPJ>
1335 AND POLL.line_location_id = POLLA.line_location_id (+)
1336 AND POLLA.latest_external_flag (+) = 'Y'
1337 AND (
1338 (POLLA.line_location_id is NULL)
1339 OR (POLL.quantity <> POLLA.quantity)
1340 OR (POLL.quantity IS NULL AND POLLA.quantity IS NOT NULL)
1341 OR (POLL.quantity IS NOT NULL AND POLLA.quantity IS NULL)
1342 OR (POLL.ship_to_location_id <>
1343 POLLA.ship_to_location_id)
1344 OR (POLL.ship_to_location_id IS NULL
1345 AND POLLA.ship_to_location_id IS NOT NULL)
1346 OR (POLL.ship_to_location_id IS NOT NULL
1347 AND POLLA.ship_to_location_id IS NULL)
1348 OR (POLL.price_override <> POLLA.price_override)
1349 OR (POLL.price_override IS NULL
1350 AND POLLA.price_override IS NOT NULL)
1351 OR (POLL.price_override IS NOT NULL
1352 AND POLLA.price_override IS NULL)
1353 OR (POLL.shipment_num <> POLLA.shipment_num)
1354 OR (POLL.shipment_num IS NULL
1355 AND POLLA.shipment_num IS NOT NULL)
1356 OR (POLL.shipment_num IS NOT NULL
1357 AND POLLA.shipment_num IS NULL)
1358 /* <TIMEPHASED FPI START> */
1359 OR (POLL.start_date <> POLLA.start_date)
1360 OR (POLL.start_date is null AND POLLA.start_date is not null)
1361 OR (POLL.start_date is not null AND POLLA.start_date is null)
1362 OR (POLL.end_date <> POLLA.end_date)
1363 OR (POLL.end_date is null AND POLLA.end_date is not null)
1364 OR (POLL.end_date is not null AND POLLA.end_date is null)));
1365 /* <TIMEPHASED FPI END> */
1366
1367 -- SERVICES FPJ Start
1368 -- Comparison for the Price differentials entity
1369
1370 ELSIF (p_element = 'PORCH_LINE_PRICE_DIFF') THEN
1371
1372 --SQL What: Check latest external archived records with
1373 -- the current records
1374 --SQL Why: If certain columns are different, a new
1375 -- revision is needed
1376
1377 SELECT 'Y'
1378 INTO x_different
1379 FROM po_price_differentials pdf,
1380 po_price_differentials_archive pdfa,
1381 po_lines_all pol
1382 WHERE pol.po_header_id = p_doc_id
1383 AND pol.po_line_id = pdf.entity_id
1384 AND pdf.entity_type in ('PO LINE', 'BLANKET LINE')
1385 AND pdf.price_differential_id = pdfa.price_differential_id (+)
1386 AND pdfa.latest_external_flag (+) = 'Y'
1387 AND (
1388 ( pdfa.price_differential_id IS NULL )
1389 OR ( pdf.price_differential_num <> pdfa.price_differential_num )
1390 OR ( pdf.price_type <> pdfa.price_type )
1391 OR ( ( pdf.multiplier <> pdfa.multiplier )
1392 OR ( pdf.multiplier IS NULL AND pdfa.multiplier IS NOT NULL )
1393 OR ( pdf.multiplier IS NOT NULL AND pdfa.multiplier IS NULL ) )
1394 OR ( ( pdf.max_multiplier <> pdfa.max_multiplier )
1395 OR ( pdf.max_multiplier IS NULL AND pdfa.max_multiplier IS NOT NULL )
1396 OR ( pdf.max_multiplier IS NOT NULL AND pdfa.max_multiplier IS NULL ) )
1397 OR ( ( pdf.min_multiplier <> pdfa.min_multiplier)
1398 OR ( pdf.min_multiplier IS NULL AND pdfa.min_multiplier IS NOT NULL )
1399 OR ( pdf.min_multiplier IS NOT NULL AND pdfa.min_multiplier IS NULL ) )
1400 OR ( ( pdf.enabled_flag <> pdfa.enabled_flag )
1401 OR ( pdf.enabled_flag IS NULL AND pdfa.enabled_flag IS NOT NULL )
1402 OR ( pdf.enabled_flag IS NOT NULL AND pdfa.enabled_flag IS NULL ) )
1403 );
1404
1405 ELSIF (p_element = 'PORCH_PB_PRICE_DIFF') THEN
1406
1407 --SQL What: Check latest external archived records with
1408 -- the current records
1409 --SQL Why: If certain columns are different, a new
1410 -- revision is needed
1411
1412 SELECT 'Y'
1413 INTO x_different
1414 FROM po_price_differentials pdf,
1415 po_price_differentials_archive pdfa,
1416 po_line_locations_all poll
1417 WHERE poll.po_header_id = p_doc_id
1418 AND poll.line_location_id = pdf.entity_id
1419 AND pdf.entity_type = 'PRICE BREAK'
1420 AND pdf.price_differential_id = pdfa.price_differential_id (+)
1421 AND pdfa.latest_external_flag (+) = 'Y'
1422 AND (
1423 ( pdfa.price_differential_id IS NULL )
1424 OR ( pdf.price_differential_num <> pdfa.price_differential_num )
1425 OR ( pdf.price_type <> pdfa.price_type )
1426 OR ( ( pdf.multiplier <> pdfa.multiplier )
1427 OR ( pdf.multiplier IS NULL AND pdfa.multiplier IS NOT NULL )
1428 OR ( pdf.multiplier IS NOT NULL AND pdfa.multiplier IS NULL ) )
1429 OR ( ( pdf.max_multiplier <> pdfa.max_multiplier )
1430 OR ( pdf.max_multiplier IS NULL AND pdfa.max_multiplier IS NOT NULL )
1431 OR ( pdf.max_multiplier IS NOT NULL AND pdfa.max_multiplier IS NULL ) )
1432 OR ( ( pdf.min_multiplier <> pdfa.min_multiplier)
1433 OR ( pdf.min_multiplier IS NULL AND pdfa.min_multiplier IS NOT NULL )
1434 OR ( pdf.min_multiplier IS NOT NULL AND pdfa.min_multiplier IS NULL ) )
1435 OR ( ( pdf.enabled_flag <> pdfa.enabled_flag )
1436 OR ( pdf.enabled_flag IS NULL AND pdfa.enabled_flag IS NOT NULL )
1437 OR ( pdf.enabled_flag IS NOT NULL AND pdfa.enabled_flag IS NULL ) )
1438 );
1439
1440 -- SERVICES FPJ End
1441
1442 elsif (p_element = 'PORCH_DISTRIBUTIONS') then --p_element='PORCH_HEADER'*/
1443 /*Bug 13960467:While comparing Encumbered_Flag, ensure that the shipment
1444 is not 'Finally Closed' since Encumbered_Flag would change to 'N' in the
1445 base tables and remain the same in the archive tables when a shipment is
1446 finally closed, thereby causing a mis-match during comparision*/
1447 Select 'Y'
1448 INTO x_different
1449 from sys.dual
1450 where exists(
1451 select null
1452 FROM PO_DISTRIBUTIONS POD,
1453 PO_DISTRIBUTIONS_ARCHIVE PODA,
1454 PO_LINE_LOCATIONS POLL --Bug 13960467
1455 WHERE POD.po_header_id = p_doc_id
1456 AND (POD.line_location_id = POLL.line_location_id) --Bug 13960467
1457 AND (p_line_id IS NULL OR POD.po_line_id = p_line_id) --<CancelPO FPJ>
1458 AND (p_line_location_id IS NULL OR POD.line_location_id = p_line_location_id) --<CancelPO FPJ>
1459 AND POD.po_distribution_id =
1460 PODA.po_distribution_id (+)
1461 AND PODA.latest_external_flag (+) = 'Y'
1462 AND (
1463 (PODA.po_distribution_id is NULL)
1464 OR (POD.quantity_ordered <> PODA.quantity_ordered)
1465 OR (POD.quantity_ordered IS NULL
1466 AND PODA.quantity_ordered IS NOT NULL)
1467 OR (POD.quantity_ordered IS NOT NULL
1468 AND PODA.quantity_ordered IS NULL)
1469 -- SERVICES FPJ
1470 OR (POD.amount_ordered <> PODA.amount_ordered)
1471 OR (POD.amount_ordered IS NULL
1472 AND PODA.amount_ordered IS NOT NULL)
1473 OR (POD.amount_ordered IS NOT NULL
1474 AND PODA.amount_ordered IS NULL)
1475 -- SERVICES FPJ
1476 /*Bug 12529922 start
1477 OR (POD.deliver_to_person_id <>
1478 PODA.deliver_to_person_id)
1479 OR (POD.deliver_to_person_id IS NULL
1480 AND PODA.deliver_to_person_id IS NOT NULL)
1481 OR (POD.deliver_to_person_id IS NOT NULL
1482 AND PODA.deliver_to_person_id IS NULL)
1483 end Bug 12529922*/
1484 /* OR (POD.distribution_num <> PODA.distribution_num)*/
1485 -- BUG 9766489: Since The Document is allowed to be canceled when its in requires
1486 -- Reapproval state, But if the document is unreserved and have the backing
1487 -- document then its not possible to manage the cancel action on the Main Document.
1488 -- Disabling the cancel action on requires reapproval action when document is
1489 -- unreserved.
1490 OR (p_chk_cancel_flag = 'N'
1491 AND NVL(POLL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED' --Bug 13960467
1492 AND POD.BUDGET_ACCOUNT_ID IS NOT NULL
1493 AND Nvl(POD.ENCUMBERED_FLAG,'P') <> Nvl(PODA.ENCUMBERED_FLAG,'P')
1494 -- to handle the null encumbered_flag
1495 )
1496 ));--Bug7286203 REMOVED THE CHECK FOR RECOVERY_RATE
1497
1498
1499 end if; -- type = PORCH_PO and p_element = 'PORCH_HEADER'*/
1500
1501 elsif (p_type = 'PORCH_RELEASE') then -- (type = 'PORCH_PO')*/
1502
1503 l_progress := '030';
1504 if (p_element = 'PORCH_HEADER') then
1505
1506 /* Start Bug# 6066670, continuation of Bug# 5943064.
1507 We need to consider 3 cases. I Supplier portal
1508 if the PO is 'Accepted'/'Rejected' then we set the acceptance_required_flag
1509 to 'N' so that we dont Enter any more acceptances.the accepted_flag can be 'Y'/'N'.
1510 But when creating the document revision we were not considering that
1511 accepted_flag can be 'N' when rejected and we should cause a document revision
1512 when this change happens and these documents can be cancelled.
1513 So we are now checking if the document is both Accepted and Rejected cases and
1514 since normal Accetances also have 'N' we differentiate a 'Rejected' case
1515 by also looking at the acceptance_required_flag in the po_headers table.
1516 Doing the same for the acceptance_due_date. l_accepted_flag='X' will
1517 represent lines which dont have acceptance Entered.
1518 We only have 'Y' and 'N' for acceptance_required_flag in Releases.
1519 so we dont need to check the other conditions as for PO.*/
1520
1521 -- Bug 3388218 Start
1522 Begin
1523 Select pav.accepted_flag
1524 into l_accepted_flag
1525 from po_acceptances_v pav,
1526 po_releases por
1527 where por.po_release_id=p_doc_id
1528 and por.po_release_id=pav.po_release_id
1529 and pav.revision_num= por.revision_num
1530 and por.acceptance_required_flag='N'
1531 and rownum=1;
1532 --and pav.accepted_flag='Y';
1533 Exception
1534 when others then
1535 l_accepted_flag:='X';
1536 End;
1537 -- Bug 3388218 End
1538
1539 /*Bug5154626: cancel action on the releases in approved state errors
1540 out on which Mass update buyer program is run to update buyer name.
1541 Hence donot use the agent_id comparision for cancel flow*/
1542
1543 Select 'Y'
1544 INTO x_different
1545 from sys.dual
1546 where exists(
1547 select null
1548 FROM PO_RELEASES POR,
1549 PO_RELEASES_ARCHIVE PORA
1550 WHERE POR.po_release_id = p_doc_id
1551 AND POR.po_release_id = PORA.po_release_id
1552 AND PORA.latest_external_flag (+) = 'Y'
1553 AND (
1554 (PORA.po_release_id IS NULL)
1555 OR (POR.release_num <> PORA.release_num)
1556 OR((POR.agent_id <> PORA.agent_id) AND (p_chk_cancel_flag='Y'))
1557 OR (POR.release_date <> PORA.release_date)
1558 -- <INBOUND LOGISTICS FPJ START>
1559 OR (POR.shipping_control <>
1560 PORA.shipping_control)
1561 OR (POR.shipping_control IS NULL
1562 AND PORA.shipping_control IS NOT NULL)
1563 OR (POR.shipping_control IS NOT NULL
1564 AND PORA.shipping_control IS NULL)
1565 -- <INBOUND LOGISTICS FPJ END>
1566 -- Start Bug 3388218
1567 OR ((POR.acceptance_required_flag <> PORA.acceptance_required_flag)
1568 AND (POR.acceptance_required_flag <> 'N'))
1569 OR (PORA.acceptance_required_flag in ('Y')
1570 AND POR.acceptance_required_flag ='N'
1571 AND (nvl(l_accepted_flag,'X') not in ('N', 'Y'))) --Bug# 6066670
1572
1573 -- End Bug 3388218
1574 OR (POR.acceptance_required_flag IS NULL
1575 AND PORA.acceptance_required_flag IS NOT NULL)
1576 OR (POR.acceptance_required_flag IS NOT NULL
1577 AND PORA.acceptance_required_flag IS NULL)
1578 OR (POR.acceptance_due_date <>
1579 PORA.acceptance_due_date)
1580 OR (POR.acceptance_due_date IS NULL
1581 AND PORA.acceptance_due_date IS NOT NULL
1582 AND nvl(l_accepted_flag,'X') not in ('N','Y')) -- Bug#3498816,Bug#6066670
1583 OR (POR.acceptance_due_date IS NOT NULL
1584 AND PORA.acceptance_due_date IS NULL)));
1585
1586 elsif(p_element = 'PORCH_SHIPMENTS') then --p_element='PORCH_HEADER'*/
1587 Select 'Y'
1588 INTO x_different
1589 from sys.dual
1590 where exists(
1591 select null
1592 FROM PO_LINE_LOCATIONS POLL,
1593 PO_LINE_LOCATIONS_ARCHIVE POLLA
1594 WHERE POLL.po_release_id = p_doc_id
1595 AND (p_line_location_id IS NULL OR POLL.line_location_id = p_line_location_id) --<CancelPO FPJ>
1596 AND POLL.line_location_id = POLLA.line_location_id (+)
1597 AND POLLA.latest_external_flag (+) = 'Y'
1598 AND (
1599 (POLLA.line_location_id is NULL)
1600 OR (POLL.quantity <> POLLA.quantity)
1601 OR (POLL.quantity IS NULL
1602 AND POLLA.quantity IS NOT NULL)
1603 OR (POLL.quantity IS NOT NULL
1604 AND POLLA.quantity IS NULL)
1605 -- SERVICES FPJ Start
1606 OR (POLL.amount <> POLLA.amount)
1607 OR (POLL.amount IS NULL AND POLLA.amount IS NOT NULL)
1608 OR (POLL.amount IS NOT NULL AND POLLA.amount IS NULL)
1609 -- SERVICES FPJ Start
1610 OR (POLL.ship_to_location_id <>
1611 POLLA.ship_to_location_id)
1612 OR (POLL.ship_to_location_id IS NULL
1613 AND POLLA.ship_to_location_id IS NOT NULL)
1614 OR (POLL.ship_to_location_id IS NOT NULL
1615 AND POLLA.ship_to_location_id IS NULL)
1616 OR (POLL.need_by_date <> POLLA.need_by_date)
1617 OR (POLL.need_by_date IS NULL
1618 AND POLLA.need_by_date IS NOT NULL)
1619 OR (POLL.need_by_date IS NOT NULL
1620 AND POLLA.need_by_date IS NULL)
1621 OR (POLL.promised_date <> POLLA.promised_date)
1622 OR (POLL.promised_date IS NULL
1623 AND POLLA.promised_date IS NOT NULL)
1624 OR (POLL.promised_date IS NOT NULL
1625 AND POLLA.promised_date IS NULL)
1626 OR (POLL.last_accept_date <> POLLA.last_accept_date)
1627 OR (POLL.last_accept_date IS NULL
1628 AND POLLA.last_accept_date IS NOT NULL)
1629 OR (POLL.last_accept_date IS NOT NULL
1630 AND POLLA.last_accept_date IS NULL)
1631 OR (POLL.price_override <> POLLA.price_override)
1632 OR (POLL.price_override IS NULL
1633 AND POLLA.price_override IS NOT NULL)
1634 OR (POLL.price_override IS NOT NULL
1635 AND POLLA.price_override IS NULL) --BUG7286203 REMOVED THE CHECK FOR TAXCODE ID
1636 OR (POLL.shipment_num <> POLLA.shipment_num)
1637 OR (POLL.shipment_num IS NULL
1638 AND POLLA.shipment_num IS NOT NULL)
1639 OR (POLL.shipment_num IS NOT NULL
1640 AND POLLA.shipment_num IS NULL)
1641 OR (POLL.sales_order_update_date <> POLLA.sales_order_update_date)
1642 OR (POLL.sales_order_update_date IS NULL
1643 AND POLLA.sales_order_update_date IS NOT NULL)
1644 OR (POLL.sales_order_update_date IS NOT NULL
1645 AND POLLA.sales_order_update_date IS NULL)
1646 OR (p_chk_cancel_flag = 'Y' AND --<CancelPO FPJ>
1647 ((POLL.cancel_flag <> POLLA.cancel_flag)
1648 OR (POLL.cancel_flag IS NULL
1649 AND POLLA.cancel_flag IS NOT NULL)
1650 OR (POLL.cancel_flag IS NOT NULL
1651 AND POLLA.cancel_flag IS NULL)))));
1652
1653 elsif (p_element = 'PORCH_DISTRIBUTIONS') then --p_element='PORCH_HEADER'*/
1654 Select 'Y'
1655 INTO x_different
1656 from sys.dual
1657 where exists(
1658 select null
1659 FROM PO_DISTRIBUTIONS POD,
1660 PO_DISTRIBUTIONS_ARCHIVE PODA
1661 WHERE POD.po_release_id = p_doc_id
1662 AND (p_line_location_id IS NULL OR POD.line_location_id = p_line_location_id) --<CancelPO FPJ>
1663 AND POD.po_distribution_id =
1664 PODA.po_distribution_id (+)
1665 AND PODA.latest_external_flag (+) = 'Y'
1666 AND (
1667 (PODA.po_distribution_id is NULL)
1668 OR (POD.quantity_ordered <> PODA.quantity_ordered)
1669 OR (POD.quantity_ordered IS NULL
1670 AND PODA.quantity_ordered IS NOT NULL)
1671 OR (POD.quantity_ordered IS NOT NULL
1672 AND PODA.quantity_ordered IS NULL)
1673 -- SERVICES FPJ
1674 OR (POD.amount_ordered <> PODA.amount_ordered)
1675 OR (POD.amount_ordered IS NULL
1676 AND PODA.amount_ordered IS NOT NULL)
1677 OR (POD.amount_ordered IS NOT NULL
1678 AND PODA.amount_ordered IS NULL)
1679 -- SERVICES FPJ
1680 /* Bug 12529922. Remove deliver_to_person_id check
1681 OR (POD.deliver_to_person_id <>
1682 PODA.deliver_to_person_id)
1683 OR (POD.deliver_to_person_id IS NULL
1684 AND PODA.deliver_to_person_id IS NOT NULL)
1685 OR (POD.deliver_to_person_id IS NOT NULL
1686 AND PODA.deliver_to_person_id IS NULL)
1687 end Bug 12529922*/
1688 /* OR (POD.distribution_num <> PODA.distribution_num) */
1689
1690 -- BUG: 9766489 Since The Document is allowed to be canceled when its in requires
1691 -- Reapproval state, But if the document is unreserved and have the backing
1692 -- document then its not possible to manage the cancel action on the Main Document.
1693 -- Disabling the cancel action on requires reapproval action when document is
1694 -- unreserved.
1695 OR (p_chk_cancel_flag = 'N'
1696 AND POD.BUDGET_ACCOUNT_ID IS NOT NULL
1697 AND Nvl(POD.ENCUMBERED_FLAG,'P') <> Nvl(PODA.ENCUMBERED_FLAG,'P')
1698 -- to handle the null encumbered_flag
1699 )
1700 ));--Bug7286203 REMOVED THE CHECK FOR RECOVERY_RATE
1701
1702
1703 end if; -- p_type = PORCH_RELEASE and p_element = 'PORCH_HEADER'*/
1704 end if; -- p_type = 'PORCH_PO'*/
1705
1706 EXCEPTION
1707 when no_data_found then
1708 x_different := 'N'; /* This is not really an error */
1709 when others then
1710 PO_MESSAGE_S.SQL_ERROR(routine => 'Compare_Table',
1711 location => l_progress,
1712 error_code => SQLCODE);
1713
1714 END Compare_Table;
1715
1716 --<CancelPO FPJ Start>
1717 -------------------------------------------------------------------------------
1718 --Start of Comments
1719 --Name: Compare
1720 --Function:
1721 -- Checks if a PO/PA/Release Header/Line/Shipment are different compared to
1722 -- its archived copy. The output parameter x_different indicates if they are different.
1723 -- All attributes that cause revision change except cancel_flag/closed_code are compared
1724 --Parameters:
1725 --IN:
1726 --p_api_version
1727 -- Standard API Version
1728 --p_doc_id
1729 -- The Document ID of the PO/PA/Release
1730 --p_doc_type
1731 -- The Document Type indicating PO, PA, or RELEASE
1732 --p_doc_subtype
1733 -- The Document Subtype
1734 --p_line_id
1735 -- The Line ID if the Line/Shipment needs to be compared
1736 --p_line_location_id
1737 -- The Shipment ID if the Shipment needs to be compared
1738 --OUT:
1739 --x_different
1740 -- Indicates if the entity Header/Line/Shipment is different.
1741 -- One of the Following Values is returned:
1742 -- Y If Archival exists and differences exist
1743 -- N If Archival exists and no differences exist
1744 -- M If archival record is missing.
1745 --x_return_status
1746 -- Standard API Return Status S, U, E
1747 --Testing:
1748 -- Test for all Types of Documents and Entity Levels
1749 --End of Comments
1750 -------------------------------------------------------------------------------
1751 PROCEDURE Compare(
1752 p_api_version IN NUMBER,
1753 p_doc_id IN NUMBER,
1754 p_doc_type IN VARCHAR2,
1755 p_doc_subtype IN VARCHAR2,
1756 p_line_id IN NUMBER,
1757 p_line_location_id IN NUMBER,
1758 x_different OUT NOCOPY Varchar2,
1759 x_return_status OUT NOCOPY VARCHAR2
1760 ) IS
1761
1762 l_api_name CONSTANT VARCHAR(30) := 'COMPARE';
1763 l_api_version CONSTANT NUMBER := 1.0;
1764 l_progress VARCHAR2(3) := '000';
1765 l_need_new_revision boolean := FALSE;
1766 l_Archive_Record_Exists VARCHAR2(1);
1767
1768 BEGIN
1769
1770 IF g_fnd_debug = 'Y' THEN
1771 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1772 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
1773 || l_progress, 'Entering Procedure '||l_api_name || ' DocType:' || p_doc_type
1774 || ' DocId:' || p_doc_id|| ' LineId:' || p_line_id
1775 || ' LineLocId:' || p_line_location_id);
1776 END IF;
1777 END IF;
1778
1779 l_progress := '010';
1780 --Standard call to check for call compatibility
1781 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1782 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1783 END IF;
1784
1785 x_return_status := FND_API.G_RET_STS_SUCCESS;
1786
1787 if ((p_doc_type = 'PO') OR (p_doc_type = 'PA')) THEN
1788
1789 l_progress := '015';
1790 BEGIN
1791 select 'Y'
1792 into l_Archive_Record_Exists
1793 from po_headers_archive
1794 where po_header_id = p_doc_id and rownum = 1;
1795 EXCEPTION
1796 WHEN NO_DATA_FOUND THEN
1797 l_Archive_Record_Exists := 'N';
1798 END;
1799
1800 IF l_Archive_Record_Exists = 'N' THEN
1801 x_different := 'M'; --Return M If archival record is missing.
1802
1803 IF g_fnd_debug = 'Y' THEN
1804 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1805 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
1806 || l_progress, 'x_different=M, No Archived Record Exists');
1807 END IF;
1808 END IF;
1809
1810 return;
1811 END IF;
1812
1813 l_progress := '020';
1814 IF p_line_id is null THEN -- Compare Header If not at line level
1815 l_need_new_revision :=
1816 Check_PO_PA_Revision(
1817 p_doc_type => p_doc_type,
1818 p_doc_subtype => p_doc_subtype,
1819 p_doc_id => p_doc_id,
1820 p_table_name => 'HEADER',
1821 p_line_id => p_line_id,
1822 p_line_location_id => p_line_location_id,
1823 p_chk_cancel_flag => 'N',
1824 x_different => x_different);
1825
1826 IF x_different = 'Y' THEN
1827
1828 IF g_fnd_debug = 'Y' THEN
1829 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1830 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
1831 || l_progress, 'PO Header is Different');
1832 END IF;
1833 END IF;
1834
1835 return;
1836 END IF;
1837 END IF;
1838
1839 l_progress := '030';
1840 IF p_line_location_id is null THEN -- Compare Line If not at Shipment level
1841 l_need_new_revision :=
1842 Check_PO_PA_Revision(
1843 p_doc_type => p_doc_type,
1844 p_doc_subtype => p_doc_subtype,
1845 p_doc_id => p_doc_id,
1846 p_table_name => 'LINES',
1847 p_line_id => p_line_id,
1848 p_line_location_id => p_line_location_id,
1849 p_chk_cancel_flag => 'N',
1850 x_different => x_different);
1851
1852 IF x_different = 'Y' THEN
1853
1854 IF g_fnd_debug = 'Y' THEN
1855 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1856 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
1857 || l_progress, 'PO Line is Different');
1858 END IF;
1859 END IF;
1860
1861 return;
1862 END IF;
1863 END IF;
1864
1865 l_progress := '040';
1866 -- Compare Shipments for any level: Header/Line/Shipment
1867 l_need_new_revision :=
1868 Check_PO_PA_Revision(
1869 p_doc_type => p_doc_type,
1870 p_doc_subtype => p_doc_subtype,
1871 p_doc_id => p_doc_id,
1872 p_table_name => 'SHIPMENTS',
1873 p_line_id => p_line_id,
1874 p_line_location_id => p_line_location_id,
1875 p_chk_cancel_flag => 'N',
1876 x_different => x_different);
1877
1878 IF x_different = 'Y' THEN
1879
1880 IF g_fnd_debug = 'Y' THEN
1881 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1882 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
1883 || l_progress, 'PO Shipment is Different');
1884 END IF;
1885 END IF;
1886
1887 return;
1888 END IF;
1889
1890 l_progress := '050';
1891 -- Compare Distributions for any level: Header/Line/Shipment
1892 l_need_new_revision :=
1893 Check_PO_PA_Revision(
1894 p_doc_type => p_doc_type,
1895 p_doc_subtype => p_doc_subtype,
1896 p_doc_id => p_doc_id,
1897 p_table_name => 'DISTRIBUTIONS',
1898 p_line_id => p_line_id,
1899 p_line_location_id => p_line_location_id,
1900 p_chk_cancel_flag => 'N',
1901 x_different => x_different);
1902
1903 elsif ((p_doc_type = 'RELEASE')) THEN
1904
1905 l_progress := '055';
1906 BEGIN
1907 select 'Y'
1908 into l_Archive_Record_Exists
1909 from po_releases_archive
1910 where po_release_id = p_doc_id and rownum = 1;
1911 EXCEPTION
1912 WHEN NO_DATA_FOUND THEN
1913 l_Archive_Record_Exists := 'N';
1914 END;
1915
1916 IF l_Archive_Record_Exists = 'N' THEN
1917 x_different := 'N';
1918
1919 IF g_fnd_debug = 'Y' THEN
1920 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1921 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
1922 || l_progress, 'No Archived Record Exists');
1923 END IF;
1924 END IF;
1925
1926 return;
1927 END IF;
1928
1929 l_progress := '060';
1930 IF p_line_location_id is null THEN -- Compare Header If not at Shipment level
1931 l_need_new_revision :=
1932 Check_Release_Revision(
1933 p_doc_type => p_doc_type,
1934 p_doc_subtype => p_doc_subtype,
1935 p_doc_id => p_doc_id,
1936 p_table_name => 'HEADER',
1937 p_line_location_id => p_line_location_id,
1938 p_chk_cancel_flag => 'N',
1939 x_different => x_different);
1940
1941 IF x_different = 'Y' THEN
1942
1943 IF g_fnd_debug = 'Y' THEN
1944 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1945 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
1946 || l_progress, 'Release Header is Different');
1947 END IF;
1948 END IF;
1949
1950 return;
1951 END IF;
1952 END IF;
1953
1954 l_progress := '070';
1955 -- Compare Shipments for any level: Release Header/Shipment
1956 l_need_new_revision :=
1957 Check_Release_Revision(
1958 p_doc_type => p_doc_type,
1959 p_doc_subtype => p_doc_subtype,
1960 p_doc_id => p_doc_id,
1961 p_table_name => 'SHIPMENTS',
1962 p_line_location_id => p_line_location_id,
1963 p_chk_cancel_flag => 'N',
1964 x_different => x_different);
1965
1966 IF x_different = 'Y' THEN
1967
1968 IF g_fnd_debug = 'Y' THEN
1969 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1970 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
1971 || l_progress, 'Releqase Shipment is Different');
1972 END IF;
1973 END IF;
1974
1975 return;
1976 END IF;
1977
1978 l_progress := '080';
1979 -- Compare Distributions for any level: Release Header/Shipment
1980 l_need_new_revision :=
1981 Check_Release_Revision(
1982 p_doc_type => p_doc_type,
1983 p_doc_subtype => p_doc_subtype,
1984 p_doc_id => p_doc_id,
1985 p_table_name => 'DISTRIBUTIONS',
1986 p_line_location_id => p_line_location_id,
1987 p_chk_cancel_flag => 'N',
1988 x_different => x_different);
1989
1990 else
1991 x_different := 'N';
1992 end if; /* (p_doc_type = 'PO') OR (p_doc_type = 'PA') */
1993
1994 IF g_fnd_debug = 'Y' THEN
1995 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1996 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
1997 || l_progress, 'Final x_Different ' || x_different);
1998 END IF;
1999 END IF;
2000
2001 EXCEPTION
2002 WHEN FND_API.G_EXC_ERROR THEN
2003 x_return_status := FND_API.G_RET_STS_ERROR;
2004 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2005 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2006 WHEN OTHERS THEN
2007 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2008 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
2009
2010 END Compare;
2011 --<CancelPO FPJ End>
2012 -------------------------------------------------------------------------------
2013 --<Bug 14254141 :Cancel Refactoring Project >
2014 --Start of Comments
2015 --Name: CHECK_REV_DIFF
2016 --Function:
2017 -- Checks if there are any non-approved changes in the base tables
2018 -- The below columns of base tables are compared against archive :
2019 -- Need_By_Data /Promised Date
2020 -- Quantity
2021 -- Price
2022 -- Amount
2023
2024 --Parameters:
2025 --IN:
2026 --p_api_version
2027 -- Standard API Version
2028 --p_doc_id
2029 -- The Document ID of the PO/PA/Release
2030 --p_doc_type
2031 -- The Document Type indicating PO, PA, or RELEASE
2032 --p_doc_subtype
2033 -- The Document Subtype
2034 --p_line_id
2035 -- The Line ID if the Line/Shipment needs to be compared
2036 --p_line_location_id
2037 -- The Shipment ID if the Shipment needs to be compared
2038 --p_action_level
2039 -- The control action level i.e. HEADER/LINE/LINE_LOCATION
2040 --OUT:
2041 --x_msg
2042 -- This will have value if the entity is different than its previous revision
2043 -- based on above explained check.
2044 --x_return_status
2045 -- Standard API Return Status S, U, E
2046 --Testing:
2047 -- Test for all Types of Documents and Entity Levels
2048 --End of Comments
2049 -------------------------------------------------------------------------------
2050
2051
2052 PROCEDURE CHECK_REV_DIFF(
2053 p_api_version IN NUMBER,
2054 p_doc_id IN NUMBER,
2055 p_doc_type IN VARCHAR2,
2056 p_doc_subtype IN VARCHAR2,
2057 p_line_id IN NUMBER,
2058 p_line_location_id IN NUMBER,
2059 p_action_level IN VARCHAR2,
2060 x_msg_name OUT NOCOPY VARCHAR2,
2061 x_msg_type OUT NOCOPY VARCHAR2,
2062 x_token_name_tbl OUT NOCOPY PO_TBL_VARCHAR30,
2063 x_token_value_tbl OUT NOCOPY PO_TBL_VARCHAR2000,
2064 x_return_status OUT NOCOPY VARCHAR2
2065 )
2066 IS
2067
2068 l_api_name CONSTANT VARCHAR(30) := 'CHECK_REV_DIFF';
2069 l_api_version CONSTANT NUMBER := 1.0;
2070 l_progress VARCHAR2(3) := '000';
2071 d_module CONSTANT VARCHAR2(100) := G_PKG_NAME||l_api_name;
2072 d_debug_stmt BOOLEAN :=(g_fnd_debug = 'Y')
2073 AND (FND_LOG.G_CURRENT_RUNTIME_LEVEL
2074 <= FND_LOG.LEVEL_STATEMENT) ;
2075
2076
2077
2078 l_line_token VARCHAR2(20);
2079 l_ship_token VARCHAR2(20);
2080 l_amt_token VARCHAR2(20);
2081 l_qty_token VARCHAR2(20);
2082 l_doc_token VARCHAR2(20);
2083 l_to_token VARCHAR2(20);
2084 l_no_chg_token VARCHAR2(20);
2085 l_po_encumbrance_flag FINANCIALS_SYSTEM_PARAMETERS.purch_encumbrance_flag%TYPE;
2086
2087
2088 BEGIN
2089
2090 IF d_debug_stmt THEN
2091 PO_DEBUG.debug_begin(d_module);
2092 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_subtype', p_doc_subtype);
2093 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_id', p_doc_id);
2094 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_type', p_doc_type);
2095 PO_DEBUG.debug_var(d_module, l_progress, 'p_line_id', p_line_id);
2096 PO_DEBUG.debug_var(d_module, l_progress, 'p_line_location_id', p_line_location_id);
2097 PO_DEBUG.debug_var(d_module, l_progress, 'p_action_level', p_action_level);
2098 END IF;
2099
2100
2101
2102 l_line_token := fnd_message.get_string('PO', 'PO_ZMVOR_LINE');
2103 l_ship_token := fnd_message.get_string('PO', 'PO_ZMVOR_SHIPMENT');
2104 l_amt_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT');
2105 l_qty_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY');
2106 l_doc_token := fnd_message.get_string('PO', 'PO_DOCUMENT_LABEL');
2107 l_to_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_TO');
2108 l_no_chg_token := fnd_message.get_string('PO', 'PO_DIALOG_NO_LABEL')||' '||fnd_message.get_string('PO','PO_WF_NOTIF_CHANGE');
2109
2110 x_token_name_tbl := PO_TBL_VARCHAR30();
2111 x_token_name_tbl.EXTEND(5);
2112 x_token_value_tbl := PO_TBL_VARCHAR2000();
2113 x_token_value_tbl.EXTEND(5);
2114
2115
2116 x_return_status := FND_API.G_RET_STS_SUCCESS;
2117 x_msg_name:=NULL;
2118
2119 BEGIN
2120
2121 -- <13503748: Edit without unreserve ER >
2122 -- Throw an error if the encumbered flag at PO distributions is N for
2123 -- encumbered enabled environment else give warning
2124
2125 --Query encumbrance flags from FINANCIALS_SYSTEM_PARAMS
2126 --Using this flag to stop revert changes as part of cancel when encumbrance
2127 --is on and any of the encumbrance related attribute is changed.
2128 -- This check would be needed until the Encumbrance ER is in place.
2129 SELECT NVL(fsp.purch_encumbrance_flag, 'N')
2130 INTO l_po_encumbrance_flag
2131 FROM financials_system_params_all fsp
2132 WHERE org_id = (SELECT org_id
2133 FROM po_releases_all
2134 WHERE po_release_id = p_doc_id
2135 AND p_doc_type = PO_DOCUMENT_CANCEL_PVT.c_doc_type_RELEASE
2136 UNION ALL
2137 SELECT org_id
2138 FROM po_headers_all
2139 WHERE po_header_id = p_doc_id
2140 AND p_doc_type <> PO_DOCUMENT_CANCEL_PVT.c_doc_type_RELEASE);
2141
2142
2143
2144
2145
2146 l_progress :='001';
2147
2148 SELECT 'PO_CHANGED_CANT_CANCEL_WARN',
2149 'DOC_LINE_SHIP_DIST_NUM',
2150 l_doc_token||''||segment1||','|| l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM,
2151 'PRICE_TOKEN',
2152 Decode(Nvl(poall.price_override,0),Nvl(poll.price_override,0),
2153 l_no_chg_token,
2154 (poall.price_override||' '||l_to_token ||' '|| poll.price_override)),
2155 'AMT_QTY_TOKEN',
2156 DECODE(poll.amount,NULL,l_qty_token,l_amt_token),
2157 'QTY_AMT',
2158 Decode(poll.amount,NULL,
2159 Decode(poall.quantity,poll.quantity,
2160 l_no_chg_token,
2161 (poall.quantity||' '||l_to_token ||' '|| poll.quantity)),
2162 Decode(poall.amount,poll.amount,
2163 l_no_chg_token,
2164 (poall.amount||' '||l_to_token ||' '|| poll.amount))
2165 ),
2166 'NEED_BY_PRM_DATE',
2167 Decode(poll.promised_date,NULL,
2168 Decode(poall.need_by_date,poll.need_by_date,
2169 l_no_chg_token,
2170 (Nvl(To_Char(poall.need_by_date),'Null')||' '||l_to_token ||' '|| Nvl(To_Char(poll.need_by_date),'Null'))),
2171 Decode(poall.promised_date,poll.promised_date,
2172 l_no_chg_token,
2173 (Nvl(To_Char(poall.promised_date),'Null')||' '||l_to_token ||' '|| Nvl(To_Char(poll.promised_date),'Null'))
2174
2175 ) )
2176 INTO x_msg_name,
2177 x_token_name_tbl(1),
2178 x_token_value_tbl(1),
2179 x_token_name_tbl(2),
2180 x_token_value_tbl(2),
2181 x_token_name_tbl(3),
2182 x_token_value_tbl(3),
2183 x_token_name_tbl(4),
2184 x_token_value_tbl(4),
2185 x_token_name_tbl(5),
2186 x_token_value_tbl(5)
2187
2188 FROM
2189 po_line_locations_archive_all poall,
2190 po_line_locations_all poll,
2191 po_headers_all poh,
2192 po_lines_all pol,
2193 po_distributions_all pod ---<BUG :13503748>--
2194 WHERE
2195 poll.line_location_id = pod.line_location_id ---<BUG :13503748>--
2196 AND poll.po_line_id = pol.po_line_id
2197 AND poll.po_header_id = pol.po_header_id
2198 AND Nvl(poll.approved_flag,'N')<>'Y'
2199 AND poll.line_location_id=poall.line_location_id
2200 AND poh.po_header_id=poll.po_header_id
2201 AND poall.latest_external_flag ='Y'
2202 AND ((l_po_encumbrance_flag = 'Y' AND pod.encumbered_flag = 'Y')
2203 OR l_po_encumbrance_flag = 'N') ---<BUG :13503748>--
2204 AND (Nvl(poll.price_override,0) <> Nvl(poall.price_override,0)
2205 OR Nvl(poll.quantity,0) <> Nvl(poall.quantity,0)
2206 OR Nvl(poll.amount,0) <> Nvl(poall.amount,0)
2207 OR Nvl(poll.promised_date,sysdate) <> Nvl(poall.promised_date,sysdate)
2208 OR Nvl(poll.need_by_date,sysdate) <> Nvl(poall.need_by_date,sysdate))
2209 AND poll.line_location_id IN
2210 ( SELECT line_location_id
2211 FROM po_line_locations_all
2212 WHERE line_location_id = p_line_location_id
2213 AND p_action_level = PO_DOCUMENT_CANCEL_PVT.c_entity_level_SHIPMENT
2214 AND 0= (SELECT Count(1)
2215 FROM po_distributions_all pod
2216 WHERE pod.line_location_id=p_line_location_id
2217 AND NOT EXISTS (SELECT po_distribution_id
2218 FROM po_distributions_archive_all poad
2219 WHERE pod.po_distribution_id=poad.po_distribution_id))
2220 UNION ALL
2221 SELECT line_location_id
2222 FROM po_line_locations_all
2223 WHERE po_line_id = p_line_id
2224 AND p_action_level = PO_DOCUMENT_CANCEL_PVT.c_entity_level_LINE
2225 AND 0= (SELECT Count(1)
2226 FROM po_line_locations_all poll
2227 WHERE po_line_id=p_line_id
2228 AND NOT EXISTS (SELECT line_location_id
2229 FROM po_line_locations_archive_all poall
2230 WHERE poll.line_location_id=poall.line_location_id))
2231 AND 0= (SELECT Count(1)
2232 FROM po_distributions_all pod
2233 WHERE pod.po_line_id=p_line_id
2234 AND NOT EXISTS (SELECT po_distribution_id
2235 FROM po_distributions_archive_all poad
2236 WHERE pod.po_distribution_id=poad.po_distribution_id))
2237
2238
2239 UNION ALL
2240 SELECT line_location_id
2241 FROM po_line_locations_all
2242 WHERE po_header_id = p_doc_id
2243 AND p_doc_type <> PO_DOCUMENT_CANCEL_PVT.c_doc_type_RELEASE
2244 AND p_action_level = PO_DOCUMENT_CANCEL_PVT.c_entity_level_HEADER
2245 AND 0= (SELECT Count(1)
2246 FROM po_line_locations_all poll
2247 WHERE po_header_id = p_doc_id
2248 AND NOT EXISTS (SELECT line_location_id
2249 FROM po_line_locations_archive_all poall
2250 WHERE poll.line_location_id=poall.line_location_id))
2251 AND 0= (SELECT Count(1)
2252 FROM po_distributions_all pod
2253 WHERE pod.po_header_id=p_doc_id
2254 AND NOT EXISTS (SELECT po_distribution_id
2255 FROM po_distributions_archive_all poad
2256 WHERE pod.po_distribution_id=poad.po_distribution_id))
2257
2258
2259
2260 UNION ALL
2261 SELECT line_location_id
2262 FROM po_line_locations_all
2263 WHERE po_release_id = p_doc_id
2264 AND p_doc_type = PO_DOCUMENT_CANCEL_PVT.c_doc_type_RELEASE
2265 AND p_action_level = PO_DOCUMENT_CANCEL_PVT.c_entity_level_HEADER
2266 AND 0= (SELECT Count(1)
2267 FROM po_line_locations_all poll
2268 WHERE po_release_id = p_doc_id
2269 AND NOT EXISTS (SELECT line_location_id
2270 FROM po_line_locations_archive_all poall
2271 WHERE poll.line_location_id=poall.line_location_id))
2272 AND 0= (SELECT Count(1)
2273 FROM po_distributions_all pod
2274 WHERE pod.po_release_id=p_doc_id
2275 AND NOT EXISTS (SELECT po_distribution_id
2276 FROM po_distributions_archive_all poad
2277 WHERE pod.po_distribution_id=poad.po_distribution_id)));
2278
2279 x_msg_type :='W';
2280
2281 IF d_debug_stmt THEN
2282 PO_DEBUG.debug_var(d_module, l_progress, 'x_msg_name', x_msg_name);
2283 END IF;
2284
2285
2286 EXCEPTION
2287 -- If more than one shipment has non approved change
2288 WHEN Too_Many_Rows THEN
2289 x_msg_type :='W';
2290 x_msg_name :='PO_CHANGED_CANT_CAN_MULTI_WARN';
2291
2292 WHEN No_Data_Found then
2293 BEGIN
2294
2295 l_progress :='002';
2296
2297 -- If the Archive does not exists and the docuemnt is not Approved
2298 -- Or if the encumbrance is On and any of the enc. related field is modified
2299 -- then do not allow cancel action , ask user to undo the changes
2300 SELECT 'PO_CHANGED_CANT_CANCEL'
2301 INTO x_msg_name
2302 FROM po_line_locations_all poll,
2303 po_distributions_all pod ---<BUG :13503748>--
2304 WHERE poll.line_location_id = pod.line_location_id
2305 AND (l_po_encumbrance_flag = 'Y' AND pod.encumbered_flag = 'N')
2306 ---<BUG :13503748>--
2307 AND Nvl(poll.approved_flag,'N') <>'Y'
2308 AND ((NOT EXISTS (SELECT 'Archive Exists'
2309 FROM po_line_locations_archive_all poall
2310 WHERE poll.line_location_id=poall.line_location_id)
2311 OR (0 <> (SELECT Count(1)
2312 FROM po_distributions_all pod
2313 WHERE pod.line_location_id=poll.line_location_id
2314 AND NOT EXISTS (SELECT po_distribution_id
2315 FROM po_distributions_archive_all poad
2316 WHERE pod.po_distribution_id=poad.po_distribution_id)
2317 )
2318 ))
2319
2320 OR(l_po_encumbrance_flag = 'Y'
2321 AND (EXISTS (SELECT 'Enc Columns Changed'
2322 FROM po_line_locations_archive_all poall
2323 WHERE poll.line_location_id=poall.line_location_id
2324 AND poall.latest_external_flag ='Y'
2325 AND (nvl(poll.price_override,0) <> Nvl(poall.price_override,0)
2326 OR Nvl(poll.quantity,0) <> Nvl(poall.quantity,0)
2327 OR Nvl(poll.amount,0) <> Nvl(poall.amount,0)))
2328 OR EXISTS (SELECT 'Enc Amount Changed'
2329 FROM po_distributions_all pod,
2330 po_distributions_archive_all poad
2331 WHERE pod.po_distribution_id=poad.po_distribution_id
2332 AND pod.line_location_id=poll.line_location_id
2333 AND poad.latest_external_flag ='Y'
2334 AND (Nvl(poad.encumbered_amount,0)<>Nvl(pod.encumbered_amount,0)
2335 OR Nvl(poad.rate,0)<>Nvl(pod.rate,0)
2336 OR Nvl(poad.quantity_ordered,0)<>Nvl(pod.quantity_ordered,0)
2337 OR Nvl(poad.amount_ordered,0)<>Nvl(pod.amount_ordered,0)
2338 OR Nvl(poad.nonrecoverable_tax,0)<>Nvl(pod.nonrecoverable_tax,0))
2339 )
2340 )
2341 )
2342 )
2343 AND ROWNUM<2
2344 AND poll.line_location_id IN
2345 ( SELECT line_location_id
2346 FROM po_line_locations_all
2347 WHERE line_location_id = p_line_location_id
2348 AND p_action_level = PO_DOCUMENT_CANCEL_PVT.c_entity_level_SHIPMENT
2349 UNION ALL
2350 SELECT line_location_id
2351 FROM po_line_locations_all
2352 WHERE po_line_id = p_line_id
2353 AND p_action_level = PO_DOCUMENT_CANCEL_PVT.c_entity_level_LINE
2354 UNION ALL
2355 SELECT line_location_id
2356 FROM po_line_locations_all
2357 WHERE po_header_id = p_doc_id
2358 AND p_doc_type <> PO_DOCUMENT_CANCEL_PVT.c_doc_type_RELEASE
2359 AND p_action_level = PO_DOCUMENT_CANCEL_PVT.c_entity_level_HEADER
2360 UNION ALL
2361 SELECT line_location_id
2362 FROM po_line_locations_all
2363 WHERE po_release_id = p_doc_id
2364 AND p_doc_type = PO_DOCUMENT_CANCEL_PVT.c_doc_type_RELEASE
2365 AND p_action_level = PO_DOCUMENT_CANCEL_PVT.c_entity_level_HEADER);
2366 x_msg_type :='E';
2367
2368 IF d_debug_stmt THEN
2369 PO_DEBUG.debug_var(d_module, l_progress, 'x_msg_name', x_msg_name);
2370 END IF;
2371
2372 EXCEPTION
2373 WHEN No_Data_Found THEN
2374 x_msg_name :=NULL;
2375 IF d_debug_stmt THEN
2376 PO_DEBUG.debug_var(d_module, l_progress, 'Setting x_msg as Null','');
2377 END IF;
2378
2379 WHEN OTHERS THEN
2380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2381 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
2382
2383 END;
2384
2385 WHEN OTHERS THEN
2386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2387 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
2388 END;
2389
2390 EXCEPTION
2391 WHEN FND_API.G_EXC_ERROR THEN
2392 x_return_status := FND_API.G_RET_STS_ERROR;
2393 x_msg_name :=sqlerrm;
2394 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2395 x_msg_name :=sqlerrm;
2396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2397 WHEN OTHERS THEN
2398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2399 x_msg_name :=sqlerrm;
2400 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
2401
2402 END CHECK_REV_DIFF;
2403
2404 END PO_DOCUMENT_REVISION_GRP;
[Home] [Help]