Sunday, 22 June 2014

How to find price discounts and surcharges on order lines in Order Management

SELECT   h.order_number, l.line_number, pa.list_line_type_code,
         pa.arithmetic_operator, pa.operand,
         DECODE (pa.modifier_level_code,
                 'ORDER', l.unit_list_price
                  * l.ordered_quantity
                  * pa.operand
                  * SIGN (pa.adjusted_amount)
                  / 100,
                 (pa.adjusted_amount * NVL (l.ordered_quantity, 0))
                ) discount_amt
    FROM qp_list_headers_vl lh,
         oe_price_adjustments pa,
         oe_order_lines_all l,
         oe_order_headers_all h
   WHERE h.order_number = 151266
     AND h.header_id = l.header_id
     AND h.org_id = l.org_id
     AND h.header_id = pa.header_id
     AND l.line_id = pa.line_id(+)
     AND pa.list_header_id = lh.list_header_id
--     AND (   pa.list_line_type_code = 'DIS'  OR pa.list_line_type_code = 'SUR'  OR pa.list_line_type_code = 'TAX'        )
     AND pa.applied_flag = 'Y'
     AND NOT EXISTS (
            SELECT 'X'
              FROM oe_price_adj_assocs pas, oe_price_adjustments pa1
             WHERE pas.rltd_price_adj_id = pa.price_adjustment_id
               AND pa1.price_adjustment_id = pas.price_adjustment_id
               AND pa1.list_line_type_code = 'PBH')
ORDER BY l.line_id
/

Note: QP_LIST_HEADERS_VL is view based on QP_LIST_HEADERS_B and QP_LIST_HEADERS_TL tables.

How to find freight charges on order lines in Order Management

SELECT   header_id, line_id, charge_id, charge_name, charge_amount,
         currency_code, invoiced_flag, interco_invoiced_flag, org_id,
         source_system_code, estimated_flag, invoiced_amount
    FROM oe_charge_lines_v
   WHERE header_id = (SELECT header_id
                        FROM oe_order_headers_all
                       WHERE order_number = 151266)
ORDER BY line_id

/

Note: The OE_CHARGE_LINES_V view is based on
OE_PRICE_ADJUSTMENTS,  OE_ORDER_HEADERS_ALL  AND OE_ORDER_LINES_ALL for FREIGHT CHARGES.

Wednesday, 22 January 2014

How to Generate a Trace file in Oracle Reports

function BeforeReport return boolean is
  CURSOR c_program_id(p_request_id IN NUMBER) IS
SELECT concurrent_program_id, nvl(enable_trace,'N')
FROM   FND_CONCURRENT_REQUESTS
WHERE  REQUEST_ID = p_request_id;
   
CURSOR get_audsid IS
SELECT a.sid, a.serial#, b.spid FROM v$session a,v$process b
WHERE audsid = userenv('SESSIONID')
AND a.paddr = b.addr;
 
CURSOR get_dbname IS
SELECT name FROM v$database;    

v_enable_trace    FND_CONCURRENT_PROGRAMS.enable_trace%TYPE;
v_program_id      FND_CONCURRENT_PROGRAMS.concurrent_program_id%TYPE;
v_audsid NUMBER := userenv('SESSIONID');
v_sid NUMBER;
v_serial NUMBER;
v_spid VARCHAR2(9);
v_dbname VARCHAR2(25);
begin
   SRW.USER_EXIT('FND SRWINIT');
  SRW.MESSAGE( 1275, 'Report Version is 120.3 Last modified date is 02/09/2005');
-------------------------------------------Trace File Generation Code---------------------------------------------------------------------------
BEGIN
   OPEN c_program_id(:p_conc_request_id);
    FETCH c_program_id INTO v_program_id, v_enable_trace;
    CLOSE c_program_id;

    SRW.message( 1275, 'v_program_id -> '||v_program_id
            ||', v_enable_trace -> '||v_enable_trace
            ||', request_id -> '||:P_CONC_REQUEST_ID);
    IF v_enable_trace = 'Y' THEN
   
    OPEN get_audsid;
  FETCH get_audsid INTO v_sid, v_serial, v_spid;
  CLOSE get_audsid;
 
  OPEN get_dbname;
  FETCH get_dbname INTO v_dbname;
  CLOSE get_dbname;
 
  srw.message(1275,'TraceFile Name = '||lower(v_dbname)||'_ora_'||v_spid||'.trc');
 
  SRW.DO_SQL('ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''');
    END IF; --Enable Trace
    return (true);
   EXCEPTION
      WHEN OTHERS THEN
          SRW.MESSAGE( 1275, 'Error during enabling the trace. ErrCode -> '||SQLCODE ||', ErrMesg -> '||SQLERRM );
   END;
end;