[Home] [Help]
PACKAGE BODY: APPS.PA_FP_ADJ_ELEMENTS_PKG
Source
1 PACKAGE BODY pa_fp_adj_elements_pkg as
2 /* $Header: PAFPAETB.pls 120.1 2005/08/19 16:23:48 mwasowic noship $ */
3 -- Start of Comments
4 -- Package name : PA_FP_ADJ_ELEMENTS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PA_FP_ADJ_ELEMENTS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pafpoetb.pls';
13
14 PROCEDURE Insert_Row
15 ( px_adj_element_id IN OUT NOCOPY pa_fp_adj_elements.adj_element_id%TYPE --File.Sql.39 bug 4440895
16 ,p_resource_assignment_id IN pa_fp_adj_elements.RESOURCE_ASSIGNMENT_ID%TYPE
17 := FND_API.G_MISS_NUM
18 ,p_budget_version_id IN pa_fp_adj_elements.budget_version_id%TYPE
19 := FND_API.G_MISS_NUM
20 ,p_project_id IN pa_fp_adj_elements.project_id%TYPE
21 := FND_API.G_MISS_NUM
22 ,p_task_id IN pa_fp_adj_elements.task_id%TYPE
23 := FND_API.G_MISS_NUM
24 ,p_adjustment_reason_code IN pa_fp_adj_elements.ADJUSTMENT_REASON_CODE%TYPE
25 := FND_API.G_MISS_CHAR
26 ,p_adjustment_comments IN pa_fp_adj_elements.ADJUSTMENT_COMMENTS%TYPE
27 := FND_API.G_MISS_CHAR
28 ,x_row_id OUT NOCOPY ROWID --File.Sql.39 bug 4440895
29 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
30 IS
31 CURSOR C2 IS SELECT pa_fp_adj_elements_s.nextval FROM sys.dual;
32 BEGIN
33 IF (px_adj_element_id IS NULL) OR
34 (px_adj_element_id = FND_API.G_MISS_NUM) THEN
35 OPEN C2;
36 FETCH C2 INTO px_adj_element_id;
37 CLOSE C2;
38 END IF;
39 INSERT INTO pa_fp_adj_elements(
40 adj_element_id
41 ,creation_date
42 ,created_by
43 ,last_update_login
44 ,last_updated_by
45 ,last_update_date
46 ,budget_version_id
47 ,project_id
48 ,task_id
49 ,resource_assignment_id
50 ,ADJUSTMENT_REASON_CODE
51 ,ADJUSTMENT_COMMENTS
52 ) values (
53 px_adj_element_id
54 ,sysdate
55 ,fnd_global.user_id
56 ,fnd_global.login_id
57 ,fnd_global.user_id
58 ,sysdate
59 ,DECODE( p_budget_version_id, FND_API.G_MISS_NUM, NULL, p_budget_version_id)
60 ,DECODE( p_project_id, FND_API.G_MISS_NUM, NULL, p_project_id)
61 ,DECODE( p_task_id, FND_API.G_MISS_NUM, NULL, p_task_id)
62 ,DECODE( p_resource_assignment_id, FND_API.G_MISS_NUM, NULL, p_resource_assignment_id)
63 ,DECODE( p_adjustment_reason_code, FND_API.G_MISS_CHAR, NULL, p_adjustment_reason_code)
64 ,DECODE( p_adjustment_comments, FND_API.G_MISS_CHAR, NULL, p_adjustment_comments));
65 EXCEPTION
66 WHEN OTHERS THEN
67 FND_MSG_PUB.add_exc_msg( p_pkg_name
68 => 'PA_FP_ADJ_ELEMENTS_PKG.Insert_Row'
69 ,p_procedure_name
70 => PA_DEBUG.G_Err_Stack);
71 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
72 RAISE;
73 END Insert_Row;
74
75 PROCEDURE update_row
76 ( p_adj_element_id IN pa_fp_adj_elements.adj_element_id%TYPE
77 := FND_API.G_MISS_NUM
78 ,p_resource_assignment_id IN pa_fp_adj_elements.RESOURCE_ASSIGNMENT_ID%TYPE
79 := FND_API.G_MISS_NUM
80 ,p_budget_version_id IN pa_fp_adj_elements.budget_version_id%TYPE
81 := FND_API.G_MISS_NUM
82 ,p_project_id IN pa_fp_adj_elements.project_id%TYPE
83 := FND_API.G_MISS_NUM
84 ,p_task_id IN pa_fp_adj_elements.task_id%TYPE
85 := FND_API.G_MISS_NUM
86 ,p_adjustment_reason_code IN pa_fp_adj_elements.ADJUSTMENT_REASON_CODE%TYPE
87 := FND_API.G_MISS_CHAR
88 ,p_adjustment_comments IN pa_fp_adj_elements.ADJUSTMENT_COMMENTS%TYPE
89 := FND_API.G_MISS_CHAR
90 ,p_row_id IN ROWID
91 := NULL
92 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
93 IS
94 BEGIN
95 UPDATE pa_fp_adj_elements
96 SET
97 last_update_login = fnd_global.login_id
98 ,last_updated_by = fnd_global.user_id
99 ,last_update_date = sysdate
100 ,adj_element_id = DECODE( p_adj_element_id, FND_API.G_MISS_NUM,
101 adj_element_id, p_adj_element_id)
102 ,budget_version_id = DECODE( p_budget_version_id, FND_API.G_MISS_NUM,
103 budget_version_id, p_budget_version_id)
104 ,project_id = DECODE( p_project_id, FND_API.G_MISS_NUM, project_id,
105 p_project_id)
106 ,task_id = DECODE( p_task_id, FND_API.G_MISS_NUM, task_id, p_task_id)
107 ,resource_assignment_id = DECODE( p_resource_assignment_id,
108 FND_API.G_MISS_NUM,
109 resource_assignment_id,
110 p_resource_assignment_id)
111 ,adjustment_reason_code = DECODE( p_adjustment_reason_code,
112 FND_API.G_MISS_CHAR,
113 adjustment_reason_code,
114 p_adjustment_reason_code)
115 ,adjustment_comments = DECODE( p_adjustment_comments, FND_API.G_MISS_CHAR,
116 adjustment_comments, p_adjustment_comments)
117 WHERE adj_element_id = p_adj_element_id;
118
119 IF (SQL%NOTFOUND) THEN
120 PA_UTILS.Add_message ( p_app_short_name => 'PA'
121 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
122 x_return_status := FND_API.G_RET_STS_ERROR;
123 END IF;
124
125 EXCEPTION
126 WHEN OTHERS THEN
127 FND_MSG_PUB.add_exc_msg( p_pkg_name
128 => 'PA_FP_ADJ_ELEMENTS_PKG.Update_Row'
129 ,p_procedure_name
130 => PA_DEBUG.G_Err_Stack);
131 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
132 RAISE;
133 END Update_Row;
134
135 PROCEDURE Delete_Row
136 ( p_adj_element_id IN pa_fp_adj_elements.adj_element_id%TYPE
137 := FND_API.G_MISS_NUM
138 ,p_row_id IN ROWID
139 := NULL
140 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
141 IS
142 BEGIN
143 IF (p_adj_element_id IS NOT NULL OR
144 p_adj_element_id <> FND_API.G_MISS_NUM) THEN
145
146 DELETE FROM pa_fp_adj_elements
147 WHERE adj_element_id = p_adj_element_id;
148
149 ELSIF (p_row_id IS NOT NULL) THEN
150
151 DELETE FROM pa_fp_adj_elements
152 WHERE rowid = p_row_id;
153 END IF;
154
155 IF (SQL%NOTFOUND) THEN
156 PA_UTILS.Add_message ( p_app_short_name => 'PA'
157 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
158 x_return_status := FND_API.G_RET_STS_ERROR;
159 END IF;
160
161 EXCEPTION
162 WHEN OTHERS THEN
163 FND_MSG_PUB.add_exc_msg( p_pkg_name
164 => 'PA_FP_ADJ_ELEMENTS_PKG.Delete_Row'
165 ,p_procedure_name
166 => PA_DEBUG.G_Err_Stack);
167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
168 RAISE;
169 END Delete_Row;
170
171 PROCEDURE Lock_Row
172 ( p_adj_element_id IN pa_fp_adj_elements.adj_element_id%TYPE
173 := FND_API.G_MISS_NUM
174 ,p_row_id IN ROWID
175 := NULL
176 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
177 IS
178 l_row_id ROWID;
179 BEGIN
180 SELECT rowid into l_row_id
181 FROM pa_fp_adj_elements
182 WHERE adj_element_id = p_adj_element_id
183 OR rowid = p_row_id
184 FOR UPDATE NOWAIT;
185
186 EXCEPTION
187 WHEN OTHERS THEN
188 FND_MSG_PUB.add_exc_msg( p_pkg_name
189 => 'PA_FP_ADJ_ELEMENTS_PKG.Lock_Row'
190 ,p_procedure_name
191 => PA_DEBUG.G_Err_Stack);
192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
193 RAISE;
194 END Lock_Row;
195
196 End pa_fp_adj_elements_pkg;