1 PACKAGE hxt_otc_retrieval_interface AS
2 /* $Header: hxtotcri.pkh 120.1.12010000.2 2008/09/19 17:19:54 asrajago ship $ */
3 TYPE t_field_name IS TABLE OF VARCHAR2 (80)
4 INDEX BY BINARY_INTEGER;
5
6 TYPE t_value IS TABLE OF VARCHAR2 (150)
7 INDEX BY BINARY_INTEGER;
8
9 TYPE t_segment IS TABLE OF VARCHAR2 (60)
10 INDEX BY BINARY_INTEGER;
11
12 -- Bug 7415291
13 -- New data types for handling the re-explosion of timecard.
14 --------------------------------------------------------------------------------
15 -- A record type to handle earning policy id
16 -- and the start and end dates effective on the assignment.
17 TYPE earn_pol_rec IS RECORD
18 ( earn_pol_id NUMBER,
19 start_date DATE,
20 end_date DATE );
21
22 -- Create a table of the above type. Each person would have
23 -- one or more record as above, so this table is used to maintain it.
24
25 TYPE earn_pol_tab IS TABLE OF earn_pol_rec;
26
27 -- Create a record of this above table type.
28
29 TYPE assg_earn_pol_rec IS RECORD
30 (
31 ep_list earn_pol_tab
32 );
33
34 -- Create an associative array of the above type.
35 -- Each element in the array would have a record, whose only member is
36 -- a plsql table of record type earn_pol_rec. Meaning each
37 -- assignment will have its own plsql table of earning policies.
38
39 TYPE earn_pol_assoc_array IS TABLE OF assg_earn_pol_rec INDEX BY VARCHAR2(15);
40
41 -- Create a list of this associative array to be maintained globally.
42
43 g_earn_pol_list earn_pol_assoc_array;
44
45
46 -- Create a table of NUMBERs to store the elements.
47 TYPE element_tab IS TABLE OF NUMBER;
48
49 -- Create a record whose member is the plsql table of numbers.
50 TYPE eg_elements IS RECORD
51 (
52 element_list element_tab
53 );
54
55 -- Create an associative array of this record as above.
56
57 TYPE ep_eg_list IS TABLE OF eg_elements INDEX BY VARCHAR2(15);
58
59 -- Create an object of the above array. Each element in the array would have
60 -- a number type plsql table. Meaning each earning policy id will have its
61 -- own list of elements in the earning group.
62
63 g_earn_group_list ep_eg_list ;
64
65 --------------------------------------------------------------------------------
66
67
68 g_full_name VARCHAR2 (240);
69
70 FUNCTION get_employee_number (
71 p_person_id IN NUMBER,
72 p_effective_date IN DATE
73 )
74 RETURN VARCHAR2;
75
76 PROCEDURE get_assignment_id (
77 p_person_id IN NUMBER,
78 p_payroll_id OUT NOCOPY NUMBER,
79 p_bg_id OUT NOCOPY NUMBER,
80 p_assignment_id OUT NOCOPY NUMBER,
81 p_effective_date IN DATE
82 );
83
84 PROCEDURE parse_attributes (
85 p_category IN OUT NOCOPY t_field_name,
86 p_field_name IN OUT NOCOPY t_field_name,
87 p_value IN OUT NOCOPY t_value,
88 p_context IN OUT NOCOPY t_field_name,
89 p_date_worked OUT NOCOPY DATE,
90 p_type IN VARCHAR2,
91 p_measure IN NUMBER,
92 p_start_time IN DATE,
93 p_stop_time IN DATE,
94 p_assignment_id IN NUMBER,
95 p_hours OUT NOCOPY NUMBER,
96 p_hours_type OUT NOCOPY VARCHAR2,
97 p_segment OUT NOCOPY t_segment,
98 p_project OUT NOCOPY VARCHAR2,
99 p_task OUT NOCOPY VARCHAR2,
100 p_STATE_NAME OUT NOCOPY VARCHAR2,
101 p_COUNTY_NAME OUT NOCOPY VARCHAR2,
102 p_CITY_NAME OUT NOCOPY VARCHAR2,
103 p_ZIP_CODE OUT NOCOPY VARCHAR2
104 );
105
106 PROCEDURE parse_attributes (
107 p_category IN OUT NOCOPY t_field_name,
108 p_field_name IN OUT NOCOPY t_field_name,
109 p_value IN OUT NOCOPY t_value,
110 p_context IN OUT NOCOPY t_field_name,
111 p_date_worked OUT NOCOPY DATE,
112 p_type IN VARCHAR2,
113 p_measure IN NUMBER,
114 p_start_time IN DATE,
115 p_stop_time IN DATE,
116 p_assignment_id IN NUMBER,
117 p_hours OUT NOCOPY NUMBER,
118 p_hours_type OUT NOCOPY VARCHAR2,
119 p_segment OUT NOCOPY t_segment,
120 p_amount OUT NOCOPY NUMBER,
121 p_hourly_rate OUT NOCOPY NUMBER,
122 p_rate_multiple OUT NOCOPY NUMBER,
123 p_project OUT NOCOPY VARCHAR2,
124 p_task OUT NOCOPY VARCHAR2,
125 p_STATE_NAME OUT NOCOPY VARCHAR2,
126 p_COUNTY_NAME OUT NOCOPY VARCHAR2,
127 p_CITY_NAME OUT NOCOPY VARCHAR2,
128 p_ZIP_CODE OUT NOCOPY VARCHAR2
129 );
130
131 PROCEDURE find_existing_timecard (
132 p_payroll_id IN NUMBER,
133 p_date_worked IN DATE,
134 p_person_id IN NUMBER,
135 p_old_ovn IN NUMBER DEFAULT NULL,
136 p_bb_id IN NUMBER DEFAULT NULL,
137 p_time_summary_id OUT NOCOPY NUMBER,
138 p_time_sum_start_date OUT NOCOPY DATE,
139 p_time_sum_end_date OUT NOCOPY DATE,
140 p_tim_id OUT NOCOPY NUMBER
141 );
142
143 PROCEDURE transfer_to_otm (
144 p_bg_id IN NUMBER,
145 p_incremental IN VARCHAR2 DEFAULT 'Y',
146 p_start_date IN VARCHAR2,
147 p_end_date IN VARCHAR2,
148 p_where_clause IN VARCHAR2,
149 p_transfer_to_bee IN VARCHAR2 DEFAULT 'N',
150 p_retrieval_transaction_code IN VARCHAR2,
151 p_batch_ref IN VARCHAR2,
152 p_no_otm IN OUT NOCOPY VARCHAR2,
153 p_unique_params IN VARCHAR2,
154 p_since_date IN VARCHAR2
155 );
156
157
158 -- Bug 7415291
159 -- New function to check if timecard needs re-explosion
160 -- in case the change is only a delete.
161
162 FUNCTION chk_need_re_explosion (
163 p_assignment_id IN NUMBER,
164 p_date_worked IN DATE,
165 p_element_type_id IN NUMBER )
166 RETURN BOOLEAN ;
167
168
169 END hxt_otc_retrieval_interface;
[Home] [Help]