[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM ozf_funds_all_b
WHERE fund_type <> 'QUOTA'
AND fund_id = p_fund_id;
SELECT fund_id
FROM ozf_funds_all_b
WHERE fund_type <> 'QUOTA'
AND fund_number = p_fund_number;
SELECT min(custom_setup_id)
FROM ams_custom_setups_vl
WHERE object_type = 'FUND'
AND application_id = 682
AND activity_type_code = p_fund_type;
SELECT 1
FROM ams_custom_setups_vl
WHERE object_type = 'FUND'
AND application_id = 682
AND activity_type_code = p_fund_type
AND custom_setup_id = p_cust_setup_id;
SELECT 1
FROM ozf_funds_all_vl
WHERE fund_type <> 'QUOTA'
AND fund_id = p_par_fund_id
AND short_name = p_par_fund_name;
SELECT 1
FROM ozf_funds_all_b
WHERE fund_type <> 'QUOTA'
AND fund_id = p_par_fund_id;
SELECT fund_id
FROM ozf_funds_all_vl
WHERE fund_type <> 'QUOTA'
AND short_name = p_par_fund_name;
SELECT 1
FROM ams_categories_vl
WHERE arc_category_created_for = 'FUND'
AND enabled_flag = 'Y'
AND category_name = p_category_name
AND category_id = p_category_id;
SELECT 1
FROM ams_categories_vl
WHERE arc_category_created_for = 'FUND'
AND enabled_flag = 'Y'
AND category_id = p_category_id;
SELECT category_id
FROM ams_categories_vl
WHERE arc_category_created_for = 'FUND'
AND enabled_flag = 'Y'
AND category_name = p_category_name;
SELECT 1
FROM ozf_thresholds_vl
WHERE threshold_type = 'BUDGET'
AND end_date_active > sysdate
AND name = p_threshold_name
AND threshold_id = p_threshold_id;
SELECT 1
FROM ozf_thresholds_vl
WHERE threshold_type = 'BUDGET'
AND end_date_active > sysdate
AND threshold_id = p_threshold_id;
SELECT threshold_id
FROM ozf_thresholds_vl
WHERE threshold_type = 'BUDGET'
AND end_date_active > sysdate
AND name = p_threshold_name;
SELECT 1
FROM ams_media_vl
WHERE media_type_code = 'DEAL'
AND media_name = p_task_name
AND media_id = p_task_id;
SELECT 1
FROM ams_media_vl
WHERE media_type_code = 'DEAL'
AND media_id = p_task_id;
SELECT media_id
FROM ams_media_vl
WHERE media_type_code = 'DEAL'
AND media_name = p_task_name;
SELECT 1
FROM hr_all_organization_units
WHERE business_group_id
IN (SELECT business_group_id
FROM hr_all_organization_units
WHERE organization_id = p_org_id
AND NVL(date_from, SYSDATE) <= SYSDATE
AND NVL(date_to, SYSDATE) >= SYSDATE)
AND type = 'BU' AND NVL(date_from, SYSDATE) <= SYSDATE
AND NVL(date_to, SYSDATE) >= SYSDATE
AND name = p_bus_name
AND organization_id = p_bus_id;
SELECT 1
FROM hr_all_organization_units
WHERE business_group_id
IN (SELECT business_group_id
FROM hr_all_organization_units
WHERE organization_id = p_org_id
AND NVL(date_from, SYSDATE) <= SYSDATE
AND NVL(date_to, SYSDATE) >= SYSDATE)
AND type = 'BU' AND NVL(date_from, SYSDATE) <= SYSDATE
AND NVL(date_to, SYSDATE) >= SYSDATE
AND organization_id = p_bus_id;
SELECT organization_id
FROM hr_all_organization_units
WHERE business_group_id
IN (SELECT business_group_id
FROM hr_all_organization_units
WHERE organization_id = p_org_id
AND NVL(date_from, SYSDATE) <= SYSDATE
AND NVL(date_to, SYSDATE) >= SYSDATE)
AND type = 'BU' AND NVL(date_from, SYSDATE) <= SYSDATE
AND NVL(date_to, SYSDATE) >= SYSDATE
AND name = p_bus_name;
SELECT user_status_id
FROM ams_user_statuses_vl
WHERE system_status_type = 'OZF_FUND_STATUS'
AND system_status_code = p_status_code
AND enabled_flag ='Y';
SELECT 1
FROM gl_ledgers_public_v
WHERE ledger_id = p_ledger_id
AND name = p_ledger_name;
SELECT 1
FROM gl_ledgers_public_v
WHERE ledger_id = p_ledger_id;
SELECT ledger_id
FROM gl_ledgers_public_v
WHERE name = p_ledger_name;
SELECT plan_id
FROM ozf_funds_all_b
WHERE fund_id = p_fund_id;
l_modifier_list_rec.modifier_operation := 'UPDATE';
l_modifier_list_rec.offer_operation := 'UPDATE';
PROCEDURE Delete_Fund(
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_fund_id IN NUMBER
,p_object_version IN NUMBER
)
IS
l_dependent_object_tbl ams_utility_pvt.dependent_objects_tbl_type;
l_api_name VARCHAR(30) := 'Delete_Fund';
SAVEPOINT Delete_Fund_PUB;
OZF_Fund_Extension_Pvt.delete_fund(p_api_version_number => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_object_id => p_fund_id
,p_object_version_number => p_object_version
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
ROLLBACK TO Delete_Fund_PUB;
ROLLBACK TO Delete_Fund_PUB;
ROLLBACK TO Delete_Fund_PUB;
END Delete_Fund;
PROCEDURE Update_fund(
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_fund_rec IN fund_rec_type
,p_modifier_list_rec IN ozf_offer_pub.modifier_list_rec_type
,p_modifier_line_tbl IN ozf_offer_pub.modifier_line_tbl_type
,p_vo_pbh_tbl IN ozf_offer_pub.vo_disc_tbl_type
,p_vo_dis_tbl IN ozf_offer_pub.vo_disc_tbl_type
,p_vo_prod_tbl IN ozf_offer_pub.vo_prod_tbl_type
,p_qualifier_tbl IN ozf_offer_pub.qualifiers_tbl_type
,p_vo_mo_tbl IN ozf_offer_pub.vo_mo_tbl_type
)
IS
l_api_name VARCHAR(30) := 'Update_Fund';
l_mode VARCHAR2(6) := 'UPDATE';
SELECT fund_id
FROM ozf_funds_all_b
WHERE fund_number = p_fund_number;
SELECT fund_number, short_name, fund_type, custom_setup_id, description, parent_fund_id, category_id,
business_unit_id, status_code, start_date_active, end_date_active, start_period_name,
end_period_name, original_budget, holdback_amt, currency_code_tc, owner, accrual_basis,
accrual_phase, accrual_discount_level, threshold_id, task_id, liability_flag,
accrued_liable_account, ded_adjustment_account, product_spread_time_id, object_version_number,
org_id, ledger_id
FROM ozf_funds_all_vl
WHERE fund_id = p_fund_id;
SAVEPOINT Update_Fund_PUB;
ozf_funds_pvt.update_fund(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,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_fund_rec => l_pvt_fund_rec
,p_mode => jtf_plsql_api.g_update
);
ROLLBACK TO Update_Fund_PUB;
ROLLBACK TO Update_Fund_PUB;
ROLLBACK TO Update_Fund_PUB;
END Update_Fund;
SELECT 1
FROM ams_act_market_segments
WHERE activity_market_segment_id = p_activity_market_segment_id;
SELECT 1
FROM ozf_funds_all_b
WHERE fund_id = p_segment_used_by_id;
SELECT 1
FROM ozf_lookups
WHERE lookup_type = 'OZF_OFFER_DEAL_CUSTOMER_TYPES'
AND enabled_flag = 'Y'
AND lookup_code = p_segment;
SELECT 1
FROM ams_party_market_segments ams, hz_parties hz
WHERE ams.market_qualifier_type = 'BG'
AND ams.market_qualifier_reference = hz.party_id
AND ams.market_qualifier_reference = ams.party_id
AND EXISTS
( SELECT 1
FROM ams_party_market_segments
WHERE market_qualifier_type = 'BG'
AND market_qualifier_reference = ams.market_qualifier_reference
AND market_qualifier_reference <> party_id)
AND hz.party_id = p_segment_id;
SELECT 1
FROM qp_customers_v
WHERE customer_id = p_segment_id;
SELECT 1
FROM oe_invoice_to_orgs_v oito,hz_cust_accounts cust_acct,hz_parties party
WHERE cust_acct.party_id = party.party_id
AND oito.customer_id = cust_acct.cust_account_id
AND oito.organization_id = p_segment_id;
SELECT 1
FROM ams_list_headers_all list, ams_list_headers_all_tl tl
WHERE list.list_header_id = tl.list_header_id
AND userenv('LANG') = language
AND status_code in ( 'AVAILABLE','LOCKED','EXECUTED','EXECUTING','VALIDATED','VALIDATING')
AND list.list_header_id = p_segment_id;
SELECT 1
FROM ams_cells_all_b cell, ams_cells_all_tl tl
WHERE cell.cell_id = tl.cell_id
AND userenv('LANG') = language
AND cell.status_code = 'AVAILABLE'
AND cell.cell_id = p_segment_id;
SELECT 1
FROM qp_ship_to_orgs_v
WHERE organization_id = p_segment_id;
SELECT 1
FROM jtf_terr_qtype_usgs jtqu, jtf_terr jt, jtf_qual_type_usgs jqtu
WHERE ( TRUNC(jt.start_date_active) <= TRUNC(SYSDATE)
AND ( TRUNC(jt.end_date_active) >= TRUNC(SYSDATE) OR jt.end_date_active IS NULL ))
AND jt.terr_id = jtqu.terr_id
AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
AND jqtu.source_id = -1003
AND jqtu.qual_type_id = -1007
AND jt.terr_id = p_segment_id;
PROCEDURE update_market_segment(
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
,p_mks_rec IN mks_rec_type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_name VARCHAR(30) := 'update_market_segment';
l_mode VARCHAR2(6) := 'UPDATE';
SELECT act_market_segment_used_by_id, segment_type, market_segment_id,
object_version_number, exclude_flag
FROM ams_act_market_segments
WHERE activity_market_segment_id = p_act_mkt_seg_id;
SAVEPOINT update_market_pub;
ams_act_market_segments_pvt.update_market_segments(p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,p_commit => l_commit
,p_validation_level => l_validation_level
,p_mks_rec => l_seg_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO update_market_pub;
ROLLBACK TO update_market_pub;
ROLLBACK TO update_market_pub;
END update_market_segment;
* This procedure deletes a market segment for an existing fund.
* @param p_api_version Indicates the version of the API
* @param p_init_msg_list Indicates whether to initialize the message stack
* @param p_commit Indicates whether to commit within the program
* @param p_act_mks_id Market segment identifier of the market segment to be deleted
* @param x_return_status Status of the program
* @param x_msg_count Number of the messages returned by the program
* @param x_msg_data Return message by the program
* @rep:scope public
* @rep:lifecycle active
* @rep:displayname Delete Market Segment
* @rep:compatibility S
* @rep:businessevent None
*/
PROCEDURE delete_market_segment(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_act_mks_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_name VARCHAR(30) := 'delete_market_segment';
SELECT object_version_number
FROM ams_act_market_segments
WHERE activity_market_segment_id = p_act_mks_id;
SAVEPOINT delete_market_pub;
ams_act_market_segments_pvt.delete_market_segments(p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,p_commit => l_commit
,p_act_mks_id => p_act_mks_id
,p_object_version => l_object_version
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO delete_market_pub;
ROLLBACK TO delete_market_pub;
ROLLBACK TO delete_market_pub;
END delete_market_segment;
SELECT 1
FROM ams_act_products
WHERE activity_product_id = p_activity_product_id;
SELECT 1
FROM ozf_funds_all_b
WHERE fund_id = p_prod_used_by;
SELECT org_id
FROM ozf_funds_all_b
WHERE fund_id = p_prod_used_by;
SELECT 1
FROM mtl_system_items_b_kfv
WHERE organization_id = p_org_id
AND inventory_item_id = p_inventory_id;
SELECT inventory_item_id
FROM mtl_system_items_b_kfv
WHERE organization_id = p_org_id
AND concatenated_segments = p_inventory_name;
SELECT category_set_id
FROM ENI_PROD_DEN_HRCHY_PARENTS_V
WHERE category_id = p_category_id;
SELECT category_id, category_set_id
FROM ENI_PROD_DEN_HRCHY_PARENTS_V
WHERE category_desc = p_category_name
AND NVL(category_id, 0) = category_id;
PROCEDURE update_product_eligibility(
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
,p_act_product_rec IN act_product_rec_type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name VARCHAR(30) := 'update_product_eligibility';
l_mode VARCHAR2(6) := 'UPDATE';
SELECT act_product_used_by_id, inventory_item_id, category_id, category_set_id,
primary_product_flag, excluded_flag, object_version_number
FROM ams_act_products
WHERE activity_product_id = p_act_prod_id;
SAVEPOINT update_product_pub;
ams_actproduct_pvt.update_act_product(p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,p_commit => l_commit
,p_validation_level => l_validation_level
,p_act_product_rec => l_act_product_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO update_product_pub;
ROLLBACK TO update_product_pub;
ROLLBACK TO update_product_pub;
END update_product_eligibility;
PROCEDURE delete_product_eligibility(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_act_product_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name VARCHAR(30) := 'delete_product_eligibility';
SELECT object_version_number
FROM ams_act_products
WHERE activity_product_id = p_act_product_id;
SAVEPOINT delete_product_pub;
ams_actproduct_pvt.delete_act_product(p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,p_commit => l_commit
,p_act_product_id => p_act_product_id
,p_object_version => l_object_version
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO delete_product_pub;
ROLLBACK TO delete_product_pub;
ROLLBACK TO delete_product_pub;
END delete_product_eligibility;
l_insert_xref VARCHAR2(1);
SELECT plan_id
FROM ozf_funds_all_b
WHERE fund_id = p_fund_id;
SELECT fund_number,
short_name,
fund_type,
description,
parent_fund_id,
business_unit_id,
status_code,
start_date_active,
end_date_active,
start_period_name,
end_period_name,
original_budget,
holdback_amt,
currency_code_tc,
owner,
accrual_basis,
accrual_phase,
accrual_discount_level,
threshold_id,
task_id,
accrued_liable_account,
ded_adjustment_account,
product_spread_time_id,
object_version_number,
org_id,
liability_flag
FROM ozf_funds_all_vl
WHERE fund_id = p_fund_id;
SELECT activity_market_segment_id,
object_version_number
FROM ams_act_market_segments
WHERE act_market_segment_used_by_id = p_fund_id
AND arc_act_market_segment_used_by = 'FUND';
SELECT object_version_number
FROM AMS_ACT_PRODUCTS
WHERE inventory_item_id = p_inventory_item_id
AND category_id = p_category_id
AND act_product_used_by_id = p_fund_id
AND arc_act_product_used_by = p_act_product_used_by
AND level_type_code = p_level_type_code;
SELECT NVL(map.xref_line_id_value,line.list_line_id) list_line_id ,
list_line_no
FROM qp_list_lines line,
ozf_funds_all_b,
ozf_xref_map map
WHERE fund_id = p_fund_id
AND list_header_id = plan_id
AND line.list_line_id = map.list_line_id(+);
SELECT list_line_no
FROM qp_list_lines
WHERE list_header_id = p_list_header_id;
SELECT jtf.resource_id
FROM JTF_RS_RESOURCE_EXTNS jtf,fnd_user fnd
WHERE fnd.user_name = cv_source
AND fnd.user_id = jtf.user_id;
SELECT primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = p_product_id
AND organization_id = p_org_id;
SELECT list_line_id
FROM ozf_xref_map
WHERE xref_line_id_value = p_list_line_id;
SELECT ledger_id
,accrued_liability_account
,ded_adjustment_account
FROM AMS_CATEGORIES_B
WHERE category_id = 851;
SELECT set_of_books_id
FROM ozf_sys_parameters;
SELECT set_of_books_id
FROM hr_operating_units
WHERE organization_id = fnd_profile.value('DEFAULT_ORG_ID');
SELECT COUNT(1)
FROM gl_ledgers_public_v
WHERE ledger_id = cv_ledger_id;
SELECT COUNT(1)
FROM gl_code_combinations
WHERE code_combination_id = cv_code_combi_id
AND enabled_flag ='Y';
SELECT fund_number
FROM ozf_funds_All_b
WHERE fund_number = cv_fund_number;
SELECT threshold_id
FROM ozf_thresholds_vl
WHERE threshold_type = 'BUDGET'
AND end_date_active > SYSDATE
AND threshold_id = cv_threshold_id;
SELECT 1
FROM hr_all_organization_units
WHERE business_group_id
IN (SELECT business_group_id
FROM hr_all_organization_units
WHERE organization_id = p_org_id
AND NVL(date_from, SYSDATE) <= SYSDATE
AND NVL(date_to, SYSDATE) >= SYSDATE)
AND type = 'BU' AND NVL(date_from, SYSDATE) <= SYSDATE
AND NVL(date_to, SYSDATE) >= SYSDATE
AND organization_id = p_bus_id;
SELECT currency_code
FROM fnd_currencies
WHERE currency_code = p_currency_code
AND enabled_flag='Y';
l_mode := 'UPDATE';
l_modifier_list_rec.modifier_operation := 'UPDATE';
l_modifier_list_rec.offer_operation := 'UPDATE';
OZF_OFFER_PVT.update_offer_status
(
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_modifier_list_rec => l_offer_hdr_rec
);
--//OZF_ACT_BUDGETS table needs to be populated when the Accrual fund created in ACTIVE status. This code flow is same as Budget Approval flow in Update_fund procedure
l_pvt_fund_rec2.fund_id := x_fund_id;
INSERT INTO ozf_xref_map ( map_attr_id
, list_line_id
, list_line_number
, xref_line_id_value
, xref_line_number_value
)
VALUES ( x_fund_id
, list_line_rec.list_line_id
, list_line_rec.list_line_no
, x_line_tbl(k).list_line_id
, x_line_tbl(k).list_line_number
);
ELSE -- Update mode
OZF_FUNDS_PVT.update_fund(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,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_fund_rec => l_pvt_fund_rec
,p_mode => jtf_plsql_api.g_update
);
AMS_ACT_MARKET_SEGMENTS_PVT.update_market_segments(
p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,p_commit => l_commit
,p_validation_level => l_validation_level
,p_mks_rec => l_seg_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
l_modifier_list_rec.modifier_operation := 'UPDATE';
l_modifier_list_rec.offer_operation := 'UPDATE';
l_modifier_line_tbl(i).OPERATION := 'UPDATE';
IF l_modifier_line_tbl(i).OPERATION = 'UPDATE' THEN
l_xref_line_id := NULL;
l_qualifier_tbl.DELETE;
l_insert_xref := 'Y';
l_insert_xref := 'N';
IF l_insert_xref = 'Y' THEN
x_line_tbl(k).list_line_number := list_line_rec.list_line_no;
INSERT INTO ozf_xref_map ( map_attr_id
, list_line_id
, list_line_number
, xref_line_id_value
, xref_line_number_value
)
VALUES ( l_fund_rec.fund_id
, list_line_rec.list_line_id
, list_line_rec.list_line_no
, x_line_tbl(k).list_line_id
, x_line_tbl(k).list_line_number
);
END IF; --IF l_insert_xref = 'Y' THEN