[Home] [Help]
PACKAGE BODY: APPS.PO_DRAFT_APPR_STATUS_PVT
Source
1 PACKAGE BODY PO_DRAFT_APPR_STATUS_PVT AS
2 /* $Header: PO_DRAFT_APPR_STATUS_PVT.plb 120.7.12020000.3 2013/05/27 15:36:11 srpantha ship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_DRAFT_APPR_STATUS_PVT');
6
7 -------------------------------------------------------
8 ----------- PRIVATE PROCEDURES PROTOTYPE --------------
9 -------------------------------------------------------
10
11 PROCEDURE handle_shipment_approved_flag
12 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE,
13 x_change_status IN OUT NOCOPY BOOLEAN
14 );
15
16 PROCEDURE val_auth_status_header
17 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE,
18 x_change_status IN OUT NOCOPY BOOLEAN
19 );
20
21 PROCEDURE val_auth_status_line
22 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE,
23 x_change_status IN OUT NOCOPY BOOLEAN
24 );
25
26 PROCEDURE val_auth_status_line_loc
27 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE,
28 x_change_status IN OUT NOCOPY BOOLEAN,
29 x_changed_line_loc_list OUT NOCOPY PO_TBL_NUMBER
30 );
31
32 PROCEDURE val_auth_status_dist
33 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE,
34 x_change_status IN OUT NOCOPY BOOLEAN,
35 x_changed_line_loc_list OUT NOCOPY PO_TBL_NUMBER
36 );
37
38 PROCEDURE val_auth_status_org_assign
39 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE,
40 x_change_status IN OUT NOCOPY BOOLEAN
41 );
42
43 PROCEDURE val_auth_status_price_diff
44 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE,
45 x_change_status IN OUT NOCOPY BOOLEAN
46 );
47
48
49 -------------------------------------------------------
50 -------------- PUBLIC PROCEDURES ----------------------
51 -------------------------------------------------------
52
53 -----------------------------------------------------------------------
54 --Start of Comments
55 --Name: update_approval_status
56 --Pre-reqs: None
57 --Modifies:
58 --Locks:
59 -- None
60 --Function:
61 -- Changes header and shipment approval status in the transaction table
62 -- based on the changes recorded in draft table
63 --Parameters:
64 --IN:
65 --p_draft_info
66 -- Record structure holding change request information
67 --IN OUT:
68 --OUT:
69 --Returns:
70 --Notes:
71 --Testing:
72 --End of Comments
73 ------------------------------------------------------------------------
74 PROCEDURE update_approval_status
75 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE,
76 x_rebuild_attribs OUT NOCOPY BOOLEAN
77 ) IS
78
79 d_api_name CONSTANT VARCHAR2(30) := 'udpate_approval_status';
80 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
81 d_position NUMBER;
82
83 l_new_auth_status PO_HEADERS_ALL.authorization_status%TYPE;
84 l_new_approved_flag PO_HEADERS_ALL.approved_flag%TYPE;
85 l_orig_auth_status PO_HEADERS_ALL.authorization_status%TYPE;
86 l_orig_approved_flag PO_HEADERS_ALL.approved_flag%TYPE;
87 l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
88
89 l_change_status BOOLEAN := FALSE;
90 BEGIN
91
92 x_rebuild_attribs := FALSE; --Bug#5264722
93 d_position := 0;
94
95 IF (PO_LOG.d_proc) THEN
96 PO_LOG.proc_begin(d_module);
97 END IF;
98
99 IF (p_draft_info.new_document = FND_API.G_TRUE) THEN
100 RETURN;
101 END IF;
102
103 d_position := 10;
104
105 SELECT NVL(authorization_status, 'INCOMPLETE'),
106 NVL(approved_flag, 'N'),
107 type_lookup_code --Bug#5264722
108 INTO l_orig_auth_status,
109 l_orig_approved_flag,
110 l_type_lookup_code --Bug#5264722
111 FROM po_headers_all
112 WHERE po_header_id = p_draft_info.po_header_id;
113
114 d_position := 20;
115
116 IF (PO_LOG.d_stmt) THEN
117 PO_LOG.stmt(d_module, d_position, 'orig_auth_status', l_orig_auth_status);
118 PO_LOG.stmt(d_module, d_position, 'orig_approved_flag',
119 l_orig_approved_flag);
120 END IF;
121
122 IF (l_orig_auth_status = 'INCOMPLETE') THEN
123 RETURN;
124 END IF;
125
126 d_position := 30;
127
128 -- This procedure takes care of shipment and distribution records
129 handle_shipment_approved_flag
130 ( p_draft_info => p_draft_info,
131 x_change_status => l_change_status
132 );
133
134 -- if document status is 'REQUIRES REAPPROVAL', we know that the header
135 -- status will not be changed, so no need to check other entities that
136 -- can only affect header approval status
137
138 IF (l_orig_approved_flag = 'Y' OR
139 l_orig_auth_status = 'PRE-APPROVED') THEN
140
141 d_position := 40;
142
143 IF (NOT l_change_status) THEN
144 val_auth_status_header
145 ( p_draft_info => p_draft_info,
146 x_change_status => l_change_status
147 );
148 END IF;
149
150 d_position := 50;
151
152 IF (NOT l_change_status) THEN
153 val_auth_status_line
154 ( p_draft_info => p_draft_info,
155 x_change_status => l_change_status
156 );
157 END IF;
158
159 d_position := 60;
160
161 IF (NOT l_change_status) THEN
162 val_auth_status_org_assign
163 ( p_draft_info => p_draft_info,
164 x_change_status => l_change_status
165 );
166 END IF;
167
168 d_position := 70;
169
170 IF (NOT l_change_status) THEN
171 val_auth_status_price_diff
172 ( p_draft_info => p_draft_info,
173 x_change_status => l_change_status
174 );
175 END IF;
176
177 d_position := 80;
178
179 IF (l_change_status) THEN
180
181 IF (l_orig_auth_status = 'PRE-APPROVED') THEN
182 l_new_auth_status := 'IN PROCESS';
183 l_new_approved_flag := 'N';
184 ELSE
185 l_new_approved_flag := 'R';
186 l_new_auth_status := 'REQUIRES REAPPROVAL';
187 END IF;
188
189 IF (PO_LOG.d_stmt) THEN
190 PO_LOG.stmt(d_module, d_position, 'new_auth_status',
191 l_new_auth_status);
192 PO_LOG.stmt(d_module, d_position, 'new_approved_flag',
193 l_new_approved_flag);
194 END IF;
195
196 d_position := 90;
197
198 UPDATE po_headers_all
199 SET authorization_status = l_new_auth_status,
200 approved_flag = l_new_approved_flag,
201 last_update_date = SYSDATE
202 WHERE po_header_id = p_draft_info.po_header_id;
203
204 d_position := 100;
205 -- update draft table as well so that the merge statement would
206 -- not set the status back to its original one
207 UPDATE po_headers_draft_all
208 SET authorization_status = l_new_auth_status,
209 approved_flag = l_new_approved_flag,
210 last_update_date = SYSDATE
211 WHERE po_header_id = p_draft_info.po_header_id
212 AND draft_id = p_draft_info.draft_id;
213
214 -- <HTML Agreement R12 START>
215 -- Since approval status is changed, give the functional lock to
216 -- BUYER
217 PO_DRAFTS_PVT.lock_document
218 ( p_po_header_id => p_draft_info.po_header_id,
219 p_role => PO_GLOBAL.g_ROLE_BUYER,
220 p_role_user_id => FND_GLOBAL.user_id,
221 p_unlock_current => FND_API.G_FALSE
222 );
223 -- <HTML Agreement R12 END>
224 END IF;
225 END IF;
226
227 --Bug#5264722
228 d_position := 107;
229 if(l_orig_auth_status = 'APPROVED' AND l_change_status = FALSE
230 AND l_type_lookup_code in (PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET,
231 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION))
232 then
233 x_rebuild_attribs := TRUE;
234 end if;
235
236 d_position := 110;
237
238 EXCEPTION
239 WHEN OTHERS THEN
240 PO_MESSAGE_S.add_exc_msg
241 ( p_pkg_name => d_pkg_name,
242 p_procedure_name => d_api_name || '.' || d_position
243 );
244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
245 END update_approval_status;
246
247 -------------------------------------------------------
248 -------------- PRIVATE PROCEDURES ---------------------
249 -------------------------------------------------------
250
251 -----------------------------------------------------------------------
252 --Start of Comments
253 --Name: handle_shipment_approved_flag
254 --Pre-reqs: None
255 --Modifies:
256 --Locks:
257 -- None
258 --Function:
259 -- Checks shipment and distribution changes and see if approval status
260 -- change will be triggered. This procedure also determines the approved
261 -- flag value for shipments based on shipment and distribution changes
262 --Parameters:
263 --IN:
264 --p_draft_info
265 -- Record structure holding draft information
266 --IN OUT:
267 --x_change_status
268 -- This procedure sets this parameter to TRUE if status needs to be
269 -- changed
270 --OUT:
271 --Returns:
272 --Notes:
273 --Testing:
274 --End of Comments
275 ------------------------------------------------------------------------
276 PROCEDURE handle_shipment_approved_flag
277 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE,
278 x_change_status IN OUT NOCOPY BOOLEAN
279 ) IS
280
281 d_api_name CONSTANT VARCHAR2(30) := 'handle_shipment_approved_flag';
282 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
283 d_position NUMBER;
284
285 l_line_loc_list1 PO_TBL_NUMBER; -- list of line locations that need to
286 -- have approved_flag re-evaluated due to
287 -- line location changes
288
289 l_line_loc_list2 PO_TBL_NUMBER; -- list of line locations that need to have
290 -- approved_flag re-evaluated due to
291 -- distribution changes
292
293 BEGIN
294
295 d_position := 0;
296 IF (PO_LOG.d_proc) THEN
297 PO_LOG.proc_begin(d_module);
298 END IF;
299
300 val_auth_status_line_loc
301 ( p_draft_info => p_draft_info,
302 x_change_status => x_change_status,
303 x_changed_line_loc_list => l_line_loc_list1
304 );
305
306 d_position := 10;
307 IF (l_line_loc_list1 IS NOT NULL) THEN
308
309 IF (PO_LOG.d_stmt) THEN
310 PO_LOG.stmt(d_module, d_position,
311 'Update shipment approved flag for shipment changes');
312 END IF;
313
314 FORALL i IN 1..l_line_loc_list1.COUNT
315 UPDATE po_line_locations_draft_all
316 SET approved_flag = 'R'
317 WHERE line_location_id = l_line_loc_list1(i)
318 AND draft_id = p_draft_info.draft_id
319 AND approved_flag = 'Y';
320 END IF;
321
322 d_position := 20;
323 val_auth_status_dist
324 ( p_draft_info => p_draft_info,
325 x_change_status => x_change_status,
326 x_changed_line_loc_list => l_line_loc_list2
327 );
328
329 d_position := 30;
330 IF (l_line_loc_list2 IS NOT NULL) THEN
331 d_position := 40;
332
333 IF (PO_LOG.d_stmt) THEN
334 PO_LOG.stmt(d_module, d_position,
335 'Update shipment approved flag for distribution changes');
336 END IF;
337
338 FORALL i IN 1..l_line_loc_list2.COUNT
339 UPDATE po_line_locations_draft_all
340 SET approved_flag = 'R'
341 WHERE line_location_id = l_line_loc_list2(i)
342 AND draft_id = p_draft_info.draft_id
343 AND approved_flag = 'Y';
344
345 d_position := 50;
346
347 -- need to update transaction table as well because there may not be
348 -- shipment changes for the distribution being changed.
349 FORALL i IN 1..l_line_loc_list2.COUNT
350 UPDATE po_line_locations_all
351 SET approved_flag = 'R'
352 WHERE line_location_id = l_line_loc_list2(i)
353 AND approved_flag = 'Y';
354 END IF;
355
356 d_position := 60;
357 EXCEPTION
358 WHEN OTHERS THEN
359 PO_MESSAGE_S.add_exc_msg
360 ( p_pkg_name => d_pkg_name,
361 p_procedure_name => d_api_name || '.' || d_position
362 );
363 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
364 END handle_shipment_approved_flag;
365
366 -----------------------------------------------------------------------
367 --Start of Comments
368 --Name: val_auth_status_header
369 --Pre-reqs: None
370 --Modifies:
371 --Locks:
372 -- None
373 --Function:
374 -- Checks whether there are any changes at the header that trigger
375 -- approval status to be updated
376 --Parameters:
377 --IN:
378 --p_draft_info
379 -- Record structure holding draft information
380 --IN OUT:
381 --x_change_status
382 -- This procedure sets this parameter to TRUE if status needs to be
383 -- changed
384 --OUT:
385 --Returns:
386 --Notes:
387 --Testing:
388 --End of Comments
389 ------------------------------------------------------------------------
390 PROCEDURE val_auth_status_header
391 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE,
392 x_change_status IN OUT NOCOPY BOOLEAN
393 ) IS
394
395 d_api_name CONSTANT VARCHAR2(30) := 'val_auth_status_header';
396 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
397 d_position NUMBER;
398
399 l_different VARCHAR2(1);
400
401 BEGIN
402 d_position := 0;
403 IF (PO_LOG.d_proc) THEN
404 PO_LOG.proc_begin(d_module);
405 END IF;
406
407 IF (p_draft_info.headers_changed = FND_API.G_FALSE) THEN
408 RETURN;
409 END IF;
410
411 d_position := 10;
412 --SQL What: Compare attributes of the transaction and draft record. Return 'Y'
413 -- if any of the attributes is different
414 --SQL Why: Need to figure out if the change triggers a change to approval
415 -- status
416 SELECT MAX(FND_API.G_TRUE)
417 INTO l_different
418 FROM dual
419 WHERE EXISTS
420 ( SELECT NULL
421 FROM po_headers_draft_all PHD,
422 po_headers_all PH
423 WHERE PHD.po_header_id = p_draft_info.po_header_id
424 AND PHD.draft_id = p_draft_info.draft_id
425 AND NVL(PHD.delete_flag, 'N') = 'N'
426 AND NVL(PHD.change_accepted_flag, 'Y') = 'Y'
427 AND PHD.po_header_id = PH.po_header_id
428 AND
429 ( DECODE (PHD.agent_id, PH.agent_id, 'Y', 'N') = 'N'
430 OR DECODE (PHD.vendor_site_id, PH.vendor_site_id, 'Y', 'N') = 'N'
431 OR DECODE (PHD.vendor_contact_id, PH.vendor_contact_id, 'Y', 'N') = 'N'
432 OR DECODE (PHD.confirming_order_flag, PH.confirming_order_flag, 'Y', 'N') = 'N'
433 OR DECODE (PHD.ship_to_location_id, PH.ship_to_location_id, 'Y', 'N') = 'N'
434 OR DECODE (PHD.bill_to_location_id, PH.bill_to_location_id, 'Y', 'N') = 'N'
435 OR DECODE (PHD.terms_id, PH.terms_id, 'Y', 'N') = 'N'
436 OR DECODE (PHD.ship_via_lookup_code, PH.ship_via_lookup_code, 'Y', 'N') = 'N'
437 OR DECODE (PHD.fob_lookup_code, PH.fob_lookup_code, 'Y', 'N') = 'N'
438 OR DECODE (PHD.freight_terms_lookup_code, PH.freight_terms_lookup_code, 'Y', 'N') = 'N'
439 OR DECODE (PHD.note_to_vendor, PH.note_to_vendor, 'Y', 'N') = 'N'
440 OR DECODE (PHD.acceptance_required_flag, PH.acceptance_required_flag, 'Y', 'N') = 'N'
441 OR DECODE (PHD.blanket_total_amount, PH.blanket_total_amount, 'Y', 'N') = 'N'
442 OR DECODE (PHD.start_date, PH.start_date, 'Y', 'N') = 'N'
443 OR DECODE (PHD.end_date, PH.end_date, 'Y', 'N') = 'N'
444 OR DECODE (PHD.amount_limit, PH.amount_limit, 'Y', 'N') = 'N'
445 OR DECODE (PHD.conterms_articles_upd_date, PH.conterms_articles_upd_date, 'Y', 'N') = 'N'
446 OR DECODE (PHD.conterms_deliv_upd_date, PH.conterms_deliv_upd_date, 'Y', 'N') = 'N'
447 OR DECODE (PHD.shipping_control, PH.shipping_control, 'Y', 'N') = 'N'
448 )
449 );
450
451 d_position := 20;
452 IF (l_different = FND_API.G_TRUE) THEN
453
454 IF (PO_LOG.d_stmt) THEN
455 PO_LOG.stmt(d_module, d_position, 'header causes approval change');
456 END IF;
457
458 x_change_status := TRUE;
459 END IF;
460
461 EXCEPTION
462 WHEN OTHERS THEN
463 PO_MESSAGE_S.add_exc_msg
464 ( p_pkg_name => d_pkg_name,
465 p_procedure_name => d_api_name || '.' || d_position
466 );
467 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
468 END val_auth_status_header;
469
470 -----------------------------------------------------------------------
471 --Start of Comments
472 --Name: val_auth_status_line
473 --Pre-reqs: None
474 --Modifies:
475 --Locks:
476 -- None
477 --Function:
478 -- Checks whether there are any changes at the line level that trigger
479 -- approval status to be updated
480 --Parameters:
481 --IN:
482 --p_draft_info
483 -- Record structure holding draft information
484 --IN OUT:
485 --x_change_status
486 -- This procedure sets this parameter to TRUE if status needs to be
487 -- changed
488 --OUT:
489 --Returns:
490 --Notes:
491 --Testing:
492 --End of Comments
493 ------------------------------------------------------------------------
494 PROCEDURE val_auth_status_line
495 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE,
496 x_change_status IN OUT NOCOPY BOOLEAN
497 ) IS
498
499 d_api_name CONSTANT VARCHAR2(30) := 'val_auth_status_line';
500 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
501 d_position NUMBER;
502
503 l_has_new_records VARCHAR2(1);
504 l_different VARCHAR2(1);
505 BEGIN
506
507 d_position := 0;
508 IF (PO_LOG.d_proc) THEN
509 PO_LOG.proc_begin(d_module);
510 END IF;
511
512 IF (p_draft_info.lines_changed = FND_API.G_FALSE) THEN
513 RETURN;
514 END IF;
515
516 d_position := 10;
517 -- Change auth status if there is a new line
518 SELECT MAX(FND_API.G_TRUE)
519 INTO l_has_new_records
520 FROM dual
521 WHERE EXISTS
522 ( SELECT NULL
523 FROM po_lines_draft_all PLD
524 WHERE PLD.draft_id = p_draft_info.draft_id
525 AND NVL(PLD.delete_flag, 'N') = 'N'
526 AND NVL(PLD.change_accepted_flag, 'Y') = 'Y'
527 AND NOT EXISTS
528 ( SELECT NULL
529 FROM po_lines_all PL
530 WHERE PLD.po_line_id = PL.po_line_id));
531
532 IF (l_has_new_records = FND_API.G_TRUE) THEN
533 IF (PO_LOG.d_stmt) THEN
534 PO_LOG.stmt(d_module, d_position, 'new reocrds at line - approval change');
535 END IF;
536
537 x_change_status := TRUE;
538 RETURN;
539 END IF;
540
541 -- Combining blanekt and standard PO approval status checking
542 -- together. Any changes to the attributes listed in the SQL will
543 -- trigger approval status change
544
545 --SQL What: Compare attributes of the transaction and draft record. Return 'Y'
546 -- if any of the attributes is different
547 --SQL Why: Need to figure out if the change triggers a change to approval
548 -- status
549 SELECT MAX(FND_API.G_TRUE)
550 INTO l_different
551 FROM dual
552 WHERE EXISTS
553 ( SELECT NULL
554 FROM po_lines_draft_all PLD,
555 po_lines_all PL
556 WHERE PLD.draft_id = p_draft_info.draft_id
557 AND NVL(PLD.delete_flag, 'N') = 'N'
558 AND NVL(PLD.change_accepted_flag, 'Y') = 'Y'
559 AND PLD.po_line_id = PL.po_line_id
560 AND
561 ( DECODE (PLD.unit_price, PL.unit_price, 'Y', 'N') = 'N'
562 OR DECODE (PLD.line_num, PL.line_num, 'Y', 'N') = 'N'
563 OR DECODE (PLD.item_id, PL.item_id, 'Y', 'N') = 'N'
564 OR DECODE (PLD.item_description, PL.item_description, 'Y', 'N') = 'N'
565 OR DECODE (PLD.quantity, PL.quantity, 'Y', 'N') = 'N'
566 OR DECODE (PLD.unit_meas_lookup_code, PL.unit_meas_lookup_code, 'Y', 'N') = 'N'
567 OR DECODE (PLD.from_header_id, PL.from_header_id, 'Y', 'N') = 'N'
568 OR DECODE (PLD.from_line_id, PL.from_line_id, 'Y', 'N') = 'N'
569 OR DECODE (PLD.hazard_class_id, PL.hazard_class_id, 'Y', 'N') = 'N'
570 OR DECODE (PLD.vendor_product_num, PL.vendor_product_num, 'Y', 'N') = 'N'
571 OR DECODE (PLD.un_number_id, PL.un_number_id, 'Y', 'N') = 'N'
572 OR DECODE (PLD.note_to_vendor, PL.note_to_vendor, 'Y', 'N') = 'N'
573 OR DECODE (PLD.item_revision, PL.item_revision, 'Y', 'N') = 'N'
574 OR DECODE (PLD.category_id, PL.category_id, 'Y', 'N') = 'N'
575 OR DECODE (PLD.price_type_lookup_code, PL.price_type_lookup_code, 'Y', 'N') = 'N'
576 OR DECODE (PLD.not_to_exceed_price, PL.not_to_exceed_price, 'Y', 'N') = 'N'
577 OR DECODE (PLD.contract_id, PL.contract_id, 'Y', 'N') = 'N'
578 OR DECODE (PLD.start_date, PL.start_date, 'Y', 'N') = 'N'
579 OR DECODE (PLD.expiration_date, PL.expiration_date, 'Y', 'N') = 'N'
580 OR DECODE (PLD.contractor_first_name, PL.contractor_first_name, 'Y', 'N') = 'N'
581 OR DECODE (PLD.contractor_last_name, PL.contractor_last_name, 'Y', 'N') = 'N'
582 OR DECODE (PLD.amount, PL.amount, 'Y', 'N') = 'N'
583 OR DECODE (PLD.quantity_committed, PL.quantity_committed, 'Y', 'N') = 'N'
584 OR DECODE (PLD.committed_amount, PL.committed_amount, 'Y', 'N') = 'N'
585 -- <Complex Work R12 START>
586 OR DECODE (PLD.retainage_rate, PL.retainage_rate, 'Y', 'N') = 'N'
587 OR DECODE (PLD.max_retainage_amount, PL.max_retainage_amount, 'Y', 'N') = 'N'
588 OR DECODE (PLD.progress_payment_rate, PL.progress_payment_rate, 'Y', 'N') = 'N'
589 OR DECODE (PLD.recoupment_rate, PL.recoupment_rate, 'Y', 'N') = 'N'
590 -- <Complex Work R12 END>
591 )
592 );
593
594 d_position := 20;
595 IF (l_different = FND_API.G_TRUE) THEN
596 IF (PO_LOG.d_stmt) THEN
597 PO_LOG.stmt(d_module, d_position, 'Line causes approval change');
598 END IF;
599
600 x_change_status := TRUE;
601 END IF;
602
603 d_position := 30;
604 EXCEPTION
605 WHEN OTHERS THEN
606 PO_MESSAGE_S.add_exc_msg
607 ( p_pkg_name => d_pkg_name,
608 p_procedure_name => d_api_name || '.' || d_position
609 );
610 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
611 END val_auth_status_line;
612
613
614 -----------------------------------------------------------------------
615 --Start of Comments
616 --Name: val_auth_status_line_loc
617 --Pre-reqs: None
618 --Modifies:
619 --Locks:
620 -- None
621 --Function:
622 -- Checks whether there are any changes at the line location level that trigger
623 -- approval status to be updated. This procedure also determines what
624 -- shipments should have their status updated
625 --Parameters:
626 --IN:
627 --p_draft_info
628 -- Record structure holding draft information
629 --IN OUT:
630 --x_change_status
631 -- This procedure sets this parameter to TRUE if status needs to be
632 -- changed
633 --OUT:
634 --x_changed_line_loc_list
635 -- List of line locations that should have their status updated
636 --Returns:
637 --Notes:
638 --Testing:
639 --End of Comments
640 ------------------------------------------------------------------------
641 PROCEDURE val_auth_status_line_loc
642 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE,
643 x_change_status IN OUT NOCOPY BOOLEAN,
644 x_changed_line_loc_list OUT NOCOPY PO_TBL_NUMBER
645 ) IS
646
647 d_api_name CONSTANT VARCHAR2(30) := 'val_auth_status_line_loc';
648 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
649 d_position NUMBER;
650
651 l_has_new_records VARCHAR2(1);
652
653 BEGIN
654
655 d_position := 0;
656 IF (PO_LOG.d_proc) THEN
657 PO_LOG.proc_begin(d_module);
658 END IF;
659
660 IF (p_draft_info.line_locations_changed = FND_API.G_FALSE) THEN
661 RETURN;
662 END IF;
663
664 d_position := 10;
665 -- Change auth status if there is a new line
666 SELECT MAX(FND_API.G_TRUE)
667 INTO l_has_new_records
668 FROM dual
669 WHERE EXISTS
670 ( SELECT NULL
671 FROM po_line_locations_draft_all PLLD
672 WHERE PLLD.draft_id = p_draft_info.draft_id
673 AND NVL(PLLD.delete_flag, 'N') = 'N'
674 AND NVL(PLLD.change_accepted_flag, 'Y') = 'Y'
675 AND NOT EXISTS
676 ( SELECT NULL
677 FROM po_line_locations_all PLL
678 WHERE PLLD.line_location_id = PLL.line_location_id));
679
680 IF (l_has_new_records = FND_API.G_TRUE) THEN
681 IF (PO_LOG.d_stmt) THEN
682 PO_LOG.stmt(d_module, d_position, 'New shipments - approval change');
683 END IF;
684
685 x_change_status := TRUE;
686 END IF;
687
688 SELECT PLLD.line_location_id
689 BULK COLLECT
690 INTO x_changed_line_loc_list
691 FROM po_line_locations_draft_all PLLD,
692 po_line_locations_all PLL
693 WHERE PLLD.draft_id = p_draft_info.draft_id
694 AND NVL(PLLD.delete_flag, 'N') = 'N'
695 AND NVL(PLLD.change_accepted_flag, 'Y') = 'Y'
696 AND PLLD.line_location_id = PLL.line_location_id
697 AND
698 ( DECODE (PLLD.quantity, PLL.quantity, 'Y', 'N') = 'N'
699 OR DECODE (PLLD.ship_to_location_id, PLL.ship_to_location_id, 'Y', 'N') = 'N'
700 OR DECODE (PLLD.promised_date, PLL.promised_date, 'Y', 'N') = 'N'
701 OR DECODE (PLLD.need_by_date, PLL.need_by_date, 'Y', 'N') = 'N'
702 OR DECODE (PLLD.shipment_num, PLL.shipment_num, 'Y', 'N') = 'N'
703 OR DECODE (PLLD.start_date, PLL.start_date, 'Y', 'N') = 'N'
704 OR DECODE (PLLD.end_date, PLL.end_date, 'Y', 'N') = 'N'
705 OR DECODE (PLLD.days_early_receipt_allowed, PLL.days_early_receipt_allowed, 'Y', 'N') = 'N'
706 OR DECODE (PLLD.last_accept_date, PLL.last_accept_date, 'Y', 'N') = 'N'
707 OR DECODE (PLLD.price_discount, PLL.price_discount, 'Y', 'N') = 'N'
708 OR DECODE (PLLD.price_override, PLL.price_override, 'Y', 'N') = 'N'
709 OR DECODE (PLLD.ship_to_organization_id, PLL.ship_to_organization_id, 'Y', 'N') = 'N'
710 OR DECODE (PLLD.tax_code_id, PLL.tax_code_id, 'Y', 'N') = 'N'
711 -- <Complex Work R12 START>
712 OR DECODE (PLLD.amount, PLL.amount, 'Y', 'N') = 'N'
713 OR DECODE (PLLD.payment_type, PLL.payment_type, 'Y', 'N') = 'N'
714 OR DECODE (PLLD.description, PLL.description, 'Y', 'N') = 'N'
715 OR DECODE (PLLD.work_approver_id, PLL.work_approver_id, 'Y', 'N') = 'N'
716 -- <Complex Work R12 END>
717 );
718
719 d_position := 20;
720 IF (x_changed_line_loc_list.COUNT > 0) THEN
721 IF (PO_LOG.d_stmt) THEN
722 PO_LOG.stmt(d_module, d_position, 'Shipment causes approval change');
723 END IF;
724
725 x_change_status := TRUE;
726 END IF;
727
728 EXCEPTION
729 WHEN OTHERS THEN
730 PO_MESSAGE_S.add_exc_msg
731 ( p_pkg_name => d_pkg_name,
732 p_procedure_name => d_api_name || '.' || d_position
733 );
734 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
735 END val_auth_status_line_loc;
736
737 -----------------------------------------------------------------------
738 --Start of Comments
739 --Name: val_auth_status_dist
740 --Pre-reqs: None
741 --Modifies:
742 --Locks:
743 -- None
744 --Function:
745 -- Checks whether there are any changes at the distribution level that trigger
746 -- approval status to be updated. This procedure also determines what
747 -- shipments should have their status updated
748 --Parameters:
749 --IN:
750 --p_draft_info
751 -- Record structure holding draft information
752 --IN OUT:
753 --x_change_status
754 -- This procedure sets this parameter to TRUE if status needs to be
755 -- changed
756 --OUT:
757 --x_changed_line_loc_list
758 -- List of line locations that should have their status updated
759 --Returns:
760 --Notes:
761 --Testing:
762 --End of Comments
763 ------------------------------------------------------------------------
764 PROCEDURE val_auth_status_dist
765 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE,
766 x_change_status IN OUT NOCOPY BOOLEAN,
767 x_changed_line_loc_list OUT NOCOPY PO_TBL_NUMBER
768 ) IS
769
770 d_api_name CONSTANT VARCHAR2(30) := 'val_auth_status_dist';
771 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
772 d_position NUMBER;
773
774 l_has_new_records VARCHAR2(1);
775
776 BEGIN
777
778 d_position := 0;
779 IF (PO_LOG.d_proc) THEN
780 PO_LOG.proc_begin(d_module);
781 END IF;
782
783 IF (p_draft_info.distributions_changed = FND_API.G_FALSE) THEN
784 RETURN;
785 END IF;
786
787 d_position := 10;
788 -- Change auth status if there is a new line
789 SELECT MAX(FND_API.G_TRUE)
790 INTO l_has_new_records
791 FROM dual
792 WHERE EXISTS
793 ( SELECT NULL
794 FROM po_distributions_draft_all PDD
795 WHERE PDD.draft_id = p_draft_info.draft_id
796 AND NVL(PDD.delete_flag, 'N') = 'N'
797 AND NVL(PDD.change_accepted_flag, 'Y') = 'Y'
798 AND NOT EXISTS
799 ( SELECT NULL
800 FROM po_distributions_all PD
801 WHERE PDD.po_distribution_id = PD.po_distribution_id));
802
803 IF (l_has_new_records = FND_API.G_TRUE) THEN
804 IF (PO_LOG.d_stmt) THEN
805 PO_LOG.stmt(d_module, d_position, 'New distribution: approval change');
806 END IF;
807
808 x_change_status := TRUE;
809 END IF;
810
811 d_position := 20;
812 -- We need to use DISTINCT here because one shipment can have multiple
813 -- distribution changes, and we only need one line location id in this case
814 SELECT DISTINCT PDD.line_location_id
815 BULK COLLECT
816 INTO x_changed_line_loc_list
817 FROM po_distributions_draft_all PDD,
818 po_distributions_all PD
819 WHERE PDD.draft_id = p_draft_info.draft_id
820 AND NVL(PDD.delete_flag, 'N') = 'N'
821 AND NVL(PDD.change_accepted_flag, 'Y') = 'Y'
822 AND PDD.po_distribution_id = PD.po_distribution_id
823 AND
824 ( DECODE (PDD.quantity_ordered, PD.quantity_ordered, 'Y', 'N') = 'N'
825 OR DECODE (PDD.amount_ordered, PD.amount_ordered, 'Y', 'N') = 'N'
826 OR DECODE (PDD.deliver_to_person_id, PD.deliver_to_person_id, 'Y', 'N') = 'N'
827 OR DECODE (PDD.rate_date, PD.rate_date, 'Y', 'N') = 'N'
828 OR DECODE (PDD.rate, PD.rate, 'Y', 'N') = 'N'
829 OR DECODE (PDD.gl_encumbered_date, PD.gl_encumbered_date, 'Y', 'N') = 'N'
830 OR DECODE (PDD.recovery_rate, PD.recovery_rate, 'Y', 'N') = 'N'
831 OR DECODE (PDD.destination_subinventory, PD.destination_subinventory, 'Y', 'N') = 'N'
832 OR DECODE (PDD.code_combination_id, PD.code_combination_id, 'Y', 'N') = 'N'
833 OR DECODE (PDD.dest_charge_account_id, PD.dest_charge_account_id, 'Y', 'N') = 'N'
834 OR DECODE (PDD.distribution_num, PD.distribution_num, 'Y', 'N') = 'N'
835 );
836
837 d_position := 30;
838 IF (x_changed_line_loc_list.COUNT > 0) THEN
839 IF (PO_LOG.d_stmt) THEN
840 PO_LOG.stmt(d_module, d_position,
841 'Distribution changes cause approval change');
842 END IF;
843
844 x_change_status := TRUE;
845 END IF;
846
847 EXCEPTION
848 WHEN OTHERS THEN
849 PO_MESSAGE_S.add_exc_msg
850 ( p_pkg_name => d_pkg_name,
851 p_procedure_name => d_api_name || '.' || d_position
852 );
853 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
854 END val_auth_status_dist;
855
856 -----------------------------------------------------------------------
857 --Start of Comments
858 --Name: val_auth_status_org_assign
859 --Pre-reqs: None
860 --Modifies:
861 --Locks:
862 -- None
863 --Function:
864 -- Checks whether there are any changes at org assignment level that trigger
865 -- approval status to be updated.
866 --Parameters:
867 --IN:
868 --p_draft_info
869 -- Record structure holding draft information
870 --IN OUT:
871 --x_change_status
872 -- This procedure sets this parameter to TRUE if status needs to be
873 -- changed
874 --OUT:
875 --Returns:
876 --Notes:
877 --Testing:
878 --End of Comments
879 ------------------------------------------------------------------------
880 PROCEDURE val_auth_status_org_assign
881 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE,
882 x_change_status IN OUT NOCOPY BOOLEAN
883 ) IS
884
885 d_api_name CONSTANT VARCHAR2(30) := 'val_auth_status_org_assign';
886 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
887 d_position NUMBER;
888
889 BEGIN
890
891 d_position := 0;
892 IF (PO_LOG.d_proc) THEN
893 PO_LOG.proc_begin(d_module);
894 END IF;
895
896 IF (p_draft_info.ga_org_assign_changed = FND_API.G_TRUE) THEN
897 IF (PO_LOG.d_stmt) THEN
898 PO_LOG.stmt(d_module, d_position, 'Org assign change - approval changes');
899 END IF;
900
901 x_change_status := TRUE;
902 END IF;
903
904 EXCEPTION
905 WHEN OTHERS THEN
906 PO_MESSAGE_S.add_exc_msg
907 ( p_pkg_name => d_pkg_name,
908 p_procedure_name => d_api_name || '.' || d_position
909 );
910 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
911 END val_auth_status_org_assign;
912
913 -----------------------------------------------------------------------
914 --Start of Comments
915 --Name: val_auth_status_price_diff
916 --Pre-reqs: None
917 --Modifies:
918 --Locks:
919 -- None
920 --Function:
921 -- Checks whether there are any changes at price differential level that
922 -- trigger approval status to be updated.
923 --Parameters:
924 --IN:
925 --p_draft_info
926 -- Record structure holding draft information
927 --IN OUT:
928 --x_change_status
929 -- This procedure sets this parameter to TRUE if status needs to be
930 -- changed
931 --OUT:
932 --Returns:
933 --Notes:
934 --Testing:
935 --End of Comments
936 ------------------------------------------------------------------------
937 PROCEDURE val_auth_status_price_diff
938 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE,
939 x_change_status IN OUT NOCOPY BOOLEAN
940 ) IS
941
942 d_api_name CONSTANT VARCHAR2(30) := 'val_auth_status_price_diff';
943 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
944 d_position NUMBER;
945
946 BEGIN
947
948 d_position := 0;
949 IF (PO_LOG.d_proc) THEN
950 PO_LOG.proc_begin(d_module);
951 END IF;
952
953 IF (p_draft_info.price_diff_changed = FND_API.G_TRUE) THEN
954 IF (PO_LOG.d_stmt) THEN
955 PO_LOG.stmt(d_module, d_position,
956 'price diff changes cause approval change');
957 END IF;
958
959 x_change_status := TRUE;
960 END IF;
961
962 EXCEPTION
963 WHEN OTHERS THEN
964 PO_MESSAGE_S.add_exc_msg
965 ( p_pkg_name => d_pkg_name,
966 p_procedure_name => d_api_name || '.' || d_position
967 );
968 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
969 END val_auth_status_price_diff;
970
971 END PO_DRAFT_APPR_STATUS_PVT;