1 Package GHR_NFC_POSITION_EXTRACTS As
2 /* $Header: ghrnfcpext.pkh 120.0 2005/06/24 07:30:09 appldev noship $ */
3
4
5 g_proc_name Varchar2(200) :='GHR_NFC_POSITION_EXTRACTS.';
6
7 TYPE r_pqp_rules IS RECORD (rep_attribute_name VARCHAR2(80)
8 ,record_indicator VARCHAR2(2)
9 , db_column_name VARCHAR2(80)
10 ,sequence NUMBER
11 );
12
13 TYPE t_pqp_rules is Table OF r_pqp_rules
14 INDEX BY BINARY_INTEGER;
15
16 TYPE r_pqp_metadata_master IS RECORD ( context_name VARCHAR2(80)
17 , segment_name VARCHAR2(80)
18 , db_column_name VARCHAR2(80)
19 , rep_attribute_name VARCHAR2(80)
20 , record_indicator VARCHAR2(2)
21 , rule VARCHAR2(1)
22 , sequence NUMBER);
23
24 TYPE t_pqp_metadata_master is Table OF r_pqp_metadata_master
25 INDEX BY BINARY_INTEGER;
26
27
28 TYPE r_pqp_record_values IS RECORD ( sequence NUMBER
29 ,attribute_name VARCHAR2(160)
30 ,attribute_value VARCHAR2(80)
31 ,rule VARCHAR2(1));
32
33 TYPE t_pqp_record_values is Table OF r_pqp_record_values
34 INDEX BY BINARY_INTEGER;
35
36
37 TYPE r_interdisciplinary_metadata IS RECORD
38 (int_series_code VARCHAR2(80)
39 ,int_title_code VARCHAR2(80)
40 ,int_title_suffix VARCHAR2(80)
41 ,int_title_prefix VARCHAR2(80));
42
43 TYPE t_interdisciplinary_metadata is Table OF r_interdisciplinary_metadata
44 INDEX BY BINARY_INTEGER;
45
46
47 TYPE valtabtyp IS TABLE OF ben_ext_rslt_dtl.val_01%TYPE
48 INDEX BY BINARY_INTEGER ;
49
50 g_per_people_f t_pqp_metadata_master;
51 g_per_assignments_f t_pqp_metadata_master;
52 g_per_positions t_pqp_metadata_master;
53 g_per_assignment_extra_info t_pqp_metadata_master;
54 g_per_people_extra_info t_pqp_metadata_master;
55 g_per_position_extra_info t_pqp_metadata_master;
56 g_ghr_pa_history t_pqp_metadata_master;
57 g_position_kff t_pqp_metadata_master;
58 g_grade_kff t_pqp_metadata_master;
59 g_job_kff t_pqp_metadata_master;
60 g_master_data t_pqp_record_values;
61 g_individual_data t_pqp_record_values;
62 g_position_id NUMBER;
63 g_business_group_id per_all_assignments_f.business_group_id%TYPE;
64 g_user_id VARCHAR2(20);
65 g_dept_code VARCHAR2(20);
66 g_agency_code VARCHAR2(20);
67 g_poi VARCHAR2(20);
68 g_person_exist VARCHAR2(2);
69 g_master_position_exist VARCHAR2(2);
70 g_conc_request_id NUMBER;
71 g_pqp_rules t_pqp_rules;
72 g_int_data t_pqp_record_values;
73 g_int_metadata t_interdisciplinary_metadata;
74 g_ext_dtl_rcd_id ben_ext_rcd.ext_rcd_id%type;
75
76 TYPE extract_params IS RECORD
77 (session_id number
78 ,business_group_id per_business_groups.business_group_id%TYPE
79 ,concurrent_req_id ben_ext_rslt.request_id%TYPE
80 ,ext_dfn_id ben_ext_dfn.ext_dfn_id%TYPE
81 ,transmission_type varchar2(30)
82 ,date_criteria varchar2(30)
83 ,from_date date
84 ,to_date date
85 ,agency_code varchar2(30)
86 ,personnel_office_id varchar2(90)
87 ,transmission_indicator varchar2(90)
88 ,signon_identification varchar2(30)
89 ,user_id varchar2(30)
90 ,dept_code varchar2(30)
91 ,payroll_id NUMBER
92 ,notify varchar2(90)
93 );
94 TYPE t_extract_params IS TABLE OF extract_params INDEX BY Binary_Integer;
95 g_extract_params t_extract_params;
96
97 -- =============================================================================
98 -- ~ NFC_Extract_Process: This is called by the conc. program as is a
99 -- ~ wrapper around the benefits conc. program Extract Process.
100 -- =============================================================================
101
102 PROCEDURE NFC_JCL_Extract_Process
103 (errbuf OUT NOCOPY VARCHAR2
104 ,retcode OUT NOCOPY VARCHAR2
105 ,p_benefit_action_id IN NUMBER
106 ,p_extract_name IN VARCHAR2
107 ,p_effective_date IN VARCHAR2
108 ,p_business_group_id IN NUMBER
109 ,p_user_id IN VARCHAR2
110 ,p_dept_code IN VARCHAR2
111 ,p_agency_code IN VARCHAR2
112 ,p_poi IN VARCHAR2
113 ,p_ext_rslt_id IN NUMBER DEFAULT NULL ) ;
114
115
116 -- =============================================================================
117 -- ~ NFC_Extract_Process: This is called by the conc. program as is a
118 -- ~ wrapper around the benefits conc. program Extract Process.
119 -- =============================================================================
120 PROCEDURE NFC_Position_Extract_Process
121 (errbuf OUT NOCOPY VARCHAR2
122 ,retcode OUT NOCOPY VARCHAR2
123 ,p_business_group_id IN NUMBER
124 ,p_benefit_action_id IN NUMBER
125 ,p_ext_dfn_id IN NUMBER
126 ,p_ext_jcl_id IN NUMBER
127 ,p_ext_dfn_typ_id IN VARCHAR2
128 ,p_ext_dfn_data_typ IN VARCHAR2
129 ,p_transmission_type IN VARCHAR2
130 ,p_date_criteria IN VARCHAR2
131 ,p_dummy1 IN VARCHAR2
132 ,p_dummy2 IN VARCHAR2
133 ,p_dummy3 IN VARCHAR2
134 ,p_from_date IN VARCHAR2
135 ,p_to_date IN VARCHAR2
136 ,p_agency_code IN VARCHAR2
137 ,p_personnel_office_id IN VARCHAR2
138 ,p_transmission_indicator IN VARCHAR2
139 ,p_signon_identification IN VARCHAR2
140 ,p_user_id IN VARCHAR2
141 ,p_dept_code IN VARCHAR2
142 ,p_payroll_id IN NUMBER
143 ,p_notify IN VARCHAR2
144 ,p_ext_rslt_id IN NUMBER DEFAULT NULL ) ;
145
146
147 -- =============================================================================
148 -- Create Build_Element_Valuese
149 -- =============================================================================
150 PROCEDURE Build_Element_Values
151 (p_position_id IN per_all_positions.position_id%type
152 ,p_business_group_id IN per_all_positions.business_group_id%type
153 ,p_effective_start_date IN date default sysdate
154 ,p_effective_end_date IN date default sysdate
155 ,p_record_indicator IN VARCHAR2);
156 -- =============================================================================
157 -- Get_Interface_Attribute_Value
158 -- =============================================================================
159 FUNCTION Get_Interface_Attribute_Value
160 (p_Indicator VARCHAR2
161 ,p_Attribute_name VARCHAR2
162 ,p_sequence NUMBER) RETURN VARCHAR2;
163
164 -- Check_Position_Type:
165 -- =============================================================================
166 FUNCTION Check_Position_Type
167 (p_sub_header_type IN VARCHAR2
168 ,p_error_message OUT NOCOPY Varchar2
169 ) RETURN Varchar2;
170
171 -- Position_Sub_Header_Criteria: The Main extract criteria that would be used
172 -- for the position extract.
173 -- =============================================================================
174
175 FUNCTION Position_Sub_Header_Criteria
176 (p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
177 ,p_position_id IN hr_all_positions_f.position_id%TYPE
178 ,p_warning_message OUT NOCOPY Varchar2
179 ,p_error_message OUT NOCOPY Varchar2
180 ) RETURN Varchar2 ;
181
182 -- =============================================================================
183 -- ~ Evaluate_SubHeader_Formula:
184 -- =============================================================================
185 FUNCTION Evaluate_SubHeader_Formula
186 (p_indicator in varchar2
187 ,p_attribute_name in varchar2
188 ,p_msg_type in out NoCopy varchar2
189 ,p_error_code in out NoCopy varchar2
190 ,p_error_message in out NoCopy varchar2
191 )RETURN VARCHAR2;
192
193 -- =============================================================================
194 -- ~ Evaluate_SubPosition_Formula:
195 -- =============================================================================
196 FUNCTION Evaluate_SubPosition_Formula
197 (p_indicator in varchar2
198 ,p_attribute_name in varchar2
199 ,p_msg_type in out NoCopy varchar2
200 ,p_error_code in out NoCopy varchar2
201 ,p_error_message in out NoCopy varchar2
202 )RETURN VARCHAR2;
203 -- =============================================================================
204 -- ~ Del_Post_Process_Recs:
205 -- =============================================================================
206 FUNCTION Del_Post_Process_Recs
207 (p_business_group_id ben_ext_rslt_dtl.business_group_id%TYPE
208 )RETURN NUMBER;
209 -- =============================================================================
210 -- ~ Evaluate_Detail_Rcd_Formula:
211 -- =============================================================================
212 FUNCTION Evaluate_Detail_Rcd_Formula
213 (p_assignment_id IN NUMBER
214 ,p_business_group_id IN NUMBER
215 ,p_indicator in varchar2
216 ,p_attribute_name in varchar2
217 ,p_msg_type in out NoCopy varchar2
218 ,p_error_code in out NoCopy varchar2
219 ,p_error_message in out NoCopy varchar2) RETURN VARCHAR2;
220
221 -- =============================================================================
222 -- ~ Position_Person_Main_Criteria:
223 -- =============================================================================
224 FUNCTION Position_Person_Main_Criteria
225 (p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
226 ,p_effective_date IN Date
227 ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
228 ,p_warning_message OUT NOCOPY Varchar2
229 ,p_error_message OUT NOCOPY Varchar2
230 ) RETURN Varchar2 ;
231 -- =============================================================================
232 -- ~ Get_NFC_ConcProg_Information: Common function to get the conc.prg parameters
233 -- =============================================================================
234 FUNCTION Get_NFC_ConcProg_Information
235 (p_header_type IN VARCHAR2
236 ,p_error_message OUT NOCOPY VARCHAR2) RETURN Varchar2;
237
238
239 END GHR_NFC_POSITION_EXTRACTS;