[Home] [Help]
1629: l_item_columns := 'sr_instance_id, organization_id';
1630: end if;
1631:
1632: l_sql :=
1633: ' insert into msc_hub_query ('||
1634: ' query_id, number3, number4, number5,'||
1635: ' created_by, creation_date, last_updated_by, last_update_date'||
1636: ' )'||
1637: ' select distinct'||
1667: if (nvl(p_plan_id, -1) <= 0) then
1668: return;
1669: end if;
1670:
1671: select msc_hub_query_s.nextval into l_qid_plan_item from dual;
1672: for r in c loop
1673: gather_items(l_qid_plan_item, r.fact_type, p_plan_id, p_plan_run_id);
1674: end loop;
1675: --msc_phub_util.log('msc_phub_pkg.build_items_from_apcc: l_qid_plan_item='||l_qid_plan_item);
1680: -- delete existing items
1681: delete from msc_apcc_item_d d
1682: where d.plan_id=p_plan_id
1683: and exists (
1684: select 1 from msc_hub_query q
1685: where q.query_id=l_qid_plan_item
1686: and q.number3=d.sr_instance_id
1687: and q.number4=d.organization_id
1688: and q.number5=d.inventory_item_id);
1792: (select distinct
1793: number3 sr_instance_id,
1794: number4 organization_id,
1795: number5 inventory_item_id
1796: from msc_hub_query where query_id=l_qid_plan_item
1797: and not (number3=-23453 and number4=-23453 and number5=-23453)) q
1798: where d.sr_instance_id=q.sr_instance_id
1799: and d.organization_id=q.organization_id
1800: and d.inventory_item_id=q.inventory_item_id