[Home] [Help]
PACKAGE BODY: APPS.CSTPALBR
Source
1 PACKAGE BODY CSTPALBR AS
2 /* $Header: CSTALBRB.pls 120.8 2011/12/16 12:33:22 anjha ship $ */
3
4 g_mrp_debug VARCHAR2(1) := NVL(fnd_profile.value('MRP_DEBUG'), 'N'); -- Added For bug 4586534
5
6 PROCEDURE dyn_proc_call (
7 i_proc_name IN VARCHAR2,
8 i_legal_entity IN NUMBER,
9 i_cost_type IN NUMBER,
10 i_cost_group IN NUMBER,
11 i_period_id IN NUMBER,
12 i_transaction_id IN NUMBER,
13 i_event_type_id IN VARCHAR2,
14 i_txn_type_flag IN VARCHAR2, -- 4586534
15 o_err_num OUT NOCOPY NUMBER,
16 o_err_code OUT NOCOPY VARCHAR2,
17 o_err_msg OUT NOCOPY VARCHAR2
18 )
19 IS
20 l_sql_to_run VARCHAR2(500);
21 --l_parameters CST_AE_LIB_PAR_TBL_TYPE := CST_AE_LIB_PAR_TBL_TYPE();
22 CONC_STATUS BOOLEAN;
23 l_err NUMBER := 0;
24 CST_PKG_FAIL EXCEPTION;
25 CST_PKG_FAIL2 EXCEPTION;
26 --l_num_params NUMBER;
27 l_stmt_num NUMBER;
28 BEGIN
29
30 l_stmt_num := 10;
31
32 l_sql_to_run := 'BEGIN ' || i_proc_name || '(';
33
34
35 l_sql_to_run := l_sql_to_run || ':I_LEGAL_ENTITY';
36 l_sql_to_run := l_sql_to_run || ', :I_COST_TYPE_ID';
37 l_sql_to_run := l_sql_to_run || ', :I_COST_GROUP_ID';
38 l_sql_to_run := l_sql_to_run || ', :I_PERIOD_ID';
39 l_sql_to_run := l_sql_to_run || ', :I_TRANSACTION_ID';
40 l_sql_to_run := l_sql_to_run || ', :I_EVENT_TYPE_ID';
41 l_sql_to_run := l_sql_to_run || ', :I_TXN_TYPE_FLAG'; -- 4586527
42 l_sql_to_run := l_sql_to_run || ', :O_ERR_NUM';
43 l_sql_to_run := l_sql_to_run || ', :O_ERR_CODE';
44 l_sql_to_run := l_sql_to_run || ', :O_ERR_MSG';
45 l_sql_to_run := l_sql_to_run || '); END;';
46
47 l_stmt_num := 20;
48 IF g_mrp_debug = 'Y' THEN -- Added For bug 4586534
49 fnd_file.put_line(fnd_file.log,l_sql_to_run);
50 END IF;
51
52 EXECUTE IMMEDIATE l_sql_to_run USING
53 I_LEGAL_ENTITY,
54 I_COST_TYPE,
55 I_COST_GROUP,
56 I_PERIOD_ID,
57 I_TRANSACTION_ID,
58 I_EVENT_TYPE_ID,
59 I_TXN_TYPE_FLAG,
60 OUT O_ERR_NUM,
61 OUT O_ERR_CODE,
62 OUT O_ERR_MSG;
63 IF (o_err_num <> 0 and o_err_num is not null) THEN
64 RAISE CST_PKG_FAIL;
65 END IF;
66
67 IF(l_err <> 0) THEN
68 RAISE CST_PKG_FAIL2;
69 END IF;
70
71 EXCEPTION
72 WHEN CST_PKG_FAIL THEN
73 fnd_file.put_line(fnd_file.log,'CSTPALBR.dyn_proc_call : Error Calling Package');
74 WHEN CST_PKG_FAIL2 THEN
75 o_err_num := l_err;
76 o_err_code := SQLCODE;
77 o_err_msg := 'CSTPALBR.dyn_proc_call ('||l_err||'): Error Calling Package';
78 fnd_file.put_line(fnd_file.log,o_err_msg);
79 WHEN OTHERS THEN
80 o_err_num := 30002;
81 o_err_code := SQLCODE;
82 o_err_msg := 'CSTPALBR.dyn_proc_call : ' || to_char(l_stmt_num) || ':'|| substr(SQLERRM,1,180);
83 fnd_file.put_line(fnd_file.log,o_err_msg);
84 END dyn_proc_call;
85
86
87 PROCEDURE create_acct_entry (
88 i_acct_lib_id IN NUMBER,
89 i_legal_entity IN NUMBER,
90 i_cost_type_id IN NUMBER,
91 i_cost_group_id IN NUMBER,
92 i_period_id IN NUMBER,
93 i_mode IN NUMBER,
94 o_err_num OUT NOCOPY NUMBER,
95 o_err_code OUT NOCOPY VARCHAR2,
96 o_err_msg OUT NOCOPY VARCHAR2
97 ) IS
98
99 /* Added for bug 4586534 */
100
101 TYPE num_tab IS TABLE OF NUMBER;
102 txn_id_tab num_tab;
103 txn_action_id_tab num_tab;
104 txn_src_typ_id_tab num_tab;
105 txn_type_id_tab num_tab;
106 trx_source_line_id_tab num_tab;
107 l_rec_cnt NUMBER := 0;
108
109 TYPE acct_event_char_tab is TABLE OF VARCHAR2(122);
110 event_type_tab acct_event_char_tab;
111 transaction_type_flag_tab acct_event_char_tab;
112
113
114 CURSOR c_txns IS
115 SELECT
116 mmt.transaction_id "TRANSACTION_ID",
117 mmt.transaction_action_id "TRANSACTION_ACTION_ID",
118 mmt.transaction_source_type_id "TRANSACTION_SOURCE_TYPE_ID",
119 mmt.transaction_type_id "TRANSACTION_TYPE_ID",
120 (to_char(mtt.transaction_type_id)||'-'||to_char(mtt.transaction_action_id)||'-'||to_char(mtt.transaction_source_type_id)) "EVENT_TYPE", -- 4586534
121 'INV' "TRANSACTION_TYPE_FLAG", -- Bug 4968702
122 mmt.trx_source_line_id "TRX_SOURCE_LINE_ID"
123 FROM
124 mtl_material_transactions mmt,
125 cst_cost_groups ccg,
126 cst_cost_group_assignments ccga,
127 cst_pac_periods cpp,
128 mtl_transaction_types mtt /* Removed the access to view to directly access the base tables Bug 4968702 */
129
130 WHERE
131 /* Periodic Cost Updates have the item master organization_id as
132 the organization_id in MMT. In this case, the org_cost_group
133 ID is stamped in MMT */
134 /* Bug 2456402 */
135
136 /* For Internal Order and Ordinary Interorg Intransit Shipment and Receipt,pick up
137 intermediate transactions as well */
138 mmt.organization_id = decode(mmt.transaction_type_id,
139 26, decode(nvl(mmt.org_cost_group_id, -1),
140 ccga.cost_group_id, mmt.organization_id,
141 ccga.organization_id),
142 21,decode(mmt.fob_point,
143 1,mmt.organization_id,
144 ccga.organization_id),
145 62,decode(mmt.fob_point,
146 1,mmt.organization_id,
147 ccga.organization_id),
148 12,decode(mmt.fob_point,
149 2,mmt.organization_id,
150 ccga.organization_id),
151 61,decode(mmt.fob_point,
152 2,mmt.organization_id,
153 ccga.organization_id),
154 ccga.organization_id)
155 AND mmt.organization_id = decode(mmt.transaction_type_id,
156 21,decode(mmt.fob_point,
157 1,mmt.organization_id,
158 NVL(mmt.owning_organization_id, mmt.organization_id)),
159 62,decode(mmt.fob_point,
160 1,mmt.organization_id,
161 NVL(mmt.owning_organization_id, mmt.organization_id)),
162 12,decode(mmt.fob_point,
163 2,mmt.organization_id,
164 NVL(mmt.owning_organization_id, mmt.organization_id)),
165 61,decode(mmt.fob_point,
166 2,mmt.organization_id,
167 NVL(mmt.owning_organization_id, mmt.organization_id)),
168 nvl(mmt.owning_organization_id, mmt.organization_id))
169 AND nvl(mmt.owning_tp_type,2) = 2
170 AND ccga.cost_group_id = ccg.cost_group_id
171 AND ccg.cost_group_id = i_cost_group_id
172 AND ccg.legal_entity = i_legal_entity
173 AND mmt.transaction_date BETWEEN trunc(cpp.period_start_date) AND
174 (trunc(cpp.period_end_date) + 0.99999)
175 AND cpp.pac_period_id = i_period_id
176 AND mtt.transaction_type_id = mmt.transaction_type_id -- Join with the base tables.Bug 4968702
177 AND mtt.transaction_action_id = mmt.transaction_action_id
178 AND mtt.transaction_source_type_id = mmt.transaction_source_type_id
179 AND ( ( mmt.parent_transaction_id is null
180 AND EXISTS (
181 SELECT 1
182 FROM mtl_pac_actual_cost_details mpacd
183 WHERE mpacd.transaction_id = mmt.transaction_id
184 AND mpacd.pac_period_id = i_period_id
185 AND mpacd.cost_group_id = ccga.cost_group_id
186 AND mpacd.cost_group_id = i_COST_GROUP_ID
187 )
188 )
189 OR ( mmt.parent_transaction_id is not null AND
190 nvl(mmt.logical_transaction, 2) = 1
191 )
192 )
193 UNION
194 SELECT
195 wt.transaction_id "transaction_id",
196 to_number(null) "transaction_action_id",
197 to_number(null) "transaction_source_type_id",
198 wt.transaction_type "transaction_type_id", -- Bug 4968702 Using the base tables Directly
199 (SELECT to_char(lookup_code) FROM mfg_lookups WHERE lookup_type = 'WIP_TRANSACTION_TYPE' AND lookup_code = wt.transaction_type) "EVENT_TYPE", -- Bug 4968702
200 'WIP' "TRANSACTION_TYPE_FLAG", -- Bug 4968702
201 NULL "TRX_SOURCE_LINE_ID"
202 FROM
203 wip_transactions wt,
204 cst_cost_groups ccg,
205 cst_cost_group_assignments ccga,
206 cst_pac_periods cpp -- Bug 4968702
207 WHERE
208 wt.organization_id = ccga.organization_id AND
209 ccga.cost_group_id = ccg.cost_group_id AND
210 ccg.cost_group_id = i_cost_group_id AND
211 ccg.legal_entity = i_legal_entity AND
212 cpp.pac_period_id = i_period_id AND
213 --wt.transaction_type in (1,2,3,6) AND
214 wt.transaction_date BETWEEN trunc(cpp.period_start_date)
215 AND (trunc(cpp.period_end_date) + 0.99999) AND
216 (wt.transaction_type = 17 -- Added 17 to support Direct Items as part of eAM support in PAC
217 OR (wt.transaction_type in (1,2,3,6) AND
218 EXISTS ( SELECT 1
219 FROM wip_pac_actual_cost_details wpacd
220 WHERE wpacd.transaction_id = wt.transaction_id AND
221 wpacd.pac_period_id = i_period_id AND
222 wpacd.cost_group_id = i_cost_group_id)
223 ))
224 --ORDER BY transaction_id
225 ;
226
227
228 l_ae_par_rec CSTPALTY.CST_AE_PAR_REC_TYPE;
229 l_err_rec CSTPALTY.CST_AE_ERR_REC_TYPE;
230 --l_ae_par_rec CST_AE_PAR_REC_TYPE;
231 --l_err_rec CST_AE_ERR_REC_TYPE;
232 l_sql_to_run VARCHAR2(500);
233 l_package_name VARCHAR2(100);
234 l_cursor NUMBER;
235 l_event_type_id VARCHAR2(15);
236 l_accounting_package_id NUMBER;
237 l_event_pkg_exists NUMBER;
238 l_stmt_num NUMBER;
239 l_so_issue_exists NUMBER;
240 CONC_STATUS BOOLEAN;
241 CST_PKG_CALL_FAIL EXCEPTION;
242 BEGIN
243 IF (i_mode <> 0) THEN
244 return;
245 END IF;
246
247
248 OPEN c_txns;
249
250
251
252 /* Used Bulk Collect to improve performance, Bug No. 4586534 */
253 LOOP
254 FETCH c_txns BULK COLLECT INTO txn_id_tab,
255 txn_action_id_tab,
256 txn_src_typ_id_tab,
257 txn_type_id_tab,
258 event_type_tab,
259 transaction_type_flag_tab,
260 trx_source_line_id_tab LIMIT 5000;
261
262
263 l_rec_cnt := txn_id_tab.COUNT;
264
265 FOR i IN 1..l_rec_cnt LOOP
266
267 IF g_mrp_debug = 'Y' THEN -- Bug No 4586534
268 fnd_file.put_line(fnd_file.log,' ');
269 fnd_file.put_line(fnd_file.log,'Processing Transaction : '||to_char(txn_id_tab(i)));
270 END IF;
271
272
273 l_stmt_num := 20;
274 /* change for bug No. 4586534 to remove COUNT(*) to COUNT(1) by adding rownum check
275 as it is used for existence check only */
276 SELECT COUNT(1)
277 INTO l_event_pkg_exists
278 FROM cst_acct_lib_packages calp2
279 WHERE calp2.accounting_lib_id = i_acct_lib_id
280 AND calp2.event_type_id = event_type_tab(i)
281 AND ROWNUM < 2;
282
283 IF (l_event_pkg_exists > 0) THEN
284
285 l_stmt_num := 30;
286
287 SELECT
288 cap.accounting_package_id,
289 cap.package_name
290 INTO
291 l_accounting_package_id,
292 l_package_name
293 FROM
294 cst_acct_lib_packages calp,
295 cst_accounting_packages cap
296 WHERE
297 calp.accounting_lib_id = i_acct_lib_id AND
298 calp.event_type_id = l_event_type_id AND
299 cap.accounting_package_id = calp.accounting_package_id;
300
301 ELSE
302
303 l_stmt_num := 40;
304
305 SELECT
306 cap.accounting_package_id,
307 cap.package_name
308 INTO
309 l_accounting_package_id,
310 l_package_name
311 FROM
312 cst_acct_lib_packages calp,
313 cst_accounting_packages cap
314 WHERE
315 calp.accounting_lib_id = i_acct_lib_id AND
316 calp.event_type_id IS NULL AND
317 cap.accounting_package_id = calp.accounting_package_id;
318
319 END IF;
320 IF g_mrp_debug = 'Y' THEN -- Bug No 4586534
321 fnd_file.put_line(fnd_file.log,'Calling Package '||(l_package_name) ||' ...');
322 END IF;
323
324 l_so_issue_exists := 1;
325
326 IF (transaction_type_flag_tab(i) = 'INV' AND
327 txn_action_id_tab(i) = 36 AND
328 txn_src_typ_id_tab(i) = 2) THEN
329
330 BEGIN
331 l_stmt_num := 45;
332 SELECT COUNT(1)
333 INTO l_so_issue_exists
334 FROM cst_revenue_cogs_match_lines crcml
335 WHERE cogs_om_line_id = trx_source_line_id_tab(i)
336 AND pac_cost_type_id = i_cost_type_id;
337
338 EXCEPTION
339 WHEN NO_DATA_FOUND THEN
340 l_so_issue_exists := 0;
341 END;
342 END IF;
343
344 IF (l_so_issue_exists <> 0) THEN
345
346 dyn_proc_call(
347 l_package_name,
348 i_legal_entity,
349 i_cost_type_id ,
350 i_cost_group_id ,
351 i_period_id ,
352 txn_id_tab(i),
353 event_type_tab(i),
354 transaction_type_flag_tab(i),
355 l_err_rec.l_err_num ,
356 l_err_rec.l_err_code ,
357 l_err_rec.l_err_msg
358 );
359
360 IF (l_err_rec.l_err_num <> 0 and l_err_rec.l_err_num is not null) THEN
361 RAISE CST_PKG_CALL_FAIL;
362 END IF;
363 END IF;
364
365 END LOOP;
366 EXIT WHEN c_txns%NOTFOUND; -- change for bug No. 4586534
367 END LOOP; -- change for bug No. 4586534
368 CLOSE c_txns;
369 IF g_mrp_debug = 'Y' THEN -- Bug No 4586534
370 fnd_file.put_line(fnd_file.log,'Total Transactions processed : '||to_char(l_rec_cnt));
371 END IF;
372
373 COMMIT;
374
375 EXCEPTION
376 WHEN CST_PKG_CALL_FAIL THEN
377 o_err_num := l_err_rec.l_err_num;
378 o_err_code := l_err_rec.l_err_code;
379 o_err_msg := l_err_rec.l_err_msg;
380 WHEN OTHERS THEN
381 o_err_num := 30002;
382 o_err_code := SQLCODE;
383 o_err_msg := 'CSTPALBR.create_acct_entry : ' || to_char(l_stmt_num) || ':'|| substr(SQLERRM,1,180);
384 fnd_file.put_line(fnd_file.log,l_err_rec.l_err_msg);
385
386 END create_acct_entry;
387
388 PROCEDURE insert_ae_lines (
389 i_ae_txn_rec IN CSTPALTY.cst_ae_txn_rec_type,
390 i_ae_line_rec_tbl IN CSTPALTY.cst_ae_line_tbl_type,
391 o_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type
392 )
393 IS
394 l_ae_header_id NUMBER;
395 l_err_rec CSTPALTY.CST_AE_ERR_REC_TYPE;
396 --l_err_rec CST_AE_ERR_REC_TYPE;
397 l_stmt_num NUMBER;
398 l_request_id NUMBER;
399 l_user_id NUMBER;
400 l_login_id NUMBER;
401 l_prog_appl_id NUMBER;
402 l_prog_id NUMBER;
403 CONC_STATUS BOOLEAN;
404
405
406 BEGIN
407
408 l_request_id := FND_GLOBAL.conc_request_id;
409 l_user_id := FND_GLOBAL.user_id;
410 l_login_id := FND_GLOBAL.login_id;
411 l_prog_appl_id := FND_GLOBAL.prog_appl_id;
412 l_prog_id := FND_GLOBAL.conc_program_id;
413
414 l_stmt_num := 10;
415 -- For Bug No. 4586534
416 /*
417 SELECT
418 cst_ae_headers_s.NEXTVAL
419 INTO
420 l_ae_header_id
421 FROM
422 dual;
423 */
424
425 IF i_ae_line_rec_tbl.EXISTS(1) THEN
426 IF g_mrp_debug = 'Y' THEN -- Bug No 4586534
427 fnd_file.put_line(fnd_file.log,'Inserting in Headers table ...');
428 END IF;
429
430 l_stmt_num := 20;
431
432 INSERT INTO
433 cst_ae_headers (
434 ae_header_id,
435 accounting_event_id,
436 set_of_books_id,
437 legal_entity_id,
438 cost_group_id,
439 cost_type_id,
440 ae_category,
441 period_id,
442 period_name,
443 accounting_date,
444 gl_transfer_flag,
445 gl_transfer_run_id,
446 description,
447 gl_transfer_error_code,
448 acct_event_source_table,
449 organization_id,
450 accounting_error_code,
451 creation_date,
452 created_by,
453 last_update_date,
454 last_updated_by,
455 last_update_login,
456 program_update_date,
457 program_application_id,
458 program_id,
459 request_id,
460 cross_currency_flag
461 )
462 VALUES
463 (
464 cst_ae_headers_s.NEXTVAL,
465 i_ae_txn_rec.transaction_id,
466 i_ae_txn_rec.set_of_books_id,
467 i_ae_txn_rec.legal_entity_id,
468 i_ae_txn_rec.cost_group_id,
469 i_ae_txn_rec.cost_type_id,
470 i_ae_txn_rec.ae_category,
471 i_ae_txn_rec.accounting_period_id,
472 i_ae_txn_rec.accounting_period_name,
473 i_ae_txn_rec.accounting_date,
474 'N',
475 -1,
476 i_ae_txn_rec.description, --description??
477 NULL, -- gl xfer error code
478 i_ae_txn_rec.source_table,
479 i_ae_txn_rec.organization_id,
480 NULL,
481 sysdate,
482 l_user_id,
483 sysdate,
484 l_user_id,
485 l_login_id,
486 sysdate,
487 l_prog_appl_id,
488 l_prog_id,
489 l_request_id,
490 NULL
491 )RETURNING ae_header_id INTO l_ae_header_id; -- Bug No.4586534
492
493 FOR i IN i_ae_line_rec_tbl.FIRST..i_ae_line_rec_tbl.LAST LOOP
494 IF g_mrp_debug = 'Y' THEN -- Bug No 4586534
495 fnd_file.put_line(fnd_file.log,'Inserting in Lines table ...');
496 END IF;
497
498 l_stmt_num := 30;
499
500 IF (i_ae_line_rec_tbl(i).actual_flag = 'E') THEN
501
502
503 INSERT INTO
504 cst_encumbrance_lines (
505 encumbrance_line_id,
506 ae_header_id,
507 ae_line_number,
508 ae_line_type_code,
509 code_combination_id,
510 currency_code,
511 currency_conversion_type,
512 currency_conversion_date,
513 currency_conversion_rate,
514 entered_dr,
515 entered_cr,
516 accounted_dr,
517 accounted_cr,
518 source_table,
519 source_id,
520 rate_or_amount,
521 basis_type,
522 resource_id,
523 cost_element_id,
524 activity_id,
525 repetitive_schedule_id,
526 overhead_basis_factor,
527 basis_resource_id,
528 gl_sl_link_id,
529 description,
530 accounting_error_code,
531 stat_amount,
532 ussgl_transaction_code,
533 subledger_doc_sequence_id,
534 subledger_doc_sequence_value,
535 gl_transfer_error_code,
536 encumbrance_type_id,
537 reference1,
538 reference2,
539 reference3,
540 reference4,
541 reference5,
542 reference6,
543 reference7,
544 reference8,
545 reference9,
546 reference10,
547 creation_date,
548 created_by,
549 last_update_date,
550 last_updated_by,
551 last_update_login,
552 program_update_date,
553 program_application_id,
554 program_id,
555 request_id
556 )
557 VALUES
558 (
559 cst_encumbrance_lines_s.nextval,
560 l_ae_header_id,
561 i,
562 i_ae_line_rec_tbl(i).ae_line_type,
563 i_ae_line_rec_tbl(i).account,
564 i_ae_line_rec_tbl(i).currency_code,
565 i_ae_line_rec_tbl(i).currency_conv_type,
566 i_ae_line_rec_tbl(i).currency_conv_date,
567 decode(i_ae_line_rec_tbl(i).currency_conv_rate,
568 -1,decode(i_ae_line_rec_tbl(i).currency_code,
569 NULL,NULL,
570 i_ae_line_rec_tbl(i).currency_conv_rate),
571 i_ae_line_rec_tbl(i).currency_conv_rate),
572 i_ae_line_rec_tbl(i).entered_dr,
573 i_ae_line_rec_tbl(i).entered_cr,
574 i_ae_line_rec_tbl(i).accounted_dr,
575 i_ae_line_rec_tbl(i).accounted_cr,
576 i_ae_line_rec_tbl(i).source_table, -- source table
577 i_ae_line_rec_tbl(i).source_id, -- source id
578 i_ae_line_rec_tbl(i).rate_or_amount,
579 i_ae_line_rec_tbl(i).basis_type,
580 i_ae_line_rec_tbl(i).resource_id,
581 i_ae_line_rec_tbl(i).cost_element_id,
582 i_ae_line_rec_tbl(i).activity_id,
583 i_ae_line_rec_tbl(i).repetitive_schedule_id,
584 i_ae_line_rec_tbl(i).overhead_basis_factor,
585 i_ae_line_rec_tbl(i).basis_resource_id,
586 NULL, -- gl_sl_link?? null
587 i_ae_line_rec_tbl(i).description, -- desc accting line desc
588 NULL, -- error code null
589 NULL, -- stat amount null
590 NULL, -- ussgl null
591 NULL, -- sub ledger doc seq id
592 NULL, -- sub ledger doc seq value
593 NULL, -- gl xfer error code
594 i_ae_line_rec_tbl(i).encum_type_id,
595 i_ae_line_rec_tbl(i).reference1,
596 i_ae_line_rec_tbl(i).reference2,
597 i_ae_line_rec_tbl(i).reference3,
598 i_ae_line_rec_tbl(i).reference4,
599 i_ae_line_rec_tbl(i).reference5,
600 i_ae_line_rec_tbl(i).reference6,
601 i_ae_line_rec_tbl(i).reference7,
602 i_ae_line_rec_tbl(i).reference8,
603 i_ae_line_rec_tbl(i).reference9,
604 i_ae_line_rec_tbl(i).reference10,
605 sysdate,
606 l_user_id,
607 sysdate,
608 l_user_id,
609 l_login_id,
610 sysdate,
611 l_prog_appl_id,
612 l_prog_id,
613 l_request_id
614 );
615 ELSE
616
617 INSERT INTO
618 cst_ae_lines (
619 ae_line_id,
620 ae_header_id,
621 ae_line_number,
622 ae_line_type_code,
623 code_combination_id,
624 currency_code,
625 currency_conversion_type,
626 currency_conversion_date,
627 currency_conversion_rate,
628 entered_dr,
629 entered_cr,
630 accounted_dr,
631 accounted_cr,
632 source_table,
633 source_id,
634 rate_or_amount,
635 basis_type,
636 resource_id,
637 cost_element_id,
638 activity_id,
639 repetitive_schedule_id,
640 overhead_basis_factor,
641 basis_resource_id,
642 gl_sl_link_id,
643 description,
644 accounting_error_code,
645 stat_amount,
646 ussgl_transaction_code,
647 subledger_doc_sequence_id,
648 subledger_doc_sequence_value,
649 gl_transfer_error_code,
650 wip_entity_id,
654 reference4,
651 reference1,
652 reference2,
653 reference3,
655 reference5,
656 reference6,
657 reference7,
658 reference8,
659 reference9,
660 reference10,
661 creation_date,
662 created_by,
663 last_update_date,
664 last_updated_by,
665 last_update_login,
666 program_update_date,
667 program_application_id,
668 program_id,
669 request_id
670 )
671 VALUES
672 (
673 cst_ae_lines_s.nextval,
674 l_ae_header_id,
675 i,
676 i_ae_line_rec_tbl(i).ae_line_type,
677 i_ae_line_rec_tbl(i).account,
678 i_ae_line_rec_tbl(i).currency_code,
679 i_ae_line_rec_tbl(i).currency_conv_type,
680 i_ae_line_rec_tbl(i).currency_conv_date,
681 decode(i_ae_line_rec_tbl(i).currency_conv_rate,
682 -1,decode(i_ae_line_rec_tbl(i).currency_code,
683 NULL,NULL,
684 i_ae_line_rec_tbl(i).currency_conv_rate),
685 i_ae_line_rec_tbl(i).currency_conv_rate),
686 i_ae_line_rec_tbl(i).entered_dr,
687 i_ae_line_rec_tbl(i).entered_cr,
688 i_ae_line_rec_tbl(i).accounted_dr,
689 i_ae_line_rec_tbl(i).accounted_cr,
690 i_ae_line_rec_tbl(i).source_table, -- source table
691 i_ae_line_rec_tbl(i).source_id, -- source id
692 i_ae_line_rec_tbl(i).rate_or_amount,
693 i_ae_line_rec_tbl(i).basis_type,
694 i_ae_line_rec_tbl(i).resource_id,
695 i_ae_line_rec_tbl(i).cost_element_id,
696 i_ae_line_rec_tbl(i).activity_id,
697 i_ae_line_rec_tbl(i).repetitive_schedule_id,
698 i_ae_line_rec_tbl(i).overhead_basis_factor,
699 i_ae_line_rec_tbl(i).basis_resource_id,
700 NULL, -- gl_sl_link?? null
701 i_ae_line_rec_tbl(i).description, -- desc accting line desc
702 NULL, -- error code null
703 NULL, -- stat amount null
704 NULL, -- ussgl null
705 NULL, -- sub ledger doc seq id
706 NULL, -- sub ledger doc seq value
707 NULL, -- gl xfer error code
708 i_ae_line_rec_tbl(i).wip_entity_id,
709 i_ae_line_rec_tbl(i).reference1,
710 i_ae_line_rec_tbl(i).reference2,
711 i_ae_line_rec_tbl(i).reference3,
712 i_ae_line_rec_tbl(i).reference4,
713 i_ae_line_rec_tbl(i).reference5,
714 i_ae_line_rec_tbl(i).reference6,
715 i_ae_line_rec_tbl(i).reference7,
716 i_ae_line_rec_tbl(i).reference8,
717 i_ae_line_rec_tbl(i).reference9,
718 i_ae_line_rec_tbl(i).reference10,
719 sysdate,
720 l_user_id,
721 sysdate,
722 l_user_id,
723 l_login_id,
724 sysdate,
725 l_prog_appl_id,
726 l_prog_id,
727 l_request_id
728 );
729 END IF;
730
731
732 END LOOP;
733
734 END IF;
735
736 EXCEPTION
737 WHEN OTHERS THEN
738 o_err_rec.l_err_num := 30001;
739 o_err_rec.l_err_code := SQLCODE;
740 o_err_rec.l_err_msg := 'CSTPALBR.insert_ae_lines : ' || to_char(l_stmt_num) || ':'|| substr(SQLERRM,1,180);
741 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',o_err_rec.l_err_msg);
742 fnd_file.put_line(fnd_file.log,o_err_rec.l_err_msg);
743
744
745
746 END insert_ae_lines;
747
748 END CSTPALBR;
749