717: -- Type : Public.
718: -- Function : This API is called from Estimate_PAC_WipJobs
719: -- Flow:
720: -- |-- Get estimation details of the wip_entity LOOP
721: -- | |--Update amount in cst_pac_eam_asset_per_balances
722: -- | End Loop;
723: -- |-- Update estimation columns of cst_pac_eam_period_balances to 0
724: -- |-- Delete the row in cst_pac_eam_period_balances if estimation and
725: -- | actual cost columns are 0 or null
722: -- | End Loop;
723: -- |-- Update estimation columns of cst_pac_eam_period_balances to 0
724: -- |-- Delete the row in cst_pac_eam_period_balances if estimation and
725: -- | actual cost columns are 0 or null
726: -- |-- Similarly delete the row in cst_pac_eam_asset_per_balances if
727: -- | estimation and actual cost columns are 0 or null
728: --
729: -- Pre-reqs : None.
730: -- Parameters :
883:
884: FOR v_est_rec IN v_est_csr(l_organization_id,
885: p_wip_entity_id_tab(l_index)) LOOP
886:
887: -- Update system estimates in cst_pac_eam_asset_per_balances
888: IF ( v_est_rec.sys_mat <> 0
889: OR v_est_rec.sys_lab <> 0
890: OR v_est_rec.sys_eqp <> 0) THEN
891:
890: OR v_est_rec.sys_eqp <> 0) THEN
891:
892: l_stmt_num := 120;
893:
894: UPDATE cst_pac_eam_asset_per_balances
895: SET system_estimated_mat_cost = system_estimated_mat_cost -
896: v_est_rec.sys_mat,
897: system_estimated_lab_cost = system_estimated_lab_cost -
898: v_est_rec.sys_lab,
911:
912: END LOOP;
913:
914: -- Delete cpeapb rows with zeros in ALL value columns
915: DELETE from cst_pac_eam_asset_per_balances
916: WHERE NVL(actual_mat_cost,0) = 0
917: AND NVL(actual_lab_cost,0) = 0
918: AND NVL(actual_eqp_cost,0) = 0
919: AND NVL(system_estimated_mat_cost,0) = 0
3327: -- Type : Public.
3328: -- Function : This API is called from InsertUpdate_PAC_eamPerBal
3329: -- Flow:
3330: -- Check if records already exist in CST_EAM_PAC_ASSET_PER_BALANCES
3331: -- |-- If yes then Update CST_PAC_EAM_ASSET_PER_BALANCES
3332: -- |-- Else Insert into CST_PAC_EAM_ASSET_PER_BALANCES
3333: -- End if
3334: --
3335: -- Pre-reqs : None.
3328: -- Function : This API is called from InsertUpdate_PAC_eamPerBal
3329: -- Flow:
3330: -- Check if records already exist in CST_EAM_PAC_ASSET_PER_BALANCES
3331: -- |-- If yes then Update CST_PAC_EAM_ASSET_PER_BALANCES
3332: -- |-- Else Insert into CST_PAC_EAM_ASSET_PER_BALANCES
3333: -- End if
3334: --
3335: -- Pre-reqs : None.
3336: -- Parameters :
3468:
3469: -- Check if records already exist for the asset
3470: SELECT count(*)
3471: INTO l_count
3472: FROM cst_pac_eam_asset_per_balances
3473: WHERE period_set_name = p_period_set_name
3474: AND period_name = p_period_name
3475: AND organization_id = p_organization_id
3476: AND inventory_item_id = p_asset_group_id
3485: IF l_count > 0 THEN -- If records already exist then Update
3486:
3487: l_stmt_num := 505;
3488:
3489: l_statement := 'UPDATE cst_pac_eam_asset_per_balances SET '
3490: || p_column || '='
3491: || 'nvl('|| p_column || ',0) + nvl(:p_value,0)'
3492: || ', last_update_date = sysdate'
3493: || ', last_updated_by = :p_user_id'
3518: ELSE -- If no records exist, then Insert
3519:
3520: l_stmt_num := 515;
3521:
3522: INSERT INTO cst_pac_eam_asset_per_balances (
3523: legal_entity_id,
3524: cost_group_id,
3525: cost_type_id,
3526: period_set_name,