1 PACKAGE WSH_LOCATIONS_PKG AUTHID CURRENT_USER as
2 /* $Header: WSHLOCMS.pls 120.1.12000000.1 2007/01/16 05:47:58 appldev ship $ */
3
4 TYPE location_rec_type IS RECORD (
5 WSH_LOCATION_ID WSH_LOCATIONS.WSH_LOCATION_ID%TYPE,
6 SOURCE_LOCATION_ID WSH_LOCATIONS.SOURCE_LOCATION_ID%TYPE,
7 LOCATION_SOURCE_CODE WSH_LOCATIONS.LOCATION_SOURCE_CODE%TYPE,
8 LOCATION_CODE WSH_LOCATIONS.LOCATION_CODE%TYPE,
9 UI_LOCATION_CODE WSH_LOCATIONS.UI_LOCATION_CODE%TYPE,
10 ADDRESS1 WSH_LOCATIONS.ADDRESS1%TYPE,
11 ADDRESS2 WSH_LOCATIONS.ADDRESS2%TYPE,
12 ADDRESS3 WSH_LOCATIONS.ADDRESS3%TYPE,
13 ADDRESS4 WSH_LOCATIONS.ADDRESS4%TYPE,
14 COUNTRY WSH_LOCATIONS.COUNTRY%TYPE,
15 STATE WSH_LOCATIONS.STATE%TYPE,
16 PROVINCE WSH_LOCATIONS.PROVINCE%TYPE,
17 COUNTY WSH_LOCATIONS.COUNTY%TYPE,
18 CITY WSH_LOCATIONS.CITY%TYPE,
19 POSTAL_CODE WSH_LOCATIONS.POSTAL_CODE%TYPE,
20 INACTIVE_DATE WSH_LOCATIONS.INACTIVE_DATE%TYPE,
21 LONGITUDE WSH_LOCATIONS.LONGITUDE%TYPE,
22 LATITUDE WSH_LOCATIONS.LATITUDE%TYPE,
23 GEOMETRY WSH_LOCATIONS.GEOMETRY%TYPE,
24 TIMEZONE_CODE WSH_LOCATIONS.TIMEZONE_CODE%TYPE
25 );
26
27 --
28 -- Package: WSH_LOCATIONS_PKG
29 --
30 -- Purpose: To populate data in WSH_LOCATIONS with the data in
31 -- HZ_LOCATIONS, HR_LOCATIONS
32 --
33 --
34 /*===========================================================================+
35 | PROCEDURE |
36 | Process_Locations |
37 | |
38 | DESCRIPTION |
39 | |
40 | This procedure will populate the WSH_LOCATIONS table |
41 | with the locations in HZ_LOCATIONS (whose usage is deliver_to |
42 | or ship_to) and HR_LOCATIONS |
43 | |
44 +===========================================================================*/
45
46 --
47 -- Parameters
48 --
49 -- p_location_type Location Type (EXTERNAL/INTERNAL/BOTH)
50 -- p_from_location From Location ID
51 -- p_to_location To Location ID
52 -- p_start_date Start Date
53 -- p_end_date End Date
54 -- If the Start Date and End Date are not null then the locations which are updated
55 -- in this date range will be considered.
56
57
58
59
60 PROCEDURE Process_Locations
61 (
62 p_location_type IN VARCHAR2,
63 p_from_location IN NUMBER ,
64 p_to_location IN NUMBER ,
65 p_start_date IN VARCHAR2,
66 p_end_date IN VARCHAR2,
67 p_create_facilities IN VARCHAR2 default NULL,
68 p_caller IN VARCHAR2 default NULL,
69 x_return_status OUT NOCOPY VARCHAR2,
70 x_sqlcode OUT NOCOPY NUMBER,
71 x_sqlerr OUT NOCOPY varchar2
72 );
73
74 TYPE ID_Tbl_Type IS TABLE OF NUMBER index by BINARY_INTEGER;
75 TYPE Address_Tbl_Type IS TABLE OF VARCHAR2(2000) index by BINARY_INTEGER;
76 TYPE LocationCode_Tbl_Type IS TABLE OF VARCHAR2(1000) index by BINARY_INTEGER;
77 TYPE Date_Tbl_Type IS TABLE OF DATE index by BINARY_INTEGER;
78 --TYPE Geometry_Tbl_Type IS TABLE OF MDSYS.SDO_GEOMETRY index by BINARY_INTEGER;
79
80 PROCEDURE get_site_number(pLocationIdTbl IN ID_Tbl_Type,
81 pLocationCodeTbl IN OUT NOCOPY LocationCode_Tbl_Type,
82 pUILocationCodeTbl IN OUT NOCOPY LocationCode_Tbl_Type);
83
84 PROCEDURE insert_locations(pInsertLocationIdTbl IN ID_Tbl_Type,
85 p_location_source_code IN VARCHAR2,
86 x_return_status OUT NOCOPY VARCHAR2);
87
88 PROCEDURE update_locations(pUpdateLocationIdTbl IN ID_Tbl_Type,
89 p_location_source_code IN VARCHAR2,
90 x_return_status OUT NOCOPY VARCHAR2);
91
92 PROCEDURE insert_location_owners(pLocationIdTbl IN ID_Tbl_Type,
93 p_location_source_code IN VARCHAR2,
94 x_return_status OUT NOCOPY VARCHAR2);
95
96 PROCEDURE Create_Geometry (p_longitude IN NUMBER,
97 p_latitude IN NUMBER,
98 x_geometry OUT NOCOPY MDSYS.SDO_GEOMETRY,
99 x_return_status OUT NOCOPY VARCHAR2,
100 x_error_msg OUT NOCOPY VARCHAR2 );
101
102
103 PROCEDURE Convert_internal_cust_location(
104 p_internal_cust_location_id IN NUMBER,
105 p_customer_id IN NUMBER DEFAULT NULL,
106 x_internal_org_location_id OUT NOCOPY NUMBER,
107 x_return_status OUT NOCOPY VARCHAR2);
108
109 FUNCTION Convert_internal_cust_location(
110 p_internal_cust_location_id IN NUMBER)
111 RETURN NUMBER;
112
113 pUpdateAddress1Tbl Address_Tbl_Type;
114 pUpdateAddress2Tbl Address_Tbl_Type;
115 pUpdateAddress3Tbl Address_Tbl_Type;
116 pUpdateAddress4Tbl Address_Tbl_Type;
117 pUpdateCountryTbl Address_Tbl_Type;
118 pUpdateStateTbl Address_Tbl_Type;
119 pUpdateProvinceTbl Address_Tbl_Type;
120 pUpdateCountyTbl Address_Tbl_Type;
121 pUpdateCityTbl Address_Tbl_Type;
122 pUpdatePostalCodeTbl Address_Tbl_Type;
123 pUpdateExpDateTbl Date_Tbl_Type;
124 pUpdateLocCodeTbl LocationCode_Tbl_Type;
125 pUpdateUILocCodeTbl LocationCode_Tbl_Type;
126 pUpdateOwnerNameTbl Address_Tbl_Type;
127
128 pInsertAddress1Tbl Address_Tbl_Type;
129 pInsertAddress2Tbl Address_Tbl_Type;
130 pInsertAddress3Tbl Address_Tbl_Type;
131 pInsertAddress4Tbl Address_Tbl_Type;
132 pInsertCountryTbl Address_Tbl_Type;
133 pInsertStateTbl Address_Tbl_Type;
134 pInsertProvinceTbl Address_Tbl_Type;
135 pInsertCountyTbl Address_Tbl_Type;
136 pInsertCityTbl Address_Tbl_Type;
137 pInsertPostalCodeTbl Address_Tbl_Type;
138 pInsertExpDateTbl Date_Tbl_Type;
139 pInsertLocCodeTbl LocationCode_Tbl_Type;
140 pInsertUILocCodeTbl LocationCode_Tbl_Type;
141 pInsertOwnerNameTbl Address_Tbl_Type;
142
143 pLocLocationIdTbl ID_Tbl_Type;
144 pLocOwnerIdTbl ID_Tbl_Type;
145 pLocOwnerTypeTbl ID_Tbl_Type;
146
147 pLatitudeTbl Id_Tbl_Type;
148 pLongitudeTbl Id_Tbl_Type;
149 pTimezoneTbl LocationCode_Tbl_Type;
150 --pGeometryTbl Geometry_Tbl_Type;
151 pLastUpdateDateTbl Date_Tbl_Type;
152
153 END WSH_LOCATIONS_PKG;
[Home] [Help]