[Home] [Help]
PACKAGE BODY: APPS.HXC_HAC_UPLOAD_PKG
Source
1 PACKAGE BODY hxc_hac_upload_pkg AS
2 /* $Header: hxchacupl.pkb 115.7 2002/06/10 01:19:32 pkm ship $ */
3
4 PROCEDURE load_hac_row (
5 p_as_name VARCHAR2
6 , p_time_recipient VARCHAR2
7 , p_approval_order NUMBER
8 , p_approval_mechanism VARCHAR2
9 , p_approval_mechanism_name VARCHAR2
10 , p_wf_item_type VARCHAR2
11 , p_wf_name VARCHAR2
12 , p_start_date VARCHAR2
13 , p_end_date VARCHAR2
14 , p_owner VARCHAR2
15 , p_custom_mode VARCHAR2 ) IS
16
17
18 l_approval_comp_id hxc_approval_comps.approval_comp_id%TYPE;
19 l_approval_style_id hxc_approval_styles.approval_style_id%TYPE;
20 l_time_recipient_id hxc_time_recipients.time_recipient_id%TYPE;
21 l_approval_mechanism_id hxc_approval_comps.approval_mechanism_id%TYPE;
22 l_ovn hxc_approval_styles.object_version_number%TYPE := NULL;
23 l_owner VARCHAR2(6);
24
25 FUNCTION get_approval_style_id ( p_name VARCHAR2 ) RETURN NUMBER IS
26
27 CURSOR csr_get_as_id IS
28 SELECT approval_style_id
29 FROM hxc_approval_styles
30 WHERE name = p_name;
31
32 l_approval_style_id hxc_approval_styles.approval_style_id%TYPE;
33
34 BEGIN
35
36 OPEN csr_get_as_id;
37 FETCH csr_get_as_id INTO l_approval_style_id;
38 CLOSE csr_get_as_id;
39
40 RETURN l_approval_style_id;
41
42 END get_approval_style_id;
43
44 FUNCTION get_formula_id ( p_formula_name VARCHAR2 ) RETURN NUMBER IS
45
46 CURSOR csr_get_ff_id IS
47 SELECT ff.formula_id
48 FROM ff_formulas_f ff
49 WHERE ff.formula_name = p_formula_name;
50
51 l_formula_id ff_formulas_f.formula_id%TYPE;
52
53 BEGIN
54
55 OPEN csr_get_ff_id;
56 FETCH csr_get_ff_id INTO l_formula_id;
57 CLOSE csr_get_ff_id;
58
59 RETURN l_formula_id;
60
61 END get_formula_id;
62
63 BEGIN -- load_hac_row
64
65 l_approval_style_id := get_approval_style_id ( p_as_name );
66 l_time_recipient_id := hxc_ret_upload_pkg.get_time_recipient_id ( p_time_recipient );
67
68 IF ( p_approval_mechanism LIKE 'FORMULA%' )
69 THEN
70
71 l_approval_mechanism_id := get_formula_id ( p_approval_mechanism_name );
72
73 END IF;
74
75 SELECT hac.approval_comp_id
76 , hac.object_version_number
77 , DECODE( NVL(hac.last_updated_by,-1), 1, 'SEED', 'CUSTOM')
78 INTO l_approval_comp_id
79 , l_ovn
80 , l_owner
81 FROM hxc_approval_comps hac
82 , hxc_approval_styles has
83 WHERE has.name = P_AS_NAME
84 AND hac.approval_style_id = has.approval_style_id
85 AND hac.time_recipient_id = l_time_recipient_id
86 AND hac.approval_mechanism = p_approval_mechanism;
87
88 IF ( p_custom_mode = 'FORCE' OR l_owner = 'SEED' )
89 THEN
90
91 hxc_hac_upd.upd (
92 p_effective_date => sysdate
93 , p_approval_comp_id => l_approval_comp_id
94 , p_object_version_number => l_ovn
95 , p_approval_style_id => l_approval_style_id
96 , p_time_recipient_id => l_time_recipient_id
97 , p_approval_mechanism => p_approval_mechanism
98 , p_start_date => to_date(p_start_date, 'DD-MM-YYYY')
99 , p_end_date => to_date(p_end_date, 'DD-MM-YYYY')
100 , p_approval_mechanism_id => l_approval_mechanism_id
101 , p_wf_item_type => p_wf_item_type
102 , p_wf_name => p_wf_name
103 , p_approval_order => p_approval_order );
104
105 END IF;
106
107 EXCEPTION WHEN NO_DATA_FOUND
108 THEN
109
110 hxc_hac_ins.ins (
111 p_effective_date => sysdate
112 , p_approval_comp_id => l_approval_comp_id
113 , p_object_version_number => l_ovn
114 , p_approval_style_id => l_approval_style_id
115 , p_time_recipient_id => l_time_recipient_id
116 , p_approval_mechanism => p_approval_mechanism
117 , p_start_date => to_date(p_start_date, 'DD-MM-YYYY')
118 , p_end_date => to_date(p_end_date, 'DD-MM-YYYY')
119 , p_approval_mechanism_id => l_approval_mechanism_id
120 , p_wf_item_type => p_wf_item_type
121 , p_wf_name => p_wf_name
122 , p_approval_order => p_approval_order );
123
124 END load_hac_row;
125
126 END hxc_hac_upload_pkg;