eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.QP_LIMIT_TRANSACTIONS_PVT

Source


1 PACKAGE BODY QP_LIMIT_TRANSACTIONS_PVT AS
2 /* $Header: QPXVLTDB.pls 120.1 2005/12/27 13:57:58 gtippire noship $ */
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 pricing_phase_id
54                                 FROM   qp_event_phases evt
55 --                                WHERE pricing_event_code = a_pricing_event_code)
56                                 --fix for bug 4765137
57                                 WHERE instr(a_pricing_event_code, evt.pricing_event_code || ',') > 0)
58   AND    t.price_request_code = b.price_request_code
59   AND    NOT EXISTS (SELECT 'X'
60                      FROM   qp_npreq_ldets_tmp l
61                      WHERE  l.created_from_list_header_id = t.list_header_id
62                      AND    l.created_from_list_line_id = t.list_line_id
63                      AND    l.pricing_phase_id = t.pricing_phase_id
64                      AND    l.line_index = b.line_index
65                      AND    l.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW);
66 
67 BEGIN
68   l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
69   IF l_debug = FND_API.G_TRUE THEN
70   QP_PREQ_GRP.engine_debug('***Begin Procedure Delete*** ');
71   QP_PREQ_GRP.engine_debug('***Begin Procedure Delete***: price_event: '|| p_pricing_event_code);
72 
73   for cl in (select line.line_index, line.price_request_code, trx.list_header_id, trx.pricing_phase_id
74              from qp_npreq_lines_tmp line, qp_limit_transactions trx
75              where trx.price_request_code = line.price_request_code)
76   LOOP
77     QP_PREQ_GRP.engine_debug('Limit transactions for current lines '||cl.line_index||' price_reqCode '||cl.price_request_code||' listhdrid '||cl.list_header_id);
78     for cl1 in (select ldet.created_from_list_header_id, ldet.pricing_status_code
79                 from qp_npreq_ldets_tmp ldet
80                 where ldet.created_from_list_header_id = cl.list_header_id
81                 and ldet.line_index = cl.line_index
82                 and ldet.pricing_phase_id in (select pricing_phase_id
83                                               from qp_event_phases evt
84                                               where instr(p_pricing_event_code, evt.pricing_event_code || ',') > 0))
85     LOOP
86       QP_PREQ_GRP.engine_debug('adjustments for above transactions '||cl1.created_from_list_header_id||' status '||cl1.pricing_status_code);
87     END LOOP;
88   END LOOP;
89 
90   END IF;
91   x_return_status := FND_API.G_RET_STS_SUCCESS;
92 
93   FOR l_rec IN limit_trans_cur(p_pricing_event_code)
94   LOOP
95     IF l_debug = FND_API.G_TRUE THEN
96       QP_PREQ_GRP.engine_debug('***Deleting LimitbalanceId: '||l_rec.limit_balance_id||' pricereqcode '||l_rec.price_request_code);
97     END IF;
98 
99     --For each transaction record selected in the cursor increment the
100     --corresponding limit balance by the transaction amount
101     Update_Balance(l_rec.amount, l_rec.limit_balance_id);
102 
103     --Then delete the transaction record
104 /*
105 INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
106 INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,LIST_HEADER_ID,2
107 INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
108 INDX,qp_limit_transactions_pvt.delete.del1,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
109 */
110     --sql statement del1
111     DELETE FROM qp_limit_transactions
112     WHERE  limit_balance_id = l_rec.limit_balance_id
113     AND    list_header_id  = l_rec.list_header_id
114     AND    list_line_id  = l_rec.list_line_id
115     AND    price_request_code  = l_rec.price_request_code;
116 
117   END LOOP; --Loop over records in limit_trans_cur
118 
119   IF l_debug = FND_API.G_TRUE THEN
120   QP_PREQ_GRP.engine_debug('***End Procedure Delete*** ');
121 
122   END IF;
123 EXCEPTION
124     WHEN FND_API.G_EXC_ERROR THEN
125 
126            x_return_status := FND_API.G_RET_STS_ERROR;
127 
128            IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
129            THEN
130                FND_MESSAGE.SET_NAME('QP','QP_INVALID_FORMULA');
131            END IF;
132 
133 --         RAISE;
134 
135     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
136 
137            x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
138 
139 --         RAISE;
140 
141     WHEN OTHERS THEN
142            x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
143 
144         IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
145            THEN
146                FND_MSG_PUB.Add_Exc_Msg
147                   (G_PKG_NAME
148                   , 'Check Balance'
149                   );
150            END IF;
151 
152 --         RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
153 
154 END Delete;
155 
156 END QP_LIMIT_TRANSACTIONS_PVT;