1 PACKAGE ghr_pa_requests_pkg2 AUTHID CURRENT_USER AS
2 /* $Header: ghparqs2.pkh 120.6 2011/12/30 10:45:11 vmididho ship $ */
3
4 -- This function checks if there are any pending PA Request actions for a given person (not including the
5 -- given PA Request)
6 -- And returns a list of thoses that are pending.
7 -- The definition of pending is its current routing status is not 'CANCELED' or 'UPDATE_HR_COMPLETE'
8 -- To prevent listing those that got put in the 'black hole' (i.e. were saved but not routed) make sure
9 -- the routing history has a date notification sent (except for 'FUTURE_ACTIONS' as they may not have
10 -- been routed but are still pending)
11 FUNCTION check_pending_pars (p_person_id IN NUMBER
12 ,p_pa_request_id IN NUMBER)
13 RETURN VARCHAR2;
14 --
15 -- This function checks if there are any processed or approved PA Requests for the given person
16 -- at the given date. The definition of 'Processed' is the lasting Routing history record is 'UPDATE_HR_COMPLETE'
17 -- and the definition of 'Approved' is the lasting Routing history record is 'FUTURE_ACTION'
18 FUNCTION check_proc_future_pars (p_person_id IN NUMBER
19 ,p_effective_date IN DATE)
20 RETURN VARCHAR2;
21 --
22 PROCEDURE refresh_par_extra_info (p_pa_request_id IN NUMBER
23 ,p_first_noa_id IN NUMBER
24 ,p_second_noa_id IN NUMBER
25 ,p_person_id IN NUMBER
26 ,p_assignment_id IN NUMBER
27 ,p_position_id IN NUMBER
28 ,p_effective_date IN DATE);
29 --
30 -- This function is passed an altered Pa request id to check that it is not a request id that
31 -- is also a correction.
32 -- Returns TRUE if the pa request id passed is not a correction
33 --
34 FUNCTION check_first_correction (p_altered_pa_request_id IN NUMBER)
35 RETURN BOOLEAN;
36 --
37 -- this function takes in a pa request id and gives back the 'Agency code Transfer from'
38 -- that is in the PAR EI (should only be called for Appointment transfers, since all these NOACs
39 -- are with APP PM family we will actually call it in the form for ALL NOAC's in the APP family)
40 -- it should then be used to go into field #14
41 FUNCTION get_agency_code_from (p_pa_request_id IN NUMBER
42 ,p_noa_id IN NUMBER)
43 RETURN VARCHAR2;
44 --
45 -- this function takes in a pa request id and gives back the 'Agency code Transfer to'
46 -- that is in the PAR EI (should only be called for NOA 352)
47 -- it should then be used to go into field #22
48 FUNCTION get_agency_code_to (p_pa_request_id IN NUMBER
49 ,p_noa_id IN NUMBER)
50 RETURN VARCHAR2;
51 --
52 FUNCTION get_poi (p_position_id IN NUMBER,p_effective_date IN DATE )
53 RETURN VARCHAR2;
54 PRAGMA RESTRICT_REFERENCES (get_poi, WNDS, WNPS);
55 --
56 --
57 --
58 --
59 FUNCTION get_position_nfc_agency_code (p_position_id IN NUMBER,
60 p_effective_date IN DATE)
61 RETURN VARCHAR2;
62 PRAGMA RESTRICT_REFERENCES (get_position_nfc_agency_code, WNDS, WNPS);
63 --
64 --
65 FUNCTION get_poi_eit (p_position_id IN NUMBER,p_effective_date in date,
66 p_bg_id in number)
67 RETURN VARCHAR2;
68 PRAGMA RESTRICT_REFERENCES (get_poi_eit, WNDS, WNPS);
69 --
70 --
71 --
72 FUNCTION get_nfc_agency_eit (p_position_id IN NUMBER,
73 p_effective_date in date,
74 p_bg_id in number)
75 RETURN VARCHAR2;
76 PRAGMA RESTRICT_REFERENCES (get_nfc_agency_eit, WNDS, WNPS);
77 --
78 --
79 FUNCTION get_pay_plan_grade (p_position_id IN NUMBER,p_effective_date IN DATE)
80 RETURN VARCHAR2;
81 PRAGMA RESTRICT_REFERENCES (get_pay_plan_grade, WNDS, WNPS);
82 --
83 --
84 FUNCTION get_pay_plan_grade_eit (p_position_id IN NUMBER,
85 p_effective_date in date,
86 p_bg_id in number)
87 RETURN VARCHAR2;
88 PRAGMA RESTRICT_REFERENCES (get_pay_plan_grade_eit, WNDS, WNPS);
89 --
90 --
91 FUNCTION get_pay_plan (p_position_id IN NUMBER)
92 RETURN VARCHAR2;
93 PRAGMA RESTRICT_REFERENCES (get_pay_plan, WNDS, WNPS);
94 --
95 FUNCTION get_grade_or_level (p_position_id IN NUMBER)
96 RETURN VARCHAR2;
97 PRAGMA RESTRICT_REFERENCES (get_grade_or_level, WNDS, WNPS);
98 --
99 FUNCTION get_pos_title_segment (p_business_group_id IN NUMBER)
100 RETURN VARCHAR2;
101 --
102 FUNCTION chk_position_obligated(p_position_id in number
103 ,p_date in date)
104 RETURN BOOLEAN;
105 --
106 FUNCTION opm_mandated_duty_stations
107 (p_duty_station_code in ghr_duty_stations_f.duty_station_code%TYPE)
108 RETURN BOOLEAN;
109
110 FUNCTION get_corr_cop (p_altered_pa_request_id
111 IN ghr_pa_requests.altered_pa_request_id%type)
112 RETURN NUMBER;
113
114 FUNCTION get_cop ( p_assignment_id IN per_assignments_f.assignment_id%type
115 ,p_effective_date IN date)
116
117 RETURN NUMBER;
118
119 PROCEDURE duty_station_warn (p_first_noa_id IN NUMBER
120 ,p_second_noa_id IN NUMBER
121 ,p_person_id IN NUMBER
122 ,p_form_ds_code IN ghr_duty_stations_f.duty_station_code%TYPE
123 ,p_effective_date IN DATE
124 ,p_message_set OUT NOCOPY BOOLEAN);
125
126 PROCEDURE chk_position_end_date (p_position_id IN NUMBER
127 ,p_business_group_id IN NUMBER
128 ,p_effective_date IN DATE
129 ,p_message_set OUT NOCOPY BOOLEAN);
130
131 PROCEDURE chk_position_hire_status (p_position_id IN NUMBER
132 ,p_business_group_id IN NUMBER
133 ,p_effective_date IN DATE
134 ,p_message_set OUT NOCOPY BOOLEAN);
135 --
136 -- This function is to display a warning message while processing 850 action
137 -- whenever the sum of individual components versus the total value of mddds pay is
138 -- having difference.
139 --
140 FUNCTION check_mddds_pay (p_pa_request_id IN NUMBER)
141 RETURN BOOLEAN;
142 --
143 --
144 --
145 --Bug # 9329643
146 FUNCTION get_employee_number(p_person_id IN NUMBER,p_effective_date IN DATE)
147 RETURN VARCHAR2;
148
149 --Bug # 11881714
150 FUNCTION primary_key_chk(p_table_name IN VARCHAR2,
151 p_var_col1 IN VARCHAR2 DEFAULT NULL,
152 p_value1 IN VARCHAR2 DEFAULT NULL,
153 p_var_col2 IN VARCHAR2 DEFAULT NULL,
154 p_value2 IN VARCHAR2 DEFAULT NULL,
155 p_var_col3 IN VARCHAR2 DEFAULT NULL,
156 p_value3 IN VARCHAR2 DEFAULT NULL,
157 p_num_col1 IN VARCHAR2 DEFAULT NULL,
158 p_num_value1 IN NUMBER DEFAULT NULL,
159 p_num_col2 IN VARCHAR2 DEFAULT NULL,
160 p_num_value2 IN NUMBER DEFAULT NULL,
161 p_num_col3 IN VARCHAR2 DEFAULT NULL,
162 p_num_value3 IN NUMBER DEFAULT NULL,
163 p_dt_col1 IN VARCHAR2 DEFAULT NULL,
164 p_dt_value1 IN DATE DEFAULT NULL,
165 p_dt_col2 IN VARCHAR2 DEFAULT NULL,
166 p_dt_value2 IN DATE DEFAULT NULL,
167 p_dt_col3 IN VARCHAR2 DEFAULT NULL,
168 p_dt_value3 IN DATE DEFAULT NULL) RETURN BOOLEAN;
169
170 END ghr_pa_requests_pkg2;
171 --
[Home] [Help]