[Home] [Help]
PACKAGE BODY: APPS.PSP_PSPARREP_XMLP_PKG
Source
1 PACKAGE BODY PSP_PSPARREP_XMLP_PKG AS
2 /* $Header: PSPARREPB.pls 120.5 2007/10/29 07:19:48 amakrish noship $ */
3
4 function BeforeReport return boolean is
5 begin
6
7 --hr_standard.event('BEFORE REPORT');
8 return (TRUE);
9 end;
10
11 function CF_Begin_period_nameFormula return Char is
12
13 l_period_name CHAR(70);
14 l_time_period_id NUMBER(15);
15
16 Cursor c_begin_period_id IS
17 SELECT min(PTP1.time_period_id) time_period_id
18 FROM PER_TIME_PERIODS PTP1
19 WHERE PTP1.payroll_id = nvl(p_payroll_id,PTP1.payroll_id);
20
21 x_period_id c_begin_period_id%ROWTYPE;
22
23 Cursor c_period IS
24 SELECT PTP.period_name period_name
25 FROM PER_TIME_PERIODS PTP
26 WHERE PTP.time_period_id = l_time_period_id;
27
28 x_period_name c_period%ROWTYPE;
29
30 begin
31
32 IF p_begin_period IS NULL THEN
33 BEGIN
34 OPEN c_begin_period_id;
35 FETCH c_begin_period_id INTO x_period_id;
36 l_time_period_id := x_period_id.time_period_id;
37 CLOSE c_begin_period_id;
38
39 OPEN c_period;
40 FETCH c_period INTO x_period_name;
41 l_period_name := x_period_name.period_name;
42 CLOSE c_period;
43
44 return(l_period_name);
45 END ;
46 ELSE
47 BEGIN
48 l_time_period_id := p_begin_period;
49 OPEN c_period;
50 FETCH c_period INTO x_period_name;
51 l_period_name := x_period_name.period_name;
52 CLOSE c_period;
53
54 return(l_period_name);
55 END;
56 END IF;
57
58 EXCEPTION
59 When no_data_found Then
60 return(NULL);
61 end;
62
63 function CF_End_period_nameFormula return Char is
64 l_period_name CHAR(70);
65 l_time_period_id NUMBER(15);
66
67 Cursor c_end_period_id IS
68 SELECT max(PTP1.time_period_id) time_period_id
69 FROM PER_TIME_PERIODS PTP1
70 WHERE PTP1.payroll_id = nvl(p_payroll_id,PTP1.payroll_id);
71
72 x_period_id c_end_period_id%ROWTYPE;
73
74 Cursor c_period IS
75 SELECT PTP.period_name period_name
76 FROM PER_TIME_PERIODS PTP
77 WHERE PTP.time_period_id = l_time_period_id;
78
79 x_period_name c_period%ROWTYPE;
80
81 begin
82
83 IF p_end_period IS NULL THEN
84 BEGIN
85 OPEN c_end_period_id;
86 FETCH c_end_period_id INTO x_period_id;
87 l_time_period_id := x_period_id.time_period_id;
88 CLOSE c_end_period_id;
89
90 OPEN c_period;
91 FETCH c_period INTO x_period_name;
92 l_period_name := x_period_name.period_name;
93 CLOSE c_period;
94
95 return(l_period_name);
96 END ;
97 ELSE
98 BEGIN
99 l_time_period_id := p_end_period;
100 OPEN c_period;
101 FETCH c_period INTO x_period_name;
102 l_period_name := x_period_name.period_name;
103 CLOSE c_period;
104
105 return(l_period_name);
106 END;
107 END IF;
108
109 EXCEPTION
110 When no_data_found Then
111 return(NULL);
112 end;
113
114 function CF_No_data_foundFormula return Number is
115 l_no_lines NUMBER(15);
116 l_no_dist_lines NUMBER(15);
117 l_no_enc_lines NUMBER(15);
118
119 CURSOR c_distribution IS
120 SELECT COUNT(*) no_dist_lines
121 FROM PSP_PAYROLL_CONTROLS PPC,
122 PER_TIME_PERIODS PTP,
123 PAY_PAYROLLS_F PPF
124 WHERE PPC.payroll_id = PPF.payroll_id
125 and PPC.time_period_id = PTP.time_period_id
126 and PPC.payroll_id = nvl(p_payroll_id,PPF.payroll_id)
127 and PTP.time_period_id >= nvl(p_begin_period,PTP.time_period_id)
128 and PTP.time_period_id <= nvl(p_end_period,PTP.time_period_id)
129 and PPC.archive_flag = 'Y';
130
131 l_distribution c_distribution%ROWTYPE;
132
133 CURSOR c_encumbrance IS
134 SELECT COUNT(*) no_enc_lines
135 FROM PSP_ENC_CONTROLS PEC,
136 PER_TIME_PERIODS PTP,
137 PAY_PAYROLLS_F PPF
138 WHERE PEC.payroll_id = PPF.payroll_id
139 and PEC.time_period_id = PTP.time_period_id
140 and PEC.payroll_id = nvl(p_payroll_id,PPF.payroll_id)
141 and PTP.time_period_id >= nvl(p_begin_period,PTP.time_period_id)
142 and PTP.time_period_id <= nvl(p_end_period,PTP.time_period_id)
143 and PEC.archive_flag = 'Y';
144
145 l_encumbrance c_encumbrance%ROWTYPE;
146 begin
147 OPEN c_distribution;
148 FETCH c_distribution INTO l_distribution;
149 l_no_dist_lines := l_distribution.no_dist_lines;
150 CLOSE c_distribution;
151
152 OPEN c_encumbrance;
153 FETCH c_encumbrance INTO l_encumbrance;
154 l_no_enc_lines := l_encumbrance.no_enc_lines;
155 CLOSE c_encumbrance;
156
157 l_no_lines := l_no_dist_lines + l_no_enc_lines;
158 return(l_no_lines);
159 end;
160
161 function BeforePForm return boolean is
162 begin
163 --ORIENTATION := 'LANDSCAPE';
164 return (TRUE);
165 end;
166
167 function AfterReport return boolean is
168 begin
169 --hr_standard.event('AFTER REPORT');
170 return (TRUE);
171 end;
172
173 --Functions to refer Oracle report placeholders--
174
175 END PSP_PSPARREP_XMLP_PKG ;