[Home] [Help]
PACKAGE BODY: APPS.PSP_ENC_ELEMENTS_PKG
Source
1 PACKAGE BODY PSP_ENC_ELEMENTS_PKG as
2 /* $Header: PSPENELB.pls 120.1 2006/02/20 05:06:08 spchakra noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_ENC_ELEMENT_ID in NUMBER,
6 X_INPUT_VALUE_ID in NUMBER, -- Added for Additional Earinings Element Enh
7 X_FORMULA_ID in NUMBER, -- Added for Encumbrance Rearchitecture
8 X_BUSINESS_GROUP_ID in NUMBER,
9 X_SET_OF_BOOKS_ID in NUMBER,
10 X_ELEMENT_TYPE_CATEGORY in VARCHAR2,
11 X_ELEMENT_TYPE_ID in NUMBER,
12 X_MODE in VARCHAR2 default 'R'
13 ) is
14 cursor C is select ROWID from PSP_ENC_ELEMENTS
15 where ENC_ELEMENT_ID = X_ENC_ELEMENT_ID;
16 X_LAST_UPDATE_DATE DATE;
17 X_LAST_UPDATED_BY NUMBER;
18 X_LAST_UPDATE_LOGIN NUMBER;
19 begin
20 X_LAST_UPDATE_DATE := SYSDATE;
21 if(X_MODE = 'I') then
22 X_LAST_UPDATED_BY := 1;
23 X_LAST_UPDATE_LOGIN := 0;
24 elsif (X_MODE = 'R') then
25 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
26 if X_LAST_UPDATED_BY is NULL then
27 X_LAST_UPDATED_BY := -1;
28 end if;
29 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
30 if X_LAST_UPDATE_LOGIN is NULL then
31 X_LAST_UPDATE_LOGIN := -1;
32 end if;
33 else
34 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
35 app_exception.raise_exception;
36 end if;
37 insert into PSP_ENC_ELEMENTS (
38 ENC_ELEMENT_ID,
39 INPUT_VALUE_ID, -- Added for Additional Earinings Element Enh
40 FORMULA_ID, -- Added for Encumbrance Rearchitecture
41 BUSINESS_GROUP_ID,
42 SET_OF_BOOKS_ID,
43 ELEMENT_TYPE_CATEGORY,
44 ELEMENT_TYPE_ID,
45 CREATION_DATE,
46 CREATED_BY,
47 LAST_UPDATE_DATE,
48 LAST_UPDATED_BY,
49 LAST_UPDATE_LOGIN
50 ) values (
51 X_ENC_ELEMENT_ID,
52 X_INPUT_VALUE_ID, -- Added for Additional Earinings Element Enh
53 X_FORMULA_ID, -- Added for Encumbrance Rearchitecture
54 X_BUSINESS_GROUP_ID,
55 X_SET_OF_BOOKS_ID,
56 X_ELEMENT_TYPE_CATEGORY,
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 );
64
65 open c;
66 fetch c into X_ROWID;
67 if (c%notfound) then
68 close c;
69 raise no_data_found;
70 end if;
71 close c;
72
73 end INSERT_ROW;
74
75 procedure LOCK_ROW (
76 X_ENC_ELEMENT_ID in NUMBER,
77 X_BUSINESS_GROUP_ID in NUMBER,
78 X_SET_OF_BOOKS_ID in NUMBER,
79 X_ELEMENT_TYPE_CATEGORY in VARCHAR2,
80 X_ELEMENT_TYPE_ID in NUMBER,
81 X_INPUT_VALUE_ID IN NUMBER, -- introduced for Enh. Encumbering addl. earnings elements
82 X_FORMULA_ID in NUMBER -- Added for Encumbrance Rearchitecture
83 ) is
84 cursor c1 is select
85 BUSINESS_GROUP_ID,
86 SET_OF_BOOKS_ID,
87 ELEMENT_TYPE_CATEGORY,
88 ELEMENT_TYPE_ID,
89 INPUT_VALUE_ID,
90 FORMULA_ID
91 from PSP_ENC_ELEMENTS
92 where ENC_ELEMENT_ID = X_ENC_ELEMENT_ID
93 -- AND ( (x_input_value_id IS NOT NULL AND input_value_id = x_input_value_id)
94 -- OR (x_formula_id IS NOT NULL AND formula_id = x_formula_id))
95 for update of ENC_ELEMENT_ID, INPUT_VALUE_ID, FORMULA_ID nowait; -- Included for Enh. Encumbering addl. earnings elements
96 tlinfo c1%rowtype;
97
98 begin
99 open c1;
100 fetch c1 into tlinfo;
101 if (c1%notfound) then
102 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
103 app_exception.raise_exception;
104 close c1;
105 return;
106 end if;
107 close c1;
108
109 if ( ( tlinfo.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID)
110 AND (tlinfo.SET_OF_BOOKS_ID = X_SET_OF_BOOKS_ID)
111 AND (tlinfo.ELEMENT_TYPE_CATEGORY = X_ELEMENT_TYPE_CATEGORY)
112 AND (tlinfo.ELEMENT_TYPE_ID = X_ELEMENT_TYPE_ID)
113 AND ( ((X_INPUT_VALUE_ID IS NOT NULL) AND (tlinfo.INPUT_VALUE_ID = X_INPUT_VALUE_ID))
114 OR ((X_FORMULA_ID IS NOT NULL) AND (tlinfo.FORMULA_ID = X_FORMULA_ID)))
115 ) then
116 null;
117 else
118 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
119 app_exception.raise_exception;
120 end if;
121 return;
122 end LOCK_ROW;
123
124 procedure UPDATE_ROW (
125 X_ENC_ELEMENT_ID in NUMBER,
126 X_INPUT_VALUE_ID in NUMBER, -- Added for Additional Earinings Element Enh
127 X_FORMULA_ID in NUMBER, -- Added for Encumbrance Rearchitecture
128 X_BUSINESS_GROUP_ID in NUMBER,
129 X_SET_OF_BOOKS_ID in NUMBER,
130 X_ELEMENT_TYPE_CATEGORY in VARCHAR2,
131 X_ELEMENT_TYPE_ID in NUMBER,
132 X_MODE in VARCHAR2 default 'R'
133 ) is
134 X_LAST_UPDATE_DATE DATE;
135 X_LAST_UPDATED_BY NUMBER;
136 X_LAST_UPDATE_LOGIN NUMBER;
137 begin
138 X_LAST_UPDATE_DATE := SYSDATE;
139 if(X_MODE = 'I') then
140 X_LAST_UPDATED_BY := 1;
141 X_LAST_UPDATE_LOGIN := 0;
142 elsif (X_MODE = 'R') then
143 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
144 if X_LAST_UPDATED_BY is NULL then
145 X_LAST_UPDATED_BY := -1;
146 end if;
147 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
148 if X_LAST_UPDATE_LOGIN is NULL then
149 X_LAST_UPDATE_LOGIN := -1;
150 end if;
151 else
152 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
153 app_exception.raise_exception;
154 end if;
155 update PSP_ENC_ELEMENTS set
156 BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID,
157 SET_OF_BOOKS_ID = X_SET_OF_BOOKS_ID,
158 INPUT_VALUE_ID = X_INPUT_VALUE_ID, -- Added for Additional Earinings Element Enh
159 FORMULA_ID = X_FORMULA_ID, -- Added for Additional Earinings Element Enh
160 ELEMENT_TYPE_CATEGORY = X_ELEMENT_TYPE_CATEGORY,
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 ENC_ELEMENT_ID = X_ENC_ELEMENT_ID
166 ;
167 if (sql%notfound) then
168 raise no_data_found;
169 end if;
170 end UPDATE_ROW;
171
172 procedure ADD_ROW (
173 X_ROWID in out NOCOPY VARCHAR2,
174 X_ENC_ELEMENT_ID in NUMBER,
175 X_INPUT_VALUE_ID in NUMBER, -- Added for Additional Earinings Element Enh
176 X_FORMULA_ID in NUMBER, -- Added for Encumbrance Rearchitecture
177 X_BUSINESS_GROUP_ID in NUMBER,
178 X_SET_OF_BOOKS_ID in NUMBER,
179 X_ELEMENT_TYPE_CATEGORY in VARCHAR2,
180 X_ELEMENT_TYPE_ID in NUMBER,
181 X_MODE in VARCHAR2 default 'R'
182 ) is
183 cursor c1 is select rowid from PSP_ENC_ELEMENTS
184 where ENC_ELEMENT_ID = X_ENC_ELEMENT_ID
185 ;
186 dummy c1%rowtype;
187 begin
188 open c1;
189 fetch c1 into dummy;
190 if (c1%notfound) then
191 close c1;
192 INSERT_ROW (
193 X_ROWID,
194 X_ENC_ELEMENT_ID,
195 X_INPUT_VALUE_ID, -- Added for Additional Earinings Element Enh
196 X_FORMULA_ID, -- Added for Encumbrance Rearchitecture
197 X_BUSINESS_GROUP_ID,
198 X_SET_OF_BOOKS_ID,
199 X_ELEMENT_TYPE_CATEGORY,
200 X_ELEMENT_TYPE_ID,
201 X_MODE);
202 return;
203 end if;
204 close c1;
205 UPDATE_ROW (
206 X_ENC_ELEMENT_ID,
207 X_INPUT_VALUE_ID, -- Added for Additional Earinings Element Enh
208 X_FORMULA_ID, -- Added for Encumbrance Rearchitecture
209 X_BUSINESS_GROUP_ID,
210 X_SET_OF_BOOKS_ID,
211 X_ELEMENT_TYPE_CATEGORY,
212 X_ELEMENT_TYPE_ID,
213 X_MODE);
214 end ADD_ROW;
215
216 procedure DELETE_ROW (
217 X_ENC_ELEMENT_ID in NUMBER
218 ) is
219 begin
220 delete from PSP_ENC_ELEMENTS
221 where ENC_ELEMENT_ID = X_ENC_ELEMENT_ID;
222 if (sql%notfound) then
223 raise no_data_found;
224 end if;
225 end DELETE_ROW;
226
227 end PSP_ENC_ELEMENTS_PKG;