[Home] [Help]
259: p_msg_data IN VARCHAR2) AS
260: l_msg_data VARCHAR2(5000);
261: l_msg_count NUMBER;
262: Begin
263: oe_debug_pub.add('no. of OE messages :'||p_msg_count);
264: for k in 1 .. p_msg_count loop
265: l_msg_data := oe_msg_pub.get( p_msg_index => k,
266: p_encoded => 'F'
267: );
264: for k in 1 .. p_msg_count loop
265: l_msg_data := oe_msg_pub.get( p_msg_index => k,
266: p_encoded => 'F'
267: );
268: oe_debug_pub.add(substr(l_msg_data,1,255));
269: oe_debug_pub.add('Error msg: '||substr(l_msg_data,1,200));
270: end loop;
271:
272: fnd_msg_pub.count_and_get( p_encoded => 'F'
265: l_msg_data := oe_msg_pub.get( p_msg_index => k,
266: p_encoded => 'F'
267: );
268: oe_debug_pub.add(substr(l_msg_data,1,255));
269: oe_debug_pub.add('Error msg: '||substr(l_msg_data,1,200));
270: end loop;
271:
272: fnd_msg_pub.count_and_get( p_encoded => 'F'
273: ,p_count => l_msg_count
272: fnd_msg_pub.count_and_get( p_encoded => 'F'
273: ,p_count => l_msg_count
274: ,p_data => l_msg_data);
275:
276: --oe_debug_pub.add('no. of FND messages :'||l_msg_count,1);
277: oe_debug_pub.add('no. of FND messages :'||l_msg_count);
278:
279: for k in 1 .. l_msg_count loop
280: l_msg_data := fnd_msg_pub.get( p_msg_index => k,
273: ,p_count => l_msg_count
274: ,p_data => l_msg_data);
275:
276: --oe_debug_pub.add('no. of FND messages :'||l_msg_count,1);
277: oe_debug_pub.add('no. of FND messages :'||l_msg_count);
278:
279: for k in 1 .. l_msg_count loop
280: l_msg_data := fnd_msg_pub.get( p_msg_index => k,
281: p_encoded => 'F'
279: for k in 1 .. l_msg_count loop
280: l_msg_data := fnd_msg_pub.get( p_msg_index => k,
281: p_encoded => 'F'
282: );
283: oe_debug_pub.add('Error msg: '||substr(l_msg_data,1,200));
284: oe_debug_pub.add(substr(l_msg_data,1,255));
285: end loop;
286:
287: End;
280: l_msg_data := fnd_msg_pub.get( p_msg_index => k,
281: p_encoded => 'F'
282: );
283: oe_debug_pub.add('Error msg: '||substr(l_msg_data,1,200));
284: oe_debug_pub.add(substr(l_msg_data,1,255));
285: end loop;
286:
287: End;
288:
296: And retrobill_request_id <> G_CURRENT_RETROBILL_REQUEST_ID;
297:
298: l_retro_line_id Number;
299: l_retro_header_id Number;
300: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
301: l_query_line_id Number;
302: l_query_header_id Number;
303:
304: Begin
303:
304: Begin
305:
306: If l_debug_level > 0 Then
307: oe_debug_pub.add('Entering oe_retrobill_pvt.get_last_retro...input header_id:'||p_header_id);
308: End If;
309:
310: --!!!Need to check system param, if no retrobill should return the same header_id immediately
311: --to save processing time!!!
320: x_header_id:=l_retro_header_id;
321: END IF;
322:
323: If l_debug_level > 0 Then
324: oe_debug_pub.add('Exiting oe_retrobill_pvt.get_last_retro...output header_id:'||x_header_id);
325: End If;
326:
327: Exception
328: When Others then
325: End If;
326:
327: Exception
328: When Others then
329: oe_debug_pub.add('Retro:get_last_retro_HdrID:'||SQLERRM);
330: x_header_id:=p_header_id;
331:
332: End;
333:
344: And l.retrobill_request_id <> G_CURRENT_RETROBILL_REQUEST_ID
345: AND b.line_id = p_line_id;
346: l_retro_line_id Number;
347: l_retro_header_id Number;
348: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
349: l_query_line_id Number;
350: l_query_header_id Number;
351: Begin
352:
350: l_query_header_id Number;
351: Begin
352:
353: If l_debug_level > 0 Then
354: oe_debug_pub.add('Entering oe_retrobill_pvt.get_last_retro...input Line_id:'||p_line_id);
355: End If;
356:
357: --!!!Need to check system param, if no retrobill should return the same header_id immediately
358: --to save processing time!!!
368: x_line_id:=l_retro_line_id;
369: END IF;
370:
371: If l_debug_level > 0 Then
372: oe_debug_pub.add('Exiting oe_retrobill_pvt.get_last_retro...output line_id:'||x_line_id);
373: End If;
374:
375: Exception
376: When Others then
373: End If;
374:
375: Exception
376: When Others then
377: oe_debug_pub.add('Retro:get_last_retro_linID:'||SQLERRM);
378: x_line_id:=p_line_id;
379: End;
380:
381:
388: --bug3738043 This function returns the number of pricing phases other than List Line Base Price Phase where the RETROBILL event is attached.
389: --The global variable G_RETRO_PRICING_PHASE_COUNT is initialized by calling this function in the procedure Perform_Operations if it is preview again
390: FUNCTION Get_Retro_Pricing_Phase_Count RETURN NUMBER AS
391: l_retro_pricing_phase_count NUMBER := 0;
392: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
393: BEGIN
394:
395: SELECT count(*) INTO l_retro_pricing_phase_count
396: FROM qp_pricing_phases p,
400: AND e.pricing_event_code='RETROBILL'
401: AND trunc(sysdate) BETWEEN trunc(nvl(e.end_date_active,sysdate))
402: AND trunc(nvl(e.end_date_active,sysdate));
403: IF l_debug_level > 0 THEN
404: 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);
405: END IF;
406:
407: RETURN l_retro_pricing_phase_count;
408: EXCEPTION
443: x_ulp_sum:=nvl(x_ulp_sum,0);
444:
445: Exception
446: When Others Then
447: oe_debug_pub.add('Retro:Get_Retrobilled_Sum:'||SQLERRM);
448: Raise;
449: End;
450:
451: Procedure Get_Return_Price(p_header_id IN NUMBER,
459: x_ulp_ppqty OUT NOCOPY NUMBER,--bug3540728
460: x_usp_ppqty OUT NOCOPY NUMBER) AS --bug3540728
461: l_usp_sum NUMBER;
462: l_ulp_sum NUMBER;
463: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
464: Begin
465: Get_Retrobilled_Sum(p_header_id=>p_header_id,
466: p_line_id =>p_line_id,
467: x_usp_sum =>l_usp_sum,
467: x_usp_sum =>l_usp_sum,
468: x_ulp_sum =>l_ulp_sum);
469:
470: IF l_debug_level > 0 THEN
471: oe_debug_pub.add('Retro:Input header_id:'||p_header_id);
472: oe_debug_pub.add('Retro:Input line_id:'||p_line_id);
473: oe_debug_pub.add('Retro:Input USP:'||p_usp);
474: oe_debug_pub.add('Retro:Input ULP:'||p_ulp);
475: END IF;
468: x_ulp_sum =>l_ulp_sum);
469:
470: IF l_debug_level > 0 THEN
471: oe_debug_pub.add('Retro:Input header_id:'||p_header_id);
472: oe_debug_pub.add('Retro:Input line_id:'||p_line_id);
473: oe_debug_pub.add('Retro:Input USP:'||p_usp);
474: oe_debug_pub.add('Retro:Input ULP:'||p_ulp);
475: END IF;
476:
469:
470: IF l_debug_level > 0 THEN
471: oe_debug_pub.add('Retro:Input header_id:'||p_header_id);
472: oe_debug_pub.add('Retro:Input line_id:'||p_line_id);
473: oe_debug_pub.add('Retro:Input USP:'||p_usp);
474: oe_debug_pub.add('Retro:Input ULP:'||p_ulp);
475: END IF;
476:
477: x_usp:=p_usp + l_usp_sum;
470: IF l_debug_level > 0 THEN
471: oe_debug_pub.add('Retro:Input header_id:'||p_header_id);
472: oe_debug_pub.add('Retro:Input line_id:'||p_line_id);
473: oe_debug_pub.add('Retro:Input USP:'||p_usp);
474: oe_debug_pub.add('Retro:Input ULP:'||p_ulp);
475: END IF;
476:
477: x_usp:=p_usp + l_usp_sum;
478: x_ulp:=p_ulp + l_ulp_sum;
483: END IF;
484: --bug3540728 end
485:
486: IF l_debug_level > 0 THEN
487: oe_debug_pub.add('Retro:Return usp_sum:'||l_usp_sum);
488: oe_debug_pub.add('Retro:Return ulp_sum:'||l_ulp_sum);
489: oe_debug_pub.add('Retro:Return USP:'||x_usp);
490: oe_debug_pub.add('Retro:Return ULP:'||x_ulp);
491: oe_debug_pub.add('Retro:Return ULP_PER_PQTY:'||x_ulp_ppqty);
484: --bug3540728 end
485:
486: IF l_debug_level > 0 THEN
487: oe_debug_pub.add('Retro:Return usp_sum:'||l_usp_sum);
488: oe_debug_pub.add('Retro:Return ulp_sum:'||l_ulp_sum);
489: oe_debug_pub.add('Retro:Return USP:'||x_usp);
490: oe_debug_pub.add('Retro:Return ULP:'||x_ulp);
491: oe_debug_pub.add('Retro:Return ULP_PER_PQTY:'||x_ulp_ppqty);
492: oe_debug_pub.add('Retro:Return USP_PER_PQTY:'||x_usp_ppqty);
485:
486: IF l_debug_level > 0 THEN
487: oe_debug_pub.add('Retro:Return usp_sum:'||l_usp_sum);
488: oe_debug_pub.add('Retro:Return ulp_sum:'||l_ulp_sum);
489: oe_debug_pub.add('Retro:Return USP:'||x_usp);
490: oe_debug_pub.add('Retro:Return ULP:'||x_ulp);
491: oe_debug_pub.add('Retro:Return ULP_PER_PQTY:'||x_ulp_ppqty);
492: oe_debug_pub.add('Retro:Return USP_PER_PQTY:'||x_usp_ppqty);
493: END IF;
486: IF l_debug_level > 0 THEN
487: oe_debug_pub.add('Retro:Return usp_sum:'||l_usp_sum);
488: oe_debug_pub.add('Retro:Return ulp_sum:'||l_ulp_sum);
489: oe_debug_pub.add('Retro:Return USP:'||x_usp);
490: oe_debug_pub.add('Retro:Return ULP:'||x_ulp);
491: oe_debug_pub.add('Retro:Return ULP_PER_PQTY:'||x_ulp_ppqty);
492: oe_debug_pub.add('Retro:Return USP_PER_PQTY:'||x_usp_ppqty);
493: END IF;
494:
487: oe_debug_pub.add('Retro:Return usp_sum:'||l_usp_sum);
488: oe_debug_pub.add('Retro:Return ulp_sum:'||l_ulp_sum);
489: oe_debug_pub.add('Retro:Return USP:'||x_usp);
490: oe_debug_pub.add('Retro:Return ULP:'||x_ulp);
491: oe_debug_pub.add('Retro:Return ULP_PER_PQTY:'||x_ulp_ppqty);
492: oe_debug_pub.add('Retro:Return USP_PER_PQTY:'||x_usp_ppqty);
493: END IF;
494:
495: End;
488: oe_debug_pub.add('Retro:Return ulp_sum:'||l_ulp_sum);
489: oe_debug_pub.add('Retro:Return USP:'||x_usp);
490: oe_debug_pub.add('Retro:Return ULP:'||x_ulp);
491: oe_debug_pub.add('Retro:Return ULP_PER_PQTY:'||x_ulp_ppqty);
492: oe_debug_pub.add('Retro:Return USP_PER_PQTY:'||x_usp_ppqty);
493: END IF;
494:
495: End;
496:
544: null;
545: END;
546:
547:
548: oe_debug_pub.add('Retro:Tax code invalid');
549: FND_MESSAGE.SET_NAME('ONT','ONT_RETROBILL_TAX_INVALID');
550: FND_MESSAGE.SET_TOKEN('TAX_CODE',p_tax_code);
551: FND_MESSAGE.SET_TOKEN('ORDER',l_order_number);
552: FND_MESSAGE.SET_TOKEN('LINE',l_line_no);
552: FND_MESSAGE.SET_TOKEN('LINE',l_line_no);
553: OE_MSG_PUB.Add;
554: RETURN FALSE;
555: WHEN OTHERS THEN
556: oe_debug_pub.add('Retro:IS_TAX_CODE_VALID:'||SQLERRM);
557: RETURN FALSE;
558: End;
559:
560: --key header and line is to facilate an index search to oe_order_lines_all
576: And l.orig_sys_line_ref = to_char(p_key_line_id) --p_key_line_id --commented for bug#7665009
577: And retrobill_request_id <> G_CURRENT_RETROBILL_REQUEST_ID;
578: l_retro_line_id Number;
579: l_retro_header_id Number;
580: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
581: l_query_line_id Number;
582: l_query_header_id Number;
583:
584: Begin
588:
589: IF l_retro_line_id IS NULL THEN
590: --No previous retrobill line perform a reqular query_
591: --IF l_debug_level > 0 THEN
592: oe_debug_pub.add('Retro: no previous retrobill line');
593: --END IF;
594: l_query_line_id := p_key_line_id;
595: l_query_header_id := p_key_header_id;
596: x_retro_exists := FALSE; --bug3738043
598: l_query_line_id := l_retro_line_id;
599: l_query_header_id := l_retro_header_id;
600: x_retro_exists := TRUE; --bug3738043
601: --IF l_debug_level > 0 THEN
602: oe_debug_pub.add('Retro:Previoulsy Retrobilled, Get Most Recent Adj:retro line id:'||l_retro_line_id);
603: --END IF;
604: End IF;
605:
606:
604: End IF;
605:
606:
607: IF p_adjustment_level = 'HEADER' THEN
608: oe_debug_pub.add('RETRO:QUERY Order level adjustment');
609: OE_Line_Adj_Util.Query_Rows(p_header_Id => l_query_header_id
610: ,x_Line_Adj_Tbl => x_Line_Adj_Tbl);
611: ELSE
612: oe_debug_pub.add('RETRO:QUERY line level adjustment');
608: oe_debug_pub.add('RETRO:QUERY Order level adjustment');
609: OE_Line_Adj_Util.Query_Rows(p_header_Id => l_query_header_id
610: ,x_Line_Adj_Tbl => x_Line_Adj_Tbl);
611: ELSE
612: oe_debug_pub.add('RETRO:QUERY line level adjustment');
613: OE_Line_Adj_Util.Query_Rows(p_line_Id => l_query_line_id
614: ,x_Line_Adj_Tbl => x_Line_Adj_Tbl);
615: END IF;
616:
615: END IF;
616:
617: Exception
618: When Others Then
619: oe_debug_pub.add('Retro:'||SQLERRM);
620: END;
621:
622: /******************************************************************
623: To get a invoice id of the original line
641: and b.language = (select language_code from fnd_languages where installed_flag = 'B');
642:
643: --CONTEXT,ATTRIBUTE1,ATTRIBUTE2 and ATTRIBUTE6 will hit a concatenate index in AR table
644: l_ci_id NUMBER;
645: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
646: Begin
647: --should not be multiple, just in case
648: For i IN Get_CI_Line_Id Loop
649: l_ci_id:=i.customer_trx_line_id;
649: l_ci_id:=i.customer_trx_line_id;
650: End Loop;
651:
652: If l_debug_level > 0 Then
653: oe_debug_pub.add('Retro:Leaving get credit invoice id:'||l_ci_id);
654: End If;
655:
656: Return l_ci_id;
657:
692: --bug3738043 end
693:
694: l_line_adj_tbl OE_ORDER_PUB.LINE_ADJ_TBL_TYPE;
695: i PLS_INTEGER;
696: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
697: --bug3738043 start
698: l_new_price_adj_id_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
699: k PLS_INTEGER := 1 ;
700: l_found BOOLEAN := FALSE;
703: --bug3738043 end
704: Begin
705:
706: if l_debug_level > 0 then
707: oe_debug_pub.add('Entering oe_retro_pvt.preprocess_adjustments');
708: end if;
709:
710: --bug3738043 start
711: IF l_debug_level > 0 THEN
708: end if;
709:
710: --bug3738043 start
711: IF l_debug_level > 0 THEN
712: oe_debug_pub.add('PVIPRANA: retrobill header_id: ' || p_header_id);
713: oe_debug_pub.add('PVIPRANA: retrobill line_id: ' || p_line_id);
714: END IF;
715: --bug3738043 end
716:
709:
710: --bug3738043 start
711: IF l_debug_level > 0 THEN
712: oe_debug_pub.add('PVIPRANA: retrobill header_id: ' || p_header_id);
713: oe_debug_pub.add('PVIPRANA: retrobill line_id: ' || p_line_id);
714: END IF;
715: --bug3738043 end
716:
717: DELETE From OE_PRICE_ADJUSTMENTS
729: i:=l_line_adj_tbl.first;
730: WHILE I IS NOT NULL LOOP
731:
732: if l_debug_level > 0 then
733: 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);
734: oe_debug_pub.add(' operand queried:'||l_line_adj_tbl(i).operand||' operand_perpqty:'||l_line_adj_tbl(i).operand_per_pqty);
735: 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);
736: oe_debug_pub.add(' adj_amt_pqty:'||l_line_adj_tbl(i).adjusted_amount_per_pqty);
737: oe_debug_pub.add(' retrobill_request_id: '||l_line_adj_tbl(i).retrobill_request_id);
730: WHILE I IS NOT NULL LOOP
731:
732: if l_debug_level > 0 then
733: 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);
734: oe_debug_pub.add(' operand queried:'||l_line_adj_tbl(i).operand||' operand_perpqty:'||l_line_adj_tbl(i).operand_per_pqty);
735: 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);
736: oe_debug_pub.add(' adj_amt_pqty:'||l_line_adj_tbl(i).adjusted_amount_per_pqty);
737: oe_debug_pub.add(' retrobill_request_id: '||l_line_adj_tbl(i).retrobill_request_id);
738: end if;
731:
732: if l_debug_level > 0 then
733: 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);
734: oe_debug_pub.add(' operand queried:'||l_line_adj_tbl(i).operand||' operand_perpqty:'||l_line_adj_tbl(i).operand_per_pqty);
735: 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);
736: oe_debug_pub.add(' adj_amt_pqty:'||l_line_adj_tbl(i).adjusted_amount_per_pqty);
737: oe_debug_pub.add(' retrobill_request_id: '||l_line_adj_tbl(i).retrobill_request_id);
738: end if;
739: --bug3738043 adding the following condition
732: if l_debug_level > 0 then
733: 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);
734: oe_debug_pub.add(' operand queried:'||l_line_adj_tbl(i).operand||' operand_perpqty:'||l_line_adj_tbl(i).operand_per_pqty);
735: 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);
736: oe_debug_pub.add(' adj_amt_pqty:'||l_line_adj_tbl(i).adjusted_amount_per_pqty);
737: oe_debug_pub.add(' retrobill_request_id: '||l_line_adj_tbl(i).retrobill_request_id);
738: end if;
739: --bug3738043 adding the following condition
740: IF (l_retro_exists AND l_line_adj_tbl(i).retrobill_request_id IS NOT NULL) OR
733: 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);
734: oe_debug_pub.add(' operand queried:'||l_line_adj_tbl(i).operand||' operand_perpqty:'||l_line_adj_tbl(i).operand_per_pqty);
735: 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);
736: oe_debug_pub.add(' adj_amt_pqty:'||l_line_adj_tbl(i).adjusted_amount_per_pqty);
737: oe_debug_pub.add(' retrobill_request_id: '||l_line_adj_tbl(i).retrobill_request_id);
738: end if;
739: --bug3738043 adding the following condition
740: IF (l_retro_exists AND l_line_adj_tbl(i).retrobill_request_id IS NOT NULL) OR
741: NOT l_retro_exists THEN
757: FETCH retro_inv_list_line_id into l_dummy;
758: IF retro_inv_list_line_id%NOTFOUND THEN
759: IF l_line_adj_tbl(i).LIST_LINE_TYPE_CODE IN ('DIS', 'SUR', 'PBH') THEN
760: IF l_debug_level > 0 THEN
761: oe_debug_pub.add('PVIPRANA: Inserting Invalid list_line_id ' || l_line_adj_tbl(i).list_line_id);
762: END IF;
763:
764: l_line_adj_tbl(i).line_id := p_line_id;
765: l_line_adj_tbl(i).header_id := p_header_id;
781: END LOOP;
782:
783: --bug3738043 start
784: IF l_debug_level > 0 THEN
785: oe_debug_pub.add('PVIPRANA: G_RETRO_PRICING_PHASE_COUNT is ' || G_RETRO_PRICING_PHASE_COUNT);
786: END IF;
787:
788: IF nvl(G_RETRO_PRICING_PHASE_COUNT,0) > 0 THEN
789: IF l_debug_level > 0 THEN
786: END IF;
787:
788: IF nvl(G_RETRO_PRICING_PHASE_COUNT,0) > 0 THEN
789: IF l_debug_level > 0 THEN
790: oe_debug_pub.add('PVIPRANA: deleting the new adjustments with retrobill_request_id NOT null');
791: END IF;
792:
793:
794: FOR retro_list_line_id IN retro_list_line_ids LOOP
809: IF NOT l_found THEN
810: l_new_price_adj_id_tbl(k) := retro_list_line_id.price_adjustment_id;
811: k := k+1;
812: IF l_debug_level > 0 THEN
813: oe_debug_pub.add('PVIPRANA: new list_line_id ' || retro_list_line_id.list_line_id);
814: END IF;
815: END IF;
816:
817: END LOOP;
817: END LOOP;
818:
819:
820: IF l_debug_level > 0 THEN
821: oe_debug_pub.add('PVIPRANA: Deleting ' || (k-1) || ' new adjustments');
822: END IF;
823:
824: IF l_new_price_adj_id_tbl.FIRST IS NOT NULL THEN
825:
832: END IF;
833: --bug3738043 end
834:
835: if l_debug_level > 0 then
836: oe_debug_pub.add('Leaving oe_retro_pvt.preprocess_adjustments');
837: end if;
838: --bug3738043
839: Exception
840: when others then
837: end if;
838: --bug3738043
839: Exception
840: when others then
841: oe_debug_pub.add('Exception in Preprocess Adjustments ' || SQLERRM);
842:
843: End;
844:
845: /***************************************************************
858: And line_category_code = 'RETURN';
859:
860:
861: l_qty Number:=0;
862: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
863:
864: Begin
865:
866: Open Return_Lines;
871: --all curr qty are eligible for returns
872: Return p_curr_ordered_qty;
873: Else
874: If l_debug_level > 0 Then
875: oe_debug_pub.add('Retro:Returned Qty:'||l_qty);
876: oe_debug_pub.add('Retro:Retrobillable Qty:'|| p_curr_ordered_qty - l_qty);
877: End If;
878: Return p_curr_ordered_qty - l_qty;
879: End If;
872: Return p_curr_ordered_qty;
873: Else
874: If l_debug_level > 0 Then
875: oe_debug_pub.add('Retro:Returned Qty:'||l_qty);
876: oe_debug_pub.add('Retro:Retrobillable Qty:'|| p_curr_ordered_qty - l_qty);
877: End If;
878: Return p_curr_ordered_qty - l_qty;
879: End If;
880:
879: End If;
880:
881: Exception
882: When NO_DATA_FOUND Then
883: oe_debug_pub.add('Retro:No returns against line_id:'||p_line_id);
884: Return p_curr_ordered_qty;
885: End;
886:
887: /*************************************************************
888: This procdures update oe_retrobill_requests table
889: *************************************************************/
890: Procedure Update_Row(p_retrobill_request_rec IN OE_RETROBILL_REQUESTS%ROWTYPE) AS
891: Begin
892: oe_debug_pub.add('Retro:Entering Update retrobill request,request_id:'||p_retrobill_request_rec.retrobill_request_id);
893: UPDATE OE_RETROBILL_REQUESTS
894: SET
895: NAME = p_retrobill_request_rec.name
896: , DESCRIPTION = p_retrobill_request_rec.description
910: , PROGRAM_ID = p_retrobill_request_rec.program_id
911: , PROGRAM_UPDATED_DATE= p_retrobill_request_rec.program_updated_date
912: Where retrobill_request_id = p_retrobill_request_rec.retrobill_request_id;
913:
914: oe_debug_pub.add('Retro:Leaving Update retrobill request:'||SQL%ROWCOUNT||' updated');
915: Exception
916: When Others Then
917: oe_debug_pub.add('Retro:Update_Row:'||SQLERRM);
918: End;
913:
914: oe_debug_pub.add('Retro:Leaving Update retrobill request:'||SQL%ROWCOUNT||' updated');
915: Exception
916: When Others Then
917: oe_debug_pub.add('Retro:Update_Row:'||SQLERRM);
918: End;
919:
920: /**************************************************************
921: This procedure calls process order api to create headers and lines
982: );
983:
984: x_created_header_id:=l_x_header_rec.header_id;
985:
986: oe_debug_pub.add('no. of OE messages :'||x_msg_count);
987: for k in 1 .. x_msg_count loop
988: x_msg_data := oe_msg_pub.get( p_msg_index => k,
989: p_encoded => 'F'
990: );
987: for k in 1 .. x_msg_count loop
988: x_msg_data := oe_msg_pub.get( p_msg_index => k,
989: p_encoded => 'F'
990: );
991: oe_debug_pub.add(substr(x_msg_data,1,255));
992: oe_debug_pub.add('Error msg: '||substr(x_msg_data,1,200));
993: end loop;
994:
995: fnd_msg_pub.count_and_get( p_encoded => 'F'
988: x_msg_data := oe_msg_pub.get( p_msg_index => k,
989: p_encoded => 'F'
990: );
991: oe_debug_pub.add(substr(x_msg_data,1,255));
992: oe_debug_pub.add('Error msg: '||substr(x_msg_data,1,200));
993: end loop;
994:
995: fnd_msg_pub.count_and_get( p_encoded => 'F'
996: , p_count => x_msg_count
994:
995: fnd_msg_pub.count_and_get( p_encoded => 'F'
996: , p_count => x_msg_count
997: , p_data => x_msg_data);
998: --oe_debug_pub.add('no. of FND messages :'||x_msg_count,1);
999: oe_debug_pub.add('no. of FND messages :'||x_msg_count);
1000: for k in 1 .. x_msg_count loop
1001: x_msg_data := fnd_msg_pub.get( p_msg_index => k,
1002: p_encoded => 'F'
995: fnd_msg_pub.count_and_get( p_encoded => 'F'
996: , p_count => x_msg_count
997: , p_data => x_msg_data);
998: --oe_debug_pub.add('no. of FND messages :'||x_msg_count,1);
999: oe_debug_pub.add('no. of FND messages :'||x_msg_count);
1000: for k in 1 .. x_msg_count loop
1001: x_msg_data := fnd_msg_pub.get( p_msg_index => k,
1002: p_encoded => 'F'
1003: );
1000: for k in 1 .. x_msg_count loop
1001: x_msg_data := fnd_msg_pub.get( p_msg_index => k,
1002: p_encoded => 'F'
1003: );
1004: oe_debug_pub.add('Error msg: '||substr(x_msg_data,1,200));
1005: oe_debug_pub.add(substr(x_msg_data,1,255));
1006: end loop;
1007:
1008: oe_debug_pub.add('header id created:'||l_x_header_rec.header_id);
1001: x_msg_data := fnd_msg_pub.get( p_msg_index => k,
1002: p_encoded => 'F'
1003: );
1004: oe_debug_pub.add('Error msg: '||substr(x_msg_data,1,200));
1005: oe_debug_pub.add(substr(x_msg_data,1,255));
1006: end loop;
1007:
1008: oe_debug_pub.add('header id created:'||l_x_header_rec.header_id);
1009:
1004: oe_debug_pub.add('Error msg: '||substr(x_msg_data,1,200));
1005: oe_debug_pub.add(substr(x_msg_data,1,255));
1006: end loop;
1007:
1008: oe_debug_pub.add('header id created:'||l_x_header_rec.header_id);
1009:
1010: End;
1011:
1012:
1018: p_retrobill_tbl In RETROBILL_TBL_TYPE,
1019: p_retrobill_request_rec In OE_RETROBILL_REQUESTS%ROWTYPE,
1020: x_line_rec Out NOCOPY OE_ORDER_PUB.LINE_REC_TYPE) AS
1021: l_retrobillable_qty NUMBER;
1022: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1023: stmt NUMBER:=7.1;
1024: Begin
1025: --Copy Original Attributes to retrobill line
1026: x_line_rec.org_id :=p_oline_rec.org_id ;
1207: --If user trying to retrobill more than he is eligible to, set the quantity to eligible qty
1208: /* If p_retrobill_tbl(p_oline_rec.plsql_tbl_index).retrobill_qty > l_retrobillable_qty Then
1209: x_line_rec.ordered_quantity:=l_retrobillable_qty;
1210: If l_debug_level > 0 Then
1211: 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);
1212: End If;
1213: Else*/
1214: oe_debug_pub.add('Retro:plsql index'||p_oline_rec.plsql_tbl_index);
1215: x_line_rec.ordered_quantity:=p_retrobill_tbl(p_oline_rec.plsql_tbl_index).retrobill_qty;
1210: If l_debug_level > 0 Then
1211: 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);
1212: End If;
1213: Else*/
1214: oe_debug_pub.add('Retro:plsql index'||p_oline_rec.plsql_tbl_index);
1215: x_line_rec.ordered_quantity:=p_retrobill_tbl(p_oline_rec.plsql_tbl_index).retrobill_qty;
1216: --End If;
1217:
1218: x_line_rec.retrobill_request_id:=p_retrobill_request_rec.retrobill_request_id;
1223: --x_line_rec.Source_Document_shipment_ref:=p_retrobill_request_rec.retrobill_request_id;
1224: x_line_rec. ORIG_SYS_SHIPMENT_REF:=p_retrobill_request_rec.retrobill_request_id;
1225: stmt:=7.52;
1226: If l_debug_level > 0 Then
1227: oe_debug_pub.add('Retro:invoiced_quantity:'|| p_oline_rec.invoiced_quantity);
1228: End If;
1229:
1230: If p_oline_rec.invoiced_quantity > 0 Then
1231: x_line_rec.credit_invoice_line_id:=Get_Credit_Invoice_Line_Id(p_oline_rec.order_number
1241: --We try to copy over the tax code and date, but if it fails
1242: --in validation, we will need to ask the system to default the tax code and date
1243: --if we default, we will to tell users about this.
1244: 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
1245: oe_debug_pub.add('Retro:Old tax code is no longer valid, redaulting a new one');
1246: x_line_rec.tax_code:=FND_API.G_MISS_CHAR;
1247: stmt:=7.61;
1248: x_line_rec.tax_rate:=FND_API.G_MISS_NUM;
1249: stmt:=7.62;
1266:
1267: -- x_line_rec.ship_to_org_id:=FND_API.G_MISS_NUM; -- Commented for bug 5612169
1268:
1269: If l_debug_level > 0 Then
1270: oe_debug_pub.add('Retro: leaving prepare line');
1271: End If;
1272:
1273: Exception
1274: When Others then
1271: End If;
1272:
1273: Exception
1274: When Others then
1275: oe_debug_pub.add('Exception occured at statement:'||stmt||':'||SQLERRM);
1276: raise;
1277: End;
1278:
1279: PROCEDURE Prepare_Header(p_cust_po_number IN VARCHAR2, -- Bug# 6603714
1311: x_header_rec.flow_status_code :='ENTERED';
1312: x_header_rec.order_source_id :=G_RETROBILL_ORDER_SOURCE_ID;
1313: x_header_rec.orig_sys_document_ref :=p_retrobill_request_rec.retrobill_request_id;
1314: x_header_rec.cust_po_number :=p_cust_po_number; -- Bug# 6603714
1315: oe_debug_pub.add('x_header_rec.cust_po_number'||x_header_rec.cust_po_number);
1316:
1317: If p_retrobill_request_rec.order_type_id IS NULL Then
1318: null;
1319: --get order type id from sys parameter
1336: );
1337:
1338: IF l_pl_tbl.First IS NOT NULL THEN
1339: l_valid_price_list_id := l_pl_tbl(l_pl_tbl.First).price_list_id;
1340: oe_debug_pub.add('RETRO:Valid Price List to be defaulted is:'||l_pl_tbl(l_pl_tbl.First).price_list_id);
1341: ELSE
1342: oe_debug_pub.add('RETRO:ERROR:UNABLE TO DEFAULT A VALID HEADER PRICE LIST');
1343: END IF;
1344: x_header_rec.price_list_id:=l_valid_price_list_id;
1338: IF l_pl_tbl.First IS NOT NULL THEN
1339: l_valid_price_list_id := l_pl_tbl(l_pl_tbl.First).price_list_id;
1340: oe_debug_pub.add('RETRO:Valid Price List to be defaulted is:'||l_pl_tbl(l_pl_tbl.First).price_list_id);
1341: ELSE
1342: oe_debug_pub.add('RETRO:ERROR:UNABLE TO DEFAULT A VALID HEADER PRICE LIST');
1343: END IF;
1344: x_header_rec.price_list_id:=l_valid_price_list_id;
1345: END IF; --end if for g_int_use_any_valid_pl
1346:
1343: END IF;
1344: x_header_rec.price_list_id:=l_valid_price_list_id;
1345: END IF; --end if for g_int_use_any_valid_pl
1346:
1347: oe_debug_pub.add('Retro:Order Type id:'||x_header_rec.order_type_id);
1348: oe_debug_pub.add('Retro:sold to org id:'||x_header_rec.sold_to_org_id);
1349: oe_debug_pub.add('Retro:currency:'||x_header_rec.transactional_curr_code);
1350: End;
1351:
1344: x_header_rec.price_list_id:=l_valid_price_list_id;
1345: END IF; --end if for g_int_use_any_valid_pl
1346:
1347: oe_debug_pub.add('Retro:Order Type id:'||x_header_rec.order_type_id);
1348: oe_debug_pub.add('Retro:sold to org id:'||x_header_rec.sold_to_org_id);
1349: oe_debug_pub.add('Retro:currency:'||x_header_rec.transactional_curr_code);
1350: End;
1351:
1352: PROCEDURE Insert_Id(p_retrobill_tbl IN RETROBILL_TBL_TYPE) As
1345: END IF; --end if for g_int_use_any_valid_pl
1346:
1347: oe_debug_pub.add('Retro:Order Type id:'||x_header_rec.order_type_id);
1348: oe_debug_pub.add('Retro:sold to org id:'||x_header_rec.sold_to_org_id);
1349: oe_debug_pub.add('Retro:currency:'||x_header_rec.transactional_curr_code);
1350: End;
1351:
1352: PROCEDURE Insert_Id(p_retrobill_tbl IN RETROBILL_TBL_TYPE) As
1353: l_id_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
1375: END IF;
1376:
1377: Exception
1378: When Others Then
1379: oe_debug_pub.add('Execption occured in Oe_Retrobill_Pvt.Insert_Id:'||SQLERRM);
1380: Raise;
1381: End;
1382:
1383: /**************************************************************
1391: l_row_count NUMBER := 0;
1392: Begin
1393:
1394: -- query the attributes table OE_ORDER_PRICE_ATTRIBS and assign it to the record structure
1395: oe_debug_pub.add('Enter procedure Oe_Retrobill_Pvt.Prepare_Line_Pricing_Attribs:');
1396:
1397: SELECT
1398: pricing_context,
1399: pricing_attribute1,
1643:
1644: l_row_count := SQL%ROWCOUNT;
1645: x_row_count := l_row_count;
1646:
1647: oe_debug_pub.add('Number of records: ' || l_row_count);
1648: oe_debug_pub.add('Exiting procedure Oe_Retrobill_Pvt.Prepare_Line_Pricing_Attribs:');
1649:
1650: Exception
1651:
1644: l_row_count := SQL%ROWCOUNT;
1645: x_row_count := l_row_count;
1646:
1647: oe_debug_pub.add('Number of records: ' || l_row_count);
1648: oe_debug_pub.add('Exiting procedure Oe_Retrobill_Pvt.Prepare_Line_Pricing_Attribs:');
1649:
1650: Exception
1651:
1652: WHEN NO_DATA_FOUND THEN
1649:
1650: Exception
1651:
1652: WHEN NO_DATA_FOUND THEN
1653: oe_debug_pub.add('Line pricing attributes not populated for the original Order');
1654: x_row_count := 0;
1655:
1656: When Others Then
1657: oe_debug_pub.add('Execption occured in Oe_Retrobill_Pvt.Prepare_Line_Pricing_Attribs:'||SQLERRM);
1653: oe_debug_pub.add('Line pricing attributes not populated for the original Order');
1654: x_row_count := 0;
1655:
1656: When Others Then
1657: oe_debug_pub.add('Execption occured in Oe_Retrobill_Pvt.Prepare_Line_Pricing_Attribs:'||SQLERRM);
1658: Raise;
1659: End;
1660:
1661:
1702: p_retrobill_request_rec.program_updated_date);
1703:
1704: Exception
1705: When Others Then
1706: oe_debug_pub.add('RETRO error:'||SQLERRM);
1707: oe_debug_pub.add('RETRO:INSERT_RETROBILL_REQUEST:'||SQLERRM);
1708: End;
1709:
1710:
1703:
1704: Exception
1705: When Others Then
1706: oe_debug_pub.add('RETRO error:'||SQLERRM);
1707: oe_debug_pub.add('RETRO:INSERT_RETROBILL_REQUEST:'||SQLERRM);
1708: End;
1709:
1710:
1711:
1791: l_debit Varchar2(1);
1792: j Number:=0;
1793: l_control_rec OE_GLOBALS.Control_Rec_Type;
1794: l_return_status Varchar2(15);
1795: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1796: --skubendr{
1797: l_inventory_item_id_previous NUMBER;
1798: l_unique_item BOOLEAN := TRUE;
1799: l_sold_to_org_id_previous NUMBER;
1804: l_line_category_code VARCHAR2(100); --Bug# 8609475
1805:
1806: BEGIN
1807:
1808: oe_debug_pub.add('Entering update retrobill lines : p_operation = '||p_operation);
1809: --GSCC Not initializing during declaration
1810: l_debit := 'N';
1811: l_inventory_item_id_previous := g_retrobill_request_rec.inventory_item_id;
1812: l_sold_to_org_id_previous := g_retrobill_request_rec.sold_to_org_id;
1829: open get_original_price(I.orig_sys_line_ref);
1830: fetch get_original_price into l_orig_usp,l_orig_ulp;
1831: close get_original_price;
1832: IF l_debug_level > 0 THEN
1833: Oe_Debug_Pub.Add('Retro:orig_usp:'||l_orig_usp||',orig_ulp:'||l_orig_ulp);
1834: END IF;
1835: l_retrobill_selling_price := l_orig_usp + l_usp_sum - I.NEW_UNIT_SELLING_PRICE;
1836: l_retrobill_list_price := l_orig_ulp + l_ulp_sum - I.NEW_UNIT_LIST_PRICE;
1837: END IF;
1836: l_retrobill_list_price := l_orig_ulp + l_ulp_sum - I.NEW_UNIT_LIST_PRICE;
1837: END IF;
1838:
1839: IF l_debug_level > 0 THEN
1840: oe_debug_pub.add('retro-old usp:'||I.OLD_UNIT_SELLING_PRICE||' new usp:'||I.NEW_UNIT_SELLING_PRICE);
1841: oe_debug_pub.add('retro:usp sum'||l_usp_sum);
1842: oe_debug_pub.add('retro:retrobill selling_price:'|| l_retrobill_selling_price);
1843:
1844: oe_debug_pub.add('retro-old ulp:'||I.OLD_UNIT_LIST_PRICE||' new ULP:'||I.NEW_UNIT_LIST_PRICE);
1837: END IF;
1838:
1839: IF l_debug_level > 0 THEN
1840: oe_debug_pub.add('retro-old usp:'||I.OLD_UNIT_SELLING_PRICE||' new usp:'||I.NEW_UNIT_SELLING_PRICE);
1841: oe_debug_pub.add('retro:usp sum'||l_usp_sum);
1842: oe_debug_pub.add('retro:retrobill selling_price:'|| l_retrobill_selling_price);
1843:
1844: oe_debug_pub.add('retro-old ulp:'||I.OLD_UNIT_LIST_PRICE||' new ULP:'||I.NEW_UNIT_LIST_PRICE);
1845: oe_debug_pub.add('retro:ulp sum'||l_ulp_sum);
1838:
1839: IF l_debug_level > 0 THEN
1840: oe_debug_pub.add('retro-old usp:'||I.OLD_UNIT_SELLING_PRICE||' new usp:'||I.NEW_UNIT_SELLING_PRICE);
1841: oe_debug_pub.add('retro:usp sum'||l_usp_sum);
1842: oe_debug_pub.add('retro:retrobill selling_price:'|| l_retrobill_selling_price);
1843:
1844: oe_debug_pub.add('retro-old ulp:'||I.OLD_UNIT_LIST_PRICE||' new ULP:'||I.NEW_UNIT_LIST_PRICE);
1845: oe_debug_pub.add('retro:ulp sum'||l_ulp_sum);
1846: oe_debug_pub.add('retro:retrobill list_price:'|| l_retrobill_list_price);
1840: oe_debug_pub.add('retro-old usp:'||I.OLD_UNIT_SELLING_PRICE||' new usp:'||I.NEW_UNIT_SELLING_PRICE);
1841: oe_debug_pub.add('retro:usp sum'||l_usp_sum);
1842: oe_debug_pub.add('retro:retrobill selling_price:'|| l_retrobill_selling_price);
1843:
1844: oe_debug_pub.add('retro-old ulp:'||I.OLD_UNIT_LIST_PRICE||' new ULP:'||I.NEW_UNIT_LIST_PRICE);
1845: oe_debug_pub.add('retro:ulp sum'||l_ulp_sum);
1846: oe_debug_pub.add('retro:retrobill list_price:'|| l_retrobill_list_price);
1847: END IF;
1848:
1841: oe_debug_pub.add('retro:usp sum'||l_usp_sum);
1842: oe_debug_pub.add('retro:retrobill selling_price:'|| l_retrobill_selling_price);
1843:
1844: oe_debug_pub.add('retro-old ulp:'||I.OLD_UNIT_LIST_PRICE||' new ULP:'||I.NEW_UNIT_LIST_PRICE);
1845: oe_debug_pub.add('retro:ulp sum'||l_ulp_sum);
1846: oe_debug_pub.add('retro:retrobill list_price:'|| l_retrobill_list_price);
1847: END IF;
1848:
1849: -------- Bug# 8609475 : Start -------
1842: oe_debug_pub.add('retro:retrobill selling_price:'|| l_retrobill_selling_price);
1843:
1844: oe_debug_pub.add('retro-old ulp:'||I.OLD_UNIT_LIST_PRICE||' new ULP:'||I.NEW_UNIT_LIST_PRICE);
1845: oe_debug_pub.add('retro:ulp sum'||l_ulp_sum);
1846: oe_debug_pub.add('retro:retrobill list_price:'|| l_retrobill_list_price);
1847: END IF;
1848:
1849: -------- Bug# 8609475 : Start -------
1850: select line_category_code into l_line_category_code
1850: select line_category_code into l_line_category_code
1851: from oe_order_lines_all
1852: where line_id = to_number(I.orig_sys_line_ref);
1853:
1854: oe_debug_pub.add(' line_category_code of original order: l_line_category_code = '|| l_line_category_code);
1855:
1856: if l_line_category_code = 'RETURN' then
1857: l_retrobill_list_price := l_retrobill_list_price * -1;
1858: l_retrobill_selling_price := l_retrobill_selling_price * -1;
1856: if l_line_category_code = 'RETURN' then
1857: l_retrobill_list_price := l_retrobill_list_price * -1;
1858: l_retrobill_selling_price := l_retrobill_selling_price * -1;
1859: end if;
1860: oe_debug_pub.add(' l_retrobill_selling_price = '|| l_retrobill_selling_price);
1861: oe_debug_pub.add(' l_retrobill_list_price = ' || l_retrobill_list_price);
1862: -------- Bug# 8609475 : end -------
1863:
1864: --Handle the line as a 'return' because new price > old price + previously retrobilled amount
1857: l_retrobill_list_price := l_retrobill_list_price * -1;
1858: l_retrobill_selling_price := l_retrobill_selling_price * -1;
1859: end if;
1860: oe_debug_pub.add(' l_retrobill_selling_price = '|| l_retrobill_selling_price);
1861: oe_debug_pub.add(' l_retrobill_list_price = ' || l_retrobill_list_price);
1862: -------- Bug# 8609475 : end -------
1863:
1864: --Handle the line as a 'return' because new price > old price + previously retrobilled amount
1865: --bug3654144
1865: --bug3654144
1866: IF l_retrobill_list_price > 0 OR
1867: (l_retrobill_list_price = 0 AND l_retrobill_selling_price > 0)
1868: THEN
1869: oe_debug_pub.add('Retro: New price is lower giving credit');
1870: UPDATE OE_ORDER_LINES_all l
1871: SET UNIT_SELLING_PRICE =l_retrobill_selling_price
1872: ,UNIT_LIST_PRICE =l_retrobill_list_price
1873: ,UNIT_SELLING_PRICE_PER_PQTY=I.UNIT_SELLING_PRICE_PER_PQTY
1882: ,CALCULATE_PRICE_FLAG ='N'
1883: ,LOCK_CONTROL =I.LOCK_CONTROL
1884: WHERE l.line_id = I.line_id;
1885: --What about update global??? Need to think about it...
1886: oe_debug_pub.add('retro:updated row number:'|| SQL%ROWCOUNT||'line_id:'|| I.line_id);
1887:
1888: --Handle the line as a 'buy' (invoice customer) if new price is higher
1889: --This case will be a little complex because we are changing line_category_code
1890: --by doing so, many line attributes will need to be redefaulted.
1892: --bug3654144
1893: ELSIF l_retrobill_list_price < 0 OR
1894: (l_retrobill_list_price = 0 AND l_retrobill_selling_price < 0)
1895: THEN
1896: oe_debug_pub.add('Retro: New price is higher charging more');
1897:
1898: l_line_rec:=Oe_Line_Util.Query_Row(I.line_id);
1899:
1900: oe_debug_pub.add(' Retro-0: l_line_rec.payment_term_id = '||l_line_rec.payment_term_id
1896: oe_debug_pub.add('Retro: New price is higher charging more');
1897:
1898: l_line_rec:=Oe_Line_Util.Query_Row(I.line_id);
1899:
1900: oe_debug_pub.add(' Retro-0: l_line_rec.payment_term_id = '||l_line_rec.payment_term_id
1901: || ' l_line_rec.price_list_id = ' || l_line_rec.price_list_id
1902: || ' l_line_rec.line_type_id = ' || l_line_rec.line_type_id
1903: || ' g_retrobill_request_rec.order_type_id = ' || g_retrobill_request_rec.order_type_id
1904: ) ;
1931:
1932: l_line_rec.price_list_id :=I.PRICE_LIST_ID;
1933: l_old_line_rec.price_list_id :=I.PRICE_LIST_ID;
1934:
1935: oe_debug_pub.add('Price list id after setting it back:' || l_line_rec.price_list_id);
1936: oe_debug_pub.add('Old price list id:'||l_old_line_rec.price_list_id);
1937: oe_debug_pub.add(' Retro-1: l_line_rec.payment_term_id = '||l_line_rec.payment_term_id
1938: || ' l_line_rec.price_list_id = ' || l_line_rec.price_list_id
1939: || ' l_line_rec.line_type_id = ' || l_line_rec.line_type_id
1932: l_line_rec.price_list_id :=I.PRICE_LIST_ID;
1933: l_old_line_rec.price_list_id :=I.PRICE_LIST_ID;
1934:
1935: oe_debug_pub.add('Price list id after setting it back:' || l_line_rec.price_list_id);
1936: oe_debug_pub.add('Old price list id:'||l_old_line_rec.price_list_id);
1937: oe_debug_pub.add(' Retro-1: l_line_rec.payment_term_id = '||l_line_rec.payment_term_id
1938: || ' l_line_rec.price_list_id = ' || l_line_rec.price_list_id
1939: || ' l_line_rec.line_type_id = ' || l_line_rec.line_type_id
1940: ) ;
1933: l_old_line_rec.price_list_id :=I.PRICE_LIST_ID;
1934:
1935: oe_debug_pub.add('Price list id after setting it back:' || l_line_rec.price_list_id);
1936: oe_debug_pub.add('Old price list id:'||l_old_line_rec.price_list_id);
1937: oe_debug_pub.add(' Retro-1: l_line_rec.payment_term_id = '||l_line_rec.payment_term_id
1938: || ' l_line_rec.price_list_id = ' || l_line_rec.price_list_id
1939: || ' l_line_rec.line_type_id = ' || l_line_rec.line_type_id
1940: ) ;
1941:
1945: --If the selling price is the same then we remove the created line
1946: --skubendr{
1947: l_current_line_deleted := 'Y';
1948: --skubendr}
1949: oe_debug_pub.add('Retro:No price difference,delete line_id:'||I.line_id);
1950:
1951: l_deleted_line_hdr := I.header_id;
1952:
1953: IF(I.line_number =1) THEN
2007: --deletion occured, need to resequence line number.
2008: END IF;
2009:
2010: IF l_deleted_line_hdr IS NOT NULL THEN
2011: oe_debug_pub.add('Retro:before line_num');
2012: FOR N IN line_number(l_deleted_line_hdr) LOOP
2013: l_line_num_tbl(k):= k;
2014: l_line_id_tbl(k):= N.LINE_ID;
2015: IF(G_FIRST_LINE_DELETED='Y' and k=1) THEN
2018: WHERE line_id=N.LINE_ID;
2019: END IF;
2020: k:=k+1;
2021: END LOOP;
2022: oe_debug_pub.add('Retro:before update line_num');
2023: IF l_line_id_tbl.FIRST IS NOT NULL THEN
2024: FORALL K IN l_line_id_tbl.FIRST..l_line_id_tbl.LAST
2025: UPDATE OE_ORDER_LINES_ALL
2026: SET LINE_NUMBER = l_line_num_tbl(K)
2025: UPDATE OE_ORDER_LINES_ALL
2026: SET LINE_NUMBER = l_line_num_tbl(K)
2027: WHERE LINE_ID = l_line_id_tbl(K);
2028: END IF;
2029: oe_debug_pub.add('Retro:after update line_num'||SQL%ROWCOUNT);
2030: END IF;
2031: --skubendr{
2032: IF(l_unique_item = FALSE) THEN
2033: g_retrobill_request_rec.inventory_item_id := NULL;
2034: END IF;
2035: IF(l_unique_customer = FALSE) THEN
2036: g_retrobill_request_rec.sold_to_org_id := NULL;
2037: END IF;
2038: oe_debug_pub.add('Customer id before inserting'||g_retrobill_request_rec.sold_to_org_id);
2039: oe_debug_pub.add('Inventory item id before inserting'||g_retrobill_request_rec.inventory_item_id);
2040: --skubendr}
2041: End;
2042:
2035: IF(l_unique_customer = FALSE) THEN
2036: g_retrobill_request_rec.sold_to_org_id := NULL;
2037: END IF;
2038: oe_debug_pub.add('Customer id before inserting'||g_retrobill_request_rec.sold_to_org_id);
2039: oe_debug_pub.add('Inventory item id before inserting'||g_retrobill_request_rec.inventory_item_id);
2040: --skubendr}
2041: End;
2042:
2043:
2042:
2043:
2044:
2045: PROCEDURE Insert_diff_Adj As
2046: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2047:
2048: cursor l_debug_cur IS
2049: select operand,operand_per_pqty,list_line_id,applied_flag,arithmetic_operator,updated_flag,adjusted_amount,adjusted_amount_per_pqty
2050: from oe_price_adjustments
2054:
2055: IF l_debug_level > 2 THEN
2056: --will be very slow if debug on fts will happen
2057: for k in l_debug_cur loop
2058: oe_debug_pub.add('operand:'||k.operand||'operand_per_pqty:'||k.operand_per_pqty||'list_line_id:'||k.list_line_id||
2059: 'applied flag:'||k.applied_flag);
2060: 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);
2061:
2062: end loop;
2056: --will be very slow if debug on fts will happen
2057: for k in l_debug_cur loop
2058: oe_debug_pub.add('operand:'||k.operand||'operand_per_pqty:'||k.operand_per_pqty||'list_line_id:'||k.list_line_id||
2059: 'applied flag:'||k.applied_flag);
2060: 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);
2061:
2062: end loop;
2063: END IF;
2064:
2297: AND ldets.list_line_id = oepj.list_line_id
2298: AND oepj.retrobill_request_id = G_CURRENT_RETROBILL_REQUEST_ID
2299: );
2300:
2301: oe_debug_pub.add('Retro:'||SQL%ROWCOUNT||' inserted, Retrobill id:'||G_CURRENT_RETROBILL_REQUEST_ID);
2302: Exception
2303: WHEN OTHERS THEN
2304: IF l_debug_level > 0 THEN
2305: oe_debug_pub.add('RETRO:ERROR in creating offset adjustments'||sqlerrm);
2301: oe_debug_pub.add('Retro:'||SQL%ROWCOUNT||' inserted, Retrobill id:'||G_CURRENT_RETROBILL_REQUEST_ID);
2302: Exception
2303: WHEN OTHERS THEN
2304: IF l_debug_level > 0 THEN
2305: oe_debug_pub.add('RETRO:ERROR in creating offset adjustments'||sqlerrm);
2306: END IF;
2307: Raise FND_API.G_EXC_ERROR;
2308: END Insert_diff_Adj;
2309:
2425: --bug3417428
2426: --and ldets2.line_index = adj.header_id+nvl(adj.line_id,0))
2427: and lines2.line_id=adj.line_id)
2428: and adj.retrobill_request_id = G_CURRENT_RETROBILL_REQUEST_ID;
2429: oe_debug_pub.add('Retro:'||SQL%ROWCOUNT||' updated');
2430: Exception
2431: WHEN OTHERS THEN
2432:
2433: oe_debug_pub.add('Retro:ERROR in updating adjustments'||sqlerrm);
2429: oe_debug_pub.add('Retro:'||SQL%ROWCOUNT||' updated');
2430: Exception
2431: WHEN OTHERS THEN
2432:
2433: oe_debug_pub.add('Retro:ERROR in updating adjustments'||sqlerrm);
2434:
2435: --Raise FND_API.G_EXC_ERROR;
2436: End;
2437:
2436: End;
2437:
2438: --bug3654144 Adding a new procedure to insert adjustment lines corresponding to new modifiers returns by pricing engine.
2439: PROCEDURE Insert_New_Adj As
2440: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2441: Begin
2442: INSERT INTO OE_PRICE_ADJUSTMENTS
2443: ( PRICE_ADJUSTMENT_ID
2444: , CREATION_DATE
2669: -- AND (l_booked_flag = 'N' or ldets.list_line_type_code<>'IUE')
2670: );
2671:
2672: IF l_debug_level > 0 THEN
2673: oe_debug_pub.add( 'pviprana INSERTED '||SQL%ROWCOUNT||' NEW ADJUSTMENTS' ) ;
2674: END IF;
2675:
2676: INSERT INTO OE_PRICE_ADJ_ASSOCS
2677: ( PRICE_ADJUSTMENT_ID
2729:
2730:
2731:
2732: IF l_debug_level > 0 THEN
2733: oe_debug_pub.add( 'pviprana INSERTED '||SQL%ROWCOUNT||' NEW PRICE ADJ ASSOCS' , 3 ) ;
2734: END IF;
2735:
2736:
2737: INSERT INTO OE_PRICE_ADJUSTMENTS
2963: -- AND (l_booked_flag = 'N' or ldets.list_line_type_code<>'IUE')
2964: );
2965:
2966: IF l_debug_level > 0 THEN
2967: oe_debug_pub.add( 'pviprana INSERTED '||SQL%ROWCOUNT||' new DIFF ADJUSTMENTS' ) ;
2968: END IF;
2969:
2970:
2971: Exception
2972: WHEN NO_DATA_FOUND THEN
2973: null;
2974: WHEN OTHERS THEN
2975: IF l_debug_level > 0 THEN
2976: oe_debug_pub.add('RETRO:ERROR in creating new offset adjustments'||sqlerrm);
2977: END IF;
2978: Raise FND_API.G_EXC_ERROR;
2979: END Insert_New_Adj;
2980:
3001: AND nvl(automatic_flag,'N') = 'Y'
3002: AND nvl(list_line_type_code,'TAX') NOT IN ('TAX', 'FREIGHT_CHARGE')
3003: ORDER BY line_id;
3004:
3005: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3006: l_price_adjustment_id NUMBER;
3007: l_updated_inv_adj_count PLS_INTEGER := 0;
3008: l_last_line_id NUMBER;
3009: l_line_category_code VARCHAR2(30);
3012: --GSCC not initializing during declaration
3013: l_last_line_id := -1;
3014: FOR invalid_price_adj IN invalid_price_adjs LOOP
3015: IF l_debug_level > 0 THEN
3016: oe_debug_pub.add('pviprana: Invalid adj list line id : '||invalid_price_adj.list_line_id);
3017: oe_debug_pub.add('pviprana: Invalid adj price_adjustment_id : '||invalid_price_adj.price_adjustment_id);
3018: oe_debug_pub.add('pviprana: Invalid adj list_line_type_code : '||invalid_price_adj.list_line_type_code);
3019: END IF;
3020:
3013: l_last_line_id := -1;
3014: FOR invalid_price_adj IN invalid_price_adjs LOOP
3015: IF l_debug_level > 0 THEN
3016: oe_debug_pub.add('pviprana: Invalid adj list line id : '||invalid_price_adj.list_line_id);
3017: oe_debug_pub.add('pviprana: Invalid adj price_adjustment_id : '||invalid_price_adj.price_adjustment_id);
3018: oe_debug_pub.add('pviprana: Invalid adj list_line_type_code : '||invalid_price_adj.list_line_type_code);
3019: END IF;
3020:
3021: IF l_last_line_id <> invalid_price_adj.line_id THEN
3014: FOR invalid_price_adj IN invalid_price_adjs LOOP
3015: IF l_debug_level > 0 THEN
3016: oe_debug_pub.add('pviprana: Invalid adj list line id : '||invalid_price_adj.list_line_id);
3017: oe_debug_pub.add('pviprana: Invalid adj price_adjustment_id : '||invalid_price_adj.price_adjustment_id);
3018: oe_debug_pub.add('pviprana: Invalid adj list_line_type_code : '||invalid_price_adj.list_line_type_code);
3019: END IF;
3020:
3021: IF l_last_line_id <> invalid_price_adj.line_id THEN
3022: BEGIN
3025: WHERE line_id = invalid_price_adj.line_id;
3026: EXCEPTION
3027: WHEN OTHERS THEN
3028: IF l_debug_level > 0 THEN
3029: oe_debug_pub.add('RETRO:ERROR in creating offset adjustments'||sqlerrm);
3030: END IF;
3031: Raise FND_API.G_EXC_ERROR;
3032: END;
3033: l_last_line_id := invalid_price_adj.line_id;
3073:
3074: END LOOP;
3075:
3076: IF l_debug_level > 0 THEN
3077: oe_debug_pub.add( 'pviprana UPDATED '|| l_updated_inv_adj_count ||' INVALID DIFF ADJUSTMENTS' ) ;
3078: END IF;
3079:
3080: EXCEPTION
3081: WHEN NO_DATA_FOUND THEN
3078: END IF;
3079:
3080: EXCEPTION
3081: WHEN NO_DATA_FOUND THEN
3082: oe_debug_pub.add('pviprana: Exception: NO_DATA_FOUND');
3083: WHEN OTHERS THEN
3084: oe_debug_pub.add('pviprana: Exception: '||SQLERRM);
3085:
3086: END;
3080: EXCEPTION
3081: WHEN NO_DATA_FOUND THEN
3082: oe_debug_pub.add('pviprana: Exception: NO_DATA_FOUND');
3083: WHEN OTHERS THEN
3084: oe_debug_pub.add('pviprana: Exception: '||SQLERRM);
3085:
3086: END;
3087:
3088: --RT{
3090: --pack I code about qp structure.
3091: Procedure Process_Retrobill_Adjustments(p_operation IN VARCHAR2) As
3092:
3093: Begin
3094: oe_debug_pub.add('Retro:Entering Process_Retrobill_Adjustments');
3095: --case 1, Retrobill does not have adjustment phases, only list price phase.
3096: --ideally in this case only adjustment amount(% discount) could have changed,operand should be the same
3097:
3098: --insert the offset adjustments, this is always needed in both cases
3108:
3109: --bug3654144
3110: Insert_New_Adj;
3111:
3112: oe_debug_pub.add('Retro:Leaving Process_Retrobill_Adjustments');
3113:
3114: End Process_Retrobill_Adjustments;
3115: --RT}
3116:
3141: l_line_tbl OE_ORDER_PUB.LINE_TBL_TYPE;
3142: l_return_status VARCHAR2(15);
3143: l_msg_count NUMBER;
3144: l_msg_data VARCHAR2(2000);
3145: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3146: Cursor unique_header_id IS
3147: SELECT DISTINCT KEY_ID Header_Id
3148: FROM OM_ID_LIST_TMP;
3149:
3168:
3169: l_header_id_tbl(n_index):=p_retrobill_tbl(i).retrobill_header_id;
3170:
3171: /*IF l_debug_level > 0 THEN
3172: oe_debug_pub.add('Retro:retrobill_header_id:'||nvl(p_retrobill_tbl(i).retrobill_header_id,'NULL'));
3173: END IF;*/
3174:
3175: IF nvl(p_retrobill_tbl(i).operation,'NULL') = 'UPDATE' THEN
3176: l_line_id_tbl(upd_index):=p_retrobill_tbl(i).retrobill_line_id;
3185:
3186: --We need to delete first, otherwise, we will be unable to delete booked records.
3187: i:=l_delete_tbl.first;
3188: WHILE i IS NOT NULL LOOP
3189: oe_debug_pub.add('Retro:Operation DELETE:deleting line id:'||l_delete_tbl(i).retrobill_line_id);
3190: Oe_Line_Util.Delete_Row(p_line_id=>l_delete_tbl(i).retrobill_line_id);
3191: i:=l_delete_tbl.next(i);
3192: END LOOP;
3193:
3207: l_arrange_line_num_tbl(j):= j;
3208: l_arrange_line_id_tbl(j):= N.LINE_ID;
3209: j:=j+1;
3210: END LOOP;
3211: oe_debug_pub.add('Retro:before update line_num');
3212: IF l_arrange_line_id_tbl.FIRST IS NOT NULL THEN
3213: FORALL i IN l_arrange_line_id_tbl.FIRST..l_arrange_line_id_tbl.LAST
3214: UPDATE OE_ORDER_LINES_ALL
3215: SET LINE_NUMBER = l_arrange_line_num_tbl(i)
3214: UPDATE OE_ORDER_LINES_ALL
3215: SET LINE_NUMBER = l_arrange_line_num_tbl(i)
3216: WHERE LINE_ID = l_arrange_line_id_tbl(i);
3217: END IF;
3218: oe_debug_pub.add('Retro:after update line_num');
3219: END LOOP;
3220:
3221: --handle mass update by joing to OM_ID_LIST_TMP
3222: DELETE FROM OM_ID_LIST_TMP;
3224: IF l_line_id_tbl.FIRST IS NOT NULL THEN
3225: FORALL j IN l_line_id_tbl.FIRST..l_line_id_tbl.LAST
3226: INSERT INTO OM_ID_LIST_TMP(KEY_ID,VALUE)
3227: VALUES (l_line_id_tbl(j),l_retrobill_qty_tbl(j));
3228: oe_debug_pub.add('Retro:execution mode='||p_execution_mode);
3229:
3230: --pricing quantity derivation might be wrong need to get back to this!!!
3231: UPDATE OE_ORDER_LINES_ALL oeol
3232: SET (ordered_quantity,
3252:
3253: --execute means book the orders
3254: IF p_execution_mode = 'EXECUTE' THEN
3255: FOR k in unique_header_id LOOP
3256: oe_debug_pub.add('Booking header id:'||k.header_id);
3257: -- Progress the workflow so that booking process is kicked off.
3258: -- This call should come back with a message OE_ORDER_BOOKED
3259: -- if booking completed successfully and if booking was deferred,
3260: -- message OE_ORDER_BOOK_DEFERRED is added to the stack.
3303:
3304: --bug3738043 end
3305:
3306: FOR k in unique_header_id LOOP
3307: oe_debug_pub.add('Retro:Repricing Order: header id:'||k.header_id);
3308: --cal price flag from retrobilling lines are 'N', set them to 'Y' before
3309: --reprice and then set them back to 'N'
3310: UPDATE OE_ORDER_LINES_ALL
3311: SET calculate_price_flag='Y'
3316: ,p_Request_Type_code=> 'ONT'
3317: ,p_Control_rec => l_Price_Control_Rec
3318: ,p_write_to_db => TRUE
3319: ,x_line_Tbl => l_Line_Tbl);
3320: oe_debug_pub.add('Retro:return status from price_line:'||l_Return_Status);
3321:
3322: --bug5003256 start
3323: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3324: x_error_count := x_error_count + l_line_tbl.count;
3344: l_return_status VARCHAR2(1);
3345: l_msg_count NUMBER;
3346: l_msg_data VARCHAR2(5000);
3347: BEGIN
3348: oe_debug_pub.add('Entering procedure Delete_Order');
3349: --bug5003256
3350: x_header_deleted := FALSE;
3351: select count(*) into l_line_count from oe_order_lines_all where header_id=p_header_id;
3352: IF(l_line_count =0) THEN
3355: x_header_deleted := TRUE;
3356: END IF;
3357: EXCEPTION
3358: WHEN OTHERS THEN
3359: oe_debug_pub.add('Exception occured in Delete_Order:'||SQLERRM);
3360: END;
3361:
3362: PROCEDURE Process_Retrobill_Request
3363: (p_retrobill_request_rec IN OE_RETROBILL_REQUESTS%ROWTYPE
3388: lx_header_id NUMBER;
3389: l_to_be_exe_hdr_id_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
3390: l_msg_count NUMBER;
3391: l_msg_data VARCHAR2(5000);
3392: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3393: lx_return_status VARCHAR2(15);
3394: l_msg VARCHAR2(2000);
3395: --bug5003256 start
3396: l_book_line_count NUMBER;
3455: IF(lx_return_status=FND_API.G_RET_STS_SUCCESS and lx_header_id is not NULL) THEN
3456: Oe_retrobill_Pvt.Delete_Order(lx_header_id, l_header_deleted);
3457: --bug 12922209
3458: IF l_header_deleted THEN
3459: oe_debug_pub.ADD('Retro Order Header created has been deleted');
3460: --assiging 1 to ret_code to return warning
3461: x_ret_code :=1;
3462: END IF;
3463: --bug12922209
3463: --bug12922209
3464: --bug5003256
3465: ELSE
3466: x_ret_code :=1; --bug12922209
3467: oe_debug_pub.ADD('Error occured in Process order');
3468:
3469: x_error_count := x_error_count + l_line_tbl.count;
3470: END IF;
3471:
3468:
3469: x_error_count := x_error_count + l_line_tbl.count;
3470: END IF;
3471:
3472: oe_debug_pub.add('Praveen: Price List Id' || G_FIRST_LINE_PRICE_LIST_ID);
3473: oe_debug_pub.add('Praveen: First Line Deleted' || G_FIRST_LINE_DELETED);
3474:
3475: BEGIN
3476:
3469: x_error_count := x_error_count + l_line_tbl.count;
3470: END IF;
3471:
3472: oe_debug_pub.add('Praveen: Price List Id' || G_FIRST_LINE_PRICE_LIST_ID);
3473: oe_debug_pub.add('Praveen: First Line Deleted' || G_FIRST_LINE_DELETED);
3474:
3475: BEGIN
3476:
3477: SELECT price_list_id INTO l_header_price_list_id
3503: l_to_be_exe_hdr_id_tbl(i):=lx_header_id;
3504: i:=i+1;
3505: End If;
3506:
3507: oe_debug_pub.add('Retro:Return status after calling process order:'||lx_return_status);
3508:
3509: --clear header, reset line tbl records
3510: l_line_tbl.delete;
3511: l_header_rec:=null;
3537: p_retrobill_request_rec=>l_retrobill_request_rec,
3538: x_line_rec=>l_line_rec);
3539:
3540: --8736629
3541: oe_debug_pub.add('Prining the line_id before call to Prepare_Line_Pricing_Attribs: '|| l.line_id);
3542: Prepare_Line_Pricing_Attribs(p_line_id => l.line_id,
3543: x_Line_price_Att_rec => l_Line_price_Att_rec,
3544: x_row_count => l_row_count);
3545: IF l_row_count > 0 THEN
3551: IF(j=0) THEN
3552: G_FIRST_LINE_PRICE_LIST_ID := l_line_rec.price_list_id;
3553: END IF;
3554: j:=j+1;
3555: oe_debug_pub.add('Adding line rec to the table to index : '|| j);
3556: l_line_tbl(j):=l_line_rec;
3557: -- Added for bug 8736629 Start
3558: IF l_row_count > 0 THEN
3559: oe_debug_pub.add('Adding attribute rec to the table to index : '|| j);
3555: oe_debug_pub.add('Adding line rec to the table to index : '|| j);
3556: l_line_tbl(j):=l_line_rec;
3557: -- Added for bug 8736629 Start
3558: IF l_row_count > 0 THEN
3559: oe_debug_pub.add('Adding attribute rec to the table to index : '|| j);
3560: l_Line_price_Att_rec.line_index:= j;
3561: l_Line_price_Att_tbl(j):=l_Line_price_Att_rec;
3562: oe_debug_pub.add('Clearing the attribute rec after adding attribute rec to the attribute table');
3563: l_Line_price_Att_rec:= NULL;
3558: IF l_row_count > 0 THEN
3559: oe_debug_pub.add('Adding attribute rec to the table to index : '|| j);
3560: l_Line_price_Att_rec.line_index:= j;
3561: l_Line_price_Att_tbl(j):=l_Line_price_Att_rec;
3562: oe_debug_pub.add('Clearing the attribute rec after adding attribute rec to the attribute table');
3563: l_Line_price_Att_rec:= NULL;
3564: END IF;
3565: -- Added for bug 8736629 End
3566: stmt:=8;
3575: IF(lx_return_status=FND_API.G_RET_STS_SUCCESS and lx_header_id is not NULL) THEN
3576: oe_retrobill_pvt.delete_order(lx_header_id, l_header_deleted); --bug5003256
3577: --bug 12922209 start
3578: IF l_header_deleted THEN
3579: oe_debug_pub.ADD('Retro Order Header created has been deleted');
3580: x_ret_code :=1;
3581: END IF;
3582: --bug12922209 end
3583: --bug5003256
3581: END IF;
3582: --bug12922209 end
3583: --bug5003256
3584: ELSE
3585: oe_debug_pub.ADD('Error occured in Process order');
3586: x_ret_code :=1; --bug12922209
3587: x_error_count := x_error_count + l_line_tbl.count;
3588:
3589: END IF;
3587: x_error_count := x_error_count + l_line_tbl.count;
3588:
3589: END IF;
3590:
3591: oe_debug_pub.add('Praveen: Price List Id' || G_FIRST_LINE_PRICE_LIST_ID);
3592: oe_debug_pub.add('Praveen: First Line Deleted' || G_FIRST_LINE_DELETED);
3593:
3594: If lx_return_status = FND_API.G_RET_STS_SUCCESS AND
3595: NOT l_header_deleted Then --bug5003256
3588:
3589: END IF;
3590:
3591: oe_debug_pub.add('Praveen: Price List Id' || G_FIRST_LINE_PRICE_LIST_ID);
3592: oe_debug_pub.add('Praveen: First Line Deleted' || G_FIRST_LINE_DELETED);
3593:
3594: If lx_return_status = FND_API.G_RET_STS_SUCCESS AND
3595: NOT l_header_deleted Then --bug5003256
3596: l_to_be_exe_hdr_id_tbl(i):=lx_header_id;
3634: , x_return_status => lx_return_status
3635: , x_msg_count => l_msg_count
3636: , x_msg_data => l_msg_data);
3637: IF l_debug_level > 0 THEN
3638: oe_debug_pub.add('Retro:Direct Execution without preview');
3639: --skubendr{
3640: IF (lx_return_status<>FND_API.G_RET_STS_SUCCESS) THEN
3641: Display_Message(l_msg_count,l_msg_data);
3642: END IF;
3659: IF(g_retrobill_request_rec.retrobill_request_id = l_retrobill_request_rec.retrobill_request_id) THEN
3660: l_retrobill_request_rec.sold_to_org_id:=g_retrobill_request_rec.sold_to_org_id;
3661: l_retrobill_request_rec.inventory_item_id:=g_retrobill_request_rec.inventory_item_id;
3662: END IF;
3663: oe_debug_pub.add('sold_to_org_id'||l_retrobill_request_rec.sold_to_org_id);
3664: oe_debug_pub.add('inventory_item_id'||l_retrobill_request_rec.inventory_item_id);
3665: Insert_Retrobill_Request(l_retrobill_request_rec);
3666:
3667:
3660: l_retrobill_request_rec.sold_to_org_id:=g_retrobill_request_rec.sold_to_org_id;
3661: l_retrobill_request_rec.inventory_item_id:=g_retrobill_request_rec.inventory_item_id;
3662: END IF;
3663: oe_debug_pub.add('sold_to_org_id'||l_retrobill_request_rec.sold_to_org_id);
3664: oe_debug_pub.add('inventory_item_id'||l_retrobill_request_rec.inventory_item_id);
3665: Insert_Retrobill_Request(l_retrobill_request_rec);
3666:
3667:
3668: Else --previously previewed request
3668: Else --previously previewed request
3669:
3670: --EXECUTE will not reprice the orders/lines, just booked the order and update
3671: --PREVIEW again will reprice the orders/lines
3672: oe_debug_pub.add('execution mode:'||p_retrobill_request_rec.execution_mode);
3673:
3674: G_CURRENT_RETROBILL_REQUEST_ID:=p_retrobill_request_rec.retrobill_request_id;
3675:
3676: IF G_CURRENT_RETROBILL_REQUEST_ID IS NULL THEN
3673:
3674: G_CURRENT_RETROBILL_REQUEST_ID:=p_retrobill_request_rec.retrobill_request_id;
3675:
3676: IF G_CURRENT_RETROBILL_REQUEST_ID IS NULL THEN
3677: oe_debug_pub.add('Retro:previouly previewed request, request id can not be NULL!');
3678: l_msg:='Retro:previouly previewed request, request id can not be NULL!';
3679: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3680: END IF;
3681:
3701: x_created_retrobill_request_id:=NULL;
3702: --bug5003256
3703: x_error_count := -1;
3704: When Others Then
3705: oe_debug_pub.add('Exception occured in process_retrobill_requests:'||SQLERRM);
3706: oe_debug_pub.add('Statement number:'||stmt);
3707: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3708: x_retrun_status_text:='OE_RETROBILL_PVT.Process_Retrobill_Request'||SQLERRM;
3709: oe_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data);
3702: --bug5003256
3703: x_error_count := -1;
3704: When Others Then
3705: oe_debug_pub.add('Exception occured in process_retrobill_requests:'||SQLERRM);
3706: oe_debug_pub.add('Statement number:'||stmt);
3707: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3708: x_retrun_status_text:='OE_RETROBILL_PVT.Process_Retrobill_Request'||SQLERRM;
3709: oe_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data);
3710: x_created_retrobill_request_id:=NULL;
3775:
3776: l_request_name varchar2(2000) := NULL;
3777: l_lines_not_retrobilled_count NUMBER:=0;
3778:
3779: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3780: l_ret_code NUMBER; --bug12922209
3781: BEGIN
3782: open c_lines;
3783: If l_debug_level > 0 Then
3780: l_ret_code NUMBER; --bug12922209
3781: BEGIN
3782: open c_lines;
3783: If l_debug_level > 0 Then
3784: Oe_Debug_Pub.add('skubendr:Entering procedure Oe_Retrobill_Pvt.Oe_Build_Retrobill_Table');
3785: End If;
3786:
3787: -- Initialize Message Stack
3788: -- We initialize the message stack here and for the subsequent process order calls pass the init_msg_list as FALSE
3794: IF(p_retrobill_request_id is NULL) THEN
3795: FOR c_open_retrobill_requests_rec in c_open_retrobill_requests(c_lines_record.header_id,c_lines_record.line_id) LOOP
3796: l_request_name := c_open_retrobill_requests_rec.name;
3797: END LOOP;
3798: oe_debug_pub.add('l_request_name:'||l_request_name);
3799: END IF;
3800:
3801: IF(l_request_name is NULL OR p_retrobill_request_id is NOT NULL) THEN
3802: l_retrobill_tbl(i).original_line_id :=c_lines_record.line_id;
3830: ,p_constraint_id => null
3831: );
3832:
3833: IF l_debug_level > 0 Then
3834: oe_debug_pub.add('Line has open retrobill line:'||c_lines_record.line_id);
3835: oe_debug_pub.add('Open Request:'||l_request_name);
3836: END IF;
3837:
3838: -- The line has previewed retrobill line and hence cannot be previewed
3831: );
3832:
3833: IF l_debug_level > 0 Then
3834: oe_debug_pub.add('Line has open retrobill line:'||c_lines_record.line_id);
3835: oe_debug_pub.add('Open Request:'||l_request_name);
3836: END IF;
3837:
3838: -- The line has previewed retrobill line and hence cannot be previewed
3839: FND_MESSAGE.SET_NAME('ONT','ONT_UNINVOICED_RETLINE_EXIST');
3858: l_retrobill_request_rec.sold_to_org_id := p_sold_to_org_id;
3859: l_retrobill_request_rec.inventory_item_id := p_inventory_item_id;
3860:
3861: If l_debug_level > 0 Then
3862: Oe_Debug_Pub.add('skubendr:Before calling Oe_Retrobill_Pvt.Process_Retrobill_Request');
3863: End If;
3864:
3865: IF(l_retrobill_tbl.count <> 0) THEN
3866: Oe_Retrobill_Pvt.Process_Retrobill_Request
3877:
3878: x_ret_code := l_ret_code; --bug 12922209
3879:
3880: IF l_debug_level > 0 Then
3881: Oe_Debug_Pub.add('skubendr:After calling Oe_Retrobill_Pvt.Process_Retrobill_Request :'||l_return_status);
3882: END IF;
3883:
3884: -- delete the data from temp table after retrobilling process
3885: IF(p_request_session_id is not NULL) THEN
3892: x_return_status := l_return_status;
3893: x_return_status_text := l_return_status_text;
3894:
3895: IF l_debug_level > 0 Then
3896: Oe_Debug_Pub.add('skubendr:Exiting procedure Oe_Retrobill_Pvt.Oe_Build_Retrobill_Table:'||l_return_status);
3897: END IF;
3898: EXCEPTION
3899: WHEN NO_DATA_FOUND THEN
3900: RAISE NO_DATA_FOUND;
3898: EXCEPTION
3899: WHEN NO_DATA_FOUND THEN
3900: RAISE NO_DATA_FOUND;
3901: WHEN OTHERS THEN
3902: oe_debug_pub.add('Error'||SQLERRM);
3903: END Oe_Build_Retrobill_Tbl;
3904:
3905:
3906: -- This procedure will be called from the UI when the user submits an concurrent request
3926: l_return_status_text VARCHAR2(2000);
3927: l_retrobill_request_id NUMBER;
3928:
3929: l_concurrent_request_id NUMBER;
3930: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3931: --rt moac
3932: l_org_id NUMBER;
3933:
3934: --bug5003256
3939:
3940: FND_PROFILE.Get('CONC_REQUEST_ID', l_concurrent_request_id);
3941:
3942: IF l_debug_level > 0 THEN
3943: oe_debug_pub.add( 'REQUEST ID: '|| TO_CHAR ( L_CONCURRENT_REQUEST_ID ) ) ;
3944: END IF;
3945: --rt moac start
3946: BEGIN
3947: SELECT org_id INTO l_org_id
3956: END;
3957: IF l_org_id IS NOT NULL THEN
3958: MO_GLOBAL.Set_Policy_Context('S',l_org_id);
3959: IF l_debug_level > 0 THEN
3960: oe_debug_pub.add('Context is set for org_id : '|| mo_global.get_current_org_id);
3961: END IF;
3962: ELSE
3963: l_return_status := FND_API.G_RET_STS_ERROR;
3964: fnd_file.put_line(FND_FILE.OUTPUT,'Could not set org context');
4011: END IF;
4012: EXCEPTION
4013: WHEN OTHERS THEN
4014: rollback;
4015: oe_debug_pub.add('Error'||SQLERRM);
4016: retcode := -1;
4017: END Oe_Retrobill_Conc_Pgm;
4018:
4019:
4039: l_result_code VARCHAR2(240);
4040: l_return_status VARCHAR2(30);
4041: l_interface_line_rec OE_Invoice_PUB.RA_Interface_Lines_Rec_Type;
4042: --
4043: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4044: --
4045:
4046: --Find all retrobill bill only lines created before the Return Line
4047: cursor previous_retrobill_lines IS
4077: l_orig_selling_price NUMBER;
4078: BEGIN
4079: null;
4080: IF l_debug_level > 0 THEN
4081: oe_debug_pub.add( 'ENTERING INTERFACE_Retrobilled_RMA ( ) PROCEDURE' , 5 ) ;
4082: oe_debug_pub.add( 'INTERFACING Retro RMA LINE ID '||TO_CHAR ( P_LINE_REC.LINE_ID ), 3);
4083: END IF;
4084:
4085: -- First credit to original order line, then positive retrobill lines
4078: BEGIN
4079: null;
4080: IF l_debug_level > 0 THEN
4081: oe_debug_pub.add( 'ENTERING INTERFACE_Retrobilled_RMA ( ) PROCEDURE' , 5 ) ;
4082: oe_debug_pub.add( 'INTERFACING Retro RMA LINE ID '||TO_CHAR ( P_LINE_REC.LINE_ID ), 3);
4083: END IF;
4084:
4085: -- First credit to original order line, then positive retrobill lines
4086: i := 1;
4090: -- loop through retrobill lines
4091: For retrobill_line in previous_retrobill_lines
4092: LOOP
4093: IF l_debug_level > 0 THEN
4094: oe_debug_pub.add('find retrobill line'||retrobill_line.line_id, 3);
4095: oe_debug_pub.add('line category code'||retrobill_line.line_category_code, 3);
4096: oe_debug_pub.add('retrobill request'||retrobill_line.retrobill_request_id, 3);
4097: oe_debug_pub.add('order_source_id'||retrobill_line.order_source_id, 3);
4098: oe_debug_pub.add('list price'||retrobill_line.unit_list_price, 3);
4091: For retrobill_line in previous_retrobill_lines
4092: LOOP
4093: IF l_debug_level > 0 THEN
4094: oe_debug_pub.add('find retrobill line'||retrobill_line.line_id, 3);
4095: oe_debug_pub.add('line category code'||retrobill_line.line_category_code, 3);
4096: oe_debug_pub.add('retrobill request'||retrobill_line.retrobill_request_id, 3);
4097: oe_debug_pub.add('order_source_id'||retrobill_line.order_source_id, 3);
4098: oe_debug_pub.add('list price'||retrobill_line.unit_list_price, 3);
4099: oe_debug_pub.add('selling price'||retrobill_line.unit_selling_price, 3);
4092: LOOP
4093: IF l_debug_level > 0 THEN
4094: oe_debug_pub.add('find retrobill line'||retrobill_line.line_id, 3);
4095: oe_debug_pub.add('line category code'||retrobill_line.line_category_code, 3);
4096: oe_debug_pub.add('retrobill request'||retrobill_line.retrobill_request_id, 3);
4097: oe_debug_pub.add('order_source_id'||retrobill_line.order_source_id, 3);
4098: oe_debug_pub.add('list price'||retrobill_line.unit_list_price, 3);
4099: oe_debug_pub.add('selling price'||retrobill_line.unit_selling_price, 3);
4100: END IF;
4093: IF l_debug_level > 0 THEN
4094: oe_debug_pub.add('find retrobill line'||retrobill_line.line_id, 3);
4095: oe_debug_pub.add('line category code'||retrobill_line.line_category_code, 3);
4096: oe_debug_pub.add('retrobill request'||retrobill_line.retrobill_request_id, 3);
4097: oe_debug_pub.add('order_source_id'||retrobill_line.order_source_id, 3);
4098: oe_debug_pub.add('list price'||retrobill_line.unit_list_price, 3);
4099: oe_debug_pub.add('selling price'||retrobill_line.unit_selling_price, 3);
4100: END IF;
4101: -- Only credit to positive lines, but want to get the
4094: oe_debug_pub.add('find retrobill line'||retrobill_line.line_id, 3);
4095: oe_debug_pub.add('line category code'||retrobill_line.line_category_code, 3);
4096: oe_debug_pub.add('retrobill request'||retrobill_line.retrobill_request_id, 3);
4097: oe_debug_pub.add('order_source_id'||retrobill_line.order_source_id, 3);
4098: oe_debug_pub.add('list price'||retrobill_line.unit_list_price, 3);
4099: oe_debug_pub.add('selling price'||retrobill_line.unit_selling_price, 3);
4100: END IF;
4101: -- Only credit to positive lines, but want to get the
4102: -- total retrobilled price even for negative lines,
4095: oe_debug_pub.add('line category code'||retrobill_line.line_category_code, 3);
4096: oe_debug_pub.add('retrobill request'||retrobill_line.retrobill_request_id, 3);
4097: oe_debug_pub.add('order_source_id'||retrobill_line.order_source_id, 3);
4098: oe_debug_pub.add('list price'||retrobill_line.unit_list_price, 3);
4099: oe_debug_pub.add('selling price'||retrobill_line.unit_selling_price, 3);
4100: END IF;
4101: -- Only credit to positive lines, but want to get the
4102: -- total retrobilled price even for negative lines,
4103: -- so that we know whether RMA line price is the same as invoiced price
4145: RAISE FND_API.G_EXC_ERROR;
4146: END;
4147:
4148: IF l_debug_level > 0 THEN
4149: oe_debug_pub.add('original Price:'||l_orig_selling_price,3);
4150: oe_debug_pub.add('rma price:'||p_line_rec.unit_selling_price,3);
4151: oe_debug_pub.add('retrobilled price'||l_retrobilled_selling_price,3);
4152: --added below debug for bug12703043
4153: oe_debug_pub.add('p_line_rec.unit_selling_price'||p_line_rec.unit_selling_price,3);
4146: END;
4147:
4148: IF l_debug_level > 0 THEN
4149: oe_debug_pub.add('original Price:'||l_orig_selling_price,3);
4150: oe_debug_pub.add('rma price:'||p_line_rec.unit_selling_price,3);
4151: oe_debug_pub.add('retrobilled price'||l_retrobilled_selling_price,3);
4152: --added below debug for bug12703043
4153: oe_debug_pub.add('p_line_rec.unit_selling_price'||p_line_rec.unit_selling_price,3);
4154: oe_debug_pub.add('l_orig_selling_price '||l_orig_selling_price,3);
4147:
4148: IF l_debug_level > 0 THEN
4149: oe_debug_pub.add('original Price:'||l_orig_selling_price,3);
4150: oe_debug_pub.add('rma price:'||p_line_rec.unit_selling_price,3);
4151: oe_debug_pub.add('retrobilled price'||l_retrobilled_selling_price,3);
4152: --added below debug for bug12703043
4153: oe_debug_pub.add('p_line_rec.unit_selling_price'||p_line_rec.unit_selling_price,3);
4154: oe_debug_pub.add('l_orig_selling_price '||l_orig_selling_price,3);
4155: oe_debug_pub.add('l_retro_adjusted_selling_price '||l_retro_adjusted_selling_price,3);
4149: oe_debug_pub.add('original Price:'||l_orig_selling_price,3);
4150: oe_debug_pub.add('rma price:'||p_line_rec.unit_selling_price,3);
4151: oe_debug_pub.add('retrobilled price'||l_retrobilled_selling_price,3);
4152: --added below debug for bug12703043
4153: oe_debug_pub.add('p_line_rec.unit_selling_price'||p_line_rec.unit_selling_price,3);
4154: oe_debug_pub.add('l_orig_selling_price '||l_orig_selling_price,3);
4155: oe_debug_pub.add('l_retro_adjusted_selling_price '||l_retro_adjusted_selling_price,3);
4156: END IF;
4157: -- No retrobill line, interface the current line as it is
4150: oe_debug_pub.add('rma price:'||p_line_rec.unit_selling_price,3);
4151: oe_debug_pub.add('retrobilled price'||l_retrobilled_selling_price,3);
4152: --added below debug for bug12703043
4153: oe_debug_pub.add('p_line_rec.unit_selling_price'||p_line_rec.unit_selling_price,3);
4154: oe_debug_pub.add('l_orig_selling_price '||l_orig_selling_price,3);
4155: oe_debug_pub.add('l_retro_adjusted_selling_price '||l_retro_adjusted_selling_price,3);
4156: END IF;
4157: -- No retrobill line, interface the current line as it is
4158: -- 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
4151: oe_debug_pub.add('retrobilled price'||l_retrobilled_selling_price,3);
4152: --added below debug for bug12703043
4153: oe_debug_pub.add('p_line_rec.unit_selling_price'||p_line_rec.unit_selling_price,3);
4154: oe_debug_pub.add('l_orig_selling_price '||l_orig_selling_price,3);
4155: oe_debug_pub.add('l_retro_adjusted_selling_price '||l_retro_adjusted_selling_price,3);
4156: END IF;
4157: -- No retrobill line, interface the current line as it is
4158: -- 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
4159: IF (l_line_tbl.count = 1) THEN
4165:
4166: l_line_rec.unit_list_price := l_orig_list_price - l_retro_adjusted_list_price + l_retrobilled_list_price;
4167: l_line_rec.unit_selling_price := l_orig_selling_price - l_retro_adjusted_selling_price + l_retrobilled_selling_price;
4168: END IF;
4169: oe_debug_pub.add('l_line_rec.unit_list_price '||l_line_rec.unit_list_price,3);
4170: oe_debug_pub.add('l_line_rec.unit_selling_price '||l_line_rec.unit_selling_price,3);
4171: --bug12703043 end
4172:
4173: OE_Invoice_PUB.Interface_Single_line(p_line_rec => l_line_rec --bug12703043
4166: l_line_rec.unit_list_price := l_orig_list_price - l_retro_adjusted_list_price + l_retrobilled_list_price;
4167: l_line_rec.unit_selling_price := l_orig_selling_price - l_retro_adjusted_selling_price + l_retrobilled_selling_price;
4168: END IF;
4169: oe_debug_pub.add('l_line_rec.unit_list_price '||l_line_rec.unit_list_price,3);
4170: oe_debug_pub.add('l_line_rec.unit_selling_price '||l_line_rec.unit_selling_price,3);
4171: --bug12703043 end
4172:
4173: OE_Invoice_PUB.Interface_Single_line(p_line_rec => l_line_rec --bug12703043
4174: ,p_header_rec => p_header_rec
4208: l_line_rec.invoiced_quantity := NULL;
4209: END IF;
4210:
4211: IF l_debug_level > 0 THEN
4212: oe_debug_pub.add('retrobill:'||l_line_rec.line_id,3);
4213: oe_debug_pub.add('retrobill line category'
4214: ||l_line_rec.line_category_code,3);
4215: oe_debug_pub.add('retrobill line type'
4216: ||l_line_rec.line_type_id,3);
4209: END IF;
4210:
4211: IF l_debug_level > 0 THEN
4212: oe_debug_pub.add('retrobill:'||l_line_rec.line_id,3);
4213: oe_debug_pub.add('retrobill line category'
4214: ||l_line_rec.line_category_code,3);
4215: oe_debug_pub.add('retrobill line type'
4216: ||l_line_rec.line_type_id,3);
4217: oe_debug_pub.add('retrobill item'||l_line_rec.ordered_item,3);
4211: IF l_debug_level > 0 THEN
4212: oe_debug_pub.add('retrobill:'||l_line_rec.line_id,3);
4213: oe_debug_pub.add('retrobill line category'
4214: ||l_line_rec.line_category_code,3);
4215: oe_debug_pub.add('retrobill line type'
4216: ||l_line_rec.line_type_id,3);
4217: oe_debug_pub.add('retrobill item'||l_line_rec.ordered_item,3);
4218: oe_debug_pub.add('invoiced_quantity'||l_line_rec.invoiced_quantity,3);
4219: END IF;
4213: oe_debug_pub.add('retrobill line category'
4214: ||l_line_rec.line_category_code,3);
4215: oe_debug_pub.add('retrobill line type'
4216: ||l_line_rec.line_type_id,3);
4217: oe_debug_pub.add('retrobill item'||l_line_rec.ordered_item,3);
4218: oe_debug_pub.add('invoiced_quantity'||l_line_rec.invoiced_quantity,3);
4219: END IF;
4220:
4221: OE_Invoice_PUB. Interface_Single_line(p_line_rec => l_line_rec
4214: ||l_line_rec.line_category_code,3);
4215: oe_debug_pub.add('retrobill line type'
4216: ||l_line_rec.line_type_id,3);
4217: oe_debug_pub.add('retrobill item'||l_line_rec.ordered_item,3);
4218: oe_debug_pub.add('invoiced_quantity'||l_line_rec.invoiced_quantity,3);
4219: END IF;
4220:
4221: OE_Invoice_PUB. Interface_Single_line(p_line_rec => l_line_rec
4222: ,p_header_rec => p_header_rec
4224: ,x_return_status => x_return_status
4225: ,x_result_out => x_result_out);
4226:
4227: IF l_debug_level > 0 THEN
4228: oe_debug_pub.add( 'INTERFACED W/REQUEST_ID : '||
4229: L_INTERFACE_LINE_REC.REQUEST_ID || ' X_RETURN_STATUS: '|| X_RETURN_STATUS , 5 ) ;
4230: END IF;
4231: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4232: RAISE FND_API.G_EXC_ERROR;
4238:
4239: -- 3661895: we need to clear this global table
4240: G_Retro_Bill_Only_Line_Tbl.delete;
4241: IF l_debug_level > 0 THEN
4242: oe_debug_pub.add('Clearing the cache G_Retro_Bill_Only_Line_Tbl');
4243: END IF;
4244: END Interface_Retrobilled_RMA;
4245:
4246: Function Invoice_Number(p_order_number IN NUMBER,
4294: p_scope IN VARCHAR2,
4295: x_result OUT NOCOPY NUMBER
4296: )
4297: AS
4298: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4299: l_count NUMBER := 0;
4300: l_line_id NUMBER;
4301: l_header_id NUMBER;
4302: l_creation_date DATE;
4420: , p_x_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl
4421: );
4422:
4423: IF(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4424: oe_debug_pub.add('Deleted Header:'||l_retrobill_header_rec.header_id);
4425: l_headers_processed := l_headers_processed+1;
4426: END IF;
4427: ELSE
4428: oe_debug_pub.add('Cannot purge request as retrobill booked order exists'||p_request_name);
4424: oe_debug_pub.add('Deleted Header:'||l_retrobill_header_rec.header_id);
4425: l_headers_processed := l_headers_processed+1;
4426: END IF;
4427: ELSE
4428: oe_debug_pub.add('Cannot purge request as retrobill booked order exists'||p_request_name);
4429: FND_MESSAGE.SET_NAME('ONT','ONT_RETRO_PURGE_NOT_ALLOWED');
4430: FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_request_name);
4431: OE_MSG_PUB.ADD;
4432:
4432:
4433: EXIT;
4434: END IF;
4435: ELSE
4436: oe_debug_pub.add('Cannot purge request as purge preview order is N and attached orders exist');
4437: FND_MESSAGE.SET_NAME('ONT','ONT_RETRO_PURGE_NOT_ALLOWED');
4438: FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_request_name);
4439: OE_MSG_PUB.ADD;
4440:
4449: IF(l_header_count = 0) THEN
4450: IF(p_retrobill_request_id is NOT NULL) THEN
4451: Delete from oe_retrobill_requests where
4452: retrobill_request_id=to_number(p_retrobill_request_id);
4453: oe_debug_pub.add('Deleted Request'||p_retrobill_request_id);
4454: l_requests_processed := l_requests_processed + 1;
4455: END IF;
4456: END IF;
4457: x_requests_processed := l_requests_processed;
4457: x_requests_processed := l_requests_processed;
4458: x_headers_processed := l_headers_processed;
4459: EXCEPTION
4460: WHEN OTHERS THEN
4461: oe_debug_pub.add('Exception occured:'||SQLERRM);
4462: RAISE;
4463: END;
4464:
4465: PROCEDURE Oe_Retrobill_Purge
4494: l_change_sequence VARCHAR2(50);
4495: l_orig_sys_line_ref VARCHAR2(50);
4496: l_message_text VARCHAR2(2000);
4497:
4498: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4499: --rt moac
4500: l_org_id NUMBER;
4501: l_old_org_id NUMBER;
4502:
4566:
4567: FND_PROFILE.Get('CONC_REQUEST_ID', l_concurrent_id);
4568:
4569: IF l_debug_level > 0 THEN
4570: oe_debug_pub.add('Entering Oe_Retrobill_Pvt.Oe_Retrobill_Purge',1);
4571: oe_debug_pub.add( 'CONCURRENT REQUEST ID: '|| TO_CHAR ( L_CONCURRENT_ID ) ) ;
4572: END IF;
4573:
4574: --rt moac start
4567: FND_PROFILE.Get('CONC_REQUEST_ID', l_concurrent_id);
4568:
4569: IF l_debug_level > 0 THEN
4570: oe_debug_pub.add('Entering Oe_Retrobill_Pvt.Oe_Retrobill_Purge',1);
4571: oe_debug_pub.add( 'CONCURRENT REQUEST ID: '|| TO_CHAR ( L_CONCURRENT_ID ) ) ;
4572: END IF;
4573:
4574: --rt moac start
4575: mo_global.init('ONT');
4576: IF p_org_id IS NOT NULL THEN
4577: l_org_id := to_number(p_org_id);
4578: MO_GLOBAL.Set_Policy_Context('S',l_org_id);
4579: IF l_debug_level > 0 THEN
4580: oe_debug_pub.add('Setting the context for org_id : '|| l_org_id);
4581: END IF;
4582: ELSE
4583: MO_GLOBAL.Set_Policy_Context('M',null);
4584: END IF;
4586:
4587: IF(p_retrobill_request_id is NULL) THEN
4588: l_old_org_id := -99; --rt moac
4589: FOR l_retrobill_request_rec IN retrobill_request LOOP
4590: oe_debug_pub.add('Retrobill Request Id:'||l_retrobill_request_rec.retrobill_request_id);
4591: --rt moac start
4592: IF p_org_id IS NULL THEN
4593: IF l_retrobill_request_rec.org_id <> l_old_org_id THEN
4594: MO_GLOBAL.Set_Policy_Context('S',l_retrobill_request_rec.org_id);
4592: IF p_org_id IS NULL THEN
4593: IF l_retrobill_request_rec.org_id <> l_old_org_id THEN
4594: MO_GLOBAL.Set_Policy_Context('S',l_retrobill_request_rec.org_id);
4595: IF l_debug_level > 0 THEN
4596: oe_debug_pub.add('Setting the context for org_id : '|| l_retrobill_request_rec.org_id);
4597: oe_debug_pub.add('mo_global.get_current_org_id : '|| mo_global.get_current_org_id);
4598: END IF;
4599: l_old_org_id := l_retrobill_request_rec.org_id;
4600: END IF;
4593: IF l_retrobill_request_rec.org_id <> l_old_org_id THEN
4594: MO_GLOBAL.Set_Policy_Context('S',l_retrobill_request_rec.org_id);
4595: IF l_debug_level > 0 THEN
4596: oe_debug_pub.add('Setting the context for org_id : '|| l_retrobill_request_rec.org_id);
4597: oe_debug_pub.add('mo_global.get_current_org_id : '|| mo_global.get_current_org_id);
4598: END IF;
4599: l_old_org_id := l_retrobill_request_rec.org_id;
4600: END IF;
4601: END IF;
4605: --rt moac end
4606:
4607: --bug4752386 If the execution mode is 'EXECUTE', need to log a message and continue with the next request
4608: IF l_retrobill_request_rec.execution_mode='EXECUTE' THEN
4609: oe_debug_pub.add('Cannot purge request as it has been executed'||l_retrobill_request_rec.name);
4610: FND_MESSAGE.SET_NAME('ONT','ONT_RT_EXC_PURGE_NOT_ALLOWED');
4611: FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_retrobill_request_rec.name);
4612: OE_MSG_PUB.ADD;
4613:
4620: END IF; --execution mode = 'EXECUTE'
4621: END LOOP;
4622: ELSE
4623: FOR l_retrobill_request_rec IN retrobill_request_id LOOP
4624: oe_debug_pub.add('Retrobill Request Id:'||l_retrobill_request_rec.retrobill_request_id);
4625:
4626: --rt moac start
4627: IF p_org_id IS NULL THEN
4628: MO_GLOBAL.Set_Policy_Context('S',l_retrobill_request_rec.org_id);
4626: --rt moac start
4627: IF p_org_id IS NULL THEN
4628: MO_GLOBAL.Set_Policy_Context('S',l_retrobill_request_rec.org_id);
4629: IF l_debug_level > 0 THEN
4630: oe_debug_pub.add('Setting the context for org_id : '|| l_retrobill_request_rec.org_id);
4631: END IF;
4632: END IF;
4633:
4634: --setting a dummy message context so that the messages not attached to any particular order get the org_id corresponding to the policy context
4636: --rt moac end
4637:
4638: --bug4752386 If the execution mode is 'EXECUTE', need to log a message and continue with the next request
4639: IF l_retrobill_request_rec.execution_mode='EXECUTE' THEN
4640: oe_debug_pub.add('Cannot purge request as it has been executed'||l_retrobill_request_rec.name);
4641: FND_MESSAGE.SET_NAME('ONT','ONT_RT_EXC_PURGE_NOT_ALLOWED');
4642: FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_retrobill_request_rec.name);
4643: OE_MSG_PUB.ADD;
4644:
4673: fnd_file.put_line(FND_FILE.OUTPUT,'');
4674: END LOOP;
4675:
4676: IF l_debug_level > 0 THEN
4677: oe_debug_pub.add('Number of Requests Purged:' ||l_requests_processed);
4678: oe_debug_pub.add('Number of Orders Purged:' ||l_headers_processed);
4679: END IF;
4680:
4681: fnd_file.put_line(FND_FILE.OUTPUT,'Number of Requests Purged:' ||l_requests_processed);
4674: END LOOP;
4675:
4676: IF l_debug_level > 0 THEN
4677: oe_debug_pub.add('Number of Requests Purged:' ||l_requests_processed);
4678: oe_debug_pub.add('Number of Orders Purged:' ||l_headers_processed);
4679: END IF;
4680:
4681: fnd_file.put_line(FND_FILE.OUTPUT,'Number of Requests Purged:' ||l_requests_processed);
4682: fnd_file.put_line(FND_FILE.OUTPUT,'Number of Orders Purged:' ||l_headers_processed);
4679: END IF;
4680:
4681: fnd_file.put_line(FND_FILE.OUTPUT,'Number of Requests Purged:' ||l_requests_processed);
4682: fnd_file.put_line(FND_FILE.OUTPUT,'Number of Orders Purged:' ||l_headers_processed);
4683: oe_debug_pub.add('Exiting Oe_Retrobill_pvt.Oe_retrobill_Purge');
4684: commit;
4685:
4686: EXCEPTION
4687: WHEN OTHERS THEN
4686: EXCEPTION
4687: WHEN OTHERS THEN
4688: ROLLBACK;
4689: retcode := -1;
4690: oe_debug_pub.add('Error in Procedure Oe_Retrobill_Purge'||sqlerrm);
4691: Raise;
4692: END Oe_Retrobill_Purge;
4693: --retro}
4694: -- 3661895 Added the following procedure
4701: p_price_adjustment_id IN NUMBER
4702: ) RETURN NUMBER
4703: AS
4704: --
4705: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4706: --
4707:
4708: l_list_line_id NUMBER;
4709: l_sum_credied_amount NUMBER := 0;
4711:
4712: BEGIN
4713:
4714: IF l_debug_level > 0 THEN
4715: oe_debug_pub.add('ENTERING FUNCTION Get_Retrobill_Credited_Adj',1);
4716: oe_debug_pub.add('G_Retro_Bill_Only_Line_Tbl count:'||G_Retro_Bill_Only_Line_Tbl.count);
4717: END IF;
4718:
4719: IF(p_price_adjustment_id is NOT NULL) THEN
4712: BEGIN
4713:
4714: IF l_debug_level > 0 THEN
4715: oe_debug_pub.add('ENTERING FUNCTION Get_Retrobill_Credited_Adj',1);
4716: oe_debug_pub.add('G_Retro_Bill_Only_Line_Tbl count:'||G_Retro_Bill_Only_Line_Tbl.count);
4717: END IF;
4718:
4719: IF(p_price_adjustment_id is NOT NULL) THEN
4720: select list_line_id into l_list_line_id
4739: EXCEPTION
4740: WHEN NO_DATA_FOUND THEN
4741: NULL;
4742: WHEN OTHERS THEN
4743: oe_debug_pub.add('Exception occured:'||SQLERRM);
4744: RAISE;
4745: END;
4746: END LOOP;
4747: END IF;
4746: END LOOP;
4747: END IF;
4748:
4749: IF l_debug_level > 0 THEN
4750: oe_debug_pub.add('l_sum_credied_amount:'||l_sum_credied_amount||'for list line id:'||l_list_line_id);
4751: END IF;
4752: RETURN nvl(l_sum_credied_amount,0);
4753:
4754: EXCEPTION
4754: EXCEPTION
4755: WHEN NO_DATA_FOUND THEN
4756: NULL;
4757: WHEN OTHERS THEN
4758: oe_debug_pub.add('Exception occured:'||SQLERRM);
4759: RAISE;
4760: END Get_Retrobill_Credited_Adj;
4761:
4762: -- 3661895
4771: ,x_line_adjustments OUT NOCOPY OE_Header_Adj_Util.Line_Adjustments_Tab_Type
4772: )
4773: IS
4774: --
4775: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4776: --
4777: l_adjustments_tbl OE_Header_Adj_Util.Line_Adjustments_Tab_Type;
4778: l_adj_rec OE_Header_Adj_Util.Line_Adjustments_Rec_Type;
4779: l_header_id NUMBER;
4781:
4782: BEGIN
4783:
4784: IF l_debug_level > 0 THEN
4785: oe_debug_pub.add( 'ENTERING OE_HEADER_ADJ_UTIL.GET_LINE_ADJUSTMENTS' , 1 ) ;
4786: END IF;
4787:
4788: IF(p_line_rec.retrobill_request_id = 0) THEN
4789: l_header_id := p_line_rec.header_id;
4793: l_line_id := p_line_rec.orig_sys_line_ref;
4794: END IF;
4795:
4796: IF l_debug_level > 0 THEN
4797: oe_debug_pub.add('HEADER ID:'||l_header_id);
4798: oe_debug_pub.add('LINE ID:'||l_line_id);
4799: END IF;
4800:
4801: OE_Header_Adj_Util.Get_Line_Adjustments
4794: END IF;
4795:
4796: IF l_debug_level > 0 THEN
4797: oe_debug_pub.add('HEADER ID:'||l_header_id);
4798: oe_debug_pub.add('LINE ID:'||l_line_id);
4799: END IF;
4800:
4801: OE_Header_Adj_Util.Get_Line_Adjustments
4802: (p_header_id => l_header_id
4802: (p_header_id => l_header_id
4803: ,p_line_id => l_line_id
4804: ,x_line_adjustments => l_adjustments_tbl);
4805:
4806: oe_debug_pub.add( ' p_line_rec.retrobill_request_id = '||p_line_rec.retrobill_request_id , 1 ) ;
4807: oe_debug_pub.add( ' l_adjustments_tbl.count = '|| l_adjustments_tbl.count , 1 ) ;
4808:
4809: IF (p_line_rec.retrobill_request_id = 0) THEN
4810:
4803: ,p_line_id => l_line_id
4804: ,x_line_adjustments => l_adjustments_tbl);
4805:
4806: oe_debug_pub.add( ' p_line_rec.retrobill_request_id = '||p_line_rec.retrobill_request_id , 1 ) ;
4807: oe_debug_pub.add( ' l_adjustments_tbl.count = '|| l_adjustments_tbl.count , 1 ) ;
4808:
4809: IF (p_line_rec.retrobill_request_id = 0) THEN
4810:
4811: if l_adjustments_tbl.count > 0 then -- bug# 8435020 : add if condition
4839:
4840: END IF;
4841:
4842: IF l_debug_level > 0 THEN
4843: oe_debug_pub.add( 'EXITING OE_HEADER_ADJ_UTIL.GET_LINE_ADJUSTMENTS' , 1 ) ;
4844: END IF;
4845:
4846: END Get_Line_Adjustments;
4847: