[Home] [Help]
PACKAGE BODY: APPS.PSP_ER_APPROVALS_PKG
Source
1 package body PSP_ER_APPROVALS_PKG as
2 /* $Header: PSPERAPB.pls 115.4 2002/11/18 12:24:42 lveerubh ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_EFFORT_REPORT_ID in NUMBER,
6 X_VERSION_NUM in NUMBER,
7 X_APPROVING_PERSON_ID in NUMBER,
8 X_APPROVED_DATE in DATE,
9 X_MODE in VARCHAR2 default 'R'
10 ) is
11 cursor C is select ROWID from PSP_ER_APPROVALS
12 where EFFORT_REPORT_ID = X_EFFORT_REPORT_ID
13 and VERSION_NUM = X_VERSION_NUM
14 and APPROVING_PERSON_ID = X_APPROVING_PERSON_ID;
15 X_LAST_UPDATE_DATE DATE;
16 X_LAST_UPDATED_BY NUMBER;
17 X_LAST_UPDATE_LOGIN NUMBER;
18 begin
19 X_LAST_UPDATE_DATE := SYSDATE;
20 if(X_MODE = 'I') then
21 X_LAST_UPDATED_BY := 1;
22 X_LAST_UPDATE_LOGIN := 0;
23 elsif (X_MODE = 'R') then
24 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
25 if X_LAST_UPDATED_BY is NULL then
26 X_LAST_UPDATED_BY := -1;
27 end if;
28 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
29 if X_LAST_UPDATE_LOGIN is NULL then
30 X_LAST_UPDATE_LOGIN := -1;
31 end if;
32 else
33 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
34 app_exception.raise_exception;
35 end if;
36 insert into PSP_ER_APPROVALS (
37 EFFORT_REPORT_ID,
38 VERSION_NUM,
39 APPROVING_PERSON_ID,
40 APPROVED_DATE,
41 CREATION_DATE,
42 CREATED_BY,
43 LAST_UPDATE_DATE,
44 LAST_UPDATED_BY,
45 LAST_UPDATE_LOGIN
46 ) values (
47 X_EFFORT_REPORT_ID,
48 X_VERSION_NUM,
49 X_APPROVING_PERSON_ID,
50 X_APPROVED_DATE,
51 X_LAST_UPDATE_DATE,
52 X_LAST_UPDATED_BY,
53 X_LAST_UPDATE_DATE,
54 X_LAST_UPDATED_BY,
55 X_LAST_UPDATE_LOGIN
56 );
57
58 open c;
59 fetch c into X_ROWID;
60 if (c%notfound) then
61 close c;
62 raise no_data_found;
63 end if;
64 close c;
65 commit;
66
67 end INSERT_ROW;
68
69 procedure LOCK_ROW (
70 X_EFFORT_REPORT_ID in NUMBER,
71 X_VERSION_NUM in NUMBER,
72 X_APPROVING_PERSON_ID in NUMBER,
73 X_APPROVED_DATE in DATE
74 ) is
75 cursor c1 is select
76 APPROVED_DATE
77 from PSP_ER_APPROVALS
78 where EFFORT_REPORT_ID = X_EFFORT_REPORT_ID
79 and VERSION_NUM = X_VERSION_NUM
80 and APPROVING_PERSON_ID = X_APPROVING_PERSON_ID
81 for update of EFFORT_REPORT_ID nowait;
82 tlinfo c1%rowtype;
83
84 begin
85 open c1;
86 fetch c1 into tlinfo;
87 if (c1%notfound) then
88 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
89 app_exception.raise_exception;
90 close c1;
91 return;
92 end if;
93 close c1;
94
95 if ( (tlinfo.APPROVED_DATE = X_APPROVED_DATE)
96 ) then
97 null;
98 else
99 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
100 app_exception.raise_exception;
101 end if;
102 return;
103 end LOCK_ROW;
104
105 procedure UPDATE_ROW (
106 X_EFFORT_REPORT_ID in NUMBER,
107 X_VERSION_NUM in NUMBER,
108 X_APPROVING_PERSON_ID in NUMBER,
109 X_APPROVED_DATE in DATE,
110 X_MODE in VARCHAR2 default 'R'
111 ) is
112 X_LAST_UPDATE_DATE DATE;
113 X_LAST_UPDATED_BY NUMBER;
114 X_LAST_UPDATE_LOGIN NUMBER;
115 begin
116 X_LAST_UPDATE_DATE := SYSDATE;
117 if(X_MODE = 'I') then
118 X_LAST_UPDATED_BY := 1;
119 X_LAST_UPDATE_LOGIN := 0;
120 elsif (X_MODE = 'R') then
121 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
122 if X_LAST_UPDATED_BY is NULL then
123 X_LAST_UPDATED_BY := -1;
124 end if;
125 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
126 if X_LAST_UPDATE_LOGIN is NULL then
127 X_LAST_UPDATE_LOGIN := -1;
128 end if;
129 else
130 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
131 app_exception.raise_exception;
132 end if;
133 update PSP_ER_APPROVALS set
134 APPROVED_DATE = X_APPROVED_DATE,
135 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
136 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
137 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
138 where EFFORT_REPORT_ID = X_EFFORT_REPORT_ID
139 and VERSION_NUM = X_VERSION_NUM
140 and APPROVING_PERSON_ID = X_APPROVING_PERSON_ID
141 ;
142 if (sql%notfound) then
143 raise no_data_found;
144 end if;
145 end UPDATE_ROW;
146
147 procedure ADD_ROW (
148 X_ROWID in out NOCOPY VARCHAR2,
149 X_EFFORT_REPORT_ID in NUMBER,
150 X_VERSION_NUM in NUMBER,
151 X_APPROVING_PERSON_ID in NUMBER,
152 X_APPROVED_DATE in DATE,
153 X_MODE in VARCHAR2 default 'R'
154 ) is
155 cursor c1 is select rowid from PSP_ER_APPROVALS
156 where EFFORT_REPORT_ID = X_EFFORT_REPORT_ID
157 and VERSION_NUM = X_VERSION_NUM
158 and APPROVING_PERSON_ID = X_APPROVING_PERSON_ID
159 ;
160 dummy c1%rowtype;
161 begin
162 open c1;
163 fetch c1 into dummy;
164 if (c1%notfound) then
165 close c1;
166 INSERT_ROW (
167 X_ROWID,
168 X_EFFORT_REPORT_ID,
169 X_VERSION_NUM,
170 X_APPROVING_PERSON_ID,
171 X_APPROVED_DATE,
172 X_MODE);
173 return;
174 end if;
175 close c1;
176 UPDATE_ROW (
177 X_EFFORT_REPORT_ID,
178 X_VERSION_NUM,
179 X_APPROVING_PERSON_ID,
180 X_APPROVED_DATE,
181 X_MODE);
182 end ADD_ROW;
183
184 procedure DELETE_ROW (
185 X_EFFORT_REPORT_ID in NUMBER,
186 X_VERSION_NUM in NUMBER,
187 X_APPROVING_PERSON_ID in NUMBER
188 ) is
189 begin
190 delete from PSP_ER_APPROVALS
191 where EFFORT_REPORT_ID = X_EFFORT_REPORT_ID
192 and VERSION_NUM = X_VERSION_NUM
193 and APPROVING_PERSON_ID = X_APPROVING_PERSON_ID;
194 ---if (sql%notfound) then
195 ---raise no_data_found;
196 --- end if;
197 commit;
198 end DELETE_ROW;
199
200 end PSP_ER_APPROVALS_PKG;