1 PACKAGE per_cagr_evaluation_pkg as
2 /* $Header: pecgrevl.pkh 120.0.12000000.1 2007/01/21 21:09:19 appldev ship $ */
3
4 TYPE cagr_SE_record IS RECORD (VALUE VARCHAR2(240)
5 ,RANGE_FROM VARCHAR2(240)
6 ,RANGE_TO VARCHAR2(240)
7 ,GRADE_SPINE_ID NUMBER(15)
8 ,PARENT_SPINE_ID NUMBER(15)
9 ,STEP_ID NUMBER(15)
10 ,FROM_STEP_ID NUMBER(15)
11 ,TO_STEP_ID NUMBER(15)
12 ,REQUEST_ID NUMBER(15)
13 ,ERROR varchar2(100));
14
15 TYPE cagr_BE_record IS RECORD (COLLECTIVE_AGREEMENT_ID NUMBER(15)
16 ,ASSIGNMENT_ID NUMBER(15)
17 ,PERSON_ID NUMBER(15)
18 ,VALUE VARCHAR2(240)
19 ,RANGE_FROM VARCHAR2(240)
20 ,RANGE_TO VARCHAR2(240)
21 ,GRADE_SPINE_ID NUMBER(15)
22 ,PARENT_SPINE_ID NUMBER(15)
23 ,STEP_ID NUMBER(15)
24 ,FROM_STEP_ID NUMBER(15)
25 ,TO_STEP_ID NUMBER(15)
26 ,CHOSEN_FLAG VARCHAR2(30)
27 ,BENEFICIAL_FLAG VARCHAR2(30));
28
29 TYPE control_structure IS RECORD (effective_date date
30 ,operation_mode varchar2(30)
31 ,business_group_id number(10)
32 ,cagr_request_id number(10)
33 ,assignment_id number(10)
34 ,assignment_set_id number(10)
35 ,collective_agreement_id number(10)
36 ,category varchar2(30)
37 ,cagr_set_id number(10)
38 ,payroll_id number(10)
39 ,person_id number(10)
40 ,entitlement_item_id number(10)
41 ,commit_flag varchar2(30)
42 ,denormalise_flag varchar2(30)
43 ,return_code number(10)); -- error code to CONC / FPORM
44
45 TYPE cagr_BE_table is TABLE OF cagr_BE_record
46 INDEX BY BINARY_INTEGER;
47
48
49 g_head_separator constant varchar2(80) := '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++';
50 g_separator constant varchar2(80) := '--------------------------------------------------------------------------------';
51 --
52 -- ----------------------------------------------------------------------------
53 -- |-------------------------< get_entitlement_value >-------------------------|
54 -- ----------------------------------------------------------------------------
55 --
56 -- Description:
57 -- Wrapper procedure running initialise in single entitlement mode, returning
58 -- entitlement value in output structure.
59 --
60 -- Post Success
61 --
62 -- Post Failure:
63 --
64 -- Developer Implementation Notes:
65 --
66 -- Access Status:
67 --
68 PROCEDURE get_entitlement_value (p_process_date in date
69 ,p_business_group_id in number
70 ,p_assignment_id in number
71 ,p_entitlement_item_id in number
72 ,p_collective_agreement_id in number default null
73 ,p_collective_agreement_set_id in number default null
74 ,p_commit_flag in varchar2 default 'N'
75 ,p_output_structure out nocopy per_cagr_evaluation_pkg.cagr_SE_record);
76
77
78 --
79 -- ----------------------------------------------------------------------------
80 -- |-------------------------< get_mass_entitlement >-------------------------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description:
84 -- Wrapper procedure to call initialise in batch entitlement mode (BE), returning
85 -- a pl/sql table of eligible people (and their entitlement results) for an
86 -- entitlement item on the process date.
87 --
88 -- If collective_agreement_id parameter is supplied then eligibility for the
89 -- entitlement item will be evaluated for that cagr only, otherwise eligibility
90 -- for the entitlement item will be evaluated across all cagrs.
91 --
92 -- If a value or step is supplied for the entitlement item then eligibility
93 -- evaluation will be further restricted to return only those people
94 -- who are entitled to that item and are eligible for that value or step.
95 --
96 -- Post Success
97 --
98 -- Post Failure:
99 --
100 -- Developer Implementation Notes:
101 --
102 -- Access Status:
103 --
104 PROCEDURE get_mass_entitlement (p_process_date in date
105 ,p_business_group_id in number
106 ,p_entitlement_item_id in number
107 ,p_value in varchar2 default null
108 ,p_step_id in number default null
109 ,p_collective_agreement_id in number default null
110 ,p_collective_agreement_set_id in number default null
111 ,p_commit_flag in varchar2 default 'N'
112 ,p_output_structure out nocopy per_cagr_evaluation_pkg.cagr_BE_table
113 ,p_cagr_request_id out nocopy number);
114
115 --
116 -- ----------------------------------------------------------------------------
117 -- |------------------------------< initialise >-----------------------------|
118 -- ----------------------------------------------------------------------------
119 --
120 -- Description:
121 -- This procedure initiates a run of the collective agreement entitlement
122 -- evaluation engine.
123 --
124 -- Post Success
125 --
126 -- Post Failure:
127 --
128 -- Developer Implementation Notes:
129 --
130 -- Access Status:
131 --
132 PROCEDURE initialise
133 (p_process_date IN DATE
134 ,p_operation_mode IN VARCHAR2
135 ,p_business_group_id IN NUMBER
136 ,p_assignment_id IN NUMBER DEFAULT NULL
137 ,p_assignment_set_id IN NUMBER DEFAULT NULL
138 ,p_collective_agreement_id IN NUMBER DEFAULT NULL
139 ,p_collective_agreement_set_id IN NUMBER DEFAULT NULL
140 ,p_payroll_id IN NUMBER DEFAULT NULL
141 ,p_person_id IN NUMBER DEFAULT NULL
142 ,p_entitlement_item_id IN NUMBER DEFAULT NULL
143 ,p_commit_flag IN VARCHAR2 DEFAULT 'N'
144 ,p_apply_results_flag IN VARCHAR2 DEFAULT 'N'
145 ,p_cagr_request_id OUT NOCOPY NUMBER);
146
147 --
148 -- ----------------------------------------------------------------------------
149 -- |-------------------------< new_entitlement >------------------------------|
150 -- ----------------------------------------------------------------------------
151 --
152 -- Description:
153 --
154 -- Post Success
155 --
156 -- Post Failure:
157 --
158 -- Developer Implementation Notes:
159 --
160 -- Access Status:
161 --
162 FUNCTION new_entitlement (p_ent_id IN NUMBER) RETURN VARCHAR2;
163
164
165 END per_cagr_evaluation_pkg;