[Home] [Help]
PACKAGE BODY: APPS.HZ_PARTY_USAGE_RULES_PKG
Source
1 PACKAGE BODY HZ_PARTY_USAGE_RULES_PKG AS
2 /*$Header: ARHPURTB.pls 120.0 2005/05/12 23:15:37 jhuang noship $ */
3
4 D_FUTURE_DATE CONSTANT DATE := TO_DATE('4712/12/31','YYYY/MM/DD');
5
6 PROCEDURE insert_row (
7 x_party_usage_rule_id IN NUMBER,
8 x_party_usage_rule_type IN VARCHAR2,
9 x_party_usage_code IN VARCHAR2,
10 x_related_party_usage_code IN VARCHAR2,
11 x_effective_start_date IN DATE,
12 x_effective_end_date IN DATE,
13 x_object_version_number IN NUMBER,
14 x_creation_date IN DATE,
15 x_created_by IN NUMBER,
16 x_last_update_date IN DATE,
17 x_last_updated_by IN NUMBER,
18 x_last_update_login IN NUMBER
19 ) IS
20
21 BEGIN
22
23 INSERT INTO hz_party_usage_rules (
24 party_usage_rule_id,
25 party_usage_rule_type,
26 party_usage_code,
27 related_party_usage_code,
28 effective_start_date,
29 effective_end_date,
30 object_version_number,
31 created_by,
32 creation_date,
33 last_update_login,
34 last_update_date,
35 last_updated_by
36 )
37 VALUES (
38 DECODE(x_party_usage_rule_id,
39 NULL, hz_party_usage_rules_s.nextval,
40 FND_API.G_MISS_CHAR, hz_party_usage_rules_s.nextval,
41 x_party_usage_rule_id),
42 x_party_usage_rule_type,
43 x_party_usage_code,
44 DECODE(x_related_party_usage_code,
45 FND_API.G_MISS_CHAR, NULL,
46 x_related_party_usage_code),
47 DECODE(x_effective_start_date,
48 NULL, trunc(sysdate),
49 FND_API.G_MISS_DATE, trunc(sysdate),
50 trunc(x_effective_start_date)),
51 DECODE(x_effective_end_date,
52 NULL, D_FUTURE_DATE,
53 FND_API.G_MISS_DATE, D_FUTURE_DATE,
54 trunc(x_effective_end_date)),
55 x_object_version_number,
56 x_created_by,
57 x_creation_date,
58 x_last_update_login,
59 x_last_update_date,
60 x_last_updated_by
61 );
62
63 END insert_row;
64
65
66 PROCEDURE update_row (
67 x_party_usage_rule_id IN NUMBER,
68 x_party_usage_rule_type IN VARCHAR2,
69 x_party_usage_code IN VARCHAR2,
70 x_related_party_usage_code IN VARCHAR2,
71 x_effective_start_date IN DATE,
72 x_effective_end_date IN DATE,
73 x_object_version_number IN NUMBER,
74 x_last_update_date IN DATE,
75 x_last_updated_by IN NUMBER,
76 x_last_update_login IN NUMBER
77 ) IS
78
79 BEGIN
80
81 UPDATE hz_party_usage_rules
82 SET
83 party_usage_rule_type = x_party_usage_rule_type,
84 party_usage_code = x_party_usage_code,
85 related_party_usage_code =
86 DECODE(x_related_party_usage_code,
87 NULL, related_party_usage_code,
88 FND_API.G_MISS_CHAR, NULL,
89 x_related_party_usage_code),
90 effective_start_date =
91 DECODE(x_effective_start_date,
92 NULL, effective_start_date,
93 trunc(x_effective_start_date)),
94 effective_end_date =
95 DECODE(x_effective_end_date,
96 NULL, effective_end_date,
97 FND_API.G_MISS_DATE, D_FUTURE_DATE,
98 trunc(x_effective_end_date)),
99 object_version_number =
100 DECODE(x_object_version_number,
101 NULL, object_version_number,
102 x_object_version_number),
103 last_update_login = x_last_update_login,
104 last_update_date = x_last_update_date,
105 last_updated_by = x_last_updated_by
106 WHERE party_usage_rule_id = x_party_usage_rule_id;
107
108 IF ( SQL%NOTFOUND ) THEN
109 RAISE NO_DATA_FOUND;
110 END IF;
111
112 END update_row;
113
114
115 /**
116 * The following procedure will be called only from lct file.
117 * We don't need to check last update date because:
118 *
119 * - Customer can't update seeded party usage rules.
120 */
121
122 PROCEDURE load_row (
123 x_party_usage_rule_id IN VARCHAR2,
124 x_party_usage_rule_type IN VARCHAR2,
125 x_party_usage_code IN VARCHAR2,
126 x_related_party_usage_code IN VARCHAR2,
127 x_effective_start_date IN VARCHAR2,
128 x_effective_end_date IN VARCHAR2,
129 x_owner IN VARCHAR2,
130 x_last_update_date IN VARCHAR2,
131 x_custom_mode IN VARCHAR2
132 ) IS
133
134 f_luby NUMBER; -- entity owner in file
135 f_ludate DATE; -- entity update date in file
136
137 CURSOR c_party_usage_rule IS
138 SELECT object_version_number
139 FROM hz_party_usage_rules
140 WHERE party_usage_rule_id = x_party_usage_rule_id;
141
142 -- we don't need to check transition rules created by customer
143 -- because we don't allow customer create seeded->seeded
144 -- transition rule.
145 CURSOR c_duplicate_rule (
146 p_seed NUMBER
147 ) IS
148 SELECT party_usage_rule_id, object_version_number
149 FROM hz_party_usage_rules
150 WHERE party_usage_rule_type = x_party_usage_rule_type
151 AND party_usage_code = x_party_usage_code
152 AND NVL(related_party_usage_code, '##') = NVL(x_related_party_usage_code, '##')
153 AND effective_end_date = D_FUTURE_DATE
154 AND created_by <> p_seed;
155
156 db_object_version_number NUMBER;
157 db_party_usage_rule_id NUMBER;
158 db_object_version_number1 NUMBER;
159
160 BEGIN
161
162 -- Translate owner to file_last_updated_by
163 f_luby := fnd_load_util.owner_id(x_owner);
164
165 -- Translate char last_update_date to date
166 f_ludate := NVL(TO_DATE(x_last_update_date, 'YYYY/MM/DD'), sysdate);
167
168 OPEN c_party_usage_rule;
169 FETCH c_party_usage_rule INTO db_object_version_number;
170
171 IF (c_party_usage_rule%NOTFOUND) THEN
172 insert_row (
173 x_party_usage_rule_id => x_party_usage_rule_id,
174 x_party_usage_rule_type => x_party_usage_rule_type,
175 x_party_usage_code => x_party_usage_code,
176 x_related_party_usage_code => x_related_party_usage_code,
177 x_effective_start_date => TO_DATE(x_effective_start_date, 'YYYY/MM/DD'),
178 x_effective_end_date => TO_DATE(x_effective_end_date, 'YYYY/MM/DD'),
179 x_object_version_number => 1,
180 x_creation_date => f_ludate,
181 x_created_by => f_luby,
182 x_last_update_date => f_ludate,
183 x_last_updated_by => f_luby,
184 x_last_update_login => 0
185 );
186 ELSE
187 update_row (
188 x_party_usage_rule_id => x_party_usage_rule_id,
189 x_party_usage_rule_type => x_party_usage_rule_type,
190 x_party_usage_code => x_party_usage_code,
191 x_related_party_usage_code => x_related_party_usage_code,
192 x_effective_start_date => TO_DATE(x_effective_start_date, 'YYYY/MM/DD'),
193 x_effective_end_date => TO_DATE(x_effective_end_date, 'YYYY/MM/DD'),
194 x_object_version_number => (db_object_version_number + 1),
195 x_last_update_date => f_ludate,
196 x_last_updated_by => f_luby,
197 x_last_update_login => 0
198 );
199 END IF;
200
201 CLOSE c_party_usage_rule;
202
203 -- check the duplicate rule created by customer
204 -- if there is any, inactive that rule
205 --
206 IF (TO_DATE(x_effective_end_date, 'YYYY/MM/DD') = D_FUTURE_DATE) THEN
207 OPEN c_duplicate_rule(f_luby);
208 FETCH c_duplicate_rule INTO db_party_usage_rule_id, db_object_version_number1;
209 IF (c_duplicate_rule%FOUND) THEN
210 update_row (
211 x_party_usage_rule_id => db_party_usage_rule_id,
212 x_party_usage_rule_type => null,
213 x_party_usage_code => null,
214 x_related_party_usage_code => null,
215 x_effective_start_date => null,
216 x_effective_end_date => sysdate,
217 x_object_version_number => (db_object_version_number1 + 1),
218 x_last_update_date => sysdate,
219 x_last_updated_by => f_luby,
220 x_last_update_login => 0
221 );
222 END IF;
223 CLOSE c_duplicate_rule;
224 END IF;
225
226 END load_row;
227
228
229 END HZ_PARTY_USAGE_RULES_PKG;