[Home] [Help]
PACKAGE BODY: APPS.FTP_TP_PKG
Source
1 PACKAGE BODY ftp_tp_pkg AS
2 /* $Header: FTPEFTPB.pls 120.16 2007/01/17 06:06:45 rknanda noship $ */
3 -- record type for objects
4 type OBJ_INFO_TYPE is record (
5 obj_id number,
6 value_set_id number,
7 eff_start_date date,
8 eff_end_date date
9 );
10 -- type used for get_min_range to find minimum effective_date
11 -- range for a set of object definitions
12 type obj_info_array is varray (10) of obj_info_type;
13 -- PACKAGE CONSTANTS
14 c_api_version CONSTANT NUMBER := 1;
15 c_stmt_type CONSTANT VARCHAR2(6) := 'INSERT'; --INSERT
16 c_prg_running CONSTANT VARCHAR2(20) := 'RUNNING';
17 c_prg_success CONSTANT VARCHAR2(20) := 'SUCCESS';
18 c_prg_err_rerun CONSTANT VARCHAR2(20) := 'ERROR_RERUN';
19 c_prg_err_undo CONSTANT VARCHAR2(20) := 'ERROR_UNDO';
20 c_prg_cnl_rerun CONSTANT VARCHAR2(20) := 'CANCELLED_RERUN';
21 c_prg_cnl_undo CONSTANT VARCHAR2(20) := 'CANCELLED_UNDO';
22 c_app CONSTANT VARCHAR2(3) := 'FTP';
23 c_ecoloss_method CONSTANT NUMBER := 4;
24 -- table used to lookup dimension_id
25 MAIN_DIM_COL_NAME varchar2(30) := 'LINE_ITEM_ID';
26 MAIN_DIM_ID number;
27 ACCT_TYPE_ATTR_ID number := 1234;
28 -- dim types for account type
29 EXT_ACCOUNT_TYPE_NAME varchar2(30) := 'EXTENDED_ACCOUNT_TYPE';
30 EXT_ACCOUNT_TYPE_ID number;
31 ASSET_FLAG_NAME varchar2(30) := 'ASSET_FLAG';
32 ASSET_FLAG_ID number;
33 OFF_BAL_NAME varchar2(30) := 'OFF_BALANCE_SHEET_FLAG';
34 OFF_BAL_FLAG_ID number;
35 OBJECT_ID number;
36 REQUEST_ID number;
37 -- Praveen Attaluri (Bug Fix)
38 LEDGER_NAME varchar2(30) := 'FEM_BALANCES';
39
40 -- Tina Dasgupta (R12 FTP Enhancements)
41 -- Record type for FTP_RATE_OUTPUT_MAPPING_RULE
42 type t_alt_rate_obj is record (
43 obj_def_id number,
44 Account_Table_Name varchar2(2000),
45 TRANSFER_RATE_COL_NAME varchar2(2000),
46 MATCHED_SPREAD_COL_NAME varchar2(2000),
47 REMAINING_TERM_COL_NAME varchar2(2000),
48 HISTORIC_OPTION_COL_NAME varchar2(2000),
49 ADJ_RATE_COL_NAME varchar2(2000),
50 HISTORIC_STAT_SPREAD_COL_NAME varchar2(2000),
51 CURRENT_STAT_SPREAD_COL_NAME varchar2(2000),
52 ADJ_AMOUNT_COL_NAME varchar2(2000),
53 CURRENT_OPTION_COL_NAME varchar2(2000),
54 ADJ_MKT_VALUE varchar2(2000)
55 );
56 -- Table for records of t_alt_rate_obj
57 TYPE t_alt_rate_obj_list IS TABLE OF t_alt_rate_obj;
58 g_alt_rate_obj t_alt_rate_obj_list := t_alt_rate_obj_list();
59
60 -- Globals that indicate whether the the global varb exists or not
61 g_alt_rate_obj_exists BOOLEAN := FALSE;
62
63 -- Global List of table names that have valid mappings defined in FTP_RATE_OUTPUT_MAPPING_RULE
64 valid_table_list varchar2(3000);
65 g_block VARCHAR2(1000) := 'ftp.plsql.FTP_PKG.';
66
67
68 -- Praveen Attaluri (Bug Fix)
69 -- return min start/end date range in obj_info_list
70 -- assumes list is non-empty
71 procedure get_min_range(obj_info_list in obj_info_array,
72 s_date out nocopy date,
73 e_date out nocopy date)
74 is
75 begin
76 s_date := obj_info_list(obj_info_list.first).eff_start_date;
77 e_date := obj_info_list(obj_info_list.first).eff_end_date;
78 for i in obj_info_list.first+1..obj_info_list.last loop
79 if obj_info_list(i).eff_start_date is not null then
80 if s_date is null or s_date < obj_info_list(i).eff_start_date then
81 s_date := obj_info_list(i).eff_start_date;
82 end if;
83 end if;
84 if obj_info_list(i).eff_end_date is not null then
85 if e_date is null or e_date > obj_info_list(i).eff_end_date then
86 e_date := obj_info_list(i).eff_end_date;
87 end if;
88 end if;
89 end loop;
90 end get_min_range;
91 -- Get the object definition id for a given obj_id, effective date,
92 procedure GET_OBJ_DEF(
93 OBJ_ID in number,
94 EFFECTIVE_DATE in date,
95 OBJ_INFO out nocopy OBJ_INFO_TYPE) is
96 begin
97 select a.object_definition_id,
98 c.value_set_id,
99 a.effective_start_date,
100 a.effective_end_date
101 into OBJ_INFO
102 from fem_object_definition_b a,
103 fem_object_catalog_b b,
104 fem_global_vs_combo_defs c
105 where a.object_id = OBJ_ID
106 and a.old_approved_copy_flag = 'N'
107 and trunc(EFFECTIVE_DATE) between a.effective_start_date
108 and nvl(a.effective_end_date,
109 to_date('99991231', 'YYYYMMDD'))
110 and a.object_id = b.object_id
111 and b.local_vs_combo_id = c.global_vs_combo_id(+)
112 and c.dimension_id(+) = main_dim_id;
113 end GET_OBJ_DEF;
114
115
116 /*********************************************
117 CRDT: 18-July-2003
118 PGMR: Praveen Attaluri
119 DESC: Method return the value set id of line item
120 and offset org unit based on the tranfer
121 price rule local valuset combo and object id of
122 the process rule
123 *********************************************/
124 PROCEDURE GET_VALUESETS_INFO(
125 OBJ_ID in number,
126 EFFECTIVE_DATE in date,
127 LN_ITEM_VAL_SET out NOCOPY number,
128 ORG_VAL_SET out NOCOPY number,
129 SOURCE_SYS_CD out NOCOPY number
130 ) IS
131 process_info obj_info_type;
132 tp_obj_id number;
133 pp_obj_id number;
134 combo_id number;
135 begin
136 -- Get the object definition id of the process
137 get_obj_def(OBJ_ID, EFFECTIVE_DATE, process_info);
138
139 -- Now get the transfer price and prepayment object -d
140 select transfer_price_object_id, prepay_object_id
141 into tp_obj_id, pp_obj_id
142 from ftp_tp_process_rule
143 where object_definition_id = process_info.obj_id;
144
145 -- Get the local value combo of the tp_object_id;
146 select local_vs_combo_id into combo_id
147 from fem_object_catalog_b where object_id = tp_obj_id;
148
149 -- Now the valuset id of the line item and org unitl.
150 select value_set_id into ORG_VAL_SET
151 from fem_global_vs_combo_defs where dimension_id = 8 and
152 global_vs_combo_id = combo_id;
153
154 -- Now the valuset id of the line item and org unitl.
155 select value_set_id into LN_ITEM_VAL_SET
156 from fem_global_vs_combo_defs where dimension_id = 14 and
157 global_vs_combo_id = combo_id;
158
159 -- Get Source System Code
160 EXECUTE IMMEDIATE 'select source_system_code from fem_source_systems_vl
161 where source_system_display_code = :1' into SOURCE_SYS_CD USING c_app;
162
163 END GET_VALUESETS_INFO;
164
165
166 procedure GET_HIER_INFO(
167 obj_id in number,
168 effective_date in date,
169 obj_info out nocopy obj_info_type) is
170 hier_id number := NULL;
171 /*
172 ******* Commenting for Bug 4185961 Waiting for the base bug 3484006 to be closed *******
173 cursor c(id number) is
174 select hierarchy_id
175 from fem_rule_hierarchies
176 where object_id = id
177 and dimension_id = main_dim_id;
178 */
179 cursor c(id number,eff_date date) is
180 select
181 cat.object_id hierarchy_id
182 from
183 fem_object_catalog_b cat,
184 fem_object_dependencies dep,
185 fem_object_definition_b def
186 where
187 def.object_id = id and
188 eff_date between def.effective_start_date and def.effective_end_date and
189 def.object_definition_id = dep.object_definition_id and
190 dep.required_object_id = cat.object_id and
191 cat.object_type_code = 'HIERARCHY';
192 begin
193 hier_id := NULL;
194
195 for cur in c(obj_id,effective_date) loop
196 hier_id := cur.hierarchy_id;
197 end loop;
198 -- if heirarchy id is found, look up appropriate definition
199 if hier_id is not null then
200 get_obj_def(hier_id, effective_date, obj_info);
201 end if;
202
203 end GET_HIER_INFO;
204 -- procedure to populate tp nodes on ftp_tp_pp_node_map for the situation
205 -- where there is no hierarchy
206 procedure populate_non_hier_tp(map_id in number,
207 tp_obj_def in number,
208 l_value_set_id in number) is
209 user_id number;
210 login_id number;
211 begin
212 user_id := fnd_global.user_id;
213 login_id := fnd_global.login_id;
214 delete ftp_tp_pp_node_map where map_id = node_map_id;
215 insert into ftp_tp_pp_node_map
216 (node_map_id, line_item_id, value_set_id,
217 currency, tp_node,
218 pp_node,
219 creation_date, created_by, last_updated_by,
220 last_update_date, last_update_login,
221 created_by_object_id,created_by_request_id,
222 last_updated_by_object_id,last_updated_by_request_id)
223 select distinct map_id, line_item_id, l_value_set_id,
224 currency, line_item_id,
225 null, sysdate,
226 user_id, user_id, sysdate, login_id,
227 OBJECT_ID,REQUEST_ID,OBJECT_ID,REQUEST_ID from ftp_transfer_price_rule
228 where object_definition_id = tp_obj_def;
229 end populate_non_hier_tp;
230 -- procedure to populate pp nodes on ftp_tp_node_map when there is
231 -- no hierarchy used for prepayment
232 procedure populate_non_hier_pp(map_id in number,
233 pp_obj_def in number,
234 l_value_set_id in number) is
235 user_id number;
236 login_id number;
237 begin
238 user_id := fnd_global.user_id;
239 login_id := fnd_global.login_id;
240 update ftp_tp_pp_node_map a set pp_node =
241 (select distinct line_item_id from ftp_prepayment_rule p
242 where p.object_definition_id = pp_obj_def
243 and p.line_item_id = a.line_item_id
244 and p.currency = a.currency),
245 -- The object id will always be the same.
246 created_by_request_id = REQUEST_ID,
247 last_updated_by_request_id = REQUEST_ID
248 where a.node_map_id = map_id;
249 end populate_non_hier_pp;
250
251 /***************************************************************************
252 Desc : Procedure to populate adj nodes on ftp_tp_node_map when there is
253 no hierarchy used for adjustment.
254 Pgmr : Raghuram K Nanda
255 Date : 16-Oct-2006
256 ***************************************************************************/
257 procedure populate_non_hier_adj(map_id in number,
258 adj_obj_def in number,
259 l_value_set_id in number) is
260 user_id number;
261 login_id number;
262 begin
263 user_id := fnd_global.user_id;
264 login_id := fnd_global.login_id;
265
266 --Merge into Node map table
267 --When found means TP assumptions exists and we need to update adjustment
268 --assumptions
269 --When not found means TP assumptions doesn't exists for the line_item,
270 --currency combo and we need to insert adjustment assumptions
271 MERGE INTO ftp_tp_pp_node_map nm USING
272 (select distinct line_item_id, currency from ftp_adjustment_rule
273 where object_definition_id = adj_obj_def) adj
274 ON (nm.line_item_id =adj.line_item_id and nm.currency=adj.currency
275 and nm.node_map_id = map_id)
276 WHEN MATCHED THEN
277 update set adj_node=adj.line_item_id
278 WHEN NOT MATCHED THEN
279 insert (node_map_id, line_item_id, value_set_id,
280 currency, adj_node, tp_node, pp_node,
281 creation_date, created_by, last_updated_by,
282 last_update_date, last_update_login,
283 created_by_object_id,created_by_request_id,
284 last_updated_by_object_id,last_updated_by_request_id) values
285 (map_id, adj.line_item_id, l_value_set_id, adj.currency, adj.line_item_id,
286 NULL, NULL, sysdate, user_id, user_id, sysdate, login_id,
287 OBJECT_ID,REQUEST_ID,OBJECT_ID,REQUEST_ID);
288
289 end populate_non_hier_adj;
290 -- procedure to populate fep_tp_pp_node_map when a hierarchy is used for
291 -- transfer pricing rule
292 procedure populate_hierarchical_tp(map_id in number,
293 tp_obj_def in number,
294 l_value_set_id in number,
295 hier_id in number) is
296 user_id number;
297 login_id number;
298 cursor c is select distinct currency from ftp_transfer_price_rule
299 where object_definition_id = tp_obj_def;
300 begin
301 user_id := fnd_global.user_id;
302 login_id := fnd_global.login_id;
303 delete ftp_tp_pp_node_map where map_id = node_map_id;
304 -- move things currency by currency
305 for cur in c loop
306 insert into ftp_tp_pp_node_map
307 (node_map_id, line_item_id,
308 value_set_id,
309 currency,
310 tp_node,
311 pp_node,
312 creation_date, created_by, last_updated_by,
313 last_update_date, last_update_login,
314 created_by_object_id,created_by_request_id,
315 last_updated_by_object_id,last_updated_by_request_id)
316 select map_id, h.child_id, l_value_set_id,
317 cur.currency, h.parent_id,
318 null, sysdate,
319 user_id, user_id, sysdate, login_id,
320 OBJECT_ID,REQUEST_ID,OBJECT_ID,REQUEST_ID
321 from fem_ln_items_hier h
322 where h.hierarchy_obj_def_id = hier_id
323 /* restrict to this value set */
324 and l_value_set_id = h.parent_value_set_id
325 and l_value_set_id = h.child_value_set_id
326 /* restrict to parents in tp id */
327 and exists (select null from ftp_transfer_price_rule tp
328 where tp.object_definition_id = tp_obj_def
329 and tp.line_item_id = h.parent_id
330 and tp.currency = cur.currency)
331 /* restrict to leaves in hierarchy */
332 and not exists (select null from fem_ln_items_hier h1
333 where h1.hierarchy_obj_def_id
334 = hier_id
335 and h1.parent_id = h.child_id
336 and h1.parent_value_set_id = l_value_set_id
337 and h1.child_value_set_id = l_value_set_id
338 and h1.child_id <> h.child_id)
339 /* restrict to lowest level when overrides exist */
340 /* select max level num parent existing for given child within ID */
341 and h.parent_depth_num = (select max(h1.parent_depth_num)
342 from fem_ln_items_hier h1,
343 ftp_transfer_price_rule q
344 where h1.hierarchy_obj_def_id
345 = hier_id
346 and q.object_definition_id = tp_obj_def
347 and q.line_item_id = h1.parent_id
348 and h1.parent_value_set_id
349 = l_value_set_id
350 and h1.child_value_set_id
351 = l_value_set_id
352 and q.currency = cur.currency
353 and h1.child_id = h.child_id);
354 end loop;
355 end populate_hierarchical_tp;
356 -- procedure to populate fep_tp_pp_node_map when a hierarchy is used for
357 -- transfer pricing rule
358 procedure populate_hierarchical_pp(map_id in number,
359 pp_obj_def in number,
360 l_value_set_id in number,
361 hier_id in number) is
362 user_id number;
363 login_id number;
364 cursor c is select distinct currency from ftp_prepayment_rule
365 where object_definition_id = pp_obj_def;
366 begin
367 user_id := fnd_global.user_id;
368 login_id := fnd_global.login_id;
369 -- move things currency by currency
370 for cur in c loop
371 update ftp_tp_pp_node_map a set pp_node =
372 (select h.parent_id
373 from fem_ln_items_hier h
374 where h.hierarchy_obj_def_id = hier_id
375 and h.parent_value_set_id = l_value_set_id
376 /* restrict to leaves in hierarchy */
377 and a.line_item_id = h.child_id
378 and h.child_value_set_id = l_value_set_id
379 /* restrict to parents in pp id */
380 and exists (select null from ftp_prepayment_rule pp
381 where pp.object_definition_id = pp_obj_def
382 and pp.line_item_id = h.parent_id
383 and pp.currency = cur.currency)
384 /* restrict to lowest level when overrides exist */
385 /* select max level num parent existing for given */
386 /* child within ID */
387 and h.parent_depth_num = (select max(h1.parent_depth_num)
388 from fem_ln_items_hier h1,
389 ftp_prepayment_rule q
390 where h1.hierarchy_obj_def_id
391 = hier_id
392 and q.object_definition_id
393 = pp_obj_def
394 and q.line_item_id = h1.parent_id
395 and l_value_set_id
396 = h1.parent_value_set_id
397 and q.currency = cur.currency
398 and h1.child_id = h.child_id
399 and h1.child_value_set_id
400 = l_value_set_id)),
401 -- The object id will always be the same.
402 created_by_request_id = REQUEST_ID,
403 last_updated_by_request_id = REQUEST_ID
404 where a.node_map_id = map_id and a.currency = cur.currency;
405 end loop;
406 end populate_hierarchical_pp;
407
408 /***************************************************************************
409 Desc : Procedure to populate ftp_tp_pp_node_map when a hierarchy is used for
410 adjustment rule.
411 Pgmr : Raghuram K Nanda
412 Date : 16-Oct-2006
413 ***************************************************************************/
414 procedure populate_hierarchical_adj(map_id in number,
415 adj_obj_def in number,
416 l_value_set_id in number,
417 hier_id in number) is
418 user_id number;
419 login_id number;
420 cursor c is select distinct currency from ftp_adjustment_rule
421 where object_definition_id = adj_obj_def;
422 --l_count number;
423 begin
424 user_id := fnd_global.user_id;
425 login_id := fnd_global.login_id;
426
427 --Merge into Node map table
428 --When found means TP assumptions exists and we need to update adjustment
429 --assumptions
430 --When not found means TP assumptions doesn't exists for the line_item,
431 --currency combo and we need to insert adjustment assumptions
432
433 -- move things currency by currency
434 for cur in c loop
435 MERGE INTO ftp_tp_pp_node_map nm USING
436 (select h.child_id, cur.currency, h.parent_id
437 from fem_ln_items_hier h
438 where h.hierarchy_obj_def_id = hier_id
439 /* restrict to this value set */
440 and l_value_set_id = h.parent_value_set_id
441 and l_value_set_id = h.child_value_set_id
442 /* restrict to parents in adjustment id */
443 and exists (select null from ftp_adjustment_rule adj
444 where adj.object_definition_id = adj_obj_def
445 and adj.line_item_id = h.parent_id
446 and adj.currency = cur.currency)
447 /* restrict to leaves in hierarchy */
448 and not exists (select null from fem_ln_items_hier h1
449 where h1.hierarchy_obj_def_id
450 = hier_id
451 and h1.parent_id = h.child_id
452 and h1.parent_value_set_id = l_value_set_id
453 and h1.child_value_set_id = l_value_set_id
454 and h1.child_id <> h.child_id)
455 /* restrict to lowest level when overrides exist */
456 /* select max level num parent existing for given child within ID */
457 and h.parent_depth_num = (select max(h1.parent_depth_num)
458 from fem_ln_items_hier h1,
459 ftp_adjustment_rule q
460 where h1.hierarchy_obj_def_id
461 = hier_id
462 and q.object_definition_id = adj_obj_def
463 and q.line_item_id = h1.parent_id
464 and h1.parent_value_set_id
465 = l_value_set_id
466 and h1.child_value_set_id
467 = l_value_set_id
468 and q.currency = cur.currency
469 and h1.child_id = h.child_id)) adj
470 ON (nm.line_item_id = adj.child_id and nm.currency=cur.currency
471 and node_map_id = map_id)
472 WHEN MATCHED THEN
473 update set adj_node = adj.parent_id
474 WHEN NOT MATCHED THEN
475 insert (node_map_id, line_item_id,value_set_id,currency,
476 adj_node, tp_node, pp_node,
477 creation_date, created_by, last_updated_by,
478 last_update_date, last_update_login,
479 created_by_object_id,created_by_request_id,
480 last_updated_by_object_id,last_updated_by_request_id) values
481 (map_id, adj.child_id, l_value_set_id,
482 cur.currency,adj.parent_id,null,
483 null, sysdate,
484 user_id, user_id, sysdate, login_id,
485 OBJECT_ID,REQUEST_ID,OBJECT_ID,REQUEST_ID);
486 --l_count := SQL%ROWCOUNT;
487 --DBMS_OUTPUT.PUT_LINE('Merge Insert:'||l_count);
488 end loop;
489
490 end populate_hierarchical_adj;
491 -- builds node map given tp, pp and adj object IDs and associated hierarchies
492 -- if they exist.
493 procedure build_node_map(map_id in number,
494 l_value_set_id in number,
495 tp_obj_def in number,
496 tp_hier_id in number,
497 pp_obj_def in number,
498 pp_hier_id in number,
499 l_adj_valueset_id in number,
500 adj_obj_def in number,
501 adj_hier_id in number) is
502 begin
503
504 --Look for Transfer Price rule
505 if tp_obj_def is not null then
506 if tp_hier_id is null then
507 populate_non_hier_tp(map_id, tp_obj_def, l_value_set_id);
508 else
509 populate_hierarchical_tp(map_id, tp_obj_def,
510 l_value_set_id, tp_hier_id);
511 end if;
512 end if;
513
514 --Look for Prepayment rule
515 if pp_obj_def is not null then
516 if pp_hier_id is null then
517 populate_non_hier_pp(map_id, pp_obj_def, l_value_set_id);
518 else
519 populate_hierarchical_pp(map_id, pp_obj_def,
520 l_value_set_id, pp_hier_id);
521 end if;
522 end if;
523
524 --Look for Adjustment rule
525 if adj_obj_def is not null then
526 if adj_hier_id is null then
527 populate_non_hier_adj(map_id, adj_obj_def, l_adj_valueset_id);
528 else
529 populate_hierarchical_adj(map_id, adj_obj_def,
530 l_adj_valueset_id, adj_hier_id);
531 end if;
532 end if;
533 end build_node_map;
534 -- Creates a Node Map Header entry for the given processing ID
535 -- returns map_id
536 function create_new_map_header(proc_def_id in number,
537 tp_def_id in number,
538 pp_def_id in number,
539 adj_def_id in number,
540 e_s_date in date,
541 e_e_date in date) return number
542 is
543 map_id number;
544 begin
545 select ftp_node_map_id_seq.nextval into map_id from dual;
546 insert into ftp_tp_pp_node_header (
547 node_map_id,
548 tp_process_object_def_id,
549 tp_object_def_id,
550 pp_object_def_id,
551 adj_object_def_id,
552 effective_start_date,
553 effective_end_date,
554 creation_date,
555 created_by,
556 last_updated_by,
557 last_update_date,
558 last_update_login,
559 created_by_object_id,
560 created_by_request_id,
561 last_updated_by_object_id,
562 last_updated_by_request_id
563 ) values (
564 map_id,
565 proc_def_id,
566 tp_def_id,
567 pp_def_id,
568 adj_def_id,
569 e_s_date,
570 e_e_date,
571 sysdate,
572 fnd_global.user_id,
573 fnd_global.user_id,
574 sysdate,
575 fnd_global.login_id,
576 OBJECT_ID,
577 REQUEST_ID,
578 OBJECT_ID,
579 REQUEST_ID
580 );
581 return map_id;
582 end create_new_map_header;
583 procedure get_tp_info(
584 proc_def_id in number,
585 e_date in date,
586 tp_info out nocopy obj_info_type,
587 tp_hier_info out nocopy obj_info_type,
588 pp_info out nocopy obj_info_type,
589 pp_hier_info out nocopy obj_info_type,
590 adj_info out nocopy obj_info_type,
591 adj_hier_info out nocopy obj_info_type)
592 is
593 tp_obj_id number; -- tp object/object_def
594 pp_obj_id number; -- pp object/object_def
595 adj_obj_id number; -- adj object/object_def
596 begin
597 select transfer_price_object_id, prepay_object_id, adjustment_object_id
598 into tp_obj_id, pp_obj_id, adj_obj_id
599 from ftp_tp_process_rule
600 where object_definition_id = proc_def_id;
601
602 -- convert object ids to obj_def_id
603 --get Transfer Price obj def if used for this rule
604 if tp_obj_id is not null and tp_obj_id > 0 then
605 get_obj_def(tp_obj_id,
606 e_date,
607 tp_info);
608 get_hier_info(tp_obj_id,
609 e_date,
610 tp_hier_info);
611 end if;
612
613 -- get prepayment def id if used for this rule
614 if pp_obj_id is not null and pp_obj_id > 0 then
615 get_obj_def(pp_obj_id,
616 e_date,
617 pp_info);
618 get_hier_info(pp_obj_id,
619 e_date,
620 pp_hier_info);
621 end if;
622
623 -- get adjustment def id if used for this rule
624 if adj_obj_id is not null and adj_obj_id > 0 then
625 get_obj_def(adj_obj_id,
626 e_date,
627 adj_info);
628 get_hier_info(adj_obj_id,
629 e_date,
630 adj_hier_info);
631 end if;
632 end get_tp_info;
633 --Check for hierarchy updates
634 procedure check_hier_update (map_id in number,
635 proc_def_id in number,
636 tp_def_id in number,
637 pp_def_id in number,
638 adj_def_id in number,
639 e_date in date,
640 tp_hier_def_id in number,
641 pp_hier_def_id in number,
642 adj_hier_def_id in number,
643 hier_updated out nocopy boolean
644 )
645 is
646 l_tp_hier_date DATE;
647 l_pp_hier_date DATE := NULL;
648 l_adj_hier_date DATE := NULL;
649 l_node_map_date DATE;
650 begin
651 --get the latest last_update_date
652 select max(last_update_date) into l_tp_hier_date from fem_ln_items_hier h
653 where h.hierarchy_obj_def_id = tp_hier_def_id group by h.hierarchy_obj_def_id;
654
655 if pp_def_id is not null then
656 if pp_hier_def_id is not null then
657 select max(last_update_date) into l_pp_hier_date from fem_ln_items_hier h
658 where h.hierarchy_obj_def_id = pp_hier_def_id group by h.hierarchy_obj_def_id;
659 end if;
660 end if;
661
662 if adj_def_id is not null then
663 if adj_hier_def_id is not null then
664 select max(last_update_date) into l_adj_hier_date from fem_ln_items_hier h
665 where h.hierarchy_obj_def_id = adj_hier_def_id group by h.hierarchy_obj_def_id;
666 end if;
667 end if;
668
669 --get the last_update_date of node map table
670 select last_update_date into l_node_map_date from ftp_tp_pp_node_header where
671 tp_process_object_def_id = proc_def_id
672 and (tp_object_def_id = tp_def_id
673 or tp_object_def_id is null and tp_def_id is null)
674 and (pp_object_def_id = pp_def_id
675 or pp_object_def_id is null and pp_def_id is null)
676 and (adj_object_def_id = adj_def_id
677 or adj_object_def_id is null and adj_def_id is null)
678 and trunc(e_date) between effective_start_date and
679 nvl(effective_end_date,
680 to_date('99991231',
681 'YYYYMMDD'));
682
683 --check tp hier date
684 if(l_tp_hier_date > l_node_map_date)then
685 hier_updated := TRUE;
686 else
687 hier_updated := FALSE;
688 end if;
689 --check pp hier date
690 if(l_pp_hier_date is not null)then
691 if(l_pp_hier_date > l_node_map_date)then
692 hier_updated := TRUE;
693 else
694 hier_updated := FALSE;
695 end if;
696 end if;
697 --check adj hier date
698 if(l_adj_hier_date is not null)then
699 if(l_adj_hier_date > l_node_map_date)then
700 hier_updated := TRUE;
701 else
702 hier_updated := FALSE;
703 end if;
704 end if;
705 --update the header table to set last_update_date.
706 if hier_updated then
707 update ftp_tp_pp_node_header a
708 set last_updated_by = fnd_global.user_id,
709 last_update_date = sysdate,
710 last_update_login = fnd_global.login_id
711 where a.node_map_id = map_id;
712 end if;
713
714 exception
715 when others then
716 hier_updated := FALSE;
717 end check_hier_update;
718
719 -- updates end date for node_map_id
720 procedure fix_end_date(map_id in number,
721 process_info in obj_info_type)
722 is
723 tp_info obj_info_type;
724 pp_info obj_info_type;
725 adj_info obj_info_type;
726 tp_hier_info obj_info_type;
727 pp_hier_info obj_info_type;
728 adj_hier_info obj_info_type;
729 s_date date;
730 e_date date;
731 begin
732 -- get start date
733 select effective_start_date into s_date
734 from ftp_tp_pp_node_header
735 where node_map_id = map_id;
736 get_tp_info(process_info.obj_id,
737 s_date,
738 tp_info,
739 tp_hier_info,
740 pp_info,
741 pp_hier_info,
742 adj_info,
743 adj_hier_info);
744 get_min_range(obj_info_array(process_info,
745 tp_info,
746 pp_info,
747 adj_info,
748 tp_hier_info,
749 pp_hier_info,
750 adj_hier_info),
751 s_date,
752 e_date);
753 -- set end date to end date from previous.
754 update ftp_tp_pp_node_header a
755 set effective_end_date = e_date,
756 last_updated_by = fnd_global.user_id,
757 last_update_date = sysdate,
758 last_update_login = fnd_global.login_id
759 where a.node_map_id = map_id;
760 end fix_end_date;
761 procedure VALIDATE_NODE_MAP(
762 OBJ_ID IN NUMBER,
763 REQ_ID IN NUMBER,
764 EFFECTIVE_DATE IN DATE,
765 NODE_MAP_ID OUT NOCOPY NUMBER,
766 DIM_COL_NAME OUT NOCOPY VARCHAR2)
767 is
768 process_info obj_info_type;
769 tp_info obj_info_type;
770 pp_info obj_info_type;
771 adj_info obj_info_type;
772 tp_hier_info obj_info_type;
773 pp_hier_info obj_info_type;
774 adj_hier_info obj_info_type;
775 map_id obj_info_type;
776 s_date date;
777 e_date date;
778 hier_updated boolean;
779 begin
780 -- Save the object id and request id
781 OBJECT_ID := OBJ_ID;
782 REQUEST_ID := REQ_ID;
783
784 get_obj_def(OBJ_ID, EFFECTIVE_DATE, process_info);
785 get_tp_info(process_info.obj_id,
786 effective_date,
787 tp_info,
788 tp_hier_info,
789 pp_info,
790 pp_hier_info,
791 adj_info,
792 adj_hier_info);
793 -- Could check here to verify that if there is a prepayment ID,
794 -- the value_set_id for both of them match.
795 get_min_range(obj_info_array(process_info,
796 tp_info,
797 pp_info,
798 adj_info,
799 tp_hier_info,
800 pp_hier_info,
801 adj_hier_info),
802 s_date,
803 e_date);
804 begin
805 select node_map_id,
806 effective_start_date,
807 effective_end_date
808 into map_id.obj_id,
809 map_id.eff_start_date,
810 map_id.eff_end_date
811 from ftp_tp_pp_node_header
812 where tp_process_object_def_id = process_info.obj_id
813 and (tp_object_def_id = tp_info.obj_id
814 or tp_object_def_id is null and tp_info.obj_id is null)
815 and (pp_object_def_id = pp_info.obj_id
816 or pp_object_def_id is null and pp_info.obj_id is null)
817 and (adj_object_def_id = adj_info.obj_id
818 or adj_object_def_id is null and adj_info.obj_id is null)
819 and trunc(effective_date) between effective_start_date and
820 nvl(effective_end_date,
821 to_date('99991231',
822 'YYYYMMDD'));
823 node_map_id := map_id.obj_id;
824 -- verify that we do indeed have the correct version.
825 if s_date <> map_id.eff_start_date then
826 -- there is been a change (i.e., a hierarchy or underlying ID has
827 -- changed). Fix the end date for the map_id so it doesn't
828 -- show up again.
829 fix_end_date(node_map_id, process_info);
830 node_map_id := null;
831 end if;
832
833 if node_map_id is not null then
834 --check for hier update
835 check_hier_update(node_map_id,
836 process_info.obj_id,
837 tp_info.obj_id,
838 pp_info.obj_id,
839 adj_info.obj_id,
840 effective_date,
841 tp_hier_info.obj_id,
842 pp_hier_info.obj_id,
843 adj_hier_info.obj_id,
844 hier_updated
845 );
846 end if;
847 exception when NO_DATA_FOUND then null; -- no obj_id
848 end;
849 if node_map_id is null then
850 -- need new ID
851 node_map_id := create_new_map_header(process_info.obj_id,
852 tp_info.obj_id,
853 pp_info.obj_id,
854 adj_info.obj_id,
855 s_date,
856 e_date);
857 build_node_map(node_map_id,
858 tp_info.value_set_id,
859 tp_info.obj_id,
860 tp_hier_info.obj_id,
861 pp_info.obj_id,
862 pp_hier_info.obj_id,
863 adj_info.value_set_id,
864 adj_info.obj_id,
865 adj_hier_info.obj_id);
866 else
867 if e_date <> map_id.eff_end_date then
868 -- effective end date has changed, update it.
869 update ftp_tp_pp_node_header a
870 set effective_end_date = e_date,
871 last_updated_by = fnd_global.user_id,
872 last_update_date = sysdate,
873 last_update_login = fnd_global.login_id,
874 -- The object id will always be the same.
875 created_by_request_id = REQUEST_ID,
876 last_updated_by_request_id = REQUEST_ID
877 where a.node_map_id = validate_node_map.node_map_id;
878 end if;
879
880 --if hierarchy updated, update the node map detail table
881 if hier_updated then
882 --delete existing details and recreate them
883 delete ftp_tp_pp_node_map a where a.node_map_id = node_map_id;
884 build_node_map(node_map_id,
885 tp_info.value_set_id,
886 tp_info.obj_id,
887 tp_hier_info.obj_id,
888 pp_info.obj_id,
889 pp_hier_info.obj_id,
890 adj_info.value_set_id,
891 adj_info.obj_id,
892 adj_hier_info.obj_id);
893 end if;
894 end if;
895 -- set dim_col_name
896 dim_col_name := main_dim_col_name;
897 end VALIDATE_NODE_MAP;
898 -- return the appropirate instrument table columns
899 -- to update given the tp process id (to allow for
900 -- future expansion to support multiple transfer rate
901 -- columns.
902 procedure GET_TP_OUT_COLS(
903 obj_id in number,
904 data_set_id in number,
905 jobid in number,
906 effective_date in date,
907 TRATE_COL out nocopy varchar2,
908 MSPREAD_COL out nocopy varchar2,
909 OAS_COL out nocopy varchar2,
910 SS_COL out nocopy varchar2,
911 LAST_OBJID_COL out nocopy varchar2,
912 LAST_REQID_COL out nocopy varchar2
913 ) is
914 process_info OBJ_INFO_TYPE;
915 remain_term_flag FTP_TP_PROCESS_RULE.CALC_MODE_CODE%TYPE;
916 magic_val varchar2(2000);
917 begin
918 get_obj_def(obj_id, effective_date, process_info);
919 --modified to read from ftp_tp_proc_stoch_params from earlier ftp_tp_process_rule
920
921 select calc_mode_code into remain_term_flag from ftp_tp_proc_stoch_params
922 where object_definition_id = process_info.obj_id and job_id = jobid;
923 -- this allows us more flexibility later, if we want we can
924 -- vary these by having the process record them or using
925 -- some other mechanism.
926 if remain_term_flag = 0 then
927 TRATE_COL := 'transfer_rate';
928 MSPREAD_COL := 'matched_spread'; --Changed from matched_spread_c
929 OAS_COL := 'historic_oas';
930 SS_COL := 'historic_static_spread';
931 else -- remaining term
932 TRATE_COL := 'tran_rate_rem_term';
933 MSPREAD_COL := '';
934 OAS_COL := 'cur_oas';
935 SS_COL := 'cur_static_spread';
936 end if;
937 LAST_OBJID_COL := 'last_updated_by_object_id';
938 LAST_REQID_COL := 'last_updated_by_request_id';
939 -- this here simplifies my debug situation
940 begin
941 fnd_profile.get('MAGIC_DATA_SET_TRAN_RATE', magic_val);
942 exception when others then magic_val := null;
943 end;
944 if (data_set_id > 0 and magic_val = 'MAGIC') then
945 trate_col := trate_col || '_' || data_set_id;
946 if mspread_col is not null then
947 mspread_col := mspread_col || '_' || data_set_id;
948 end if;
949 oas_col := oas_col || '_' || data_set_id;
950 ss_col := ss_col || '_' || data_set_id;
951 end if;
952
953 exception
954 when others then NULL;
955
956 end GET_TP_OUT_COLS;
957 -- return information for joining ftp_pp_node_map to
958 -- attribute table to get account type.
959 -- aliases needed to properly generate where clause.
960 procedure ACCT_TYPE_JOIN_INFO(
961 TBL_ALIAS in varchar2, -- alias of main table
962 TBL_JOIN_ALIAS in varchar2, -- alias of attribute table
963 JOIN_TBL_NAME out NOCOPY varchar2, -- table to join to
964 ATTR_COL_NAME out NOCOPY varchar2, -- attribute column to select
965 IS_ASSET_DECODE out NOCOPY varchar2, -- decode to determine if asset/liab
966 WHERE_CLAUSE out NOCOPY varchar2 -- where clause for join
967 )
968 is
969 begin
970 select attribute_table_name into join_tbl_name
971 from fem_xdim_dimensions
972 where dimension_id = main_dim_id;
973 select attribute_value_column_name into attr_col_name
974 from fem_dim_attributes_b
975 where dimension_id = main_dim_id
976 and attribute_id = acct_type_attr_id;
977 where_clause := tbl_alias || '.line_item_id'
978 || '=' || tbl_join_alias || '.' || main_dim_col_name
979 || '(+) and '
980 || tbl_alias || '.value_set_id'
981 || '=' || tbl_join_alias || '.value_set_id(+)'
982 || ' and '
983 || tbl_join_alias || '.attribute_id(+)='
984 || acct_type_attr_id;
985 -- populate IS_ASSET_DECODE -- 1 if asset, 0 liability
986 -- second bit if off balance sheet
987 is_asset_decode := 'decode(' || tbl_join_alias || '.' || attr_col_name;
988 declare
989 -- this is really ugly because the FEM data modeling group refuses
990 -- to do anything in the most straightforward way
991 -- I refuse to look up the dim_attriube_name column
992 cursor c is
993 select ext.ext_account_type_code,
994 (decode(af.dim_attribute_varchar_member, 'Y', 1, 0)
995 + decode(obf.dim_attribute_varchar_member, 'Y', 2, 0)) flags
996 from fem_ext_account_types_b ext,
997 fem_ext_acct_types_attr af,
998 fem_ext_acct_types_attr obf
999 where ext.ext_account_type_code = af.ext_account_type_code
1000 and af.attribute_id = ASSET_FLAG_ID
1001 and ext.ext_account_type_code = obf.ext_account_type_code
1002 and obf.attribute_id = OFF_BAL_FLAG_ID;
1003 begin
1004 for cur in c loop
1005 is_asset_decode := is_asset_decode || ','''
1006 || cur.ext_account_type_code || ''',' || cur.flags;
1007 end loop;
1008 -- second to last case is null case, last case is unknown
1009 -- third bit set means there was none defined,
1010 -- fourth bit set means the type was invalid
1011 is_asset_decode := is_asset_decode || ',null,5,9)';
1012 end;
1013 end ACCT_TYPE_JOIN_INFO;
1014 -- this is not yet complete (nor are we sure it will even be used)
1015 procedure REGISTER_TP_PROCESS(
1016 OBJ_ID in number,
1017 LEDGER_ID in number,
1018 EFFECTIVE_DATE in date,
1019 PROCESS_PARAM_ID out NOCOPY number
1020 ) is
1021 begin
1022 -- register with fem_dimensions_pkg
1023 fem_dimension_util_pkg.fem_initialize(ledger_id);
1024 process_param_id := -1;
1025 end REGISTER_TP_PROCESS;
1026 -- return information for joining ftp_pp_node_map to
1027 -- attribute table to get account type.
1028 -- aliases needed to properly generate where clause.
1029 procedure CHG_CRDT_ACC_BASIS_JOIN(
1030 TBL_ALIAS in varchar2, -- alias of main table
1031 TBL_JOIN_ALIAS in varchar2, -- alias of attribute table
1032 JOIN_TBL_NAME out NOCOPY varchar2, -- table to join to
1033 Attr_COL_NAME out NOCOPY varchar2, -- attribute column to select
1034 ACCR_DECODE out NOCOPY varchar2, -- decode to determine if asset/liab
1035 WHERE_CLAUSE out NOCOPY varchar2 -- where clause for join
1036 )
1037 is
1038 begin
1039 join_tbl_name := 'FTP_LN_ITEM_CURRENCIES'; -- Changed to FTP_LN_ITEM_CURRENCIES since used only by FTP -Mallica
1040 attr_col_name := 'TP_CHG_CRD_ACCRUAL_BASIS_CODE';
1041 where_clause := tbl_alias || '.line_item_id'
1042 || '=' || tbl_join_alias || '.line_item_id(+)'
1043 || ' and '
1044 || tbl_alias || '.value_set_id'
1045 || '=' || tbl_join_alias || '.value_set_id'
1046 || ' and '
1047 || tbl_alias || '.currency'
1048 || '=' || tbl_join_alias || '.currency_code(+)';
1049 -- this will probably change
1050 accr_decode := tbl_join_alias || '.' || attr_col_name;
1051 end CHG_CRDT_ACC_BASIS_JOIN;
1052
1053 /***************************************************************************
1054 Desc : Procedure queries the list of input data set codes. Provides a list
1055 of input dataset codes in the form a string.
1056 Pgmr : Raghuram K Nanda
1057 Date : 16-Aug-2005
1058 **************************************************************************/
1059 PROCEDURE get_input_datasets(
1060 p_io_def_id IN NUMBER,
1061 x_datasets IN OUT NOCOPY VARCHAR2
1062 )
1063 IS
1064
1065 BEGIN
1066
1067 FOR indx IN (select input_dataset_code from fem_ds_input_lists
1068 where dataset_io_obj_def_id = p_io_def_id)
1069 LOOP
1070
1071 x_datasets := x_datasets || indx.input_dataset_code ||',';
1072
1073 END LOOP;
1074
1075 IF (x_datasets IS NULL)
1076 THEN
1077 RAISE FND_API.G_EXC_ERROR;
1078 END IF;
1079
1080 x_datasets := '(' || RTRIM(x_datasets,',') || ')';
1081
1082 END get_input_datasets;
1083
1084 /***************************************************************************
1085 Desc : Procedure to initiate creation of process locks metadata required
1086 for the process run.
1087 Pgmr : Raghuram K Nanda
1088 Date : 16-Aug-2005
1089 -- Parameters
1090 -- OUT
1091 -- x_exec_lock_exists OUT VARCHAR2
1092 -- Indicates whether an execution lock exists.
1093 -- Returns 'T' if an execution lock exists, and 'F' if an execution
1094 -- lock does not exist.
1095 -- x_return_status OUT VARCHAR2
1096 -- Possible return status:
1097 -- FND_API.G_RET_STS_SUCCESS - Call was successful, msgs may
1098 -- still be present (check x_msg_count)
1099 -- FND_API.G_RET_STS_ERROR - Call was not successful, msgs should
1100 -- be present (check x_msg_count)
1101 -- FND_API.G_RET_STS_UNEXP_ERROR - Unexpected errors occurred which are
1102 -- unrecoverable (check x_msg_count)
1103 --
1104 -- x_msg_count OUT NUMBER
1105 -- Count of messages returned. If x_msg_count = 1, then the message is returned
1106 -- in x_msg_data. If x_msg_count > 1, then messages are returned via FND_MSG_PUB.
1107 --
1108 -- x_msg_data OUT VARCHAR2
1109 -- Error message returned.
1110 **************************************************************************/
1111 PROCEDURE START_PROCESS_LOCKS(
1112 p_request_id IN NUMBER,
1113 p_object_id IN NUMBER,
1114 p_cal_period_id IN NUMBER,
1115 p_ledger_id IN NUMBER,
1116 p_dataset_def_id IN NUMBER,
1117 p_job_id IN NUMBER,
1118 p_condition_id IN NUMBER,
1119 p_effective_date IN DATE,
1120 p_user_id IN NUMBER,
1121 p_last_update_login IN NUMBER,
1122 p_program_id IN NUMBER,
1123 p_program_login_id IN NUMBER,
1124 p_program_application_id IN NUMBER,
1125 x_exec_lock_exists OUT NOCOPY VARCHAR2,
1126 x_return_status OUT NOCOPY VARCHAR2,
1127 x_msg_count OUT NOCOPY NUMBER,
1128 x_msg_data OUT NOCOPY VARCHAR2
1129 )
1130 IS
1131 process_info obj_info_type;
1132 l_output_ds NUMBER;
1133 l_data_table VARCHAR2(30);
1134 l_exception_code VARCHAR2(80);
1135 l_msg VARCHAR2(300);
1136 l_tbl_alias VARCHAR2(1);
1137 l_condition_id NUMBER;
1138 l_condition_sql VARCHAR2(4000) := NULL;
1139 l_exec_state VARCHAR2(30); -- normal, restart, rerun
1140 l_prev_request_id NUMBER;
1141 l_date_str VARCHAR2(26);
1142 l_stmt_type VARCHAR2(10);
1143
1144 TYPE varchar_std_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
1145 l_table_names varchar_std_type;
1146
1147 -- cursor that retrieves the tables that should be used as source
1148 CURSOR l_cur_tables IS select table_name from ftp_tp_proc_tabls_params
1149 where object_definition_id = process_info.obj_id and job_id = p_job_id;
1150
1151 BEGIN
1152 /*FEM_ENGINES_PKG.Put_Message(
1153 p_app_name => 'FTP',
1154 p_msg_name => 'START_PROCESS_LOCKS.BEGIN',
1155 p_token1 => 'OBJ_ID',
1156 p_value1 => p_object_id,
1157 p_token2 => 'EFF_DATE',
1158 p_value2 => p_effective_date
1159 );*/
1160 -- initialize our status to 'we are good!'
1161 x_return_status := FND_API.G_RET_STS_SUCCESS;
1162
1163 -- Initialize FND message queue
1164 FND_MSG_PUB.Initialize;
1165
1166 savepoint register_request_pub;
1167
1168 -- Get the object definition id of the process
1169 get_obj_def(p_object_id, p_effective_date, process_info);
1170
1171 --Get the output dataset code for the given IODD
1172 select output_dataset_code into l_output_ds from fem_ds_input_output_defs
1173 where dataset_io_obj_def_id = p_dataset_def_id;
1174
1175 /*FEM_ENGINES_PKG.Put_Message(
1176 p_app_name => 'FTP',
1177 p_msg_name => 'START_PROCESS_LOCKS',
1178 p_token1 => 'OUT_DS',
1179 p_value1 => l_output_ds
1180 );*/
1181 BEGIN
1182 select filter_object_id into l_condition_id from ftp_tp_proc_stoch_params where
1183 object_definition_id = process_info.obj_id and job_id = p_job_id;
1184 EXCEPTION
1185 when others then
1186 -- when no condition id, still fine
1187 NULL;
1188 END;
1189
1190 IF l_condition_id = 0 THEN
1191 l_condition_id := NULL;
1192 END IF;
1193
1194 /*FEM_ENGINES_PKG.Put_Message(
1195 p_app_name => 'FTP',
1196 p_msg_name => 'START_PROCESS_LOCKS',
1197 p_token1 => 'COND_ID',
1198 p_value1 => l_condition_id
1199 );*/
1200
1201 --Do the request registration
1202 FEM_PL_PKG.register_request(
1203 p_api_version => c_api_version,
1204 p_commit => FND_API.G_FALSE,
1205 p_request_id => p_request_id,
1206 p_cal_period_id => p_cal_period_id,
1207 p_ledger_id => p_ledger_id,
1208 p_dataset_io_obj_def_id => p_dataset_def_id,
1209 p_output_dataset_code => l_output_ds,
1210 p_effective_date => trunc(p_effective_date),
1211 p_rule_set_obj_def_id => 0 /*process_info.obj_id*/, --<< FIX IT >>
1212 p_user_id => p_user_id,
1213 p_last_update_login => p_last_update_login,
1214 p_program_id => p_program_id,
1215 p_program_login_id => p_program_login_id,
1216 p_program_application_id => p_program_application_id,
1217 x_msg_count => x_msg_count,
1218 x_msg_data => x_msg_data,
1219 x_return_status => x_return_status
1220 );
1221
1222 /*FEM_ENGINES_PKG.Put_Message(
1223 p_app_name => 'FTP',
1224 p_msg_name => 'AFTER REGISTER REQUEST',
1225 p_token1 => 'MSG_COUNT',
1226 p_value1 => x_msg_count,
1227 p_token2 => 'MSG_DATA',
1228 p_value2 => x_msg_data
1229 );*/
1230
1231 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1232 RAISE FND_API.G_EXC_ERROR;
1233 END IF;
1234
1235 -- Read the rule to retieve the names of the tables to process
1236 OPEN l_cur_tables;
1237 FETCH l_cur_tables BULK COLLECT INTO l_table_names;
1238 CLOSE l_cur_tables;
1239
1240 l_date_str := FND_DATE.date_to_canonical(p_effective_date);
1241
1242 /*FEM_ENGINES_PKG.Put_Message(
1243 p_app_name => 'FTP',
1244 p_msg_name => 'IN START PROCESS LOCK',
1245 p_token1 => 'DATE_STR',
1246 p_value1 => l_date_str
1247 );*/
1248
1249 FOR i IN 1..l_table_names.COUNT
1250 LOOP
1251 -- Get the table name to be processed.
1252 l_data_table := l_table_names(i);
1253 IF (l_condition_id IS NOT NULL)
1254 THEN
1255 l_condition_sql := NULL;
1256 /*FEM_ENGINES_PKG.Put_Message(
1257 p_app_name => 'FTP',
1258 p_msg_name => 'IN START PROCESS LOCK',
1259 p_token1 => 'TABLE_NAME',
1260 p_value1 => l_data_table,
1261 p_token2 => 'ALIAS',
1262 p_value2 => l_tbl_alias
1263 );*/
1264 /*FEM_CONDITIONS_API.generate_condition_predicate(
1265 x_msg_count => x_msg_count,
1266 x_msg_data => x_msg_data,
1267 x_return_status => x_return_status,
1268 p_condition_obj_id => l_condition_id,
1269 p_rule_effective_date => l_date_str,
1270 p_input_fact_table_name => l_data_table,
1271 p_table_alias => l_tbl_alias,
1272 p_display_predicate => 'N',
1273 p_return_predicate_type => 'BOTH',
1274 p_logging_turned_on => 'N',
1275 x_predicate_string => l_condition_sql);*/
1276 BEGIN
1277 FEM_CONDITIONS_API.GENERATE_CONDITION_PREDICATE(
1278 x_msg_count, x_msg_data, l_condition_id,
1279 l_date_str,
1280 l_data_table,
1281 l_tbl_alias, 'N', 'BOTH', 'Y',
1282 l_condition_sql );
1283 EXCEPTION
1284 when others then
1285 FEM_ENGINES_PKG.Put_Message(
1286 p_app_name => 'FTP',
1287 p_msg_name => 'WCLAUSE EXCEPTION',
1288 p_token1 => 'TABLE_NAME',
1289 p_value1 => l_data_table,
1290 p_token2 => 'MSG_DATA',
1291 p_value2 => x_msg_data
1292 );
1293 END;
1294
1295 END IF;
1296 /*FEM_ENGINES_PKG.Put_Message(
1297 p_app_name => 'FTP',
1298 p_msg_name => 'AFTER CALL TO CONDITIONS API',
1299 p_token1 => 'OBJ_ID',
1300 p_value1 => p_object_id,
1301 p_token2 => 'condition',
1302 p_value2 => l_condition_sql,
1303 p_token3 => 'TABLE',
1304 p_value3 => l_data_table
1305 );*/
1306 --Check to see if chaining exists
1307 chaining_exists(
1308 p_request_id => p_request_id,
1309 p_object_id => p_object_id,
1310 p_cal_period_id => p_cal_period_id,
1311 p_ledger_id => p_ledger_id,
1312 p_dataset_def_id => p_dataset_def_id,
1313 p_condition_str => l_condition_sql,
1314 p_effective_date => p_effective_date,
1315 p_table_name => l_data_table,
1316 x_exec_lock_exists => x_exec_lock_exists
1317 );
1318
1319
1320 IF x_exec_lock_exists = 'T' THEN
1321 /*FEM_ENGINES_PKG.Put_Message(
1322 p_app_name => 'FTP',
1323 p_msg_name => 'AFTER CHAINING EXISTS',
1324 p_token1 => 'CAL_ID',
1325 p_value1 => p_cal_period_id
1326 );*/
1327 --undo request registration
1328 rollback to register_request_pub;
1329 l_table_names.DELETE;
1330 RETURN;
1331 END IF;
1332
1333 END LOOP;
1334
1335 --Check if object executions exists. If not then register
1336 FEM_PL_PKG.register_object_execution(
1337 p_api_version => c_api_version,
1338 p_request_id => p_request_id,
1339 p_object_id => p_object_id,
1340 p_exec_object_definition_id => process_info.obj_id,
1341 p_user_id => p_user_id,
1342 p_last_update_login => p_last_update_login,
1343 x_exec_state => l_exec_state,
1344 x_prev_request_id => l_prev_request_id,
1345 x_msg_count => x_msg_count,
1346 x_msg_data => x_msg_data,
1347 x_return_status => x_return_status
1348 );
1349
1350 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1351 RAISE FND_API.G_EXC_ERROR;
1352 END IF;
1353
1354 /*--Register all obj defs read during processing
1355 FEM_PL_PKG.register_object_def(
1356 p_api_version => c_api_version,
1357 p_request_id => p_request_id,
1358 p_object_id => p_object_id,
1359 p_object_definition_Id => process_info.obj_id,
1360 p_user_id => p_user_id,
1361 p_last_update_login => p_last_update_login,
1362 x_msg_count => x_msg_count,
1363 x_msg_data => x_msg_data,
1364 x_return_status => x_return_status
1365 );
1366
1367 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1368 RAISE FND_API.G_EXC_ERROR;
1369 END IF;*/
1370
1371 -- Register all dependent object definitions
1372 FEM_PL_PKG.register_dependent_objdefs(
1373 p_api_version => c_api_version,
1374 p_request_id => p_request_id,
1375 p_object_id => p_object_id,
1376 p_exec_object_definition_id => process_info.obj_id,
1377 p_effective_date => trunc(p_effective_date),
1378 p_user_id => p_user_id,
1379 p_last_update_login => p_last_update_login,
1380 x_msg_count => x_msg_count,
1381 x_msg_data => x_msg_data,
1382 x_return_status => x_return_status
1383 );
1384 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1385 RAISE FND_API.G_EXC_ERROR;
1386 END IF;
1387
1388 --Register all the selected tables that are written to, during processing
1389 FOR i IN 1 .. l_table_names.COUNT LOOP
1390 -- Set the statement typ
1391 IF (l_table_names(i) <> 'FEM_BALANCES')
1392 THEN
1393 l_stmt_type := 'UPDATE';
1394 ELSE
1395 l_stmt_type := c_stmt_type;
1396 END IF;
1397 FEM_PL_PKG.register_table(
1398 p_api_version => c_api_version,
1399 p_request_id => p_request_id,
1400 p_object_id => p_object_id,
1401 p_table_name => l_table_names(i),
1402 p_statement_type => l_stmt_type,
1403 p_num_of_output_rows => 0,
1404 p_user_id => p_user_id,
1405 p_last_update_login => p_last_update_login,
1406 x_msg_count => x_msg_count,
1407 x_msg_data => x_msg_data,
1408 x_return_status => x_return_status
1409 );
1410
1411 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1412 RAISE FND_API.G_EXC_ERROR;
1413 END IF;
1414 END LOOP;
1415
1416 -- Register the Node Map Tables as well
1417 -- Register FTP_TP_PP_NODE_HEADER and FTP_TP_PP_NODE_MAP
1418 FEM_PL_PKG.register_table(
1419 p_api_version => c_api_version,
1420 p_request_id => p_request_id,
1421 p_object_id => p_object_id,
1422 p_table_name => 'FTP_TP_PP_NODE_HEADER',
1423 p_statement_type => c_stmt_type,
1424 p_num_of_output_rows => 0,
1425 p_user_id => p_user_id,
1426 p_last_update_login => p_last_update_login,
1427 x_msg_count => x_msg_count,
1428 x_msg_data => x_msg_data,
1429 x_return_status => x_return_status
1430 );
1431
1432 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1433 RAISE FND_API.G_EXC_ERROR;
1434 END IF;
1435
1436 FEM_PL_PKG.register_table(
1437 p_api_version => c_api_version,
1438 p_request_id => p_request_id,
1439 p_object_id => p_object_id,
1440 p_table_name => 'FTP_TP_PP_NODE_MAP',
1441 p_statement_type => c_stmt_type,
1442 p_num_of_output_rows => 0,
1443 p_user_id => p_user_id,
1444 p_last_update_login => p_last_update_login,
1445 x_msg_count => x_msg_count,
1446 x_msg_data => x_msg_data,
1447 x_return_status => x_return_status
1448 );
1449
1450 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1451 RAISE FND_API.G_EXC_ERROR;
1452 END IF;
1453
1454 /*FEM_ENGINES_PKG.Put_Message(
1455 p_app_name => 'FTP',
1456 p_msg_name => 'AFTER register_table END',
1457 p_token1 => 'MSG_COUNT',
1458 p_value1 => x_msg_count,
1459 p_token2 => 'MSG_DATA',
1460 p_value2 => x_msg_data,
1461 p_token3 => 'RETURN_STATUS',
1462 p_value3 => x_return_status
1463 );*/
1464 l_table_names.DELETE;
1465 FND_MSG_PUB.Count_And_Get
1466 (p_encoded => 'F',
1467 p_count => x_msg_count,
1468 p_data => x_msg_data);
1469 EXCEPTION
1470 WHEN FND_API.G_EXC_ERROR THEN
1471 FND_MSG_PUB.Count_And_Get
1472 (p_encoded => 'F',
1473 p_count => x_msg_count,
1474 p_data => x_msg_data);
1475 x_return_status := FND_API.G_RET_STS_ERROR;
1476 rollback to register_request_pub;
1477
1478 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1479 l_msg := SUBSTR(SQLERRM, 1, 300);
1480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1481
1482 FEM_ENGINES_PKG.Put_Message(
1483 p_app_name => 'FTP',
1484 p_msg_name => 'UNEXP EXCEPTION',
1485 p_token1 => 'SQLERRM',
1486 p_value1 => l_msg
1487 );
1488 FND_MSG_PUB.Count_And_Get
1489 (p_encoded => 'F',
1490 p_count => x_msg_count,
1491 p_data => x_msg_data);
1492 rollback to register_request_pub;
1493
1494 WHEN OTHERS THEN
1495 l_msg := SUBSTR(SQLERRM, 1, 300);
1496
1497 --DBMS_OUTPUT.PUT_LINE(l_msg);
1498 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1499
1500 FEM_ENGINES_PKG.Put_Message(
1501 p_app_name => 'FTP',
1502 p_msg_name => 'UNEXP EXCEPTION',
1503 p_token1 => 'DATE',
1504 p_value1 => trunc(p_effective_date),
1505 p_token2 => 'SQLERRM',
1506 p_value2 => SQLERRM
1507 );
1508 FND_MSG_PUB.Count_And_Get
1509 (p_encoded => 'F',
1510 p_count => x_msg_count,
1511 p_data => x_msg_data);
1512
1513 rollback to register_request_pub;
1514
1515 END START_PROCESS_LOCKS;
1516
1517 /***************************************************************************
1518 Desc : Procedure to stop process locking.
1519 Pgmr : Raghuram K Nanda
1520 Date : 16-Aug-2005
1521 **************************************************************************/
1522 PROCEDURE STOP_PROCESS_LOCKS(
1523 p_request_id IN NUMBER,
1524 p_object_id IN NUMBER,
1525 p_cal_period_id IN NUMBER,
1526 p_ledger_id IN NUMBER,
1527 p_dataset_def_id IN NUMBER,
1528 p_exec_status_code IN VARCHAR2,
1529 p_job_id IN NUMBER,
1530 p_condition_id IN NUMBER,
1531 p_effective_date IN DATE,
1532 p_user_id IN NUMBER,
1533 p_last_update_login IN NUMBER,
1534 x_return_status OUT NOCOPY VARCHAR2,
1535 x_msg_count OUT NOCOPY NUMBER,
1536 x_msg_data OUT NOCOPY VARCHAR2
1537 )
1538 IS
1539
1540 l_input_cnt NUMBER;
1541 l_total_inp NUMBER;
1542 l_output_cnt NUMBER;
1543 l_total_out NUMBER;
1544 process_info obj_info_type;
1545 l_select_stmt VARCHAR2(4000);
1546 l_input_ds VARCHAR2(1000);
1547 l_exception_code VARCHAR2(80);
1548 l_msg VARCHAR2(4000);
1549 l_tbl_alias VARCHAR2(1);
1550 l_condition_sql VARCHAR2(4000);
1551 l_condition_id NUMBER;
1552 l_exec_status_code VARCHAR2(30);
1553 l_date_str VARCHAR2(26);
1554 l_stmt_type VARCHAR2(10);
1555
1556 l_b_data_location boolean;
1557 l_output_ds NUMBER;
1558 l_ss_code NUMBER;
1559
1560 f_set_status boolean;
1561
1562 TYPE varchar_std_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
1563
1564 l_table_names varchar_std_type;
1565 l_col_names varchar_std_type;
1566
1567 /*TYPE number_std_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1568 l_input_ds number_std_type;*/
1569
1570 -- cursor that retrieves the tables that should be used as source
1571 CURSOR l_cur_tables IS select table_name from ftp_tp_proc_tabls_params
1572 where object_definition_id = process_info.obj_id and job_id = p_job_id;
1573
1574 -- set up the flags
1575 trate_prop_flg ftp_tp_proc_stoch_params.trans_rate_propagate_flg%TYPE;
1576 trate_calc_flg ftp_tp_proc_stoch_params.trans_rate_calc_flg%TYPE;
1577 ocost_prop_flg ftp_tp_proc_stoch_params.option_cost_propagate_flg%TYPE;
1578 ocost_calc_flg ftp_tp_proc_stoch_params.option_cost_calc_flg%TYPE;
1579
1580 -- tdasgupt : Added for R12 Enhancements
1581 l_adj_calc_flag ftp_tp_proc_stoch_params.adj_calc_flg%TYPE;
1582 calcmode_flg ftp_tp_proc_stoch_params.calc_mode_code%TYPE;
1583 pos number;
1584 len number ;
1585 len2 number := 5;
1586 modify_valid_table_list varchar2(3000);
1587 token varchar2(300);
1588 i number;
1589
1590
1591 BEGIN
1592
1593 -- initialize our status to 'we are good!'
1594 x_return_status := FND_API.G_RET_STS_SUCCESS;
1595 FND_MSG_PUB.Delete_Msg();
1596 --Initialize sum variables
1597 l_total_inp := 0;
1598 l_total_out := 0;
1599 l_input_cnt := 0;
1600 l_output_cnt := 0;
1601 l_b_data_location := FALSE;
1602
1603
1604 FEM_ENGINES_PKG.TECH_MESSAGE(
1605 p_severity => fnd_log.level_error,
1606 p_module => g_block || '.STOP_PROCESS_LOCKS',
1607 p_msg_text => 'valid_table_list= '|| valid_table_list ||' g_alt_rate_obj.COUNT=' || g_alt_rate_obj.COUNT
1608 );
1609
1610 -- Get the object definition id of the process
1611 get_obj_def(p_object_id, p_effective_date, process_info);
1612
1613 --Get input dataset codes
1614 get_input_datasets(
1615 p_io_def_id => p_dataset_def_id,
1616 x_datasets => l_input_ds
1617 );
1618
1619 BEGIN
1620 select filter_object_id into l_condition_id from ftp_tp_proc_stoch_params where
1621 object_definition_id = process_info.obj_id and job_id = p_job_id;
1622 EXCEPTION
1623 when others then
1624 -- when no condition id, still fine
1625 NULL;
1626 END;
1627
1628 IF l_condition_id = 0 THEN
1629 l_condition_id := NULL;
1630 END IF;
1631
1632 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1633 RAISE FND_API.G_EXC_ERROR;
1634 END IF;
1635
1636 If (valid_table_list IS NOT NULL) THEN
1637 modify_valid_table_list := valid_table_list || ',';
1638 i := 1;
1639 while( len2 > 0) LOOP
1640 select INSTR(modify_valid_table_list,',') into pos from dual;
1641 select length(modify_valid_table_list) into len from dual;
1642 select substr(modify_valid_table_list,0,pos-1) into token from dual;
1643 select LTRIM(RTRIM(token,''''),'''') into token from dual;
1644
1645 FEM_ENGINES_PKG.TECH_MESSAGE(
1646 p_severity => fnd_log.level_error,
1647 p_module => g_block || '.tokens_generation',
1648 p_msg_text => 'token =' || token
1649 );
1650
1651 l_table_names(i) := token;
1652
1653 FEM_ENGINES_PKG.TECH_MESSAGE(
1654 p_severity => fnd_log.level_error,
1655 p_module => g_block || '.tokens_generation',
1656 p_msg_text => 'Before substr modify_valid_table_list= '|| modify_valid_table_list
1657 );
1658
1659 select substr(modify_valid_table_list,pos+1,len) into modify_valid_table_list from dual;
1660
1661 FEM_ENGINES_PKG.TECH_MESSAGE(
1662 p_severity => fnd_log.level_error,
1663 p_module => g_block || '.tokens_generation',
1664 p_msg_text => 'After substr modify_valid_table_list= '|| modify_valid_table_list
1665 );
1666
1667 select length(modify_valid_table_list) into len2 from dual;
1668 i := i + 1;
1669 END LOOP;
1670 ELSE
1671 OPEN l_cur_tables;
1672 FETCH l_cur_tables BULK COLLECT INTO l_table_names;
1673 CLOSE l_cur_tables;
1674 END IF;
1675
1676 l_date_str := FND_DATE.date_to_canonical(p_effective_date);
1677
1678 FEM_ENGINES_PKG.TECH_MESSAGE(
1679 p_severity => fnd_log.level_error,
1680 p_module => g_block || '.l_table_names preparation ready',
1681 p_msg_text => 'l_table_names.COUNT = '|| l_table_names.COUNT || 'l_table_names(1) =' || l_table_names(1)
1682 );
1683
1684 FOR i IN 1..l_table_names.COUNT
1685 LOOP
1686
1687 FEM_ENGINES_PKG.TECH_MESSAGE(
1688 p_severity => fnd_log.level_error,
1689 p_module => g_block || '.after tokenizing iterating in l_table_names',
1690 p_msg_text => 'l_table_names(i) =' || l_table_names(i)
1691 );
1692
1693 IF (l_condition_id IS NOT NULL)
1694 THEN
1695 /*FEM_CONDITIONS_API.generate_condition_predicate(
1696 x_msg_count => x_msg_count,
1697 x_msg_data => x_msg_data,
1698 x_return_status => x_return_status,
1699 p_condition_obj_id => l_condition_id,
1700 p_rule_effective_date => FND_DATE.date_to_canonical(p_effective_date),
1701 p_input_fact_table_name => l_table_names(i),
1702 p_table_alias => l_tbl_alias,
1703 p_display_predicate => 'N',
1704 p_return_predicate_type => 'BOTH',
1705 p_logging_turned_on => 'N',
1706 x_predicate_string => l_condition_sql);*/
1707 BEGIN
1708 FEM_CONDITIONS_API.GENERATE_CONDITION_PREDICATE(
1709 x_msg_count, x_msg_data, l_condition_id,
1710 l_date_str,
1711 l_table_names(i),
1712 l_tbl_alias, 'N', 'BOTH', 'Y',
1713 l_condition_sql );
1714 EXCEPTION
1715 when others then
1716 FEM_ENGINES_PKG.Put_Message(
1717 p_app_name => 'FTP',
1718 p_msg_name => 'WCLAUSE EXCEPTION',
1719 p_token1 => 'TABLE_NAME',
1720 p_value1 => l_table_names(i),
1721 p_token2 => 'MSG_DATA',
1722 p_value2 => x_msg_data
1723 );
1724 END;
1725
1726 END IF;
1727
1728 IF l_condition_sql IS NOT NULL THEN
1729 --get the l_input_cnt
1730 l_select_stmt := 'select count(*) from '|| l_table_names(i) ||
1731 ' where (ledger_id = :1) and (cal_period_id = :2) and dataset_code in '||
1732 l_input_ds||' and '||l_condition_sql;
1733 ELSE
1734 l_select_stmt := 'select count(*) from '|| l_table_names(i) ||
1735 ' where (ledger_id = :1) and (cal_period_id = :2) and dataset_code in '||
1736 l_input_ds;
1737 END IF;
1738
1739 EXECUTE IMMEDIATE l_select_stmt INTO l_input_cnt USING p_ledger_id,p_cal_period_id;
1740
1741 --do the summation of input rows across all tables
1742 l_total_inp := l_total_inp + l_input_cnt;
1743
1744 --get the l_output_cnt
1745 l_select_stmt := 'select count(*) from '||l_table_names(i) ||
1746 ' where last_updated_by_object_id = :1 and last_updated_by_request_id = :2';
1747
1748 EXECUTE IMMEDIATE l_select_stmt INTO l_output_cnt USING p_object_id,p_request_id;
1749
1750 --Register updated columns
1751 IF l_table_names(i) <> 'FEM_BALANCES' THEN
1752 -- R12 Enh : Commenting out call to FTP_TP_PKG.GET_TP_OUT_COLS
1753 /* FTP_TP_PKG.GET_TP_OUT_COLS(
1754 obj_id => p_object_id,
1755 data_set_id => p_dataset_def_id,
1756 jobid => p_job_id,
1757 effective_date =>p_effective_date,
1758 TRATE_COL => l_col_names(1),
1759 MSPREAD_COL => l_col_names(2),
1760 OAS_COL =>l_col_names(3),
1761 SS_COL => l_col_names(4),
1762 LAST_OBJID_COL => l_col_names(5),
1763 LAST_REQID_COL => l_col_names(6)
1764 ); */
1765
1766 -- Get the transfer rate and calculate flag
1767 -- R12 Enh : Get the calc_mode_code,adj_calc_flg
1768 select trans_rate_propagate_flg,trans_rate_calc_flg,
1769 option_cost_propagate_flg,option_cost_calc_flg,calc_mode_code,adj_calc_flg
1770 into trate_prop_flg,trate_calc_flg,ocost_prop_flg,ocost_calc_flg,
1771 calcmode_flg,l_adj_calc_flag
1772 from ftp_tp_proc_stoch_params
1773 where object_definition_id = process_info.obj_id and job_id = p_job_id;
1774
1775 FOR j IN 1..g_alt_rate_obj.COUNT
1776 LOOP
1777 FEM_ENGINES_PKG.TECH_MESSAGE(
1778 p_severity => fnd_log.level_error,
1779 p_module => g_block || '.accessing g_alt_rate_obj',
1780 p_msg_text => 'g_alt_rate_obj(j).Account_Table_Name = ' || g_alt_rate_obj(j).Account_Table_Name
1781 );
1782
1783 if ( g_alt_rate_obj(j).Account_Table_Name = l_table_names(i) ) THEN
1784 if ( calcmode_flg = 0 ) THEN --STANDARD
1785 l_col_names(1) := g_alt_rate_obj(j).TRANSFER_RATE_COL_NAME;
1786 l_col_names(2) := g_alt_rate_obj(j).MATCHED_SPREAD_COL_NAME;
1787 l_col_names(3) := g_alt_rate_obj(j).HISTORIC_OPTION_COL_NAME;
1788 l_col_names(4) := g_alt_rate_obj(j).HISTORIC_STAT_SPREAD_COL_NAME;
1789 ELSE
1790 l_col_names(1) := g_alt_rate_obj(j).REMAINING_TERM_COL_NAME;
1791 l_col_names(2) := g_alt_rate_obj(j).MATCHED_SPREAD_COL_NAME;
1792 l_col_names(3) := g_alt_rate_obj(j).CURRENT_OPTION_COL_NAME;
1793 l_col_names(4) := g_alt_rate_obj(j).CURRENT_STAT_SPREAD_COL_NAME;
1794 end if;
1795 l_col_names(5) := g_alt_rate_obj(j).ADJ_RATE_COL_NAME;
1796 l_col_names(6) := g_alt_rate_obj(j).ADJ_AMOUNT_COL_NAME;
1797 l_col_names(7) := g_alt_rate_obj(j).ADJ_MKT_VALUE;
1798 end if;
1799 END LOOP;
1800
1801 l_col_names(8) := 'last_updated_by_object_id';
1802 l_col_names(9) := 'last_updated_by_request_id';
1803
1804 -- Null out the cols that are not required.
1805 IF ((trate_prop_flg IS NULL) OR (trate_prop_flg = 0)) AND
1806 ((trate_calc_flg IS NULL) OR (trate_calc_flg = 0))
1807 THEN
1808 l_col_names(1) := NULL;
1809 l_col_names(2) := NULL;
1810 END IF;
1811
1812 IF ((ocost_prop_flg IS NULL) OR (ocost_prop_flg = 0)) AND
1813 ((ocost_calc_flg IS NULL) OR (ocost_calc_flg = 0))
1814 THEN
1815 l_col_names(3) := NULL;
1816 l_col_names(4) := NULL;
1817 END IF;
1818
1819 IF ((l_adj_calc_flag IS NULL) OR (l_adj_calc_flag = 0))
1820 THEN
1821 l_col_names(5) := NULL;
1822 l_col_names(6) := NULL;
1823 l_col_names(7) := NULL;
1824 END IF;
1825
1826 -- Null the last cols
1827 l_col_names(8) := NULL;
1828 l_col_names(9) := NULL;
1829
1830 -- Set the statement type
1831 l_stmt_type := 'UPDATE';
1832 FOR j IN 1..9 LOOP
1833
1834 FEM_ENGINES_PKG.TECH_MESSAGE(
1835 p_severity => fnd_log.level_error,
1836 p_module => g_block || '.REGISTER_UPDATED_COLUMN',
1837 p_msg_text => 'before REGISTER_UPDATED_COLUMN j = ' || j ||' l_col_names(j)=' ||l_col_names(j)
1838 );
1839
1840 IF l_col_names(j) IS NOT NULL THEN
1841 --Register updated columns
1842 fem_pl_pkg.REGISTER_UPDATED_COLUMN(
1843 p_api_version => c_api_version,
1844 p_request_id => p_request_id,
1845 p_object_id => p_object_id,
1846 p_table_name => l_table_names(i),
1847 p_statement_type => l_stmt_type,
1848 p_column_name => UPPER(l_col_names(j)),
1849 p_user_id => p_user_id,
1850 p_last_update_login => p_last_update_login,
1851 x_msg_count => x_msg_count,
1852 x_msg_data => x_msg_data,
1853 x_return_status => x_return_status
1854 );
1855 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1856 RAISE FND_API.G_EXC_ERROR;
1857 END IF;
1858 END IF;--end of is col name null
1859 END LOOP;--end of column names loop
1860 ELSE
1861 l_b_data_location := (l_output_cnt > 0);
1862 -- Set the statement type
1863 l_stmt_type := c_stmt_type;
1864 END IF;--end of if not fem_balances
1865
1866 FEM_PL_PKG.update_num_of_output_rows(
1867 p_api_version => c_api_version,
1868 p_request_id => p_request_id,
1869 p_object_id => p_object_id,
1870 p_table_name => l_table_names(i),
1871 p_statement_type => l_stmt_type,
1872 p_num_of_output_rows => l_output_cnt,
1873 p_user_id => p_user_id,
1874 p_last_update_login => p_last_update_login,
1875 x_msg_count => x_msg_count,
1876 x_msg_data => x_msg_data,
1877 x_return_status => x_return_status
1878 );
1879
1880 FEM_ENGINES_PKG.TECH_MESSAGE(
1881 p_severity => fnd_log.level_error,
1882 p_module => g_block || '.debugg error',
1883 p_msg_text => 'Error Mesg 1'
1884 );
1885
1886 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1887 RAISE FND_API.G_EXC_ERROR;
1888 END IF;
1889
1890 l_total_out := l_total_out + l_output_cnt;
1891
1892 END LOOP;
1893
1894 -- Register teh FTP_TP_PP_NODE_MAP and FTP_TP_PP_NODE_HEADER info
1895 select count(*) into l_output_cnt from FTP_TP_PP_NODE_HEADER
1896 where created_by_object_id = OBJECT_ID and created_by_request_id = REQUEST_ID;
1897
1898 FEM_ENGINES_PKG.TECH_MESSAGE(
1899 p_severity => fnd_log.level_error,
1900 p_module => g_block || '.debugg error',
1901 p_msg_text => 'Error Mesg 2'
1902 );
1903
1904 FEM_PL_PKG.update_num_of_output_rows(
1905 p_api_version => c_api_version,
1906 p_request_id => p_request_id,
1907 p_object_id => p_object_id,
1908 p_table_name => 'FTP_TP_PP_NODE_HEADER',
1909 p_statement_type => c_stmt_type,
1910 p_num_of_output_rows => l_output_cnt,
1911 p_user_id => p_user_id,
1912 p_last_update_login => p_last_update_login,
1913 x_msg_count => x_msg_count,
1914 x_msg_data => x_msg_data,
1915 x_return_status => x_return_status
1916 );
1917
1918 FEM_ENGINES_PKG.TECH_MESSAGE(
1919 p_severity => fnd_log.level_error,
1920 p_module => g_block || '.debugg error',
1921 p_msg_text => 'Error Mesg 3'
1922 );
1923
1924 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1925 RAISE FND_API.G_EXC_ERROR;
1926 END IF;
1927
1928 select count(*) into l_output_cnt from FTP_TP_PP_NODE_MAP
1929 where created_by_object_id = OBJECT_ID and created_by_request_id = REQUEST_ID;
1930
1931 FEM_ENGINES_PKG.TECH_MESSAGE(
1932 p_severity => fnd_log.level_error,
1933 p_module => g_block || '.debugg error',
1934 p_msg_text => 'Error Mesg 4'
1935 );
1936
1937 FEM_PL_PKG.update_num_of_output_rows(
1938 p_api_version => c_api_version,
1939 p_request_id => p_request_id,
1940 p_object_id => p_object_id,
1941 p_table_name => 'FTP_TP_PP_NODE_MAP',
1942 p_statement_type => c_stmt_type,
1943 p_num_of_output_rows => l_output_cnt,
1944 p_user_id => p_user_id,
1945 p_last_update_login => p_last_update_login,
1946 x_msg_count => x_msg_count,
1947 x_msg_data => x_msg_data,
1948 x_return_status => x_return_status
1949 );
1950
1951 FEM_ENGINES_PKG.TECH_MESSAGE(
1952 p_severity => fnd_log.level_error,
1953 p_module => g_block || '.debugg error',
1954 p_msg_text => 'Error Mesg 5'
1955 );
1956
1957 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1958 RAISE FND_API.G_EXC_ERROR;
1959 END IF;
1960
1961 --Update the number of input rows
1962 FEM_PL_PKG.update_num_of_input_rows (
1963 p_api_version => c_api_version,
1964 p_commit => FND_API.G_FALSE,
1965 p_request_id => p_request_id,
1966 p_object_id => p_object_id,
1967 p_num_of_input_rows => l_total_inp,
1968 p_user_id => p_user_id,
1969 p_last_update_login => p_last_update_login,
1970 x_msg_count => x_msg_count,
1971 x_msg_data => x_msg_data,
1972 x_return_status => x_return_status
1973 );
1974
1975 FEM_ENGINES_PKG.TECH_MESSAGE(
1976 p_severity => fnd_log.level_error,
1977 p_module => g_block || '.debugg error',
1978 p_msg_text => 'Error Mesg 6'
1979 );
1980
1981 -- Update the data locations table
1982 IF (l_b_data_location)
1983 THEN
1984 -- Get Source System Code
1985 EXECUTE IMMEDIATE 'select source_system_code from fem_source_systems_vl
1986 where source_system_display_code = :1' into l_ss_code USING c_app;
1987 -- Get Output dataset code
1988 select output_dataset_code into l_output_ds from fem_ds_input_output_defs
1989 where dataset_io_obj_def_id = p_dataset_def_id;
1990
1991 FEM_ENGINES_PKG.TECH_MESSAGE(
1992 p_severity => fnd_log.level_error,
1993 p_module => g_block || '.debugg error',
1994 p_msg_text => 'Error Mesg 7'
1995 );
1996
1997 -- Register the data location
1998 FEM_DIMENSION_UTIL_PKG.Register_Data_Location (
1999 p_request_id => p_request_id,
2000 p_object_id => p_object_id,
2001 p_table_name => 'FEM_BALANCES',
2002 p_ledger_id => p_ledger_id,
2003 p_cal_per_id => p_cal_period_id,
2004 p_dataset_cd => l_output_ds,
2005 p_source_cd => l_ss_code,
2006 p_load_status => NULL,
2007 p_avg_bal_flag => NULL,
2008 p_trans_curr => NULL
2009 );
2010
2011 FEM_ENGINES_PKG.TECH_MESSAGE(
2012 p_severity => fnd_log.level_error,
2013 p_module => g_block || '.debugg error',
2014 p_msg_text => 'Error Mesg 8'
2015 );
2016
2017 END IF;
2018
2019 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2020 RAISE FND_API.G_EXC_ERROR;
2021 END IF;
2022
2023 IF l_total_inp = 0 THEN
2024 FEM_ENGINES_PKG.Put_Message(
2025 p_app_name => 'FTP',
2026 p_msg_name => 'FTP_ZERO_REC_ERR'
2027 );
2028 END IF;
2029
2030 IF l_total_out = 0 THEN
2031 l_exec_status_code := c_prg_err_rerun;
2032 f_set_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',null);
2033 ELSE
2034 l_exec_status_code := c_prg_success;
2035 f_set_status := FND_CONCURRENT.SET_COMPLETION_STATUS('SUCCESS',null);
2036 END IF;
2037
2038 FEM_ENGINES_PKG.TECH_MESSAGE(
2039 p_severity => fnd_log.level_error,
2040 p_module => g_block || '.debugg error',
2041 p_msg_text => 'Error Mesg 9'
2042 );
2043
2044 fem_pl_pkg.UPDATE_OBJ_EXEC_STATUS(
2045 p_api_version => c_api_version,
2046 p_request_id => p_request_id,
2047 p_object_id => p_object_id,
2048 p_exec_status_code => l_exec_status_code,
2049 p_user_id => p_user_id,
2050 p_last_update_login => p_last_update_login,
2051 x_msg_count => x_msg_count,
2052 x_msg_data => x_msg_data,
2053 x_return_status => x_return_status
2054 );
2055
2056 FEM_ENGINES_PKG.TECH_MESSAGE(
2057 p_severity => fnd_log.level_error,
2058 p_module => g_block || '.debugg error',
2059 p_msg_text => 'Error Mesg 10'
2060 );
2061
2062 --update status of the request
2063 FEM_PL_PKG.update_request_status(
2064 p_api_version => c_api_version,
2065 p_request_id => p_request_id,
2066 p_exec_status_code => l_exec_status_code,
2067 p_user_id => p_user_id,
2068 p_last_update_login => p_last_update_login,
2069 x_msg_count => x_msg_count,
2070 x_msg_data => x_msg_data,
2071 x_return_status => x_return_status
2072 );
2073
2074 FEM_ENGINES_PKG.TECH_MESSAGE(
2075 p_severity => fnd_log.level_error,
2076 p_module => g_block || '.debugg error',
2077 p_msg_text => 'Error Mesg 11'
2078 );
2079
2080 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2081 RAISE FND_API.G_EXC_ERROR;
2082 END IF;
2083
2084 l_table_names.DELETE;
2085 FND_MSG_PUB.Count_And_Get
2086 (p_encoded => 'F',
2087 p_count => x_msg_count,
2088 p_data => x_msg_data);
2089 EXCEPTION
2090 WHEN FND_API.G_EXC_ERROR THEN
2091 FND_MSG_PUB.Count_And_Get
2092 (p_encoded => 'F',
2093 p_count => x_msg_count,
2094 p_data => x_msg_data);
2095 x_return_status := FND_API.G_RET_STS_ERROR;
2096
2097 FEM_ENGINES_PKG.TECH_MESSAGE(
2098 p_severity => fnd_log.level_error,
2099 p_module => g_block || 'FND_API.G_EXC_ERROR',
2100 p_msg_text => 'sqlerrm =' || sqlerrm
2101 );
2102
2103 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2105 fem_pl_pkg.UPDATE_OBJ_EXEC_STATUS(
2106 p_api_version => c_api_version,
2107 p_request_id => p_request_id,
2108 p_object_id => p_object_id,
2109 p_exec_status_code => c_prg_err_undo,
2110 p_user_id => p_user_id,
2111 p_last_update_login => p_last_update_login,
2112 x_msg_count => x_msg_count,
2113 x_msg_data => x_msg_data,
2114 x_return_status => x_return_status
2115
2116 );
2117 f_set_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',null);
2118
2119 FEM_ENGINES_PKG.TECH_MESSAGE(
2120 p_severity => fnd_log.level_error,
2121 p_module => g_block || 'FND_API.G_EXC_UNEXPECTED_ERROR',
2122 p_msg_text => 'sqlerrm =' || sqlerrm
2123 );
2124
2125 --update status of the request
2126 FEM_PL_PKG.update_request_status(
2127 p_api_version => c_api_version,
2128 p_request_id => p_request_id,
2129 p_exec_status_code => c_prg_err_undo,
2130 p_user_id => p_user_id,
2131 p_last_update_login => p_last_update_login,
2132 x_msg_count => x_msg_count,
2133 x_msg_data => x_msg_data,
2134 x_return_status => x_return_status
2135 );
2136 FND_MSG_PUB.Count_And_Get
2137 (p_encoded => 'F',
2138 p_count => x_msg_count,
2139 p_data => x_msg_data);
2140
2141 WHEN OTHERS THEN
2142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2143
2144 FEM_ENGINES_PKG.TECH_MESSAGE(
2145 p_severity => fnd_log.level_error,
2146 p_module => g_block || 'OTHERS',
2147 p_msg_text => 'sqlerrm =' || sqlerrm
2148 );
2149
2150 fem_pl_pkg.UPDATE_OBJ_EXEC_STATUS(
2151 p_api_version => c_api_version,
2152 p_request_id => p_request_id,
2153 p_object_id => p_object_id,
2154 p_exec_status_code
2155 => c_prg_err_undo,
2156 p_user_id => p_user_id,
2157 p_last_update_login => p_last_update_login,
2158 x_msg_count => x_msg_count,
2159 x_msg_data => x_msg_data,
2160 x_return_status => x_return_status
2161
2162 );
2163 f_set_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',null);
2164
2165 --update status of the request
2166 FEM_PL_PKG.update_request_status(
2167 p_api_version => c_api_version,
2168 p_request_id => p_request_id,
2169 p_exec_status_code => c_prg_err_undo,
2170 p_user_id => p_user_id,
2171 p_last_update_login => p_last_update_login,
2172 x_msg_count => x_msg_count,
2173 x_msg_data => x_msg_data,
2174 x_return_status => x_return_status
2175 );
2176 FND_MSG_PUB.Count_And_Get
2177 (p_encoded => 'F',
2178 p_count => x_msg_count,
2179 p_data => x_msg_data);
2180
2181
2182
2183 END STOP_PROCESS_LOCKS;
2184
2185 /***************************************************************************
2186 Desc : Procedure to initiate ledger migration or calculation process.
2187 Pgmr : Raghuram K Nanda
2188 Date : 16-Aug-2005
2189 **************************************************************************/
2190 PROCEDURE LEDGER_PROCESSING(
2191 p_request_id IN NUMBER,
2192 p_object_id IN NUMBER,
2193 p_cal_period_id IN NUMBER,
2194 p_ledger_id IN NUMBER,
2195 p_dataset_def_id IN NUMBER,
2196 p_job_id IN NUMBER,
2197 p_condition_id IN NUMBER,
2198 p_effective_date IN DATE,
2199 p_user_id IN NUMBER,
2200 p_last_update_login IN NUMBER,
2201 x_exec_lock_exists OUT NOCOPY VARCHAR2,
2202 x_return_status OUT NOCOPY VARCHAR2,
2203 x_msg_count OUT NOCOPY NUMBER,
2204 x_msg_data OUT NOCOPY VARCHAR2
2205 )
2206 IS
2207
2208 TYPE t_obj_id_tbl IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
2209 TYPE cv_curs IS REF CURSOR;
2210
2211 l_output_ds NUMBER;
2212 process_info obj_info_type;
2213 l_exception_code VARCHAR2(80);
2214 l_msg VARCHAR2(4000);
2215 l_tbl_alias VARCHAR2(1);
2216 l_condition_sql VARCHAR2(4000);
2217 --l_migrate_flg BOOLEAN;
2218 --l_trate_migr VARCHAR2(1);
2219 --l_ocost_migr VARCHAR2(1);
2220 l_uow_cursor cv_curs;
2221 l_sql_stmt VARCHAR2(4000);
2222 l_date_str VARCHAR2(26);
2223 l_created_by_obj_tbl t_obj_id_tbl;
2224 l_created_by_req_tbl t_obj_id_tbl;
2225
2226 -- Praveen Attaluri (Bug Fix)
2227 -- cursor that retrieves the tables that should be used as source
2228 CURSOR l_cur_tables IS select table_name from ftp_tp_proc_tabls_params
2229 where object_definition_id = process_info.obj_id and job_id = p_job_id;
2230
2231 TYPE varchar_std_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
2232 l_table_names varchar_std_type;
2233 -- Praveen Attaluri (Bug Fix)
2234
2235 BEGIN
2236
2237 -- initialize our status to 'we are good!'
2238 x_return_status := FND_API.G_RET_STS_SUCCESS;
2239
2240 -- Get the object definition id of the process
2241 get_obj_def(p_object_id, p_effective_date, process_info);
2242
2243 --Get the output dataset code for the given IODD
2244 select output_dataset_code into l_output_ds from fem_ds_input_output_defs
2245 where dataset_io_obj_def_id = p_dataset_def_id;
2246
2247 x_exec_lock_exists := 'F';
2248
2249 /*--Get the migrate option
2250 select trans_rate_migrate_flg,option_cost_migrate_flg into l_trate_migr,l_ocost_migr
2251 from ftp_tp_proc_stoch_params where object_definition_id = process_info.obj_id
2252 and job_id = p_job_id;
2253
2254 IF (l_trate_migr = 1 OR l_ocost_migr = 1) THEN
2255 l_migrate_flg := TRUE;
2256 ELSE
2257 l_migrate_flg := FALSE;
2258 END IF;*/
2259 l_date_str := FND_DATE.date_to_canonical(p_effective_date);
2260
2261
2262 --Check to see if chaining exists before we move any further.
2263 --IF l_migrate_flg
2264 --THEN
2265 /*chaining_exists(
2266 p_request_id => p_request_id,
2267 p_object_id => p_object_id,
2268 p_cal_period_id => p_cal_period_id,
2269 p_ledger_id => p_ledger_id,
2270 p_dataset_def_id => p_dataset_def_id,
2271 p_condition_str => l_condition_sql,
2272 p_effective_date => p_effective_date,
2273 p_table_name => LEDGER_NAME,
2274 x_exec_lock_exists => x_exec_lock_exists
2275 );
2276
2277
2278 IF x_exec_lock_exists = 'T' THEN
2279 RETURN;
2280 END IF;*/
2281 --END IF;
2282 --Chaining exists is now handled by following FEM
2283 --x_exec_lock_exists is no longer required..
2284 FEM_UD_PKG.Delete_Balances (
2285 p_api_version => c_api_version,
2286 p_init_msg_list => FND_API.G_TRUE,
2287 p_commit => FND_API.G_FALSE,
2288 p_encoded => FND_API.G_FALSE,
2289 x_return_status => x_return_status,
2290 x_msg_count => x_msg_count,
2291 x_msg_data => x_msg_data,
2292 p_current_request_id => p_request_id,
2293 p_object_id => p_object_id,
2294 p_cal_period_id => p_cal_period_id,
2295 p_ledger_id => p_ledger_id,
2296 p_dataset_code => l_output_ds
2297 );
2298
2299 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2300 RAISE FND_API.G_EXC_ERROR;
2301 END IF;
2302
2303 --- Praveen Attaluri (Bug Fix)
2304 -- Get the tables that should be processed. Chaining must be implemented
2305 -- even if only calculation is selected on FEM_BALANCES.
2306
2307 -- Read the rule to retieve the names of the tables to process
2308 OPEN l_cur_tables;
2309 FETCH l_cur_tables BULK COLLECT INTO l_table_names;
2310 CLOSE l_cur_tables;
2311
2312 -- Go through all the tables
2313 FOR i IN 1..l_table_names.COUNT
2314 LOOP
2315
2316 IF (p_condition_id IS NOT NULL AND p_condition_id <> 0)
2317 THEN
2318 BEGIN
2319 FEM_CONDITIONS_API.GENERATE_CONDITION_PREDICATE(
2320 x_msg_count, x_msg_data, p_condition_id,
2321 l_date_str,
2322 l_table_names(i),
2323 l_tbl_alias, 'N', 'BOTH', 'Y',
2324 l_condition_sql );
2325 EXCEPTION
2326 when others then
2327 FEM_ENGINES_PKG.Put_Message(
2328 p_app_name => 'FTP',
2329 p_msg_name => 'WCLAUSE EXCEPTION',
2330 p_token1 => 'TABLE_NAME',
2331 p_value1 => l_table_names(i),
2332 p_token2 => 'MSG_DATA',
2333 p_value2 => x_msg_data
2334 );
2335 END;
2336 END IF;
2337
2338 -- Add the condition if necessary. We have to add a separate condition to
2339 -- not include the previous runs of the calculation. For FEM_BALANCES we only
2340 -- have to include the F140 so that we don't include the calculated results
2341 -- of the another rule using the same cal period and ledger_id.
2342 l_sql_stmt := 'select distinct CREATED_BY_OBJECT_ID,CREATED_BY_REQUEST_ID
2343 from ' || l_table_names(i) ||
2344 ' where ledger_id=:1 and cal_period_id = :2 and created_by_object_id <> :3';
2345 IF l_condition_sql IS NOT NULL THEN
2346 l_sql_stmt := l_sql_stmt || ' and '||l_condition_sql;
2347 END IF;
2348
2349 IF (l_table_names(i) = LEDGER_NAME)
2350 THEN
2351 l_sql_stmt := l_sql_stmt || ' and financial_elem_id=140';
2352 END IF;
2353
2354 OPEN l_uow_cursor FOR l_sql_stmt USING p_ledger_id,p_cal_period_id,p_object_id;
2355 FETCH l_uow_cursor BULK COLLECT INTO l_created_by_obj_tbl,l_created_by_req_tbl;
2356 CLOSE l_uow_cursor;
2357
2358 FOR i in 1 .. l_created_by_obj_tbl.COUNT LOOP
2359 -- Call the FEM_PL_PKG.Register_Chain API procedure to register
2360 -- the specified chain.
2361 FEM_PL_PKG.Register_Chain (
2362 p_api_version => c_api_version,
2363 p_commit => FND_API.G_FALSE,
2364 p_request_id => p_request_id,
2365 p_object_id => p_object_id,
2366 p_source_created_by_request_id => l_created_by_req_tbl(i),
2367 p_source_created_by_object_id => l_created_by_obj_tbl(i),
2368 p_user_id => p_user_id,
2369 p_last_update_login => p_last_update_login,
2370 x_msg_count => x_msg_count,
2371 x_msg_data => x_msg_data,
2372 x_return_status => x_return_status
2373 );
2374
2375 END LOOP;
2376
2377
2378 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2379 RAISE FND_API.G_EXC_ERROR;
2380 END IF;
2381
2382 END LOOP;
2383
2384 --delete the previous run data
2385 --IF l_migrate_flg THEN
2386 /*DELETE from fem_balances where created_by_object_id = p_object_id AND
2387 (DATASET_CODE = l_output_ds) AND (LEDGER_ID = p_ledger_id) AND
2388 (CAL_PERIOD_ID = p_cal_period_id);*/
2389 --END IF;
2390 FND_MSG_PUB.Count_And_Get
2391 (p_encoded => 'F',
2392 p_count => x_msg_count,
2393 p_data => x_msg_data);
2394 EXCEPTION
2395 WHEN FND_API.G_EXC_ERROR THEN
2396 FND_MSG_PUB.Count_And_Get
2397 (p_encoded => 'F',
2398 p_count => x_msg_count,
2399 p_data => x_msg_data);
2400 x_return_status := FND_API.G_RET_STS_ERROR;
2401
2402 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2403 FND_MSG_PUB.Count_And_Get
2404 (p_encoded => 'F',
2405 p_count => x_msg_count,
2406 p_data => x_msg_data);
2407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2408
2409 WHEN OTHERS THEN
2410 FND_MSG_PUB.Count_And_Get
2411 (p_encoded => 'F',
2412 p_count => x_msg_count,
2413 p_data => x_msg_data);
2414
2415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2416
2417 END LEDGER_PROCESSING;
2418
2419 /***************************************************************************
2420 Desc : Procedure to check if any chaining dependency exists on the data
2421 that rule is going to process.
2422 Pgmr : Raghuram K Nanda
2423 Date : 16-Aug-2005
2424 **************************************************************************/
2425 PROCEDURE CHAINING_EXISTS(
2426 p_request_id IN NUMBER,
2427 p_object_id IN NUMBER,
2428 p_cal_period_id IN NUMBER,
2429 p_ledger_id IN NUMBER,
2430 p_dataset_def_id IN NUMBER,
2431 p_condition_str IN VARCHAR2,
2432 p_effective_date IN DATE,
2433 p_table_name IN VARCHAR2,
2434 x_exec_lock_exists OUT NOCOPY VARCHAR2
2435 )
2436 IS
2437
2438 l_chain_count PLS_INTEGER;
2439
2440 BEGIN
2441
2442 -- Check if the object id exists in pl chains table
2443 select count(*) into l_chain_count from fem_pl_chains
2444 where source_created_by_object_id = p_object_id;
2445
2446 IF (l_chain_count > 0) THEN
2447 x_exec_lock_exists := 'T';
2448 RETURN;
2449 ELSE
2450 x_exec_lock_exists := 'F';
2451 END IF;
2452
2453 EXCEPTION
2454 WHEN others THEN
2455 x_exec_lock_exists := 'T';
2456 return;
2457
2458 END CHAINING_EXISTS;
2459
2460 /***************************************************************************
2461 Desc : Procedure to return the valid list of table names present in the
2462 FTP_RATE_OUTPUT_MAPPING_RULE.
2463 Pgmr : Tina Dasgupta
2464 Date : 25-Oct-2006
2465 **************************************************************************/
2466 procedure GET_VALID_TABLE_LIST(
2467 obj_id in number,
2468 jobid in number,
2469 effective_date in date,
2470 new_valid_table_list out nocopy varchar2,
2471 LAST_OBJID_COL out nocopy varchar2,
2472 LAST_REQID_COL out nocopy varchar2,
2473 x_return_status OUT NOCOPY VARCHAR2,
2474 x_msg_count OUT NOCOPY NUMBER,
2475 x_msg_data OUT NOCOPY VARCHAR2
2476 )
2477 is
2478 process_info OBJ_INFO_TYPE;
2479
2480 indx NUMBER;
2481 rate_output_rule_obj_id FTP_TP_PROCESS_RULE.ALT_RATE_OP_OBJECT_ID%TYPE;
2482 adj_object_id FTP_TP_PROCESS_RULE.adjustment_object_id%TYPE;
2483 adj_type_code ftp_adjustment_rule.adjustment_type_code%TYPE;
2484 ecoloss_meth_count NUMBER;
2485 TYPE varchar_std_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
2486 rate_map_info OBJ_INFO_TYPE;
2487 adj_rule_info OBJ_INFO_TYPE;
2488 valid_flg BOOLEAN;
2489 l_table_names varchar_std_type;
2490 cnt NUMBER;
2491
2492 -- cursor that retrieves the tables that should be used as source
2493 CURSOR l_cur_tables IS select table_name from ftp_tp_proc_tabls_params
2494 where object_definition_id = process_info.obj_id and job_id = jobid;
2495
2496 begin
2497
2498 -- initialize our status to 'we are good!'
2499 x_return_status := FND_API.G_RET_STS_SUCCESS;
2500 FND_MSG_PUB.Delete_Msg();
2501 adj_type_code := '';
2502
2503 get_obj_def(obj_id, effective_date, process_info);
2504
2505 -- From TP Process Rule Info to chk if new Alternate Rate Output Mapping
2506 -- Rule,Adjustment Rule exists or not.
2507 select ALT_RATE_OP_OBJECT_ID,adjustment_object_id
2508 into rate_output_rule_obj_id,adj_object_id
2509 from ftp_tp_process_rule
2510 where object_definition_id = process_info.obj_id ;
2511
2512 IF ( adj_object_id IS NOT NULL ) THEN
2513 -- For rate_output_rule get detail info
2514 get_obj_def(adj_object_id, effective_date, adj_rule_info);
2515
2516 select distinct(adjustment_type_code) into adj_type_code
2517 from ftp_adjustment_rule
2518 where object_definition_id = adj_rule_info.obj_id; --11762;
2519 END IF;
2520
2521 IF ( rate_output_rule_obj_id IS NULL ) THEN
2522 valid_table_list := '';
2523 LAST_OBJID_COL := 'last_updated_by_object_id';
2524 LAST_REQID_COL := 'last_updated_by_request_id';
2525 FND_MSG_PUB.Count_And_Get
2526 (p_encoded => 'F',
2527 p_count => x_msg_count,
2528 p_data => x_msg_data);
2529
2530 -- Load into global varb standard output colm names
2531 g_alt_rate_obj := t_alt_rate_obj_list();
2532 indx := 1;
2533
2534 -- Read the rule to retieve the names of the tables to process
2535 OPEN l_cur_tables;
2536 FETCH l_cur_tables BULK COLLECT INTO l_table_names;
2537 CLOSE l_cur_tables;
2538
2539 FOR i IN 1..l_table_names.COUNT
2540 LOOP
2541 g_alt_rate_obj.extend();
2542 g_alt_rate_obj(i).Account_Table_Name := l_table_names(i);
2543 g_alt_rate_obj(i).TRANSFER_RATE_COL_NAME := 'transfer_rate';
2544 g_alt_rate_obj(i).MATCHED_SPREAD_COL_NAME := 'matched_spread';
2545 g_alt_rate_obj(i).REMAINING_TERM_COL_NAME := 'tran_rate_rem_term';
2546 g_alt_rate_obj(i).HISTORIC_OPTION_COL_NAME := 'historic_oas';
2547 g_alt_rate_obj(i).HISTORIC_STAT_SPREAD_COL_NAME := 'historic_static_spread';
2548 g_alt_rate_obj(i).CURRENT_STAT_SPREAD_COL_NAME := 'cur_static_spread';
2549 g_alt_rate_obj(i).CURRENT_OPTION_COL_NAME := 'cur_oas';
2550
2551 -- Based on the adjustment_type_code initialize ADJ_RATE_COL_NAME and
2552 -- ADJ_AMOUNT_COL_NAME
2553 IF ( adj_type_code = '1' ) THEN
2554 g_alt_rate_obj(i).ADJ_RATE_COL_NAME := 'liquidity_premium_rate';
2555 g_alt_rate_obj(i).ADJ_AMOUNT_COL_NAME := 'liquidity_premium_amt';
2556 ELSIF ( adj_type_code = '2' ) THEN
2557 g_alt_rate_obj(i).ADJ_RATE_COL_NAME := 'basis_risk_cost_rate';
2558 g_alt_rate_obj(i).ADJ_AMOUNT_COL_NAME := 'basis_risk_cost_amt';
2559 ELSIF ( adj_type_code = '3' ) THEN
2560 g_alt_rate_obj(i).ADJ_RATE_COL_NAME := 'pricing_incentive_rate';
2561 g_alt_rate_obj(i).ADJ_AMOUNT_COL_NAME := 'pricing_incentive_amt';
2562 ELSIF ( adj_type_code = '4' ) THEN
2563 g_alt_rate_obj(i).ADJ_RATE_COL_NAME := 'other_adjustments_rate';
2564 g_alt_rate_obj(i).ADJ_AMOUNT_COL_NAME := 'other_adjustments_amt';
2565 ELSIF ( adj_type_code IS NULL ) THEN
2566 g_alt_rate_obj(i).ADJ_RATE_COL_NAME := NULL;
2567 g_alt_rate_obj(i).ADJ_AMOUNT_COL_NAME := NULL;
2568 END IF;
2569
2570 -- If table has classification_code as 'FTP_BREAK_FUND' populate
2571 -- BREAK_FUNDING_RATE,BREAK_FUNDING_AMT,BREAK_FUNDING_MV
2572 select count(*) into cnt from FEM_TABLE_CLASS_ASSIGNMT
2573 where table_name = l_table_names(i) --'FEM_BREAK_FUNDING'
2574 and table_classification_code ='FTP_BREAK_FUND';
2575
2576 if ( cnt > 0 ) THEN
2577 g_alt_rate_obj(i).ADJ_RATE_COL_NAME := 'BREAK_FUNDING_RATE';
2578 g_alt_rate_obj(i).ADJ_AMOUNT_COL_NAME := 'BREAK_FUNDING_AMT';
2579
2580 --Check for Eco Loss method to register MV Column
2581 select count(calc_method_code) into ecoloss_meth_count
2582 from ftp_adjustment_rule where
2583 object_definition_id = adj_rule_info.obj_id and
2584 calc_method_code = c_ecoloss_method;
2585
2586 if(ecoloss_meth_count > 0) THEN
2587 g_alt_rate_obj(i).ADJ_MKT_VALUE := 'BREAK_FUNDING_MV';
2588 else
2589 g_alt_rate_obj(i).ADJ_MKT_VALUE := NULL;
2590 end if;
2591
2592 else
2593 g_alt_rate_obj(i).ADJ_RATE_COL_NAME := NULL;
2594 g_alt_rate_obj(i).ADJ_AMOUNT_COL_NAME := NULL;
2595 g_alt_rate_obj(i).ADJ_MKT_VALUE := NULL;
2596 End If;
2597 END LOOP;
2598 return;
2599 END IF;
2600
2601 -- Verify the exception condition for Valid Rule Version
2602 begin
2603 -- For rate_output_rule get detail info
2604 get_obj_def(rate_output_rule_obj_id, effective_date, rate_map_info);
2605 exception
2606 when NO_DATA_FOUND then
2607 FEM_ENGINES_PKG.Put_Message(
2608 p_app_name => 'FTP',
2609 p_msg_name => 'RATE_MAP_VERSN_EXCEPTION',
2610 p_token1 => 'OBJ_ID',
2611 p_value1 => rate_output_rule_obj_id,
2612 p_token2 => 'EFFECTIVE_DATE',
2613 p_value2 => effective_date
2614 );
2615 end;
2616
2617 IF (g_alt_rate_obj IS NULL) OR (NOT g_alt_rate_obj_exists) THEN
2618 --dbms_output.put_line('IF (g_alt_rate_obj IS NULL) OR (NOT g_alt_rate_obj_exists) THEN');
2619 g_alt_rate_obj := t_alt_rate_obj_list();
2620 valid_table_list := '';
2621 indx := 1;
2622
2623 FOR ids IN (SELECT
2624 i.table_name,
2625 max (DECODE (TRANSFER_RATE_COL_NAME, column_name, TRANSFER_RATE_COL_NAME,NULL,NULL, -1)) as trate,
2626 max (DECODE (MATCHED_SPREAD_COL_NAME, column_name, MATCHED_SPREAD_COL_NAME, NULL,NULL, -1)) as match_spread,
2627 max (DECODE (REMAINING_TERM_COL_NAME, column_name, REMAINING_TERM_COL_NAME, NULL,NULL, -1)) as rem_term,
2628 max (DECODE (HIST_OAS_COL_NAME, column_name, HIST_OAS_COL_NAME, NULL,NULL, -1)) as hist_option,
2629 max (DECODE (ADJUSTMENT_SPRD_COL_NAME, column_name, ADJUSTMENT_SPRD_COL_NAME, NULL,NULL, -1)) as adj_rate,
2630 max(DECODE(HIST_STAT_SPREAD_COL_NAME, column_name, HIST_STAT_SPREAD_COL_NAME, NULL,NULL, -1)) as hist_stat_spr,
2631 max(DECODE(CUR_STAT_SPREAD_COL_NAME, column_name, CUR_STAT_SPREAD_COL_NAME, NULL,NULL, -1)) as cur_stat_spr,
2632 max(DECODE(ADJUSTMENT_AMOUNT_COL_NAME, column_name, ADJUSTMENT_AMOUNT_COL_NAME, NULL,NULL, -1)) as adj_amt,
2633 max(DECODE(CUR_OAS_COL_NAME, column_name, CUR_OAS_COL_NAME, NULL,NULL, -1)) as cur_option
2634 FROM fem_tab_columns_v i, FTP_RATE_OUTPUT_MAPPING_RULE j, ftp_tp_proc_tabls_params k
2635 WHERE i.table_name = j. FTP_ACCOUNT_TABLE_NAME
2636 AND j. FTP_ACCOUNT_TABLE_NAME = k.table_name
2637 AND job_id = jobid
2638 AND k.object_definition_id = process_info.obj_id
2639 AND j.object_definition_id = rate_map_info.obj_id
2640 GROUP BY i.table_name) LOOP
2641
2642 g_alt_rate_obj.extend();
2643
2644 g_alt_rate_obj(indx).obj_def_id := rate_map_info.obj_id;
2645 g_alt_rate_obj(indx).Account_Table_Name := ids.table_name;
2646
2647 valid_flg := true;
2648 VERIFY_VALID_COLUMN(rate_output_rule_obj_id,'TRANSFER_RATE_COL_NAME',ids.trate,ids.table_name,valid_flg);
2649 VERIFY_VALID_COLUMN(rate_output_rule_obj_id,'MATCHED_SPREAD_COL_NAME',ids.match_spread,ids.table_name,valid_flg);
2650 VERIFY_VALID_COLUMN(rate_output_rule_obj_id,'REMAINING_TERM_COL_NAME',ids.rem_term,ids.table_name,valid_flg);
2651 VERIFY_VALID_COLUMN(rate_output_rule_obj_id,'HIST_OAS_COL_NAME',ids.hist_option,ids.table_name,valid_flg);
2652 VERIFY_VALID_COLUMN(rate_output_rule_obj_id,'ADJUSTMENT_SPRD_COL_NAME',ids.adj_rate,ids.table_name,valid_flg);
2653 VERIFY_VALID_COLUMN(rate_output_rule_obj_id,'HIST_STAT_SPREAD_COL_NAME',ids.hist_stat_spr,ids.table_name,valid_flg);
2654 VERIFY_VALID_COLUMN(rate_output_rule_obj_id,'CUR_STAT_SPREAD_COL_NAME',ids.cur_stat_spr,ids.table_name,valid_flg);
2655 VERIFY_VALID_COLUMN(rate_output_rule_obj_id,'ADJUSTMENT_AMOUNT_COL_NAME',ids.adj_amt,ids.table_name,valid_flg);
2656 VERIFY_VALID_COLUMN(rate_output_rule_obj_id,'CUR_OAS_COL_NAME',ids.cur_option,ids.table_name,valid_flg);
2657
2658 g_alt_rate_obj(indx).TRANSFER_RATE_COL_NAME := ids.trate;
2659 g_alt_rate_obj(indx).MATCHED_SPREAD_COL_NAME := ids.match_spread;
2660 g_alt_rate_obj(indx).REMAINING_TERM_COL_NAME := ids.rem_term;
2661 g_alt_rate_obj(indx).HISTORIC_OPTION_COL_NAME := ids.hist_option;
2662 g_alt_rate_obj(indx).ADJ_RATE_COL_NAME := ids.adj_rate;
2663 g_alt_rate_obj(indx).HISTORIC_STAT_SPREAD_COL_NAME := ids.hist_stat_spr;
2664 g_alt_rate_obj(indx).CURRENT_STAT_SPREAD_COL_NAME := ids.cur_stat_spr;
2665 g_alt_rate_obj(indx).ADJ_AMOUNT_COL_NAME := ids.adj_amt;
2666 g_alt_rate_obj(indx).CURRENT_OPTION_COL_NAME := ids.cur_option;
2667 g_alt_rate_obj(indx).ADJ_MKT_VALUE := NULL;
2668
2669 if (valid_flg) AND (indx=1) THEN
2670 valid_table_list := '''' || ids.table_name || '''' || ',';
2671 END IF;
2672
2673 if (valid_flg) AND (indx <> 1) THEN
2674 valid_table_list := valid_table_list || '''' || ids.table_name || '''' || ',';
2675 END IF;
2676
2677 indx := indx + 1;
2678
2679 END LOOP;
2680 END IF;
2681
2682 IF (NOT g_alt_rate_obj_exists) AND (indx > 1)
2683 THEN
2684 g_alt_rate_obj_exists := TRUE;
2685 END IF;
2686
2687 LAST_OBJID_COL := 'last_updated_by_object_id';
2688 LAST_REQID_COL := 'last_updated_by_request_id';
2689
2690 select RTRIM(valid_table_list,',') into new_valid_table_list from dual;
2691 valid_table_list := new_valid_table_list;
2692
2693 FND_MSG_PUB.Count_And_Get
2694 (p_encoded => 'F',
2695 p_count => x_msg_count,
2696 p_data => x_msg_data);
2697
2698
2699 END GET_VALID_TABLE_LIST;
2700
2701
2702 /***************************************************************************
2703 Desc : Procedure to verify if column is valid or NULL.
2704 Pgmr : Tina Dasgupta
2705 Date : 01-Sept-2006
2706 **************************************************************************/
2707 PROCEDURE VERIFY_VALID_COLUMN(
2708 rate_output_rule_obj_id IN NUMBER,
2709 p_col_name IN VARCHAR2,
2710 p_col_value IN VARCHAR2,
2711 p_table_name IN VARCHAR2,
2712 valid_flg IN OUT NOCOPY BOOLEAN
2713 )
2714 IS
2715 cnt NUMBER;
2716 BEGIN
2717
2718 select count(*) into cnt from fem_table_class_assignmt
2719 where table_name = p_table_name
2720 and table_classification_code = 'FTP_OPTION_COST';
2721
2722 if ( cnt <> 0 ) OR (( cnt = 0 ) and NOT(p_col_name = 'HIST_OAS_COL_NAME' or p_col_name = 'HIST_STAT_SPREAD_COL_NAME' or
2723 p_col_name = 'CUR_STAT_SPREAD_COL_NAME' or p_col_name = 'CUR_OAS_COL_NAME')) THEN
2724 if ( p_col_value IS NULL ) THEN
2725 valid_flg := false;
2726 FEM_ENGINES_PKG.Put_Message(
2727 p_app_name => 'FTP',
2728 p_msg_name => 'FTP_ALL_COLS_NOT_MAPPED',
2729 p_token1 => 'OBJ_ID',
2730 p_value1 => rate_output_rule_obj_id,
2731 p_token2 => 'TABLE_NAME',
2732 p_value2 => p_table_name
2733 );
2734 end if;
2735 end if;
2736
2737 if ( cnt <> 0 ) OR (( cnt = 0 ) and NOT(p_col_name = 'HIST_OAS_COL_NAME' or p_col_name = 'HIST_STAT_SPREAD_COL_NAME' or
2738 p_col_name = 'CUR_STAT_SPREAD_COL_NAME' or p_col_name = 'CUR_OAS_COL_NAME')) THEN
2739 if ( p_col_value = '-1') THEN
2740 valid_flg := false;
2741 FEM_ENGINES_PKG.Put_Message(
2742 p_app_name => 'FTP',
2743 p_msg_name => 'FTP_INVALID_COL',
2744 p_token1 => 'OBJ_ID',
2745 p_value1 => rate_output_rule_obj_id,
2746 p_token2 => 'TABLE_NAME',
2747 p_value2 => p_table_name,
2748 p_token3 => 'COLUMN_NAME',
2749 p_value3 => p_col_name
2750 );
2751 end if;
2752 end if;
2753
2754 END VERIFY_VALID_COLUMN;
2755
2756
2757 begin
2758 -- initialize dimension_id
2759 select dimension_id into main_dim_id from fem_dimensions_b
2760 where dimension_varchar_label = 'LINE_ITEM';
2761 select attribute_id into ACCT_TYPE_ATTR_ID from fem_dim_attributes_b
2762 where attribute_varchar_label = 'EXTENDED_ACCOUNT_TYPE'
2763 and dimension_id = main_dim_id;
2764 -- ext_accuont_types values
2765 select dimension_id into ext_account_type_id from fem_dimensions_b
2766 where dimension_varchar_label = EXT_ACCOUNT_TYPE_NAME;
2767 -- asset flag attribute id
2768 select attribute_id into asset_flag_id from fem_dim_attributes_b
2769 where dimension_id = ext_account_type_id
2770 and attribute_varchar_label = ASSET_FLAG_NAME;
2771 -- off_bal attribute
2772 select attribute_id into off_bal_flag_id from fem_dim_attributes_b
2773 where dimension_id = ext_account_type_id
2774 and attribute_varchar_label = OFF_BAL_NAME;
2775 end FTP_TP_PKG;