1 PACKAGE BODY hxc_missing_tcard_report AS
2 /* $Header: hxcmistc.pkb 115.3 2003/11/02 22:26:16 namrute ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |-------------------------< check_assignment_set >-------------------------|
6 -- ----------------------------------------------------------------------------
7 FUNCTION check_assignment_set
8 (p_assignment_set_id in number,
9 p_assignment_id in number)
10 Return varchar2 is
11
12 l_flag varchar2(5):= 'N';
13
14 CURSOR csr_chk_asg_set IS
15 SELECT 'Y'
16 FROM dual
17 WHERE EXISTS (
18 SELECT 'x'
19 FROM hr_assignment_set_amendments has
20 WHERE has.assignment_set_id = p_assignment_set_id
21 AND has.assignment_id = p_assignment_id);
22
23
24 begin
25
26 OPEN csr_chk_asg_set;
27 FETCH csr_chk_asg_set INTO l_flag;
28 CLOSE csr_chk_asg_set;
29
30 return l_flag;
31
32 end check_assignment_set;
33
34 Function get_vendor_name
35 (p_start_date in date,
36 p_end_date in date,
37 p_resource_id in number
38 )
39 Return varchar2 IS
40
41 cursor c_vendor is
42 SELECT pov.vendor_name VENDOR_NAME,
43 a.vendor_id VENDOR_ID
44 FROM per_assignments_f a,
45 per_all_people_f pp,
46 po_vendors pov
47 WHERE a.assignment_type ='C'
48 AND a.person_id = pp.person_id
49 AND p_start_date <= a.effective_end_date
50 AND p_end_date >= a.effective_start_date
51 AND p_start_date <= pp.effective_end_date
52 AND p_end_date >= pp.effective_start_date
53 and pp.person_id=p_resource_id
54 AND a.vendor_id = pov.vendor_id(+)
55 AND a.vendor_id is not null
56 group by pov.vendor_name,a.vendor_id;
57
58 l_vender_list varchar2(2000) :=NULL;
59 Begin
60
61
62 FOR X IN c_vendor LOOP
63
64 if l_vender_list is null then
65 l_vender_list:=x.vendor_name;
66 else
67 l_vender_list:=l_vender_list||'&'||x.vendor_name;
68 end if;
69
70 END LOOP;
71
72 return(l_vender_list);
73
74 End get_vendor_name;
75
76 Function check_vendor_exists
77 (p_start_date in date,
78 p_end_date in date,
79 p_assignment_id in NUMBER,
80 p_resource_id in number,
81 p_vendor_id in number
82 )
83 Return varchar2 IS
84
85 cursor c_vendor is
86 SELECT distinct 'Y'
87 FROM per_assignments_f a,
88 per_all_people_f pp,
89 po_vendors pov
90 WHERE a.assignment_type ='C'
91 AND a.person_id = pp.person_id
92 AND p_start_date <= a.effective_end_date
93 AND p_end_date >= a.effective_start_date
94 AND p_start_date <= pp.effective_end_date
95 AND p_end_date >= pp.effective_start_date
96 and pp.person_id=p_resource_id
97 AND a.vendor_id = pov.vendor_id
98 and a.vendor_id=p_vendor_id
99 AND a.vendor_id is not null;
100
101 l_vender_exists varchar2(2000) :=NULL;
102 Begin
103
104 l_vender_exists :='N';
105
106 open c_vendor;
107 fetch c_vendor into l_vender_exists;
108 close c_vendor;
109
110 return(l_vender_exists);
111
112 End check_vendor_exists;
113
114
115 END hxc_missing_tcard_report;
[Home] [Help]