1 PACKAGE BODY oki_load_wbc_pvt as
2 /* $Header: OKIRWBCB.pls 115.21 2003/11/24 08:24:41 kbajaj ship $ */
3
4 --------------------------------------------------------------------------------
5 -- Modification History
6 -- 25-Aug-2001 mezra Changed program to reflect the addition of
7 -- new columns: authoring_org_id,
8 -- authoring_org_name, and subclass code.
9 -- 10-Sep-2001 mezra Added column value, All Categories, for summary
10 -- level of all scs_code; All Organizations, for
11 -- summary level of all organizations.
12 -- 18-Sep-2001 mezra Moved wbc_csr from local cursor to global
13 -- cursor since it is used by all the calc
14 -- procedures.
15 -- 25-Sep-2001 mezra Change usd_ columns to base_.
16 -- 22-Oct-2001 mezra Changed All Categories value to -1.
17 -- 24-Oct-2001 mezra Removed trunc on date columns to increase
18 -- performance since index will be used.
19 -- 26-NOV-2002 rpotnuru NOCOPY Changes
20 -- 19-Dec-2002 brrao UTF-8 Changes to Org Name
21 -- 29-oct-2003 axraghav null out organization_name for 11510 Changes
22 --
23 --------------------------------------------------------------------------------
24
25 -- Global exception declaration
26
27 -- Generic exception to immediately exit the procedure
28 g_excp_exit_immediate EXCEPTION ;
29
30
31 -- Global constant delcaration
32
33 -- Constants for the "All" organization and subclass record
34 g_all_org_id CONSTANT NUMBER := -1 ;
35 g_all_org_name CONSTANT VARCHAR2(240) := 'All Organizations' ;
36 g_all_scs_code CONSTANT VARCHAR2(30) := '-1' ;
37
38
39 -- Global cursor declaration
40
41 -- Cursor to retrieve the rowid for the selected record
42 -- If a rowid is retrieved, then the record will be updated,
43 -- else the record will be inserted.
44 CURSOR g_wbc_csr
45 ( p_period_set_name IN VARCHAR2
46 , p_period_name IN VARCHAR2
47 , p_authoring_org_id IN NUMBER
48 , p_customer_id IN NUMBER
49 , p_scs_code IN VARCHAR2
50 ) IS
51 SELECT rowid
52 FROM oki_wip_by_customers wbc
53 WHERE wbc.period_set_name = p_period_set_name
54 AND wbc.period_name = p_period_name
55 AND wbc.authoring_org_id = p_authoring_org_id
56 AND wbc.customer_party_id = p_customer_id
57 AND wbc.scs_code = p_scs_code ;
58 rec_g_wbc_csr g_wbc_csr%ROWTYPE
59 ;
60
61 --------------------------------------------------------------------------------
62 -- Procedure to insert records into the oki_wip_by_customers table.
63
64 --------------------------------------------------------------------------------
65 PROCEDURE ins_wip_by_cust
66 ( p_period_name IN VARCHAR2
67 , p_period_set_name IN VARCHAR2
68 , p_period_type IN VARCHAR2
69 , p_authoring_org_id IN NUMBER
70 , p_authoring_org_name IN VARCHAR2
71 , p_customer_party_id IN NUMBER
72 , p_customer_name IN VARCHAR2
73 , p_scs_code IN VARCHAR2
74 , p_base_forecast_amount IN NUMBER
75 , p_base_booked_amount IN NUMBER
76 , p_base_lost_amount IN NUMBER
77 , x_retcode OUT NOCOPY VARCHAR2
78 ) IS
79
80 -- Local variable declaration
81
82 -- For error handling
83 l_sqlcode VARCHAR2(100) ;
84 l_sqlerrm VARCHAR2(1000) ;
85
86 l_sequence NUMBER := NULL ;
87
88 -- Cursor declaration
89 CURSOR l_seq_num IS
90 SELECT oki_wip_by_customers_s1.nextval seq
91 FROM dual
92 ;
93 rec_l_seq_num l_seq_num%ROWTYPE ;
94
95 BEGIN
96
97 OPEN l_seq_num ;
98 FETCH l_seq_num INTO rec_l_seq_num ;
99 -- unable to generate sequence number, exit immediately
100 IF l_seq_num%NOTFOUND THEN
101 RAISE g_excp_exit_immediate ;
102 END IF ;
103 l_sequence := rec_l_seq_num.seq ;
104 CLOSE l_seq_num ;
105
106
107 -- initialize return code to success
108 x_retcode := '0';
109
110 INSERT INTO oki_wip_by_customers
111 ( id
112 , period_set_name
113 , period_name
114 , period_type
115 , authoring_org_id
116 , authoring_org_name
117 , customer_party_id
118 , customer_name
119 , scs_code
120 , base_forecast_amount
121 , base_booked_amount
122 , base_lost_amount
123 , request_id
124 , program_application_id
125 , program_id
126 , program_update_date )
127 VALUES ( l_sequence
128 , p_period_set_name
129 , p_period_name
130 , p_period_type
131 , p_authoring_org_id
132 , p_authoring_org_name
133 , p_customer_party_id
134 , p_customer_name
135 , p_scs_code
136 , p_base_forecast_amount
137 , p_base_booked_amount
138 , p_base_lost_amount
139 , oki_load_wbc_pvt.g_request_id
140 , oki_load_wbc_pvt.g_program_application_id
141 , oki_load_wbc_pvt.g_program_id
142 , oki_load_wbc_pvt.g_program_update_date ) ;
143
144
145 EXCEPTION
146 WHEN oki_load_wbc_pvt.g_excp_exit_immediate THEN
147 l_sqlcode := SQLCODE ;
148 l_sqlerrm := SQLERRM ;
149
150 -- Set return code to error
151 x_retcode := '2';
152
153 fnd_message.set_name( application => 'OKI'
154 , name => 'OKI_TABLE_LOAD_FAILURE');
155
156 fnd_message.set_token( token => 'TABLE_NAME'
157 , value => 'OKI_WIP_BY_CUSTOMERS' );
158
159 fnd_file.put_line( which => fnd_file.log
160 , buff => fnd_message.get);
161
162 fnd_file.put_line( which => fnd_file.log
163 , buff => l_sqlcode||' '||l_sqlerrm );
164
165 WHEN OTHERS THEN
166 l_sqlcode := SQLCODE ;
167 l_sqlerrm := SQLERRM ;
168
169 -- Set return code to error
170 x_retcode := '2';
171
172 fnd_message.set_name( application => 'OKI'
173 , name => 'OKI_TABLE_LOAD_FAILURE');
174
175 fnd_message.set_token( token => 'TABLE_NAME'
176 , value => 'OKI_WIP_BY_CUSTOMERS' );
177
178 fnd_file.put_line( which => fnd_file.log
179 , buff => fnd_message.get);
180
181 fnd_file.put_line( which => fnd_file.log
182 , buff => l_sqlcode||' '||l_sqlerrm );
183 END ins_wip_by_cust ;
184
185 --------------------------------------------------------------------------------
186 -- Procedure to update records in the oki_wip_by_customers table.
187
188 --------------------------------------------------------------------------------
189 PROCEDURE upd_wip_by_cust
190 ( p_base_forecast_amount IN NUMBER
191 , p_base_booked_amount IN NUMBER
192 , p_base_lost_amount IN NUMBER
193 , p_wbc_rowid IN ROWID
194 , x_retcode OUT NOCOPY VARCHAR2
195 ) IS
196
197 -- Local variable declaration
198
199 -- For error handling
200 l_sqlcode VARCHAR2(100) ;
201 l_sqlerrm VARCHAR2(1000) ;
202
203
204 BEGIN
205
206 -- initialize return code to success
207 x_retcode := '0';
208
209 UPDATE oki_wip_by_customers SET
210 base_forecast_amount = p_base_forecast_amount
211 , base_booked_amount = p_base_booked_amount
212 , base_lost_amount = p_base_lost_amount
213 , request_id = oki_load_wbc_pvt.g_request_id
214 , program_application_id = oki_load_wbc_pvt.g_program_application_id
215 , program_id = oki_load_wbc_pvt.g_program_id
216 , program_update_date = oki_load_wbc_pvt.g_program_update_date
217 WHERE ROWID = p_wbc_rowid ;
218
219 EXCEPTION
220 WHEN OTHERS THEN
221 l_sqlcode := SQLCODE ;
222 l_sqlerrm := SQLERRM ;
223
224 -- Set return code to error
225 x_retcode := '2';
226
227 fnd_message.set_name( application => 'OKI'
228 , name => 'OKI_UNEXPECTED_FAILURE');
229
230 fnd_message.set_token( token => 'OBJECT_NAME'
231 , value => 'OKI_LOAD_WBC_PVT.UPD_WIP_BY_CUST');
232
233 fnd_file.put_line( which => fnd_file.log
234 , buff => fnd_message.get);
235
236 fnd_file.put_line( which => fnd_file.log
237 , buff => l_sqlcode||' '||l_sqlerrm );
238 END upd_wip_by_cust ;
239
240 --------------------------------------------------------------------------------
241 -- Procedure to calcuate the forecast and booked amounts for the
242 -- customers.
243 -- Calculates the amounts by each dimension:
244 -- period set name
245 -- period type
246 -- period name
247 -- customer
248 -- subclass
249 -- organization
250 --
251 --------------------------------------------------------------------------------
252 PROCEDURE calc_wbc_dtl1
253 ( p_period_set_name IN VARCHAR2
254 , p_period_type IN VARCHAR2
255 , p_summary_build_date IN DATE
256 , x_retcode OUT NOCOPY VARCHAR2
257 ) IS
258
259 -- Local variable declaration
260
261 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
262 l_retcode VARCHAR2(1) := NULL ;
263
264 -- For error handling
265 l_sqlcode VARCHAR2(100) := NULL ;
266 l_sqlerrm VARCHAR2(1000) := NULL ;
267
268 -- Holds the calculated forecast, booked and lost amounts
269 l_base_forecast_amount NUMBER := 0 ;
270 l_base_booked_amount NUMBER := 0 ;
271 l_base_lost_amount NUMBER := 0 ;
272
273 -- Location within the program before the error was encountered.
274 l_loc VARCHAR2(100) ;
275
276 -- Holds the truncated start and end dates from gl_periods
277 l_glpr_start_date DATE ;
278 l_glpr_end_date DATE ;
279
280 -- Cursor declaration
281
282 -- Cursor that calculates the forecast amount for a particular customer,
283 -- oganization and subclass
284 CURSOR l_cust_fcst_csr
285 ( p_glpr_start_date IN DATE
286 , p_glpr_end_date IN DATE
287 , p_authoring_org_id IN NUMBER
288 , p_customer_party_id IN NUMBER
289 , p_scs_code IN VARCHAR2
290 ) IS
291 SELECT NVL(SUM(shd.base_forecast_amount), 0) base_forecast_amount
292 FROM oki_sales_k_hdrs shd
293 -- Contract is a renewal contract
294 WHERE shd.is_new_yn IS NULL
295 -- Contract must have undergone forecasting
296 AND shd.close_date IS NOT NULL
297 AND shd.win_percent IS NOT NULL
298 -- Expected close date is in the period
299 AND shd.close_date BETWEEN p_glpr_start_date AND p_glpr_end_date
300 AND shd.customer_party_id = p_customer_party_id
301 AND shd.authoring_org_id = p_authoring_org_id
302 AND shd.scs_code = p_scs_code
303 ;
304 rec_l_cust_fcst_csr l_cust_fcst_csr%ROWTYPE ;
305
306 -- Cursor that calculates the booked amount for a particular customer,
307 -- oganization and subclass
308 CURSOR l_cust_booked_csr
309 ( p_glpr_start_date IN DATE
310 , p_glpr_end_date IN DATE
311 , p_authoring_org_id IN NUMBER
312 , p_customer_party_id IN NUMBER
313 , p_scs_code IN VARCHAR
314 ) IS
315 SELECT NVL(SUM(shd.base_contract_amount), 0) base_contract_amount
316 FROM oki_sales_k_hdrs shd
317 -- Contract is a renewal contract
318 WHERE shd.is_new_yn IS NULL
319 -- Contract is signed or active
320 AND shd.ste_code IN ('SIGNED', 'ACTIVE')
321 -- Lesser of the signed date or the start date falls within
322 -- the period
323 AND LEAST(NVL(shd.date_signed, shd.start_date),shd.start_date)
324 BETWEEN p_glpr_start_date AND p_glpr_end_date
325 AND shd.customer_party_id = p_customer_party_id
326 AND shd.authoring_org_id = p_authoring_org_id
327 AND shd.scs_code = p_scs_code
328 ;
329 rec_l_cust_booked_csr l_cust_booked_csr%ROWTYPE ;
330
331 -- Cursor that calculates the lost amount for a particular customer,
332 -- oganization and subclass
333 CURSOR l_cust_lost_csr
334 ( p_glpr_start_date IN DATE
335 , p_glpr_end_date IN DATE
336 , p_authoring_org_id IN NUMBER
337 , p_customer_party_id IN NUMBER
338 , p_scs_code IN VARCHAR
339 ) IS
340 SELECT NVL(SUM(cpl.base_price_negotiated), 0) base_price_negotiated
341 /*11510 change removed oki_Expired_lines and joined to oki_cov_prd_lines*/
342 FROM oki_cov_prd_lines cpl
343 , oki_sales_k_hdrs shd
344 WHERE shd.chr_id = cpl.chr_id
345 /*11510 change start*/
346 AND cpl.is_exp_not_renewed_yn='Y'
347 /*11510 change start*/
348 -- expiration date is in the period
349 AND cpl.end_date BETWEEN p_glpr_start_date AND p_glpr_end_date
350 AND shd.customer_party_id = p_customer_party_id
351 AND shd.authoring_org_id = p_authoring_org_id
352 AND shd.scs_code = p_scs_code ;
353 rec_l_cust_lost_csr l_cust_lost_csr%ROWTYPE ;
354
355 -- Cursor to get all the customers, oganizations and subclasses
356 CURSOR l_cust_id_csr IS
357 SELECT DISTINCT(shd.customer_party_id) customer_id
358 , /*11510 change*/ NULL customer_name
359 , shd.authoring_org_id authoring_org_id
360 , /*11510 change*/ NULL authoring_org_name
361 , shd.scs_code scs_code
362 FROM oki_sales_k_hdrs shd
363 ;
364
365
366 BEGIN
367
368 -- initialize return code to success
369 l_retcode := '0';
370
371 l_loc := 'Looping through valid customers.' ;
372 << l_cust_id_csr_loop >>
373 -- Loop through all the customers to calcuate the appropriate amounts
374 FOR rec_l_cust_id_csr IN l_cust_id_csr LOOP
375
376 l_loc := 'Looping through valid periods.' ;
377 << g_glpr_csr_loop >>
378 -- Loop through all the periods
379 FOR rec_g_glpr_csr IN oki_utl_pvt.g_glpr_csr(
380 p_period_set_name, p_period_type, p_summary_build_date ) LOOP
381
382 -- Get the truncated gl_periods start and end dates
383 l_glpr_start_date := trunc(rec_g_glpr_csr.start_date );
384 l_glpr_end_date := trunc(rec_g_glpr_csr.end_date );
385
386 -- Re-initialize the amounts before calculating
387 l_base_forecast_amount := 0 ;
388 l_base_booked_amount := 0 ;
389 l_base_lost_amount := 0 ;
390
391 l_loc := 'Opening cursor to determine the forecast sum.' ;
392 -- Calculate the forecast amount for a given customer
393 OPEN l_cust_fcst_csr( l_glpr_start_date, l_glpr_end_date,
394 rec_l_cust_id_csr.authoring_org_id,
395 rec_l_cust_id_csr.customer_id, rec_l_cust_id_csr.scs_code ) ;
396 FETCH l_cust_fcst_csr INTO rec_l_cust_fcst_csr ;
397 IF l_cust_fcst_csr%FOUND THEN
398 l_base_forecast_amount := rec_l_cust_fcst_csr.base_forecast_amount ;
399 END IF ;
400 CLOSE l_cust_fcst_csr ;
401
402 l_loc := 'Opening cursor to determine the booked sum.' ;
403 -- Calculate the booked amount for a given customer
404 OPEN l_cust_booked_csr( l_glpr_start_date, l_glpr_end_date,
405 rec_l_cust_id_csr.authoring_org_id,
406 rec_l_cust_id_csr.customer_id, rec_l_cust_id_csr.scs_code ) ;
407 FETCH l_cust_booked_csr INTO rec_l_cust_booked_csr ;
408 IF l_cust_booked_csr%FOUND THEN
409 l_base_booked_amount := rec_l_cust_booked_csr.base_contract_amount ;
410 END IF ;
411 CLOSE l_cust_booked_csr ;
412
413 l_loc := 'Opening cursor to determine the lost sum.' ;
414 -- Calculate the lost amount for a given customer
415 OPEN l_cust_lost_csr( l_glpr_start_date, l_glpr_end_date,
416 rec_l_cust_id_csr.authoring_org_id,
417 rec_l_cust_id_csr.customer_id, rec_l_cust_id_csr.scs_code ) ;
418 FETCH l_cust_lost_csr INTO rec_l_cust_lost_csr ;
419 IF l_cust_lost_csr%FOUND THEN
420 l_base_lost_amount := rec_l_cust_lost_csr.base_price_negotiated ;
421 END IF ;
422 CLOSE l_cust_lost_csr ;
423
424
425 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
426 -- Determine if the record is a new one or an existing one
427 OPEN oki_load_wbc_pvt.g_wbc_csr( rec_g_glpr_csr.period_set_name,
428 rec_g_glpr_csr.period_name, rec_l_cust_id_csr.authoring_org_id,
432 l_loc := 'Insert the new record.' ;
429 rec_l_cust_id_csr.customer_id, rec_l_cust_id_csr.scs_code ) ;
430 FETCH oki_load_wbc_pvt.g_wbc_csr into rec_g_wbc_csr ;
431 IF oki_load_wbc_pvt.g_wbc_csr%NOTFOUND THEN
433 -- Insert the current period data for the period
434 oki_load_wbc_pvt.ins_wip_by_cust(
435 p_period_name => rec_g_glpr_csr.period_name
436 , p_period_set_name => rec_g_glpr_csr.period_set_name
437 , p_period_type => rec_g_glpr_csr.period_type
438 , p_authoring_org_id => rec_l_cust_id_csr.authoring_org_id
439 , p_authoring_org_name => rec_l_cust_id_csr.authoring_org_name
440 , p_customer_party_id => rec_l_cust_id_csr.customer_id
441 , p_customer_name => rec_l_cust_id_csr.customer_name
442 , p_scs_code => rec_l_cust_id_csr.scs_code
443 , p_base_forecast_amount => l_base_forecast_amount
444 , p_base_booked_amount => l_base_booked_amount
445 , p_base_lost_amount => l_base_lost_amount
446 , x_retcode => l_retcode) ;
447
448 IF l_retcode = '2' THEN
449 -- Load failed, exit immediately.
450 RAISE oki_load_wbc_pvt.g_excp_exit_immediate ;
451 EXIT ;
452 END IF ;
453
454 ELSE
455 l_loc := 'Update the existing record.' ;
456 -- Record already exists, so perform an update
457 oki_load_wbc_pvt.upd_wip_by_cust(
458 p_base_forecast_amount => l_base_forecast_amount
459 , p_base_booked_amount => l_base_booked_amount
460 , p_base_lost_amount => l_base_lost_amount
461 , p_wbc_rowid => rec_g_wbc_csr.rowid
462 , x_retcode => l_retcode ) ;
463
464 IF l_retcode = '2' THEN
465 -- Load failed, exit immediately.
466 RAISE oki_load_wbc_pvt.g_excp_exit_immediate ;
467 END IF ;
468 END IF ;
469
470 CLOSE g_wbc_csr ;
471
472 END LOOP g_glpr_csr_loop ;
473 END LOOP l_cust_id_csr_loop ;
474
475 EXCEPTION
476 WHEN oki_load_wbc_pvt.g_excp_exit_immediate THEN
477 -- Do not log an error ; It has already been logged.
478 -- Set return code to error
479 x_retcode := '2' ;
480
481 WHEN OTHERS THEN
482 l_sqlcode := SQLCODE ;
483 l_sqlerrm := SQLERRM ;
484
485 -- Set return code TO error
486 x_retcode := '2' ;
487
488 fnd_message.set_name( application => 'OKI'
489 , name => 'OKI_UNEXPECTED_FAILURE');
490
491 fnd_message.set_token( token => 'OBJECT_NAME'
492 , value => 'OKI_LOAD_WBC_PVT.CALC_WBC_DTL1');
493
494 fnd_file.put_line( which => fnd_file.log
495 , buff => fnd_message.get);
496
497 -- Log the location within the procedure where the error occurred
498 fnd_message.set_name( application => 'OKI'
499 , name => 'OKI_LOC_IN_PROG_FAILURE');
500
501 fnd_message.set_token( token => 'LOCATION'
502 , value => l_loc);
503
504 fnd_file.put_line( which => fnd_file.log
505 , buff => fnd_message.get);
506
507 fnd_file.put_line( which => fnd_file.log
508 , buff => l_sqlcode||' '||l_sqlerrm );
509
510 END calc_wbc_dtl1 ;
511
512 --------------------------------------------------------------------------------
513 -- Procedure to calculate the forecast and booked amounts for the
514 -- customers.
515 -- Calculates the amounts across organizations:
516 -- each period set name
517 -- each period type
518 -- each period name
519 -- each customer
520 -- each subclass
521 -- all organizations
522 --
523 --------------------------------------------------------------------------------
524 PROCEDURE calc_wbc_dtl2
525 ( p_period_set_name IN VARCHAR2
526 , p_period_type IN VARCHAR2
527 , p_summary_build_date IN DATE
528 , x_retcode OUT NOCOPY VARCHAR2
529 ) IS
530
531 -- Local variable declaration
532
533 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
534 l_retcode VARCHAR2(1) := NULL ;
535
536 -- For error handling
537 l_sqlcode VARCHAR2(100) := NULL ;
538 l_sqlerrm VARCHAR2(1000) := NULL ;
539
540 -- Holds the calculated forecast, booked and lost amounts
541 l_base_forecast_amount NUMBER := 0 ;
542 l_base_booked_amount NUMBER := 0 ;
543 l_base_lost_amount NUMBER := 0 ;
544
545 -- Location within the program before the error was encountered.
546 l_loc VARCHAR2(100) ;
547
548 -- Holds the truncated start and end dates from gl_periods
549 l_glpr_start_date DATE ;
550 l_glpr_end_date DATE ;
551
552 -- Cusror declaration
553
554 -- Cursor that calculates the forecast amount for a particular customer
555 -- and subclass
556 CURSOR l_cust_fcst_csr
557 ( p_glpr_start_date IN DATE
561 ) IS
558 , p_glpr_end_date IN DATE
559 , p_customer_party_id IN NUMBER
560 , p_scs_code IN VARCHAR2
562 SELECT NVL(SUM(shd.base_forecast_amount), 0) base_forecast_amount
563 FROM oki_sales_k_hdrs shd
564 -- Contract is a renewal contract
565 WHERE shd.is_new_yn IS NULL
566 -- Contract must have undergone forecasting
567 AND shd.close_date IS NOT NULL
568 AND shd.win_percent IS NOT NULL
569 -- Expected close date is in the period
570 AND shd.close_date BETWEEN p_glpr_start_date AND p_glpr_end_date
571 AND shd.customer_party_id = p_customer_party_id
572 AND shd.scs_code = p_scs_code
573 ;
574 rec_l_cust_fcst_csr l_cust_fcst_csr%ROWTYPE ;
575
576 -- Cursor that calculates the booked amount for a particular customer
577 -- and subclass
578 CURSOR l_cust_booked_csr
579 ( p_glpr_start_date IN DATE
580 , p_glpr_end_date IN DATE
581 , p_customer_party_id IN NUMBER
582 , p_scs_code IN VARCHAR2
583 ) IS
584 SELECT NVL(SUM(shd.base_contract_amount), 0) base_contract_amount
585 FROM oki_sales_k_hdrs shd
586 -- Contract is a renewal contract
587 WHERE shd.is_new_yn IS NULL
588 -- Contract is signed or active
589 AND shd.ste_code IN ('SIGNED', 'ACTIVE')
590 -- Lesser of the signed date or the start date falls within
591 -- the period
592 AND LEAST(NVL(shd.date_signed, shd.start_date), shd.start_date)
593 BETWEEN p_glpr_start_date AND p_glpr_end_date
594 AND shd.customer_party_id = p_customer_party_id
595 AND shd.scs_code = p_scs_code
596 ;
597 rec_l_cust_booked_csr l_cust_booked_csr%ROWTYPE ;
598
599 -- Cursor that calculates the lost amount for a particular customer
600 -- and subclass
601 CURSOR l_cust_lost_csr
602 ( p_glpr_start_date IN DATE
603 , p_glpr_end_date IN DATE
604 , p_customer_party_id IN NUMBER
605 , p_scs_code IN VARCHAR2
606 ) IS
607 SELECT NVL(SUM(cpl.base_price_negotiated), 0) base_price_negotiated
608 /*11510 change removed oki_Expired_lines and joined to oki_cov_prd_lines*/
609 FROM oki_cov_prd_lines cpl
610 , oki_sales_k_hdrs shd
611 -- expiration date is in the period
612 WHERE shd.chr_id = cpl.chr_id
613 /*11510 change start*/
614 AND cpl.is_exp_not_renewed_yn = 'Y'
615 /*11510 change end*/
616 AND cpl.end_date BETWEEN p_glpr_start_date AND p_glpr_end_date
617 AND shd.customer_party_id = p_customer_party_id
618 AND shd.scs_code = p_scs_code
619 ;
620 rec_l_cust_lost_csr l_cust_lost_csr%ROWTYPE ;
621
622 -- Cusror to get all the customers
623 CURSOR l_cust_id_csr IS
624 SELECT DISTINCT(shd.customer_party_id) customer_id
625 , /*11510 change*/NULL customer_name
626 , shd.scs_code
627 FROM oki_sales_k_hdrs shd ;
628
629
630 BEGIN
631
632 -- initialize return code to success
633 l_retcode := '0';
634
635 l_loc := 'Looping through valid customers.' ;
636 << l_cust_id_csr_loop >>
637 -- Loop through all the customers to calcuate the appropriate amounts
638 FOR rec_l_cust_id_csr IN l_cust_id_csr LOOP
639
640 l_loc := 'Looping through valid periods.' ;
641 << g_glpr_csr_loop >>
642 -- Loop through all the periods
643 FOR rec_g_glpr_csr IN oki_utl_pvt.g_glpr_csr(
644 p_period_set_name, p_period_type, p_summary_build_date ) LOOP
645
646 -- Get the truncated gl_periods start and end dates
647 l_glpr_start_date := trunc(rec_g_glpr_csr.start_date );
648 l_glpr_end_date := trunc(rec_g_glpr_csr.end_date );
649
650 -- Re-initialize the amounts before calculating
651 l_base_forecast_amount := 0 ;
652 l_base_booked_amount := 0 ;
653 l_base_lost_amount := 0 ;
654
655 l_loc := 'Opening cursor to determine the forecast sum.' ;
656 -- Calculate the forecast amount for a given customer
657 OPEN l_cust_fcst_csr( l_glpr_start_date, l_glpr_end_date,
658 rec_l_cust_id_csr.customer_id, rec_l_cust_id_csr.scs_code ) ;
659 FETCH l_cust_fcst_csr INTO rec_l_cust_fcst_csr ;
660 IF l_cust_fcst_csr%FOUND THEN
661 l_base_forecast_amount := rec_l_cust_fcst_csr.base_forecast_amount ;
662 END IF ;
663 CLOSE l_cust_fcst_csr ;
664
665 l_loc := 'Opening cursor to determine the booked sum.' ;
666 -- Calculate the booked amount for a given customer
667 OPEN l_cust_booked_csr( l_glpr_start_date, l_glpr_end_date,
668 rec_l_cust_id_csr.customer_id, rec_l_cust_id_csr.scs_code ) ;
669 FETCH l_cust_booked_csr INTO rec_l_cust_booked_csr ;
670 IF l_cust_booked_csr%FOUND THEN
671 l_base_booked_amount := rec_l_cust_booked_csr.base_contract_amount ;
672 END IF ;
673 CLOSE l_cust_booked_csr ;
674
675 l_loc := 'Opening cursor to determine the lost sum.' ;
676 -- Calculate the lost amount for a given customer
677 OPEN l_cust_lost_csr( l_glpr_start_date, l_glpr_end_date,
678 rec_l_cust_id_csr.customer_id, rec_l_cust_id_csr.scs_code ) ;
679 FETCH l_cust_lost_csr INTO rec_l_cust_lost_csr ;
680 IF l_cust_lost_csr%FOUND THEN
681 l_base_lost_amount := rec_l_cust_lost_csr.base_price_negotiated ;
685
682 END IF ;
683 CLOSE l_cust_lost_csr ;
684
686 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
687 -- Determine if the record is a new one or an existing one
688 OPEN oki_load_wbc_pvt.g_wbc_csr( rec_g_glpr_csr.period_set_name,
689 rec_g_glpr_csr.period_name, oki_load_wbc_pvt.g_all_org_id,
690 rec_l_cust_id_csr.customer_id, rec_l_cust_id_csr.scs_code ) ;
691 FETCH oki_load_wbc_pvt.g_wbc_csr into rec_g_wbc_csr ;
692 IF oki_load_wbc_pvt.g_wbc_csr%NOTFOUND THEN
693 l_loc := 'Insert the new record.' ;
694 -- Insert the current period data for the period
695 oki_load_wbc_pvt.ins_wip_by_cust(
696 p_period_name => rec_g_glpr_csr.period_name
697 , p_period_set_name => rec_g_glpr_csr.period_set_name
698 , p_period_type => rec_g_glpr_csr.period_type
699 , p_authoring_org_id => oki_load_wbc_pvt.g_all_org_id
700 , p_authoring_org_name => oki_load_wbc_pvt.g_all_org_name
701 , p_customer_party_id => rec_l_cust_id_csr.customer_id
702 , p_customer_name => rec_l_cust_id_csr.customer_name
703 , p_scs_code => rec_l_cust_id_csr.scs_code
704 , p_base_forecast_amount => l_base_forecast_amount
705 , p_base_booked_amount => l_base_booked_amount
706 , p_base_lost_amount => l_base_lost_amount
707 , x_retcode => l_retcode) ;
708
709 IF l_retcode = '2' THEN
710 -- Load failed, exit immediately.
711 RAISE oki_load_wbc_pvt.g_excp_exit_immediate ;
712 EXIT ;
713 END IF ;
714
715 ELSE
716 l_loc := 'Update the existing record.' ;
717 -- Record already exists, so perform an update
718 oki_load_wbc_pvt.upd_wip_by_cust(
719 p_base_forecast_amount => l_base_forecast_amount
720 , p_base_booked_amount => l_base_booked_amount
721 , p_base_lost_amount => l_base_lost_amount
722 , p_wbc_rowid => rec_g_wbc_csr.rowid
723 , x_retcode => l_retcode ) ;
724
725 IF l_retcode = '2' THEN
726 -- Load failed, exit immediately.
727 RAISE oki_load_wbc_pvt.g_excp_exit_immediate ;
728 END IF ;
729 END IF ;
730
731 CLOSE oki_load_wbc_pvt.g_wbc_csr ;
732
733 END LOOP g_glpr_csr_loop ;
734 END LOOP l_cust_id_csr_loop ;
735
736 EXCEPTION
737 WHEN oki_load_wbc_pvt.g_excp_exit_immediate THEN
738 -- Do not log an error ; It has already been logged.
739 -- Set return code to error
740 x_retcode := '2' ;
741
742 WHEN OTHERS THEN
743 l_sqlcode := SQLCODE ;
744 l_sqlerrm := SQLERRM ;
745
746 -- Set return code TO error
747 x_retcode := '2' ;
748
749 fnd_message.set_name( application => 'OKI'
750 , name => 'OKI_UNEXPECTED_FAILURE');
751
752 fnd_message.set_token( token => 'OBJECT_NAME'
753 , value => 'OKI_LOAD_WBC_PVT.CALC_WBC_DTL2');
754
755 fnd_file.put_line( which => fnd_file.log
756 , buff => fnd_message.get);
757
758 -- Log the location within the procedure where the error occurred
759 fnd_message.set_name( application => 'OKI'
760 , name => 'OKI_LOC_IN_PROG_FAILURE');
761
762 fnd_message.set_token( token => 'LOCATION'
763 , value => l_loc);
764
765 fnd_file.put_line( which => fnd_file.log
766 , buff => fnd_message.get);
767
768 fnd_file.put_line( which => fnd_file.log
769 , buff => l_sqlcode||' '||l_sqlerrm );
770
771 END calc_wbc_dtl2 ;
772
773 --------------------------------------------------------------------------------
774 -- Procedure to calculate the forecast and booked amounts for the
775 -- customers.
776 -- Calculates the amounts across organizations and subclasses
777 -- each period set name
778 -- each period type
779 -- each period name
780 -- each customer
781 -- all subclasses
782 -- all organizations
783 --
784 --------------------------------------------------------------------------------
785 PROCEDURE calc_wbc_sum
786 ( p_period_set_name IN VARCHAR2
787 , p_period_type IN VARCHAR2
788 , p_summary_build_date IN DATE
789 , x_retcode OUT NOCOPY VARCHAR2
790 ) IS
791
792 -- Local variable declaration
793
794 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
795 l_retcode VARCHAR2(1) := NULL ;
796
797 -- For error handling
798 l_sqlcode VARCHAR2(100) := NULL ;
799 l_sqlerrm VARCHAR2(1000) := NULL ;
800
801 -- Holds the calculated forecast, booked and lost amounts
802 l_base_forecast_amount NUMBER := 0 ;
803 l_base_booked_amount NUMBER := 0 ;
804 l_base_lost_amount NUMBER := 0 ;
805
806 -- Location within the program before the error was encountered.
810 l_glpr_start_date DATE ;
807 l_loc VARCHAR2(100) ;
808
809 -- Holds the truncated start and end dates from gl_periods
811 l_glpr_end_date DATE ;
812
813 -- Cusror declaration
814
815 -- Cursor that calculates the forecast amount for a particular customer
816 CURSOR l_cust_fcst_csr
817 ( p_glpr_start_date IN DATE
818 , p_glpr_end_date IN DATE
819 , p_customer_party_id IN NUMBER
820 ) IS
821 SELECT NVL(SUM(shd.base_forecast_amount), 0) base_forecast_amount
822 FROM oki_sales_k_hdrs shd
823 -- Contract is a renewal contract
824 WHERE shd.is_new_yn IS NULL
825 -- Contract must have undergone forecasting
826 AND shd.close_date IS NOT NULL
827 AND shd.win_percent IS NOT NULL
828 -- Expected close date is in the period
829 AND shd.close_date BETWEEN p_glpr_start_date AND p_glpr_end_date
830 AND shd.customer_party_id = p_customer_party_id
831 ;
832 rec_l_cust_fcst_csr l_cust_fcst_csr%ROWTYPE ;
833
834 -- Cursor that calculates the booked amount for a particular customer
835 CURSOR l_cust_booked_csr
836 ( p_glpr_start_date IN DATE
837 , p_glpr_end_date IN DATE
838 , p_customer_party_id IN NUMBER
839 ) IS
840 SELECT NVL(SUM(shd.base_contract_amount), 0) base_contract_amount
841 FROM oki_sales_k_hdrs shd
842 -- Contract is a renewal contract
843 WHERE shd.is_new_yn IS NULL
844 -- Contract is signed or active
845 AND shd.ste_code IN ('SIGNED', 'ACTIVE')
846 -- Lesser of the signed date or the start date falls within
847 -- the period
848 AND LEAST(NVL(shd.date_signed, shd.start_date), shd.start_date)
849 BETWEEN p_glpr_start_date AND p_glpr_end_date
850 AND shd.customer_party_id = p_customer_party_id
851 ;
852 rec_l_cust_booked_csr l_cust_booked_csr%ROWTYPE ;
853
854 -- Cursor that calculates the lost amount for a particular customer
855 CURSOR l_cust_lost_csr
856 ( p_glpr_start_date IN DATE
857 , p_glpr_end_date IN DATE
858 , p_customer_party_id IN NUMBER
859 ) IS
860 SELECT NVL(SUM(cpl.base_price_negotiated), 0) base_price_negotiated
861 /*11510 change removed oki_Expired_lines and joined to oki_cov_prd_lines*/
862 FROM oki_cov_prd_lines cpl
863 , oki_sales_k_hdrs shd
864 -- expiration date is in the period
865 WHERE shd.chr_id = cpl.chr_id
866 /*11510 change start*/
867 AND cpl.is_exp_not_renewed_yn = 'Y'
868 /*11510 change end*/
869 AND cpl.end_date BETWEEN p_glpr_start_date AND p_glpr_end_date
870 AND shd.customer_party_id = p_customer_party_id
871 ;
872 rec_l_cust_lost_csr l_cust_lost_csr%ROWTYPE ;
873
874 -- Cusror to get all the customers
875 CURSOR l_cust_id_csr IS
876 SELECT DISTINCT(shd.customer_party_id) customer_id,
877 /*11510 Change*/ NULL customer_name
878 FROM oki_sales_k_hdrs shd
879 ;
880
881
882 BEGIN
883
884 -- initialize return code to success
885 l_retcode := '0';
886
887 l_loc := 'Looping through valid customers.' ;
888 -- Loop through all the customers to calcuate the appropriate amounts
889 FOR rec_l_cust_id_csr IN l_cust_id_csr LOOP
890
891 l_loc := 'Looping through valid periods.' ;
892 -- Loop through all the periods
893 FOR rec_g_glpr_csr IN oki_utl_pvt.g_glpr_csr(
894 p_period_set_name, p_period_type, p_summary_build_date ) LOOP
895
896 -- Get the truncated gl_periods start and end dates
897 l_glpr_start_date := trunc(rec_g_glpr_csr.start_date );
898 l_glpr_end_date := trunc(rec_g_glpr_csr.end_date );
899
900 -- Re-initialize the amounts before calculating
901 l_base_forecast_amount := 0 ;
902 l_base_booked_amount := 0 ;
903 l_base_lost_amount := 0 ;
904
905 l_loc := 'Opening cursor to determine the forecast sum.' ;
906 -- Calculate the forecast amount for a given customer
907 OPEN l_cust_fcst_csr( l_glpr_start_date, l_glpr_end_date,
908 rec_l_cust_id_csr.customer_id ) ;
909 FETCH l_cust_fcst_csr INTO rec_l_cust_fcst_csr ;
910 IF l_cust_fcst_csr%FOUND THEN
911 l_base_forecast_amount := rec_l_cust_fcst_csr.base_forecast_amount ;
912 END IF ;
913 CLOSE l_cust_fcst_csr ;
914
915 l_loc := 'Opening cursor to determine the booked sum.' ;
916 -- Calculate the booked amount for a given customer
917 OPEN l_cust_booked_csr( l_glpr_start_date, l_glpr_end_date,
918 rec_l_cust_id_csr.customer_id ) ;
919 FETCH l_cust_booked_csr INTO rec_l_cust_booked_csr ;
920 IF l_cust_booked_csr%FOUND THEN
921 l_base_booked_amount := rec_l_cust_booked_csr.base_contract_amount ;
922 END IF ;
923 CLOSE l_cust_booked_csr ;
924
925 l_loc := 'Opening cursor to determine the lost sum.' ;
926 -- Calculate the lost amount for a given customer
927 OPEN l_cust_lost_csr( l_glpr_start_date, l_glpr_end_date,
928 rec_l_cust_id_csr.customer_id ) ;
929 FETCH l_cust_lost_csr INTO rec_l_cust_lost_csr ;
933 CLOSE l_cust_lost_csr ;
930 IF l_cust_lost_csr%FOUND THEN
931 l_base_lost_amount := rec_l_cust_lost_csr.base_price_negotiated ;
932 END IF ;
934
935 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
936 -- Determine if the record is a new one or an existing one
937 OPEN oki_load_wbc_pvt.g_wbc_csr( rec_g_glpr_csr.period_set_name,
938 rec_g_glpr_csr.period_name, oki_load_wbc_pvt.g_all_org_id,
939 rec_l_cust_id_csr.customer_id,
940 oki_load_wbc_pvt.g_all_scs_code ) ;
941 FETCH oki_load_wbc_pvt.g_wbc_csr into rec_g_wbc_csr ;
942 IF oki_load_wbc_pvt.g_wbc_csr%NOTFOUND THEN
943 l_loc := 'Insert the new record.' ;
944 -- Insert the current period data for the period
945 oki_load_wbc_pvt.ins_wip_by_cust(
946 p_period_name => rec_g_glpr_csr.period_name
947 , p_period_set_name => rec_g_glpr_csr.period_set_name
948 , p_period_type => rec_g_glpr_csr.period_type
949 , p_authoring_org_id => oki_load_wbc_pvt.g_all_org_id
950 , p_authoring_org_name => oki_load_wbc_pvt.g_all_org_name
951 , p_customer_party_id => rec_l_cust_id_csr.customer_id
952 , p_customer_name => rec_l_cust_id_csr.customer_name
953 , p_scs_code => oki_load_wbc_pvt.g_all_scs_code
954 , p_base_forecast_amount => l_base_forecast_amount
955 , p_base_booked_amount => l_base_booked_amount
956 , p_base_lost_amount => l_base_lost_amount
957 , x_retcode => l_retcode) ;
958
959 IF l_retcode = '2' THEN
960 -- Load failed, exit immediately.
961 RAISE oki_load_wbc_pvt.g_excp_exit_immediate ;
962 EXIT ;
963 END IF ;
964
965 ELSE
966 l_loc := 'Update the existing record.' ;
967 -- Record already exists, so perform an update
968 oki_load_wbc_pvt.upd_wip_by_cust(
969 p_base_forecast_amount => l_base_forecast_amount
970 , p_base_booked_amount => l_base_booked_amount
971 , p_base_lost_amount => l_base_lost_amount
972 , p_wbc_rowid => rec_g_wbc_csr.rowid
973 , x_retcode => l_retcode ) ;
974
975 IF l_retcode = '2' THEN
976 -- Load failed, exit immediately.
977 RAISE oki_load_wbc_pvt.g_excp_exit_immediate ;
978 END IF ;
979 END IF ;
980
981 CLOSE oki_load_wbc_pvt.g_wbc_csr ;
982
983 END LOOP ;
984 END LOOP ;
985
986 EXCEPTION
987 WHEN oki_load_wbc_pvt.g_excp_exit_immediate THEN
988 -- Do not log an error ; It has already been logged.
989 -- Set return code to error
990 x_retcode := '2' ;
991
992 WHEN OTHERS THEN
993 l_sqlcode := SQLCODE ;
994 l_sqlerrm := SQLERRM ;
995
996 -- Set return code TO error
997 x_retcode := '2' ;
998
999 fnd_message.set_name( application => 'OKI'
1000 , name => 'OKI_UNEXPECTED_FAILURE');
1001
1002 fnd_message.set_token( token => 'OBJECT_NAME'
1003 , value => 'OKI_LOAD_WBC_PVT.CALC_WBC_SUM');
1004
1005 fnd_file.put_line( which => fnd_file.log
1006 , buff => fnd_message.get);
1007
1008 -- Log the location within the procedure where the error occurred
1009 fnd_message.set_name( application => 'OKI'
1010 , name => 'OKI_LOC_IN_PROG_FAILURE');
1011
1012 fnd_message.set_token( token => 'LOCATION'
1013 , value => l_loc);
1014
1015 fnd_file.put_line( which => fnd_file.log
1016 , buff => fnd_message.get);
1017
1018 fnd_file.put_line( which => fnd_file.log
1019 , buff => l_sqlcode||' '||l_sqlerrm );
1020
1021 END calc_wbc_sum ;
1022
1023 --------------------------------------------------------------------------------
1024 -- Procedure to create all the WIP by customer records.
1025 -- If an error is encountered in this procedure or subsequent procedures then
1026 -- rollback all changes. Once the table is loaded and the data is committed
1027 -- the load is considered successful even if update of the oki_refreshs
1028 -- table failed.
1029 --------------------------------------------------------------------------------
1030 PROCEDURE crt_wip_by_cust
1031 ( p_period_set_name IN VARCHAR2
1032 , p_period_type IN VARCHAR2
1033 , p_summary_build_date IN DATE
1034 , x_errbuf OUT NOCOPY VARCHAR2
1035 , x_retcode OUT NOCOPY VARCHAR2
1036 ) IS
1037
1038 -- Local exception declaration
1039
1040 -- Exception to immediately exit the procedure
1041 l_excp_upd_refresh EXCEPTION ;
1042
1043
1044 -- Constant declaration
1045
1046 -- Name of the table for which data is being inserted
1047 l_table_name CONSTANT VARCHAR2(30) := 'OKI_WIP_BY_CUSTOMERS' ;
1048
1049
1050 -- Local variable declaration
1051
1055 -- For error handling
1052 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
1053 l_retcode VARCHAR2(1) := NULL ;
1054
1056 l_sqlcode VARCHAR2(100) ;
1057 l_sqlerrm VARCHAR2(1000) ;
1058
1059
1060 BEGIN
1061
1062 SAVEPOINT oki_load_wbc_pvt_crt_wip_cust ;
1063
1064 -- initialize return code to success
1065 l_retcode := '0' ;
1066 x_retcode := '0' ;
1067
1068 -- Procedure to calculate the amounts for each dimension
1069 oki_load_wbc_pvt.calc_wbc_dtl1(
1070 p_period_set_name => p_period_set_name
1071 , p_period_type => p_period_type
1072 , p_summary_build_date => p_summary_build_date
1073 , x_retcode => l_retcode ) ;
1074
1075 IF l_retcode = '2' THEN
1076 -- Load failed, exit immediately.
1077 RAISE oki_load_wbc_pvt.g_excp_exit_immediate ;
1078 END IF ;
1079
1080 -- Procedure to calculate the amounts across organizations
1081 oki_load_wbc_pvt.calc_wbc_dtl2(
1082 p_period_set_name => p_period_set_name
1083 , p_period_type => p_period_type
1084 , p_summary_build_date => p_summary_build_date
1085 , x_retcode => l_retcode ) ;
1086
1087 IF l_retcode = '2' THEN
1088 -- Load failed, exit immediately.
1089 RAISE oki_load_wbc_pvt.g_excp_exit_immediate ;
1090 END IF ;
1091
1092 -- Procedure to calculate the amounts across organizations
1093 -- and subclasses
1094 oki_load_wbc_pvt.calc_wbc_sum(
1095 p_period_set_name => p_period_set_name
1096 , p_period_type => p_period_type
1097 , p_summary_build_date => p_summary_build_date
1098 , x_retcode => l_retcode ) ;
1099
1100 IF l_retcode = '2' THEN
1101 -- Load failed, exit immediately.
1102 RAISE oki_load_wbc_pvt.g_excp_exit_immediate ;
1103 END IF ;
1104
1105 COMMIT ;
1106
1107 SAVEPOINT oki_load_wbc_pvt_upd_refresh ;
1108
1109 -- Table loaded successfully. Log message in concurrent manager
1110 -- log indicating successful load.
1111 fnd_message.set_name( application => 'OKI'
1112 , name => 'OKI_TABLE_LOAD_SUCCESS');
1113
1114 fnd_message.set_token( token => 'TABLE_NAME'
1115 , value => l_table_name );
1116
1117 fnd_file.put_line( which => fnd_file.log
1118 , buff => fnd_message.get);
1119
1120 oki_refresh_pvt.update_oki_refresh( l_table_name, l_retcode ) ;
1121
1122 IF l_retcode in ('1', '2') THEN
1123 -- Update to OKI_REFRESHS failed, exit immediately.
1124 RAISE l_excp_upd_refresh ;
1125 END IF ;
1126
1127 COMMIT ;
1128
1129 EXCEPTION
1130 WHEN l_excp_upd_refresh THEN
1131 -- Do not log error; It has already been logged by the refreshs
1132 -- program
1133 x_retcode := l_retcode ;
1134
1135 ROLLBACK to oki_load_wbc_pvt_upd_refresh ;
1136
1137 WHEN oki_load_wbc_pvt.g_excp_exit_immediate THEN
1138 -- Do not log an error ; It has already been logged.
1139 -- Set return code to error
1140 x_retcode := '2' ;
1141
1142 ROLLBACK TO oki_load_wbc_pvt_crt_wip_cust ;
1143
1144 WHEN OTHERS THEN
1145
1146 l_sqlcode := sqlcode ;
1147 l_sqlerrm := sqlerrm ;
1148
1149 -- Set return code to error
1150 x_retcode := '2' ;
1151
1152 -- rollback all transactions
1153 ROLLBACK to oki_load_wbc_pvt_crt_wip_cust ;
1154
1155
1156 fnd_message.set_name( application => 'OKI'
1157 , name => 'OKI_UNEXPECTED_FAILURE');
1158
1159 fnd_message.set_token( token => 'OBJECT_NAME'
1160 , value => 'OKI_LOAD_WBC_PVT.CRT_WIP_BY_CUST');
1161
1162 fnd_file.put_line( which => fnd_file.log
1163 , buff => fnd_message.get);
1164
1165 fnd_file.put_line( which => fnd_file.log
1166 , buff => l_sqlcode||' '||l_sqlerrm );
1167
1168 end crt_wip_by_cust ;
1169
1170
1171 BEGIN
1172 -- Initialize the global variables used to log this job run
1173 -- from concurrent manager
1174 g_request_id := fnd_global.conc_request_id ;
1175 g_program_application_id := fnd_global.prog_appl_id ;
1176 g_program_id := fnd_global.conc_program_id ;
1177 g_program_update_date := SYSDATE ;
1178
1179 END oki_load_wbc_pvt ;
[Home] [Help]