[Home] [Help]
PACKAGE BODY: APPS.CSM_DEBRIEF_HEADER_EVENT_PKG
Source
1 PACKAGE BODY csm_debrief_header_event_pkg AS
2 /* $Header: csmedbhb.pls 120.1 2005/07/24 23:45:04 trajasek noship $*/
3
4 --
5 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
6 -- directory of SQL Navigator
7 --
8 -- Purpose: Briefly explain the functionality of the package body
9 --
10 -- MODIFICATION HISTORY
11 -- Person Date Comments
12 -- --------- ------ ------------------------------------------
13 -- Enter procedure, function bodies as shown below
14
15 g_debrief_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_DEBRIEF_HEADERS_ACC';
16 g_debrief_table_name CONSTANT VARCHAR2(30) := 'CSF_DEBRIEF_HEADERS';
17 g_debrief_seq_name CONSTANT VARCHAR2(30) := 'CSM_DEBRIEF_HEADERS_ACC_S';
18 g_debrief_pk1_name CONSTANT VARCHAR2(30) := 'DEBRIEF_HEADER_ID';
19 g_debrief_pubi_name CSM_ACC_PKG.t_publication_item_list;
20
21 PROCEDURE DEBRIEF_HEADER_INS_INIT(p_debrief_header_id IN NUMBER, p_h_user_id IN NUMBER,
22 p_flow_type IN VARCHAR2)
23 IS
24 l_sqlerrno VARCHAR2(20);
25 l_sqlerrmsg VARCHAR2(4000);
26 l_error_msg VARCHAR2(4000);
27 l_return_status VARCHAR2(2000);
28 l_user_id NUMBER;
29
30 CURSOR l_csm_debrfHdInsInit_csr (p_debrief_header_id csf_debrief_headers.debrief_header_id%TYPE)
31 IS
32 SELECT dhdr.task_assignment_id, jtrs.user_id, jta.resource_id
33 FROM csf_debrief_headers dhdr,
34 jtf_task_assignments jta,
35 jtf_rs_resource_extns jtrs
36 WHERE dhdr.debrief_header_id = p_debrief_header_id
37 AND jta.task_assignment_id = dhdr.task_assignment_id
38 AND jtrs.resource_id (+)= jta.resource_id
39 ;
40
41 l_csm_debrfHdInsInit_rec l_csm_debrfHdInsInit_csr%ROWTYPE;
42 l_csm_debrfHdInsInit_null l_csm_debrfHdInsInit_csr%ROWTYPE;
43
44 BEGIN
45 CSM_UTIL_PKG.LOG('Entering DEBRIEF_HEADER_INS_INIT for debrief_header_id: ' || p_debrief_header_id,
46 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_HEADER_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
47
48 l_csm_debrfHdInsInit_rec := l_csm_debrfHdInsInit_null;
49
50 OPEN l_csm_debrfHdInsInit_csr(p_debrief_header_id);
51 FETCH l_csm_debrfHdInsInit_csr INTO l_csm_debrfHdInsInit_rec;
52 IF l_csm_debrfHdInsInit_csr%NOTFOUND THEN
53 CLOSE l_csm_debrfHdInsInit_csr;
54 RETURN;
55 END IF;
56 CLOSE l_csm_debrfHdInsInit_csr;
57
58 IF p_flow_type IS NULL OR p_flow_type <> 'HISTORY' THEN
59 IF ( NOT (CSM_UTIL_PKG.is_palm_resource(l_csm_debrfHdInsInit_rec.resource_id))) THEN
60 CSM_UTIL_PKG.LOG('Not a mobile resource for debrief_header_id: ' || p_debrief_header_id,
61 'CSM_DEBRIEF_HEADER_EVENT_PKG.DEBRIEF_HEADER_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
62 RETURN;
63 END IF;
64 l_user_id := l_csm_debrfHdInsInit_rec.user_id;
65
66 -- get debrief header notes
67 csm_notes_event_pkg.notes_make_dirty_i_grp(p_sourceobjectcode=>'SD',
68 p_sourceobjectid=>p_debrief_header_id,
69 p_userid=>l_user_id,
70 p_error_msg=>l_error_msg,
71 x_return_status=>l_return_status);
72 ELSE
73 l_user_id := p_h_user_id;
74 END IF;
75
76 -- insert debrief headers into acc table
77 DEBRIEF_HEADER_MDIRTY_I(p_debrief_header_id=>p_debrief_header_id,
78 p_user_id=>l_user_id);
79
80 CSM_UTIL_PKG.LOG('Leaving DEBRIEF_HEADER_INS_INIT for debrief_header_id: ' || p_debrief_header_id,
81 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_HEADER_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
82 EXCEPTION
83 WHEN OTHERS THEN
84 l_sqlerrno := to_char(SQLCODE);
85 l_sqlerrmsg := substr(SQLERRM, 1,2000);
86 l_error_msg := ' Exception in DEBRIEF_HEADER_INS_INIT for debrief_header_id:'
87 || to_char(p_debrief_header_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
88 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_HEADER_INS_INIT',FND_LOG.LEVEL_EXCEPTION);
89 RAISE;
90 END DEBRIEF_HEADER_INS_INIT;
91
92 PROCEDURE DEBRIEF_HEADER_MDIRTY_I(p_debrief_header_id IN NUMBER, p_user_id IN NUMBER)
93 IS
94 l_sqlerrno VARCHAR2(20);
95 l_sqlerrmsg VARCHAR2(4000);
96 l_error_msg VARCHAR2(4000);
97 l_return_status VARCHAR2(2000);
98
99 BEGIN
100 CSM_UTIL_PKG.LOG('Entering DEBRIEF_HEADER_MDIRTY_I for debrief_header_id: ' || p_debrief_header_id,
101 'CSM_DEBRIEF_HEADER_EVENT_PKG.DEBRIEF_HEADER_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
102
103 CSM_ACC_PKG.Insert_Acc
104 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_DEBRIEF_HEADERS')
105 ,P_ACC_TABLE_NAME => g_debrief_acc_table_name
106 ,P_SEQ_NAME => g_debrief_seq_name
107 ,P_PK1_NAME => g_debrief_pk1_name
108 ,P_PK1_NUM_VALUE => p_debrief_header_id
109 ,P_USER_ID => p_user_id
110 );
111
112 CSM_UTIL_PKG.LOG('Leaving DEBRIEF_HEADER_MDIRTY_I for debrief_header_id: ' || p_debrief_header_id,
113 'CSM_DEBRIEF_HEADER_EVENT_PKG.DEBRIEF_HEADER_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
114 EXCEPTION
115 WHEN OTHERS THEN
116 l_sqlerrno := to_char(SQLCODE);
117 l_sqlerrmsg := substr(SQLERRM, 1,2000);
118 l_error_msg := ' Exception in DEBRIEF_HEADER_MDIRTY_I for debrief_header_id:'
119 || to_char(p_debrief_header_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
120 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_DEBRIEF_HEADER_EVENT_PKG.DEBRIEF_HEADER_MDIRTY_I',FND_LOG.LEVEL_EXCEPTION);
121 RAISE;
122 END DEBRIEF_HEADER_MDIRTY_I;
123
124 PROCEDURE DEBRIEF_HEADER_DEL_INIT(p_debrief_header_id IN NUMBER, p_user_id IN NUMBER,
125 p_flow_type IN VARCHAR2)
126 IS
127 l_sqlerrno VARCHAR2(20);
128 l_sqlerrmsg VARCHAR2(4000);
129 l_error_msg VARCHAR2(4000);
130 l_return_status VARCHAR2(2000);
131 l_user_id NUMBER;
132
133 CURSOR l_csm_debrfHdDel_csr (p_debrief_header_id csf_debrief_headers.debrief_header_id%TYPE,
134 p_user_id NUMBER)
135 IS
136 SELECT dhdr.task_assignment_id, acc.user_id
137 FROM csf_debrief_headers dhdr,
138 csm_debrief_headers_acc acc
139 WHERE dhdr.debrief_header_id = p_debrief_header_id
140 AND acc.debrief_header_id = dhdr.debrief_header_id
141 AND acc.user_id = p_user_id;
142
143 l_csm_debrfHdDel_rec l_csm_debrfHdDel_csr%ROWTYPE;
144 l_csm_debrfHdDel_null l_csm_debrfHdDel_csr%ROWTYPE;
145
146 BEGIN
147 CSM_UTIL_PKG.LOG('Entering DEBRIEF_HEADER_DEL_INIT for debrief_header_id: ' || p_debrief_header_id,
148 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_HEADER_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
149
150 l_csm_debrfHdDel_rec := l_csm_debrfHdDel_null;
151
152 OPEN l_csm_debrfHdDel_csr(p_debrief_header_id, p_user_id);
153 FETCH l_csm_debrfHdDel_csr INTO l_csm_debrfHdDel_rec;
154 IF l_csm_debrfHdDel_csr%NOTFOUND THEN
155 CLOSE l_csm_debrfHdDel_csr;
156 RETURN;
157 END IF;
158 CLOSE l_csm_debrfHdDel_csr;
159
160 IF p_flow_type IS NULL OR p_flow_type <> 'HISTORY' THEN
161 -- no need to check if its a mobile resource
162
163 -- delete debrief header notes
164 csm_notes_event_pkg.notes_make_dirty_i_grp(p_sourceobjectcode=>'SD',
165 p_sourceobjectid=>p_debrief_header_id,
166 p_userid=>p_user_id,
167 p_error_msg=>l_error_msg,
168 x_return_status=>l_return_status);
169 END IF;
170
171 -- delete debrief headers from acc table
172 DEBRIEF_HEADER_MDIRTY_D(p_debrief_header_id=>p_debrief_header_id,
173 p_user_id=>l_user_id);
174
175 CSM_UTIL_PKG.LOG('Leaving DEBRIEF_HEADER_DEL_INIT for debrief_header_id: ' || p_debrief_header_id,
176 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_HEADER_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
177 EXCEPTION
178 WHEN OTHERS THEN
179 l_sqlerrno := to_char(SQLCODE);
180 l_sqlerrmsg := substr(SQLERRM, 1,2000);
181 l_error_msg := ' Exception in DEBRIEF_HEADER_DEL_INIT for debrief_header_id:'
182 || to_char(p_debrief_header_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
183 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_HEADER_DEL_INIT',FND_LOG.LEVEL_EXCEPTION);
184 RAISE;
185 END DEBRIEF_HEADER_DEL_INIT;
186
187 PROCEDURE DEBRIEF_HEADER_MDIRTY_D(p_debrief_header_id IN NUMBER, p_user_id IN NUMBER)
188 IS
189 l_sqlerrno VARCHAR2(20);
190 l_sqlerrmsg VARCHAR2(4000);
191 l_error_msg VARCHAR2(4000);
192 l_return_status VARCHAR2(2000);
193 l_user_id NUMBER;
194
195 BEGIN
196 CSM_UTIL_PKG.LOG('Entering DEBRIEF_HEADER_MDIRTY_D for debrief_header_id: ' || p_debrief_header_id,
197 'CSM_DEBRIEF_HEADER_EVENT_PKG.DEBRIEF_HEADER_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
198
199 CSM_ACC_PKG.Delete_Acc
200 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_DEBRIEF_HEADERS')
201 ,P_ACC_TABLE_NAME => g_debrief_acc_table_name
202 ,P_PK1_NAME => g_debrief_pk1_name
203 ,P_PK1_NUM_VALUE => p_debrief_header_id
204 ,P_USER_ID => p_user_id
205 );
206
207 CSM_UTIL_PKG.LOG('Leaving DEBRIEF_HEADER_MDIRTY_D for debrief_header_id: ' || p_debrief_header_id,
208 'CSM_DEBRIEF_HEADER_EVENT_PKG.DEBRIEF_HEADER_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
209 EXCEPTION
210 WHEN OTHERS THEN
211 l_sqlerrno := to_char(SQLCODE);
212 l_sqlerrmsg := substr(SQLERRM, 1,2000);
213 l_error_msg := ' Exception in DEBRIEF_HEADER_MDIRTY_D for debrief_header_id:'
214 || to_char(p_debrief_header_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
215 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_DEBRIEF_HEADER_EVENT_PKG.DEBRIEF_HEADER_MDIRTY_D',FND_LOG.LEVEL_EXCEPTION);
216 RAISE;
217 END DEBRIEF_HEADER_MDIRTY_D;
218
219 PROCEDURE DEBRIEF_HEADER_MDIRTY_U(p_debrief_header_id IN NUMBER, p_user_id IN NUMBER)
220 IS
221 l_sqlerrno VARCHAR2(20);
222 l_sqlerrmsg VARCHAR2(4000);
223 l_error_msg VARCHAR2(4000);
224 l_return_status VARCHAR2(2000);
225 l_access_id NUMBER;
226
227 BEGIN
228 CSM_UTIL_PKG.LOG('Entering DEBRIEF_HEADER_MDIRTY_U for debrief_header_id: ' || p_debrief_header_id,
229 'CSM_DEBRIEF_HEADER_EVENT_PKG.DEBRIEF_HEADER_MDIRTY_U',FND_LOG.LEVEL_PROCEDURE);
230
231 l_access_id := CSM_ACC_PKG.Get_Acc_Id
232 ( P_ACC_TABLE_NAME => g_debrief_acc_table_name
233 ,P_PK1_NAME => g_debrief_pk1_name
234 ,P_PK1_NUM_VALUE => p_debrief_header_id
235 ,P_USER_ID => p_user_id
236 );
237
238 IF l_access_id <> -1 THEN
239 CSM_ACC_PKG.Update_Acc
240 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_DEBRIEF_HEADERS')
241 ,P_ACC_TABLE_NAME => g_debrief_acc_table_name
242 ,P_USER_ID => p_user_id
243 ,p_ACCESS_ID => l_access_id
244 );
245 END IF;
246
247 CSM_UTIL_PKG.LOG('Leaving DEBRIEF_HEADER_MDIRTY_U for debrief_header_id: ' || p_debrief_header_id,
248 'CSM_DEBRIEF_HEADER_EVENT_PKG.DEBRIEF_HEADER_MDIRTY_U',FND_LOG.LEVEL_PROCEDURE);
249 EXCEPTION
250 WHEN OTHERS THEN
251 l_sqlerrno := to_char(SQLCODE);
252 l_sqlerrmsg := substr(SQLERRM, 1,2000);
253 l_error_msg := ' Exception in DEBRIEF_HEADER_MDIRTY_U for debrief_header_id:'
254 || to_char(p_debrief_header_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
255 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_DEBRIEF_HEADER_EVENT_PKG.DEBRIEF_HEADER_MDIRTY_U',FND_LOG.LEVEL_EXCEPTION);
256 RAISE;
257 END DEBRIEF_HEADER_MDIRTY_U;
258
259 END CSM_DEBRIEF_HEADER_EVENT_PKG;