[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
, ib.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.org_id squal_16 -- THIS CODE WAS COMMENTED BY VAKULKAR AS TERRITORY POPULATE VALUE3_ID AS ORG_ID
-- 1ST VALUE WILL BE 1373( Inv.Items - COMPUTER.DESKTOP - Desktop Computers)
-- 2ND VALUE WILL BE 8980 (FS54888 - Sentinel Standard Desktop - FS Series)
-- 3RD VALUE IS 204 -- THAT IS WHAT TERRITORY IS CREATED FOR
-- AND WE ARE PASSING ib.inv_organization_id IN squal_num16 WHICH HAS VALUE 207
-- SO IT DOES NOT QUALIFY TERRITORY. SO now i am passsign org_id as squal_num16
, ib.owner_group_id squal_num17
, ib.language_id squal_num30
, ib.contract_service_id
, TB.PLANNED_START_DATE
, ib.system_id
, ib.cust_pref_lang_code
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.
* 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
* @return Common Resource Table (JTF_ASSIGN_PUB.ASSIGNRESOURCES_TBL_TYPE)
*/
FUNCTION union_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
) RETURN jtf_assign_pub.assignresources_tbl_type IS
i PLS_INTEGER;
SELECT rs.resource_id
, rs.resource_type
, rs.winstart
, rs.winend
, rs.count_of_matching_skills
, rs.skill_level
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(*) count_of_matching_skills
, SUM( 1/rsl.step_value ) skill_level
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 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 (
ts.skill_type_id NOT IN (2, 3)
AND rs.skill_id = ts.skill_id
AND ts.skill_type_id = rs.skill_type_id
AND TRUNC(ss.start_date_active) < SYSDATE
AND TRUNC(NVL(ss.end_date_active, SYSDATE) + 1) > SYSDATE
OR ts.skill_type_id = 2
AND rs.skill_id = ts.skill_id
AND ts.skill_type_id = rs.skill_type_id
AND EXISTS (SELECT 1 FROM mtl_system_items_kfv msi WHERE msi.inventory_item_id = rs.skill_id)
OR ts.skill_type_id = 3
AND rs.skill_id = ts.skill_id
AND ts.skill_type_id = rs.skill_type_id
AND EXISTS (SELECT 1
FROM mtl_item_categories mic
WHERE mic.category_id = rs.skill_id
AND category_set_id = fnd_profile.VALUE('CS_SR_DEFAULT_CATEGORY_SET'))
/* OR ts.skill_type_id = 2
AND rs.skill_type_id = 3
AND NOT EXISTS (SELECT 1
FROM csf_required_skills_b ts2
, mtl_item_categories mic
WHERE NVL(ts2.disabled_flag, 'N') <> 'Y'
AND ts2.has_skill_type = ts.has_skill_type
AND ts2.has_skill_id = ts.has_skill_id
AND ts2.required_skill_id <> ts.required_skill_id
AND ts2.skill_type_id = 3
AND mic.inventory_item_id = ts.skill_id
AND mic.category_set_id = fnd_profile.VALUE('CS_SR_DEFAULT_CATEGORY_SET')
AND mic.category_id = ts2.skill_id)
AND NOT EXISTS (SELECT 1
FROM csf_resource_skills_b rs2
WHERE TRUNC(rs2.start_date_active) < p_end
AND (TRUNC(rs2.end_date_active + 1) > p_start OR rs2.end_date_active IS NULL)
AND rs2.resource_id = rs.resource_id
AND rs2.skill_id = ts.skill_id
AND rs2.skill_type_id = ts.skill_type_id)
AND EXISTS (SELECT category_id
FROM mtl_item_categories mic
WHERE mic.inventory_item_id = ts.skill_id
AND mic.category_set_id = fnd_profile.VALUE('CS_SR_DEFAULT_CATEGORY_SET')
AND mic.category_id = rs.skill_id) */
)
GROUP BY rs.resource_id, rs.resource_type) rs
, (
SELECT COUNT(*) count_of_req_skills
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.count_of_matching_skills = ts.count_of_req_skills
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;
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;
debug( fnd_log.level_statement, l_api_name, 'No Resource Selection Criteria is specified');
x_res_tbl.DELETE;
debug( fnd_log.level_statement, l_api_name, 'Resource Selection is based on Skills..finding out the skills');
, 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 source_object_id
, planned_start_date
, planned_end_date
, planned_effort
, planned_effort_uom
FROM jtf_tasks_b t
WHERE t.task_id = p_task_id;
SELECT /*+ CARDINALITY(tr, 1) */
r.resource_name
, t.name terr_name
, tr.resource_index
FROM jtf_rs_all_resources_vl r
, jtf_terr_all t
, TABLE( CAST( x_res_tbl AS csf_resource_tbl ) ) tr
WHERE r.resource_id = tr.resource_id
AND r.resource_type = tr.resource_type
AND t.terr_id (+) = tr.terr_id;
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');
SELECT NVL(meaning, 'AT_HOME')
FROM fnd_lookups
WHERE lookup_type = 'CSF_GPS_DEVICE_STATUSES'
AND lookup_code = 'AT_HOME';
SELECT count(*)
FROM csf_gps_device_assignments a
WHERE a.resource_id = p_resource_id
AND a.resource_type = p_resource_type
AND NVL(p_date, SYSDATE) BETWEEN a.start_date_active AND NVL(a.end_date_active, SYSDATE + 1);
SELECT jrb.role_code
, jrr.role_resource_type
FROM jtf_rs_role_relations jrr
, jtf_rs_roles_b jrb
WHERE jrr.role_id = jrb.role_id
AND jrr.role_resource_id = p_resource_id
AND jrb.role_type_code = 'CSF_THIRD_PARTY'
AND jrr.role_resource_type = p_resource_type
AND ( jrr.start_date_active IS NULL or trunc(jrr.start_date_active) <= sysdate )
AND ( jrr.end_date_active IS NULL or trunc(jrr.end_date_active) >= sysdate )
AND NVL( jrr.delete_flag, 'N') = 'N'
ORDER BY 1;