[Home] [Help]
1: PACKAGE BODY csf_resource_pub AS
2: /* $Header: CSFPRESB.pls 120.20.12010000.3 2008/10/31 13:47:05 lokumar ship $ */
3:
4: g_pkg_name CONSTANT VARCHAR2(30) := 'CSF_RESOURCE_PUB';
5: g_assign_doc_type CONSTANT VARCHAR2(2) := 'SR';
1: PACKAGE BODY csf_resource_pub AS
2: /* $Header: CSFPRESB.pls 120.20.12010000.3 2008/10/31 13:47:05 lokumar ship $ */
3:
4: g_pkg_name CONSTANT VARCHAR2(30) := 'CSF_RESOURCE_PUB';
5: g_assign_doc_type CONSTANT VARCHAR2(2) := 'SR';
6: g_earth_radius CONSTANT NUMBER := 6378137;
7: g_pi CONSTANT NUMBER := 2 * ACOS(0);
8:
40: resource_id NUMBER
41: , resource_type jtf_objects_b.object_code%TYPE
42: , resource_name jtf_rs_resource_extns_tl.resource_name%TYPE
43: , resource_number jtf_rs_resource_extns.resource_number%TYPE
44: , address csf_resource_address_pvt.address_rec_type
45: );
46:
47: /**
48: * PLSQL Index By Table Type to contain information about many Resources
642: , NVL(MIN(ss.end_date_active + 1), p_end)
643: , p_end
644: ) winend
645: , COUNT(*) amount
646: FROM csf_resource_skills_b rs
647: , csf_required_skills_b ts
648: , csf_skill_levels_b rsl
649: , csf_skill_levels_b tsl
650: , csf_skills_b ss
734: l_return_status VARCHAR2(1);
735: l_msg_count NUMBER;
736: l_msg_data VARCHAR2(2000);
737: l_sorted_res_tbl jtf_assign_pub.assignresources_tbl_type;
738: l_res_dist_tbl csf_resource_tbl;
739: i PLS_INTEGER;
740: j PLS_INTEGER;
741: l_pref_res_cnt PLS_INTEGER;
742: l_address csf_resource_address_pvt.address_rec_type;
738: l_res_dist_tbl csf_resource_tbl;
739: i PLS_INTEGER;
740: j PLS_INTEGER;
741: l_pref_res_cnt PLS_INTEGER;
742: l_address csf_resource_address_pvt.address_rec_type;
743: l_geometry MDSYS.SDO_GEOMETRY;
744: l_task_lat NUMBER;
745: l_task_lon NUMBER;
746: l_res_lat NUMBER;
756:
757: CURSOR c_sorted_resources IS
758: SELECT resource_index
759: , distance
760: FROM TABLE(CAST(l_res_dist_tbl AS csf_resource_tbl) )
761: ORDER BY preferred_resource_flag desc, distance, resource_index;
762: BEGIN
763: -- Validate the Geometry of the Task.
764: -- If Task has no or invalid geometry, no need to sort by distance at all
783: END IF;
784:
785: l_task_lon := l_geometry.sdo_ordinates(1);
786: l_task_lat := l_geometry.sdo_ordinates(2);
787: l_res_dist_tbl := csf_resource_tbl();
788: i := p_unsorted_res_tbl.FIRST;
789: l_pref_res_cnt := 1;
790: WHILE i IS NOT NULL LOOP
791: l_address := get_resource_party_address(
813: END IF;
814: l_res_dist_tbl.EXTEND;
815:
816: IF p_unsorted_res_tbl(i).preference_type = 'I' OR p_unsorted_res_tbl(i).preference_type = 'C' THEN
817: l_res_dist_tbl(i) := csf_resource('Y', i, l_distance,null,null,null,null,null);
818: ELSE
819: l_res_dist_tbl(i) := csf_resource('N', i, l_distance,null,null,null,null,null);
820: END IF;
821:
815:
816: IF p_unsorted_res_tbl(i).preference_type = 'I' OR p_unsorted_res_tbl(i).preference_type = 'C' THEN
817: l_res_dist_tbl(i) := csf_resource('Y', i, l_distance,null,null,null,null,null);
818: ELSE
819: l_res_dist_tbl(i) := csf_resource('N', i, l_distance,null,null,null,null,null);
820: END IF;
821:
822: i := p_unsorted_res_tbl.NEXT(i);
823: END LOOP;
1693: IF l_res_cache_info.address.party_site_id IS NULL
1694: OR TRUNC(p_date) < l_res_cache_info.address.start_date_active
1695: OR TRUNC(p_date) > NVL(l_res_cache_info.address.end_date_active, p_date + 1)
1696: THEN
1697: csf_resource_address_pvt.get_resource_address(
1698: p_api_version => 1.0
1699: , x_return_status => l_return_status
1700: , x_msg_count => l_msg_count
1701: , x_msg_data => l_msg_data
1771: p_res_id NUMBER
1772: , p_res_type VARCHAR2
1773: , p_date DATE
1774: )
1775: RETURN csf_resource_address_pvt.address_rec_type IS
1776: BEGIN
1777: RETURN get_resource_from_cache(p_res_id, p_res_type, TRUE, p_date).address;
1778: END get_resource_party_address;
1779:
2050:
2051:
2052: BEGIN
2053: init_assign_errors;
2054: END csf_resource_pub;