[Home] [Help]
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;