1 PACKAGE BODY WSH_Document_PVT AS
2 -- $Header: WSHVPACB.pls 120.5.12020000.2 2013/03/05 11:40:26 sunilku ship $
3
4 ---------------------
5 -- TYPE DECLARATIONS
6 ---------------------
7
8 TYPE delivery_id_tabtype IS TABLE OF NUMBER;
9
10 ------------
11 -- CONSTANTS
12 ------------
13
14 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_Document_PVT';
15
16 --------------------
17 -- GLOBAL VARIABLES
18 --------------------
19
20 -- None
21
22 -----------------------------------
23 -- PRIVATE PROCEDURES AND FUNCTIONS
24 -----------------------------------
25
26 ------------------------------------------------------------------------------
27 -- PROCEDURE : GET_ChildDeliveryTab PRIVATE
28 -- PARAMETER LIST
29 -- IN
30 -- p_delivery_id Delivery for which child deliveries are required
31 -- OUT
32 -- x_child_delivery_tab Table of child delivery records for that delivery
33 --
34 -- COMMENT : For a delivery_id gets the delivery_ids of all
35 -- the child deliveries (all generations)
36 -- PRE-COND : None
37 -- POST-COND : None
38 -- EXCEPTIONS : None
39 ------------------------------------------------------------------------------
40
41 PROCEDURE GET_ChildDeliveryTab
42 ( p_delivery_id IN NUMBER
43 , x_child_delivery_tab OUT NOCOPY delivery_id_tabtype
44 )
45 IS
46 -- Get all child delivery ids (all successive generations)
47
48 l_loop_count NUMBER := 1;
49
50 --
51 l_debug_on BOOLEAN;
52 --
53 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CHILDDELIVERYTAB';
54 --
55 BEGIN
56 -- initialize the table
57 --
58 -- Debug Statements
59 --
60 --
61 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
62 --
63 IF l_debug_on IS NULL
64 THEN
65 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
66 END IF;
67 --
68 IF l_debug_on THEN
69 WSH_DEBUG_SV.push(l_module_name);
70 --
71 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
72 END IF;
73 --
74 x_child_delivery_tab := delivery_id_tabtype();
75 -- add children of this delivery id into the table
76 -- but exclude this delivery id in the table
77
78 --
79 -- Debug Statements
80 --
81 IF l_debug_on THEN
82 WSH_DEBUG_SV.log(l_module_name,'X_CHILD_DELIVERY_TAB.COUNT',x_child_delivery_tab.count);
83 WSH_DEBUG_SV.pop(l_module_name);
84 END IF;
85 --
86 END Get_ChildDeliveryTab;
87
88 ------------------------------------------------------------------------------
89 -- FUNCTION : GET_Sequence_Type PRIVATE
90 -- PARAMETER LIST
91 -- IN
92 -- p_doc_sequence_id sequence for which type is required
93 -- OUT
94 -- None
95 -- RETURN
96 -- Sequence Type sequence type ('A' for automatic, 'M' for manual)
97 --
98 -- COMMENT : Gets the type of a given sequence
99 -- PRE-COND : None
100 -- POST-COND : None
101 -- EXCEPTIONS : None
102 ------------------------------------------------------------------------------
103
104
105 FUNCTION Get_Sequence_Type ( p_doc_sequence_id IN NUMBER )
106 RETURN VARCHAR2
107 IS
108 CURSOR type_csr IS
109 SELECT
110 type
111 FROM
112 fnd_document_sequences
113 WHERE doc_sequence_id = p_doc_sequence_id;
114 type_rec type_csr%rowtype;
115 l_type VARCHAR2(1);
116 --
117 l_debug_on BOOLEAN;
118 --
119 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_SEQUENCE_TYPE';
120 --
121 BEGIN
122 --
123 -- Debug Statements
124 --
125 --
126 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
127 --
128 IF l_debug_on IS NULL
129 THEN
130 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
131 END IF;
132 --
133 IF l_debug_on THEN
134 WSH_DEBUG_SV.push(l_module_name);
135 --
136 WSH_DEBUG_SV.log(l_module_name,'P_DOC_SEQUENCE_ID',P_DOC_SEQUENCE_ID);
137 END IF;
138 --
139 OPEN type_csr;
140 FETCH type_csr INTO type_rec;
141 l_type := type_rec.type;
142 CLOSE type_csr;
143 --
144 -- Debug Statements
145 --
146 IF l_debug_on THEN
147 WSH_DEBUG_SV.log(l_module_name,'TYPE',l_type);
148 WSH_DEBUG_SV.pop(l_module_name);
149 END IF;
150 --
151 RETURN l_type;
152 END Get_Sequence_Type;
153
154 ------------------------------------------------------------------------------
155 -- FUNCTION : Init_Entity_Name PRIVATE
156 -- PARAMETER LIST
157 -- IN
158 -- p_document_type Type codes (PACK_TYPE, BOL, ASN, etc.)
159 -- p_entity_name Entity Name (current value)
160 -- OUT
161 -- None
162 -- RETURN Entity Name (new initialized value)
163 --
164 -- COMMENT : Initializes the entity name
165 -- PRE-COND : None
166 -- POST-COND : None
167 -- EXCEPTIONS : None
168 ------------------------------------------------------------------------------
169
170
171 FUNCTION Init_Entity_Name ( p_document_type IN VARCHAR2
172 , p_entity_name IN VARCHAR2 )
173 RETURN VARCHAR2
174 IS
175 l_entity_name wsh_document_instances.entity_name%type;
176 --
177 l_debug_on BOOLEAN;
178 --
179 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INIT_ENTITY_NAME';
180 --
181 BEGIN
182 --
183 -- Debug Statements
184 --
185 --
186 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
187 --
188 IF l_debug_on IS NULL
189 THEN
190 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
191 END IF;
192 --
193 IF l_debug_on THEN
194 WSH_DEBUG_SV.push(l_module_name);
195 --
196 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
197 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
198 END IF;
199 --
200 IF ( p_entity_name IS NOT NULL )
201 THEN
202 l_entity_name := p_entity_name;
203 ELSE
204 IF p_document_type = 'PACK_TYPE'
205 THEN
206 l_entity_name := 'WSH_NEW_DELIVERIES';
207 ELSIF p_document_type = 'BOL'
208 THEN
209 l_entity_name := 'WSH_DELIVERY_LEGS';
210 ELSIF p_document_type = 'MBOL'
211 THEN
212 l_entity_name := 'WSH_TRIPS';
213 END IF;
214 END IF;
215 --
216 -- Debug Statements
217 --
218 IF l_debug_on THEN
219 WSH_DEBUG_SV.log(l_module_name,'ENTITY_NAME',l_entity_name);
220 WSH_DEBUG_SV.pop(l_module_name);
221 END IF;
222 --
223 RETURN l_entity_name;
224 END Init_Entity_Name;
225
226
227 ------------------------------------------------------------------------------
228 -- FUNCTION : Get_Delivery_Leg_Id PRIVATE
229 -- PARAMETER LIST
230 -- IN
231 -- p_delivery_id Delivery Id for which the leg id is reqd
232 -- p_pick_up_stop_id Pick up stop Id
233 -- p_drop_off_stop_id Drop off stop Id
234 -- OUT
235 -- None
236 -- RETURN Delivery Leg Id that corresponds to an unique
237 -- combination of the delivery, pick-up, drop-off stops
238 --
239 -- COMMENT : Initializes the entity name
240 -- PRE-COND : None
241 -- POST-COND : None
242 -- EXCEPTIONS : None
243 -------------------------------------------------------------------------------
244
245 FUNCTION Get_Delivery_Leg_id ( p_delivery_id IN NUMBER
246 , p_pick_up_stop_id IN NUMBER
247 , p_drop_off_stop_id IN NUMBER
248 )
249 RETURN NUMBER
250 IS
251 l_delivery_leg_id NUMBER;
252 CURSOR delivery_leg_csr IS
253 SELECT
254 delivery_leg_id
255 FROM
256 wsh_delivery_legs
257 WHERE delivery_id = p_delivery_id
258 AND pick_up_stop_id = p_pick_up_stop_id
259 AND drop_off_stop_id = p_drop_off_stop_id;
260 --
261 l_debug_on BOOLEAN;
262 --
263 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DELIVERY_LEG_ID';
264 --
265 BEGIN
266 --
267 -- Debug Statements
268 --
269 --
270 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
271 --
272 IF l_debug_on IS NULL
273 THEN
274 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
275 END IF;
276 --
277 IF l_debug_on THEN
278 WSH_DEBUG_SV.push(l_module_name);
279 --
280 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
281 WSH_DEBUG_SV.log(l_module_name,'P_PICK_UP_STOP_ID',P_PICK_UP_STOP_ID);
282 WSH_DEBUG_SV.log(l_module_name,'P_DROP_OFF_STOP_ID',P_DROP_OFF_STOP_ID);
283 END IF;
284 --
285 OPEN delivery_leg_csr;
286 FETCH delivery_leg_csr INTO l_delivery_leg_id;
287 IF delivery_leg_csr%NOTFOUND
288 THEN
289 CLOSE delivery_leg_csr;
290 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_INVALID_DELIVERY');
291 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
292 RAISE FND_API.G_EXC_ERROR;
293 END IF;
294 CLOSE delivery_leg_csr;
295 --
296 -- Debug Statements
297 --
298 IF l_debug_on THEN
299 WSH_DEBUG_SV.log(l_module_name,'DELIVERY_LEG_ID',l_delivery_leg_id);
300 WSH_DEBUG_SV.pop(l_module_name);
301 END IF;
302 --
303 RETURN l_delivery_leg_id;
304 END Get_Delivery_Leg_Id;
305
306 ----------------------------------
307 -- PUBLIC PROCEDURES AND FUNCTIONS
308 ----------------------------------
309
310 -----------------------------------------------------------------------------
311 -- FUNCTION : Get_Sequence_Type PUBLIC
312 -- VERSION : 1.0
313 -- COMMENT : Checks and returns the type of a sequence assigned
314 -- to a specific document category
315 --
316 -- PARAMETER LIST :
317 --
318 -- IN
319 --
320 -- p_api_version known API version
321 -- p_init_msg_list should API reset message stack (default: false)
322 -- p_commit should API do a commit (default: false)
323 -- p_validation_level extent of validation done in the API (not used)
324 -- p_application_id appl id of the calling program. Should be same
325 -- as the application that owns the doc category
326 -- p_ledger_id Ledger id of the calling program. Should be as the
327 -- SOB used to setup the doc category/assignment
328 -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
329 -- p_document_code For pack slips this means document sub types (
330 -- 'SALES_ORDER') and for BOL ship method codes
331 -- p_location_id Ship Location of the current delivery.
332 --
333 -- OUT
334 --
335 -- x_msg_count number of messages in stack
336 -- x_msg_data message if there is only one message in stack
337 -- x_return_status API return status ('S', 'E', 'U')
338 --
339 -- RETURN sequence type ('A' for automatic, 'M' for manual)
340 --
341 -- PRE-CONDITIONS : None
342 -- POST-CONDITIONS : None
343 -- EXCEPTIONS : None
344 -----------------------------------------------------------------------------
345
346 FUNCTION Get_Sequence_Type
347 ( p_api_version IN NUMBER
348 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
349 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
350 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
351 , x_return_status OUT NOCOPY VARCHAR2
352 , x_msg_count OUT NOCOPY NUMBER
353 , x_msg_data OUT NOCOPY VARCHAR2
354 , p_application_id IN NUMBER
355 , p_ledger_id IN NUMBER -- LE Uptake
356 , p_document_type IN VARCHAR2
357 , p_document_code IN VARCHAR2
358 , p_location_id IN NUMBER
359 )
360 RETURN VARCHAR2
361 IS
362 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Sequence_Type';
363 L_API_VERSION CONSTANT NUMBER := 1.0;
364 l_seq_return NUMBER;
365 l_docseq_id NUMBER;
366 l_docseq_type VARCHAR2(1);
367 l_docseq_name VARCHAR2(255);
368 l_db_seq_name VARCHAR2(255);
369 l_seq_ass_id NUMBER;
370 l_prd_tab_name VARCHAR2(255);
371 l_aud_tab_name VARCHAR2(255);
372 l_msg_flag VARCHAR2(2000);
373
374 -- LE Uptake
375 CURSOR type_csr IS
376 SELECT
377 wsh.category_code,
378 fnd.method_code
379 FROM
380 wsh_doc_sequence_categories wsh
381 , fnd_doc_sequence_assignments fnd
382 WHERE wsh.document_type = p_document_type
383 AND wsh.enabled_flag = 'Y'
384 AND ( (wsh.location_id = p_location_id AND
385 wsh.document_code = p_document_code)
386 OR
387 (wsh.location_id = p_location_id AND wsh.document_code IS NULL)
388 OR
389 ((nvl(wsh.location_id,-99) = -99) AND wsh.document_code = p_document_code)
390 OR
391 ((nvl(wsh.location_id,-99) = -99) AND wsh.document_code IS NULL)
392 )
393 AND wsh.category_code = fnd.category_code
394 AND fnd.application_id = p_application_id
395 AND fnd.set_of_books_id = p_ledger_id
396 AND (fnd.end_date is NULL or fnd.end_date >= trunc(sysdate)) --Bug8608685 added trunc to sysdate
397 AND fnd.start_date <= sysdate ;
398 type_rec type_csr%rowtype;
399 l_type VARCHAR2(255);
400 --
401 l_debug_on BOOLEAN;
402 --
403 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_SEQUENCE_TYPE';
404 --
405 BEGIN
406 -- standard call to check for call compatibility.
407 --
408 -- Debug Statements
409 --
410 --
411 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
412 --
413 IF l_debug_on IS NULL
414 THEN
415 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
416 END IF;
417 --
418 IF l_debug_on THEN
419 WSH_DEBUG_SV.push(l_module_name);
420 --
421 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
422 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
423 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
424 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
425 WSH_DEBUG_SV.log(l_module_name,'P_APPLICATION_ID',P_APPLICATION_ID);
426 WSH_DEBUG_SV.log(l_module_name,'P_LEDGER_ID',P_LEDGER_ID); -- LE Uptake
427 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
428 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_CODE',P_DOCUMENT_CODE);
429 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
430 END IF;
431 --
432 IF NOT FND_API.Compatible_API_Call ( l_api_version,
433 p_api_version,
434 l_api_name,
435 g_pkg_name ) THEN
436 RAISE FND_API.g_exc_unexpected_error;
437 END IF;
438
439 -- initialize message list if p_init_msg_list is set to TRUE.
440
441 IF FND_API.to_boolean( p_init_msg_list ) THEN
442 FND_MSG_PUB.initialize;
443 END IF;
444
445 -- initialize API return status to success
446 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
447
448 OPEN type_csr;
449 FETCH type_csr INTO type_rec;
450 IF type_csr%NOTFOUND
451 THEN
452 CLOSE type_csr;
453 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_ASSIGNMENT_MISSING');
454 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
455 RAISE FND_API.G_EXC_ERROR;
456 END IF;
457 -- call FND API to get the sequence type information
458 l_seq_return := FND_SEQNUM.get_seq_info
459 ( app_id => p_application_id
460 , cat_code => type_rec.category_code
461 , sob_id => p_ledger_id -- LE Uptake
462 , met_code => type_rec.method_code
463 , trx_date => sysdate
464 , docseq_id => l_docseq_id
465 , docseq_type => l_docseq_type
466 , docseq_name => l_docseq_name
467 , db_seq_name => l_db_seq_name
468 , seq_ass_id => l_seq_ass_id
469 , prd_tab_name => l_prd_tab_name
470 , aud_tab_name => l_aud_tab_name
471 , msg_flag => l_msg_flag
472 );
473 l_type := l_docseq_type;
474 CLOSE type_csr;
475 --
476 -- Debug Statements
477 --
478 IF l_debug_on THEN
479 WSH_DEBUG_SV.log(l_module_name,'TYPE',l_type);
480 WSH_DEBUG_SV.pop(l_module_name);
481 END IF;
482 --
483 RETURN l_type;
484 EXCEPTION
485
486 WHEN FND_API.g_exc_error THEN
487 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
488 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
489 p_data => x_msg_data );
490 --
491 -- Debug Statements
492 --
493 IF l_debug_on THEN
494 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
495 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
496 END IF;
497 --
498 RETURN null;
499
500 WHEN FND_API.g_exc_unexpected_error THEN
501 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
502 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
503 p_data => x_msg_data );
504 --
505 -- Debug Statements
506 --
507 IF l_debug_on THEN
508 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
509 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
510 END IF;
511 --
512 RETURN null;
513
514 WHEN others THEN
515 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
516 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
517 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
518 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
519 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
520 WSH_UTIL_CORE.add_message (x_return_status);
521 --
522 -- Debug Statements
523 --
524 IF l_debug_on THEN
525 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
526 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
527 END IF;
528 --
529 RETURN null;
530
531 END Get_Sequence_Type;
532
533
534 ------------------------------------------------------------------------------
535 -- FUNCTION : Is_Final PUBLIC
536 -- VERSION : 1.0
537 -- COMMENT : Checks the status of all documents for the delivery
538 -- including its children. If any such document is final,
539 -- returns true. Else return false. This is used by
540 -- print document routine and packing slip report to bail
541 -- out if any of the document has final_print_date set.
542 --
543 -- PARAMETER LIST :
544 --
545 -- IN
546 --
547 -- p_api_version known API version
548 -- p_init_msg_list should API reset message stack (default: false)
549 -- p_commit should API do a commit (default: false)
550 -- p_validation_level extent of validation done in the API (not used)
551 -- p_delivery_id delivery_id of the delivery to check
552 -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
553 --
554 -- OUT
555 --
556 -- x_msg_count number of messages in stack
557 -- x_msg_data message if there is only one message in stack
558 -- x_return_status API return status ('S', 'E', 'U')
559 --
560 -- RETURN VARCHAR2, value 'T' or 'F'
561 --
562 -- PRE-CONDITIONS : None
563 -- POST-CONDITIONS : None
564 -- EXCEPTIONS : None
565 ------------------------------------------------------------------------------
566
567 FUNCTION Is_Final
568 ( p_api_version IN NUMBER
569 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
570 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
571 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
572 , x_return_status OUT NOCOPY VARCHAR2
573 , x_msg_count OUT NOCOPY NUMBER
574 , x_msg_data OUT NOCOPY VARCHAR2
575 , p_delivery_id IN NUMBER
576 , p_document_type IN VARCHAR2
577 )
578 RETURN VARCHAR2
579 IS
580 L_API_NAME CONSTANT VARCHAR2(30) := 'Is_Final';
581 L_API_VERSION CONSTANT NUMBER := 1.0;
582 l_msg_flag VARCHAR2(2000);
583
584
585 l_isfinal VARCHAR2(1);
586 l_temp_date DATE;
587
588 --
589 l_debug_on BOOLEAN;
590 --
591 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'IS_FINAL';
592 --
593 BEGIN
594 -- standard call to check for call compatibility.
595 --
596 -- Debug Statements
597 --
598 --
599 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
600 --
601 IF l_debug_on IS NULL
602 THEN
603 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
604 END IF;
605 --
606 IF l_debug_on THEN
607 WSH_DEBUG_SV.push(l_module_name);
608 --
609 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
610 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
611 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
612 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
613 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
614 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
615 END IF;
616 --
617 IF NOT FND_API.Compatible_API_Call ( l_api_version,
618 p_api_version,
619 l_api_name,
620 g_pkg_name ) THEN
621 RAISE FND_API.g_exc_unexpected_error;
622 END IF;
623
624 -- initialize message list if p_init_msg_list is set to TRUE.
625 IF FND_API.to_boolean( p_init_msg_list ) THEN
626 FND_MSG_PUB.initialize;
627 END IF;
628
629 -- initialize API return status to success
630 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
631
632 -- initially l_isfinal=FALSE, means no document is not final
633 l_isfinal := FND_API.G_false;
634
635 -- if p_consolidate_option is CONSOLIDATE or BOTH, we need to check
636 -- the parent delivery first
637 SELECT doc.final_print_date
638 INTO l_temp_date
639 FROM wsh_document_instances doc
640 WHERE doc.entity_id=p_delivery_id
641 AND doc.entity_name='WSH_NEW_DELIVERIES'
642 AND doc.document_type=p_document_type;
643 IF l_temp_date IS NOT NULL
644 THEN
645 l_isfinal := FND_API.g_true;
646 END IF;
647 --
648 -- Debug Statements
649 --
650 IF l_debug_on THEN
651 WSH_DEBUG_SV.log(l_module_name,'IS FINAL',l_isfinal);
652 WSH_DEBUG_SV.pop(l_module_name);
653 END IF;
654 --
655 RETURN l_isfinal;
656
657 EXCEPTION
658
659 WHEN FND_API.g_exc_error THEN
660 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
661 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
662 p_data => x_msg_data );
663 --
664 -- Debug Statements
665 --
666 IF l_debug_on THEN
667 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
668 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
669 END IF;
670 --
671 RETURN null;
672
673 WHEN FND_API.g_exc_unexpected_error THEN
674 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
675 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
676 p_data => x_msg_data );
677 --
678 -- Debug Statements
679 --
680 IF l_debug_on THEN
681 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
682 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
683 END IF;
684 --
685 RETURN null;
686
687 WHEN others THEN
688 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
689 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
690 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
691 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
692 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
693 WSH_UTIL_CORE.add_message (x_return_status);
694 --
695 -- Debug Statements
696 --
697 IF l_debug_on THEN
698 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
699 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
700 END IF;
701 --
702 RETURN null;
703
704 END Is_Final;
705
706 ------------------------------------------------------------------------------
707 -- PROCEDURE : Set_Final_Print_Date PUBLIC
708 -- VERSION : 1.0
709 -- COMMENT : Set the FINAL_PRINT_DATE column of all document instances
710 -- of the delivery and/or its child delivery to SYSDATE.
711 -- This procedure is called when user chooses print option
712 -- as FINAL. This means later the same document instances
713 -- cannot be printed as they fail the Is_Final check.
714 --
715 -- PARAMETER LIST :
716 --
717 -- IN
718 --
719 -- p_api_version known API version
720 -- p_init_msg_list should API reset message stack (default: false)
721 -- p_commit should API do a commit (default: false)
722 -- p_validation_level extent of validation done in the API (not used)
723 -- p_delivery_id delivery_id of the delivery to check
724 -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
725 -- p_final_print_date the final_print_date to be set
726 --
727 -- OUT
728 --
729 -- x_msg_count number of messages in stack
730 -- x_msg_data message if there is only one message in stack
731 -- x_return_status API return status ('S', 'E', 'U')
732 --
733 --
734 -- PRE-CONDITIONS : FINAL_PRINT_DATE column of WSH_DOCUMENT_INSTANCES
735 -- rows of related deliveries have NULL value
736 -- POST-CONDITIONS : such FINAL_PRINT_DATE columns have SYSDATE value
737 -- EXCEPTIONS : None
738 ------------------------------------------------------------------------------
739
740 PROCEDURE Set_Final_Print_Date
741 ( p_api_version IN NUMBER
742 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
743 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
744 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
745 , x_return_status OUT NOCOPY VARCHAR2
746 , x_msg_count OUT NOCOPY NUMBER
747 , x_msg_data OUT NOCOPY VARCHAR2
748 , p_delivery_id IN NUMBER
749 , p_document_type IN VARCHAR2
750 , p_final_print_date IN DATE
751 )
752 IS
753
754 L_API_NAME CONSTANT VARCHAR2(30) := 'Set_Final_Print_Date';
755 L_API_VERSION CONSTANT NUMBER := 1.0;
756 l_msg_flag VARCHAR2(2000);
757 l_temp_date DATE;
758
759 --
760 l_debug_on BOOLEAN;
761 --
762 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SET_FINAL_PRINT_DATE';
763 --
764 BEGIN
765
766 --
767 -- Debug Statements
768 --
769 --
770 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
771 --
772 IF l_debug_on IS NULL
773 THEN
774 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
775 END IF;
776 --
777 IF l_debug_on THEN
778 WSH_DEBUG_SV.push(l_module_name);
779 --
780 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
781 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
782 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
783 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
784 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
785 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
786 WSH_DEBUG_SV.log(l_module_name,'P_FINAL_PRINT_DATE',P_FINAL_PRINT_DATE);
787 END IF;
788 --
789 SAVEPOINT WSH_Document_PVT;
790
791 -- standard call to check for call compatibility.
792 IF NOT FND_API.Compatible_API_Call ( l_api_version,
793 p_api_version,
794 l_api_name,
795 g_pkg_name ) THEN
796 RAISE FND_API.g_exc_unexpected_error;
797 END IF;
798
799 -- initialize message list if p_init_msg_list is set to TRUE.
800 IF FND_API.to_boolean( p_init_msg_list ) THEN
801 FND_MSG_PUB.initialize;
802 END IF;
803
804 -- initialize API return status to success
805 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
806
807 -- initialize l_temp_date to p_final_print_date
808 l_temp_date := p_final_print_date;
809
810 UPDATE
811 wsh_document_instances doc
812 SET
813 doc.final_print_date = l_temp_date
814 WHERE doc.entity_id=p_delivery_id
815 AND doc.entity_name='WSH_NEW_DELIVERIES'
816 AND doc.document_type=p_document_type;
817
818 -- get message count and the message itself (if only one message)
819 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
820 p_data => x_msg_data);
821
822 -- Standard check of p_commit.
823 IF FND_API.to_boolean( p_commit ) THEN
824 COMMIT WORK;
825 END IF;
826
827 --
828 -- Debug Statements
829 --
830 IF l_debug_on THEN
831 WSH_DEBUG_SV.pop(l_module_name);
832 END IF;
833 --
834 EXCEPTION
835
836 WHEN FND_API.g_exc_error THEN
837
838 ROLLBACK to WSH_Document_PVT;
839 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
840 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
841 p_data => x_msg_data );
842
843 --
844 -- Debug Statements
845 --
846 IF l_debug_on THEN
847 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
848 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
849 END IF;
850 --
851 WHEN FND_API.g_exc_unexpected_error THEN
852
853 ROLLBACK to WSH_Document_PVT;
854 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
855 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
856 p_data => x_msg_data );
857 --
858 -- Debug Statements
859 --
860 IF l_debug_on THEN
861 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
862 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
863 END IF;
864 --
865 WHEN others THEN
866
867 ROLLBACK to WSH_Document_PVT;
868 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
869 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
870 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
871 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
872 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
873 WSH_UTIL_CORE.add_message (x_return_status);
874 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
875 p_data => x_msg_data );
876
877 --
878 -- Debug Statements
879 --
880 IF l_debug_on THEN
881 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
882 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
883 END IF;
884 --
885 END Set_Final_Print_Date;
886
887 -----------------------------------------------------------------------------
888 -- PROCEDURE : Create_Document PUBLIC
889 -- VERSION : 1.0
890 -- COMMENT : Creates a document (packing slip, bill of lading) for a
891 -- delivery and assigns(or validates) a sequence number
892 -- as per pre-defined document category definitions
893 --
894 -- PARAMETER LIST :
895 --
896 -- IN
897 --
898 -- p_api_version known API version
899 -- p_init_msg_list should API reset message stack (default: false)
900 -- p_commit should API do a commit (default: false)
901 -- p_validation_level extent of validation done in the API (not used)
902 -- p_entity_name Entity for which the document is being created
903 -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
904 -- p_entity_id Entity id that the document belongs to
905 -- example: delivery_id, delivery_leg_id, etc
906 -- p_application_id Application which is creating the document (
907 -- should be same as the one that owns the
908 -- document category )
909 -- p_location_id Location id which the document is being created
910 -- p_document_type type codes (PACK_TYPE, BOL, ASN, etc.)
911 -- p_document_sub_type for packing slips (SALES_ORDER, etc) and
912 -- for Bills of Lading the ship method codes
913 -- p_pod_flag pod_flag for the document
914 -- p_pod_by pod_by for the document
915 -- p_pod_date pod_date for the document
916 -- p_reason_of_transport reason of transport that describes the delivery
917 -- p_description external aspect of the delivery
918 -- p_cod_amount cod_amount of the document
919 -- p_cod_currency_code cod_currency_code of the document
920 -- p_cod_remit_to cod_remit_to of the document
921 -- p_cod_charge_paid_by cod_charge_paid_by of the document
922 -- p_problem_contact_reference problem_contact_referene of the document
923 -- p_bill_freight_to bill_freight_to of the document
924 -- p_carried_by carried_by of the document
925 -- p_port_of_loading port_of_loading of the docucent
926 -- p_port_of_discharge port_of_discharge of the document
927 -- p_booking_office booking_office of the document
928 -- p_booking_number booking_number of the document
929 -- p_service_contract service_contract of the document
930 -- p_shipper_export_ref shipper_export_ref of the document
931 -- p_carrier_export_ref carrier_export_ref of the document
932 -- p_bol_notify_party bol_notify_party of the document
933 -- p_supplier_code supplier_code of the document
934 -- p_aetc_number aetc_number of the document
935 -- p_shipper_signed_by shipper_signed_by of the document
936 -- p_shipper_date shipper_date of the document
937 -- p_carrier_signed_by carrier_signed_by of the document
938 -- p_carrier_date carrier_date of the document
939 -- p_bol_issue_office bol_issue_office of the document
940 -- p_bol_issued_by bol_issued_by of the document
941 -- p_bol_date_issued bol_date_issued of the document
942 -- p_shipper_hm_by shipper_bm_by of the document
943 -- p_shipper_hm_date shipper_hm_date of the document
944 -- p_carrier_hm_by carrier_hm_by of the document
945 -- p_carrier_hm_date carrier_hm_date of the document
946 -- p_ledger_id Ledger id attached to the calling program (
947 -- should be same as SOB used to setup the
948 -- document category/assignment )
949 -- p_consolidate_option calling program's choice to create document(s)
950 -- for this parent delivery only ('CONSOLIDATE')
951 -- or for child dels of this delivery ('SEPARATE')
952 -- or both parent and child deliveries ('BOTH')
953 -- p_manual_sequence_number user defined sequence number ( used only
954 -- if the document falls in a category that has
955 -- manual type suquence assigned to it (else null)
956 --
957 -- OUT
958 --
959 -- x_msg_count number of messages in stack
960 -- x_msg_data message if there is only one message in stack
961 -- x_document_number the document number (generated/manual sequence
962 -- with concatenated prefix and suffix).
963 -- x_return_status API return status ('S', 'E', 'U')
964 --
965 -- PRE-CONDITIONS : The delivery should be existing in the Database
966 -- POST-CONDITIONS : None
967 -- EXCEPTIONS : None
968 -----------------------------------------------------------------------------
969
970 PROCEDURE Create_Document
971 ( p_api_version IN NUMBER
972 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
973 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
974 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
975 , x_return_status OUT NOCOPY VARCHAR2
976 , x_msg_count OUT NOCOPY NUMBER
977 , x_msg_data OUT NOCOPY VARCHAR2
978 , p_entity_name IN VARCHAR2 DEFAULT NULL
979 , p_entity_id IN NUMBER
980 , p_application_id IN NUMBER
981 , p_location_id IN NUMBER
982 , p_document_type IN VARCHAR2
983 , p_document_sub_type IN VARCHAR2
984 , p_ledger_id IN NUMBER -- LE Uptake
985 , p_consolidate_option IN VARCHAR2 DEFAULT 'BOTH'
986 , p_manual_sequence_number IN NUMBER DEFAULT NULL
987 , x_document_number OUT NOCOPY VARCHAR2)
988 IS
989 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Document';
990 L_API_VERSION CONSTANT NUMBER := 1.0;
991 l_sequence_id fnd_sequences.sequence_id%type;
992 l_sequence_name fnd_sequences.sequence_name%type;
993 l_sequence_number NUMBER(38);
994 l_delivery_id NUMBER(38);
995 l_delivery_leg_id NUMBER(38);
996 l_delivery_id_tab delivery_id_tabtype := delivery_id_tabtype();
997 l_table_count NUMBER;
998 l_doc_sequence_id fnd_document_sequences.doc_sequence_id%type;
999 l_doc_sequence_category_id
1000 wsh_doc_sequence_categories.doc_sequence_category_id%type;
1001 l_document_number VARCHAR2(255);
1002 l_seq_return NUMBER;
1003 l_seq_type VARCHAR2(255);
1004 l_entity_name wsh_document_instances.entity_name%type;
1005 l_debug_msg VARCHAR2(32000);
1006 l_prefix VARCHAR2(10);
1007 l_suffix VARCHAR2(10);
1008 l_delivery_name VARCHAR2(30);
1009 l_status VARCHAR2(10) := 'OPEN'; -- bug 3761178
1010 l_entity_id NUMBER(38); -- bug 3761178
1011
1012 CURSOR trip_stop_csr IS
1013 SELECT
1014 delivery_id
1015 , pick_up_stop_id
1016 , drop_off_stop_id
1017 FROM
1018 wsh_delivery_legs
1019 WHERE delivery_leg_id = p_entity_id;
1020
1021
1022 CURSOR document_csr ( c_entity_name IN VARCHAR2
1023 , c_entity_id IN NUMBER )
1024 IS
1025 SELECT
1026 sequence_number
1027 FROM
1028 wsh_document_instances
1029 WHERE entity_name = c_entity_name
1030 AND entity_id = c_entity_id
1031 AND status <> 'CANCELLED'
1032 -- AND status = 'OPEN'
1033 AND document_type = p_document_type;
1034
1035 -- identify the category that is defined for this location
1036 -- and document sub type or for all locations or all sub types
1037
1038 CURSOR category_csr IS
1039 SELECT
1040 doc_sequence_category_id
1041 , category_code
1042 , prefix
1043 , suffix
1044 , delimiter
1045 FROM
1046 wsh_doc_sequence_categories
1047 WHERE document_type = p_document_type
1048 AND enabled_flag = 'Y'
1049 AND ((location_id = p_location_id AND document_code = p_document_sub_type)
1050 OR
1051 (location_id = p_location_id AND document_code IS NULL)
1052 OR
1053 -- (location_id IS NULL AND document_code = p_document_sub_type)
1054 -- change location_id is null to location_id = -99 (all locations => -99)
1055 (location_id = -99 AND document_code = p_document_sub_type)
1056 OR
1057 (location_id = -99 AND document_code IS NULL))
1058 ORDER BY location_id desc, document_code asc; --Bugfix 13944987
1059
1060 -- get the method code for the sequence assigned to this category.
1061 -- does not support multiple sequences being assigned to a category.
1062 -- in such cases the first assignment's method code only is used.
1063
1064 -- LE Uptake
1065 CURSOR assignment_csr (c_category_code IN VARCHAR2) IS
1066 SELECT
1067 method_code,
1068 doc_sequence_id
1069 FROM
1070 fnd_doc_sequence_assignments
1071 WHERE application_id = p_application_id
1072 AND set_of_books_id = p_ledger_id
1073 AND category_code = c_category_code
1074 AND start_date <= sysdate
1075 AND ( (end_date IS NULL)
1076 OR
1077 (end_date >= trunc(sysdate)) --Bug8608685 added trunc to sysdate
1078 AND start_date <= sysdate
1079 );
1080
1081 CURSOR delivery_csr (c_delivery_id IN NUMBER) IS
1082 SELECT
1083 delivery_id
1084 FROM
1085 wsh_new_deliveries
1086 WHERE delivery_id = c_delivery_id;
1087
1088 CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
1089 SELECT
1090 delivery_id
1091 FROM
1092 wsh_delivery_legs
1093 WHERE delivery_leg_id = c_delivery_leg_id;
1094
1095 --
1096 --Bug 4284167 (FP Bug 4149501)
1097 --
1098 CURSOR get_lock_on_leg(p_delivery_leg_id IN NUMBER) IS
1099 SELECT p_delivery_leg_id
1100 FROM wsh_delivery_legs
1101 WHERE delivery_leg_id = p_delivery_leg_id
1102 FOR UPDATE NOWAIT;
1103
1104 document_rec document_csr%rowtype;
1105 category_rec category_csr%rowtype;
1106 assignment_rec assignment_csr%rowtype;
1107 delivery_rec delivery_csr%rowtype;
1108 delivery_id_rec delivery_id_csr%rowtype;
1109 trip_stop_rec trip_stop_csr%rowtype;
1110
1111 --Bug 4284167 (FP Bug 4149501)
1112 lock_detected EXCEPTION;
1113 PRAGMA EXCEPTION_INIT( lock_detected, -00054);
1114 --
1115 l_debug_on BOOLEAN;
1116 --
1117 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_DOCUMENT';
1118 --
1119 BEGIN
1120 -- since this procedure does DML issue savepoint
1121 --
1122 -- Debug Statements
1123 --
1124 --
1125 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1126 --
1127 IF l_debug_on IS NULL
1128 THEN
1129 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1130 END IF;
1131 --
1132 IF l_debug_on THEN
1133 WSH_DEBUG_SV.push(l_module_name);
1134 --
1135 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
1136 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
1137 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
1138 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
1139 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
1140 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
1141 WSH_DEBUG_SV.log(l_module_name,'P_APPLICATION_ID',P_APPLICATION_ID);
1142 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
1143 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
1144 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_SUB_TYPE',P_DOCUMENT_SUB_TYPE);
1145 WSH_DEBUG_SV.log(l_module_name,'P_LEDGER_ID',P_LEDGER_ID); -- LE Uptake
1146 WSH_DEBUG_SV.log(l_module_name,'P_CONSOLIDATE_OPTION',P_CONSOLIDATE_OPTION);
1147 WSH_DEBUG_SV.log(l_module_name,'P_MANUAL_SEQUENCE_NUMBER',P_MANUAL_SEQUENCE_NUMBER);
1148 END IF;
1149 --
1150 SAVEPOINT WSH_Document_PVT;
1151
1152 -- standard call to check for call compatibility.
1153 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1154 p_api_version,
1155 l_api_name,
1156 g_pkg_name ) THEN
1157 RAISE FND_API.g_exc_unexpected_error;
1158 END IF;
1159
1160 -- initialize message list if p_init_msg_list is set to TRUE.
1161
1162 IF FND_API.to_boolean( p_init_msg_list ) THEN
1163 FND_MSG_PUB.initialize;
1164 END IF;
1165
1166 -- initialize API return status to success
1167 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
1168 OPEN category_csr;
1169 FETCH category_csr INTO category_rec;
1170 IF category_csr%NOTFOUND
1171 THEN
1172 CLOSE category_csr;
1173 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_CATEGORY_MISSING');
1174 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
1175 RAISE FND_API.G_EXC_ERROR;
1176 END IF;
1177 l_doc_sequence_category_id := category_rec.doc_sequence_category_id;
1178 CLOSE category_csr;
1179
1180 -- get the method code for the sequence assigned to this category.
1181 -- does not support multiple sequences being assigned to a category.
1182 -- in such cases the first assignment's method code only is used.
1183
1184 OPEN assignment_csr(category_rec.category_code);
1185 FETCH assignment_csr INTO assignment_rec;
1186 IF assignment_csr%NOTFOUND THEN
1187 CLOSE assignment_csr;
1188 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_ASSIGNMENT_MISSING');
1189 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
1190 RAISE FND_API.G_EXC_ERROR;
1191 END IF;
1192 l_doc_sequence_id := assignment_rec.doc_sequence_id;
1193 CLOSE assignment_csr;
1194
1195 -------------------------------------------------------------
1196 -- Initialize the entity_name based on the document type --
1197 -- If the entity is Delivery leg, look up its delivery id --
1198 -- to be used to build the child delivery table later --
1199 -------------------------------------------------------------
1200
1201 l_entity_name := Init_Entity_Name (p_document_type, p_entity_name);
1202 IF l_entity_name = 'WSH_DELIVERY_LEGS'
1203 THEN
1204 OPEN delivery_id_csr (p_entity_id);
1205 FETCH delivery_id_csr INTO delivery_id_rec;
1206 l_delivery_id := delivery_id_rec.delivery_id;
1207 CLOSE delivery_id_csr;
1208 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
1209 THEN
1210 l_delivery_id := p_entity_id;
1211 END IF;
1212
1213 ----------------------------------------------
1214 -- if the document is for delivery leg --
1215 -- get its pick up and drop off trip stops --
1216 ----------------------------------------------
1217 IF l_entity_name = 'WSH_DELIVERY_LEGS'
1218 THEN
1219 OPEN trip_stop_csr;
1220 FETCH trip_stop_csr INTO trip_stop_rec;
1221 CLOSE trip_stop_csr;
1222 END IF;
1223
1224 IF l_entity_name <> 'WSH_TRIPS' THEN
1225 ----------------------------------------------
1226 -- Validate the delivery id --
1227 ----------------------------------------------
1228 OPEN delivery_csr (l_delivery_id);
1229 FETCH delivery_csr INTO delivery_rec;
1230 IF delivery_csr%NOTFOUND
1231 THEN
1232 CLOSE delivery_csr;
1233 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_INVALID_DELIVERY');
1234 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
1235 RAISE FND_API.G_EXC_ERROR;
1236 END IF;
1237 CLOSE delivery_csr;
1238
1239 ---------------------------------------------------------------
1240 -- based on the consolidate_option identify the delivery ids --
1241 -- to create documents for --
1242 ---------------------------------------------------------------
1243
1244 IF (p_consolidate_option IN ('BOTH', 'SEPARATE'))
1245 THEN
1246 GET_ChildDeliveryTab ( l_delivery_id , l_delivery_id_tab );
1247 END IF;
1248
1249 IF p_consolidate_option IN ('BOTH', 'CONSOLIDATE') THEN
1250 l_table_count := l_delivery_id_tab.count;
1251 l_delivery_id_tab.extend;
1252 l_delivery_id_tab(l_table_count+1) := l_delivery_id;
1253 END IF;
1254
1255 IF NOT l_delivery_id_tab.EXISTS(1) THEN
1256 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_INVALID_DELIVERY');
1257 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
1258 RAISE FND_API.G_EXC_ERROR;
1259 END IF;
1260
1261
1262 FOR ctr IN 1..l_delivery_id_tab.count LOOP
1263
1264 -----------------------------------------------------------
1265 -- For each delivery, if the docuement is required for --
1266 -- delivery leg entity then identify the delivery leg id --
1267 -----------------------------------------------------------
1268
1269 IF l_entity_name = 'WSH_DELIVERY_LEGS'
1270 THEN
1271
1272 l_delivery_leg_id := Get_Delivery_Leg_Id ( l_delivery_id_tab(ctr)
1273 , trip_stop_rec.pick_up_stop_id
1274 , trip_stop_rec.drop_off_stop_id
1275 );
1276 END IF;
1277
1278 -----------------------------------------------------------
1279 -- For every entity check if a document of this type --
1280 -- already exists with OPEN status ( Here the entity --
1281 -- would be Deliveries in case of packing slips and --
1282 -- Delivery legs in case of Bill of Lading ) --
1283 -----------------------------------------------------------
1284
1285 IF document_csr%ISOPEN
1286 THEN
1287 CLOSE document_csr;
1288 END IF;
1289 IF l_entity_name = 'WSH_DELIVERY_LEGS'
1290 THEN
1291 OPEN document_csr ( l_entity_name
1292 , l_delivery_leg_id
1293 ) ;
1294 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
1295 THEN
1296 OPEN document_csr (l_entity_name, l_delivery_id_tab(ctr));
1297 END IF;
1298
1299 FETCH document_csr INTO document_rec;
1300 IF document_csr%FOUND THEN
1301 CLOSE document_csr;
1302
1303 --Fix for bug 3878973
1304 --If document exists already, just return success
1305
1306 IF l_debug_on THEN
1307 WSH_DEBUG_SV.logmsg(l_module_name, 'Doc Number exists already');
1308 WSH_DEBUG_SV.pop(l_module_name);
1309 END IF;
1310 RETURN;
1311 END IF;
1312 CLOSE document_csr;
1313
1314 l_seq_type := get_sequence_type (l_doc_sequence_id);
1315
1316 -- for manual seq the user input is considered
1317 IF l_seq_type = 'M'
1318 THEN
1319 l_sequence_number := p_manual_sequence_number;
1320 END IF;
1321
1322 -------------------------------------------------------------------------
1323 -- if the sequence type is automatic --
1324 -- call the FND API to get a new number for each delivery --
1325 -- if the sequence type is manual --
1326 -- if this is a parent delivery --
1327 -- call the FND API to validate the number given by the user --
1328 -- if this is a child delivery --
1329 -- use the same number given by the user for all child deliveries --
1330 -------------------------------------------------------------------------
1331 -- 2695602: Added type = 'G' below for handling Gapless Sequences
1332
1333 IF (l_seq_type = 'A')
1334 OR
1335 (l_seq_type = 'G')
1336 OR
1337 ((l_seq_type = 'M') AND (l_delivery_id_tab(ctr) = l_delivery_id) )
1338 THEN
1339 l_seq_return := FND_SEQNUM.get_seq_val
1340 ( app_id => p_application_id
1341 , cat_code => category_rec.category_code
1342 , sob_id => p_ledger_id -- LE Uptake
1343 , met_code => assignment_rec.method_code
1344 , trx_date => sysdate
1345 , seq_val => l_sequence_number
1346 , docseq_id => l_sequence_id );
1347 END IF;
1348 IF NVL(l_sequence_number,0) = 0
1349 THEN
1350 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_SEQ_ERROR');
1351 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
1352 RAISE FND_API.G_EXC_ERROR;
1353 END IF;
1354
1355 /* l_document_number := LTRIM(RTRIM(category_rec.prefix)) ||
1356 category_rec.delimiter ||
1357 LTRIM(RTRIM(TO_CHAR(l_sequence_number))) ||
1358 category_rec.delimiter ||
1359 LTRIM(RTRIM(category_rec.suffix)); */
1360
1361 /* Bug 1973913 begins */
1362
1363 IF category_rec.prefix is not NULL THEN
1364 l_prefix:= LTRIM(RTRIM(category_rec.prefix))||category_rec.delimiter;
1365 END IF;
1366 IF category_rec.suffix is not NULL THEN
1367 l_suffix:= category_rec.delimiter||LTRIM(RTRIM(category_rec.suffix));
1368 END IF;
1369
1370 -------------------------------------------------------------
1371 -- Bug 2310825 : Removed the LPAD which was being used to pad
1372 -- the sequence number with 0's.
1373 -------------------------------------------------------------
1374
1375 l_document_number:=l_prefix||LTRIM(RTRIM(TO_CHAR(l_sequence_number))) ||l_suffix;
1376
1377 /* Bug 1973913 ends */
1378
1379 -- if this is the parent delivery id then
1380 -- return the seq number to the calling program
1381
1382 IF l_delivery_id_tab(ctr) = l_delivery_id THEN
1383 x_document_number := l_document_number;
1384 END IF;
1385
1386 --{ Bug 3761178
1387 --
1388 -- The decode statements that were in the values clause of the coming insert stmt
1389 -- have been modified to make use of local variables for performance reasons.
1390 --
1391
1392 IF l_entity_name = 'WSH_DELIVERY_LEGS' THEN
1393 l_status := 'PLANNED';
1394 l_entity_id := l_delivery_leg_id;
1395 ELSIF l_entity_name ='WSH_NEW_DELIVERIES' THEN
1396 l_status := 'OPEN';
1397 l_entity_id := l_delivery_id_tab(ctr);
1398 END IF;
1399
1400 --}
1401
1402 ---------------------------------------------------------
1403 -- logic in insert statement: --
1404 -- --
1405 -- if the entity is delivery ( packing slips ) --
1406 -- then entity_name is WSH_NEW_DELIVERIES --
1407 -- and entity_id is delivery_id --
1408 -- and if there is consolidation --
1409 -- then for child deliveries --
1410 -- entity_name is WSH_NEW_DELIVERIES --
1411 -- entity_id is child delivery_id --
1412 -- if the entity is delivery leg ( bill of lading ) --
1413 -- then entity_name is WSH_DELIVERY_LEGS --
1414 -- and entity_id is delivery_leg_id --
1415 -- and if there is consolidation --
1416 -- then for child deliveries --
1417 -- entity_name is WSH_DELIVERY_LEGS --
1418 -- entity_id is the delivery_leg_id --
1419 -- that corresponds to the --
1420 -- child delivery_id, --
1421 -- master pick_up_stop_id, --
1422 -- master drop_off_stop_id --
1423 -- - - - - - - - - - - - - - - - - - - - - - - - - - - --
1424 -- Assumption here: --
1425 -- --
1426 -- All deliveries (even those contained in another --
1427 -- delivery for consolidation purposes) will have a --
1428 -- delivery leg created before the Bill of Lading can --
1429 -- be created. --
1430 ---------------------------------------------------------
1431 INSERT INTO wsh_document_instances
1432 ( document_instance_id
1433 , document_type
1434 , sequence_number
1435 , status
1436 , final_print_date
1437 , entity_name
1438 , entity_id
1439 , doc_sequence_category_id
1440 , created_by
1441 , creation_date
1442 , last_updated_by
1443 , last_update_date
1444 , last_update_login
1445 , program_application_id
1446 , program_id
1447 , program_update_date
1448 , request_id
1449 , attribute_category
1450 , attribute1
1451 , attribute2
1452 , attribute3
1453 , attribute4
1454 , attribute5
1455 , attribute6
1456 , attribute7
1457 , attribute8
1458 , attribute9
1459 , attribute10
1460 , attribute11
1461 , attribute12
1462 , attribute13
1463 , attribute14
1464 , attribute15
1465 )
1466 VALUES
1467 ( wsh_document_instances_s.nextval
1468 , p_document_type
1469 , l_document_number
1470 , l_status
1471 , null
1472 , l_entity_name
1473 , l_entity_id
1474 , l_doc_sequence_category_id
1475 , fnd_global.user_id
1476 , sysdate
1477 , fnd_global.user_id
1478 , sysdate
1479 , fnd_global.login_id
1480 , null
1481 , null
1482 , null
1483 , null
1484 , null
1485 , null
1486 , null
1487 , null
1488 , null
1489 , null
1490 , null
1491 , null
1492 , null
1493 , null
1494 , null
1495 , null
1496 , null
1497 , null
1498 , null
1499 , null
1500 );
1501
1502 --
1503 --Bug 4284167 (FP Bug 4149501)ISSUED DATE OF A BOL IS NOT GETTING SYSTEM GENERATED.
1504 --
1505
1506 IF l_entity_name = 'WSH_DELIVERY_LEGS' THEN
1507
1508 OPEN get_lock_on_leg(l_delivery_leg_id);
1509
1510 FETCH get_lock_on_leg INTO l_delivery_leg_id;
1511 IF (get_lock_on_leg%FOUND) THEN
1512 UPDATE wsh_delivery_legs
1513 SET doc_date_issued = SYSDATE
1514 WHERE current of get_lock_on_leg;
1515 END IF;
1516 CLOSE get_lock_on_leg;
1517
1518 END IF;
1519
1520 END LOOP;
1521
1522 ELSE--for l_entity_name <> 'WSH_TRIPS'
1523
1524 --Check if document number for the entity type and the name
1525 --already exists.
1526 OPEN document_csr (p_entity_name, p_entity_id);
1527 FETCH document_csr INTO document_rec;
1528 IF document_csr%FOUND THEN
1529 CLOSE document_csr;
1530
1531 --Fix for bug 3878973
1532 --If document exists already, just return success
1533 IF l_debug_on THEN
1534 WSH_DEBUG_SV.logmsg(l_module_name, 'Doc Number exists already');
1535 WSH_DEBUG_SV.pop(l_module_name);
1536 END IF;
1537 RETURN;
1538 END IF;
1539 CLOSE document_csr;
1540
1541 l_seq_type := get_sequence_type (l_doc_sequence_id);
1542 -- for manual seq the user input is considered
1543 IF l_seq_type = 'M'
1544 THEN
1545 l_sequence_number := p_manual_sequence_number;
1546 END IF;
1547
1548 -------------------------------------------------------------------------
1549 -- if the sequence type is automatic or Gapless --
1550 -- call the FND API to get a new number --
1551 -- if the sequence type is manual --
1552 -- use the number given by the user --
1553 -------------------------------------------------------------------------
1554
1555 IF (l_seq_type = 'A')
1556 OR
1557 (l_seq_type = 'G')
1558 THEN
1559 l_seq_return := FND_SEQNUM.get_seq_val
1560 ( app_id => p_application_id
1561 , cat_code => category_rec.category_code
1562 , sob_id => p_ledger_id -- LE Uptake
1563 , met_code => assignment_rec.method_code
1564 , trx_date => sysdate
1565 , seq_val => l_sequence_number
1566 , docseq_id => l_sequence_id );
1567 END IF;
1568
1569 IF NVL(l_sequence_number,0) = 0
1570 THEN
1571 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_SEQ_ERROR');
1572 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
1573 RAISE FND_API.G_EXC_ERROR;
1574 END IF;
1575
1576 IF category_rec.prefix is not NULL THEN
1577 l_prefix:= LTRIM(RTRIM(category_rec.prefix))||category_rec.delimiter;
1578 END IF;
1579 IF category_rec.suffix is not NULL THEN
1580 l_suffix:= category_rec.delimiter||LTRIM(RTRIM(category_rec.suffix));
1581 END IF;
1582
1583 l_document_number:=l_prefix||LTRIM(RTRIM(TO_CHAR(l_sequence_number))) ||l_suffix;
1584
1585 x_document_number := l_document_number;
1586
1587 l_status := 'OPEN'; --bug # 3789154
1588
1589 INSERT INTO wsh_document_instances
1590 ( document_instance_id
1591 , document_type
1592 , sequence_number
1593 , status
1594 , final_print_date
1595 , entity_name
1596 , entity_id
1597 , doc_sequence_category_id
1598 , created_by
1599 , creation_date
1600 , last_updated_by
1601 , last_update_date
1602 , last_update_login
1603 , program_application_id
1604 , program_id
1605 , program_update_date
1606 , request_id
1607 , attribute_category
1608 , attribute1
1609 , attribute2
1610 , attribute3
1611 , attribute4
1612 , attribute5
1613 , attribute6
1614 , attribute7
1615 , attribute8
1616 , attribute9
1617 , attribute10
1618 , attribute11
1619 , attribute12
1620 , attribute13
1621 , attribute14
1622 , attribute15
1623 )
1624 VALUES
1625 ( wsh_document_instances_s.nextval
1626 , p_document_type
1627 , l_document_number
1628 , l_status --Bug# 3789154
1629 , null
1630 , p_entity_name
1631 , p_entity_id
1632 , l_doc_sequence_category_id
1633 , fnd_global.user_id
1634 , sysdate
1635 , fnd_global.user_id
1636 , sysdate
1637 , fnd_global.login_id
1638 , null
1639 , null
1640 , null
1641 , null
1642 , null
1643 , null
1644 , null
1645 , null
1646 , null
1647 , null
1648 , null
1649 , null
1650 , null
1651 , null
1652 , null
1653 , null
1654 , null
1655 , null
1656 , null
1657 , null
1658 );
1659 END IF;--for l_entity_name <> 'WSH_TRIPS'
1660
1661 -- get message count and the message itself (if only one message)
1662 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1663 p_data => x_msg_data);
1664
1665 -- Standard check of p_commit.
1666 IF FND_API.to_boolean( p_commit ) THEN
1667 COMMIT WORK;
1668 END IF;
1669
1670
1671 --
1672 -- Debug Statements
1673 --
1674 IF l_debug_on THEN
1675 WSH_DEBUG_SV.pop(l_module_name);
1676 END IF;
1677 --
1678 EXCEPTION
1679
1680 WHEN lock_detected THEN --Bug 4284167 (FP Bug 4149501)
1681
1682 IF (get_lock_on_leg%ISOPEN) THEN
1683 CLOSE get_lock_on_leg;
1684 END IF;
1685 ROLLBACK to WSH_Document_PVT;
1686
1687 SELECT wnd.name INTO l_delivery_name
1688 FROM wsh_new_deliveries wnd, wsh_delivery_legs wdl
1689 WHERE wnd.delivery_id = wdl.delivery_id
1690 AND wdl.delivery_leg_id = l_delivery_leg_id;
1691
1692 FND_MESSAGE.SET_NAME('WSH',' WSH_DLVY_DEL_LEG_LOCK');
1693 FND_MESSAGE.SET_TOKEN('DEL_NAME',l_delivery_name);
1694 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
1695 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
1696
1697 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
1698 p_data => x_msg_data );
1699 IF l_debug_on THEN
1700 WSH_DEBUG_SV.logmsg(l_module_name,'Cannot lock delivery leg for update');
1701 END IF;
1702
1703 WHEN FND_API.g_exc_error THEN
1704 --Bug 4284167 (FP Bug 4149501)
1705 IF (get_lock_on_leg%ISOPEN) THEN
1706 CLOSE get_lock_on_leg;
1707 END IF;
1708
1709 ROLLBACK to WSH_Document_PVT;
1710 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
1711 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
1712 p_data => x_msg_data );
1713 --
1714 -- Debug Statements
1715 --
1716 IF l_debug_on THEN
1717 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1718 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1719 END IF;
1720 --
1721 WHEN FND_API.g_exc_unexpected_error THEN
1722 --Bug 4284167 (FP Bug 4149501)
1723 IF (get_lock_on_leg%ISOPEN) THEN
1724 CLOSE get_lock_on_leg;
1725 END IF;
1726
1727 ROLLBACK to WSH_Document_PVT;
1728 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
1729 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
1730 p_data => x_msg_data );
1731 --
1732 -- Debug Statements
1733 --
1734 IF l_debug_on THEN
1735 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1736 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1737 END IF;
1738 --
1739 WHEN others THEN
1740 --Bug 4284167 (FP Bug 4149501)
1741 IF (get_lock_on_leg%ISOPEN) THEN
1742 CLOSE get_lock_on_leg;
1743 END IF;
1744
1745 ROLLBACK to WSH_Document_PVT;
1746 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
1747 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
1748 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
1749 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
1750 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
1751 WSH_UTIL_CORE.add_message (x_return_status);
1752 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
1753 p_data => x_msg_data );
1754
1755 --
1756 -- Debug Statements
1757 --
1758 IF l_debug_on THEN
1759 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1760 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1761 END IF;
1762 --
1763 END Create_Document;
1764
1765
1766 -----------------------------------------------------------------------------
1767 -- PROCEDURE : Update_Document PUBLIC
1768 -- VERSION : 1.0
1769 -- COMMENT : Updates a document (pack slip, bill of lading) for a delivery
1770 --
1771 -- PARAMETER LIST :
1772 --
1773 -- IN
1774 --
1775 -- p_api_version known API version
1776 -- p_init_msg_list should API reset message stack (default: false)
1777 -- p_commit should API do a commit (default: false)
1778 -- p_validation_level extent of validation done in the API (not used)
1779 -- p_entity_name Entity for which the document is being updated
1780 -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
1781 -- p_entity_id Entity id that the document belongs to
1782 -- example: delivery_id, delivery_leg_id, etc
1783 -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
1784 -- p_pod_flag pod_flag for the document
1785 -- p_pod_by pod_by for the document
1786 -- p_pod_date pod_date for the document
1787 -- p_reason_of_transport reason of transport that describes the delivery
1788 -- p_description external aspect of the delivery
1789 -- p_cod_amount cod_amount of the document
1790 -- p_cod_currency_code cod_currency_code of the document
1791 -- p_cod_remit_to cod_remit_to of the document
1792 -- p_cod_charge_paid_by cod_charge_paid_by of the document
1793 -- p_problem_contact_reference problem_contact_referene of the document
1794 -- p_bill_freight_to bill_freight_to of the document
1795 -- p_carried_by carried_by of the document
1796 -- p_port_of_loading port_of_loading of the docucent
1797 -- p_port_of_discharge port_of_discharge of the document
1798 -- p_booking_office booking_office of the document
1799 -- p_booking_number booking_number of the document
1800 -- p_service_contract service_contract of the document
1801 -- p_shipper_export_ref shipper_export_ref of the document
1802 -- p_carrier_export_ref carrier_export_ref of the document
1803 -- p_bol_notify_party bol_notify_party of the document
1804 -- p_supplier_code supplier_code of the document
1805 -- p_aetc_number aetc_number of the document
1806 -- p_shipper_signed_by shipper_signed_by of the document
1807 -- p_shipper_date shipper_date of the document
1808 -- p_carrier_signed_by carrier_signed_by of the document
1809 -- p_carrier_date carrier_date of the document
1810 -- p_bol_issue_office bol_issue_office of the document
1811 -- p_bol_issued_by bol_issued_by of the document
1812 -- p_bol_date_issued bol_date_issued of the document
1813 -- p_shipper_hm_by shipper_bm_by of the document
1814 -- p_shipper_hm_date shipper_hm_date of the document
1815 -- p_carrier_hm_by carrier_hm_by of the document
1816 -- p_carrier_hm_date carrier_hm_date of the document
1817 -- p_ledger_id LEDGER id attached to the calling program (
1818 -- should be same as SOB used to setup the
1819 -- document category/assignment )
1820 -- p_consolidate_option calling program's choice to update document(s)
1821 -- for this parent delivery only ('CONSOLIDATE')
1822 -- or for child dels of this delivery ('SEPARATE')
1823 -- or both parent and child deliveries ('BOTH')
1824 --
1825 -- OUT
1826 --
1827 -- x_msg_count number of messages in stack
1828 -- x_msg_data message if there is only one message in stack
1829 -- x_return_status API return status ('S', 'E', 'U')
1830 --
1831 -- PRE-CONDITIONS : None
1832 -- POST-CONDITIONS : None
1833 -- EXCEPTIONS : None
1834 -----------------------------------------------------------------------------
1835
1836 PROCEDURE Update_Document
1837 ( p_api_version IN NUMBER
1838 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
1839 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
1840 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
1841 , x_return_status OUT NOCOPY VARCHAR2
1842 , x_msg_count OUT NOCOPY NUMBER
1843 , x_msg_data OUT NOCOPY VARCHAR2
1844 , p_entity_name IN VARCHAR2 DEFAULT NULL
1845 , p_entity_id IN NUMBER
1846 , p_document_type IN VARCHAR2
1847 , p_ledger_id IN NUMBER -- LE Uptake
1848 , p_consolidate_option IN VARCHAR2 DEFAULT 'BOTH'
1849 ) IS
1850 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Document';
1851 L_API_VERSION CONSTANT NUMBER := 1.0;
1852 l_delivery_id_tab delivery_id_tabtype := delivery_id_tabtype();
1853 l_delivery_id wsh_new_deliveries.delivery_id%type;
1854 l_delivery_leg_id wsh_delivery_legs.delivery_leg_id%type;
1855 l_table_count NUMBER;
1856 l_entity_name wsh_document_instances.entity_name%type;
1857
1858 CURSOR old_values_csr (c_entity_name IN VARCHAR2, c_entity_id IN NUMBER) IS
1859
1860 --Changed for BUG#3330869
1861 SELECT status
1862 --SELECT *
1863 FROM
1864 wsh_document_instances
1865 WHERE entity_name = c_entity_name
1866 AND entity_id = c_entity_id
1867 AND document_type = p_document_type
1868 AND status not in ('COMPLETE', 'CANCELLED')
1869 FOR UPDATE;
1870
1871 CURSOR trip_stop_csr IS
1872 SELECT
1873 delivery_id
1874 , pick_up_stop_id
1875 , drop_off_stop_id
1876 FROM
1877 wsh_delivery_legs
1878 WHERE delivery_leg_id = p_entity_id;
1879
1880 CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
1881 SELECT
1882 delivery_id
1883 FROM
1884 wsh_delivery_legs
1885 WHERE delivery_leg_id = c_delivery_leg_id;
1886
1887 old_values_rec old_values_csr%rowtype;
1888 trip_stop_rec trip_stop_csr%rowtype;
1889 delivery_id_rec delivery_id_csr%rowtype;
1890
1891 --
1892 l_debug_on BOOLEAN;
1893 --
1894 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DOCUMENT';
1895 --
1896 BEGIN
1897
1898 -- since this procedure does DML issue savepoint
1899
1900 --
1901 -- Debug Statements
1902 --
1903 --
1904 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1905 --
1906 IF l_debug_on IS NULL
1907 THEN
1908 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1909 END IF;
1910 --
1911 IF l_debug_on THEN
1912 WSH_DEBUG_SV.push(l_module_name);
1913 --
1914 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
1915 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
1916 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
1917 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
1918 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
1919 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
1920 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
1921 WSH_DEBUG_SV.log(l_module_name,'P_LEDGER_ID',P_LEDGER_ID); -- LE Uptake
1922 WSH_DEBUG_SV.log(l_module_name,'P_CONSOLIDATE_OPTION',P_CONSOLIDATE_OPTION);
1923 END IF;
1924 --
1925 SAVEPOINT WSH_Document_PVT;
1926
1927 -- standard call to check for call compatibility.
1928 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1929 p_api_version,
1930 l_api_name,
1931 g_pkg_name ) THEN
1932 RAISE FND_API.g_exc_unexpected_error;
1933 END IF;
1934
1935 -- initialize message list if p_init_msg_list is set to TRUE.
1936
1937 IF FND_API.to_boolean( p_init_msg_list ) THEN
1938 FND_MSG_PUB.initialize;
1939 END IF;
1940
1941 -- initialize API return status to success
1942 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
1943
1944 -------------------------------------------------------------
1945 -- Initialize the entity_name based on the document type --
1946 -- If the entity is Delivery leg, look up its delivery id --
1947 -- to be used to build the child delivery table later --
1948 -------------------------------------------------------------
1949
1950 l_entity_name := Init_Entity_Name (p_document_type, p_entity_name);
1951 IF l_entity_name = 'WSH_DELIVERY_LEGS'
1952 THEN
1953 OPEN delivery_id_csr (p_entity_id);
1954 FETCH delivery_id_csr INTO delivery_id_rec;
1955 l_delivery_id := delivery_id_rec.delivery_id;
1956 CLOSE delivery_id_csr;
1957 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
1958 THEN
1959 l_delivery_id := p_entity_id;
1960 END IF;
1961
1962 ----------------------------------------------
1963 -- if the document is for delivery leg --
1964 -- get its pick up and drop off trip stops --
1965 ----------------------------------------------
1966
1967 IF l_entity_name = 'WSH_DELIVERY_LEGS'
1968 THEN
1969 OPEN trip_stop_csr;
1970 FETCH trip_stop_csr INTO trip_stop_rec;
1971 CLOSE trip_stop_csr;
1972 END IF;
1973
1974 ---------------------------------------------------------------
1975 -- based on the consolidate_option identify the delivery ids --
1976 -- to make the document updates --
1977 ---------------------------------------------------------------
1978
1979 IF (p_consolidate_option IN ('BOTH', 'SEPARATE')) THEN
1980 GET_ChildDeliveryTab ( l_delivery_id
1981 , l_delivery_id_tab );
1982 END IF;
1983
1984 IF p_consolidate_option IN ('BOTH', 'CONSOLIDATE') THEN
1985 l_table_count := l_delivery_id_tab.count;
1986 l_delivery_id_tab.extend;
1987 l_delivery_id_tab(l_table_count+1) := l_delivery_id;
1988 END IF;
1989
1990 IF NOT l_delivery_id_tab.EXISTS(1) THEN
1991 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_INVALID_DELIVERY');
1992 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
1993 RAISE FND_API.G_EXC_ERROR;
1994 END IF;
1995
1996 FOR ctr IN 1..l_delivery_id_tab.count LOOP
1997
1998 -----------------------------------------------------------
1999 -- For each delivery, if the docuement relates to --
2000 -- delivery leg entity then identify the delivery leg id --
2001 -----------------------------------------------------------
2002
2003 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2004 THEN
2005 l_delivery_leg_id := Get_Delivery_Leg_Id ( l_delivery_id_tab(ctr)
2006 , trip_stop_rec.pick_up_stop_id
2007 , trip_stop_rec.drop_off_stop_id
2008 );
2009 END IF;
2010
2011 IF old_values_csr%ISOPEN
2012 THEN
2013 CLOSE old_values_csr;
2014 END IF;
2015 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2016 THEN
2017 OPEN old_values_csr (l_entity_name, l_delivery_leg_id);
2018 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
2019 THEN
2020 OPEN old_values_csr (l_entity_name, l_delivery_id_tab(ctr));
2021 END IF;
2022
2023 FETCH old_values_csr INTO old_values_rec;
2024 IF old_values_csr%NOTFOUND THEN
2025 CLOSE old_values_csr;
2026 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_MISSING');
2027 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
2028 RAISE FND_API.G_EXC_ERROR;
2029 END IF;
2030
2031 -- if the document is cancelled, raise error
2032 IF old_values_rec.status = 'CANCELLED' THEN
2033 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_INVALID_DELIVERY');
2034 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
2035 RAISE FND_API.G_EXC_ERROR;
2036 END IF;
2037
2038 -- need to change this update within loop to a bulk update later.
2039 -- probably use temporary tables with a join
2040
2041 UPDATE wsh_document_instances
2042 SET
2043 last_update_date = sysdate
2044 , last_updated_by = fnd_global.user_id
2045 , last_update_login = fnd_global.login_id
2046 WHERE CURRENT OF old_values_csr;
2047
2048 END LOOP;
2049 IF old_values_csr%ISOPEN
2050 THEN
2051 CLOSE old_values_csr;
2052 END IF;
2053
2054 -- get message count and the message itself (if only one message)
2055 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2056 p_data => x_msg_data);
2057 -- Standard check of p_commit.
2058 IF FND_API.to_boolean( p_commit ) THEN
2059 COMMIT WORK;
2060 END IF;
2061 --
2062 -- Debug Statements
2063 --
2064 IF l_debug_on THEN
2065 WSH_DEBUG_SV.pop(l_module_name);
2066 END IF;
2067 --
2068 EXCEPTION
2069
2070 WHEN FND_API.g_exc_error THEN
2071 ROLLBACK to WSH_Document_PVT;
2072 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
2073 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
2074 p_data => x_msg_data );
2075
2076 --
2077 -- Debug Statements
2078 --
2079 IF l_debug_on THEN
2080 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2081 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2082 END IF;
2083 --
2084 WHEN FND_API.g_exc_unexpected_error THEN
2085 ROLLBACK to WSH_Document_PVT;
2086 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
2087 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
2088 p_data => x_msg_data );
2089
2090 --
2091 -- Debug Statements
2092 --
2093 IF l_debug_on THEN
2094 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2095 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2096 END IF;
2097 --
2098 WHEN others THEN
2099 ROLLBACK to WSH_Document_PVT;
2100 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
2101 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
2102 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
2103 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
2104 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
2105 WSH_UTIL_CORE.add_message (x_return_status);
2106 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
2107 p_data => x_msg_data );
2108 --
2109 -- Debug Statements
2110 --
2111 IF l_debug_on THEN
2112 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2113 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2114 END IF;
2115 --
2116 END Update_Document;
2117
2118
2119 -----------------------------------------------------------------------------
2120 -- PROCEDURE : Cancel_Document PUBLIC
2121 -- VERSION : 1.0
2122 -- COMMENT : Updates the status of a document to 'CANCELLED'
2123 --
2124 -- PARAMETER LIST :
2125 --
2126 -- IN
2127 --
2128 -- p_api_version known API version
2129 -- p_init_msg_list should API reset message stack (default: false)
2130 -- p_commit should API do a commit (default: false)
2131 -- p_validation_level extent of validation done in the API (not used)
2132 -- p_entity_name Entity for which the document is being cancelled
2133 -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
2134 -- p_entity_id Entity id that the document belongs to
2135 -- example: delivery_id, delivery_leg_id, etc
2136 -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
2137 -- p_consolidate_option calling program's choice to cancel document(s)
2138 -- for this parent delivery only ('CONSOLIDATE')
2139 -- or for child dels of this delivery ('SEPARATE')
2140 -- or both parent and child deliveries ('BOTH')
2141 --
2142 -- OUT
2143 --
2144 -- x_msg_count number of messages in stack
2145 -- x_msg_data message if there is only one message in stack
2146 -- x_return_status API return status ('S', 'E', 'U')
2147 --
2148 -- PRE-CONDITIONS : None
2149 -- POST-CONDITIONS : None
2150 -- EXCEPTIONS : None
2151 -----------------------------------------------------------------------------
2152
2153
2154 PROCEDURE Cancel_Document
2155 ( p_api_version IN NUMBER
2156 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
2157 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
2158 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
2159 , x_return_status OUT NOCOPY VARCHAR2
2160 , x_msg_count OUT NOCOPY NUMBER
2161 , x_msg_data OUT NOCOPY VARCHAR2
2162 , p_entity_name IN VARCHAR2 DEFAULT NULL
2163 , p_entity_id IN NUMBER
2164 , p_document_type IN VARCHAR2
2165 , p_consolidate_option IN VARCHAR2 DEFAULT 'BOTH'
2166 )
2167 IS
2168 L_API_NAME CONSTANT VARCHAR2(30) := 'WSH_Document_PVT';
2169 L_API_VERSION CONSTANT NUMBER := 1.0;
2170 l_delivery_id_tab delivery_id_tabtype := delivery_id_tabtype();
2171 l_table_count NUMBER;
2172 l_entity_name wsh_document_instances.entity_name%type;
2173 l_delivery_id wsh_new_deliveries.delivery_id%type;
2174 l_delivery_leg_id wsh_delivery_legs.delivery_leg_id%type;
2175
2176
2177 -------------------------------------------------------------------------
2178 -- cursor to fetch the current document (in PLAN/OPEN status) of --
2179 -- the entity. Assumes to get only one row because the delivery UI --
2180 -- currently enforces it (for both BOL and Pack Slips). If there is --
2181 -- a change to this behavior the cursor definition would need change --
2182 -------------------------------------------------------------------------
2183
2184 CURSOR status_csr (c_entity_name IN VARCHAR2, c_entity_id IN NUMBER) IS
2185 SELECT
2186 entity_id
2187 , status
2188 FROM
2189 wsh_document_instances
2190 WHERE entity_name = c_entity_name
2191 AND entity_id = c_entity_id
2192 AND document_type = p_document_type
2193 AND status in ('OPEN', 'PLANNED')
2194 FOR UPDATE;
2195
2196 CURSOR trip_stop_csr IS
2197 SELECT
2198 delivery_id
2199 , pick_up_stop_id
2200 , drop_off_stop_id
2201 FROM
2202 wsh_delivery_legs
2203 WHERE delivery_leg_id = p_entity_id;
2204
2205 CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
2206 SELECT
2207 delivery_id
2208 FROM
2209 wsh_delivery_legs
2210 WHERE delivery_leg_id = c_delivery_leg_id;
2211
2212 trip_stop_rec trip_stop_csr%rowtype;
2213 delivery_id_rec delivery_id_csr%rowtype;
2214 status_rec status_csr%rowtype;
2215
2216 --
2217 l_debug_on BOOLEAN;
2218 --
2219 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CANCEL_DOCUMENT';
2220 --
2221 BEGIN
2222 -- since this procedure does DML issue savepoint
2223 --
2224 -- Debug Statements
2225 --
2226 --
2227 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2228 --
2229 IF l_debug_on IS NULL
2230 THEN
2231 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2232 END IF;
2233 --
2234 IF l_debug_on THEN
2235 WSH_DEBUG_SV.push(l_module_name);
2236 --
2237 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
2238 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
2239 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
2240 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
2241 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
2242 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
2243 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
2244 WSH_DEBUG_SV.log(l_module_name,'P_CONSOLIDATE_OPTION',P_CONSOLIDATE_OPTION);
2245 END IF;
2246 --
2247 SAVEPOINT WSH_Document_PVT;
2248
2249 -- standard call to check for call compatibility.
2250 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2251 p_api_version,
2252 l_api_name,
2253 g_pkg_name ) THEN
2254 RAISE FND_API.g_exc_unexpected_error;
2255 END IF;
2256
2257 -- initialize message list if p_init_msg_list is set to TRUE.
2258
2259 IF FND_API.to_boolean( p_init_msg_list ) THEN
2260 FND_MSG_PUB.initialize;
2261 END IF;
2262
2263 -- initialize API return status to success
2264 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
2265
2266 -------------------------------------------------------------
2267 -- Initialize the entity_name based on the document type --
2268 -- If the entity is Delivery leg, look up its delivery id --
2269 -- to be used to build the child delivery table later --
2270 -------------------------------------------------------------
2271
2272 l_entity_name := Init_Entity_Name (p_document_type, p_entity_name);
2273
2274 IF l_entity_name <> 'WSH_TRIPS' THEN
2275
2276 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2277 THEN
2278 OPEN delivery_id_csr (p_entity_id);
2279 FETCH delivery_id_csr INTO delivery_id_rec;
2280 l_delivery_id := delivery_id_rec.delivery_id;
2281 CLOSE delivery_id_csr;
2282 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
2283 THEN
2284 l_delivery_id := p_entity_id;
2285 END IF;
2286
2287 ----------------------------------------------
2288 -- if the document is for delivery leg --
2289 -- get its pick up and drop off trip stops --
2290 ----------------------------------------------
2291
2292 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2293 THEN
2294 OPEN trip_stop_csr;
2295 FETCH trip_stop_csr INTO trip_stop_rec;
2296 CLOSE trip_stop_csr;
2297 END IF;
2298
2299 ---------------------------------------------------------------
2300 -- based on the consolidate_option identify the delivery ids --
2301 -- to cancel documents for --
2302 ---------------------------------------------------------------
2303
2304 IF (p_consolidate_option IN ('BOTH', 'SEPARATE')) THEN
2305 GET_ChildDeliveryTab ( l_delivery_id
2306 , l_delivery_id_tab );
2307 END IF;
2308
2309 IF p_consolidate_option IN ('BOTH', 'CONSOLIDATE') THEN
2310 l_table_count := l_delivery_id_tab.count;
2311 l_delivery_id_tab.extend;
2312 l_delivery_id_tab(l_table_count+1) := l_delivery_id;
2313 END IF;
2314
2315 IF NOT l_delivery_id_tab.EXISTS(1) THEN
2316 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_INVALID_DELIVERY');
2317 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
2318 RAISE FND_API.G_EXC_ERROR;
2319 END IF;
2320
2321 FOR ctr IN 1..l_delivery_id_tab.count LOOP
2322
2323 -----------------------------------------------------------
2324 -- For each delivery, if the docuement relates to --
2325 -- delivery leg entity then identify the delivery leg id --
2326 -----------------------------------------------------------
2327
2328 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2329 THEN
2330 l_delivery_leg_id := Get_Delivery_Leg_Id ( l_delivery_id_tab(ctr)
2331 , trip_stop_rec.pick_up_stop_id
2332 , trip_stop_rec.drop_off_stop_id
2333 );
2334 END IF;
2335
2336 IF status_csr%ISOPEN
2337 THEN
2338 CLOSE status_csr;
2339 END IF;
2340 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2341 THEN
2342 OPEN status_csr (l_entity_name, l_delivery_leg_id);
2343 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
2344 THEN
2345 OPEN status_csr (l_entity_name, l_delivery_id_tab(ctr));
2346 END IF;
2347
2348 FETCH status_csr INTO status_rec;
2349 IF status_csr%NOTFOUND THEN
2350 CLOSE status_csr;
2351 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_MISSING');
2352 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
2353 RAISE FND_API.G_EXC_ERROR;
2354 END IF;
2355
2356 UPDATE wsh_document_instances
2357 SET status = 'CANCELLED'
2358 , last_update_date = sysdate
2359 , last_updated_by = fnd_global.user_id
2360 , last_update_login = fnd_global.login_id
2361 WHERE CURRENT OF status_csr;
2362
2363 END LOOP;
2364 IF status_csr%ISOPEN
2365 THEN
2366 CLOSE status_csr;
2367 END IF;
2368
2369 ELSIF l_entity_name = 'WSH_TRIPS' THEN
2370
2371 UPDATE wsh_document_instances
2372 SET status = 'CANCELLED'
2373 , last_update_date = sysdate
2374 , last_updated_by = fnd_global.user_id
2375 , last_update_login = fnd_global.login_id
2376 WHERE entity_name = l_entity_name
2377 AND entity_id = p_entity_id
2378 AND document_type = p_document_type
2379 AND status in ('OPEN', 'PLANNED');
2380
2381 END IF;
2382 -- get message count and the message itself (if only one message)
2383 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2384 p_data => x_msg_data);
2385 -- Standard check of p_commit.
2386 IF FND_API.to_boolean( p_commit ) THEN
2387 COMMIT WORK;
2388 END IF;
2389 --
2390 -- Debug Statements
2391 --
2392 IF l_debug_on THEN
2393 WSH_DEBUG_SV.pop(l_module_name);
2394 END IF;
2395 --
2396 EXCEPTION
2397
2398 WHEN FND_API.g_exc_error THEN
2399 ROLLBACK to WSH_Document_PVT;
2400 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
2401 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
2402 p_data => x_msg_data );
2403
2404 --
2405 -- Debug Statements
2406 --
2407 IF l_debug_on THEN
2408 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2409 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2410 END IF;
2411 --
2412 WHEN FND_API.g_exc_unexpected_error THEN
2413 ROLLBACK to WSH_Document_PVT;
2414 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
2415 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
2416 p_data => x_msg_data );
2417
2418 --
2419 -- Debug Statements
2420 --
2421 IF l_debug_on THEN
2422 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2423 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2424 END IF;
2425 --
2426 WHEN others THEN
2427 ROLLBACK to WSH_Document_PVT;
2428 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
2429 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
2430 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
2431 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
2432 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
2433 WSH_UTIL_CORE.add_message (x_return_status);
2434 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
2435 p_data => x_msg_data );
2436 --
2437 -- Debug Statements
2438 --
2439 IF l_debug_on THEN
2440 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2441 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2442 END IF;
2443 --
2444 END Cancel_Document;
2445
2446 -----------------------------------------------------------------------------
2447 -- PROCEDURE : Open_Document PUBLIC
2448 -- VERSION : 1.0
2449 -- COMMENT : Updates the status of a document to 'OPEN'
2450 --
2451 -- PARAMETER LIST :
2452 --
2453 -- IN
2454 --
2455 -- p_api_version known API version
2456 -- p_init_msg_list should API reset message stack (default: false)
2457 -- p_commit should API do a commit (default: false)
2458 -- p_validation_level extent of validation done in the API (not used)
2459 -- p_entity_name Entity for which the document is being opened
2460 -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
2461 -- p_entity_id Entity id that the document belongs to
2462 -- example: delivery_id, delivery_leg_id, etc
2463 -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
2464 -- p_consolidate_option calling program's choice to open document(s)
2465 -- for this parent delivery only ('CONSOLIDATE')
2466 -- or for child dels of this delivery ('SEPARATE')
2467 -- or both parent and child deliveries ('BOTH')
2468 --
2469 -- OUT
2470 --
2471 -- x_msg_count number of messages in stack
2472 -- x_msg_data message if there is only one message in stack
2473 -- x_return_status API return status ('S', 'E', 'U')
2474 --
2475 -- PRE-CONDITIONS : None
2476 -- POST-CONDITIONS : None
2477 -- EXCEPTIONS : None
2478 -----------------------------------------------------------------------------
2479
2480
2481 PROCEDURE Open_Document
2482 ( p_api_version IN NUMBER
2483 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
2484 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
2485 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
2486 , x_return_status OUT NOCOPY VARCHAR2
2487 , x_msg_count OUT NOCOPY NUMBER
2488 , x_msg_data OUT NOCOPY VARCHAR2
2489 , p_entity_name IN VARCHAR2 DEFAULT NULL
2490 , p_entity_id IN NUMBER
2491 , p_document_type IN VARCHAR2
2492 , p_consolidate_option IN VARCHAR2 DEFAULT 'BOTH'
2493 )
2494 IS
2495 L_API_NAME CONSTANT VARCHAR2(30) := 'WSH_Document_PVT';
2496 L_API_VERSION CONSTANT NUMBER := 1.0;
2497 l_delivery_id_tab delivery_id_tabtype := delivery_id_tabtype();
2498 l_table_count NUMBER;
2499 l_entity_name wsh_document_instances.entity_name%type;
2500 l_delivery_id wsh_new_deliveries.delivery_id%type;
2501 l_delivery_leg_id wsh_delivery_legs.delivery_leg_id%type;
2502
2503 CURSOR status_csr (c_entity_name IN VARCHAR2, c_entity_id IN NUMBER) IS
2504 SELECT
2505 entity_id
2506 , status
2507 FROM
2508 wsh_document_instances
2509 WHERE entity_name = c_entity_name
2510 AND entity_id = c_entity_id
2511 AND document_type = p_document_type
2512 AND status = 'PLANNED'
2513 FOR UPDATE;
2514
2515 CURSOR trip_stop_csr IS
2516 SELECT
2517 delivery_id
2518 , pick_up_stop_id
2519 , drop_off_stop_id
2520 FROM
2521 wsh_delivery_legs
2522 WHERE delivery_leg_id = p_entity_id;
2523
2524 CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
2525 SELECT
2526 delivery_id
2527 FROM
2528 wsh_delivery_legs
2529 WHERE delivery_leg_id = c_delivery_leg_id;
2530
2531 trip_stop_rec trip_stop_csr%rowtype;
2532 delivery_id_rec delivery_id_csr%rowtype;
2533 status_rec status_csr%rowtype;
2534
2535 --
2536 l_debug_on BOOLEAN;
2537 --
2538 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'OPEN_DOCUMENT';
2539 --
2540 BEGIN
2541 -- since this procedure does DML issue savepoint
2542 --
2543 -- Debug Statements
2544 --
2545 --
2546 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2547 --
2548 IF l_debug_on IS NULL
2549 THEN
2550 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2551 END IF;
2552 --
2553 IF l_debug_on THEN
2554 WSH_DEBUG_SV.push(l_module_name);
2555 --
2556 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
2557 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
2558 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
2559 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
2560 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
2561 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
2562 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
2563 WSH_DEBUG_SV.log(l_module_name,'P_CONSOLIDATE_OPTION',P_CONSOLIDATE_OPTION);
2564 END IF;
2565 --
2566 SAVEPOINT WSH_Document_PVT;
2567
2568 -- standard call to check for call compatibility.
2569 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2570 p_api_version,
2571 l_api_name,
2572 g_pkg_name ) THEN
2573 RAISE FND_API.g_exc_unexpected_error;
2574 END IF;
2575
2576 -- initialize message list if p_init_msg_list is set to TRUE.
2577
2578 IF FND_API.to_boolean( p_init_msg_list ) THEN
2579 FND_MSG_PUB.initialize;
2580 END IF;
2581
2582 -- initialize API return status to success
2583 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
2584
2585 -------------------------------------------------------------
2586 -- Initialize the entity_name based on the document type --
2587 -- If the entity is Delivery leg, look up its delivery id --
2588 -- to be used to build the child delivery table later --
2589 -------------------------------------------------------------
2590
2591 l_entity_name := Init_Entity_Name (p_document_type, p_entity_name);
2592 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2593 THEN
2594 OPEN delivery_id_csr (p_entity_id);
2595 FETCH delivery_id_csr INTO delivery_id_rec;
2596 l_delivery_id := delivery_id_rec.delivery_id;
2597 CLOSE delivery_id_csr;
2598 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
2599 THEN
2600 l_delivery_id := p_entity_id;
2601 END IF;
2602
2603 ----------------------------------------------
2604 -- if the document is for delivery leg --
2605 -- get its pick up and drop off trip stops --
2606 ----------------------------------------------
2607
2608 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2609 THEN
2610 OPEN trip_stop_csr;
2611 FETCH trip_stop_csr INTO trip_stop_rec;
2612 CLOSE trip_stop_csr;
2613 END IF;
2614
2615 ---------------------------------------------------------------
2616 -- based on the consolidate_option identify the delivery ids --
2617 -- to cancel documents for --
2618 ---------------------------------------------------------------
2619
2620 IF (p_consolidate_option IN ('BOTH', 'SEPARATE')) THEN
2621 GET_ChildDeliveryTab ( l_delivery_id
2622 , l_delivery_id_tab );
2623 END IF;
2624
2625 IF p_consolidate_option IN ('BOTH', 'CONSOLIDATE') THEN
2626 l_table_count := l_delivery_id_tab.count;
2627 l_delivery_id_tab.extend;
2628 l_delivery_id_tab(l_table_count+1) := l_delivery_id;
2629 END IF;
2630
2631 IF NOT l_delivery_id_tab.EXISTS(1) THEN
2632 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_INVALID_DELIVERY');
2633 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
2634 RAISE FND_API.G_EXC_ERROR;
2635 END IF;
2636
2637 FOR ctr IN 1..l_delivery_id_tab.count LOOP
2638
2639 -----------------------------------------------------------
2640 -- For each delivery, if the docuement relates to --
2641 -- delivery leg entity then identify the delivery leg id --
2642 -----------------------------------------------------------
2643
2644 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2645 THEN
2646 l_delivery_leg_id := Get_Delivery_Leg_Id ( l_delivery_id_tab(ctr)
2647 , trip_stop_rec.pick_up_stop_id
2648 , trip_stop_rec.drop_off_stop_id
2649 );
2650 END IF;
2651
2652 IF status_csr%ISOPEN
2653 THEN
2654 CLOSE status_csr;
2655 END IF;
2656 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2657 THEN
2658 OPEN status_csr (l_entity_name, l_delivery_leg_id);
2659 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
2660 THEN
2661 OPEN status_csr (l_entity_name, l_delivery_id_tab(ctr));
2662 END IF;
2663
2664 FETCH status_csr INTO status_rec;
2665 IF status_csr%NOTFOUND THEN
2666 CLOSE status_csr;
2667 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_MISSING');
2668 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
2669 RAISE FND_API.G_EXC_ERROR;
2670 END IF;
2671
2672 UPDATE wsh_document_instances
2673 SET status = 'OPEN'
2674 , last_update_date = sysdate
2675 , last_updated_by = fnd_global.user_id
2676 , last_update_login = fnd_global.login_id
2677 WHERE CURRENT OF status_csr;
2678
2679 END LOOP;
2680 IF status_csr%ISOPEN
2681 THEN
2682 CLOSE status_csr;
2683 END IF;
2684
2685 -- get message count and the message itself (if only one message)
2686 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2687 p_data => x_msg_data);
2688 -- Standard check of p_commit.
2689 IF FND_API.to_boolean( p_commit ) THEN
2690 COMMIT WORK;
2691 END IF;
2692 --
2693 -- Debug Statements
2694 --
2695 IF l_debug_on THEN
2696 WSH_DEBUG_SV.pop(l_module_name);
2697 END IF;
2698 --
2699 EXCEPTION
2700
2701 WHEN FND_API.g_exc_error THEN
2702 ROLLBACK to WSH_Document_PVT;
2703 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
2704 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
2705 p_data => x_msg_data );
2706
2707 --
2708 -- Debug Statements
2709 --
2710 IF l_debug_on THEN
2711 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2712 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2713 END IF;
2714 --
2715 WHEN FND_API.g_exc_unexpected_error THEN
2716 ROLLBACK to WSH_Document_PVT;
2717 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
2718 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
2719 p_data => x_msg_data );
2720
2721 --
2722 -- Debug Statements
2723 --
2724 IF l_debug_on THEN
2725 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2726 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2727 END IF;
2728 --
2729 WHEN others THEN
2730 ROLLBACK to WSH_Document_PVT;
2731 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
2732 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
2733 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
2734 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
2735 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
2736 WSH_UTIL_CORE.add_message (x_return_status);
2737 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
2738 p_data => x_msg_data );
2739 --
2740 -- Debug Statements
2741 --
2742 IF l_debug_on THEN
2743 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2744 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2745 END IF;
2746 --
2747 END Open_Document;
2748
2749 -----------------------------------------------------------------------------
2750 -- PROCEDURE : Complete_Document PUBLIC
2751 -- VERSION : 1.0
2752 -- COMMENT : Updates the status of a document to 'COMPLETE'
2753 --
2754 -- PARAMETER LIST :
2755 --
2756 -- IN
2757 --
2758 -- p_api_version known API version
2759 -- p_init_msg_list should API reset message stack (default: false)
2760 -- p_commit should API do a commit (default: false)
2761 -- p_validation_level extent of validation done in the API (not used)
2762 -- p_entity_name Entity for which the document is being completed
2763 -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
2764 -- p_entity_id Entity id that the document belongs to
2765 -- example: delivery_id, delivery_leg_id, etc
2766 -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
2767 -- p_consolidate_option calling program's choice to complete document(s)
2768 -- for this parent delivery only ('CONSOLIDATE')
2769 -- or for child dels of this delivery ('SEPARATE')
2770 -- or both parent and child deliveries ('BOTH')
2771 --
2772 -- OUT
2773 --
2774 -- x_msg_count number of messages in stack
2775 -- x_msg_data message if there is only one message in stack
2776 -- x_return_status API return status ('S', 'E', 'U')
2777 --
2778 -- PRE-CONDITIONS : None
2779 -- POST-CONDITIONS : None
2780 -- EXCEPTIONS : None
2781 -----------------------------------------------------------------------------
2782
2783
2784
2785 PROCEDURE Complete_Document
2786 ( p_api_version IN NUMBER
2787 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
2788 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
2789 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
2790 , x_return_status OUT NOCOPY VARCHAR2
2791 , x_msg_count OUT NOCOPY NUMBER
2792 , x_msg_data OUT NOCOPY VARCHAR2
2793 , p_entity_name IN VARCHAR2 DEFAULT NULL
2794 , p_entity_id IN NUMBER
2795 , p_document_type IN VARCHAR2
2796 , p_consolidate_option IN VARCHAR2 DEFAULT 'BOTH'
2797 )
2798 IS
2799 L_API_NAME CONSTANT VARCHAR2(30) := 'WSH_Document_PVT';
2800 L_API_VERSION CONSTANT NUMBER := 1.0;
2801 l_delivery_id_tab delivery_id_tabtype := delivery_id_tabtype();
2802 l_table_count NUMBER;
2803 l_entity_name wsh_document_instances.entity_name%type;
2804 l_delivery_id wsh_new_deliveries.delivery_id%type;
2805 l_delivery_leg_id wsh_delivery_legs.delivery_leg_id%type;
2806
2807 CURSOR status_csr (c_entity_name IN VARCHAR2, c_entity_id IN NUMBER) IS
2808 SELECT
2809 entity_id
2810 , status
2811 FROM
2812 wsh_document_instances
2813 WHERE entity_name = c_entity_name
2814 AND entity_id = c_entity_id
2815 AND document_type = p_document_type
2816 AND status not in ('CANCELLED')
2817 FOR UPDATE;
2818
2819 CURSOR trip_stop_csr IS
2820 SELECT
2821 delivery_id
2822 , pick_up_stop_id
2823 , drop_off_stop_id
2824 FROM
2825 wsh_delivery_legs
2826 WHERE delivery_leg_id = p_entity_id;
2827
2828 CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
2829 SELECT
2830 delivery_id
2831 FROM
2832 wsh_delivery_legs
2833 WHERE delivery_leg_id = c_delivery_leg_id;
2834
2835 trip_stop_rec trip_stop_csr%rowtype;
2836 delivery_id_rec delivery_id_csr%rowtype;
2837 status_rec status_csr%rowtype;
2838
2839 --
2840 l_debug_on BOOLEAN;
2841 --
2842 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'COMPLETE_DOCUMENT';
2843 --
2844 BEGIN
2845 -- since this procedure does DML issue savepoint
2846 --
2847 -- Debug Statements
2848 --
2849 --
2850 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2851 --
2852 IF l_debug_on IS NULL
2853 THEN
2854 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2855 END IF;
2856 --
2857 IF l_debug_on THEN
2858 WSH_DEBUG_SV.push(l_module_name);
2859 --
2860 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
2861 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
2862 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
2863 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
2864 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
2865 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
2866 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
2867 WSH_DEBUG_SV.log(l_module_name,'P_CONSOLIDATE_OPTION',P_CONSOLIDATE_OPTION);
2868 END IF;
2869 --
2870 SAVEPOINT WSH_Document_PVT;
2871
2872 -- standard call to check for call compatibility.
2873 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2874 p_api_version,
2875 l_api_name,
2876 g_pkg_name ) THEN
2877 RAISE FND_API.g_exc_unexpected_error;
2878 END IF;
2879
2880 -- initialize message list if p_init_msg_list is set to TRUE.
2881
2882 IF FND_API.to_boolean( p_init_msg_list ) THEN
2883 FND_MSG_PUB.initialize;
2884 END IF;
2885
2886 -- initialize API return status to success
2887 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
2888
2889 -------------------------------------------------------------
2890 -- Initialize the entity_name based on the document type --
2891 -- If the entity is Delivery leg, look up its delivery id --
2892 -- to be used to build the child delivery table later --
2893 -------------------------------------------------------------
2894
2895 l_entity_name := Init_Entity_Name (p_document_type, p_entity_name);
2896 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2897 THEN
2898 OPEN delivery_id_csr (p_entity_id);
2899 FETCH delivery_id_csr INTO delivery_id_rec;
2900 l_delivery_id := delivery_id_rec.delivery_id;
2901 CLOSE delivery_id_csr;
2902 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
2903 THEN
2904 l_delivery_id := p_entity_id;
2905 END IF;
2906
2907 ----------------------------------------------
2908 -- if the document is for delivery leg --
2909 -- get its pick up and drop off trip stops --
2910 ----------------------------------------------
2911
2912 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2913 THEN
2914 OPEN trip_stop_csr;
2915 FETCH trip_stop_csr INTO trip_stop_rec;
2916 CLOSE trip_stop_csr;
2917 END IF;
2918
2919 ---------------------------------------------------------------
2920 -- based on the consolidate_option identify the delivery ids --
2921 -- to cancel documents for --
2922 ---------------------------------------------------------------
2923
2924 IF (p_consolidate_option IN ('BOTH', 'SEPARATE')) THEN
2925 GET_ChildDeliveryTab ( l_delivery_id
2926 , l_delivery_id_tab );
2927 END IF;
2928
2929 IF p_consolidate_option IN ('BOTH', 'CONSOLIDATE') THEN
2930 l_table_count := l_delivery_id_tab.count;
2931 l_delivery_id_tab.extend;
2932 l_delivery_id_tab(l_table_count+1) := l_delivery_id;
2933 END IF;
2934
2935 IF NOT l_delivery_id_tab.EXISTS(1) THEN
2936 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_INVALID_DELIVERY');
2937 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
2938 RAISE FND_API.G_EXC_ERROR;
2939 END IF;
2940
2941 FOR ctr IN 1..l_delivery_id_tab.count LOOP
2942
2943 -----------------------------------------------------------
2944 -- For each delivery, if the docuement relates to --
2945 -- delivery leg entity then identify the delivery leg id --
2946 -----------------------------------------------------------
2947
2948 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2949 THEN
2950 l_delivery_leg_id := Get_Delivery_Leg_Id ( l_delivery_id_tab(ctr)
2951 , trip_stop_rec.pick_up_stop_id
2952 , trip_stop_rec.drop_off_stop_id
2953 );
2954 END IF;
2955
2956 IF status_csr%ISOPEN
2957 THEN
2958 CLOSE status_csr;
2959 END IF;
2960 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2961 THEN
2962 OPEN status_csr (l_entity_name, l_delivery_leg_id);
2963 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
2964 THEN
2965 OPEN status_csr (l_entity_name, l_delivery_id_tab(ctr));
2966 END IF;
2967
2968 FETCH status_csr INTO status_rec;
2969 IF status_csr%NOTFOUND THEN
2970 CLOSE status_csr;
2971 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_MISSING');
2972 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
2973 RAISE FND_API.G_EXC_ERROR;
2974 END IF;
2975
2976 UPDATE wsh_document_instances
2977 SET status = 'COMPLETE'
2978 , last_update_date = sysdate
2979 , last_updated_by = fnd_global.user_id
2980 , last_update_login = fnd_global.login_id
2981 WHERE CURRENT OF status_csr;
2982
2983 END LOOP;
2984 IF status_csr%ISOPEN
2985 THEN
2986 CLOSE status_csr;
2987 END IF;
2988
2989 -- get message count and the message itself (if only one message)
2990 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2991 p_data => x_msg_data);
2992 -- Standard check of p_commit.
2993 IF FND_API.to_boolean( p_commit ) THEN
2994 COMMIT WORK;
2995 END IF;
2996 --
2997 -- Debug Statements
2998 --
2999 IF l_debug_on THEN
3000 WSH_DEBUG_SV.pop(l_module_name);
3001 END IF;
3002 --
3003 EXCEPTION
3004
3005 WHEN FND_API.g_exc_error THEN
3006 ROLLBACK to WSH_Document_PVT;
3007 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
3008 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3009 p_data => x_msg_data );
3010
3011 --
3012 -- Debug Statements
3013 --
3014 IF l_debug_on THEN
3015 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3016 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3017 END IF;
3018 --
3019 WHEN FND_API.g_exc_unexpected_error THEN
3020 ROLLBACK to WSH_Document_PVT;
3021 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3022 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3023 p_data => x_msg_data );
3024
3025 --
3026 -- Debug Statements
3027 --
3028 IF l_debug_on THEN
3029 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3030 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3031 END IF;
3032 --
3033 WHEN others THEN
3034 ROLLBACK to WSH_Document_PVT;
3035 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
3036 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
3037 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
3038 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
3039 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3040 WSH_UTIL_CORE.add_message (x_return_status);
3041 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3042 p_data => x_msg_data );
3043 --
3044 -- Debug Statements
3045 --
3046 IF l_debug_on THEN
3047 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3048 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3049 END IF;
3050 --
3051 END Complete_Document;
3052
3053
3054 ------------------------------------------------------------------------------
3055 -- PROCEDURE : Print_Document PUBLIC
3056 -- VERSION : 1.0
3057 -- COMMENT : Submit the report WSHRDPAK.rdf to print the packing slip.
3058 --
3059 -- PARAMETER LIST :
3060 --
3061 -- IN
3062 --
3063 -- p_api_version known API version
3064 -- p_init_msg_list should API reset message stack (default: false)
3065 -- p_commit should API do a commit (default: false)
3066 -- p_validation_level extent of validation done in the API (not used)
3067 -- p_delivery_id delivery id for which document is being printed
3068 -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
3069 -- p_departure_date_lo delivery date (low)
3070 -- p_departure_date_hi delivery date (high)
3071 -- p_item_display display FLEX, DESC or BOTH (default BOTH)
3072 -- p_print_cust_item print customer item information or not (default
3073 -- NO)
3074 -- p_print_mode print FINAL or DRAFT (default DRAFT)
3075 -- p_print_all calling program's choice to cancel document(s)
3076 -- for this parent delivery only ('CONSOLIDATE')
3077 -- or for child dels of this delivery ('SEPARATE')
3078 -- or both parent and child deliveries ('BOTH')
3079 -- p_sort sort the report by customer item or inventory
3080 -- item (INV or CUST, default INV)
3081 -- p_freight_carrier carrier_id of the freight carrier
3082 -- p_warehouse_id current organization_id
3083 --
3084 -- OUT
3085 --
3086 -- x_msg_count number of messages in stack
3087 -- x_msg_data message if there is only one message in stack
3088 -- x_return_status API return status ('S', 'E', 'U')
3089 --
3090 -- PRE-CONDITIONS : None
3091 -- POST-CONDITIONS : None
3092 -- EXCEPTIONS : None
3093 ------------------------------------------------------------------------------
3094
3095 PROCEDURE Print_Document
3096 ( p_api_version IN NUMBER
3097 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
3098 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
3099 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
3100 , x_return_status OUT NOCOPY VARCHAR2
3101 , x_msg_count OUT NOCOPY NUMBER
3102 , x_msg_data OUT NOCOPY VARCHAR2
3103 , p_delivery_id IN NUMBER
3104 , p_document_type IN VARCHAR2
3105 , p_departure_date_lo IN DATE DEFAULT NULL
3106 , p_departure_date_hi IN DATE DEFAULT NULL
3107 , p_item_display IN VARCHAR2 DEFAULT 'D'
3108 , p_print_cust_item IN VARCHAR2 DEFAULT 'N'
3109 , p_print_mode IN VARCHAR2 DEFAULT 'DRAFT'
3110 , p_print_all IN VARCHAR2 DEFAULT 'BOTH'
3111 , p_sort IN VARCHAR2 DEFAULT 'INV'
3112 , p_freight_carrier IN VARCHAR2 DEFAULT NULL
3113 , p_warehouse_id IN NUMBER
3114 , x_conc_request_id OUT NOCOPY NUMBER
3115 )
3116 IS
3117 l_api_name CONSTANT VARCHAR2(30) := 'Print_Document';
3118 l_api_version CONSTANT NUMBER := 1.0;
3119 l_conc_request_id NUMBER;
3120
3121 --
3122 l_debug_on BOOLEAN;
3123 --
3124 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PRINT_DOCUMENT';
3125 --
3126 BEGIN
3127 -- standard call to check for call compatibility.
3128 --
3129 -- Debug Statements
3130 --
3131 --
3132 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3133 --
3134 IF l_debug_on IS NULL
3135 THEN
3136 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3137 END IF;
3138 --
3139 IF l_debug_on THEN
3140 WSH_DEBUG_SV.push(l_module_name);
3141 --
3142 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
3143 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
3144 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
3145 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
3146 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
3147 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
3148 WSH_DEBUG_SV.log(l_module_name,'P_DEPARTURE_DATE_LO',P_DEPARTURE_DATE_LO);
3149 WSH_DEBUG_SV.log(l_module_name,'P_DEPARTURE_DATE_HI',P_DEPARTURE_DATE_HI);
3150 WSH_DEBUG_SV.log(l_module_name,'P_ITEM_DISPLAY',P_ITEM_DISPLAY);
3151 WSH_DEBUG_SV.log(l_module_name,'P_PRINT_CUST_ITEM',P_PRINT_CUST_ITEM);
3152 WSH_DEBUG_SV.log(l_module_name,'P_PRINT_MODE',P_PRINT_MODE);
3153 WSH_DEBUG_SV.log(l_module_name,'P_PRINT_ALL',P_PRINT_ALL);
3154 WSH_DEBUG_SV.log(l_module_name,'P_SORT',P_SORT);
3155 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_CARRIER',P_FREIGHT_CARRIER);
3156 WSH_DEBUG_SV.log(l_module_name,'P_WAREHOUSE_ID',P_WAREHOUSE_ID);
3157 END IF;
3158 --
3159 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3160 p_api_version,
3161 l_api_name,
3162 g_pkg_name ) THEN
3163 RAISE FND_API.g_exc_unexpected_error;
3164 END IF;
3165
3166 -- initialize message list if p_init_msg_list is set to TRUE.
3167
3168 IF FND_API.to_boolean( p_init_msg_list ) THEN
3169 FND_MSG_PUB.initialize;
3170 END IF;
3171
3172 -- initialize API return status to success
3173 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
3174
3175 -- call FND_REQUEST.SUBMIT_REQUEST to run the WSHRDPAK.rdf report
3176 l_conc_request_id := FND_REQUEST.SUBMIT_REQUEST
3177 ( 'WSH'
3178 , 'WSHRDPAK'
3179 , 'Packing Slip Report'
3180 , NULL
3181 , FALSE
3182 , p_delivery_id
3183 , p_print_cust_item
3184 , p_item_display
3185 , p_print_mode
3186 , p_print_all
3187 , p_sort
3188 , p_departure_date_lo
3189 , p_departure_date_hi
3190 , p_freight_carrier
3191 , p_warehouse_id
3192 , '', '', '', '', '', '', '', '', '', ''
3193 , '', '', '', '', '', '', '', '', '', ''
3194 , '', '', '', '', '', '', '', '', '', ''
3195 , '', '', '', '', '', '', '', '', '', ''
3196 , '', '', '', '', '', '', '', '', '', ''
3197 , '', '', '', '', '', '', '', '', '', ''
3198 , '', '', '', '', '', '', '', '', '', ''
3199 , '', '', '', '', '', '', '', '', '', ''
3200 , '', '', '', '', '', '', '', '', '', '');
3201
3202 -- must commit in order to submit the request
3203 COMMIT WORK;
3204
3205 x_conc_request_id := l_conc_request_id;
3206
3207 -- get message count and the message itself (if only one message)
3208 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
3209 p_data => x_msg_data);
3210
3211 --
3212 -- Debug Statements
3213 --
3214 IF l_debug_on THEN
3215 WSH_DEBUG_SV.log(l_module_name,'REQUEST ID',x_conc_request_id);
3216 WSH_DEBUG_SV.pop(l_module_name);
3217 END IF;
3218 --
3219 EXCEPTION
3220
3221 WHEN FND_API.g_exc_error THEN
3222 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
3223 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3224 p_data => x_msg_data );
3225
3226 --
3227 -- Debug Statements
3228 --
3229 IF l_debug_on THEN
3230 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3231 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3232 END IF;
3233 --
3234 WHEN FND_API.g_exc_unexpected_error THEN
3235 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3236 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3237 p_data => x_msg_data );
3238 --
3239 -- Debug Statements
3240 --
3241 IF l_debug_on THEN
3242 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3243 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3244 END IF;
3245 --
3246 WHEN others THEN
3247 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
3248 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
3249 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
3250 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
3251 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3252 WSH_UTIL_CORE.add_message (x_return_status);
3253 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3254 p_data => x_msg_data );
3255 --
3256 -- Debug Statements
3257 --
3258 IF l_debug_on THEN
3259 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3260 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3261 END IF;
3262 --
3263 END Print_Document;
3264
3265 ------------------------------------------------------------------------------
3266 -- FUNCTION : Get_CumQty PUBLIC
3267 -- VERSION : 1.0
3268 -- COMMENT : Obtain cummulative quantity value based on the inputs
3269 -- by calling Automotive's CUM Management API. Return such
3270 -- value.
3271 --
3272 -- PARAMETER LIST :
3273 --
3274 -- IN
3275 --
3276 -- p_api_version known API version
3277 -- p_init_msg_list should API reset message stack (default: false)
3278 -- p_commit should API do a commit (default: false)
3279 -- p_validation_level extent of validation done in the API (not used)
3280 -- p_customer_id from delivery details
3281 -- p_oe_order_line_id from delivery details for getting line level
3282 -- information to be passed to cal_cum api
3283 --
3284 -- OUT
3285 --
3286 -- x_msg_count number of messages in stack
3287 -- x_msg_data message if there is only one message in stack
3288 -- x_return_status API return status ('S', 'E', 'U')
3289 --
3290 -- RETURN NUMBER, cum quantity value
3291 --
3292 -- PRE-CONDITIONS : None
3293 -- POST-CONDITIONS : None
3294 -- EXCEPTIONS : None
3295 ------------------------------------------------------------------------------
3296
3297 FUNCTION Get_CumQty
3298 ( p_api_version IN NUMBER
3299 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
3300 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
3301 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
3302 , x_return_status OUT NOCOPY VARCHAR2
3303 , x_msg_count OUT NOCOPY NUMBER
3304 , x_msg_data OUT NOCOPY VARCHAR2
3305 , p_customer_id IN NUMBER
3306 , p_oe_order_line_id IN NUMBER
3307 )
3308 RETURN NUMBER
3309 IS
3310 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_CumQty';
3311 L_API_VERSION CONSTANT NUMBER := 1.0;
3312 l_msg_flag VARCHAR2(2000);
3313 l_cum_qty NUMBER;
3314 l_customer_item_id RLM_CUST_ITEM_CUM_KEYS.CUSTOMER_ITEM_ID%TYPE;
3315 l_ship_from_org_id RLM_CUST_ITEM_CUM_KEYS.SHIP_FROM_ORG_ID%TYPE;
3316 l_ship_to_address_id RLM_CUST_ITEM_CUM_KEYS.SHIP_TO_ADDRESS_ID%TYPE;
3317 l_bill_to_address_id RLM_CUST_ITEM_CUM_KEYS.BILL_TO_ADDRESS_ID%TYPE;
3318 l_po_number RLM_CUST_ITEM_CUM_KEYS.PURCHASE_ORDER_NUMBER%TYPE;
3319 l_cust_record_year RLM_CUST_ITEM_CUM_KEYS.CUST_RECORD_YEAR%TYPE;
3320 l_inventory_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%TYPE;
3321 l_cum_key_record RLM_CUM_SV.cum_key_attrib_rec_type;
3322 l_cum_record RLM_CUM_SV.cum_rec_type;
3323 -- 1711448
3324 l_source_doc_hdr_id NUMBER;
3325 l_source_doc_line_id NUMBER;
3326 l_cum_start_date DATE;
3327 l_return_message VARCHAR2(4000);
3328 l_msg_data VARCHAR2(4000);
3329 l_return_status BOOLEAN;
3330
3331 --
3332 l_debug_on BOOLEAN;
3333 --
3334 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CUMQTY';
3335 --
3336 BEGIN
3337 -- standard call to check for call compatibility.
3338 --
3339 -- Debug Statements
3340 --
3341 --
3342 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3343 --
3344 IF l_debug_on IS NULL
3345 THEN
3346 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3347 END IF;
3348 --
3349 IF l_debug_on THEN
3350 WSH_DEBUG_SV.push(l_module_name);
3351 --
3352 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
3353 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
3354 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
3355 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
3356 WSH_DEBUG_SV.log(l_module_name,'P_CUSTOMER_ID',P_CUSTOMER_ID);
3357 WSH_DEBUG_SV.log(l_module_name,'P_OE_ORDER_LINE_ID',P_OE_ORDER_LINE_ID);
3358 END IF;
3359 --
3360 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3361 p_api_version,
3362 l_api_name,
3363 g_pkg_name ) THEN
3364 RAISE FND_API.g_exc_unexpected_error;
3365 END IF;
3366
3367 -- initialize message list if p_init_msg_list is set to TRUE.
3368 IF FND_API.to_boolean( p_init_msg_list ) THEN
3369 FND_MSG_PUB.initialize;
3370 END IF;
3371
3372 -- initialize API return status to success
3373 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
3374
3375 -- initially l_cum_qty=0
3376 l_cum_qty :=0;
3377
3378 -- get information required about that order line from OE
3379 -- 1711448, changed to hz Tables from oe views
3380 SELECT
3381 ol.ship_from_org_id
3382 , ol.source_document_id
3383 , ol.source_document_line_id
3384 , ss.cust_acct_site_id
3385 , bs.cust_acct_site_id
3386 , DECODE (ol.item_identifier_type,
3387 'CUST', ol.ordered_item_id, NULL)
3388 , ol.cust_po_number
3389 , ol.industry_attribute1
3390 , ol.inventory_item_id
3391 INTO
3392 l_ship_from_org_id
3393 , l_source_doc_hdr_id
3394 , l_source_doc_line_id
3395 , l_ship_to_address_id
3396 , l_bill_to_address_id
3397 , l_customer_item_id
3398 , l_po_number
3399 , l_cust_record_year
3400 , l_inventory_item_id
3401 FROM
3402 oe_order_lines_all ol
3403 , hz_cust_acct_sites_all ss
3404 , hz_cust_site_uses_all ssu
3405 , hz_cust_acct_sites_all bs
3406 , hz_cust_site_uses_all bsu
3407 WHERE ol.line_id=p_oe_order_line_id
3408 and ol.ship_to_org_id = ssu.site_use_id (+)
3409 and ssu.site_use_code = 'SHIP_TO'
3410 and ol.invoice_to_org_id = bsu.site_use_id (+)
3411 and bsu.site_use_code = 'BILL_TO'
3412 and ssu.cust_acct_site_ID = ss.cust_acct_site_ID
3413 and bsu.cust_acct_site_ID = bs.cust_acct_site_ID;
3414
3415 IF SQL%NOTFOUND or SQL%ROWCOUNT > 1
3416 THEN
3417 RAISE FND_API.g_exc_error;
3418 END IF;
3419
3420 -- 1711448, Added this API to get CUM Start Date
3421 --
3422 -- Debug Statements
3423 --
3424 IF l_debug_on THEN
3425 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit RLM_CUM_SV.GETCUMSTARTDATE',WSH_DEBUG_SV.C_PROC_LEVEL);
3426 END IF;
3427 --
3428 RLM_CUM_SV.GetCumStartDate(
3429 i_schedule_header_id => l_source_doc_hdr_id,
3430 i_schedule_line_id => l_source_doc_line_id,
3431 o_cum_start_date => l_cum_start_date,
3432 o_cust_record_year => l_cust_record_year,
3433 o_return_message => l_msg_data,
3434 o_return_status => l_return_status
3435 );
3436
3437 -- prepare the record parameters for use by the cum apis
3438 l_cum_key_record.customer_id:=p_customer_id;
3439 l_cum_key_record.customer_item_id:=l_customer_item_id;
3440 l_cum_key_record.ship_from_org_id:=l_ship_from_org_id;
3441 l_cum_key_record.ship_to_address_id:=l_ship_to_address_id;
3442 l_cum_key_record.bill_to_address_id:=l_bill_to_address_id;
3443 l_cum_key_record.purchase_order_number:=l_po_number;
3444 l_cum_key_record.cust_record_year:=l_cust_record_year;
3445 l_cum_key_record.create_cum_key_flag:='N';
3446 -- 1711448, Added Cum start Dt.
3447 l_cum_key_record.cum_start_date:=l_cum_start_date;
3448
3449 --BUG 1932236
3450 l_cum_key_record.inventory_item_id:=l_inventory_item_id;
3451
3452 -- get cum_key by calling calculate_cum_key routine in RLM_CUM_SV
3453 --
3454 -- Debug Statements
3455 --
3456 IF l_debug_on THEN
3457 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit RLM_TPA_SV.CALCULATECUMKEY',WSH_DEBUG_SV.C_PROC_LEVEL);
3458 END IF;
3459 --
3460 RLM_TPA_SV.CalculateCumKey( x_cum_key_record=>l_cum_key_record
3461 , x_cum_record=>l_cum_record);
3462
3463 -- get cum_qty by calling calculate_supplier_cum in RLM_CUM_SV
3464 --
3465 -- Debug Statements
3466 --
3467 IF l_debug_on THEN
3468 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit RLM_TPA_SV.CALCULATESUPPLIERCUM',WSH_DEBUG_SV.C_PROC_LEVEL);
3469 END IF;
3470 --
3471 RLM_TPA_SV.CalculateSupplierCum( x_cum_key_record=>l_cum_key_record
3472 , x_cum_record=>l_cum_record);
3473
3474 l_cum_qty:=l_cum_record.cum_qty;
3475
3476 --
3477 -- Debug Statements
3478 --
3479 IF l_debug_on THEN
3480 WSH_DEBUG_SV.log(l_module_name,'L_CUM_QTY',l_cum_qty);
3481 WSH_DEBUG_SV.pop(l_module_name);
3482 END IF;
3483 --
3484 RETURN l_cum_qty;
3485
3486 EXCEPTION
3487
3488 WHEN FND_API.g_exc_error THEN
3489 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
3490 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3491 p_data => x_msg_data );
3492 --
3493 -- Debug Statements
3494 --
3495 IF l_debug_on THEN
3496 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3497 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3498 END IF;
3499 --
3500 RETURN null;
3501
3502 WHEN FND_API.g_exc_unexpected_error THEN
3503 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3504 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3505 p_data => x_msg_data );
3506 --
3507 -- Debug Statements
3508 --
3509 IF l_debug_on THEN
3510 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3511 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3512 END IF;
3513 --
3514 RETURN null;
3515
3516 WHEN others THEN
3517 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
3518 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
3519 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
3520 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
3521 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3522 WSH_UTIL_CORE.add_message (x_return_status);
3523 --
3524 -- Debug Statements
3525 --
3526 IF l_debug_on THEN
3527 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3528 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3529 END IF;
3530 --
3531 RETURN null;
3532 END Get_CumQty;
3533
3534 ------------------------------------------------------------------------------
3535 -- PROCEDURE : Cancel_All_Documents PUBLIC
3536 -- VERSION : 1.0
3537 -- COMMENT : Updates status of all documents of all types that
3538 -- belong to a specific entity
3539 -- to 'CANCELLED'
3540 --
3541 -- PARAMETER LIST :
3542 --
3543 -- IN
3544 --
3545 -- p_api_version known API version
3546 -- p_init_msg_list should API reset message stack (default: false)
3547 -- p_commit should API do a commit (default: false)
3548 -- p_validation_level extent of validation done in the API (not used)
3549 -- p_entity_name Entity for which the document is being cancelled
3550 -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
3551 -- p_entity_id Entity id that the document belongs to
3552 -- example: delivery_id, delivery_leg_id, etc
3553 --
3554 -- OUT
3555 --
3556 -- x_msg_count number of messages in stack
3557 -- x_msg_data message if there is only one message in stack
3558 -- x_return_status API return status ('S', 'E', 'U')
3559 --
3560 -- PRE-CONDITIONS : None
3561 -- POST-CONDITIONS : None
3562 -- EXCEPTIONS : None
3563 -- NOTES : In consolidation situation, the child documents
3564 -- are not cancelled. Call this routine recursively
3565 -- for all entities where cancellation is reqd.
3566 ------------------------------------------------------------------------------
3567
3568 PROCEDURE Cancel_All_Documents
3569 ( p_api_version IN NUMBER
3570 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
3571 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
3572 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
3573 , x_return_status OUT NOCOPY VARCHAR2
3574 , x_msg_count OUT NOCOPY NUMBER
3575 , x_msg_data OUT NOCOPY VARCHAR2
3576 , p_entity_name IN VARCHAR2
3577 , p_entity_id IN NUMBER
3578 ) IS
3579 L_API_NAME CONSTANT VARCHAR2(30) := 'Cancel_All_Documents';
3580 L_API_VERSION CONSTANT NUMBER := 1.0;
3581
3582 --
3583 l_debug_on BOOLEAN;
3584 --
3585 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CANCEL_ALL_DOCUMENTS';
3586 --
3587 BEGIN
3588 -- since this procedure does DML issue savepoint
3589 --
3590 -- Debug Statements
3591 --
3592 --
3593 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3594 --
3595 IF l_debug_on IS NULL
3596 THEN
3597 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3598 END IF;
3599 --
3600 IF l_debug_on THEN
3601 WSH_DEBUG_SV.push(l_module_name);
3602 --
3603 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
3604 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
3605 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
3606 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
3607 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
3608 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
3609 END IF;
3610 --
3611 SAVEPOINT WSH_Document_PVT;
3612
3613 -- standard call to check for call compatibility.
3614 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3615 p_api_version,
3616 l_api_name,
3617 g_pkg_name ) THEN
3618 RAISE FND_API.g_exc_unexpected_error;
3619 END IF;
3620
3621 -- initialize message list if p_init_msg_list is set to TRUE.
3622 IF FND_API.to_boolean( p_init_msg_list ) THEN
3623 FND_MSG_PUB.initialize;
3624 END IF;
3625
3626 -- initialize API return status to success
3627 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
3628
3629
3630 UPDATE wsh_document_instances
3631 SET status = 'CANCELLED'
3632 , last_update_date = sysdate
3633 , last_updated_by = fnd_global.user_id
3634 , last_update_login = fnd_global.login_id
3635 WHERE entity_name = p_entity_name
3636 AND entity_id = p_entity_id;
3637
3638 -- get message count and the message itself (if only one message)
3639 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
3640 p_data => x_msg_data);
3641 -- Standard check of p_commit.
3642 IF FND_API.to_boolean( p_commit ) THEN
3643 COMMIT WORK;
3644 END IF;
3645 --
3646 -- Debug Statements
3647 --
3648 IF l_debug_on THEN
3649 WSH_DEBUG_SV.pop(l_module_name);
3650 END IF;
3651 --
3652 EXCEPTION
3653
3654 WHEN FND_API.g_exc_error THEN
3655 ROLLBACK to WSH_Document_PVT;
3656 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
3657 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3658 p_data => x_msg_data );
3659
3660 --
3661 -- Debug Statements
3662 --
3663 IF l_debug_on THEN
3664 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3665 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3666 END IF;
3667 --
3668 WHEN FND_API.g_exc_unexpected_error THEN
3669 ROLLBACK to WSH_Document_PVT;
3670 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3671 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3672 p_data => x_msg_data );
3673
3674 --
3675 -- Debug Statements
3676 --
3677 IF l_debug_on THEN
3678 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3679 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3680 END IF;
3681 --
3682 WHEN others THEN
3683 ROLLBACK to WSH_Document_PVT;
3684 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
3685 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
3686 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
3687 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
3688 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3689 WSH_UTIL_CORE.add_message (x_return_status);
3690 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3691 p_data => x_msg_data );
3692 --
3693 -- Debug Statements
3694 --
3695 IF l_debug_on THEN
3696 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3697 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3698 END IF;
3699 --
3700 END Cancel_All_Documents;
3701
3702 ------------------------------------------------------------------------------
3703 -- PROCEDURE : Get_All_Documents PUBLIC
3704 -- VERSION : 1.0
3705 -- COMMENT : Returns as an out-param a table of records of all documents
3706 -- (packing slip, bill of lading, etc.) that belong to a
3707 -- specific entity (delivery, delivery_leg, etc.)
3708 --
3709 -- PARAMETER LIST :
3710 --
3711 -- IN
3712 --
3713 -- p_api_version known API version
3714 -- p_init_msg_list should API reset message stack (default: false)
3715 -- p_commit should API do a commit (default: false)
3716 -- p_validation_level extent of validation done in the API (not used)
3717 -- p_entity_name Entity for which the document is being cancelled
3718 -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
3719 -- p_entity_id Entity id that the document belongs to
3720 -- example: delivery_id, delivery_leg_id, etc
3721
3722 -- OUT
3723 --
3724 -- x_msg_count number of messages in stack
3725 -- x_msg_data message if there is only one message in stack
3726 -- x_return_status API return status ('S', 'E', 'U')
3727 -- x_document_tab table that contains all documents of the entity
3728 --
3729 -- PRE-CONDITIONS : None
3730 -- POST-CONDITIONS : None
3731 -- EXCEPTIONS : None
3732 ------------------------------------------------------------------------------
3733
3734 PROCEDURE Get_All_Documents
3735 ( p_api_version IN NUMBER
3736 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
3737 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
3738 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
3739 , x_return_status OUT NOCOPY VARCHAR2
3740 , x_msg_count OUT NOCOPY NUMBER
3741 , x_msg_data OUT NOCOPY VARCHAR2
3742 , p_entity_name IN VARCHAR2
3743 , p_entity_id IN NUMBER
3744 , x_document_tab OUT NOCOPY wsh_document_pub.document_tabtype
3745 ) IS
3746 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_All_Documents';
3747 L_API_VERSION CONSTANT NUMBER := 1.0;
3748 CURSOR doc_csr IS
3749 SELECT document_instance_id
3750 , document_type
3751 , entity_name
3752 , entity_id
3753 , doc_sequence_category_id
3754 , sequence_number
3755 , status
3756 , final_print_date
3757 , created_by
3758 , creation_date
3759 , last_updated_by
3760 , last_update_date
3761 , last_update_login
3762 , program_application_id
3763 , program_id
3764 , program_update_date
3765 , request_id
3766 , attribute_category
3767 , attribute1
3768 , attribute2
3769 , attribute3
3770 , attribute4
3771 , attribute5
3772 , attribute6
3773 , attribute7
3774 , attribute8
3775 , attribute9
3776 , attribute10
3777 , attribute11
3778 , attribute12
3779 , attribute13
3780 , attribute14
3781 , attribute15
3782 FROM wsh_document_instances
3783 WHERE entity_name = p_entity_name
3784 AND entity_id = p_entity_id;
3785
3786 i NUMBER := 1; -- loop counter
3787
3788 --
3789 l_debug_on BOOLEAN;
3790 --
3791 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_ALL_DOCUMENTS';
3792 --
3793 BEGIN
3794
3795 -- standard call to check for call compatibility.
3796 --
3797 -- Debug Statements
3798 --
3799 --
3800 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3801 --
3802 IF l_debug_on IS NULL
3803 THEN
3804 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3805 END IF;
3806 --
3807 IF l_debug_on THEN
3808 WSH_DEBUG_SV.push(l_module_name);
3809 --
3810 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
3811 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
3812 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
3813 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
3814 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
3815 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
3816 END IF;
3817 --
3818 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3819 p_api_version,
3820 l_api_name,
3821 g_pkg_name ) THEN
3822 RAISE FND_API.g_exc_unexpected_error;
3823 END IF;
3824
3825 -- initialize message list if p_init_msg_list is set to TRUE.
3826 IF FND_API.to_boolean( p_init_msg_list ) THEN
3827 FND_MSG_PUB.initialize;
3828 END IF;
3829
3830 -- initialize API return status to success
3831 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
3832
3833 -- Initialize the table
3834 --
3835 -- Debug Statements
3836 --
3837 IF l_debug_on THEN
3838 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DOCUMENT_PUB.DOCUMENT_TABTYPE',WSH_DEBUG_SV.C_PROC_LEVEL);
3839 END IF;
3840 --
3841 x_document_tab := wsh_document_pub.document_tabtype();
3842
3843
3844 FOR doc_rec IN doc_csr
3845 LOOP
3846 x_document_tab.extend;
3847 x_document_tab(i).document_instance_id:= doc_rec.document_instance_id;
3848 x_document_tab(i).document_type := doc_rec.document_type;
3849 x_document_tab(i).entity_name := doc_rec.entity_name;
3850 x_document_tab(i).entity_id := doc_rec.entity_id;
3851 x_document_tab(i).doc_sequence_category_id
3852 := doc_rec.doc_sequence_category_id;
3853 x_document_tab(i).sequence_number := doc_rec.sequence_number;
3854 x_document_tab(i).status := doc_rec.status;
3855 x_document_tab(i).final_print_date := doc_rec.final_print_date;
3856 x_document_tab(i).created_by := doc_rec.created_by;
3857 x_document_tab(i).creation_date := doc_rec.creation_date;
3858 x_document_tab(i).last_updated_by := doc_rec.last_updated_by;
3859 x_document_tab(i).last_update_date := doc_rec.last_update_date;
3860 x_document_tab(i).last_update_login := doc_rec.last_update_login;
3861 x_document_tab(i).program_application_id
3862 := doc_rec.program_application_id;
3863 x_document_tab(i).program_id := doc_rec.program_id;
3864 x_document_tab(i).program_update_date := doc_rec.program_update_date;
3865 x_document_tab(i).request_id := doc_rec.request_id;
3866 x_document_tab(i).attribute_category := doc_rec.attribute_category;
3867 x_document_tab(i).attribute1 := doc_rec.attribute1;
3868 x_document_tab(i).attribute2 := doc_rec.attribute2;
3869 x_document_tab(i).attribute3 := doc_rec.attribute3;
3870 x_document_tab(i).attribute4 := doc_rec.attribute4;
3871 x_document_tab(i).attribute5 := doc_rec.attribute5;
3872 x_document_tab(i).attribute6 := doc_rec.attribute6;
3873 x_document_tab(i).attribute7 := doc_rec.attribute7;
3874 x_document_tab(i).attribute8 := doc_rec.attribute8;
3875 x_document_tab(i).attribute9 := doc_rec.attribute9;
3876 x_document_tab(i).attribute10 := doc_rec.attribute10;
3877 x_document_tab(i).attribute11 := doc_rec.attribute11;
3878 x_document_tab(i).attribute12 := doc_rec.attribute12;
3879 x_document_tab(i).attribute13 := doc_rec.attribute13;
3880 x_document_tab(i).attribute14 := doc_rec.attribute14;
3881 x_document_tab(i).attribute15 := doc_rec.attribute15;
3882 i := i + 1;
3883 END LOOP;
3884
3885 -- get message count and the message itself (if only one message)
3886 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
3887 p_data => x_msg_data);
3888 -- Standard check of p_commit.
3889 IF FND_API.to_boolean( p_commit ) THEN
3890 COMMIT WORK;
3891 END IF;
3892 --
3893 -- Debug Statements
3894 --
3895 IF l_debug_on THEN
3896 WSH_DEBUG_SV.log(l_module_name,'X_DOCUMENT_TAB.COUNT',x_document_tab.count);
3897 WSH_DEBUG_SV.pop(l_module_name);
3898 END IF;
3899 --
3900 EXCEPTION
3901
3902 WHEN FND_API.g_exc_error THEN
3903 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
3904 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3905 p_data => x_msg_data );
3906
3907 --
3908 -- Debug Statements
3909 --
3910 IF l_debug_on THEN
3911 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3912 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3913 END IF;
3914 --
3915 WHEN FND_API.g_exc_unexpected_error THEN
3916 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3917 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3918 p_data => x_msg_data );
3919
3920 --
3921 -- Debug Statements
3922 --
3923 IF l_debug_on THEN
3924 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3925 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3926 END IF;
3927 --
3928 WHEN others THEN
3929 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
3930 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
3931 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
3932 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
3933 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3934 WSH_UTIL_CORE.add_message (x_return_status);
3935 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3936 p_data => x_msg_data );
3937 --
3938 -- Debug Statements
3939 --
3940 IF l_debug_on THEN
3941 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3942 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3943 END IF;
3944 --
3945 END Get_All_Documents;
3946
3947 ------------------------------------------------------------------------------
3948 -- PROCEDURE : Lock_Document PUBLIC
3949 -- VERSION : 1.0
3950 -- COMMENT : Locks a document row
3951 --
3952 -- PARAMETER LIST :
3953 --
3954 -- IN
3955 --
3956 -- p_rowid Rowid of wsh_document_instances table
3957 -- p_document_instance_id document instance id
3958 -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
3959 -- p_sequence_number sequence number of the document
3960 -- p_status status of the document
3961 -- p_final_print_date final print date
3962 -- p_entity_name Entity for which the document is being updated
3963 -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
3964 -- p_entity_id Entity id that the document belongs to
3965 -- example: delivery_id, delivery_leg_id, etc
3966 -- p_doc_sequence_category_id document sequence category id
3967 -- p_pod_flag pod_flag for the document
3968 -- p_pod_by pod_by for the document
3969 -- p_pod_date pod_date for the document
3970 -- p_reason_of_transport reason of transport that describes the delivery
3971 -- p_description external aspect of the delivery
3972 -- p_cod_amount cod_amount of the document
3973 -- p_cod_currency_code cod_currency_code of the document
3974 -- p_cod_remit_to cod_remit_to of the document
3975 -- p_cod_charge_paid_by cod_charge_paid_by of the document
3976 -- p_problem_contact_reference problem_contact_referene of the document
3977 -- p_bill_freight_to bill_freight_to of the document
3978 -- p_carried_by carried_by of the document
3979 -- p_port_of_loading port_of_loading of the docucent
3980 -- p_port_of_discharge port_of_discharge of the document
3981 -- p_booking_office booking_office of the document
3982 -- p_booking_number booking_number of the document
3983 -- p_service_contract service_contract of the document
3984 -- p_shipper_export_ref shipper_export_ref of the document
3985 -- p_carrier_export_ref carrier_export_ref of the document
3986 -- p_bol_notify_party bol_notify_party of the document
3987 -- p_supplier_code supplier_code of the document
3988 -- p_aetc_number aetc_number of the document
3989 -- p_shipper_signed_by shipper_signed_by of the document
3990 -- p_shipper_date shipper_date of the document
3991 -- p_carrier_signed_by carrier_signed_by of the document
3992 -- p_carrier_date carrier_date of the document
3993 -- p_bol_issue_office bol_issue_office of the document
3994 -- p_bol_issued_by bol_issued_by of the document
3995 -- p_bol_date_issued bol_date_issued of the document
3996 -- p_shipper_hm_by shipper_bm_by of the document
3997 -- p_shipper_hm_date shipper_hm_date of the document
3998 -- p_carrier_hm_by carrier_hm_by of the document
3999 -- p_carrier_hm_date carrier_hm_date of the document
4000 -- p_created_by standard who column
4001 -- p_creation_date standard who column
4002 -- p_last_updated_by standard who column
4003 -- p_last_update_date standard who column
4004 -- p_last_update_login standard who column
4005 -- p_program_applicaiton_id standard who column
4006 -- p_program_id standard who column
4007 -- p_program_update_date standard who column
4008 -- p_request_id standard who column
4009 -- p_attribute_category Descriptive Flex field context
4010 -- p_attribute1 Descriptive Flex field
4011 -- p_attribute2 Descriptive Flex field
4012 -- p_attribute3 Descriptive Flex field
4013 -- p_attribute4 Descriptive Flex field
4014 -- p_attribute5 Descriptive Flex field
4015 -- p_attribute6 Descriptive Flex field
4016 -- p_attribute7 Descriptive Flex field
4017 -- p_attribute8 Descriptive Flex field
4018 -- p_attribute9 Descriptive Flex field
4019 -- p_attribute10 Descriptive Flex field
4020 -- p_attribute11 Descriptive Flex field
4021 -- p_attribute12 Descriptive Flex field
4022 -- p_attribute13 Descriptive Flex field
4023 -- p_attribute14 Descriptive Flex field
4024 -- p_attribute15 Descriptive Flex field
4025 --
4026 -- OUT
4027 --
4028 -- x_return_status API return status ('S', 'E', 'U')
4029 --
4030 -- PRE-CONDITIONS : None
4031 -- POST-CONDITIONS : None
4032 -- EXCEPTIONS : None
4033 --
4034 -- NOTES : 1. Called from Shipping trx form only. Not an API.
4035 -- Does not conform to API standards.
4036 --
4037 -- 2. In a consolidation situation, this routine looks
4038 -- for a lock only on the parent document only.
4039 --
4040 ------------------------------------------------------------------------------
4041
4042
4043 PROCEDURE Lock_Document
4044 ( p_rowid IN VARCHAR2
4045 , p_document_instance_id IN NUMBER
4046 , p_document_type IN VARCHAR2
4047 , p_sequence_number IN VARCHAR2
4048 , p_status IN VARCHAR2
4049 , p_final_print_date IN DATE
4050 , p_entity_name IN VARCHAR2
4051 , p_entity_id IN NUMBER
4052 , p_doc_sequence_category_id IN NUMBER
4053 , p_created_by IN NUMBER
4054 , p_creation_date IN DATE
4055 , p_last_updated_by IN NUMBER
4056 , p_last_update_date IN DATE
4057 , p_last_update_login IN NUMBER
4058 , p_program_application_id IN NUMBER
4059 , p_program_id IN NUMBER
4060 , p_program_update_date IN DATE
4061 , p_request_id IN NUMBER
4062 , p_attribute_category IN VARCHAR2
4063 , p_attribute1 IN VARCHAR2
4064 , p_attribute2 IN VARCHAR2
4065 , p_attribute3 IN VARCHAR2
4066 , p_attribute4 IN VARCHAR2
4067 , p_attribute5 IN VARCHAR2
4068 , p_attribute6 IN VARCHAR2
4069 , p_attribute7 IN VARCHAR2
4070 , p_attribute8 IN VARCHAR2
4071 , p_attribute9 IN VARCHAR2
4072 , p_attribute10 IN VARCHAR2
4073 , p_attribute11 IN VARCHAR2
4074 , p_attribute12 IN VARCHAR2
4075 , p_attribute13 IN VARCHAR2
4076 , p_attribute14 IN VARCHAR2
4077 , p_attribute15 IN VARCHAR2
4078 , x_return_status OUT NOCOPY VARCHAR2
4079 ) IS
4080
4081 counter NUMBER;
4082 CURSOR lock_csr IS
4083 SELECT
4084 document_instance_id
4085 , document_type
4086 , sequence_number
4087 , status
4088 , final_print_date
4089 , entity_name
4090 , entity_id
4091 , doc_sequence_category_id
4092 , created_by
4093 , creation_date
4094 , last_updated_by
4095 , last_update_date
4096 , last_update_login
4097 , program_application_id
4098 , program_id
4099 , program_update_date
4100 , request_id
4101 , attribute_category
4102 , attribute1
4103 , attribute2
4104 , attribute3
4105 , attribute4
4106 , attribute5
4107 , attribute6
4108 , attribute7
4109 , attribute8
4110 , attribute9
4111 , attribute10
4112 , attribute11
4113 , attribute12
4114 , attribute13
4115 , attribute14
4116 , attribute15
4117 FROM
4118 wsh_document_instances
4119 WHERE rowid = p_rowid
4120 FOR UPDATE OF document_instance_id NOWAIT;
4121 lock_rec lock_csr%rowtype;
4122 --
4123 l_debug_on BOOLEAN;
4124 --
4125 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_DOCUMENT';
4126 --
4127 BEGIN
4128 --
4129 -- Debug Statements
4130 --
4131 --
4132 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4133 --
4134 IF l_debug_on IS NULL
4135 THEN
4136 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4137 END IF;
4138 --
4139 IF l_debug_on THEN
4140 WSH_DEBUG_SV.push(l_module_name);
4141 --
4142 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
4143 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_INSTANCE_ID',P_DOCUMENT_INSTANCE_ID);
4144 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
4145 WSH_DEBUG_SV.log(l_module_name,'P_SEQUENCE_NUMBER',P_SEQUENCE_NUMBER);
4146 WSH_DEBUG_SV.log(l_module_name,'P_STATUS',P_STATUS);
4147 WSH_DEBUG_SV.log(l_module_name,'P_FINAL_PRINT_DATE',P_FINAL_PRINT_DATE);
4148 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
4149 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
4150 WSH_DEBUG_SV.log(l_module_name,'P_DOC_SEQUENCE_CATEGORY_ID',P_DOC_SEQUENCE_CATEGORY_ID);
4151 WSH_DEBUG_SV.log(l_module_name,'P_CREATED_BY',P_CREATED_BY);
4152 WSH_DEBUG_SV.log(l_module_name,'P_CREATION_DATE',P_CREATION_DATE);
4153 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);
4154 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);
4155 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);
4156 WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_APPLICATION_ID',P_PROGRAM_APPLICATION_ID);
4157 WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_ID',P_PROGRAM_ID);
4158 WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_UPDATE_DATE',P_PROGRAM_UPDATE_DATE);
4159 WSH_DEBUG_SV.log(l_module_name,'P_REQUEST_ID',P_REQUEST_ID);
4160 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE_CATEGORY',P_ATTRIBUTE_CATEGORY);
4161 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE1',P_ATTRIBUTE1);
4162 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE2',P_ATTRIBUTE2);
4163 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE3',P_ATTRIBUTE3);
4164 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE4',P_ATTRIBUTE4);
4165 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE5',P_ATTRIBUTE5);
4166 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE6',P_ATTRIBUTE6);
4167 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE7',P_ATTRIBUTE7);
4168 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE8',P_ATTRIBUTE8);
4169 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE9',P_ATTRIBUTE9);
4170 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE10',P_ATTRIBUTE10);
4171 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE11',P_ATTRIBUTE11);
4172 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE12',P_ATTRIBUTE12);
4173 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE13',P_ATTRIBUTE13);
4174 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE14',P_ATTRIBUTE14);
4175 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE15',P_ATTRIBUTE15);
4176 END IF;
4177 --
4178 OPEN lock_csr;
4179 FETCH lock_csr INTO lock_rec;
4180 IF lock_csr%NOTFOUND
4181 THEN
4182 CLOSE lock_csr;
4183 FND_MESSAGE.set_name ('FND', 'FORM_RECORD_DELETED');
4184 APP_EXCEPTION.raise_exception;
4185 END IF;
4186 CLOSE lock_csr;
4187
4188 -- verify the not null columns are identical
4189 IF ( lock_rec.document_instance_id = p_document_instance_id
4190 AND lock_rec.document_type = p_document_type
4191 AND lock_rec.sequence_number = p_sequence_number
4192 AND lock_rec.entity_name = p_entity_name
4193 AND lock_rec.entity_id = p_entity_id
4194 AND lock_rec.created_by = p_created_by
4195 AND lock_rec.creation_date = p_creation_date
4196 AND lock_rec.last_updated_by = p_last_updated_by
4197 AND lock_rec.last_update_date = p_last_update_date
4198
4199 -- verify the nullable columns are either identical or both null
4200 AND ((lock_rec.status = p_status)
4201 OR
4202 (lock_rec.status IS NULL AND p_status IS NULL))
4203 AND ((lock_rec.final_print_date = p_final_print_date)
4204 OR
4205 (lock_rec.final_print_date IS NULL AND p_final_print_date IS NULL))
4206 AND ((lock_rec.doc_sequence_category_id = p_doc_sequence_category_id)
4207 OR
4208 (lock_rec.doc_sequence_category_id IS NULL AND
4209 p_doc_sequence_category_id IS NULL))
4210 AND ((lock_rec.last_update_login = p_last_update_login)
4211 OR
4212 (lock_rec.last_update_login IS NULL AND p_last_update_login IS NULL))
4213 AND ((lock_rec.program_application_id = p_program_application_id)
4214 OR
4215 (lock_rec.program_application_id IS NULL AND
4216 p_program_application_id IS NULL))
4217 AND ((lock_rec.program_id = p_program_id)
4218 OR
4219 (lock_rec.program_id IS NULL AND p_program_id IS NULL))
4220 AND ((lock_rec.program_update_date = p_program_update_date)
4221 OR
4222 (lock_rec.program_update_date IS NULL AND p_program_update_date IS NULL))
4223 AND ((lock_rec.request_id = p_request_id)
4224 OR
4225 (lock_rec.request_id IS NULL AND p_request_id IS NULL))
4226 AND ((lock_rec.attribute_category = p_attribute_category)
4227 OR
4228 (lock_rec.attribute_category IS NULL AND p_attribute_category IS NULL))
4229 AND ((lock_rec.attribute1 = p_attribute1)
4230 OR
4231 (lock_rec.attribute1 IS NULL AND p_attribute1 IS NULL))
4232 AND ((lock_rec.attribute2 = p_attribute2)
4233 OR
4234 (lock_rec.attribute2 IS NULL AND p_attribute2 IS NULL))
4235 AND ((lock_rec.attribute3 = p_attribute3)
4236 OR
4237 (lock_rec.attribute3 IS NULL AND p_attribute3 IS NULL))
4238 AND ((lock_rec.attribute4 = p_attribute4)
4239 OR
4240 (lock_rec.attribute4 IS NULL AND p_attribute4 IS NULL))
4241 AND ((lock_rec.attribute5 = p_attribute5)
4242 OR
4243 (lock_rec.attribute5 IS NULL AND p_attribute5 IS NULL))
4244 AND ((lock_rec.attribute6 = p_attribute6)
4245 OR
4246 (lock_rec.attribute6 IS NULL AND p_attribute6 IS NULL))
4247 AND ((lock_rec.attribute7 = p_attribute7)
4248 OR
4249 (lock_rec.attribute7 IS NULL AND p_attribute7 IS NULL))
4250 AND ((lock_rec.attribute8 = p_attribute8)
4251 OR
4252 (lock_rec.attribute8 IS NULL AND p_attribute8 IS NULL))
4253 AND ((lock_rec.attribute9 = p_attribute9)
4254 OR
4255 (lock_rec.attribute9 IS NULL AND p_attribute9 IS NULL))
4256 AND ((lock_rec.attribute10 = p_attribute10)
4257 OR
4258 (lock_rec.attribute10 IS NULL AND p_attribute10 IS NULL))
4259 AND ((lock_rec.attribute11 = p_attribute11)
4260 OR
4261 (lock_rec.attribute11 IS NULL AND p_attribute11 IS NULL))
4262 AND ((lock_rec.attribute12 = p_attribute12)
4263 OR
4264 (lock_rec.attribute12 IS NULL AND p_attribute12 IS NULL))
4265 AND ((lock_rec.attribute13 = p_attribute13)
4266 OR
4267 (lock_rec.attribute13 IS NULL AND p_attribute13 IS NULL))
4268 AND ((lock_rec.attribute14 = p_attribute14)
4269 OR
4270 (lock_rec.attribute14 IS NULL AND p_attribute14 IS NULL))
4271 AND ((lock_rec.attribute15 = p_attribute15)
4272 OR
4273 (lock_rec.attribute15 IS NULL AND p_attribute15 IS NULL))
4274 )
4275 THEN
4276 --
4277 -- Debug Statements
4278 --
4279 IF l_debug_on THEN
4280 WSH_DEBUG_SV.pop(l_module_name);
4281 END IF;
4282 --
4283 return;
4284 ELSE
4285 FND_MESSAGE.set_name('FND','FORM_RECORD_CHANGED');
4286 APP_EXCEPTION.raise_exception;
4287 END IF;
4288
4289 --
4290 -- Debug Statements
4291 --
4292 IF l_debug_on THEN
4293 WSH_DEBUG_SV.pop(l_module_name);
4294 END IF;
4295 --
4296 END Lock_Document;
4297
4298
4299 ------------------------------------------------------------------------------
4300 -- PROCEDURE : set_template PUBLIC
4301 -- VERSION : 1.0
4302 -- COMMENT : This procedure is called before calling fnd_request.submit to
4303 -- set the layout template so that pdf output is generated.
4304 -- Template is obtained from shipping parameters based on the
4305 -- organization_id.
4306 -- PARAMETER LIST :
4307 --
4308 -- IN
4309 --
4310 -- p_organization_id Organization Id
4311 -- p_report 'BOL'/'MBOL'/'PAK'
4312 --
4313 -- OUT
4314 --
4315 -- x_conc_prog_name 'WSHRDBOL'/'WSHRDBOLX'/'WSHRDMBL'/'WSHRDMBLX'/'WSHRDPAK'/'WSHRDPAKX'
4316 -- x_return_status API return status ('S', 'E', 'U')
4317 --
4318 --
4319 -- PRE-CONDITIONS : None
4320 -- POST-CONDITIONS : None
4321 -- EXCEPTIONS : None
4322 ------------------------------------------------------------------------------
4323
4324
4325 PROCEDURE set_template ( p_organization_id NUMBER,
4326 p_report VARCHAR2,
4327 p_template_name VARCHAR2,
4328 x_conc_prog_name OUT NOCOPY VARCHAR2,
4329 x_return_status OUT NOCOPY VARCHAR2 ) IS
4330
4331 l_language VARCHAR2(100);
4332 l_territory VARCHAR2(100);
4333 l_param_value_info WSH_SHIPPING_PARAMS_PVT.parameter_value_rec_typ;
4334 l_report_template VARCHAR2(80);
4335 l_return_status VARCHAR2(1);
4336 l_status BOOLEAN;
4337 l_conc_prog_name VARCHAR2(10);
4338 get_shipping_param_err EXCEPTION;
4339
4340 l_debug_on BOOLEAN;
4341 --
4342 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'set_template';
4343
4344 BEGIN
4345
4346 --
4347 -- Debug Statements
4348 --
4349 --
4350 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4351 --
4352 IF l_debug_on IS NULL
4353 THEN
4354 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4355 END IF;
4356 --
4357 IF l_debug_on THEN
4358 WSH_DEBUG_SV.push(l_module_name);
4359 --
4360 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
4361 WSH_DEBUG_SV.log(l_module_name,'P_REPORT',P_REPORT);
4362 END IF;
4363 --
4364 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4365
4366 l_param_value_info.organization_id := p_organization_id;
4367 l_param_value_info.class_code(1) := 'XDO_TEMPLATE';
4368 l_param_value_info.param_name(1) := p_template_name;
4369
4370 IF l_debug_on THEN
4371 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.Get',WSH_DEBUG_SV.C_PROC_LEVEL);
4372 END IF;
4373
4374 wsh_shipping_params_pvt.get(l_param_value_info,l_return_status);
4375 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR))
4376 THEN
4377 IF l_debug_on THEN
4378 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_SHIPPING_PARAMS_PVT.Get returned '||l_return_status);
4379 END IF;
4380 RAISE get_shipping_param_err;
4381 END IF;
4382 --
4383 IF l_debug_on THEN
4384 WSH_DEBUG_SV.logmsg(l_module_name,'param_name.count '||l_param_value_info.param_name.COUNT);
4385 WSH_DEBUG_SV.logmsg(l_module_name,'param_name_chr.count '||l_param_value_info.param_value_chr.COUNT);
4386 END IF;
4387 --
4388 IF (l_param_value_info.param_value_chr.COUNT >0 and l_param_value_info.param_name.COUNT >0) THEN
4389 IF (l_param_value_info.param_name(1) = p_template_name) THEN
4390 l_report_template := l_param_value_info.param_value_chr(1);
4391 END IF;
4392 ELSE
4393 IF l_debug_on THEN
4394 WSH_DEBUG_SV.logmsg(l_module_name,'No Parameters returned from WSH_SHIPPING_PARAMS_PVT.Get ');
4395 END IF;
4396 RAISE get_shipping_param_err;
4397 END IF;
4398
4399 IF l_debug_on THEN
4400 wsh_debug_sv.log(l_module_name, 'Report Template from Shipping Parameters ',l_report_template);
4401 END IF;
4402
4403 IF (l_report_template IS NULL ) THEN
4404 l_conc_prog_name := 'WSHRD' || p_report;
4405 ELSE
4406 --{
4407 l_conc_prog_name := 'WSHRD' || p_report || 'X';
4408 SELECT ISO_LANGUAGE, ISO_TERRITORY INTO l_language, l_territory
4409 FROM FND_LANGUAGES
4410 WHERE LANGUAGE_CODE = userenv('LANG');
4411
4412 IF l_debug_on THEN
4413 wsh_debug_sv.log(l_module_name, 'language ', l_language);
4414 wsh_debug_sv.log(l_module_name, 'territory ', l_territory);
4415 END IF;
4416
4417 l_status := fnd_request.add_layout
4418 ('WSH',
4419 l_report_template,
4420 l_language,
4421 l_territory,
4422 'PDF');
4423 IF l_debug_on THEN
4424 wsh_debug_sv.log(l_module_name,'Return Status After Calling fnd_request.add_layout ',l_status);
4425 END IF;
4426 IF (l_status=FALSE) THEN
4427 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4428 wsh_util_core.add_message(x_return_status,l_module_name);
4429 END IF;
4430 --}
4431 END IF;
4432 x_conc_prog_name := l_conc_prog_name;
4433
4434 IF l_debug_on THEN
4435 WSH_DEBUG_SV.pop(l_module_name);
4436 END IF;
4437
4438 EXCEPTION
4439 WHEN get_shipping_param_err THEN
4440 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4441 FND_MESSAGE.Set_Name('WSH', 'WSH_PARAM_NOT_DEFINED');
4442 FND_MESSAGE.Set_Token('ORGANIZATION_CODE',
4443 wsh_util_core.get_org_name(p_organization_id));
4444 wsh_util_core.add_message(x_return_status,l_module_name);
4445
4446 --
4447 IF l_debug_on THEN
4448 WSH_DEBUG_SV.logmsg(l_module_name,'Failed to get Shipping Parameters',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4449 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:GET_SHIPPING_PARAM_ERR');
4450 END IF;
4451
4452 WHEN OTHERS THEN
4453
4454 wsh_util_core.default_handler('WSH_DOCUMENT_PVT.set_template',l_module_name);
4455 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
4456 --
4457 IF l_debug_on THEN
4458 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4459 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4460 END IF;
4461 End set_template;
4462
4463
4464 END WSH_Document_PVT;
[Home] [Help]