[Home] [Help]
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
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
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
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
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
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
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
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
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
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:
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: