eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PA_CONTROL_ITEMS_PKG

Source


4 
1 PACKAGE BODY PA_CONTROL_ITEMS_PKG AS
2 --$Header: PACICITB.pls 120.3.12010000.7 2010/05/06 12:00:56 rrambati ship $
3 
5 procedure INSERT_ROW (
6          p_ci_type_id           IN  NUMBER
7         ,p_summary              IN  VARCHAR2
8         ,p_status_code          IN  VARCHAR2
9         ,p_owner_id             IN  NUMBER
10         ,p_highlighted_flag     IN  VARCHAR2
11         ,p_progress_status_code IN  VARCHAR2
12         ,p_progress_as_of_date  IN  DATE
13         ,p_classification_code  IN  NUMBER
14         ,p_reason_code          IN  NUMBER
15         ,p_project_id           IN  NUMBER
16         ,p_last_modified_by_id  IN  NUMBER
17      := NVL(PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ), fnd_global.user_id) -- 26-Jun-2009    cklee     Modified for the Bug# 8633676
18         ,p_object_type          IN  VARCHAR2   := NULL
19         ,p_object_id            IN  NUMBER     := NULL
20         ,p_ci_number            IN  VARCHAR2   := NULL
21         ,p_date_required        IN  DATE       := NULL
22         ,p_date_closed          IN  DATE      := NULL
23         ,p_closed_by_id         IN  NUMBER    := NULL
24 
25 
26         ,p_description          IN  VARCHAR2   := NULL
27         ,p_status_overview      IN  VARCHAR2   := NULL
28         ,p_resolution           IN  VARCHAR2   := NULL
29         ,p_resolution_code      IN  NUMBER     := NULL
30         ,p_priority_code        IN  VARCHAR2   := NULL
34         ,p_price                IN  NUMBER     := NULL
31         ,p_effort_level_code    IN  VARCHAR2   := NULL
32         ,p_open_action_num      IN  NUMBER    := NULL
33 
35         ,p_price_currency_code  IN  VARCHAR2   := NULL
36         ,p_source_type_code     IN  VARCHAR2   := NULL
37         ,p_source_comment       IN  VARCHAR2   := NULL
38         ,p_source_number        IN  VARCHAR2   := NULL
39         ,p_source_date_received IN  DATE       := NULL
40 
41         ,p_source_organization  IN  VARCHAR2  := NULL
42         ,p_source_person        IN  VARCHAR2  := NULL
43 
44         ,p_attribute_category    IN  VARCHAR2 := NULL
45         ,p_attribute1            IN  VARCHAR2 := NULL
46         ,p_attribute2            IN  VARCHAR2 := NULL
47         ,p_attribute3            IN  VARCHAR2 := NULL
48         ,p_attribute4            IN  VARCHAR2 := NULL
49         ,p_attribute5            IN  VARCHAR2 := NULL
50         ,p_attribute6            IN  VARCHAR2 := NULL
51         ,p_attribute7            IN  VARCHAR2 := NULL
52         ,p_attribute8            IN  VARCHAR2 := NULL
53         ,p_attribute9            IN  VARCHAR2 := NULL
54         ,p_attribute10           IN  VARCHAR2 := NULL
55         ,p_attribute11           IN  VARCHAR2 := NULL
56         ,p_attribute12           IN  VARCHAR2 := NULL
57         ,p_attribute13           IN  VARCHAR2 := NULL
58         ,p_attribute14           IN  VARCHAR2 := NULL
59         ,p_attribute15           IN  VARCHAR2 := NULL
60 
61 -- start: 26-Jun-2009    cklee     Modified for the Bug# 8633676
62         ,p_PCO_STATUS_CODE         IN  VARCHAR2 := NULL
63         ,p_APPROVAL_TYPE_CODE      IN  VARCHAR2 := NULL
64         ,p_LOCKED_FLAG             IN  VARCHAR2 := 'N'
65 -- end: 26-Jun-2009    cklee     Modified for the Bug# 8633676
66 
67         ,p_Version_number        IN number    := null
68         ,p_Current_Version_flag  IN varchar2 := 'Y'
69         ,p_Version_Comments      IN varchar2 := NULL
70         ,p_Original_ci_id        IN number := NULL
71         ,p_Source_ci_id          IN number := NULL
72 
73   	,px_ci_id               IN  OUT  NOCOPY NUMBER
74   	,x_return_status         OUT  NOCOPY VARCHAR2
75   	,x_msg_count             OUT  NOCOPY NUMBER
76  	,x_msg_data              OUT  NOCOPY VARCHAR2
77         ,p_orig_system_code     IN VARCHAR2 := NULL
78         ,p_orig_system_reference IN VARCHAR2 := NULL
79         ,p_change_approver       IN varchar2 DEFAULT NULL --Added for bug 9108474
80 
81 ) is
82 
83 
84 
85    l_rowid ROWID;
86    l_ci_id NUMBER;
87    l_number_prefix varchar2(50) := NULL;
88    l_vers_num NUMBER :=3;
89    l_Current_Version_flag varchar2(1) := 'Y';
90 
91    cursor C is select ROWID from PA_CONTROL_ITEMS
92      where ci_id = px_ci_id;
93 
94    cursor vn_csr is select max(version_number) from PA_CONTROL_ITEMS
95      where Original_ci_id = p_Original_ci_id;
96 
97 	cursor curr_prefix is select prefix_auto_number from pa_ci_types_v
98 	  where ci_type_id = p_ci_type_id;
99 
100 BEGIN
101 
102   x_return_status := FND_API.G_RET_STS_SUCCESS;
103 
104   --get the unique control item id from the Oracle Sequence
105   IF (px_ci_id is null) THEN
106 	  SELECT pa_control_items_s.nextval
107 	  INTO l_ci_id
108 	  FROM DUAL;
109 	  px_ci_id := l_ci_id;
110   END IF;
111 
112  IF (nvl(p_version_number,1) = 1) THEN
113 
114   open curr_prefix;
115   fetch curr_prefix into l_number_prefix;
116   if (curr_prefix%notfound) then
117     close curr_prefix;
118     raise no_data_found;
119   end if;
120   close curr_prefix;
121 
122   l_number_prefix := l_number_prefix || p_ci_number;
123 
124  ELSE
125 
126   l_number_prefix := p_ci_number;
127 
128  END IF;
129   insert into PA_CONTROL_ITEMS (
130         ci_id
131         ,ci_type_id
132         ,summary
133         ,status_code
134         ,owner_id
135         ,highlighted_flag
136         ,progress_status_code
137         ,progress_as_of_date
138         ,classification_code_id
139         ,reason_code_id
140         ,RECORD_VERSION_NUMBER
141         ,project_id
142         ,LAST_MODIFICATION_DATE
143         ,LAST_MODIFIED_BY_ID
144         ,CREATION_DATE
145         ,CREATED_BY
146         ,LAST_UPDATE_DATE
147         ,LAST_UPDATED_BY
148         ,LAST_UPDATE_LOGIN
149 
150         ,object_type
151         ,object_id
152         ,ci_number
153         ,date_required
154         ,date_closed
155         ,closed_by_id
156         ,description
157         ,status_overview
158         ,resolution
159         ,resolution_code_id
160         ,priority_code
161         ,effort_level_code
162         ,open_action_num
163         ,price
164         ,price_currency_code
165         ,source_type_code
166         ,source_comment
167         ,source_number
168         ,source_date_received
169         ,source_organization
170         ,source_person
171 
172 	,attribute_category
173         ,attribute1
174         ,attribute2
175         ,attribute3
176         ,attribute4
177         ,attribute5
178         ,attribute6
179         ,attribute7
180         ,attribute8
181         ,attribute9
182         ,attribute10
183         ,attribute11
184         ,attribute12
188         ,orig_system_code
185         ,attribute13
186         ,attribute14
187         ,attribute15
189         ,orig_system_reference
190 
191 -- start: 26-Jun-2009    cklee     Modified for the Bug# 8633676
192         ,PCO_STATUS_CODE
193         ,APPROVAL_TYPE_CODE
194         ,LOCKED_FLAG
195 -- end: 26-Jun-2009    cklee     Modified for the Bug# 8633676
196 
197         ,Version_number
198         ,Current_Version_flag
199         ,Version_Comments
200         ,Original_ci_id
201         ,Source_ci_id
202         ,CHANGE_APPROVER -- Added for bug 9108474
203 
204   ) VALUES (
205          px_ci_id
206         ,p_ci_type_id
207         ,p_summary
208         ,p_status_code
209         ,p_owner_id
210         ,p_highlighted_flag
211         ,p_progress_status_code
212         ,p_progress_as_of_date
213         ,p_classification_code
214         ,p_reason_code
215         ,1                      --record_version_number
216         ,p_project_id
217         ,sysdate                --last_modification_date
218         ,p_last_modified_by_id  --hz_parties.party_id
219         ,sysdate                --creation_date
220         ,fnd_global.user_id     --created_by
221         ,sysdate                --last_update_date
222         ,fnd_global.user_id     --last_updated_by
223         ,fnd_global.user_id     --last_update_login
224         ,p_object_type
225         ,p_object_id
226         ,l_number_prefix
227         ,p_date_required
228         ,p_date_closed
229         ,p_closed_by_id
230         ,p_description
231         ,p_status_overview
232 
233         ,p_resolution
234 	,p_resolution_code
235         ,p_priority_code
236         ,p_effort_level_code
237         ,nvl(p_open_action_num,0)
238         ,p_price
239         ,p_price_currency_code
240         ,p_source_type_code
241         ,p_source_comment
242         ,p_source_number
243         ,p_source_date_received
244         ,p_source_organization
245         ,p_source_person
246 
247         ,p_attribute_category
248         ,p_attribute1
249         ,p_attribute2
250         ,p_attribute3
251         ,p_attribute4
252         ,p_attribute5
253         ,p_attribute6
254         ,p_attribute7
255         ,p_attribute8
256         ,p_attribute9
257         ,p_attribute10
258         ,p_attribute11
259         ,p_attribute12
260         ,p_attribute13
261         ,p_attribute14
262         ,p_attribute15
263         ,p_orig_system_code
264         ,p_orig_system_reference
265 
266 -- start: 26-Jun-2009    cklee     Modified for the Bug# 8633676
267         ,p_PCO_STATUS_CODE
268         ,p_APPROVAL_TYPE_CODE
269         ,p_LOCKED_FLAG
270 -- end: 26-Jun-2009    cklee     Modified for the Bug# 8633676
271 
272         ,nvl(p_version_number,1)
273         ,'Y'
274         ,p_Version_Comments
275         ,nvl(p_Original_ci_id,px_ci_id)
276         ,nvl(p_source_ci_id,px_ci_id)
277         ,p_change_approver
278     );
279 
280 
281   -- PA_CHNGE_DOC_POLICY_PVT.SET_CHNGE_DOC_VERS;
282 
283   open c;
284   fetch c into l_ROWID;
285   if (c%notfound) then
286     close c;
287     raise no_data_found;
288   end if;
289   close c;
290  /*  px_ci_id := l_ci_id;  */  /* Bug#3297238 */
291 
292    --PA_CHNGE_DOC_POLICY_PVT.RESET_CHNGE_DOC_VERS;
293 
294 
295 EXCEPTION
296     WHEN OTHERS THEN
297         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
298         RAISE;
299 
300 end INSERT_ROW;
301 
302 procedure UPDATE_ROW (
303         p_ci_id                IN  NUMBER
304         ,p_ci_type_id           IN  NUMBER
305         ,p_summary              IN  VARCHAR2
306         ,p_status_code          IN  VARCHAR2
307         ,p_owner_id             IN  NUMBER
308         ,p_highlighted_flag     IN  VARCHAR2
309         ,p_progress_status_code IN VARCHAR2
310         ,p_progress_as_of_date  IN DATE
311         ,p_classification_code  IN NUMBER
312         ,p_reason_code          IN NUMBER
313         ,p_record_version_number IN  NUMBER
314 
315         ,p_project_id           IN  NUMBER
316         ,p_last_modified_by_id  IN  NUMBER
317      := NVL(PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ), fnd_global.user_id) -- 26-Jun-2009    cklee     Modified for the Bug# 8633676
318         ,p_object_type          IN  VARCHAR2   := NULL
319         ,p_object_id            IN  NUMBER     := NULL
320         ,p_ci_number            IN  VARCHAR2   := NULL
321         ,p_date_required        IN  DATE       := NULL
322         ,p_date_closed          IN  DATE      := NULL
323         ,p_closed_by_id         IN  NUMBER    := NULL
324         ,p_description          IN  VARCHAR2   := NULL
325         ,p_status_overview      IN  VARCHAR2   := NULL
326 
327         ,p_resolution           IN  VARCHAR2   := NULL
328         ,p_resolution_code      IN  NUMBER     := NULL
329         ,p_priority_code        IN  VARCHAR2   := NULL
330         ,p_effort_level_code    IN  VARCHAR2   := NULL
331         ,p_open_action_num      IN  NUMBER    := NULL
332 
333         ,p_price                IN  NUMBER         := NULL
334         ,p_price_currency_code  IN  VARCHAR2   := NULL
335         ,p_source_type_code     IN  VARCHAR2   := NULL
336         ,p_source_comment       IN  VARCHAR2   := NULL
340         ,p_source_person        IN  VARCHAR2       := NULL
337         ,p_source_number        IN  VARCHAR2   := NULL
338         ,p_source_date_received IN  DATE           := NULL
339         ,p_source_organization  IN  VARCHAR2  := NULL
341 
342         ,p_attribute_category    IN  VARCHAR2 := NULL
343 
344         ,p_attribute1            IN  VARCHAR2 := NULL
345         ,p_attribute2            IN  VARCHAR2 := NULL
346         ,p_attribute3            IN  VARCHAR2 := NULL
347         ,p_attribute4            IN  VARCHAR2 := NULL
348         ,p_attribute5            IN  VARCHAR2 := NULL
349         ,p_attribute6            IN  VARCHAR2 := NULL
350         ,p_attribute7            IN  VARCHAR2 := NULL
351         ,p_attribute8            IN  VARCHAR2 := NULL
352         ,p_attribute9            IN  VARCHAR2 := NULL
353         ,p_attribute10           IN  VARCHAR2 := NULL
354         ,p_attribute11           IN  VARCHAR2 := NULL
355         ,p_attribute12           IN  VARCHAR2 := NULL
356         ,p_attribute13           IN  VARCHAR2 := NULL
357         ,p_attribute14           IN  VARCHAR2 := NULL
358         ,p_attribute15           IN  VARCHAR2 := NULL
359 
360 -- start: 26-Jun-2009    cklee     Modified for the Bug# 8633676
361         ,p_PCO_STATUS_CODE         IN  VARCHAR2 := NULL
362         ,p_APPROVAL_TYPE_CODE      IN  VARCHAR2 := NULL
363         ,p_LOCKED_FLAG             IN  VARCHAR2 := 'N'
364 -- end: 26-Jun-2009    cklee     Modified for the Bug# 8633676
365 
366         ,p_Version_number        IN number
367         ,p_Current_Version_flag  IN varchar2 := 'Y'
368         ,p_Version_Comments      IN varchar2 := NULL
369         ,p_Original_ci_id        IN number := NULL
370         ,p_Source_ci_id          IN number := NULL
371 		,p_change_approver       IN varchar2 := NULL
372         ,x_return_status         OUT  NOCOPY VARCHAR2
373         ,x_msg_count             OUT  NOCOPY NUMBER
374         ,x_msg_data              OUT  NOCOPY VARCHAR2
375         ,p_last_updated_by 	 in NUMBER default fnd_global.user_id  --Added the parameter for bug# 3877985
376         ,p_last_update_date 	 in DATE default sysdate               --Added the parameter for bug# 3877985
377         ,p_last_update_login     in NUMBER default fnd_global.user_id  --Added the parameter for bug# 3877985
378 
379 ) is
380 begin
381   x_return_status := FND_API.G_RET_STS_SUCCESS;
382 
383   update PA_CONTROL_ITEMS set
384          ci_type_id			= Nvl(p_ci_type_id,ci_type_id)
385         ,summary                        = Nvl(p_summary,summary)
386         ,status_code                    = Nvl(p_status_code, status_code)
387         ,owner_id                       = Nvl(p_owner_id,owner_id)
388         ,highlighted_flag               = Nvl(p_highlighted_flag, highlighted_flag)
389         ,progress_status_code           = Nvl(p_progress_status_code, progress_status_code)
390         ,progress_as_of_date            = Nvl(p_progress_as_of_date, progress_as_of_date)
391         ,classification_code_id         = Nvl(p_classification_code,classification_code_id)
392         ,reason_code_id                 = Nvl(p_reason_code,reason_code_id)
393         ,RECORD_VERSION_NUMBER          = record_version_number +1
394         ,project_id			= Nvl(p_project_id,project_id)
395         ,LAST_MODIFICATION_DATE         = SYSDATE
396         ,last_modified_by_id            = p_last_modified_by_id
397         ,LAST_UPDATE_DATE               = p_last_update_date    --Modified for bug# 3877985
398         ,LAST_UPDATED_BY                = p_last_updated_by     --Modified for bug# 3877985
399         ,LAST_UPDATE_LOGIN              = p_last_update_login   --Modified for bug# 3877985
400         ,object_type			= p_object_type
401         ,object_id			= p_object_id
402         ,ci_number                      = p_ci_number
403         ,date_required                  = p_date_required
404         ,date_closed                    = p_date_closed
405         ,closed_by_id                   = p_closed_by_id
406         ,description			= p_description
407         ,status_overview                = p_status_overview
408         ,resolution			= p_resolution
409         ,resolution_code_id             = p_resolution_code
410         ,priority_code			= p_priority_code
411         ,effort_level_code              = p_effort_level_code
412         ,open_action_num                = nvl(p_open_action_num,open_action_num)
413         ,price				= p_price
414         ,price_currency_code		= p_price_currency_code
415         ,source_type_code		= p_source_type_code
416         ,source_comment			= p_source_comment
417         ,source_number			= p_source_number
418         ,source_date_received		= p_source_date_received
419         ,source_organization            = p_source_organization--, source_org_id)
420         ,source_person   		= p_source_person --, source_person_id)
421 
422         ,attribute_category             = p_attribute_category--, attribute1)
423 
424         ,attribute1			= p_attribute1--, attribute1)
425         ,attribute2			= p_attribute2-- , attribute2)
426         ,attribute3                     = p_attribute3--, attribute3)
427         ,attribute4                     = p_attribute4--, attribute4)
428         ,attribute5                     = p_attribute5--, attribute5)
429         ,attribute6                     = p_attribute6--, attribute6)
430         ,attribute7                     = p_attribute7--, attribute7)
431         ,attribute8                     = p_attribute8--, attribute8)
432         ,attribute9                     = p_attribute9--, attribute9)
433         ,attribute10                    = p_attribute10--, attribute10)
434         ,attribute11                    = p_attribute11--, attribute11)
435         ,attribute12                    = p_attribute12--, attribute12)
436         ,attribute13                    = p_attribute13--, attribute13)
437         ,attribute14                    = p_attribute14--, attribute14)
438         ,attribute15                    = p_attribute15--, attribute15)
439 -- start: 26-Jun-2009    cklee     Modified for the Bug# 8633676
440         ,PCO_STATUS_CODE                = p_PCO_STATUS_CODE
441         ,APPROVAL_TYPE_CODE             = p_APPROVAL_TYPE_CODE
442         ,LOCKED_FLAG                    = p_LOCKED_FLAG
443 -- end: 26-Jun-2009    cklee     Modified for the Bug# 8633676
444 
445         --,Version_number                 = p_Version_number--, attribute15)
446         --,Current_Version_flag           = p_Current_Version_flag--, attribute15)
447         ,Version_Comments               = p_Version_Comments--, attribute15)
448         --,Original_ci_id                 = p_Original_ci_id--, attribute15)
449         --,Source_ci_id                   = p_Source_ci_id--, attribute15)
450 		,Change_approver                  = p_change_approver
451 where ci_id     = p_ci_id
452     AND record_version_number = Nvl(p_record_version_number, record_version_number);
453 
454    if (sql%notfound) then
455        PA_UTILS.Add_Message ( p_app_short_name => 'PA',p_msg_name => 'PA_XC_RECORD_CHANGED');
456        x_return_status := FND_API.G_RET_STS_ERROR;
457   end if;
458 
459 EXCEPTION
460     WHEN OTHERS THEN
461         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
462         RAISE;
463 end UPDATE_ROW;
464 
465 
466 
467 procedure DELETE_ROW (
468   p_ci_id                IN  NUMBER
469   ,p_record_version_number       IN     NUMBER
470   ,x_return_status               OUT    NOCOPY  VARCHAR2
471   ,x_msg_count                   OUT    NOCOPY  NUMBER
472   ,x_msg_data                    OUT    NOCOPY  VARCHAR2
473 
474 ) is
475 begin
476   x_return_status := FND_API.G_RET_STS_SUCCESS;
477 
478   DELETE FROM  PA_CONTROL_ITEMS
479     where ci_id = p_ci_id
480       and record_version_number = p_record_version_number;
481 
482 
483 EXCEPTION
484     WHEN OTHERS THEN
485         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
486         RAISE;
487 end DELETE_ROW;
488 
489 END  PA_CONTROL_ITEMS_PKG;