[Home] [Help]
PACKAGE BODY: APPS.CSM_DEBRIEF_EVENT_PKG
Source
1 PACKAGE BODY CSM_DEBRIEF_EVENT_PKG AS
2 /* $Header: csmedebb.pls 120.2.12020000.2 2013/04/09 10:53:51 saradhak 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 usage_type
164 FROM csf_debrief_lines dbl,
165 csf_debrief_headers dhdr,
166 jtf_task_assignments jta,
167 jtf_rs_resource_extns jtrs
168 WHERE dbl.debrief_line_id = p_debrief_line_id
169 AND dbl.debrief_header_id = dhdr.debrief_header_id
170 AND jta.task_assignment_id = dhdr.task_assignment_id
171 AND jtrs.resource_id (+)= jta.resource_id
172 ;
173
174 l_csm_debrfLnInsInit_rec l_csm_debrfLnInsInit_csr%ROWTYPE;
175 l_csm_debrfLnInsInit_null l_csm_debrfLnInsInit_csr%ROWTYPE;
176
177 BEGIN
178 CSM_UTIL_PKG.LOG('Entering DEBRIEF_LINE_INS_INIT for debrief_line_id: ' || p_debrief_line_id,
179 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
180
181 l_csm_debrfLnInsInit_rec := l_csm_debrfLnInsInit_null;
182
183 OPEN l_csm_debrfLnInsInit_csr(p_debrief_line_id);
184 FETCH l_csm_debrfLnInsInit_csr INTO l_csm_debrfLnInsInit_rec;
185 IF l_csm_debrfLnInsInit_csr%NOTFOUND THEN
186 CLOSE l_csm_debrfLnInsInit_csr;
187 RETURN;
188 END IF;
189 CLOSE l_csm_debrfLnInsInit_csr;
190
191 IF p_flow_type IS NULL OR p_flow_type <> 'HISTORY' THEN
192 IF ( NOT (CSM_UTIL_PKG.is_palm_resource(l_csm_debrfLnInsInit_rec.resource_id))) THEN
193 CSM_UTIL_PKG.LOG('Not a mobile resource for debrief_line_id: ' || p_debrief_line_id,
194 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
195 RETURN;
196 END IF;
197 l_user_id := l_csm_debrfLnInsInit_rec.user_id;
198 l_labor_exp_organization_id := l_csm_debrfLnInsInit_rec.organization_id;
199 ELSE
200 l_user_id := p_h_user_id;
201 l_labor_exp_organization_id := csm_profile_pkg.get_organization_id(l_user_id);
202 END IF;
203
204 -- get material billable flag of the debrief line
205
206 IF l_csm_debrfLnInsInit_rec.USAGE_TYPE ='DOA' Or l_csm_debrfLnInsInit_rec.USAGE_TYPE ='UNUSED' THEN
207 l_material_billable_flag :='M';
208 ELSE
209 l_material_billable_flag := material_billable_flag(p_debrief_line_id=>p_debrief_line_id,
210 p_inventory_item_id=>l_csm_debrfLnInsInit_rec.inventory_item_id,
211 p_user_id=>l_user_id);
212 END IF;
213
214 IF l_material_billable_flag IN ('L','E') THEN -- labor, expense line
215 -- insert into csm_system_items_acc is inventory_item_id is not null
216 IF l_csm_debrfLnInsInit_rec.inventory_item_id IS NOT NULL THEN
217 csm_system_item_event_pkg.system_item_mdirty_i(p_inventory_item_id=>l_csm_debrfLnInsInit_rec.inventory_item_id,
218 p_organization_id=>l_labor_exp_organization_id,
219 p_user_id=>l_user_id);
220 END IF;
221 ELSIF l_material_billable_flag = ('M') THEN-- material line
222 -- insert instance_id into acc table
223 IF l_csm_debrfLnInsInit_rec.instance_id IS NOT NULL THEN
224 csm_item_instance_event_pkg.item_instances_acc_processor(p_instance_id=>l_csm_debrfLnInsInit_rec.instance_id,
225 p_user_id=>l_user_id,
226 p_flowtype=>p_flow_type,
227 p_error_msg=>l_error_msg,
228 x_return_status=>l_return_status);
229 END IF;
230 END IF;
231
232 IF l_material_billable_flag IS NOT NULL THEN -- only for labor, expense and Material line
233 -- insert debrief line into acc table
234 DEBRIEF_LINES_ACC_I(p_debrief_line_id=>p_debrief_line_id,
235 p_billing_category=>l_material_billable_flag,
236 p_user_id=>l_user_id);
237 END IF;
238 CSM_UTIL_PKG.LOG('Leaving DEBRIEF_LINE_INS_INIT for debrief_line_id: ' || p_debrief_line_id,
239 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
240 EXCEPTION
241 WHEN OTHERS THEN
242 l_sqlerrno := to_char(SQLCODE);
243 l_sqlerrmsg := substr(SQLERRM, 1,2000);
244 l_error_msg := ' Exception in DEBRIEF_LINE_INS_INIT for debrief_line_id:'
245 || to_char(p_debrief_line_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
246 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_INS_INIT',FND_LOG.LEVEL_EXCEPTION);
247 RAISE;
248 END DEBRIEF_LINE_INS_INIT;
249
250 PROCEDURE DEBRIEF_LINE_DEL_INIT (p_debrief_line_id IN NUMBER, p_user_id IN NUMBER,
251 p_flow_type IN VARCHAR2)
252 IS
253 l_sqlerrno VARCHAR2(20);
254 l_sqlerrmsg VARCHAR2(4000);
255 l_error_msg VARCHAR2(4000);
256 l_return_status VARCHAR2(2000);
257 l_user_id NUMBER;
258 l_material_billable_flag VARCHAR2(1);
259
260 CURSOR l_csm_debrfLnDel_csr (p_debrief_line_id csf_debrief_lines.debrief_line_id%TYPE, p_user_id IN NUMBER)
261 IS
262 SELECT acc.user_id, dbl.inventory_item_id, dbl.instance_id,
263 NVL(NVL(dbl.issuing_inventory_org_id, dbl.receiving_inventory_org_id), csm_profile_pkg.get_organization_id(p_user_id)) AS organization_id,
264 usage_type
265 FROM csf_debrief_lines dbl,
266 csm_debrief_lines_acc acc
267 WHERE dbl.debrief_line_id = p_debrief_line_id
268 AND acc.debrief_line_id = dbl.debrief_line_id
269 AND acc.user_id = p_user_id;
270
271 l_csm_debrfLnDel_rec l_csm_debrfLnDel_csr%ROWTYPE;
272 l_csm_debrfLnDel_null l_csm_debrfLnDel_csr%ROWTYPE;
273
274 BEGIN
275 CSM_UTIL_PKG.LOG('Entering DEBRIEF_LINE_DEL_INIT for debrief_line_id: ' || p_debrief_line_id,
276 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
277 l_csm_debrfLnDel_rec := l_csm_debrfLnDel_null;
278
279 OPEN l_csm_debrfLnDel_csr(p_debrief_line_id, p_user_id);
280 FETCH l_csm_debrfLnDel_csr INTO l_csm_debrfLnDel_rec;
281 IF l_csm_debrfLnDel_csr%NOTFOUND THEN
282 CLOSE l_csm_debrfLnDel_csr;
283 RETURN;
284 END IF;
285 CLOSE l_csm_debrfLnDel_csr;
286 -- no need to check if its history as the line has to be deleted from the acc table
287
288 IF l_csm_debrfLnDel_rec.USAGE_TYPE ='DOA' Or l_csm_debrfLnDel_rec.USAGE_TYPE ='UNUSED' THEN
289 l_material_billable_flag :='M';
290 ELSE
291 -- get material billable flag of the debrief line
292 l_material_billable_flag := material_billable_flag(p_debrief_line_id=>p_debrief_line_id,
293 p_inventory_item_id=>l_csm_debrfLnDel_rec.inventory_item_id,
294 p_user_id=>p_user_id);
295 END IF;
296
297 IF l_material_billable_flag IN ('L','E') THEN -- labor, expense line
298 -- insert into csm_system_items_acc is inventory_item_id is not null
299 IF l_csm_debrfLnDel_rec.inventory_item_id IS NOT NULL THEN
300 csm_system_item_event_pkg.system_item_mdirty_d(p_inventory_item_id=>l_csm_debrfLnDel_rec.inventory_item_id,
301 p_organization_id=>l_csm_debrfLnDel_rec.organization_id,
302 p_user_id=>p_user_id);
303 END IF;
304 ELSIF l_material_billable_flag = ('M') THEN-- material line
305 -- delete instance_id from acc table
306 IF l_csm_debrfLnDel_rec.instance_id IS NOT NULL THEN
307 csm_item_instance_event_pkg.item_instances_acc_d(p_instance_id=>l_csm_debrfLnDel_rec.instance_id,
308 p_user_id=>p_user_id,
309 p_error_msg=>l_error_msg,
310 x_return_status=>l_return_status);
311 END IF;
312 END IF;
313 IF l_material_billable_flag IS NOT NULL THEN -- only for labor, expense and Material line
314 -- delete debrief line from acc table
315 debrief_lines_acc_d(p_debrief_line_id=>p_debrief_line_id,
316 p_billing_category=>l_material_billable_flag,
317 p_user_id=>p_user_id);
318 END IF;
319 CSM_UTIL_PKG.LOG('Leaving DEBRIEF_LINE_DEL_INIT for debrief_line_id: ' || p_debrief_line_id,
320 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
321 EXCEPTION
322 WHEN OTHERS THEN
323 l_sqlerrno := to_char(SQLCODE);
324 l_sqlerrmsg := substr(SQLERRM, 1,2000);
325 l_error_msg := ' Exception in DEBRIEF_LINE_DEL_INIT for debrief_line_id:'
326 || to_char(p_debrief_line_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
327 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_DEL_INIT',FND_LOG.LEVEL_EXCEPTION);
328 RAISE;
329 END DEBRIEF_LINE_DEL_INIT;
330
331 PROCEDURE DEBRIEF_LINES_ACC_D(p_debrief_line_id IN NUMBER, p_billing_category IN VARCHAR2,
332 p_user_id IN NUMBER)
333 IS
334 l_sqlerrno VARCHAR2(20);
335 l_sqlerrmsg VARCHAR2(4000);
336 l_error_msg VARCHAR2(4000);
337 l_return_status VARCHAR2(2000);
338
339 BEGIN
340 CSM_UTIL_PKG.LOG('Entering DEBRIEF_LINES_ACC_D for debrief_line_id: ' || p_debrief_line_id,
341 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINES_ACC_D',FND_LOG.LEVEL_PROCEDURE);
342
343 CSM_ACC_PKG.Delete_Acc
344 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list(get_debrief_pi_name (p_billing_category))
345 ,P_ACC_TABLE_NAME => g_debrief_acc_table_name
346 ,P_PK1_NAME => g_debrief_pk1_name
347 ,P_PK1_NUM_VALUE => p_debrief_line_id
348 ,P_USER_ID => p_user_id
349 );
350 CSM_UTIL_PKG.LOG('Leaving DEBRIEF_LINES_ACC_D for debrief_line_id: ' || p_debrief_line_id,
351 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINES_ACC_D',FND_LOG.LEVEL_PROCEDURE);
352 EXCEPTION
353 WHEN OTHERS THEN
354 l_sqlerrno := to_char(SQLCODE);
355 l_sqlerrmsg := substr(SQLERRM, 1,2000);
356 l_error_msg := ' Exception in DEBRIEF_LINES_ACC_D for debrief_line_id:'
357 || to_char(p_debrief_line_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
358 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINES_ACC_D',FND_LOG.LEVEL_EXCEPTION);
359 RAISE;
360 END DEBRIEF_LINES_ACC_D;
361
362 PROCEDURE DEBRIEF_LINE_UPD_INIT(p_debrief_line_id IN NUMBER, p_old_inventory_item_id IN NUMBER,
363 p_is_inventory_item_updated IN VARCHAR2, p_old_instance_id IN NUMBER,
364 p_is_instance_updated IN VARCHAR2)
365 IS
366 l_sqlerrno VARCHAR2(20);
367 l_sqlerrmsg VARCHAR2(4000);
368 l_error_msg VARCHAR2(4000);
369 l_return_status VARCHAR2(2000);
370 l_material_billable_flag VARCHAR2(1);
371
372 CURSOR l_csm_debrfLnUpdInit_csr (p_debrief_line_id csf_debrief_lines.debrief_line_id%type) IS
373 SELECT dhdr.task_assignment_id,
374 jtrs.user_id,
375 jta.resource_id,
376 dbl.inventory_item_id,
377 dbl.instance_id,
378 NVL(nvl(issuing_inventory_org_id, receiving_inventory_org_id), csm_profile_pkg.get_organization_id(jtrs.user_id)) AS organization_id,
379 acc.access_id,
380 usage_type
381 FROM csf_debrief_lines dbl,
382 csf_debrief_headers dhdr,
383 csm_debrief_lines_acc acc,
384 jtf_task_assignments jta,
385 jtf_rs_resource_extns jtrs
386 WHERE dbl.debrief_line_id = p_debrief_line_id
387 AND dbl.debrief_header_id = dhdr.debrief_header_id
388 AND jta.task_assignment_id = dhdr.task_assignment_id
389 AND jtrs.resource_id = jta.resource_id
390 AND acc.debrief_line_id = dbl.debrief_line_id
391 AND acc.user_id = jtrs.user_id
392 ;
393
394 l_csm_debrfLnUpdInit_rec l_csm_debrfLnUpdInit_csr%ROWTYPE;
395 l_csm_debrfLnUpdInit_null l_csm_debrfLnUpdInit_csr%ROWTYPE;
396
397 BEGIN
398 CSM_UTIL_PKG.LOG('Entering DEBRIEF_LINE_UPD_INIT for debrief_line_id: ' || p_debrief_line_id,
399 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_UPD_INIT',FND_LOG.LEVEL_PROCEDURE);
400
401 l_csm_debrfLnUpdInit_rec := l_csm_debrfLnUpdInit_null;
402
403 OPEN l_csm_debrfLnUpdInit_csr(p_debrief_line_id);
404 FETCH l_csm_debrfLnUpdInit_csr INTO l_csm_debrfLnUpdInit_rec;
405 IF l_csm_debrfLnUpdInit_csr%NOTFOUND THEN
406 CLOSE l_csm_debrfLnUpdInit_csr;
407 RETURN;
408 END IF;
409 CLOSE l_csm_debrfLnUpdInit_csr;
410
411
412 IF l_csm_debrfLnUpdInit_rec.USAGE_TYPE ='DOA' Or l_csm_debrfLnUpdInit_rec.USAGE_TYPE ='UNUSED' THEN
413 l_material_billable_flag :='M';
414 ELSE
415 -- get material billable flag of the debrief line
416 l_material_billable_flag := material_billable_flag(p_debrief_line_id=>p_debrief_line_id,
417 p_inventory_item_id=>l_csm_debrfLnUpdInit_rec.inventory_item_id,
418 p_user_id=>l_csm_debrfLnUpdInit_rec.user_id);
419 END IF;
420
421 IF l_material_billable_flag IS NOT NULL THEN -- only for labor, expense and Material line
422 -- mark dirty the debrief line
423 DEBRIEF_LINES_ACC_U(p_debrief_line_id=>p_debrief_line_id,
424 p_billing_category=>l_material_billable_flag,
425 p_access_id=>l_csm_debrfLnUpdInit_rec.access_id,
426 p_user_id=>l_csm_debrfLnUpdInit_rec.user_id);
427 END IF;
428
429 IF l_material_billable_flag IN ('L','E') THEN -- labor, expense line
430 IF p_is_inventory_item_updated = 'Y' THEN
431 -- insert the new inventory item
432 IF l_csm_debrfLnUpdInit_rec.inventory_item_id IS NOT NULL THEN
433 csm_system_item_event_pkg.system_item_mdirty_i(p_inventory_item_id=>l_csm_debrfLnUpdInit_rec.inventory_item_id,
434 p_organization_id=>l_csm_debrfLnUpdInit_rec.organization_id,
435 p_user_id=>l_csm_debrfLnUpdInit_rec.user_id);
436 END IF;
437
438 -- delete the old inventory item
439 IF p_old_inventory_item_id IS NOT NULL THEN
440 csm_system_item_event_pkg.system_item_mdirty_d(p_inventory_item_id=>p_old_inventory_item_id,
441 p_organization_id=>l_csm_debrfLnUpdInit_rec.organization_id,
442 p_user_id=>l_csm_debrfLnUpdInit_rec.user_id);
443 END IF;
444 END IF;
445 ELSIF l_material_billable_flag = ('M') THEN-- material line
446 IF p_is_instance_updated = 'Y' THEN
447 -- insert the new instance
448 IF l_csm_debrfLnUpdInit_rec.instance_id IS NOT NULL THEN
449 csm_item_instance_event_pkg.item_instances_acc_processor(p_instance_id=>l_csm_debrfLnUpdInit_rec.instance_id,
450 p_user_id=>l_csm_debrfLnUpdInit_rec.user_id,
451 p_flowtype=>NULL,
452 p_error_msg=>l_error_msg,
453 x_return_status=>l_return_status);
454 END IF;
455
456 -- delete the old instance
457 IF p_old_instance_id IS NOT NULL THEN
458 csm_item_instance_event_pkg.item_instances_acc_d(p_instance_id=>p_old_instance_id,
459 p_user_id=>l_csm_debrfLnUpdInit_rec.user_id,
460 p_error_msg=>l_error_msg,
461 x_return_status=>l_return_status);
462 END IF;
463 END IF;
464 END IF;
465
466 CSM_UTIL_PKG.LOG('Leaving DEBRIEF_LINE_UPD_INIT for debrief_line_id: ' || p_debrief_line_id,
467 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_UPD_INIT',FND_LOG.LEVEL_PROCEDURE);
468 EXCEPTION
469 WHEN OTHERS THEN
470 l_sqlerrno := to_char(SQLCODE);
471 l_sqlerrmsg := substr(SQLERRM, 1,2000);
472 l_error_msg := ' Exception in DEBRIEF_LINE_UPD_INIT for debrief_line_id:'
473 || to_char(p_debrief_line_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
474 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINE_UPD_INIT',FND_LOG.LEVEL_EXCEPTION);
475 RAISE;
476 END DEBRIEF_LINE_UPD_INIT;
477
478 PROCEDURE DEBRIEF_LINES_ACC_U(p_debrief_line_id IN NUMBER, p_billing_category IN VARCHAR2,
479 p_access_id IN NUMBER, p_user_id IN NUMBER)
480 IS
481 l_sqlerrno VARCHAR2(20);
482 l_sqlerrmsg VARCHAR2(4000);
483 l_error_msg VARCHAR2(4000);
484 l_return_status VARCHAR2(2000);
485
486 BEGIN
487 CSM_UTIL_PKG.LOG('Entering DEBRIEF_LINES_ACC_U for debrief_line_id: ' || p_debrief_line_id,
488 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINES_ACC_U',FND_LOG.LEVEL_PROCEDURE);
489
490 CSM_ACC_PKG.Update_Acc
491 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list(get_debrief_pi_name (p_billing_category))
492 ,P_ACC_TABLE_NAME => g_debrief_acc_table_name
493 ,P_ACCESS_ID => p_access_id
494 ,P_USER_ID => p_user_id
495 );
496
497 CSM_UTIL_PKG.LOG('Leaving DEBRIEF_LINES_ACC_U for debrief_line_id: ' || p_debrief_line_id,
498 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINES_ACC_U',FND_LOG.LEVEL_PROCEDURE);
499 EXCEPTION
500 WHEN OTHERS THEN
501 l_sqlerrno := to_char(SQLCODE);
502 l_sqlerrmsg := substr(SQLERRM, 1,2000);
503 l_error_msg := ' Exception in DEBRIEF_LINES_ACC_U for debrief_line_id:'
504 || to_char(p_debrief_line_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
505 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_DEBRIEF_EVENT_PKG.DEBRIEF_LINES_ACC_U',FND_LOG.LEVEL_EXCEPTION);
506 RAISE;
507 END DEBRIEF_LINES_ACC_U;
508
509 END CSM_DEBRIEF_EVENT_PKG;