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