APPS.QP_LIMIT_TRANSACTIONS_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 7

 Procedure to autonomously update qp_limit_balances table.
 ***********************************************************************/
PROCEDURE Update_Balance(p_amount           IN  NUMBER,
                         p_limit_balance_id IN  NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;

Line: 15

INDX,qp_limit_transactions_pvt.update.upd1,QP_LIMIT_BALANCES_U1,LIMIT_BALANCE_ID,1
*/
    --sql statement upd1
    UPDATE qp_limit_balances
    SET    available_amount = available_amount + p_amount
    WHERE  limit_balance_id = p_limit_balance_id;

Line: 24

END Update_Balance;

Line: 28

   Procedure to Delete a Limit Transaction record for an event and phase
   combination that does not have a corresponding modifier in the
   qp_npreq_ldets_tmp table.
***********************************************************************/
PROCEDURE Delete (p_pricing_event_code IN  VARCHAR2,
                  x_return_status      OUT NOCOPY VARCHAR2)
IS
/*
INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIST_HEADER_ID,2
INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_EVENT_PHASES_U1,PRICING_EVENT_CODE,1
INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_EVENT_PHASES_U1,PRICING_PHASE_ID,2
*/
CURSOR limit_trans_cur(a_pricing_event_code VARCHAR2)
IS
  SELECT t.limit_balance_id, t.list_header_id, t.list_line_id,
         t.price_request_code, t.pricing_phase_id, t.amount
  FROM   qp_limit_transactions t, qp_npreq_lines_tmp b
  WHERE  t.pricing_phase_id IN (SELECT evt.pricing_phase_id
                                FROM   qp_event_phases evt, qp_pricing_phases qpp
--                                WHERE pricing_event_code = a_pricing_event_code)
                                --fix for bug 4765137
                                WHERE evt.PRICING_PHASE_ID = qpp.PRICING_PHASE_ID
                                AND instr(a_pricing_event_code, evt.pricing_event_code || ',') > 0
				AND ((b.price_flag='Y') OR (Nvl(qpp.USER_FREEZE_OVERRIDE_FLAG,qpp.FREEZE_OVERRIDE_FLAG) = 'Y' AND b.price_flag='P' ))) --bug#13508707
  AND    (((t.price_request_code = b.price_request_code)
  AND    NOT EXISTS (SELECT 'X'
                     FROM   qp_npreq_ldets_tmp l
                     WHERE  l.created_from_list_header_id = t.list_header_id
                     AND    l.created_from_list_line_id = t.list_line_id
                     AND    l.pricing_phase_id = t.pricing_phase_id
                     AND    l.line_index = b.line_index
                     AND    l.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW))
 OR (t.price_request_code = ( SELECT 'ASO-'|| asm.quote_header_id || '-' || asm.quote_line_id
                                            FROM oe_order_lines_all oel, aso_shipments asm
                                            WHERE oel.line_id = b.line_id
                                            AND asm.order_line_id = oel.line_id
                                        )));  --bug#10130826;

Line: 77

  QP_PREQ_GRP.engine_debug('***Begin Procedure Delete*** ');

Line: 78

  QP_PREQ_GRP.engine_debug('***Begin Procedure Delete***: price_event: '|| p_pricing_event_code);

Line: 80

  for cl in (select line.line_index, line.price_request_code, trx.list_header_id, trx.pricing_phase_id
             from qp_npreq_lines_tmp line, qp_limit_transactions trx
             where trx.price_request_code = line.price_request_code)
  LOOP
    QP_PREQ_GRP.engine_debug('Limit transactions for current lines '||cl.line_index||' price_reqCode '||cl.price_request_code||' listhdrid '||cl.list_header_id);

Line: 85

    for cl1 in (select ldet.created_from_list_header_id, ldet.pricing_status_code
                from qp_npreq_ldets_tmp ldet
                where ldet.created_from_list_header_id = cl.list_header_id
                and ldet.line_index = cl.line_index
                and ldet.pricing_phase_id in (select pricing_phase_id
                                              from qp_event_phases evt
                                              where instr(p_pricing_event_code, evt.pricing_event_code || ',') > 0))
    LOOP
      QP_PREQ_GRP.engine_debug('adjustments for above transactions '||cl1.created_from_list_header_id||' status '||cl1.pricing_status_code);

Line: 108

    Update_Balance(l_rec.amount, l_rec.limit_balance_id);

Line: 112

INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,LIST_HEADER_ID,2
INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
*/
    --sql statement del1
    DELETE FROM qp_limit_transactions
    WHERE  limit_balance_id = l_rec.limit_balance_id
    AND    list_header_id  = l_rec.list_header_id
    AND    list_line_id  = l_rec.list_line_id
    AND    price_request_code  = l_rec.price_request_code;

Line: 127

  QP_PREQ_GRP.engine_debug('***End Procedure Delete*** ');

Line: 161

END Delete;