[Home] [Help]
57: CURSOR GET_LOAD_NUMBER(p_tariff_name IN VARCHAR2) IS
58: SELECT
59: SUBSTR(lane_type,INSTR(lane_type,'_', -1)+1)
60: FROM
61: fte_lanes
62: WHERE
63: tariff_name = p_tariff_name
64: ORDER BY creation_date DESC;
65:
86: owr.country_code,
87: dwr.country_code,
88: l.carrier_id
89: FROM
90: fte_lanes l,
91: fte_tariff_carriers tc,
92: wsh_zone_regions ozr,
93: wsh_zone_regions dzr,
94: wsh_regions owr,
279: p_carrier_id IN NUMBER) IS
280: SELECT distinct
281: lrc.list_header_id
282: FROM
283: fte_lanes l,
284: fte_lane_rate_charts lrc
285: WHERE
286: l.lane_id = lrc.lane_id AND
287: l.tariff_name = p_tariff_name AND
360: BEGIN
361: FTE_UTIL_PKG.Enter_Debug(l_module_name);
362:
363: FORALL i IN 1..LN_LANE_ID.COUNT
364: INSERT INTO fte_lanes( lane_id,
365: lane_number,
366: owner_id,
367: carrier_id,
368: origin_id,
654: hzp.party_name carrier_name,
655: qlht.name rate_chart_name,
656: prc.value_from minimum_charge
657: FROM
658: fte_lanes l,
659: hz_parties hzp,
660: qp_list_headers_tl qlht,
661: wsh_regions_tl oz,
662: wsh_regions_tl dz,
679: CURSOR NEW_RATE_CHARTS (p_load_number IN NUMBER, p_tariff_name IN VARCHAR2) IS
680: SELECT DISTINCT
681: qlht.name rate_chart_name
682: FROM
683: fte_lanes l,
684: fte_lane_rate_charts flrc,
685: qp_list_headers_tl qlht
686: WHERE
687: l.tariff_name = p_tariff_name AND
899: --
900: -- PROCEDURE: OBSOLETE_PREVIOUS_LOAD
901: --
902: -- PURPOSE Obsolete the lanes from a previous LTL load. This procedure
903: -- uses the lane_type column in FTE_LANES to identify the lanes
904: -- to be obsoleted.
905: --
906: -- PARAMETERS
907: -- IN
948: --+
949: IF p_delete_lanes THEN
950:
951: UPDATE
952: FTE_LANES
953: SET
954: expiry_date = sysdate-1,
955: effective_date = sysdate,
956: editable_flag = 'D',
966:
967: ELSE
968:
969: UPDATE
970: FTE_LANES
971: SET
972: expiry_date = (G_VALID_DATE-Fnd_Number.Canonical_To_Number('0.0001')),
973: last_updated_by = G_USER_ID,
974: last_update_date = SYSDATE,
1094: qp_list_headers_tl lh,
1095: qp_list_headers_b b,
1096: qp_qualifiers ql,
1097: fte_lane_rate_charts lrc,
1098: fte_lanes l
1099: WHERE
1100: l.tariff_name = p_tariff_name AND
1101: l.lane_type = 'LTL_' || p_tariff_name || '_' || p_load_number AND
1102: l.lane_id = lrc.lane_id AND
1528: FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_at || ' from QP_PRICINg_ATTRIBUTES');
1529: FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_ql || ' from QP_QUALIFIERS');
1530: FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_ln || ' from FTE_INTERFACE_LANES');
1531: FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_zn || ' from FTE_INTERFACE_ZONES');
1532: FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_lane || ' from FTE_LANES');
1533: END IF;
1534:
1535: --+
1536: -- Cleanup FTE_TARIFF_CARRIERS
2045: -- For each carrier, create a lane and its associated entities
2046: --+
2047: FOR k IN 1..p_carrier_ids.COUNT LOOP
2048:
2049: SELECT fte_lanes_s.NEXTVAL INTO l_lane_id FROM DUAL;
2050:
2051: -- FTE_LANE_COMMODITIES
2052: FOR m IN 1..p_category_ids.COUNT LOOP
2053: CM_LANE_ID(cm_lane_id.COUNT+1) := l_lane_id;
2055: END LOOP;
2056:
2057: --+
2058: -- If we have more than one commodity on the lane, we need to set the commodity
2059: -- in FTE_LANES to null and store everything in FTE_LANE_COMMODITIES.
2060: --+
2061: IF p_category_ids.COUNT > 1 THEN
2062: l_catg_id := NULL;
2063: END IF;
2061: IF p_category_ids.COUNT > 1 THEN
2062: l_catg_id := NULL;
2063: END IF;
2064:
2065: -- FTE_LANES
2066: LN_LANE_ID(ln_lane_id.COUNT+1) := l_lane_id;
2067: LN_CARRIER_ID(ln_carrier_id.COUNT+1):= p_carrier_ids(k);
2068: LN_START_DATE(ln_start_date.COUNT+1):= p_effective_dates(k);
2069: LN_END_DATE(ln_end_date.COUNT+1) := p_expiry_dates(k);
2698: CURSOR GET_LOAD_NUMBER(p_tariff_name IN VARCHAR2) IS
2699: SELECT
2700: substr(lane_type,instr(lane_type, '_', -1 ) + 1)
2701: FROM
2702: fte_lanes
2703: WHERE
2704: tariff_name = p_tariff_name
2705: ORDER BY creation_date desc;
2706:
2752: l_need_to_fetch := false;
2753:
2754: --+
2755: -- Get the load number for the previous load
2756: -- The LANE_TYPE of FTE_LANES has the format LTL_
2758: OPEN GET_LOAD_NUMBER(p_tariff_name);
2759:
2760: FETCH GET_LOAD_NUMBER INTO l_number_of_loads;
4641: lrc.list_header_id,
4642: lrc.start_date_active,
4643: prc.value_from
4644: FROM
4645: fte_lanes l,
4646: fte_lane_rate_charts lrc,
4647: fte_prc_parameters prc
4648: WHERE
4649: l.tariff_name = p_tariff_name AND
4669: DECODE (COUNT(DISTINCT origin_id), 1, 'O', 'I') direction,
4670: MAX(ozr.postal_code_from) origin_low,
4671: MAX(dzr.postal_code_from) destination_low
4672: FROM
4673: fte_lanes l,
4674: wsh_zone_regions ozr,
4675: wsh_zone_regions dzr
4676: WHERE
4677: l.lane_type = p_lane_type AND
4959:
4960: IF l_modified_carriers.COUNT > 0 THEN
4961: FORALL n IN 1..l_modified_carriers.COUNT
4962: UPDATE
4963: fte_lanes
4964: SET
4965: effective_date = l_mod_start_dates(n),
4966: expiry_date = l_mod_end_dates(n),
4967: last_updated_by = G_USER_ID,
5001: -- SET start_date_active = l_mod_start_dates(n),
5002: -- end_date_active = l_mod_end_dates(n),
5003: -- last_update_date = sysdate
5004: -- WHERE lane_id IN
5005: -- (select lane_id from fte_lanes
5006: -- where lane_type = 'LTL_' || p_tariff_name || '_' || l_load_number
5007: -- and carrier_id = l_modified_carriers(n)
5008: -- and tariff_name = p_tariff_name);
5009: END IF;