[Home] [Help]
PACKAGE BODY: APPS.OZF_ACTIVITY_DENORM_PVT
Source
1 PACKAGE BODY OZF_ACTIVITY_DENORM_PVT AS
2 /* $Header: ozfvacdb.pls 120.4.12000000.2 2007/04/16 08:01:38 kdass ship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(30):='OZF_ACTIVITY_DENORM_PVT';
6
7 PROCEDURE prepare_customer_full_load
8 IS
9
10 -- CURSOR c_customer_indexes IS
11 -- SELECT index_name
12 -- FROM all_indexes
13 -- WHERE table_name = 'OZF_ACTIVITY_CUSTOMERS'
14 -- AND table_owner = 'OZF';
15
16 BEGIN
17
18 EXECUTE IMMEDIATE 'ALTER TABLE ozf_activity_customers PARALLEL';
19 -- FOR k IN c_customer_indexes LOOP
20 -- EXECUTE IMMEDIATE 'DROP INDEX ozf.' || k.index_name;
21 -- END LOOP;
22 EXECUTE IMMEDIATE 'TRUNCATE TABLE ozf_activity_customers_temp';
23 EXECUTE IMMEDIATE 'TRUNCATE TABLE ozf_activity_customers';
24
25 END;
26
27 PROCEDURE prepare_product_full_load
28 IS
29
30 -- CURSOR c_product_indexes IS
31 -- SELECT index_name
32 -- FROM all_indexes
33 -- WHERE table_name = 'OZF_ACTIVITY_PRODUCTS'
34 -- AND table_owner = 'OZF';
35
36 BEGIN
37 EXECUTE IMMEDIATE 'ALTER TABLE ozf_activity_products PARALLEL';
38 -- FOR k IN c_product_indexes LOOP
39 -- EXECUTE IMMEDIATE 'DROP INDEX ozf.' || k.index_name;
40 -- END LOOP;
41 EXECUTE IMMEDIATE 'TRUNCATE TABLE ozf_activity_products_temp';
42 EXECUTE IMMEDIATE 'TRUNCATE TABLE ozf_activity_products';
43
44 END;
45
46 PROCEDURE create_customer_indexes
47
48 IS
49
50 l_index_tablespace VARCHAR2(100);
51
52 BEGIN
53
54 EXECUTE IMMEDIATE
55 'CREATE INDEX ozf_activity_customers_n1 ON ozf_activity_customers(party_id) TABLESPACE '
56 || l_index_tablespace
57 || ' COMPUTE STATISTICS';
58
59 EXECUTE IMMEDIATE
60 'CREATE INDEX ozf_activity_customers_n2 ON ozf_activity_customers(site_use_id) TABLESPACE '
61 || l_index_tablespace
62 || ' COMPUTE STATISTICS';
63
64 EXECUTE IMMEDIATE
65 'CREATE INDEX ozf_activity_customers_n4 ON ozf_activity_customers(object_id) TABLESPACE '
66 || l_index_tablespace
67 || ' COMPUTE STATISTICS';
68 EXECUTE IMMEDIATE
69 'CREATE INDEX ozf_activity_customers_n5 ON ozf_activity_customers(object_id,party_id) TABLESPACE '
70 || l_index_tablespace
71 || ' COMPUTE STATISTICS';
72
73 END;
74
75 PROCEDURE create_product_indexes
76
77 IS
78 l_index_tablespace VARCHAR2(100);
79 BEGIN
80
81 EXECUTE IMMEDIATE
82 'CREATE INDEX ozf_activity_products_n1 ON ozf_activity_products(item,item_type) TABLESPACE '
83 || l_index_tablespace
84 || ' COMPUTE STATISTICS';
85 EXECUTE IMMEDIATE
86 'CREATE INDEX ozf_activity_products_n2 ON ozf_activity_products(object_id) TABLESPACE '
87 || l_index_tablespace
88 || ' COMPUTE STATISTICS';
89 EXECUTE IMMEDIATE
90 'CREATE INDEX ozf_activity_products_n3 ON ozf_activity_products(object_id,item,item_type) TABLESPACE '
91 || l_index_tablespace
92 || ' COMPUTE STATISTICS';
93
94 END;
95
96
97 PROCEDURE refresh_denorm(
98 ERRBUF OUT NOCOPY VARCHAR2,
99 RETCODE OUT NOCOPY VARCHAR2,
100 p_increment_flag IN VARCHAR2 := 'N',
101 p_offer_id IN NUMBER
102 )
103 IS
104 CURSOR c_app_id IS
105 SELECT application_id
106 FROM fnd_application
107 WHERE application_short_name='OZF';
108
109 CURSOR get_conc_program_id(l_app_id NUMBER) IS
110 SELECT concurrent_program_id
111 FROM fnd_concurrent_programs
112 WHERE application_id = l_app_id
113 AND concurrent_program_name = 'OZFOEPD';
114
115 CURSOR c_get_latest_comp_date(l_conc_program_id NUMBER, l_app_id NUMBER) IS
116 SELECT max(actual_completion_date)
117 FROM fnd_concurrent_requests
118 WHERE program_application_id = l_app_id
119 AND concurrent_program_id = l_conc_program_id
120 --16-APR-2007 kdass added for bug 5975207
121 AND (argument2 = NVL(p_offer_id,0) OR argument2 IS NULL)
122 AND status_code = 'C'
123 AND phase_code = 'C';
124
125 l_conc_program_id NUMBER;
126 l_app_id NUMBER;
127 l_latest_comp_date DATE;
128 x_return_status VARCHAR2(1);
129 l_status VARCHAR2(5);
130 l_industry VARCHAR2(5);
131 l_schema VARCHAR2(30);
132 l_return BOOLEAN;
133
134
135 BEGIN
136
137 OPEN c_app_id;
138 FETCH c_app_id INTO l_app_id;
139 CLOSE c_app_id;
140
141 OPEN get_conc_program_id(l_app_id);
142 FETCH get_conc_program_id INTO l_conc_program_id;
143 CLOSE get_conc_program_id;
144
145 OPEN c_get_latest_comp_date(l_conc_program_id, l_app_id);
146 FETCH c_get_latest_comp_date INTO l_latest_comp_date;
147 CLOSE c_get_latest_comp_date;
148
149 ozf_utility_pvt.write_conc_log('-- Last Refresh Date is : ' || l_latest_comp_date );
150 ozf_utility_pvt.write_conc_log('-- Increment Flag is : ' || p_increment_flag );
151 ozf_utility_pvt.write_conc_log('-- Start refresh offers --');
152
153 l_return := fnd_installation.get_app_info('OZF', l_status, l_industry, l_schema);
154
155 ozf_utility_pvt.write_conc_log('-- After getting schema name --');
156 ozf_utility_pvt.write_conc_log('-- After getting schema name --' || l_schema );
157
158 if p_increment_flag = 'N' then
159 EXECUTE IMMEDIATE 'ALTER table '||l_schema||'.ozf_activity_products_temp NOLOGGING';
160 EXECUTE IMMEDIATE 'ALTER table '||l_schema||'.ozf_activity_customers_temp NOLOGGING';
161 end if;
162
163 ozf_utility_pvt.write_conc_log('-- After altering --');
164
165 if p_increment_flag = 'N' then
166 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.ozf_activity_products_temp';
167 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.ozf_activity_customers_temp';
168 end if;
169
170 ozf_utility_pvt.write_conc_log('-- After truncating --');
171
172 OZF_OFFR_ELIG_PROD_DENORM_PVT.refresh_offers(
173 ERRBUF,
174 RETCODE,
175 x_return_status,
176 p_increment_flag,
177 NVL(l_latest_comp_date, TO_DATE('01/01/1952','MM/DD/YYYY')),
178 p_offer_id
179 );
180
181 IF x_return_status = FND_API.g_ret_sts_error
182 THEN
183 RAISE FND_API.g_exc_error;
184 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
185 RAISE FND_API.g_exc_unexpected_error;
186 END IF;
187
188 ozf_utility_pvt.write_conc_log('-- End refresh Offers --');
189 IF p_offer_id IS NULL THEN
190 ozf_utility_pvt.write_conc_log('-- Start refresh schedules --');
191 OZF_SCHEDULE_DENORM_PVT.refresh_schedules(
192 ERRBUF,
193 RETCODE,
194 x_return_status,
195 p_increment_flag,
196 NVL(l_latest_comp_date, TO_DATE('01/01/1952','MM/DD/YYYY'))
197 );
198 ozf_utility_pvt.write_conc_log('-- End refresh schedules --');
199
200
201 IF x_return_status = FND_API.g_ret_sts_error
202 THEN
203 RAISE FND_API.g_exc_error;
204 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
205 RAISE FND_API.g_exc_unexpected_error;
206 END IF;
207 END IF;
208
209
210 EXCEPTION
211 WHEN FND_API.g_exc_unexpected_error THEN
212 ozf_utility_pvt.write_conc_log('-- Unexpected Error: --'||ERRBUF);
213
214 WHEN OTHERS THEN
215 ozf_utility_pvt.write_conc_log('-- Error: --'||ERRBUF);
216
217 END;
218
219 END OZF_ACTIVITY_DENORM_PVT;