1 PACKAGE BODY oki_utl_pvt as
2 /* $Header: OKIRUTLB.pls 115.28 2003/11/24 08:25:15 kbajaj ship $ */
3
4 --------------------------------------------------------------------------------
5 -- Modification History
6 -- 25-Aug-2001 mezra Removed uppercase from defaulting logic for
7 -- period set, period name and period type
8 -- due to release JTF 11.5.5.1.1.A
9 -- 05-Sep-2001 mezra Change length of l_message in get_rfh_date.
10 -- 20-Sep-2001 mezra Added get_aging_label function.
11 -- 27-Sep-2001 mezra Fixed get_aging_label to return the correct header
12 -- labels
13 -- Change get_period_set, get_period_type,
14 -- get_period_name to take a parameter value
15 -- that is defaulted to to null.
16 -- 01-Oct-2001 mezra Added function to determine the start and
17 -- end value of the age grouping.
18 -- Added function to get the bin title for the
19 -- aging detail bin.
20 -- 19-Oct-2001 rpotnuru Commented dbms_output statements
21 -- 18-Dec-2001 mezra Removed dbms_output statements.
22 -- Changed get_rfh_date function to support parameters
23 -- as defined by DCF.
24 -- 19-Dec-2001 mezra Added function get_aging_label1, get_aging_label2,
25 -- get_aging_label3, get_aging_label4 to get the aging
26 -- label for column.
27 -- 26-Dec-2001 mezra Added cursors that are used across packages:
28 -- g_tactk_all_csr, g_tactk_by_org_csr,
29 -- g_rnwl_oppty_all_csr, g_rnwl_oppty_by_org_csr,
30 -- g_k_exp_in_qtr_all_csr, g_k_exp_in_qtr_by_org_csr,
31 -- g_org_csr.
32 -- Added get_bin_title2 to retrieve the title for the
33 -- drilldown bins.
34 -- 26-Dec-2001 mezra Added function to default the build summary date.
35 -- 04-Jan-2002 mezra Remove all functions and procedures for the bin.
36 -- 20-Mar-2002 mezra Added logic to set the previous year summary
37 -- build date.
38 -- 27-MAR-2002 mezra Changed total active contracts cursor to retrieve
39 -- data at the covered product line level
40 -- 02-APR-2002 mezra Changed total active contracts cursor logic to
41 -- include contracts that are expiring on the
42 -- same day as the summary build date.
43 -- 08-Apr-2002 mezra Added g_bin_disp_lkup_csr cursor to retrieve bin
44 -- display lookup details.
45 -- 26-NOV-2002 rpotnuru NOCOPY Changes
46 --
47 -- 28-Oct-2003 axraghav populated null for Organization name in g_org_csr as
48 -- this name is tobe resolved in the views
49 --------------------------------------------------------------------------------
50
51 -- Global cursor declaration
52
53 -- Cursor to get the gl_periods based on period_set_name,
54 -- period_type and p_summary_build_date between the start_date
55 -- and end_date
56 CURSOR g_glpr_csr
57 (
58 p_period_set_name IN VARCHAR2
59 , p_period_type IN vARCHAR2
60 , p_summary_build_date IN DATE
61 ) RETURN gl_periods%ROWTYPE IS
62 SELECT *
63 FROM gl_periods glpr
64 WHERE glpr.adjustment_period_flag = 'N'
65 AND p_summary_build_date BETWEEN glpr.start_date AND glpr.end_date
66 AND glpr.period_set_name LIKE NVL(p_period_set_name, '%')
67 AND glpr.period_type LIKE NVL(p_period_type, '%')
68 ;
69
70 -- Retrieve the total active contracts
71 CURSOR g_tactk_all_csr
72 (
73 p_start_date IN DATE
74 ) RETURN g_tactk_all_csr_row IS
75 SELECT NVL(SUM(cpl.base_price_negotiated), 0) value
76 , COUNT(DISTINCT(shd.chr_id)) contract_count
77 FROM oki_sales_k_hdrs shd
78 , oki_cov_prd_lines cpl
79 WHERE shd.chr_id = cpl.chr_id
80 AND cpl.sts_code = 'ACTIVE'
81 AND shd.date_signed <= p_start_date
82 AND shd.date_approved <= p_start_date
83 AND shd.start_date <= p_start_date
84 AND shd.end_date >= p_start_date
85 AND ( shd.date_terminated IS NULL
86 OR shd.date_terminated > p_start_date )
87 AND shd.base_contract_amount BETWEEN 0
88 AND oki_utl_pub.g_contract_limit
89 ;
90
91 -- Retrieve the total active contracts by organization
92 CURSOR g_tactk_by_org_csr
93 (
94 p_start_date IN DATE
95 , p_authoring_org_id IN NUMBER
96 ) RETURN g_tactk_by_org_csr_row IS
97 SELECT NVL(SUM(cpl.base_price_negotiated), 0) value
98 , COUNT(DISTINCT cpl.chr_id) contract_count
99 , shd.authoring_org_id authoring_org_id
100 FROM oki_sales_k_hdrs shd
101 , oki_cov_prd_lines cpl
102 WHERE shd.chr_id = cpl.chr_id
103 AND cpl.sts_code = 'ACTIVE'
104 AND shd.date_signed <= p_start_date
105 AND shd.date_approved <= p_start_date
106 AND shd.start_date <= p_start_date
107 AND shd.end_date >= p_start_date
108 AND ( shd.date_terminated IS NULL
109 OR shd.date_terminated > p_start_date )
110 AND shd.base_contract_amount BETWEEN 0
111 AND oki_utl_pub.g_contract_limit
112 AND shd.authoring_org_id = p_authoring_org_id
113 GROUP BY shd.authoring_org_id
114 ORDER BY value
115 ;
116
117 -- Retrieve the renewal opportunity
118 CURSOR g_rnwl_oppty_all_csr
119 (
120 p_qtr_end_date IN DATE
121 ) RETURN g_rnwl_oppty_all_csr_row IS
122 SELECT NVL(SUM(shd.base_contract_amount), 0) value
123 , COUNT(shd.chr_id) contract_count
124 FROM oki_sales_k_hdrs shd
125 WHERE shd.start_date <= p_qtr_end_date
126 AND shd.is_new_yn IS NULL
127 AND shd.date_signed IS NULL
128 AND shd.date_canceled IS NULL
129 AND shd.contract_amount BETWEEN 0
130 AND oki_utl_pub.g_contract_limit
131 ;
132
133
134 -- Retrieve the renewal opportunity by organization
135 CURSOR g_rnwl_oppty_by_org_csr
136 (
137 p_qtr_end_date IN DATE
138 , p_authoring_org_id IN NUMBER
139 ) RETURN g_rnwl_oppty_by_org_csr_row IS
140 SELECT NVL(SUM(shd.base_contract_amount), 0) value
141 , COUNT(shd.chr_id) contract_count
142 FROM oki_sales_k_hdrs shd
143 WHERE shd.start_date <= p_qtr_end_date
144 AND shd.is_new_yn IS NULL
145 AND shd.date_signed IS NULL
146 AND shd.date_canceled IS NULL
147 AND shd.contract_amount BETWEEN 0
148 AND oki_utl_pub.g_contract_limit
149 AND shd.authoring_org_id = p_authoring_org_id
150 GROUP BY shd.authoring_org_id
151 ORDER BY value
152 ;
153
154 -- Retrieve contracts that are expiring in the quarter
155 CURSOR g_k_exp_in_qtr_all_csr
156 (
157 p_qtr_start_date IN DATE
158 , p_qtr_end_date IN DATE
159 ) RETURN g_k_exp_in_qtr_all_csr_row IS
160 SELECT NVL(SUM(base_contract_amount), 0) value
161 , COUNT(shd.chr_id) contract_count
162 FROM oki_sales_k_hdrs shd
163 WHERE shd.date_signed <= p_qtr_start_date
164 AND shd.date_approved <= p_qtr_end_date
165 AND shd.end_date BETWEEN p_qtr_start_date AND p_qtr_end_date
166 AND shd.date_terminated IS NULL
167 AND shd.base_contract_amount BETWEEN 0
168 AND oki_utl_pub.g_contract_limit
169 ;
170
171 -- Retrieve contracts that are expiring in the quarter
172 CURSOR g_k_exp_in_qtr_by_org_csr
173 (
174 p_qtr_start_date IN DATE
175 , p_qtr_end_date IN DATE
176 , p_authoring_org_id IN NUMBER
177 ) RETURN g_k_exp_in_qtr_by_org_csr_row IS
178 SELECT NVL(SUM(base_contract_amount), 0) value
179 , COUNT(shd.chr_id) contract_count
180 FROM oki_sales_k_hdrs shd
181 WHERE shd.date_signed <= p_qtr_start_date
182 AND shd.date_approved <= p_qtr_end_date
183 AND shd.end_date BETWEEN p_qtr_start_date AND p_qtr_end_date
184 AND shd.date_terminated IS NULL
185 AND shd.base_contract_amount BETWEEN 0
186 AND oki_utl_pub.g_contract_limit
187 AND shd.authoring_org_id = p_authoring_org_id
188 ;
189 /*
190 -- Contracts that have been renewed in the quarter
191 CURSOR g_k_rnwed_csr
192 (
193 p_qtr_start_date IN DATE
194 , p_qtr_end_date IN DATE
195 , p_authoring_org_id IN NUMBER
196
197 ) IS
198 SELECT NVL(SUM(base_contract_amount), 0) value
199 , COUNT(shd.chr_id) contract_count
200 FROM oki_sales_k_hdrs shd
201 WHERE shd.is_new_yn IS NULL
202 AND shd.date_signed IS NOT NULL
203 AND shd.start_date BETWEEN p_qtr_start_date AND p_qtr_end_date
204 AND GREATEST(shd.date_signed, shd.date_approved) <= p_qtr_end_date
205 AND shd.base_contract_amount BETWEEN 0
206 AND oki_utl_pub.g_contract_limit
207 AND shd.authoring_org_id = p_authoring_org_id
208 ;
209 */
210
211 -- Retrieve the organization
212
213 /*11510 Change Null the Organization name as this resolved in the views*/
214
215 CURSOR g_org_csr RETURN g_org_csr_row IS
216 SELECT DISTINCT shd.authoring_org_id authoring_org_id
217 , NULL organization_name
218 FROM oki_sales_k_hdrs shd
219 ;
220
221 -- Retrieve the bin metadata
222 CURSOR g_bin_disp_lkup_csr
223 ( p_bin_id IN VARCHAR2
224 , p_bin_code IN VARCHAR2
225 ) RETURN g_bin_disp_lkup_csr_row IS
226 SELECT bdl.bin_code_meaning bin_code_meaning
227 , bdl.bin_code_seq bin_code_seq
228 FROM oki_bin_disp_lkup bdl
229 WHERE bdl.bin_id = p_bin_id
230 AND bdl.bin_code = p_bin_code
231 ;
232
233
234 --------------------------------------------------------------------------------
235 -- Procedure to get the GL period start and end dates.
236 --------------------------------------------------------------------------------
237 PROCEDURE get_gl_period_date
238 (
239 x_retcode OUT NOCOPY VARCHAR2
240 ) IS
241
242 -- Cursor to set the GL period start and end dates
243 CURSOR l_gl_period_csr IS
244 SELECT glpr.*
245 FROM
246 gl_periods glpr
247 , gl_sets_of_books sob
248 , hr_organization_information oin
249 WHERE
250 oki_utl_pub.g_summary_build_date BETWEEN glpr.start_date
251 AND glpr.end_date
252 AND glpr.period_set_name = sob.period_set_name
253 AND glpr.period_type = sob.accounted_period_type
254 AND glpr.adjustment_period_flag = 'N'
255 AND sob.set_of_books_id = oin.org_information3
256 AND oin.org_information_context = 'Operating Unit Information'
257 AND oin.organization_id = fnd_profile.value('OKI_BASE_ORG_ID')
258 ;
259 rec_l_gl_period_csr l_gl_period_csr%ROWTYPE ;
260
261 BEGIN
262 OPEN l_gl_period_csr ;
263 FETCH l_gl_period_csr INTO rec_l_gl_period_csr ;
264 IF l_gl_period_csr%FOUND THEN
265 -- Set current year start / end information
266 oki_utl_pub.g_glpr_start_date := rec_l_gl_period_csr.start_date ;
267 oki_utl_pub.g_glpr_end_date := rec_l_gl_period_csr.end_date ;
268 oki_utl_pub.g_glpr_qtr_start_date := rec_l_gl_period_csr.quarter_start_date ;
269 oki_utl_pub.g_glpr_qtr_end_date :=
270 ADD_MONTHS(oki_utl_pub.g_glpr_qtr_start_date, 3 ) - 1 ;
271 oki_utl_pub.g_glpr_qtr_num := rec_l_gl_period_csr.quarter_num ;
272 oki_utl_pub.g_glpr_year_start_date := rec_l_gl_period_csr.year_start_date ;
273 oki_utl_pub.g_glpr_year_end_date :=
274 ADD_MONTHS(TRUNC(oki_utl_pub.g_glpr_year_start_date,'YYYY' ), 12 ) -1 ;
275 oki_utl_pub.g_period_year := rec_l_gl_period_csr.period_year ;
276 oki_utl_pub.g_week_start_date := ( oki_utl_pub.g_summary_build_date -
277 TO_NUMBER(TO_CHAR(oki_utl_pub.g_summary_build_date,'D' )) -1) ;
278
279 -- Set prior year start / end information
280 oki_utl_pub.g_py_summary_build_date
281 := ADD_MONTHS(oki_utl_pub.g_summary_build_date, -12) ;
282 oki_utl_pub.g_py_glpr_start_date
283 := ADD_MONTHS(oki_utl_pub.g_glpr_start_date, -12 ) ;
284 oki_utl_pub.g_py_glpr_end_date
285 := ADD_MONTHS(oki_utl_pub.g_glpr_end_date, -12) ;
286 oki_utl_pub.g_py_glpr_qtr_start_date
287 := ADD_MONTHS(oki_utl_pub.g_glpr_qtr_start_date, -12 ) ;
288 oki_utl_pub.g_py_glpr_qtr_end_date
289 := ADD_MONTHS(oki_utl_pub.g_glpr_qtr_end_date, -12 ) ;
290 oki_utl_pub.g_py_glpr_qtr_num
291 := oki_utl_pub.g_glpr_qtr_num ;
292 oki_utl_pub.g_py_glpr_year_start_date
293 := ADD_MONTHS(oki_utl_pub.g_glpr_year_start_date, -12 ) ;
294 oki_utl_pub.g_py_glpr_year_end_date
295 := ADD_MONTHS(oki_utl_pub.g_glpr_year_end_date, -12 ) ;
296 oki_utl_pub.g_py_period_year
297 := oki_utl_pub.g_period_year -1 ;
298 oki_utl_pub.g_py_week_start_date
299 := ADD_MONTHS(oki_utl_pub.g_week_start_date, -12 ) ;
300 END IF ;
301 CLOSE l_gl_period_csr ;
302 END get_gl_period_date ;
303
304 END oki_utl_pvt ;
[Home] [Help]