[Home] [Help]
816: l_status VARCHAR2(50);
817:
818: cursor last_refresh_date_cursor(p_obj_name varchar2, l_status varchar2) is
819: select period_from, period_to
820: from bis_refresh_log
821: where object_name = p_obj_name and status=l_status
822: and last_update_date =( select max(last_update_date)
823: from bis_refresh_log
824: where object_name= p_obj_name and status=l_status ) ;
819: select period_from, period_to
820: from bis_refresh_log
821: where object_name = p_obj_name and status=l_status
822: and last_update_date =( select max(last_update_date)
823: from bis_refresh_log
824: where object_name= p_obj_name and status=l_status ) ;
825:
826:
827: begin
888: END IF;
889:
890:
891: SELECT MIN(period_from) INTO l_date
892: FROM bis_refresh_log
893: WHERE object_name = p_object_name AND
894: status=l_status AND
895: last_update_date =
896: (SELECT MIN(last_update_date)
893: WHERE object_name = p_object_name AND
894: status=l_status AND
895: last_update_date =
896: (SELECT MIN(last_update_date)
897: FROM bis_refresh_log
898: WHERE object_name= p_object_name AND
899: status=l_status ) ;
900:
901: IF (l_date IS NULL) THEN
946: END IF;
947:
948:
949: SELECT count(*) INTO l_num
950: FROM bis_refresh_log
951: WHERE object_name = p_object_name
952: and status=l_status ;
953:
954:
1531: --execute immediate 'Truncate table BIL_BI_DENLOG_STG';
1532: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_DENLOG_TMP');
1533:
1534: -- Added by TR (to populate the correct period_to and period_from dates in Attribute
1535: -- columns of bis_refresh_log)
1536: SELECT TO_CHAR(MIN(snap_date),'DD-MM-YYYY')
1537: INTO l_period_from
1538: FROM bil_bi_pipec_f;
1539:
1544:
1545:
1546: IF l_curr_coll_start between l_failure_from+1 and l_failure_to THEN
1547:
1548: INSERT INTO bis_refresh_log
1549: (
1550: Request_id,
1551: Concurrent_id,
1552: Object_name,
1578: sysdate,
1579: g_login_id,
1580: g_user_id
1581: );
1582: INSERT INTO bis_refresh_log
1583: (
1584: Request_id,
1585: Concurrent_id,
1586: Object_name,
1618: l_period_to
1619: );
1620:
1621: ELSE
1622: INSERT INTO bis_refresh_log
1623: (
1624: Request_id,
1625: Concurrent_id,
1626: Object_name,
2046:
2047: END IF; -- initial/incr gap
2048:
2049: -- Added by TR (to populate the correct period_to and period_from dates in Attribute
2050: -- columns of bis_refresh_log)
2051: SELECT TO_CHAR(MIN(snap_date),'DD-MM-YYYY')
2052: INTO l_period_from
2053: FROM bil_bi_pipec_f;
2054:
2056: INTO l_period_to
2057: FROM bil_bi_pipec_f;
2058:
2059: IF l_curr_coll_start BETWEEN l_last_run+2 and l_date-1 THEN
2060: --Insert into bis_refresh_log for both tables: bil_bi_pipeline_f and bil_bi_pipec_f
2061:
2062:
2063: INSERT INTO bis_refresh_log
2064: (
2059: IF l_curr_coll_start BETWEEN l_last_run+2 and l_date-1 THEN
2060: --Insert into bis_refresh_log for both tables: bil_bi_pipeline_f and bil_bi_pipec_f
2061:
2062:
2063: INSERT INTO bis_refresh_log
2064: (
2065: Request_id,
2066: Concurrent_id,
2067: Object_name,
2096: );
2097: commit;
2098:
2099:
2100: INSERT INTO bis_refresh_log
2101: (
2102: Request_id,
2103: Concurrent_id,
2104: Object_name,
2141: -- l_coll_start is before the beggining of the gap
2142: -- so innsert only in the current pipeline table
2143: --this is the only other possible case, since we are always
2144: --collecting up to sysdate (or opty base sumry last run)
2145: INSERT INTO bis_refresh_log
2146: (
2147: Request_id,
2148: Concurrent_id,
2149: Object_name,
2729: l_period_to
2730: FROM bil_bi_pipec_f;
2731:
2732:
2733: INSERT INTO bis_refresh_log
2734: (
2735: Request_id,
2736: Concurrent_id,
2737: Object_name,