[Home] [Help]
413: -- PROCEDURE
414: -- Update_eamCost
415: --
416: -- DESCRIPTION
417: -- This API insert or updates WIP_EAM_PERIOD_BALANCES and CST_EAM_ASSET_PER_BALANCES
418: -- with the amount passed by the calling program.
419: --
420: -- PURPOSE:
421: -- Support eAM job costing in Oracle Applications Rel 11i
449: -- on the resource type, and the dept type of the owning department.
450: --
451: -- ESTIMATED COSTS:
452: -- The eAM cost estimating process will call this API to populate
453: -- WIP_EAM_PERIOD_BALANCES and CST_EAM_ASSET_PER_BALANCES with the estimated
454: -- cost values. If it is a re-estimate, the calling program should back out the
455: -- old estimates from both WIP_EAM_PERIOD_BALANCES and CST_EAM_ASSET_PER_BALANCES
456: -- before calling this API and passing the new estimates to avoid duplication.
457: /* =============================================================================== */
451: -- ESTIMATED COSTS:
452: -- The eAM cost estimating process will call this API to populate
453: -- WIP_EAM_PERIOD_BALANCES and CST_EAM_ASSET_PER_BALANCES with the estimated
454: -- cost values. If it is a re-estimate, the calling program should back out the
455: -- old estimates from both WIP_EAM_PERIOD_BALANCES and CST_EAM_ASSET_PER_BALANCES
456: -- before calling this API and passing the new estimates to avoid duplication.
457: /* =============================================================================== */
458:
459: PROCEDURE Update_eamCost (
685: if (l_debug = 'Y') then
686: fnd_file.put_line(fnd_file.log, 'Calling insertUpdate_EamPerBal');
687: end if;
688:
689: -- Insert/update WIP_EAM_PERIOD_BALANCES
690: l_stmt_num := 120;
691: InsertUpdate_eamPerBal(
692: p_api_version => 1.0,
693: x_return_status => l_return_status,
781: -- PROCEDURE
782: -- InsertUpdate_eamPerBal
783: --
784: -- DESCRIPTION
785: -- This procedure inserts or updates a row in wip_eam_period_balances table,
786: -- according to the parameters passed by the calling program.
787: -- Subsequently, it also inserts or update the related row in
788: -- cst_eam_asset_per_balances.
789: --
1020: END IF;
1021: END IF;
1022:
1023: /* -----------------------------------------------------------
1024: Insert/update WIP_EAM_PERIOD_BALANCES
1025: ------------------------------------------------------------- */
1026:
1027: l_stmt_num := 140;
1028: /* Bug 5315176 - Added nvl as operations are stamped in Requirements
1028: /* Bug 5315176 - Added nvl as operations are stamped in Requirements
1029: only when the work order is released */
1030: SELECT count(*)
1031: INTO l_count
1032: FROM wip_eam_period_balances
1033: WHERE period_set_name = l_period_set_name AND
1034: period_name = l_period_name AND
1035: /* Bug 2113001 */
1036: acct_period_id = l_period_id AND
1046: /* Bug 2545791: Change statement to use bind variables. */
1047: /* Bug 2935692: Change statement to use bind variables even in SET clause. */
1048: /* Bug 5315176 - Added nvl as operations are stamped in Requirements
1049: only when the work order is released */
1050: l_statement := 'UPDATE wip_eam_period_balances SET '
1051: || l_column || '='
1052: || 'nvl('|| l_column || ',0) + nvl(:p_value,0)'
1053: || ', last_update_date = sysdate'
1054: || ', last_updated_by = :p_user_id'
1079: ELSE
1080: l_stmt_num := 160;
1081:
1082: if (l_debug = 'Y') then
1083: FND_FILE.PUT_LINE(fnd_file.log,'Inserting wip_eam_period_balances....');
1084: FND_FILE.PUT_LINE(fnd_file.log,'period_Setname: '|| l_period_set_name );
1085: FND_FILE.PUT_LINE(fnd_file.log,'period_name: '||l_period_name );
1086: FND_FILE.PUT_LINE(fnd_file.log,'acct_period_id: '||to_char( l_period_id) );
1087: FND_FILE.PUT_LINE(fnd_file.log,'owning_dept_id: '||to_char(p_owning_dept_id) );
1091: FND_FILE.PUT_LINE(fnd_file.log,'asset group id: '|| to_char( p_asset_group_id) );
1092: end if;
1093:
1094:
1095: INSERT INTO wip_eam_period_balances (
1096: period_set_name,
1097: period_name,
1098: acct_period_id,
1099: wip_entity_id,
2011: sum(NVL(system_estimated_eqp_cost,0)) sys_eqp,
2012: sum(NVL(manual_estimated_mat_cost,0)) man_mat,
2013: sum(NVL(manual_estimated_lab_cost,0)) man_lab,
2014: sum(NVL(manual_estimated_eqp_cost,0)) man_eqp
2015: FROM wip_eam_period_balances
2016: WHERE wip_entity_id = c_wip_entity_id AND
2017: organization_id = c_org_id
2018: GROUP BY period_set_name,
2019: period_name,
2111: end if;
2112: End if;
2113: -----------------------------------------------------
2114: -- Update cst_eam_asset_per_balances first before
2115: -- deleting wip_eam_period_balances
2116: -- If the asset on the work order is a route asset,
2117: -- then adjust asset value for the members
2118: ------------------------------------------------------
2119:
2346: -------------------------------------------------------------
2347: l_stmt_num := 330;
2348: FORALL l_index IN p_entity_id_tab.FIRST..p_entity_id_tab.LAST
2349:
2350: UPDATE wip_eam_period_balances
2351: SET system_estimated_mat_cost =
2352: decode(p_type,1,0,system_estimated_mat_cost),
2353: system_estimated_lab_cost =
2354: decode(p_type,1,0,system_estimated_lab_cost),
2367: -- Delete wepb and ceapb rows with zeros in ALL value columns
2368: ----------------------------------------------------------------
2369: l_stmt_num := 340;
2370: FORALL l_index IN p_entity_id_tab.FIRST..p_entity_id_tab.LAST
2371: DELETE from wip_eam_period_balances
2372: WHERE actual_mat_cost = 0 AND
2373: NVL(actual_lab_cost,0) = 0 AND
2374: NVL(actual_eqp_cost,0) = 0 AND
2375: NVL(system_estimated_mat_cost,0) = 0 AND
9188: SUM(NVL(WEPB.system_estimated_mat_cost,0)) estimated_mat_cost,
9189: SUM(NVL(WEPB.system_estimated_lab_cost,0)) estimated_lab_cost,
9190: SUM(NVL(WEPB.system_estimated_eqp_cost,0)) estimated_eqp_cost
9191: FROM cst_eam_hierarchy_snapshot CURR,
9192: wip_eam_period_balances WEPB
9193: WHERE CURR.group_id = p_group_id
9194: AND CURR.level_num = l_level
9195: AND CURR.object_type = 2 -- WIP job
9196: AND WEPB.organization_id = p_organization_id