[Home] [Help]
PACKAGE BODY: APPS.PA_PROJ_ELEM_VER_STRUCTURE_PKG
Source
1 package BODY PA_PROJ_ELEM_VER_STRUCTURE_PKG as
2 /*$Header: PAXSVATB.pls 120.2 2005/08/25 23:24:29 avaithia noship $*/
3
4 -- API name : insert_row
5 -- Type : Table Handlers
6 --
7 -- History
8 --
9 -- 25-JUN-01 HSIU -Created
10 --
11 --
12
13
14 procedure insert_row
15 (
16 X_ROWID IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
17 , X_PEV_STRUCTURE_ID IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
18 , X_ELEMENT_VERSION_ID NUMBER
19 , X_VERSION_NUMBER NUMBER
20 , X_NAME VARCHAR2
21 , X_PROJECT_ID NUMBER
22 , X_PROJ_ELEMENT_ID NUMBER
23 , X_DESCRIPTION VARCHAR2
24 , X_EFFECTIVE_DATE DATE
25 , X_PUBLISHED_DATE DATE
26 , X_PUBLISHED_BY NUMBER
27 , X_CURRENT_BASELINE_DATE DATE
28 , X_CURRENT_BASELINE_FLAG VARCHAR2
29 , X_CURRENT_BASELINE_BY NUMBER
30 , X_ORIGINAL_BASELINE_DATE DATE
31 , X_ORIGINAL_BASELINE_FLAG VARCHAR2
32 , X_ORIGINAL_BASELINE_BY NUMBER
33 , X_LOCK_STATUS_CODE VARCHAR2
34 , X_LOCKED_BY NUMBER
35 , X_LOCKED_DATE DATE
36 , X_STATUS_CODE VARCHAR2
37 , X_WF_STATUS_CODE VARCHAR2
38 , X_LATEST_EFF_PUBLISHED_FLAG VARCHAR2
39 , X_CHANGE_REASON_CODE VARCHAR2
40 , X_RECORD_VERSION_NUMBER NUMBER
41 , X_CURRENT_WORKING_FLAG VARCHAR2 := 'N' --FPM changes bug 3301192
42 , x_source_object_id IN NUMBER:= PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM --Bug No 3594635 SMukka
43 , x_source_object_type IN VARCHAR2:= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR --Bug No 3594635 SMukka
44 )
45 IS
46 cursor c is select rowid from PA_PROJ_ELEM_VER_STRUCTURE
47 where pev_structure_id = X_PEV_STRUCTURE_ID;
48 cursor c2 is select pa_proj_elem_ver_structure_s.nextval from sys.dual;
49
50 -- 4537865 : Start
51 l_incoming_rowid NUMBER ;
52 l_incoming_pev_structure_id NUMBER ;
53 -- 4537865 : End
54
55 BEGIN
56 -- 4537865 : Start
57 l_incoming_rowid := X_ROWID ;
58 l_incoming_pev_structure_id := X_PEV_STRUCTURE_ID ;
59 -- 4537865 : End
60
61 if (X_PEV_STRUCTURE_ID IS NULL) then
62 open c2;
63 fetch c2 into X_PEV_STRUCTURE_ID;
64 close c2;
65 end if;
66
67 INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
68 PEV_STRUCTURE_ID
69 ,ELEMENT_VERSION_ID
70 ,VERSION_NUMBER
71 ,NAME
72 ,PROJECT_ID
73 ,PROJ_ELEMENT_ID
74 ,DESCRIPTION
75 ,EFFECTIVE_DATE
76 ,PUBLISHED_DATE
77 ,PUBLISHED_BY_PERSON_ID
78 ,CURRENT_BASELINE_DATE
79 ,CURRENT_FLAG
80 ,CURRENT_BASELINE_PERSON_ID
81 ,ORIGINAL_BASELINE_DATE
82 ,ORIGINAL_FLAG
83 ,ORIGINAL_BASELINE_PERSON_ID
84 ,LOCK_STATUS_CODE
85 ,LOCKED_BY_PERSON_ID
86 ,LOCKED_DATE
87 ,STATUS_CODE
88 ,WF_STATUS_CODE
89 ,LATEST_EFF_PUBLISHED_FLAG
90 ,RECORD_VERSION_NUMBER
91 ,WBS_RECORD_VERSION_NUMBER
92 ,CHANGE_REASON_CODE
93 ,CREATION_DATE
94 ,CREATED_BY
95 ,LAST_UPDATE_DATE
96 ,LAST_UPDATED_BY
97 ,LAST_UPDATE_LOGIN
98 ,process_update_wbs_flag
99 ,CURRENT_WORKING_FLAG
100 ,source_object_id --Bug No 3594635 SMukka
101 ,source_object_type --Bug No 3594635 SMukka
102 ) VALUES (
103 X_PEV_STRUCTURE_ID
104 ,X_ELEMENT_VERSION_ID
105 ,X_VERSION_NUMBER
106 ,X_NAME
107 ,X_PROJECT_ID
108 ,X_PROJ_ELEMENT_ID
109 ,X_DESCRIPTION
110 ,X_EFFECTIVE_DATE
111 ,X_PUBLISHED_DATE
112 ,X_PUBLISHED_BY
113 ,X_CURRENT_BASELINE_DATE
114 ,X_CURRENT_BASELINE_FLAG
115 ,X_CURRENT_BASELINE_BY
116 ,X_ORIGINAL_BASELINE_DATE
117 ,X_ORIGINAL_BASELINE_FLAG
118 ,X_ORIGINAL_BASELINE_BY
119 ,X_LOCK_STATUS_CODE
120 ,X_LOCKED_BY
121 ,X_LOCKED_DATE
122 ,X_STATUS_CODE
123 ,X_WF_STATUS_CODE
124 ,X_LATEST_EFF_PUBLISHED_FLAG
125 ,X_RECORD_VERSION_NUMBER
126 ,1
127 ,X_CHANGE_REASON_CODE
128 ,sysdate
129 ,FND_GLOBAL.USER_ID
130 ,sysdate
131 ,FND_GLOBAL.USER_ID
132 ,FND_GLOBAL.LOGIN_ID
133 ,'N'
134 ,X_CURRENT_WORKING_FLAG
135 ,x_source_object_id --Bug No 3594635 SMukka
136 ,x_source_object_type --Bug No 3594635 SMukka
137 );
138
139 OPEN c;
140 FETCH c into X_ROWID;
141 if (C%NOTFOUND) then
142 CLOSE c;
143 Raise NO_DATA_FOUND;
144 end if;
145 CLOSE c;
146 -- 4537865 : Included Exception block
147 EXCEPTION
148 WHEN OTHERS THEN
149
150 -- Restore the IN OUT params to original values
151 X_ROWID := l_incoming_rowid ;
152 x_pev_structure_id := l_incoming_pev_structure_id ;
153
154 -- Add the xception message to stack and RAISE
155 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEM_VER_STRUCTURE_PKG',
156 p_procedure_name => 'Insert_Row',
157 p_error_text => SUBSTRB(SQLERRM,1,240));
158 RAISE ;
159 END;
160
161
162
163 -- API name : update_row
164 -- Type : Table Handler
165 --
166 -- History
167 --
168 -- 25-JUN-01 HSIU -Created
169 --
170 --
171
172
173 procedure update_row
174 (
175 X_ROWID VARCHAR2
176 , X_PEV_STRUCTURE_ID NUMBER
177 , X_ELEMENT_VERSION_ID NUMBER
178 , X_VERSION_NUMBER NUMBER
179 , X_NAME VARCHAR2
180 , X_PROJECT_ID NUMBER
181 , X_PROJ_ELEMENT_ID NUMBER
182 , X_DESCRIPTION VARCHAR2
183 , X_EFFECTIVE_DATE DATE
184 , X_PUBLISHED_DATE DATE
185 , X_PUBLISHED_BY NUMBER
186 , X_CURRENT_BASELINE_DATE DATE
187 , X_CURRENT_BASELINE_FLAG VARCHAR2
188 , X_CURRENT_BASELINE_BY NUMBER
189 , X_ORIGINAL_BASELINE_DATE DATE
190 , X_ORIGINAL_BASELINE_FLAG VARCHAR2
191 , X_ORIGINAL_BASELINE_BY NUMBER
192 , X_LOCK_STATUS_CODE VARCHAR2
193 , X_LOCKED_BY NUMBER
194 , X_LOCKED_DATE DATE
195 , X_STATUS_CODE VARCHAR2
196 , X_WF_STATUS_CODE VARCHAR2
197 , X_LATEST_EFF_PUBLISHED_FLAG VARCHAR2
198 , X_CHANGE_REASON_CODE VARCHAR2
199 , X_RECORD_VERSION_NUMBER NUMBER
200 , X_CURRENT_WORKING_FLAG VARCHAR2 := 'N' --FPM changes bug 3301192
201 )
202 IS
203 BEGIN
204 UPDATE PA_PROJ_ELEM_VER_STRUCTURE
205 SET
206 PEV_STRUCTURE_ID = X_PEV_STRUCTURE_ID
207 , ELEMENT_VERSION_ID = X_ELEMENT_VERSION_ID
208 , VERSION_NUMBER = X_VERSION_NUMBER
209 , NAME = X_NAME
210 , PROJECT_ID = X_PROJECT_ID
211 , PROJ_ELEMENT_ID = X_PROJ_ELEMENT_ID
212 , DESCRIPTION = X_DESCRIPTION
213 , EFFECTIVE_DATE = X_EFFECTIVE_DATE
214 , PUBLISHED_DATE = X_PUBLISHED_DATE
215 , PUBLISHED_BY_PERSON_ID = X_PUBLISHED_BY
216 , CURRENT_BASELINE_DATE = X_CURRENT_BASELINE_DATE
217 , CURRENT_FLAG = X_CURRENT_BASELINE_FLAG
218 , CURRENT_BASELINE_PERSON_ID= X_CURRENT_BASELINE_BY
219 , ORIGINAL_BASELINE_DATE = X_ORIGINAL_BASELINE_DATE
220 , ORIGINAL_FLAG = X_ORIGINAL_BASELINE_FLAG
221 , ORIGINAL_BASELINE_PERSON_ID = X_ORIGINAL_BASELINE_BY
222 , LOCK_STATUS_CODE = X_LOCK_STATUS_CODE
223 , LOCKED_BY_PERSON_ID = X_LOCKED_BY
224 , LOCKED_DATE = X_LOCKED_DATE
225 , STATUS_CODE = X_STATUS_CODE
226 , WF_STATUS_CODE = X_WF_STATUS_CODE
227 , LATEST_EFF_PUBLISHED_FLAG = X_LATEST_EFF_PUBLISHED_FLAG
228 , RECORD_VERSION_NUMBER = NVL(X_RECORD_VERSION_NUMBER,0) + 1
229 , WBS_RECORD_VERSION_NUMBER = WBS_RECORD_VERSION_NUMBER + 1
230 , LAST_UPDATE_DATE = sysdate
231 , LAST_UPDATED_BY = FND_GLOBAL.USER_ID
232 , LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
233 , CHANGE_REASON_CODE = X_CHANGE_REASON_CODE
234 , CURRENT_WORKING_FLAG = X_CURRENT_WORKING_FLAG --FPM changes bug 3301192
235 WHERE rowid = X_ROWID;
236
237 if (SQL%NOTFOUND) then
238 Raise NO_DATA_FOUND;
239 end if;
240
241 END;
242
243
244
245 -- API name : delete_row
246 -- Type : Table Handler
247 --
248 -- History
249 --
250 -- 25-JUN-01 HSIU -Created
251 --
252 --
253
254
255 procedure delete_row
256 (
257 X_ROWID VARCHAR2
258 )
259 IS
260 BEGIN
261
262 DELETE FROM PA_PROJ_ELEM_VER_STRUCTURE
263 WHERE ROWID = X_ROWID;
264
265 if (SQL%NOTFOUND) then
266 Raise NO_DATA_FOUND;
267 end if;
268
269 END;
270
271
272
273 end PA_PROJ_ELEM_VER_STRUCTURE_PKG;