[Home] [Help]
PACKAGE BODY: APPS.PV_PRICE_PVT
Source
1 PACKAGE BODY PV_PRICE_PVT as
2 /* $Header: pvxvprib.pls 120.5 2006/05/04 15:37:47 dgottlie ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PV_PRICE_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvpomb.pls';
6 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
7 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
8 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
9
10 FUNCTION check_billing_address_exists(
11 p_partner_party_id IN NUMBER
12 ,p_contact_party_id IN NUMBER
13 ) RETURN BOOLEAN IS
14
15 l_return number;
16
17 BEGIN
18
19 select 1
20 into l_return
21 from dual
22 where exists
23 (
24 select hzu.party_site_use_id
25 from hz_party_sites hzs,
26 hz_party_site_uses hzu
27 where hzs.party_id in (p_partner_party_id, p_contact_party_id)
28 and hzu.party_site_id = hzs.party_site_id
29 and hzu.site_use_type = 'BILL_TO'
30 and hzs.status = 'A'
31 and hzu.status = 'A'
32 );
33
34 IF (l_return = 1) THEN
35 return true;
36 ELSE
37 return false;
38 END IF;
39
40 EXCEPTION
41 WHEN NO_DATA_FOUND THEN
42 return false;
43
44 END check_billing_address_exists;
45
46 PROCEDURE Price_Request(
47 p_api_version_number IN NUMBER
48 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
49 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
50 ,p_partner_account_id IN NUMBER
51 ,p_partner_party_id IN NUMBER
52 ,p_contact_party_id IN NUMBER
53 ,p_transaction_currency IN VARCHAR2
54 ,p_enrl_req_id IN JTF_NUMBER_TABLE
55 ,x_return_status OUT NOCOPY VARCHAR2
56 ,x_msg_count OUT NOCOPY NUMBER
57 ,x_msg_data OUT NOCOPY VARCHAR2
58 )
59 IS
60 l_api_version_number CONSTANT NUMBER := 1.0;
61 l_api_name CONSTANT VARCHAR2(30) := 'Price_Request';
62 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
63
64 l_inventory_item_id NUMBER;
65 l_enrl_req_id NUMBER;
66 l_inventory_item_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
67 l_enrl_req_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
68 l_obj_ver_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
69
70
71 l_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
72 G_LiNE_iNDEX_TBL QP_PREQ_GRP.PLS_iNTEGER_TYPE;
73 G_LiNE_TYPE_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
74 G_PRiCiNG_EFFECTiVE_DATE_TBL QP_PREQ_GRP.DATE_TYPE ;
75 G_ACTiVE_DATE_FiRST_TBL QP_PREQ_GRP.DATE_TYPE ;
76 G_ACTiVE_DATE_FiRST_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
77 G_ACTiVE_DATE_SECOND_TBL QP_PREQ_GRP.DATE_TYPE ;
78 G_ACTiVE_DATE_SECOND_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE ;
79 --G_LiNE_QUANTiTY_TBL QP_PREQ_GRP.NUMBER_TYPE ;
80 --G_LiNE_UOM_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
81 G_REQUEST_TYPE_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
82 G_PRiCED_QUANTiTY_TBL QP_PREQ_GRP.NUMBER_TYPE;
83 G_UOM_QUANTiTY_TBL QP_PREQ_GRP.NUMBER_TYPE;
84 G_PRiCED_UOM_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
85 G_CURRENCY_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
86 G_UNiT_PRiCE_TBL QP_PREQ_GRP.NUMBER_TYPE;
87 G_PERCENT_PRiCE_TBL QP_PREQ_GRP.NUMBER_TYPE;
88 G_ADJUSTED_UNiT_PRiCE_TBL QP_PREQ_GRP.NUMBER_TYPE;
89 G_UPD_ADJUSTED_UNiT_PRiCE_TBL QP_PREQ_GRP.NUMBER_TYPE;
90 G_PROCESSED_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
91 G_PRiCE_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
92 G_LiNE_iD_TBL QP_PREQ_GRP.NUMBER_TYPE;
93 G_PROCESSiNG_ORDER_TBL QP_PREQ_GRP.PLS_iNTEGER_TYPE;
94 G_ROUNDiNG_FACTOR_TBL QP_PREQ_GRP.PLS_iNTEGER_TYPE;
95 G_ROUNDiNG_FLAG_TBL QP_PREQ_GRP.FLAG_TYPE;
96 G_QUALiFiERS_EXiST_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
97 G_PRiCiNG_ATTRS_EXiST_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
98 G_PRiCE_LiST_iD_TBL QP_PREQ_GRP.NUMBER_TYPE;
99 G_PL_VALiDATED_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
100 G_PRiCE_REQUEST_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
101 G_USAGE_PRiCiNG_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
102 G_LiNE_CATEGORY_TBL QP_PREQ_GRP.VARCHAR_TYPE;
103 G_PRiCiNG_STATUS_CODE_tbl QP_PREQ_GRP.VARCHAR_TYPE;
104 G_PRiCiNG_STATUS_TEXT_tbl QP_PREQ_GRP.VARCHAR_TYPE;
105 G_RELATiONSHiP_TYPE_CODE QP_PREQ_GRP.VARCHAR_TYPE;
106 G_LiNE_DETAiL_iNDEX_tbl QP_PREQ_GRP.NUMBER_TYPE;
107 G_RLTD_LiNE_DETAiL_iNDEX_tbl QP_PREQ_GRP.NUMBER_TYPE;
108 G_LiNE_QUANTiTY_TBL QP_PREQ_GRP.NUMBER_TYPE ;
109 G_LiNE_UOM_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
110
111 line_index number := 0;
112 l_line_index number;
113 l_line_id number;
114 l_adjusted_unit_price NUMBER;
115 l_unit_price number;
116 L_LINE_UNIT_PRICE number;
117 l_ORDER_UOM_SELLiNG_PRiCE number;
118 l_LINE_AMOUNT number;
119 l_EXTENDED_PRICE number;
120 l_LiNE_UOM_CODE VARCHAR2(30);
121 l_PRICED_QUANTITY number;
122 l_PRICED_UOM_CODE VARCHAR2(30);
123 l_UOM_QUANTITY number;
124 l_LINE_TYPE_CODE VARCHAR2(30);
125 l_LiNE_QUANTiTY number;
126 l_PRiCiNG_STATUS_CODE VARCHAR2(30);
127 l_PRiCiNG_STATUS_TEXT VARCHAR2(2000);
128 l_price_list_header_id number;
129 l_currency_code VARCHAR2(30);
130 l_return_status_text VARCHAR2(2000);
131 l_billing_address_exists boolean := false;
132 l_line_has_price_error boolean := false;
133 l_has_price_error boolean := false;
134 l_program_name varchar2(60);
135 l_enrl_req_rec PV_Pg_Enrl_Requests_PVT.enrl_request_rec_type;
136 l_uom_code VARCHAR2(10);
137
138
139
140 -- Cursor to get partner program inventoty item id
141 CURSOR c_inventory_item_id IS
142 SELECT /*+ CARDINALITY(erequests 10) */ pvpp.inventory_item_id, pver.enrl_request_id, pver.object_version_number
143 FROM PV_PARTNER_PROGRAM_B pvpp, PV_PG_ENRL_REQUESTS pver,
144 (Select column_value from table (CAST(p_enrl_req_id AS JTF_NUMBER_TABLE))) erequests
145 WHERE pver.enrl_request_id = erequests.column_value
146 and pver.PROGRAM_ID = pvpp.program_id
147 and pver.custom_setup_id in (7004, 7005)
148 and (pver.order_header_id is null
149 OR (pver.order_header_id is not null and pver.payment_status_code <> 'AUTHORIZED_PAYMENT'));
150
151
152
153 -- Cursor to get price
154 CURSOR c_price IS
155 SELECT LiNE_INDEX, LINE_ID, adjusted_unit_price, UNIT_PRICE, LINE_UNIT_PRICE, ORDER_UOM_SELLING_PRICE, LINE_AMOUNT, EXTENDED_PRICE
156 ,LiNE_UOM_CODE, PRICED_QUANTITY, PRICED_UOM_CODE, UOM_QUANTITY
157 , LINE_TYPE_CODE, LiNE_QUANTiTY, PRiCiNG_STATUS_CODE, PRICING_STATUS_TEXT, price_list_header_id, currency_code
158 FROM QP_PREQ_LiNES_TMP
159 ORDER BY LiNE_INDEX;
160
161 CURSOR c_program_name (cv_enrl_request_id NUMBER) IS
162 select pvppv.program_name
163 from pv_partner_program_vl pvppv, PV_PG_ENRL_REQUESTS pver
164 where pver.enrl_request_id = cv_enrl_request_id
165 and pver.program_id = pvppv.program_id;
166
167
168
169 BEGIN
170
171
172 ---------------Initialize --------------------
173 -- Standard Start of API savepoint
174 SAVEPOINT PRICE_REQUEST;
175
176 -- Initialize message list if p_init_msg_list is set to TRUE.
177 IF FND_API.to_Boolean( p_init_msg_list )
178 THEN
179 FND_MSG_PUB.initialize;
180 END IF;
181
182 -- Standard call to check for call compatibility.
183 IF NOT FND_API.Compatible_API_Call (
184 l_api_version_number
185 ,p_api_version_number
186 ,l_api_name
187 ,G_PKG_NAME
188 )
189 THEN
190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
191 END IF;
192
193 -- Debug Message
194 IF (PV_DEBUG_HIGH_ON) THEN
195 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - start');
196 END IF;
197
198
199 -- Initialize API return status to SUCCESS
200 x_return_status := FND_API.G_RET_STS_SUCCESS;
201 -------------End Of Initialize -------------------------------
202
203 IF (p_partner_party_id = FND_API.G_MISS_NUM) THEN
204 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_ID');
205 FND_MESSAGE.Set_Token('ID', 'Partner Party', FALSE);
206 FND_MSG_PUB.add;
207 RAISE FND_API.G_EXC_ERROR;
208 END IF;
209
210
211 IF (p_transaction_currency = FND_API.G_MISS_CHAR) THEN
212 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_ID');
213 FND_MESSAGE.Set_Token('ID', 'Currency', FALSE);
214 FND_MSG_PUB.add;
215 RAISE FND_API.G_EXC_ERROR;
216 END IF;
217
218
219
220 IF (p_partner_account_id = FND_API.G_MISS_NUM) THEN
221 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_ID');
222 FND_MESSAGE.Set_Token('ID', 'Account', FALSE);
223 FND_MSG_PUB.add;
224 RAISE FND_API.G_EXC_ERROR;
225 END IF;
226
227 IF (p_enrl_req_id.count < 1) THEN
228 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_ID');
229 FND_MESSAGE.Set_Token('ID', 'Enrollment Request', FALSE);
230 FND_MSG_PUB.add;
231 RAISE FND_API.G_EXC_ERROR;
232 END IF;
233
234 FOR x in c_inventory_item_id LOOP
235 l_inventory_item_id_tbl.extend();
236 l_enrl_req_id_tbl.extend;
237 l_obj_ver_tbl.extend;
238 l_inventory_item_id_tbl(l_inventory_item_id_tbl.count) := x.inventory_item_id;
239 l_enrl_req_id_tbl(l_enrl_req_id_tbl.count) := x.enrl_request_id;
240 l_obj_ver_tbl(l_obj_ver_tbl.count) := x.object_version_number;
241 END loop;
242
243 IF (l_inventory_item_id_tbl.count > 0 ) THEN
244 IF (check_billing_address_exists(p_partner_party_id => p_partner_party_id,
245 p_contact_party_id => p_contact_party_id)) THEN
246 l_billing_address_exists := true;
247 END IF;
248
249 if(not l_billing_address_exists) then
250 FND_MESSAGE.set_name('PV', 'PV_NO_BILLING_ADDRESS');
251 FND_MSG_PUB.add;
252 RAISE FND_API.G_EXC_ERROR;
253 else
254 IF (PV_DEBUG_HIGH_ON) THEN
255 PVX_UTiLiTY_PVT.debug_message('Billing Address Validated succesfully');
256 END IF;
257 end if;
258
259 PV_ORDER_MGMT_PVT.Order_Debug_On;
260
261 -- Debug Message
262 IF (PV_DEBUG_HIGH_ON) THEN
263 PVX_UTiLiTY_PVT.debug_message('QP Version'||QP_PREQ_GRP.GET_VERSiON);
264 END IF;
265
266 QP_PRiCE_REQUEST_CONTEXT.SET_REQUEST_iD();
267
268 IF (PV_DEBUG_HIGH_ON) THEN
269 PVX_UTiLiTY_PVT.debug_message('request id was set');
270 END IF;
271
272
273 l_control_rec.pricing_event := 'BATCH';
274 l_control_rec.calculate_flag := 'Y';
275 l_control_rec.simulation_flag := 'N';
276 l_control_rec.temp_table_insert_flag := 'N';
277 l_control_rec.request_type_code := 'ONT';
278
279
280 FOR line_index in l_enrl_req_id_tbl.FIRST..l_enrl_req_id_tbl.LAST LOOP
281
282 IF (PV_DEBUG_HIGH_ON) THEN
283 PVX_UTiLiTY_PVT.debug_message('building contex for line :' || line_index);
284 PVX_UTiLiTY_PVT.debug_message('Getting UOM code for item id:' || l_inventory_item_id_tbl(line_index));
285 END IF;
286
287 select msi.primary_uom_code
288 into l_uom_code
289 from mtl_system_items_b msi
290 where msi.inventory_item_id = l_inventory_item_id_tbl(line_index)
291 and rownum = 1;
292
293 IF (PV_DEBUG_HIGH_ON) THEN
294 PVX_UTiLiTY_PVT.debug_message('UOM Code is:' || l_uom_code);
295 END IF;
296
297 G_line_index_tbl(line_index) := line_index;
298 G_line_type_code_tbl(line_index) := 'LINE';
299 G_pricing_effective_date_tbl(line_index) := sysdate;
300 G_active_date_first_tbl(line_index) := sysdate;
304
301 G_active_date_first_type_tbl(line_index) := 'NO TYPE';
302 G_active_date_second_tbl(line_index) := sysdate;
303 G_active_date_second_type_tbl(line_index) :='NO TYPE';
305 G_line_quantity_tbl(line_index) := 1;
306 G_LiNE_UOM_CODE_TBL(line_index) := l_uom_code;
307
308 G_REQUEST_TYPE_CODE_TBL(line_index) := 'ONT';
309 G_PRiCED_QUANTiTY_TBL(line_index) := null;
310 G_PRiCED_UOM_CODE_TBL(line_index) := null;
311 G_CURRENCY_CODE_TBL(line_index) := p_transaction_currency;
312 G_UNiT_PRiCE_TBL(line_index) := null;
313 G_PERCENT_PRiCE_TBL(line_index) := null;
314 G_UOM_QUANTiTY_TBL(line_index) := null;
315 G_ADJUSTED_UNiT_PRiCE_TBL(line_index) := null;
316 G_UPD_ADJUSTED_UNiT_PRiCE_TBL(line_index) := null;
317 G_PROCESSED_FLAG_TBL(line_index) := null;
318 G_PRiCE_FLAG_TBL(line_index) := 'Y';
319 G_LiNE_iD_TBL(line_index) := line_index;
320 G_PROCESSiNG_ORDER_TBL(line_index) := null;
321 G_PRiCiNG_STATUS_CODE_tbl(line_index) := QP_PREQ_GRP.G_STATUS_UNCHANGED;
322 G_PRiCiNG_STATUS_TEXT_tbl(line_index) := null;
323 G_ROUNDiNG_FLAG_TBL(line_index) := null;
324 G_ROUNDiNG_FACTOR_TBL(line_index) := null;
325 G_QUALiFiERS_EXiST_FLAG_TBL(line_index) := 'N';
326 G_PRiCiNG_ATTRS_EXiST_FLAG_TBL(line_index) := 'N';
327 G_PRiCE_LiST_iD_TBL(line_index) := null;
328 G_PL_VALiDATED_FLAG_TBL(line_index) := 'N';
329 G_PRiCE_REQUEST_CODE_TBL(line_index) := null;
330 G_usage_pricing_type_tbl(line_index) := QP_PREQ_GRP.G_REGULAR_USAGE_TYPE;
331 G_LiNE_CATEGORY_tbl(line_index) := null;
332
333
334 OE_ORDER_PUB.G_line.inventory_item_id := l_inventory_item_id_tbl(line_index);
335 OE_ORDER_PUB.G_line.order_quantity_uom:= l_uom_code;
336 OE_ORDER_PUB.G_line.ordered_quantity:= 1;
337 OE_ORDER_PUB.G_line.sold_to_org_id := p_partner_account_id;
338 OE_ORDER_PUB.G_line.order_source_id := 23;
339
340 QP_ATTR_MAPPING_PUB.BUILD_CONTEXTS('ONT', line_index, 'L');
341
342 END LOOP;
343
344
345 line_index := l_enrl_req_id_tbl.LAST;
346 line_index := line_index + 1;
347
348 IF (PV_DEBUG_HIGH_ON) THEN
349 PVX_UTiLiTY_PVT.debug_message('line_index '|| line_index);
350 END IF;
351
352 G_LiNE_iNDEX_TBL(line_index) :=line_index;
353 G_LiNE_TYPE_CODE_TBL(line_index) := 'ORDER';
354 G_pricing_effective_date_tbl(line_index) := sysdate;
355 G_active_date_first_tbl(line_index) := sysdate;
356 G_active_date_first_type_tbl(line_index) := 'NO TYPE';
357 G_active_date_second_tbl(line_index) := sysdate;
358 G_active_date_second_type_tbl(line_index) :='NO TYPE';
359
360 G_LiNE_QUANTiTY_TBL(line_index) := 1;
361 G_LiNE_UOM_CODE_TBL(line_index) := NULL;
362 g_request_type_code_tbl(line_index) := 'ONT';
363 G_PRiCED_QUANTiTY_TBL(line_index) := null;
364 G_PRiCED_UOM_CODE_TBL(line_index) := null;
365 G_currency_code_tbl(line_index) := p_transaction_currency;
366 G_UNiT_PRiCE_TBL(line_index) := null;
367 G_PERCENT_PRiCE_TBL(line_index) := null;
368 G_UOM_QUANTiTY_TBL(line_index) := null;
369 G_ADJUSTED_UNiT_PRiCE_TBL(line_index) := null;
370 G_UPD_ADJUSTED_UNiT_PRiCE_TBL(line_index) := null;
371 G_PROCESSED_FLAG_TBL(line_index) := null;
375 G_PRiCiNG_STATUS_CODE_tbl(line_index) := QP_PREQ_GRP.G_STATUS_UNCHANGED;
372 G_PRiCE_FLAG_TBL(line_index) := 'Y';
373 G_LiNE_iD_TBL(line_index) := line_index;
374 G_PROCESSiNG_ORDER_TBL(line_index) := null;
376 G_PRiCiNG_STATUS_TEXT_tbl(line_index) := null;
377 G_ROUNDiNG_FLAG_TBL(line_index) := null;
378 G_ROUNDiNG_FACTOR_TBL(line_index) := null;
379 G_QUALiFiERS_EXiST_FLAG_TBL(line_index) := 'N';
380 G_PRiCiNG_ATTRS_EXiST_FLAG_TBL(line_index) := 'N';
381 G_PRiCE_LiST_iD_TBL(line_index) := null;
382 G_PL_VALiDATED_FLAG_TBL(line_index) := 'N';
383 G_PRiCE_REQUEST_CODE_TBL(line_index) := null;
384 G_usage_pricing_type_tbl(line_index) := QP_PREQ_GRP.G_REGULAR_USAGE_TYPE;
385 --G_LiNE_CATEGORY_tbl(line_index) := null;
386
387 oe_order_pub.g_hdr.transactional_curr_code := p_transaction_currency;
388 oe_order_pub.g_hdr.sold_to_org_id := p_partner_account_id;
389 oe_order_pub.g_hdr.order_source_id := 23;
390 oe_order_pub.g_hdr.freight_terms_code := NULL;
391 oe_order_pub.g_hdr.order_type_id := to_number(FND_PROFILE.Value('PV_ORDER_TRANSACTION_TYPE_ID'));
392
393 -- populate header attibutes/qualifiers
394 QP_ATTR_MAPPING_PUB.BUILD_CONTEXTS
395 (p_request_type_code => 'ONT',
396 p_line_index => line_index,
397 p_pricing_type_code => 'H');
398
399
400 IF (PV_DEBUG_HIGH_ON) THEN
401 PVX_UTiLiTY_PVT.debug_message('Before inserting into temporary tables');
402 END IF;
403
404
405 QP_PREQ_GRP.iNSERT_LiNES2
406 (p_LiNE_iNDEX => G_LiNE_iNDEX_TBL,
407 p_LiNE_TYPE_CODE => G_LiNE_TYPE_CODE_TBL,
408 p_PRiCiNG_EFFECTiVE_DATE => G_PRiCiNG_EFFECTiVE_DATE_TBL,
409 p_ACTiVE_DATE_FiRST => G_ACTiVE_DATE_FiRST_TBL,
410 p_ACTiVE_DATE_FiRST_TYPE => G_ACTiVE_DATE_FiRST_TYPE_TBL,
411 p_ACTiVE_DATE_SECOND => G_ACTiVE_DATE_SECOND_TBL,
412 p_ACTiVE_DATE_SECOND_TYPE => G_ACTiVE_DATE_SECOND_TYPE_TBL,
413 p_LiNE_QUANTiTY => G_LiNE_QUANTiTY_TBL,
414 p_LiNE_UOM_CODE => G_line_UOM_CODE_TBL,
415 p_REQUEST_TYPE_CODE => G_REQUEST_TYPE_CODE_TBL,
416 p_PRiCED_QUANTiTY => G_PRiCED_QUANTiTY_TBL,
417 p_PRiCED_UOM_CODE => G_line_UOM_CODE_TBL,
418 p_CURRENCY_CODE => G_CURRENCY_CODE_TBL,
419 p_UNiT_PRiCE => G_UNiT_PRiCE_TBL,
420 p_PERCENT_PRiCE => G_PERCENT_PRiCE_TBL,
421 p_UOM_QUANTiTY => G_UOM_QUANTiTY_TBL,
422 p_ADJUSTED_UNiT_PRiCE => G_ADJUSTED_UNiT_PRiCE_TBL,
423 p_UPD_ADJUSTED_UNiT_PRiCE => G_UPD_ADJUSTED_UNiT_PRiCE_TBL,
424 p_PROCESSED_FLAG => G_PROCESSED_FLAG_TBL,
425 p_PRiCE_FLAG => G_PRiCE_FLAG_TBL,
426 p_LiNE_iD => G_LiNE_iD_TBL,
427 p_PROCESSiNG_ORDER => G_PROCESSiNG_ORDER_TBL,
428 p_PRiCiNG_STATUS_CODE => G_PRiCiNG_STATUS_CODE_TBL,
429 p_PRiCiNG_STATUS_TEXT => G_PRiCiNG_STATUS_TEXT_TBL,
430 p_ROUNDiNG_FLAG => G_ROUNDiNG_FLAG_TBL,
431 p_ROUNDiNG_FACTOR => G_ROUNDiNG_FACTOR_TBL,
432 p_QUALiFiERS_EXiST_FLAG => G_QUALiFiERS_EXiST_FLAG_TBL,
433 p_PRiCiNG_ATTRS_EXiST_FLAG => G_PRiCiNG_ATTRS_EXiST_FLAG_TBL,
434 p_PRiCE_LiST_iD => G_PRiCE_LiST_iD_TBL,
435 p_VALiDATED_FLAG => G_PL_VALiDATED_FLAG_TBL,
436 p_PRiCE_REQUEST_CODE => G_PRiCE_REQUEST_CODE_TBL,
437 p_USAGE_PRiCiNG_TYPE => G_USAGE_PRiCiNG_TYPE_TBL,
438 --p_line_category => G_LiNE_CATEGORY_TBL,
439 x_status_code => x_return_status,
440 x_status_text => l_return_status_text);
441
442
443
444 IF (PV_DEBUG_HIGH_ON) THEN
445 PVX_UTiLiTY_PVT.debug_message('Return Status ater inserting : '|| x_return_status);
446 END IF;
447
448 IF (PV_DEBUG_HIGH_ON) THEN
449 PVX_UTiLiTY_PVT.debug_message('Return Status text after inserting : '|| l_return_status_text);
450 END IF;
451
452 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
453 FND_MESSAGE.SET_NAME('PV','PV_PRICING_ERROR');
454 FND_MESSAGE.SET_TOKEN('ERR_TEXT',l_return_status_text);
455 OE_MSG_PUB.Add;
456 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
457 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
458 FND_MESSAGE.SET_NAME('PV','PV_PRICING_ERROR');
459 FND_MESSAGE.SET_TOKEN('ERR_TEXT',l_return_status_text);
460 OE_MSG_PUB.Add;
461 RAISE FND_API.G_EXC_ERROR;
462 END IF;
463
464
465 IF (PV_DEBUG_HIGH_ON) THEN
466 PVX_UTiLiTY_PVT.debug_message('Before pricing engine call');
467 END IF;
468
469
470
471 QP_PREQ_PUB.PRICE_REQUEST
472 (p_control_rec => l_control_rec,
473 x_return_status => x_return_status,
474 x_return_status_text => l_return_status_text);
475
476
477 IF (PV_DEBUG_HIGH_ON) THEN
478 PVX_UTiLiTY_PVT.debug_message('Return Status ater pricing call : '|| x_return_status);
479 END IF;
480
481
482 IF (PV_DEBUG_HIGH_ON) THEN
483 PVX_UTiLiTY_PVT.debug_message('Return Status text after pricing call : '|| l_return_status_text);
484 END IF;
485
486
487 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
488 FND_MESSAGE.SET_NAME('PV','PV_PRICING_ERROR');
489 FND_MESSAGE.SET_TOKEN('ERR_TEXT',l_return_status_text);
490 OE_MSG_PUB.Add;
491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
492 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
493 FND_MESSAGE.SET_NAME('PV','PV_PRICING_ERROR');
494 FND_MESSAGE.SET_TOKEN('ERR_TEXT',l_return_status_text);
495 OE_MSG_PUB.Add;
496 RAISE FND_API.G_EXC_ERROR;
497 END IF;
498
499
500
501 IF (PV_DEBUG_HIGH_ON) THEN
502 PVX_UTiLiTY_PVT.debug_message('Before retrieving the price from temp table ');
503 END IF;
504
505
506
507 open c_price;
508 loop
509
510
511
512 fetch c_price into l_line_index , l_line_id, l_adjusted_unit_price, l_unit_price, l_LINE_UNIT_PRICE, l_ORDER_UOM_SELLiNG_PRiCE,
513 l_LINE_AMOUNT, l_EXTENDED_PRICE, l_LiNE_UOM_CODE, l_PRICED_QUANTITY,
514 l_PRICED_UOM_CODE, l_UOM_QUANTITY, l_LINE_TYPE_CODE, l_LiNE_QUANTiTY, l_PRiCiNG_STATUS_CODE,
515 l_PRiCiNG_STATUS_TEXT, l_price_list_header_id, l_currency_code;
516 exit when c_price%notfound;
517
518 l_line_has_price_error := l_pricing_status_code <> QP_PREQ_PUB.G_STATUS_NEW AND
519 l_pricing_status_code <> QP_PREQ_PUB.G_STATUS_UNCHANGED AND
520 l_pricing_status_code <> QP_PREQ_PUB.G_STATUS_UPDATED;
521
522 IF(l_line_has_price_error) THEN
523 l_has_price_error := true;
524 IF (l_line_type_code = 'LINE') THEN
525 open c_program_name(l_enrl_req_id_tbl(l_line_index));
526 fetch c_program_name into l_program_name;
527 close c_program_name;
528
529 IF (PV_DEBUG_HIGH_ON) THEN
530 PVX_UTiLiTY_PVT.debug_message('Pricing Error for program : ' || l_program_name || ' : ' ||
531 'Pricing_status_code' || ' : ' || l_pricing_status_code || ' : ' ||
532 ' pricing status text :' || l_PRiCiNG_STATUS_TEXT );
533 END IF;
534
535 FND_MESSAGE.set_name('PV', 'PV_ERROR_IN_CALC_PRICE');
536 FND_MESSAGE.Set_Token('PROGRAM_NAME', l_program_name);
537 FND_MSG_PUB.add;
538
539
540
541 ELSIF (l_line_type_code = 'ORDER') THEN
542 IF (PV_DEBUG_HIGH_ON) THEN
543 PVX_UTiLiTY_PVT.debug_message('Pricing Error for ORDER LINE : Pricing_status_code' || ' : ' || l_pricing_status_code || ' : ' ||
544 ' pricing status text :' || l_PRiCiNG_STATUS_TEXT );
545 END IF;
546
547 FND_MESSAGE.set_name('PV', 'PV_ERROR_IN_CALC_PRICE');
548 FND_MSG_PUB.add;
549
550 END IF;
551 exit;
552
553
554 ELSE
555 IF (l_line_type_code = 'LINE') THEN
556 IF (PV_DEBUG_HIGH_ON) THEN
557 PVX_UTiLiTY_PVT.debug_message('Updating : ' || l_line_index || ' : ' || 'Enrl_req_id' || ' : ' ||
558 l_enrl_req_id_tbl(l_line_index) || ' : ' || 'Price' || ' : '
559 || l_adjusted_unit_price || ' : ' || 'currency' || ' : ' || l_currency_code ||
560 'Price_list' || l_price_list_header_id);
561 END IF;
562
563 l_enrl_req_rec.object_version_number := l_obj_ver_tbl(l_line_index);
564 l_enrl_req_rec.enrl_request_id := l_enrl_req_id_tbl(l_line_index);
565 l_enrl_req_rec.membership_fee := l_adjusted_unit_price;
566 l_enrl_req_rec.trans_curr_code := l_currency_code;
570 (
567
568
569 PV_Pg_Enrl_Requests_PVT.Update_Pg_Enrl_Requests
571 p_api_version_number => p_api_version_number,
572 p_init_msg_list => Fnd_Api.G_FALSE,
573 p_commit => Fnd_Api.G_FALSE,
574 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
575 x_return_status => x_return_status,
576 x_msg_count => x_msg_count,
577 x_msg_data => x_msg_data,
578 p_enrl_request_rec => l_enrl_req_rec
579 );
580
581 IF x_return_status = FND_API.g_ret_sts_error THEN
582 RAISE FND_API.g_exc_error;
583 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
584 RAISE FND_API.g_exc_unexpected_error;
585 END IF;
586
587
588 update pv_pg_enrl_requests
589 set MEMBERSHIP_FEE = l_adjusted_unit_price , TRANS_CURR_CODE = l_currency_code
590 where enrl_request_id = l_enrl_req_id_tbl(l_line_index);
591
592 ELSIF (l_line_type_code = 'ORDER') THEN
593 IF (PV_DEBUG_HIGH_ON) THEN
594 PVX_UTiLiTY_PVT.debug_message('ORDER LINE : ' || l_line_index || ' : ' || 'Price' || ' : '
595 || l_adjusted_unit_price || ' : ' || 'currency' || ' : ' || l_currency_code);
596 END IF;
597
598 END IF;
599 END IF;
600
601 END LOOP;
602
603 IF (l_has_price_error) THEN
604 RAISE FND_API.G_EXC_ERROR;
605 END IF;
606
607 Close c_price;
608
609 IF (PV_DEBUG_HIGH_ON) THEN
610 PVX_UTiLiTY_PVT.debug_message('After retrieving the price and updating price information ');
611 END IF;
612 END IF;
613
614 FND_MSG_PUB.Count_And_Get
615 ( p_encoded => FND_API.G_FALSE,
616 p_count => x_msg_count,
617 p_data => x_msg_data
618 );
619
620 IF FND_API.to_Boolean( p_commit ) THEN
621 COMMIT WORK;
622 END IF;
623
624
625 EXCEPTION
626 WHEN FND_API.G_EXC_ERROR THEN
627 ROLLBACK TO PRICE_REQUEST;
628 x_return_status := FND_API.G_RET_STS_ERROR;
629 FND_MSG_PUB.Count_And_Get (
630 p_encoded => FND_API.G_FALSE
631 ,p_count => x_msg_count
632 ,p_data => x_msg_data
633 );
634
635
636 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
637 ROLLBACK TO PRICE_REQUEST;
638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
639 -- Standard call to get message count and if count=1, get the message
640 FND_MSG_PUB.Count_And_Get (
641 p_encoded => FND_API.G_FALSE
642 ,p_count => x_msg_count
643 ,p_data => x_msg_data
644 );
645
646 WHEN OTHERS THEN
647 ROLLBACK TO PRICE_REQUEST;
648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
649 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
650 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
651 END IF;
652 FND_MSG_PUB.Count_And_Get (
653 p_encoded => FND_API.G_FALSE
654 ,p_count => x_msg_count
655 ,p_data => x_msg_data
656 );
657
658 END price_request;
659
660 END PV_PRICE_PVT;