1 PACKAGE BODY PA_TRANSACTIONS_PUB AS
2 /* $Header: PAXTTCPB.pls 120.11.12020000.3 2013/03/31 06:38:02 arbandyo ship $ */
3
4 INVALID_DATA EXCEPTION;
5 ----------------------------------------------------------------------
6 -- Please refer to package spec for detailed description of the
7 -- procedure.
8 ----------------------------------------------------------------------
9
10 PROCEDURE validate_transaction(
11 X_project_id IN NUMBER
12 , X_task_id IN NUMBER
13 , X_ei_date IN DATE
14 , X_expenditure_type IN VARCHAR2
15 , X_non_labor_resource IN VARCHAR2
16 , X_person_id IN NUMBER
17 , X_quantity IN NUMBER DEFAULT NULL
18 , X_denom_currency_code IN VARCHAR2 DEFAULT NULL
19 , X_acct_currency_code IN VARCHAR2 DEFAULT NULL
20 , X_denom_raw_cost IN NUMBER DEFAULT NULL
21 , X_acct_raw_cost IN NUMBER DEFAULT NULL
22 , X_acct_rate_type IN VARCHAR2 DEFAULT NULL
23 , X_acct_rate_date IN DATE DEFAULT NULL
24 , X_acct_exchange_rate IN NUMBER DEFAULT NULL
25 , X_transfer_ei IN NUMBER DEFAULT NULL
26 , X_incurred_by_org_id IN NUMBER DEFAULT NULL
27 , X_nl_resource_org_id IN NUMBER DEFAULT NULL
28 , X_transaction_source IN VARCHAR2 DEFAULT NULL
29 , X_calling_module IN VARCHAR2 DEFAULT NULL
30 , X_vendor_id IN NUMBER DEFAULT NULL
31 , X_entered_by_user_id IN NUMBER DEFAULT NULL
32 , X_attribute_category IN VARCHAR2 DEFAULT NULL
33 , X_attribute1 IN VARCHAR2 DEFAULT NULL
34 , X_attribute2 IN VARCHAR2 DEFAULT NULL
35 , X_attribute3 IN VARCHAR2 DEFAULT NULL
36 , X_attribute4 IN VARCHAR2 DEFAULT NULL
37 , X_attribute5 IN VARCHAR2 DEFAULT NULL
38 , X_attribute6 IN VARCHAR2 DEFAULT NULL
39 , X_attribute7 IN VARCHAR2 DEFAULT NULL
40 , X_attribute8 IN VARCHAR2 DEFAULT NULL
41 , X_attribute9 IN VARCHAR2 DEFAULT NULL
42 , X_attribute10 IN VARCHAR2 DEFAULT NULL
43 , X_attribute11 IN VARCHAR2 DEFAULT NULL
44 , X_attribute12 IN VARCHAR2 DEFAULT NULL
45 , X_attribute13 IN VARCHAR2 DEFAULT NULL
46 , X_attribute14 IN VARCHAR2 DEFAULT NULL
47 , X_attribute15 IN VARCHAR2 DEFAULT NULL
48 , X_msg_application IN OUT NOCOPY VARCHAR2
49 , X_msg_type OUT NOCOPY VARCHAR2
50 , X_msg_token1 OUT NOCOPY VARCHAR2
51 , X_msg_token2 OUT NOCOPY VARCHAR2
52 , X_msg_token3 OUT NOCOPY VARCHAR2
53 , X_msg_count OUT NOCOPY NUMBER
54 , X_msg_data OUT NOCOPY VARCHAR2
55 , X_billable_flag OUT NOCOPY VARCHAR2
56 , p_projfunc_currency_code IN VARCHAR2 default null
57 , p_projfunc_cost_rate_type IN VARCHAR2 default null
58 , p_projfunc_cost_rate_date IN DATE default null
59 , p_projfunc_cost_exchg_rate IN NUMBER default null
60 , p_assignment_id IN NUMBER default null
61 , p_work_type_id IN NUMBER default null
62 , p_sys_link_function IN VARCHAR2 default null
63 , P_Po_Header_Id IN NUMBER default null
64 , P_Po_Line_Id IN NUMBER default null
65 , P_Person_Type IN VARCHAR2 default null
66 , P_Po_Price_Type IN VARCHAR2 default null
67 , P_Document_Type IN VARCHAR2 default null
68 , P_Document_Line_Type IN VARCHAR2 default null
69 , P_Document_Dist_Type IN VARCHAR2 default null
70 /* 10253400: Payroll integration enhancement for 12.2.. start */
71 , P_Job_Id IN NUMBER default null
72 , P_Location_Id IN NUMBER default null
73 , P_Pay_Element_Type_Id IN NUMBER default null
74 , P_payroll_accrual_flag IN VARCHAR2 default null ---- Added for payroll billable_flag changes for 12.2 bug#12344689
75 /* 10253400: Payroll integration enhancement for 12.2.. end */
76 , P_pa_ref_num1 IN NUMBER default null
77 , P_pa_ref_num2 IN NUMBER default null
78 , P_pa_ref_num3 IN NUMBER default null
79 , P_pa_ref_num4 IN NUMBER default null
80 , P_pa_ref_num5 IN NUMBER default null
81 , P_pa_ref_num6 IN NUMBER default null
82 , P_pa_ref_num7 IN NUMBER default null
83 , P_pa_ref_num8 IN NUMBER default null
84 , P_pa_ref_num9 IN NUMBER default null
85 , P_pa_ref_num10 IN NUMBER default null
86 , P_pa_ref_var1 IN VARCHAR2 default null
87 , P_pa_ref_var2 IN VARCHAR2 default null
88 , P_pa_ref_var3 IN VARCHAR2 default null
89 , P_pa_ref_var4 IN VARCHAR2 default null
90 , P_pa_ref_var5 IN VARCHAR2 default null
94 , P_pa_ref_var9 IN VARCHAR2 default null
91 , P_pa_ref_var6 IN VARCHAR2 default null
92 , P_pa_ref_var7 IN VARCHAR2 default null
93 , P_pa_ref_var8 IN VARCHAR2 default null
95 , P_pa_ref_var10 IN VARCHAR2 default null) IS
96
97
98 l_sys_link_function VARCHAR2(3) default NULL; -- bug 2991182
99 l_task_count NUMBER := 0; -- Added as part of Bug 16220146
100 l_task_id NUMBER; -- Added as part of Bug 16220146
101 INVALID_TASK EXCEPTION; /*8574986*/
102 BEGIN
103
104 X_msg_count := 1;
105
106 -- set default message type to Error.
107 X_msg_type := 'E';
108
109
110 -- set default message application to Oracle Projects
111 X_msg_application := 'PA';
112
113 /* Added for bug 2991182 */
114 If (X_calling_module in ('APXINENT','apiindib.pls','apiimptb.pls','APXIIMPT')
115 and X_transaction_source is NOT NULL)
116 Then
117 l_sys_link_function := X_transaction_source;
118 /* Added ElsIf for Bug#3155151 */
119 ElsIf (X_calling_module IN ('PSPLDTRF','PAXVSSTS')) Then /* OIT */ -- removed PAXVOTCB from if clause bug 3406396
120 l_sys_link_function := nvl(p_sys_link_function, 'ST'); /* Added nvl() for Bug#3844346 */
121 ElsIf (X_calling_module = 'SelfService') Then /* OIE */
122 l_sys_link_function := 'ER';
123 ElsIf (X_calling_module in ('POXPOEPO','POXPOERL','POXRQERQ','POWEBREQ','REQIMPORT','IGCCENTR','APXRICAD')) Then /*Bug 3428967,3581050*/ /* Added IGCCENTR for 6523746 */ /*Added for bug#13595311*/
124 l_sys_link_function := 'VI';
125 Else
126 l_sys_link_function := p_sys_link_function;
127 End If;
128 /* bug 2991182 */
129 /*Bug 8574986 begin*/
130
131 /* Start: changes for CBS Enhancement: Bug 16220146 */
132 select count(*) into l_task_count
133 from pa_tasks
134 where project_id = X_project_id
135 and task_id = X_task_id;
136
137 IF l_task_count <> 0 THEN
138 l_task_id := X_task_id;
139 ELSE
140 BEGIN
141 select proj_element_id into l_task_id
142 from pa_alternate_tasks
143 where alt_task_id = X_task_id;
144 EXCEPTION
145 WHEN OTHERS THEN
146 l_task_id := X_task_id;
147 END;
148 END IF;
149
150 /* End: changes for CBS Enhancement: Bug 16220146 */
151
152 PA_TRANSACTIONS_PUB.validate_task(X_project_id=>X_project_id
153 , X_task_id=> l_task_id -- Changed for bug 16220146
154 , X_msg_data=> X_msg_data
155 , X_msg_type=> X_msg_type
156 , X_msg_token1=>X_msg_token1
157 , X_msg_count=> X_msg_count);
158
159
160
161
162 If (x_msg_count>0) then
163 RAISE INVALID_TASK;
164 end if;
165
166 /*Bug 8574986 end*/
167
168
169 PATC.get_status(
170 X_project_id => X_project_id
171 , X_task_id => l_task_id -- Changed for bug 16220146
172 , X_ei_date => X_ei_date
173 , X_expenditure_type => X_expenditure_type
174 , X_non_labor_resource => X_non_labor_resource
175 , X_person_id => X_person_id
176 , X_quantity => X_quantity
177 , X_denom_currency_code => X_denom_currency_code
178 , X_acct_currency_code => X_acct_currency_code
179 , X_denom_raw_cost => X_denom_raw_cost
180 , X_acct_raw_cost => X_acct_raw_cost
181 , X_acct_rate_type => X_acct_rate_type
182 , X_acct_rate_date => X_acct_rate_date
183 , X_acct_exchange_rate => X_acct_exchange_rate
184 , X_transfer_ei => X_transfer_ei
185 , X_incurred_by_org_id => X_incurred_by_org_id
186 , X_nl_resource_org_id => X_nl_resource_org_id
187 , X_transaction_source => X_transaction_source
188 , X_calling_module => X_calling_module
189 , X_vendor_id => X_vendor_id
190 , X_entered_by_user_id => X_entered_by_user_id
191 , X_attribute_category => X_attribute_category
192 , X_attribute1 => X_attribute1
193 , X_attribute2 => X_attribute2
194 , X_attribute3 => X_attribute3
195 , X_attribute4 => X_attribute4
196 , X_attribute5 => X_attribute5
197 , X_attribute6 => X_attribute6
198 , X_attribute7 => X_attribute7
199 , X_attribute8 => X_attribute8
200 , X_attribute9 => X_attribute9
201 , X_attribute10 => X_attribute10
202 , X_attribute11 => X_attribute11
203 , X_attribute12 => X_attribute12
204 , X_attribute13 => X_attribute13
205 , X_attribute14 => X_attribute14
206 , X_attribute15 => X_attribute15
207 , X_msg_application => X_msg_application
208 , X_msg_type => X_msg_type
209 , X_msg_token1 => X_msg_token1
210 , X_msg_token2 => X_msg_token2
211 , X_msg_token3 => X_msg_token3
212 , X_msg_count => X_msg_count
213 , X_status => X_msg_data
214 , X_billable_flag => X_billable_flag
215 , p_projfunc_currency_code => p_projfunc_currency_code
216 , p_projfunc_cost_rate_type => p_projfunc_cost_rate_type
217 , p_projfunc_cost_rate_date => p_projfunc_cost_rate_date
218 , p_projfunc_cost_exchg_rate => p_projfunc_cost_exchg_rate
219 , p_assignment_id => p_assignment_id
220 , p_work_type_id => p_work_type_id
221 , p_sys_link_function => l_sys_link_function
222 , P_Po_Header_Id => P_Po_Header_Id
223 , P_Po_Line_Id => P_Po_Line_Id
224 , P_Person_Type => P_Person_Type
225 , P_Po_Price_Type => P_Po_Price_Type -- bug 2991182
226 , P_Document_Type => P_Document_Type
227 , P_Document_Line_Type => P_Document_Line_Type
228 , P_Document_Dist_Type => P_Document_Dist_Type
229 /* 10253400: Payroll integration enhancement for 12.2.. start */
230 , P_Job_Id => P_Job_Id
231 , P_Location_id => P_Location_Id
232 , P_Pay_Element_Type_Id => P_Pay_Element_Type_Id
233 , P_payroll_accrual_flag => P_payroll_accrual_flag ---- Added for payroll billable_flag changes for 12.2 bug#12344689
234 /* 10253400: Payroll integration enhancement for 12.2.. end */
235 , P_pa_ref_num1 => P_pa_ref_num1
236 , P_pa_ref_num2 => P_pa_ref_num2
237 , P_pa_ref_num3 => P_pa_ref_num3
238 , P_pa_ref_num4 => P_pa_ref_num4
239 , P_pa_ref_num5 => P_pa_ref_num5
240 , P_pa_ref_num6 => P_pa_ref_num6
241 , P_pa_ref_num7 => P_pa_ref_num7
242 , P_pa_ref_num8 => P_pa_ref_num8
243 , P_pa_ref_num9 => P_pa_ref_num9
244 , P_pa_ref_num10 => P_pa_ref_num10
245 , P_pa_ref_var1 => P_pa_ref_var1
246 , P_pa_ref_var2 => P_pa_ref_var2
247 , P_pa_ref_var3 => P_pa_ref_var3
248 , P_pa_ref_var4 => P_pa_ref_var4
249 , P_pa_ref_var5 => P_pa_ref_var5
250 , P_pa_ref_var6 => P_pa_ref_var6
251 , P_pa_ref_var7 => P_pa_ref_var7
252 , P_pa_ref_var8 => P_pa_ref_var8
253 , P_pa_ref_var9 => P_pa_ref_var9
254 , P_pa_ref_var10 => P_pa_ref_var10);
255
256 EXCEPTION
257 /*Bug 8574986 begin*/
258 WHEN INVALID_TASK THEN
259 null;
260 /*Bug 8574986 end*/
261 WHEN OTHERS THEN
262 X_msg_data := SQLCODE;
263 X_billable_flag := null;
264 END validate_transaction;
265
266
267
268 -- =====================================================================
269 -- DFF Upgrade ---------------------------------------------------------
270
271 -- API: pop_dff_segments_enabled_table
272 -- Purpose: This procedure is called by validate_dff API to populate the
273 -- global variable G_dff_segments_enabled_table.
274 -- G_dff_segments_enabled_table is a variable which keeps track
275 -- of which context code has which segments enabled. We are keeping
276 -- these information in a table in order to avoid calling ATG's
277 -- DFF API's extraneously.
278
279 PROCEDURE pop_dff_segments_enabled_table IS
280 flex fnd_dflex.dflex_r;
281 flex_detail fnd_dflex.dflex_dr;
282 context_detail fnd_dflex.contexts_dr;
283 context fnd_dflex.context_r;
284 segment_detail fnd_dflex.segments_dr;
285 i NUMBER;
286 BEGIN
287
288 -- Need to get flexfield token before getting context token
289 fnd_dflex.get_flexfield('PA', 'PA_EXPENDITURE_ITEMS_DESC_FLEX', flex, flex_detail);
290
291 -- Need to get context token before getting segment token
292 fnd_dflex.get_contexts(flex, context_detail);
293
294 context.flexfield := flex;
295 i := 1;
296 -- After getting the information for this descriptive flexfield, we will
297 -- loop through each context codes to find out which segments are enabled
298 -- for each context code
299 WHILE i <= context_detail.ncontexts LOOP
300 IF context_detail.is_enabled(i) THEN
301 context.context_code := context_detail.context_code(i);
302 fnd_dflex.get_segments(context, segment_detail);
303
304 -- Store the context code
305 G_dff_segments_enabled_table(i).context_code := context_detail.context_code(i);
306 G_dff_segments_enabled_table(i).context_name := context_detail.context_name(i);
307
308 -- Store whether this context code is GLOBAL context code or not
312 populate_segments(
309 G_dff_segments_enabled_table(i).is_global := context_detail.is_global(i);
310
311 -- Call populate_segments to store the enabled segments for this context code
313 p_segment_detail => segment_detail,
314 p_dff_segments_enabled_record => G_dff_segments_enabled_table(i));
315 ELSE
316 G_dff_segments_enabled_table(i).context_code := null; -- Added for bug 1457298
317 END IF;
318 i := i + 1;
319 END LOOP;
320 END pop_dff_segments_enabled_table;
321
322
323 -- API: populate_segments
324 -- Purpose: This procedure is called by pop_dff_segments_enabled_table.
325 -- It is called once for each context code in the descriptive flexfield
326 -- definition. For a particular context code, this API populate
327 -- one record of G_dff_segments_enabled_table table to store the enabled
328 -- segments of this context code
329
330 PROCEDURE populate_segments (
331 p_segment_detail IN fnd_dflex.segments_dr,
332 /* Start of bug# 2672653 */
333 -- p_dff_segments_enabled_record IN OUT dff_segments_enabled_record) IS
334 p_dff_segments_enabled_record IN OUT NOCOPY dff_segments_enabled_record) IS /* added NOCOPY */
335 /* End of bug# 2672653 */
336 counter NUMBER;
337 BEGIN
338 counter := 1;
339
340 -- Loop through each segment of the context code to see what is the database
341 -- table column name of this segment. It must be ATTRIBUTE1-10. If yes and the
342 -- segment is enabled, it will set the attributex_enabled field of the
343 -- G_dff_segments_enabled_table record to TRUE, otherwise to FALSE
344 WHILE (counter <= p_segment_detail.nsegments) LOOP
345 IF p_segment_detail.application_column_name(counter) = 'ATTRIBUTE1' THEN
346 IF p_segment_detail.is_enabled(counter) THEN
347 p_dff_segments_enabled_record.attribute1_enabled := TRUE;
348 ELSE
349 p_dff_segments_enabled_record.attribute1_enabled := FALSE;
350 END IF;
351 ELSIF p_segment_detail.application_column_name(counter) = 'ATTRIBUTE2' THEN
352 IF p_segment_detail.is_enabled(counter) THEN
353 p_dff_segments_enabled_record.attribute2_enabled := TRUE;
354 ELSE
355 p_dff_segments_enabled_record.attribute2_enabled := FALSE;
356 END IF;
357 ELSIF p_segment_detail.application_column_name(counter) = 'ATTRIBUTE3' THEN
358 IF p_segment_detail.is_enabled(counter) THEN
359 p_dff_segments_enabled_record.attribute3_enabled := TRUE;
360 ELSE
361 p_dff_segments_enabled_record.attribute3_enabled := FALSE;
362 END IF;
363 ELSIF p_segment_detail.application_column_name(counter) = 'ATTRIBUTE4' THEN
364 IF p_segment_detail.is_enabled(counter) THEN
365 p_dff_segments_enabled_record.attribute4_enabled := TRUE;
366 ELSE
367 p_dff_segments_enabled_record.attribute4_enabled := FALSE;
368 END IF;
369 ELSIF p_segment_detail.application_column_name(counter) = 'ATTRIBUTE5' THEN
370 IF p_segment_detail.is_enabled(counter) THEN
371 p_dff_segments_enabled_record.attribute5_enabled := TRUE;
372 ELSE
373 p_dff_segments_enabled_record.attribute5_enabled := FALSE;
374 END IF;
375 ELSIF p_segment_detail.application_column_name(counter) = 'ATTRIBUTE6' THEN
376 IF p_segment_detail.is_enabled(counter) THEN
377 p_dff_segments_enabled_record.attribute6_enabled := TRUE;
378 ELSE
379 p_dff_segments_enabled_record.attribute6_enabled := FALSE;
380 END IF;
381 ELSIF p_segment_detail.application_column_name(counter) = 'ATTRIBUTE7' THEN
382 IF p_segment_detail.is_enabled(counter) THEN
383 p_dff_segments_enabled_record.attribute7_enabled := TRUE;
384 ELSE
385 p_dff_segments_enabled_record.attribute7_enabled := FALSE;
386 END IF;
390 ELSE
387 ELSIF p_segment_detail.application_column_name(counter) = 'ATTRIBUTE8' THEN
388 IF p_segment_detail.is_enabled(counter) THEN
389 p_dff_segments_enabled_record.attribute8_enabled := TRUE;
391 p_dff_segments_enabled_record.attribute8_enabled := FALSE;
392 END IF;
393 ELSIF p_segment_detail.application_column_name(counter) = 'ATTRIBUTE9' THEN
394 IF p_segment_detail.is_enabled(counter) THEN
395 p_dff_segments_enabled_record.attribute9_enabled := TRUE;
396 ELSE
400 IF p_segment_detail.is_enabled(counter) THEN
397 p_dff_segments_enabled_record.attribute9_enabled := FALSE;
398 END IF;
399 ELSIF p_segment_detail.application_column_name(counter) = 'ATTRIBUTE10' THEN
401 p_dff_segments_enabled_record.attribute10_enabled := TRUE;
402 ELSE
403 p_dff_segments_enabled_record.attribute10_enabled := FALSE;
404 END IF;
405 END IF;
406 counter := counter + 1;
407 END LOOP;
408 END populate_segments;
409
410
411 -- API: validate_dff
412 -- Purpose: This is the main API for validating DFF. Given all ten segment fields,
413 -- this API will validate them against descriptive flexfield definition.
414 -- Underneath, the real validation is done by API's provided by ATG.
415 -- Once passed validation, this API also makes sure only the enabled segments
416 -- are passed back through the IN/OUT p_attributex parameters
417 PROCEDURE validate_dff (
418 p_dff_name IN fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE,
419 p_attribute_category IN pa_expenditure_items_all.attribute_category%TYPE,
420 p_attribute1 IN OUT NOCOPY pa_expenditure_items_all.attribute1%TYPE,
421 p_attribute2 IN OUT NOCOPY pa_expenditure_items_all.attribute2%TYPE,
422 p_attribute3 IN OUT NOCOPY pa_expenditure_items_all.attribute3%TYPE,
423 p_attribute4 IN OUT NOCOPY pa_expenditure_items_all.attribute4%TYPE,
424 p_attribute5 IN OUT NOCOPY pa_expenditure_items_all.attribute5%TYPE,
425 p_attribute6 IN OUT NOCOPY pa_expenditure_items_all.attribute6%TYPE,
426 p_attribute7 IN OUT NOCOPY pa_expenditure_items_all.attribute7%TYPE,
427 p_attribute8 IN OUT NOCOPY pa_expenditure_items_all.attribute8%TYPE,
428 p_attribute9 IN OUT NOCOPY pa_expenditure_items_all.attribute9%TYPE,
429 p_attribute10 IN OUT NOCOPY pa_expenditure_items_all.attribute10%TYPE,
430 x_status_code OUT NOCOPY VARCHAR2,
431 x_error_message OUT NOCOPY VARCHAR2)
432
433 IS
434 v_attribute1 pa_expenditure_items_all.attribute1%TYPE;
435 v_attribute2 pa_expenditure_items_all.attribute2%TYPE;
436 v_attribute3 pa_expenditure_items_all.attribute3%TYPE;
437 v_attribute4 pa_expenditure_items_all.attribute4%TYPE;
438 v_attribute5 pa_expenditure_items_all.attribute5%TYPE;
439 v_attribute6 pa_expenditure_items_all.attribute6%TYPE;
440 v_attribute7 pa_expenditure_items_all.attribute7%TYPE;
441 v_attribute8 pa_expenditure_items_all.attribute8%TYPE;
442 v_attribute9 pa_expenditure_items_all.attribute9%TYPE;
443 v_attribute10 pa_expenditure_items_all.attribute10%TYPE;
444 v_desc_flex_context_name fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE;
445 i NUMBER;
446 /* Start of Bug 3064318 */
447 TYPE seg_col_name is TABLE of varchar2(150)
448 Index by binary_integer;
449
450 p_segment_column_name seg_col_name;
451 l_attribute seg_col_name;
452 j NUMBER;
453 /* End of Bug 3064318 */
454
455 -- API: populate_v_attributes
456 -- Purpose: This procedure is part of the body of populate_dff. It is called by
457 -- validate_dff to check against the global table G_dff_segments_enabled_table
458 -- to see, for the given context code, what segments are enabled. If
459 -- a segment is enabled for this context code, it will then populate the
460 -- v_attributex variable with the IN/OUT parameter p_attributex. If the segment
461 -- is not enabled, then v_attributex will be left as NULL. This is the API
462 -- which filters out all the extra attributes the user has passed in for segments
463 -- which are not enabled. v_attributex variables are later used in the DFF validation
464 -- process
465
466 PROCEDURE populate_v_attributes IS
467 c NUMBER;
468 BEGIN
469 c := 1;
470 -- Loop through each record of the G_dff_segments_enabled_table table.
471 -- Check the attribute_category passed in as parameter against the context code
472 -- of each record. If equals, then we have found the record we want to retrieve.
473 -- We also always retrive the 'Global Data Elements' context code because it stores
474 -- the global segments
475 WHILE c <= G_dff_segments_enabled_table.count LOOP
476 IF (G_dff_segments_enabled_table(c).is_global OR
477 G_dff_segments_enabled_table(c).context_code = p_attribute_category) THEN
478 IF (G_dff_segments_enabled_table(c).context_code =
479 p_attribute_category) THEN
480 /* Commented for Bug # 1982950 -- gjain
481 v_desc_flex_context_name :=
482 G_dff_segments_enabled_table(c).context_name;
483 */
484 v_desc_flex_context_name :=
485 G_dff_segments_enabled_table(c).context_code;
486 END IF;
487
488 IF G_dff_segments_enabled_table(c).attribute1_enabled THEN
489 v_attribute1 := p_attribute1;
490 END IF;
491 IF G_dff_segments_enabled_table(c).attribute2_enabled THEN
492 v_attribute2 := p_attribute2;
493 END IF;
494 IF G_dff_segments_enabled_table(c).attribute3_enabled THEN
495 v_attribute3 := p_attribute3;
496 END IF;
497 IF G_dff_segments_enabled_table(c).attribute4_enabled THEN
498 v_attribute4 := p_attribute4;
499 END IF;
500 IF G_dff_segments_enabled_table(c).attribute5_enabled THEN
501 v_attribute5 := p_attribute5;
502 END IF;
503 IF G_dff_segments_enabled_table(c).attribute6_enabled THEN
504 v_attribute6 := p_attribute6;
505 END IF;
509 IF G_dff_segments_enabled_table(c).attribute8_enabled THEN
506 IF G_dff_segments_enabled_table(c).attribute7_enabled THEN
507 v_attribute7 := p_attribute7;
508 END IF;
510 v_attribute8 := p_attribute8;
511 END IF;
512 IF G_dff_segments_enabled_table(c).attribute9_enabled THEN
513 v_attribute9 := p_attribute9;
514 END IF;
515 IF G_dff_segments_enabled_table(c).attribute10_enabled THEN
516 v_attribute10 := p_attribute10;
517 END IF;
518 END IF;
519 c := c + 1;
520 END LOOP;
521 END populate_v_attributes;
522
523 -- Validate_dff logic begins
524 BEGIN
525 x_error_message := NULL;
526 x_status_code := NULL;
527 v_attribute1 := NULL;
528 v_attribute2 := NULL;
529 v_attribute3 := NULL;
530 v_attribute4 := NULL;
531 v_attribute5 := NULL;
532 v_attribute6 := NULL;
533 v_attribute7 := NULL;
534 v_attribute8 := NULL;
535 v_attribute9 := NULL;
536 v_attribute10 := NULL;
537 i := 1;
538
539 --Initialize G_dff_segments_enabled_table if it hasn't been initialized
540 IF (G_dff_segments_enabled_table.count = 0) THEN
541 pop_dff_segments_enabled_table();
542 END IF;
543
544 -- Populate the v_attributex variables to get ready to do validation
545 populate_v_attributes();
546
547 -- Validate the DFF
548 fnd_flex_descval.set_context_value(v_desc_flex_context_name);
549 fnd_flex_descval.set_column_value('ATTRIBUTE1', v_attribute1);
550 fnd_flex_descval.set_column_value('ATTRIBUTE2', v_attribute2);
551 fnd_flex_descval.set_column_value('ATTRIBUTE3', v_attribute3);
552 fnd_flex_descval.set_column_value('ATTRIBUTE4', v_attribute4);
553 fnd_flex_descval.set_column_value('ATTRIBUTE5', v_attribute5);
554 fnd_flex_descval.set_column_value('ATTRIBUTE6', v_attribute6);
555 fnd_flex_descval.set_column_value('ATTRIBUTE7', v_attribute7);
556 fnd_flex_descval.set_column_value('ATTRIBUTE8', v_attribute8);
557 fnd_flex_descval.set_column_value('ATTRIBUTE9', v_attribute9);
558 fnd_flex_descval.set_column_value('ATTRIBUTE10', v_attribute10);
559
560 /* Start of Bug 3064318 */
561 IF (FND_FLEX_DESCVAL.validate_desccols('PA', 'PA_EXPENDITURE_ITEMS_DESC_FLEX', 'D', sysdate)) THEN
562
563 for j in 1 ..10 Loop
564 p_segment_column_name(j) := ltrim(rtrim(FND_FLEX_DESCVAL.segment_column_name(j)));
565 l_attribute(j) := rtrim(FND_FLEX_DESCVAL.segment_id(j)); --Bug#6506638: Removed ltrim()
566
567 If p_segment_column_name(j) = 'ATTRIBUTE1' Then
568 p_attribute1 := l_attribute(j);
569 ElsIf p_segment_column_name(j) = 'ATTRIBUTE2' Then
570 p_attribute2 := l_attribute(j);
571 ElsIf p_segment_column_name(j) = 'ATTRIBUTE3' Then
572 p_attribute3 := l_attribute(j);
573 ElsIf p_segment_column_name(j) = 'ATTRIBUTE4' Then
574 p_attribute4 := l_attribute(j);
575 ElsIf p_segment_column_name(j) = 'ATTRIBUTE5' Then
576 p_attribute5 := l_attribute(j);
577 ElsIf p_segment_column_name(j) = 'ATTRIBUTE6' Then
578 p_attribute6 := l_attribute(j);
579 ElsIf p_segment_column_name(j) = 'ATTRIBUTE7' Then
580 p_attribute7 := l_attribute(j);
581 ElsIf p_segment_column_name(j) = 'ATTRIBUTE8' Then
582 p_attribute8 := l_attribute(j);
583 ElsIf p_segment_column_name(j) = 'ATTRIBUTE9' Then
584 p_attribute9 := l_attribute(j);
585 ElsIf p_segment_column_name(j) = 'ATTRIBUTE10' Then
586 p_attribute10 := l_attribute(j);
587 End If;
588 End Loop;
589
590 /* p_attribute1 := v_attribute1;
591 p_attribute2 := v_attribute2;
592 p_attribute3 := v_attribute3;
593 p_attribute4 := v_attribute4;
594 p_attribute5 := v_attribute5;
595 p_attribute6 := v_attribute6;
596 p_attribute7 := v_attribute7;
597 p_attribute8 := v_attribute8;
598 p_attribute9 := v_attribute9;
599 p_attribute10 := v_attribute10; Commented for Bug 3064318 */
600 /* End of Bug 3064318 */
601 ELSE
602 X_error_message := FND_FLEX_DESCVAL.error_message;
603 X_status_code := 'PA_DFF_VALIDATION_FAILED';
604 RAISE INVALID_DATA;
605 END IF;
606
607 EXCEPTION
608 WHEN INVALID_DATA THEN
609 null;
610 WHEN OTHERS THEN
611 raise;
612
613 END validate_dff;
614
615 ----------------------------------------------------------------------
616 -- Please refer to package spec for detailed description of the
617 -- procedure.
618 ----------------------------------------------------------------------
619
620 PROCEDURE Check_Adjustment_of_Proj_Txn(
621 x_transaction_source IN VARCHAR2,
622 x_orig_transaction_reference IN VARCHAR2,
623 x_expenditure_type_class IN VARCHAR2,
624 x_expenditure_type IN VARCHAR2,
625 x_expenditure_item_id IN NUMBER DEFAULT NULL,
626 x_expenditure_item_date IN DATE,
627 x_employee_number IN VARCHAR2 DEFAULT NULL,
628 x_expenditure_org_name IN VARCHAR2 DEFAULT NULL,
629 x_project_number IN VARCHAR2,
630 x_task_number IN VARCHAR2,
631 x_non_labor_resource IN VARCHAR2 DEFAULT NULL,
632 x_non_labor_resource_org_name IN VARCHAR2 DEFAULT NULL,
633 x_quantity IN NUMBER,
634 x_raw_cost IN NUMBER DEFAULT NULL,
635 x_attribute_category IN VARCHAR2 DEFAULT NULL,
636 x_attribute1 IN VARCHAR2 DEFAULT NULL,
640 x_attribute5 IN VARCHAR2 DEFAULT NULL,
637 x_attribute2 IN VARCHAR2 DEFAULT NULL,
638 x_attribute3 IN VARCHAR2 DEFAULT NULL,
639 x_attribute4 IN VARCHAR2 DEFAULT NULL,
641 x_attribute6 IN VARCHAR2 DEFAULT NULL,
642 x_attribute7 IN VARCHAR2 DEFAULT NULL,
643 x_attribute8 IN VARCHAR2 DEFAULT NULL,
644 x_attribute9 IN VARCHAR2 DEFAULT NULL,
645 x_attribute10 IN VARCHAR2 DEFAULT NULL,
649 x_return_status OUT NOCOPY VARCHAR2,
646 x_org_id IN NUMBER DEFAULT NULL,
647 x_adjustment_status OUT NOCOPY VARCHAR2,
648 x_adjustment_status_code OUT NOCOPY VARCHAR2,
650 x_message_data OUT NOCOPY VARCHAR2)
651 IS
652
653 --The following Cursor will select the item in Projects based upon the parameters passed
654 --to the API. It is a UNION because
655 -- 1) pa_expenditure_items_all.non_labor_resource and pa_expenditure_items_all.organization_id
656 -- (non labor resource owning organization) only exist for Usages (expenditure_type_class = 'USG')
657 -- 2) pa_expenditures_all.incurred_by_person_id may or may not exist for Usages, and
658 -- does not exist for Supplier Invoices.
659
660 CURSOR item_in_projects IS
661 --The first part of the UNION handles all expenditure_type_classes EXCEPT
662 --Usages (USG) and Supplier Invoices (VI).
663 SELECT net_zero_adjustment_flag
664 FROM pa_expenditure_items_all ei,
665 pa_expenditures_all exp,
666 per_all_people_f per,
667 pa_projects_all proj,
668 pa_tasks task,
669 hr_all_organization_units hr1
670 WHERE ei.transaction_source = x_transaction_source AND
671 ei.orig_transaction_reference = x_orig_transaction_reference AND
672 ei.system_linkage_function = x_expenditure_type_class AND
673 ei.expenditure_type = x_expenditure_type AND
674 ei.expenditure_item_id = nvl(x_expenditure_item_id, ei.expenditure_item_id) AND
675 ei.expenditure_item_date = x_expenditure_item_date AND
676 NVL(per.employee_number,per.npw_number) = nvl(x_employee_number, nvl(per.employee_number,per.npw_number) ) AND /* FP.M / CWK Changes */
677 ei.expenditure_item_date BETWEEN per.effective_start_date AND per.effective_end_date AND
678 per.person_id = exp.incurred_by_person_id AND
679 exp.expenditure_id = ei.expenditure_id AND
680 hr1.name = nvl(x_expenditure_org_name,hr1.name) AND
681 hr1.organization_id = exp.incurred_by_organization_id AND
682 proj.segment1 = x_project_number AND
683 task.project_id = proj.project_id AND
684 task.task_number = x_task_number AND
685 ei.task_id = task.task_id AND
686 ei.quantity = x_quantity AND
687 nvl(ei.raw_cost,'-99') = nvl(x_raw_cost,nvl(ei.raw_cost,'-99')) AND
688 nvl(ei.attribute_category,'-99') = nvl(x_attribute_category,nvl(ei.attribute_category,'-99')) AND
689 nvl(ei.attribute1,'-99') = nvl(x_attribute1, nvl(ei.attribute1,'-99')) AND
690 nvl(ei.attribute2,'-99') = nvl(x_attribute2, nvl(ei.attribute2,'-99')) AND
691 nvl(ei.attribute3,'-99') = nvl(x_attribute3, nvl(ei.attribute3,'-99')) AND
692 nvl(ei.attribute4,'-99') = nvl(x_attribute4, nvl(ei.attribute4,'-99')) AND
693 nvl(ei.attribute5,'-99') = nvl(x_attribute5, nvl(ei.attribute5,'-99')) AND
694 nvl(ei.attribute6,'-99') = nvl(x_attribute6, nvl(ei.attribute6,'-99')) AND
695 nvl(ei.attribute7,'-99') = nvl(x_attribute7, nvl(ei.attribute7,'-99')) AND
696 nvl(ei.attribute8,'-99') = nvl(x_attribute8, nvl(ei.attribute8,'-99')) AND
697 nvl(ei.attribute9,'-99') = nvl(x_attribute9, nvl(ei.attribute9,'-99')) AND
698 nvl(ei.attribute10,'-99') = nvl(x_attribute10, nvl(ei.attribute10,'-99')) AND
699 nvl(ei.org_id,'-99') = nvl(x_org_id,nvl(ei.org_id,'-99')) AND
700 x_expenditure_type_class <> 'USG' AND
701 x_expenditure_type_class <> 'VI'
702 UNION ALL
703 --The second part of the UNION handles Usages (expenditure_type_class='USG')
704 --when IN parameter x_employee_number is NOT NULL. The additional join to
705 --hr_all_organization_units hr2 is required.
706 SELECT net_zero_adjustment_flag
707 FROM pa_expenditure_items_all ei,
708 pa_expenditures_all exp,
709 per_all_people_f per,
710 pa_projects_all proj,
711 pa_tasks task,
712 hr_all_organization_units hr1,
713 hr_all_organization_units hr2
714 WHERE ei.transaction_source = x_transaction_source AND
715 ei.orig_transaction_reference = x_orig_transaction_reference AND
716 ei.system_linkage_function = x_expenditure_type_class AND
717 ei.expenditure_type = x_expenditure_type AND
718 ei.expenditure_item_id = nvl(x_expenditure_item_id, ei.expenditure_item_id) AND
719 ei.expenditure_item_date = x_expenditure_item_date AND
720 nvl(per.employee_number,per.npw_number) = x_employee_number AND /* FP.M / CWK Changes */
721 ei.expenditure_item_date BETWEEN per.effective_start_date AND per.effective_end_date AND
722 per.person_id = exp.incurred_by_person_id AND
723 exp.expenditure_id = ei.expenditure_id AND
724 hr1.name = nvl(x_expenditure_org_name,hr1.name) AND
725 hr1.organization_id = exp.incurred_by_organization_id AND
726 proj.segment1 = x_project_number AND
727 task.project_id = proj.project_id AND
728 task.task_number = x_task_number AND
729 ei.task_id = task.task_id AND
730 nvl(ei.non_labor_resource,'-99') = nvl(x_non_labor_resource,nvl(ei.non_labor_resource,'-99')) AND
731 hr2.name = nvl(x_non_labor_resource_org_name,hr2.name) AND
732 hr2.organization_id = ei.organization_id AND
733 ei.quantity = x_quantity AND
734 nvl(ei.raw_cost,'-99') = nvl(x_raw_cost,nvl(ei.raw_cost,'-99')) AND
735 nvl(ei.attribute_category,'-99') = nvl(x_attribute_category,nvl(ei.attribute_category,'-99')) AND
736 nvl(ei.attribute1,'-99') = nvl(x_attribute1, nvl(ei.attribute1,'-99')) AND
740 nvl(ei.attribute5,'-99') = nvl(x_attribute5, nvl(ei.attribute5,'-99')) AND
737 nvl(ei.attribute2,'-99') = nvl(x_attribute2, nvl(ei.attribute2,'-99')) AND
738 nvl(ei.attribute3,'-99') = nvl(x_attribute3, nvl(ei.attribute3,'-99')) AND
739 nvl(ei.attribute4,'-99') = nvl(x_attribute4, nvl(ei.attribute4,'-99')) AND
741 nvl(ei.attribute6,'-99') = nvl(x_attribute6, nvl(ei.attribute6,'-99')) AND
742 nvl(ei.attribute7,'-99') = nvl(x_attribute7, nvl(ei.attribute7,'-99')) AND
743 nvl(ei.attribute8,'-99') = nvl(x_attribute8, nvl(ei.attribute8,'-99')) AND
744 nvl(ei.attribute9,'-99') = nvl(x_attribute9, nvl(ei.attribute9,'-99')) AND
745 nvl(ei.attribute10,'-99') = nvl(x_attribute10, nvl(ei.attribute10,'-99')) AND
746 nvl(ei.org_id,'-99') = nvl(x_org_id,nvl(ei.org_id,'-99')) AND
747 x_expenditure_type_class = 'USG' AND
748 x_employee_number IS NOT NULL
749 UNION ALL
750 --The third part of the UNION handles Usages (expenditure_type_class='USG')
751 --when IN PARAMETER x_exployee_number IS NULL. The additional join to
752 --hr_all_organization_units hr2 is required and the join to per_all_people_f is not required.
753 SELECT net_zero_adjustment_flag
754 FROM pa_expenditure_items_all ei,
755 pa_expenditures_all exp,
756 pa_projects_all proj,
757 pa_tasks task,
758 hr_all_organization_units hr1,
759 hr_all_organization_units hr2
760 WHERE ei.transaction_source = x_transaction_source AND
761 ei.orig_transaction_reference = x_orig_transaction_reference AND
762 ei.system_linkage_function = x_expenditure_type_class AND
763 ei.expenditure_type = x_expenditure_type AND
764 ei.expenditure_item_id = nvl(x_expenditure_item_id, ei.expenditure_item_id) AND
765 ei.expenditure_item_date = x_expenditure_item_date AND
766 exp.expenditure_id = ei.expenditure_id AND
767 hr1.name = nvl(x_expenditure_org_name,hr1.name) AND
768 hr1.organization_id = exp.incurred_by_organization_id AND
769 proj.segment1 = x_project_number AND
770 task.project_id = proj.project_id AND
771 task.task_number = x_task_number AND
772 ei.task_id = task.task_id AND
773 nvl(ei.non_labor_resource,'-99') = nvl(x_non_labor_resource,nvl(ei.non_labor_resource,'-99')) AND
774 hr2.name = nvl(x_non_labor_resource_org_name,hr2.name) AND
775 hr2.organization_id = ei.organization_id AND
776 ei.quantity = x_quantity AND
777 nvl(ei.raw_cost,'-99') = nvl(x_raw_cost,nvl(ei.raw_cost,'-99')) AND
778 nvl(ei.attribute_category,'-99') = nvl(x_attribute_category,nvl(ei.attribute_category,'-99')) AND
779 nvl(ei.attribute1,'-99') = nvl(x_attribute1, nvl(ei.attribute1,'-99')) AND
780 nvl(ei.attribute2,'-99') = nvl(x_attribute2, nvl(ei.attribute2,'-99')) AND
781 nvl(ei.attribute3,'-99') = nvl(x_attribute3, nvl(ei.attribute3,'-99')) AND
782 nvl(ei.attribute4,'-99') = nvl(x_attribute4, nvl(ei.attribute4,'-99')) AND
783 nvl(ei.attribute5,'-99') = nvl(x_attribute5, nvl(ei.attribute5,'-99')) AND
784 nvl(ei.attribute6,'-99') = nvl(x_attribute6, nvl(ei.attribute6,'-99')) AND
785 nvl(ei.attribute7,'-99') = nvl(x_attribute7, nvl(ei.attribute7,'-99')) AND
786 nvl(ei.attribute8,'-99') = nvl(x_attribute8, nvl(ei.attribute8,'-99')) AND
787 nvl(ei.attribute9,'-99') = nvl(x_attribute9, nvl(ei.attribute9,'-99')) AND
788 nvl(ei.attribute10,'-99') = nvl(x_attribute10, nvl(ei.attribute10,'-99')) AND
789 nvl(ei.org_id,'-99') = nvl(x_org_id,nvl(ei.org_id,'-99')) AND
790 x_expenditure_type_class = 'USG' AND
791 x_employee_number IS NULL
792 UNION ALL
793 --The fourth part of the UNION handles Supplier Invoices (expenditure_type_class='VI').
797 FROM pa_expenditure_items_all ei,
794 --The join to per_all_people_f is not required as pa_expenditures_all.incurred_by_person_id
795 --is NULL for Supplier Invoices.
796 SELECT net_zero_adjustment_flag
798 pa_expenditures_all exp,
799 pa_projects_all proj,
800 pa_tasks task,
801 hr_all_organization_units hr1
802 WHERE ei.transaction_source = x_transaction_source AND
803 ei.orig_transaction_reference = x_orig_transaction_reference AND
804 ei.system_linkage_function = x_expenditure_type_class AND
805 ei.expenditure_type = x_expenditure_type AND
806 ei.expenditure_item_id = nvl(x_expenditure_item_id, ei.expenditure_item_id) AND
807 ei.expenditure_item_date = x_expenditure_item_date AND
808 exp.expenditure_id = ei.expenditure_id AND
809 hr1.name = nvl(x_expenditure_org_name,hr1.name) AND
810 hr1.organization_id = ei.override_to_organization_id AND
811 proj.segment1 = x_project_number AND
812 task.project_id = proj.project_id AND
813 task.task_number = x_task_number AND
814 ei.task_id = task.task_id AND
815 ei.quantity = x_quantity AND
816 nvl(ei.raw_cost,'-99') = nvl(x_raw_cost,nvl(ei.raw_cost,'-99')) AND
817 nvl(ei.attribute_category,'-99') = nvl(x_attribute_category,nvl(ei.attribute_category,'-99')) AND
818 nvl(ei.attribute1,'-99') = nvl(x_attribute1, nvl(ei.attribute1,'-99')) AND
819 nvl(ei.attribute2,'-99') = nvl(x_attribute2, nvl(ei.attribute2,'-99')) AND
820 nvl(ei.attribute3,'-99') = nvl(x_attribute3, nvl(ei.attribute3,'-99')) AND
821 nvl(ei.attribute4,'-99') = nvl(x_attribute4, nvl(ei.attribute4,'-99')) AND
822 nvl(ei.attribute5,'-99') = nvl(x_attribute5, nvl(ei.attribute5,'-99')) AND
823 nvl(ei.attribute6,'-99') = nvl(x_attribute6, nvl(ei.attribute6,'-99')) AND
824 nvl(ei.attribute7,'-99') = nvl(x_attribute7, nvl(ei.attribute7,'-99')) AND
825 nvl(ei.attribute8,'-99') = nvl(x_attribute8, nvl(ei.attribute8,'-99')) AND
826 nvl(ei.attribute9,'-99') = nvl(x_attribute9, nvl(ei.attribute9,'-99')) AND
827 nvl(ei.attribute10,'-99') = nvl(x_attribute10, nvl(ei.attribute10,'-99')) AND
828 nvl(ei.org_id,'-99') = nvl(x_org_id,nvl(ei.org_id,'-99')) AND
829 x_expenditure_type_class = 'VI';
830
831 --The following Cursor selects the meaning for the adjustment_status_code
832 --from pa_lookups.
833
834 CURSOR adjustment_status_meaning IS
835 SELECT meaning
836 FROM pa_lookups
837 WHERE lookup_type = 'PA_ADJUSTMENT_STATUS'
838 AND lookup_code = x_adjustment_status_code;
839
840 l_net_zero_adjustment_flag pa_expenditure_items_all.net_zero_adjustment_flag%TYPE;
841 e_no_unique_transaction exception;
842
843
844 BEGIN
845
846 OPEN item_in_projects;
847
848 LOOP
849
850 FETCH item_in_projects INTO l_net_zero_adjustment_flag;
851
852 --If no rows are selected by the cursor then the item cannot be found in Projects.
853
854 IF item_in_projects%ROWCOUNT = 0 THEN
855
856 x_adjustment_status_code := 'NF';
857
858 x_return_status := 'S';
859
860 OPEN adjustment_status_meaning;
861
862 FETCH adjustment_status_meaning INTO x_adjustment_status;
863
864 CLOSE adjustment_status_meaning;
865
866 RETURN;
867
868 END IF;
869
870 --Exit the loop when there are no more records in the cursor.
871
872 EXIT WHEN item_in_projects%NOTFOUND;
873
874 --If more than one record in the cursor then the transaction was not
875 --uniquely identified in Projects based on the parameters passed to the API.
876
877 IF item_in_projects%ROWCOUNT > 1 THEN
878
879 RAISE e_no_unique_transaction;
880
881 END IF;
882
883 --If the net_zero_adjustment_flag of the item in Projects = 'Y' then
884 --the item has been adjusted in Projects.
885
889
886 IF (l_net_zero_adjustment_flag = 'Y') THEN
887
888 x_adjustment_status_code := 'A';
890 x_return_status := 'S';
891
892 OPEN adjustment_status_meaning;
893
894 FETCH adjustment_status_meaning INTO x_adjustment_status;
895
896 CLOSE adjustment_status_meaning;
897
898 END IF;
899
900 --If the net_zero_adjustment_flag of the item in Projects = 'N' or NULL then
901 --the item has not been adjusted in Projects.
902
903 IF (nvl(l_net_zero_adjustment_flag,'N') = 'N') THEN
904
905 x_adjustment_status_code := 'NA';
906
907 x_return_status := 'S';
908
909 OPEN adjustment_status_meaning;
910
911 FETCH adjustment_status_meaning INTO x_adjustment_status;
912
913 CLOSE adjustment_status_meaning;
914
915 END IF;
916
917 END LOOP;
918
919 EXCEPTION
920
921 WHEN e_no_unique_transaction THEN
922
923 x_adjustment_status := NULL;
924
925 x_adjustment_status_code := NULL;
926
927 x_return_status := 'E';
928
929 FND_MSG_PUB.initialize;
930
931 FND_MESSAGE.SET_NAME('PA','PA_NO_UNIQUE_TRANSACTION');
932
933 x_message_data := FND_MESSAGE.GET;
934
935 WHEN others THEN
936
937 x_adjustment_status := NULL;
938
939 x_adjustment_status_code := NULL;
940
941 x_return_status := 'U';
942
943 x_message_data := substrb(SQLERRM,1,2000);
944
945 END Check_Adjustment_of_Proj_Txn;
946
947
948 PROCEDURE Allow_Adjustment_Extn(
949 p_transaction_source IN VARCHAR2,
950 p_allow_adjustment_flag IN VARCHAR2,
951 p_orig_transaction_reference IN VARCHAR2,
952 p_expenditure_type_class IN VARCHAR2,
953 p_expenditure_type IN VARCHAR2,
954 p_expenditure_item_id IN NUMBER,
955 p_expenditure_item_date IN DATE,
956 p_employee_number IN VARCHAR2,
957 p_expenditure_org_name IN VARCHAR2,
958 p_project_number IN VARCHAR2,
959 p_task_number IN VARCHAR2,
960 p_non_labor_resource IN VARCHAR2,
961 p_non_labor_resource_org_name IN VARCHAR2,
962 p_quantity IN NUMBER,
963 p_raw_cost IN NUMBER,
964 p_attribute_category IN VARCHAR2,
965 p_attribute1 IN VARCHAR2,
966 p_attribute2 IN VARCHAR2,
967 p_attribute3 IN VARCHAR2,
968 p_attribute4 IN VARCHAR2,
969 p_attribute5 IN VARCHAR2,
970 p_attribute6 IN VARCHAR2,
971 p_attribute7 IN VARCHAR2,
972 p_attribute8 IN VARCHAR2,
973 p_attribute9 IN VARCHAR2,
974 p_attribute10 IN VARCHAR2,
975 p_org_id IN NUMBER,
976 x_allow_adjustment_code OUT NOCOPY VARCHAR2,
977 x_return_status OUT NOCOPY VARCHAR2,
978 x_application_code OUT NOCOPY VARCHAR2,
979 x_message_code OUT NOCOPY VARCHAR2,
980 x_token_name1 OUT NOCOPY VARCHAR2,
981 x_token_val1 OUT NOCOPY VARCHAR2,
982 x_token_name2 OUT NOCOPY VARCHAR2,
983 x_token_val2 OUT NOCOPY VARCHAR2,
984 x_token_name3 OUT NOCOPY VARCHAR2,
985 x_token_val3 OUT NOCOPY VARCHAR2)
986
987 IS
988
989 BEGIN
990
991 --The default logic of this client extension will return the
992 --pa_transaction_sources.allow_adjustment_flag for p_transaction_source.
993
994 x_allow_adjustment_code := p_allow_adjustment_flag;
995
996 x_return_status := 'S';
997
998
999 EXCEPTION
1000
1001 WHEN others THEN
1002
1003 x_return_status := 'U';
1004
1005 x_message_code := to_char(SQLCODE);
1006
1007 END Allow_Adjustment_Extn;
1008
1009
1010 /*Bug 8574986 begin*/
1011
1012 PROCEDURE validate_task(
1016 , X_msg_token1 OUT NOCOPY VARCHAR2
1013 X_project_id IN NUMBER
1014 , X_task_id IN NUMBER
1015 , X_msg_type OUT NOCOPY VARCHAR2
1017 , X_msg_count OUT NOCOPY NUMBER
1018 , X_msg_data OUT NOCOPY VARCHAR2
1019 ) IS
1020
1021 INVALID_DATA EXCEPTION;
1022 l_cm_subt_count number ;
1023
1024
1025 BEGIN
1026 X_msg_count:=0;
1027
1028 select count(*)
1029 into l_cm_subt_count
1030 from pa_proj_elements ppe
1031 where ppe.proj_element_id= X_task_id
1032 and ppe.project_id=X_project_id
1033 /*and link_task_flag='Y'*/ /*bug 8916805*/ /*commented for bug 8996313*/
1034 and exists (select por.* from /*modified logic for bug 8996313*/
1035 pa_proj_element_versions ppev,
1036 pa_proj_element_versions ppev1,
1037 pa_proj_elements ppe1,
1038 pa_object_relationships por
1039 where
1040 ppev.element_version_id = por.object_id_from1
1041 and ppev.proj_element_id =ppe.proj_element_id
1042 and ppev1.element_version_id = por.object_id_to1
1043 and ppe1.proj_element_id = ppev1.proj_element_id
1044 and ppe1.link_task_flag = 'Y'
1045 and ppe1.task_status is not null /*Bug 8916805*/
1046 and por.relationship_type = 'S'
1047 and ppev.financial_task_flag = 'Y'
1048 and por.object_type_from='PA_TASKS')
1049 and not exists (select 1 from pa_tasks where parent_task_id=X_task_id);
1050
1051 IF (nvl(l_cm_subt_count,0)>0) Then
1052 X_msg_data :='PA_CM_SUB_TASK';
1053 select task_number into X_msg_token1 from pa_tasks where task_id=X_task_id;
1054
1055 raise INVALID_DATA;
1056 END IF;
1057
1058 EXCEPTION
1059 WHEN INVALID_DATA THEN
1060 X_msg_count:= X_msg_count+1;
1061 X_msg_type:='E';
1062 WHEN OTHERS THEN
1063 X_msg_data :=SQLERRM;
1064 X_msg_count:= X_msg_count+1;
1065 X_msg_type:='E';
1066 END validate_task;
1067
1068 /*Bug 8574986 end*/
1069
1070
1071 END PA_TRANSACTIONS_PUB;
[Home] [Help]