1 PACKAGE BODY ONT_FREIGHT_UTIL AS
2 /* $Header: OEXUFDBB.pls 120.1 2005/06/21 02:31:03 appldev ship $ */
3
4 PROCEDURE dbms_debug(p_debug IN VARCHAR2)
5 IS
6 i INTEGER;
7 m INTEGER;
8 c INTEGER := 80;
9 BEGIN
10 m := Ceil(Length(p_debug)/c);
11 FOR i IN 1..m LOOP
12 execute immediate ('begin dbms' ||
13 '_output' ||
14 '.put_line(''' ||
15 REPLACE(Substr(p_debug, 1+c*(i-1), c), '''', '''''')||
16 '''); end;');
17 END LOOP;
18 EXCEPTION
19 WHEN OTHERS THEN
20 NULL;
21 END dbms_debug;
22
23 Procedure Freight_Debug(p_header_name In Varchar2 default null,
24 p_list_line_id In Number default null,
25 p_line_id In Number)
26 As
27 l_list_header_id Number;
28 l_list_header_name Varchar2(250);
29 l_pricing_phase_id Number;
30 l_list_line_id Number;
31 l_line_rec Oe_Order_Pub.Line_Rec_Type;
32 l_freeze_override_flag Varchar2(1);
33 l_cost_type_code Varchar2(30);
34 l_cost_amount Number;
35 l_pricing_contexts_tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
36 l_qualifier_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
37 l_found boolean default false;
38 l_dummy Varchar2(30);
39 l_inv_interfaced_flag Varchar2(1);
40 l_oe_interfaced_flag Varchar2(1);
41 j Number;
42 l_Price_Control_rec QP_PREQ_GRP.control_record_type;
43 l_return_status VARCHAR2(5);
44 l_x_line_tbl oe_order_pub.line_tbl_type;
45 l_pricing_status_code VARCHAR2(15);
46
47 Cursor list_line_info1 is
48 select b.list_header_id,
49 b.list_line_id,
50 b.list_line_type_code,
51 b.start_date_active,
52 b.end_date_active,
53 b.modifier_level_code,
54 b.pricing_phase_id,
55 b.incompatibility_grp_code,
56 b.price_break_type_code,
57 b.operand,
58 b.automatic_flag,
59 b.arithmetic_operator,
60 b.qualification_ind,
61 b.product_precedence,
62 b.price_by_formula_id
63 from qp_list_headers_vl a,
64 qp_list_lines b
65 where a.name = p_header_name
66 and a.list_header_id = b.list_header_id;
67
68 Cursor list_line_info2 is
69 select b.list_header_id,
70 b.list_line_id,
71 b.list_line_type_code,
72 b.start_date_active,
73 b.end_date_active,
74 b.modifier_level_code,
75 b.pricing_phase_id,
76 b.incompatibility_grp_code,
77 b.price_break_type_code,
78 b.operand,
79 b.automatic_flag,
80 b.arithmetic_operator,
81 b.qualification_ind,
82 b.product_precedence,
83 b.price_by_formula_id
84 From qp_list_lines b
85 where list_line_id = p_list_line_id;
86
87 Cursor pricing_attribute_info Is
88 select list_line_id
89 , list_header_id
90 , pricing_phase_id
91 , product_attribute_context
92 , product_attribute
93 , product_attr_value
94 , product_uom_code
95 , comparison_operator_code
96 , pricing_attribute_context
97 , pricing_attribute
98 , pricing_attr_value_from
99 , pricing_attr_value_to
100 , attribute_grouping_no
101 , qualification_ind
102 , excluder_flag
103 from qp_pricing_attributes
104 where list_line_id = p_list_line_id;
105
106 /*select dl.delivery_id,
107 pa.line_id,
108 pa.cost_id,
109 pa.list_line_type_code,
110 pa.adjusted_amount,
111 pa.operand
112 from oe_price_adjustments pa,
113 wsh_delivery_details dd,
114 wsh_delivery_assignments da,
115 wsh_new_deliveries dl
116 where dl.name = 'delivery_name'
117 and dl.delivery_id = da.delivery_id
118 and da.delivery_detail_id = dd.delivery_detail_id
119 and dd.source_code = 'OE'
120 and dd.source_line_id = pa.line_id
121 and pa.list_line_type_code = 'COST'; */
122
123 Cursor Other_Cost is
124 Select CHARGE_TYPE_CODE,Adjusted_amount
125 From oe_price_adjustments
126 Where line_id = l_line_rec.line_id
127 and list_line_type_code = 'COST';
128
129 Cursor formula_attribute(p_price_formula_id In Number) is
130 Select a.pricing_attribute_context,
131 a.pricing_attribute,
132 a.price_formula_line_id,
133 a.numeric_constant,
134 a.step_number,
135 a.start_date_active,
136 a.end_date_active,
137 b.name
138 From qp_price_formula_lines a,
139 qp_price_formulas_vl b
140 Where a.price_formula_id = p_price_formula_id
141 and b.price_formula_id = p_price_formula_id;
142
143 --Type list_line_info_type list_line_info1%rowtype;
144 l_list_line_info list_line_info1%rowtype;
145 l_pricing_attribute_info pricing_attribute_info%rowtype;
146 l_org_id Number;
147 Begin
148
149 Begin
150 Select org_id into l_org_id
151 From oe_order_lines_all
152 Where line_id = p_line_id;
153
154 Exception
155 When No_Data_Found Then
156 DBMS_DEBUG('Error: Invalid line id, exiting');
157 Return;
158 End;
159 --MOAC changes
160 --dbms_application_info.set_client_info(l_org_id);
161 mo_global.set_policy_context('S',l_org_id);
162 --MOAC Changes
163 --Hardcode it for now, need to revisit this later.
164 l_cost_type_code := 'FREIGHT';
165
166 If p_list_line_id is null and p_header_name is null Then
167 DBMS_DEBUG('Please enter provide Modifier header name or list line id');
168 Return;
169 End If;
170
171 If p_list_line_id is not null and p_header_name is not null Then
172 DBMS_DEBUG('Please enter either header name or list line id. Not both');
173 Return;
174 End If;
175
176 If p_list_line_id is not null Then
177 Begin
178 Open list_line_info2;
179 Fetch list_line_info2 into l_list_line_info;
180
181 Exception When Others Then
182 DBMS_DEBUG(SQLERRM);
183 End;
184 Close list_line_info2;
185 Elsif p_header_name is not null Then
186 Begin
187 Open list_line_info1;
188 Fetch list_line_info1 into l_list_line_info;
189
190 If list_line_info1%ROWCOUNT > 1 Then
191 DBMS_DEBUG('This header has multiple modifiers, please specify one by just passing list line id');
192 close list_line_info1;
193 Return;
194 End If;
195
196 Exception When Others Then
197 DBMS_DEBUG(SQLERRM);
198 End;
199 close list_line_info1;
200 End If;
201
202 /*************************************************
203 --check if there is data qp_list_header_phases
204 --if not this is a pricing bug
205 *************************************************/
206 DBMS_DEBUG('Checking for pricing bug');
207 Begin
208 Select list_header_id,
209 pricing_phase_id
210 Into l_list_header_id,l_pricing_phase_id
211 from qp_list_header_phases
212 where list_header_id = l_list_line_info.list_header_id;
213 Exception
214 when no_data_found Then
215 --check if it has line level qualifier
216 Begin
217 Select list_line_id into l_dummy
218 From qp_qualifiers
219 Where list_header_id = l_list_line_info.list_header_id
220 and nvl(list_line_id,-1) = l_list_line_id
221 and rownum = 1;
222
223 DBMS_DEBUG(' Oracle Pricing bugs.');
224 DBMS_DEBUG(' Please apply pricing patch 1806021 if this is an upgrade');
225 DBMS_DEBUG(' Otherwise apply 1797603');
226
227 Exception
228 When no_data_found then null;
229 DBMS_DEBUG(' Passed');
230 End;
231 when too_many_rows Then
232 DBMS_DEBUG(' Passed');
233 Null;
234 when others Then
235 DBMS_DEBUG(SQLERRM);
236 End;
237
238 DBMS_DEBUG('--------------------------');
239 /*******************************************
240 --check if the freeze_override_flag set to Y
241 ********************************************/
242 Begin
243 select a.freeze_override_flag
244 into l_freeze_override_flag
245 from qp_pricing_phases a, qp_event_phases b
246 where a.pricing_phase_id = b.pricing_phase_id
247 and b.pricing_event_code='SHIP'
248 and a.pricing_phase_id =l_list_line_info.pricing_phase_id;
249
250 If l_freeze_override_flag Is Null or l_freeze_override_flag = 'N' Then
251 DBMS_DEBUG(' Freeze override flag for SHIP event and phase id '||l_list_line_info.pricing_phase_id ||'is ''N'' or nulll');
252 DBMS_DEBUG(' Please contact Oracle Pricing to fix this problem');
253 End If;
254
255 Exception
256 When no_data_found then
257 DBMS_DEBUG(SQLERRM||':Event phases check. Please make sure your the pricing phase for your modifier has been associated to SHIP event');
258 When Others then
259 DBMS_DEBUG(SQLERRM||':Event phases');
260 End;
261
262 --query line and header record
263 --Set org?
264 oe_line_util.query_row(p_line_id,l_line_rec);
265
266 If l_line_rec.line_id is null Then
267 DBMS_DEBUG('Invalid line id or incorrect org_id');
268 Return;
269 End If;
270
271
272 /*****************************
273 --testing qp attribute mapping
274 ******************************/
275 DBMS_DEBUG('Checking if attributes sourced by Pricing');
276 OE_Order_Pub.G_Line := l_line_rec;
277
278 Begin
279 QP_Attr_Mapping_PUB.Build_Contexts(p_request_type_code => 'ONT',
280 p_pricing_type => 'L',
281 x_price_contexts_result_tbl => l_pricing_contexts_Tbl,
282 x_qual_contexts_result_tbl => l_qualifier_Contexts_Tbl);
283
284 Exception when others then
285 DBMS_DEBUG('QP Attribute mapping:'||SQLERRM);
286 End;
287 OE_Order_Pub.G_Line := NULL;
288
289 --Test if attribute mapping sorces required pricing attributes
290 For i in pricing_attribute_info Loop
291 l_found:=false;
292 DBMS_DEBUG('Check if attribute mapping sources:'||i.pricing_attribute_context||','||i.pricing_attribute||','||i.pricing_attr_value_from);
293
294 j := l_pricing_contexts_tbl.first;
295 While j is not null Loop
296 if i.pricing_attribute_context = l_pricing_contexts_tbl(j).context_name
297 and i.pricing_attribute = l_pricing_contexts_tbl(j).attribute_name Then
298 DBMS_DEBUG(' This attribute is sourced with value:'||l_pricing_contexts_tbl(j).attribute_value);
299 l_found := True;
300 exit;
301 End If;
302 j:= l_pricing_contexts_tbl.next(j);
303 End Loop;
304
305
306 If not l_found Then
307 DBMS_DEBUG(' This attribute did not get sourced. The caused could be:');
308 DBMS_DEBUG(' 1. You have not run QP build sourcing concurent program');
309 DBMS_DEBUG(' 2. The cost record was not passed to OM');
310 Else
311 DBMS_DEBUG('Passed');
312 End If;
313
314 End Loop;
315
316 DBMS_DEBUG('Attribute setup in for formula id:'||l_list_line_info.price_by_formula_id);
317 --DBMS_DEBUG('Formula Name:
318 For f In formula_attribute(l_list_line_info.price_by_formula_id) Loop
319 DBMS_DEBUG(' Context:'||f. pricing_attribute_context);
320 DBMS_DEBUG(' Pricing Attribute:'||f.pricing_attribute);
321 DBMS_DEBUG(' Numeric Constant:'||f.numeric_constant);
322 End Loop;
323
324 DBMS_DEBUG('Attribute sourced by QP_ATTRIBUTE_MAPPING:');
325 j := l_pricing_contexts_tbl.first;
326 While j is not null Loop
327 DBMS_DEBUG(' Context sourced:'||l_pricing_contexts_tbl(j).context_name);
328 DBMS_DEBUG(' Attribute sourced:'||l_pricing_contexts_tbl(j).attribute_name);
329 j:= l_pricing_contexts_tbl.next(j);
330 End Loop;
331
332 DBMS_DEBUG('--------------------------------');
333
334 /*********************************
335 --Check the pricing quantity of the line
336 **********************************/
337 DBMS_DEBUG('Checking pricing_quantity of the line');
338 If l_line_rec.pricing_quantity < 0 or l_line_rec.pricing_quantity = FND_API.G_MISS_NUM Then
339 DBMS_DEBUG(' Error: Invalid pricing quantity:'|| l_line_rec.pricing_quantity);
340 Return;
341 Else
342 DBMS_DEBUG('Passed');
343 DBMS_DEBUG('-------------');
344 End If;
345
346 /*********************************
347 --check if this is a shippable line
348 **********************************/
349 DBMS_DEBUG('Checking if the line is shippable');
350 If l_line_rec.shippable_flag = 'N' or nvl(l_line_rec.shipped_quantity,-999) <= 0 Then
351 DBMS_DEBUG(' Error: Either this line is not shippable or has not been ship confirmed');
352 DBMS_DEBUG(' Shippable flag:'||l_line_rec.shippable_flag);
353 DBMS_DEBUG(' Shipped Qty:'||l_line_rec.shipped_quantity);
354 Return;
355 Else
356 DBMS_DEBUG('Passed');
357 DBMS_DEBUG('-------------');
358 End If;
359
360 /*********************************
361 --check calculate_price_flag
362 **********************************/
363 DBMS_DEBUG('Checking if the calculate price is set to either ''Partial'' or ''Yes''');
364 If nvl(l_line_rec.calculate_price_flag,'N') = 'N' Then
365 DBMS_DEBUG(' Error: The calculate price flag is set to ''No''. Freight charge will not come');
366 --Return;
367 Else
368 DBMS_DEBUG('Passed');
369 DBMS_DEBUG('-------------');
370 End If;
371
372
373 /*************************************************
374 --Check if this line has been inventory interfaced
375 **************************************************/
376 DBMS_DEBUG('Checking if the line has been inventory interfaced');
377 Begin
378 Select inv_interfaced_flag, oe_interfaced_flag
379 Into l_inv_interfaced_flag,l_oe_interfaced_flag
380 From wsh_delivery_details
381 Where source_line_id = l_line_rec.line_id;
382
383 If nvl(l_inv_interfaced_flag,'X') <> 'Y' Then
384 DBMS_DEBUG(' This line has not been inventory interfaced. Shipping could not happen before inventory interface. Please check with inventory about this issue.');
385 DBMS_DEBUG(' inv_interfaced_flag:'||l_inv_interfaced_flag);
386 --Return;
387 Else
388 DBMS_DEBUG(' Passed');
389 DBMS_DEBUG(' This line has been inventory interfaced');
390 End If;
391
392 Exception
393 When no_data_found Then
394 DBMS_DEBUG('Line Id:'||l_line_rec.line_id||' can not be found in wsh_delivery_details');
395 Return;
396 When Others Then
397 DBMS_DEBUG(SQLERRM);
398 End;
399 DBMS_DEBUG('-------------------------');
400 /***************************************************
401 --Check if this line has been om interfaced
402 ****************************************************/
403 DBMS_DEBUG('Checking if the line has been OM interfaced');
404 DBMS_DEBUG(' oe_interfaced_flag:'||l_oe_interfaced_flag);
405
406 DBMS_DEBUG('-------------------------');
407 /************************************************
408 --check if cost record have been inserted into OM
409 *************************************************/
410 DBMS_DEBUG('Checking if freight cost has been passed to OM');
411
412 -- Cost records are stored in OE_PRICE_ADJUSTMENTS table with
413 -- list_line_type_code = 'COST'
414 Begin
415 SELECT SUM(ADJUSTED_AMOUNT)
416 INTO l_cost_amount
417 FROM OE_PRICE_ADJUSTMENTS_V
418 WHERE LINE_ID = l_line_rec.line_id
419 AND LIST_LINE_TYPE_CODE = 'COST'
420 AND CHARGE_TYPE_CODE = l_cost_type_code;
421 If l_cost_amount is not Null Then
422 DBMS_DEBUG(' Passed. Cost record inserted with value:'||l_cost_amount);
423 Else
424 DBMS_DEBUG(' Error: Freight Cost record is not passed by Shipping or you have not entered the freight cost');
425 End If;
426
427 Exception
428 When No_Data_Found Then
429 DBMS_DEBUG(' Error: Freight Cost record is not passed by Shipping or you have not entered the freight cost');
430 End;
431
432 For i in Other_Cost Loop
433 DBMS_DEBUG(' Inserted charge cost type in OM:'||i.charge_type_code);
434 DBMS_DEBUG(' Cost Amount:'||i.adjusted_amount);
435 End Loop;
436
437 /*****************************************************************************
438 Check for automatic flag, It must set to Y in order for cost to charge to work
439 ******************************************************************************/
440 DBMS_DEBUG('----------------------------------');
441 DBMS_DEBUG('Check if automatic flag set to ''Y'' ');
442 If l_list_line_info.automatic_flag = 'N' Then
443 DBMS_DEBUG('Error: You have set your modifier to None automatic (manual)');
444 DBMS_DEBUG(' Please go to modifier form and check automatic check box to make it automatic');
445 Else
446 DBMS_DEBUG('Passed');
447 End If;
448
449 DBMS_DEBUG('----------------------------------');
450 /*********************************************************************************************
451 Simulating pricing engine call to make sure pricing engine returns this freight charge modifier
452 **********************************************************************************************/
453 DBMS_DEBUG('Testing if pricing engine returns this modifier');
454 l_Price_Control_Rec.pricing_event := 'SHIP';
455 l_Price_Control_Rec.calculate_flag := QP_PREQ_GRP.G_SEARCH_N_CALCULATE;
456 l_Price_Control_Rec.Simulation_Flag := 'N';
457 oe_order_Adj_pvt.Price_line(X_Return_Status => l_Return_Status
458 ,p_header_id => l_line_rec.header_id
459 ,p_Request_Type_code=> 'ONT'
460 ,p_Control_rec => l_Price_Control_Rec
461 ,p_Write_To_Db => FALSE
462 ,x_Line_Tbl => l_x_Line_Tbl);
463 Begin
464 Select pricing_status_code
465 into l_pricing_status_code
466 From qp_preq_ldets_tmp
467 Where created_from_list_line_id = l_list_line_info.list_line_id;
468
469 If l_pricing_status_code <> QP_PREQ_GRP.G_STATUS_NEW Then
470 DBMS_DEBUG('Error: Oracle Pricing did not find this modifier line:'||l_list_line_info.list_line_id);
471 DBMS_DEBUG(' : Pricing status code from pricing engine is:'||l_pricing_status_code);
472 Else
473 DBMS_DEBUG('Passed');
474 End If;
475
476 Exception when no_data_found Then
477 DBMS_DEBUG('Error: Oracle Pricing Engine does not return this modifier, please contact Pricing');
478 DBMS_DEBUG('Please run $QP_TOP/patch/115/sql/qp_list_line_detail.sql and provide the output to Pricing');
479 when others Then
480 DBMS_DEBUG('Pricing simulation:'||SQLERRM);
481 End;
482
483 End;
484
485 END ONT_FREIGHT_UTIL;
[Home] [Help]