[Home] [Help]
PACKAGE BODY: APPS.PO_OTM_INTEGRATION_PVT
Source
1 PACKAGE BODY PO_OTM_INTEGRATION_PVT AS
2 /* $Header: POXVOTMB.pls 120.7 2010/11/01 21:49:00 yawang noship $ */
3
4 -- Debugging booleans used to bypass logging when turned off
5 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
6 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
7 g_po_wf_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
8
9 -- Logging constants
10 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_OTM_INTEGRATION_PVT';
11 g_module_prefix CONSTANT VARCHAR2(100) := 'po.plsql.' || g_pkg_name || '.';
12
13 -- Exceptions
14 g_OTM_INTEGRATION_EXC EXCEPTION;
15
16 -- 7449918 OTM Recovery START
17 -- Recovery Event
18 g_OTM_RECOVERY_EVENT CONSTANT VARCHAR2(30) := 'OTM_RECOVERY_EVENT';
19 -- OTM Recovyer END
20
21 -- Private procedures
22 PROCEDURE populate_otm_info (
23 p_action IN VARCHAR2
24 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
25 );
26
27 PROCEDURE populate_address_info (
28 x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
29 );
30
31 PROCEDURE populate_address_info (
32 x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
33 , x_otm_sched_line IN OUT NOCOPY PO_OTM_SCHEDULE_LINE_TYPE
34 );
35
36 PROCEDURE populate_drop_ship_info (
37 x_otm_sched_line IN OUT NOCOPY PO_OTM_SCHEDULE_LINE_TYPE
38 );
39
40 PROCEDURE get_approved_po (
41 p_doc_id IN NUMBER
42 , p_doc_revision IN NUMBER
43 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
44 );
45
46 PROCEDURE get_canceled_po (
47 p_doc_id IN NUMBER
48 , p_doc_revision IN NUMBER
49 , p_line_id IN NUMBER
50 , p_line_loc_id IN NUMBER
51 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
52 );
53
54 PROCEDURE get_closed_po (
55 p_doc_id IN NUMBER
56 , p_doc_revision IN NUMBER
57 , p_line_id IN NUMBER
58 , p_line_loc_id IN NUMBER
59 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
60 );
61
62 PROCEDURE get_opened_po (
63 p_doc_id IN NUMBER
64 , p_doc_revision IN NUMBER
65 , p_line_id IN NUMBER
66 , p_line_loc_id IN NUMBER
67 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
68 );
69
70 PROCEDURE get_held_po (
71 p_doc_id IN NUMBER
72 , p_doc_revision IN NUMBER
73 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
74 );
75
76 PROCEDURE get_po_for_status_change (
77 p_doc_id IN NUMBER
78 , p_doc_revision IN NUMBER
79 , p_line_id IN NUMBER
80 , p_line_loc_id IN NUMBER
81 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
82 );
83
84 PROCEDURE get_approved_release (
85 p_doc_id IN NUMBER
86 , p_doc_revision IN NUMBER
87 , p_blanket_revision IN NUMBER
88 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
89 );
90
91 PROCEDURE get_canceled_release (
92 p_doc_id IN NUMBER
93 , p_doc_revision IN NUMBER
94 , p_blanket_revision IN NUMBER
95 , p_line_loc_id IN NUMBER
96 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
97 );
98
99 PROCEDURE get_closed_release (
100 p_doc_id IN NUMBER
101 , p_doc_revision IN NUMBER
102 , p_blanket_revision IN NUMBER
103 , p_line_loc_id IN NUMBER
104 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
105 );
106
107 PROCEDURE get_opened_release (
108 p_doc_id IN NUMBER
109 , p_doc_revision IN NUMBER
110 , p_blanket_revision IN NUMBER
111 , p_line_loc_id IN NUMBER
112 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
113 );
114
115 PROCEDURE get_held_release (
116 p_doc_id IN NUMBER
117 , p_doc_revision IN NUMBER
118 , p_blanket_revision IN NUMBER
119 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
120 );
121
122 PROCEDURE get_release_for_status_change (
123 p_doc_id IN NUMBER
124 , p_doc_revision IN NUMBER
125 , p_blanket_revision IN NUMBER
126 , p_line_loc_id IN NUMBER
127 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
128 );
129
130 -- 7449918 OTM Recovery START
131 PROCEDURE get_recovering_order
132 ( p_doc_id IN NUMBER,
133 x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
134 );
135
136 PROCEDURE get_recovering_release
137 ( p_doc_id IN NUMBER,
138 x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
139 );
140 -- OTM Recovery END
141
142 FUNCTION is_otm_installed
143 RETURN BOOLEAN
144 IS
145
146 l_is_otm_installed BOOLEAN;
147
148 d_progress VARCHAR2(3);
149 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'IS_OTM_INSTALLED';
150
151 BEGIN
152
153 d_progress := '000';
154
155 IF (g_debug_stmt) THEN
156 PO_DEBUG.debug_begin(d_module);
157 END IF;
158
159 d_progress := '100';
160
161 IF (FND_PROFILE.value('WSH_OTM_INSTALLED') IN ('Y', 'P')) THEN
162 d_progress := '110';
163 l_is_otm_installed := TRUE;
164 ELSE
165 d_progress := '120';
166 l_is_otm_installed := FALSE;
167 END IF;
168
169 d_progress := '130';
170
171 IF (g_debug_stmt) THEN
172 PO_DEBUG.debug_var(d_module, d_progress, 'l_is_otm_installed', l_is_otm_installed);
173 PO_DEBUG.debug_end(d_module);
174 END IF;
175
176 RETURN l_is_otm_installed;
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 IF (g_debug_unexp) THEN
181 PO_DEBUG.debug_unexp(d_module, d_progress, 'Unexpected error');
182 END IF;
183
184 END is_otm_installed;
185
186 FUNCTION is_inbound_logistics_enabled
187 RETURN BOOLEAN
188 IS
189
190 l_is_logistics_enabled BOOLEAN;
191
192 d_progress VARCHAR2(3);
193 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'IS_INBOUND_LOGISTICS_ENABLED';
194
195 BEGIN
196
197 d_progress := '000';
198
199 IF (g_debug_stmt) THEN
200 PO_DEBUG.debug_begin(d_module);
201 END IF;
202
203 d_progress := '100';
204
205 -- Check OTM and FTE status
206 IF (is_otm_installed() OR WSH_UTIL_CORE.fte_is_installed() = 'Y') THEN
207 d_progress := '110';
208 l_is_logistics_enabled := TRUE;
209 ELSE
210 d_progress := '120';
211 l_is_logistics_enabled := FALSE;
212 END IF;
213
214 d_progress := '130';
215
216 IF (g_debug_stmt) THEN
217 PO_DEBUG.debug_var(d_module, d_progress, 'l_is_logistics_enabled', l_is_logistics_enabled);
218 PO_DEBUG.debug_end(d_module);
219 END IF;
220
221 RETURN l_is_logistics_enabled;
222
223 EXCEPTION
224 WHEN OTHERS THEN
225 IF (g_debug_unexp) THEN
226 PO_DEBUG.debug_unexp(d_module, d_progress, 'Unexpected error');
227 END IF;
228
229 END is_inbound_logistics_enabled;
230
231 PROCEDURE handle_doc_update (
232 p_doc_type IN VARCHAR2
233 , p_doc_id IN NUMBER
234 , p_action IN VARCHAR2
235 , p_line_id IN NUMBER
236 , p_line_loc_id IN NUMBER
237 )
238 IS
239
240 l_param_list PO_EVENT_PARAMS_TYPE;
241 l_command VARCHAR2(30);
242
243 l_doc_revision NUMBER;
244 l_blanket_revision PO_HEADERS_ALL.revision_num%TYPE;
245 l_org_name HR_ALL_ORGANIZATION_UNITS.name%TYPE;
246 l_po_number PO_HEADERS_ALL.segment1%TYPE;
247 l_release_number PO_RELEASES_ALL.release_num%TYPE;
248 l_shipping_control PO_HEADERS_ALL.shipping_control%TYPE;
249 l_line_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE; --<Bug 5935970>
250 l_approved_date PO_HEADERS_ALL.approved_date%TYPE; --7449918
251
252 l_return_status VARCHAR2(1);
253
254 d_progress VARCHAR2(3);
255 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'HANDLE_DOC_UPDATE';
256 d_log_msg VARCHAR2(200) := 'Unknown error';
257
258 BEGIN
259
260 d_progress := '000';
261
262 IF (g_debug_stmt) THEN
263 PO_DEBUG.debug_begin(d_module);
264 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_type', p_doc_type);
265 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_id', p_doc_id);
266 PO_DEBUG.debug_var(d_module, d_progress, 'p_action', p_action);
267 PO_DEBUG.debug_var(d_module, d_progress, 'p_action', p_action);
268 PO_DEBUG.debug_var(d_module, d_progress, 'p_line_id', p_line_id);
269 PO_DEBUG.debug_var(d_module, d_progress, 'p_line_loc_id', p_line_loc_id);
270 END IF;
271
272 d_progress := '010';
273
274 -- procedure should only be called if OTM is installed
275 IF (NOT is_otm_installed()) THEN
276 d_progress := '020';
277 d_log_msg := 'procedure unexpectedly called when OTM not installed';
278 RAISE g_OTM_INTEGRATION_EXC;
279 END IF;
280
281 d_progress := '030';
282 -- We do not want to communicate Complex Work Purchase Orders to OTM.
283 IF(p_doc_type = 'PO' AND PO_COMPLEX_WORK_PVT.is_complex_work_po(p_doc_id)) THEN
284 d_progress := '040';
285 IF (g_debug_stmt) THEN
286 PO_DEBUG.debug_stmt(d_module, d_progress, 'Not initiating OTM integration because PO: ' || p_doc_id || ' is a Complex Work Purchase Order.');
287 END IF;
288 RETURN;
289 END IF; --IF complex work PO
290
291 --<Bug 5935970 Begin> OTM SHOWS ENTIRE ORDER AS CANCELLED, IF WE CLOSE A NON-QUANTITY BASED LINE
292 --Check if the action is at the line level for a PO. If the action is at the shipment
293 --level for the PO then we will still get the p_line_id.
294 IF (p_doc_type = 'PO' AND p_line_id IS NOT NULL) THEN
295 SELECT pol.order_type_lookup_code
296 INTO l_line_value_basis
297 FROM po_lines_all pol
298 WHERE pol.po_line_id = p_line_id;
299 --If the action is at the shipment level for a release we will not get the p_line_id
300 ELSIF (p_doc_type = 'RELEASE' AND p_line_loc_id IS NOT NULL) THEN
301 SELECT pol.order_type_lookup_code
302 INTO l_line_value_basis
303 FROM po_lines_all pol,
304 po_line_locations_all pll
305 WHERE pll.line_location_id = p_line_loc_id
306 AND pll.po_line_id = pol.po_line_id;
307 END IF;
308 --Return and do not raise event if the action was on a non-quantity based line.
309 IF (l_line_value_basis <> 'QUANTITY') THEN
310 RETURN;
311 END IF;
312 --<Bug 5935970 End>
313 d_progress := '100';
314
315 -- convert command text to those handled by BPEL process
316 IF (p_action IN ('APPROVE_DOCUMENT', 'APPROVE', 'APPROVE AND RESERVE')) THEN
317 d_progress := '110';
318 l_command := 'APPROVE';
319 ELSIF (p_action = 'CANCEL') THEN
320 d_progress := '120';
321 l_command := 'CANCEL';
322 ELSIF (p_action IN ('HOLD_DOCUMENT', 'HOLD')) THEN
323 d_progress := '130';
324 l_command := 'HOLD';
325 ELSIF (p_action = 'RELEASE HOLD') THEN
326 d_progress := '140';
327 l_command := 'UNHOLD';
328 ELSIF (p_action IN ('CLOSE', 'RECEIVE CLOSE', 'FINALLY CLOSE')) THEN
329 d_progress := '150';
330 l_command := 'CLOSE';
331 ELSIF (p_action IN ('OPEN', 'RECEIVE OPEN')) THEN
332 d_progress := '160';
333 l_command := 'OPEN';
334 ELSIF (p_action = 'RECOVER') THEN -- 7449918 OTM Recovery
335 d_progress := '170';
336 l_command := 'RECOVER';
337 ELSE
338 d_progress := '190';
339 l_command := '';
340 END IF;
341
342 IF (g_debug_stmt) THEN
343 PO_DEBUG.debug_var(d_module, d_progress, 'l_command', l_command);
344 END IF;
345
346 d_progress := '200';
347
348 -- see if the event is one handled by OTM
349 --7449918 add 'RECOVER'
350 IF (l_command IN ('APPROVE', 'CANCEL', 'HOLD', 'UNHOLD', 'CLOSE', 'OPEN', 'RECOVER')) THEN
351
352 d_progress := '220';
353
354 -- Integration only applies to documents that have the a value
355 -- for "transportation arranged by."
356 IF (p_doc_type = 'RELEASE') THEN
357 d_progress := '230';
358
359 SELECT por.shipping_control
360 , por.approved_date
361 INTO l_shipping_control
362 , l_approved_date --7449918
363 FROM po_releases_all por
364 WHERE por.po_release_id = p_doc_id;
365
366 d_progress := '240';
367 ELSE
368 d_progress := '250';
369
370 SELECT poh.shipping_control
371 , poh.approved_date
372 INTO l_shipping_control
373 , l_approved_date --7449918
374 FROM po_headers_all poh
375 WHERE poh.po_header_id = p_doc_id;
376
377 d_progress := '260';
378 END IF;
379
380 --<Bug# 5842690> PO-OTM: DOCUMENT INCORRECTLY COMMUNICATED TO OTM WITH TRANSPORT ARRANGED = NONE
381 --Since none is a new lookup code we can no longer just check if shipping control is not null.
382 -- 7449918 If PO or Release has never been approved, do not invoke OTM
383 IF (l_shipping_control IN ('BUYER', 'SUPPLIER') AND l_approved_date IS NOT NULL) THEN
384
385 d_progress := '300';
386
387 -- construct parameter list
388 l_param_list := PO_EVENT_PARAMS_TYPE.new_instance();
389
390 -- For all documents, we will gather some user-legible doc info
391 -- (PO Number, Org Name), so, should the BPEL process fail,
392 -- someone reading the audit trail
393 -- can more easily figure out which process failed.
394 --
395 -- If this is a release, need to additionally get the approved blanket's
396 -- revision number, in case it is modified before the callback
397 -- to pull the data, and the release number for context.
398 IF (p_doc_type = 'RELEASE') THEN
399 d_progress := '310';
400 SELECT poha.revision_num
401 , poha.segment1
402 , pora.release_num
403 , pora.revision_num
404 , hou.name
405 INTO l_blanket_revision
406 , l_po_number
407 , l_release_number
408 , l_doc_revision
409 , l_org_name
410 FROM po_headers_archive_all poha
411 , po_releases_archive_all pora
412 , hr_all_organization_units hou
413 WHERE pora.po_release_id = p_doc_id
414 AND pora.latest_external_flag = 'Y'
415 AND poha.po_header_id = pora.po_header_id
416 AND poha.latest_external_flag = 'Y'
417 AND hou.organization_id = pora.org_id;
418
419 ELSIF (p_doc_type = 'PO') THEN
420 d_progress := '320';
421 SELECT poha.segment1
422 , poha.revision_num
423 , hou.name
424 INTO l_po_number
425 , l_doc_revision
426 , l_org_name
427 FROM po_headers_archive_all poha
428 , hr_all_organization_units hou
429 WHERE poha.po_header_id = p_doc_id
430 AND poha.latest_external_flag = 'Y'
431 AND hou.organization_id = poha.org_id;
432 ELSE
433 d_progress := '340';
434 d_log_msg := 'unrecognized doc type: ' || p_doc_type;
435 RAISE g_OTM_INTEGRATION_EXC;
436 END IF;
437
438 d_progress := '350';
439
440 l_param_list.add_param (
441 p_param_name => 'document_type'
442 , p_param_value => p_doc_type);
443
444 l_param_list.add_param (
445 p_param_name => 'document_id'
446 , p_param_value => p_doc_id);
447
448 l_param_list.add_param (
449 p_param_name => 'action'
450 , p_param_value => l_command);
451
452 l_param_list.add_param (
453 p_param_name => 'line_id'
454 , p_param_value => p_line_id);
455
456 l_param_list.add_param (
457 p_param_name => 'line_location_id'
458 , p_param_value => p_line_loc_id);
459
460 l_param_list.add_param (
461 p_param_name => 'document_revision'
462 , p_param_value => l_doc_revision);
463
464 l_param_list.add_param (
465 p_param_name => 'po_number'
466 , p_param_value => l_po_number);
467
468 l_param_list.add_param (
469 p_param_name => 'org_name'
470 , p_param_value => l_org_name);
471
472 l_param_list.add_param (
473 p_param_name => 'blanket_revision'
474 , p_param_value => l_blanket_revision);
475
476 l_param_list.add_param (
477 p_param_name => 'release_number'
478 , p_param_value => l_release_number);
479
480 d_progress := '360';
481
482 -- raise event
483 PO_BUSINESSEVENT_PVT.raise_event (
484 p_api_version => 1.0
485 , p_event_name => 'oracle.apps.po.event.document_action_event'
486 , p_param_list => l_param_list
487 , p_deferred => FALSE
488 , x_return_status => l_return_status);
489
490 d_progress := '370';
491
492 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
493 d_progress := '380';
494 d_log_msg := 'Error raising business event';
495
496 -- 7449918 OTM Recovery
497 update_order_otm_status
498 ( p_doc_id => p_doc_id,
499 p_doc_type => p_doc_type,
500 p_order_otm_status => 'Business Event Failure',
501 p_otm_recovery_flag => 'Y'
502 );
503
504 --RAISE g_OTM_INTEGRATION_EXC;
505 ELSE
506 d_progress := '385';
507
508
509 -- 7449918 OTM Recovery
510 update_order_otm_status
511 ( p_doc_id => p_doc_id,
512 p_doc_type => p_doc_type,
513 p_order_otm_status => 'In Advanced Queue',
514 p_otm_recovery_flag => 'N'
515 );
516 END IF;
517
518 d_progress := '390';
519
520 ELSE
521 d_progress := '395';
522 IF (g_debug_stmt) THEN
523 PO_DEBUG.debug_stmt(d_module, d_progress, 'Not initiating OTM integration because SHIPPING_CONTROL (Transporation Arranged By) is NULL or it has not been approved.');
524 END IF;
525
526 END IF; -- IF (l_shipping_control IN ('BUYER', 'SUPPLIER')) THEN
527
528 ELSE
529 d_progress := '400';
530 IF (g_debug_stmt) THEN
531 PO_DEBUG.debug_stmt(d_module, d_progress, 'Ignoring doc action: ' || l_command);
532 END IF;
533
534 END IF; -- IF (l_command IN ('APPROVE', 'CANCEL', 'HOLD', 'UNHOLD')) THEN
535
536 d_progress := '500';
537
538 IF (g_debug_stmt) THEN
539 PO_DEBUG.debug_end(d_module);
540 END IF;
541
542 EXCEPTION
543 WHEN NO_DATA_FOUND THEN
544 IF (g_debug_unexp) THEN
545 PO_DEBUG.debug_unexp(d_module, d_progress, 'No data found for document');
546 END IF;
547 RAISE;
548
549 WHEN OTHERS THEN
550 IF (g_debug_unexp) THEN
551 PO_DEBUG.debug_unexp(d_module, d_progress, d_log_msg);
552 END IF;
553 RAISE;
554
555 END handle_doc_update;
556
557 PROCEDURE get_otm_document (
558 p_doc_type IN VARCHAR2
559 , p_doc_id IN NUMBER
560 , p_doc_revision IN NUMBER
561 , p_blanket_revision IN NUMBER DEFAULT NULL
562 , p_action IN VARCHAR2
563 , p_line_id IN NUMBER DEFAULT NULL
564 , p_line_loc_id IN NUMBER DEFAULT NULL
565 , x_otm_doc OUT NOCOPY PO_OTM_ORDER_TYPE
566 )
567 IS
568
569 d_progress VARCHAR2(3);
570 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'GET_OTM_DOCUMENT';
571 d_log_msg VARCHAR2(200) := 'Unknown error';
572
573 BEGIN
574
575 d_progress := '000';
576
577 IF (g_debug_stmt) THEN
578 PO_DEBUG.debug_begin(d_module);
579 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_type', p_doc_type);
580 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_id', p_doc_id);
581 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_revision', p_doc_revision);
582 PO_DEBUG.debug_var(d_module, d_progress, 'p_blanket_revision', p_blanket_revision);
583 PO_DEBUG.debug_var(d_module, d_progress, 'p_action', p_action);
584 PO_DEBUG.debug_var(d_module, d_progress, 'p_line_id', p_line_id);
585 PO_DEBUG.debug_var(d_module, d_progress, 'p_line_loc_id', p_line_loc_id);
586 END IF;
587
588
589 d_progress := '100';
590
591 -- 7449918 OTM Recovery
592 update_order_otm_status
593 ( p_doc_id => p_doc_id,
594 p_doc_type => p_doc_type,
595 p_order_otm_status => 'IN BPEL Processing',
596 p_otm_recovery_flag => 'N'
597 );
598
599 -- initialize the OTM document object and
600 -- get domain info
601 x_otm_doc := PO_OTM_ORDER_TYPE.new_instance();
602 populate_otm_info(
603 p_action => p_action
604 , x_otm_doc => x_otm_doc);
605
606 d_progress := '110';
607
608 IF (p_doc_type = 'PO') THEN
609 d_progress := '200';
610 IF (p_action = 'APPROVE') THEN
611 d_progress := '300';
612 get_approved_po(
613 p_doc_id => p_doc_id
614 , p_doc_revision => p_doc_revision
615 , x_otm_doc => x_otm_doc);
616 d_progress := '310';
617 ELSIF (p_action = 'CANCEL') THEN
618 d_progress := '320';
619 get_canceled_po(
620 p_doc_id => p_doc_id
621 , p_doc_revision => p_doc_revision
622 , p_line_id => p_line_id
623 , p_line_loc_id => p_line_loc_id
624 , x_otm_doc => x_otm_doc);
625 d_progress := '330';
626 ELSIF (p_action IN ('HOLD', 'UNHOLD')) THEN
627 d_progress := '340';
628 get_held_po(
629 p_doc_id => p_doc_id
630 , p_doc_revision => p_doc_revision
631 , x_otm_doc => x_otm_doc);
632 d_progress := '350';
633 ELSIF (p_action = 'CLOSE') THEN
634 d_progress := '360';
635 get_closed_po(
636 p_doc_id => p_doc_id
637 , p_doc_revision => p_doc_revision
638 , p_line_id => p_line_id
639 , p_line_loc_id => p_line_loc_id
640 , x_otm_doc => x_otm_doc);
641 d_progress := '365';
642 ELSIF (p_action = 'OPEN') THEN
643 d_progress := '370';
644 get_opened_po(
645 p_doc_id => p_doc_id
646 , p_doc_revision => p_doc_revision
647 , p_line_id => p_line_id
648 , p_line_loc_id => p_line_loc_id
649 , x_otm_doc => x_otm_doc);
650 d_progress := '375';
651
652 ELSIF (p_action = 'RECOVER') THEN -- 7449918 OTM Recovery
653 d_progress := '380';
654 get_recovering_order
655 ( p_doc_id => p_doc_id
656 , x_otm_doc => x_otm_doc);
657 d_progress := '385';
658 ELSE
659 d_progress := '390';
660 d_log_msg := 'Unknown action: ' || p_action;
661 RAISE g_OTM_INTEGRATION_EXC;
662 END IF;
663 ElSIF (p_doc_type = 'RELEASE') THEN
664 d_progress := '400';
665 IF (p_action = 'APPROVE') THEN
666 d_progress := '500';
667 get_approved_release(
668 p_doc_id => p_doc_id
669 , p_doc_revision => p_doc_revision
670 , p_blanket_revision => p_blanket_revision
671 , x_otm_doc => x_otm_doc);
672 d_progress := '510';
673 ELSIF (p_action = 'CANCEL') THEN
674 d_progress := '520';
675 get_canceled_release(
676 p_doc_id => p_doc_id
677 , p_doc_revision => p_doc_revision
678 , p_blanket_revision => p_blanket_revision
679 , p_line_loc_id => p_line_loc_id
680 , x_otm_doc => x_otm_doc);
681 d_progress := '530';
682 ELSIF (p_action IN ('HOLD', 'UNHOLD')) THEN
683 d_progress := '540';
684 get_held_release(
685 p_doc_id => p_doc_id
686 , p_doc_revision => p_doc_revision
687 , p_blanket_revision => p_blanket_revision
688 , x_otm_doc => x_otm_doc);
689 d_progress := '550';
690 ELSIF (p_action = 'CLOSE') THEN
691 d_progress := '560';
692 get_closed_release(
693 p_doc_id => p_doc_id
694 , p_doc_revision => p_doc_revision
695 , p_blanket_revision => p_blanket_revision
696 , p_line_loc_id => p_line_loc_id
697 , x_otm_doc => x_otm_doc);
698 d_progress := '565';
699 ELSIF (p_action = 'OPEN') THEN
700 d_progress := '570';
701 get_opened_release(
702 p_doc_id => p_doc_id
703 , p_doc_revision => p_doc_revision
704 , p_blanket_revision => p_blanket_revision
705 , p_line_loc_id => p_line_loc_id
706 , x_otm_doc => x_otm_doc);
707 d_progress := '575';
708
709 ELSIF (p_action = 'RECOVER') THEN -- OTM Recovery
710 d_progress := '580';
711 get_recovering_release
712 ( p_doc_id => p_doc_id
713 , x_otm_doc => x_otm_doc);
714 d_progress := '585';
715 ELSE
716 d_progress := '590';
717 d_log_msg := 'Unknown action: ' || p_action;
718 RAISE g_OTM_INTEGRATION_EXC;
719 END IF;
720 ELSE
721 d_progress := '110';
722 d_log_msg := 'Unknown doc type: ' || p_doc_type;
723 RAISE g_OTM_INTEGRATION_EXC;
724 END IF;
725
726 IF (g_debug_stmt) THEN
727 PO_DEBUG.debug_end(d_module);
728 END IF;
729
730 EXCEPTION
731 WHEN OTHERS THEN
732 IF (g_debug_stmt) THEN
733 PO_DEBUG.debug_unexp(d_module, d_progress, d_log_msg);
734 END IF;
735
736 RAISE;
737 END get_otm_document;
738
739 PROCEDURE populate_otm_info (
740 p_action IN VARCHAR2
741 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
742 )
743 IS
744
745 d_progress VARCHAR2(3);
746 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'POPULATE_OTM_INFO';
747
748 BEGIN
749
750 d_progress := '000';
751
752 IF (g_debug_stmt) THEN
753 PO_DEBUG.debug_begin(d_module);
754 END IF;
755
756 x_otm_doc.otm_domain := FND_PROFILE.value('WSH_OTM_DOMAIN_NAME');
757 x_otm_doc.otm_user := FND_PROFILE.value('WSH_OTM_USER_ID');
758 x_otm_doc.otm_password := FND_PROFILE.value('WSH_OTM_PASSWORD');
759
760 -- get server timezone
761 SELECT ftb.timezone_code
762 INTO x_otm_doc.server_timezone_code
763 FROM fnd_timezones_b ftb
764 WHERE ftb.upgrade_tz_id = FND_PROFILE.value('SERVER_TIMEZONE_ID');
765
766 x_otm_doc.action := p_action;
767
768 d_progress := '100';
769
770 IF (g_debug_stmt) THEN
771 PO_DEBUG.debug_var(d_module, d_progress, 'x_otm_doc.otm_domain', x_otm_doc.otm_domain);
772 PO_DEBUG.debug_end(d_module);
773 END IF;
774
775 EXCEPTION
776 WHEN OTHERS THEN
777 IF (g_debug_stmt) THEN
778 PO_DEBUG.debug_unexp(d_module, d_progress, 'Unexpected exception');
779 END IF;
780 RAISE;
781
782 END populate_otm_info;
783
784 PROCEDURE get_approved_po (
785 p_doc_id IN NUMBER
786 , p_doc_revision IN NUMBER
787 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
788 )
789 IS
790
791 CURSOR get_shipment_line_info (
792 p_doc_id NUMBER
793 , p_doc_revision NUMBER
794 , p_gt_key NUMBER)
795 IS
796 SELECT pola.po_line_id
797 , plla.line_location_id
798 , pola.line_num
799 , plla.shipment_num
800 , plla.quantity
801 , plla.quantity_cancelled quantity_canceled
802 , plla.price_override
803 , pola.item_description
804 , msik.concatenated_segments item
805 , pola.item_revision
806 , pola.vendor_product_num supplier_item_id
807 , pola.supplier_ref_number supplier_config_id
808 , NVL(muom.attribute15, muom.uom_code) uom
809 , poha.currency_code
810 , pola.order_type_lookup_code
811 , plla.need_by_date
812 , plla.promised_date
813 , NVL(plla.days_early_receipt_allowed, 0)
814 , NVL(plla.days_late_receipt_allowed, 0)
815 , plla.ship_to_organization_id
816 , hou.name ship_to_org_name
817 , plla.drop_ship_flag
818 , plla.ship_to_location_id
819 , hrl.location_code ship_to_location_code
820 , TRIM(ppf.first_name || ' ' || ppf.last_name) ship_to_contact_name
821 , ppf.email_address ship_to_contact_email
822 , HR_GENERAL.get_phone_number(
823 psg.deliver_to_person_id, 'W1', SYSDATE) ship_to_contact_phone
824 , HR_GENERAL.get_phone_number(
825 psg.deliver_to_person_id, 'W1', SYSDATE) ship_to_contact_fax
826 FROM po_headers_archive_all poha
827 , po_lines_archive_all pola
828 , po_line_locations_archive_all plla
829 , hr_all_organization_units hou
830 , hr_locations_all hrl
831 , mtl_system_items_kfv msik
832 , mtl_units_of_measure muom
833 , financials_system_params_all fsp
834 , per_all_people_f ppf
835 , ( SELECT psg.index_num1 line_location_id
836 , psg.num1 deliver_to_person_id
837 FROM po_session_gt psg
838 WHERE psg.key = p_gt_key ) psg
839 WHERE poha.po_header_id = p_doc_id
840 AND poha.revision_num = p_doc_revision
841 AND pola.po_header_id = poha.po_header_id
842 AND pola.revision_num =
843 ( SELECT MAX(pola2.revision_num)
844 FROM po_lines_archive_all pola2
845 WHERE pola2.po_line_id = pola.po_line_id
846 AND pola2.revision_num <= poha.revision_num )
847 AND plla.po_line_id = pola.po_line_id
848 AND plla.revision_num =
849 ( SELECT MAX(plla2.revision_num)
850 FROM po_line_locations_archive_all plla2
851 WHERE plla2.line_location_id = plla.line_location_id
852 AND plla2.revision_num <= poha.revision_num )
853 AND psg.line_location_id (+) = plla.line_location_id
854 AND ppf.person_id (+) = psg.deliver_to_person_id
855 AND TRUNC(SYSDATE)
856 BETWEEN ppf.effective_start_date (+) AND ppf.effective_end_date (+)
857 AND hou.organization_id = plla.ship_to_organization_id
858 AND hrl.location_id (+) = plla.ship_to_location_id
859 AND NVL(fsp.org_id, -99) = NVL(pola.org_id, -99)
860 AND msik.inventory_item_id (+) = pola.item_id
861 AND NVL(msik.organization_id,
862 fsp.inventory_organization_id) = fsp.inventory_organization_id
863 AND muom.unit_of_measure = pola.unit_meas_lookup_code
864 AND pola.order_type_lookup_code = 'QUANTITY'
865 AND NVL(msik.outside_operation_flag, 'N') = 'N'
866 AND plla.approved_flag = 'Y'
867 AND NVL(plla.cancel_flag, 'N') <> 'Y';
868
869 l_count NUMBER;
870 l_otm_schedule_line PO_OTM_SCHEDULE_LINE_TYPE;
871 l_gt_key1 NUMBER;
872 l_gt_key2 NUMBER;
873
874 d_progress VARCHAR2(3);
875 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'GET_APPROVED_PO';
876
877 BEGIN
878
879 d_progress := '000';
880
881 IF (g_debug_stmt) THEN
882 PO_DEBUG.debug_begin(d_module);
883 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_id', p_doc_id);
884 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_revision', p_doc_revision);
885 END IF;
886
887 d_progress := '010';
888
889 -- get header info
890 SELECT poha.po_header_id
891 , poha.segment1
892 , poha.freight_terms_lookup_code
893 , poha.shipping_control
894 , poha.vendor_id
895 , poha.vendor_site_id
896 , pov.vendor_name
897 , povs.address_line1
898 , povs.address_line2
899 , povs.address_line3
900 , povs.city
901 , fter.iso_territory_code
902 , povs.vendor_site_code
903 , povs.zip
904 , DECODE(povs.state, NULL,
905 DECODE(povs.province, NULL, povs.county, povs.province), povs.state)
906 , povc.prefix
907 , povc.first_name
908 , povc.middle_name
909 , povc.last_name
910 , povc.area_code || povc.phone
911 , povc.email_address
912 , povc.fax_area_code || povc.fax
913 , poha.org_id
914 , hou.name
915 , hrl.location_id
916 , hrl.location_code
917 , ppf.first_name
918 , ppf.last_name
919 , hr_general.get_phone_number(poha.agent_id, 'W1', SYSDATE)
920 , ppf.email_address
921 , hr_general.get_phone_number(poha.agent_id, 'WF', SYSDATE)
922 , poha.bill_to_location_id
923 , hrl2.location_code
924 , apt.name -- terms
925 INTO x_otm_doc.po_header_id
926 , x_otm_doc.po_number
927 , x_otm_doc.freight_terms_lookup_code
928 , x_otm_doc.shipping_control
929 , x_otm_doc.supplier_id
930 , x_otm_doc.supplier_site_id
931 , x_otm_doc.supplier_name
932 , x_otm_doc.supplier_addr_line_1
933 , x_otm_doc.supplier_addr_line_2
934 , x_otm_doc.supplier_addr_line_3
935 , x_otm_doc.supplier_addr_city
936 , x_otm_doc.supplier_addr_country
937 , x_otm_doc.supplier_site_code
938 , x_otm_doc.supplier_addr_zip
939 , x_otm_doc.supplier_addr_state_province
940 , x_otm_doc.supplier_contact_prefix
941 , x_otm_doc.supplier_contact_first_name
942 , x_otm_doc.supplier_contact_middle_name
943 , x_otm_doc.supplier_contact_last_name
944 , x_otm_doc.supplier_contact_phone
945 , x_otm_doc.supplier_contact_email
946 , x_otm_doc.supplier_contact_fax
947 , x_otm_doc.org_id
948 , x_otm_doc.org_name
949 , x_otm_doc.org_location_id
950 , x_otm_doc.org_location_code
951 , x_otm_doc.buyer_first_name
952 , x_otm_doc.buyer_last_name
953 , x_otm_doc.buyer_phone
954 , x_otm_doc.buyer_email
955 , x_otm_doc.buyer_fax
956 , x_otm_doc.bill_to_location_id
957 , x_otm_doc.bill_to_location_code
958 , x_otm_doc.terms
959 FROM po_headers_archive_all poha
960 , po_vendors pov
961 , po_vendor_sites_all povs
962 , fnd_territories fter
963 , po_vendor_contacts povc
964 , hr_all_organization_units hou
965 , hr_locations_all hrl
966 , per_all_people_f ppf
967 , hr_locations_all hrl2
968 , ap_terms apt
969 WHERE poha.po_header_id = p_doc_id
970 AND poha.revision_num = p_doc_revision
971 AND poha.vendor_id = pov.vendor_id
972 AND poha.vendor_site_id = povs.vendor_site_id
973 AND fter.territory_code (+) = povs.country
974 AND poha.vendor_contact_id = povc.vendor_contact_id (+)
975 AND povs.vendor_site_id = NVL(povc.vendor_site_id,povs.vendor_site_id) /*bug 7173062, added the condition
976 to eliminate duplicate rows being returned when the same contact is assigned for different supplier sites */
977 AND poha.org_id = hou.organization_id
978 AND hrl.location_id = hou.location_id
979 AND ppf.person_id = poha.agent_id
980 AND trunc(sysdate)
981 BETWEEN ppf.effective_start_date (+) AND ppf.effective_end_date (+)
982 AND hrl2.location_id = poha.bill_to_location_id
983 AND apt.term_id (+) = poha.terms_id
984 AND poha.authorization_status = 'APPROVED'
985 AND NVL(poha.consigned_consumption_flag, 'N') = 'N'
986 ;
987
988 d_progress := '010';
989
990 -- Get Address info for the order header
991 populate_address_info (x_otm_doc => x_otm_doc);
992
993 d_progress := '012';
994
995 -- In order to properly select a requester for each shipment (which
996 -- should be the first non-null deliver-to-person on the shipment's
997 -- distributions, if one exists), we do a two-step pre-processing
998 -- on the PO's shipments and distributions:
999 -- 1. For the latest revision of each distribution with a
1000 -- non-null deliver to person on
1001 -- the PO, create a record in PO_SESSION_GT containing the
1002 --- line_location_id, distribution_num, and deliver_to_person_id.
1003 -- 2. From the data inserted in step 1, select the deliver_to_person_id
1004 -- from the first distribution on each shipment, inserting the
1005 -- results into PO_SESSION_GT.
1006 --
1007 -- Our main cursor query will then join with the entries created in step 2
1008 -- to get the appropriate requester for each shipment.
1009
1010 -- Step 1.
1011 l_gt_key1 := PO_CORE_S.get_session_gt_nextval();
1012
1013 d_progress := '015';
1014
1015 INSERT INTO po_session_gt
1016 ( key
1017 , index_num1 -- line_location_id
1018 , index_num2 -- distribution_num
1019 , num1 -- deliver_to_person_id
1020 )
1021 ( SELECT l_gt_key1
1022 , poda.line_location_id
1023 , poda.distribution_num
1024 , poda.deliver_to_person_id
1025 FROM po_line_locations_archive_all plla
1026 , po_distributions_archive_all poda
1027 WHERE plla.po_header_id = p_doc_id
1028 AND plla.revision_num = ( SELECT MAX(plla2.revision_num)
1029 FROM
1030 po_line_locations_archive_all plla2
1031 WHERE plla2.line_location_id
1032 = plla.line_location_id
1033 AND plla2.po_header_id = p_doc_id
1034 AND plla2.revision_num <= p_doc_revision )
1035 AND poda.line_location_id = plla.line_location_id
1036 AND poda.revision_num = ( SELECT MAX(poda2.revision_num)
1037 FROM po_distributions_archive_all poda2
1038 WHERE poda2.po_distribution_id
1039 = poda.po_distribution_id
1040 AND poda2.line_location_id = plla.line_location_id
1041 AND poda2.revision_num <= p_doc_revision )
1042 AND NVL(plla.cancel_flag,'N') <> 'Y'
1043 AND poda.deliver_to_person_id IS NOT NULL
1044 )
1045 ;
1046
1047 d_progress := '020';
1048
1049 -- Step 2
1050 l_gt_key2 := PO_CORE_S.get_session_gt_nextval();
1051
1052 d_progress := '025';
1053
1054 INSERT INTO po_session_gt
1055 ( key
1056 , index_num1 -- line_location_id
1057 , num1 -- deliver_to_person_id
1058 )
1059 ( SELECT l_gt_key2
1060 , psg.index_num1
1061 , psg.num1
1062 FROM po_session_gt psg
1063 , ( SELECT MIN(psg2.index_num2) distribution_num
1064 , psg2.index_num1 line_location_id
1065 FROM po_session_gt psg2
1066 WHERE psg2.key = l_gt_key1
1067 GROUP BY psg2.index_num1 ) min_dists
1068 WHERE psg.key = l_gt_key1
1069 AND psg.index_num1 = min_dists.line_location_id
1070 AND psg.index_num2 = min_dists.distribution_num
1071 )
1072 ;
1073
1074 d_progress := '030';
1075
1076 -- initialize table for shedule line info
1077 x_otm_doc.schedule_lines := PO_OTM_SCHEDULE_LINE_TBL();
1078
1079 d_progress := '040';
1080
1081 -- open cursor to pull shipment data
1082 OPEN get_shipment_line_info (
1083 p_doc_id => p_doc_id
1084 , p_doc_revision => p_doc_revision
1085 , p_gt_key => l_gt_key2 );
1086
1087 d_progress := '050';
1088
1089 l_count := 1;
1090
1091 -- pull all shipments with pertinent line info
1092 LOOP
1093 d_progress := '100';
1094
1095 l_otm_schedule_line := PO_OTM_SCHEDULE_LINE_TYPE.new_instance();
1096
1097 d_progress := '110';
1098
1099 FETCH get_shipment_line_info
1100 INTO l_otm_schedule_line.po_line_id
1101 , l_otm_schedule_line.line_location_id
1102 , l_otm_schedule_line.line_num
1103 , l_otm_schedule_line.shipment_num
1104 , l_otm_schedule_line.quantity
1105 , l_otm_schedule_line.quantity_canceled
1106 , l_otm_schedule_line.price_override
1107 , l_otm_schedule_line.item_description
1108 , l_otm_schedule_line.item
1109 , l_otm_schedule_line.item_revision
1110 , l_otm_schedule_line.supplier_item_id
1111 , l_otm_schedule_line.supplier_ref_num
1112 , l_otm_schedule_line.uom
1113 , l_otm_schedule_line.currency_code
1114 , l_otm_schedule_line.order_type_lookup_code
1115 , l_otm_schedule_line.need_by_date
1116 , l_otm_schedule_line.promised_date
1117 , l_otm_schedule_line.days_early_receipt_allowed
1118 , l_otm_schedule_line.days_late_receipt_allowed
1119 , l_otm_schedule_line.ship_to_organization_id
1120 , l_otm_schedule_line.ship_to_org_name
1121 , l_otm_schedule_line.drop_ship_flag
1122 , l_otm_schedule_line.ship_to_location_id
1123 , l_otm_schedule_line.ship_to_location_code
1124 , l_otm_schedule_line.ship_to_contact_name
1125 , l_otm_schedule_line.ship_to_contact_email
1126 , l_otm_schedule_line.ship_to_contact_phone
1127 , l_otm_schedule_line.ship_to_contact_fax
1128 ;
1129
1130 EXIT WHEN get_shipment_line_info%NOTFOUND;
1131
1132 d_progress := '120';
1133
1134 IF (g_debug_stmt) THEN
1135 PO_DEBUG.debug_stmt(d_module, d_progress, 'Got schedule line. line_location_id='
1136 || TO_CHAR(l_otm_schedule_line.line_location_id));
1137 END IF;
1138
1139 -- Populate ship-to address info (including drop-ship info)
1140 d_progress := '150';
1141
1142 populate_address_info(
1143 x_otm_doc => x_otm_doc
1144 , x_otm_sched_line => l_otm_schedule_line );
1145
1146 d_progress := '160';
1147
1148 x_otm_doc.schedule_lines.extend;
1149 x_otm_doc.schedule_lines(l_count) := l_otm_schedule_line;
1150
1151 d_progress := '190';
1152
1153 l_count := l_count + 1;
1154 END LOOP;
1155
1156 d_progress := '060';
1157
1158 CLOSE get_shipment_line_info;
1159
1160 d_progress := '070';
1161
1162 IF (g_debug_stmt) THEN
1163 PO_DEBUG.debug_end(d_module);
1164 END IF;
1165
1166 EXCEPTION
1167 WHEN NO_DATA_FOUND THEN
1168 IF (g_debug_stmt) THEN
1169 PO_DEBUG.debug_unexp(d_module, d_progress, 'No document header found. This probably means that archive-on-approval is not set');
1170 END IF;
1171 RAISE;
1172 WHEN OTHERS THEN
1173 IF (g_debug_stmt) THEN
1174 PO_DEBUG.debug_unexp(d_module, d_progress, 'Exception retrieving document');
1175 END IF;
1176 RAISE;
1177
1178 END get_approved_po;
1179
1180 PROCEDURE get_canceled_po (
1181 p_doc_id IN NUMBER
1182 , p_doc_revision IN NUMBER
1183 , p_line_id IN NUMBER
1184 , p_line_loc_id IN NUMBER
1185 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
1186 )
1187 IS
1188
1189 d_progress VARCHAR2(3);
1190 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'GET_CANCELED_PO';
1191
1192 BEGIN
1193
1194 d_progress := '000';
1195
1196 IF (g_debug_stmt) THEN
1197 PO_DEBUG.debug_begin(d_module);
1198 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_id', p_doc_id);
1199 PO_DEBUG.debug_var(d_module, d_progress, 'p_line_id', p_line_id);
1200 PO_DEBUG.debug_var(d_module, d_progress, 'p_line_loc_id', p_line_loc_id);
1201 END IF;
1202
1203 d_progress := '020';
1204
1205 get_po_for_status_change (
1206 p_doc_id => p_doc_id
1207 , p_doc_revision => p_doc_revision
1208 , p_line_id => p_line_id
1209 , p_line_loc_id => p_line_loc_id
1210 , x_otm_doc => x_otm_doc );
1211
1212 d_progress := '030';
1213
1214 IF (g_debug_stmt) THEN
1215 PO_DEBUG.debug_end(d_module);
1216 END IF;
1217
1218 EXCEPTION
1219 WHEN OTHERS THEN
1220 IF (g_debug_stmt) THEN
1221 PO_DEBUG.debug_unexp(d_module, d_progress, 'Exception retrieving document');
1222 END IF;
1223 RAISE;
1224
1225 END get_canceled_po;
1226
1227 PROCEDURE get_closed_po (
1228 p_doc_id IN NUMBER
1229 , p_doc_revision IN NUMBER
1230 , p_line_id IN NUMBER
1231 , p_line_loc_id IN NUMBER
1232 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
1233 )
1234 IS
1235
1236 d_progress VARCHAR2(3);
1237 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'GET_CLOSED_PO';
1238
1239 BEGIN
1240
1241 d_progress := '000';
1242
1243 IF (g_debug_stmt) THEN
1244 PO_DEBUG.debug_begin(d_module);
1245 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_id', p_doc_id);
1246 PO_DEBUG.debug_var(d_module, d_progress, 'p_line_id', p_line_id);
1247 PO_DEBUG.debug_var(d_module, d_progress, 'p_line_loc_id', p_line_loc_id);
1248 END IF;
1249
1250 d_progress := '020';
1251
1252 get_po_for_status_change (
1253 p_doc_id => p_doc_id
1254 , p_doc_revision => p_doc_revision
1255 , p_line_id => p_line_id
1256 , p_line_loc_id => p_line_loc_id
1257 , x_otm_doc => x_otm_doc );
1258
1259 d_progress := '030';
1260
1261 IF (g_debug_stmt) THEN
1262 PO_DEBUG.debug_end(d_module);
1263 END IF;
1264
1265 EXCEPTION
1266 WHEN OTHERS THEN
1267 IF (g_debug_stmt) THEN
1268 PO_DEBUG.debug_unexp(d_module, d_progress, 'Exception retrieving document');
1269 END IF;
1270 RAISE;
1271
1272 END get_closed_po;
1273
1274 PROCEDURE get_opened_po (
1275 p_doc_id IN NUMBER
1276 , p_doc_revision IN NUMBER
1277 , p_line_id IN NUMBER
1278 , p_line_loc_id IN NUMBER
1279 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
1280 )
1281 IS
1282
1283 d_progress VARCHAR2(3);
1284 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'GET_OPENED_PO';
1285
1286 BEGIN
1287
1288 d_progress := '000';
1289
1290 IF (g_debug_stmt) THEN
1291 PO_DEBUG.debug_begin(d_module);
1292 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_id', p_doc_id);
1293 PO_DEBUG.debug_var(d_module, d_progress, 'p_line_id', p_line_id);
1294 PO_DEBUG.debug_var(d_module, d_progress, 'p_line_loc_id', p_line_loc_id);
1295 END IF;
1296
1297 d_progress := '020';
1298
1299 get_po_for_status_change (
1300 p_doc_id => p_doc_id
1301 , p_doc_revision => p_doc_revision
1302 , p_line_id => p_line_id
1303 , p_line_loc_id => p_line_loc_id
1304 , x_otm_doc => x_otm_doc );
1305
1306 d_progress := '030';
1307
1308 IF (g_debug_stmt) THEN
1309 PO_DEBUG.debug_end(d_module);
1310 END IF;
1311
1312 EXCEPTION
1313 WHEN OTHERS THEN
1314 IF (g_debug_stmt) THEN
1315 PO_DEBUG.debug_unexp(d_module, d_progress, 'Exception retrieving document');
1316 END IF;
1317 RAISE;
1318
1319 END get_opened_po;
1320
1321 PROCEDURE get_po_for_status_change (
1322 p_doc_id IN NUMBER
1323 , p_doc_revision IN NUMBER
1324 , p_line_id IN NUMBER
1325 , p_line_loc_id IN NUMBER
1326 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
1327 )
1328 IS
1329
1330 CURSOR get_shipment_line_info (
1331 p_doc_id NUMBER
1332 , p_doc_revision NUMBER
1333 , p_line_id NUMBER
1334 , p_line_loc_id NUMBER
1335 )
1336 IS
1337 SELECT pola.po_line_id
1338 , plla.line_location_id
1339 , pola.line_num
1340 , plla.shipment_num
1341 FROM po_headers_archive_all poha
1342 , po_lines_archive_all pola
1343 , po_line_locations_archive_all plla
1344 , financials_system_params_all fsp
1345 , mtl_system_items msi
1346 WHERE poha.po_header_id = p_doc_id
1347 AND poha.revision_num = p_doc_revision
1348 AND pola.po_header_id = poha.po_header_id
1349 AND pola.revision_num =
1350 ( SELECT MAX(pola2.revision_num)
1351 FROM po_lines_archive_all pola2
1352 WHERE pola2.po_line_id = pola.po_line_id
1353 AND pola2.revision_num <= poha.revision_num )
1354 AND plla.po_line_id = pola.po_line_id
1355 AND plla.revision_num =
1356 ( SELECT MAX(plla2.revision_num)
1357 FROM po_line_locations_archive_all plla2
1358 WHERE plla2.line_location_id = plla.line_location_id
1359 AND plla2.revision_num <= poha.revision_num )
1360 AND pola.order_type_lookup_code = 'QUANTITY'
1361 AND fsp.org_id = pola.org_id
1362 AND msi.inventory_item_id (+) = pola.item_id
1363 AND NVL(msi.organization_id,
1364 fsp.inventory_organization_id) = fsp.inventory_organization_id
1365 AND NVL(msi.outside_operation_flag, 'N') = 'N'
1366 AND NVL(p_line_id, pola.po_line_id) = pola.po_line_id
1367 AND NVL(p_line_loc_id, plla.line_location_id) = plla.line_location_id
1368 ;
1369
1370 l_count NUMBER;
1371 l_otm_schedule_line PO_OTM_SCHEDULE_LINE_TYPE;
1372
1373 d_progress VARCHAR2(3);
1374 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'GET_PO_FOR_STATUS_CHANGE';
1375
1376 BEGIN
1377
1378 d_progress := '000';
1379
1380 IF (g_debug_stmt) THEN
1381 PO_DEBUG.debug_begin(d_module);
1382 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_id', p_doc_id);
1383 PO_DEBUG.debug_var(d_module, d_progress, 'p_line_id', p_line_id);
1384 PO_DEBUG.debug_var(d_module, d_progress, 'p_line_loc_id', p_line_loc_id);
1385 END IF;
1386
1387 d_progress := '020';
1388
1389 -- get header info
1390 SELECT poha.po_header_id
1391 , poha.segment1
1392 , hou.name
1393 INTO x_otm_doc.po_header_id
1394 , x_otm_doc.po_number
1395 , x_otm_doc.org_name
1396 FROM po_headers_archive_all poha
1397 , hr_all_organization_units hou
1398 WHERE poha.po_header_id = p_doc_id
1399 AND poha.revision_num = p_doc_revision
1400 AND poha.org_id = hou.organization_id
1401 AND NVL(poha.consigned_consumption_flag, 'N') = 'N'
1402 ;
1403
1404 d_progress := '030';
1405
1406 -- initialize table for shedule line info
1407 x_otm_doc.schedule_lines := PO_OTM_SCHEDULE_LINE_TBL();
1408
1409 d_progress := '040';
1410
1411 -- if the event occurred at the line or shipment level, pull
1412 -- that info
1413 IF (p_line_id IS NOT NULL OR p_line_loc_id IS NOT NULL) THEN
1414 -- open cursor to pull shipment data
1415 OPEN get_shipment_line_info (
1416 p_doc_id => p_doc_id
1417 , p_doc_revision => p_doc_revision
1418 , p_line_id => p_line_id
1419 , p_line_loc_id => p_line_loc_id);
1420
1421 d_progress := '050';
1422
1423 l_count := 1;
1424
1425 -- pull all shipments with pertinent line info
1426 LOOP
1427 d_progress := '100';
1428
1429 l_otm_schedule_line := PO_OTM_SCHEDULE_LINE_TYPE.new_instance();
1430
1431 d_progress := '110';
1432
1433 FETCH get_shipment_line_info
1434 INTO l_otm_schedule_line.po_line_id
1435 , l_otm_schedule_line.line_location_id
1436 , l_otm_schedule_line.line_num
1437 , l_otm_schedule_line.shipment_num
1438 ;
1439 EXIT WHEN get_shipment_line_info%NOTFOUND;
1440
1441 d_progress := '120';
1442
1443 IF (g_debug_stmt) THEN
1444 PO_DEBUG.debug_stmt(d_module, d_progress, 'Got schedule line. line_location_id=' || TO_CHAR(l_otm_schedule_line.line_location_id));
1445 END IF;
1446
1447 x_otm_doc.schedule_lines.extend;
1448 x_otm_doc.schedule_lines(l_count) := l_otm_schedule_line;
1449
1450 d_progress := '130';
1451
1452 l_count := l_count + 1;
1453 END LOOP;
1454
1455 d_progress := '060';
1456
1457 CLOSE get_shipment_line_info;
1458 END IF; -- IF (p_line_id IS NOT NULL OR p_line_loc_id IS NOT NULL) THEN
1459
1460 d_progress := '070';
1461
1462 IF (g_debug_stmt) THEN
1463 PO_DEBUG.debug_end(d_module);
1464 END IF;
1465
1466 EXCEPTION
1467 WHEN OTHERS THEN
1468 IF (g_debug_stmt) THEN
1469 PO_DEBUG.debug_unexp(d_module, d_progress, 'Exception retrieving document');
1470 END IF;
1471 RAISE;
1472
1473 END get_po_for_status_change;
1474
1475
1476
1477
1478 PROCEDURE get_held_po (
1479 p_doc_id IN NUMBER
1480 , p_doc_revision IN NUMBER
1481 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
1482 )
1483 IS
1484
1485 d_progress VARCHAR2(3);
1486 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'GET_HELD_PO';
1487
1488 BEGIN
1489
1490 d_progress := '000';
1491
1492 IF (g_debug_stmt) THEN
1493 PO_DEBUG.debug_begin(d_module);
1494 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_id', p_doc_id);
1495 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_revision', p_doc_revision);
1496 END IF;
1497
1498 d_progress := '020';
1499
1500 -- get header info
1501 SELECT poha.po_header_id
1502 , poha.segment1
1503 , hou.name
1504 INTO x_otm_doc.po_header_id
1505 , x_otm_doc.po_number
1506 , x_otm_doc.org_name
1507 FROM po_headers_archive_all poha
1508 , hr_all_organization_units hou
1509 WHERE poha.po_header_id = p_doc_id
1510 AND poha.revision_num = p_doc_revision
1511 AND poha.org_id = hou.organization_id
1512 AND NVL(poha.consigned_consumption_flag, 'N') = 'N'
1513 ;
1514
1515 d_progress := '030';
1516
1517 IF (g_debug_stmt) THEN
1518 PO_DEBUG.debug_end(d_module);
1519 END IF;
1520
1521 EXCEPTION
1522 WHEN NO_DATA_FOUND THEN
1523 IF (g_debug_stmt) THEN
1524 PO_DEBUG.debug_unexp(d_module, d_progress, 'No data found. This probably means that archive-on-approve is off.');
1525 END IF;
1526 RAISE;
1527
1528 WHEN OTHERS THEN
1529 IF (g_debug_stmt) THEN
1530 PO_DEBUG.debug_unexp(d_module, d_progress, 'Exception retrieving document');
1531 END IF;
1532 RAISE;
1533
1534 END get_held_po;
1535
1536
1537 PROCEDURE get_approved_release (
1538 p_doc_id IN NUMBER
1539 , p_doc_revision IN NUMBER
1540 , p_blanket_revision IN NUMBER
1541 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
1542 )
1543 IS
1544
1545 CURSOR get_shipment_line_info (
1546 p_doc_id NUMBER
1547 , p_doc_revision NUMBER
1548 , p_blanket_revision NUMBER
1549 , p_gt_key NUMBER)
1550 IS
1551 SELECT pola.po_line_id
1552 , plla.line_location_id
1553 , pola.line_num
1554 , plla.shipment_num
1555 , plla.quantity
1556 , plla.quantity_cancelled quantity_canceled
1557 , plla.price_override
1558 , pola.item_description
1559 , msik.concatenated_segments item
1560 , pola.item_revision
1561 , pola.vendor_product_num supplier_item_id
1562 , pola.supplier_ref_number supplier_config_id
1563 , NVL(muom.attribute15, muom.uom_code) uom
1564 , poha.currency_code
1565 , pola.order_type_lookup_code
1566 , plla.need_by_date
1567 , plla.promised_date
1568 , NVL(plla.days_early_receipt_allowed, 0)
1569 , NVL(plla.days_late_receipt_allowed, 0)
1570 , plla.ship_to_organization_id
1571 , hou.name ship_to_org_name
1572 , plla.drop_ship_flag
1573 , plla.ship_to_location_id
1574 , hrl.location_code ship_to_location_code
1575 , TRIM(ppf.first_name || ' ' || ppf.last_name) ship_to_contact_name
1576 , ppf.email_address ship_to_contact_email
1577 , HR_GENERAL.get_phone_number(
1578 psg.deliver_to_person_id, 'W1', SYSDATE) ship_to_contact_phone
1579 , HR_GENERAL.get_phone_number(
1580 psg.deliver_to_person_id, 'W1', SYSDATE) ship_to_contact_fax
1581 FROM po_headers_archive_all poha
1582 , po_releases_archive_all pora
1583 , po_lines_archive_all pola
1584 , po_line_locations_archive_all plla
1585 , hr_all_organization_units hou
1586 , hr_locations_all hrl
1587 , mtl_system_items_kfv msik
1588 , mtl_units_of_measure muom
1589 , financials_system_params_all fsp
1590 , per_all_people_f ppf
1591 , ( SELECT psg.index_num1 line_location_id
1592 , psg.num1 deliver_to_person_id
1593 FROM po_session_gt psg
1594 WHERE psg.key = p_gt_key ) psg
1595 WHERE pora.po_release_id = p_doc_id
1596 AND pora.revision_num = p_doc_revision
1597 AND poha.po_header_id = pora.po_header_id
1598 AND poha.revision_num = p_blanket_revision
1599 AND pola.po_header_id = poha.po_header_id
1600 AND pola.revision_num =
1601 ( SELECT MAX(pola2.revision_num)
1602 FROM po_lines_archive_all pola2
1603 WHERE pola2.po_line_id = pola.po_line_id
1604 AND pola2.revision_num <= poha.revision_num )
1605 AND plla.po_line_id = pola.po_line_id
1606 AND plla.po_release_id = pora.po_release_id
1607 AND plla.revision_num =
1608 ( SELECT MAX(plla2.revision_num)
1609 FROM po_line_locations_archive_all plla2
1610 WHERE plla2.line_location_id = plla.line_location_id
1611 AND plla2.revision_num <= pora.revision_num )
1612 AND psg.line_location_id (+) = plla.line_location_id
1613 AND ppf.person_id (+) = psg.deliver_to_person_id
1614 AND TRUNC(SYSDATE)
1615 BETWEEN ppf.effective_start_date (+) AND ppf.effective_end_date (+)
1616 AND hou.organization_id = plla.ship_to_organization_id
1617 AND hrl.location_id (+) = plla.ship_to_location_id
1618 AND NVL(fsp.org_id, -99) = NVL(pola.org_id, -99)
1619 AND msik.inventory_item_id (+) = pola.item_id
1620 AND NVL(msik.organization_id,
1621 fsp.inventory_organization_id) = fsp.inventory_organization_id
1622 AND muom.unit_of_measure = pola.unit_meas_lookup_code
1623 AND pola.order_type_lookup_code = 'QUANTITY'
1624 AND NVL(msik.outside_operation_flag, 'N') = 'N'
1625 AND plla.approved_flag = 'Y'
1626 AND NVL(plla.cancel_flag, 'N') <> 'Y';
1627
1628 l_count NUMBER;
1629 l_otm_schedule_line PO_OTM_SCHEDULE_LINE_TYPE;
1630 l_gt_key1 NUMBER;
1631 l_gt_key2 NUMBER;
1632
1633 d_progress VARCHAR2(3);
1634 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'GET_APPROVED_RELEASE';
1635
1636 BEGIN
1637
1638 d_progress := '000';
1639
1640 IF (g_debug_stmt) THEN
1641 PO_DEBUG.debug_begin(d_module);
1642 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_id', p_doc_id);
1643 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_revision', p_doc_revision);
1644 PO_DEBUG.debug_var(d_module, d_progress, 'p_blanket_revision', p_blanket_revision);
1645 END IF;
1646
1647 d_progress := '010';
1648
1649 -- get header info
1650 SELECT poha.po_header_id
1651 , pora.po_release_id
1652 , poha.segment1
1653 , pora.release_num
1654 , poha.freight_terms_lookup_code
1655 , pora.shipping_control
1656 , poha.vendor_id
1657 , poha.vendor_site_id
1658 , pov.vendor_name
1659 , povs.address_line1
1660 , povs.address_line2
1661 , povs.address_line3
1662 , povs.city
1663 , fter.iso_territory_code
1664 , povs.vendor_site_code
1665 , povs.zip
1666 , DECODE(povs.state, NULL,
1667 DECODE(povs.province, NULL, povs.county, povs.province), povs.state)
1668 , povc.prefix
1669 , povc.first_name
1670 , povc.middle_name
1671 , povc.last_name
1672 , povc.area_code || povc.phone
1673 , povc.email_address
1674 , povc.fax_area_code || povc.fax
1675 , poha.org_id
1676 , hou.name
1677 , hrl.location_id
1678 , hrl.location_code
1679 , ppf.first_name
1680 , ppf.last_name
1681 , hr_general.get_phone_number(poha.agent_id, 'W1', SYSDATE)
1682 , ppf.email_address
1683 , hr_general.get_phone_number(poha.agent_id, 'WF', SYSDATE)
1684 , poha.bill_to_location_id
1685 , hrl2.location_code
1686 , apt.name -- terms
1687 INTO x_otm_doc.po_header_id
1688 , x_otm_doc.po_release_id
1689 , x_otm_doc.po_number
1690 , x_otm_doc.release_number
1691 , x_otm_doc.freight_terms_lookup_code
1692 , x_otm_doc.shipping_control
1693 , x_otm_doc.supplier_id
1694 , x_otm_doc.supplier_site_id
1695 , x_otm_doc.supplier_name
1696 , x_otm_doc.supplier_addr_line_1
1697 , x_otm_doc.supplier_addr_line_2
1698 , x_otm_doc.supplier_addr_line_3
1699 , x_otm_doc.supplier_addr_city
1700 , x_otm_doc.supplier_addr_country
1701 , x_otm_doc.supplier_site_code
1702 , x_otm_doc.supplier_addr_zip
1703 , x_otm_doc.supplier_addr_state_province
1704 , x_otm_doc.supplier_contact_prefix
1705 , x_otm_doc.supplier_contact_first_name
1706 , x_otm_doc.supplier_contact_middle_name
1707 , x_otm_doc.supplier_contact_last_name
1708 , x_otm_doc.supplier_contact_phone
1709 , x_otm_doc.supplier_contact_email
1710 , x_otm_doc.supplier_contact_fax
1711 , x_otm_doc.org_id
1712 , x_otm_doc.org_name
1713 , x_otm_doc.org_location_id
1714 , x_otm_doc.org_location_code
1715 , x_otm_doc.buyer_first_name
1716 , x_otm_doc.buyer_last_name
1717 , x_otm_doc.buyer_phone
1718 , x_otm_doc.buyer_email
1719 , x_otm_doc.buyer_fax
1720 , x_otm_doc.bill_to_location_id
1721 , x_otm_doc.bill_to_location_code
1722 , x_otm_doc.terms
1723 FROM po_headers_archive_all poha
1724 , po_releases_archive_all pora
1725 , po_vendors pov
1726 , po_vendor_sites_all povs
1727 , fnd_territories fter
1728 , po_vendor_contacts povc
1729 , hr_all_organization_units hou
1730 , hr_locations_all hrl
1731 , per_all_people_f ppf
1732 , hr_locations_all hrl2
1733 , ap_terms apt
1734 WHERE pora.po_release_id = p_doc_id
1735 AND pora.revision_num = p_doc_revision
1736 AND poha.po_header_id = pora.po_header_id
1737 AND poha.revision_num = p_blanket_revision
1738 AND poha.vendor_id = pov.vendor_id
1739 AND poha.vendor_site_id = povs.vendor_site_id
1740 AND fter.territory_code (+) = povs.country
1741 AND poha.vendor_contact_id = povc.vendor_contact_id (+)
1742 AND povs.vendor_site_id = NVL(povc.vendor_site_id,povs.vendor_site_id) /*bug 7173062, added the condition to
1743 eliminate duplicate rows being returned when the same contact is assigned for different supplier sites */
1744 AND poha.org_id = hou.organization_id
1745 AND hrl.location_id = hou.location_id
1746 AND ppf.person_id = pora.agent_id
1747 AND trunc(sysdate)
1748 BETWEEN ppf.effective_start_date (+) AND ppf.effective_end_date (+)
1749 AND hrl2.location_id = poha.bill_to_location_id
1750 AND apt.term_id (+) = poha.terms_id
1751 AND poha.authorization_status = 'APPROVED'
1752 AND NVL(poha.consigned_consumption_flag, 'N') = 'N'
1753 ;
1754
1755 d_progress := '010';
1756
1757 -- Get address info for header
1758 populate_address_info (x_otm_doc => x_otm_doc);
1759
1760 d_progress := '012';
1761
1762 -- In order to properly select a requester for each shipment (which
1763 -- should be the first non-null deliver-to-person on the shipment's
1764 -- distributions, if one exists), we do a two-step pre-processing
1765 -- on the PO's shipments and distributions:
1766 -- 1. For the latest revision of each distribution with a
1767 -- non-null deliver to person on
1768 -- the PO, create a record in PO_SESSION_GT containing the
1769 --- line_location_id, distribution_num, and deliver_to_person_id.
1770 -- 2. From the data inserted in step 1, select the deliver_to_person_id
1771 -- from the first distribution on each shipment, inserting the
1772 -- results into PO_SESSION_GT.
1773 --
1774 -- Our main cursor query will then join with the entries created in step 2
1775 -- to get the appropriate requester for each shipment.
1776
1777 -- Step 1.
1778 l_gt_key1 := PO_CORE_S.get_session_gt_nextval();
1779
1780 d_progress := '015';
1781
1782 INSERT INTO po_session_gt
1783 ( key
1784 , index_num1 -- line_location_id
1785 , index_num2 -- distribution_num
1786 , num1 -- deliver_to_person_id
1787 )
1788 ( SELECT l_gt_key1
1789 , poda.line_location_id
1790 , poda.distribution_num
1791 , poda.deliver_to_person_id
1792 FROM po_line_locations_archive_all plla
1793 , po_distributions_archive_all poda
1794 WHERE plla.po_release_id = p_doc_id
1795 AND plla.revision_num = ( SELECT MAX(plla2.revision_num)
1796 FROM
1797 po_line_locations_archive_all plla2
1798 WHERE plla2.line_location_id
1799 = plla.line_location_id
1800 AND plla2.po_release_id = p_doc_id
1801 AND plla2.revision_num <= p_doc_revision )
1802 AND poda.line_location_id = plla.line_location_id
1803 AND poda.revision_num = ( SELECT MAX(poda2.revision_num)
1804 FROM po_distributions_archive_all poda2
1805 WHERE poda2.po_distribution_id
1806 = poda.po_distribution_id
1807 AND poda2.line_location_id = plla.line_location_id
1808 AND poda2.revision_num <= p_doc_revision )
1809 AND NVL(plla.cancel_flag,'N') <> 'Y'
1810 AND poda.deliver_to_person_id IS NOT NULL
1811 )
1812 ;
1813
1814 d_progress := '020';
1815
1816 -- Step 2
1817 l_gt_key2 := PO_CORE_S.get_session_gt_nextval();
1818
1819 d_progress := '025';
1820
1821 INSERT INTO po_session_gt
1822 ( key
1823 , index_num1 -- line_location_id
1824 , num1 -- deliver_to_person_id
1825 )
1826 ( SELECT l_gt_key2
1827 , psg.index_num1
1828 , psg.num1
1829 FROM po_session_gt psg
1830 , ( SELECT MIN(psg2.index_num2) distribution_num
1831 , psg2.index_num1 line_location_id
1832 FROM po_session_gt psg2
1833 WHERE psg2.key = l_gt_key1
1834 GROUP BY psg2.index_num1 ) min_dists
1835 WHERE psg.key = l_gt_key1
1836 AND psg.index_num1 = min_dists.line_location_id
1837 AND psg.index_num2 = min_dists.distribution_num
1838 )
1839 ;
1840
1841 d_progress := '030';
1842
1843 -- initialize table for shedule line info
1844 x_otm_doc.schedule_lines := PO_OTM_SCHEDULE_LINE_TBL();
1845
1846 d_progress := '040';
1847
1848 -- open cursor to pull shipment data
1849 OPEN get_shipment_line_info (
1850 p_doc_id => p_doc_id
1851 , p_doc_revision => p_doc_revision
1852 , p_blanket_revision => p_blanket_revision
1853 , p_gt_key => l_gt_key2 );
1854
1855 d_progress := '050';
1856
1857 l_count := 1;
1858
1859 -- pull all shipments with pertinent line info
1860 LOOP
1861 d_progress := '100';
1862
1863 l_otm_schedule_line := PO_OTM_SCHEDULE_LINE_TYPE.new_instance();
1864
1865 d_progress := '110';
1866
1867 FETCH get_shipment_line_info
1868 INTO l_otm_schedule_line.po_line_id
1869 , l_otm_schedule_line.line_location_id
1870 , l_otm_schedule_line.line_num
1871 , l_otm_schedule_line.shipment_num
1872 , l_otm_schedule_line.quantity
1873 , l_otm_schedule_line.quantity_canceled
1874 , l_otm_schedule_line.price_override
1875 , l_otm_schedule_line.item_description
1876 , l_otm_schedule_line.item
1877 , l_otm_schedule_line.item_revision
1878 , l_otm_schedule_line.supplier_item_id
1879 , l_otm_schedule_line.supplier_ref_num
1880 , l_otm_schedule_line.uom
1881 , l_otm_schedule_line.currency_code
1882 , l_otm_schedule_line.order_type_lookup_code
1883 , l_otm_schedule_line.need_by_date
1884 , l_otm_schedule_line.promised_date
1885 , l_otm_schedule_line.days_early_receipt_allowed
1886 , l_otm_schedule_line.days_late_receipt_allowed
1887 , l_otm_schedule_line.ship_to_organization_id
1888 , l_otm_schedule_line.ship_to_org_name
1889 , l_otm_schedule_line.drop_ship_flag
1890 , l_otm_schedule_line.ship_to_location_id
1891 , l_otm_schedule_line.ship_to_location_code
1892 , l_otm_schedule_line.ship_to_contact_name
1893 , l_otm_schedule_line.ship_to_contact_email
1894 , l_otm_schedule_line.ship_to_contact_phone
1895 , l_otm_schedule_line.ship_to_contact_fax
1896 ;
1897 EXIT WHEN get_shipment_line_info%NOTFOUND;
1898
1899 d_progress := '120';
1900
1901 IF (g_debug_stmt) THEN
1902 PO_DEBUG.debug_stmt(d_module, d_progress, 'Got schedule line. line_location_id=' || TO_CHAR(l_otm_schedule_line.line_location_id));
1903 END IF;
1904
1905 -- populate shipment address info
1906 d_progress := '150';
1907
1908 populate_address_info(
1909 x_otm_doc => x_otm_doc
1910 , x_otm_sched_line => l_otm_schedule_line );
1911
1912 d_progress := '160';
1913
1914 x_otm_doc.schedule_lines.extend;
1915 x_otm_doc.schedule_lines(l_count) := l_otm_schedule_line;
1916
1917 d_progress := '190';
1918
1919 l_count := l_count + 1;
1920 END LOOP;
1921
1922 d_progress := '060';
1923
1924 CLOSE get_shipment_line_info;
1925
1926 d_progress := '070';
1927
1928 IF (g_debug_stmt) THEN
1929 PO_DEBUG.debug_end(d_module);
1930 END IF;
1931
1932 EXCEPTION
1933 WHEN NO_DATA_FOUND THEN
1934 IF (g_debug_stmt) THEN
1935 PO_DEBUG.debug_unexp(d_module, d_progress, 'No document header found. This probably means that archive-on-approval is not set');
1936 END IF;
1937 RAISE;
1938 WHEN OTHERS THEN
1939 IF (g_debug_stmt) THEN
1940 PO_DEBUG.debug_unexp(d_module, d_progress, 'Exception retrieving document');
1941 END IF;
1942 RAISE;
1943
1944 END get_approved_release;
1945
1946 PROCEDURE get_canceled_release (
1947 p_doc_id IN NUMBER
1948 , p_doc_revision IN NUMBER
1949 , p_blanket_revision IN NUMBER
1950 , p_line_loc_id IN NUMBER
1951 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
1952 )
1953 IS
1954
1955 d_progress VARCHAR2(3);
1956 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'GET_CANCELED_RELEASE';
1957
1958 BEGIN
1959
1960 d_progress := '000';
1961
1962 IF (g_debug_stmt) THEN
1963 PO_DEBUG.debug_begin(d_module);
1964 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_id', p_doc_id);
1965 PO_DEBUG.debug_var(d_module, d_progress, 'p_line_loc_id', p_line_loc_id);
1966 PO_DEBUG.debug_var(d_module, d_progress, 'p_blanket_revision', p_blanket_revision);
1967 END IF;
1968
1969 d_progress := '020';
1970
1971 get_release_for_status_change (
1972 p_doc_id => p_doc_id
1973 , p_doc_revision => p_doc_revision
1974 , p_blanket_revision => p_blanket_revision
1975 , p_line_loc_id => p_line_loc_id
1976 , x_otm_doc => x_otm_doc );
1977
1978 d_progress := '030';
1979
1980 IF (g_debug_stmt) THEN
1981 PO_DEBUG.debug_end(d_module);
1982 END IF;
1983
1984 EXCEPTION
1985 WHEN OTHERS THEN
1986 IF (g_debug_stmt) THEN
1987 PO_DEBUG.debug_unexp(d_module, d_progress, 'Exception retrieving document');
1988 END IF;
1989 RAISE;
1990
1991 END get_canceled_release;
1992
1993 PROCEDURE get_closed_release (
1994 p_doc_id IN NUMBER
1995 , p_doc_revision IN NUMBER
1996 , p_blanket_revision IN NUMBER
1997 , p_line_loc_id IN NUMBER
1998 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
1999 )
2000 IS
2001
2002 d_progress VARCHAR2(3);
2003 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'GET_CLOSED_RELEASE';
2004
2005 BEGIN
2006
2007 d_progress := '000';
2008
2009 IF (g_debug_stmt) THEN
2010 PO_DEBUG.debug_begin(d_module);
2011 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_id', p_doc_id);
2012 PO_DEBUG.debug_var(d_module, d_progress, 'p_line_loc_id', p_line_loc_id);
2013 PO_DEBUG.debug_var(d_module, d_progress, 'p_blanket_revision', p_blanket_revision);
2014 END IF;
2015
2016 d_progress := '020';
2017
2018 get_release_for_status_change (
2019 p_doc_id => p_doc_id
2020 , p_doc_revision => p_doc_revision
2021 , p_blanket_revision => p_blanket_revision
2022 , p_line_loc_id => p_line_loc_id
2023 , x_otm_doc => x_otm_doc );
2024
2025 d_progress := '030';
2026
2027 IF (g_debug_stmt) THEN
2028 PO_DEBUG.debug_end(d_module);
2029 END IF;
2030
2031 EXCEPTION
2032 WHEN OTHERS THEN
2033 IF (g_debug_stmt) THEN
2034 PO_DEBUG.debug_unexp(d_module, d_progress, 'Exception retrieving document');
2035 END IF;
2036 RAISE;
2037
2038 END get_closed_release;
2039
2040 PROCEDURE get_opened_release (
2041 p_doc_id IN NUMBER
2042 , p_doc_revision IN NUMBER
2043 , p_blanket_revision IN NUMBER
2044 , p_line_loc_id IN NUMBER
2045 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
2046 )
2047 IS
2048
2049 d_progress VARCHAR2(3);
2050 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'GET_OPENED_RELEASE';
2051
2052 BEGIN
2053
2054 d_progress := '000';
2055
2056 IF (g_debug_stmt) THEN
2057 PO_DEBUG.debug_begin(d_module);
2058 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_id', p_doc_id);
2059 PO_DEBUG.debug_var(d_module, d_progress, 'p_line_loc_id', p_line_loc_id);
2060 PO_DEBUG.debug_var(d_module, d_progress, 'p_blanket_revision', p_blanket_revision);
2061 END IF;
2062
2063 d_progress := '020';
2064
2065 get_release_for_status_change (
2066 p_doc_id => p_doc_id
2067 , p_doc_revision => p_doc_revision
2068 , p_blanket_revision => p_blanket_revision
2069 , p_line_loc_id => p_line_loc_id
2070 , x_otm_doc => x_otm_doc );
2071
2072 d_progress := '030';
2073
2074 IF (g_debug_stmt) THEN
2075 PO_DEBUG.debug_end(d_module);
2076 END IF;
2077
2078 EXCEPTION
2079 WHEN OTHERS THEN
2080 IF (g_debug_stmt) THEN
2081 PO_DEBUG.debug_unexp(d_module, d_progress, 'Exception retrieving document');
2082 END IF;
2083 RAISE;
2084
2085 END get_opened_release;
2086
2087
2088 PROCEDURE get_release_for_status_change (
2089 p_doc_id IN NUMBER
2090 , p_doc_revision IN NUMBER
2091 , p_blanket_revision IN NUMBER
2092 , p_line_loc_id IN NUMBER
2093 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
2094 )
2095 IS
2096
2097 CURSOR get_shipment_line_info (
2098 p_doc_id NUMBER
2099 , p_doc_revision NUMBER
2100 , p_line_loc_id NUMBER
2101 , p_blanket_revision NUMBER
2102 )
2103 IS
2104 SELECT pola.po_line_id
2105 , plla.line_location_id
2106 , pola.line_num
2107 , plla.shipment_num
2108 FROM po_headers_archive_all poha
2109 , po_releases_archive_all pora
2110 , po_lines_archive_all pola
2111 , po_line_locations_archive_all plla
2112 , mtl_system_items msi
2113 , financials_system_params_all fsp
2114 WHERE pora.po_release_id = p_doc_id
2115 AND pora.revision_num = p_doc_revision
2116 AND poha.po_header_id = pora.po_header_id
2117 AND poha.revision_num = p_blanket_revision
2118 AND pola.po_header_id = poha.po_header_id
2119 AND pola.revision_num =
2120 ( SELECT MAX(pola2.revision_num)
2121 FROM po_lines_archive_all pola2
2122 WHERE pola2.po_line_id = pola.po_line_id
2123 AND pola2.po_header_id = poha.po_header_id
2124 AND pola2.revision_num <= poha.revision_num )
2125 AND plla.po_line_id = pola.po_line_id
2126 AND plla.po_release_id = pora.po_release_id
2127 AND plla.revision_num =
2128 ( SELECT MAX(plla2.revision_num)
2129 FROM po_line_locations_archive_all plla2
2130 WHERE plla2.line_location_id = plla.line_location_id
2131 AND plla2.po_line_id = pola.po_line_id
2132 AND plla2.revision_num <= pora.revision_num )
2133 AND pola.order_type_lookup_code = 'QUANTITY'
2134 AND fsp.org_id = pola.org_id
2135 AND msi.inventory_item_id (+) = pola.item_id
2136 AND NVL(msi.organization_id,
2137 fsp.inventory_organization_id) = fsp.inventory_organization_id
2138 AND NVL(msi.outside_operation_flag, 'N') = 'N'
2139 AND NVL(p_line_loc_id, plla.line_location_id) = plla.line_location_id;
2140
2141 l_count NUMBER;
2142 l_otm_schedule_line PO_OTM_SCHEDULE_LINE_TYPE;
2143
2144 d_progress VARCHAR2(3);
2145 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'GET_RELEASE_FOR_STATUS_CHANGE';
2146
2147 BEGIN
2148
2149 d_progress := '000';
2150
2151 IF (g_debug_stmt) THEN
2152 PO_DEBUG.debug_begin(d_module);
2153 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_id', p_doc_id);
2154 PO_DEBUG.debug_var(d_module, d_progress, 'p_line_loc_id', p_line_loc_id);
2155 PO_DEBUG.debug_var(d_module, d_progress, 'p_blanket_revision', p_blanket_revision);
2156 END IF;
2157
2158 d_progress := '020';
2159
2160 -- get header info
2161 SELECT poha.po_header_id
2162 , pora.po_release_id
2163 , poha.segment1
2164 , pora.release_num
2165 , hou.name
2166 INTO x_otm_doc.po_header_id
2167 , x_otm_doc.po_release_id
2168 , x_otm_doc.po_number
2169 , x_otm_doc.release_number
2170 , x_otm_doc.org_name
2171 FROM po_headers_archive_all poha
2172 , po_releases_archive_all pora
2173 , hr_all_organization_units hou
2174 WHERE pora.po_release_id = p_doc_id
2175 AND pora.revision_num = p_doc_revision
2176 AND poha.po_header_id = pora.po_header_id
2177 AND poha.revision_num = p_blanket_revision
2178 AND poha.org_id = hou.organization_id
2179 AND NVL(poha.consigned_consumption_flag, 'N') = 'N'
2180 ;
2181
2182 d_progress := '030';
2183
2184 -- initialize table for shedule line info
2185 x_otm_doc.schedule_lines := PO_OTM_SCHEDULE_LINE_TBL();
2186
2187 -- get shipment info if action was performed at shipment level
2188 IF (p_line_loc_id IS NOT NULL) THEN
2189 -- open cursor to pull shipment data
2190 OPEN get_shipment_line_info (
2191 p_doc_id => p_doc_id
2192 , p_doc_revision => p_doc_revision
2193 , p_line_loc_id => p_line_loc_id
2194 , p_blanket_revision => p_blanket_revision);
2195
2196 d_progress := '040';
2197
2198 l_count := 1;
2199
2200 -- pull all shipments with pertinent line info
2201 LOOP
2202 d_progress := '100';
2203
2204 l_otm_schedule_line := PO_OTM_SCHEDULE_LINE_TYPE.new_instance();
2205
2206 d_progress := '110';
2207
2208 FETCH get_shipment_line_info
2209 INTO l_otm_schedule_line.po_line_id
2210 , l_otm_schedule_line.line_location_id
2211 , l_otm_schedule_line.line_num
2212 , l_otm_schedule_line.shipment_num
2213 ;
2214 EXIT WHEN get_shipment_line_info%NOTFOUND;
2215
2216 d_progress := '120';
2217
2218 IF (g_debug_stmt) THEN
2219 PO_DEBUG.debug_stmt(d_module, d_progress, 'Got schedule line. line_location_id=' || TO_CHAR(l_otm_schedule_line.line_location_id));
2220 END IF;
2221
2222 x_otm_doc.schedule_lines.extend;
2223 x_otm_doc.schedule_lines(l_count) := l_otm_schedule_line;
2224
2225 d_progress := '130';
2226
2227 l_count := l_count + 1;
2228 END LOOP;
2229
2230 d_progress := '060';
2231
2232 CLOSE get_shipment_line_info;
2233
2234 END IF; -- IF (p_line_loc_id IS NOT NULL) THEN
2235
2236 d_progress := '070';
2237
2238 IF (g_debug_stmt) THEN
2239 PO_DEBUG.debug_end(d_module);
2240 END IF;
2241
2242 EXCEPTION
2243 WHEN OTHERS THEN
2244 IF (g_debug_stmt) THEN
2245 PO_DEBUG.debug_unexp(d_module, d_progress, 'Exception retrieving document');
2246 END IF;
2247 RAISE;
2248
2249 END get_release_for_status_change;
2250
2251
2252 PROCEDURE get_held_release (
2253 p_doc_id IN NUMBER
2254 , p_doc_revision IN NUMBER
2255 , p_blanket_revision IN NUMBER
2256 , x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
2257 )
2258 IS
2259
2260 d_progress VARCHAR2(3);
2261 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'GET_HELD_RELEASE';
2262
2263 BEGIN
2264
2265 d_progress := '000';
2266
2267 IF (g_debug_stmt) THEN
2268 PO_DEBUG.debug_begin(d_module);
2269 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_id', p_doc_id);
2270 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_revision', p_doc_revision);
2271 PO_DEBUG.debug_var(d_module, d_progress, 'p_blanket_revision', p_blanket_revision);
2272 END IF;
2273
2274 d_progress := '020';
2275
2276 -- get header info
2277 SELECT poha.po_header_id
2278 , por.po_release_id
2279 , poha.segment1
2280 , por.release_num
2281 , hou.name
2282 INTO x_otm_doc.po_header_id
2283 , x_otm_doc.po_release_id
2284 , x_otm_doc.po_number
2285 , x_otm_doc.release_number
2286 , x_otm_doc.org_name
2287 FROM po_headers_archive_all poha
2288 , po_releases_all por
2289 , hr_all_organization_units hou
2290 WHERE por.po_release_id = p_doc_id
2291 AND poha.po_header_id = por.po_header_id
2292 AND poha.revision_num = p_blanket_revision
2293 AND poha.org_id = hou.organization_id
2294 AND NVL(poha.consigned_consumption_flag, 'N') = 'N'
2295 ;
2296
2297 d_progress := '030';
2298
2299 IF (g_debug_stmt) THEN
2300 PO_DEBUG.debug_end(d_module);
2301 END IF;
2302
2303 EXCEPTION
2304 WHEN NO_DATA_FOUND THEN
2305 IF (g_debug_stmt) THEN
2306 PO_DEBUG.debug_unexp(d_module, d_progress, 'No data found. This probably means that archive-on-approve is off.');
2307 END IF;
2308 RAISE;
2309
2310 WHEN OTHERS THEN
2311 IF (g_debug_stmt) THEN
2312 PO_DEBUG.debug_unexp(d_module, d_progress, 'Exception retrieving document');
2313 END IF;
2314 RAISE;
2315
2316 END get_held_release;
2317
2318 PROCEDURE populate_address_info (
2319 x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
2320 )
2321 IS
2322
2323 l_territory_short_name FND_TERRITORIES_TL.territory_short_name%TYPE;
2324
2325 d_progress VARCHAR2(3);
2326 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'POPULATE_ADDRESS';
2327
2328 BEGIN
2329
2330 d_progress := '000';
2331
2332 IF (g_debug_stmt) THEN
2333 PO_DEBUG.debug_begin(d_module);
2334 PO_DEBUG.debug_var(d_module, d_progress, 'x_otm_doc.po_header_id', x_otm_doc.po_header_id);
2335 PO_DEBUG.debug_var(d_module, d_progress, 'x_otm_doc.org_location_id', x_otm_doc.org_location_id);
2336 PO_DEBUG.debug_var(d_module, d_progress, 'x_otm_doc.bill_to_location_id', x_otm_doc.bill_to_location_id);
2337 END IF;
2338
2339 d_progress := '100';
2340
2341 -- Get org location info
2342 PO_HR_LOCATION.get_address (
2343 p_location_id => x_otm_doc.org_location_id
2344 , x_address_line_1 => x_otm_doc.org_loc_addr_line_1
2345 , x_address_line_2 => x_otm_doc.org_loc_addr_line_2
2346 , x_address_line_3 => x_otm_doc.org_loc_addr_line_3
2347 , x_town_or_city => x_otm_doc.org_loc_addr_city
2348 , x_state_or_province => x_otm_doc.org_loc_addr_state_province
2349 , x_postal_code => x_otm_doc.org_loc_addr_zip
2350 , x_territory_short_name => l_territory_short_name
2351 , x_iso_territory_code => x_otm_doc.org_loc_addr_country );
2352
2353 d_progress := '110';
2354
2355 -- If Bill-To is same as org loc, just copy over, otherwise, call API again
2356 IF (x_otm_doc.bill_to_location_id = x_otm_doc.org_location_id) THEN
2357 d_progress := '120';
2358
2359 x_otm_doc.bill_to_addr_line_1 := x_otm_doc.org_loc_addr_line_1;
2360 x_otm_doc.bill_to_addr_line_2 := x_otm_doc.org_loc_addr_line_2;
2361 x_otm_doc.bill_to_addr_line_3 := x_otm_doc.org_loc_addr_line_3;
2362 x_otm_doc.bill_to_addr_city := x_otm_doc.org_loc_addr_city;
2363 x_otm_doc.bill_to_addr_state_province := x_otm_doc.org_loc_addr_state_province;
2364 x_otm_doc.bill_to_addr_zip := x_otm_doc.org_loc_addr_zip;
2365 x_otm_doc.bill_to_addr_country := x_otm_doc.org_loc_addr_country;
2366
2367 d_progress := '130';
2368
2369 ELSE
2370 d_progress := '150';
2371
2372 PO_HR_LOCATION.get_address (
2373 p_location_id => x_otm_doc.bill_to_location_id
2374 , x_address_line_1 => x_otm_doc.bill_to_addr_line_1
2375 , x_address_line_2 => x_otm_doc.bill_to_addr_line_2
2376 , x_address_line_3 => x_otm_doc.bill_to_addr_line_3
2377 , x_town_or_city => x_otm_doc.bill_to_addr_city
2378 , x_state_or_province => x_otm_doc.bill_to_addr_state_province
2379 , x_postal_code => x_otm_doc.bill_to_addr_zip
2380 , x_territory_short_name => l_territory_short_name
2381 , x_iso_territory_code => x_otm_doc.bill_to_addr_country );
2382
2383 d_progress := '160';
2384
2385 END IF;
2386
2387 d_progress := '200';
2388
2389 IF (g_debug_stmt) THEN
2390 PO_DEBUG.debug_var(d_module, d_progress, 'x_otm_doc.org_loc_addr_line_1', x_otm_doc.org_loc_addr_line_1);
2391 PO_DEBUG.debug_var(d_module, d_progress, 'x_otm_doc.bill_to_addr_line_1', x_otm_doc.bill_to_addr_line_1);
2392 PO_DEBUG.debug_end(d_module);
2393 END IF;
2394
2395 EXCEPTION
2396 WHEN OTHERS THEN
2397 IF (g_debug_stmt) THEN
2398 PO_DEBUG.debug_unexp(d_module, d_progress, 'Unexpected exception');
2399 END IF;
2400
2401 END populate_address_info;
2402
2403 PROCEDURE populate_address_info (
2404 x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
2405 , x_otm_sched_line IN OUT NOCOPY PO_OTM_SCHEDULE_LINE_TYPE
2406 )
2407 IS
2408
2409 l_territory_short_name FND_TERRITORIES_TL.territory_short_name%TYPE;
2410
2411 d_progress VARCHAR2(3);
2412 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'POPULATE_ADDRESS';
2413
2414 BEGIN
2415
2416 d_progress := '000';
2417
2418 IF (g_debug_stmt) THEN
2419 PO_DEBUG.debug_begin(d_module);
2420 PO_DEBUG.debug_var(d_module, d_progress, 'x_otm_doc.po_header_id', x_otm_doc.po_header_id);
2421 PO_DEBUG.debug_var(d_module, d_progress, 'x_otm_sched_line.line_location_id', x_otm_sched_line.line_location_id);
2422 PO_DEBUG.debug_var(d_module, d_progress, 'x_otm_sched_line.ship_to_location_id', x_otm_sched_line.ship_to_location_id);
2423 END IF;
2424
2425 d_progress := '100';
2426
2427 -- If Ship-To location is the same as the Bill-To or Sold-To, copy from header,
2428 -- otherwise call helper routine to get address info
2429 IF (x_otm_sched_line.ship_to_location_id = x_otm_doc.org_location_id) THEN
2430 d_progress := '110';
2431
2432 x_otm_sched_line.ship_to_loc_addr_line_1 := x_otm_doc.org_loc_addr_line_1;
2433 x_otm_sched_line.ship_to_loc_addr_line_2 := x_otm_doc.org_loc_addr_line_2;
2434 x_otm_sched_line.ship_to_loc_addr_line_3 := x_otm_doc.org_loc_addr_line_3;
2435 x_otm_sched_line.ship_to_loc_city := x_otm_doc.org_loc_addr_city;
2436 x_otm_sched_line.ship_to_loc_state_province := x_otm_doc.org_loc_addr_state_province;
2437 x_otm_sched_line.ship_to_loc_zip := x_otm_doc.org_loc_addr_zip;
2438 x_otm_sched_line.ship_to_loc_country := x_otm_doc.org_loc_addr_country;
2439
2440 d_progress := '120';
2441
2442 ELSIF (x_otm_sched_line.ship_to_location_id = x_otm_doc.bill_to_location_id) THEN
2443 d_progress := '130';
2444
2445 x_otm_sched_line.ship_to_loc_addr_line_1 := x_otm_doc.bill_to_addr_line_1;
2446 x_otm_sched_line.ship_to_loc_addr_line_2 := x_otm_doc.bill_to_addr_line_2;
2447 x_otm_sched_line.ship_to_loc_addr_line_3 := x_otm_doc.bill_to_addr_line_3;
2448 x_otm_sched_line.ship_to_loc_city := x_otm_doc.bill_to_addr_city;
2449 x_otm_sched_line.ship_to_loc_state_province := x_otm_doc.bill_to_addr_state_province;
2450 x_otm_sched_line.ship_to_loc_zip := x_otm_doc.bill_to_addr_zip;
2451 x_otm_sched_line.ship_to_loc_country := x_otm_doc.bill_to_addr_country;
2452
2453 d_progress := '140';
2454
2455 ELSE
2456 d_progress := '150';
2457
2458 PO_HR_LOCATION.get_address (
2459 p_location_id => x_otm_sched_line.ship_to_location_id
2460 , x_address_line_1 => x_otm_sched_line.ship_to_loc_addr_line_1
2461 , x_address_line_2 => x_otm_sched_line.ship_to_loc_addr_line_2
2462 , x_address_line_3 => x_otm_sched_line.ship_to_loc_addr_line_3
2463 , x_town_or_city => x_otm_sched_line.ship_to_loc_city
2464 , x_state_or_province => x_otm_sched_line.ship_to_loc_state_province
2465 , x_postal_code => x_otm_sched_line.ship_to_loc_zip
2466 , x_territory_short_name => l_territory_short_name
2467 , x_iso_territory_code => x_otm_sched_line.ship_to_loc_country );
2468
2469 d_progress := '160';
2470
2471 END IF;
2472
2473 d_progress := '200';
2474
2475 -- If this is a drop-ship location, need to populate customer info
2476 IF (x_otm_sched_line.drop_ship_flag = 'Y') THEN
2477 d_progress := '210';
2478
2479 populate_drop_ship_info (
2480 x_otm_sched_line => x_otm_sched_line );
2481
2482 d_progress := '220';
2483 END IF;
2484
2485 IF (g_debug_stmt) THEN
2486 PO_DEBUG.debug_var(d_module, d_progress, 'x_otm_sched_line.ship_to_loc_addr_line_1',
2487 x_otm_sched_line.ship_to_loc_addr_line_1);
2488 PO_DEBUG.debug_end(d_module);
2489 END IF;
2490
2491 EXCEPTION
2492 WHEN OTHERS THEN
2493 IF (g_debug_stmt) THEN
2494 PO_DEBUG.debug_unexp(d_module, d_progress, 'Unexpected exception');
2495 END IF;
2496
2497 END populate_address_info;
2498
2499 PROCEDURE populate_drop_ship_info (
2500 x_otm_sched_line IN OUT NOCOPY PO_OTM_SCHEDULE_LINE_TYPE
2501 )
2502 IS
2503
2504 l_order_line_info_rec OE_DROP_SHIP_GRP.order_line_info_rec_type;
2505 l_return_status VARCHAR2(1);
2506 l_msg_count NUMBER;
2507 l_msg_data VARCHAR2(4000);
2508
2509 d_progress VARCHAR2(3);
2510 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'POPULATE_DROP_SHIP_INFO';
2511 d_log_msg VARCHAR2(200) := 'Unknown error';
2512
2513 BEGIN
2514
2515 d_progress := '000';
2516
2517 IF (g_debug_stmt) THEN
2518 PO_DEBUG.debug_begin(d_module);
2519 PO_DEBUG.debug_var(d_module, d_progress, 'x_otm_sched_line.line_location_id', x_otm_sched_line.line_location_id);
2520 END IF;
2521
2522 d_progress := '010';
2523
2524 -- call OM API to get customer and order info.
2525 OE_DROP_SHIP_GRP.get_order_line_info (
2526 p_api_version => 1.0
2527 , p_po_header_id => NULL
2528 , p_po_release_id => NULL
2529 , p_po_line_id => NULL
2530 , p_po_line_location_id => x_otm_sched_line.line_location_id
2531 , p_mode => 2 -- get all attributes
2532 , x_order_line_info_rec => l_order_line_info_rec
2533 , x_msg_data => l_msg_data
2534 , x_msg_count => l_msg_count
2535 , x_return_status => l_return_status);
2536
2537 d_progress := '100';
2538
2539 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
2540 d_progress := '110';
2541 d_log_msg := 'Call to OE_DROP_SHIP_GRP.get_order_line_info failed.';
2542 RAISE g_OTM_INTEGRATION_EXC;
2543 END IF;
2544
2545 d_progress := '120';
2546
2547 -- default location code from address
2548 x_otm_sched_line.ship_to_location_code := SUBSTRB(RTRIM(x_otm_sched_line.ship_to_loc_addr_line_1) || '-' || RTRIM(x_otm_sched_line.ship_to_loc_city), 1, 20);
2549
2550 -- copy contact info
2551 x_otm_sched_line.ship_to_contact_name := l_order_line_info_rec.deliver_to_contact_name;
2552 x_otm_sched_line.ship_to_contact_email := l_order_line_info_rec.deliver_to_contact_email;
2553 x_otm_sched_line.ship_to_contact_phone := l_order_line_info_rec.deliver_to_contact_phone;
2554 x_otm_sched_line.ship_to_contact_fax := l_order_line_info_rec.deliver_to_contact_fax;
2555
2556 d_progress := '130';
2557
2558 IF (g_debug_stmt) THEN
2559 PO_DEBUG.debug_var(d_module, d_progress, 'x_otm_sched_line.ship_to_location_code', x_otm_sched_line.ship_to_location_code);
2560 PO_DEBUG.debug_var(d_module, d_progress, 'x_otm_sched_line.ship_to_contact_name', x_otm_sched_line.ship_to_contact_name);
2561 PO_DEBUG.debug_var(d_module, d_progress, 'x_otm_sched_line.ship_to_contact_email', x_otm_sched_line.ship_to_contact_email);
2562 PO_DEBUG.debug_var(d_module, d_progress, 'x_otm_sched_line.ship_to_contact_phone', x_otm_sched_line.ship_to_contact_phone);
2563 PO_DEBUG.debug_var(d_module, d_progress, 'x_otm_sched_line.ship_to_contact_fax', x_otm_sched_line.ship_to_contact_fax);
2564 PO_DEBUG.debug_end(d_module);
2565 END IF;
2566
2567
2568 d_progress := '140';
2569
2570 EXCEPTION
2571 WHEN OTHERS THEN
2572 IF (g_debug_stmt) THEN
2573 PO_DEBUG.debug_unexp(d_module, d_progress, d_log_msg);
2574 END IF;
2575 RAISE;
2576
2577 END populate_drop_ship_info;
2578
2579 -- 7449918 OTM Recovery START
2580 PROCEDURE recover_failed_docs
2581 ( errbuf OUT NOCOPY VARCHAR2,
2582 retcode OUT NOCOPY VARCHAR2
2583 ) IS
2584
2585 CURSOR c_get_failed_orders IS
2586 SELECT po_header_id
2587 FROM po_headers_all POH
2588 WHERE POH.otm_recovery_flag = 'Y';
2589
2590 CURSOR c_get_failed_releases IS
2591 SELECT po_release_id
2592 FROM po_releases_all POR
2593 WHERE POR.otm_recovery_flag = 'Y';
2594
2595 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'recover_failed_orders';
2596 d_progress VARCHAR2(3);
2597
2598 l_failed_doc_tbl PO_TBL_NUMBER;
2599
2600 BEGIN
2601
2602 d_progress := '000';
2603
2604 IF (g_debug_stmt) THEN
2605 PO_DEBUG.debug_begin(d_module);
2606 END IF;
2607
2608 -- Process POs
2609
2610 OPEN c_get_failed_orders;
2611
2612 FETCH c_get_failed_orders
2613 BULK COLLECT
2614 INTO l_failed_doc_tbl;
2615
2616 CLOSE c_get_failed_orders;
2617
2618 d_progress := '010';
2619
2620 IF (g_debug_stmt) THEN
2621 PO_DEBUG.debug_var(d_module, d_progress, 'failed order count', l_failed_doc_tbl.COUNT);
2622 END IF;
2623
2624
2625 FOR i IN 1..l_failed_doc_tbl.COUNT LOOP
2626
2627 d_progress := '015';
2628
2629 IF (g_debug_stmt) THEN
2630 PO_DEBUG.debug_stmt(d_module, d_progress, 'processing headerid = ' || l_failed_doc_tbl(i));
2631 END IF;
2632
2633 -- Reset the recovery status
2634 update_order_otm_status
2635 ( p_doc_id => l_failed_doc_tbl(i),
2636 p_doc_type => 'PO',
2637 p_order_otm_status => NULL,
2638 p_otm_recovery_flag => 'R'
2639 );
2640
2641 d_progress := '020';
2642
2643 PO_OTM_INTEGRATION_PVT.handle_doc_update
2644 ( p_doc_type => 'PO',
2645 p_doc_id => l_failed_doc_tbl(i),
2646 p_action => 'APPROVE',
2647 p_line_id => null,
2648 p_line_loc_id => null
2649 );
2650
2651 d_progress := '040';
2652
2653 PO_OTM_INTEGRATION_PVT.handle_doc_update
2654 ( p_doc_type => 'PO',
2655 p_doc_id => l_failed_doc_tbl(i),
2656 p_action => 'RECOVER',
2657 p_line_id => null,
2658 p_line_loc_id => null
2659 );
2660
2661 END LOOP;
2662
2663 l_failed_doc_tbl.DELETE;
2664
2665 d_progress := '050';
2666
2667 -- Process failed releases
2668 OPEN c_get_failed_releases;
2669
2670 FETCH c_get_failed_releases
2671 BULK COLLECT
2672 INTO l_failed_doc_tbl;
2673
2674 CLOSE c_get_failed_releases;
2675
2676 IF (g_debug_stmt) THEN
2677 PO_DEBUG.debug_var(d_module, d_progress, 'failed release count', l_failed_doc_tbl.COUNT);
2678 END IF;
2679
2680 FOR i IN 1..l_failed_doc_tbl.COUNT LOOP
2681
2682 d_progress := '060';
2683
2684 IF (g_debug_stmt) THEN
2685 PO_DEBUG.debug_stmt(d_module, d_progress, 'processing releaseid = ' || l_failed_doc_tbl(i));
2686 END IF;
2687
2688 -- Reset recovery status
2689 update_order_otm_status
2690 ( p_doc_id => l_failed_doc_tbl(i),
2691 p_doc_type => 'RELEASE',
2692 p_order_otm_status => NULL,
2693 p_otm_recovery_flag => 'R'
2694 );
2695
2696 d_progress := '070';
2697
2698 PO_OTM_INTEGRATION_PVT.handle_doc_update
2699 ( p_doc_type => 'RELEASE',
2700 p_doc_id => l_failed_doc_tbl(i),
2701 p_action => 'APPROVE',
2702 p_line_id => null,
2703 p_line_loc_id => null
2704 );
2705
2706 d_progress := '080';
2707
2708 PO_OTM_INTEGRATION_PVT.handle_doc_update
2709 ( p_doc_type => 'RELEASE',
2710 p_doc_id => l_failed_doc_tbl(i),
2711 p_action => 'RECOVER',
2712 p_line_id => null,
2713 p_line_loc_id => null
2714 );
2715 END LOOP;
2716
2717 d_progress := '090';
2718
2719 COMMIT;
2720
2721 IF (g_debug_stmt) THEN
2722 PO_DEBUG.debug_end(d_module);
2723 END IF;
2724
2725 EXCEPTION
2726 WHEN OTHERS THEN
2727 IF (g_debug_stmt) THEN
2728 PO_DEBUG.debug_unexp(d_module, d_progress, 'Unexpected error: ' || SQLERRM);
2729 END IF;
2730 RAISE;
2731
2732 END recover_failed_docs;
2733
2734 PROCEDURE get_recovering_order
2735 ( p_doc_id IN NUMBER,
2736 x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
2737 ) IS
2738
2739 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'get_recovering_order';
2740 d_progress VARCHAR2(3);
2741
2742
2743 CURSOR get_shipment_lines
2744 ( p_doc_id IN NUMBER,
2745 p_inv_org_id IN NUMBER
2746 ) IS
2747 SELECT POL.po_line_id,
2748 PLL.line_location_id,
2749 POL.line_num,
2750 PLL.shipment_num,
2751 NVL(PLL.cancel_flag, 'N'),
2752 NVL(PLL.closed_code, 'OPEN')
2753 FROM po_lines_all POL,
2754 po_line_locations_all PLL,
2755 mtl_system_items MSI
2756 WHERE POL.po_header_id = p_doc_id
2757 AND PLL.po_line_id = POL.po_line_id
2758 AND POL.order_type_lookup_code = 'QUANTITY'
2759 AND MSI.inventory_item_id(+) = POL.item_id
2760 AND MSI.organization_id(+) = p_inv_org_id
2761 AND NVL(MSI.outside_operation_flag, 'N') = 'N';
2762
2763 l_inv_org_id NUMBER;
2764 l_header_cancel_flag PO_HEADERS_ALL.cancel_flag%TYPE;
2765 l_header_closed_code PO_HEADERS_ALL.closed_code%TYPE;
2766 l_header_user_hold_flag PO_HEADERS_ALL.user_hold_flag%TYPE;
2767 l_shipment_cancel_flag PO_LINE_LOCATIONS_ALL.cancel_flag%TYPE;
2768 l_shipment_closed_code PO_LINE_LOCATIONS_ALL.closed_code%TYPE;
2769
2770 l_otm_schedule_line PO_OTM_SCHEDULE_LINE_TYPE;
2771
2772 l_count NUMBER := 0;
2773 BEGIN
2774
2775 d_progress := '000';
2776
2777 IF (g_debug_stmt) THEN
2778 PO_DEBUG.debug_begin(d_module);
2779 END IF;
2780
2781 SELECT POH.po_header_id,
2782 POH.segment1,
2783 HOU.name,
2784 NVL(POH.cancel_flag, 'N'),
2785 NVL(POH.closed_code, 'OPEN'),
2786 NVL(POH.user_hold_flag, 'N'),
2787 FSP.inventory_organization_id
2788 INTO x_otm_doc.po_header_id,
2789 x_otm_doc.po_number,
2790 x_otm_doc.org_name,
2791 l_header_cancel_flag,
2792 l_header_closed_code,
2793 l_header_user_hold_flag,
2794 l_inv_org_id
2795 FROM po_headers_all POH,
2796 hr_all_organization_units HOU,
2797 financials_system_params_all FSP
2798 WHERE POH.po_header_id = p_doc_id
2799 AND POH.org_id = HOU.organization_id
2800 AND POH.org_id = FSP.org_id
2801 AND NVL(POH.consigned_consumption_flag, 'N') = 'N';
2802
2803 d_progress := '010';
2804
2805 IF (l_header_cancel_flag = 'Y') THEN
2806 x_otm_doc.recovery_action := 'CANCEL';
2807 ELSIF (l_header_closed_code IN ('CLOSED', 'CLOSED FOR RECEIVING',
2808 'FINALLY CLOSED')) THEN
2809 x_otm_doc.recovery_action := 'CLOSE';
2810 ELSIF (l_header_user_hold_flag = 'Y') THEN
2811 x_otm_doc.recovery_action := 'HOLD';
2812 ELSIF ( l_header_closed_code = 'OPEN' AND
2813 l_header_user_hold_flag = 'N') THEN
2814 x_otm_doc.recovery_action := 'OPEN';
2815 END IF;
2816
2817 IF (g_debug_stmt) THEN
2818 PO_DEBUG.debug_var(d_module, d_progress, 'header recovery action', x_otm_doc.recovery_action);
2819 END IF;
2820
2821
2822 d_progress := '020';
2823
2824 OPEN get_shipment_lines (p_doc_id => p_doc_id,
2825 p_inv_org_id => l_inv_org_id);
2826
2827 LOOP
2828 l_count := l_count + 1;
2829
2830 l_otm_schedule_line := PO_OTM_SCHEDULE_LINE_TYPE.new_instance();
2831
2832 d_progress := '030';
2833
2834 FETCH get_shipment_lines
2835 INTO l_otm_schedule_line.po_line_id,
2836 l_otm_schedule_line.line_location_id,
2837 l_otm_schedule_line.line_num,
2838 l_otm_schedule_line.shipment_num,
2839 l_shipment_cancel_flag,
2840 l_shipment_closed_code;
2841
2842 EXIT WHEN get_shipment_lines%NOTFOUND;
2843
2844 IF (l_shipment_cancel_flag = 'Y') THEN
2845 l_otm_schedule_line.recovery_action := 'CANCEL';
2846 ELSIF (l_shipment_closed_code IN ('CLOSED', 'CLOSED FOR RECEIVING',
2847 'FINALLY CLOSED')) THEN
2848 l_otm_schedule_line.recovery_action := 'CLOSE';
2849 ELSIF (NVL(l_shipment_closed_code, 'OPEN') = 'OPEN') THEN
2850 l_otm_schedule_line.recovery_action := 'OPEN';
2851 END IF;
2852
2853 d_progress := '040';
2854
2855 IF (g_debug_stmt) THEN
2856 PO_DEBUG.debug_stmt(d_module, d_progress, 'Adding line information');
2857 PO_DEBUG.debug_var(d_module, d_progress, 'otm line', l_count);
2858 PO_DEBUG.debug_var(d_module, d_progress, 'line_recovery_action',l_otm_schedule_line.recovery_action);
2859 PO_DEBUG.debug_var(d_module, d_progress, 'po_line_id', l_otm_schedule_line.po_line_id);
2860 PO_DEBUG.debug_var(d_module, d_progress, 'line_location_id', l_otm_schedule_line.line_location_id);
2861 END IF;
2862
2863 d_progress := '050';
2864 x_otm_doc.schedule_lines.extend;
2865 x_otm_doc.schedule_lines(l_count) := l_otm_schedule_line;
2866
2867
2868 END LOOP;
2869
2870 CLOSE get_shipment_lines;
2871
2872
2873
2874 d_progress := '060';
2875
2876 IF (g_debug_stmt) THEN
2877 PO_DEBUG.debug_begin(d_module);
2878 END IF;
2879
2880 EXCEPTION
2881 WHEN OTHERS THEN
2882 IF (g_debug_stmt) THEN
2883 PO_DEBUG.debug_unexp(d_module, d_progress, 'Unexpected error: ' || SQLERRM);
2884 END IF;
2885 RAISE;
2886
2887 END get_recovering_order;
2888
2889 PROCEDURE get_recovering_release
2890 ( p_doc_id IN NUMBER,
2891 x_otm_doc IN OUT NOCOPY PO_OTM_ORDER_TYPE
2892 ) IS
2893
2894 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'get_recovering_release';
2895 d_progress VARCHAR2(3);
2896
2897
2898 CURSOR get_shipment_lines
2899 ( p_doc_id IN NUMBER,
2900 p_inv_org_id IN NUMBER
2901 ) IS
2902 SELECT POL.po_line_id,
2903 PLL.line_location_id,
2904 POL.line_num,
2905 PLL.shipment_num,
2906 NVL(PLL.cancel_flag, 'N'),
2907 NVL(PLL.closed_code, 'OPEN')
2908 FROM po_releases_all POR,
2909 po_lines_all POL,
2910 po_line_locations_all PLL,
2911 mtl_system_items MSI
2912 WHERE POR.po_release_id = p_doc_id
2913 AND POL.po_header_id = POR.po_header_id
2914 AND PLL.po_release_id = POR.po_release_id
2915 AND PLL.po_line_id = POL.po_line_id
2916 AND POL.order_type_lookup_code = 'QUANTITY'
2917 AND MSI.inventory_item_id(+) = POL.item_id
2918 AND MSI.organization_id(+) = p_inv_org_id
2919 AND NVL(MSI.outside_operation_flag, 'N') = 'N';
2920
2921 l_inv_org_id NUMBER;
2922 l_header_cancel_flag PO_HEADERS_ALL.cancel_flag%TYPE;
2923 l_header_closed_code PO_HEADERS_ALL.closed_code%TYPE;
2924 l_header_user_hold_flag PO_HEADERS_ALL.user_hold_flag%TYPE;
2925 l_shipment_cancel_flag PO_LINE_LOCATIONS_ALL.cancel_flag%TYPE;
2926 l_shipment_closed_code PO_LINE_LOCATIONS_ALL.closed_code%TYPE;
2927
2928 l_otm_schedule_line PO_OTM_SCHEDULE_LINE_TYPE;
2929
2930 l_count NUMBER := 0;
2931 BEGIN
2932
2933 d_progress := '000';
2934
2935 IF (g_debug_stmt) THEN
2936 PO_DEBUG.debug_begin(d_module);
2937 END IF;
2938
2939 SELECT POH.po_header_id,
2940 POR.po_release_id,
2941 POH.segment1,
2942 POR.release_num,
2943 HOU.name,
2944 NVL(POR.cancel_flag, 'N'),
2945 NVL(POR.closed_code, 'OPEN'),
2946 NVL(POR.hold_flag, 'N'),
2947 FSP.inventory_organization_id
2948 INTO x_otm_doc.po_header_id,
2949 x_otm_doc.po_release_id,
2950 x_otm_doc.po_number,
2951 x_otm_doc.release_number,
2952 x_otm_doc.org_name,
2953 l_header_cancel_flag,
2954 l_header_closed_code,
2955 l_header_user_hold_flag,
2956 l_inv_org_id
2957 FROM po_releases_all POR,
2958 po_headers_all POH,
2959 hr_all_organization_units HOU,
2960 financials_system_params_all FSP
2961 WHERE POR.po_release_id = p_doc_id
2962 AND POR.po_header_id = POH.po_header_id
2963 AND POR.org_id = HOU.organization_id
2964 AND POR.org_id = FSP.org_id
2965 AND NVL(POR.consigned_consumption_flag, 'N') = 'N';
2966
2967 d_progress := '010';
2968
2969 IF (l_header_cancel_flag = 'Y') THEN
2970 x_otm_doc.recovery_action := 'CANCEL';
2971 ELSIF (l_header_closed_code IN ('CLOSED', 'CLOSED FOR RECEIVING',
2972 'FINALLY CLOSED')) THEN
2973 x_otm_doc.recovery_action := 'CLOSE';
2974 ELSIF (l_header_user_hold_flag = 'Y') THEN
2975 x_otm_doc.recovery_action := 'HOLD';
2976 ELSIF ( l_header_closed_code = 'OPEN' AND
2977 l_header_user_hold_flag = 'N') THEN
2978 x_otm_doc.recovery_action := 'OPEN';
2979 END IF;
2980
2981 IF (g_debug_stmt) THEN
2982 PO_DEBUG.debug_var(d_module, d_progress, 'header recovery action', x_otm_doc.recovery_action);
2983 END IF;
2984
2985 d_progress := '020';
2986
2987 OPEN get_shipment_lines (p_doc_id => p_doc_id,
2988 p_inv_org_id => l_inv_org_id);
2989
2990 LOOP
2991 l_count := l_count + 1;
2992
2993 l_otm_schedule_line := PO_OTM_SCHEDULE_LINE_TYPE.new_instance();
2994
2995 d_progress := '030';
2996
2997 FETCH get_shipment_lines
2998 INTO l_otm_schedule_line.po_line_id,
2999 l_otm_schedule_line.line_location_id,
3000 l_otm_schedule_line.line_num,
3001 l_otm_schedule_line.shipment_num,
3002 l_shipment_cancel_flag,
3003 l_shipment_closed_code;
3004
3005 EXIT WHEN get_shipment_lines%NOTFOUND;
3006
3007
3008 IF (l_shipment_cancel_flag = 'Y') THEN
3009 l_otm_schedule_line.recovery_action := 'CANCEL';
3010 ELSIF (l_shipment_closed_code IN ('CLOSED', 'CLOSED FOR RECEIVING',
3011 'FINALLY CLOSED')) THEN
3012 l_otm_schedule_line.recovery_action := 'CLOSE';
3013 ELSIF (NVL(l_shipment_closed_code, 'OPEN') = 'OPEN') THEN
3014 l_otm_schedule_line.recovery_action := 'OPEN';
3015 END IF;
3016
3017 d_progress := '040';
3018
3019 IF (g_debug_stmt) THEN
3020 PO_DEBUG.debug_stmt(d_module, d_progress, 'Adding line information');
3021 PO_DEBUG.debug_var(d_module, d_progress, 'otm line', l_count);
3022 PO_DEBUG.debug_var(d_module, d_progress, 'line_recovery_action',l_otm_schedule_line.recovery_action);
3023 PO_DEBUG.debug_var(d_module, d_progress, 'po_line_id', l_otm_schedule_line.po_line_id);
3024 PO_DEBUG.debug_var(d_module, d_progress, 'line_location_id', l_otm_schedule_line.line_location_id);
3025 END IF;
3026
3027 d_progress := '050';
3028 x_otm_doc.schedule_lines.extend;
3029 x_otm_doc.schedule_lines(l_count) := l_otm_schedule_line;
3030
3031
3032 END LOOP;
3033
3034 CLOSE get_shipment_lines;
3035
3036 d_progress := '060';
3037
3038 IF (g_debug_stmt) THEN
3039 PO_DEBUG.debug_begin(d_module);
3040 END IF;
3041
3042 EXCEPTION
3043 WHEN OTHERS THEN
3044 IF (g_debug_stmt) THEN
3045 PO_DEBUG.debug_unexp(d_module, d_progress, 'Unexpected error: ' || SQLERRM);
3046 END IF;
3047 RAISE;
3048
3049 END get_recovering_release;
3050
3051
3052 PROCEDURE update_order_otm_status
3053 ( p_doc_id IN NUMBER,
3054 p_doc_type IN VARCHAR2,
3055 p_order_otm_status IN VARCHAR2,
3056 p_otm_recovery_flag IN VARCHAR2
3057 ) IS
3058
3059 d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'update_order_otm_status';
3060 d_progress VARCHAR2(3);
3061
3062 BEGIN
3063
3064 d_progress := '000';
3065
3066 IF (g_debug_stmt) THEN
3067 PO_DEBUG.debug_begin(d_module);
3068 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_id', p_doc_id);
3069 PO_DEBUG.debug_var(d_module, d_progress, 'p_doc_type', p_doc_type);
3070 PO_DEBUG.debug_var(d_module, d_progress, 'p_order_otm_status', p_order_otm_status);
3071 PO_DEBUG.debug_var(d_module, d_progress, 'p_otm_recovery_flag', p_otm_recovery_flag);
3072 END IF;
3073
3074 -- For p_otm_recovery_flag, we accept the following statuses
3075 -- 'Y': Set the flag to 'Y'
3076 -- 'R': Reset the flag to NULL
3077 -- 'N': Keep the flag as is
3078
3079 IF (p_doc_type = 'RELEASE') THEN
3080 UPDATE po_releases_all POR
3081 SET POR.otm_status_code = p_order_otm_status,
3082 POR.otm_recovery_flag = DECODE (p_otm_recovery_flag,
3083 'R', null,
3084 'N', POR.otm_recovery_flag,
3085 p_otm_recovery_flag)
3086 WHERE POR.po_release_id = p_doc_id;
3087 ELSE -- PO
3088 UPDATE po_headers_all POH
3089 SET POH.otm_status_code = p_order_otm_status,
3090 POH.otm_recovery_flag = DECODE (p_otm_recovery_flag,
3091 'R', null,
3092 'N', POH.otm_recovery_flag,
3093 p_otm_recovery_flag)
3094 WHERE POH.po_header_id = p_doc_id;
3095 END IF;
3096
3097 d_progress := '010';
3098
3099 IF (g_debug_stmt) THEN
3100 PO_DEBUG.debug_begin(d_module);
3101 END IF;
3102
3103 EXCEPTION
3104 WHEN OTHERS THEN
3105 IF (g_debug_stmt) THEN
3106 PO_DEBUG.debug_unexp(d_module, d_progress, 'Unexpected error: ' || SQLERRM);
3107 END IF;
3108 RAISE;
3109
3110 END update_order_otm_status;
3111
3112 -- OTM Recovery END
3113
3114 END;