PACKAGE BODY: APPS.HXC_HRR_UPLOAD_PKG

Source

1 PACKAGE BODY hxc_hrr_upload_pkg AS
2 /* $Header: hxchrrupl.pkb 115.7 2002/06/10 00:37:26 pkm ship      $ */
3 
4 PROCEDURE load_hrr_row (
5           p_name			VARCHAR2
6         , p_legislation_code            VARCHAR2
7 	, p_eligibility_criteria_type 	VARCHAR2
8 	, p_pref_hierarchy_name		VARCHAR2
9 	, p_rule_evaluation_order	NUMBER
10 	, p_resource_type		VARCHAR2
11 	, p_start_date			VARCHAR
12 	, p_end_date			VARCHAR
13 	, p_owner			VARCHAR2
14 	, p_custom_mode			VARCHAR2 ) IS
15 
16 l_resource_rule_id	hxc_resource_rules.resource_rule_id%TYPE;
17 l_pref_hierarchy_id	hxc_pref_hierarchies.pref_hierarchy_id%TYPE;
18 l_ovn			hxc_pref_hierarchies.object_version_number%TYPE := NULL;
19 l_owner			VARCHAR2(6);
20 
21 FUNCTION get_pref_id ( p_name VARCHAR2 ) RETURN NUMBER IS
22 
23 l_pref_id	hxc_pref_hierarchies.parent_pref_hierarchy_id%TYPE;
24 
25 CURSOR	csr_get_pref_id IS
26 SELECT	pref_hierarchy_id
27 FROM	hxc_pref_hierarchies
28 WHERE	name	= p_name
29 AND	parent_pref_hierarchy_id is null;
30 
31 BEGIN
32 
33 OPEN  csr_get_pref_id;
34 FETCH csr_get_pref_id INTO l_pref_id;
35 CLOSE csr_get_pref_id;
36 
37 RETURN l_pref_id;
38 
39 END get_pref_id;
40 
41 
42 BEGIN -- load_hrr_row
43 
44 l_pref_hierarchy_id := get_pref_id ( p_pref_hierarchy_name );
45 
46 	SELECT	resource_rule_id
47 	,	object_version_number
48 	,	DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
49 	INTO	l_resource_rule_id
50 	,	l_ovn
51 	,	l_owner
52 	FROM	hxc_resource_rules
53 	WHERE	name	= P_NAME;
54 
55 	IF ( p_custom_mode = 'FORCE' OR l_owner = 'SEED' )
56 	THEN
57 
58 		hxc_resource_rules_api.update_resource_rules (
59 				  p_effective_date		=> sysdate
60 				, p_name			=> p_name
61 				, p_business_group_id           => null
62 				, p_legislation_code            => p_legislation_code
63 				, p_resource_rule_id		=> l_resource_rule_id
64 				, p_eligibility_criteria_type   => p_eligibility_criteria_type
65 				, p_pref_hierarchy_id           => l_pref_hierarchy_id
66 				, p_rule_evaluation_order       => p_rule_evaluation_order
67 				, p_resource_type               => p_resource_type
68 				, p_start_date                  => TO_DATE(p_start_date, 'DD-MM-YYYY')
69 				, p_end_date                    => TO_DATE(p_end_date, 'DD-MM-YYYY')
70 	  			, p_object_version_number  	=> l_ovn );
71 
72 	END IF;
73 
74 EXCEPTION WHEN NO_DATA_FOUND
75 THEN
76 		hxc_resource_rules_api.create_resource_rules (
77 			  p_effective_date		=> sysdate
78 			, p_name			=> p_name
79 			, p_business_group_id           => null
80 			, p_legislation_code            => p_legislation_code
81 			, p_resource_rule_id		=> l_resource_rule_id
82 			, p_eligibility_criteria_type   => p_eligibility_criteria_type
83 			, p_pref_hierarchy_id           => l_pref_hierarchy_id
84 			, p_rule_evaluation_order       => p_rule_evaluation_order
85 			, p_resource_type               => p_resource_type
86 			, p_start_date                  => TO_DATE(p_start_date, 'DD-MM-YYYY')
87 			, p_end_date                    => TO_DATE(p_end_date, 'DD-MM-YYYY')
88   			, p_object_version_number  	=> l_ovn );
89 END load_hrr_row;
90 
91 END hxc_hrr_upload_pkg;