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
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',
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(
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',
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:
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
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',
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:
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
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',
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:
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
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',
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:
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
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',
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
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
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',
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
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