eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OKI_DBI_LOAD_CLEB_PVT dependencies on OKI_DBI_CLE_B

Line 229: l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.OKI_DBI_CLE_B_OLD';

225: l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.OKI_DBI_CURR_CONV';
226: EXECUTE IMMEDIATE l_sql_string;
227: rlog( 'Truncated table OKI_DBI_CURR_CONV' ,2);
228:
229: l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.OKI_DBI_CLE_B_OLD';
230: EXECUTE IMMEDIATE l_sql_string;
231: rlog( 'Truncated Table OKI_DBI_CLE_B_OLD' ,2);
232:
233: -- RSG is now dropping the MV log before doing the initial load

Line 231: rlog( 'Truncated Table OKI_DBI_CLE_B_OLD' ,2);

227: rlog( 'Truncated table OKI_DBI_CURR_CONV' ,2);
228:
229: l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.OKI_DBI_CLE_B_OLD';
230: EXECUTE IMMEDIATE l_sql_string;
231: rlog( 'Truncated Table OKI_DBI_CLE_B_OLD' ,2);
232:
233: -- RSG is now dropping the MV log before doing the initial load
234: -- l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.MLOG$_OKI_DBI_CLE_B';
235: -- l_sql_string := 'TRUNCATE TABLE MLOG$_OKI_DBI_CLE_B';

Line 234: -- l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.MLOG$_OKI_DBI_CLE_B';

230: EXECUTE IMMEDIATE l_sql_string;
231: rlog( 'Truncated Table OKI_DBI_CLE_B_OLD' ,2);
232:
233: -- RSG is now dropping the MV log before doing the initial load
234: -- l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.MLOG$_OKI_DBI_CLE_B';
235: -- l_sql_string := 'TRUNCATE TABLE MLOG$_OKI_DBI_CLE_B';
236: -- EXECUTE IMMEDIATE l_sql_string;
237: -- rlog( 'Base table LOG MLOG_OKI_DBI_CLE_B was truncated ' ,2);
238:

Line 235: -- l_sql_string := 'TRUNCATE TABLE MLOG$_OKI_DBI_CLE_B';

231: rlog( 'Truncated Table OKI_DBI_CLE_B_OLD' ,2);
232:
233: -- RSG is now dropping the MV log before doing the initial load
234: -- l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.MLOG$_OKI_DBI_CLE_B';
235: -- l_sql_string := 'TRUNCATE TABLE MLOG$_OKI_DBI_CLE_B';
236: -- EXECUTE IMMEDIATE l_sql_string;
237: -- rlog( 'Base table LOG MLOG_OKI_DBI_CLE_B was truncated ' ,2);
238:
239: l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.OKI_DBI_CLE_B';

Line 237: -- rlog( 'Base table LOG MLOG_OKI_DBI_CLE_B was truncated ' ,2);

233: -- RSG is now dropping the MV log before doing the initial load
234: -- l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.MLOG$_OKI_DBI_CLE_B';
235: -- l_sql_string := 'TRUNCATE TABLE MLOG$_OKI_DBI_CLE_B';
236: -- EXECUTE IMMEDIATE l_sql_string;
237: -- rlog( 'Base table LOG MLOG_OKI_DBI_CLE_B was truncated ' ,2);
238:
239: l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.OKI_DBI_CLE_B';
240: EXECUTE IMMEDIATE l_sql_string;
241: rlog( 'Truncated Table OKI_DBI_CLE_B' ,2);

Line 239: l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.OKI_DBI_CLE_B';

235: -- l_sql_string := 'TRUNCATE TABLE MLOG$_OKI_DBI_CLE_B';
236: -- EXECUTE IMMEDIATE l_sql_string;
237: -- rlog( 'Base table LOG MLOG_OKI_DBI_CLE_B was truncated ' ,2);
238:
239: l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.OKI_DBI_CLE_B';
240: EXECUTE IMMEDIATE l_sql_string;
241: rlog( 'Truncated Table OKI_DBI_CLE_B' ,2);
242:
243: BIS_COLLECTION_UTILITIES.DeleteLogForObject('OKIDBICLEB');

Line 241: rlog( 'Truncated Table OKI_DBI_CLE_B' ,2);

237: -- rlog( 'Base table LOG MLOG_OKI_DBI_CLE_B was truncated ' ,2);
238:
239: l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.OKI_DBI_CLE_B';
240: EXECUTE IMMEDIATE l_sql_string;
241: rlog( 'Truncated Table OKI_DBI_CLE_B' ,2);
242:
243: BIS_COLLECTION_UTILITIES.DeleteLogForObject('OKIDBICLEB');
244: rlog( 'Completed resetting base tables and BIS log file ' || fnd_date.date_to_displayDT(sysdate),1);
245: END IF;

Line 1135: DELETE FROM OKI_DBI_WORKER_STATUS WHERE OBJECT_NAME = 'OKI_DBI_CLE_B_OLD';

1131: EXECUTE IMMEDIATE l_sql_string;
1132: rlog( 'Truncated Table ' || l_oki_schema || '.OKI_DBI_WORKER_STATUS',1);
1133: */
1134:
1135: DELETE FROM OKI_DBI_WORKER_STATUS WHERE OBJECT_NAME = 'OKI_DBI_CLE_B_OLD';
1136: COMMIT;
1137:
1138: l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema || '.OKI_DBI_CLE_B_OLD';
1139: EXECUTE IMMEDIATE l_sql_string;

Line 1138: l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema || '.OKI_DBI_CLE_B_OLD';

1134:
1135: DELETE FROM OKI_DBI_WORKER_STATUS WHERE OBJECT_NAME = 'OKI_DBI_CLE_B_OLD';
1136: COMMIT;
1137:
1138: l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema || '.OKI_DBI_CLE_B_OLD';
1139: EXECUTE IMMEDIATE l_sql_string;
1140: rlog( 'Truncated Table ' || l_oki_schema || '.OKI_DBI_CLE_B_OLD',1);
1141:
1142: l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema || '.OKI_DBI_CURR_CONV';

Line 1140: rlog( 'Truncated Table ' || l_oki_schema || '.OKI_DBI_CLE_B_OLD',1);

1136: COMMIT;
1137:
1138: l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema || '.OKI_DBI_CLE_B_OLD';
1139: EXECUTE IMMEDIATE l_sql_string;
1140: rlog( 'Truncated Table ' || l_oki_schema || '.OKI_DBI_CLE_B_OLD',1);
1141:
1142: l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema || '.OKI_DBI_CURR_CONV';
1143: EXECUTE IMMEDIATE l_sql_string;
1144: rlog( 'Truncated Table ' || l_oki_schema || '.OKI_DBI_CURR_CONV',1);

Line 2141: SELECT /+ index_ffs(f OKI_DBI_CLE_B_U1) parallel_index(f OKI_DBI_CLE_B_U1) /

2137: EXECUTE IMMEDIATE l_sql_string ;
2138:
2139: INSERT /*+ APPEND */ INTO oki_dbi_cle_del
2140: (cle_id)
2141: SELECT /*+ index_ffs(f OKI_DBI_CLE_B_U1) parallel_index(f OKI_DBI_CLE_B_U1) */
2142: f.cle_id
2143: FROM oki_dbi_cle_b f
2144: WHERE cle_id not in (SELECT /*+ index_ffs(okc OKC_K_LINES_B_U1) parallel_index(okc OKC_K_LINES_B_U1) */
2145: id

Line 2143: FROM oki_dbi_cle_b f

2139: INSERT /*+ APPEND */ INTO oki_dbi_cle_del
2140: (cle_id)
2141: SELECT /*+ index_ffs(f OKI_DBI_CLE_B_U1) parallel_index(f OKI_DBI_CLE_B_U1) */
2142: f.cle_id
2143: FROM oki_dbi_cle_b f
2144: WHERE cle_id not in (SELECT /*+ index_ffs(okc OKC_K_LINES_B_U1) parallel_index(okc OKC_K_LINES_B_U1) */
2145: id
2146: FROM okc_k_lines_b okc );
2147:

Line 2155: DELETE FROM oki_dbi_cle_b

2151: TO_CHAR(l_count), 2);
2152: COMMIT;
2153:
2154: IF ( g_del_count > 0 ) THEN
2155: DELETE FROM oki_dbi_cle_b
2156: WHERE cle_id IN ( SELECT cle_id
2157: FROM oki_dbi_cle_del
2158: );
2159: l_count := SQL%ROWCOUNT;

Line 2161: rlog('Number of lines deleted from oki_dbi_cle_b: ' ||

2157: FROM oki_dbi_cle_del
2158: );
2159: l_count := SQL%ROWCOUNT;
2160: END IF;
2161: rlog('Number of lines deleted from oki_dbi_cle_b: ' ||
2162: TO_CHAR(l_count), 2);
2163: COMMIT;
2164: END IF ;
2165:

Line 2217: l_location := ' Inserting modified contract lines into OKI_DBI_CLE_B_OLD

2213: l_annu_curr_code := bis_common_parameters.get_annualized_currency_code;
2214: l_glob_curr_code := 'PRIMARY';/* BUg 4015406 bis_common_parameters.get_currency_code; */
2215: l_sglob_curr_code := 'SECONDARY';/*bis_common_parameters.get_secondary_currency_code;*/
2216:
2217: l_location := ' Inserting modified contract lines into OKI_DBI_CLE_B_OLD
2218: table with worker '|| p_worker||' ';
2219: rlog('Populating Staging Table OKI_DBI_CLE_B_OLD with updated/created Contracts : '
2220: ||fnd_date.date_to_displayDT(sysdate), 1) ;
2221:

Line 2219: rlog('Populating Staging Table OKI_DBI_CLE_B_OLD with updated/created Contracts : '

2215: l_sglob_curr_code := 'SECONDARY';/*bis_common_parameters.get_secondary_currency_code;*/
2216:
2217: l_location := ' Inserting modified contract lines into OKI_DBI_CLE_B_OLD
2218: table with worker '|| p_worker||' ';
2219: rlog('Populating Staging Table OKI_DBI_CLE_B_OLD with updated/created Contracts : '
2220: ||fnd_date.date_to_displayDT(sysdate), 1) ;
2221:
2222: -- l_balance_logic:='EVENTDATE';
2223: -- l_balance_logic:=nvl(l_balance_logic,'CONTRDATE');

Line 2264: INSERT /+ append / INTO oki_dbi_cle_b_old(

2260: AND clsop.opn_code in ('RENEWAL','REN_CON')
2261: );
2262:
2263:
2264: INSERT /*+ append */ INTO oki_dbi_cle_b_old(
2265: chr_id
2266: , cle_id
2267: , cle_creation_date
2268: , inv_organization_id

Line 2868: SELECT 1 FROM oki_dbi_cle_b old

2864: ) ilv1
2865: )new
2866: WHERE NOT EXISTS
2867: (
2868: SELECT 1 FROM oki_dbi_cle_b old
2869: WHERE new.cle_id = old.cle_id
2870: AND new.cl_last_update_date = old.cl_last_update_date
2871: AND new.sl_last_update_date = old.sl_last_update_date
2872: AND new.hdr_last_update_date = old.hdr_last_update_date

Line 2969: rlog( 'Number of lines inserted into OKI_DBI_CLE_B_OLD is '||p_recs_processed,2);

2965: -- AND NVL(new.hdr_grace_end_date, l_run_date) = NVL(old.hdr_grace_end_date, l_run_date)
2966: -- AND NVL(new.hdr_date_signed,l_run_date) = NVL(old.hdr_date_signed,l_run_date)
2967: ));
2968: p_recs_processed := SQL%ROWCOUNT ;
2969: rlog( 'Number of lines inserted into OKI_DBI_CLE_B_OLD is '||p_recs_processed,2);
2970: rlog('Load of Staging Table OKI_DBI_CLE_B_OLD for updated/created Contracts completed: '
2971: ||fnd_date.date_to_displayDT(sysdate), 1) ;
2972: COMMIT;
2973:

Line 2970: rlog('Load of Staging Table OKI_DBI_CLE_B_OLD for updated/created Contracts completed: '

2966: -- AND NVL(new.hdr_date_signed,l_run_date) = NVL(old.hdr_date_signed,l_run_date)
2967: ));
2968: p_recs_processed := SQL%ROWCOUNT ;
2969: rlog( 'Number of lines inserted into OKI_DBI_CLE_B_OLD is '||p_recs_processed,2);
2970: rlog('Load of Staging Table OKI_DBI_CLE_B_OLD for updated/created Contracts completed: '
2971: ||fnd_date.date_to_displayDT(sysdate), 1) ;
2972: COMMIT;
2973:
2974: EXCEPTION

Line 3043: FROM oki_dbi_cle_b b

3039: AND clsop.opn_code in ('RENEWAL','REN_CON')
3040: AND
3041: ( EXISTS
3042: (select null
3043: FROM oki_dbi_cle_b b
3044: where b.cle_id = rel.object_cle_id)
3045: OR
3046: EXISTS
3047: (select null

Line 3048: FROM oki_dbi_cle_b_old o

3044: where b.cle_id = rel.object_cle_id)
3045: OR
3046: EXISTS
3047: (select null
3048: FROM oki_dbi_cle_b_old o
3049: where o.cle_Id = rel.object_cle_id)
3050: )
3051: AND rel.object_cle_id IS NOT NULL
3052: AND rel.subject_cle_id IS NOT NULL

Line 3152: , oki_dbi_cle_b del

3148: del.cle_id
3149: , MAX(rl.subject_cle_id) KEEP (DENSE_RANK LAST
3150: ORDER BY rl.last_update_date) r_cle_id
3151: FROM oki_dbi_cle_del del1
3152: , oki_dbi_cle_b del
3153: , okc_operation_lines rl
3154: , okc_operation_instances opins
3155: , okc_class_operations clsop
3156: WHERE 1 = 1

Line 3241: l_location := ' Updating Covered Lines in OKI_DBI_CLE_B_OLD with Renewals: ' ;

3237: l_sts_count number;
3238:
3239: BEGIN
3240:
3241: l_location := ' Updating Covered Lines in OKI_DBI_CLE_B_OLD with Renewals: ' ;
3242:
3243: rlog('Updating Staging Table OKI_DBI_CLE_B_OLD with renewal information : '
3244: ||fnd_date.date_to_displayDT(sysdate), 1) ;
3245: UPDATE oki_dbi_cle_b_old b set (

Line 3243: rlog('Updating Staging Table OKI_DBI_CLE_B_OLD with renewal information : '

3239: BEGIN
3240:
3241: l_location := ' Updating Covered Lines in OKI_DBI_CLE_B_OLD with Renewals: ' ;
3242:
3243: rlog('Updating Staging Table OKI_DBI_CLE_B_OLD with renewal information : '
3244: ||fnd_date.date_to_displayDT(sysdate), 1) ;
3245: UPDATE oki_dbi_cle_b_old b set (
3246: r_chr_id
3247: , r_cle_id

Line 3245: UPDATE oki_dbi_cle_b_old b set (

3241: l_location := ' Updating Covered Lines in OKI_DBI_CLE_B_OLD with Renewals: ' ;
3242:
3243: rlog('Updating Staging Table OKI_DBI_CLE_B_OLD with renewal information : '
3244: ||fnd_date.date_to_displayDT(sysdate), 1) ;
3245: UPDATE oki_dbi_cle_b_old b set (
3246: r_chr_id
3247: , r_cle_id
3248: , r_date_signed
3249: , r_date_cancelled

Line 3257: oki_dbi_cle_b_old old

3253: , old.cle_id
3254: , date_signed
3255: , date_cancelled
3256: FROM oki_dbi_ren_inc ren_inc,
3257: oki_dbi_cle_b_old old
3258: WHERE ren_inc.r_cle_id = old.cle_id (+)
3259: AND ren_inc.worker_number = p_worker
3260: AND ren_inc.cle_id = b.cle_id)
3261: WHERE EXISTS (SELECT/*+ ordered index(ren_inc) use_nl(old) */ 1

Line 3262: FROM oki_dbi_cle_b_old old,

3258: WHERE ren_inc.r_cle_id = old.cle_id (+)
3259: AND ren_inc.worker_number = p_worker
3260: AND ren_inc.cle_id = b.cle_id)
3261: WHERE EXISTS (SELECT/*+ ordered index(ren_inc) use_nl(old) */ 1
3262: FROM oki_dbi_cle_b_old old,
3263: oki_dbi_ren_inc ren_inc
3264: WHERE ren_inc.r_cle_id = old.cle_id (+)
3265: AND ren_inc.worker_number = p_worker
3266: AND ren_inc.cle_id = b.cle_id);

Line 3270: rlog( 'Number of lines updated into OKI_DBI_CLE_B_OLD with renewal information is '

3266: AND ren_inc.cle_id = b.cle_id);
3267:
3268: p_recs_processed := SQL%ROWCOUNT ;
3269:
3270: rlog( 'Number of lines updated into OKI_DBI_CLE_B_OLD with renewal information is '
3271: || TO_CHAR(p_recs_processed),2) ;
3272: rlog('Updation of Staging Table OKI_DBI_CLE_B_OLD with renewal information completed: '
3273: ||fnd_date.date_to_displayDT(sysdate), 1) ;
3274:

Line 3272: rlog('Updation of Staging Table OKI_DBI_CLE_B_OLD with renewal information completed: '

3268: p_recs_processed := SQL%ROWCOUNT ;
3269:
3270: rlog( 'Number of lines updated into OKI_DBI_CLE_B_OLD with renewal information is '
3271: || TO_CHAR(p_recs_processed),2) ;
3272: rlog('Updation of Staging Table OKI_DBI_CLE_B_OLD with renewal information completed: '
3273: ||fnd_date.date_to_displayDT(sysdate), 1) ;
3274:
3275: COMMIT;
3276: EXCEPTION

Line 3312: l_location := 'Updating Covered Lines in OKI_DBI_CLE_B_OLD with Original Information: ' ;

3308: l_sts_count NUMBER;
3309:
3310: BEGIN
3311:
3312: l_location := 'Updating Covered Lines in OKI_DBI_CLE_B_OLD with Original Information: ' ;
3313:
3314: rlog('Updating Staging Table OKI_DBI_CLE_B_OLD with original information : '
3315: ||fnd_date.date_to_displayDT(sysdate), 1) ;
3316:

Line 3314: rlog('Updating Staging Table OKI_DBI_CLE_B_OLD with original information : '

3310: BEGIN
3311:
3312: l_location := 'Updating Covered Lines in OKI_DBI_CLE_B_OLD with Original Information: ' ;
3313:
3314: rlog('Updating Staging Table OKI_DBI_CLE_B_OLD with original information : '
3315: ||fnd_date.date_to_displayDT(sysdate), 1) ;
3316:
3317: UPDATE oki_dbi_cle_b_old b SET
3318: ( p_chr_id

Line 3317: UPDATE oki_dbi_cle_b_old b SET

3313:
3314: rlog('Updating Staging Table OKI_DBI_CLE_B_OLD with original information : '
3315: ||fnd_date.date_to_displayDT(sysdate), 1) ;
3316:
3317: UPDATE oki_dbi_cle_b_old b SET
3318: ( p_chr_id
3319: , p_cle_id
3320: , p_price_negotiated
3321: , p_price_negotiated_f

Line 3380: ,oki_dbi_cle_b_old cle

3376: , credit_amt_a
3377: , override_amt_a
3378: , supp_credit_a
3379: FROM oki_dbi_prev_inc prev_inc
3380: ,oki_dbi_cle_b_old cle
3381: WHERE prev_inc.p_cle_id = cle.cle_id (+)
3382: AND prev_inc.worker_number = p_worker
3383: AND prev_inc.cle_id = b.cle_id)
3384: WHERE EXISTS (SELECT /*+ ordered index(prev_inc) use_nl(cle) */ 1

Line 3385: FROM oki_dbi_cle_b_old cle

3381: WHERE prev_inc.p_cle_id = cle.cle_id (+)
3382: AND prev_inc.worker_number = p_worker
3383: AND prev_inc.cle_id = b.cle_id)
3384: WHERE EXISTS (SELECT /*+ ordered index(prev_inc) use_nl(cle) */ 1
3385: FROM oki_dbi_cle_b_old cle
3386: , oki_dbi_prev_inc prev_inc
3387: WHERE prev_inc.p_cle_id = cle.cle_id (+)
3388: AND prev_inc.worker_number = p_worker
3389: AND prev_inc.cle_id = b.cle_id);

Line 3393: rlog( 'Number of lines updated into OKI_DBI_CLE_B_OLD with original information is '

3389: AND prev_inc.cle_id = b.cle_id);
3390:
3391: p_recs_processed := SQL%ROWCOUNT ;
3392:
3393: rlog( 'Number of lines updated into OKI_DBI_CLE_B_OLD with original information is '
3394: || TO_CHAR(p_recs_processed),2) ;
3395: rlog('Updation of Staging Table OKI_DBI_CLE_B_OLD with original information completed: '
3396: ||fnd_date.date_to_displayDT(sysdate), 1) ;
3397:

Line 3395: rlog('Updation of Staging Table OKI_DBI_CLE_B_OLD with original information completed: '

3391: p_recs_processed := SQL%ROWCOUNT ;
3392:
3393: rlog( 'Number of lines updated into OKI_DBI_CLE_B_OLD with original information is '
3394: || TO_CHAR(p_recs_processed),2) ;
3395: rlog('Updation of Staging Table OKI_DBI_CLE_B_OLD with original information completed: '
3396: ||fnd_date.date_to_displayDT(sysdate), 1) ;
3397:
3398: COMMIT;
3399:

Line 3402: rlog('Error in update_LHS: Updating LHS information in OKI_DBI_CLE_B_OLD FAILED',0);

3398: COMMIT;
3399:
3400: EXCEPTION
3401: WHEN OTHERS THEN
3402: rlog('Error in update_LHS: Updating LHS information in OKI_DBI_CLE_B_OLD FAILED',0);
3403: rlog(sqlerrm ||' '||sqlcode, 0) ;
3404: rlog(l_location , 0) ;
3405: fnd_message.set_name( application => 'FND'
3406: , name => 'CRM-DEBUG ERROR' ) ;

Line 3416: Description: Load OKI_DBI_CLE_B_OLD with original, LHS and RHS information.

3412: END Update_LHS;
3413:
3414: /*******************************************************************************
3415: Procedure: incr_load
3416: Description: Load OKI_DBI_CLE_B_OLD with original, LHS and RHS information.
3417: Update OKI_DBI_CLE_B with this information.
3418: Parameters: p_recs_processed:The number of records processed
3419: *******************************************************************************/
3420: PROCEDURE incr_Load ( p_recs_processed OUT NOCOPY NUMBER )

Line 3417: Update OKI_DBI_CLE_B with this information.

3413:
3414: /*******************************************************************************
3415: Procedure: incr_load
3416: Description: Load OKI_DBI_CLE_B_OLD with original, LHS and RHS information.
3417: Update OKI_DBI_CLE_B with this information.
3418: Parameters: p_recs_processed:The number of records processed
3419: *******************************************************************************/
3420: PROCEDURE incr_Load ( p_recs_processed OUT NOCOPY NUMBER )
3421: IS

Line 3472: 'OKI_DBI_CLE_B_OLD'

3468: object_name
3469: , worker_number
3470: , status
3471: , phase) VALUES(
3472: 'OKI_DBI_CLE_B_OLD'
3473: , worker_no
3474: , 'UNASSIGNED'
3475: , 0);
3476: END IF;

Line 3483: rlog ('Inserting modified/created contracts into OKI_DBI_CLE_B_OLD : '||fnd_date.date_to_displayDT(sysdate), 1);

3479:
3480: FOR l_phase IN 1 .. 7
3481: LOOP
3482: IF l_phase = 1 THEN
3483: rlog ('Inserting modified/created contracts into OKI_DBI_CLE_B_OLD : '||fnd_date.date_to_displayDT(sysdate), 1);
3484: ELSIF l_phase = 2 THEN
3485: rlog ('Finding new renewal relationship information Phase I : '||fnd_date.date_to_displayDT(sysdate),1);
3486: ELSIF l_phase = 3 THEN
3487: rlog ('Finding new renewal relationship information Phase II : '||fnd_date.date_to_displayDT(sysdate),1);

Line 3491: rlog ('Inserting covered lines into OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B : '||fnd_date.date_to_displayDT(sysdate),1);

3487: rlog ('Finding new renewal relationship information Phase II : '||fnd_date.date_to_displayDT(sysdate),1);
3488: ELSIF l_phase = 4 THEN
3489: rlog ('Finding new renewal relationship information Phase III : '||fnd_date.date_to_displayDT(sysdate),1);
3490: ELSIF l_phase = 5 THEN
3491: rlog ('Inserting covered lines into OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B : '||fnd_date.date_to_displayDT(sysdate),1);
3492: ELSIF l_phase = 6 THEN
3493: rlog ('Updating renewal information into OKI_DBI_CLE_B_OLD : '||fnd_date.date_to_displayDT(sysdate),1);
3494: ELSIF l_phase = 7 THEN
3495: rlog ('Updating original information into OKI_DBI_CLE_B_OLD : '||fnd_date.date_to_displayDT(sysdate),1);

Line 3493: rlog ('Updating renewal information into OKI_DBI_CLE_B_OLD : '||fnd_date.date_to_displayDT(sysdate),1);

3489: rlog ('Finding new renewal relationship information Phase III : '||fnd_date.date_to_displayDT(sysdate),1);
3490: ELSIF l_phase = 5 THEN
3491: rlog ('Inserting covered lines into OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B : '||fnd_date.date_to_displayDT(sysdate),1);
3492: ELSIF l_phase = 6 THEN
3493: rlog ('Updating renewal information into OKI_DBI_CLE_B_OLD : '||fnd_date.date_to_displayDT(sysdate),1);
3494: ELSIF l_phase = 7 THEN
3495: rlog ('Updating original information into OKI_DBI_CLE_B_OLD : '||fnd_date.date_to_displayDT(sysdate),1);
3496: END IF;
3497: FOR i IN 1 .. l_no_of_workers

Line 3495: rlog ('Updating original information into OKI_DBI_CLE_B_OLD : '||fnd_date.date_to_displayDT(sysdate),1);

3491: rlog ('Inserting covered lines into OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B : '||fnd_date.date_to_displayDT(sysdate),1);
3492: ELSIF l_phase = 6 THEN
3493: rlog ('Updating renewal information into OKI_DBI_CLE_B_OLD : '||fnd_date.date_to_displayDT(sysdate),1);
3494: ELSIF l_phase = 7 THEN
3495: rlog ('Updating original information into OKI_DBI_CLE_B_OLD : '||fnd_date.date_to_displayDT(sysdate),1);
3496: END IF;
3497: FOR i IN 1 .. l_no_of_workers
3498: LOOP
3499: g_worker(i) := LAUNCH_WORKER( i

Line 3524: WHERE object_name = 'OKI_DBI_CLE_B_OLD';

3520: l_wip_cnt,
3521: l_failed_cnt,
3522: l_tot_cnt
3523: FROM OKI_DBI_WORKER_STATUS
3524: WHERE object_name = 'OKI_DBI_CLE_B_OLD';
3525:
3526: IF ( l_failed_cnt > 0 ) THEN
3527: rlog('One of the sub-workers errored out..Exiting',1);
3528: RAISE G_CHILD_PROCESS_ISSUE;

Line 3533: rlog ('Inserting modified/created contracts into OKI_DBI_CLE_B_OLD completed : '||fnd_date.date_to_displayDT(sysdate), 1);

3529: END IF;
3530:
3531: IF ( l_tot_cnt = l_completed_cnt ) THEN
3532: IF l_phase = 1 THEN
3533: rlog ('Inserting modified/created contracts into OKI_DBI_CLE_B_OLD completed : '||fnd_date.date_to_displayDT(sysdate), 1);
3534: ELSIF l_phase = 2 THEN
3535: rlog ('Finding new renewal relationship information Phase I completed : '||fnd_date.date_to_displayDT(sysdate),1);
3536: ELSIF l_phase = 3 THEN
3537: rlog ('Finding new renewal relationship information Phase II completed : '||fnd_date.date_to_displayDT(sysdate),1);

Line 3541: rlog ('Inserting covered lines into OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B completed : '||fnd_date.date_to_displayDT(sysdate),1);

3537: rlog ('Finding new renewal relationship information Phase II completed : '||fnd_date.date_to_displayDT(sysdate),1);
3538: ELSIF l_phase = 4 THEN
3539: rlog ('Finding new renewal relationship information Phase III completed : '||fnd_date.date_to_displayDT(sysdate),1);
3540: ELSIF l_phase = 5 THEN
3541: rlog ('Inserting covered lines into OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B completed : '||fnd_date.date_to_displayDT(sysdate),1);
3542: ELSIF l_phase = 6 THEN
3543: rlog ('Updating renewal information into OKI_DBI_CLE_B_OLD completed : '||fnd_date.date_to_displayDT(sysdate),1);
3544: ELSIF l_phase = 7 THEN
3545: rlog ('Updating original information into OKI_DBI_CLE_B_OLD completed : '||fnd_date.date_to_displayDT(sysdate),1);

Line 3543: rlog ('Updating renewal information into OKI_DBI_CLE_B_OLD completed : '||fnd_date.date_to_displayDT(sysdate),1);

3539: rlog ('Finding new renewal relationship information Phase III completed : '||fnd_date.date_to_displayDT(sysdate),1);
3540: ELSIF l_phase = 5 THEN
3541: rlog ('Inserting covered lines into OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B completed : '||fnd_date.date_to_displayDT(sysdate),1);
3542: ELSIF l_phase = 6 THEN
3543: rlog ('Updating renewal information into OKI_DBI_CLE_B_OLD completed : '||fnd_date.date_to_displayDT(sysdate),1);
3544: ELSIF l_phase = 7 THEN
3545: rlog ('Updating original information into OKI_DBI_CLE_B_OLD completed : '||fnd_date.date_to_displayDT(sysdate),1);
3546: END IF;
3547: EXIT;

Line 3545: rlog ('Updating original information into OKI_DBI_CLE_B_OLD completed : '||fnd_date.date_to_displayDT(sysdate),1);

3541: rlog ('Inserting covered lines into OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B completed : '||fnd_date.date_to_displayDT(sysdate),1);
3542: ELSIF l_phase = 6 THEN
3543: rlog ('Updating renewal information into OKI_DBI_CLE_B_OLD completed : '||fnd_date.date_to_displayDT(sysdate),1);
3544: ELSIF l_phase = 7 THEN
3545: rlog ('Updating original information into OKI_DBI_CLE_B_OLD completed : '||fnd_date.date_to_displayDT(sysdate),1);
3546: END IF;
3547: EXIT;
3548: END IF;
3549: DBMS_LOCK.sleep(5);

Line 3643: GATHER_TABLE_STATS(TABNAME=>'OKI_DBI_CLE_B_OLD');

3639: END IF;
3640: COMMIT;
3641: END LOOP;
3642:
3643: GATHER_TABLE_STATS(TABNAME=>'OKI_DBI_CLE_B_OLD');
3644:
3645: rlog('Updating summary table OKI_DBI_CLE_B from OKI_DBI_CLE_B_OLD: '
3646: ||fnd_date.date_to_displayDT(sysdate), 1) ;
3647:

Line 3645: rlog('Updating summary table OKI_DBI_CLE_B from OKI_DBI_CLE_B_OLD: '

3641: END LOOP;
3642:
3643: GATHER_TABLE_STATS(TABNAME=>'OKI_DBI_CLE_B_OLD');
3644:
3645: rlog('Updating summary table OKI_DBI_CLE_B from OKI_DBI_CLE_B_OLD: '
3646: ||fnd_date.date_to_displayDT(sysdate), 1) ;
3647:
3648: UPDATE
3649: (

Line 3973: FROM OKI_DBI_CLE_B_OLD S ,

3969: , s.reminder old_reminder /* Added this colunm as a part of ER#5950128 */
3970: , s.hdr_term_cancel_source old_hdr_term_cancel_source /* Added for ER 6684955 */
3971: , s.sl_term_cancel_source old_sl_term_cancel_source /* Added for ER 6684955 */
3972:
3973: FROM OKI_DBI_CLE_B_OLD S ,
3974: OKI_DBI_CLE_B B
3975: WHERE B.CLE_ID = S.CLE_ID )
3976: SET
3977: new_chr_id = old_chr_id,

Line 3974: OKI_DBI_CLE_B B

3970: , s.hdr_term_cancel_source old_hdr_term_cancel_source /* Added for ER 6684955 */
3971: , s.sl_term_cancel_source old_sl_term_cancel_source /* Added for ER 6684955 */
3972:
3973: FROM OKI_DBI_CLE_B_OLD S ,
3974: OKI_DBI_CLE_B B
3975: WHERE B.CLE_ID = S.CLE_ID )
3976: SET
3977: new_chr_id = old_chr_id,
3978: new_cle_creation_date = old_cle_creation_date ,

Line 4145: rlog('Number of lines updated into OKI_DBI_CLE_B : '|| l_count,2);

4141:
4142:
4143: l_count := SQL%ROWCOUNT;
4144:
4145: rlog('Number of lines updated into OKI_DBI_CLE_B : '|| l_count,2);
4146: COMMIT ;
4147:
4148: INSERT INTO OKI_DBI_CLE_B
4149: (

Line 4148: INSERT INTO OKI_DBI_CLE_B

4144:
4145: rlog('Number of lines updated into OKI_DBI_CLE_B : '|| l_count,2);
4146: COMMIT ;
4147:
4148: INSERT INTO OKI_DBI_CLE_B
4149: (
4150: chr_id
4151: , cle_id
4152: , cle_creation_date

Line 4486: FROM oki_dbi_cle_b_old s

4482: , s.negotiation_status /* Added this colunm as a part of ER#5950128 */
4483: , s.reminder /* Added this colunm as a part of ER#5950128 */
4484: , s.hdr_term_cancel_source /* Added for ER 6684955 */
4485: , s.sl_term_cancel_source /* Added for ER 6684955 */
4486: FROM oki_dbi_cle_b_old s
4487: WHERE NOT EXISTS( SELECT NULL
4488: FROM oki_dbi_cle_b b
4489: WHERE b.cle_id = s.cle_id ) );
4490: p_recs_processed := SQL%ROWCOUNT;

Line 4488: FROM oki_dbi_cle_b b

4484: , s.hdr_term_cancel_source /* Added for ER 6684955 */
4485: , s.sl_term_cancel_source /* Added for ER 6684955 */
4486: FROM oki_dbi_cle_b_old s
4487: WHERE NOT EXISTS( SELECT NULL
4488: FROM oki_dbi_cle_b b
4489: WHERE b.cle_id = s.cle_id ) );
4490: p_recs_processed := SQL%ROWCOUNT;
4491:
4492: rlog('Number of lines inserted into OKI_DBI_CLE_B : '|| p_recs_processed,2);

Line 4492: rlog('Number of lines inserted into OKI_DBI_CLE_B : '|| p_recs_processed,2);

4488: FROM oki_dbi_cle_b b
4489: WHERE b.cle_id = s.cle_id ) );
4490: p_recs_processed := SQL%ROWCOUNT;
4491:
4492: rlog('Number of lines inserted into OKI_DBI_CLE_B : '|| p_recs_processed,2);
4493: p_recs_processed := l_count + p_recs_processed;
4494: COMMIT ;
4495:
4496: rlog('Updation of summary table OKI_DBI_CLE_B from OKI_DBI_CLE_B_OLD completed: '

Line 4496: rlog('Updation of summary table OKI_DBI_CLE_B from OKI_DBI_CLE_B_OLD completed: '

4492: rlog('Number of lines inserted into OKI_DBI_CLE_B : '|| p_recs_processed,2);
4493: p_recs_processed := l_count + p_recs_processed;
4494: COMMIT ;
4495:
4496: rlog('Updation of summary table OKI_DBI_CLE_B from OKI_DBI_CLE_B_OLD completed: '
4497: ||fnd_date.date_to_displayDT(sysdate), 1) ;
4498:
4499: EXCEPTION
4500: WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN

Line 4587: rlog('Populating Staging Table OKI_DBI_CLE_B_OLD - ' ||fnd_date.date_to_displayDT(sysdate),1) ;

4583: -- Lesters Feedback 5/19/04 force parallel qry
4584:
4585: EXECUTE IMMEDIATE 'alter session force parallel query';
4586:
4587: rlog('Populating Staging Table OKI_DBI_CLE_B_OLD - ' ||fnd_date.date_to_displayDT(sysdate),1) ;
4588: /* Balance logic for OI */
4589: l_balance_logic := nvl(fnd_profile.value('OKI_BAL_IDENT'),'CONTRDATE');
4590: /* Renewal logic for OI */
4591: l_renewal_logic := nvl(fnd_profile.value('OKI_REN_IDENT'),'STANDARD');

Line 4619: INSERT /+ append parallel(old) / into oki_dbi_cle_b_old old

4615: contract being signed and hence, to avoid unsigned contracts showing up in expirations, effective_expire_date
4616: is populated only when date signed is not null */
4617:
4618: --++++++++++++++++++++++
4619: INSERT /*+ append parallel(old) */ into oki_dbi_cle_b_old old
4620: ( chr_id
4621: ,cle_id
4622: ,cle_creation_date
4623: ,inv_organization_id

Line 5291: rlog( 'Number of lines inserted into OKI_DBI_CLE_B_OLD : ' ||

5287:
5288: --Lesters Feedback 5/19/04 disable force parallel qry
5289: EXECUTE IMMEDIATE 'alter session enable parallel query';
5290: l_count := p_recs_processed;
5291: rlog( 'Number of lines inserted into OKI_DBI_CLE_B_OLD : ' ||
5292: to_char(l_count),2);
5293: COMMIT;
5294: rlog('Load of Staging Table OKI_DBI_CLE_B_OLD completed - ' ||
5295: fnd_date.date_to_displayDT(SYSDATE),1) ;

Line 5294: rlog('Load of Staging Table OKI_DBI_CLE_B_OLD completed - ' ||

5290: l_count := p_recs_processed;
5291: rlog( 'Number of lines inserted into OKI_DBI_CLE_B_OLD : ' ||
5292: to_char(l_count),2);
5293: COMMIT;
5294: rlog('Load of Staging Table OKI_DBI_CLE_B_OLD completed - ' ||
5295: fnd_date.date_to_displayDT(SYSDATE),1) ;
5296:
5297: GATHER_TABLE_STATS(TABNAME=>'OKI_DBI_CLE_B_OLD');
5298:

Line 5297: GATHER_TABLE_STATS(TABNAME=>'OKI_DBI_CLE_B_OLD');

5293: COMMIT;
5294: rlog('Load of Staging Table OKI_DBI_CLE_B_OLD completed - ' ||
5295: fnd_date.date_to_displayDT(SYSDATE),1) ;
5296:
5297: GATHER_TABLE_STATS(TABNAME=>'OKI_DBI_CLE_B_OLD');
5298:
5299: rlog('Populating Base Summary Table OKI_DBI_CLE_B - ' || fnd_date.date_to_displayDT(SYSDATE),1) ;
5300:
5301:

Line 5299: rlog('Populating Base Summary Table OKI_DBI_CLE_B - ' || fnd_date.date_to_displayDT(SYSDATE),1) ;

5295: fnd_date.date_to_displayDT(SYSDATE),1) ;
5296:
5297: GATHER_TABLE_STATS(TABNAME=>'OKI_DBI_CLE_B_OLD');
5298:
5299: rlog('Populating Base Summary Table OKI_DBI_CLE_B - ' || fnd_date.date_to_displayDT(SYSDATE),1) ;
5300:
5301:
5302: -- Added enable parallel dml since change in dbms_stats implementation disables
5303: -- the parellel dml call which results in the subsequent insert being serialized.

Line 5307: INSERT /+ APPEND parallel(f)/ INTO OKI_DBI_CLE_B f

5303: -- the parellel dml call which results in the subsequent insert being serialized.
5304: -- No longer need since it is now done in the gather_table_stats procedure
5305: -- EXECUTE IMMEDIATE 'alter session enable parallel dml';
5306:
5307: INSERT /*+ APPEND parallel(f)*/ INTO OKI_DBI_CLE_B f
5308: (
5309: chr_id
5310: , cle_id
5311: , cle_creation_date

Line 5651: oki_dbi_cle_b_old ren_rel

5647: , ren_rel.reminder /* ER#5950128 */
5648: , ren_rel.hdr_term_cancel_source /* ER 6684955 */
5649: , ren_rel.sl_term_cancel_source /* ER 6684955 */
5650: FROM
5651: oki_dbi_cle_b_old ren_rel
5652: , oki_dbi_cle_b_old P
5653: , oki_dbi_cle_b_old rl
5654: WHERE 1=1
5655: AND ren_rel.r_cle_id=rl.cle_id(+)

Line 5652: , oki_dbi_cle_b_old P

5648: , ren_rel.hdr_term_cancel_source /* ER 6684955 */
5649: , ren_rel.sl_term_cancel_source /* ER 6684955 */
5650: FROM
5651: oki_dbi_cle_b_old ren_rel
5652: , oki_dbi_cle_b_old P
5653: , oki_dbi_cle_b_old rl
5654: WHERE 1=1
5655: AND ren_rel.r_cle_id=rl.cle_id(+)
5656: AND ren_rel.cle_id=P.r_cle_id(+);

Line 5653: , oki_dbi_cle_b_old rl

5649: , ren_rel.sl_term_cancel_source /* ER 6684955 */
5650: FROM
5651: oki_dbi_cle_b_old ren_rel
5652: , oki_dbi_cle_b_old P
5653: , oki_dbi_cle_b_old rl
5654: WHERE 1=1
5655: AND ren_rel.r_cle_id=rl.cle_id(+)
5656: AND ren_rel.cle_id=P.r_cle_id(+);
5657:

Line 5661: rlog('Number of lines inserted into OKI_DBI_CLE_B : ' || to_char(l_count),2);

5657:
5658: p_recs_processed := SQL%ROWCOUNT ;
5659:
5660: l_count := p_recs_processed;
5661: rlog('Number of lines inserted into OKI_DBI_CLE_B : ' || to_char(l_count),2);
5662: COMMIT;
5663: rlog('Load of Base Summary Table OKI_DBI_CLE_B Completed - ' ||
5664: fnd_date.date_to_displayDT(SYSDATE),1) ;
5665:

Line 5663: rlog('Load of Base Summary Table OKI_DBI_CLE_B Completed - ' ||

5659:
5660: l_count := p_recs_processed;
5661: rlog('Number of lines inserted into OKI_DBI_CLE_B : ' || to_char(l_count),2);
5662: COMMIT;
5663: rlog('Load of Base Summary Table OKI_DBI_CLE_B Completed - ' ||
5664: fnd_date.date_to_displayDT(SYSDATE),1) ;
5665:
5666: EXCEPTION
5667: WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN

Line 5670: rlog('Error during direct_load: Insert into OKI_DBI_CLE_B Table Failed' , 0);

5666: EXCEPTION
5667: WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5668: RAISE;
5669: WHEN OTHERS THEN
5670: rlog('Error during direct_load: Insert into OKI_DBI_CLE_B Table Failed' , 0);
5671: rlog(sqlerrm ||' '||sqlcode, 0);
5672: bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
5673: fnd_message.set_name( application => 'FND'
5674: , name => 'CRM-DEBUG ERROR' ) ;

Line 5686: Stage 1) Load Staging table oki_dbi_cle_b_old with contracts info

5682: Procedure: worker
5683: Description: This Procedure will be called by spawned request.
5684: Hence value of global variables will be lost.
5685: Here we will decide which stage it should load.
5686: Stage 1) Load Staging table oki_dbi_cle_b_old with contracts info
5687: Stage 2) Load RHS of oki_dbi_cle_b_old with its renewal information
5688: Stage 3) Load LHS of oki_dbi_cle_b_old with its original information
5689: Parameters:
5690: errbuf -Mandatory out parameter containing error message to be passed back to the concurrent manager

Line 5687: Stage 2) Load RHS of oki_dbi_cle_b_old with its renewal information

5683: Description: This Procedure will be called by spawned request.
5684: Hence value of global variables will be lost.
5685: Here we will decide which stage it should load.
5686: Stage 1) Load Staging table oki_dbi_cle_b_old with contracts info
5687: Stage 2) Load RHS of oki_dbi_cle_b_old with its renewal information
5688: Stage 3) Load LHS of oki_dbi_cle_b_old with its original information
5689: Parameters:
5690: errbuf -Mandatory out parameter containing error message to be passed back to the concurrent manager
5691: retcode-Mandatory out parameter containing the Oracle error number to be passed back to the concurrent manager

Line 5688: Stage 3) Load LHS of oki_dbi_cle_b_old with its original information

5684: Hence value of global variables will be lost.
5685: Here we will decide which stage it should load.
5686: Stage 1) Load Staging table oki_dbi_cle_b_old with contracts info
5687: Stage 2) Load RHS of oki_dbi_cle_b_old with its renewal information
5688: Stage 3) Load LHS of oki_dbi_cle_b_old with its original information
5689: Parameters:
5690: errbuf -Mandatory out parameter containing error message to be passed back to the concurrent manager
5691: retcode-Mandatory out parameter containing the Oracle error number to be passed back to the concurrent manager
5692: p_worker_no- current worker number

Line 5730: AND object_name = 'OKI_DBI_CLE_B_OLD';

5726: l_completed_cnt,
5727: l_total_cnt
5728: FROM OKI_DBI_WORKER_STATUS
5729: WHERE 1=1
5730: AND object_name = 'OKI_DBI_CLE_B_OLD';
5731:
5732: IF (l_failed_cnt > 0) THEN
5733: rlog('Another worker have errored out. Stop processing.',2);
5734: ELSIF (l_unassigned_cnt = 0) THEN

Line 5743: WHERE object_name = 'OKI_DBI_CLE_B_OLD'

5739:
5740: UPDATE OKI_DBI_WORKER_STATUS
5741: SET status = 'IN PROCESS'
5742: ,phase = p_phase
5743: WHERE object_name = 'OKI_DBI_CLE_B_OLD'
5744: AND worker_number = p_worker_no
5745: AND STATUS ='UNASSIGNED';
5746:
5747: COMMIT;

Line 5769: -- rlog('Total No of Records updated in OKI_DBI_CLE_B_OLD using Worker '||

5765: ELSE
5766: RAISE G_CHILD_PROCESS_ISSUE;
5767: END IF;
5768:
5769: -- rlog('Total No of Records updated in OKI_DBI_CLE_B_OLD using Worker '||
5770: -- p_worker_no || ' is '|| l_recs_processed,3);
5771: COMMIT;
5772:
5773: UPDATE OKI_DBI_WORKER_STATUS

Line 5775: WHERE object_name = 'OKI_DBI_CLE_B_OLD'

5771: COMMIT;
5772:
5773: UPDATE OKI_DBI_WORKER_STATUS
5774: SET status = 'COMPLETED'
5775: WHERE object_name = 'OKI_DBI_CLE_B_OLD'
5776: AND status = 'IN PROCESS'
5777: AND worker_number = p_worker_no;
5778: COMMIT;
5779:

Line 5786: WHERE object_name = 'OKI_DBI_CLE_B_OLD'

5782: retcode := -1;
5783:
5784: UPDATE OKI_DBI_WORKER_STATUS
5785: SET status = 'FAILED'
5786: WHERE object_name = 'OKI_DBI_CLE_B_OLD'
5787: AND status = 'IN PROCESS'
5788: AND worker_number = p_worker_no;
5789:
5790: COMMIT;

Line 5903: FROM oki_dbi_cle_b_old ch

5899: (SELECT /*+ ordered index(ch) use_nl(al) */
5900: al.cle_id
5901: , al.r_cle_id
5902: , MOD(ROWNUM-1,p_no_of_workers)+1 worker_number
5903: FROM oki_dbi_cle_b_old ch
5904: , oki_dbi_cle_b al
5905: WHERE ch.worker_number = p_worker
5906: AND ch.cle_id = al.r_cle_id);
5907:

Line 5904: , oki_dbi_cle_b al

5900: al.cle_id
5901: , al.r_cle_id
5902: , MOD(ROWNUM-1,p_no_of_workers)+1 worker_number
5903: FROM oki_dbi_cle_b_old ch
5904: , oki_dbi_cle_b al
5905: WHERE ch.worker_number = p_worker
5906: AND ch.cle_id = al.r_cle_id);
5907:
5908: l_count := SQL%ROWCOUNT;

Line 5934: FROM oki_dbi_cle_b_old ch

5930: (SELECT /*+ ordered index(ch) use_nl(al) */
5931: al.cle_id p_cle_id
5932: , al.r_cle_id cle_id
5933: , MOD(ROWNUM-1,p_no_of_workers)+1 worker_number
5934: FROM oki_dbi_cle_b_old ch
5935: , oki_dbi_cle_b al
5936: WHERE ch.cle_id = al.cle_id
5937: AND al.r_cle_id IS NOT NULL
5938: AND ch.worker_number = p_worker)S);

Line 5935: , oki_dbi_cle_b al

5931: al.cle_id p_cle_id
5932: , al.r_cle_id cle_id
5933: , MOD(ROWNUM-1,p_no_of_workers)+1 worker_number
5934: FROM oki_dbi_cle_b_old ch
5935: , oki_dbi_cle_b al
5936: WHERE ch.cle_id = al.cle_id
5937: AND al.r_cle_id IS NOT NULL
5938: AND ch.worker_number = p_worker)S);
5939:

Line 5966: oki_dbi_cle_b_old which have been identified for update

5962:
5963: /*******************************************************************************
5964: Procedure: update_staging
5965: Description: This Function is used to insert covered lines into
5966: oki_dbi_cle_b_old which have been identified for update
5967: in identifying renewal relationship change
5968: Parameters:
5969: p_worker - current worker number
5970: p_no_of_workers - the total number of workers requested by the user

Line 5984: rlog('Populating staging table OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B: '

5980:
5981: BEGIN
5982:
5983:
5984: rlog('Populating staging table OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B: '
5985: ||fnd_date.date_to_displayDT(sysdate), 1) ;
5986:
5987: INSERT INTO oki_dbi_cle_b_old
5988: ( chr_id

Line 5987: INSERT INTO oki_dbi_cle_b_old

5983:
5984: rlog('Populating staging table OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B: '
5985: ||fnd_date.date_to_displayDT(sysdate), 1) ;
5986:
5987: INSERT INTO oki_dbi_cle_b_old
5988: ( chr_id
5989: , cle_id
5990: , cle_creation_date
5991: , inv_organization_id

Line 6333: oki_dbi_cle_b b

6329: , b.reminder /* Added part of ER#5950128 */
6330: , b.HDR_TERM_CANCEL_SOURCE /* ER 6684955 */
6331: , b.SL_TERM_CANCEL_SOURCE /* ER 6684955 */
6332: FROM oki_dbi_cle_del ren_inc ,
6333: oki_dbi_cle_b b
6334: WHERE b.cle_id = ren_inc.cle_id
6335: AND ren_inc.worker_number = p_worker
6336: AND NOT EXISTS
6337: (SELECT NULL

Line 6338: FROM oki_dbi_cle_b_old old

6334: WHERE b.cle_id = ren_inc.cle_id
6335: AND ren_inc.worker_number = p_worker
6336: AND NOT EXISTS
6337: (SELECT NULL
6338: FROM oki_dbi_cle_b_old old
6339: WHERE old.cle_id = b.cle_id)
6340: );
6341:
6342: l_count := SQL%ROWCOUNT;

Line 6343: rlog( 'Number of lines inserted into OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B: '

6339: WHERE old.cle_id = b.cle_id)
6340: );
6341:
6342: l_count := SQL%ROWCOUNT;
6343: rlog( 'Number of lines inserted into OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B: '
6344: ||TO_CHAR(l_count),2);
6345: rlog('Load of staging table OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B complete: '
6346: ||fnd_date.date_to_displayDT(sysdate), 1) ;
6347: COMMIT;

Line 6345: rlog('Load of staging table OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B complete: '

6341:
6342: l_count := SQL%ROWCOUNT;
6343: rlog( 'Number of lines inserted into OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B: '
6344: ||TO_CHAR(l_count),2);
6345: rlog('Load of staging table OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B complete: '
6346: ||fnd_date.date_to_displayDT(sysdate), 1) ;
6347: COMMIT;
6348:
6349: p_recs_processed := NVL(l_count,0);

Line 6446: oki_dbi_cle_b cle

6442: cle.cle_id,
6443: NULL p_cle_id,
6444: MOD(ROWNUM-1,p_no_of_workers)+1 worker_number
6445: FROM oki_dbi_prev_inc del,
6446: oki_dbi_cle_b cle
6447: WHERE cle.p_cle_id = del.p_cle_id
6448: AND cle.cle_id <> del.cle_id
6449: AND cle.p_cle_id IS NOT NULL
6450: AND del.worker_number = p_worker) s1