[Home] [Help]
PACKAGE BODY: APPS.WSH_ITM_PARTY_SYNC
Source
1 PACKAGE BODY WSH_ITM_PARTY_SYNC AS
2 /* $Header: WSHITPSB.pls 120.9 2011/02/04 07:09:42 shtadepa 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 'party.party_number hz_party_number, ' || -- gtm related v
84 'PARTY.PARTY_TYPE HZ_PARTY_TYPE, '||
85 'PARTY.STATUS HZ_PARTY_STATUS, '||
86 'PARTY.HQ_BRANCH_IND HZ_HQ_BRANCH_IND, '||
87 'PARTY.SIC_CODE HZ_SIC_CODE, '||
88 'PARTY.SIC_CODE_TYPE HZ_SIC_CODE_TYPE, '||
89 'PARTY.PERSON_FIRST_NAME PERSON_FIRST_NAME, '||
90 'PARTY.PERSON_MIDDLE_NAME PERSON_MIDDLE_NAME, '||
91 'PARTY.PERSON_LAST_NAME PERSON_LAST_NAME, '||
92 'LOC.LOCATION_ID HZ_LOCATION_ID, '||
93 'PARTY.COUNTRY HZ_COUNTRY, '||
94 'PARTY.ADDRESS1 HZ_ADDRESS1, '||
95 'PARTY.ADDRESS2 HZ_ADDRESS2, '||
96 'PARTY.ADDRESS3 HZ_ADDRESS3, '||
97 'PARTY.ADDRESS4 HZ_ADDRESS4, '||
98 'PARTY.CITY HZ_CITY, '||
99 'PARTY.POSTAL_CODE HZ_POSTAL_CODE, '||
100 'PARTY.STATE HZ_STATE, '||
101 'PARTY.PROVINCE HZ_PROVINCE, '||
102 'PARTY.COUNTY HZ_COUNTY '||
103 ' FROM '||
104 'hz_cust_site_uses_all site_uses,'||
105 'hz_cust_acct_sites_all acct_site,'||
106 'hz_cust_accounts acct,'||
107 'hz_party_sites party_site,'||
108 'hz_parties party,'||
109 'hz_locations loc ,'||
110 'hz_Person_profiles person_profile '||
111 'WHERE '||
112 ' site_uses.CUST_ACCT_SITE_ID = acct_site.CUST_ACCT_SITE_ID'||
113 ' AND acct_site.PARTY_SITE_ID = party_site.PARTY_SITE_ID'||
114 ' AND party_site.PARTY_ID = party.PARTY_ID'||
115 ' AND loc.LOCATION_ID = party_site.LOCATION_ID'||
116 ' AND acct.CUST_ACCOUNT_ID = acct_site.CUST_ACCOUNT_ID'||
117 ' AND acct.STATUS = ''A'' '||
118 ' AND party.PARTY_ID = person_profile.PARTY_ID(+) ';
119
120 l_ContactQuery VARCHAR2(1000) := ' OR party.party_id IN' || --TCA view removal starts
121 '( SELECT hr.object_id FROM hz_relationships hr,' ||
122 ' hz_Parties hp,' ||
123 'hz_contact_points hcp,' ||
124 ' hz_org_contacts hoc' ||
125 ' WHERE '||
126 ' hr.subject_table_name = ''HZ_PARTIES'' ' ||
127 ' AND hr.object_table_name =''HZ_PARTIES'' ' ||
128 ' AND hr.directional_flag = ''F'' ' ||
129 ' AND hr.relationship_type = ''CONTACT_OF'' '||
130 ' AND hr.subject_id = hp.party_id '||
131 ' AND hcp.owner_table_id = hr.party_id '||
132 ' AND hoc.party_relationship_id = hr.relationship_id '; --TCA view removal ends
133
134
135
136
137 l_CarrierSQLQuery VARCHAR2(12000) := ' SELECT '||
138 'to_number(null) source_id,'||
139 'party.party_id party_id, '||
140 'party_site.party_site_id party_site_id, '||
141 'to_number(null) custAcctId,'||
142 '''CARRIER'' partnrtype,'||
143 'party.party_name party_name,'||
144 'party.organization_name_phonetic alternate_name,'||
145 'party.tax_reference tax_reference,'||
146 'loc.address1 address1,'||
147 'loc.address2 address2,'||
148 'loc.address3 address3,'||
149 'loc.address4 address4,'||
150 'loc.city city,'||
151 'loc.state state,'||
152 'loc.country country,'||
153 'loc.postal_code postal_code,'||
154 'to_char(null) acct_number,'||
155 'to_char(null) acct_name,'||
156 'party_site.party_site_number site_number,'||
157 'party.attribute1 acct_attribute1,'||
158 'party.attribute2 acct_attribute2,'||
159 'party.attribute3 acct_attribute3,'||
160 'party.attribute4 acct_attribute4,'||
161 'party.attribute5 acct_attribute5,'||
162 'party.attribute6 acct_attribute6,'||
163 'party.attribute7 acct_attribute7,'||
164 'party.attribute8 acct_attribute8,'||
165 'party.attribute9 acct_attribute9,'||
166 'party.attribute10 acct_attribute10,'||
167 'party.attribute11 acct_attribute11,'||
168 'party.attribute12 acct_attribute12,'||
169 'party.attribute13 acct_attribute13,'||
170 'party.attribute14 acct_attribute14,'||
171 'party.attribute15 acct_attribute15,'||
172 'party_site.attribute1 acct_site_attribute1,'||
173 'party_site.attribute2 acct_site_attribute2,'||
174 'party_site.attribute3 acct_site_attribute3,'||
175 'party_site.attribute4 acct_site_attribute4,'||
176 'party_site.attribute5 acct_site_attribute5,'||
180 'party_site.attribute9 acct_site_attribute9,'||
177 'party_site.attribute6 acct_site_attribute6,'||
178 'party_site.attribute7 acct_site_attribute7,'||
179 'party_site.attribute8 acct_site_attribute8,'||
181 'party_site.attribute10 acct_site_attribute10,'||
182 'party_site.attribute11 acct_site_attribute11,'||
183 'party_site.attribute12 acct_site_attribute12,'||
184 'party_site.attribute13 acct_site_attribute13,'||
185 'party_site.attribute14 acct_site_attribute14,'||
186 'party_site.attribute15 acct_site_attribute15,' ||
187 ' null, ' ||
188 'party_site.identifying_address_flag address_type,' ||
189 'party.party_number hz_party_number, ' || -- gtm related v
190 'PARTY.PARTY_TYPE HZ_PARTY_TYPE, '||
191 'PARTY.STATUS HZ_PARTY_STATUS, '||
192 'PARTY.HQ_BRANCH_IND HZ_HQ_BRANCH_IND, '||
193 'PARTY.SIC_CODE HZ_SIC_CODE, '||
194 'PARTY.SIC_CODE_TYPE HZ_SIC_CODE_TYPE, '||
195 'PARTY.PERSON_FIRST_NAME PERSON_FIRST_NAME, '||
196 'PARTY.PERSON_MIDDLE_NAME PERSON_MIDDLE_NAME, '||
197 'PARTY.PERSON_LAST_NAME PERSON_LAST_NAME, '||
198 'LOC.LOCATION_ID HZ_LOCATION_ID, '||
199 'PARTY.COUNTRY HZ_COUNTRY, '||
200 'PARTY.ADDRESS1 HZ_ADDRESS1, '||
201 'PARTY.ADDRESS2 HZ_ADDRESS2, '||
202 'PARTY.ADDRESS3 HZ_ADDRESS3, '||
203 'PARTY.ADDRESS4 HZ_ADDRESS4, '||
204 'PARTY.CITY HZ_CITY, '||
205 'PARTY.POSTAL_CODE HZ_POSTAL_CODE, '||
206 'PARTY.STATE HZ_STATE, '||
207 'PARTY.PROVINCE HZ_PROVINCE, '||
208 'PARTY.COUNTY HZ_COUNTY '||
209 ' FROM '||
210 'hz_party_sites party_site,'||
211 'hz_parties party,'||
212 'hz_locations loc ,'||
213 'wsh_carriers_v wsh_car '||
214 'WHERE '||
215 ' party_site.party_id = party.party_id'||
216 ' AND wsh_car.ACTIVE = ''A'' '||
217 ' AND party.party_id = wsh_car.carrier_id'||
218 ' AND loc.location_id = party_site.location_id';
219
220 -- gtm : new Query
221 l_GtmSQLQuery VARCHAR2(12000) := ' SELECT '||
222 'site_uses.party_site_use_id source_id,'||
223 'party.party_id party_id, '||
224 'party_site.party_site_id party_site_id, '||
225 'to_number(null) custAcctId,'||
226 'site_uses.site_use_type partnrtype,'||
227 'decode(party.party_type,''PERSON'',person_profile.person_name,party.party_name) party_name,'||
228 'decode(party.party_type,''ORGANIZATION'',party.organization_name_phonetic,''PERSON'',
229 person_profile.person_name_phonetic,party.organization_name_phonetic) alternate_name,'||
230 'party.tax_reference tax_reference,'||
231 'loc.address1 address1,'||
232 'loc.address2 address2,'||
233 'loc.address3 address3,'||
234 'loc.address4 address4,'||
235 'loc.city city,'||
236 'loc.state state,'||
237 'loc.country country,'||
238 'loc.postal_code postal_code,'||
239 'to_char(null) acct_number,'||
240 'to_char(null) acct_name,'||
244 'party.attribute3 acct_attribute3,'||
241 'party_site.party_site_number site_number,'||
242 'party.attribute1 acct_attribute1,'||
243 'party.attribute2 acct_attribute2,'||
245 'party.attribute4 acct_attribute4,'||
246 'party.attribute5 acct_attribute5,'||
247 'party.attribute6 acct_attribute6,'||
248 'party.attribute7 acct_attribute7,'||
249 'party.attribute8 acct_attribute8,'||
250 'party.attribute9 acct_attribute9,'||
251 'party.attribute10 acct_attribute10,'||
252 'party.attribute11 acct_attribute11,'||
253 'party.attribute12 acct_attribute12,'||
254 'party.attribute13 acct_attribute13,'||
255 'party.attribute14 acct_attribute14,'||
256 'party.attribute15 acct_attribute15,'||
257 'party_site.attribute1 acct_site_attribute1,'||
258 'party_site.attribute2 acct_site_attribute2,'||
259 'party_site.attribute3 acct_site_attribute3,'||
260 'party_site.attribute4 acct_site_attribute4,'||
261 'party_site.attribute5 acct_site_attribute5,'||
262 'party_site.attribute6 acct_site_attribute6,'||
263 'party_site.attribute7 acct_site_attribute7,'||
264 'party_site.attribute8 acct_site_attribute8,'||
265 'party_site.attribute9 acct_site_attribute9,'||
266 'party_site.attribute10 acct_site_attribute10,'||
267 'party_site.attribute11 acct_site_attribute11,'||
268 'party_site.attribute12 acct_site_attribute12,'||
269 'party_site.attribute13 acct_site_attribute13,'||
270 'party_site.attribute14 acct_site_attribute14,'||
271 'party_site.attribute15 acct_site_attribute15,' ||
272 ' null, ' ||
273 'party_site.identifying_address_flag address_type, ' ||
274 'party.party_number hz_party_number, ' ||
275 'PARTY.PARTY_TYPE HZ_PARTY_TYPE, '||
276 'PARTY.STATUS HZ_PARTY_STATUS, '||
277 'PARTY.HQ_BRANCH_IND HZ_HQ_BRANCH_IND, '||
278 'PARTY.SIC_CODE HZ_SIC_CODE, '||
279 'PARTY.SIC_CODE_TYPE HZ_SIC_CODE_TYPE, '||
280 'PARTY.PERSON_FIRST_NAME PERSON_FIRST_NAME, '||
281 'PARTY.PERSON_MIDDLE_NAME PERSON_MIDDLE_NAME, '||
282 'PARTY.PERSON_LAST_NAME PERSON_LAST_NAME, '||
283 'LOC.LOCATION_ID HZ_LOCATION_ID, '||
284 'PARTY.COUNTRY HZ_COUNTRY, '||
285 'PARTY.ADDRESS1 HZ_ADDRESS1, '||
286 'PARTY.ADDRESS2 HZ_ADDRESS2, '||
287 'PARTY.ADDRESS3 HZ_ADDRESS3, '||
288 'PARTY.ADDRESS4 HZ_ADDRESS4, '||
289 'PARTY.CITY HZ_CITY, '||
290 'PARTY.POSTAL_CODE HZ_POSTAL_CODE, '||
291 'PARTY.STATE HZ_STATE, '||
292 'PARTY.PROVINCE HZ_PROVINCE, '||
293 'PARTY.COUNTY HZ_COUNTY '||
294 ' FROM '||
295 'hz_party_sites party_site,'||
296 'hz_party_site_uses site_uses,'||
297 'hz_parties party,'||
298 'hz_locations loc, '||
299 'hz_Person_profiles person_profile '||
300 'WHERE '||
301 ' party_site.party_id (+) = party.party_id'||
302 ' AND party_site.party_site_id = site_uses.party_site_id (+) '||
306
303 ' AND party.status in ( ''A'', ''M'', ''D'') '||
304 ' AND party.PARTY_ID = person_profile.PARTY_ID(+) '||
305 ' AND loc.location_id (+) = party_site.location_id';
307 -- gtm: Cursor to Check for GTM Flows
308 CURSOR cur_check_gtm_flows IS
309 SELECT decode (wits.value, 'FALSE', 'N', null, 'N', 'TRUE', 'Y', 'N')
310 FROM wsh_itm_parameter_setups_b wits
311 WHERE wits.PARAMETER_NAME = 'WSH_ITM_INTG_GTM';
312
313 -- gtm
314 l_itm_intg_gtm VARCHAR2(1) := 'N';
315
316 l_Party_Table WSH_ITM_QUERY_CUSTOM.g_CondnValTableType;
317
318 l_Party_Condn1Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
319 l_Party_Condn11Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
320 l_Party_Condn2Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
321 l_Party_Condn3Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
322 l_Party_Condn4Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
323
324
325 l_Carrier_Table WSH_ITM_QUERY_CUSTOM.g_CondnValTableType;
326
327 l_Carrier_Condn1Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
328 l_Carrier_Condn11Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
329 l_Carrier_Condn2Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
330 l_Carrier_Condn3Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
331
332 -- gtm
333 l_gtm_Condn1Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
334
335
336 l_tempStr1 VARCHAR2(10000) := ' ';
337 l_tempStr2 VARCHAR2(10000) := ' ';
338 l_CursorID NUMBER;
339 l_ignore NUMBER;
340
341 --PL/SQL Table used for Bulk Select
342 l_num_sourceID_tab DBMS_SQL.Number_Table;
343 l_num_custAccountID_tab DBMS_SQL.Number_Table;
344 l_num_hzpartyID_tab DBMS_SQL.Number_Table;
345
346 l_num_hzpartySiteID_tab DBMS_SQL.Number_Table;
347 l_num_itmpartyID_tab DBMS_SQL.Number_Table;
348
349 l_num_partyrel_tab DBMS_SQL.Number_Table;
350 l_varchar_PartnrType_tab DBMS_SQL.Varchar2_Table;
351 l_varchar_PartyName_tab DBMS_SQL.Varchar2_Table;
352 l_varchar_AlternateName_tab DBMS_SQL.Varchar2_Table;
353 l_varchar_Address1_tab DBMS_SQL.Varchar2_Table;
354 l_varchar_Address2_tab DBMS_SQL.Varchar2_Table;
355 l_varchar_Address3_tab DBMS_SQL.Varchar2_Table;
356 l_varchar_Address4_tab DBMS_SQL.Varchar2_Table;
357 l_varchar_City_tab DBMS_SQL.Varchar2_Table;
358 l_varchar_State_tab DBMS_SQL.Varchar2_Table;
359 l_varchar_Country_tab DBMS_SQL.Varchar2_Table;
360 l_varchar_PostalCode_tab DBMS_SQL.Varchar2_Table;
361 l_varchar_TaxRef_tab DBMS_SQL.Varchar2_Table;
362 l_varchar_AcctNumber_tab DBMS_SQL.Varchar2_Table;
363 l_varchar_AcctName_tab DBMS_SQL.Varchar2_Table;
364 l_varchar_SiteNumber_tab DBMS_SQL.Varchar2_Table;
365
366 -- gtm: contact start
367 l_num_CtPartyId_tab DBMS_SQL.Number_Table;
368 l_varchar_CtPartyNum_tab DBMS_SQL.Varchar2_Table;
369 l_ct_alternate_name_tab DBMS_SQL.Varchar2_Table;
370 l_ct_party_type_tab DBMS_SQL.Varchar2_Table;
371 l_ct_status_tab DBMS_SQL.Varchar2_Table;
372 l_ct_first_name_tab DBMS_SQL.Varchar2_Table;
373 l_ct_middle_name_tab DBMS_SQL.Varchar2_Table;
374 l_ct_last_name_tab DBMS_SQL.Varchar2_Table;
375 l_ct_country_tab DBMS_SQL.Varchar2_Table;
376 l_ct_address1_tab DBMS_SQL.Varchar2_Table;
377 l_ct_address2_tab DBMS_SQL.Varchar2_Table;
378 l_ct_address3_tab DBMS_SQL.Varchar2_Table;
379 l_ct_address4_tab DBMS_SQL.Varchar2_Table;
380 l_ct_city_tab DBMS_SQL.Varchar2_Table;
381 l_ct_postal_code_tab DBMS_SQL.Varchar2_Table;
382 l_ct_state_tab DBMS_SQL.Varchar2_Table;
383 l_ct_province_tab DBMS_SQL.Varchar2_Table;
384 l_ct_county_tab DBMS_SQL.Varchar2_Table; -- gtm related: end
385
386 l_varchar_CtPartyName_tab DBMS_SQL.Varchar2_Table;
387 l_varchar_CtPointType_tab DBMS_SQL.Varchar2_Table;
388 l_varchar_ctEmail_tab DBMS_SQL.Varchar2_Table;
389 l_varchar_ctPhone_tab DBMS_SQL.Varchar2_Table;
390 l_varchar_ctFax_tab DBMS_SQL.Varchar2_Table;
391
392 l_varchar_tempAttrib1_tab DBMS_SQL.Varchar2_Table;
393 l_varchar_tempAttrib2_tab DBMS_SQL.Varchar2_Table;
394 l_varchar_tempAttrib3_tab DBMS_SQL.Varchar2_Table;
395 l_varchar_tempAttrib4_tab DBMS_SQL.Varchar2_Table;
396 l_varchar_tempAttrib5_tab DBMS_SQL.Varchar2_Table;
397 l_varchar_tempAttrib6_tab DBMS_SQL.Varchar2_Table;
398 l_varchar_tempAttrib7_tab DBMS_SQL.Varchar2_Table;
399 l_varchar_tempAttrib8_tab DBMS_SQL.Varchar2_Table;
400 l_varchar_tempAttrib9_tab DBMS_SQL.Varchar2_Table;
401 l_varchar_tempAttrib10_tab DBMS_SQL.Varchar2_Table;
402 l_varchar_tempAttrib11_tab DBMS_SQL.Varchar2_Table;
406 l_varchar_tempAttrib15_tab DBMS_SQL.Varchar2_Table;
403 l_varchar_tempAttrib12_tab DBMS_SQL.Varchar2_Table;
404 l_varchar_tempAttrib13_tab DBMS_SQL.Varchar2_Table;
405 l_varchar_tempAttrib14_tab DBMS_SQL.Varchar2_Table;
407
408
409 l_varchar_rcAttrib1_tab DBMS_SQL.Varchar2_Table;
410 l_varchar_rcAttrib2_tab DBMS_SQL.Varchar2_Table;
411 l_varchar_rcAttrib3_tab DBMS_SQL.Varchar2_Table;
412 l_varchar_rcAttrib4_tab DBMS_SQL.Varchar2_Table;
413 l_varchar_rcAttrib5_tab DBMS_SQL.Varchar2_Table;
414 l_varchar_rcAttrib6_tab DBMS_SQL.Varchar2_Table;
415 l_varchar_rcAttrib7_tab DBMS_SQL.Varchar2_Table;
416 l_varchar_rcAttrib8_tab DBMS_SQL.Varchar2_Table;
417 l_varchar_rcAttrib9_tab DBMS_SQL.Varchar2_Table;
418 l_varchar_rcAttrib10_tab DBMS_SQL.Varchar2_Table;
419 l_varchar_rcAttrib11_tab DBMS_SQL.Varchar2_Table;
420 l_varchar_rcAttrib12_tab DBMS_SQL.Varchar2_Table;
421 l_varchar_rcAttrib13_tab DBMS_SQL.Varchar2_Table;
422 l_varchar_rcAttrib14_tab DBMS_SQL.Varchar2_Table;
423 l_varchar_rcAttrib15_tab DBMS_SQL.Varchar2_Table;
424
425
426 l_varchar_Attrib1_tab DBMS_SQL.Varchar2_Table;
427 l_varchar_Attrib2_tab DBMS_SQL.Varchar2_Table;
428 l_varchar_Attrib3_tab DBMS_SQL.Varchar2_Table;
429 l_varchar_Attrib4_tab DBMS_SQL.Varchar2_Table;
430 l_varchar_Attrib5_tab DBMS_SQL.Varchar2_Table;
431 l_varchar_Attrib6_tab DBMS_SQL.Varchar2_Table;
432 l_varchar_Attrib7_tab DBMS_SQL.Varchar2_Table;
433 l_varchar_Attrib8_tab DBMS_SQL.Varchar2_Table;
434 l_varchar_Attrib9_tab DBMS_SQL.Varchar2_Table;
435 l_varchar_Attrib10_tab DBMS_SQL.Varchar2_Table;
436 l_varchar_Attrib11_tab DBMS_SQL.Varchar2_Table;
437 l_varchar_Attrib12_tab DBMS_SQL.Varchar2_Table;
438 l_varchar_Attrib13_tab DBMS_SQL.Varchar2_Table;
439 l_varchar_Attrib14_tab DBMS_SQL.Varchar2_Table;
440 l_varchar_Attrib15_tab DBMS_SQL.Varchar2_Table;
441
442 --Bug 7297690 Added variable to store the party site id and Org contact id of the contacts who will be screened
443 l_org_contact_id_tab DBMS_SQL.Number_Table;
444 Type l_org_contact_type is table of Varchar2(1) index by BINARY_INTEGER;
445 l_org_contact_cache l_org_contact_type;
446 Type l_party_site_id_type is table of Varchar2(1) index by BINARY_INTEGER;
447 l_party_site_id_cache l_party_site_id_type;
448
449 l_operating_unit DBMS_SQL.Number_Table;
450 l_address_type DBMS_SQL.Varchar2_Table; -- gtm related: start
451 l_hz_party_num_tab DBMS_SQL.Varchar2_Table;
452 l_hz_party_type_tab DBMS_SQL.Varchar2_Table;
453 l_hz_status_tab DBMS_SQL.Varchar2_Table;
454 l_hz_hq_branch_ind_tab DBMS_SQL.Varchar2_Table;
455 l_hz_sic_code_tab DBMS_SQL.Varchar2_Table;
456 l_hz_sic_code_type_tab DBMS_SQL.Varchar2_Table;
457 l_person_first_name_tab DBMS_SQL.Varchar2_Table;
458 l_person_middle_name_tab DBMS_SQL.Varchar2_Table;
459 l_person_last_name_tab DBMS_SQL.Varchar2_Table;
460 l_hz_location_id_tab DBMS_SQL.Number_Table;
461 l_hz_country_tab DBMS_SQL.Varchar2_Table;
462 l_hz_address1_tab DBMS_SQL.Varchar2_Table;
463 l_hz_address2_tab DBMS_SQL.Varchar2_Table;
464 l_hz_address3_tab DBMS_SQL.Varchar2_Table;
465 l_hz_address4_tab DBMS_SQL.Varchar2_Table;
466 l_hz_city_tab DBMS_SQL.Varchar2_Table;
467 l_hz_postal_code_tab DBMS_SQL.Varchar2_Table;
468 l_hz_state_tab DBMS_SQL.Varchar2_Table;
469 l_hz_province_tab DBMS_SQL.Varchar2_Table;
470 l_hz_county_tab DBMS_SQL.Varchar2_Table; -- gtm related: end
471 l_email VARCHAR2(100);
472 l_phone VARCHAR2(100);
473 l_fax VARCHAR2(100);
474
475 --For Insert to ITM Inteface Tables
476 l_num_ReqCtrl_tab DBMS_SQL.Number_Table;
477 l_num_PartyReqCtrl_tab DBMS_SQL.Number_Table;
478 l_tempSourceID NUMBER := -999;
479 j NUMBER;
480
481 l_user_id NUMBER;
482 l_login_id NUMBER;
483 l_temp BOOLEAN;
484 l_LanguageCode VARCHAR2(4);
485
486 --Party name
487 l_from_party VARCHAR2(360);
488 l_to_party VARCHAR2(360);
489
490
491 CURSOR cur_customer_party_name(c_account_id varchar2) IS
492 SELECT
493 HP.party_name
494 FROM
495 hz_parties HP,
496 HZ_CUST_ACCOUNTS HC
497 WHERE
498 HP.PARTY_ID = HC.PARTY_ID AND
499 hc.cust_account_id = TO_NUMBER(c_account_id);
500
501
502
503 CURSOR cur_carrier_party_name(c_freight_code varchar2) IS
504 SELECT
505 HP.PARTY_NAME
506 FROM
507 WSH_CARRIERS WC,
508 HZ_PARTIES HP
509 WHERE
510 HP.PARTY_ID = WC.CARRIER_ID AND
511 wc.freight_code = c_freight_code;
512
513 -- gtm : for parties other than Customer or Carrier
514 CURSOR cur_other_party_name(c_party_code varchar2) IS
515 SELECT
516 HP.PARTY_NAME
517 FROM
518 HZ_PARTIES HP
519 WHERE
523 l_debug_on BOOLEAN;
520 HP.PARTY_ID = to_number(c_party_code);
521
522 --
524 --
525 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'POPULATE_DATA';
526 --
527 BEGIN
528 --Frame draft SQL
529
530 --
531 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
532 --
533 IF l_debug_on IS NULL
534 THEN
535 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
536 END IF;
537 --
538 --
539 -- Debug Statements
540 --
541 IF l_debug_on THEN
542 WSH_DEBUG_SV.push(l_module_name);
543 --
544 WSH_DEBUG_SV.log(l_module_name,'P_PARTY_TYPE',P_PARTY_TYPE);
545 WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_CODE',P_FROM_PARTY_CODE);
546 WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_CODE',P_TO_PARTY_CODE);
547 WSH_DEBUG_SV.log(l_module_name,'P_DUMMY',P_DUMMY);
548 WSH_DEBUG_SV.log(l_module_name,'P_SITE_USE_CODE',P_SITE_USE_CODE);
549 WSH_DEBUG_SV.log(l_module_name,'P_CREATED_N_DAYS',P_CREATED_N_DAYS);
550 WSH_DEBUG_SV.log(l_module_name,'P_UPDATED_N_DAYS',P_UPDATED_N_DAYS);
551 END IF;
552 --
553 --gtm flow check
554 OPEN cur_check_gtm_flows;
555 FETCH cur_check_gtm_flows into l_itm_intg_gtm;
556 CLOSE cur_check_gtm_flows;
557
558 -- Fetch user and login information
559 l_user_id := FND_GLOBAL.USER_ID;
560 l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
561
562 -- {
563 IF p_from_party_code IS NOT NULL THEN
564 -- {
565 IF p_party_type = 'CUSTOMER' THEN
566
567 OPEN cur_customer_party_name(p_from_party_code);
568 FETCH cur_customer_party_name into l_from_party;
569 CLOSE cur_customer_party_name;
570
571 l_Party_Condn1Tab(1).g_varchar_val := l_from_party;
572 l_Party_Condn1Tab(1).g_Bind_Literal := ':b_from_party';
573 --
574 -- Debug Statements
575 --
576 IF l_debug_on THEN
577 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
578 END IF;
579 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' AND PARTY.PARTY_NAME >= :b_from_party', l_Party_Condn1Tab, 'VARCHAR');
580
581 ELSIF (p_party_type = 'CARRIER' ) THEN
582
583 OPEN cur_carrier_party_name(p_from_party_code);
584 FETCH cur_carrier_party_name into l_from_party;
585 CLOSE cur_carrier_party_name;
586
587 l_Carrier_Condn1Tab(1).g_varchar_val := l_from_party;
588 l_Carrier_Condn1Tab(1).g_Bind_Literal := ':b_from_party';
589 --
590 -- Debug Statements
591 --
592 IF l_debug_on THEN
593 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
594 END IF;
595 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' AND PARTY.PARTY_NAME >= :b_from_party', l_Carrier_Condn1Tab, 'VARCHAR');
596
597 ELSE
598
599 OPEN cur_other_party_name(p_from_party_code);
600 FETCH cur_other_party_name into l_from_party;
601 CLOSE cur_other_party_name;
602
603 l_Carrier_Condn1Tab(1).g_varchar_val := l_from_party;
604 l_Carrier_Condn1Tab(1).g_Bind_Literal := ':b_from_party';
605 --
606 -- Debug Statements
607 --
608 IF l_debug_on THEN
609 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
610 END IF;
611 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' AND PARTY.PARTY_NAME >= :b_from_party', l_Carrier_Condn1Tab, 'VARCHAR');
612
613 END IF; -- } party_type
614 END IF; -- } party_code not null
615
616 -- { gtm: party code
617 IF p_to_party_code IS NOT NULL THEN
618 -- { : party_type
619 IF p_party_type = 'CUSTOMER' THEN
620
621 OPEN cur_customer_party_name(p_to_party_code);
622 FETCH cur_customer_party_name into l_to_party;
623 CLOSE cur_customer_party_name;
624
625 l_Party_Condn11Tab(1).g_varchar_val := l_to_party;
626 l_Party_Condn11Tab(1).g_Bind_Literal := ':b_to_party';
627 --
628 -- Debug Statements
629 --
630 IF l_debug_on THEN
631 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
632 END IF;
633 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' AND PARTY.PARTY_NAME <= :b_to_party', l_Party_Condn11Tab, 'VARCHAR');
634
635 ELSIF (p_party_type = 'CARRIER' ) THEN
636
637 OPEN cur_carrier_party_name(p_to_party_code);
641 l_Carrier_Condn11Tab(1).g_varchar_val := l_to_party;
638 FETCH cur_carrier_party_name into l_to_party;
639 CLOSE cur_carrier_party_name;
640
642 l_Carrier_Condn11Tab(1).g_Bind_Literal := ':b_to_party';
643 --
644 -- Debug Statements
645 --
646 IF l_debug_on THEN
647 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
648 END IF;
649 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' AND PARTY.PARTY_NAME <= :b_to_party', l_Carrier_Condn11Tab, 'VARCHAR');
650
651 -- gtm
652 ELSE
653
654 OPEN cur_other_party_name(p_to_party_code);
655 FETCH cur_other_party_name into l_to_party;
656 CLOSE cur_other_party_name;
657
658 l_Carrier_Condn11Tab(1).g_varchar_val := l_to_party;
659 l_Carrier_Condn11Tab(1).g_Bind_Literal := ':b_to_party';
660 --
661 -- Debug Statements
662 --
663 IF l_debug_on THEN
664 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
665 END IF;
666 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' AND PARTY.PARTY_NAME <= :b_to_party', l_Carrier_Condn11Tab, 'VARCHAR');
667
668 END IF; -- } party_type
669 END IF; -- } to party_code not null
670
671 IF ( p_party_type IS NOT NULL AND p_party_type NOT IN ('CUSTOMER','CARRIER')) THEN
672 --{
673 -- gtm : to add the Party Type (org, person etc.)
674 l_gtm_Condn1Tab(1).g_varchar_val := p_party_type;
675 l_gtm_Condn1Tab(1).g_Bind_Literal := ':b_party_type';
676 --
677 -- Debug Statements
678 --
679 IF l_debug_on THEN
680 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
681 END IF;
682 --
683 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' AND PARTY.PARTY_TYPE = :b_party_type', l_gtm_Condn1Tab, 'VARCHAR');
684 END IF;
685 --}
686 --Adding Business Purpose Condn
687 IF p_site_use_code IS NOT NULL THEN
688 l_Party_Condn2Tab(1).g_varchar_val := p_site_use_code;
689 l_Party_Condn2Tab(1).g_Bind_Literal := ':b_site_use_code';
690 --
691 -- Debug Statements
692 --
693 IF l_debug_on THEN
694 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
695 END IF;
696 --
697 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' AND SITE_USES.SITE_USE_CODE = :b_site_use_code', l_Party_Condn2Tab, 'VARCHAR');
698 END IF;
699
700 --Adding Creates Last N Days Condn
701 IF p_created_n_days IS NOT NULL THEN
702 IF p_party_type = 'CUSTOMER' OR p_party_type is NULL THEN
703 l_Party_Condn3Tab(1).g_number_val := p_created_n_days;
704 l_Party_Condn3Tab(1).g_Bind_Literal := ':b_created_n_days';
705 END IF;
706 -- gtm
707 IF (p_party_type <> 'CUSTOMER' OR p_party_type is NULL) THEN
708 l_Carrier_Condn2Tab(1).g_number_val := p_created_n_days;
709 l_Carrier_Condn2Tab(1).g_Bind_Literal := ':b_created_n_days';
710 END IF;
711
712 --
713 -- Debug Statements
714 --
715 IF l_debug_on THEN
716 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
717 END IF;
718 --
719 IF p_party_type = 'CUSTOMER' OR p_party_type is NULL THEN
720
721 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' AND (SITE_USES.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Party_Condn3Tab, 'NUMBER');
722 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR ACCT.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Party_Condn3Tab, 'NUMBER');
723 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR ACCT_SITE.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Party_Condn3Tab, 'NUMBER');
724 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR PARTY_SITE.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Party_Condn3Tab, 'NUMBER');
725 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR LOC.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Party_Condn3Tab, 'NUMBER');
726 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR PARTY.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Party_Condn3Tab, 'NUMBER');
727 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, l_ContactQuery || ' and ( hoc.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Party_Condn3Tab, 'NUMBER');
728 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' or hcp.CREATION_DATE >= SYSDATE - :b_created_n_days))) ', l_Party_Condn3Tab, 'NUMBER');
729 END IF;
730 -- gtm
731 IF (p_party_type <> 'CUSTOMER' OR p_party_type is NULL) THEN
732 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' AND ( PARTY_SITE.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Carrier_Condn2Tab, 'NUMBER');
736 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' or hcp.CREATION_DATE >= SYSDATE - :b_created_n_days))) ', l_Carrier_Condn2Tab, 'NUMBER');
733 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' OR PARTY.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Carrier_Condn2Tab, 'NUMBER');
734 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' OR LOC.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Carrier_Condn2Tab, 'NUMBER');
735 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, l_ContactQuery || ' and ( hoc.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Carrier_Condn2Tab, 'NUMBER');
737
738 END IF;
739 END IF;
740
741 --Adding Creates Last N Days Condn
742 IF p_updated_n_days IS NOT NULL THEN
743 IF p_party_type = 'CUSTOMER' OR p_party_type is NULL THEN
744 l_Party_Condn4Tab(1).g_number_val := p_updated_n_days;
745 l_Party_Condn4Tab(1).g_Bind_Literal := ':b_updated_n_days';
746 END IF;
747 -- gtm
748 IF p_party_type <> 'CUSTOMER' OR p_party_type is NULL THEN
749 l_Carrier_Condn3Tab(1).g_number_val := p_updated_n_days;
750 l_Carrier_Condn3Tab(1).g_Bind_Literal := ':b_updated_n_days';
751 END IF;
752
753
754 --
755 -- Debug Statements
756 --
757 IF l_debug_on THEN
758 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
759 END IF;
760 --
761 IF p_party_type = 'CUSTOMER' OR p_party_type is NULL THEN
762 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' AND (SITE_USES.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
763 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR ACCT.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
764 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR ACCT_SITE.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
765 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR PARTY_SITE.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
766 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR LOC.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
767 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' OR PARTY.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Party_Condn4Tab, 'NUMBER');
768 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');
769 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Party_Table, ' or hcp.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days))) ', l_Party_Condn4Tab, 'NUMBER');
770
771 END IF;
772 -- gtm
773 IF p_party_type <> 'CUSTOMER' OR p_party_type is NULL THEN
774 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' AND (PARTY_SITE.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Carrier_Condn3Tab, 'NUMBER');
775 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' OR PARTY.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Carrier_Condn3Tab, 'NUMBER');
776 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' OR LOC.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Carrier_Condn3Tab, 'NUMBER');
777 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');
778 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Carrier_Table, ' or hcp.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days))) ', l_Carrier_Condn3Tab, 'NUMBER');
779 END IF;
780 END IF;
781
782
783 --Call to custom Procedure which could be edited by the Customer.
784 --
785 -- Debug Statements
786 --
787 IF l_debug_on THEN
788 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_CUSTOMIZE.ALTER_PARTY_SYNC for Customer',WSH_DEBUG_SV.C_PROC_LEVEL);
789 END IF;
790 WSH_ITM_CUSTOMIZE.ALTER_PARTY_SYNC(l_Party_Table);
791
792 --
793 -- Debug Statements
794 --
795 IF l_debug_on THEN
796 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_CUSTOMIZE.ALTER_PARTY_SYNC for Carrier',WSH_DEBUG_SV.C_PROC_LEVEL);
797 END IF;
798 WSH_ITM_CUSTOMIZE.ALTER_PARTY_SYNC(l_Carrier_Table);
799
800
801 --Create SQL and bind parameters
802 FOR I IN 1..l_Party_Table.COUNT
803 LOOP
804 l_tempStr1 := l_tempStr1 || ' ' || l_Party_table(i).g_Condn_Qry;
805 END LOOP;
806
807 FOR I IN 1..l_Carrier_Table.COUNT
808 LOOP
809 l_tempStr2 := l_tempStr2 || ' ' || l_Carrier_table(i).g_Condn_Qry;
810 END LOOP;
811
812
813
814 --Concatenating Main SQL with Condition SQL
815 IF p_party_type = 'CUSTOMER' THEN
816 l_SQLQuery := l_CustSQLQuery || l_tempStr1;
817 END IF;
818 IF p_party_type = 'CARRIER' THEN
819 l_SQLQuery := l_CarrierSQLQuery || l_tempStr2;
820 END IF;
821 IF ( (p_party_type is NOT NULL) AND (p_party_type <> 'CARRIER' AND p_party_type <> 'CUSTOMER')) THEN
822 l_SQLQuery := l_GtmSQLQuery || l_tempStr2;
823 END IF;
824
825
826 -- gtm
827 IF (p_party_type is NULL and l_itm_intg_gtm = 'Y' ) then
828 l_SQLQuery := l_GtmSQLQuery || l_tempStr2;
832 l_SQLQuery := l_CustSQLQuery || l_tempStr1 || ' UNION ' || l_CarrierSQLQuery || l_tempStr2;
829 END IF;
830
831 IF (p_party_type is NULL and l_itm_intg_gtm = 'N' ) then
833 END IF;
834
835
836 IF l_debug_on THEN
837 WSH_DEBUG_SV.LOG (l_module_name, 'Query ', l_SQLQuery, WSH_DEBUG_SV.C_STMT_LEVEL);
838 END IF;
839
840 -- Parse cursor
841 l_CursorID := DBMS_SQL.Open_Cursor;
842 DBMS_SQL.PARSE(l_CursorID, l_SQLQuery, DBMS_SQL.v7);
843
844
845
846 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 1, l_num_sourceID_tab, 100, 0);
847 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 2, l_num_hzpartyID_tab, 100, 0);
848 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 3, l_num_hzpartySiteID_tab, 100, 0);
849 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 4, l_num_custAccountID_tab, 100, 0);
850 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 5, l_varchar_PartnrType_tab, 100, 0);
851 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 6, l_varchar_PartyName_tab, 100, 0);
852 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 7, l_varchar_AlternateName_tab, 100, 0);
853 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 8, l_varchar_TaxRef_tab, 100, 0);
854 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 9, l_varchar_Address1_tab, 100, 0);
855 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 10,l_varchar_Address2_tab, 100, 0);
856 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 11,l_varchar_Address3_tab, 100, 0);
857 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 12, l_varchar_Address4_tab, 100, 0);
858 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 13, l_varchar_City_tab, 100, 0);
859 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 14, l_varchar_State_tab, 100, 0);
860 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 15, l_varchar_Country_tab, 100, 0);
861 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 16, l_varchar_PostalCode_tab, 100, 0);
862 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 17, l_varchar_AcctNumber_tab , 100, 0);
863 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 18, l_varchar_AcctName_tab , 100, 0);
864 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 19, l_varchar_SiteNumber_tab , 100, 0);
865
866
867 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 20, l_varchar_tempAttrib1_tab, 100, 0);
868 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 21, l_varchar_tempAttrib2_tab, 100, 0);
869 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 22, l_varchar_tempAttrib3_tab, 100, 0);
870 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 23, l_varchar_tempAttrib4_tab, 100, 0);
871 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 24, l_varchar_tempAttrib5_tab, 100, 0);
872 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 25, l_varchar_tempAttrib6_tab, 100, 0);
873 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 26, l_varchar_tempAttrib7_tab, 100, 0);
874 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 27, l_varchar_tempAttrib8_tab, 100, 0);
875 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 28, l_varchar_tempAttrib9_tab, 100, 0);
876 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 29, l_varchar_tempAttrib10_tab, 100, 0);
877 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 30, l_varchar_tempAttrib11_tab, 100, 0);
878 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 31, l_varchar_tempAttrib12_tab, 100, 0);
879 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 32, l_varchar_tempAttrib13_tab, 100, 0);
880 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 33, l_varchar_tempAttrib14_tab, 100, 0);
881 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 34, l_varchar_tempAttrib15_tab, 100, 0);
882
883
884
885 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 35, l_varchar_Attrib1_tab, 100, 0);
886 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 36, l_varchar_Attrib2_tab, 100, 0);
887 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 37, l_varchar_Attrib3_tab, 100, 0);
888 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 38, l_varchar_Attrib4_tab, 100, 0);
889 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 39, l_varchar_Attrib5_tab, 100, 0);
890 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 40, l_varchar_Attrib6_tab, 100, 0);
891 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 41, l_varchar_Attrib7_tab, 100, 0);
892 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 42, l_varchar_Attrib8_tab, 100, 0);
893 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 43, l_varchar_Attrib9_tab, 100, 0);
894 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 44, l_varchar_Attrib10_tab, 100, 0);
895 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 45, l_varchar_Attrib11_tab, 100, 0);
896 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 46, l_varchar_Attrib12_tab, 100, 0);
897 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 47, l_varchar_Attrib13_tab, 100, 0);
898 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 48, l_varchar_Attrib14_tab, 100, 0);
899 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 49, l_varchar_Attrib15_tab, 100, 0);
900 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 50, l_operating_unit, 100, 0);
901 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 51, l_address_type, 100, 0); -- gtm v Changes added below
902 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 52, l_hz_party_num_tab, 100, 0);
903 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 53, l_hz_party_type_tab, 100, 0);
904 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 54, l_hz_status_tab, 100, 0);
905 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 55, l_hz_hq_branch_ind_tab, 100, 0);
906 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 56, l_hz_sic_code_tab, 100, 0);
907 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 57, l_hz_sic_code_type_tab, 100, 0);
908 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 58, l_person_first_name_tab, 100, 0);
909 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 59, l_person_middle_name_tab, 100, 0);
910 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 60, l_person_last_name_tab, 100, 0);
911 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 61, l_hz_location_id_tab, 100, 0);
915 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 65, l_hz_address3_tab, 100, 0);
912 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 62, l_hz_country_tab, 100, 0);
913 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 63, l_hz_address1_tab, 100, 0);
914 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 64, l_hz_address2_tab, 100, 0);
916 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 66, l_hz_address4_tab, 100, 0);
917 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 67, l_hz_city_tab, 100, 0);
918 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 68, l_hz_postal_code_tab, 100, 0);
919 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 69, l_hz_state_tab, 100, 0);
920 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 70, l_hz_province_tab, 100, 0);
921 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 71, l_hz_county_tab, 100, 0);
922 --
923 -- Debug Statements
924 --
925 IF l_debug_on THEN
926 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.BIND_VALUES',WSH_DEBUG_SV.C_PROC_LEVEL);
927 END IF;
928 --
929 WSH_ITM_QUERY_CUSTOM.BIND_VALUES(l_Party_Table,l_CursorID);
930
931 --
932 -- Debug Statements
933 --
934 IF l_debug_on THEN
935 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.BIND_VALUES',WSH_DEBUG_SV.C_PROC_LEVEL);
936 END IF;
937 --
938 WSH_ITM_QUERY_CUSTOM.BIND_VALUES(l_Carrier_Table,l_CursorID);
939
940 IF l_debug_on THEN
941 WSH_DEBUG_SV.LOGMSG (l_module_name,'Successfull in binding values',WSH_DEBUG_SV.C_STMT_LEVEL);
942 END IF;
943
944 l_ignore := DBMS_SQL.EXECUTE(l_CursorID);
945
946
947 --Bulk Collect customized SQL
948 LOOP
949 l_ignore := DBMS_SQL.FETCH_ROWS(l_CursorID);
950
951
952 DBMS_SQL.COLUMN_VALUE(l_CursorID, 1, l_num_sourceID_tab);
953 DBMS_SQL.COLUMN_VALUE(l_CursorID, 2, l_num_hzpartyID_tab);
954 DBMS_SQL.COLUMN_VALUE(l_CursorID, 3, l_num_hzpartySiteID_tab);
955 DBMS_SQL.COLUMN_VALUE(l_CursorID, 4, l_num_custAccountID_tab);
956 DBMS_SQL.COLUMN_VALUE(l_CursorID, 5, l_varchar_PartnrType_tab);
957 DBMS_SQL.COLUMN_VALUE(l_CursorID, 6, l_varchar_PartyName_tab);
958 DBMS_SQL.COLUMN_VALUE(l_CursorID, 7, l_varchar_AlternateName_tab);
959 DBMS_SQL.COLUMN_VALUE(l_CursorID, 8, l_varchar_TaxRef_tab);
960 DBMS_SQL.COLUMN_VALUE(l_CursorID, 9, l_varchar_Address1_tab);
961 DBMS_SQL.COLUMN_VALUE(l_CursorID, 10,l_varchar_Address2_tab);
962 DBMS_SQL.COLUMN_VALUE(l_CursorID, 11, l_varchar_Address3_tab);
963 DBMS_SQL.COLUMN_VALUE(l_CursorID, 12, l_varchar_Address4_tab);
964 DBMS_SQL.COLUMN_VALUE(l_CursorID, 13, l_varchar_City_tab);
965 DBMS_SQL.COLUMN_VALUE(l_CursorID, 14, l_varchar_State_tab);
966 DBMS_SQL.COLUMN_VALUE(l_CursorID, 15, l_varchar_Country_tab);
967 DBMS_SQL.COLUMN_VALUE(l_CursorID, 16, l_varchar_PostalCode_tab);
968 DBMS_SQL.COLUMN_VALUE(l_CursorID, 17, l_varchar_AcctNumber_tab);
969 DBMS_SQL.COLUMN_VALUE(l_CursorID, 18, l_varchar_AcctName_tab);
970 DBMS_SQL.COLUMN_VALUE(l_CursorID, 19, l_varchar_SiteNumber_tab);
971
972 DBMS_SQL.COLUMN_VALUE(l_CursorID, 20, l_varchar_tempAttrib1_tab);
973 DBMS_SQL.COLUMN_VALUE(l_CursorID, 21, l_varchar_tempAttrib2_tab);
974 DBMS_SQL.COLUMN_VALUE(l_CursorID, 22, l_varchar_tempAttrib3_tab);
975 DBMS_SQL.COLUMN_VALUE(l_CursorID, 23, l_varchar_tempAttrib4_tab);
976 DBMS_SQL.COLUMN_VALUE(l_CursorID, 24, l_varchar_tempAttrib5_tab);
977 DBMS_SQL.COLUMN_VALUE(l_CursorID, 25, l_varchar_tempAttrib6_tab);
978 DBMS_SQL.COLUMN_VALUE(l_CursorID, 26, l_varchar_tempAttrib7_tab);
979 DBMS_SQL.COLUMN_VALUE(l_CursorID, 27, l_varchar_tempAttrib8_tab);
980 DBMS_SQL.COLUMN_VALUE(l_CursorID, 28, l_varchar_tempAttrib9_tab);
981 DBMS_SQL.COLUMN_VALUE(l_CursorID, 29, l_varchar_tempAttrib10_tab);
982 DBMS_SQL.COLUMN_VALUE(l_CursorID, 30, l_varchar_tempAttrib11_tab);
983 DBMS_SQL.COLUMN_VALUE(l_CursorID, 31, l_varchar_tempAttrib12_tab);
984 DBMS_SQL.COLUMN_VALUE(l_CursorID, 32, l_varchar_tempAttrib13_tab);
985 DBMS_SQL.COLUMN_VALUE(l_CursorID, 33, l_varchar_tempAttrib14_tab);
986 DBMS_SQL.COLUMN_VALUE(l_CursorID, 34, l_varchar_tempAttrib15_tab);
987
988 DBMS_SQL.COLUMN_VALUE(l_CursorID, 35, l_varchar_Attrib1_tab);
989 DBMS_SQL.COLUMN_VALUE(l_CursorID, 36, l_varchar_Attrib2_tab);
990 DBMS_SQL.COLUMN_VALUE(l_CursorID, 37, l_varchar_Attrib3_tab);
991 DBMS_SQL.COLUMN_VALUE(l_CursorID, 38, l_varchar_Attrib4_tab);
992 DBMS_SQL.COLUMN_VALUE(l_CursorID, 39, l_varchar_Attrib5_tab);
993 DBMS_SQL.COLUMN_VALUE(l_CursorID, 40, l_varchar_Attrib6_tab);
994 DBMS_SQL.COLUMN_VALUE(l_CursorID, 41, l_varchar_Attrib7_tab);
995 DBMS_SQL.COLUMN_VALUE(l_CursorID, 42, l_varchar_Attrib8_tab);
996 DBMS_SQL.COLUMN_VALUE(l_CursorID, 43, l_varchar_Attrib9_tab);
997 DBMS_SQL.COLUMN_VALUE(l_CursorID, 44, l_varchar_Attrib10_tab);
998 DBMS_SQL.COLUMN_VALUE(l_CursorID, 45, l_varchar_Attrib11_tab);
999 DBMS_SQL.COLUMN_VALUE(l_CursorID, 46, l_varchar_Attrib12_tab);
1000 DBMS_SQL.COLUMN_VALUE(l_CursorID, 47, l_varchar_Attrib13_tab);
1001 DBMS_SQL.COLUMN_VALUE(l_CursorID, 48, l_varchar_Attrib14_tab);
1002 DBMS_SQL.COLUMN_VALUE(l_CursorID, 49, l_varchar_Attrib15_tab);
1003 DBMS_SQL.COLUMN_VALUE(l_CursorID, 50, l_operating_unit);
1007 DBMS_SQL.COLUMN_VALUE(l_CursorID, 54, l_hz_status_tab);
1004 DBMS_SQL.COLUMN_VALUE(l_CursorID, 51, l_address_type); -- gtm New Ones added Below
1005 DBMS_SQL.COLUMN_VALUE(l_CursorID, 52, l_hz_party_num_tab);
1006 DBMS_SQL.COLUMN_VALUE(l_CursorID, 53, l_hz_party_type_tab);
1008 DBMS_SQL.COLUMN_VALUE(l_CursorID, 55, l_hz_hq_branch_ind_tab);
1009 DBMS_SQL.COLUMN_VALUE(l_CursorID, 56, l_hz_sic_code_tab);
1010 DBMS_SQL.COLUMN_VALUE(l_CursorID, 57, l_hz_sic_code_type_tab);
1011 DBMS_SQL.COLUMN_VALUE(l_CursorID, 58, l_person_first_name_tab);
1012 DBMS_SQL.COLUMN_VALUE(l_CursorID, 59, l_person_middle_name_tab);
1013 DBMS_SQL.COLUMN_VALUE(l_CursorID, 60, l_person_last_name_tab);
1014 DBMS_SQL.COLUMN_VALUE(l_CursorID, 61, l_hz_location_id_tab);
1015 DBMS_SQL.COLUMN_VALUE(l_CursorID, 62, l_hz_country_tab);
1016 DBMS_SQL.COLUMN_VALUE(l_CursorID, 63, l_hz_address1_tab);
1017 DBMS_SQL.COLUMN_VALUE(l_CursorID, 64, l_hz_address2_tab);
1018 DBMS_SQL.COLUMN_VALUE(l_CursorID, 65, l_hz_address3_tab);
1019 DBMS_SQL.COLUMN_VALUE(l_CursorID, 66, l_hz_address4_tab);
1020 DBMS_SQL.COLUMN_VALUE(l_CursorID, 67, l_hz_city_tab);
1021 DBMS_SQL.COLUMN_VALUE(l_CursorID, 68, l_hz_postal_code_tab);
1022 DBMS_SQL.COLUMN_VALUE(l_CursorID, 69, l_hz_state_tab);
1023 DBMS_SQL.COLUMN_VALUE(l_CursorID, 70, l_hz_province_tab);
1024 DBMS_SQL.COLUMN_VALUE(l_CursorID, 71, l_hz_county_tab);
1025
1026 EXIT WHEN l_ignore <> 100;
1027
1028 END LOOP;
1029 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
1030
1031 IF l_debug_on THEN
1032 WSH_DEBUG_SV.LOG (l_module_name, 'Number of parties queried : ' ,l_num_sourceID_tab.COUNT,WSH_DEBUG_SV.C_STMT_LEVEL);
1033 END IF;
1034
1035 --Bulk Insert into Interface Tables Appropriately
1036 IF l_num_hzpartyID_tab.COUNT <> 0 THEN
1037 FOR i in l_num_hzpartyID_tab.FIRST..l_num_hzpartyID_tab.LAST
1038 LOOP
1039
1040 IF l_num_hzpartyID_tab(i) <> l_tempSourceID THEN
1041 l_tempSourceID := l_num_hzpartyID_tab(i);
1042 --Create a new Request Control Seq
1043
1044 j := l_num_ReqCtrl_tab.COUNT + 1;
1045
1046 select WSH_ITM_REQUEST_CONTROL_S.NEXTVAL
1047 into
1048 l_num_ReqCtrl_tab(j)
1049 from dual;
1050
1051 l_varchar_rcAttrib1_tab(j) := l_varchar_tempAttrib1_tab(i);
1052 l_varchar_rcAttrib2_tab(j) := l_varchar_tempAttrib2_tab(i);
1053 l_varchar_rcAttrib3_tab(j) := l_varchar_tempAttrib3_tab(i);
1054 l_varchar_rcAttrib4_tab(j) := l_varchar_tempAttrib4_tab(i);
1055 l_varchar_rcAttrib5_tab(j) := l_varchar_tempAttrib5_tab(i);
1056 l_varchar_rcAttrib6_tab(j) := l_varchar_tempAttrib6_tab(i);
1057 l_varchar_rcAttrib7_tab(j) := l_varchar_tempAttrib7_tab(i);
1058 l_varchar_rcAttrib8_tab(j) := l_varchar_tempAttrib8_tab(i);
1059 l_varchar_rcAttrib9_tab(j) := l_varchar_tempAttrib9_tab(i);
1060 l_varchar_rcAttrib10_tab(j) := l_varchar_tempAttrib10_tab(i);
1061 l_varchar_rcAttrib11_tab(j) := l_varchar_tempAttrib11_tab(i);
1062 l_varchar_rcAttrib12_tab(j) := l_varchar_tempAttrib12_tab(i);
1063 l_varchar_rcAttrib13_tab(j) := l_varchar_tempAttrib13_tab(i);
1064 l_varchar_rcAttrib14_tab(j) := l_varchar_tempAttrib14_tab(i);
1065 l_varchar_rcAttrib15_tab(j) := l_varchar_tempAttrib15_tab(i);
1066
1067 END IF;
1068 --Saving Request Control for Child WSH_ITM_PARTIES Table
1069
1070 l_num_PartyReqCtrl_tab(i) := l_num_ReqCtrl_tab(l_num_ReqCtrl_tab.COUNT);
1071 select WSH_ITM_PARTIES_S.NEXTVAL
1072 into
1073 l_num_itmpartyID_tab(l_num_itmpartyID_tab.COUNT)
1074 from dual;
1075 END LOOP;
1076 END IF;
1077
1078 --Getting the Base Language into the variable
1079 SELECT LANGUAGE_CODE INTO l_LanguageCode
1080 FROM FND_LANGUAGES
1081 WHERE INSTALLED_FLAG = 'B';
1082 IF l_debug_on THEN
1083 WSH_DEBUG_SV.LOG (l_module_name, 'Base Language : ', l_LanguageCode, WSH_DEBUG_SV.C_STMT_LEVEL);
1084 END IF;
1085
1086
1087
1088 IF l_debug_on THEN
1089 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);
1090 END IF;
1091
1092
1093 --Bulk Insert to Request Control Table
1094 IF l_num_ReqCtrl_tab.COUNT <> 0 THEN
1095 FORALL i IN l_num_ReqCtrl_tab.FIRST..l_num_ReqCtrl_tab.LAST
1096 INSERT INTO WSH_ITM_REQUEST_CONTROL(
1097 REQUEST_CONTROL_ID,
1098 APPLICATION_ID,
1099 APPLICATION_USER_ID, --bug 11676284
1100 LANGUAGE_CODE,
1101 PROCESS_FLAG,
1102 SERVICE_TYPE_CODE,
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,
1118 LAST_UPDATE_DATE,
1115 ATTRIBUTE13_VALUE,
1116 ATTRIBUTE14_VALUE,
1117 ATTRIBUTE15_VALUE,
1119 LAST_UPDATED_BY,
1120 CREATION_DATE,
1121 CREATED_BY,
1122 LAST_UPDATE_LOGIN
1123 )
1124 VALUES(
1125 l_num_ReqCtrl_tab(i),
1126 222,
1127 fnd_global.user_id, --bug 11676284
1128 l_LanguageCode,
1129 0,
1130 'PARTY_SYNC',
1131 l_varchar_rcAttrib1_tab(i),
1132 l_varchar_rcAttrib2_tab(i),
1133 l_varchar_rcAttrib3_tab(i),
1134 l_varchar_rcAttrib4_tab(i),
1135 l_varchar_rcAttrib5_tab(i),
1136 l_varchar_rcAttrib6_tab(i),
1137 l_varchar_rcAttrib7_tab(i),
1138 l_varchar_rcAttrib8_tab(i),
1139 l_varchar_rcAttrib9_tab(i),
1140 l_varchar_rcAttrib10_tab(i),
1141 l_varchar_rcAttrib11_tab(i),
1142 l_varchar_rcAttrib12_tab(i),
1143 l_varchar_rcAttrib13_tab(i),
1144 l_varchar_rcAttrib14_tab(i),
1145 l_varchar_rcAttrib15_tab(i),
1146 SYSDATE,
1147 l_user_id,
1148 SYSDATE,
1149 l_user_id,
1150 l_login_id
1151 );
1152 END IF;
1153
1154 IF l_debug_on THEN
1155 WSH_DEBUG_SV.LOG (l_module_name, 'Number of Parties to be inserted : ' , l_num_PartyReqCtrl_tab.COUNT,WSH_DEBUG_SV.C_STMT_LEVEL);
1156 END IF;
1157
1158 --Bulk Insert into Parties Table
1159 IF l_num_PartyReqCtrl_tab.COUNT <> 0 THEN
1160 FOR i IN l_num_PartyReqCtrl_tab.FIRST..l_num_PartyReqCtrl_tab.LAST LOOP
1161 INSERT INTO WSH_ITM_PARTIES (
1162 PARTY_ID,
1163 CUST_SITE_USE_ID,
1164 REQUEST_CONTROL_ID,
1165 PARTY_NAME,
1166 ALTERNATE_NAME,
1167 TAX_REFERENCE,
1168 PARTY_TYPE,
1169 PARTY_ADDRESS1,
1170 PARTY_ADDRESS2,
1171 PARTY_ADDRESS3,
1172 PARTY_ADDRESS4,
1173 PARTY_CITY,
1174 PARTY_STATE,
1175 PARTY_COUNTRY_CODE,
1176 POSTAL_CODE,
1177 ORIGINAL_SYSTEM_REFERENCE,
1178 ACCOUNT_NUMBER,
1179 ACCOUNT_NAME,
1180 PARTY_SITE_NUMBER,
1181 ATTRIBUTE1_VALUE,
1182 ATTRIBUTE2_VALUE,
1183 ATTRIBUTE3_VALUE,
1184 ATTRIBUTE4_VALUE,
1185 ATTRIBUTE5_VALUE,
1186 ATTRIBUTE6_VALUE,
1187 ATTRIBUTE7_VALUE,
1188 ATTRIBUTE8_VALUE,
1189 ATTRIBUTE9_VALUE,
1190 ATTRIBUTE10_VALUE,
1191 ATTRIBUTE11_VALUE,
1192 ATTRIBUTE12_VALUE,
1193 ATTRIBUTE13_VALUE,
1194 ATTRIBUTE14_VALUE,
1195 ATTRIBUTE15_VALUE,
1196 LAST_UPDATE_DATE,
1197 LAST_UPDATED_BY,
1198 CREATION_DATE,
1199 CREATED_BY,
1200 LAST_UPDATE_LOGIN,
1201 OPERATING_UNIT,
1202 ADDRTYPE, -- gtm , new columns added below
1203 HZ_PARTY_ID ,
1204 HZ_PARTY_NUMBER,
1205 HZ_PARTY_TYPE ,
1206 HZ_PARTY_STATUS ,
1207 HZ_HQ_BRANCH_IND ,
1208 HZ_SIC_CODE ,
1209 HZ_SIC_CODE_TYPE ,
1210 PERSON_FIRST_NAME ,
1211 PERSON_MIDDLE_NAME ,
1212 PERSON_LAST_NAME ,
1213 HZ_LOCATION_ID ,
1214 HZ_COUNTRY ,
1215 HZ_ADDRESS1 ,
1216 HZ_ADDRESS2 ,
1217 HZ_ADDRESS3 ,
1218 HZ_ADDRESS4 ,
1219 HZ_CITY ,
1220 HZ_POSTAL_CODE ,
1221 HZ_STATE ,
1222 HZ_PROVINCE ,
1223 HZ_COUNTY
1224 )
1225 VALUES(
1226 l_num_itmpartyID_tab(i),
1227 l_num_sourceID_tab(i),
1228 l_num_PartyReqCtrl_tab(i),
1229 l_varchar_PartyName_tab(i),
1230 l_varchar_AlternateName_tab(i),
1231 l_varchar_TaxRef_tab(i),
1232 l_varchar_PartnrType_tab(i),
1233 l_varchar_Address1_tab(i),
1234 l_varchar_Address2_tab(i),
1238 l_varchar_State_tab(i),
1235 l_varchar_Address3_tab(i),
1236 l_varchar_Address4_tab(i),
1237 l_varchar_City_tab(i),
1239 l_varchar_Country_tab(i),
1240 l_varchar_PostalCode_tab(i),
1241 l_num_hzpartySiteID_tab(i),
1242 l_varchar_AcctNumber_tab(i),
1243 l_varchar_AcctName_tab(i),
1244 l_varchar_SiteNumber_tab(i),
1245 l_varchar_Attrib1_tab(i),
1246 l_varchar_Attrib2_tab(i),
1247 l_varchar_Attrib3_tab(i),
1248 l_varchar_Attrib4_tab(i),
1249 l_varchar_Attrib5_tab(i),
1250 l_varchar_Attrib6_tab(i),
1251 l_varchar_Attrib7_tab(i),
1252 l_varchar_Attrib8_tab(i),
1253 l_varchar_Attrib9_tab(i),
1254 l_varchar_Attrib10_tab(i),
1255 l_varchar_Attrib11_tab(i),
1256 l_varchar_Attrib12_tab(i),
1257 l_varchar_Attrib13_tab(i),
1258 l_varchar_Attrib14_tab(i),
1259 l_varchar_Attrib15_tab(i),
1260 --num_orgId_tab(i),
1261 SYSDATE,
1262 l_user_id,
1263 SYSDATE,
1264 l_user_id,
1265 l_login_id,
1266 l_operating_unit(i),
1267 l_address_type(i), -- gtm , new tabs added below
1268 l_num_hzpartyID_tab(i),
1269 l_hz_party_num_tab(i),
1270 l_hz_party_type_tab(i) ,
1271 l_hz_status_tab(i) ,
1272 l_hz_hq_branch_ind_tab(i) ,
1273 l_hz_sic_code_tab(i) ,
1274 l_hz_sic_code_type_tab(i) ,
1275 l_person_first_name_tab(i) ,
1276 l_person_middle_name_tab(i) ,
1277 l_person_last_name_tab(i) ,
1278 l_hz_location_id_tab(i) ,
1279 l_hz_country_tab(i) ,
1280 l_hz_address1_tab(i) ,
1281 l_hz_address2_tab(i) ,
1282 l_hz_address3_tab(i) ,
1283 l_hz_address4_tab(i) ,
1284 l_hz_city_tab(i) ,
1285 l_hz_postal_code_tab(i) ,
1286 l_hz_state_tab(i) ,
1287 l_hz_province_tab(i) ,
1288 l_hz_county_tab(i)
1289 );
1290 END LOOP;
1291 END IF;
1292
1293 -- Selecting Contacts Data
1294 IF l_num_itmpartyID_tab.COUNT <> 0 THEN
1295 For i in l_num_itmpartyID_tab.FIRST..l_num_itmpartyID_tab.LAST LOOP
1296
1297
1298 IF l_debug_on THEN
1299 WSH_DEBUG_SV.LOG (l_module_name, 'Getting Contacts for Party : ' , l_varchar_PartyName_tab(i),WSH_DEBUG_SV.C_STMT_LEVEL);
1300 WSH_DEBUG_SV.LOG (l_module_name, 'gtm :Getting Contacts for Party : ', l_num_hzpartySiteID_tab.count, WSH_DEBUG_SV.C_STMT_LEVEL);
1301 END IF;
1302 -- gtm {
1303 if (l_num_hzpartySiteID_tab.count <> 0 and l_num_hzpartySiteID_tab(i) is not NULL ) then
1304
1305 /* Bug 7297690 Added check to determine whether Contacts for this party site is already being inserted into wsh_itm_party_contacts
1306 - For each party site this query will be fired n no of times, n is no of usage of this site (billto, shipto etc)
1307 - This needs to be reduced to one, since when the party site is queried first time, contacts will be added
1308 and for all other hits contact will not be inserted since it is inserted first time itself. */
1309 IF NOT l_party_site_id_cache.Exists(l_num_hzpartySiteID_tab(i)) THEN
1310
1311 l_party_site_id_cache(l_num_hzpartySiteID_tab(i)) := 'Y';
1312
1313 --Bug 7297690 Added column hoc.org_contact_id to Store the Org Contact id of the contact that needs to be screened */
1314 SELECT DISTINCT hr.party_id,
1315 hp.party_name,
1316 nvl(hoc.job_title,hoc.job_title_code),
1317 hoc.org_contact_id, -- Added in Bug 7297690
1318 hcar.attribute1,
1319 hcar.attribute2,
1320 hcar.attribute3,
1321 hcar.attribute4,
1322 hcar.attribute5,
1323 hcar.attribute6,
1324 hcar.attribute7,
1325 hcar.attribute8,
1326 hcar.attribute9,
1327 hcar.attribute10,
1328 hcar.attribute11,
1329 hcar.attribute12,
1330 hcar.attribute13,
1331 hcar.attribute14,
1332 hcar.attribute15,
1333 hp.party_id , -- gtm related v
1334 hp.party_number,
1335 decode(hp.party_type,'ORGANIZATION',hp.organization_name_phonetic, hpp.person_name_phonetic),
1336 hp.PARTY_TYPE ,
1337 hp.STATUS ,
1341 hp.COUNTRY ,
1338 hp.PERSON_FIRST_NAME ,
1339 hp.PERSON_MIDDLE_NAME ,
1340 hp.PERSON_LAST_NAME ,
1342 hp.ADDRESS1 ,
1343 hp.ADDRESS2 ,
1344 hp.ADDRESS3 ,
1345 hp.ADDRESS4 ,
1346 hp.CITY ,
1347 hp.POSTAL_CODE ,
1348 hp.STATE ,
1349 hp.PROVINCE ,
1350 hp.COUNTY
1351 BULK COLLECT INTO
1352 l_num_partyrel_tab,
1353 l_varchar_CtPartyName_tab,
1354 l_varchar_CtPointType_tab,
1355 l_org_contact_id_tab, -- Added in Bug 7297690
1356 l_varchar_Attrib1_tab,
1357 l_varchar_Attrib2_tab,
1358 l_varchar_Attrib3_tab,
1359 l_varchar_Attrib4_tab,
1360 l_varchar_Attrib5_tab,
1361 l_varchar_Attrib6_tab,
1362 l_varchar_Attrib7_tab,
1363 l_varchar_Attrib8_tab,
1364 l_varchar_Attrib9_tab,
1365 l_varchar_Attrib10_tab,
1366 l_varchar_Attrib11_tab,
1367 l_varchar_Attrib12_tab,
1368 l_varchar_Attrib13_tab,
1369 l_varchar_Attrib14_tab,
1370 l_varchar_Attrib15_tab,
1371 l_num_CtPartyId_tab,
1372 l_varchar_CtPartyNum_tab,
1373 l_ct_alternate_name_tab,
1374 l_ct_party_type_tab ,
1375 l_ct_status_tab ,
1376 l_ct_first_name_tab ,
1377 l_ct_middle_name_tab ,
1378 l_ct_last_name_tab ,
1379 l_ct_country_tab ,
1380 l_ct_address1_tab ,
1381 l_ct_address2_tab ,
1382 l_ct_address3_tab ,
1383 l_ct_address4_tab ,
1384 l_ct_city_tab ,
1385 l_ct_postal_code_tab ,
1386 l_ct_state_tab ,
1387 l_ct_province_tab ,
1388 l_ct_county_tab
1389 from hz_relationships hr, --TCA view removal Starts
1390 hz_Parties hp,
1391 hz_org_contacts hoc,
1392 hz_cust_account_roles hcar,
1393 hz_Person_profiles hpp
1394 where hr.subject_table_name = 'HZ_PARTIES'
1395 and hr.object_table_name = 'HZ_PARTIES'
1396 and hr.Directional_flag = 'F'
1397 and object_id = l_num_hzpartyID_tab(i)
1398 and hr.relationship_code = 'CONTACT_OF'
1399 and hr.subject_id = hp.party_id
1400 and hoc.party_relationship_id = hr.relationship_id
1401 and nvl(hoc.party_site_id,l_num_hzpartySiteID_tab(i)) = l_num_hzpartySiteID_tab(i)
1402 and hcar.party_id(+) = hr.party_id
1403 and hp.PARTY_ID = hpp.PARTY_ID(+); -- TCA view removal Ends
1404
1405
1406 -- Added for Bug 3420203
1407 IF l_num_partyrel_tab.COUNT <> 0 THEN
1408 -- Bug 7297690 Added check to determine whether party contact is already being inserted into wsh_itm_party_contacts
1409 FOR j IN l_num_partyrel_tab.FIRST..l_num_partyrel_tab.LAST LOOP
1410 BEGIN
1411
1412 IF (l_org_contact_cache(l_org_contact_id_tab(j)) = 'Y' ) THEN
1413 IF l_debug_on THEN
1414 WSH_DEBUG_SV.LOGMSG (l_module_name,'Party Contact Already inserted ',WSH_DEBUG_SV.C_STMT_LEVEL);
1415 END IF;
1416 END IF;
1417 EXCEPTION
1418 WHEN NO_DATA_FOUND THEN
1419 BEGIN
1420 SELECT email_address into l_varchar_ctEmail_tab(j) FROM
1421 hz_contact_points where contact_point_type ='EMAIL'
1422 and owner_table_id = l_num_partyrel_tab(j)
1423 --added for bug 6391747
1424 and owner_table_name = 'HZ_PARTIES'
1425 and primary_flag ='Y';
1426 EXCEPTION
1427 WHEN NO_DATA_FOUND THEN
1428 l_varchar_ctEmail_tab(j) :=null;
1429 END ;
1430
1431 BEGIN
1432 SELECT phone_number into l_varchar_ctPhone_tab(j) FROM
1433 hz_contact_points where contact_point_type ='PHONE'
1434 AND owner_table_id = l_num_partyrel_tab(j)
1435 --added for bug 6391747
1436 AND owner_table_name = 'HZ_PARTIES'
1437 AND phone_line_type= 'GEN'
1438 and primary_flag ='Y';
1439 EXCEPTION
1440 WHEN NO_DATA_FOUND THEN
1441 l_varchar_ctPhone_tab(j) :=null;
1442 END ;
1443 BEGIN
1444 SELECT phone_number into l_varchar_ctFax_tab(j) FROM
1445 hz_contact_points where contact_point_type ='PHONE'
1449 and phone_line_type= 'FAX'
1446 AND owner_table_id = l_num_partyrel_tab(j)
1447 --added for bug 6391747
1448 AND owner_table_name = 'HZ_PARTIES'
1450 and primary_flag ='Y';
1451 EXCEPTION
1452 WHEN NO_DATA_FOUND THEN
1453 l_varchar_ctFax_tab(j) :=null;
1454 END ;
1455 END;
1456
1457 END LOOP;
1458 END IF;
1459
1460 IF l_debug_on THEN
1461 WSH_DEBUG_SV.LOG (l_module_name, 'Number of Contacts : ' , l_varchar_CtPartyName_tab.COUNT,WSH_DEBUG_SV.C_STMT_LEVEL);
1462 END IF;
1463 -- Bug 7297690 Inserting Party contact only if it is not already populated in wsh_itm_party_contacts
1464 if l_varchar_CtPartyName_tab.COUNT <> 0 THEN
1465
1466 FOR j in l_varchar_CtPartyName_tab.FIRST..l_varchar_CtPartyName_tab.LAST LOOP
1467 -- WSH_DEBUG_SV.LOG (l_module_name,'org contact : ',l_org_contact_id_tab(j),WSH_DEBUG_SV.C_STMT_LEVEL);
1468 BEGIN
1469 IF (l_org_contact_cache(l_org_contact_id_tab(j)) = 'Y' ) THEN
1470 IF l_debug_on THEN
1471 WSH_DEBUG_SV.LOGMSG (l_module_name,' Party Contact Already inserted ',WSH_DEBUG_SV.C_STMT_LEVEL);
1472 END IF;
1473 END IF;
1474 EXCEPTION
1475 WHEN NO_DATA_FOUND THEN
1476 IF l_debug_on THEN
1477 WSH_DEBUG_SV.LOGMSG (l_module_name,'Party Contact being inserted ',WSH_DEBUG_SV.C_STMT_LEVEL);
1478 END IF;
1479 l_org_contact_cache(l_org_contact_id_tab(j)) := 'Y';
1480 insert into wsh_itm_party_contacts
1481 (
1482 PARTY_ID ,
1483 NAME ,
1484 JOB_TITLE ,
1485 EMAIL ,
1486 PHONE,
1487 FAX ,
1488 ATTRIBUTE1_VALUE,
1489 ATTRIBUTE2_VALUE,
1490 ATTRIBUTE3_VALUE,
1491 ATTRIBUTE4_VALUE,
1492 ATTRIBUTE5_VALUE,
1493 ATTRIBUTE6_VALUE,
1494 ATTRIBUTE7_VALUE,
1495 ATTRIBUTE8_VALUE,
1496 ATTRIBUTE9_VALUE,
1497 ATTRIBUTE10_VALUE,
1498 ATTRIBUTE11_VALUE,
1499 ATTRIBUTE12_VALUE,
1500 ATTRIBUTE13_VALUE,
1501 ATTRIBUTE14_VALUE,
1502 ATTRIBUTE15_VALUE,
1503 LAST_UPDATE_DATE,
1504 LAST_UPDATED_BY,
1505 CREATION_DATE,
1506 CREATED_BY,
1507 LAST_UPDATE_LOGIN,
1508 HZ_PARTY_ID,
1509 HZ_PARTY_NUMBER,
1510 ALTERNATE_NAME,
1511 HZ_PARTY_TYPE,
1512 HZ_PARTY_STATUS,
1513 PERSON_FIRST_NAME,
1514 PERSON_MIDDLE_NAME,
1515 PERSON_LAST_NAME,
1516 HZ_COUNTRY ,
1517 HZ_ADDRESS1 ,
1518 HZ_ADDRESS2 ,
1519 HZ_ADDRESS3 ,
1520 HZ_ADDRESS4 ,
1521 HZ_CITY ,
1522 HZ_POSTAL_CODE,
1523 HZ_STATE ,
1524 HZ_PROVINCE ,
1525 HZ_COUNTY
1526 )
1527 VALUES
1528 (
1529 l_num_itmpartyID_tab(i),
1530 l_varchar_CtPartyName_tab(j),
1531 l_varchar_CtPointType_tab(j),
1532 l_varchar_ctEmail_tab(j),
1533 l_varchar_ctPhone_tab(j),
1534 l_varchar_ctFax_tab(j),
1535 l_varchar_Attrib1_tab(j),
1536 l_varchar_Attrib2_tab(j),
1537 l_varchar_Attrib3_tab(j),
1538 l_varchar_Attrib4_tab(j),
1539 l_varchar_Attrib5_tab(j),
1540 l_varchar_Attrib6_tab(j),
1541 l_varchar_Attrib7_tab(j),
1542 l_varchar_Attrib8_tab(j),
1543 l_varchar_Attrib9_tab(j),
1544 l_varchar_Attrib10_tab(j),
1545 l_varchar_Attrib11_tab(j),
1546 l_varchar_Attrib12_tab(j),
1547 l_varchar_Attrib13_tab(j),
1548 l_varchar_Attrib14_tab(j),
1549 l_varchar_Attrib15_tab(j),
1550 SYSDATE,
1551 l_user_id,
1552 SYSDATE,
1553 l_user_id,
1554 l_login_id,
1555 l_num_CtPartyId_tab(j), -- gtm
1556 l_varchar_CtPartyNum_tab(j),
1557 l_ct_alternate_name_tab(j),
1558 l_ct_party_type_tab(j) ,
1559 l_ct_status_tab(j) ,
1560 l_ct_first_name_tab(j) ,
1561 l_ct_middle_name_tab(j) ,
1562 l_ct_last_name_tab(j) ,
1563 l_ct_country_tab(j) ,
1564 l_ct_address1_tab(j) ,
1565 l_ct_address2_tab(j) ,
1566 l_ct_address3_tab(j) ,
1567 l_ct_address4_tab(j) ,
1568 l_ct_city_tab(j) ,
1569 l_ct_postal_code_tab(j) ,
1570 l_ct_state_tab(j) ,
1571 l_ct_province_tab(j) ,
1572 l_ct_county_tab(j)
1573 );
1574 END;
1575 END LOOP;
1576
1577 END IF;
1578 END IF; -- Party site cache if loop
1579 end if; -- gtm }
1580 END LOOP;
1581 END IF;
1582
1583 --
1584 -- Debug Statements
1585 --
1586 IF l_debug_on THEN
1587 WSH_DEBUG_SV.pop(l_module_name);
1588 END IF;
1589 --
1590
1591
1592 EXCEPTION
1593 WHEN OTHERS THEN
1594 IF l_debug_on THEN
1595 WSH_DEBUG_SV.logmsg(l_module_name,'The unexpected Error Code ' || SQLCODE || ' : ' || SQLERRM);
1596 END IF;
1597 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
1598 errbuf := 'Procedure WSH_ITM_PARTY_SYNC.POPULATE_DATA failed with unexpected error';
1599 retcode := '2';
1600 IF l_debug_on THEN
1601 WSH_DEBUG_SV.pop(l_module_name);
1602 END IF;
1603 RETURN;
1604
1605 END POPULATE_DATA;
1606
1607 END WSH_ITM_PARTY_SYNC;