[Home] [Help]
PACKAGE BODY: APPS.ASO_VALIDATE_PRICING_PVT
Source
1 PACKAGE BODY ASO_VALIDATE_PRICING_PVT as
2 /* $Header: asovvprb.pls 120.3 2005/10/26 04:17:59 gsachdev ship $ */
3 -- Start of Comments
4 -- Package name : ASO_VALIDATE_PRICING_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_VALIDATE_PRICING_PVT';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asovvprb.pls';
12
13
14 /*local functions and procedures*/
15
16 PROCEDURE Copy_Attribs_To_Req(
17 p_line_index number,
18 p_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type,
19 p_qualifier_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type,
20 px_Req_line_attr_tbl IN OUT NOCOPY /* file.sql.39 change */ QP_PREQ_GRP.LINE_ATTR_TBL_TYPE,
21 px_Req_qual_tbl IN OUT NOCOPY /* file.sql.39 change */ QP_PREQ_GRP.QUAL_TBL_TYPE)
22 IS
23 l_attr_index number := nvl(px_Req_line_attr_tbl.last,0);
24 l_qual_index number := nvl(px_Req_qual_tbl.last,0);
25 BEGIN
26 for i in 1..p_pricing_contexts_Tbl.count loop
27 l_attr_index := l_attr_index +1;
28 px_Req_line_attr_tbl(l_attr_index).VALIDATED_FLAG := 'N';
29 px_Req_line_attr_tbl(l_attr_index).line_index := p_line_index;
30 -- Product and Pricing Contexts go into pricing contexts...
31 px_Req_line_attr_tbl(l_attr_index).PRICING_CONTEXT :=
32 p_pricing_contexts_Tbl(i).context_name;
33 px_Req_line_attr_tbl(l_attr_index).PRICING_ATTRIBUTE :=
34 p_pricing_contexts_Tbl(i).Attribute_Name;
35 px_Req_line_attr_tbl(l_attr_index).PRICING_ATTR_VALUE_FROM :=
36 p_pricing_contexts_Tbl(i).attribute_value;
37 end loop;
38 -- Copy the qualifiers
39 for i in 1..p_qualifier_contexts_Tbl.count loop
40 l_qual_index := l_qual_index +1;
41 px_Req_qual_tbl(l_qual_index).VALIDATED_FLAG := 'N';
42 px_Req_qual_tbl(l_qual_index).line_index := p_line_index;
43 px_Req_qual_tbl(l_qual_index).QUALIFIER_CONTEXT :=
44 p_qualifier_contexts_Tbl(i).context_name;
45 px_Req_qual_tbl(l_qual_index).QUALIFIER_ATTRIBUTE :=
46 p_qualifier_contexts_Tbl(i).Attribute_Name;
47 px_Req_qual_tbl(l_qual_index).QUALIFIER_ATTR_VALUE_FROM :=
48 p_qualifier_contexts_Tbl(i).attribute_value;
49 end loop;
50 end copy_attribs_to_Req;
51
52 PROCEDURE Copy_hdr_attr_to_line(
53 p_line_index number,
54 p_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type,
55 p_qualifier_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type,
56 px_Req_line_attr_tbl IN OUT NOCOPY /* file.sql.39 change */ QP_PREQ_GRP.LINE_ATTR_TBL_TYPE,
57 px_Req_qual_tbl IN OUT NOCOPY /* file.sql.39 change */ QP_PREQ_GRP.QUAL_TBL_TYPE)
58 IS
59 l_attr_index number := nvl(px_Req_line_attr_tbl.last,0);
60 l_qual_index number := nvl(px_Req_qual_tbl.last,0);
61 copy_hdr_rec_to_line_flag boolean := TRUE;
62 BEGIN
63 for i in 1..p_pricing_contexts_Tbl.count loop
64 l_attr_index := l_attr_index +1;
65 px_Req_line_attr_tbl(l_attr_index).VALIDATED_FLAG := 'N';
66 px_Req_line_attr_tbl(l_attr_index).line_index := p_line_index;
67 -- Product and Pricing Contexts go into pricing contexts...
68 px_Req_line_attr_tbl(l_attr_index).PRICING_CONTEXT :=
69 p_pricing_contexts_Tbl(i).context_name;
70 px_Req_line_attr_tbl(l_attr_index).PRICING_ATTRIBUTE :=
71 p_pricing_contexts_Tbl(i).Attribute_Name;
72 px_Req_line_attr_tbl(l_attr_index).PRICING_ATTR_VALUE_FROM :=
73 p_pricing_contexts_Tbl(i).attribute_value;
74 end loop;
75 -- Copy the qualifiers
76 for i in 1..p_qualifier_contexts_Tbl.count loop
77 copy_hdr_rec_to_line_flag := TRUE;
78 for j in 1..px_Req_qual_tbl.count loop
79 if px_Req_qual_tbl(j).QUALIFIER_CONTEXT = p_qualifier_contexts_Tbl(i).context_name
80 and px_Req_qual_tbl(j).QUALIFIER_ATTRIBUTE = p_qualifier_contexts_Tbl(i).Attribute_Name
81 and p_line_index = px_Req_qual_tbl(j).line_index
82 then
83 copy_hdr_rec_to_line_flag := FALSE;
84 end if;
85 end loop;
86
87 if copy_hdr_rec_to_line_flag then
88 l_qual_index := l_qual_index +1;
89 px_Req_qual_tbl(l_qual_index).VALIDATED_FLAG := 'N';
90 px_Req_qual_tbl(l_qual_index).line_index := p_line_index;
91 px_Req_qual_tbl(l_qual_index).QUALIFIER_CONTEXT :=
92 p_qualifier_contexts_Tbl(i).context_name;
93 px_Req_qual_tbl(l_qual_index).QUALIFIER_ATTRIBUTE :=
94 p_qualifier_contexts_Tbl(i).Attribute_Name;
95 px_Req_qual_tbl(l_qual_index).QUALIFIER_ATTR_VALUE_FROM :=
96 p_qualifier_contexts_Tbl(i).attribute_value;
97 end if;
98 end loop;
99 end Copy_hdr_attr_to_line;
100
101 procedure copy_Header_to_request(
102 p_Request_Type VARCHAR2,
103 p_pricing_event VARCHAR2,
104 p_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type,
105 px_req_line_tbl IN OUT NOCOPY /* file.sql.39 change */ QP_PREQ_GRP.LINE_TBL_TYPE)
106 IS
107 BEGIN
108 px_req_line_tbl(1).REQUEST_TYPE_CODE := p_Request_Type;
109 px_req_line_tbl(1).PRICING_EVENT := p_pricing_event;
110 px_req_line_tbl(1).LINE_INDEX := 1;
111 px_req_line_tbl(1).LINE_TYPE_CODE := 'ORDER';
112 px_req_line_tbl(1).CURRENCY_CODE := p_Header_rec.currency_code;
113 px_req_line_tbl(1).PRICE_FLAG := 'Y';
114 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
115 aso_debug_pub.add('ASO_PRICING_INT.g_header_rec.PRICE_FROZEN_DATE '
116 ||ASO_PRICING_INT.g_header_rec.PRICE_FROZEN_DATE,1,'N');
117 END IF;
118 /*FastTrak: Price effective date is assigned to the price frozen unless the price frozen date is null*/
119 if ASO_PRICING_INT.g_header_rec.PRICE_FROZEN_DATE = FND_API.G_MISS_DATE
120 OR ASO_PRICING_INT.g_header_rec.PRICE_FROZEN_DATE is NULL then
121 px_req_line_tbl(1).PRICING_EFFECTIVE_DATE := trunc(sysdate);
122 else
123 px_req_line_tbl(1).PRICING_EFFECTIVE_DATE := trunc(ASO_PRICING_INT.g_header_rec.PRICE_FROZEN_DATE);
124 end if;
125 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
126 aso_debug_pub.add('Header Request copy: px_req_line_tbl(1).PRICING_EFFECTIVE_DATE '
127 ||px_req_line_tbl(1).PRICING_EFFECTIVE_DATE,1,'N');
128 END IF;
129 end copy_Header_to_request;
130
131 procedure copy_Line_to_request(
132 p_Request_Type VARCHAR2,
133 p_pricing_event VARCHAR2,
134 p_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type,
135 p_line_dtl_rec ASO_QUOTE_PUB.Qte_Line_Dtl_Rec_Type,
136 p_control_rec IN ASO_PRICING_INT.PRICING_CONTROL_REC_TYPE,
137 px_req_line_tbl IN OUT NOCOPY /* file.sql.39 change */ QP_PREQ_GRP.LINE_TBL_TYPE)
138 is
139 l_req_line_rec QP_PREQ_GRP.LINE_REC_TYPE;
140 l_uom_rate NUMBER;
141 begin
142 /*
143 * New Code: Passing quote line id to QP pl/sql tbl
144 */
145 l_req_line_rec.line_id := p_Line_rec.quote_line_id;
146 l_req_line_rec.REQUEST_TYPE_CODE := p_Request_Type;
147 l_req_line_rec.PRICING_EVENT :=p_pricing_event;
148 l_req_line_rec.LINE_INDEX := px_req_line_tbl.count+1;
149 l_req_line_rec.LINE_TYPE_CODE := 'LINE';
150 l_req_line_rec.LINE_QUANTITY := p_Line_rec.quantity;
151 l_req_line_rec.LINE_UOM_CODE := p_Line_rec.uom_code;
152 l_req_line_rec.CURRENCY_CODE := p_Line_rec.currency_code;
153 If p_Line_rec.line_list_price <> FND_API.G_MISS_NUM Then
154 l_req_line_rec.UNIT_PRICE := p_Line_rec.line_list_price;
155 Else
156 l_req_line_rec.UNIT_PRICE := Null;
157 End If;
158 -- Added for Service Item after pathcset E
159 If p_line_dtl_rec.service_period is not null
160 AND p_line_dtl_rec.service_period <> fnd_api.g_miss_char then
161 If (p_line_dtl_rec.service_period = p_Line_rec.uom_code) Then
162 l_req_line_rec.UOM_QUANTITY := p_line_dtl_rec.service_duration;
163 Else
164 INV_CONVERT.INV_UM_CONVERSION(
165 From_Unit => p_line_dtl_rec.service_period
166 ,To_Unit => p_Line_rec.uom_code
167 ,Item_ID => p_Line_rec.Inventory_item_id
168 ,Uom_Rate => l_Uom_rate);
169 l_req_line_rec.UOM_QUANTITY := p_line_dtl_rec.service_duration * l_uom_rate;
170 End If;
171 End If;
172 -- Change for manual discount updating UPDATED_ADJUSTED_UNIT_PRICE
173 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
174 aso_debug_pub.add('In Copy line to req p_Line_rec.SELLING_PRICE_CHANGE '
175 ||p_Line_rec.SELLING_PRICE_CHANGE,1,'Y');
176 END IF;
177 If p_Line_rec.SELLING_PRICE_CHANGE = 'Y' then
178 l_req_line_rec.UPDATED_ADJUSTED_UNIT_PRICE := p_Line_rec.line_quote_price;
179 End If;
180 l_req_line_rec.PRICE_FLAG := 'Y';
181 /*FastTrak: Price effective date is assigned to the price frozen unless the price frozen date is null*/
182 if ASO_PRICING_INT.g_header_rec.PRICE_FROZEN_DATE = FND_API.G_MISS_DATE
183 OR ASO_PRICING_INT.g_header_rec.PRICE_FROZEN_DATE is NULL then
184 l_req_line_rec.PRICING_EFFECTIVE_DATE := trunc(sysdate);
185 else
186 l_req_line_rec.PRICING_EFFECTIVE_DATE := trunc(ASO_PRICING_INT.g_header_rec.PRICE_FROZEN_DATE);
187 end if;
188 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
189 aso_debug_pub.add('copy line request: l_req_line_rec.PRICING_EFFECTIVE_DATE '
190 ||l_req_line_rec.PRICING_EFFECTIVE_DATE,1,'N');
191 aso_debug_pub.add('ASO_PRICING_CALLBACK_PVT:copy line request: p_Line_rec.CHARGE_PERIODICITY_CODE '
192 ||p_Line_rec.CHARGE_PERIODICITY_CODE,1,'N');
193 END IF;
194 l_req_line_rec.CHARGE_PERIODICITY_CODE := p_Line_rec.CHARGE_PERIODICITY_CODE;
195
196 px_req_line_tbl(px_req_line_tbl.count+1) := l_req_line_rec;
197 end copy_Line_to_request;
198 /*End of local functions and Procedures*/
199
200
201
202 /*Main API*/
203
204 PROCEDURE Validate_Pricing_Order(
205 P_Api_Version_Number IN NUMBER,
206 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
207 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
208 p_control_rec IN ASO_PRICING_INT.PRICING_CONTROL_REC_TYPE,
209 p_qte_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
210 p_hd_shipment_rec IN ASO_QUOTE_PUB.Shipment_Rec_Type
211 := ASO_QUOTE_PUB.G_Miss_Shipment_Rec,
212 p_hd_price_attr_tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
213 := ASO_QUOTE_PUB.G_Miss_Price_Attributes_Tbl,
214 p_qte_line_tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type,
215 p_line_rltship_tbl IN ASO_QUOTE_PUB.Line_Rltship_Tbl_Type
216 := ASO_QUOTE_PUB.G_Miss_Line_Rltship_Tbl,
217 p_qte_line_dtl_tbl IN ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type
218 := ASO_QUOTE_PUB.G_Miss_Qte_Line_Dtl_Tbl,
219 p_ln_shipment_tbl IN ASO_QUOTE_PUB.Shipment_Tbl_Type
220 := ASO_QUOTE_PUB.G_Miss_Shipment_Tbl,
221 p_ln_price_attr_tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
222 := ASO_QUOTE_PUB.G_Miss_Price_Attributes_Tbl,
223 x_qte_header_rec OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Header_Rec_Type,
224 x_qte_line_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Line_Tbl_Type,
225 x_qte_line_dtl_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type,
226 x_price_adj_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Price_Adj_Tbl_Type,
227 x_price_adj_attr_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type,
228 x_price_adj_rltship_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type,
229 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
230 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
231 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
232
233 IS
234 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Pricing_Order';
235 l_api_version_number CONSTANT NUMBER := 1.0;
236 l_request_type VARCHAR2(60);
237 l_pricing_event VARCHAR2(30);
238 l_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
239 l_req_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
240 l_Req_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
241 l_Req_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
242 l_Req_LINE_DETAIL_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
243 l_req_LINE_DETAIL_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
244 l_req_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
245 l_req_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
246 l_req_adj_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
247 l_hd_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
248 l_hd_qual_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
249 l_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
250 l_qual_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
251 lx_req_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
252 lx_req_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
253 lx_req_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
254 lx_req_LINE_DETAIL_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
255 lx_req_LINE_DETAIL_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
256 lx_req_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
257 lx_req_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
258 l_qte_line_id NUMBER;
259 l_qte_line_dtl_rec ASO_QUOTE_PUB.Qte_Line_Dtl_Rec_Type;
260 l_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
261 l_return_status VARCHAR2(1);
262 l_return_status_text VARCHAR2(2000);
263 i BINARY_INTEGER;
264 j BINARY_INTEGER;
265 k BINARY_INTEGER;
266 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
267 lx_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
268 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
269 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
270 l_srv_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
271 l_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
272 l_qte_line_prcd_parent ASO_PRICING_INT.Index_Link_Tbl_Type;
273 l_qte_line_prcd_child ASO_PRICING_INT.Index_Link_Tbl_Type;
274 lx_req_line_rec QP_PREQ_GRP.LINE_REC_TYPE;
275 l_call_flg Varchar2(1):='Q';
276 l_db_tbl_flg Varchar2(3);
277 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
278 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
279
280 CURSOR C_status_code IS
281 select line_id,
282 pricing_status_code,
283 pricing_status_text
284 from qp_preq_lines_tmp lines
285 where lines.line_type_code='LINE'
286 and lines.pricing_status_code in(
287 QP_PREQ_GRP.g_status_invalid_price_list,
288 QP_PREQ_GRP.g_sts_lhs_not_found,
289 QP_PREQ_GRP.g_status_formula_error,
290 QP_PREQ_GRP.g_status_other_errors,
291 fnd_api.g_ret_sts_unexp_error,
292 fnd_api.g_ret_sts_error,
293 QP_PREQ_GRP.g_status_calc_error,
294 QP_PREQ_GRP.g_status_uom_failure,
295 QP_PREQ_GRP.g_status_invalid_uom,
296 QP_PREQ_GRP.g_status_dup_price_list,
297 QP_PREQ_GRP.g_status_invalid_uom_conv,
298 QP_PREQ_GRP.g_status_invalid_incomp,
299 QP_PREQ_GRP.g_status_best_price_eval_error);
300
301
302
303
304 BEGIN
305 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
306 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT: Validate_Pricing_Order Starts',1,'N');
307 END IF;
308 -- Standard Start of API savepoint
309 SAVEPOINT PRICING_ORDER_PVT;
310
311 -- Standard call to check for call compatibility.
312 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
313 p_api_version_number,
314 l_api_name,
315 G_PKG_NAME)
316 THEN
317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
318 END IF;
319
320 -- Initialize message list if p_init_msg_list is set to TRUE.
321 IF FND_API.to_Boolean( p_init_msg_list )
322 THEN
323 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
324 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:Begin FND_API.to_Boolean'||p_init_msg_list, 1, 'Y');
325 END IF;
326 FND_MSG_PUB.initialize;
327 END IF;
328
329 x_return_status := FND_API.G_RET_STS_SUCCESS;
330 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
331 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:p_control_rec.request_type:'
332 ||p_control_rec.request_type,1,'N');
333 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:p_control_rec.pricing_event:'
334 ||p_control_rec.pricing_event,1,'N');
335 END IF;
336 l_request_type := p_control_rec.request_type;
337 l_pricing_event := p_control_rec.pricing_event;
338
339 ASO_PRICING_INT.G_HEADER_REC := ASO_PRICING_CORE_PVT.Set_Global_Rec (
340 p_qte_header_rec => p_qte_header_rec,
341 p_shipment_rec => p_hd_shipment_rec);
342
343 /*Debug msgs to check the value of global header record elements passed into pricing order*/
344 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
345 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:QP_ATTR_MAPPING_PUB.Build_Contexts Starts',1,'Y');
346 END IF;
347 QP_ATTR_MAPPING_PUB.Build_Contexts (
348 P_REQUEST_TYPE_CODE => l_request_type,
349 P_PRICING_TYPE => 'H',
350 X_PRICE_CONTEXTS_RESULT_TBL => l_hd_pricing_contexts_tbl,
351 X_QUAL_CONTEXTS_RESULT_TBL => l_hd_qual_contexts_tbl);
352 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
353 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:QP_ATTR_MAPPING_PUB.Build_Contexts Ends',1,'Y');
354 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:Copy_Attribs_To_Req Starts',1,'Y');
355 END IF;
356
357 Copy_Attribs_To_Req (
358 p_line_index => 1,
359 p_pricing_contexts_tbl => l_hd_pricing_contexts_tbl,
360 p_qualifier_contexts_tbl => l_hd_qual_contexts_tbl,
361 px_req_line_attr_tbl => l_req_line_attr_tbl,
362 px_req_qual_tbl => l_req_qual_tbl);
363 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
364 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:Copy_Attribs_To_Req Ends',1,'Y');
365 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:Copy_Header_To_Request Starts',1,'Y');
366 END IF;
367
368 Copy_Header_To_Request(
369 p_Request_Type => l_request_type,
370 p_pricing_event => l_pricing_event,
371 p_header_rec => p_qte_header_rec,
372 px_req_line_tbl => l_Req_line_tbl);
373 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
374 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:Copy_Header_To_Request Ends',1,'Y');
375 aso_debug_pub.add('p_qte_header_rec.quote_header_id:'||p_qte_header_rec.quote_header_id,1,'N');
376 END IF;
377
378 If p_qte_line_tbl.count = 0 then
379 l_qte_line_tbl := ASO_UTILITY_PVT.Query_Qte_Line_Rows(p_qte_header_rec.quote_header_id);
380 Else
381 l_qte_line_tbl := p_qte_line_tbl;
382 End If;
383
384 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
385 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:l_qte_line_tbl.count'|| l_qte_line_tbl.count, 1, 'N');
386 END IF;
387 If l_qte_line_tbl.count > 0 Then
388 FOR i IN 1..l_qte_line_tbl.count LOOP
389 /*We store the line_index in quote_line_id before building the l_req_line_tbl */
390 if l_qte_line_tbl(i).quote_line_id is null
391 OR l_qte_line_tbl(i).quote_line_id = fnd_api.g_miss_num Then
392 l_call_flg := 'O';
393 l_qte_line_tbl(i).quote_line_id := i+1;
394 end if;
395 l_qte_line_id := l_qte_line_tbl(i).quote_line_id;
396 /*Save a copy of the record to a different table before defaulting*/
397 lx_qte_line_tbl(i) := l_qte_line_tbl(i);
398
399 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
400 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT: l_qte_line_id:'||nvl(l_qte_line_id,0),1,'N');
401 END IF;
402
403 /* Default header_level currency_code and price list id to line level*/
404 l_qte_line_tbl(i).currency_code := p_qte_header_rec.currency_code;
405
406 If (l_qte_line_tbl(i).price_list_id is null
407 OR l_qte_line_tbl(i).price_list_id= FND_API.G_MISS_NUM) Then
408 l_qte_line_tbl(i).price_list_id := p_qte_header_rec.price_list_id;
409 End if;
410
411 /*Query Detail Line tbl and shipment*/
412 If l_call_flg = 'Q' then
413 l_qte_line_dtl_tbl := ASO_UTILITY_PVT.Query_Line_Dtl_Rows(l_qte_line_id);
414 If l_qte_line_dtl_tbl.count >0 then
415 l_srv_line_dtl_tbl(l_srv_line_dtl_tbl.count+1):= l_qte_line_dtl_tbl(1);
416 end if;
417
418 l_qte_line_dtl_rec := ASO_QUOTE_PUB.G_Miss_Qte_Line_Dtl_REC ;
419 IF l_qte_line_dtl_tbl.count = 1 THEN
420 l_qte_line_dtl_rec := l_qte_line_dtl_tbl(1);
421 END IF;
422
423 l_shipment_tbl := ASO_UTILITY_PVT.Query_Shipment_Rows(p_qte_header_rec.quote_header_id,
424 l_qte_line_id);
425 l_shipment_rec := ASO_QUOTE_PUB.G_Miss_Shipment_rec;
426 IF l_shipment_tbl.count = 1 THEN
427 l_shipment_rec := l_shipment_tbl(1);
428 END IF;
429 End If;--If l_call_flg = 'Q' then
430
431 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
432 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:Begin Setting up the G_LINE_REC', 1, 'Y');
433 END IF;
434 ASO_PRICING_INT.G_LINE_REC := ASO_PRICING_CORE_PVT.Set_Global_Rec (
435 p_qte_line_rec => l_qte_line_tbl(i),
436 p_qte_line_dtl_rec => l_qte_line_dtl_rec,
437 p_shipment_rec => l_shipment_rec);
438 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
439 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:End Setting up the G_LINE_REC', 1, 'Y');
440 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:Begin Setting up the Line Build_Contexts', 1, 'Y');
441 END IF;
442 QP_ATTR_MAPPING_PUB.Build_Contexts (
443 P_REQUEST_TYPE_CODE => l_request_type,
444 P_PRICING_TYPE => 'L',
445 X_PRICE_CONTEXTS_RESULT_TBL => l_pricing_contexts_tbl,
446 X_QUAL_CONTEXTS_RESULT_TBL => l_qual_contexts_tbl);
447 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
448 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:End Setting up the Line Build_Contexts', 1, 'Y');
449 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:Begin Setting up the Copy_Attribs_To_Req', 1, 'Y');
450 END IF;
451 Copy_Attribs_To_Req (
452 p_line_index => i+1,
453 p_pricing_contexts_tbl => l_pricing_contexts_tbl,
454 p_qualifier_contexts_tbl => l_qual_contexts_tbl,
455 px_req_line_attr_tbl => l_req_line_attr_tbl,
456 px_req_qual_tbl => l_req_qual_tbl);
457 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
458 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:End Setting up the Copy_Attribs_To_Req', 1, 'Y');
459 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:Begin Setting up the Copy_hdr_attr_to_line', 1, 'Y');
460 END IF;
461 Copy_hdr_attr_to_line (
462 p_line_index => i+1,
463 p_pricing_contexts_tbl => l_hd_pricing_contexts_tbl,
464 p_qualifier_contexts_tbl => l_hd_qual_contexts_tbl,
465 px_req_line_attr_tbl => l_req_line_attr_tbl,
466 px_req_qual_tbl => l_req_qual_tbl);
467 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
468 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:End Setting up the Copy_hdr_attr_to_line', 1, 'Y');
469 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:Begin Setting up the Copy_Line_To_Request', 1, 'Y');
470 END IF;
471 Copy_Line_To_Request(
472 p_Request_Type => l_request_type,
473 p_pricing_event => l_pricing_event,
474 p_line_rec => l_qte_line_tbl(i),
475 p_line_dtl_rec => l_qte_line_dtl_rec,
476 p_control_rec => p_control_rec,
477 px_req_line_tbl => l_Req_line_tbl);
478 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
479 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:End Setting up the Copy_Line_To_Request', 1, 'Y');
480 END IF;
481
482 /*Keeping track of indexes for service processing*/
483 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
484 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:serviceable_product_flag:'
485 ||l_qte_line_tbl(i).serviceable_product_flag,1,'Y');
486 END IF;
487 If l_qte_line_tbl(i).serviceable_product_flag = 'Y' then
488 l_qte_line_prcd_parent(l_qte_line_id) := i;
489 end if;
490 /*Assuming l_qte_line_tbl is the p_qte_line_tbl and data is always passed in the PL/SQL table*/
491 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
492 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:service_item_flag:'
493 ||l_qte_line_tbl(i).service_item_flag,1,'Y');
494 END IF;
495 If l_qte_line_tbl(i).service_item_flag = 'Y' then
496 l_qte_line_prcd_child(l_qte_line_id):= i;
497 End if;
498
499 END LOOP; --main End loop FOR i IN 1..l_qte_line_tbl.count
500
501 /*Build relationship table: Only support Current quote*/
502 If l_srv_line_dtl_tbl.count > 0 then
503 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
504 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:l_srv_line_dtl_tbl.count:'
505 ||l_srv_line_dtl_tbl.count,1,'Y');
506 END IF;
507 For j in 1..l_srv_line_dtl_tbl.count loop
508 If (l_srv_line_dtl_tbl(j).SERVICE_REF_LINE_ID IS NOT NULL
509 OR l_srv_line_dtl_tbl(j).SERVICE_REF_LINE_ID <> FND_API.G_MISS_NUM) Then
510 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
511 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:quote line id:'||l_srv_line_dtl_tbl(j).quote_line_id,1,'Y');
512 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:service ref line id:'||l_srv_line_dtl_tbl(j).service_ref_line_id,1,'Y');
513 END IF;
514 l_req_related_lines_tbl(j).line_index := l_qte_line_prcd_parent(l_srv_line_dtl_tbl(j).service_ref_line_id)+1;
515 l_req_related_lines_tbl(j).LINE_DETAIL_INDEX := 0;
516 l_req_related_lines_tbl(j).RELATED_LINE_INDEX := l_qte_line_prcd_child(l_srv_line_dtl_tbl(j).quote_line_id)+1;
517 l_req_related_lines_tbl(j).RELATED_LINE_DETAIL_INDEX := 0;
518 l_req_related_lines_tbl(j).RELATIONSHIP_TYPE_CODE :=QP_PREQ_GRP.G_SERVICE_LINE;
519 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
520 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:line_index :'
521 ||l_req_related_lines_tbl(j).line_index, 1, 'Y');
522 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:related line_index: '
523 ||l_req_related_lines_tbl(j).RELATED_LINE_INDEX , 1, 'Y');
524 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:RELATIONSHIP TYPE CODE: '
525 ||l_req_related_lines_tbl(j).RELATIONSHIP_TYPE_CODE, 1, 'Y');
526 END IF;
527 END IF; --l_srv_line_dtl_tbl(j).SERVICE_REF_LINE_ID IS NOT NULL
528 End Loop;
529 End If;--l_srv_line_dtl_tbl.count > 0
530
531
532 l_control_rec.pricing_event := p_control_rec.pricing_event;
533 l_control_rec.calculate_flag := p_control_rec.calculate_flag;
534 l_control_rec.simulation_flag := p_control_rec.simulation_flag;
535 l_control_rec.TEMP_TABLE_INSERT_FLAG := 'Y';
536 l_control_rec.source_order_amount_flag := 'Y';
537 l_control_rec.GSA_CHECK_FLAG := 'Y';
538 l_control_rec.GSA_DUP_CHECK_FLAG := 'Y';
539
540 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
541 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:Start of QP_PREQ_PUB.PRICE_REQUEST', 1, 'Y');
542 END IF;
543
544 /* Change for populating QP_PREQ_GRP.CONTROL_RECORD_TYPE.ORG_ID Yogeshwar (MOAC) */
545
546 IF ((p_qte_header_rec.org_id IS NULL) OR (p_qte_header_rec.org_id = FND_API.G_MISS_NUM)) THEN
547 IF fnd_msg_pub.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
548 FND_MESSAGE.Set_Name('ASO', 'ASO_MISSING_OU');
549 FND_MSG_PUB.ADD;
550 END IF;
551
552 RAISE FND_API.G_EXC_ERROR;
553 END IF;
554
555 l_control_rec.ORG_ID := p_qte_header_rec.org_id;
556
557 /* End of Change (MOAC) */
558
559
560
561
562 QP_PREQ_PUB.PRICE_REQUEST
563 (p_control_rec => l_control_rec,
564 p_line_tbl => l_Req_line_tbl,
565 p_qual_tbl => l_Req_qual_tbl,
566 p_line_attr_tbl => l_Req_line_attr_tbl,
567 p_line_detail_tbl => l_req_line_detail_tbl,
568 p_line_detail_qual_tbl => l_req_line_detail_qual_tbl,
569 p_line_detail_attr_tbl => l_req_line_detail_attr_tbl,
570 p_related_lines_tbl => l_req_related_lines_tbl,
571 x_line_tbl => lx_req_line_tbl,
572 x_line_qual => lx_Req_qual_tbl,
573 x_line_attr_tbl => lx_Req_line_attr_tbl,
574 x_line_detail_tbl => lx_req_line_detail_tbl,
575 x_line_detail_qual_tbl => lx_req_line_detail_qual_tbl,
576 x_line_detail_attr_tbl => lx_req_line_detail_attr_tbl,
577 x_related_lines_tbl => lx_req_related_lines_tbl,
578 x_return_status => l_return_status,
579 x_return_status_text => l_return_status_text);
580 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
581 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:End of QP_PREQ_PUB.PRICE_REQUEST', 1, 'Y');
582 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:After PRICE_REQUEST l_return_status:'
583 ||l_return_status, 1, 'Y');
584 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:After PRICE_REQUEST l_return_status_text '
585 ||l_return_status_text,1,'N');
586 END IF;
587
588 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
589 RAISE FND_API.G_EXC_ERROR;
590 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
591 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
592 END IF;
593
594 /*In lx_Req_line_tbl check for the return status for each line and then populate that status in
595 **x_qte_line_tbl.
596 */
597 ASO_PRICING_INT.G_LINE_REC := NULL;
598 ASO_PRICING_INT.G_HEADER_REC := NULL;
599 /*Returning the same table back to the caller*/
600 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
601 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT: After pricing call lx_qte_line_tbl.count:'
602 ||nvl(lx_qte_line_tbl.count,0),1,'Y');
603 END IF;
604 x_qte_line_tbl := lx_qte_line_tbl;
605 /*In quote line table initialize the pricing_status_text to success*/
606 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
607 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:x_qte_line_tbl.count:'||x_qte_line_tbl.count,1,'N');
608 END IF;
609 k := x_qte_line_tbl.FIRST;
610 While k is not null loop
611 x_qte_line_tbl(k).pricing_status_code := FND_API.G_RET_STS_SUCCESS;
612 k := x_qte_line_tbl.NEXT(k);
613 end loop;
614
615 /*Update all the errored records*/
616 FOR C_status_code_rec in C_status_code LOOP
617 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
618 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT: C_status_code_rec.line_id:'
619 ||lx_req_line_rec.line_id,1,'N');
620 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT: C_status_code_rec.pricing_status_code:'
621 ||lx_req_line_rec.status_code,1,'N');
622 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT: C_status_code_rec.pricing_status_text:'
623 ||lx_req_line_rec.status_text,1,'N');
624 END IF;
625
626 /*For this line_id update the pricing_status_code in the x_qte_line_tbl*/
627 j := x_qte_line_tbl.FIRST;
628 WHILE j IS NOT NULL LOOP
629 If C_status_code_rec.line_id = x_qte_line_tbl(j).quote_line_id then
630 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
631 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT: Updating the quote_line_id: '
632 ||x_qte_line_tbl(j).quote_line_id||' with status_code: '
633 ||FND_API.G_RET_STS_ERROR,1,'N');
634 END IF;
635 x_qte_line_tbl(j).pricing_status_code := FND_API.G_RET_STS_ERROR;
636 x_qte_line_tbl(j).pricing_status_text := C_status_code_rec.pricing_status_text;
637 exit;
638 End If;
639 j := x_qte_line_tbl.NEXT(j);
640 END LOOP; --End loop WHILE j IS NOT NULL LOOP
641 END LOOP; --C_status_code_rec in C_status_code LOOP
642
643
644 If l_call_flg = 'O' then
645 For i in 1..x_qte_line_tbl.count loop
646 x_qte_line_tbl(i).quote_line_id := FND_API.G_MISS_NUM;
647 end loop;
648 End If;
649
650
651 Else
652 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
653 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT: There are no quote lines for the price request validation',1,'N');
654 END IF;
655 End If; -- If l_qte_line_tbl.count > 0
656
657
658 -- Standard check for p_commit
659 IF FND_API.to_Boolean( p_commit ) THEN
660 COMMIT WORK;
661 END IF;
662
663 FND_MSG_PUB.Count_And_Get
664 ( p_encoded => 'F',
665 p_count => x_msg_count,
666 p_data => x_msg_data
667 );
668
669 for l in 1 .. x_msg_count loop
670 x_msg_data := fnd_msg_pub.get( p_msg_index => l, p_encoded => 'F');
671 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
672 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:Messge count and get '||x_msg_data, 1, 'Y');
673 aso_debug_pub.add('ASO_VALIDATE_PRICING_PVT:Messge count and get '||x_msg_count, 1, 'Y');
674 END IF;
675 end loop;
676
677
678 EXCEPTION
679 WHEN FND_API.G_EXC_ERROR THEN
680 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
681 aso_debug_pub.add('after inside EXCEPTION return status'||x_return_status, 1, 'Y');
682 END IF;
683 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
684 P_API_NAME => L_API_NAME
685 ,P_PKG_NAME => G_PKG_NAME
686 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
687 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
688 ,P_SQLCODE => SQLCODE
689 ,P_SQLERRM => SQLERRM
690 ,X_MSG_COUNT => X_MSG_COUNT
691 ,X_MSG_DATA => X_MSG_DATA
692 ,X_RETURN_STATUS => X_RETURN_STATUS);
693
694 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
695 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
696 P_API_NAME => L_API_NAME
697 ,P_PKG_NAME => G_PKG_NAME
698 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
699 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
700 ,P_SQLCODE => SQLCODE
701 ,P_SQLERRM => SQLERRM
702 ,X_MSG_COUNT => X_MSG_COUNT
703 ,X_MSG_DATA => X_MSG_DATA
704 ,X_RETURN_STATUS => X_RETURN_STATUS);
705
706 WHEN OTHERS THEN
707 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
708 P_API_NAME => L_API_NAME
709 ,P_PKG_NAME => G_PKG_NAME
710 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
711 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
712 ,P_SQLCODE => SQLCODE
713 ,P_SQLERRM => SQLERRM
714 ,X_MSG_COUNT => X_MSG_COUNT
715 ,X_MSG_DATA => X_MSG_DATA
716 ,X_RETURN_STATUS => X_RETURN_STATUS);
717
718
719 END;
720
721
722 End ASO_VALIDATE_PRICING_PVT;