[Home] [Help]
PACKAGE BODY: APPS.AMS_APPROVAL_RULES_PKG
Source
1 package body AMS_APPROVAL_RULES_PKG as
2 /* $Header: amslappb.pls 120.1 2005/06/27 05:37:55 appldev ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in OUT NOCOPY VARCHAR2,
6 X_APPROVAL_RULE_ID in NUMBER,
7 X_OBJECT_VERSION_NUMBER in NUMBER,
8 X_ARC_APPROVAL_FOR_OBJECT in VARCHAR2,
9 X_APPROVAL_TYPE in VARCHAR2,
10 X_TIMEOUT_DAYS_LOW_PRIO in NUMBER,
11 X_TIMEOUT_DAYS_STD_PRIO in NUMBER,
12 X_TIMEOUT_DAYS_HIGH_PRIO in NUMBER,
13 X_TIMEOUT_DAYS_MEDIUM_PRIO in NUMBER,
14 X_MGR_APPROVAL_NEEDED_FLAG in VARCHAR2,
15 X_PARENT_OWNER_APPROVAL_FLAG in VARCHAR2,
16 X_ACTIVITY_TYPE_CODE in VARCHAR2,
17 X_CREATION_DATE in DATE,
18 X_CREATED_BY in NUMBER,
19 X_LAST_UPDATE_DATE in DATE,
20 X_LAST_UPDATED_BY in NUMBER,
21 X_LAST_UPDATE_LOGIN in NUMBER
22 ) is
23 cursor C is select ROWID from AMS_APPROVAL_RULES
24 where APPROVAL_RULE_ID = X_APPROVAL_RULE_ID ;
25 begin
26 insert into AMS_APPROVAL_RULES (
27 APPROVAL_RULE_ID,
28 LAST_UPDATE_DATE,
29 LAST_UPDATED_BY,
30 CREATION_DATE,
31 CREATED_BY,
32 LAST_UPDATE_LOGIN,
33 OBJECT_VERSION_NUMBER,
34 ARC_APPROVAL_FOR_OBJECT,
35 APPROVAL_TYPE,
36 TIMEOUT_DAYS_LOW_PRIO,
37 TIMEOUT_DAYS_STD_PRIO,
38 TIMEOUT_DAYS_HIGH_PRIO,
39 TIMEOUT_DAYS_MEDIUM_PRIO,
40 MGR_APPROVAL_NEEDED_FLAG,
41 PARENT_OWNER_APPROVAL_FLAG,
42 ACTIVITY_TYPE_CODE
43 ) VALUES(
44 X_APPROVAL_RULE_ID,
45 X_LAST_UPDATE_DATE,
46 X_LAST_UPDATED_BY,
47 X_CREATION_DATE,
48 X_CREATED_BY,
49 X_LAST_UPDATE_LOGIN,
50 X_OBJECT_VERSION_NUMBER,
51 X_ARC_APPROVAL_FOR_OBJECT,
52 X_APPROVAL_TYPE,
53 X_TIMEOUT_DAYS_LOW_PRIO,
54 X_TIMEOUT_DAYS_STD_PRIO,
55 X_TIMEOUT_DAYS_HIGH_PRIO,
56 X_TIMEOUT_DAYS_MEDIUM_PRIO,
57 X_MGR_APPROVAL_NEEDED_FLAG,
58 X_PARENT_OWNER_APPROVAL_FLAG,
59 X_ACTIVITY_TYPE_CODE);
60
61 open c;
62 fetch c into X_ROWID;
63 if (c%notfound) then
64 close c;
65 raise no_data_found;
66 end if;
67 close c;
68
69 end INSERT_ROW;
70
71 procedure UPDATE_ROW (
72 X_APPROVAL_RULE_ID in NUMBER,
73 X_OBJECT_VERSION_NUMBER in NUMBER,
74 X_ARC_APPROVAL_FOR_OBJECT in VARCHAR2,
75 X_APPROVAL_TYPE in VARCHAR2,
76 X_TIMEOUT_DAYS_LOW_PRIO in NUMBER,
77 X_TIMEOUT_DAYS_STD_PRIO in NUMBER,
78 X_TIMEOUT_DAYS_HIGH_PRIO in NUMBER,
79 X_TIMEOUT_DAYS_MEDIUM_PRIO in NUMBER,
80 X_MGR_APPROVAL_NEEDED_FLAG in VARCHAR2,
81 X_PARENT_OWNER_APPROVAL_FLAG in VARCHAR2,
82 X_ACTIVITY_TYPE_CODE in VARCHAR2,
83 X_LAST_UPDATE_DATE in DATE,
84 X_LAST_UPDATED_BY in NUMBER,
85 X_LAST_UPDATE_LOGIN in NUMBER
86 ) is
87 begin
88 update AMS_APPROVAL_RULES set
89 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
90 ARC_APPROVAL_FOR_OBJECT = X_ARC_APPROVAL_FOR_OBJECT,
91 APPROVAL_TYPE = X_APPROVAL_TYPE,
92 TIMEOUT_DAYS_LOW_PRIO = X_TIMEOUT_DAYS_LOW_PRIO,
93 TIMEOUT_DAYS_STD_PRIO = X_TIMEOUT_DAYS_STD_PRIO,
94 TIMEOUT_DAYS_HIGH_PRIO = X_TIMEOUT_DAYS_HIGH_PRIO,
95 TIMEOUT_DAYS_MEDIUM_PRIO = X_TIMEOUT_DAYS_MEDIUM_PRIO,
96 MGR_APPROVAL_NEEDED_FLAG = X_MGR_APPROVAL_NEEDED_FLAG,
97 PARENT_OWNER_APPROVAL_FLAG = X_PARENT_OWNER_APPROVAL_FLAG,
98 ACTIVITY_TYPE_CODE = X_ACTIVITY_TYPE_CODE,
99 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
100 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
101 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
102 where APPROVAL_RULE_ID = X_APPROVAL_RULE_ID;
103
104 if (sql%notfound) then
105 raise no_data_found;
106 end if;
107 end UPDATE_ROW;
108
109 procedure DELETE_ROW (
110 X_APPROVAL_RULE_ID in NUMBER
111 ) is
112 begin
113 delete from AMS_APPROVAL_RULES
114 where APPROVAL_RULE_ID = X_APPROVAL_RULE_ID;
115
116 if (sql%notfound) then
117 raise no_data_found;
118 end if;
119
120 end DELETE_ROW;
121
122 procedure LOAD_ROW(
123 X_APPROVAL_RULE_ID in NUMBER,
124 X_OBJECT_VERSION_NUMBER in NUMBER,
125 X_ARC_APPROVAL_FOR_OBJECT in VARCHAR2,
126 X_APPROVAL_TYPE in VARCHAR2,
127 X_TIMEOUT_DAYS_LOW_PRIO in NUMBER,
128 X_TIMEOUT_DAYS_STD_PRIO in NUMBER,
129 X_TIMEOUT_DAYS_HIGH_PRIO in NUMBER,
130 X_TIMEOUT_DAYS_MEDIUM_PRIO in NUMBER,
131 X_MGR_APPROVAL_NEEDED_FLAG in VARCHAR2,
132 X_PARENT_OWNER_APPROVAL_FLAG in VARCHAR2,
133 X_ACTIVITY_TYPE_CODE in VARCHAR2,
134 X_CREATION_DATE in DATE,
135 X_CREATED_BY in NUMBER,
136 X_LAST_UPDATE_DATE in DATE,
137 X_LAST_UPDATED_BY in NUMBER,
138 X_LAST_UPDATE_LOGIN in NUMBER,
139 X_Owner VARCHAR2
140 ) is
141
142 l_user_id number := 0;
143 l_obj_verno number;
144 l_dummy_char varchar2(1);
145 l_row_id varchar2(100);
146 l_approval_rule_id number;
147
148 cursor c_obj_verno is
149 select object_version_number
150 from AMS_APPROVAL_RULES
151 where APPROVAL_RULE_ID = X_APPROVAL_RULE_ID;
152
153 cursor c_chk_app_exists is
154 select 'x'
155 from AMS_APPROVAL_RULES
156 where APPROVAL_RULE_ID = X_APPROVAL_RULE_ID;
157
158 cursor c_get_appruleid is
159 select AMS_APPROVAL_RULES_S.nextval
160 from dual;
161
162 BEGIN
163
164 if X_OWNER = 'SEED' then
165 l_user_id := 1;
166 end if;
167
168 open c_chk_app_exists;
169 fetch c_chk_app_exists into l_dummy_char;
170 if c_chk_app_exists%notfound
171 then
172 if X_APPROVAL_RULE_ID is null
173 then
174 open c_get_appruleid;
175 fetch c_get_appruleid into l_APPROVAL_RULE_ID;
176 close c_get_appruleid;
177 else
178 l_APPROVAL_RULE_ID := X_APPROVAL_RULE_ID;
179 end if;
180 l_obj_verno := 1;
181
182 AMS_APPROVAL_RULES_PKG.INSERT_ROW(
183 X_ROWID => l_row_id,
184 X_APPROVAL_RULE_ID => l_APPROVAL_RULE_ID ,
185 X_OBJECT_VERSION_NUMBER => l_obj_verno,
186 X_ARC_APPROVAL_FOR_OBJECT => X_ARC_APPROVAL_FOR_OBJECT ,
187 X_APPROVAL_TYPE => X_APPROVAL_TYPE,
188 X_TIMEOUT_DAYS_LOW_PRIO => X_TIMEOUT_DAYS_LOW_PRIO,
189 X_TIMEOUT_DAYS_STD_PRIO => X_TIMEOUT_DAYS_STD_PRIO ,
190 X_TIMEOUT_DAYS_HIGH_PRIO => X_TIMEOUT_DAYS_HIGH_PRIO,
191 X_TIMEOUT_DAYS_MEDIUM_PRIO => X_TIMEOUT_DAYS_MEDIUM_PRIO,
192 X_MGR_APPROVAL_NEEDED_FLAG => X_MGR_APPROVAL_NEEDED_FLAG,
193 X_PARENT_OWNER_APPROVAL_FLAG => X_PARENT_OWNER_APPROVAL_FLAG ,
194 X_ACTIVITY_TYPE_CODE => X_ACTIVITY_TYPE_CODE,
195 X_CREATION_DATE => SYSDATE,
196 X_CREATED_BY => l_user_id,
197 X_LAST_UPDATE_DATE => SYSDATE,
198 X_LAST_UPDATED_BY => l_user_id,
199 X_LAST_UPDATE_LOGIN => 0
200 );
201 close c_chk_app_exists;
202 else
203 close c_chk_app_exists;
204 open c_obj_verno;
205 fetch c_obj_verno into l_obj_verno;
206 close c_obj_verno;
207 AMS_APPROVAL_RULES_PKG.UPDATE_ROW(
208 X_APPROVAL_RULE_ID => x_APPROVAL_RULE_ID ,
209 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
210 X_ARC_APPROVAL_FOR_OBJECT => X_ARC_APPROVAL_FOR_OBJECT ,
211 X_APPROVAL_TYPE => X_APPROVAL_TYPE,
212 X_TIMEOUT_DAYS_LOW_PRIO => X_TIMEOUT_DAYS_LOW_PRIO,
213 X_TIMEOUT_DAYS_STD_PRIO => X_TIMEOUT_DAYS_STD_PRIO ,
214 X_TIMEOUT_DAYS_HIGH_PRIO => X_TIMEOUT_DAYS_HIGH_PRIO,
215 X_TIMEOUT_DAYS_MEDIUM_PRIO => X_TIMEOUT_DAYS_MEDIUM_PRIO,
216 X_MGR_APPROVAL_NEEDED_FLAG => X_MGR_APPROVAL_NEEDED_FLAG,
217 X_PARENT_OWNER_APPROVAL_FLAG => X_PARENT_OWNER_APPROVAL_FLAG ,
218 X_ACTIVITY_TYPE_CODE => X_ACTIVITY_TYPE_CODE,
219 X_LAST_UPDATE_DATE => SYSDATE,
220 X_LAST_UPDATED_BY => l_user_id,
221 X_LAST_UPDATE_LOGIN => 0
222 );
223 end if;
224 END LOAD_ROW;
225
226 end AMS_APPROVAL_RULES_PKG;