[Home] [Help]
PACKAGE BODY: APPS.OKI_REFRESH_PVT
Source
1 Package Body OKI_REFRESH_PVT AS
2 /* $Header: OKIRRFHB.pls 115.74 2004/08/13 16:35:16 asparama ship $ */
3
4 ----
5 --
6 -- type declarations
7 --
8 ----
9 TYPE vc_tab_type IS TABLE OF VARCHAR2(30);
10
11 -- arrays of various fixed-size datatypes used in bulk collections.
12 TYPE numlist is table of number index by binary_integer ;
13 TYPE rowlist is table of rowid index by binary_integer ;
14 TYPE datelist is table of date index by binary_integer ;
15
16 ----
17 --
18 -- cursors
19 --
20 ----
21 CURSOR index_info_csr (b_tab_name VARCHAR2
22 ,b_ind_name VARCHAR2
23 ,b_own_name VARCHAR2) IS
24
25 SELECT tablespace_name,initial_extent, next_extent
26 FROM all_indexes
27 WHERE table_name = b_tab_name
28 AND table_owner = b_own_name
29 AND index_name = b_ind_name
30 ;
31
32 CURSOR index_name_csr(b_tab_name VARCHAR2) IS
33 SELECT index_name
34 FROM all_indexes
35 WHERE table_name = b_tab_name
36 AND table_owner = 'OKI'
37 ;
38
39 CURSOR g_latest_job_run_id_csr IS
40 SELECT MAX(jrn.job_run_id) job_run_id
41 FROM oki_job_runs jrn
42 ;
43 rec_g_latest_job_run_id_csr g_latest_job_run_id_csr%ROWTYPE ;
44
45 --
46 -- Variables
47 --
48 l_ind_rec index_info_csr%ROWTYPE;
49 l_base_currency CONSTANT fnd_currencies.currency_code%TYPE :=
50 fnd_profile.value('OKI_BASE_CURRENCY');
51 -- used to limit batch collection sizes
52 l_max_rows CONSTANT binary_integer := 1000;
53
54 ---------------------------------------------------------------------
55 --
56 -- Procedure create_indices
57 -- Creates the known indicies for the named table
58 --
59 ---------------------------------------------------------------------
60 -- 11510 Changes to create_indices
61 -- 1. Denormalization of Tables
62 -- Tables oki_addresses and oki_expired_lines are obsoleted.
63 -- So, indexes of these tables are removed
64 -- 2. Indexes on columns which are not populated in the table are removed.
65 -- Index on salesrep_name of oki_k_salesrep and
66 -- customer_name of oki_sales_k_hdrs are removed.
67 -- 3. Index created on new column is_exp_not_renewed_yn of oki_cov_prd_lines table.
68 PROCEDURE create_indicies (p_object_name IN VARCHAR2
69 ,p_parm_rec IN index_info_csr%ROWTYPE) IS
70
71 l_contract_number VARCHAR2(1000);
72 l_sql_string VARCHAR2(4000);
73 l_index_info index_info_csr%ROWTYPE;
74
75 CURSOR c_default_index_info IS
76 SELECT tspace.tablespace_name,
77 tspace.initial_extent,
78 tspace.next_extent
79 FROM dba_users usr, dba_tablespaces tspace
80 WHERE usr.username = 'OKI'
81 AND usr.default_tablespace = tspace.tablespace_name;
82
83 BEGIN
84 l_index_info := p_parm_rec;
85 -- guard against failures where we could not get storage parameters
86 -- for the index
87 IF l_index_info.tablespace_name IS NULL THEN
88 -- get parameters for the OKI user
89 OPEN c_default_index_info;
90 FETCH c_default_index_info INTO l_index_info;
91 CLOSE c_default_index_info;
92 END IF;
93
94 -- create the indexes
95 IF p_object_name = 'OKI_SALES_K_HDRS' THEN
96 l_sql_string := 'CREATE INDEX OKI.OKI_SALES_K_HDRS_N1 ON';
97 l_sql_string := l_sql_string||' OKI.OKI_SALES_K_HDRS(IS_NEW_YN)'||' PARALLEL ';
98 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
99 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
100 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
101 EXECUTE IMMEDIATE l_sql_string;
102 l_sql_string := 'CREATE INDEX OKI.OKI_SALES_K_HDRS_N2 ON';
103 l_sql_string := l_sql_string||' OKI.OKI_SALES_K_HDRS(CLOSE_DATE)'||' PARALLEL ';
104 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
105 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
106 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
107 EXECUTE IMMEDIATE l_sql_string;
108 l_sql_string := 'CREATE INDEX OKI.OKI_SALES_K_HDRS_N3 ON';
109 l_sql_string := l_sql_string||' OKI.OKI_SALES_K_HDRS(END_DATE)'||' PARALLEL ';
110 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
111 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
112 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
113 EXECUTE IMMEDIATE l_sql_string;
114 l_sql_string := 'CREATE INDEX OKI.OKI_SALES_K_HDRS_N4 ON';
115 l_sql_string := l_sql_string||' OKI.OKI_SALES_K_HDRS(CUSTOMER_PARTY_ID)'||' PARALLEL ';
116 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
117 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
118 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
119 EXECUTE IMMEDIATE l_sql_string;
120 l_sql_string := 'CREATE INDEX OKI.OKI_SALES_K_HDRS_N5 ON';
121 l_sql_string := l_sql_string||' OKI.OKI_SALES_K_HDRS(DATE_CANCELED)'||' PARALLEL ';
122 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
123 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
124 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
125 EXECUTE IMMEDIATE l_sql_string;
126 l_sql_string := 'CREATE INDEX OKI.OKI_SALES_K_HDRS_N6 ON';
127 l_sql_string := l_sql_string||' OKI.OKI_SALES_K_HDRS(START_DATE)'||' PARALLEL ';
128 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
129 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
130 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
131 EXECUTE IMMEDIATE l_sql_string;
132 l_sql_string := 'CREATE INDEX OKI.OKI_SALES_K_HDRS_N7 ON';
133 l_sql_string := l_sql_string||' OKI.OKI_SALES_K_HDRS(DATE_TERMINATED)'||' PARALLEL ';
134 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
135 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
136 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
137 EXECUTE IMMEDIATE l_sql_string;
138 l_sql_string := 'CREATE INDEX OKI.OKI_SALES_K_HDRS_N8 ON';
139 l_sql_string := l_sql_string||' OKI.OKI_SALES_K_HDRS(authoring_org_id)'||' PARALLEL ';
140 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
141 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
142 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
143 EXECUTE IMMEDIATE l_sql_string;
144 l_sql_string := 'CREATE INDEX OKI.OKI_SALES_K_HDRS_N9 ON';
145 l_sql_string := l_sql_string||' OKI.OKI_SALES_K_HDRS(date_approved)'
146 ||' PARALLEL ';
147 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
148 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
149 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
150 EXECUTE IMMEDIATE l_sql_string;
151
152 /* 11510 Changes
153 Commenting out creation of index as customer name is not be used
154 from this table.
155
156 l_sql_string := 'CREATE INDEX OKI.OKI_SALES_K_HDRS_N10 ON';
157 l_sql_string := l_sql_string||' OKI.OKI_SALES_K_HDRS(customer_name)'||' PARALLEL ';
158 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
159 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
160 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
161 EXECUTE IMMEDIATE l_sql_string;
162 */
163 l_sql_string := 'CREATE INDEX OKI.OKI_SALES_K_HDRS_N11 ON';
164 l_sql_string := l_sql_string||' OKI.OKI_SALES_K_HDRS(COMPLETE_CONTRACT_NUMBER)'
165 ||' PARALLEL ';
166 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
167 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
168 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
169 EXECUTE IMMEDIATE l_sql_string;
170 l_sql_string := 'CREATE UNIQUE INDEX OKI.OKI_SALES_K_HDRS_U1 ON';
171 l_sql_string := l_sql_string||' OKI.OKI_SALES_K_HDRS(CHR_ID)'||' PARALLEL ';
172 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
173 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
174 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
175 EXECUTE IMMEDIATE l_sql_string;
176 l_sql_string := 'CREATE UNIQUE INDEX OKI.OKI_SALES_K_HDRS_U2 ON';
177 l_sql_string := l_sql_string||' OKI.OKI_SALES_K_HDRS(CONTRACT_NUMBER,CONTRACT_NUMBER_MODIFIER)'||' PARALLEL ';
178 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
179 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
180 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
181 EXECUTE IMMEDIATE l_sql_string;
182
183 /* 11510 Changes
184
185 Commenting out creation of index as oki_addresses is obsoleted
186
187 ELSIF p_object_name = 'OKI_ADDRESSES' THEN
188 l_sql_string := 'CREATE INDEX OKI.OKI_ADDRESSES_N1 ON OKI.OKI_ADDRESSES (CLE_ID)'||' PARALLEL ';
189 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
190 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
191 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
192 EXECUTE IMMEDIATE l_sql_string;
193 */
194 ELSIF p_object_name = 'OKI_SOLD_ITM_LINES' THEN
195 l_sql_string := 'CREATE INDEX OKI.OKI_SOLD_ITM_LINES_N1 ON';
196 l_sql_string := l_sql_string||' OKI.OKI_SOLD_ITM_LINES(CHR_ID)'||' PARALLEL ';
197 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
198 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
199 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
200 EXECUTE IMMEDIATE l_sql_string;
201 l_sql_string := 'CREATE INDEX OKI.OKI_SOLD_ITM_LINES_N2 ON';
202 l_sql_string := l_sql_string||' OKI.OKI_SOLD_ITM_LINES(CONTRACT_NUMBER)'||' PARALLEL ';
203 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
204 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
205 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
206 EXECUTE IMMEDIATE l_sql_string;
207 l_sql_string := 'CREATE INDEX OKI.OKI_SOLD_ITM_LINES_N3 ON';
208 l_sql_string := l_sql_string||' OKI.OKI_SOLD_ITM_LINES(COMPLETE_CONTRACT_NUMBER)'||' PARALLEL ';
209 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
210 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
211 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
212 EXECUTE IMMEDIATE l_sql_string;
213 l_sql_string := 'CREATE INDEX OKI.OKI_SOLD_ITM_LINES_N4 ON';
214 l_sql_string := l_sql_string||' OKI.OKI_SOLD_ITM_LINES(ITEM_ID, INVENTORY_ORGANIZATION_ID)'||' PARALLEL ';
215 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
216 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
217 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
218 EXECUTE IMMEDIATE l_sql_string;
219 l_sql_string := 'CREATE UNIQUE INDEX OKI.OKI_SOLD_ITM_LINES_U1 ON';
220 l_sql_string := l_sql_string||' OKI.OKI_SOLD_ITM_LINES(CLE_ID)'||' PARALLEL ';
221 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
222 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
223 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
224 EXECUTE IMMEDIATE l_sql_string;
225 /*11.5.10 ADDED
226 l_sql_string := 'CREATE INDEX OKI.OKI_SOLD_ITM_LINES_N5 ON';
227 l_sql_string := l_sql_string||' OKI.OKI_SOLD_ITM_LINES(BILL_TO_SITE_USE_ID)'||' PARALLEL ';
228
229 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
230 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
231 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
232 EXECUTE IMMEDIATE l_sql_string;
233 l_sql_string := 'CREATE INDEX OKI.OKI_SOLD_ITM_LINES_N6 ON';
234 l_sql_string := l_sql_string||' OKI.OKI_SOLD_ITM_LINES(SHIP_TO_SITE_USE_ID)'||' PARALLEL ';
235 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
236 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
237 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
238 EXECUTE IMMEDIATE l_sql_string;
239 11510 Changes End */
240 ELSIF p_object_name = 'OKI_COV_PRD_LINES' THEN
241 l_sql_string := 'CREATE INDEX OKI.OKI_COV_PRD_LINES_N1 ON';
242 l_sql_string := l_sql_string||' OKI.OKI_COV_PRD_LINES(CHR_ID)'||' PARALLEL ';
243 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
244 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
245 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
246 EXECUTE IMMEDIATE l_sql_string;
247 l_sql_string := 'CREATE INDEX OKI.OKI_COV_PRD_LINES_N2 ON';
248 l_sql_string := l_sql_string||' OKI.OKI_COV_PRD_LINES(CONTRACT_NUMBER)'||' PARALLEL ';
249 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
250 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
251 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
252 EXECUTE IMMEDIATE l_sql_string;
253 l_sql_string := 'CREATE INDEX OKI.OKI_COV_PRD_LINES_N3 ON';
254 l_sql_string := l_sql_string||' OKI.OKI_COV_PRD_LINES(COVERED_PRODUCT_ID)'||' PARALLEL ';
255 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
256 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
257 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
258 EXECUTE IMMEDIATE l_sql_string;
259 l_sql_string := 'CREATE INDEX OKI.OKI_COV_PRD_LINES_N4 ON';
260 l_sql_string := l_sql_string||' OKI.OKI_COV_PRD_LINES(PARENT_CLE_ID)'||' PARALLEL ';
261 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
262 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
263 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
264 EXECUTE IMMEDIATE l_sql_string;
265 l_sql_string := 'CREATE INDEX OKI.OKI_COV_PRD_LINES_N5 ON';
266 l_sql_string := l_sql_string||' OKI.OKI_COV_PRD_LINES(START_DATE)'
267 ||' PARALLEL ';
268 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
269 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
270 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
271 EXECUTE IMMEDIATE l_sql_string;
272 l_sql_string := 'CREATE INDEX OKI.OKI_COV_PRD_LINES_N6 ON';
273 l_sql_string := l_sql_string||' OKI.OKI_COV_PRD_LINES(COMPLETE_CONTRACT_NUMBER)'
274 ||' PARALLEL ';
275 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
276 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
277 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
278 EXECUTE IMMEDIATE l_sql_string;
279 l_sql_string := 'CREATE INDEX OKI.OKI_COV_PRD_LINES_N7 ON';
280 l_sql_string := l_sql_string||' OKI.OKI_COV_PRD_LINES(system_name)'
281 ||' PARALLEL ';
282 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
283 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
284 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
285 EXECUTE IMMEDIATE l_sql_string;
286 l_sql_string := 'CREATE INDEX OKI.OKI_COV_PRD_LINES_N8 ON';
287 l_sql_string := l_sql_string||' OKI.OKI_COV_PRD_LINES(end_date)'
288 ||' PARALLEL ';
289 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
290 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
291 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
292 EXECUTE IMMEDIATE l_sql_string;
293 /* 11510 Changes Start
294 Adding new index on oki_cov_prd_lines table. */
295 l_sql_string := 'CREATE INDEX OKI.OKI_COV_PRD_LINES_N9 ON';
296 l_sql_string := l_sql_string||' OKI.OKI_COV_PRD_LINES(IS_EXP_NOT_RENEWED_YN)'||' PARALLEL ';
297 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
298 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
299 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
300 EXECUTE IMMEDIATE l_sql_string;
301 /* 11510 Changes End */
302
303 l_sql_string := 'CREATE UNIQUE INDEX OKI.OKI_COV_PRD_LINES_U1 ON';
304 l_sql_string := l_sql_string||' OKI.OKI_COV_PRD_LINES(CLE_ID)'||' PARALLEL ';
305 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
306 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
307 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
308 EXECUTE IMMEDIATE l_sql_string;
309
310 /* 11510 Changes
311
312 Commenting out creation of index as oki_expired_lines is obsoleted
313
314 ELSIF p_object_name = 'OKI_EXPIRED_LINES' THEN
315 l_sql_string := 'CREATE INDEX OKI.OKI_EXPIRED_LINES_N1 ON';
316 l_sql_string := l_sql_string||' OKI.OKI_EXPIRED_LINES(CHR_ID)'||' PARALLEL ';
317 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
318 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
319 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
320 EXECUTE IMMEDIATE l_sql_string;
321 l_sql_string := 'CREATE INDEX OKI.OKI_EXPIRED_LINES_N2 ON';
322 l_sql_string := l_sql_string||' OKI.OKI_EXPIRED_LINES(END_DATE)'||' PARALLEL ';
323 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
324 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
325 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
326 EXECUTE IMMEDIATE l_sql_string;
327 l_sql_string := 'CREATE UNIQUE INDEX OKI.OKI_EXPIRED_LINES_U1 ON';
328 l_sql_string := l_sql_string||' OKI.OKI_EXPIRED_LINES(CLE_ID)'||' PARALLEL ';
329 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
330 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
331 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
332 EXECUTE IMMEDIATE l_sql_string;
333 */
334 ELSIF p_object_name = 'OKI_K_SALESREPS' THEN
335 l_sql_string := 'CREATE INDEX OKI.OKI_K_SALESREPS_N1 ON';
336 l_sql_string := l_sql_string||' OKI.OKI_K_SALESREPS(CONTRACT_ID)'||' PARALLEL ';
337 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
338 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
339 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
340 EXECUTE IMMEDIATE l_sql_string;
341
342 /* 11510 Changes
343 Commenting out creation of index as salesrep name is not be used from
344 this table.
345
346 l_sql_string := 'CREATE INDEX OKI.OKI_K_SALESREPS_N2 ON';
347 l_sql_string := l_sql_string||' OKI.OKI_K_SALESREPS(SALESREP_NAME)'||' PARALLEL ';
348 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
349 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
350 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
351 EXECUTE IMMEDIATE l_sql_string;
352 */
353 l_sql_string := 'CREATE INDEX OKI.OKI_K_SALESREPS_N3 ON';
354 l_sql_string := l_sql_string||' OKI.OKI_K_SALESREPS(PARTY_ROLE_ID)'||' PARALLEL ';
355 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
356 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
357 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
358 EXECUTE IMMEDIATE l_sql_string;
359 l_sql_string := 'CREATE UNIQUE INDEX OKI.OKI_K_SALESREPS_U1 ON';
360 l_sql_string := l_sql_string||' OKI.OKI_K_SALESREPS(PARTY_CONTACT_ID)'||' PARALLEL ';
361 l_sql_string := l_sql_string||' TABLESPACE '||l_index_info.tablespace_name;
362 l_sql_string := l_sql_string||' STORAGE(INITIAL '||l_index_info.initial_extent;
363 l_sql_string := l_sql_string||' NEXT '||l_index_info.next_extent||')';
364 EXECUTE IMMEDIATE l_sql_string;
365 END IF;
366 EXCEPTION
367 WHEN OTHERS
368 THEN
369 fnd_file.put_line( which => fnd_file.log
370 , buff => 'The Following SQL statement failed ');
371 fnd_file.put_line( which => fnd_file.log
372 , buff => l_sql_string);
373 fnd_file.put_line( which => fnd_file.log
374 , buff => ' ');
375 IF (p_object_name = 'OKI_SALES_K_HDRS')
376 THEN
377 FOR r_rec IN (SELECT dnz_chr_id, count(1) chr_count
378 FROM okc_k_party_roles_b
379 WHERE rle_code IN ('CUSTOMER','LICENSEE','BUYER')
380 AND cle_id is null
381 GROUP BY dnz_chr_id
382 HAVING COUNT(1) > 1)
383 LOOP
384 SELECT contract_number ||' '||contract_number_modifier
385 INTO l_contract_number
386 FROM okc_k_headers_b
387 WHERE id = r_rec.dnz_chr_id;
388 fnd_file.put_line( which => fnd_file.log
389 , buff => 'Multiple Parties (Ex. CUSTOMER) defined for contract number '
390 || l_contract_number);
391 END LOOP;
392 fnd_file.put_line( which => fnd_file.log
393 , buff => ' ');
394 END IF;
395 RAISE;
396 END create_indicies;
397
398 ---------------------------------------------------------------------
399 --
400 -- Procedure update_oki_refreshs
401 -- Log concurrent manager information for full refresh of an object
402 --
403 ---------------------------------------------------------------------
404 PROCEDURE update_oki_refresh ( p_object_name IN VARCHAR2
405 , x_retcode OUT NOCOPY VARCHAR2
406 , p_job_run_id IN NUMBER DEFAULT NULL ) IS
407 l_sqlcode VARCHAR2(100);
408 l_sqlerrm VARCHAR2(1000);
409
410 BEGIN
411
412 x_retcode := '0';
413
414 UPDATE OKI_REFRESHS
415 SET REQUEST_ID = g_request_id ,
416 PROGRAM_APPLICATION_ID = g_program_application_id,
417 PROGRAM_ID = g_program_id,
418 PROGRAM_UPDATE_DATE = g_program_update_date,
419 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
420 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
421 LAST_UPDATE_DATE = SYSDATE,
422 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
423 JOB_RUN_ID = p_job_run_id
424 WHERE OBJECT_NAME = p_object_name;
425
426 IF SQL%ROWCOUNT <> 1 THEN
427 x_retcode := 2;
428 END IF;
429
430 EXCEPTION
431 WHEN OTHERS THEN
432
433 l_sqlcode := sqlcode ;
434 l_sqlerrm := sqlerrm ;
435
436 -- Set return code to warning
437 x_retcode := '2';
438
439 fnd_message.set_name( application => 'OKI'
440 , name => 'OKI_UPD_RFR_TABLE_FAILURE');
441 fnd_file.put_line( which => fnd_file.log
442 , buff => fnd_message.get);
443 fnd_file.put_line( which => fnd_file.log
444 , buff => l_sqlcode||' '||l_sqlerrm );
445 END update_oki_refresh;
446
447 ---------------------------------------------------------------------
448 --
449 -- Procedure update_oki_refreshs
450 -- Log concurrent manager information for full refresh of an object
451 --
452 ---------------------------------------------------------------------
453 PROCEDURE update_refresh_job_run(
454 p_object_name IN VARCHAR2
455 , p_job_run_id IN NUMBER
456 , x_retcode OUT NOCOPY VARCHAR2 ) IS
457
458 -- Location within the program before the error was encountered.
459 l_loc VARCHAR2(200) ;
460 l_sqlcode VARCHAR2(100) ;
461 l_sqlerrm VARCHAR2(1000) ;
462
463 BEGIN
464
465 x_retcode := '0';
466
467 l_loc := 'Updating oki_refreshs job_run_id for ' || p_object_name ;
468 UPDATE OKI_REFRESHS
469 SET REQUEST_ID = g_request_id ,
470 PROGRAM_APPLICATION_ID = g_program_application_id,
471 PROGRAM_ID = g_program_id,
472 -- PROGRAM_UPDATE_DATE = g_program_update_date,
473 JOB_RUN_ID = p_job_run_id,
474 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
475 LAST_UPDATE_DATE = SYSDATE,
476 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
477 WHERE OBJECT_NAME = p_object_name;
478
479 EXCEPTION
480 WHEN OTHERS THEN
481
482 l_sqlcode := sqlcode ;
483 l_sqlerrm := sqlerrm ;
484
485 -- Set return code to warning
486 x_retcode := '1';
487
488 -- Log the location within the package where the error occurred
489 fnd_message.set_name( application => 'OKI'
490 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
491 fnd_message.set_token( token => 'LOCATION'
492 , value => 'oki_refresh_pvt.update_refresh_job_run ' ) ;
493 fnd_file.put_line( which => fnd_file.log
494 , buff => fnd_message.get ) ;
495
496 fnd_message.set_name( application => 'OKI'
497 , name => 'OKI_UPD_RFR_TABLE_FAILURE');
498 fnd_file.put_line( which => fnd_file.log
499 , buff => fnd_message.get);
500 fnd_file.put_line( which => fnd_file.log
501 , buff => l_sqlcode||' '||l_sqlerrm );
502 END update_refresh_job_run ;
503
504 ---------------------------------------------------------------------
505 -- procedure ins_job_runs
506 --
507 -- This procedure creates a job_runs record.
508 --
509 ---------------------------------------------------------------------
510 PROCEDURE ins_job_runs
511 ( p_job_start_date IN DATE
512 , p_job_end_date IN DATE
513 , p_job_curr_start_date IN DATE
514 , p_job_curr_end_date IN DATE
515 , p_job_run_id OUT NOCOPY NUMBER
516 , x_errbuf OUT NOCOPY VARCHAR2
517 , x_retcode OUT NOCOPY VARCHAR2
518 ) IS
519
520 -- Cursor declaration
521 CURSOR l_seq_num_csr IS
522 SELECT oki_job_runs_s1.nextval seq
523 FROM dual
524 ;
525 rec_l_seq_num l_seq_num_csr%ROWTYPE ;
526
527 -- Exception to immediately exit the procedure
528 l_excp_exit_immediate EXCEPTION ;
529
530 -- Location within the program before the error was encountered.
531 l_loc VARCHAR2(200) ;
532 l_sqlcode VARCHAR2(100) ;
533 l_sqlerrm VARCHAR2(1000) ;
534 l_retcode VARCHAR2(1) ;
535 l_table_name VARCHAR2(30);
536 l_sequence NUMBER := NULL ;
537 l_sysdate DATE ;
538
539 BEGIN
540 l_retcode := '0' ;
541 l_sysdate := sysdate;
542 l_table_name := 'OKI_JOB_RUNS';
543
544 l_loc := 'Get job_run_id sequence number.' ;
545 OPEN l_seq_num_csr ;
546 FETCH l_seq_num_csr INTO rec_l_seq_num ;
547 -- unable to generate sequence number, exit immediately
548 IF l_seq_num_csr%NOTFOUND THEN
549 RAISE l_excp_exit_immediate ;
550 END IF ;
551 l_sequence := rec_l_seq_num.seq ;
552 CLOSE l_seq_num_csr ;
553
554 l_loc := 'Inserting into ' || l_table_name ;
555 INSERT INTO oki_job_runs (
556 job_run_id
557 , job_start_date
558 , job_end_date
559 , job_curr_start_date
560 , job_curr_end_date
561 , creation_date
562 , created_by
563 , last_update_date
564 , last_updated_by
565 , security_group_id
566 , request_id
567 ) VALUES (
568 l_sequence
569 , p_job_start_date
570 , p_job_end_date
571 , least(p_job_curr_start_date,l_sysdate)
572 , least(p_job_curr_end_date,l_sysdate)
573 , l_sysdate
574 , FND_GLOBAL.USER_ID
575 , l_sysdate
576 , FND_GLOBAL.USER_ID
577 , NULL
578 , g_request_id );
579
580 p_job_run_id := l_sequence ;
581
582 COMMIT ;
583
584 EXCEPTION
585 WHEN l_excp_exit_immediate THEN
586 -- Set return code to error
587 x_retcode := 2 ;
588
589 -- Log the location within the package where the error occurred
590 fnd_message.set_name( application => 'OKI'
591 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
592 fnd_message.set_token( token => 'LOCATION'
593 , value => 'oki_refresh_pvt.ins_job_runs' ) ;
594 fnd_file.put_line( which => fnd_file.log
595 , buff => fnd_message.get ) ;
596
597 -- Log the location within the procedure where the error occurred
598 fnd_message.set_name( application => 'OKI'
599 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
600 fnd_message.set_token( token => 'LOCATION'
601 , value => l_loc ) ;
602 fnd_file.put_line( which => fnd_file.log
603 , buff => fnd_message.get ) ;
604
605 WHEN OTHERS THEN
606 l_sqlcode := sqlcode ;
607 l_sqlerrm := sqlerrm ;
608 ROLLBACK ;
609 x_retcode := '2' ;
610 fnd_message.set_name( application => 'OKI'
611 , name => 'OKI_UNEXPECTED_FAILURE' ) ;
612
613 fnd_message.set_token( token => 'OBJECT_NAME'
614 , value => 'oki_refresh_pvt.job_start' ) ;
615
616 fnd_file.put_line( which => fnd_file.log
617 , buff => fnd_message.get ) ;
618
619 -- Log the location within the procedure where the error occurred
620 fnd_message.set_name( application => 'OKI'
621 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
622 fnd_message.set_token( token => 'LOCATION'
623 , value => l_loc ) ;
624 fnd_file.put_line( which => fnd_file.log
625 , buff => fnd_message.get ) ;
626
627 fnd_file.put_line( which => fnd_file.log
628 , buff => l_sqlcode || ' ' || l_sqlerrm ) ;
629 END ins_job_runs ;
630
631
632 ---------------------------------------------------------------------
633 -- procedure truncate_table
634 --
635 -- This procedure truncates a table.
636 --
637 ---------------------------------------------------------------------
638 PROCEDURE truncate_table
639 ( p_table_owner IN VARCHAR2
640 , p_table_name IN VARCHAR2
641 , x_errbuf OUT NOCOPY VARCHAR2
642 , x_retcode OUT NOCOPY VARCHAR2
643 ) IS
644 -- Location within the program before the error was encountered.
645 l_loc VARCHAR2(200) ;
646 l_sqlcode VARCHAR2(100) ;
647 l_sqlerrm VARCHAR2(1000) ;
648 l_retcode VARCHAR2(1) ;
649 l_sql_string VARCHAR2(4000) ;
650
651 BEGIN
652 l_retcode := '0' ;
653
654 l_loc := 'Truncating table ' || p_table_owner || '.' || p_table_name ;
655 l_sql_string := 'TRUNCATE TABLE ' || p_table_owner || '.' || p_table_name ;
656 EXECUTE IMMEDIATE l_sql_string ;
657
658 EXCEPTION
659 WHEN OTHERS THEN
660 l_sqlcode := sqlcode ;
661 l_sqlerrm := sqlerrm ;
662 ROLLBACK ;
663 x_retcode := '2' ;
664 fnd_message.set_name( application => 'OKI'
665 , name => 'OKI_UNEXPECTED_FAILURE' ) ;
666
667 fnd_message.set_token( token => 'OBJECT_NAME'
668 , value => 'oki_refresh_pvt.truncate_table' ) ;
669
670 fnd_file.put_line( which => fnd_file.log
671 , buff => fnd_message.get ) ;
672
673 -- Log the location within the procedure where the error occurred
674 fnd_message.set_name( application => 'OKI'
675 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
676 fnd_message.set_token( token => 'LOCATION'
677 , value => l_loc ) ;
678 fnd_file.put_line( which => fnd_file.log
679 , buff => fnd_message.get ) ;
680
681 fnd_file.put_line( which => fnd_file.log
682 , buff => l_sqlcode || ' ' || l_sqlerrm ) ;
683
684 END truncate_table ;
685
686 ---------------------------------------------------------------------
687 -- procedure initial_job_check
688 --
689 -- This procedure checks if this is the initial first time job run.
690 -- If it is, then it:
691 -- 1. Truncates the oki base tables
692 -- 2. Seeds the oki_job_runs table with a dummy record
693 -- 3. Updates the oki_refreshs table job_run id with
694 -- the dummy job_run_id
695 --
696 ---------------------------------------------------------------------
697 PROCEDURE initial_job_check
698 ( x_errbuf OUT NOCOPY VARCHAR2
699 , x_retcode OUT NOCOPY VARCHAR2
700 ) IS
701
702 -- Exception to immediately exit the procedure
703 l_excp_exit_immediate EXCEPTION ;
704 -- Records have already been processed, just exit the program
705 l_excp_no_processing EXCEPTION ;
706
707 -- Location within the program before the error was encountered.
708 l_loc VARCHAR2(200) ;
709 l_sqlcode VARCHAR2(100) ;
710 l_sqlerrm VARCHAR2(1000) ;
711 l_retcode VARCHAR2(1) ;
712 l_job_run_count NUMBER ;
713 l_table_owner VARCHAR2(30) ;
714 l_table_name VARCHAR2(30) ;
715 l_job_run_id NUMBER ;
716 l_init_job_run_date DATE ;
717
718 -- Cursor to check if this is the first time the job has ever run
719 CURSOR l_job_run_count_csr IS
720 SELECT count(*) jbrn_count
721 FROM oki_job_runs jrn ;
722 rec_l_job_run_count_csr l_job_run_count_csr%ROWTYPE ;
723
724 BEGIN
725 l_retcode := '0' ;
726 l_init_job_run_date := fnd_conc_date.string_to_date('1900/01/01');
727
728 l_loc := 'Checking if this is the first time the job has ever run.' ;
729 OPEN l_job_run_count_csr ;
730 FETCH l_job_run_count_csr INTO rec_l_job_run_count_csr ;
731 l_job_run_count := rec_l_job_run_count_csr.jbrn_count ;
732 CLOSE l_job_run_count_csr ;
733
734 l_loc := 'Checking if this is the initial first time load' ;
735 IF l_job_run_count > 0 THEN
736 RAISE l_excp_no_processing ;
737 END IF ;
738
739 l_table_owner := 'OKI' ;
740
741 l_table_name := 'OKI_SALES_K_HDRS' ;
742 l_loc := 'Calling truncate_table with ' || l_table_owner || '.' || l_table_name ;
743 truncate_table( p_table_owner => l_table_owner
744 , p_table_name => l_table_name
745 , x_errbuf => x_errbuf
746 , x_retcode => l_retcode ) ;
747 IF l_retcode = '2' THEN
748 -- Truncate failed, exit immediately.
749 RAISE l_excp_exit_immediate ;
750 END IF ;
751 l_table_name := 'OKI_SOLD_ITM_LINES' ;
752 l_loc := 'Calling truncate_table with ' || l_table_owner || '.' || l_table_name ;
753 truncate_table( p_table_owner => l_table_owner
754 , p_table_name => l_table_name
755 , x_errbuf => x_errbuf
756 , x_retcode => l_retcode ) ;
757 IF l_retcode = '2' THEN
758 -- Truncate failed, exit immediately.
759 RAISE l_excp_exit_immediate ;
760 END IF ;
761
762 l_table_name := 'OKI_COV_PRD_LINES' ;
763 l_loc := 'Calling truncate_table with ' || l_table_owner || '.' || l_table_name ;
764 truncate_table( p_table_owner => l_table_owner
765 , p_table_name => l_table_name
766 , x_errbuf => x_errbuf
767 , x_retcode => l_retcode ) ;
768 IF l_retcode = '2' THEN
769 -- Truncate failed, exit immediately.
770 RAISE l_excp_exit_immediate ;
771 END IF ;
772 /* 11510 Changes
773 l_table_name := 'OKI_EXPIRED_LINES' ;
774 l_loc := 'Calling truncate_table with ' || l_table_owner || '.' || l_table_name ;
775 truncate_table( p_table_owner => l_table_owner
776 , p_table_name => l_table_name
777 , x_errbuf => x_errbuf
778 , x_retcode => l_retcode ) ;
779 IF l_retcode = '2' THEN
780 -- Truncate failed, exit immediately.
781 RAISE l_excp_exit_immediate ;
782 END IF ;
783 */
784 l_table_name := 'OKI_K_SALESREPS' ;
785 l_loc := 'Calling truncate_table with ' || l_table_owner || '.' || l_table_name ;
786 truncate_table( p_table_owner => l_table_owner
787 , p_table_name => l_table_name
788 , x_errbuf => x_errbuf
789 , x_retcode => l_retcode ) ;
790 IF l_retcode = '2' THEN
791 -- Truncate failed, exit immediately.
792 RAISE l_excp_exit_immediate ;
793 END IF ;
794
795 /* 11510 Changes
796
797 l_table_name := 'OKI_ADDRESSES' ;
798 l_loc := 'Calling truncate_table with ' || l_table_owner || '.' || l_table_name ;
799 truncate_table( p_table_owner => l_table_owner
800 , p_table_name => l_table_name
801 , x_errbuf => x_errbuf
802 , x_retcode => l_retcode ) ;
803 IF l_retcode = '2' THEN
804 -- Truncate failed, exit immediately.
805 RAISE l_excp_exit_immediate ;
806 END IF ;
807 */
808 ins_job_runs(
809 p_job_start_date => sysdate
810 , p_job_end_date => sysdate
811 , p_job_curr_start_date => l_init_job_run_date
812 , p_job_curr_end_date => l_init_job_run_date
813 , p_job_run_id => l_job_run_id
814 , x_errbuf => x_errbuf
815 , x_retcode => l_retcode ) ;
816 IF l_retcode = '2' THEN
817 -- Truncate failed, exit immediately.
818 RAISE l_excp_exit_immediate ;
819 END IF ;
820
821 l_table_name := 'OKI_SALES_K_HDRS' ;
822 l_loc := 'Updating OKI_REFRESHS for || l_table_name' ;
823 -- update oki_refreshes
824 update_oki_refresh( p_object_name => l_table_name
825 , p_job_run_id => l_job_run_id
826 , x_retcode => l_retcode ) ;
827 IF l_retcode = '2' THEN
828 -- update_oki_refresh failed, exit immediately.
829 RAISE l_excp_exit_immediate ;
830 END IF ;
831
832 l_table_name := 'OKI_SOLD_ITM_LINES' ;
833 l_loc := 'Updating OKI_REFRESHS for || l_table_name' ;
834 -- update oki_refreshes
835 update_oki_refresh( p_object_name => l_table_name
836 , p_job_run_id => l_job_run_id
837 , x_retcode => l_retcode ) ;
838 IF l_retcode = '2' THEN
839 -- Truncate failed, exit immediately.
840 RAISE l_excp_exit_immediate ;
841 END IF ;
842
843 l_table_name := 'OKI_COV_PRD_LINES' ;
844 l_loc := 'Updating OKI_REFRESHS for || l_table_name' ;
845 -- update oki_refreshes
846 update_oki_refresh( p_object_name => l_table_name
847 , p_job_run_id => l_job_run_id
848 , x_retcode => l_retcode ) ;
849 IF l_retcode = '2' THEN
850 -- Truncate failed, exit immediately.
851 RAISE l_excp_exit_immediate ;
852 END IF ;
853
854 /* 11510 Changes
855
856 l_table_name := 'OKI_EXPIRED_LINES' ;
857 l_loc := 'Updating OKI_REFRESHS for || l_table_name' ;
858 -- update oki_refreshes
859 update_oki_refresh( p_object_name => l_table_name
860 , p_job_run_id => l_job_run_id
861 , x_retcode => l_retcode ) ;
862 IF l_retcode = '2' THEN
863 -- Truncate failed, exit immediately.
864 RAISE l_excp_exit_immediate ;
865 END IF ;
866 */
867
868 l_table_name := 'OKI_K_SALESREPS' ;
869 l_loc := 'Updating OKI_REFRESHS for || l_table_name' ;
870 -- update oki_refreshes
871 update_oki_refresh( p_object_name => l_table_name
872 , p_job_run_id => l_job_run_id
873 , x_retcode => l_retcode ) ;
874 IF l_retcode = '2' THEN
875 -- Truncate failed, exit immediately.
876 RAISE l_excp_exit_immediate ;
877 END IF ;
878
879 /* 11510 Changes
880
881 l_table_name := 'OKI_ADDRESSES' ;
882 l_loc := 'Updating OKI_REFRESHS for || l_table_name' ;
883 -- update oki_refreshes
884 update_oki_refresh( p_object_name => l_table_name
885 , p_job_run_id => l_job_run_id
886 , x_retcode => l_retcode ) ;
887 IF l_retcode = '2' THEN
888 -- Truncate failed, exit immediately.
889 RAISE l_excp_exit_immediate ;
890 END IF ;
891 */
892
893 COMMIT ;
894 EXCEPTION
895 WHEN l_excp_no_processing then
896 -- Just exit the program and continue with the table load
897
898 -- Log the location within the procedure where the error occurred
899 fnd_message.set_name( application => 'OKI'
900 , name => 'OKI_OBJ_ALREADY_RFR_MSG') ;
901 fnd_message.set_token( token => 'OBJECT_NAME'
902 , value => 'OKI_JOB_RUNS') ;
903 fnd_file.put_line( which => fnd_file.log
904 , buff => fnd_message.get) ;
905
906 WHEN l_excp_exit_immediate THEN
907 -- Do not log an error ; It has already been logged.
908 -- Set return code to error
909 x_retcode := '2' ;
910
911
912 WHEN OTHERS THEN
913 l_sqlcode := sqlcode;
914 l_sqlerrm := sqlerrm;
915 ROLLBACK;
916 x_retcode := '2';
917 fnd_message.set_name( application => 'OKI'
918 , name => 'OKI_UNEXPECTED_FAILURE' ) ;
919
920 fnd_message.set_token( token => 'OBJECT_NAME'
921 , value => 'oki_refresh_pvt.initial_job_check' ) ;
922
923 fnd_file.put_line( which => fnd_file.log
924 , buff => fnd_message.get ) ;
925
926 -- Log the location within the procedure where the error occurred
927 fnd_message.set_name( application => 'OKI'
928 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
929 fnd_message.set_token( token => 'LOCATION'
930 , value => l_loc ) ;
931 fnd_file.put_line( which => fnd_file.log
932 , buff => fnd_message.get ) ;
933
934 fnd_file.put_line( which => fnd_file.log
935 , buff => l_sqlcode || ' ' || l_sqlerrm ) ;
936
937 END initial_job_check ;
938
939 ---------------------------------------------------------------------
940 -- procedure get_load_date_range
941 --
942 -- Retrieves the start and end date for which the load should be
943 -- processed.
944 ---------------------------------------------------------------------
945 PROCEDURE get_load_date_range
946 ( p_job_run_id IN NUMBER
947 , p_job_curr_start_date OUT NOCOPY DATE
948 , p_job_curr_end_date OUT NOCOPY DATE
949 , x_errbuf OUT NOCOPY VARCHAR2
950 , x_retcode OUT NOCOPY VARCHAR2
951 ) IS
952
953 -- Exception to immediately exit the procedure
954 l_excp_exit_immediate EXCEPTION ;
955
956 -- Location within the program before the error was encountered.
957 l_loc VARCHAR2(200) ;
958 l_sqlcode VARCHAR2(100) ;
959 l_sqlerrm VARCHAR2(1000) ;
960 l_retcode VARCHAR2(1) ;
961
962 -- Cursor to retrieve the
963 CURSOR l_job_curr_date_csr
964 ( p_job_run_id IN NUMBER
965 ) IS
966 SELECT
967 job_curr_start_date
968 , job_curr_end_date
969 FROM oki_job_runs jrn
970 WHERE jrn.job_run_id = p_job_run_id ;
971 rec_l_job_curr_date l_job_curr_date_csr%ROWTYPE ;
972
973 BEGIN
974 l_retcode := '0' ;
975
976 l_loc := 'Getting job_curr_date range.' ;
977 OPEN l_job_curr_date_csr(p_job_run_id) ;
978 FETCH l_job_curr_date_csr INTO rec_l_job_curr_date ;
979 IF l_job_curr_date_csr%NOTFOUND THEN
980 RAISE l_excp_exit_immediate ;
981 END IF ;
982 p_job_curr_start_date := rec_l_job_curr_date.job_curr_start_date ;
983 p_job_curr_end_date := rec_l_job_curr_date.job_curr_end_date ;
984 CLOSE l_job_curr_date_csr ;
985
986 EXCEPTION
987 WHEN OTHERS THEN
988 l_sqlcode := sqlcode ;
989 l_sqlerrm := sqlerrm ;
990 ROLLBACK ;
991 x_retcode := '2' ;
992 fnd_message.set_name( application => 'OKI'
993 , name => 'OKI_UNEXPECTED_FAILURE' ) ;
994
995 fnd_message.set_token( token => 'OBJECT_NAME'
996 , value => 'oki_refresh_pvt.get_load_date_range' ) ;
997
998 fnd_file.put_line( which => fnd_file.log
999 , buff => fnd_message.get ) ;
1000
1001 -- Log the location within the procedure where the error occurred
1002 fnd_message.set_name( application => 'OKI'
1003 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
1004 fnd_message.set_token( token => 'LOCATION'
1005 , value => l_loc ) ;
1006 fnd_file.put_line( which => fnd_file.log
1007 , buff => fnd_message.get ) ;
1008
1009 fnd_file.put_line( which => fnd_file.log
1010 , buff => l_sqlcode || ' ' || l_sqlerrm ) ;
1011 END get_load_date_range ;
1012
1013 ---------------------------------------------------------------------
1014 -- procedure ins_job_run_dtl
1015 --
1016 -- Procedure to insert into the oki_job_run_dtl the list of
1017 -- contracts to be deleted from and inserted into the oki base
1018 -- tables.
1019 ---------------------------------------------------------------------
1020
1021 PROCEDURE ins_job_run_dtl
1022 ( p_job_run_id IN NUMBER
1023 , p_job_curr_start_date IN DATE
1024 , p_job_curr_end_date IN DATE
1025 , x_retcode OUT NOCOPY VARCHAR2
1026 ) IS
1027 -- Exception to immediately exit the procedure
1028 l_excp_exit_immediate EXCEPTION ;
1029
1030 -- Location within the program before the error was encountered.
1031 l_loc VARCHAR2(200) ;
1032 l_sqlcode VARCHAR2(100) ;
1033 l_sqlerrm VARCHAR2(1000) ;
1034 l_retcode VARCHAR2(1) ;
1035 l_sysdate DATE ;
1036
1037 BEGIN
1038 l_retcode := '0' ;
1039 l_sysdate := sysdate;
1040
1041 l_loc := 'Inserting into oki_job_run_dtl.' ;
1042 -- Insert into the job_run_dtl table
1043 -- record to be deleted and records to be inserted
1044 INSERT INTO oki_job_run_dtl (
1045 job_run_id
1046 , chr_id
1047 , action_flag
1048 , sob_id
1049 , period_set_name
1050 , accounted_period_type
1051 , func_currency
1052 , trx_func_rate
1053 , trx_base_rate
1054 , conversion_date
1055 , creation_date
1056 , created_by
1057 , last_update_date
1058 , last_updated_by
1059 , security_group_id
1060 , request_id
1061 , major_version
1062 , minor_version
1063 ) (SELECT
1064 p_job_run_id
1065 , shd.chr_id
1066 , 'D'
1067 , NULL --sob_id
1068 , NULL --period_set_name
1069 , NULL --accounted_period_type
1070 , NULL --func_currency
1071 , NULL --trx_func_rate
1072 , NULL --trx_base_rate
1073 , NULL --conversion_date
1074 , l_sysdate
1075 , FND_GLOBAL.USER_ID
1076 , l_sysdate
1077 , FND_GLOBAL.USER_ID
1078 , NULL
1079 , g_request_id
1080 , NULL --Major Version
1081 , NULL --Minor Version
1082 FROM oki_sales_k_hdrs shd
1083 MINUS
1084 SELECT
1085 p_job_run_id
1086 , khr.id
1087 , 'D'
1088 , NULL --sob_id
1089 , NULL --period_set_name
1090 , NULL --accounted_period_type
1091 , NULL --func_currency
1092 , NULL --trx_func_rate
1093 , NULL --trx_base_rate
1094 , NULL --conversion_date
1095 , l_sysdate
1096 , FND_GLOBAL.USER_ID
1097 , l_sysdate
1098 , FND_GLOBAL.USER_ID
1099 , NULL
1100 , g_request_id
1101 , NULL --Major Version
1102 , NULL --Minor Version
1103 FROM okc_k_headers_b khr)
1104 UNION
1105 SELECT
1106 p_job_run_id
1107 , vnm.chr_id
1108 , 'I'
1109 , SOB.set_of_books_id
1110 , SOB.period_set_name
1111 , SOB.accounted_period_type
1112 , SOB.currency_code
1113 , decode (sts.ste_code,'ACTIVE',
1114 get_conversion_rate( trunc(khr.start_date),khr.currency_code,sob.currency_code)
1115 , 'HOLD',
1116 get_conversion_rate( trunc(khr.start_date),khr.currency_code,sob.currency_code)
1117 ,'TERMINATED',
1118 get_conversion_rate( trunc(khr.start_date),khr.currency_code,sob.currency_code)
1119 , 'EXPIRED',
1120 get_conversion_rate( trunc(khr.start_date),khr.currency_code,sob.currency_code)
1121 , get_conversion_rate(l_sysdate,khr.currency_code,sob.currency_code)
1122 ) trx_func_rate
1123 , decode (sts.ste_code,'ACTIVE',
1124 get_conversion_rate( trunc(khr.start_date),khr.currency_code,l_base_currency)
1125 , 'HOLD',
1126 get_conversion_rate( trunc(khr.start_date),khr.currency_code,l_base_currency)
1127 ,'TERMINATED',
1128 get_conversion_rate( trunc(khr.start_date),khr.currency_code,l_base_currency)
1129 , 'EXPIRED',
1130 get_conversion_rate( trunc(khr.start_date),khr.currency_code,l_base_currency)
1131 , get_conversion_rate(l_sysdate,khr.currency_code,l_base_currency)
1132 ) trx_base_rate
1133 , decode (sts.ste_code,'ACTIVE',
1134 trunc(khr.start_date)
1135 , 'HOLD',
1136 trunc(khr.start_date)
1137 ,'TERMINATED',
1138 trunc(khr.start_date)
1139 , 'EXPIRED',
1140 trunc(khr.start_date)
1141 , trunc(sysdate)
1142 ) conversion_date
1143 , l_sysdate
1144 , FND_GLOBAL.USER_ID
1145 , l_sysdate
1146 , FND_GLOBAL.USER_ID
1147 , NULL
1148 , g_request_id
1149 , vnm.major_version
1150 , vnm.minor_version
1151 FROM okc_k_headers_b khr
1152 ,okc_k_vers_numbers vnm
1153 ,hr_organization_information oin
1154 ,gl_sets_of_books sob
1155 ,okc_statuses_b sts
1156 WHERE 1 = 1
1157 AND khr.buy_or_sell = 'S'
1158 AND khr.template_yn = 'N'
1159 AND khr.application_id = 515
1160 AND khr.scs_code IN ('SERVICE','WARRANTY') -- 11510 Change
1161 AND vnm.chr_id = khr.id
1162 AND oin.organization_id = khr.authoring_org_id
1163 AND oin.org_information_context = 'Operating Unit Information'
1164 AND sob.set_of_books_id = TO_NUMBER(oin.org_information3)
1165 AND vnm.last_update_date BETWEEN p_job_curr_start_date
1166 AND p_job_curr_end_date
1167 AND khr.sts_code = sts.code;
1168 COMMIT ;
1169
1170
1171 -- analyze table
1172 fnd_stats.gather_table_stats(ownname=>'OKI' ,tabname=>'OKI_JOB_RUN_DTL',percent=> 10);
1173
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 l_sqlcode := sqlcode ;
1177 l_sqlerrm := sqlerrm ;
1178 ROLLBACK ;
1179 x_retcode := '2' ;
1180 fnd_message.set_name( application => 'OKI'
1181 , name => 'OKI_UNEXPECTED_FAILURE' ) ;
1182
1183 fnd_message.set_token( token => 'OBJECT_NAME'
1184 , value => 'oki_refresh_pvt.ins_job_run_dtl' ) ;
1185
1186 fnd_file.put_line( which => fnd_file.log
1187 , buff => fnd_message.get ) ;
1188
1189 -- Log the location within the procedure where the error occurred
1190 fnd_message.set_name( application => 'OKI'
1191 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
1192 fnd_message.set_token( token => 'LOCATION'
1193 , value => l_loc ) ;
1194 fnd_file.put_line( which => fnd_file.log
1195 , buff => fnd_message.get ) ;
1196
1197 fnd_file.put_line( which => fnd_file.log
1198 , buff => l_sqlcode || ' ' || l_sqlerrm ) ;
1199 END ins_job_run_dtl ;
1200
1201 /****************11510 changes for currency conversion new Procedure***********/
1202 ---------------------------------------------------------------------
1203 -- procedure initial_load_job_run_dtl
1204 -- Insert into the oki_job_run_dtl the list of
1205 -- contracts to be inserted into OKI_SALES_K_HDRS table during inital load.
1206 -- This table stores both the functional as well as base conversion rates for
1207 -- all the contracts inserted
1208 ---------------------------------------------------------------------
1209
1210 PROCEDURE initial_load_job_run_dtl( p_job_run_id IN NUMBER
1211 , p_job_curr_start_date IN DATE
1212 , p_job_curr_end_date IN DATE
1213 ,x_retcode OUT NOCOPY VARCHAR2) IS
1214 TYPE chr_id_tab_typ IS TABLE OF oki_sales_k_hdrs.chr_id%TYPE INDEX BY BINARY_INTEGER;
1215 TYPE version_number_tab_typ IS TABLE OF oki_sales_k_hdrs.major_version%TYPE INDEX BY BINARY_INTEGER;
1216 TYPE date_tab_typ IS TABLE OF oki_sales_k_hdrs.start_date%TYPE INDEX BY BINARY_INTEGER;
1217 TYPE currency_tab_typ IS TABLE OF oki_sales_k_hdrs.currency_code%TYPE INDEX BY BINARY_INTEGER;
1218 TYPE rate_tab_typ IS TABLE OF gl_daily_rates.conversion_rate%TYPE INDEX BY BINARY_INTEGER;
1219
1220 TYPE sob_acct_period_tab_typ IS TABLE OF gl_sets_of_books.accounted_period_type%TYPE INDEX BY BINARY_INTEGER;
1221 TYPE sob_id_tab_typ IS TABLE OF gl_sets_of_books.set_of_books_id%TYPE INDEX BY BINARY_INTEGER;
1222 TYPE period_set_name_tab_typ IS TABLE OF gl_sets_of_books.period_set_name%TYPE INDEX BY BINARY_INTEGER;
1223
1224 l_no_update_refresh exception;
1225 chr_id_tab chr_id_tab_typ;
1226 conversion_date_tab date_tab_typ;
1227 trans_currency_tab currency_tab_typ;
1228 base_currency_tab currency_tab_typ;
1229 func_currency_tab currency_tab_typ;
1230 sob_acct_period_tab sob_acct_period_tab_typ;
1231 sob_id_tab sob_id_tab_typ;
1232 period_set_name_tab period_set_name_tab_typ;
1233 major_version_tab version_number_tab_typ;
1234 minor_version_tab version_number_tab_typ;
1235
1236 l_sysdate DATE ;
1237
1238 CURSOR cur_contracts IS
1239 SELECT khr.id chr_id,
1240 decode (sts.ste_code,'ACTIVE',
1241 trunc(khr.start_date)
1242 , 'HOLD',
1243 trunc(khr.start_date)
1244 ,'TERMINATED',
1245 trunc(khr.start_date)
1246 , 'EXPIRED',
1247 trunc(khr.start_date)
1248 , l_sysdate
1249 ) conversion_date,
1250 khr.currency_code currency_code,
1251 fnd_profile.value('OKI_BASE_CURRENCY') base_currency_code,
1252 sob.currency_code sob_currency_code,
1253 sob.accounted_period_type,
1254 sob.set_of_books_id,
1255 sob.period_set_name,
1256 vnm.major_version,
1257 vnm.minor_version
1258 FROM
1259 okc_k_headers_b khr,
1260 okc_k_vers_numbers vnm,
1261 okc_statuses_b sts,
1262 gl_sets_of_books sob,
1263 hr_organization_information oin
1264 WHERE 1 = 1
1265 AND khr.buy_or_sell = 'S'
1266 AND khr.template_yn = 'N'
1267 AND khr.application_id = 515
1268 AND khr.scs_code IN ('SERVICE','WARRANTY') -- 11510 Change
1269 AND khr.sts_code = sts.code
1270 AND khr.authoring_org_id = oin.organization_id
1271 AND vnm.chr_id = khr.id
1272 AND oin.org_information_context = 'Operating Unit Information'
1273 AND sob.set_of_books_id = oin.org_information3;
1274
1275
1276 l_conv_type gl_daily_rates.conversion_type%TYPE;
1277
1278 CURSOR cur_currency_rate(l_curr_date Date,
1279 l_from_curr Varchar2,
1280 l_to_curr Varchar2,
1281 l_conv_type Varchar2) IS
1282 Select conversion_rate from gl_daily_rates
1283 Where from_currency = l_from_curr
1284 And to_currency = l_to_curr
1285 And conversion_date = (SELECT MAX(conversion_date)
1286 FROM gl_daily_rates
1287 WHERE from_currency = l_from_curr
1288 AND to_currency = l_to_curr
1289 AND conversion_date <= l_curr_date
1290 AND conversion_type = l_conv_type)
1291 And conversion_type = l_conv_type;
1292
1293 l_date date ;
1294 l_last_date date ;
1295 l_trx_base_rate_tab rate_tab_typ;
1296 l_trx_func_rate_tab rate_tab_typ;
1297 l_message varchar2(1000);
1298 l_limit INTEGER := 0;
1299 l_max_select INTEGER := 20000;
1300 l_remainder INTEGER;
1301 l_sqlcode VARCHAR2(100);
1302 l_sqlerrm VARCHAR2(1000);
1303
1304 BEGIN
1305 x_retcode := 0;
1306 l_sysdate := sysdate ;
1307 l_conv_type := fnd_profile.value('OKI_DEFAULT_CONVERSION_TYPE');
1308
1309 SELECT count(*) INTO l_remainder
1310 FROM
1311 okc_k_headers_b khr,
1312 okc_statuses_b sts,
1313 gl_sets_of_books sob,
1314 hr_organization_information oin
1315 WHERE 1 = 1
1316 AND khr.buy_or_sell = 'S'
1317 AND khr.template_yn = 'N'
1318 AND khr.application_id = 515
1319 AND khr.scs_code IN ('SERVICE','WARRANTY') -- 11510 Change
1320 AND khr.sts_code = sts.code
1321 AND khr.authoring_org_id = oin.organization_id
1322 AND oin.org_information_context = 'Operating Unit Information'
1323 AND sob.set_of_books_id = oin.org_information3;
1324
1325
1326
1327 IF l_remainder > l_max_select THEN
1328 l_limit := l_max_select;
1329 l_remainder := l_remainder - l_max_select;
1330 ELSE
1331 l_limit := l_remainder;
1332 l_remainder := 0;
1333 END IF;
1334
1335 IF l_limit > 0 THEN
1336 OPEN cur_contracts;
1337 LOOP
1338 FETCH cur_contracts BULK COLLECT INTO chr_id_tab
1339 ,conversion_date_tab
1340 ,trans_currency_tab
1341 ,base_currency_tab
1342 ,func_currency_tab
1343 ,sob_acct_period_tab
1344 ,sob_id_tab
1345 ,period_set_name_tab
1346 ,major_version_tab
1347 ,minor_version_tab
1348 LIMIT l_limit;
1349
1350 IF chr_id_tab.first IS NOT NULL THEN
1351 FOR i in chr_id_tab.first..chr_id_tab.last LOOP
1352 l_date := conversion_date_tab(i);
1353 l_trx_base_rate_tab(i) := 0;
1354 l_trx_func_rate_tab(i) := 0;
1355
1356 IF(trans_currency_tab(i) = base_currency_tab(i)) THEN
1357 l_trx_base_rate_tab(i) := 1 ;
1358 ELSE
1359 FOR curr_rec IN cur_currency_rate (l_date,trans_currency_tab(i),base_currency_tab(i),l_conv_type)
1360 LOOP
1361 l_trx_base_rate_tab(i) := curr_rec.conversion_rate ;
1362 END LOOP ;
1363 END IF;
1364
1365 IF(trans_currency_tab(i) = func_currency_tab(i)) THEN
1366 l_trx_func_rate_tab(i) := 1 ;
1367 ELSE
1368 FOR curr_rec IN cur_currency_rate (l_date,trans_currency_tab(i),func_currency_tab(i),l_conv_type)
1369 LOOP
1370 l_trx_func_rate_tab(i) := curr_rec.conversion_rate ;
1371 END LOOP ;
1372 END IF;
1373
1374
1375 IF(l_trx_base_rate_tab(i) = 0) THEN
1376 fnd_message.set_name(application => 'OKI'
1377 ,name => 'OKI_CONV_RATE_FAILURE');
1378 fnd_message.set_token(token => 'FROM_CURRENCY'
1379 ,value => trans_currency_tab(i));
1380 fnd_message.set_token(token => 'TO_CURRENCY'
1381 ,value => base_currency_tab(i));
1382 fnd_file.put_line(which => fnd_file.log
1383 ,buff => fnd_message.get);
1384 END IF;
1385
1386 IF(l_trx_func_rate_tab(i) = 0) THEN
1387 fnd_message.set_name(application => 'OKI'
1388 ,name => 'OKI_CONV_RATE_FAILURE');
1389 fnd_message.set_token(token => 'FROM_CURRENCY'
1390 ,value => trans_currency_tab(i));
1391 fnd_message.set_token(token => 'TO_CURRENCY'
1392 ,value => func_currency_tab(i));
1393 fnd_file.put_line(which => fnd_file.log
1394 ,buff => fnd_message.get);
1395 END IF;
1396
1397 END LOOP; -- FOR loop on tab(i)
1398
1399
1400 FORALL j in chr_id_tab.first..chr_id_tab.last
1401 INSERT INTO oki_job_run_dtl (
1402 job_run_id
1403 ,chr_id
1404 ,action_flag
1405 ,sob_id
1406 ,period_set_name
1407 ,accounted_period_type
1408 ,func_currency
1409 ,trx_func_rate
1410 ,trx_base_rate
1411 ,conversion_date
1412 ,creation_date
1413 ,created_by
1414 ,last_update_date
1415 ,last_updated_by
1416 ,security_group_id
1417 ,request_id
1418 ,major_version
1419 ,minor_version
1420 )
1421 values
1422 ( p_job_run_id
1423 ,chr_id_tab(j)
1424 ,'I'
1425 ,sob_id_tab(j)
1426 ,period_set_name_tab(j)
1427 ,sob_acct_period_tab(j)
1428 ,func_currency_tab(j)
1429 ,l_trx_func_rate_tab(j)
1430 ,l_trx_base_rate_tab(j)
1431 , conversion_date_tab(j)
1432 , l_sysdate
1433 , FND_GLOBAL.USER_ID
1434 , l_sysdate
1435 , FND_GLOBAL.USER_ID
1436 , NULL
1437 , g_request_id
1438 ,major_version_tab(j)
1439 ,minor_version_tab(j));
1440 COMMIT;
1441 END IF; -- chr_id_tab.first IS NOT NULL
1442
1443 IF l_remainder > l_max_select THEN
1444 l_limit := l_max_select;
1445 l_remainder := l_remainder - l_max_select;
1446 ELSE
1447 l_limit := l_remainder;
1448 l_remainder := 0;
1449 END IF;
1450
1451 IF l_limit = 0 THEN
1452 EXIT;
1453 END IF;
1454
1455 END LOOP; -- main loop
1456 CLOSE cur_contracts;
1457 END IF; -- l_limit > 0
1458
1459 -- update oki_refreshes
1460 update_oki_refresh(p_object_name => 'OKI_K_CONV_RATE'
1461 ,x_retcode => x_retcode);
1462 IF x_retcode = '2' THEN
1463 -- update_oki_refresh failed, exit immediately.
1464 RAISE l_no_update_refresh;
1465 END IF ;
1466
1467
1468 -- analyze table
1469 fnd_stats.gather_table_stats(ownname => 'OKI'
1470 ,tabname => 'OKI_K_CONV_RATE',percent=> 10);
1471
1472 EXCEPTION
1473 when l_no_update_refresh then
1474 fnd_message.set_name(application => 'OKI'
1475 ,name => 'OKI_TABLE_LOAD_FAILURE');
1476 fnd_message.set_token(token => 'TABLE_NAME'
1477 ,value => 'initial_load_job_run_dtl');
1478 fnd_file.put_line(which => fnd_file.log
1479 ,buff => fnd_message.get);
1480
1481 fnd_file.put_line(which => fnd_file.log
1482 ,buff => 'Update of OKI_REFRESHS failed');
1483 WHEN OTHERS THEN
1484 CLOSE cur_contracts;
1485 l_sqlcode := sqlcode;
1486 l_sqlerrm := sqlerrm;
1487 ROLLBACK;
1488 x_retcode := '2';
1489 fnd_message.set_name(application => 'OKI'
1490 ,name => 'OKI_TABLE_LOAD_FAILURE');
1491 fnd_message.set_token(token => 'TABLE_NAME'
1492 ,value => 'initial_load_job_run_dtl');
1493 fnd_file.put_line(which => fnd_file.log
1494 ,buff => fnd_message.get);
1495 fnd_file.put_line(which => fnd_file.log
1496 ,buff => l_sqlcode||' '||l_sqlerrm);
1497 END initial_load_job_run_dtl;
1498
1499
1500 ---------------------------------------------------------------------
1501 -- procedure job_start
1502 --
1503 -- Starts the refresh process
1504 -- 1. Creates an oki_job_runs record
1505 -- 2. Loads the oki_job_run_dtl table with the records to delete
1506 -- and insert into the oki base tables.
1507 ---------------------------------------------------------------------
1508 PROCEDURE job_start
1509 ( p_job_start_date IN DATE
1510 , x_errbuf OUT NOCOPY VARCHAR2
1511 , x_retcode OUT NOCOPY VARCHAR2
1512 ) IS
1513
1514 -- Exception to immediately exit the procedure
1515 l_excp_exit_immediate EXCEPTION ;
1516
1517 -- Location within the program before the error was encountered.
1518 l_loc VARCHAR2(200) ;
1519 l_sqlcode VARCHAR2(100) ;
1520 l_sqlerrm VARCHAR2(1000) ;
1521 l_retcode VARCHAR2(1) ;
1522 l_sequence NUMBER := NULL;
1523 l_sysdate DATE ;
1524 l_job_start_date DATE ;
1525 l_job_end_date DATE;
1526 l_job_curr_start_date DATE ;
1527 l_job_curr_end_date DATE ;
1528 l_table_name VARCHAR2(30) ;
1529 l_table_owner VARCHAR2(30) ;
1530
1531 -- Cursor declaration
1532 CURSOR l_seq_num_csr IS
1533 SELECT oki_job_runs_s1.nextval seq
1534 FROM dual
1535 ;
1536 rec_l_seq_num l_seq_num_csr%ROWTYPE ;
1537
1538 BEGIN
1539 l_retcode := '0' ;
1540 l_sysdate := sysdate;
1541 l_table_owner :='OKI';
1542
1543 /* No need to truncate this table for incremental load
1544 l_table_name := 'OKI_JOB_RUN_DTL' ;
1545 l_loc := 'Calling truncate_table with ' || l_table_owner || '.' || l_table_name ;
1546 truncate_table( p_table_owner => l_table_owner
1547 , p_table_name => l_table_name
1548 , x_errbuf => x_errbuf
1549 , x_retcode => l_retcode ) ;
1550 IF l_retcode = '2' THEN
1551 -- Truncate failed, exit immediately.
1552 RAISE l_excp_exit_immediate ;
1553 END IF ;
1554 */
1555 l_loc := 'Get job_runs_id sequence number.' ;
1556
1557 OPEN l_seq_num_csr ;
1558 FETCH l_seq_num_csr INTO rec_l_seq_num ;
1559 -- unable to generate sequence number, exit immediately
1560 IF l_seq_num_csr%NOTFOUND THEN
1561 RAISE l_excp_exit_immediate;
1562 END IF ;
1563 l_sequence := rec_l_seq_num.seq ;
1564 CLOSE l_seq_num_csr ;
1565
1566 SELECT least(jrn.job_curr_end_date + (1/(24 * 60 * 60)), l_sysdate)
1567 INTO l_job_start_date
1568 FROM oki_job_runs jrn
1569 WHERE jrn.job_run_id = (
1570 SELECT MAX(jrn1.job_run_id)
1571 FROM oki_job_runs jrn1
1572 WHERE jrn1.job_run_id < l_sequence ) ;
1573
1574 l_job_end_date := LEAST(nvl(p_job_start_date, sysdate ),l_sysdate);
1575
1576 fnd_file.put_line( which => fnd_file.log
1577 , buff => 'Service Contracts Intelligence - incremental load Started on ' ||
1578 fnd_date.date_to_displayDT(sysdate));
1579 fnd_file.put_line( which => fnd_file.log
1580 , buff => 'Parameter : start date '|| fnd_date.date_to_displayDT(l_job_start_date));
1581 fnd_file.put_line( which => fnd_file.log
1582 , buff => 'Parameter : end date '|| fnd_date.date_to_displayDT(l_job_end_date));
1583
1584 IF l_job_end_date < l_job_start_date THEN
1585 -- Job start date is greater than job end date
1586 fnd_file.put_line( which => fnd_file.log
1587 , buff => 'Job start date is greater than job end date...Hence exiting');
1588 RAISE l_excp_exit_immediate;
1589 END IF;
1590
1591 l_loc := 'Inserting into oki_job_runs' ;
1592 INSERT INTO oki_job_runs (
1593 job_run_id
1594 , job_start_date
1595 , job_end_date
1596 , job_curr_start_date
1597 , job_curr_end_date
1598 , creation_date
1599 , created_by
1600 , last_update_date
1601 , last_updated_by
1602 , security_group_id
1603 , request_id
1604 ) SELECT
1605 l_sequence
1606 , l_sysdate
1607 , NULL
1608 -- Add 1 second to the start time so the time
1609 -- does not overlap with the previous job run
1610 , l_job_start_date
1611 , l_job_end_date
1612 , l_sysdate
1613 , FND_GLOBAL.USER_ID
1614 , l_sysdate
1615 , FND_GLOBAL.USER_ID
1616 , NULL
1617 , g_request_id
1618 FROM oki_job_runs jrn1
1619 WHERE jrn1.job_run_id = (
1620 SELECT MAX(jrn3.job_run_id)
1621 FROM oki_job_runs jrn3
1622 WHERE jrn3.job_run_id < l_sequence ) ;
1623 COMMIT ;
1624
1625 l_loc := 'Calling get_load_date_range.' ;
1626 get_load_date_range(
1627 p_job_run_id => l_sequence
1628 , p_job_curr_start_date => l_job_curr_start_date
1629 , p_job_curr_end_date => l_job_curr_end_date
1630 , x_errbuf => x_errbuf
1631 , x_retcode => l_retcode ) ;
1632 l_loc := 'Determining status of get_job_run_id.' ;
1633 IF l_retcode = '2' THEN
1634 -- No job_run_id, exit immediately.
1635 RAISE l_excp_exit_immediate ;
1636 END IF ;
1637
1638 l_loc := 'Calling ins_job_run_dtl.' ;
1639 ins_job_run_dtl(
1640 p_job_run_id => l_sequence
1641 , p_job_curr_start_date => l_job_curr_start_date
1642 , p_job_curr_end_date => l_job_curr_end_date
1643 , x_retcode => l_retcode ) ;
1644 IF l_retcode = '2' THEN
1645 -- ins_job_run_dtl failed, exit immediately.
1646 RAISE l_excp_exit_immediate ;
1647 END IF ;
1648
1649 EXCEPTION
1650 WHEN l_excp_exit_immediate THEN
1651 -- Set return code to error
1652 x_retcode := 2 ;
1653
1654 -- Log the location within the package where the error occurred
1655 fnd_message.set_name( application => 'OKI'
1656 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
1657 fnd_message.set_token( token => 'LOCATION'
1658 , value => 'oki_refresh_pvt.job_start' ) ;
1659 fnd_file.put_line( which => fnd_file.log
1660 , buff => fnd_message.get ) ;
1661
1662 -- Log the location within the procedure where the error occurred
1663 fnd_message.set_name( application => 'OKI'
1664 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
1665 fnd_message.set_token( token => 'LOCATION'
1666 , value => l_loc ) ;
1667 fnd_file.put_line( which => fnd_file.log
1668 , buff => fnd_message.get ) ;
1669
1670 WHEN OTHERS THEN
1671 l_sqlcode := sqlcode ;
1672 l_sqlerrm := sqlerrm ;
1673 ROLLBACK ;
1674 x_retcode := '2' ;
1675 fnd_message.set_name( application => 'OKI'
1676 , name => 'OKI_UNEXPECTED_FAILURE' ) ;
1677
1678 fnd_message.set_token( token => 'OBJECT_NAME'
1679 , value => 'oki_refresh_pvt.job_start' ) ;
1680
1681 fnd_file.put_line( which => fnd_file.log
1682 , buff => fnd_message.get ) ;
1683
1684 -- Log the location within the procedure where the error occurred
1685 fnd_message.set_name( application => 'OKI'
1686 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
1687 fnd_message.set_token( token => 'LOCATION'
1688 , value => l_loc ) ;
1689 fnd_file.put_line( which => fnd_file.log
1690 , buff => fnd_message.get ) ;
1691
1692 fnd_file.put_line( which => fnd_file.log
1693 , buff => l_sqlcode || ' ' || l_sqlerrm ) ;
1694 END job_start ;
1695
1696
1697 /**********11510 Changes due to currency conversion New Procedure**********/
1698 ---------------------------------------------------------------------
1699 -- procedure initial_load_job_start
1700 --
1701 -- Starts the refresh process
1702 -- 1. Creates an oki_job_runs record
1703 -- 2. Loads the oki_job_run_dtl table with the records
1704 -- to insert into the oki base tables.
1705 ---------------------------------------------------------------------
1706
1707 PROCEDURE initial_load_job_start
1708 ( x_errbuf OUT NOCOPY VARCHAR2
1709 , x_retcode OUT NOCOPY VARCHAR2
1710 ) IS
1711
1712 -- Exception to immediately exit the procedure
1713 l_excp_exit_immediate EXCEPTION ;
1714
1715 -- Location within the program before the error was encountered.
1716 l_loc VARCHAR2(200) ;
1717 l_sqlcode VARCHAR2(100) ;
1718 l_sqlerrm VARCHAR2(1000) ;
1719 l_retcode VARCHAR2(1) ;
1720 l_sequence NUMBER := NULL ;
1721 l_sysdate DATE ;
1722 l_job_start_date DATE ;
1723 l_table_name VARCHAR2(30);
1724 l_table_owner VARCHAR2(30);
1725
1726 -- Cursor declaration
1727 CURSOR l_seq_num_csr IS
1728 SELECT oki_job_runs_s1.nextval seq
1729 FROM dual
1730 ;
1731 rec_l_seq_num l_seq_num_csr%ROWTYPE ;
1732
1733 BEGIN
1734 l_retcode := '0' ;
1735 l_sysdate := sysdate ;
1736 l_job_start_date := fnd_conc_date.string_to_date('1900/01/01');
1737 l_table_name :='OKI_JOB_RUN_DTL' ;
1738 l_table_owner := 'OKI';
1739
1740 fnd_file.put_line( which => fnd_file.log
1741 , buff => 'Service Contracts Intelligence - Initial load Started on: ' ||
1742 fnd_date.date_to_displayDT(sysdate));
1743 fnd_file.put_line( which => fnd_file.log
1744 , buff => 'Parameter : start date '|| fnd_date.date_to_displayDT(l_job_start_date));
1745 fnd_file.put_line( which => fnd_file.log
1746 , buff => 'Parameter : end date '|| fnd_date.date_to_displayDT(l_sysdate));
1747
1748 l_table_name := 'OKI_JOB_RUNS' ;
1749 l_loc := 'Calling truncate_table with ' || l_table_owner || '.' || l_table_name ;
1750 truncate_table( p_table_owner => l_table_owner
1751 , p_table_name => l_table_name
1752 , x_errbuf => x_errbuf
1753 , x_retcode => l_retcode ) ;
1754 IF l_retcode = '2' THEN
1755 -- Truncate failed, exit immediately.
1756 RAISE l_excp_exit_immediate ;
1757 END IF ;
1758
1759 l_table_name := 'OKI_JOB_RUN_DTL' ;
1760 l_loc := 'Calling truncate_table with ' || l_table_owner || '.' || l_table_name ;
1761 truncate_table( p_table_owner => l_table_owner
1762 , p_table_name => l_table_name
1763 , x_errbuf => x_errbuf
1764 , x_retcode => l_retcode ) ;
1765 IF l_retcode = '2' THEN
1766 -- Truncate failed, exit immediately.
1767 RAISE l_excp_exit_immediate ;
1768 END IF ;
1769
1770
1771 l_loc := 'Get job_runs_id sequence number.' ;
1772
1773 OPEN l_seq_num_csr ;
1774 FETCH l_seq_num_csr INTO rec_l_seq_num ;
1775 -- unable to generate sequence number, exit immediately
1776 IF l_seq_num_csr%NOTFOUND THEN
1777 RAISE l_excp_exit_immediate ;
1778 END IF ;
1779 l_sequence := rec_l_seq_num.seq ;
1780 CLOSE l_seq_num_csr ;
1781
1782 l_loc := 'Inserting into oki_job_runs' ;
1783
1784 INSERT INTO oki_job_runs (
1785 job_run_id
1786 , job_start_date
1787 , job_end_date
1788 , job_curr_start_date
1789 , job_curr_end_date
1790 , creation_date
1791 , created_by
1792 , last_update_date
1793 , last_updated_by
1794 , security_group_id
1795 , request_id
1796 ) values
1797 ( l_sequence
1798 , l_sysdate
1799 , NULL
1800 , l_job_start_date
1801 , l_sysdate
1802 , l_sysdate
1803 , FND_GLOBAL.USER_ID
1804 , l_sysdate
1805 , FND_GLOBAL.USER_ID
1806 , NULL
1807 , g_request_id);
1808
1809 COMMIT ;
1810
1811 l_loc := 'Calling initial_load_job_run_dtl.' ;
1812 initial_load_job_run_dtl(
1813 p_job_run_id => l_sequence
1814 , p_job_curr_start_date => l_job_start_date
1815 , p_job_curr_end_date => l_sysdate
1816 , x_retcode => l_retcode ) ;
1817 IF l_retcode = '2' THEN
1818 -- initial_load_job_run_dtl failed, exit immediately.
1819 RAISE l_excp_exit_immediate ;
1820 END IF ;
1821
1822 EXCEPTION
1823 WHEN l_excp_exit_immediate THEN
1824 -- Set return code to error
1825 x_retcode := 2 ;
1826
1827 -- Log the location within the package where the error occurred
1828 fnd_message.set_name( application => 'OKI'
1829 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
1830 fnd_message.set_token( token => 'LOCATION'
1831 , value => 'oki_refresh_pvt.job_start' ) ;
1832 fnd_file.put_line( which => fnd_file.log
1833 , buff => fnd_message.get ) ;
1834
1835 -- Log the location within the procedure where the error occurred
1836 fnd_message.set_name( application => 'OKI'
1837 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
1838 fnd_message.set_token( token => 'LOCATION'
1839 , value => l_loc ) ;
1840 fnd_file.put_line( which => fnd_file.log
1841 , buff => fnd_message.get ) ;
1842
1843 WHEN OTHERS THEN
1844 l_sqlcode := sqlcode ;
1845 l_sqlerrm := sqlerrm ;
1846 ROLLBACK ;
1847 x_retcode := '2' ;
1848 fnd_message.set_name( application => 'OKI'
1849 , name => 'OKI_UNEXPECTED_FAILURE' ) ;
1850
1851 fnd_message.set_token( token => 'OBJECT_NAME'
1852 , value => 'oki_refresh_pvt.job_start' ) ;
1853
1854 fnd_file.put_line( which => fnd_file.log
1855 , buff => fnd_message.get ) ;
1856
1857 -- Log the location within the procedure where the error occurred
1858 fnd_message.set_name( application => 'OKI'
1859 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
1860 fnd_message.set_token( token => 'LOCATION'
1861 , value => l_loc ) ;
1862 fnd_file.put_line( which => fnd_file.log
1863 , buff => fnd_message.get ) ;
1864
1865 fnd_file.put_line( which => fnd_file.log
1866 , buff => l_sqlcode || ' ' || l_sqlerrm ) ;
1867 END initial_load_job_start;
1868
1869
1870 /***11510 Change New Function to get the conversion rates for the contracts ***/
1871 ---------------------------------------------------------------------
1872 -- Function get_conversion_rate
1873 --
1874 -- 1. gets the latest conversion rate from GL_Daily_Rates given a
1875 -- conversion date,From and To currency
1876 -- Returns 0 if the conversion rates could not be found
1877 -- Returns 1 if the from and to cirrency are same
1878 -- Returns -1 if some error occurs during the calculation of the rate
1879
1880 ---------------------------------------------------------------------
1881 FUNCTION get_conversion_rate( p_curr_date DATE
1882 , p_from_currency IN VARCHAR2
1883 , p_to_currency IN VARCHAR2
1884 ) RETURN NUMBER
1885 IS
1886
1887 l_conv_type gl_daily_rates.conversion_type%TYPE;
1888
1889 CURSOR cur_currency_rate (p_curr_date Date,
1890 p_from_currency varchar2,
1891 p_to_currency varchar2)
1892 IS
1893 SELECT conversion_rate FROM gl_daily_rates
1894 WHERE from_currency = p_from_currency
1895 AND to_currency = p_to_currency
1896 AND conversion_date = (SELECT MAX (conversion_date)
1897 FROM gl_daily_rates
1898 WHERE from_currency = p_from_currency
1899 AND to_currency = p_to_currency
1900 AND conversion_date <= p_curr_date
1901 AND conversion_type = l_conv_type)
1902 AND conversion_type = l_conv_type;
1903
1904 l_curr_rec cur_currency_rate%ROWTYPE;
1905
1906 l_excp_exit_immediate EXCEPTION;
1907 l_rate NUMBER;
1908 l_sqlcode VARCHAR2(100);
1909 l_sqlerrm VARCHAR2(1000);
1910 l_loc VARCHAR2(200) ;
1911
1912
1913 BEGIN
1914 l_conv_type := fnd_profile.value('OKI_DEFAULT_CONVERSION_TYPE');
1915 IF (p_from_currency =p_to_currency) THEN
1916 RETURN 1;
1917 END IF;
1918
1919 l_loc := 'The from and to currencies are different..' ;
1920
1921 OPEN cur_currency_rate (p_curr_date, p_from_currency, p_to_currency);
1922 FETCH cur_currency_rate INTO l_curr_rec;
1923 IF cur_currency_rate%NOTFOUND THEN
1924 RAISE l_excp_exit_immediate;
1925 END IF;
1926 l_rate:=l_curr_rec.conversion_rate;
1927 CLOSE cur_currency_rate;
1928 RETURN l_rate;
1929
1930 EXCEPTION
1931 WHEN l_excp_exit_immediate THEN
1932 IF cur_currency_rate%ISOPEN THEN
1933 CLOSE cur_currency_rate;
1934 END IF;
1935 RETURN 0;
1936
1937 WHEN OTHERS THEN
1938 IF cur_currency_rate%ISOPEN THEN
1939 CLOSE cur_currency_rate;
1940 END IF;
1941 l_sqlcode := sqlcode;
1942 l_sqlerrm := sqlerrm;
1943 fnd_message.set_name( application => 'OKI'
1944 , name => 'OKI_CONV_RATE_FAILURE' ) ;
1945
1946 fnd_message.set_token( token => 'OBJECT_NAME'
1947 , value => 'oki_refresh_pvt.get_conversion_rate' ) ;
1948
1949 fnd_message.set_token( token => 'FROM_CURRENCY'
1950 , value => 'p_from_currency' ) ;
1951
1952 fnd_message.set_token( token => 'TO_CURRENCY'
1953 , value => 'p_to_currency' ) ;
1954
1955 fnd_file.put_line( which => fnd_file.log
1956 , buff => fnd_message.get ) ;
1957
1958 -- Log the location within the procedure where the error occurred
1959 fnd_message.set_name( application => 'OKI'
1960 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
1961 fnd_message.set_token( token => 'LOCATION'
1962 , value => l_loc ) ;
1963 fnd_file.put_line( which => fnd_file.log
1964 , buff => fnd_message.get ) ;
1965 RETURN -1;
1966 END;
1967
1968
1969 ---------------------------------------------------------------------
1970 -- procedure job_end
1971 --
1972 -- Updates the job_end_date if it has not already been updated.
1973 -- Cases where the job_end_date is not populated is:
1974 -- 1. Start of the job
1975 -- 2. Load fails when the oki base tables are being updated
1976 ---------------------------------------------------------------------
1977 PROCEDURE job_end
1978 ( x_errbuf OUT NOCOPY VARCHAR2
1979 , x_retcode OUT NOCOPY VARCHAR2
1980 ) IS
1981
1982 -- Exception to immediately exit the procedure
1983 l_excp_exit_immediate EXCEPTION ;
1984
1985 -- Location within the program before the error was encountered.
1986 l_loc VARCHAR2(200) ;
1987 l_sqlcode VARCHAR2(100) ;
1988 l_sqlerrm VARCHAR2(1000) ;
1989 l_retcode VARCHAR2(1) ;
1990 l_job_run_id NUMBER ;
1991 l_job_end_date DATE ;
1992
1993 -- Cursor to get the latest job_run_id
1994 CURSOR l_job_run_id_csr IS
1995 SELECT jrn1.job_run_id, jrn1.job_end_date
1996 FROM oki_job_runs jrn1
1997 WHERE jrn1.job_run_id = ( SELECT MAX(jrn2.job_run_id)
1998 FROM oki_job_runs jrn2)
1999 ;
2000 rec_l_job_run_id l_job_run_id_csr%ROWTYPE ;
2001
2002 BEGIN
2003 l_retcode := '0' ;
2004
2005 l_loc := 'Get latest job_run_id sequence number.' ;
2006 OPEN l_job_run_id_csr ;
2007 FETCH l_job_run_id_csr INTO rec_l_job_run_id ;
2008 -- unable to retrieve job_id, exit immediately
2009 IF l_job_run_id_csr%NOTFOUND THEN
2010 RAISE l_excp_exit_immediate ;
2011 END IF ;
2012 l_job_run_id := rec_l_job_run_id.job_run_id ;
2013 l_job_end_date := rec_l_job_run_id.job_end_date ;
2014 CLOSE l_job_run_id_csr ;
2015
2016 l_loc := 'Determining if job_end_date needs to be set.' ;
2017 IF l_job_end_date IS NULL THEN
2018 l_loc := 'Updating oki_job_runs.' ;
2019 UPDATE oki_job_runs jrn1
2020 SET job_end_date = sysdate
2021 , last_update_date = sysdate
2022 WHERE job_run_id = l_job_run_id ;
2023 END IF ;
2024
2025 COMMIT ;
2026 EXCEPTION
2027 WHEN l_excp_exit_immediate THEN
2028 -- Set return code to error
2029 x_retcode := 2 ;
2030
2031 -- Log the location within the package where the error occurred
2032 fnd_message.set_name( application => 'OKI'
2033 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
2034 fnd_message.set_token( token => 'LOCATION'
2035 , value => 'oki_refresh_pvt.job_end' ) ;
2036 fnd_file.put_line( which => fnd_file.log
2037 , buff => fnd_message.get ) ;
2038
2039 -- Log the location within the procedure where the error occurred
2040 fnd_message.set_name( application => 'OKI'
2041 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
2042 fnd_message.set_token( token => 'LOCATION'
2043 , value => l_loc ) ;
2044 fnd_file.put_line( which => fnd_file.log
2045 , buff => fnd_message.get ) ;
2046
2047 WHEN OTHERS THEN
2048 l_sqlcode := sqlcode ;
2049 l_sqlerrm := sqlerrm ;
2050 ROLLBACK ;
2051 x_retcode := '2' ;
2052 fnd_message.set_name( application => 'OKI'
2053 , name => 'OKI_UNEXPECTED_FAILURE' ) ;
2054
2055 fnd_message.set_token( token => 'OBJECT_NAME'
2056 , value => 'oki_refresh_pvt.job_end' ) ;
2057
2058 fnd_file.put_line( which => fnd_file.log
2059 , buff => fnd_message.get ) ;
2060
2061 -- Log the location within the procedure where the error occurred
2062 fnd_message.set_name( application => 'OKI'
2063 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
2064 fnd_message.set_token( token => 'LOCATION'
2065 , value => l_loc ) ;
2066 fnd_file.put_line( which => fnd_file.log
2067 , buff => fnd_message.get ) ;
2068
2069 fnd_file.put_line( which => fnd_file.log
2070 , buff => l_sqlcode || ' ' || l_sqlerrm ) ;
2071 END job_end ;
2072
2073 ---------------------------------------------------------------------
2074 -- procedure process_refresh_check
2075 --
2076 -- Determines if the object has been refreshed or if it needs to be
2077 -- refreshed
2078 ---------------------------------------------------------------------
2079
2080 PROCEDURE process_refresh_check
2081 ( p_object_name IN VARCHAR2
2082 , x_job_run_id OUT NOCOPY NUMBER
2083 , x_process_yn OUT NOCOPY VARCHAR2
2084 , x_errbuf OUT NOCOPY VARCHAR2
2085 , x_retcode OUT NOCOPY VARCHAR2
2086 ) IS
2087
2088 -- Exception to immediately exit the procedure
2089 l_excp_exit_immediate EXCEPTION ;
2090 -- Records have already been processed, just exit the program
2091 l_excp_no_processing EXCEPTION ;
2092
2093 -- Location within the program before the error was encountered.
2094 l_loc VARCHAR2(200) ;
2095 l_sqlcode VARCHAR2(100) ;
2096 l_sqlerrm VARCHAR2(1000) ;
2097 l_retcode VARCHAR2(1) ;
2098 l_job_run_id NUMBER ;
2099 l_rfh_job_run_id NUMBER ;
2100
2101 -- Cursor to get the latest job_run_id
2102 CURSOR l_job_run_id_csr IS
2103 SELECT max(jrn.job_run_id) job_run_id
2104 FROM oki_job_runs jrn
2105 ;
2106 rec_l_job_run_id l_job_run_id_csr%ROWTYPE ;
2107
2108 -- Cursor to get the job_run_id from the oki_refreshs table
2109 CURSOR l_rfh_job_run_id_csr
2110 ( p_object_name IN VARCHAR2
2111 ) IS
2112 SELECT rfh.job_run_id job_run_id
2113 FROM oki_refreshs rfh
2114 WHERE rfh.object_name = p_object_name
2115 ;
2116 rec_l_rfh_job_run_id l_rfh_job_run_id_csr%ROWTYPE ;
2117
2118 BEGIN
2119 l_retcode := '0' ;
2120 x_retcode := '0' ;
2121 x_process_yn := 'Y' ;
2122
2123 l_loc := 'Get latest job_run_id sequence number.' ;
2124 OPEN l_job_run_id_csr ;
2125 FETCH l_job_run_id_csr INTO rec_l_job_run_id ;
2126 -- unable to retrieve job_id, exit immediately
2127 IF l_job_run_id_csr%NOTFOUND THEN
2128 RAISE l_excp_exit_immediate ;
2129 END IF ;
2130 l_job_run_id := rec_l_job_run_id.job_run_id ;
2131 CLOSE l_job_run_id_csr ;
2132
2133 l_loc := 'Get refresh job_run_id sequence number.' ;
2134 OPEN l_rfh_job_run_id_csr (p_object_name ) ;
2135 FETCH l_rfh_job_run_id_csr INTO rec_l_rfh_job_run_id ;
2136 -- unable to retrieve refresh job_run_id, exit immediately
2137 IF l_rfh_job_run_id_csr%NOTFOUND THEN
2138 RAISE l_excp_exit_immediate ;
2139 END IF ;
2140 l_rfh_job_run_id := rec_l_rfh_job_run_id.job_run_id ;
2141 CLOSE l_rfh_job_run_id_csr ;
2142
2143 l_loc := p_object_name || ' has already been refreshed.' ;
2144 -- Refresh for this table has already been completed successfully
2145 -- since the job_run_id from the oki_job_run table equals the
2146 -- job_run_id from the oki_job_run_dtl table.
2147 IF l_job_run_id = l_rfh_job_run_id THEN
2148 raise l_excp_no_processing ;
2149 END IF ;
2150
2151 l_loc := 'Getting job_id to process.' ;
2152 IF l_job_run_id > l_rfh_job_run_id THEN
2153 -- Refresh needs to be processed
2154 x_job_run_id := l_job_run_id ;
2155 END IF ;
2156
2157 EXCEPTION
2158 WHEN l_excp_no_processing then
2159 x_process_yn := 'N' ;
2160 -- Log the location within the procedure where the error occurred
2161 fnd_message.set_name( application => 'OKI'
2162 , name => 'OKI_OBJ_ALREADY_RFR_MSG') ;
2163 fnd_message.set_token( token => 'OBJECT_NAME'
2164 , value => p_object_name ) ;
2165 fnd_file.put_line( which => fnd_file.log
2166 , buff => fnd_message.get) ;
2167
2168 WHEN l_excp_exit_immediate THEN
2169 -- Do not log an error ; It has already been logged.
2170 -- Set return code to error
2171 x_retcode := 2 ;
2172 -- Log the location within the package where the error occurred
2173 fnd_message.set_name( application => 'OKI'
2174 , name => 'OKI_LOC_IN_PROG_FAILURE') ;
2175 fnd_message.set_token( token => 'LOCATION'
2176 , value => 'oki_refresh_pvt.process_refresh_check' ) ;
2177 fnd_file.put_line( which => fnd_file.log
2178 , buff => fnd_message.get) ;
2179
2180 -- Log the location within the procedure where the error occurred
2181 fnd_message.set_name( application => 'OKI'
2182 , name => 'OKI_LOC_IN_PROG_FAILURE') ;
2183 fnd_message.set_token( token => 'LOCATION'
2184 , value => l_loc) ;
2185 fnd_file.put_line( which => fnd_file.log
2186 , buff => fnd_message.get) ;
2187
2188 WHEN OTHERS THEN
2189 l_sqlcode := sqlcode ;
2190 l_sqlerrm := sqlerrm ;
2191 ROLLBACK ;
2192 x_retcode := '2' ;
2193 fnd_message.set_name( application => 'OKI'
2194 , name => 'OKI_UNEXPECTED_FAILURE' ) ;
2195
2196 fnd_message.set_token( token => 'OBJECT_NAME'
2197 , value => 'oki_refresh_pvt.process_refresh_check' ) ;
2198
2199 fnd_file.put_line( which => fnd_file.log
2200 , buff => fnd_message.get ) ;
2201
2202 -- Log the location within the procedure where the error occurred
2203 fnd_message.set_name( application => 'OKI'
2204 , name => 'OKI_LOC_IN_PROG_FAILURE' ) ;
2205 fnd_message.set_token( token => 'LOCATION'
2206 , value => l_loc ) ;
2207 fnd_file.put_line( which => fnd_file.log
2208 , buff => fnd_message.get ) ;
2209
2210 fnd_file.put_line( which => fnd_file.log
2211 , buff => l_sqlcode || ' ' || l_sqlerrm ) ;
2212 END process_refresh_check ;
2213
2214 ---------------------------------------------------------------------
2215 --
2216 -- Procedure to refresh the latest conversion rates from
2217 -- gl table to oki schema
2218 --
2219 ---------------------------------------------------------------------
2220 PROCEDURE refresh_daily_rates(errbuf OUT NOCOPY VARCHAR2
2221 ,retcode OUT NOCOPY VARCHAR2) IS
2222
2223 v_dummy varchar2(1);
2224 -- l_conversion_type gl_daily_rates.conversion_type%TYPE := 'Corporate';
2225 l_conversion_type gl_daily_rates.conversion_type%TYPE ;
2226 l_no_update_refresh EXCEPTION;
2227 CURSOR c_conversion IS
2228 SELECT a.from_currency,
2229 a.to_currency,
2230 a.conversion_rate,
2231 a.conversion_date
2232 FROM gl_daily_rates a
2233 WHERE (a.from_currency, a.to_currency, a.conversion_date) IN
2234 ( SELECT b.from_currency, b.to_currency, MAX(b.conversion_date) conversion_date
2235 FROM gl_daily_rates b
2236 WHERE b.conversion_type = l_conversion_type
2237 GROUP BY b.from_currency, b.to_currency)
2238 AND a.conversion_type = l_conversion_type;
2239 BEGIN
2240
2241 retcode := '0';
2242 l_conversion_type := fnd_profile.value('OKI_DEFAULT_CONVERSION_TYPE');
2243 /* For all distinct from and to currencies */
2244 FOR r_conversion IN c_conversion
2245 LOOP
2246 --dbms_output.put_line(r_conversion.from_currency || ' '|| r_conversion.to_currency);
2247 /* Update oki_daily_rates table with the latest conversion rates
2248 If no record is found in OKI table insert a new record */
2249 UPDATE oki_daily_rates
2250 SET conversion_rate = r_conversion.conversion_rate
2251 , conversion_date = r_conversion.conversion_date
2252 WHERE from_currency = r_conversion.from_currency
2253 AND to_currency = r_conversion.to_currency ;
2254
2255 IF SQL%ROWCOUNT = 0 THEN
2256 INSERT INTO oki_daily_rates
2257 (from_currency
2258 , to_currency
2259 , conversion_rate
2260 , conversion_date
2261 )
2262 VALUES
2263 (r_conversion.from_currency
2264 , r_conversion.to_currency
2265 , r_conversion.conversion_rate
2266 , r_conversion.conversion_date
2267 ) ;
2268 END IF ;
2269 /* Check and insert a record for the conversion rate between the same currency with a
2270 conversion rate of 1 */
2271 BEGIN
2272 SELECT 'x'
2273 INTO v_dummy
2274 FROM oki_daily_rates
2275 WHERE from_currency = r_conversion.from_currency
2276 AND to_currency = r_conversion.from_currency ;
2277 EXCEPTION
2278 WHEN NO_DATA_FOUND THEN
2279 --DBMS_OUTPUT.PUT_LINE('inserting conversion rate for same currency ' || r_conversion.from_currency);
2280 INSERT INTO oki_daily_rates
2281 (from_currency
2282 , to_currency
2283 , conversion_rate
2284 , conversion_date
2285 )
2286 VALUES
2287 (r_conversion.from_currency
2288 , r_conversion.from_currency
2289 , 1
2290 , TRUNC(sysdate)
2291 ) ;
2292 END;
2293 END LOOP;
2294 -- Update oki_refresh
2295 update_oki_refresh(p_object_name => 'OKI_DAILY_RATES'
2296 ,x_retcode => retcode);
2297 IF retcode = '2' THEN
2298 -- update_oki_refresh failed, exit immediately.
2299 RAISE l_no_update_refresh;
2300 END IF ;
2301
2302 -- analyze table
2303 fnd_stats.gather_table_stats(ownname=>'OKI' ,tabname=>'OKI_DAILY_RATES',percent=> 10);
2304
2305 --DBMS_OUTPUT.PUT_LINE('End of the program...');
2306 fnd_message.set_name('OKI','OKI_TABLE_LOAD_SUCCESS');
2307 fnd_message.set_token('TABLE_NAME','OKI_DAILY_RATES');
2308 fnd_file.put_line(fnd_file.log,fnd_message.get);
2309 EXCEPTION
2310 when l_no_update_refresh then
2311 fnd_message.set_name(application => 'OKI'
2312 ,name => 'OKI_TABLE_LOAD_FAILURE');
2313 fnd_message.set_token(token => 'TABLE_NAME'
2314 ,value => 'OKI_DAILY_RATES');
2315 fnd_file.put_line(which => fnd_file.log
2316 ,buff => fnd_message.get);
2317
2318 fnd_file.put_line(which => fnd_file.log
2319 ,buff => 'Update of OKI_REFRESHS failed');
2320 WHEN OTHERS THEN
2321 retcode := '2';
2322 fnd_file.put_line(fnd_file.log,sqlcode||' '||sqlerrm);
2323
2324 fnd_message.set_name(application => 'OKI'
2325 ,name => 'OKI_UNEXPECTED_FAILURE');
2326 fnd_message.set_token(token => 'OBJECT_NAME'
2327 ,value => 'OKI_DAILY_RATES');
2328 fnd_file.put_line(which => fnd_file.log
2329 ,buff => fnd_message.get);
2330
2331 fnd_message.set_name(application => 'OKI'
2332 ,name => 'OKI_TABLE_LOAD_FAILURE');
2333 fnd_message.set_token(token => 'TABLE_NAME'
2334 ,value => 'OKI_DAILY_RATES');
2335 fnd_file.put_line(which => fnd_file.log
2336 ,buff => fnd_message.get);
2337 ROLLBACK;
2338 END refresh_daily_rates;
2339
2340 ---------------------------------------------------------------------
2341 --
2342 -- Procedure to load data into oki_sales_k_hdrs
2343 -- This procedure assumes the customer is always in HZ_PARTIES
2344 -- for a sell contract
2345 --
2346 ---------------------------------------------------------------------
2347 -- 11510 Changes to refresh/fast_sales_k_headers
2348 -- 1. Rules Migration
2349 -- 2. Denormalization of Tables
2350 -- Logic of refresh_k_pricing_rules is migrated to refresh_sales_k_hdrs.
2351 -- 3. True Value
2352 -- 4. Currency Conversion
2353 -- 5. Removal of customer_name and customer_number
2354 -- Customer_name and customer_number columns are not populated in the
2355 -- table. Therefore, the logic to populate these columns is removed from
2356 -- the procedure.
2357 -- 6. Removal of reference / joins to unnecessary tables
2358 -- Major_version and minor_version columns are to be obsoleted
2359 -- and join to the table okc_k_vers_numbers is removed.
2360 PROCEDURE refresh_sales_k_hdrs(errbuf OUT NOCOPY VARCHAR2
2361 ,retcode OUT NOCOPY VARCHAR2) IS
2362
2363 l_index_tab vc_tab_type;
2364 l_sql_string VARCHAR2(4000);
2365 l_sqlcode VARCHAR2(100);
2366 l_sqlerrm VARCHAR2(1000);
2367 l_errpos NUMBER := 0;
2368
2369 -- Exception to immediately exit the procedure
2370 l_excp_exit_immediate EXCEPTION ;
2371 l_no_update_refresh EXCEPTION;
2372 -- Application ID for OKS
2373 l_application_id CONSTANT NUMBER := 515 ;
2374
2375 -- variables to set up the fast refresh job
2376 l_job_run_id NUMBER ;
2377 l_init_job_run_date DATE ;
2378 l_sysdate DATE := NULL ;
2379
2380 BEGIN
2381 retcode := '0';
2382 l_init_job_run_date := fnd_conc_date.string_to_date('1900/01/01');
2383 l_sysdate := sysdate ;
2384
2385 -- get representative index storage parms for later use
2386 OPEN index_info_csr('OKI_SALES_K_HDRS','OKI_SALES_K_HDRS_N1','OKI');
2387 FETCH index_info_csr INTO l_ind_rec;
2388 CLOSE index_info_csr;
2389 l_errpos := 1;
2390
2391 -- drop indexes
2392 OPEN index_name_csr('OKI_SALES_K_HDRS');
2393 FETCH index_name_csr BULK COLLECT INTO l_index_tab;
2394 CLOSE index_name_csr;
2395
2396 IF l_index_tab.first IS NOT NULL THEN
2397 FOR i IN l_index_tab.first..l_index_tab.last LOOP
2398 l_sql_string := 'DROP INDEX OKI.'||l_index_tab(i);
2399 EXECUTE IMMEDIATE l_sql_string;
2400 END LOOP;
2401 END IF;
2402
2403 l_errpos := 2;
2404
2405 -- truncate table
2406 l_sql_string := 'TRUNCATE TABLE OKI.OKI_SALES_K_HDRS';
2407 EXECUTE IMMEDIATE l_sql_string;
2408 l_errpos := 3;
2409
2410 /*
2411 oki_refresh_pvt.ins_job_runs (
2412 p_job_start_date => l_sysdate
2413 , p_job_end_date => l_sysdate
2414 , p_job_curr_start_date => l_init_job_run_date
2415 , p_job_curr_end_date => l_sysdate
2416 , p_job_run_id => l_job_run_id
2417 , x_errbuf => errbuf
2418 , x_retcode => retcode ) ;
2419 IF retcode = '2' THEN
2420 -- ins_job_runs failed, exit immediately.
2421 RAISE l_excp_exit_immediate ;
2422 END IF ;
2423 */
2424
2425 BEGIN
2426 select max(job_run_id)
2427 into l_job_run_id
2428 from oki_job_runs;
2429 EXCEPTION
2430 WHEN NO_DATA_FOUND THEN
2431 retcode := '2';
2432 fnd_file.put_line(which => fnd_file.log
2433 ,buff => 'For initial load please run Initial Job Start Set-up, Contracts Intelligence concurrent program first');
2434
2435 RAISE l_excp_exit_immediate ;
2436 END;
2437
2438
2439 l_sql_string := 'ALTER SESSION ENABLE PARALLEL DML';
2440 EXECUTE IMMEDIATE l_sql_string;
2441
2442 l_sql_string := 'alter session set hash_area_size=100000000';
2443 EXECUTE IMMEDIATE l_sql_string;
2444 l_sql_string := 'alter session set sort_area_size=100000000';
2445 EXECUTE IMMEDIATE l_sql_string;
2446
2447 l_errpos := 4;
2448
2449 -- insert data
2450 INSERT /*+ append */ INTO oki_sales_k_hdrs
2451 (
2452 chr_id,
2453 contract_number,
2454 contract_number_modifier,
2455 complete_contract_number,
2456 order_number,
2457 authoring_org_id,
2458 organization_name,
2459 scs_code,
2460 sts_code,
2461 ste_code,
2462 customer_party_id,
2463 customer_name,
2464 customer_number,
2465 contract_amount,
2466 currency_code,
2467 contract_amount_renewed,
2468 currency_code_renewed,
2469 win_percent,
2470 forecast_amount,
2471 sob_id,
2472 sob_contract_amount,
2473 sob_forecast_amount,
2474 sob_contract_amount_renewed,
2475 sob_currency_code,
2476 base_contract_amount,
2477 base_forecast_amount,
2478 base_contract_amount_renewed,
2479 base_currency_code,
2480 close_date,
2481 start_date,
2482 end_date,
2483 duration,
2484 period,
2485 date_approved,
2486 date_signed,
2487 date_renewed,
2488 date_canceled,
2489 date_terminated,
2490 start_period_num,
2491 start_period_name,
2492 start_quarter,
2493 start_year,
2494 close_period_num,
2495 close_period_name,
2496 close_quarter,
2497 close_year,
2498 trn_code,
2499 inventory_organization_id,
2500 is_new_yn,
2501 is_latest_yn,
2502 orig_system_source_code,
2503 orig_system_id1,
2504 orig_system_reference1,
2505 contract_type,
2506 application_id,
2507 creation_date,
2508 last_update_date,
2509 attribute_category,
2510 attribute1,
2511 attribute2,
2512 attribute3,
2513 attribute4,
2514 attribute5,
2515 attribute6,
2516 attribute7,
2517 attribute8,
2518 attribute9,
2519 attribute10,
2520 attribute11,
2521 attribute12,
2522 attribute13,
2523 attribute14,
2524 attribute15,
2525 major_version,
2526 minor_version,
2527 /* 11510 Changes Start
2528 Added the new columns from oki_pricing_rules and oki_qto
2529 Also, terminated amount is now calculated during insertion */
2530 agreement_id,
2531 acct_rule_id,
2532 payment_term_id,
2533 inv_rule_id,
2534 list_header_id,
2535 grace_duration,
2536 grace_period_code,
2537 quote_to_contact_id,
2538 quote_to_site_id,
2539 quote_to_email_id,
2540 quote_to_phone_id,
2541 quote_to_fax_id,
2542 terminated_amount,
2543 sob_terminated_amount,
2544 base_terminated_amount
2545 /* 11510 Changes End */
2546 )
2547 SELECT /*+ leading(khr) use_hash(khr, hoks,jrd,cpl, pty, sts,vnm , spd,spd1, ro, oh.oh, r.ol,og,terminated.okscle) */
2548 khr.id
2549 ,khr.contract_number
2550 ,khr.contract_number_modifier
2551 ,khr.contract_number ||
2552 decode(khr.contract_number_modifier
2553 , null, null, '-'|| khr.contract_number_modifier )
2554 ,oh.order_number
2555 ,khr.authoring_org_id
2556 ,NULL organization_name -- 11510 Changes
2557 ,khr.scs_code
2558 ,khr.sts_code
2559 ,sts.ste_code
2560 ,to_number(cpl.object1_id1)
2561 ,NULL customer_name -- 11510 Changes
2562 ,NULL customer_number -- 11510 Changes
2563 ,khr.estimated_amount
2564 ,khr.currency_code
2565 ,null
2566 ,khr.currency_code_renewed
2567 ,hoks.est_rev_percent win_percent -- 11510 Changes
2568 ,((khr.estimated_amount * hoks.est_rev_percent ) / 100 ) -- 11510 Changes
2569 ,jrd.sob_id -- 11510 Changes
2570 ,(khr.estimated_amount * jrd.trx_func_rate) -- 11510 Changes
2571 ,(((khr.estimated_amount * jrd.trx_func_rate) * hoks.est_rev_percent ) / 100 ) -- 11510 Changes
2572 ,null
2573 ,jrd.func_currency -- 11510 Changes
2574 ,(khr.estimated_amount * jrd.trx_base_rate) -- 11510 Changes
2575 , (((khr.estimated_amount * jrd.trx_base_rate) * hoks.est_rev_percent) / 100 ) -- 11510 Changes
2576 ,null
2577 ,l_base_currency
2578 ,hoks.est_rev_date close_date -- 11510 Changes
2579 ,trunc(khr.start_date)
2580 ,trunc(khr.end_date)
2581 ,oki_disco_util_pub.get_duration(khr.start_date,khr.end_date)
2582 ,oki_disco_util_pub.get_period(khr.start_date,khr.end_date)
2583 ,trunc(khr.date_approved)
2584 ,trunc(khr.date_signed)
2585 ,trunc(khr.date_renewed)
2586 ,trunc(khr.datetime_cancelled)
2587 ,trunc(khr.date_terminated)
2588 ,spd.period_num
2589 ,spd.period_name
2590 ,spd.quarter_num
2591 ,spd.period_year
2592 /* 11510 Changes Start */
2593 ,spd1.period_num
2594 ,spd1.period_name
2595 ,spd1.quarter_num
2596 ,spd1.period_year
2597 /* 11510 Changes End */
2598 ,khr.trn_code
2599 ,khr.inv_organization_id
2600 -- ,DECODE(r.is_new_yn,'Y','Y',NULL)
2601 ,DECODE( nvl(r.is_new_yn,'Y'),'Y','Y',NULL)
2602 , decode(khr.datetime_cancelled,null,null,'N')
2603 ,khr.orig_system_source_code
2604 ,khr.orig_system_id1
2605 ,khr.orig_system_reference1
2606 ,decode( nvl(r.is_new_yn,'Y'),'Y','NEW','REN')
2607 ,khr.application_id
2608 ,khr.creation_date
2609 ,khr.last_update_date -- 11510 Changes
2610 ,khr.attribute_category
2611 ,khr.attribute1
2612 ,khr.attribute2
2613 ,khr.attribute3
2614 ,khr.attribute4
2615 ,khr.attribute5
2616 ,khr.attribute6
2617 ,khr.attribute7
2618 ,khr.attribute8
2619 ,khr.attribute9
2620 ,khr.attribute10
2621 ,khr.attribute11
2622 ,khr.attribute12
2623 ,khr.attribute13
2624 ,khr.attribute14
2625 ,khr.attribute15
2626 /* 11510 Changes Start */
2627 ,jrd.major_version major_version
2628 ,jrd.minor_version minor_version
2629 ,og.ISA_AGREEMENT_ID agreement_id -- From oki_pricing_rules
2630 ,hoks.acct_rule_id
2631 ,khr.payment_term_id -- From oki_pricing_rules
2632 ,khr.inv_rule_id
2633 ,khr.price_list_id list_header_id -- From oki_pricing_rules
2634 ,hoks.grace_duration -- From oki_pricing_rules
2635 ,hoks.grace_period -- From oki_pricing_rules
2636 ,hoks.quote_to_contact_id quote_to_contact_id -- From oki_qto
2637 ,hoks.quote_to_site_id quote_to_site_id -- From oki_qto
2638 ,hoks.quote_to_email_id quote_to_email_id -- From oki_qto
2639 ,hoks.quote_to_phone_id quote_to_phone_id -- From oki_qto
2640 ,hoks.quote_to_fax_id quote_to_fax_id -- From oki_qto
2641 ,terminated.terminated_amount
2642 ,terminated.terminated_amount * jrd.trx_func_rate
2643 ,terminated.terminated_amount * jrd.trx_base_rate
2644 /* 11510 Changes End */
2645 FROM
2646 okc_k_headers_b khr
2647 , oks_k_headers_b hoks -- 11510 Changes
2648 , oki_job_run_dtl jrd -- 11510 Changes
2649 , okc_k_party_roles_b cpl
2650 , okc_statuses_b sts
2651 , gl_periods spd
2652 , gl_periods spd1 -- 11510 Changes
2653 , okc_k_rel_objs ro
2654 , okx_order_headers_v oh
2655 , okc_governances og -- 11510 Changes
2656 , ( SELECT ol.subject_chr_id, decode(count(1),0,'Y','N') is_new_yn
2657 FROM okc_operation_lines ol
2658 WHERE 1 = 1
2659 AND ol.object_chr_id is not null
2660 GROUP BY ol.subject_chr_id
2661 ) r
2662 /* 11510 Changes Start */
2663 , (SELECT okscle.dnz_chr_id
2664 , SUM (NVL(ubt_amount,0) + NVL(credit_amount,0) +
2665 NVL(suppressed_credit,0)) terminated_amount
2666 FROM oks_k_lines_b okscle,okc_k_lines_b okccle
2667 WHERE okccle.id = okscle.cle_id
2668 AND okccle.price_level_ind='Y'
2669 GROUP BY okscle.dnz_chr_id
2670 ) terminated
2671 /* 11510 Changes End */
2672 WHERE 1 = 1
2673 and khr.buy_or_sell = 'S'
2674 and khr.template_yn = 'N'
2675 and khr.application_id = 515
2676 and cpl.rle_code in ('CUSTOMER','LICENSEE','BUYER')
2677 and cpl.dnz_chr_id = khr.id
2678 and cpl.cle_id IS NULL
2679 and sts.code = khr.sts_code
2680 and spd.period_set_name = jrd.period_set_name
2681 and spd.period_type = jrd.accounted_period_type
2682 and spd.adjustment_period_flag = 'N'
2683 and khr.start_date BETWEEN spd.start_date
2684 AND spd.end_date+0.99999
2685 /* 11510 Changes Start */
2686 and nvl(spd1.period_set_name,jrd.period_set_name) = jrd.period_set_name
2687 and nvl(spd1.period_type,jrd.accounted_period_type) = jrd.accounted_period_type
2688 and spd1.adjustment_period_flag(+) = 'N'
2689 and hoks.est_rev_date BETWEEN spd1.start_date(+)
2690 AND spd1.end_date(+) + 0.99999
2691 /* 11510 Changes End */
2692 and ro.chr_id (+) = khr.id
2693 AND ro.jtot_object1_code (+) = 'OKX_ORDERHEAD'
2694 /*Bug Fix 3675638
2695 AND ro.rty_code (+) = 'CONTRACTSERVICESORDER'
2696 */
2697 AND oh.id1 (+) = ro.object1_id1
2698 AND r.subject_chr_id(+) = khr.id
2699 /* 11510 Changes Start */
2700 AND hoks.chr_id = khr.id
2701 AND og.chr_id(+) = hoks.chr_id -- From oki_pricing_rules
2702 AND jrd.chr_id = khr.id
2703 AND terminated.dnz_chr_id(+) = khr.id
2704 /* 11510 Changes End */
2705 ;
2706 COMMIT;
2707
2708 l_errpos := 5;
2709
2710 /* 11510 Changes..
2711
2712 UPDATE oki_sales_k_hdrs shd
2713 SET (close_period_num
2714 ,close_period_name
2715 ,close_quarter
2716 ,close_year) =
2717 (SELECT cpd.period_num
2718 ,cpd.period_name
2719 ,cpd.quarter_num
2720 ,cpd.period_year
2721 FROM gl_periods cpd
2722 ,gl_sets_of_books sob
2723 WHERE shd.close_date between cpd.start_date and cpd.end_date+0.99999
2724 and cpd.period_type = sob.accounted_period_type
2725 and cpd.period_set_name = sob.period_set_name
2726 and cpd.adjustment_period_flag = 'N'
2727 and sob.set_of_books_id = shd.sob_id)
2728 WHERE shd.close_date IS NOT NULL
2729 ;
2730 COMMIT;
2731
2732 l_errpos := 5.5;
2733
2734
2735 UPDATE oki_sales_k_hdrs shd
2736 SET (terminated_amount
2737 ,sob_terminated_amount
2738 ,base_terminated_amount) = (
2739 SELECT -SUM(bcl.amount),
2740 -SUM(bcl.amount) * shd.sob_contract_amount/ DECODE(shd.contract_amount,0,NULL,shd.contract_amount),
2741 -SUM(bcl.amount) * shd.base_contract_amount/ DECODE(shd.contract_amount,0,NULL,shd.contract_amount)
2742 FROM oks_bill_cont_lines bcl,
2743 okc_k_lines_b cle
2744 WHERE bcl.bill_action = 'TR'
2745 AND cle.id = bcl.cle_id
2746 AND cle.chr_id = shd.chr_id )
2747 WHERE shd.date_terminated IS NOT NULL;
2748 COMMIT;
2749 */
2750 l_errpos := 6;
2751 -- recreate indexes
2752 create_indicies(p_object_name => 'OKI_SALES_K_HDRS'
2753 ,p_parm_rec => l_ind_rec);
2754
2755 /* Fetches distinct qualifiers before issuing update with subquery. */
2756 l_errpos := 6.25;
2757 -- analyze table
2758 fnd_stats.gather_table_stats(ownname => 'OKI'
2759 ,tabname => 'OKI_SALES_K_HDRS',percent=> 10);
2760
2761 l_errpos := 7;
2762
2763 -- alter table back
2764 l_sql_string := 'ALTER TABLE OKI.OKI_SALES_K_HDRS NOPARALLEL';
2765 EXECUTE IMMEDIATE l_sql_string;
2766 l_errpos := 8;
2767
2768 -- update oki_refreshes
2769 update_oki_refresh( p_object_name => 'OKI_SALES_K_HDRS'
2770 , p_job_run_id => l_job_run_id
2771 , x_retcode => retcode);
2772 COMMIT;
2773 IF retcode = '2' THEN
2774 -- update_oki_refresh failed, exit immediately.
2775 RAISE l_no_update_refresh;
2776 END IF;
2777
2778
2779 l_errpos := 10;
2780
2781 fnd_message.set_name(application => 'OKI'
2782 ,name => 'OKI_TABLE_LOAD_SUCCESS');
2783 fnd_message.set_token(token => 'TABLE_NAME'
2784 ,value => 'OKI_SALES_K_HDRS');
2785 fnd_file.put_line(which => fnd_file.log
2786 ,buff => fnd_message.get);
2787 l_errpos := 11;
2788
2789 EXCEPTION
2790 when l_no_update_refresh then
2791 fnd_message.set_name(application => 'OKI'
2792 ,name => 'OKI_TABLE_LOAD_FAILURE');
2793 fnd_message.set_token(token => 'TABLE_NAME'
2794 ,value => 'OKI_SALES_K_HDRS');
2795 fnd_file.put_line(which => fnd_file.log
2796 ,buff => fnd_message.get);
2797
2798 fnd_file.put_line(which => fnd_file.log
2799 ,buff => 'Update of OKI_REFRESHS failed');
2800 WHEN OTHERS THEN
2801 l_sqlcode := sqlcode;
2802 l_sqlerrm := sqlerrm;
2803 ROLLBACK;
2804 retcode := '2';
2805 fnd_message.set_name( application => 'OKI'
2806 , name => 'OKI_TABLE_LOAD_FAILURE');
2807 fnd_message.set_token( token => 'TABLE_NAME'
2808 , value => 'OKI_SALES_K_HDRS');
2809 fnd_file.put_line( which => fnd_file.log
2810 , buff => fnd_message.get);
2811 fnd_file.put_line( which => fnd_file.log
2812 , buff => l_sqlcode || ' ' || l_sqlerrm);
2813 -- try to get indexes back
2814 IF l_errpos < 6 THEN
2815 create_indicies(p_object_name => 'OKI_SALES_K_HDRS'
2816 ,p_parm_rec => l_ind_rec);
2817 ELSIF(l_errpos = 6 ) THEN
2818 -- truncate table
2819 l_sql_string := 'TRUNCATE TABLE OKI.OKI_SALES_K_HDRS';
2820 EXECUTE IMMEDIATE l_sql_string;
2821 END IF;
2822
2823 END refresh_sales_k_hdrs;
2824
2825 ---------------------------------------------------------------------
2826 --
2827 -- Procedure to load data into oki_sales_k_hdrs without a
2828 -- complete refresh. This procedure assumes the customer is always
2829 -- in HZ_PARTIES for a sell contract
2830 --
2831 ---------------------------------------------------------------------
2832 PROCEDURE fast_sales_k_hdrs
2833 ( x_errbuf OUT NOCOPY VARCHAR2
2834 , x_retcode OUT NOCOPY VARCHAR2 ) IS
2835
2836
2837 -- Exception to immediately exit the procedure
2838 l_excp_exit_immediate EXCEPTION ;
2839 -- Records have already been processed, just exit the program
2840 l_excp_no_processing EXCEPTION ;
2841 l_no_update_refresh EXCEPTION;
2842 l_index_tab vc_tab_type;
2843 l_sql_string VARCHAR2(4000);
2844 l_sqlcode VARCHAR2(100);
2845 l_sqlerrm VARCHAR2(1000);
2846 l_errpos NUMBER := 0;
2847 -- Application ID for OKS
2848 l_application_id CONSTANT NUMBER := 515 ;
2849
2850 -- Location within the program before the error was encountered.
2851 l_loc VARCHAR2(200) ;
2852 l_retcode VARCHAR2(1) ;
2853 l_process_yn VARCHAR2(1) ;
2854 l_job_run_id NUMBER ;
2855 l_job_curr_start_date DATE ;
2856 l_job_curr_end_date DATE ;
2857 l_table_name VARCHAR2(30) ;
2858
2859 BEGIN
2860 l_retcode := '0' ;
2861 l_table_name := 'OKI_SALES_K_HDRS';
2862 l_loc := 'Calling process_refresh_check.' ;
2863
2864 process_refresh_check(
2865 p_object_name => l_table_name
2866 , x_job_run_id => l_job_run_id
2867 , x_process_yn => l_process_yn
2868 , x_errbuf => x_errbuf
2869 , x_retcode => l_retcode ) ;
2870
2871 fnd_file.put_line( which => fnd_file.log
2872 , buff => 'Job-run-id'||to_char(l_job_run_id)) ;
2873
2874
2875 l_loc := 'Determining status of process_refresh_check.' ;
2876 IF l_retcode = '2' THEN
2877 -- No job_run_id, exit immediately.
2878 RAISE l_excp_exit_immediate ;
2879 END IF ;
2880
2881 l_loc := 'Checking if records have already been processed.' ;
2882
2883
2884 IF l_process_yn = 'N' THEN
2885 RAISE l_excp_no_processing ;
2886 END IF ;
2887
2888
2889 l_loc := 'Calling get_load_date_range.' ;
2890 get_load_date_range(
2891 p_job_run_id => l_job_run_id
2892 , p_job_curr_start_date => l_job_curr_start_date
2893 , p_job_curr_end_date => l_job_curr_end_date
2894 , x_errbuf => x_errbuf
2895 , x_retcode => l_retcode ) ;
2896 l_loc := 'Determining status of get_load_date_range' ;
2897 IF l_retcode = '2' THEN
2898 -- No job_curr_date range values, exit immediately.
2899 RAISE l_excp_exit_immediate ;
2900 END IF ;
2901
2902 l_loc := 'Deleting from ' || l_table_name ;
2903 DELETE FROM oki_sales_k_hdrs shd
2904 WHERE chr_id in ( SELECT /*+ index_ffs(jrd oki_job_run_dtl_u1)*/
2905 jrd.chr_id
2906 FROM oki_job_run_dtl jrd
2907 WHERE jrd.job_run_id = l_job_run_id ) ;
2908
2909
2910 l_loc := 'Populating Table oki_sales_k_hdrs ' ;
2911 INSERT INTO oki_sales_k_hdrs (
2912 chr_id
2913 , contract_number
2914 , contract_number_modifier
2915 , complete_contract_number
2916 , order_number
2917 , authoring_org_id
2918 , organization_name
2919 , scs_code
2920 , sts_code
2921 , ste_code
2922 , customer_party_id
2923 , customer_name
2924 , customer_number
2925 , contract_amount
2926 , currency_code
2927 , contract_amount_renewed
2928 , currency_code_renewed
2929 , win_percent
2930 , forecast_amount
2931 , sob_id
2932 , sob_contract_amount
2933 , sob_forecast_amount
2934 , sob_contract_amount_renewed
2935 , sob_currency_code
2936 , base_contract_amount
2937 , base_forecast_amount
2938 , base_contract_amount_renewed
2939 , base_currency_code
2940 , close_date
2941 , start_date
2942 , end_date
2943 , duration
2944 , period
2945 , date_approved
2946 , date_signed
2947 , date_renewed
2948 , date_canceled
2949 , date_terminated
2950 , start_period_num
2951 , start_period_name
2952 , start_quarter
2953 , start_year
2954 , close_period_num
2955 , close_period_name
2956 , close_quarter
2957 , close_year
2958 , trn_code
2959 , inventory_organization_id
2960 , is_new_yn
2961 , is_latest_yn
2962 , orig_system_source_code
2963 , orig_system_id1
2964 , orig_system_reference1
2965 , contract_type
2966 , application_id
2967 , creation_date
2968 , last_update_date
2969 , attribute_category
2970 , attribute1
2971 , attribute2
2972 , attribute3
2973 , attribute4
2974 , attribute5
2975 , attribute6
2976 , attribute7
2977 , attribute8
2978 , attribute9
2979 , attribute10
2980 , attribute11
2981 , attribute12
2982 , attribute13
2983 , attribute14
2984 , attribute15
2985 , major_version
2986 , minor_version
2987 /* 11510 Changes Start
2988 Added the new columns from oki_pricing_rules and oki_qto
2989 Also, terminated amount is now calculated during insertion */
2990 , agreement_id -- From oki_pricing_rule
2991 , acct_rule_id
2992 , payment_term_id -- From oki_pricing_rule
2993 , inv_rule_id
2994 , list_header_id -- From oki_pricing_rule
2995 , grace_duration -- From oki_pricing_rule
2996 , grace_period_code -- From oki_pricing_rule
2997 , quote_to_contact_id -- From oki_qto
2998 , quote_to_site_id -- From oki_qto
2999 , quote_to_email_id -- From oki_qto
3000 , quote_to_phone_id -- From oki_qto
3001 , quote_to_fax_id -- From oki_qto
3002 , terminated_amount
3003 , sob_terminated_amount
3004 , base_terminated_amount
3005 /* 11510 Changes End */
3006 ) SELECT /*+ leading(jrd) full(jrd) cardinality(jrd,1) use_nl(r) */
3007 khr.id
3008 , khr.contract_number
3009 , khr.contract_number_modifier
3010 , khr.contract_number || decode(khr.contract_number_modifier
3011 ,null, null
3012 ,'-' || khr.contract_number_modifier )
3013 , oh.order_number
3014 , khr.authoring_org_id
3015 , NULL organization_name -- 11510 Changes
3016 , khr.scs_code
3017 , khr.sts_code
3018 , sts.ste_code
3019 , to_number(cpl.object1_id1)
3020 , NULL customer_name -- 11510 Changes
3021 , NULL customer_number -- 11510 Changes
3022 , khr.estimated_amount
3023 , khr.currency_code
3024 , khr.estimated_amount_renewed
3025 , khr.currency_code_renewed
3026 , hoks.est_rev_percent win_percent -- 11510 Changes
3027 , ((khr.estimated_amount * hoks.est_rev_percent ) / 100 ) -- 11510 Changes
3028 , jrd.sob_id -- 11510 Changes
3029 , (khr.estimated_amount * jrd.trx_func_rate) -- 11510 Changes
3030 ,(((khr.estimated_amount * jrd.trx_func_rate) * hoks.est_rev_percent ) / 100 ) -- 11510 Changes
3031 , NULL
3032 , jrd.func_currency -- 11510 Changes
3033 , (khr.estimated_amount * jrd.trx_base_rate) -- 11510 Changes
3034 , (((khr.estimated_amount * jrd.trx_base_rate) * hoks.est_rev_percent) / 100 ) -- 11510 Changes
3035 , NULL
3036 , l_base_currency
3037 , hoks.est_rev_date close_date -- 11510 Changes
3038 , TRUNC(khr.start_date)
3039 , TRUNC(khr.end_date)
3040 , oki_disco_util_pub.get_duration(khr.start_date,khr.end_date)
3041 , oki_disco_util_pub.get_period(khr.start_date,khr.end_date)
3042 , TRUNC(khr.date_approved)
3043 , TRUNC(khr.date_signed)
3044 , TRUNC(khr.date_renewed)
3045 , TRUNC(khr.datetime_cancelled)
3046 , TRUNC(khr.date_terminated)
3047 , spd.period_num
3048 , spd.period_name
3049 , spd.quarter_num
3050 , spd.period_year
3051 /* 11510 Changes Start */
3052 ,spd1.period_num
3053 ,spd1.period_name
3054 ,spd1.quarter_num
3055 ,spd1.period_year
3056 /* 11510 Changes End */
3057 , khr.trn_code
3058 , khr.inv_organization_id
3059 , decode( nvl(r.is_new_yn,'Y'),'Y','Y',null) is_new_yn
3060 , decode(khr.datetime_cancelled,null,null,'N') is_latest_yn
3061 , khr.orig_system_source_code
3062 , khr.orig_system_id1
3063 , khr.orig_system_reference1
3064 , decode( nvl(r.is_new_yn,'Y'),'Y','NEW','REN') ren_type
3065 , khr.application_id
3066 , khr.creation_date
3067 , khr.last_update_date
3068 , khr.attribute_category
3069 , khr.attribute1
3070 , khr.attribute2
3071 , khr.attribute3
3072 , khr.attribute4
3073 , khr.attribute5
3074 , khr.attribute6
3075 , khr.attribute7
3076 , khr.attribute8
3077 , khr.attribute9
3078 , khr.attribute10
3079 , khr.attribute11
3080 , khr.attribute12
3081 , khr.attribute13
3082 , khr.attribute14
3083 , khr.attribute15
3084 /* 11510 Changes Start */
3085 , jrd.major_version major_version
3086 , jrd.minor_version minor_version
3087 , og.ISA_AGREEMENT_ID agreement_id -- From oki_pricing_rules
3088 , hoks.acct_rule_id
3089 , khr.payment_term_id -- From oki_pricing_rules
3090 , khr.inv_rule_id
3091 , khr.price_list_id list_header_id -- From oki_pricing_rules
3092 , hoks.grace_duration -- From oki_pricing_rules
3093 , hoks.grace_period -- From oki_pricing_rules
3094 , hoks.quote_to_contact_id quote_to_contact_id -- From oki_qto
3095 , hoks.quote_to_site_id quote_to_site_id -- From oki_qto
3096 , hoks.quote_to_email_id quote_to_email_id -- From oki_qto
3097 , hoks.quote_to_phone_id quote_to_phone_id -- From oki_qto
3098 , hoks.quote_to_fax_id quote_to_fax_id -- From oki_qto
3099 , terminated.terminated_amount
3100 , terminated.terminated_amount * jrd.trx_func_rate
3101 , terminated.terminated_amount * jrd.trx_base_rate
3102 /* 11510 Changes End */
3103 FROM
3104 oki_job_run_dtl jrd
3105 , okc_k_headers_b khr
3106 , oks_k_headers_b hoks -- 11510 Changes
3107 , okc_k_party_roles_b cpl
3108 , okc_statuses_b sts
3109 , gl_periods spd
3110 , gl_periods spd1 -- 11510 Changes
3111 , okc_k_rel_objs ro
3112 , okx_order_headers_v oh
3113 , okc_governances og -- From oki_pricing_rule
3114 , ( SELECT /*+ leading(jrd) full(jrd) cardinality(jrd,1)*/
3115 ol.subject_chr_id, decode(count(1),0,'Y','N') is_new_yn
3116 FROM okc_operation_lines ol, oki_job_run_dtl jrd
3117 WHERE 1 = 1
3118 AND jrd.job_run_id = l_job_run_id
3119 AND jrd.action_flag = 'I'
3120 AND ol.subject_chr_id = jrd.chr_id
3121 AND ol.object_chr_id is not null
3122 GROUP by ol.subject_chr_id
3123 ) r
3124 /* 11510 Changes Start */
3125 ,(SELECT okscle.dnz_chr_id /*+ leading(jrd) full(jrd) cardinality(jrd,1)*/
3126 , SUM (NVL(ubt_amount,0) + NVL(credit_amount,0) +
3127 NVL(suppressed_credit,0)) terminated_amount
3128 FROM oks_k_lines_b okscle,okc_k_lines_b okccle,oki_job_run_dtl jrd
3129 WHERE okccle.id = okscle.cle_id
3130 AND okscle.dnz_chr_id = jrd.chr_id
3131 AND jrd.job_run_id = l_job_run_id
3132 AND jrd.action_flag = 'I'
3133 AND okccle.price_level_ind='Y'
3134 GROUP BY okscle.dnz_chr_id
3135 ) terminated
3136 /* 11510 Changes End */
3137 WHERE 1 = 1
3138 AND jrd.job_run_id = l_job_run_id
3139 AND jrd.action_flag = 'I'
3140 AND khr.id = jrd.chr_id
3141 AND cpl.rle_code in ('CUSTOMER','LICENSEE','BUYER')
3142 AND cpl.dnz_chr_id = khr.id
3143 AND cpl.cle_id IS NULL
3144 AND sts.code = khr.sts_code
3145 AND spd.period_set_name = jrd.period_set_name
3146 AND spd.period_type = jrd.accounted_period_type
3147 AND spd.adjustment_period_flag = 'N'
3148 AND khr.start_date BETWEEN spd.start_date
3149 AND spd.end_date + 0.99999
3150 /* 11510 Changes Start */
3151 AND nvl(spd1.period_set_name,jrd.period_set_name) = jrd.period_set_name
3152 AND nvl(spd1.period_type,jrd.accounted_period_type) = jrd.accounted_period_type
3153 AND spd1.adjustment_period_flag(+) = 'N'
3154 AND hoks.est_rev_date BETWEEN spd1.start_date(+)
3155 AND spd1.end_date(+) + 0.99999
3156 /* 11510 Changes End */
3157 AND ro.chr_id(+) = jrd.chr_id
3158 AND ro.jtot_object1_code (+) = 'OKX_ORDERHEAD'
3159 /* Bug Fix 3675638
3160 AND ro.rty_code (+) = 'CONTRACTSERVICESORDER'
3161 */
3162 AND oh.id1 (+) = ro.object1_id1
3163 AND r.subject_chr_id(+) = khr.id
3164 /* 11510 Changes Start */
3165 AND hoks.chr_id = khr.id
3166 AND og.chr_id(+) = hoks.chr_id -- From oki_pricing_rule
3167 AND terminated.dnz_chr_id(+) = khr.id;
3168 /* 11510 Changes End */
3169
3170
3171 /*
3172 l_loc := 'Updating Table oki_sales_k_hdrs with forecast period details ' ;
3173 UPDATE oki_sales_k_hdrs shd
3174 SET (close_period_num
3175 ,close_period_name
3176 ,close_quarter
3177 ,close_year) =
3178 (SELECT cpd.period_num
3179 ,cpd.period_name
3180 ,cpd.quarter_num
3181 ,cpd.period_year
3182 FROM gl_periods cpd
3183 ,gl_sets_of_books sob
3184 WHERE shd.close_date between cpd.start_date and cpd.end_date+0.99999
3185 and cpd.period_type = sob.accounted_period_type
3186 and cpd.period_set_name = sob.period_set_name
3187 and cpd.adjustment_period_flag = 'N'
3188 and sob.set_of_books_id = shd.sob_id)
3189 WHERE shd.chr_id IN (
3190 SELECT chr_id
3191 FROM oki_job_run_dtl jrd
3192 WHERE 1 = 1
3193 AND jrd.job_run_id = l_job_run_id
3194 AND jrd.action_flag = 'I'
3195 )
3196 AND shd.close_date IS NOT NULL;
3197 COMMIT;
3198
3199 l_loc := 'Updating Table oki_sales_k_hdrs with termination amount details ' ;
3200
3201
3202 UPDATE oki_sales_k_hdrs shd
3203 SET (terminated_amount
3204 ,sob_terminated_amount
3205 ,base_terminated_amount) = (
3206 SELECT -SUM(bcl.amount),
3207 -SUM(bcl.amount) * shd.sob_contract_amount/ DECODE(shd.contract_amount,0,NULL,shd.contract_amount),
3208 -SUM(bcl.amount) * shd.base_contract_amount/ DECODE(shd.contract_amount,0,NULL,shd.contract_amount)
3209 FROM oks_bill_cont_lines bcl,
3210 okc_k_lines_b cle
3211 WHERE bcl.bill_action = 'TR'
3212 AND cle.id = bcl.cle_id
3213 AND cle.chr_id = shd.chr_id )
3214 WHERE shd.date_terminated IS NOT NULL
3215 AND shd.chr_id IN (
3216 SELECT chr_id
3217 FROM oki_job_run_dtl jrd
3218 WHERE 1 = 1
3219 AND jrd.job_run_id = l_job_run_id
3220 AND jrd.action_flag = 'I'
3221 );
3222
3223 COMMIT;
3224 */
3225
3226 l_loc := 'Updating OKI_REFRESHS.' ;
3227 -- update oki_refreshes
3228 update_oki_refresh( p_object_name => l_table_name
3229 , p_job_run_id => l_job_run_id
3230 , x_retcode => l_retcode ) ;
3231
3232 COMMIT ;
3233 IF l_retcode = 2 THEN
3234 RAISE l_no_update_refresh;
3235 END IF;
3236 l_loc := 'Analyzing ' || l_table_name ;
3237 -- analyze table
3238 fnd_stats.gather_table_stats( ownname => 'OKI'
3239 , tabname => l_table_name
3240 , percent => 10 ) ;
3241
3242 fnd_message.set_name( application => 'OKI'
3243 , name => 'OKI_TABLE_LOAD_SUCCESS' ) ;
3244 fnd_message.set_token( token => 'TABLE_NAME'
3245 , value => l_table_name ) ;
3246 fnd_file.put_line( which => fnd_file.log
3247 , buff => fnd_message.get ) ;
3248
3249 EXCEPTION
3250 WHEN l_excp_no_processing then
3251 -- Do not log an error ; It has already been logged.
3252 -- Just exit the program and continue with the other table load
3253 null ;
3254
3255 WHEN l_excp_exit_immediate THEN
3256 -- Do not log an error ; It has already been logged.
3257 -- Set return code to error
3258 x_retcode := '2' ;
3259
3260 WHEN l_no_update_refresh then
3261 fnd_message.set_name(application => 'OKI'
3262 ,name => 'OKI_TABLE_LOAD_FAILURE');
3263 fnd_message.set_token(token => 'TABLE_NAME'
3264 ,value => 'OKI_SALES_K_HDRS');
3265 fnd_file.put_line(which => fnd_file.log
3266 ,buff => fnd_message.get);
3267
3268 fnd_file.put_line(which => fnd_file.log
3269 ,buff => 'Update of OKI_REFRESHS failed');
3270 WHEN OTHERS THEN
3271 l_sqlcode := sqlcode;
3272 l_sqlerrm := sqlerrm;
3273 ROLLBACK;
3274 x_retcode := '2';
3275
3276 fnd_message.set_name( application => 'OKI'
3277 , name => 'OKI_TABLE_LOAD_FAILURE' ) ;
3278 fnd_message.set_token( token => 'TABLE_NAME'
3279 , value => 'OKI_SALES_K_HDRS' ) ;
3280 fnd_file.put_line( which => fnd_file.log
3281 , buff => fnd_message.get ) ;
3282
3283 -- Log the location within the procedure where the error occurred
3284 fnd_message.set_name( application => 'OKI'
3285 , name => 'OKI_LOC_IN_PROG_FAILURE');
3286 fnd_message.set_token( token => 'LOCATION'
3287 , value => l_loc ) ;
3288 fnd_file.put_line( which => fnd_file.log
3289 , buff => fnd_message.get ) ;
3290
3291 fnd_file.put_line( which => fnd_file.log
3292 , buff => l_sqlcode || ' ' || l_sqlerrm ) ;
3293 END fast_sales_k_hdrs ;
3294
3295 ---------------------------------------------------------------------
3296 --
3297 -- load address (bill to, ship to) information
3298 --
3299 -- Since the address can come from any number of places, such as
3300 -- account site uses, party site uses, party sites, it must determine
3301 -- the source view dynamically from jtf_objects.
3302 --
3303 ---------------------------------------------------------------------
3304
3305 -- 11510 Change: Stub out the procedure
3306
3307 PROCEDURE refresh_addrs(errbuf OUT NOCOPY VARCHAR2
3308 ,retcode OUT NOCOPY VARCHAR2) IS
3309
3310 l_sql_string VARCHAR2(2000);
3311
3312 BEGIN
3313
3314 l_sql_string := 'Procedure no longer used' ;
3315
3316 END refresh_addrs;
3317
3318 ---------------------------------------------------------------------
3319 --
3320 -- Procedure to load data into oki_addresses without a complete
3321 -- refresh
3322 --
3323 ---------------------------------------------------------------------
3324
3325 -- 11510 Change: Stub out the procedure
3326
3327 PROCEDURE fast_addrs
3328 ( x_errbuf OUT NOCOPY VARCHAR2
3329 , x_retcode OUT NOCOPY VARCHAR2 ) IS
3330
3331 l_sql_string VARCHAR2(2000);
3332
3333 BEGIN
3334
3335 l_sql_string := 'Procedure no longer used' ;
3336
3337 end fast_addrs ;
3338
3339 ---------------------------------------------------------------------
3340 --
3341 -- procedure to load the sold item lines table
3342 --
3343 ---------------------------------------------------------------------
3344 -- 11510 Changes to refresh/fast_sold_itm_lines
3345 -- 1. Rules Migration
3346 -- 2. Denormalization of Tables
3347 -- Logic of refresh_addrs is migrated to refresh_sold_itm_lines.
3348 -- 3. True Value
3349 -- 4. Currency Conversion
3350 -- OKI currently uses only two object types for top line level WARRANTY
3351 -- and SERVICE. The join to oks_line_style_sources should be removed and
3352 -- hard coded with OKX_SERVICE and OKX_WARRANTY
3353 procedure refresh_sold_itm_lines(errbuf OUT NOCOPY VARCHAR2
3354 ,retcode OUT NOCOPY VARCHAR2) IS
3355 -- Exception to immediately exit the procedure
3356 l_excp_no_job_run_id EXCEPTION ;
3357 l_no_update_refresh EXCEPTION;
3358 l_index_tab vc_tab_type;
3359 l_sql_string VARCHAR2(4000);
3360 l_sqlcode VARCHAR2(100);
3361 l_sqlerrm VARCHAR2(1000);
3362 l_errpos NUMBER := 0;
3363
3364 -- variables to set up the fast refresh job
3365 l_job_run_id NUMBER ;
3366
3367 BEGIN
3368 retcode := '0';
3369
3370 -- get representative index storage parms for later use
3371 OPEN index_info_csr('OKI_SOLD_ITM_LINES','OKI_SOLD_ITM_LINES_U1','OKI');
3372 FETCH index_info_csr INTO l_ind_rec;
3373 CLOSE index_info_csr;
3374 l_errpos := 1;
3375
3376 -- drop indexes
3377 OPEN index_name_csr('OKI_SOLD_ITM_LINES');
3378 FETCH index_name_csr BULK COLLECT INTO l_index_tab;
3379 CLOSE index_name_csr;
3380
3381 IF l_index_tab.first IS NOT NULL THEN
3382 FOR i IN l_index_tab.first..l_index_tab.last LOOP
3383 l_sql_string := 'DROP INDEX OKI.'||l_index_tab(i);
3384 EXECUTE IMMEDIATE l_sql_string;
3385 END LOOP;
3386 END IF;
3387 l_errpos := 2;
3388
3389 -- truncate table
3390 l_sql_string := 'TRUNCATE TABLE OKI.OKI_SOLD_ITM_LINES';
3391 EXECUTE IMMEDIATE l_sql_string;
3392 l_errpos := 3;
3393
3394 l_sql_string := 'ALTER SESSION ENABLE PARALLEL DML';
3395 EXECUTE IMMEDIATE l_sql_string;
3396 l_errpos := 4;
3397
3398 l_sql_string := 'alter session set hash_area_size=100000000';
3399 EXECUTE IMMEDIATE l_sql_string;
3400 l_sql_string := 'alter session set sort_area_size=100000000';
3401 EXECUTE IMMEDIATE l_sql_string;
3402
3403 -- insert data
3404 INSERT /*+ append */ INTO OKI_SOLD_ITM_LINES
3405 (
3406 CLE_ID,
3407 CHR_ID,
3408 CONTRACT_NUMBER,
3409 CONTRACT_NUMBER_MODIFIER,
3410 COMPLETE_CONTRACT_NUMBER,
3411 SCS_CODE,
3412 LINE_NUMBER,
3413 START_DATE,
3414 END_DATE,
3415 DURATION,
3416 PERIOD,
3417 STS_CODE,
3418 ste_code,
3419 TRN_CODE,
3420 DATE_TERMINATED,
3421 DATE_RENEWED,
3422 NUMBER_OF_ITEMS,
3423 UOM_CODE,
3424 UNIT_PRICE,
3425 UNIT_PRICE_PERCENT,
3426 PRICE_NEGOTIATED,
3427 CURRENCY_CODE,
3428 PRICE_NEGOTIATED_RENEWED,
3429 CURRENCY_CODE_RENEWED,
3430 SOB_PRICE_UNIT,
3431 SOB_PRICE_NEGOTIATED,
3432 SOB_PRICE_NEGOTIATED_RENEWED,
3433 SOB_CURRENCY_CODE,
3434 BASE_PRICE_UNIT,
3435 BASE_PRICE_NEGOTIATED,
3436 BASE_PRICE_NEGOTIATED_RENEWED,
3437 BASE_CURRENCY_CODE,
3438 SOLD_ITEM,
3439 ITEM_ID,
3440 CONCATENATED_SEGMENTS,
3441 AUTHORING_ORG_ID,
3442 INVENTORY_ORGANIZATION_ID,
3443 CREATION_DATE,
3444 LAST_UPDATE_DATE,
3445 ATTRIBUTE_CATEGORY,
3446 ATTRIBUTE1,
3447 ATTRIBUTE2,
3448 ATTRIBUTE3,
3449 ATTRIBUTE4,
3450 ATTRIBUTE5,
3451 ATTRIBUTE6,
3452 ATTRIBUTE7,
3453 ATTRIBUTE8,
3454 ATTRIBUTE9,
3455 ATTRIBUTE10,
3456 ATTRIBUTE11,
3457 ATTRIBUTE12,
3458 ATTRIBUTE13,
3459 ATTRIBUTE14,
3460 ATTRIBUTE15,
3461 BILL_TO_SITE_USE_ID, -- 11510 Changes
3462 SHIP_TO_SITE_USE_ID -- 11510 Changes
3463 )
3464 SELECT
3465 cle.id
3466 ,shd.chr_id
3467 ,shd.contract_number
3468 ,shd.contract_number_modifier
3469 ,shd.complete_contract_number
3470 ,shd.scs_code
3471 ,cle.line_number
3472 ,trunc(cle.start_date)
3473 ,trunc(cle.end_date)
3474 ,oki_disco_util_pub.get_duration(cle.start_date,cle.end_date)
3475 ,oki_disco_util_pub.get_period(cle.start_date,cle.end_date)
3476 ,cle.sts_code
3477 ,sts.ste_code
3478 ,cle.trn_code
3479 ,trunc(cle.date_terminated)
3480 ,trunc(cle.date_renewed)
3481 ,cim.number_of_items
3482 ,cim.uom_code
3483 ,cle.price_unit
3484 ,cle.price_unit_percent
3485 ,cle.price_negotiated
3486 ,shd.currency_code
3487 ,cle.price_negotiated_renewed
3488 ,cle.currency_code_renewed
3489 ,(cle.price_unit * jrd.trx_func_rate) -- 11510 Changes
3490 ,(cle.price_negotiated * jrd.trx_func_rate) -- 11510 Changes
3491 ,null
3492 ,shd.sob_currency_code
3493 ,(cle.price_unit * jrd.trx_base_rate) -- 11510 Changes
3494 ,(cle.price_negotiated * jrd.trx_base_rate) -- 11510 Changes
3495 ,null
3496 ,l_base_currency
3497 , null
3498 ,cim.object1_id1
3499 , null
3500 ,shd.authoring_org_id
3501 ,cim.object1_id2
3502 ,cle.creation_date
3503 ,cle.last_update_date
3504 ,cle.attribute_category
3505 ,cle.attribute1
3506 ,cle.attribute2
3507 ,cle.attribute3
3508 ,cle.attribute4
3509 ,cle.attribute5
3510 ,cle.attribute6
3511 ,cle.attribute7
3512 ,cle.attribute8
3513 ,cle.attribute9
3514 ,cle.attribute10
3515 ,cle.attribute11
3516 ,cle.attribute12
3517 ,cle.attribute13
3518 ,cle.attribute14
3519 ,cle.attribute15
3520 ,cle.bill_to_site_use_id -- 11510 Changes
3521 ,cle.ship_to_site_use_id -- 11510 Changes
3522 FROM
3523 okc_k_lines_b cle
3524 ,oki_sales_k_hdrs shd
3525 ,okc_k_items cim
3526 ,okc_statuses_b sts
3527 ,oki_job_run_dtl jrd -- 11510 Changes
3528 WHERE 1=1
3529 and cim.cle_id = cle.id
3530 and sts.code = cle.sts_code
3531 and cle.chr_id is not null
3532 and cle.dnz_chr_id = shd.chr_id
3533 and jrd.chr_id = shd.chr_id -- 11510 Changes
3534 and cim.jtot_object1_code IN ('OKX_SERVICE','OKX_WARRANTY') -- 11510 Changes
3535 and cle.lse_id IN (1,19,14) -- 11510 Changes
3536 ;
3537 COMMIT;
3538 l_errpos := 5;
3539
3540 -- recreate indexes
3541 create_indicies(p_object_name => 'OKI_SOLD_ITM_LINES'
3542 ,p_parm_rec => l_ind_rec);
3543 l_errpos := 6;
3544
3545
3546 OPEN g_latest_job_run_id_csr ;
3547 FETCH g_latest_job_run_id_csr INTO rec_g_latest_job_run_id_csr ;
3548 IF g_latest_job_run_id_csr%NOTFOUND THEN
3549 RAISE l_excp_no_job_run_id ;
3550 END IF ;
3551 l_job_run_id := rec_g_latest_job_run_id_csr.job_run_id ;
3552 CLOSE g_latest_job_run_id_csr ;
3553
3554 -- update oki_refreshes
3555 update_oki_refresh( p_object_name => 'OKI_SOLD_ITM_LINES'
3556 , p_job_run_id => l_job_run_id
3557 , x_retcode => retcode ) ;
3558 COMMIT ;
3559 IF retcode = '2' THEN
3560 -- update_oki_refresh failed, exit immediately.
3561 RAISE l_no_update_refresh;
3562 END IF ;
3563 l_errpos := 8;
3564
3565 -- analyze table
3566 fnd_stats.gather_table_stats(ownname => 'OKI'
3567 ,tabname => 'OKI_SOLD_ITM_LINES',percent=> 10);
3568 l_errpos := 9;
3569
3570 fnd_message.set_name(application => 'OKI'
3571 ,name => 'OKI_TABLE_LOAD_SUCCESS');
3572 fnd_message.set_token(token => 'TABLE_NAME'
3573 ,value => 'OKI_SOLD_ITM_LINES');
3574 fnd_file.put_line(which => fnd_file.log
3575 ,buff => fnd_message.get);
3576
3577 EXCEPTION
3578 WHEN l_excp_no_job_run_id THEN
3579 ROLLBACK;
3580 retcode := '2';
3581 fnd_message.set_name( application => 'OKI'
3582 , name => 'OKI_TABLE_LOAD_FAILURE');
3583 fnd_message.set_token( token => 'TABLE_NAME'
3584 , value => 'OKI_SOLD_ITM_LINES');
3585 fnd_file.put_line( which => fnd_file.log
3586 , buff => fnd_message.get);
3587
3588 fnd_message.set_name( application => 'OKI'
3589 , name => 'OKI_NO_JOB_RUN_ID_FAILURE');
3590 fnd_file.put_line( which => fnd_file.log
3591 , buff => fnd_message.get);
3592
3593 WHEN l_no_update_refresh then
3594 fnd_message.set_name(application => 'OKI'
3595 ,name => 'OKI_TABLE_LOAD_FAILURE');
3596 fnd_message.set_token(token => 'TABLE_NAME'
3597 ,value => 'OKI_SOLD_ITM_LINES');
3598 fnd_file.put_line(which => fnd_file.log
3599 ,buff => fnd_message.get);
3600
3601 fnd_file.put_line(which => fnd_file.log
3602 ,buff => 'Update of OKI_REFRESHS failed');
3603 WHEN OTHERS THEN
3604 l_sqlcode := sqlcode;
3605 l_sqlerrm := sqlerrm;
3606 ROLLBACK;
3607 retcode := '2';
3608 fnd_message.set_name( application => 'OKI'
3609 , name => 'OKI_TABLE_LOAD_FAILURE');
3610 fnd_message.set_token( token => 'TABLE_NAME'
3611 , value => 'OKI_SOLD_ITM_LINES');
3612 fnd_file.put_line( which => fnd_file.log
3613 , buff => fnd_message.get);
3614 fnd_file.put_line( which => fnd_file.log
3615 , buff => l_sqlcode||' '||l_sqlerrm);
3616 IF l_errpos < 5 THEN
3617 create_indicies(p_object_name => 'OKI_SOLD_ITM_LINES'
3618 ,p_parm_rec => l_ind_rec);
3619 ELSIF l_errpos = 5 THEN
3620 -- truncate table
3621 l_sql_string := 'TRUNCATE TABLE OKI.OKI_SOLD_ITM_LINES';
3622 EXECUTE IMMEDIATE l_sql_string;
3623 END IF;
3624
3625 END refresh_sold_itm_lines;
3626
3627 ---------------------------------------------------------------------
3628 --
3629 -- Procedure to load data into oki_sold_itm_lines without a complete
3630 -- refresh
3631 --
3632 ---------------------------------------------------------------------
3633 procedure fast_sold_itm_lines
3634 ( x_errbuf OUT NOCOPY VARCHAR2
3635 , x_retcode OUT NOCOPY VARCHAR2 ) IS
3636
3637 -- Exception to immediately exit the procedure
3638 l_excp_exit_immediate EXCEPTION ;
3639 -- Records have already been processed, just exit the program
3640 l_excp_no_processing EXCEPTION ;
3641 l_no_update_refresh EXCEPTION;
3642 l_sqlcode VARCHAR2(100) ;
3643 l_sqlerrm VARCHAR2(1000) ;
3644 l_errpos NUMBER := 0 ;
3645 -- Location within the program before the error was encountered.
3646 l_loc VARCHAR2(200) ;
3647 l_retcode VARCHAR2(1) ;
3648 l_process_yn VARCHAR2(1) ;
3649 l_job_run_id NUMBER ;
3650 l_job_curr_start_date DATE ;
3651 l_job_curr_end_date DATE ;
3652 l_table_name VARCHAR2(30) ;
3653
3654 BEGIN
3655 l_retcode := '0' ;
3656 l_table_name := 'OKI_SOLD_ITM_LINES' ;
3657
3658 l_loc := 'Calling process_refresh_check.' ;
3659 process_refresh_check(
3660 p_object_name => l_table_name
3661 , x_job_run_id => l_job_run_id
3662 , x_process_yn => l_process_yn
3663 , x_errbuf => x_errbuf
3664 , x_retcode => l_retcode ) ;
3665 l_loc := 'Determining status of process_refresh_check.' ;
3666 IF l_retcode = '2' THEN
3667 -- No job_run_id, exit immediately.
3668 RAISE l_excp_exit_immediate ;
3669 END IF ;
3670
3671 l_loc := 'Checking if records have already been processed.' ;
3672 IF l_process_yn = 'N' THEN
3673 -- Object has been refreshed successfully.
3674 RAISE l_excp_no_processing ;
3675 END IF ;
3676
3677 l_loc := 'Calling get_load_date_range.' ;
3678 get_load_date_range(
3679 p_job_run_id => l_job_run_id
3680 , p_job_curr_start_date => l_job_curr_start_date
3681 , p_job_curr_end_date => l_job_curr_end_date
3682 , x_errbuf => x_errbuf
3683 , x_retcode => l_retcode ) ;
3684 l_loc := 'Determining status of get_load_date_range' ;
3685 IF l_retcode = '2' THEN
3686 -- No job_curr_date range values, exit immediately.
3687 RAISE l_excp_exit_immediate ;
3688 END IF ;
3689
3690 l_loc := 'Deleting from ' || l_table_name ;
3691 DELETE FROM oki_sold_itm_lines sil
3692 WHERE chr_id in ( SELECT jrd.chr_id
3693 FROM oki_job_run_dtl jrd
3694 WHERE jrd.job_run_id = l_job_run_id ) ;
3695
3696 l_loc := 'Inserting into ' || l_table_name ;
3697 -- insert data
3698 INSERT INTO oki_sold_itm_lines(
3699 cle_id
3700 , chr_id
3701 , contract_number
3702 , contract_number_modifier
3703 , complete_contract_number
3704 , scs_code
3705 , line_number
3706 , start_date
3707 , end_date
3708 , duration
3709 , period
3710 , sts_code
3711 , ste_code
3712 , trn_code
3713 , date_terminated
3714 , date_renewed
3715 , number_of_items
3716 , uom_code
3717 , unit_price
3718 , unit_price_percent
3719 , price_negotiated
3720 , currency_code
3721 , price_negotiated_renewed
3722 , currency_code_renewed
3723 , sob_price_unit
3724 , sob_price_negotiated
3725 , sob_price_negotiated_renewed
3726 , sob_currency_code
3727 , base_price_unit
3728 , base_price_negotiated
3729 , base_price_negotiated_renewed
3730 , base_currency_code
3731 , sold_item
3732 , item_id
3733 , concatenated_segments
3734 , authoring_org_id
3735 , inventory_organization_id
3736 , creation_date
3737 , last_update_date
3738 , attribute_category
3739 , attribute1
3740 , attribute2
3741 , attribute3
3742 , attribute4
3743 , attribute5
3744 , attribute6
3745 , attribute7
3746 , attribute8
3747 , attribute9
3748 , attribute10
3749 , attribute11
3750 , attribute12
3751 , attribute13
3752 , attribute14
3753 , attribute15
3754 , bill_to_site_use_id -- 11510 Changes
3755 , ship_to_site_use_id -- 11510 Changes
3756 ) SELECT
3757 cle.id
3758 , shd.chr_id
3759 , shd.contract_number
3760 , shd.contract_number_modifier
3761 , shd.complete_contract_number
3762 , shd.scs_code
3763 , cle.line_number
3764 , TRUNC(cle.start_date)
3765 , TRUNC(cle.end_date)
3766 , oki_disco_util_pub.get_duration(cle.start_date,cle.end_date)
3767 , oki_disco_util_pub.get_period(cle.start_date,cle.end_date)
3768 , cle.sts_code
3769 , sts.ste_code
3770 , cle.trn_code
3771 , TRUNC(cle.date_terminated)
3772 , TRUNC(cle.date_renewed)
3773 , cim.number_of_items
3774 , cim.uom_code
3775 , cle.price_unit
3776 , cle.price_unit_percent
3777 , cle.price_negotiated
3778 , shd.currency_code
3779 , cle.price_negotiated_renewed
3780 , cle.currency_code_renewed
3781 , (cle.price_unit * jrd.trx_func_rate) -- 11510 Changes
3782 , (cle.price_negotiated * jrd.trx_func_rate) -- 11510 Changes
3783 , null
3784 , shd.sob_currency_code
3785 , (cle.price_unit * jrd.trx_base_rate) -- 11510 Changes
3786 , (cle.price_negotiated * jrd.trx_base_rate) -- 11510 Changes
3787 , null
3788 , l_base_currency
3789 , null
3790 , cim.object1_id1
3791 , null
3792 , shd.authoring_org_id
3793 , cim.object1_id2
3794 , cle.creation_date
3795 , cle.last_update_date
3796 , cle.attribute_category
3797 , cle.attribute1
3798 , cle.attribute2
3799 , cle.attribute3
3800 , cle.attribute4
3801 , cle.attribute5
3802 , cle.attribute6
3803 , cle.attribute7
3804 , cle.attribute8
3805 , cle.attribute9
3806 , cle.attribute10
3807 , cle.attribute11
3808 , cle.attribute12
3809 , cle.attribute13
3810 , cle.attribute14
3811 , cle.attribute15
3812 , cle.bill_to_site_use_id -- 11510 Changes
3813 , cle.ship_to_site_use_id -- 11510 Changes
3814 FROM
3815 okc_k_lines_b cle
3816 , oki_sales_k_hdrs shd
3817 , okc_k_items cim
3818 , okc_statuses_b sts
3819 , oki_job_run_dtl jrd
3820 WHERE 1=1
3821 AND cim.cle_id = cle.id
3822 and sts.code = cle.sts_code
3823 AND cle.chr_id IS NOT NULL
3824 AND cle.dnz_chr_id = shd.chr_id
3825 AND jrd.chr_id = shd.chr_id
3826 AND jrd.job_run_id = l_job_run_id
3827 AND jrd.action_flag = 'I'
3828 AND cim.jtot_object1_code IN ('OKX_SERVICE','OKX_WARRANTY') -- 11510 Changes
3829 ;
3830
3831 l_loc := 'Updating OKI_REFRESHS.' ;
3832 -- update oki_refreshes
3833 update_oki_refresh(p_object_name => l_table_name
3834 , p_job_run_id => l_job_run_id
3835 , x_retcode => l_retcode ) ;
3836
3837 COMMIT;
3838 IF l_retcode = 2 THEN
3839 RAISE l_no_update_refresh;
3840 END IF;
3841
3842 l_loc := 'Analyzing ' || l_table_name ;
3843 -- analyze table
3844 fnd_stats.gather_table_stats( ownname => 'OKI'
3845 , tabname => l_table_name
3846 , percent => 10 ) ;
3847
3848 fnd_message.set_name( application => 'OKI'
3849 , name => 'OKI_TABLE_LOAD_SUCCESS' ) ;
3850 fnd_message.set_token( token => 'TABLE_NAME'
3851 , value => l_table_name ) ;
3852 fnd_file.put_line( which => fnd_file.log
3853 , buff => fnd_message.get ) ;
3854
3855 EXCEPTION
3856 WHEN l_excp_no_processing then
3857 -- Do not log an error ; It has already been logged.
3858 -- Just exit the program and continue with the other table load
3859 null ;
3860
3861 WHEN l_excp_exit_immediate THEN
3862 -- Do not log an error ; It has already been logged.
3863 -- Set return code to error
3864 x_retcode := '2' ;
3865
3866 when l_no_update_refresh then
3867 fnd_message.set_name(application => 'OKI'
3868 ,name => 'OKI_TABLE_LOAD_FAILURE');
3869 fnd_message.set_token(token => 'TABLE_NAME'
3870 ,value => 'OKI_SOLD_ITM_LINES');
3871 fnd_file.put_line(which => fnd_file.log
3872 ,buff => fnd_message.get);
3873
3874 fnd_file.put_line(which => fnd_file.log
3875 ,buff => 'Update of OKI_REFRESHS failed');
3876 WHEN OTHERS THEN
3877 l_sqlcode := sqlcode;
3878 l_sqlerrm := sqlerrm;
3879 ROLLBACK;
3880 x_retcode := '2';
3881 fnd_message.set_name( application => 'OKI'
3882 , name => 'OKI_TABLE_LOAD_FAILURE' ) ;
3883 fnd_message.set_token( token => 'TABLE_NAME'
3884 , value => l_table_name ) ;
3885 fnd_file.put_line( which => fnd_file.log
3886 , buff => fnd_message.get ) ;
3887 fnd_file.put_line( which => fnd_file.log
3888 , buff => l_sqlcode || ' ' || l_sqlerrm ) ;
3889 END fast_sold_itm_lines ;
3890
3891 ---------------------------------------------------------------------
3892 --
3893 -- procedure to refresh the covered product lines
3894 --
3895 ---------------------------------------------------------------------
3896 procedure refresh_cov_prd_lines(errbuf OUT NOCOPY VARCHAR2
3897 ,retcode OUT NOCOPY VARCHAR2) IS
3898
3899
3900 -- Exception to immediately exit the procedure
3901 l_excp_no_job_run_id EXCEPTION ;
3902 l_no_update_refresh EXCEPTION;
3903 l_index_tab vc_tab_type;
3904 l_sql_string VARCHAR2(4000);
3905 l_sqlcode VARCHAR2(100);
3906 l_sqlerrm VARCHAR2(1000);
3907 l_errpos NUMBER := 0;
3908
3909 -- variables to set up the fast refresh job
3910 l_job_run_id NUMBER ;
3911
3912 BEGIN
3913 retcode := '0';
3914
3915 -- get representative index storage parms for later use
3916 OPEN index_info_csr('OKI_COV_PRD_LINES','OKI_COV_PRD_LINES_U1','OKI');
3917 FETCH index_info_csr INTO l_ind_rec;
3918 CLOSE index_info_csr;
3919 l_errpos := 1;
3920
3921 -- drop indexes
3922 OPEN index_name_csr('OKI_COV_PRD_LINES');
3923 FETCH index_name_csr BULK COLLECT INTO l_index_tab;
3924 CLOSE index_name_csr;
3925
3926 IF l_index_tab.first IS NOT NULL THEN
3927 FOR i IN l_index_tab.first..l_index_tab.last LOOP
3928 l_sql_string := 'DROP INDEX OKI.'||l_index_tab(i);
3929 EXECUTE IMMEDIATE l_sql_string;
3930 END LOOP;
3931 END IF;
3932 l_errpos := 2;
3933
3934 -- truncate table
3935 l_sql_string := 'TRUNCATE TABLE OKI.OKI_COV_PRD_LINES';
3936 EXECUTE IMMEDIATE l_sql_string;
3937 l_errpos := 3;
3938
3939 l_sql_string := 'ALTER SESSION ENABLE PARALLEL DML';
3940 EXECUTE IMMEDIATE l_sql_string;
3941 l_errpos := 4;
3942
3943 l_sql_string := 'alter session set hash_area_size=100000000';
3944 EXECUTE IMMEDIATE l_sql_string;
3945 l_sql_string := 'alter session set sort_area_size=100000000';
3946 EXECUTE IMMEDIATE l_sql_string;
3947
3948 -- insert data
3949 INSERT /*+ append */ INTO OKI_COV_PRD_LINES
3950 (
3951 CLE_ID,
3952 CHR_ID,
3953 PARENT_CLE_ID,
3954 SCS_CODE,
3955 CONTRACT_NUMBER,
3956 CONTRACT_NUMBER_MODIFIER,
3957 COMPLETE_CONTRACT_NUMBER,
3958 LINE_NUMBER,
3959 START_DATE,
3960 END_DATE,
3961 DURATION,
3962 PERIOD,
3963 STS_CODE,
3964 ste_code,
3965 TRN_CODE,
3966 DATE_TERMINATED,
3967 DATE_RENEWED,
3968 NUMBER_OF_ITEMS,
3969 UOM_CODE,
3970 UNIT_PRICE,
3971 UNIT_PRICE_PERCENT,
3972 PRICE_NEGOTIATED,
3973 CURRENCY_CODE,
3974 PRICE_NEGOTIATED_RENEWED,
3975 CURRENCY_CODE_RENEWED,
3976 SOB_PRICE_UNIT,
3977 SOB_PRICE_NEGOTIATED,
3978 SOB_PRICE_NEGOTIATED_RENEWED,
3979 SOB_CURRENCY_CODE,
3980 BASE_PRICE_UNIT,
3981 BASE_PRICE_NEGOTIATED,
3982 BASE_PRICE_NEGOTIATED_RENEWED,
3983 BASE_CURRENCY_CODE,
3984 SERVICE_ITEM,
3985 COVERED_PRODUCT_ID,
3986 CUSTOMER_PRODUCT_ITEM_ID,
3987 CONCATENATED_SEGMENTS,
3988 SERIAL_NUMBER,
3989 REFERENCE_NUMBER,
3990 COV_PROD_QUANTITY,
3991 COV_PROD_UOM,
3992 INSTALLATION_DATE,
3993 COV_PROD_ORDER_DATE,
3994 COV_PROD_ORDER_NUMBER,
3995 COV_PROD_ORDER_LINE,
3996 COV_PROD_NET_AMOUNT,
3997 COV_PROD_ORDER_LINE_ID,
3998 SYSTEM_ID,
3999 SYSTEM_NAME,
4000 PRODUCT_AGREEMENT_ID,
4001 COV_PROD_BILL_TO_SITE_ID,
4002 COV_PROD_SHIP_TO_SITE_ID,
4003 COV_PROD_INSTALL_SITE_ID,
4004 COV_PROD_BILL_TO_CONTACT_ID,
4005 COV_PROD_SHIP_TO_CONTACT_ID,
4006 AUTHORING_ORG_ID,
4007 INVENTORY_ORGANIZATION_ID,
4008 CREATION_DATE,
4009 LAST_UPDATE_DATE,
4010 ATTRIBUTE_CATEGORY,
4011 ATTRIBUTE1,
4012 ATTRIBUTE2,
4013 ATTRIBUTE3,
4014 ATTRIBUTE4,
4015 ATTRIBUTE5,
4016 ATTRIBUTE6,
4017 ATTRIBUTE7,
4018 ATTRIBUTE8,
4019 ATTRIBUTE9,
4020 ATTRIBUTE10,
4021 ATTRIBUTE11,
4022 ATTRIBUTE12,
4023 ATTRIBUTE13,
4024 ATTRIBUTE14,
4025 ATTRIBUTE15 ,
4026 PRICING_ATTRIBUTE1,
4027 PRICING_ATTRIBUTE2,
4028 PRICING_ATTRIBUTE3,
4029 PRICING_ATTRIBUTE4,
4030 PRICING_ATTRIBUTE5,
4031 PRICING_ATTRIBUTE6,
4032 PRICING_ATTRIBUTE7,
4033 PRICING_ATTRIBUTE8,
4034 PRICING_ATTRIBUTE9,
4035 PRICING_ATTRIBUTE10,
4036 /* 11510 changes start added these columns*/
4037 END_PERIOD_NUM ,
4038 END_PERIOD_NAME ,
4039 END_QUARTER ,
4040 END_YEAR ,
4041 IS_EXP_NOT_RENEWED_YN
4042 /* 11510 changes end */
4043 )
4044 SELECT /*+ leading(sil) */
4045 cle.id
4046 ,sil.chr_id
4047 ,cle.cle_id
4048 ,sil.scs_code
4049 ,sil.contract_number
4050 ,sil.contract_number_modifier
4051 ,sil.complete_contract_number
4052 ,sil.line_number ||'.'|| cle.line_number
4053 ,trunc(cle.start_date)
4054 ,trunc(cle.end_date)
4055 ,oki_disco_util_pub.get_duration(cle.start_date,cle.end_date)
4056 ,oki_disco_util_pub.get_period(cle.start_date,cle.end_date)
4057 ,cle.sts_code
4058 ,sts.ste_code
4059 ,cle.trn_code
4060 ,trunc(cle.date_terminated)
4061 ,trunc(cle.date_renewed)
4062 ,cim.number_of_items
4063 ,cim.uom_code
4064 ,cle.price_unit
4065 ,cle.price_unit_percent
4066 ,cle.price_negotiated
4067 ,sil.currency_code
4068 ,cle.price_negotiated_renewed
4069 ,cle.currency_code_renewed
4070 /* 11510 changes start */
4071 ,cle.price_unit * jrd.trx_func_rate --(cle.price_unit * nvl(kcr.sob_currency_conv_rate,dre1.conversion_rate))
4072 ,cle.price_negotiated * jrd.trx_func_rate --(cle.price_negotiated * nvl(kcr.sob_currency_conv_rate,dre1.conversion_rate))
4073 /* 11510 changes end */
4074 ,null
4075 ,sil.sob_currency_code
4076 /* 11510 changes start */
4077 ,cle.price_unit * jrd.trx_base_rate --(cle.price_unit * nvl(kcr.base_currency_conv_rate,dre3.conversion_rate))
4078 ,cle.price_negotiated * jrd.trx_base_rate --(cle.price_negotiated * nvl(kcr.base_currency_conv_rate,dre3.conversion_rate))
4079 /* 11510 changes end */
4080 ,null
4081 ,l_base_currency
4082 ,sil.sold_item
4083 ,cii.instance_id
4084 ,cii.inventory_item_id
4085 ,null
4086 ,cii.serial_number
4087 ,cii.instance_number
4088 ,cii.quantity
4089 ,cii.unit_of_measure
4090 ,cii.install_date
4091 ,null
4092 ,null
4093 ,null
4094 ,null
4095 ,cii.last_oe_order_line_id
4096 ,cii.system_id
4097 ,null
4098 ,cii.last_oe_agreement_id
4099 ,null
4100 ,null
4101 ,cii.install_location_id
4102 ,null
4103 ,null
4104 ,sil.authoring_org_id
4105 ,sil.inventory_organization_id
4106 ,cle.creation_date
4107 ,cle.last_update_date
4108 ,cle.attribute_category
4109 ,cle.attribute1
4110 ,cle.attribute2
4111 ,cle.attribute3
4112 ,cle.attribute4
4113 ,cle.attribute5
4114 ,cle.attribute6
4115 ,cle.attribute7
4116 ,cle.attribute8
4117 ,cle.attribute9
4118 ,cle.attribute10
4119 ,cle.attribute11
4120 ,cle.attribute12
4121 ,cle.attribute13
4122 ,cle.attribute14
4123 ,cle.attribute15
4124 ,null
4125 ,null
4126 ,null
4127 ,null
4128 ,null
4129 ,null
4130 ,null
4131 ,null
4132 ,null
4133 ,null
4134 /*11510 changes start*/
4135 , epd.period_num
4136 , epd.period_name
4137 , epd.quarter_num
4138 , epd.period_year
4139 --Bug Fix 3469671 code changes-------------------------------------------------
4140 , DECODE(sts.ste_code,'EXPIRED' -- If contract is expired then
4141 ,DECODE(cle.price_level_ind,'Y' --check if line is priced
4142 ,DECODE(cle.date_renewed,NULL,'Y' --check if renewed or record from operation line exists
4143 ,DECODE(exp.cle_id,NULL,'Y','N') --if so 'N' else 'Y'
4144 )
4145 ,'N')--if the line is not priced 'N'
4146 ,'N')--if the contract is not expired 'N'
4147 is_exp_not_renewed_yn
4148 --Bug Fix 3469671 code changes-------------------------------------------------
4149 from
4150 oki_sold_itm_lines sil
4151 /*11510 changes added*/
4152 ,oki_job_run_dtl jrd
4153 ,okc_k_lines_b cle
4154 ,okc_k_items cim
4155 ,csi_item_instances cii
4156 ,okc_statuses_b sts
4157 /*11510 changes added*/
4158 ,gl_periods epd
4159 ,(select distinct object_cle_id cle_id from okc_operation_lines
4160 where active_yn = 'Y' ) exp
4161 where 1 = 1
4162 and cii.instance_id = to_number(cim.object1_id1)
4163 and cim.cle_id = cle.id
4164 and sts.code = cle.sts_code
4165 and cle.cle_id = sil.cle_id
4166 /* 11510 changes added join for expired inline view */
4167 and exp.cle_id(+) = cle.cle_id
4168 and cle.lse_id in (9, 18, 25)
4169 /*11510 changes start*/
4170 and jrd.chr_id = sil.chr_id
4171 and cle.end_date between epd.start_date and epd.end_date+0.99999
4172 and epd.period_type = jrd.accounted_period_type
4173 and epd.period_set_name = jrd.period_set_name
4174 and epd.adjustment_period_flag = 'N'
4175 /*11510 changes end */
4176 ;
4177
4178 COMMIT;
4179 l_errpos := 5;
4180
4181 -- recreate indexes
4182 create_indicies(p_object_name => 'OKI_COV_PRD_LINES'
4183 ,p_parm_rec => l_ind_rec);
4184 l_errpos := 6;
4185
4186 OPEN g_latest_job_run_id_csr ;
4187 FETCH g_latest_job_run_id_csr INTO rec_g_latest_job_run_id_csr ;
4188 IF g_latest_job_run_id_csr%NOTFOUND THEN
4189 RAISE l_excp_no_job_run_id ;
4190 END IF ;
4191 l_job_run_id := rec_g_latest_job_run_id_csr.job_run_id ;
4192 CLOSE g_latest_job_run_id_csr ;
4193
4194 -- update oki_refreshes
4195 update_oki_refresh( p_object_name => 'OKI_COV_PRD_LINES'
4196 , p_job_run_id => l_job_run_id
4197 , x_retcode => retcode);
4198 if retcode =2 THEN
4199 raise l_no_update_refresh;
4200 END IF;
4201 l_errpos := 8;
4202
4203 -- analyze table
4204 fnd_stats.gather_table_stats(ownname => 'OKI'
4205 ,tabname => 'OKI_COV_PRD_LINES',percent=> 10);
4206 l_errpos := 9;
4207
4208 fnd_message.set_name(application => 'OKI'
4209 ,name => 'OKI_TABLE_LOAD_SUCCESS');
4210 fnd_message.set_token(token => 'TABLE_NAME'
4211 ,value => 'OKI_COV_PRD_LINES');
4212 fnd_file.put_line(which => fnd_file.log
4213 ,buff => fnd_message.get);
4214
4215 EXCEPTION
4216 WHEN l_no_update_refresh THEN
4217 fnd_message.set_name( application => 'OKI'
4218 , name => 'OKI_TABLE_LOAD_FAILURE');
4219 fnd_message.set_token( token => 'TABLE_NAME'
4220 , value => 'OKI_COV_PRD_LINES');
4221 fnd_file.put_line( which => fnd_file.log
4222 , buff => fnd_message.get);
4223 fnd_file.put_line( which => fnd_file.log
4224 , buff =>'Update of OKI_REFRESHS failed' );
4225
4226 WHEN l_excp_no_job_run_id THEN
4227 ROLLBACK;
4228 retcode := '2';
4229
4230 fnd_message.set_name( application => 'OKI'
4231 , name => 'OKI_TABLE_LOAD_FAILURE');
4232 fnd_message.set_token( token => 'TABLE_NAME'
4233 , value => 'OKI_COV_PRD_LINES');
4234 fnd_file.put_line( which => fnd_file.log
4235 , buff => fnd_message.get);
4236
4237 fnd_message.set_name( application => 'OKI'
4238 , name => 'OKI_NO_JOB_RUN_ID_FAILURE');
4239 fnd_file.put_line( which => fnd_file.log
4240 , buff => fnd_message.get);
4241 WHEN OTHERS THEN
4242 l_sqlcode := sqlcode;
4243 l_sqlerrm := sqlerrm;
4244 ROLLBACK;
4245 retcode := '2';
4246
4247 fnd_message.set_name( application => 'OKI'
4248 , name => 'OKI_TABLE_LOAD_FAILURE');
4249 fnd_message.set_token( token => 'TABLE_NAME'
4250 , value => 'OKI_COV_PRD_LINES');
4251 fnd_file.put_line( which => fnd_file.log
4252 , buff => fnd_message.get);
4253 fnd_file.put_line( which => fnd_file.log
4254 , buff => l_sqlcode||' '||l_sqlerrm);
4255 IF l_errpos < 5 THEN
4256 create_indicies( p_object_name => 'OKI_COV_PRD_LINES'
4257 , p_parm_rec => l_ind_rec);
4258 ELSIF l_errpos = 5 THEN
4259 -- truncate table
4260 l_sql_string := 'TRUNCATE TABLE OKI.OKI_COV_PRD_LINES';
4261 EXECUTE IMMEDIATE l_sql_string;
4262 END IF;
4263 END refresh_cov_prd_lines;
4264
4265 ---------------------------------------------------------------------
4266 --
4267 -- Procedure to load data into oki_cov_prd_lines without a complete
4268 -- refresh
4269 --
4270 ---------------------------------------------------------------------
4271 procedure fast_cov_prd_lines
4272 ( x_errbuf OUT NOCOPY VARCHAR2
4273 , x_retcode OUT NOCOPY VARCHAR2 ) IS
4274
4275 -- Exception to immediately exit the procedure
4276 l_excp_exit_immediate EXCEPTION ;
4277 -- Records have already been processed, just exit the program
4278 l_excp_no_processing EXCEPTION ;
4279 l_no_update_refresh EXCEPTION;
4280 l_sqlcode VARCHAR2(100);
4281 l_sqlerrm VARCHAR2(1000);
4282 l_errpos NUMBER := 0;
4283 -- Location within the program before the error was encountered.
4284 l_loc VARCHAR2(200) ;
4285 l_retcode VARCHAR2(1) ;
4286 l_process_yn VARCHAR2(1) ;
4287 l_job_run_id NUMBER ;
4288 l_job_curr_start_date DATE ;
4289 l_job_curr_end_date DATE ;
4290 l_table_name VARCHAR2(30);
4291
4292
4293 BEGIN
4294 l_retcode := '0';
4295 l_table_name := 'OKI_COV_PRD_LINES' ;
4296
4297 l_loc := 'Calling process_refresh_check.' ;
4298 process_refresh_check(
4299 p_object_name => 'OKI_COV_PRD_LINES'
4300 , x_job_run_id => l_job_run_id
4301 , x_process_yn => l_process_yn
4302 , x_errbuf => x_errbuf
4303 , x_retcode => l_retcode ) ;
4304 l_loc := 'Determining status of process_refresh_check.' ;
4305 IF l_retcode = '2' THEN
4306 -- No job_run_id, exit immediately.
4307 RAISE l_excp_exit_immediate ;
4308 END IF ;
4309
4310 l_loc := 'Checking if records have already been processed.' ;
4311 IF l_process_yn = 'N' THEN
4312 -- Object has been refreshed successfully.
4313 RAISE l_excp_no_processing ;
4314 END IF ;
4315
4316 l_loc := 'Calling get_load_date_range.' ;
4317 get_load_date_range(
4318 p_job_run_id => l_job_run_id
4319 , p_job_curr_start_date => l_job_curr_start_date
4320 , p_job_curr_end_date => l_job_curr_end_date
4321 , x_errbuf => x_errbuf
4322 , x_retcode => l_retcode ) ;
4323 l_loc := 'Determining status of get_load_date_range' ;
4324 IF l_retcode = '2' THEN
4325 -- No job_curr_date range values, exit immediately.
4326 RAISE l_excp_exit_immediate ;
4327 END IF ;
4328
4329 l_loc := 'Deleting from ' || l_table_name ;
4330 DELETE FROM oki_cov_prd_lines cpl
4331 WHERE chr_id in ( SELECT /*+ index_ffs(jrd oki_job_run_dtl_u1)*/
4332 jrd.chr_id
4333 FROM oki_job_run_dtl jrd
4334 WHERE jrd.job_run_id = l_job_run_id ) ;
4335
4336 l_loc := 'Inserting into ' || l_table_name ;
4337 -- insert data
4338 INSERT INTO oki_cov_prd_lines (
4339 cle_id
4340 , chr_id
4341 , parent_cle_id
4342 , scs_code
4343 , contract_number
4344 , contract_number_modifier
4345 , complete_contract_number
4346 , line_number
4347 , start_date
4348 , end_date
4349 , duration
4350 , period
4351 , sts_code
4352 , ste_code
4353 , trn_code
4354 , date_terminated
4355 , date_renewed
4356 , number_of_items
4357 , uom_code
4358 , unit_price
4359 , unit_price_percent
4360 , price_negotiated
4361 , currency_code
4362 , price_negotiated_renewed
4363 , currency_code_renewed
4364 , sob_price_unit
4365 , sob_price_negotiated
4366 , sob_price_negotiated_renewed
4367 , sob_currency_code
4368 , base_price_unit
4369 , base_price_negotiated
4370 , base_price_negotiated_renewed
4371 , base_currency_code
4372 , service_item
4373 , covered_product_id
4374 , customer_product_item_id
4375 , concatenated_segments
4376 , serial_number
4377 , reference_number
4378 , cov_prod_quantity
4379 , cov_prod_uom
4380 , installation_date
4381 , cov_prod_order_date
4382 , cov_prod_order_number
4383 , cov_prod_order_line
4384 , cov_prod_net_amount
4385 , cov_prod_order_line_id
4386 , system_id
4387 , system_name
4388 , product_agreement_id
4389 , cov_prod_bill_to_site_id
4390 , cov_prod_ship_to_site_id
4391 , cov_prod_install_site_id
4392 , cov_prod_bill_to_contact_id
4393 , cov_prod_ship_to_contact_id
4394 , authoring_org_id
4395 , inventory_organization_id
4396 , creation_date
4397 , last_update_date
4398 , attribute_category
4399 , attribute1
4400 , attribute2
4401 , attribute3
4402 , attribute4
4403 , attribute5
4404 , attribute6
4405 , attribute7
4406 , attribute8
4407 , attribute9
4408 , attribute10
4409 , attribute11
4410 , attribute12
4411 , attribute13
4412 , attribute14
4413 , attribute15
4414 , pricing_attribute1
4415 , pricing_attribute2
4416 , pricing_attribute3
4417 , pricing_attribute4
4418 , pricing_attribute5
4419 , pricing_attribute6
4420 , pricing_attribute7
4421 , pricing_attribute8
4422 , pricing_attribute9
4423 , pricing_attribute10
4424 /* 11510 changes start */
4425 , end_period_num
4426 , end_period_name
4427 , end_quarter
4428 , end_year
4429 , is_exp_not_renewed_yn
4430 /* 11510 changes start */
4431 ) SELECT /*+ leading(jrd) full(jrd) cardinality(jrd,1) */
4432 cle.id
4433 , sil.chr_id
4434 , cle.cle_id
4435 , sil.scs_code
4436 , sil.contract_number
4437 , sil.contract_number_modifier
4438 , sil.complete_contract_number
4439 , sil.line_number ||'.'|| cle.line_number
4440 , TRUNC(cle.start_date)
4441 , TRUNC(cle.end_date)
4442 ,oki_disco_util_pub.get_duration(cle.start_date,cle.end_date)
4443 ,oki_disco_util_pub.get_period(cle.start_date,cle.end_date)
4444 , cle.sts_code
4445 , sts.ste_code
4446 , cle.trn_code
4447 , TRUNC(cle.date_terminated)
4448 , TRUNC(cle.date_renewed)
4449 , cim.number_of_items
4450 , cim.uom_code
4451 , cle.price_unit
4452 , cle.price_unit_percent
4453 , cle.price_negotiated
4454 , sil.currency_code
4455 , cle.price_negotiated_renewed
4456 , cle.currency_code_renewed
4457 /* 11510 changes start */
4458 , cle.price_unit * jrd.trx_func_rate --(cle.price_unit * NVL(kcr.sob_currency_conv_rate, dre1.conversion_rate))
4459 , cle.price_negotiated * jrd.trx_func_rate --(cle.price_negotiated * NVL(kcr.sob_currency_conv_rate,dre1.conversion_rate))
4460 /* 11510 changes start */
4461 , null--(cle.price_negotiated_renewed * NVL(kcr.renewed_sob_conv_rate,
4462 -- dre2.conversion_rate))
4463 , sil.sob_currency_code
4464 /* 11510 changes start */
4465 , cle.price_unit * jrd.trx_base_rate --(cle.price_unit * NVL(kcr.base_currency_conv_rate,dre3.conversion_rate))
4466 , cle.price_negotiated * jrd.trx_base_rate --(cle.price_negotiated * NVL(kcr.base_currency_conv_rate,dre3.conversion_rate))
4467 /* 11510 changes start */
4468 , null --(cle.price_negotiated_renewed * NVL(kcr.renewed_base_conv_rate,
4469 -- dre4.conversion_rate))
4470 , l_base_currency
4471 , sil.sold_item
4472 , cii.instance_id
4473 , cii.inventory_item_id
4474 , null
4475 , cii.serial_number
4476 , cii.instance_number
4477 , cii.quantity
4478 , cii.unit_of_measure
4479 , cii.install_date
4480 , null
4481 , null
4482 , null
4483 , null
4484 , cii.last_oe_order_line_id
4485 , cii.system_id
4486 , null
4487 , cii.last_oe_agreement_id
4488 , null
4489 , null
4490 , cii.install_location_id
4491 , null
4492 , null
4493 , sil.authoring_org_id
4494 , sil.inventory_organization_id
4495 , cle.creation_date
4496 , cle.last_update_date
4497 , cle.attribute_category
4498 , cle.attribute1
4499 , cle.attribute2
4500 , cle.attribute3
4501 , cle.attribute4
4502 , cle.attribute5
4503 , cle.attribute6
4504 , cle.attribute7
4505 , cle.attribute8
4506 , cle.attribute9
4507 , cle.attribute10
4508 , cle.attribute11
4509 , cle.attribute12
4510 , cle.attribute13
4511 , cle.attribute14
4512 , cle.attribute15
4513 , null
4514 , null
4515 , null
4516 , null
4517 , null
4518 , null
4519 , null
4520 , null
4521 , null
4522 , null
4523 , epd.period_num
4524 , epd.period_name
4525 , epd.quarter_num
4526 , epd.period_year
4527 --Bug Fix 3469671 code changes-------------------------------------------------
4528 , DECODE(sts.ste_code,'EXPIRED' -- If contract is expired then
4529 ,DECODE(cle.price_level_ind,'Y' --check if line is priced
4530 ,DECODE(cle.date_renewed,NULL,'Y' --check if renewed or record from operation line exists
4531 ,DECODE(exp.cle_id,NULL,'Y','N') --if so 'N' else 'Y'
4532 )
4533 ,'N')--if the line is not priced 'N'
4534 ,'N')--if the contract is not expired 'N'
4535 is_exp_not_renewed_yn
4536 --Bug Fix 3469671 code changes-------------------------------------------------
4537 FROM
4538 okc_k_lines_b cle
4539 , oki_sold_itm_lines sil
4540 , okc_k_items cim
4541 , csi_item_instances cii
4542 , okc_statuses_b sts
4543 , oki_job_run_dtl jrd
4544 /*11510 changes added*/
4545 , gl_periods epd
4546 ,(select distinct object_cle_id cle_id from okc_operation_lines
4547 where active_yn = 'Y' ) exp --Bug Fix 3469671 code changes
4548 WHERE
4549 cii.instance_id = to_number(cim.object1_id1)
4550 AND exp.cle_id(+) = cle.id --Bug Fix 3469671 code changes
4551 AND cim.cle_id = cle.id
4552 AND sts.code = cle.sts_code
4553 AND cle.cle_id = sil.cle_id
4554 AND cle.lse_id IN (9, 18, 25)
4555 AND jrd.chr_id = sil.chr_id
4556 AND jrd.job_run_id = l_job_run_id
4557 AND jrd.action_flag = 'I'
4558 /*11510 changes start*/
4559 AND cle.end_date between epd.start_date and epd.end_date+0.99999
4560 AND epd.period_type = jrd.accounted_period_type
4561 AND epd.period_set_name = jrd.period_set_name
4562 AND epd.adjustment_period_flag = 'N'
4563 /*11510 changes end*/
4564 ;
4565
4566 l_loc := 'Updating OKI_REFRESHS.' ;
4567 -- update oki_refreshes
4568 update_oki_refresh(p_object_name => 'OKI_COV_PRD_LINES'
4569 , p_job_run_id => l_job_run_id
4570 , x_retcode => l_retcode ) ;
4571 COMMIT;
4572
4573 IF l_retcode = 2 THEN
4574 RAISE l_no_update_refresh;
4575 END IF;
4576
4577 l_loc := 'Analyzing ' || l_table_name ;
4578 -- analyze table
4579 fnd_stats.gather_table_stats( ownname => 'OKI'
4580 , tabname => l_table_name
4581 , percent => 10 ) ;
4582
4583 fnd_message.set_name( application => 'OKI'
4584 , name => 'OKI_TABLE_LOAD_SUCCESS' ) ;
4585 fnd_message.set_token( token => 'TABLE_NAME'
4586 , value => l_table_name ) ;
4587 fnd_file.put_line( which => fnd_file.log
4588 , buff => fnd_message.get ) ;
4589
4590 EXCEPTION
4591 WHEN l_excp_no_processing then
4592 -- Do not log an error ; It has already been logged.
4593 -- Just exit the program and continue with the other table load
4594 null ;
4595
4596 WHEN l_excp_exit_immediate THEN
4597 -- Do not log an error ; It has already been logged.
4598 -- Set return code to error
4599 x_retcode := '2' ;
4600
4601 when l_no_update_refresh then
4602 fnd_message.set_name(application => 'OKI'
4603 ,name => 'OKI_TABLE_LOAD_FAILURE');
4604 fnd_message.set_token(token => 'TABLE_NAME'
4605 ,value => 'OKI_COV_PRD_LINES');
4606 fnd_file.put_line(which => fnd_file.log
4607 ,buff => fnd_message.get);
4608
4609 fnd_file.put_line(which => fnd_file.log
4610 ,buff => 'Update of OKI_REFRESHS failed');
4611
4612 WHEN OTHERS THEN
4613 l_sqlcode := sqlcode;
4614 l_sqlerrm := sqlerrm;
4615 ROLLBACK;
4616 x_retcode := '2';
4617 fnd_message.set_name( application => 'OKI'
4618 , name => 'OKI_TABLE_LOAD_FAILURE' ) ;
4619 fnd_message.set_token( token => 'TABLE_NAME'
4620 , value => l_table_name ) ;
4621 fnd_file.put_line( which => fnd_file.log
4622 , buff => fnd_message.get ) ;
4623 fnd_file.put_line( which => fnd_file.log
4624 , buff => l_sqlcode || ' ' || l_sqlerrm ) ;
4625 END fast_cov_prd_lines ;
4626 ---------------------------------------------------------------------
4627 --
4628 -- load the expired lines table
4629 --
4630 ---------------------------------------------------------------------
4631
4632 -- 11510 Change: Stub out the procedure
4633
4634 procedure refresh_expired_lines(errbuf OUT NOCOPY VARCHAR2
4635 ,retcode OUT NOCOPY VARCHAR2) IS
4636
4637 l_sql_string VARCHAR2(2000);
4638
4639 BEGIN
4640
4641 l_sql_string := 'Procedure no longer used' ;
4642
4643 END refresh_expired_lines;
4644
4645 ---------------------------------------------------------------------
4646 --
4647 -- Procedure to load data into oki_expired_lines without a complete
4648 -- refresh
4649 --
4650 ---------------------------------------------------------------------
4651
4652 -- 11510 Change: Stub out the procedure
4653
4654 procedure fast_expired_lines
4655 (
4656 x_errbuf OUT NOCOPY VARCHAR2
4657 , x_retcode OUT NOCOPY VARCHAR2
4658 ) IS
4659
4660 l_sql_string VARCHAR2(2000);
4661
4662 BEGIN
4663
4664 l_sql_string := 'Procedure no longer used' ;
4665
4666 END fast_expired_lines ;
4667
4668 ---------------------------------------------------------------------
4669 --
4670 -- load the contract salesreps table
4671 --
4672 ---------------------------------------------------------------------
4673 -- 11510 Changes to refresh/fast_k_salesrep
4674 -- 1. Table oki_role_maps is obsoleted, so the procedure hard codes the
4675 -- value for contact role code depending upon the contract subclass.
4676 -- 2. Column salesrep_name is not populated in the table
4677 -- oki_k_salesreps. Therefore, the logic to populate this column is
4678 -- removed from the procedure.
4679 procedure refresh_k_salesreps(errbuf OUT NOCOPY VARCHAR2
4680 ,retcode OUT NOCOPY VARCHAR2) IS
4681
4682 -- Exception to immediately exit the procedure
4683 l_excp_no_job_run_id EXCEPTION ;
4684 l_no_update_refresh EXCEPTION;
4685 l_index_tab vc_tab_type;
4686 l_sql_string VARCHAR2(4000);
4687 l_sqlcode VARCHAR2(100);
4688 l_sqlerrm VARCHAR2(1000);
4689 l_errpos NUMBER := 0;
4690
4691 -- variables to set up the fast refresh job
4692 l_job_run_id NUMBER ;
4693
4694 l_salesperson_code okc_contacts.cro_code%TYPE; -- 11510 Changes
4695
4696 BEGIN
4697 retcode := '0';
4698
4699 -- get representative index storage parms for later use
4700 OPEN index_info_csr('OKI_K_SALESREPS','OKI_K_SALESREPS_U1','OKI');
4701 FETCH index_info_csr INTO l_ind_rec;
4702 CLOSE index_info_csr;
4703 l_errpos := 1;
4704
4705 -- drop indexes
4706 OPEN index_name_csr('OKI_K_SALESREPS');
4707 FETCH index_name_csr BULK COLLECT INTO l_index_tab;
4708 CLOSE index_name_csr;
4709 l_errpos := 2;
4710
4711 /* 11510 Changes Start */
4712 l_salesperson_code := fnd_profile.value('OKS_ENABLE_SALES_CREDIT');
4713 IF l_salesperson_code IN ('YES') THEN
4714 l_salesperson_code := fnd_profile.value('OKS_VENDOR_CONTACT_ROLE');
4715 ELSE
4716 l_salesperson_code := 'SALESPERSON';
4717 END IF;
4718 /* 11510 Changes End */
4719
4720 IF l_index_tab.first IS NOT NULL THEN
4721 FOR i IN l_index_tab.first..l_index_tab.last LOOP
4722 l_sql_string := 'DROP INDEX OKI.'||l_index_tab(i);
4723 EXECUTE IMMEDIATE l_sql_string;
4724 END LOOP;
4725 END IF;
4726 l_errpos := 3;
4727
4728 -- truncate table
4729 l_sql_string := 'TRUNCATE TABLE OKI.OKI_K_SALESREPS';
4730 EXECUTE IMMEDIATE l_sql_string;
4731 l_errpos := 4;
4732
4733 l_sql_string := 'ALTER SESSION ENABLE PARALLEL DML';
4734 EXECUTE IMMEDIATE l_sql_string;
4735 l_errpos := 5;
4736
4737 l_sql_string := 'alter session set hash_area_size=100000000';
4738 EXECUTE IMMEDIATE l_sql_string;
4739 l_sql_string := 'alter session set sort_area_size=100000000';
4740 EXECUTE IMMEDIATE l_sql_string;
4741
4742 -- insert data
4743 INSERT /*+ append */ INTO OKI_K_SALESREPS
4744 (
4745 PARTY_CONTACT_ID,
4746 CONTRACT_ID,
4747 PARTY_ROLE_ID,
4748 CONTACT_ROLE_CODE,
4749 CONTACT_ID,
4750 SALESREP_NAME ,
4751 CREATION_DATE,
4752 LAST_UPDATE_DATE,
4753 ATTRIBUTE_CATEGORY,
4754 ATTRIBUTE1,
4755 ATTRIBUTE2,
4756 ATTRIBUTE3,
4757 ATTRIBUTE4,
4758 ATTRIBUTE5,
4759 ATTRIBUTE6,
4760 ATTRIBUTE7,
4761 ATTRIBUTE8,
4762 ATTRIBUTE9,
4763 ATTRIBUTE10,
4764 ATTRIBUTE11,
4765 ATTRIBUTE12,
4766 ATTRIBUTE13,
4767 ATTRIBUTE14,
4768 ATTRIBUTE15
4769 )
4770 SELECT /*+ use_hash(shd) use_hash(ctt) use_hash(srp) use_hash(jrs) */
4771 ctt.id
4772 ,ctt.dnz_chr_id
4773 ,ctt.cpl_id
4774 ,ctt.cro_code
4775 ,ctt.object1_id1
4776 ,NULL salesrep_name -- 11510 Changes
4777 ,ctt.creation_date
4778 ,ctt.last_update_date
4779 ,ctt.attribute_category
4780 ,ctt.attribute1
4781 ,ctt.attribute2
4782 ,ctt.attribute3
4783 ,ctt.attribute4
4784 ,ctt.attribute5
4785 ,ctt.attribute6
4786 ,ctt.attribute7
4787 ,ctt.attribute8
4788 ,ctt.attribute9
4789 ,ctt.attribute10
4790 ,ctt.attribute11
4791 ,ctt.attribute12
4792 ,ctt.attribute13
4793 ,ctt.attribute14
4794 ,ctt.attribute15
4795 FROM
4796 oki_sales_k_hdrs shd
4797 ,okc_contacts ctt
4798 WHERE 1=1
4799 AND ctt.cro_code = l_salesperson_code -- 11510 Changes
4800 AND ctt.dnz_chr_id = shd.chr_id
4801 ;
4802 COMMIT;
4803 l_errpos := 6;
4804
4805 -- recreate indexes
4806 create_indicies(p_object_name => 'OKI_K_SALESREPS'
4807 ,p_parm_rec => l_ind_rec);
4808 l_errpos := 7;
4809
4810 OPEN g_latest_job_run_id_csr ;
4811 FETCH g_latest_job_run_id_csr INTO rec_g_latest_job_run_id_csr ;
4812 IF g_latest_job_run_id_csr%NOTFOUND THEN
4813 RAISE l_excp_no_job_run_id ;
4814 END IF ;
4815 l_job_run_id := rec_g_latest_job_run_id_csr.job_run_id ;
4816 CLOSE g_latest_job_run_id_csr ;
4817
4818 -- update oki_refreshes
4819 update_oki_refresh( p_object_name => 'OKI_K_SALESREPS'
4820 , p_job_run_id => l_job_run_id
4821 , x_retcode => retcode ) ;
4822 if retcode = 2 THEN
4823 raise l_no_update_refresh;
4824 end if;
4825
4826 l_errpos := 9;
4827
4828 -- analyze table
4829 fnd_stats.gather_table_stats(ownname => 'OKI'
4830 ,tabname => 'OKI_K_SALESREPS',percent=> 10);
4831
4832 fnd_message.set_name(application => 'OKI'
4833 ,name => 'OKI_TABLE_LOAD_SUCCESS');
4834 fnd_message.set_token(token => 'TABLE_NAME'
4835 ,value => 'OKI_K_SALESREPS');
4836 fnd_file.put_line(which => fnd_file.log
4837 ,buff => fnd_message.get);
4838
4839 EXCEPTION
4840 WHEN l_excp_no_job_run_id THEN
4841 ROLLBACK;
4842 retcode := '2';
4843
4844 fnd_message.set_name( application => 'OKI'
4845 , name => 'OKI_TABLE_LOAD_FAILURE');
4846 fnd_message.set_token( token => 'TABLE_NAME'
4847 , value => 'OKI_K_SALESREPS');
4848 fnd_file.put_line( which => fnd_file.log
4849 , buff => fnd_message.get);
4850
4851 fnd_message.set_name( application => 'OKI'
4852 , name => 'OKI_NO_JOB_RUN_ID_FAILURE');
4853 fnd_file.put_line( which => fnd_file.log
4854 , buff => fnd_message.get);
4855
4856 when l_no_update_refresh then
4857 fnd_message.set_name(application => 'OKI'
4858 ,name => 'OKI_TABLE_LOAD_FAILURE');
4859 fnd_message.set_token(token => 'TABLE_NAME'
4860 ,value => 'OKI_K_SALESREPS');
4861 fnd_file.put_line(which => fnd_file.log
4862 ,buff => fnd_message.get);
4863
4864 fnd_file.put_line(which => fnd_file.log
4865 ,buff => 'Update of OKI_REFRESHS failed');
4866 WHEN OTHERS THEN
4867 l_sqlcode := sqlcode;
4868 l_sqlerrm := sqlerrm;
4869 ROLLBACK;
4870 retcode := '2';
4871 fnd_message.set_name( application => 'OKI'
4872 , name => 'OKI_TABLE_LOAD_FAILURE');
4873 fnd_message.set_token( token => 'TABLE_NAME'
4874 , value => 'OKI_K_SALESREPS');
4875 fnd_file.put_line( which => fnd_file.log
4876 , buff => fnd_message.get);
4877 fnd_file.put_line( which => fnd_file.log
4878 , buff => l_sqlcode||' '||l_sqlerrm);
4879 IF l_errpos < 6 THEN
4880 create_indicies( p_object_name => 'OKI_K_SALESREPS'
4881 , p_parm_rec => l_ind_rec);
4882 ELSIF l_errpos = 6 THEN
4883 -- truncate table
4884 l_sql_string := 'TRUNCATE TABLE OKI.OKI_K_SALESREPS';
4885 EXECUTE IMMEDIATE l_sql_string;
4886 END IF;
4887 END refresh_k_salesreps;
4888 ---------------------------------------------------------------------
4889 --
4890 -- Procedure to load data into oki_k_salesrep without a complete
4891 -- refresh
4892 --
4893 ---------------------------------------------------------------------
4894 procedure fast_k_salesreps
4895 ( x_errbuf OUT NOCOPY VARCHAR2
4896 , x_retcode OUT NOCOPY VARCHAR2
4897 ) IS
4898
4899 -- Exception to immediately exit the procedure
4900 l_excp_exit_immediate EXCEPTION ;
4901 -- Records have already been processed, just exit the program
4902 l_excp_no_processing EXCEPTION ;
4903 l_no_update_refresh EXCEPTION;
4904 l_sqlcode VARCHAR2(100);
4905 l_sqlerrm VARCHAR2(1000);
4906 l_errpos NUMBER := 0;
4907 -- Location within the program before the error was encountered.
4908 l_loc VARCHAR2(200) ;
4909 l_retcode VARCHAR2(1) ;
4910 l_process_yn VARCHAR2(1) ;
4911 l_job_run_id NUMBER ;
4912 l_job_curr_start_date DATE ;
4913 l_job_curr_end_date DATE ;
4914 l_table_name VARCHAR2(30) ;
4915
4916 l_salesperson_code okc_contacts.cro_code%TYPE; -- 11510 Changes
4917
4918 BEGIN
4919 l_retcode := '0';
4920 l_table_name := 'OKI_K_SALESREPS';
4921
4922 l_loc := 'Calling process_refresh_check.' ;
4923 process_refresh_check(
4924 p_object_name => l_table_name
4925 , x_job_run_id => l_job_run_id
4926 , x_process_yn => l_process_yn
4927 , x_errbuf => x_errbuf
4928 , x_retcode => l_retcode ) ;
4929 l_loc := 'Determining status of process_refresh_check.' ;
4930 IF l_retcode = '2' THEN
4931 -- No job_run_id, exit immediately.
4932 RAISE l_excp_exit_immediate ;
4933 END IF ;
4934
4935 l_loc := 'Checking if records have already been processed.' ;
4936 IF l_process_yn = 'N' THEN
4937 RAISE l_excp_no_processing ;
4938 END IF ;
4939
4940 /* 11510 Changes Start */
4941 l_salesperson_code := fnd_profile.value('OKS_ENABLE_SALES_CREDIT');
4942 IF l_salesperson_code IN ('YES') THEN
4943 l_salesperson_code := fnd_profile.value('OKS_VENDOR_CONTACT_ROLE');
4944 ELSE
4945 l_salesperson_code := 'SALESPERSON';
4946 END IF;
4947 /* 11510 Changes End */
4948
4949 l_loc := 'Calling get_load_date_range.' ;
4950 get_load_date_range(
4951 p_job_run_id => l_job_run_id
4952 , p_job_curr_start_date => l_job_curr_start_date
4953 , p_job_curr_end_date => l_job_curr_end_date
4954 , x_errbuf => x_errbuf
4955 , x_retcode => l_retcode ) ;
4956 l_loc := 'Determining status of get_load_date_range' ;
4957 IF l_retcode = '2' THEN
4958 -- No job_curr_date range values, exit immediately.
4959 RAISE l_excp_exit_immediate ;
4960 END IF ;
4961
4962 l_loc := 'Deleting from ' || l_table_name ;
4963 DELETE FROM oki_k_salesreps ksr
4964 WHERE contract_id in ( SELECT jrd.chr_id
4965 FROM oki_job_run_dtl jrd
4966 WHERE jrd.job_run_id = l_job_run_id ) ;
4967
4968 l_loc := 'Inserting into ' || l_table_name ;
4969 -- insert data
4970 INSERT INTO OKI_K_SALESREPS(
4971 PARTY_CONTACT_ID
4972 , CONTRACT_ID
4973 , PARTY_ROLE_ID
4974 , CONTACT_ROLE_CODE
4975 , CONTACT_ID
4976 , SALESREP_NAME
4977 , CREATION_DATE
4978 , LAST_UPDATE_DATE
4979 , ATTRIBUTE_CATEGORY
4980 , ATTRIBUTE1
4981 , ATTRIBUTE2
4982 , ATTRIBUTE3
4983 , ATTRIBUTE4
4984 , ATTRIBUTE5
4985 , ATTRIBUTE6
4986 , ATTRIBUTE7
4987 , ATTRIBUTE8
4988 , ATTRIBUTE9
4989 , ATTRIBUTE10
4990 , ATTRIBUTE11
4991 , ATTRIBUTE12
4992 , ATTRIBUTE13
4993 , ATTRIBUTE14
4994 , ATTRIBUTE15
4995 ) SELECT
4996 ctt.id
4997 , ctt.dnz_chr_id
4998 , ctt.cpl_id
4999 , ctt.cro_code
5000 , ctt.object1_id1
5001 , NULL salesrep_name -- 11510 Changes
5002 , ctt.creation_date
5003 , ctt.last_update_date
5004 , ctt.attribute_category
5005 , ctt.attribute1
5006 , ctt.attribute2
5007 , ctt.attribute3
5008 , ctt.attribute4
5009 , ctt.attribute5
5010 , ctt.attribute6
5011 , ctt.attribute7
5012 , ctt.attribute8
5013 , ctt.attribute9
5014 , ctt.attribute10
5015 , ctt.attribute11
5016 , ctt.attribute12
5017 , ctt.attribute13
5018 , ctt.attribute14
5019 , ctt.attribute15
5020 FROM
5021 oki_sales_k_hdrs shd
5022 , okc_contacts ctt
5023 , oki_job_run_dtl jrd
5024 WHERE 1=1
5025 AND ctt.cro_code = l_salesperson_code -- 11510 Changes
5026 AND ctt.dnz_chr_id = shd.chr_id
5027 and jrd.chr_id = shd.chr_id
5028 and jrd.job_run_id = l_job_run_id
5029 and jrd.action_flag = 'I' ;
5030
5031 l_loc := 'Updating OKI_REFRESHS.' ;
5032 -- update oki_refreshes
5033 update_oki_refresh( p_object_name => l_table_name
5034 , p_job_run_id => l_job_run_id
5035 , x_retcode => l_retcode ) ;
5036 COMMIT;
5037 if l_retcode = 2 then
5038 raise l_no_update_refresh;
5039 end if;
5040
5041 l_loc := 'Analyze ' || l_table_name ;
5042 -- analyze table
5043 fnd_stats.gather_table_stats( ownname => 'OKI'
5044 , tabname => l_table_name
5045 , percent => 10 ) ;
5046
5047 fnd_message.set_name( application => 'OKI'
5048 , name => 'OKI_TABLE_LOAD_SUCCESS' ) ;
5049 fnd_message.set_token( token => 'TABLE_NAME'
5050 , value => l_table_name ) ;
5051 fnd_file.put_line( which => fnd_file.log
5052 , buff => fnd_message.get ) ;
5053
5054 EXCEPTION
5055 WHEN l_excp_no_processing then
5056 -- Do not log an error ; It has already been logged.
5057 -- Just exit the program and continue with the other table load
5058 null ;
5059
5060 when l_no_update_refresh then
5061 fnd_message.set_name(application => 'OKI'
5062 ,name => 'OKI_TABLE_LOAD_FAILURE');
5063 fnd_message.set_token(token => 'TABLE_NAME'
5064 ,value => l_table_name);
5065 fnd_file.put_line(which => fnd_file.log
5066 ,buff => fnd_message.get);
5067
5068 fnd_file.put_line(which => fnd_file.log
5069 ,buff => 'Update of OKI_REFRESHS failed');
5070 WHEN l_excp_exit_immediate THEN
5071 -- Do not log an error ; It has already been logged.
5072 -- Set return code to error
5073 x_retcode := '2' ;
5074
5075 WHEN OTHERS THEN
5076 l_sqlcode := sqlcode;
5077 l_sqlerrm := sqlerrm;
5078 ROLLBACK;
5079 x_retcode := '2';
5080 fnd_message.set_name( application => 'OKI'
5081 , name => 'OKI_TABLE_LOAD_FAILURE' ) ;
5082 fnd_message.set_token( token => 'TABLE_NAME'
5083 , value => l_table_name ) ;
5084 fnd_file.put_line( which => fnd_file.log
5085 , buff => fnd_message.get ) ;
5086 fnd_file.put_line( which => fnd_file.log
5087 , buff => l_sqlcode || ' ' || l_sqlerrm ) ;
5088 END fast_k_salesreps ;
5089 /*********11510 changes stub out the procedure *********/
5090 --------------------------------------------------------------
5091 --- Refresh the conversion rates which are locked for contracts
5092 ---------------------------------------------------------------
5093
5094 -- 11510 Change: Stub out the procedure
5095
5096 PROCEDURE refresh_k_conv_rates(errbuf OUT NOCOPY VARCHAR2
5097 ,retcode OUT NOCOPY VARCHAR2) IS
5098 l_sql_string VARCHAR2(2000);
5099
5100 BEGIN
5101
5102 l_sql_string := 'Procedure no longer used' ;
5103
5104 END refresh_k_conv_rates;
5105
5106 ---------------------------------------------------------------------
5107 --
5108 -- Procedure to load data into oki_pricing_rules.
5109 --
5110 ---------------------------------------------------------------------
5111
5112 -- 11510 Change: Stub out the procedure
5113
5114 PROCEDURE refresh_k_pricing_rules( x_errbuf OUT NOCOPY VARCHAR2
5115 , x_retcode OUT NOCOPY VARCHAR2 ) IS
5116
5117 l_sql_string VARCHAR2(2000);
5118
5119 BEGIN
5120
5121 l_sql_string := 'Procedure no longer used' ;
5122
5123 END refresh_k_pricing_rules ;
5124
5125 ---------------------------------------------------------------------
5126 --
5127 -- Procedure to load change data into oki_pricing_rules and OKI QTO.
5128 --
5129 ---------------------------------------------------------------------
5130
5131 -- 11510 Change: Stub out the procedure
5132
5133 PROCEDURE fast_k_pricing_rules( x_errbuf OUT NOCOPY VARCHAR2
5134 , x_retcode OUT NOCOPY VARCHAR2 ) IS
5135 l_sql_string VARCHAR2(2000);
5136
5137 BEGIN
5138
5139 l_sql_string := 'Procedure no longer used' ;
5140
5141 END fast_k_pricing_rules ;
5142
5143
5144 PROCEDURE update_service_line( x_errbuf OUT NOCOPY VARCHAR2
5145 , x_retcode OUT NOCOPY VARCHAR2 ) IS
5146
5147 -- Exception to immediately exit the procedure
5148 l_excp_no_job_run_id EXCEPTION ;
5149 l_no_update_refresh EXCEPTION;
5150 l_message VARCHAR2(2000);
5151 l_sqlcode VARCHAR2(100);
5152 l_sqlerrm VARCHAR2(1000);
5153 l_errpos NUMBER := 0 ;
5154
5155 -- variables to set up the fast refresh job
5156 l_job_run_id NUMBER ;
5157
5158 -- Location within the program before the error was encountered.
5159 l_loc VARCHAR2(200) ;
5160
5161 BEGIN
5162 x_retcode := 0 ;
5163
5164 l_loc := ' Updating price negotiated for Service Contracts ';
5165
5166 UPDATE /*+ parallel(v) bypass_ujvc */ oki_sold_itm_lines_cpl_v v
5167 SET price_negotiated = cpl_price_negotiated
5168 , sob_price_negotiated = cpl_sob_price_negotiated
5169 , base_price_negotiated = cpl_base_price_negotiated ;
5170
5171 COMMIT;
5172 l_loc := ' Succesfully Updated price negotiated for Service Contracts ';
5173
5174 OPEN g_latest_job_run_id_csr ;
5175 FETCH g_latest_job_run_id_csr INTO rec_g_latest_job_run_id_csr ;
5176 IF g_latest_job_run_id_csr%NOTFOUND THEN
5177 RAISE l_excp_no_job_run_id ;
5178 END IF ;
5179 l_job_run_id := rec_g_latest_job_run_id_csr.job_run_id ;
5180 CLOSE g_latest_job_run_id_csr ;
5181
5182 -- update oki_refreshes
5183 update_oki_refresh( p_object_name => 'OKI_SOLD_ITM_LINES_UPDATE'
5184 , p_job_run_id => l_job_run_id
5185 , x_retcode => x_retcode ) ;
5186 COMMIT ;
5187 if x_retcode = 2 then
5188 raise l_no_update_refresh;
5189 end if;
5190
5191 fnd_message.set_name(application => 'OKI'
5192 ,name => 'OKI_TABLE_LOAD_SUCCESS');
5193 fnd_message.set_token(token => 'TABLE_NAME'
5194 ,value => 'OKI_SOLD_ITM_LINES');
5195 fnd_file.put_line(which => fnd_file.log
5196 ,buff => fnd_message.get);
5197
5198 EXCEPTION
5199 WHEN l_excp_no_job_run_id THEN
5200 ROLLBACK;
5201 x_retcode := '2';
5202 fnd_message.set_name( application => 'OKI'
5203 , name => 'OKI_TABLE_LOAD_FAILURE');
5204 fnd_message.set_token( token => 'TABLE_NAME'
5205 , value => 'OKI_SOLD_ITM_LINES');
5206 fnd_file.put_line( which => fnd_file.log
5207 , buff => fnd_message.get);
5208
5209 fnd_message.set_name( application => 'OKI'
5210 , name => 'OKI_NO_JOB_RUN_ID_FAILURE');
5211 fnd_file.put_line( which => fnd_file.log
5212 , buff => fnd_message.get);
5213
5214 when l_no_update_refresh then
5215 fnd_message.set_name(application => 'OKI'
5216 ,name => 'OKI_TABLE_LOAD_FAILURE');
5217 fnd_message.set_token(token => 'TABLE_NAME'
5218 ,value => 'OKI_SOLD_ITM_LINES');
5219 fnd_file.put_line(which => fnd_file.log
5220 ,buff => fnd_message.get);
5221
5222 fnd_file.put_line(which => fnd_file.log
5223 ,buff => 'Update of OKI_REFRESHS failed');
5224 WHEN OTHERS THEN
5225 l_sqlcode := sqlcode;
5226 l_sqlerrm := sqlerrm;
5227 fnd_file.put_line(which => fnd_file.log
5228 ,buff => l_message);
5229 ROLLBACK;
5230 x_retcode := '2';
5231 fnd_message.set_name( application => 'OKI'
5232 , name => 'OKI_TABLE_LOAD_FAILURE');
5233 fnd_message.set_token( token => 'TABLE_NAME'
5234 , value => 'OKI_SOLD_ITM_LINES');
5235 fnd_file.put_line( which => fnd_file.log
5236 , buff => fnd_message.get);
5237
5238 -- Log the location within the procedure where the error occurred
5239 fnd_message.set_name( application => 'OKI'
5240 , name => 'OKI_LOC_IN_PROG_FAILURE');
5241
5242 fnd_message.set_token( token => 'LOCATION'
5243 , value => l_loc);
5244
5245 fnd_file.put_line( which => fnd_file.log
5246 , buff => fnd_message.get);
5247
5248 fnd_file.put_line( which => fnd_file.log
5249 , buff => l_sqlcode || ' ' || l_sqlerrm);
5250
5251 END update_service_line;
5252
5253
5254 PROCEDURE fast_update_service_line( x_errbuf OUT NOCOPY VARCHAR2
5255 , x_retcode OUT NOCOPY VARCHAR2 ) IS
5256
5257 -- Exception to immediately exit the procedure
5258 l_excp_exit_immediate EXCEPTION ;
5259 -- Records have already been processed, just exit the program
5260 l_excp_no_processing EXCEPTION ;
5261 l_no_update_refresh EXCEPTION;
5262 l_retcode VARCHAR2(1) ;
5263 l_process_yn VARCHAR2(1) ;
5264 l_message VARCHAR2(2000);
5265 l_sqlcode VARCHAR2(100);
5266 l_sqlerrm VARCHAR2(1000);
5267 l_errpos NUMBER := 0 ;
5268
5269 -- Location within the program before the error was encountered.
5270 l_loc VARCHAR2(200) ;
5271
5272 l_job_run_id NUMBER ;
5273 l_table_name VARCHAR2(30) ;
5274
5275
5276 BEGIN
5277 x_retcode := 0 ;
5278 l_table_name := 'OKI_SOLD_ITM_LINES_UPDATE';
5279
5280 l_loc := 'Calling process_refresh_check.' ;
5281 process_refresh_check(
5282 p_object_name => l_table_name
5283 , x_job_run_id => l_job_run_id
5284 , x_process_yn => l_process_yn
5285 , x_errbuf => x_errbuf
5286 , x_retcode => l_retcode ) ;
5287 l_loc := 'Determining status of process_refresh_check.' ;
5288 IF l_retcode = '2' THEN
5289 -- No job_run_id, exit immediately.
5290 RAISE l_excp_exit_immediate ;
5291 END IF ;
5292
5293 l_loc := 'Checking if records have already been processed.' ;
5294 IF l_process_yn = 'N' THEN
5295 -- Object has been refreshed successfully.
5296 RAISE l_excp_no_processing ;
5297 END IF ;
5298
5299 l_loc := ' Updating price negotiated for Service Contracts ';
5300
5301 UPDATE OKI_SOLD_ITM_LINES sil
5302 SET (price_negotiated,
5303 sob_price_negotiated,
5304 base_price_negotiated)
5305 = (SELECT
5306 SUM(price_negotiated),
5307 SUM(sob_price_negotiated),
5308 SUM(base_price_negotiated)
5309 FROM oki_cov_prd_lines cpl
5310 where cpl.parent_cle_id = sil.cle_id
5311 )
5312 where sil.chr_id in (select shd.chr_id
5313 from oki_job_run_dtl jrd, oki_sales_k_hdrs shd
5314 where 1 =1
5315 and jrd.action_flag = 'I'
5316 and jrd.job_run_id = l_job_run_id
5317 and jrd.chr_id = shd.chr_id
5318 and shd.application_id = 515);
5319
5320 l_loc := 'Updating OKI_REFRESHS.' ;
5321 -- update oki_refreshes
5322 update_oki_refresh(p_object_name => l_table_name
5323 , p_job_run_id => l_job_run_id
5324 , x_retcode => l_retcode ) ;
5325 COMMIT;
5326 if l_retcode = 2 then
5327 raise l_no_update_refresh;
5328 end if;
5329 l_loc := ' Succesfully Updated price negotiated for Service Contracts ';
5330
5331 fnd_message.set_name(application => 'OKI'
5332 ,name => 'OKI_TABLE_LOAD_SUCCESS');
5333 fnd_message.set_token(token => 'TABLE_NAME'
5334 ,value => 'OKI_SOLD_ITM_LINES');
5335 fnd_file.put_line(which => fnd_file.log
5336 ,buff => fnd_message.get);
5337
5338 EXCEPTION
5339 WHEN l_excp_no_processing then
5340 -- Do not log an error ; It has already been logged.
5341 -- Just exit the program and continue with the other table load
5342 null ;
5343 when l_no_update_refresh then
5344 fnd_message.set_name(application => 'OKI'
5345 ,name => 'OKI_TABLE_LOAD_FAILURE');
5346 fnd_message.set_token(token => 'TABLE_NAME'
5347 ,value => 'OKI_SOLD_ITM_LINES');
5348 fnd_file.put_line(which => fnd_file.log
5349 ,buff => fnd_message.get);
5350
5351 fnd_file.put_line(which => fnd_file.log
5352 ,buff => 'Update of OKI_REFRESHS failed');
5353 WHEN l_excp_exit_immediate THEN
5354 -- Do not log an error ; It has already been logged.
5355 -- Set return code to error
5356 x_retcode := '2' ;
5357
5358 WHEN OTHERS THEN
5359 l_sqlcode := sqlcode;
5360 l_sqlerrm := sqlerrm;
5361 fnd_file.put_line(which => fnd_file.log
5362 ,buff => l_message);
5363 ROLLBACK;
5364 x_retcode := '2';
5365 fnd_message.set_name( application => 'OKI'
5366 , name => 'OKI_TABLE_LOAD_FAILURE');
5367 fnd_message.set_token( token => 'TABLE_NAME'
5368 , value => 'OKI_SOLD_ITM_LINES');
5369 fnd_file.put_line( which => fnd_file.log
5370 , buff => fnd_message.get);
5371
5372 -- Log the location within the procedure where the error occurred
5373 fnd_message.set_name( application => 'OKI'
5374 , name => 'OKI_LOC_IN_PROG_FAILURE');
5375
5376 fnd_message.set_token( token => 'LOCATION'
5377 , value => l_loc);
5378
5379 fnd_file.put_line( which => fnd_file.log
5380 , buff => fnd_message.get);
5381
5382 fnd_file.put_line( which => fnd_file.log
5383 , buff => l_sqlcode || ' ' || l_sqlerrm);
5384 END fast_update_service_line;
5385
5386
5387
5388 ---------------------------------------------------------------------
5389 --
5390 -- GLOBAL VARIABLES INITIALIZATION
5391 --
5392 ---------------------------------------------------------------------
5393
5394 BEGIN
5395 g_request_id := fnd_global.CONC_REQUEST_ID;
5396 g_program_application_id := fnd_global.PROG_APPL_ID;
5397 g_program_id := fnd_global.CONC_PROGRAM_ID;
5398 g_program_update_date := SYSDATE;
5399 END OKI_REFRESH_PVT ;