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