[Home] [Help]
319: --
320: -- Procedure: release_plan
321: -- Parameters:
322: -- p_group_ids list of group_ids to process their
323: -- WSH_TRIPS_INTERFACE records and
324: -- their associated tables' records.
325: -- p_commit_flag FND_API.G_TRUE - commit changes; FND_API.G_FALSE - do not commit
326: -- x_return_status return status
327: -- success means all groups have been released.
650: --
651: -- Procedure: purge_interface_tables
652: -- Parameters:
653: -- p_group_ids list of group_ids to purge their
654: -- WSH_TRIPS_INTERFACE records and
655: -- their associated tables' records.
656: -- WSH_INTERFACE_ERRORS will be purged.
657: -- p_commit_flag FND_API.G_TRUE - commit changes; FND_API.G_FALSE - do not commit
658: -- x_return_status return status
690: FORALL i in p_group_ids.FIRST ..p_group_ids.LAST
691: delete from wsh_del_details_interface wddi
692: where wddi.delivery_detail_interface_id in (
693: select wdai.delivery_detail_interface_id
694: from wsh_trips_interface wti,
695: wsh_trip_stops_interface wtsi,
696: wsh_del_legs_interface wdli,
697: wsh_del_assgn_interface wdai
698: where wti.group_id = p_group_ids(i)
711: FORALL i in p_group_ids.FIRST ..p_group_ids.LAST
712: delete from wsh_del_assgn_interface wdai
713: where wdai.delivery_interface_id in (
714: select wdli.delivery_interface_id
715: from wsh_trips_interface wti,
716: wsh_trip_stops_interface wtsi,
717: wsh_del_legs_interface wdli
718: where wti.group_id = p_group_ids(i)
719: and wti.trip_interface_id = wtsi.trip_interface_id
729: FORALL i in p_group_ids.FIRST ..p_group_ids.LAST
730: delete from wsh_new_del_interface wndi
731: where wndi.delivery_interface_id in (
732: select wdli.delivery_interface_id
733: from wsh_trips_interface wti,
734: wsh_trip_stops_interface wtsi,
735: wsh_del_legs_interface wdli
736: where wti.group_id = p_group_ids(i)
737: and wti.trip_interface_id = wtsi.trip_interface_id
746: FORALL i in p_group_ids.FIRST ..p_group_ids.LAST
747: delete from wsh_del_legs_interface wdli
748: where wdli.pick_up_stop_interface_id in (
749: select wtsi.stop_interface_id
750: from wsh_trips_interface wti,
751: wsh_trip_stops_interface wtsi
752: where wti.group_id = p_group_ids(i)
753: and wti.trip_interface_id = wtsi.trip_interface_id
754: and wti.interface_action_code = G_TP_RELEASE_CODE
760: FORALL i in p_group_ids.FIRST ..p_group_ids.LAST
761: delete from wsh_trip_stops_interface wtsi
762: where wtsi.trip_interface_id in (
763: select wtsi.stop_interface_id
764: from wsh_trips_interface wti
765: where wti.group_id = p_group_ids(i)
766: and wti.interface_action_code = G_TP_RELEASE_CODE)
767: and wtsi.interface_action_code = G_TP_RELEASE_CODE;
768:
782: RETURN;
783: END IF;
784:
785:
786: -- Purge wsh_trips_interface
787: -- (Trips have to be the last entity interface table purged
788: -- because this is the only entity interface table having GROUP_ID.)
789:
790: FORALL i in p_group_ids.FIRST ..p_group_ids.LAST
787: -- (Trips have to be the last entity interface table purged
788: -- because this is the only entity interface table having GROUP_ID.)
789:
790: FORALL i in p_group_ids.FIRST ..p_group_ids.LAST
791: delete from wsh_trips_interface wti
792: where wti.group_id = p_group_ids(i)
793: and wti.INTERFACE_ACTION_CODE = G_TP_RELEASE_CODE;
794:
795:
989: wddi.source_header_id,
990: wddi.source_line_id,
991: wddi.source_line_set_id,
992: wndi.tp_delivery_number
993: from wsh_trips_interface wti,
994: wsh_trip_stops_interface wtsi,
995: wsh_del_legs_interface wdli,
996: wsh_del_assgn_interface wdai,
997: wsh_del_details_interface wddi,
1120: -- this cursor checks for existence of interface (dangling) containers
1121: -- Bug 4274651: It also checks for existence of cancelled lines.
1122: cursor c_tp_dangling_conts (p_group_id in number) is
1123: select wddi.delivery_detail_id
1124: from wsh_trips_interface wti,
1125: wsh_trip_stops_interface wtsi,
1126: wsh_del_legs_interface wdli,
1127: wsh_del_assgn_interface wdai,
1128: wsh_del_details_interface wddi,
1587: wddi.src_requested_quantity,
1588: wddi.src_requested_quantity_uom,
1589: wndi.delivery_id,
1590: wndi.delivery_interface_id
1591: FROM wsh_trips_interface wti,
1592: wsh_trip_stops_interface wtsi,
1593: wsh_del_legs_interface wdli,
1594: wsh_new_del_interface wndi,
1595: wsh_del_assgn_interface wdai,
1680: wndi.volume_uom_code,
1681: wndi.pooled_ship_to_location_id,
1682: wndi.dock_code,
1683: wnd.wv_frozen_flag -- WV changes
1684: FROM wsh_trips_interface wti,
1685: wsh_trip_stops_interface wtsi,
1686: wsh_del_legs_interface wdli,
1687: wsh_new_del_interface wndi,
1688: wsh_new_deliveries wnd
1743: wti.shipper_wait_time,
1744: wti.wait_time_uom,
1745: wti.carrier_response,
1746: wti.operator
1747: FROM wsh_trips_interface wti,
1748: wsh_trips wt
1749: WHERE wti.group_id = p_group_id
1750: AND wti.interface_action_code = G_TP_RELEASE_CODE
1751: AND wt.trip_id(+) = wti.trip_id
3508: <> NVL(itrip.vehicle_item_id, FND_API.G_MISS_NUM))
3509: ) THEN
3510: stamp_interface_error(
3511: p_group_id => x_context.group_id,
3512: p_entity_table_name => 'WSH_TRIPS_INTERFACE',
3513: p_entity_interface_id => itrip.trip_interface_id,
3514: p_message_name => 'WSH_TP_F_TRIP_DIFF_ATTR',
3515: p_token_1_name => 'TRIP_NAME',
3516: p_token_1_value => WSH_TRIPS_PVT.get_name(x_plan_trips(l_new_index).trip_id),
3547: EXCEPTION
3548: WHEN OTHERS THEN
3549: stamp_interface_error(
3550: p_group_id => x_context.group_id,
3551: p_entity_table_name => 'WSH_TRIPS_INTERFACE',
3552: p_entity_interface_id => itrip.trip_interface_id,
3553: p_message_name => 'WSH_TP_F_NO_LOCK_TRIP',
3554: p_token_1_name => 'TRIP_NAME',
3555: p_token_1_value => WSH_TRIPS_PVT.get_name(x_plan_trips(l_new_index).trip_id),
3592: WSH_UTIL_CORE.G_RET_STS_ERROR,
3593: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3594: stamp_interface_error(
3595: p_group_id => x_context.group_id,
3596: p_entity_table_name => 'WSH_TRIPS_INTERFACE',
3597: p_entity_interface_id => itrip.trip_interface_id,
3598: p_message_name => 'WSH_TP_F_INVALID_FC',
3599: p_token_1_name => 'PLAN_TRIP_NUM',
3600: p_token_1_value => itrip.tp_trip_number,
3698: -- if wsh trip is firmed and plan does not find matching stop, fail plan
3699: IF itrip.wsh_planned_flag IN ('Y', 'F') THEN --[
3700: stamp_interface_error(
3701: p_group_id => x_context.group_id,
3702: p_entity_table_name => 'WSH_TRIPS_INTERFACE',
3703: p_entity_interface_id => x_plan_trips(l_new_index).trip_interface_id,
3704: p_message_name => 'WSH_TP_F_TRIP_FIRM_NO_STOP',
3705: p_token_1_name => 'TRIP_NAME',
3706: p_token_1_value => WSH_TRIPS_PVT.get_name(x_plan_trips(l_new_index).trip_id),
4078: EXCEPTION
4079: WHEN OTHERS THEN
4080: stamp_interface_error(
4081: p_group_id => x_context.group_id,
4082: p_entity_table_name => 'WSH_TRIPS_INTERFACE',
4083: p_entity_interface_id => x_plan_trips(l_trip_index).trip_interface_id,
4084: p_message_name => 'WSH_TP_F_NO_LOCK_TRIP',
4085: p_token_1_name => 'TRIP_NAME',
4086: p_token_1_value => WSH_TRIPS_PVT.get_name(x_plan_trips(l_trip_index).trip_id),
4172:
4173: ELSIF (x_plan_trips(l_trip_index).wsh_planned_flag = 'F') THEN --[
4174: stamp_interface_error(
4175: p_group_id => x_context.group_id,
4176: p_entity_table_name => 'WSH_TRIPS_INTERFACE',
4177: p_entity_interface_id => x_plan_trips(l_trip_index).trip_interface_id,
4178: p_message_name => 'WSH_TP_F_TRIP_FIRM_NO_STOP',
4179: p_token_1_name => 'TRIP_NAME',
4180: p_token_1_value => WSH_TRIPS_PVT.get_name(x_plan_trips(l_trip_index).trip_id),
4264: -- fail plan if delivery is routing/contents firmed
4265: IF x_plan_deliveries(l_plan_del_index).wsh_planned_flag = 'F' THEN
4266: stamp_interface_error(
4267: p_group_id => x_context.group_id,
4268: p_entity_table_name => 'WSH_TRIPS_INTERFACE',
4269: p_entity_interface_id => x_plan_deliveries(l_plan_del_index).del_interface_id,
4270: p_message_name => 'WSH_TP_F_DEL_FIRM_LEG_DIFF',
4271: p_token_1_name => 'DELIVERY_NAME',
4272: p_token_1_value => wsh_new_deliveries_pvt.get_name(x_plan_deliveries(l_plan_del_index).delivery_id),
7415: WSH_DEBUG_SV.log(l_module_name, 'Bulk update trips: count', l_interface_ids.COUNT);
7416: END IF;
7417:
7418: FORALL i in 1..l_interface_ids.count
7419: UPDATE wsh_trips_interface
7420: SET trip_id = l_ids(i)
7421: WHERE trip_interface_id = l_interface_ids(i);
7422:
7423: -- update stops
9047: WSH_DEBUG_SV.logmsg(l_module_name,'plan does not match firmed trip having stop_id', s.stop_id);
9048: END IF;
9049:
9050: Stamp_Interface_Error(p_group_id => x_context.group_id,
9051: p_entity_table_name => 'WSH_TRIPS_INTERFACE',
9052: p_entity_interface_id => x_plan_trips(i).trip_interface_id,
9053: p_message_name => 'WSH_TP_F_PLAN_TRIP_NOT_MATCH',
9054: p_token_1_name => 'PLAN_TRIP_NUM',
9055: p_token_1_value => x_plan_trips(i).tp_trip_number,
9148: END IF;
9149:
9150: IF NVL(x_plan_trips(i).wsh_planned_flag,'N') = 'F' THEN
9151: Stamp_Interface_Error(p_group_id => x_context.group_id,
9152: p_entity_table_name => 'WSH_TRIPS_INTERFACE',
9153: p_entity_interface_id => x_plan_trips(i).trip_interface_id,
9154: p_message_name => 'WSH_TP_F_PLAN_TRIP_NOT_MATCH',
9155: x_errors_tab => x_errors_tab,
9156: x_return_status => x_return_status);
10253: x_return_status => l_return_status,
10254: x_msg_count => l_msg_count,
10255: x_msg_data => l_msg_data);
10256:
10257: l_interface_entity := 'WSH_TRIPS_INTERFACE';
10258: l_interface_id := x_plan_trips(l_index).trip_interface_id;
10259: wsh_util_core.api_post_call(
10260: p_return_status => l_return_status,
10261: x_num_warnings => l_number_of_warnings,
10627: -- mapping shipping trips that were snapshot;
10628: -- code will later validate these shipping trips can be mapped.
10629: CURSOR c_name(p_group_id IN NUMBER) IS
10630: SELECT wti.tp_trip_number
10631: FROM wsh_trips_interface wti
10632: WHERE wti.group_id = p_group_id
10633: AND wti.interface_action_code = G_TP_RELEASE_CODE
10634: AND rownum = 1
10635: ;