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.POS_SBD_TBL_PKG

Source


1 PACKAGE BODY POS_SBD_TBL_PKG as
2 /*$Header: POSSBDTB.pls 120.0 2005/08/21 08:48:24 gdwivedi noship $ */
3 
4 PROCEDURE del_row_pos_acnt_summ_req (
5   p_assignment_id	   IN NUMBER
6 , x_status        out nocopy VARCHAR2
7 , x_exception_msg out nocopy VARCHAR2
8 )
9 IS
10 
11 l_step NUMBER;
12 
13 BEGIN
14 
15 l_step := 0;
16 
17 delete from pos_acnt_addr_summ_req where assignment_id = p_assignment_id;
18 
19 l_step := 1;
20 
21 x_status := 'S';
22 
23 EXCEPTION
24     WHEN OTHERS THEN
25       X_STATUS  :='E';
26       x_exception_msg := 'Failure at step ' || l_step || Sqlerrm;
27       raise_application_error(-20043, x_exception_msg, true);
28 END del_row_pos_acnt_summ_req;
29 
30 PROCEDURE del_row_pos_acnt_addr_req (
31   p_assignment_request_id	   IN NUMBER
32 , x_status        out nocopy VARCHAR2
33 , x_exception_msg out nocopy VARCHAR2
34 )
35 IS
36 
37 l_step NUMBER;
38 
39 BEGIN
40 
41 l_step := 0;
42 
43 delete from pos_acnt_addr_req where assignment_request_id = p_assignment_request_id;
44 
45 l_step := 1;
46 
47 x_status := 'S';
48 
49 EXCEPTION
50     WHEN OTHERS THEN
51       X_STATUS  :='E';
52       x_exception_msg := 'Failure at step ' || l_step || Sqlerrm;
53       raise_application_error(-20045, x_exception_msg, true);
54 END del_row_pos_acnt_addr_req;
55 
56 PROCEDURE del_row_pos_acnt_gen_req (
57   p_account_request_id	   IN NUMBER
58 , x_status        out nocopy VARCHAR2
59 , x_exception_msg out nocopy VARCHAR2
60 )
61 IS
62 
63 l_step NUMBER;
64 
65 BEGIN
66 
67 l_step := 0;
68 
69 delete from pos_acnt_gen_req where account_request_id = p_account_request_id;
70 
71 l_step := 1;
72 
73 x_status := 'S';
74 
75 EXCEPTION
76     WHEN OTHERS THEN
77       X_STATUS  :='E';
78       x_exception_msg := 'Failure at step ' || l_step || Sqlerrm;
79       raise_application_error(-20049, x_exception_msg, true);
80 END del_row_pos_acnt_gen_req;
81 
82 PROCEDURE update_row_pos_acnt_summ_req (
83   p_assignment_id	   IN NUMBER
84 , p_assignment_request_id  IN NUMBER
85 , p_ext_bank_account_id    IN NUMBER
86 , p_account_request_id     IN NUMBER
87 , p_start_date             IN DATE
88 , p_end_date               IN DATE
89 , p_priority               IN NUMBER
90 , p_assignment_status      IN VARCHAR2
91 , x_status        out nocopy VARCHAR2
92 , x_exception_msg out nocopy VARCHAR2
93 )
94 IS
95 
96 l_step NUMBER;
97 
98 BEGIN
99 
100    l_step:= 0;
101 
102     update pos_acnt_addr_summ_req set
103      last_update_date = sysdate
104    , last_updated_by = fnd_global.user_id
105    , last_update_login  = fnd_global.login_id
106    , ext_bank_account_id = nvl(p_ext_bank_account_id,ext_bank_account_id)
107    , account_request_id = nvl(p_account_request_id, account_request_id)
108    , assignment_status = nvl(p_assignment_status, assignment_status)
109    , start_date = p_start_date
110    , end_date = p_end_date
111    , priority = p_priority
112    where assignment_request_id = p_assignment_request_id
113    and assignment_id = p_assignment_id;
114 
115    l_step:= 1;
116 
117 x_status := 'S';
118 
119 EXCEPTION
120     WHEN OTHERS THEN
121       X_STATUS  :='E';
122       x_exception_msg := 'Failure at step ' || l_step || Sqlerrm;
123       raise_application_error(-20046, x_exception_msg, true);
124 END update_row_pos_acnt_summ_req;
125 
126 
127 /* This procedure create a row in POS_ACNT_ADDR_REQ
128  *
129  */
130 PROCEDURE insert_row_pos_acnt_addr_req (
131   p_mapping_id	   in NUMBER
132 , p_request_type   in varchar2
133 , p_party_site_id  in number
134 , p_address_request_id in number
135 , x_assignment_request_id out nocopy number
136 , x_status        out nocopy VARCHAR2
137 , x_exception_msg out nocopy VARCHAR2
138 )
139 IS
140 
141 l_step number;
142 
143 BEGIN
144 
145   l_step := 0;
146    select POS_ACNT_ADDR_REQ_S.nextval into x_assignment_request_id from dual;
147 
148   l_step := 1;
149   insert into POS_ACNT_ADDR_REQ (
150      assignment_request_id
151    , creation_date
152    , created_by
153    , last_update_date
154    , last_updated_by
155    , last_update_login
156    , object_version_number
157    , MAPPING_ID
158    , request_status
159    , request_type
160    , party_site_id
161    , address_request_id
162   )
163   values
164   (
165     x_assignment_request_id
166   , sysdate -- creation_date
167   , fnd_global.user_id -- created_by
168   , sysdate -- last_update_date
169   , fnd_global.user_id -- last_updated_by
170   , fnd_global.login_id -- last_update_login
171   , 1
172   , p_mapping_id
173   , 'PENDING'
174   , p_request_type
175   , p_party_site_id
176   , p_address_request_id
177   );
178 
179   l_step := 2;
180 
181   x_status := 'S';
182 
183 EXCEPTION
184     WHEN OTHERS THEN
185       X_STATUS  :='E';
186       x_exception_msg := 'Failure at step ' || l_step || Sqlerrm;
187       raise_application_error(-20048, x_exception_msg, true);
188 END insert_row_pos_acnt_addr_req;
189 
190 
191 /* This procedure create a row in POS_ACNT_GEN_REQ
192  *
193  */
194 PROCEDURE insert_row_pos_acnt_gen_req (
195   p_mapping_id	   IN NUMBER
196 , p_temp_ext_bank_account_id IN NUMBER
197 , p_ext_bank_account_id IN NUMBER
198 , x_account_request_id out nocopy number
199 , x_status        out nocopy VARCHAR2
200 , x_exception_msg out nocopy VARCHAR2
201 )
202 IS
203 
204 l_step number;
205 
206 BEGIN
207 
208   l_step := 0;
209   select POS_ACNT_GEN_REQ_S.nextval into x_account_request_id from dual;
210 
211   l_step := 1;
212   insert into POS_ACNT_GEN_REQ (
213      account_request_id
214    , creation_date
215    , created_by
216    , last_update_date
217    , last_updated_by
218    , last_update_login
219    , object_version_number
220    , MAPPING_ID
221    , TEMP_EXT_BANK_ACCT_ID
222    , EXT_BANK_ACCOUNT_ID
223   )
224   values
225   (
226    x_account_request_id
227   , sysdate -- creation_date
228   , fnd_global.user_id -- created_by
229   , sysdate -- last_update_date
230   , fnd_global.user_id -- last_updated_by
231   , fnd_global.login_id -- last_update_login
232   , 1
233   , p_mapping_id
234   , p_temp_ext_bank_account_id
235   , p_ext_bank_account_id
236   );
237 
238   l_step := 2;
239 
240   x_status := 'S';
241 
242 EXCEPTION
243     WHEN OTHERS THEN
244       X_STATUS  :='E';
245       x_exception_msg := 'Failure at step ' || l_step || Sqlerrm;
246       raise_application_error(-20047, x_exception_msg, true);
247 END insert_row_pos_acnt_gen_req;
248 
249 PROCEDURE insert_row_pos_acnt_summ_req (
250   p_assignment_request_id in number
251 , p_ext_bank_account_id IN NUMBER
252 , p_account_request_id in number
253 , p_start_date in date
254 , p_end_date in date
255 , p_priority in number
256 , p_assignment_status in varchar2
257 , x_assignment_id out nocopy number
258 , x_status        out nocopy VARCHAR2
259 , x_exception_msg out nocopy VARCHAR2
260 )
261 IS
262 
263 l_step number;
264 
265 BEGIN
266 
267   l_step := 0;
268   select POS_ACNT_ADDR_SUMM_REQ_S.nextval into x_assignment_id from dual;
269 
270   l_step := 1;
271   insert into POS_ACNT_ADDR_SUMM_REQ (
272      assignment_id
273    , creation_date
274    , created_by
275    , last_update_date
276    , last_updated_by
277    , last_update_login
278    , assignment_request_id
279    , ext_bank_account_id
280    , start_date
281    , end_date
282    , priority
283    , assignment_status
284    , account_request_id
285    , object_version_number
286   )
287   values
288   (
289     x_assignment_id
290   , sysdate -- creation_date
291   , fnd_global.user_id -- created_by
292   , sysdate -- last_update_date
293   , fnd_global.user_id -- last_updated_by
294   , fnd_global.login_id -- last_update_login
295   , p_assignment_request_id
296   , p_ext_bank_account_id
297   , p_start_date
298   , p_end_date
299   , p_priority
300   , p_assignment_status
301   , p_account_request_id
302   , 1
303   );
304   l_step := 2;
305 
306   x_status := 'S';
307 
308 EXCEPTION
309     WHEN OTHERS THEN
310       X_STATUS  :='E';
311       x_exception_msg := 'Failure at step ' || l_step || Sqlerrm;
312       raise_application_error(-20049, x_exception_msg, true);
313 END insert_row_pos_acnt_summ_req;
314 
315 
316 /* This procedure updates a row in POS_ACNT_ADDR_REQ
317  *
318  */
319 PROCEDURE update_row_pos_acnt_addr_req (
320   p_assignment_request_id  IN NUMBER
321 , p_request_status in varchar2
322 , p_party_site_id  in number
323 , p_address_request_id in number
324 , p_object_version_number in number
325 , x_status        out nocopy VARCHAR2
326 , x_exception_msg out nocopy VARCHAR2
327 )
328 IS
329 l_step number;
330 l_object_version_number number;
331 
332 cursor l_update_cur is
333 select object_version_number from POS_ACNT_ADDR_REQ
334 where assignment_request_id = p_assignment_request_id for update;
335 
336 BEGIN
337 
338 l_step := 0;
339 
340 open l_update_cur;
341 fetch l_update_cur into l_object_version_number;
342 close l_update_cur;
343 
344 l_step := 1;
345 
346 if l_object_version_number = p_object_version_number then
347 
348   l_step := 2;
349   update POS_ACNT_ADDR_REQ set
350    last_update_date = sysdate
351    , last_updated_by = fnd_global.user_id
352    , last_update_login = fnd_global.login_id
353    , request_status = nvl(p_request_status, request_status)
354    , party_site_id = p_party_site_id
355    , address_request_id = p_address_request_id
356   where assignment_request_id = p_assignment_request_id;
357 
358 else
359 
360  l_step := 3;
361  raise_application_error(-20049, 'Concurrent Access', true);
362 
363 end if;
364 
365 l_step := 4;
366 
367 x_status := 'S';
368 
369 EXCEPTION
370     WHEN OTHERS THEN
371       X_STATUS  :='E';
372       x_exception_msg := 'Failure at step ' || l_step || Sqlerrm;
373       raise_application_error(-20049, x_exception_msg, true);
374 END update_row_pos_acnt_addr_req;
375 
376 END POS_SBD_TBL_PKG;