[Home] [Help]
PACKAGE BODY: APPS.HXC_RTR_UPLOAD_PKG
Source
1 PACKAGE BODY hxc_rtr_upload_pkg AS
2 /* $Header: hxrtrupl.pkb 115.4 2002/03/04 17:55:54 pkm ship $*/
3
4 PROCEDURE load_rtr_row (
5 p_name VARCHAR2
6 , p_ret_name VARCHAR2
7 , p_owner VARCHAR2
8 , p_custom_mode VARCHAR2 ) IS
9
10 l_retrieval_rule_id hxc_retrieval_rules.retrieval_rule_id%TYPE;
11 l_retrieval_process_id hxc_retrieval_processes.retrieval_process_id%TYPE;
12 l_ovn hxc_retrieval_rules.object_version_number%TYPE := NULL;
13 l_owner VARCHAR2(6);
14
15 FUNCTION get_retrieval_process_id ( p_retrieval_process_name VARCHAR2 ) RETURN NUMBER IS
16
17 l_retrieval_process_id hxc_retrieval_processes.retrieval_process_id%TYPE;
18
19 CURSOR csr_get_ret_id IS
20 SELECT retrieval_process_id
21 FROM hxc_retrieval_processes
22 WHERE name = p_retrieval_process_name;
23
24 BEGIN
25
26 OPEN csr_get_ret_id;
27 FETCH csr_get_ret_id INTO l_retrieval_process_id;
28 CLOSE csr_get_ret_id;
29
30 RETURN l_retrieval_process_id;
31
32 END get_retrieval_process_id;
33
34 BEGIN
35
36 l_retrieval_process_id := get_retrieval_process_id ( p_ret_name );
37
38 SELECT retrieval_rule_id
39 , object_version_number
40 , DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
41 INTO l_retrieval_rule_id
42 , l_ovn
43 , l_owner
44 FROM hxc_retrieval_rules
45 WHERE name = P_NAME;
46
47 IF ( p_custom_mode = 'FORCE' OR l_owner = 'SEED' )
48 THEN
49
50 hxc_retrieval_rules_api.update_retrieval_rules (
51 p_effective_date => sysdate
52 , p_retrieval_rule_id => l_retrieval_rule_id
53 , p_retrieval_process_id => l_retrieval_process_id
54 , p_name => p_name
55 , p_object_version_number => l_ovn );
56
57 END IF;
58
59 EXCEPTION WHEN NO_DATA_FOUND
60 THEN
61
62 hxc_retrieval_rules_api.create_retrieval_rules (
63 p_effective_date => sysdate
64 , p_name => p_name
65 , p_retrieval_rule_id => l_retrieval_rule_id
66 , p_retrieval_process_id => l_retrieval_process_id
67 , p_object_version_number => l_ovn );
68
69 END load_rtr_row;
70
71 PROCEDURE load_rtc_row (
72 p_rtr_name VARCHAR2
73 , p_time_recipient VARCHAR2
74 , p_status VARCHAR2
75 , p_owner VARCHAR2
76 , p_custom_mode VARCHAR2 ) IS
77
78 l_retrieval_rule_comp_id hxc_retrieval_rule_comps.retrieval_rule_comp_id%TYPE;
79 l_retrieval_rule_id hxc_retrieval_rules.retrieval_rule_id%TYPE;
80 l_time_recipient_id hxc_time_recipients.time_recipient_id%TYPE;
81 l_ovn hxc_retrieval_rules.object_version_number%TYPE := NULL;
82 l_owner VARCHAR2(6);
83
84 FUNCTION get_retrieval_rule_id ( p_retrieval_rule_name VARCHAR2 ) RETURN NUMBER IS
85
86 l_retrieval_rule_id hxc_retrieval_rules.retrieval_rule_id%TYPE;
87
88 CURSOR csr_get_rtr_id IS
89 SELECT retrieval_rule_id
90 FROM hxc_retrieval_rules
91 WHERE name = p_retrieval_rule_name;
92
93 BEGIN
94
95 OPEN csr_get_rtr_id;
96 FETCH csr_get_rtr_id INTO l_retrieval_rule_id;
97 CLOSE csr_get_rtr_id;
98
99 RETURN l_retrieval_rule_id;
100
101 END get_retrieval_rule_id;
102
103 BEGIN
104
105 l_time_recipient_id := hxc_ret_upload_pkg.get_time_recipient_id ( p_time_recipient );
106 l_retrieval_rule_id := get_retrieval_rule_id ( p_rtr_name );
107
108 SELECT rtc.retrieval_rule_comp_id
109 , rtc.object_version_number
110 , DECODE( NVL(rtc.last_updated_by,-1), 1, 'SEED', 'CUSTOM')
111 INTO l_retrieval_rule_comp_id
112 , l_ovn
113 , l_owner
114 FROM hxc_retrieval_rule_comps rtc
115 WHERE rtc.retrieval_rule_id = l_retrieval_rule_id
116 AND rtc.time_recipient_id = l_time_recipient_id
117 AND rtc.status = p_status;
118
119 IF ( p_custom_mode = 'FORCE' OR l_owner = 'SEED' )
120 THEN
121
122 hxc_retrieval_rule_comps_api.update_retrieval_rule_comps (
123 p_retrieval_rule_id => l_retrieval_rule_id
124 , p_effective_date => sysdate
125 , p_status => p_status
126 , p_time_recipient_id => l_time_recipient_id
127 , p_retrieval_rule_comp_id => l_retrieval_rule_comp_id
128 , p_object_version_number => l_ovn );
129
130 END IF;
131
132 EXCEPTION WHEN NO_DATA_FOUND
133 THEN
134
135 hxc_retrieval_rule_comps_api.create_retrieval_rule_comps (
136 p_retrieval_rule_id => l_retrieval_rule_id
137 , p_effective_date => sysdate
138 , p_status => p_status
139 , p_time_recipient_id => l_time_recipient_id
140 , p_retrieval_rule_comp_id => l_retrieval_rule_comp_id
141 , p_object_version_number => l_ovn );
142
143 END load_rtc_row;
144
145 END hxc_rtr_upload_pkg;