[Home] [Help]
6653: raise fnd_api.g_exc_unexpected_error;
6654: end if;
6655:
6656:
6657: -- Update wip period balances , pl_material_in
6658: l_stmt_num := 110;
6659: if (l_debug = 'Y') then
6660: fnd_file.put_line(fnd_file.log,'Update wip_period_balances');
6661: end if;
6656:
6657: -- Update wip period balances , pl_material_in
6658: l_stmt_num := 110;
6659: if (l_debug = 'Y') then
6660: fnd_file.put_line(fnd_file.log,'Update wip_period_balances');
6661: end if;
6662:
6663: update_wip_period_balances (
6664: p_api_version => 1.0,
6659: if (l_debug = 'Y') then
6660: fnd_file.put_line(fnd_file.log,'Update wip_period_balances');
6661: end if;
6662:
6663: update_wip_period_balances (
6664: p_api_version => 1.0,
6665: p_wip_entity_id => direct_item_txn_rec.wip_entity_id,
6666: p_acct_period_id => direct_item_txn_rec.acct_period_id,
6667: p_txn_id => direct_item_txn_rec.transaction_id,
7104: END insert_direct_item_distr;
7105:
7106: ----------------------------------------------------------------------------
7107: -- PROCEDURE --
7108: -- update_wip_period_balances --
7109: -- DESCRIPTION --
7110: -- This function updates the tl_material_in in wip_period_balances --
7111: -- for the Direct Item Shopfloor delivery transaction --
7112: -- PURPOSE: --
7106: ----------------------------------------------------------------------------
7107: -- PROCEDURE --
7108: -- update_wip_period_balances --
7109: -- DESCRIPTION --
7110: -- This function updates the tl_material_in in wip_period_balances --
7111: -- for the Direct Item Shopfloor delivery transaction --
7112: -- PURPOSE: --
7113: -- Oracle Applications - Enterprise asset management --
7114: -- Beta on 11i Patchset G --
7116: -- --
7117: -- HISTORY: --
7118: -- 07/18/01 Anitha Dixit Created --
7119: ----------------------------------------------------------------------------
7120: PROCEDURE update_wip_period_balances (
7121: p_api_version IN NUMBER,
7122: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
7123: p_commit IN VARCHAR2 := FND_API.G_FALSE,
7124: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
7135: x_return_status OUT NOCOPY VARCHAR2,
7136: x_msg_count OUT NOCOPY NUMBER,
7137: x_msg_data OUT NOCOPY VARCHAR2 ) IS
7138:
7139: l_api_name CONSTANT VARCHAR2(30) := 'update_wip_period_balances';
7140: l_api_version CONSTANT NUMBER := 1.0;
7141:
7142: l_api_message VARCHAR2(240);
7143: l_statement NUMBER := 0;
7150: BEGIN
7151: ---------------------------------------------
7152: -- Standard start of API savepoint
7153: ---------------------------------------------
7154: SAVEPOINT update_wip_period_balances_PUB;
7155:
7156: l_debug := fnd_profile.value('MRP_DEBUG');
7157:
7158: ------------------------------------------------
7235: in the SQL and use bind variables instead. This is to make the SQL
7236: comply with PL/SQL Standards */
7237:
7238: if l_cost_element = 1 then
7239: l_update_stmt := 'UPDATE wip_period_balances ' ||
7240: 'SET pl_material_in = nvl( pl_material_in, 0) + :l_txn_value , ' ||
7241: 'last_update_date = sysdate, ' ||
7242: 'last_updated_by = :p_user_id, ' ||
7243: 'last_update_login = :p_login_id, ' ||
7248: 'WHERE wip_entity_id = :p_wip_entity_id ' ||
7249: ' AND acct_period_id = :p_acct_period_id ';
7250:
7251: elsif l_cost_element = 3 then
7252: l_update_stmt := 'UPDATE wip_period_balances ' ||
7253: 'SET tl_resource_in = nvl( tl_resource_in, 0) + :l_txn_value , ' ||
7254: 'last_update_date = sysdate, ' ||
7255: 'last_updated_by = :p_user_id, ' ||
7256: 'last_update_login = :p_login_id, ' ||
7261: 'WHERE wip_entity_id = :p_wip_entity_id ' ||
7262: ' AND acct_period_id = :p_acct_period_id ';
7263:
7264: else
7265: l_update_stmt := 'UPDATE wip_period_balances ' ||
7266: 'SET tl_outside_processing_in = ' ||
7267: 'nvl( tl_outside_processing_in, 0) + :l_txn_value , ' ||
7268: 'last_update_date = sysdate, ' ||
7269: 'last_updated_by = :p_user_id, ' ||
7277:
7278: end if;
7279:
7280: --------------------------------------------
7281: -- Update wip_period_balances
7282: --------------------------------------
7283: l_statement := 60;
7284:
7285: EXECUTE IMMEDIATE l_update_stmt USING
7300:
7301:
7302: EXCEPTION
7303: WHEN fnd_api.g_exc_error then
7304: ROLLBACK TO update_wip_period_balances_PUB;
7305: x_return_status := fnd_api.g_ret_sts_error;
7306:
7307: fnd_msg_pub.count_and_get
7308: ( p_count => x_msg_count,
7308: ( p_count => x_msg_count,
7309: p_data => x_msg_data );
7310:
7311: WHEN fnd_api.g_exc_unexpected_error then
7312: ROLLBACK TO update_wip_period_balances_PUB;
7313: x_return_status := fnd_api.g_ret_sts_unexp_error;
7314:
7315: fnd_msg_pub.count_and_get
7316: ( p_count => x_msg_count,
7316: ( p_count => x_msg_count,
7317: p_data => x_msg_data );
7318:
7319: WHEN OTHERS THEN
7320: ROLLBACK TO update_wip_period_balances_PUB;
7321: x_return_status := fnd_api.g_ret_sts_unexp_error ;
7322: If fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
7323: fnd_msg_pub.add_exc_msg
7324: ( 'CST_eamCost_PUB',' update_wip_period_balances : Statement - ' || to_char(l_statement));
7320: ROLLBACK TO update_wip_period_balances_PUB;
7321: x_return_status := fnd_api.g_ret_sts_unexp_error ;
7322: If fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
7323: fnd_msg_pub.add_exc_msg
7324: ( 'CST_eamCost_PUB',' update_wip_period_balances : Statement - ' || to_char(l_statement));
7325: end if;
7326:
7327: fnd_msg_pub.count_and_get( p_count => x_msg_count,
7328: p_data => x_msg_data );
7325: end if;
7326:
7327: fnd_msg_pub.count_and_get( p_count => x_msg_count,
7328: p_data => x_msg_data );
7329: END update_wip_period_balances;
7330:
7331: ----------------------------------------------------------------------------
7332: -- PROCEDURE --
7333: -- insert_direct_item_txn --
8126: INTO l_pl_var,
8127: l_res_var,
8128: l_osp_var,
8129: l_ovh_var
8130: from wip_period_balances wpb
8131: where wpb.wip_entity_id = c_route_rec.wip_entity_id
8132: and wpb.acct_period_id <= c_route_rec.acct_period_id;
8133:
8134: l_stmt_num := 32;