1 PACKAGE BODY psp_auto_na_bypass_pkg AS
2 /* $Header: PSPAUTNB.pls 115.5 2002/11/19 11:05:37 ddubey ship $ */
3 PROCEDURE insert_row
4 (p_rowid IN OUT NOCOPY VARCHAR2,
5 p_natural_account IN VARCHAR2,
6 p_segment_num IN NUMBER,
7 p_business_group_id IN NUMBER,
8 p_set_of_books_id IN NUMBER,
9 p_mode IN VARCHAR2 DEFAULT 'R') IS
10 CURSOR row_id_cur IS
11 SELECT rowid
12 FROM psp_auto_na_bypass
13 WHERE natural_account = p_natural_account
14 AND segment_num = p_segment_num;
15
16 l_last_update_date DATE;
17 l_last_updated_by NUMBER;
18 l_last_update_login NUMBER;
19
20 BEGIN
21 l_last_update_date := SYSDATE;
22 IF (p_mode = 'I') THEN
23 l_last_updated_by := 1;
24 l_last_update_login := 0;
25 ELSIF (p_mode = 'R') THEN
26 l_last_updated_by := fnd_global.user_id;
27 IF l_last_updated_by IS NULL THEN
28 l_last_updated_by := -1;
29 END IF;
30 l_last_update_login :=fnd_global.login_id;
31 IF l_last_update_login IS null THEN
32 l_last_update_login := -1;
33 END IF;
34 ELSE
35 fnd_message.set_name('FND', 'SYSTEM-INVALID ARGS');
36 app_exception.raise_exception;
37 END IF;
38 INSERT INTO psp_auto_na_bypass
39 (natural_account, segment_num, business_group_id, set_of_books_id,
40 creation_date, created_by, last_update_date, last_updated_by,
41 last_update_login)
42 VALUES (p_natural_account, p_segment_num, p_business_group_id, p_set_of_books_id,
43 l_last_update_date, l_last_updated_by, l_last_update_date, l_last_updated_by,
44 l_last_update_login);
45
46 OPEN row_id_cur;
47 FETCH row_id_cur INTO p_rowid;
48 IF (row_id_cur%NOTFOUND) THEN
49 CLOSE row_id_cur;
50 RAISE NO_DATA_FOUND;
51 END IF;
52 CLOSE row_id_cur;
53
54 END insert_row;
55
56 PROCEDURE lock_row
57 (p_rowid IN OUT NOCOPY VARCHAR2,
58 p_natural_account IN VARCHAR2) IS
59 CURSOR natural_account_lock_cur IS
60 SELECT *
61 FROM psp_auto_na_bypass
62 WHERE rowid = p_rowid
63 FOR UPDATE OF natural_account NOWAIT;
64
65 -- Becuase the primary key may be updated, so we lock the row
66 -- based on rowid instead of these 2 primary keys.
67 -- where NATURAL_ACCOUNT = P_NATURAL_ACCOUNT
68 -- and SEGMENT_NUM = P_SEGMENT_NUM
69
70 l_natural_account_lock_info natural_account_lock_cur%ROWTYPE;
71
72 BEGIN
73 OPEN natural_account_lock_cur;
74 FETCH natural_account_lock_cur INTO l_natural_account_lock_info;
75 IF (natural_account_lock_cur%NOTFOUND) THEn
76 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
77 app_exception.raise_exception;
78 CLOSE natural_account_lock_cur;
79 RETURN;
80 END IF;
81 CLOSE natural_account_lock_cur;
82
83 IF (l_natural_account_lock_info.natural_account = p_natural_account) THEN
84 RETURN;
85 ELSE
86 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
87 app_exception.raise_exception;
88 END IF;
89 RETURN;
90 END lock_row;
91
92 PROCEDURE update_row
93 (p_rowid IN OUT NOCOPY VARCHAR2,
94 p_natural_account IN VARCHAR2,
95 p_segment_num IN NUMBER,
96 p_mode IN VARCHAR2 DEFAULT 'R') IS
97 l_last_update_date DATE;
98 l_last_updated_by NUMBER;
99 l_last_update_login NUMBER;
100
101 BEGIN
102 l_last_update_date := SYSDATE;
103 IF (p_mode = 'I') THEN
104 l_last_updated_by := 1;
105 l_last_update_login := 0;
106 ELSIF (p_mode = 'R') THEN
107 l_last_updated_by := fnd_global.user_id;
108 IF (l_last_updated_by IS NULL) THEN
109 l_last_updated_by := -1;
110 END IF;
111 l_last_update_login :=fnd_global.login_id;
112 IF (l_last_update_login IS NULL) THEN
113 l_last_update_login := -1;
114 END IF;
115 ELSE
116 fnd_message.set_name('FND', 'SYSTEM-INVALID ARGS');
117 app_exception.raise_exception;
118 END IF;
119
120 UPDATE psp_auto_na_bypass
121 SET natural_account = p_natural_account,
122 segment_num = p_segment_num,
123 last_update_date = l_last_update_date,
124 last_updated_by = l_last_updated_by,
125 last_update_login = l_last_update_login
126 WHERE rowid = p_rowid;
127 -- Becuase the primary key may be updated, so we update the row
128 -- based on rowid instead of these 2 primary keys.
129 -- where NATURAL_ACCOUNT = P_NATURAL_ACCOUNT
130 -- and SEGMENT_NUM = P_SEGMENT_NUM
131
132 IF (SQL%NOTFOUND) THEN
133 RAISE NO_DATA_FOUND;
134 END IF;
135 END update_row;
136
137
138 PROCEDURE delete_row
139 (p_rowid IN OUT NOCOPY VARCHAR2) IS
140 BEGIN
141 DELETE FROM psp_auto_na_bypass
142 WHERE rowid = p_rowid;
143
144 -- Becuase the primary key may be updated, so we lock the row
145 -- based on rowid instead of these 2 primary keys.
146 -- where NATURAL_ACCOUNT = P_NATURAL_ACCOUNT
147 -- and SEGMENT_NUM = P_SEGMENT_NUM;
148
149 IF (SQL%NOTFOUND) THEN
150 RAISE NO_DATA_FOUND;
151 END IF;
152 END delete_row;
153
154 END psp_auto_na_bypass_pkg;
[Home] [Help]