[Home] [Help]
150: --
151: -- FUNCTION: ROW_NUM_MAX
152: --
153: -- PURPOSE: To get the next number to be used for ROW_NUMBER in
154: -- insertion into fte_interface_zones.
155: --
156: -- RETURNS: The number to use for ROW_NUMBER, by adding one to the max of the
157: -- ROW_NUMBER at present in FTE_INTERFACE_ZONES for a given zone.
158: --_________________________________________________________________________________--
153: -- PURPOSE: To get the next number to be used for ROW_NUMBER in
154: -- insertion into fte_interface_zones.
155: --
156: -- RETURNS: The number to use for ROW_NUMBER, by adding one to the max of the
157: -- ROW_NUMBER at present in FTE_INTERFACE_ZONES for a given zone.
158: --_________________________________________________________________________________--
159:
160: FUNCTION ROW_NUM_MAX(l_zone_name IN VARCHAR2) RETURN NUMBER IS
161:
167:
168: SELECT
169: MAX(row_number) INTO l_max
170: FROM
171: fte_interface_zones
172: WHERE
173: zone_name = l_zone_name;
174:
175: FTE_UTIL_PKG.Exit_Debug(l_module_name);
640: CURSOR NEW_ZONES (p_load_id IN NUMBER) IS
641: SELECT
642: zone_name
643: FROM
644: fte_interface_zones
645: WHERE
646: load_id = p_load_id AND
647: hash_value <> 0;
648:
1491: QP_LIST_HEADERS_TL
1492: WHERE name = l_chart_name;
1493:
1494: DELETE FROM
1495: FTE_INTERFACE_ZONES
1496: WHERE load_id = p_load_id;
1497:
1498: l_num_zn := l_num_zn + SQL%ROWCOUNT;
1499: END IF;
1517: DELETE FROM fte_interface_lanes WHERE load_id = p_load_id;
1518:
1519: l_num_ln := SQL%ROWCOUNT;
1520:
1521: DELETE FROM fte_interface_zones WHERE zone_id IS NULL;
1522:
1523: l_num_zn := l_num_zn + SQL%ROWCOUNT;
1524:
1525: IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
1527: FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_ll || ' from QP_LIST_LINES');
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: --+
2382: postal_code_string,
2383: row_number,
2384: zone_id
2385: FROM
2386: fte_interface_zones
2387: WHERE
2388: zone_name IN (p_zone1, p_zone2) AND
2389: hash_value <> 0
2390: ORDER BY
2394: SELECT
2395: w2.country_code
2396: FROM
2397: wsh_zone_regions w,
2398: fte_interface_zones f,
2399: wsh_regions w2
2400: WHERE
2401: w.parent_region_id = f.zone_id AND
2402: f.zone_name = p_zone_name AND
2529: --
2530: -- PURPOSE
2531: -- Analyze the data in FTE_BULKLOAD_FILE:
2532: -- a. Identify the destinations sharing the same rate chart and group
2533: -- them in the same zone. Put this information in FTE_INTERFACE_ZONES.
2534: -- b. Create a rate chart for each zone. Put this chart information
2535: -- into the QP_INTERFACE tables and later into the QP tables.
2536: -- PARAMETERS
2537: -- IN
2633: -- PURPOSE
2634: --
2635: -- Analyze the data in FTE_BULKLOAD_FILE:
2636: -- a. Identify the destinations sharing the same rate chart and group
2637: -- them in the same zone. Put this information in FTE_INTERFACE_ZONES.
2638: -- b. Create a rate chart for each zone. Put this chart information
2639: -- into the QP_INTERFACE tables and later into the QP tables.
2640: -- PARAMETERS
2641: -- IN p_load_id,
2805: FTE_UTIL_PKG.Write_LogFile(l_module_name, 'p_load_id ', p_load_id);
2806: END IF;
2807:
2808: --+
2809: -- Insert a new origin into FTE_INTERFACE_ZONES
2810: --+
2811: BEGIN
2812: INSERT INTO fte_interface_zones(ZONE_NAME,
2813: POSTAL_CODE_FROM,
2808: --+
2809: -- Insert a new origin into FTE_INTERFACE_ZONES
2810: --+
2811: BEGIN
2812: INSERT INTO fte_interface_zones(ZONE_NAME,
2813: POSTAL_CODE_FROM,
2814: POSTAL_CODE_TO,
2815: POSTAL_CODE_STRING,
2816: LOAD_ID,
2827: 1);
2828: EXCEPTION
2829: WHEN OTHERS THEN
2830: x_status := 2;
2831: FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXCEPTED ERROR while inserting into FTE_INTERFACE_ZONES',sqlerrm);
2832: FTE_UTIL_PKG.Exit_Debug(l_module_name);
2833: RETURN;
2834: END ;
2835:
3000: IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
3001: FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_row_number' || l_row_number);
3002: END IF;
3003:
3004: INSERT INTO fte_interface_zones(ZONE_NAME,
3005: POSTAL_CODE_FROM,
3006: POSTAL_CODE_TO,
3007: POSTAL_CODE_STRING,
3008: LOAD_ID,
3080: null,
3081: l_process_id,
3082: decode(l_rate_chart_match, 'N', l_group_id, 'Y', NULL));
3083:
3084: INSERT INTO fte_interface_zones(ZONE_NAME,
3085: POSTAL_CODE_FROM,
3086: POSTAL_CODE_TO,
3087: POSTAL_CODE_STRING,
3088: LOAD_ID,
3167: --
3168: -- PROCEDURE: MANAGE_ZONES
3169: --
3170: -- PURPOSE
3171: -- Remove redundant rows from FTE_INTERFACE_ZONES by grouping
3172: -- all zones sharing the same rate chart into a single row.
3173: -- Reuse old zones if the new zones are exactly the same.
3174: --
3175: -- PARAMETERS
3215: l_return_status VARCHAR2(100);
3216: l_error_msg VARCHAR2(100);
3217:
3218: --+
3219: -- This cursor is used to collect queried rows from FTE_INTERFACE_ZONES. With those rows we want to
3220: -- check if zones are already present into the system.
3221: --+
3222: CURSOR DEFINE_ZONES(p_load_id NUMBER) IS
3223: SELECT
3224: zone_name,
3225: postal_code_from,
3226: postal_code_to
3227: FROM
3228: fte_interface_zones
3229: WHERE
3230: load_id = p_load_id
3231: ORDER BY
3232: zone_name ASC, row_number ASC ;
3232: zone_name ASC, row_number ASC ;
3233:
3234: --
3235: -- Get summary zone information about existing zones in
3236: -- fte_interface_zones. This is used to check if a new
3237: -- zone already exists in the table.
3238: --
3239: CURSOR GET_EXISTING_ZONE_INFO(p_load_id IN NUMBER,
3240: p_sum_rownum IN NUMBER,
3242: p_sum_hash IN NUMBER)IS
3243: SELECT
3244: zone_name
3245: FROM
3246: fte_interface_zones
3247: WHERE
3248: load_id <> p_load_id
3249: HAVING
3250: SUM(hash_value) = p_sum_hash AND
3259: SUM(row_number),
3260: MAX(row_number),
3261: SUM(hash_value)
3262: FROM
3263: fte_interface_zones
3264: WHERE
3265: load_id = p_load_id AND
3266: hash_value <> 0
3267: GROUP BY
3319:
3320: IF (l_need_to_fetch AND l_previous_name <> l_zones_temp.zone_name(l_counter)) THEN
3321: --
3322: -- zone name has changed at fetch boundary. We need to update
3323: -- the previous zone in fte_interface_zones before building the
3324: -- next zone.
3325: --
3326: l_zone_name := l_previous_name;
3327: ELSE
3361: base => g_hash_base,
3362: hash_size => g_hash_size );
3363:
3364: UPDATE
3365: FTE_INTERFACE_ZONES
3366: SET
3367: POSTAL_CODE_STRING = l_dest_string,
3368: HASH_VALUE = l_hash_value
3369: WHERE
3509: --
3510: -- we don't need this current zone
3511: --
3512: DELETE FROM
3513: fte_interface_zones
3514: WHERE
3515: zone_name = l_zone_name AND
3516: load_id = p_load_id;
3517: END IF;
3574: l_zones_temp zones_temp_record;
3575: l_zone_name VARCHAR2(125);
3576:
3577: --+
3578: -- This cursor is used to collect queried rows from fte_interface_zones. Those are used to
3579: -- create Zones into WSH_REGIONS, WSH_REGIONS_TL and WSH_ZONE_REGIONS
3580: --+
3581: CURSOR BULK_ZONES(p_load_id NUMBER) IS
3582: SELECT
3583: zone_name,
3584: postal_code_from,
3585: postal_code_to
3586: FROM
3587: fte_interface_zones
3588: WHERE
3589: load_id = p_load_id AND
3590: zone_id is null
3591: ORDER BY
3668: END IF;
3669:
3670: BEGIN
3671: UPDATE
3672: FTE_INTERFACE_ZONES
3673: SET
3674: ZONE_ID = l_zone_id
3675: WHERE
3676: zone_name = l_zone_name AND
3974: RETURN;
3975: END IF;
3976:
3977: --+
3978: -- The rows in FTE_INTERFACE_ZONES with hash value of 0 are no longer needed
3979: -- because the information has been put into another row.
3980: --+
3981: DELETE FROM FTE_INTERFACE_ZONES WHERE hash_value = 0;
3982:
3977: --+
3978: -- The rows in FTE_INTERFACE_ZONES with hash value of 0 are no longer needed
3979: -- because the information has been put into another row.
3980: --+
3981: DELETE FROM FTE_INTERFACE_ZONES WHERE hash_value = 0;
3982:
3983: IF (l_number_of_loads = 1 and G_SERVICE_CODE = 'LTL') THEN
3984:
3985: --+