[Home] [Help]
PACKAGE BODY: APPS.PA_EX_PAY_UTILS
Source
1 PACKAGE BODY PA_EX_PAY_UTILS AS
2 -- $Header: PAEXPYTB.pls 120.2 2011/04/08 09:31:57 speddi noship $
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : INSERT_ROW
6 -- INVOKED FROM : insert_row procedure
7 -- PURPOSE : inserts the row
8 -- HISTORY :
9 -- 05-Apr-2011 asahoo Created
10 -------------------------------------------------------------------------------
11 PROCEDURE Insert_Row
12 (
13 x_rowid IN OUT NOCOPY VARCHAR2,
14 x_business_group_id IN NUMBER,
15 x_payroll_id IN OUT NOCOPY NUMBER,
16 x_payroll_name IN VARCHAR2,
17 x_effective_start_date IN DATE,
18 x_effective_end_date IN DATE,
19 x_payroll_provider_name IN VARCHAR2,
20 x_active_flag IN VARCHAR2,
21 x_priority IN NUMBER,
22 x_description IN VARCHAR2,
23 x_creation_date IN DATE,
24 x_created_by IN NUMBER,
25 x_last_update_date IN DATE,
26 x_last_updated_by IN NUMBER,
27 x_last_update_login IN NUMBER
28 )
29 IS
30
31 CURSOR c IS
32 SELECT ROWID
33 FROM pa_pay_external_payroll
34 WHERE payroll_id = x_payroll_id;
35
36
37 BEGIN
38
39 INSERT INTO pa_pay_external_payroll
40 (
41 BUSINESS_GROUP_ID,
42 PAYROLL_ID,
43 PAYROLL_NAME,
44 EFFECTIVE_START_DATE,
45 EFFECTIVE_END_DATE,
46 PAYROLL_PROVIDER_NAME,
47 ACTIVE_FLAG,
48 PRIORITY,
49 DESCRIPTION,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY,
52 CREATION_DATE,
53 CREATED_BY,
54 LAST_UPDATE_LOGIN
55 )
56 VALUES
57 (
58 X_BUSINESS_GROUP_ID,
59 pa_pay_external_payroll_s.NEXTVAL,
60 X_PAYROLL_NAME,
61 X_EFFECTIVE_START_DATE,
62 X_EFFECTIVE_END_DATE,
63 X_PAYROLL_PROVIDER_NAME,
64 X_ACTIVE_FLAG,
65 X_PRIORITY,
66 X_DESCRIPTION,
67 X_LAST_UPDATE_DATE,
68 X_LAST_UPDATED_BY,
69 X_CREATION_DATE,
70 X_CREATED_BY,
71 X_LAST_UPDATE_LOGIN
72 )
73 RETURNING payroll_id INTO x_payroll_id;
74
75 OPEN c;
76 FETCH C INTO x_rowid;
77 IF (c%NOTFOUND) THEN
78 CLOSE c;
79 RAISE NO_DATA_FOUND;
80 END IF;
81 CLOSE c;
82
83 END Insert_Row;
84
85
86 -------------------------------------------------------------------------------
87 -- PROCDURE : Lock_Row
88 -- INVOKED FROM : Lock_Row procedure
89 -- PURPOSE : locks the row
90 -- HISTORY :
91 -- 05-Apr-2011 asahoo o Created
92 -------------------------------------------------------------------------------
93 PROCEDURE Lock_Row
94 (
95 x_business_group_id IN NUMBER,
96 x_payroll_id IN NUMBER,
97 x_payroll_name IN VARCHAR2,
98 x_effective_start_date IN DATE,
99 x_effective_end_date IN DATE,
100 x_payroll_provider_name IN VARCHAR2,
101 x_active_flag IN VARCHAR2,
102 x_priority IN NUMBER,
103 x_description IN VARCHAR2
104 )
105 IS
106 CURSOR c1 IS
107 SELECT *
108 FROM pa_pay_external_payroll
109 WHERE payroll_id = x_payroll_id
110 FOR UPDATE OF payroll_id NOWAIT;
111
112 Recinfo c1%ROWTYPE;
113
114 BEGIN
115 OPEN c1;
116 FETCH c1 INTO Recinfo;
117 IF (c1%NOTFOUND) THEN
118 CLOSE c1;
119 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
120 RAISE FND_API.G_EXC_ERROR;
121 END IF;
122 CLOSE c1;
123
124 if (
125 ( Recinfo.payroll_id = X_payroll_id)
126 AND ( (Recinfo.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID)
127 OR ( (Recinfo.BUSINESS_GROUP_ID IS NULL)
128 AND (X_BUSINESS_GROUP_ID IS NULL)))
129 AND ( (Recinfo.PAYROLL_NAME = X_PAYROLL_NAME)
130 OR ( (Recinfo.PAYROLL_NAME IS NULL)
131 AND (X_PAYROLL_NAME IS NULL)))
132 AND ( (Recinfo.EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE)
133 OR ( (Recinfo.EFFECTIVE_START_DATE IS NULL)
134 AND (X_EFFECTIVE_START_DATE IS NULL)))
135 AND ( (Recinfo.EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE)
136 OR ( (Recinfo.EFFECTIVE_END_DATE IS NULL)
137 AND (X_EFFECTIVE_END_DATE IS NULL)))
138 AND ( (Recinfo.PAYROLL_PROVIDER_NAME = X_PAYROLL_PROVIDER_NAME)
139 OR ( (Recinfo.PAYROLL_PROVIDER_NAME IS NULL)
140 AND (X_PAYROLL_PROVIDER_NAME IS NULL)))
141 AND ( (Recinfo.ACTIVE_FLAG = X_ACTIVE_FLAG)
142 OR ( (Recinfo.ACTIVE_FLAG IS NULL)
143 AND (X_ACTIVE_FLAG IS NULL)))
144 AND ( (Recinfo.PRIORITY = X_PRIORITY)
145 OR ( (Recinfo.PRIORITY IS NULL)
146 AND (X_PRIORITY IS NULL)))
147 AND ( (Recinfo.DESCRIPTION = X_DESCRIPTION)
148 OR ( (Recinfo.DESCRIPTION IS NULL)
149 AND (X_DESCRIPTION IS NULL)))
150 ) THEN
151 RETURN;
152 ELSE
153 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
154 RAISE FND_API.G_EXC_ERROR;
155 END IF;
156
157 END Lock_Row;
158
159
160 -------------------------------------------------------------------------------
161 -- PROCDURE : UPDATE_ROW
162 -- INVOKED FROM : update_row procedure
163 -- PURPOSE : updates the row
164 -- HISTORY :
165 -- 05-Apr-2011 asahoo o Created
166 -------------------------------------------------------------------------------
167 PROCEDURE Update_Row
168 (
169 x_business_group_id IN NUMBER,
170 x_payroll_id IN NUMBER,
171 x_payroll_name IN VARCHAR2,
172 x_effective_start_date IN DATE,
173 x_effective_end_date IN DATE,
174 x_payroll_provider_name IN VARCHAR2,
175 x_active_flag IN VARCHAR2,
176 x_priority IN NUMBER,
177 x_description IN VARCHAR2,
178 x_creation_date IN DATE,
179 x_created_by IN NUMBER,
180 x_last_update_date IN DATE,
181 x_last_updated_by IN NUMBER,
182 x_last_update_login IN NUMBER
183 )
184 IS
185
186 BEGIN
187
188 UPDATE pa_pay_external_payroll
189 SET BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID,
190 PAYROLL_NAME = X_PAYROLL_NAME,
191 EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE,
192 EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE,
193 PAYROLL_PROVIDER_NAME = X_PAYROLL_PROVIDER_NAME,
194 ACTIVE_FLAG = X_ACTIVE_FLAG,
195 PRIORITY = X_PRIORITY,
196 DESCRIPTION = X_DESCRIPTION,
197 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
198 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
199 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
200 WHERE PAYROLL_ID = X_PAYROLL_ID;
201
202
203 END Update_Row;
204
205
206 -------------------------------------------------------------------------------
207 -- PROCDURE : Delete_Row
208 -- INVOKED FROM : Delete_Row procedure
209 -- PURPOSE : Deletes the row
210 -- HISTORY :
211 -- 05-Apr-2011 asahoo o Created
212 -------------------------------------------------------------------------------
213 PROCEDURE Delete_Row
214 (
215 X_PAYROLL_ID in NUMBER
216 )
217 IS
218 BEGIN
219 DELETE FROM pa_pay_external_payroll
220 WHERE payroll_id = x_payroll_id;
221
222 IF (SQL%NOTFOUND) THEN
223 RAISE NO_DATA_FOUND;
224 END IF;
225
226
227 END Delete_Row;
228
229 END PA_EX_PAY_UTILS;