[Home] [Help]
258: p_msg_data IN VARCHAR2) AS
259: l_msg_data VARCHAR2(5000);
260: l_msg_count NUMBER;
261: Begin
262: oe_debug_pub.add('no. of OE messages :'||p_msg_count);
263: for k in 1 .. p_msg_count loop
264: l_msg_data := oe_msg_pub.get( p_msg_index => k,
265: p_encoded => 'F'
266: );
263: for k in 1 .. p_msg_count loop
264: l_msg_data := oe_msg_pub.get( p_msg_index => k,
265: p_encoded => 'F'
266: );
267: oe_debug_pub.add(substr(l_msg_data,1,255));
268: oe_debug_pub.add('Error msg: '||substr(l_msg_data,1,200));
269: end loop;
270:
271: fnd_msg_pub.count_and_get( p_encoded => 'F'
264: l_msg_data := oe_msg_pub.get( p_msg_index => k,
265: p_encoded => 'F'
266: );
267: oe_debug_pub.add(substr(l_msg_data,1,255));
268: oe_debug_pub.add('Error msg: '||substr(l_msg_data,1,200));
269: end loop;
270:
271: fnd_msg_pub.count_and_get( p_encoded => 'F'
272: ,p_count => l_msg_count
271: fnd_msg_pub.count_and_get( p_encoded => 'F'
272: ,p_count => l_msg_count
273: ,p_data => l_msg_data);
274:
275: --oe_debug_pub.add('no. of FND messages :'||l_msg_count,1);
276: oe_debug_pub.add('no. of FND messages :'||l_msg_count);
277:
278: for k in 1 .. l_msg_count loop
279: l_msg_data := fnd_msg_pub.get( p_msg_index => k,
272: ,p_count => l_msg_count
273: ,p_data => l_msg_data);
274:
275: --oe_debug_pub.add('no. of FND messages :'||l_msg_count,1);
276: oe_debug_pub.add('no. of FND messages :'||l_msg_count);
277:
278: for k in 1 .. l_msg_count loop
279: l_msg_data := fnd_msg_pub.get( p_msg_index => k,
280: p_encoded => 'F'
278: for k in 1 .. l_msg_count loop
279: l_msg_data := fnd_msg_pub.get( p_msg_index => k,
280: p_encoded => 'F'
281: );
282: oe_debug_pub.add('Error msg: '||substr(l_msg_data,1,200));
283: oe_debug_pub.add(substr(l_msg_data,1,255));
284: end loop;
285:
286: End;
279: l_msg_data := fnd_msg_pub.get( p_msg_index => k,
280: p_encoded => 'F'
281: );
282: oe_debug_pub.add('Error msg: '||substr(l_msg_data,1,200));
283: oe_debug_pub.add(substr(l_msg_data,1,255));
284: end loop;
285:
286: End;
287:
295: And retrobill_request_id <> G_CURRENT_RETROBILL_REQUEST_ID;
296:
297: l_retro_line_id Number;
298: l_retro_header_id Number;
299: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
300: l_query_line_id Number;
301: l_query_header_id Number;
302:
303: Begin
302:
303: Begin
304:
305: If l_debug_level > 0 Then
306: oe_debug_pub.add('Entering oe_retrobill_pvt.get_last_retro...input header_id:'||p_header_id);
307: End If;
308:
309: --!!!Need to check system param, if no retrobill should return the same header_id immediately
310: --to save processing time!!!
319: x_header_id:=l_retro_header_id;
320: END IF;
321:
322: If l_debug_level > 0 Then
323: oe_debug_pub.add('Exiting oe_retrobill_pvt.get_last_retro...output header_id:'||x_header_id);
324: End If;
325:
326: Exception
327: When Others then
324: End If;
325:
326: Exception
327: When Others then
328: oe_debug_pub.add('Retro:get_last_retro_HdrID:'||SQLERRM);
329: x_header_id:=p_header_id;
330:
331: End;
332:
343: And l.retrobill_request_id <> G_CURRENT_RETROBILL_REQUEST_ID
344: AND b.line_id = p_line_id;
345: l_retro_line_id Number;
346: l_retro_header_id Number;
347: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
348: l_query_line_id Number;
349: l_query_header_id Number;
350: Begin
351:
349: l_query_header_id Number;
350: Begin
351:
352: If l_debug_level > 0 Then
353: oe_debug_pub.add('Entering oe_retrobill_pvt.get_last_retro...input Line_id:'||p_line_id);
354: End If;
355:
356: --!!!Need to check system param, if no retrobill should return the same header_id immediately
357: --to save processing time!!!
367: x_line_id:=l_retro_line_id;
368: END IF;
369:
370: If l_debug_level > 0 Then
371: oe_debug_pub.add('Exiting oe_retrobill_pvt.get_last_retro...output line_id:'||x_line_id);
372: End If;
373:
374: Exception
375: When Others then
372: End If;
373:
374: Exception
375: When Others then
376: oe_debug_pub.add('Retro:get_last_retro_linID:'||SQLERRM);
377: x_line_id:=p_line_id;
378: End;
379:
380:
387: --bug3738043 This function returns the number of pricing phases other than List Line Base Price Phase where the RETROBILL event is attached.
388: --The global variable G_RETRO_PRICING_PHASE_COUNT is initialized by calling this function in the procedure Perform_Operations if it is preview again
389: FUNCTION Get_Retro_Pricing_Phase_Count RETURN NUMBER AS
390: l_retro_pricing_phase_count NUMBER := 0;
391: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
392: BEGIN
393:
394: SELECT count(*) INTO l_retro_pricing_phase_count
395: FROM qp_pricing_phases p,
399: AND e.pricing_event_code='RETROBILL'
400: AND trunc(sysdate) BETWEEN trunc(nvl(e.end_date_active,sysdate))
401: AND trunc(nvl(e.end_date_active,sysdate));
402: IF l_debug_level > 0 THEN
403: oe_debug_pub.add('IN Get_Retro_Pricing_Phase_count: The number of phases for RETROBILL event other than 1 is ' || l_retro_pricing_phase_count);
404: END IF;
405:
406: RETURN l_retro_pricing_phase_count;
407: EXCEPTION
442: x_ulp_sum:=nvl(x_ulp_sum,0);
443:
444: Exception
445: When Others Then
446: oe_debug_pub.add('Retro:Get_Retrobilled_Sum:'||SQLERRM);
447: Raise;
448: End;
449:
450: Procedure Get_Return_Price(p_header_id IN NUMBER,
458: x_ulp_ppqty OUT NOCOPY NUMBER,--bug3540728
459: x_usp_ppqty OUT NOCOPY NUMBER) AS --bug3540728
460: l_usp_sum NUMBER;
461: l_ulp_sum NUMBER;
462: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
463: Begin
464: Get_Retrobilled_Sum(p_header_id=>p_header_id,
465: p_line_id =>p_line_id,
466: x_usp_sum =>l_usp_sum,
466: x_usp_sum =>l_usp_sum,
467: x_ulp_sum =>l_ulp_sum);
468:
469: IF l_debug_level > 0 THEN
470: oe_debug_pub.add('Retro:Input header_id:'||p_header_id);
471: oe_debug_pub.add('Retro:Input line_id:'||p_line_id);
472: oe_debug_pub.add('Retro:Input USP:'||p_usp);
473: oe_debug_pub.add('Retro:Input ULP:'||p_ulp);
474: END IF;
467: x_ulp_sum =>l_ulp_sum);
468:
469: IF l_debug_level > 0 THEN
470: oe_debug_pub.add('Retro:Input header_id:'||p_header_id);
471: oe_debug_pub.add('Retro:Input line_id:'||p_line_id);
472: oe_debug_pub.add('Retro:Input USP:'||p_usp);
473: oe_debug_pub.add('Retro:Input ULP:'||p_ulp);
474: END IF;
475:
468:
469: IF l_debug_level > 0 THEN
470: oe_debug_pub.add('Retro:Input header_id:'||p_header_id);
471: oe_debug_pub.add('Retro:Input line_id:'||p_line_id);
472: oe_debug_pub.add('Retro:Input USP:'||p_usp);
473: oe_debug_pub.add('Retro:Input ULP:'||p_ulp);
474: END IF;
475:
476: x_usp:=p_usp + l_usp_sum;
469: IF l_debug_level > 0 THEN
470: oe_debug_pub.add('Retro:Input header_id:'||p_header_id);
471: oe_debug_pub.add('Retro:Input line_id:'||p_line_id);
472: oe_debug_pub.add('Retro:Input USP:'||p_usp);
473: oe_debug_pub.add('Retro:Input ULP:'||p_ulp);
474: END IF;
475:
476: x_usp:=p_usp + l_usp_sum;
477: x_ulp:=p_ulp + l_ulp_sum;
482: END IF;
483: --bug3540728 end
484:
485: IF l_debug_level > 0 THEN
486: oe_debug_pub.add('Retro:Return usp_sum:'||l_usp_sum);
487: oe_debug_pub.add('Retro:Return ulp_sum:'||l_ulp_sum);
488: oe_debug_pub.add('Retro:Return USP:'||x_usp);
489: oe_debug_pub.add('Retro:Return ULP:'||x_ulp);
490: oe_debug_pub.add('Retro:Return ULP_PER_PQTY:'||x_ulp_ppqty);
483: --bug3540728 end
484:
485: IF l_debug_level > 0 THEN
486: oe_debug_pub.add('Retro:Return usp_sum:'||l_usp_sum);
487: oe_debug_pub.add('Retro:Return ulp_sum:'||l_ulp_sum);
488: oe_debug_pub.add('Retro:Return USP:'||x_usp);
489: oe_debug_pub.add('Retro:Return ULP:'||x_ulp);
490: oe_debug_pub.add('Retro:Return ULP_PER_PQTY:'||x_ulp_ppqty);
491: oe_debug_pub.add('Retro:Return USP_PER_PQTY:'||x_usp_ppqty);
484:
485: IF l_debug_level > 0 THEN
486: oe_debug_pub.add('Retro:Return usp_sum:'||l_usp_sum);
487: oe_debug_pub.add('Retro:Return ulp_sum:'||l_ulp_sum);
488: oe_debug_pub.add('Retro:Return USP:'||x_usp);
489: oe_debug_pub.add('Retro:Return ULP:'||x_ulp);
490: oe_debug_pub.add('Retro:Return ULP_PER_PQTY:'||x_ulp_ppqty);
491: oe_debug_pub.add('Retro:Return USP_PER_PQTY:'||x_usp_ppqty);
492: END IF;
485: IF l_debug_level > 0 THEN
486: oe_debug_pub.add('Retro:Return usp_sum:'||l_usp_sum);
487: oe_debug_pub.add('Retro:Return ulp_sum:'||l_ulp_sum);
488: oe_debug_pub.add('Retro:Return USP:'||x_usp);
489: oe_debug_pub.add('Retro:Return ULP:'||x_ulp);
490: oe_debug_pub.add('Retro:Return ULP_PER_PQTY:'||x_ulp_ppqty);
491: oe_debug_pub.add('Retro:Return USP_PER_PQTY:'||x_usp_ppqty);
492: END IF;
493:
486: oe_debug_pub.add('Retro:Return usp_sum:'||l_usp_sum);
487: oe_debug_pub.add('Retro:Return ulp_sum:'||l_ulp_sum);
488: oe_debug_pub.add('Retro:Return USP:'||x_usp);
489: oe_debug_pub.add('Retro:Return ULP:'||x_ulp);
490: oe_debug_pub.add('Retro:Return ULP_PER_PQTY:'||x_ulp_ppqty);
491: oe_debug_pub.add('Retro:Return USP_PER_PQTY:'||x_usp_ppqty);
492: END IF;
493:
494: End;
487: oe_debug_pub.add('Retro:Return ulp_sum:'||l_ulp_sum);
488: oe_debug_pub.add('Retro:Return USP:'||x_usp);
489: oe_debug_pub.add('Retro:Return ULP:'||x_ulp);
490: oe_debug_pub.add('Retro:Return ULP_PER_PQTY:'||x_ulp_ppqty);
491: oe_debug_pub.add('Retro:Return USP_PER_PQTY:'||x_usp_ppqty);
492: END IF;
493:
494: End;
495:
533: null;
534: END;
535:
536:
537: oe_debug_pub.add('Retro:Tax code invalid');
538: FND_MESSAGE.SET_NAME('ONT','ONT_RETROBILL_TAX_INVALID');
539: FND_MESSAGE.SET_TOKEN('TAX_CODE',p_tax_code);
540: FND_MESSAGE.SET_TOKEN('ORDER',l_order_number);
541: FND_MESSAGE.SET_TOKEN('LINE',l_line_no);
541: FND_MESSAGE.SET_TOKEN('LINE',l_line_no);
542: OE_MSG_PUB.Add;
543: RETURN FALSE;
544: WHEN OTHERS THEN
545: oe_debug_pub.add('Retro:IS_TAX_CODE_VALID:'||SQLERRM);
546: RETURN FALSE;
547: End;
548:
549: --key header and line is to facilate an index search to oe_order_lines_all
565: And l.orig_sys_line_ref = p_key_line_id
566: And retrobill_request_id <> G_CURRENT_RETROBILL_REQUEST_ID;
567: l_retro_line_id Number;
568: l_retro_header_id Number;
569: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
570: l_query_line_id Number;
571: l_query_header_id Number;
572:
573: Begin
577:
578: IF l_retro_line_id IS NULL THEN
579: --No previous retrobill line perform a reqular query_
580: --IF l_debug_level > 0 THEN
581: oe_debug_pub.add('Retro: no previous retrobill line');
582: --END IF;
583: l_query_line_id := p_key_line_id;
584: l_query_header_id := p_key_header_id;
585: x_retro_exists := FALSE; --bug3738043
587: l_query_line_id := l_retro_line_id;
588: l_query_header_id := l_retro_header_id;
589: x_retro_exists := TRUE; --bug3738043
590: --IF l_debug_level > 0 THEN
591: oe_debug_pub.add('Retro:Previoulsy Retrobilled, Get Most Recent Adj:retro line id:'||l_retro_line_id);
592: --END IF;
593: End IF;
594:
595:
593: End IF;
594:
595:
596: IF p_adjustment_level = 'HEADER' THEN
597: oe_debug_pub.add('RETRO:QUERY Order level adjustment');
598: OE_Line_Adj_Util.Query_Rows(p_header_Id => l_query_header_id
599: ,x_Line_Adj_Tbl => x_Line_Adj_Tbl);
600: ELSE
601: oe_debug_pub.add('RETRO:QUERY line level adjustment');
597: oe_debug_pub.add('RETRO:QUERY Order level adjustment');
598: OE_Line_Adj_Util.Query_Rows(p_header_Id => l_query_header_id
599: ,x_Line_Adj_Tbl => x_Line_Adj_Tbl);
600: ELSE
601: oe_debug_pub.add('RETRO:QUERY line level adjustment');
602: OE_Line_Adj_Util.Query_Rows(p_line_Id => l_query_line_id
603: ,x_Line_Adj_Tbl => x_Line_Adj_Tbl);
604: END IF;
605:
604: END IF;
605:
606: Exception
607: When Others Then
608: oe_debug_pub.add('Retro:'||SQLERRM);
609: END;
610:
611: /******************************************************************
612: To get a invoice id of the original line
629: and b.language = (select language_code from fnd_languages
630: where installed_flag = 'B');
631: --CONTEXT,ATTRIBUTE1,ATTRIBUTE2 and ATTRIBUTE6 will hit a concatenate index in AR table
632: l_ci_id NUMBER;
633: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
634: Begin
635: --should not be multiple, just in case
636: For i IN Get_CI_Line_Id Loop
637: l_ci_id:=i.customer_trx_line_id;
637: l_ci_id:=i.customer_trx_line_id;
638: End Loop;
639:
640: If l_debug_level > 0 Then
641: oe_debug_pub.add('Retro:Leaving get credit invoice id:'||l_ci_id);
642: End If;
643:
644: Return l_ci_id;
645:
680: --bug3738043 end
681:
682: l_line_adj_tbl OE_ORDER_PUB.LINE_ADJ_TBL_TYPE;
683: i PLS_INTEGER;
684: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
685: --bug3738043 start
686: l_new_price_adj_id_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
687: k PLS_INTEGER := 1 ;
688: l_found BOOLEAN := FALSE;
691: --bug3738043 end
692: Begin
693:
694: if l_debug_level > 0 then
695: oe_debug_pub.add('Entering oe_retro_pvt.preprocess_adjustments');
696: end if;
697:
698: --bug3738043 start
699: IF l_debug_level > 0 THEN
696: end if;
697:
698: --bug3738043 start
699: IF l_debug_level > 0 THEN
700: oe_debug_pub.add('PVIPRANA: retrobill header_id: ' || p_header_id);
701: oe_debug_pub.add('PVIPRANA: retrobill line_id: ' || p_line_id);
702: END IF;
703: --bug3738043 end
704:
697:
698: --bug3738043 start
699: IF l_debug_level > 0 THEN
700: oe_debug_pub.add('PVIPRANA: retrobill header_id: ' || p_header_id);
701: oe_debug_pub.add('PVIPRANA: retrobill line_id: ' || p_line_id);
702: END IF;
703: --bug3738043 end
704:
705: DELETE From OE_PRICE_ADJUSTMENTS
717: i:=l_line_adj_tbl.first;
718: WHILE I IS NOT NULL LOOP
719:
720: if l_debug_level > 0 then
721: oe_debug_pub.add(' list_line_no: ' || l_line_adj_tbl(i).list_line_no || ' list_line_id: ' || l_line_adj_tbl(i).list_line_id);
722: oe_debug_pub.add(' operand queried:'||l_line_adj_tbl(i).operand||' operand_perpqty:'||l_line_adj_tbl(i).operand_per_pqty);
723: oe_debug_pub.add(' list_line_type_code:'||l_line_adj_tbl(i).list_line_type_code||'applied_flag:'||l_line_adj_tbl(i).applied_flag); oe_debug_pub.add(' adjusted amount:'||l_line_adj_tbl(i).adjusted_amount);
724: oe_debug_pub.add(' adj_amt_pqty:'||l_line_adj_tbl(i).adjusted_amount_per_pqty);
725: oe_debug_pub.add(' retrobill_request_id: '||l_line_adj_tbl(i).retrobill_request_id);
718: WHILE I IS NOT NULL LOOP
719:
720: if l_debug_level > 0 then
721: oe_debug_pub.add(' list_line_no: ' || l_line_adj_tbl(i).list_line_no || ' list_line_id: ' || l_line_adj_tbl(i).list_line_id);
722: oe_debug_pub.add(' operand queried:'||l_line_adj_tbl(i).operand||' operand_perpqty:'||l_line_adj_tbl(i).operand_per_pqty);
723: oe_debug_pub.add(' list_line_type_code:'||l_line_adj_tbl(i).list_line_type_code||'applied_flag:'||l_line_adj_tbl(i).applied_flag); oe_debug_pub.add(' adjusted amount:'||l_line_adj_tbl(i).adjusted_amount);
724: oe_debug_pub.add(' adj_amt_pqty:'||l_line_adj_tbl(i).adjusted_amount_per_pqty);
725: oe_debug_pub.add(' retrobill_request_id: '||l_line_adj_tbl(i).retrobill_request_id);
726: end if;
719:
720: if l_debug_level > 0 then
721: oe_debug_pub.add(' list_line_no: ' || l_line_adj_tbl(i).list_line_no || ' list_line_id: ' || l_line_adj_tbl(i).list_line_id);
722: oe_debug_pub.add(' operand queried:'||l_line_adj_tbl(i).operand||' operand_perpqty:'||l_line_adj_tbl(i).operand_per_pqty);
723: oe_debug_pub.add(' list_line_type_code:'||l_line_adj_tbl(i).list_line_type_code||'applied_flag:'||l_line_adj_tbl(i).applied_flag); oe_debug_pub.add(' adjusted amount:'||l_line_adj_tbl(i).adjusted_amount);
724: oe_debug_pub.add(' adj_amt_pqty:'||l_line_adj_tbl(i).adjusted_amount_per_pqty);
725: oe_debug_pub.add(' retrobill_request_id: '||l_line_adj_tbl(i).retrobill_request_id);
726: end if;
727: --bug3738043 adding the following condition
720: if l_debug_level > 0 then
721: oe_debug_pub.add(' list_line_no: ' || l_line_adj_tbl(i).list_line_no || ' list_line_id: ' || l_line_adj_tbl(i).list_line_id);
722: oe_debug_pub.add(' operand queried:'||l_line_adj_tbl(i).operand||' operand_perpqty:'||l_line_adj_tbl(i).operand_per_pqty);
723: oe_debug_pub.add(' list_line_type_code:'||l_line_adj_tbl(i).list_line_type_code||'applied_flag:'||l_line_adj_tbl(i).applied_flag); oe_debug_pub.add(' adjusted amount:'||l_line_adj_tbl(i).adjusted_amount);
724: oe_debug_pub.add(' adj_amt_pqty:'||l_line_adj_tbl(i).adjusted_amount_per_pqty);
725: oe_debug_pub.add(' retrobill_request_id: '||l_line_adj_tbl(i).retrobill_request_id);
726: end if;
727: --bug3738043 adding the following condition
728: IF (l_retro_exists AND l_line_adj_tbl(i).retrobill_request_id IS NOT NULL) OR
721: oe_debug_pub.add(' list_line_no: ' || l_line_adj_tbl(i).list_line_no || ' list_line_id: ' || l_line_adj_tbl(i).list_line_id);
722: oe_debug_pub.add(' operand queried:'||l_line_adj_tbl(i).operand||' operand_perpqty:'||l_line_adj_tbl(i).operand_per_pqty);
723: oe_debug_pub.add(' list_line_type_code:'||l_line_adj_tbl(i).list_line_type_code||'applied_flag:'||l_line_adj_tbl(i).applied_flag); oe_debug_pub.add(' adjusted amount:'||l_line_adj_tbl(i).adjusted_amount);
724: oe_debug_pub.add(' adj_amt_pqty:'||l_line_adj_tbl(i).adjusted_amount_per_pqty);
725: oe_debug_pub.add(' retrobill_request_id: '||l_line_adj_tbl(i).retrobill_request_id);
726: end if;
727: --bug3738043 adding the following condition
728: IF (l_retro_exists AND l_line_adj_tbl(i).retrobill_request_id IS NOT NULL) OR
729: NOT l_retro_exists THEN
745: FETCH retro_inv_list_line_id into l_dummy;
746: IF retro_inv_list_line_id%NOTFOUND THEN
747: IF l_line_adj_tbl(i).LIST_LINE_TYPE_CODE IN ('DIS', 'SUR', 'PBH') THEN
748: IF l_debug_level > 0 THEN
749: oe_debug_pub.add('PVIPRANA: Inserting Invalid list_line_id ' || l_line_adj_tbl(i).list_line_id);
750: END IF;
751:
752: l_line_adj_tbl(i).line_id := p_line_id;
753: l_line_adj_tbl(i).header_id := p_header_id;
769: END LOOP;
770:
771: --bug3738043 start
772: IF l_debug_level > 0 THEN
773: oe_debug_pub.add('PVIPRANA: G_RETRO_PRICING_PHASE_COUNT is ' || G_RETRO_PRICING_PHASE_COUNT);
774: END IF;
775:
776: IF nvl(G_RETRO_PRICING_PHASE_COUNT,0) > 0 THEN
777: IF l_debug_level > 0 THEN
774: END IF;
775:
776: IF nvl(G_RETRO_PRICING_PHASE_COUNT,0) > 0 THEN
777: IF l_debug_level > 0 THEN
778: oe_debug_pub.add('PVIPRANA: deleting the new adjustments with retrobill_request_id NOT null');
779: END IF;
780:
781:
782: FOR retro_list_line_id IN retro_list_line_ids LOOP
797: IF NOT l_found THEN
798: l_new_price_adj_id_tbl(k) := retro_list_line_id.price_adjustment_id;
799: k := k+1;
800: IF l_debug_level > 0 THEN
801: oe_debug_pub.add('PVIPRANA: new list_line_id ' || retro_list_line_id.list_line_id);
802: END IF;
803: END IF;
804:
805: END LOOP;
805: END LOOP;
806:
807:
808: IF l_debug_level > 0 THEN
809: oe_debug_pub.add('PVIPRANA: Deleting ' || (k-1) || ' new adjustments');
810: END IF;
811:
812: IF l_new_price_adj_id_tbl.FIRST IS NOT NULL THEN
813:
820: END IF;
821: --bug3738043 end
822:
823: if l_debug_level > 0 then
824: oe_debug_pub.add('Leaving oe_retro_pvt.preprocess_adjustments');
825: end if;
826: --bug3738043
827: Exception
828: when others then
825: end if;
826: --bug3738043
827: Exception
828: when others then
829: oe_debug_pub.add('Exception in Preprocess Adjustments ' || SQLERRM);
830:
831: End;
832:
833: /***************************************************************
846: And line_category_code = 'RETURN';
847:
848:
849: l_qty Number:=0;
850: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
851:
852: Begin
853:
854: Open Return_Lines;
859: --all curr qty are eligible for returns
860: Return p_curr_ordered_qty;
861: Else
862: If l_debug_level > 0 Then
863: oe_debug_pub.add('Retro:Returned Qty:'||l_qty);
864: oe_debug_pub.add('Retro:Retrobillable Qty:'|| p_curr_ordered_qty - l_qty);
865: End If;
866: Return p_curr_ordered_qty - l_qty;
867: End If;
860: Return p_curr_ordered_qty;
861: Else
862: If l_debug_level > 0 Then
863: oe_debug_pub.add('Retro:Returned Qty:'||l_qty);
864: oe_debug_pub.add('Retro:Retrobillable Qty:'|| p_curr_ordered_qty - l_qty);
865: End If;
866: Return p_curr_ordered_qty - l_qty;
867: End If;
868:
867: End If;
868:
869: Exception
870: When NO_DATA_FOUND Then
871: oe_debug_pub.add('Retro:No returns against line_id:'||p_line_id);
872: Return p_curr_ordered_qty;
873: End;
874:
875: /*************************************************************
876: This procdures update oe_retrobill_requests table
877: *************************************************************/
878: Procedure Update_Row(p_retrobill_request_rec IN OE_RETROBILL_REQUESTS%ROWTYPE) AS
879: Begin
880: oe_debug_pub.add('Retro:Entering Update retrobill request,request_id:'||p_retrobill_request_rec.retrobill_request_id);
881: UPDATE OE_RETROBILL_REQUESTS
882: SET
883: NAME = p_retrobill_request_rec.name
884: , DESCRIPTION = p_retrobill_request_rec.description
898: , PROGRAM_ID = p_retrobill_request_rec.program_id
899: , PROGRAM_UPDATED_DATE= p_retrobill_request_rec.program_updated_date
900: Where retrobill_request_id = p_retrobill_request_rec.retrobill_request_id;
901:
902: oe_debug_pub.add('Retro:Leaving Update retrobill request:'||SQL%ROWCOUNT||' updated');
903: Exception
904: When Others Then
905: oe_debug_pub.add('Retro:Update_Row:'||SQLERRM);
906: End;
901:
902: oe_debug_pub.add('Retro:Leaving Update retrobill request:'||SQL%ROWCOUNT||' updated');
903: Exception
904: When Others Then
905: oe_debug_pub.add('Retro:Update_Row:'||SQLERRM);
906: End;
907:
908: /**************************************************************
909: This procedure calls process order api to create headers and lines
966: );
967:
968: x_created_header_id:=l_x_header_rec.header_id;
969:
970: oe_debug_pub.add('no. of OE messages :'||x_msg_count);
971: for k in 1 .. x_msg_count loop
972: x_msg_data := oe_msg_pub.get( p_msg_index => k,
973: p_encoded => 'F'
974: );
971: for k in 1 .. x_msg_count loop
972: x_msg_data := oe_msg_pub.get( p_msg_index => k,
973: p_encoded => 'F'
974: );
975: oe_debug_pub.add(substr(x_msg_data,1,255));
976: oe_debug_pub.add('Error msg: '||substr(x_msg_data,1,200));
977: end loop;
978:
979: fnd_msg_pub.count_and_get( p_encoded => 'F'
972: x_msg_data := oe_msg_pub.get( p_msg_index => k,
973: p_encoded => 'F'
974: );
975: oe_debug_pub.add(substr(x_msg_data,1,255));
976: oe_debug_pub.add('Error msg: '||substr(x_msg_data,1,200));
977: end loop;
978:
979: fnd_msg_pub.count_and_get( p_encoded => 'F'
980: , p_count => x_msg_count
978:
979: fnd_msg_pub.count_and_get( p_encoded => 'F'
980: , p_count => x_msg_count
981: , p_data => x_msg_data);
982: --oe_debug_pub.add('no. of FND messages :'||x_msg_count,1);
983: oe_debug_pub.add('no. of FND messages :'||x_msg_count);
984: for k in 1 .. x_msg_count loop
985: x_msg_data := fnd_msg_pub.get( p_msg_index => k,
986: p_encoded => 'F'
979: fnd_msg_pub.count_and_get( p_encoded => 'F'
980: , p_count => x_msg_count
981: , p_data => x_msg_data);
982: --oe_debug_pub.add('no. of FND messages :'||x_msg_count,1);
983: oe_debug_pub.add('no. of FND messages :'||x_msg_count);
984: for k in 1 .. x_msg_count loop
985: x_msg_data := fnd_msg_pub.get( p_msg_index => k,
986: p_encoded => 'F'
987: );
984: for k in 1 .. x_msg_count loop
985: x_msg_data := fnd_msg_pub.get( p_msg_index => k,
986: p_encoded => 'F'
987: );
988: oe_debug_pub.add('Error msg: '||substr(x_msg_data,1,200));
989: oe_debug_pub.add(substr(x_msg_data,1,255));
990: end loop;
991:
992: oe_debug_pub.add('header id created:'||l_x_header_rec.header_id);
985: x_msg_data := fnd_msg_pub.get( p_msg_index => k,
986: p_encoded => 'F'
987: );
988: oe_debug_pub.add('Error msg: '||substr(x_msg_data,1,200));
989: oe_debug_pub.add(substr(x_msg_data,1,255));
990: end loop;
991:
992: oe_debug_pub.add('header id created:'||l_x_header_rec.header_id);
993:
988: oe_debug_pub.add('Error msg: '||substr(x_msg_data,1,200));
989: oe_debug_pub.add(substr(x_msg_data,1,255));
990: end loop;
991:
992: oe_debug_pub.add('header id created:'||l_x_header_rec.header_id);
993:
994: End;
995:
996:
1002: p_retrobill_tbl In RETROBILL_TBL_TYPE,
1003: p_retrobill_request_rec In OE_RETROBILL_REQUESTS%ROWTYPE,
1004: x_line_rec Out NOCOPY OE_ORDER_PUB.LINE_REC_TYPE) AS
1005: l_retrobillable_qty NUMBER;
1006: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1007: stmt NUMBER:=7.1;
1008: Begin
1009: --Copy Original Attributes to retrobill line
1010: x_line_rec.org_id :=p_oline_rec.org_id ;
1188: --If user trying to retrobill more than he is eligible to, set the quantity to eligible qty
1189: /* If p_retrobill_tbl(p_oline_rec.plsql_tbl_index).retrobill_qty > l_retrobillable_qty Then
1190: x_line_rec.ordered_quantity:=l_retrobillable_qty;
1191: If l_debug_level > 0 Then
1192: oe_debug_pub.add('Retro:User tries to retrobill more than eligible. Retrobill Qty:'|| p_retrobill_tbl(p_oline_rec.plsql_tbl_index).retrobill_qty);
1193: End If;
1194: Else*/
1195: oe_debug_pub.add('Retro:plsql index'||p_oline_rec.plsql_tbl_index);
1196: x_line_rec.ordered_quantity:=p_retrobill_tbl(p_oline_rec.plsql_tbl_index).retrobill_qty;
1191: If l_debug_level > 0 Then
1192: oe_debug_pub.add('Retro:User tries to retrobill more than eligible. Retrobill Qty:'|| p_retrobill_tbl(p_oline_rec.plsql_tbl_index).retrobill_qty);
1193: End If;
1194: Else*/
1195: oe_debug_pub.add('Retro:plsql index'||p_oline_rec.plsql_tbl_index);
1196: x_line_rec.ordered_quantity:=p_retrobill_tbl(p_oline_rec.plsql_tbl_index).retrobill_qty;
1197: --End If;
1198:
1199: x_line_rec.retrobill_request_id:=p_retrobill_request_rec.retrobill_request_id;
1204: --x_line_rec.Source_Document_shipment_ref:=p_retrobill_request_rec.retrobill_request_id;
1205: x_line_rec. ORIG_SYS_SHIPMENT_REF:=p_retrobill_request_rec.retrobill_request_id;
1206: stmt:=7.52;
1207: If l_debug_level > 0 Then
1208: oe_debug_pub.add('Retro:invoiced_quantity:'|| p_oline_rec.invoiced_quantity);
1209: End If;
1210:
1211: If p_oline_rec.invoiced_quantity > 0 Then
1212: x_line_rec.credit_invoice_line_id:=Get_Credit_Invoice_Line_Id(p_oline_rec.order_number
1222: --We try to copy over the tax code and date, but if it fails
1223: --in validation, we will need to ask the system to default the tax code and date
1224: --if we default, we will to tell users about this.
1225: IF NOT IS_TAX_CODE_VALID(p_header_id=>x_line_rec.header_id,p_line_id=>x_line_rec.line_id,p_tax_code=>x_line_rec.tax_code,p_tax_date=>x_line_rec.tax_date, p_org_id=>x_line_rec.org_id) THEN
1226: oe_debug_pub.add('Retro:Old tax code is no longer valid, redaulting a new one');
1227: x_line_rec.tax_code:=FND_API.G_MISS_CHAR;
1228: stmt:=7.61;
1229: x_line_rec.tax_rate:=FND_API.G_MISS_NUM;
1230: stmt:=7.62;
1247:
1248: -- x_line_rec.ship_to_org_id:=FND_API.G_MISS_NUM; -- Commented for bug 5612169
1249:
1250: If l_debug_level > 0 Then
1251: oe_debug_pub.add('Retro:leaving prepare line');
1252: End If;
1253:
1254: Exception
1255: When Others then
1252: End If;
1253:
1254: Exception
1255: When Others then
1256: oe_debug_pub.add('Exception occured at statement:'||stmt||':'||SQLERRM);
1257: raise;
1258: End;
1259:
1260: PROCEDURE Prepare_Header(p_cust_po_number IN VARCHAR2, -- Bug# 6603714
1292: x_header_rec.flow_status_code :='ENTERED';
1293: x_header_rec.order_source_id :=G_RETROBILL_ORDER_SOURCE_ID;
1294: x_header_rec.orig_sys_document_ref :=p_retrobill_request_rec.retrobill_request_id;
1295: x_header_rec.cust_po_number :=p_cust_po_number; -- Bug# 6603714
1296: oe_debug_pub.add('x_header_rec.cust_po_number'||x_header_rec.cust_po_number);
1297:
1298: If p_retrobill_request_rec.order_type_id IS NULL Then
1299: null;
1300: --get order type id from sys parameter
1317: );
1318:
1319: IF l_pl_tbl.First IS NOT NULL THEN
1320: l_valid_price_list_id := l_pl_tbl(l_pl_tbl.First).price_list_id;
1321: oe_debug_pub.add('RETRO:Valid Price List to be defaulted is:'||l_pl_tbl(l_pl_tbl.First).price_list_id);
1322: ELSE
1323: oe_debug_pub.add('RETRO:ERROR:UNABLE TO DEFAULT A VALID HEADER PRICE LIST');
1324: END IF;
1325: x_header_rec.price_list_id:=l_valid_price_list_id;
1319: IF l_pl_tbl.First IS NOT NULL THEN
1320: l_valid_price_list_id := l_pl_tbl(l_pl_tbl.First).price_list_id;
1321: oe_debug_pub.add('RETRO:Valid Price List to be defaulted is:'||l_pl_tbl(l_pl_tbl.First).price_list_id);
1322: ELSE
1323: oe_debug_pub.add('RETRO:ERROR:UNABLE TO DEFAULT A VALID HEADER PRICE LIST');
1324: END IF;
1325: x_header_rec.price_list_id:=l_valid_price_list_id;
1326: END IF; --end if for g_int_use_any_valid_pl
1327:
1324: END IF;
1325: x_header_rec.price_list_id:=l_valid_price_list_id;
1326: END IF; --end if for g_int_use_any_valid_pl
1327:
1328: oe_debug_pub.add('Retro:Order Type id:'||x_header_rec.order_type_id);
1329: oe_debug_pub.add('Retro:sold to org id:'||x_header_rec.sold_to_org_id);
1330: oe_debug_pub.add('Retro:currency:'||x_header_rec.transactional_curr_code);
1331: End;
1332:
1325: x_header_rec.price_list_id:=l_valid_price_list_id;
1326: END IF; --end if for g_int_use_any_valid_pl
1327:
1328: oe_debug_pub.add('Retro:Order Type id:'||x_header_rec.order_type_id);
1329: oe_debug_pub.add('Retro:sold to org id:'||x_header_rec.sold_to_org_id);
1330: oe_debug_pub.add('Retro:currency:'||x_header_rec.transactional_curr_code);
1331: End;
1332:
1333: PROCEDURE Insert_Id(p_retrobill_tbl IN RETROBILL_TBL_TYPE) As
1326: END IF; --end if for g_int_use_any_valid_pl
1327:
1328: oe_debug_pub.add('Retro:Order Type id:'||x_header_rec.order_type_id);
1329: oe_debug_pub.add('Retro:sold to org id:'||x_header_rec.sold_to_org_id);
1330: oe_debug_pub.add('Retro:currency:'||x_header_rec.transactional_curr_code);
1331: End;
1332:
1333: PROCEDURE Insert_Id(p_retrobill_tbl IN RETROBILL_TBL_TYPE) As
1334: l_id_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
1356: END IF;
1357:
1358: Exception
1359: When Others Then
1360: oe_debug_pub.add('Execption occured in Oe_Retrobill_Pvt.Insert_Id:'||SQLERRM);
1361: Raise;
1362: End;
1363:
1364: PROCEDURE INSERT_RETROBILL_REQUEST(p_retrobill_request_rec IN OE_RETROBILL_REQUESTS%ROWTYPE) AS
1403: p_retrobill_request_rec.program_updated_date);
1404:
1405: Exception
1406: When Others Then
1407: oe_debug_pub.add('RETRO error:'||SQLERRM);
1408: oe_debug_pub.add('RETRO:INSERT_RETROBILL_REQUEST:'||SQLERRM);
1409: End;
1410:
1411: /**************************************************************
1404:
1405: Exception
1406: When Others Then
1407: oe_debug_pub.add('RETRO error:'||SQLERRM);
1408: oe_debug_pub.add('RETRO:INSERT_RETROBILL_REQUEST:'||SQLERRM);
1409: End;
1410:
1411: /**************************************************************
1412: Called by OE_ORDER_PRICE_PVT, Update retrobill lines
1487: l_debit Varchar2(1);
1488: j Number:=0;
1489: l_control_rec OE_GLOBALS.Control_Rec_Type;
1490: l_return_status Varchar2(15);
1491: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1492: --skubendr{
1493: l_inventory_item_id_previous NUMBER;
1494: l_unique_item BOOLEAN := TRUE;
1495: l_sold_to_org_id_previous NUMBER;
1497: l_current_line_deleted VARCHAR2(1);
1498: --skubendr}
1499: BEGIN
1500:
1501: oe_debug_pub.add('Entering update retrobill lines');
1502: --GSCC Not initializing during declaration
1503: l_debit := 'N';
1504: l_inventory_item_id_previous := g_retrobill_request_rec.inventory_item_id;
1505: l_sold_to_org_id_previous := g_retrobill_request_rec.sold_to_org_id;
1522: open get_original_price(I.orig_sys_line_ref);
1523: fetch get_original_price into l_orig_usp,l_orig_ulp;
1524: close get_original_price;
1525: IF l_debug_level > 0 THEN
1526: Oe_Debug_Pub.Add('Retro:orig_usp:'||l_orig_usp||',orig_ulp:'||l_orig_ulp);
1527: END IF;
1528: l_retrobill_selling_price := l_orig_usp + l_usp_sum - I.NEW_UNIT_SELLING_PRICE;
1529: l_retrobill_list_price := l_orig_ulp + l_ulp_sum - I.NEW_UNIT_LIST_PRICE;
1530: END IF;
1529: l_retrobill_list_price := l_orig_ulp + l_ulp_sum - I.NEW_UNIT_LIST_PRICE;
1530: END IF;
1531:
1532: IF l_debug_level > 0 THEN
1533: oe_debug_pub.add('retro-old usp:'||I.OLD_UNIT_SELLING_PRICE||' new usp:'||I.NEW_UNIT_SELLING_PRICE);
1534: oe_debug_pub.add('retro:usp sum'||l_usp_sum);
1535: oe_debug_pub.add('retro:retrobill selling_price:'|| l_retrobill_selling_price);
1536:
1537: oe_debug_pub.add('retro-old ulp:'||I.OLD_UNIT_LIST_PRICE||' new ULP:'||I.NEW_UNIT_LIST_PRICE);
1530: END IF;
1531:
1532: IF l_debug_level > 0 THEN
1533: oe_debug_pub.add('retro-old usp:'||I.OLD_UNIT_SELLING_PRICE||' new usp:'||I.NEW_UNIT_SELLING_PRICE);
1534: oe_debug_pub.add('retro:usp sum'||l_usp_sum);
1535: oe_debug_pub.add('retro:retrobill selling_price:'|| l_retrobill_selling_price);
1536:
1537: oe_debug_pub.add('retro-old ulp:'||I.OLD_UNIT_LIST_PRICE||' new ULP:'||I.NEW_UNIT_LIST_PRICE);
1538: oe_debug_pub.add('retro:ulp sum'||l_ulp_sum);
1531:
1532: IF l_debug_level > 0 THEN
1533: oe_debug_pub.add('retro-old usp:'||I.OLD_UNIT_SELLING_PRICE||' new usp:'||I.NEW_UNIT_SELLING_PRICE);
1534: oe_debug_pub.add('retro:usp sum'||l_usp_sum);
1535: oe_debug_pub.add('retro:retrobill selling_price:'|| l_retrobill_selling_price);
1536:
1537: oe_debug_pub.add('retro-old ulp:'||I.OLD_UNIT_LIST_PRICE||' new ULP:'||I.NEW_UNIT_LIST_PRICE);
1538: oe_debug_pub.add('retro:ulp sum'||l_ulp_sum);
1539: oe_debug_pub.add('retro:retrobill list_price:'|| l_retrobill_list_price);
1533: oe_debug_pub.add('retro-old usp:'||I.OLD_UNIT_SELLING_PRICE||' new usp:'||I.NEW_UNIT_SELLING_PRICE);
1534: oe_debug_pub.add('retro:usp sum'||l_usp_sum);
1535: oe_debug_pub.add('retro:retrobill selling_price:'|| l_retrobill_selling_price);
1536:
1537: oe_debug_pub.add('retro-old ulp:'||I.OLD_UNIT_LIST_PRICE||' new ULP:'||I.NEW_UNIT_LIST_PRICE);
1538: oe_debug_pub.add('retro:ulp sum'||l_ulp_sum);
1539: oe_debug_pub.add('retro:retrobill list_price:'|| l_retrobill_list_price);
1540: END IF;
1541:
1534: oe_debug_pub.add('retro:usp sum'||l_usp_sum);
1535: oe_debug_pub.add('retro:retrobill selling_price:'|| l_retrobill_selling_price);
1536:
1537: oe_debug_pub.add('retro-old ulp:'||I.OLD_UNIT_LIST_PRICE||' new ULP:'||I.NEW_UNIT_LIST_PRICE);
1538: oe_debug_pub.add('retro:ulp sum'||l_ulp_sum);
1539: oe_debug_pub.add('retro:retrobill list_price:'|| l_retrobill_list_price);
1540: END IF;
1541:
1542: --Handle the line as a 'return' because new price > old price + previously retrobilled amount
1535: oe_debug_pub.add('retro:retrobill selling_price:'|| l_retrobill_selling_price);
1536:
1537: oe_debug_pub.add('retro-old ulp:'||I.OLD_UNIT_LIST_PRICE||' new ULP:'||I.NEW_UNIT_LIST_PRICE);
1538: oe_debug_pub.add('retro:ulp sum'||l_ulp_sum);
1539: oe_debug_pub.add('retro:retrobill list_price:'|| l_retrobill_list_price);
1540: END IF;
1541:
1542: --Handle the line as a 'return' because new price > old price + previously retrobilled amount
1543: --bug3654144
1543: --bug3654144
1544: IF l_retrobill_list_price > 0 OR
1545: (l_retrobill_list_price = 0 AND
1546: l_retrobill_selling_price > 0) THEN
1547: oe_debug_pub.add('Retro: New price is lower giving credit');
1548: UPDATE OE_ORDER_LINES_all l
1549: SET UNIT_SELLING_PRICE =l_retrobill_selling_price
1550: ,UNIT_LIST_PRICE =l_retrobill_list_price
1551: ,UNIT_SELLING_PRICE_PER_PQTY=I.UNIT_SELLING_PRICE_PER_PQTY
1560: ,CALCULATE_PRICE_FLAG ='N'
1561: ,LOCK_CONTROL =I.LOCK_CONTROL
1562: WHERE l.line_id = I.line_id;
1563: --What about update global??? Need to think about it...
1564: oe_debug_pub.add('retro:updated row number:'|| SQL%ROWCOUNT||'line_id:'|| I.line_id);
1565:
1566: --Handle the line as a 'buy' (invoice customer) if new price is higher
1567: --This case will be a little complex because we are changing line_category_code
1568: --by doing so, many line attributes will need to be redefaulted.
1570: --bug3654144
1571: ELSIF l_retrobill_list_price < 0 OR
1572: (l_retrobill_list_price = 0 AND
1573: l_retrobill_selling_price < 0) THEN
1574: oe_debug_pub.add('Retro: New price is higher charging more');
1575:
1576: l_line_rec:=Oe_Line_Util.Query_Row(I.line_id);
1577: l_old_line_rec:=l_line_rec;
1578: l_line_rec.unit_selling_price :=-1 * l_retrobill_selling_price; --bug3654144
1599:
1600: l_line_rec.price_list_id :=I.PRICE_LIST_ID;
1601: l_old_line_rec.price_list_id :=I.PRICE_LIST_ID;
1602:
1603: oe_debug_pub.add('Price list id after setting it back:' || l_line_rec.price_list_id);
1604: oe_debug_pub.add('Old price list id:'||l_old_line_rec.price_list_id);
1605:
1606: l_old_line_tbl(j):=l_old_line_rec;
1607: l_line_tbl(j):=l_line_rec;
1600: l_line_rec.price_list_id :=I.PRICE_LIST_ID;
1601: l_old_line_rec.price_list_id :=I.PRICE_LIST_ID;
1602:
1603: oe_debug_pub.add('Price list id after setting it back:' || l_line_rec.price_list_id);
1604: oe_debug_pub.add('Old price list id:'||l_old_line_rec.price_list_id);
1605:
1606: l_old_line_tbl(j):=l_old_line_rec;
1607: l_line_tbl(j):=l_line_rec;
1608: ELSE
1609: --If the selling price is the same then we remove the created line
1610: --skubendr{
1611: l_current_line_deleted := 'Y';
1612: --skubendr}
1613: oe_debug_pub.add('Retro:No price difference,delete line_id:'||I.line_id);
1614:
1615: l_deleted_line_hdr := I.header_id;
1616:
1617: IF(I.line_number =1) THEN
1671: --deletion occured, need to resequence line number.
1672: END IF;
1673:
1674: IF l_deleted_line_hdr IS NOT NULL THEN
1675: oe_debug_pub.add('Retro:before line_num');
1676: FOR N IN line_number(l_deleted_line_hdr) LOOP
1677: l_line_num_tbl(k):= k;
1678: l_line_id_tbl(k):= N.LINE_ID;
1679: IF(G_FIRST_LINE_DELETED='Y' and k=1) THEN
1682: WHERE line_id=N.LINE_ID;
1683: END IF;
1684: k:=k+1;
1685: END LOOP;
1686: oe_debug_pub.add('Retro:before update line_num');
1687: IF l_line_id_tbl.FIRST IS NOT NULL THEN
1688: FORALL K IN l_line_id_tbl.FIRST..l_line_id_tbl.LAST
1689: UPDATE OE_ORDER_LINES_ALL
1690: SET LINE_NUMBER = l_line_num_tbl(K)
1689: UPDATE OE_ORDER_LINES_ALL
1690: SET LINE_NUMBER = l_line_num_tbl(K)
1691: WHERE LINE_ID = l_line_id_tbl(K);
1692: END IF;
1693: oe_debug_pub.add('Retro:after update line_num'||SQL%ROWCOUNT);
1694: END IF;
1695: --skubendr{
1696: IF(l_unique_item = FALSE) THEN
1697: g_retrobill_request_rec.inventory_item_id := NULL;
1698: END IF;
1699: IF(l_unique_customer = FALSE) THEN
1700: g_retrobill_request_rec.sold_to_org_id := NULL;
1701: END IF;
1702: oe_debug_pub.add('Customer id before inserting'||g_retrobill_request_rec.sold_to_org_id);
1703: oe_debug_pub.add('Inventory item id before inserting'||g_retrobill_request_rec.inventory_item_id);
1704: --skubendr}
1705: End;
1706:
1699: IF(l_unique_customer = FALSE) THEN
1700: g_retrobill_request_rec.sold_to_org_id := NULL;
1701: END IF;
1702: oe_debug_pub.add('Customer id before inserting'||g_retrobill_request_rec.sold_to_org_id);
1703: oe_debug_pub.add('Inventory item id before inserting'||g_retrobill_request_rec.inventory_item_id);
1704: --skubendr}
1705: End;
1706:
1707: PROCEDURE Insert_diff_Adj As
1704: --skubendr}
1705: End;
1706:
1707: PROCEDURE Insert_diff_Adj As
1708: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1709:
1710: cursor l_debug_cur IS
1711: select operand,operand_per_pqty,list_line_id,applied_flag,arithmetic_operator,updated_flag,adjusted_amount,adjusted_amount_per_pqty
1712: from oe_price_adjustments
1716:
1717: IF l_debug_level > 2 THEN
1718: --will be very slow if debug on fts will happen
1719: for k in l_debug_cur loop
1720: oe_debug_pub.add('operand:'||k.operand||'operand_per_pqty:'||k.operand_per_pqty||'list_line_id:'||k.list_line_id||
1721: 'applied flag:'||k.applied_flag);
1722: oe_debug_pub.add('arithmetic_operator:'||k.arithmetic_operator||'updated_flag:'||k.updated_flag||'adjusted_amount:'||k.adjusted_amount||'adjusted_amount_per_pqty:'||k.adjusted_amount_per_pqty||' retrobill id:'||G_CURRENT_RETROBILL_REQUEST_ID);
1723:
1724: end loop;
1718: --will be very slow if debug on fts will happen
1719: for k in l_debug_cur loop
1720: oe_debug_pub.add('operand:'||k.operand||'operand_per_pqty:'||k.operand_per_pqty||'list_line_id:'||k.list_line_id||
1721: 'applied flag:'||k.applied_flag);
1722: oe_debug_pub.add('arithmetic_operator:'||k.arithmetic_operator||'updated_flag:'||k.updated_flag||'adjusted_amount:'||k.adjusted_amount||'adjusted_amount_per_pqty:'||k.adjusted_amount_per_pqty||' retrobill id:'||G_CURRENT_RETROBILL_REQUEST_ID);
1723:
1724: end loop;
1725: END IF;
1726:
1959: AND ldets.list_line_id = oepj.list_line_id
1960: AND oepj.retrobill_request_id = G_CURRENT_RETROBILL_REQUEST_ID
1961: );
1962:
1963: oe_debug_pub.add('Retro:'||SQL%ROWCOUNT||' inserted, Retrobill id:'||G_CURRENT_RETROBILL_REQUEST_ID);
1964: Exception
1965: WHEN OTHERS THEN
1966: IF l_debug_level > 0 THEN
1967: oe_debug_pub.add('RETRO:ERROR in creating offset adjustments'||sqlerrm);
1963: oe_debug_pub.add('Retro:'||SQL%ROWCOUNT||' inserted, Retrobill id:'||G_CURRENT_RETROBILL_REQUEST_ID);
1964: Exception
1965: WHEN OTHERS THEN
1966: IF l_debug_level > 0 THEN
1967: oe_debug_pub.add('RETRO:ERROR in creating offset adjustments'||sqlerrm);
1968: END IF;
1969: Raise FND_API.G_EXC_ERROR;
1970: END Insert_diff_Adj;
1971:
2087: --bug3417428
2088: --and ldets2.line_index = adj.header_id+nvl(adj.line_id,0))
2089: and lines2.line_id=adj.line_id)
2090: and adj.retrobill_request_id = G_CURRENT_RETROBILL_REQUEST_ID;
2091: oe_debug_pub.add('Retro:'||SQL%ROWCOUNT||' updated');
2092: Exception
2093: WHEN OTHERS THEN
2094:
2095: oe_debug_pub.add('Retro:ERROR in updating adjustments'||sqlerrm);
2091: oe_debug_pub.add('Retro:'||SQL%ROWCOUNT||' updated');
2092: Exception
2093: WHEN OTHERS THEN
2094:
2095: oe_debug_pub.add('Retro:ERROR in updating adjustments'||sqlerrm);
2096:
2097: --Raise FND_API.G_EXC_ERROR;
2098: End;
2099:
2098: End;
2099:
2100: --bug3654144 Adding a new procedure to insert adjustment lines corresponding to new modifiers returns by pricing engine.
2101: PROCEDURE Insert_New_Adj As
2102: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2103: Begin
2104: INSERT INTO OE_PRICE_ADJUSTMENTS
2105: ( PRICE_ADJUSTMENT_ID
2106: , CREATION_DATE
2331: -- AND (l_booked_flag = 'N' or ldets.list_line_type_code<>'IUE')
2332: );
2333:
2334: IF l_debug_level > 0 THEN
2335: oe_debug_pub.add( 'pviprana INSERTED '||SQL%ROWCOUNT||' NEW ADJUSTMENTS' ) ;
2336: END IF;
2337:
2338: INSERT INTO OE_PRICE_ADJ_ASSOCS
2339: ( PRICE_ADJUSTMENT_ID
2391:
2392:
2393:
2394: IF l_debug_level > 0 THEN
2395: oe_debug_pub.add( 'pviprana INSERTED '||SQL%ROWCOUNT||' NEW PRICE ADJ ASSOCS' , 3 ) ;
2396: END IF;
2397:
2398:
2399: INSERT INTO OE_PRICE_ADJUSTMENTS
2625: -- AND (l_booked_flag = 'N' or ldets.list_line_type_code<>'IUE')
2626: );
2627:
2628: IF l_debug_level > 0 THEN
2629: oe_debug_pub.add( 'pviprana INSERTED '||SQL%ROWCOUNT||' new DIFF ADJUSTMENTS' ) ;
2630: END IF;
2631:
2632:
2633: Exception
2634: WHEN NO_DATA_FOUND THEN
2635: null;
2636: WHEN OTHERS THEN
2637: IF l_debug_level > 0 THEN
2638: oe_debug_pub.add('RETRO:ERROR in creating new offset adjustments'||sqlerrm);
2639: END IF;
2640: Raise FND_API.G_EXC_ERROR;
2641: END Insert_New_Adj;
2642:
2663: AND nvl(automatic_flag,'N') = 'Y'
2664: AND nvl(list_line_type_code,'TAX') NOT IN ('TAX', 'FREIGHT_CHARGE')
2665: ORDER BY line_id;
2666:
2667: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2668: l_price_adjustment_id NUMBER;
2669: l_updated_inv_adj_count PLS_INTEGER := 0;
2670: l_last_line_id NUMBER;
2671: l_line_category_code VARCHAR2(30);
2674: --GSCC not initializing during declaration
2675: l_last_line_id := -1;
2676: FOR invalid_price_adj IN invalid_price_adjs LOOP
2677: IF l_debug_level > 0 THEN
2678: oe_debug_pub.add('pviprana: Invalid adj list line id : '||invalid_price_adj.list_line_id);
2679: oe_debug_pub.add('pviprana: Invalid adj price_adjustment_id : '||invalid_price_adj.price_adjustment_id);
2680: oe_debug_pub.add('pviprana: Invalid adj list_line_type_code : '||invalid_price_adj.list_line_type_code);
2681: END IF;
2682:
2675: l_last_line_id := -1;
2676: FOR invalid_price_adj IN invalid_price_adjs LOOP
2677: IF l_debug_level > 0 THEN
2678: oe_debug_pub.add('pviprana: Invalid adj list line id : '||invalid_price_adj.list_line_id);
2679: oe_debug_pub.add('pviprana: Invalid adj price_adjustment_id : '||invalid_price_adj.price_adjustment_id);
2680: oe_debug_pub.add('pviprana: Invalid adj list_line_type_code : '||invalid_price_adj.list_line_type_code);
2681: END IF;
2682:
2683: IF l_last_line_id <> invalid_price_adj.line_id THEN
2676: FOR invalid_price_adj IN invalid_price_adjs LOOP
2677: IF l_debug_level > 0 THEN
2678: oe_debug_pub.add('pviprana: Invalid adj list line id : '||invalid_price_adj.list_line_id);
2679: oe_debug_pub.add('pviprana: Invalid adj price_adjustment_id : '||invalid_price_adj.price_adjustment_id);
2680: oe_debug_pub.add('pviprana: Invalid adj list_line_type_code : '||invalid_price_adj.list_line_type_code);
2681: END IF;
2682:
2683: IF l_last_line_id <> invalid_price_adj.line_id THEN
2684: BEGIN
2687: WHERE line_id = invalid_price_adj.line_id;
2688: EXCEPTION
2689: WHEN OTHERS THEN
2690: IF l_debug_level > 0 THEN
2691: oe_debug_pub.add('RETRO:ERROR in creating offset adjustments'||sqlerrm);
2692: END IF;
2693: Raise FND_API.G_EXC_ERROR;
2694: END;
2695: l_last_line_id := invalid_price_adj.line_id;
2735:
2736: END LOOP;
2737:
2738: IF l_debug_level > 0 THEN
2739: oe_debug_pub.add( 'pviprana UPDATED '|| l_updated_inv_adj_count ||' INVALID DIFF ADJUSTMENTS' ) ;
2740: END IF;
2741:
2742: EXCEPTION
2743: WHEN NO_DATA_FOUND THEN
2740: END IF;
2741:
2742: EXCEPTION
2743: WHEN NO_DATA_FOUND THEN
2744: oe_debug_pub.add('pviprana: Exception: NO_DATA_FOUND');
2745: WHEN OTHERS THEN
2746: oe_debug_pub.add('pviprana: Exception: '||SQLERRM);
2747:
2748: END;
2742: EXCEPTION
2743: WHEN NO_DATA_FOUND THEN
2744: oe_debug_pub.add('pviprana: Exception: NO_DATA_FOUND');
2745: WHEN OTHERS THEN
2746: oe_debug_pub.add('pviprana: Exception: '||SQLERRM);
2747:
2748: END;
2749:
2750: --RT{
2752: --pack I code about qp structure.
2753: Procedure Process_Retrobill_Adjustments(p_operation IN VARCHAR2) As
2754:
2755: Begin
2756: oe_debug_pub.add('Retro:Entering Process_Retrobill_Adjustments');
2757: --case 1, Retrobill does not have adjustment phases, only list price phase.
2758: --ideally in this case only adjustment amount(% discount) could have changed,operand should be the same
2759:
2760: --insert the offset adjustments, this is always needed in both cases
2770:
2771: --bug3654144
2772: Insert_New_Adj;
2773:
2774: oe_debug_pub.add('Retro:Leaving Process_Retrobill_Adjustments');
2775:
2776: End Process_Retrobill_Adjustments;
2777: --RT}
2778:
2803: l_line_tbl OE_ORDER_PUB.LINE_TBL_TYPE;
2804: l_return_status VARCHAR2(15);
2805: l_msg_count NUMBER;
2806: l_msg_data VARCHAR2(2000);
2807: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2808: Cursor unique_header_id IS
2809: SELECT DISTINCT KEY_ID Header_Id
2810: FROM OM_ID_LIST_TMP;
2811:
2830:
2831: l_header_id_tbl(n_index):=p_retrobill_tbl(i).retrobill_header_id;
2832:
2833: /*IF l_debug_level > 0 THEN
2834: oe_debug_pub.add('Retro:retrobill_header_id:'||nvl(p_retrobill_tbl(i).retrobill_header_id,'NULL'));
2835: END IF;*/
2836:
2837: IF nvl(p_retrobill_tbl(i).operation,'NULL') = 'UPDATE' THEN
2838: l_line_id_tbl(upd_index):=p_retrobill_tbl(i).retrobill_line_id;
2847:
2848: --We need to delete first, otherwise, we will be unable to delete booked records.
2849: i:=l_delete_tbl.first;
2850: WHILE i IS NOT NULL LOOP
2851: oe_debug_pub.add('Retro:Operation DELETE:deleting line id:'||l_delete_tbl(i).retrobill_line_id);
2852: Oe_Line_Util.Delete_Row(p_line_id=>l_delete_tbl(i).retrobill_line_id);
2853: i:=l_delete_tbl.next(i);
2854: END LOOP;
2855:
2869: l_arrange_line_num_tbl(j):= j;
2870: l_arrange_line_id_tbl(j):= N.LINE_ID;
2871: j:=j+1;
2872: END LOOP;
2873: oe_debug_pub.add('Retro:before update line_num');
2874: IF l_arrange_line_id_tbl.FIRST IS NOT NULL THEN
2875: FORALL i IN l_arrange_line_id_tbl.FIRST..l_arrange_line_id_tbl.LAST
2876: UPDATE OE_ORDER_LINES_ALL
2877: SET LINE_NUMBER = l_arrange_line_num_tbl(i)
2876: UPDATE OE_ORDER_LINES_ALL
2877: SET LINE_NUMBER = l_arrange_line_num_tbl(i)
2878: WHERE LINE_ID = l_arrange_line_id_tbl(i);
2879: END IF;
2880: oe_debug_pub.add('Retro:after update line_num');
2881: END LOOP;
2882:
2883: --handle mass update by joing to OM_ID_LIST_TMP
2884: DELETE FROM OM_ID_LIST_TMP;
2886: IF l_line_id_tbl.FIRST IS NOT NULL THEN
2887: FORALL j IN l_line_id_tbl.FIRST..l_line_id_tbl.LAST
2888: INSERT INTO OM_ID_LIST_TMP(KEY_ID,VALUE)
2889: VALUES (l_line_id_tbl(j),l_retrobill_qty_tbl(j));
2890: oe_debug_pub.add('Retro:execution mode='||p_execution_mode);
2891:
2892: --pricing quantity derivation might be wrong need to get back to this!!!
2893: UPDATE OE_ORDER_LINES_ALL oeol
2894: SET (ordered_quantity,
2914:
2915: --execute means book the orders
2916: IF p_execution_mode = 'EXECUTE' THEN
2917: FOR k in unique_header_id LOOP
2918: oe_debug_pub.add('Booking header id:'||k.header_id);
2919: -- Progress the workflow so that booking process is kicked off.
2920: -- This call should come back with a message OE_ORDER_BOOKED
2921: -- if booking completed successfully and if booking was deferred,
2922: -- message OE_ORDER_BOOK_DEFERRED is added to the stack.
2965:
2966: --bug3738043 end
2967:
2968: FOR k in unique_header_id LOOP
2969: oe_debug_pub.add('Retro:Repricing Order: header id:'||k.header_id);
2970: --cal price flag from retrobilling lines are 'N', set them to 'Y' before
2971: --reprice and then set them back to 'N'
2972: UPDATE OE_ORDER_LINES_ALL
2973: SET calculate_price_flag='Y'
2978: ,p_Request_Type_code=> 'ONT'
2979: ,p_Control_rec => l_Price_Control_Rec
2980: ,p_write_to_db => TRUE
2981: ,x_line_Tbl => l_Line_Tbl);
2982: oe_debug_pub.add('Retro:return status from price_line:'||l_Return_Status);
2983:
2984: --bug5003256 start
2985: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2986: x_error_count := x_error_count + l_line_tbl.count;
3006: l_return_status VARCHAR2(1);
3007: l_msg_count NUMBER;
3008: l_msg_data VARCHAR2(5000);
3009: BEGIN
3010: oe_debug_pub.add('Entering procedure Delete_Order');
3011: --bug5003256
3012: x_header_deleted := FALSE;
3013: select count(*) into l_line_count from oe_order_lines_all where header_id=p_header_id;
3014: IF(l_line_count =0) THEN
3017: x_header_deleted := TRUE;
3018: END IF;
3019: EXCEPTION
3020: WHEN OTHERS THEN
3021: oe_debug_pub.add('Exception occured in Delete_Order:'||SQLERRM);
3022: END;
3023:
3024: PROCEDURE Process_Retrobill_Request
3025: (p_retrobill_request_rec IN OE_RETROBILL_REQUESTS%ROWTYPE
3049: lx_header_id NUMBER;
3050: l_to_be_exe_hdr_id_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
3051: l_msg_count NUMBER;
3052: l_msg_data VARCHAR2(5000);
3053: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3054: lx_return_status VARCHAR2(15);
3055: l_msg VARCHAR2(2000);
3056: --bug5003256 start
3057: l_book_line_count NUMBER;
3115: ELSE
3116: x_error_count := x_error_count + l_line_tbl.count;
3117: END IF;
3118:
3119: oe_debug_pub.add('Praveen: Price List Id' || G_FIRST_LINE_PRICE_LIST_ID);
3120: oe_debug_pub.add('Praveen: First Line Deleted' || G_FIRST_LINE_DELETED);
3121:
3122: BEGIN
3123:
3116: x_error_count := x_error_count + l_line_tbl.count;
3117: END IF;
3118:
3119: oe_debug_pub.add('Praveen: Price List Id' || G_FIRST_LINE_PRICE_LIST_ID);
3120: oe_debug_pub.add('Praveen: First Line Deleted' || G_FIRST_LINE_DELETED);
3121:
3122: BEGIN
3123:
3124: SELECT price_list_id INTO l_header_price_list_id
3150: l_to_be_exe_hdr_id_tbl(i):=lx_header_id;
3151: i:=i+1;
3152: End If;
3153:
3154: oe_debug_pub.add('Retro:Return status after calling process order:'||lx_return_status);
3155:
3156: --clear header, reset line tbl records
3157: l_line_tbl.delete;
3158: l_header_rec:=null;
3202: x_error_count := x_error_count + l_line_tbl.count;
3203:
3204: END IF;
3205:
3206: oe_debug_pub.add('Praveen: Price List Id' || G_FIRST_LINE_PRICE_LIST_ID);
3207: oe_debug_pub.add('Praveen: First Line Deleted' || G_FIRST_LINE_DELETED);
3208:
3209: If lx_return_status = FND_API.G_RET_STS_SUCCESS AND
3210: NOT l_header_deleted Then --bug5003256
3203:
3204: END IF;
3205:
3206: oe_debug_pub.add('Praveen: Price List Id' || G_FIRST_LINE_PRICE_LIST_ID);
3207: oe_debug_pub.add('Praveen: First Line Deleted' || G_FIRST_LINE_DELETED);
3208:
3209: If lx_return_status = FND_API.G_RET_STS_SUCCESS AND
3210: NOT l_header_deleted Then --bug5003256
3211: l_to_be_exe_hdr_id_tbl(i):=lx_header_id;
3249: , x_return_status => lx_return_status
3250: , x_msg_count => l_msg_count
3251: , x_msg_data => l_msg_data);
3252: IF l_debug_level > 0 THEN
3253: oe_debug_pub.add('Retro:Direct Execution without preview');
3254: --skubendr{
3255: IF (lx_return_status<>FND_API.G_RET_STS_SUCCESS) THEN
3256: Display_Message(l_msg_count,l_msg_data);
3257: END IF;
3274: IF(g_retrobill_request_rec.retrobill_request_id = l_retrobill_request_rec.retrobill_request_id) THEN
3275: l_retrobill_request_rec.sold_to_org_id:=g_retrobill_request_rec.sold_to_org_id;
3276: l_retrobill_request_rec.inventory_item_id:=g_retrobill_request_rec.inventory_item_id;
3277: END IF;
3278: oe_debug_pub.add('sold_to_org_id'||l_retrobill_request_rec.sold_to_org_id);
3279: oe_debug_pub.add('inventory_item_id'||l_retrobill_request_rec.inventory_item_id);
3280: Insert_Retrobill_Request(l_retrobill_request_rec);
3281:
3282:
3275: l_retrobill_request_rec.sold_to_org_id:=g_retrobill_request_rec.sold_to_org_id;
3276: l_retrobill_request_rec.inventory_item_id:=g_retrobill_request_rec.inventory_item_id;
3277: END IF;
3278: oe_debug_pub.add('sold_to_org_id'||l_retrobill_request_rec.sold_to_org_id);
3279: oe_debug_pub.add('inventory_item_id'||l_retrobill_request_rec.inventory_item_id);
3280: Insert_Retrobill_Request(l_retrobill_request_rec);
3281:
3282:
3283: Else --previously previewed request
3283: Else --previously previewed request
3284:
3285: --EXECUTE will not reprice the orders/lines, just booked the order and update
3286: --PREVIEW again will reprice the orders/lines
3287: oe_debug_pub.add('execution mode:'||p_retrobill_request_rec.execution_mode);
3288:
3289: G_CURRENT_RETROBILL_REQUEST_ID:=p_retrobill_request_rec.retrobill_request_id;
3290:
3291: IF G_CURRENT_RETROBILL_REQUEST_ID IS NULL THEN
3288:
3289: G_CURRENT_RETROBILL_REQUEST_ID:=p_retrobill_request_rec.retrobill_request_id;
3290:
3291: IF G_CURRENT_RETROBILL_REQUEST_ID IS NULL THEN
3292: oe_debug_pub.add('Retro:previouly previewed request, request id can not be NULL!');
3293: l_msg:='Retro:previouly previewed request, request id can not be NULL!';
3294: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3295: END IF;
3296:
3315: x_created_retrobill_request_id:=NULL;
3316: --bug5003256
3317: x_error_count := -1;
3318: When Others Then
3319: oe_debug_pub.add('Exception occured in process_retrobill_requests:'||SQLERRM);
3320: oe_debug_pub.add('Statement number:'||stmt);
3321: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3322: x_retrun_status_text:='OE_RETROBILL_PVT.Process_Retrobill_Request'||SQLERRM;
3323: oe_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data);
3316: --bug5003256
3317: x_error_count := -1;
3318: When Others Then
3319: oe_debug_pub.add('Exception occured in process_retrobill_requests:'||SQLERRM);
3320: oe_debug_pub.add('Statement number:'||stmt);
3321: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3322: x_retrun_status_text:='OE_RETROBILL_PVT.Process_Retrobill_Request'||SQLERRM;
3323: oe_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data);
3324: x_created_retrobill_request_id:=NULL;
3389:
3390: l_request_name varchar2(2000) := NULL;
3391: l_lines_not_retrobilled_count NUMBER:=0;
3392:
3393: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3394: BEGIN
3395: open c_lines;
3396: If l_debug_level > 0 Then
3397: Oe_Debug_Pub.add('skubendr:Entering procedure Oe_Retrobill_Pvt.Oe_Build_Retrobill_Table');
3393: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3394: BEGIN
3395: open c_lines;
3396: If l_debug_level > 0 Then
3397: Oe_Debug_Pub.add('skubendr:Entering procedure Oe_Retrobill_Pvt.Oe_Build_Retrobill_Table');
3398: End If;
3399:
3400: -- Initialize Message Stack
3401: -- We initialize the message stack here and for the subsequent process order calls pass the init_msg_list as FALSE
3407: IF(p_retrobill_request_id is NULL) THEN
3408: FOR c_open_retrobill_requests_rec in c_open_retrobill_requests(c_lines_record.header_id,c_lines_record.line_id) LOOP
3409: l_request_name := c_open_retrobill_requests_rec.name;
3410: END LOOP;
3411: oe_debug_pub.add('l_request_name:'||l_request_name);
3412: END IF;
3413:
3414: IF(l_request_name is NULL OR p_retrobill_request_id is NOT NULL) THEN
3415: l_retrobill_tbl(i).original_line_id :=c_lines_record.line_id;
3443: ,p_constraint_id => null
3444: );
3445:
3446: IF l_debug_level > 0 Then
3447: oe_debug_pub.add('Line has open retrobill line:'||c_lines_record.line_id);
3448: oe_debug_pub.add('Open Request:'||l_request_name);
3449: END IF;
3450:
3451: -- The line has previewed retrobill line and hence cannot be previewed
3444: );
3445:
3446: IF l_debug_level > 0 Then
3447: oe_debug_pub.add('Line has open retrobill line:'||c_lines_record.line_id);
3448: oe_debug_pub.add('Open Request:'||l_request_name);
3449: END IF;
3450:
3451: -- The line has previewed retrobill line and hence cannot be previewed
3452: FND_MESSAGE.SET_NAME('ONT','ONT_UNINVOICED_RETLINE_EXIST');
3470: l_retrobill_request_rec.sold_to_org_id := p_sold_to_org_id;
3471: l_retrobill_request_rec.inventory_item_id := p_inventory_item_id;
3472:
3473: If l_debug_level > 0 Then
3474: Oe_Debug_Pub.add('skubendr:Before calling Oe_Retrobill_Pvt.Process_Retrobill_Request');
3475: End If;
3476:
3477: IF(l_retrobill_tbl.count <> 0) THEN
3478: Oe_Retrobill_Pvt.Process_Retrobill_Request
3486: ,x_error_count => x_error_count); --bug5003256
3487: END IF;
3488:
3489: IF l_debug_level > 0 Then
3490: Oe_Debug_Pub.add('skubendr:After calling Oe_Retrobill_Pvt.Process_Retrobill_Request :'||l_return_status);
3491: END IF;
3492:
3493: -- delete the data from temp table after retrobilling process
3494: IF(p_request_session_id is not NULL) THEN
3501: x_return_status := l_return_status;
3502: x_return_status_text := l_return_status_text;
3503:
3504: IF l_debug_level > 0 Then
3505: Oe_Debug_Pub.add('skubendr:Exiting procedure Oe_Retrobill_Pvt.Oe_Build_Retrobill_Table:'||l_return_status);
3506: END IF;
3507: EXCEPTION
3508: WHEN NO_DATA_FOUND THEN
3509: RAISE NO_DATA_FOUND;
3507: EXCEPTION
3508: WHEN NO_DATA_FOUND THEN
3509: RAISE NO_DATA_FOUND;
3510: WHEN OTHERS THEN
3511: oe_debug_pub.add('Error'||SQLERRM);
3512: END Oe_Build_Retrobill_Tbl;
3513:
3514:
3515: -- This procedure will be called from the UI when the user submits an concurrent request
3535: l_return_status_text VARCHAR2(2000);
3536: l_retrobill_request_id NUMBER;
3537:
3538: l_concurrent_request_id NUMBER;
3539: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3540: --rt moac
3541: l_org_id NUMBER;
3542:
3543: --bug5003256
3548:
3549: FND_PROFILE.Get('CONC_REQUEST_ID', l_concurrent_request_id);
3550:
3551: IF l_debug_level > 0 THEN
3552: oe_debug_pub.add( 'REQUEST ID: '|| TO_CHAR ( L_CONCURRENT_REQUEST_ID ) ) ;
3553: END IF;
3554: --rt moac start
3555: BEGIN
3556: SELECT org_id INTO l_org_id
3565: END;
3566: IF l_org_id IS NOT NULL THEN
3567: MO_GLOBAL.Set_Policy_Context('S',l_org_id);
3568: IF l_debug_level > 0 THEN
3569: oe_debug_pub.add('Context is set for org_id : '|| mo_global.get_current_org_id);
3570: END IF;
3571: ELSE
3572: l_return_status := FND_API.G_RET_STS_ERROR;
3573: fnd_file.put_line(FND_FILE.OUTPUT,'Could not set org context');
3610: END IF;
3611: EXCEPTION
3612: WHEN OTHERS THEN
3613: rollback;
3614: oe_debug_pub.add('Error'||SQLERRM);
3615: retcode := -1;
3616: END Oe_Retrobill_Conc_Pgm;
3617:
3618:
3638: l_result_code VARCHAR2(240);
3639: l_return_status VARCHAR2(30);
3640: l_interface_line_rec OE_Invoice_PUB.RA_Interface_Lines_Rec_Type;
3641: --
3642: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3643: --
3644:
3645: --Find all retrobill bill only lines created before the Return Line
3646: cursor previous_retrobill_lines IS
3676: l_orig_selling_price NUMBER;
3677: BEGIN
3678: null;
3679: IF l_debug_level > 0 THEN
3680: oe_debug_pub.add( 'ENTERING INTERFACE_Retrobilled_RMA ( ) PROCEDURE' , 5 ) ;
3681: oe_debug_pub.add( 'INTERFACING Retro RMA LINE ID '||TO_CHAR ( P_LINE_REC.LINE_ID ), 3);
3682: END IF;
3683:
3684: -- First credit to original order line, then positive retrobill lines
3677: BEGIN
3678: null;
3679: IF l_debug_level > 0 THEN
3680: oe_debug_pub.add( 'ENTERING INTERFACE_Retrobilled_RMA ( ) PROCEDURE' , 5 ) ;
3681: oe_debug_pub.add( 'INTERFACING Retro RMA LINE ID '||TO_CHAR ( P_LINE_REC.LINE_ID ), 3);
3682: END IF;
3683:
3684: -- First credit to original order line, then positive retrobill lines
3685: i := 1;
3689: -- loop through retrobill lines
3690: For retrobill_line in previous_retrobill_lines
3691: LOOP
3692: IF l_debug_level > 0 THEN
3693: oe_debug_pub.add('find retrobill line'||retrobill_line.line_id, 3);
3694: oe_debug_pub.add('line category code'||retrobill_line.line_category_code, 3);
3695: oe_debug_pub.add('retrobill request'||retrobill_line.retrobill_request_id, 3);
3696: oe_debug_pub.add('order_source_id'||retrobill_line.order_source_id, 3);
3697: oe_debug_pub.add('list price'||retrobill_line.unit_list_price, 3);
3690: For retrobill_line in previous_retrobill_lines
3691: LOOP
3692: IF l_debug_level > 0 THEN
3693: oe_debug_pub.add('find retrobill line'||retrobill_line.line_id, 3);
3694: oe_debug_pub.add('line category code'||retrobill_line.line_category_code, 3);
3695: oe_debug_pub.add('retrobill request'||retrobill_line.retrobill_request_id, 3);
3696: oe_debug_pub.add('order_source_id'||retrobill_line.order_source_id, 3);
3697: oe_debug_pub.add('list price'||retrobill_line.unit_list_price, 3);
3698: oe_debug_pub.add('selling price'||retrobill_line.unit_selling_price, 3);
3691: LOOP
3692: IF l_debug_level > 0 THEN
3693: oe_debug_pub.add('find retrobill line'||retrobill_line.line_id, 3);
3694: oe_debug_pub.add('line category code'||retrobill_line.line_category_code, 3);
3695: oe_debug_pub.add('retrobill request'||retrobill_line.retrobill_request_id, 3);
3696: oe_debug_pub.add('order_source_id'||retrobill_line.order_source_id, 3);
3697: oe_debug_pub.add('list price'||retrobill_line.unit_list_price, 3);
3698: oe_debug_pub.add('selling price'||retrobill_line.unit_selling_price, 3);
3699: END IF;
3692: IF l_debug_level > 0 THEN
3693: oe_debug_pub.add('find retrobill line'||retrobill_line.line_id, 3);
3694: oe_debug_pub.add('line category code'||retrobill_line.line_category_code, 3);
3695: oe_debug_pub.add('retrobill request'||retrobill_line.retrobill_request_id, 3);
3696: oe_debug_pub.add('order_source_id'||retrobill_line.order_source_id, 3);
3697: oe_debug_pub.add('list price'||retrobill_line.unit_list_price, 3);
3698: oe_debug_pub.add('selling price'||retrobill_line.unit_selling_price, 3);
3699: END IF;
3700: -- Only credit to positive lines, but want to get the
3693: oe_debug_pub.add('find retrobill line'||retrobill_line.line_id, 3);
3694: oe_debug_pub.add('line category code'||retrobill_line.line_category_code, 3);
3695: oe_debug_pub.add('retrobill request'||retrobill_line.retrobill_request_id, 3);
3696: oe_debug_pub.add('order_source_id'||retrobill_line.order_source_id, 3);
3697: oe_debug_pub.add('list price'||retrobill_line.unit_list_price, 3);
3698: oe_debug_pub.add('selling price'||retrobill_line.unit_selling_price, 3);
3699: END IF;
3700: -- Only credit to positive lines, but want to get the
3701: -- total retrobilled price even for negative lines,
3694: oe_debug_pub.add('line category code'||retrobill_line.line_category_code, 3);
3695: oe_debug_pub.add('retrobill request'||retrobill_line.retrobill_request_id, 3);
3696: oe_debug_pub.add('order_source_id'||retrobill_line.order_source_id, 3);
3697: oe_debug_pub.add('list price'||retrobill_line.unit_list_price, 3);
3698: oe_debug_pub.add('selling price'||retrobill_line.unit_selling_price, 3);
3699: END IF;
3700: -- Only credit to positive lines, but want to get the
3701: -- total retrobilled price even for negative lines,
3702: -- so that we know whether RMA line price is the same as invoiced price
3744: RAISE FND_API.G_EXC_ERROR;
3745: END;
3746:
3747: IF l_debug_level > 0 THEN
3748: oe_debug_pub.add('original Price:'||l_orig_selling_price,3);
3749: oe_debug_pub.add('rma price:'||p_line_rec.unit_selling_price,3);
3750: oe_debug_pub.add('retrobilled price'||l_retrobilled_selling_price,3);
3751: END IF;
3752: -- No retrobill line, interface the current line as it is
3745: END;
3746:
3747: IF l_debug_level > 0 THEN
3748: oe_debug_pub.add('original Price:'||l_orig_selling_price,3);
3749: oe_debug_pub.add('rma price:'||p_line_rec.unit_selling_price,3);
3750: oe_debug_pub.add('retrobilled price'||l_retrobilled_selling_price,3);
3751: END IF;
3752: -- No retrobill line, interface the current line as it is
3753: -- If RMA price doesn't match the invoiced price, we don't know how to handle this, credit to the original line: normally should nothappen
3746:
3747: IF l_debug_level > 0 THEN
3748: oe_debug_pub.add('original Price:'||l_orig_selling_price,3);
3749: oe_debug_pub.add('rma price:'||p_line_rec.unit_selling_price,3);
3750: oe_debug_pub.add('retrobilled price'||l_retrobilled_selling_price,3);
3751: END IF;
3752: -- No retrobill line, interface the current line as it is
3753: -- If RMA price doesn't match the invoiced price, we don't know how to handle this, credit to the original line: normally should nothappen
3754: IF (l_line_tbl.count = 1) THEN
3783: l_line_rec.invoiced_quantity := NULL;
3784: END IF;
3785:
3786: IF l_debug_level > 0 THEN
3787: oe_debug_pub.add('retrobill:'||l_line_rec.line_id,3);
3788: oe_debug_pub.add('retrobill line category'
3789: ||l_line_rec.line_category_code,3);
3790: oe_debug_pub.add('retrobill line type'
3791: ||l_line_rec.line_type_id,3);
3784: END IF;
3785:
3786: IF l_debug_level > 0 THEN
3787: oe_debug_pub.add('retrobill:'||l_line_rec.line_id,3);
3788: oe_debug_pub.add('retrobill line category'
3789: ||l_line_rec.line_category_code,3);
3790: oe_debug_pub.add('retrobill line type'
3791: ||l_line_rec.line_type_id,3);
3792: oe_debug_pub.add('retrobill item'||l_line_rec.ordered_item,3);
3786: IF l_debug_level > 0 THEN
3787: oe_debug_pub.add('retrobill:'||l_line_rec.line_id,3);
3788: oe_debug_pub.add('retrobill line category'
3789: ||l_line_rec.line_category_code,3);
3790: oe_debug_pub.add('retrobill line type'
3791: ||l_line_rec.line_type_id,3);
3792: oe_debug_pub.add('retrobill item'||l_line_rec.ordered_item,3);
3793: oe_debug_pub.add('invoiced_quantity'||l_line_rec.invoiced_quantity,3);
3794: END IF;
3788: oe_debug_pub.add('retrobill line category'
3789: ||l_line_rec.line_category_code,3);
3790: oe_debug_pub.add('retrobill line type'
3791: ||l_line_rec.line_type_id,3);
3792: oe_debug_pub.add('retrobill item'||l_line_rec.ordered_item,3);
3793: oe_debug_pub.add('invoiced_quantity'||l_line_rec.invoiced_quantity,3);
3794: END IF;
3795:
3796: OE_Invoice_PUB. Interface_Single_line(p_line_rec => l_line_rec
3789: ||l_line_rec.line_category_code,3);
3790: oe_debug_pub.add('retrobill line type'
3791: ||l_line_rec.line_type_id,3);
3792: oe_debug_pub.add('retrobill item'||l_line_rec.ordered_item,3);
3793: oe_debug_pub.add('invoiced_quantity'||l_line_rec.invoiced_quantity,3);
3794: END IF;
3795:
3796: OE_Invoice_PUB. Interface_Single_line(p_line_rec => l_line_rec
3797: ,p_header_rec => p_header_rec
3799: ,x_return_status => x_return_status
3800: ,x_result_out => x_result_out);
3801:
3802: IF l_debug_level > 0 THEN
3803: oe_debug_pub.add( 'INTERFACED W/REQUEST_ID : '||
3804: L_INTERFACE_LINE_REC.REQUEST_ID || ' X_RETURN_STATUS: '|| X_RETURN_STATUS , 5 ) ;
3805: END IF;
3806: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3807: RAISE FND_API.G_EXC_ERROR;
3813:
3814: -- 3661895: we need to clear this global table
3815: G_Retro_Bill_Only_Line_Tbl.delete;
3816: IF l_debug_level > 0 THEN
3817: oe_debug_pub.add('Clearing the cache G_Retro_Bill_Only_Line_Tbl');
3818: END IF;
3819: END Interface_Retrobilled_RMA;
3820:
3821: Function Invoice_Number(p_order_number IN NUMBER,
3869: p_scope IN VARCHAR2,
3870: x_result OUT NOCOPY NUMBER
3871: )
3872: AS
3873: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3874: l_count NUMBER := 0;
3875: l_line_id NUMBER;
3876: l_header_id NUMBER;
3877: l_creation_date DATE;
3995: , p_x_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl
3996: );
3997:
3998: IF(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3999: oe_debug_pub.add('Deleted Header:'||l_retrobill_header_rec.header_id);
4000: l_headers_processed := l_headers_processed+1;
4001: END IF;
4002: ELSE
4003: oe_debug_pub.add('Cannot purge request as retrobill booked order exists'||p_request_name);
3999: oe_debug_pub.add('Deleted Header:'||l_retrobill_header_rec.header_id);
4000: l_headers_processed := l_headers_processed+1;
4001: END IF;
4002: ELSE
4003: oe_debug_pub.add('Cannot purge request as retrobill booked order exists'||p_request_name);
4004: FND_MESSAGE.SET_NAME('ONT','ONT_RETRO_PURGE_NOT_ALLOWED');
4005: FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_request_name);
4006: OE_MSG_PUB.ADD;
4007:
4007:
4008: EXIT;
4009: END IF;
4010: ELSE
4011: oe_debug_pub.add('Cannot purge request as purge preview order is N and attached orders exist');
4012: FND_MESSAGE.SET_NAME('ONT','ONT_RETRO_PURGE_NOT_ALLOWED');
4013: FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_request_name);
4014: OE_MSG_PUB.ADD;
4015:
4024: IF(l_header_count = 0) THEN
4025: IF(p_retrobill_request_id is NOT NULL) THEN
4026: Delete from oe_retrobill_requests where
4027: retrobill_request_id=to_number(p_retrobill_request_id);
4028: oe_debug_pub.add('Deleted Request'||p_retrobill_request_id);
4029: l_requests_processed := l_requests_processed + 1;
4030: END IF;
4031: END IF;
4032: x_requests_processed := l_requests_processed;
4032: x_requests_processed := l_requests_processed;
4033: x_headers_processed := l_headers_processed;
4034: EXCEPTION
4035: WHEN OTHERS THEN
4036: oe_debug_pub.add('Exception occured:'||SQLERRM);
4037: RAISE;
4038: END;
4039:
4040: PROCEDURE Oe_Retrobill_Purge
4069: l_change_sequence VARCHAR2(50);
4070: l_orig_sys_line_ref VARCHAR2(50);
4071: l_message_text VARCHAR2(2000);
4072:
4073: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4074: --rt moac
4075: l_org_id NUMBER;
4076: l_old_org_id NUMBER;
4077:
4141:
4142: FND_PROFILE.Get('CONC_REQUEST_ID', l_concurrent_id);
4143:
4144: IF l_debug_level > 0 THEN
4145: oe_debug_pub.add('Entering Oe_Retrobill_Pvt.Oe_Retrobill_Purge',1);
4146: oe_debug_pub.add( 'CONCURRENT REQUEST ID: '|| TO_CHAR ( L_CONCURRENT_ID ) ) ;
4147: END IF;
4148:
4149: --rt moac start
4142: FND_PROFILE.Get('CONC_REQUEST_ID', l_concurrent_id);
4143:
4144: IF l_debug_level > 0 THEN
4145: oe_debug_pub.add('Entering Oe_Retrobill_Pvt.Oe_Retrobill_Purge',1);
4146: oe_debug_pub.add( 'CONCURRENT REQUEST ID: '|| TO_CHAR ( L_CONCURRENT_ID ) ) ;
4147: END IF;
4148:
4149: --rt moac start
4150: mo_global.init('ONT');
4151: IF p_org_id IS NOT NULL THEN
4152: l_org_id := to_number(p_org_id);
4153: MO_GLOBAL.Set_Policy_Context('S',l_org_id);
4154: IF l_debug_level > 0 THEN
4155: oe_debug_pub.add('Setting the context for org_id : '|| l_org_id);
4156: END IF;
4157: ELSE
4158: MO_GLOBAL.Set_Policy_Context('M',null);
4159: END IF;
4161:
4162: IF(p_retrobill_request_id is NULL) THEN
4163: l_old_org_id := -99; --rt moac
4164: FOR l_retrobill_request_rec IN retrobill_request LOOP
4165: oe_debug_pub.add('Retrobill Request Id:'||l_retrobill_request_rec.retrobill_request_id);
4166: --rt moac start
4167: IF p_org_id IS NULL THEN
4168: IF l_retrobill_request_rec.org_id <> l_old_org_id THEN
4169: MO_GLOBAL.Set_Policy_Context('S',l_retrobill_request_rec.org_id);
4167: IF p_org_id IS NULL THEN
4168: IF l_retrobill_request_rec.org_id <> l_old_org_id THEN
4169: MO_GLOBAL.Set_Policy_Context('S',l_retrobill_request_rec.org_id);
4170: IF l_debug_level > 0 THEN
4171: oe_debug_pub.add('Setting the context for org_id : '|| l_retrobill_request_rec.org_id);
4172: oe_debug_pub.add('mo_global.get_current_org_id : '|| mo_global.get_current_org_id);
4173: END IF;
4174: l_old_org_id := l_retrobill_request_rec.org_id;
4175: END IF;
4168: IF l_retrobill_request_rec.org_id <> l_old_org_id THEN
4169: MO_GLOBAL.Set_Policy_Context('S',l_retrobill_request_rec.org_id);
4170: IF l_debug_level > 0 THEN
4171: oe_debug_pub.add('Setting the context for org_id : '|| l_retrobill_request_rec.org_id);
4172: oe_debug_pub.add('mo_global.get_current_org_id : '|| mo_global.get_current_org_id);
4173: END IF;
4174: l_old_org_id := l_retrobill_request_rec.org_id;
4175: END IF;
4176: END IF;
4180: --rt moac end
4181:
4182: --bug4752386 If the execution mode is 'EXECUTE', need to log a message and continue with the next request
4183: IF l_retrobill_request_rec.execution_mode='EXECUTE' THEN
4184: oe_debug_pub.add('Cannot purge request as it has been executed'||l_retrobill_request_rec.name);
4185: FND_MESSAGE.SET_NAME('ONT','ONT_RT_EXC_PURGE_NOT_ALLOWED');
4186: FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_retrobill_request_rec.name);
4187: OE_MSG_PUB.ADD;
4188:
4195: END IF; --execution mode = 'EXECUTE'
4196: END LOOP;
4197: ELSE
4198: FOR l_retrobill_request_rec IN retrobill_request_id LOOP
4199: oe_debug_pub.add('Retrobill Request Id:'||l_retrobill_request_rec.retrobill_request_id);
4200:
4201: --rt moac start
4202: IF p_org_id IS NULL THEN
4203: MO_GLOBAL.Set_Policy_Context('S',l_retrobill_request_rec.org_id);
4201: --rt moac start
4202: IF p_org_id IS NULL THEN
4203: MO_GLOBAL.Set_Policy_Context('S',l_retrobill_request_rec.org_id);
4204: IF l_debug_level > 0 THEN
4205: oe_debug_pub.add('Setting the context for org_id : '|| l_retrobill_request_rec.org_id);
4206: END IF;
4207: END IF;
4208:
4209: --setting a dummy message context so that the messages not attached to any particular order get the org_id corresponding to the policy context
4211: --rt moac end
4212:
4213: --bug4752386 If the execution mode is 'EXECUTE', need to log a message and continue with the next request
4214: IF l_retrobill_request_rec.execution_mode='EXECUTE' THEN
4215: oe_debug_pub.add('Cannot purge request as it has been executed'||l_retrobill_request_rec.name);
4216: FND_MESSAGE.SET_NAME('ONT','ONT_RT_EXC_PURGE_NOT_ALLOWED');
4217: FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_retrobill_request_rec.name);
4218: OE_MSG_PUB.ADD;
4219:
4248: fnd_file.put_line(FND_FILE.OUTPUT,'');
4249: END LOOP;
4250:
4251: IF l_debug_level > 0 THEN
4252: oe_debug_pub.add('Number of Requests Purged:' ||l_requests_processed);
4253: oe_debug_pub.add('Number of Orders Purged:' ||l_headers_processed);
4254: END IF;
4255:
4256: fnd_file.put_line(FND_FILE.OUTPUT,'Number of Requests Purged:' ||l_requests_processed);
4249: END LOOP;
4250:
4251: IF l_debug_level > 0 THEN
4252: oe_debug_pub.add('Number of Requests Purged:' ||l_requests_processed);
4253: oe_debug_pub.add('Number of Orders Purged:' ||l_headers_processed);
4254: END IF;
4255:
4256: fnd_file.put_line(FND_FILE.OUTPUT,'Number of Requests Purged:' ||l_requests_processed);
4257: fnd_file.put_line(FND_FILE.OUTPUT,'Number of Orders Purged:' ||l_headers_processed);
4254: END IF;
4255:
4256: fnd_file.put_line(FND_FILE.OUTPUT,'Number of Requests Purged:' ||l_requests_processed);
4257: fnd_file.put_line(FND_FILE.OUTPUT,'Number of Orders Purged:' ||l_headers_processed);
4258: oe_debug_pub.add('Exiting Oe_Retrobill_pvt.Oe_retrobill_Purge');
4259: commit;
4260:
4261: EXCEPTION
4262: WHEN OTHERS THEN
4261: EXCEPTION
4262: WHEN OTHERS THEN
4263: ROLLBACK;
4264: retcode := -1;
4265: oe_debug_pub.add('Error in Procedure Oe_Retrobill_Purge'||sqlerrm);
4266: Raise;
4267: END Oe_Retrobill_Purge;
4268: --retro}
4269: -- 3661895 Added the following procedure
4276: p_price_adjustment_id IN NUMBER
4277: ) RETURN NUMBER
4278: AS
4279: --
4280: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4281: --
4282:
4283: l_list_line_id NUMBER;
4284: l_sum_credied_amount NUMBER := 0;
4286:
4287: BEGIN
4288:
4289: IF l_debug_level > 0 THEN
4290: oe_debug_pub.add('ENTERING FUNCTION Get_Retrobill_Credited_Adj',1);
4291: oe_debug_pub.add('G_Retro_Bill_Only_Line_Tbl count:'||G_Retro_Bill_Only_Line_Tbl.count);
4292: END IF;
4293:
4294: IF(p_price_adjustment_id is NOT NULL) THEN
4287: BEGIN
4288:
4289: IF l_debug_level > 0 THEN
4290: oe_debug_pub.add('ENTERING FUNCTION Get_Retrobill_Credited_Adj',1);
4291: oe_debug_pub.add('G_Retro_Bill_Only_Line_Tbl count:'||G_Retro_Bill_Only_Line_Tbl.count);
4292: END IF;
4293:
4294: IF(p_price_adjustment_id is NOT NULL) THEN
4295: select list_line_id into l_list_line_id
4314: EXCEPTION
4315: WHEN NO_DATA_FOUND THEN
4316: NULL;
4317: WHEN OTHERS THEN
4318: oe_debug_pub.add('Exception occured:'||SQLERRM);
4319: RAISE;
4320: END;
4321: END LOOP;
4322: END IF;
4321: END LOOP;
4322: END IF;
4323:
4324: IF l_debug_level > 0 THEN
4325: oe_debug_pub.add('l_sum_credied_amount:'||l_sum_credied_amount||'for list line id:'||l_list_line_id);
4326: END IF;
4327: RETURN nvl(l_sum_credied_amount,0);
4328:
4329: EXCEPTION
4329: EXCEPTION
4330: WHEN NO_DATA_FOUND THEN
4331: NULL;
4332: WHEN OTHERS THEN
4333: oe_debug_pub.add('Exception occured:'||SQLERRM);
4334: RAISE;
4335: END Get_Retrobill_Credited_Adj;
4336:
4337: -- 3661895
4346: ,x_line_adjustments OUT NOCOPY OE_Header_Adj_Util.Line_Adjustments_Tab_Type
4347: )
4348: IS
4349: --
4350: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4351: --
4352: l_adjustments_tbl OE_Header_Adj_Util.Line_Adjustments_Tab_Type;
4353: l_adj_rec OE_Header_Adj_Util.Line_Adjustments_Rec_Type;
4354: l_header_id NUMBER;
4356:
4357: BEGIN
4358:
4359: IF l_debug_level > 0 THEN
4360: oe_debug_pub.add( 'ENTERING OE_HEADER_ADJ_UTIL.GET_LINE_ADJUSTMENTS' , 1 ) ;
4361: END IF;
4362:
4363: IF(p_line_rec.retrobill_request_id = 0) THEN
4364: l_header_id := p_line_rec.header_id;
4368: l_line_id := p_line_rec.orig_sys_line_ref;
4369: END IF;
4370:
4371: IF l_debug_level > 0 THEN
4372: oe_debug_pub.add('HEADER ID:'||l_header_id);
4373: oe_debug_pub.add('LINE ID:'||l_line_id);
4374: END IF;
4375:
4376: OE_Header_Adj_Util.Get_Line_Adjustments
4369: END IF;
4370:
4371: IF l_debug_level > 0 THEN
4372: oe_debug_pub.add('HEADER ID:'||l_header_id);
4373: oe_debug_pub.add('LINE ID:'||l_line_id);
4374: END IF;
4375:
4376: OE_Header_Adj_Util.Get_Line_Adjustments
4377: (p_header_id => l_header_id
4409:
4410: END IF;
4411:
4412: IF l_debug_level > 0 THEN
4413: oe_debug_pub.add( 'EXITING OE_HEADER_ADJ_UTIL.GET_LINE_ADJUSTMENTS' , 1 ) ;
4414: END IF;
4415:
4416: END Get_Line_Adjustments;
4417: