1 PACKAGE BODY PO_DOCUMENT_REVISION_GRP AS
2 /* $Header: POXDOCRB.pls 120.4.12010000.2 2008/08/13 06:19:29 lswamina 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',
359 x_different => x_different);
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>
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
480
477 x_different := 'Y'; --<CancelPO FPJ>
478 return FALSE;
479 end if;
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
606 DESCRIPTION : Called from Check_PO_PA_Revision function.
603 /*******************************************************************
604 FUNCTION NAME: Release_Archive_Check
605
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 -- Bug 3388218 Start
699 Begin
700 Select 'Y'
701 into l_accepted_flag
702 from po_acceptances_v pav,
703 po_headers poh
704 where poh.po_header_id=p_doc_id
705 and poh.po_header_id=pav.po_header_id
706 and pav.revision_num= poh.revision_num
707 and pav.accepted_flag='Y';
708 Exception
709 when others then
710 NULL;
711 End;
712 -- Bug 3388218 End
713 /*Bug5154626: cancel action on the PO's in approved state errors out on which
714 Mass update buyer program is run before to update buyer name.
715 Hence donot use the agent_id comparision for cancel flow*/
716
717 Select 'Y'
718 INTO x_different
719 from sys.dual
720 where exists(
721 select null
722 FROM PO_HEADERS POH,
723 PO_HEADERS_ARCHIVE POHA
724 WHERE POH.po_header_id = p_doc_id
725 AND POH.po_header_id = POHA.po_header_id (+)
726 AND POHA.latest_external_flag (+) = 'Y'
727 AND (
728 ( POHA.po_header_id IS NULL)
729 OR ( (POH.agent_id <> POHA.agent_id) AND (p_chk_cancel_flag='Y'))
730 OR (POH.vendor_site_id <> POHA.vendor_site_id)
731 OR (POH.vendor_site_id IS NULL
732 AND POHA.vendor_site_id IS NOT NULL)
733 OR (POH.vendor_site_id IS NOT NULL
734 AND POHA.vendor_site_id IS NULL)
735 OR (POH.vendor_contact_id <> POHA.vendor_contact_id)
736 OR (POH.vendor_contact_id IS NULL
737 AND POHA.vendor_contact_id IS NOT NULL)
738 OR (POH.vendor_contact_id IS NOT NULL
739 AND POHA.vendor_contact_id IS NULL)
743 OR (POH.ship_to_location_id IS NOT NULL
740 OR (POH.ship_to_location_id <> POHA.ship_to_location_id)
741 OR (POH.ship_to_location_id IS NULL
742 AND POHA.ship_to_location_id IS NOT NULL)
744 AND POHA.ship_to_location_id IS NULL)
745 OR (POH.bill_to_location_id <> POHA.bill_to_location_id)
746 OR (POH.bill_to_location_id IS NULL
747 AND POHA.bill_to_location_id IS NOT NULL)
748 OR (POH.bill_to_location_id IS NOT NULL
749 AND POHA.bill_to_location_id IS NULL)
750 OR (POH.terms_id <> POHA.terms_id)
751 OR (POH.terms_id IS NULL
752 AND POHA.terms_id IS NOT NULL)
753 OR (POH.terms_id IS NOT NULL
754 AND POHA.terms_id IS NULL)
755 OR (POH.ship_via_lookup_code <>
756 POHA.ship_via_lookup_code)
757 OR (POH.ship_via_lookup_code IS NULL
758 AND POHA.ship_via_lookup_code IS NOT NULL)
759 OR (POH.ship_via_lookup_code IS NOT NULL
760 AND POHA.ship_via_lookup_code IS NULL)
761 OR (POH.fob_lookup_code <> POHA.fob_lookup_code)
762 OR (POH.fob_lookup_code IS NULL
763 AND POHA.fob_lookup_code IS NOT NULL)
764 OR (POH.fob_lookup_code IS NOT NULL
765 AND POHA.fob_lookup_code IS NULL)
766 OR (POH.freight_terms_lookup_code <>
767 POHA.freight_terms_lookup_code)
768 OR (POH.freight_terms_lookup_code IS NULL
769 AND POHA.freight_terms_lookup_code IS NOT NULL)
770 OR (POH.freight_terms_lookup_code IS NOT NULL
771 AND POHA.freight_terms_lookup_code IS NULL)
772 -- <INBOUND LOGISTICS FPJ START>
773 OR (POH.shipping_control <>
774 POHA.shipping_control)
775 OR (POH.shipping_control IS NULL
776 AND POHA.shipping_control IS NOT NULL)
777 OR (POH.shipping_control IS NOT NULL
778 AND POHA.shipping_control IS NULL)
779 -- <INBOUND LOGISTICS FPJ END>
780 OR (POH.blanket_total_amount <>
781 POHA.blanket_total_amount)
782 OR (POH.blanket_total_amount IS NULL
783 AND POHA.blanket_total_amount IS NOT NULL)
784 OR (POH.blanket_total_amount IS NOT NULL
785 AND POHA.blanket_total_amount IS NULL)
786 OR (POH.note_to_vendor <> POHA.note_to_vendor)
787 OR (POH.note_to_vendor IS NULL
788 AND POHA.note_to_vendor IS NOT NULL)
789 OR (POH.note_to_vendor IS NOT NULL
790 AND POHA.note_to_vendor IS NULL)
791 OR (POH.confirming_order_flag <>
792 POHA.confirming_order_flag)
793 OR (POH.confirming_order_flag IS NULL
794 AND POHA.confirming_order_flag IS NOT NULL)
795 OR (POH.confirming_order_flag IS NOT NULL
796 AND POHA.confirming_order_flag IS NULL)
797 -- Start Bug 3659223: Clean up logic, and correctly handle
798 -- revisioning for PO rejected during signature process.
799 -- Replaced bug fix for bug 3388218
800 OR ((POH.acceptance_required_flag <> POHA.acceptance_required_flag)
801 AND (POH.acceptance_required_flag <> 'N'))
802 OR (POHA.acceptance_required_flag in ('Y','D')
803 AND POH.acceptance_required_flag ='N'
804 AND (nvl(l_accepted_flag,'X') <> 'Y'))
805 -- End Bug 3659223
806 OR (POH.acceptance_required_flag IS NULL
807 AND POHA.acceptance_required_flag IS NOT NULL)
808 OR (POH.acceptance_required_flag IS NOT NULL
809 AND POHA.acceptance_required_flag IS NULL)
810 OR (POH.acceptance_due_date <> POHA.acceptance_due_date)
811 OR (POH.acceptance_due_date IS NULL
812 AND POHA.acceptance_due_date IS NOT NULL
813 AND nvl(l_accepted_flag,'N')='N' -- Bug 3498816
814 -- Bug 3659223: Do not revision for Doc and Sig, as
815 -- accepting/rejecting will null out the date.
816 AND nvl(POH.acceptance_required_flag, 'X') <> 'S')
817 OR (POH.acceptance_due_date IS NOT NULL
818 AND POHA.acceptance_due_date IS NULL)
819 OR (POH.amount_limit <> POHA.amount_limit)
820 OR (POH.amount_limit IS NULL
821 AND POHA.amount_limit IS NOT NULL)
822 OR (POH.amount_limit IS NOT NULL
823 AND POHA.amount_limit IS NULL)
824 OR (POH.start_date <> POHA.start_date)
825 OR (POH.start_date IS NULL
826 AND POHA.start_date IS NOT NULL)
827 OR (POH.start_date IS NOT NULL
828 AND POHA.start_date IS NULL)
829 OR (POH.end_date <> POHA.end_date)
830 OR (POH.end_date IS NULL
831 AND POHA.end_date IS NOT NULL)
832 OR (POH.end_date IS NOT NULL
833 AND POHA.end_date IS NULL)
834 OR (p_chk_cancel_flag = 'Y' AND --<CancelPO FPJ>
835 ((POH.cancel_flag <> POHA.cancel_flag)
836 OR (POH.cancel_flag IS NULL
837 AND POHA.cancel_flag IS NOT NULL)
838 OR (POH.cancel_flag IS NOT NULL
839 AND POHA.cancel_flag IS NULL)))
840
844 AND POHA.conterms_articles_upd_date IS NOT NULL)
841 --<CONTERMS FPJ START> dependency popo.odf , poarc.odf
842 OR (POH.conterms_articles_upd_date <> POHA.conterms_articles_upd_date)
843 OR (POH.conterms_articles_upd_date IS NULL
845 OR (POH.conterms_articles_upd_date IS NOT NULL
846 AND POHA.conterms_articles_upd_date IS NULL)
847 OR (POH.conterms_deliv_upd_date <> POHA.conterms_deliv_upd_date)
848 OR (POH.conterms_deliv_upd_date IS NULL
849 AND POHA.conterms_deliv_upd_date IS NOT NULL)
850 OR (POH.conterms_deliv_upd_date IS NOT NULL
851 AND POHA.conterms_deliv_upd_date IS NULL)
852
853 --<CONTERMS FPJ END>
854 ));
855
856 --< Shared Proc FPJ Start >
857 ELSIF (p_element = 'PORCH_GA_ORG_ASSIGN') AND
858 (p_doc_subtype IN ('BLANKET', 'CONTRACT'))
859 THEN
860
861 l_progress := '015';
862
863 --SQL What: Check latest external archived records with
864 -- the current records
865 --SQL Why: If certain columns are different, a new
866 -- revision is needed
867 SELECT 'Y'
868 INTO x_different
869 FROM po_ga_org_assignments pgoa,
870 po_ga_org_assignments_archive pgoaa
871 WHERE pgoa.po_header_id = p_doc_id
872 AND pgoa.po_header_id = pgoaa.po_header_id (+)
873 AND pgoa.organization_id = pgoaa.organization_id (+)
874 AND pgoaa.latest_external_flag (+) = 'Y'
875 AND ( (pgoaa.po_header_id IS NULL)
876 OR (pgoaa.organization_id <> pgoa.organization_id)
877 OR (pgoaa.purchasing_org_id <> pgoa.purchasing_org_id)
878 OR (pgoaa.vendor_site_id <> pgoa.vendor_site_id)
879 OR (pgoaa.enabled_flag <> pgoa.enabled_flag)
880 )
881 AND ROWNUM = 1;
882
883 --< Shared Proc FPJ End >
884
885 elsif (p_element = 'PORCH_LINES') then --p_element='PORCH_HEADER'*/
886
887 l_progress := '020';
888 if (p_doc_subtype = 'BLANKET') then
889 Select 'Y'
890 INTO x_different
891 from sys.dual
892 where exists(
893 select null
894 FROM PO_LINES POL,
895 PO_LINES_ARCHIVE POLA
896 WHERE POL.po_header_id = p_doc_id
897 AND (p_line_id IS NULL OR POL.po_line_id = p_line_id) --<CancelPO FPJ>
898 AND POL.po_line_id = POLA.po_line_id (+)
899 AND POLA.latest_external_flag (+) = 'Y'
900 AND (
901 (POLA.po_line_id is NULL)
902 OR (POL.line_num <> POLA.line_num)
903 OR (POL.item_id <> POLA.item_id)
904 OR (POL.item_id IS NULL
905 AND POLA.item_id IS NOT NULL)
906 OR (POL.item_id IS NOT NULL
907 AND POLA.item_id IS NULL)
908 -- SERVICES FPJ Start
909 OR (POL.job_id <> POLA.job_id)
910 OR (POL.job_id IS NULL
911 AND POLA.job_id IS NOT NULL)
912 OR (POL.job_id IS NOT NULL
913 AND POLA.job_id IS NULL)
914 OR (POL.amount <> POLA.amount)
915 OR (POL.amount IS NULL
916 AND POLA.amount IS NOT NULL)
917 OR (POL.amount IS NOT NULL
918 AND POLA.amount IS NULL)
919 -- SERVICES FPJ Start
920 OR (POL.item_revision <> POLA.item_revision)
921 OR (POL.item_revision IS NULL
922 AND POLA.item_revision IS NOT NULL)
923 OR (POL.item_revision IS NOT NULL
924 AND POLA.item_revision IS NULL)
925 OR (POL.item_description <>
926 POLA.item_description)
927 OR (POL.item_description IS NULL
928 AND POLA.item_description IS NOT NULL)
929 OR (POL.item_description IS NOT NULL
930 AND POLA.item_description IS NULL)
931 OR (POL.unit_meas_lookup_code <>
932 POLA.unit_meas_lookup_code)
933 OR (POL.unit_meas_lookup_code IS NULL
934 AND POLA.unit_meas_lookup_code IS NOT NULL)
935 OR (POL.unit_meas_lookup_code IS NOT NULL
936 AND POLA.unit_meas_lookup_code IS NULL)
937 OR (POL.quantity_committed <>
938 POLA.quantity_committed)
939 OR (POL.quantity_committed IS NULL
940 AND POLA.quantity_committed IS NOT NULL)
941 OR (POL.quantity_committed IS NOT NULL
942 AND POLA.quantity_committed IS NULL)
943 OR (POL.committed_amount <>
944 POLA.committed_amount)
945 OR (POL.committed_amount IS NULL
946 AND POLA.committed_amount IS NOT NULL)
947 OR (POL.committed_amount IS NOT NULL
948 AND POLA.committed_amount IS NULL)
949 OR (POL.unit_price <> POLA.unit_price)
950 OR (POL.unit_price IS NULL
954 -- Bug 3471211
951 AND POLA.unit_price IS NOT NULL)
952 OR (POL.unit_price IS NOT NULL
953 AND POLA.unit_price IS NULL)
955 OR (POL.not_to_exceed_price <> POLA.not_to_exceed_price)
956 OR (POL.not_to_exceed_price IS NULL
957 AND POLA.not_to_exceed_price IS NOT NULL)
958 OR (POL.not_to_exceed_price IS NOT NULL
959 AND POLA.not_to_exceed_price IS NULL)
960 OR (POL.un_number_id <> POLA.un_number_id)
961 OR (POL.un_number_id IS NULL
962 AND POLA.un_number_id IS NOT NULL)
963 OR (POL.un_number_id IS NOT NULL
964 AND POLA.un_number_id IS NULL)
965 OR (POL.hazard_class_id <> POLA.hazard_class_id)
966 OR (POL.hazard_class_id IS NULL
967 AND POLA.hazard_class_id IS NOT NULL)
968 OR (POL.hazard_class_id IS NOT NULL
969 AND POLA.hazard_class_id IS NULL)
970 OR (POL.note_to_vendor <> POLA.note_to_vendor)
971 OR (POL.note_to_vendor IS NULL
972 AND POLA.note_to_vendor IS NOT NULL)
973 OR (POL.note_to_vendor IS NOT NULL
974 AND POLA.note_to_vendor IS NULL)
975 OR (POL.note_to_vendor <> POLA.note_to_vendor)
976 OR (POL.note_to_vendor IS NULL
977 AND POLA.note_to_vendor IS NOT NULL)
978 OR (POL.note_to_vendor IS NOT NULL
979 AND POLA.note_to_vendor IS NULL)
980 OR (POL.from_header_id <> POLA.from_header_id)
981 OR (POL.from_header_id IS NULL
982 AND POLA.from_header_id IS NOT NULL)
983 OR (POL.from_header_id IS NOT NULL
984 AND POLA.from_header_id IS NULL)
985 OR (POL.from_line_id <> POLA.from_line_id)
986 OR (POL.from_line_id IS NULL
987 AND POLA.from_line_id IS NOT NULL)
988 OR (POL.from_line_id IS NOT NULL
989 AND POLA.from_line_id IS NULL)
990 -- Bug 3305753: Closed code need not be compared
991 -- Since close action is an internal action and
992 -- should not affect the document revision.
993 -- ((POL.closed_code <> POLA.closed_code)
994 --OR (POL.closed_code IS NULL
995 -- AND POLA.closed_code IS NOT NULL)
996 --OR (POL.closed_code IS NOT NULL
997 -- AND POLA.closed_code IS NULL))
998 OR (POL.vendor_product_num <>
999 POLA.vendor_product_num)
1000 OR (POL.vendor_product_num IS NULL
1001 AND POLA.vendor_product_num IS NOT NULL)
1002 OR (POL.vendor_product_num IS NOT NULL
1003 AND POLA.vendor_product_num IS NULL)
1004 -- <GC FPJ>
1005 -- Removing CONTRACT_NUM check because
1006 -- Blanket line cannot reference a contract
1007 OR (POL.price_type_lookup_code <>
1008 POLA.price_type_lookup_code)
1009 OR (POL.price_type_lookup_code IS NULL
1010 AND POLA.price_type_lookup_code IS NOT NULL)
1011 OR (POL.price_type_lookup_code IS NOT NULL
1012 AND POLA.price_type_lookup_code IS NULL)
1013 OR (POL.expiration_date IS NULL
1014 AND POLA.expiration_date IS NOT NULL)
1015 OR (POL.expiration_date IS NOT NULL
1016 AND POLA.expiration_date IS NULL)
1017 OR (trunc(POL.expiration_date) <>
1018 trunc(POLA.expiration_date))
1019 OR (p_chk_cancel_flag = 'Y' AND --<CancelPO FPJ>
1020 ((POL.cancel_flag <> POLA.cancel_flag)
1021 OR (POL.cancel_flag IS NULL
1022 AND POLA.cancel_flag IS NOT NULL)
1023 OR (POL.cancel_flag IS NOT NULL
1024 AND POLA.cancel_flag IS NULL)))));
1025
1026
1027 else -- (p_doc_subtype = 'BLANKET') */
1028 Select 'Y'
1029 INTO x_different
1030 from sys.dual
1031 where exists(
1032 select null
1033 FROM PO_LINES POL,
1034 PO_LINES_ARCHIVE POLA
1035 WHERE POL.po_header_id = p_doc_id
1036 AND (p_line_id IS NULL OR POL.po_line_id = p_line_id) --<CancelPO FPJ>
1037 AND POL.po_line_id = POLA.po_line_id (+)
1038 AND POLA.latest_external_flag (+) = 'Y'
1039 AND (
1040 (POLA.po_line_id is NULL)
1041 OR (POL.line_num <> POLA.line_num)
1042 OR (POL.item_id <> POLA.item_id)
1043 OR (POL.item_id IS NULL
1044 AND POLA.item_id IS NOT NULL)
1045 OR (POL.item_id IS NOT NULL
1046 AND POLA.item_id IS NULL)
1047 -- SERVICES FPJ Start
1048 OR (POL.job_id <> POLA.job_id)
1049 OR (POL.job_id IS NULL
1050 AND POLA.job_id IS NOT NULL)
1051 OR (POL.job_id IS NOT NULL
1052 AND POLA.job_id IS NULL)
1056 OR (POL.amount IS NOT NULL
1053 OR (POL.amount <> POLA.amount)
1054 OR (POL.amount IS NULL
1055 AND POLA.amount IS NOT NULL)
1057 AND POLA.amount IS NULL)
1058 OR (POL.expiration_date IS NULL
1059 AND POLA.expiration_date IS NOT NULL)
1060 OR (POL.expiration_date IS NOT NULL
1061 AND POLA.expiration_date IS NULL)
1062 OR (trunc(POL.expiration_date) <>
1063 trunc(POLA.expiration_date))
1064 OR (POL.start_date IS NULL
1065 AND POLA.start_date IS NOT NULL)
1066 OR (POL.start_date IS NOT NULL
1067 AND POLA.start_date IS NULL)
1068 OR (trunc(POL.start_date) <>
1069 trunc(POLA.start_date))
1070 OR (POL.contractor_first_name <>
1071 POLA.contractor_first_name)
1072 OR (POL.contractor_first_name IS NULL
1073 AND POLA.contractor_first_name IS NOT NULL)
1074 OR (POL.contractor_first_name IS NOT NULL
1075 AND POLA.contractor_first_name IS NULL)
1076 OR (POL.contractor_last_name <>
1077 POLA.contractor_last_name)
1078 OR (POL.contractor_last_name IS NULL
1079 AND POLA.contractor_last_name IS NOT NULL)
1080 OR (POL.contractor_last_name IS NOT NULL
1081 AND POLA.contractor_last_name IS NULL)
1082 -- SERVICES FPJ Start
1083 OR (POL.item_revision <> POLA.item_revision)
1084 OR (POL.item_revision IS NULL
1085 AND POLA.item_revision IS NOT NULL)
1086 OR (POL.item_revision IS NOT NULL
1087 AND POLA.item_revision IS NULL)
1088 OR (POL.item_description <>
1089 POLA.item_description)
1090 OR (POL.item_description IS NULL
1091 AND POLA.item_description IS NOT NULL)
1092 OR (POL.item_description IS NOT NULL
1093 AND POLA.item_description IS NULL)
1094 OR (POL.unit_meas_lookup_code <>
1095 POLA.unit_meas_lookup_code)
1096 OR (POL.unit_meas_lookup_code IS NULL
1097 AND POLA.unit_meas_lookup_code IS NOT NULL)
1098 OR (POL.unit_meas_lookup_code IS NOT NULL
1099 AND POLA.unit_meas_lookup_code IS NULL)
1100 OR (p_chk_cancel_flag = 'Y' AND POL.quantity <> POLA.quantity) --<CancelPO FPJ>
1101 OR (POL.quantity IS NULL
1102 AND POLA.quantity IS NOT NULL)
1103 OR (POL.quantity_committed <>
1104 POLA.quantity_committed)
1105 OR (POL.quantity_committed IS NULL
1106 AND POLA.quantity_committed IS NOT NULL)
1107 OR (POL.quantity_committed IS NOT NULL
1108 AND POLA.quantity_committed IS NULL)
1109 OR (POL.committed_amount <>
1110 POLA.committed_amount)
1111 OR (POL.committed_amount IS NULL
1112 AND POLA.committed_amount IS NOT NULL)
1113 OR (POL.committed_amount IS NOT NULL
1114 AND POLA.committed_amount IS NULL)
1115 OR (POL.unit_price <> POLA.unit_price)
1116 OR (POL.unit_price IS NULL
1117 AND POLA.unit_price IS NOT NULL)
1118 OR (POL.unit_price IS NOT NULL
1119 AND POLA.unit_price IS NULL)
1120 -- Bug 3471211
1121 OR (POL.not_to_exceed_price <> POLA.not_to_exceed_price)
1122 OR (POL.not_to_exceed_price IS NULL
1123 AND POLA.not_to_exceed_price IS NOT NULL)
1124 OR (POL.not_to_exceed_price IS NOT NULL
1125 AND POLA.not_to_exceed_price IS NULL)
1126 OR (POL.un_number_id <> POLA.un_number_id)
1127 OR (POL.un_number_id IS NULL
1128 AND POLA.un_number_id IS NOT NULL)
1129 OR (POL.un_number_id IS NOT NULL
1130 AND POLA.un_number_id IS NULL)
1131 OR (POL.hazard_class_id <>
1132 POLA.hazard_class_id)
1133 OR (POL.hazard_class_id IS NULL
1134 AND POLA.hazard_class_id IS NOT NULL)
1135 OR (POL.hazard_class_id IS NOT NULL
1136 AND POLA.hazard_class_id IS NULL)
1137 OR (POL.note_to_vendor <> POLA.note_to_vendor)
1138 OR (POL.note_to_vendor IS NULL
1139 AND POLA.note_to_vendor IS NOT NULL)
1140 OR (POL.note_to_vendor IS NOT NULL
1141 AND POLA.note_to_vendor IS NULL)
1142 OR (POL.note_to_vendor <> POLA.note_to_vendor)
1143 OR (POL.note_to_vendor IS NULL
1144 AND POLA.note_to_vendor IS NOT NULL)
1145 OR (POL.note_to_vendor IS NOT NULL
1146 AND POLA.note_to_vendor IS NULL)
1147 OR (POL.from_header_id <> POLA.from_header_id)
1148 OR (POL.from_header_id IS NULL
1149 AND POLA.from_header_id IS NOT NULL)
1150 OR (POL.from_header_id IS NOT NULL
1151 AND POLA.from_header_id IS NULL)
1155 OR (POL.from_line_id IS NOT NULL
1152 OR (POL.from_line_id <> POLA.from_line_id)
1153 OR (POL.from_line_id IS NULL
1154 AND POLA.from_line_id IS NOT NULL)
1156 AND POLA.from_line_id IS NULL)
1157 -- Bug 3305753:Closed code need not be compared
1158 -- Since close action is an internal action and
1159 -- should not affect the document revision.
1160 -- ((POL.closed_code <> POLA.closed_code)
1161 -- OR (POL.closed_code IS NULL
1162 -- AND POLA.closed_code IS NOT NULL)
1163 -- OR (POL.closed_code IS NOT NULL
1164 -- AND POLA.closed_code IS NULL))
1165 OR (POL.vendor_product_num <>
1166 POLA.vendor_product_num)
1167 OR (POL.vendor_product_num IS NULL
1168 AND POLA.vendor_product_num IS NOT NULL)
1169 OR (POL.vendor_product_num IS NOT NULL
1170 AND POLA.vendor_product_num IS NULL)
1171 -- <GC FPJ>
1172 -- Compare contract_id instead of contract_num
1173 OR (POL.contract_id <> POLA.contract_id)
1174 OR (POL.contract_id IS NULL
1175 AND POLA.contract_id IS NOT NULL)
1176 OR (POL.contract_id IS NOT NULL
1177 AND POLA.contract_id IS NULL)
1178 OR (POL.price_type_lookup_code <>
1179 POLA.price_type_lookup_code)
1180 OR (POL.price_type_lookup_code IS NULL
1181 AND POLA.price_type_lookup_code IS NOT NULL)
1182 OR (POL.price_type_lookup_code IS NOT NULL
1183 AND POLA.price_type_lookup_code IS NULL)
1184 OR (p_chk_cancel_flag = 'Y' AND --<CancelPO FPJ>
1185 ((POL.cancel_flag <> POLA.cancel_flag)
1186 OR (POL.cancel_flag IS NULL
1187 AND POLA.cancel_flag IS NOT NULL)
1188 OR (POL.cancel_flag IS NOT NULL
1189 AND POLA.cancel_flag IS NULL)))
1190 -- <Complex Work R12 Start>
1191 OR (POL.retainage_rate <> POLA.retainage_rate)
1192 OR (POL.retainage_rate IS NULL
1193 AND POLA.retainage_rate IS NOT NULL)
1194 OR (POL.retainage_rate IS NOT NULL
1195 AND POLA.retainage_rate IS NULL)
1196 OR (POL.max_retainage_amount <> POLA.max_retainage_amount)
1197 OR (POL.max_retainage_amount IS NULL
1198 AND POLA.max_retainage_amount IS NOT NULL)
1199 OR (POL.max_retainage_amount IS NOT NULL
1200 AND POLA.max_retainage_amount IS NULL)
1201 OR (POL.progress_payment_rate <> POLA.progress_payment_rate)
1202 OR (POL.progress_payment_rate IS NULL
1203 AND POLA.progress_payment_rate IS NOT NULL)
1204 OR (POL.progress_payment_rate IS NOT NULL
1205 AND POLA.progress_payment_rate IS NULL)
1206 OR (POL.recoupment_rate <> POLA.recoupment_rate)
1207 OR (POL.recoupment_rate IS NULL
1208 AND POLA.recoupment_rate IS NOT NULL)
1209 OR (POL.recoupment_rate IS NOT NULL
1210 AND POLA.recoupment_rate IS NULL)
1211 -- <Complex Work R12 End>
1212 ));
1213
1214 end if; -- (p_doc_subtype = 'BLANKET') */
1215
1216
1217 elsif(p_element = 'PORCH_SHIPMENTS') then --p_element='PORCH_HEADER'*/
1218 Select 'Y'
1219 INTO x_different
1220 from sys.dual
1221 where exists(
1222 select null
1223 FROM PO_LINE_LOCATIONS POLL,
1224 PO_LINE_LOCATIONS_ARCHIVE POLLA
1225 WHERE POLL.po_header_id = p_doc_id
1226 AND POLL.po_release_id is null -- Bug 3876235
1227 AND (p_line_id IS NULL OR POLL.po_line_id = p_line_id) --<CancelPO FPJ>
1228 AND (p_line_location_id IS NULL OR POLL.line_location_id = p_line_location_id) --<CancelPO FPJ>
1229 AND POLL.line_location_id = POLLA.line_location_id (+)
1230 AND POLLA.latest_external_flag (+) = 'Y'
1231 AND (
1232 (POLLA.line_location_id is NULL)
1233 OR (POLL.quantity <> POLLA.quantity)
1234 OR (POLL.quantity IS NULL AND POLLA.quantity IS NOT NULL)
1235 OR (POLL.quantity IS NOT NULL AND POLLA.quantity IS NULL)
1236 -- SERVICES FPJ Start
1237 OR (POLL.amount <> POLLA.amount)
1238 OR (POLL.amount IS NULL AND POLLA.amount IS NOT NULL)
1239 OR (POLL.amount IS NOT NULL AND POLLA.amount IS NULL)
1240 -- SERVICES FPJ Start
1241 OR (POLL.ship_to_location_id <>
1242 POLLA.ship_to_location_id)
1243 OR (POLL.ship_to_location_id IS NULL
1244 AND POLLA.ship_to_location_id IS NOT NULL)
1245 OR (POLL.ship_to_location_id IS NOT NULL
1246 AND POLLA.ship_to_location_id IS NULL)
1247 OR (POLL.need_by_date <> POLLA.need_by_date)
1248 OR (POLL.need_by_date IS NULL
1249 AND POLLA.need_by_date IS NOT NULL)
1250 OR (POLL.need_by_date IS NOT NULL
1251 AND POLLA.need_by_date IS NULL)
1255 OR (POLL.promised_date IS NOT NULL
1252 OR (POLL.promised_date <> POLLA.promised_date)
1253 OR (POLL.promised_date IS NULL
1254 AND POLLA.promised_date IS NOT NULL)
1256 AND POLLA.promised_date IS NULL)
1257 OR (POLL.last_accept_date <> POLLA.last_accept_date)
1258 OR (POLL.last_accept_date IS NULL
1259 AND POLLA.last_accept_date IS NOT NULL)
1260 OR (POLL.last_accept_date IS NOT NULL
1261 AND POLLA.last_accept_date IS NULL)
1262 OR (POLL.price_override <> POLLA.price_override)
1263 OR (POLL.price_override IS NULL
1264 AND POLLA.price_override IS NOT NULL)
1265 OR (POLL.price_override IS NOT NULL
1266 AND POLLA.price_override IS NULL) --BUG7286203 REMOVED THE CHECK FOR TAXCODE ID
1267 -- <Complex Work R12 Start>
1268 OR (POLL.payment_type <> POLLA.payment_type)
1269 OR (POLL.payment_type IS NULL
1270 AND POLLA.payment_type IS NOT NULL)
1271 OR (POLL.payment_type IS NOT NULL
1272 AND POLLA.payment_type IS NULL)
1273 OR (POLL.description <> POLLA.description)
1274 OR (POLL.description IS NULL
1275 AND POLLA.description IS NOT NULL)
1276 OR (POLL.description IS NOT NULL
1277 AND POLLA.description IS NULL)
1278 OR (POLL.work_approver_id <> POLLA.work_approver_id)
1279 OR (POLL.work_approver_id IS NULL
1280 AND POLLA.work_approver_id IS NOT NULL)
1281 OR (POLL.work_approver_id IS NOT NULL
1282 AND POLLA.work_approver_id IS NULL)
1283 -- <Complex Work R12 End>
1284 OR (POLL.shipment_num <> POLLA.shipment_num)
1285 OR (POLL.shipment_num IS NULL
1286 AND POLLA.shipment_num IS NOT NULL)
1287 OR (POLL.shipment_num IS NOT NULL
1288 AND POLLA.shipment_num IS NULL)
1289 OR (POLL.sales_order_update_date <> POLLA.sales_order_update_date)
1290 OR (POLL.sales_order_update_date IS NULL
1291 AND POLLA.sales_order_update_date IS NOT NULL)
1292 OR (POLL.sales_order_update_date IS NOT NULL
1293 AND POLLA.sales_order_update_date IS NULL)
1294 OR (p_chk_cancel_flag = 'Y' AND --<CancelPO FPJ>
1295 ((POLL.cancel_flag <> POLLA.cancel_flag)
1296 OR (POLL.cancel_flag IS NULL
1297 AND POLLA.cancel_flag IS NOT NULL)
1298 OR (POLL.cancel_flag IS NOT NULL
1299 AND POLLA.cancel_flag IS NULL)))));
1300
1301 elsif (p_element = 'PORCH_PBREAK') then --p_element='PORCH_HEADER'*/
1302 /*
1303 ** note that change sin price discount will be reflected in
1304 ** changes in price_override, hence price_discount is not
1305 ** considered below.
1306 ** Also changes to ship_to_org will not cause a revision change.
1307 ** since print changed orders report does not cover that case.
1308 */
1309 Select 'Y'
1310 INTO x_different
1311 from sys.dual
1312 where exists(
1313 select null
1314 FROM PO_LINE_LOCATIONS POLL,
1315 PO_LINE_LOCATIONS_ARCHIVE POLLA
1316 WHERE POLL.po_header_id = p_doc_id
1317 AND POLL.po_release_id is null -- Bug 3876235
1318 AND (p_line_id IS NULL OR POLL.po_line_id = p_line_id) --<CancelPO FPJ>
1319 AND (p_line_location_id IS NULL OR POLL.line_location_id = p_line_location_id) --<CancelPO FPJ>
1320 AND POLL.line_location_id = POLLA.line_location_id (+)
1321 AND POLLA.latest_external_flag (+) = 'Y'
1322 AND (
1323 (POLLA.line_location_id is NULL)
1324 OR (POLL.quantity <> POLLA.quantity)
1325 OR (POLL.quantity IS NULL AND POLLA.quantity IS NOT NULL)
1326 OR (POLL.quantity IS NOT NULL AND POLLA.quantity IS NULL)
1327 OR (POLL.ship_to_location_id <>
1328 POLLA.ship_to_location_id)
1329 OR (POLL.ship_to_location_id IS NULL
1330 AND POLLA.ship_to_location_id IS NOT NULL)
1331 OR (POLL.ship_to_location_id IS NOT NULL
1332 AND POLLA.ship_to_location_id IS NULL)
1333 OR (POLL.price_override <> POLLA.price_override)
1334 OR (POLL.price_override IS NULL
1335 AND POLLA.price_override IS NOT NULL)
1336 OR (POLL.price_override IS NOT NULL
1337 AND POLLA.price_override IS NULL)
1338 OR (POLL.shipment_num <> POLLA.shipment_num)
1339 OR (POLL.shipment_num IS NULL
1340 AND POLLA.shipment_num IS NOT NULL)
1341 OR (POLL.shipment_num IS NOT NULL
1342 AND POLLA.shipment_num IS NULL)
1343 /* <TIMEPHASED FPI START> */
1344 OR (POLL.start_date <> POLLA.start_date)
1345 OR (POLL.start_date is null AND POLLA.start_date is not null)
1346 OR (POLL.start_date is not null AND POLLA.start_date is null)
1347 OR (POLL.end_date <> POLLA.end_date)
1348 OR (POLL.end_date is null AND POLLA.end_date is not null)
1352 -- SERVICES FPJ Start
1349 OR (POLL.end_date is not null AND POLLA.end_date is null)));
1350 /* <TIMEPHASED FPI END> */
1351
1353 -- Comparison for the Price differentials entity
1354
1355 ELSIF (p_element = 'PORCH_LINE_PRICE_DIFF') THEN
1356
1357 --SQL What: Check latest external archived records with
1358 -- the current records
1359 --SQL Why: If certain columns are different, a new
1360 -- revision is needed
1361
1362 SELECT 'Y'
1363 INTO x_different
1364 FROM po_price_differentials pdf,
1365 po_price_differentials_archive pdfa,
1366 po_lines_all pol
1367 WHERE pol.po_header_id = p_doc_id
1368 AND pol.po_line_id = pdf.entity_id
1369 AND pdf.entity_type in ('PO LINE', 'BLANKET LINE')
1370 AND pdf.price_differential_id = pdfa.price_differential_id (+)
1371 AND pdfa.latest_external_flag (+) = 'Y'
1372 AND (
1373 ( pdfa.price_differential_id IS NULL )
1374 OR ( pdf.price_differential_num <> pdfa.price_differential_num )
1375 OR ( pdf.price_type <> pdfa.price_type )
1376 OR ( ( pdf.multiplier <> pdfa.multiplier )
1377 OR ( pdf.multiplier IS NULL AND pdfa.multiplier IS NOT NULL )
1378 OR ( pdf.multiplier IS NOT NULL AND pdfa.multiplier IS NULL ) )
1379 OR ( ( pdf.max_multiplier <> pdfa.max_multiplier )
1380 OR ( pdf.max_multiplier IS NULL AND pdfa.max_multiplier IS NOT NULL )
1381 OR ( pdf.max_multiplier IS NOT NULL AND pdfa.max_multiplier IS NULL ) )
1382 OR ( ( pdf.min_multiplier <> pdfa.min_multiplier)
1383 OR ( pdf.min_multiplier IS NULL AND pdfa.min_multiplier IS NOT NULL )
1384 OR ( pdf.min_multiplier IS NOT NULL AND pdfa.min_multiplier IS NULL ) )
1385 OR ( ( pdf.enabled_flag <> pdfa.enabled_flag )
1386 OR ( pdf.enabled_flag IS NULL AND pdfa.enabled_flag IS NOT NULL )
1387 OR ( pdf.enabled_flag IS NOT NULL AND pdfa.enabled_flag IS NULL ) )
1388 );
1389
1390 ELSIF (p_element = 'PORCH_PB_PRICE_DIFF') THEN
1391
1392 --SQL What: Check latest external archived records with
1393 -- the current records
1394 --SQL Why: If certain columns are different, a new
1395 -- revision is needed
1396
1397 SELECT 'Y'
1398 INTO x_different
1399 FROM po_price_differentials pdf,
1400 po_price_differentials_archive pdfa,
1401 po_line_locations_all poll
1402 WHERE poll.po_header_id = p_doc_id
1403 AND poll.line_location_id = pdf.entity_id
1404 AND pdf.entity_type = 'PRICE BREAK'
1405 AND pdf.price_differential_id = pdfa.price_differential_id (+)
1406 AND pdfa.latest_external_flag (+) = 'Y'
1407 AND (
1408 ( pdfa.price_differential_id IS NULL )
1409 OR ( pdf.price_differential_num <> pdfa.price_differential_num )
1410 OR ( pdf.price_type <> pdfa.price_type )
1411 OR ( ( pdf.multiplier <> pdfa.multiplier )
1412 OR ( pdf.multiplier IS NULL AND pdfa.multiplier IS NOT NULL )
1413 OR ( pdf.multiplier IS NOT NULL AND pdfa.multiplier IS NULL ) )
1414 OR ( ( pdf.max_multiplier <> pdfa.max_multiplier )
1415 OR ( pdf.max_multiplier IS NULL AND pdfa.max_multiplier IS NOT NULL )
1416 OR ( pdf.max_multiplier IS NOT NULL AND pdfa.max_multiplier IS NULL ) )
1417 OR ( ( pdf.min_multiplier <> pdfa.min_multiplier)
1418 OR ( pdf.min_multiplier IS NULL AND pdfa.min_multiplier IS NOT NULL )
1419 OR ( pdf.min_multiplier IS NOT NULL AND pdfa.min_multiplier IS NULL ) )
1420 OR ( ( pdf.enabled_flag <> pdfa.enabled_flag )
1421 OR ( pdf.enabled_flag IS NULL AND pdfa.enabled_flag IS NOT NULL )
1422 OR ( pdf.enabled_flag IS NOT NULL AND pdfa.enabled_flag IS NULL ) )
1423 );
1424
1425 -- SERVICES FPJ End
1426
1427 elsif (p_element = 'PORCH_DISTRIBUTIONS') then --p_element='PORCH_HEADER'*/
1428 Select 'Y'
1429 INTO x_different
1430 from sys.dual
1431 where exists(
1432 select null
1433 FROM PO_DISTRIBUTIONS POD,
1434 PO_DISTRIBUTIONS_ARCHIVE PODA
1435 WHERE POD.po_header_id = p_doc_id
1436 AND (p_line_id IS NULL OR POD.po_line_id = p_line_id) --<CancelPO FPJ>
1437 AND (p_line_location_id IS NULL OR POD.line_location_id = p_line_location_id) --<CancelPO FPJ>
1438 AND POD.po_distribution_id =
1439 PODA.po_distribution_id (+)
1440 AND PODA.latest_external_flag (+) = 'Y'
1441 AND (
1445 AND PODA.quantity_ordered IS NOT NULL)
1442 (PODA.po_distribution_id is NULL)
1443 OR (POD.quantity_ordered <> PODA.quantity_ordered)
1444 OR (POD.quantity_ordered IS NULL
1446 OR (POD.quantity_ordered IS NOT NULL
1447 AND PODA.quantity_ordered IS NULL)
1448 -- SERVICES FPJ
1449 OR (POD.amount_ordered <> PODA.amount_ordered)
1450 OR (POD.amount_ordered IS NULL
1451 AND PODA.amount_ordered IS NOT NULL)
1452 OR (POD.amount_ordered IS NOT NULL
1453 AND PODA.amount_ordered IS NULL)
1454 -- SERVICES FPJ
1455 OR (POD.deliver_to_person_id <>
1456 PODA.deliver_to_person_id)
1457 OR (POD.deliver_to_person_id IS NULL
1458 AND PODA.deliver_to_person_id IS NOT NULL)
1459 OR (POD.deliver_to_person_id IS NOT NULL
1460 AND PODA.deliver_to_person_id IS NULL)
1461 /* OR (POD.distribution_num <> PODA.distribution_num)*/
1462 ));--Bug7286203 REMOVED THE CHECK FOR RECOVERY_RATE
1463
1464
1465 end if; -- type = PORCH_PO and p_element = 'PORCH_HEADER'*/
1466
1467 elsif (p_type = 'PORCH_RELEASE') then -- (type = 'PORCH_PO')*/
1468
1469 l_progress := '030';
1470 if (p_element = 'PORCH_HEADER') then
1471
1472 -- Bug 3388218 Start
1473 Begin
1474 Select 'Y'
1475 into l_accepted_flag
1476 from po_acceptances_v pav,
1477 po_releases por
1478 where por.po_release_id=p_doc_id
1479 and por.po_release_id=pav.po_release_id
1480 and pav.revision_num= por.revision_num
1481 and pav.accepted_flag='Y';
1482 Exception
1483 when others then
1484 NULL;
1485 End;
1486 -- Bug 3388218 End
1487
1488 /*Bug5154626: cancel action on the releases in approved state errors
1489 out on which Mass update buyer program is run to update buyer name.
1490 Hence donot use the agent_id comparision for cancel flow*/
1491
1492 Select 'Y'
1493 INTO x_different
1494 from sys.dual
1495 where exists(
1496 select null
1497 FROM PO_RELEASES POR,
1498 PO_RELEASES_ARCHIVE PORA
1499 WHERE POR.po_release_id = p_doc_id
1500 AND POR.po_release_id = PORA.po_release_id
1501 AND PORA.latest_external_flag (+) = 'Y'
1502 AND (
1503 (PORA.po_release_id IS NULL)
1504 OR (POR.release_num <> PORA.release_num)
1505 OR((POR.agent_id <> PORA.agent_id) AND (p_chk_cancel_flag='Y'))
1506 OR (POR.release_date <> PORA.release_date)
1507 -- <INBOUND LOGISTICS FPJ START>
1508 OR (POR.shipping_control <>
1509 PORA.shipping_control)
1510 OR (POR.shipping_control IS NULL
1511 AND PORA.shipping_control IS NOT NULL)
1512 OR (POR.shipping_control IS NOT NULL
1513 AND PORA.shipping_control IS NULL)
1514 -- <INBOUND LOGISTICS FPJ END>
1515 -- Start Bug 3388218
1516 OR ((POR.acceptance_required_flag <>
1517 PORA.acceptance_required_flag) AND NOT
1518 (nvl(POR.acceptance_required_flag,'X') ='N' AND
1519 nvl(PORA.acceptance_required_flag,'X') = 'Y' AND
1520 nvl(l_accepted_flag,'X')='Y'))
1521 OR (POR.acceptance_required_flag = 'Y' AND
1522 PORA.acceptance_required_flag ='Y' AND
1523 nvl(l_accepted_flag,'X')='Y')
1524 -- End Bug 3388218
1525 OR (POR.acceptance_required_flag IS NULL
1526 AND PORA.acceptance_required_flag IS NOT NULL)
1527 OR (POR.acceptance_required_flag IS NOT NULL
1528 AND PORA.acceptance_required_flag IS NULL)
1529 OR (POR.acceptance_due_date <>
1530 PORA.acceptance_due_date)
1531 OR (POR.acceptance_due_date IS NULL
1532 AND PORA.acceptance_due_date IS NOT NULL
1533 AND nvl(l_accepted_flag,'N')='N') -- Bug 3498816
1534 OR (POR.acceptance_due_date IS NOT NULL
1535 AND PORA.acceptance_due_date IS NULL)));
1536
1537 elsif(p_element = 'PORCH_SHIPMENTS') then --p_element='PORCH_HEADER'*/
1538 Select 'Y'
1539 INTO x_different
1540 from sys.dual
1541 where exists(
1542 select null
1543 FROM PO_LINE_LOCATIONS POLL,
1544 PO_LINE_LOCATIONS_ARCHIVE POLLA
1545 WHERE POLL.po_release_id = p_doc_id
1546 AND (p_line_location_id IS NULL OR POLL.line_location_id = p_line_location_id) --<CancelPO FPJ>
1547 AND POLL.line_location_id = POLLA.line_location_id (+)
1548 AND POLLA.latest_external_flag (+) = 'Y'
1549 AND (
1550 (POLLA.line_location_id is NULL)
1551 OR (POLL.quantity <> POLLA.quantity)
1552 OR (POLL.quantity IS NULL
1553 AND POLLA.quantity IS NOT NULL)
1554 OR (POLL.quantity IS NOT NULL
1555 AND POLLA.quantity IS NULL)
1559 OR (POLL.amount IS NOT NULL AND POLLA.amount IS NULL)
1556 -- SERVICES FPJ Start
1557 OR (POLL.amount <> POLLA.amount)
1558 OR (POLL.amount IS NULL AND POLLA.amount IS NOT NULL)
1560 -- SERVICES FPJ Start
1561 OR (POLL.ship_to_location_id <>
1562 POLLA.ship_to_location_id)
1563 OR (POLL.ship_to_location_id IS NULL
1564 AND POLLA.ship_to_location_id IS NOT NULL)
1565 OR (POLL.ship_to_location_id IS NOT NULL
1566 AND POLLA.ship_to_location_id IS NULL)
1567 OR (POLL.need_by_date <> POLLA.need_by_date)
1568 OR (POLL.need_by_date IS NULL
1569 AND POLLA.need_by_date IS NOT NULL)
1570 OR (POLL.need_by_date IS NOT NULL
1571 AND POLLA.need_by_date IS NULL)
1572 OR (POLL.promised_date <> POLLA.promised_date)
1573 OR (POLL.promised_date IS NULL
1574 AND POLLA.promised_date IS NOT NULL)
1575 OR (POLL.promised_date IS NOT NULL
1576 AND POLLA.promised_date IS NULL)
1577 OR (POLL.last_accept_date <> POLLA.last_accept_date)
1578 OR (POLL.last_accept_date IS NULL
1579 AND POLLA.last_accept_date IS NOT NULL)
1580 OR (POLL.last_accept_date IS NOT NULL
1581 AND POLLA.last_accept_date IS NULL)
1582 OR (POLL.price_override <> POLLA.price_override)
1583 OR (POLL.price_override IS NULL
1584 AND POLLA.price_override IS NOT NULL)
1585 OR (POLL.price_override IS NOT NULL
1586 AND POLLA.price_override IS NULL) --BUG7286203 REMOVED THE CHECK FOR TAXCODE ID
1587 OR (POLL.shipment_num <> POLLA.shipment_num)
1588 OR (POLL.shipment_num IS NULL
1589 AND POLLA.shipment_num IS NOT NULL)
1590 OR (POLL.shipment_num IS NOT NULL
1591 AND POLLA.shipment_num IS NULL)
1592 OR (POLL.sales_order_update_date <> POLLA.sales_order_update_date)
1593 OR (POLL.sales_order_update_date IS NULL
1594 AND POLLA.sales_order_update_date IS NOT NULL)
1595 OR (POLL.sales_order_update_date IS NOT NULL
1596 AND POLLA.sales_order_update_date IS NULL)
1597 OR (p_chk_cancel_flag = 'Y' AND --<CancelPO FPJ>
1598 ((POLL.cancel_flag <> POLLA.cancel_flag)
1599 OR (POLL.cancel_flag IS NULL
1600 AND POLLA.cancel_flag IS NOT NULL)
1601 OR (POLL.cancel_flag IS NOT NULL
1602 AND POLLA.cancel_flag IS NULL)))));
1603
1604 elsif (p_element = 'PORCH_DISTRIBUTIONS') then --p_element='PORCH_HEADER'*/
1605 Select 'Y'
1606 INTO x_different
1607 from sys.dual
1608 where exists(
1609 select null
1610 FROM PO_DISTRIBUTIONS POD,
1611 PO_DISTRIBUTIONS_ARCHIVE PODA
1612 WHERE POD.po_release_id = p_doc_id
1613 AND (p_line_location_id IS NULL OR POD.line_location_id = p_line_location_id) --<CancelPO FPJ>
1614 AND POD.po_distribution_id =
1615 PODA.po_distribution_id (+)
1616 AND PODA.latest_external_flag (+) = 'Y'
1617 AND (
1618 (PODA.po_distribution_id is NULL)
1619 OR (POD.quantity_ordered <> PODA.quantity_ordered)
1620 OR (POD.quantity_ordered IS NULL
1621 AND PODA.quantity_ordered IS NOT NULL)
1622 OR (POD.quantity_ordered IS NOT NULL
1623 AND PODA.quantity_ordered IS NULL)
1624 -- SERVICES FPJ
1625 OR (POD.amount_ordered <> PODA.amount_ordered)
1626 OR (POD.amount_ordered IS NULL
1627 AND PODA.amount_ordered IS NOT NULL)
1628 OR (POD.amount_ordered IS NOT NULL
1629 AND PODA.amount_ordered IS NULL)
1630 -- SERVICES FPJ
1631 OR (POD.deliver_to_person_id <>
1632 PODA.deliver_to_person_id)
1633 OR (POD.deliver_to_person_id IS NULL
1634 AND PODA.deliver_to_person_id IS NOT NULL)
1635 OR (POD.deliver_to_person_id IS NOT NULL
1636 AND PODA.deliver_to_person_id IS NULL)
1637 /* OR (POD.distribution_num <> PODA.distribution_num) */
1638 ));--Bug7286203 REMOVED THE CHECK FOR RECOVERY_RATE
1639
1640
1641 end if; -- p_type = PORCH_RELEASE and p_element = 'PORCH_HEADER'*/
1642 end if; -- p_type = 'PORCH_PO'*/
1643
1644 EXCEPTION
1645 when no_data_found then
1646 x_different := 'N'; /* This is not really an error */
1647 when others then
1648 PO_MESSAGE_S.SQL_ERROR(routine => 'Compare_Table',
1649 location => l_progress,
1650 error_code => SQLCODE);
1651
1652 END Compare_Table;
1653
1654 --<CancelPO FPJ Start>
1655 -------------------------------------------------------------------------------
1656 --Start of Comments
1657 --Name: Compare
1658 --Function:
1659 -- Checks if a PO/PA/Release Header/Line/Shipment are different compared to
1660 -- its archived copy. The output parameter x_different indicates if they are different.
1661 -- All attributes that cause revision change except cancel_flag/closed_code are compared
1662 --Parameters:
1663 --IN:
1664 --p_api_version
1665 -- Standard API Version
1666 --p_doc_id
1667 -- The Document ID of the PO/PA/Release
1668 --p_doc_type
1669 -- The Document Type indicating PO, PA, or RELEASE
1670 --p_doc_subtype
1671 -- The Document Subtype
1672 --p_line_id
1673 -- The Line ID if the Line/Shipment needs to be compared
1674 --p_line_location_id
1675 -- The Shipment ID if the Shipment needs to be compared
1676 --OUT:
1677 --x_different
1678 -- Indicates if the entity Header/Line/Shipment is different.
1679 -- One of the Following Values is returned:
1680 -- Y If Archival exists and differences exist
1681 -- N If Archival exists and no differences exist
1682 -- M If archival record is missing.
1683 --x_return_status
1684 -- Standard API Return Status S, U, E
1685 --Testing:
1686 -- Test for all Types of Documents and Entity Levels
1687 --End of Comments
1688 -------------------------------------------------------------------------------
1689 PROCEDURE Compare(
1690 p_api_version IN NUMBER,
1691 p_doc_id IN NUMBER,
1692 p_doc_type IN VARCHAR2,
1693 p_doc_subtype IN VARCHAR2,
1694 p_line_id IN NUMBER,
1695 p_line_location_id IN NUMBER,
1696 x_different OUT NOCOPY Varchar2,
1697 x_return_status OUT NOCOPY VARCHAR2
1698 ) IS
1699
1700 l_api_name CONSTANT VARCHAR(30) := 'COMPARE';
1701 l_api_version CONSTANT NUMBER := 1.0;
1702 l_progress VARCHAR2(3) := '000';
1703 l_need_new_revision boolean := FALSE;
1704 l_Archive_Record_Exists VARCHAR2(1);
1705
1706 BEGIN
1707
1708 IF g_fnd_debug = 'Y' THEN
1709 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1710 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
1711 || l_progress, 'Entering Procedure '||l_api_name || ' DocType:' || p_doc_type
1712 || ' DocId:' || p_doc_id|| ' LineId:' || p_line_id
1713 || ' LineLocId:' || p_line_location_id);
1714 END IF;
1715 END IF;
1716
1717 l_progress := '010';
1718 --Standard call to check for call compatibility
1719 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1720 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1721 END IF;
1722
1723 x_return_status := FND_API.G_RET_STS_SUCCESS;
1724
1725 if ((p_doc_type = 'PO') OR (p_doc_type = 'PA')) THEN
1726
1727 l_progress := '015';
1728 BEGIN
1729 select 'Y'
1730 into l_Archive_Record_Exists
1731 from po_headers_archive
1732 where po_header_id = p_doc_id and rownum = 1;
1733 EXCEPTION
1734 WHEN NO_DATA_FOUND THEN
1735 l_Archive_Record_Exists := 'N';
1736 END;
1737
1738 IF l_Archive_Record_Exists = 'N' THEN
1739 x_different := 'M'; --Return M If archival record is missing.
1740
1741 IF g_fnd_debug = 'Y' THEN
1742 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1743 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
1744 || l_progress, 'x_different=M, No Archived Record Exists');
1745 END IF;
1746 END IF;
1747
1748 return;
1749 END IF;
1750
1751 l_progress := '020';
1752 IF p_line_id is null THEN -- Compare Header If not at line level
1753 l_need_new_revision :=
1754 Check_PO_PA_Revision(
1755 p_doc_type => p_doc_type,
1756 p_doc_subtype => p_doc_subtype,
1757 p_doc_id => p_doc_id,
1758 p_table_name => 'HEADER',
1759 p_line_id => p_line_id,
1760 p_line_location_id => p_line_location_id,
1761 p_chk_cancel_flag => 'N',
1762 x_different => x_different);
1763
1764 IF x_different = 'Y' THEN
1765
1766 IF g_fnd_debug = 'Y' THEN
1767 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1768 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
1769 || l_progress, 'PO Header is Different');
1770 END IF;
1771 END IF;
1772
1773 return;
1774 END IF;
1775 END IF;
1776
1777 l_progress := '030';
1778 IF p_line_location_id is null THEN -- Compare Line If not at Shipment level
1779 l_need_new_revision :=
1780 Check_PO_PA_Revision(
1781 p_doc_type => p_doc_type,
1785 p_line_id => p_line_id,
1782 p_doc_subtype => p_doc_subtype,
1783 p_doc_id => p_doc_id,
1784 p_table_name => 'LINES',
1786 p_line_location_id => p_line_location_id,
1787 p_chk_cancel_flag => 'N',
1788 x_different => x_different);
1789
1790 IF x_different = 'Y' THEN
1791
1792 IF g_fnd_debug = 'Y' THEN
1793 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1794 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
1795 || l_progress, 'PO Line is Different');
1796 END IF;
1797 END IF;
1798
1799 return;
1800 END IF;
1801 END IF;
1802
1803 l_progress := '040';
1804 -- Compare Shipments for any level: Header/Line/Shipment
1805 l_need_new_revision :=
1806 Check_PO_PA_Revision(
1807 p_doc_type => p_doc_type,
1808 p_doc_subtype => p_doc_subtype,
1809 p_doc_id => p_doc_id,
1810 p_table_name => 'SHIPMENTS',
1811 p_line_id => p_line_id,
1812 p_line_location_id => p_line_location_id,
1813 p_chk_cancel_flag => 'N',
1814 x_different => x_different);
1815
1816 IF x_different = 'Y' THEN
1817
1818 IF g_fnd_debug = 'Y' THEN
1819 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1820 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
1821 || l_progress, 'PO Shipment is Different');
1822 END IF;
1823 END IF;
1824
1825 return;
1826 END IF;
1827
1828 l_progress := '050';
1829 -- Compare Distributions for any level: Header/Line/Shipment
1830 l_need_new_revision :=
1831 Check_PO_PA_Revision(
1832 p_doc_type => p_doc_type,
1833 p_doc_subtype => p_doc_subtype,
1834 p_doc_id => p_doc_id,
1835 p_table_name => 'DISTRIBUTIONS',
1836 p_line_id => p_line_id,
1837 p_line_location_id => p_line_location_id,
1838 p_chk_cancel_flag => 'N',
1839 x_different => x_different);
1840
1841 elsif ((p_doc_type = 'RELEASE')) THEN
1842
1843 l_progress := '055';
1844 BEGIN
1845 select 'Y'
1846 into l_Archive_Record_Exists
1847 from po_releases_archive
1848 where po_release_id = p_doc_id and rownum = 1;
1849 EXCEPTION
1850 WHEN NO_DATA_FOUND THEN
1851 l_Archive_Record_Exists := 'N';
1852 END;
1853
1854 IF l_Archive_Record_Exists = 'N' THEN
1855 x_different := 'N';
1856
1857 IF g_fnd_debug = 'Y' THEN
1858 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1859 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
1860 || l_progress, 'No Archived Record Exists');
1861 END IF;
1862 END IF;
1863
1864 return;
1865 END IF;
1866
1867 l_progress := '060';
1868 IF p_line_location_id is null THEN -- Compare Header If not at Shipment level
1869 l_need_new_revision :=
1870 Check_Release_Revision(
1871 p_doc_type => p_doc_type,
1872 p_doc_subtype => p_doc_subtype,
1873 p_doc_id => p_doc_id,
1874 p_table_name => 'HEADER',
1875 p_line_location_id => p_line_location_id,
1876 p_chk_cancel_flag => 'N',
1877 x_different => x_different);
1878
1879 IF x_different = 'Y' THEN
1880
1881 IF g_fnd_debug = 'Y' THEN
1882 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1883 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
1884 || l_progress, 'Release Header is Different');
1885 END IF;
1886 END IF;
1887
1888 return;
1889 END IF;
1890 END IF;
1891
1892 l_progress := '070';
1893 -- Compare Shipments for any level: Release Header/Shipment
1894 l_need_new_revision :=
1895 Check_Release_Revision(
1896 p_doc_type => p_doc_type,
1897 p_doc_subtype => p_doc_subtype,
1898 p_doc_id => p_doc_id,
1899 p_table_name => 'SHIPMENTS',
1900 p_line_location_id => p_line_location_id,
1901 p_chk_cancel_flag => 'N',
1902 x_different => x_different);
1903
1904 IF x_different = 'Y' THEN
1905
1906 IF g_fnd_debug = 'Y' THEN
1907 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1908 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
1909 || l_progress, 'Releqase Shipment is Different');
1910 END IF;
1911 END IF;
1912
1913 return;
1914 END IF;
1915
1916 l_progress := '080';
1917 -- Compare Distributions for any level: Release Header/Shipment
1918 l_need_new_revision :=
1919 Check_Release_Revision(
1920 p_doc_type => p_doc_type,
1921 p_doc_subtype => p_doc_subtype,
1922 p_doc_id => p_doc_id,
1923 p_table_name => 'DISTRIBUTIONS',
1924 p_line_location_id => p_line_location_id,
1925 p_chk_cancel_flag => 'N',
1926 x_different => x_different);
1927
1928 else
1929 x_different := 'N';
1930 end if; /* (p_doc_type = 'PO') OR (p_doc_type = 'PA') */
1931
1932 IF g_fnd_debug = 'Y' THEN
1933 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1934 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name||'.'
1935 || l_progress, 'Final x_Different ' || x_different);
1936 END IF;
1937 END IF;
1938
1939 EXCEPTION
1940 WHEN FND_API.G_EXC_ERROR THEN
1941 x_return_status := FND_API.G_RET_STS_ERROR;
1942 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1943 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1944 WHEN OTHERS THEN
1945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1946 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
1947
1948 END Compare;
1949 --<CancelPO FPJ End>
1950
1951 END PO_DOCUMENT_REVISION_GRP;
[Home] [Help]