[Home] [Help]
PACKAGE BODY: APPS.FUN_WF_AUTHORIZATION_LIMIT
Source
1 PACKAGE BODY FUN_WF_AUTHORIZATION_LIMIT AS
2 /* $Header: FUN_WF_AUTH_LIMIT_B.pls 120.2 2011/04/06 12:10:00 srampure noship $ */
3
4 FUNCTION get_conv_rate(p_trx_id NUMBER) RETURN NUMBER
5 IS
6
7 l_precision NUMBER;
8 l_gl_date FUN_TRX_BATCHES.GL_DATE%TYPE;
9 l_currency_code FUN_TRX_BATCHES.CURRENCY_CODE%TYPE;
10 l_exchange_rate_type FUN_TRX_BATCHES.EXCHANGE_RATE_TYPE%TYPE;
11 l_to_ledger_id FUN_TRX_HEADERS.TO_LEDGER_ID%TYPE;
12
13 BEGIN
14
15 SELECT TRXB.GL_DATE,
16 TRXB.CURRENCY_CODE,
17 TRXB.EXCHANGE_RATE_TYPE,
18 TRXH.TO_LEDGER_ID
19 INTO l_gl_date, l_currency_code,
20 l_exchange_rate_type, l_to_ledger_id
21 FROM FUN_TRX_HEADERS TRXH,
22 FUN_TRX_BATCHES TRXB
23 WHERE TRXB.BATCH_ID = TRXH.BATCH_ID
24 AND TRXH.TRX_ID = p_trx_id;
25
26 return GL_CURRENCY_API.Get_Rate_Sql(l_to_ledger_id,
27 l_currency_code,
28 l_gl_date,
29 l_exchange_rate_type);
30 EXCEPTION
31 WHEN OTHERS
32 THEN
33 FND_MSG_PUB.ADD;
34
35 IF FND_MSG_PUB.Check_Msg_Level
36 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
37 THEN
38 FND_MSG_PUB.Add_Exc_Msg
39 ( 'FUN_WF_AUTHORIZATION_LIMIT',
40 'get_converted_amount'
41 );
42 END IF;
43 return -1;
44
45 END;
46
47
48 /**
49 *This package is called from FUNRMAIN to check the authorization limit.
50 *Default return value: T
51 */
52
53 PROCEDURE CHECK_AUTHORIZATION_LIMIT_T(
54 itemtype IN varchar2,
55 itemkey IN varchar2,
56 actid IN number,
57 funcmode IN varchar2,
58 resultout IN OUT NOCOPY varchar2)
59 IS
60 l_result varchar2(1);
61 l_transaction_id NUMBER;
62 l_limit NUMBER;
63 l_trx_amount NUMBER;
64 l_status varchar2(1);
65 l_msg_count number;
66 l_msg_data varchar2(1000);
67 l_authorization_error VARCHAR2(4000);
68 l_batch_number VARCHAR2(15);
69
70 BEGIN
71 l_result := 'Y';
72 /**
73 * Implement custom code here.
74 * If user has approval limit set l_result = 'Y'
75 * If user does not have approval limit set l_result = 'N'
76 */
77
78 /*
79 wf_engine.SetItemAttrText(itemtype => itemtype,
80 itemkey => itemkey,
81 aname => 'AUTHORIZATION_ERROR',
82 avalue => l_authorization_error);
83 l_transaction_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
84 itemkey => itemkey,
85 aname => 'TRX_ID');
86 l_batch_number := wf_engine.GetItemAttrNumber (itemtype => itemtype,
87 itemkey => itemkey,
88 aname => 'BATCH_NUMBER');
89 l_authorization_error := 'Batch Number:'||l_batch_number||', recipients of the Approval Hierarchy do not have the required authorization limits';
90 SELECT nvl(min(authorization_limit), -1)
91 INTO l_limit
92 FROM GL_AUTHORIZATION_LIMITS GAL, FUN_TRX_HEADERS TRXH
93 WHERE employee_id = (SELECT EMPLOYEE_ID
94 FROM FND_USER
95 WHERE USER_ID = fnd_global.user_id)
96 AND GAL.ledger_id = TRXH.TO_LEDGER_ID
97 AND TRXH.TRX_ID = l_transaction_id;
98
99 SELECT ABS(NVL(INIT_AMOUNT_DR, 0) - NVL(INIT_AMOUNT_CR, 0))
100 INTO l_trx_amount
101 FROM FUN_TRX_HEADERS
102 WHERE TRX_ID = l_transaction_id;
103
104 IF (l_trx_amount * get_conv_rate(l_transaction_id) <= l_limit) THEN
105 l_result := 'Y';
106 ELSE
107 l_result := 'N';
108
109 fun_trx_pvt.update_trx_status
110 (p_api_version => 1.0,
111 x_return_status => l_status,
112 x_msg_count => l_msg_count,
113 x_msg_data => l_msg_data,
114 p_trx_id => l_transaction_id,
115 p_update_status_to => 'ERROR');
116
117 END IF;*/
118
119 IF (l_result = 'Y') THEN
120 resultout := wf_engine.eng_completed||':T';
121 RETURN;
122 ELSE
123 resultout := wf_engine.eng_completed||':F';
124 RETURN;
125 END IF;
126
127 resultout := wf_engine.eng_null;
128 RETURN;
129
130 EXCEPTION
131
132 WHEN others THEN
133 wf_core.context('FUN_WF_AUTHORIZATION_LIMIT', 'CHECK_AUTHORIZATION_LIMIT_T',
134 itemtype, itemkey, TO_CHAR(actid), funcmode);
135 RAISE;
136
137 END CHECK_AUTHORIZATION_LIMIT_T;
138
139 /**
140 *This package is called from FUNRMAIN to check the authorization limit.
141 *Default return value: F
142 */
143
144 PROCEDURE CHECK_AUTHORIZATION_LIMIT_F(
145 itemtype IN varchar2,
146 itemkey IN varchar2,
147 actid IN number,
148 funcmode IN varchar2,
149 resultout IN OUT NOCOPY varchar2)
150 IS
151 l_result varchar2(1);
152 l_transaction_id NUMBER;
153 l_limit NUMBER;
154 l_trx_amount NUMBER;
155 l_authorization_error VARCHAR2(4000);
156
157 BEGIN
158 l_result := 'N';
159 /**
160 * Implement custom code here.
161 * If user has approval limit set l_result = 'Y'
162 * If user does not have approval limit set l_result = 'N'
163 */
164
165 /*
166 l_transaction_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
167 itemkey => itemkey,
168 aname => 'TRX_ID');
169 SELECT nvl(min(authorization_limit), -1)
170 INTO l_limit
171 FROM GL_AUTHORIZATION_LIMITS GAL, FUN_TRX_HEADERS TRXH
172 WHERE employee_id = (SELECT EMPLOYEE_ID
173 FROM FND_USER
174 WHERE USER_ID = fnd_global.user_id)
175 AND GAL.ledger_id = TRXH.TO_LEDGER_ID
176 AND TRXH.TRX_ID = l_transaction_id;
177
178 SELECT ABS(NVL(INIT_AMOUNT_DR, 0) - NVL(INIT_AMOUNT_CR, 0))
179 INTO l_trx_amount
180 FROM FUN_TRX_HEADERS
181 WHERE TRX_ID = l_transaction_id;
182
183 IF (l_trx_amount * get_conv_rate(l_transaction_id) <= l_limit) THEN
184 l_result := 'Y';
185 ELSE
186 l_result := 'N';
187 END IF; */
188
189 IF (l_result = 'Y') THEN
190 resultout := wf_engine.eng_completed||':T';
191 RETURN;
192 ELSE
193 resultout := wf_engine.eng_completed||':F';
194 RETURN;
195 END IF;
196
197 resultout := wf_engine.eng_null;
198 RETURN;
199
200 EXCEPTION
201
202 WHEN others THEN
203 wf_core.context('FUN_WF_AUTHORIZATION_LIMIT', 'CHECK_AUTHORIZATION_LIMIT_F',
204 itemtype, itemkey, TO_CHAR(actid), funcmode);
205 RAISE;
206
207 END CHECK_AUTHORIZATION_LIMIT_F;
208
209 PROCEDURE HAS_VALID_EXCHANGE_RATE(
210 x_return_status OUT NOCOPY VARCHAR2,
211 p_trx_id IN FUN_TRX_HEADERS.TRX_ID%TYPE)
212 IS
213
214 l_rate NUMBER;
215
216 BEGIN
217 x_return_status := FND_API.G_RET_STS_SUCCESS;
218 l_rate := get_conv_rate(p_trx_id);
219 IF l_rate = -1 THEN
220 x_return_status := FND_API.G_RET_STS_ERROR;
221 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
222 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_CONV_RATE_NOT_FOUND');
223 FND_MSG_PUB.Add;
224 END IF;
225 END IF;
226
227 EXCEPTION
228 WHEN OTHERS
229 THEN
230 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
231 THEN
232 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
233 'fun.plsql.FUN_WF_AUTHORIZATION_LIMIT.HAS_VALID_EXCHANGE_RATE',
234 SQLERRM || ' Error occurred ');
235 END IF;
236
237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
238
239 END HAS_VALID_EXCHANGE_RATE;
240
241 END FUN_WF_AUTHORIZATION_LIMIT;