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