1 PACKAGE BODY WSH_Document_PVT AS
2 -- $Header: WSHVPACB.pls 120.3.12000000.2 2007/01/23 18:32:52 rlanka 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 >= 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
1059 -- get the method code for the sequence assigned to this category.
1060 -- does not support multiple sequences being assigned to a category.
1061 -- in such cases the first assignment's method code only is used.
1062
1063 -- LE Uptake
1064 CURSOR assignment_csr (c_category_code IN VARCHAR2) IS
1065 SELECT
1066 method_code,
1067 doc_sequence_id
1068 FROM
1069 fnd_doc_sequence_assignments
1070 WHERE application_id = p_application_id
1071 AND set_of_books_id = p_ledger_id
1072 AND category_code = c_category_code
1073 AND start_date <= sysdate
1074 AND ( (end_date IS NULL)
1075 OR
1076 (end_date >= sysdate)
1077 AND start_date <= sysdate
1078 );
1079
1080 CURSOR delivery_csr (c_delivery_id IN NUMBER) IS
1081 SELECT
1082 delivery_id
1083 FROM
1084 wsh_new_deliveries
1085 WHERE delivery_id = c_delivery_id;
1086
1087 CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
1088 SELECT
1089 delivery_id
1090 FROM
1091 wsh_delivery_legs
1092 WHERE delivery_leg_id = c_delivery_leg_id;
1093
1094 --
1095 --Bug 4284167 (FP Bug 4149501)
1096 --
1097 CURSOR get_lock_on_leg(p_delivery_leg_id IN NUMBER) IS
1098 SELECT p_delivery_leg_id
1099 FROM wsh_delivery_legs
1100 WHERE delivery_leg_id = p_delivery_leg_id
1101 FOR UPDATE NOWAIT;
1102
1103 document_rec document_csr%rowtype;
1104 category_rec category_csr%rowtype;
1105 assignment_rec assignment_csr%rowtype;
1106 delivery_rec delivery_csr%rowtype;
1107 delivery_id_rec delivery_id_csr%rowtype;
1108 trip_stop_rec trip_stop_csr%rowtype;
1109
1110 --Bug 4284167 (FP Bug 4149501)
1111 lock_detected EXCEPTION;
1112 PRAGMA EXCEPTION_INIT( lock_detected, -00054);
1113 --
1114 l_debug_on BOOLEAN;
1115 --
1116 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_DOCUMENT';
1117 --
1118 BEGIN
1119 -- since this procedure does DML issue savepoint
1120 --
1121 -- Debug Statements
1122 --
1123 --
1124 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1125 --
1126 IF l_debug_on IS NULL
1127 THEN
1128 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1129 END IF;
1130 --
1131 IF l_debug_on THEN
1132 WSH_DEBUG_SV.push(l_module_name);
1133 --
1134 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
1135 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
1136 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
1137 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
1138 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
1139 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
1140 WSH_DEBUG_SV.log(l_module_name,'P_APPLICATION_ID',P_APPLICATION_ID);
1141 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
1142 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
1143 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_SUB_TYPE',P_DOCUMENT_SUB_TYPE);
1144 WSH_DEBUG_SV.log(l_module_name,'P_LEDGER_ID',P_LEDGER_ID); -- LE Uptake
1145 WSH_DEBUG_SV.log(l_module_name,'P_CONSOLIDATE_OPTION',P_CONSOLIDATE_OPTION);
1146 WSH_DEBUG_SV.log(l_module_name,'P_MANUAL_SEQUENCE_NUMBER',P_MANUAL_SEQUENCE_NUMBER);
1147 END IF;
1148 --
1149 SAVEPOINT WSH_Document_PVT;
1150
1151 -- standard call to check for call compatibility.
1152 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1153 p_api_version,
1154 l_api_name,
1155 g_pkg_name ) THEN
1156 RAISE FND_API.g_exc_unexpected_error;
1157 END IF;
1158
1159 -- initialize message list if p_init_msg_list is set to TRUE.
1160
1161 IF FND_API.to_boolean( p_init_msg_list ) THEN
1162 FND_MSG_PUB.initialize;
1163 END IF;
1164
1165 -- initialize API return status to success
1166 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
1167 OPEN category_csr;
1168 FETCH category_csr INTO category_rec;
1169 IF category_csr%NOTFOUND
1170 THEN
1171 CLOSE category_csr;
1172 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_CATEGORY_MISSING');
1173 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
1174 RAISE FND_API.G_EXC_ERROR;
1175 END IF;
1176 l_doc_sequence_category_id := category_rec.doc_sequence_category_id;
1177 CLOSE category_csr;
1178
1179 -- get the method code for the sequence assigned to this category.
1180 -- does not support multiple sequences being assigned to a category.
1181 -- in such cases the first assignment's method code only is used.
1182
1183 OPEN assignment_csr(category_rec.category_code);
1184 FETCH assignment_csr INTO assignment_rec;
1185 IF assignment_csr%NOTFOUND THEN
1186 CLOSE assignment_csr;
1187 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_ASSIGNMENT_MISSING');
1188 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
1189 RAISE FND_API.G_EXC_ERROR;
1190 END IF;
1191 l_doc_sequence_id := assignment_rec.doc_sequence_id;
1192 CLOSE assignment_csr;
1193
1194 -------------------------------------------------------------
1195 -- Initialize the entity_name based on the document type --
1196 -- If the entity is Delivery leg, look up its delivery id --
1197 -- to be used to build the child delivery table later --
1198 -------------------------------------------------------------
1199
1200 l_entity_name := Init_Entity_Name (p_document_type, p_entity_name);
1201 IF l_entity_name = 'WSH_DELIVERY_LEGS'
1202 THEN
1203 OPEN delivery_id_csr (p_entity_id);
1204 FETCH delivery_id_csr INTO delivery_id_rec;
1205 l_delivery_id := delivery_id_rec.delivery_id;
1206 CLOSE delivery_id_csr;
1207 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
1208 THEN
1209 l_delivery_id := p_entity_id;
1210 END IF;
1211
1212 ----------------------------------------------
1213 -- if the document is for delivery leg --
1214 -- get its pick up and drop off trip stops --
1215 ----------------------------------------------
1216 IF l_entity_name = 'WSH_DELIVERY_LEGS'
1217 THEN
1218 OPEN trip_stop_csr;
1219 FETCH trip_stop_csr INTO trip_stop_rec;
1220 CLOSE trip_stop_csr;
1221 END IF;
1222
1223 IF l_entity_name <> 'WSH_TRIPS' THEN
1224 ----------------------------------------------
1225 -- Validate the delivery id --
1226 ----------------------------------------------
1227 OPEN delivery_csr (l_delivery_id);
1228 FETCH delivery_csr INTO delivery_rec;
1229 IF delivery_csr%NOTFOUND
1230 THEN
1231 CLOSE delivery_csr;
1232 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_INVALID_DELIVERY');
1233 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
1234 RAISE FND_API.G_EXC_ERROR;
1235 END IF;
1236 CLOSE delivery_csr;
1237
1238 ---------------------------------------------------------------
1239 -- based on the consolidate_option identify the delivery ids --
1240 -- to create documents for --
1241 ---------------------------------------------------------------
1242
1243 IF (p_consolidate_option IN ('BOTH', 'SEPARATE'))
1244 THEN
1245 GET_ChildDeliveryTab ( l_delivery_id , l_delivery_id_tab );
1246 END IF;
1247
1248 IF p_consolidate_option IN ('BOTH', 'CONSOLIDATE') THEN
1249 l_table_count := l_delivery_id_tab.count;
1250 l_delivery_id_tab.extend;
1251 l_delivery_id_tab(l_table_count+1) := l_delivery_id;
1252 END IF;
1253
1254 IF NOT l_delivery_id_tab.EXISTS(1) THEN
1255 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_INVALID_DELIVERY');
1256 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
1257 RAISE FND_API.G_EXC_ERROR;
1258 END IF;
1259
1260
1261 FOR ctr IN 1..l_delivery_id_tab.count LOOP
1262
1263 -----------------------------------------------------------
1264 -- For each delivery, if the docuement is required for --
1265 -- delivery leg entity then identify the delivery leg id --
1266 -----------------------------------------------------------
1267
1268 IF l_entity_name = 'WSH_DELIVERY_LEGS'
1269 THEN
1270
1271 l_delivery_leg_id := Get_Delivery_Leg_Id ( l_delivery_id_tab(ctr)
1272 , trip_stop_rec.pick_up_stop_id
1273 , trip_stop_rec.drop_off_stop_id
1274 );
1275 END IF;
1276
1277 -----------------------------------------------------------
1278 -- For every entity check if a document of this type --
1279 -- already exists with OPEN status ( Here the entity --
1280 -- would be Deliveries in case of packing slips and --
1281 -- Delivery legs in case of Bill of Lading ) --
1282 -----------------------------------------------------------
1283
1284 IF document_csr%ISOPEN
1285 THEN
1286 CLOSE document_csr;
1287 END IF;
1288 IF l_entity_name = 'WSH_DELIVERY_LEGS'
1289 THEN
1290 OPEN document_csr ( l_entity_name
1291 , l_delivery_leg_id
1292 ) ;
1293 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
1294 THEN
1295 OPEN document_csr (l_entity_name, l_delivery_id_tab(ctr));
1296 END IF;
1297
1298 FETCH document_csr INTO document_rec;
1299 IF document_csr%FOUND THEN
1300 CLOSE document_csr;
1301
1302 --Fix for bug 3878973
1303 --If document exists already, just return success
1304
1305 IF l_debug_on THEN
1306 WSH_DEBUG_SV.logmsg(l_module_name, 'Doc Number exists already');
1307 WSH_DEBUG_SV.pop(l_module_name);
1308 END IF;
1309 RETURN;
1310 END IF;
1311 CLOSE document_csr;
1312
1313 l_seq_type := get_sequence_type (l_doc_sequence_id);
1314
1315 -- for manual seq the user input is considered
1316 IF l_seq_type = 'M'
1317 THEN
1318 l_sequence_number := p_manual_sequence_number;
1319 END IF;
1320
1321 -------------------------------------------------------------------------
1322 -- if the sequence type is automatic --
1323 -- call the FND API to get a new number for each delivery --
1324 -- if the sequence type is manual --
1325 -- if this is a parent delivery --
1326 -- call the FND API to validate the number given by the user --
1327 -- if this is a child delivery --
1328 -- use the same number given by the user for all child deliveries --
1329 -------------------------------------------------------------------------
1330 -- 2695602: Added type = 'G' below for handling Gapless Sequences
1331
1332 IF (l_seq_type = 'A')
1333 OR
1334 (l_seq_type = 'G')
1335 OR
1336 ((l_seq_type = 'M') AND (l_delivery_id_tab(ctr) = l_delivery_id) )
1337 THEN
1338 l_seq_return := FND_SEQNUM.get_seq_val
1339 ( app_id => p_application_id
1340 , cat_code => category_rec.category_code
1341 , sob_id => p_ledger_id -- LE Uptake
1342 , met_code => assignment_rec.method_code
1343 , trx_date => sysdate
1344 , seq_val => l_sequence_number
1345 , docseq_id => l_sequence_id );
1346 END IF;
1347 IF NVL(l_sequence_number,0) = 0
1348 THEN
1349 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_SEQ_ERROR');
1350 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
1351 RAISE FND_API.G_EXC_ERROR;
1352 END IF;
1353
1354 /* l_document_number := LTRIM(RTRIM(category_rec.prefix)) ||
1355 category_rec.delimiter ||
1356 LTRIM(RTRIM(TO_CHAR(l_sequence_number))) ||
1357 category_rec.delimiter ||
1358 LTRIM(RTRIM(category_rec.suffix)); */
1359
1360 /* Bug 1973913 begins */
1361
1362 IF category_rec.prefix is not NULL THEN
1363 l_prefix:= LTRIM(RTRIM(category_rec.prefix))||category_rec.delimiter;
1364 END IF;
1365 IF category_rec.suffix is not NULL THEN
1366 l_suffix:= category_rec.delimiter||LTRIM(RTRIM(category_rec.suffix));
1367 END IF;
1368
1369 -------------------------------------------------------------
1370 -- Bug 2310825 : Removed the LPAD which was being used to pad
1371 -- the sequence number with 0's.
1372 -------------------------------------------------------------
1373
1374 l_document_number:=l_prefix||LTRIM(RTRIM(TO_CHAR(l_sequence_number))) ||l_suffix;
1375
1376 /* Bug 1973913 ends */
1377
1378 -- if this is the parent delivery id then
1379 -- return the seq number to the calling program
1380
1381 IF l_delivery_id_tab(ctr) = l_delivery_id THEN
1382 x_document_number := l_document_number;
1383 END IF;
1384
1385 --{ Bug 3761178
1386 --
1387 -- The decode statements that were in the values clause of the coming insert stmt
1388 -- have been modified to make use of local variables for performance reasons.
1389 --
1390
1391 IF l_entity_name = 'WSH_DELIVERY_LEGS' THEN
1392 l_status := 'PLANNED';
1393 l_entity_id := l_delivery_leg_id;
1394 ELSIF l_entity_name ='WSH_NEW_DELIVERIES' THEN
1395 l_status := 'OPEN';
1396 l_entity_id := l_delivery_id_tab(ctr);
1397 END IF;
1398
1399 --}
1400
1401 ---------------------------------------------------------
1402 -- logic in insert statement: --
1403 -- --
1404 -- if the entity is delivery ( packing slips ) --
1405 -- then entity_name is WSH_NEW_DELIVERIES --
1406 -- and entity_id is delivery_id --
1407 -- and if there is consolidation --
1408 -- then for child deliveries --
1409 -- entity_name is WSH_NEW_DELIVERIES --
1410 -- entity_id is child delivery_id --
1411 -- if the entity is delivery leg ( bill of lading ) --
1412 -- then entity_name is WSH_DELIVERY_LEGS --
1413 -- and entity_id is delivery_leg_id --
1414 -- and if there is consolidation --
1415 -- then for child deliveries --
1416 -- entity_name is WSH_DELIVERY_LEGS --
1417 -- entity_id is the delivery_leg_id --
1418 -- that corresponds to the --
1419 -- child delivery_id, --
1420 -- master pick_up_stop_id, --
1421 -- master drop_off_stop_id --
1422 -- - - - - - - - - - - - - - - - - - - - - - - - - - - --
1423 -- Assumption here: --
1424 -- --
1425 -- All deliveries (even those contained in another --
1426 -- delivery for consolidation purposes) will have a --
1427 -- delivery leg created before the Bill of Lading can --
1428 -- be created. --
1429 ---------------------------------------------------------
1430 INSERT INTO wsh_document_instances
1431 ( document_instance_id
1432 , document_type
1433 , sequence_number
1434 , status
1435 , final_print_date
1436 , entity_name
1437 , entity_id
1438 , doc_sequence_category_id
1439 , created_by
1440 , creation_date
1441 , last_updated_by
1442 , last_update_date
1443 , last_update_login
1444 , program_application_id
1445 , program_id
1446 , program_update_date
1447 , request_id
1448 , attribute_category
1449 , attribute1
1450 , attribute2
1451 , attribute3
1452 , attribute4
1453 , attribute5
1454 , attribute6
1455 , attribute7
1456 , attribute8
1457 , attribute9
1458 , attribute10
1459 , attribute11
1460 , attribute12
1461 , attribute13
1462 , attribute14
1463 , attribute15
1464 )
1465 VALUES
1466 ( wsh_document_instances_s.nextval
1467 , p_document_type
1468 , l_document_number
1469 , l_status
1470 , null
1471 , l_entity_name
1472 , l_entity_id
1473 , l_doc_sequence_category_id
1474 , fnd_global.user_id
1475 , sysdate
1476 , fnd_global.user_id
1477 , sysdate
1478 , fnd_global.login_id
1479 , null
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 );
1500
1501 --
1502 --Bug 4284167 (FP Bug 4149501)ISSUED DATE OF A BOL IS NOT GETTING SYSTEM GENERATED.
1503 --
1504
1505 IF l_entity_name = 'WSH_DELIVERY_LEGS' THEN
1506
1507 OPEN get_lock_on_leg(l_delivery_leg_id);
1508
1509 FETCH get_lock_on_leg INTO l_delivery_leg_id;
1510 IF (get_lock_on_leg%FOUND) THEN
1511 UPDATE wsh_delivery_legs
1512 SET doc_date_issued = SYSDATE
1513 WHERE current of get_lock_on_leg;
1514 END IF;
1515 CLOSE get_lock_on_leg;
1516
1517 END IF;
1518
1519 END LOOP;
1520
1521 ELSE--for l_entity_name <> 'WSH_TRIPS'
1522
1523 --Check if document number for the entity type and the name
1524 --already exists.
1525 OPEN document_csr (p_entity_name, p_entity_id);
1526 FETCH document_csr INTO document_rec;
1527 IF document_csr%FOUND THEN
1528 CLOSE document_csr;
1529
1530 --Fix for bug 3878973
1531 --If document exists already, just return success
1532 IF l_debug_on THEN
1533 WSH_DEBUG_SV.logmsg(l_module_name, 'Doc Number exists already');
1534 WSH_DEBUG_SV.pop(l_module_name);
1535 END IF;
1536 RETURN;
1537 END IF;
1538 CLOSE document_csr;
1539
1540 l_seq_type := get_sequence_type (l_doc_sequence_id);
1541 -- for manual seq the user input is considered
1542 IF l_seq_type = 'M'
1543 THEN
1544 l_sequence_number := p_manual_sequence_number;
1545 END IF;
1546
1547 -------------------------------------------------------------------------
1548 -- if the sequence type is automatic or Gapless --
1549 -- call the FND API to get a new number --
1550 -- if the sequence type is manual --
1551 -- use the number given by the user --
1552 -------------------------------------------------------------------------
1553
1554 IF (l_seq_type = 'A')
1555 OR
1556 (l_seq_type = 'G')
1557 THEN
1558 l_seq_return := FND_SEQNUM.get_seq_val
1559 ( app_id => p_application_id
1560 , cat_code => category_rec.category_code
1561 , sob_id => p_ledger_id -- LE Uptake
1562 , met_code => assignment_rec.method_code
1563 , trx_date => sysdate
1564 , seq_val => l_sequence_number
1565 , docseq_id => l_sequence_id );
1566 END IF;
1567
1568 IF NVL(l_sequence_number,0) = 0
1569 THEN
1570 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_SEQ_ERROR');
1571 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
1572 RAISE FND_API.G_EXC_ERROR;
1573 END IF;
1574
1575 IF category_rec.prefix is not NULL THEN
1576 l_prefix:= LTRIM(RTRIM(category_rec.prefix))||category_rec.delimiter;
1577 END IF;
1578 IF category_rec.suffix is not NULL THEN
1579 l_suffix:= category_rec.delimiter||LTRIM(RTRIM(category_rec.suffix));
1580 END IF;
1581
1582 l_document_number:=l_prefix||LTRIM(RTRIM(TO_CHAR(l_sequence_number))) ||l_suffix;
1583
1584 x_document_number := l_document_number;
1585
1586 l_status := 'OPEN'; --bug # 3789154
1587
1588 INSERT INTO wsh_document_instances
1589 ( document_instance_id
1590 , document_type
1591 , sequence_number
1592 , status
1593 , final_print_date
1594 , entity_name
1595 , entity_id
1596 , doc_sequence_category_id
1597 , created_by
1598 , creation_date
1599 , last_updated_by
1600 , last_update_date
1601 , last_update_login
1602 , program_application_id
1603 , program_id
1604 , program_update_date
1605 , request_id
1606 , attribute_category
1607 , attribute1
1608 , attribute2
1609 , attribute3
1610 , attribute4
1611 , attribute5
1612 , attribute6
1613 , attribute7
1614 , attribute8
1615 , attribute9
1616 , attribute10
1617 , attribute11
1618 , attribute12
1619 , attribute13
1620 , attribute14
1621 , attribute15
1622 )
1623 VALUES
1624 ( wsh_document_instances_s.nextval
1625 , p_document_type
1626 , l_document_number
1627 , l_status --Bug# 3789154
1628 , null
1629 , p_entity_name
1630 , p_entity_id
1631 , l_doc_sequence_category_id
1632 , fnd_global.user_id
1633 , sysdate
1634 , fnd_global.user_id
1635 , sysdate
1636 , fnd_global.login_id
1637 , null
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 );
1658 END IF;--for l_entity_name <> 'WSH_TRIPS'
1659
1660 -- get message count and the message itself (if only one message)
1661 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1662 p_data => x_msg_data);
1663
1664 -- Standard check of p_commit.
1665 IF FND_API.to_boolean( p_commit ) THEN
1666 COMMIT WORK;
1667 END IF;
1668
1669
1670 --
1671 -- Debug Statements
1672 --
1673 IF l_debug_on THEN
1674 WSH_DEBUG_SV.pop(l_module_name);
1675 END IF;
1676 --
1677 EXCEPTION
1678
1679 WHEN lock_detected THEN --Bug 4284167 (FP Bug 4149501)
1680
1681 IF (get_lock_on_leg%ISOPEN) THEN
1682 CLOSE get_lock_on_leg;
1683 END IF;
1684 ROLLBACK to WSH_Document_PVT;
1685
1686 SELECT wnd.name INTO l_delivery_name
1687 FROM wsh_new_deliveries wnd, wsh_delivery_legs wdl
1688 WHERE wnd.delivery_id = wdl.delivery_id
1689 AND wdl.delivery_leg_id = l_delivery_leg_id;
1690
1691 FND_MESSAGE.SET_NAME('WSH',' WSH_DLVY_DEL_LEG_LOCK');
1692 FND_MESSAGE.SET_TOKEN('DEL_NAME',l_delivery_name);
1693 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
1694 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
1695
1696 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
1697 p_data => x_msg_data );
1698 IF l_debug_on THEN
1699 WSH_DEBUG_SV.logmsg(l_module_name,'Cannot lock delivery leg for update');
1700 END IF;
1701
1702 WHEN FND_API.g_exc_error THEN
1703 --Bug 4284167 (FP Bug 4149501)
1704 IF (get_lock_on_leg%ISOPEN) THEN
1705 CLOSE get_lock_on_leg;
1706 END IF;
1707
1708 ROLLBACK to WSH_Document_PVT;
1709 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
1710 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
1711 p_data => x_msg_data );
1712 --
1713 -- Debug Statements
1714 --
1715 IF l_debug_on THEN
1716 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1717 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1718 END IF;
1719 --
1720 WHEN FND_API.g_exc_unexpected_error THEN
1721 --Bug 4284167 (FP Bug 4149501)
1722 IF (get_lock_on_leg%ISOPEN) THEN
1723 CLOSE get_lock_on_leg;
1724 END IF;
1725
1726 ROLLBACK to WSH_Document_PVT;
1727 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
1728 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
1729 p_data => x_msg_data );
1730 --
1731 -- Debug Statements
1732 --
1733 IF l_debug_on THEN
1734 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1735 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1736 END IF;
1737 --
1738 WHEN others THEN
1739 --Bug 4284167 (FP Bug 4149501)
1740 IF (get_lock_on_leg%ISOPEN) THEN
1741 CLOSE get_lock_on_leg;
1742 END IF;
1743
1744 ROLLBACK to WSH_Document_PVT;
1745 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
1746 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
1747 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
1748 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
1749 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
1750 WSH_UTIL_CORE.add_message (x_return_status);
1751 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
1752 p_data => x_msg_data );
1753
1754 --
1755 -- Debug Statements
1756 --
1757 IF l_debug_on THEN
1758 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1759 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1760 END IF;
1761 --
1762 END Create_Document;
1763
1764
1765 -----------------------------------------------------------------------------
1766 -- PROCEDURE : Update_Document PUBLIC
1767 -- VERSION : 1.0
1768 -- COMMENT : Updates a document (pack slip, bill of lading) for a delivery
1769 --
1770 -- PARAMETER LIST :
1771 --
1772 -- IN
1773 --
1774 -- p_api_version known API version
1775 -- p_init_msg_list should API reset message stack (default: false)
1776 -- p_commit should API do a commit (default: false)
1777 -- p_validation_level extent of validation done in the API (not used)
1778 -- p_entity_name Entity for which the document is being updated
1779 -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
1780 -- p_entity_id Entity id that the document belongs to
1781 -- example: delivery_id, delivery_leg_id, etc
1782 -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
1783 -- p_pod_flag pod_flag for the document
1784 -- p_pod_by pod_by for the document
1785 -- p_pod_date pod_date for the document
1786 -- p_reason_of_transport reason of transport that describes the delivery
1787 -- p_description external aspect of the delivery
1788 -- p_cod_amount cod_amount of the document
1789 -- p_cod_currency_code cod_currency_code of the document
1790 -- p_cod_remit_to cod_remit_to of the document
1791 -- p_cod_charge_paid_by cod_charge_paid_by of the document
1792 -- p_problem_contact_reference problem_contact_referene of the document
1793 -- p_bill_freight_to bill_freight_to of the document
1794 -- p_carried_by carried_by of the document
1795 -- p_port_of_loading port_of_loading of the docucent
1796 -- p_port_of_discharge port_of_discharge of the document
1797 -- p_booking_office booking_office of the document
1798 -- p_booking_number booking_number of the document
1799 -- p_service_contract service_contract of the document
1800 -- p_shipper_export_ref shipper_export_ref of the document
1801 -- p_carrier_export_ref carrier_export_ref of the document
1802 -- p_bol_notify_party bol_notify_party of the document
1803 -- p_supplier_code supplier_code of the document
1804 -- p_aetc_number aetc_number of the document
1805 -- p_shipper_signed_by shipper_signed_by of the document
1806 -- p_shipper_date shipper_date of the document
1807 -- p_carrier_signed_by carrier_signed_by of the document
1808 -- p_carrier_date carrier_date of the document
1809 -- p_bol_issue_office bol_issue_office of the document
1810 -- p_bol_issued_by bol_issued_by of the document
1811 -- p_bol_date_issued bol_date_issued of the document
1812 -- p_shipper_hm_by shipper_bm_by of the document
1813 -- p_shipper_hm_date shipper_hm_date of the document
1814 -- p_carrier_hm_by carrier_hm_by of the document
1815 -- p_carrier_hm_date carrier_hm_date of the document
1816 -- p_ledger_id LEDGER id attached to the calling program (
1817 -- should be same as SOB used to setup the
1818 -- document category/assignment )
1819 -- p_consolidate_option calling program's choice to update document(s)
1820 -- for this parent delivery only ('CONSOLIDATE')
1821 -- or for child dels of this delivery ('SEPARATE')
1822 -- or both parent and child deliveries ('BOTH')
1823 --
1824 -- OUT
1825 --
1826 -- x_msg_count number of messages in stack
1827 -- x_msg_data message if there is only one message in stack
1828 -- x_return_status API return status ('S', 'E', 'U')
1829 --
1830 -- PRE-CONDITIONS : None
1831 -- POST-CONDITIONS : None
1832 -- EXCEPTIONS : None
1833 -----------------------------------------------------------------------------
1834
1835 PROCEDURE Update_Document
1836 ( p_api_version IN NUMBER
1837 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
1838 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
1839 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
1840 , x_return_status OUT NOCOPY VARCHAR2
1841 , x_msg_count OUT NOCOPY NUMBER
1842 , x_msg_data OUT NOCOPY VARCHAR2
1843 , p_entity_name IN VARCHAR2 DEFAULT NULL
1844 , p_entity_id IN NUMBER
1845 , p_document_type IN VARCHAR2
1846 , p_ledger_id IN NUMBER -- LE Uptake
1847 , p_consolidate_option IN VARCHAR2 DEFAULT 'BOTH'
1848 ) IS
1849 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Document';
1850 L_API_VERSION CONSTANT NUMBER := 1.0;
1851 l_delivery_id_tab delivery_id_tabtype := delivery_id_tabtype();
1852 l_delivery_id wsh_new_deliveries.delivery_id%type;
1853 l_delivery_leg_id wsh_delivery_legs.delivery_leg_id%type;
1854 l_table_count NUMBER;
1855 l_entity_name wsh_document_instances.entity_name%type;
1856
1857 CURSOR old_values_csr (c_entity_name IN VARCHAR2, c_entity_id IN NUMBER) IS
1858
1859 --Changed for BUG#3330869
1860 SELECT status
1861 --SELECT *
1862 FROM
1863 wsh_document_instances
1864 WHERE entity_name = c_entity_name
1865 AND entity_id = c_entity_id
1866 AND document_type = p_document_type
1867 AND status not in ('COMPLETE', 'CANCELLED')
1868 FOR UPDATE;
1869
1870 CURSOR trip_stop_csr IS
1871 SELECT
1872 delivery_id
1873 , pick_up_stop_id
1874 , drop_off_stop_id
1875 FROM
1876 wsh_delivery_legs
1877 WHERE delivery_leg_id = p_entity_id;
1878
1879 CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
1880 SELECT
1881 delivery_id
1882 FROM
1883 wsh_delivery_legs
1884 WHERE delivery_leg_id = c_delivery_leg_id;
1885
1886 old_values_rec old_values_csr%rowtype;
1887 trip_stop_rec trip_stop_csr%rowtype;
1888 delivery_id_rec delivery_id_csr%rowtype;
1889
1890 --
1891 l_debug_on BOOLEAN;
1892 --
1893 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DOCUMENT';
1894 --
1895 BEGIN
1896
1897 -- since this procedure does DML issue savepoint
1898
1899 --
1900 -- Debug Statements
1901 --
1902 --
1903 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1904 --
1905 IF l_debug_on IS NULL
1906 THEN
1907 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1908 END IF;
1909 --
1910 IF l_debug_on THEN
1911 WSH_DEBUG_SV.push(l_module_name);
1912 --
1913 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
1914 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
1915 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
1916 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
1917 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
1918 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
1919 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
1920 WSH_DEBUG_SV.log(l_module_name,'P_LEDGER_ID',P_LEDGER_ID); -- LE Uptake
1921 WSH_DEBUG_SV.log(l_module_name,'P_CONSOLIDATE_OPTION',P_CONSOLIDATE_OPTION);
1922 END IF;
1923 --
1924 SAVEPOINT WSH_Document_PVT;
1925
1926 -- standard call to check for call compatibility.
1927 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1928 p_api_version,
1929 l_api_name,
1930 g_pkg_name ) THEN
1931 RAISE FND_API.g_exc_unexpected_error;
1932 END IF;
1933
1934 -- initialize message list if p_init_msg_list is set to TRUE.
1935
1936 IF FND_API.to_boolean( p_init_msg_list ) THEN
1937 FND_MSG_PUB.initialize;
1938 END IF;
1939
1940 -- initialize API return status to success
1941 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
1942
1943 -------------------------------------------------------------
1944 -- Initialize the entity_name based on the document type --
1945 -- If the entity is Delivery leg, look up its delivery id --
1946 -- to be used to build the child delivery table later --
1947 -------------------------------------------------------------
1948
1949 l_entity_name := Init_Entity_Name (p_document_type, p_entity_name);
1950 IF l_entity_name = 'WSH_DELIVERY_LEGS'
1951 THEN
1952 OPEN delivery_id_csr (p_entity_id);
1953 FETCH delivery_id_csr INTO delivery_id_rec;
1954 l_delivery_id := delivery_id_rec.delivery_id;
1955 CLOSE delivery_id_csr;
1956 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
1957 THEN
1958 l_delivery_id := p_entity_id;
1959 END IF;
1960
1961 ----------------------------------------------
1962 -- if the document is for delivery leg --
1963 -- get its pick up and drop off trip stops --
1964 ----------------------------------------------
1965
1966 IF l_entity_name = 'WSH_DELIVERY_LEGS'
1967 THEN
1968 OPEN trip_stop_csr;
1969 FETCH trip_stop_csr INTO trip_stop_rec;
1970 CLOSE trip_stop_csr;
1971 END IF;
1972
1973 ---------------------------------------------------------------
1974 -- based on the consolidate_option identify the delivery ids --
1975 -- to make the document updates --
1976 ---------------------------------------------------------------
1977
1978 IF (p_consolidate_option IN ('BOTH', 'SEPARATE')) THEN
1979 GET_ChildDeliveryTab ( l_delivery_id
1980 , l_delivery_id_tab );
1981 END IF;
1982
1983 IF p_consolidate_option IN ('BOTH', 'CONSOLIDATE') THEN
1984 l_table_count := l_delivery_id_tab.count;
1985 l_delivery_id_tab.extend;
1986 l_delivery_id_tab(l_table_count+1) := l_delivery_id;
1987 END IF;
1988
1989 IF NOT l_delivery_id_tab.EXISTS(1) THEN
1990 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_INVALID_DELIVERY');
1991 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
1992 RAISE FND_API.G_EXC_ERROR;
1993 END IF;
1994
1995 FOR ctr IN 1..l_delivery_id_tab.count LOOP
1996
1997 -----------------------------------------------------------
1998 -- For each delivery, if the docuement relates to --
1999 -- delivery leg entity then identify the delivery leg id --
2000 -----------------------------------------------------------
2001
2002 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2003 THEN
2004 l_delivery_leg_id := Get_Delivery_Leg_Id ( l_delivery_id_tab(ctr)
2005 , trip_stop_rec.pick_up_stop_id
2006 , trip_stop_rec.drop_off_stop_id
2007 );
2008 END IF;
2009
2010 IF old_values_csr%ISOPEN
2011 THEN
2012 CLOSE old_values_csr;
2013 END IF;
2014 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2015 THEN
2016 OPEN old_values_csr (l_entity_name, l_delivery_leg_id);
2017 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
2018 THEN
2019 OPEN old_values_csr (l_entity_name, l_delivery_id_tab(ctr));
2020 END IF;
2021
2022 FETCH old_values_csr INTO old_values_rec;
2023 IF old_values_csr%NOTFOUND THEN
2024 CLOSE old_values_csr;
2025 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_MISSING');
2026 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
2027 RAISE FND_API.G_EXC_ERROR;
2028 END IF;
2029
2030 -- if the document is cancelled, raise error
2031 IF old_values_rec.status = 'CANCELLED' THEN
2032 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_INVALID_DELIVERY');
2033 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
2034 RAISE FND_API.G_EXC_ERROR;
2035 END IF;
2036
2037 -- need to change this update within loop to a bulk update later.
2038 -- probably use temporary tables with a join
2039
2040 UPDATE wsh_document_instances
2041 SET
2042 last_update_date = sysdate
2043 , last_updated_by = fnd_global.user_id
2044 , last_update_login = fnd_global.login_id
2045 WHERE CURRENT OF old_values_csr;
2046
2047 END LOOP;
2048 IF old_values_csr%ISOPEN
2049 THEN
2050 CLOSE old_values_csr;
2051 END IF;
2052
2053 -- get message count and the message itself (if only one message)
2054 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2055 p_data => x_msg_data);
2056 -- Standard check of p_commit.
2057 IF FND_API.to_boolean( p_commit ) THEN
2058 COMMIT WORK;
2059 END IF;
2060 --
2061 -- Debug Statements
2062 --
2063 IF l_debug_on THEN
2064 WSH_DEBUG_SV.pop(l_module_name);
2065 END IF;
2066 --
2067 EXCEPTION
2068
2069 WHEN FND_API.g_exc_error THEN
2070 ROLLBACK to WSH_Document_PVT;
2071 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
2072 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
2073 p_data => x_msg_data );
2074
2075 --
2076 -- Debug Statements
2077 --
2078 IF l_debug_on THEN
2079 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2080 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2081 END IF;
2082 --
2083 WHEN FND_API.g_exc_unexpected_error THEN
2084 ROLLBACK to WSH_Document_PVT;
2085 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
2086 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
2087 p_data => x_msg_data );
2088
2089 --
2090 -- Debug Statements
2091 --
2092 IF l_debug_on THEN
2093 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2094 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2095 END IF;
2096 --
2097 WHEN others THEN
2098 ROLLBACK to WSH_Document_PVT;
2099 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
2100 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
2101 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
2102 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
2103 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
2104 WSH_UTIL_CORE.add_message (x_return_status);
2105 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
2106 p_data => x_msg_data );
2107 --
2108 -- Debug Statements
2109 --
2110 IF l_debug_on THEN
2111 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2112 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2113 END IF;
2114 --
2115 END Update_Document;
2116
2117
2118 -----------------------------------------------------------------------------
2119 -- PROCEDURE : Cancel_Document PUBLIC
2120 -- VERSION : 1.0
2121 -- COMMENT : Updates the status of a document to 'CANCELLED'
2122 --
2123 -- PARAMETER LIST :
2124 --
2125 -- IN
2126 --
2127 -- p_api_version known API version
2128 -- p_init_msg_list should API reset message stack (default: false)
2129 -- p_commit should API do a commit (default: false)
2130 -- p_validation_level extent of validation done in the API (not used)
2131 -- p_entity_name Entity for which the document is being cancelled
2132 -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
2133 -- p_entity_id Entity id that the document belongs to
2134 -- example: delivery_id, delivery_leg_id, etc
2135 -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
2136 -- p_consolidate_option calling program's choice to cancel document(s)
2137 -- for this parent delivery only ('CONSOLIDATE')
2138 -- or for child dels of this delivery ('SEPARATE')
2139 -- or both parent and child deliveries ('BOTH')
2140 --
2141 -- OUT
2142 --
2143 -- x_msg_count number of messages in stack
2144 -- x_msg_data message if there is only one message in stack
2145 -- x_return_status API return status ('S', 'E', 'U')
2146 --
2147 -- PRE-CONDITIONS : None
2148 -- POST-CONDITIONS : None
2149 -- EXCEPTIONS : None
2150 -----------------------------------------------------------------------------
2151
2152
2153 PROCEDURE Cancel_Document
2154 ( p_api_version IN NUMBER
2155 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
2156 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
2157 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
2158 , x_return_status OUT NOCOPY VARCHAR2
2159 , x_msg_count OUT NOCOPY NUMBER
2160 , x_msg_data OUT NOCOPY VARCHAR2
2161 , p_entity_name IN VARCHAR2 DEFAULT NULL
2162 , p_entity_id IN NUMBER
2163 , p_document_type IN VARCHAR2
2164 , p_consolidate_option IN VARCHAR2 DEFAULT 'BOTH'
2165 )
2166 IS
2167 L_API_NAME CONSTANT VARCHAR2(30) := 'WSH_Document_PVT';
2168 L_API_VERSION CONSTANT NUMBER := 1.0;
2169 l_delivery_id_tab delivery_id_tabtype := delivery_id_tabtype();
2170 l_table_count NUMBER;
2171 l_entity_name wsh_document_instances.entity_name%type;
2172 l_delivery_id wsh_new_deliveries.delivery_id%type;
2173 l_delivery_leg_id wsh_delivery_legs.delivery_leg_id%type;
2174
2175
2176 -------------------------------------------------------------------------
2177 -- cursor to fetch the current document (in PLAN/OPEN status) of --
2178 -- the entity. Assumes to get only one row because the delivery UI --
2179 -- currently enforces it (for both BOL and Pack Slips). If there is --
2180 -- a change to this behavior the cursor definition would need change --
2181 -------------------------------------------------------------------------
2182
2183 CURSOR status_csr (c_entity_name IN VARCHAR2, c_entity_id IN NUMBER) IS
2184 SELECT
2185 entity_id
2186 , status
2187 FROM
2188 wsh_document_instances
2189 WHERE entity_name = c_entity_name
2190 AND entity_id = c_entity_id
2191 AND document_type = p_document_type
2192 AND status in ('OPEN', 'PLANNED')
2193 FOR UPDATE;
2194
2195 CURSOR trip_stop_csr IS
2196 SELECT
2197 delivery_id
2198 , pick_up_stop_id
2199 , drop_off_stop_id
2200 FROM
2201 wsh_delivery_legs
2202 WHERE delivery_leg_id = p_entity_id;
2203
2204 CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
2205 SELECT
2206 delivery_id
2207 FROM
2208 wsh_delivery_legs
2209 WHERE delivery_leg_id = c_delivery_leg_id;
2210
2211 trip_stop_rec trip_stop_csr%rowtype;
2212 delivery_id_rec delivery_id_csr%rowtype;
2213 status_rec status_csr%rowtype;
2214
2215 --
2216 l_debug_on BOOLEAN;
2217 --
2218 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CANCEL_DOCUMENT';
2219 --
2220 BEGIN
2221 -- since this procedure does DML issue savepoint
2222 --
2223 -- Debug Statements
2224 --
2225 --
2226 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2227 --
2228 IF l_debug_on IS NULL
2229 THEN
2230 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2231 END IF;
2232 --
2233 IF l_debug_on THEN
2234 WSH_DEBUG_SV.push(l_module_name);
2235 --
2236 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
2237 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
2238 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
2239 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
2240 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
2241 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
2242 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
2243 WSH_DEBUG_SV.log(l_module_name,'P_CONSOLIDATE_OPTION',P_CONSOLIDATE_OPTION);
2244 END IF;
2245 --
2246 SAVEPOINT WSH_Document_PVT;
2247
2248 -- standard call to check for call compatibility.
2249 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2250 p_api_version,
2251 l_api_name,
2252 g_pkg_name ) THEN
2253 RAISE FND_API.g_exc_unexpected_error;
2254 END IF;
2255
2256 -- initialize message list if p_init_msg_list is set to TRUE.
2257
2258 IF FND_API.to_boolean( p_init_msg_list ) THEN
2259 FND_MSG_PUB.initialize;
2260 END IF;
2261
2262 -- initialize API return status to success
2263 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
2264
2265 -------------------------------------------------------------
2266 -- Initialize the entity_name based on the document type --
2267 -- If the entity is Delivery leg, look up its delivery id --
2268 -- to be used to build the child delivery table later --
2269 -------------------------------------------------------------
2270
2271 l_entity_name := Init_Entity_Name (p_document_type, p_entity_name);
2272
2273 IF l_entity_name <> 'WSH_TRIPS' THEN
2274
2275 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2276 THEN
2277 OPEN delivery_id_csr (p_entity_id);
2278 FETCH delivery_id_csr INTO delivery_id_rec;
2279 l_delivery_id := delivery_id_rec.delivery_id;
2280 CLOSE delivery_id_csr;
2281 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
2282 THEN
2283 l_delivery_id := p_entity_id;
2284 END IF;
2285
2286 ----------------------------------------------
2287 -- if the document is for delivery leg --
2288 -- get its pick up and drop off trip stops --
2289 ----------------------------------------------
2290
2291 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2292 THEN
2293 OPEN trip_stop_csr;
2294 FETCH trip_stop_csr INTO trip_stop_rec;
2295 CLOSE trip_stop_csr;
2296 END IF;
2297
2298 ---------------------------------------------------------------
2299 -- based on the consolidate_option identify the delivery ids --
2300 -- to cancel documents for --
2301 ---------------------------------------------------------------
2302
2303 IF (p_consolidate_option IN ('BOTH', 'SEPARATE')) THEN
2304 GET_ChildDeliveryTab ( l_delivery_id
2305 , l_delivery_id_tab );
2306 END IF;
2307
2308 IF p_consolidate_option IN ('BOTH', 'CONSOLIDATE') THEN
2309 l_table_count := l_delivery_id_tab.count;
2310 l_delivery_id_tab.extend;
2311 l_delivery_id_tab(l_table_count+1) := l_delivery_id;
2312 END IF;
2313
2314 IF NOT l_delivery_id_tab.EXISTS(1) THEN
2315 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_INVALID_DELIVERY');
2316 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
2317 RAISE FND_API.G_EXC_ERROR;
2318 END IF;
2319
2320 FOR ctr IN 1..l_delivery_id_tab.count LOOP
2321
2322 -----------------------------------------------------------
2323 -- For each delivery, if the docuement relates to --
2324 -- delivery leg entity then identify the delivery leg id --
2325 -----------------------------------------------------------
2326
2327 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2328 THEN
2329 l_delivery_leg_id := Get_Delivery_Leg_Id ( l_delivery_id_tab(ctr)
2330 , trip_stop_rec.pick_up_stop_id
2331 , trip_stop_rec.drop_off_stop_id
2332 );
2333 END IF;
2334
2335 IF status_csr%ISOPEN
2336 THEN
2337 CLOSE status_csr;
2338 END IF;
2339 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2340 THEN
2341 OPEN status_csr (l_entity_name, l_delivery_leg_id);
2342 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
2343 THEN
2344 OPEN status_csr (l_entity_name, l_delivery_id_tab(ctr));
2345 END IF;
2346
2347 FETCH status_csr INTO status_rec;
2348 IF status_csr%NOTFOUND THEN
2349 CLOSE status_csr;
2350 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_MISSING');
2351 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
2352 RAISE FND_API.G_EXC_ERROR;
2353 END IF;
2354
2355 UPDATE wsh_document_instances
2356 SET status = 'CANCELLED'
2357 , last_update_date = sysdate
2358 , last_updated_by = fnd_global.user_id
2359 , last_update_login = fnd_global.login_id
2360 WHERE CURRENT OF status_csr;
2361
2362 END LOOP;
2363 IF status_csr%ISOPEN
2364 THEN
2365 CLOSE status_csr;
2366 END IF;
2367
2368 ELSIF l_entity_name = 'WSH_TRIPS' THEN
2369
2370 UPDATE wsh_document_instances
2371 SET status = 'CANCELLED'
2372 , last_update_date = sysdate
2373 , last_updated_by = fnd_global.user_id
2374 , last_update_login = fnd_global.login_id
2375 WHERE entity_name = l_entity_name
2376 AND entity_id = p_entity_id
2377 AND document_type = p_document_type
2378 AND status in ('OPEN', 'PLANNED');
2379
2380 END IF;
2381 -- get message count and the message itself (if only one message)
2382 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2383 p_data => x_msg_data);
2384 -- Standard check of p_commit.
2385 IF FND_API.to_boolean( p_commit ) THEN
2386 COMMIT WORK;
2387 END IF;
2388 --
2389 -- Debug Statements
2390 --
2391 IF l_debug_on THEN
2392 WSH_DEBUG_SV.pop(l_module_name);
2393 END IF;
2394 --
2395 EXCEPTION
2396
2397 WHEN FND_API.g_exc_error THEN
2398 ROLLBACK to WSH_Document_PVT;
2399 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
2400 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
2401 p_data => x_msg_data );
2402
2403 --
2404 -- Debug Statements
2405 --
2406 IF l_debug_on THEN
2407 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2408 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2409 END IF;
2410 --
2411 WHEN FND_API.g_exc_unexpected_error THEN
2412 ROLLBACK to WSH_Document_PVT;
2413 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
2414 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
2415 p_data => x_msg_data );
2416
2417 --
2418 -- Debug Statements
2419 --
2420 IF l_debug_on THEN
2421 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2422 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2423 END IF;
2424 --
2425 WHEN others THEN
2426 ROLLBACK to WSH_Document_PVT;
2427 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
2428 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
2429 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
2430 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
2431 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
2432 WSH_UTIL_CORE.add_message (x_return_status);
2433 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
2434 p_data => x_msg_data );
2435 --
2436 -- Debug Statements
2437 --
2438 IF l_debug_on THEN
2439 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2440 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2441 END IF;
2442 --
2443 END Cancel_Document;
2444
2445 -----------------------------------------------------------------------------
2446 -- PROCEDURE : Open_Document PUBLIC
2447 -- VERSION : 1.0
2448 -- COMMENT : Updates the status of a document to 'OPEN'
2449 --
2450 -- PARAMETER LIST :
2451 --
2452 -- IN
2453 --
2454 -- p_api_version known API version
2455 -- p_init_msg_list should API reset message stack (default: false)
2456 -- p_commit should API do a commit (default: false)
2457 -- p_validation_level extent of validation done in the API (not used)
2458 -- p_entity_name Entity for which the document is being opened
2459 -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
2460 -- p_entity_id Entity id that the document belongs to
2461 -- example: delivery_id, delivery_leg_id, etc
2462 -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
2463 -- p_consolidate_option calling program's choice to open document(s)
2464 -- for this parent delivery only ('CONSOLIDATE')
2465 -- or for child dels of this delivery ('SEPARATE')
2466 -- or both parent and child deliveries ('BOTH')
2467 --
2468 -- OUT
2469 --
2470 -- x_msg_count number of messages in stack
2471 -- x_msg_data message if there is only one message in stack
2472 -- x_return_status API return status ('S', 'E', 'U')
2473 --
2474 -- PRE-CONDITIONS : None
2475 -- POST-CONDITIONS : None
2476 -- EXCEPTIONS : None
2477 -----------------------------------------------------------------------------
2478
2479
2480 PROCEDURE Open_Document
2481 ( p_api_version IN NUMBER
2482 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
2483 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
2484 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
2485 , x_return_status OUT NOCOPY VARCHAR2
2486 , x_msg_count OUT NOCOPY NUMBER
2487 , x_msg_data OUT NOCOPY VARCHAR2
2488 , p_entity_name IN VARCHAR2 DEFAULT NULL
2489 , p_entity_id IN NUMBER
2490 , p_document_type IN VARCHAR2
2491 , p_consolidate_option IN VARCHAR2 DEFAULT 'BOTH'
2492 )
2493 IS
2494 L_API_NAME CONSTANT VARCHAR2(30) := 'WSH_Document_PVT';
2495 L_API_VERSION CONSTANT NUMBER := 1.0;
2496 l_delivery_id_tab delivery_id_tabtype := delivery_id_tabtype();
2497 l_table_count NUMBER;
2498 l_entity_name wsh_document_instances.entity_name%type;
2499 l_delivery_id wsh_new_deliveries.delivery_id%type;
2500 l_delivery_leg_id wsh_delivery_legs.delivery_leg_id%type;
2501
2502 CURSOR status_csr (c_entity_name IN VARCHAR2, c_entity_id IN NUMBER) IS
2503 SELECT
2504 entity_id
2505 , status
2506 FROM
2507 wsh_document_instances
2508 WHERE entity_name = c_entity_name
2509 AND entity_id = c_entity_id
2510 AND document_type = p_document_type
2511 AND status = 'PLANNED'
2512 FOR UPDATE;
2513
2514 CURSOR trip_stop_csr IS
2515 SELECT
2516 delivery_id
2517 , pick_up_stop_id
2518 , drop_off_stop_id
2519 FROM
2520 wsh_delivery_legs
2521 WHERE delivery_leg_id = p_entity_id;
2522
2523 CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
2524 SELECT
2525 delivery_id
2526 FROM
2527 wsh_delivery_legs
2528 WHERE delivery_leg_id = c_delivery_leg_id;
2529
2530 trip_stop_rec trip_stop_csr%rowtype;
2531 delivery_id_rec delivery_id_csr%rowtype;
2532 status_rec status_csr%rowtype;
2533
2534 --
2535 l_debug_on BOOLEAN;
2536 --
2537 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'OPEN_DOCUMENT';
2538 --
2539 BEGIN
2540 -- since this procedure does DML issue savepoint
2541 --
2542 -- Debug Statements
2543 --
2544 --
2545 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2546 --
2547 IF l_debug_on IS NULL
2548 THEN
2549 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2550 END IF;
2551 --
2552 IF l_debug_on THEN
2553 WSH_DEBUG_SV.push(l_module_name);
2554 --
2555 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
2556 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
2557 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
2558 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
2559 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
2560 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
2561 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
2562 WSH_DEBUG_SV.log(l_module_name,'P_CONSOLIDATE_OPTION',P_CONSOLIDATE_OPTION);
2563 END IF;
2564 --
2565 SAVEPOINT WSH_Document_PVT;
2566
2567 -- standard call to check for call compatibility.
2568 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2569 p_api_version,
2570 l_api_name,
2571 g_pkg_name ) THEN
2572 RAISE FND_API.g_exc_unexpected_error;
2573 END IF;
2574
2575 -- initialize message list if p_init_msg_list is set to TRUE.
2576
2577 IF FND_API.to_boolean( p_init_msg_list ) THEN
2578 FND_MSG_PUB.initialize;
2579 END IF;
2580
2581 -- initialize API return status to success
2582 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
2583
2584 -------------------------------------------------------------
2585 -- Initialize the entity_name based on the document type --
2586 -- If the entity is Delivery leg, look up its delivery id --
2587 -- to be used to build the child delivery table later --
2588 -------------------------------------------------------------
2589
2590 l_entity_name := Init_Entity_Name (p_document_type, p_entity_name);
2591 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2592 THEN
2593 OPEN delivery_id_csr (p_entity_id);
2594 FETCH delivery_id_csr INTO delivery_id_rec;
2595 l_delivery_id := delivery_id_rec.delivery_id;
2596 CLOSE delivery_id_csr;
2597 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
2598 THEN
2599 l_delivery_id := p_entity_id;
2600 END IF;
2601
2602 ----------------------------------------------
2603 -- if the document is for delivery leg --
2604 -- get its pick up and drop off trip stops --
2605 ----------------------------------------------
2606
2607 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2608 THEN
2609 OPEN trip_stop_csr;
2610 FETCH trip_stop_csr INTO trip_stop_rec;
2611 CLOSE trip_stop_csr;
2612 END IF;
2613
2614 ---------------------------------------------------------------
2615 -- based on the consolidate_option identify the delivery ids --
2616 -- to cancel documents for --
2617 ---------------------------------------------------------------
2618
2619 IF (p_consolidate_option IN ('BOTH', 'SEPARATE')) THEN
2620 GET_ChildDeliveryTab ( l_delivery_id
2621 , l_delivery_id_tab );
2622 END IF;
2623
2624 IF p_consolidate_option IN ('BOTH', 'CONSOLIDATE') THEN
2625 l_table_count := l_delivery_id_tab.count;
2626 l_delivery_id_tab.extend;
2627 l_delivery_id_tab(l_table_count+1) := l_delivery_id;
2628 END IF;
2629
2630 IF NOT l_delivery_id_tab.EXISTS(1) THEN
2631 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_INVALID_DELIVERY');
2632 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
2633 RAISE FND_API.G_EXC_ERROR;
2634 END IF;
2635
2636 FOR ctr IN 1..l_delivery_id_tab.count LOOP
2637
2638 -----------------------------------------------------------
2639 -- For each delivery, if the docuement relates to --
2640 -- delivery leg entity then identify the delivery leg id --
2641 -----------------------------------------------------------
2642
2643 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2644 THEN
2645 l_delivery_leg_id := Get_Delivery_Leg_Id ( l_delivery_id_tab(ctr)
2646 , trip_stop_rec.pick_up_stop_id
2647 , trip_stop_rec.drop_off_stop_id
2648 );
2649 END IF;
2650
2651 IF status_csr%ISOPEN
2652 THEN
2653 CLOSE status_csr;
2654 END IF;
2655 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2656 THEN
2657 OPEN status_csr (l_entity_name, l_delivery_leg_id);
2658 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
2659 THEN
2660 OPEN status_csr (l_entity_name, l_delivery_id_tab(ctr));
2661 END IF;
2662
2663 FETCH status_csr INTO status_rec;
2664 IF status_csr%NOTFOUND THEN
2665 CLOSE status_csr;
2666 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_MISSING');
2667 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
2668 RAISE FND_API.G_EXC_ERROR;
2669 END IF;
2670
2671 UPDATE wsh_document_instances
2672 SET status = 'OPEN'
2673 , last_update_date = sysdate
2674 , last_updated_by = fnd_global.user_id
2675 , last_update_login = fnd_global.login_id
2676 WHERE CURRENT OF status_csr;
2677
2678 END LOOP;
2679 IF status_csr%ISOPEN
2680 THEN
2681 CLOSE status_csr;
2682 END IF;
2683
2684 -- get message count and the message itself (if only one message)
2685 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2686 p_data => x_msg_data);
2687 -- Standard check of p_commit.
2688 IF FND_API.to_boolean( p_commit ) THEN
2689 COMMIT WORK;
2690 END IF;
2691 --
2692 -- Debug Statements
2693 --
2694 IF l_debug_on THEN
2695 WSH_DEBUG_SV.pop(l_module_name);
2696 END IF;
2697 --
2698 EXCEPTION
2699
2700 WHEN FND_API.g_exc_error THEN
2701 ROLLBACK to WSH_Document_PVT;
2702 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
2703 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
2704 p_data => x_msg_data );
2705
2706 --
2707 -- Debug Statements
2708 --
2709 IF l_debug_on THEN
2710 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2711 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2712 END IF;
2713 --
2714 WHEN FND_API.g_exc_unexpected_error THEN
2715 ROLLBACK to WSH_Document_PVT;
2716 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
2717 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
2718 p_data => x_msg_data );
2719
2720 --
2721 -- Debug Statements
2722 --
2723 IF l_debug_on THEN
2724 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2725 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2726 END IF;
2727 --
2728 WHEN others THEN
2729 ROLLBACK to WSH_Document_PVT;
2730 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
2731 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
2732 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
2733 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
2734 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
2735 WSH_UTIL_CORE.add_message (x_return_status);
2736 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
2737 p_data => x_msg_data );
2738 --
2739 -- Debug Statements
2740 --
2741 IF l_debug_on THEN
2742 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2743 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2744 END IF;
2745 --
2746 END Open_Document;
2747
2748 -----------------------------------------------------------------------------
2749 -- PROCEDURE : Complete_Document PUBLIC
2750 -- VERSION : 1.0
2751 -- COMMENT : Updates the status of a document to 'COMPLETE'
2752 --
2753 -- PARAMETER LIST :
2754 --
2755 -- IN
2756 --
2757 -- p_api_version known API version
2758 -- p_init_msg_list should API reset message stack (default: false)
2759 -- p_commit should API do a commit (default: false)
2760 -- p_validation_level extent of validation done in the API (not used)
2761 -- p_entity_name Entity for which the document is being completed
2762 -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
2763 -- p_entity_id Entity id that the document belongs to
2764 -- example: delivery_id, delivery_leg_id, etc
2765 -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
2766 -- p_consolidate_option calling program's choice to complete document(s)
2767 -- for this parent delivery only ('CONSOLIDATE')
2768 -- or for child dels of this delivery ('SEPARATE')
2769 -- or both parent and child deliveries ('BOTH')
2770 --
2771 -- OUT
2772 --
2773 -- x_msg_count number of messages in stack
2774 -- x_msg_data message if there is only one message in stack
2775 -- x_return_status API return status ('S', 'E', 'U')
2776 --
2777 -- PRE-CONDITIONS : None
2778 -- POST-CONDITIONS : None
2779 -- EXCEPTIONS : None
2780 -----------------------------------------------------------------------------
2781
2782
2783
2784 PROCEDURE Complete_Document
2785 ( p_api_version IN NUMBER
2786 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
2787 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
2788 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
2789 , x_return_status OUT NOCOPY VARCHAR2
2790 , x_msg_count OUT NOCOPY NUMBER
2791 , x_msg_data OUT NOCOPY VARCHAR2
2792 , p_entity_name IN VARCHAR2 DEFAULT NULL
2793 , p_entity_id IN NUMBER
2794 , p_document_type IN VARCHAR2
2795 , p_consolidate_option IN VARCHAR2 DEFAULT 'BOTH'
2796 )
2797 IS
2798 L_API_NAME CONSTANT VARCHAR2(30) := 'WSH_Document_PVT';
2799 L_API_VERSION CONSTANT NUMBER := 1.0;
2800 l_delivery_id_tab delivery_id_tabtype := delivery_id_tabtype();
2801 l_table_count NUMBER;
2802 l_entity_name wsh_document_instances.entity_name%type;
2803 l_delivery_id wsh_new_deliveries.delivery_id%type;
2804 l_delivery_leg_id wsh_delivery_legs.delivery_leg_id%type;
2805
2806 CURSOR status_csr (c_entity_name IN VARCHAR2, c_entity_id IN NUMBER) IS
2807 SELECT
2808 entity_id
2809 , status
2810 FROM
2811 wsh_document_instances
2812 WHERE entity_name = c_entity_name
2813 AND entity_id = c_entity_id
2814 AND document_type = p_document_type
2815 AND status not in ('CANCELLED')
2816 FOR UPDATE;
2817
2818 CURSOR trip_stop_csr IS
2819 SELECT
2820 delivery_id
2821 , pick_up_stop_id
2822 , drop_off_stop_id
2823 FROM
2824 wsh_delivery_legs
2825 WHERE delivery_leg_id = p_entity_id;
2826
2827 CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
2828 SELECT
2829 delivery_id
2830 FROM
2831 wsh_delivery_legs
2832 WHERE delivery_leg_id = c_delivery_leg_id;
2833
2834 trip_stop_rec trip_stop_csr%rowtype;
2835 delivery_id_rec delivery_id_csr%rowtype;
2836 status_rec status_csr%rowtype;
2837
2838 --
2839 l_debug_on BOOLEAN;
2840 --
2841 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'COMPLETE_DOCUMENT';
2842 --
2843 BEGIN
2844 -- since this procedure does DML issue savepoint
2845 --
2846 -- Debug Statements
2847 --
2848 --
2849 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2850 --
2851 IF l_debug_on IS NULL
2852 THEN
2853 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2854 END IF;
2855 --
2856 IF l_debug_on THEN
2857 WSH_DEBUG_SV.push(l_module_name);
2858 --
2859 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
2860 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
2861 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
2862 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
2863 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
2864 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
2865 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
2866 WSH_DEBUG_SV.log(l_module_name,'P_CONSOLIDATE_OPTION',P_CONSOLIDATE_OPTION);
2867 END IF;
2868 --
2869 SAVEPOINT WSH_Document_PVT;
2870
2871 -- standard call to check for call compatibility.
2872 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2873 p_api_version,
2874 l_api_name,
2875 g_pkg_name ) THEN
2876 RAISE FND_API.g_exc_unexpected_error;
2877 END IF;
2878
2879 -- initialize message list if p_init_msg_list is set to TRUE.
2880
2881 IF FND_API.to_boolean( p_init_msg_list ) THEN
2882 FND_MSG_PUB.initialize;
2883 END IF;
2884
2885 -- initialize API return status to success
2886 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
2887
2888 -------------------------------------------------------------
2889 -- Initialize the entity_name based on the document type --
2890 -- If the entity is Delivery leg, look up its delivery id --
2891 -- to be used to build the child delivery table later --
2892 -------------------------------------------------------------
2893
2894 l_entity_name := Init_Entity_Name (p_document_type, p_entity_name);
2895 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2896 THEN
2897 OPEN delivery_id_csr (p_entity_id);
2898 FETCH delivery_id_csr INTO delivery_id_rec;
2899 l_delivery_id := delivery_id_rec.delivery_id;
2900 CLOSE delivery_id_csr;
2901 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
2902 THEN
2903 l_delivery_id := p_entity_id;
2904 END IF;
2905
2906 ----------------------------------------------
2907 -- if the document is for delivery leg --
2908 -- get its pick up and drop off trip stops --
2909 ----------------------------------------------
2910
2911 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2912 THEN
2913 OPEN trip_stop_csr;
2914 FETCH trip_stop_csr INTO trip_stop_rec;
2915 CLOSE trip_stop_csr;
2916 END IF;
2917
2918 ---------------------------------------------------------------
2919 -- based on the consolidate_option identify the delivery ids --
2920 -- to cancel documents for --
2921 ---------------------------------------------------------------
2922
2923 IF (p_consolidate_option IN ('BOTH', 'SEPARATE')) THEN
2924 GET_ChildDeliveryTab ( l_delivery_id
2925 , l_delivery_id_tab );
2926 END IF;
2927
2928 IF p_consolidate_option IN ('BOTH', 'CONSOLIDATE') THEN
2929 l_table_count := l_delivery_id_tab.count;
2930 l_delivery_id_tab.extend;
2931 l_delivery_id_tab(l_table_count+1) := l_delivery_id;
2932 END IF;
2933
2934 IF NOT l_delivery_id_tab.EXISTS(1) THEN
2935 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_INVALID_DELIVERY');
2936 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
2937 RAISE FND_API.G_EXC_ERROR;
2938 END IF;
2939
2940 FOR ctr IN 1..l_delivery_id_tab.count LOOP
2941
2942 -----------------------------------------------------------
2943 -- For each delivery, if the docuement relates to --
2944 -- delivery leg entity then identify the delivery leg id --
2945 -----------------------------------------------------------
2946
2947 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2948 THEN
2949 l_delivery_leg_id := Get_Delivery_Leg_Id ( l_delivery_id_tab(ctr)
2950 , trip_stop_rec.pick_up_stop_id
2951 , trip_stop_rec.drop_off_stop_id
2952 );
2953 END IF;
2954
2955 IF status_csr%ISOPEN
2956 THEN
2957 CLOSE status_csr;
2958 END IF;
2959 IF l_entity_name = 'WSH_DELIVERY_LEGS'
2960 THEN
2961 OPEN status_csr (l_entity_name, l_delivery_leg_id);
2962 ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
2963 THEN
2964 OPEN status_csr (l_entity_name, l_delivery_id_tab(ctr));
2965 END IF;
2966
2967 FETCH status_csr INTO status_rec;
2968 IF status_csr%NOTFOUND THEN
2969 CLOSE status_csr;
2970 FND_MESSAGE.set_name ('WSH', 'WSH_DOC_MISSING');
2971 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.g_ret_sts_error);
2972 RAISE FND_API.G_EXC_ERROR;
2973 END IF;
2974
2975 UPDATE wsh_document_instances
2976 SET status = 'COMPLETE'
2977 , last_update_date = sysdate
2978 , last_updated_by = fnd_global.user_id
2979 , last_update_login = fnd_global.login_id
2980 WHERE CURRENT OF status_csr;
2981
2982 END LOOP;
2983 IF status_csr%ISOPEN
2984 THEN
2985 CLOSE status_csr;
2986 END IF;
2987
2988 -- get message count and the message itself (if only one message)
2989 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2990 p_data => x_msg_data);
2991 -- Standard check of p_commit.
2992 IF FND_API.to_boolean( p_commit ) THEN
2993 COMMIT WORK;
2994 END IF;
2995 --
2996 -- Debug Statements
2997 --
2998 IF l_debug_on THEN
2999 WSH_DEBUG_SV.pop(l_module_name);
3000 END IF;
3001 --
3002 EXCEPTION
3003
3004 WHEN FND_API.g_exc_error THEN
3005 ROLLBACK to WSH_Document_PVT;
3006 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
3007 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3008 p_data => x_msg_data );
3009
3010 --
3011 -- Debug Statements
3012 --
3013 IF l_debug_on THEN
3014 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3015 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3016 END IF;
3017 --
3018 WHEN FND_API.g_exc_unexpected_error THEN
3019 ROLLBACK to WSH_Document_PVT;
3020 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3021 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3022 p_data => x_msg_data );
3023
3024 --
3025 -- Debug Statements
3026 --
3027 IF l_debug_on THEN
3028 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3029 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3030 END IF;
3031 --
3032 WHEN others THEN
3033 ROLLBACK to WSH_Document_PVT;
3034 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
3035 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
3036 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
3037 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
3038 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3039 WSH_UTIL_CORE.add_message (x_return_status);
3040 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3041 p_data => x_msg_data );
3042 --
3043 -- Debug Statements
3044 --
3045 IF l_debug_on THEN
3046 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3047 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3048 END IF;
3049 --
3050 END Complete_Document;
3051
3052
3053 ------------------------------------------------------------------------------
3054 -- PROCEDURE : Print_Document PUBLIC
3055 -- VERSION : 1.0
3056 -- COMMENT : Submit the report WSHRDPAK.rdf to print the packing slip.
3057 --
3058 -- PARAMETER LIST :
3059 --
3060 -- IN
3061 --
3062 -- p_api_version known API version
3063 -- p_init_msg_list should API reset message stack (default: false)
3064 -- p_commit should API do a commit (default: false)
3065 -- p_validation_level extent of validation done in the API (not used)
3066 -- p_delivery_id delivery id for which document is being printed
3067 -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
3068 -- p_departure_date_lo delivery date (low)
3069 -- p_departure_date_hi delivery date (high)
3070 -- p_item_display display FLEX, DESC or BOTH (default BOTH)
3071 -- p_print_cust_item print customer item information or not (default
3072 -- NO)
3073 -- p_print_mode print FINAL or DRAFT (default DRAFT)
3074 -- p_print_all calling program's choice to cancel document(s)
3075 -- for this parent delivery only ('CONSOLIDATE')
3076 -- or for child dels of this delivery ('SEPARATE')
3077 -- or both parent and child deliveries ('BOTH')
3078 -- p_sort sort the report by customer item or inventory
3079 -- item (INV or CUST, default INV)
3080 -- p_freight_carrier carrier_id of the freight carrier
3081 -- p_warehouse_id current organization_id
3082 --
3083 -- OUT
3084 --
3085 -- x_msg_count number of messages in stack
3086 -- x_msg_data message if there is only one message in stack
3087 -- x_return_status API return status ('S', 'E', 'U')
3088 --
3089 -- PRE-CONDITIONS : None
3090 -- POST-CONDITIONS : None
3091 -- EXCEPTIONS : None
3092 ------------------------------------------------------------------------------
3093
3094 PROCEDURE Print_Document
3095 ( p_api_version IN NUMBER
3096 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
3097 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
3098 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
3099 , x_return_status OUT NOCOPY VARCHAR2
3100 , x_msg_count OUT NOCOPY NUMBER
3101 , x_msg_data OUT NOCOPY VARCHAR2
3102 , p_delivery_id IN NUMBER
3103 , p_document_type IN VARCHAR2
3104 , p_departure_date_lo IN DATE DEFAULT NULL
3105 , p_departure_date_hi IN DATE DEFAULT NULL
3106 , p_item_display IN VARCHAR2 DEFAULT 'D'
3107 , p_print_cust_item IN VARCHAR2 DEFAULT 'N'
3108 , p_print_mode IN VARCHAR2 DEFAULT 'DRAFT'
3109 , p_print_all IN VARCHAR2 DEFAULT 'BOTH'
3110 , p_sort IN VARCHAR2 DEFAULT 'INV'
3111 , p_freight_carrier IN VARCHAR2 DEFAULT NULL
3112 , p_warehouse_id IN NUMBER
3113 , x_conc_request_id OUT NOCOPY NUMBER
3114 )
3115 IS
3116 l_api_name CONSTANT VARCHAR2(30) := 'Print_Document';
3117 l_api_version CONSTANT NUMBER := 1.0;
3118 l_conc_request_id NUMBER;
3119
3120 --
3121 l_debug_on BOOLEAN;
3122 --
3123 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PRINT_DOCUMENT';
3124 --
3125 BEGIN
3126 -- standard call to check for call compatibility.
3127 --
3128 -- Debug Statements
3129 --
3130 --
3131 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3132 --
3133 IF l_debug_on IS NULL
3134 THEN
3135 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3136 END IF;
3137 --
3138 IF l_debug_on THEN
3139 WSH_DEBUG_SV.push(l_module_name);
3140 --
3141 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
3142 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
3143 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
3144 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
3145 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
3146 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
3147 WSH_DEBUG_SV.log(l_module_name,'P_DEPARTURE_DATE_LO',P_DEPARTURE_DATE_LO);
3148 WSH_DEBUG_SV.log(l_module_name,'P_DEPARTURE_DATE_HI',P_DEPARTURE_DATE_HI);
3149 WSH_DEBUG_SV.log(l_module_name,'P_ITEM_DISPLAY',P_ITEM_DISPLAY);
3150 WSH_DEBUG_SV.log(l_module_name,'P_PRINT_CUST_ITEM',P_PRINT_CUST_ITEM);
3151 WSH_DEBUG_SV.log(l_module_name,'P_PRINT_MODE',P_PRINT_MODE);
3152 WSH_DEBUG_SV.log(l_module_name,'P_PRINT_ALL',P_PRINT_ALL);
3153 WSH_DEBUG_SV.log(l_module_name,'P_SORT',P_SORT);
3154 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_CARRIER',P_FREIGHT_CARRIER);
3155 WSH_DEBUG_SV.log(l_module_name,'P_WAREHOUSE_ID',P_WAREHOUSE_ID);
3156 END IF;
3157 --
3158 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3159 p_api_version,
3160 l_api_name,
3161 g_pkg_name ) THEN
3162 RAISE FND_API.g_exc_unexpected_error;
3163 END IF;
3164
3165 -- initialize message list if p_init_msg_list is set to TRUE.
3166
3167 IF FND_API.to_boolean( p_init_msg_list ) THEN
3168 FND_MSG_PUB.initialize;
3169 END IF;
3170
3171 -- initialize API return status to success
3172 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
3173
3174 -- call FND_REQUEST.SUBMIT_REQUEST to run the WSHRDPAK.rdf report
3175 l_conc_request_id := FND_REQUEST.SUBMIT_REQUEST
3176 ( 'WSH'
3177 , 'WSHRDPAK'
3178 , 'Packing Slip Report'
3179 , NULL
3180 , FALSE
3181 , p_delivery_id
3182 , p_print_cust_item
3183 , p_item_display
3184 , p_print_mode
3185 , p_print_all
3186 , p_sort
3187 , p_departure_date_lo
3188 , p_departure_date_hi
3189 , p_freight_carrier
3190 , p_warehouse_id
3191 , '', '', '', '', '', '', '', '', '', ''
3192 , '', '', '', '', '', '', '', '', '', ''
3193 , '', '', '', '', '', '', '', '', '', ''
3194 , '', '', '', '', '', '', '', '', '', ''
3195 , '', '', '', '', '', '', '', '', '', ''
3196 , '', '', '', '', '', '', '', '', '', ''
3197 , '', '', '', '', '', '', '', '', '', ''
3198 , '', '', '', '', '', '', '', '', '', ''
3199 , '', '', '', '', '', '', '', '', '', '');
3200
3201 -- must commit in order to submit the request
3202 COMMIT WORK;
3203
3204 x_conc_request_id := l_conc_request_id;
3205
3206 -- get message count and the message itself (if only one message)
3207 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
3208 p_data => x_msg_data);
3209
3210 --
3211 -- Debug Statements
3212 --
3213 IF l_debug_on THEN
3214 WSH_DEBUG_SV.log(l_module_name,'REQUEST ID',x_conc_request_id);
3215 WSH_DEBUG_SV.pop(l_module_name);
3216 END IF;
3217 --
3218 EXCEPTION
3219
3220 WHEN FND_API.g_exc_error THEN
3221 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
3222 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3223 p_data => x_msg_data );
3224
3225 --
3226 -- Debug Statements
3227 --
3228 IF l_debug_on THEN
3229 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3230 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3231 END IF;
3232 --
3233 WHEN FND_API.g_exc_unexpected_error THEN
3234 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3235 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3236 p_data => x_msg_data );
3237 --
3238 -- Debug Statements
3239 --
3240 IF l_debug_on THEN
3241 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3242 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3243 END IF;
3244 --
3245 WHEN others THEN
3246 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
3247 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
3248 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
3249 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
3250 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3251 WSH_UTIL_CORE.add_message (x_return_status);
3252 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3253 p_data => x_msg_data );
3254 --
3255 -- Debug Statements
3256 --
3257 IF l_debug_on THEN
3258 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3259 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3260 END IF;
3261 --
3262 END Print_Document;
3263
3264 ------------------------------------------------------------------------------
3265 -- FUNCTION : Get_CumQty PUBLIC
3266 -- VERSION : 1.0
3267 -- COMMENT : Obtain cummulative quantity value based on the inputs
3268 -- by calling Automotive's CUM Management API. Return such
3269 -- value.
3270 --
3271 -- PARAMETER LIST :
3272 --
3273 -- IN
3274 --
3275 -- p_api_version known API version
3276 -- p_init_msg_list should API reset message stack (default: false)
3277 -- p_commit should API do a commit (default: false)
3278 -- p_validation_level extent of validation done in the API (not used)
3279 -- p_customer_id from delivery details
3280 -- p_oe_order_line_id from delivery details for getting line level
3281 -- information to be passed to cal_cum api
3282 --
3283 -- OUT
3284 --
3285 -- x_msg_count number of messages in stack
3286 -- x_msg_data message if there is only one message in stack
3287 -- x_return_status API return status ('S', 'E', 'U')
3288 --
3289 -- RETURN NUMBER, cum quantity value
3290 --
3291 -- PRE-CONDITIONS : None
3292 -- POST-CONDITIONS : None
3293 -- EXCEPTIONS : None
3294 ------------------------------------------------------------------------------
3295
3296 FUNCTION Get_CumQty
3297 ( p_api_version IN NUMBER
3298 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
3299 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
3300 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
3301 , x_return_status OUT NOCOPY VARCHAR2
3302 , x_msg_count OUT NOCOPY NUMBER
3303 , x_msg_data OUT NOCOPY VARCHAR2
3304 , p_customer_id IN NUMBER
3305 , p_oe_order_line_id IN NUMBER
3306 )
3307 RETURN NUMBER
3308 IS
3309 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_CumQty';
3310 L_API_VERSION CONSTANT NUMBER := 1.0;
3311 l_msg_flag VARCHAR2(2000);
3312 l_cum_qty NUMBER;
3313 l_customer_item_id RLM_CUST_ITEM_CUM_KEYS.CUSTOMER_ITEM_ID%TYPE;
3314 l_ship_from_org_id RLM_CUST_ITEM_CUM_KEYS.SHIP_FROM_ORG_ID%TYPE;
3315 l_ship_to_address_id RLM_CUST_ITEM_CUM_KEYS.SHIP_TO_ADDRESS_ID%TYPE;
3316 l_bill_to_address_id RLM_CUST_ITEM_CUM_KEYS.BILL_TO_ADDRESS_ID%TYPE;
3317 l_po_number RLM_CUST_ITEM_CUM_KEYS.PURCHASE_ORDER_NUMBER%TYPE;
3318 l_cust_record_year RLM_CUST_ITEM_CUM_KEYS.CUST_RECORD_YEAR%TYPE;
3319 l_inventory_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%TYPE;
3320 l_cum_key_record RLM_CUM_SV.cum_key_attrib_rec_type;
3321 l_cum_record RLM_CUM_SV.cum_rec_type;
3322 -- 1711448
3323 l_source_doc_hdr_id NUMBER;
3324 l_source_doc_line_id NUMBER;
3325 l_cum_start_date DATE;
3326 l_return_message VARCHAR2(4000);
3327 l_msg_data VARCHAR2(4000);
3328 l_return_status BOOLEAN;
3329
3330 --
3331 l_debug_on BOOLEAN;
3332 --
3333 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CUMQTY';
3334 --
3335 BEGIN
3336 -- standard call to check for call compatibility.
3337 --
3338 -- Debug Statements
3339 --
3340 --
3341 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3342 --
3343 IF l_debug_on IS NULL
3344 THEN
3345 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3346 END IF;
3347 --
3348 IF l_debug_on THEN
3349 WSH_DEBUG_SV.push(l_module_name);
3350 --
3351 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
3352 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
3353 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
3354 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
3355 WSH_DEBUG_SV.log(l_module_name,'P_CUSTOMER_ID',P_CUSTOMER_ID);
3356 WSH_DEBUG_SV.log(l_module_name,'P_OE_ORDER_LINE_ID',P_OE_ORDER_LINE_ID);
3357 END IF;
3358 --
3359 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3360 p_api_version,
3361 l_api_name,
3362 g_pkg_name ) THEN
3363 RAISE FND_API.g_exc_unexpected_error;
3364 END IF;
3365
3366 -- initialize message list if p_init_msg_list is set to TRUE.
3367 IF FND_API.to_boolean( p_init_msg_list ) THEN
3368 FND_MSG_PUB.initialize;
3369 END IF;
3370
3371 -- initialize API return status to success
3372 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
3373
3374 -- initially l_cum_qty=0
3375 l_cum_qty :=0;
3376
3377 -- get information required about that order line from OE
3378 -- 1711448, changed to hz Tables from oe views
3379 SELECT
3380 ol.ship_from_org_id
3381 , ol.source_document_id
3382 , ol.source_document_line_id
3383 , ss.cust_acct_site_id
3384 , bs.cust_acct_site_id
3385 , DECODE (ol.item_identifier_type,
3386 'CUST', ol.ordered_item_id, NULL)
3387 , ol.cust_po_number
3388 , ol.industry_attribute1
3389 , ol.inventory_item_id
3390 INTO
3391 l_ship_from_org_id
3392 , l_source_doc_hdr_id
3393 , l_source_doc_line_id
3394 , l_ship_to_address_id
3395 , l_bill_to_address_id
3396 , l_customer_item_id
3397 , l_po_number
3398 , l_cust_record_year
3399 , l_inventory_item_id
3400 FROM
3401 oe_order_lines_all ol
3402 , hz_cust_acct_sites_all ss
3403 , hz_cust_site_uses_all ssu
3404 , hz_cust_acct_sites_all bs
3405 , hz_cust_site_uses_all bsu
3406 WHERE ol.line_id=p_oe_order_line_id
3407 and ol.ship_to_org_id = ssu.site_use_id (+)
3408 and ssu.site_use_code = 'SHIP_TO'
3409 and ol.invoice_to_org_id = bsu.site_use_id (+)
3410 and bsu.site_use_code = 'BILL_TO'
3411 and ssu.cust_acct_site_ID = ss.cust_acct_site_ID
3412 and bsu.cust_acct_site_ID = bs.cust_acct_site_ID;
3413
3414 IF SQL%NOTFOUND or SQL%ROWCOUNT > 1
3415 THEN
3416 RAISE FND_API.g_exc_error;
3417 END IF;
3418
3419 -- 1711448, Added this API to get CUM Start Date
3420 --
3421 -- Debug Statements
3422 --
3423 IF l_debug_on THEN
3424 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit RLM_CUM_SV.GETCUMSTARTDATE',WSH_DEBUG_SV.C_PROC_LEVEL);
3425 END IF;
3426 --
3427 RLM_CUM_SV.GetCumStartDate(
3428 i_schedule_header_id => l_source_doc_hdr_id,
3429 i_schedule_line_id => l_source_doc_line_id,
3430 o_cum_start_date => l_cum_start_date,
3431 o_cust_record_year => l_cust_record_year,
3432 o_return_message => l_msg_data,
3433 o_return_status => l_return_status
3434 );
3435
3436 -- prepare the record parameters for use by the cum apis
3437 l_cum_key_record.customer_id:=p_customer_id;
3438 l_cum_key_record.customer_item_id:=l_customer_item_id;
3439 l_cum_key_record.ship_from_org_id:=l_ship_from_org_id;
3440 l_cum_key_record.ship_to_address_id:=l_ship_to_address_id;
3441 l_cum_key_record.bill_to_address_id:=l_bill_to_address_id;
3442 l_cum_key_record.purchase_order_number:=l_po_number;
3443 l_cum_key_record.cust_record_year:=l_cust_record_year;
3444 l_cum_key_record.create_cum_key_flag:='N';
3445 -- 1711448, Added Cum start Dt.
3446 l_cum_key_record.cum_start_date:=l_cum_start_date;
3447
3448 --BUG 1932236
3449 l_cum_key_record.inventory_item_id:=l_inventory_item_id;
3450
3451 -- get cum_key by calling calculate_cum_key routine in RLM_CUM_SV
3452 --
3453 -- Debug Statements
3454 --
3455 IF l_debug_on THEN
3456 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit RLM_TPA_SV.CALCULATECUMKEY',WSH_DEBUG_SV.C_PROC_LEVEL);
3457 END IF;
3458 --
3459 RLM_TPA_SV.CalculateCumKey( x_cum_key_record=>l_cum_key_record
3460 , x_cum_record=>l_cum_record);
3461
3462 -- get cum_qty by calling calculate_supplier_cum in RLM_CUM_SV
3463 --
3464 -- Debug Statements
3465 --
3466 IF l_debug_on THEN
3467 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit RLM_TPA_SV.CALCULATESUPPLIERCUM',WSH_DEBUG_SV.C_PROC_LEVEL);
3468 END IF;
3469 --
3470 RLM_TPA_SV.CalculateSupplierCum( x_cum_key_record=>l_cum_key_record
3471 , x_cum_record=>l_cum_record);
3472
3473 l_cum_qty:=l_cum_record.cum_qty;
3474
3475 --
3476 -- Debug Statements
3477 --
3478 IF l_debug_on THEN
3479 WSH_DEBUG_SV.log(l_module_name,'L_CUM_QTY',l_cum_qty);
3480 WSH_DEBUG_SV.pop(l_module_name);
3481 END IF;
3482 --
3483 RETURN l_cum_qty;
3484
3485 EXCEPTION
3486
3487 WHEN FND_API.g_exc_error THEN
3488 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
3489 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3490 p_data => x_msg_data );
3491 --
3492 -- Debug Statements
3493 --
3494 IF l_debug_on THEN
3495 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3496 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3497 END IF;
3498 --
3499 RETURN null;
3500
3501 WHEN FND_API.g_exc_unexpected_error THEN
3502 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3503 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3504 p_data => x_msg_data );
3505 --
3506 -- Debug Statements
3507 --
3508 IF l_debug_on THEN
3509 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3510 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3511 END IF;
3512 --
3513 RETURN null;
3514
3515 WHEN others THEN
3516 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
3517 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
3518 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
3519 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
3520 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3521 WSH_UTIL_CORE.add_message (x_return_status);
3522 --
3523 -- Debug Statements
3524 --
3525 IF l_debug_on THEN
3526 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3527 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3528 END IF;
3529 --
3530 RETURN null;
3531 END Get_CumQty;
3532
3533 ------------------------------------------------------------------------------
3534 -- PROCEDURE : Cancel_All_Documents PUBLIC
3535 -- VERSION : 1.0
3536 -- COMMENT : Updates status of all documents of all types that
3537 -- belong to a specific entity
3538 -- to 'CANCELLED'
3539 --
3540 -- PARAMETER LIST :
3541 --
3542 -- IN
3543 --
3544 -- p_api_version known API version
3545 -- p_init_msg_list should API reset message stack (default: false)
3546 -- p_commit should API do a commit (default: false)
3547 -- p_validation_level extent of validation done in the API (not used)
3548 -- p_entity_name Entity for which the document is being cancelled
3549 -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
3550 -- p_entity_id Entity id that the document belongs to
3551 -- example: delivery_id, delivery_leg_id, etc
3552 --
3553 -- OUT
3554 --
3555 -- x_msg_count number of messages in stack
3556 -- x_msg_data message if there is only one message in stack
3557 -- x_return_status API return status ('S', 'E', 'U')
3558 --
3559 -- PRE-CONDITIONS : None
3560 -- POST-CONDITIONS : None
3561 -- EXCEPTIONS : None
3562 -- NOTES : In consolidation situation, the child documents
3563 -- are not cancelled. Call this routine recursively
3564 -- for all entities where cancellation is reqd.
3565 ------------------------------------------------------------------------------
3566
3567 PROCEDURE Cancel_All_Documents
3568 ( p_api_version IN NUMBER
3569 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
3570 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
3571 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
3572 , x_return_status OUT NOCOPY VARCHAR2
3573 , x_msg_count OUT NOCOPY NUMBER
3574 , x_msg_data OUT NOCOPY VARCHAR2
3575 , p_entity_name IN VARCHAR2
3576 , p_entity_id IN NUMBER
3577 ) IS
3578 L_API_NAME CONSTANT VARCHAR2(30) := 'Cancel_All_Documents';
3579 L_API_VERSION CONSTANT NUMBER := 1.0;
3580
3581 --
3582 l_debug_on BOOLEAN;
3583 --
3584 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CANCEL_ALL_DOCUMENTS';
3585 --
3586 BEGIN
3587 -- since this procedure does DML issue savepoint
3588 --
3589 -- Debug Statements
3590 --
3591 --
3592 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3593 --
3594 IF l_debug_on IS NULL
3595 THEN
3596 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3597 END IF;
3598 --
3599 IF l_debug_on THEN
3600 WSH_DEBUG_SV.push(l_module_name);
3601 --
3602 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
3603 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
3604 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
3605 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
3606 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
3607 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
3608 END IF;
3609 --
3610 SAVEPOINT WSH_Document_PVT;
3611
3612 -- standard call to check for call compatibility.
3613 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3614 p_api_version,
3615 l_api_name,
3616 g_pkg_name ) THEN
3617 RAISE FND_API.g_exc_unexpected_error;
3618 END IF;
3619
3620 -- initialize message list if p_init_msg_list is set to TRUE.
3621 IF FND_API.to_boolean( p_init_msg_list ) THEN
3622 FND_MSG_PUB.initialize;
3623 END IF;
3624
3625 -- initialize API return status to success
3626 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
3627
3628
3629 UPDATE wsh_document_instances
3630 SET status = 'CANCELLED'
3631 , last_update_date = sysdate
3632 , last_updated_by = fnd_global.user_id
3633 , last_update_login = fnd_global.login_id
3634 WHERE entity_name = p_entity_name
3635 AND entity_id = p_entity_id;
3636
3637 -- get message count and the message itself (if only one message)
3638 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
3639 p_data => x_msg_data);
3640 -- Standard check of p_commit.
3641 IF FND_API.to_boolean( p_commit ) THEN
3642 COMMIT WORK;
3643 END IF;
3644 --
3645 -- Debug Statements
3646 --
3647 IF l_debug_on THEN
3648 WSH_DEBUG_SV.pop(l_module_name);
3649 END IF;
3650 --
3651 EXCEPTION
3652
3653 WHEN FND_API.g_exc_error THEN
3654 ROLLBACK to WSH_Document_PVT;
3655 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
3656 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3657 p_data => x_msg_data );
3658
3659 --
3660 -- Debug Statements
3661 --
3662 IF l_debug_on THEN
3663 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3664 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3665 END IF;
3666 --
3667 WHEN FND_API.g_exc_unexpected_error THEN
3668 ROLLBACK to WSH_Document_PVT;
3669 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3670 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3671 p_data => x_msg_data );
3672
3673 --
3674 -- Debug Statements
3675 --
3676 IF l_debug_on THEN
3677 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3678 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3679 END IF;
3680 --
3681 WHEN others THEN
3682 ROLLBACK to WSH_Document_PVT;
3683 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
3684 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
3685 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
3686 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
3687 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3688 WSH_UTIL_CORE.add_message (x_return_status);
3689 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3690 p_data => x_msg_data );
3691 --
3692 -- Debug Statements
3693 --
3694 IF l_debug_on THEN
3695 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3696 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3697 END IF;
3698 --
3699 END Cancel_All_Documents;
3700
3701 ------------------------------------------------------------------------------
3702 -- PROCEDURE : Get_All_Documents PUBLIC
3703 -- VERSION : 1.0
3704 -- COMMENT : Returns as an out-param a table of records of all documents
3705 -- (packing slip, bill of lading, etc.) that belong to a
3706 -- specific entity (delivery, delivery_leg, etc.)
3707 --
3708 -- PARAMETER LIST :
3709 --
3710 -- IN
3711 --
3712 -- p_api_version known API version
3713 -- p_init_msg_list should API reset message stack (default: false)
3714 -- p_commit should API do a commit (default: false)
3715 -- p_validation_level extent of validation done in the API (not used)
3716 -- p_entity_name Entity for which the document is being cancelled
3717 -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
3718 -- p_entity_id Entity id that the document belongs to
3719 -- example: delivery_id, delivery_leg_id, etc
3720
3721 -- OUT
3722 --
3723 -- x_msg_count number of messages in stack
3724 -- x_msg_data message if there is only one message in stack
3725 -- x_return_status API return status ('S', 'E', 'U')
3726 -- x_document_tab table that contains all documents of the entity
3727 --
3728 -- PRE-CONDITIONS : None
3729 -- POST-CONDITIONS : None
3730 -- EXCEPTIONS : None
3731 ------------------------------------------------------------------------------
3732
3733 PROCEDURE Get_All_Documents
3734 ( p_api_version IN NUMBER
3735 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
3736 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
3737 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
3738 , x_return_status OUT NOCOPY VARCHAR2
3739 , x_msg_count OUT NOCOPY NUMBER
3740 , x_msg_data OUT NOCOPY VARCHAR2
3741 , p_entity_name IN VARCHAR2
3742 , p_entity_id IN NUMBER
3743 , x_document_tab OUT NOCOPY wsh_document_pub.document_tabtype
3744 ) IS
3745 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_All_Documents';
3746 L_API_VERSION CONSTANT NUMBER := 1.0;
3747 CURSOR doc_csr IS
3748 SELECT document_instance_id
3749 , document_type
3750 , entity_name
3751 , entity_id
3752 , doc_sequence_category_id
3753 , sequence_number
3754 , status
3755 , final_print_date
3756 , created_by
3757 , creation_date
3758 , last_updated_by
3759 , last_update_date
3760 , last_update_login
3761 , program_application_id
3762 , program_id
3763 , program_update_date
3764 , request_id
3765 , attribute_category
3766 , attribute1
3767 , attribute2
3768 , attribute3
3769 , attribute4
3770 , attribute5
3771 , attribute6
3772 , attribute7
3773 , attribute8
3774 , attribute9
3775 , attribute10
3776 , attribute11
3777 , attribute12
3778 , attribute13
3779 , attribute14
3780 , attribute15
3781 FROM wsh_document_instances
3782 WHERE entity_name = p_entity_name
3783 AND entity_id = p_entity_id;
3784
3785 i NUMBER := 1; -- loop counter
3786
3787 --
3788 l_debug_on BOOLEAN;
3789 --
3790 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_ALL_DOCUMENTS';
3791 --
3792 BEGIN
3793
3794 -- standard call to check for call compatibility.
3795 --
3796 -- Debug Statements
3797 --
3798 --
3799 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3800 --
3801 IF l_debug_on IS NULL
3802 THEN
3803 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3804 END IF;
3805 --
3806 IF l_debug_on THEN
3807 WSH_DEBUG_SV.push(l_module_name);
3808 --
3809 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
3810 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
3811 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
3812 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
3813 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
3814 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
3815 END IF;
3816 --
3817 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3818 p_api_version,
3819 l_api_name,
3820 g_pkg_name ) THEN
3821 RAISE FND_API.g_exc_unexpected_error;
3822 END IF;
3823
3824 -- initialize message list if p_init_msg_list is set to TRUE.
3825 IF FND_API.to_boolean( p_init_msg_list ) THEN
3826 FND_MSG_PUB.initialize;
3827 END IF;
3828
3829 -- initialize API return status to success
3830 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
3831
3832 -- Initialize the table
3833 --
3834 -- Debug Statements
3835 --
3836 IF l_debug_on THEN
3837 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DOCUMENT_PUB.DOCUMENT_TABTYPE',WSH_DEBUG_SV.C_PROC_LEVEL);
3838 END IF;
3839 --
3840 x_document_tab := wsh_document_pub.document_tabtype();
3841
3842
3843 FOR doc_rec IN doc_csr
3844 LOOP
3845 x_document_tab.extend;
3846 x_document_tab(i).document_instance_id:= doc_rec.document_instance_id;
3847 x_document_tab(i).document_type := doc_rec.document_type;
3848 x_document_tab(i).entity_name := doc_rec.entity_name;
3849 x_document_tab(i).entity_id := doc_rec.entity_id;
3850 x_document_tab(i).doc_sequence_category_id
3851 := doc_rec.doc_sequence_category_id;
3852 x_document_tab(i).sequence_number := doc_rec.sequence_number;
3853 x_document_tab(i).status := doc_rec.status;
3854 x_document_tab(i).final_print_date := doc_rec.final_print_date;
3855 x_document_tab(i).created_by := doc_rec.created_by;
3856 x_document_tab(i).creation_date := doc_rec.creation_date;
3857 x_document_tab(i).last_updated_by := doc_rec.last_updated_by;
3858 x_document_tab(i).last_update_date := doc_rec.last_update_date;
3859 x_document_tab(i).last_update_login := doc_rec.last_update_login;
3860 x_document_tab(i).program_application_id
3861 := doc_rec.program_application_id;
3862 x_document_tab(i).program_id := doc_rec.program_id;
3863 x_document_tab(i).program_update_date := doc_rec.program_update_date;
3864 x_document_tab(i).request_id := doc_rec.request_id;
3865 x_document_tab(i).attribute_category := doc_rec.attribute_category;
3866 x_document_tab(i).attribute1 := doc_rec.attribute1;
3867 x_document_tab(i).attribute2 := doc_rec.attribute2;
3868 x_document_tab(i).attribute3 := doc_rec.attribute3;
3869 x_document_tab(i).attribute4 := doc_rec.attribute4;
3870 x_document_tab(i).attribute5 := doc_rec.attribute5;
3871 x_document_tab(i).attribute6 := doc_rec.attribute6;
3872 x_document_tab(i).attribute7 := doc_rec.attribute7;
3873 x_document_tab(i).attribute8 := doc_rec.attribute8;
3874 x_document_tab(i).attribute9 := doc_rec.attribute9;
3875 x_document_tab(i).attribute10 := doc_rec.attribute10;
3876 x_document_tab(i).attribute11 := doc_rec.attribute11;
3877 x_document_tab(i).attribute12 := doc_rec.attribute12;
3878 x_document_tab(i).attribute13 := doc_rec.attribute13;
3879 x_document_tab(i).attribute14 := doc_rec.attribute14;
3880 x_document_tab(i).attribute15 := doc_rec.attribute15;
3881 i := i + 1;
3882 END LOOP;
3883
3884 -- get message count and the message itself (if only one message)
3885 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
3886 p_data => x_msg_data);
3887 -- Standard check of p_commit.
3888 IF FND_API.to_boolean( p_commit ) THEN
3889 COMMIT WORK;
3890 END IF;
3891 --
3892 -- Debug Statements
3893 --
3894 IF l_debug_on THEN
3895 WSH_DEBUG_SV.log(l_module_name,'X_DOCUMENT_TAB.COUNT',x_document_tab.count);
3896 WSH_DEBUG_SV.pop(l_module_name);
3897 END IF;
3898 --
3899 EXCEPTION
3900
3901 WHEN FND_API.g_exc_error THEN
3902 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
3903 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3904 p_data => x_msg_data );
3905
3906 --
3907 -- Debug Statements
3908 --
3909 IF l_debug_on THEN
3910 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3911 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3912 END IF;
3913 --
3914 WHEN FND_API.g_exc_unexpected_error THEN
3915 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3916 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3917 p_data => x_msg_data );
3918
3919 --
3920 -- Debug Statements
3921 --
3922 IF l_debug_on THEN
3923 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3924 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3925 END IF;
3926 --
3927 WHEN others THEN
3928 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
3929 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
3930 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
3931 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
3932 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
3933 WSH_UTIL_CORE.add_message (x_return_status);
3934 FND_MSG_PUB.count_and_get ( p_count => x_msg_count,
3935 p_data => x_msg_data );
3936 --
3937 -- Debug Statements
3938 --
3939 IF l_debug_on THEN
3940 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3941 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3942 END IF;
3943 --
3944 END Get_All_Documents;
3945
3946 ------------------------------------------------------------------------------
3947 -- PROCEDURE : Lock_Document PUBLIC
3948 -- VERSION : 1.0
3949 -- COMMENT : Locks a document row
3950 --
3951 -- PARAMETER LIST :
3952 --
3953 -- IN
3954 --
3955 -- p_rowid Rowid of wsh_document_instances table
3956 -- p_document_instance_id document instance id
3957 -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
3958 -- p_sequence_number sequence number of the document
3959 -- p_status status of the document
3960 -- p_final_print_date final print date
3961 -- p_entity_name Entity for which the document is being updated
3962 -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
3963 -- p_entity_id Entity id that the document belongs to
3964 -- example: delivery_id, delivery_leg_id, etc
3965 -- p_doc_sequence_category_id document sequence category id
3966 -- p_pod_flag pod_flag for the document
3967 -- p_pod_by pod_by for the document
3968 -- p_pod_date pod_date for the document
3969 -- p_reason_of_transport reason of transport that describes the delivery
3970 -- p_description external aspect of the delivery
3971 -- p_cod_amount cod_amount of the document
3972 -- p_cod_currency_code cod_currency_code of the document
3973 -- p_cod_remit_to cod_remit_to of the document
3974 -- p_cod_charge_paid_by cod_charge_paid_by of the document
3975 -- p_problem_contact_reference problem_contact_referene of the document
3976 -- p_bill_freight_to bill_freight_to of the document
3977 -- p_carried_by carried_by of the document
3978 -- p_port_of_loading port_of_loading of the docucent
3979 -- p_port_of_discharge port_of_discharge of the document
3980 -- p_booking_office booking_office of the document
3981 -- p_booking_number booking_number of the document
3982 -- p_service_contract service_contract of the document
3983 -- p_shipper_export_ref shipper_export_ref of the document
3984 -- p_carrier_export_ref carrier_export_ref of the document
3985 -- p_bol_notify_party bol_notify_party of the document
3986 -- p_supplier_code supplier_code of the document
3987 -- p_aetc_number aetc_number of the document
3988 -- p_shipper_signed_by shipper_signed_by of the document
3989 -- p_shipper_date shipper_date of the document
3990 -- p_carrier_signed_by carrier_signed_by of the document
3991 -- p_carrier_date carrier_date of the document
3992 -- p_bol_issue_office bol_issue_office of the document
3993 -- p_bol_issued_by bol_issued_by of the document
3994 -- p_bol_date_issued bol_date_issued of the document
3995 -- p_shipper_hm_by shipper_bm_by of the document
3996 -- p_shipper_hm_date shipper_hm_date of the document
3997 -- p_carrier_hm_by carrier_hm_by of the document
3998 -- p_carrier_hm_date carrier_hm_date of the document
3999 -- p_created_by standard who column
4000 -- p_creation_date standard who column
4001 -- p_last_updated_by standard who column
4002 -- p_last_update_date standard who column
4003 -- p_last_update_login standard who column
4004 -- p_program_applicaiton_id standard who column
4005 -- p_program_id standard who column
4006 -- p_program_update_date standard who column
4007 -- p_request_id standard who column
4008 -- p_attribute_category Descriptive Flex field context
4009 -- p_attribute1 Descriptive Flex field
4010 -- p_attribute2 Descriptive Flex field
4011 -- p_attribute3 Descriptive Flex field
4012 -- p_attribute4 Descriptive Flex field
4013 -- p_attribute5 Descriptive Flex field
4014 -- p_attribute6 Descriptive Flex field
4015 -- p_attribute7 Descriptive Flex field
4016 -- p_attribute8 Descriptive Flex field
4017 -- p_attribute9 Descriptive Flex field
4018 -- p_attribute10 Descriptive Flex field
4019 -- p_attribute11 Descriptive Flex field
4020 -- p_attribute12 Descriptive Flex field
4021 -- p_attribute13 Descriptive Flex field
4022 -- p_attribute14 Descriptive Flex field
4023 -- p_attribute15 Descriptive Flex field
4024 --
4025 -- OUT
4026 --
4027 -- x_return_status API return status ('S', 'E', 'U')
4028 --
4029 -- PRE-CONDITIONS : None
4030 -- POST-CONDITIONS : None
4031 -- EXCEPTIONS : None
4032 --
4033 -- NOTES : 1. Called from Shipping trx form only. Not an API.
4034 -- Does not conform to API standards.
4035 --
4036 -- 2. In a consolidation situation, this routine looks
4037 -- for a lock only on the parent document only.
4038 --
4039 ------------------------------------------------------------------------------
4040
4041
4042 PROCEDURE Lock_Document
4043 ( p_rowid IN VARCHAR2
4044 , p_document_instance_id IN NUMBER
4045 , p_document_type IN VARCHAR2
4046 , p_sequence_number IN VARCHAR2
4047 , p_status IN VARCHAR2
4048 , p_final_print_date IN DATE
4049 , p_entity_name IN VARCHAR2
4050 , p_entity_id IN NUMBER
4051 , p_doc_sequence_category_id IN NUMBER
4052 , p_created_by IN NUMBER
4053 , p_creation_date IN DATE
4054 , p_last_updated_by IN NUMBER
4055 , p_last_update_date IN DATE
4056 , p_last_update_login IN NUMBER
4057 , p_program_application_id IN NUMBER
4058 , p_program_id IN NUMBER
4059 , p_program_update_date IN DATE
4060 , p_request_id IN NUMBER
4061 , p_attribute_category IN VARCHAR2
4062 , p_attribute1 IN VARCHAR2
4063 , p_attribute2 IN VARCHAR2
4064 , p_attribute3 IN VARCHAR2
4065 , p_attribute4 IN VARCHAR2
4066 , p_attribute5 IN VARCHAR2
4067 , p_attribute6 IN VARCHAR2
4068 , p_attribute7 IN VARCHAR2
4069 , p_attribute8 IN VARCHAR2
4070 , p_attribute9 IN VARCHAR2
4071 , p_attribute10 IN VARCHAR2
4072 , p_attribute11 IN VARCHAR2
4073 , p_attribute12 IN VARCHAR2
4074 , p_attribute13 IN VARCHAR2
4075 , p_attribute14 IN VARCHAR2
4076 , p_attribute15 IN VARCHAR2
4077 , x_return_status OUT NOCOPY VARCHAR2
4078 ) IS
4079
4080 counter NUMBER;
4081 CURSOR lock_csr IS
4082 SELECT
4083 document_instance_id
4084 , document_type
4085 , sequence_number
4086 , status
4087 , final_print_date
4088 , entity_name
4089 , entity_id
4090 , doc_sequence_category_id
4091 , created_by
4092 , creation_date
4093 , last_updated_by
4094 , last_update_date
4095 , last_update_login
4096 , program_application_id
4097 , program_id
4098 , program_update_date
4099 , request_id
4100 , attribute_category
4101 , attribute1
4102 , attribute2
4103 , attribute3
4104 , attribute4
4105 , attribute5
4106 , attribute6
4107 , attribute7
4108 , attribute8
4109 , attribute9
4110 , attribute10
4111 , attribute11
4112 , attribute12
4113 , attribute13
4114 , attribute14
4115 , attribute15
4116 FROM
4117 wsh_document_instances
4118 WHERE rowid = p_rowid
4119 FOR UPDATE OF document_instance_id NOWAIT;
4120 lock_rec lock_csr%rowtype;
4121 --
4122 l_debug_on BOOLEAN;
4123 --
4124 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_DOCUMENT';
4125 --
4126 BEGIN
4127 --
4128 -- Debug Statements
4129 --
4130 --
4131 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4132 --
4133 IF l_debug_on IS NULL
4134 THEN
4135 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4136 END IF;
4137 --
4138 IF l_debug_on THEN
4139 WSH_DEBUG_SV.push(l_module_name);
4140 --
4141 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
4142 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_INSTANCE_ID',P_DOCUMENT_INSTANCE_ID);
4143 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
4144 WSH_DEBUG_SV.log(l_module_name,'P_SEQUENCE_NUMBER',P_SEQUENCE_NUMBER);
4145 WSH_DEBUG_SV.log(l_module_name,'P_STATUS',P_STATUS);
4146 WSH_DEBUG_SV.log(l_module_name,'P_FINAL_PRINT_DATE',P_FINAL_PRINT_DATE);
4147 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
4148 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
4149 WSH_DEBUG_SV.log(l_module_name,'P_DOC_SEQUENCE_CATEGORY_ID',P_DOC_SEQUENCE_CATEGORY_ID);
4150 WSH_DEBUG_SV.log(l_module_name,'P_CREATED_BY',P_CREATED_BY);
4151 WSH_DEBUG_SV.log(l_module_name,'P_CREATION_DATE',P_CREATION_DATE);
4152 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);
4153 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);
4154 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);
4155 WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_APPLICATION_ID',P_PROGRAM_APPLICATION_ID);
4156 WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_ID',P_PROGRAM_ID);
4157 WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_UPDATE_DATE',P_PROGRAM_UPDATE_DATE);
4158 WSH_DEBUG_SV.log(l_module_name,'P_REQUEST_ID',P_REQUEST_ID);
4159 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE_CATEGORY',P_ATTRIBUTE_CATEGORY);
4160 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE1',P_ATTRIBUTE1);
4161 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE2',P_ATTRIBUTE2);
4162 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE3',P_ATTRIBUTE3);
4163 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE4',P_ATTRIBUTE4);
4164 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE5',P_ATTRIBUTE5);
4165 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE6',P_ATTRIBUTE6);
4166 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE7',P_ATTRIBUTE7);
4167 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE8',P_ATTRIBUTE8);
4168 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE9',P_ATTRIBUTE9);
4169 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE10',P_ATTRIBUTE10);
4170 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE11',P_ATTRIBUTE11);
4171 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE12',P_ATTRIBUTE12);
4172 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE13',P_ATTRIBUTE13);
4173 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE14',P_ATTRIBUTE14);
4174 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE15',P_ATTRIBUTE15);
4175 END IF;
4176 --
4177 OPEN lock_csr;
4178 FETCH lock_csr INTO lock_rec;
4179 IF lock_csr%NOTFOUND
4180 THEN
4181 CLOSE lock_csr;
4182 FND_MESSAGE.set_name ('FND', 'FORM_RECORD_DELETED');
4183 APP_EXCEPTION.raise_exception;
4184 END IF;
4185 CLOSE lock_csr;
4186
4187 -- verify the not null columns are identical
4188 IF ( lock_rec.document_instance_id = p_document_instance_id
4189 AND lock_rec.document_type = p_document_type
4190 AND lock_rec.sequence_number = p_sequence_number
4191 AND lock_rec.entity_name = p_entity_name
4192 AND lock_rec.entity_id = p_entity_id
4193 AND lock_rec.created_by = p_created_by
4194 AND lock_rec.creation_date = p_creation_date
4195 AND lock_rec.last_updated_by = p_last_updated_by
4196 AND lock_rec.last_update_date = p_last_update_date
4197
4198 -- verify the nullable columns are either identical or both null
4199 AND ((lock_rec.status = p_status)
4200 OR
4201 (lock_rec.status IS NULL AND p_status IS NULL))
4202 AND ((lock_rec.final_print_date = p_final_print_date)
4203 OR
4204 (lock_rec.final_print_date IS NULL AND p_final_print_date IS NULL))
4205 AND ((lock_rec.doc_sequence_category_id = p_doc_sequence_category_id)
4206 OR
4207 (lock_rec.doc_sequence_category_id IS NULL AND
4208 p_doc_sequence_category_id IS NULL))
4209 AND ((lock_rec.last_update_login = p_last_update_login)
4210 OR
4211 (lock_rec.last_update_login IS NULL AND p_last_update_login IS NULL))
4212 AND ((lock_rec.program_application_id = p_program_application_id)
4213 OR
4214 (lock_rec.program_application_id IS NULL AND
4215 p_program_application_id IS NULL))
4216 AND ((lock_rec.program_id = p_program_id)
4217 OR
4218 (lock_rec.program_id IS NULL AND p_program_id IS NULL))
4219 AND ((lock_rec.program_update_date = p_program_update_date)
4220 OR
4221 (lock_rec.program_update_date IS NULL AND p_program_update_date IS NULL))
4222 AND ((lock_rec.request_id = p_request_id)
4223 OR
4224 (lock_rec.request_id IS NULL AND p_request_id IS NULL))
4225 AND ((lock_rec.attribute_category = p_attribute_category)
4226 OR
4227 (lock_rec.attribute_category IS NULL AND p_attribute_category IS NULL))
4228 AND ((lock_rec.attribute1 = p_attribute1)
4229 OR
4230 (lock_rec.attribute1 IS NULL AND p_attribute1 IS NULL))
4231 AND ((lock_rec.attribute2 = p_attribute2)
4232 OR
4233 (lock_rec.attribute2 IS NULL AND p_attribute2 IS NULL))
4234 AND ((lock_rec.attribute3 = p_attribute3)
4235 OR
4236 (lock_rec.attribute3 IS NULL AND p_attribute3 IS NULL))
4237 AND ((lock_rec.attribute4 = p_attribute4)
4238 OR
4239 (lock_rec.attribute4 IS NULL AND p_attribute4 IS NULL))
4240 AND ((lock_rec.attribute5 = p_attribute5)
4241 OR
4242 (lock_rec.attribute5 IS NULL AND p_attribute5 IS NULL))
4243 AND ((lock_rec.attribute6 = p_attribute6)
4244 OR
4245 (lock_rec.attribute6 IS NULL AND p_attribute6 IS NULL))
4246 AND ((lock_rec.attribute7 = p_attribute7)
4247 OR
4248 (lock_rec.attribute7 IS NULL AND p_attribute7 IS NULL))
4249 AND ((lock_rec.attribute8 = p_attribute8)
4250 OR
4251 (lock_rec.attribute8 IS NULL AND p_attribute8 IS NULL))
4252 AND ((lock_rec.attribute9 = p_attribute9)
4253 OR
4254 (lock_rec.attribute9 IS NULL AND p_attribute9 IS NULL))
4255 AND ((lock_rec.attribute10 = p_attribute10)
4256 OR
4257 (lock_rec.attribute10 IS NULL AND p_attribute10 IS NULL))
4258 AND ((lock_rec.attribute11 = p_attribute11)
4259 OR
4260 (lock_rec.attribute11 IS NULL AND p_attribute11 IS NULL))
4261 AND ((lock_rec.attribute12 = p_attribute12)
4262 OR
4263 (lock_rec.attribute12 IS NULL AND p_attribute12 IS NULL))
4264 AND ((lock_rec.attribute13 = p_attribute13)
4265 OR
4266 (lock_rec.attribute13 IS NULL AND p_attribute13 IS NULL))
4267 AND ((lock_rec.attribute14 = p_attribute14)
4268 OR
4269 (lock_rec.attribute14 IS NULL AND p_attribute14 IS NULL))
4270 AND ((lock_rec.attribute15 = p_attribute15)
4271 OR
4272 (lock_rec.attribute15 IS NULL AND p_attribute15 IS NULL))
4273 )
4274 THEN
4275 --
4276 -- Debug Statements
4277 --
4278 IF l_debug_on THEN
4279 WSH_DEBUG_SV.pop(l_module_name);
4280 END IF;
4281 --
4282 return;
4283 ELSE
4284 FND_MESSAGE.set_name('FND','FORM_RECORD_CHANGED');
4285 APP_EXCEPTION.raise_exception;
4286 END IF;
4287
4288 --
4289 -- Debug Statements
4290 --
4291 IF l_debug_on THEN
4292 WSH_DEBUG_SV.pop(l_module_name);
4293 END IF;
4294 --
4295 END Lock_Document;
4296
4297
4298 ------------------------------------------------------------------------------
4299 -- PROCEDURE : set_template PUBLIC
4300 -- VERSION : 1.0
4301 -- COMMENT : This procedure is called before calling fnd_request.submit to
4302 -- set the layout template so that pdf output is generated.
4303 -- Template is obtained from shipping parameters based on the
4304 -- organization_id.
4305 -- PARAMETER LIST :
4306 --
4307 -- IN
4308 --
4309 -- p_organization_id Organization Id
4310 -- p_report 'BOL'/'MBOL'/'PAK'
4311 --
4312 -- OUT
4313 --
4314 -- x_conc_prog_name 'WSHRDBOL'/'WSHRDBOLX'/'WSHRDMBL'/'WSHRDMBLX'/'WSHRDPAK'/'WSHRDPAKX'
4315 -- x_return_status API return status ('S', 'E', 'U')
4316 --
4317 --
4318 -- PRE-CONDITIONS : None
4319 -- POST-CONDITIONS : None
4320 -- EXCEPTIONS : None
4321 ------------------------------------------------------------------------------
4322
4323
4324 PROCEDURE set_template ( p_organization_id NUMBER,
4325 p_report VARCHAR2,
4326 p_template_name VARCHAR2,
4327 x_conc_prog_name OUT NOCOPY VARCHAR2,
4328 x_return_status OUT NOCOPY VARCHAR2 ) IS
4329
4330 l_language VARCHAR2(100);
4331 l_territory VARCHAR2(100);
4332 l_param_value_info WSH_SHIPPING_PARAMS_PVT.parameter_value_rec_typ;
4333 l_report_template VARCHAR2(80);
4334 l_return_status VARCHAR2(1);
4335 l_status BOOLEAN;
4336 l_conc_prog_name VARCHAR2(10);
4337 get_shipping_param_err EXCEPTION;
4338
4339 l_debug_on BOOLEAN;
4340 --
4341 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'set_template';
4342
4343 BEGIN
4344
4345 --
4346 -- Debug Statements
4347 --
4348 --
4349 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4350 --
4351 IF l_debug_on IS NULL
4352 THEN
4353 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4354 END IF;
4355 --
4356 IF l_debug_on THEN
4357 WSH_DEBUG_SV.push(l_module_name);
4358 --
4359 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
4360 WSH_DEBUG_SV.log(l_module_name,'P_REPORT',P_REPORT);
4361 END IF;
4362 --
4363 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4364
4365 l_param_value_info.organization_id := p_organization_id;
4366 l_param_value_info.class_code(1) := 'XDO_TEMPLATE';
4367 l_param_value_info.param_name(1) := p_template_name;
4368
4369 IF l_debug_on THEN
4370 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.Get',WSH_DEBUG_SV.C_PROC_LEVEL);
4371 END IF;
4372
4373 wsh_shipping_params_pvt.get(l_param_value_info,l_return_status);
4374 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR))
4375 THEN
4376 IF l_debug_on THEN
4377 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_SHIPPING_PARAMS_PVT.Get returned '||l_return_status);
4378 END IF;
4379 RAISE get_shipping_param_err;
4380 END IF;
4381 --
4382 IF l_debug_on THEN
4383 WSH_DEBUG_SV.logmsg(l_module_name,'param_name.count '||l_param_value_info.param_name.COUNT);
4384 WSH_DEBUG_SV.logmsg(l_module_name,'param_name_chr.count '||l_param_value_info.param_value_chr.COUNT);
4385 END IF;
4386 --
4387 IF (l_param_value_info.param_value_chr.COUNT >0 and l_param_value_info.param_name.COUNT >0) THEN
4388 IF (l_param_value_info.param_name(1) = p_template_name) THEN
4389 l_report_template := l_param_value_info.param_value_chr(1);
4390 END IF;
4391 ELSE
4392 IF l_debug_on THEN
4393 WSH_DEBUG_SV.logmsg(l_module_name,'No Parameters returned from WSH_SHIPPING_PARAMS_PVT.Get ');
4394 END IF;
4395 RAISE get_shipping_param_err;
4396 END IF;
4397
4398 IF l_debug_on THEN
4399 wsh_debug_sv.log(l_module_name, 'Report Template from Shipping Parameters ',l_report_template);
4400 END IF;
4401
4402 IF (l_report_template IS NULL ) THEN
4403 l_conc_prog_name := 'WSHRD' || p_report;
4404 ELSE
4405 --{
4406 l_conc_prog_name := 'WSHRD' || p_report || 'X';
4407 SELECT ISO_LANGUAGE, ISO_TERRITORY INTO l_language, l_territory
4408 FROM FND_LANGUAGES
4409 WHERE LANGUAGE_CODE = userenv('LANG');
4410
4411 IF l_debug_on THEN
4412 wsh_debug_sv.log(l_module_name, 'language ', l_language);
4413 wsh_debug_sv.log(l_module_name, 'territory ', l_territory);
4414 END IF;
4415
4416 l_status := fnd_request.add_layout
4417 ('WSH',
4418 l_report_template,
4419 l_language,
4420 l_territory,
4421 'PDF');
4422 IF l_debug_on THEN
4423 wsh_debug_sv.log(l_module_name,'Return Status After Calling fnd_request.add_layout ',l_status);
4424 END IF;
4425 IF (l_status=FALSE) THEN
4426 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4427 wsh_util_core.add_message(x_return_status,l_module_name);
4428 END IF;
4429 --}
4430 END IF;
4431 x_conc_prog_name := l_conc_prog_name;
4432
4433 IF l_debug_on THEN
4434 WSH_DEBUG_SV.pop(l_module_name);
4435 END IF;
4436
4437 EXCEPTION
4438 WHEN get_shipping_param_err THEN
4439 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4440 FND_MESSAGE.Set_Name('WSH', 'WSH_PARAM_NOT_DEFINED');
4441 FND_MESSAGE.Set_Token('ORGANIZATION_CODE',
4442 wsh_util_core.get_org_name(p_organization_id));
4443 wsh_util_core.add_message(x_return_status,l_module_name);
4444
4445 --
4446 IF l_debug_on THEN
4447 WSH_DEBUG_SV.logmsg(l_module_name,'Failed to get Shipping Parameters',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4448 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:GET_SHIPPING_PARAM_ERR');
4449 END IF;
4450
4451 WHEN OTHERS THEN
4452
4453 wsh_util_core.default_handler('WSH_DOCUMENT_PVT.set_template',l_module_name);
4454 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
4455 --
4456 IF l_debug_on THEN
4457 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4458 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4459 END IF;
4460 End set_template;
4461
4462
4463 END WSH_Document_PVT;
[Home] [Help]