[Home] [Help]
PACKAGE BODY: APPS.OZF_OM_PAYMENT_PVT
Source
1 PACKAGE BODY OZF_OM_PAYMENT_PVT AS
2 /* $Header: ozfvompb.pls 120.3.12020000.2 2012/12/27 10:52:38 apyadav ship $ */
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 = TO_CHAR(cv_item_id) --ninarasi fix for bug 12939639
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
620 --Bug 14161847, if effective date is provided, fetch the pricing applicable on effective date otherwise on system date
621 IF p_x_claim_rec.effective_date IS NOT NULL THEN
622 l_line_tbl(i).pricing_date := p_x_claim_rec.effective_date;
623 END IF;
624
625 -- Bug3965003: Tax Code should be passed to OM APIs if the RMA does not
626 -- reference an Order/Invoice.
627 IF ( p_claim_line_tbl(i).source_object_class IS NULL OR
628 p_claim_line_tbl(i).source_object_class NOT IN ('INVOICE', 'DM', 'CB', 'ORDER')) AND
629 p_claim_line_tbl(i).source_object_id IS NULL AND
630 p_claim_line_tbl(i).tax_code IS NOT NULL THEN
631 l_line_tbl(i).tax_code := p_claim_line_tbl(i).tax_code;
632 END IF;
633
634 --l_line_tbl(i).calculate_price_flag := NVL(p_claim_line_tbl(i).payment_status, 'Y');
635 --IF l_line_tbl(i).calculate_price_flag = 'N' THEN
636 OPEN csr_qp_list_price(p_claim_line_tbl(i).item_id, l_price_list_id);
637 FETCH csr_qp_list_price INTO l_line_tbl(i).unit_list_price;
638 CLOSE csr_qp_list_price;
639 --END IF;
640 l_line_tbl(i).operation := OE_GLOBALS.g_opr_create;
641
642 IF OZF_DEBUG_HIGH_ON THEN
643 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);
644 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);
645 OZF_Utility_PVT.debug_message(l_full_name||': RMA line'||i||' return_reason_code : '||l_line_tbl(i).return_reason_code);
646 OZF_Utility_PVT.debug_message(l_full_name||': RMA line'||i||' line_type_id : '||l_line_tbl(i).line_type_id);
647 END IF;
648 END IF;
649 EXIT WHEN i = p_claim_line_tbl.LAST;
650 i := p_claim_line_tbl.NEXT(i);
651 END LOOP;
652 END IF;
653
654 /*------------------------------------------------------*
655 | 3. Create an order in Order Management
656 *------------------------------------------------------*/
657 -- [BEGIN OF BUG 3868264 FIXING]
658 -- Change OM Order Creation call to OE_ORDER_GRP api, as per apps standards.
659 OE_ORDER_GRP.Process_Order(
660 p_api_version_number => l_api_version
661 ,p_init_msg_list => FND_API.g_false
662 ,p_return_values => FND_API.g_true
663 ,p_commit => FND_API.g_false
664 ,p_validation_level => FND_API.g_valid_level_full
665
666 ,x_return_status => l_return_status
667 ,x_msg_count => l_oe_msg_count
668 ,x_msg_data => l_oe_msg_data
669 -- [END OF BUG 3868264 FIXING]
670 ,p_header_rec => l_header_rec
671 ,p_header_val_rec => l_header_val_rec
672 ,p_Header_Adj_tbl => l_Header_Adj_tbl
673 ,p_Header_Adj_val_tbl => l_Header_Adj_val_tbl
674 ,p_Header_price_Att_tbl => l_Header_price_Att_tbl
675 ,p_Header_Adj_Att_tbl => l_Header_Adj_Att_tbl
676 ,p_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl
677 ,p_Header_Scredit_tbl => l_Header_Scredit_tbl
678 ,p_Header_Scredit_val_tbl => l_Header_Scredit_val_tbl
679 ,p_line_tbl => l_line_tbl
680 ,p_line_val_tbl => l_line_val_tbl
681 ,p_Line_Adj_tbl => l_Line_Adj_tbl
682 ,p_Line_Adj_val_tbl => l_Line_Adj_val_tbl
683 ,p_Line_price_Att_tbl => l_Line_price_Att_tbl
684 ,p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
685 ,p_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl
686 ,p_Line_Scredit_tbl => l_Line_Scredit_tbl
687 ,p_Line_Scredit_val_tbl => l_Line_Scredit_val_tbl
688 ,p_Lot_Serial_tbl => l_Lot_Serial_tbl
689 ,p_Lot_Serial_val_tbl => l_Lot_Serial_val_tbl
690 ,p_action_request_tbl => l_action_request_tbl
691
692 ,x_header_rec => l_x_header_rec
693 ,x_header_val_rec => l_x_header_val_rec
694 ,x_Header_Adj_tbl => l_x_Header_Adj_tbl
695 ,x_Header_Adj_val_tbl => l_x_Header_Adj_val_tbl
696 ,x_Header_price_Att_tbl => l_x_Header_price_Att_tbl
697 ,x_Header_Adj_Att_tbl => l_x_Header_Adj_Att_tbl
698 ,x_Header_Adj_Assoc_tbl => l_x_Header_Adj_Assoc_tbl
699 ,x_Header_Scredit_tbl => l_x_Header_Scredit_tbl
700 ,x_Header_Scredit_val_tbl => l_x_Header_Scredit_val_tbl
701
702 ,x_line_tbl => l_x_line_tbl
703 ,x_line_val_tbl => l_x_line_val_tbl
704 ,x_Line_Adj_tbl => l_x_Line_Adj_tbl
705 ,x_Line_Adj_val_tbl => l_x_Line_Adj_val_tbl
706 ,x_Line_price_Att_tbl => l_x_Line_price_Att_tbl
707 ,x_Line_Adj_Att_tbl => l_x_Line_Adj_Att_tbl
708 ,x_Line_Adj_Assoc_tbl => l_x_Line_Adj_Assoc_tbl
709 ,x_Line_Scredit_tbl => l_x_Line_Scredit_tbl
710 ,x_Line_Scredit_val_tbl => l_x_Line_Scredit_val_tbl
711 ,x_Lot_Serial_tbl => l_x_Lot_Serial_tbl
712 ,x_Lot_Serial_val_tbl => l_x_Lot_Serial_val_tbl
713
714 ,x_action_request_tbl => l_x_action_request_tbl
715 );
716 IF l_return_status = FND_API.g_ret_sts_error THEN
717 FOR i in 1 .. l_oe_msg_count LOOP
718 l_oe_msg_data := OE_MSG_PUB.get( p_msg_index => i
719 , p_encoded => 'F'
720 );
721 FND_MESSAGE.SET_NAME('OZF', 'OZF_API_DEBUG_MESSAGE');
722 FND_MESSAGE.SET_TOKEN('TEXT', l_oe_msg_data);
723 FND_MSG_PUB.ADD;
724 END LOOP;
725 RAISE FND_API.g_exc_error;
726 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
727 FOR i in 1 .. l_oe_msg_count LOOP
728 l_oe_msg_data := OE_MSG_PUB.get( p_msg_index => i
729 , p_encoded => 'F'
730 );
731 FND_MESSAGE.SET_NAME('OZF', 'OZF_API_DEBUG_MESSAGE');
732 FND_MESSAGE.SET_TOKEN('TEXT', l_oe_msg_data);
733 FND_MSG_PUB.ADD;
734 END LOOP;
735 RAISE FND_API.g_exc_unexpected_error;
736 END IF;
737
738 /*------------------------------------------------------*
739 | 4. Query selling price of RMA created by TM
740 *------------------------------------------------------*/
741 i := l_x_line_tbl.FIRST;
742 IF i IS NOT NULL THEN
743 l_header_rec := OE_ORDER_PUB.g_miss_header_rec;
744 l_header_rec.header_id := l_x_header_rec.header_id;
745 -- [BEGIN OF BUG 3868264 FIXING]
746 l_header_rec.change_reason := 'SYSTEM';
747 -- [END OF BUG 3868264 FIXING]
748 l_header_rec.operation := OE_GLOBALS.g_opr_update;
749
750 LOOP
751 IF p_claim_line_tbl(i).item_id = l_x_line_tbl(i).inventory_item_id THEN
752 l_line_tbl(i).line_id := l_x_line_tbl(i).line_id;
753 -- [BEGIN OF BUG 3868264 FIXING]
754 l_line_tbl(i).change_reason := 'SYSTEM';
755 -- [END OF BUG 3868264 FIXING]
756 l_line_tbl(i).operation := OE_GLOBALS.g_opr_update;
757
758 OPEN csr_rma_unit_price(l_x_line_tbl(i).line_id);
759 FETCH csr_rma_unit_price INTO l_rma_unit_price;
760 CLOSE csr_rma_unit_price;
761
762 IF p_claim_line_tbl(i).rate IS NOT NULL AND
763 l_rma_unit_price <> p_claim_line_tbl(i).rate THEN
764 IF l_modifer_line_id IS NULL THEN
765 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
766 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_MODIFIER_ERR');
767 FND_MSG_PUB.add;
768 END IF;
769 RAISE FND_API.g_exc_error;
770 ELSE
771 l_rma_price_diff := l_rma_unit_price - p_claim_line_tbl(i).rate;
772
773 l_line_adj_tbl(l_adj_idx) :=OE_ORDER_PUB.g_miss_line_adj_rec;
774
775 l_line_adj_tbl(l_adj_idx).line_index := i;
776 l_line_adj_tbl(l_adj_idx).list_header_id := l_modifer_header_id;
777 l_line_adj_tbl(l_adj_idx).list_line_id := l_modifer_line_id;
778 l_line_adj_tbl(l_adj_idx).modifier_level_code := 'LINE';
779 l_line_adj_tbl(l_adj_idx).operand := l_rma_price_diff;
780 l_line_adj_tbl(l_adj_idx).adjusted_amount_per_pqty := l_rma_price_diff * -1;
781 l_line_adj_tbl(l_adj_idx).arithmetic_operator := 'AMT';
782 l_line_adj_tbl(l_adj_idx).updated_flag := 'Y';
783 l_line_adj_tbl(l_adj_idx).applied_flag := 'Y';
784 -- [BEGIN OF BUG 3868264 FIXING]
785 l_line_adj_tbl(l_adj_idx).change_reason_code := 'SYSTEM';
786 -- [END OF BUG 3868264 FIXING]
787 l_line_adj_tbl(l_adj_idx).operation := OE_GLOBALS.g_opr_create;
788
789 l_adj_idx := l_adj_idx + 1;
790 END IF;
791 END IF;
792 END IF;
793 EXIT WHEN i = l_x_line_tbl.LAST;
794 i := l_x_line_tbl.NEXT(i);
795 END LOOP;
796 END IF;
797
798 /*------------------------------------------------------*
799 | 5. Adjust price
800 *------------------------------------------------------*/
801 i := l_line_adj_tbl.FIRST;
802 IF i IS NOT NULL THEN
803 OE_ORDER_GRP.Process_Order(
804 p_api_version_number => l_api_version
805 ,p_init_msg_list => FND_API.g_false
806 ,p_return_values => FND_API.g_true
807 ,p_commit => FND_API.g_false
808 ,x_return_status => l_return_status
809 ,x_msg_count => l_oe_msg_count
810 ,x_msg_data => l_oe_msg_data
811
812 ,p_header_rec => l_header_rec
813 ,p_header_val_rec => l_header_val_rec
814 ,p_Header_Adj_tbl => l_Header_Adj_tbl
815 ,p_Header_Adj_val_tbl => l_Header_Adj_val_tbl
816 ,p_Header_price_Att_tbl => l_Header_price_Att_tbl
817 ,p_Header_Adj_Att_tbl => l_Header_Adj_Att_tbl
818 ,p_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl
819 ,p_Header_Scredit_tbl => l_Header_Scredit_tbl
820 ,p_Header_Scredit_val_tbl => l_Header_Scredit_val_tbl
821 ,p_line_tbl => l_line_tbl
822 ,p_line_val_tbl => l_line_val_tbl
823 ,p_Line_Adj_tbl => l_Line_Adj_tbl
824 ,p_Line_Adj_val_tbl => l_Line_Adj_val_tbl
825 ,p_Line_price_Att_tbl => l_Line_price_Att_tbl
826 ,p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
827 ,p_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl
828 ,p_Line_Scredit_tbl => l_Line_Scredit_tbl
829 ,p_Line_Scredit_val_tbl => l_Line_Scredit_val_tbl
830 ,p_Lot_Serial_tbl => l_Lot_Serial_tbl
831 ,p_Lot_Serial_val_tbl => l_Lot_Serial_val_tbl
832 ,p_action_request_tbl => l_action_request_tbl
833
834 ,x_header_rec => l_x_header_rec
835 ,x_header_val_rec => l_x_header_val_rec
836 ,x_Header_Adj_tbl => l_x_Header_Adj_tbl
837 ,x_Header_Adj_val_tbl => l_x_Header_Adj_val_tbl
838 ,x_Header_price_Att_tbl => l_x_Header_price_Att_tbl
839 ,x_Header_Adj_Att_tbl => l_x_Header_Adj_Att_tbl
840 ,x_Header_Adj_Assoc_tbl => l_x_Header_Adj_Assoc_tbl
841 ,x_Header_Scredit_tbl => l_x_Header_Scredit_tbl
842 ,x_Header_Scredit_val_tbl => l_x_Header_Scredit_val_tbl
843
844 ,x_line_tbl => l_x_line_tbl
845 ,x_line_val_tbl => l_x_line_val_tbl
846 ,x_Line_Adj_tbl => l_x_Line_Adj_tbl
847 ,x_Line_Adj_val_tbl => l_x_Line_Adj_val_tbl
848 ,x_Line_price_Att_tbl => l_x_Line_price_Att_tbl
849 ,x_Line_Adj_Att_tbl => l_x_Line_Adj_Att_tbl
850 ,x_Line_Adj_Assoc_tbl => l_x_Line_Adj_Assoc_tbl
851 ,x_Line_Scredit_tbl => l_x_Line_Scredit_tbl
852 ,x_Line_Scredit_val_tbl => l_x_Line_Scredit_val_tbl
853 ,x_Lot_Serial_tbl => l_x_Lot_Serial_tbl
854 ,x_Lot_Serial_val_tbl => l_x_Lot_Serial_val_tbl
855
856 ,x_action_request_tbl => l_x_action_request_tbl
857 );
858 IF l_return_status = FND_API.g_ret_sts_error THEN
859 FOR i in 1 .. l_oe_msg_count LOOP
860 l_oe_msg_data := OE_MSG_PUB.get( p_msg_index => i
861 , p_encoded => 'F'
862 );
863 FND_MESSAGE.SET_NAME('AMS', 'AMS_API_DEBUG_MESSAGE');
864 FND_MESSAGE.SET_TOKEN('TEXT', l_oe_msg_data);
865 FND_MSG_PUB.ADD;
866 END LOOP;
867 RAISE FND_API.g_exc_error;
868 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
869 FOR i in 1 .. l_oe_msg_count LOOP
870 l_oe_msg_data := OE_MSG_PUB.get( p_msg_index => i
871 , p_encoded => 'F'
872 );
873 FND_MESSAGE.SET_NAME('AMS', 'AMS_API_DEBUG_MESSAGE');
874 FND_MESSAGE.SET_TOKEN('TEXT', l_oe_msg_data);
875 FND_MSG_PUB.ADD;
876 END LOOP;
877 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
878 FND_MESSAGE.set_name('AMS', 'AMS_SETL_OM_CRE_ORD_U_ERR');
879 FND_MSG_PUB.add;
880 END IF;
881 RAISE FND_API.g_exc_unexpected_error;
882 END IF;
883 END IF;
884
885
886 /*------------------------------------------------------*
887 | 6. Update RMA order information in Claim after order is
888 | been successfully created.
889 *------------------------------------------------------*/
890
891 UPDATE ozf_claims_all
892 SET payment_reference_id = l_x_header_rec.header_id
893 , payment_reference_number = l_x_header_rec.order_number
894 , payment_reference_date = l_x_header_rec.ordered_date
895 WHERE claim_id = p_x_claim_rec.claim_id;
896
897 i := l_x_line_tbl.FIRST;
898 IF i IS NOT NULL THEN
899 LOOP
900 IF p_claim_line_tbl(i).item_id = l_x_line_tbl(i).inventory_item_id THEN
901 UPDATE ozf_claim_lines_all
902 SET payment_reference_id = l_x_line_tbl(i).line_id
903 , payment_reference_number = l_x_line_tbl(i).line_number
904 WHERE claim_line_id = p_claim_line_tbl(i).claim_line_id;
905 END IF;
906 EXIT WHEN i = l_x_line_tbl.LAST;
907 i := l_x_line_tbl.NEXT(i);
908 END LOOP;
909 END IF;
910
911 IF OZF_DEBUG_HIGH_ON THEN
912 OZF_Utility_PVT.debug_message(l_full_name||': end');
913 END IF;
914 EXCEPTION
915 WHEN FND_API.g_exc_error THEN
916 x_return_status := FND_API.g_ret_sts_error;
917
918 WHEN FND_API.g_exc_unexpected_error THEN
919 x_return_status := FND_API.g_ret_sts_unexp_error ;
920
921 WHEN OTHERS THEN
922 x_return_status := FND_API.g_ret_sts_unexp_error ;
923 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
924 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
925 END IF;
926
927 END Complete_RMA_Order;
928
929
930 /*=======================================================================*
931 | PROCEDURE
932 | Book_RMA_Order
933 |
934 | NOTES
935 |
936 | HISTORY
937 | 24-OCT-2002 mchang Create.
938 *=======================================================================*/
939 PROCEDURE Book_RMA_Order(
940 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
941 ,p_claim_line_tbl IN OZF_CLAIM_LINE_PVT.claim_line_tbl_type
942
943 ,x_return_status OUT NOCOPY VARCHAR2
944 ,x_msg_data OUT NOCOPY VARCHAR2
945 ,x_msg_count OUT NOCOPY NUMBER
946 )
947 IS
948 l_api_version CONSTANT NUMBER := 1.0;
949 l_api_name CONSTANT VARCHAR2(30) := 'Book_RMA_Order';
950 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
951 l_return_status VARCHAR2(1);
952
953 l_header_rec OE_ORDER_PUB.header_rec_type;
954 l_header_val_rec OE_ORDER_PUB.header_val_rec_type;
955 l_header_adj_tbl OE_ORDER_PUB.header_adj_tbl_type;
956 l_header_adj_val_tbl OE_ORDER_PUB.header_adj_val_tbl_type;
957 l_header_price_att_tbl OE_ORDER_PUB.header_price_att_tbl_type;
958 l_header_adj_att_tbl OE_ORDER_PUB.header_adj_att_tbl_type;
959 l_header_adj_assoc_tbl OE_ORDER_PUB.header_adj_assoc_tbl_type;
960 l_header_scredit_tbl OE_ORDER_PUB.header_scredit_tbl_type;
961 l_header_scredit_val_tbl OE_ORDER_PUB.header_scredit_val_tbl_type;
962
963 l_line_tbl OE_ORDER_PUB.line_tbl_type;
964 l_line_val_tbl OE_ORDER_PUB.line_val_tbl_type;
965 l_line_adj_tbl OE_ORDER_PUB.line_adj_tbl_type;
966 l_line_adj_val_tbl OE_ORDER_PUB.line_adj_val_tbl_type;
967 l_line_price_att_tbl OE_ORDER_PUB.line_price_att_tbl_type;
968 l_line_adj_att_tbl OE_ORDER_PUB.line_adj_att_tbl_type;
969 l_line_adj_assoc_tbl OE_ORDER_PUB.line_adj_assoc_tbl_type;
970 l_line_scredit_tbl OE_ORDER_PUB.line_scredit_tbl_type;
971 l_line_scredit_val_tbl OE_ORDER_PUB.line_scredit_val_tbl_type;
972 l_lot_serial_tbl OE_ORDER_PUB.lot_serial_tbl_type;
973 l_lot_serial_val_tbl OE_ORDER_PUB.lot_serial_val_tbl_type;
974 l_action_request_tbl OE_ORDER_PUB.request_tbl_type;
975
976 l_x_header_rec OE_ORDER_PUB.header_rec_type;
977 l_x_header_val_rec OE_ORDER_PUB.header_val_rec_type;
978 l_x_header_adj_tbl OE_ORDER_PUB.header_adj_tbl_type;
979 l_x_header_adj_val_tbl OE_ORDER_PUB.header_adj_val_tbl_type;
980 l_x_header_price_att_tbl OE_ORDER_PUB.header_price_att_tbl_type;
981 l_x_header_adj_att_tbl OE_ORDER_PUB.header_adj_att_tbl_type;
982 l_x_header_adj_assoc_tbl OE_ORDER_PUB.header_adj_assoc_tbl_type;
983 l_x_header_scredit_tbl OE_ORDER_PUB.header_scredit_tbl_type;
984 l_x_header_scredit_val_tbl OE_ORDER_PUB.header_scredit_val_tbl_type;
985
986 l_x_line_tbl OE_ORDER_PUB.line_tbl_type;
987 l_x_line_val_tbl OE_ORDER_PUB.line_val_tbl_type;
988 l_x_line_adj_tbl OE_ORDER_PUB.line_adj_tbl_type;
989 l_x_line_adj_val_tbl OE_ORDER_PUB.line_adj_val_tbl_type;
990 l_x_line_price_att_tbl OE_ORDER_PUB.line_price_att_tbl_type;
991 l_x_line_adj_att_tbl OE_ORDER_PUB.line_adj_att_tbl_type;
992 l_x_line_adj_assoc_tbl OE_ORDER_PUB.line_adj_assoc_tbl_type;
993 l_x_line_scredit_tbl OE_ORDER_PUB.line_scredit_tbl_type;
994 l_x_line_scredit_val_tbl OE_ORDER_PUB.line_scredit_val_tbl_type;
995 l_x_lot_serial_tbl OE_ORDER_PUB.lot_serial_tbl_type;
996 l_x_lot_serial_val_tbl OE_ORDER_PUB.lot_serial_val_tbl_type;
997 l_x_action_request_tbl OE_ORDER_PUB.request_tbl_type;
998
999 i NUMBER;
1000 l_oe_msg_count NUMBER;
1001 l_oe_msg_data VARCHAR2(2000);
1002
1003 BEGIN
1004 -------------------- initialize -----------------------
1005 IF OZF_DEBUG_HIGH_ON THEN
1006 OZF_Utility_PVT.debug_message(l_full_name||': start');
1007 END IF;
1008
1009 x_return_status := FND_API.g_ret_sts_success;
1010
1011 OE_STANDARD_WF.save_messages_off;
1012
1013 ------------------------ start -------------------------
1014 /*------------------------------------------------------*
1015 | Book RMA order
1016 *------------------------------------------------------*/
1017 l_action_request_tbl(1) := OE_ORDER_PUB.g_miss_request_rec;
1018 l_action_request_tbl(1).request_type := OE_GLOBALS.g_book_order;
1019 l_action_request_tbl(1).entity_code := OE_GLOBALS.g_entity_header;
1020 --l_action_request_tbl(1).entity_id := l_x_header_rec.header_id;
1021 l_action_request_tbl(1).entity_id := p_claim_rec.payment_reference_id;
1022
1023
1024 OE_ORDER_GRP.Process_Order(
1025 p_api_version_number => l_api_version
1026 ,p_init_msg_list => FND_API.g_false
1027 ,p_return_values => FND_API.g_true
1028 ,p_commit => FND_API.g_false
1029 ,x_return_status => l_return_status
1030 ,x_msg_count => l_oe_msg_count
1031 ,x_msg_data => l_oe_msg_data
1032
1033 ,p_action_request_tbl => l_action_request_tbl
1034
1035 ,x_header_rec => l_x_header_rec
1036 ,x_header_val_rec => l_x_header_val_rec
1037 ,x_Header_Adj_tbl => l_x_Header_Adj_tbl
1038 ,x_Header_Adj_val_tbl => l_x_Header_Adj_val_tbl
1039 ,x_Header_price_Att_tbl => l_x_Header_price_Att_tbl
1040 ,x_Header_Adj_Att_tbl => l_x_Header_Adj_Att_tbl
1041 ,x_Header_Adj_Assoc_tbl => l_x_Header_Adj_Assoc_tbl
1042 ,x_Header_Scredit_tbl => l_x_Header_Scredit_tbl
1043 ,x_Header_Scredit_val_tbl => l_x_Header_Scredit_val_tbl
1044
1045 ,x_line_tbl => l_x_line_tbl
1046 ,x_line_val_tbl => l_x_line_val_tbl
1047 ,x_Line_Adj_tbl => l_x_Line_Adj_tbl
1048 ,x_Line_Adj_val_tbl => l_x_Line_Adj_val_tbl
1049 ,x_Line_price_Att_tbl => l_x_Line_price_Att_tbl
1050 ,x_Line_Adj_Att_tbl => l_x_Line_Adj_Att_tbl
1051 ,x_Line_Adj_Assoc_tbl => l_x_Line_Adj_Assoc_tbl
1052 ,x_Line_Scredit_tbl => l_x_Line_Scredit_tbl
1053 ,x_Line_Scredit_val_tbl => l_x_Line_Scredit_val_tbl
1054 ,x_Lot_Serial_tbl => l_x_Lot_Serial_tbl
1055 ,x_Lot_Serial_val_tbl => l_x_Lot_Serial_val_tbl
1056
1057 ,x_action_request_tbl => l_x_action_request_tbl
1058 );
1059 IF l_x_action_request_tbl(1).request_type <> OE_GLOBALS.g_book_order THEN
1060 FOR i in 1 .. l_oe_msg_count LOOP
1061 l_oe_msg_data := OE_MSG_PUB.get( p_msg_index => i
1062 , p_encoded => 'F'
1063 );
1064 FND_MESSAGE.SET_NAME('OZF', 'OZF_API_DEBUG_MESSAGE');
1065 FND_MESSAGE.SET_TOKEN('TEXT', l_oe_msg_data);
1066 FND_MSG_PUB.ADD;
1067 END LOOP;
1068
1069 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1070 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_BOK_ORD_E_ERR');
1071 FND_MSG_PUB.add;
1072 END IF;
1073 RAISE FND_API.g_exc_error;
1074
1075 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1076 FOR i in 1 .. l_oe_msg_count LOOP
1077 l_oe_msg_data := OE_MSG_PUB.get( p_msg_index => i
1078 , p_encoded => 'F'
1079 );
1080 FND_MESSAGE.SET_NAME('OZF', 'OZF_API_DEBUG_MESSAGE');
1081 FND_MESSAGE.SET_TOKEN('TEXT', l_oe_msg_data);
1082 FND_MSG_PUB.ADD;
1083 END LOOP;
1084
1085 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1086 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_BOK_ORD_E_ERR');
1087 FND_MSG_PUB.add;
1088 END IF;
1089 RAISE FND_API.g_exc_error;
1090 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1091 FOR i in 1 .. l_oe_msg_count LOOP
1092 l_oe_msg_data := OE_MSG_PUB.get( p_msg_index => i
1093 , p_encoded => 'F'
1094 );
1095 FND_MESSAGE.SET_NAME('OZF', 'OZF_API_DEBUG_MESSAGE');
1096 FND_MESSAGE.SET_TOKEN('TEXT', l_oe_msg_data);
1097 FND_MSG_PUB.ADD;
1098 END LOOP;
1099
1100 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1101 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OM_BOK_ORD_U_ERR');
1102 FND_MSG_PUB.add;
1103 END IF;
1104 RAISE FND_API.g_exc_unexpected_error;
1105 END IF;
1106
1107 IF OZF_DEBUG_HIGH_ON THEN
1108 OZF_Utility_PVT.debug_message(l_full_name||': end');
1109 END IF;
1110 EXCEPTION
1111 WHEN FND_API.g_exc_error THEN
1112 x_return_status := FND_API.g_ret_sts_error;
1113
1114 WHEN FND_API.g_exc_unexpected_error THEN
1115 x_return_status := FND_API.g_ret_sts_unexp_error ;
1116
1117 WHEN OTHERS THEN
1118 x_return_status := FND_API.g_ret_sts_unexp_error ;
1119 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1120 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1121 END IF;
1122
1123 END Book_RMA_Order;
1124
1125
1126 /*=======================================================================*
1127 | PROCEDURE
1128 | Create_OM_Payment
1129 |
1130 | NOTES
1131 |
1132 | HISTORY
1133 | 24-OCT-2002 mchang Create.
1134 *=======================================================================*/
1135 PROCEDURE Create_OM_Payment(
1136 p_api_version IN NUMBER
1137 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
1138 ,p_commit IN VARCHAR2 := FND_API.g_false
1139 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
1140
1141 ,x_return_status OUT NOCOPY VARCHAR2
1142 ,x_msg_data OUT NOCOPY VARCHAR2
1143 ,x_msg_count OUT NOCOPY NUMBER
1144
1145 ,p_claim_id IN NUMBER
1146 )
1147 IS
1148 l_api_version CONSTANT NUMBER := 1.0;
1149 l_api_name CONSTANT VARCHAR2(30) := 'Create_OM_Payment';
1150 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1151 l_return_status VARCHAR2(1);
1152
1153 l_claim_rec OZF_CLAIM_PVT.claim_rec_type;
1154 l_claim_line_tbl OZF_CLAIM_LINE_PVT.claim_line_tbl_type;
1155
1156 BEGIN
1157 -------------------- initialize -----------------------
1158 SAVEPOINT Create_OM_Payment;
1159
1160 IF OZF_DEBUG_HIGH_ON THEN
1161 OZF_Utility_PVT.debug_message(l_full_name||': start');
1162 END IF;
1163
1164 IF FND_API.to_boolean(p_init_msg_list) THEN
1165 FND_MSG_PUB.initialize;
1166 END IF;
1167
1168
1169 IF NOT FND_API.compatible_api_call(
1170 l_api_version,
1171 p_api_version,
1172 l_api_name,
1173 g_pkg_name
1174 ) THEN
1175 RAISE FND_API.g_exc_unexpected_error;
1176 END IF;
1177
1178 x_return_status := FND_API.g_ret_sts_success;
1179
1180 ------------------------ start -------------------------
1181 Query_Claim(
1182 p_claim_id => p_claim_id
1183 ,x_claim_rec => l_claim_rec
1184 ,x_return_status => l_return_status
1185 );
1186 IF l_return_status = FND_API.g_ret_sts_error THEN
1187 RAISE FND_API.g_exc_error;
1188 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1189 RAISE FND_API.g_exc_unexpected_error;
1190 END IF;
1191
1192 Query_Claim_Line(
1193 p_claim_id => p_claim_id
1194 ,x_claim_line_tbl => l_claim_line_tbl
1195 ,x_return_status => l_return_status
1196 );
1197 IF l_return_status = FND_API.g_ret_sts_error THEN
1198 RAISE FND_API.g_exc_error;
1199 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1200 RAISE FND_API.g_exc_unexpected_error;
1201 END IF;
1202
1203 IF l_claim_rec.payment_method = 'RMA' THEN
1204 IF OZF_DEBUG_HIGH_ON THEN
1205 OZF_Utility_PVT.debug_message('Create RMA order for ==> '||l_claim_rec.claim_number);
1206 END IF;
1207 OZF_OM_PAYMENT_PVT.Complete_RMA_Order(
1208 p_x_claim_rec => l_claim_rec
1209 ,p_claim_line_tbl => l_claim_line_tbl
1210 ,x_return_status => l_return_status
1211 ,x_msg_data => x_msg_data
1212 ,x_msg_count => x_msg_count
1213 );
1214 IF l_return_status = FND_API.g_ret_sts_error THEN
1215 RAISE FND_API.g_exc_error;
1216 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1217 RAISE FND_API.g_exc_unexpected_error;
1218 END IF;
1219
1220 Query_Claim(
1221 p_claim_id => p_claim_id
1222 ,x_claim_rec => l_claim_rec
1223 ,x_return_status => l_return_status
1224 );
1225 IF l_return_status = FND_API.g_ret_sts_error THEN
1226 RAISE FND_API.g_exc_error;
1227 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1228 RAISE FND_API.g_exc_unexpected_error;
1229 END IF;
1230
1231 IF OZF_DEBUG_HIGH_ON THEN
1232 OZF_Utility_PVT.debug_message('Book RMA order for ==> '||l_claim_rec.claim_number);
1233 OZF_Utility_PVT.debug_message('RMA order number = '||l_claim_rec.payment_reference_number);
1234 END IF;
1235 Book_RMA_Order(
1236 p_claim_rec => l_claim_rec
1237 ,p_claim_line_tbl => l_claim_line_tbl
1238 ,x_return_status => l_return_status
1239 ,x_msg_data => x_msg_data
1240 ,x_msg_count => x_msg_count
1241 );
1242 IF l_return_status = FND_API.g_ret_sts_error THEN
1243 RAISE FND_API.g_exc_error;
1244 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1245 RAISE FND_API.g_exc_unexpected_error;
1246 END IF;
1247 END IF;
1248
1249 -- reset payment status in ozf_claim_lines_all
1250 BEGIN
1251 UPDATE ozf_claim_lines_all
1252 SET payment_status = 'PENDING'
1253 WHERE claim_id = p_claim_id;
1254 EXCEPTION
1255 WHEN OTHERS THEN
1256 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1257 FND_MESSAGE.SET_NAME('OZF','OZF_API_DEBUG_MESSAGE');
1258 FND_MESSAGE.SET_TOKEN('TEXT',SQLERRM);
1259 FND_MSG_PUB.ADD;
1260 END IF;
1261 END;
1262
1263 ------------------------ finish ------------------------
1264 FND_MSG_PUB.count_and_get(
1265 p_encoded => FND_API.g_false,
1266 p_count => x_msg_count,
1267 p_data => x_msg_data
1268 );
1269
1270 IF OZF_DEBUG_HIGH_ON THEN
1271 OZF_Utility_PVT.debug_message(l_full_name ||': end');
1272 END IF;
1273 EXCEPTION
1274 WHEN FND_API.g_exc_error THEN
1275 ROLLBACK TO Create_OM_Payment;
1276 x_return_status := FND_API.g_ret_sts_error;
1277 FND_MSG_PUB.count_and_get (
1278 p_encoded => FND_API.g_false
1279 ,p_count => x_msg_count
1280 ,p_data => x_msg_data
1281 );
1282
1283 WHEN FND_API.g_exc_unexpected_error THEN
1284 ROLLBACK TO Create_OM_Payment;
1285 x_return_status := FND_API.g_ret_sts_unexp_error ;
1286 FND_MSG_PUB.count_and_get (
1287 p_encoded => FND_API.g_false
1288 ,p_count => x_msg_count
1289 ,p_data => x_msg_data
1290 );
1291
1292 WHEN OTHERS THEN
1293 ROLLBACK TO Create_OM_Payment;
1294 x_return_status := FND_API.g_ret_sts_unexp_error ;
1295 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1296 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1297 END IF;
1298 FND_MSG_PUB.count_and_get(
1299 p_encoded => FND_API.g_false
1300 ,p_count => x_msg_count
1301 ,p_data => x_msg_data
1302 );
1303
1304 END Create_OM_Payment;
1305
1306 END OZF_OM_PAYMENT_PVT;