[Home] [Help]
PACKAGE BODY: APPS.FTE_LOCATION_PARAMETERS_PKG
Source
1 PACKAGE BODY FTE_LOCATION_PARAMETERS_PKG AS
2 /* $Header: FTEGFACB.pls 120.1 2005/07/18 03:27:48 skattama noship $ */
3
4 -- -------------------------------------------------------------------------- --
5 -- --
6 -- NAME: FTE_LOCATION_PARAMETERS_PKG --
7 -- TYPE: PACKAGE BODY --
8 -- DESCRIPTION: Facility Creation Package. --
9 -- --
10 -- PROCEDURES: --
11 -- i. Create_Facilities: Given location Id's from the WSH_LOCATIONS --
12 -- table, it creates a facility for each --
13 -- location. --
14 -- ii. Create_Facility: Creates a single facility, given a location ID --
15 -- iii. Get_Facility_Info: Returns information about a facility, given --
16 -- a location ID. --
17 -- --
18 -- CHANGE CONTROL LOG --
19 -- DATE VERSION BY BUG DESCRIPTION --
20 -- ---------- ------- -------- ------- ---------------------------------- --
21 -- 04/07/2003 1.0 ndodoo N/A Created. --
22 ----------------------------------------------------------------------------- --
23
24 G_PKG_NAME VARCHAR2(50) := 'FTE_LOCATION_PARAMETERS_PKG';
25 g_cache BOOLEAN := TRUE;
26 g_cached BOOLEAN := FALSE;
27
28 g_nonbinary_indices NoIndex_Number_Tbl_Type := NoIndex_Number_Tbl_Type();
29 --
30 g_Facility_Descriptions Index_Varchar100_Tbl_Type;
31 g_nonindexed_Descriptions NoIndex_Varchar100_Tbl_Type := NoIndex_Varchar100_Tbl_Type();
32
33 g_dup_locations STRINGARRAY;
34
35 g_company_names WSH_LOCATIONS_PKG.Address_Tbl_Type;
36 g_site_names WSH_LOCATIONS_PKG.LocationCode_Tbl_Type;
37 g_cur_index NUMBER;
38 g_names_exist BOOLEAN;
39
40 -----------------------------------------------------------------------------
41 -- PROCEDURE LogMsg
42 --
43 -- Purpose
44 --
45 -- Parameters
46 --
47 --
48 -----------------------------------------------------------------------------
49 PROCEDURE LogMsg (p_module_name IN VARCHAR2,
50 p_text IN VARCHAR2) IS
51 BEGIN
52 IF l_debug_on THEN
53 WSH_DEBUG_SV.LogMsg(p_module_name, p_text);
54 END IF;
55 -- testpkg2.print_to_file(p_text);
56 END LogMsg;
57
58
59 -----------------------------------------------------------------------------
60 -- PROCEDURE LogMsg
61 --
62 -- Purpose
63 --
64 -- Parameters
65 --
66 --
67 -----------------------------------------------------------------------------
68 PROCEDURE LogMsg (p_module_name IN VARCHAR2,
69 p_attribute IN VARCHAR2,
70 p_value IN VARCHAR2) IS
71 BEGIN
72 IF l_debug_on THEN
73 WSH_DEBUG_SV.Log(x_Module => p_module_name,
77 -- testpkg2.print_to_file(p_attribute || '=> ' || p_value);
74 x_Text => p_attribute,
75 x_Value => p_value);
76 END IF;
78 END LogMsg;
79
80 -----------------------------------------------------------------------------
81 -- PROCEDURE Init_Debug
82 --
83 -- Purpose
84 --
85 -- Parameters
86 --
87 --
88 -----------------------------------------------------------------------------
89 PROCEDURE Init_Debug(p_module_name IN VARCHAR2) IS
90 BEGIN
91
92 --SETUP DEBUGGING
93 --SETUP DEBUGGING
94 IF (NOT g_debug_set) THEN
95 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
96 IF l_debug_on IS NULL THEN
97 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
98 END IF;
99 g_debug_set := TRUE;
100 END IF;
101
102 IF l_debug_on THEN
103 WSH_DEBUG_SV.push(p_module_name);
104 END IF;
105 END Init_Debug;
106
107 -----------------------------------------------------------------------------
108 -- PROCEDURE Exit_Debug
109 --
110 -- Purpose
111 --
112 -- Parameters
113 --
114 --
115 -----------------------------------------------------------------------------
116 PROCEDURE Exit_Debug(p_module_name IN VARCHAR2) IS
117 BEGIN
118 IF l_debug_on THEN
119 WSH_DEBUG_SV.pop(p_module_name);
120 END IF;
121 END Exit_Debug;
122
123
124 -----------------------------------------------------------------------------
125 -- PROCEDURE Reset_All
126 --
127 -- Purpose: Reset Package Level global variables.
128 --
129 -- Parameters: None
130 -----------------------------------------------------------------------------
131 PROCEDURE Reset_All IS
132 BEGIN
133 g_cache := true;
134 g_cached := false;
135
136 g_nonbinary_indices.delete;
137 g_facility_descriptions.Delete;
138 g_nonindexed_descriptions.delete;
139 END Reset_All;
140
141 -----------------------------------------------------------------------------
142 -- PROCEDURE
143 --
144 -- Purpose: Returns TRUE if the number is a binary integer.
145 --
146 -- Parameters:
147 -- p_location_id IN NUMBER: location ID of facility
148 --
149 -----------------------------------------------------------------------------
150 FUNCTION Is_Binary_Integer (p_Number IN NUMBER) RETURN BOOLEAN IS
151 BEGIN
152 RETURN( p_Number >= -2147483647 AND p_Number <= 2147483647);
153 END Is_Binary_Integer;
154
155 -----------------------------------------------------------------------------
156 -- PROCEDURE Create_Description
157 --
158 -- Purpose: Create the company description from the company name and site.
159 --
160 -- Parameters:
161 -- 1. p_company_name
162 -- 2. p_site
163 -----------------------------------------------------------------------------
164 FUNCTION Create_Description(p_company_name IN VARCHAR2,
165 p_site IN VARCHAR2) RETURN VARCHAR2
166 IS
167 l_compname_length NUMBER;
168 l_description VARCHAR2(300);
169
170 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'Create_Description';
171 BEGIN
172 Init_Debug(l_module_name);
173
174 l_compname_length := 59 - length(p_site);
175 l_description := substrb(p_company_name, 0, l_compname_length) || '_' || p_site;
176
177 Exit_Debug(l_module_name);
178 RETURN l_description;
179 END Create_Description;
180
181
182 -----------------------------------------------------------------------------
183 -- FUNCTION Fetch_Single_Facility_Info
184 --
185 -- Purpose: Fetches and derives the facility code of a facility, from the
186 -- facility's company information.
187 -- Parameters:
188 -- p_location_id IN NUMBER: location ID of facility
189 --
190 -----------------------------------------------------------------------------
191 PROCEDURE Fetch_Single_Facility_Info(p_location_id IN NUMBER,
192 x_description OUT NOCOPY VARCHAR2) IS
193
194 l_hzr VARCHAR2(5);
195 l_cdate DATE;
196 l_company_type VARCHAR2(30);
197 l_description VARCHAR2(300);
198 l_facility_code VARCHAR2(60);
199 l_company_name VARCHAR2(300);
200 l_site VARCHAR2(50);
201
202 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'Fetch_Single_Facility_Info';
203
204 --Obtain company information for internal sites.
205 CURSOR c_hr_descriptions IS
206 --ORGANIZATION
207 SELECT hou.name company_name,
208 hou.name site,
209 hou.creation_date cdate,
210 'ORGANIZATION' company_type
211 FROM wsh_locations wl, hr_organization_units hou,
212 HR_ORGANIZATION_INFORMATION HOI1, MTL_PARAMETERS MP
213 WHERE wl.source_location_id = p_location_id
214 AND wl.location_source_code = 'HR'
215 AND wl.source_location_id = hou.location_id
216 AND HOU.ORGANIZATION_ID = HOI1.ORGANIZATION_ID
217 AND HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
218 AND HOI1.ORG_INFORMATION1 = 'INV'
219 AND HOI1.ORG_INFORMATION2 = 'Y'
220 AND ( HOI1.ORG_INFORMATION_CONTEXT || '') = 'CLASS'
221 ORDER BY cdate;
222
223 --Obtain company information for external sites.
224 CURSOR c_hz_descriptions IS
225 --CARRIER
226 SELECT wc.carrier_name Company_Name,
227 wc.carrier_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
228 hps.creation_date Cdate,
232 WHERE wl.source_location_id = p_location_id
229 'CARRIER' Company_Type
230 FROM wsh_locations wl, hz_party_sites hps,
231 wsh_carriers_v wc
233 AND wl.location_source_code = 'HZ'
234 AND wl.source_location_id = hps.location_id
235 AND hps.party_id = wc.carrier_id
236 AND wc.active= 'A'
237 UNION
238 --CUSTOMER
239 SELECT hp.party_name Company_Name,
240 hp.party_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
241 hps.creation_date Cdate,
242 'CUSTOMER' Company_Type
243 FROM wsh_locations wl, hz_party_sites hps, hz_parties hp,
244 hz_cust_acct_sites_all hcas
245 WHERE wl.source_location_id = p_location_id
246 AND wl.location_source_code = 'HZ'
247 AND wl.source_location_id = hps.location_id
248 AND hps.party_id=hp.party_id
249 AND hp.status='A'
250 AND hcas.party_site_id = hps.party_site_id
251 UNION
252 --SUPPLIER
253 SELECT hp.party_name Company_Name,
254 hp.party_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
255 hps.creation_date Cdate,
256 'SUPPLIER' Company_Type
257 FROM wsh_locations wl, po_vendors po, hz_relationships rel,
258 hz_party_sites hps, hz_parties hp
259 WHERE wl.source_location_id = p_location_id
260 AND wl.source_location_id = hps.location_id
261 AND hps.party_id = hp.party_id
262 AND hp.status='A'
263 AND rel.relationship_type = 'POS_VENDOR_PARTY'
264 AND rel.object_id = hp.party_id
265 AND rel.object_table_name = 'HZ_PARTIES'
266 AND rel.object_type = 'ORGANIZATION'
267 AND rel.subject_table_name = 'PO_VENDORS'
268 AND rel.subject_id = po.vendor_id
269 AND rel.subject_type = 'POS_VENDOR'
270 ORDER BY cdate;
271
272
273 BEGIN
274 Init_Debug(l_module_name);
275
276 LogMsg(l_module_name, 'p_location_id' || p_location_id);
277 x_description := NULL;
278
279 BEGIN
280 SELECT location_source_code INTO l_hzr
281 FROM WSH_LOCATIONS
282 WHERE wsh_location_id = p_location_id;
283 EXCEPTION
284 WHEN OTHERS THEN
285 NULL;
286 END;
287
288 IF (l_hzr = 'HZ') THEN
289 OPEN c_hz_descriptions;
290 FETCH c_hz_descriptions INTO l_company_name, l_site, l_cdate, l_company_type;
291 CLOSE c_hz_descriptions;
292
293 ELSIF (l_hzr = 'HR') THEN
294 OPEN c_hr_descriptions;
295 FETCH c_hr_descriptions INTO l_company_name, l_site, l_cdate, l_company_type;
296 CLOSE c_hr_descriptions;
297
298 ELSE
299 LogMsg(l_module_name, 'ERROR: LOCATION IS NEITHER HR NOR HZ. Location ID = ' || p_location_id);
300 x_description := NULL;
301 END IF;
302
303 IF (l_company_name IS NOT NULL AND l_site IS NOT NULL) THEN
304 x_description := create_description(l_company_name, l_site);
305 END IF;
306
307 Exit_Debug(l_module_name);
308 EXCEPTION
309 WHEN NO_DATA_FOUND THEN
310 LogMsg(l_module_name, 'LOCATION ID ' || p_location_id || ' IS NOT A VALID FACILITY LOCATION.');
311 Exit_Debug(l_module_name);
312 END Fetch_Single_Facility_Info;
313
314 -----------------------------------------------------------------------------
315 -- PROCEDURE Fetch_Facility_Descriptions
316 --
317 -- Purpose Fetch company information and derive and cache the descriptions
318 -- for all locations in WSH_LOCATIONS that are not already in
319 -- FTE_LOCATION_PARAMETERS
320 -- Parameters None.
321 -----------------------------------------------------------------------------
322 PROCEDURE Fetch_Facility_Descriptions IS
323
324 l_compNames Index_Varchar100_Tbl_Type;
325 l_compSites Index_Varchar100_Tbl_Type;
326 l_locIds WSH_UTIL_CORE.Id_Tab_Type;
327 l_company_types Index_Varchar100_Tbl_Type;
328 l_dates WSH_UTIL_CORE.Date_Tab_Type;
329 l_locId NUMBER;
330 l_desc VARCHAR2(100);
331 l_locid_exists BOOLEAN;
332
333 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'Fetch_Facility_Descriptions';
334
335 --counters
336 k NUMBER;
337 m NUMBER;
338
339 --Fetch company locations for all locations that exist in WSH_LOCATIONS
340 --but NOT in FTE_LOCATION_PARAMETERS.
341 --Note that this query is the same as the one in fetch_single_facility_info,
342 --but without binding a location id. We order by date of creation, because
343 --in the case of multiple sites for an organization, we only pick the
344 --earliest one.
345 CURSOR c_hzr_descriptions IS
346 SELECT hou.name Company_Name,
347 wl.wsh_location_id LocId,
348 hou.name Site,
349 hou.creation_date Cdate,
350 'ORGANIZATION' Company_Type
351 FROM wsh_locations wl, hr_organization_units hou,
352 HR_ORGANIZATION_INFORMATION HOI1, MTL_PARAMETERS MP,fte_location_parameters flp
353 WHERE wl.wsh_location_id = flp.location_id(+)
354 AND flp.location_id IS NULL
355 AND wl.location_source_code = 'HR'
356 AND wl.source_location_id = hou.location_id
357 AND HOU.ORGANIZATION_ID = HOI1.ORGANIZATION_ID
358 AND HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
359 AND HOI1.ORG_INFORMATION1 = 'INV'
360 AND HOI1.ORG_INFORMATION2 = 'Y'
361 AND ( HOI1.ORG_INFORMATION_CONTEXT || '') = 'CLASS'
362 UNION
363 --CARRIER
364 SELECT wc.carrier_name Company_Name,
365 wl.wsh_location_id LocId,
369 FROM wsh_locations wl, hz_party_sites hps, wsh_carriers_v wc,
366 wc.carrier_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
367 hps.creation_date cdate,
368 'CARRIER' company_type
370 fte_location_parameters flp
371 WHERE wl.wsh_location_id = flp.location_id(+)
372 AND flp.location_id IS NULL
373 AND wl.location_source_code = 'HZ'
374 AND wl.source_location_id = hps.location_id
375 AND wc.active = 'A'
376 AND hps.party_id = wc.carrier_id
377 UNION
378 --CUSTOMER
379 SELECT hp.party_name Company_Name,
380 wl.wsh_location_id LocId,
381 hp.party_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
382 hps.creation_date Cdate,
383 'CUSTOMER' Company_Type
384 FROM wsh_locations wl, hz_party_sites hps, hz_parties hp,
385 hz_cust_acct_sites_all hcas, fte_location_parameters flp,
386 wsh_location_owners wlo
387 WHERE wl.wsh_location_id = flp.location_id(+)
388 AND wl.wsh_location_id = wlo.wsh_location_id
389 AND wlo.owner_type = 2
390 AND wlo.owner_party_id = hp.party_id
391 AND flp.location_id IS NULL
392 AND wl.location_source_code = 'HZ'
393 AND wl.source_location_id = hps.location_id
394 AND hp.status='A'
395 AND hps.party_id=hp.party_id
396 AND hcas.party_site_id = hps.party_site_id
397 UNION
398 --SUPPLIER
399 SELECT hp.party_name Company_Name,
400 wl.wsh_location_id LocId,
401 hp.party_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
402 hps.creation_date Cdate, 'SUPPLIER' Company_Type
403 FROM wsh_locations wl, po_vendors po, hz_relationships rel,
404 hz_party_sites hps, hz_parties hp, fte_location_parameters flp
405 WHERE wl.wsh_location_id = flp.location_id(+)
406 AND flp.location_id IS NULL
407 AND wl.source_location_id = hps.location_id
408 AND hp.status='A'
409 AND hps.party_id = hp.party_id
410 AND rel.relationship_type = 'POS_VENDOR_PARTY'
411 AND rel.object_id = hp.party_id
412 AND rel.object_table_name = 'HZ_PARTIES'
413 AND rel.object_type = 'ORGANIZATION'
414 AND rel.subject_table_name = 'PO_VENDORS'
415 AND rel.subject_id = po.vendor_id
416 AND rel.subject_type = 'POS_VENDOR'
417 ORDER BY locid, cdate;
418
419 BEGIN
420 Init_Debug(l_module_name);
421
422 OPEN c_hzr_descriptions;
423 FETCH c_hzr_descriptions
424 BULK COLLECT INTO l_compNames, l_locIds, l_compSites, l_dates, l_company_types;
425 CLOSE c_hzr_descriptions;
426
427 IF (l_locIds.COUNT = 0) THEN
428 RETURN;
429 END IF;
430
431 k := l_locIds.FIRST;
432 LOOP
433 l_locId := l_locIds(k);
434 --If the location ID falls within the range of a binary integer,
435 --then we can put it in a table indexed by the location id for
436 --quicker reference.
437 IF (Is_Binary_Integer(l_locId)) THEN
438 l_desc := create_description(l_compNames(k), l_compSites(k));
439 g_Facility_Descriptions(l_locId) := l_desc;
440
441 --Otherwise we have to put in a non-indexed table, and search for
442 --it "manually"
443 ELSE
444 l_locid_exists := FALSE;
445 IF g_nonbinary_indices.EXISTS(1) THEN
446 FOR m IN 1..g_nonbinary_indices.COUNT LOOP
447 IF (g_nonbinary_indices(m) = l_locid) THEN
448 l_desc := create_description(l_compNames(k), l_compSites(k));
449 g_NonIndexed_Descriptions(m) := l_desc;
450 l_locid_exists := TRUE;
451 EXIT; --It already exists
452 END IF;
453 END LOOP;
454 END IF;
455
456 IF (NOT l_locid_exists) THEN
457 g_nonbinary_indices.EXTEND;
458 g_Nonindexed_Descriptions.EXTEND;
459
460 m := g_nonbinary_indices.COUNT;
461 l_desc := create_description(l_compNames(k), l_compSites(k));
462 g_nonbinary_indices(m) := l_locid;
463 g_Nonindexed_Descriptions(m) := l_desc;
464 END IF;
465 END IF;
466
467 EXIT WHEN k = l_locIds.LAST;
468 k := l_locIds.NEXT(k);
469 END LOOP;
470
471
472 g_cached := TRUE;
473 Exit_Debug(l_module_name);
474 EXCEPTION
475 WHEN OTHERS THEN
476 LogMsg(l_module_name, 'UNEXPECTED ERROR: ' || sqlerrm);
477 Exit_Debug(l_module_name);
478 RAISE;
479 END Fetch_Facility_Descriptions;
480
481 -----------------------------------------------------------------------------
482 -- PROCEDURE Get_Facility_Description
483 --
484 -- Purpose Derive a description for a facility given its location ID
485 --
486 -- Parameters
487 -- p_location_id IN NUMBER: location ID of facility
488 --
489 -- Return
490 -- The facility description.
491 -----------------------------------------------------------------------------
492 FUNCTION Get_Facility_Description(p_location_id IN NUMBER)
493 RETURN VARCHAR2 IS
494
495 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.'||G_PKG_NAME||'.Get_Facility_Description';
496 l_debug_on BOOLEAN;
497
498 l_error_msg VARCHAR2(250);
499 l_description VARCHAR2(250);
500 l_company_name VARCHAR2(100);
501 l_site_name VARCHAR2(100);
502
503 BEGIN
504
505 Init_Debug(l_module_name);
509
506 logmsg(l_module_name, 'p_location_id', p_location_id);
507
508 IF (g_names_exist) THEN
510 l_company_name := g_company_names(g_cur_index);
511 IF (g_site_names.EXISTS(g_cur_index)) THEN
512 l_site_name := g_site_names(g_cur_index);
513 END IF;
514 Exit_Debug(l_module_name);
515 RETURN Create_Description(l_company_name, l_site_name);
516
517 ELSIF (g_cache) THEN
518 --derive and cache the descriptions if not already done.
519 IF (NOT g_cached) THEN
520 Fetch_Facility_Descriptions;
521 END IF;
522
523 IF (Is_Binary_Integer(p_location_id)) THEN
524 Exit_Debug(l_module_name);
525 RETURN g_Facility_Descriptions(p_location_id);
526 ELSE
527 --search the non-indexed tables for the description
528 FOR n IN g_nonbinary_indices.FIRST .. g_nonbinary_indices.LAST LOOP
529 IF (g_nonbinary_indices(n) = p_location_id) THEN
530 Exit_Debug(l_module_name);
531 RETURN g_Nonindexed_Descriptions(n);
532 END IF;
533 END LOOP;
534 END IF;
535 ELSE -- no caching: one-time query
536 Fetch_Single_Facility_Info(p_location_id, l_description);
537 Exit_Debug(l_module_name);
538 RETURN l_description;
539 END IF;
540
541 Exit_Debug(l_module_name);
542 RETURN NULL;
543 EXCEPTION
544 WHEN NO_DATA_FOUND THEN
545 l_error_msg := 'LOC ID '||p_location_id||' IS EITHER AN INVALID FACILITY LOCATION OR ALREADY';
546 l_error_msg := l_error_msg || ' EXISTS IN FTE_LOCATION_PARAMETERS.';
547 logmsg(l_module_name, l_error_msg);
548 Exit_Debug(l_module_name);
549 RETURN NULL;
550 WHEN OTHERS THEN
551 logmsg(l_module_name, 'UNEXPECTED ERROR. => ' || sqlerrm);
552 Exit_Debug(l_module_name);
553 RAISE;
554 END Get_Facility_Description;
555
556 -----------------------------------------------------------------------------------
557 -- Start of comments
558 -- API name : Create_Facilities
559 -- Type : Private
560 -- Pre-reqs : None.
561 -- Function : 1. Create a new facility (row in FTE_LOCATION_PARAMETERS) for each
562 -- location id specified in the input table p_location_ids.
563 -- NOTE: It is recommended, but not required, to pass in the corresponding
564 -- <p_company_names> and <p_site_names>.
565 -- (a). If both <p_company_names> and <p_site_names> are not empty
566 -- the facility codes are generated from a combination of
567 -- the location's company_name and site_name. (See function
568 -- Create_Description)
569 -- (b). If the <p_company_names> is NOT empty but <p_site_names> is
570 -- empty, the facility codes are derived from the company names.
571 -- (c). If both <p_company_names> AND <p_site_names> are empty,
572 -- a query is used to obtain the company_name and site_name for
573 -- each location_id (See Fetch_Facility_Descriptions). Caching
574 -- is used to make more efficient.
575 --
576 -- PARAMETERS :
577 -- IN Parameters:
578 -- 1. p_location_ids WSH_LOCATIONS_PKG.ID_Tbl_Type (Required)
579 -- The location ids for the facilities.
580 --
581 -- 2. p_company_names WSH_LOCATIONS_PKG.Address_Tbl_Type (Not Required)
582 -- Corresponding company names for each
583 -- location ID.
584 -- 3. p_site_names WSH_LOCATIONS_PKG.LocationCode_Tbl_Type (Not Required)
585 -- Corresponding site names for the
586 -- location ids, if applicable.
587 --
588 -- OUT Parameters : x_return_status VARCHAR2
589 -- The return status is one of the following:
590 -- i. WSH_UTIL_CORE.G_RET_STS_SUCCESS: All facilities created.
591 -- ii. WSH_UTIL_CORE.G_RET_STS_WARNING: If some of the location_ids
592 -- already had facilities created.
593 -- iii. WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
594 -- Version : 1.0
595 -- Previous version 1.0
596 -- Initial version 1.0
597 -- End of comments
598 -----------------------------------------------------------------------------------
599 PROCEDURE Create_Facilities (p_location_ids IN WSH_LOCATIONS_PKG.ID_Tbl_Type,
600 p_company_names IN WSH_LOCATIONS_PKG.Address_Tbl_Type,
601 p_site_names IN WSH_LOCATIONS_PKG.LocationCode_Tbl_Type,
602 x_return_status OUT NOCOPY VARCHAR2,
603 x_error_msg OUT NOCOPY VARCHAR2) IS
604
605 CURSOR get_location_owner(c_location_id IN NUMBER) IS
606 SELECT owner_type
607 FROM wsh_location_owners
608 WHERE wsh_location_id = c_location_id
609 AND rownum = 1;
610
611 CURSOR get_uom_class(c_uom_code IN VARCHAR2) IS
612 SELECT uom_class
613 FROM mtl_units_of_measure_vl
614 WHERE uom_code = c_uom_code;
615
616 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.CREATE_FACILITIES';
617 l_debug_on BOOLEAN;
618
619 l_facility_codes Index_Varchar100_Tbl_Type;
620 l_facility_descriptions Index_Varchar100_Tbl_Type;
621 l_loc_id NUMBER;
622 l_facility_Description VARCHAR2(200);
623
624 l_start NUMBER;
628 l_msg_count VARCHAR2(10);
625 l_error_code NUMBER;
626 --l_userId NUMBER := FND_GLOBAL.USER_ID;
627 l_userId NUMBER := 123456;
629
630 -- TP Global Attribute
631 l_NPall_Loading_Rate NUMBER := NULL;
632 l_NPall_Unloading_Rate NUMBER := NULL;
633 l_NPall_Handling_Uom VARCHAR2(30) := NULL;
634 l_Pall_Loading_Rate NUMBER := NULL;
635 l_Pall_Unloading_Rate NUMBER := NULL;
636 l_Pall_Handling_Uom VARCHAR2(30) := NULL;
637 l_loadUnload_Time_Uom VARCHAR2(30) := NULL;
638 l_flow_thru_time NUMBER := NULL;
639 l_flow_thru_time_uom VARCHAR2(3) := NULL;
640 l_Pall_Handling_type VARCHAR2(30) := NULL;
641 l_NPall_Handling_type VARCHAR2(30) := NULL;
642
643
644 l_mvmt_required VARCHAR2(15) := 'NEITHER';
645 l_loadUnload_Protocol VARCHAR2(15) := 'JOINT';
646 l_private_residence VARCHAR2(1) := 'N';
647
648 l_owner_type NUMBER;
649 l_include_mileage_flag Index_Varchar100_Tbl_Type;
650 l_Param_Info WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
651 l_return_status VARCHAR2(1);
652 l_api_version NUMBER := 1.0;
653 l_sql_str VARCHAR2(3000);
654
655 --counters
656 k NUMBER;
657 l NUMBER;
658 m NUMBER;
659 BEGIN
660 Init_Debug(l_module_name);
661
662 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
663 g_dup_locations := STRINGARRAY();
664
665 --Ensure that the company_names and location_id tables are the same length;
666 IF (p_company_names.COUNT <> p_location_ids.COUNT) THEN
667 x_error_msg := 'PROGRAMMER ERROR?: <p_company_names> IS NOT THE SAME LENGTH AS';
668 x_error_msg := x_error_msg || ' <p_location_ids>. Reverting to query';
669 LogMsg(l_module_name, x_error_msg);
670 g_names_exist := FALSE;
671 ELSE
672 LogMsg(l_module_name, 'Using Company Names Passed To Procedure ...');
673 g_company_names := p_company_names;
674 g_site_names := p_site_names;
675 g_names_exist := TRUE;
676 END IF;
677
678 -- Get Facility default processing Attributes from
679 -- WSH global
680 WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters(
681 x_Param_Info => l_Param_Info,
682 x_return_status => l_return_status );
683
684 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
685 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
686 END IF;
687
688 l_LoadUnload_Time_Uom := l_param_info.Time_Uom;
689
690 --Get TP Global Attributes from TP Global Preferences
691 IF (WSH_UTIL_CORE.Tp_Is_Installed = 'Y') THEN
692 LogMsg(l_module_name, 'Calling TP API to Obtain Global Parameters');
693
694 BEGIN
695 l_sql_str := 'BEGIN
696 MST_GEOCODING.Get_facility_parameters(
697 p_api_version => :1,
698 p_init_msg_list => :2,
699 x_pallet_load_rate => :3,
700 x_pallet_unload_rate => :4,
701 x_non_pallet_load_rate => :5,
702 x_non_pallet_unload_rate => :6,
703 x_pallet_handling_uom => :7,
704 x_non_pallet_handling_uom => :8,
705 x_return_status => :9,
706 x_msg_count => :10,
707 x_msg_data => :11);
708 END;';
709
710 LogMsg(l_module_name, l_sql_str);
711
712 EXECUTE IMMEDIATE l_sql_str
713 USING IN l_api_version,
714 IN l_api_version,
715 OUT l_pall_loading_rate,
716 OUT l_pall_unloading_rate,
717 OUT l_NPall_loading_rate,
718 OUT l_NPall_unloading_rate,
719 OUT l_pall_handling_uom,
720 OUT l_NPall_handling_uom,
721 OUT x_return_status,
722 OUT l_msg_count,
723 OUT x_error_msg;
724
725 IF (x_error_msg IS NOT NULL AND l_msg_count > 0) THEN
726 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
727 RETURN;
728 END IF;
729
730 IF (l_pall_handling_uom = 'CONTAINER' OR l_pall_handling_uom = 'PALLET') THEN
731 l_Pall_Handling_type := l_pall_handling_uom;
732 ELSE
733 OPEN get_uom_class(l_pall_handling_uom);
734 FETCH get_uom_class INTO l_Pall_Handling_type;
735 CLOSE get_uom_class;
736 END IF;
737
738 IF (l_NPall_handling_uom = 'CONTAINER' OR l_NPall_handling_uom = 'PALLET') THEN
739 l_NPall_Handling_type := l_NPall_handling_uom;
740 ELSE
741 OPEN get_uom_class(l_NPall_handling_uom);
742 FETCH get_uom_class INTO l_NPall_Handling_type;
743 CLOSE get_uom_class;
744 END IF;
745
746 IF l_debug_on THEN
747 WSH_DEBUG_SV.Log(l_module_name, 'l_pall_loading_rate', l_pall_loading_rate);
748 WSH_DEBUG_SV.Log(l_module_name, 'l_pall_unloading_rate', l_pall_unloading_rate);
749 WSH_DEBUG_SV.Log(l_module_name, 'l_pall_handling_uom', l_pall_handling_uom);
750 WSH_DEBUG_SV.Log(l_module_name, 'l_NPall_loading_rate', l_NPall_loading_rate);
751 WSH_DEBUG_SV.Log(l_module_name, 'l_NPall_unloading_rate', l_NPall_unloading_rate);
752 WSH_DEBUG_SV.Log(l_module_name, 'l_NPall_handling_uom', l_NPall_handling_uom);
753 WSH_DEBUG_SV.Log(l_module_name, 'l_Pall_Handling_type', l_Pall_Handling_type);
757 EXCEPTION
754 WSH_DEBUG_SV.Log(l_module_name, 'l_NPall_Handling_type', l_NPall_Handling_type);
755 END IF;
756
758 WHEN OTHERS THEN
759 LogMsg(l_module_name, 'MST INTEGRATION CALL ERROR : ' || SQLERRM);
760 END;
761 END IF;
762
763 --Derive Facility Attributes (Description, Facility Code)
764 g_cur_index := p_location_ids.FIRST;
765 LOOP
766
767 l_loc_id := p_location_ids(g_cur_index);
768 l_facility_description := Get_Facility_Description(l_loc_id);
769
770 IF l_facility_description IS NULL THEN
771 --To prevent null value exceptions during insertion, we generate
772 --descriptions for locations with no company information.
773 --These facilities are deleted later
774 l_facility_description := 'BAD_FACILITY_DELETE_' || l_loc_id;
775 END IF;
776
777 l_facility_descriptions(g_cur_index) := l_facility_description;
778 l_facility_codes(g_cur_index) := l_facility_description;
779
780 -- Derive mileage_flag for a location depending on company type
781 -- from WSH global parameters
782 -- use wsh_location_owners to get the location company type
783 l_include_mileage_flag(g_cur_index) := 'N';
784
785 OPEN get_location_owner(l_loc_id);
786 FETCH get_location_owner INTO l_owner_type;
787 CLOSE get_location_owner;
788
789 -- 1=Organization,2=Customer,3=Carrier,4=Supplier
790 IF l_owner_type = 1 THEN
791 -- Organization
792 l_include_mileage_flag(g_cur_index) := nvl(l_Param_Info.DEF_MILE_CALC_ON_ORG_FAC,'N');
793 ELSIF l_owner_type = 2 THEN
794 -- Customer
795 l_include_mileage_flag(g_cur_index) := nvl(l_Param_Info.DEF_MILE_CALC_ON_CUST_FAC,'N');
796 ELSIF l_owner_type = 3 THEN
797 -- Carrier
798 l_include_mileage_flag(g_cur_index) := nvl(l_Param_Info.DEF_MILE_CALC_ON_CARR_FAC,'N');
799 ELSIF l_owner_type = 4 THEN
800 -- Supplier
801 l_include_mileage_flag(g_cur_index) := nvl(l_Param_Info.DEF_MILE_CALC_ON_SUPP_FAC,'N');
802 END IF;
803
804 logMsg(l_module_name, 'Facility Code', l_facility_codes(g_cur_index));
805 logMsg(l_module_name, 'Facility Description', l_facility_descriptions(g_cur_index));
806
807 EXIT WHEN g_cur_index = p_location_ids.LAST;
808 g_cur_index := p_location_ids.NEXT(g_cur_index);
809
810 END LOOP;
811
812 --Insert into the Fte_Location_Parameters Table
813 logmsg(l_Module_name, 'Inserting Facilities into FTE_LOCATION_PARAMETERS...');
814
815 BEGIN
816 l_start := p_location_ids.FIRST;
817
818 LOOP
819 BEGIN
820 FORALL k IN l_start..p_location_ids.LAST
821 INSERT INTO fte_location_parameters (
822 FACILITY_ID,
823 FACILITY_CODE,
824 LOCATION_ID,
825 DESCRIPTION,
826 CONSOLIDATION_ALLOWED,
827 DECONSOLIDATION_ALLOWED,
828 CROSSDOCKING_ALLOWED,
829 PARCEL_LTL_CONSOLIDATION,
830 PARCEL_TL_CONSOLIDATION,
831 LTL_TL_CONSOLIDATION,
832 LTL_LTL_CONSOLIDATION,
833 LTL_PARCEL_DECONSOLIDATION,
834 TL_PARCEL_DECONSOLIDATION,
835 TL_LTL_DECONSOLIDATION,
836 LTL_LTL_DECONSOLIDATION,
837 PARCEL_INBOUND_CROSSDOCKING,
838 LTL_INBOUND_CROSSDOCKING,
839 TL_INBOUND_CROSSDOCKING,
840 PARCEL_OUTBOUND_CROSSDOCKING,
841 LTL_OUTBOUND_CROSSDOCKING,
842 TL_OUTBOUND_CROSSDOCKING,
843 STORAGE_FACILITY,
844 CARRIER_OWNED_HAUL,
845 FLOW_THROUGH_TIME,
846 FLOW_THROUGH_TIME_UOM,
847 NON_ADJACENT_LOADING,
848 NON_ADJACENT_UNLOADING,
849 MODIFIER_LIST,
850 HANDLE_STACKED_PALLETS,
851 NONPALLETIZED_LOADING_RATE,
852 NONPALLETIZED_UNLOADING_RATE,
853 NONPALLETIZED_HANDLING_UOM,
854 NONPALLETIZED_HANDLING_TYPE,
855 PALLETIZED_LOADING_RATE,
856 PALLETIZED_UNLOADING_RATE,
857 PALLETIZED_HANDLING_UOM,
858 PALLETIZED_HANDLING_TYPE,
859 LOAD_UNLOAD_TIME_UOM,
860 PRIVATE_RESIDENCE,
861 CREATION_DATE,
862 CREATED_BY,
863 LAST_UPDATE_DATE,
864 LAST_UPDATED_BY,
865 EFFECTIVE_DATE_FROM,
866 LOAD_UNLOAD_PROTOCOL,
867 INCLUDE_MILEAGE_FLAG)
868 VALUES (
869 fte_location_parameters_s.nextval,-- FACILITY_ID
870 l_facility_codes(k), -- FACILITY_CODE
871 p_location_ids(k), -- LOCATION_ID
872 l_facility_descriptions(k), -- DESCRIPTION
873 'N', -- CONSOLIDATION_ALLOWED
874 'N', -- DECONSOLIDATION_ALLOWED
875 'N', -- CROSSDOCKING_ALLOWED
876 'N', -- PARCEL_LTL_CONSOLIDATION
877 'N', -- PARCEL_TL_CONSOLIDATION
878 'N', -- LTL_TL_CONSOLIDATION
879 'N', -- LTL_LTL_CONSOLIDATION
880 'N', -- LTL_PARCEL_DECONSOLIDATION
881 'N', -- TL_PARCEL_DECONSOLIDATION
882 'N', -- TL_LTL_DECONSOLIDATION
883 'N', -- LTL_LTL_DECONSOLIDATION
884 'N', -- PARCEL_INBOUND_CROSSDOCKING
885 'N', -- LTL_INBOUND_CROSSDOCKING
886 'N', -- TL_INBOUND_CROSSDOCKING
887 'N', -- PARCEL_OUTBOUND_CROSSDOCKING
888 'N', -- LTL_OUTBOUND_CROSSDOCKING
889 'N', -- TL_OUTBOUND_CROSSDOCKING
890 'N', -- STORAGE_FACILITY
894 'N', -- NON_ADJACENT_LOADING
891 'NEITHER', -- CARRIER_OWNED_HAUL
892 l_flow_thru_time, -- FLOW_THROUGH_TIME
893 l_flow_thru_time_uom, -- FLOW_THROUGH_TIME_UOM
895 'N', -- NON_ADJACENT_UNLOADING
896 NULL, -- MODIFIER_LIST
897 'Y', -- HANDLE_STACKED_PALLETS
898 l_NPall_Loading_Rate, -- NONPALLETIZED_LOADING_RATE
899 l_NPall_Unloading_Rate, -- NONPALLETIZED_UNLOADING_RATE
900 l_NPall_Handling_Uom, -- NONPALLETIZED_HANDLING_UOM
901 l_NPall_Handling_type, -- NONPALLETIZED_HANDLING_TYPE
902 l_Pall_Loading_Rate, -- PALLETIZED_LOADING_RATE
903 l_Pall_Unloading_Rate, -- PALLETIZED_UNLOADING_RATE
904 l_Pall_Handling_Uom, -- PALLETIZED_HANDLING_UOM
905 l_Pall_Handling_type, -- PALLETIZED_HANDLING_TYPE
906 l_loadUnload_Time_Uom, -- LOAD_UNLOAD_TIME_UOM
907 l_private_residence, -- PRIVATE_RESIDENCE
908 sysdate, -- CREATION_DATE
909 l_userId, -- CREATED_BY
910 sysdate, -- LAST_UPDATE_DATE
911 l_userId, -- LAST_UPDATED_BY
912 sysdate, -- EFFECTIVE_DATE_FROM
913 l_loadUnload_Protocol, -- LOAD_UNLOAD_PROTOCOL
914 l_include_mileage_flag(k)); --INCLUDE_MILEAGE_FLAG
915
916 EXIT; --exit the infinite loop
917 EXCEPTION
918 WHEN OTHERS THEN
919 l_error_code := SQLCODE;
920 --ORA:00001 is the unique constraint violation. We are attempting
921 --to insert a facility that already exists.
922 IF (l_error_code = -1) THEN
923 g_dup_locations.EXTEND;
924 g_dup_locations(g_dup_locations.COUNT) := p_location_ids(l_start + sql%rowcount);
925 l_start := l_start + sql%rowcount + 1;
926 ELSE
927 x_error_msg := 'UNEXP. ERROR WHILE CREATING FACILITY FOR LOCATION ' || p_location_ids(l_start + sql%rowcount);
928 x_error_msg := x_error_msg || ' => ' || SQLERRM;
929 LogMsg(l_module_name, x_error_msg);
930 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
931 Exit_Debug(l_module_name);
932 RETURN;
933 END IF;
934 END;
935 END LOOP;
936 END;
937
938 logMsg(l_module_name, 'Finished Inserting Facilities ');
939
940 k := p_location_ids.COUNT - g_dup_locations.COUNT;
941
942 -- Delete Facilities which are for dummy locations
943 BEGIN
944 DELETE FROM FTE_LOCATION_PARAMETERS
945 WHERE location_id IN (
946 SELECT wl.wsh_location_id WSH_LOCATION_ID
947 FROM hz_party_sites hps,
948 hz_cust_acct_sites_all hcas,
949 hz_cust_site_uses_all hcsu, po_location_associations_all pla,
950 wsh_locations wl
951 WHERE pla.customer_id = hcas.cust_account_id
952 AND pla.site_use_id = hcsu.site_use_id
953 AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
954 AND hcas.party_site_id = hps.party_site_id
955 AND hps.location_id = wl.source_location_id
956 AND wl.location_source_code = 'HZ'
957 );
958 EXCEPTION
959 WHEN NO_DATA_FOUND THEN
960 NULL;
961 WHEN OTHERS THEN
962 x_error_msg := 'UNEXPECTED ERROR WHILE DELETING CUSTOMER DUMMY FACILITIES ' || sqlerrm;
963 LogMsg(l_module_name, x_error_msg);
964 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
965 END;
966
967 m := SQL%ROWCOUNT;
968 k := k - m;
969
970 --Now delete the bad facilities (Those that had no valid companies)
971 BEGIN
972 DELETE FROM FTE_LOCATION_PARAMETERS
973 WHERE Description LIKE 'BAD_FACILITY_DELETE%';
974 EXCEPTION
975 WHEN NO_DATA_FOUND THEN
976 NULL;
977 WHEN OTHERS THEN
978 x_error_msg := 'UNEXPECTED ERROR WHILE DELETING BAD FACILITIES ' || sqlerrm;
979 LogMsg(l_module_name, x_error_msg);
980 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
981 END;
982
983 l := SQL%ROWCOUNT;
984 k := k - l;
985
986 -- ************************** ERROR REPORTING ************************************
987 x_error_msg := 'Successfully inserted '||k||' facilities ';
988 x_error_msg := x_error_msg || 'into FTE_LOCATION_PARAMETERS';
989 logmsg(l_module_name, x_error_msg);
990 Fnd_File.Put_Line(Fnd_File.output, x_error_msg);
991
992 IF (l > 0) THEN
993 x_error_msg := 'Could not create ' || l || ' facilities because they are invalid as facility locations.';
994 LogMsg(l_module_name, x_error_msg );
995 END IF;
996
997 IF (m > 0) THEN
998 x_error_msg := 'Could not create ' || m || ' facilities because they are dummy customer locations.';
999 LogMsg(l_module_name, x_error_msg );
1000 END IF;
1001
1002 --Report the location IDs that already exist and set return status to warning
1003 IF (g_dup_locations.COUNT > 0 ) THEN
1004 LogMsg(l_module_name, '***************************************************************');
1005 Fnd_File.Put_Line(Fnd_File.output, '***************************************************************');
1006
1007 LogMsg(l_module_name, '* THE FACILITIES WITH THE FOLLOWING LOCATION IDS ALREADY EXIST');
1008 Fnd_File.Put_Line(Fnd_File.output, '* THE FACILITIES WITH THE FOLLOWING LOCATION IDS ALREADY EXIST');
1009
1010 FOR i IN g_dup_locations.FIRST..g_dup_locations.LAST LOOP
1011 LogMsg(l_module_name, '* ' || i || '. Location ID ' || g_dup_locations(i));
1012 Fnd_File.Put_Line(Fnd_File.output, '* ' || i || '. Location ID ' || g_dup_locations(i));
1013 END LOOP;
1017 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
1014 LogMsg(l_module_name, '***************************************************************');
1015 Fnd_File.Put_Line(Fnd_File.output, '***************************************************************');
1016
1018 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1019 x_error_msg := 'Some facilities already exist. Please check log file for details';
1020 Fnd_File.Put_Line(Fnd_File.output,
1021 'WARNING: Some facilities already exist. Please check log file for details');
1022 END IF;
1023 END IF;
1024
1025 -- ************************** ERROR REPORTING ************************************
1026
1027 Reset_All;
1028 IF l_debug_on THEN
1029 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1030 WSH_DEBUG_SV.pop(x_Module => l_module_name,
1031 x_Context => 'EXCEPTION:OTHERS');
1032 ELSE
1033 Exit_Debug(l_module_name);
1034 END IF;
1035 END IF;
1036 EXCEPTION
1037 WHEN OTHERS THEN
1038 x_error_msg := 'UNEXPECTED ERROR ' || sqlerrm;
1039 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1040 LogMsg(l_module_name, x_error_msg);
1041
1042 IF l_debug_on THEN
1043 WSH_DEBUG_SV.pop(x_Module => l_module_name,
1044 x_Context => 'EXCEPTION:OTHERS');
1045 END IF;
1046 END Create_Facilities;
1047
1048 -----------------------------------------------------------------------------------
1049 -- Start of comments
1050 -- API name : Create_Facility
1051 -- Type : Private
1052 -- Pre-reqs : None.
1053 -- Function : Create a new facility (row in FTE_LOCATION_PARAMETERS).
1054 -- Note: To create more than one facility, it might be more
1055 -- efficient to call Create_Facilities since this procedure
1056 -- does not cache common queried information from previous calls.
1057 -- Parameters :
1058 -- IN: p_location_id IN NUMBER Required
1059 -- The location id for the facility.
1060 -- p_facility_code IN VARCHAR2 Optional
1061 -- The facility code for this facility. If null, it will be automatically
1062 -- generated from the company name and site name.
1063 --
1064 -- OUT: x_return_status OUT NOCOPY VARCHAR2
1065 -- The return status is one of the following:
1066 -- i. WSH_UTIL_CORE.G_RET_STS_SUCCESS: Facility created successfully
1067 -- iv. WSH_UTIL_CORE.G_RET_STS_WARNING: If facility already exists.
1068 -- iv. WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1069 -- x_error_msg OUT NOCOPY VARCHAR2
1070 -- The error message
1071 -- Version : 1.0
1072 -- Previous version 1.0
1073 -- Initial version 1.0
1074 -- End of comments
1075 -----------------------------------------------------------------------------------
1076 PROCEDURE Create_Facility (p_location_id IN NUMBER,
1077 p_facility_code IN VARCHAR2 default NULL,
1078 x_return_status OUT NOCOPY VARCHAR2,
1079 x_error_msg OUT NOCOPY VARCHAR2) IS
1080
1081 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.CREATE_FACILITY';
1082 l_debug_on BOOLEAN;
1083 l_location_id_tab WSH_LOCATIONS_PKG.ID_Tbl_Type;
1084 l_site_names WSH_LOCATIONS_PKG.LocationCode_Tbl_Type;
1085 l_company_names WSH_LOCATIONS_PKG.Address_Tbl_Type;
1086
1087 BEGIN
1088 Init_Debug(l_module_name);
1089
1090 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1091 g_cache := FALSE;
1092
1093 --Create an ID table with a single entry and call Create_Facilities
1094 l_location_id_tab(1) := p_location_id;
1095
1096 logmsg(l_module_name, 'Calling Create_Facilities');
1097 logmsg(l_module_name, 'p_location_id', l_location_id_tab(1));
1098
1099 Create_Facilities( p_location_ids => l_location_id_tab,
1100 p_company_names => l_company_names,
1101 p_site_names => l_site_names,
1102 x_return_status => x_return_status,
1103 x_error_msg => x_error_msg);
1104
1105 --Update with the facility code, if it was supplied.
1106 IF (p_facility_code IS NOT NULL AND
1107 x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1108 BEGIN
1109 UPDATE fte_location_parameters
1110 SET facility_code = p_facility_code
1111 WHERE location_id = p_location_id;
1112 EXCEPTION
1113 WHEN OTHERS THEN
1114 x_error_msg := ('UNEXPECTED ERROR AFTER CREATING FACILITY: ' || sqlerrm);
1115 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1116 LogMsg(l_module_name, x_error_msg);
1117 END;
1118 END IF;
1119
1120 IF l_debug_on THEN
1121 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1122 WSH_DEBUG_SV.pop(x_Module => l_module_name,
1123 x_Context => 'EXCEPTION:OTHERS');
1124 ELSE
1125 Exit_Debug(l_module_name);
1126 END IF;
1127 END IF;
1128
1129 EXCEPTION
1130 WHEN OTHERS THEN
1131 LogMsg(l_module_name, 'UNEXPECTED ERROR: ' || sqlerrm);
1132
1133 IF l_debug_on THEN
1134 WSH_DEBUG_SV.pop(x_Module => l_module_name,
1135 x_Context => 'EXCEPTION:OTHERS');
1136 END IF;
1137 END Create_Facility;
1138
1139 -----------------------------------------------------------------------------------
1140 -- Start of comments
1141 -- API name : Get_Fac_Info
1142 -- Type : Public
1143 -- Pre-reqs : None.
1144 -- Function : Return Information about a Facility
1145 --
1146 -- Parameters :
1147 -- IN:
1151 -- populated. X_fac_info_rows is returned with information about the
1148 --
1149 -- IN OUT: x_fac_info_rows Tl_Fac_Info_Tab_Type
1150 -- The location_id attribute of each record in x_fac_info_rows must be
1152 -- facility.
1153 --
1154 -- OUT: x_return_status VARCHAR2
1155 --
1156 -- Version : 1.0
1157 -- Previous version 1.0
1158 -- Initial version 1.0
1159 -- End of comments
1160 -----------------------------------------------------------------------------------
1161 PROCEDURE Get_Fac_Info (x_fac_info_rows IN OUT NOCOPY Tl_Fac_Info_Tab_Type,
1162 x_return_status OUT NOCOPY VARCHAR2) IS
1163
1164 l_location_id NUMBER;
1165
1166 fac_currency VARCHAR2(30);
1167 fac_pricelist_id NUMBER;
1168
1169 x_error_msg VARCHAR2(4000);
1170 l_charge_basis VARCHAR2(50);
1171 l_charge_basis_uom VARCHAR2(30);
1172 l_error_code NUMBER;
1173 k NUMBER;
1174
1175 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'Get_Fac_Info';
1176
1177 BEGIN
1178 Init_Debug(l_module_name);
1179 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1180
1181 k := x_fac_info_rows.FIRST;
1182 LOOP
1183 l_location_id := x_fac_info_rows(k).location_id;
1184
1185 BEGIN
1186 SELECT fl.facility_code facility_code,
1187 fl.load_unload_protocol load_unload_protocol,
1188 fl.modifier_list modifier_id,
1189 prc_rc_id.value_from pricelist_id,
1190 prc_cbasis.value_from charge_basis,
1191 prc_cbasis_uom.value_from charge_basis_uom,
1192 prc_currency.value_from currency_code
1193 INTO x_fac_info_rows(k).fac_code,
1194 x_fac_info_rows(k).loading_protocol,
1195 x_fac_info_rows(k).fac_modifier_id,
1196 x_fac_info_rows(k).fac_pricelist_id,
1197 l_charge_basis,
1198 l_charge_basis_uom,
1199 x_fac_info_rows(k).fac_currency
1200 FROM fte_location_parameters fl,
1201 fte_prc_parameters prc_cbasis,
1202 fte_prc_parameters prc_cbasis_uom,
1203 fte_prc_parameters prc_currency,
1204 fte_prc_parameters prc_rc_id
1205 WHERE fl.location_id = x_fac_info_rows(k).location_id
1206 AND prc_cbasis.list_header_id(+) = fl.modifier_list
1207 AND prc_cbasis_uom.list_header_id(+)= fl.modifier_list
1208 AND prc_currency.list_header_id(+) = fl.modifier_list
1209 AND prc_rc_id.list_header_id(+) = fl.modifier_list
1210 AND prc_cbasis.parameter_id(+) = 57
1211 AND prc_cbasis_uom.parameter_id(+) = 58
1212 AND prc_rc_id.parameter_id (+)= 59
1213 AND prc_currency.parameter_id(+) = 60;
1214
1215 --Set the charge basis uom accordingly
1216 IF (upper(l_charge_basis) = 'VOLUME') THEN
1217 x_fac_info_rows(k).fac_volume_uom := l_charge_basis_uom;
1218 ELSIF (upper(l_charge_basis) = 'WEIGHT') THEN
1219 x_fac_info_rows(k).fac_weight_uom := l_charge_basis_uom;
1220 END IF;
1221
1222 x_fac_info_rows(k).fac_charge_basis := l_charge_basis;
1223
1224 EXCEPTION
1225 WHEN NO_DATA_FOUND THEN
1226 x_error_msg := 'Location ID ' || l_location_id || ' does not exist';
1227 LogMsg(l_module_name, x_error_msg);
1228 WHEN OTHERS THEN
1229 x_error_msg := 'UNEXPECTED ERROR WHILE GETTING FACILITY INFO ';
1230 x_error_msg := x_error_msg || 'for location ID ' || l_location_id;
1231 LogMsg(l_module_name, x_error_msg);
1232 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1233 Exit_Debug(l_module_name);
1234 RETURN;
1235 END;
1236
1237 EXIT WHEN k = x_fac_info_rows.LAST;
1238 k := x_fac_info_rows.NEXT(k);
1239
1240 END LOOP;
1241 Exit_Debug(l_module_name);
1242 EXCEPTION
1243 WHEN OTHERS THEN
1244 x_error_msg := 'UNEXPECTED ERROR: ' || sqlerrm;
1245 Exit_Debug(l_module_name);
1246 END Get_Fac_Info;
1247
1248 -----------------------------------------------------------------------------------
1249 -- Start of comments
1250 -- API name : Get_Fac_Lat_Long_and_TimeZone
1251 -- Type : Public
1252 -- Pre-reqs : None.
1253 -- Function : Return Information about Latitude, Longitude,Timezone,Geometry of a Facility
1254 -- given the address attributes
1255 -- Parameters :
1256 -- IN: p_country VARCHAR2
1257 -- p_city VARCHAR2
1258 -- p_postalcode VARCHAR2
1259 -- p_state VARCHAR2
1260 -- p_county VARCHAR2
1261 -- p_province VARCHAR2
1262 --
1263 --
1264 -- OUT: x_return_status VARCHAR2
1265 -- x_msg_count VARCHAR2
1266 -- x_msg_data VARCHAR2
1267 -- x_latitude NUMBER
1268 -- x_longitude NUMBER
1269 -- x_timezone VARCHAR2
1270 -- x_geometry MDSYS.SDO_GEOMETRY
1271 --
1272 -- Version : 1.0
1273 -- Previous version 1.0
1274 -- Initial version 1.0
1275 -- End of comments
1276 -----------------------------------------------------------------------------------
1277
1278 PROCEDURE Get_Fac_Lat_Long_and_TimeZone(p_country IN VARCHAR2,
1279 p_city IN VARCHAR2,
1280 p_postalcode IN VARCHAR2,
1281 p_state IN VARCHAR2,
1282 p_county IN VARCHAR2,
1283 p_province IN VARCHAR2,
1284 x_return_status OUT NOCOPY VARCHAR2,
1285 x_msg_count OUT NOCOPY VARCHAR2,
1286 x_msg_data OUT NOCOPY VARCHAR2,
1287 x_latitude OUT NOCOPY VARCHAR2,
1288 x_longitude OUT NOCOPY VARCHAR2,
1289 x_timezone OUT NOCOPY VARCHAR2,
1290 x_geometry OUT NOCOPY MDSYS.SDO_GEOMETRY
1291 ) is
1292
1296 BEGIN
1293 l_location WSH_LOCATIONS_PKG.LOCATION_REC_TYPE;
1294 x_error_msg VARCHAR2(100);
1295 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'Get_Fac_Lat_Long_and_TimeZone';
1297 l_location.COUNTRY := p_country;
1298 l_location.STATE := p_state;
1299 l_location.CITY := p_city;
1300 l_location.POSTAL_CODE := p_postalcode;
1301 l_location.COUNTY := p_county;
1302 l_location.PROVINCE := p_province;
1303
1304 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1305 x_msg_data := NULL;
1306 WSH_GEOCODING.Get_Lat_Long_and_TimeZone(p_api_version => 1.0,
1307 p_init_msg_list => NULL,
1308 x_return_status => x_return_status,
1309 x_msg_count => x_msg_count,
1310 x_msg_data => x_msg_data,
1311 l_location => l_location);
1312
1313 x_latitude := to_char(l_location.LATITUDE);
1314 x_longitude := to_char(l_location.LONGITUDE);
1315 x_timezone := l_location.TIMEZONE_CODE;
1316 x_geometry := l_location.GEOMETRY;
1317
1318 IF (x_return_status IS NULL) THEN
1319 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1320 END IF;
1321
1322 EXCEPTION
1323 WHEN OTHERS THEN
1324 x_error_msg := 'UNEXPECTED ERROR WHILE GETTING LATITUDE, LONGITUDE,TIMEZONE, GEOMETRY INFO ';
1325 LogMsg(l_module_name, x_error_msg);
1326 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1327 Exit_Debug(l_module_name);
1328 RETURN;
1329
1330 END Get_Fac_Lat_Long_and_TimeZone;
1331
1332 END FTE_LOCATION_PARAMETERS_PKG;
1333