[Home] [Help]
1657: END IF;
1658:
1659:
1660:
1661: /* can't do bulk insert into cst_sc_lists for PLS-00436 */
1662: /* insert item by item */
1663:
1664: lStmtNumber := 39.1;
1665:
1663:
1664: lStmtNumber := 39.1;
1665:
1666:
1667: insert into cst_sc_lists(
1668: rollup_id,
1669: inventory_item_id,
1670: organization_id,
1671: last_update_date,
1694: and msi.inventory_item_id = p_cfg_itm_tbl(i).cfg_item_id
1695: and msi.organization_id = p_cfg_itm_tbl(i).cfg_org_id
1696: and NOT EXISTS
1697: (select NULL
1698: from cst_sc_lists
1699: where rollup_id = lRollupId
1700: and inventory_item_id = msi.inventory_item_id
1701: and organization_id = mp.cost_organization_id) --3116778
1702: and NOT EXISTS /* check whether item has been rolled up */
2049:
2050: -- debug 3116778
2051: cursor cst_cur (xRollupId number) is
2052: select inventory_item_id,organization_id
2053: from cst_sc_lists
2054: where rollup_id =xRollupId;
2055: d_item_id number;
2056: d_org_id number;
2057:
2372:
2373:
2374:
2375: --
2376: -- insert into CST_SC_LISTS rows for all config items in all possible
2377: -- src orgs, where ->
2378: -- 1. cost has not been calculated yet (cost_rollup = Y)
2379: -- 2. costing_enabled_flag = Y
2380: -- 3. primary_cost_method = 1 (standard)
2379: -- 2. costing_enabled_flag = Y
2380: -- 3. primary_cost_method = 1 (standard)
2381: --
2382: IF PG_DEBUG <> 0 THEN
2383: oe_debug_pub.add('Cost_Rollup_ML: ' || 'inserting into cst_sc_lists', 2);
2384: END IF;
2385:
2386:
2387: /*
2385:
2386:
2387: /*
2388: BUG 3931290
2389: The statement insert into cst_sc_lists was using a union (organization_id, rcv_org_id)
2390: to determine organizations where cost rollup needs to be performed.
2391: The organization where cost rollup will be performed will now be determined using organization_id.
2392: This will eliminate the 2nd sql statement (rcv_org_id) in the union and there will be no need for a union.
2393: */
2392: This will eliminate the 2nd sql statement (rcv_org_id) in the union and there will be no need for a union.
2393: */
2394:
2395: lStmtNumber := 38;
2396: insert into cst_sc_lists(
2397: rollup_id,
2398: inventory_item_id,
2399: organization_id,
2400: last_update_date,
2443: )
2444: )
2445: and NOT EXISTS
2446: (select NULL
2447: from cst_sc_lists
2448: where rollup_id = lRollupId
2449: and inventory_item_id = bcso.config_item_id
2450: and organization_id = mp.cost_organization_id ) ;
2451:
2453:
2454: -- debug 3116778
2455: lCnt := sql%rowcount;
2456: IF PG_DEBUG <> 0 THEN
2457: oe_debug_pub.add('Cost_Rollup_ML: ' || 'rows inserted into cst_sc_lists::'||to_char(lCnt), 2);
2458: END IF;
2459: cst_sc_list_count := lCnt; -- Bug# 4867460
2460: open cst_cur ( lRollupId ) ;
2461:
2476:
2477:
2478: lCnt := sql%rowcount;
2479: IF PG_DEBUG <> 0 THEN
2480: oe_debug_pub.add('Cost_Rollup_ML: ' || 'rows inserted into cst_sc_lists::'||to_char(lCnt), 2);
2481: END IF;
2482:
2483: --
2484: -- call SC Cost Rollup API
2621:
2622:
2623: lStmtNumber := 60;
2624: if cst_sc_list_count > 0 then --bug4867460: call the costing api only if records are inserted
2625: -- in cst_sc_lists. This is to improve performance.
2626: IF PG_DEBUG <> 0 THEN
2627: oe_debug_pub.add('******************** calling costing API ****************** ', 2);
2628: oe_debug_pub.add(' Params assig set ' || lMrpAssignmentSet ||
2629: ' cto cost id ' || v_cto_cost_type_id ||