1 PACKAGE FTE_LTL_LOADER AUTHID CURRENT_USER AS
2 /* $Header: FTELTLRS.pls 120.0 2005/06/28 02:24:35 pkaliyam noship $ */
3 --
4 -- Package
5 -- FTE_BULKLOAD_LTL
6 --
7 -- global package variables
8
9 TYPE NUMBER_TAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
10 TYPE POSITIVE_NUMBER_TAB IS TABLE OF NATURAL INDEX BY BINARY_INTEGER;
11
12 TYPE VAR_ARR40 IS TABLE OF VARCHAR2(1200);
13 TYPE VAR_ARR4000 IS TABLE OF VARCHAR2(4000);
14 TYPE VAR_ARR15 IS TABLE OF VARCHAR2(15);
15 TYPE VAR_ARR10 IS TABLE OF VARCHAR2(10);
16 TYPE NUMBER_ARR IS TABLE OF NUMBER;
17 TYPE VAR_ARR100 IS TABLE OF VARCHAR2(100);
18
19 TYPE VAR_ARR10_INDEX IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
20 TYPE VAR_ARR40_INDEX IS TABLE OF VARCHAR2(40) INDEX BY BINARY_INTEGER;
21
22 TYPE LANES_TEMP_RECORD IS RECORD(
23 rate_chart_string var_arr40,
24 dest_low var_arr10,
25 dest_high var_arr10,
26 class var_arr100,
27 min_charge1 var_arr10,
28 rate_chart_name var_arr40,
29 origin_id number_arr,
30 dest_id number_arr);
31
32 TYPE ZONES_TEMP_RECORD IS RECORD(
33 zone_name var_arr40,
34 dest_low var_arr10,
35 dest_high var_arr10,
36 hash_value number_arr,
37 dest_string var_arr4000,
38 row_number number_arr,
39 owner_id number_arr);
40
41 TYPE ZONE_INFO_RECORD IS RECORD(
42 zone_name var_arr40,
43 sum_row_number number_arr,
44 max_row_number number_arr,
45 sum_hash_value number_arr,
46 sum_postal_code_from number_arr,
47 sum_postal_code_to number_arr);
48
49 -- Used in Read_LTL_File
50
51 TYPE FL_SCAC_TAB IS TABLE OF FTE_BULKLOAD_FILE.SCAC%TYPE INDEX BY BINARY_INTEGER;
52 TYPE FL_EFFECTIVE_DATE_TAB IS TABLE OF FTE_BULKLOAD_FILE.EFFECTIVE_DATE%TYPE INDEX BY BINARY_INTEGER;
53 TYPE FL_OUTBOUND_FLAG_TAB IS TABLE OF FTE_BULKLOAD_FILE.OUTBOUND_FLAG%TYPE INDEX BY BINARY_INTEGER;
54
55 FL_LOAD_ID NUMBER_TAB;
56 FL_SCAC FL_SCAC_TAB;
57 FL_ORIGIN_LOW VAR_ARR10_INDEX;
58 FL_ORIGIN_HIGH VAR_ARR10_INDEX;
59 FL_DEST_LOW VAR_ARR10_INDEX;
60 FL_DEST_HIGH VAR_ARR10_INDEX;
61 FL_CLASS NUMBER_TAB;
62 FL_MIN_CHARGE1 POSITIVE_NUMBER_TAB;
63 FL_L5C POSITIVE_NUMBER_TAB;
64 FL_M5C POSITIVE_NUMBER_TAB;
65 FL_M1M POSITIVE_NUMBER_TAB;
66 FL_M2M POSITIVE_NUMBER_TAB;
67 FL_M5M POSITIVE_NUMBER_TAB;
68 FL_M10M POSITIVE_NUMBER_TAB;
69 FL_M20M POSITIVE_NUMBER_TAB;
70 FL_M30M POSITIVE_NUMBER_TAB;
71 FL_M40M POSITIVE_NUMBER_TAB;
72 FL_EFFECTIVE_DATE FL_EFFECTIVE_DATE_TAB;
73 FL_OUTBOUND_FLAG FL_OUTBOUND_FLAG_TAB;
74 FL_MILEAGE POSITIVE_NUMBER_TAB;
75
76 TYPE LN_COMM_FC_CLASS_CODE_TAB IS TABLE OF FTE_LANES.COMM_FC_CLASS_CODE%TYPE INDEX BY BINARY_INTEGER;
77 TYPE LN_LANE_TYPE_TAB IS TABLE OF FTE_LANES.LANE_TYPE%TYPE INDEX BY BINARY_INTEGER;
78 TYPE LN_TARIFF_NAME_TAB IS TABLE OF FTE_LANES.TARIFF_NAME%TYPE INDEX BY BINARY_INTEGER;
79
80 LN_LANE_ID NUMBER_TAB;
81 LN_CARRIER_ID NUMBER_TAB;
82 LN_ORIGIN_ID NUMBER_TAB;
83 LN_DEST_ID NUMBER_TAB;
84 LN_COMMODITY_CATG_ID NUMBER_TAB;
85 LN_COMM_FC_CLASS_CODE LN_COMM_FC_CLASS_CODE_TAB;
86 LN_LANE_TYPE LN_LANE_TYPE_TAB;
87 LN_TARIFF_NAME LN_TARIFF_NAME_TAB;
88 LN_START_DATE var_arr40_index;
89 LN_END_DATE var_arr40_index;
90
91 TYPE PRC_VALUE_FROM_TAB IS TABLE OF FTE_PRC_PARAMETERS.VALUE_FROM%TYPE INDEX BY BINARY_INTEGER;
92 TYPE PRC_PARAMETER_ID_TAB IS TABLE OF FTE_PRC_PARAMETERS.PARAMETER_ID%TYPE INDEX BY BINARY_INTEGER;
93
94 PRC_VALUE_FROM PRC_VALUE_FROM_TAB;
95 PRC_PARAMETER_ID PRC_PARAMETER_ID_TAB;
96 PRC_LANE_ID NUMBER_TAB;
97
98 TYPE CM_CATG_ID_TAB IS TABLE OF FTE_LANE_COMMODITIES.COMMODITY_CATG_ID%TYPE INDEX BY BINARY_INTEGER;
99
100 CM_CATG_ID CM_CATG_ID_TAB;
101 CM_LANE_ID NUMBER_TAB;
102
103
104 TYPE LRC_LANE_ID_TAB IS TABLE OF FTE_LANE_RATE_CHARTS.LANE_ID%TYPE INDEX BY BINARY_INTEGER;
105 TYPE LRC_LIST_HEADER_ID_TAB IS TABLE OF FTE_LANE_RATE_CHARTS.LIST_HEADER_ID%TYPE INDEX BY BINARY_INTEGER;
106
107 LRC_LANE_ID LRC_LANE_ID_TAB;
108 LRC_LIST_HEADER_ID LRC_LIST_HEADER_ID_TAB;
109 LRC_START_DATE var_arr40_index;
110 LRC_END_DATE var_arr40_index;
111
112
113 TYPE lane_number_tab IS TABLE OF fte_lanes.lane_number%TYPE INDEX BY BINARY_INTEGER;
114 TYPE zone_tab IS TABLE OF wsh_regions_tl.zone%TYPE INDEX BY BINARY_INTEGER;
115 TYPE carrier_name_tab IS TABLE OF hz_parties.party_name%TYPE INDEX BY BINARY_INTEGER;
116 TYPE min_charge_tab IS TABLE OF fte_prc_parameters.value_from%TYPE INDEX BY BINARY_INTEGER;
117 TYPE ZoneNamesTab IS TABLE OF fte_interface_zones.zone_name%TYPE INDEX BY BINARY_INTEGER;
118 TYPE RateChartNamesTab IS TABLE OF qp_list_headers_tl.name%TYPE INDEX BY BINARY_INTEGER;
119
120 TYPE LTL_REPORT_HEADER IS RECORD(StartDate VARCHAR2(100),
121 EndDate VARCHAR2(100),
122 FileName VARCHAR2(100),
123 TariffName VARCHAR2(150),
124 ServiceLevel VARCHAR2(60),
125 Orig_Country VARCHAR2(80),
126 Dest_Country VARCHAR2(80),
127 Currency VARCHAR2(50),
128 UOM VARCHAR2(25));
129
130 FUNCTION VERIFY_TARIFF_CARRIER(p_tariff_name IN VARCHAR2,
131 p_carrier_id IN NUMBER,
132 x_error_msg OUT NOCOPY VARCHAR2)
133 RETURN BOOLEAN;
134
135 FUNCTION GET_TARIFF_RATECHARTS (p_tariff_name IN VARCHAR2,
136 x_error_msg OUT NOCOPY VARCHAR2)
137 RETURN WSH_UTIL_CORE.ID_TAB_TYPE;
138
139 /* PROCEDURE PROCESS_LTL_DATA(p_load_id IN NUMBER,
140 p_src_filename IN VARCHAR2,
141 p_currency IN VARCHAR2,
142 p_uom_code IN VARCHAR2,
143 p_orig_country IN VARCHAR2,
144 p_dest_country IN VARCHAR2,
145 p_service_code IN VARCHAR2,
146 p_action_code IN VARCHAR2,
147 p_tariff_name IN VARCHAR2,
148 x_request_id OUT NOCOPY NUMBER,
149 x_error_msg OUT NOCOPY VARCHAR2);*/
150
151 PROCEDURE QP_PROCESS ( errbuf OUT NOCOPY VARCHAR2,
152 retcode OUT NOCOPY VARCHAR2,
153 p_load_id IN NUMBER,
154 p_group_process_id IN NUMBER,
155 p_user_debug IN NUMBER);
156
157 PROCEDURE PROCESS_LTL_DATA(errbuf OUT NOCOPY VARCHAR2,
158 retcode OUT NOCOPY VARCHAR2,
159 p_load_id IN NUMBER,
160 p_src_filename IN VARCHAR2,
161 p_currency IN VARCHAR2,
162 p_uom_code IN VARCHAR2,
163 p_orig_country IN VARCHAR2,
164 p_dest_country IN VARCHAR2,
165 p_service_code IN VARCHAR2,
166 p_action_code IN VARCHAR2,
167 p_tariff_name IN VARCHAR2,
168 p_user_debug IN NUMBER);
169
170 END FTE_LTL_LOADER;
[Home] [Help]