[Home] [Help]
PACKAGE BODY: APPS.HXC_LOCKING_RULES_UPLOAD_PKG
Source
1 PACKAGE BODY hxc_locking_rules_upload_pkg AS
2 /* $Header: hxclockrulesload.pkb 115.1 2004/05/13 02:18:37 dragarwa noship $ */
3
4 PROCEDURE load_locking_rules_row (
5 p_owner_process_type IN VARCHAR2
6 , p_owner_locker_type IN VARCHAR2
7 , p_requestor_process_type IN VARCHAR2
8 , p_requestor_locker_type IN VARCHAR2
9 , p_owner IN VARCHAR2
10 , p_grant_lock IN VARCHAR2
11 , p_custom_mode IN VARCHAR2 ) IS
12
13 l_owner_id hxc_locking_rules.locker_type_owner_id%TYPE;
14 l_requestor_id hxc_locking_rules.locker_type_requestor_id%TYPE;
15 l_grant_lock hxc_locking_rules.grant_lock%TYPE;
16
17 l_locker_type hxc_locker_types.locker_type%TYPE;
18 l_process_type hxc_locker_types.process_type%TYPE;
19 l_owner VARCHAR2(4000);
20
21 FUNCTION find_locker_type_id ( p_process_type VARCHAR2,p_locker_type VARCHAR2 ) RETURN number IS
22 CURSOR csr_get_locker_type_id IS
23 SELECT locker_type_id
24 FROM hxc_locker_types
25 WHERE locker_type = p_locker_type
26 AND process_type = p_process_type;
27
28 l_locker_type_id hxc_locker_types.locker_type_id%TYPE;
29
30 BEGIN
31
32 l_locker_type_id :=NULL;
33
34 OPEN csr_get_locker_type_id;
35 FETCH csr_get_locker_type_id INTO l_locker_type_id;
36 CLOSE csr_get_locker_type_id;
37 RETURN l_locker_type_id;
38 END find_locker_type_id;
39
40 BEGIN
41
42 l_owner_id:=find_locker_type_id(p_owner_process_type,p_owner_locker_type);
43 l_requestor_id:=find_locker_type_id(p_requestor_process_type,p_requestor_locker_type);
44
45 BEGIN
46 SELECT locker_type_owner_id
47 ,locker_type_requestor_id
48 , fnd_load_util.owner_name(NVL(last_updated_by,-1))
49 , grant_lock
50
51 INTO l_owner_id
52 ,l_requestor_id
53 ,l_owner
54 ,l_grant_lock
55
56 FROM hxc_locking_rules lkr
57 WHERE lkr.locker_type_owner_id = l_owner_id
58 AND lkr.locker_type_requestor_id = l_requestor_id;
59
60
61 IF ( p_custom_mode = 'FORCE' OR l_owner = 'ORACLE' )
62 THEN
63 -- only update if the grant_lock has actually changed
64
65
66 hxc_lkr_upd.upd
67 ( p_locker_type_owner_id =>l_owner_id
68 ,p_locker_type_requestor_id =>l_requestor_id
69 ,p_grant_lock =>p_grant_lock
70 );
71
72 END IF;
73 EXCEPTION WHEN NO_DATA_FOUND THEN
74
75 hxc_lkr_ins.ins
76 ( p_grant_lock => p_grant_lock
77 ,p_locker_type_owner_id => l_owner_id
78 ,p_locker_type_requestor_id => l_requestor_id
79 );
80
81 END;
82
83 END load_locking_rules_row;
84 PROCEDURE load_locking_rules_row (
85 p_owner_process_type IN VARCHAR2
86 , p_owner_locker_type IN VARCHAR2
87 , p_requestor_process_type IN VARCHAR2
88 , p_requestor_locker_type IN VARCHAR2
89 , p_owner IN VARCHAR2
90 , p_grant_lock IN VARCHAR2
91 , p_custom_mode IN VARCHAR2
92 ,p_last_update_date IN VARCHAR2 ) IS
93
94 l_owner_id hxc_locking_rules.locker_type_owner_id%TYPE;
95 l_requestor_id hxc_locking_rules.locker_type_requestor_id%TYPE;
96 l_grant_lock hxc_locking_rules.grant_lock%TYPE;
97
98 l_locker_type hxc_locker_types.locker_type%TYPE;
99 l_process_type hxc_locker_types.process_type%TYPE;
100
101 l_last_update_date_db hxc_locking_rules.last_update_date%TYPE;
102 l_last_updated_by_db hxc_locking_rules.last_updated_by%TYPE;
103 l_last_updated_by_f hxc_locking_rules.last_updated_by%TYPE;
104 l_last_update_date_f hxc_locking_rules.last_update_date%TYPE;
105
106 FUNCTION find_locker_type_id ( p_process_type VARCHAR2,p_locker_type VARCHAR2 ) RETURN number IS
107 CURSOR csr_get_locker_type_id IS
108 SELECT locker_type_id
109 FROM hxc_locker_types
110 WHERE locker_type = p_locker_type
111 AND process_type = p_process_type;
112
113 l_locker_type_id hxc_locker_types.locker_type_id%TYPE;
114
115 BEGIN
116
117 l_locker_type_id :=NULL;
118
119 OPEN csr_get_locker_type_id;
120 FETCH csr_get_locker_type_id INTO l_locker_type_id;
121 CLOSE csr_get_locker_type_id;
122 RETURN l_locker_type_id;
123 END find_locker_type_id;
124
125 BEGIN
126
127 l_owner_id:=find_locker_type_id(p_owner_process_type,p_owner_locker_type);
128 l_requestor_id:=find_locker_type_id(p_requestor_process_type,p_requestor_locker_type);
129 l_last_updated_by_f := fnd_load_util.owner_id(p_owner);
130 l_last_update_date_f := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
131
132 BEGIN
133 SELECT locker_type_owner_id
134 ,locker_type_requestor_id
135
136 , grant_lock
137 ,last_update_date
138 ,last_updated_by
139
140 INTO l_owner_id
141 ,l_requestor_id
142
143 ,l_grant_lock
144 ,l_last_update_date_db
145 ,l_last_updated_by_db
146
147 FROM hxc_locking_rules lkr
148 WHERE lkr.locker_type_owner_id = l_owner_id
149 AND lkr.locker_type_requestor_id = l_requestor_id;
150
151
152 IF (fnd_load_util.upload_test( l_last_updated_by_f,
153 l_last_update_date_f,
154 l_last_updated_by_db,
155 l_last_update_date_db ,
156 p_custom_mode))
157 THEN
158 -- only update if the grant_lock has actually changed
159
160
161 hxc_lkr_upd.upd
162 ( p_locker_type_owner_id =>l_owner_id
163 ,p_locker_type_requestor_id =>l_requestor_id
164 ,p_grant_lock =>p_grant_lock
165 );
166
167 END IF;
168 EXCEPTION WHEN NO_DATA_FOUND THEN
169
170 hxc_lkr_ins.ins
171 ( p_grant_lock => p_grant_lock
172 ,p_locker_type_owner_id => l_owner_id
173 ,p_locker_type_requestor_id => l_requestor_id
174 );
175
176 END;
177
178 END load_locking_rules_row;
179
180
181 END hxc_locking_rules_upload_pkg;