[Home] [Help]
PACKAGE BODY: APPS.FTE_TRACKING_WRAPPER
Source
1 PACKAGE BODY FTE_TRACKING_WRAPPER as
2 /* $Header: FTETKWRB.pls 120.6 2006/02/13 16:05:22 schennal noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FTE_TRACKING_WRAPPER';
5
6 --========================================================================
7 -- PROCEDURE : populate_child_delivery_legs FTE Tracking wrapper
8 --
9 -- COMMENT : Populate the child legs when Tracking information is
10 -- sent for the parent_delivery i.e Populate FTE_SHIPMENT_
11 -- STATUS_HEADERS, FTE_SHIPMENT_STATUS_DETAIL, FTE_DELIVERY_PROOF
12 --========================================================================
13
14 PROCEDURE populate_child_delivery_legs
15 (
16 p_init_msg_list IN VARCHAR2,
17 p_delivery_leg_id IN NUMBER,
18 p_transaction_id IN NUMBER,
19 p_carrier_id IN NUMBER,
20 x_return_status OUT NOCOPY VARCHAR2,
21 x_msg_count OUT NOCOPY NUMBER,
22 x_msg_data OUT NOCOPY VARCHAR2
23 ) is
24
25 cursor c_child_delivery_leg (c_parent_delivery_leg_id NUMBER) is
26 select delivery_leg_id, delivery_id from
27 wsh_Delivery_legs where parent_delivery_leg_id = c_parent_delivery_leg_id;
28
29
30 l_transaction_id_s number ;
31 l_activity_id_s number;
32 l_address_to_id number;
33 l_content_details_id number;
34 l_content_pod_id number;
35 l_message_partner_id number;
36 l_content_exceptions_id number;
37
38 l_delivery_leg_id number;
39 l_delivery_id number;
40
41
42 --Added for Funtional workflow
43 l_organization_id NUMBER;
44 l_parameter_list wf_parameter_list_t;
45 l_delivery_id_tk NUMBER;
46 l_return_statuswf VARCHAR2(1);
47
48 l_received_date DATE;
49 l_flag number;
50
51 --Declaration to handle out parameter
52 l_exception_message VARCHAR2(2000);
53 l_return_status NUMBER;
54 l_error_token_text NUMBER;
55 l_error_id NUMBER;
56 l_sql_error_code VARCHAR2(2000);
57 l_sql_error_msg VARCHAR2(2000);
58 l_procedure_name VARCHAR2(240) := 'FTE_TRACKING_WRAPPER.populate_child_delivery_legs';
59
60 cursor get_org_delivery_info ( c_delivery_leg_id NUMBER) IS
61 SELECT wnd.delivery_id, wnd.organization_id FROM
62 wsh_delivery_legs wdl, wsh_new_deliveries wnd
63 WHERE wdl.delivery_id = wnd.delivery_id AND
64 wdl.delivery_leg_id = c_delivery_leg_id;
65
66 BEGIN
67 SAVEPOINT POPULATE_CHILD_LEGS_PUB;
68
69 -- Initialize message list if p_init_msg_list is set to TRUE.
70 --
71 --
72 IF FND_API.to_Boolean( p_init_msg_list )
73 THEN
74 FND_MSG_PUB.initialize;
75 END IF;
76 --
77 --
78 -- Initialize API return status to success
79 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
80 x_msg_count := 0;
81 x_msg_data := '';
82
83
84
85 OPEN c_child_delivery_leg(p_delivery_leg_id);
86 LOOP
87 FETCH c_child_delivery_leg into l_delivery_leg_id,l_delivery_id;
88 EXIT when c_child_delivery_leg%NOTFOUND;
89
90
91 --Create a new Transaction id for records in headers/detail/proof
92 select FTE_TRACKING_TRANSACTION_S.nextval into l_transaction_id_s from dual;
93
94
95 --Create a new Activity Id to record the activity in Activities table
96 select WSH_DELIVERY_LEG_ACTIVITIES_S.nextval into l_activity_id_s from dual;
97
98
99 --Insert into Activities table
100 insert into wsh_delivery_leg_activities
101 (activity_id, delivery_leg_id, activity_date, activity_type,
102 creation_date, created_by, last_update_date, last_updated_by)
103 values
104 (l_activity_id_s, l_delivery_leg_id, sysdate, 'TRACKING',
105 sysdate, p_carrier_id, sysdate, p_carrier_id);
106 begin
107 --Insert into Headers table
108 insert into fte_shipment_status_headers(
109 TRANSACTION_ID, delivery_leg_id, delivery_id, MESSAGE_TYPE, SHIPMENT_STATUS_ID,
110 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
111 CARRIER_NAME, GENERATION_DATE , STATUS , TRACKING_ID, TRACKING_ID_TYPE,
112 BILL_OF_LADING , CARRIER_SERVICE_LEVEL, CONTAINER_ID , CONTAINER_SEAL ,
113 ARRIVAL_DATE, RECEIVED_DATE , DELIVERY_SCHEDULED_DATE , DEPARTURE_DATE,
114 ESTIMATED_ARRIVAL_DATE, ESTIMATED_DEPARTURE_DATE, BEGIN_LOADING_DATE,
115 END_LOADING_DATE , PROMISED_SHIPMENT_DATE , PROMISED_DELIVERY_DATE ,
116 SHIPPED_DATE, EXPECTED_SHIPMENT_DATE, BEGIN_UNLOADING_DATE, END_UNLOADING_DATE ,
117 DESCRIPTION , FREIGHT_CLASS, HAZARDOUS_MATERIAL, LOAD_POINT, NOTES1,NOTES2 , NOTES3,
118 NOTES4 ,NOTES5, NOTES6, NOTES7 , NOTES8 , NOTES9,
119 SHIP_UNIT_QUANTITY, SHIP_UNIT_UOM, VOLUME , VOLUME_UOM ,
120 WEIGHT, WEIGHT_UOM, ROUTE_ID , ROUTE_TYPE,
121 SHIP_NOTES, SHIPPER_NUMBER , SHIP_POINT, SPECIAL_HANDLING,
122 STOP_NUMBER, SHIPPING_METHOD, SHIP_FROM_PARTNER, SHIP_TO_PARTNER,
123 CARRIER_PARTNER, BILL_TO_PARTNER, NOTIFY_PARTNER, HOLD_AT_PARTNER, RETURN_TO_PARTNER, MARK_FOR_PARTNER,
124 IMPORTER_PARTNER, EXPORTER_PARTNER, DELIVERY_DETAIL_ID, LICENSE_PLATE_NUMBER, REASON_CODE,
125 ACTIVITY_ID
126 )
127 select
128 l_transaction_id_s, l_delivery_leg_id,l_delivery_id,
129 MESSAGE_TYPE, FTE_TRACKING_STATUS_S.nextval,
130 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
131 CARRIER_NAME, GENERATION_DATE , STATUS , TRACKING_ID, TRACKING_ID_TYPE,
132 BILL_OF_LADING , CARRIER_SERVICE_LEVEL, CONTAINER_ID , CONTAINER_SEAL ,
133 ARRIVAL_DATE, RECEIVED_DATE , DELIVERY_SCHEDULED_DATE , DEPARTURE_DATE,
134 ESTIMATED_ARRIVAL_DATE, ESTIMATED_DEPARTURE_DATE, BEGIN_LOADING_DATE,
135 END_LOADING_DATE , PROMISED_SHIPMENT_DATE , PROMISED_DELIVERY_DATE ,
136 SHIPPED_DATE, EXPECTED_SHIPMENT_DATE, BEGIN_UNLOADING_DATE, END_UNLOADING_DATE ,
137 DESCRIPTION , FREIGHT_CLASS, HAZARDOUS_MATERIAL, LOAD_POINT, NOTES1,NOTES2 , NOTES3,
138 NOTES4 ,NOTES5, NOTES6, NOTES7 , NOTES8 , NOTES9,
139 SHIP_UNIT_QUANTITY, SHIP_UNIT_UOM, VOLUME , VOLUME_UOM ,
140 WEIGHT, WEIGHT_UOM, ROUTE_ID , ROUTE_TYPE,
141 SHIP_NOTES, SHIPPER_NUMBER , SHIP_POINT, SPECIAL_HANDLING,
142 STOP_NUMBER, SHIPPING_METHOD, SHIP_FROM_PARTNER, SHIP_TO_PARTNER,
143 CARRIER_PARTNER, BILL_TO_PARTNER, NOTIFY_PARTNER, HOLD_AT_PARTNER, RETURN_TO_PARTNER, MARK_FOR_PARTNER,
144 IMPORTER_PARTNER, EXPORTER_PARTNER, DELIVERY_DETAIL_ID, LICENSE_PLATE_NUMBER, REASON_CODE,
145 l_activity_id_s
146 from fte_shipment_status_headers where transaction_id = p_transaction_id;
147 EXCEPTION
148 WHEN OTHERS THEN
149 null;
150
151 END ;
152 -- Insert into details table
153 BEGIN
154
155 select fte_tracking_status_s.nextval into l_content_details_id from dual;
156 insert into fte_shipment_status_details(
157 SHIPMENT_STATUS_DETAIL_ID,
158 DELIVERY_LEG_ID,
159 TRANSACTION_ID,
160 REPORT_DATE,
161 SHIPMENT_STATUS,
162 CHANGED_STATUS_DATE,
163 DESCRIPTION,
164 CREATED_BY,
165 CREATION_DATE,
166 LAST_UPDATED_BY,
167 LAST_UPDATE_DATE,
168 LAST_UPDATE_LOGIN,
169 SHIP_UNIT_SEQ,
170 SHIP_UNIT_TOTAL,
171 TRACKING_ID,
172 TRACKING_ID_TYPE)
173 SELECT
174 l_content_details_id,
175 l_delivery_leg_id,
176 l_transaction_id_s,
177 REPORT_DATE,
178 SHIPMENT_STATUS,
179 CHANGED_STATUS_DATE,
180 DESCRIPTION,
181 CREATED_BY,
182 CREATION_DATE,
183 LAST_UPDATED_BY,
184 LAST_UPDATE_DATE,
185 LAST_UPDATE_LOGIN,
186 SHIP_UNIT_SEQ,
187 SHIP_UNIT_TOTAL,
188 TRACKING_ID,
189 TRACKING_ID_TYPE
190 FROM fte_shipment_status_details WHERE transaction_id = p_transaction_id;
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 NULL; --No records in FTE_SHIPMENT_STATUS_DETAILS
195 END ;
196 -- Insert into delivery proof table
197 BEGIN
198 select fte_tracking_status_s.nextval into l_content_pod_id from dual;
199 insert into fte_delivery_proof(
200 ID,
201 TRANSACTION_ID,
202 RECEIVED_DATE,
203 --NAME1, -- to fix Bug#5031206
204 SHIP_UNIT_QUANTITY,
205 SHIP_UNIT_UOM,
206 NOTES1,
207 NOTES2,
208 NOTES3,
209 NOTES4,
210 NOTES5,
211 NOTES6,
212 NOTES7,
213 NOTES8,
214 NOTES9,
215 SHIP_UNIT_SEQ,
216 SHIP_UNIT_TOTAL,
217 CREATED_BY,
218 CREATION_DATE,
219 LAST_UPDATED_BY,
220 LAST_UPDATE_DATE,
221 LAST_UPDATE_LOGIN,
222 TRACKING_ID,
223 TRACKING_ID_TYPE,
224 CONSIGNEE_NAME,
225 STATUS,
226 SHIPMENT_WEIGHT,
227 SHIPMENT_VOLUME,
228 LOCATION
229 )
230 SELECT
231 l_content_pod_id,
232 l_transaction_id_s,
233 RECEIVED_DATE,
234 --NAME1, --To fix bug#5031206
235 SHIP_UNIT_QUANTITY,
236 SHIP_UNIT_UOM,
237 NOTES1,
238 NOTES2,
239 NOTES3,
240 NOTES4,
241 NOTES5,
242 NOTES6,
243 NOTES7,
244 NOTES8,
245 NOTES9,
246 SHIP_UNIT_SEQ,
247 SHIP_UNIT_TOTAL,
248 CREATED_BY,
249 CREATION_DATE,
250 LAST_UPDATED_BY,
251 LAST_UPDATE_DATE,
252 LAST_UPDATE_LOGIN,
253
254 TRACKING_ID,
255 TRACKING_ID_TYPE,
256 CONSIGNEE_NAME,
257 STATUS,
258 SHIPMENT_WEIGHT,
259 SHIPMENT_VOLUME,
260 LOCATION
261 FROM fte_delivery_proof WHERE transaction_id = p_transaction_id;
262 EXCEPTION
263 WHEN OTHERS THEN
264 NULL; --No records in FTE_DELIVERY_PROOF table
265 END ;
266
267
268 --Inserting into fte_message partner as per FTEFSSI XGM to content
269 BEGIN
270
271 select fte_tracking_status_s.nextval into l_address_to_id from dual;
272 INSERT INTO
273 FTE_MESSAGE_PARTNER (
274 ID,
275 NAME1 ,
276 NAME2 ,
277 NAME3 ,
278 NAME4 ,
279 NAME5 ,
280 NAME6 ,
281 NAME7 ,
282 NAME8 ,
283 NAME9 ,
284 ONETIME ,
285 PARTNER_ID ,
286 PARTNER_TYPE ,
287 ACTIVE ,
288 CURRENCY ,
289 DESCRIPTION ,
290 DUNS_NUMBER ,
291 GL_ENTITIES ,
292 PARENT_ID ,
293 PARTNER_ID_X ,
294 PARTNER_RATING,
295 PARTNER_ROLE ,
296 PAYMENT_METHOD,
297 TAX_EXEMPT ,
298 TAX_ID ,
299 TERM_ID ,
300 CREATED_BY ,
301 CREATION_DATE ,
302 LAST_UPDATED_BY ,
303 LAST_UPDATE_DATE,
304 LAST_UPDATE_LOGIN ,
305 SHIPPER_ACCOUNT_NUMBER,
306 TRANSACTION_ID)
307 SELECT
308 l_address_to_id,
309 NAME1 ,
310 NAME2 ,
311 NAME3 ,
312 NAME4 ,
313 NAME5 ,
314 NAME6 ,
315 NAME7 ,
316 NAME8 ,
317 NAME9 ,
318 ONETIME ,
319 PARTNER_ID ,
320 PARTNER_TYPE ,
321 ACTIVE ,
322 CURRENCY ,
323 DESCRIPTION ,
324 DUNS_NUMBER ,
325 GL_ENTITIES ,
326 PARENT_ID ,
327 PARTNER_ID_X ,
328 PARTNER_RATING,
329 PARTNER_ROLE ,
330 PAYMENT_METHOD,
331 TAX_EXEMPT ,
332 TAX_ID ,
333 TERM_ID ,
334 CREATED_BY ,
335 CREATION_DATE ,
336 LAST_UPDATED_BY ,
337 LAST_UPDATE_DATE,
338 LAST_UPDATE_LOGIN ,
339 SHIPPER_ACCOUNT_NUMBER,
340 l_transaction_id_s from
341 fte_message_partner WHERE TRANSACTION_ID = l_transaction_id_s ;
342
343
344 BEGIN
345 INSERT INTO
346 FTE_MESSAGE_CONTACT(
347 CONTACT_ID ,
348 PARTNER_ID ,
349 NAME1 ,
350 NAME2 ,
351 NAME3 ,
352 NAME4 ,
353 NAME5 ,
354 NAME6 ,
355 NAME7 ,
356 NAME8 ,
357 NAME9 ,
358 CONTACT_TYPE ,
359 DESCRIPTION ,
360 EMAIL ,
361 FAX1 ,
362 FAX2 ,
363 FAX3 ,
364 FAX4 ,
365 FAX5 ,
366 FAX6 ,
367 FAX7 ,
368 FAX8 ,
369 FAX9 ,
370 TELEPHONE1 ,
371 TELEPHONE2 ,
372 TELEPHONE3 ,
373 TELEPHONE4 ,
374 TELEPHONE5 ,
375 TELEPHONE6 ,
376 TELEPHONE7 ,
377 TELEPHONE8 ,
378 TELEPHONE9 ,
379 CREATED_BY ,
380 CREATION_DATE ,
381 LAST_UPDATED_BY,
382 LAST_UPDATE_DATE,
383 LAST_UPDATE_LOGIN,
384 TRANSACTION_ID
385 )
386 SELECT
387 fte_tracking_status_s.nextval,
388 l_address_to_id,
389 NAME1 ,
390 NAME2 ,
391 NAME3 ,
392 NAME4 ,
393 NAME5 ,
394 NAME6 ,
395 NAME7 ,
396 NAME8 ,
397 NAME9 ,
398 CONTACT_TYPE ,
399 DESCRIPTION ,
400 EMAIL ,
401 FAX1 ,
402 FAX2 ,
403 FAX3 ,
404 FAX4 ,
405 FAX5 ,
406 FAX6 ,
407 FAX7 ,
408 FAX8 ,
409 FAX9 ,
410 TELEPHONE1 ,
411 TELEPHONE2 ,
412 TELEPHONE3 ,
413 TELEPHONE4 ,
414 TELEPHONE5 ,
415 TELEPHONE6 ,
416 TELEPHONE7 ,
417 TELEPHONE8 ,
418 TELEPHONE9 ,
419 CREATED_BY ,
420 CREATION_DATE ,
421 LAST_UPDATED_BY,
422 LAST_UPDATE_DATE,
423 LAST_UPDATE_LOGIN,
424 l_transaction_id_s
425 FROM
426 FTE_MESSAGE_CONTACT WHERE
427 TRANSACTION_ID = p_transaction_id;
428 EXCEPTION
429 WHEN OTHERS THEN
430 null;
431 END ;
432
433
434 EXCEPTION
435 WHEN OTHERS THEN
436 null;
437 END ;
438
439
440 BEGIN
441 INSERT INTO FTE_MESSAGE_LOCATION (
442 location_to_id,
443 ID ,
444 DESCRIPTION ,
445 GEOCOORDINATES ,
446 GEOCOORDINATES_TYPE ,
447 LOCATION_ID ,
448 LOCATION_ID_TYPE ,
449 SITELEVEL1 ,
450 SITELEVEL2 ,
451 SITELEVEL3 ,
452 SITELEVEL4 ,
453 SITELEVEL5 ,
454 SITELEVEL6 ,
455 SITELEVEL7 ,
456 SITELEVEL8 ,
457 SITELEVEL9 ,
458 ADDRESS_ID ,
459 CREATED_BY ,
460 CREATION_DATE ,
461 LAST_UPDATED_BY ,
462 LAST_UPDATE_DATE ,
463 LAST_UPDATE_LOGIN ,
464 TRANSACTION_ID ,
465 LOCATION_TO_TABLE
466 )
467 SELECT
468 DECODE(location_to_table,'FTE_DELIVERY_PROOF',l_content_pod_id,l_content_details_id),
469 fte_tracking_status_s.nextval,
470 DESCRIPTION ,
471 GEOCOORDINATES ,
472 GEOCOORDINATES_TYPE ,
473 LOCATION_ID ,
474 LOCATION_ID_TYPE ,
475 SITELEVEL1 ,
476 SITELEVEL2 ,
477 SITELEVEL3 ,
478 SITELEVEL4 ,
479 SITELEVEL5 ,
480 SITELEVEL6 ,
481 SITELEVEL7 ,
482 SITELEVEL8 ,
483 SITELEVEL9 ,
484 ADDRESS_ID ,
485 CREATED_BY ,
486 CREATION_DATE ,
487 LAST_UPDATED_BY ,
488 LAST_UPDATE_DATE ,
489 LAST_UPDATE_LOGIN ,
490 l_transaction_id_s ,
491 LOCATION_TO_TABLE
492 from
493 FTE_MESSAGE_LOCATION
494 where transaction_id = p_transaction_id;
495 EXCEPTION
496 WHEN OTHERS THEN
497 null;
498 END ;
499
500
501 BEGIN
502 INSERT INTO
503 FTE_MESSAGE_ADDRESS(
504 ADDRESS_TO_ID,ID ,
505 TRANSACTION_ID ,
506 ADDRESS_TO_TABLE ,
507 ADDR_LINE1 ,
508 ADDR_LINE2 ,
509 ADDR_LINE3 ,
510 ADDR_LINE4 ,
511 ADDR_LINE5 ,
512 ADDR_LINE6 ,
513 ADDR_LINE7 ,
514 ADDR_LINE8 ,
515 ADDR_LINE9 ,
516 ADDR_TYPE ,
517 ADDR_CITY ,
518 ADDR_COUNTY ,
519 ADDR_COUNTRY ,
520 ADDR_DESCRIPTION ,
521 ADDR_POSTAL_CODE ,
522 ADDR_REGION ,
523 ADDR_STATE ,
524 ADDR_TAX_JURISDICTION ,
525 ADDR_URL ,
526 FAX1 ,
527 FAX2 ,
528 FAX3 ,
529 FAX4 ,
530 FAX5 ,
531 FAX6 ,
532 FAX7 ,
533 FAX8 ,
534 FAX9 ,
535 TELEPHONE1 ,
536 TELEPHONE2 ,
537 TELEPHONE3 ,
538 TELEPHONE4 ,
539 TELEPHONE5 ,
540 TELEPHONE6 ,
541 TELEPHONE7 ,
542 TELEPHONE8 ,
543 TELEPHONE9 ,
544 CREATED_BY ,
545 CREATION_DATE ,
546 LAST_UPDATED_BY ,
547 LAST_UPDATE_DATE ,
548 LAST_UPDATE_LOGIN )
549 SELECT
550 decode (ADDRESS_TO_TABLE,'FTE_MESSAGE_PARTNER',
551 l_address_to_id,
552 (select id from fte_message_location where
553 location_to_table =( SELECT fl.location_to_table
554 FROM fte_message_location fl WHERE fl.id= fa.address_to_id
555 AND transaction_id = p_transaction_id ) and
556 transaction_id = l_transaction_id_s
557 )
558 ),
559 fte_tracking_status_s.nextval,
560 l_transaction_id_s ,
561 ADDRESS_TO_TABLE ,
562 ADDR_LINE1 ,
563 ADDR_LINE2 ,
564 ADDR_LINE3 ,
565 ADDR_LINE4 ,
566 ADDR_LINE5 ,
567 ADDR_LINE6 ,
568 ADDR_LINE7 ,
569 ADDR_LINE8 ,
570 ADDR_LINE9 ,
571 ADDR_TYPE ,
572 ADDR_CITY ,
573 ADDR_COUNTY ,
574 ADDR_COUNTRY ,
575 ADDR_DESCRIPTION ,
576 ADDR_POSTAL_CODE ,
577 ADDR_REGION ,
578 ADDR_STATE ,
579 ADDR_TAX_JURISDICTION ,
580 ADDR_URL ,
581 FAX1 ,
582 FAX2 ,
583 FAX3 ,
584 FAX4 ,
585 FAX5 ,
586 FAX6 ,
587 FAX7 ,
588 FAX8 ,
589 FAX9 ,
590 TELEPHONE1 ,
591 TELEPHONE2 ,
592 TELEPHONE3 ,
593 TELEPHONE4 ,
594 TELEPHONE5 ,
595 TELEPHONE6 ,
596 TELEPHONE7 ,
597 TELEPHONE8 ,
598 TELEPHONE9 ,
599 CREATED_BY ,
600 CREATION_DATE ,
601 LAST_UPDATED_BY ,
602 LAST_UPDATE_DATE ,
603 LAST_UPDATE_LOGIN
604 from fte_message_address fa
605 where transaction_id =p_transaction_id ;
606 EXCEPTION
607 WHEN OTHERS THEN
608 l_exception_message := substr(SQLERRM,1,100);
609
610 END ;
611
612 BEGIN
613 INSERT INTO FTE_SHIPMENT_STATUS_EXCEPTIONS
614 (
615 DETAIL_ID ,
616 EXCEPTION_ID ,
617 EXCEPTION_DATE ,
618 DESCRIPTION ,
619 REASON_CODE ,
620 CREATED_BY ,
621 CREATION_DATE ,
622 LAST_UPDATED_BY ,
623 LAST_UPDATE_DATE ,
624 LAST_UPDATE_LOGIN ,
625 LADING_QUANTITY ,
626 LADING_QUANTITY_UOM ,
627 TRANSACTION_ID
628 )
629 SELECT
630 l_content_details_id,
631 fte_tracking_status_s.nextval,
632 EXCEPTION_DATE ,
633 DESCRIPTION ,
634 REASON_CODE ,
635 CREATED_BY ,
636 CREATION_DATE ,
637 LAST_UPDATED_BY ,
638 LAST_UPDATE_DATE ,
639 LAST_UPDATE_LOGIN ,
640 LADING_QUANTITY ,
641 LADING_QUANTITY_UOM ,
642 l_transaction_id_s
643 FROM FTE_SHIPMENT_STATUS_EXCEPTIONS
644 WHERE TRANSACTION_ID = P_TRANSACTION_ID ;
645
646 select fte_tracking_status_s.currval into l_content_exceptions_id from dual;
647 EXCEPTION
648 WHEN OTHERS THEN
649 null;
650 END ;
651
652 BEGIN
653 INSERT INTO WSH_MESSAGE_ATTACHMENT
654 (
655 ATTACHMENT_ID ,
656 ATTACH_TO_ID ,
657 ATTACH_TO_TABLE ,
658 DESCRIPTION ,
659 FILETYPE ,
660 TITLE ,
661 FILE_CREATION_DATE ,
662 FILESIZE ,
663 FILESIZE_UOM ,
664 FILENAME ,
665 URI ,
666 COMPRESSION_TYPE ,
667 COMPRESSION_ID ,
668 NOTES1 ,
669 NOTES2 ,
670 NOTES3 ,
671 NOTES4 ,
672 NOTES5 ,
673 NOTES6 ,
674 NOTES7 ,
675 NOTES8 ,
676 NOTES9 ,
677 CREATED_BY ,
678 CREATION_DATE ,
679 LAST_UPDATED_BY ,
680 LAST_UPDATE_DATE ,
681 LAST_UPDATE_LOGIN ,
682 TRANSACTION_ID
683 )
684 SELECT
685 fte_tracking_status_s.nextval ,
686 DECODE(ATTACH_TO_TABLE,'FTE_MESSAGE_PARTNER',l_address_to_id,
687 'FTE_SHIPMENT_STATUS_DETAILS',l_content_details_id,
688 'FTE_SHIPMENT_STATUS_EXCEPTIONS',l_content_exceptions_id,
689 l_content_pod_id),
690 ATTACH_TO_TABLE ,
691 DESCRIPTION ,
692 FILETYPE ,
693 TITLE ,
694 FILE_CREATION_DATE ,
695 FILESIZE ,
696 FILESIZE_UOM ,
697 FILENAME ,
698 URI ,
699 COMPRESSION_TYPE ,
700 COMPRESSION_ID ,
701 NOTES1 ,
702 NOTES2 ,
703 NOTES3 ,
704 NOTES4 ,
705 NOTES5 ,
706 NOTES6 ,
707 NOTES7 ,
708 NOTES8 ,
709 NOTES9 ,
710 CREATED_BY ,
711 CREATION_DATE ,
712 LAST_UPDATED_BY ,
713 LAST_UPDATE_DATE ,
714 LAST_UPDATE_LOGIN ,
715 l_transaction_id_s
716 FROM
717 WSH_MESSAGE_ATTACHMENT WHERE TRANSACTION_ID = P_TRANSACTION_ID ;
718 EXCEPTION
719 WHEN OTHERS THEN
720 null;
721 END ;
722
723
724
725 --To ensure that only one record exist in details and in delivery proof for
726 --every transaction_id
727
728 -- Call the procedure get_delivery_details to delete all the
729 -- details and POD information for a delivery leg and retain only the
730 -- last detail and POD information
731
732 FTE_TRACKING_WRAPPER.get_delivery_details ( l_transaction_id_s, p_carrier_id,
733 l_exception_message,
734 l_return_status, l_error_token_text);
735
736
737 -- To update the recieced date for the last leg of a delivery when POD is received
738 BEGIN
739 select received_date,1 into l_received_date,l_flag
740 from fte_delivery_proof where transaction_id= l_transaction_id_s;
741 EXCEPTION
742 WHEN OTHERS THEN
743 NULL; --No records in FTE_DELIVERY_PROOF table
744 END ;
745
746 IF l_flag= 1 THEN
747 FTE_TRACKING_WRAPPER.call_last_delivery_leg(l_delivery_leg_id,l_received_date);
748 ELSE
749 --Added by shravisa for Release 12
750 --Raise Workflow Tracking Event when ever Tracking information comes for any
751 --Functional Workflow
752
753 OPEN get_org_delivery_info(l_delivery_leg_id);
754 FETCH get_org_delivery_info into l_delivery_id_tk,l_organization_id;
755 CLOSE get_org_delivery_info;
756
757
758
759 wf_event.AddParameterToList(
760 p_name=>'ORGANIZATION_ID',
761 p_value => l_organization_id,
762 p_parameterlist=> l_parameter_list);
763
764
765 --Do not handle the Return status from this method
766 WSH_WF_STD.raise_event(
767 p_entity_type => 'DELIVERY',
768 p_entity_id => TO_CHAR(l_delivery_id_tk),
769 p_event => 'oracle.apps.fte.delivery.trk.matchtrackingadvice',
770 p_parameters => l_parameter_list,
771 p_organization_id => l_organization_id,
772 x_return_status => l_return_statuswf);
773 END IF;
774
775
776 END LOOP;
777 CLOSE c_child_delivery_leg;
778
779
780 EXCEPTION
781 WHEN NO_DATA_FOUND THEN
782 ROLLBACK TO populate_child_legs_pub;
783 select wsh_interface_errors_s.nextval into l_error_id
784 from dual;
785
786 FTE_TRACKING_WRAPPER.insert_error_status
787 (l_error_id, 'FTE_SHIPMEMT_STATUS_HEADERS',
788 p_transaction_id, 3, 'ERROR', 'NO CHILD DELIVERY FOUND',
789 p_carrier_id);
790
791 x_return_status := FND_API.G_RET_STS_ERROR ;
792 FND_MESSAGE.SET_NAME('FTE','FTE_INVALID_TRACKING_INFO');
793 FND_MSG_PUB.ADD;
794 FND_MSG_PUB.Count_And_Get
795 (
796 p_count => x_msg_count,
797 p_data => x_msg_data,
798 p_encoded => FND_API.G_FALSE
799 );
800 WHEN OTHERS THEN
801 ROLLBACK TO populate_child_legs_pub;
802 select wsh_interface_errors_s.nextval into l_error_id
803 from dual;
804
805 FTE_TRACKING_WRAPPER.insert_error_status
806 (l_error_id, 'FTE_SHIPMEMT_STATUS_HEADERS',
807 p_transaction_id, 5, 'ERROR', 'OTHER EXCEPTION',
808 p_carrier_id);
809
810 l_sql_error_code := to_char(SQLCODE);
811 l_sql_error_msg := substr(SQLERRM,1,2000);
812 IF (l_exception_message IS NULL) THEN
813 l_exception_message :=
814 (l_sql_error_code||':'||l_sql_error_msg||' : '
815 ||' : '||l_procedure_name);
816 END IF;
817 x_return_status := FND_API.G_RET_STS_ERROR ;
818 FND_MESSAGE.SET_NAME('FTE','FTE_INVALID_TRACKING_INFO');
819 FND_MSG_PUB.ADD;
820 FND_MSG_PUB.Count_And_Get
821 (
822 p_count => x_msg_count,
823 p_data => x_msg_data,
824 p_encoded => FND_API.G_FALSE
825 );
826 END populate_child_delivery_legs;
827
828
829 --========================================================================
830 -- PROCEDURE : Get Location FTE Tracking wrapper
831 --
832 -- COMMENT : Get the city,state and country information from the
833 -- fte_message_address and populate the location cloumn of
834 -- fte_delivery_proof table using address_to_id as the foreign
835 -- key for fte_message_location .
836 --========================================================================
837
838 FUNCTION GET_LOCATION(p_location_id IN NUMBER)
839 RETURN VARCHAR2
840 IS
841 l_addr_state VARCHAR2(1000);
842 l_addr_city VARCHAR2(1000);
843 l_addr_country VARCHAR2(1000);
844 l_loca_csc VARCHAR2(1000) ;
845 CURSOR C_GET_CSC(p_loc_id NUMBER) IS
846 SELECT ADDR_CITY,
847 ADDR_STATE,
848 ADDR_COUNTRY
849 FROM
850 FTE_MESSAGE_ADDRESS
851 WHERE
852 ADDRESS_TO_ID=P_LOC_ID;
853 BEGIN
854
855 --GET VALLUES FROM FTE_MESSAGE_ADDRESS
856 OPEN C_GET_CSC(p_location_id);
857 FETCH C_GET_CSC INTO L_addr_CITY,L_addr_STATE,L_addr_COUNTRY;
858 CLOSE C_GET_CSC;
859
860 --CHECK FOR NULL AND CONCAT
861 IF l_addr_city IS NOT NULL THEN
862 l_loca_csc := l_loca_csc || l_addr_city || ', ';
863 END IF;
864 IF l_addr_state IS NOT NULL THEN
865 l_loca_csc := l_loca_csc || l_ADDR_state || ', ';
866 END IF;
867 IF l_addr_country IS NOT NULL THEN
868 l_loca_csc := l_loca_csc || l_addr_country;
869 END IF;
870
871 RETURN (l_loca_csc);
872 END GET_LOCATION;
873
874 --========================================================================
875 -- PROCEDURE : Insert_delete_delivery FTE Tracking wrapper
876 --
877 -- COMMENT : Insert the data from the header interface table to
878 -- wsh_delivery_leg_activities and wsh_delivery_leg_details.
879 -- Delete the record from the detail interface table if they are
880 -- repeated.
881 --========================================================================
882 PROCEDURE get_delivery_or_container(
883 p_transaction_id IN NUMBER,
884 x_exception_message OUT NOCOPY VARCHAR2,
885 x_return_status OUT NOCOPY NUMBER,
886 x_error_token_text OUT NOCOPY NUMBER) IS
887 l_delivery_id NUMBER;
888 l_delivery_id_tk NUMBER;
889 l_delivery_leg_id NUMBER;
890 l_delivery_detail_id NUMBER;
891 l_message_type VARCHAR2(10);
892 l_carrier_name VARCHAR2(30);
893 l_carrier_id NUMBER;
894 l_shipment_status VARCHAR2(30);
895 l_activity_date DATE;
896 l_arrival_date DATE;
897 l_departure_date DATE;
898 l_estimated_arrival_date DATE;
899 l_estimated_departure_date DATE;
900 l_end_loading_date DATE;
901 l_begin_loading_date DATE;
902 l_end_unloading_date DATE;
903 l_begin_unloading_date DATE;
904 l_notes VARCHAR2(1000);
905 l_description VARCHAR2(1000);
906 l_tracking_id VARCHAR2(30);
907 l_tracking_id_type VARCHAR2(30);
908
909 --Added for Funtional workflow
910 l_organization_id NUMBER;
911 l_parameter_list wf_parameter_list_t;
912
913 -- Added for Bug
914 l_received_date DATE;
915
916 l_waybill VARCHAR2(30);
917 l_booking_number VARCHAR2(30);
918 l_container_name VARCHAR2(30);
919 l_seal_code VARCHAR2(30);
920 l_tracking_number VARCHAR2(30);
921
922 l_exception_message VARCHAR2(2000);
923 l_return_status NUMBER;
924 l_error_token_text NUMBER;
925
926 l_sql_error_code VARCHAR2(2000);
927 l_sql_error_msg VARCHAR2(2000);
928 l_procedure_name VARCHAR2(240) := 'FTE_TRACKING_WRAPPER.get_delivery_or_container';
929
930 invalid_carrier EXCEPTION;
931 no_license_plate EXCEPTION;
932 invalid_tracking_id_type EXCEPTION;
933 l_error_id NUMBER;
934 l_location_id NUMBER;
935 l_location_code VARCHAR2(100);
936 l_address VARCHAR2(1000);
937 l_flag NUMBER;
938 l_msg_data VARCHAR2(2000);
939 l_msg_count NUMBER;
940 l_return_statusp VARCHAR2(1);
941 l_return_statuswf VARCHAR2(1);
942
943 cursor get_org_delivery_info ( c_delivery_leg_id NUMBER) IS
944 SELECT wnd.delivery_id, wnd.organization_id FROM
945 wsh_delivery_legs wdl, wsh_new_deliveries wnd
946 WHERE wdl.delivery_id = wnd.delivery_id AND
947 wdl.delivery_leg_id = c_delivery_leg_id;
948
949
950
951 BEGIN
952
953
954
955 --
956 -- Set the return Status Flag
957 --
958 x_return_status := 0;
959 x_exception_message := null;
960 x_error_token_text := 0;
961 -- Select header information for the transaction just entered
962 SELECT tracking_id, tracking_id_type, carrier_name, status, arrival_date,
963 departure_date, estimated_arrival_date, estimated_departure_date,
964 end_loading_date, begin_loading_date, end_unloading_date,
965 begin_unloading_date,
966 (notes1||notes2||notes3||notes4||notes5||notes6||notes7||notes8||notes9) as notes,
967 description
968 INTO l_tracking_id, l_tracking_id_type, l_carrier_name, l_shipment_status,
969 l_arrival_date, l_departure_date, l_estimated_arrival_date, l_estimated_departure_date,
970 l_end_loading_date, l_begin_loading_date, l_end_unloading_date, l_begin_unloading_date,
971 l_notes, l_description
972 FROM fte_shipment_status_headers
973 WHERE transaction_id = p_transaction_id;
974
975
976
977 SELECT carrier_name INTO l_carrier_name
978 FROM fte_shipment_status_headers WHERE transaction_id = p_transaction_id;
979
980 -- Select the carrier id for the carrier name passed through the XML
981 -- inner exception handling
982 BEGIN
983 --select party_id into l_carrier_id from hz_parties where party_name = l_carrier_name;
984 SELECT h.party_id INTO l_carrier_id
985 FROM hz_parties h, wsh_carriers w
986 WHERE h.party_id = w.carrier_id
987 AND party_name = l_carrier_name;
988 EXCEPTION
989 WHEN NO_DATA_FOUND THEN
990 RAISE invalid_carrier;
991 END;
992
993
994 ---- Update fte_shipment_status_header,fte_shipment_status_details and fte_delivery_proof
995 -- Based on the tracking id type
996 IF (UPPER(l_tracking_id_type) = 'WAYBILL') THEN
997
998
999 SELECT /*+ first_rows ordered */
1000 wsh_delivery_legs.delivery_id, wsh_trips.carrier_id, wsh_new_deliveries.waybill,
1001 wsh_delivery_legs.delivery_leg_id
1002 INTO l_delivery_id, l_carrier_id, l_waybill, l_delivery_leg_id
1003 FROM wsh_trips, wsh_trip_stops, wsh_delivery_legs , wsh_new_deliveries
1004 WHERE wsh_delivery_legs.delivery_id = wsh_new_deliveries.delivery_id
1005 AND wsh_delivery_legs.pick_up_stop_id = wsh_trip_stops.stop_id
1006 AND wsh_trip_stops.trip_id = wsh_trips.trip_id
1007 AND wsh_trips.carrier_id = l_carrier_id
1008 AND wsh_new_deliveries.waybill = l_tracking_id
1009 AND wsh_delivery_legs.parent_delivery_leg_id is null;--Rel 12 MDC Changes
1010
1011
1012
1013 INSERT INTO wsh_delivery_leg_activities
1014 (activity_id, delivery_leg_id, activity_date, activity_type,
1015 creation_date, created_by, last_update_date, last_updated_by)
1016 VALUES
1017 (WSH_DELIVERY_LEG_ACTIVITIES_S.NEXTVAL, l_delivery_leg_id, sysdate, 'TRACKING',
1018 sysdate, l_carrier_id, sysdate, l_carrier_id);
1019
1020
1021
1022
1023 UPDATE fte_shipment_status_headers
1024 SET delivery_id = l_delivery_id,
1025 delivery_leg_id = l_delivery_leg_id,
1026 message_type = 'DELIVERY',
1027 creation_date = sysdate,
1028 created_by = FND_GLOBAL.USER_ID,
1029 last_update_date= sysdate,
1030 last_updated_by =FND_GLOBAL.USER_ID,
1031 last_update_login=FND_GLOBAL.USER_ID,
1032 activity_id = WSH_DELIVERY_LEG_ACTIVITIES_S.CURRVAL
1033 WHERE transaction_id = p_transaction_id;
1034
1035 BEGIN
1036 UPDATE fte_shipment_status_details
1037 SET delivery_leg_id = l_delivery_leg_id,
1038 creation_date = sysdate,
1039 created_by = FND_GLOBAL.USER_ID,
1040 last_update_date = sysdate,
1041 last_updated_by = FND_GLOBAL.USER_ID,
1042 last_update_login=FND_GLOBAL.USER_ID
1043 WHERE transaction_id = p_transaction_id;
1044 EXCEPTION
1045 WHEN OTHERS THEN
1046 NULL; --No records in FTE_DELIVERY_PROOF table
1047 END ;
1048
1049 BEGIN
1050 --Get the location inforamtion for the POD from the fte_message_address for the location id
1051 SELECT id INTO l_location_id
1052 FROM fte_message_location
1053 WHERE transaction_id=p_transaction_id
1054 and location_to_table = 'FTE_DELIVERY_PROOF';
1055 l_address:=get_location(l_location_id);
1056
1057
1058 -- Update the POD information
1059 UPDATE fte_delivery_proof
1060 SET creation_date = sysdate,
1061 created_by =FND_GLOBAL.USER_ID,
1062 last_update_date=sysdate,
1063 last_updated_by =FND_GLOBAL.USER_ID,
1064 last_update_login=FND_GLOBAL.USER_ID,
1065 location =l_address
1066 WHERE transaction_id= p_transaction_id;
1067
1068 EXCEPTION
1069 WHEN OTHERS THEN
1070 NULL; --No records in FTE_DELIVERY_PROOF table
1071 END ;
1072
1073
1074
1075
1076 -- populate child legs if any
1077 populate_child_delivery_legs
1078 (
1079 p_init_msg_list => FND_API.G_TRUE,
1080 p_delivery_leg_id => l_delivery_leg_id,
1081 p_transaction_id => p_transaction_id,
1082 p_carrier_id => l_carrier_id,
1083 x_return_status => l_return_statusp,
1084 x_msg_count => l_msg_count,
1085 x_msg_data => l_msg_data
1086 );
1087
1088
1089
1090 ELSIF (upper(l_tracking_id_type) = 'BOOKING NUMBER')
1091 THEN
1092 SELECT /*+ first_rows ordered */
1093 wsh_delivery_legs.delivery_id, wsh_trips.carrier_id,
1094 wsh_delivery_legs.booking_number, wsh_delivery_legs.delivery_leg_id
1095 INTO l_delivery_id, l_carrier_id, l_booking_number, l_delivery_leg_id
1096 FROM wsh_trips, wsh_trip_stops, wsh_delivery_legs , wsh_new_deliveries
1097 WHERE wsh_delivery_legs.delivery_id = wsh_new_deliveries.delivery_id
1098 AND wsh_delivery_legs.pick_up_stop_id = wsh_trip_stops.stop_id
1099 AND wsh_trip_stops.trip_id = wsh_trips.trip_id
1100 AND wsh_trips.carrier_id = l_carrier_id
1101 AND wsh_delivery_legs.booking_number = l_tracking_id
1102 AND wsh_delivery_legs.parent_delivery_leg_id is null;--Rel 12 MDC Changes
1103
1104
1105
1106 insert into wsh_delivery_leg_activities
1107 (activity_id, delivery_leg_id, activity_date, activity_type,
1108 creation_date, created_by, last_update_date, last_updated_by)
1109 values
1110 (WSH_DELIVERY_LEG_ACTIVITIES_S.nextval, l_delivery_leg_id, sysdate, 'TRACKING',
1111 sysdate, l_carrier_id, sysdate, l_carrier_id);
1112
1113
1114
1115 update fte_shipment_status_headers
1116 set delivery_id = l_delivery_id,
1117 delivery_leg_id = l_delivery_leg_id,
1118 message_type = 'DELIVERY',
1119 creation_date = sysdate,
1120 created_by =FND_GLOBAL.USER_ID,
1121 last_update_date=sysdate,
1122 last_updated_by = FND_GLOBAL.USER_ID,
1123 last_update_login=FND_GLOBAL.USER_ID,
1124 activity_id = WSH_DELIVERY_LEG_ACTIVITIES_S.currval
1125 where transaction_id = p_transaction_id;
1126
1127 BEGIN
1128 update fte_shipment_status_details
1129 set delivery_leg_id = l_delivery_leg_id,
1130 creation_date = sysdate,
1131 created_by =FND_GLOBAL.USER_ID,
1132 last_update_date= sysdate,
1133 last_updated_by = FND_GLOBAL.USER_ID,
1134 last_update_login=FND_GLOBAL.USER_ID
1135 where transaction_id = p_transaction_id;
1136 EXCEPTION
1137 WHEN OTHERS THEN
1138 NULL; --No records in FTE_DELIVERY_PROOF table
1139 END ;
1140
1141 BEGIN
1142 --Get the location inforamtion for the POD from the fte_message_address for the location id
1143 select id into l_location_id
1144 from fte_message_location
1145 where transaction_id=p_transaction_id
1146 and location_to_table = 'FTE_DELIVERY_PROOF';
1147
1148 l_address :=GET_LOCATION(l_location_id);
1149
1150
1151
1152 -- Update the POD information
1153 update fte_delivery_proof
1154 set creation_date=sysdate,
1155 created_by =FND_GLOBAL.USER_ID,
1156 last_update_date=sysdate,
1157 last_updated_by=FND_GLOBAL.USER_ID,
1158 last_update_login=FND_GLOBAL.USER_ID,
1159 location = l_address
1160 where transaction_id= p_transaction_id;
1161 EXCEPTION
1162 WHEN OTHERS THEN
1163 NULL; --No records in FTE_DELIVERY_PROOF table
1164 END ;
1165
1166
1167 -- populate child legs if any
1168 populate_child_delivery_legs
1169 (
1170 p_init_msg_list => FND_API.G_TRUE,
1171 p_delivery_leg_id => l_delivery_leg_id,
1172 p_transaction_id => p_transaction_id,
1173 p_carrier_id => l_carrier_id,
1174 x_return_status => l_return_statusp,
1175 x_msg_count => l_msg_count,
1176 x_msg_data => l_msg_data
1177 );
1178
1179
1180 elsif (upper(l_tracking_id_type) = 'BILL OF LADING')
1181 then
1182 select wsh_new_deliveries.delivery_id, wsh_delivery_legs.delivery_leg_id,
1183 wsh_trips.carrier_id
1184 into l_delivery_id, l_delivery_leg_id, l_carrier_id
1185 from wsh_new_deliveries, wsh_bols_rd_v, wsh_delivery_legs, wsh_trips, wsh_trip_stops
1186 where wsh_bols_rd_v.delivery_leg_id = wsh_delivery_legs.delivery_leg_id
1187 and wsh_delivery_legs.delivery_id = wsh_new_deliveries.delivery_id
1188 and wsh_delivery_legs.pick_up_stop_id = wsh_trip_stops.stop_id
1189 and wsh_trip_stops.trip_Id = wsh_trips.trip_id
1190 and wsh_trips.carrier_id = l_carrier_id
1191 and wsh_bols_rd_v.bill_of_lading_number = l_tracking_id
1192 and wsh_delivery_legs.parent_delivery_leg_id is null;--Rel 12 MDC Changes
1193
1194
1195 insert into wsh_delivery_leg_activities
1196 (activity_id, delivery_leg_id, activity_date, activity_type,
1197 creation_date, created_by, last_update_date, last_updated_by)
1198 values
1199 (WSH_DELIVERY_LEG_ACTIVITIES_S.nextval, l_delivery_leg_id, sysdate, 'TRACKING',
1200 sysdate, l_carrier_id, sysdate, l_carrier_id);
1201
1202
1203
1204 update fte_shipment_status_headers
1205 set delivery_id = l_delivery_id,
1206 delivery_leg_id = l_delivery_leg_id,
1207 message_type = 'DELIVERY',
1208 creation_date = sysdate,
1209 created_by = FND_GLOBAL.USER_ID,
1210 last_update_date= sysdate,
1211 last_updated_by = FND_GLOBAL.USER_ID,
1212 last_update_login=FND_GLOBAL.USER_ID,
1213 activity_id = WSH_DELIVERY_LEG_ACTIVITIES_S.currval
1214 where transaction_id = p_transaction_id;
1215
1216 BEGIN
1217 update fte_shipment_status_details
1218 set delivery_leg_id = l_delivery_leg_id,
1219 creation_date = sysdate,
1220 created_by = FND_GLOBAL.USER_ID,
1221 last_update_date= sysdate,
1222 last_updated_by = FND_GLOBAL.USER_ID,
1223 last_update_login=FND_GLOBAL.USER_ID
1224 where transaction_id = p_transaction_id;
1225 EXCEPTION
1226 WHEN OTHERS THEN
1227 NULL; --No records in FTE_DELIVERY_PROOF table
1228 END ;
1229
1230 --Get the location inforamtion for the POD from the fte_message_address for the location id
1231
1232 BEGIN
1233 select id into l_location_id
1234 from fte_message_location
1235 where transaction_id=p_transaction_id
1236 and location_to_table = 'FTE_DELIVERY_PROOF';
1237
1238 l_address :=GET_LOCATION(l_location_id);
1239
1240
1241 -- Update the POD information
1242
1243 update fte_delivery_proof
1244 set creation_date = sysdate,
1245 created_by = FND_GLOBAL.USER_ID,
1246 last_update_date=sysdate,
1247 last_updated_by=FND_GLOBAL.USER_ID,
1248 last_update_login=FND_GLOBAL.USER_ID,
1249 location = l_address
1250 where transaction_id= p_transaction_id;
1251 EXCEPTION
1252 WHEN OTHERS THEN
1253 NULL; --No records in FTE_DELIVERY_PROOF table
1254 END ;
1255
1256 -- populate child legs if any
1257 populate_child_delivery_legs
1258 (
1259 p_init_msg_list => FND_API.G_TRUE,
1260 p_delivery_leg_id => l_delivery_leg_id,
1261 p_transaction_id => p_transaction_id,
1262 p_carrier_id => l_carrier_id,
1263 x_return_status => l_return_statusp,
1264 x_msg_count => l_msg_count,
1265 x_msg_data => l_msg_data
1266 );
1267
1268
1269 -- Since Consol delivery does not have Detials attached to it, it is not possible to
1270 -- send 'LICENSE PLATE NUMBER' information for Content delivery. Hence populate_child_delivery_legs
1271 -- method will not be called.
1272
1273 elsif (upper(l_tracking_id_type) = 'LICENSE PLATE NUMBER')
1274 then -- license plate number is only used by container
1275 select wsh_new_deliveries.delivery_id, wsh_delivery_legs.delivery_leg_id,
1276 wsh_trips.carrier_id, wsh_delivery_details.container_name,
1277 wsh_delivery_details.delivery_detail_id
1278 into l_delivery_id, l_delivery_leg_id, l_carrier_id, l_container_name,
1279 l_delivery_detail_id
1280 from wsh_new_deliveries, wsh_delivery_legs, wsh_trips, wsh_trip_stops,
1281 wsh_delivery_details, wsh_delivery_assignments
1282 where wsh_delivery_assignments.DELIVERY_ID = wsh_new_deliveries.DELIVERY_ID
1283 and wsh_delivery_assignments.DELIVERY_DETAIL_ID =
1284 wsh_delivery_details.DELIVERY_DETAIL_ID
1285 and wsh_delivery_legs.delivery_id = wsh_new_deliveries.delivery_id
1286 and wsh_delivery_legs.pick_up_stop_id = wsh_trip_stops.stop_id
1287 and wsh_trip_stops.trip_Id = wsh_trips.trip_id
1288 and wsh_trips.carrier_id = l_carrier_id
1289 and wsh_delivery_details.container_name = l_tracking_id
1290 and wsh_delivery_legs.parent_delivery_leg_id is null;--Rel 12 MDC Changes
1291
1292
1293
1294 insert into wsh_delivery_leg_activities
1295 (activity_id, delivery_leg_id, activity_date, activity_type,
1296 creation_date, created_by, last_update_date, last_updated_by)
1297 values
1298 (WSH_DELIVERY_LEG_ACTIVITIES_S.nextval, l_delivery_leg_id, sysdate, 'TRACKING',
1299 sysdate, l_carrier_id, sysdate, l_carrier_id);
1300
1301
1302
1303 update fte_shipment_status_headers
1304 set delivery_id = l_delivery_id,
1305 delivery_leg_id = l_delivery_leg_id,
1306 delivery_detail_id = l_delivery_detail_id,
1307 license_plate_number = l_container_name,
1308 message_type = 'CONTAINER',
1309 creation_date = sysdate,
1310 activity_id = WSH_DELIVERY_LEG_ACTIVITIES_S.currval
1311 where transaction_id = p_transaction_id;
1312
1313 BEGIN
1314 update fte_shipment_status_details
1315 set delivery_leg_id = l_delivery_leg_id,
1316 creation_date = sysdate
1317 where transaction_id = p_transaction_id;
1318 EXCEPTION
1319 WHEN OTHERS THEN
1320 NULL; --No records in FTE_DELIVERY_PROOF table
1321 END ;
1322
1323 -- populate child legs if any
1324 populate_child_delivery_legs
1325 (
1326 p_init_msg_list => FND_API.G_TRUE,
1327 p_delivery_leg_id => l_delivery_leg_id,
1328 p_transaction_id => p_transaction_id,
1329 p_carrier_id => l_carrier_id,
1330 x_return_status => l_return_statusp,
1331 x_msg_count => l_msg_count,
1332 x_msg_data => l_msg_data
1333 );
1334
1335 elsif (upper(l_tracking_id_type) = 'SEAL IDENTIFIER')
1336 then -- seal code is only used by container
1337
1338 select /*+ORDERED INDEX(wdl WSH_DELIVERY_LEGS_N2)*/
1339 wnd.delivery_id,
1340 wdl.delivery_leg_id,
1341 wt.carrier_id,
1342 wdd.seal_code,
1343 wdd.delivery_detail_id
1344 into l_delivery_id, l_delivery_leg_id, l_carrier_id, l_seal_code,
1345 l_delivery_detail_id
1346 from
1347 wsh_trips wt,
1348 wsh_trip_stops wts,
1349 wsh_delivery_legs wdl,
1350 wsh_new_deliveries wnd,
1351 wsh_delivery_assignments wda,
1352 wsh_delivery_details wdd
1353 where wda.DELIVERY_ID = wnd.DELIVERY_ID
1354 and wda.DELIVERY_DETAIL_ID = wdd.DELIVERY_DETAIL_ID
1355 and wdl.delivery_id = wnd.delivery_id
1356 and wdl.pick_up_stop_id = wts.stop_id
1357 and wts.trip_Id = wt.trip_id
1358 and wt.carrier_id = l_carrier_id
1359 and wdd.seal_code = l_tracking_id
1360 and wdl.parent_delivery_leg_id is null;--Rel 12 MDC Changes
1361
1362
1363
1364
1365 insert into wsh_delivery_leg_activities
1366 (activity_id, delivery_leg_id, activity_date, activity_type,
1367 creation_date, created_by, last_update_date, last_updated_by)
1368 values
1369 (WSH_DELIVERY_LEG_ACTIVITIES_S.nextval, l_delivery_leg_id, sysdate, 'TRACKING',
1370 sysdate, l_carrier_id, sysdate, l_carrier_id);
1371
1372 begin
1373 select container_name into l_container_name from wsh_delivery_details
1374 where delivery_detail_id = l_delivery_detail_id;
1375
1376
1377 exception
1378 WHEN NO_DATA_FOUND THEN
1379 raise no_license_plate;
1380
1381 end;
1382
1383
1384 update fte_shipment_status_headers
1385 set delivery_id = l_delivery_id,
1386 delivery_leg_id = l_delivery_leg_id,
1387 delivery_detail_id = l_delivery_detail_id,
1388 container_seal = l_seal_code,
1389 message_type = 'CONTAINER',
1390 creation_date = sysdate,
1391 activity_id = WSH_DELIVERY_LEG_ACTIVITIES_S.currval,
1392 license_plate_number = l_container_name
1393 where transaction_id = p_transaction_id;
1394
1395 BEGIN
1396 update fte_shipment_status_details
1397 set delivery_leg_id = l_delivery_leg_id,
1398 creation_date = sysdate
1399 where transaction_id = p_transaction_id;
1400 EXCEPTION
1401 WHEN OTHERS THEN
1402 NULL; --No records in FTE_DELIVERY_PROOF table
1403 END ;
1404
1405 -- populate child legs if any
1406 populate_child_delivery_legs
1407 (
1408 p_init_msg_list => FND_API.G_TRUE,
1409 p_delivery_leg_id => l_delivery_leg_id,
1410 p_transaction_id => p_transaction_id,
1411 p_carrier_id => l_carrier_id,
1412 x_return_status => l_return_statusp,
1413 x_msg_count => l_msg_count,
1414 x_msg_data => l_msg_data
1415 );
1416
1417
1418
1419 elsif (upper(l_tracking_id_type) = 'CARRIER REFERENCE NUMBER')
1420 then -- tracking number is only used for container.
1421 -- It is only in wsh_delivery_details level,
1422 -- not in wsh_new_deliveries level
1423
1424
1425 select wsh_new_deliveries.delivery_id, wsh_delivery_legs.delivery_leg_id,
1426 wsh_trips.carrier_id, wsh_delivery_details.tracking_number,
1427 wsh_delivery_details.delivery_detail_id
1428 into l_delivery_id, l_delivery_leg_id, l_carrier_id, l_tracking_number,
1429 l_delivery_detail_id
1430 from wsh_new_deliveries, wsh_delivery_legs, wsh_trips,
1431 wsh_trip_stops, wsh_delivery_details, wsh_delivery_assignments
1432 where wsh_delivery_assignments.DELIVERY_ID = wsh_new_deliveries.DELIVERY_ID
1433 and wsh_delivery_assignments.DELIVERY_DETAIL_ID =
1434 wsh_delivery_details.DELIVERY_DETAIL_ID
1435 and wsh_delivery_legs.delivery_id = wsh_new_deliveries.delivery_id
1436 and wsh_delivery_legs.pick_up_stop_id = wsh_trip_stops.stop_id
1437 and wsh_trip_stops.trip_Id = wsh_trips.trip_id
1438 and wsh_trips.carrier_id = l_carrier_id
1439 and wsh_delivery_details.tracking_number = l_tracking_id
1440 and wsh_delivery_legs.parent_delivery_leg_id is null;--Rel 12 MDC Changes
1441
1442
1443
1444 insert into wsh_delivery_leg_activities
1445 (activity_id, delivery_leg_id, activity_date, activity_type,
1446 creation_date, created_by, last_update_date, last_updated_by)
1447 values
1448 (WSH_DELIVERY_LEG_ACTIVITIES_S.nextval, l_delivery_leg_id, sysdate, 'TRACKING',
1449 sysdate, l_carrier_id, sysdate, l_carrier_id);
1450
1451 begin
1452 select container_name into l_container_name from wsh_delivery_details
1453 where delivery_detail_id = l_delivery_detail_id;
1454 exception
1455 WHEN NO_DATA_FOUND THEN
1456 raise no_license_plate;
1457 end;
1458
1459
1460 update fte_shipment_status_headers
1461 set delivery_id = l_delivery_id,
1462 delivery_leg_id = l_delivery_leg_id,
1463 delivery_detail_id = l_delivery_detail_id,
1464 message_type = 'CONTAINER',
1465 creation_date = sysdate,
1466 activity_id = WSH_DELIVERY_LEG_ACTIVITIES_S.currval,
1467 license_plate_number = l_container_name
1468 where transaction_id = p_transaction_id;
1469
1470 BEGIN
1471 update fte_shipment_status_details
1472 set delivery_leg_id = l_delivery_leg_id,
1473 creation_date = sysdate
1474 where transaction_id = p_transaction_id;
1475 EXCEPTION
1476 WHEN OTHERS THEN
1477 NULL; --No records in FTE_DELIVERY_PROOF table
1478 END ;
1479
1480 --Get the location inforamtion for the POD from the fte_message_address for the location id
1481
1482 BEGIN
1483 select id into l_location_id
1484 from fte_message_location
1485 where transaction_id=p_transaction_id
1486 and location_to_table = 'FTE_DELIVERY_PROOF';
1487
1488 l_address :=GET_LOCATION(l_location_id);
1489
1490
1491 -- Update the POD information
1492
1493 update fte_delivery_proof
1494 set creation_date = sysdate,
1495 created_by = FND_GLOBAL.USER_ID,
1496 last_update_date=sysdate,
1497 last_updated_by=FND_GLOBAL.USER_ID,
1498 last_update_login=FND_GLOBAL.USER_ID,
1499 location = l_address
1500 where transaction_id= p_transaction_id;
1501 EXCEPTION
1502 WHEN OTHERS THEN
1503 NULL; --No records in FTE_DELIVERY_PROOF table
1504 END ;
1505
1506 -- populate child legs if any
1507 populate_child_delivery_legs
1508 (
1509 p_init_msg_list => FND_API.G_TRUE,
1510 p_delivery_leg_id => l_delivery_leg_id,
1511 p_transaction_id => p_transaction_id,
1512 p_carrier_id => l_carrier_id,
1513 x_return_status => l_return_statusp,
1514 x_msg_count => l_msg_count,
1515 x_msg_data => l_msg_data
1516 );
1517
1518
1519 else
1520
1521 raise invalid_tracking_id_type;
1522
1523 end if;
1524
1525
1526
1527 update wsh_trip_stops
1528 set actual_departure_date = l_departure_date,
1529 carrier_est_departure_date = l_estimated_departure_date,
1530 loading_start_datetime = l_begin_loading_date,
1531 loading_end_datetime = l_end_loading_date
1532 where wsh_trip_stops.stop_id =
1533 (select pick_up_stop_id
1534 from wsh_delivery_legs
1535 where delivery_leg_id = l_delivery_leg_id);
1536
1537 update wsh_trip_stops
1538 set actual_arrival_date = l_arrival_date,
1539 carrier_est_arrival_date = l_estimated_arrival_date,
1540 unloading_start_datetime = l_begin_unloading_date,
1541 unloading_end_datetime = l_end_unloading_date
1542 where wsh_trip_stops.stop_id =
1543 (select drop_off_stop_id
1544 from wsh_delivery_legs
1545 where delivery_leg_id = l_delivery_leg_id);
1546
1547 -- Call the procedure get_delivery_details to delete all the
1548 -- details and POD information for a delivery leg and retain only the
1549 -- last detail and POD information
1550
1551
1552 FTE_TRACKING_WRAPPER.get_delivery_details (p_transaction_id, l_carrier_id,
1553 l_exception_message,
1554 l_return_status, l_error_token_text);
1555
1556
1557
1558
1559
1560 begin
1561 select received_date,1 into l_received_date,l_flag
1562 from
1563 fte_delivery_proof where transaction_id= p_transaction_id;
1564 EXCEPTION
1565 WHEN OTHERS THEN
1566 NULL; --No records in FTE_DELIVERY_PROOF table
1567 END ;
1568
1569 if l_flag= 1 then
1570 FTE_TRACKING_WRAPPER.call_last_delivery_leg(l_delivery_leg_id,l_received_date);
1571 else
1572 --Added by shravisa for Release 12
1573 --Raise Workflow Tracking Event when ever Tracking information comes for any
1574 --Functional Workflow
1575
1576 OPEN get_org_delivery_info(l_delivery_leg_id);
1577 FETCH get_org_delivery_info into l_delivery_id_tk,l_organization_id;
1578 CLOSE get_org_delivery_info;
1579
1580
1581
1582 wf_event.AddParameterToList(
1583 p_name=>'ORGANIZATION_ID',
1584 p_value => l_organization_id,
1585 p_parameterlist=> l_parameter_list);
1586
1587
1588 --Do not handle the Return status from this method
1589 WSH_WF_STD.raise_event(
1590 p_entity_type => 'DELIVERY',
1591 p_entity_id => TO_CHAR(l_delivery_id_tk),
1592 p_event => 'oracle.apps.fte.delivery.trk.matchtrackingadvice',
1593 p_parameters => l_parameter_list,
1594 p_organization_id => l_organization_id,
1595 x_return_status => l_return_statuswf);
1596 END IF;
1597
1598
1599 EXCEPTION
1600 WHEN INVALID_CARRIER THEN
1601 select wsh_interface_errors_s.nextval into l_error_id
1602 from dual;
1603
1604 FTE_TRACKING_WRAPPER.insert_error_status
1605 (l_error_id, 'FTE_SHIPMEMT_STATUS_HEADERS',
1606 p_transaction_id, 1, 'ERROR', 'INVALID CARRIER',
1607 0);
1608 x_error_token_text := 1;
1609 x_exception_message := 'Invalid carrier name. ';
1610 x_return_status := 1;
1611 WHEN NO_LICENSE_PLATE THEN
1612 select wsh_interface_errors_s.nextval into l_error_id
1613 from dual;
1614
1615 FTE_TRACKING_WRAPPER.insert_error_status
1616 (l_error_id, 'FTE_SHIPMEMT_STATUS_HEADERS',
1617 p_transaction_id, 2, 'ERROR', 'NO LICENSE PLATE NUMBER',
1618 0);
1619 x_error_token_text := 2;
1620 x_exception_message := 'No license plate number. ';
1621 x_return_status := 2;
1622 WHEN NO_DATA_FOUND THEN
1623 select wsh_interface_errors_s.nextval into l_error_id
1624 from dual;
1625
1626 FTE_TRACKING_WRAPPER.insert_error_status
1627 (l_error_id, 'FTE_SHIPMEMT_STATUS_HEADERS',
1628 p_transaction_id, 3, 'ERROR', 'NO DELIVERY FOUND',
1629 l_carrier_id);
1630 x_error_token_text := 3;
1631 x_exception_message := 'Unable to determine delivery. ';
1632 x_return_status := 3;
1633 WHEN INVALID_TRACKING_ID_TYPE THEN
1634 select wsh_interface_errors_s.nextval into l_error_id
1635 from dual;
1636
1637 FTE_TRACKING_WRAPPER.insert_error_status
1638 (l_error_id, 'FTE_SHIPMEMT_STATUS_HEADERS',
1639 p_transaction_id, 4, 'ERROR', 'INVALID TRACKING ID TYPE',
1640 l_carrier_id);
1641 x_return_status := 4 ;
1642 x_exception_message := 'Invalid tracking id type';
1643 x_error_token_text := 4;
1644 WHEN OTHERS THEN
1645 select wsh_interface_errors_s.nextval into l_error_id
1646 from dual;
1647
1648 FTE_TRACKING_WRAPPER.insert_error_status
1649 (l_error_id, 'FTE_SHIPMEMT_STATUS_HEADERS',
1650 p_transaction_id, 5, 'ERROR', 'OTHER EXCEPTION',
1651 l_carrier_id);
1652
1653 l_sql_error_code := to_char(SQLCODE);
1654 l_sql_error_msg := substr(SQLERRM,1,2000);
1655
1656 IF (l_exception_message IS NULL) THEN
1657 l_exception_message :=
1658 (l_sql_error_code||':'||l_sql_error_msg||' : '
1659 ||' : '||l_procedure_name);
1660 END IF;
1661 x_return_status := 5 ;
1662 x_exception_message := l_exception_message;
1663 x_error_token_text := 5;
1664
1665
1666 END get_delivery_or_container;
1667
1668
1669
1670 PROCEDURE get_delivery_details(
1671 p_transaction_id IN NUMBER,
1672 p_carrier_id IN NUMBER,
1673 x_exception_message OUT NOCOPY VARCHAR2,
1674 x_return_status OUT NOCOPY NUMBER,
1675 x_error_token_text OUT NOCOPY NUMBER) IS
1676
1677 l_creation_date DATE;
1678 l_delivery_leg_id NUMBER;
1679 l_rows NUMBER;
1680
1681 l_tracking_id VARCHAR2(30);
1682 l_message_type VARCHAR2(30);
1683 l_previous_transaction_id NUMBER;
1684
1685 l_exception_message VARCHAR2(2000);
1686 l_return_status NUMBER;
1687 l_error_token_text VARCHAR2(240);
1688
1689 l_sql_error_code VARCHAR2(2000);
1690 l_sql_error_msg VARCHAR2(2000);
1691 l_cursor_name VARCHAR2(2000);
1692 l_procedure_name VARCHAR2(240) := 'FTE_TRACKING_WRAPPER.get_delivery_details';
1693
1694 INVALID_TRANSACTION_ID EXCEPTION;
1695
1696 l_error_id NUMBER;
1697
1698 CURSOR c1 is
1699
1700 select delivery_leg_id, creation_date
1701 from fte_shipment_status_headers
1702 where transaction_id = p_transaction_id;
1703
1704 Cursor c_get_prev (c_transaction_id number, c_delivery_leg_id number) is
1705 SELECT
1706 fh.transaction_id
1707 FROM
1708 fte_shipment_status_headers fh,
1709 fte_shipment_status_details fs
1710 WHERE
1711 fh.transaction_id = fs.transaction_id and
1712 fh.transaction_id <> c_transaction_id and
1713 fh.delivery_leg_id = c_delivery_leg_id
1714 UNION
1715 SELECT
1716 fp.transaction_id
1717 FROM
1718 fte_shipment_status_headers fh,
1719 fte_delivery_proof fp
1720 WHERE
1721 fh.transaction_id = fp.transaction_id and
1722 fh.transaction_id <> c_transaction_id and
1723 fh.delivery_leg_id = c_delivery_leg_id;
1724
1725 BEGIN
1726
1727 begin
1728 select tracking_id, message_type
1729 into l_tracking_id, l_message_type
1730 from fte_shipment_status_headers
1731 where transaction_id = p_transaction_id;
1732
1733 exception
1734 when others then
1735 raise INVALID_TRANSACTION_ID;
1736 end;
1737
1738
1739
1740 OPEN c1;
1741 LOOP
1742 FETCH c1 into
1743 l_delivery_leg_id, l_creation_date;
1744
1745
1746
1747 if (upper(l_message_type) = 'DELIVERY')
1748 then
1749
1750
1751 open c_get_prev(p_transaction_id, l_delivery_leg_id);
1752 FETCH c_get_prev into l_previous_transaction_id;
1753 CLOSE c_get_prev;
1754
1755
1756
1757 DECLARE
1758 l_shipment_detail_id NUMBER;
1759
1760 CURSOR c2 is
1761
1762 select shipment_status_detail_id
1763 from fte_shipment_status_details
1764 where delivery_leg_id = l_delivery_leg_id
1765 and transaction_id <> p_transaction_id;
1766
1767 BEGIN
1768 OPEN c2;
1769 LOOP
1770 FETCH c2 into
1771 l_shipment_detail_id;
1772
1773
1774 delete from fte_shipment_status_exceptions
1775 where detail_id = l_shipment_detail_id;
1776
1777
1778 EXIT when c2%NOTFOUND;
1779 END LOOP;
1780 CLOSE c2;
1781
1782 END;
1783
1784 select count (*)
1785 into l_rows
1786 from fte_shipment_status_details
1787 where transaction_id = l_previous_transaction_id;
1788
1789
1790 delete
1791 from fte_shipment_status_details
1792 where transaction_id = l_previous_transaction_id;
1793
1794
1795 delete
1796 from fte_delivery_proof where
1797 transaction_id =l_previous_transaction_id;
1798
1799
1800 elsif (upper(l_message_type) = 'CONTAINER')
1801 then
1802 select tracking_id, message_type
1803 into l_tracking_id, l_message_type
1804 from fte_shipment_status_headers
1805 where transaction_id = p_transaction_id;
1806
1807 OPEN c_get_prev(p_transaction_id, l_delivery_leg_id);
1808 FETCH c_get_prev into l_previous_transaction_id;
1809 CLOSE c_get_prev;
1810
1811 select count (*)
1812 into l_rows
1813 from fte_shipment_status_details
1814 where transaction_id = l_previous_transaction_id;
1815
1816
1817 DECLARE
1818 l_shipment_detail_id NUMBER;
1819
1820 CURSOR c2 is
1821
1822 select shipment_status_detail_id
1823 from fte_shipment_status_details
1824 where delivery_leg_id = l_delivery_leg_id
1825 and transaction_id = l_previous_transaction_id;
1826
1827 BEGIN
1828 OPEN c2;
1829 LOOP
1830 FETCH c2 into
1831 l_shipment_detail_id;
1832
1833
1834 delete from fte_shipment_status_exceptions
1835 where detail_id = l_shipment_detail_id;
1836
1837
1838 EXIT when c2%NOTFOUND;
1839 END LOOP;
1840 CLOSE c2;
1841
1842 END;
1843
1844
1845 delete
1846 from fte_shipment_status_details
1847 where transaction_id = l_previous_transaction_id;
1848
1849
1850
1851 delete
1852 from fte_delivery_proof where
1853 transaction_id =l_previous_transaction_id;
1854
1855 end if;
1856
1857
1858 EXIT when c1%NOTFOUND;
1859 END LOOP;
1860 CLOSE c1;
1861
1862
1863 EXCEPTION
1864 WHEN INVALID_TRANSACTION_ID THEN
1865
1866 l_sql_error_code := 1;
1867 l_sql_error_msg := 'Invalid transaction id';
1868 l_exception_message := 'Invalid transaction id';
1869
1870 WHEN OTHERS THEN
1871
1872 select wsh_interface_errors_s.nextval into l_error_id
1873 from dual;
1874
1875 FTE_TRACKING_WRAPPER.insert_error_status
1876 (l_error_id, 'FTE_SHIPMEMT_STATUS_DETAILS',
1877 p_transaction_id, 2, 'ERROR', 'NO DELIVERY DETAILS FOUND',
1878 p_carrier_id);
1879
1880 l_sql_error_code := to_char(SQLCODE);
1881 l_sql_error_msg := substr(SQLERRM,1,2000);
1882
1883 IF (l_exception_message IS NULL) THEN
1884 l_exception_message :=
1885 (l_sql_error_code||':'||l_sql_error_msg||' : '
1886 ||l_cursor_name||' : '||l_procedure_name);
1887 END IF;
1888
1889 x_return_status := 2 ;
1890 x_exception_message := l_exception_message;
1891 x_error_token_text := 2;
1892
1893
1894 END get_delivery_details;
1895
1896
1897 PROCEDURE insert_error_status
1898 (
1899 p_interface_error_id IN NUMBER,
1900 p_interface_table_name IN VARCHAR2,
1901 p_interface_id IN NUMBER,
1902 p_message_code IN NUMBER,
1903 p_message_name IN VARCHAR2,
1904 p_error_message IN VARCHAR2,
1905 p_carrier_id IN NUMBER
1906 )
1907 IS
1908 BEGIN
1909
1910 -- message_code 0 = completed with success, 1 = completed with error
1911 INSERT INTO WSH_INTERFACE_ERRORS
1912 (INTERFACE_ERROR_ID, INTERFACE_ERROR_GROUP_ID, INTERFACE_TABLE_NAME,
1913 INTERFACE_ID, MESSAGE_CODE, MESSAGE_NAME, ERROR_MESSAGE,
1914 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
1915 VALUES (p_interface_error_id, 1, p_interface_table_name,
1916 p_interface_id, p_message_code, p_message_name, p_error_message,
1917 sysdate, p_carrier_id, sysdate, p_carrier_id);
1918 commit;
1919
1920 END insert_error_status;
1921
1922 PROCEDURE call_last_delivery_leg(p_delivery_leg_id IN NUMBER,
1923 p_received_date IN DATE)
1924 IS
1925
1926 l_return_status VARCHAR2(10);
1927 l_msg_count NUMBER;
1928 l_msg_data VARCHAR2(32767);
1929
1930
1931 BEGIN
1932
1933 CALL_LAST_DELIVERY_LEG(p_api_version_number => 1,
1934 p_init_msg_list => FND_API.G_FALSE ,
1935 x_return_status => l_return_status,
1936 x_msg_count => l_msg_count,
1937 x_msg_data => l_msg_data,
1938 p_delivery_leg_id => p_delivery_leg_id,
1939 p_received_date => p_received_date);
1940
1941
1942 END call_last_delivery_leg;
1943
1944
1945 PROCEDURE CALL_LAST_DELIVERY_LEG(
1946 p_api_version_number IN NUMBER,
1947 p_init_msg_list IN VARCHAR2,
1948 x_return_status OUT NOCOPY VARCHAR2,
1949 x_msg_count OUT NOCOPY NUMBER,
1950 x_msg_data OUT NOCOPY VARCHAR2,
1951 p_delivery_leg_id IN NUMBER,
1952 p_received_date IN DATE)
1953 IS
1954 l_leg_Tab WSH_DELIVERY_LEGS_GRP.dlvy_leg_tab_type;
1955 l_in_rec WSH_DELIVERY_LEGS_GRP.action_parameters_rectype;
1956 l_out_rec WSH_DELIVERY_LEGS_GRP.action_out_rec_type;
1957
1958 -- Return values
1959 l_return_status VARCHAR2(10);
1960 l_msg_count NUMBER;
1961 l_msg_data VARCHAR2(32767);
1962 l_number_of_warnings NUMBER;
1963 l_number_of_errors NUMBER;
1964 l_out_action_rec FTE_ACTION_OUT_REC;
1965
1966 --Return values for AR Call
1967 l_ar_return_status VARCHAR2(10);
1968 l_ar_msg_count NUMBER;
1969 l_ar_msg_data VARCHAR2(32767);
1970
1971
1972 -- local variables
1973
1974 l_last_delivery_leg NUMBER;
1975 l_last_stop_id NUMBER;
1976 l_last_stop_status VARCHAR(2);
1977 l_dlvy_ud_location_id NUMBER;
1978 l_last_stop_location_id NUMBER;
1979 l_organization_id NUMBER;
1980 l_delivery_id NUMBER;
1981
1982 --Added for Funtional Tracking workflow
1983 l_organization_id_tk NUMBER;
1984 l_parameter_list_tk wf_parameter_list_t;
1985 l_delivery_id_tk NUMBER;
1986 l_return_statuswf VARCHAR2(1);
1987
1988 l_stop_id_tab FTE_ID_TAB_TYPE;
1989 l_stop_action_params FTE_STOP_ACTION_PARAM_REC;
1990
1991
1992 x_stop_out_rec WSH_TRIP_STOPS_GRP.stopActionOutRecType;
1993
1994 p_action_prms WSH_TRIP_STOPS_GRP.action_parameters_rectype;
1995 p_entity_id_tab WSH_UTIL_CORE.id_tab_type;
1996
1997 --For Workflow Impact on ITM
1998 l_parameter_list wf_parameter_list_t;
1999
2000 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2001 --
2002 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'call_last_delivery_leg';
2003
2004
2005 -- [HBHAGAVA: 10+ Modfied for bug 3952734
2006 --Modified for rel 12 Workflow Impact on ITM
2007 cursor get_last_delivery_leg(l_delivery_leg_id NUMBER) IS
2008 SELECT
2009 DELIVERY_LEG_ID, DROP_OFF_STOP_ID,
2010 WTL.STATUS_CODE,WND.ULTIMATE_DROPOFF_LOCATION_ID,
2011 WTL.STOP_LOCATION_ID, WND.ORGANIZATION_ID, WND.DELIVERY_ID
2012 FROM
2013 WSH_DELIVERY_LEGS WDL ,
2014 WSH_NEW_DELIVERIES WND,
2015 WSH_TRIP_STOPS WTL
2016 WHERE
2017 WND.DELIVERY_ID= WDL.DELIVERY_ID AND
2018 WDL.DROP_OFF_STOP_ID=WTL.STOP_ID AND
2019 WDL.delivery_leg_id = l_delivery_leg_id;
2020
2021 cursor get_org_delivery_info ( c_delivery_leg_id NUMBER) IS
2022 SELECT wnd.delivery_id, wnd.organization_id FROM
2023 wsh_delivery_legs wdl, wsh_new_deliveries wnd
2024 WHERE wdl.delivery_id = wnd.delivery_id AND
2025 wdl.delivery_leg_id = c_delivery_leg_id;
2026
2027 BEGIN
2028
2029 SAVEPOINT CALL_LAST_DELIVERY_LEG_PUB;
2030
2031
2032 IF l_debug_on THEN
2033 wsh_debug_sv.push(l_module_name);
2034 END IF;
2035
2036
2037 -- Initialize message list if p_init_msg_list is set to TRUE.
2038 --
2039 --
2040 IF FND_API.to_Boolean( p_init_msg_list )
2041 THEN
2042 FND_MSG_PUB.initialize;
2043 END IF;
2044
2045
2046 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2047 x_msg_count := 0;
2048 x_msg_data := 0;
2049 l_number_of_warnings := 0;
2050 l_number_of_errors := 0;
2051
2052
2053 --Raise Workflow Tracking Event when ever POD information comes in irrespective of the delivery leg position
2054 --Functional Workflow
2055
2056 OPEN get_org_delivery_info(p_delivery_leg_id);
2057 FETCH get_org_delivery_info into l_delivery_id_tk,l_organization_id_tk;
2058 CLOSE get_org_delivery_info;
2059
2060
2061
2062 wf_event.AddParameterToList(
2063 p_name=>'ORGANIZATION_ID',
2064 p_value => l_organization_id_tk,
2065 p_parameterlist=> l_parameter_list_tk);
2066
2067
2068 --Do not handle the Return status from this method
2069 WSH_WF_STD.raise_event(
2070 p_entity_type => 'DELIVERY',
2071 p_entity_id => TO_CHAR(l_delivery_id_tk),
2072 p_event => 'oracle.apps.fte.delivery.trk.matchtrackingadvice',
2073 p_parameters => l_parameter_list_tk,
2074 p_organization_id => l_organization_id_tk,
2075 x_return_status => l_return_statuswf);
2076
2077
2078 OPEN get_last_delivery_leg(p_delivery_leg_id);
2079 FETCH get_last_delivery_leg into
2080 l_last_delivery_leg,l_last_stop_id,l_last_stop_status,
2081 l_dlvy_ud_location_id,
2082 l_last_stop_location_id,l_organization_id,l_delivery_id;
2083 CLOSE get_last_delivery_leg;
2084
2085 IF l_debug_on THEN
2086
2087 WSH_DEBUG_SV.logmsg(l_module_name,' Stop Id ' || l_last_stop_id);
2088 WSH_DEBUG_SV.logmsg(l_module_name,' Stop status ' || l_last_stop_status);
2089 WSH_DEBUG_SV.logmsg(l_module_name,' Last dleg id ' || l_last_delivery_leg);
2090 WSH_DEBUG_SV.logmsg(l_module_name,' Dlvy UD LocatioId ' || l_dlvy_ud_location_id);
2091 WSH_DEBUG_SV.logmsg(l_module_name,' Stop Location Id ' || l_last_stop_location_id);
2092 END IF;
2093
2094 IF l_last_delivery_leg IS NOT NULL THEN
2095
2096 --[HBHAGAVA 10+]
2097 -- If deliveries ultimate dropoff location is not same as
2098 -- stop's location then the stop is not the last delivery leg
2099 -- so no need to update pod flag
2100 IF (l_last_stop_location_id = l_dlvy_ud_location_id) THEN
2101
2102 l_in_rec.action_code := 'UPDATE';
2103 l_in_rec.caller := 'FTE';
2104 l_leg_Tab(1).delivery_leg_id := p_delivery_leg_id;
2105 l_leg_Tab(1).pod_flag := 'Y';
2106 l_leg_Tab(1).pod_date := p_received_date;
2107
2108
2109 WSH_INTERFACE_GRP.Update_Delivery_Leg(
2110 p_api_version_number => 1.0,
2111 p_init_msg_list => FND_API.G_TRUE,
2112 p_commit => NULL,
2113 p_delivery_leg_tab => l_leg_Tab,
2114 p_in_rec => l_in_rec,
2115 x_out_rec => l_out_rec,
2116 x_return_status => l_return_status,
2117 x_msg_count => l_msg_count,
2118 x_msg_data => l_msg_data);
2119
2120 wsh_util_core.api_post_call(
2121 p_return_status =>l_return_status,
2122 x_num_warnings =>l_number_of_warnings,
2123 x_num_errors =>l_number_of_errors,
2124 p_msg_data =>l_msg_data);
2125
2126
2127
2128 IF l_number_of_errors > 0
2129 THEN
2130 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2131 ELSIF l_number_of_warnings > 0
2132 THEN
2133 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2134 ELSE
2135 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2136 END IF;
2137
2138 --Release 12 WF Impact on POD
2139 --Raise POD received event for Tracking workflow
2140
2141 wf_event.AddParameterToList(
2142 p_name=>'ORGANIZATION_ID',
2143 p_value => l_organization_id,
2144 p_parameterlist=> l_parameter_list);
2145
2146 --Do not handle the Return status from this method
2147 WSH_WF_STD.raise_event(
2148 p_entity_type => 'DELIVERY',
2149 p_entity_id => l_delivery_id,
2150 p_event => 'oracle.apps.fte.delivery.pod.podreceived',
2151 p_parameters => l_parameter_list,
2152 p_organization_id => l_organization_id,
2153 x_return_status => x_return_status);
2154
2155 --call To AR events for Revenue Recognition of a System
2156 --Do not handle the status
2157 ar_deferral_reasons_grp.record_proof_of_delivery
2158 (
2159 p_api_version => 1.0,
2160 p_init_msg_list => FND_API.G_FALSE,
2161 p_commit => FND_API.G_FALSE,
2162 p_delivery_id =>l_delivery_id,
2163 p_pod_date => p_received_date,
2164 x_return_status => l_ar_return_status,
2165 x_msg_count => l_ar_msg_count,
2166 x_msg_data => l_ar_msg_data
2167 );
2168
2169 END IF;
2170
2171
2172 IF (l_last_stop_status = 'CL') THEN
2173 IF l_debug_on THEN
2174
2175 WSH_DEBUG_SV.logmsg(l_module_name,' Stop is closed nothing to update ' || l_last_stop_id);
2176 WSH_DEBUG_SV.pop(l_module_name);
2177 END IF;
2178
2179 RETURN;
2180 ELSE
2181
2182
2183 IF l_debug_on THEN
2184
2185 WSH_DEBUG_SV.logmsg(l_module_name,' Calling stop action with ARRIVE ' || l_last_stop_id);
2186 END IF;
2187
2188
2189 p_entity_id_tab(1):= l_last_stop_id;
2190 p_action_prms.action_code := 'UPDATE-STATUS';
2191 p_action_prms.stop_action := 'ARRIVE';
2192 p_action_prms.phase:=NULL;
2193 p_action_prms.caller:='FTE_MLS_WRAPPER';
2194 p_action_prms.actual_date:=p_received_date;
2195
2196
2197 WSH_INTERFACE_GRP.Stop_Action
2198 ( p_api_version_number => p_api_version_number,
2199 p_init_msg_list => FND_API.G_FALSE,
2200 p_commit => 'F',
2201 p_entity_id_tab => p_entity_id_tab,
2202 p_action_prms => p_action_prms,
2203 x_stop_out_rec => x_stop_out_rec,
2204 x_return_status => l_return_status ,
2205 x_msg_count => l_msg_count,
2206 x_msg_data => l_msg_data
2207
2208 );
2209
2210 IF l_debug_on THEN
2211 WSH_DEBUG_SV.logmsg(l_module_name,'After calling stop action');
2212 WSH_DEBUG_SV.logmsg(l_module_name,'l_return_status:' || l_return_status);
2213 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_count:' || l_msg_count);
2214 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_count:' || l_msg_data);
2215
2216 END IF;
2217
2218
2219 wsh_util_core.api_post_call(
2220 p_return_status =>l_return_status,
2221 x_num_warnings =>l_number_of_warnings,
2222 x_num_errors =>l_number_of_errors,
2223 p_msg_data =>l_msg_data);
2224
2225
2226
2227 IF l_number_of_errors > 0
2228 THEN
2229 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2230 ELSIF l_number_of_warnings > 0
2231 THEN
2232 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2233 ELSE
2234 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2235 END IF;
2236
2237
2238 END IF;
2239
2240
2241
2242 END IF;
2243
2244 IF l_debug_on THEN
2245 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status:' || x_return_status);
2246 WSH_DEBUG_SV.logmsg(l_module_name,'x_msg_count:' || x_msg_count);
2247 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_count:' || l_msg_data);
2248
2249 END IF;
2250
2251
2252 IF l_debug_on THEN
2253 WSH_DEBUG_SV.pop(l_module_name);
2254 END IF;
2255 EXCEPTION
2256 WHEN FND_API.G_EXC_ERROR THEN
2257 ROLLBACK TO CALL_LAST_DELIVERY_LEG_PUB;
2258 x_return_status := FND_API.G_RET_STS_ERROR;
2259 FND_MSG_PUB.Count_And_Get
2260 (
2261 p_count => x_msg_count,
2262 p_data => x_msg_data,
2263 p_encoded => FND_API.G_FALSE
2264 );
2265 IF l_debug_on THEN
2266 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status:' || x_return_status);
2267 WSH_DEBUG_SV.logmsg(l_module_name,'x_msg_count:' || x_msg_count);
2268 WSH_DEBUG_SV.pop(l_module_name);
2269 END IF;
2270
2271 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2272 ROLLBACK TO CALL_LAST_DELIVERY_LEG_PUB;
2273 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2274 FND_MSG_PUB.Count_And_Get
2275 (
2276 p_count => x_msg_count,
2277 p_data => x_msg_data,
2278 p_encoded => FND_API.G_FALSE
2279 );
2280 IF l_debug_on THEN
2281 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status:' || x_return_status);
2282 WSH_DEBUG_SV.logmsg(l_module_name,'x_msg_count:' || x_msg_count);
2283 WSH_DEBUG_SV.pop(l_module_name);
2284 END IF;
2285 WHEN OTHERS THEN
2286 ROLLBACK TO CALL_LAST_DELIVERY_LEG_PUB;
2287 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2288 x_msg_data := substr(sqlerrm,1,200);
2289 IF l_debug_on THEN
2290 wsh_debug_sv.log (l_module_name,'Error',substr(sqlerrm,1,200));
2291 WSH_DEBUG_SV.pop(l_module_name);
2292 END IF;
2293
2294 FND_MSG_PUB.Count_And_Get
2295 (
2296 p_count => x_msg_count,
2297 p_data => x_msg_data,
2298 p_encoded => FND_API.G_FALSE
2299 );
2300
2301 END call_last_delivery_leg;
2302
2303 END FTE_TRACKING_WRAPPER;