1 package body PA_CC_TP_SCHEDULE_LINES_PKG AS
2 /* $Header: PAXTPSLB.pls 120.2 2005/08/05 02:15:39 rgandhi ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY varchar2,/*File.sql.39*/
5 X_SCHEDULE_LINE_ID in NUMBER,
6 X_TP_SCHEDULE_ID in NUMBER,
7 X_PRVDR_ORGANIZATION_ID in NUMBER,
8 X_START_DATE_ACTIVE in DATE,
9 X_DEFAULT_FLAG in varchar2,
10 X_TP_AMT_TYPE_CODE in VARCHAR2,
11 X_LAST_UPDATE_DATE in DATE,
12 X_LAST_UPDATED_BY in NUMBER,
13 X_CREATION_DATE in DATE,
14 X_CREATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER,
16 X_SORT_ORDER in NUMBER,
17 X_LABOR_TP_RULE_ID in NUMBER,
18 X_LABOR_PERCENTAGE_APPLIED in NUMBER,
19 X_NL_TP_RULE_ID in NUMBER,
20 X_NL_PERCENTAGE_APPLIED in NUMBER,
21 X_RECVR_ORGANIZATION_ID in NUMBER,
22 X_END_DATE_ACTIVE in DATE,
23 X_ATTRIBUTE_CATEGORY in VARCHAR2,
24 X_ATTRIBUTE1 in VARCHAR2,
25 X_ATTRIBUTE2 in VARCHAR2,
26 X_ATTRIBUTE3 in VARCHAR2,
27 X_ATTRIBUTE4 in VARCHAR2,
28 X_ATTRIBUTE5 in VARCHAR2,
29 X_ATTRIBUTE6 in VARCHAR2,
30 X_ATTRIBUTE7 in VARCHAR2,
31 X_ATTRIBUTE8 in VARCHAR2,
32 X_ATTRIBUTE9 in VARCHAR2,
33 X_ATTRIBUTE10 in VARCHAR2,
34 X_ATTRIBUTE11 in VARCHAR2,
35 X_ATTRIBUTE12 in VARCHAR2,
36 X_ATTRIBUTE13 in VARCHAR2,
37 X_ATTRIBUTE14 in VARCHAR2,
38 X_ATTRIBUTE15 in VARCHAR2
39 ) is
40 cursor C is select ROWID from PA_CC_TP_SCHEDULE_LINES
41 where TP_SCHEDULE_ID = X_TP_SCHEDULE_ID
42 and TP_SCHEDULE_LINE_ID=X_SCHEDULE_LINE_ID;
43 begin
44 insert into PA_CC_TP_SCHEDULE_LINES (
45 TP_SCHEDULE_LINE_ID,
46 TP_SCHEDULE_ID,
47 PRVDR_ORGANIZATION_ID,
48 START_DATE_ACTIVE,
49 DEFAULT_FLAG,
50 TP_AMT_TYPE_CODE,
51 LAST_UPDATE_DATE,
52 LAST_UPDATED_BY,
53 CREATION_DATE,
54 CREATED_BY,
55 LAST_UPDATE_LOGIN,
56 SORT_ORDER,
57 LABOR_TP_RULE_ID,
58 LABOR_PERCENTAGE_APPLIED,
59 NL_TP_RULE_ID,
60 NL_PERCENTAGE_APPLIED,
61 RECVR_ORGANIZATION_ID,
62 END_DATE_ACTIVE,
63 ATTRIBUTE_CATEGORY,
64 ATTRIBUTE1,
65 ATTRIBUTE2,
66 ATTRIBUTE3,
67 ATTRIBUTE4,
68 ATTRIBUTE5,
69 ATTRIBUTE6,
70 ATTRIBUTE7,
71 ATTRIBUTE8,
72 ATTRIBUTE9,
73 ATTRIBUTE10,
74 ATTRIBUTE11,
75 ATTRIBUTE12,
76 ATTRIBUTE13,
77 ATTRIBUTE14,
78 ATTRIBUTE15
79 ) values (
80 X_SCHEDULE_LINE_ID,
81 X_TP_SCHEDULE_ID,
82 X_PRVDR_ORGANIZATION_ID,
83 X_START_DATE_ACTIVE,
84 X_DEFAULT_FLAG,
85 X_TP_AMT_TYPE_CODE,
86 X_LAST_UPDATE_DATE,
87 X_LAST_UPDATED_BY,
88 X_CREATION_DATE,
89 X_CREATED_BY,
90 X_LAST_UPDATE_LOGIN,
91 X_SORT_ORDER,
92 X_LABOR_TP_RULE_ID,
93 X_LABOR_PERCENTAGE_APPLIED,
94 X_NL_TP_RULE_ID,
95 X_NL_PERCENTAGE_APPLIED,
96 X_RECVR_ORGANIZATION_ID,
97 X_END_DATE_ACTIVE,
98 X_ATTRIBUTE_CATEGORY,
99 X_ATTRIBUTE1,
100 X_ATTRIBUTE2,
101 X_ATTRIBUTE3,
102 X_ATTRIBUTE4,
103 X_ATTRIBUTE5,
104 X_ATTRIBUTE6,
105 X_ATTRIBUTE7,
106 X_ATTRIBUTE8,
107 X_ATTRIBUTE9,
108 X_ATTRIBUTE10,
109 X_ATTRIBUTE11,
110 X_ATTRIBUTE12,
111 X_ATTRIBUTE13,
112 X_ATTRIBUTE14,
113 X_ATTRIBUTE15
114 );
115 open c;
116 fetch c into X_ROWID;
117 if (c%notfound) then
118 close c;
119 raise no_data_found;
120 end if;
121 close c;
122 end INSERT_ROW;
123
124 procedure LOCK_ROW (
125 X_SCHEDULE_LINE_ID in NUMBER,
126 X_TP_SCHEDULE_ID in NUMBER,
127 X_PRVDR_ORGANIZATION_ID in NUMBER,
128 X_START_DATE_ACTIVE in DATE,
129 X_DEFAULT_FLAG in varchar2,
130 X_TP_AMT_TYPE_CODE in VARCHAR2,
131 X_LAST_UPDATE_DATE in DATE,
132 X_LAST_UPDATED_BY in NUMBER,
133 X_CREATION_DATE in DATE,
134 X_CREATED_BY in NUMBER,
135 X_LAST_UPDATE_LOGIN in NUMBER,
136 X_SORT_ORDER in NUMBER,
137 X_LABOR_TP_RULE_ID in NUMBER,
138 X_LABOR_PERCENTAGE_APPLIED in NUMBER,
139 X_NL_TP_RULE_ID in NUMBER,
140 X_NL_PERCENTAGE_APPLIED in NUMBER,
141 X_RECVR_ORGANIZATION_ID in NUMBER,
142 X_END_DATE_ACTIVE in DATE,
143 X_ATTRIBUTE_CATEGORY in VARCHAR2,
144 X_ATTRIBUTE1 in VARCHAR2,
145 X_ATTRIBUTE2 in VARCHAR2,
146 X_ATTRIBUTE3 in VARCHAR2,
147 X_ATTRIBUTE4 in VARCHAR2,
148 X_ATTRIBUTE5 in VARCHAR2,
149 X_ATTRIBUTE6 in VARCHAR2,
150 X_ATTRIBUTE7 in VARCHAR2,
151 X_ATTRIBUTE8 in VARCHAR2,
152 X_ATTRIBUTE9 in VARCHAR2,
153 X_ATTRIBUTE10 in VARCHAR2,
154 X_ATTRIBUTE11 in VARCHAR2,
155 X_ATTRIBUTE12 in VARCHAR2,
156 X_ATTRIBUTE13 in VARCHAR2,
157 X_ATTRIBUTE14 in VARCHAR2,
158 X_ATTRIBUTE15 in VARCHAR2
159 ) is
160 cursor c1 is select
161 TP_SCHEDULE_LINE_ID,
162 TP_SCHEDULE_ID,
163 PRVDR_ORGANIZATION_ID,
164 START_DATE_ACTIVE,
165 DEFAULT_FLAG,
166 TP_AMT_TYPE_CODE,
167 LAST_UPDATE_DATE,
168 LAST_UPDATED_BY,
169 CREATION_DATE,
170 CREATED_BY,
171 LAST_UPDATE_LOGIN,
172 SORT_ORDER,
173 LABOR_TP_RULE_ID,
174 LABOR_PERCENTAGE_APPLIED,
175 NL_TP_RULE_ID,
176 NL_PERCENTAGE_APPLIED,
177 RECVR_ORGANIZATION_ID,
178 END_DATE_ACTIVE,
179 ATTRIBUTE_CATEGORY,
180 ATTRIBUTE1,
181 ATTRIBUTE2,
182 ATTRIBUTE3,
183 ATTRIBUTE4,
184 ATTRIBUTE5,
185 ATTRIBUTE6,
186 ATTRIBUTE7,
187 ATTRIBUTE8,
188 ATTRIBUTE9,
189 ATTRIBUTE10,
190 ATTRIBUTE11,
191 ATTRIBUTE12,
192 ATTRIBUTE13,
193 ATTRIBUTE14,
194 ATTRIBUTE15
195 from PA_CC_TP_SCHEDULE_LINES
196 where TP_SCHEDULE_ID = X_TP_SCHEDULE_ID
197 and TP_SCHEDULE_LINE_ID=X_SCHEDULE_LINE_ID
198 for update of TP_SCHEDULE_ID nowait;
199 tlinfo c1%rowtype;
200
201 begin
202 open c1;
203 fetch c1 into tlinfo;
204 if (c1%notfound) then
205 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
206 app_exception.raise_exception;
207 close c1;
208 return;
209 end if;
210 close c1;
211
212 if(
213 ( tlinfo.TP_SCHEDULE_LINE_ID=X_SCHEDULE_LINE_ID)
214 AND( tlinfo.TP_SCHEDULE_ID=X_TP_SCHEDULE_ID)
215 AND ((tlinfo.PRVDR_ORGANIZATION_ID=X_PRVDR_ORGANIZATION_ID)
216 OR ((tlinfo.PRVDR_ORGANIZATION_ID IS NULL ) AND (X_PRVDR_ORGANIZATION_ID IS NULL)))
217 AND (tlinfo.START_DATE_ACTIVE=X_START_DATE_ACTIVE)
218 AND (tlinfo.DEFAULT_FLAG=X_DEFAULT_FLAG)
219 AND ((tlinfo.TP_AMT_TYPE_CODE=X_TP_AMT_TYPE_CODE)
220 OR ((tlinfo.TP_AMT_TYPE_CODE IS NULL ) AND (X_TP_AMT_TYPE_CODE IS NULL)))
221 AND ((tlinfo.SORT_ORDER=X_SORT_ORDER)
222 OR ((tlinfo.SORT_ORDER IS NULL ) AND (X_SORT_ORDER IS NULL)))
223 AND ((tlinfo.LABOR_TP_RULE_ID=X_LABOR_TP_RULE_ID)
224 OR ((tlinfo.LABOR_TP_RULE_ID is null ) AND (X_LABOR_TP_RULE_ID is null)))
225 AND ((tlinfo.LABOR_PERCENTAGE_APPLIED=X_LABOR_PERCENTAGE_APPLIED)
226 OR ((tlinfo.LABOR_PERCENTAGE_APPLIED IS NULL) AND (X_LABOR_PERCENTAGE_APPLIED IS NULL)))
227 AND ((tlinfo.NL_TP_RULE_ID=X_NL_TP_RULE_ID)
228 OR ((tlinfo.NL_TP_RULE_ID IS NULL) AND (X_NL_TP_RULE_ID IS NULL)))
229 AND ((tlinfo.NL_PERCENTAGE_APPLIED=X_NL_PERCENTAGE_APPLIED)
230 OR ((tlinfo.NL_PERCENTAGE_APPLIED IS NULL) AND (X_NL_PERCENTAGE_APPLIED IS NULL)))
231 AND ((tlinfo.RECVR_ORGANIZATION_ID=X_RECVR_ORGANIZATION_ID)
232 OR ((tlinfo.RECVR_ORGANIZATION_ID IS NULL) AND (X_RECVR_ORGANIZATION_ID IS null)))
233 AND ((tlinfo.END_DATE_ACTIVE=X_END_DATE_ACTIVE)
234 OR ((tlinfo.END_DATE_ACTIVE IS NULL) AND (X_END_DATE_ACTIVE is null)))
235 AND ((tlinfo.ATTRIBUTE_CATEGORY=X_ATTRIBUTE_CATEGORY)
236 OR ((tlinfo.ATTRIBUTE_CATEGORY IS NULL) AND (X_ATTRIBUTE_CATEGORY IS NULL)))
237 AND ((tlinfo.ATTRIBUTE1=X_ATTRIBUTE1)
238 OR ((tlinfo.ATTRIBUTE1 IS NULL) AND (X_ATTRIBUTE1 IS NULL )))
239 AND ((tlinfo.ATTRIBUTE2=X_ATTRIBUTE2)
240 OR ((tlinfo.ATTRIBUTE2 IS NULL) AND (X_ATTRIBUTE2 IS NULL )))
241 AND ((tlinfo.ATTRIBUTE3=X_ATTRIBUTE3)
242 OR ((tlinfo.ATTRIBUTE3 IS NULL) AND (X_ATTRIBUTE3 IS NULL )))
243 AND ((tlinfo.ATTRIBUTE4=X_ATTRIBUTE4)
244 OR ((tlinfo.ATTRIBUTE4 IS NULL) AND (X_ATTRIBUTE4 IS NULL )))
245 AND ((tlinfo.ATTRIBUTE5=X_ATTRIBUTE5)
246 OR ((tlinfo.ATTRIBUTE5 IS NULL) AND (X_ATTRIBUTE5 IS NULL )))
247 AND ((tlinfo.ATTRIBUTE6=X_ATTRIBUTE6)
248 OR ((tlinfo.ATTRIBUTE6 IS NULL) AND (X_ATTRIBUTE6 IS NULL )))
249 AND ((tlinfo.ATTRIBUTE7=X_ATTRIBUTE7)
250 OR ((tlinfo.ATTRIBUTE7 IS NULL) AND (X_ATTRIBUTE7 IS NULL )))
251 AND ((tlinfo.ATTRIBUTE8=X_ATTRIBUTE8)
252 OR ((tlinfo.ATTRIBUTE8 IS NULL) AND (X_ATTRIBUTE8 IS NULL )))
253 AND ((tlinfo.ATTRIBUTE9=X_ATTRIBUTE9)
254 OR ((tlinfo.ATTRIBUTE9 IS NULL) AND (X_ATTRIBUTE9 IS NULL )))
255 AND ((tlinfo.ATTRIBUTE10=X_ATTRIBUTE10)
256 OR ((tlinfo.ATTRIBUTE10 IS NULL) AND (X_ATTRIBUTE10 IS NULL )))
257 AND ((tlinfo.ATTRIBUTE11=X_ATTRIBUTE11)
258 OR ((tlinfo.ATTRIBUTE11 IS NULL) AND (X_ATTRIBUTE11 IS NULL )))
259 AND ((tlinfo.ATTRIBUTE12=X_ATTRIBUTE12)
260 OR ((tlinfo.ATTRIBUTE12 IS NULL) AND (X_ATTRIBUTE12 IS NULL )))
261 AND ((tlinfo.ATTRIBUTE13=X_ATTRIBUTE13)
262 OR ((tlinfo.ATTRIBUTE13 IS NULL) AND (X_ATTRIBUTE13 IS NULL )))
263 AND ((tlinfo.ATTRIBUTE14=X_ATTRIBUTE14)
264 OR ((tlinfo.ATTRIBUTE14 IS NULL) AND (X_ATTRIBUTE14 IS NULL )))
265 AND ((tlinfo.ATTRIBUTE15=X_ATTRIBUTE15)
266 OR ((tlinfo.ATTRIBUTE15 IS NULL) AND (X_ATTRIBUTE15 IS NULL )))
267
268 ) then
269 null;
270 else
271 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
272 app_exception.raise_exception;
273 end if;
274 return;
275 end LOCK_ROW;
276
277 procedure UPDATE_ROW (
278 X_ROWID in VARCHAR2,
279 X_SCHEDULE_LINE_ID in NUMBER,
280 X_TP_SCHEDULE_ID in NUMBER,
281 X_PRVDR_ORGANIZATION_ID in NUMBER,
282 X_START_DATE_ACTIVE in DATE,
283 X_DEFAULT_FLAG in varchar2,
284 X_TP_AMT_TYPE_CODE in VARCHAR2,
285 X_LAST_UPDATE_DATE in DATE,
286 X_LAST_UPDATED_BY in NUMBER,
287 X_LAST_UPDATE_LOGIN in NUMBER,
288 X_SORT_ORDER in NUMBER,
289 X_LABOR_TP_RULE_ID in NUMBER,
290 X_LABOR_PERCENTAGE_APPLIED in NUMBER,
291 X_NL_TP_RULE_ID in NUMBER,
292 X_NL_PERCENTAGE_APPLIED in NUMBER,
293 X_RECVR_ORGANIZATION_ID in NUMBER,
294 X_END_DATE_ACTIVE in DATE,
295 X_ATTRIBUTE_CATEGORY in VARCHAR2,
296 X_ATTRIBUTE1 in VARCHAR2,
297 X_ATTRIBUTE2 in VARCHAR2,
298 X_ATTRIBUTE3 in VARCHAR2,
299 X_ATTRIBUTE4 in VARCHAR2,
300 X_ATTRIBUTE5 in VARCHAR2,
301 X_ATTRIBUTE6 in VARCHAR2,
302 X_ATTRIBUTE7 in VARCHAR2,
303 X_ATTRIBUTE8 in VARCHAR2,
304 X_ATTRIBUTE9 in VARCHAR2,
305 X_ATTRIBUTE10 in VARCHAR2,
306 X_ATTRIBUTE11 in VARCHAR2,
307 X_ATTRIBUTE12 in VARCHAR2,
308 X_ATTRIBUTE13 in VARCHAR2,
309 X_ATTRIBUTE14 in VARCHAR2,
310 X_ATTRIBUTE15 in VARCHAR2
311 ) is
312 begin
313 update PA_CC_TP_SCHEDULE_LINES set
314 TP_SCHEDULE_LINE_ID=X_SCHEDULE_LINE_ID,
315 TP_SCHEDULE_ID=X_TP_SCHEDULE_ID,
316 PRVDR_ORGANIZATION_ID=X_PRVDR_ORGANIZATION_ID,
317 START_DATE_ACTIVE=X_START_DATE_ACTIVE,
318 DEFAULT_FLAG=X_DEFAULT_FLAG,
319 TP_AMT_TYPE_CODE=X_TP_AMT_TYPE_CODE,
320 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
321 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
322 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
323 SORT_ORDER=X_SORT_ORDER,
324 LABOR_TP_RULE_ID=X_LABOR_TP_RULE_ID,
325 LABOR_PERCENTAGE_APPLIED=X_LABOR_PERCENTAGE_APPLIED,
326 NL_TP_RULE_ID=X_NL_TP_RULE_ID,
327 NL_PERCENTAGE_APPLIED=X_NL_PERCENTAGE_APPLIED,
328 RECVR_ORGANIZATION_ID=X_RECVR_ORGANIZATION_ID,
329 END_DATE_ACTIVE=X_END_DATE_ACTIVE,
330 ATTRIBUTE_CATEGORY=X_ATTRIBUTE_CATEGORY,
331 ATTRIBUTE1=X_ATTRIBUTE1,
332 ATTRIBUTE2=X_ATTRIBUTE2,
333 ATTRIBUTE3=X_ATTRIBUTE3,
334 ATTRIBUTE4=X_ATTRIBUTE4,
335 ATTRIBUTE5=X_ATTRIBUTE5,
336 ATTRIBUTE6=X_ATTRIBUTE6,
337 ATTRIBUTE7=X_ATTRIBUTE7,
338 ATTRIBUTE8=X_ATTRIBUTE8,
339 ATTRIBUTE9=X_ATTRIBUTE9,
340 ATTRIBUTE10=X_ATTRIBUTE10,
341 ATTRIBUTE11=X_ATTRIBUTE11,
342 ATTRIBUTE12=X_ATTRIBUTE12,
343 ATTRIBUTE13=X_ATTRIBUTE13,
344 ATTRIBUTE14=X_ATTRIBUTE14,
345 ATTRIBUTE15=X_ATTRIBUTE15
346 where rowid = X_rowid;
347 if (sql%notfound) then
348 raise no_data_found;
349 end if;
350 end UPDATE_ROW;
351
352 procedure DELETE_ROW (
353 X_ROWID in varchar2
354 ) is
355 begin
356 delete from PA_CC_TP_SCHEDULE_LINES
357 where Rowid = X_rowid;
358 if (sql%notfound) then
359 raise no_data_found;
360 end if;
361 end DELETE_ROW;
362
363
364 end PA_CC_TP_SCHEDULE_LINES_PKG;
[Home] [Help]