[Home] [Help]
PACKAGE BODY: APPS.CSM_COUNTER_PROPERTY_EVENT_PKG
Source
1 PACKAGE BODY CSM_COUNTER_PROPERTY_EVENT_PKG AS
2 /* $Header: csmecptb.pls 120.0 2006/07/24 12:53:33 trajasek noship $ */
3
4 --
5 -- Purpose: USed to downlaod Counter properties for each counter
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -----------------------------------------------------------
9
10 /*** Globals ***/
11 g_count_prp_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_COUNTER_PROPERTIES_ACC';
12 g_count_prp_table_name CONSTANT VARCHAR2(30) := 'CSI_COUNTER_PROPERTIES_B';
13 g_count_prp_seq_name CONSTANT VARCHAR2(30) := 'CSM_COUNTER_PROPERTIES_ACC_S';
14 g_count_prp_pk1_name CONSTANT VARCHAR2(30) := 'COUNTER_PROPERTY_ID';
15 g_count_prp_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
16 CSM_ACC_PKG.t_publication_item_list('CSM_COUNTER_PROPERTIES');
17
18
19 PROCEDURE COUNTER_PROPERTY_INS( p_counter_id NUMBER,
20 p_user_id NUMBER,
21 p_error_msg OUT NOCOPY VARCHAR2,
22 x_return_status IN OUT NOCOPY VARCHAR2)
23 IS
24 --Cursor to insert counter property
25 --this happens if a counter is inserted
26 CURSOR c_property_ins(c_counter_id NUMBER,c_user_id NUMBER)
27 IS
28 SELECT
29 B.COUNTER_PROPERTY_ID ,
30 B.COUNTER_ID
31 FROM
32 CSI_COUNTER_PROPERTIES_B B,
33 CSM_COUNTERS_ACC CACC
34 WHERE B.COUNTER_ID = CACC.COUNTER_ID
35 AND CACC.USER_ID = c_user_id
36 AND CACC.COUNTER_ID = c_counter_id
37 AND NOT EXISTS( SELECT 1
38 FROM CSM_COUNTER_PROPERTIES_ACC PACC
39 WHERE PACC.COUNTER_PROPERTY_ID = B.COUNTER_PROPERTY_ID
40 AND PACC.USER_ID = c_user_id);
41
42 --variable declarations
43 l_err_msg VARCHAR2(4000);
44
45 BEGIN
46 x_return_status := FND_API.G_RET_STS_SUCCESS;
47 l_err_msg := 'Entering CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_INS' || ' for PK ' || to_char(p_counter_id);
48 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_INS', FND_LOG.LEVEL_PROCEDURE);
49
50 -- Open USER IDs loop
51 FOR r_cntr_prp_rec IN c_property_ins(p_counter_id,p_user_id) LOOP
52 -- Call Insert ACC
53 CSM_ACC_PKG.Insert_Acc
54 ( P_PUBLICATION_ITEM_NAMES => g_count_prp_pubi_name
55 ,P_ACC_TABLE_NAME => g_count_prp_acc_table_name
56 ,P_SEQ_NAME => g_count_prp_seq_name
57 ,P_PK1_NAME => g_count_prp_pk1_name
58 ,P_PK1_NUM_VALUE => r_cntr_prp_rec.counter_property_id
59 ,P_USER_ID => p_user_id
60 );
61 END LOOP ; --End USER IDs loop
62
63 l_err_msg := 'Leaving CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_INS' || ' for Counter ' || to_char(p_counter_id);
64 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_INS', FND_LOG.LEVEL_PROCEDURE);
65
66 EXCEPTION
67 WHEN OTHERS THEN
68 IF c_property_ins%ISOPEN then
69 CLOSE c_property_ins;
70 END IF;
71
72 p_error_msg := ' FAILED COUNTER_PROPERTY_INS:' || to_char(p_counter_id);
73 x_return_status := FND_API.G_RET_STS_ERROR;
74 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_INS',FND_LOG.LEVEL_EXCEPTION);
75 RAISE;
76
77 END COUNTER_PROPERTY_INS;
78
79
80 PROCEDURE COUNTER_PROPERTY_UPD( p_counter_id IN NUMBER,
81 p_user_id IN NUMBER,
82 p_error_msg OUT NOCOPY VARCHAR2,
83 x_return_status IN OUT NOCOPY VARCHAR2)
84 IS
85 --Cursors to update counter property
86 --this is called if a counter is updated
87 CURSOR c_property_upd(c_counter_id NUMBER,c_user_id NUMBER)
88 IS
89 SELECT
90 PACC.ACCESS_ID ,
91 PACC.USER_ID
92 FROM
93 CSI_COUNTER_PROPERTIES_B B,
94 CSM_COUNTER_PROPERTIES_ACC PACC
95 WHERE B.COUNTER_PROPERTY_ID = PACC.COUNTER_PROPERTY_ID
96 AND PACC.USER_ID = c_user_id
97 AND B.COUNTER_ID = c_counter_id;
98
99 --variable declarations
100 l_err_msg VARCHAR2(4000);
101 l_markdirty BOOLEAN;
102 l_pub_item_name VARCHAR2(240) := 'CSM_COUNTER_PROPERTIES';
103
104 BEGIN
105 x_return_status := FND_API.G_RET_STS_SUCCESS;
106 l_err_msg := 'Entering CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_UPD' || ' for PK ' || to_char(p_counter_id);
107 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_UPD', FND_LOG.LEVEL_PROCEDURE);
108
109 -- Open USER IDs loop
110 FOR r_cntr_prp_rec IN c_property_upd(p_counter_id,p_user_id) LOOP
111 -- Call Insert ACC
112 l_markdirty := csm_util_pkg.MakeDirtyForUser ( l_pub_item_name
113 , r_cntr_prp_rec.access_id
114 , r_cntr_prp_rec.user_id
115 , 'U'
116 , sysdate);
117 END LOOP ; --End USER IDs loop
118
119 l_err_msg := 'Leaving CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_UPD' || ' for Counter ' || to_char(p_counter_id);
120 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_UPD', FND_LOG.LEVEL_PROCEDURE);
121
122 EXCEPTION
123 WHEN OTHERS THEN
124 IF c_property_upd%ISOPEN then
125 CLOSE c_property_upd;
126 END IF;
127
128 p_error_msg := ' FAILED COUNTER_PROPERTY_UPD FOR COUNTER : ' || to_char(p_counter_id);
129 x_return_status := FND_API.G_RET_STS_ERROR;
130 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_UPD',FND_LOG.LEVEL_EXCEPTION);
131 RAISE;
132
133
134 END COUNTER_PROPERTY_UPD;
135
136 PROCEDURE COUNTER_PROPERTY_DEL( p_counter_id IN NUMBER,
137 p_user_id IN NUMBER,
138 p_error_msg OUT NOCOPY VARCHAR2,
139 x_return_status IN OUT NOCOPY VARCHAR2)
140 IS
141 --Cursor to delete counter property
142 --this is called if a counter is Deleted for a user
143 CURSOR c_property_del(c_counter_id NUMBER,c_user_id NUMBER)
144 IS
145 SELECT
146 PACC.ACCESS_ID ,
147 B.COUNTER_PROPERTY_ID
148 FROM
149 CSI_COUNTER_PROPERTIES_B B,
150 CSM_COUNTER_PROPERTIES_ACC PACC
151 WHERE B.COUNTER_PROPERTY_ID = PACC.COUNTER_PROPERTY_ID
152 AND PACC.USER_ID = c_user_id
153 AND B.COUNTER_ID = c_counter_id;
154
155 --variable declarations
156 l_err_msg VARCHAR2(4000);
157
158 BEGIN
159 x_return_status := FND_API.G_RET_STS_SUCCESS;
160 l_err_msg := 'Entering CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_DEL' || ' for PK ' || to_char(p_counter_id);
161 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_DEL', FND_LOG.LEVEL_PROCEDURE);
162
163 -- Open counter property id loop
164 FOR r_cntr_prp_rec IN c_property_del(p_counter_id,p_user_id) LOOP
165 -- Call Delete ACC
166 CSM_ACC_PKG.Delete_Acc
167 ( P_PUBLICATION_ITEM_NAMES => g_count_prp_pubi_name
168 ,P_ACC_TABLE_NAME => g_count_prp_acc_table_name
169 ,P_PK1_NAME => g_count_prp_pk1_name
170 ,P_PK1_NUM_VALUE => r_cntr_prp_rec.counter_property_id
171 ,P_USER_ID => p_user_id
172 );
173 END LOOP ; --End USER IDs loop
174
175 l_err_msg := 'Leaving CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_DEL' || ' for Counter ' || to_char(p_counter_id);
176 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_DEL', FND_LOG.LEVEL_PROCEDURE);
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 IF c_property_del%ISOPEN then
181 CLOSE c_property_del;
182 END IF;
183
184 p_error_msg := ' FAILED COUNTER_PROPERTY_DEL FOR COUNTER : ' || to_char(p_counter_id);
185 x_return_status := FND_API.G_RET_STS_ERROR;
186 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_DEL',FND_LOG.LEVEL_EXCEPTION);
187 RAISE;
188
189 END COUNTER_PROPERTY_DEL;
190
191 END CSM_COUNTER_PROPERTY_EVENT_PKG; -- Package spec of CSM_COUNTER_PROPERTY_EVENT_PKG