[Home] [Help]
PACKAGE BODY: APPS.OKS_FULFILL
Source
1 PACKAGE BODY OKS_FULFILL AS
2 /* $Header: OKSFULFB.pls 120.4 2007/12/24 07:30:00 rriyer ship $*/
3
4 --
5 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
6 -- directory of SQL Navigator
7 --
8 -- Purpose: Briefly explain the functionality of the package body
9 --
10 -- MODIFICATION HISTORY
11 -- Person Date Comments
12 -- --------- ------ ------------------------------------------
13 -- Enter procedure, function bodies as shown below
14 -- OKS_FULFILL_MAIN
15
16 -- Global constant holding the package name
17 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKS_BILLING_PVT' ;
18
19 -- Global var holding the Current Error code for the error encountered
20 Current_Error_Code Varchar2(20) := NULL;
21
22 -- Global var holding the User Id
23 user_id NUMBER;
24
25 -- Global var to hold the ERROR value.
26 ERROR NUMBER := 0;
27
28 -- Global var to hold the SUCCESS value.
29 SUCCESS NUMBER := 1;
30
31 -- Global var to hold the commit size.
32 COMMIT_SIZE NUMBER := 10;
33
34 -- Global var to hold the Concurrent Process return value
35 conc_ret_code NUMBER := SUCCESS;
36
37 -- Global constant for the threshold count before splitting into sub-requests
38 MAX_SINGLE_REQUEST NUMBER := 500;
39
40 -- Global constant for the maximum allowed sub-requests (parallel workers)
41 MAX_JOBS NUMBER := 20;
42
43 -- Global vars to hold the min and max hdr_id for each sub-request range
44 -- Global vars to hold the min and max line_id for each sub-request range
45 -- Bug 4915691 --
46 type range_rec is record (
47 lo number,
48 hi number,
49 line_id_lo NUMBER,
50 line_id_hi NUMBER);
51
52 -- Bug 4915691 --
53 type rangeArray is VARRAY(50) of range_rec;
54 range_arr rangeArray;
55
56
57 procedure split_range (
58 p_lo number,
59 p_hi number,
60 P_default_date IN DATE,
61 p_org_id IN NUMBER,
62 P_Customer_id IN NUMBER,
63 P_Grp_Id IN NUMBER,
64 p_buckets number) is
65 l_lo number := p_lo;
66 l_idx1 number := range_arr.count + 1;
67 l_idx2 number := range_arr.count + p_buckets;
68 l_bucket_width integer;
69
70 -- Bug 4915691 --
71 -- Added logic to filter data based on Subscr elements
72 -- processed for a Subscription line
73 -- Added condition to fetch line id from OKC_K_LINES_B
74
75 CURSOR chr_csr IS
76 SELECT hdr.id id
77 , line.id line_id
78 FROM
79 OKC_K_GRPINGS okg
80 ,OKC_K_PARTY_ROLES_B okp
81 ,OKC_K_HEADERS_B Hdr
82 ,OKC_STATUSES_B st
83 ,OKC_K_LINES_B line
84 Where Hdr.scs_code = 'SUBSCRIPTION'
85 And Hdr.Template_yn = 'N'
86 And Hdr.sts_code = st.CODE
87 AND st.ste_code in ('ACTIVE','SIGNED','EXPIRED','TERMINATED')
88 AND hdr.sts_code <> 'QA_HOLD'
89 And Hdr.authoring_org_id = NVL(p_org_id, Hdr.authoring_org_id)
90 And okp.chr_id = hdr.id
91 And line.dnz_chr_id = Hdr.id
92 And line.lse_id = 46
93 And okp.rle_code = 'SUBSCRIBER'
94 And okp.object1_id1 = nvl(p_customer_id,okp.object1_id1)
95 And okg.included_chr_id = hdr.id
96 And okg.cgp_parent_id = nvl(p_grp_id,okg.cgp_parent_id)
97 And EXISTS (Select 1 from OKS_SUBSCR_ELEMENTS sub
98 Where sub.dnz_chr_id = hdr.id
99 And sub.dnz_cle_id = line.id
100 And sub.order_header_id is null)
101 /* Commented by sjanakir for Bug# 5568285 (FP Bug for 5442268) */
102 /* Order By hdr.id ; */
103 /* Added by sjanakir for Bug# 5568285 (FP Bug for 5442268) */
104 Order By line.id ;
105 /* Addition Ends */
106 CURSOR chr_csr_no_para IS
107 SELECT hdr.id id
108 , line.id line_id
109 FROM
110 OKC_K_HEADERS_B Hdr
111 ,OKC_STATUSES_B st
112 ,OKC_K_LINES_B line
113 Where Hdr.scs_code = 'SUBSCRIPTION'
114 And Hdr.Template_yn = 'N'
115 And Hdr.sts_code = st.CODE
116 AND st.ste_code in ('ACTIVE','SIGNED','EXPIRED','TERMINATED')
117 AND Hdr.sts_code <> 'QA_HOLD'
118 And line.dnz_chr_id = Hdr.id
119 And line.lse_id = 46
120 And EXISTS (Select 1 from OKS_SUBSCR_ELEMENTS sub
121 Where sub.dnz_chr_id = hdr.id
122 And sub.dnz_cle_id = line.id
123 And sub.order_header_id is null)
124 Order By hdr.id ;
125 -- Bug 4915691 --
126
127 TYPE t_k_number is TABLE of number index by binary_integer;
128 c_k_number t_k_number;
129 c_k_line t_k_number; -- Bug 4915691 --
130
131 c_k_index BINARY_INTEGER ;
132 v_total number;
133 x number;
134 begin
135 fnd_file.put_line(FND_FILE.LOG, 'INSIDE SPLIT RANGE');
136 fnd_file.put_line(FND_FILE.LOG, 'p_lo = '|| p_lo);
137 fnd_file.put_line(FND_FILE.LOG, 'p_hi = '|| p_hi);
138 fnd_file.put_line(FND_FILE.LOG, 'p_buckets = '|| p_buckets);
139 if p_buckets = 0 then
140 return;
141 end if;
142 c_k_number(1) := 0;
143 c_k_index:= 1;
144
145 IF p_org_id is null and p_customer_id is null and p_grp_id is null then
146 for c_chr_csr_no_para in chr_csr_no_para loop
147 c_k_number(c_k_index) := c_chr_csr_no_para.id;
148 c_k_line(c_k_index) := c_chr_csr_no_para.line_id; -- Bug 4915691 --
149 fnd_file.put_line(FND_FILE.LOG, 'c_k_number('||c_k_index||') = '|| c_k_number(c_k_index));
150 c_k_index := c_k_index + 1;
151 end loop;
152 ELSE
153 for c_chr_csr in chr_csr loop
154 c_k_number(c_k_index) := c_chr_csr.id;
155 c_k_line(c_k_index) := c_chr_csr.line_id; -- Bug 4915691 --
156 fnd_file.put_line(FND_FILE.LOG, 'c_k_number('||c_k_index||') = '|| c_k_number(c_k_index));
157 c_k_index := c_k_index + 1;
158 end loop;
159 END IF;
160
161 v_total := c_k_number.count;
162 fnd_file.put_line(FND_FILE.LOG, 'v_total = '|| v_total);
163 fnd_file.put_line(FND_FILE.LOG, 'range_arr.count = '|| range_arr.count);
164 if range_arr.count > 0 then
165 -- so we don't overlap high value of previous range
166 l_lo := p_lo + 1;
167 end if;
168 -- c_k_index:= c_k_number.first;
169 c_k_index:= 1;
170 if v_total < p_buckets then
171 l_bucket_width := 1;
172 l_idx2 := range_arr.count + v_total;
173 range_arr.extend(v_total);
174 Else
175 l_bucket_width := trunc(v_total / p_buckets);
176 fnd_file.put_line(FND_FILE.LOG, 'l_bucket_width = '|| l_bucket_width);
177 range_arr.extend(p_buckets);
178 End if;
179
180 fnd_file.put_line(FND_FILE.LOG, 'l_idx1 = '|| l_idx1);
181 fnd_file.put_line(FND_FILE.LOG, 'l_idx2 = '|| l_idx2);
182 for idx in l_idx1..l_idx2 loop
183
184 range_arr(idx).lo := c_k_number(c_k_index + ((idx - l_idx1) * l_bucket_width));
185 range_arr(idx).line_id_lo := c_k_line(c_k_index + ((idx - l_idx1) * l_bucket_width)); -- Bug 4915691 --
186
187 x := c_k_index + ((idx - l_idx1) * l_bucket_width);
188 fnd_file.put_line(FND_FILE.LOG, '1...X = ' ||x);
189 if idx < l_idx2 then
190 range_arr(idx).hi := c_k_number(c_k_index+((idx - l_idx1+1) * l_bucket_width)-1);
191 range_arr(idx).line_id_hi := c_k_line(c_k_index+((idx - l_idx1+1) * l_bucket_width)-1); -- Bug 4915691 --
192 x := c_k_index+((idx - l_idx1+1) * l_bucket_width)-1;
193 fnd_file.put_line(FND_FILE.LOG, '2...X = ' ||x);
194 else
195 range_arr(idx).hi := p_hi;
196 range_arr(idx).line_id_hi := c_k_line(v_total);
197 end if;
198 fnd_file.put_line(FND_FILE.LOG, 'range_arr('||idx||').lo = '|| range_arr(idx).lo);
199 fnd_file.put_line(FND_FILE.LOG, 'range_arr('||idx||').hi = '|| range_arr(idx).hi);
200
201 end loop;
202 fnd_file.put_line(FND_FILE.LOG, 'DONE SPLIT RANGE');
203 end split_range;
204
205
206
207
208 function generate_ranges (
209 p_lo number,
210 p_hi number,
211 P_default_date IN DATE,
212 p_org_id IN NUMBER,
213 P_Customer_id IN NUMBER,
214 P_Grp_Id IN NUMBER,
215 p_avg number,
216 p_stddev number,
217 p_total number) return integer is
218 l_total_buckets integer := 0;
219
220 l_stdlo number := greatest(round(p_avg - p_stddev), p_lo);
221 l_stdhi number := least(round(p_avg + p_stddev), p_hi);
222 l_stddev_percent number := 0.66; -- the area covered by +/-1 stddev
223 l_outlier_buckets integer := 0;
224 l_std_buckets integer := 0;
225 l_lo_buckets integer := 0;
226 l_hi_buckets integer := 0;
227 l_outlier_entries_per_bucket number := 0;
228 modidx integer;
229
230 begin
231 fnd_file.put_line(FND_FILE.LOG, 'BEGIN GENERATE RANGE');
232 range_arr := rangeArray();
233 -- number of buckets is set to 20
234
235 fnd_file.put_line(FND_FILE.LOG, 'p_total = ' || p_total);
236 fnd_file.put_line(FND_FILE.LOG, 'p_lo = ' || p_lo);
237 fnd_file.put_line(FND_FILE.LOG, 'p_hi = ' || p_hi);
238 fnd_file.put_line(FND_FILE.LOG, 'l_stdlo = ' || l_stdlo);
239 fnd_file.put_line(FND_FILE.LOG, 'l_stdhi = ' || l_stdhi);
240
241 IF p_total <= 20 THEN
242 l_total_buckets := p_total;
243 ELSE
244 l_total_buckets := MAX_JOBS;
245 END IF;
246
247 fnd_file.put_line(FND_FILE.LOG, 'l_total_buckets = ' || l_total_buckets);
248
249 IF p_total <= 20 THEN
250 -- split_range(p_lo, p_hi, l_total_buckets);
251 split_range(p_lo, p_hi, P_default_date,
252 p_org_id ,
253 P_Customer_id,
254 P_Grp_Id,l_total_buckets);
255 ELSE
256 /*-- ranges for negative outliers
257 split_range(p_lo, l_stdlo, 2);
258 -- ranges for +/-1 stddev from mean
259 split_range(l_stdlo, l_stdhi, 16);
260 -- ranges for positive outliers
261 split_range(l_stdhi, p_hi, 2);
262 */
263 --split_range(p_lo, p_hi, l_total_buckets);
264 split_range(p_lo,
265 p_hi,
266 p_default_date,
267 p_org_id ,
268 p_Customer_id,
269 p_Grp_Id,
270 l_total_buckets);
271 END IF;
272 fnd_file.put_line(FND_FILE.LOG, 'END GENERATE RANGE');
273 return l_total_buckets;
274
275 /*
276 l_outlier_buckets := l_total_buckets * (1 - l_stddev_percent);
277 fnd_file.put_line(FND_FILE.LOG, 'l_outlier_buckets = ' || l_outlier_buckets);
278 if l_outlier_buckets > 0 then
279 l_outlier_entries_per_bucket := p_total * (1 - l_stddev_percent)
280 / l_outlier_buckets ;
281 fnd_file.put_line(FND_FILE.LOG, 'l_outlier_entries_per_bucket = ' || l_outlier_entries_per_bucket);
282 end if;
283 for idx in 1..l_outlier_buckets loop
284 modidx := mod(idx,2);
285 -- alternate assignment between hi and lo buckets
286 fnd_file.put_line(FND_FILE.LOG, 'modidx = ' || modidx);
287 if modidx = 1
288 AND (p_hi - (l_hi_buckets+1) * l_outlier_entries_per_bucket)
289 > l_stdhi then
290 -- allocate buckets for positive outliers
291 l_hi_buckets := l_hi_buckets + 1;
292 fnd_file.put_line(FND_FILE.LOG, '---- l_hi_buckets = ' || l_hi_buckets);
293 elsif modidx = 0
294 AND (p_lo + (l_lo_buckets+1) * l_outlier_entries_per_bucket)
295 < l_stdlo then
296 -- allocate buckets for negative outliers
297 l_lo_buckets := l_lo_buckets + 1;
298 fnd_file.put_line(FND_FILE.LOG, '---- l_lo_buckets = ' || l_lo_buckets);
299 -- else min or max has been consumed, save bucket for middle
300 end if;
301 end loop;
302
303 -- compute middle buckets
304 l_std_buckets := l_total_buckets - l_lo_buckets - l_hi_buckets;
305 -- in case low-high allocations yielded zero buckets.
306 -- i.e., outliers were folded into middle buckets.
307
308 if l_lo_buckets = 0 then
309 l_stdlo := p_lo;
310 end if;
311 if l_hi_buckets = 0 then
312 l_stdhi := p_hi;
313 end if;
314 */
315 /*
316 fnd_file.put_line(FND_FILE.LOG, 'p_lo = ' || p_lo);
317 fnd_file.put_line(FND_FILE.LOG, 'p_hi = ' || p_hi);
318 fnd_file.put_line(FND_FILE.LOG, 'l_stdlo = ' || l_stdlo);
319 fnd_file.put_line(FND_FILE.LOG, 'l_stdhi = ' || l_stdhi);
320 --fnd_file.put_line(FND_FILE.LOG, 'l_lo_buckets = ' || l_lo_buckets);
321 --fnd_file.put_line(FND_FILE.LOG, 'l_hi_buckets = ' || l_hi_buckets);
322 --fnd_file.put_line(FND_FILE.LOG, 'l_std_buckets = ' || l_std_buckets);
323 -- ranges for negative outliers
324 split_range(p_lo, l_stdlo, 2);
325 -- ranges for +/-1 stddev from mean
326 split_range(l_stdlo, l_stdhi, 16);
327 -- ranges for positive outliers
328 split_range(l_stdhi, p_hi, 2);
329 fnd_file.put_line(FND_FILE.LOG, 'l_total_buckets = ' || l_total_buckets);
330 return l_total_buckets;
331 */
332
333 end generate_ranges;
334
335
336 PROCEDURE Submit
337 (
338 ERRBUF OUT NOCOPY VARCHAR2,
339 RETCODE OUT NOCOPY NUMBER,
340 p_contract_hdr_id IN NUMBER,
341 P_default_date IN VARCHAR2,
342 p_org_id IN NUMBER,
343 P_Customer_id IN NUMBER,
344 P_Grp_Id IN NUMBER
345 ) is
346 -- Bug 4915691 --
347 -- Added logic to filter data based on Subscr elements
348 -- processed for a Subscription line
349 -- Subscription lines which have already been processed should not be
350 -- selected
351 Cursor l_fulfill_agg_csr IS
352 Select min(hdr.id) minid,
353 max(hdr.id) maxid,
354 avg(hdr.id) avgid,
355 stddev(hdr.id) stdid,
356 count(*) total
357 From
358 OKC_K_GRPINGS okg
359 ,OKC_K_PARTY_ROLES_B okp
360 ,OKC_K_HEADERS_B Hdr
361 ,OKC_STATUSES_B ST
362 Where Hdr.scs_code = 'SUBSCRIPTION'
363 And Hdr.Template_yn = 'N'
364 And Hdr.sts_code = st.CODE
365 AND st.ste_code in ('ACTIVE','SIGNED','EXPIRED','TERMINATED')
366 AND hdr.sts_code <> 'QA_HOLD'
367 And Hdr.authoring_org_id = NVL(p_org_id, Hdr.authoring_org_id)
368 And okp.chr_id = hdr.id
369 And okp.rle_code = 'SUBSCRIBER'
370 And okp.object1_id1 = nvl(p_customer_id,okp.object1_id1)
371 And okg.included_chr_id = hdr.id
372 And okg.cgp_parent_id = nvl(p_grp_id,okg.cgp_parent_id)
373 And EXISTS (Select 1 from OKS_SUBSCR_ELEMENTS sub
374 Where sub.dnz_chr_id = hdr.id
375 And sub.order_header_id is null);
376
377 Cursor l_fulfill_agg_csr_no_para IS
378 Select min(hdr.id) minid,
379 max(hdr.id) maxid,
380 avg(hdr.id) avgid,
381 stddev(hdr.id) stdid,
382 count(*) total
383 From
384 OKC_K_HEADERS_B Hdr
385 ,OKC_STATUSES_B ST
386 Where Hdr.scs_code = 'SUBSCRIPTION'
387 And Hdr.Template_yn = 'N'
388 And Hdr.sts_code = st.CODE
389 AND st.ste_code in ('ACTIVE','SIGNED','EXPIRED','TERMINATED')
390 AND hdr.sts_code <> 'QA_HOLD'
391 And EXISTS (Select 1 from OKS_SUBSCR_ELEMENTS sub
392 Where sub.dnz_chr_id = hdr.id
393 And sub.order_header_id is null);
394 -- Bug 4915691 --
395
396
397 l_agg_rec l_fulfill_agg_csr%ROWTYPE;
398 CONC_STATUS BOOLEAN;
399 l_retcode NUMBER;
400 l_msg_count NUMBER;
401 l_msg_data VARCHAR2(2000);
402 l_ret INTEGER;
403 l_subrequests INTEGER;
404 l_errbuf VARCHAR2(240);
405 l_return_status VARCHAR2(1);
406 use_parallel_worker VARCHAR2(1);
407 v_index BINARY_INTEGER;
408 l_default_date DATE;
409
410 BEGIN
411 l_default_date := to_date(P_default_date,'YYYY/MM/DD HH24:MI:SS');
412
413 null;
414 IF p_contract_hdr_id is not null then
415 use_parallel_worker := 'N';
416 ELSE
417 IF p_org_id is null and p_customer_id is null and p_grp_id is null then
418 OPEN l_fulfill_agg_csr_no_para;
419 FETCH l_fulfill_agg_csr_no_para INTO l_agg_rec;
420 CLOSE l_fulfill_agg_csr_no_para;
421 ELSE
422 OPEN l_fulfill_agg_csr;
423 FETCH l_fulfill_agg_csr INTO l_agg_rec;
424 CLOSE l_fulfill_agg_csr;
425 END IF;
426 use_parallel_worker := 'Y';
427 fnd_file.put_line(FND_FILE.LOG, 'P_parallel = ' || use_parallel_worker);
428
429 END IF;
430
431 fnd_file.put_line(FND_FILE.LOG, 'P_parallel = ' || use_parallel_worker);
432 fnd_file.put_line(FND_FILE.LOG, 'l_agg_rec.minid = ' || l_agg_rec.minid);
433 fnd_file.put_line(FND_FILE.LOG, 'l_agg_rec.maxid = ' || l_agg_rec.maxid);
434 fnd_file.put_line(FND_FILE.LOG, 'l_agg_rec.avgid = ' || l_agg_rec.avgid);
435 fnd_file.put_line(FND_FILE.LOG, 'l_agg_rec.stdid = ' || l_agg_rec.stdid);
436 fnd_file.put_line(FND_FILE.LOG, 'l_agg_rec.total = ' || l_agg_rec.total);
437
438 IF use_parallel_worker = 'Y' THEN
439 /* Added by sjanakir for Bug# 5568285 (FP Bug for 5442268) */
440 IF l_agg_rec.total = 0 THEN
441 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, '***************Subscription Contract to Order Creation****************** ');
442 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ');
443 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ');
444 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, '***************No subscription elements to be interfaced**************** ');
445 END IF;
446 /* Addition Ends */
447
448 l_subrequests := generate_ranges(l_agg_rec.minid, l_agg_rec.maxid,
449 l_default_date ,
450 p_org_id ,
451 P_Customer_id ,
452 P_Grp_Id , l_agg_rec.avgid,
453 l_agg_rec.stdid, l_agg_rec.total);
454
455 fnd_file.put_line(FND_FILE.LOG, 'l_subrequests = ' || l_subrequests);
456 FOR idx in 1..l_subrequests LOOP
457
458 l_ret := FND_REQUEST.submit_request('OKS','OKSKTOCR',
459 null, null, -- TRUE means isSubRequest
460 null, null,
461 /* Commented by sjanakir for Bug# 5568285 (FP Bug for 5442268) */
462 /* l_default_date, null,
463 null, null, */
464 /* Added by sjanakir for Bug# 5568285 (FP Bug for 5442268) */
465 l_default_date, P_Customer_id,
466 P_Grp_Id , p_org_id,
467 /* Addition Ends */
468 range_arr(idx).lo, range_arr(idx).hi,
469 range_arr(idx).line_id_lo, range_arr(idx).line_id_hi); -- Bug 4915691 --
470
471 fnd_file.put_line(FND_FILE.LOG, 'idx = ' || idx);
472 fnd_file.put_line(FND_FILE.LOG, 'l_ret.lo = ' || range_arr(idx).lo);
473 fnd_file.put_line(FND_FILE.LOG, 'l_ret.hi = ' || range_arr(idx).hi);
474 -- fnd_file.put_line(FND_FILE.LOG, 'l_ret = ' || l_ret);
475
476 IF (l_ret = 0) then
477 errbuf := fnd_message.get;
478 retcode := 2;
479 FND_FILE.PUT_LINE (FND_FILE.LOG,
480 'Sub-request failed to submit: ' || errbuf);
481 return;
482 ELSE
483 FND_FILE.PUT_LINE (FND_FILE.LOG,
484 'Sub-request '||to_char(l_ret)||' submitted');
485 END IF;
486 END LOOP;
487 ELSE
488 oks_kto_int_pub.create_order_from_k(ERRBUF => l_errbuf
489 ,RETCODE => l_retcode
490 ,p_contract_id => p_contract_hdr_id
491 ,p_default_date => l_default_date
492 ,P_Customer_id => p_customer_id
493 ,P_Grp_id => p_grp_id
494 ,P_org_id => p_org_id
495 ,P_contract_hdr_id_lo => null
496 ,P_contract_hdr_id_hi => null
497 -- Bug 4915691 --
498 ,p_contract_line_id_lo => null
499 ,p_contract_line_id_hi => null
500 -- Bug 4915691 --
501 );
502
503 END IF;
504 END SUBMIT;
505
506 -- Enter further code below as specified in the Package spec.
507 END OKS_FULFILL;
508