[Home] [Help]
PACKAGE BODY: APPS.WMS_OTM_DOCK_SYNC
Source
1 PACKAGE BODY WMS_OTM_DOCK_SYNC AS
2 /* $Header: WMSOTDDB.pls 120.0 2007/12/22 04:13:09 dramamoo noship $ */
3
4 --Global variable to hold the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WMS_OTM_DOCK_SYNC';
6
7 --Global variable used in print_debug utility
8 G_VERSION_PRINTED BOOLEAN := FALSE;
9
10 G_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
11
12 PROCEDURE print_debug
13 (
14 p_err_msg VARCHAR2
15 , p_level NUMBER
16 ) IS
17 BEGIN
18 IF (g_debug = 1) THEN
19 IF(G_VERSION_PRINTED = FALSE ) THEN
20 inv_log_util.trace (
21 p_message => '$Header: WMSOTDDB.pls 120.0 2007/12/22 04:13:09 dramamoo noship $'
22 , p_module => G_PKG_NAME
23 , p_level => 9);
24 G_VERSION_PRINTED :=TRUE;
25 END IF;
26 inv_log_util.trace (
27 p_message => p_err_msg
28 , p_module => G_PKG_NAME
29 , p_level => p_level);
30 END IF;
31 END print_debug;
32
33
34 PROCEDURE Send_Dock_Doors (
35 p_entity_in_rec IN WSH_OTM_ENTITY_REC_TYPE,
36 x_username OUT NOCOPY VARCHAR2,
37 x_password OUT NOCOPY VARCHAR2,
38 x_org_dock_tbl OUT NOCOPY WMS_ORG_DOCK_TBL_TYPE,
39 x_return_status OUT NOCOPY VARCHAR2,
40 x_msg_data OUT NOCOPY VARCHAR2 )
41 IS
42 -- Cursor to get the Location XID for the Organization
43 CURSOR l_org_loc_csr(l_organization_id NUMBER) IS
44 SELECT 'ORG-' || ORGANIZATION_ID ||'-'|| LOCATION_ID LOCATION_XID
45 FROM WSH_SHIP_FROM_ORGS_V
46 WHERE ORGANIZATION_ID = l_organization_id;
47
48 -- Cursor to get the Location XID for the Organization
49 -- NVL function added for Description since OTM always expects a Dock Name
50 CURSOR l_dock_info_csr(l_organization_id NUMBER) IS
51 SELECT 'DOCK-' || inventory_location_id LOCATION_RES_XID,
52 NVL(description, 'DOCK-' || inventory_location_id||' Name') LOCATION_RES_NAME
53 FROM mtl_item_locations
54 WHERE organization_id = l_organization_id
55 AND inventory_location_type = 1;
56
57 l_domain_name VARCHAR2(100);
58 l_location_xid VARCHAR2(50);
59 i NUMBER;
60 j NUMBER;
61 l_msg_count NUMBER;
62 BEGIN
63 IF g_debug = 1 THEN
64 print_debug('In Send_Dock_Doors API ', 4);
65 END IF;
66 -- Initialize the return status to success
67 x_return_status := FND_API.G_RET_STS_SUCCESS;
68
69 IF p_entity_in_rec.entity_id_tbl.count > 0 AND p_entity_in_rec.ENTITY_TYPE = 'ORG_LOC' THEN --{
70
71 FND_PROFILE.Get('WSH_OTM_DOMAIN_NAME', l_domain_name);
72 FND_PROFILE.Get('WSH_OTM_USER_ID', x_username);
73 FND_PROFILE.Get('WSH_OTM_PASSWORD', x_password);
74
75 IF g_debug = 1 THEN
76 print_debug('OTM Domain : ' || l_domain_name || ' , OTM User : ' || x_username
77 || ' , OTM Password : ' || x_password
78 || ' , Number of Organizations : '||p_entity_in_rec.entity_id_tbl.count, 4);
79 END IF;
80
81 x_org_dock_tbl := WMS_ORG_DOCK_TBL_TYPE();
82
83 FOR i in p_entity_in_rec.entity_id_tbl.first..p_entity_in_rec.entity_id_tbl.last LOOP --{
84
85 x_org_dock_tbl.extend;
86 x_org_dock_tbl(i) := WMS_ORG_DOCK_REC_TYPE
87 ( NULL,
88 NULL,
89 NULL,
90 NULL,
91 NULL,
92 NULL,
93 WMS_DOCK_DOOR_TBL_TYPE()
94 );
95
96 OPEN l_org_loc_csr(p_entity_in_rec.entity_id_tbl(i));
97 FETCH l_org_loc_csr INTO l_location_xid;
98 CLOSE l_org_loc_csr;
99
100 x_org_dock_tbl(i).LOCATION_XID := l_location_xid;
101 x_org_dock_tbl(i).LOCATION_DN := l_domain_name;
102 x_org_dock_tbl(i).RESOURCE_TYPE := 'DOCK DOOR';
103 x_org_dock_tbl(i).CALENDAR_DN := NULL;
104 x_org_dock_tbl(i).CALENDAR_XID := NULL;
105 x_org_dock_tbl(i).CONSTRAINT_APPT := NULL;
106
107 IF g_debug = 1 THEN
108 print_debug('Organization XID : '||l_location_xid, 4);
109 END IF;
110
111 j := 1;
112 FOR l_dock_info IN l_dock_info_csr(p_entity_in_rec.entity_id_tbl(i)) LOOP --{
113 x_org_dock_tbl(i).DOCK_DOOR_TBL.extend;
114 x_org_dock_tbl(i).DOCK_DOOR_TBL(j) := WMS_DOCK_DOOR_REC_TYPE(NULL, NULL, NULL);
115 x_org_dock_tbl(i).DOCK_DOOR_TBL(j).LOCATION_RES_XID := l_dock_info.LOCATION_RES_XID;
116 x_org_dock_tbl(i).DOCK_DOOR_TBL(j).LOCATION_RES_NAME := l_dock_info.LOCATION_RES_NAME;
117 x_org_dock_tbl(i).DOCK_DOOR_TBL(j).LOCATION_RES_DN := l_domain_name;
118 IF g_debug = 1 THEN
119 print_debug('Location Res XID : '||l_dock_info.LOCATION_RES_XID, 4);
120 print_debug('Location Res Name : '||l_dock_info.LOCATION_RES_NAME, 4);
121 END IF;
122 j := j + 1;
123 END LOOP; --}
124 IF g_debug = 1 THEN
125 print_debug('Number of Dock Doors sent : '||x_org_dock_tbl(i).DOCK_DOOR_TBL.count, 4);
126 END IF;
127 END LOOP; --}
128 END IF; --}
129 IF g_debug = 1 THEN
130 print_debug('Exiting Send_Dock_Doors API ', 4);
131 END IF;
132
133 EXCEPTION
134 WHEN OTHERS THEN
135 IF g_debug = 1 THEN
136 print_debug('Unexpected error in Send_Dock_Doors API ', 4);
137 print_debug(SQLERRM, 4);
138 END IF;
139 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
140 FND_MESSAGE.SET_NAME('WMS','WMS_DOCK_DOOR_SYNC');
141 FND_MSG_PUB.ADD;
142 FND_MSG_PUB.Count_And_Get
143 (p_count => l_msg_count,
144 p_data => x_msg_data);
145 END;
146
147 -- Get FND Security details.
148 -- Create New if doesn't exist
149 -- Return existing if valid.
150 -- If expired, delete existing and create new
151 PROCEDURE get_secure_ticket_details( p_op_code IN VARCHAR2,
152 p_argument IN VARCHAR2,
153 x_ticket OUT NOCOPY RAW,
154 x_return_status OUT NOCOPY VARCHAR2
155 )
156 IS
157 l_ticket RAW(16);
158 l_ticket_string VARCHAR2(1000);
159 l_operation VARCHAR2(255);
160 l_argument VARCHAR2(4000);
161 l_end_date VARCHAR2(100);
162 l_edate TimeStamp;
163 l_sysdate TimeStamp;
164
165 CURSOR c_get_ticket_details (c_operation VARCHAR2, c_argument VARCHAR2) IS
166 SELECT ticket, operation, argument, end_date
167 FROM FND_HTTP_TICKETS
168 WHERE operation = c_operation
169 AND argument = c_argument;
170
171 CURSOR c_get_sysdate IS
172 SELECT SYSDATE FROM DUAL;
173
174 BEGIN
175 IF g_debug = 1 THEN
176 print_debug('In Get_Secure_Ticket_Details API ', 4);
177 END IF;
178 x_return_status := FND_API.G_RET_STS_SUCCESS;
179
180 OPEN c_get_ticket_details (p_op_code,p_argument);
181 FETCH c_get_ticket_details INTO l_ticket,l_operation, l_argument, l_edate;
182 CLOSE c_get_ticket_details;
183
184 -- Ticket Exists. Valid and not expired
185 -- return the existing ticket
186 OPEN c_get_sysdate;
187 FETCH c_get_sysdate INTO l_sysdate;
188 CLOSE c_get_sysdate;
189
190 IF ( l_edate IS NOT NULL) AND ( l_edate > SYSDATE) THEN
191 IF g_debug = 1 THEN
192 print_debug('Tickets Exists. Valid and not expired', 4);
193 END IF;
194 -- l_ticket is actual ticket. Do Nothing.
195 -- Ticket Exists but expired.Delete existing
196 ELSIF ( l_edate IS NOT NULL) AND ( l_edate < SYSDATE) THEN
197 IF g_debug = 1 THEN
198 print_debug('Tickets Exists but expired. Delete existing', 4);
199 END IF;
200 FND_HTTP_TICKET.DESTROY_TICKET(l_ticket);
201
202 IF g_debug = 1 THEN
203 print_debug('Creating new ticket ...', 4);
204 END IF;
205 l_ticket := FND_HTTP_TICKET.CREATE_TICKET(p_op_code
206 ,p_argument
207 ,36000 --10 hrs
208 );
209 ELSE
210 IF g_debug = 1 THEN
211 print_debug('ticket does not exist. Create a new ticket', 4);
212 END IF;
213 l_ticket := FND_HTTP_TICKET.CREATE_TICKET(p_op_code
214 ,p_argument
215 ,36000 --10 hrs
216 );
217 END IF;
218
219 x_ticket := l_ticket;
220
221 IF g_debug = 1 THEN
222 print_debug('l_ticket : '||x_ticket, 4);
223 print_debug('Exiting Get_Secure_Ticket_Details API ', 4);
224 END IF;
225
226 EXCEPTION
227 WHEN others THEN
228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
229 IF g_debug = 1 THEN
230 print_debug('Unexpected error has occured in Get_Secure_Ticket_Details API ', 4);
231 print_debug(sqlerrm, 4);
232 END IF;
233 END get_secure_ticket_details;
234 END;