1 PACKAGE BODY FUN_TCA_PKG AS
2 /* $Header: FUNSTCAB.pls 120.17.12010000.2 2008/08/06 07:43:30 makansal ship $*/
3
4
5 /* This Procedure returns the potential duplicates for the party_name that is
6 * passed. This is wrapper to call the TCA DQM API */
7
8 PROCEDURE find_party(p_party_name IN VARCHAR2 , p_party_type IN VARCHAR2 , p_dqm_context OUT NOCOPY NUMBER , p_dqm_count OUT NOCOPY NUMBER)
9 IS
10 p_party_search_rec_type hz_party_search.party_search_rec_type;
11 p_party_site_list hz_party_search.party_site_list;
12 p_contact_list hz_party_search.contact_list;
13 p_contact_point_list hz_party_search.contact_point_list;
14 p_x_search_ctx_id NUMBER;
15 p_x_num_matches NUMBER;
16 p_x_return_status VARCHAR2(4000);
17 p_x_msg_count NUMBER;
18 p_x_msg_data VARCHAR2(10000);
19 p_x_rule_id NUMBER;
20 p_p_restrict_sql VARCHAR2(100);
21 p_p_match_type VARCHAR2(10);
22 p_p_search_merged VARCHAR2(10);
23 BEGIN
24 -- Added the select statement for bug 3169934
25 select match_rule_id into p_x_rule_id from hz_match_rules_vl where rule_name='IC_PARTY_SEARCH';
26 p_party_search_rec_type.PARTY_NAME := p_party_name;
27 p_party_search_rec_type.PARTY_TYPE := P_PARTY_TYPE;
28 p_p_restrict_sql := null;
29 p_p_search_merged := 'Y';
30 p_p_match_type := 'AND';
31 hz_party_search.find_parties(
32 p_init_msg_list => FND_API.G_TRUE,
33 x_rule_id => p_x_rule_id,
34 p_party_search_rec => p_party_search_rec_type,
35 p_party_site_list => p_party_site_list,
36 p_contact_list => p_contact_list,
37 p_contact_point_list => p_contact_point_list,
38 p_restrict_sql => p_p_restrict_sql,
39 p_search_merged => p_p_search_merged,
40 x_search_ctx_id => p_x_search_ctx_id,
41 x_num_matches => p_x_num_matches,
42 x_return_status => p_x_return_status,
43 x_msg_count => p_x_msg_count,
44 x_msg_data => p_x_msg_data
45 );
46 p_dqm_context := p_x_search_ctx_id;
47 p_dqm_count := p_x_num_matches;
48 EXCEPTION
49 WHEN OTHERS THEN
50 raise_application_error(-20001, SQLERRM);
51 END find_party;
52
53
54
55 /* This function returns the Legal Entity Id associated with the
56 * party that is passed. This Legal Entity should have a valid
57 * "Intercompany Legal Entity" relationship with the party thats is passed*/
58
59 FUNCTION get_le_id (p_party_id IN NUMBER , p_as_date IN DATE ) RETURN NUMBER is
60 l_count_le NUMBER ;
61 l_le_id NUMBER;
62 BEGIN
63 SELECT count(1)
64 INTO l_count_le
65 FROM xle_firstparty_information_v FAL
66 WHERE FAL.party_id = p_party_id;
67 IF(l_count_le>0) THEN
68 RETURN p_party_id;
69 ELSE
70 -- hzr has time component in start and end dates
71 SELECT hzr.object_id
72 INTO l_le_id
73 FROM hz_relationships hzr
74 WHERE HZR.subject_id=p_party_id
75 AND hzr.subject_table_name='HZ_PARTIES'
76 AND hzr.object_table_name='HZ_PARTIES'
77 AND hzr.relationship_code='INTERCOMPANY_ORGANIZATION_OF'
78 AND hzr.relationship_type='INTERCOMPANY_LEGAL_ENTITY'
79 AND hzr.directional_flag='F'
80 AND hzr.status='A'
81 AND TRUNC(start_date) <= nvl(p_as_date ,sysdate)
82 AND (TRUNC(end_date) >= nvl(p_as_date,sysdate) OR end_date IS NULL);
83 RETURN l_le_id;
84 END IF;
85 EXCEPTION
86 WHEN NO_DATA_FOUND THEN
87 RETURN NULL;
88 END get_le_id;
89
90
91
92 /* This function returns the Operating unit Id thats has a valid
93 * "Intercompany Operating Unit" relation with the party that is passed*/
94
95 FUNCTION get_ou_id (p_party_id IN NUMBER , p_as_date IN DATE )
96 RETURN NUMBER is
97 l_ou_id NUMBER;
98 BEGIN
99 SELECT hzr.subject_id into l_ou_id
100 FROM hz_relationships hzr
101 WHERE HZR.object_id=p_party_id
102 AND hzr.subject_table_name='HR_ALL_ORGANIZATION_UNITS'
103 AND hzr.object_table_name='HZ_PARTIES'
104 AND hzr.relationship_type='INTERCOMPANY_OPERATING_UNIT'
105 AND hzr.relationship_code='OPERATING_UNIT_OF'
106 AND hzr.directional_flag='B'
107 AND hzr.status='A'
108 AND TRUNC(start_date) <=nvl(p_as_date ,sysdate)
109 AND (TRUNC(end_date) >= nvl(p_as_date ,sysdate) OR end_date IS NULL);
110 RETURN l_ou_id;
111 EXCEPTION
112 WHEN NO_DATA_FOUND THEN
113 RETURN NULL;
114 WHEN TOO_MANY_ROWS THEN
115 RETURN NULL;
116 END get_ou_id;
117
118
119
120
121 /* This function returns the System Reference for the party that is passed
122 * This function will undergo further changes after input from
123 * the Source System Management Team -- open issue 2 --*/
124
125 FUNCTION get_system_reference(p_party_id NUMBER)
126 RETURN VARCHAR2 is
127 l_sys_ref VARCHAR2(240);
128 BEGIN
129 SELECT orig_system_reference
130 INTO l_sys_ref
131 FROM hz_parties
132 WHERE party_id = p_party_id;
133 RETURN l_sys_ref;
134 EXCEPTION
135 WHEN OTHERS THEN
136 raise_application_error(20001, SQLERRM);
137 END get_system_reference;
138
139
140
141
142 /* This function returns "Y" or "N" depending on the Org thats passed
143 * has a intercompany classification or not*/
144
145 FUNCTION is_intercompany_org (p_party_id NUMBER)
146 RETURN VARCHAR2 is
147 l_status VARCHAR2(10);
148 BEGIN
149 SELECT status
150 INTO l_status
151 FROM hz_parties
152 WHERE party_id=p_party_id;
153 IF(l_status <>'A') THEN RETURN 'N';
154 END IF;
155
156 SELECT status_flag
157 INTO l_status
158 FROM hz_party_usg_assignments hua
159 WHERE hua.party_id = p_party_id
160 AND hua.party_usage_code = 'INTERCOMPANY_ORG';
161 /*
162 SELECT status
163 INTO l_status
164 FROM HZ_CODE_ASSIGNMENTS hca
165 WHERE hca.owner_table_id=p_party_id
166 AND hca.class_category='INTERCOMPANY'
167 AND hca.class_code ='INTERCOMPANY'
168 AND hca.owner_table_name='HZ_PARTIES' ;
169 */
170 IF(l_status = 'A') THEN RETURN 'Y';
171 ELSE RETURN 'N';
172 END IF;
173 EXCEPTION
174 WHEN NO_DATA_FOUND THEN
175 RETURN 'N';
176 END is_intercompany_org;
177
178
179 /* This function returns "Y" or "N" depending on the Org thats passed
180 * is a valid intercompany or not*/
181
182 FUNCTION is_intercompany_org_valid (p_party_id NUMBER , p_as_date DATE )
183 RETURN VARCHAR2 is
184 l_status VARCHAR2(10);
185 BEGIN
186 SELECT status
187 INTO l_status
188 FROM hz_parties
189 WHERE party_id=p_party_id;
190 IF(l_status <>'A') THEN RETURN 'N';
191 END IF;
192
193 SELECT status_flag
194 INTO l_status
195 FROM hz_party_usg_assignments hua
196 WHERE hua.party_id = p_party_id
197 AND hua.party_usage_code = 'INTERCOMPANY_ORG'
198 AND hua.effective_start_date <= NVL(p_as_date, sysdate)
199 AND (hua.effective_end_date >= NVL(p_as_date, sysdate) OR effective_end_date IS NULL);
200
201 /* SELECT status
202 INTO l_status
203 FROM HZ_CODE_ASSIGNMENTS hca
204 WHERE hca.owner_table_id=p_party_id
205 AND hca.class_category='INTERCOMPANY'
206 AND hca.class_code ='INTERCOMPANY'
207 AND hca.owner_table_name='HZ_PARTIES'
208 AND start_date_active<=nvl(p_as_date ,sysdate)
209 AND (end_date_active >= nvl(p_as_date ,sysdate) OR end_date_active IS NULL);
210 */
211 IF(l_status = 'A') THEN RETURN 'Y';
212 ELSE RETURN 'N';
213 END IF;
214 EXCEPTION
215 WHEN NO_DATA_FOUND THEN
216 RETURN 'N';
217 END is_intercompany_org_valid;
218
219 /**This procedure will return the Efffective Start Date and Effective End Date for the Orgnazition id passed
220 */
221
222 PROCEDURE get_ic_org_valid_dates (p_party_id IN NUMBER, effective_start_date OUT NOCOPY DATE, effective_end_date OUT NOCOPY DATE)
223 is
224 CURSOR ic_valid_dates IS
225 SELECT hua.effective_start_date, hua.effective_end_date
226 FROM hz_party_usg_assignments hua
227 WHERE hua.party_id = p_party_id
228 AND hua.party_usage_code = 'INTERCOMPANY_ORG'
229 AND hua.effective_start_date = (
230 SELECT max(effective_start_date)
231 FROM hz_party_usg_assignments
232 WHERE party_id = p_party_id
233 AND party_usage_code = 'INTERCOMPANY_ORG'
234 );
235 BEGIN
236
237 OPEN ic_valid_dates;
238 FETCH ic_valid_dates INTO effective_start_date, effective_end_date;
239 CLOSE ic_valid_dates;
240 EXCEPTION
241 WHEN NO_DATA_FOUND THEN
242 effective_start_date := NULL;
243 effective_end_date := NULL;
244 END get_ic_org_valid_dates;
245
246 FUNCTION CF_TRANSACTING_ENTITY_FLAG(p_party_id in NUMBER, p_date DATE ) RETURN VARCHAR2
247 IS
248 --l_code_assignment_rec hz_classification_v2pub.code_assignment_rec_type;
249 l_party_usg_assignment_rec HZ_PARTY_USG_ASSIGNMENT_PVT.party_usg_assignment_rec_type;
250 l_return_status varchar2(2000);
251 l_msg_count number;
252 l_msg_data varchar2(2000);
253 --l_code_assignment_id number;
254
255 BEGIN
256
257 --- Classifying Legal Entity as Intercompany
258 /*
259 l_code_assignment_rec.owner_table_name := 'HZ_PARTIES';
260 l_code_assignment_rec.owner_table_id := p_party_id;
261 l_code_assignment_rec.class_category := 'INTERCOMPANY';
262 l_code_assignment_rec.class_code := 'INTERCOMPANY';
263 l_code_assignment_rec.primary_flag := 'Y';
264 l_code_assignment_rec.content_source_type := 'USER_ENTERED';
265 l_code_assignment_rec.start_date_active := nvl(p_date,sysdate);
266 l_code_assignment_rec.created_by_module := 'FUN_AGIS';
267 l_code_assignment_rec.application_id := 435;
268
269 HZ_CLASSIFICATION_V2PUB.create_Code_Assignment(
270 p_init_msg_list => 'F',
271 p_code_assignment_rec => l_code_assignment_rec,
272 x_return_status => l_return_status,
273 x_msg_count => l_msg_count,
274 x_msg_data => l_msg_data,
275 x_code_assignment_id => l_code_assignment_id
276 );
277 */
278 l_party_usg_assignment_rec.party_id := p_party_id;
279 l_party_usg_assignment_rec.party_usage_code := 'INTERCOMPANY_ORG';
280 l_party_usg_assignment_rec.created_by_module := 'FUN_AGIS';
281
282 HZ_PARTY_USG_ASSIGNMENT_PUB.assign_party_usage(
283 p_init_msg_list => 'F',
284 p_party_usg_assignment_rec => l_party_usg_assignment_rec,
285 x_return_status => l_return_status,
286 x_msg_count => l_msg_count,
287 x_msg_data => l_msg_data
288 );
289
290 /* BUG NO: 6001688
291 The function was exiting without returning a value.
292 Changes are made for the function to return l_return_status
293 */
294
295 return l_return_status;
296
297 END CF_TRANSACTING_ENTITY_FLAG;
298
299 /*
300 Bug No: 6146773. Check for the existence of the given party name.
301 */
302 PROCEDURE is_party_exist (l_party_name in VARCHAR2, flag out NOCOPY VARCHAR2)
303 IS
304
305 CURSOR chk_party IS
306 SELECT 'Y'
307 FROM hz_parties h, hz_party_usg_assignments hua
308 WHERE h.party_name = l_party_name
309 AND hua.party_id = h.party_id
310 AND hua.party_usage_code = 'INTERCOMPANY_ORG';
311 BEGIN
312
313 OPEN chk_party;
314
315 FETCH chk_party INTO flag;
316
317 if (chk_party%found) then
318
319 flag := 'Y';
320 else
321
322 flag := 'N';
323 end if;
324
325 CLOSE chk_party;
326 END is_party_exist;
327
328 END FUN_TCA_PKG;
[Home] [Help]