1 PACKAGE FTE_QP_ENGINE AUTHID CURRENT_USER as
2 /* $Header: FTEFRQPS.pls 115.10 2003/11/14 01:51:43 vphalak ship $ */
3
4 -- This package encapsulates all qp engine related methods and data structures.
5 -- It provides several utility packages to create engine input records.
6 -- It will also hold engine i/p and o/p in global tables per event.
7
8 /*
9 TYPE pricing_control_input_rec_type IS RECORD (
10 pricing_event_num NUMBER,
11 currency_code VARCHAR2(30),
12 lane_id NUMBER,
13 price_list_id NUMBER,
14 party_id NUMBER
15 );
16 */
17
18 TYPE pricing_engine_def_rec_type IS RECORD (
19 pricing_event_num NUMBER, --index
20 pricing_event_code VARCHAR2(30),
21 request_type_code VARCHAR2(30),
22 line_type_code VARCHAR2(30),
23 price_flag VARCHAR2(1)
24 );
25
26 TYPE pricing_engine_def_tab_type IS TABLE OF pricing_engine_def_rec_type INDEX BY BINARY_INTEGER;
27
28 -- brought over from freight pricing. Contains some changes
29 /*
30 TYPE pricing_engine_input_rec_type IS RECORD
31 (input_index NUMBER , -- Same as QP engine line_index ?
32 instance_index NUMBER , -- Origin pricing dual instance. Can be more than one input rec only in case of pricing objective consideration/percel hundredwt.
33 category_id NUMBER DEFAULT NULL, -- Populated for WITHIN
34 basis NUMBER DEFAULT NULL, -- Populated for ACROSS
35 line_quantity NUMBER ,
36 line_uom VARCHAR2(60) ,
37 input_set_number NUMBER DEFAULT 1 -- indentifies an input set (for stuff like parcel hundred wt)
38 );
39
40 TYPE pricing_engine_input_tab_type IS TABLE OF pricing_engine_input_rec_type INDEX BY BINARY_INTEGER;
41 */
42
43
44 /*
45 -- brought over from freight pricing. Contains some changes
46 TYPE pricing_attribute_rec_type IS RECORD
47 (attribute_index NUMBER ,
48 input_index NUMBER , -- Origin QP engine input line index
49 attribute_name VARCHAR2(60) ,
50 attribute_value VARCHAR2(240),
51 attribute_value_to VARCHAR2(240) DEFAULT NULL
52 );
53
54 TYPE pricing_attribute_tab_type IS TABLE OF pricing_attribute_rec_type INDEX BY BINARY_INTEGER;
55 */
56
57 TYPE qualifier_rec_type IS RECORD
58 (qualifier_index NUMBER ,
59 input_index NUMBER , -- Origin QP engine input line index
60 qualifier_name VARCHAR2(60) ,
61 qualifier_value VARCHAR2(240),
62 qualifier_value_to VARCHAR2(240) DEFAULT NULL,
63 operator VARCHAR2(30) DEFAULT '='
64 );
65
66 -- This type stores additional stuff that should go along with a qp line_rec
67 -- mainly used to associate set number to a line rec
68 TYPE line_extras_rec IS RECORD
69 ( line_index NUMBER,
70 input_set_number NUMBER,
71 category_id NUMBER
72 );
73 -- This table will have one record per input line rec
74 TYPE line_extras_tab_type IS TABLE OF line_extras_rec INDEX BY BINARY_INTEGER;
75
76
77 TYPE commodity_price_rec_type IS RECORD (
78 category_id NUMBER, --index
79 unit_price NUMBER,
80 total_wt NUMBER,
81 priced_uom VARCHAR2(30), -- AG 5/12
82 output_line_index NUMBER, -- AG 5/13
83 output_line_priced_quantity NUMBER, -- xizhang 11/22/02 in original line uom
84 wt_uom VARCHAR2(30) ); -- This will always be in deficit wt. uom
85
86 TYPE commodity_price_tbl_type IS TABLE OF commodity_price_rec_type INDEX BY BINARY_INTEGER;
87
88
89 -- Parcel output conditions
90 G_PAR_NO_MP_PRICE NUMBER := 1; -- singlepiece all line successful, multipiece all line ipl
91 G_PAR_NO_SP_PRICE NUMBER := 2; -- singlepiece all line ipl or parcial ipl, multipiece all line successful
92
93
94
95 -- pricing events
96 G_LINE_EVENT_NUM NUMBER := 1;
97 G_CHARGE_EVENT_NUM NUMBER := 2;
98 --G_LINE_EVENT_CODE VARCHAR2(30) := 'LINE';
99 --G_CHARGE_EVENT_CODE VARCHAR2(30) := 'PRICE_LOAD'; -- should have a proper value (say FTE_CHARGE_EVENT)
100 G_LINE_EVENT_CODE VARCHAR2(30) := 'FTE_PRICE_LINE';
101 G_CHARGE_EVENT_CODE VARCHAR2(30) := 'FTE_APPLY_MOD'; -- should have a proper value (say FTE_CHARGE_EVENT)
102
103 G_EXTRAS_OFFSET NUMBER := 100000; -- offset that is used to lookup into the extras tbl
104
105
106 -- input to QP
107 G_I_LINE_INDEX QP_PREQ_GRP.PLS_INTEGER_TYPE;
108 G_I_LINE_TYPE_CODE QP_PREQ_GRP.VARCHAR_TYPE;
109 G_I_PRICING_EFFECTIVE_DATE QP_PREQ_GRP.DATE_TYPE ;
110 G_I_ACTIVE_DATE_FIRST QP_PREQ_GRP.DATE_TYPE ;
111 G_I_ACTIVE_DATE_FIRST_TYPE QP_PREQ_GRP.VARCHAR_TYPE;
112 G_I_ACTIVE_DATE_SECOND QP_PREQ_GRP.DATE_TYPE ;
113 G_I_ACTIVE_DATE_SECOND_TYPE QP_PREQ_GRP.VARCHAR_TYPE ;
114 G_I_LINE_QUANTITY QP_PREQ_GRP.NUMBER_TYPE ;
115 G_I_LINE_UOM_CODE QP_PREQ_GRP.VARCHAR_TYPE;
116 G_I_REQUEST_TYPE_CODE QP_PREQ_GRP.VARCHAR_TYPE;
117 G_I_PRICED_QUANTITY QP_PREQ_GRP.NUMBER_TYPE;
118 G_I_PRICED_UOM_CODE QP_PREQ_GRP.VARCHAR_TYPE;
119 G_I_CURRENCY_CODE QP_PREQ_GRP.VARCHAR_TYPE;
120 G_I_UNIT_PRICE QP_PREQ_GRP.NUMBER_TYPE;
121 G_I_PERCENT_PRICE QP_PREQ_GRP.NUMBER_TYPE;
122 G_I_UOM_QUANTITY QP_PREQ_GRP.NUMBER_TYPE;
123 G_I_ADJUSTED_UNIT_PRICE QP_PREQ_GRP.NUMBER_TYPE;
124 G_I_UPD_ADJUSTED_UNIT_PRICE QP_PREQ_GRP.NUMBER_TYPE;
125 G_I_PROCESSED_FLAG QP_PREQ_GRP.VARCHAR_TYPE;
126 G_I_PRICE_FLAG QP_PREQ_GRP.VARCHAR_TYPE;
127 G_I_LINE_ID QP_PREQ_GRP.NUMBER_TYPE;
128 G_I_PROCESSING_ORDER QP_PREQ_GRP.PLS_INTEGER_TYPE;
129 G_I_PRICING_STATUS_CODE QP_PREQ_GRP.VARCHAR_TYPE;
130 G_I_PRICING_STATUS_TEXT QP_PREQ_GRP.VARCHAR_TYPE;
131 G_I_ROUNDING_FLAG QP_PREQ_GRP.FLAG_TYPE;
132 G_I_ROUNDING_FACTOR QP_PREQ_GRP.PLS_INTEGER_TYPE;
133 G_I_QUALIFIERS_EXIST_FLAG QP_PREQ_GRP.VARCHAR_TYPE;
134 G_I_PRICING_ATTRS_EXIST_FLAG QP_PREQ_GRP.VARCHAR_TYPE;
135 G_I_PRICE_LIST_ID QP_PREQ_GRP.NUMBER_TYPE;
136 G_I_VALIDATED_FLAG QP_PREQ_GRP.VARCHAR_TYPE;
137 G_I_PRICE_REQUEST_CODE QP_PREQ_GRP.VARCHAR_TYPE;
138 G_I_USAGE_PRICING_TYPE QP_PREQ_GRP.VARCHAR_TYPE;
139 G_I_LINE_CATEGORY QP_PREQ_GRP.VARCHAR_TYPE;
140
141 G_I_A_LINE_INDEX QP_PREQ_GRP.PLS_INTEGER_TYPE;
142 G_I_A_LINE_DETAIL_INDEX QP_PREQ_GRP.PLS_INTEGER_TYPE;
143 G_I_A_ATTRIBUTE_LEVEL QP_PREQ_GRP.VARCHAR_TYPE;
144 G_I_A_ATTRIBUTE_TYPE QP_PREQ_GRP.VARCHAR_TYPE;
145 G_I_A_LIST_HEADER_ID QP_PREQ_GRP.NUMBER_TYPE;
146 G_I_A_LIST_LINE_ID QP_PREQ_GRP.NUMBER_TYPE;
147 G_I_A_CONTEXT QP_PREQ_GRP.VARCHAR_TYPE;
148 G_I_A_ATTRIBUTE QP_PREQ_GRP.VARCHAR_TYPE;
149 G_I_A_VALUE_FROM QP_PREQ_GRP.VARCHAR_TYPE;
150 G_I_A_SETUP_VALUE_FROM QP_PREQ_GRP.VARCHAR_TYPE;
151 G_I_A_VALUE_TO QP_PREQ_GRP.VARCHAR_TYPE;
152 G_I_A_SETUP_VALUE_TO QP_PREQ_GRP.VARCHAR_TYPE;
153 G_I_A_GROUPING_NUMBER QP_PREQ_GRP.PLS_INTEGER_TYPE;
154 G_I_A_NO_QUALIFIERS_IN_GRP QP_PREQ_GRP.PLS_INTEGER_TYPE;
155 G_I_A_COMPARISON_OPERATOR_TYPE QP_PREQ_GRP.VARCHAR_TYPE;
156 G_I_A_VALIDATED_FLAG QP_PREQ_GRP.VARCHAR_TYPE;
157 G_I_A_APPLIED_FLAG QP_PREQ_GRP.VARCHAR_TYPE;
158 G_I_A_PRICING_STATUS_CODE QP_PREQ_GRP.VARCHAR_TYPE;
159 G_I_A_PRICING_STATUS_TEXT QP_PREQ_GRP.VARCHAR_TYPE;
160 G_I_A_QUALIFIER_PRECEDENCE QP_PREQ_GRP.PLS_INTEGER_TYPE;
161 G_I_A_DATATYPE QP_PREQ_GRP.VARCHAR_TYPE;
162 G_I_A_PRICING_ATTR_FLAG QP_PREQ_GRP.VARCHAR_TYPE;
163 G_I_A_QUALIFIER_TYPE QP_PREQ_GRP.VARCHAR_TYPE;
164 G_I_A_PRODUCT_UOM_CODE QP_PREQ_GRP.VARCHAR_TYPE;
165 G_I_A_EXCLUDER_FLAG QP_PREQ_GRP.VARCHAR_TYPE;
166 G_I_A_PRICING_PHASE_ID QP_PREQ_GRP.PLS_INTEGER_TYPE;
167 G_I_A_INCOMPATABILITY_GRP_CODE QP_PREQ_GRP.VARCHAR_TYPE;
168 G_I_A_LINE_DETAIL_TYPE_CODE QP_PREQ_GRP.VARCHAR_TYPE;
169 G_I_A_MODIFIER_LEVEL_CODE QP_PREQ_GRP.VARCHAR_TYPE;
170 G_I_A_PRIMARY_UOM_FLAG QP_PREQ_GRP.VARCHAR_TYPE;
171
172 g_I_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
173 g_I_line_extras_tbl line_extras_tab_type;
174
175 -- output from QP
176 g_O_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
177 g_O_line_detail_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
178
179 -- stores defaults for each pricing event
180 --g_engine_defaults_tab pricing_engine_def_tab_type;
181
182
183
184 -- we need procedures to :
185 -- create the control record
186 -- create line record
187 -- create attribute record
188 -- create qualifier record
189
190 -- creates a control record based on defaults and adds it to the input table for the event
191 PROCEDURE create_control_record (p_event_num IN NUMBER,
192 x_return_status OUT NOCOPY VARCHAR2);
193
194 -- creates a single line record and adds it to the event input table
195 PROCEDURE create_line_record (p_pricing_control_rec IN fte_freight_pricing.pricing_control_input_rec_type,
196 p_pricing_engine_input_rec IN fte_freight_pricing.pricing_engine_input_rec_type,
197 x_return_status OUT NOCOPY VARCHAR2);
198
199 -- creates a single qualifier record and adds it to the appropriate i/p table
200 PROCEDURE create_qual_record (p_event_num IN NUMBER,
201 p_qual_rec IN qualifier_rec_type,
202 x_return_status OUT NOCOPY VARCHAR2);
203
204 -- creates a single attribute record and adds it to the appropriate i/p table
205 PROCEDURE create_attr_record (p_event_num IN NUMBER,
206 p_attr_rec IN fte_freight_pricing.pricing_attribute_rec_type,
207 x_return_status OUT NOCOPY VARCHAR2);
208
209 -- This procedure is called to create pricing attributes per line rec from the input attr rows
210 PROCEDURE prepare_qp_line_attributes (
211 p_event_num IN NUMBER,
212 p_input_index IN NUMBER,
213 p_attr_rows IN fte_freight_pricing.pricing_attribute_tab_type,
214 x_return_status OUT NOCOPY VARCHAR2 );
215
216 PROCEDURE prepare_qp_line_qualifiers (p_event_num IN NUMBER,
217 p_pricing_control_rec IN fte_freight_pricing.pricing_control_input_rec_type,
218 p_input_index IN NUMBER,
219 x_return_status OUT NOCOPY VARCHAR2 );
220
221
222 -- add one qp output line detail record into qp output line detail table
223 -- most of the qp output should come directly from qp
224 -- since qp cannot handle all of FTE pricing reqirement (e.g. deficit weight for LTL)
225 -- In some cases, FTE pricing engine needs to add some more records into
226 -- qp output tables
227 PROCEDURE add_qp_output_detail(
228 p_line_index IN NUMBER,
229 p_list_line_type_code IN VARCHAR2,
230 p_charge_subtype_code IN VARCHAR2,
231 p_adjustment_amount IN NUMBER,
232 x_return_status OUT NOCOPY VARCHAR2);
233
234 --PROCEDURE call_qp_api ( p_event_num IN NUMBER,
235 -- x_return_status OUT VARCHAR2);
236
237 PROCEDURE call_qp_api ( x_qp_output_line_rows OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
238 x_qp_output_detail_rows OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
239 x_return_status OUT NOCOPY VARCHAR2);
240
241
242 -- return the pointer to the qp outputs
243 PROCEDURE get_qp_output(
244 x_qp_output_line_rows OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
245 x_qp_output_detail_rows OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
246 x_return_status OUT NOCOPY VARCHAR2);
247
248 -- other utility methods ---
249
250 -- this procedure calculates the total base price of a shipment, for a given set.
251 -- multiplies unit price by line quantity
252 -- price is in the priced currency
253
254 PROCEDURE get_total_base_price (p_set_num IN NUMBER DEFAULT 1,
255 -- x_priced_currency OUT NUMBER,
256 x_price OUT NOCOPY NUMBER,
257 x_return_status OUT NOCOPY VARCHAR2);
258
259 -- prorate (apply) new charge across engine output lines by ratio of current unit_price to current total unit price?
260 -- ( it could also be by ratio of current line amount to current total base price)
261 -- assumes that the new price is in the priced currency.
262 PROCEDURE apply_new_base_price (p_set_num IN NUMBER DEFAULT 1,
263 p_new_total_price IN NUMBER,
264 x_return_status OUT NOCOPY VARCHAR2);
265
266
267 -- copies input lines of one event to the input of another event
268 -- the base prices from the source event are carried over to the input of the target event
269 -- currently it will copy only from event 1 to event 2
270 PROCEDURE prepare_next_event_request ( x_return_status OUT NOCOPY VARCHAR2);
271
272 -- get me unit price for each individual commodity for each set
273 -- get me total wt. for each individual commodity
274 -- give me all weights in the deficit wt uom
275 -- currently we have implementation only for event num =1
276 PROCEDURE analyse_output_for_deficit_wt (p_set_num IN NUMBER,
277 p_wt_uom IN VARCHAR2,
278 x_commodity_price_rows OUT NOCOPY commodity_price_tbl_type,
279 x_return_status OUT NOCOPY VARCHAR2);
280
281 -- delete a set from the input and output lines for event 1
282 PROCEDURE delete_set_from_line_event(p_set_num IN NUMBER,
283 x_return_status OUT NOCOPY VARCHAR2);
284
285
286
287 -- delete from event tables for the specified line_index
288 --PROCEDURE delete_lines(p_event_num IN NUMBER DEFAULT 1,
289 -- p_line_index IN NUMBER,
290 -- x_return_status OUT VARCHAR2);
291
292 PROCEDURE delete_lines(p_line_index IN NUMBER,
293 x_qp_output_line_rows IN OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
294 x_qp_output_detail_rows IN OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
295 x_return_status OUT NOCOPY VARCHAR2);
296
297 -- clear qp input line table, line extra table, attribute/qualifier tabl
298 PROCEDURE clear_qp_input(x_return_status OUT NOCOPY VARCHAR2);
299
300 --debug methods
301
302 PROCEDURE print_qp_input;
303
304 PROCEDURE print_qp_output;
305
306 PROCEDURE check_qp_output_errors (x_return_status OUT NOCOPY VARCHAR2);
307
308 PROCEDURE check_tl_qp_output_errors (x_return_status OUT NOCOPY VARCHAR2);
309
310 PROCEDURE check_parcel_output_errors (p_event_num IN NUMBER,
311 x_return_code OUT NOCOPY NUMBER,
312 x_return_status OUT NOCOPY VARCHAR2);
313
314 PROCEDURE clear_globals (
315 x_return_status OUT NOCOPY VARCHAR2);
316
317 END FTE_QP_ENGINE;
[Home] [Help]