[Home] [Help]
PACKAGE BODY: APPS.OZF_OM_VALIDATION_PVT
Source
1 PACKAGE BODY OZF_OM_VALIDATION_PVT AS
2 /* $Header: ozfvomvb.pls 120.4 2010/06/24 06:47:44 hbandi ship $ */
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 | 24-06-2010 Hbandi modified for the Forward port bug #9498844
393 *=======================================================================*/
394 PROCEDURE Price_Invoice_Line(
395 x_return_status OUT NOCOPY VARCHAR2
396 ,x_msg_data OUT NOCOPY VARCHAR2
397 ,x_msg_count OUT NOCOPY NUMBER
398
399 ,p_x_inv_line_tbl IN OUT NOCOPY claim_line_item_tbl_type
400 )
401 IS
402 l_api_version CONSTANT NUMBER := 1.0;
403 l_api_name CONSTANT VARCHAR2(30) := 'Price_Invoice_Line';
404 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
405
406 l_org_id NUMBER;
407 l_oe_disc_dtls_on_invoice VARCHAR2(1);
408
409 --R12 Forward port bug#9498844,This Cursor is used for Getting the OM system Paramter value for the 'OE_DISCOUNT_DETAILS_ON_INVOICE'.
410 CURSOR get_om_profle IS
411 select parameter_value from oe_sys_parameters_all
412 where parameter_code = 'OE_DISCOUNT_DETAILS_ON_INVOICE' and org_id = mo_global.get_current_org_id;
413
414 CURSOR csr_inv_line_price(cv_customer_trx_line_id IN NUMBER) IS
415 SELECT inventory_item_id
416 , unit_selling_price
417 , uom_code
418 FROM ra_customer_trx_lines
419 WHERE customer_trx_line_id = cv_customer_trx_line_id;
420
421 --Added for bug 7680032
422 CURSOR csr_om_line_price(cv_customer_trx_line_id NUMBER) IS
423 SELECT ra.inventory_item_id
424 , ol.unit_selling_price
425 , ra.uom_code
426 FROM ra_customer_trx_lines_all ra,
427 oe_order_lines_all ol
428 WHERE ra.customer_trx_line_id = cv_customer_trx_line_id
429 AND ra.interface_line_attribute6 = ol.line_id;
430
431 i NUMBER;
432
433 BEGIN
434
435 IF OZF_DEBUG_HIGH_ON THEN
436 OZF_Utility_PVT.debug_message(l_full_name||' : start');
437 END IF;
438
439 -- Initialize API return status to sucess
440 x_return_status := FND_API.g_ret_sts_success;
441
442 i := p_x_inv_line_tbl.FIRST;
443 IF i IS NOT NULL THEN
444 LOOP
445 OPEN get_om_profle;
446 FETCH get_om_profle INTO l_oe_disc_dtls_on_invoice;
447 CLOSE get_om_profle;
448
449 l_oe_disc_dtls_on_invoice := NVL(l_oe_disc_dtls_on_invoice,'N');
450 IF l_oe_disc_dtls_on_invoice = 'Y' THEN
451 OPEN csr_om_line_price(p_x_inv_line_tbl(i).source_object_line_id);
452 FETCH csr_om_line_price INTO p_x_inv_line_tbl(i).item_id
453 , p_x_inv_line_tbl(i).rate
454 , p_x_inv_line_tbl(i).quantity_uom;
455
456 --If the inventory item id is null then there is no order number is linked with the invoice.hbandi Added this code for the BUG #9372038(+)
457 IF p_x_inv_line_tbl(i).item_id IS NULL THEN
458 OPEN csr_inv_line_price(p_x_inv_line_tbl(i).source_object_line_id);
459 FETCH csr_inv_line_price INTO p_x_inv_line_tbl(i).item_id
460 , p_x_inv_line_tbl(i).rate
461 , p_x_inv_line_tbl(i).quantity_uom;
462 CLOSE csr_inv_line_price;
463 END IF;
464 --End of the Hbandi Code For the BUG #9372038(-)
465
466 CLOSE csr_om_line_price;
467 ELSE
468 OPEN csr_inv_line_price(p_x_inv_line_tbl(i).source_object_line_id);
469 FETCH csr_inv_line_price INTO p_x_inv_line_tbl(i).item_id
470 , p_x_inv_line_tbl(i).rate
471 , p_x_inv_line_tbl(i).quantity_uom;
472 CLOSE csr_inv_line_price;
473 END IF;
474
475 /*--Profile logic added for bug 7680032 (This profile option was obsolated from R12 ,They made the same as OM system paramter we modified the code accordingly.
476 IF fnd_profile.Value('OE_DISCOUNT_DETAILS_ON_INVOICE') = 'Y' THEN
477 OPEN csr_om_line_price(p_x_inv_line_tbl(i).source_object_line_id);
478 FETCH csr_om_line_price INTO p_x_inv_line_tbl(i).item_id
479 , p_x_inv_line_tbl(i).rate
480 , p_x_inv_line_tbl(i).quantity_uom;
481 CLOSE csr_om_line_price;
482 ELSE
483 OPEN csr_inv_line_price(p_x_inv_line_tbl(i).source_object_line_id);
484 FETCH csr_inv_line_price INTO p_x_inv_line_tbl(i).item_id
485 , p_x_inv_line_tbl(i).rate
486 , p_x_inv_line_tbl(i).quantity_uom;
487 CLOSE csr_inv_line_price;
488 END IF;*/
489
490 EXIT WHEN i = p_x_inv_line_tbl.LAST;
491 i := p_x_inv_line_tbl.NEXT(i);
492 END LOOP;
493 END IF;
494
495 IF OZF_DEBUG_HIGH_ON THEN
496 OZF_Utility_PVT.debug_message(l_full_name||' : end');
497 END IF;
498 EXCEPTION
499 WHEN FND_API.G_EXC_ERROR THEN
500 x_return_status := FND_API.G_RET_STS_ERROR;
501 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
503 WHEN OTHERS THEN
504 IF OZF_DEBUG_LOW_ON THEN
505 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
506 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
507 FND_MSG_PUB.Add;
508 END IF;
509 x_return_status := FND_API.g_ret_sts_unexp_error;
510
511 END Price_Invoice_Line;
512
513
514 /*=======================================================================*
515 | PROCEDURE
516 | Price_Order_Line
517 |
518 | NOTES
519 | This API default the unit price for a order line.
520 |
521 | HISTORY
522 | 14-NOV-2002 mchang Create.
523 *=======================================================================*/
524 PROCEDURE Price_Order_Line(
525 x_return_status OUT NOCOPY VARCHAR2
526 ,x_msg_data OUT NOCOPY VARCHAR2
527 ,x_msg_count OUT NOCOPY NUMBER
528
529 ,p_x_ord_line_tbl IN OUT NOCOPY claim_line_item_tbl_type
530 )
531 IS
532 l_api_version CONSTANT NUMBER := 1.0;
533 l_api_name CONSTANT VARCHAR2(30) := 'Price_Order_Line';
534 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
535
536 CURSOR csr_ord_line_price(cv_order_line_id IN NUMBER) IS
537 SELECT inventory_item_id
538 , unit_selling_price
539 , order_quantity_uom
540 FROM oe_order_lines
541 WHERE line_id = cv_order_line_id;
542
543 i NUMBER;
544
545 BEGIN
546 IF OZF_DEBUG_HIGH_ON THEN
547 OZF_Utility_PVT.debug_message(l_full_name||' : start');
548 END IF;
549
550 -- Initialize API return status to sucess
551 x_return_status := FND_API.g_ret_sts_success;
552
553 i := p_x_ord_line_tbl.FIRST;
554 IF i IS NOT NULL THEN
555 LOOP
556 OPEN csr_ord_line_price(p_x_ord_line_tbl(i).source_object_line_id);
557 FETCH csr_ord_line_price INTO p_x_ord_line_tbl(i).item_id
558 , p_x_ord_line_tbl(i).rate
559 , p_x_ord_line_tbl(i).quantity_uom;
560 CLOSE csr_ord_line_price;
561
562 EXIT WHEN i = p_x_ord_line_tbl.LAST;
563 i := p_x_ord_line_tbl.NEXT(i);
564 END LOOP;
565 END IF;
566
567 IF OZF_DEBUG_HIGH_ON THEN
568 OZF_Utility_PVT.debug_message(l_full_name||' : end');
569 END IF;
570 EXCEPTION
571 WHEN FND_API.G_EXC_ERROR THEN
572 x_return_status := FND_API.G_RET_STS_ERROR;
573 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
575 WHEN OTHERS THEN
576 IF OZF_DEBUG_LOW_ON THEN
577 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
578 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
579 FND_MSG_PUB.Add;
580 END IF;
581 x_return_status := FND_API.g_ret_sts_unexp_error;
582
583 END Price_Order_Line;
584
585
586 /*=======================================================================*
587 | PROCEDURE
588 | Get_Default_Order_Type
589 |
590 | NOTES
591 | This API default order_type_id for Claim.
592 |
593 | HISTORY
594 | 16-JAN-2003 mchang Create.
595 *=======================================================================*/
596 PROCEDURE Get_Default_Order_Type(
597 p_api_version IN NUMBER
598 ,p_init_msg_list IN VARCHAR2
599 ,p_validation_level IN NUMBER
600
601 ,x_return_status OUT NOCOPY VARCHAR2
602 ,x_msg_data OUT NOCOPY VARCHAR2
603 ,x_msg_count OUT NOCOPY NUMBER
604
605 ,p_reason_code_id IN NUMBER
606 ,p_claim_type_id IN NUMBER
607 ,p_set_of_books_id IN NUMBER
608 ,x_order_type_id OUT NOCOPY NUMBER
609 )
610 IS
611 l_api_version CONSTANT NUMBER := 1.0;
612 l_api_name CONSTANT VARCHAR2(30) := 'Get_Default_Order_Type';
613 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
614 l_return_status VARCHAR2(1);
615
616 CURSOR csr_reason_rma_trx_type(cv_reason_code_id IN NUMBER) IS
617 SELECT order_type_id
618 FROM ozf_reason_codes_vl
619 WHERE reason_code_id = cv_reason_code_id;
620
621 CURSOR csr_claim_type_rma_trx_type(cv_claim_type_id IN NUMBER) IS
622 SELECT order_type_id
623 FROM ozf_claim_types_vl
624 WHERE claim_type_id = cv_claim_type_id;
625
626 CURSOR csr_sys_param_rma_trx_type(cv_set_of_books_id IN NUMBER) IS
627 SELECT order_type_id
628 FROM ozf_sys_parameters
629 WHERE set_of_books_id = cv_set_of_books_id;
630
631 BEGIN
632 IF OZF_DEBUG_HIGH_ON THEN
633 OZF_Utility_PVT.debug_message(l_full_name||' : start');
634 END IF;
635
636 -- The following hierarchy is used to determine the default RMA transaction type,
637 -- stopping when one is found
638 -- 1. Reason (ozf_reason_codes_all_b.order_type_id)
639 -- 2. Claim Type (ozf_claim_types_all_b.order_type_id)
640 -- 3. System Parameter (ozf_sys_parameters_all.order_type_id)
641
642 OPEN csr_reason_rma_trx_type(p_reason_code_id);
643 FETCH csr_reason_rma_trx_type INTO x_order_type_id;
644 CLOSE csr_reason_rma_trx_type;
645
646 IF x_order_type_id IS NULL THEN
647 OPEN csr_claim_type_rma_trx_type(p_claim_type_id);
648 FETCH csr_claim_type_rma_trx_type INTO x_order_type_id;
649 CLOSE csr_claim_type_rma_trx_type;
650 END IF;
651
652 IF x_order_type_id IS NULL THEN
653 OPEN csr_sys_param_rma_trx_type(p_set_of_books_id);
654 FETCH csr_sys_param_rma_trx_type INTO x_order_type_id;
655 CLOSE csr_sys_param_rma_trx_type;
656 END IF;
657
658 IF x_order_type_id IS NULL THEN
659 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
660 FND_MESSAGE.set_name('OZF', 'OZF_SETL_MISSING_RMA_TRX_TYPE');
661 FND_MSG_PUB.add;
662 END IF;
663 RAISE FND_API.G_EXC_ERROR;
664 END IF;
665
666 IF OZF_DEBUG_HIGH_ON THEN
667 OZF_Utility_PVT.debug_message(l_full_name||' : end');
668 END IF;
669 EXCEPTION
670 WHEN FND_API.G_EXC_ERROR THEN
671 x_return_status := FND_API.G_RET_STS_ERROR;
672 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
673 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
674 WHEN OTHERS THEN
675 IF OZF_DEBUG_LOW_ON THEN
676 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
677 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
678 FND_MSG_PUB.Add;
679 END IF;
680 x_return_status := FND_API.g_ret_sts_unexp_error;
681
682 END Get_Default_Order_Type;
683
684
685 /*=======================================================================*
686 | PROCEDURE
687 | Default_Claim_Line
688 |
689 | NOTES
690 | This API default claim line recored for RMA settlement method.
691 |
692 | HISTORY
693 | 14-NOV-2002 mchang Create.
694 *=======================================================================*/
695 PROCEDURE Default_Claim_Line(
696 p_api_version IN NUMBER
697 ,p_init_msg_list IN VARCHAR2
698 ,p_validation_level IN NUMBER
699
700 ,x_return_status OUT NOCOPY VARCHAR2
701 ,x_msg_data OUT NOCOPY VARCHAR2
702 ,x_msg_count OUT NOCOPY NUMBER
703
704 ,p_x_claim_line_rec IN OUT NOCOPY OZF_CLAIM_LINE_PVT.claim_line_rec_type
705 )
706 IS
707 l_api_version CONSTANT NUMBER := 1.0;
708 l_api_name CONSTANT VARCHAR2(30) := 'Default_Claim_Line';
709 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
710 l_return_status VARCHAR2(1);
711 /*
712 CURSOR csr_claim(p_claim_id IN NUMBER) IS
713 SELECT cust_account_id
714 , currency_code
715 FROM ozf_claims
716 WHERE claim_id = p_claim_id;
717
718 l_cust_account_id NUMBER;
719 l_claim_currency VARCHAR2(15);
720 l_price_list_id NUMBER := 1000;
721 l_line_item_tbl claim_line_item_tbl_type;
722 */
723 l_claim_line_tbl OZF_CLAIM_LINE_PVT.claim_line_tbl_type;
724
725 BEGIN
726 IF OZF_DEBUG_HIGH_ON THEN
727 OZF_Utility_PVT.debug_message(l_full_name||' : start');
728 END IF;
729
730 -- Initialize API return status to sucess
731 x_return_status := FND_API.g_ret_sts_success;
732
733 l_claim_line_tbl(1) := p_x_claim_line_rec;
734
735 Default_Claim_Line_Tbl(
736 p_api_version => l_api_version
737 ,p_init_msg_list => FND_API.g_false
738 ,p_validation_level => FND_API.g_valid_level_full
739 ,x_return_status => l_return_status
740 ,x_msg_data => x_msg_data
741 ,x_msg_count => x_msg_count
742 ,p_x_claim_line_tbl => l_claim_line_tbl
743 );
744 IF l_return_status = FND_API.g_ret_sts_error THEN
745 RAISE FND_API.g_exc_error;
746 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
747 RAISE FND_API.g_exc_unexpected_error;
748 END IF;
749
750 /*
751 IF p_x_claim_line_rec.source_object_line_id IS NOT NULL AND
752 p_x_claim_line_rec.source_object_class = 'INVOICE' THEN
753 l_line_item_tbl(1).source_object_class := p_x_claim_line_rec.source_object_class;
754 l_line_item_tbl(1).source_object_id := p_x_claim_line_rec.source_object_id;
755 l_line_item_tbl(1).source_object_line_id := p_x_claim_line_rec.source_object_line_id;
756
757 Price_Invoice_Line(
758 x_return_status => l_return_status
759 ,x_msg_data => x_msg_data
760 ,x_msg_count => x_msg_count
761 ,p_x_inv_line_tbl => l_line_item_tbl
762 );
763 IF l_return_status = FND_API.g_ret_sts_error THEN
764 RAISE FND_API.g_exc_error;
765 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
766 RAISE FND_API.g_exc_unexpected_error;
767 END IF;
768
769 ELSIF p_x_claim_line_rec.item_id IS NOT NULL THEN
770 l_line_item_tbl(1).claim_line_index := 1;
771 l_line_item_tbl(1).item_id := p_x_claim_line_rec.item_id;
772 l_line_item_tbl(1).quantity := p_x_claim_line_rec.quantity;
773 l_line_item_tbl(1).quantity_uom := p_x_claim_line_rec.quantity_uom;
774 l_line_item_tbl(1).currency_code := p_x_claim_line_rec.currency_code;
775
776 OPEN csr_claim(p_x_claim_line_rec.claim_id);
777 FETCH csr_claim INTO l_cust_account_id, l_claim_currency;
778 CLOSE csr_claim;
779
780 Price_Item(
781 p_cust_account_id => l_cust_account_id
782 ,p_price_list_id => l_price_list_id
783 ,p_currency_code => l_claim_currency
784 ,x_return_status => l_return_status
785 ,x_msg_data => x_msg_data
786 ,x_msg_count => x_msg_count
787
788 ,p_x_item_tbl => l_line_item_tbl
789 );
790 IF l_return_status = FND_API.g_ret_sts_error THEN
791 RAISE FND_API.g_exc_error;
792 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
793 RAISE FND_API.g_exc_unexpected_error;
794 END IF;
795
796 END IF;
797 */
798 IF l_claim_line_tbl(1).rate IS NOT NULL THEN
799 p_x_claim_line_rec.rate := l_claim_line_tbl(1).rate;
800 END IF;
801
802 IF OZF_DEBUG_HIGH_ON THEN
803 OZF_Utility_PVT.debug_message(l_full_name||' : end');
804 END IF;
805 EXCEPTION
806 WHEN FND_API.G_EXC_ERROR THEN
807 x_return_status := FND_API.G_RET_STS_ERROR;
808 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
809 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
810 WHEN OTHERS THEN
811 IF OZF_DEBUG_LOW_ON THEN
812 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
813 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
814 FND_MSG_PUB.Add;
815 END IF;
816 x_return_status := FND_API.g_ret_sts_unexp_error;
817
818 END Default_Claim_Line;
819
820
821 /*=======================================================================*
822 | PROCEDURE
823 | Default_Claim_Line_Tbl
824 |
825 | NOTES
826 | This API default claim line table for RMA settlement method.
827 |
828 | HISTORY
829 | 14-NOV-2002 mchang Create.
830 *=======================================================================*/
831 PROCEDURE Default_Claim_Line_Tbl(
832 p_api_version IN NUMBER
833 ,p_init_msg_list IN VARCHAR2
834 ,p_validation_level IN NUMBER
835
836 ,x_return_status OUT NOCOPY VARCHAR2
837 ,x_msg_data OUT NOCOPY VARCHAR2
838 ,x_msg_count OUT NOCOPY NUMBER
839
840 ,p_x_claim_line_tbl IN OUT NOCOPY OZF_CLAIM_LINE_PVT.claim_line_tbl_type
841 )
842 IS
843 l_api_version CONSTANT NUMBER := 1.0;
844 l_api_name CONSTANT VARCHAR2(30) := 'Default_Claim_Line_Tbl';
845 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
846 l_return_status VARCHAR2(1);
847
848 CURSOR csr_claim(p_claim_id IN NUMBER) IS
849 SELECT cust_account_id
850 , order_type_id
851 , currency_code
852 FROM ozf_claims
853 WHERE claim_id = p_claim_id;
854
855 CURSOR csr_product_name(cv_item_id IN NUMBER, cv_org_id IN NUMBER) IS
856 SELECT description
857 FROM mtl_system_items_vl
858 WHERE inventory_item_id = cv_item_id
859 AND organization_id = cv_org_id;
860
861 CURSOR csr_line_old_rate(cv_claim_line_id IN NUMBER) IS
862 SELECT rate
863 FROM ozf_claim_lines
864 WHERE claim_line_id = cv_claim_line_id;
865
866 l_csr_product_name csr_product_name%ROWTYPE;
867 l_cust_account_id NUMBER;
868 l_order_type_id NUMBER;
869 l_claim_currency VARCHAR2(15);
870 --l_price_list_id NUMBER := 1000;
871 l_item_tbl claim_line_item_tbl_type;
872 l_inv_line_tbl claim_line_item_tbl_type;
873 l_ord_line_tbl claim_line_item_tbl_type;
874 i NUMBER;
875 idx_item NUMBER := 1;
876 idx_inv_line NUMBER := 1;
877 idx_ord_line NUMBER := 1;
878 l_org_id NUMBER;
879 l_line_old_rate NUMBER;
880
881 BEGIN
882 IF OZF_DEBUG_HIGH_ON THEN
883 OZF_Utility_PVT.debug_message(l_full_name||' : start');
884 END IF;
885
886 -- Initialize API return status to sucess
887 x_return_status := FND_API.g_ret_sts_success;
888
889 --l_org_id := TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10));
890
891 ----------------------------
892 -- Default RMA Line Price --
893 ----------------------------
894 i := p_x_claim_line_tbl.FIRST;
895
896 IF OZF_DEBUG_HIGH_ON THEN
897 OZF_Utility_PVT.debug_message(l_full_name||' p_x_claim_line_tbl.FIRST='||i);
898 END IF;
899
900 IF i IS NOT NULL THEN
901 OPEN csr_claim(p_x_claim_line_tbl(1).claim_id);
902 FETCH csr_claim INTO l_cust_account_id
903 , l_order_type_id
904 , l_claim_currency;
905 CLOSE csr_claim;
906
907 LOOP
908 IF OZF_DEBUG_HIGH_ON THEN
909 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);
910 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);
911 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);
912 OZF_Utility_PVT.debug_message(l_full_name||' : p_x_claim_line_tbl('||i||').item_type='||p_x_claim_line_tbl(i).item_type);
913 OZF_Utility_PVT.debug_message(l_full_name||' : p_x_claim_line_tbl('||i||').item_id='||p_x_claim_line_tbl(i).item_id);
914 END IF;
915 IF ( p_x_claim_line_tbl(i).source_object_line_id IS NOT NULL AND
916 p_x_claim_line_tbl(i).source_object_line_id <> FND_API.g_miss_num
917 ) OR
918 ( p_x_claim_line_tbl(i).item_type = 'PRODUCT' AND
919 p_x_claim_line_tbl(i).item_id IS NOT NULL AND
920 p_x_claim_line_tbl(i).item_id <> FND_API.g_miss_num AND
921 p_x_claim_line_tbl(i).rate IS NULL
922 ) THEN
923 -- Quantity is required for pricing item
924 IF p_x_claim_line_tbl(i).quantity IS NULL THEN
925 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
926 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_QUANTITY_REQ');
927 FND_MSG_PUB.add;
928 END IF;
929 RAISE FND_API.G_EXC_ERROR;
930 END IF;
931
932 IF p_x_claim_line_tbl(i).source_object_line_id IS NOT NULL AND
933 p_x_claim_line_tbl(i).source_object_line_id <> FND_API.g_miss_num AND
934 p_x_claim_line_tbl(i).rate IS NOT NULL THEN
935 IF p_x_claim_line_tbl(i).source_object_class = 'INVOICE' THEN
936 l_inv_line_tbl(idx_inv_line).claim_line_index := i;
937 l_inv_line_tbl(idx_inv_line).source_object_class := p_x_claim_line_tbl(i).source_object_class;
938 l_inv_line_tbl(idx_inv_line).source_object_id := p_x_claim_line_tbl(i).source_object_id;
939 l_inv_line_tbl(idx_inv_line).source_object_line_id := p_x_claim_line_tbl(i).source_object_line_id;
940 idx_inv_line := idx_inv_line + 1;
941 ELSIF p_x_claim_line_tbl(i).source_object_class = 'ORDER' THEN
942 l_ord_line_tbl(idx_ord_line).claim_line_index := i;
943 l_ord_line_tbl(idx_ord_line).source_object_class := p_x_claim_line_tbl(i).source_object_class;
944 l_ord_line_tbl(idx_ord_line).source_object_id := p_x_claim_line_tbl(i).source_object_id;
945 l_ord_line_tbl(idx_ord_line).source_object_line_id := p_x_claim_line_tbl(i).source_object_line_id;
946 idx_ord_line := idx_ord_line + 1;
947 END IF;
948 ELSIF p_x_claim_line_tbl(i).item_type = 'PRODUCT' AND
949 p_x_claim_line_tbl(i).item_id IS NOT NULL AND
950 p_x_claim_line_tbl(i).item_id <> FND_API.g_miss_num AND
951 p_x_claim_line_tbl(i).rate IS NULL THEN
952 l_item_tbl(idx_item).claim_line_index := i;
953 l_item_tbl(idx_item).item_id := p_x_claim_line_tbl(i).item_id;
954 l_item_tbl(idx_item).quantity := p_x_claim_line_tbl(i).quantity;
955 -- UOM is required for pricing item
956 IF p_x_claim_line_tbl(i).quantity_uom IS NULL THEN
957 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
958 l_org_id := FND_PROFILE.value('AMS_ITEM_ORGANIZATION_ID');
959 OPEN csr_product_name(p_x_claim_line_tbl(i).item_id, l_org_id);
960 --OPEN csr_product_name(p_x_claim_line_tbl(i).item_id, l_org_id);
961 FETCH csr_product_name INTO l_csr_product_name;
962 CLOSE csr_product_name;
963 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_UOM_REQ');
964 FND_MESSAGE.set_token('ITEM', l_csr_product_name.description);
965 FND_MSG_PUB.add;
966 END IF;
967 RAISE FND_API.G_EXC_ERROR;
968 ELSE
969 l_item_tbl(idx_item).quantity_uom := p_x_claim_line_tbl(i).quantity_uom;
970 END IF;
971 l_item_tbl(idx_item).currency_code := l_claim_currency;
972 idx_item := idx_item + 1;
973 END IF;
974 ELSIF p_x_claim_line_tbl(i).rate IS NOT NULL AND
975 p_x_claim_line_tbl(i).rate <> FND_API.g_miss_num THEN
976 -- create mode
977 IF p_x_claim_line_tbl(i).claim_line_id IS NULL OR
978 p_x_claim_line_tbl(i).claim_line_id = FND_API.g_miss_num THEN
979 p_x_claim_line_tbl(i).payment_status := 'N'; --set calculate_price_flag
980
981 -- update mode
982 ELSE
983 IF p_x_claim_line_tbl(i).payment_status = 'Y' THEN
984 OPEN csr_line_old_rate(p_x_claim_line_tbl(i).claim_line_id);
985 FETCH csr_line_old_rate INTO l_line_old_rate;
986 CLOSE csr_line_old_rate;
987 IF p_x_claim_line_tbl(i).rate <> l_line_old_rate THEN
988 p_x_claim_line_tbl(i).payment_status := 'N';
989 END IF;
990 END IF;
991 END IF;
992 END IF;
993 EXIT WHEN i = p_x_claim_line_tbl.LAST;
994 i := p_x_claim_line_tbl.NEXT(i);
995 END LOOP;
996 END IF;
997
998 IF OZF_DEBUG_HIGH_ON THEN
999 OZF_Utility_PVT.debug_message(l_full_name||' : count-inv line tbl='||l_inv_line_tbl.COUNT);
1000 OZF_Utility_PVT.debug_message(l_full_name||' : count-ord line tbl='||l_ord_line_tbl.COUNT);
1001 OZF_Utility_PVT.debug_message(l_full_name||' : count-prd line tbl='||l_item_tbl.COUNT);
1002 END IF;
1003
1004 ------ Price Item -------
1005 i := l_item_tbl.FIRST;
1006 IF i IS NOT NULL THEN
1007 Price_Item(
1008 p_cust_account_id => l_cust_account_id
1009 ,p_order_type_id => l_order_type_id
1010 --,p_price_list_id => l_price_list_id
1011 ,p_currency_code => l_claim_currency
1012
1013 ,x_return_status => l_return_status
1014 ,x_msg_data => x_msg_data
1015 ,x_msg_count => x_msg_count
1016
1017 ,p_x_item_tbl => l_item_tbl
1018 );
1019 IF l_return_status = FND_API.g_ret_sts_error THEN
1020 RAISE FND_API.g_exc_error;
1021 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1022 RAISE FND_API.g_exc_unexpected_error;
1023 END IF;
1024
1025 LOOP
1026 p_x_claim_line_tbl(l_item_tbl(i).claim_line_index).rate := l_item_tbl(i).rate;
1027 p_x_claim_line_tbl(l_item_tbl(i).claim_line_index).payment_status := 'Y'; -- calculate_price_flag
1028 EXIT WHEN i = l_item_tbl.LAST;
1029 i := l_item_tbl.NEXT(i);
1030 END LOOP;
1031 END IF;
1032
1033 ------ Price Invoice Line -------
1034 i := l_inv_line_tbl.FIRST;
1035 IF i IS NOT NULL THEN
1036
1037 OZF_Utility_PVT.debug_message('Calling the Price Invoice line .... **... ');
1038
1039 Price_Invoice_Line(
1040 x_return_status => l_return_status
1041 ,x_msg_data => x_msg_data
1042 ,x_msg_count => x_msg_count
1043 ,p_x_inv_line_tbl => l_inv_line_tbl
1044 );
1045 IF l_return_status = FND_API.g_ret_sts_error THEN
1046 RAISE FND_API.g_exc_error;
1047 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1048 RAISE FND_API.g_exc_unexpected_error;
1049 END IF;
1050
1051 LOOP
1052 p_x_claim_line_tbl(l_inv_line_tbl(i).claim_line_index).item_id := l_inv_line_tbl(i).item_id;
1053 p_x_claim_line_tbl(l_inv_line_tbl(i).claim_line_index).rate := l_inv_line_tbl(i).rate;
1054 p_x_claim_line_tbl(l_inv_line_tbl(i).claim_line_index).quantity_uom := l_inv_line_tbl(i).quantity_uom;
1055 p_x_claim_line_tbl(l_inv_line_tbl(i).claim_line_index).payment_status := 'Y'; -- calculate_price_flag
1056 EXIT WHEN i = l_inv_line_tbl.LAST;
1057 i := l_inv_line_tbl.NEXT(i);
1058 END LOOP;
1059 END IF;
1060
1061 ------ Price Order Line -------
1062 i := l_ord_line_tbl.FIRST;
1063 IF i IS NOT NULL THEN
1064 Price_Order_Line(
1065 x_return_status => l_return_status
1066 ,x_msg_data => x_msg_data
1067 ,x_msg_count => x_msg_count
1068 ,p_x_ord_line_tbl => l_ord_line_tbl
1069 );
1070 IF l_return_status = FND_API.g_ret_sts_error THEN
1071 RAISE FND_API.g_exc_error;
1072 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1073 RAISE FND_API.g_exc_unexpected_error;
1074 END IF;
1075
1076 LOOP
1077 p_x_claim_line_tbl(l_ord_line_tbl(i).claim_line_index).item_id := l_ord_line_tbl(i).item_id;
1078 p_x_claim_line_tbl(l_ord_line_tbl(i).claim_line_index).rate := l_ord_line_tbl(i).rate;
1079 p_x_claim_line_tbl(l_ord_line_tbl(i).claim_line_index).quantity_uom := l_ord_line_tbl(i).quantity_uom;
1080 p_x_claim_line_tbl(l_ord_line_tbl(i).claim_line_index).payment_status := 'Y'; -- calculate_price_flag
1081 EXIT WHEN i = l_ord_line_tbl.LAST;
1082 i := l_ord_line_tbl.NEXT(i);
1083 END LOOP;
1084 END IF;
1085
1086 IF OZF_DEBUG_HIGH_ON THEN
1087 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1088 END IF;
1089 EXCEPTION
1090 WHEN FND_API.G_EXC_ERROR THEN
1091 x_return_status := FND_API.G_RET_STS_ERROR;
1092 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1093 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1094 WHEN OTHERS THEN
1095 IF OZF_DEBUG_LOW_ON THEN
1096 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1097 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
1098 FND_MSG_PUB.Add;
1099 END IF;
1100 x_return_status := FND_API.g_ret_sts_unexp_error;
1101
1102 END Default_Claim_Line_Tbl;
1103
1104
1105 /*=======================================================================*
1106 | PROCEDURE
1107 | Validate_Claim_Line
1108 |
1109 | NOTES
1110 | This API validate claim line recored against RMA settlement.
1111 |
1112 | HISTORY
1113 | 30-JUL-2002 mchang Create.
1114 *=======================================================================*/
1115 PROCEDURE Validate_Claim_Line(
1116 p_api_version IN NUMBER
1117 ,p_init_msg_list IN VARCHAR2
1118 ,p_validation_level IN NUMBER
1119
1120 ,x_return_status OUT NOCOPY VARCHAR2
1121 ,x_msg_data OUT NOCOPY VARCHAR2
1122 ,x_msg_count OUT NOCOPY NUMBER
1123
1124 ,p_claim_line_rec IN OZF_CLAIM_LINE_PVT.claim_line_rec_type
1125 )
1126 IS
1127 l_api_version CONSTANT NUMBER := 1.0;
1128 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Claim_Line';
1129 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1130
1131
1132 l_error BOOLEAN := FALSE;
1133
1134 BEGIN
1135 IF OZF_DEBUG_HIGH_ON THEN
1136 OZF_Utility_PVT.debug_message(l_full_name||' : start');
1137 END IF;
1138
1139 -- Initialize API return status to sucess
1140 x_return_status := FND_API.g_ret_sts_success;
1141
1142 IF OZF_DEBUG_HIGH_ON THEN
1143 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1144 END IF;
1145 EXCEPTION
1146 WHEN FND_API.G_EXC_ERROR THEN
1147 x_return_status := FND_API.G_RET_STS_ERROR;
1148 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1149 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1150 WHEN OTHERS THEN
1151 IF OZF_DEBUG_LOW_ON THEN
1152 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1153 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
1154 FND_MSG_PUB.Add;
1155 END IF;
1156 x_return_status := FND_API.g_ret_sts_unexp_error;
1157
1158 END Validate_Claim_Line;
1159
1160
1161 /*=======================================================================*
1162 | PROCEDURE
1163 | Validate_Claim_Line_Tbl
1164 |
1165 | NOTES
1166 | This API validate claim line table against RMA settlement.
1167 |
1168 | HISTORY
1169 | 30-JUL-2002 mchang Create.
1170 *=======================================================================*/
1171 PROCEDURE Validate_Claim_Line_Tbl(
1172 p_api_version IN NUMBER
1173 ,p_init_msg_list IN VARCHAR2
1174 ,p_validation_level IN NUMBER
1175
1176 ,x_return_status OUT NOCOPY VARCHAR2
1177 ,x_msg_data OUT NOCOPY VARCHAR2
1178 ,x_msg_count OUT NOCOPY NUMBER
1179
1180 ,p_claim_line_tbl IN OZF_CLAIM_LINE_PVT.claim_line_tbl_type
1181 )
1182 IS
1183 l_api_version CONSTANT NUMBER := 1.0;
1184 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Claim_Line_Tbl';
1185 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1186 l_return_status VARCHAR2(1);
1187
1188 i NUMBER;
1189 l_error BOOLEAN := FALSE;
1190
1191 BEGIN
1192 IF OZF_DEBUG_HIGH_ON THEN
1193 OZF_Utility_PVT.debug_message(l_full_name||' : start');
1194 END IF;
1195
1196 -- Initialize API return status to sucess
1197 x_return_status := FND_API.g_ret_sts_success;
1198
1199 ----------------------- Start -----------------------
1200 i := p_claim_line_tbl.FIRST;
1201 IF i IS NOT NULL THEN
1202 LOOP
1203 IF p_claim_line_tbl(i).claim_line_id IS NOT NULL THEN
1204 Validate_Claim_Line(
1205 p_api_version => l_api_version
1206 ,p_init_msg_list => FND_API.g_false
1207 ,p_validation_level => FND_API.g_valid_level_full
1208 ,x_return_status => l_return_status
1209 ,x_msg_data => x_msg_data
1210 ,x_msg_count => x_msg_count
1211 ,p_claim_line_rec => p_claim_line_tbl(i)
1212 );
1213 IF l_return_status = FND_API.g_ret_sts_error THEN
1214 RAISE FND_API.g_exc_error;
1215 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1216 RAISE FND_API.g_exc_unexpected_error;
1217 END IF;
1218 END IF;
1219 EXIT WHEN i = p_claim_line_tbl.LAST;
1220 i := p_claim_line_tbl.NEXT(i);
1221 END LOOP;
1222 END IF;
1223
1224 IF l_error THEN
1225 RAISE FND_API.G_EXC_ERROR;
1226 END IF;
1227
1228
1229 IF OZF_DEBUG_HIGH_ON THEN
1230 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1231 END IF;
1232 EXCEPTION
1233 WHEN FND_API.G_EXC_ERROR THEN
1234 x_return_status := FND_API.G_RET_STS_ERROR;
1235 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1237 WHEN OTHERS THEN
1238 IF OZF_DEBUG_LOW_ON THEN
1239 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1240 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
1241 FND_MSG_PUB.Add;
1242 END IF;
1243 x_return_status := FND_API.g_ret_sts_unexp_error;
1244
1245 END Validate_Claim_Line_Tbl;
1246
1247 -- kishore
1248 -- Fix for bug 4565361
1249 /*=======================================================================*
1250 | Procedure
1251 | Validate_reference_information
1252 |
1253 | Return
1254 |
1255 | NOTES
1256 |
1257 | HISTORY
1258 | 15-DEC-2005 kdhulipa Created.
1259 | 17-Jan-2006 kdhulipa Fix for bug 4565507
1260 *=======================================================================*/
1261 PROCEDURE Validate_reference_information(
1262 x_return_status OUT NOCOPY VARCHAR2
1263 ,x_msg_data OUT NOCOPY VARCHAR2
1264 ,x_msg_count OUT NOCOPY NUMBER
1265 ,p_source_object_id IN NUMBER
1266 ,p_source_object_line_id IN NUMBER
1267 ,p_source_object_class IN VARCHAR2
1268 ,p_quantity IN NUMBER
1269 )
1270 IS
1271 l_api_version CONSTANT NUMBER := 1.0;
1272 l_api_name CONSTANT VARCHAR2(30) := 'Validate_reference_information';
1273 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1274
1275 CURSOR csr_order_reference(cv_customer_trx_id IN NUMBER) IS
1276 SELECT interface_header_context
1277 FROM ra_customer_trx_all
1278 WHERE customer_trx_id = cv_customer_trx_id;
1279
1280 CURSOR csr_ord_total_quantity(cv_header_id IN NUMBER) IS
1281 SELECT ordered_quantity
1282 FROM oe_order_lines_all
1283 WHERE header_id = cv_header_id;
1284
1285 CURSOR csr_ord_return_quantity(cv_header_id IN NUMBER, cv_line_id NUMBER) IS
1286 SELECT sum(nvl(ordered_quantity, 0))
1287 FROM oe_order_lines_all
1288 WHERE reference_header_id = cv_header_id
1289 AND reference_line_id = cv_line_id
1290 AND booked_flag = 'Y'
1291 AND cancelled_flag <> 'Y'
1292 AND line_category_code = 'RETURN';
1293
1294 CURSOR csr_inv_ord_number(cv_cust_id IN NUMBER) IS
1295 SELECT interface_header_attribute1
1296 FROM ra_customer_trx_all
1297 WHERE customer_trx_id = cv_cust_id;
1298
1299 CURSOR csr_inv_header_id(cv_customer_trx IN NUMBER) IS
1300 SELECT header_id
1301 FROM oe_order_headers_all
1302 WHERE order_number = cv_customer_trx;
1303
1304 CURSOR csr_inv_line_id(cv_line_id IN NUMBER) IS
1305 SELECT interface_line_attribute6
1306 FROM ra_customer_trx_lines_all
1307 WHERE customer_trx_line_id = cv_line_id;
1308
1309 l_reference_header varchar(20);
1310 l_total_order_quantity NUMBER;
1311 l_return_order_quantity NUMBER;
1312 l_inv_order_number NUMBER;
1313 l_inv_header_id NUMBER;
1314 l_inv_line_id NUMBER;
1315
1316 BEGIN
1317
1318 IF OZF_DEBUG_HIGH_ON THEN
1319 OZF_Utility_PVT.debug_message(l_full_name||' : start');
1320 END IF;
1321
1322 -- check whether invoice is generated from ORDER or not.
1323 IF p_source_object_class = 'INVOICE' THEN
1324 OPEN csr_order_reference(p_source_object_id);
1325 FETCH csr_order_reference INTO l_reference_header;
1326 CLOSE csr_order_reference;
1327 IF nvl(l_reference_header, 'OZF_DUMMY') <> 'ORDER ENTRY' THEN
1328 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1329 FND_MESSAGE.Set_Name('OZF','OZF_CLAIM_INVOICE_ORDER_ERROR');
1330 FND_MSG_PUB.Add;
1331 END IF;
1332 RAISE FND_API.G_EXC_ERROR;
1333 END IF;
1334 END IF;
1335
1336
1337 -- BUG 4565507 Begin
1338 IF p_source_object_class = 'ORDER' THEN
1339
1340 OPEN csr_ord_return_quantity(p_source_object_id, p_source_object_line_id);
1341 FETCH csr_ord_return_quantity INTO l_return_order_quantity;
1342 CLOSE csr_ord_return_quantity;
1343
1344 OPEN csr_ord_total_quantity(p_source_object_id);
1345 FETCH csr_ord_total_quantity into l_total_order_quantity;
1346 CLOSE csr_ord_total_quantity;
1347
1348 IF l_return_order_quantity IS NOT NULL THEN
1349 IF ( (p_quantity + l_return_order_quantity ) > l_total_order_quantity ) THEN
1350 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1351 FND_MESSAGE.Set_Name('OZF','OZF_RETURN_INVALID_QUANTITY');
1352 FND_MSG_PUB.Add;
1353 END IF;
1354 RAISE FND_API.G_EXC_ERROR;
1355 END IF;
1356 END IF;
1357 END IF;
1358
1359 IF p_source_object_class = 'INVOICE' THEN
1360
1361 -- to find the order number
1362 OPEN csr_inv_ord_number(p_source_object_id);
1363 FETCH csr_inv_ord_number INTO l_inv_order_number;
1364 CLOSE csr_inv_ord_number;
1365
1366 -- to find header id
1367 OPEN csr_inv_header_id(l_inv_order_number);
1368 FETCH csr_inv_header_id INTO l_inv_header_id;
1369 CLOSE csr_inv_header_id;
1370
1371 -- to find the line id
1372 OPEN csr_inv_line_id(p_source_object_line_id);
1373 FETCH csr_inv_line_id INTO l_inv_line_id;
1374 CLOSE csr_inv_line_id;
1375
1376 OPEN csr_ord_return_quantity(l_inv_header_id, l_inv_line_id);
1377 FETCH csr_ord_return_quantity INTO l_return_order_quantity;
1378 CLOSE csr_ord_return_quantity;
1379
1380 OPEN csr_ord_total_quantity(l_inv_header_id);
1381 FETCH csr_ord_total_quantity into l_total_order_quantity;
1382 CLOSE csr_ord_total_quantity;
1383
1384 IF OZF_DEBUG_HIGH_ON THEN
1385 OZF_Utility_PVT.debug_message(' Order Number '|| l_inv_order_number);
1386 OZF_Utility_PVT.debug_message(' Return Order Quantity '|| l_return_order_quantity);
1387 OZF_Utility_PVT.debug_message(' Total quantity '|| l_total_order_quantity);
1388 END IF;
1389
1390 IF l_return_order_quantity IS NOT NULL THEN
1391 IF ( (p_quantity + l_return_order_quantity ) > l_total_order_quantity ) THEN
1392 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1393 FND_MESSAGE.Set_Name('OZF','OZF_RETURN_INVALID_QUANTITY');
1394 FND_MSG_PUB.Add;
1395 END IF;
1396 RAISE FND_API.G_EXC_ERROR;
1397 END IF;
1398 END IF;
1399
1400 END IF;
1401
1402 -- BUG 4565507 END
1403
1404
1405 EXCEPTION
1406 WHEN FND_API.G_EXC_ERROR THEN
1407 x_return_status := FND_API.G_RET_STS_ERROR;
1408 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1410 WHEN OTHERS THEN
1411 IF OZF_DEBUG_LOW_ON THEN
1412 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1413 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
1414 FND_MSG_PUB.Add;
1415 END IF;
1416 x_return_status := FND_API.g_ret_sts_unexp_error;
1417 END Validate_reference_information;
1418
1419
1420 /*=======================================================================*
1421 | Procedure
1422 | Validate_Return_Quantity
1423 |
1424 | Return
1425 |
1426 | NOTES
1427 |
1428 | HISTORY
1429 | 13-DEC-2002 mchang Create.
1430 *=======================================================================*/
1431 PROCEDURE Validate_Return_Quantity(
1432 x_return_status OUT NOCOPY VARCHAR2
1433 ,x_msg_data OUT NOCOPY VARCHAR2
1434 ,x_msg_count OUT NOCOPY NUMBER
1435
1436 ,p_claim_line_rec IN OZF_CLAIM_LINE_PVT.claim_line_rec_type
1437 )
1438 IS
1439 l_api_version CONSTANT NUMBER := 1.0;
1440 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Return_Quantity';
1441 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1442 l_return_status VARCHAR2(1);
1443
1444 CURSOR csr_inv_line_quantity(cv_invoice_line_id IN NUMBER) IS
1445 SELECT quantity_invoiced
1446 , inventory_item_id
1447 FROM ra_customer_trx_lines
1448 WHERE customer_trx_line_id = cv_invoice_line_id;
1449
1450 CURSOR csr_ord_line_quantity(cv_order_line_id IN NUMBER) IS
1451 SELECT ordered_quantity
1452 , inventory_item_id
1453 FROM oe_order_lines
1454 WHERE line_id = cv_order_line_id;
1455
1456 CURSOR csr_product_name(cv_item_id IN NUMBER, cv_org_id IN NUMBER) IS
1457 SELECT description
1458 FROM mtl_system_items_vl
1459 WHERE inventory_item_id = cv_item_id
1460 AND organization_id = cv_org_id;
1461
1462 i NUMBER;
1463 l_most_quantity NUMBER;
1464 l_item_id NUMBER;
1465 l_csr_product_name csr_product_name%ROWTYPE;
1466 l_org_id NUMBER;
1467 l_error BOOLEAN := FALSE;
1468
1469 BEGIN
1470 IF OZF_DEBUG_HIGH_ON THEN
1471 OZF_Utility_PVT.debug_message(l_full_name||' : start');
1472 END IF;
1473
1474 -- Initialize API return status to sucess
1475 x_return_status := FND_API.g_ret_sts_success;
1476
1477 -- Checking Reference information -----
1478 -- kishore.
1479
1480 IF p_claim_line_rec.source_object_class IN ('INVOICE', 'ORDER') AND
1481 p_claim_line_rec.source_object_id IS NOT NULL AND
1482 p_claim_line_rec.source_object_line_id IS NOT NULL THEN
1483
1484 Validate_reference_information(
1485 x_return_status => l_return_status
1486 ,x_msg_data => x_msg_data
1487 ,x_msg_count => x_msg_count
1488 ,p_source_object_id => p_claim_line_rec.source_object_id
1489 ,p_source_object_line_id => p_claim_line_rec.source_object_line_id
1490 ,p_source_object_class => p_claim_line_rec.source_object_class
1491 ,p_quantity => p_claim_line_rec.quantity
1492 );
1493
1494 IF l_return_status = FND_API.g_ret_sts_error THEN
1495 RAISE FND_API.g_exc_error;
1496 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1497 RAISE FND_API.g_exc_unexpected_error;
1498 END IF;
1499
1500 END IF;
1501
1502 ----------------------- Start -----------------------
1503 IF p_claim_line_rec.source_object_class IN ('INVOICE', 'ORDER') AND
1504 p_claim_line_rec.source_object_id IS NOT NULL AND
1505 p_claim_line_rec.source_object_line_id IS NOT NULL THEN
1506 ------- INVOICE -------
1507 IF p_claim_line_rec.source_object_class = 'INVOICE' THEN
1508 OPEN csr_inv_line_quantity(p_claim_line_rec.source_object_line_id);
1509 FETCH csr_inv_line_quantity INTO l_most_quantity, l_item_id;
1510 CLOSE csr_inv_line_quantity;
1511 ------- ORDER -------
1512 ELSIF p_claim_line_rec.source_object_class = 'ORDER' THEN
1513 OPEN csr_ord_line_quantity(p_claim_line_rec.source_object_line_id);
1514 FETCH csr_ord_line_quantity INTO l_most_quantity, l_item_id;
1515 CLOSE csr_ord_line_quantity;
1516 END IF;
1517
1518 -- If both product and invoice/order line are defined,
1519 -- the product needs to belong to the same invoice/order line.
1520 IF p_claim_line_rec.item_id IS NOT NULL AND
1521 p_claim_line_rec.item_id <> l_item_id THEN
1522 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1523 l_org_id := FND_PROFILE.value('AMS_ITEM_ORGANIZATION_ID');
1524 OPEN csr_product_name(p_claim_line_rec.item_id, l_org_id);
1525 -- OPEN csr_product_name(p_claim_line_rec.item_id, l_org_id);
1526 FETCH csr_product_name INTO l_csr_product_name;
1527 CLOSE csr_product_name;
1528 FND_MESSAGE.set_name('OZF', 'OZF_SETL_VAL_PROD_ERR');
1529 FND_MESSAGE.set_token('PROD', l_csr_product_name.description);
1530 FND_MSG_PUB.add;
1531 END IF;
1532 l_error := TRUE;
1533 END IF;
1534
1535 IF p_claim_line_rec.quantity > l_most_quantity THEN
1536 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1537 FND_MESSAGE.set_name('OZF', 'OZF_SETL_VAL_RMA_QUTY_ERR');
1538 FND_MSG_PUB.add;
1539 END IF;
1540 l_error := TRUE;
1541 END IF;
1542 END IF;
1543
1544 IF l_error THEN
1545 RAISE FND_API.G_EXC_ERROR;
1546 END IF;
1547
1548 IF OZF_DEBUG_HIGH_ON THEN
1549 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1550 END IF;
1551 EXCEPTION
1552 WHEN FND_API.G_EXC_ERROR THEN
1553 x_return_status := FND_API.G_RET_STS_ERROR;
1554 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1556 WHEN OTHERS THEN
1557 IF OZF_DEBUG_LOW_ON THEN
1558 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1559 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
1560 FND_MSG_PUB.Add;
1561 END IF;
1562 x_return_status := FND_API.g_ret_sts_unexp_error;
1563
1564 END Validate_Return_Quantity;
1565
1566 /*=======================================================================*
1567 | Procedure
1568 | Check_RMA_Item_Attribute
1569 |
1570 | Return
1571 |
1572 | NOTES
1573 |
1574 | HISTORY
1575 | 13-DEC-2002 mchang Create.
1576 *=======================================================================*/
1577 PROCEDURE Check_RMA_Item_Attribute(
1578 x_return_status OUT NOCOPY VARCHAR2
1579 ,x_msg_data OUT NOCOPY VARCHAR2
1580 ,x_msg_count OUT NOCOPY NUMBER
1581
1582 ,p_item_id IN NUMBER
1583 )
1584 IS
1585 l_api_version CONSTANT NUMBER := 1.0;
1586 l_api_name CONSTANT VARCHAR2(30) := 'Check_RMA_Item_Attribute';
1587 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1588
1589 CURSOR csr_item_attr(cv_item_id IN NUMBER, cv_org_id IN NUMBER) IS
1590 SELECT invoice_enabled_flag
1591 , invoiceable_item_flag
1592 , returnable_flag
1593 , description
1594 FROM mtl_system_items_vl
1595 WHERE inventory_item_id = cv_item_id
1596 AND organization_id = cv_org_id;
1597
1598 l_csr_item_attr csr_item_attr%ROWTYPE;
1599 l_org_id NUMBER;
1600 l_error BOOLEAN := FALSE;
1601
1602 BEGIN
1603 IF OZF_DEBUG_HIGH_ON THEN
1604 OZF_Utility_PVT.debug_message(l_full_name||' : start');
1605 END IF;
1606
1607 -- Initialize API return status to sucess
1608 x_return_status := FND_API.g_ret_sts_success;
1609
1610 -- l_org_id := TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10));
1611 l_org_id := FND_PROFILE.value('AMS_ITEM_ORGANIZATION_ID');
1612
1613 OPEN csr_item_attr(p_item_id, l_org_id);
1614 FETCH csr_item_attr INTO l_csr_item_attr;
1615 CLOSE csr_item_attr;
1616
1617 IF l_csr_item_attr.invoice_enabled_flag = 'N' OR
1618 l_csr_item_attr.invoiceable_item_flag = 'N' THEN
1619 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1620 FND_MESSAGE.set_name('OZF', 'OZF_SETL_VAL_RMA_INV_ERR');
1621 FND_MESSAGE.set_token('PROD', l_csr_item_attr.description);
1622 FND_MSG_PUB.add;
1623 END IF;
1624 l_error := TRUE;
1625 END IF;
1626
1627 IF l_csr_item_attr.returnable_flag <> 'Y' THEN
1628 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1629 FND_MESSAGE.set_name('OZF', 'OZF_SETL_VAL_ITEM_RMA_ERR');
1630 FND_MESSAGE.set_token('PROD', l_csr_item_attr.description);
1631 FND_MSG_PUB.add;
1632 END IF;
1633 l_error := TRUE;
1634 END IF;
1635
1636 IF l_error THEN
1637 RAISE FND_API.G_EXC_ERROR;
1638 END IF;
1639
1640 IF OZF_DEBUG_HIGH_ON THEN
1641 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1642 END IF;
1643 EXCEPTION
1644 WHEN FND_API.G_EXC_ERROR THEN
1645 x_return_status := FND_API.G_RET_STS_ERROR;
1646 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1647 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1648 WHEN OTHERS THEN
1649 IF OZF_DEBUG_LOW_ON THEN
1650 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1651 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
1652 FND_MSG_PUB.Add;
1653 END IF;
1654 x_return_status := FND_API.g_ret_sts_unexp_error;
1655
1656 END Check_RMA_Item_Attribute;
1657
1658
1659 /*=======================================================================*
1660 | Procedure
1661 | Check_RMA_Line_Items
1662 |
1663 | Return
1664 |
1665 | NOTES
1666 |
1667 | HISTORY
1668 | 13-DEC-2002 mchang Create.
1669 *=======================================================================*/
1670 PROCEDURE Check_RMA_Line_Items(
1671 x_return_status OUT NOCOPY VARCHAR2
1672 ,x_msg_data OUT NOCOPY VARCHAR2
1673 ,x_msg_count OUT NOCOPY NUMBER
1674
1675 ,p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
1676 ,p_claim_line_rec IN OZF_CLAIM_LINE_PVT.claim_line_rec_type
1677 )
1678 IS
1679 l_api_version CONSTANT NUMBER := 1.0;
1680 l_api_name CONSTANT VARCHAR2(30) := 'Check_RMA_Line_Items';
1681 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1682
1683 l_error BOOLEAN := FALSE;
1684
1685 BEGIN
1686 IF OZF_DEBUG_HIGH_ON THEN
1687 OZF_Utility_PVT.debug_message(l_full_name||' : start');
1688 END IF;
1689
1690 -- Initialize API return status to sucess
1691 x_return_status := FND_API.g_ret_sts_success;
1692
1693 -- 1. invoice line or product is required
1694 IF (p_claim_line_rec.item_id IS NULL OR p_claim_line_rec.item_type <> 'PRODUCT') AND
1695 p_claim_line_rec.source_object_line_id IS NULL THEN
1696 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1697 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_PROD_ERR');
1698 FND_MSG_PUB.add;
1699 END IF;
1700 l_error := TRUE;
1701 END IF;
1702
1703 -- 2. quantity is required
1704 IF p_claim_line_rec.quantity IS NULL THEN
1705 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1706 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_QUANTITY_ERR');
1707 FND_MSG_PUB.add;
1708 END IF;
1709 l_error := TRUE;
1710 END IF;
1711
1712 -- 3. uom is required
1713 IF p_claim_line_rec.quantity_uom IS NULL THEN
1714 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1715 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_UOM_ERR');
1716 FND_MSG_PUB.add;
1717 END IF;
1718 l_error := TRUE;
1719 END IF;
1720
1721 -- 4. price is required
1722 IF p_claim_line_rec.rate IS NULL THEN
1723 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1724 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_RATE_ERR');
1725 FND_MSG_PUB.add;
1726 END IF;
1727 l_error := TRUE;
1728 END IF;
1729
1730 -- 5. Pay Related Customer is not applicable if RMA is with referenced order/line
1731 IF p_claim_line_rec.source_object_class IN ('INVOICE', 'ORDER') AND
1732 p_claim_line_rec.source_object_line_id IS NOT NULL AND
1733 p_claim_rec.pay_related_account_flag = 'T' THEN
1734 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1735 FND_MESSAGE.set_name('OZF', 'OZF_SETL_RMA_REL_CUST_NA');
1736 FND_MSG_PUB.add;
1737 END IF;
1738 l_error := TRUE;
1739 END IF;
1740
1741 IF l_error THEN
1742 RAISE FND_API.G_EXC_ERROR;
1743 END IF;
1744
1745 IF OZF_DEBUG_HIGH_ON THEN
1746 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1747 END IF;
1748 EXCEPTION
1749 WHEN FND_API.G_EXC_ERROR THEN
1750 x_return_status := FND_API.G_RET_STS_ERROR;
1751 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1753 WHEN OTHERS THEN
1754 IF OZF_DEBUG_LOW_ON THEN
1755 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1756 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
1757 FND_MSG_PUB.Add;
1758 END IF;
1759 x_return_status := FND_API.g_ret_sts_unexp_error;
1760
1761 END Check_RMA_Line_Items;
1762
1763
1764 /*=======================================================================*
1765 | Procedure
1766 | Complete_RMA_Validation
1767 |
1768 | Return
1769 |
1770 | NOTES
1771 |
1772 | HISTORY
1773 | 24-OCT-2002 mchang Create.
1774 *=======================================================================*/
1775 PROCEDURE Complete_RMA_Validation(
1776 p_api_version IN NUMBER
1777 ,p_init_msg_list IN VARCHAR2
1778 ,p_validation_level IN NUMBER
1779
1780 ,x_return_status OUT NOCOPY VARCHAR2
1781 ,x_msg_data OUT NOCOPY VARCHAR2
1782 ,x_msg_count OUT NOCOPY NUMBER
1783
1784 ,p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
1785 ,x_claim_rec OUT NOCOPY OZF_CLAIM_PVT.claim_rec_type
1786 )
1787 IS
1788 l_api_version CONSTANT NUMBER := 1.0;
1789 l_api_name CONSTANT VARCHAR2(30) := 'Complete_RMA_Validation';
1790 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1791 l_return_status VARCHAR2(1);
1792
1793 i NUMBER;
1794 l_claim_line_tbl OZF_CLAIM_LINE_PVT.claim_line_tbl_type;
1795 l_claim_line_rec OZF_CLAIM_LINE_PVT.claim_line_rec_type;
1796
1797 BEGIN
1798 IF OZF_DEBUG_HIGH_ON THEN
1799 OZF_Utility_PVT.debug_message(l_full_name||' : start');
1800 END IF;
1801
1802 -- Initialize API return status to sucess
1803 x_return_status := FND_API.g_ret_sts_success;
1804
1805 x_claim_rec := p_claim_rec;
1806
1807 ----------------------- Start -----------------------
1808 OZF_OM_PAYMENT_PVT.Query_Claim_Line(
1809 p_claim_id => p_claim_rec.claim_id
1810 ,x_claim_line_tbl => l_claim_line_tbl
1811 ,x_return_status => l_return_status
1812 );
1813 IF l_return_status = FND_API.g_ret_sts_error THEN
1814 RAISE FND_API.g_exc_error;
1815 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1816 RAISE FND_API.g_exc_unexpected_error;
1817 END IF;
1818
1819 i := l_claim_line_tbl.FIRST;
1820 IF i IS NOT NULL THEN
1821 LOOP
1822 IF l_claim_line_tbl(i).claim_line_id IS NOT NULL THEN
1823 l_claim_line_rec := l_claim_line_tbl(i);
1824
1825 -------------------------------
1826 -- RMA Line Items Validation --
1827 -------------------------------
1828 Check_RMA_Line_Items(
1829 x_return_status => l_return_status
1830 ,x_msg_data => x_msg_data
1831 ,x_msg_count => x_msg_count
1832 ,p_claim_rec => p_claim_rec
1833 ,p_claim_line_rec => l_claim_line_rec
1834 );
1835 IF l_return_status = FND_API.g_ret_sts_error THEN
1836 RAISE FND_API.g_exc_error;
1837 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1838 RAISE FND_API.g_exc_unexpected_error;
1839 END IF;
1840
1841 -------------------------------------
1842 -- RMA Items Attributes Validation --
1843 -------------------------------------
1844 Check_RMA_Item_Attribute(
1845 x_return_status => l_return_status
1846 ,x_msg_data => x_msg_data
1847 ,x_msg_count => x_msg_count
1848 ,p_item_id => l_claim_line_rec.item_id
1849 );
1850 IF l_return_status = FND_API.g_ret_sts_error THEN
1851 RAISE FND_API.g_exc_error;
1852 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1853 RAISE FND_API.g_exc_unexpected_error;
1854 END IF;
1855
1856 --------------------------------
1857 -- Return Quantity Validation --
1858 --------------------------------
1859 Validate_Return_Quantity(
1860 x_return_status => l_return_status
1861 ,x_msg_data => x_msg_data
1862 ,x_msg_count => x_msg_count
1863 ,p_claim_line_rec => l_claim_line_rec
1864 );
1865 IF l_return_status = FND_API.g_ret_sts_error THEN
1866 RAISE FND_API.g_exc_error;
1867 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1868 RAISE FND_API.g_exc_unexpected_error;
1869 END IF;
1870
1871 END IF;
1872 EXIT WHEN i = l_claim_line_tbl.LAST;
1873 i := l_claim_line_tbl.NEXT(i);
1874 END LOOP;
1875 END IF;
1876
1877
1878 IF OZF_DEBUG_HIGH_ON THEN
1879 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1880 END IF;
1881 EXCEPTION
1882 WHEN FND_API.G_EXC_ERROR THEN
1883 x_return_status := FND_API.G_RET_STS_ERROR;
1884 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1886 WHEN OTHERS THEN
1887 IF OZF_DEBUG_LOW_ON THEN
1888 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1889 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
1890 FND_MSG_PUB.Add;
1891 END IF;
1892 x_return_status := FND_API.g_ret_sts_unexp_error;
1893
1894 END Complete_RMA_Validation;
1895
1896
1897
1898 END OZF_OM_VALIDATION_PVT;