[Home] [Help]
PACKAGE BODY: APPS.WSH_ITM_PARTY_SYNC
Source
1 PACKAGE BODY WSH_ITM_PARTY_SYNC AS
2 /* $Header: WSHITPSB.pls 120.5.12010000.2 2008/08/13 13:09:32 sankarun ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_ITM_PARTY_SYNC';
5
6 /*===========================================================================+
7 | PROCEDURE |
8 | POPULATE_DATA |
9 | |
10 | DESCRIPTION |
11 | This procedure is called when the Party Synchronization |
12 | Concurrent Program is launched. It populates the data |
13 | into WSH_ITM_REQUEST_CONTROL and WSH_ITM_PARTIES |
14 | based on the parameters selected. |
15 | |
16 +===========================================================================*/
17
18 PROCEDURE POPULATE_DATA (
19 errbuf OUT NOCOPY VARCHAR2,
20 retcode OUT NOCOPY NUMBER,
21 p_party_type IN VARCHAR2 ,
22 p_from_party_code IN VARCHAR2 ,
23 p_to_party_code IN VARCHAR2 ,
24 p_dummy IN NUMBER DEFAULT NULL,
25 p_site_use_code IN VARCHAR2 ,
26 p_created_n_days IN NUMBER ,
27 p_updated_n_days IN NUMBER
28 )IS
29 l_SQLQuery VARCHAR2(12000);
30
31 l_CustSQLQuery VARCHAR2(12000) := ' SELECT '||
32 'site_uses.site_use_id source_id,'||
33 'party.party_id party_id, '||
34 'party_site.party_site_id party_site_id, '||
35 'acct_site.cust_account_id custAcctId,'||
36 'site_uses.site_use_code partnrtype,'||
37 'party.party_name party_name,'||
38 'decode(party.party_type,''ORGANIZATION'',party.organization_name_phonetic,person_profile.person_name_phonetic) alternate_name,'||
39 'party.tax_reference tax_reference,'||
40 'loc.address1 address1,'||
41 'loc.address2 address2,'||
42 'loc.address3 address3,'||
43 'loc.address4 address4,'||
44 'loc.city city,'||
45 'loc.state state,'||
46 'loc.country country,'||
47 'loc.postal_code postal_code,'||
48 'acct.account_number acct_number,'||
49 'acct.account_name acct_name,'||
50 'party_site.party_site_number site_number,'||
51 'acct.attribute1 acct_attribute1,'||
52 'acct.attribute2 acct_attribute2,'||
53 'acct.attribute3 acct_attribute3,'||
54 'acct.attribute4 acct_attribute4,'||
55 'acct.attribute5 acct_attribute5,'||
56 'acct.attribute6 acct_attribute6,'||
57 'acct.attribute7 acct_attribute7,'||
58 'acct.attribute8 acct_attribute8,'||
59 'acct.attribute9 acct_attribute9,'||
60 'acct.attribute10 acct_attribute10,'||
61 'acct.attribute11 acct_attribute11,'||
62 'acct.attribute12 acct_attribute12,'||
63 'acct.attribute13 acct_attribute13,'||
64 'acct.attribute14 acct_attribute14,'||
65 'acct.attribute15 acct_attribute15,'||
66 'acct_site.attribute1 acct_site_attribute1,'||
67 'acct_site.attribute2 acct_site_attribute2,'||
68 'acct_site.attribute3 acct_site_attribute3,'||
69 'acct_site.attribute4 acct_site_attribute4,'||
70 'acct_site.attribute5 acct_site_attribute5,'||
71 'acct_site.attribute6 acct_site_attribute6,'||
72 'acct_site.attribute7 acct_site_attribute7,'||
73 'acct_site.attribute8 acct_site_attribute8,'||
74 'acct_site.attribute9 acct_site_attribute9,'||
75 'acct_site.attribute10 acct_site_attribute10,'||
76 'acct_site.attribute11 acct_site_attribute11,'||
77 'acct_site.attribute12 acct_site_attribute12,'||
78 'acct_site.attribute13 acct_site_attribute13,'||
79 'acct_site.attribute14 acct_site_attribute14,'||
80 'acct_site.attribute15 acct_site_attribute15, ' ||
81 'acct_site.org_id operating_unit,' ||
82 'site_uses.primary_flag address_type ' ||
83 ' FROM '||
84 'hz_cust_site_uses_all site_uses,'||
85 'hz_cust_acct_sites_all acct_site,'||
86 'hz_cust_accounts acct,'||
87 'hz_party_sites party_site,'||
88 'hz_parties party,'||
89 'hz_locations loc ,'||
90 'hz_Person_profiles person_profile '||
91 'WHERE '||
92 ' site_uses.CUST_ACCT_SITE_ID = acct_site.CUST_ACCT_SITE_ID'||
93 ' AND acct_site.PARTY_SITE_ID = party_site.PARTY_SITE_ID'||
94 ' AND party_site.PARTY_ID = party.PARTY_ID'||
95 ' AND loc.LOCATION_ID = party_site.LOCATION_ID'||
96 ' AND acct.CUST_ACCOUNT_ID = acct_site.CUST_ACCOUNT_ID'||
97 ' AND acct.STATUS = ''A'' '||
98 ' AND party.PARTY_ID = person_profile.PARTY_ID(+) ';
99
100 l_ContactQuery VARCHAR2(1000) := ' OR party.party_id IN' || --TCA view removal starts
101 '( SELECT hr.object_id FROM hz_relationships hr,' ||
102 ' hz_Parties hp,' ||
103 'hz_contact_points hcp,' ||
104 ' hz_org_contacts hoc' ||
105 ' WHERE '||
106 ' hr.subject_table_name = ''HZ_PARTIES'' ' ||
107 ' AND hr.object_table_name =''HZ_PARTIES'' ' ||
108 ' AND hr.directional_flag = ''F'' ' ||
109 ' AND hr.relationship_type = ''CONTACT_OF'' '||
110 ' AND hr.subject_id = hp.party_id '||
111 ' AND hcp.owner_table_id = hr.party_id '||
112 ' AND hoc.party_relationship_id = hr.relationship_id '; --TCA view removal ends
113
114
115
116
117 l_CarrierSQLQuery VARCHAR2(12000) := ' SELECT '||
118 'to_number(null) source_id,'||
119 'party.party_id party_id, '||
120 'party_site.party_site_id party_site_id, '||
121 'to_number(null) custAcctId,'||
122 '''CARRIER'' partnrtype,'||
123 'party.party_name party_name,'||
124 'party.organization_name_phonetic alternate_name,'||
125 'party.tax_reference tax_reference,'||
126 'loc.address1 address1,'||
127 'loc.address2 address2,'||
128 'loc.address3 address3,'||
129 'loc.address4 address4,'||
130 'loc.city city,'||
131 'loc.state state,'||
132 'loc.country country,'||
133 'loc.postal_code postal_code,'||
134 'to_char(null) acct_number,'||
135 'to_char(null) acct_name,'||
136 'party_site.party_site_number site_number,'||
137 'party.attribute1 acct_attribute1,'||
138 'party.attribute2 acct_attribute2,'||
139 'party.attribute3 acct_attribute3,'||
140 'party.attribute4 acct_attribute4,'||
141 'party.attribute5 acct_attribute5,'||
142 'party.attribute6 acct_attribute6,'||
143 'party.attribute7 acct_attribute7,'||
144 'party.attribute8 acct_attribute8,'||
145 'party.attribute9 acct_attribute9,'||
146 'party.attribute10 acct_attribute10,'||
147 'party.attribute11 acct_attribute11,'||
148 'party.attribute12 acct_attribute12,'||
149 'party.attribute13 acct_attribute13,'||
150 'party.attribute14 acct_attribute14,'||
151 'party.attribute15 acct_attribute15,'||
152 'party_site.attribute1 acct_site_attribute1,'||
153 'party_site.attribute2 acct_site_attribute2,'||
154 'party_site.attribute3 acct_site_attribute3,'||
155 'party_site.attribute4 acct_site_attribute4,'||
156 'party_site.attribute5 acct_site_attribute5,'||
157 'party_site.attribute6 acct_site_attribute6,'||
158 'party_site.attribute7 acct_site_attribute7,'||
159 'party_site.attribute8 acct_site_attribute8,'||
160 'party_site.attribute9 acct_site_attribute9,'||
161 'party_site.attribute10 acct_site_attribute10,'||
162 'party_site.attribute11 acct_site_attribute11,'||
163 'party_site.attribute12 acct_site_attribute12,'||
164 'party_site.attribute13 acct_site_attribute13,'||
165 'party_site.attribute14 acct_site_attribute14,'||
166 'party_site.attribute15 acct_site_attribute15,' ||
167 ' null, ' ||
168 'party_site.identifying_address_flag address_type ' ||
169 ' FROM '||
170 'hz_party_sites party_site,'||
171 'hz_parties party,'||
172 'hz_locations loc ,'||
173 'wsh_carriers_v wsh_car '||
174 'WHERE '||
175 ' party_site.party_id = party.party_id'||
176 ' AND wsh_car.ACTIVE = ''A'' '||
177 ' AND party.party_id = wsh_car.carrier_id'||
178 ' AND loc.location_id = party_site.location_id';
179 l_Party_Table WSH_ITM_QUERY_CUSTOM.g_CondnValTableType;
180
181 l_Party_Condn1Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
182 l_Party_Condn11Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
183 l_Party_Condn2Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
184 l_Party_Condn3Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
185 l_Party_Condn4Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
186
187
188 l_Carrier_Table WSH_ITM_QUERY_CUSTOM.g_CondnValTableType;
189
190 l_Carrier_Condn1Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
191 l_Carrier_Condn11Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
192 l_Carrier_Condn2Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
193 l_Carrier_Condn3Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
194
195
196 l_tempStr1 VARCHAR2(10000) := ' ';
197 l_tempStr2 VARCHAR2(10000) := ' ';
198 l_CursorID NUMBER;
199 l_ignore NUMBER;
200
201 --PL/SQL Table used for Bulk Select
202 l_num_sourceID_tab DBMS_SQL.Number_Table;
203 l_num_custAccountID_tab DBMS_SQL.Number_Table;
204 l_num_hzpartyID_tab DBMS_SQL.Number_Table;
205
206 l_num_hzpartySiteID_tab DBMS_SQL.Number_Table;
207 l_num_itmpartyID_tab DBMS_SQL.Number_Table;
208
209 l_num_partyrel_tab DBMS_SQL.Number_Table;
210 l_varchar_PartnrType_tab DBMS_SQL.Varchar2_Table;
211 l_varchar_PartyName_tab DBMS_SQL.Varchar2_Table;
212 l_varchar_AlternateName_tab DBMS_SQL.Varchar2_Table;
213 l_varchar_Address1_tab DBMS_SQL.Varchar2_Table;
214 l_varchar_Address2_tab DBMS_SQL.Varchar2_Table;
215 l_varchar_Address3_tab DBMS_SQL.Varchar2_Table;
216 l_varchar_Address4_tab DBMS_SQL.Varchar2_Table;
217 l_varchar_City_tab DBMS_SQL.Varchar2_Table;
218 l_varchar_State_tab DBMS_SQL.Varchar2_Table;
219 l_varchar_Country_tab DBMS_SQL.Varchar2_Table;
220 l_varchar_PostalCode_tab DBMS_SQL.Varchar2_Table;
221 l_varchar_TaxRef_tab DBMS_SQL.Varchar2_Table;
222 l_varchar_AcctNumber_tab DBMS_SQL.Varchar2_Table;
223 l_varchar_AcctName_tab DBMS_SQL.Varchar2_Table;
224 l_varchar_SiteNumber_tab DBMS_SQL.Varchar2_Table;
225
226
227 l_varchar_CtPartyName_tab DBMS_SQL.Varchar2_Table;
228 l_varchar_CtPointType_tab DBMS_SQL.Varchar2_Table;
229 l_varchar_ctEmail_tab DBMS_SQL.Varchar2_Table;
230 l_varchar_ctPhone_tab DBMS_SQL.Varchar2_Table;
231 l_varchar_ctFax_tab DBMS_SQL.Varchar2_Table;
232
233 l_varchar_tempAttrib1_tab DBMS_SQL.Varchar2_Table;
234 l_varchar_tempAttrib2_tab DBMS_SQL.Varchar2_Table;
235 l_varchar_tempAttrib3_tab DBMS_SQL.Varchar2_Table;
236 l_varchar_tempAttrib4_tab DBMS_SQL.Varchar2_Table;
237 l_varchar_tempAttrib5_tab DBMS_SQL.Varchar2_Table;
238 l_varchar_tempAttrib6_tab DBMS_SQL.Varchar2_Table;
239 l_varchar_tempAttrib7_tab DBMS_SQL.Varchar2_Table;
240 l_varchar_tempAttrib8_tab DBMS_SQL.Varchar2_Table;
241 l_varchar_tempAttrib9_tab DBMS_SQL.Varchar2_Table;
242 l_varchar_tempAttrib10_tab DBMS_SQL.Varchar2_Table;
243 l_varchar_tempAttrib11_tab DBMS_SQL.Varchar2_Table;
244 l_varchar_tempAttrib12_tab DBMS_SQL.Varchar2_Table;
245 l_varchar_tempAttrib13_tab DBMS_SQL.Varchar2_Table;
246 l_varchar_tempAttrib14_tab DBMS_SQL.Varchar2_Table;
247 l_varchar_tempAttrib15_tab DBMS_SQL.Varchar2_Table;
248
249
250 l_varchar_rcAttrib1_tab DBMS_SQL.Varchar2_Table;
251 l_varchar_rcAttrib2_tab DBMS_SQL.Varchar2_Table;
252 l_varchar_rcAttrib3_tab DBMS_SQL.Varchar2_Table;
253 l_varchar_rcAttrib4_tab DBMS_SQL.Varchar2_Table;
254 l_varchar_rcAttrib5_tab DBMS_SQL.Varchar2_Table;
255 l_varchar_rcAttrib6_tab DBMS_SQL.Varchar2_Table;
256 l_varchar_rcAttrib7_tab DBMS_SQL.Varchar2_Table;
257 l_varchar_rcAttrib8_tab DBMS_SQL.Varchar2_Table;
258 l_varchar_rcAttrib9_tab DBMS_SQL.Varchar2_Table;
259 l_varchar_rcAttrib10_tab DBMS_SQL.Varchar2_Table;
260 l_varchar_rcAttrib11_tab DBMS_SQL.Varchar2_Table;
261 l_varchar_rcAttrib12_tab DBMS_SQL.Varchar2_Table;
262 l_varchar_rcAttrib13_tab DBMS_SQL.Varchar2_Table;
263 l_varchar_rcAttrib14_tab DBMS_SQL.Varchar2_Table;
264 l_varchar_rcAttrib15_tab DBMS_SQL.Varchar2_Table;
265
266
267 l_varchar_Attrib1_tab DBMS_SQL.Varchar2_Table;
268 l_varchar_Attrib2_tab DBMS_SQL.Varchar2_Table;
269 l_varchar_Attrib3_tab DBMS_SQL.Varchar2_Table;
270 l_varchar_Attrib4_tab DBMS_SQL.Varchar2_Table;
271 l_varchar_Attrib5_tab DBMS_SQL.Varchar2_Table;
272 l_varchar_Attrib6_tab DBMS_SQL.Varchar2_Table;
273 l_varchar_Attrib7_tab DBMS_SQL.Varchar2_Table;
274 l_varchar_Attrib8_tab DBMS_SQL.Varchar2_Table;
275 l_varchar_Attrib9_tab DBMS_SQL.Varchar2_Table;
276 l_varchar_Attrib10_tab DBMS_SQL.Varchar2_Table;
277 l_varchar_Attrib11_tab DBMS_SQL.Varchar2_Table;
278 l_varchar_Attrib12_tab DBMS_SQL.Varchar2_Table;
279 l_varchar_Attrib13_tab DBMS_SQL.Varchar2_Table;
280 l_varchar_Attrib14_tab DBMS_SQL.Varchar2_Table;
281 l_varchar_Attrib15_tab DBMS_SQL.Varchar2_Table;
282
283 --Bug 7297690 Added variable to store the party site id and Org contact id of the contacts who will be screened
284 l_org_contact_id_tab DBMS_SQL.Number_Table;
285 Type l_org_contact_type is table of Varchar2(1) index by BINARY_INTEGER;
286 l_org_contact_cache l_org_contact_type;
287 Type l_party_site_id_type is table of Varchar2(1) index by BINARY_INTEGER;
288 l_party_site_id_cache l_party_site_id_type;
289
290 l_operating_unit DBMS_SQL.Number_Table;
291 l_address_type DBMS_SQL.Varchar2_Table;
292 l_email VARCHAR2(100);
293 l_phone VARCHAR2(100);
294 l_fax VARCHAR2(100);
295
296 --For Insert to ITM Inteface Tables
297 l_num_ReqCtrl_tab DBMS_SQL.Number_Table;
298 l_num_PartyReqCtrl_tab DBMS_SQL.Number_Table;
299 l_tempSourceID NUMBER := -999;
300 j NUMBER;
301
302 l_user_id NUMBER;
303 l_login_id NUMBER;
304 l_temp BOOLEAN;
305 l_LanguageCode VARCHAR2(4);
306
307 --Party name
308 l_from_party VARCHAR2(360);
309 l_to_party VARCHAR2(360);
310
311
312 CURSOR cur_customer_party_name(c_account_id varchar2) IS
313 SELECT
314 HP.party_name
315 FROM
316 hz_parties HP,
317 HZ_CUST_ACCOUNTS HC
318 WHERE
319 HP.PARTY_ID = HC.PARTY_ID AND
320 hc.cust_account_id = TO_NUMBER(c_account_id);
321
322
323
324 CURSOR cur_carrier_party_name(c_freight_code varchar2) IS
325 SELECT
326 HP.PARTY_NAME
327 FROM
328 WSH_CARRIERS WC,
329 HZ_PARTIES HP
330 WHERE
331 HP.PARTY_ID = WC.CARRIER_ID AND
332 wc.freight_code = c_freight_code;
333
334
335 --
336 l_debug_on BOOLEAN;
337 --
338 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'POPULATE_DATA';
339 --
340 BEGIN
341 --Frame draft SQL
342
343 --
344 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
345 --
346 IF l_debug_on IS NULL
347 THEN
348 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
349 END IF;
350 --
351 --
352 -- Debug Statements
353 --
354 IF l_debug_on THEN
355 WSH_DEBUG_SV.push(l_module_name);
356 --
357 WSH_DEBUG_SV.log(l_module_name,'P_PARTY_TYPE',P_PARTY_TYPE);
358 WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_CODE',P_FROM_PARTY_CODE);
359 WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_CODE',P_TO_PARTY_CODE);
360 WSH_DEBUG_SV.log(l_module_name,'P_DUMMY',P_DUMMY);
361 WSH_DEBUG_SV.log(l_module_name,'P_SITE_USE_CODE',P_SITE_USE_CODE);
362 WSH_DEBUG_SV.log(l_module_name,'P_CREATED_N_DAYS',P_CREATED_N_DAYS);
363 WSH_DEBUG_SV.log(l_module_name,'P_UPDATED_N_DAYS',P_UPDATED_N_DAYS);
364 END IF;
365 --
366
367 -- Fetch user and login information
368 l_user_id := FND_GLOBAL.USER_ID;
369 l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
370
371 IF p_from_party_code IS NOT NULL THEN
372
373 IF p_party_type = 'CUSTOMER' THEN
374
375 OPEN cur_customer_party_name(p_from_party_code);
376 FETCH cur_customer_party_name into l_from_party;
377 CLOSE cur_customer_party_name;
378
379 l_Party_Condn1Tab(1).g_varchar_val := l_from_party;
380 l_Party_Condn1Tab(1).g_Bind_Literal := ':b_from_party';
381 --
382 -- Debug Statements
383 --
384 IF l_debug_on THEN
385 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
386 END IF;
387 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' AND PARTY.PARTY_NAME >= :b_from_party', l_Party_Condn1Tab, 'VARCHAR');
388
389 ELSE
390
391 OPEN cur_carrier_party_name(p_from_party_code);
392 FETCH cur_carrier_party_name into l_from_party;
393 CLOSE cur_carrier_party_name;
394
395 l_Carrier_Condn1Tab(1).g_varchar_val := l_from_party;
396 l_Carrier_Condn1Tab(1).g_Bind_Literal := ':b_from_party';
397 --
398 -- Debug Statements
399 --
400 IF l_debug_on THEN
401 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
402 END IF;
403 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' AND PARTY.PARTY_NAME >= :b_from_party', l_Carrier_Condn1Tab, 'VARCHAR');
404
405 END IF;
406 END IF;
407
408 IF p_to_party_code IS NOT NULL THEN
409
410 IF p_party_type = 'CUSTOMER' THEN
411
412 OPEN cur_customer_party_name(p_to_party_code);
413 FETCH cur_customer_party_name into l_to_party;
414 CLOSE cur_customer_party_name;
415
416 l_Party_Condn11Tab(1).g_varchar_val := l_to_party;
417 l_Party_Condn11Tab(1).g_Bind_Literal := ':b_to_party';
418 --
419 -- Debug Statements
420 --
421 IF l_debug_on THEN
422 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
423 END IF;
424 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' AND PARTY.PARTY_NAME <= :b_to_party', l_Party_Condn11Tab, 'VARCHAR');
425 ELSE
426
427 OPEN cur_carrier_party_name(p_to_party_code);
428 FETCH cur_carrier_party_name into l_to_party;
429 CLOSE cur_carrier_party_name;
430
431 l_Carrier_Condn11Tab(1).g_varchar_val := l_to_party;
432 l_Carrier_Condn11Tab(1).g_Bind_Literal := ':b_to_party';
433 --
434 -- Debug Statements
435 --
436 IF l_debug_on THEN
437 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
438 END IF;
439 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' AND PARTY.PARTY_NAME <= :b_to_party', l_Carrier_Condn11Tab, 'VARCHAR');
440
441 END IF;
442 END IF;
443
444
445
446 --Adding Business Purpose Condn
447 IF p_site_use_code IS NOT NULL THEN
448 l_Party_Condn2Tab(1).g_varchar_val := p_site_use_code;
449 l_Party_Condn2Tab(1).g_Bind_Literal := ':b_site_use_code';
450 --
451 -- Debug Statements
452 --
453 IF l_debug_on THEN
454 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
455 END IF;
456 --
457 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' AND SITE_USES.SITE_USE_CODE = :b_site_use_code', l_Party_Condn2Tab, 'VARCHAR');
458 END IF;
459
460 --Adding Creates Last N Days Condn
461 IF p_created_n_days IS NOT NULL THEN
462 IF p_party_type = 'CUSTOMER' OR p_party_type is NULL THEN
463 l_Party_Condn3Tab(1).g_number_val := p_created_n_days;
464 l_Party_Condn3Tab(1).g_Bind_Literal := ':b_created_n_days';
465 END IF;
466 IF p_party_type = 'CARRIER' OR p_party_type is NULL THEN
467 l_Carrier_Condn2Tab(1).g_number_val := p_created_n_days;
468 l_Carrier_Condn2Tab(1).g_Bind_Literal := ':b_created_n_days';
469 END IF;
470
471 --
472 -- Debug Statements
473 --
474 IF l_debug_on THEN
475 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
476 END IF;
477 --
478 IF p_party_type = 'CUSTOMER' OR p_party_type is NULL THEN
479
480 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' AND (SITE_USES.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Party_Condn3Tab, 'NUMBER');
481 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR ACCT.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Party_Condn3Tab, 'NUMBER');
482 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR ACCT_SITE.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Party_Condn3Tab, 'NUMBER');
483 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR PARTY_SITE.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Party_Condn3Tab, 'NUMBER');
484 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR LOC.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Party_Condn3Tab, 'NUMBER');
485 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR PARTY.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Party_Condn3Tab, 'NUMBER');
486 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, l_ContactQuery || ' and ( hoc.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Party_Condn3Tab, 'NUMBER');
487 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' or hcp.CREATION_DATE >= SYSDATE - :b_created_n_days))) ', l_Party_Condn3Tab, 'NUMBER');
488 END IF;
489 IF p_party_type = 'CARRIER' OR p_party_type is NULL THEN
490 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' AND ( PARTY_SITE.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Carrier_Condn2Tab, 'NUMBER');
491 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' OR PARTY.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Carrier_Condn2Tab, 'NUMBER');
492 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' OR LOC.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Carrier_Condn2Tab, 'NUMBER');
493 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, l_ContactQuery || ' and ( hoc.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Carrier_Condn2Tab, 'NUMBER');
494 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' or hcp.CREATION_DATE >= SYSDATE - :b_created_n_days))) ', l_Carrier_Condn2Tab, 'NUMBER');
495
496 END IF;
497 END IF;
498
499 --Adding Creates Last N Days Condn
500 IF p_updated_n_days IS NOT NULL THEN
501 IF p_party_type = 'CUSTOMER' OR p_party_type is NULL THEN
502 l_Party_Condn4Tab(1).g_number_val := p_updated_n_days;
503 l_Party_Condn4Tab(1).g_Bind_Literal := ':b_updated_n_days';
504 END IF;
505 IF p_party_type = 'CARRIER' OR p_party_type is NULL THEN
506 l_Carrier_Condn3Tab(1).g_number_val := p_updated_n_days;
507 l_Carrier_Condn3Tab(1).g_Bind_Literal := ':b_updated_n_days';
508 END IF;
509
510
511 --
512 -- Debug Statements
513 --
514 IF l_debug_on THEN
515 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
516 END IF;
517 --
518 IF p_party_type = 'CUSTOMER' OR p_party_type is NULL THEN
519 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' AND (SITE_USES.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
520 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR ACCT.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
521 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR ACCT_SITE.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
522 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR PARTY_SITE.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
523 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR LOC.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
524 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR PARTY.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
525 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, l_ContactQuery || ' and ( hoc.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
526 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' or hcp.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days))) ', l_Party_Condn4Tab, 'NUMBER');
527
528 END IF;
529 IF p_party_type = 'CARRIER' OR p_party_type is NULL THEN
530 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' AND (PARTY_SITE.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Carrier_Condn3Tab, 'NUMBER');
531 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' OR PARTY.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Carrier_Condn3Tab, 'NUMBER');
532 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' OR LOC.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Carrier_Condn3Tab, 'NUMBER');
533 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, l_ContactQuery || ' and ( hoc.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ',l_Carrier_Condn3Tab, 'NUMBER');
534 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' or hcp.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days))) ', l_Carrier_Condn3Tab, 'NUMBER');
535 END IF;
536 END IF;
537
538
539 --Call to custom Procedure which could be edited by the Customer.
540 --
541 -- Debug Statements
542 --
543 IF l_debug_on THEN
544 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_CUSTOMIZE.ALTER_PARTY_SYNC for Customer',WSH_DEBUG_SV.C_PROC_LEVEL);
545 END IF;
546 WSH_ITM_CUSTOMIZE.ALTER_PARTY_SYNC(l_Party_Table);
547
548 --
549 -- Debug Statements
550 --
551 IF l_debug_on THEN
552 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_CUSTOMIZE.ALTER_PARTY_SYNC for Carrier',WSH_DEBUG_SV.C_PROC_LEVEL);
553 END IF;
554 WSH_ITM_CUSTOMIZE.ALTER_PARTY_SYNC(l_Carrier_Table);
555
556
557 --Create SQL and bind parameters
558 FOR I IN 1..l_Party_Table.COUNT
559 LOOP
560 l_tempStr1 := l_tempStr1 || ' ' || l_Party_table(i).g_Condn_Qry;
561 END LOOP;
562
563 FOR I IN 1..l_Carrier_Table.COUNT
564 LOOP
565 l_tempStr2 := l_tempStr2 || ' ' || l_Carrier_table(i).g_Condn_Qry;
566 END LOOP;
567
568
569 --Concatenating Main SQL with Condition SQL
570 IF p_party_type = 'CUSTOMER' THEN
571 l_SQLQuery := l_CustSQLQuery || l_tempStr1;
572 END IF;
573 IF p_party_type = 'CARRIER' THEN
574 l_SQLQuery := l_CarrierSQLQuery || l_tempStr2;
575 END IF;
576
577 IF p_party_type is NULL THEN
578 l_SQLQuery := l_CustSQLQuery || l_tempStr1 || ' UNION ' || l_CarrierSQLQuery || l_tempStr2;
579 END IF;
580
581 IF l_debug_on THEN
582 WSH_DEBUG_SV.LOG (l_module_name, 'Query ', l_SQLQuery, WSH_DEBUG_SV.C_STMT_LEVEL);
583 END IF;
584
585 -- Parse cursor
586 l_CursorID := DBMS_SQL.Open_Cursor;
587 DBMS_SQL.PARSE(l_CursorID, l_SQLQuery, DBMS_SQL.v7);
588
589
590
591 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 1, l_num_sourceID_tab, 100, 0);
592 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 2, l_num_hzpartyID_tab, 100, 0);
593 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 3, l_num_hzpartySiteID_tab, 100, 0);
594 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 4, l_num_custAccountID_tab, 100, 0);
595 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 5, l_varchar_PartnrType_tab, 100, 0);
596 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 6, l_varchar_PartyName_tab, 100, 0);
597 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 7, l_varchar_AlternateName_tab, 100, 0);
598 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 8, l_varchar_TaxRef_tab, 100, 0);
599 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 9, l_varchar_Address1_tab, 100, 0);
600 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 10,l_varchar_Address2_tab, 100, 0);
601 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 11,l_varchar_Address3_tab, 100, 0);
602 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 12, l_varchar_Address4_tab, 100, 0);
603 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 13, l_varchar_City_tab, 100, 0);
604 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 14, l_varchar_State_tab, 100, 0);
605 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 15, l_varchar_Country_tab, 100, 0);
606 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 16, l_varchar_PostalCode_tab, 100, 0);
607 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 17, l_varchar_AcctNumber_tab , 100, 0);
608 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 18, l_varchar_AcctName_tab , 100, 0);
609 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 19, l_varchar_SiteNumber_tab , 100, 0);
610
611
612 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 20, l_varchar_tempAttrib1_tab, 100, 0);
613 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 21, l_varchar_tempAttrib2_tab, 100, 0);
614 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 22, l_varchar_tempAttrib3_tab, 100, 0);
615 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 23, l_varchar_tempAttrib4_tab, 100, 0);
616 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 24, l_varchar_tempAttrib5_tab, 100, 0);
617 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 25, l_varchar_tempAttrib6_tab, 100, 0);
618 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 26, l_varchar_tempAttrib7_tab, 100, 0);
619 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 27, l_varchar_tempAttrib8_tab, 100, 0);
620 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 28, l_varchar_tempAttrib9_tab, 100, 0);
621 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 29, l_varchar_tempAttrib10_tab, 100, 0);
622 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 30, l_varchar_tempAttrib11_tab, 100, 0);
623 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 31, l_varchar_tempAttrib12_tab, 100, 0);
624 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 32, l_varchar_tempAttrib13_tab, 100, 0);
625 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 33, l_varchar_tempAttrib14_tab, 100, 0);
626 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 34, l_varchar_tempAttrib15_tab, 100, 0);
627
628
629
630 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 35, l_varchar_Attrib1_tab, 100, 0);
631 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 36, l_varchar_Attrib2_tab, 100, 0);
632 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 37, l_varchar_Attrib3_tab, 100, 0);
633 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 38, l_varchar_Attrib4_tab, 100, 0);
634 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 39, l_varchar_Attrib5_tab, 100, 0);
635 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 40, l_varchar_Attrib6_tab, 100, 0);
636 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 41, l_varchar_Attrib7_tab, 100, 0);
637 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 42, l_varchar_Attrib8_tab, 100, 0);
638 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 43, l_varchar_Attrib9_tab, 100, 0);
639 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 44, l_varchar_Attrib10_tab, 100, 0);
640 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 45, l_varchar_Attrib11_tab, 100, 0);
641 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 46, l_varchar_Attrib12_tab, 100, 0);
642 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 47, l_varchar_Attrib13_tab, 100, 0);
643 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 48, l_varchar_Attrib14_tab, 100, 0);
644 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 49, l_varchar_Attrib15_tab, 100, 0);
645 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 50, l_operating_unit, 100, 0);
646 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 51, l_address_type, 100, 0);
647 --
648 -- Debug Statements
649 --
650 IF l_debug_on THEN
651 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.BIND_VALUES',WSH_DEBUG_SV.C_PROC_LEVEL);
652 END IF;
653 --
654 WSH_ITM_QUERY_CUSTOM.BIND_VALUES(l_Party_Table,l_CursorID);
655
656 --
657 -- Debug Statements
658 --
659 IF l_debug_on THEN
660 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.BIND_VALUES',WSH_DEBUG_SV.C_PROC_LEVEL);
661 END IF;
662 --
663 WSH_ITM_QUERY_CUSTOM.BIND_VALUES(l_Carrier_Table,l_CursorID);
664
665 IF l_debug_on THEN
666 WSH_DEBUG_SV.LOGMSG (l_module_name,'Successfull in binding values',WSH_DEBUG_SV.C_STMT_LEVEL);
667 END IF;
668
669 l_ignore := DBMS_SQL.EXECUTE(l_CursorID);
670
671
672 --Bulk Collect customized SQL
673 LOOP
674 l_ignore := DBMS_SQL.FETCH_ROWS(l_CursorID);
675
676
677 DBMS_SQL.COLUMN_VALUE(l_CursorID, 1, l_num_sourceID_tab);
678 DBMS_SQL.COLUMN_VALUE(l_CursorID, 2, l_num_hzpartyID_tab);
679 DBMS_SQL.COLUMN_VALUE(l_CursorID, 3, l_num_hzpartySiteID_tab);
680 DBMS_SQL.COLUMN_VALUE(l_CursorID, 4, l_num_custAccountID_tab);
681 DBMS_SQL.COLUMN_VALUE(l_CursorID, 5, l_varchar_PartnrType_tab);
682 DBMS_SQL.COLUMN_VALUE(l_CursorID, 6, l_varchar_PartyName_tab);
683 DBMS_SQL.COLUMN_VALUE(l_CursorID, 7, l_varchar_AlternateName_tab);
684 DBMS_SQL.COLUMN_VALUE(l_CursorID, 8, l_varchar_TaxRef_tab);
685 DBMS_SQL.COLUMN_VALUE(l_CursorID, 9, l_varchar_Address1_tab);
686 DBMS_SQL.COLUMN_VALUE(l_CursorID, 10,l_varchar_Address2_tab);
687 DBMS_SQL.COLUMN_VALUE(l_CursorID, 11, l_varchar_Address3_tab);
688 DBMS_SQL.COLUMN_VALUE(l_CursorID, 12, l_varchar_Address4_tab);
689 DBMS_SQL.COLUMN_VALUE(l_CursorID, 13, l_varchar_City_tab);
690 DBMS_SQL.COLUMN_VALUE(l_CursorID, 14, l_varchar_State_tab);
691 DBMS_SQL.COLUMN_VALUE(l_CursorID, 15, l_varchar_Country_tab);
692 DBMS_SQL.COLUMN_VALUE(l_CursorID, 16, l_varchar_PostalCode_tab);
693 DBMS_SQL.COLUMN_VALUE(l_CursorID, 17, l_varchar_AcctNumber_tab);
694 DBMS_SQL.COLUMN_VALUE(l_CursorID, 18, l_varchar_AcctName_tab);
695 DBMS_SQL.COLUMN_VALUE(l_CursorID, 19, l_varchar_SiteNumber_tab);
696
697 DBMS_SQL.COLUMN_VALUE(l_CursorID, 20, l_varchar_tempAttrib1_tab);
698 DBMS_SQL.COLUMN_VALUE(l_CursorID, 21, l_varchar_tempAttrib2_tab);
699 DBMS_SQL.COLUMN_VALUE(l_CursorID, 22, l_varchar_tempAttrib3_tab);
700 DBMS_SQL.COLUMN_VALUE(l_CursorID, 23, l_varchar_tempAttrib4_tab);
701 DBMS_SQL.COLUMN_VALUE(l_CursorID, 24, l_varchar_tempAttrib5_tab);
702 DBMS_SQL.COLUMN_VALUE(l_CursorID, 25, l_varchar_tempAttrib6_tab);
703 DBMS_SQL.COLUMN_VALUE(l_CursorID, 26, l_varchar_tempAttrib7_tab);
704 DBMS_SQL.COLUMN_VALUE(l_CursorID, 27, l_varchar_tempAttrib8_tab);
705 DBMS_SQL.COLUMN_VALUE(l_CursorID, 28, l_varchar_tempAttrib9_tab);
706 DBMS_SQL.COLUMN_VALUE(l_CursorID, 29, l_varchar_tempAttrib10_tab);
707 DBMS_SQL.COLUMN_VALUE(l_CursorID, 30, l_varchar_tempAttrib11_tab);
708 DBMS_SQL.COLUMN_VALUE(l_CursorID, 31, l_varchar_tempAttrib12_tab);
709 DBMS_SQL.COLUMN_VALUE(l_CursorID, 32, l_varchar_tempAttrib13_tab);
710 DBMS_SQL.COLUMN_VALUE(l_CursorID, 33, l_varchar_tempAttrib14_tab);
711 DBMS_SQL.COLUMN_VALUE(l_CursorID, 34, l_varchar_tempAttrib15_tab);
712
713 DBMS_SQL.COLUMN_VALUE(l_CursorID, 35, l_varchar_Attrib1_tab);
714 DBMS_SQL.COLUMN_VALUE(l_CursorID, 36, l_varchar_Attrib2_tab);
715 DBMS_SQL.COLUMN_VALUE(l_CursorID, 37, l_varchar_Attrib3_tab);
716 DBMS_SQL.COLUMN_VALUE(l_CursorID, 38, l_varchar_Attrib4_tab);
717 DBMS_SQL.COLUMN_VALUE(l_CursorID, 39, l_varchar_Attrib5_tab);
718 DBMS_SQL.COLUMN_VALUE(l_CursorID, 40, l_varchar_Attrib6_tab);
719 DBMS_SQL.COLUMN_VALUE(l_CursorID, 41, l_varchar_Attrib7_tab);
720 DBMS_SQL.COLUMN_VALUE(l_CursorID, 42, l_varchar_Attrib8_tab);
721 DBMS_SQL.COLUMN_VALUE(l_CursorID, 43, l_varchar_Attrib9_tab);
722 DBMS_SQL.COLUMN_VALUE(l_CursorID, 44, l_varchar_Attrib10_tab);
723 DBMS_SQL.COLUMN_VALUE(l_CursorID, 45, l_varchar_Attrib11_tab);
724 DBMS_SQL.COLUMN_VALUE(l_CursorID, 46, l_varchar_Attrib12_tab);
725 DBMS_SQL.COLUMN_VALUE(l_CursorID, 47, l_varchar_Attrib13_tab);
726 DBMS_SQL.COLUMN_VALUE(l_CursorID, 48, l_varchar_Attrib14_tab);
727 DBMS_SQL.COLUMN_VALUE(l_CursorID, 49, l_varchar_Attrib15_tab);
728 DBMS_SQL.COLUMN_VALUE(l_CursorID, 50, l_operating_unit);
729 DBMS_SQL.COLUMN_VALUE(l_CursorID, 51, l_address_type);
730
731 EXIT WHEN l_ignore <> 100;
732
733 END LOOP;
734 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
735
736 IF l_debug_on THEN
737 WSH_DEBUG_SV.LOG (l_module_name, 'Number of parties queried : ' ,l_num_sourceID_tab.COUNT,WSH_DEBUG_SV.C_STMT_LEVEL);
738 END IF;
739
740 --Bulk Insert into Interface Tables Appropriately
741 IF l_num_hzpartyID_tab.COUNT <> 0 THEN
742 FOR i in l_num_hzpartyID_tab.FIRST..l_num_hzpartyID_tab.LAST
743 LOOP
744
745 IF l_num_hzpartyID_tab(i) <> l_tempSourceID THEN
746 l_tempSourceID := l_num_hzpartyID_tab(i);
747 --Create a new Request Control Seq
748
749 j := l_num_ReqCtrl_tab.COUNT + 1;
750
751 select WSH_ITM_REQUEST_CONTROL_S.NEXTVAL
752 into
753 l_num_ReqCtrl_tab(j)
754 from dual;
755
756 l_varchar_rcAttrib1_tab(j) := l_varchar_tempAttrib1_tab(i);
757 l_varchar_rcAttrib2_tab(j) := l_varchar_tempAttrib2_tab(i);
758 l_varchar_rcAttrib3_tab(j) := l_varchar_tempAttrib3_tab(i);
759 l_varchar_rcAttrib4_tab(j) := l_varchar_tempAttrib4_tab(i);
760 l_varchar_rcAttrib5_tab(j) := l_varchar_tempAttrib5_tab(i);
761 l_varchar_rcAttrib6_tab(j) := l_varchar_tempAttrib6_tab(i);
762 l_varchar_rcAttrib7_tab(j) := l_varchar_tempAttrib7_tab(i);
763 l_varchar_rcAttrib8_tab(j) := l_varchar_tempAttrib8_tab(i);
764 l_varchar_rcAttrib9_tab(j) := l_varchar_tempAttrib9_tab(i);
765 l_varchar_rcAttrib10_tab(j) := l_varchar_tempAttrib10_tab(i);
766 l_varchar_rcAttrib11_tab(j) := l_varchar_tempAttrib11_tab(i);
767 l_varchar_rcAttrib12_tab(j) := l_varchar_tempAttrib12_tab(i);
768 l_varchar_rcAttrib13_tab(j) := l_varchar_tempAttrib13_tab(i);
769 l_varchar_rcAttrib14_tab(j) := l_varchar_tempAttrib14_tab(i);
770 l_varchar_rcAttrib15_tab(j) := l_varchar_tempAttrib15_tab(i);
771
772 END IF;
773 --Saving Request Control for Child WSH_ITM_PARTIES Table
774
775 l_num_PartyReqCtrl_tab(i) := l_num_ReqCtrl_tab(l_num_ReqCtrl_tab.COUNT);
776 select WSH_ITM_PARTIES_S.NEXTVAL
777 into
778 l_num_itmpartyID_tab(l_num_itmpartyID_tab.COUNT)
779 from dual;
780 END LOOP;
781 END IF;
782
783 --Getting the Base Language into the variable
784 SELECT LANGUAGE_CODE INTO l_LanguageCode
785 FROM FND_LANGUAGES
786 WHERE INSTALLED_FLAG = 'B';
787 IF l_debug_on THEN
788 WSH_DEBUG_SV.LOG (l_module_name, 'Base Language : ', l_LanguageCode, WSH_DEBUG_SV.C_STMT_LEVEL);
789 END IF;
790
791
792
793
794 IF l_debug_on THEN
795 WSH_DEBUG_SV.LOG (l_module_name, 'Number of Request Controls to be inserted : ' , l_num_ReqCtrl_tab.COUNT,WSH_DEBUG_SV.C_STMT_LEVEL);
796 END IF;
797
798
799 --Bulk Insert to Request Control Table
800 IF l_num_ReqCtrl_tab.COUNT <> 0 THEN
801 FORALL i IN l_num_ReqCtrl_tab.FIRST..l_num_ReqCtrl_tab.LAST
802 INSERT INTO WSH_ITM_REQUEST_CONTROL(
803 REQUEST_CONTROL_ID,
804 APPLICATION_ID,
805 LANGUAGE_CODE,
806 PROCESS_FLAG,
807 SERVICE_TYPE_CODE,
808 ATTRIBUTE1_VALUE,
809 ATTRIBUTE2_VALUE,
810 ATTRIBUTE3_VALUE,
811 ATTRIBUTE4_VALUE,
812 ATTRIBUTE5_VALUE,
813 ATTRIBUTE6_VALUE,
814 ATTRIBUTE7_VALUE,
815 ATTRIBUTE8_VALUE,
816 ATTRIBUTE9_VALUE,
817 ATTRIBUTE10_VALUE,
818 ATTRIBUTE11_VALUE,
819 ATTRIBUTE12_VALUE,
820 ATTRIBUTE13_VALUE,
821 ATTRIBUTE14_VALUE,
822 ATTRIBUTE15_VALUE,
823 LAST_UPDATE_DATE,
824 LAST_UPDATED_BY,
825 CREATION_DATE,
826 CREATED_BY,
827 LAST_UPDATE_LOGIN
828 )
829 VALUES(
830 l_num_ReqCtrl_tab(i),
831 222,
832 l_LanguageCode,
833 0,
834 'PARTY_SYNC',
835 l_varchar_rcAttrib1_tab(i),
836 l_varchar_rcAttrib2_tab(i),
837 l_varchar_rcAttrib3_tab(i),
838 l_varchar_rcAttrib4_tab(i),
839 l_varchar_rcAttrib5_tab(i),
840 l_varchar_rcAttrib6_tab(i),
841 l_varchar_rcAttrib7_tab(i),
842 l_varchar_rcAttrib8_tab(i),
843 l_varchar_rcAttrib9_tab(i),
844 l_varchar_rcAttrib10_tab(i),
845 l_varchar_rcAttrib11_tab(i),
846 l_varchar_rcAttrib12_tab(i),
847 l_varchar_rcAttrib13_tab(i),
848 l_varchar_rcAttrib14_tab(i),
849 l_varchar_rcAttrib15_tab(i),
850 SYSDATE,
851 l_user_id,
852 SYSDATE,
853 l_user_id,
854 l_login_id
855 );
856 END IF;
857
858 IF l_debug_on THEN
859 WSH_DEBUG_SV.LOG (l_module_name, 'Number of Parties to be inserted : ' , l_num_PartyReqCtrl_tab.COUNT,WSH_DEBUG_SV.C_STMT_LEVEL);
860 END IF;
861
862 --Bulk Insert into Parties Table
863 IF l_num_PartyReqCtrl_tab.COUNT <> 0 THEN
864 FOR i IN l_num_PartyReqCtrl_tab.FIRST..l_num_PartyReqCtrl_tab.LAST LOOP
865 INSERT INTO WSH_ITM_PARTIES (
866 PARTY_ID,
867 CUST_SITE_USE_ID,
868 REQUEST_CONTROL_ID,
869 PARTY_NAME,
870 ALTERNATE_NAME,
871 TAX_REFERENCE,
872 PARTY_TYPE,
873 PARTY_ADDRESS1,
874 PARTY_ADDRESS2,
875 PARTY_ADDRESS3,
876 PARTY_ADDRESS4,
877 PARTY_CITY,
878 PARTY_STATE,
879 PARTY_COUNTRY_CODE,
880 POSTAL_CODE,
881 ORIGINAL_SYSTEM_REFERENCE,
882 ACCOUNT_NUMBER,
883 ACCOUNT_NAME,
884 PARTY_SITE_NUMBER,
885 ATTRIBUTE1_VALUE,
886 ATTRIBUTE2_VALUE,
887 ATTRIBUTE3_VALUE,
888 ATTRIBUTE4_VALUE,
889 ATTRIBUTE5_VALUE,
890 ATTRIBUTE6_VALUE,
891 ATTRIBUTE7_VALUE,
892 ATTRIBUTE8_VALUE,
893 ATTRIBUTE9_VALUE,
894 ATTRIBUTE10_VALUE,
895 ATTRIBUTE11_VALUE,
896 ATTRIBUTE12_VALUE,
897 ATTRIBUTE13_VALUE,
898 ATTRIBUTE14_VALUE,
899 ATTRIBUTE15_VALUE,
900 LAST_UPDATE_DATE,
901 LAST_UPDATED_BY,
902 CREATION_DATE,
903 CREATED_BY,
904 LAST_UPDATE_LOGIN,
905 OPERATING_UNIT,
906 ADDRTYPE
907 )
908 VALUES(
909 l_num_itmpartyID_tab(i),
910 l_num_sourceID_tab(i),
911 l_num_PartyReqCtrl_tab(i),
912 l_varchar_PartyName_tab(i),
913 l_varchar_AlternateName_tab(i),
914 l_varchar_TaxRef_tab(i),
915 l_varchar_PartnrType_tab(i),
916 l_varchar_Address1_tab(i),
917 l_varchar_Address2_tab(i),
918 l_varchar_Address3_tab(i),
919 l_varchar_Address4_tab(i),
920 l_varchar_City_tab(i),
921 l_varchar_State_tab(i),
922 l_varchar_Country_tab(i),
923 l_varchar_PostalCode_tab(i),
924 l_num_hzpartySiteID_tab(i),
925 l_varchar_AcctNumber_tab(i),
926 l_varchar_AcctName_tab(i),
927 l_varchar_SiteNumber_tab(i),
928 l_varchar_Attrib1_tab(i),
929 l_varchar_Attrib2_tab(i),
930 l_varchar_Attrib3_tab(i),
931 l_varchar_Attrib4_tab(i),
932 l_varchar_Attrib5_tab(i),
933 l_varchar_Attrib6_tab(i),
934 l_varchar_Attrib7_tab(i),
935 l_varchar_Attrib8_tab(i),
936 l_varchar_Attrib9_tab(i),
937 l_varchar_Attrib10_tab(i),
938 l_varchar_Attrib11_tab(i),
939 l_varchar_Attrib12_tab(i),
940 l_varchar_Attrib13_tab(i),
941 l_varchar_Attrib14_tab(i),
942 l_varchar_Attrib15_tab(i),
943 --num_orgId_tab(i),
944 SYSDATE,
945 l_user_id,
946 SYSDATE,
947 l_user_id,
948 l_login_id,
949 l_operating_unit(i),
950 l_address_type(i)
951 );
952 END LOOP;
953 END IF;
954
955 -- Selecting Contacts Data
956 IF l_num_itmpartyID_tab.COUNT <> 0 THEN
957 For i in l_num_itmpartyID_tab.FIRST..l_num_itmpartyID_tab.LAST LOOP
958
959
960 IF l_debug_on THEN
961 WSH_DEBUG_SV.LOG (l_module_name, 'Getting Contacts for Party : ' , l_varchar_PartyName_tab(i),WSH_DEBUG_SV.C_STMT_LEVEL);
962 END IF;
963
964 /* Bug 7297690 Added check to determine whether Contacts for this party site is already being inserted into wsh_itm_party_contacts
965 - For each party site this query will be fired n no of times, n is no of usage of this site (billto, shipto etc)
966 - This needs to be reduced to one, since when the party site is queried first time, contacts will be added
967 and for all other hits contact will not be inserted since it is inserted first time itself. */
968 IF NOT l_party_site_id_cache.Exists(l_num_hzpartySiteID_tab(i)) THEN
969
970 l_party_site_id_cache(l_num_hzpartySiteID_tab(i)) := 'Y';
971
972 --Bug 7297690 Added column hoc.org_contact_id to Store the Org Contact id of the contact that needs to be screened */
973 SELECT DISTINCT hr.party_id,
974 hp.party_name,
975 nvl(hoc.job_title,hoc.job_title_code),
976 hoc.org_contact_id, -- Added in Bug 7297690
977 hcar.attribute1,
978 hcar.attribute2,
979 hcar.attribute3,
980 hcar.attribute4,
981 hcar.attribute5,
982 hcar.attribute6,
983 hcar.attribute7,
984 hcar.attribute8,
985 hcar.attribute9,
986 hcar.attribute10,
987 hcar.attribute11,
988 hcar.attribute12,
989 hcar.attribute13,
990 hcar.attribute14,
991 hcar.attribute15
992 BULK COLLECT INTO
993 l_num_partyrel_tab,
994 l_varchar_CtPartyName_tab,
995 l_varchar_CtPointType_tab,
996 l_org_contact_id_tab, -- Added in Bug 7297690
997 l_varchar_Attrib1_tab,
998 l_varchar_Attrib2_tab,
999 l_varchar_Attrib3_tab,
1000 l_varchar_Attrib4_tab,
1001 l_varchar_Attrib5_tab,
1002 l_varchar_Attrib6_tab,
1003 l_varchar_Attrib7_tab,
1004 l_varchar_Attrib8_tab,
1005 l_varchar_Attrib9_tab,
1006 l_varchar_Attrib10_tab,
1007 l_varchar_Attrib11_tab,
1008 l_varchar_Attrib12_tab,
1009 l_varchar_Attrib13_tab,
1010 l_varchar_Attrib14_tab,
1011 l_varchar_Attrib15_tab
1012 from hz_relationships hr, --TCA view removal Starts
1013 hz_Parties hp,
1014 hz_org_contacts hoc,
1015 hz_cust_account_roles hcar
1016 where hr.subject_table_name = 'HZ_PARTIES'
1017 and hr.object_table_name = 'HZ_PARTIES'
1018 and hr.Directional_flag = 'F'
1019 and object_id = l_num_hzpartyID_tab(i)
1020 and hr.relationship_code = 'CONTACT_OF'
1021 and hr.subject_id = hp.party_id
1022 and hoc.party_relationship_id = hr.relationship_id
1023 and nvl(hoc.party_site_id,l_num_hzpartySiteID_tab(i)) = l_num_hzpartySiteID_tab(i)
1024 and hcar.party_id(+) = hr.party_id; -- TCA view removal Ends
1025
1026
1027 -- Added for Bug 3420203
1028 IF l_num_partyrel_tab.COUNT <> 0 THEN
1029 -- Bug 7297690 Added check to determine whether party contact is already being inserted into wsh_itm_party_contacts
1030 FOR j IN l_num_partyrel_tab.FIRST..l_num_partyrel_tab.LAST LOOP
1031 BEGIN
1032
1033 IF (l_org_contact_cache(l_org_contact_id_tab(j)) = 'Y' ) THEN
1034 WSH_DEBUG_SV.LOG (l_module_name,'Party Contact Already inserted ',WSH_DEBUG_SV.C_STMT_LEVEL);
1035 END IF;
1036 EXCEPTION
1037 WHEN NO_DATA_FOUND THEN
1038 BEGIN
1039 SELECT email_address into l_varchar_ctEmail_tab(j) FROM
1040 hz_contact_points where contact_point_type ='EMAIL'
1041 and owner_table_id = l_num_partyrel_tab(j)
1042 --added for bug 6391747
1043 and owner_table_name = 'HZ_PARTIES'
1044 and primary_flag ='Y';
1045 EXCEPTION
1046 WHEN NO_DATA_FOUND THEN
1047 l_varchar_ctEmail_tab(j) :=null;
1048 END ;
1049
1050 BEGIN
1051 SELECT phone_number into l_varchar_ctPhone_tab(j) FROM
1052 hz_contact_points where contact_point_type ='PHONE'
1053 AND owner_table_id = l_num_partyrel_tab(j)
1054 --added for bug 6391747
1055 AND owner_table_name = 'HZ_PARTIES'
1056 AND phone_line_type= 'GEN'
1057 and primary_flag ='Y';
1058 EXCEPTION
1059 WHEN NO_DATA_FOUND THEN
1060 l_varchar_ctPhone_tab(j) :=null;
1061 END ;
1062 BEGIN
1063 SELECT phone_number into l_varchar_ctFax_tab(j) FROM
1064 hz_contact_points where contact_point_type ='PHONE'
1065 AND owner_table_id = l_num_partyrel_tab(j)
1066 --added for bug 6391747
1067 AND owner_table_name = 'HZ_PARTIES'
1068 and phone_line_type= 'FAX'
1069 and primary_flag ='Y';
1070 EXCEPTION
1071 WHEN NO_DATA_FOUND THEN
1072 l_varchar_ctFax_tab(j) :=null;
1073 END ;
1074 END;
1075
1076 END LOOP;
1077 END IF;
1078
1079 IF l_debug_on THEN
1080 WSH_DEBUG_SV.LOG (l_module_name, 'Number of Contacts : ' , l_varchar_CtPartyName_tab.COUNT,WSH_DEBUG_SV.C_STMT_LEVEL);
1081 END IF;
1082 -- Bug 7297690 Inserting Party contact only if it is not already populated in wsh_itm_party_contacts
1083 if l_varchar_CtPartyName_tab.COUNT <> 0 THEN
1084
1085 FOR j in l_varchar_CtPartyName_tab.FIRST..l_varchar_CtPartyName_tab.LAST LOOP
1086 -- WSH_DEBUG_SV.LOG (l_module_name,'org contact : ',l_org_contact_id_tab(j),WSH_DEBUG_SV.C_STMT_LEVEL);
1087 BEGIN
1088 IF (l_org_contact_cache(l_org_contact_id_tab(j)) = 'Y' ) THEN
1089 WSH_DEBUG_SV.LOG (l_module_name,' Party Contact Already inserted ',WSH_DEBUG_SV.C_STMT_LEVEL);
1090 END IF;
1091 EXCEPTION
1092 WHEN NO_DATA_FOUND THEN
1093 WSH_DEBUG_SV.LOG (l_module_name,'Party Contact being inserted ',WSH_DEBUG_SV.C_STMT_LEVEL);
1094 l_org_contact_cache(l_org_contact_id_tab(j)) := 'Y';
1095 insert into wsh_itm_party_contacts
1096 (
1097 PARTY_ID ,
1098 NAME ,
1099 JOB_TITLE ,
1100 EMAIL ,
1101 PHONE,
1102 FAX ,
1103 ATTRIBUTE1_VALUE,
1104 ATTRIBUTE2_VALUE,
1105 ATTRIBUTE3_VALUE,
1106 ATTRIBUTE4_VALUE,
1107 ATTRIBUTE5_VALUE,
1108 ATTRIBUTE6_VALUE,
1109 ATTRIBUTE7_VALUE,
1110 ATTRIBUTE8_VALUE,
1111 ATTRIBUTE9_VALUE,
1112 ATTRIBUTE10_VALUE,
1113 ATTRIBUTE11_VALUE,
1114 ATTRIBUTE12_VALUE,
1115 ATTRIBUTE13_VALUE,
1116 ATTRIBUTE14_VALUE,
1117 ATTRIBUTE15_VALUE,
1118 LAST_UPDATE_DATE,
1119 LAST_UPDATED_BY,
1120 CREATION_DATE,
1121 CREATED_BY,
1122 LAST_UPDATE_LOGIN
1123
1124 )
1125 VALUES
1126 (
1127 l_num_itmpartyID_tab(i),
1128 l_varchar_CtPartyName_tab(j),
1129 l_varchar_CtPointType_tab(j),
1130 l_varchar_ctEmail_tab(j),
1131 l_varchar_ctPhone_tab(j),
1132 l_varchar_ctFax_tab(j),
1133 l_varchar_Attrib1_tab(j),
1134 l_varchar_Attrib2_tab(j),
1135 l_varchar_Attrib3_tab(j),
1136 l_varchar_Attrib4_tab(j),
1137 l_varchar_Attrib5_tab(j),
1138 l_varchar_Attrib6_tab(j),
1139 l_varchar_Attrib7_tab(j),
1140 l_varchar_Attrib8_tab(j),
1141 l_varchar_Attrib9_tab(j),
1142 l_varchar_Attrib10_tab(j),
1143 l_varchar_Attrib11_tab(j),
1144 l_varchar_Attrib12_tab(j),
1145 l_varchar_Attrib13_tab(j),
1146 l_varchar_Attrib14_tab(j),
1147 l_varchar_Attrib15_tab(j),
1148 SYSDATE,
1149 l_user_id,
1150 SYSDATE,
1151 l_user_id,
1152 l_login_id
1153 );
1154 END;
1155 END LOOP;
1156
1157 END IF;
1158 END IF; -- Party site cache if loop
1159 END LOOP;
1160 END IF;
1161
1162 --
1163 -- Debug Statements
1164 --
1165 IF l_debug_on THEN
1166 WSH_DEBUG_SV.pop(l_module_name);
1167 END IF;
1168 --
1169
1170
1171 EXCEPTION
1172 WHEN OTHERS THEN
1173 IF l_debug_on THEN
1174 WSH_DEBUG_SV.logmsg(l_module_name,'The unexpected Error Code ' || SQLCODE || ' : ' || SQLERRM);
1175 END IF;
1176 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
1177 errbuf := 'Procedure WSH_ITM_PARTY_SYNC.POPULATE_DATA failed with unexpected error';
1178 retcode := '2';
1179 IF l_debug_on THEN
1180 WSH_DEBUG_SV.pop(l_module_name);
1181 END IF;
1182 RETURN;
1183
1184 END POPULATE_DATA;
1185
1186 END WSH_ITM_PARTY_SYNC;