[Home] [Help]
PACKAGE BODY: APPS.CSTPALPC
Source
1 PACKAGE BODY CSTPALPC AS
2 /* $Header: CSTALPCB.pls 120.11.12010000.2 2008/11/10 13:07:26 anjha ship $ */
3
4 l_debug_flag VARCHAR2(1) := FND_PROFILE.VALUE('MRP_DEBUG');
5 PROCEDURE dyn_proc_call (
6 i_proc_name IN VARCHAR2,
7 i_legal_entity IN NUMBER,
8 i_cost_type IN NUMBER,
9 i_cost_group IN NUMBER,
10 i_period_id IN NUMBER,
11 i_transaction_id IN NUMBER,
12 i_event_type_id IN VARCHAR2,
13 i_txn_type_flag IN VARCHAR2, -- 4586534
14 o_err_num OUT NOCOPY NUMBER,
15 o_err_code OUT NOCOPY VARCHAR2,
16 o_err_msg OUT NOCOPY VARCHAR2
17 )
18 IS
19 l_sql_to_run VARCHAR2(500);
20 CONC_STATUS BOOLEAN;
21 l_err NUMBER := 0;
22 CST_PKG_FAIL EXCEPTION;
23 CST_PKG_FAIL2 EXCEPTION;
24 l_stmt_num NUMBER;
25 BEGIN
26
27 l_stmt_num := 10;
28
29 l_sql_to_run := 'BEGIN ' || i_proc_name || '(';
30
31
32 l_sql_to_run := l_sql_to_run || ':I_LEGAL_ENTITY';
33 l_sql_to_run := l_sql_to_run || ', :I_COST_TYPE_ID';
34 l_sql_to_run := l_sql_to_run || ', :I_COST_GROUP_ID';
35 l_sql_to_run := l_sql_to_run || ', :I_PERIOD_ID';
36 l_sql_to_run := l_sql_to_run || ', :I_TRANSACTION_ID';
37 l_sql_to_run := l_sql_to_run || ', :I_EVENT_TYPE_ID';
38 l_sql_to_run := l_sql_to_run || ', :I_TXN_TYPE_FLAG'; -- 4586534
39 l_sql_to_run := l_sql_to_run || ', :O_ERR_NUM';
40 l_sql_to_run := l_sql_to_run || ', :O_ERR_CODE';
41 l_sql_to_run := l_sql_to_run || ', :O_ERR_MSG';
42 l_sql_to_run := l_sql_to_run || '); END;';
43
44 l_stmt_num := 20;
45
46 EXECUTE IMMEDIATE l_sql_to_run USING
47 I_LEGAL_ENTITY,
48 I_COST_TYPE,
49 I_COST_GROUP,
50 I_PERIOD_ID,
51 I_TRANSACTION_ID,
52 I_EVENT_TYPE_ID,
53 I_TXN_TYPE_FLAG,
54 OUT O_ERR_NUM,
55 OUT O_ERR_CODE,
56 OUT O_ERR_MSG;
57 IF (o_err_num <> 0 and o_err_num is not null) THEN
58 RAISE CST_PKG_FAIL;
59 END IF;
60
61 IF(l_err <> 0) THEN
62 RAISE CST_PKG_FAIL2;
63 END IF;
64
65 EXCEPTION
66 WHEN CST_PKG_FAIL THEN
67 fnd_file.put_line(fnd_file.log,o_err_msg);
68 fnd_file.put_line(fnd_file.log,'CSTPALPC.dyn_proc_call : Error Calling Package');
69 WHEN CST_PKG_FAIL2 THEN
70 o_err_num := l_err;
71 o_err_code := SQLCODE;
72 o_err_msg := 'CSTPALPC.dyn_proc_call ('||l_err||'): Error Calling Package';
73 fnd_file.put_line(fnd_file.log,o_err_msg);
74 WHEN OTHERS THEN
75 o_err_num := 30002;
76 o_err_code := SQLCODE;
77 o_err_msg := 'CSTPALPC.dyn_proc_call : ' || to_char(l_stmt_num) || ':'|| substr(SQLERRM,1,180);
78 fnd_file.put_line(fnd_file.log,o_err_msg);
79 END dyn_proc_call;
80
81
82 PROCEDURE create_acct_entry (
83 i_acct_lib_id IN NUMBER,
84 i_legal_entity IN NUMBER,
85 i_cost_type_id IN NUMBER,
86 i_cost_group_id IN NUMBER,
87 i_period_id IN NUMBER,
88 i_mode IN NUMBER,
89 o_err_num OUT NOCOPY NUMBER,
90 o_err_code OUT NOCOPY VARCHAR2,
91 o_err_msg OUT NOCOPY VARCHAR2
92 ) IS
93 l_err_rec CSTPALTY.CST_AE_ERR_REC_TYPE;
94 l_stmt_num NUMBER;
95 BEGIN
96 IF l_debug_flag = 'Y' THEN
97 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Create_Acct_Entry <<< ');
98 END IF;
99
100 IF (i_mode = 0) THEN
101 /* Normal Mode */
102 l_stmt_num := 10;
103 CSTPALPC.create_dist_entry (
104 i_acct_lib_id => i_acct_lib_id,
105 i_legal_entity => i_legal_entity,
106 i_cost_type_id => i_cost_type_id,
107 i_cost_group_id => i_cost_group_id,
108 i_period_id => i_period_id,
109 o_err_num => o_err_num,
110 o_err_code => o_err_code,
111 o_err_msg => o_err_msg);
112 ELSE
113 /* Period End */
114 l_stmt_num := 20;
115 CSTPALPC.create_per_end_entry (
116 i_acct_lib_id => i_acct_lib_id,
117 i_legal_entity => i_legal_entity,
118 i_cost_type_id => i_cost_type_id,
119 i_cost_group_id => i_cost_group_id,
120 i_period_id => i_period_id,
121 o_err_num => o_err_num,
122 o_err_code => o_err_code,
123 o_err_msg => o_err_msg);
124 END IF;
125 IF l_debug_flag = 'Y' THEN
126 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Create_Acct_Entry >>> ');
127 END IF;
128 EXCEPTION
129 WHEN OTHERS THEN
130 o_err_num := 30001;
131 o_err_code := SQLCODE;
132 o_err_msg := 'CSTPALPC.create_acct_entry : ' || to_char(l_stmt_num) || ' :'|| substr(SQLERRM,1,180);
133 fnd_file.put_line(fnd_file.log,l_err_rec.l_err_msg);
134
135 END create_acct_entry;
136
137 PROCEDURE create_dist_entry (
138 i_acct_lib_id IN NUMBER,
139 i_legal_entity IN NUMBER,
140 i_cost_type_id IN NUMBER,
141 i_cost_group_id IN NUMBER,
142 i_period_id IN NUMBER,
143 o_err_num OUT NOCOPY NUMBER,
144 o_err_code OUT NOCOPY VARCHAR2,
145 o_err_msg OUT NOCOPY VARCHAR2
146 ) IS
147
148 CURSOR c_txns IS
149 SELECT mmt.transaction_id "TRANSACTION_ID",
150 mmt.transaction_action_id "TRANSACTION_ACTION_ID",
151 mmt.transaction_source_type_id "TRANSACTION_SOURCE_TYPE_ID",
152 mmt.transaction_type_id "TRANSACTION_TYPE_ID",
153 to_char(null) "TRANSACTION_TYPE",
154 (to_char(mtt.transaction_type_id)||'-'||to_char(mtt.transaction_action_id)||'-'||to_char(mtt.transaction_source_type_id)) "EVENT_TYPE", -- 4986702
155 'INV' "TRANSACTION_TYPE_FLAG", -- 4986702
156 mmt.trx_source_line_id "TRX_SOURCE_LINE_ID"
157 FROM mtl_material_transactions mmt,
158 cst_cost_groups ccg,
159 cst_cost_group_assignments ccga,
160 cst_pac_periods cpp,
161 mtl_transaction_types mtt /* Removed the access to view to directly access the base tables Bug 4968702 */
162 WHERE
163 /* Periodic Cost Updates have the item master organization_id as
164 the organization_id in MMT. In this case, the org_cost_group
165 ID is stamped in MMT */
166
167 /* For Internal Order and Ordinary Interorg Intransit Shipment and Receipt,pick up
168 intermediate transactions as well */
169
170 mmt.organization_id = decode(mmt.transaction_type_id,
171 26, decode(nvl(mmt.org_cost_group_id, -1), ccga.cost_group_id, mmt.organization_id, ccga.organization_id),
172 21,decode(mmt.fob_point,
173 1,mmt.organization_id,
174 ccga.organization_id),
175 62,decode(mmt.fob_point,
176 1,mmt.organization_id,
177 ccga.organization_id),
178 12,decode(mmt.fob_point,
179 2,mmt.organization_id,
180 ccga.organization_id),
181 61,decode(mmt.fob_point,
182 2,mmt.organization_id,
183 ccga.organization_id),
184 ccga.organization_id)
185 AND mmt.organization_id = decode(mmt.transaction_type_id,
186 21,decode(mmt.fob_point,
187 1,mmt.organization_id,
188 NVL(mmt.owning_organization_id, mmt.organization_id)),
189 62,decode(mmt.fob_point,
190 1,mmt.organization_id,
191 NVL(mmt.owning_organization_id, mmt.organization_id)),
192 12,decode(mmt.fob_point,
193 2,mmt.organization_id,
194 NVL(mmt.owning_organization_id, mmt.organization_id)),
195 61,decode(mmt.fob_point,
196 2,mmt.organization_id,
197 NVL(mmt.owning_organization_id, mmt.organization_id)),
198 nvl(mmt.owning_organization_id, mmt.organization_id))
199 AND nvl(mmt.owning_tp_type,2) = 2
200 AND ccga.cost_group_id = ccg.cost_group_id
201 AND ccg.cost_group_id = i_cost_group_id
202 AND ccg.legal_entity = i_legal_entity
203 AND mmt.transaction_date BETWEEN trunc(cpp.period_start_date)
204 AND (trunc(cpp.period_end_date) + 0.99999)
205 AND cpp.pac_period_id = i_period_id
206 --AND caet.transaction_type_flag = 'INV'
207 AND mtt.transaction_type_id = mmt.transaction_type_id -- Join with the base tables.Bug 4968702
208 AND mtt.transaction_action_id = mmt.transaction_action_id
209 AND mtt.transaction_source_type_id = mmt.transaction_source_type_id
210 /* Drop Ship/Global Proc:
211 These transactions will be picked up from the union statement below. For now
212 omit any Drop Ship/Global Proc transactions. */
213 AND mmt.parent_transaction_id is null
214 AND EXISTS (
215 SELECT 1
216 FROM mtl_pac_actual_cost_details mpacd
217 WHERE
218 mpacd.transaction_id = mmt.transaction_id
219 AND mpacd.pac_period_id = i_period_id
220 AND mpacd.cost_group_id = ccga.cost_group_id
221 AND mpacd.cost_group_id = i_COST_GROUP_ID
222 )
223
224 UNION
225 /* This section will pick up all global procurement and Drop shipment txns
226 However, parent physical transactions are omitted, since they have no
227 ditributions against them */
228 select
229 mmt.transaction_id "TRANSACTION_ID",
230 mmt.transaction_action_id "TRANSACTION_ACTION_ID",
231 mmt.transaction_source_type_id "TRANSACTION_SOURCE_TYPE_ID",
232 mmt.transaction_type_id "TRANSACTION_TYPE_ID",
233 to_char(null) "TRANSACTION_TYPE",
234 (to_char(mtt.transaction_type_id)||'-'||to_char(mtt.transaction_action_id)||'-'||to_char(mtt.transaction_source_type_id)) "EVENT_TYPE", -- Bug 4968702
235 'INV' "TRANSACTION_TYPE_FLAG", -- Bug 4968702
236 mmt.trx_source_line_id "TRX_SOURCE_LINE_ID"
237 FROM
238 mtl_material_transactions mmt,
239 cst_cost_groups ccg,
240 cst_cost_group_assignments ccga ,
241 cst_pac_periods cpp,
242 mtl_material_transactions mtt -- Directly Using the base tables. Bug 4968702
243 WHERE
244 mmt.organization_id = ccga.organization_id AND
245 mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id) AND
246 nvl(mmt.owning_tp_type,2) = 2 AND
247 ccga.cost_group_id = ccg.cost_group_id AND
248 ccg.cost_group_id = i_cost_group_id AND
249 ccg.legal_entity = i_legal_entity AND
250 mmt.transaction_date BETWEEN trunc(cpp.period_start_date)AND (trunc(cpp.period_end_date) + 0.99999) AND
251 cpp.pac_period_id = i_PERIOD_ID AND /* Joining with the base tables Bug 4968702 */
252 mtt.transaction_type_id = mmt.transaction_type_id AND
253 mtt.transaction_action_id = mmt.transaction_action_id AND
254 mtt.transaction_source_type_id = mmt.transaction_source_type_id AND
255 mmt.parent_transaction_id is not null AND
256 nvl(mmt.logical_transaction, 2) = 1
257 UNION
258 SELECT
259 wt.transaction_id "TRANSACTION_ID",
260 to_number(null) "TRANSACTION_ACTION_ID",
261 to_number(null) "TRANSACTION_SOURCE_TYPE_ID",
262 wt.transaction_type "TRANSACTION_TYPE_ID",
263 to_char(null) "TRANSACTION_TYPE", -- Directly taking data from mfg_lookups instead of the caet view Bug 4968702
264 (SELECT to_char(lookup_code) FROM mfg_lookups WHERE lookup_type = 'WIP_TRANSACTION_TYPE' AND lookup_code = wt.transaction_type) "EVENT_TYPE", --Bug 4968702
265 'WIP' "TRANSACTION_TYPE_FLAG", -- 4968702
266 NULL "TRX_SOURCE_LINE_ID"
267 FROM
268 wip_transactions wt,
269 cst_cost_groups ccg,
270 cst_cost_group_assignments ccga,
271 cst_pac_periods cpp
272 WHERE
273 wt.organization_id = ccga.organization_id AND
274 ccga.cost_group_id = ccg.cost_group_id AND
275 ccg.cost_group_id = i_cost_group_id AND
276 ccg.legal_entity = i_legal_entity AND
277 cpp.pac_period_id = i_period_id AND
278 wt.transaction_date BETWEEN trunc(cpp.period_start_date)
279 AND (trunc(cpp.period_end_date) + 0.99999) AND
280 (wt.transaction_type = 17 -- Added 17 to support Direct Items as part of eAM support in PAC
281 OR (wt.transaction_type in (1,2,3,6) AND
282 EXISTS (SELECT 1
283 FROM wip_pac_actual_cost_details wpacd
284 WHERE wpacd.transaction_id = wt.transaction_id AND
285 wpacd.pac_period_id = i_period_id AND
286 wpacd.cost_group_id = i_cost_group_id)
287 ))
288 UNION
289 /* Drop Shipment changes: Omit any true drop shipment transactions */
290 SELECT
291 rt.transaction_id "TRANSACTION_ID",
292 to_number(null) "TRANSACTION_ACTION_ID",
293 to_number(null) "TRANSACTION_SOURCE_TYPE_ID",
294 to_number(null) "TRANSACTION_TYPE_ID",
295 rt.transaction_type "TRANSACTION_TYPE",
296 plc.lookup_code "EVENT_TYPE", --4968702 Directly taking data from mfg_lookup_codes instead of view caet
297 'RCV' "TRANSACTION_TYPE_FLAG", -- 4968702
298 NULL "TRX_SOURCE_LINE_ID"
299 FROM
300 rcv_transactions rt,
301 cst_cost_groups ccg,
302 cst_cost_group_assignments ccga,
303 cst_pac_periods cpp,
304 po_lookup_codes plc
305 WHERE
306 rt.organization_id = ccga.organization_id AND
307 NVL(rt.consigned_flag,'N') = 'N' AND
308 NVL(rt.dropship_type_code, 3) <> 2 AND -- FP BUG 5845861 do not pick up txn when DS with old accounting
309 ccga.cost_group_id = ccg.cost_group_id AND
310 ccg.cost_group_id = i_cost_group_id AND
311 ccg.legal_entity = i_legal_entity AND
312 rt.transaction_date BETWEEN trunc(cpp.period_start_date)
313 AND (trunc(cpp.period_end_date) + 0.99999) AND
314 cpp.pac_period_id = i_PERIOD_ID AND
315 plc.lookup_type = 'RCV TRANSACTION TYPE' AND --joining with po_look_up codes. Bug 4968702
316 plc.lookup_code = rt.transaction_type AND
317 rt.source_document_code = 'PO' AND
318 ((rt.transaction_type = 'RECEIVE' AND rt.parent_transaction_id = -1)
319 OR (rt.transaction_type in ('MATCH','RETURN TO VENDOR','RETURN TO RECEIVING','DELIVER'))
320 OR (rt.transaction_type = 'CORRECT' and rt.parent_transaction_id IN
321 (select rt2.transaction_id from rcv_transactions rt2
322 where
323 (rt2.transaction_type = 'RECEIVE' AND rt2.parent_transaction_id = -1) OR
324 (rt2.transaction_type in ('RETURN TO VENDOR','RETURN TO RECEIVING','MATCH','DELIVER'))
325 )
326 )
327 )
328 --pick up global procurement receipts even if set to period end accrual,
329 --since period end will apply only to supplier facing org.
330 AND exists (
331 select 1
332 from po_line_locations_all poll
333 where poll.line_location_id = rt.po_line_location_id and
334 poll.shipment_type <>'PREPAYMENT' and -- Added for complex work procurement
335 (poll.transaction_flow_header_id is not null or
336 ( poll.accrue_on_receipt_flag = 'Y' and
337 not exists (
338 select 1
339 from po_distributions_all pod
340 where pod.line_location_id = poll.line_location_id and
341 accrue_on_receipt_flag = 'N')))
342 )
343 UNION
344 SELECT
345 rae.accounting_event_id "TRANSACTION_ID",
346 to_number(null) "TRANSACTION_ACTION_ID",
347 to_number(null) "TRANSACTION_SOURCE_TYPE_ID",
348 to_number(null) "TRANSACTION_TYPE_ID", -- Removing the usage of CAET here
349 decode(rae.event_type_id, 9, 'LOGICAL RECEIVE', 10, 'LOGICAL RETURN TO VENDOR') "TRANSACTION_TYPE", -- Bug 4968702 Directly Using Base table
350 decode(rae.event_type_id, 9, 'LOGICAL RECEIVE', 10, 'LOGICAL RETURN TO VENDOR') "EVENT_TYPE",-- Bug 4968702 Directly Using Base table
351 'RAE' "TRANSACTION_TYPE_FLAG", -- Bug 4968702
352 NULL "TRX_SOURCE_LINE_ID"
353 FROM
354 rcv_accounting_events rae,
355 rcv_transactions rt,
356 cst_cost_groups ccg,
357 cst_cost_group_assignments ccga,
358 cst_pac_periods cpp
359
360 WHERE
361 rae.organization_id = ccga.organization_id AND
362 rae.rcv_transaction_id = rt.transaction_id AND
363 ccga.cost_group_id = ccg.cost_group_id AND
364 ccg.cost_group_id = i_cost_group_id AND
365 ccg.legal_entity = i_legal_entity AND
366 rae.transaction_date BETWEEN trunc(cpp.period_start_date)
367 AND (trunc(cpp.period_end_date) + 0.99999) AND
368 cpp.pac_period_id = i_PERIOD_ID AND
369 cpp.legal_entity =ccg.legal_entity AND
370 rae.event_source = 'RECEIVING' AND
371 -- rae.trx_flow_header_id is not null AND /*Bug 5263514*/
372 rae.event_type_id in (9,10) --Logical Receive OR Logical Return to Vendor
373 --omit logical transactions from supplier facing org if accrual option is
374 --period end.
375 AND ( nvl(rae.procurement_org_flag, 'N') = 'N'
376 OR EXISTS (
377 select 1
378 from po_line_locations_all poll
379 where poll.line_location_id = rt.po_line_location_id and
380 poll.accrue_on_receipt_flag = 'Y' and
381 not exists (
382 select 1
383 from po_distributions_all pod
384 where pod.line_location_id = poll.line_location_id and
385 accrue_on_receipt_flag = 'N')
386 ))
387
388 -- Retro Changes---------------------------------------------------------
389 UNION
390 SELECT RAE.ACCOUNTING_EVENT_ID "TRANSACTION_ID",
391 TO_NUMBER(NULL) "TRANSACTION_ACTION_ID",
392 TO_NUMBER(NULL) "TRANSACTION_SOURCE_TYPE_ID",
393 TO_NUMBER(NULL) "TRANSACTION_TYPE_ID",
394 'Adjust Receive' "TRANSACTION_TYPE",
395 'ADJUST RECEIVE' "EVENT_TYPE",
396 'ADJ' "TRANSACTION_TYPE_FLAG",
397 NULL "TRX_SOURCE_LINE_ID"
398 FROM RCV_ACCOUNTING_EVENTS RAE,
399 RCV_TRANSACTIONS RT,
400 CST_COST_GROUPS CCG,
401 CST_COST_GROUP_ASSIGNMENTS CCGA,
402 CST_PAC_PERIODS CPP
403 WHERE RAE.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
404 AND CCGA.COST_GROUP_ID = CCG.COST_GROUP_ID
405 AND CCG.COST_GROUP_ID = i_cost_group_id
406 AND CCG.LEGAL_ENTITY = i_legal_entity
407 AND RAE.TRANSACTION_DATE BETWEEN TRUNC(CPP.PERIOD_START_DATE)
408 AND (TRUNC(CPP.PERIOD_END_DATE) + 0.99999)
409 AND CPP.PAC_PERIOD_ID = i_PERIOD_ID
410 AND RT.TRANSACTION_ID = RAE.RCV_TRANSACTION_ID
411 AND RT.TRANSACTION_DATE < CPP.PERIOD_START_DATE
412 AND RAE.EVENT_TYPE_ID = 7
413 -- EVENT_TYPE_ID = 7 refers to ADJUST_RECEIVE
414 -- Number used to avoid RCV dependencies
415 -------------------------------------------------------------------------
416 /*----LCM CHANGE------------------------------------------*/
417 UNION
418 SELECT RAE.ACCOUNTING_EVENT_ID "TRANSACTION_ID",
419 TO_NUMBER(NULL) "TRANSACTION_ACTION_ID",
420 TO_NUMBER(NULL) "TRANSACTION_SOURCE_TYPE_ID",
421 TO_NUMBER(NULL) "TRANSACTION_TYPE_ID",
422 decode(RAE.EVENT_TYPE_ID,18,'PAC LC ADJ REC',
423 19,'PAC LC ADJ DEL ASSET',
424 20,'PAC LC ADJ DEL EXP') "TRANSACTION_TYPE",
425 decode(RAE.EVENT_TYPE_ID,18,'PAC LC ADJ REC',
426 19,'PAC LC ADJ DEL ASSET',
427 20,'PAC LC ADJ DEL EXP') "EVENT_TYPE",
428 'LC ADJ' "TRANSACTION_TYPE_FLAG",
429 NULL "TRX_SOURCE_LINE_ID"
430 FROM RCV_ACCOUNTING_EVENTS RAE,
431 RCV_TRANSACTIONS RT,
432 CST_COST_GROUPS CCG,
433 CST_COST_GROUP_ASSIGNMENTS CCGA,
434 CST_PAC_PERIODS CPP
435 WHERE RAE.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
436 AND CCGA.COST_GROUP_ID = CCG.COST_GROUP_ID
437 AND CCG.COST_GROUP_ID = i_cost_group_id
438 AND CCG.LEGAL_ENTITY = i_legal_entity
439 AND RAE.TRANSACTION_DATE BETWEEN TRUNC(CPP.PERIOD_START_DATE)
440 AND (TRUNC(CPP.PERIOD_END_DATE) + 0.99999)
441 AND CPP.PAC_PERIOD_ID = i_PERIOD_ID
442 AND RT.TRANSACTION_ID = RAE.RCV_TRANSACTION_ID
443 AND RT.TRANSACTION_DATE < CPP.PERIOD_START_DATE
444 AND RAE.EVENT_TYPE_ID in (18,19,20);
445
446 l_err_rec CSTPALTY.CST_AE_ERR_REC_TYPE;
447 l_sql_to_run VARCHAR2(500);
448 l_package_name VARCHAR2(100);
449 l_cursor NUMBER;
450 l_event_type_id VARCHAR2(30);
451 l_accounting_package_id NUMBER;
452 l_event_pkg_exists NUMBER;
453 l_stmt_num NUMBER;
454 l_txn_count NUMBER;
455 l_transaction_type_flag VARCHAR2(100);
456 l_period_end NUMBER;
457 l_so_issue_exists NUMBER;
458 CONC_STATUS BOOLEAN;
459 CST_PKG_CALL_FAIL EXCEPTION;
460 BEGIN
461
462 l_stmt_num := 5;
463 IF l_debug_flag = 'Y' THEN
464 fnd_file.put_line(fnd_file.log, 'Create_Dist_Entry <<< ');
465 END IF;
466 l_txn_count := 0;
467
468 FOR c_txns_rec IN c_txns LOOP
469 l_stmt_num := 10;
470 IF l_debug_flag = 'Y' THEN
471 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Transaction: '||to_char(c_txns_rec.transaction_id));
472 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Transaction Type: '||c_txns_rec.transaction_type);
473 END IF;
474 l_transaction_type_flag := c_txns_rec.transaction_type_flag;
475 l_event_type_id := c_txns_rec.event_type;
476
477 l_stmt_num := 20;
478
479 SELECT
480 count(*)
481 INTO
482 l_event_pkg_exists
483 FROM
484 cst_acct_lib_packages calp2
485 WHERE
486 calp2.accounting_lib_id = i_acct_lib_id AND
487 calp2.event_type_id = l_event_type_id;
488
489 IF (l_event_pkg_exists > 0) THEN
490
491 l_stmt_num := 30;
492
493 SELECT
494 cap.accounting_package_id,
495 cap.package_name
496 INTO
497 l_accounting_package_id,
498 l_package_name
499 FROM
500 cst_acct_lib_packages calp,
501 cst_accounting_packages cap
502 WHERE
503 calp.accounting_lib_id = i_acct_lib_id AND
504 calp.event_type_id = l_event_type_id AND
505 cap.accounting_package_id = calp.accounting_package_id;
506
507 ELSE
508
509 l_stmt_num := 40;
510
511 SELECT
512 cap.accounting_package_id,
513 cap.package_name
514 INTO
515 l_accounting_package_id,
516 l_package_name
517 FROM
518 cst_acct_lib_packages calp,
519 cst_accounting_packages cap
520 WHERE
521 calp.accounting_lib_id = i_acct_lib_id AND
522 calp.event_type_id IS NULL AND
523 cap.accounting_package_id = calp.accounting_package_id;
524
525 END IF;
526 IF l_debug_flag = 'Y' THEN
527 fnd_file.put_line(fnd_file.log,'Calling Package '||(l_package_name) ||' ...');
528 END IF;
529
530 l_so_issue_exists := 1;
531
532 IF (c_txns_rec.transaction_type_flag = 'INV' AND
533 c_txns_rec.transaction_action_id = 36 AND
534 c_txns_rec.transaction_source_type_id = 2) THEN
535
536 BEGIN
537 l_stmt_num := 45;
538 SELECT COUNT(1)
539 INTO l_so_issue_exists
540 FROM cst_revenue_cogs_match_lines crcml
541 WHERE cogs_om_line_id = c_txns_rec.trx_source_line_id
542 AND pac_cost_type_id = i_cost_type_id;
543
544 EXCEPTION
545 WHEN NO_DATA_FOUND THEN
546 l_so_issue_exists := 0;
547 END;
548 END IF;
549
550 IF (l_so_issue_exists <> 0) THEN
551
552 dyn_proc_call(
553 l_package_name,
554 i_legal_entity,
555 i_cost_type_id ,
556 i_cost_group_id ,
557 i_period_id ,
558 c_txns_rec.transaction_id,
559 l_event_type_id,
560 l_transaction_type_flag,
561 l_err_rec.l_err_num ,
562 l_err_rec.l_err_code ,
563 l_err_rec.l_err_msg
564 );
565
566 IF (l_err_rec.l_err_num <> 0 and l_err_rec.l_err_num is not null) THEN
567 RAISE CST_PKG_CALL_FAIL;
568 END IF;
569 END IF; -- End of l_so_issue_exists
570
571 l_txn_count := l_txn_count + 1;
572 END LOOP;
573
574 IF l_debug_flag = 'Y' THEN
575 fnd_file.put_line(fnd_file.log,'Total Transactions processed : '||to_char(l_txn_count));
576 fnd_file.put_line(fnd_file.log, 'Create_Dist_Entry >>> ');
577 END IF;
578
579 COMMIT;
580
581 EXCEPTION
582 WHEN CST_PKG_CALL_FAIL THEN
583 o_err_num := l_err_rec.l_err_num;
584 o_err_code := l_err_rec.l_err_code;
585 o_err_msg := l_err_rec.l_err_msg || ': CSTPALPC.create_dist_entry : ' || to_char(l_stmt_num);
586 fnd_file.put_line(fnd_file.log,o_err_msg);
587 WHEN OTHERS THEN
588 o_err_num := 30002;
589 o_err_code := SQLCODE;
590 o_err_msg := 'CSTPALPC.create_dist_entry : ' || to_char(l_stmt_num) || ':'|| substr(SQLERRM,1,180);
591 fnd_file.put_line(fnd_file.log,l_err_rec.l_err_msg);
592
593 END create_dist_entry;
594
595 PROCEDURE create_per_end_entry (
596 i_acct_lib_id IN NUMBER,
597 i_legal_entity IN NUMBER,
598 i_cost_type_id IN NUMBER,
599 i_cost_group_id IN NUMBER,
600 i_period_id IN NUMBER,
601 o_err_num OUT NOCOPY NUMBER,
602 o_err_code OUT NOCOPY VARCHAR2,
603 o_err_msg OUT NOCOPY VARCHAR2
604 ) IS
605
606 ---------------------------------------------------------------------------
607 -- Complex Work Procurement Changes
608 -- Transactions related to PREPAYMENT shipment type should not be considered.
609 ---------------------------------------------------------------------------
610 CURSOR c_txns IS
611 SELECT
612 rt.transaction_id "TRANSACTION_ID",
613 to_number(null) "TRANSACTION_ACTION_ID",
614 to_number(null) "TRANSACTION_SOURCE_TYPE_ID",
615 to_number(null) "TRANSACTION_TYPE_ID",
616 rt.transaction_type "TRANSACTION_TYPE",
617 caet.event_type "EVENT_TYPE",
618 caet.transaction_type_flag "TRANSACTION_TYPE_FLAG"
619 FROM
620 rcv_transactions rt,
621 cst_cost_groups ccg,
622 cst_cost_group_assignments ccga,
623 cst_pac_periods cpp,
624 cst_accounting_event_types_v caet
625 WHERE
626 rt.organization_id = ccga.organization_id AND
627 NVL(rt.consigned_flag,'N') = 'N' AND
628 ccga.cost_group_id = ccg.cost_group_id AND
629 ccg.cost_group_id = i_cost_group_id AND
630 ccg.legal_entity = i_legal_entity AND
631 rt.transaction_date <= (trunc(cpp.period_end_date) + 0.99999) AND
632 cpp.pac_period_id = i_period_id AND
633 rt.source_document_code = 'PO' AND
634 caet.transaction_type_flag = 'ACR' AND
635 ((rt.transaction_type = 'RECEIVE' AND rt.parent_transaction_id = -1)
636 OR
637 (rt.transaction_type = 'MATCH'))
638 AND exists (
639 select
640 1
641 from
642 po_line_locations_all poll,
643 po_headers_all poh /*Added for bug 5352511 */
644 where poll.line_location_id = rt.po_line_location_id and
645 poll.accrue_on_receipt_flag = 'N' and /* Begin Bug5352511 */
646 poh.po_header_id = poll.po_header_id and
647 ( (nvl(poll.closed_date,poh.closed_date) >=
648 (trunc(cpp.period_end_date)+0.9999)) OR
649 (nvl(poh.closed_date,poll.closed_date) is null)
650 ) and /* End Bug 5352511 */
651 poll.shipment_type <> 'PREPAYMENT' and
652 not exists (
653 select
654 1
655 from
656 po_distributions_all pod
657 where pod.line_location_id = poll.line_location_id and
658 accrue_on_receipt_flag = 'Y'));
659
660 l_err_rec CSTPALTY.CST_AE_ERR_REC_TYPE;
661 l_sql_to_run VARCHAR2(500);
662 l_package_name VARCHAR2(100);
663 l_cursor NUMBER;
664 l_event_type_id VARCHAR2(30);
665 l_accounting_package_id NUMBER;
666 l_event_pkg_exists NUMBER;
667 l_stmt_num NUMBER;
668 l_txn_count NUMBER;
669 l_transaction_type_flag VARCHAR2(100);
670 l_period_end NUMBER;
671 CONC_STATUS BOOLEAN;
672 CST_PKG_CALL_FAIL EXCEPTION;
673 BEGIN
674
675
676 fnd_file.put_line(fnd_file.log,'CSTPALPC.create_per_end_entries in');
677 l_txn_count := 0;
678
679 FOR c_txns_rec IN c_txns LOOP
680
681 l_transaction_type_flag := c_txns_rec.transaction_type_flag;
682 fnd_file.put_line(fnd_file.log,'Processing Transaction : '||to_char(c_txns_rec.transaction_id));
683
684 l_stmt_num := 10;
685 l_event_type_id := c_txns_rec.EVENT_TYPE;
686
687 l_stmt_num := 20;
688
689 SELECT
690 count(*)
691 INTO
692 l_event_pkg_exists
693 FROM
694 cst_acct_lib_packages calp2
695 WHERE
696 calp2.accounting_lib_id = i_acct_lib_id AND
697 calp2.event_type_id = l_event_type_id;
698
699 IF (l_event_pkg_exists > 0) THEN
700
701 l_stmt_num := 30;
702
703 SELECT
704 cap.accounting_package_id,
705 cap.package_name
706 INTO
707 l_accounting_package_id,
708 l_package_name
709 FROM
710 cst_acct_lib_packages calp,
711 cst_accounting_packages cap
712 WHERE
713 calp.accounting_lib_id = i_acct_lib_id AND
714 calp.event_type_id = l_event_type_id AND
715 cap.accounting_package_id = calp.accounting_package_id;
716
717 ELSE
718
719 l_stmt_num := 40;
720
721 SELECT
722 cap.accounting_package_id,
723 cap.package_name
724 INTO
725 l_accounting_package_id,
726 l_package_name
727 FROM
728 cst_acct_lib_packages calp,
729 cst_accounting_packages cap
730 WHERE
731 calp.accounting_lib_id = i_acct_lib_id AND
732 calp.event_type_id IS NULL AND
733 cap.accounting_package_id = calp.accounting_package_id;
734
735 END IF;
736
737 fnd_file.put_line(fnd_file.log,'Calling Package '||(l_package_name) ||' ...');
738
739 dyn_proc_call(
740 l_package_name,
741 i_legal_entity,
742 i_cost_type_id ,
743 i_cost_group_id ,
744 i_period_id ,
745 c_txns_rec.transaction_id,
746 l_event_type_id,
747 l_transaction_type_flag,
748 l_err_rec.l_err_num ,
749 l_err_rec.l_err_code ,
750 l_err_rec.l_err_msg
751 );
752
753 IF (l_err_rec.l_err_num <> 0 and l_err_rec.l_err_num is not null) THEN
754 RAISE CST_PKG_CALL_FAIL;
755 END IF;
756 l_txn_count := l_txn_count + 1;
757
758 END LOOP;
759 fnd_file.put_line(fnd_file.log,'Total Transactions processed : '||to_char(l_txn_count));
760
761 --COMMIT;
762
763 EXCEPTION
764 WHEN CST_PKG_CALL_FAIL THEN
765 o_err_num := l_err_rec.l_err_num;
766 o_err_code := l_err_rec.l_err_code;
767 o_err_msg := l_err_rec.l_err_msg;
768 WHEN OTHERS THEN
769 o_err_num := 30002;
770 o_err_code := SQLCODE;
771 o_err_msg := 'CSTPALPC.create_per_end_entry : ' || to_char(l_stmt_num) || ':'|| substr(SQLERRM,1,180);
772 fnd_file.put_line(fnd_file.log,l_err_rec.l_err_msg);
773
774 END create_per_end_entry;
775
776
777
778 PROCEDURE insert_ae_lines (
779 i_ae_txn_rec IN CSTPALTY.cst_ae_txn_rec_type,
780 i_ae_line_rec_tbl IN CSTPALTY.cst_ae_line_tbl_type,
781 o_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type
782 )
783 IS
784 l_ae_header_id NUMBER;
785 l_err_rec CSTPALTY.CST_AE_ERR_REC_TYPE;
786 l_stmt_num NUMBER;
787 l_request_id NUMBER;
788 l_user_id NUMBER;
789 l_login_id NUMBER;
790 l_prog_appl_id NUMBER;
791 l_prog_id NUMBER;
792 CONC_STATUS BOOLEAN;
793
794
795 BEGIN
796
797 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CSTPALPC.Insert_Ae_Lines <<< ');
798
799 l_request_id := FND_GLOBAL.conc_request_id;
800 l_user_id := FND_GLOBAL.user_id;
801 l_login_id := FND_GLOBAL.login_id;
802 l_prog_appl_id := FND_GLOBAL.prog_appl_id;
803 l_prog_id := FND_GLOBAL.conc_program_id;
804
805 l_stmt_num := 10;
806
807 SELECT
808 cst_ae_headers_s.NEXTVAL
809 INTO
810 l_ae_header_id
811 FROM
812 dual;
813
814
815 IF i_ae_line_rec_tbl.EXISTS(1) THEN
816
817 fnd_file.put_line(fnd_file.log,'Inserting in Headers table ...');
818
819 l_stmt_num := 20;
820
821 INSERT INTO
822 cst_ae_headers (
823 ae_header_id,
824 accounting_event_id,
825 set_of_books_id,
826 legal_entity_id,
827 cost_group_id,
828 cost_type_id,
829 ae_category,
830 period_id,
831 period_name,
832 accounting_date,
833 gl_transfer_flag,
834 gl_transfer_run_id,
835 description,
836 gl_transfer_error_code,
837 acct_event_source_table,
838 organization_id,
839 accounting_error_code,
840 creation_date,
841 created_by,
842 last_update_date,
843 last_updated_by,
844 last_update_login,
845 program_update_date,
846 program_application_id,
847 program_id,
848 request_id,
849 cross_currency_flag,
850 gl_reversal_flag
851 )
852 VALUES
853 (
854 l_ae_header_id,
855 i_ae_txn_rec.transaction_id,
856 i_ae_txn_rec.set_of_books_id,
857 i_ae_txn_rec.legal_entity_id,
858 i_ae_txn_rec.cost_group_id,
859 i_ae_txn_rec.cost_type_id,
860 decode(i_ae_txn_rec.ae_category,'RCV','Receiving',
861 'ACR','Accrual',
862 'ADJ','Receiving',
863 'RAE','Receiving',
864 'LC ADJ','Receiving',i_ae_txn_rec.ae_category),
865 i_ae_txn_rec.accounting_period_id,
866 i_ae_txn_rec.accounting_period_name,
867 i_ae_txn_rec.accounting_date,
868 'N',
869 -1,
870 i_ae_txn_rec.description, --description??
871 NULL, -- gl xfer error code
872 i_ae_txn_rec.source_table,
873 i_ae_txn_rec.organization_id,
874 NULL,
875 sysdate,
876 l_user_id,
877 sysdate,
878 l_user_id,
879 l_login_id,
880 sysdate,
881 l_prog_appl_id,
882 l_prog_id,
883 l_request_id,
884 NULL,
885 decode(i_ae_txn_rec.ae_category,'ACR','Y',NULL)
886 );
887
888 FOR i IN i_ae_line_rec_tbl.FIRST..i_ae_line_rec_tbl.LAST LOOP
889
890 fnd_file.put_line(fnd_file.log,'Inserting in Lines table ...');
891
892 l_stmt_num := 30;
893 IF (i_ae_line_rec_tbl(i).actual_flag = 'E') then
894
895 INSERT INTO
896 cst_encumbrance_lines (
897 encumbrance_line_id,
898 ae_header_id,
899 ae_line_number,
900 ae_line_type_code,
901 code_combination_id,
902 currency_code,
903 currency_conversion_type,
904 currency_conversion_date,
905 currency_conversion_rate,
906 entered_dr,
907 entered_cr,
908 accounted_dr,
909 accounted_cr,
910 source_table,
911 source_id,
912 rate_or_amount,
913 basis_type,
914 resource_id,
915 cost_element_id,
916 activity_id,
917 repetitive_schedule_id,
918 overhead_basis_factor,
919 basis_resource_id,
920 gl_sl_link_id,
921 description,
922 accounting_error_code,
923 stat_amount,
924 ussgl_transaction_code,
925 subledger_doc_sequence_id,
926 subledger_doc_sequence_value,
927 gl_transfer_error_code,
928 encumbrance_type_id,
929 po_distribution_id,
930 reference1,
931 reference2,
932 reference3,
933 reference4,
934 reference5,
935 reference6,
936 reference7,
937 reference8,
938 reference9,
939 reference10,
940 creation_date,
941 created_by,
942 last_update_date,
943 last_updated_by,
944 last_update_login,
945 program_update_date,
946 program_application_id,
947 program_id,
948 request_id
949 )
950 VALUES
951 (
952 cst_encumbrance_lines_s.nextval,
953 l_ae_header_id,
954 i,
955 i_ae_line_rec_tbl(i).ae_line_type,
956 i_ae_line_rec_tbl(i).account,
957 i_ae_line_rec_tbl(i).currency_code,
958 i_ae_line_rec_tbl(i).currency_conv_type,
959 i_ae_line_rec_tbl(i).currency_conv_date,
960 decode(i_ae_line_rec_tbl(i).currency_conv_rate,
961 -1,decode(i_ae_line_rec_tbl(i).currency_code,
962 NULL,NULL,
963 i_ae_line_rec_tbl(i).currency_conv_rate),
964 i_ae_line_rec_tbl(i).currency_conv_rate),
965 i_ae_line_rec_tbl(i).entered_dr,
966 i_ae_line_rec_tbl(i).entered_cr,
967 i_ae_line_rec_tbl(i).accounted_dr,
968 i_ae_line_rec_tbl(i).accounted_cr,
969 i_ae_line_rec_tbl(i).source_table, -- source table
970 i_ae_line_rec_tbl(i).source_id, -- source id
971 i_ae_line_rec_tbl(i).rate_or_amount,
972 i_ae_line_rec_tbl(i).basis_type,
973 i_ae_line_rec_tbl(i).resource_id,
974 i_ae_line_rec_tbl(i).cost_element_id,
975 i_ae_line_rec_tbl(i).activity_id,
976 i_ae_line_rec_tbl(i).repetitive_schedule_id,
977 i_ae_line_rec_tbl(i).overhead_basis_factor,
978 i_ae_line_rec_tbl(i).basis_resource_id,
979 NULL, -- gl_sl_link?? null
980 i_ae_line_rec_tbl(i).description, -- desc accting line desc
981 NULL, -- error code null
982 NULL, -- stat amount null
983 NULL, -- ussgl null
984 NULL, -- sub ledger doc seq id
985 NULL, -- sub ledger doc seq value
986 NULL, -- gl xfer error code
987 i_ae_line_rec_tbl(i).encum_type_id,
988 i_ae_line_rec_tbl(i).po_distribution_id,
989 i_ae_line_rec_tbl(i).reference1,
990 i_ae_line_rec_tbl(i).reference2,
991 i_ae_line_rec_tbl(i).reference3,
992 i_ae_line_rec_tbl(i).reference4,
993 i_ae_line_rec_tbl(i).reference5,
994 i_ae_line_rec_tbl(i).reference6,
995 i_ae_line_rec_tbl(i).reference7,
996 i_ae_line_rec_tbl(i).reference8,
997 i_ae_line_rec_tbl(i).reference9,
998 i_ae_line_rec_tbl(i).reference10,
999 sysdate,
1000 l_user_id,
1001 sysdate,
1002 l_user_id,
1003 l_login_id,
1004 sysdate,
1005 l_prog_appl_id,
1006 l_prog_id,
1007 l_request_id
1008 );
1009
1010 ELSE
1011
1012 INSERT INTO
1013 cst_ae_lines (
1014 ae_line_id,
1015 ae_header_id,
1016 ae_line_number,
1017 ae_line_type_code,
1018 code_combination_id,
1019 currency_code,
1020 currency_conversion_type,
1021 currency_conversion_date,
1022 currency_conversion_rate,
1023 entered_dr,
1024 entered_cr,
1025 accounted_dr,
1026 accounted_cr,
1027 source_table,
1028 source_id,
1029 rate_or_amount,
1030 basis_type,
1031 resource_id,
1032 cost_element_id,
1033 activity_id,
1034 repetitive_schedule_id,
1035 overhead_basis_factor,
1036 basis_resource_id,
1037 gl_sl_link_id,
1038 description,
1039 accounting_error_code,
1040 stat_amount,
1041 ussgl_transaction_code,
1042 subledger_doc_sequence_id,
1043 subledger_doc_sequence_value,
1044 gl_transfer_error_code,
1045 po_distribution_id,
1046 wip_entity_id,
1047 reference1,
1048 reference2,
1049 reference3,
1050 reference4,
1051 reference5,
1052 reference6,
1053 reference7,
1054 reference8,
1055 reference9,
1056 reference10,
1057 creation_date,
1058 created_by,
1059 last_update_date,
1060 last_updated_by,
1061 last_update_login,
1062 program_update_date,
1063 program_application_id,
1064 program_id,
1065 request_id
1066 )
1067 VALUES
1068 (
1069 cst_ae_lines_s.nextval,
1070 l_ae_header_id,
1071 i,
1072 i_ae_line_rec_tbl(i).ae_line_type,
1073 i_ae_line_rec_tbl(i).account,
1074 i_ae_line_rec_tbl(i).currency_code,
1075 i_ae_line_rec_tbl(i).currency_conv_type,
1076 i_ae_line_rec_tbl(i).currency_conv_date,
1077 decode(i_ae_line_rec_tbl(i).currency_conv_rate,
1078 -1,decode(i_ae_line_rec_tbl(i).currency_code,
1079 NULL,NULL,
1080 i_ae_line_rec_tbl(i).currency_conv_rate),
1081 i_ae_line_rec_tbl(i).currency_conv_rate),
1082 i_ae_line_rec_tbl(i).entered_dr,
1083 i_ae_line_rec_tbl(i).entered_cr,
1084 i_ae_line_rec_tbl(i).accounted_dr,
1085 i_ae_line_rec_tbl(i).accounted_cr,
1086 i_ae_line_rec_tbl(i).source_table, -- source table
1087 i_ae_line_rec_tbl(i).source_id, -- source id
1088 i_ae_line_rec_tbl(i).rate_or_amount,
1089 i_ae_line_rec_tbl(i).basis_type,
1090 i_ae_line_rec_tbl(i).resource_id,
1091 i_ae_line_rec_tbl(i).cost_element_id,
1092 i_ae_line_rec_tbl(i).activity_id,
1093 i_ae_line_rec_tbl(i).repetitive_schedule_id,
1094 i_ae_line_rec_tbl(i).overhead_basis_factor,
1095 i_ae_line_rec_tbl(i).basis_resource_id,
1096 NULL, -- gl_sl_link?? null
1097 i_ae_line_rec_tbl(i).description, -- desc accting line desc
1098 NULL, -- error code null
1099 NULL, -- stat amount null
1100 NULL, -- ussgl null
1101 NULL, -- sub ledger doc seq id
1102 NULL, -- sub ledger doc seq value
1103 NULL, -- gl xfer error code
1104 i_ae_line_rec_tbl(i).po_distribution_id,
1105 i_ae_line_rec_tbl(i).wip_entity_id,
1106 i_ae_line_rec_tbl(i).reference1,
1107 i_ae_line_rec_tbl(i).reference2,
1108 i_ae_line_rec_tbl(i).reference3,
1109 i_ae_line_rec_tbl(i).reference4,
1110 i_ae_line_rec_tbl(i).reference5,
1111 i_ae_line_rec_tbl(i).reference6,
1112 i_ae_line_rec_tbl(i).reference7,
1113 i_ae_line_rec_tbl(i).reference8,
1114 i_ae_line_rec_tbl(i).reference9,
1115 i_ae_line_rec_tbl(i).reference10,
1116 sysdate,
1117 l_user_id,
1118 sysdate,
1119 l_user_id,
1120 l_login_id,
1121 sysdate,
1122 l_prog_appl_id,
1123 l_prog_id,
1124 l_request_id
1125 );
1126 END IF;
1127
1128
1129 END LOOP;
1130
1131 END IF;
1132 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CSTPALPC.Insert_Ae_Lines >>> ');
1133
1134 EXCEPTION
1135 WHEN OTHERS THEN
1136 o_err_rec.l_err_num := 30001;
1137 o_err_rec.l_err_code := SQLCODE;
1138 o_err_rec.l_err_msg := 'CSTPALPC.insert_ae_lines : ' || to_char(l_stmt_num) || ':'|| substr(SQLERRM,1,180);
1139 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',o_err_rec.l_err_msg);
1140 fnd_file.put_line(fnd_file.log,o_err_rec.l_err_msg);
1141
1142
1143
1144 END insert_ae_lines;
1145
1146 END CSTPALPC;
1147