[Home] [Help]
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: -- --
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: --
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();
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:
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.
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
366: wc.carrier_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
367: hps.creation_date cdate,
368: 'CARRIER' company_type
369: FROM wsh_locations wl, hz_party_sites hps, wsh_carriers_v wc,
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
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
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'
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
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: --
565: -- (a). If both
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;
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:
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,
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
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,
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;
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
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 :
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
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
1328: RETURN;
1329:
1330: END Get_Fac_Lat_Long_and_TimeZone;
1331:
1332: END FTE_LOCATION_PARAMETERS_PKG;
1333: