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.BIM_EVENT_FACTS dependencies on BIM_R_EVEN_DAILY_FACTS

Line 467: l_table_name :='bim_r_even_daily_facts';

463: if l_profile is null then
464: l_profile :=0;
465: end if;
466:
467: l_table_name :='bim_r_even_daily_facts';
468:
469: --dbms_output.put_line(' l_profile'||l_profile);
470: --dbms_output.put_line(' start_time'||p_start_datel);
471: --dbms_output.put_line(' end time'||p_end_datel);

Line 510: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';

506: END LOOP;
507:
508: BEGIN
509:
510: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
511: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_weekly_facts nologging';
512:
513: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 1000';
514:

Line 513: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 1000';

509:
510: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
511: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_weekly_facts nologging';
512:
513: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 1000';
514:
515: /*This insert statement is getting transactions book of order, leads happen and event header level between p_start_date and p_end_date */
516: ams_utility_pvt.write_conc_log(p_start_datel || ' '|| p_end_datel);
517:

Line 521: bim_r_even_daily_facts EDF(

517:
518: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
519: ams_utility_pvt.write_conc_log('EVENT:FIRST_LOAD: BEFORE FIRST INSERT BEGIN.' || l_temp_msg);
520: INSERT /*+ append parallel(EDF,1) */ INTO
521: bim_r_even_daily_facts EDF(
522: event_daily_transaction_id
523: ,creation_date
524: ,last_update_date
525: ,created_by

Line 576: bim_r_even_daily_facts_s.nextval,

572: ,booked_orders_amt
573: )
574: SELECT
575: /*+ parallel(INNER, 4) */
576: bim_r_even_daily_facts_s.nextval,
577: sysdate,
578: sysdate,
579: l_user_id,
580: l_user_id,

Line 829: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

825: or g.opportunity_amt >0
826: or orders.booked_orders >0
827: or orders.booked_orders_amt >0)inner;
828: commit;
829: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
830: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
831:
832: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
833: ams_utility_pvt.write_conc_log('EVENT:FIRST_LOAD: AFTER FIRST INSERT.' || l_temp_msg);

Line 830: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';

826: or orders.booked_orders >0
827: or orders.booked_orders_amt >0)inner;
828: commit;
829: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
830: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
831:
832: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
833: ams_utility_pvt.write_conc_log('EVENT:FIRST_LOAD: AFTER FIRST INSERT.' || l_temp_msg);
834:

Line 837: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

833: ams_utility_pvt.write_conc_log('EVENT:FIRST_LOAD: AFTER FIRST INSERT.' || l_temp_msg);
834:
835: EXCEPTION
836: WHEN OTHERS THEN
837: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
838: x_return_status := FND_API.G_RET_STS_ERROR;
839: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
840: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
841: FND_MSG_PUB.Add;

Line 854: bim_r_even_daily_facts EDF(

850:
851: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
852: ams_utility_pvt.write_conc_log('EVENT:FIRST_LOAD: BEFORE SECOND INSERT BEGIN.' || l_temp_msg);
853: INSERT /*+ append parallel(EDF,1) */ INTO
854: bim_r_even_daily_facts EDF(
855: event_daily_transaction_id
856: ,creation_date
857: ,last_update_date
858: ,created_by

Line 909: bim_r_even_daily_facts_s.nextval,

905: ,booked_orders_amt
906: )
907: SELECT
908: /*+ parallel(INNER, 4) */
909: bim_r_even_daily_facts_s.nextval,
910: sysdate,
911: sysdate,
912: l_user_id,
913: l_user_id,

Line 1165: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

1161: ams_utility_pvt.write_conc_log('EVENT:FIRST_LOAD: AFTER SECOND INSERT.' || l_temp_msg);
1162:
1163: commit;
1164: --dbms_output.put_line('after insert row count:'||SQL%ROWCOUNT);
1165: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
1166: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
1167: EXCEPTION
1168: WHEN OTHERS THEN
1169: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

Line 1166: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';

1162:
1163: commit;
1164: --dbms_output.put_line('after insert row count:'||SQL%ROWCOUNT);
1165: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
1166: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
1167: EXCEPTION
1168: WHEN OTHERS THEN
1169: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
1170: --dbms_output.put_line('even_daily:'||sqlerrm(sqlcode));

Line 1169: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

1165: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
1166: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
1167: EXCEPTION
1168: WHEN OTHERS THEN
1169: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
1170: --dbms_output.put_line('even_daily:'||sqlerrm(sqlcode));
1171: x_return_status := FND_API.G_RET_STS_ERROR;
1172: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
1173: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);

Line 1187: bim_r_even_daily_facts EDF(

1183: p_start_date and p_end_date parameter*/
1184: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
1185: ams_utility_pvt.write_conc_log('EVENT:FIRST_LOAD: BEFORE THIRD INSERT BEGIN.' || l_temp_msg);
1186: INSERT INTO /*+ append parallel(EDF,1) */
1187: bim_r_even_daily_facts EDF(
1188: event_daily_transaction_id
1189: ,creation_date
1190: ,last_update_date
1191: ,created_by

Line 1240: bim_r_even_daily_facts_s.nextval,

1236: ,booked_orders
1237: ,booked_orders_amt
1238: )
1239: SELECT /*+ parallel(INNER, 4) */
1240: bim_r_even_daily_facts_s.nextval,
1241: sysdate,
1242: sysdate,
1243: l_user_id,
1244: l_user_id,

Line 1466: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

1462: ams_utility_pvt.write_conc_log('EVENT:FIRST_LOAD: AFTER THIRD INSERT.' || l_temp_msg);
1463:
1464: commit;
1465:
1466: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
1467: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
1468: EXCEPTION
1469: WHEN OTHERS THEN
1470: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

Line 1467: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';

1463:
1464: commit;
1465:
1466: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
1467: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
1468: EXCEPTION
1469: WHEN OTHERS THEN
1470: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
1471: x_return_status := FND_API.G_RET_STS_ERROR;

Line 1470: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

1466: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
1467: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
1468: EXCEPTION
1469: WHEN OTHERS THEN
1470: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
1471: x_return_status := FND_API.G_RET_STS_ERROR;
1472: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
1473: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
1474: FND_MSG_PUB.Add;

Line 1487: --Insert into bim_r_even_daily_facts for one-off event offer level

1483: for the one-off event offers */
1484:
1485: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
1486: ams_utility_pvt.write_conc_log('EVENT:FIRST_LOAD: BEFORE SECOND INSERT.' || l_temp_msg);
1487: --Insert into bim_r_even_daily_facts for one-off event offer level
1488:
1489: INSERT INTO
1490: bim_r_even_daily_facts ewf(
1491: event_daily_transaction_id

Line 1490: bim_r_even_daily_facts ewf(

1486: ams_utility_pvt.write_conc_log('EVENT:FIRST_LOAD: BEFORE SECOND INSERT.' || l_temp_msg);
1487: --Insert into bim_r_even_daily_facts for one-off event offer level
1488:
1489: INSERT INTO
1490: bim_r_even_daily_facts ewf(
1491: event_daily_transaction_id
1492: ,creation_date
1493: ,last_update_date
1494: ,created_by

Line 1543: bim_r_even_daily_facts_s.nextval,

1539: ,booked_orders
1540: ,booked_orders_amt
1541: )
1542: SELECT
1543: bim_r_even_daily_facts_s.nextval,
1544: sysdate,
1545: sysdate,
1546: l_user_id,
1547: l_user_id,

Line 1917: bim_r_even_daily_facts EDF(

1913:
1914: BEGIN
1915:
1916: INSERT INTO
1917: bim_r_even_daily_facts EDF(
1918: event_daily_transaction_id
1919: ,creation_date
1920: ,last_update_date
1921: ,created_by

Line 1971: bim_r_even_daily_facts_s.nextval

1967: ,booked_orders
1968: ,booked_orders_amt
1969: )
1970: SELECT
1971: bim_r_even_daily_facts_s.nextval
1972: ,sysdate
1973: ,sysdate
1974: ,l_user_id
1975: ,l_user_id

Line 2062: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

2058:
2059: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
2060: ams_utility_pvt.write_conc_log('EVENT:FIRST_LOAD: AFTER registration INSERT.' || l_temp_msg);
2061: COMMIT;
2062: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
2063: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
2064: EXCEPTION
2065: WHEN OTHERS THEN
2066: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

Line 2063: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';

2059: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
2060: ams_utility_pvt.write_conc_log('EVENT:FIRST_LOAD: AFTER registration INSERT.' || l_temp_msg);
2061: COMMIT;
2062: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
2063: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
2064: EXCEPTION
2065: WHEN OTHERS THEN
2066: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
2067: --dbms_output.put_line('even_update:'||sqlerrm(sqlcode));

Line 2066: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

2062: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
2063: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
2064: EXCEPTION
2065: WHEN OTHERS THEN
2066: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
2067: --dbms_output.put_line('even_update:'||sqlerrm(sqlcode));
2068: x_return_status := FND_API.G_RET_STS_ERROR;
2069: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
2070: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);

Line 2107: bim_r_even_daily_facts EDF(

2103:
2104: BEGIN
2105:
2106: INSERT INTO
2107: bim_r_even_daily_facts EDF(
2108: event_daily_transaction_id
2109: ,creation_date
2110: ,last_update_date
2111: ,created_by

Line 2161: bim_r_even_daily_facts_s.nextval,

2157: ,booked_orders
2158: ,booked_orders_amt
2159: )
2160: SELECT
2161: bim_r_even_daily_facts_s.nextval,
2162: sysdate,
2163: sysdate,
2164: l_user_id,
2165: l_user_id,

Line 2257: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

2253:
2254: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
2255: ams_utility_pvt.write_conc_log('EVENT:FIRST_LOAD: AFTER registration INSERT.' || l_temp_msg);
2256: COMMIT;
2257: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
2258: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
2259: EXCEPTION
2260: WHEN OTHERS THEN
2261: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

Line 2258: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';

2254: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
2255: ams_utility_pvt.write_conc_log('EVENT:FIRST_LOAD: AFTER registration INSERT.' || l_temp_msg);
2256: COMMIT;
2257: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
2258: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
2259: EXCEPTION
2260: WHEN OTHERS THEN
2261: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
2262: --dbms_output.put_line('even_update:'||sqlerrm(sqlcode));

Line 2261: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

2257: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
2258: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
2259: EXCEPTION
2260: WHEN OTHERS THEN
2261: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
2262: --dbms_output.put_line('even_update:'||sqlerrm(sqlcode));
2263: x_return_status := FND_API.G_RET_STS_ERROR;
2264: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
2265: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);

Line 2273: DBMS_STATS.gather_table_stats('BIM','BIM_R_EVEN_DAILY_FACTS', estimate_percent => 5,

2269: END;
2270:
2271: -- analyze the bim_r_event_daily_facts with dbms_stats
2272: BEGIN
2273: DBMS_STATS.gather_table_stats('BIM','BIM_R_EVEN_DAILY_FACTS', estimate_percent => 5,
2274: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
2275: END;
2276:
2277: /*This update statement is to updating forecasted_cost, actual_cost, forecasted_revenue, and actual_revenue that event header happened between p_start_date and p_end_date parameter*/

Line 2288: FROM bim_r_even_daily_facts

2284: l_oneoff_forecasted_revenue NUMBER;
2285:
2286: CURSOR event_dates IS
2287: SELECT event_header_id,event_offer_id, max(transaction_create_date) max_date
2288: FROM bim_r_even_daily_facts
2289: GROUP BY event_header_id,event_offer_id;
2290:
2291: CURSOR one_off_cost_revenue IS
2292: SELECT event_header_id,event_offer_id, max(transaction_create_date) max_date

Line 2293: FROM bim_r_even_daily_facts

2289: GROUP BY event_header_id,event_offer_id;
2290:
2291: CURSOR one_off_cost_revenue IS
2292: SELECT event_header_id,event_offer_id, max(transaction_create_date) max_date
2293: FROM bim_r_even_daily_facts
2294: WHERE event_header_id = -999
2295: GROUP BY event_header_id,event_offer_id;
2296:
2297: BEGIN

Line 2344: UPDATE bim_r_even_daily_facts

2340: END;
2341:
2342: BEGIN
2343:
2344: UPDATE bim_r_even_daily_facts
2345: SET actual_cost = l_actual_costh
2346: ,forecasted_cost = l_forecasted_costh
2347: ,actual_revenue = l_actual_revenueh
2348: ,forecasted_revenue = l_forecasted_revenueh

Line 2374: FROM bim_r_even_daily_facts

2370: l_oneoff_forecasted_revenue NUMBER;
2371:
2372: CURSOR one_off_cost_revenue IS
2373: SELECT event_header_id,event_offer_id, max(transaction_create_date) max_date
2374: FROM bim_r_even_daily_facts
2375: WHERE event_header_id = -999
2376: GROUP BY event_header_id,event_offer_id;
2377:
2378: BEGIN

Line 2422: UPDATE bim_r_even_daily_facts

2418: END;
2419:
2420:
2421: BEGIN
2422: UPDATE bim_r_even_daily_facts
2423: SET actual_cost = l_oneoff_actual_cost
2424: ,forecasted_cost = l_oneoff_forecasted_cost
2425: ,actual_revenue = l_oneoff_actual_revenue
2426: ,forecasted_revenue = l_oneoff_forecasted_revenue

Line 2609: FROM bim_r_even_daily_facts

2605: ,qtr
2606: ,year
2607: ,sum(booked_orders) booked_orders
2608: ,sum(booked_orders_amt) booked_orders_amt
2609: FROM bim_r_even_daily_facts
2610: GROUP BY event_offer_id
2611: ,load_date
2612: ,event_header_id
2613: ,parent_id

Line 2636: /* If there are some data insert into bim_r_even_daily_facts and bim_r_even_weekly_facts, then insert a record into bim_rep_history*/

2632: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
2633: ams_utility_pvt.write_conc_log('EVENT:FIRST_LOAD: AFTER INSERT INTO WEEKLY FACTS.' || l_temp_msg);
2634: COMMIT;
2635:
2636: /* If there are some data insert into bim_r_even_daily_facts and bim_r_even_weekly_facts, then insert a record into bim_rep_history*/
2637:
2638: --IF SQL%ROWCOUNT >0 THEN
2639:
2640: LOG_HISTORY(

Line 2651: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

2647:
2648: );
2649: --END IF;
2650:
2651: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
2652: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
2653:
2654: ams_utility_pvt.write_conc_log('End of Events Facts Program -- First Load');
2655:

Line 2652: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';

2648: );
2649: --END IF;
2650:
2651: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
2652: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
2653:
2654: ams_utility_pvt.write_conc_log('End of Events Facts Program -- First Load');
2655:
2656: EXCEPTION

Line 2815: l_table_name :='bim_r_even_daily_facts';

2811: -- Debug Message
2812: AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
2813:
2814: BEGIN
2815: l_table_name :='bim_r_even_daily_facts';
2816:
2817: IF p_api_version_number = 1 THEN
2818: l_min_start_date := trunc(p_start_datel);
2819: ELSE

Line 2827: --Insert into bim_r_even_daily_facts on event offer level

2823: END IF;
2824:
2825: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
2826: ams_utility_pvt.write_conc_log('EVENT:LOAD: BEFORE FIRST INSERT.' || l_temp_msg);
2827: --Insert into bim_r_even_daily_facts on event offer level
2828: INSERT INTO
2829: bim_r_even_daily_facts ewf(
2830: event_daily_transaction_id
2831: ,creation_date

Line 2829: bim_r_even_daily_facts ewf(

2825: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
2826: ams_utility_pvt.write_conc_log('EVENT:LOAD: BEFORE FIRST INSERT.' || l_temp_msg);
2827: --Insert into bim_r_even_daily_facts on event offer level
2828: INSERT INTO
2829: bim_r_even_daily_facts ewf(
2830: event_daily_transaction_id
2831: ,creation_date
2832: ,last_update_date
2833: ,created_by

Line 2882: bim_r_even_daily_facts_s.nextval,

2878: ,booked_orders
2879: ,booked_orders_amt
2880: )
2881: select
2882: bim_r_even_daily_facts_s.nextval,
2883: sysdate,
2884: sysdate,
2885: l_user_id,
2886: l_user_id,

Line 3299: --Insert into bim_r_even_daily_facts for one-off event offer level

3295: for the one-off event offers */
3296:
3297: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
3298: ams_utility_pvt.write_conc_log('EVENT:LOAD: BEFORE SECOND INSERT.' || l_temp_msg);
3299: --Insert into bim_r_even_daily_facts for one-off event offer level
3300:
3301: INSERT INTO
3302: bim_r_even_daily_facts ewf(
3303: event_daily_transaction_id

Line 3302: bim_r_even_daily_facts ewf(

3298: ams_utility_pvt.write_conc_log('EVENT:LOAD: BEFORE SECOND INSERT.' || l_temp_msg);
3299: --Insert into bim_r_even_daily_facts for one-off event offer level
3300:
3301: INSERT INTO
3302: bim_r_even_daily_facts ewf(
3303: event_daily_transaction_id
3304: ,creation_date
3305: ,last_update_date
3306: ,created_by

Line 3355: bim_r_even_daily_facts_s.nextval,

3351: ,booked_orders
3352: ,booked_orders_amt
3353: )
3354: SELECT
3355: bim_r_even_daily_facts_s.nextval,
3356: sysdate,
3357: sysdate,
3358: l_user_id,
3359: l_user_id,

Line 3779: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

3775:
3776: -----------------------------------------------------------------------
3777:
3778: --dbms_output.put_line('after insert row count:'||SQL%ROWCOUNT);
3779: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
3780: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
3781: EXCEPTION
3782: WHEN OTHERS THEN
3783: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

Line 3780: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';

3776: -----------------------------------------------------------------------
3777:
3778: --dbms_output.put_line('after insert row count:'||SQL%ROWCOUNT);
3779: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
3780: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
3781: EXCEPTION
3782: WHEN OTHERS THEN
3783: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
3784: --dbms_output.put_line('even_daily:'||sqlerrm(sqlcode));

Line 3783: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

3779: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
3780: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
3781: EXCEPTION
3782: WHEN OTHERS THEN
3783: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
3784: --dbms_output.put_line('even_daily:'||sqlerrm(sqlcode));
3785: x_return_status := FND_API.G_RET_STS_ERROR;
3786: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
3787: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);

Line 3796: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';

3792:
3793:
3794: BEGIN
3795:
3796: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
3797: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_weekly_facts nologging';
3798:
3799: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 1000';
3800:

Line 3799: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 1000';

3795:
3796: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_daily_facts nologging';
3797: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_weekly_facts nologging';
3798:
3799: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 1000';
3800:
3801: --insert into bim_r_even_daily_facts on event header level
3802:
3803: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');

Line 3801: --insert into bim_r_even_daily_facts on event header level

3797: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_even_weekly_facts nologging';
3798:
3799: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 1000';
3800:
3801: --insert into bim_r_even_daily_facts on event header level
3802:
3803: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
3804: ams_utility_pvt.write_conc_log('EVENT:LOAD: BEFORE SECOND INSERT.' || l_temp_msg);
3805:

Line 3807: bim_r_even_daily_facts ewf(

3803: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
3804: ams_utility_pvt.write_conc_log('EVENT:LOAD: BEFORE SECOND INSERT.' || l_temp_msg);
3805:
3806: INSERT INTO
3807: bim_r_even_daily_facts ewf(
3808: event_daily_transaction_id
3809: ,creation_date
3810: ,last_update_date
3811: ,created_by

Line 3860: bim_r_even_daily_facts_s.nextval,

3856: ,booked_orders
3857: ,booked_orders_amt
3858: )
3859: SELECT
3860: bim_r_even_daily_facts_s.nextval,
3861: sysdate,
3862: sysdate,
3863: l_user_id,
3864: l_user_id,

Line 4255: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';

4251: commit;
4252:
4253: EXCEPTION
4254: WHEN OTHERS THEN
4255: EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_even_daily_facts_s CACHE 20';
4256: --dbms_output.put_line('even_update:'||sqlerrm(sqlcode));
4257: x_return_status := FND_API.G_RET_STS_ERROR;
4258: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
4259: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);

Line 4266: -- analyze the BIM_R_EVEN_daily_facts with dbms_stats

4262: ams_utility_pvt.write_conc_log('EVENT:LOAD: EXCEPTION FOR SECOND INSERT. '||sqlerrm(sqlcode));
4263: RAISE FND_API.G_EXC_ERROR;
4264: END;
4265:
4266: -- analyze the BIM_R_EVEN_daily_facts with dbms_stats
4267: BEGIN
4268: DBMS_STATS.gather_table_stats('BIM','BIM_R_EVEN_DAILY_FACTS', estimate_percent => 5,
4269: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
4270: END;

Line 4268: DBMS_STATS.gather_table_stats('BIM','BIM_R_EVEN_DAILY_FACTS', estimate_percent => 5,

4264: END;
4265:
4266: -- analyze the BIM_R_EVEN_daily_facts with dbms_stats
4267: BEGIN
4268: DBMS_STATS.gather_table_stats('BIM','BIM_R_EVEN_DAILY_FACTS', estimate_percent => 5,
4269: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
4270: END;
4271:
4272:

Line 4431: FROM bim_r_even_daily_facts

4427: ,qtr
4428: ,year
4429: ,sum(booked_orders) booked_orders
4430: ,sum(booked_orders_amt) booked_orders_amt
4431: FROM bim_r_even_daily_facts
4432: -- where load_date between p_start_datel and p_end_datel + 0.9999
4433: GROUP BY event_offer_id
4434: ,load_date
4435: ,event_header_id