APPS.GHR_SS_VIEWS_PKG dependencies on GHR_PA_REQUESTS

Line 138: from ghr_pa_requests par1

134:
135: --Bug# 12652755 commented below cursor and rewrite
136: /*cursor c_notification_id is
137: select max(par1.pa_notification_id) notification_id
138: from ghr_pa_requests par1
139: where par1.person_id = p_person_id
140: and par1.pa_notification_id is NOT Null
141: and par1.noa_family_code NOT in ('CORRECT', 'CANCEL')
142: and nvl(par1.first_noa_cancel_or_correct, 'normal') <> 'CANCEL'

Line 144: from ghr_pa_requests par2

140: and par1.pa_notification_id is NOT Null
141: and par1.noa_family_code NOT in ('CORRECT', 'CANCEL')
142: and nvl(par1.first_noa_cancel_or_correct, 'normal') <> 'CANCEL'
143: and par1.effective_date = ( select max(par2.effective_date) maxdate
144: from ghr_pa_requests par2
145: where par2.person_id = p_person_id
146: and par2.pa_notification_id is NOT Null
147: and par2.effective_date <= p_effective_date
148: and par2.noa_family_code NOT in ('CORRECT', 'CANCEL')

Line 154: from ghr_pa_requests par1

150:
151: --Begin bug 12652755
152: cursor c_notification_id is
153: select max(par1.pa_notification_id) notification_id
154: from ghr_pa_requests par1
155: where par1.person_id = p_person_id
156: and par1.pa_notification_id is NOT Null
157: and par1.noa_family_code NOT in ('CANCEL')
158: and nvl(par1.altered_pa_request_id,par1.pa_request_id) not in

Line 159: ( select par2.altered_pa_request_id from ghr_pa_requests par2

155: where par1.person_id = p_person_id
156: and par1.pa_notification_id is NOT Null
157: and par1.noa_family_code NOT in ('CANCEL')
158: and nvl(par1.altered_pa_request_id,par1.pa_request_id) not in
159: ( select par2.altered_pa_request_id from ghr_pa_requests par2
160: where par2.altered_pa_request_id = nvl(par1.altered_pa_request_id,par1.pa_request_id)
161: and par2.pa_notification_id is not null
162: and par2.first_noa_code = '001')
163: and par1.effective_date =

Line 164: (select max(effective_date) from ghr_pa_requests par3

160: where par2.altered_pa_request_id = nvl(par1.altered_pa_request_id,par1.pa_request_id)
161: and par2.pa_notification_id is not null
162: and par2.first_noa_code = '001')
163: and par1.effective_date =
164: (select max(effective_date) from ghr_pa_requests par3
165: where par3.pa_notification_id is not null
166: and par3.person_id=p_person_id
167: and par3.effective_date <= p_effective_date
168: and par3.first_noa_code <> '001'

Line 170: (select par4.altered_pa_request_id from ghr_pa_requests par4

166: and par3.person_id=p_person_id
167: and par3.effective_date <= p_effective_date
168: and par3.first_noa_code <> '001'
169: and nvl(par3.altered_pa_request_id,par3.pa_request_id) not in
170: (select par4.altered_pa_request_id from ghr_pa_requests par4
171: where par4.altered_pa_request_id = nvl(par3.altered_pa_request_id,par3.pa_request_id)
172: and par4.pa_notification_id is not null
173: and par4.first_noa_code = '001'));
174: --End bug# 12652755

Line 178: from ghr_pa_requests g

174: --End bug# 12652755
175:
176: cursor c_request_id (c_notification_id number) is
177: select pa_request_id
178: from ghr_pa_requests g
179: where g.pa_notification_id = c_notification_id ;
180:
181: l_notification_id ghr_pa_requests.pa_notification_id%type;
182: l_pa_request_id ghr_pa_requests.pa_request_id%type;

Line 181: l_notification_id ghr_pa_requests.pa_notification_id%type;

177: select pa_request_id
178: from ghr_pa_requests g
179: where g.pa_notification_id = c_notification_id ;
180:
181: l_notification_id ghr_pa_requests.pa_notification_id%type;
182: l_pa_request_id ghr_pa_requests.pa_request_id%type;
183: l_proc varchar2(72);
184:
185:

Line 182: l_pa_request_id ghr_pa_requests.pa_request_id%type;

178: from ghr_pa_requests g
179: where g.pa_notification_id = c_notification_id ;
180:
181: l_notification_id ghr_pa_requests.pa_notification_id%type;
182: l_pa_request_id ghr_pa_requests.pa_request_id%type;
183: l_proc varchar2(72);
184:
185:
186: BEGIN