[Home] [Help]
1709: END IF;
1710:
1711:
1712:
1713: /* can't do bulk insert into cst_sc_lists for PLS-00436 */
1714: /* insert item by item */
1715:
1716: lStmtNumber := 39.1;
1717:
1715:
1716: lStmtNumber := 39.1;
1717:
1718:
1719: insert into cst_sc_lists(
1720: rollup_id,
1721: inventory_item_id,
1722: organization_id,
1723: last_update_date,
1746: and msi.inventory_item_id = p_cfg_itm_tbl(i).cfg_item_id
1747: and msi.organization_id = p_cfg_itm_tbl(i).cfg_org_id
1748: and NOT EXISTS
1749: (select NULL
1750: from cst_sc_lists
1751: where rollup_id = lRollupId
1752: and inventory_item_id = msi.inventory_item_id
1753: and organization_id = mp.cost_organization_id) --3116778
1754: and NOT EXISTS /* check whether item has been rolled up */
2170:
2171: -- debug 3116778
2172: cursor cst_cur (xRollupId number) is
2173: select inventory_item_id,organization_id
2174: from cst_sc_lists
2175: where rollup_id =xRollupId;
2176: d_item_id number;
2177: d_org_id number;
2178:
2497:
2498:
2499:
2500: --
2501: -- insert into CST_SC_LISTS rows for all config items in all possible
2502: -- src orgs, where ->
2503: -- 1. cost has not been calculated yet (cost_rollup = Y)
2504: -- 2. costing_enabled_flag = Y
2505: -- 3. primary_cost_method = 1 (standard)
2504: -- 2. costing_enabled_flag = Y
2505: -- 3. primary_cost_method = 1 (standard)
2506: --
2507: IF PG_DEBUG <> 0 THEN
2508: oe_debug_pub.add('Cost_Rollup_ML: ' || 'inserting into cst_sc_lists', 2);
2509: END IF;
2510:
2511:
2512: /*
2510:
2511:
2512: /*
2513: BUG 3931290
2514: The statement insert into cst_sc_lists was using a union (organization_id, rcv_org_id)
2515: to determine organizations where cost rollup needs to be performed.
2516: The organization where cost rollup will be performed will now be determined using organization_id.
2517: This will eliminate the 2nd sql statement (rcv_org_id) in the union and there will be no need for a union.
2518: */
2517: This will eliminate the 2nd sql statement (rcv_org_id) in the union and there will be no need for a union.
2518: */
2519:
2520: lStmtNumber := 38;
2521: insert into cst_sc_lists(
2522: rollup_id,
2523: inventory_item_id,
2524: organization_id,
2525: last_update_date,
2568: )
2569: )
2570: and NOT EXISTS
2571: (select NULL
2572: from cst_sc_lists
2573: where rollup_id = lRollupId
2574: and inventory_item_id = bcso.config_item_id
2575: and organization_id = mp.cost_organization_id ) ;
2576:
2578:
2579: -- debug 3116778
2580: lCnt := sql%rowcount;
2581: IF PG_DEBUG <> 0 THEN
2582: oe_debug_pub.add('Cost_Rollup_ML: ' || 'rows inserted into cst_sc_lists::'||to_char(lCnt), 2);
2583: END IF;
2584: cst_sc_list_count := lCnt; -- Bug# 4867460
2585: open cst_cur ( lRollupId ) ;
2586:
2601:
2602:
2603: lCnt := sql%rowcount;
2604: IF PG_DEBUG <> 0 THEN
2605: oe_debug_pub.add('Cost_Rollup_ML: ' || 'rows inserted into cst_sc_lists::'||to_char(lCnt), 2);
2606: END IF;
2607:
2608: --
2609: -- call SC Cost Rollup API
2746:
2747:
2748: lStmtNumber := 60;
2749: if cst_sc_list_count > 0 then --bug4867460: call the costing api only if records are inserted
2750: -- in cst_sc_lists. This is to improve performance.
2751: IF PG_DEBUG <> 0 THEN
2752: oe_debug_pub.add('******************** calling costing API ****************** ', 2);
2753: oe_debug_pub.add(' Params assig set ' || lMrpAssignmentSet ||
2754: ' cto cost id ' || v_cto_cost_type_id ||