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