1: PACKAGE BODY QP_LIMIT_TRANSACTIONS_PVT AS
2: /* $Header: QPXVLTDB.pls 120.4 2011/12/22 15:21:41 jputta ship $ */
3:
4: l_debug VARCHAR2(3);
5:
11: IS
12: PRAGMA AUTONOMOUS_TRANSACTION;
13: BEGIN
14: /*
15: INDX,qp_limit_transactions_pvt.update.upd1,QP_LIMIT_BALANCES_U1,LIMIT_BALANCE_ID,1
16: */
17: --sql statement upd1
18: UPDATE qp_limit_balances
19: SET available_amount = available_amount + p_amount
32: PROCEDURE Delete (p_pricing_event_code IN VARCHAR2,
33: x_return_status OUT NOCOPY VARCHAR2)
34: IS
35: /*
36: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
37: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIST_HEADER_ID,2
38: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
39: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
40: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
33: x_return_status OUT NOCOPY VARCHAR2)
34: IS
35: /*
36: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
37: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIST_HEADER_ID,2
38: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
39: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
40: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
41: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
34: IS
35: /*
36: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
37: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIST_HEADER_ID,2
38: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
39: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
40: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
41: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
42: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
35: /*
36: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
37: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIST_HEADER_ID,2
38: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
39: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
40: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
41: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
42: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
43: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
36: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
37: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIST_HEADER_ID,2
38: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
39: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
40: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
41: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
42: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
43: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
44: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
37: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIST_HEADER_ID,2
38: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
39: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
40: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
41: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
42: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
43: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
44: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
45: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_EVENT_PHASES_U1,PRICING_EVENT_CODE,1
38: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
39: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
40: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
41: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
42: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
43: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
44: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
45: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_EVENT_PHASES_U1,PRICING_EVENT_CODE,1
46: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_EVENT_PHASES_U1,PRICING_PHASE_ID,2
39: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
40: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
41: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
42: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
43: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
44: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
45: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_EVENT_PHASES_U1,PRICING_EVENT_CODE,1
46: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_EVENT_PHASES_U1,PRICING_PHASE_ID,2
47: */
40: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
41: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
42: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
43: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
44: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
45: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_EVENT_PHASES_U1,PRICING_EVENT_CODE,1
46: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_EVENT_PHASES_U1,PRICING_PHASE_ID,2
47: */
48: CURSOR limit_trans_cur(a_pricing_event_code VARCHAR2)
41: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
42: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
43: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
44: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
45: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_EVENT_PHASES_U1,PRICING_EVENT_CODE,1
46: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_EVENT_PHASES_U1,PRICING_PHASE_ID,2
47: */
48: CURSOR limit_trans_cur(a_pricing_event_code VARCHAR2)
49: IS
42: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
43: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
44: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
45: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_EVENT_PHASES_U1,PRICING_EVENT_CODE,1
46: INDX,qp_limit_transactions_pvt.delete.limit_trans_cur,QP_EVENT_PHASES_U1,PRICING_PHASE_ID,2
47: */
48: CURSOR limit_trans_cur(a_pricing_event_code VARCHAR2)
49: IS
50: SELECT t.limit_balance_id, t.list_header_id, t.list_line_id,
108: Update_Balance(l_rec.amount, l_rec.limit_balance_id);
109:
110: --Then delete the transaction record
111: /*
112: INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
113: INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,LIST_HEADER_ID,2
114: INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
115: INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
116: */
109:
110: --Then delete the transaction record
111: /*
112: INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
113: INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,LIST_HEADER_ID,2
114: INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
115: INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
116: */
117: --sql statement del1
110: --Then delete the transaction record
111: /*
112: INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
113: INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,LIST_HEADER_ID,2
114: INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
115: INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
116: */
117: --sql statement del1
118: DELETE FROM qp_limit_transactions
111: /*
112: INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
113: INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,LIST_HEADER_ID,2
114: INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
115: INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
116: */
117: --sql statement del1
118: DELETE FROM qp_limit_transactions
119: WHERE limit_balance_id = l_rec.limit_balance_id
159: -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
160:
161: END Delete;
162:
163: END QP_LIMIT_TRANSACTIONS_PVT;