[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* encountered during Resource Selection Process.
*
* Index to the table G_ASSIGN_ERRORS represent the flags that have
* been set to select the resources - namely Skills (S), Territories (T)
* Installed Base (I) and Contracts (B). When a Flag is set to 'Y', the
* corresponding bit is set to 1. Otherwise it is 0.
*
* Examples:
* S T I C
* - - - -
* 0 0 0 0 No selection criteria
* 0 0 0 1 Only contracts
* 0 0 1 0 Only installed base
*/
PROCEDURE init_assign_errors IS
BEGIN
g_assign_errors(0) := 'CSF_NO_RES_SEL_CRIT'; -- 0000
SELECT tb.task_id task_id
, ib.incident_id service_request_id
, ib.customer_id party_id
, lo.country
, tb.address_id party_site_id
, lo.city
, lo.postal_code
, lo.state
, lo.county
, pa.party_name comp_name_range
, lo.province
, pa.employees_total num_of_employees
, tb.task_type_id
, tb.task_status_id
, tb.task_priority_id
, ib.incident_type_id
, ib.incident_severity_id
, ib.incident_urgency_id
, ib.problem_code
, ib.incident_status_id
, ib.platform_id
, ib.site_id support_site_id
, ib.customer_site_id
, it.sr_creation_channel
, ib.inventory_item_id
, ib.problem_code squal_char12
, ib.comm_pref_code squal_char13
, ib.platform_id squal_num12
, ib.inv_platform_org_id squal_num13
, ib.category_id squal_num14
, ib.inventory_item_id squal_num15
, ib.inv_organization_id squal_num16
, ib.owner_group_id squal_num17
, ib.language_id squal_num30
, ib.contract_service_id
FROM jtf_tasks_b tb
, cs_incidents_all_b ib
, cs_incidents_all_tl it
, hz_locations lo
, hz_parties pa
WHERE tb.task_id = p_task_id
AND tb.source_object_type_code = 'SR'
AND tb.source_object_id = ib.incident_id
AND tb.source_object_id = it.incident_id
AND it.LANGUAGE = USERENV('lang')
AND lo.location_id = csf_tasks_pub.get_task_location_id(tb.task_id, tb.address_id, tb.location_id)
AND ib.customer_id = pa.party_id(+);
SELECT phone_area_code
FROM hz_contact_points
WHERE owner_table_id = b_party_id
AND owner_table_name = 'HZ_PARTIES'
AND contact_point_type = 'PHONE'
AND primary_flag = 'Y';
SELECT TO_NUMBER(object1_id1) item_id
, TO_NUMBER(object1_id2) org_id
FROM okc_k_items
WHERE cle_id = b_contract_service_id;
SELECT class_code
FROM hz_code_assignments
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = b_party_id;
SELECT qual_usg_id qual_usg_id
, seeded_qual_name label
FROM jty_all_enabled_attributes_v
WHERE source_id = -1002
AND qual_type_id IN(-1002, -1005, -1006)
ORDER BY UPPER(seeded_qual_name);
g_all_qualifiers.DELETE;
* Assembles the selected Qualifiers for this Task from the Qualifier
* Table in to a Record Type understandable by JTF Assignment Manager.
*
* Uses a Hard Coded Mapping between JTF_SEEDED_QUAL_USGS_V.QUAL_USG_ID
* and the fields in JTF_ASSIGN_PUB.JTF_SRV_TASK_REC_TYPE.
*
* The Task and SR Number must be set by the caller and wont be set by
* this API. Moreover Qualifiers of type -1211, -1212 and -1218 have
* been disabled and therefore wont be set by this API.
*
* @param p_table Qualifier Table having the list of Task Qualifiers
*/
FUNCTION get_qualified_task_rec(p_table resource_qualifier_tbl_type)
RETURN jtf_assign_pub.jtf_srv_task_rec_type IS
k PLS_INTEGER;
* Side Effect of the API. Second Table may have entries deleted after
* the operation.
*
* @param p_res_1_tbl Resource Table 1
* @param p_res_2_tbl Resource Table 2
* @param p_start Start Date of the Window
* @param p_end End Date of the Window
* @return Common Resource Table (JTF_ASSIGN_PUB.ASSIGNRESOURCES_TBL_TYPE)
*/
FUNCTION intersect_results(
p_res_1_tbl IN OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
, p_res_2_tbl IN OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
, p_start DATE
, p_end DATE
) RETURN jtf_assign_pub.assignresources_tbl_type IS
i PLS_INTEGER;
p_res_2_tbl.DELETE(j); -- So that Table is smaller for other iterations.
SELECT rs.resource_id
, rs.resource_type
, rs.winstart
, rs.winend
, rs.amount
FROM (SELECT rs.resource_id
, rs.resource_type
, GREATEST(MAX(rs.start_date_active), NVL(MAX(ss.start_date_active), p_start)
, p_start) winstart
, LEAST(
NVL(MIN(rs.end_date_active + 1), p_end)
, NVL(MIN(ss.end_date_active + 1), p_end)
, p_end
) winend
, COUNT(*) amount
FROM csf_resource_skills_b rs
, csf_required_skills_b ts
, csf_skill_levels_b rsl
, csf_skill_levels_b tsl
, csf_skills_b ss
WHERE DECODE(
SIGN(rsl.step_value - tsl.step_value)
, -1, DECODE(l_levelmatch, 1, 'Y', 'N')
, 0, 'Y'
, 1, DECODE(l_levelmatch, 3, 'Y', 'N')
) = 'Y'
AND rsl.skill_level_id = rs.skill_level_id
AND tsl.skill_level_id = ts.skill_level_id
AND ts.skill_id = rs.skill_id
AND ts.skill_type_id = rs.skill_type_id
AND TRUNC(rs.start_date_active) < p_end
AND (TRUNC(rs.end_date_active + 1) > p_start OR rs.end_date_active IS NULL)
AND (rs.resource_id = p_resource_id OR p_resource_id IS NULL)
AND (rs.resource_type = p_resource_type OR p_resource_type IS NULL)
AND NVL(ts.disabled_flag, 'N') <> 'Y'
AND ts.has_skill_type = 'TASK'
AND ts.has_skill_id = p_task_id
AND ss.skill_id(+) = rs.skill_id
AND (
(
rs.skill_type_id <> 2
AND TRUNC(ss.start_date_active) < p_end
AND (TRUNC(ss.end_date_active + 1) > p_start OR ss.end_date_active IS NULL)
)
OR EXISTS(SELECT 1
FROM mtl_system_items_kfv msi
WHERE msi.inventory_item_id = ts.skill_id)
)
GROUP BY rs.resource_id, rs.resource_type) rs
, (SELECT COUNT(*) amount
FROM csf_required_skills_b
WHERE NVL(disabled_flag, 'N') <> 'Y' AND has_skill_type = 'TASK'
AND has_skill_id = p_task_id) ts
WHERE rs.amount = ts.amount AND rs.winstart < rs.winend;
x_skilled_res_tbl.DELETE;
SELECT l.geometry
FROM jtf_tasks_b t, hz_locations l
WHERE t.task_id = p_task_id
AND l.location_id = csf_tasks_pub.get_task_location_id(t.task_id, t.address_id, t.location_id);
SELECT resource_index
, distance
FROM TABLE(CAST(l_res_dist_tbl AS csf_resource_tbl) )
ORDER BY preferred_resource_flag desc, distance, resource_index;
* The API doesnt delete resources as such. It first prunes the resource list
* by considering only those Resources who have valid Shift Definitions between
* the given dates and then give the top N resources. Note that it will be
* better if the Resources are already sorted in the order of their preference.
*
* @param p_res_tbl List of Sorted Resources
* @param p_start Start of the Window for Valid Shifts Consideration
* @param p_end End of the Window for Valid Shifts Consideration
* @returns Top N Resources as determined by the MAXNRSELECTEDRES profile.
*/
FUNCTION reduce_resource_list(
p_res_tbl IN OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
, p_start DATE
, p_end DATE
)
RETURN jtf_assign_pub.assignresources_tbl_type IS
l_max_resources NUMBER;
l_max_resources := fnd_profile.VALUE('MAXNRSELECTEDRES');
p_res_tbl.delete(cnt);
* Returns the Error Message that is encountered during Resource Selection
* process based on the Flags passed. Each Flag corresponds to a But in the
* Error Message Table. See INIT_ASSIGN_ERRORS for more information.
*
* @param p_flags Flags used to select Resources for the Task
* @return Name of the Error Message encountered.
*/
FUNCTION get_assign_error_msg(p_flags IN NUMBER)
RETURN VARCHAR2 IS
l_msg VARCHAR2(100);
SELECT has_skill_id
FROM csf_required_skills_b,jtf_tasks_b t
WHERE t.task_id = p_task_id
AND has_skill_type = 'TASK'
AND has_skill_id = nvl(t.parent_task_id, t.task_id)
AND NVL(disabled_flag, 'N') <> 'Y'
AND NVL(t.deleted_flag, 'N') <> 'Y';
select iv.business_process_id
from jtf_tasks_b t, cs_incidents_all i, cs_incident_types_vl iv
where t.task_id = p_task_id
and t.source_object_id = i.incident_id
and i.incident_type_id = iv.incident_type_id;
x_res_tbl.DELETE;
, p_auto_select_flag => 'N'
, p_calendar_flag => NVL(p_calendar_flag, 'N')
, p_calendar_check => 'N'
, p_calling_doc_id => l_sr_task_rec.service_request_id
, p_calling_doc_type => g_assign_doc_type
, p_business_process_id => l_business_process_id
, x_assign_resources_tbl => l_contracts_ib_res_tbl
);
, p_auto_select_flag => 'N'
, p_calendar_flag => NVL(p_calendar_flag, 'N')
, p_calendar_check => 'N'
, p_calling_doc_id => l_sr_task_rec.service_request_id
, p_calling_doc_type => g_assign_doc_type
, x_assign_resources_tbl => l_territory_res_tbl
);
x_res_tbl.delete;
SELECT resource_id
FROM jtf_rs_resource_extns
WHERE user_id = NVL(p_user_id, fnd_global.user_id);
SELECT category
FROM jtf_rs_resource_extns
WHERE user_id = NVL(p_user_id, fnd_global.user_id);
SELECT resource_id
, p_res_type resource_type
, resource_name
, resource_number
FROM jtf_rs_resource_extns_vl
WHERE resource_id = p_res_id;
SELECT group_id resource_id
, 'RS_GROUP' resource_type
, group_name resource_name
, group_number resource_number
FROM jtf_rs_groups_vl
WHERE group_id = p_res_id;
SELECT team_id resource_id
, 'RS_TEAM' resource_type
, team_name resource_name
, team_number resource_number
FROM jtf_rs_teams_vl
WHERE team_id = p_res_id;
SELECT o.object_code code, o.name
FROM jtf_object_usages u
, jtf_objects_tl o
WHERE u.object_user_code = 'RESOURCE_TYPES'
AND o.object_code = u.object_code
AND o.language = userenv('LANG');
SELECT time_zone
FROM jtf_rs_resource_extns
WHERE resource_id = p_resource_id
AND p_resource_type NOT IN('RS_GROUP', 'RS_TEAM');