1 PACKAGE BODY GL_GLXCLVAL_XMLP_PKG AS
2 /* $Header: GLXCLVALB.pls 120.0 2007/12/27 14:49:53 vijranga noship $ */
3 function first_period_numformula(periodset in varchar2, periodtype in varchar2, first_period_year in number) return number is
4 first_period_num NUMBER;
5 dummy NUMBER;
6 begin
7 SELECT period_year, min(period_num)
8 INTO dummy, first_period_num
9 FROM gl_periods
10 WHERE
11 period_set_name = periodset
12 AND period_type = periodtype
13 AND period_year = first_period_year
14 GROUP BY period_year;
15 -- raise appplication error created by atul
16 --R(first_period_num);
17 return(first_period_num);
18 end;
19 function max_num_periodformula(periodtype1 in varchar2) return number is
20 --function max_num_periodformula(periodtype in varchar2) return number is
21 max_period NUMBER;
22 -- user_period_type VARCHAR2(30);
23 user_period_type1 VARCHAR2(30);
24 begin
25 SELECT number_per_fiscal_year, user_period_type
26 --INTO max_period, user_period_type
27 INTO max_period, user_period_type1
28 FROM gl_period_types
29 WHERE
30 period_type = periodtype1;
31 -- user_period_type := user_period_type;
32 user_period_type := user_period_type1;
33 return(max_period);
34 end;
35 function last_cal_yearformula(periodset in varchar2, periodtype in varchar2) return number is
36 max_year NUMBER;
37 begin
38 SELECT max(period_year)
39 INTO max_year
40 FROM gl_periods
41 WHERE
42 period_set_name = periodset
43 AND period_type = periodtype;
44 return(max_year);
45 end;
46 function date_lowformula(periodset in varchar2, periodtype in varchar2) return varchar2 is
47 date_low date;
48 begin
49 IF (P_start_year is NULL) THEN
50 date_low := to_date('1000/01/01','YYYY/MM/DD');
51 ELSE
52 SELECT
53 max(end_date)
54 INTO date_low
55 FROM gl_periods
56 WHERE period_set_name = periodset
57 AND period_type = periodtype
58 AND period_year < P_start_year
59 ;
60 IF (date_low is NULL) THEN
61 SELECT min(start_date)
62 INTO date_low
63 FROM gl_periods
64 WHERE period_set_name = periodset
65 AND period_type = periodtype
66 AND period_year = P_start_year;
67 END IF;
68 IF (date_low is NULL) THEN
69 date_low := to_date('1000/01/01','YYYY/MM/DD');
70 END IF;
71 END IF;
72 return(to_char(date_low,'YYYY/MM/DD'));
73 end;
74 function date_highformula(periodset in varchar2, periodtype in varchar2) return varchar2 is
75 date_high date;
76 begin
77 IF (P_end_year is NULL) THEN
78 date_high := to_date('2999/12/31','YYYY/MM/DD');
79 ELSE
80 SELECT
81 min(start_date)
82 INTO date_high
83 FROM gl_periods
84 WHERE period_set_name = periodset
85 AND period_type = periodtype
86 AND period_year > P_end_year
87 ;
88 IF (date_high is NULL) THEN
89 SELECT max(end_date)
90 INTO date_high
91 FROM gl_periods
92 WHERE period_set_name = periodset
93 AND period_type = periodtype
94 AND period_year = P_end_year;
95 END IF;
96 IF (date_high is NULL) THEN
97 date_high := to_date('2999/12/31','YYYY/MM/DD');
98 END IF;
99 END IF;
100 return(to_char(date_high,'YYYY/MM/DD'));
101 end;
102 function first_period_yearformula(periodset in varchar2, periodtype in varchar2, first_period_date in varchar2) return number is
103 first_period_year NUMBER;
104 begin
105 SELECT min(period_year)
106 INTO first_period_year
107 FROM gl_periods
108 WHERE
109 period_set_name = periodset
110 AND period_type = periodtype
111 AND start_date = to_date(first_period_date,'YYYY/MM/DD');
112 return(first_period_year);
113 end;
114 function min_quarter2formula(first_period_year in number, periodset in varchar2, periodtype in varchar2, period_year_qg in number) return number is
115 min_quarter NUMBER(15);
116 begin
117 SELECT min(decode(p.period_year,
118 first_period_year, p.quarter_num, 1))
119 INTO min_quarter
120 FROM gl_periods p
121 WHERE p.period_set_name = periodset
122 AND p.period_type = periodtype
123 AND p.period_year = period_year_qg;
124 return(min_quarter);
125 end;
126 function max_quarter2formula(periodset in varchar2, periodtype in varchar2, period_year_qg in number) return number is
127 max_quarter NUMBER(15);
128 begin
129 SELECT max(p.quarter_num)
130 INTO max_quarter
131 FROM gl_periods p
132 WHERE p.period_set_name = periodset
133 AND p.period_type = periodtype
134 AND p.period_year = period_year_qg;
135 return(max_quarter);
136 end;
137 function BeforeReport return boolean is
138 begin
139 /*SRW.USER_EXIT('FND SRWINIT');*/null;
140 TOTAL_VIOLATIONS := 0;
141 PREV_PS := '';
142 PREV_PT := '';
143 return (TRUE);
144 end;
145 function AfterReport return boolean is
146 ExecVal BOOLEAN;
147 begin
148 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
149 if (TOTAL_VIOLATIONS > 0) then
150 ExecVal := FND_CONCURRENT.set_completion_status('WARNING', '');
151 end if;
152 return (TRUE);
153 end;
154 function count_violation_qgformula(Num_Miss_Quarter_qg in number, max_quarter_qg in number) return number is
155 violation NUMBER(10);
156 begin
157 IF (Num_Miss_Quarter_qg = 0
158 AND max_quarter_qg IS NOT NULL) THEN
159 violation := 0;
160 ELSE
161 violation := 1;
162 END IF;
163 return(violation);
164 end;
165 function user_period_typeFormula return VARCHAR2 is
166 begin
167 return(user_period_type);
168 end;
169 --procedure gl_increment_violation_count (num number) is
170 procedure gl_increment_violation_count (num number , periodset varchar2) is
171 BEGIN
172 TOTAL_VIOLATIONS := TOTAL_VIOLATIONS + num;
173 PREV_PS := periodset;
174 PREV_PT := user_period_type;
175 END;
176 --Functions to refer Oracle report placeholders--
177 Function user_period_type_p return varchar2 is
178 Begin
179 return user_period_type;
180 END;
181 Function PREV_PS_p return varchar2 is
182 Begin
183 return PREV_PS;
184 END;
185 Function PREV_PT_p return varchar2 is
186 Begin
187 return PREV_PT;
188 END;
189 Function TOTAL_VIOLATIONS_p return number is
190 Begin
191 return TOTAL_VIOLATIONS;
192 END;
193 END GL_GLXCLVAL_XMLP_PKG ;
194
[Home] [Help]