[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 (
907: if (l_debug = 'Y') then
908: fnd_file.put_line(fnd_file.log, 'Calling insertUpdate_EamPerBal');
909: end if;
910:
911: -- Insert/update WIP_EAM_PERIOD_BALANCES
912: l_stmt_num := 120;
913: InsertUpdate_eamPerBal(
914: p_api_version => 1.0,
915: x_return_status => l_return_status,
1003: -- PROCEDURE
1004: -- InsertUpdate_eamPerBal
1005: --
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: --
1242: END IF;
1243: END IF;
1244:
1245: /* -----------------------------------------------------------
1246: Insert/update WIP_EAM_PERIOD_BALANCES
1247: ------------------------------------------------------------- */
1248:
1249: l_stmt_num := 140;
1250: /* Bug 5315176 - Added nvl as operations are stamped in Requirements
1250: /* Bug 5315176 - Added nvl as operations are stamped in Requirements
1251: only when the work order is released */
1252: SELECT count(*)
1253: INTO l_count
1254: FROM wip_eam_period_balances
1255: WHERE period_set_name = l_period_set_name AND
1256: period_name = l_period_name AND
1257: /* Bug 2113001 */
1258: acct_period_id = l_period_id AND
1268: /* Bug 2545791: Change statement to use bind variables. */
1269: /* Bug 2935692: Change statement to use bind variables even in SET clause. */
1270: /* Bug 5315176 - Added nvl as operations are stamped in Requirements
1271: only when the work order is released */
1272: l_statement := 'UPDATE wip_eam_period_balances SET '
1273: || l_column || '='
1274: || 'nvl('|| l_column || ',0) + nvl(:p_value,0)'
1275: || ', last_update_date = sysdate'
1276: || ', last_updated_by = :p_user_id'
1301: ELSE
1302: l_stmt_num := 160;
1303:
1304: if (l_debug = 'Y') then
1305: FND_FILE.PUT_LINE(fnd_file.log,'Inserting wip_eam_period_balances....');
1306: FND_FILE.PUT_LINE(fnd_file.log,'period_Setname: '|| l_period_set_name );
1307: FND_FILE.PUT_LINE(fnd_file.log,'period_name: '||l_period_name );
1308: FND_FILE.PUT_LINE(fnd_file.log,'acct_period_id: '||to_char( l_period_id) );
1309: FND_FILE.PUT_LINE(fnd_file.log,'owning_dept_id: '||to_char(p_owning_dept_id) );
1313: FND_FILE.PUT_LINE(fnd_file.log,'asset group id: '|| to_char( p_asset_group_id) );
1314: end if;
1315:
1316:
1317: INSERT INTO wip_eam_period_balances (
1318: period_set_name,
1319: period_name,
1320: acct_period_id,
1321: wip_entity_id,
2233: sum(NVL(system_estimated_eqp_cost,0)) sys_eqp,
2234: sum(NVL(manual_estimated_mat_cost,0)) man_mat,
2235: sum(NVL(manual_estimated_lab_cost,0)) man_lab,
2236: sum(NVL(manual_estimated_eqp_cost,0)) man_eqp
2237: FROM wip_eam_period_balances
2238: WHERE wip_entity_id = c_wip_entity_id AND
2239: organization_id = c_org_id
2240: GROUP BY period_set_name,
2241: period_name,
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: ------------------------------------------------------
2341:
2568: -------------------------------------------------------------
2569: l_stmt_num := 330;
2570: FORALL l_index IN p_entity_id_tab.FIRST..p_entity_id_tab.LAST
2571:
2572: UPDATE wip_eam_period_balances
2573: SET system_estimated_mat_cost =
2574: decode(p_type,1,0,system_estimated_mat_cost),
2575: system_estimated_lab_cost =
2576: decode(p_type,1,0,system_estimated_lab_cost),
2589: -- Delete wepb and ceapb rows with zeros in ALL value columns
2590: ----------------------------------------------------------------
2591: l_stmt_num := 340;
2592: FORALL l_index IN p_entity_id_tab.FIRST..p_entity_id_tab.LAST
2593: DELETE from wip_eam_period_balances
2594: WHERE actual_mat_cost = 0 AND
2595: NVL(actual_lab_cost,0) = 0 AND
2596: NVL(actual_eqp_cost,0) = 0 AND
2597: NVL(system_estimated_mat_cost,0) = 0 AND
9901: SUM(NVL(WEPB.system_estimated_mat_cost,0)) estimated_mat_cost,
9902: SUM(NVL(WEPB.system_estimated_lab_cost,0)) estimated_lab_cost,
9903: SUM(NVL(WEPB.system_estimated_eqp_cost,0)) estimated_eqp_cost
9904: FROM cst_eam_hierarchy_snapshot CURR,
9905: wip_eam_period_balances WEPB
9906: WHERE CURR.group_id = p_group_id
9907: AND CURR.level_num = l_level
9908: AND CURR.object_type = 2 -- WIP job
9909: AND WEPB.organization_id = p_organization_id