357: -- Once passed validation, this API also makes sure only the enabled segments
358: -- are passed back through the IN/OUT p_attributex parameters
359: PROCEDURE validate_dff (
360: p_dff_name IN fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE,
361: p_attribute_category IN pa_expenditure_items_all.attribute_category%TYPE,
362: p_attribute1 IN OUT NOCOPY pa_expenditure_items_all.attribute1%TYPE,
363: p_attribute2 IN OUT NOCOPY pa_expenditure_items_all.attribute2%TYPE,
364: p_attribute3 IN OUT NOCOPY pa_expenditure_items_all.attribute3%TYPE,
365: p_attribute4 IN OUT NOCOPY pa_expenditure_items_all.attribute4%TYPE,
358: -- are passed back through the IN/OUT p_attributex parameters
359: PROCEDURE validate_dff (
360: p_dff_name IN fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE,
361: p_attribute_category IN pa_expenditure_items_all.attribute_category%TYPE,
362: p_attribute1 IN OUT NOCOPY pa_expenditure_items_all.attribute1%TYPE,
363: p_attribute2 IN OUT NOCOPY pa_expenditure_items_all.attribute2%TYPE,
364: p_attribute3 IN OUT NOCOPY pa_expenditure_items_all.attribute3%TYPE,
365: p_attribute4 IN OUT NOCOPY pa_expenditure_items_all.attribute4%TYPE,
366: p_attribute5 IN OUT NOCOPY pa_expenditure_items_all.attribute5%TYPE,
359: PROCEDURE validate_dff (
360: p_dff_name IN fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE,
361: p_attribute_category IN pa_expenditure_items_all.attribute_category%TYPE,
362: p_attribute1 IN OUT NOCOPY pa_expenditure_items_all.attribute1%TYPE,
363: p_attribute2 IN OUT NOCOPY pa_expenditure_items_all.attribute2%TYPE,
364: p_attribute3 IN OUT NOCOPY pa_expenditure_items_all.attribute3%TYPE,
365: p_attribute4 IN OUT NOCOPY pa_expenditure_items_all.attribute4%TYPE,
366: p_attribute5 IN OUT NOCOPY pa_expenditure_items_all.attribute5%TYPE,
367: p_attribute6 IN OUT NOCOPY pa_expenditure_items_all.attribute6%TYPE,
360: p_dff_name IN fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE,
361: p_attribute_category IN pa_expenditure_items_all.attribute_category%TYPE,
362: p_attribute1 IN OUT NOCOPY pa_expenditure_items_all.attribute1%TYPE,
363: p_attribute2 IN OUT NOCOPY pa_expenditure_items_all.attribute2%TYPE,
364: p_attribute3 IN OUT NOCOPY pa_expenditure_items_all.attribute3%TYPE,
365: p_attribute4 IN OUT NOCOPY pa_expenditure_items_all.attribute4%TYPE,
366: p_attribute5 IN OUT NOCOPY pa_expenditure_items_all.attribute5%TYPE,
367: p_attribute6 IN OUT NOCOPY pa_expenditure_items_all.attribute6%TYPE,
368: p_attribute7 IN OUT NOCOPY pa_expenditure_items_all.attribute7%TYPE,
361: p_attribute_category IN pa_expenditure_items_all.attribute_category%TYPE,
362: p_attribute1 IN OUT NOCOPY pa_expenditure_items_all.attribute1%TYPE,
363: p_attribute2 IN OUT NOCOPY pa_expenditure_items_all.attribute2%TYPE,
364: p_attribute3 IN OUT NOCOPY pa_expenditure_items_all.attribute3%TYPE,
365: p_attribute4 IN OUT NOCOPY pa_expenditure_items_all.attribute4%TYPE,
366: p_attribute5 IN OUT NOCOPY pa_expenditure_items_all.attribute5%TYPE,
367: p_attribute6 IN OUT NOCOPY pa_expenditure_items_all.attribute6%TYPE,
368: p_attribute7 IN OUT NOCOPY pa_expenditure_items_all.attribute7%TYPE,
369: p_attribute8 IN OUT NOCOPY pa_expenditure_items_all.attribute8%TYPE,
362: p_attribute1 IN OUT NOCOPY pa_expenditure_items_all.attribute1%TYPE,
363: p_attribute2 IN OUT NOCOPY pa_expenditure_items_all.attribute2%TYPE,
364: p_attribute3 IN OUT NOCOPY pa_expenditure_items_all.attribute3%TYPE,
365: p_attribute4 IN OUT NOCOPY pa_expenditure_items_all.attribute4%TYPE,
366: p_attribute5 IN OUT NOCOPY pa_expenditure_items_all.attribute5%TYPE,
367: p_attribute6 IN OUT NOCOPY pa_expenditure_items_all.attribute6%TYPE,
368: p_attribute7 IN OUT NOCOPY pa_expenditure_items_all.attribute7%TYPE,
369: p_attribute8 IN OUT NOCOPY pa_expenditure_items_all.attribute8%TYPE,
370: p_attribute9 IN OUT NOCOPY pa_expenditure_items_all.attribute9%TYPE,
363: p_attribute2 IN OUT NOCOPY pa_expenditure_items_all.attribute2%TYPE,
364: p_attribute3 IN OUT NOCOPY pa_expenditure_items_all.attribute3%TYPE,
365: p_attribute4 IN OUT NOCOPY pa_expenditure_items_all.attribute4%TYPE,
366: p_attribute5 IN OUT NOCOPY pa_expenditure_items_all.attribute5%TYPE,
367: p_attribute6 IN OUT NOCOPY pa_expenditure_items_all.attribute6%TYPE,
368: p_attribute7 IN OUT NOCOPY pa_expenditure_items_all.attribute7%TYPE,
369: p_attribute8 IN OUT NOCOPY pa_expenditure_items_all.attribute8%TYPE,
370: p_attribute9 IN OUT NOCOPY pa_expenditure_items_all.attribute9%TYPE,
371: p_attribute10 IN OUT NOCOPY pa_expenditure_items_all.attribute10%TYPE,
364: p_attribute3 IN OUT NOCOPY pa_expenditure_items_all.attribute3%TYPE,
365: p_attribute4 IN OUT NOCOPY pa_expenditure_items_all.attribute4%TYPE,
366: p_attribute5 IN OUT NOCOPY pa_expenditure_items_all.attribute5%TYPE,
367: p_attribute6 IN OUT NOCOPY pa_expenditure_items_all.attribute6%TYPE,
368: p_attribute7 IN OUT NOCOPY pa_expenditure_items_all.attribute7%TYPE,
369: p_attribute8 IN OUT NOCOPY pa_expenditure_items_all.attribute8%TYPE,
370: p_attribute9 IN OUT NOCOPY pa_expenditure_items_all.attribute9%TYPE,
371: p_attribute10 IN OUT NOCOPY pa_expenditure_items_all.attribute10%TYPE,
372: x_status_code OUT NOCOPY VARCHAR2,
365: p_attribute4 IN OUT NOCOPY pa_expenditure_items_all.attribute4%TYPE,
366: p_attribute5 IN OUT NOCOPY pa_expenditure_items_all.attribute5%TYPE,
367: p_attribute6 IN OUT NOCOPY pa_expenditure_items_all.attribute6%TYPE,
368: p_attribute7 IN OUT NOCOPY pa_expenditure_items_all.attribute7%TYPE,
369: p_attribute8 IN OUT NOCOPY pa_expenditure_items_all.attribute8%TYPE,
370: p_attribute9 IN OUT NOCOPY pa_expenditure_items_all.attribute9%TYPE,
371: p_attribute10 IN OUT NOCOPY pa_expenditure_items_all.attribute10%TYPE,
372: x_status_code OUT NOCOPY VARCHAR2,
373: x_error_message OUT NOCOPY VARCHAR2)
366: p_attribute5 IN OUT NOCOPY pa_expenditure_items_all.attribute5%TYPE,
367: p_attribute6 IN OUT NOCOPY pa_expenditure_items_all.attribute6%TYPE,
368: p_attribute7 IN OUT NOCOPY pa_expenditure_items_all.attribute7%TYPE,
369: p_attribute8 IN OUT NOCOPY pa_expenditure_items_all.attribute8%TYPE,
370: p_attribute9 IN OUT NOCOPY pa_expenditure_items_all.attribute9%TYPE,
371: p_attribute10 IN OUT NOCOPY pa_expenditure_items_all.attribute10%TYPE,
372: x_status_code OUT NOCOPY VARCHAR2,
373: x_error_message OUT NOCOPY VARCHAR2)
374:
367: p_attribute6 IN OUT NOCOPY pa_expenditure_items_all.attribute6%TYPE,
368: p_attribute7 IN OUT NOCOPY pa_expenditure_items_all.attribute7%TYPE,
369: p_attribute8 IN OUT NOCOPY pa_expenditure_items_all.attribute8%TYPE,
370: p_attribute9 IN OUT NOCOPY pa_expenditure_items_all.attribute9%TYPE,
371: p_attribute10 IN OUT NOCOPY pa_expenditure_items_all.attribute10%TYPE,
372: x_status_code OUT NOCOPY VARCHAR2,
373: x_error_message OUT NOCOPY VARCHAR2)
374:
375: IS
372: x_status_code OUT NOCOPY VARCHAR2,
373: x_error_message OUT NOCOPY VARCHAR2)
374:
375: IS
376: v_attribute1 pa_expenditure_items_all.attribute1%TYPE;
377: v_attribute2 pa_expenditure_items_all.attribute2%TYPE;
378: v_attribute3 pa_expenditure_items_all.attribute3%TYPE;
379: v_attribute4 pa_expenditure_items_all.attribute4%TYPE;
380: v_attribute5 pa_expenditure_items_all.attribute5%TYPE;
373: x_error_message OUT NOCOPY VARCHAR2)
374:
375: IS
376: v_attribute1 pa_expenditure_items_all.attribute1%TYPE;
377: v_attribute2 pa_expenditure_items_all.attribute2%TYPE;
378: v_attribute3 pa_expenditure_items_all.attribute3%TYPE;
379: v_attribute4 pa_expenditure_items_all.attribute4%TYPE;
380: v_attribute5 pa_expenditure_items_all.attribute5%TYPE;
381: v_attribute6 pa_expenditure_items_all.attribute6%TYPE;
374:
375: IS
376: v_attribute1 pa_expenditure_items_all.attribute1%TYPE;
377: v_attribute2 pa_expenditure_items_all.attribute2%TYPE;
378: v_attribute3 pa_expenditure_items_all.attribute3%TYPE;
379: v_attribute4 pa_expenditure_items_all.attribute4%TYPE;
380: v_attribute5 pa_expenditure_items_all.attribute5%TYPE;
381: v_attribute6 pa_expenditure_items_all.attribute6%TYPE;
382: v_attribute7 pa_expenditure_items_all.attribute7%TYPE;
375: IS
376: v_attribute1 pa_expenditure_items_all.attribute1%TYPE;
377: v_attribute2 pa_expenditure_items_all.attribute2%TYPE;
378: v_attribute3 pa_expenditure_items_all.attribute3%TYPE;
379: v_attribute4 pa_expenditure_items_all.attribute4%TYPE;
380: v_attribute5 pa_expenditure_items_all.attribute5%TYPE;
381: v_attribute6 pa_expenditure_items_all.attribute6%TYPE;
382: v_attribute7 pa_expenditure_items_all.attribute7%TYPE;
383: v_attribute8 pa_expenditure_items_all.attribute8%TYPE;
376: v_attribute1 pa_expenditure_items_all.attribute1%TYPE;
377: v_attribute2 pa_expenditure_items_all.attribute2%TYPE;
378: v_attribute3 pa_expenditure_items_all.attribute3%TYPE;
379: v_attribute4 pa_expenditure_items_all.attribute4%TYPE;
380: v_attribute5 pa_expenditure_items_all.attribute5%TYPE;
381: v_attribute6 pa_expenditure_items_all.attribute6%TYPE;
382: v_attribute7 pa_expenditure_items_all.attribute7%TYPE;
383: v_attribute8 pa_expenditure_items_all.attribute8%TYPE;
384: v_attribute9 pa_expenditure_items_all.attribute9%TYPE;
377: v_attribute2 pa_expenditure_items_all.attribute2%TYPE;
378: v_attribute3 pa_expenditure_items_all.attribute3%TYPE;
379: v_attribute4 pa_expenditure_items_all.attribute4%TYPE;
380: v_attribute5 pa_expenditure_items_all.attribute5%TYPE;
381: v_attribute6 pa_expenditure_items_all.attribute6%TYPE;
382: v_attribute7 pa_expenditure_items_all.attribute7%TYPE;
383: v_attribute8 pa_expenditure_items_all.attribute8%TYPE;
384: v_attribute9 pa_expenditure_items_all.attribute9%TYPE;
385: v_attribute10 pa_expenditure_items_all.attribute10%TYPE;
378: v_attribute3 pa_expenditure_items_all.attribute3%TYPE;
379: v_attribute4 pa_expenditure_items_all.attribute4%TYPE;
380: v_attribute5 pa_expenditure_items_all.attribute5%TYPE;
381: v_attribute6 pa_expenditure_items_all.attribute6%TYPE;
382: v_attribute7 pa_expenditure_items_all.attribute7%TYPE;
383: v_attribute8 pa_expenditure_items_all.attribute8%TYPE;
384: v_attribute9 pa_expenditure_items_all.attribute9%TYPE;
385: v_attribute10 pa_expenditure_items_all.attribute10%TYPE;
386: v_desc_flex_context_name fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE;
379: v_attribute4 pa_expenditure_items_all.attribute4%TYPE;
380: v_attribute5 pa_expenditure_items_all.attribute5%TYPE;
381: v_attribute6 pa_expenditure_items_all.attribute6%TYPE;
382: v_attribute7 pa_expenditure_items_all.attribute7%TYPE;
383: v_attribute8 pa_expenditure_items_all.attribute8%TYPE;
384: v_attribute9 pa_expenditure_items_all.attribute9%TYPE;
385: v_attribute10 pa_expenditure_items_all.attribute10%TYPE;
386: v_desc_flex_context_name fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE;
387: i NUMBER;
380: v_attribute5 pa_expenditure_items_all.attribute5%TYPE;
381: v_attribute6 pa_expenditure_items_all.attribute6%TYPE;
382: v_attribute7 pa_expenditure_items_all.attribute7%TYPE;
383: v_attribute8 pa_expenditure_items_all.attribute8%TYPE;
384: v_attribute9 pa_expenditure_items_all.attribute9%TYPE;
385: v_attribute10 pa_expenditure_items_all.attribute10%TYPE;
386: v_desc_flex_context_name fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE;
387: i NUMBER;
388: /* Start of Bug 3064318 */
381: v_attribute6 pa_expenditure_items_all.attribute6%TYPE;
382: v_attribute7 pa_expenditure_items_all.attribute7%TYPE;
383: v_attribute8 pa_expenditure_items_all.attribute8%TYPE;
384: v_attribute9 pa_expenditure_items_all.attribute9%TYPE;
385: v_attribute10 pa_expenditure_items_all.attribute10%TYPE;
386: v_desc_flex_context_name fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE;
387: i NUMBER;
388: /* Start of Bug 3064318 */
389: TYPE seg_col_name is TABLE of varchar2(150)
593: IS
594:
595: --The following Cursor will select the item in Projects based upon the parameters passed
596: --to the API. It is a UNION because
597: -- 1) pa_expenditure_items_all.non_labor_resource and pa_expenditure_items_all.organization_id
598: -- (non labor resource owning organization) only exist for Usages (expenditure_type_class = 'USG')
599: -- 2) pa_expenditures_all.incurred_by_person_id may or may not exist for Usages, and
600: -- does not exist for Supplier Invoices.
601:
602: CURSOR item_in_projects IS
603: --The first part of the UNION handles all expenditure_type_classes EXCEPT
604: --Usages (USG) and Supplier Invoices (VI).
605: SELECT net_zero_adjustment_flag
606: FROM pa_expenditure_items_all ei,
607: pa_expenditures_all exp,
608: per_all_people_f per,
609: pa_projects_all proj,
610: pa_tasks task,
645: --The second part of the UNION handles Usages (expenditure_type_class='USG')
646: --when IN parameter x_employee_number is NOT NULL. The additional join to
647: --hr_all_organization_units hr2 is required.
648: SELECT net_zero_adjustment_flag
649: FROM pa_expenditure_items_all ei,
650: pa_expenditures_all exp,
651: per_all_people_f per,
652: pa_projects_all proj,
653: pa_tasks task,
692: --The third part of the UNION handles Usages (expenditure_type_class='USG')
693: --when IN PARAMETER x_exployee_number IS NULL. The additional join to
694: --hr_all_organization_units hr2 is required and the join to per_all_people_f is not required.
695: SELECT net_zero_adjustment_flag
696: FROM pa_expenditure_items_all ei,
697: pa_expenditures_all exp,
698: pa_projects_all proj,
699: pa_tasks task,
700: hr_all_organization_units hr1,
735: --The fourth part of the UNION handles Supplier Invoices (expenditure_type_class='VI').
736: --The join to per_all_people_f is not required as pa_expenditures_all.incurred_by_person_id
737: --is NULL for Supplier Invoices.
738: SELECT net_zero_adjustment_flag
739: FROM pa_expenditure_items_all ei,
740: pa_expenditures_all exp,
741: pa_projects_all proj,
742: pa_tasks task,
743: hr_all_organization_units hr1
778: FROM pa_lookups
779: WHERE lookup_type = 'PA_ADJUSTMENT_STATUS'
780: AND lookup_code = x_adjustment_status_code;
781:
782: l_net_zero_adjustment_flag pa_expenditure_items_all.net_zero_adjustment_flag%TYPE;
783: e_no_unique_transaction exception;
784:
785:
786: BEGIN