1 package hxc_mapping_utilities as
2 /* $Header: hxcmputl.pkh 120.0 2005/05/29 04:59:25 appldev noship $ */
3
4 g_package varchar2(33) := ' hxc_mapping_utilities'; -- Global package name
5
6 TYPE r_bld_blk_info IS RECORD ( bld_blk_info_type_id hxc_time_attributes.bld_blk_info_type_id%TYPE );
7
8 TYPE t_bld_blk_info IS TABLE OF r_bld_blk_info INDEX BY BINARY_INTEGER;
9
10 TYPE Consolidated_info IS RECORD ( bld_blk_info_type_id hxc_time_attributes.bld_blk_info_type_id%TYPE
11 ,field_name hxc_mapping_components.field_name%TYPE
12 ,field_value varchar2(1000)
13 ,segment hxc_mapping_components.segment%TYPE);
14
15
16 TYPE t_consolidated_info_1 IS TABLE OF Consolidated_info INDEX BY BINARY_INTEGER;
17
18
19 FUNCTION get_day_date ( p_type VARCHAR2, p_bb_id NUMBER, p_bb_ovn NUMBER ) RETURN DATE;
20
21 -- public function
22 -- chk_mapping_changed
23 -- description
24 -- Returns true if any attributes identified by the specified mapping
25 -- have changed between the timecard specified by the TIMECARD scope
26 -- time building block and the earliest previous version of the timecard
27 -- time building block of the specified status
28 --
29 -- Algorithm
30 -- First of all if the object version number of any of the time building
31 -- blocks is 1 then the function returns TRUE - since the time building
32 -- block did not previously exist.
33 -- If this is not the case then for each building block the current attributes
34 -- are read into a table. Then the attributes for a prior version of the
35 -- building block with the specified status are found. If none exist then again
36 -- the function returns TRUE.
37 -- At this point before comparing the individual attributes in the table an
38 -- analysis is made of the number of bld blk info types and their value between
39 -- the old and the new. If the number of records in the new table is different
40 -- to the number in the old then the function returns TRUE. (NOTE: we have to
41 -- make sure that these are records where the bld_blk_info_type_id is not null
42 -- since there is an outer join to detect when a bld blk info type is added or
43 -- deleted). If the number of old and new bld blk info types is the same then
44
45 -- a comparison is made to see if they are the same values before attempting
46 -- to compare each attribute. Finally, if the old and the new bld blk info types
47 -- are the same each old and new attribute value is compared.
48 -- NOTE: all these comparisons above are made within the scope of the attributes
49 -- and bld blk info types specified by the mapping
50
51 FUNCTION chk_mapping_changed ( p_mapping_id NUMBER
52 , p_timecard_bb_id NUMBER
53 , p_timecard_ovn NUMBER
54 , p_start_date DATE
55 , p_end_date DATE
56 , p_last_status VARCHAR2
57 , p_time_building_blocks hxc_self_service_time_deposit.timecard_info
58 , p_time_attributes hxc_self_service_time_deposit.building_block_attribute_info
59 , p_called_from VARCHAR2 default 'APPROVALS' )
60 RETURN BOOLEAN;
61
62 -- public function
63 -- chk_bld_blk_changed
64 -- description
65 -- checks to see if the start time, stop time or measure have
66 -- changed between the current timecard and that of the prior
67 -- approval status specified.
68 --
69 -- Returns true if any of the bld blk attribution has changed
70 --
71 -- The bld blk attributes are
72 -- measure (at the DETAIL scope only)
73 -- start time (at the DAY scope only)
74 -- stop time (at the DAY scope only)
75 --
76 -- Algorithm
77 -- populates table of current timecard bld blks whose ovn is not 1
78 -- If the current bld bld is deleted store in separate table
79 --
80 -- For deleted bld blks get the prior status bld blks
81 -- check to see what the prior status bld blk looks like
82 -- if deleted then do nothing
83 -- if not deleted then RETURN TRUE
84 -- if not found at the prior status then do nothing
85 --
86 -- For non deleted bld blks get the prior status bld blks
87 -- if exists then retrieve to prior status table
88 -- if not then DO NOTHING
89 --
90 -- Finally, have table of prior bld blks
91 -- Compare the start and stop times at the DAY level
92 -- and measure at the DETAIL level - if any differ then RETURN TRUE
93 -- else return FALSE
94
95 FUNCTION chk_bld_blk_changed ( p_timecard_bb_id NUMBER
96 , p_timecard_ovn NUMBER
97 , p_start_date DATE
98 , p_end_date DATE
99 , p_last_status VARCHAR2
100 , p_time_bld_blks hxc_self_service_time_deposit.timecard_info ) RETURN BOOLEAN;
101
102 -- function
103 -- attribute_column
104 --
105 -- description
106 -- returns the name of the attribute column in HXC_TIME_ATTRIBUTES which
107 -- maps to the parameter p_field_name, based on the building block
108 -- category and information type
109 --
110 -- parameters
111 -- p_field_name - the name of the field to be mapped
112 -- p_bld_blk_info_type - the information type of the attribute
113 -- p_descriptive_flexfield_name - the name of the flexfield
114
115 function attribute_column
116 (p_field_name in varchar2
117 ,p_bld_blk_info_type in varchar2
118 ,p_descriptive_flexfield_name in varchar2
119 ) return varchar2;
120
121
122 -- function
123 -- attribute_column
124 --
125 -- description
126 -- overload of attribute_column function. returns the name of the
127 -- attribute column in HXC_TIME_ATTRIBUTES which maps to the parameter
128 -- p_field_name, based on the deposit or retrieval process identifier.
129 -- since there is no guarantee that mappings have been explicitly defined
130 -- for the given process, the column name is returned in an out parameter,
131 -- and the function returns true or false depending on whether or not a
132 -- mapping was found.
133 --
134 -- parameters
135 -- p_field_name - the name of the field to be mapped
136 -- p_process_type - (D)eposit or (R)etrieval
137 -- p_process_id - deposit or retrieval process id
138 -- p_column_name (out) - the column name where the specified field is
139 -- stored
140 -- p_bld_blk_info_type (out) - the information type of the mapped field
141
142 function attribute_column
143 (p_field_name in varchar2
144 ,p_process_type in varchar2
145 ,p_process_id in number
146 ,p_column_name in out nocopy varchar2
147 ,p_bld_blk_info_type in out nocopy varchar2
148 ) return boolean;
149
150 -- function
151 -- chk_mapping_exists
152 --
153 -- description
154 -- Returns TRUE if the mapping field name and value specified exists
155 -- anywhere in the time store. Returns FALSE if not.
156 --
157 -- If the retrieval process name is specified then
158 --
159 -- Returns TRUE only if the mapping exists but has NOT been
160 -- successfully retrieved, else RETURNS FALSE which means
161 -- that at least one has been transferred or it did not exist
162 --
163 --
164 --
165 -- parameters
166 -- p_bld_blk_info_type - bld blk info type of the mapping
167 -- p_field_name - field name of the mapping component
168 -- p_field_value - value to search for
169 -- p_scope - the scope at which the value is associated
170 -- p_retrieval_process - Retrieval Process Name
171
172 FUNCTION chk_mapping_exists ( p_bld_blk_info_type VARCHAR2
173 , p_field_name VARCHAR2
174 , p_field_value VARCHAR2
175 , p_bld_blk_info_type2 VARCHAR2 default null
176 , p_field_name2 VARCHAR2 default null
177 , p_field_value2 VARCHAR2 default null
178 , p_bld_blk_info_type3 VARCHAR2 default null
179 , p_field_name3 VARCHAR2 default null
180 , p_field_value3 VARCHAR2 default null
181 , p_bld_blk_info_type4 VARCHAR2 default null
182 , p_field_name4 VARCHAR2 default null
183 , p_field_value4 VARCHAR2 default null
184 , p_bld_blk_info_type5 VARCHAR2 default null
185 , p_field_name5 VARCHAR2 default null
186 , p_field_value5 VARCHAR2 default null
187 , p_scope VARCHAR2
188 , p_retrieval_process_name VARCHAR2 DEFAULT 'None'
189 , p_status VARCHAR2 DEFAULT 'None'
190 , p_end_date DATE DEFAULT null) RETURN BOOLEAN ;
191
192 FUNCTION get_mappingvalue_sum ( p_bld_blk_info_type VARCHAR2
193 , p_field_name1 VARCHAR2
194 , p_bld_blk_info_type2 VARCHAR2 default null
195 , p_field_name2 VARCHAR2
196 , p_field_value2 VARCHAR2
197 , p_bld_blk_info_type3 VARCHAR2 default null
198 , p_field_name3 VARCHAR2 default null
199 , p_field_value3 VARCHAR2 default null
200 , p_bld_blk_info_type4 VARCHAR2 default null
201 , p_field_name4 VARCHAR2 default null
202 , p_field_value4 VARCHAR2 default null
203 , p_bld_blk_info_type5 VARCHAR2 default null
204 , p_field_name5 VARCHAR2 default null
205 , p_field_value5 VARCHAR2 default null
206 , p_status VARCHAR2
207 , p_resource_id VARCHAR2
208 ) RETURN NUMBER;
209
210 Procedure get_mapping_value(p_bld_blk_info_type in varchar2,
211 p_field_name in varchar2,
212 p_segment out nocopy hxc_mapping_components.segment%TYPE,
213 p_bld_blk_info_type_id out nocopy hxc_mapping_components.bld_blk_info_type_id%TYPE ) ;
214
215
216
217
218 end hxc_mapping_utilities;
[Home] [Help]