Reprice an Order Line or Order

Suppose you need to price all lines of an order using pl/sql script, you may use following script. Advantage of using OE_LINE_REPRICE.Reprice_Line procedure:

1. You will have access to correct value of oe_order_pub.g_line.line_id in QP_CUSTOM.get_custom_price procedure 

CREATE OR REPLACE PROCEDURE skm_reprice_lines
( p_header_id NUMBER
) IS
  l_line_rec      OE_Order_Pub.Line_Rec_Type;
  l_return_status VARCHAR2(10);
  i NUMBER;
  l_msg_data VARCHAR2(250);
  
  CURSOR c_lines IS
     SELECT line_id
       FROM oe_order_lines_all
      WHERE header_id = p_header_id;
BEGIN

  OE_DEBUG_PUB.debug_on();
  OE_DEBUG_PUB.Start_ONT_Debugger('/home/users/smisra','skm1',null);

  DBMS_APPLICATION_INFO.set_client_info('1');

  FOR l_line IN c_lines 
  LOOP
    OE_Line_Util.Query_Row
    ( p_line_id     =>    l_line.line_id
    , x_line_rec    =>    l_line_rec
    );

    DBMS_OUTPUT.put_line('Line Id:' || l_line_rec.line_id);
    DBMS_OUTPUT.put_line('ordered_item:' || l_line_rec.ordered_item);
    OE_LINE_REPRICE.Reprice_Line
    ( p_line_rec         => l_line_rec
    , p_Repricing_date    => 'SYSDATE'
    , p_Repricing_event    => 'LINE'
    , p_Honor_Price_Flag  => 'Y'
    , x_return_status    => l_return_status
    ) ;
  END LOOP;
 
  DBMS_OUTPUT.put_line('Return Message:' || l_return_status);

  IF fnd_msg_pub.count_msg > 0
  THEN
     FOR j in 1..FND_MSG_PUB.count_msg
     LOOP
        FND_MSG_PUB.get
        ( p_msg_index      => j
        , p_encoded       => 'F'
        , p_data          => l_msg_data
        , p_msg_index_out => i
        );
        dbms_output.put_line( 'Error: ' || j || ':' || l_msg_data);
     END LOOP;
  END IF;

OE_DEBUG_PUB.debug_off();
END;
/

References:

http://sanjaimisra.blogspot.com/2008/05/reprice-order-line.html

PROCEDURE reprice_order (p_in_header_id IN NUMBER)
   IS
      v_header_count    NUMBER;
      v_header_list     VARCHAR2 (32000);
      v_line_count      NUMBER;
      v_line_list       VARCHAR2 (32000);
      v_price_level     VARCHAR2 (32000);
      v_return_status   VARCHAR2 (32000);
      v_msg_count       NUMBER;
      v_msg_data        VARCHAR2 (32000);
   BEGIN

      v_header_count := 1;
      v_header_list := TO_CHAR (p_in_header_id);
      v_price_level := 'ORDER';

      BEGIN
         oe_order_adj_pvt.price_action (p_header_count       => v_header_count,
                                        p_header_list        => v_header_list,
                                        p_line_count         => v_line_count,
                                        p_line_list          => v_line_list,
                                        p_price_level        => v_price_level,
                                        x_return_status      => v_return_status,
                                        x_msg_count          => v_msg_count,
                                        x_msg_data           => v_msg_data
                                       );
         COMMIT;

         IF (v_return_status != fnd_api.g_ret_sts_success)
         THEN
            dbms_output.put_line(v_msg_data);
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            dbms_output.put_line(sqlerrm);
      END;
   EXCEPTION
      WHEN OTHERS
      THEN
        dbms_output.put_line(sqlerrm);
   END reprice_order;

References:

http://www.oracleappstoday.com/2014/08/api-to-reprice-order-total-in-oracle-r12.html

How does one add a day/hour/minute/second to a date value?

DATE is the datatype that we are all familiar with when we think about representing date and time values. It has the ability to store the month, day, year, century, hours, minutes, and seconds. It is typically good for representing data for when something has happened or should happen in the future. The problem with the DATE datatype is its' granularity when trying to determine a time interval between two events when the events happen within a second of each other. This issue is solved with the TIMESTAMP datatype. 
In order to represent the date stored in a more readable format, the TO_CHAR function has traditionally been wrapped around the date: 

SQL> SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "hiredate"
  FROM employees; 
hiredate
-------------------
17.12.1980:00:00:00
20.02.1981:00:00:00 
The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples:

SQL> select sysdate, sysdate+1/24, sysdate +1/1440,sysdate +1/86400 from dual;

SYSDATE                        SYSDATE+1/24              SYSDATE+1/1440              SYSDATE+1/86400
--------------------           --------------------            --------------------               --------------------
03-Jul-2002 08:32:12     03-Jul-2002 09:32:12      03-Jul-2002 08:33:12        03-Jul-2002 08:32:13

Wondering how 30 seconds can be added to a timestamp, here is a typical example.


SQL>select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;

NOW                             NOW_PLUS_30_SECS
--------------------           --------------------
03-JUL-2005 16:47:23     03-JUL-2005 16:47:53

Seems pretty simple right ? :) here is a couple more that I did;

Description	Date Expression
Now	SYSDATE
Tomorow/ next day	SYSDATE + 1
Seven days from now	SYSDATE + 7
One hour from now	SYSDATE + 1/24
Three hours from now	SYSDATE + 3/24
An half hour from now	SYSDATE + 1/48
10 minutes from now	SYSDATE + 10/1440
30 seconds from now	SYSDATE + 30/86400
Tomorrow at 12 midnight	TRUNC(SYSDATE + 1)
Tomorrow at 8 AM	TRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noon	NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
First day of the month at 12 midnight	TRUNC(LAST_DAY(SYSDATE ) + 1)
The next Monday, Wednesday or Friday at 9 a.m	TRUNC(LEAST(NEXT_DAY(sysdate,''MONDAY' ' ),NEXT_DAY(sysdate,''WEDNESDAY''), NEXT_DAY(sysdate,''FRIDAY'' ))) + (9/24)

Hope it was helpful, enjoy. - 

References: 
http://bkintsiful.blogspot.com/2012/04/how-does-one-add-dayhourminutesecond-to.html