[Home] [Help]
PACKAGE BODY: APPS.QP_LIMIT_TRANSACTIONS_PVT
Source
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
6 /************************************************************************
7 Procedure to autonomously update qp_limit_balances table.
8 ***********************************************************************/
9 PROCEDURE Update_Balance(p_amount IN NUMBER,
10 p_limit_balance_id IN NUMBER)
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
20 WHERE limit_balance_id = p_limit_balance_id;
21
22 COMMIT;
23
24 END Update_Balance;
25
26
27 /***********************************************************************
28 Procedure to Delete a Limit Transaction record for an event and phase
29 combination that does not have a corresponding modifier in the
30 qp_npreq_ldets_tmp table.
31 ***********************************************************************/
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
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
50 SELECT t.limit_balance_id, t.list_header_id, t.list_line_id,
51 t.price_request_code, t.pricing_phase_id, t.amount
52 FROM qp_limit_transactions t, qp_npreq_lines_tmp b
53 WHERE t.pricing_phase_id IN (SELECT evt.pricing_phase_id
54 FROM qp_event_phases evt, qp_pricing_phases qpp
55 -- WHERE pricing_event_code = a_pricing_event_code)
56 --fix for bug 4765137
57 WHERE evt.PRICING_PHASE_ID = qpp.PRICING_PHASE_ID
58 AND instr(a_pricing_event_code, evt.pricing_event_code || ',') > 0
59 AND ((b.price_flag='Y') OR (Nvl(qpp.USER_FREEZE_OVERRIDE_FLAG,qpp.FREEZE_OVERRIDE_FLAG) = 'Y' AND b.price_flag='P' ))) --bug#13508707
60 AND (((t.price_request_code = b.price_request_code)
61 AND NOT EXISTS (SELECT 'X'
62 FROM qp_npreq_ldets_tmp l
63 WHERE l.created_from_list_header_id = t.list_header_id
64 AND l.created_from_list_line_id = t.list_line_id
65 AND l.pricing_phase_id = t.pricing_phase_id
66 AND l.line_index = b.line_index
67 AND l.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW))
68 OR (t.price_request_code = ( SELECT 'ASO-'|| asm.quote_header_id || '-' || asm.quote_line_id
69 FROM oe_order_lines_all oel, aso_shipments asm
70 WHERE oel.line_id = b.line_id
71 AND asm.order_line_id = oel.line_id
72 ))); --bug#10130826;
73
74 BEGIN
75 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
76 IF l_debug = FND_API.G_TRUE THEN
77 QP_PREQ_GRP.engine_debug('***Begin Procedure Delete*** ');
78 QP_PREQ_GRP.engine_debug('***Begin Procedure Delete***: price_event: '|| p_pricing_event_code);
79
80 for cl in (select line.line_index, line.price_request_code, trx.list_header_id, trx.pricing_phase_id
81 from qp_npreq_lines_tmp line, qp_limit_transactions trx
82 where trx.price_request_code = line.price_request_code)
83 LOOP
84 QP_PREQ_GRP.engine_debug('Limit transactions for current lines '||cl.line_index||' price_reqCode '||cl.price_request_code||' listhdrid '||cl.list_header_id);
85 for cl1 in (select ldet.created_from_list_header_id, ldet.pricing_status_code
86 from qp_npreq_ldets_tmp ldet
87 where ldet.created_from_list_header_id = cl.list_header_id
88 and ldet.line_index = cl.line_index
89 and ldet.pricing_phase_id in (select pricing_phase_id
90 from qp_event_phases evt
91 where instr(p_pricing_event_code, evt.pricing_event_code || ',') > 0))
92 LOOP
93 QP_PREQ_GRP.engine_debug('adjustments for above transactions '||cl1.created_from_list_header_id||' status '||cl1.pricing_status_code);
94 END LOOP;
95 END LOOP;
96
97 END IF;
98 x_return_status := FND_API.G_RET_STS_SUCCESS;
99
100 FOR l_rec IN limit_trans_cur(p_pricing_event_code)
101 LOOP
102 IF l_debug = FND_API.G_TRUE THEN
103 QP_PREQ_GRP.engine_debug('***Deleting LimitbalanceId: '||l_rec.limit_balance_id||' pricereqcode '||l_rec.price_request_code);
104 END IF;
105
106 --For each transaction record selected in the cursor increment the
107 --corresponding limit balance by the transaction amount
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 */
117 --sql statement del1
118 DELETE FROM qp_limit_transactions
119 WHERE limit_balance_id = l_rec.limit_balance_id
120 AND list_header_id = l_rec.list_header_id
121 AND list_line_id = l_rec.list_line_id
122 AND price_request_code = l_rec.price_request_code;
123
124 END LOOP; --Loop over records in limit_trans_cur
125
126 IF l_debug = FND_API.G_TRUE THEN
127 QP_PREQ_GRP.engine_debug('***End Procedure Delete*** ');
128
129 END IF;
130 EXCEPTION
131 WHEN FND_API.G_EXC_ERROR THEN
132
133 x_return_status := FND_API.G_RET_STS_ERROR;
134
135 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
136 THEN
137 FND_MESSAGE.SET_NAME('QP','QP_INVALID_FORMULA');
138 END IF;
139
140 -- RAISE;
141
142 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
143
144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
145
146 -- RAISE;
147
148 WHEN OTHERS THEN
149 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
150
151 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
152 THEN
153 FND_MSG_PUB.Add_Exc_Msg
154 (G_PKG_NAME
155 , 'Check Balance'
156 );
157 END IF;
158
159 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
160
161 END Delete;
162
163 END QP_LIMIT_TRANSACTIONS_PVT;