[Home] [Help]
PACKAGE BODY: APPS.WMS_CYCLE_PVT
Source
1 PACKAGE BODY WMS_Cycle_PVT AS
2 /* $Header: WMSCYCCB.pls 120.1.12000000.3 2007/04/17 13:13:36 abaid ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WMS_Cycle_PVT';
6
7 PROCEDURE print_debug(p_err_msg VARCHAR2)
8 IS
9 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
10 BEGIN
11 inv_mobile_helper_functions.tracelog
12 (p_err_msg => p_err_msg,
13 p_module => 'WMS_Cycle_PVT',
14 p_level => 4);
15
16 -- dbms_output.put_line(p_err_msg);
17 END print_debug;
18
19 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data to comply
20 --with GSCC File.Sql.39 standard Bug:4410902
21 PROCEDURE Create_Unscheduled_Counts
22 ( p_api_version IN NUMBER ,
23 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
24 p_commit IN VARCHAR2 := fnd_api.g_false ,
25 x_return_status OUT NOCOPY VARCHAR2 ,
26 x_msg_count OUT NOCOPY NUMBER ,
27 x_msg_data OUT NOCOPY VARCHAR2 ,
28 p_organization_id IN NUMBER ,
29 p_subinventory IN VARCHAR2 ,
30 p_locator_id IN NUMBER ,
31 p_inventory_item_id IN NUMBER
32 )
33 -- BUG#2867331 added inventory_item_id
34 IS
35 l_api_name CONSTANT VARCHAR2(30) := 'Create_Unscheduled_Counts';
36 l_api_version CONSTANT NUMBER := 1.0;
37 l_org INV_Validate.ORG;
38 l_sub INV_Validate.SUB;
39 l_locator INV_Validate.LOCATOR;
40 l_result NUMBER;
41 l_cycle_count_schedule_id NUMBER;
42 l_cycle_count_header_id NUMBER;
43 l_unscheduled_count_entry NUMBER;
44 l_req_id NUMBER;
45 l_zero_count_flag NUMBER ; --Bug 5236299
46
47 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
48 BEGIN
49 -- Standard Start of API savepoint
50 SAVEPOINT Create_Unscheduled_Counts_PVT;
51 -- Standard call to check for call compatibility.
52 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
53 p_api_version ,
54 l_api_name ,
55 G_PKG_NAME )
56 THEN
57 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
58 FND_MSG_PUB.ADD;
59 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60 END IF;
61 -- Initialize message list if p_init_msg_list is set to TRUE.
62 IF FND_API.to_Boolean( p_init_msg_list ) THEN
63 FND_MSG_PUB.initialize;
64 END IF;
65 -- Initialize API return status to success
66 x_return_status := FND_API.G_RET_STS_SUCCESS;
67
68 -- API body
69 /* Validate all inputs */
70
71 IF (l_debug = 1) THEN
72 print_debug('Calling Create_Unscheduled_Counts_PVT');
73 print_debug('Organization ID: ' || p_organization_id);
74 print_debug('Subinventory: ' || p_subinventory);
75 print_debug('Locator ID: ' || p_locator_id);
76 print_debug('Inventory Item ID: ' || p_inventory_item_id);
77 END IF;
78 /* Validate Organization ID */
79 l_org.organization_id := p_organization_id;
80 l_result := INV_Validate.Organization(l_org);
81 IF (l_result = INV_Validate.F) THEN
82 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_ORG');
83 FND_MSG_PUB.ADD;
84 RAISE FND_API.G_EXC_ERROR;
85 END IF;
86
87 /* Validate Subinventory */
88 IF (p_subinventory IS NOT NULL) THEN
89 l_sub.secondary_inventory_name := p_subinventory;
90 l_result := INV_Validate.subinventory(l_sub, l_org);
91 IF (l_result = INV_Validate.F) THEN
92 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_SUB');
93 FND_MSG_PUB.ADD;
94 RAISE FND_API.G_EXC_ERROR;
95 END IF;
96 END IF;
97
98 /* Validate Locator */
99 IF (p_subinventory IS NOT NULL) THEN
100 IF (l_sub.locator_type IN (2,3)) THEN
101 IF (p_locator_id IS NULL) THEN
102 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_MISS_REQ_LOC');
103 FND_MSG_PUB.ADD;
104 RAISE FND_API.G_EXC_ERROR;
105 END IF;
106 l_locator.inventory_location_id := p_locator_id;
107 l_result := INV_Validate.validateLocator(l_locator, l_org, l_sub);
108 IF (l_result = INV_Validate.F) THEN
109 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LOC');
110 FND_MSG_PUB.ADD;
111 RAISE FND_API.G_EXC_ERROR;
112 END IF;
113 END IF;
114 END IF;
115
116 /* Validate the default cycle count header */
117 IF (l_org.default_cyc_count_header_id IS NULL) THEN
118 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_INVALID_HEADER');
119 FND_MSG_PUB.ADD;
120 RAISE FND_API.G_EXC_ERROR;
121 ELSE
122 l_cycle_count_header_id := l_org.default_cyc_count_header_id;
123 END IF;
124
125 /* Check that unscheduled counts are allowed */
126 SELECT unscheduled_count_entry
127 INTO l_unscheduled_count_entry
128 FROM mtl_cycle_count_headers
129 WHERE cycle_count_header_id = l_cycle_count_header_id
130 AND organization_id = p_organization_id;
131 IF (l_unscheduled_count_entry = 2) THEN
132 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_NO_UNSCHED_COUNTS');
133 FND_MSG_PUB.ADD;
134 RAISE FND_API.G_EXC_ERROR;
135 END IF;
136 IF (l_debug = 1) THEN
137 print_debug('Finished Validations');
138 END IF;
139
140 /* End of input validation */
141
142 -- Get a new cycle count schedule ID
143 SELECT mtl_cc_schedule_requests_s.NEXTVAL
144 INTO l_cycle_count_schedule_id
145 FROM dual;
146 IF (l_debug = 1) THEN
147 print_debug('CC Schedule ID: ' || l_cycle_count_schedule_id);
148 END IF;
149
150 /* Bug 5236299 - Selecting the zero count flag for the cycle count */
151
152 SELECT NVL(zero_count_flag,2)
153 INTO l_zero_count_flag
154 FROM mtl_cycle_count_headers
155 WHERE cycle_count_header_id = l_cycle_count_header_id
156 AND organization_id = p_organization_id;
157
158 IF (l_debug = 1) THEN
159 print_debug('Zero Count Flag: ' || l_zero_count_flag);
160 END IF;
161
162 /* End of fix for Bug 5236299 */
163
164 -- Insert a new record into MTL_CC_SCHEDULE_REQUESTS
165 IF (l_debug = 1) THEN
166 print_debug('Inserting into table MTL_CC_SCHEDULE_REQUESTS');
167 END IF;
168 INSERT INTO MTL_CC_SCHEDULE_REQUESTS
169 (cycle_count_schedule_id, last_update_date, last_updated_by,
170 creation_date, created_by, cycle_count_header_id,
171 request_source_type, zero_count_flag, schedule_date,
172 schedule_status, subinventory, locator_id,
173 inventory_item_id) -- BUG #2867331
174 VALUES
175 (l_cycle_count_schedule_id, SYSDATE, FND_GLOBAL.USER_ID,
176 SYSDATE, FND_GLOBAL.USER_ID, l_cycle_count_header_id,
177 2, l_zero_count_flag, SYSDATE, 1, --Bug 5236299- inserting the value of l_zero_count_flag
178 p_subinventory, p_locator_id,
179 p_inventory_item_id); --BUG#2867331
180
181 -- We need to commit this insert first before
182 -- we can call the concurrent programs
183 COMMIT;
184
185 -- Call the concurrent program to generate count requests
186 IF (l_debug = 1) THEN
187 print_debug('Calling the generate count requests concurrent program');
188 END IF;
189 l_req_id := fnd_request.submit_request
190 ( application => 'INV',
191 program => 'INCACG',
192 argument1 => '2',
193 argument2 => TO_CHAR(l_cycle_count_header_id),
194 argument3 => TO_CHAR(p_organization_id));
195
196 -- Check for errors
197 IF (l_req_id <= 0 OR l_req_id IS NULL) THEN
198 IF (l_debug = 1) THEN
199 print_debug('Error in calling the generate count requests program');
200 END IF;
201 RAISE FND_API.G_EXC_ERROR;
202 ELSE
203 COMMIT;
204 END IF;
205
206 -- Call the concurrent program to print
207 -- the cycle count listing report
208 IF (l_debug = 1) THEN
209 print_debug('About to call the cycle count listing report program');
210 END IF;
211 l_req_id := fnd_request.submit_request
212 ( application => 'INV',
213 program => 'INVARCLI',
214 argument1 => TO_CHAR(p_organization_id),
215 argument2 => TO_CHAR(l_cycle_count_header_id),
216 argument3 => TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'), --bug 5944231
217 argument4 => TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'), --bug 5944231
218 argument5 => '2',
219 argument6 => p_subinventory,
220 argument7 => '1',
221 argument8 => '1',
222 argument9 => '1');
223
224 -- Check for errors
225 IF (l_req_id <= 0 OR l_req_id IS NULL) THEN
226 IF (l_debug = 1) THEN
227 print_debug('Errored out when calling the cycle count listing report program');
228 END IF;
229 NULL;
230 ELSE
231 COMMIT;
232 END IF;
233
234 -- End of API body
235 -- Standard check of p_commit.
236 IF FND_API.To_Boolean( p_commit ) THEN
237 COMMIT WORK;
238 END IF;
239 -- Standard call to get message count and if count is 1,
240 -- get message info.
241 FND_MSG_PUB.Count_And_Get
242 ( p_count => x_msg_count,
243 p_data => x_msg_data
244 );
245 IF (l_debug = 1) THEN
246 print_debug('Successfully called the procedure Create_Unscheduled_Counts');
247 END IF;
248
249 EXCEPTION
250 WHEN FND_API.G_EXC_ERROR THEN
251 ROLLBACK TO Create_Unscheduled_Counts_PVT;
252 x_return_status := FND_API.G_RET_STS_ERROR;
253 IF (l_debug = 1) THEN
254 print_debug('Execution error!');
255 END IF;
256 FND_MSG_PUB.Count_And_Get
257 ( p_count => x_msg_count,
258 p_data => x_msg_data
259 );
260 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
261 ROLLBACK TO Create_Unscheduled_Counts_PVT;
262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
263 IF (l_debug = 1) THEN
264 print_debug('Unexpected error!');
265 END IF;
266 FND_MSG_PUB.Count_And_Get
267 ( p_count => x_msg_count,
268 p_data => x_msg_data
269 );
270 WHEN OTHERS THEN
271 ROLLBACK TO Create_Unscheduled_Counts_PVT;
272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
273 IF (l_debug = 1) THEN
274 print_debug('Others error!');
275 END IF;
276 IF FND_MSG_PUB.Check_Msg_Level
277 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
278 THEN
279 FND_MSG_PUB.Add_Exc_Msg
280 ( G_PKG_NAME ,
281 l_api_name
282 );
283 END IF;
284 FND_MSG_PUB.Count_And_Get
285 ( p_count => x_msg_count,
286 p_data => x_msg_data
287 );
288
289 END Create_Unscheduled_Counts;
290
291
292 -- End of package
293 END WMS_Cycle_PVT;