[Home] [Help]
PACKAGE BODY: APPS.XTR_DNM_PKG
Source
1 PACKAGE BODY XTR_DNM_PKG AS
2 /* $Header: xtrdnmpb.pls 120.6.12010000.2 2008/08/06 10:43:10 srsampat ship $ */
3
4
5 PROCEDURE AUTHORIZE(p_batch_id in NUMBER) IS
6
7 Cursor cur_dnm(p_batch_id in number) is
8 SELECT
9 A.COMPANY_CODE
10 , A.DEAL_NO
11 , A.REVAL_CCY
12 , SOB.CURRENCY_CODE SOB_CCY
13 , A.PERIOD_TO
14 , A.TRANSACTION_NO
15 , A.BATCH_ID
16 , decode(A.REALIZED_FLAG,'Y',A.REALISED_PL,A.UNREALISED_PL) REAL_UNREAL_AMT -- replaced A.CUMM_GAIN_LOSS_AMOUNT
17 , A.CURR_GAIN_LOSS_AMOUNT
18 , A.REALIZED_FLAG
19 , decode(A.DEAL_TYPE,'BOND',decode(A.AMOUNT_TYPE,'UNREAL','UNREAL' ,'REALAMC','REALAMC','REAL','REAL'),decode(A.REALIZED_FLAG,'Y','REAL','N','UNREAL')) AMOUNT_TYPE
20 , decode(A.DEAL_TYPE,'BOND',decode(A.AMOUNT_TYPE,'UNREAL','CCYUNRL','REALAMC','CCYAMRL','REAL','CCYREAL'),decode(A.REALIZED_FLAG,'Y','CCYREAL','N','CCYUNRL')) CURR_AMOUNT_TYPE
21 , decode(sign(decode(A.REALIZED_FLAG,'Y',A.REALISED_PL,A.UNREALISED_PL)),-1,'LOSS','PROFIT') ACTION
22 , decode(sign(A.CURR_GAIN_LOSS_AMOUNT),-1,'LOSS','PROFIT') CURR_ACTION
23 , B.PERIOD_END
24 , 'REVAL' DATE_TYPE
25 FROM
26 XTR_REVALUATION_DETAILS A,
27 XTR_BATCHES B,
28 XTR_BATCH_EVENTS C,
29 XTR_PARTIES_V P,
30 GL_SETS_OF_BOOKS SOB
31 WHERE B.BATCH_ID = p_batch_id
32 AND B.BATCH_ID = A.BATCH_ID
33 AND B.BATCH_ID = C.BATCH_ID
34 AND C.EVENT_CODE = 'REVAL'
35 AND P.PARTY_CODE = A.COMPANY_CODE
36 AND P.CHART_OF_ACCOUNTS_ID = SOB.CHART_OF_ACCOUNTS_ID
37 AND P.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
38 AND nvl(C.AUTHORIZED,'N') <> 'Y'
39 AND nvl(B.UPGRADE_BATCH,'N') <> 'Y'
40 AND ( nvl(nvl(A.REALISED_PL,A.UNREALISED_PL),0) <> 0 OR nvl(A.CURR_GAIN_LOSS_AMOUNT,0) <> 0 )
41 --AND ( nvl(A.CUMM_GAIN_LOSS_AMOUNT,0) <> 0 OR nvl(A.CURR_GAIN_LOSS_AMOUNT,0) <> 0 )
42 order by A.DEAL_NO;
43
44 Cursor cur_count IS
45 SELECT COUNT(*)
46 FROM XTR_REVALUATION_DETAILS
47 WHERE batch_id = p_batch_id
48 AND complete_flag = 'N';
49
50 l_amount_type XTR_AMOUNT_TYPES.AMOUNT_TYPE%TYPE;
51 l_curr_amount_type XTR_AMOUNT_TYPES.AMOUNT_TYPE%TYPE;
52 row_id VARCHAR2(64);
53 l_dnm_id NUMBER;
54 l_count NUMBER;
55
56 Begin
57 --xtr_debug_pkg.enable_file_debug;
58 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
59 xtr_debug_pkg.debug('>> BEGIN: XTR_DNM_PKG.AUTHORIZE');
60 END IF;
61
62 Open cur_count;
63 Fetch cur_count INTO l_count;
64 Close cur_count;
65
66 If nvl(l_count,1) = 0 Then
67 For dnm_rec in cur_dnm(p_batch_id)
68
69 Loop
70
71 -- If nvl(dnm_rec.CUMM_GAIN_LOSS_AMOUNT,0) <> 0 Then
72 If nvl(dnm_rec.REAL_UNREAL_AMT,0) <> 0 Then
73 select XTR_GAIN_LOSS_DNM_S.nextval into l_dnm_id from dual;
74
75 XTR_GAIN_LOSS_DNM_PKG.INSERT_ROW(
76 row_id,
77 l_dnm_id ,
78 dnm_rec.BATCH_ID,
79 dnm_rec.COMPANY_CODE,
80 dnm_rec.DEAL_NO,
81 dnm_rec.TRANSACTION_NO,
82 dnm_rec.date_type,
83 abs(dnm_rec.real_unreal_amt), -- replaced abs(dnm_rec.CUMM_GAIN_LOSS_AMOUNT),
84 dnm_rec.amount_type,
85 dnm_rec.action,
86 dnm_rec.REVAL_CCY,
87 dnm_rec.PERIOD_TO,
88 'R', -- Revaluation process flag
89 sysdate,
90 fnd_global.user_id,
91 sysdate,
92 fnd_global.user_id,
93 fnd_global.login_id
94 );
95 End If;
96
97 If nvl(dnm_rec.CURR_GAIN_LOSS_AMOUNT,0) <> 0 Then
98
99 select XTR_GAIN_LOSS_DNM_S.nextval into l_dnm_id from dual;
100
101 XTR_GAIN_LOSS_DNM_PKG.INSERT_ROW(
102 row_id,
103 l_dnm_id,
104 dnm_rec.BATCH_ID,
105 dnm_rec.COMPANY_CODE,
106 dnm_rec.DEAL_NO,
107 dnm_rec.TRANSACTION_NO,
108 dnm_rec.date_type,
109 abs(dnm_rec.CURR_GAIN_LOSS_AMOUNT),
110 dnm_rec.curr_amount_type,
111 dnm_rec.curr_action,
112 dnm_rec.SOB_CCY, -- replaced dnm_rec.REVAL_CCY with dnm_rec.SOB_CCY,
113 dnm_rec.PERIOD_TO,
114 'R', -- Revaluation process flag
115 sysdate,
116 fnd_global.user_id,
117 sysdate,
118 fnd_global.user_id,
119 fnd_global.login_id
120 );
121 End If;
122
123 End Loop;
124
125 Begin
126 Update XTR_BATCH_EVENTS
127 set AUTHORIZED = 'Y',
128 AUTHORIZED_BY = FND_GLOBAL.USER_ID,
129 AUTHORIZED_ON = SYSDATE,
130 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
131 LAST_UPDATE_DATE = SYSDATE,
132 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
133 where BATCH_ID = p_batch_id
134 and EVENT_CODE = 'REVAL'
135 and nvl(AUTHORIZED,'N') <> 'Y';
136
137 if (sql%notfound) then
138 raise no_data_found;
139 end if;
140 Exception
141 When Others Then
142 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
143 xtr_debug_pkg.debug('AUTHORIZE: ' || '>>EXCEPTION: Error updating XTR_BATCH_EVENTS');
144 END IF;
145 Raise;
146 End;
147
148 Else
149 NULL;
150 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
151 xtr_debug_pkg.debug('>>XTR_DNM_PKG.AUTHORIZE-->Can not Authorize with incomplete reval details');
152 END IF;
153 End If;
154
155 EXCEPTION
156 When Others Then
157 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
158 xtr_debug_pkg.debug('AUTHORIZE: ' || '>>EXCEPTION: Error in XTR_GAIN_LOSS_DNM_PKG.INSERT_ROW');
159 END IF;
160 Raise;
161 End AUTHORIZE;
162
163
164 PROCEDURE UNAUTHORIZE(p_batch_id in NUMBER) IS
165
166 -- Bug 2685848 additions.
167
168 Cursor DNM is
169 Select rowid
170 From XTR_GAIN_LOSS_DNM A
171 where A.BATCH_ID = p_batch_id
172 and EXISTS (select 'Dummy' from XTR_BATCH_EVENTS B
173 where B.BATCH_ID = A.BATCH_ID
174 and B.EVENT_CODE = 'REVAL'
175 and B.AUTHORIZED = 'Y')
176 and NOT EXISTS (select 'X' from XTR_BATCH_EVENTS C
177 where C.BATCH_ID = A.BATCH_ID
178 and C.EVENT_CODE = 'JRNLGN')
179 for update nowait;
180
181 Cursor BE is
182 Select rowid
183 From XTR_BATCH_EVENTS
184 Where BATCH_ID = p_batch_id
185 and EVENT_CODE = 'REVAL'
186 and AUTHORIZED = 'Y'
187 for update of batch_id nowait;
188
189 l_rowid rowid;
190 l_err_table number;
191 l_err_num number;
192 l_err_msg varchar2(100);
193
194 -- End bug 2685848 additions.
195 Begin
196
197 -- Bug 2685848 start modifications.
198 -- Ensure all records to be deleted/updated can be successfully accessed
199 -- before actual execution.
200
201 l_err_table := 1;
202 Open DNM;
203 Loop
204 Fetch DNM into l_rowid;
205 Exit when DNM%NOTFOUND;
206
207 Delete from XTR_GAIN_LOSS_DNM
208 Where rowid = l_rowid;
209 End Loop;
210 Close DNM;
211
212 l_err_table := 2;
213 Open BE;
214 Loop
215 Fetch BE into l_rowid;
216 Exit when BE%NOTFOUND;
217
218 Update XTR_BATCH_EVENTS
219 set AUTHORIZED = 'N',
220 AUTHORIZED_BY = NULL,
221 AUTHORIZED_ON = NULL,
222 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
223 LAST_UPDATE_DATE = SYSDATE,
224 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
225 where rowid = l_rowid;
226 End Loop;
227 Close BE;
228
229 EXCEPTION
230 When app_exception.record_lock_exception then
231 If (xtr_debug_pkg.pg_sqlplus_enable_flag = 1) THEN
232 xtr_debug_pkg.debug('>>XTR_DNM_PKG.UNAUTHORIZE --> Unable to lock records');
233 End If;
234 If (DNM%ISOPEN) then
235 Close DNM;
236 End If;
237 If (BE%ISOPEN) then
238 Close BE;
239 End If;
240 If (l_err_table = 1) then
241 FND_MESSAGE.Set_Name ('XTR', 'XTR_DNM_LOCKED');
242 Elsif (l_err_table = 2) then
243 FND_MESSAGE.Set_Name ('XTR', 'XTR_REVAL_EVENT_LOCKED');
244 End If;
245 App_Exception.Raise_Exception;
246 /*
247 When no_data_found then
248 dbms_output.put_line ('no data found exception');
249 If (DNM%ISOPEN) then
250 Close DNM;
251 End If;
252 If (BE%ISOPEN) then
253 Close BE;
254 End If;
255 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
256 xtr_debug_pkg.debug('>>XTR_DNM_PKG.UNAUTHORIZE-->No_Data_Found');
257 END IF;
258 */
259 When Others Then
260 If (DNM%ISOPEN) then
261 Close DNM;
262 End If;
263 If (BE%ISOPEN) then
264 Close BE;
265 End If;
266 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
267 xtr_debug_pkg.debug('>>EXCEPTION: Error in XTR_DNM_PKG.UNAUTHORIZE');
268 END IF;
269 l_err_num := SQLCODE;
270 l_err_msg := SQLERRM(l_err_num);
271 FND_MESSAGE.SET_NAME('XTR', 'XTR_UNHANDLED_EXCEPTION');
272 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'XTR_DNM_PKG.Unauthorize');
273 FND_MESSAGE.SET_TOKEN('EVENT', l_err_msg);
274 App_Exception.Raise_Exception;
275 End UNAUTHORIZE;
276
277 END XTR_DNM_PKG;