[Home] [Help]
PACKAGE BODY: APPS.OZF_OM_VALIDATION_PVT
Source
1 PACKAGE BODY OZF_OM_VALIDATION_PVT AS
2 /* $Header: ozfvomvb.pls 120.2 2006/01/17 06:50:42 kdhulipa noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_OM_VALIDATION_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvomvb.pls';
6
7 OZF_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
8 OZF_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
9
10 /*=======================================================================*
11 | PROCEDURE
12 | Price_Item
13 |
14 | NOTES
15 | This API default the unit price for a item.
16 |
17 | HISTORY
18 | 14-NOV-2002 mchang Create.
19 *=======================================================================*/
20 PROCEDURE Price_Item(
21 p_cust_account_id IN NUMBER
22 ,p_order_type_id IN NUMBER
23 --,p_price_list_id IN NUMBER
24 ,p_currency_code IN VARCHAR2
25
26 ,x_return_status OUT NOCOPY VARCHAR2
27 ,x_msg_data OUT NOCOPY VARCHAR2
28 ,x_msg_count OUT NOCOPY NUMBER
29
30 ,p_x_item_tbl IN OUT NOCOPY claim_line_item_tbl_type
31 )
32 IS
33 l_api_version CONSTANT NUMBER := 1.0;
34 l_api_name CONSTANT VARCHAR2(30) := 'Price_Item';
35 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
36 l_return_status VARCHAR2(1);
37 /*
38 l_p_line_tbl QP_PREQ_GRP.line_tbl_type;
39 l_p_qual_tbl QP_PREQ_GRP.qual_tbl_type;
40 l_p_line_attr_tbl QP_PREQ_GRP.line_attr_tbl_type;
41 l_p_line_detail_tbl QP_PREQ_GRP.line_detail_tbl_type;
42 l_p_line_detail_qual_tbl QP_PREQ_GRP.line_detail_qual_tbl_type;
43 l_p_line_detail_attr_tbl QP_PREQ_GRP.line_detail_attr_tbl_type;
44 l_p_related_lines_tbl QP_PREQ_GRP.related_lines_tbl_type;
45 l_p_control_rec QP_PREQ_GRP.control_record_type;
46 l_x_line_tbl QP_PREQ_GRP.line_tbl_type;
47 l_x_line_qual QP_PREQ_GRP.qual_tbl_type;
48 l_x_line_attr_tbl QP_PREQ_GRP.line_attr_tbl_type;
49 l_x_line_detail_tbl QP_PREQ_GRP.line_detail_tbl_type;l_x_line_detail_qual_tbl QP_PREQ_GRP.line_detail_qual_tbl_type;
50 l_x_line_detail_attr_tbl QP_PREQ_GRP.line_detail_attr_tbl_type;
51 l_x_related_lines_tbl QP_PREQ_GRP.related_lines_tbl_type;
52 l_return_status VARCHAR2(240);
53 l_return_status_text VARCHAR2(240);
54 */
55
56 l_header_id NUMBER := TO_NUMBER(TO_CHAR(SYSDATE, 'HH24MISS'));
57 l_p_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
58 l_p_x_line_tbl OZF_ORDER_PRICE_PVT.LINE_REC_TBL_TYPE;
59 l_x_ldets_tbl OZF_ORDER_PRICE_PVT.LDETS_TBL_TYPE;
60 l_x_related_lines_tbl OZF_ORDER_PRICE_PVT.RLTD_LINE_TBL_TYPE;
61 l_price_list_id NUMBER;
62
63 i NUMBER;
64 idx_price_line NUMBER;
65 l_prod_name VARCHAR2(40);
66 l_uom_name VARCHAR2(25);
67 l_error BOOlEAN := FALSE;
68 l_inv_org_id NUMBER;
69
70 BEGIN
71 IF OZF_DEBUG_HIGH_ON THEN
72 OZF_Utility_PVT.debug_message(l_full_name||' : start');
73 END IF;
74
75 -- Initialize API return status to sucess
76 x_return_status := FND_API.g_ret_sts_success;
77
78 OZF_ORDER_PRICE_PVT.Purge_Pricing_Temp_table(
79 p_api_version => l_api_version
80 ,p_init_msg_list => FND_API.g_false
81 ,p_commit => FND_API.g_false
82 ,p_validation_level => FND_API.g_valid_level_full
83 ,x_return_status => l_return_status
84 ,x_msg_data => x_msg_data
85 ,x_msg_count => x_msg_count
86 );
87 IF l_return_status = FND_API.g_ret_sts_error THEN
88 IF OZF_DEBUG_LOW_ON THEN
89 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
90 FND_MESSAGE.Set_Token('TEXT','Expected error happened when calling OZF_ORDER_PRICE_PVT.Purge_Pricing_Temp_table().');
91 FND_MSG_PUB.Add;
92 END IF;
93 RAISE FND_API.g_exc_error;
94 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
95 IF OZF_DEBUG_LOW_ON THEN
96 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
97 FND_MESSAGE.Set_Token('TEXT','Unexpected error happened when calling OZF_ORDER_PRICE_PVT.Purge_Pricing_Temp_table().');
98 FND_MSG_PUB.Add;
99 END IF;
100 RAISE FND_API.g_exc_unexpected_error;
101 END IF;
102
103 l_price_list_id := FND_PROFILE.value('OZF_CLAIM_PRICE_LIST_ID');
104
105 IF OZF_DEBUG_HIGH_ON THEN
106 OZF_Utility_PVT.debug_message(l_full_name||' : price list id from profile = '||l_price_list_id);
107 END IF;
108
109 i := p_x_item_tbl.FIRST;
110 IF i IS NOT NULL THEN
111 OZF_ORDER_PRICE_PVT.G_HEADER_REC.header_id := l_header_id;
112 OZF_ORDER_PRICE_PVT.G_HEADER_REC.sold_to_org_id := p_cust_account_id;
113 OZF_ORDER_PRICE_PVT.G_HEADER_REC.order_type_id := p_order_type_id;
114
115
116 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL.delete();
117
118 LOOP
119 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(i).header_id := l_header_id;
120 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(i).inventory_item_id := p_x_item_tbl(i).item_id;
121 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(i).line_id := null;
122 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(i).ordered_quantity := p_x_item_tbl(i).quantity;
123 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(i).order_quantity_uom := p_x_item_tbl(i).quantity_uom;
124 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(i).price_list_id := null;
125 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(i).sold_to_org_id := p_cust_account_id;
126 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(i).request_date := sysdate;
127
128 l_p_x_line_tbl(i).LINE_INDEX := i;
129 l_p_x_line_tbl(i).LINE_ID := null;
130 l_p_x_line_tbl(i).LINE_TYPE_CODE := 'LINE';
131 l_p_x_line_tbl(i).PRICING_EFFECTIVE_DATE := sysdate;
132 l_p_x_line_tbl(i).ACTIVE_DATE_FIRST := sysdate;
133 l_p_x_line_tbl(i).ACTIVE_DATE_FIRST_TYPE := 'NO TYPE';
134 l_p_x_line_tbl(i).ACTIVE_DATE_SECOND := sysdate;
135 l_p_x_line_tbl(i).ACTIVE_DATE_SECOND_TYPE := 'NO TYPE';
136 l_p_x_line_tbl(i).LINE_QUANTITY := p_x_item_tbl(i).quantity;
137 l_p_x_line_tbl(i).LINE_UOM_CODE := p_x_item_tbl(i).quantity_uom;
138 l_p_x_line_tbl(i).REQUEST_TYPE_CODE := 'ONT';
139 l_p_x_line_tbl(i).PRICED_QUANTITY := null;
140 l_p_x_line_tbl(i).PRICED_UOM_CODE := null;
141 l_p_x_line_tbl(i).CURRENCY_CODE := p_currency_code;
142 l_p_x_line_tbl(i).UNIT_PRICE := null;
143 l_p_x_line_tbl(i).PERCENT_PRICE := null;
144 l_p_x_line_tbl(i).UOM_QUANTITY := null;
145 l_p_x_line_tbl(i).ADJUSTED_UNIT_PRICE := null;
146 l_p_x_line_tbl(i).UPD_ADJUSTED_UNIT_PRICE := null;
147 l_p_x_line_tbl(i).PROCESSED_FLAG := null;
148 l_p_x_line_tbl(i).PRICE_FLAG := 'Y';
149 l_p_x_line_tbl(i).PROCESSING_ORDER := null;
150 l_p_x_line_tbl(i).PRICING_STATUS_CODE := QP_PREQ_GRP.G_STATUS_UNCHANGED;
151 l_p_x_line_tbl(i).PRICING_STATUS_TEXT := null;
152 l_p_x_line_tbl(i).ROUNDING_FLAG := null;
153 l_p_x_line_tbl(i).ROUNDING_FACTOR := null;
154 l_p_x_line_tbl(i).QUALIFIERS_EXIST_FLAG := null;
155 l_p_x_line_tbl(i).PRICING_ATTRS_EXIST_FLAG:= null;
156 l_p_x_line_tbl(i).PRICE_LIST_ID := l_price_list_id;
157 l_p_x_line_tbl(i).PL_VALIDATED_FLAG := null;
158 l_p_x_line_tbl(i).PRICE_REQUEST_CODE := null;
159 l_p_x_line_tbl(i).USAGE_PRICING_TYPE := null;
160 EXIT WHEN i = p_x_item_tbl.LAST;
161 i := p_x_item_tbl.NEXT(i);
162 END LOOP;
163 END IF;
164
165 OZF_ORDER_PRICE_PVT.Get_Order_Price(
166 p_api_version => l_api_version
167 ,p_init_msg_list => FND_API.g_false
168 ,p_commit => FND_API.g_false
169 ,p_validation_level => FND_API.g_valid_level_full
170 ,x_return_status => l_return_status
171 ,x_msg_data => x_msg_data
172 ,x_msg_count => x_msg_count
173 ,p_control_rec => l_p_control_rec
174 ,xp_line_tbl => l_p_x_line_tbl
175 ,x_ldets_tbl => l_x_ldets_tbl
176 ,x_related_lines_tbl => l_x_related_lines_tbl
177 );
178 IF l_return_status = FND_API.g_ret_sts_error THEN
179 IF OZF_DEBUG_LOW_ON THEN
180 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
181 FND_MESSAGE.Set_Token('TEXT','Expected error happened when calling OZF_ORDER_PRICE_PVT.Get_Order_Price().');
182 FND_MSG_PUB.Add;
183 END IF;
184 RAISE FND_API.g_exc_error;
185 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
186 IF OZF_DEBUG_LOW_ON THEN
187 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
188 FND_MESSAGE.Set_Token('TEXT','Unexpected error happened when calling OZF_ORDER_PRICE_PVT.Get_Order_Price().');
189 FND_MSG_PUB.Add;
190 END IF;
191 RAISE FND_API.g_exc_unexpected_error;
192 END IF;
193
194 i := l_p_x_line_tbl.FIRST;
195 IF i IS NOT NULL THEN
196 LOOP
197 IF l_p_x_line_tbl(i).adjusted_unit_price IS NULL THEN
198 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
199 l_inv_org_id := FND_PROFILE.value('AMS_ITEM_ORGANIZATION_ID');
200 l_prod_name := OZF_UTILITY_PVT.get_product_name(
201 p_prod_level => 'PRODUCT'
202 ,p_prod_id => p_x_item_tbl(i).item_id
203 --,p_org_id => TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10))
204 ,p_org_id => l_inv_org_id
205
206 );
207 /*
208 IF l_p_x_line_tbl(i).pricing_status_code = 'IPL' THEN
209 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
210 FND_MESSAGE.Set_Token('TEXT','Invalid price list for pricing product '||l_prod_name);
211 FND_MSG_PUB.Add;
212 ELSIF l_p_x_line_tbl(i).pricing_status_code = 'GSA' THEN
213 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
214 FND_MESSAGE.Set_Token('TEXT','GSA violation for pricing product '||l_prod_name);
215 FND_MSG_PUB.Add;
216 ELSIF l_p_x_line_tbl(i).pricing_status_code = 'FER' THEN
217 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
218 FND_MESSAGE.Set_Token('TEXT','Error processing formula for pricing product '||l_prod_name);
219 FND_MSG_PUB.Add;
220 ELSIF l_p_x_line_tbl(i).pricing_status_code = 'CALC' THEN
221 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
222 FND_MESSAGE.Set_Token('TEXT','Error in calculation engine for pricing product '||l_prod_name);
223 FND_MSG_PUB.Add;
224 ELSIF l_p_x_line_tbl(i).pricing_status_code = 'UOM' THEN
225 l_uom_name := OZF_UTILITY_PVT.get_uom_name(p_uom_code => l_p_x_line_tbl(i).LINE_UOM_CODE);
226 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
227 FND_MESSAGE.Set_Token('TEXT','Failed to price using unit of measure('||l_uom_name||') for pricing product '||l_prod_name);
228 FND_MSG_PUB.Add;
229 ELSIF l_p_x_line_tbl(i).pricing_status_code = 'INVALID_UOM' THEN
230 l_uom_name := OZF_UTILITY_PVT.get_uom_name(p_uom_code => l_p_x_line_tbl(i).LINE_UOM_CODE);
231 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
232 FND_MESSAGE.Set_Token('TEXT','Invalid unit of measure('||l_uom_name||') for pricing product '||l_prod_name);
233 FND_MSG_PUB.Add;
234 ELSIF l_p_x_line_tbl(i).pricing_status_code = 'DUPLICATE_PRICE_LIST' THEN
235 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
236 FND_MESSAGE.Set_Token('TEXT','Duplicate price list for pricing product '||l_prod_name);
237 FND_MSG_PUB.Add;
238 ELSIF l_p_x_line_tbl(i).pricing_status_code = 'INVALID_UOM_CONV' THEN
239 l_uom_name := OZF_UTILITY_PVT.get_uom_name(p_uom_code => l_p_x_line_tbl(i).LINE_UOM_CODE);
240 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
241 FND_MESSAGE.Set_Token('TEXT','Unit of measure('||l_uom_name||') conversion is not found for pricing product '||l_prod_name);
242 FND_MSG_PUB.Add;
243 ELSIF l_p_x_line_tbl(i).pricing_status_code = 'INVALID_INCOMP' THEN
244 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
245 FND_MESSAGE.Set_Token('TEXT','Could not resolve incompatibility for pricing product '||l_prod_name);
246 FND_MSG_PUB.Add;
247 ELSIF l_p_x_line_tbl(i).pricing_status_code = 'INVALID_BEST_PRICE' THEN
248 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
249 FND_MESSAGE.Set_Token('TEXT','Could not resolve best price for product '||l_prod_name);
250 FND_MSG_PUB.Add;
251 END IF;
252 */
253 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
254 FND_MESSAGE.Set_Token('TEXT',l_p_x_line_tbl(i).pricing_status_text);
255 FND_MSG_PUB.Add;
256
257 FND_MESSAGE.set_name('OZF', 'OZF_SETL_DEF_PROD_PRICE_ERR');
258 FND_MESSAGE.set_token('PROD', l_prod_name);
259 FND_MSG_PUB.add;
260 END IF;
261 l_error := TRUE;
262 ELSE
263 p_x_item_tbl(i).rate := l_p_x_line_tbl(i).adjusted_unit_price; --adjusted_unit_price??
264 END IF;
265 EXIT WHEN i = l_p_x_line_tbl.LAST;
266 i := l_p_x_line_tbl.NEXT(i);
267 END LOOP;
268 END IF;
269
270 IF l_error THEN
271 RAISE FND_API.G_EXC_ERROR;
272 END IF;
273
274 /*
275 -- Setting up the control record variables
276 l_p_control_rec.pricing_event := 'LINE';
277 l_p_control_rec.calculate_flag := 'Y';
278 l_p_control_rec.simulation_flag := 'N';
279
280 i := p_x_item_tbl.FIRST;
281 IF i IS NOT NULL THEN
282 LOOP
283 -- Request Line (Order Line) Information
284 l_p_line_tbl(i).request_type_code :='ONT';
285 l_p_line_tbl(i).line_id :=9999;
286 l_p_line_tbl(i).line_Index := i;
287 l_p_line_tbl(i).line_type_code := 'LINE';
288 l_p_line_tbl(i).pricing_effective_date := sysdate;
289 l_p_line_tbl(i).active_date_first := sysdate;
290 l_p_line_tbl(i).active_date_second := sysdate;
291 l_p_line_tbl(i).active_date_first_type := 'NO TYPE';
292 l_p_line_tbl(i).active_date_second_type :='NO TYPE';
293 l_p_line_tbl(i).line_quantity := p_x_item_tbl(i).quantity;
294 l_p_line_tbl(i).line_uom_code := p_x_item_tbl(i).quantity_uom;
295 l_p_line_tbl(i).currency_code := p_currency_code;
296 l_p_line_tbl(i).price_flag := 'Y';
297
298 -- Set Pricing Attributes
299 l_p_line_attr_tbl(idx_line_attr).line_index := i;
300 l_p_line_attr_tbl(idx_line_attr).pricing_context :='ITEM';
301 l_p_line_attr_tbl(idx_line_attr).pricing_attribute :='PRICING_ATTRIBUTE1';
302 l_p_line_attr_tbl(idx_line_attr).pricing_attr_value_from := p_x_item_tbl(i).item_id; -- Inventory Item Id
303 l_p_line_attr_tbl(idx_line_attr).validated_flag :='N';
304 idx_line_attr := idx_line_attr + 1;
305
306 -- Set Qualifiers (Price_List_Id)
307 l_p_qual_tbl(idx_qual).line_index := i;
308 l_p_qual_tbl(idx_qual).qualifier_context :='MODLIST';
309 l_p_qual_tbl(idx_qual).qualifier_attribute :='QUALIFIER_ATTRIBUTE4';
310 l_p_qual_tbl(idx_qual).qualifier_attr_value_from :=p_price_list_id; -- Price List Id
311 l_p_qual_tbl(idx_qual).comparison_operator_code := '=';
312 l_p_qual_tbl(idx_qual).validated_flag :='Y';
313 idx_qual := idx_qual + 1;
314 -- Set Qualifiers (Customer)
315 l_p_qual_tbl(idx_qual).line_index := i;
316 l_p_qual_tbl(idx_qual).qualifier_context :='CUSTOMER';
317 l_p_qual_tbl(idx_qual).qualifier_attribute :='QUALIFIER_ATTRIBUTE2';
318 l_p_qual_tbl(idx_qual).qualifier_attr_value_from :=p_cust_account_id;
319 l_p_qual_tbl(idx_qual).comparison_operator_code := '=';
320 l_p_qual_tbl(idx_qual).validated_flag :='Y';
321 idx_qual := idx_qual + 1;
322
323 EXIT WHEN i = p_x_item_tbl.LAST;
324 i := p_x_item_tbl.NEXT(i);
325 END LOOP;
326 END IF;
327
328 -- Call Pricing Engine
329 QP_PREQ_GRP.PRICE_REQUEST(
330 l_p_line_tbl,
331 l_p_qual_tbl,
332 l_p_line_attr_tbl,
333 l_p_line_detail_tbl,
334 l_p_line_detail_qual_tbl,
335 l_p_line_detail_attr_tbl,
336 l_p_related_lines_tbl,
337 l_p_control_rec,
338 l_x_line_tbl,
339 l_x_line_qual,
340 l_x_line_attr_tbl,
341 l_x_line_detail_tbl,
342 l_x_line_detail_qual_tbl,
343 l_x_line_detail_attr_tbl,
344 l_x_related_lines_tbl,
345 l_return_status,
346 l_return_status_text
347 );
348 IF l_return_status = FND_API.g_ret_sts_error THEN
349 RAISE FND_API.g_exc_error;
350 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
351 RAISE FND_API.g_exc_unexpected_error;
352 END IF;
353
354 i := l_x_line_tbl.FIRST;
355 IF i IS NOT NULL THEN
356 LOOP
357 p_x_item_tbl(i).rate := l_x_line_tbl(i).unit_price; --adjusted_unit_price??
358 EXIT WHEN i = l_x_line_tbl.LAST;
359 i := l_x_line_tbl.NEXT(i);
360 END LOOP;
361 END IF;
362 */
363
364 IF OZF_DEBUG_HIGH_ON THEN
365 OZF_Utility_PVT.debug_message(l_full_name||' : end');
366 END IF;
367 EXCEPTION
368 WHEN FND_API.G_EXC_ERROR THEN
369 x_return_status := FND_API.G_RET_STS_ERROR;
370 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
371 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
372 WHEN OTHERS THEN
373 IF OZF_DEBUG_LOW_ON THEN
374 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
375 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
376 FND_MSG_PUB.Add;
377 END IF;
378 x_return_status := FND_API.g_ret_sts_unexp_error;
379
380 END Price_Item;
381
382
383 /*=======================================================================*
384 | PROCEDURE
385 | Price_Invoice_Line
386 |
387 | NOTES
388 | This API default the unit price for a invoice line.
389 |
390 | HISTORY
391 | 14-NOV-2002 mchang Create.
392 *=======================================================================*/
393 PROCEDURE Price_Invoice_Line(
394 x_return_status OUT NOCOPY VARCHAR2
395 ,x_msg_data OUT NOCOPY VARCHAR2
396 ,x_msg_count OUT NOCOPY NUMBER
397
398 ,p_x_inv_line_tbl IN OUT NOCOPY claim_line_item_tbl_type
399 )
400 IS
401 l_api_version CONSTANT NUMBER := 1.0;
402 l_api_name CONSTANT VARCHAR2(30) := 'Price_Invoice_Line';
403 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
404
405 CURSOR csr_inv_line_price(cv_customer_trx_line_id IN NUMBER) IS
406 SELECT inventory_item_id
407 , unit_selling_price
408 , uom_code
409 FROM ra_customer_trx_lines
410 WHERE customer_trx_line_id = cv_customer_trx_line_id;
411
412 i NUMBER;
413
414 BEGIN
415 IF OZF_DEBUG_HIGH_ON THEN
416 OZF_Utility_PVT.debug_message(l_full_name||' : start');
417 END IF;
418
419 -- Initialize API return status to sucess
420 x_return_status := FND_API.g_ret_sts_success;
421
422 i := p_x_inv_line_tbl.FIRST;
423 IF i IS NOT NULL THEN
424 LOOP
425 OPEN csr_inv_line_price(p_x_inv_line_tbl(i).source_object_line_id);
426 FETCH csr_inv_line_price INTO p_x_inv_line_tbl(i).item_id
427 , p_x_inv_line_tbl(i).rate
428 , p_x_inv_line_tbl(i).quantity_uom;
429 CLOSE csr_inv_line_price;
430
431 EXIT WHEN i = p_x_inv_line_tbl.LAST;
432 i := p_x_inv_line_tbl.NEXT(i);
433 END LOOP;
434 END IF;
435
436 IF OZF_DEBUG_HIGH_ON THEN
437 OZF_Utility_PVT.debug_message(l_full_name||' : end');
438 END IF;
439 EXCEPTION
440 WHEN FND_API.G_EXC_ERROR THEN
441 x_return_status := FND_API.G_RET_STS_ERROR;
442 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
444 WHEN OTHERS THEN
445 IF OZF_DEBUG_LOW_ON THEN
446 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
447 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
448 FND_MSG_PUB.Add;
449 END IF;
450 x_return_status := FND_API.g_ret_sts_unexp_error;
451
452 END Price_Invoice_Line;
453
454
455 /*=======================================================================*
456 | PROCEDURE
457 | Price_Order_Line
458 |
459 | NOTES
460 | This API default the unit price for a order line.
461 |
462 | HISTORY
463 | 14-NOV-2002 mchang Create.
464 *=======================================================================*/
465 PROCEDURE Price_Order_Line(
466 x_return_status OUT NOCOPY VARCHAR2
467 ,x_msg_data OUT NOCOPY VARCHAR2
468 ,x_msg_count OUT NOCOPY NUMBER
469
470 ,p_x_ord_line_tbl IN OUT NOCOPY claim_line_item_tbl_type
471 )
472 IS
473 l_api_version CONSTANT NUMBER := 1.0;
474 l_api_name CONSTANT VARCHAR2(30) := 'Price_Order_Line';
475 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
476
477 CURSOR csr_ord_line_price(cv_order_line_id IN NUMBER) IS
478 SELECT inventory_item_id
479 , unit_selling_price
480 , order_quantity_uom
481 FROM oe_order_lines
482 WHERE line_id = cv_order_line_id;
483
484 i NUMBER;
485
486 BEGIN
487 IF OZF_DEBUG_HIGH_ON THEN
488 OZF_Utility_PVT.debug_message(l_full_name||' : start');
489 END IF;
490
491 -- Initialize API return status to sucess
492 x_return_status := FND_API.g_ret_sts_success;
493
494 i := p_x_ord_line_tbl.FIRST;
495 IF i IS NOT NULL THEN
496 LOOP
497 OPEN csr_ord_line_price(p_x_ord_line_tbl(i).source_object_line_id);
498 FETCH csr_ord_line_price INTO p_x_ord_line_tbl(i).item_id
499 , p_x_ord_line_tbl(i).rate
500 , p_x_ord_line_tbl(i).quantity_uom;
501 CLOSE csr_ord_line_price;
502
503 EXIT WHEN i = p_x_ord_line_tbl.LAST;
504 i := p_x_ord_line_tbl.NEXT(i);
505 END LOOP;
506 END IF;
507
508 IF OZF_DEBUG_HIGH_ON THEN
509 OZF_Utility_PVT.debug_message(l_full_name||' : end');
510 END IF;
511 EXCEPTION
512 WHEN FND_API.G_EXC_ERROR THEN
513 x_return_status := FND_API.G_RET_STS_ERROR;
514 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
515 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
516 WHEN OTHERS THEN
517 IF OZF_DEBUG_LOW_ON THEN
518 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
519 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
520 FND_MSG_PUB.Add;
521 END IF;
522 x_return_status := FND_API.g_ret_sts_unexp_error;
523
524 END Price_Order_Line;
525
526
527 /*=======================================================================*
528 | PROCEDURE
529 | Get_Default_Order_Type
530 |
531 | NOTES
532 | This API default order_type_id for Claim.
533 |
534 | HISTORY
535 | 16-JAN-2003 mchang Create.
536 *=======================================================================*/
537 PROCEDURE Get_Default_Order_Type(
538 p_api_version IN NUMBER
539 ,p_init_msg_list IN VARCHAR2
540 ,p_validation_level IN NUMBER
541
542 ,x_return_status OUT NOCOPY VARCHAR2
543 ,x_msg_data OUT NOCOPY VARCHAR2
544 ,x_msg_count OUT NOCOPY NUMBER
545
546 ,p_reason_code_id IN NUMBER
547 ,p_claim_type_id IN NUMBER
548 ,p_set_of_books_id IN NUMBER
549 ,x_order_type_id OUT NOCOPY NUMBER
550 )
551 IS
552 l_api_version CONSTANT NUMBER := 1.0;
553 l_api_name CONSTANT VARCHAR2(30) := 'Get_Default_Order_Type';
554 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
555 l_return_status VARCHAR2(1);
556
557 CURSOR csr_reason_rma_trx_type(cv_reason_code_id IN NUMBER) IS
558 SELECT order_type_id
559 FROM ozf_reason_codes_vl
560 WHERE reason_code_id = cv_reason_code_id;
561
562 CURSOR csr_claim_type_rma_trx_type(cv_claim_type_id IN NUMBER) IS
563 SELECT order_type_id
564 FROM ozf_claim_types_vl
565 WHERE claim_type_id = cv_claim_type_id;
566
567 CURSOR csr_sys_param_rma_trx_type(cv_set_of_books_id IN NUMBER) IS
568 SELECT order_type_id
569 FROM ozf_sys_parameters
570 WHERE set_of_books_id = cv_set_of_books_id;
571
572 BEGIN
573 IF OZF_DEBUG_HIGH_ON THEN
574 OZF_Utility_PVT.debug_message(l_full_name||' : start');
575 END IF;
576
577 -- The following hierarchy is used to determine the default RMA transaction type,
578 -- stopping when one is found
579 -- 1. Reason (ozf_reason_codes_all_b.order_type_id)
580 -- 2. Claim Type (ozf_claim_types_all_b.order_type_id)
581 -- 3. System Parameter (ozf_sys_parameters_all.order_type_id)
582
583 OPEN csr_reason_rma_trx_type(p_reason_code_id);
584 FETCH csr_reason_rma_trx_type INTO x_order_type_id;
585 CLOSE csr_reason_rma_trx_type;
586
587 IF x_order_type_id IS NULL THEN
588 OPEN csr_claim_type_rma_trx_type(p_claim_type_id);
589 FETCH csr_claim_type_rma_trx_type INTO x_order_type_id;
590 CLOSE csr_claim_type_rma_trx_type;
591 END IF;
592
593 IF x_order_type_id IS NULL THEN
594 OPEN csr_sys_param_rma_trx_type(p_set_of_books_id);
595 FETCH csr_sys_param_rma_trx_type INTO x_order_type_id;
596 CLOSE csr_sys_param_rma_trx_type;
597 END IF;
598
599 IF x_order_type_id IS NULL THEN
600 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
601 FND_MESSAGE.set_name('OZF', 'OZF_SETL_MISSING_RMA_TRX_TYPE');
602 FND_MSG_PUB.add;
603 END IF;
604 RAISE FND_API.G_EXC_ERROR;
605 END IF;
606
607 IF OZF_DEBUG_HIGH_ON THEN
608 OZF_Utility_PVT.debug_message(l_full_name||' : end');
609 END IF;
610 EXCEPTION
611 WHEN FND_API.G_EXC_ERROR THEN
612 x_return_status := FND_API.G_RET_STS_ERROR;
613 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
614 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
615 WHEN OTHERS THEN
616 IF OZF_DEBUG_LOW_ON THEN
617 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
618 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
619 FND_MSG_PUB.Add;
620 END IF;
621 x_return_status := FND_API.g_ret_sts_unexp_error;
622
623 END Get_Default_Order_Type;
624
625
626 /*=======================================================================*
627 | PROCEDURE
628 | Default_Claim_Line
629 |
630 | NOTES
631 | This API default claim line recored for RMA settlement method.
632 |
633 | HISTORY
634 | 14-NOV-2002 mchang Create.
635 *=======================================================================*/
636 PROCEDURE Default_Claim_Line(
637 p_api_version IN NUMBER
638 ,p_init_msg_list IN VARCHAR2
639 ,p_validation_level IN NUMBER
640
641 ,x_return_status OUT NOCOPY VARCHAR2
642 ,x_msg_data OUT NOCOPY VARCHAR2
643 ,x_msg_count OUT NOCOPY NUMBER
644
645 ,p_x_claim_line_rec IN OUT NOCOPY OZF_CLAIM_LINE_PVT.claim_line_rec_type
646 )
647 IS
648 l_api_version CONSTANT NUMBER := 1.0;
649 l_api_name CONSTANT VARCHAR2(30) := 'Default_Claim_Line';
650 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
651 l_return_status VARCHAR2(1);
652 /*
653 CURSOR csr_claim(p_claim_id IN NUMBER) IS
654 SELECT cust_account_id
655 , currency_code
656 FROM ozf_claims
657 WHERE claim_id = p_claim_id;
658
659 l_cust_account_id NUMBER;
660 l_claim_currency VARCHAR2(15);
661 l_price_list_id NUMBER := 1000;
662 l_line_item_tbl claim_line_item_tbl_type;
663 */
664 l_claim_line_tbl OZF_CLAIM_LINE_PVT.claim_line_tbl_type;
665
666 BEGIN
667 IF OZF_DEBUG_HIGH_ON THEN
668 OZF_Utility_PVT.debug_message(l_full_name||' : start');
669 END IF;
670
671 -- Initialize API return status to sucess
672 x_return_status := FND_API.g_ret_sts_success;
673
674 l_claim_line_tbl(1) := p_x_claim_line_rec;
675
676 Default_Claim_Line_Tbl(
677 p_api_version => l_api_version
678 ,p_init_msg_list => FND_API.g_false
679 ,p_validation_level => FND_API.g_valid_level_full
680 ,x_return_status => l_return_status
681 ,x_msg_data => x_msg_data
682 ,x_msg_count => x_msg_count
683 ,p_x_claim_line_tbl => l_claim_line_tbl
684 );
685 IF l_return_status = FND_API.g_ret_sts_error THEN
686 RAISE FND_API.g_exc_error;
687 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
688 RAISE FND_API.g_exc_unexpected_error;
689 END IF;
690
691 /*
692 IF p_x_claim_line_rec.source_object_line_id IS NOT NULL AND
693 p_x_claim_line_rec.source_object_class = 'INVOICE' THEN
694 l_line_item_tbl(1).source_object_class := p_x_claim_line_rec.source_object_class;
695 l_line_item_tbl(1).source_object_id := p_x_claim_line_rec.source_object_id;
696 l_line_item_tbl(1).source_object_line_id := p_x_claim_line_rec.source_object_line_id;
697
698 Price_Invoice_Line(
699 x_return_status => l_return_status
700 ,x_msg_data => x_msg_data
701 ,x_msg_count => x_msg_count
702 ,p_x_inv_line_tbl => l_line_item_tbl
703 );
704 IF l_return_status = FND_API.g_ret_sts_error THEN
705 RAISE FND_API.g_exc_error;
706 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
707 RAISE FND_API.g_exc_unexpected_error;
708 END IF;
709
710 ELSIF p_x_claim_line_rec.item_id IS NOT NULL THEN
711 l_line_item_tbl(1).claim_line_index := 1;
712 l_line_item_tbl(1).item_id := p_x_claim_line_rec.item_id;
713 l_line_item_tbl(1).quantity := p_x_claim_line_rec.quantity;
714 l_line_item_tbl(1).quantity_uom := p_x_claim_line_rec.quantity_uom;
715 l_line_item_tbl(1).currency_code := p_x_claim_line_rec.currency_code;
716
717 OPEN csr_claim(p_x_claim_line_rec.claim_id);
718 FETCH csr_claim INTO l_cust_account_id, l_claim_currency;
719 CLOSE csr_claim;
720
721 Price_Item(
722 p_cust_account_id => l_cust_account_id
723 ,p_price_list_id => l_price_list_id
724 ,p_currency_code => l_claim_currency
725 ,x_return_status => l_return_status
726 ,x_msg_data => x_msg_data
727 ,x_msg_count => x_msg_count
728
729 ,p_x_item_tbl => l_line_item_tbl
730 );
731 IF l_return_status = FND_API.g_ret_sts_error THEN
732 RAISE FND_API.g_exc_error;
733 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
734 RAISE FND_API.g_exc_unexpected_error;
735 END IF;
736
737 END IF;
738 */
739 IF l_claim_line_tbl(1).rate IS NOT NULL THEN
740 p_x_claim_line_rec.rate := l_claim_line_tbl(1).rate;
741 END IF;
742
743 IF OZF_DEBUG_HIGH_ON THEN
744 OZF_Utility_PVT.debug_message(l_full_name||' : end');
745 END IF;
746 EXCEPTION
747 WHEN FND_API.G_EXC_ERROR THEN
748 x_return_status := FND_API.G_RET_STS_ERROR;
749 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
750 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
751 WHEN OTHERS THEN
752 IF OZF_DEBUG_LOW_ON THEN
753 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
754 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
755 FND_MSG_PUB.Add;
756 END IF;
757 x_return_status := FND_API.g_ret_sts_unexp_error;
758
759 END Default_Claim_Line;
760
761
762 /*=======================================================================*
763 | PROCEDURE
764 | Default_Claim_Line_Tbl
765 |
766 | NOTES
767 | This API default claim line table for RMA settlement method.
768 |
769 | HISTORY
770 | 14-NOV-2002 mchang Create.
771 *=======================================================================*/
772 PROCEDURE Default_Claim_Line_Tbl(
773 p_api_version IN NUMBER
774 ,p_init_msg_list IN VARCHAR2
775 ,p_validation_level IN NUMBER
776
777 ,x_return_status OUT NOCOPY VARCHAR2
778 ,x_msg_data OUT NOCOPY VARCHAR2
779 ,x_msg_count OUT NOCOPY NUMBER
780
781 ,p_x_claim_line_tbl IN OUT NOCOPY OZF_CLAIM_LINE_PVT.claim_line_tbl_type
782 )
783 IS
784 l_api_version CONSTANT NUMBER := 1.0;
785 l_api_name CONSTANT VARCHAR2(30) := 'Default_Claim_Line_Tbl';
786 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
787 l_return_status VARCHAR2(1);
788
789 CURSOR csr_claim(p_claim_id IN NUMBER) IS
790 SELECT cust_account_id
791 , order_type_id
792 , currency_code
793 FROM ozf_claims
794 WHERE claim_id = p_claim_id;
795
796 CURSOR csr_product_name(cv_item_id IN NUMBER, cv_org_id IN NUMBER) IS
797 SELECT description
798 FROM mtl_system_items_vl
799 WHERE inventory_item_id = cv_item_id
800 AND organization_id = cv_org_id;
801
802 CURSOR csr_line_old_rate(cv_claim_line_id IN NUMBER) IS
803 SELECT rate
804 FROM ozf_claim_lines
805 WHERE claim_line_id = cv_claim_line_id;
806
807 l_csr_product_name csr_product_name%ROWTYPE;
808 l_cust_account_id NUMBER;
809 l_order_type_id NUMBER;
810 l_claim_currency VARCHAR2(15);
811 --l_price_list_id NUMBER := 1000;
812 l_item_tbl claim_line_item_tbl_type;
813 l_inv_line_tbl claim_line_item_tbl_type;
814 l_ord_line_tbl claim_line_item_tbl_type;
815 i NUMBER;
816 idx_item NUMBER := 1;
817 idx_inv_line NUMBER := 1;
818 idx_ord_line NUMBER := 1;
819 l_org_id NUMBER;
820 l_line_old_rate NUMBER;
821
822 BEGIN
823 IF OZF_DEBUG_HIGH_ON THEN
824 OZF_Utility_PVT.debug_message(l_full_name||' : start');
825 END IF;
826
827 -- Initialize API return status to sucess
828 x_return_status := FND_API.g_ret_sts_success;
829
830 --l_org_id := TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10));
831
832 ----------------------------
833 -- Default RMA Line Price --
834 ----------------------------
835 i := p_x_claim_line_tbl.FIRST;
836
837 IF OZF_DEBUG_HIGH_ON THEN
838 OZF_Utility_PVT.debug_message(l_full_name||' p_x_claim_line_tbl.FIRST='||i);
839 END IF;
840
841 IF i IS NOT NULL THEN
842 OPEN csr_claim(p_x_claim_line_tbl(1).claim_id);
843 FETCH csr_claim INTO l_cust_account_id
844 , l_order_type_id
845 , l_claim_currency;
846 CLOSE csr_claim;
847
848 LOOP
849 IF OZF_DEBUG_HIGH_ON THEN
850 OZF_Utility_PVT.debug_message(l_full_name||' : p_x_claim_line_tbl('||i||').source_object_class='||p_x_claim_line_tbl(i).source_object_class);
851 OZF_Utility_PVT.debug_message(l_full_name||' : p_x_claim_line_tbl('||i||').source_object_id='||p_x_claim_line_tbl(i).source_object_id);
852 OZF_Utility_PVT.debug_message(l_full_name||' : p_x_claim_line_tbl('||i||').source_object_line_id='||p_x_claim_line_tbl(i).source_object_line_id);
853 OZF_Utility_PVT.debug_message(l_full_name||' : p_x_claim_line_tbl('||i||').item_type='||p_x_claim_line_tbl(i).item_type);
854 OZF_Utility_PVT.debug_message(l_full_name||' : p_x_claim_line_tbl('||i||').item_id='||p_x_claim_line_tbl(i).item_id);
855 END IF;
856 IF ( p_x_claim_line_tbl(i).source_object_line_id IS NOT NULL AND
857 p_x_claim_line_tbl(i).source_object_line_id <> FND_API.g_miss_num
858 ) OR
859 ( p_x_claim_line_tbl(i).item_type = 'PRODUCT' AND
860 p_x_claim_line_tbl(i).item_id IS NOT NULL AND
861 p_x_claim_line_tbl(i).item_id <> FND_API.g_miss_num AND
862 p_x_claim_line_tbl(i).rate IS NULL
863 ) THEN
864 -- Quantity is required for pricing item
865 IF p_x_claim_line_tbl(i).quantity IS NULL THEN
866 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
867 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_QUANTITY_REQ');
868 FND_MSG_PUB.add;
869 END IF;
870 RAISE FND_API.G_EXC_ERROR;
871 END IF;
872
873 IF p_x_claim_line_tbl(i).source_object_line_id IS NOT NULL AND
874 p_x_claim_line_tbl(i).source_object_line_id <> FND_API.g_miss_num AND
875 p_x_claim_line_tbl(i).rate IS NOT NULL THEN
876 IF p_x_claim_line_tbl(i).source_object_class = 'INVOICE' THEN
877 l_inv_line_tbl(idx_inv_line).claim_line_index := i;
878 l_inv_line_tbl(idx_inv_line).source_object_class := p_x_claim_line_tbl(i).source_object_class;
879 l_inv_line_tbl(idx_inv_line).source_object_id := p_x_claim_line_tbl(i).source_object_id;
880 l_inv_line_tbl(idx_inv_line).source_object_line_id := p_x_claim_line_tbl(i).source_object_line_id;
881 idx_inv_line := idx_inv_line + 1;
882 ELSIF p_x_claim_line_tbl(i).source_object_class = 'ORDER' THEN
883 l_ord_line_tbl(idx_ord_line).claim_line_index := i;
884 l_ord_line_tbl(idx_ord_line).source_object_class := p_x_claim_line_tbl(i).source_object_class;
885 l_ord_line_tbl(idx_ord_line).source_object_id := p_x_claim_line_tbl(i).source_object_id;
886 l_ord_line_tbl(idx_ord_line).source_object_line_id := p_x_claim_line_tbl(i).source_object_line_id;
887 idx_ord_line := idx_ord_line + 1;
888 END IF;
889 ELSIF p_x_claim_line_tbl(i).item_type = 'PRODUCT' AND
890 p_x_claim_line_tbl(i).item_id IS NOT NULL AND
891 p_x_claim_line_tbl(i).item_id <> FND_API.g_miss_num AND
892 p_x_claim_line_tbl(i).rate IS NULL THEN
893 l_item_tbl(idx_item).claim_line_index := i;
894 l_item_tbl(idx_item).item_id := p_x_claim_line_tbl(i).item_id;
895 l_item_tbl(idx_item).quantity := p_x_claim_line_tbl(i).quantity;
896 -- UOM is required for pricing item
897 IF p_x_claim_line_tbl(i).quantity_uom IS NULL THEN
898 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
899 l_org_id := FND_PROFILE.value('AMS_ITEM_ORGANIZATION_ID');
900 OPEN csr_product_name(p_x_claim_line_tbl(i).item_id, l_org_id);
901 --OPEN csr_product_name(p_x_claim_line_tbl(i).item_id, l_org_id);
902 FETCH csr_product_name INTO l_csr_product_name;
903 CLOSE csr_product_name;
904 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_UOM_REQ');
905 FND_MESSAGE.set_token('ITEM', l_csr_product_name.description);
906 FND_MSG_PUB.add;
907 END IF;
908 RAISE FND_API.G_EXC_ERROR;
909 ELSE
910 l_item_tbl(idx_item).quantity_uom := p_x_claim_line_tbl(i).quantity_uom;
911 END IF;
912 l_item_tbl(idx_item).currency_code := l_claim_currency;
913 idx_item := idx_item + 1;
914 END IF;
915 ELSIF p_x_claim_line_tbl(i).rate IS NOT NULL AND
916 p_x_claim_line_tbl(i).rate <> FND_API.g_miss_num THEN
917 -- create mode
918 IF p_x_claim_line_tbl(i).claim_line_id IS NULL OR
919 p_x_claim_line_tbl(i).claim_line_id = FND_API.g_miss_num THEN
920 p_x_claim_line_tbl(i).payment_status := 'N'; --set calculate_price_flag
921
922 -- update mode
923 ELSE
924 IF p_x_claim_line_tbl(i).payment_status = 'Y' THEN
925 OPEN csr_line_old_rate(p_x_claim_line_tbl(i).claim_line_id);
926 FETCH csr_line_old_rate INTO l_line_old_rate;
927 CLOSE csr_line_old_rate;
928 IF p_x_claim_line_tbl(i).rate <> l_line_old_rate THEN
929 p_x_claim_line_tbl(i).payment_status := 'N';
930 END IF;
931 END IF;
932 END IF;
933 END IF;
934 EXIT WHEN i = p_x_claim_line_tbl.LAST;
935 i := p_x_claim_line_tbl.NEXT(i);
936 END LOOP;
937 END IF;
938
939 IF OZF_DEBUG_HIGH_ON THEN
940 OZF_Utility_PVT.debug_message(l_full_name||' : count-inv line tbl='||l_inv_line_tbl.COUNT);
941 OZF_Utility_PVT.debug_message(l_full_name||' : count-ord line tbl='||l_ord_line_tbl.COUNT);
942 OZF_Utility_PVT.debug_message(l_full_name||' : count-prd line tbl='||l_item_tbl.COUNT);
943 END IF;
944
945 ------ Price Item -------
946 i := l_item_tbl.FIRST;
947 IF i IS NOT NULL THEN
948 Price_Item(
949 p_cust_account_id => l_cust_account_id
950 ,p_order_type_id => l_order_type_id
951 --,p_price_list_id => l_price_list_id
952 ,p_currency_code => l_claim_currency
953
954 ,x_return_status => l_return_status
955 ,x_msg_data => x_msg_data
956 ,x_msg_count => x_msg_count
957
958 ,p_x_item_tbl => l_item_tbl
959 );
960 IF l_return_status = FND_API.g_ret_sts_error THEN
961 RAISE FND_API.g_exc_error;
962 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
963 RAISE FND_API.g_exc_unexpected_error;
964 END IF;
965
966 LOOP
967 p_x_claim_line_tbl(l_item_tbl(i).claim_line_index).rate := l_item_tbl(i).rate;
968 p_x_claim_line_tbl(l_item_tbl(i).claim_line_index).payment_status := 'Y'; -- calculate_price_flag
969 EXIT WHEN i = l_item_tbl.LAST;
970 i := l_item_tbl.NEXT(i);
971 END LOOP;
972 END IF;
973
974 ------ Price Invoice Line -------
975 i := l_inv_line_tbl.FIRST;
976 IF i IS NOT NULL THEN
977 Price_Invoice_Line(
978 x_return_status => l_return_status
979 ,x_msg_data => x_msg_data
980 ,x_msg_count => x_msg_count
981 ,p_x_inv_line_tbl => l_inv_line_tbl
982 );
983 IF l_return_status = FND_API.g_ret_sts_error THEN
984 RAISE FND_API.g_exc_error;
985 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
986 RAISE FND_API.g_exc_unexpected_error;
987 END IF;
988
989 LOOP
990 p_x_claim_line_tbl(l_inv_line_tbl(i).claim_line_index).item_id := l_inv_line_tbl(i).item_id;
991 p_x_claim_line_tbl(l_inv_line_tbl(i).claim_line_index).rate := l_inv_line_tbl(i).rate;
992 p_x_claim_line_tbl(l_inv_line_tbl(i).claim_line_index).quantity_uom := l_inv_line_tbl(i).quantity_uom;
993 p_x_claim_line_tbl(l_inv_line_tbl(i).claim_line_index).payment_status := 'Y'; -- calculate_price_flag
994 EXIT WHEN i = l_inv_line_tbl.LAST;
995 i := l_inv_line_tbl.NEXT(i);
996 END LOOP;
997 END IF;
998
999 ------ Price Order Line -------
1000 i := l_ord_line_tbl.FIRST;
1001 IF i IS NOT NULL THEN
1002 Price_Order_Line(
1003 x_return_status => l_return_status
1004 ,x_msg_data => x_msg_data
1005 ,x_msg_count => x_msg_count
1006 ,p_x_ord_line_tbl => l_ord_line_tbl
1007 );
1008 IF l_return_status = FND_API.g_ret_sts_error THEN
1009 RAISE FND_API.g_exc_error;
1010 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1011 RAISE FND_API.g_exc_unexpected_error;
1012 END IF;
1013
1014 LOOP
1015 p_x_claim_line_tbl(l_ord_line_tbl(i).claim_line_index).item_id := l_ord_line_tbl(i).item_id;
1016 p_x_claim_line_tbl(l_ord_line_tbl(i).claim_line_index).rate := l_ord_line_tbl(i).rate;
1017 p_x_claim_line_tbl(l_ord_line_tbl(i).claim_line_index).quantity_uom := l_ord_line_tbl(i).quantity_uom;
1018 p_x_claim_line_tbl(l_ord_line_tbl(i).claim_line_index).payment_status := 'Y'; -- calculate_price_flag
1019 EXIT WHEN i = l_ord_line_tbl.LAST;
1020 i := l_ord_line_tbl.NEXT(i);
1021 END LOOP;
1022 END IF;
1023
1024 IF OZF_DEBUG_HIGH_ON THEN
1025 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1026 END IF;
1027 EXCEPTION
1028 WHEN FND_API.G_EXC_ERROR THEN
1029 x_return_status := FND_API.G_RET_STS_ERROR;
1030 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1031 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1032 WHEN OTHERS THEN
1033 IF OZF_DEBUG_LOW_ON THEN
1034 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1035 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
1036 FND_MSG_PUB.Add;
1037 END IF;
1038 x_return_status := FND_API.g_ret_sts_unexp_error;
1039
1040 END Default_Claim_Line_Tbl;
1041
1042
1043 /*=======================================================================*
1044 | PROCEDURE
1045 | Validate_Claim_Line
1046 |
1047 | NOTES
1048 | This API validate claim line recored against RMA settlement.
1049 |
1050 | HISTORY
1051 | 30-JUL-2002 mchang Create.
1052 *=======================================================================*/
1053 PROCEDURE Validate_Claim_Line(
1054 p_api_version IN NUMBER
1055 ,p_init_msg_list IN VARCHAR2
1056 ,p_validation_level IN NUMBER
1057
1058 ,x_return_status OUT NOCOPY VARCHAR2
1059 ,x_msg_data OUT NOCOPY VARCHAR2
1060 ,x_msg_count OUT NOCOPY NUMBER
1061
1062 ,p_claim_line_rec IN OZF_CLAIM_LINE_PVT.claim_line_rec_type
1063 )
1064 IS
1065 l_api_version CONSTANT NUMBER := 1.0;
1066 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Claim_Line';
1067 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1068
1069
1070 l_error BOOLEAN := FALSE;
1071
1072 BEGIN
1073 IF OZF_DEBUG_HIGH_ON THEN
1074 OZF_Utility_PVT.debug_message(l_full_name||' : start');
1075 END IF;
1076
1077 -- Initialize API return status to sucess
1078 x_return_status := FND_API.g_ret_sts_success;
1079
1080 IF OZF_DEBUG_HIGH_ON THEN
1081 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1082 END IF;
1083 EXCEPTION
1084 WHEN FND_API.G_EXC_ERROR THEN
1085 x_return_status := FND_API.G_RET_STS_ERROR;
1086 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1087 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1088 WHEN OTHERS THEN
1089 IF OZF_DEBUG_LOW_ON THEN
1090 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1091 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
1092 FND_MSG_PUB.Add;
1093 END IF;
1094 x_return_status := FND_API.g_ret_sts_unexp_error;
1095
1096 END Validate_Claim_Line;
1097
1098
1099 /*=======================================================================*
1100 | PROCEDURE
1101 | Validate_Claim_Line_Tbl
1102 |
1103 | NOTES
1104 | This API validate claim line table against RMA settlement.
1105 |
1106 | HISTORY
1107 | 30-JUL-2002 mchang Create.
1108 *=======================================================================*/
1109 PROCEDURE Validate_Claim_Line_Tbl(
1110 p_api_version IN NUMBER
1111 ,p_init_msg_list IN VARCHAR2
1112 ,p_validation_level IN NUMBER
1113
1114 ,x_return_status OUT NOCOPY VARCHAR2
1115 ,x_msg_data OUT NOCOPY VARCHAR2
1116 ,x_msg_count OUT NOCOPY NUMBER
1117
1118 ,p_claim_line_tbl IN OZF_CLAIM_LINE_PVT.claim_line_tbl_type
1119 )
1120 IS
1121 l_api_version CONSTANT NUMBER := 1.0;
1122 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Claim_Line_Tbl';
1123 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1124 l_return_status VARCHAR2(1);
1125
1126 i NUMBER;
1127 l_error BOOLEAN := FALSE;
1128
1129 BEGIN
1130 IF OZF_DEBUG_HIGH_ON THEN
1131 OZF_Utility_PVT.debug_message(l_full_name||' : start');
1132 END IF;
1133
1134 -- Initialize API return status to sucess
1135 x_return_status := FND_API.g_ret_sts_success;
1136
1137 ----------------------- Start -----------------------
1138 i := p_claim_line_tbl.FIRST;
1139 IF i IS NOT NULL THEN
1140 LOOP
1141 IF p_claim_line_tbl(i).claim_line_id IS NOT NULL THEN
1142 Validate_Claim_Line(
1143 p_api_version => l_api_version
1144 ,p_init_msg_list => FND_API.g_false
1145 ,p_validation_level => FND_API.g_valid_level_full
1146 ,x_return_status => l_return_status
1147 ,x_msg_data => x_msg_data
1148 ,x_msg_count => x_msg_count
1149 ,p_claim_line_rec => p_claim_line_tbl(i)
1150 );
1151 IF l_return_status = FND_API.g_ret_sts_error THEN
1152 RAISE FND_API.g_exc_error;
1153 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1154 RAISE FND_API.g_exc_unexpected_error;
1155 END IF;
1156 END IF;
1157 EXIT WHEN i = p_claim_line_tbl.LAST;
1158 i := p_claim_line_tbl.NEXT(i);
1159 END LOOP;
1160 END IF;
1161
1162 IF l_error THEN
1163 RAISE FND_API.G_EXC_ERROR;
1164 END IF;
1165
1166
1167 IF OZF_DEBUG_HIGH_ON THEN
1168 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1169 END IF;
1170 EXCEPTION
1171 WHEN FND_API.G_EXC_ERROR THEN
1172 x_return_status := FND_API.G_RET_STS_ERROR;
1173 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1175 WHEN OTHERS THEN
1176 IF OZF_DEBUG_LOW_ON THEN
1177 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1178 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
1179 FND_MSG_PUB.Add;
1180 END IF;
1181 x_return_status := FND_API.g_ret_sts_unexp_error;
1182
1183 END Validate_Claim_Line_Tbl;
1184
1185 -- kishore
1186 -- Fix for bug 4565361
1187 /*=======================================================================*
1188 | Procedure
1189 | Validate_reference_information
1190 |
1191 | Return
1192 |
1193 | NOTES
1194 |
1195 | HISTORY
1196 | 15-DEC-2005 kdhulipa Created.
1197 | 17-Jan-2006 kdhulipa Fix for bug 4565507
1198 *=======================================================================*/
1199 PROCEDURE Validate_reference_information(
1200 x_return_status OUT NOCOPY VARCHAR2
1201 ,x_msg_data OUT NOCOPY VARCHAR2
1202 ,x_msg_count OUT NOCOPY NUMBER
1203 ,p_source_object_id IN NUMBER
1204 ,p_source_object_line_id IN NUMBER
1205 ,p_source_object_class IN VARCHAR2
1206 ,p_quantity IN NUMBER
1207 )
1208 IS
1209 l_api_version CONSTANT NUMBER := 1.0;
1210 l_api_name CONSTANT VARCHAR2(30) := 'Validate_reference_information';
1211 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1212
1213 CURSOR csr_order_reference(cv_customer_trx_id IN NUMBER) IS
1214 SELECT interface_header_context
1215 FROM ra_customer_trx_all
1216 WHERE customer_trx_id = cv_customer_trx_id;
1217
1218 CURSOR csr_ord_total_quantity(cv_header_id IN NUMBER) IS
1219 SELECT ordered_quantity
1220 FROM oe_order_lines_all
1221 WHERE header_id = cv_header_id;
1222
1223 CURSOR csr_ord_return_quantity(cv_header_id IN NUMBER, cv_line_id NUMBER) IS
1224 SELECT sum(nvl(ordered_quantity, 0))
1225 FROM oe_order_lines_all
1226 WHERE reference_header_id = cv_header_id
1227 AND reference_line_id = cv_line_id
1228 AND booked_flag = 'Y'
1229 AND cancelled_flag <> 'Y'
1230 AND line_category_code = 'RETURN';
1231
1232 CURSOR csr_inv_ord_number(cv_cust_id IN NUMBER) IS
1233 SELECT interface_header_attribute1
1234 FROM ra_customer_trx_all
1235 WHERE customer_trx_id = cv_cust_id;
1236
1237 CURSOR csr_inv_header_id(cv_customer_trx IN NUMBER) IS
1238 SELECT header_id
1239 FROM oe_order_headers_all
1240 WHERE order_number = cv_customer_trx;
1241
1242 CURSOR csr_inv_line_id(cv_line_id IN NUMBER) IS
1243 SELECT interface_line_attribute6
1244 FROM ra_customer_trx_lines_all
1245 WHERE customer_trx_line_id = cv_line_id;
1246
1247 l_reference_header varchar(20);
1248 l_total_order_quantity NUMBER;
1249 l_return_order_quantity NUMBER;
1250 l_inv_order_number NUMBER;
1251 l_inv_header_id NUMBER;
1252 l_inv_line_id NUMBER;
1253
1254 BEGIN
1255
1256 IF OZF_DEBUG_HIGH_ON THEN
1257 OZF_Utility_PVT.debug_message(l_full_name||' : start');
1258 END IF;
1259
1260 -- check whether invoice is generated from ORDER or not.
1261 IF p_source_object_class = 'INVOICE' THEN
1262 OPEN csr_order_reference(p_source_object_id);
1263 FETCH csr_order_reference INTO l_reference_header;
1264 CLOSE csr_order_reference;
1265 IF nvl(l_reference_header, 'OZF_DUMMY') <> 'ORDER ENTRY' THEN
1266 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1267 FND_MESSAGE.Set_Name('OZF','OZF_CLAIM_INVOICE_ORDER_ERROR');
1268 FND_MSG_PUB.Add;
1269 END IF;
1270 RAISE FND_API.G_EXC_ERROR;
1271 END IF;
1272 END IF;
1273
1274
1275 -- BUG 4565507 Begin
1276 IF p_source_object_class = 'ORDER' THEN
1277
1278 OPEN csr_ord_return_quantity(p_source_object_id, p_source_object_line_id);
1279 FETCH csr_ord_return_quantity INTO l_return_order_quantity;
1280 CLOSE csr_ord_return_quantity;
1281
1282 OPEN csr_ord_total_quantity(p_source_object_id);
1283 FETCH csr_ord_total_quantity into l_total_order_quantity;
1284 CLOSE csr_ord_total_quantity;
1285
1286 IF l_return_order_quantity IS NOT NULL THEN
1287 IF ( (p_quantity + l_return_order_quantity ) > l_total_order_quantity ) THEN
1288 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1289 FND_MESSAGE.Set_Name('OZF','OZF_RETURN_INVALID_QUANTITY');
1290 FND_MSG_PUB.Add;
1291 END IF;
1292 RAISE FND_API.G_EXC_ERROR;
1293 END IF;
1294 END IF;
1295 END IF;
1296
1297 IF p_source_object_class = 'INVOICE' THEN
1298
1299 -- to find the order number
1300 OPEN csr_inv_ord_number(p_source_object_id);
1301 FETCH csr_inv_ord_number INTO l_inv_order_number;
1302 CLOSE csr_inv_ord_number;
1303
1304 -- to find header id
1305 OPEN csr_inv_header_id(l_inv_order_number);
1306 FETCH csr_inv_header_id INTO l_inv_header_id;
1307 CLOSE csr_inv_header_id;
1308
1309 -- to find the line id
1310 OPEN csr_inv_line_id(p_source_object_line_id);
1311 FETCH csr_inv_line_id INTO l_inv_line_id;
1312 CLOSE csr_inv_line_id;
1313
1314 OPEN csr_ord_return_quantity(l_inv_header_id, l_inv_line_id);
1315 FETCH csr_ord_return_quantity INTO l_return_order_quantity;
1316 CLOSE csr_ord_return_quantity;
1317
1318 OPEN csr_ord_total_quantity(l_inv_header_id);
1319 FETCH csr_ord_total_quantity into l_total_order_quantity;
1320 CLOSE csr_ord_total_quantity;
1321
1322 IF OZF_DEBUG_HIGH_ON THEN
1323 OZF_Utility_PVT.debug_message(' Order Number '|| l_inv_order_number);
1324 OZF_Utility_PVT.debug_message(' Return Order Quantity '|| l_return_order_quantity);
1325 OZF_Utility_PVT.debug_message(' Total quantity '|| l_total_order_quantity);
1326 END IF;
1327
1328 IF l_return_order_quantity IS NOT NULL THEN
1329 IF ( (p_quantity + l_return_order_quantity ) > l_total_order_quantity ) THEN
1330 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1331 FND_MESSAGE.Set_Name('OZF','OZF_RETURN_INVALID_QUANTITY');
1332 FND_MSG_PUB.Add;
1333 END IF;
1334 RAISE FND_API.G_EXC_ERROR;
1335 END IF;
1336 END IF;
1337
1338 END IF;
1339
1340 -- BUG 4565507 END
1341
1342
1343 EXCEPTION
1344 WHEN FND_API.G_EXC_ERROR THEN
1345 x_return_status := FND_API.G_RET_STS_ERROR;
1346 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1348 WHEN OTHERS THEN
1349 IF OZF_DEBUG_LOW_ON THEN
1350 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1351 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
1352 FND_MSG_PUB.Add;
1353 END IF;
1354 x_return_status := FND_API.g_ret_sts_unexp_error;
1355 END Validate_reference_information;
1356
1357
1358 /*=======================================================================*
1359 | Procedure
1360 | Validate_Return_Quantity
1361 |
1362 | Return
1363 |
1364 | NOTES
1365 |
1366 | HISTORY
1367 | 13-DEC-2002 mchang Create.
1368 *=======================================================================*/
1369 PROCEDURE Validate_Return_Quantity(
1370 x_return_status OUT NOCOPY VARCHAR2
1371 ,x_msg_data OUT NOCOPY VARCHAR2
1372 ,x_msg_count OUT NOCOPY NUMBER
1373
1374 ,p_claim_line_rec IN OZF_CLAIM_LINE_PVT.claim_line_rec_type
1375 )
1376 IS
1377 l_api_version CONSTANT NUMBER := 1.0;
1378 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Return_Quantity';
1379 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1380 l_return_status VARCHAR2(1);
1381
1382 CURSOR csr_inv_line_quantity(cv_invoice_line_id IN NUMBER) IS
1383 SELECT quantity_invoiced
1384 , inventory_item_id
1385 FROM ra_customer_trx_lines
1386 WHERE customer_trx_line_id = cv_invoice_line_id;
1387
1388 CURSOR csr_ord_line_quantity(cv_order_line_id IN NUMBER) IS
1389 SELECT ordered_quantity
1390 , inventory_item_id
1391 FROM oe_order_lines
1392 WHERE line_id = cv_order_line_id;
1393
1394 CURSOR csr_product_name(cv_item_id IN NUMBER, cv_org_id IN NUMBER) IS
1395 SELECT description
1396 FROM mtl_system_items_vl
1397 WHERE inventory_item_id = cv_item_id
1398 AND organization_id = cv_org_id;
1399
1400 i NUMBER;
1401 l_most_quantity NUMBER;
1402 l_item_id NUMBER;
1403 l_csr_product_name csr_product_name%ROWTYPE;
1404 l_org_id NUMBER;
1405 l_error BOOLEAN := FALSE;
1406
1407 BEGIN
1408 IF OZF_DEBUG_HIGH_ON THEN
1409 OZF_Utility_PVT.debug_message(l_full_name||' : start');
1410 END IF;
1411
1412 -- Initialize API return status to sucess
1413 x_return_status := FND_API.g_ret_sts_success;
1414
1415 -- Checking Reference information -----
1416 -- kishore.
1417
1418 IF p_claim_line_rec.source_object_class IN ('INVOICE', 'ORDER') AND
1419 p_claim_line_rec.source_object_id IS NOT NULL AND
1420 p_claim_line_rec.source_object_line_id IS NOT NULL THEN
1421
1422 Validate_reference_information(
1423 x_return_status => l_return_status
1424 ,x_msg_data => x_msg_data
1425 ,x_msg_count => x_msg_count
1426 ,p_source_object_id => p_claim_line_rec.source_object_id
1427 ,p_source_object_line_id => p_claim_line_rec.source_object_line_id
1428 ,p_source_object_class => p_claim_line_rec.source_object_class
1429 ,p_quantity => p_claim_line_rec.quantity
1430 );
1431
1432 IF l_return_status = FND_API.g_ret_sts_error THEN
1433 RAISE FND_API.g_exc_error;
1434 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1435 RAISE FND_API.g_exc_unexpected_error;
1436 END IF;
1437
1438 END IF;
1439
1440 ----------------------- Start -----------------------
1441 IF p_claim_line_rec.source_object_class IN ('INVOICE', 'ORDER') AND
1442 p_claim_line_rec.source_object_id IS NOT NULL AND
1443 p_claim_line_rec.source_object_line_id IS NOT NULL THEN
1444 ------- INVOICE -------
1445 IF p_claim_line_rec.source_object_class = 'INVOICE' THEN
1446 OPEN csr_inv_line_quantity(p_claim_line_rec.source_object_line_id);
1447 FETCH csr_inv_line_quantity INTO l_most_quantity, l_item_id;
1448 CLOSE csr_inv_line_quantity;
1449 ------- ORDER -------
1450 ELSIF p_claim_line_rec.source_object_class = 'ORDER' THEN
1451 OPEN csr_ord_line_quantity(p_claim_line_rec.source_object_line_id);
1452 FETCH csr_ord_line_quantity INTO l_most_quantity, l_item_id;
1453 CLOSE csr_ord_line_quantity;
1454 END IF;
1455
1456 -- If both product and invoice/order line are defined,
1457 -- the product needs to belong to the same invoice/order line.
1458 IF p_claim_line_rec.item_id IS NOT NULL AND
1459 p_claim_line_rec.item_id <> l_item_id THEN
1460 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1461 l_org_id := FND_PROFILE.value('AMS_ITEM_ORGANIZATION_ID');
1462 OPEN csr_product_name(p_claim_line_rec.item_id, l_org_id);
1463 -- OPEN csr_product_name(p_claim_line_rec.item_id, l_org_id);
1464 FETCH csr_product_name INTO l_csr_product_name;
1465 CLOSE csr_product_name;
1466 FND_MESSAGE.set_name('OZF', 'OZF_SETL_VAL_PROD_ERR');
1467 FND_MESSAGE.set_token('PROD', l_csr_product_name.description);
1468 FND_MSG_PUB.add;
1469 END IF;
1470 l_error := TRUE;
1471 END IF;
1472
1473 IF p_claim_line_rec.quantity > l_most_quantity THEN
1474 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1475 FND_MESSAGE.set_name('OZF', 'OZF_SETL_VAL_RMA_QUTY_ERR');
1476 FND_MSG_PUB.add;
1477 END IF;
1478 l_error := TRUE;
1479 END IF;
1480 END IF;
1481
1482 IF l_error THEN
1483 RAISE FND_API.G_EXC_ERROR;
1484 END IF;
1485
1486 IF OZF_DEBUG_HIGH_ON THEN
1487 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1488 END IF;
1489 EXCEPTION
1490 WHEN FND_API.G_EXC_ERROR THEN
1491 x_return_status := FND_API.G_RET_STS_ERROR;
1492 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1493 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1494 WHEN OTHERS THEN
1495 IF OZF_DEBUG_LOW_ON THEN
1496 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1497 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
1498 FND_MSG_PUB.Add;
1499 END IF;
1500 x_return_status := FND_API.g_ret_sts_unexp_error;
1501
1502 END Validate_Return_Quantity;
1503
1504 /*=======================================================================*
1505 | Procedure
1506 | Check_RMA_Item_Attribute
1507 |
1508 | Return
1509 |
1510 | NOTES
1511 |
1512 | HISTORY
1513 | 13-DEC-2002 mchang Create.
1514 *=======================================================================*/
1515 PROCEDURE Check_RMA_Item_Attribute(
1516 x_return_status OUT NOCOPY VARCHAR2
1517 ,x_msg_data OUT NOCOPY VARCHAR2
1518 ,x_msg_count OUT NOCOPY NUMBER
1519
1520 ,p_item_id IN NUMBER
1521 )
1522 IS
1523 l_api_version CONSTANT NUMBER := 1.0;
1524 l_api_name CONSTANT VARCHAR2(30) := 'Check_RMA_Item_Attribute';
1525 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1526
1527 CURSOR csr_item_attr(cv_item_id IN NUMBER, cv_org_id IN NUMBER) IS
1528 SELECT invoice_enabled_flag
1529 , invoiceable_item_flag
1530 , returnable_flag
1531 , description
1532 FROM mtl_system_items_vl
1533 WHERE inventory_item_id = cv_item_id
1534 AND organization_id = cv_org_id;
1535
1536 l_csr_item_attr csr_item_attr%ROWTYPE;
1537 l_org_id NUMBER;
1538 l_error BOOLEAN := FALSE;
1539
1540 BEGIN
1541 IF OZF_DEBUG_HIGH_ON THEN
1542 OZF_Utility_PVT.debug_message(l_full_name||' : start');
1543 END IF;
1544
1545 -- Initialize API return status to sucess
1546 x_return_status := FND_API.g_ret_sts_success;
1547
1548 -- l_org_id := TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10));
1549 l_org_id := FND_PROFILE.value('AMS_ITEM_ORGANIZATION_ID');
1550
1551 OPEN csr_item_attr(p_item_id, l_org_id);
1552 FETCH csr_item_attr INTO l_csr_item_attr;
1553 CLOSE csr_item_attr;
1554
1555 IF l_csr_item_attr.invoice_enabled_flag = 'N' OR
1556 l_csr_item_attr.invoiceable_item_flag = 'N' THEN
1557 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1558 FND_MESSAGE.set_name('OZF', 'OZF_SETL_VAL_RMA_INV_ERR');
1559 FND_MESSAGE.set_token('PROD', l_csr_item_attr.description);
1560 FND_MSG_PUB.add;
1561 END IF;
1562 l_error := TRUE;
1563 END IF;
1564
1565 IF l_csr_item_attr.returnable_flag <> 'Y' THEN
1566 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1567 FND_MESSAGE.set_name('OZF', 'OZF_SETL_VAL_ITEM_RMA_ERR');
1568 FND_MESSAGE.set_token('PROD', l_csr_item_attr.description);
1569 FND_MSG_PUB.add;
1570 END IF;
1571 l_error := TRUE;
1572 END IF;
1573
1574 IF l_error THEN
1575 RAISE FND_API.G_EXC_ERROR;
1576 END IF;
1577
1578 IF OZF_DEBUG_HIGH_ON THEN
1579 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1580 END IF;
1581 EXCEPTION
1582 WHEN FND_API.G_EXC_ERROR THEN
1583 x_return_status := FND_API.G_RET_STS_ERROR;
1584 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1586 WHEN OTHERS THEN
1587 IF OZF_DEBUG_LOW_ON THEN
1588 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1589 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
1590 FND_MSG_PUB.Add;
1591 END IF;
1592 x_return_status := FND_API.g_ret_sts_unexp_error;
1593
1594 END Check_RMA_Item_Attribute;
1595
1596
1597 /*=======================================================================*
1598 | Procedure
1599 | Check_RMA_Line_Items
1600 |
1601 | Return
1602 |
1603 | NOTES
1604 |
1605 | HISTORY
1606 | 13-DEC-2002 mchang Create.
1607 *=======================================================================*/
1608 PROCEDURE Check_RMA_Line_Items(
1609 x_return_status OUT NOCOPY VARCHAR2
1610 ,x_msg_data OUT NOCOPY VARCHAR2
1611 ,x_msg_count OUT NOCOPY NUMBER
1612
1613 ,p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
1614 ,p_claim_line_rec IN OZF_CLAIM_LINE_PVT.claim_line_rec_type
1615 )
1616 IS
1617 l_api_version CONSTANT NUMBER := 1.0;
1618 l_api_name CONSTANT VARCHAR2(30) := 'Check_RMA_Line_Items';
1619 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1620
1621 l_error BOOLEAN := FALSE;
1622
1623 BEGIN
1624 IF OZF_DEBUG_HIGH_ON THEN
1625 OZF_Utility_PVT.debug_message(l_full_name||' : start');
1626 END IF;
1627
1628 -- Initialize API return status to sucess
1629 x_return_status := FND_API.g_ret_sts_success;
1630
1631 -- 1. invoice line or product is required
1632 IF (p_claim_line_rec.item_id IS NULL OR p_claim_line_rec.item_type <> 'PRODUCT') AND
1633 p_claim_line_rec.source_object_line_id IS NULL THEN
1634 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1635 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_PROD_ERR');
1636 FND_MSG_PUB.add;
1637 END IF;
1638 l_error := TRUE;
1639 END IF;
1640
1641 -- 2. quantity is required
1642 IF p_claim_line_rec.quantity IS NULL THEN
1643 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1644 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_QUANTITY_ERR');
1645 FND_MSG_PUB.add;
1646 END IF;
1647 l_error := TRUE;
1648 END IF;
1649
1650 -- 3. uom is required
1651 IF p_claim_line_rec.quantity_uom IS NULL THEN
1652 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1653 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_UOM_ERR');
1654 FND_MSG_PUB.add;
1655 END IF;
1656 l_error := TRUE;
1657 END IF;
1658
1659 -- 4. price is required
1660 IF p_claim_line_rec.rate IS NULL THEN
1661 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1662 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_RATE_ERR');
1663 FND_MSG_PUB.add;
1664 END IF;
1665 l_error := TRUE;
1666 END IF;
1667
1668 -- 5. Pay Related Customer is not applicable if RMA is with referenced order/line
1669 IF p_claim_line_rec.source_object_class IN ('INVOICE', 'ORDER') AND
1670 p_claim_line_rec.source_object_line_id IS NOT NULL AND
1671 p_claim_rec.pay_related_account_flag = 'T' THEN
1672 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1673 FND_MESSAGE.set_name('OZF', 'OZF_SETL_RMA_REL_CUST_NA');
1674 FND_MSG_PUB.add;
1675 END IF;
1676 l_error := TRUE;
1677 END IF;
1678
1679 IF l_error THEN
1680 RAISE FND_API.G_EXC_ERROR;
1681 END IF;
1682
1683 IF OZF_DEBUG_HIGH_ON THEN
1684 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1685 END IF;
1686 EXCEPTION
1687 WHEN FND_API.G_EXC_ERROR THEN
1688 x_return_status := FND_API.G_RET_STS_ERROR;
1689 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1690 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1691 WHEN OTHERS THEN
1692 IF OZF_DEBUG_LOW_ON THEN
1693 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1694 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
1695 FND_MSG_PUB.Add;
1696 END IF;
1697 x_return_status := FND_API.g_ret_sts_unexp_error;
1698
1699 END Check_RMA_Line_Items;
1700
1701
1702 /*=======================================================================*
1703 | Procedure
1704 | Complete_RMA_Validation
1705 |
1706 | Return
1707 |
1708 | NOTES
1709 |
1710 | HISTORY
1711 | 24-OCT-2002 mchang Create.
1712 *=======================================================================*/
1713 PROCEDURE Complete_RMA_Validation(
1714 p_api_version IN NUMBER
1715 ,p_init_msg_list IN VARCHAR2
1716 ,p_validation_level IN NUMBER
1717
1718 ,x_return_status OUT NOCOPY VARCHAR2
1719 ,x_msg_data OUT NOCOPY VARCHAR2
1720 ,x_msg_count OUT NOCOPY NUMBER
1721
1722 ,p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
1723 ,x_claim_rec OUT NOCOPY OZF_CLAIM_PVT.claim_rec_type
1724 )
1725 IS
1726 l_api_version CONSTANT NUMBER := 1.0;
1727 l_api_name CONSTANT VARCHAR2(30) := 'Complete_RMA_Validation';
1728 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1729 l_return_status VARCHAR2(1);
1730
1731 i NUMBER;
1732 l_claim_line_tbl OZF_CLAIM_LINE_PVT.claim_line_tbl_type;
1733 l_claim_line_rec OZF_CLAIM_LINE_PVT.claim_line_rec_type;
1734
1735 BEGIN
1736 IF OZF_DEBUG_HIGH_ON THEN
1737 OZF_Utility_PVT.debug_message(l_full_name||' : start');
1738 END IF;
1739
1740 -- Initialize API return status to sucess
1741 x_return_status := FND_API.g_ret_sts_success;
1742
1743 x_claim_rec := p_claim_rec;
1744
1745 ----------------------- Start -----------------------
1746 OZF_OM_PAYMENT_PVT.Query_Claim_Line(
1747 p_claim_id => p_claim_rec.claim_id
1748 ,x_claim_line_tbl => l_claim_line_tbl
1749 ,x_return_status => l_return_status
1750 );
1751 IF l_return_status = FND_API.g_ret_sts_error THEN
1752 RAISE FND_API.g_exc_error;
1753 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1754 RAISE FND_API.g_exc_unexpected_error;
1755 END IF;
1756
1757 i := l_claim_line_tbl.FIRST;
1758 IF i IS NOT NULL THEN
1759 LOOP
1760 IF l_claim_line_tbl(i).claim_line_id IS NOT NULL THEN
1761 l_claim_line_rec := l_claim_line_tbl(i);
1762
1763 -------------------------------
1764 -- RMA Line Items Validation --
1765 -------------------------------
1766 Check_RMA_Line_Items(
1767 x_return_status => l_return_status
1768 ,x_msg_data => x_msg_data
1769 ,x_msg_count => x_msg_count
1770 ,p_claim_rec => p_claim_rec
1771 ,p_claim_line_rec => l_claim_line_rec
1772 );
1773 IF l_return_status = FND_API.g_ret_sts_error THEN
1774 RAISE FND_API.g_exc_error;
1775 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1776 RAISE FND_API.g_exc_unexpected_error;
1777 END IF;
1778
1779 -------------------------------------
1780 -- RMA Items Attributes Validation --
1781 -------------------------------------
1782 Check_RMA_Item_Attribute(
1783 x_return_status => l_return_status
1784 ,x_msg_data => x_msg_data
1785 ,x_msg_count => x_msg_count
1786 ,p_item_id => l_claim_line_rec.item_id
1787 );
1788 IF l_return_status = FND_API.g_ret_sts_error THEN
1789 RAISE FND_API.g_exc_error;
1790 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1791 RAISE FND_API.g_exc_unexpected_error;
1792 END IF;
1793
1794 --------------------------------
1795 -- Return Quantity Validation --
1796 --------------------------------
1797 Validate_Return_Quantity(
1798 x_return_status => l_return_status
1799 ,x_msg_data => x_msg_data
1800 ,x_msg_count => x_msg_count
1801 ,p_claim_line_rec => l_claim_line_rec
1802 );
1803 IF l_return_status = FND_API.g_ret_sts_error THEN
1804 RAISE FND_API.g_exc_error;
1805 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1806 RAISE FND_API.g_exc_unexpected_error;
1807 END IF;
1808
1809 END IF;
1810 EXIT WHEN i = l_claim_line_tbl.LAST;
1811 i := l_claim_line_tbl.NEXT(i);
1812 END LOOP;
1813 END IF;
1814
1815
1816 IF OZF_DEBUG_HIGH_ON THEN
1817 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1818 END IF;
1819 EXCEPTION
1820 WHEN FND_API.G_EXC_ERROR THEN
1821 x_return_status := FND_API.G_RET_STS_ERROR;
1822 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1823 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1824 WHEN OTHERS THEN
1825 IF OZF_DEBUG_LOW_ON THEN
1826 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1827 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
1828 FND_MSG_PUB.Add;
1829 END IF;
1830 x_return_status := FND_API.g_ret_sts_unexp_error;
1831
1832 END Complete_RMA_Validation;
1833
1834
1835
1836 END OZF_OM_VALIDATION_PVT;