[Home] [Help]
PACKAGE BODY: APPS.CSM_DEBRIEF_EVENT_PKG
Source
1 PACKAGE BODY CSM_DEBRIEF_EVENT_PKG AS
2 /* $Header: csmedebb.pls 120.1.12010000.2 2008/08/07 05:42:01 trajasek ship $ */
3
4 g_debrief_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_DEBRIEF_LINES_ACC';
5 g_debrief_table_name CONSTANT VARCHAR2(30) := 'CSF_DEBRIEF_LINES';
6 g_debrief_seq_name CONSTANT VARCHAR2(30) := 'CSM_DEBRIEF_LINES_ACC_S';
7 g_debrief_pk1_name CONSTANT VARCHAR2(30) := 'DEBRIEF_LINE_ID';
8 g_debrief_pubi_name CSM_ACC_PKG.t_publication_item_list;
9 g_labor_billing_type_category CONSTANT VARCHAR2(30) := 'L';
10
11 g_systemitems_table_name CONSTANT VARCHAR2(30) := 'MTL_SYSTEM_ITEMS_B';
12 g_systemitems_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_SYSTEM_ITEMS_ACC';
13 g_systemitems_acc_seq_name CONSTANT VARCHAR2(30) := 'CSM_SYSTEM_ITEMS_ACC_S';
14 g_systemitems_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
15 CSM_ACC_PKG.t_publication_item_list('CSF_M_SYSTEM_ITEMS');
16 g_systemitems_pk1_name CONSTANT VARCHAR2(30) := 'INVENTORY_ITEM_ID';
17 g_systemitems_pk2_name CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
18
19 /*
20 g_counters_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
21 CSM_ACC_PKG.t_publication_item_list('CSF_M_COUNTERS');
22 */
23
24 /** Returns the name of the debrief Publication Item based upon the billing type **/
25 function GET_DEBRIEF_PI_NAME (p_material_bill_type cs_txn_billing_types.billing_type%TYPE)
26 return varchar2
27 is
28 l_debrief_pi_name varchar2(30);
29 l_no_billing_type_excp exception;
30 begin
31 if (p_material_bill_type = 'L') then
32 return 'CSF_M_DEBRIEF_LABOR';
33 elsif (p_material_bill_type = 'E') then
34 return 'CSF_M_DEBRIEF_EXPENSES';
35 elsif (p_material_bill_type = 'M') then
36 return 'CSF_M_DEBRIEF_PARTS';
37 else
38 raise l_no_billing_type_excp;
39 end if;
40 END GET_DEBRIEF_PI_NAME;
41
42 FUNCTION MATERIAL_BILLABLE_FLAG(p_debrief_line_id IN NUMBER, p_inventory_item_id IN NUMBER,
43 p_user_id IN NUMBER)
44 RETURN VARCHAR2
45 IS
46 l_sqlerrno VARCHAR2(20);
47 l_sqlerrmsg VARCHAR2(4000);
48 l_error_msg VARCHAR2(4000);
49 l_return_status VARCHAR2(2000);
50 l_billing_type_category cs_billing_type_categories.billing_category%TYPE;
51
52 --getting billing category using transaction type id
53 CURSOR l_txn_billing_csr(p_debrief_line_id csf_debrief_lines.debrief_line_id%TYPE, p_user_id IN NUMBER)
54 IS
55 SELECT cbtc.billing_category
56 FROM csf_debrief_lines lines,
57 mtl_system_items_b msi,
58 cs_txn_billing_types txbt,
59 CS_BILLING_TYPE_CATEGORIES cbtc
60 WHERE lines.debrief_line_id = p_debrief_line_id
61 AND msi.inventory_item_id = lines.inventory_item_id
62 AND msi.organization_id = NVL(NVL(lines.issuing_inventory_org_id, lines.receiving_inventory_org_id), csm_profile_pkg.get_organization_id(p_user_id))
63 AND lines.transaction_type_id = txbt.transaction_type_id
64 AND msi.material_billable_flag = txbt.billing_type
65 AND txbt.billing_type = cbtc.billing_type;
66
67 --getting billing category using Billing type id
68 CURSOR l_txn_billing_type_csr(p_debrief_line_id csf_debrief_lines.debrief_line_id%TYPE)
69 IS
70 SELECT cbtc.billing_category
71 FROM csf_debrief_lines lines,
72 cs_txn_billing_types txbt,
73 CS_BILLING_TYPE_CATEGORIES cbtc
74 WHERE lines.debrief_line_id = p_debrief_line_id
75 AND lines.txn_billing_type_id = txbt.txn_billing_type_id
76 AND txbt.billing_type = cbtc.billing_type;
77
78 BEGIN
79 l_billing_type_category := NULL;
80 OPEN l_txn_billing_csr(p_debrief_line_id, p_user_id);
81 FETCH l_txn_billing_csr INTO l_billing_type_category;
82 IF l_txn_billing_csr%NOTFOUND THEN
83 --getting billing category using billing type in debrief lines
84 OPEN l_txn_billing_type_csr(p_debrief_line_id);
85 FETCH l_txn_billing_type_csr INTO l_billing_type_category;
86
87
88 --Get the inventory item id for the debrief line
89 --If the inventory item id is null then the debrief line
90 --has to be considered as Labor line as per 11.5.9 CSF
91 --Material and Expense Lines require inventory item id as mandatory
92
93 --LOTR: At this point, we know that csr didnt return and inv_item is not null
94 --so we need to classify it as material or labour. How??
95 IF l_txn_billing_type_csr%NOTFOUND THEN
96 IF p_inventory_item_id IS NULL THEN
97 l_billing_type_category := g_labor_billing_type_category;
98 END IF;
99 END IF;
100 CLOSE l_txn_billing_type_csr;
101 END IF;
102 CLOSE l_txn_billing_csr;
103 RETURN l_billing_type_category;
104
105 EXCEPTION
106 WHEN OTHERS THEN
107 l_sqlerrno := to_char(SQLCODE);
108 l_sqlerrmsg := substr(SQLERRM, 1,2000);
109 l_error_msg := ' Exception in MATERIAL_BILLABLE_FLAG for debrief_line_id: ' || p_debrief_line_id
110 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
111 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_DEBRIEF_EVENT_PKG.MATERIAL_BILLABLE_FLAG',FND_LOG.LEVEL_EXCEPTION);
112 RETURN l_billing_type_category;
113 END MATERIAL_BILLABLE_FLAG;
114
115 PROCEDURE DEBRIEF_LINES_ACC_I(p_debrief_line_id IN NUMBER, p_billing_category IN VARCHAR2,
116 p_user_id IN NUMBER)
117 IS
118 l_sqlerrno VARCHAR2(20);
119 l_sqlerrmsg VARCHAR2(4000);
120 l_error_msg VARCHAR2(4000);
121 l_return_status VARCHAR2(2000);
122
123 BEGIN
124 CSM_UTIL_PKG.LOG('Entering DEBRIEF_LINES_ACC_I for debrief_line_id: ' || p_debrief_line_id,
125 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINES_ACC_I',FND_LOG.LEVEL_PROCEDURE);
126
127 CSM_ACC_PKG.Insert_Acc
128 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list(get_debrief_pi_name (p_billing_category))
129 ,P_ACC_TABLE_NAME => g_debrief_acc_table_name
130 ,P_SEQ_NAME => g_debrief_seq_name
131 ,P_PK1_NAME => g_debrief_pk1_name
132 ,P_PK1_NUM_VALUE => p_debrief_line_id
133 ,P_USER_ID => p_user_id
134 );
135
136 CSM_UTIL_PKG.LOG('Leaving DEBRIEF_LINES_ACC_I for debrief_line_id: ' || p_debrief_line_id,
137 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINES_ACC_I',FND_LOG.LEVEL_PROCEDURE);
138 EXCEPTION
139 WHEN OTHERS THEN
140 l_sqlerrno := to_char(SQLCODE);
141 l_sqlerrmsg := substr(SQLERRM, 1,2000);
142 l_error_msg := ' Exception in DEBRIEF_LINES_ACC_I for debrief_line_id:'
143 || to_char(p_debrief_line_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
144 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINES_ACC_I',FND_LOG.LEVEL_EXCEPTION);
145 RAISE;
146 END DEBRIEF_LINES_ACC_I;
147
148 PROCEDURE DEBRIEF_LINE_INS_INIT (p_debrief_line_id IN NUMBER, p_h_user_id IN NUMBER,
149 p_flow_type IN VARCHAR2)
150 IS
151 l_sqlerrno VARCHAR2(20);
152 l_sqlerrmsg VARCHAR2(4000);
153 l_error_msg VARCHAR2(4000);
154 l_return_status VARCHAR2(2000);
155 l_user_id NUMBER;
156 l_material_billable_flag VARCHAR2(1);
157 l_labor_exp_organization_id NUMBER;
158
159 CURSOR l_csm_debrfLnInsInit_csr (p_debrief_line_id csf_debrief_lines.debrief_line_id%TYPE)
160 IS
161 SELECT dhdr.task_assignment_id, jtrs.user_id, jta.resource_id ,dbl.inventory_item_id, dbl.instance_id,
162 NVL(NVL(issuing_inventory_org_id, receiving_inventory_org_id), csm_profile_pkg.get_organization_id(jtrs.user_id)) AS organization_id
163 FROM csf_debrief_lines dbl,
164 csf_debrief_headers dhdr,
165 jtf_task_assignments jta,
166 jtf_rs_resource_extns jtrs
167 WHERE dbl.debrief_line_id = p_debrief_line_id
168 AND dbl.debrief_header_id = dhdr.debrief_header_id
169 AND jta.task_assignment_id = dhdr.task_assignment_id
170 AND jtrs.resource_id (+)= jta.resource_id
171 ;
172
173 l_csm_debrfLnInsInit_rec l_csm_debrfLnInsInit_csr%ROWTYPE;
174 l_csm_debrfLnInsInit_null l_csm_debrfLnInsInit_csr%ROWTYPE;
175
176 BEGIN
177 CSM_UTIL_PKG.LOG('Entering DEBRIEF_LINE_INS_INIT for debrief_line_id: ' || p_debrief_line_id,
178 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
179
180 l_csm_debrfLnInsInit_rec := l_csm_debrfLnInsInit_null;
181
182 OPEN l_csm_debrfLnInsInit_csr(p_debrief_line_id);
183 FETCH l_csm_debrfLnInsInit_csr INTO l_csm_debrfLnInsInit_rec;
184 IF l_csm_debrfLnInsInit_csr%NOTFOUND THEN
185 CLOSE l_csm_debrfLnInsInit_csr;
186 RETURN;
187 END IF;
188 CLOSE l_csm_debrfLnInsInit_csr;
189
190 IF p_flow_type IS NULL OR p_flow_type <> 'HISTORY' THEN
191 IF ( NOT (CSM_UTIL_PKG.is_palm_resource(l_csm_debrfLnInsInit_rec.resource_id))) THEN
192 CSM_UTIL_PKG.LOG('Not a mobile resource for debrief_line_id: ' || p_debrief_line_id,
193 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
194 RETURN;
195 END IF;
196 l_user_id := l_csm_debrfLnInsInit_rec.user_id;
197 l_labor_exp_organization_id := l_csm_debrfLnInsInit_rec.organization_id;
198 ELSE
199 l_user_id := p_h_user_id;
200 l_labor_exp_organization_id := csm_profile_pkg.get_organization_id(l_user_id);
201 END IF;
202
203 -- get material billable flag of the debrief line
204 l_material_billable_flag := material_billable_flag(p_debrief_line_id=>p_debrief_line_id,
205 p_inventory_item_id=>l_csm_debrfLnInsInit_rec.inventory_item_id,
206 p_user_id=>l_user_id);
207
208 IF l_material_billable_flag IN ('L','E') THEN -- labor, expense line
209 -- insert into csm_system_items_acc is inventory_item_id is not null
210 IF l_csm_debrfLnInsInit_rec.inventory_item_id IS NOT NULL THEN
211 csm_system_item_event_pkg.system_item_mdirty_i(p_inventory_item_id=>l_csm_debrfLnInsInit_rec.inventory_item_id,
212 p_organization_id=>l_labor_exp_organization_id,
213 p_user_id=>l_user_id);
214 END IF;
215 ELSIF l_material_billable_flag = ('M') THEN-- material line
216 -- insert instance_id into acc table
217 IF l_csm_debrfLnInsInit_rec.instance_id IS NOT NULL THEN
218 csm_item_instance_event_pkg.item_instances_acc_processor(p_instance_id=>l_csm_debrfLnInsInit_rec.instance_id,
219 p_user_id=>l_user_id,
220 p_flowtype=>p_flow_type,
221 p_error_msg=>l_error_msg,
222 x_return_status=>l_return_status);
223 END IF;
224 END IF;
225
226 IF l_material_billable_flag IS NOT NULL THEN -- only for labor, expense and Material line
227 -- insert debrief line into acc table
228 DEBRIEF_LINES_ACC_I(p_debrief_line_id=>p_debrief_line_id,
229 p_billing_category=>l_material_billable_flag,
230 p_user_id=>l_user_id);
231 END IF;
232 CSM_UTIL_PKG.LOG('Leaving DEBRIEF_LINE_INS_INIT for debrief_line_id: ' || p_debrief_line_id,
233 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
234 EXCEPTION
235 WHEN OTHERS THEN
236 l_sqlerrno := to_char(SQLCODE);
237 l_sqlerrmsg := substr(SQLERRM, 1,2000);
238 l_error_msg := ' Exception in DEBRIEF_LINE_INS_INIT for debrief_line_id:'
239 || to_char(p_debrief_line_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
240 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_INS_INIT',FND_LOG.LEVEL_EXCEPTION);
241 RAISE;
242 END DEBRIEF_LINE_INS_INIT;
243
244 PROCEDURE DEBRIEF_LINE_DEL_INIT (p_debrief_line_id IN NUMBER, p_user_id IN NUMBER,
245 p_flow_type IN VARCHAR2)
246 IS
247 l_sqlerrno VARCHAR2(20);
248 l_sqlerrmsg VARCHAR2(4000);
249 l_error_msg VARCHAR2(4000);
250 l_return_status VARCHAR2(2000);
251 l_user_id NUMBER;
252 l_material_billable_flag VARCHAR2(1);
253
254 CURSOR l_csm_debrfLnDel_csr (p_debrief_line_id csf_debrief_lines.debrief_line_id%TYPE, p_user_id IN NUMBER)
255 IS
256 SELECT acc.user_id, dbl.inventory_item_id, dbl.instance_id,
257 NVL(NVL(dbl.issuing_inventory_org_id, dbl.receiving_inventory_org_id), csm_profile_pkg.get_organization_id(p_user_id)) AS organization_id
258 FROM csf_debrief_lines dbl,
259 csm_debrief_lines_acc acc
260 WHERE dbl.debrief_line_id = p_debrief_line_id
261 AND acc.debrief_line_id = dbl.debrief_line_id
262 AND acc.user_id = p_user_id;
263
264 l_csm_debrfLnDel_rec l_csm_debrfLnDel_csr%ROWTYPE;
265 l_csm_debrfLnDel_null l_csm_debrfLnDel_csr%ROWTYPE;
266
267 BEGIN
268 CSM_UTIL_PKG.LOG('Entering DEBRIEF_LINE_DEL_INIT for debrief_line_id: ' || p_debrief_line_id,
269 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
270 l_csm_debrfLnDel_rec := l_csm_debrfLnDel_null;
271
272 OPEN l_csm_debrfLnDel_csr(p_debrief_line_id, p_user_id);
273 FETCH l_csm_debrfLnDel_csr INTO l_csm_debrfLnDel_rec;
274 IF l_csm_debrfLnDel_csr%NOTFOUND THEN
275 CLOSE l_csm_debrfLnDel_csr;
276 RETURN;
277 END IF;
278 CLOSE l_csm_debrfLnDel_csr;
279 -- no need to check if its history as the line has to be deleted from the acc table
280 -- get material billable flag of the debrief line
281 l_material_billable_flag := material_billable_flag(p_debrief_line_id=>p_debrief_line_id,
282 p_inventory_item_id=>l_csm_debrfLnDel_rec.inventory_item_id,
283 p_user_id=>p_user_id);
284 IF l_material_billable_flag IN ('L','E') THEN -- labor, expense line
285 -- insert into csm_system_items_acc is inventory_item_id is not null
286 IF l_csm_debrfLnDel_rec.inventory_item_id IS NOT NULL THEN
287 csm_system_item_event_pkg.system_item_mdirty_d(p_inventory_item_id=>l_csm_debrfLnDel_rec.inventory_item_id,
288 p_organization_id=>l_csm_debrfLnDel_rec.organization_id,
289 p_user_id=>p_user_id);
290 END IF;
291 ELSIF l_material_billable_flag = ('M') THEN-- material line
292 -- delete instance_id from acc table
293 IF l_csm_debrfLnDel_rec.instance_id IS NOT NULL THEN
294 csm_item_instance_event_pkg.item_instances_acc_d(p_instance_id=>l_csm_debrfLnDel_rec.instance_id,
295 p_user_id=>p_user_id,
296 p_error_msg=>l_error_msg,
297 x_return_status=>l_return_status);
298 END IF;
299 END IF;
300 IF l_material_billable_flag IS NOT NULL THEN -- only for labor, expense and Material line
301 -- delete debrief line from acc table
302 debrief_lines_acc_d(p_debrief_line_id=>p_debrief_line_id,
303 p_billing_category=>l_material_billable_flag,
304 p_user_id=>p_user_id);
305 END IF;
306 CSM_UTIL_PKG.LOG('Leaving DEBRIEF_LINE_DEL_INIT for debrief_line_id: ' || p_debrief_line_id,
307 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
308 EXCEPTION
309 WHEN OTHERS THEN
310 l_sqlerrno := to_char(SQLCODE);
311 l_sqlerrmsg := substr(SQLERRM, 1,2000);
312 l_error_msg := ' Exception in DEBRIEF_LINE_DEL_INIT for debrief_line_id:'
313 || to_char(p_debrief_line_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
314 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_DEL_INIT',FND_LOG.LEVEL_EXCEPTION);
315 RAISE;
316 END DEBRIEF_LINE_DEL_INIT;
317
318 PROCEDURE DEBRIEF_LINES_ACC_D(p_debrief_line_id IN NUMBER, p_billing_category IN VARCHAR2,
319 p_user_id IN NUMBER)
320 IS
321 l_sqlerrno VARCHAR2(20);
322 l_sqlerrmsg VARCHAR2(4000);
323 l_error_msg VARCHAR2(4000);
324 l_return_status VARCHAR2(2000);
325
326 BEGIN
327 CSM_UTIL_PKG.LOG('Entering DEBRIEF_LINES_ACC_D for debrief_line_id: ' || p_debrief_line_id,
328 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINES_ACC_D',FND_LOG.LEVEL_PROCEDURE);
329
330 CSM_ACC_PKG.Delete_Acc
331 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list(get_debrief_pi_name (p_billing_category))
332 ,P_ACC_TABLE_NAME => g_debrief_acc_table_name
333 ,P_PK1_NAME => g_debrief_pk1_name
334 ,P_PK1_NUM_VALUE => p_debrief_line_id
335 ,P_USER_ID => p_user_id
336 );
337 CSM_UTIL_PKG.LOG('Leaving DEBRIEF_LINES_ACC_D for debrief_line_id: ' || p_debrief_line_id,
338 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINES_ACC_D',FND_LOG.LEVEL_PROCEDURE);
339 EXCEPTION
340 WHEN OTHERS THEN
341 l_sqlerrno := to_char(SQLCODE);
342 l_sqlerrmsg := substr(SQLERRM, 1,2000);
343 l_error_msg := ' Exception in DEBRIEF_LINES_ACC_D for debrief_line_id:'
344 || to_char(p_debrief_line_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
345 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINES_ACC_D',FND_LOG.LEVEL_EXCEPTION);
346 RAISE;
347 END DEBRIEF_LINES_ACC_D;
348
349 PROCEDURE DEBRIEF_LINE_UPD_INIT(p_debrief_line_id IN NUMBER, p_old_inventory_item_id IN NUMBER,
350 p_is_inventory_item_updated IN VARCHAR2, p_old_instance_id IN NUMBER,
351 p_is_instance_updated IN VARCHAR2)
352 IS
353 l_sqlerrno VARCHAR2(20);
354 l_sqlerrmsg VARCHAR2(4000);
355 l_error_msg VARCHAR2(4000);
356 l_return_status VARCHAR2(2000);
357 l_material_billable_flag VARCHAR2(1);
358
359 CURSOR l_csm_debrfLnUpdInit_csr (p_debrief_line_id csf_debrief_lines.debrief_line_id%type) IS
360 SELECT dhdr.task_assignment_id,
361 jtrs.user_id,
362 jta.resource_id,
363 dbl.inventory_item_id,
364 dbl.instance_id,
365 NVL(nvl(issuing_inventory_org_id, receiving_inventory_org_id), csm_profile_pkg.get_organization_id(jtrs.user_id)) AS organization_id,
366 acc.access_id
367 FROM csf_debrief_lines dbl,
368 csf_debrief_headers dhdr,
369 csm_debrief_lines_acc acc,
370 jtf_task_assignments jta,
371 jtf_rs_resource_extns jtrs
372 WHERE dbl.debrief_line_id = p_debrief_line_id
373 AND dbl.debrief_header_id = dhdr.debrief_header_id
374 AND jta.task_assignment_id = dhdr.task_assignment_id
375 AND jtrs.resource_id = jta.resource_id
376 AND acc.debrief_line_id = dbl.debrief_line_id
377 AND acc.user_id = jtrs.user_id
378 ;
379
380 l_csm_debrfLnUpdInit_rec l_csm_debrfLnUpdInit_csr%ROWTYPE;
381 l_csm_debrfLnUpdInit_null l_csm_debrfLnUpdInit_csr%ROWTYPE;
382
383 BEGIN
384 CSM_UTIL_PKG.LOG('Entering DEBRIEF_LINE_UPD_INIT for debrief_line_id: ' || p_debrief_line_id,
385 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_UPD_INIT',FND_LOG.LEVEL_PROCEDURE);
386
387 l_csm_debrfLnUpdInit_rec := l_csm_debrfLnUpdInit_null;
388
389 OPEN l_csm_debrfLnUpdInit_csr(p_debrief_line_id);
390 FETCH l_csm_debrfLnUpdInit_csr INTO l_csm_debrfLnUpdInit_rec;
391 IF l_csm_debrfLnUpdInit_csr%NOTFOUND THEN
392 CLOSE l_csm_debrfLnUpdInit_csr;
393 RETURN;
394 END IF;
395 CLOSE l_csm_debrfLnUpdInit_csr;
396
397 -- get material billable flag of the debrief line
398 l_material_billable_flag := material_billable_flag(p_debrief_line_id=>p_debrief_line_id,
399 p_inventory_item_id=>l_csm_debrfLnUpdInit_rec.inventory_item_id,
400 p_user_id=>l_csm_debrfLnUpdInit_rec.user_id);
401
402 IF l_material_billable_flag IS NOT NULL THEN -- only for labor, expense and Material line
403 -- mark dirty the debrief line
404 DEBRIEF_LINES_ACC_U(p_debrief_line_id=>p_debrief_line_id,
405 p_billing_category=>l_material_billable_flag,
406 p_access_id=>l_csm_debrfLnUpdInit_rec.access_id,
407 p_user_id=>l_csm_debrfLnUpdInit_rec.user_id);
408 END IF;
409
410 IF l_material_billable_flag IN ('L','E') THEN -- labor, expense line
411 IF p_is_inventory_item_updated = 'Y' THEN
412 -- insert the new inventory item
413 IF l_csm_debrfLnUpdInit_rec.inventory_item_id IS NOT NULL THEN
414 csm_system_item_event_pkg.system_item_mdirty_i(p_inventory_item_id=>l_csm_debrfLnUpdInit_rec.inventory_item_id,
415 p_organization_id=>l_csm_debrfLnUpdInit_rec.organization_id,
416 p_user_id=>l_csm_debrfLnUpdInit_rec.user_id);
417 END IF;
418
419 -- delete the old inventory item
420 IF p_old_inventory_item_id IS NOT NULL THEN
421 csm_system_item_event_pkg.system_item_mdirty_d(p_inventory_item_id=>p_old_inventory_item_id,
422 p_organization_id=>l_csm_debrfLnUpdInit_rec.organization_id,
423 p_user_id=>l_csm_debrfLnUpdInit_rec.user_id);
424 END IF;
425 END IF;
426 ELSIF l_material_billable_flag = ('M') THEN-- material line
427 IF p_is_instance_updated = 'Y' THEN
428 -- insert the new instance
429 IF l_csm_debrfLnUpdInit_rec.instance_id IS NOT NULL THEN
430 csm_item_instance_event_pkg.item_instances_acc_processor(p_instance_id=>l_csm_debrfLnUpdInit_rec.instance_id,
431 p_user_id=>l_csm_debrfLnUpdInit_rec.user_id,
432 p_flowtype=>NULL,
433 p_error_msg=>l_error_msg,
434 x_return_status=>l_return_status);
435 END IF;
436
437 -- delete the old instance
438 IF p_old_instance_id IS NOT NULL THEN
439 csm_item_instance_event_pkg.item_instances_acc_d(p_instance_id=>p_old_instance_id,
440 p_user_id=>l_csm_debrfLnUpdInit_rec.user_id,
441 p_error_msg=>l_error_msg,
442 x_return_status=>l_return_status);
443 END IF;
444 END IF;
445 END IF;
446
447 CSM_UTIL_PKG.LOG('Leaving DEBRIEF_LINE_UPD_INIT for debrief_line_id: ' || p_debrief_line_id,
448 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_UPD_INIT',FND_LOG.LEVEL_PROCEDURE);
449 EXCEPTION
450 WHEN OTHERS THEN
451 l_sqlerrno := to_char(SQLCODE);
452 l_sqlerrmsg := substr(SQLERRM, 1,2000);
453 l_error_msg := ' Exception in DEBRIEF_LINE_UPD_INIT for debrief_line_id:'
454 || to_char(p_debrief_line_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
455 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_UPD_INIT',FND_LOG.LEVEL_EXCEPTION);
456 RAISE;
457 END DEBRIEF_LINE_UPD_INIT;
458
459 PROCEDURE DEBRIEF_LINES_ACC_U(p_debrief_line_id IN NUMBER, p_billing_category IN VARCHAR2,
460 p_access_id IN NUMBER, p_user_id IN NUMBER)
461 IS
462 l_sqlerrno VARCHAR2(20);
463 l_sqlerrmsg VARCHAR2(4000);
464 l_error_msg VARCHAR2(4000);
465 l_return_status VARCHAR2(2000);
466
467 BEGIN
468 CSM_UTIL_PKG.LOG('Entering DEBRIEF_LINES_ACC_U for debrief_line_id: ' || p_debrief_line_id,
469 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINES_ACC_U',FND_LOG.LEVEL_PROCEDURE);
470
471 CSM_ACC_PKG.Update_Acc
472 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list(get_debrief_pi_name (p_billing_category))
473 ,P_ACC_TABLE_NAME => g_debrief_acc_table_name
474 ,P_ACCESS_ID => p_access_id
475 ,P_USER_ID => p_user_id
476 );
477
478 CSM_UTIL_PKG.LOG('Leaving DEBRIEF_LINES_ACC_U for debrief_line_id: ' || p_debrief_line_id,
479 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINES_ACC_U',FND_LOG.LEVEL_PROCEDURE);
480 EXCEPTION
481 WHEN OTHERS THEN
482 l_sqlerrno := to_char(SQLCODE);
483 l_sqlerrmsg := substr(SQLERRM, 1,2000);
484 l_error_msg := ' Exception in DEBRIEF_LINES_ACC_U for debrief_line_id:'
485 || to_char(p_debrief_line_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
486 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINES_ACC_U',FND_LOG.LEVEL_EXCEPTION);
487 RAISE;
488 END DEBRIEF_LINES_ACC_U;
489
490 END CSM_DEBRIEF_EVENT_PKG;