[Home] [Help]
PACKAGE BODY: APPS.BIM_POPDATES_PKG
Source
1 PACKAGE BODY BIM_POPDATES_PKG AS
2 /*$Header: bimdateb.pls 120.2 2005/11/09 01:59:23 arvikuma noship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(20) := 'BIM_POPDATES_PKG';
5 g_file_name CONSTANT VARCHAR2(20) := 'bimdateb.pls';
6
7 PROCEDURE POP_INTL_DATES(p_input_date DATE) IS
8 CURSOR min_gl_date IS
9 SELECT TRUNC(min(start_date))
10 FROM gl_periods
11 WHERE period_set_name = fnd_profile.value('AMS_CAMPAIGN_DEFAULT_CALENDER');
12
13 CURSOR max_gl_date IS
14 SELECT TRUNC(max(end_date))
15 FROM gl_periods
16 WHERE period_set_name = fnd_profile.value('AMS_CAMPAIGN_DEFAULT_CALENDER');
17
18 cursor min_object_date (p_date DATE) is
19 select trunc(min(de)) from (
20 select min(bu1.approval_date) de
21 FROM ozf_funds_all_b o,
22 ozf_act_budgets BU1
23 WHERE o.start_date_active > p_date
24 and o.status_code in ('ACTIVE','CANCELLED', 'CLOSED')
25 AND bu1.transfer_type in ('TRANSFER', 'REQUEST')
26 AND bu1.approval_date <=trunc(o.start_date_active)
27 AND bu1.status_code = 'APPROVED'
28 AND bu1.arc_act_budget_used_by = 'FUND'
29 AND bu1.act_budget_used_by_id = o.fund_id
30 AND bu1.budget_source_type ='FUND'
31 union all
32 select min(bu2.approval_date) de
33 FROM ozf_funds_all_b o,
34 ozf_act_budgets BU2
35 WHERE o.start_date_active > p_date --between p_start_datel and p_end_datel
36 AND o.status_code in ('ACTIVE','CANCEL', 'CLOSED')
37 AND bu2.approval_date <=trunc(o.start_date_active)
38 AND bu2.status_code= 'APPROVED'
39 AND bu2.arc_act_budget_used_by = 'FUND'
40 AND bu2.budget_source_type ='FUND'
41 AND bu2.budget_source_id = o.fund_id
42 union all
43 SELECT min(a.approval_date) de
44 FROM ozf_act_budgets a,
45 ams_campaigns_all_b b
46 WHERE a.budget_source_type ='FUND'
47 AND a.ARC_ACT_BUDGET_USED_BY = 'CAMP'
48 AND a.status_code ='APPROVED'
49 and a.act_budget_used_by_id=b.campaign_id
50 and b.actual_exec_start_date>p_date
51 and b.status_code in ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
52 union all
53 SELECT min(a.approval_date)
54 FROM ozf_act_budgets a,
55 ams_event_headers_all_b b
56 WHERE a.budget_source_type ='FUND'
57 AND a.ARC_ACT_BUDGET_USED_BY = 'EVEH'
58 AND a.status_code ='APPROVED'
59 and a.act_budget_used_by_id=b.event_header_id
60 and b.active_from_date>p_date
61 and b.system_status_code in ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
62 union all
63 SELECT min(a.last_reg_status_date) de
64 FROM ams_event_registrations A,
65 ams_event_headers_all_b b,
66 ams_event_offers_all_b c
67 where b.active_from_date>p_date
68 and b.system_status_code in ('ACTIVE', 'CANCELLED', 'COMPLETED')
69 and a.event_offer_id = c.event_offer_id
70 and c.event_header_id = b.event_header_id );
71
72 l_min_date DATE;
73 l_end_date DATE := sysdate-1;
74 l_date DATE;
75 l_status VARCHAR2(5);
76 l_industry VARCHAR2(5);
77 l_schema VARCHAR2(30);
78 l_return BOOLEAN;
79
80 BEGIN
81 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
82
83 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_intl_dates';
84
85 OPEN max_gl_date;
86 FETCH max_gl_date into l_date;
87 CLOSE max_gl_date;
88
89 IF l_date> l_end_date THEN
90 l_end_date :=l_date;
91 END IF;
92
93 OPEN min_object_date(p_input_date) ;
94 FETCH min_object_date into l_min_date;
95 --ams_utility_pvt.write_conc_log('Inside pop dates: starting from '||l_min_date);
96
97 IF l_min_date is null then
98 l_min_date :=p_input_date;
99 END IF;
100 -- IF p_input_date <l_min_date then
101 -- l_min_date :=p_input_date;
102 -- END IF;
103 --ams_utility_pvt.write_conc_log('Before the loop');
104 WHILE l_min_date < l_end_date+1 LOOP
105 BEGIN
106 INSERT
107 INTO BIM_INTL_DATES fdf (
108 TRDATE
109 ,FISCAL_MONTH
110 ,MONTH_FLAG
111 ,FISCAL_QTR
112 ,QTR_FLAG
113 ,FISCAL_YEAR
114 ,FISCAL_MONTH_START
115 ,FISCAL_MONTH_END
116 ,MONTH_NUM
117 ,FISCAL_QTR_START
118 ,FISCAL_QTR_END
119 ,QTR_NUM
120 ,FISCAL_YEAR_START
121 ,FISCAL_YEAR_END
122 ,FISCAL_ROLL_YEAR_START
123 ,PRE_FISCAL_MONTH_START
124 ,PRE_FISCAL_MONTH_END
125 ,PRE_FISCAL_QTR_START
126 ,PRE_FISCAL_QTR_END
127 ,PRE_FISCAL_YEAR_START
128 ,PRE_FISCAL_YEAR_END
129 ,PRE_FISCAL_ROLL_YEAR_START
130 ,PRE_FISCAL_ROLL_YEAR_END
131 ,YEAR_FLAG
132 )
133 SELECT
134 l_min_date
135 ,BIM_SET_OF_BOOKS.GET_FISCAL_MONTH(l_min_date,204)
136 ,'N'
137 ,BIM_SET_OF_BOOKS.GET_FISCAL_QTR(l_min_date,204)
138 ,'N'
139 ,BIM_SET_OF_BOOKS.GET_FISCAL_YEAR(l_min_date,204)
140 ,BIM_SET_OF_BOOKS.GET_FISCAL_MONTH_START(l_min_date,204)
141 ,BIM_SET_OF_BOOKS.GET_FISCAL_MONTH_END(l_min_date,204)
142 ,BIM_SET_OF_BOOKS.GET_FISCAL_MONTH_NUM(l_min_date,204)
143 ,BIM_SET_OF_BOOKS.GET_FISCAL_QTR_START(l_min_date,204)
144 ,BIM_SET_OF_BOOKS.GET_FISCAL_QTR_END(l_min_date,204)
145 ,BIM_SET_OF_BOOKS.GET_FISCAL_QTR_NUM(l_min_date,204)
146 ,BIM_SET_OF_BOOKS.GET_FISCAL_YEAR_START(l_min_date,204)
147 ,BIM_SET_OF_BOOKS.GET_FISCAL_YEAR_END(l_min_date,204)
148 ,BIM_SET_OF_BOOKS.GET_FISCAL_ROLL_YEAR_START(l_min_date,204)
149 ,BIM_SET_OF_BOOKS.GET_PRE_FISCAL_MONTH_START(l_min_date,204)
150 ,BIM_SET_OF_BOOKS.GET_PRE_FISCAL_MONTH_END(l_min_date,204)
151 ,BIM_SET_OF_BOOKS.GET_PRE_FISCAL_QTR_START(l_min_date,204)
152 ,BIM_SET_OF_BOOKS.GET_PRE_FISCAL_QTR_END(l_min_date,204)
153 ,BIM_SET_OF_BOOKS.GET_PRE_FISCAL_YEAR_START(l_min_date,204)
154 ,BIM_SET_OF_BOOKS.GET_PRE_FISCAL_YEAR_END(l_min_date,204)
155 ,BIM_SET_OF_BOOKS.GET_PRE_FISCAL_ROLL_YEAR_START(l_min_date,204)
156 ,BIM_SET_OF_BOOKS.GET_PRE_FISCAL_ROLL_YEAR_END(l_min_date,204)
157 ,'N'
158 FROM DUAL;
159 l_min_date := l_min_date +1;
160 EXCEPTION
161 WHEN OTHERS THEN
162 ams_utility_pvt.write_conc_log('Error inserting bim_intl_dates ' || sqlerrm(sqlcode));
163 RAISE FND_API.g_exc_error;
164 END;
165 END LOOP;
166 --ams_utility_pvt.write_conc_log('After the loop');
167 --ams_utility_pvt.write_conc_log('Update the month flag');
168 BEGIN
169 update bim_intl_dates p set month_flag = 'Y'
170 where p.trdate in (SELECT min(trdate)
171 FROM bim_intl_dates
172 GROUP BY fiscal_month);
173 EXCEPTION
174 WHEN OTHERS THEN
175 ams_utility_pvt.write_conc_log('Error updating bim_intl_dates ' || sqlerrm(sqlcode));
176 RAISE FND_API.g_exc_error;
177 END;
178
179 --ams_utility_pvt.write_conc_log('Update the quarter flag');
180 BEGIN
181 update bim_intl_dates p set qtr_flag = 'Y'
182 where p.trdate in (SELECT min(trdate)
183 FROM bim_intl_dates
184 GROUP BY fiscal_qtr);
185 EXCEPTION
186 WHEN OTHERS THEN
187 ams_utility_pvt.write_conc_log('Error updating bim_intl_dates ' || sqlerrm(sqlcode));
188 RAISE FND_API.g_exc_error;
189 END;
190 --ams_utility_pvt.write_conc_log('Update the year flag');
191 BEGIN
192 update bim_intl_dates p set year_flag = 'Y'
193 where p.trdate in (SELECT min(trdate)
194 FROM bim_intl_dates
195 GROUP BY fiscal_year);
196 EXCEPTION
197 WHEN OTHERS THEN
198 ams_utility_pvt.write_conc_log('Error updating bim_intl_dates ' || sqlerrm(sqlcode));
199 RAISE FND_API.g_exc_error;
200 END;
201
202 DELETE FROM bim_rep_history
203 WHERE object='DATES';
204 INSERT INTO
205 bim_rep_history
206 (creation_date,
207 last_update_date,
208 created_by,
209 last_updated_by,
210 object,
211 object_last_updated_date)
212 VALUES
213 (sysdate,
214 sysdate,
215 -1,
216 -1,
217 'DATES',
218 sysdate);
219
220 commit;
221 --ams_utility_pvt.write_conc_log('Successfully finished pop date');
222 EXCEPTION
223 WHEN OTHERS THEN
224 ams_utility_pvt.write_conc_log('Error in procedure' || sqlerrm(sqlcode));
225 RAISE FND_API.g_exc_unexpected_error;
226 END POP_INTL_DATES;
227 END BIM_POPDATES_PKG;