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