1: PACKAGE BODY CSM_AD_SRCH_REGION_EVENT_PKG AS
2: /* $Header: csmeasrb.pls 120.5 2008/02/22 08:51:32 trajasek noship $ */
3: /*** Globals ***/
4: g_ad_sch_region_acc_tab_name CONSTANT VARCHAR2(30) := 'CSM_AD_SEARCH_REGION_ACC';
5: g_ad_sch_region_table_name CONSTANT VARCHAR2(30) := 'CSM_AD_SEARCH_REGION_VIEW';
6: g_ad_sch_region_seq_name CONSTANT VARCHAR2(30) := 'CSM_AD_SEARCH_REGION_ACC_S' ;
7: g_ad_sch_region_pubi_name CONSTANT VARCHAR2(30) := 'CSM_AD_SEARCH_REGION';
8:
9: g_ad_sch_region_pkg_name CONSTANT VARCHAR2(30) := 'CSM_AD_SRCH_REGION_EVENT_PKG';
39: CURSOR c_delete IS
40: SELECT ACC.ACCESS_ID,
41: ACC.ID
42: FROM CSM_AD_SEARCH_REGION_ACC ACC
43: WHERE NOT EXISTS (SELECT 1 FROM CSM_AD_SEARCH_REGION_VIEW B
44: WHERE B.ID=ACC.ID
45: AND EXISTS (SELECT 1 FROM CSM_AD_SEARCH_TITLE_VIEW C
46: WHERE C.ID= B.SEARCH_TYPE_ID));
47:
48: CURSOR c_update(b_lastrundate DATE) IS
49: SELECT ACC.ACCESS_ID,
50: ACC.ID
51: FROM CSM_AD_SEARCH_REGION_ACC ACC
52: WHERE EXISTS (SELECT 1 FROM CSM_AD_SEARCH_REGION_VIEW B
53: WHERE B.ID=ACC.ID
54: AND B.LAST_UPDATE_DATE > b_lastrundate);
55:
56:
56:
57: CURSOR c_insert IS
58: SELECT 1 ACCESS_ID,
59: B.ID
60: FROM CSM_AD_SEARCH_REGION_VIEW B
61: WHERE NOT EXISTS (SELECT 1 FROM CSM_AD_SEARCH_REGION_ACC ACC
62: WHERE ACC.ID=B.ID
63: OR NOT EXISTS(SELECT 1 FROM CSM_AD_SEARCH_TITLE_VIEW C
64: WHERE C.ID= B.SEARCH_TYPE_ID));
76:
77: TYPE l_conc_rec IS RECORD
78: (
79: ACCESS_ID CSM_AD_SEARCH_REGION_ACC.ACCESS_ID%TYPE,
80: ID CSM_AD_SEARCH_REGION_VIEW.ID%TYPE
81: );
82:
83: TYPE l_tab_type IS TABLE OF l_conc_rec
84: INDEX BY BINARY_INTEGER;