[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ams_deliverables_all_b_s.NEXTVAL
FROM DUAL;
SELECT COUNT(*)
FROM ams_deliverables_vl
WHERE deliverable_id = deliv_id;
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE system_status_type = 'AMS_DELIV_STATUS'
AND user_status_id = status_id;
SELECT custom_setup_id
FROM ams_custom_setups_b
WHERE object_type = 'DELV';
SELECT user_status_id
-- SQLID: 11753160 fix FROM ams_user_statuses_vl a
FROM ams_user_statuses_b a
WHERE system_status_type = 'AMS_DELIV_STATUS'
AND system_Status_code = status_code
AND a.user_status_id = (SELECT MIN(b.user_status_id)
--SQLID: 11753160 fix FROM ams_user_statuses_vl b
FROM ams_user_statuses_b b
WHERE b.system_status_type = a.system_status_type
AND a.system_status_code = b.system_Status_code) ;
SELECT min(user_status_id)
FROM ams_user_statuses_b a
WHERE system_status_type = 'AMS_DELIV_STATUS'
AND system_Status_code = status_code;
if the periods exist then only the calendar values has to updated
*/
IF ((l_deliv_rec.start_period_name IS NOT NULL)
OR (l_deliv_rec.end_period_name IS NOT NULL)) THEN
-- default deliverable_calendar
IF l_deliv_rec.deliverable_calendar IS NULL THEN
l_deliv_rec.deliverable_calendar := FND_PROFILE.value('AMS_CAMPAIGN_DEFAULT_CALENDER');
AMS_Utility_PVT.debug_message(l_full_name ||': insert');
INSERT INTO ams_deliverables_all_b(
deliverable_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
language_code,
version,
application_id,
user_status_id,
status_code,
status_date,
active_flag,
private_flag,
owner_user_id,
org_id,
fund_source_id,
fund_source_type,
category_type_id,
category_sub_type_id,
kit_flag,
can_fulfill_electronic_flag,
can_fulfill_physical_flag,
jtf_amv_item_id,
inventory_flag,
transaction_currency_code,
functional_currency_code,
budget_amount_tc,
budget_amount_fc,
actual_avail_from_date,
actual_avail_to_date,
forecasted_complete_date,
actual_complete_date,
replaced_by_deliverable_id,
inventory_item_id,
inventory_item_org_id,
pricelist_header_id,
pricelist_line_id,
non_inv_ctrl_code,
non_inv_quantity_on_hand,
non_inv_quantity_on_order,
non_inv_quantity_on_reserve,
chargeback_amount,
chargeback_amount_curr_code,
deliverable_code,
deliverable_pick_flag,
currency_code,
forecasted_cost,
actual_cost,
forecasted_responses,
actual_responses,
country,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
chargeback_uom ,
deliverable_calendar,
start_period_name,
end_period_name,
country_id,
custom_setup_id,
email_content_type
) VALUES (
l_deliv_rec.deliverable_id,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id,
1, -- object_version_number
l_deliv_rec.language_code,
l_deliv_rec.version,
l_deliv_rec.application_id,
l_deliv_rec.user_status_id,
l_deliv_rec.status_code,
l_deliv_rec.status_date,
NVL(l_deliv_rec.active_flag, 'Y'),
NVL(l_deliv_rec.private_flag, 'N'),
l_deliv_rec.owner_user_id,
TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10)), -- org_id
l_deliv_rec.fund_source_id,
l_deliv_rec.fund_source_type,
l_deliv_rec.category_type_id,
l_deliv_rec.category_sub_type_id,
NVL(l_deliv_rec.kit_flag, 'N'),
NVL(l_deliv_rec.can_fulfill_electronic_flag, 'N'),
NVL(l_deliv_rec.can_fulfill_physical_flag, 'N'),
l_deliv_rec.jtf_amv_item_id,
NVL(l_deliv_rec.inventory_flag, 'N'),
l_deliv_rec.transaction_currency_code,--l_deliv_rec.currency_code, /* since defaulted to currency_code if it is null */
l_deliv_rec.functional_currency_code,
l_deliv_rec.budget_amount_tc,
l_deliv_rec.budget_amount_fc,
l_deliv_rec.actual_avail_from_date,
l_deliv_rec.actual_avail_to_date,
l_deliv_rec.forecasted_complete_date,
l_deliv_rec.actual_complete_date,
l_deliv_rec.replaced_by_deliverable_id,
l_deliv_rec.inventory_item_id,
l_deliv_rec.inventory_item_org_id,
l_deliv_rec.pricelist_header_id,
l_deliv_rec.pricelist_line_id,
l_deliv_rec.non_inv_ctrl_code,
l_deliv_rec.non_inv_quantity_on_hand,
l_deliv_rec.non_inv_quantity_on_order,
l_deliv_rec.non_inv_quantity_on_reserve,
l_deliv_rec.chargeback_amount,
l_deliv_rec.chargeback_amount_curr_code,
l_deliv_rec.deliverable_code,
NVL(l_deliv_rec.deliverable_pick_flag, 'N'),
l_deliv_rec.currency_code,
l_deliv_rec.forecasted_cost,
l_deliv_rec.actual_cost,
l_deliv_rec.forecasted_responses,
l_deliv_rec.actual_responses,
l_deliv_rec.country,
l_deliv_rec.attribute_category,
l_deliv_rec.attribute1,
l_deliv_rec.attribute2,
l_deliv_rec.attribute3,
l_deliv_rec.attribute4,
l_deliv_rec.attribute5,
l_deliv_rec.attribute6,
l_deliv_rec.attribute7,
l_deliv_rec.attribute8,
l_deliv_rec.attribute9,
l_deliv_rec.attribute10,
l_deliv_rec.attribute11,
l_deliv_rec.attribute12,
l_deliv_rec.attribute13,
l_deliv_rec.attribute14,
l_deliv_rec.attribute15,
l_deliv_rec.chargeback_uom ,
l_deliv_rec.deliverable_calendar,
l_deliv_rec.start_period_name,
l_deliv_rec.end_period_name,
l_deliv_rec.country_id,
l_deliv_Rec.setup_id,
l_deliv_Rec.email_content_type
);
INSERT INTO ams_deliverables_all_tl(
deliverable_id,
language,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
source_lang,
deliverable_name,
description
)
SELECT
l_deliv_rec.deliverable_id,
l.language_code,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id,
USERENV('LANG'),
l_deliv_rec.deliverable_name,
l_deliv_rec.description
FROM fnd_languages l
WHERE l.installed_flag in ('I', 'B')
AND NOT EXISTS(
SELECT NULL
FROM ams_deliverables_all_tl t
WHERE t.deliverable_id = l_deliv_rec.deliverable_id
AND t.language = l.language_code );
l_access_rec.delete_flag := 'N';
update_deliverable
(p_api_version => 1.0,
p_commit => p_commit,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_deliv_rec => x_deliv_rec
);
AMS_Utility_PVT.debug_message(l_full_name||': in the exception of call to update');
PROCEDURE delete_deliverable
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_deliv_id IN NUMBER,
p_object_version IN NUMBER
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'delete_deliverable';
SAVEPOINT delete_deliverable;
AMS_Utility_PVT.debug_message(l_full_name ||': delete');
DELETE FROM ams_deliverables_all_b
WHERE deliverable_id = p_deliv_id
AND object_version_number = p_object_version;
DELETE FROM ams_deliverables_all_tl
WHERE deliverable_id = p_deliv_id;
ROLLBACK TO delete_deliverable;
ROLLBACK TO delete_deliverable;
ROLLBACK TO delete_deliverable;
END delete_deliverable;
SELECT deliverable_id
FROM ams_deliverables_all_b
WHERE deliverable_id = p_deliv_id
AND object_version_number = p_object_version
FOR UPDATE OF deliverable_id NOWAIT;
SELECT deliverable_id
FROM ams_deliverables_all_tl
WHERE deliverable_id = p_deliv_id
AND USERENV('LANG') IN (language, source_lang)
FOR UPDATE OF deliverable_id NOWAIT;
PROCEDURE update_deliverable
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validation_level IN NUMBER := FND_API.g_valid_level_full,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_deliv_rec IN deliv_rec_type
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'update_deliverable';
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE user_status_id = status_id;
SELECT user_status_id
FROM ams_user_statuses_vl
WHERE system_status_type = l_deliv_status_type
AND system_status_code = p_status_code
AND default_flag = 'Y';
SELECT user_status_id, status_code
FROM ams_deliverables_vl
WHERE deliverable_id = p_deliverable_id;
SELECT distinct(inventory_item_id)
FROM mtl_system_items_b_kfv
WHERE segment1 = p_inv_item_num;
SELECT distinct(pricelist_header_id)
FROM ams_deliverables_vl;
SELECT jtf_amv_item_id
FROM ams_deliverables_vl
WHERE deliverable_id = p_deliv_id;
SELECT count(1)
FROM jtf_amv_attachments_v
WHERE attachment_used_by_id = p_deliv_id
AND attachment_used_by = 'AMS_DELV';
SELECT file_id, file_name, attachment_type, version
FROM jtf_amv_attachments_v
WHERE attachment_used_by_id = p_deliv_id
AND attachment_used_by = 'AMS_DELV'
AND creation_date = (
SELECT max(creation_date) FROM jtf_amv_attachments_v
WHERE attachment_used_by_id = p_deliv_id
AND attachment_used_by = 'AMS_DELV');
SELECT file_id, file_name, attachment_type, version
FROM jtf_amv_attachments_v
WHERE attachment_used_by_id = p_deliv_id
AND attachment_used_by = 'AMS_DELV'
AND file_id NOT IN
( select file_id
from jtf_amv_attachments_v
where attachment_used_by_id = p_itm_id
and attachment_used_by = 'ITEM' );
SELECT object_version_number
from ams_deliverables_all_b where deliverable_id = delv_id;
SELECT owner_user_id
FROM ams_deliverables_all_b
WHERE deliverable_id = deliv_id;
SELECT DISTINCT 'Y'
FROM ams_act_budgets
WHERE arc_act_budget_used_by='DELV'
AND act_budget_used_by_id = deliv_id;
SELECT kit_flag, inventory_flag,non_inv_quantity_on_hand
FROM ams_deliverables_all_b
WHERE deliverable_id = deliv_id;
SELECT resource_id
FROM ams_jtf_rs_emp_v
WHERE user_id = l_user_id;
SAVEPOINT update_deliverable;
if AMS_ACCESS_PVT.check_update_access(l_deliv_rec.deliverable_id, 'DELV', l_res_id, 'USER') = 'N' then
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
FND_MESSAGE.set_name('AMS', 'AMS_EVO_NO_UPDATE_ACCESS');-- reusing the message
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_deliv_rec => l_deliv_rec
);
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => l_return_status
);
UPDATE ams_deliverables_all_b
SET last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id,
object_version_number = l_deliv_rec.object_version_number + 1,
language_code = l_deliv_rec.language_code,
version = l_deliv_rec.version,
application_id = l_deliv_rec.application_id,
-- user_status_id = l_pending_budget_stat_id,
-- status_code = l_pending_budget_stat,
-- status_date = l_deliv_rec.status_date,
active_flag = l_deliv_rec.active_flag,
private_flag = l_deliv_rec.private_flag,
owner_user_id = l_deliv_rec.owner_user_id,
fund_source_id = l_deliv_rec.fund_source_id,
fund_source_type = l_deliv_rec.fund_source_type,
category_type_id = l_deliv_rec.category_type_id,
category_sub_type_id = l_deliv_rec.category_sub_type_id,
kit_flag = l_deliv_rec.kit_flag,
can_fulfill_electronic_flag = l_deliv_rec.can_fulfill_electronic_flag,
can_fulfill_physical_flag = l_deliv_rec.can_fulfill_physical_flag,
jtf_amv_item_id = l_deliv_rec.jtf_amv_item_id,
inventory_flag = l_deliv_rec.inventory_flag,
transaction_currency_code = l_deliv_rec.transaction_currency_code, /* because defaulted to l_deliv_rec.currency_code if transaction currency is null*/
functional_currency_code = l_deliv_rec.functional_currency_code,
budget_amount_tc = l_deliv_rec.budget_amount_tc,
budget_amount_fc = l_deliv_rec.budget_amount_fc,
actual_avail_from_date = l_deliv_rec.actual_avail_from_date,
actual_avail_to_date = l_deliv_rec.actual_avail_to_date,
forecasted_complete_date = l_deliv_rec.forecasted_complete_date,
actual_complete_date = l_deliv_rec.actual_complete_date,
replaced_by_deliverable_id = l_deliv_rec.replaced_by_deliverable_id,
inventory_item_id = l_deliv_rec.inventory_item_id,
inventory_item_org_id = l_deliv_rec.inventory_item_org_id,
pricelist_header_id = l_deliv_rec.pricelist_header_id,
pricelist_line_id = l_deliv_rec.pricelist_line_id,
non_inv_ctrl_code = l_deliv_rec.non_inv_ctrl_code,
non_inv_quantity_on_hand = l_deliv_rec.non_inv_quantity_on_hand,
non_inv_quantity_on_order = l_deliv_rec.non_inv_quantity_on_order,
non_inv_quantity_on_reserve = l_deliv_rec.non_inv_quantity_on_reserve,
chargeback_amount = l_deliv_rec.chargeback_amount,
chargeback_amount_curr_code = l_deliv_rec.chargeback_amount_curr_code,
deliverable_code = l_deliv_rec.deliverable_code,
deliverable_pick_flag = l_deliv_rec.deliverable_pick_flag,
currency_code = l_deliv_rec.currency_code,
forecasted_cost = l_deliv_rec.forecasted_cost,
actual_cost = l_deliv_rec.actual_cost,
forecasted_responses = l_deliv_rec.forecasted_responses,
actual_responses = l_deliv_rec.actual_responses,
country = l_deliv_rec.country,
attribute_category = l_deliv_rec.attribute_category,
attribute1 = l_deliv_rec.attribute1,
attribute2 = l_deliv_rec.attribute2,
attribute3 = l_deliv_rec.attribute3,
attribute4 = l_deliv_rec.attribute4,
attribute5 = l_deliv_rec.attribute5,
attribute6 = l_deliv_rec.attribute6,
attribute7 = l_deliv_rec.attribute7,
attribute8 = l_deliv_rec.attribute8,
attribute9 = l_deliv_rec.attribute9,
attribute10 = l_deliv_rec.attribute10,
attribute11 = l_deliv_rec.attribute11,
attribute12 = l_deliv_rec.attribute12,
attribute13 = l_deliv_rec.attribute13,
attribute14 = l_deliv_rec.attribute14,
attribute15 = l_deliv_rec.attribute15,
chargeback_uom = l_deliv_rec.chargeback_uom,
deliverable_calendar = l_deliv_rec.deliverable_calendar,
start_period_name = l_deliv_rec.start_period_name,
end_period_name = l_deliv_rec.end_period_name,
email_content_type = l_deliv_rec.email_content_type
WHERE deliverable_id = l_deliv_rec.deliverable_id
AND object_version_number = l_deliv_rec.object_version_number;
ams_utility_pvt.debug_message (l_full_name ||' end of update 1 ');
UPDATE ams_deliverables_all_tl
SET deliverable_name = l_deliv_rec.deliverable_name,
description = l_deliv_rec.description,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id,
source_lang = USERENV('LANG')
WHERE deliverable_id = l_deliv_rec.deliverable_id
AND USERENV('LANG') IN (language, source_lang);
AMS_Access_PVT.update_object_owner
( p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => p_validation_level
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_object_type => 'DELV'
,p_object_id => l_deliv_Rec.deliverable_id
,p_resource_id => l_deliv_rec.owner_user_id
,p_old_resource_id => l_owner_user_id
);
AMS_DeliverableRules_PVT.update_delv_status
(
p_deliverable_id => l_deliv_rec.deliverable_id
,p_user_status_id => l_deliv_rec.user_status_id
);
ams_utility_pvt.debug_message (l_full_name ||' end of update 2 ');
ROLLBACK TO update_deliverable;
ROLLBACK TO update_deliverable;
ROLLBACK TO update_deliverable;
ROLLBACK TO update_deliverable;
ROLLBACK TO update_deliverable;
END update_deliverable;
AND p_validation_mode = JTF_PLSQL_API.g_update
THEN
check_deliv_record(
p_deliv_rec => p_deliv_rec,
p_complete_rec => p_deliv_rec,
x_return_status => l_return_status
);
SELECT 'N'
FROM ams_deliverables_vl
WHERE deliverable_name = del_nm
AND version = del_ver;
SELECT 'N'
FROM ams_deliverables_vl
WHERE deliverable_name = del_nm
AND version = del_ver
AND deliverable_id <> del_id;
AMS_Utility_PVT.debug_message(': start of check update ');
IF p_validation_mode = JTF_PLSQL_API.g_update
THEN
l_uniq_flag_u := 'Y';
SELECT can_fulfill_electronic_flag
,can_fulfill_physical_flag
,inventory_flag
,non_inv_quantity_on_hand
,kit_flag
,status_code
FROM ams_deliverables_all_b
WHERE deliverable_id = delivId;
IS select count(*)
from ams_deliv_kit_items
where deliverable_kit_id = l_deliverable_id;
IS select count(*)
from ams_deliv_kit_items
where deliverable_kit_part_id = l_deliverable_id;
SELECT 1
FROM dual
WHERE EXISTS (
SELECT 1
FROM gl_periods_v
WHERE period_set_name = p_deliv_rec.deliverable_calendar
AND period_name = l_name)
;
SELECT 1
FROM dual
WHERE EXISTS (SELECT 1
FROM gl_periods_v
WHERE period_set_name = l_calendar_name);
SELECT start_date, end_date
FROM gl_periods_v
WHERE period_set_name = p_deliv_rec.deliverable_calendar
AND period_name = l_name;
x_deliv_rec.last_update_date := FND_API.g_miss_date;
x_deliv_rec.last_updated_by := FND_API.g_miss_num;
x_deliv_rec.last_update_login := FND_API.g_miss_num;
SELECT *
FROM ams_deliverables_vl
WHERE deliverable_id = p_deliv_rec.deliverable_id;
SELECT DISTINCT master_object_id,
master_object_type
FROM ams_object_associations
WHERE using_object_type = l_using_object_type
AND using_object_id = p_deliverable_id;
SELECT owner_user_id ,campaign_name
FROM ams_campaigns_vl
WHERE campaign_id = l_master_object_id;
SELECT owner_user_id, event_header_name
FROM ams_event_headers_vl
WHERE event_header_id = l_master_object_id;
SELECT owner_user_id, event_offer_name
FROM ams_event_offers_vl
WHERE event_offer_id = l_master_object_id;
IS SELECT start_period_name
,end_period_name
,deliverable_calendar
FROM ams_deliverables_all_b
WHERE deliverable_id = deliv_id;
SELECT owner_user_id
FROM ams_deliverables_all_b
WHERE deliverable_id = deliv_id;
SELECT currency_code
FROM ams_deliverables_all_b
WHERE deliverable_id = deliv_id;
SELECT 'Y'
FROM ams_act_budgets
WHERE arc_act_budget_used_by='DELV'
AND act_budget_used_by_id = deliv_id;
SELECT attr_available_flag
FROM ams_custom_setup_attr
WHERE custom_setup_id = p_custom_setup_id
AND object_attribute = p_approval_type ;
SELECT active_flag
FROM ams_deliverables_all_b
WHERE deliverable_id = deliv_id;
SELECT collateral_flag
,costing_enabled_flag
,customer_order_flag
,customer_order_enabled_flag
,shippable_item_flag
FROM mtl_system_items_b
WHERE inventory_item_id = inv_id
AND organization_id = org_id;