[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_resource_tbl.delete;
x_res_tbl.delete;
SELECT 'Y'
FROM jtf_rs_group_members mem
WHERE mem.group_id = l_group_id
AND mem.resource_id = l_resource_id
AND nvl(mem.delete_flag , 'N') <> 'Y';
x_assign_resources_tbl.delete;
SELECT 'Y'
FROM jtf_rs_group_usages
WHERE group_id = l_group_id
AND usage = l_usage;
SELECT 'Y'
FROM jtf_rs_team_usages
WHERE team_id = l_team_id
AND usage = l_usage;
x_assign_resources_tbl.delete;
l_resources_tbl.delete;
x_assign_resources_tbl.delete;
l_resources_tbl.delete;
JTF_ASSIGN_PUB.g_resource_avail.delete;
jtf_assign_pub.g_resource_avail.delete;
x_assign_resources_tbl.delete;
JTF_ASSIGN_PUB.g_resource_avail.delete;
x_assign_resources_tbl.delete;
SELECT application_id
FROM fnd_application
WHERE application_short_name = 'OKS'; -- IN ('OKC', 'OKL', 'OKS');
SELECT resource_id,
'RS_'||category category
FROM jtf_rs_resource_extns_vl
WHERE source_id = l_cs_contacts_rsc_id
AND category = l_cs_contacts_rsc_cat;
SELECT group_id resource_id,
'RS_GROUP'
FROM jtf_rs_groups_b
WHERE group_id = l_cs_contacts_rsc_id;
SELECT team_id resource_id,
'RS_TEAM'
FROM jtf_rs_teams_b
WHERE team_id = l_cs_contacts_rsc_id;
SELECT resource_id,
'RS_'||category category
FROM jtf_rs_resource_extns_vl
WHERE source_id = l_cs_contacts_rsc_id
AND category = 'SUPPLIER_CONTACT';
l_dynamic_sql4 := 'SELECT CSI_UTILITY_GRP.ib_active_flag() FROM DUAL';
l_dynamic_sql2 := 'SELECT PARTY_ID resource_id, PARTY_SOURCE_TABLE resource_category, primary_flag, preferred_flag'||
' FROM CSI_I_PARTIES'||
' WHERE INSTANCE_ID = :2'||
-- ' AND PARTY_SOURCE_TABLE in ('''||'EMPLOYEE'||''''||','''||'HZ_PARTIES'||''''||','''||'GROUP'||''')'||
--' AND PREFERRED_FLAG = :4' ;
l_dynamic_sql2 := ' SELECT resource_id, resource_category, primary_flag, preferred_flag '||
' FROM cs_contacts_v'||
' WHERE source_object_code = :1 AND '||
' source_object_id = :2 AND '||
-- ' resource_category = :3 AND '||
' preferred_flag = :4';
p_auto_select_flag IN VARCHAR2,
p_contracts_preferred_engineer IN VARCHAR2,
p_ib_preferred_engineer IN VARCHAR2,
p_effort_duration IN NUMBER,
p_effort_uom IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_territory_flag IN VARCHAR2,
p_calendar_flag IN VARCHAR2,
p_web_availability_flag IN VARCHAR2,
p_task_id IN JTF_TASKS_VL.TASK_ID%TYPE,
p_column_list IN VARCHAR2,
p_business_process_id IN NUMBER,
p_business_process_date IN DATE,
p_filter_excluded_resource IN VARCHAR2,
x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
--Added for Bug # 5573916
p_calendar_check IN VARCHAR2 DEFAULT 'Y'
--Added for Bug # 5573916 Ends here
)
IS
l_return_status_1 VARCHAR2(10);
SELECT source_object_type_code,
source_object_id,
planned_start_date,
planned_end_date,
planned_effort,
planned_effort_uom
FROM jtf_tasks_vl
WHERE task_id = l_task_id;
SELECT resource_id,
'RS_'||category category
FROM jtf_rs_resource_extns_vl
WHERE source_id = l_cs_contacts_rsc_id AND
category = l_cs_contacts_rsc_cat;
SELECT support_site_id
FROM jtf_rs_resource_extns_vl
WHERE resource_id = p_rsc_id AND
category = p_rsc_type;
SELECT resource_id
FROM jtf_rs_web_available_v
WHERE resource_id = p_res_id AND
category = p_res_type;
SELECT contract_service_id,
customer_product_id,
expected_resolution_date
FROM cs_incidents_all_vl
WHERE incident_id = l_task_source_id;
l_dynamic_sql1 := ' SELECT contract_service_id, customer_product_id, expected_resolution_date'||
' FROM cs_incidents_all_vl'||
' WHERE incident_id = :1';
SELECT * INTO l_assign_resources_sr_rec
FROM jtf_terr_srv_task_v -- (cs_sr_task_territory_v)
WHERE task_id = l_task_id AND
service_request_id = l_task_source_id;
l_dynamic_sql := 'SELECT '||
l_column_list||
' FROM cs_sr_task_territory_v'||
' WHERE task_id = :1 AND
service_request_id = :2';
l_dynamic_sql := 'SELECT '||
l_column_list||
' FROM jtf_task_territory_v'||
' WHERE task_id = :1 AND rownum < 2';
l_dynamic_sql3 := ' SELECT a.city city '||
' FROM hz_locations a, hz_party_sites b, hz_party_site_uses c '||
' WHERE c.site_use_type = :1 AND '||
' b.party_site_id = :2 AND '||
' a.location_id = b.location_id AND '||
' c.party_site_id = b.party_site_id ';
p_auto_select_flag IN VARCHAR2,
p_contracts_preferred_engineer IN VARCHAR2,
p_ib_preferred_engineer IN VARCHAR2,
p_contract_id IN NUMBER,
p_customer_product_id IN NUMBER,
p_effort_duration IN NUMBER,
p_effort_uom IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_territory_flag IN VARCHAR2,
p_calendar_flag IN VARCHAR2,
p_web_availability_flag IN VARCHAR2,
p_category_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_inventory_org_id IN NUMBER,
p_problem_code IN VARCHAR2 ,
p_sr_id IN NUMBER,
p_sr_rec IN JTF_TERRITORY_PUB.JTF_Serv_Req_rec_type,
p_sr_task_rec IN JTF_TERRITORY_PUB.JTF_Srv_Task_rec_type,
p_business_process_id IN NUMBER,
p_business_process_date IN DATE,
p_filter_excluded_resource IN VARCHAR2,
x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
--Added for Bug # 5386560
p_inventory_component_id IN NUMBER DEFAULT NULL,
--Added for Bug # 5386560 Ends here
--Added for Bug # 5573916
p_calendar_check IN VARCHAR2 DEFAULT 'Y'
--Added for Bug # 5573916 Ends here
)
IS
l_return_status_1 VARCHAR2(10);
SELECT support_site_id
FROM jtf_rs_resource_extns_vl
WHERE resource_id = p_rsc_id AND
category = p_rsc_type;
SELECT resource_id
FROM jtf_rs_web_available_v
WHERE resource_id = p_res_id AND
category = p_res_type;
SELECT skill_level, level_name
FROM jtf_rs_resource_skills a,
jtf_rs_skill_levels_vl b
WHERE a.skill_level_id = b.skill_level_id AND
a.resource_id = p_res_id AND
(category_id = p_category_id OR category_id IS NULL) AND
product_id = p_inventory_item_id AND
product_org_id = p_inventory_org_id AND
component_id IS NULL AND
subcomponent_id IS NULL;
l_dynamic_sql1 := ' SELECT contract_service_id, customer_product_id, expected_resolution_date'||
' FROM cs_incidents_all_vl'||
' WHERE incident_id = :1';
l_excluded_resource_tbl.delete;
l_contracts_tbl.delete;
l_ib_tbl.delete;
x_assign_resources_tbl.delete;
l_dynamic_sql3 := ' SELECT a.city city '||
' FROM hz_locations a, hz_party_sites b, hz_party_site_uses c '||
' WHERE c.site_use_type = :1 AND '||
' b.party_site_id = :2 AND '||
' a.location_id = b.location_id AND '||
' c.party_site_id = b.party_site_id ';
p_auto_select_flag IN VARCHAR2 ,
p_contracts_preferred_engineer IN VARCHAR2 ,
p_ib_preferred_engineer IN VARCHAR2 ,
p_contract_id IN NUMBER ,
p_customer_product_id IN NUMBER ,
p_effort_duration IN NUMBER ,
p_effort_uom IN VARCHAR2 ,
p_start_date IN DATE ,
p_end_date IN DATE ,
p_territory_flag IN VARCHAR2 ,
p_calendar_flag IN VARCHAR2 ,
p_web_availability_flag IN VARCHAR2 ,
p_category_id IN NUMBER ,
p_inventory_item_id IN NUMBER ,
p_inventory_org_id IN NUMBER ,
p_problem_code IN VARCHAR2 ,
p_dr_id IN NUMBER,
p_column_list IN VARCHAR2 ,
p_dr_rec IN JTF_ASSIGN_PUB.JTF_DR_rec_type ,
p_business_process_id IN NUMBER,
p_business_process_date IN DATE,
p_filter_excluded_resource IN VARCHAR2,
x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
--Added for Bug # 5573916
p_calendar_check IN VARCHAR2 DEFAULT 'Y'
--Added for Bug # 5573916 Ends here
)
IS
l_return_status_1 VARCHAR2(10);
l_auto_select_flag VARCHAR2(1) := p_auto_select_flag;
l_auto_select_profile VARCHAR2(1);
SELECT object_code
FROM jtf_object_usages
WHERE object_user_code = 'RESOURCES' AND
object_code = p_resource_type;
SELECT DECODE(source_postal_code, NULL, '00000', source_postal_code)
FROM jtf_rs_resource_extns_vl
WHERE resource_id = p_rid AND
'RS_'||category = p_rtype;
SELECT 'Y'
FROM jtf_task_resources_vl
where resource_id = l_resource_id
and resource_type = l_resource_type
and nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate);
SELECT support_site_id
FROM jtf_rs_resource_extns_vl
WHERE resource_id = p_rsc_id AND
category = p_rsc_type;
SELECT resource_id
FROM jtf_rs_web_available_v
WHERE resource_id = p_res_id AND
category = p_res_type;
SELECT skill_level, level_name
FROM jtf_rs_resource_skills a,
jtf_rs_skill_levels_vl b
WHERE a.skill_level_id = b.skill_level_id AND
a.resource_id = p_res_id AND
(category_id = p_category_id OR category_id IS NULL) AND
product_id = p_inventory_item_id AND
product_org_id = p_inventory_org_id AND
component_id IS NULL AND
subcomponent_id IS NULL;
l_auto_select_profile := FND_PROFILE.VALUE ( 'ACTIVATE_AUTO_SELECT' );
IF (p_auto_select_flag IS NULL) THEN
l_auto_select_flag := l_auto_select_profile; -- PROFILE VALUE is the default value
l_auto_select_flag := p_auto_select_flag;
l_dynamic_sql1 := ' SELECT contract_service_id, customer_product_id, expected_resolution_date'||
' FROM cs_incidents_all_vl'||
' WHERE incident_id = :1';
l_excluded_resource_tbl.delete;
l_contracts_tbl.delete;
l_ib_tbl.delete;
l_assign_resources_tbl.delete;
l_dynamic_sql3 := ' SELECT a.city city '||
' FROM hz_locations a, hz_party_sites b, hz_party_site_uses c '||
' WHERE c.site_use_type = :1 AND '||
' b.party_site_id = :2 AND '||
' a.location_id = b.location_id AND '||
' c.party_site_id = b.party_site_id ';
IF ( UPPER(l_auto_select_flag) = 'Y' ) THEN
l_no_of_resources := least(nvl(l_assign_resources_tbl.count, 0),l_no_of_resources) ;
ELSE -- Auto Select Flag is NO
WHILE l_current_record <= l_assign_resources_tbl.LAST
LOOP
open check_date_cur(l_assign_resources_tbl(l_current_record).resource_id,
l_assign_resources_tbl(l_current_record).resource_type);
END IF; -- Auto Select Flag
jtf_assign_pub.g_assign_resources_tbl.delete;
SELECT jtf_calendars_s.NEXTVAL INTO l_workflow_key
FROM dual;
p_auto_select_flag IN VARCHAR2 ,
p_effort_duration IN NUMBER ,
p_effort_uom IN VARCHAR2 ,
p_start_date IN DATE ,
p_end_date IN DATE ,
p_territory_flag IN VARCHAR2,
p_calendar_flag IN VARCHAR2,
p_opportunity_rec IN JTF_ASSIGN_PUB.JTF_Oppor_rec_type,
p_business_process_id IN NUMBER,
p_business_process_date IN DATE,
x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_return_status_1 VARCHAR2(10);
l_auto_select_profile VARCHAR2(03);
l_auto_select_flag VARCHAR2(03);
SELECT 'Y'
--FROM jtf_rs_all_resources_vl
FROM jtf_task_resources_vl
where resource_id = l_resource_id
and resource_type = l_resource_type
and nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate);
/* Getting the Auto Select Profile value defined for the Assignment Manager */
--l_auto_select_profile := FND_PROFILE.VALUE_SPECIFIC ( 'ACTIVATE_AUTO_SELECT' ); -- Commented out by SBARAT on 12/10/2004, Bug-3830061
l_auto_select_profile := FND_PROFILE.VALUE ( 'ACTIVATE_AUTO_SELECT' ); -- Added by SBARAT on 12/10/2004, Bug-3830061
/* Assigning the DEFAULT value to the Auto Select Parameter */
IF (p_auto_select_flag IS NULL) THEN
l_auto_select_flag := l_auto_select_profile;
l_auto_select_flag := p_auto_select_flag;
l_dynamic_sql := 'SELECT '||
l_column_list||
' FROM jtf_terr_opportunities_v'||
' WHERE lead_id = :1';
IF(l_auto_select_flag = 'Y')
THEN
l_temp_table.delete;
x_assign_resources_tbl.delete;
END IF; -- end of auto select flag
jtf_assign_pub.g_assign_resources_tbl.delete;
x_assign_resources_tbl.delete;
SELECT jtf_calendars_s.NEXTVAL INTO l_workflow_key
FROM dual;
p_auto_select_flag IN VARCHAR2 ,
p_effort_duration IN NUMBER ,
p_effort_uom IN VARCHAR2 ,
p_start_date IN DATE ,
p_end_date IN DATE ,
p_territory_flag IN VARCHAR2,
p_calendar_flag IN VARCHAR2,
p_lead_rec IN JTF_TERRITORY_PUB.JTF_Lead_BULK_rec_type,
-- x_assign_resources_bulk_rec OUT NOCOPY JTF_TERRITORY_PUB.WINNING_BULK_REC_TYPE,
p_business_process_id IN NUMBER,
p_business_process_date IN DATE,
x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_return_status_1 VARCHAR2(10);
l_auto_select_profile VARCHAR2(03);
l_auto_select_flag VARCHAR2(03);
SELECT 'Y'
--FROM jtf_rs_all_resources_vl
FROM jtf_task_resources_vl
where resource_id = l_resource_id
and resource_type = l_resource_type
and nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate);
/* Getting the Auto Select Profile value defined for the Assignment Manager */
--l_auto_select_profile := FND_PROFILE.VALUE_SPECIFIC ( 'ACTIVATE_AUTO_SELECT' ); -- Commented out by SBARAT on 12/10/2004, Bug-3830061
l_auto_select_profile := FND_PROFILE.VALUE ( 'ACTIVATE_AUTO_SELECT' ); -- Added by SBARAT on 12/10/2004, Bug-3830061
/* Assigning the DEFAULT value to the Auto Select Parameter */
IF (p_auto_select_flag IS NULL) THEN
l_auto_select_flag := l_auto_select_profile;
l_auto_select_flag := p_auto_select_flag;
IF(l_auto_select_flag = 'Y')
THEN
l_temp_table.delete;
x_assign_resources_tbl.delete;
END IF; -- end of auto select flag
jtf_assign_pub.g_assign_resources_tbl.delete;
x_assign_resources_tbl.delete;
SELECT jtf_calendars_s.NEXTVAL INTO l_workflow_key
FROM dual;
p_auto_select_flag IN VARCHAR2 ,
p_effort_duration IN NUMBER ,
p_effort_uom IN VARCHAR2 ,
p_start_date IN DATE ,
p_end_date IN DATE ,
p_territory_flag IN VARCHAR2 ,
p_calendar_flag IN VARCHAR2 ,
p_lead_rec IN JTF_ASSIGN_PUB.JTF_Lead_rec_type,
p_business_process_id IN NUMBER,
p_business_process_date IN DATE,
x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_return_status_1 VARCHAR2(10);
l_auto_select_profile VARCHAR2(03);
l_auto_select_flag VARCHAR2(03);
/* Getting the Auto Select Profile value defined for the Assignment Manager */
--l_auto_select_profile := FND_PROFILE.VALUE_SPECIFIC ( 'ACTIVATE_AUTO_SELECT' ); -- Commented out by SBARAT on 12/10/2004, Bug-3830061
l_auto_select_profile := FND_PROFILE.VALUE ( 'ACTIVATE_AUTO_SELECT' ); -- Added by SBARAT on 12/10/2004, Bug-3830061
/* Assigning the DEFAULT value to the Auto Select Parameter */
IF (p_auto_select_flag IS NULL) THEN
l_auto_select_flag := l_auto_select_profile;
l_auto_select_flag := p_auto_select_flag;
IF (l_auto_select_flag = 'Y') THEN
l_total_records := p_no_of_resources;
jtf_assign_pub.g_assign_resources_tbl.delete;
x_assign_resources_tbl.delete;
SELECT jtf_calendars_s.NEXTVAL INTO l_workflow_key
FROM dual;
p_auto_select_flag IN VARCHAR2 ,
p_effort_duration IN NUMBER ,
p_effort_uom IN VARCHAR2 ,
p_start_date IN DATE ,
p_end_date IN DATE ,
p_territory_flag IN VARCHAR2,
p_calendar_flag IN VARCHAR2 ,
p_account_rec IN JTF_ASSIGN_PUB.JTF_Account_rec_type,
p_business_process_id IN NUMBER,
p_business_process_date IN DATE,
x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_return_status_1 VARCHAR2(10);
l_auto_select_profile VARCHAR2(03);
l_auto_select_flag VARCHAR2(03);
SELECT 'Y'
--FROM jtf_rs_all_resources_vl
FROM jtf_task_resources_vl
where resource_id = l_resource_id
and resource_type = l_resource_type
and nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate);
/* Getting the Auto Select Profile value defined for the Assignment Manager */
--l_auto_select_profile := FND_PROFILE.VALUE_SPECIFIC ( 'ACTIVATE_AUTO_SELECT' ); -- Commented out by SBARAT on 12/10/2004, Bug-3830061
l_auto_select_profile := FND_PROFILE.VALUE ( 'ACTIVATE_AUTO_SELECT' ); -- Added by SBARAT on 12/10/2004, Bug-3830061
/* Assigning the DEFAULT value to the Auto Select Parameter */
IF (p_auto_select_flag IS NULL) THEN
l_auto_select_flag := l_auto_select_profile;
l_auto_select_flag := p_auto_select_flag;
IF (l_auto_select_flag = 'Y') THEN
l_total_records := p_no_of_resources;
IF(l_auto_select_flag = 'Y')
THEN
l_temp_table.delete;
x_assign_resources_tbl.delete;
END IF; -- end of auto select flag
jtf_assign_pub.g_assign_resources_tbl.delete;
x_assign_resources_tbl.delete;
SELECT jtf_calendars_s.NEXTVAL INTO l_workflow_key
FROM dual;
p_auto_select_flag IN VARCHAR2,
p_effort_duration IN NUMBER,
p_effort_uom IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_territory_flag IN VARCHAR2,
p_calendar_flag IN VARCHAR2,
p_defect_rec IN JTF_TERRITORY_PUB.JTF_DEF_MGMT_rec_type,
p_business_process_id IN NUMBER,
p_business_process_date IN DATE,
x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_return_status_1 VARCHAR2(10);
x_assign_resources_tbl.delete;
IF (p_auto_select_flag = 'Y') THEN
l_total_records := p_no_of_resources;
p_auto_select_flag IN VARCHAR2 ,
p_effort_duration IN NUMBER ,
p_effort_uom IN VARCHAR2 ,
p_start_date IN DATE ,
p_end_date IN DATE ,
p_territory_flag IN VARCHAR2 ,
p_calendar_flag IN VARCHAR2 ,
p_web_availability_flag IN VARCHAR2 ,
p_esc_tbl IN JTF_ASSIGN_PUB.Escalations_tbl_type,
p_business_process_id IN NUMBER,
p_business_process_date IN DATE,
x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_return_status_1 VARCHAR2(10);
l_auto_select_profile VARCHAR2(03);
l_auto_select_flag VARCHAR2(03);
SELECT source_object_type_code,
source_object_id,
planned_start_date,
planned_end_date,
planned_effort,
planned_effort_uom
FROM jtf_tasks_vl
WHERE task_id = l_esc_id;
SELECT reference_code,
object_type_code,
object_id
FROM jtf_task_references_vl
WHERE task_id = l_esc_id;
SELECT owner_territory_id
FROM jtf_tasks_vl
WHERE task_id = l_object_id;
SELECT support_site_id
FROM jtf_rs_resource_extns_vl
WHERE resource_id = p_rsc_id AND
category = p_rsc_type;
SELECT resource_id
FROM jtf_rs_web_available_v
WHERE resource_id = p_res_id AND
category = p_res_type;
SELECT 'Y'
--FROM jtf_rs_all_resources_vl
FROM jtf_task_resources_vl
where resource_id = l_resource_id
and resource_type = l_resource_type
and nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate);
/* Getting the Auto Select Profile value defined for the Assignment Manager */
--l_auto_select_profile := FND_PROFILE.VALUE_SPECIFIC ( 'ACTIVATE_AUTO_SELECT' ); -- Commented out by SBARAT on 12/10/2004, Bug-3830061
l_auto_select_profile := FND_PROFILE.VALUE ( 'ACTIVATE_AUTO_SELECT' ); -- Added by SBARAT on 12/10/2004, Bug-3830061
/* Assigning the DEFAULT value to the Auto Select Parameter */
IF (p_auto_select_flag IS NULL) THEN
l_auto_select_flag := l_auto_select_profile;
l_auto_select_flag := p_auto_select_flag;
l_dynamic_sql := ' SELECT territory_id'||
' FROM cs_incidents_all_vl'||
' WHERE incident_id = :1';
l_dynamic_sql := ' SELECT territory_id'||
' FROM css_def_defects_all'||
' WHERE defect_id = :1';
l_assign_resources_tbl.DELETE;
IF (l_auto_select_flag = 'Y') THEN
l_total_records := p_no_of_resources;
IF(l_auto_select_flag = 'Y')
THEN
l_temp_table.delete;
x_assign_resources_tbl.delete;
END IF; -- end of auto select flag
IF (l_auto_select_flag = 'Y') THEN
l_total_records := p_no_of_resources;
l_dynamic_sql1 := ' SELECT a.city city '||
' FROM hz_locations a, hz_party_sites b, hz_party_site_uses c '||
' WHERE c.site_use_type = :1 AND '||
' b.party_site_id = :2 AND '||
' a.location_id = b.location_id AND '||
' c.party_site_id = b.party_site_id ';
jtf_assign_pub.g_assign_resources_tbl.delete;
x_assign_resources_tbl.delete;
x_assign_resources_tbl.delete;
SELECT jtf_calendars_s.NEXTVAL INTO l_workflow_key
FROM dual;
p_auto_select_flag IN VARCHAR2 ,
p_contracts_preferred_engineer IN VARCHAR2 ,
p_ib_preferred_engineer IN VARCHAR2 ,
p_contract_id IN NUMBER ,
p_customer_product_id IN NUMBER ,
p_effort_duration IN NUMBER ,
p_effort_uom IN VARCHAR2 ,
p_start_date IN DATE ,
p_end_date IN DATE ,
p_territory_flag IN VARCHAR2 ,
p_calendar_flag IN VARCHAR2 ,
p_web_availability_flag IN VARCHAR2 ,
p_category_id IN NUMBER ,
p_inventory_item_id IN NUMBER ,
p_inventory_org_id IN NUMBER ,
p_problem_code IN VARCHAR2 ,
p_calling_doc_id IN NUMBER,
p_calling_doc_type IN VARCHAR2,
p_column_list IN VARCHAR2 ,
p_sr_rec IN JTF_ASSIGN_PUB.JTF_Serv_Req_rec_type ,
p_sr_task_rec IN JTF_ASSIGN_PUB.JTF_Srv_Task_rec_type ,
p_defect_rec IN JTF_ASSIGN_PUB.JTF_Def_Mgmt_rec_type ,
p_business_process_id IN NUMBER,
p_business_process_date IN DATE,
p_filter_excluded_resource IN VARCHAR2,
x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
--Added for Bug # 5386560
p_inventory_component_id IN NUMBER DEFAULT NULL,
--Added for Bug # 5386560 Ends here
--Added for Bug # 5573916
p_calendar_check IN VARCHAR2 DEFAULT 'Y'
--Added for Bug # 5573916 Ends here
)
IS
l_return_status_1 VARCHAR2(10);
l_auto_select_flag VARCHAR2(1) := p_auto_select_flag;
l_auto_select_profile VARCHAR2(1);
SELECT object_code
FROM jtf_object_usages
WHERE object_user_code = 'RESOURCES' AND
object_code = p_resource_type;
SELECT DECODE(source_postal_code, NULL, '00000', source_postal_code)
FROM jtf_rs_resource_extns_vl
WHERE resource_id = p_rid AND
'RS_'||category = p_rtype;
SELECT uom_code
FROM mtl_units_of_measure_vl
WHERE uom_code = p_effort_uom;
SELECT 'Y'
--FROM jtf_rs_all_resources_vl
FROM jtf_task_resources_vl
where resource_id = l_resource_id
and resource_type = l_resource_type
and nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate);
l_auto_select_profile := FND_PROFILE.VALUE_SPECIFIC ( 'ACTIVATE_AUTO_SELECT' );
l_auto_select_profile := FND_PROFILE.VALUE ( 'ACTIVATE_AUTO_SELECT' );
IF (p_auto_select_flag IS NULL) THEN
l_auto_select_flag := l_auto_select_profile;
l_auto_select_flag := p_auto_select_flag;
p_auto_select_flag => l_auto_select_flag,
p_contracts_preferred_engineer => l_contracts_preferred_engineer,
p_ib_preferred_engineer => l_ib_preferred_engineer,
p_contract_id => p_contract_id,
p_customer_product_id => p_customer_product_id,
p_effort_duration => l_effort_duration, --p_effort_duration,
p_effort_uom => p_effort_uom,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_territory_flag => l_territory_flag,
p_calendar_flag => l_calendar_flag,
p_web_availability_flag => l_web_availability_flag,
p_category_id => p_category_id,
p_inventory_item_id => p_inventory_item_id,
p_inventory_org_id => p_inventory_org_id,
--Added for Bug # 5386560
p_inventory_component_id => p_inventory_component_id,
--Added for Bug # 5386560 Ends here
p_problem_code => p_problem_code ,
p_sr_id => p_calling_doc_id,
p_sr_rec => l_sr_rec,
p_sr_task_rec => l_sr_task_rec,
p_business_process_id => p_business_process_id,
p_business_process_date => p_business_process_date,
p_filter_excluded_resource => p_filter_excluded_resource,
x_assign_resources_tbl => l_assign_resources_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
--Added for Bug # 5573916
p_calendar_check => p_calendar_check
--Added for Bug # 5573916 Ends here
);
IF ( UPPER(l_auto_select_flag) = 'Y' ) THEN
--l_no_of_resources := l_assign_resources_tbl.count;
ELSE -- Auto Select Flag is NO
WHILE l_current_record <= l_assign_resources_tbl.LAST
LOOP
-- add check to see whether the resource is end dated or not
-- added by sudarsana 21 feb 02
open check_date_cur(l_assign_resources_tbl(l_current_record).resource_id,
l_assign_resources_tbl(l_current_record).resource_type);
END IF; -- Auto Select Flag
jtf_assign_pub.g_assign_resources_tbl.delete;
x_assign_resources_tbl.delete;
p_auto_select_flag => l_auto_select_flag,
p_contracts_preferred_engineer => l_contracts_preferred_engineer,
p_ib_preferred_engineer => l_ib_preferred_engineer,
p_effort_duration => l_effort_duration, --p_effort_duration,
p_effort_uom => p_effort_uom,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_territory_flag => l_territory_flag,
p_calendar_flag => l_calendar_flag,
p_web_availability_flag => l_web_availability_flag,
p_task_id => p_calling_doc_id,
p_column_list => p_column_list,
p_business_process_id => p_business_process_id,
p_business_process_date => p_business_process_date,
x_assign_resources_tbl => l_assign_resources_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
--Added for Bug # 5573916
p_calendar_check => p_calendar_check
--Added for Bug # 5573916 Ends here
);
IF ( UPPER(l_auto_select_flag) = 'Y' ) THEN
-- added this condition to avoid pl/sql numeric error. if no of resources was greater than table count
-- it still used to go into loop . 13 july 2004
l_no_of_resources := least(nvl(l_assign_resources_tbl.count, 0),l_no_of_resources) ;
ELSE -- Auto Select Flag is NO
WHILE l_current_record <= l_assign_resources_tbl.LAST
LOOP
-- add check to see whether the resource is end dated or not
-- added by sudarsana 21 feb 02
open check_date_cur(l_assign_resources_tbl(l_current_record).resource_id,
l_assign_resources_tbl(l_current_record).resource_type);
END IF; -- Auto Select Flag
jtf_assign_pub.g_assign_resources_tbl.delete;
x_assign_resources_tbl.delete;
x_assign_resources_tbl.delete;
p_auto_select_flag => l_auto_select_flag,
p_effort_duration => l_effort_duration, --p_effort_duration,
p_effort_uom => p_effort_uom,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_territory_flag => l_territory_flag,
p_calendar_flag => l_calendar_flag,
p_defect_rec => l_defect_rec,
p_business_process_id => p_business_process_id,
p_business_process_date => p_business_process_date,
x_assign_resources_tbl => l_assign_resources_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF (p_auto_select_flag = 'Y') THEN
l_no_of_resources := p_no_of_resources;
x_assign_resources_tbl.delete;
jtf_assign_pub.g_assign_resources_tbl.delete;
x_assign_resources_tbl.delete;
SELECT jtf_calendars_s.NEXTVAL INTO l_workflow_key
FROM dual;
SELECT source_object_type_code,
source_object_id,
planned_start_date,
planned_end_date,
planned_effort,
planned_effort_uom
FROM jtf_tasks_vl
WHERE task_id = l_task_id;
l_dynamic_sql1 := ' SELECT contract_service_id, customer_product_id'||
' FROM cs_incidents_all_vl'||
' WHERE incident_id = :1';
l_dynamic_sql1 := ' SELECT contract_service_id, customer_product_id'||
' FROM cs_incidents_all_vl'||
' WHERE incident_id = :1';
l_dynamic_sql1 := ' SELECT contract_service_id, customer_product_id'||
' FROM cs_incidents_all_vl'||
' WHERE incident_id = :1';
x_excluded_resouurce_tbl.delete;