[Home] [Help]
614: -- PROCEDURE
615: -- Update_eamCost
616: --
617: -- DESCRIPTION
618: -- This API insert or updates WIP_EAM_PERIOD_BALANCES and CST_EAM_ASSET_PER_BALANCES
619: -- with the amount passed by the calling program.
620: --
621: -- PURPOSE:
622: -- Support eAM job costing in Oracle Applications Rel 11i
650: -- on the resource type, and the dept type of the owning department.
651: --
652: -- ESTIMATED COSTS:
653: -- The eAM cost estimating process will call this API to populate
654: -- WIP_EAM_PERIOD_BALANCES and CST_EAM_ASSET_PER_BALANCES with the estimated
655: -- cost values. If it is a re-estimate, the calling program should back out the
656: -- old estimates from both WIP_EAM_PERIOD_BALANCES and CST_EAM_ASSET_PER_BALANCES
657: -- before calling this API and passing the new estimates to avoid duplication.
658: /* =============================================================================== */
652: -- ESTIMATED COSTS:
653: -- The eAM cost estimating process will call this API to populate
654: -- WIP_EAM_PERIOD_BALANCES and CST_EAM_ASSET_PER_BALANCES with the estimated
655: -- cost values. If it is a re-estimate, the calling program should back out the
656: -- old estimates from both WIP_EAM_PERIOD_BALANCES and CST_EAM_ASSET_PER_BALANCES
657: -- before calling this API and passing the new estimates to avoid duplication.
658: /* =============================================================================== */
659:
660: PROCEDURE Update_eamCost (
1006: -- DESCRIPTION
1007: -- This procedure inserts or updates a row in wip_eam_period_balances table,
1008: -- according to the parameters passed by the calling program.
1009: -- Subsequently, it also inserts or update the related row in
1010: -- cst_eam_asset_per_balances.
1011: --
1012: -- PURPOSE
1013: -- Oracle Application Rel 11i.5
1014: -- eAM Job Costing support
1548:
1549: end if;
1550:
1551: if (l_debug = 'Y') then
1552: FND_FILE.PUT_LINE(fnd_file.log,'inserted into cst_eam_asset_per_balances' );
1553: end if;
1554:
1555: -- Standard check of p_commit
1556: IF FND_API.to_Boolean(p_commit) THEN
1637: --
1638: -- HISTORY
1639: -- 03/27/05 Anjali R Added p_maint_obj_id and p_maint_obj_type parameters
1640: -- for eAM Requirements Project - R12.
1641: -- CST_EAM_ASSET_PER_BALANCES table will now
1642: -- store corresponding IB Instance_id for each
1643: -- serialized asset or serialized rebuildable.
1644: -- For non-serialized rebuildable item, it will
1645: -- store MSI.inventory_item_id.
1724: l_count := 0;
1725:
1726: SELECT count(*)
1727: INTO l_count
1728: FROM cst_eam_asset_per_balances
1729: WHERE period_set_name = p_period_set_name AND
1730: period_name = p_period_name AND
1731: organization_id = p_org_id AND
1732: inventory_item_id = p_asset_group_id AND
1737: l_stmt_num := 180;
1738: /* Bug 2545791: Using bind variables to construct statement */
1739: /* Bug 2935692: Change statement to use bind variables even in SET clause. */
1740:
1741: l_statement := 'UPDATE cst_eam_asset_per_balances SET '
1742: || p_column || '='
1743: || 'nvl('|| p_column || ',0) + nvl(:p_value,0)'
1744: || ', last_update_date = sysdate'
1745: || ', last_updated_by = :p_user_id'
1755: p_maint_cost_cat ;
1756:
1757: ELSE
1758: l_stmt_num := 190;
1759: INSERT INTO cst_eam_asset_per_balances (
1760: period_set_name,
1761: period_name,
1762: acct_period_id,
1763: organization_id,
2168: -- This API removes the cost of a specific type, such as system --
2169: -- or manual estimates from wip_eam_per_balances and delete the rows --
2170: -- if all the costs are zeros. It also update the corresponding amount --
2171: -- It also update the corresponding amount in --
2172: -- cst_eam_asset_per_balances. --
2173: -- NOTE: This process is at the wip entity level. --
2174: -- --
2175: -- p_type = 1 (system estimates) --
2176: -- 2 (manual estimates) --
2332: and maintenance_object_type =3;
2333: end if;
2334: End if;
2335: -----------------------------------------------------
2336: -- Update cst_eam_asset_per_balances first before
2337: -- deleting wip_eam_period_balances
2338: -- If the asset on the work order is a route asset,
2339: -- then adjust asset value for the members
2340: ------------------------------------------------------
2347: ( v_est_rec.sys_mat <> 0 OR
2348: v_est_rec.sys_lab <> 0 OR
2349: v_est_rec.sys_eqp <> 0) THEN
2350: l_stmt_num := 312;
2351: UPDATE cst_eam_asset_per_balances
2352: SET system_estimated_mat_cost =
2353: system_estimated_mat_cost
2354: - (v_est_rec.sys_mat/l_asset_count),
2355: system_estimated_lab_cost =
2383:
2384: ----------------------------------------------------------------
2385: -- Delete ceapb rows with zeros in ALL value columns
2386: ----------------------------------------------------------------
2387: DELETE from cst_eam_asset_per_balances
2388: WHERE actual_mat_cost = 0 AND
2389: NVL(actual_lab_cost,0) = 0 AND
2390: NVL(actual_eqp_cost,0) = 0 AND
2391: NVL(system_estimated_mat_cost,0) = 0 AND
2421: (v_est_rec.man_mat <> 0 OR
2422: v_est_rec.man_lab <> 0 OR
2423: v_est_rec.man_eqp <> 0) THEN
2424: l_stmt_num := 314;
2425: UPDATE cst_eam_asset_per_balances
2426: SET manual_estimated_mat_cost =
2427: manual_estimated_mat_cost
2428: - (v_est_rec.man_mat/l_asset_count),
2429: manual_estimated_lab_cost =
2457:
2458: ----------------------------------------------------------------
2459: -- Delete ceapb rows with zeros in ALL value columns
2460: ----------------------------------------------------------------
2461: DELETE from cst_eam_asset_per_balances
2462: WHERE actual_mat_cost = 0 AND
2463: NVL(actual_lab_cost,0) = 0 AND
2464: NVL(actual_eqp_cost,0) = 0 AND
2465: NVL(system_estimated_mat_cost,0) = 0 AND
2503: ( v_est_rec.sys_mat <> 0 OR
2504: v_est_rec.sys_lab <> 0 OR
2505: v_est_rec.sys_eqp <> 0) THEN
2506: l_stmt_num := 316;
2507: UPDATE cst_eam_asset_per_balances
2508: SET system_estimated_mat_cost =
2509: system_estimated_mat_cost
2510: - v_est_rec.sys_mat,
2511: system_estimated_lab_cost =
2523: (v_est_rec.man_mat <> 0 OR
2524: v_est_rec.man_lab <> 0 OR
2525: v_est_rec.man_eqp <> 0) THEN
2526: l_stmt_num := 320;
2527: UPDATE cst_eam_asset_per_balances
2528: SET manual_estimated_mat_cost =
2529: manual_estimated_mat_cost
2530: - v_est_rec.man_mat,
2531: manual_estimated_lab_cost =
2549: l_stmt_num := 325;
2550: ----------------------------------------------------------------
2551: -- Delete ceapb rows with zeros in ALL value columns
2552: ----------------------------------------------------------------
2553: DELETE from cst_eam_asset_per_balances
2554: WHERE actual_mat_cost = 0 AND
2555: NVL(actual_lab_cost,0) = 0 AND
2556: NVL(actual_eqp_cost,0) = 0 AND
2557: NVL(system_estimated_mat_cost,0) = 0 AND
5663: sysdate,
5664: 1,
5665: NULL,
5666: NULL
5667: FROM cst_eam_asset_per_balances ceapb,
5668: mtl_serial_numbers msn,
5669: --Bug#16095661: Distinct group_id, object
5670: (select DISTINCT group_id, object_id
5671: from cst_eam_hierarchy_snapshot