[Home] [Help]
PACKAGE BODY: APPS.OZF_OM_PAYMENT_PVT
Source
1 PACKAGE BODY OZF_OM_PAYMENT_PVT AS
2 /* $Header: ozfvompb.pls 120.2 2005/11/21 00:22:36 sashetty noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_OM_PAYMENT_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(15) := 'ozfvompb.pls';
6
7 OZF_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
8
9 /*=======================================================================*
10 | PROCEDURE
11 | Query_Claim
12 |
13 | NOTES
14 |
15 | HISTORY
16 | 24-OCT-2002 mchang Create.
17 *=======================================================================*/
18 PROCEDURE Query_Claim(
19 p_claim_id IN NUMBER
20 ,x_claim_rec OUT NOCOPY OZF_Claim_PVT.claim_rec_type
21 ,x_return_status OUT NOCOPY VARCHAR2
22 )
23 IS
24 BEGIN
25 SELECT
26 claim_id
27 ,object_version_number
28 ,last_update_date
29 ,last_updated_by
30 ,creation_date
31 ,created_by
32 ,last_update_login
33 ,request_id
34 ,program_application_id
35 ,program_update_date
36 ,program_id
37 ,created_from
38 ,batch_id
39 ,claim_number
40 ,claim_type_id
41 ,claim_class
42 ,claim_date
43 ,due_date
44 ,owner_id
45 ,history_event
46 ,history_event_date
47 ,history_event_description
48 ,split_from_claim_id
49 ,duplicate_claim_id
50 ,split_date
51 ,root_claim_id
52 ,amount
53 ,amount_adjusted
54 ,amount_remaining
55 ,amount_settled
56 ,acctd_amount
57 ,acctd_amount_remaining
58 ,tax_amount
59 ,tax_code
60 ,tax_calculation_flag
61 ,currency_code
62 ,exchange_rate_type
63 ,exchange_rate_date
64 ,exchange_rate
65 ,set_of_books_id
66 ,original_claim_date
67 ,source_object_id
68 ,source_object_class
69 ,source_object_type_id
70 ,source_object_number
71 ,cust_account_id
72 ,cust_billto_acct_site_id
73 ,cust_shipto_acct_site_id
74 ,location_id
75 ,pay_related_account_flag
76 ,related_cust_account_id
77 ,related_site_use_id
78 ,relationship_type
79 ,vendor_id
80 ,vendor_site_id
81 ,reason_type
82 ,reason_code_id
83 ,task_template_group_id
84 ,status_code
85 ,user_status_id
86 ,sales_rep_id
87 ,collector_id
88 ,contact_id
89 ,broker_id
90 ,territory_id
91 ,customer_ref_date
92 ,customer_ref_number
93 ,assigned_to
94 ,receipt_id
95 ,receipt_number
96 ,doc_sequence_id
97 ,doc_sequence_value
98 ,gl_date
99 ,payment_method
100 ,voucher_id
101 ,voucher_number
102 ,payment_reference_id
103 ,payment_reference_number
104 ,payment_reference_date
105 ,payment_status
106 ,approved_flag
107 ,approved_date
108 ,approved_by
109 ,settled_date
110 ,settled_by
111 ,effective_date
112 ,custom_setup_id
113 ,task_id
114 ,country_id
115 ,comments
116 ,attribute_category
117 ,attribute1
118 ,attribute2
119 ,attribute3
120 ,attribute4
121 ,attribute5
122 ,attribute6
123 ,attribute7
124 ,attribute8
125 ,attribute9
126 ,attribute10
127 ,attribute11
128 ,attribute12
129 ,attribute13
130 ,attribute14
131 ,attribute15
132 ,deduction_attribute_category
133 ,deduction_attribute1
134 ,deduction_attribute2
135 ,deduction_attribute3
136 ,deduction_attribute4
137 ,deduction_attribute5
138 ,deduction_attribute6
139 ,deduction_attribute7
140 ,deduction_attribute8
141 ,deduction_attribute9
142 ,deduction_attribute10
143 ,deduction_attribute11
144 ,deduction_attribute12
145 ,deduction_attribute13
146 ,deduction_attribute14
147 ,deduction_attribute15
148 ,org_id
149 ,order_type_id
150 INTO
151 x_claim_rec.claim_id
152 ,x_claim_rec.object_version_number
153 ,x_claim_rec.last_update_date
154 ,x_claim_rec.last_updated_by
155 ,x_claim_rec.creation_date
156 ,x_claim_rec.created_by
157 ,x_claim_rec.last_update_login
158 ,x_claim_rec.request_id
159 ,x_claim_rec.program_application_id
160 ,x_claim_rec.program_update_date
161 ,x_claim_rec.program_id
162 ,x_claim_rec.created_from
163 ,x_claim_rec.batch_id
164 ,x_claim_rec.claim_number
165 ,x_claim_rec.claim_type_id
166 ,x_claim_rec.claim_class
167 ,x_claim_rec.claim_date
168 ,x_claim_rec.due_date
169 ,x_claim_rec.owner_id
170 ,x_claim_rec.history_event
171 ,x_claim_rec.history_event_date
172 ,x_claim_rec.history_event_description
173 ,x_claim_rec.split_from_claim_id
174 ,x_claim_rec.duplicate_claim_id
175 ,x_claim_rec.split_date
176 ,x_claim_rec.root_claim_id
177 ,x_claim_rec.amount
178 ,x_claim_rec.amount_adjusted
179 ,x_claim_rec.amount_remaining
180 ,x_claim_rec.amount_settled
181 ,x_claim_rec.acctd_amount
182 ,x_claim_rec.acctd_amount_remaining
183 ,x_claim_rec.tax_amount
184 ,x_claim_rec.tax_code
185 ,x_claim_rec.tax_calculation_flag
186 ,x_claim_rec.currency_code
187 ,x_claim_rec.exchange_rate_type
188 ,x_claim_rec.exchange_rate_date
189 ,x_claim_rec.exchange_rate
190 ,x_claim_rec.set_of_books_id
191 ,x_claim_rec.original_claim_date
192 ,x_claim_rec.source_object_id
193 ,x_claim_rec.source_object_class
194 ,x_claim_rec.source_object_type_id
195 ,x_claim_rec.source_object_number
196 ,x_claim_rec.cust_account_id
197 ,x_claim_rec.cust_billto_acct_site_id
198 ,x_claim_rec.cust_shipto_acct_site_id
199 ,x_claim_rec.location_id
200 ,x_claim_rec.pay_related_account_flag
201 ,x_claim_rec.related_cust_account_id
202 ,x_claim_rec.related_site_use_id
203 ,x_claim_rec.relationship_type
204 ,x_claim_rec.vendor_id
205 ,x_claim_rec.vendor_site_id
206 ,x_claim_rec.reason_type
207 ,x_claim_rec.reason_code_id
208 ,x_claim_rec.task_template_group_id
209 ,x_claim_rec.status_code
210 ,x_claim_rec.user_status_id
211 ,x_claim_rec.sales_rep_id
212 ,x_claim_rec.collector_id
213 ,x_claim_rec.contact_id
214 ,x_claim_rec.broker_id
215 ,x_claim_rec.territory_id
216 ,x_claim_rec.customer_ref_date
217 ,x_claim_rec.customer_ref_number
218 ,x_claim_rec.assigned_to
219 ,x_claim_rec.receipt_id
220 ,x_claim_rec.receipt_number
221 ,x_claim_rec.doc_sequence_id
222 ,x_claim_rec.doc_sequence_value
223 ,x_claim_rec.gl_date
224 ,x_claim_rec.payment_method
225 ,x_claim_rec.voucher_id
226 ,x_claim_rec.voucher_number
227 ,x_claim_rec.payment_reference_id
228 ,x_claim_rec.payment_reference_number
229 ,x_claim_rec.payment_reference_date
230 ,x_claim_rec.payment_status
231 ,x_claim_rec.approved_flag
232 ,x_claim_rec.approved_date
233 ,x_claim_rec.approved_by
234 ,x_claim_rec.settled_date
235 ,x_claim_rec.settled_by
236 ,x_claim_rec.effective_date
237 ,x_claim_rec.custom_setup_id
238 ,x_claim_rec.task_id
239 ,x_claim_rec.country_id
240 ,x_claim_rec.comments
241 ,x_claim_rec.attribute_category
242 ,x_claim_rec.attribute1
243 ,x_claim_rec.attribute2
244 ,x_claim_rec.attribute3
245 ,x_claim_rec.attribute4
246 ,x_claim_rec.attribute5
247 ,x_claim_rec.attribute6
248 ,x_claim_rec.attribute7
249 ,x_claim_rec.attribute8
250 ,x_claim_rec.attribute9
251 ,x_claim_rec.attribute10
252 ,x_claim_rec.attribute11
253 ,x_claim_rec.attribute12
254 ,x_claim_rec.attribute13
255 ,x_claim_rec.attribute14
256 ,x_claim_rec.attribute15
257 ,x_claim_rec.deduction_attribute_category
258 ,x_claim_rec.deduction_attribute1
259 ,x_claim_rec.deduction_attribute2
260 ,x_claim_rec.deduction_attribute3
261 ,x_claim_rec.deduction_attribute4
262 ,x_claim_rec.deduction_attribute5
263 ,x_claim_rec.deduction_attribute6
264 ,x_claim_rec.deduction_attribute7
265 ,x_claim_rec.deduction_attribute8
266 ,x_claim_rec.deduction_attribute9
267 ,x_claim_rec.deduction_attribute10
268 ,x_claim_rec.deduction_attribute11
269 ,x_claim_rec.deduction_attribute12
270 ,x_claim_rec.deduction_attribute13
271 ,x_claim_rec.deduction_attribute14
272 ,x_claim_rec.deduction_attribute15
273 ,x_claim_rec.org_id
274 ,x_claim_rec.order_type_id
275 FROM ozf_claims
276 WHERE claim_id = p_claim_id ;
277
278 x_return_status := FND_API.g_ret_sts_success;
279 EXCEPTION
280 WHEN OTHERS THEN
281 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
282 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_QUERY_ERROR');
283 FND_MSG_PUB.add;
284 END IF;
285 IF OZF_DEBUG_HIGH_ON THEN
286 OZF_Utility_PVT.debug_message('Claim Id = '|| p_claim_id);
287 OZF_Utility_PVT.debug_message('SQLERRM = '|| SQLERRM);
288 END IF;
289 x_return_status := FND_API.g_ret_sts_unexp_error;
290
291 END Query_Claim;
292
293
294 /*=======================================================================*
295 | PROCEDURE
296 | Query_Claim_Line
297 |
298 | NOTES
299 |
300 | HISTORY
301 | 24-OCT-2002 mchang Create.
302 *=======================================================================*/
303 PROCEDURE Query_Claim_Line(
304 p_claim_id IN NUMBER
305 ,x_claim_line_tbl OUT NOCOPY OZF_CLAIM_LINE_PVT.claim_line_tbl_type
306 ,x_return_status OUT NOCOPY VARCHAR2
307 )
308 IS
309 CURSOR csr_claim_line(cv_claim_id IN NUMBER) IS
310 SELECT claim_line_id
311 , source_object_class
312 , source_object_id
313 , source_object_line_id
314 , item_id
315 , quantity
316 , quantity_uom
317 , rate
318 , claim_currency_amount
319 , tax_code
320 , payment_status
321 FROM ozf_claim_lines
322 WHERE claim_id = cv_claim_id;
323
324 i PLS_INTEGER := 1;
325
326 BEGIN
327 OPEN csr_claim_line(p_claim_id);
328 LOOP
329 FETCH csr_claim_line INTO x_claim_line_tbl(i).claim_line_id
330 , x_claim_line_tbl(i).source_object_class
331 , x_claim_line_tbl(i).source_object_id
332 , x_claim_line_tbl(i).source_object_line_id
333 , x_claim_line_tbl(i).item_id
334 , x_claim_line_tbl(i).quantity
335 , x_claim_line_tbl(i).quantity_uom
336 , x_claim_line_tbl(i).rate
337 , x_claim_line_tbl(i).claim_currency_amount
338 , x_claim_line_tbl(i).tax_code
339 , x_claim_line_tbl(i).payment_status;
340 EXIT WHEN csr_claim_line%NOTFOUND;
341 i := i + 1;
342 END LOOP;
343 CLOSE csr_claim_line;
344
345 x_return_status := FND_API.g_ret_sts_success;
346 EXCEPTION
347 WHEN OTHERS THEN
348 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
349 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_QUERY_ERROR');
350 FND_MSG_PUB.add;
351 END IF;
352 x_return_status := FND_API.g_ret_sts_unexp_error;
353
354 END Query_Claim_Line;
355
356
357 /*=======================================================================*
358 | PROCEDURE
359 | Complete_RMA_Order
360 |
361 | NOTES
362 |
363 | HISTORY
364 | 15-JAN-2003 mchang Create.
365 *=======================================================================*/
366 PROCEDURE Complete_RMA_Order(
367 p_x_claim_rec IN OUT NOCOPY OZF_CLAIM_PVT.claim_rec_type
368 ,p_claim_line_tbl IN OZF_CLAIM_LINE_PVT.claim_line_tbl_type
369
370 ,x_return_status OUT NOCOPY VARCHAR2
371 ,x_msg_data OUT NOCOPY VARCHAR2
372 ,x_msg_count OUT NOCOPY NUMBER
373 )
374 IS
375 l_api_version CONSTANT NUMBER := 1.0;
376 l_api_name CONSTANT VARCHAR2(30) := 'Complete_RMA_Order';
377 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
378 l_return_status VARCHAR2(1);
379
380 l_header_rec OE_ORDER_PUB.header_rec_type;
381 l_header_val_rec OE_ORDER_PUB.header_val_rec_type;
382 l_header_adj_tbl OE_ORDER_PUB.header_adj_tbl_type;
383 l_header_adj_val_tbl OE_ORDER_PUB.header_adj_val_tbl_type;
384 l_header_price_att_tbl OE_ORDER_PUB.header_price_att_tbl_type;
385 l_header_adj_att_tbl OE_ORDER_PUB.header_adj_att_tbl_type;
386 l_header_adj_assoc_tbl OE_ORDER_PUB.header_adj_assoc_tbl_type;
387 l_header_scredit_tbl OE_ORDER_PUB.header_scredit_tbl_type;
388 l_header_scredit_val_tbl OE_ORDER_PUB.header_scredit_val_tbl_type;
389
390 l_line_tbl OE_ORDER_PUB.line_tbl_type;
391 l_line_val_tbl OE_ORDER_PUB.line_val_tbl_type;
392 l_line_adj_tbl OE_ORDER_PUB.line_adj_tbl_type;
393 l_line_adj_val_tbl OE_ORDER_PUB.line_adj_val_tbl_type;
394 l_line_price_att_tbl OE_ORDER_PUB.line_price_att_tbl_type;
395 l_line_adj_att_tbl OE_ORDER_PUB.line_adj_att_tbl_type;
396 l_line_adj_assoc_tbl OE_ORDER_PUB.line_adj_assoc_tbl_type;
397 l_line_scredit_tbl OE_ORDER_PUB.line_scredit_tbl_type;
398 l_line_scredit_val_tbl OE_ORDER_PUB.line_scredit_val_tbl_type;
399 l_lot_serial_tbl OE_ORDER_PUB.lot_serial_tbl_type;
400 l_lot_serial_val_tbl OE_ORDER_PUB.lot_serial_val_tbl_type;
401 l_action_request_tbl OE_ORDER_PUB.request_tbl_type;
402
403 l_x_header_rec OE_ORDER_PUB.header_rec_type;
404 l_x_header_val_rec OE_ORDER_PUB.header_val_rec_type;
405 l_x_header_adj_tbl OE_ORDER_PUB.header_adj_tbl_type;
406 l_x_header_adj_val_tbl OE_ORDER_PUB.header_adj_val_tbl_type;
407 l_x_header_price_att_tbl OE_ORDER_PUB.header_price_att_tbl_type;
408 l_x_header_adj_att_tbl OE_ORDER_PUB.header_adj_att_tbl_type;
409 l_x_header_adj_assoc_tbl OE_ORDER_PUB.header_adj_assoc_tbl_type;
410 l_x_header_scredit_tbl OE_ORDER_PUB.header_scredit_tbl_type;
411 l_x_header_scredit_val_tbl OE_ORDER_PUB.header_scredit_val_tbl_type;
412
413 l_x_line_tbl OE_ORDER_PUB.line_tbl_type;
414 l_x_line_val_tbl OE_ORDER_PUB.line_val_tbl_type;
415 l_x_line_adj_tbl OE_ORDER_PUB.line_adj_tbl_type;
416 l_x_line_adj_val_tbl OE_ORDER_PUB.line_adj_val_tbl_type;
417 l_x_line_price_att_tbl OE_ORDER_PUB.line_price_att_tbl_type;
418 l_x_line_adj_att_tbl OE_ORDER_PUB.line_adj_att_tbl_type;
419 l_x_line_adj_assoc_tbl OE_ORDER_PUB.line_adj_assoc_tbl_type;
420 l_x_line_scredit_tbl OE_ORDER_PUB.line_scredit_tbl_type;
421 l_x_line_scredit_val_tbl OE_ORDER_PUB.line_scredit_val_tbl_type;
422 l_x_lot_serial_tbl OE_ORDER_PUB.lot_serial_tbl_type;
423 l_x_lot_serial_val_tbl OE_ORDER_PUB.lot_serial_val_tbl_type;
424 l_x_action_request_tbl OE_ORDER_PUB.request_tbl_type;
425
426 i NUMBER;
427 l_adj_idx NUMBER := 1;
428 l_return_reson_code VARCHAR2(30);
429 l_line_type_id NUMBER;
430 l_price_list_id NUMBER;
431 l_oe_msg_count NUMBER;
432 l_oe_msg_data VARCHAR2(2000);
433 l_credit_invoice_line_id NUMBER;
434 l_rma_unit_price NUMBER;
435 l_rma_price_diff NUMBER;
436 l_modifer_header_id NUMBER;
437 l_modifer_line_id NUMBER;
438 l_tm_order_source_id NUMBER;
439
440
441 CURSOR csr_return_reason(cv_reason_code_id IN NUMBER) IS
442 SELECT reason_code
443 FROM ozf_reason_codes_vl
444 WHERE reason_code_id = cv_reason_code_id;
445
446 CURSOR csr_rma_trx_type(cv_order_type_id IN NUMBER) IS
447 SELECT default_inbound_line_type_id
448 FROM oe_transaction_types_vl
449 WHERE transaction_type_id = cv_order_type_id;
450
451 CURSOR csr_inv_ord_line_attr(cv_invoice_line_id IN NUMBER) IS
452 SELECT rctl.customer_trx_line_id
453 FROM ra_customer_trx_lines rctl
454 , oe_order_lines ol
455 WHERE rctl.customer_trx_line_id = cv_invoice_line_id
456 AND TO_NUMBER(rctl.interface_line_attribute6) = ol.line_id;
457
458 CURSOR csr_contact_role(cv_party_id IN NUMBER) IS
459 SELECT cust_account_role_id
460 FROM hz_cust_account_roles
461 WHERE party_id = cv_party_id;
462
463 CURSOR csr_qp_list_price( cv_item_id IN NUMBER
464 , cv_price_list_id IN NUMBER
465 ) IS
466 SELECT ql.list_price
467 FROM qp_list_lines ql
468 , qp_pricing_attributes atr
469 WHERE ql.list_line_id = atr.list_line_id
470 AND atr.product_attribute_context = 'ITEM'
471 AND atr.product_attribute = 'PRICING_ATTRIBUTE1'
472 AND atr.product_attr_value = cv_item_id
473 AND atr.list_header_id = cv_price_list_id
474 AND atr.excluder_flag = 'N';
475
476 CURSOR csr_sales_credit_type(cv_salesrep_id IN NUMBER) IS
477 SELECT sales_credit_type_id
478 FROM ra_salesreps
479 WHERE salesrep_id = cv_salesrep_id;
480
481 CURSOR csr_rma_unit_price(cv_line_id IN NUMBER) IS
482 SELECT unit_selling_price
483 FROM oe_order_lines_all
484 WHERE line_id = cv_line_id;
485
486 CURSOR csr_modifier(cv_list_line_id IN NUMBER) IS
487 SELECT list_header_id
488 FROM qp_list_lines
489 WHERE list_line_id = cv_list_line_id;
490
491 CURSOR csr_order_source(cv_order_source_id IN NUMBER) IS
492 SELECT order_source_id
493 FROM oe_order_sources
494 WHERE order_source_id = cv_order_source_id;
495
496
497 BEGIN
498 -------------------- initialize -----------------------
499 IF OZF_DEBUG_HIGH_ON THEN
500 OZF_Utility_PVT.debug_message(l_full_name||': start');
501 END IF;
502
503 x_return_status := FND_API.g_ret_sts_success;
504
505 l_price_list_id := FND_PROFILE.value('OZF_CLAIM_PRICE_LIST_ID');
506 l_modifer_line_id := FND_PROFILE.value('OZF_CLAIM_RMA_MODIFIER_ID');
507
508 OPEN csr_modifier(l_modifer_line_id);
509 FETCH csr_modifier INTO l_modifer_header_id;
510 CLOSE csr_modifier;
511
512 ------------------------ start -------------------------
513 OPEN csr_return_reason(p_x_claim_rec.reason_code_id);
514 FETCH csr_return_reason INTO l_return_reson_code;
515 CLOSE csr_return_reason;
516
517 OPEN csr_rma_trx_type(p_x_claim_rec.order_type_id);
518 FETCH csr_rma_trx_type INTO l_line_type_id;
519 CLOSE csr_rma_trx_type;
520
521 /*------------------------------------------------------*
522 | 1. Setting up header record
523 *------------------------------------------------------*/
524 l_header_rec := OE_ORDER_PUB.g_miss_header_rec;
525
526 -- Bug4249629
527 l_header_rec.ordered_date :=
528 NVL(p_x_claim_rec.effective_date,p_x_claim_rec.settled_date);
529 OPEN csr_order_source(24);
530 FETCH csr_order_source INTO l_tm_order_source_id;
531 CLOSE csr_order_source;
532 IF l_tm_order_source_id IS NOT NULL THEN
533 l_header_rec.order_source_id := l_tm_order_source_id;
534 END IF;
535
536 l_header_rec.orig_sys_document_ref := 'OZF_CLAIMS_ALL:'||p_x_claim_rec.claim_number;
537
538 l_header_rec.order_type_id := p_x_claim_rec.order_type_id;
539
540 l_header_rec.sold_to_org_id := p_x_claim_rec.cust_account_id;
541 IF p_x_claim_rec.pay_related_account_flag = 'T' THEN
542 l_header_rec.invoice_to_org_id := p_x_claim_rec.related_site_use_id;
543 l_header_rec.ship_to_org_id := p_x_claim_rec.cust_shipto_acct_site_id;
544 ELSE
545 l_header_rec.invoice_to_org_id := p_x_claim_rec.cust_billto_acct_site_id;
546 l_header_rec.ship_to_org_id := p_x_claim_rec.cust_shipto_acct_site_id;
547 END IF;
548 IF p_x_claim_rec.sales_rep_id IS NOT NULL THEN
549 l_header_rec.salesrep_id := p_x_claim_rec.sales_rep_id;
550 END IF;
551 IF p_x_claim_rec.contact_id IS NOT NULL THEN
552 OPEN csr_contact_role(p_x_claim_rec.contact_id);
553 FETCH csr_contact_role INTO l_header_rec.sold_to_contact_id;
554 CLOSE csr_contact_role;
555 END IF;
556 --l_header_rec.return_reason_code := l_return_reson_code;
557 IF l_price_list_id IS NOT NULL THEN
558 l_header_rec.price_list_id := l_price_list_id;
559 END IF;
560 --l_header_rec.order_date := sysdate;
561 --l_header_rec.pricing_date := sysdate;
562 l_header_rec.transactional_curr_code := p_x_claim_rec.currency_code;
563 l_header_rec.operation := OE_GLOBALS.g_opr_create;
564
565 IF OZF_DEBUG_HIGH_ON THEN
566 OZF_Utility_PVT.debug_message(l_full_name||': RMA header - order_type_id : '||l_header_rec.order_type_id);
567 OZF_Utility_PVT.debug_message(l_full_name||': RMA header - sold_to_org_id : '||l_header_rec.sold_to_org_id);
568 OZF_Utility_PVT.debug_message(l_full_name||': RMA header - invoice_to_org_id : '||l_header_rec.invoice_to_org_id);
569 OZF_Utility_PVT.debug_message(l_full_name||': RMA header - ship_to_org_id : '||l_header_rec.ship_to_org_id);
570 END IF;
571
572 /*------------------------------------------------------*
573 | 2. Setting up line records
574 *------------------------------------------------------*/
575 i := p_claim_line_tbl.FIRST;
576 IF i IS NOT NULL THEN
577 LOOP
578 IF p_claim_line_tbl(i).item_id IS NOT NULL THEN
579 l_line_tbl(i) := OE_ORDER_PUB.g_miss_line_rec;
580 IF p_claim_line_tbl(i).source_object_class IN ('INVOICE', 'ORDER') AND
581 p_claim_line_tbl(i).source_object_id IS NOT NULL AND
582 p_claim_line_tbl(i).source_object_line_id IS NOT NULL THEN
583 IF p_claim_line_tbl(i).source_object_class = 'INVOICE' THEN
584 --[BEGIN OF BUG 3873986 Fixing]
585 IF ( p_x_claim_rec.claim_class = 'CLAIM' )
586 OR
587 ( p_x_claim_rec.claim_class = 'DEDUCTION' AND
588 p_x_claim_rec.source_object_id = p_claim_line_tbl(i).source_object_id
589 ) THEN
590 --[END OF BUG 3873986 Fixing]
591 OPEN csr_inv_ord_line_attr(p_claim_line_tbl(i).source_object_line_id);
592 FETCH csr_inv_ord_line_attr INTO l_credit_invoice_line_id;
593 CLOSE csr_inv_ord_line_attr;
594 IF l_credit_invoice_line_id IS NOT NULL THEN
595 l_line_tbl(i).return_context := 'INVOICE';
596 l_line_tbl(i).return_attribute1 := TO_CHAR(p_claim_line_tbl(i).source_object_id);
597 l_line_tbl(i).return_attribute2 := TO_CHAR(p_claim_line_tbl(i).source_object_line_id);
598 END IF;
599 END IF;
600 ELSIF p_claim_line_tbl(i).source_object_class = 'ORDER' THEN
601 l_line_tbl(i).return_context := 'ORDER';
602 l_line_tbl(i).return_attribute1 := TO_CHAR(p_claim_line_tbl(i).source_object_id);
603 l_line_tbl(i).return_attribute2 := TO_CHAR(p_claim_line_tbl(i).source_object_line_id);
604 END IF;
605 END IF;
606 --[BEGIN OF BUG 3735800 Fixing]
607 l_line_tbl(i).item_identifier_type := 'INT';
608 --[END OF BUG 3735800 Fixing]
609 l_line_tbl(i).inventory_item_id := p_claim_line_tbl(i).item_id;
610 l_line_tbl(i).ordered_quantity := p_claim_line_tbl(i).quantity * -1;
611 l_line_tbl(i).order_quantity_uom := p_claim_line_tbl(i).quantity_uom;
612 l_line_tbl(i).return_reason_code := l_return_reson_code;
613 --[BEGIN OF BUG 3831562 Fixing]
614 --l_line_tbl(i).line_type_id := l_line_type_id;
615 --[END OF BUG 3831562 Fixing]
616 --l_line_tbl(i).price_list_id := l_price_list_id;
617 --l_line_tbl(i).unit_list_price := p_claim_line_tbl(i).rate;
618 l_line_tbl(i).unit_selling_price := p_claim_line_tbl(i).rate;
619 -- Bug3965003: Tax Code should be passed to OM APIs if the RMA does not
620 -- reference an Order/Invoice.
621 IF ( p_claim_line_tbl(i).source_object_class IS NULL OR
622 p_claim_line_tbl(i).source_object_class NOT IN ('INVOICE', 'DM', 'CB', 'ORDER')) AND
623 p_claim_line_tbl(i).source_object_id IS NULL AND
624 p_claim_line_tbl(i).tax_code IS NOT NULL THEN
625 l_line_tbl(i).tax_code := p_claim_line_tbl(i).tax_code;
626 END IF;
627
628 --l_line_tbl(i).calculate_price_flag := NVL(p_claim_line_tbl(i).payment_status, 'Y');
629 --IF l_line_tbl(i).calculate_price_flag = 'N' THEN
630 OPEN csr_qp_list_price(p_claim_line_tbl(i).item_id, l_price_list_id);
631 FETCH csr_qp_list_price INTO l_line_tbl(i).unit_list_price;
632 CLOSE csr_qp_list_price;
633 --END IF;
634 l_line_tbl(i).operation := OE_GLOBALS.g_opr_create;
635
636 IF OZF_DEBUG_HIGH_ON THEN
637 OZF_Utility_PVT.debug_message(l_full_name||': RMA line'||i||': '||l_line_tbl(i).inventory_item_id||'/'||l_line_tbl(i).ordered_quantity||'/'||l_line_tbl(i).order_quantity_uom||'/'||l_line_tbl(i).unit_list_price);
638 OZF_Utility_PVT.debug_message(l_full_name||': RMA line'||i||' reference: '||l_line_tbl(i).return_context||'/'||l_line_tbl(i).return_attribute1||'/'||l_line_tbl(i).return_attribute2);
639 OZF_Utility_PVT.debug_message(l_full_name||': RMA line'||i||' return_reason_code : '||l_line_tbl(i).return_reason_code);
640 OZF_Utility_PVT.debug_message(l_full_name||': RMA line'||i||' line_type_id : '||l_line_tbl(i).line_type_id);
641 END IF;
642 END IF;
643 EXIT WHEN i = p_claim_line_tbl.LAST;
644 i := p_claim_line_tbl.NEXT(i);
645 END LOOP;
646 END IF;
647
648 /*------------------------------------------------------*
649 | 3. Create an order in Order Management
650 *------------------------------------------------------*/
651 -- [BEGIN OF BUG 3868264 FIXING]
652 -- Change OM Order Creation call to OE_ORDER_GRP api, as per apps standards.
653 OE_ORDER_GRP.Process_Order(
654 p_api_version_number => l_api_version
655 ,p_init_msg_list => FND_API.g_false
656 ,p_return_values => FND_API.g_true
657 ,p_commit => FND_API.g_false
658 ,p_validation_level => FND_API.g_valid_level_full
659
660 ,x_return_status => l_return_status
661 ,x_msg_count => l_oe_msg_count
662 ,x_msg_data => l_oe_msg_data
663 -- [END OF BUG 3868264 FIXING]
664 ,p_header_rec => l_header_rec
665 ,p_header_val_rec => l_header_val_rec
666 ,p_Header_Adj_tbl => l_Header_Adj_tbl
667 ,p_Header_Adj_val_tbl => l_Header_Adj_val_tbl
668 ,p_Header_price_Att_tbl => l_Header_price_Att_tbl
669 ,p_Header_Adj_Att_tbl => l_Header_Adj_Att_tbl
670 ,p_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl
671 ,p_Header_Scredit_tbl => l_Header_Scredit_tbl
672 ,p_Header_Scredit_val_tbl => l_Header_Scredit_val_tbl
673 ,p_line_tbl => l_line_tbl
674 ,p_line_val_tbl => l_line_val_tbl
675 ,p_Line_Adj_tbl => l_Line_Adj_tbl
676 ,p_Line_Adj_val_tbl => l_Line_Adj_val_tbl
677 ,p_Line_price_Att_tbl => l_Line_price_Att_tbl
678 ,p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
679 ,p_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl
680 ,p_Line_Scredit_tbl => l_Line_Scredit_tbl
681 ,p_Line_Scredit_val_tbl => l_Line_Scredit_val_tbl
682 ,p_Lot_Serial_tbl => l_Lot_Serial_tbl
683 ,p_Lot_Serial_val_tbl => l_Lot_Serial_val_tbl
684 ,p_action_request_tbl => l_action_request_tbl
685
686 ,x_header_rec => l_x_header_rec
687 ,x_header_val_rec => l_x_header_val_rec
688 ,x_Header_Adj_tbl => l_x_Header_Adj_tbl
689 ,x_Header_Adj_val_tbl => l_x_Header_Adj_val_tbl
690 ,x_Header_price_Att_tbl => l_x_Header_price_Att_tbl
691 ,x_Header_Adj_Att_tbl => l_x_Header_Adj_Att_tbl
692 ,x_Header_Adj_Assoc_tbl => l_x_Header_Adj_Assoc_tbl
693 ,x_Header_Scredit_tbl => l_x_Header_Scredit_tbl
694 ,x_Header_Scredit_val_tbl => l_x_Header_Scredit_val_tbl
695
696 ,x_line_tbl => l_x_line_tbl
697 ,x_line_val_tbl => l_x_line_val_tbl
698 ,x_Line_Adj_tbl => l_x_Line_Adj_tbl
699 ,x_Line_Adj_val_tbl => l_x_Line_Adj_val_tbl
700 ,x_Line_price_Att_tbl => l_x_Line_price_Att_tbl
701 ,x_Line_Adj_Att_tbl => l_x_Line_Adj_Att_tbl
702 ,x_Line_Adj_Assoc_tbl => l_x_Line_Adj_Assoc_tbl
703 ,x_Line_Scredit_tbl => l_x_Line_Scredit_tbl
704 ,x_Line_Scredit_val_tbl => l_x_Line_Scredit_val_tbl
705 ,x_Lot_Serial_tbl => l_x_Lot_Serial_tbl
706 ,x_Lot_Serial_val_tbl => l_x_Lot_Serial_val_tbl
707
708 ,x_action_request_tbl => l_x_action_request_tbl
709 );
710 IF l_return_status = FND_API.g_ret_sts_error THEN
711 FOR i in 1 .. l_oe_msg_count LOOP
712 l_oe_msg_data := OE_MSG_PUB.get( p_msg_index => i
713 , p_encoded => 'F'
714 );
715 FND_MESSAGE.SET_NAME('OZF', 'OZF_API_DEBUG_MESSAGE');
716 FND_MESSAGE.SET_TOKEN('TEXT', l_oe_msg_data);
717 FND_MSG_PUB.ADD;
718 END LOOP;
719 RAISE FND_API.g_exc_error;
720 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
721 FOR i in 1 .. l_oe_msg_count LOOP
722 l_oe_msg_data := OE_MSG_PUB.get( p_msg_index => i
723 , p_encoded => 'F'
724 );
725 FND_MESSAGE.SET_NAME('OZF', 'OZF_API_DEBUG_MESSAGE');
726 FND_MESSAGE.SET_TOKEN('TEXT', l_oe_msg_data);
727 FND_MSG_PUB.ADD;
728 END LOOP;
729 RAISE FND_API.g_exc_unexpected_error;
730 END IF;
731
732 /*------------------------------------------------------*
733 | 4. Query selling price of RMA created by TM
734 *------------------------------------------------------*/
735 i := l_x_line_tbl.FIRST;
736 IF i IS NOT NULL THEN
737 l_header_rec := OE_ORDER_PUB.g_miss_header_rec;
738 l_header_rec.header_id := l_x_header_rec.header_id;
739 -- [BEGIN OF BUG 3868264 FIXING]
740 l_header_rec.change_reason := 'SYSTEM';
741 -- [END OF BUG 3868264 FIXING]
742 l_header_rec.operation := OE_GLOBALS.g_opr_update;
743
744 LOOP
745 IF p_claim_line_tbl(i).item_id = l_x_line_tbl(i).inventory_item_id THEN
746 l_line_tbl(i).line_id := l_x_line_tbl(i).line_id;
747 -- [BEGIN OF BUG 3868264 FIXING]
748 l_line_tbl(i).change_reason := 'SYSTEM';
749 -- [END OF BUG 3868264 FIXING]
750 l_line_tbl(i).operation := OE_GLOBALS.g_opr_update;
751
752 OPEN csr_rma_unit_price(l_x_line_tbl(i).line_id);
753 FETCH csr_rma_unit_price INTO l_rma_unit_price;
754 CLOSE csr_rma_unit_price;
755
756 IF p_claim_line_tbl(i).rate IS NOT NULL AND
757 l_rma_unit_price <> p_claim_line_tbl(i).rate THEN
758 IF l_modifer_line_id IS NULL THEN
759 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
760 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_MODIFIER_ERR');
761 FND_MSG_PUB.add;
762 END IF;
763 RAISE FND_API.g_exc_error;
764 ELSE
765 l_rma_price_diff := l_rma_unit_price - p_claim_line_tbl(i).rate;
766
767 l_line_adj_tbl(l_adj_idx) :=OE_ORDER_PUB.g_miss_line_adj_rec;
768
769 l_line_adj_tbl(l_adj_idx).line_index := i;
770 l_line_adj_tbl(l_adj_idx).list_header_id := l_modifer_header_id;
771 l_line_adj_tbl(l_adj_idx).list_line_id := l_modifer_line_id;
772 l_line_adj_tbl(l_adj_idx).modifier_level_code := 'LINE';
773 l_line_adj_tbl(l_adj_idx).operand := l_rma_price_diff;
774 l_line_adj_tbl(l_adj_idx).adjusted_amount_per_pqty := l_rma_price_diff * -1;
775 l_line_adj_tbl(l_adj_idx).arithmetic_operator := 'AMT';
776 l_line_adj_tbl(l_adj_idx).updated_flag := 'Y';
777 l_line_adj_tbl(l_adj_idx).applied_flag := 'Y';
778 -- [BEGIN OF BUG 3868264 FIXING]
779 l_line_adj_tbl(l_adj_idx).change_reason_code := 'SYSTEM';
780 -- [END OF BUG 3868264 FIXING]
781 l_line_adj_tbl(l_adj_idx).operation := OE_GLOBALS.g_opr_create;
782
783 l_adj_idx := l_adj_idx + 1;
784 END IF;
785 END IF;
786 END IF;
787 EXIT WHEN i = l_x_line_tbl.LAST;
788 i := l_x_line_tbl.NEXT(i);
789 END LOOP;
790 END IF;
791
792 /*------------------------------------------------------*
793 | 5. Adjust price
794 *------------------------------------------------------*/
795 i := l_line_adj_tbl.FIRST;
796 IF i IS NOT NULL THEN
797 OE_ORDER_GRP.Process_Order(
798 p_api_version_number => l_api_version
799 ,p_init_msg_list => FND_API.g_false
800 ,p_return_values => FND_API.g_true
801 ,p_commit => FND_API.g_false
802 ,x_return_status => l_return_status
803 ,x_msg_count => l_oe_msg_count
804 ,x_msg_data => l_oe_msg_data
805
806 ,p_header_rec => l_header_rec
807 ,p_header_val_rec => l_header_val_rec
808 ,p_Header_Adj_tbl => l_Header_Adj_tbl
809 ,p_Header_Adj_val_tbl => l_Header_Adj_val_tbl
810 ,p_Header_price_Att_tbl => l_Header_price_Att_tbl
811 ,p_Header_Adj_Att_tbl => l_Header_Adj_Att_tbl
812 ,p_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl
813 ,p_Header_Scredit_tbl => l_Header_Scredit_tbl
814 ,p_Header_Scredit_val_tbl => l_Header_Scredit_val_tbl
815 ,p_line_tbl => l_line_tbl
816 ,p_line_val_tbl => l_line_val_tbl
817 ,p_Line_Adj_tbl => l_Line_Adj_tbl
818 ,p_Line_Adj_val_tbl => l_Line_Adj_val_tbl
819 ,p_Line_price_Att_tbl => l_Line_price_Att_tbl
820 ,p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
821 ,p_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl
822 ,p_Line_Scredit_tbl => l_Line_Scredit_tbl
823 ,p_Line_Scredit_val_tbl => l_Line_Scredit_val_tbl
824 ,p_Lot_Serial_tbl => l_Lot_Serial_tbl
825 ,p_Lot_Serial_val_tbl => l_Lot_Serial_val_tbl
826 ,p_action_request_tbl => l_action_request_tbl
827
828 ,x_header_rec => l_x_header_rec
829 ,x_header_val_rec => l_x_header_val_rec
830 ,x_Header_Adj_tbl => l_x_Header_Adj_tbl
831 ,x_Header_Adj_val_tbl => l_x_Header_Adj_val_tbl
832 ,x_Header_price_Att_tbl => l_x_Header_price_Att_tbl
833 ,x_Header_Adj_Att_tbl => l_x_Header_Adj_Att_tbl
834 ,x_Header_Adj_Assoc_tbl => l_x_Header_Adj_Assoc_tbl
835 ,x_Header_Scredit_tbl => l_x_Header_Scredit_tbl
836 ,x_Header_Scredit_val_tbl => l_x_Header_Scredit_val_tbl
837
838 ,x_line_tbl => l_x_line_tbl
839 ,x_line_val_tbl => l_x_line_val_tbl
840 ,x_Line_Adj_tbl => l_x_Line_Adj_tbl
841 ,x_Line_Adj_val_tbl => l_x_Line_Adj_val_tbl
842 ,x_Line_price_Att_tbl => l_x_Line_price_Att_tbl
843 ,x_Line_Adj_Att_tbl => l_x_Line_Adj_Att_tbl
844 ,x_Line_Adj_Assoc_tbl => l_x_Line_Adj_Assoc_tbl
845 ,x_Line_Scredit_tbl => l_x_Line_Scredit_tbl
846 ,x_Line_Scredit_val_tbl => l_x_Line_Scredit_val_tbl
847 ,x_Lot_Serial_tbl => l_x_Lot_Serial_tbl
848 ,x_Lot_Serial_val_tbl => l_x_Lot_Serial_val_tbl
849
850 ,x_action_request_tbl => l_x_action_request_tbl
851 );
852 IF l_return_status = FND_API.g_ret_sts_error THEN
853 FOR i in 1 .. l_oe_msg_count LOOP
854 l_oe_msg_data := OE_MSG_PUB.get( p_msg_index => i
855 , p_encoded => 'F'
856 );
857 FND_MESSAGE.SET_NAME('AMS', 'AMS_API_DEBUG_MESSAGE');
858 FND_MESSAGE.SET_TOKEN('TEXT', l_oe_msg_data);
859 FND_MSG_PUB.ADD;
860 END LOOP;
861 RAISE FND_API.g_exc_error;
862 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
863 FOR i in 1 .. l_oe_msg_count LOOP
864 l_oe_msg_data := OE_MSG_PUB.get( p_msg_index => i
865 , p_encoded => 'F'
866 );
867 FND_MESSAGE.SET_NAME('AMS', 'AMS_API_DEBUG_MESSAGE');
868 FND_MESSAGE.SET_TOKEN('TEXT', l_oe_msg_data);
869 FND_MSG_PUB.ADD;
870 END LOOP;
871 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
872 FND_MESSAGE.set_name('AMS', 'AMS_SETL_OM_CRE_ORD_U_ERR');
873 FND_MSG_PUB.add;
874 END IF;
875 RAISE FND_API.g_exc_unexpected_error;
876 END IF;
877 END IF;
878
879
880 /*------------------------------------------------------*
881 | 6. Update RMA order information in Claim after order is
882 | been successfully created.
883 *------------------------------------------------------*/
884
885 UPDATE ozf_claims_all
886 SET payment_reference_id = l_x_header_rec.header_id
887 , payment_reference_number = l_x_header_rec.order_number
888 , payment_reference_date = l_x_header_rec.ordered_date
889 WHERE claim_id = p_x_claim_rec.claim_id;
890
891 i := l_x_line_tbl.FIRST;
892 IF i IS NOT NULL THEN
893 LOOP
894 IF p_claim_line_tbl(i).item_id = l_x_line_tbl(i).inventory_item_id THEN
895 UPDATE ozf_claim_lines_all
896 SET payment_reference_id = l_x_line_tbl(i).line_id
897 , payment_reference_number = l_x_line_tbl(i).line_number
898 WHERE claim_line_id = p_claim_line_tbl(i).claim_line_id;
899 END IF;
900 EXIT WHEN i = l_x_line_tbl.LAST;
901 i := l_x_line_tbl.NEXT(i);
902 END LOOP;
903 END IF;
904
905 IF OZF_DEBUG_HIGH_ON THEN
906 OZF_Utility_PVT.debug_message(l_full_name||': end');
907 END IF;
908 EXCEPTION
909 WHEN FND_API.g_exc_error THEN
910 x_return_status := FND_API.g_ret_sts_error;
911
912 WHEN FND_API.g_exc_unexpected_error THEN
913 x_return_status := FND_API.g_ret_sts_unexp_error ;
914
915 WHEN OTHERS THEN
916 x_return_status := FND_API.g_ret_sts_unexp_error ;
917 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
918 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
919 END IF;
920
921 END Complete_RMA_Order;
922
923
924 /*=======================================================================*
925 | PROCEDURE
926 | Book_RMA_Order
927 |
928 | NOTES
929 |
930 | HISTORY
931 | 24-OCT-2002 mchang Create.
932 *=======================================================================*/
933 PROCEDURE Book_RMA_Order(
934 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
935 ,p_claim_line_tbl IN OZF_CLAIM_LINE_PVT.claim_line_tbl_type
936
937 ,x_return_status OUT NOCOPY VARCHAR2
938 ,x_msg_data OUT NOCOPY VARCHAR2
939 ,x_msg_count OUT NOCOPY NUMBER
940 )
941 IS
942 l_api_version CONSTANT NUMBER := 1.0;
943 l_api_name CONSTANT VARCHAR2(30) := 'Book_RMA_Order';
944 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
945 l_return_status VARCHAR2(1);
946
947 l_header_rec OE_ORDER_PUB.header_rec_type;
948 l_header_val_rec OE_ORDER_PUB.header_val_rec_type;
949 l_header_adj_tbl OE_ORDER_PUB.header_adj_tbl_type;
950 l_header_adj_val_tbl OE_ORDER_PUB.header_adj_val_tbl_type;
951 l_header_price_att_tbl OE_ORDER_PUB.header_price_att_tbl_type;
952 l_header_adj_att_tbl OE_ORDER_PUB.header_adj_att_tbl_type;
953 l_header_adj_assoc_tbl OE_ORDER_PUB.header_adj_assoc_tbl_type;
954 l_header_scredit_tbl OE_ORDER_PUB.header_scredit_tbl_type;
955 l_header_scredit_val_tbl OE_ORDER_PUB.header_scredit_val_tbl_type;
956
957 l_line_tbl OE_ORDER_PUB.line_tbl_type;
958 l_line_val_tbl OE_ORDER_PUB.line_val_tbl_type;
959 l_line_adj_tbl OE_ORDER_PUB.line_adj_tbl_type;
960 l_line_adj_val_tbl OE_ORDER_PUB.line_adj_val_tbl_type;
961 l_line_price_att_tbl OE_ORDER_PUB.line_price_att_tbl_type;
962 l_line_adj_att_tbl OE_ORDER_PUB.line_adj_att_tbl_type;
963 l_line_adj_assoc_tbl OE_ORDER_PUB.line_adj_assoc_tbl_type;
964 l_line_scredit_tbl OE_ORDER_PUB.line_scredit_tbl_type;
965 l_line_scredit_val_tbl OE_ORDER_PUB.line_scredit_val_tbl_type;
966 l_lot_serial_tbl OE_ORDER_PUB.lot_serial_tbl_type;
967 l_lot_serial_val_tbl OE_ORDER_PUB.lot_serial_val_tbl_type;
968 l_action_request_tbl OE_ORDER_PUB.request_tbl_type;
969
970 l_x_header_rec OE_ORDER_PUB.header_rec_type;
971 l_x_header_val_rec OE_ORDER_PUB.header_val_rec_type;
972 l_x_header_adj_tbl OE_ORDER_PUB.header_adj_tbl_type;
973 l_x_header_adj_val_tbl OE_ORDER_PUB.header_adj_val_tbl_type;
974 l_x_header_price_att_tbl OE_ORDER_PUB.header_price_att_tbl_type;
975 l_x_header_adj_att_tbl OE_ORDER_PUB.header_adj_att_tbl_type;
976 l_x_header_adj_assoc_tbl OE_ORDER_PUB.header_adj_assoc_tbl_type;
977 l_x_header_scredit_tbl OE_ORDER_PUB.header_scredit_tbl_type;
978 l_x_header_scredit_val_tbl OE_ORDER_PUB.header_scredit_val_tbl_type;
979
980 l_x_line_tbl OE_ORDER_PUB.line_tbl_type;
981 l_x_line_val_tbl OE_ORDER_PUB.line_val_tbl_type;
982 l_x_line_adj_tbl OE_ORDER_PUB.line_adj_tbl_type;
983 l_x_line_adj_val_tbl OE_ORDER_PUB.line_adj_val_tbl_type;
984 l_x_line_price_att_tbl OE_ORDER_PUB.line_price_att_tbl_type;
985 l_x_line_adj_att_tbl OE_ORDER_PUB.line_adj_att_tbl_type;
986 l_x_line_adj_assoc_tbl OE_ORDER_PUB.line_adj_assoc_tbl_type;
987 l_x_line_scredit_tbl OE_ORDER_PUB.line_scredit_tbl_type;
988 l_x_line_scredit_val_tbl OE_ORDER_PUB.line_scredit_val_tbl_type;
989 l_x_lot_serial_tbl OE_ORDER_PUB.lot_serial_tbl_type;
990 l_x_lot_serial_val_tbl OE_ORDER_PUB.lot_serial_val_tbl_type;
991 l_x_action_request_tbl OE_ORDER_PUB.request_tbl_type;
992
993 i NUMBER;
994 l_oe_msg_count NUMBER;
995 l_oe_msg_data VARCHAR2(2000);
996
997 BEGIN
998 -------------------- initialize -----------------------
999 IF OZF_DEBUG_HIGH_ON THEN
1000 OZF_Utility_PVT.debug_message(l_full_name||': start');
1001 END IF;
1002
1003 x_return_status := FND_API.g_ret_sts_success;
1004
1005 OE_STANDARD_WF.save_messages_off;
1006
1007 ------------------------ start -------------------------
1008 /*------------------------------------------------------*
1009 | Book RMA order
1010 *------------------------------------------------------*/
1011 l_action_request_tbl(1) := OE_ORDER_PUB.g_miss_request_rec;
1012 l_action_request_tbl(1).request_type := OE_GLOBALS.g_book_order;
1013 l_action_request_tbl(1).entity_code := OE_GLOBALS.g_entity_header;
1014 --l_action_request_tbl(1).entity_id := l_x_header_rec.header_id;
1015 l_action_request_tbl(1).entity_id := p_claim_rec.payment_reference_id;
1016
1017
1018 OE_ORDER_GRP.Process_Order(
1019 p_api_version_number => l_api_version
1020 ,p_init_msg_list => FND_API.g_false
1021 ,p_return_values => FND_API.g_true
1022 ,p_commit => FND_API.g_false
1023 ,x_return_status => l_return_status
1024 ,x_msg_count => l_oe_msg_count
1025 ,x_msg_data => l_oe_msg_data
1026
1027 ,p_action_request_tbl => l_action_request_tbl
1028
1029 ,x_header_rec => l_x_header_rec
1030 ,x_header_val_rec => l_x_header_val_rec
1031 ,x_Header_Adj_tbl => l_x_Header_Adj_tbl
1032 ,x_Header_Adj_val_tbl => l_x_Header_Adj_val_tbl
1033 ,x_Header_price_Att_tbl => l_x_Header_price_Att_tbl
1034 ,x_Header_Adj_Att_tbl => l_x_Header_Adj_Att_tbl
1035 ,x_Header_Adj_Assoc_tbl => l_x_Header_Adj_Assoc_tbl
1036 ,x_Header_Scredit_tbl => l_x_Header_Scredit_tbl
1037 ,x_Header_Scredit_val_tbl => l_x_Header_Scredit_val_tbl
1038
1039 ,x_line_tbl => l_x_line_tbl
1040 ,x_line_val_tbl => l_x_line_val_tbl
1041 ,x_Line_Adj_tbl => l_x_Line_Adj_tbl
1042 ,x_Line_Adj_val_tbl => l_x_Line_Adj_val_tbl
1043 ,x_Line_price_Att_tbl => l_x_Line_price_Att_tbl
1044 ,x_Line_Adj_Att_tbl => l_x_Line_Adj_Att_tbl
1045 ,x_Line_Adj_Assoc_tbl => l_x_Line_Adj_Assoc_tbl
1046 ,x_Line_Scredit_tbl => l_x_Line_Scredit_tbl
1047 ,x_Line_Scredit_val_tbl => l_x_Line_Scredit_val_tbl
1048 ,x_Lot_Serial_tbl => l_x_Lot_Serial_tbl
1049 ,x_Lot_Serial_val_tbl => l_x_Lot_Serial_val_tbl
1050
1051 ,x_action_request_tbl => l_x_action_request_tbl
1052 );
1053 IF l_x_action_request_tbl(1).request_type <> OE_GLOBALS.g_book_order THEN
1054 FOR i in 1 .. l_oe_msg_count LOOP
1055 l_oe_msg_data := OE_MSG_PUB.get( p_msg_index => i
1056 , p_encoded => 'F'
1057 );
1058 FND_MESSAGE.SET_NAME('OZF', 'OZF_API_DEBUG_MESSAGE');
1059 FND_MESSAGE.SET_TOKEN('TEXT', l_oe_msg_data);
1060 FND_MSG_PUB.ADD;
1061 END LOOP;
1062
1063 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1064 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_BOK_ORD_E_ERR');
1065 FND_MSG_PUB.add;
1066 END IF;
1067 RAISE FND_API.g_exc_error;
1068
1069 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1070 FOR i in 1 .. l_oe_msg_count LOOP
1071 l_oe_msg_data := OE_MSG_PUB.get( p_msg_index => i
1072 , p_encoded => 'F'
1073 );
1074 FND_MESSAGE.SET_NAME('OZF', 'OZF_API_DEBUG_MESSAGE');
1075 FND_MESSAGE.SET_TOKEN('TEXT', l_oe_msg_data);
1076 FND_MSG_PUB.ADD;
1077 END LOOP;
1078
1079 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1080 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_BOK_ORD_E_ERR');
1081 FND_MSG_PUB.add;
1082 END IF;
1083 RAISE FND_API.g_exc_error;
1084 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1085 FOR i in 1 .. l_oe_msg_count LOOP
1086 l_oe_msg_data := OE_MSG_PUB.get( p_msg_index => i
1087 , p_encoded => 'F'
1088 );
1089 FND_MESSAGE.SET_NAME('OZF', 'OZF_API_DEBUG_MESSAGE');
1090 FND_MESSAGE.SET_TOKEN('TEXT', l_oe_msg_data);
1091 FND_MSG_PUB.ADD;
1092 END LOOP;
1093
1094 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1095 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_BOK_ORD_U_ERR');
1096 FND_MSG_PUB.add;
1097 END IF;
1098 RAISE FND_API.g_exc_unexpected_error;
1099 END IF;
1100
1101 IF OZF_DEBUG_HIGH_ON THEN
1102 OZF_Utility_PVT.debug_message(l_full_name||': end');
1103 END IF;
1104 EXCEPTION
1105 WHEN FND_API.g_exc_error THEN
1106 x_return_status := FND_API.g_ret_sts_error;
1107
1108 WHEN FND_API.g_exc_unexpected_error THEN
1109 x_return_status := FND_API.g_ret_sts_unexp_error ;
1110
1111 WHEN OTHERS THEN
1112 x_return_status := FND_API.g_ret_sts_unexp_error ;
1113 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1114 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1115 END IF;
1116
1117 END Book_RMA_Order;
1118
1119
1120 /*=======================================================================*
1121 | PROCEDURE
1122 | Create_OM_Payment
1123 |
1124 | NOTES
1125 |
1126 | HISTORY
1127 | 24-OCT-2002 mchang Create.
1128 *=======================================================================*/
1129 PROCEDURE Create_OM_Payment(
1130 p_api_version IN NUMBER
1131 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
1132 ,p_commit IN VARCHAR2 := FND_API.g_false
1133 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
1134
1135 ,x_return_status OUT NOCOPY VARCHAR2
1136 ,x_msg_data OUT NOCOPY VARCHAR2
1137 ,x_msg_count OUT NOCOPY NUMBER
1138
1139 ,p_claim_id IN NUMBER
1140 )
1141 IS
1142 l_api_version CONSTANT NUMBER := 1.0;
1143 l_api_name CONSTANT VARCHAR2(30) := 'Create_OM_Payment';
1144 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1145 l_return_status VARCHAR2(1);
1146
1147 l_claim_rec OZF_CLAIM_PVT.claim_rec_type;
1148 l_claim_line_tbl OZF_CLAIM_LINE_PVT.claim_line_tbl_type;
1149
1150 BEGIN
1151 -------------------- initialize -----------------------
1152 SAVEPOINT Create_OM_Payment;
1153
1154 IF OZF_DEBUG_HIGH_ON THEN
1155 OZF_Utility_PVT.debug_message(l_full_name||': start');
1156 END IF;
1157
1158 IF FND_API.to_boolean(p_init_msg_list) THEN
1159 FND_MSG_PUB.initialize;
1160 END IF;
1161
1162
1163 IF NOT FND_API.compatible_api_call(
1164 l_api_version,
1165 p_api_version,
1166 l_api_name,
1167 g_pkg_name
1168 ) THEN
1169 RAISE FND_API.g_exc_unexpected_error;
1170 END IF;
1171
1172 x_return_status := FND_API.g_ret_sts_success;
1173
1174 ------------------------ start -------------------------
1175 Query_Claim(
1176 p_claim_id => p_claim_id
1177 ,x_claim_rec => l_claim_rec
1178 ,x_return_status => l_return_status
1179 );
1180 IF l_return_status = FND_API.g_ret_sts_error THEN
1181 RAISE FND_API.g_exc_error;
1182 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1183 RAISE FND_API.g_exc_unexpected_error;
1184 END IF;
1185
1186 Query_Claim_Line(
1187 p_claim_id => p_claim_id
1188 ,x_claim_line_tbl => l_claim_line_tbl
1189 ,x_return_status => l_return_status
1190 );
1191 IF l_return_status = FND_API.g_ret_sts_error THEN
1192 RAISE FND_API.g_exc_error;
1193 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1194 RAISE FND_API.g_exc_unexpected_error;
1195 END IF;
1196
1197 IF l_claim_rec.payment_method = 'RMA' THEN
1198 IF OZF_DEBUG_HIGH_ON THEN
1199 OZF_Utility_PVT.debug_message('Create RMA order for ==> '||l_claim_rec.claim_number);
1200 END IF;
1201 OZF_OM_PAYMENT_PVT.Complete_RMA_Order(
1202 p_x_claim_rec => l_claim_rec
1203 ,p_claim_line_tbl => l_claim_line_tbl
1204 ,x_return_status => l_return_status
1205 ,x_msg_data => x_msg_data
1206 ,x_msg_count => x_msg_count
1207 );
1208 IF l_return_status = FND_API.g_ret_sts_error THEN
1209 RAISE FND_API.g_exc_error;
1210 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1211 RAISE FND_API.g_exc_unexpected_error;
1212 END IF;
1213
1214 Query_Claim(
1215 p_claim_id => p_claim_id
1216 ,x_claim_rec => l_claim_rec
1217 ,x_return_status => l_return_status
1218 );
1219 IF l_return_status = FND_API.g_ret_sts_error THEN
1220 RAISE FND_API.g_exc_error;
1221 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1222 RAISE FND_API.g_exc_unexpected_error;
1223 END IF;
1224
1225 IF OZF_DEBUG_HIGH_ON THEN
1226 OZF_Utility_PVT.debug_message('Book RMA order for ==> '||l_claim_rec.claim_number);
1227 OZF_Utility_PVT.debug_message('RMA order number = '||l_claim_rec.payment_reference_number);
1228 END IF;
1229 Book_RMA_Order(
1230 p_claim_rec => l_claim_rec
1231 ,p_claim_line_tbl => l_claim_line_tbl
1232 ,x_return_status => l_return_status
1233 ,x_msg_data => x_msg_data
1234 ,x_msg_count => x_msg_count
1235 );
1236 IF l_return_status = FND_API.g_ret_sts_error THEN
1237 RAISE FND_API.g_exc_error;
1238 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1239 RAISE FND_API.g_exc_unexpected_error;
1240 END IF;
1241 END IF;
1242
1243 -- reset payment status in ozf_claim_lines_all
1244 BEGIN
1245 UPDATE ozf_claim_lines_all
1246 SET payment_status = 'PENDING'
1247 WHERE claim_id = p_claim_id;
1248 EXCEPTION
1249 WHEN OTHERS THEN
1250 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1251 FND_MESSAGE.SET_NAME('OZF','OZF_API_DEBUG_MESSAGE');
1252 FND_MESSAGE.SET_TOKEN('TEXT',SQLERRM);
1253 FND_MSG_PUB.ADD;
1254 END IF;
1255 END;
1256
1257 ------------------------ finish ------------------------
1258 FND_MSG_PUB.count_and_get(
1259 p_encoded => FND_API.g_false,
1260 p_count => x_msg_count,
1261 p_data => x_msg_data
1262 );
1263
1264 IF OZF_DEBUG_HIGH_ON THEN
1265 OZF_Utility_PVT.debug_message(l_full_name ||': end');
1266 END IF;
1267 EXCEPTION
1268 WHEN FND_API.g_exc_error THEN
1269 ROLLBACK TO Create_OM_Payment;
1270 x_return_status := FND_API.g_ret_sts_error;
1271 FND_MSG_PUB.count_and_get (
1272 p_encoded => FND_API.g_false
1273 ,p_count => x_msg_count
1274 ,p_data => x_msg_data
1275 );
1276
1277 WHEN FND_API.g_exc_unexpected_error THEN
1278 ROLLBACK TO Create_OM_Payment;
1279 x_return_status := FND_API.g_ret_sts_unexp_error ;
1280 FND_MSG_PUB.count_and_get (
1281 p_encoded => FND_API.g_false
1282 ,p_count => x_msg_count
1283 ,p_data => x_msg_data
1284 );
1285
1286 WHEN OTHERS THEN
1287 ROLLBACK TO Create_OM_Payment;
1288 x_return_status := FND_API.g_ret_sts_unexp_error ;
1289 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1290 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1291 END IF;
1292 FND_MSG_PUB.count_and_get(
1293 p_encoded => FND_API.g_false
1294 ,p_count => x_msg_count
1295 ,p_data => x_msg_data
1296 );
1297
1298 END Create_OM_Payment;
1299
1300 END OZF_OM_PAYMENT_PVT;