1 PACKAGE FTE_RATE_CHART_PKG AUTHID CURRENT_USER AS
2 /* $Header: FTERCPKS.pls 120.3 2005/07/21 14:13:29 jishen noship $ */
3
4 TYPE qp_list_header_rec IS RECORD (
5 PROCESS_ID qp_interface_list_headers.process_id%TYPE,
6 INTERFACE_ACTION_CODE qp_interface_list_headers.interface_action_code%TYPE,
7 LIST_TYPE_CODE qp_interface_list_headers.list_type_code%TYPE,
8 START_DATE_ACTIVE qp_interface_list_headers.start_date_active%TYPE,
9 END_DATE_ACTIVE qp_interface_list_headers.end_date_active%TYPE,
10 CURRENCY_CODE qp_interface_list_headers.currency_code%TYPE,
11 NAME qp_interface_list_headers.name%TYPE,
12 DESCRIPTION qp_interface_list_headers.description%TYPE,
13 LIST_HEADER_ID qp_interface_list_headers.list_header_id%TYPE,
14 ATTRIBUTE1 qp_interface_list_headers.attribute1%TYPE);
15
16 TYPE qp_list_header_tbl IS TABLE OF
17 qp_list_header_rec
18 INDEX BY BINARY_INTEGER;
19
20 TYPE qp_list_line_rec IS RECORD (
21 PROCESS_ID qp_interface_list_lines.process_id%TYPE,
22 OPERAND qp_interface_list_lines.operand%TYPE,
23 COMMENTS qp_interface_list_lines.comments%TYPE,
24 LIST_LINE_NO qp_interface_list_lines.list_line_no%TYPE,
25 PRIMARY_UOM_FLAG qp_interface_list_lines.primary_uom_flag%TYPE,
26 PROCESS_TYPE qp_interface_list_lines.process_type%TYPE,
27 INTERFACE_ACTION_CODE qp_interface_list_lines.interface_action_code%TYPE,
28 LIST_LINE_TYPE_CODE qp_interface_list_lines.list_line_type_code%TYPE,
29 AUTOMATIC_FLAG qp_interface_list_lines.automatic_flag%TYPE,
30 OVERRIDE_FLAG qp_interface_list_lines.override_flag%TYPE,
31 MODIFIER_LEVEL_CODE qp_interface_list_lines.modifier_level_code%TYPE,
32 ARITHMETIC_OPERATOR qp_interface_list_lines.arithmetic_operator%TYPE,
33 ACCRUAL_FLAG qp_interface_list_lines.accrual_flag%TYPE,
34 PRICE_BREAK_TYPE_CODE qp_interface_list_lines.price_break_type_code%TYPE,
35 PRODUCT_PRECEDENCE qp_interface_list_lines.product_precedence%TYPE,
36 PRICE_BREAK_HEADER_INDEX qp_interface_list_lines.price_break_header_index%TYPE,
37 RLTD_MODIFIER_GRP_NO qp_interface_list_lines.rltd_modifier_grp_no%TYPE,
38 PRICE_BY_FORMULA_ID qp_interface_list_lines.price_by_formula_id%TYPE,
39 ATTRIBUTE1 qp_interface_list_lines.attribute1%TYPE,
40 ATTRIBUTE2 qp_interface_list_lines.attribute2%TYPE,
41 RLTD_MODIFIER_GRP_TYPE qp_interface_list_lines.rltd_modifier_grp_type%TYPE,
42 PRICING_GROUP_SEQUENCE qp_interface_list_lines.pricing_group_sequence%TYPE,
43 PRICING_PHASE_ID qp_interface_list_lines.pricing_phase_id%TYPE,
44 QUALIFICATION_IND qp_interface_list_lines.qualification_ind%TYPE,
45 CHARGE_TYPE_CODE qp_interface_list_lines.charge_type_code%TYPE,
46 CHARGE_SUBTYPE_CODE qp_interface_list_lines.charge_subtype_code%TYPE,
47 START_DATE_ACTIVE qp_interface_list_lines.start_date_active%TYPE,
48 END_DATE_ACTIVE qp_interface_list_lines.end_date_active%TYPE);
49
50 TYPE qp_list_line_tbl IS TABLE OF
51 qp_list_line_rec
52 INDEX BY BINARY_INTEGER;
53
54 TYPE qp_pricing_attrib_rec IS RECORD (
55 PROCESS_ID qp_interface_pricing_attribs.process_id%TYPE,
59 PRODUCT_ATTRIBUTE_CONTEXT qp_interface_pricing_attribs.product_attribute_context%TYPE,
56 PROCESS_TYPE qp_interface_pricing_attribs.process_type%TYPE,
57 INTERFACE_ACTION_CODE qp_interface_pricing_attribs.interface_action_code%TYPE,
58 EXCLUDER_FLAG qp_interface_pricing_attribs.excluder_flag%TYPE,
60 PRODUCT_ATTRIBUTE qp_interface_pricing_attribs.product_attribute%TYPE,
61 PRODUCT_ATTR_VALUE qp_interface_pricing_attribs.product_attr_value%TYPE,
62 PRODUCT_UOM_CODE qp_interface_pricing_attribs.product_uom_code%TYPE,
63 PRODUCT_ATTRIBUTE_DATATYPE qp_interface_pricing_attribs.product_attribute_datatype%TYPE,
64 PRICING_ATTRIBUTE_DATATYPE qp_interface_pricing_attribs.pricing_attribute_datatype%TYPE,
65 PRICING_ATTRIBUTE_CONTEXT qp_interface_pricing_attribs.pricing_attribute_context%TYPE,
66 PRICING_ATTRIBUTE qp_interface_pricing_attribs.pricing_attribute%TYPE,
67 PRICING_ATTR_VALUE_FROM qp_interface_pricing_attribs.pricing_attr_value_from%TYPE,
68 PRICING_ATTR_VALUE_TO qp_interface_pricing_attribs.pricing_attr_value_to%TYPE,
69 ATTRIBUTE_GROUPING_NO qp_interface_pricing_attribs.attribute_grouping_no%TYPE,
70 COMPARISON_OPERATOR_CODE qp_interface_pricing_attribs.comparison_operator_code%TYPE,
71 LIST_LINE_NO qp_interface_pricing_attribs.list_line_no%TYPE);
72
73 TYPE qp_pricing_attrib_tbl IS TABLE OF
74 qp_pricing_attrib_rec
75 INDEX BY BINARY_INTEGER;
76
77 TYPE qp_qualifier_rec IS RECORD (
78 PROCESS_ID qp_interface_qualifiers.process_id%TYPE,
79 INTERFACE_ACTION_CODE qp_interface_qualifiers.interface_action_code%TYPE,
80 QUALIFIER_ATTR_VALUE qp_interface_qualifiers.qualifier_attr_value%TYPE,
81 QUALIFIER_GROUPING_NO qp_interface_qualifiers.qualifier_grouping_no%TYPE,
82 PROCESS_TYPE qp_interface_qualifiers.process_type%TYPE,
83 EXCLUDER_FLAG qp_interface_qualifiers.excluder_flag%TYPE,
84 COMPARISON_OPERATOR_CODE qp_interface_qualifiers.comparison_operator_code%TYPE,
85 QUALIFIER_CONTEXT qp_interface_qualifiers.qualifier_context%TYPE,
86 QUALIFIER_ATTRIBUTE qp_interface_qualifiers.qualifier_attribute%TYPE);
87
88 TYPE qp_qualifier_tbl IS TABLE OF
89 qp_qualifier_rec
90 INDEX BY BINARY_INTEGER;
91
92 g_is_ltl BOOLEAN := false;
93
94 TYPE VARCHAR2_TAB IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
95 TYPE NUMBER_TAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
96
97 TYPE LH_CURRENCY_CODE_TAB IS TABLE OF QP_INTERFACE_LIST_HEADERS.CURRENCY_CODE%TYPE INDEX BY BINARY_INTEGER;
98 TYPE LH_NAME_TAB IS TABLE OF QP_INTERFACE_LIST_HEADERS.NAME%TYPE INDEX BY BINARY_INTEGER;
99
100 -- GLOBAL VARIABLES FOR Rate Chart HEADER
101
102 G_Chart_Type VARCHAR2(50);
103 G_prc_brk_hdr_count NUMBER := 0;
104 G_brk_hdr_updt_cnt NUMBER := 0;
105
106
107 G_line_desc VARCHAR2(200);
108 G_previous_upper NUMBER := 0;
109 G_qualifier_count NUMBER := 0;
110 g_qualifier_group NUMBER := 0;
111
112
113 -- GLOBAL VARIABLES FROM LIST_LINES
114 G_Is_Load_Pricelist BOOLEAN;
115 G_Process_Id NUMBER;
116
117 G_item VARCHAR2(20);
118 G_listHeaderId NUMBER;
119 G_listLineId NUMBER;
120 G_Prc_Brk_Linenum NUMBER;
121 G_Prc_Brk_Hdr_Index NUMBER;
122 G_Mod_Grp NUMBER := 1;
123
124 G_Prc_Brk_Type VARCHAR2(20);
125 G_Prc_Rate_Type VARCHAR2(20);
126 G_Prc_Vol_Type VARCHAR2(20);
127 G_Prc_Line_Desc VARCHAR2(200);
128 G_Product_UOM VARCHAR2(20);
129 G_Cur_Line_Index NUMBER;
130
131
132 -- G_region_info region_rec;
133 G_region_info wsh_regions_search_pkg.region_rec;
134 G_line_number NUMBER;
135 G_region_flag VARCHAR2(20); --remember what the current region is
136 G_region_linenum NUMBER := NULL;
137 G_region_context VARCHAR2(30);
138
139 LH_NEW_RC VARCHAR2_TAB;
140 LH_REPLACE_RC NUMBER_TAB;
141
142 -------------------------------------------------------------------------------
143 --
144 -- Reset_Price_Values. Resets GLOBAL Variables
145 --
146 -------------------------------------------------------------------------------
147 PROCEDURE Reset_Price_Values;
148
149 -------------------------------------------------------------------------------
150 --
151 -- Reset_Region_Info. Resets GLOBAL record for Region. It is used by
152 -- Origin and Destination attributes
153 -------------------------------------------------------------------------------
154 PROCEDURE Reset_REGION_INFO;
155
156 -------------------------------------------------------------------------------
157 --
158 -- Reset_All. Resets All GLOBAL Variables
159 --
160 -------------------------------------------------------------------------------
161 PROCEDURE Reset_ALL;
162
163 -----------------------------------------------------------------------------
164 -- FUNCTION Get_Pricelist_Id
165 --
166 -- Purpose
167 -- Get the pricelist_id of pricelist, or -1 if the pricelist doesn't exist
168 --
169 -- IN Parameters
170 -- 1. p_name: The name of the pricelist.
171 -- 2. p_carrier_id: The carrier Id of the pricelist
172 -- RETURNS:
173 -- The pricelist Id, or -1 if the pricelist doesn't exist
174 -----------------------------------------------------------------------------
175 FUNCTION Get_Pricelist_Id (p_name IN VARCHAR2,
176 p_carrier_id IN NUMBER,
180 -----------------------------------------------------------------------------
177 p_attribute1 OUT NOCOPY VARCHAR2) RETURN NUMBER;
178
179
181 -- FUNCTION GET_RATE_CHART_INFO
182 --
183 -- Purpose: get the rate chart list header id, start date, and end date using the name and carrier id
184 --
185 -- IN Parameters
186 -- 1. p_name: The name of the pricelist.
187 -- 2. p_carrier_id: The carrier Id of the pricelist
188 --
189 -- OUT Parameters:
190 -- 1. x_status: status, -1 if no error
191 -- 2. x_error_msg: error message if any
192 -- RETURNS:
193 -- The pricelist Id, or -1 if the pricelist doesn't exist
194 -----------------------------------------------------------------------------
195 FUNCTION GET_RATE_CHART_INFO(p_name IN VARCHAR2,
196 p_carrier_id IN NUMBER,
197 x_status OUT NOCOPY NUMBER,
198 x_error_msg OUT NOCOPY VARCHAR2) RETURN STRINGARRAY;
199
200 -----------------------------------------------------------------------------
201 -- PROCEDURE: INSERT_QP_INTERFACE_TABLES
202 --
203 -- Purpose: Transfer pricelist data from the temporary tables in memory into
204 -- QP_INTERFACE_LIST_HEADERS, QP_INTERFACE_LIST_LINES,
205 -- QP_INTERFACE_QUALIFIERS and QP_INTERFACE_PRICING_ATTRIBS.
206 -- If input parameter 'p_job_id' IS NOT NULL, then the QP api
207 -- QP_PRL_LOADER_PUB(...) is also used to load the rate chart for
208 -- that particular job id. Otherwise, the data might correspond to
209 -- several rate charts, and the QP loading is done elsewhere for all
210 -- the corresponding job ids.
211 --
212 -- IN Parameters
213 -- 1. p_qp_list_header_tbl: list header pl/sql table.
214 -- 2. p_qp_list_line_tbl: list line pl/sql table.
215 -- 3. p_qp_qualifier_tbl: qualifier pl/sql table.
216 -- 4. p_qp_pricing_attrib_tbl: pricing attributes pl/sql table.
217 --
218 -- Out Parameters
219 -- 1. x_status: -1 if successful, 2 otherwise.
220 -- 2. x_error_msg: error msg if any
221 -----------------------------------------------------------------------------
222 PROCEDURE INSERT_QP_INTERFACE_TABLES(p_qp_list_header_tbl IN OUT NOCOPY qp_list_header_tbl,
223 p_qp_list_line_tbl IN OUT NOCOPY qp_list_line_tbl,
224 p_qp_qualifier_tbl IN OUT NOCOPY qp_qualifier_tbl,
225 p_qp_pricing_attrib_tbl IN OUT NOCOPY qp_pricing_attrib_tbl,
226 p_qp_call IN BOOLEAN DEFAULT TRUE,
227 x_status OUT NOCOPY NUMBER,
228 x_error_msg OUT NOCOPY VARCHAR2);
229
230 -------------------------------------------------------------------------------
231 -- PROCEDURE DELETE_FROM_QP
232 --
233 -- Purpose: delete from the qp table
234 --
235 -- IN parameters:
236 -- 1. p_list_header_id: list header id
237 -- 2. p_name: name associated with list header id
238 -- 3. p_action: delete or update
239 -- 4. p_line_number: line number
240 -- 5. p_delete_qualifier: boolean for deleting qualifier when updating, default true
241 --
242 -- OUT parameters:
243 -- 1. x_status: status, -1 if no error
244 -- 2. x_error_msg: error message if any
245 -------------------------------------------------------------------------------
246 PROCEDURE DELETE_FROM_QP(p_list_header_id IN NUMBER,
247 p_name IN VARCHAR2,
248 p_action IN VARCHAR2,
249 p_line_number IN NUMBER,
250 p_delete_qualifier IN BOOLEAN DEFAULT TRUE,
251 x_status OUT NOCOPY NUMBER,
252 x_error_msg OUT NOCOPY VARCHAR2);
253
254 -----------------------------------------------------------------------------
255 -- FUNCTION Get_Assoc_Modifiers
256 --
257 -- Purpose Get the list of modifiers associated with the rate chart
258 -- using either the rate chart's name or List Header ID.
259 --
260 -- IN Parameters
261 -- 1. p_list_header_id:
262 -- 2. p_pricelist_name:
263 --
264 -- RETURN
265 -- 1. STRINGARRAY: A list of associated modifier IDs.
266 -----------------------------------------------------------------------------
267 FUNCTION GET_ASSOC_MODIFIERS(p_list_header_id IN NUMBER,
268 p_pricelist_name IN VARCHAR2)
269 RETURN STRINGARRAY;
270
271 -----------------------------------------------------------------------------
272 -- FUNCTION GET_ASSOC_PRICELISTS
273 --
274 -- Purpose Get the list of pricelists associated with the modifier using
275 -- either the rate chart's name or List Header ID.
276 --
277 -- IN Parameters
278 -- 1. p_list_header_id:
279 -- 2. p_modifier_name:
280 --
281 -- RETURN
282 -- 1. STRINGARRAY: A list of associated pricelist IDs.
283 -----------------------------------------------------------------------------
284 FUNCTION GET_ASSOC_PRICELISTS (p_list_header_id IN NUMBER,
285 p_modifier_name IN VARCHAR2)
286 RETURN STRINGARRAY;
287
288 -----------------------------------------------------------------------------
289 -- PROCEDURE QP_API_CALL
290 --
291 -- Purpose Call the qp api for loading pricelist and modlist
292 --
293 -- IN Parameters
294 -- 1. p_chart_type: type of the load, pricelist or modlist
295 -- 2. p_process_id: process id to load
296 -- 3. p_name: name of the chart
297 -- 4. p_currency: currency of the chart
298 --
299 -- OUT paramters
300 -- 1. x_status: status, -1 for no error
301 -- 2. x_error_msg: error message if any.
302 -----------------------------------------------------------------------------
303
304 PROCEDURE QP_API_CALL(p_chart_type IN VARCHAR2,
305 p_process_id IN NUMBER,
306 p_name IN LH_NAME_TAB,
307 p_currency IN LH_CURRENCY_CODE_TAB,
308 x_status OUT NOCOPY NUMBER,
309 x_error_msg OUT NOCOPY VARCHAR2);
310 END FTE_RATE_CHART_PKG;
[Home] [Help]