1 PACKAGE pqp_rates_history_calc AS
2 /* $Header: pqrthcal.pkh 120.1.12010000.1 2008/07/28 11:22:57 appldev ship $ */
3
4
5 c_overrides_asg_contract CONSTANT VARCHAR2(80):= 'OVERRIDE';
6 c_defaults_asg_contract CONSTANT VARCHAR2(80):= 'DEFAULT';
7 c_default_type_of_rate CONSTANT VARCHAR2(30):= 'E';
8 c_contract_table_name CONSTANT pay_user_tables.user_table_name%TYPE :=
9 'PQP_CONTRACT_TYPES';
10
11 g_default_contract_type_usage CONSTANT VARCHAR2(80):=
12 c_overrides_asg_contract;
13
14 g_rounding_precision NUMBER(2,0):=5;
15
16
17 CURSOR csr_element_type_id
18 (p_name VARCHAR2
19 ) IS
20 SELECT element_type_id
21 FROM pay_element_types_f
22 WHERE element_name = p_name;
23
24 TYPE csr_element_set_typ IS REF CURSOR RETURN csr_element_type_id%ROWTYPE;
25
26 -- ----------------------------------------------------------------------------
27 -- |--------------------------< get_element_attributes >----------------------|
28 -- ----------------------------------------------------------------------------
29 --
30 -- Description: Returns all the rates history attribution held at the
31 -- element level.
32 -- Added to spec due to dependency in pqpgbtp1.pkb
33 PROCEDURE get_element_attributes
34 (p_element_type_extra_info_id IN NUMBER
35 ,p_service_history OUT NOCOPY VARCHAR2
36 ,p_fte OUT NOCOPY VARCHAR2
37 ,p_pay_source_value OUT NOCOPY VARCHAR2
38 ,p_qualifier OUT NOCOPY VARCHAR2
39 ,p_from_time_dim OUT NOCOPY VARCHAR2
40 ,p_calculation_type OUT NOCOPY VARCHAR2
41 ,p_calculation_value OUT NOCOPY VARCHAR2
42 ,p_input_value OUT NOCOPY VARCHAR2
43 ,p_linked_to_assignment OUT NOCOPY VARCHAR2
44 ,p_term_time_yes_no OUT NOCOPY VARCHAR2
45 ,p_sum_multiple_entries_yn OUT NOCOPY VARCHAR2
46 ,p_lookup_input_values_yn OUT NOCOPY VARCHAR2
47 ,p_column_name_source_type OUT NOCOPY VARCHAR2
48 ,p_column_name_source_name OUT NOCOPY VARCHAR2
49 ,p_row_name_source_type OUT NOCOPY VARCHAR2
50 ,p_row_name_source_name OUT NOCOPY VARCHAR2
51 );
52
53 -- ----------------------------------------------------------------------------
54 -- |--------------------------< convert_values >-----------------------------|
55 -- ----------------------------------------------------------------------------
56 --
57 -- Description: Convert values from source to destination dimensions
58 -- includes applying the FTE, Service History and Term Time Factors
59 -- Added to spec due to dependency in pqpgbtp1.pkb
60 FUNCTION convert_values
61 (p_assignment_id IN NUMBER
62 ,p_date IN DATE
63 ,p_value IN NUMBER
64 ,p_to_time_dim IN VARCHAR2
65 ,p_from_time_dim IN VARCHAR2
66 ,p_fte IN VARCHAR2
67 ,p_service_history IN VARCHAR2
68 ,p_term_time_yes_no IN VARCHAR2
69 ,p_contract_type IN VARCHAR2 DEFAULT NULL
70 ,p_contract_type_usage IN VARCHAR2 DEFAULT g_default_contract_type_usage
71 ) RETURN NUMBER;
72
73 -- ----------------------------------------------------------------------------
74 -- |--------------------------< process_element >-----------------------------|
75 -- ----------------------------------------------------------------------------
76 --
77 -- Description: Retrive all necessary data, and calculate the
78 -- applicable rate of pay.
79 -- Added to spec due to dependency in pqpgbtp1.pkb
80 FUNCTION process_element(
81 p_assignment_id IN NUMBER
82 ,p_date IN DATE
83 ,p_element_type_id IN NUMBER
84 ,p_to_time_dim IN VARCHAR2
85 ,p_fte IN VARCHAR2
86 ,p_service_history IN VARCHAR2
87 ,p_pay_source_value IN VARCHAR2
88 ,p_qualifier IN VARCHAR2
89 ,p_from_time_dim IN VARCHAR2
90 ,p_calculation_type IN VARCHAR2
91 ,p_calculation_value IN NUMBER
92 ,p_input_value IN VARCHAR2
93 ,p_term_time_yes_no IN VARCHAR2
94 ,p_sum_multiple_entries_yn IN VARCHAR2
95 ,p_lookup_input_values_yn IN VARCHAR2
96 ,p_column_name_source_type IN VARCHAR2
97 ,p_column_name_source_name IN VARCHAR2
98 ,p_row_name_source_type IN VARCHAR2
99 ,p_row_name_source_name IN VARCHAR2
100 ,p_contract_type IN VARCHAR2 DEFAULT NULL
101 ,p_contract_type_usage IN VARCHAR2 DEFAULT g_default_contract_type_usage
102 ) RETURN NUMBER;
103
104
105 -- ----------------------------------------------------------------------------
106 -- |--------------------------< rates_history >------------------------------|
107 -- ----------------------------------------------------------------------------
108 --
109 -- Description: Top level function, returning rate of pay. Can be used
110 -- for single element or rate type.
111 -- Formula Function: GB_RATES_HISTORY (aliased RATES_HISTORY) maps to this spec
112 -- deprecate use of this function gradually
113
114 FUNCTION rates_history
115 (p_assignment_id IN NUMBER
116 ,p_calculation_date IN DATE
117 ,p_name IN VARCHAR2
118 ,p_rt_element IN VARCHAR2
119 ,p_to_time_dim IN VARCHAR2
120 ,p_rate IN OUT NOCOPY NUMBER
121 ,p_error_message IN OUT NOCOPY VARCHAR2
122 ,p_contract_type IN VARCHAR2 DEFAULT NULL
123 ,p_contract_type_usage IN VARCHAR2 DEFAULT g_default_contract_type_usage
124 ) RETURN NUMBER;
125
126 -- ----------------------------------------------------------------------------
127 -- |-------------------------< get_historic_rate >----------------------------|
128 -- ----------------------------------------------------------------------------
129 --
130 -- Description: Top level function, returning rate of pay. Can be used
131 -- for single element or rate type.
132 -- Formula Function: GET_HISTORIC_RATE (aliased RATES_HISTORY) maps to this spec
133 -- p_effective_date DEFAULT session effective date,
134 -- if the session date is no not set then
135 -- system date
136 -- p_time_dimension DEFAULT the same as the source time dimension
137 -- for the element
138 -- p_rate_type_or_element DEFAULT c_default_type_of_rate = 'E'
139 -- p_contract_type DEFAULT Null , if no contract type is supplied
140 -- then one is expected to exist
141 -- at the assignment level
142 -- p_contract_type_usage DEFAULT g_default_contract_type_usage = 'OVERRIDE'
143 -- override the assignment contract with
144 -- the one specified in p_contract_type
145 FUNCTION get_historic_rate
146 (p_assignment_id IN NUMBER
147 ,p_rate_name IN VARCHAR2
148 ,p_effective_date IN DATE DEFAULT NULL
149 ,p_time_dimension IN VARCHAR2 DEFAULT NULL
150 ,p_rate_type_or_element IN VARCHAR2 DEFAULT c_default_type_of_rate
151 ,p_contract_type IN VARCHAR2 DEFAULT NULL
152 ,p_contract_type_usage IN VARCHAR2 DEFAULT g_default_contract_type_usage
153 ) RETURN NUMBER;
154
155
156
157 END pqp_rates_history_calc;
[Home] [Help]