1 package body PAY_COST_ALLOCATIONS_F_PKG as
2 /* $Header: pycsa01t.pkb 120.1 2005/10/04 05:27:56 pgongada noship $ */
3 --
4 procedure insert_row(p_rowid in out nocopy varchar2,
5 p_cost_allocation_id in number,
6 p_effective_start_date in date,
7 p_effective_end_date in date,
8 p_business_group_id in number,
9 p_cost_allocation_keyflex_id in number,
10 p_assignment_id in number,
11 p_proportion in number,
12 p_request_id in number,
13 p_program_application_id in number,
14 p_program_id in number,
15 p_program_update_date in date) IS
16 --
17 begin
18 --
19 insert into PAY_COST_ALLOCATIONS_F
20 ( COST_ALLOCATION_ID,
21 EFFECTIVE_START_DATE,
22 EFFECTIVE_END_DATE,
23 BUSINESS_GROUP_ID,
24 COST_ALLOCATION_KEYFLEX_ID,
25 ASSIGNMENT_ID,
26 PROPORTION,
27 REQUEST_ID,
28 PROGRAM_APPLICATION_ID,
29 PROGRAM_ID,
30 PROGRAM_UPDATE_DATE)
31 values
32 ( p_cost_allocation_id,
33 p_effective_start_date,
34 p_effective_end_date,
35 p_business_group_id,
36 p_cost_allocation_keyflex_id,
37 p_assignment_id,
38 p_proportion,
39 p_request_id,
40 p_program_application_id,
41 p_program_id,
42 p_program_update_date);
43 --
44 select rowid
45 into p_rowid
46 from PAY_COST_ALLOCATIONS_F
47 where COST_ALLOCATION_ID = p_cost_allocation_id
48 and EFFECTIVE_START_DATE = p_effective_start_date
49 and EFFECTIVE_END_DATE = p_effective_end_date;
50 --
51 end insert_row;
52 --
53 procedure update_row(p_rowid in varchar2,
54 p_cost_allocation_id in number,
55 p_effective_start_date in date,
56 p_effective_end_date in date,
57 p_business_group_id in number,
58 p_cost_allocation_keyflex_id in number,
59 p_assignment_id in number,
60 p_proportion in number,
61 p_request_id in number,
62 p_program_application_id in number,
63 p_program_id in number,
64 p_program_update_date in date) is
65 begin
66 --
67 update PAY_COST_ALLOCATIONS_F
68 set COST_ALLOCATION_ID = p_cost_allocation_id,
69 EFFECTIVE_START_DATE = p_effective_start_date,
70 EFFECTIVE_END_DATE = p_effective_end_date,
71 BUSINESS_GROUP_ID = p_business_group_id,
72 COST_ALLOCATION_KEYFLEX_ID = p_cost_allocation_keyflex_id,
73 ASSIGNMENT_ID = p_assignment_id,
74 PROPORTION = p_proportion,
75 REQUEST_ID = p_request_id,
76 PROGRAM_APPLICATION_ID = p_program_application_id,
77 PROGRAM_ID = p_program_id,
78 PROGRAM_UPDATE_DATE = p_program_update_date
79 where ROWID = p_rowid;
80 --
81 end update_row;
82 --
83 procedure delete_row(p_rowid in varchar2) is
84 --
85 begin
86 --
87 delete from PAY_COST_ALLOCATIONS_F
88 where ROWID = p_rowid;
89 --
90 end delete_row;
91 --
92 procedure lock_row(p_rowid in varchar2,
93 p_cost_allocation_id in number,
94 p_effective_start_date in date,
95 p_effective_end_date in date,
96 p_business_group_id in number,
97 p_cost_allocation_keyflex_id in number,
98 p_assignment_id in number,
99 p_proportion in number,
100 p_request_id in number,
101 p_program_application_id in number,
102 p_program_id in number,
103 p_program_update_date in date) is
104 --
105 cursor C is select *
106 from PAY_COST_ALLOCATIONS_F
107 where rowid = p_rowid
108 for update of COST_ALLOCATION_ID nowait;
109 --
110 rowinfo C%rowtype;
111 --
112 begin
113 --
114 open C;
115 fetch C into rowinfo;
116 close C;
117 --
118 if ( ( (rowinfo.COST_ALLOCATION_ID = p_cost_allocation_id)
119 or (rowinfo.COST_ALLOCATION_ID is null and p_cost_allocation_id is null))
120 and ( (rowinfo.EFFECTIVE_START_DATE = p_effective_start_date)
121 or (rowinfo.EFFECTIVE_START_DATE is null and p_effective_start_date is null))
122 and ( (rowinfo.EFFECTIVE_END_DATE = p_effective_end_date)
123 or (rowinfo.EFFECTIVE_END_DATE is null and p_effective_end_date is null))
124 and ( (rowinfo.BUSINESS_GROUP_ID = p_business_group_id)
125 or (rowinfo.BUSINESS_GROUP_ID is null and p_business_group_id is null))
126 and ( (rowinfo.COST_ALLOCATION_KEYFLEX_ID = p_cost_allocation_keyflex_id)
127 or (rowinfo.COST_ALLOCATION_KEYFLEX_ID is null and p_cost_allocation_keyflex_id is null))
128 and ( (rowinfo.ASSIGNMENT_ID = p_assignment_id)
129 or (rowinfo.ASSIGNMENT_ID is null and p_assignment_id is null))
130 and ( (rowinfo.PROPORTION = p_proportion)
131 or (rowinfo.PROPORTION is null and p_proportion is null))
132 and ( (rowinfo.REQUEST_ID = p_request_id)
133 or (rowinfo.REQUEST_ID is null and p_request_id is null))
134 and ( (rowinfo.PROGRAM_APPLICATION_ID = p_program_application_id)
135 or (rowinfo.PROGRAM_APPLICATION_ID is null and p_program_application_id is null))
136 and ( (rowinfo.PROGRAM_ID = p_program_id)
137 or (rowinfo.PROGRAM_ID is null and p_program_id is null))
138 and ( (rowinfo.PROGRAM_UPDATE_DATE = p_program_update_date)
139 or (rowinfo.PROGRAM_UPDATE_DATE is null and p_program_update_date is null))) then
140 return;
141 else
142 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
143 app_exception.raise_exception;
144 end if;
145 end lock_row;
146 --
147 procedure maintain_cost_keyflex(p_cost_keyflex_id in out nocopy number,
148 p_cost_keyflex_structure in varchar2,
149 p_cost_allocation_keyflex_id in number,
150 p_concatenated_segments in varchar2,
151 p_summary_flag in varchar2,
152 p_start_date_active in date,
153 p_end_date_active in date,
154 p_segment1 in varchar2,
155 p_segment2 in varchar2,
156 p_segment3 in varchar2,
157 p_segment4 in varchar2,
158 p_segment5 in varchar2,
159 p_segment6 in varchar2,
160 p_segment7 in varchar2,
161 p_segment8 in varchar2,
162 p_segment9 in varchar2,
163 p_segment10 in varchar2,
164 p_segment11 in varchar2,
165 p_segment12 in varchar2,
166 p_segment13 in varchar2,
167 p_segment14 in varchar2,
168 p_segment15 in varchar2,
169 p_segment16 in varchar2,
170 p_segment17 in varchar2,
171 p_segment18 in varchar2,
172 p_segment19 in varchar2,
173 p_segment20 in varchar2,
174 p_segment21 in varchar2,
175 p_segment22 in varchar2,
176 p_segment23 in varchar2,
177 p_segment24 in varchar2,
178 p_segment25 in varchar2,
179 p_segment26 in varchar2,
180 p_segment27 in varchar2,
181 p_segment28 in varchar2,
182 p_segment29 in varchar2,
183 p_segment30 in varchar2) is
184 --
185 begin
186 -- This is a workaround as it is not possible to call this procedure from
187 -- the client side. Maybe a problem with incompatible PL/SQL versions
188 -- If a keyflex field has been entered then check to see if a record exists
189 -- with the segment values entered and retrieve its primary key value, else
190 -- key flexfield or a new complete flexfield has been entered.
191 --
192 -- Bug fix 3561111
193 -- Should pass null concatenated_segments into maintain_cost_keyflex to
194 -- ensure concatenated_segments gets calculated correctly.
195 -- The value passed from above is the from value - and will only be the
196 -- segments qualified at this level concatenated together.
197 --
198 p_cost_keyflex_id :=
199 hr_entry.maintain_cost_keyflex(p_cost_keyflex_structure,
200 p_cost_allocation_keyflex_id,
201 null,
202 p_summary_flag,
203 p_start_date_active,
204 p_end_date_active,
205 p_segment1,
206 p_segment2,
207 p_segment3,
208 p_segment4,
209 p_segment5,
210 p_segment6,
211 p_segment7,
212 p_segment8,
213 p_segment9,
214 p_segment10,
215 p_segment11,
216 p_segment12,
217 p_segment13,
218 p_segment14,
219 p_segment15,
220 p_segment16,
221 p_segment17,
222 p_segment18,
223 p_segment19,
224 p_segment20,
225 p_segment21,
226 p_segment22,
227 p_segment23,
228 p_segment24,
229 p_segment25,
230 p_segment26,
231 p_segment27,
232 p_segment28,
233 p_segment29,
234 p_segment30);
235 --
236 end maintain_cost_keyflex;
237 end PAY_COST_ALLOCATIONS_F_PKG;